Klasa III wyszukiwanie informacji w bazie danych

Na dzisiejszych zajęciach stworzymy trzy tabele powiązane relacją. Będą one miały następującą budowę:

kraje
id	kod	nazwa	waluta	populacja	stolica	kontynent
INT	CHAR(3)	VARCHAR(45)	CHAR(3)	BIGINT	VARCHAR(30)	CHAR(2)

jezyki
kraj	jezyk
CHAR(3)	VARCHAR(10)

kontynenty

kod kontynent

CHAR(2) VARCHAR(18)

Tabele są powiązane ze sobą relacjami:

Tabela języki w kolumnie kraj przechowuje kody krajów z kolumny kod w tabeli kraje.

Tabela kraje w kolumnie kontynent przechowuje kody kontynentów z kolumny kod w tabeli kontynenty.

Zaloguj się na swój serwer mysql za pomocą polecenia podanego przez nauczyciela na poprzednich zajęciach. Wpisz polecenia tworzenia tabel:

CREATE TABLE kraje
(
   id INT AUTO_INCREMENT PRIMARY KEY,
   kod CHAR(3) NOT NULL,
   nazwa VARCHAR(45) NOT NULL,
   waluta CHAR(3) DEFAULT NULL,
   populacja BIGINT DEFAULT 0,
   stolica VARCHAR(30) DEFAULT NULL,
   kontynent CHAR(2) DEFAULT NULL
);
CREATE TABLE jezyki
(
   kraj CHAR(3) NOT NULL,
   jezyk VARCHAR(10) NOT NULL,
   PRIMARY KEY(kraj,jezyk)
);
CREATE TABLE kontynenty
(
   kod CHAR(2) PRIMARY KEY,
   kontynent VARCHAR(13) NOT NULL
);

Sprawdź tabele w swojej bazie danych:

SHOW TABLES;
+-------------------+
| Tables_in_base3k1 |
+-------------------+
| jezyki            |
| kontynenty        |
| kraje             |
+-------------------+
3 rows in set (0.00 sec)

Wprowadzimy dane do tabeli kontynenty:

INSERT INTO kontynenty (kod,kontynent) VALUES
('AF','Africa'),
('EU','Europe'),
('AS','Asia'),
('SA','South America'),
('NA','North America'),
('AN','Antarctica'),
('OC','Oceania');

Sprawdzamy:

SELECT * FROM kontynenty;
+-----+---------------+
| kod | kontynent     |
+-----+---------------+
| AF  | Africa        |
| AN  | Antarctica    |
| AS  | Asia          |
| EU  | Europe        |
| NA  | North America |
| OC  | Oceania       |
| SA  | South America |
+-----+---------------+
7 rows in set (0.00 sec)

Dane dla pozostałych dwóch tabel wczytamy z pliku. Pobierz poniższy plik i zapisz go w katalogu /tmp:

jezyki.txt

Następnie wpisz w mysql polecenie:

LOAD DATA INFILE '/tmp/jezyki.txt' INTO TABLE jezyki FIELDS TERMINATED BY ',';

Wyświetl zawartość tabeli:

SELECT * FROM jezyki;

Teraz wczytamy tabelę kraje. Pobierz poniższy plik i umieść go w katalogu /tmp.

kraje.txt

Wpisz w mysql polecenie:

LOAD DATA INFILE '/tmp/kraje.txt' INTO TABLE kraje FIELDS TERMINATED BY ',';

Wyświetl zawartość tabeli:

SELECT * FROM kraje;

Mamy przygotowaną bazę danych. Wykorzystamy ją do uzyskania różnych informacji o krajach.

Wyszukiwanie informacji w pojedynczej tabeli uzyskamy za pomocą polecenia:

SELECT kolumna_1,kolumna_2,...,kolumna_n FROM tabela 
		WHERE warunek;

Na przykład wyszukajmy wszystkie kraje, w których walutą jest USD. Wpisujemy:

SELECT nazwa,waluta FROM kraje WHERE waluta='USD';

+--------------------------------+--------+
| nazwa                          | waluta |
+--------------------------------+--------+
| American Samoa                 | USD    |
| Bonaire                        | USD    |
| Ecuador                        | USD    |
| Micronesia                     | USD    |
| Guam                           | USD    |
| British Indian Ocean Territory | USD    |
| Marshall Islands               | USD    |
| Northern Mariana Islands       | USD    |
| Puerto Rico                    | USD    |
| Palau                          | USD    |
| El Salvador                    | USD    |
| Turks and Caicos Islands       | USD    |
| East Timor                     | USD    |
| U.S. Minor Outlying Islands    | USD    |
| United States                  | USD    |
| British Virgin Islands         | USD    |
| U.S. Virgin Islands            | USD    |
+--------------------------------+--------+
17 rows in set (0.00 sec)

W podobny sposób znajdź kraje korzystające z EUR, PLN, GBP.

Wynik zapytania można sortować wg wybranej kolumny rosnąco ASC lub malejąco DEC. Na przykład, jeszcze raz wyświetlmy kraje używające USD, lecz posortujmy je w porządku alfabetycznym:

SELECT nazwa,waluta FROM kraje WHERE waluta='USD' ORDER BY nazwa ASC;

+--------------------------------+--------+
| nazwa                          | waluta |
+--------------------------------+--------+
| American Samoa                 | USD    |
| Bonaire                        | USD    |
| British Indian Ocean Territory | USD    |
| British Virgin Islands         | USD    |
| East Timor                     | USD    |
| Ecuador                        | USD    |
| El Salvador                    | USD    |
| Guam                           | USD    |
| Marshall Islands               | USD    |
| Micronesia                     | USD    |
| Northern Mariana Islands       | USD    |
| Palau                          | USD    |
| Puerto Rico                    | USD    |
| Turks and Caicos Islands       | USD    |
| U.S. Minor Outlying Islands    | USD    |
| U.S. Virgin Islands            | USD    |
| United States                  | USD    |
+--------------------------------+--------+
17 rows in set (0.03 sec)

Jeśli nie podoba nam się nazwa kolumny i w wyniku zapytania chcielibyśmy mieć inną nazwę, to robimy to tak:

SELECT nazwa AS Kraj,waluta AS Waluta FROM kraje WHERE waluta='USD' ORDER BY nazwa ASC;

+--------------------------------+--------+
| Kraj                           | Waluta |
+--------------------------------+--------+
| American Samoa                 | USD    |
| Bonaire                        | USD    |
| British Indian Ocean Territory | USD    |
| British Virgin Islands         | USD    |
| East Timor                     | USD    |
| Ecuador                        | USD    |
| El Salvador                    | USD    |
| Guam                           | USD    |
| Marshall Islands               | USD    |
| Micronesia                     | USD    |
| Northern Mariana Islands       | USD    |
| Palau                          | USD    |
| Puerto Rico                    | USD    |
| Turks and Caicos Islands       | USD    |
| U.S. Minor Outlying Islands    | USD    |
| U.S. Virgin Islands            | USD    |
| United States                  | USD    |
+--------------------------------+--------+
17 rows in set (0.00 sec)

Wyszukajmy teraz wszystkie kraje leżące w Europie. Symbol Europy to EU:

SELECT nazwa AS 'Kraj w Europie' FROM kraje WHERE 
		kontynent='EU' ORDER BY nazwa ASC;

W podobny sposób wyszukaj kraje w Azji (AS), Afryce (AF) i Ameryce Północnej (NA).

Wyszukajmy kraje, w których liczba ludności jest większa od 100000000. W wyniku chcemy uzyskać informację o nazwie kraju, populacji i nazwie kontynentu:

SELECT nazwa AS Kraj,populacja AS Ludność,kontynent AS Kontynent FROM kraje WHERE populacja>100000000 ORDER BY nazwa;

Niestety, w wyniku otrzymaliśmy jedynie kody kontynentów, a nie same nazwy kontynentów:

+---------------+------------+-----------+
| Kraj          | Ludność    | Kontynent |
+---------------+------------+-----------+
| Bangladesh    |  156118464 | AS        |
| Brazil        |  201103330 | SA        |
| China         | 1330044000 | AS        |
| India         | 1173108018 | AS        |
| Indonesia     |  242968342 | AS        |
| Japan         |  127288000 | AS        |
| Mexico        |  112468855 | NA        |
| Nigeria       |  154000000 | AF        |
| Pakistan      |  184404791 | AS        |
| Russia        |  140702000 | EU        |
| United States |  310232863 | NA        |
+---------------+------------+-----------+
11 rows in set (0.02 sec)

Nazwy są w innej tabeli. Aby otrzymać te nazwy w wyniku zapytania, musimy w instrukcji SELECT użyć dwóch tabel połączonych relacją. Relacja ta jest taka, że kolumna kontynent w tabeli kraje przechowuje klucz podstawowy rekordu w tabeli kontynenty. Na razie wyświetlmy wszystkie kraje z informacją o kontynencie, na którym się znajdują:

SELECT nazwa AS Kraj,kontynenty.kontynent AS Kontynent FROM kraje,kontynenty WHERE kraje.kontynent=kontynenty.kod ORDER BY nazwa;

Ponieważ korzystamy w zapytaniu z dwóch tabel, to może się zdarzyć, że w tych tabelach istnieją kolumny o takich samych nazwach. W takim przypadku należy zawsze odwoływać się do kolumny poprzez nazwę tabeli, kropkę i nazwę kolumny w tej tabeli. Inaczej mysql zgłosi błąd polegający na niejednoznaczności nazw kolumn. Oczywiście, najlepiej jest tak zaprojektować bazę danych, aby kolumny nie miały tych samych nazw. Wracając do naszego pierwotnego problemu, należy w zapytaniu wykorzystać operator logiczny AND, aby uzyskać dodatkowo informację o nazwach kontynentów:

SELECT nazwa AS Kraj,populacja AS Ludność,kontynenty.kontynent AS Kontynent FROM kraje,kontynenty
WHERE populacja>100000000 AND kraje.kontynent=kontynenty.kod ORDER BY nazwa;

+---------------+------------+---------------+
| Kraj          | Ludność    | Kontynent     |
+---------------+------------+---------------+
| Bangladesh    |  156118464 | Asia          |
| Brazil        |  201103330 | South America |
| China         | 1330044000 | Asia          |
| India         | 1173108018 | Asia          |
| Indonesia     |  242968342 | Asia          |
| Japan         |  127288000 | Asia          |
| Mexico        |  112468855 | North America |
| Nigeria       |  154000000 | Africa        |
| Pakistan      |  184404791 | Asia          |
| Russia        |  140702000 | Europe        |
| United States |  310232863 | North America |
+---------------+------------+---------------+
11 rows in set (0.00 sec)

Ćwiczenia

Sprawdź, czy waluta 'EUR' jest używana w Azji.
Poszukaj krajów, gdzie nikt nie mieszka.
Wyszukaj kraje, w których używa się języka niemieckiego (de), francuskiego (fr) i odmian angielskiego (LIKE 'en%').

Podsumowanie poznanych poleceń SQL

Tabele w bazie danych

SHOW TABLES;

Wprowadzanie do tabeli wielu rekordów

INSERT INTO tabela 
		(kolumna_1,kolumna_2,...,kolumna_n) VALUES

		(wartości kolumn w pierwszym rekordzie),

		(wartości kolumn w drugim rekordzie),

		...

		(wartości kolumn w ostatnim rekordzie);

Odczyt zawartości tabeli z pliku

LOAD DATA INFILE 'plik' INTO TABLE tabela FIELDS 
		TERMINATED BY 'znak';

Wyświetlanie rekordów spełniających warunek

SELECT kolumna_1,kolumna_2,...,kolumna_n FROM 
		tabela_1,tabela_2,...,tabela_n WHERE warunek ORDER BY kolumna;

Odwoływanie się do nazw kolumn o tych samych nazwach w różnych tabelach

tabela.kolumna

Zmiana nazwy kolumny w wyniku zapytania

SELECT kolumna AS nazwa...

Przydatne operatory w warunkach WHERE

= <> != > < >= <=
AND
LIKE wzorzec
NOT LIKE wzorzec

wzorzec jest tekstem, w którym % oznacza dowolny ciąg znaków, a _ zastępuje jeden dowolny znak. Na przykład, jeśli kolumna imie zawiera imiona, to warunek:

imie LIKE 'A%'

będzie prawdziwy dla imion na literę A. Z kolei warunek:

imie NOT LIKE 'A%'

będzie prawdziwy dla wszystkich imion, które nie rozpoczynają się literą A.

Informatyka dla klas III

Wyszukiwanie informacji w bazie danych