Práce s SQL dotazy

Tvorba a úprava databáze

Než je možné jakákoliv data uložit, musíme vytvořit samotnou strukturu databáze. K tomu slouží Data Definition Language (zkráceně DDL). Jeho úkolem je vytvořit jednotlivé tabulky s jejich sloupci (atributy), datovými typy a relacemi.

Příkazy

CREATE - vytváření tabulky či databáze
DROP - smazání tabulky či databáze (i s jejími daty)
ALTER - úprava existující struktury (např. přidání sloupce)
TRUNCATE - smazání všech záznamů v tabulce

Základní datové typy a klíče

INT – celé číslo (často pro ID, věk, počty).
VARCHAR(n) – text s proměnnou délkou do maximálně n znaků (např. jméno, e-mail).
DATE – kalendářní datum (rok, měsíc, den).
DECIMAL / FLOAT – desetinné číslo (např. průměr známek, cena).
PRIMARY KEY (PK) – primární klíč; unikátní identifikátor každého řádku v tabulce.
FOREIGN KEY (FK) – cizí klíč; odkazuje na primární klíč v jiné tabulce a vytváří tím relaci (vazbu).

-- 1. CREATE: Vytvoření tabulek s jejich sloupci
CREATE TABLE studenti (
    id INT PRIMARY KEY,
    jmeno VARCHAR(50) NOT NULL
);

CREATE TABLE predmety (
    id INT PRIMARY KEY,
    nazev VARCHAR(50) NOT NULL
);

CREATE TABLE znamky (
    id INT PRIMARY KEY,
    student_id INT,
    predmet_id INT,
    hodnota INT NOT NULL,
    FOREIGN KEY (student_id) REFERENCES studenti(id),
    FOREIGN KEY (predmet_id) REFERENCES predmety(id)
);

CREATE TABLE poznamky_k_znamkam (
    id INT PRIMARY KEY,
    znamka_id INT,
    text VARCHAR(255),
    FOREIGN KEY (znamka_id) REFERENCES znamky(id)
);

-- 2. DROP: Smazání nadbytečné tabulky
DROP TABLE poznamky_k_znamkam;

-- 3. ALTER: Přesun sloupce poznámky přímo do tabulky známek
ALTER TABLE znamky ADD COLUMN poznamka VARCHAR(255);

Záznamy v databázi

Jakmile existují tabulky, můžeme do nich vložit konkrétní obsah a následně s ním pracovat. Jednotlivé záznamy totiž tvoří řádky tabulky.

Zápis a úprava dat

Data Manipulation Language (DML) má na starost vkládání, upravování a mazání řádků v existujících tabulkách.

Příkazy

INSERT INTO - vložení řádku
UPDATE - úprava řádku
DELETE - smazání řádku

Úprava a mazání se využívá s klauzulí WHERE, bez ní dojde k ovlivnění všech řádků v tabulce.

-- 1. INSERT: Vložení dat do tabulek
INSERT INTO studenti (id, jmeno) VALUES (1, 'Jan Novák');
INSERT INTO studenti (id, jmeno) VALUES (2, 'Eva Svobodová');

INSERT INTO predmety (id, nazev) VALUES (101, 'Informatika');

INSERT INTO znamky (id, student_id, predmet_id, hodnota, poznamka) 
VALUES (1, 1, 101, 1, 'Výborný projekt');

-- 2. UPDATE: Oprava překlepu nebo změna hodnoty
UPDATE znamky 
SET hodnota = 2, poznamka = 'Oprava po testu' 
WHERE id = 1;

-- 3. DELETE: Smazání konkrétního záznamu
DELETE FROM znamky WHERE id = 1;

Čtení a vyhledávání dat

Vůbec nejčastější činností při práci s databázemi je vyhledávání a čtení informací. K tomu slouží Data Query Language (DQL), jehož jádrem je příkaz SELECT. Data můžeme zobrazit se všemi sloupci pomocí hvězdičky *, nebo jen konkrétní sloupce vypsáním jejich názvů. Výsledky lze navíc omezit podmínkami, seskupovat do souhrnů či skládat dohromady z několika provázaných tabulek.

Klauzule příkazu SELECT

Při psaní je důležité dodržet pořadí klauzulí:

SELECT – určuje výstupní sloupce nebo výpočty.
- Agregační funkce: Slouží ke zpracování celé sady řádků do jediné výsledné hodnoty. Mezi nejčastější patří COUNT() (spočítá počet záznamů), AVG() (vypočítá aritmetický průměr), SUM() (sečte hodnoty), MIN() a MAX() (najde nejmenší či největší hodnotu). Jak pracovat s funkcemi: GeeksforGeeks – Aggregate Functions in SQL
FROM – určuje výchozí tabulku, ze které data čerpáme.
- JOIN****: Umožňuje spojit více tabulek do jednoho výstupu na základě vazby mezi primárním a cizím klíčem. Nejčastěji používané typy spojení tabulek jsou INNER, RIGHT/LEFT, jak s nimi pracovat se dozvíš v článku: W3Schools – SQL JOIN Guide
WHERE – základní filtr, který vybere jen řádky splňující zadané podmínky ještě před jakýmkoliv seskupováním.
GROUP BY – seskupí řádky se shodnou hodnotou do logických balíků (např. seskupí známky podle studenta nebo předmětu, abychom na nich mohli provést agregační funkce).
HAVING – funguje jako filtr pro celé vytvořené skupiny po použití GROUP BY (např. vypiš jen ty studenty, kteří mají průměr lepší než 1,5).
ORDER BY – seřadí výsledky vzestupně (ASC, výchozí) nebo sestupně (DESC).
LIMIT – určí maximální počet zobrazených záznamů

SELECT 
    COUNT(*) AS celkem_znamek,
    AVG(hodnota) AS celkovy_prumer
FROM znamky;

SELECT 
    studenti.jmeno,
    znamky.hodnota AS znamka
FROM studenti
LEFT JOIN znamky ON studenti.id = znamky.student_id;

SELECT 
    studenti.jmeno,
    AVG(znamky.hodnota) AS prumer_znamek,
    COUNT(znamky.id) AS pocet_znamek
FROM studenti
INNER JOIN znamky ON studenti.id = znamky.student_id
WHERE znamky.hodnota IS NOT NULL
GROUP BY studenti.jmeno
HAVING COUNT(znamky.id) >= 2
ORDER BY prumer_znamek ASC
LIMIT 3;

Detailní informace je možné vždy zjistit v dokumentaci nebo v přehledných tutoriálech jako jsou tyto od W3Schools.

Pokročilá práce s databázemi

Transaction Control Language - TCL

Pro složitější úpravy, při kterých nesmí nastat žádná chyba, můžeme využít transakce. Ty umožňují při neúspěchu vrátit data do původního stavu, úspěšná změna dat se trvale zapíše do databáze.

COMMIT – trvale uloží veškeré změny provedené v rámci transakce.
ROLLBACK – vrátí databázi do původního stavu před začátkem transakce a zruší neuložené změny.
SAVEPOINT – vytvoří záchytný bod uvnitř transakce, ke kterému se lze vrátit bez rušení celého procesu.

Pro hlubší pochopení transakcí doporučuji článek GeeksforGeeks – SQL Commands (DDL, DQL, DML, DCL, TCL).

Data Control Language - DCL

Slouží k nastavení bezpečnosti a řízení přístupových práv. Určuje, kdo smí data pouze prohlížet a kdo má právo je upravovat nebo mazat.

GRANT – udělí uživateli nebo roli konkrétní oprávnění (např. právo na SELECT či UPDATE).
REVOKE – dříve udělené oprávnění odebere nebo zruší.
Základy správy uživatelů a rolí přehledně vysvětluje W3Schools – SQL Hosting & Database Privileges.