🗃️ SQL - Podstawy

Język SQL (Structured Query Language) jest deklaratywnym jęzkiem zapytań. Umożliwia tworzenie, modyfikowanie oraz ogólne zarządzanie relacyjnymi bazami danych. Warto zaznaczyć, że większość silników baz danych wspiera podstawowe (określone w standardzie sql) polecenia.

Dostępne w standardzie języka SQL komendy/zapytania, można podzielić ze względu na ich działanie:

1. DDL (Data Definition Language) - zestaw poleceń przeznaczonych do manipulowania całymi relacjami (w kontekście relacyjnych baz danych relacja = tabela), bazami czy schemami (jeśli silnik bazodanowy je wspiera):
   1. CREATE - umożliwia utworzenie struktury np.: bazy, schemy, tabeli, indeksu, użytkownika, widoku itd.,
   2. ALTER - umożliwia aktualizację struktury (np. zmiana typu kolumny; dodanie bądź usunięcie kolumny),
   3. DROP - umożliwia usunięcie struktury,
   4. TRUNCATE - umożliwia szybkie usunięcie struktury z pominięciem ograniczeń zdefiniowanych w strukturze, np.
   • usuwa dane ale pozostawia indeksy,
   • usuwa dane ale bez zapewnienia integralności zdefininiowanej w ograniczeniach nakładanych na tabelę.
2. DML (Data Manipulation Language) - zestaw poleceń umożliwiających dokonywanie wstawiania, aktualizacji oraz usuwania poszczególnych danych (głównie skierowane do manipulowania krotkami; krotka w kontekście relacyjnych baz danych to wiersz tabeli):
   1. INSERT - umożliwia wstawianie nowych rekordów,
   2. UPDATE - umożliwia aktualizację rekordów,
   3. DELETE - umożliwia usuwanie rekordów.
3. DCL (Data Control Language) - zestaw poleceń umożliwiający nadawanie uprawnień do poszczególnych struktur istniejących w bazie danych:
   1. GRANT - służy do nadawania uprawnień do struktur poszczególnym użytkownikom,
   2. REVOKE - służy do odbierania wybranych uprawnień użytkownikowi,
   3. DENY - służy do zabraniania wykonywania wybranych operacji,
4. DQL (Data Query Language) - język zapytań używany do pobierania danych z bazy danych:
   1. SELECT - pozwala na zadawanie zapytań do bazy danych w celu pobrania konkretnych danych (wierszy/krotek) z jednej lub więcej tabel (tabele w ramach zapytań można łączyć), zgodnie z podanymi warunkami.
5. TCL (Transaction Control Language) - zestaw poleceń służących do zarządzania transakcjami w bazie danych. Pozwala na zatwierdzanie, wycofywanie oraz kontrolowanie punktów przywracania w ramach transakcji. Główne polecenia to:
   1. COMMIT - zatwierdza wszystkie zmiany dokonane w ramach transakcji i zapisuje je na stałe w bazie danych.
   2. ROLLBACK - cofa zmiany dokonane w ramach bieżącej transakcji do ostatniego punktu zapisu (SAVEPOINT) lub do początku transakcji.
   3. SAVEPOINT - ustawia punkt przywracania w transakcji, do którego można później cofnąć się za pomocą ROLLBACK.

Składnia SELECT (DQL)

Można pokusić się o stwierdzenie, że najczęściej stowowanym rodzajem zapytania jest SELECT (w teorii relacyjnych baz danych jest to operacja projekcji).

Składnie tej sekcji przestawiają poniższe przykłady (ciągiem znaków -- tworzy się komentarze w SQL):

-- Pobierz wszystkie dostepne kolumny i dane z tabeli
SELECT *
FROM <NAZWA_TABELI>;

-- Pobierz wszystkie unikalne wiersze, wybierając do wyniku tylko
-- ATRYBUT_0 oraz ATRYBUT_1 zmieniając ich wynikowe nazwy kolumn
SELECT DISTINCT <ATRYBUT_0> AS imie, <ATRYBUT_1> AS nazwisko
FROM <NAZWA_TABELI>;

-- Pobierz wszystkie dane zwrócone w ramach podzapytania
SELECT * 
FROM (SELECT <ATRYBUT_0> FROM <NAZWA_TABELI>)

Funkcje agregujące

W ramach sekcji SELECT można zastosować również funkcje agregujące, które umożliwiają dokonywanie prostych operacji na liczbach. Wyróżniamy funkcje:

• MIN(<ATRYBUT>) - znajduje minimalną wartość w kolumnie
• MAX(<ATRYBUT>) - znajduje maksymalną wartość w kolumnie
• COUNT(<ATRYBUT>) - zwraca liczebność danych w kolumnie
• AVG(<ATRYBUT>) - oblicza śrędnią arytmetyczną w kolumnie
• SUM(<ATRYBUT>) - oblicza sumę wartości kolumny

SELECT MIN(<ATRYBUT_2> AS minimum)
FROM <NAZWA_TABELI>

TIP

Warto zaznaczyć, że funkcje agregujące ignorują wartości NULL. Wyjątkiem jest funkcja COUNT(), która w momencie wywołania COUNT(*) zwraca liczbę wszystkich wierszy razem z NULL. Natomiast gdy podamy zliczanie po konkretnych atrybutach COUNT(<ATRYBUT>) to wartości NULL zostaną pominięte.

Sekcja FROM

Umożliwia wskazanie źródła, z którego będą pobierane informacje. W tej sekcji można również dokonywać złączeń różnych tabel za pomocą słowa kluczowego JOIN. Wyróżnia się kilka rodzajów złączeń:

Rodzaj JOIN	Graficzne przedstawienie	Co zwraca
(INNER) JOIN	A ◯─────◉─────◯ B	Tylko wspólne rekordy z A i B (przecięcie)
LEFT (OUTER) JOIN	A ◉─────◉─────◯ B	Wszystkie rekordy z A + dopasowane z B
RIGHT (OUTER) JOIN	A ◯─────◉─────◉ B	Wszystkie rekordy z B + dopasowane z A
FULL (OUTER) JOIN	A ◉─────◉─────◉ B	Wszystkie rekordy z A i B

gdzie:

• A, B – tabele, które łączymy
• ◯ – dane nie zawierające się w wyniku złączenia
• ◉ – część wspólna / połączona

-- założenia:
-- A.id - to klucz główny tabeli A
-- B.a_id - to klucz obcy do tabeli A z B

-- INNER JOIN
SELECT *
FROM A
INNER JOIN B ON A.id = B.a_id;

-- LEFT JOIN
SELECT *
FROM A
LEFT JOIN B ON A.id = B.a_id;

-- RIGHT JOIN
SELECT *
FROM A
RIGHT JOIN B ON A.id = B.a_id;

-- FULL OUTER JOIN
SELECT *
FROM A
FULL JOIN B ON A.id = B.a_id;

INFO

Istnieje również starsza wersja składni, w której złączeń tabel dokonywało się w sekcji WHERE, ale jest ona uważana przez wielu twórców silników baz danych jako przestarzała i należy jej unikać.

Sekcja ORDER BY

Umożliwia sortowanie wynikowych rekordów zapytania po wskazanym atrybucie. Sortować można rosnąco ASC (domyślnie) lub malejąco DESC. Słowo kluczowe ORDER BY należy umieszczać na końcu zapytania.

-- Posortuj wyniki rosnąco względem <ATRYBUT_1>
SELECT * 
FROM <NAZWA_TABELI>
ORDER BY <ATRYBUT_1>;

-- Posrotuj wyniki malejąco względem <ATRYBUT_1>
SELECT * 
FROM <NAZWA_TABELI>
ORDER BY <ATRYBUT_1> DESC;

Sekcja ORDER BY umożliwia również sortowanie po wielu kolumnach. Takie sortowanie polega na tym, że najpierw elementy sortowane są po pierwszej z wybranych kolumn i jeśli w pierwszym sortowaniu jakieś elementy są sobie równe to są kolejno sortowane przez następną kolumnę wymienioną w sekcji.

-- Posortuj wyniki rosnąco względem <ATRYBUT_1> i <ATRYBUT_2>
SELECT * 
FROM <NAZWA_TABELI>
ORDER BY <ATRYBUT_1>, <ATRYBUT_2> ASC;

Sekcja WHERE

Aby wstepnie odfiltrować dane należy dodać słowo kluczowe WHERE <WARUNEK> do zapytania SELECT. W ramach warunku dostepne są następujące operatory:

• = - równa się
• > - większy od
• < - mniejszy od
• >= - większy równy od
• <= - mniejszy równy od
• <> lub != - różny od
• BETWEEN - pomiędzy jakimiś wartościami (zakres)
• IN - aby określić czy wartość należy do zbioru
• ANY - porównuje wartość z dowolnym elementem podzapytania. Zwróci TRUE którykolwiek z wyników podzapytania spełnia warunek
• ALL - porównuje wartość ze wszystkimi elementami podzapytania. Zwróci TRUE jeśli warunek zostanie spełniony dla wszystkich elementów podzapytania
• EXISTS - służy do sprawdzania istnienia rekordu w podzapytaniu
• LIKE - szuka wzorca w ciągu znaków

-- Ogólna składnia WHERE
SELECT *
FROM <NAZWA_TABELI>
WHERE <WARUNEK>;

-- Warunki można łączyć operatorami logicznymi
SELECT *
FROM <NAZWA_TABELI>
WHERE <WARUNEK_0> 
AND <WARUNEK_1>;

SELECT *
FROM <NAZWA_TABELI>
WHERE <WARUNEK_0> 
AND (<WARUNEK_1> OR <WARUNEK_2);

---

Operator LIKE wymaga szczególnej, uwagi ponieważ umożliwia bardzo praktyczny i łatwy w użyciu mechanizm dopasowywania ciągu znaków do wzorca. Służą do tego tak zwane Wildcharacters/Wildcards:

• % lub * - reprezentuje 0 lub więcej znaków
• _ lub ? - reprezentuje pojedynczy znak
• [<ZESTAW_ZNAKOW>] - reprezentuje pojedynczy znak ze zdefiniowanego zestawu
  • ^ lub ! - reprezentuje każdy znak, który nie jewt zdefiniowany w zestawie np. +[!0]
  • - - reprezentuje zakres znaków np. [a-z]

Przykłady zastosowania %, _ oraz []:

• WHERE napis LIKE 'a%' - znajduje wszystkie napisy zaczynające się od a
• WHERE napis LIKE '%a' - znajduje wszystkie napisy kończące się na a
• WHERE napis LIKE '%a%' - znajduje wszystkie napisy zawierające literę a
• WHERE napis LIKE '_a%' - znajduje wszystkie napisy z literą z literą a na 2 miejscu w ciągu
• WHERE napis LIKE 'a%z' - znajduje wszystkie napisy zaczynajce się od a i kończące się na z
• WHERE napis NOT LIKE 'a%' - znajduje napisy nie zaczynające się na a
• WHERE napis NOT LIKE '[abc]%z' - znajduje wszystkie napisy rozpoczynające się na a lub b lub c i kończące sie na z

Warto również wspomnieć, że w sekcji WHERE można odfiltrowywać rekordy sprawdzając wartość NULL, ale trzeba użyć do tego słowa kluczowego IS

-- Pobierz wszystkie rekordy, których wartość dla atrybutu_0 jest różna od NULL
SELECT *
FROM <NAZWA_TABELI>
WHERE <ATRYBUT_0> IS NOT NULL;

---

Operator BETWEEN pozwala na uzyskiwanie rekordów których wartości mieszczą się w jakiś przedziałach (np. przedziały: liczbowe, dat czy czasu)

SELECT *
FROM <NAZWA_TABELI>
WHERE CREATED_AT BETWEEN DATE_1 AND DATE_2;

---

Operatory IN, ALL, ESIXTS i ANY wykorzystywane są zwykle przy podzapytaniach, ale mogą być również stosowane w ramach sprawdzenia czy, któraś z wartości mieści się w stale zdefiniowanym zbiorze.

-- Operator IN jako alternatywa dla warunku OR
-- wartości: STALA_0, STALA_1, STALA_2 - są zależne od typu <ATRYBUT_1>
SELECT *
FROM <NAZWA_TABELI>
WHERE <ATRYBUT_1> IN (STALA_0, STALA_1, STALA_2);

-- Zwraca wszystkie rekordy, których wartość <ATRYBUT_1> 
-- mieści się w zbiorze zwróconym przez podzapytanie
SELECT *
FROM <NAZWA_TABELI_0>
WHERE <ATRYBUT_1> IN (SELECT <ATRYBUT_1>
                      FROM <NAZWA_TABELI_1>
                      WHERE <ATRYBUT_2> BETWEEN VAL_0 AND VAL_1);

-- Zwraca prawdę jeżeli istnieje jakakolwiek wartość spełniająca warunek
-- operator może przyjąć postać: =, <>, !=, >, >=, <, <=
SELECT *
FROM <NAZWA_TABELI_0>
WHERE <ATRYBUT_1> operator ANY (SELECT <ATRYBUT_1>
                                FROM <NAZWA_TABELI_1>
                                WHERE <ATRYBUT_2> BETWEEN VAL_0 AND VAL_1);

-- Zwraca prawdę jeżeli każda wartość podzapytania spełnia warunek
-- operator przyjmuje analogiczne postacie jak dla ANY
SELECT *
FROM <NAZWA_TABELI_0>
WHERE <ATRYBUT_1> operator ALL (SELECT <ATRYBUT_1>
                                FROM <NAZWA_TABELI_1>
                                WHERE <ATRYBUT_2> BETWEEN VAL_0 AND VAL_1);

-- Zwraca prawdę jeżeli podzapytanie zwróci jeden lub więcej rekordów.
-- W tym przypadku sprawdzamy czy w TABELA_1 istnieją rekordy, które posiadają
-- klucz obcy ATRYBUT_2 TABELA_0_ID powiązany z kluczem głównym TABELA_0.ID i 
-- wartości TABELA_1.ATRYBUT_2 są mniejsze niż zadany limit VAL_1
SELECT *
FROM TABELA_0
WHERE EXISTS (SELECT ATRYBUT_1
              FROM TABELA_1
              WHERE TABELA_1.TABELA_0_ID = TABELA_0.ID
              AND ATRYBUT_2 < VAL_1);

Sekcja GROUP BY

Umożliwia grupowanie odfiltrowanych wyników względem wskazanego w zapytaniu atrybutu. Jest to bardzo pomocna sekcja w kontekście zapytań statystycznych. Sekcja pozwala na stosowanie funkcji agregujących w obrębie pogrupowanych danych.

-- Ogólna składania
SELECT *
FROM <NAZWA_TABELI>
WHERE <WARUNEK>
GROUP BY <ATRYBUT>;

-- Zakładając, że mamy tabelę users zawierającą atrybuty:
-- id, email, country ...
-- Zapytanie zwróci statystyki liczebności użytkowników z 
-- podziałem na kraje pochodzenia. Wynik będzie posortowany od
-- największej liczności do najmniejszej
SELECT country, COUNT(country)
FROM users
WHERE country IS NOT NULL
AND email IS NOT NULL
GROUP BY country
ORDER BY COUNT(id) DESC;

Sekcja HAVING

Jest to druga sekcja filtrowania. Zostało dodane, ponieważ WHERE nie umożliwia bezpośredniej pracy z funkcjami agregującymi. To filtrowanie wystepuje po sekcji grupowania.

-- Ogólna składania
SELECT *
FROM <NAZWA_TABELI>
WHERE <WARUNEK_0>
GROUP BY <ATRYBUT>
HAVING <WARUNEK_1>

-- Zakładając, że mamy tabelę users zawierającą atrybuty:
-- id, email, country ...
-- Zapytanie zwróci statystyki liczebności użytkowników z 
-- podziałem na kraje pochodzenia, gdzie liczba użytkowników
-- jest większa niż 1000. Wynik będzie posortowany od
-- największej liczności do najmniejszej
SELECT country, COUNT(country)
FROM users
WHERE country IS NOT NULL
AND email IS NOT NULL
GROUP BY country
HAVING COUNT(id) > 1000
ORDER BY COUNT(id) DESC;

Składnia CREATE (DDL)

Do tworzenia nowych tabel/relacji, baz, schem, widoków, funkcji, indeksów itd. w bazie danych służy komenda CREATE.

-- Ogólna postać komendy
CREATE TABLE <NAZWA_TABELI>
(
   <ATRYBUT_0> <TYP>,
   <ATRYBUT_1> <TYP>,
   -- ...
);

-- Przykład utworzenia relacji user
CREATE TABLE user
(
   id          UUID,
   age         INT,
   email       VARCHAR(256),
   firstName   VARCHAR(64),
   lastName    VARCHAR(64)
);

-- Przykład utworzenia indeksu user_age_idx
CREATE INDEX user_age_idx
ON user (user_age_idx);

-- Przykład utworzenia indeksu złożonego
CREATE INDEX user_full_name
ON user (firstName, lastName);

-- Przykład utworzenia widoku
CREATE VIEW user_view AS
SELECT id, email
FROM user
WHERE email IS NOT NULL;

WARNING

Należy pamiętać o tym, że nie wszystkie silniki bazodanowe wspierają te same typy danych. W zależności od wyboru dostawcy bazy danych dostepne typy będą różniły się nie tylko dostepnością, implementacją ale również nazewnictwem.

Polecenie CREATE TABLE można użyć razem z SELECT

CREATE TABLE select_result AS
SELECT <ATRYBUT_1>
FROM <NAZWA_TABELI>
WHERE <WARUNEK>;

Składnia ALTER

Polecenie ALTER TABLE umożliwia dodawanie, usuwanie albo modyfikowanie kolumn w istniejącej tabeli. Za jej pomocą można również nałożyć albo usunąć ograniczenia.

-- Przykład dodawania nowej kolumny do tabeli 
ALTER TABLE <NAZWA_TABELI>
ADD <ATRYBUT_NOWY> <TYP>;

-- Przykład usuwania kolumny do tabeli
ALTER TABLE <NAZWA_TABELI>
DROP COLUMN <ATRYBUT_2>;

-- Przykład modyfikacji kolumny
-- w zależności od silnika MODIFY = MODIFY COLUMN = ALTER COLUMN
ALTER TABLE <NAZWA_TABELI>
MODIFY COLUMN <ATRYBUT_2> INT;

Składnia DROP i TRUNCATE

Za pomocą DROP możemy usunąć bazę danych, schemę (jeśli jest wspierana) lub tabelę.

-- usunięcie bazy danych
DROP DATABASE <NAZWA_BAZY>;

-- usunięcie tabeli
DROP TABLE <NAZWA_TABELI>;

-- usunięcie tabeli z pominięciem ograniczeń
-- usunięte zostaną same wiersze/krotki
TRUNCATE TABLE <NAZWA_TABELI>;

Nakładanie ograniczeń

Ograniczenia (Constraints) pozwalają nakładać dodatkowe ograniczenia na relacje.

-- Nakładanie ograniczeń w ramach tworzenia nowej tabeli
CREATE TABLE <NAZWA_TABELI>
(
   <ATRYBUT_0> <TYP> <CONSTRAINT>,
   <ATRYBUT_1> <TYP> <CONSTRAINT>,
   -- ...
);

-- Nakładanie ograniczeń w ramach aktualizacji istniejącej tabeli
ALTER TABLE <NAZWA_TABELI>
ADD <CONSTRAINT>

Lista wybranych ograniczeń (<CONSTRAINT>):

• NOT NULL - zapewnia, że wartości nie będą przyjmowały NULL
• UNIQUE - zapewnia unikalność wartości w kolumnie
• PRIMARY KEY - klucz główny, połączenie NOT NULL i UNIQUE
• FOREIGN KEY REFERENCES <TABELA>(<ATRYBUT>) - klucz obcy
  • ON <UPDATE | DELETE> <ACTION> - dalsza część ograniczenia umożliwiająca określenie polityki zachowania integralności referencyjnej, gdzie <ACTION> może przyjmować:
    • CASCADE - kaskadowo aktualizuje podrzędne tabele. Dla DELETE wszystkie podrzędne rekordy zostaną usunięte. Dla UPDATE wszystie wartości zostaną zaktualizowane na wzór rodzica.
    • RESTRICT - jeśli istnieje zależność pomiędzy tabelami (dziecko-rodzic) to operacja usuwania lub aktualizacji rodzica może zostać zablokowana
    • NO ACTION
    • SET NULL - przy usunięciu/aktualizacji tabeli nadrzędnej ustaw wartość klucza na NULL
    • SET DEFAULT - przy usunięciu/aktualizacji tabeli nadrzędnej ustaw wartość klucza na wartość domyślną kolumny
• CHECK - zapewnia, że wartości w kolumnie spełniają zdefiniowany przez użytkownika warunek
• DEFAULT - ustawia wartość domyślną
• CREATE INDEX - służy do tworzenia indeksów

-- Nakładanie PRIMARY KEY
CREATE TABLE user
(
   id INT PRIMARY KEY,
   -- ...
);

-- alternatywnie (po utworzeniu tabeli bez ograniczeń)
ALTER TABLE user
ADD PRIMARY KEY(id);

-- Nakładanie FOREIGN KEY
CREATE TABLE user_car
(
   userId INT FOREIGN KEY REFERENCES user(id),
   -- ...
);

-- alternatywnie
ALTER TABLE user_car
ADD FOREIGN KEY(id) REFERENCES user(id);

-- Nakładanie CHECK i DEFAULT
CREATE TABLE user
(
   id INT PRIMARY KEY,
   age INT NOT NULL CHECK (age >= 18),
   country VARCHAR(64) DEFAULT 'Poland',
   -- ...
);

-- alternatywnie
ALTER TABLE user
ADD CHECK (age >= 18);

ALTER TABLE user
MODIFY country SET DEFAULT 'Poland';

Składnia DML

Do wstawiania nowych wierszy służy polecenie INSERT INTO. Natomiast do kopiowania danych z jednej tabeli i wstawiania do drugiej słuzy INSERT INTO SELECT.

-- Ogólna postać
INSERT INTO user
VALUES (<VAL_1>, <VAL_2> ...);

-- Kopiowanie danych z jednej tabeli do drugiej - ogólna postać
INSERT INTO <NAZWA_TABELI>
SELECT * FROM user
WHERE country LIKE '[!Pp]%';

Do usuwania i aktualizacji wykorzystujemy polecenia DELETE i UPDATE.

DELETE FROM user
WHERE lastName = 'Dolas';

UPDATE user
SET firstName = 'Franciszek'
WHERE lastName = 'Dolas';

Uprawnienia (DCL)

Aby dodać nowego użytkownika do bazy danych należy wykorzystać polecenie CREATE USER a nastepnie nadać mu uprawnienia do wybranych struktur poprzez komendę GRANT.

-- Polecenia tworzenia użytkowników mogą lekko różnić się 
-- w zależności od zastosowanej bazy danych
CREATE USER webapi_test IDENTIFIED BY '<HASŁO>';

-- Ogólna postać przyznawania uprawnień
GRANT <UPRAWNIENIE_0>, <UPRAWNIENIE_1>, ... 
ON <NAZWA_TABELI>
TO <UŻYTKOWNIK_1>, <UŻYTKOWNIK_2>, ...
WITH GRANT OPTION; -- umożliwia dalsze przekazywanie uprawnień

-- Przyznaj uprawnienia wykonywania zapytań SELECT na tabeli user 
GRANT SELECT ON user TO webapi_test;

TIP

Tworzenie użytkwników z mniejszym zakresem uprawień niż administrator to bardzo dobra praktyka, mogąca uratować naszą bazę w przypadku ataku hackerskiego. Taki zabieg może zabezpiecyzć nas przed utradą danych lub przed nieautoryzowanym wglądem w dane wrażliwe.