Opakování

Kombinování dat z více tabulek

• CROSS JOIN (Kartézský součin)
• INNER JOIN
• OUTER JOIN

CROSS JOIN

Spojení dvou tabulek "každý s každým" a.k.a. Beverly Hills

CROSS JOIN - příklad

CROSS JOIN - příklad

SELECT * FROM studenti, barvy;
            

SELECT * FROM studenti CROSS JOIN barvy;
            

[INNER] JOIN

Nejpoužívanější

• INNER JOIN ON (TRUE) je shodný s CROSS JOIN
• INNER JOIN navíc určuje podmínku výběru (které řádky zůstanou)
• Vybere a spojí pouze ty řádky, které je schopen spárovat (viz příklad dále).

[INNER] JOIN - příklad

[INNER] JOIN - příklad

SELECT * FROM autor
INNER JOIN kniha ON (autor.id = kniha.id_autora);
            

SELECT * FROM autor
CROSS JOIN kniha WHERE autor.id = kniha.id_autora;
            

[OUTER] JOIN

• LEFT [OUTER] JOIN
• RIGHT [OUTER] JOIN
• FULL [OUTER] JOIN

LEFT [OUTER] JOIN

Vybere všechny záznamy z "LEVÉ" tabulky a přiřadí záznamy, které může. Ke zbylým záznamům přiřadí NULL.

SELECT * FROM autor
LEFT OUTER JOIN kniha ON (autor.id = kniha.id_autora);
            

RIGHT [OUTER] JOIN

Vybere všechny záznamy z "PRAVÉ" tabulky a přiřadí záznamy, které může. Ke zbylým záznamům přiřadí NULL.

SELECT * FROM autor
RIGHT OUTER JOIN kniha ON (autor.id = kniha.id_autora);
            

FULL [OUTER] JOIN

Vybere všechny záznamy z "PRAVÉ" i "LEVÉ" tabulky a přiřadí záznamy, které může. Ke zbylým záznamům přiřadí NULL.

SELECT * FROM autor
FULL OUTER JOIN kniha ON (autor.id = kniha.id_autora);
            

Vkládání cizích klíčů

Vložit cizí klíč (neboli referenci na jinou tabulku) je možné dvěma způsoby

Vkládání cizích klíčů - postupně

SELECT id FROM autor WHERE prijmeni = 'Pratchett';

INSERT INTO kniha (id, nazev, id_autora) VALUES
  (7, 'Čaroprávnost', ###),
  (8, 'Soudné sestry', ###);
  -- místo ### je nalezené ID z předchozího dotazu
            

Tímto způsobem je možné zkontrolovat, že nám záznam skutečně vrací ID (resp. záznam), který opravdu chceme.

Vkládání cizích klíčů - najednou

INSERT INTO kniha (id, nazev, id_autora) VALUES
  (7, 'Čaroprávnost',  (SELECT id FROM autor WHERE prijmeni = 'Pratchett') ),
  (8, 'Soudné sestry',  (SELECT id FROM autor WHERE prijmeni = 'Pratchett') );
            

Pokud jsme si jisti, že nám záznam vybere správného autora, můžeme použít vnořený SELECT.

Trocha nové teorie

LIMIT / OFFSET

Omezí počet vypisovaných řádků.

SELECT * FROM student LIMIT 20;
            

SELECT * FROM student LIMIT 20 OFFSET 20;
            

DISTINCT

odlišný

Každá hodnota je ve výsledné tabulce zobrazena pouze jenou.

SELECT DISTINCT vek FROM student;
            

Agregace

seskupování

Agregační funkce slouží k získávání agregovaných údajů z tabulky.

Student

Základní agregační funkce

• AVG
• MAX
• MIN
• SUM
• COUNT

AVG

SELECT AVG(vek) FROM student;
            

SELECT AVG(DISTINCT vek) FROM student;
            

MAX

SELECT MAX(vek) FROM student;
            

SELECT MAX(DISTINCT vek) FROM student;
            

SELECT vek FROM student ORDER BY vek DESC LIMIT 1;
            

MIN

SELECT MIN(vek) FROM student;
            

SELECT MIN(DISTINCT vek) FROM student;
            

SELECT vek FROM student ORDER BY vek ASC LIMIT 1;
            

SUM

SELECT SUM(vek) FROM student;
            

SELECT SUM(DISTINCT vek) FROM student;
            

COUNT

SELECT COUNT(vek) FROM student;
            

SELECT COUNT(DISTINCT vek) FROM student;
            

Kombinace agregačních funkcí

SELECT COUNT(vek), AVG(vek), MIN(vek), MAX(vek), SUM(vek)
  FROM student;
            

Kombinace agregačních funkcí II

SELECT vek, COUNT(vek) FROM student;
            

ERROR: column "student.vek" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT vek, COUNT(vek) FROM student;

GROUP BY

SELECT vek, COUNT(vek) FROM student GROUP BY vek;
            

SELECT DISTINCT id_autora, COUNT(id_autora)
  FROM kniha GROUP BY id_autora;
            

SELECT autor.id, autor.jmeno, autor.prijmeni,
  COUNT(kniha.id_autora) FROM kniha
  FULL JOIN autor ON (kniha.id_autora = autor.id)
  GROUP BY autor.id, autor.jmeno, autor.prijmeni;
            

DIY - Do It Yourself

Iniciální data

• Pravým na databázi
• Obnovit (restore)
• Vybrat soubor
• OK

ÚKOL 1 - opáčko z minula

Vyberte všechny autory, kteří nemají jméno (pouze příjmení).

Vypište 10 knih od Terryho Pratchetta a seřaďte je podle hodnocení vzestupně (od nejmenšího po největší).

Vypište všechny autory, jejichž knihy mají český i anglický název začínající na písmeno "h" a seřaďte je podle příjmení a jména. Autoři se v záznamu budou vyskytovat maximálně jednou (DISTINCT).

ÚKOL 2 - Agregační funkce

Kolik autorů obsahuje databáze?

Kolik knih obsahuje databáze?

Jaké je nejvyšší hodnocení jedné z knih od George R.R. Martina

Jaké je průměrné hodnocení knih o Harrym Potterovi?

Vypište kolik knih napsali jednotliví autoři.

Seskupte počet knih podle jejich hodnocení. (Kolik knih má hodnocení 93%, kolik 94% atd...)

Kolik autorů (počet) napsalo knihy, které jsou hodnoceny na 94%?