Lekce SQL a funkce

Obsah lekce:

SQL a funkce

V této lekci se seznámíme s některými dalšími příkazy a funkcemi, které můžeme použít v dotazech SELECT. Veškeré zde použité funkce budou vysvětllovány na příkladech s použitím databáze studentských novin, kterou jsme používali v minulých lekcích. Celou databázi si můžete stáhnout v souboru databaze.txt.

Příkaz LIMIT

Příkaz LIMIT využijeme nejčastěji v situaci, kdy máme rozsáhlou tabulku a chceme zobrazit pouze určitý počet záznamů (např. prvních 10 záznamů), nebo pokud chceme záznamy rozepsat na více stránek (např. stránkování seznamu článků po deeti článcích na stránku) - v tomto případě vypisujeme např. 10 záznamů od N-tého záznamu.

       

obecný zápis:

LIMIT od, pocet

LIMIT pocet



výpis prvních 10 záznamů:

LIMIT 10

lze zapsat i jako LIMIT 0, 10



výpis záznamů 11 - 20:

LIMIT 10, 10

První příklad, který je uveden níže, zobrazí 10 nejčtenějších článků s počtem zobrazení (příkaz ORDER BY viewedcount DESC seřadí data dle počtu zobrazení sestupně a příkaz LIMIT 10 vybere prvních 10 záznamů).

SELECT heading, viewedcount

FROM renome_tarticle

ORDER BY viewedcount DESC

LIMIT 10

Prvních 10 nejčtenějších článků již máme zobrazeno. Nyní můžeme chtít zobrazit dalších 10 nejčtenějších článků, tj. 11. - 20. nejčtenější článek. Zde je nutné pouze upravit příkaz LIMIT a rozšířit jej o jeden číselný parametr (LIMIT od, pocet), tedy LIMIT 10, 10.

SELECT heading, viewedcount

FROM renome_tarticle

ORDER BY viewedcount DESC

LIMIT 10, 10

Příkaz LIKE

Příkaz LiKE se nejčastěji používá v dotazech SELECT u příkazu WHERE, který slouží pro výběr záznamů splňujících danou podmínku. V případě, že vyhledáváme textová data a používáme operátor porovnání je nutné zadat přesný text. Někdy je však potřeba podmínku stanovit trochu obecněji (např. články, jejichž nadpis začíná na písmeno P, nebo články obsahující nějaký odkaz). Pro tento účel slouží příkaz LIKE, kde nalevo od tohoto příkazu je jméno pole a napravo výraz definující hledanou hodnotu. V tomto výrazu lze využít speciální znaky dle následující tabulky.

Po přehledu základních možností zápisu je vhodné uvést několik příkladů. První příklad bude sloužit pro výběr článků, jejichž název začíná na písmeno "P".

SELECT heading

FROM renome_tarticle

WHERE heading LIKE 'P%'

Ve druhém příkladu budou vybrány články, v jejichž textu je nějaký odkaz, tj. HTML tag a s nastavenou vlastností href (hledáme text, který obsahuje slovo href).

SELECT heading

FROM renome_tarticle

WHERE text LIKE '%href%'

Agregační funkce a příkaz GROUP BY

Agregační funkce

Agregační funkce slouží pro práci s hodnotami v určité množině záznamů. Nejčastěji používané funkce jsou popsány v následující tabulce.

Následující dva příklady demonstrují použití funkce AVG() pro zjištění průměrného počtu zobrazených článků a funkce SUM() pro zjištění celkového počtu zobrazených článků (součet hodnot). Použití dalších funkcí z výše uvedeného seznamu je založeno na stejném principu.

SELECT AVG(viewedcount)

FROM renome_tarticle

SELECT SUM(viewedcount)

FROM renome_tarticle

Příkaz GROUP BY

Výše popsané agregační funkce se nejčastěji používají společně s příkazem GROUP BY. Tento příkaz slouží k seskupování hodnot dle určitého kritéria. U každé takto seskupené hodnoty můžeme pomocí agregačních funkcí zjistit například počet hodnot v každé kategorii.

SELECT name, COUNT(*)

FROM renome_tarticle

INNER JOIN renome_ccathegory ON renome_tarticle.idccathegory = renome_ccathegory.idccathegory

GROUP BY renome_tarticle.idccathegory

Funkce pro práci s datem a časem

V prvním příkladu je použit dotaz pro zobrazení seznamu článků a jejich stáří ve dnech. Pro zjištění stáří ve dnech je použita funkce DATEDIFF, která zjistí rozdíl mezi aktuálním datem (funkce CURDATE) a datem uvedeným ve sloupci createdate.

SELECT heading, createdate, 

DATEDIFF(CURDATE(), createdate)

FROM renome_tarticle

Dotaz ve druhém příkladě slouží pro výběr článků ze září 2009. Funkce pro práci s datem a časem jsou použity v podmínce WHERE, kde je z data ve sloupci createdate vybrán měsíc pomocí funkce MONTH a rok pomocí funkce YEAR. Měsíc je porovnán s hodnotou 9 (září) a rok s hodnotou 2009. Pro spojení těchto porovnání slouží logický operátor AND (a zároveň).

SELECT heading, createdate 

FROM renome_tarticle 

WHERE (MONTH(createdate)=9 and YEAR (createdate)=2009)

Další čtení

Otázky

1. K čemu slouží v jazyku SQL příkaz GROUP BY a jaké znáte agregační funkce?
2. Jak je v příkazu SELECT možné použít příkaz LIMIT?
3. Jaké znáte SQL funkce pro práci s datem a časem?