Lekce SQL a příkaz SELECT - cvičení

Obsah lekce:

Struktura vstupní tabulky

V této lekci budeme realizovat několik SQL dotazů nad databázovou tabulkou online studentských novin. Tabulka se jmenuje renome_tarticle a má následující atributy (neuvádíme všechy atributy, ale pouze ty podstatné pro naše cvičení):

• idtarticle - primární klíč tabulky - obsahuje číselnou hodnotu udávající identifikátor každého řádku (hodnota má vlastnost auto_increment pro automatické vložení o jedničku většího čísla než má poslední vložený záznam při vložení záznamu)
• heading - hlavní nadpis článku
• descr - (description) - jedná se atribut, do kterého se vkládá stručný obsah článku určený pro krátkou charakteristiku článku
• text - text článku - obsahuje celé znění článku
• public - tento atribut má dvě možné hodnoty: 1 - článek je publikován a je tedy na www stránce vidět, pokud je hodnota jiná, pak článek (záznam tabulky) vidět na www není.
• mysort - tento atribut slouží k řazení záznamů dle potřeby
• del - (delete) - tento atribut udává, zda je řádek "smazán" či nikoli. Má-li tento atribut hodnotu 0, pak článek smazán není, ve všech ostatních případech smazán je.
• viewedcount - tento atribut obsahuje číslo reprezentující celkový počet přečtení daného článku.
• createdate - datum a čas vytvoření článku v tabulce

Pro lepší představu slouží následující obrázek se strukturou tabulky.

Tabulku bychom mohli podle obázku vytvořit ručně nebo použít SQL příkaz pro vytvoření tabulky pomocí jazyka SQL takto:

CREATE TABLE IF NOT EXISTS `renome_tarticle` (
  `idtarticle` int(11) NOT NULL auto_increment,
  `idccathegory` int(11) NOT NULL default '0',
  `idcreporter` int(11) default NULL,
  `heading` varchar(255) collate cp1250_czech_cs default NULL,
  `descr` mediumtext collate cp1250_czech_cs,
  `text` longtext collate cp1250_czech_cs,
  `public` tinyint(4) default '0',
  `tocome` tinyint(4) default '0',
  `mainpage` tinyint(4) default '0',
  `mysort` mediumint(9) default '0',
  `mysortmp` mediumint(9) default '0',
  `del` tinyint(4) default '0',
  `html` tinyint(4) default NULL,
  `viewedcount` mediumint(9) default '0',
  `createdate` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`idtarticle`)
) ENGINE=InnoDB  DEFAULT CHARSET=cp1250 COLLATE=cp1250_czech_cs AUTO_INCREMENT=1;        
        

Abychom mohli s tabulkou pracovat (vybírat data), tak nestačí pouze vytvořit strukturu, ale je nutno tabulku také naplnit daty. Pro tyto účely máme připraven soubor, který nám v námi definované databázi vytvoří tuto tabulku a naplní ji daty: renome-sql-dump.txt. Obsah tohoto souboru stačí importovat do MySQL (například jednoduše zkopírovat a vložit v phpmysqladminu do políčka pro vykonání SQL dotazu a spustit jej). Ukázku vložených záznamů v tabulce můžeme vidět níže.

Realizace jednolivých dotazů

V této části lekce se pokusíme vytvořit SQL dotazy na tabulku tarticle. Bude se jednat o dotazy, které nám budou vracet (zobrazovat) záznamy vyhovující našim zadáním. Všechny dotazy realizujte například v phpmysqladminu v okně pro zadávání SQL příkázů.

Dotaz 1

Název a popis všech článků z tabulky tarticle.

SELECT 
    heading, descr  
FROM 
    renome_tarticle

Dotaz 2

Název a popis všech článků z tabulky tarticle tříděný podle data vytvoření.

SELECT 
    heading, 
    descr, 
    createdate 
FROM 
    renome_tarticle 
ORDER BY 
    createdate

Dotaz 3

Název a datum vytvoření všech článků z tabulky tarticle, které byly vytvořeny v roce 2009.

Nápověda: použijte SQL funkci YEAR (o jeho použití se můžete dočíst například v MySQL manuálu).

SELECT 
    heading,                  
    createdate 
FROM 
    renome_tarticle 
WHERE 
    YEAR(createdate)=2009

Dotaz 4

Název a datum vytvoření všech článků z tabulky tarticle, které byly vytvořeny ve školním roce 2009.

Nápověda: použijte SQL funkci YEAR, MONTH (o jejich použití se můžete dočíst například v MySQL manuálu)a případně složitějších podmínek s AND a OR.

SELECT 
    heading,                  
    createdate 
FROM 
    renome_tarticle 
WHERE 
    ((YEAR(createdate)=2009) AND (MONTH(createdate)>8) OR (YEAR(createdate)=2010) AND (MONTH(createdate)<7))

Dotaz 5

Název a identifikátor kategorie všech článků z tabulky tarticle, které jsou z kategorie "K zamyšlení".

Abychom mohli tento dotaz napsat, tak potřebujeme další tabulku, která obsahuje informace o kategoriích. Bez ní máme k dispozici pouze identifikátor kategorie, ale nevíme, jak se kategorie s daným identifikátorem (idccathegory) jmenuje.

• idccathegory - primární klíč tabulky - obsahuje číselnou hodnotu udávající identifikátor každého řádku (hodnota má vlastnost auto_increment pro automatické vložení o jedničku většího čísla než má poslední vložený záznam při vložení záznamu)
• name - jméno kategorie
• mysort - tento atribut slouží k řazení záznamů dle potřeby
• href - tento atribut obsahuje odkaz na internetovou stránku (volitelné).
• del - (delete) - tento atribut udává, zda je řádek "smazán" či nikoli. Má-li tento atribut hodnotu 0, pak kategorie smazána není, ve všech ostatních případech smazána je.

Tabulku ccathegory naplněnou daty máme k dispozici ve formátu SQL v souboru renome_ccathegory-sql-dump.txt. Obsah tohoto souboru stačí importovat do MySQL (například jednoduše zkopírovat a vložit v phpmysqladminu do políčka pro vykonání SQL dotazu a spustit jej). Ukázku vložených záznamů v tabulce můžeme vidět níže.

Z tabulky vidíme, že kategorie "K zamyšlení" má identifikátor s hodnotou 6.

SELECT 
    heading, idccathegory
FROM 
    renome_tarticle 
WHERE 
    idccathegory = 6

Dotaz 6

Název a identifikátor kategorie všech článků z tabulky tarticle, které jsou z jedné z kategorií "K zamyšlení" nebo "Školní šepoty".

SELECT 
    heading, idccathegory
FROM 
    renome_tarticle 
WHERE 
    (idccathegory = 6 OR idccathegory = 2)

Dotaz 7

Všechny atributy článků z tabulky tarticle, které jsou z jedné z kategorií "K zamyšlení" nebo "Školní šepoty" a jsou uveřejněny.

SELECT 
    *
FROM 
    renome_tarticle 
WHERE 
    (idccathegory = 6 OR idccathegory = 2) AND public = 1

Další čtení

Otázky

1. K čemu slouží v jazyku SQL příkaz SELECT?
2. Charakterizujte možnosti příkazu SELECT.
3. K čemu slouží v příkazu SELECT klauzule WHERE?
4. K čemu slouží v příkazu SELECT klauzule ORDER BY?
5. Jak se tvoří v příkazu SELECT složitější podmínky?