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ę Uruchom program 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:
Innym rozwiązaniem jest posortowanie malejąco wg cen i wybranie tylko pierwszego wiersza przy użyciu specyficznej dla MySQL
klauzuli 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 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:
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 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 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 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 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 Jeżeli wstawiasz jednym poleceniem wiele wierszy, to Jeżeli używasz atrybutu MAX(kolumna_auto_increment) + 1 WHERE prefix = dodawany-prefixMoż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 | +----+-------+--------+
|