SQL (Structured Query Language) je jazyk pro práci s relačními databázemi. Jeho hlavní výhodou je jednoduchost, čitelnost a možnost provádět složité operace nad (strukturovanými) daty.
SQL je standardizovaný jazyk, ale každý databázový systém má své specifické rozšíření, vlastnosti a nadstavby.
Hlavními koncepty SQL jsou:
Nastartuje si nový docker workspace přes Coder
Vytvořte si nový adresář pro tento workshop
mkdir sql-workshop
cd sql-workshop
A vytvořte si compose.yml soubor
vim compose.yml
services:
database:
image: mysql:9.2.0
ports:
- '3306:3306'
environment:
MYSQL_ROOT_PASSWORD: root
MYSQL_DATABASE: workshop
MYSQL_USER: user
MYSQL_PASSWORD: password
volumes:
- database:/var/lib/mysql
adminer:
image: adminer:4.17.1
ports:
- 8080:8080
environment:
ADMINER_DEFAULT_SERVER: database
depends_on:
- database
phpmyadmin:
image: phpmyadmin:5.2.2
ports:
- 8081:80
environment:
PMA_HOST: database
depends_on:
- database
volumes:
database:
Spusťte si pomocí docker compose databázový server a administrační rozhraní
docker compose up --detach
Přes Coder si můžete otevřít port
8080 pro adminer8081 pro phpmyadminObě rozhraní slouží jako grafické nástroje pro správu databází, ale mají trochu jiný vzhled a funkce. Pro účely tohoto workshopu si můžete vybrat, které chcete použít.
Tento workshop bude používat adminer.
Přihlašovací údaje pro databázi jsou:
database3306userpasswordS databázi lze interagovat i přes příkazovou řádku, ale pro začátek je lepší použít grafické rozhraní
Příkaz pro připojení k databázi přes příkazovou řádku
docker compose exec database mysql -u user -ppassword workshop
Při práci s jakýmkoliv databázovým systémem je nutné pracovat v kontextu databáze. Databázové servery totiž mohou obsahovat více databází, každou s vlastními tabulkami a daty.
Přepněte se do databáze workshop pomocí rolovacího menu vlevo nahoře s názvem DB:.
Pomocí záložky SQL příkaz v admineru si vytvořte hlavní vytvořte tabulku s názvem movies.
CREATE TABLE movies (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
year INT NOT NULL
);
Tento příkaz vytvoří tabulku movies s třemi sloupci:
id - primární klíč, který se automaticky inkrementuje, jedná se o INT, tedy celé číslotitle - název filmu, povinný, řetězec s maximální délkou 255 znakůyear - rok vydání filmu, povinný, celé čísloDále si do databáze vložte několik záznamů
INSERT INTO movies (title, year) VALUES
('The Shawshank Redemption', 1994),
('The Godfather', 1972),
('The Dark Knight', 2008),
('The Lord of the Rings: The Return of the King', 2003),
('Pulp Fiction', 1994),
('Forrest Gump', 1994),
('Inception', 2010),
('The Matrix', 1999),
('The Silence of the Lambs', 1991),
('The Green Mile', 1999),
('The Lord of the Rings: The Fellowship of the Ring', 2001),
('The Lord of the Rings: The Two Towers', 2002),
('The Lord of the Rings: The Return of the King', 2003),
('The Hobbit: An Unexpected Journey', 2012),
('The Hobbit: The Desolation of Smaug', 2013),
('The Hobbit: The Battle of the Five Armies', 2014),
('The Lion King', 1994);
Použijte grafické rozhraní pro zjištění počtu záznamů v tabulce movies.
34
SELECT je základní příkaz pro získání dat z tabulky.
Většinou se používá ve spojení s dalšími příkazy, jako je WHERE, ORDER BY, GROUP BY, HAVING, JOIN, atd.
Základní syntaxe SELECT příkazu je
SELECT column1, column2, ... FROM table_name;
Pokud chcete získat všechny sloupce z tabulky, můžete použít *
SELECT * FROM table_name;
Pomocí SELECT příkazu získejte všechny záznamy z tabulky movies.
SELECT * FROM movies;
Pomocí SELECT příkazu získejte názvy filmů od nejnovějšího po nejstarší.
SELECT title FROM movies ORDER BY year DESC;
Pomocí SELECT příkazu vyhledejte 5 nejstarších filmů.
SELECT * FROM movies ORDER BY year ASC LIMIT 5;
Pomocí SELECT příkazu získejte názvy filmů, které neobsahují slovo The v názvu.
SELECT title FROM movies WHERE title NOT LIKE '%The%';
Ke každému roku v tabulce vypište počet filmů, které byly vydány v daném roce.
SELECT year, COUNT(*) FROM movies GROUP BY year;
JOIN je příkaz, který slouží k spojení dvou tabulek na základě společného identifikátoru.
Existují různé typy JOIN, nejčastější jsou:
INNER JOIN - vrátí pouze záznamy, které mají shodný identifikátor v obou tabulkáchLEFT JOIN - vrátí všechny záznamy z levé tabulky a záznamy z pravé tabulky, které mají shodný identifikátorRIGHT JOIN - vrátí všechny záznamy z pravé tabulky a záznamy z levé tabulky, které mají shodný identifikátorFULL JOIN - vrátí všechny záznamy z obou tabulekTaké klasifikujeme následující vazby mezi tabulkami:
1:1 - jeden k jednomu1:n - jeden k mnohan:m - mnoho k mnohaTyto vazby se modelují pomocí cizích klíčů.
U vazby 1:1 jedna tabulka obsahuje primární klíč a druhá tabulka obsahuje cizí klíč, který je ale zároveň také primárním klíčem.
Příklad je například tabulka users a profiles, kde každý uživatel má pouze jeden záznam v tabulce profiles, kde například ukládáme dodatkové informace o uživateli.
U vazby 1:n jedna tabulka obsahuje primární klíč a druhá tabulka obsahuje cizí klíč, který odkazuje na primární klíč.
Rozdíl oproti 1:1 je, že druhá tabulka má svůj vlastní primární klíč a sloupeček s cizím klíčem nemusí být unikátní.
Příklad je například tabulka users a posts, kde každý uživatel může mít více příspěvků, ale příspěvek je napojen pouze jedním uživatelem.
Pro vazbu typu n:m se používá spojovací tabulka, která obsahuje dvojice cizích klíčů, které odkazují na primární klíče obou tabulek.
Příklad je například tabulka users a roles, kde každý uživatel může mít více rolí a každá role může být přiřazena více uživatelům.
Vytvořme si další tabulky users a ratings.
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL
);
CREATE TABLE ratings (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
movie_id INT NOT NULL,
rating INT NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (movie_id) REFERENCES movies(id),
UNIQUE (user_id, movie_id)
);
Najděte v grafickém rozhraní rozložení tabulek a prohlédněte si vytvořené vzájemné relace (pomocí cizích klíčů).
Přidali jsme také
UNIQUEomezení, které zaručí, že každý uživatel může hodnotit každý film pouze jednou.
Vložte několik záznamů do tabulek users a ratings.
INSERT INTO users (name) VALUES
('Alice'),
('Bob'),
('Charlie'),
('David'),
('Eve');
INSERT INTO ratings (user_id, movie_id, rating) VALUES
(1, 1, 10), (1, 2, 9), (1, 3, 8), (1, 4, 7), (1, 5, 6),
(2, 1, 5), (2, 2, 6), (2, 3, 7), (2, 4, 8), (2, 5, 9), (2, 6, 10), (2, 7, 1), (2, 8, 2),
(3, 1, 9),
(5, 1, 10), (5, 2, 9), (5, 3, 8), (5, 4, 7), (5, 5, 6);
Pomocí JOIN příkazu získejte průměrné hodnocení filmů.
SELECT title, AVG(rating) FROM movies
JOIN ratings ON movies.id = ratings.movie_id
GROUP BY title;
Pomocí JOIN příkazu získejte průměrné hodnocení filmů, které byly hodnoceny uživatelem Alice.
SELECT title, AVG(rating) FROM movies
JOIN ratings ON movies.id = ratings.movie_id
JOIN users ON ratings.user_id = users.id
WHERE users.name = 'Alice'
GROUP BY title;
Vypište filmy bez hodnocení.
SELECT title FROM movies
LEFT JOIN ratings ON movies.id = ratings.movie_id
WHERE ratings.movie_id IS NULL;
Vypište uživatele a počet jejich hodnocení.
SELECT users.name, COUNT(ratings.id) FROM users
LEFT JOIN ratings ON users.id = ratings.user_id
GROUP BY users.name;
Vypište uživatele, kteří hodnotili alespoň 5 filmů.
SELECT users.name, COUNT(ratings.id) FROM users
LEFT JOIN ratings ON users.id = ratings.user_id
GROUP BY users.name
HAVING COUNT(ratings.id) >= 5;
Vypište filmy, které byly hodnocemy méně než 2 uživateli.
SELECT title, COUNT(ratings.id) FROM movies
LEFT JOIN ratings ON movies.id = ratings.movie_id
GROUP BY title
HAVING COUNT(ratings.id) < 2;
Vypište seznam nejlépe hodnocených filmů, společně s počtem hodnocení.
SELECT title, AVG(rating), COUNT(ratings.id) FROM movies
JOIN ratings ON movies.id = ratings.movie_id
GROUP BY title
ORDER BY AVG(rating) DESC;
Přidejte do databáze novou tabulku genres, která bude obsahovat žánry filmů.
CREATE TABLE genres (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL
);
A vložte několik záznamů
INSERT INTO genres (name) VALUES
('Action'),
('Adventure'),
('Comedy'),
('Crime'),
('Drama'),
('Fantasy'),
('Horror'),
('Mystery'),
('Romance'),
('Sci-Fi'),
('Thriller');
Vytvořte spojovací tabulku movie_genres, která bude obsahovat vztah mezi filmy a žánry.
CREATE TABLE movie_genres (
id INT PRIMARY KEY AUTO_INCREMENT,
movie_id INT NOT NULL,
genre_id INT NOT NULL,
FOREIGN KEY (movie_id) REFERENCES movies(id),
FOREIGN KEY (genre_id) REFERENCES genres(id),
UNIQUE (movie_id, genre_id)
);
Vytvořte tabulku users_favorite_genres, která bude obsahovat oblíbené žánry uživatelů.
CREATE TABLE users_favorite_genres (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
genre_id INT NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (genre_id) REFERENCES genres(id),
UNIQUE (user_id, genre_id)
);
Pomocí grafického rozhraní si zobrazte vztahy mezi tabulkami a zkontrolujte, že všechny vztahy jsou správně nastaveny.
Referenční řešení zvolilo přístup jednoho primárního klíče a následně aplikaci unikátního omezení na dvojici sloupců.
Existuje i jiný přístup, kdy se jako primární klíč použije dvojice sloupců, ale tento přístup není vždy vhodný z praktických důvodů.
Představte si, že chcete odkazovat na spojovací tabulku z jiné tabulky, pak je lepší mít jeden jednoznačný identifikátor. Pokud byste měli jako primární klíč dvojici sloupců, museli byste vytvořit
- složený klíč, který by se skládal z obou sloupců
- cizí klíče by se museli odkazovat na oba sloupce, což je složitější a méně efektivní
Vložte několik záznamů do právě vytvořených tabulek.
INSERT INTO movie_genres (movie_id, genre_id) VALUES
(1, 5), (1, 8), (1, 9), (1, 10),
(2, 5), (2, 8), (2, 9), (2, 10),
(3, 1), (3, 5), (3, 8), (3, 9), (3, 10),
(4, 1), (4, 5), (4, 8), (4, 9), (4, 10),
(5, 1), (5, 5), (5, 8), (5, 9), (5, 10),
(6, 1), (6, 5), (6, 8), (6, 9), (6, 10),
(7, 1), (7, 5), (7, 8), (7, 9), (7, 10),
(8, 1), (8, 5), (8, 8), (8, 9), (8, 10),
(9, 1), (9, 5), (9, 8), (9, 9), (9, 10),
(10, 1), (10, 5), (10, 8), (10, 9), (10, 10);
INSERT INTO users_favorite_genres (user_id, genre_id) VALUES
(1, 5), (1, 8), (1, 9), (1, 10),
(2, 5), (2, 8), (2, 9), (2, 10),
(3, 1), (3, 5), (3, 8), (3, 9), (3, 10),
(4, 1), (4, 5), (4, 8), (4, 9), (4, 10),
(5, 1), (5, 5), (5, 8), (5, 9), (5, 10);
Vypište všechny žánry, které má Alice ráda.
SELECT genres.name FROM genres
JOIN users_favorite_genres ON genres.id = users_favorite_genres.genre_id
JOIN users ON users_favorite_genres.user_id = users.id
WHERE users.name = 'Alice';
Pohledy jsou virtuální tabulky, které jsou vytvořeny na základě uloženého SELECT příkazu.
Pohledy se používají pro zjednodušení složitých dotazů, které se často opakují.
Vytvořte pohled, který bude obsahovat všechny filmy a jejich průměrné hodnocení, nazvěte ho movies_ratings.
CREATE VIEW movies_ratings AS
SELECT title, AVG(rating) AS avg_rating FROM movies
JOIN ratings ON movies.id = ratings.movie_id
GROUP BY title;
Nad takto vytvořeným pohledem můžete provádět stejné operace jako nad tabulkou.
Například vypsat všechny filmy, které mají průměrné hodnocení vyšší než 8, seřazené sestupně.
SELECT * FROM movies_ratings
WHERE avg_rating > 8
ORDER BY avg_rating DESC;
Transakce je skupina operací, které se provádí jako jeden celek.
Pokud by při zpracování tohoto celku nastala chyba, databázový server se vrátí do původního stavu (provede rollback).
Typickým příkladem transakce je převod peněz mezi dvěma účty. Pokud by se při převodu stala chyba, nemůže dojít k situaci, že na jednom účtu budou peníze odečteny a na druhém nepřijdou!
V naší databázi si transakce předvedeme na tvorbě struktury pro odemykání filmů. Uživatelé budou mít možnost shlédnout film pouze tehdy, pokud za něj zaplatí.
Mohli bychom do tabulky users přidat sloupec balance, ale častokrát se v praktických aplikacích oddělulují
data od různých bussinessových entit. Zlepšuje to čitelnost a udržitelnost kódu.
Vytvoříme si tedy novou tabulku balances, která bude obsahovat zůstatky uživatelů.
Jedná se o relaci
1:1mezi tabulkamiusersabalances.Tento typ relace lze modelovat několika způsoby:
- vlastník (kde je uložen) cizí klíč je tabulka
users- vlastník (kde je uložen) cizí klíč je tabulka
balancesVe většině případů se setkáte s postupem 2, jelikož to umožňuje snadnější
- přidání dalších vlastností k uživatelům,
- odebrání této funkcionliaty, pokud již není potřeba
- snadnější rozšíření o další funkcionality v budoucnu
Syntaxe pro transakci v MySQL je následující
START TRANSACTION;
-- operace
COMMIT;
Pokud chce provést rollback, můžete použít
ROLLBACK;
Často se v programovaích jazycích používá try-catch blok, který zachytí chybu a provede rollback transakce.
Kód může vypadat následovně
try {
$db->beginTransaction();
// operace
$db->commit();
} catch (Exception $e) {
$db->rollback();
}
Vytvořte tabulku balances, kde cizí klíč bude vlastnit tabulka balances.
CREATE TABLE balances (
user_id INT PRIMARY KEY,
balance DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id)
);
V referenčním řešení jsme použili
DECIMALpro uložení zůstatku, jelikož se jedná o peníze, které mají pevně daný formát.
DECIMAL(p, s)kdepje celkový počet číslic asje počet číslic za desetinnou čárkou.Také jsme NEpoužili
AUTO_INCREMENT, jelikož primární klíč je také cizí klíč a jedná se o1:1relaci
Vytvořte tabulku subscriptions, která bude obsahovat informace o zakoupených filmech.
CREATE TABLE subscriptions (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
movie_id INT NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (movie_id) REFERENCES movies(id)
);
A nakonec vytvoře speciální relační tabulku 1:1 movie_prices, která bude obsahovat ceny filmů.
CREATE TABLE movie_prices (
movie_id INT PRIMARY KEY,
price DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (movie_id) REFERENCES movies(id)
);
Vytvořte jednoduchou aplikaci, která se připojí na databázi a bude umožňovat uživatelům zakoupit film.
Aplikace by měla provést následující operace
subscriptions