Czym różni się programista od polityka? Programiście płacą tylko za programy, które dają jakiś efekt...
Kurs MySQL - przykłady zapytań SQL

Przykłady zapytań SQL

Przedstawione poniżej przykłady pokazują, jak rozwiązać pewne powszechnie występujące problemy za pomocą MySQL.

Niektóre przykłady wykorzystują tabelę sklep do przechowywania ceny każdego artykułu (nr produktu) u pewnych sprzedawców. Zakładając, że każdy sprzedawca ma pewną ustaloną i stałą cenę na każdy artykuł, możemy ustanowić klucz główny (primary key) naszej tabeli: artykul i sprzedawca.

Uruchom program mysql i wybierz bazę danych:

    mysql nazwa-twojej-bazy

W większości wypadków po instalacji możesz użyć nazwy bazy test.

Możesz utworzyć przykładową tabelę za pomocą polecenia:

   CREATE TABLE sklep (
   artykul INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
   sprzedawca  CHAR(20)             DEFAULT ''     NOT NULL,
   cena   DOUBLE(16,2)              DEFAULT '0.00' NOT NULL,
   PRIMARY KEY(artykul, sprzedawca));

INSERT INTO sklep VALUES
   (1, 'A', 3.45),
   (1, 'B', 3.99),
   (2, 'A', 10.99),
   (3, 'B', 1.45),
   (3, 'C', 1.69),
   (3, 'D', 1.25),
   (4, 'D', 19.95);

A teraz wyświetl swoje przykładowe dane:

    SELECT * FROM sklep;

    +---------+------------+-------+
    | artykul | sprzedawca | cena  |
    +---------+------------+-------+
    |    0001 | A          |  3.45 |
    |    0001 | B          |  3.99 |
    |    0002 | A          | 10.99 |
    |    0003 | B          |  1.45 |
    |    0003 | C          |  1.69 |
    |    0003 | D          |  1.25 |
    |    0004 | D          | 19.95 |
    +---------+------------+-------+

Przykład 1. Maksymalna wartość kolumny

Jaki jest największy numer artykułu?:

    SELECT MAX(artykul) AS artykul FROM sklep;

    +---------+
    | artykul |
    +---------+
    |    0004 |
    +---------+

Przykład 2 Wiersz zawierający maksymalną wartość dla wybranej kolumny

Kolejne zadanie polega na znalezieniu numeru, sprzedawcy i ceny najdroższego artykułu.

W MySQL, który nie obsługuje zapytań zagnieżdżonych, musimy to zrobić w dwóch krokach:

  1. Znajdź najwyższą wartość ceny z tabeli za pomocą instrukcji SELECT.
        SELECT MAX(cena) AS artykul FROM sklep;
    
        +--------+
        | cena   |
        +--------+
        |  19.95 |
        +--------+

     
  2. Używając znalezionej wartości sformułujmy następujące zapytanie:
        SELECT artykul, sprzedawca, cena
        FROM   sklep
        WHERE  cena=19.95;
    
        +---------+------------+-------+
        | artykul | sprzedawca | cena  |
        +---------+------------+-------+
        |    0004 | D          | 19.95 |
        +---------+------------+-------+

     

Innym rozwiązaniem jest posortowanie malejąco wg cen i wybranie tylko pierwszego wiersza przy użyciu specyficznej dla MySQL klauzuli LIMIT:

    SELECT artykul, sprzedawca, cena\n   FROM sklep\n   ORDER BY cena DESC\n   LIMIT 1;
	
    +---------+------------+-------+
    | artykul | sprzedawca | cena  |
    +---------+------------+-------+
    |    0004 | D          | 19.95 |
    +---------+------------+-------+

Jeżeli jest więcej niż jeden najdroższy artykuł (np.: każdy kosztuje 19.95), to użycie LIMIT pokaże tylko 1 (pierwszy z nich)!

Przykład 3. Maksymalna wartość kolumny dla grupy rekordów

Kolejnym zadaniem jest udzielenie odpowiedzi na pytanie: Jaka jest najwyższa cena każdego artykułu?

    SELECT artykul, MAX(cena) AS cena\n   FROM   sklep\n   GROUP BY artykul;
	
    +---------+-------+
    | artykul | cena  |
    +---------+-------+
    |    0001 |  3.99 |
    |    0002 | 10.99 |
    |    0003 |  1.69 |
    |    0004 | 19.95 |
    +---------+-------+

Przykład 4. Grupowanie wartości maksymalnych

Kolejne zadanie jest bardziej złożone: Dla każdego artykułu, znajdź sprzedawców mających najwyższe ceny.

W MySQL trzeba to zrobić w kilku krokach:

  1. Pobierz listę (artykuł, max_cena).
     
  2. Dla każdego artykułu pobierz odpowiadający mu wiersz zawierający najwyższą cenę.
     

Można to zrobić wykorzystując tabelę pośrednią (tymczasową - temporary):

    CREATE TEMPORARY TABLE tmp (
       artykul INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
       cena   DOUBLE(16,2)              DEFAULT '0.00' NOT NULL);

    LOCK TABLES sklep read;

    INSERT INTO tmp SELECT artykul, MAX(cena) 
        FROM sklep 
        GROUP BY artykul;

    SELECT sklep.artykul, sprzedawca, cena
        FROM sklep, tmp
        WHERE sklep.artykul = tmp.artykul AND sklep.cena = tmp.cena;

    UNLOCK TABLES;

    DROP TABLE tmp;

Jeżeli nie będziesz używał tabeli TEMPORARY musisz zablokować (lock) także tabelę tmp.

Nasuwa się pytanie, czy nie dałoby się zrobić tego jednym zapytaniem? Owszem, ale tylko przy użyciu mało nieefektywnej sztuczki:

    SELECT artykul,
           SUBSTRING( MAX( CONCAT( LPAD(cena, 6, '0'), sprzedawca)), 7) AS sprzedawca,
           0.00 + LEFT( MAX( CONCAT( LPAD(cena, 6, '0'), sprzedawca) ), 6) AS cena
       FROM   sklep
       GROUP BY artykul;

    +---------+------------+-------+
    | artykul | sprzedawca | cena  |
    +---------+------------+-------+
    |    0001 | B          |  3.99 |
    |    0002 | A          | 10.99 |
    |    0003 | C          |  1.69 |
    |    0004 | D          | 19.95 |
    +---------+------------+-------+

Ostatni przykład można wykonać trochę bardziej efektywnie przez wykonywanie podziału połączonych kolumn po stronie klienta.

Przykład 5. Używanie zmiennych użytkownika

Możesz używać zmiennych użytkownika do zapamiętywania wyników bez konieczności przechowywania ich w zmiennych tymczasowych po stronie klienta.

W przykładzie znajdywania artykułu o najwyższej i najniższej cenie możesz postąpić w następujący sposób:

    SELECT @min_cena:=min(cena), @max_cena:=max(cena)
       FROM sklep;

    SELECT * 
       FROM sklep
       WHERE cena=@min_cena OR cena=@max_cena;

    +---------+------------+-------+
    | artykul | sprzedawca | cena  |
    +---------+------------+-------+
    |    0003 | D          |  1.25 |
    |    0004 | D          | 19.95 |
    +---------+------------+-------+

Przykład 6. Używanie kluczy obcych (foreign keys)

Jednyej rzeczy jakiej nie robi MySQL to funkcja CHECK czyli upewnienie się, że klucze których używasz rzeczywiście istnieją w tabeli(ach), do których się odwołujesz oraz nie usuwa automatycznie wierszy z tabeli zawierającej definicję obcego klucza. Jeżeli używasz swoich kluczy normalnie, będzie to działać całkiem sprawnie!

Poniższy przykład pokazuje, jak wybrać dane z jednej tabeli na podstawie wartości klucza zapisanego w drugiej tabeli:

    CREATE TABLE osoby (
        id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
        nazwisko CHAR(60) NOT NULL,
        PRIMARY KEY (id)
    );

    CREATE TABLE koszulki (
        id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
        styl ENUM('t-shirt', 'polo', 'golf') NOT NULL,
        kolor ENUM('czerwony', 'niebieski', 'zielony', 'biały', 'czarny') NOT NULL,
        wlasciciel SMALLINT UNSIGNED NOT NULL REFERENCES osoby,
        PRIMARY KEY (id)
    );

    INSERT INTO osoby VALUES (NULL, 'Jan Kowalski');

    INSERT INTO koszulki VALUES
         (NULL, 'polo', 'niebieski', LAST_INSERT_ID()),
         (NULL, 'golf', 'biały', LAST_INSERT_ID()),
         (NULL, 't-shirt', 'niebieski', LAST_INSERT_ID());

    INSERT INTO osoby VALUES (NULL, 'Ewa Wisniewska');

    INSERT INTO koszulki VALUES
         (NULL, 'golf', 'zielony', LAST_INSERT_ID()),
         (NULL, 'polo', 'czerwony', LAST_INSERT_ID()),
         (NULL, 'golf', 'niebieski', LAST_INSERT_ID()),
         (NULL, 't-shirt', 'biały', LAST_INSERT_ID());

    SELECT * FROM osoby;

    +----+----------------+
    | id | nazwisko       |
    +----+----------------+
    |  1 | Jan Kowalski   |
    |  2 | Ewa Wisniewska |
    +----+----------------+

    SELECT * FROM koszulki;

    +----+---------+-----------+------------+
    | id | styl    | kolor     | wlasciciel |
    +----+---------+-----------+------------+
    |  1 | polo    | niebieski |          1 |
    |  2 | golf    | biały     |          1 |
    |  3 | t-shirt | niebieski |          1 |
    |  4 | golf    | zielony   |          2 |
    |  5 | polo    | czerwony  |          2 |
    |  6 | golf    | niebieski |          2 |
    |  7 | t-shirt | biały     |          2 |
    +----+---------+-----------+------------+

    SELECT s.*
        FROM osoby p, koszulki s
        WHERE p.nazwisko LIKE 'Ewa%'
              AND s.wlasciciel = p.id
              AND s.kolor <> 'biały';

    +----+-------+-----------+------------+
    | id | styl  | kolor     | wlasciciel |
    +----+-------+-----------+------------+
    |  4 | golf  | zielony   |          2 |
    |  5 | polo  | czerwony  |          2 |
    |  6 | golf  | niebieski |          2 |
    +----+-------+-----------+------------+

Przykład 7. Wyszukiwanie według dwóch kluczy

Aby wybrać z tabeli rekordy spełniające jeden z 2 warunków możesz użyć prostego polecenia z OR:

    SELECT ple1, pole2
        FROM tabela
        WHERE ple1 = '1' OR  ple2 = '1';
Jednak bardziej efektywną metoda jest skorzystanie z klauzuli UNION:
    SELECT pole1, pole2
        FROM tabela
        WHERE pole1 = '1'
    UNION
    SELECT pole1, pole2
        FROM tabela
        WHERE pole2 = '1';

Przykład 7. Obliczanie ilości wizyt

Kolejny przykład pokazuje jak użyć funkcji grupowania bitów do obliczenia, ile dni (dni - nie razy) miesięcznie użytkownik odwiedzał np. stronę www:

    CREATE TABLE t1 (rok YEAR(4),
                 miesiac INT(2) UNSIGNED ZEROFILL,
                 dzien INT(2) UNSIGNED ZEROFILL);

    INSERT INTO t1 VALUES
        (2005, 1, 1),
        (2005, 1, 30),
        (2005, 1, 30),
        (2005, 2, 2),
        (2005, 2, 23),
        (2005, 2, 23);

    SELECT rok, miesiac,
            BIT_COUNT( BIT_OR( 1 << dzien)) AS dni
        FROM t1
        GROUP BY rok, miesiac;

    +------+---------+-----+
    | rok  | miesiac | dni |
    +------+---------+-----+
    | 2005 |      01 |   2 |
    | 2005 |      02 |   2 |
    +------+---------+-----+

Wynik pokazuje ile dni każdego miesiąca każdego roku występuje w tabeli z pominięciem powtarzających się dni.

Przykład 8. Użycie atrybutu AUTO_INCREMENT

Atrybutu AUTO_INCREMENT możesz użyć do nadania każdemu rekordowi tabeli unikalnego identyfikatora:

    CREATE TABLE zwierzeta (
       id MEDIUMINT NOT NULL AUTO_INCREMENT,
       nazwa CHAR(30) NOT NULL,
       PRIMARY KEY (id)
     );

    INSERT INTO zwierzeta (nazwa) VALUES
      ('pies'), ('kot'),
      ('gil'), ('szpak'),
      ('karp'), ('lin');

    SELECT * FROM zwierzeta;

    +----+-------+
    | id | nazwa |
    +----+-------+
    |  1 | pies  |
    |  2 | kot   |
    |  3 | gil   |
    |  4 | szpak |
    |  5 | karp  |
    |  6 | lin   |
    +----+-------+

Możesz sprawdzić ostatnio nadaną wartość polu AUTO_INCREMENT korzystając z funkcji LAST_INSERT_ID().

Jeżeli wstawiasz jednym poleceniem wiele wierszy, to LAST_INSERT_ID() zwróci wartość AUTO_INCREMENT pierwszego wstawionego wiersza tabeli.

Jeżeli używasz atrybutu AUTO_INCREMENT dla pola, które występuje jako drugie w wielokolumnowym kluczu podstawowym. to wartość ttego pola jest wyliczna według wzoru:

    MAX(kolumna_auto_increment) + 1 WHERE prefix = dodawany-prefix
Może to być bardzo przydatne przy grupowaniu danych, ale musisz pamiętać, że w takim przypadku wartości pola AUTO_INCREMENT mogą się powtarzać w ramach tabeli:
    CREATE TABLE zwierzeta (
         id MEDIUMINT NOT NULL AUTO_INCREMENT,
         nazwa CHAR(30) NOT NULL,
         grupa ENUM('ryba','ssak','ptak') NOT NULL,
         PRIMARY KEY (grupa, id)
     );

    INSERT INTO zwierzeta (grupa, nazwa) VALUES
        ('ssak', 'pies'), ('ssak', 'kot'),
        ('ptak', 'gil'), ('ptak', 'szpak'),
        ('ryba', 'karp'), ('ryba', 'lin');

    SELECT * FROM zwierzeta ORDER BY grupa, id;

    +----+-------+--------+
    | id | nazwa | grupa  |
    +----+-------+--------+
    |  1 | pies  | ssak   |
    |  2 | kot   | ssak   |
    |  1 | gil   | ptak   |
    |  2 | szpak | ptak   |
    |  1 | karp  | ryba   |
    |  2 | lin   | ryba   |
    +----+-------+--------+
« wstecz   dalej »