Matura: internet - SQL

Najpierw przygotujemy na dysku katalog na pliki danych. Otwórz Eksplorator plików i w głównym katalogu dysku C: utwórz katalog C:\sql:

Otwórz ten katalog. W katalogu sql utwórz katalog d1, w którym będziemy przechowywać pliki na potrzeby tej lekcji:

Teraz otwórz notatnik Windows i wpisz w nim tekst:

@echo off
c:
cd \sql
\xampp\mysql\bin\mysql -u twoje_konto -ptwoje_hasło twoja_baza

Zamień w tekście czerwone napisy na swoje dane. Jest to prosty skrypt dla systemu Windows:

@echo off	–	To polecenie wyłącza wyświetlanie wykonywanych poleceń skryptu w oknie konsoli. Znak @ na początku powoduje, iż samo polecenie echo off również nie zostanie wyświetlone, tylko wykonane.
c:	–	Wybiera dysk C: jako dysk bieżący
cd \sql	–	Ustawia katalog c:\sql jako katalog bieżący
\xampp\...	–	Uruchamia terminal MySQL.

Uwaga! Skrypt zakłada, iż katalog sql został utworzony w katalogu głównym dysku C:. Jeśli utworzyłeś go w innym miejscu, to musisz skrypt odpowiednio zmodyfikować. Również skrypt zakłada, iż pakiet XAMPP został zainstalowany standardowo w katalogu C:\xampp.

Plik notatnika zapisz w katalogu pulpit pod nazwą sql.cmd. Plik sql.cmd będzie teraz automatycznie uruchamiał terminal MySQL z katalogu c:\sql.

Uruchom panel sterowania XAMPP i włącz serwer MySQL:

Przejdź na pulpit i kliknij dwukrotnie lewym przyciskiem myszki w ikonę sql.cmd:

Jeśli wszystko wykonałeś poprawnie, to powinien uruchomić się terminal MySQL z twoim kontem i z twoją bazą danych:

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 19
Server version: 10.4.32-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [twoja_baza]>

Sprawdzimy, czy pracujemy we właściwym katalogu na swoim dysku. Wpisz polecenie:

SYSTEM dir

lub

\! dir

Polecenie SYSTEM (lub \!) pozwala wykonać rozkaz systemowy dir (ang. directory = katalog), który wyświetli bieżący katalog roboczy. Powinien to być utworzony przez nas katalog c:\sql:

SYSTEM dir
Volume in drive C has no label.
Volume Serial Number is 182A-FCC9

Directory of c:\sql

09.01.2024 10:06 <DIR> .
09.01.2024 10:06 <DIR> ..
09.01.2024 08:57 <DIR> d1
0 File(s) 0 bytes
3 Dir(s) 1 603 732 922 368 bytes free

Do zapamiętania:

Przygotowanie komputera do szybkiego uruchamiania terminala MySQL.
Wykonywanie poleceń systemowych Windows z terminala MySQL przy pomocy rozkazu SYSTEM.

do podrozdziału do strony

Tu i w dalszych podrozdziałach zakładam, iż włączyłeś serwer MySQL w panelu sterowania XAMPP, uruchomiłeś terminal MySQL ze skryptu sql.cmd, który utworzyliśmy na pulpicie i twoim bieżącym katalogiem jest C:\sql.

Baza danych składa się z tabel. Sprawdźmy, jakie tabele zawiera baza, do której jesteśmy załogowani. Wpisz polecenie SQL:

SHOW TABLES;

Polecenie zwraca informację o nazwach tabel w twojej bazie:

SHOW TABLES;
+---------------+
| Tables_in_geo |
+---------------+
| osoba         |
+---------------+

W mojej bazie danych, której nazwa to geo, znajduje się jedna tabela osoba, utworzona na poprzedniej lekcji. Ponieważ nie jest nam już potrzebna tabela osoby, usuwamy ją z bazy danych poleceniem SQL:

DROP TABLE osoba;

i sprawdź:

SHOW TABLES;
Empty set (0.001 sec)

Empty set = zbiór pusty, czyli tutaj oznacza brak tabel w bazie danych.

Teraz utworzymy trzy nowe tabele o następującej strukturze:

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)

Kolor żółty tła oznacza klucz główny, czyli wartość, która dla każdego rekordu tabeli jest inna.

W tabeli kraje klucz główny będzie kolejnym numerem rekordu.

W tabeli jezyki klucz główny będzie obejmował dwa pola: kraj oraz jezyk. Dlaczego tak? Otóż klucz główny musi być unikalny dla każdego rekordu tabeli, a ten warunek spełniają kraj i jezyk wzięte razem – ten sam kraj może mieć kilka różnych języków i ten sam język może być używany w kilku różnych krajach, ale para kraj-język może pojawić się tylko jeden raz.

W tabeli kontynenty kluczem głównym jest pole kod, zawierające 2-literowy kod kontynentu.

Tabele są ze sobą powiązane 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.

Wpisz polecenia SQL (dłuższe polecenia można edytować w edytorze przed wysłaniem ich na terminal SQL. Rozkaz uruchamiający edytor to polecenie EDIT, wprowadzone polecenia należy zapisać przy pomocy Ctrl-S i zamknąć edytor. Inną opcją jest użycie własnego notatnika Windows, w którym wpisujemy polecenie, po czym kopiujemy je do schowka i wklejamy do terminala MySQL.):

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
);

Atrybut NOT NULL wymusza wprowadzenie informacji do danego pola rekordu tabeli. Pole z atrybutem NOT NULL nie może być puste.

Atrybut DEFAULT NULL umieszcza w polu standardowo tekst pusty.

Atrybut DEFAULT wartość liczbowa umieszcza w polu standardowo podaną wartość.

PRIMARY KEY(lista pól tabeli) tworzy klucz główny z podanej listy pól. Oznacza to, iż w tabeli nie może być dwóch rekordów o takiej samej zawartości pól objętych kluczem głównym.

Sprawdź tabele w swojej bazie danych:

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

W następnym kroku wpiszemy dane do tabel. Wpisz EDIT, po czym w edytorze wprowadź polecenie SQL:

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

Zapisz treść edytora (Ctrl+S), zamknij okienko edytora i wpisz znak ; , aby wykonać polecenie. Następnie wpisz

SELECT * FROM kontynenty;

Powinieneś otrzymać wynik:

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.000 sec)

Dane dla pozostałych dwóch tabel kraje i języki są zbyt duże, aby wprowadzać je ręcznie. Dlatego odczytamy je z plików.

Pobierz poniższy plik i zapisz go w katalogu C:\sql\d1 :

jezyki.txt

Wprowadź wpisz polecenie SQL:

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

Polecenie ładuje dane lokalnie (czyli z komputera, na którym pracujesz w terminalu MySQL) dane z pliku jezyki.txt, który znajduje się w katalogu d1 (zwróć uwagę, iż znak rozdzielający katalog od pliku to /, a nie \, jak w Windows) do tabeli jezyki, gdzie pola danych w pliku rozdzielono znakiem przecinka.

Jeśli wszystko wykonałeś poprawnie, to powinieneś otrzymać komunikat:

Query OK, 696 rows affected (0.100 sec)
Records: 696  Deleted: 0  Skipped: 0  Warnings: 0

Wyświetl zawartość tabeli jezyki:

SELECT * FROM jezyki;
+------+-------+
| kraj | jezyk |
+------+-------+
| AD   | ca    |
| AE   | ar-AE |
| AE   | en    |
| ...  | ...   |
| ...  | ...   |
| ...  | ...   |
| ZW   | nd    |
| ZW   | nr    |
| ZW   | sn    |
+------+-------+
696 rows in set (0.001 sec)

W tabeli jest 696 rekordów.

Podobnie wczytamy dane do tabeli kraje. Pobierz poniższy plik do katalogu C:\sql\d1 :

kraje.txt

Wpisz polecenie SQL:

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

Jeśli nie będzie błędów, otrzymasz komunikat:

Query OK, 250 rows affected (0.124 sec)
Records: 250  Deleted: 0  Skipped: 0  Warnings: 0

Tabela kraje zawiera teraz 250 rekordów. Wyświetl jej zawartość:

SELECT * FROM kraje;
+-----+-----+----------------------------------------------+--------+------------+--------------------------------+-----------+
| id  | kod | nazwa                                        | waluta | populacja  | stolica                        | kontynent |
+-----+-----+----------------------------------------------+--------+------------+--------------------------------+-----------+
|   1 | AD  | Andorra                                      | EUR    |      84000 | Andorra la Vella               | EU        |
|   2 | AE  | United Arab Emirates                         | AED    |    4975593 | Abu Dhabi                      | AS        |
|   3 | AF  | Afghanistan                                  | AFN    |   29121286 | Kabul                          | AS        |
...

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

Podsumowanie 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);

Wprowadzenie do tabeli rekordów z pliku

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

do podrozdziału do strony

Wyszukiwanie informacji w pojedynczej tabeli przeprowadzamy za pomocą zapytania:

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

Efektem takiego zapytania jest tabela wyników zawierająca kolumny z wybranej tabeli. W kolumnach są umieszczane te rekordy, w których jest spełniony warunek. Na przykład wyszukajmy wszystkie kraje, w których walutą jest USD (dolar amerykański - United States Dollar). 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.001 sec)

W podobny sposób znajdź kraje korzystające z EUR, PLN, GBP (funt brytyjski - Great Britain Pound).

Wynik zapytania można sortować wg wybranej kolumny rosnąco ASC (ang. ascendingly - rosnąco) lub malejąco DESC (ang. descendingly - malejąco). Jeszcze raz wyświetlmy kraje używające USD, lecz posortujmy je w porządku rosnącym wg pola nazwa:

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.001 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.001 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;
+------------------------+
| Kraj w Europie         |
+------------------------+
| Aland                  |
| Albania                |
| Andorra                |
| ...                    |

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. Kraje będą uporządkowane malejąco wg liczby ludności:

SELECT
    nazwa AS Kraj,
    populacja AS Ludność,
    kontynent AS Kontynent
FROM kraje
WHERE populacja > 100000000
ORDER BY populacja DESC;
+---------------+------------+-----------+
| Kraj          | Ludność    | Kontynent |
+---------------+------------+-----------+
| China         | 1330044000 | AS        |
| India         | 1173108018 | AS        |
| United States |  310232863 | NA        |
| Indonesia     |  242968342 | AS        |
| Brazil        |  201103330 | SA        |
| Pakistan      |  184404791 | AS        |
| Bangladesh    |  156118464 | AS        |
| Nigeria       |  154000000 | AF        |
| Russia        |  140702000 | EU        |
| Japan         |  127288000 | AS        |
| Mexico        |  112468855 | NA        |
+---------------+------------+-----------+

Niestety, w wyniku otrzymaliśmy jedynie kody kontynentów, a nie nazwy kontynentów. 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 pole w kolumnie kontynent tabeli kraje ma tę samą wartość co pole w kolumnie kod tabeli kontynenty:

kraje.kontynent = kontynenty.kod

Na początek wyświetlmy wszystkie kraje z informacją o kontynencie, na którym się znajdują. Kraje uporządkowane alfabetycznie wg ich nazwy:

SELECT
    nazwa AS Kraj,
    kontynenty.kontynent AS Kontynent
FROM kraje, kontynenty
WHERE kraje.kontynent = kontynenty.kod
ORDER BY nazwa;
+----------------------------------------------+---------------+
| Kraj                                         | Kontynent     |
+----------------------------------------------+---------------+
| Afghanistan                                  | Asia          |
| Aland                                        | Europe        |
| Albania                                      | Europe        |
| ...                                          | ...           |

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

Podsumowanie poleceń SQL

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_i {ASC/DESC};

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 OR NOT
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.

Ćwiczenia

W których krajach poza Europą używana jest waluta euro ('EUR')?
Wyszukaj kraje, gdzie nikt nie mieszka.
Wyszukaj kraje, w których używa się języka niemieckiego (de), francuskiego (fr) i odmian angielskiego (LIKE 'en%').
Wyszukaj kraje, które nie leżą w Azji i Afryce.
Wyszukaj kraj niemieckojęzyczny o najmniejszej liczbie ludności?

do podrozdziału do strony

Materiały tylko do użytku dydaktycznego. Ich kopiowanie i powielanie jest dozwolone pod warunkiem podania źródła oraz niepobierania za to pieniędzy.
Pytania proszę przesyłać na adres email: i-lo@eduinf.waw.pl
Serwis wykorzystuje pliki cookies. Jeśli nie chcesz ich otrzymywać, zablokuj je w swojej przeglądarce.

Informacje dodatkowe.

Matura - Internet

SQL - część 2

Przygotowania

Do zapamiętania:

Tworzenie tabel

Podsumowanie poleceń SQL

Tabele w bazie danych

Wprowadzanie do tabeli wielu rekordów

Wprowadzenie do tabeli rekordów z pliku

Wyszukiwanie informacji

Podsumowanie poleceń SQL

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

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

Zmiana nazwy kolumny w wyniku zapytania SELECT

Przydatne operatory w warunkach WHERE

Ćwiczenia