SQL – pokročilá práce s tabulkami a daty

23. října 2000

•

V dnešním díle si ukážeme využití vnořování SQL dotazů do příkazů pro manipulaci s daty (INSERT, UPDATE, DELETE). Druhou část pak budu věnovat dalším typům spojování tabulek, které nám rozšíří další možnosti konstrukce pokročilých SQL dotazů.

Ještě než se pustíte dále, můžete si přečíst slibované řešení příkladů zadaných na konci minulého dílu.

Řešení příkladů

a) Vypište seznam knih, které napsal pan Horáček samostatně (tj. ne jako spoluautor):

SELECT název
FROM kniha, napsané_knihy, autoři
WHERE kniha.id = napsané_knihy.publikace_id
  AND napsané_knihy.autor_id = autoři.id
  AND příjmení LIKE ‚Horáček‘
  AND NOT EXISTS (
        SELECT 1
        FROM napsané_knihy x
        WHERE x.publikace_id = napsané_knihy.publikace_id
          AND x.autor_id != napsané_knihy.autor_id
      )

b) Vypište seznam knih, které byly v devadesátých letech vydány alespoň třemi různými nakladatelstvími:

SELECT kniha.název
FROM kniha, výtisk
WHERE kniha.id = výtisk.publikace_id
  AND (1990 < rok AND rok <= 2000)
  AND EXISTS (
        SELECT 1
        FROM výtisk v
        WHERE v.publikace_id = kniha.id
        GROUP BY kniha.id
        HAVING COUNT(*) >= 3
      )

c) Následující SELECT dává odpověď na dotaz: „vypište seznam všech autorů, kteří nenapsali žádnou knihu samostatně“.

SELECT jméno, příjmení
FROM napsané_knihy, autoři
WHERE napsané_knihy.autor_id = autoři.id
  AND NOT EXISTS (
        SELECT 1
        FROM napsané_knihy x, napsané_knihy y
        WHERE x.autor_id = napsané_knihy.autor_id
          AND x.publikace_id = y.publikace_id
        GROUP BY y.publikace_id
        HAVING COUNT(*) = 1
      )

Pokročilé vkládání dat

Nejprve se podívejme na příkaz INSERT, který jsme probírali ve 4. díle. Někdy v databázovém systému vytvoříme časem novou tabulku, a tu budeme chtít naplnit daty, která můžeme získat z jiných tabulek. Syntaxe příkazu INSERT má tedy dva tvary:

INSERT INTO jméno_tabulky [(jména sloupců)] VALUES (seznam hodnot)

INSERT INTO jméno_tabulky [(jména sloupců)] vnořený_select

Syntaxi toho prvního již známe, podívejme se blíže na druhou variantu. Data do nové tabulky naplníme daty z jiných tabulek, které nám vrátí vnořený SELECT. Důležité je, že struktura sloupců, které nám vrací vnořený dotaz, musí být stejná, jako je struktura sloupců nové tabulky, do které data chceme vkládat. Strukturou rozumím stejný počet vzájemně si odpovídajících sloupců (tzn. odpovídají si typy sloupců). Uveďme si jednoduchý příklad. Na vysokou školu se nám hlásili uchazeči o studium. Máme tabulku UCHAZEČI vytvořenou následujícím příkazem:

CREATE TABLE uchazeči
(id INTEGER PRIMARY KEY,
jméno VARCHAR(10),
příjmení VARCHAR(20),
body INTEGER DEFAULT 0)

A dále v systému již existuje tabulka STUDENTI evidující již studující studenty na naší škole. Předpokládejme, že tabulka STUDENTI má podobnou strukturu jako UCHAZEČI, tj. obsahuje sloupce ID, JMÉNO a PŘÍJMENÍ. Nyní si představme, že skončilo přijímací řízení a že kritérium pro přijetí je získání alespoň 200 bodů. Z uchazečů uděláme studenty následujícím příkazem

INSERT INTO studenti
SELECT id, jméno, příjmení
FROM uchazeči
WHERE body >= 200

za předpokladu, že máme nějakým způsobem ošetřeno, že nám nedojde ke kolizi ID jednotlivých lidí. Kdybychom kritérium pro přijetí zvolili „přijati jsou všichni, kteří dosáhli nadprůmerného zisku bodů“, příkaz INSERT by vypadal následovně:

INSERT INTO studenti
SELECT id, jméno, příjmení
FROM uchazeči
WHERE body > (SELECT AVG(body) FROM uchazeči)

Tím jsem jen chtěl poukázat na to, že vnořený příkaz SELECT nemusí být jenom jednoduchý, ale může to být libovolně strukturovaný dotaz. Důležité je, aby vracel stejnou strukturu sloupců.

Aktualizace dat

U příkazů UPDATE a DELETE nebude nic zvlášť nového. Použití těchto příkazů se nám rozšiřuje jen o možnost aplikování vnořených dotazů v části WHERE (restrikce). Kdybychom všem, kteří mají podprůměrný plat, chtěli přidat 500 Kč k základnímu platu a 100 Kč k osobnímu ohodnocení, zapsali bychom to následovně:

UPDATE platy
SET plat = plat + 500,
osobni = osobni + 100
WHERE plat < (SELECT AVG(plat) FROM platy)

V průběhu vykonávání příkazu UPDATE se nám průměrný plat nebude měnit, protože vnořený příkaz se v tomto případě vyhodnotí jen jednou.

Spojování tabulek pomocí intervalu

Co se týká spojování tabulek, tak v 6. díle jsme se seznámili jejich přirozeným spojením. Standard SQL definuje další možná spojení tabulek. Jak už jste mohli vysledovat, v příkladech c) ve cvičení jsem použil spojení tabulky se sebou samou. Neděje se tak příliš často, ale někdy je to docela užitečné. Využijeme to v těch případech, kdy potřebujeme „znovu nahlédnout“ do téže tabulky a něco zjistit, např. jestli k danému KNIHA_ID existuje ještě jeden záznam, apod. Spojení tabulky se sebou samou je přirozeným spojením v klasickém slova smyslu a realizuje se úplně stejným postupem, jako přirozené spojení dvou různých tabulek.

U přirozeného spojení jste si mohli všimnout, že bylo realizováno na základě rovnosti dvou hodnot ve vybraných sloupcích (primární a cizí klíč). Tabulky můžeme spojovat i na základě nerovnosti. Tomu se v SQL říká spojení tabulek pomocí intervalu. Uveďme si nyní příklad, kde využijeme intervalového spojení tabulek.

Příklad

Představme si, že máme nějakou univerzitu. Budeme si vést číselník pracovišť. Nejvyšší pracoviště bude mít číslo 1. Univerzita se nám bude dělit na fakulty. Každá fakulta k tomu číslu 1 přidá další cifru. Např. pedagogicka fakulta by měla číslo 12. V rámci každé fakulty budou určitá pracoviště. Každé pracoviště do číselníku k předchozím cifrám přidá další. Např. studijní oddělení pedagogické fakulty bude mít číslo 127.

A řekněme, že si v systému povedeme tabulku PRAVOMOCI, která bude obsahovat lidi a jejich pravomoci nad určitými pracovišti. Pravomoc bude vyjádřena jako interval od kterého čísla do kterého čísla pracoviště. Nechť naplnění tabulek vypadá následovně:

PRACOVIŠTĚ PRAVOMOCI ————————- ———————– ID NÁZEV PLNÉ_JMÉNO P_OD P_DO 1 Masarykova univerzita Jan Novák 1 1 12 Pedagogická fakulta Petr Nový 11 15 13 Fakulta informatiky David Dvořák 121 129 127 studijní oddělení 137 studijní oddělení 121 děkanát 122 ekonomické oddělení

Podívejme se na tabulku PRAVOMOCI. Z ní zjistíme, že např. Petr Nový má pravomoce nad prvními pěti fakultami, David Dvořák na pracoviště v rámci pedagogické fakulty. Budeme-li chtít seznam názvů všech pracovišť, na kterým má pravomoce Petr Nový, zapíšeme dotaz následovně:

SELECT název
FROM pracoviště, pravomoci
WHERE id >= p_od AND id <= p_do
AND plné_jméno LIKE ‚Petr Nový‘

Znovu opakuji, že toto je úplně jiný přístup ke spojování tabulek. Někdy je výhodnější, neboť ušetří místo na disku. Kdybychom uvedený příklad chtěli řešit pomocí klasického přirozeného spojení tabulek, tabulky, které by nesly tytéž informace, by vypadaly takto:

PRACOVIŠTĚ LIDÉ PRAVOMOCI ————————- ————— ——— ID NÁZEV ID PLNÉ_JMÉNO OS_ID PR_ID 1 Masarykova univerzita 1 Jan Novák 1 1 12 Pedagogická fakulta 2 Petr Nový 2 11 13 Fakulta informatiky 3 David Dvořák 2 12 127 studijní oddělení 2 13 137 studijní oddělení 2 14 121 děkanát 2 15 122 ekonomické oddělení 3 121 … 3 129

Všimněte si, že jednotlivá PR_ID pro každou osobu jdou v řadě za sebou, tj. jednotlivá PR_ID tvoří interval, proto bylo výhodnější využít původní varianty s intervalovým spojením tabulek. V momentě, kdy bychom např. Petrovi Novému odepřeli pravomoce na pracoviště 13, tak bychom museli použít klasický přístup ke spojování tabulek. Uvedený dotaz bychom v tomto případě zapsali ekvivalentně takto:

SELECT název
FROM pracoviště, lidé, pravomoci
WHERE pracoviště.id = pr_id
AND lidé.id = is_id
AND plné_jméno LIKE ‚Petr Nový‘

Intervalové spojení lze chápat jako rozšíření, v podstatě se mu dá při návrzích systémů úplně vyhnout.

Spojení tabulek bez omezení

Další možností je spojení dvou tabulek bez omezení – kartézský součin tabulek. V části WHERE nedefinujeme žádnou podmínku pro sloupce. Výsledná tabulka obsahuje spojené řádky každý s každým. Např. má-li první tabulka 10 záznamů, druhá 20, tak výsledná po spojení bude mít 200 záznamů. Nepoužívá se moc často, ale někdy se to může hodit.

Příklad

Jednoduchý příklad bych mohl demonstrovat na hodu dvěma kostkami. Zajímá nás tabulka všech možných hodů bílou a modrou kostkou. Pro každý možný vypsaný hod, budeme chtít znát součet ok hozených na obou kostkách dohromady. Budeme mít dvě tabulky BÍLÁ a MODRÁ, které budou obsahovat následující údaje:

BÍLÁ MODRÁ —- —– ČÍSLO TEXT ČÍSLO TEXT 1 jedna 1 one 2 dva 2 two 3 tři 3 three 4 čtyři 4 four 5 pět 5 five 6 šest 6 six

Dotaz na seznam všech hodů včetně součtu hozených ok bude vypadat následovně:

SELECT bílá.číslo, modrá.číslo, bílá.číslo+modrá.číslo AS součet
FROM bílá, modrá

Tento dotaz nám vrátí celkem 36 řádků, kde se postupně vystřídají všechny kombinace hodů dvěma kostkami.

V příštím díle se podíváme na další použití spojených tabulek a tvorbu pohledů (tzv. VIEW).