To jest jeden z artykułów w ramach praktycznego kursu SQL. Proszę zapoznaj się z pozostałymi częściami, mogą one być pomocne w zrozumieniu materiału z tego artykułu.
Każde zapytanie z kursu możesz wykonać samodzielnie. Potrzebujesz do tego środowiska opisanego w pierwszym artykule kursu. Bardzo mocno Cię do tego zachęcam. Moim zdaniem najwięcej nauczysz się samodzielnie eksperymentując z zapytaniami.
Wprowadzenie do języka SQL
Język SQL (ang. Structured Query Language) powstał kilkadziesiąt lat temu. Służy do pobierania i przetwarzania danych zapisanych w bazie danych. Język ten został ustandaryzowany i na przestrzeni kilkudziesięciu lat powstało wiele wersji tego standardu.
Niestety treść standardów nie jest dostępna bezpłatnie. Jeśli będziesz chcieć uzupełnić swoją wiedzę, to dokumentacja bazy danych, której używasz jest bardzo dobrym źródłem. Popularne bazy danych dokładnie opisują swoją implementację standardu SQL:
Język SQL jest językiem deklaratywnym. Oznacza to tyle, że instrukcje tego języka opisują co chcemy osiągnąć, a nie jak to zrobić. Dla porównania można powiedzieć, że język Java nie jest językiem deklaratywnym. Programując w języku Java mówisz o tym jak chcesz coś zrobić.
Język SQL oparty jest na zapytaniach. Przykładowe zapytanie SQL może wyglądać tak:
SELECT *
FROM genre
WHERE name = 'Rock'
AND genreid < 20;
SQL to nie baza danych
Definicji bazy danych może być wiele. Jednak nie znam żadnej, która mówiłaby, że baza danych to SQL.
SQL to język, który pomaga dogadać się z bazą danych. Baza danych to dane, to ich zbiór. W relacyjnych bazach danych są one zorganizowane w tabele. W jednej bazie danych przeważnie znajduje się wiele tabel.
Tabele zawierają wiersze i kolumny. Na przykład tabela genre zawiera nazwy gatunków muzycznych:
| genreid | name |
|---------|--------|
| 1 | 'Rock' |
| 2 | 'Jazz' |
Tabela, którą pokazałem wyżej zawiera dwa wiersze i dwie kolumny: genreid i name. Można powiedzieć, że baza danych to zbiór tabel zawierających dane. Język SQL pomaga w łatwym operowaniu na danych. SQL ukrywa w sobie sposób w jaki dane są przetwarzane, zwraca wyłącznie finalny wynik.
Bazy danych także ukrywają sposób przechowywania danych. Użytkownika nie interesuje sposób ich zapisu a jedynie to, co chce uzyskać przy pomocy zapytania SQL1.
Podział SQL
Zapytania w SQL możemy podzielić na kilka rozłącznych grup. Każda z tych grup zawiera różne rodzaje zapytań. Grupy zostały wydzielone na podstawie zadań realizowanych przez poszczególne zapytania. Możemy wyszczególnić następujące grupy:
- DQL (ang. Data Query Language)
- DML (ang. Data Manipulation Language)
- DDL (ang. Data Definition Language)
Dodatkowo czasami wyróżnia się też grupy:
- DCL (ang. Data Control Language)
- TCL (ang. Transaction Control Language)
DQL
DQL składa się wyłącznie z zapytań typu SELECT. Zapytania te służą do odpytywania (ang. query) bazy danych. Innymi słowy służą do pobierania danych z bazy danych. Zapytania typu SELECT są najczęściej używane. Poniżej możesz zobaczyć zapytanie, które pobiera wszystkie kolumny i wiersze z tabeli genre.
SELECT *
FROM genre;
Na razie nie przejmuj się składnią zapytania, omówię ją szczegółowo poniżej.
DML
DML służy do tworzenie, modyfikowania i usuwania danych. W skład tej grupy wchodzą zapytania:
INSERT– dodaje wiersze do tabeli,UPDATE– aktualizuje wiersze w tabeli,DELETE– usuwa wiersze z tabeli.
DDL
Wiesz już, że relacyjne bazy danych składają się z tabel. Dodatkowo w bazach występują inne obiekty jak indeksy (ang. index), klucze obce (ang. foreign key), klucze główne (ang. primary key), ograniczenia (ang. constraint), wyzwalacze (ang. trigger) czy widoki (ang. view). Część języka odpowiedzialna za zarządzanie tymi obiektami to DDL. Zapytania należące do DDL to:
CREATE– tworzą obiekty bazy danych,ALTER– modyfikują tabele bazy danych,DROP– usuwają obiekty bazy danych,TRUNCATE– usuwa wszystkie dane z tabeli2.
DCL
Bazy danych często pozwalają na zarządzanie dostępem do danych. Realizowane jest to przy pomocy kont użytkowników3. DCL służy do manipulacji prawami dostępu do danych przypisanych do poszczególnych kont:
GRANT– nadaje uprawnienia,REVOKE– usuwa uprawnienia.
TCL
Na początku przygody z SQL nie musisz przejmować się transakcjami. Opiszę je dokładniej w kolejnych artykułach w ramach kursu. Teraz w zupełności wystarczy wiedza o tym, że istnieje coś takiego jak transakcja. Do zarządzania transakcjami służą zapytania:
BEGIN– rozpoczyna transakcję,COMMIT– zatwierdza transakcję,ROLLBACK– wycofuje transakcję,SAVEPOINT– zapisuje punkt przywracania aktualnej transakcji.
SQL a wielkość liter
SQL jest językiem, w którym wielkość liter w słowach kluczowych i identyfikatorach nie ma znaczenia. Wyjątkiem są tu identyfikatory, które są otoczone znakiem cudzysłowu "4. Na przykład oba poniższe zapytania są równoważne:
SELECT * FROM genre WHERE genreid = 1;
SELECT * frOM geNRe wherE GenReID = 1;
Chociaż wielkość liter nie ma znaczenia, moim zdaniem dobrą praktyką jest pisanie słów kluczowych wielkimi literami. W codziennej pracy także staram się unikać nadawania nazw, które wymagają otoczenia ". Dodatkowo zawsze staram się formatować zapytania żeby były bardziej czytelne:
SELECT *
FROM genre
WHERE genreid = 1;
Pobierz opracowania zadań z rozmów kwalifikacyjnych
Przygotowałem rozwiązania kilku zadań algorytmicznych z rozmów kwalifikacyjnych. Rozkładam je na czynniki pierwsze i pokazuję różne sposoby ich rozwiązania. Dołącz do grupy ponad 6147 Samouków, którzy jako pierwsi dowiadują się o nowych treściach na blogu, a prześlę je na Twój e-mail.
Przygotowanie środowiska
Moim zdaniem najlepszym sposobem na naukę jest praktyka. Właśnie z tego powodu chcę pomóc przygotować Ci środowisko, w którym możliwe będzie testowanie zapytań.
Aby móc ćwiczyć na bieżąco wszystkie zagadnienia, które będę opisywał będziesz potrzebować serwera bazy danych. Jak wspomniałem w artykule opisującym relacyjne bazy danych jest wiele silników baz danych.
Ze względu na łatwą instalację (właściwie to jej brak), w kursie używał będę bazy danych SQLite. Baza ta jest w zupełności wystarczająca na potrzeby kursu. Oczywiście, jeśli chcesz wykonywać ćwiczenia używając bardziej zaawansowanych baz danych możesz to zrobić ;).
Instalacja bazy danych
Zacznij od pobrania narzędzi SQLite. W zależności od systemu operacyjnego, na którym pracujesz pobierz odpowiednią wersję:
- Windows – Precompiled Binaries for Windows,
- Linux – Precompiled Binaries for Linux,
- Mac OS X – Precompiled Binaries for Mac OS X (x86).
Plik do pobrania to archiwum zip rozpoczynające się od sqlite-tools-. Wewnątrz tego archiwum znajduje się program sqlite.exe (lub sqlite, w zależności od Twojego systemu operacyjnego). Program ten pozwala na pracę z bazą danych SQLite.
Jak widzisz w tym przypadku właściwie nie ma potrzeby instalacji żadnego programu, wystarczy rozpakować archiwum zip. W przypadku baz danych używanych w środowiskach produkcyjnych proces ten jest dużo bardziej skomplikowany.
Import gotowej bazy danych
W internecie istnieje wiele zbiorów danych. Jednym z nich jest ten udostępniony przez projekt Chinook. Jest to testowa baza danych reprezentująca sklep z muzyką. Sama baza nie jest duża, jednak w zupełności wystarczy na omówienie podstawowych możliwości SQL.
Pobierz przykładową bazę danych
i zachowaj ją w pliku Chinook_Sqlite.sqlite, następnie uruchom program sqlite3. Po uruchomieniu wpisz komendę, która otworzy pobraną bazę danych:
.open <ścieżka do pobranego pliku>
Żeby sprawdzić, czy wszystko działa poprawnie możesz wpisać komendę .tables, powinna ona wypisać wszystkie tabele znajdujące się bazie danych.
Komendy zaczynające się od . (na przykład .open czy .tables) to wewnętrzne polecenia SQLite. Jest ich dużo więcej. Jeśli chcesz je zobaczyć użyj polecenia .help.
Zapytania SELECT
Założeniem tego kursu jest to, że będzie on praktyczny od samego początku do końca. Wszystkie zapytania, które tutaj pokazuję możesz wykonać samodzielnie używając środowiska, które wcześniej opisałem.
Schemat tabeli
Zanim przejdę do tłumaczenia zapytań SELECT chciałbym zwrócić Twoją uwagę na budowę tabeli. Wiesz już, że tabela składa się z wierszy i kolumn. Każda kolumna przechowuje dane pewnego typu. Mogą to być na przykład łańcuchy znaków czy liczby.
Można powiedzieć, że tabela ma swój schemat. SQLite ma wewnętrzne polecenie, które pozwala pokazać schemat tabeli – .schema. Na przykład schemat tabeli Invoice wygląda tak:
sqlite> .schema Invoice
CREATE TABLE [Invoice]
(
[InvoiceId] INTEGER NOT NULL,
[CustomerId] INTEGER NOT NULL,
[InvoiceDate] DATETIME NOT NULL,
[BillingAddress] NVARCHAR(70),
[BillingCity] NVARCHAR(40),
[BillingState] NVARCHAR(40),
[BillingCountry] NVARCHAR(40),
[BillingPostalCode] NVARCHAR(10),
[Total] NUMERIC(10,2) NOT NULL,
CONSTRAINT [PK_Invoice] PRIMARY KEY ([InvoiceId]),
FOREIGN KEY ([CustomerId]) REFERENCES [Customer] ([CustomerId])
ON DELETE NO ACTION ON UPDATE NO ACTION
);
CREATE UNIQUE INDEX [IPK_Invoice] ON [Invoice]([InvoiceId]);
CREATE INDEX [IFK_InvoiceCustomerId] ON [Invoice] ([CustomerId]);
To co widzisz, to zapytania typu DDL, które tworzą tabelę i obiekty z nią powiązane. Powyższe zapytana poza tabelą tworzą indeksy, klucze obce i klucz główny.
Istnieją także bazy danych, które pozwalają przechowywać dane w strukturze, która nie ma sztywno określonego schematu. Podobnie jak relacyjne bazy danych mają one swoje wady i zalety.
Typy danych
Typy obsługiwanych danych mogą znacznie różnić się pomiędzy różnymi silnikami baz danych. Różnice te jednak nie przeszkadzają w nauce języka SQL.
Tabela Invoice składa się z dziewięciu kolumn. Kolumna InvoiceId jest kluczem głównym tabeli. Każda z kolumn ma przypisany typ. Typ określa rodzaj danych przechowywanych w danej kolumnie5. Na przykład kolumna InvoiceDate jest typu DATETIME, kolumny tego typu służą do przechowywania daty i czasu.
Innymi typami, które występują w tej tabeli są:
INTEGER– służy on do przechowywania liczb całkowitych,NVARCHAR(x)– służy on do przechowywania łańcuchów znaków do długościx,NUMERIC(x, y)– służy do przechowywania liczb rzeczywistych, które mają doxcyfr zypo przecinku.
Innymi popularnymi typami są6:
BLOB– służy do przechowywania danych binarnych (ang. binary large object),DATETIME– służy do przechowywania daty i czasu,DATE– służy do przechowywania daty,BOOLEAN– służy do przechowywania wartości logicznych,TEXT– służy do przechowywania łańcuchów znaków gdzie ciężko jest oszacować maksymalną długość tekstu, lub wahania długości tekstu są duże.
Składnia zapytania SELECT
Zapytanie SELECT w swojej najprostszej formie wygląda tak:
SELECT *
FROM invoice;
To zapytanie zawiera dwa słowa kluczowe: SELECT i FROM. Pomiędzy tymi słowami znajduje się lista kolumn, które powinny zostać zwrócone. Znak * w tym kontekście oznacza “pobierz wszystkie”. Po słowie kluczowym FROM występuje nazwa tabeli – invoice. Całe zapytanie jest zakończone średnikiem. Spróbuj wykonać to zapytanie na swojej kopii bazy danych:
sqlite> SELECT * FROM invoice;
1|2|2009-01-01 00:00:00|Theodor-Heuss-Straße 34|Stuttgart||Germany|70174|1.98
2|4|2009-01-02 00:00:00|Ullevålsveien 14|Oslo||Norway|0171|3.96
(...)
Filtrowanie przy pomocy WHERE
Pobieranie całej tabeli nie zawsze jest przydatne. Bardzo często zapytania SELECT filtrują pobierane dane. Aby filtrować dane zwracane przez zapytanie musisz użyć słowa kluczowego WHERE i warunków, które filtrują dane:
SELECT *
FROM invoice
WHERE billingcity = 'Dublin' AND total > 5
OR billingcity = 'Boston' AND total < 3 AND total > 1;
Zapytanie wyżej ma kilka warunków. Każdy z nich oddzielony jest słowem kluczowym OR (logiczne lub) i AND (logiczne i). W tym przypadku zostaną zwrócone wszystkie wiersze dla których spełniony jest jeden z warunków:
- kolumna
billingcityma wartość Dublin i kolumnatotalzawiera liczbę większą od 5, - kolumna
billingcityma wartość Boston i kolumnatotalzawiera liczbę z przedziału (1, 3).
Słowo kluczowe AND ma wyższy priorytet niż OR. Oznacza to, że warunki pomiędzy AND wykonywane są wcześniej niż OR. Dla przykładu w konstrukcji:
warunek1 OR warunek2 AND warunek3
Na początku zostanie wykonany fragment warunek2 AND warunek3 dając wynik1, następnie warunek1 OR wynik1. Czasami dla większej czytelności możesz użyć nawiasów. Poniższe zapytanie da dokładnie ten sam wynik co poprzednie:
SELECT *
FROM invoice
WHERE (billingcity = 'Dublin' AND total > 5)
OR (billingcity = 'Boston' AND total < 3 AND total > 1);
Spróbuj wywołać to zapytanie na swojej kopii bazy danych:
sqlite> SELECT * FROM invoice WHERE billingcity = 'Dublin' AND total > 5 OR billingcity = 'Boston' AND total < 3 AND total > 1;
10|46|2009-02-03 00:00:00|3 Chatham Street|Dublin|Dublin|Ireland||5.94
189|23|2011-04-18 00:00:00|69 Salem Street|Boston|MA|USA|2113|1.98
(...)
Magiczna wartość NULL
Każdy wiersz w tabeli może mieć wartość, która pasuje do typu przechowywanego przez daną kolumnę. W SQL jest jeszcze specjalna wartość. Jest nią NULL. Możesz ją porównać do pustej wartości w językach programowania. W Javie i JavaScript byłby to null, w Pythonie None, w Ruby i Go nil itd.
Istotne jest rozróżnienie pomiędzy pustą wartością a NULL. Na przykład pusty łańcuch znaków to zupełnie coś innego niż NULL.
Wartość ta jest traktowana w specyficzny sposób w przypadku porównań. Do sprawdzania czy kolumna w wierszu ma wartość NULL służy wyrażenie IS NULL. Aby sprawdzić, czy dana kolumna nie ma wartości NULL używa się wyrażenia IS NOT NULL.
Na przykład poniższe zapytanie zwróci jedynie te wiersze dla których kolumna billingcountry ma wartość inną niż USA i billingstate nie ma wartości NULL:
SELECT *
FROM invoice
WHERE billingstate IS NOT NULL
AND billingcountry != 'USA';
Wybór kolumn
Użycie klauzuli WHERE pozwala na odfiltrowanie części wierszy. W przypadku kolumn mechanizm jest podobny. Jeśli chcesz wybrać podzbiór kolumn, a nie wszystkie, to musisz wskazać te, które Cię interesują:
SELECT invoicedate
,billingcity
,total
FROM invoice
WHERE billingcountry = 'Poland';
Powyższe zapytanie zwróci wyłącznie te faktury, które zostały wystawione w Polsce. Wynik będzie zawierał tylko trzy wskazane kolumny. Zwróć uwagę, że klauzula WHERE może używać kolumn, które nie są zwracane w wyniku wykonania zapytania.
To dopiero początek
Rysunek poniżej pokazuje składnię zapytania SELECT w SQLite. W artykule tym omówiłem wyłącznie podstawowe elementy.
SELECT
W kolejnych artykułach omówię między innymi pozostałe elementy składni zapytania SELECT.
Zadania do wykonania
Na koniec mam dla Ciebie kilka zadań, w których przećwiczysz materiał z tego artykułu. Postaraj się napisać zapytania samodzielnie, wtedy nauczysz się najwięcej. Żadne z zapytań nie musi zwrócić unikalnych wyników7:
- Napisz zapytanie, które zwróci wszystkie gatunki muzyczne z tabeli
genre, - Napisz zapytanie, które zwróci wszystkie stany w USA, w których wystawiono fakturę na kwotę większą niż 15,
- Napisz zapytanie, które zwróci wszystkie kraje, w których wystawiono fakturę na kwotę mniejszą niż 10 pomiędzy ‘2013-12-05 00:00:00’ i ‘2013-12-09 00:00:00’,
- Napisz zapytanie, które zwróci wszystkie miasta i kraje gdzie wartość kolumny
billingstaterówna sięNULLi wartość zamówienia jest większa niż 17 oraz te gdzie wartość zamówienia jest mniejsza niż 1,billingstatenie ma wartościNULLi zostały wystawione po ‘2013-09-20 00:00:00’.
Przykładowe rozwiązania zadań
SELECT name
FROM genre;
SELECT billingstate
FROM invoice
WHERE billingcountry = 'USA'
AND total > 15;
SELECT billingcountry
FROM invoice
WHERE total < 10
AND invoicedate > '2013-12-05 00:00:00'
AND invoicedate < '2013-12-09 00:00:00';
SELECT billingcity, billingcountry
FROM invoice
WHERE (billingstate IS NULL AND total > 17)
OR (total < 1
AND billingstate IS NOT NULL
AND invoicedate > '2013-09-20 00:00:00');
Dodatkowe materiały do nauki
Jeśli chcesz spojrzeć na temat z innej perspektywy polecam przeczytanie poniższych materiałów. Pozwoli Ci to poszerzyć swoją wiedzę związaną z językiem SQL i jego składnią.
Podsumowanie
Po przeczytaniu tego artykułu wiesz czym jest język SQL. Potrafisz podzielić zapytania języka SQL na grupy. Znasz podstawy zapytania typu SELECT. Potrafisz zastosować w praktyce zapytania tego typu do pobrania danych z bazy. Innymi słowy masz solidne podstawy, dzięki którym możesz przejść do kolejnego etapu nauki języka SQL.
Przyznam Ci się, że miałem problem z zakończeniem tego artykułu. Mam świadomość, że nie jest kompletny, ale postanowiłem podzielić go na mniejsze, łatwiejsze do przyswojenia części. W kolejnych artykułach z cyklu możesz spodziewać się pogłębienia tematu.
Na koniec proszę Cię o polecenie tego artykułu Twoim znajomym, którym może się on przydać. Dzięki Tobie uda mi się dotrzeć do nowych czytelników. Z góry dziękuję ;). Jeśli cokolwiek nie będzie dla Ciebie jasne proszę daj znać w komentarzach, postaram się pomóc. Jeśli nie chcesz pominąć kolejnych artykułów na blogu proszę polub Samouczka na Facebooku i dodaj swój adres e-mail do samouczkowego newslettera. Do następnego razu!
-
Na pewnym etapie zaawansowania znajomość wewnętrznych mechanizmów działania bazy danych jest bardzo ważna. Pozwala ona na tworzenie zapytań, które są bardziej wydajne. ↩
-
Chociaż
TRUNCATEjest podobne do zapytania typuDELETEjest klasyfikowane jako DDL. Wynika to z faktu, że zapytaniaTRUNCATEnie mogą być cofnięte. Zapytania typuDELETEmogą być cofnięte w ramach trwającej transakcji. ↩ -
Pomijam tu ustawienia na poziomie konfiguracji silnika bazy danych. Te ustawienia mogą wymagać restartu silnika. Przykładem może tu być plik konfiguracyjny
pg_hba.confistniejący w bazie danych PostgreSQL. ↩ -
To zachowanie zależy od silnika bazy danych. Niektóre silniki inaczej interpretują identyfikatory otoczone
", inne nie. ↩ -
To stwierdzenie nie jest do końca prawdziwe dla SQLite, jednak ma zastosowanie w innych silnikach baz danych. W przypadku SQLite typ danych określany jest na podstawie zawartości a nie typu kolumny. To zachowanie jest raczej wyjątkiem w świecie relacyjnych baz danych. Po szczegóły odsyłam Cię do dokumentacji SQLite. ↩
-
W przypadku SQLite wszystkie typy danych są przekształcane na “typy pierwotne”:
NULL,INTEGER,REAL,TEXT,BLOB. Dane na dysku zawsze zapisane są jako jeden z typów pierwotnych. ↩ -
Mechanizmy, które pozwalają na zwracanie unikalnych wyników omówię w kolejnych artykułach. ↩
Pobierz opracowania zadań z rozmów kwalifikacyjnych
Przygotowałem rozwiązania kilku zadań algorytmicznych z rozmów kwalifikacyjnych. Rozkładam je na czynniki pierwsze i pokazuję różne sposoby ich rozwiązania. Dołącz do grupy ponad 6147 Samouków, którzy jako pierwsi dowiadują się o nowych treściach na blogu, a prześlę je na Twój e-mail.
Zostaw komentarz