Základy SQL, část 1.

MyEgo.cz

home foto blogy mywindows.cz kontakt

Základy SQL, část 1.

PHP 23.03.06

V této sérii článků budu popisovat poněkud opomíjenou věc - SQL a PL/SQL syntax pro současné databáze, a to konkrétně pro opensource produkt MySQL 5.x (tedy včetně VIEWS, TRIGGERS, atd.). Řada lidí se nějak naučí programovat v PHP, ale kvalitní použití SQL pro ně zůstává záhadou. A to je velice špatně.

Osobně veškeré rozsáhlé aplikace píšu zásadně nad databází (typicky Oracle 8 až 10g). Proč? Řada činností se dá rozdělit na události, a tyto události, pokud je napíšete jako triggery a uložené procedury, není nutné explicitně volat. Ať už přistupujete do databáze z webové aplikace, z Delphi či z .NET aplikace, máte záruku, že daná akce se vždy provede. Navíc je zpracování dat nad databází mnohem efektivnější a rychlejší než tahat všechna data na klienta, tam to aplikačně zpracovat, a poté data posílat zpět k uložení na server.

Ubohá znalost SQL je dána i zcela nechutným rozšířením jedné extrémně špatné databáze, tedy MySQL 3.x a 4.x. Mírně lepší je až MySQL 5.x, nicméně, její nasazení na hostingu není zatím moc reálné, řada českých webhostingů se sotva dopracovala k MySQL 4.1.

Tolik k úvodu, a následuje první, pochopitelně nejsnazší, část tohoto seriálu.

SELECT a LEFT/RIGHT/INNER JOIN

Představte si, že máte nějaké fórum, ve kterém ja tabulka příspěvků (TOPICS) a tabulka práv uživatelů (PERMS). Pokud administrátor nic nenastaví, je tabulka PERMS prázdná a platí defaultní práva systému, pokud chce defaultní práva změnit, zapíše přes administrační rozhraní práva pro jednotlivé skupiny uživatelů do tabulky PERMS.

Pokud chcete tedy vypsat posledních 5 příspěvků ve fóru, nemůžete udělat prostý SELECT na tabulku TOPICS, vypsalo by se i to, co právě přihlášenému uživateli běžně není přístupné. Řešením je použít LEFT JOIN:

SELECT t.id, t.subject, t.last_post FROM topics AS t
LEFT JOIN perms AS p ON (p.forum_id=t.forum_id AND p.group_id=4) 
WHERE p.read_forum IS NULL OR p.read_forum=1
ORDER BY t.last_post DESC 
LIMIT 0 , 5

Uvedený jednoduchý SELECT a LEFT JOIN respektuje to, že práva nemusí být v tabulce PERMS nastavena (IS NULL), ale pokud jsou, zkontroluje, zda uživatel zařazený ve skupině [4] má právo pro čtení tohoto fóra (read_forum = 1).

Existuje ještě RIGHT JOIN, který obrátí pozici tabulek, a samozřejmě INNER JOIN, který požaduje, aby shodná data byla v obou tabulkách, nepovoluje tedy NULL hodnoty. Klasický INNER JOIN by v této situaci samozřejmě nebylo možné použít, protože tabulka PERMS je naplněna jen pokud jsou hodnoty práv rozdílné od defaultních hodnot.

Více k dané problematice: MySQL.com manuál na JOIN.

SELECT DISTINCT a GROUP BY

Příkaz SELECT DISTINCT se hodí všude tam, kde chcete z mnoha stejných hodnot vybrat jen ty unikátní, například máte tabulku komentářů a chcete vybrat posledních 5 komentářů od různých autorů. 

SELECT DISTINCT author
FROM comment
ORDER BY time DESC 
LIMIT 0 , 5

Uvedený příklad je nicméně poněkud umělý, ve skutečnosti se budete zajímat i o číslo posledního komentáře daného uživatele (třeba pro zobrazení odkazu na takový komentář) a datum komentáře, což můžete nejsnáze udělat pomocí klauzule GROUP BY, který provede seskupení dotazu kolem pole autor, třeba takto:

SELECT author, MAX(time), MAX(comment_id) 
FROM comment 
GROUP BY author 
ORDER BY time DESC 
LIMIT 0,5

V obou případech je vhodné mít definován INDEX na pole autor i time. Příkaz EXPLAIN Vám zobrazí, jaké existující či pomocné indexy jsou pro SQL dotaz použity.

Komentáře

  1. 1 MiSHAK 23.03.06, 03:03:25
    FB

    Hodilo by se na začátek popsat taky logiku názvů tabulek resp. názvů jeich sloupců. Někdy i na "profesionálních dílech" lze najít roztodivné pojmenování.

    Nechápu, proč je v posledním příkladu použita fce. MAX?

  2. 2 Lukáš 23.03.06, 03:03:49
    FB

    [1] Protože chceš zjistit číslo posledního komentáře - což bude asi to nejvyšší číslo..

  3. 3 Martin Keder 23.03.06, 04:03:05
    FB

    Zajisté chvályhodný záměr. Jsem zvědavý, co se z něj vyvine.
    Dovolím si vznést pár dotazů:

    • Pro koho bude tento seriál, začíná-li od JOINů? Abychom se ke kvalitnímu použití SQL vůbec dostali. Zatím to zavání úvodem do SQL.
    • Není PL/SQL jazyk Oracle? Bude na MySQL plně použitelný?

  4. 4 Radek Hulán 23.03.06, 04:03:13
    FB

    [3] syntaxe všech příkladů je a bude přizpůsobena MySQL 5.x, aby si ji kdokoliv jednoduše otestovat. Nemá smysl, bohužel, se zabývat pro webdesign Oraclem.. Jinak článek opravdu začíná od základů, i se tak jmenuje, viz [1], který nechápe ani toto triviální použití a dokazuje nutnost opakování základů ;-)

  5. 5 Martin Keder 23.03.06, 04:03:36
    FB

    [4] Po pravdě řečeno, konstrukce MAX(autor) mi také přišla nadbytečná, když se podle autora agreguje. Nebo se pletu?

    A MAX(time), MAX(comment_id) by v rámci tohoto blogu asi fungoval podle očekávání (číslo posledního příspěvku a datum jeho vložení), je to ale tím, že příspěvek s nejvyšším číslem je vždy ten poslední. Asi by se navíc mělo agregovat i podle ID článku, jinak to přestane fungovat i tady.

  6. 6 MaReK 23.03.06, 05:03:02
    FB

    Radku situace se už lepší, já se snažím vážné věci nahostovat tam, kde je PostgreSQL o které si myslím, že občas zbytečně opomíjena a přitom může směle konkurovat i velikým komerčním databázím. Zkus někdy porovnat db mezi sebou (nemyslím výkonově :-))

  7. 7 Jakub Vrána 23.03.06, 05:03:13
    FB

    [4] Ale on má pravdu, MAX(author) je skutečně nadbytečné, lepší je author.

    INDEX na pole autor i time působí dojmem, že mají být vytvořeny indexy (author) a (time), pro tento dotaz je ale vhodnější složený index (author, time).

  8. 8 Radek Hulán 23.03.06, 05:03:23
    FB

    [7] máš pravdu, první MAX je tam nadbytečný

  9. 9 František Pazdera 23.03.06, 06:03:40
    FB

    Zdravím, musím souhlasit s prvním příspěvkem, skoro by podle mě bylo vhodnější zahájit základy databází obecně.
    Pokud použiji analogii, k čemu mi bude dobrá znalost používání příboru, když s ním budu jíst z koryta svinstvo spolu s ostatními prasátky?
    Alespoň základní věci, jako postup návrhu db, jak vytvořit kvalitní schema, čemu se vyhnout, jak pojmenovat tabulky a sloupce, jak a proč dekomponovat složité vztahy atd...
    Nemuselo by se snad zabíhat do věcí jako normální formy apod., protože to jde vysvětlit jednodušeji pomocí "přikázání":-). Pro "teenagery" používající MySQL by to dle mého názoru bylo pro jejich další praxi přínosnější.

  10. 10 lukas 23.03.06, 08:03:55
    FB

    Možná by bylo vhodné u příkladů ukázat i datový model.
    Ne všichni jsou zběhlí v SQL a nemusí jim být hned jasná struktura tabulek z uvedených SQL dotazů

  11. 11 Asthmatic 23.03.06, 10:03:18
    FB

    A co CROSS JOIN nebo FULL JOIN? ;)

  12. 12 Ivo 23.03.06, 10:03:27
    FB

    Mne vyhovuje, že seriál nezačína úplnymi základmi, o tom sa dajú nájsť iné seriály. Teším sa na časť o TRIGGERS.

  13. 13 Jirka Pech 24.03.06, 03:03:37
    FB

    [4] Ahoj Radku, díky za inspiraci. Jsem zvědav kolik lidí se bude chtít skutečně dozvědět něco hlubšího o PL/SQL.

  14. 14 EL 24.03.06, 11:03:25
    FB

    Jenom malá poznámka k tomu, že by všechno měla dělat databáze.

    Obecně to samozřejmě pravda je, ale vždy je třeba se zamyslet nad konkrétním systémem, protože nezřídka nastává situace, kdy DB stroj "jede jak fretka" a na nějaké složitější operace prostě nemá čas.

    Proto je čas od času lepší získat surová data a zpracovat je až na aplikačním serveru (pokud DB i aplikační server běží na jednom stroji, předchozí s klidem ignorujte:-)

    Prostě při programování používejte hlavu.

  15. 15 Radek Hulán 24.03.06, 11:03:31
    FB

    [14] tato situace nenastane v podstatě nikdy. Výkon databáze se dá podstatně lépe škálovat, včetně clusterů..

  16. 16 Martin Keder 24.03.06, 11:03:43
    FB

    [14] Velmi bludný názor. Dovedeme-li ho do důsledku, není databázový server vůbec potřeba - všechno se uloží do souboru a budem se v tom hrabat vlastními metodami. A přitom jsou (nebo aspoň mají být) DB servery navrženy tak, aby můj dotaz vykonaly co nejefektivněji. A vůbec už nemluvím o referenční integritě, transakcích, triggerech a podobných vychytávkách, které by jen zoufalec řešil na úrovni aplikace. A ty baga zbytečných dat, lítajících po síti, si taky s klidem odpustím.

  17. 17 Zigi 25.03.06, 09:03:33
    FB

    Také si myslím, že seriál začíná moc daleko. Na začátek bych dal Relační algebru, aby si lidi uvědomili, jak to vůbec vlasně celé funguje. Oni pak vidí operaci typu JOIN a vůbec nemají ponětí, jaké rozsáhlé výpočty jsou za tím. Pak se stává že nerozumně joinujou několik tisíci řádkových tabulek a diví se, že je to pomalé. Zkrátka a dobře místo toho aby nejprve provedli restrikci a pak teprve joinovali, tak nejprve si udělají join a pak až restrikci.

  18. 18 brade-cz 25.03.06, 09:03:33
    FB

    Zajímavý článek, ale opravdu tam chybí popsat tabulky. Kdy bude další díl? :)

  19. 19 kub!Czek 25.03.06, 11:03:01
    FB

    děkuji za tento seriál článků....začínám s SQL a tohle mi právě přijde vhod...
    ještě jednou díky moc a jen tak dál