Slovník pojmů

SQL

SQL je jazyk pro práci s relačními daty, nikoli databáze. PostgreSQL je konkrétní systém; Doctrine DBAL a ORM jsou PHP vrstvy, které SQL používají nebo generují.

Stručná definice

Popisujete požadovaná data, databáze volí cestu.

SQL se používá pro vytvoření tabulek, čtení dat, jejich vložení, změnu, smazání, agregace a transakce. Deklarativní znamená, že dotaz popíše požadovaný výsledek, například objednávky zákazníka se součtem položek. Databázový planner pak vyhodnotí, zda použije index nebo jiný plán.

Existuje SQL standard, ale PostgreSQL, MySQL a SQLite mají vlastní dialekty a funkce. Přenosný návrh proto rozlišuje obecný princip od konkrétní syntaxe. Parametrizované hodnoty oddělují data od struktury dotazu; ruční skládání nedůvěryhodného vstupu do SQL vede k SQL injection.

Jaký problém řeší

Čtení, změny a definice relačních dat

SQL umožní pracovat s daty přímo a přesně, také tam, kde obecná ORM abstrakce není vhodná.

SELECT pro seznamy, detail, filtry a agregace
INSERT, UPDATE a DELETE pro změny dat
CREATE TABLE a ALTER TABLE pro strukturu databáze
JOIN pro spojení souvisejících tabulek
GROUP BY pro součty, počty a reporty
COMMIT a ROLLBACK pro potvrzení nebo vrácení změn

SQL ukázka

PostgreSQL dotaz s JOIN a agregací

Ukázka je pro PostgreSQL; $1 není vložený řetězec, ale parametr předaný driverem.

SELECT
    o.id,
    o.order_number,
    COUNT(oi.id) AS item_count,
    COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS total_amount
FROM orders AS o
LEFT JOIN order_items AS oi ON oi.order_id = o.id
WHERE o.customer_id = $1
GROUP BY o.id, o.order_number, o.created_at
ORDER BY o.created_at DESC;

Jak funguje

Od datového modelu k výsledku dotazu

Přesný plán provedení je věcí konkrétní databáze a dat, ne jen délky SQL zápisu.

Model Tabulky, typy, klíče a constrainty vymezí, jaká data lze bezpečně uložit.
Dotaz a parametry Aplikace připraví strukturu SQL a hodnoty předá databázovému driveru odděleně jako parametry.
Kontrola Databáze vyhodnotí syntaxi, oprávnění, datové typy a pravidla integrity.
Plán Planner zvolí způsob provedení podle statistik, podmínek, joinů a dostupných indexů.
Výsledek nebo změna Aplikace přečte řádky, nebo potvrdí lokální změnu v transakci.

Důležité pojmy

Příkazy, podmínky a dialekty.

SQL kombinuje práci nad daty se strukturou, pravidly a transakcemi.

SELECT a WHERE

SELECT vybírá sloupce nebo výrazy. WHERE omezuje řádky podle podmínek; bezpečné hodnoty patří do parametrů.

INSERT, UPDATE a DELETE

Mění data. V kritické operaci obvykle patří do krátké databázové transakce spolu se souvisejícími zápisy.

JOIN, GROUP BY a agregace

JOIN propojuje tabulky podle vztahů; GROUP BY a funkce jako COUNT nebo SUM vytvářejí přehledy.

DDL a DML

CREATE TABLE či ALTER TABLE definují schéma. SELECT, INSERT, UPDATE a DELETE pracují s konkrétními daty.

Dialekt a parametr

Funkce, typy a placeholdery se mohou lišit. Parametr chrání hodnotu, ale názvy sloupců nebo směr řazení potřebují povolený seznam.

Vztah k podobným pojmům

SQL je jazyk, ne název produktu.

Nástroje kolem SQL mění úroveň abstrakce, ne význam uložených dat.

PostgreSQL: Konkrétní databáze s vlastním SQL dialektem, plannerem a dalšími funkcemi.
Doctrine DBAL: PHP databázová vrstva pro explicitní SQL, parametry a transakce.
Doctrine ORM: Mapuje objekty na relační data a SQL generuje; při výkonu je stále nutné znát výsledné dotazy.
Databázová transakce: Hranice, ve které se více SQL změn potvrdí nebo vrátí jako celek.

Výhody a omezení

Přímý přístup vyžaduje porozumění modelu.

Přínosy

přesné dotazy, agregace a spojení nad relačními daty
využití typů, constraintů a schopností databáze
vhodný nástroj pro reporty a hromadné změny
možnost hodnotit skutečný plán a výkon dotazu

Časté chyby

záměna SQL s konkrétní databází nebo ORM
ruční skládání nedůvěryhodného vstupu do řetězce
představa, že složitější dotaz je automaticky horší než mnoho malých
ignorování výsledného plánu, indexů a objemu dat
spoléhání na neověřenou specifickou syntaxi mimo cílový dialekt

Praktický příklad

Objednávky zákazníka s počtem položek

Následující ukázka používá PostgreSQL placeholder $1; skutečnou hodnotu předá databázový driver jako parametr. Dotaz čte objednávky jednoho zákazníka, spojí jejich položky a spočítá je. Výkon závisí na datovém modelu, objemu a vhodných indexech.

Složitost se neposuzuje jen podle počtu řádků SQL. Jeden čitelný agregovaný dotaz může být vhodnější než opakované načítání souvisejících dat v cyklu.

Na co myslet

Bezpečnost a výkon jsou součástí jednoho návrhu.

SQL má být čitelné, parametrizované a ověřené nad reálnými daty.

předávat hodnoty jako parametry, nikoli je lepit do SQL řetězce
pro dynamické identifikátory používat konkrétní whitelist
číst výsledné SQL z ORM u výkonnostně citlivých míst
ověřovat EXPLAIN a reálný objem dat před přidáním indexů
rozlišit lokální transakci od externího API nebo fronty

Časté otázky

SQL v aplikaci

Je SQL totéž co PostgreSQL?

Ne. SQL je jazyk a standardizovaný přístup; PostgreSQL je konkrétní databázový systém s vlastním dialektem a funkcemi.

Musím SQL znát, když používám ORM?

ORM mnoho věcí zjednoduší, ale stále pracuje nad relačními daty a generuje SQL. Porozumění dotazům pomáhá u výkonu, transakcí i chyb.

Chrání parametrizovaný dotaz před SQL injection?

Chrání před vložením hodnoty do struktury dotazu, pokud je použit správně. Dynamické názvy tabulek, sloupců nebo SQL klíčová slova parametrizovat obvykle nelze a potřebují whitelist.

Je jeden složitější dotaz vždy horší než více jednoduchých?

Ne. Vhodnost závisí na datovém modelu, počtu dat, přenosech a výsledném plánu. Rozhodnutí má vycházet z měření, ne z počtu řádků SQL.

Jak tento princip používám v praxi

SQL používám tam, kde má přímý dotaz jasnou hodnotu.

Vedle ORM používám explicitní SQL pro reporty, importy a výkonnostně citlivé pohledy; návrh vždy ověřuji proti modelu a výslednému plánu.

Jak pracuji s databázemi Nezávazně probrat projekt