Relační datový model a jazyk SQL

SQL = structured query language, je standardizovaný dotazovací jazyk používaný pro práci s daty v relačních databázích

Příkazy e mohou více či méně lišit pro konkrétní databázi

SQL příkazy se dělí na čtyři základní skupiny:

• Příkazy pro definici dat (CREATE, ALTER, DROP, …)
• Příkazy pro manipulaci s daty (SELECT, INSERT, UPDATE, DELETE, …)
• Příkazy pro řízení přístupových práv (GRANT, REVOKE)
• Příkazy pro řízení transakcí (START TRANSACTION, COMMIT, ROLLBACK)
• Ostatní nebo speciální příkazy

Lze označit jako DDL – Data Definition Language

CREATE - slouží pro vytváření nových objektů

- například: tabulku, index, pohled, proceduru

Vytvoření pohledu

CREATE VIEW <nazev pohledu>
AS
<SELECT prikaz>

Vytvoření indexu

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
INDEX <nazev indexu> ON <nazev tabulky nebo pohledu>(<nazev sloupce> [ASC | DESC]
[,...n])

ALTER - slouží pro upravení objektů

DROP - pro smazání objektů

Slouží pro manipulaci s daty.

DELETE - maže data z tabulky

DELETE from users where (age < 13)

UPDATE - aktualizuje data

UPDATE users set city = 'Brno', wtf = 'FIT' where (surname = 'Novák')

INSERT - vkládáme nová data

INSERT into users (name, surname, age) values ('Jiri', 'Janda', 22)

SELECT - nejčastější příkaz

• V příkazu vybíráme s jakými sloupci v jaké tabulce (tabulkách) budeme pracovat a dale podminku která řádky mají být zpracovány.
• nejkomplikovanější příkaz, má spoustu volitelných podčástí

• where - společný i pro ostatní kromě insertu. Obsahuje podmínky, mohou se spojovat and / or, používat závorky atd.
• group by - Seskupení záznamů znamená, že shrneme informace několika záznamů do jednoho záznamu. K tomuto používáme GROUP BY, za kterým uvedeme název sloupce, podle kterého se mají záznamy seskupit (všechny záznamy, které mají stejnou hodnotu tohoto sloupce, se sloučí do jednoho záznamu).
• having - je velmi podobná podmínce WHERE. Rozdíl je v tom, že podmínka WHERE omezuje ještě neseskupené záznamy a podmínka HAVING omezuje seskupené záznamy. Další rozdíl je, že podmínka HAVING se musí týkat sloupce, který má konstantní hodnotu a nebo agregační funkce. Př. (HAVING `pocet` > 5)
• order by - specifikuje, podle čeho budou výstupní data seřazena - sloupec asc / desc
• distinct Uvedením klíčového slova DISTINCT se eliminují duplicitní řádky „Ze kterých měst jsou klienti spořitelny?“ SELECT DISTINCT město FROM Klient

Transakce a prava jsme prakticky neprobírali.

Slouží ke spojování výsledku dotazu SELECT ze dvou vstupních množin (typicky tabulek relační databáze).

Vnitřní spojení - Nejjednodušší variantou je tzv. INNER JOIN (pozn. INNER a OUTER není nutné používat a v podstatě se ani nedoporučuje používat - zbytečně prodlužují zápis). Ten odpovídá kartézskému součinu tabulek a aplikaci predikátu na tento součin.

• obecné na základě podmínky (T1 JOIN T2 ON A<C)
• na základě rovnosti (equijoin) (T1 JOIN T2 ON A=D)
• přirozené (natural join) (T1 NATURAL JOIN T2)- Přirozené spojování (NATURAL

JOIN) je zvláštním případem vnitřního spojování, kde je spojovací podmínka realizována automaticky dle shodnosti názvů a datových typů ve spojovaných tabulkách. Pro nemožnost konkrétněji definovat ve spojovacích tabulkách, ale pouze strukturou, není tento typ tolik používán.

Vnější spojení - Vnější spojování (OUTER JOIN) generuje výstupní množinu omezenou o spojovací podmínky podobně jako vnitřní spojování, pokud však není nalezen vhodný řádek v druhé množině, je nenalezený řádek nahrazen hodnotami NULL. Dle typu vnějšího spojení mohou být doplňovány řádky z jedné nebo obou vstupních množin. Výsledkem vnějšího spojování jsou řádky naplněné hodnotami ze vstupních množin i částečně.

Částečné vnější spojování - Pro částečné vnější spojování jsou definována klíčová slova LEFT a RIGHT, která definují, která vstupní množina má zahrnuté všechny řádky v množině výstupní, tj. která vstupní množina není doplňována o NULL hodnoty

Lze použít i FULL OUTER JOIN.

SQL umožňuje v klauzuli FROM uvádět nejen jména tabulek, ale i tabulkové výrazy, resp. výraz spojení

Pohled je virtuální databázová struktura, která může obsahovat data z nula až více tabulek.

V prohlížeči máte záložky, na ploše zástupce a v databázi? V databázi jsou pohledy. Z pohledů lze především vybírat data (k čemuž slouží nejčastěji), a někdy do nich lze i data vkládat. Databázové systémy by se bez pohledů obešly - podobně, jako byste se na ploše obešli bez zástupců nebo v prohlížeči bez záložek. Ale práce s nimi může být mnohem pohodlnější.

• Mohou sloužit jako jiné pojmenování tabulek
• Mohou z tabulky vybrat jen určité řádky (nebo sloupce)

· *Pohledy mohou obsahovat výrazy (jako jsou vestavěné funkce MySQL nebo uživatelsky definované funkce).

• Mohou spojovat data z více tabulek
• Pohledy se mohou odkazovat na další pohledy

Pohledy s klauzulemi DISTINCT, GROUP BY, HAVING, s agregačními funkcemi a spojující několik tabulek, umožňují jen čtení.

create view vwPracovnici as select * from pracovnici;

- Od tohoto okamžiku můžete pohled použít jako tabulku, ve smyslu:

select * from vwPracovnici;

create view vwPrumernyVek as select avg(vek) as prumer from pracovnici;

- Vrátí průměrný věk (výsledek bude obsahovat vždy jen jeden řádek). Neboli pohled může obsahovat agregační funkci.

Používáme pohledy, pokud

• si tím subjektivně zjednodušíte práci
• se stejný dotaz opakuje
• potřebujete mít v dotazu SELECT nějaké výrazy

Procedura - Uložená procedura je sada příkazů SQL, které jsou:

• Uložené na serveru
• Zkompilované pro rychlejší použití
• Sada příkazů je provedena najednou
• Může se podobat filozofii maker v OpenOffice.org writeru
• Jestliže chce několik databázových klientů vykonávat stejnou (nebo hodně podobnou) práci, bude pro ně příjemné zjištění, že něco takového již server „umí“ díky uložené proceduře, kterou má k dispozici.
• Přispívají k zabezpečení serveru. Nastaveny tak, že: 1. Smí spouštět pouze někdo, 2. Mohou samy kontrolovat počet, typ, velikost a některé jiné charakteristiky parametrů, jež jsou jim posílány
• Nejdůležitější argument procedur - jsou obecně schopny běžet rychleji

Procedury mohou dělat to, co normální příkazy:

• Vybírání dat
• Vkládání, aktualizace, odstraňování dat
• Vytváření, používání a rušení dočasných tabulek
• Matematické a statistické výpočty

Příklad:

create procedure sp_vratradky (od int, do int)
begin
select * from software where id between od and do;
end

A zavolat pomocí příkazu:

call sp_vratradky 10, 20)

Triggery - Trigger je uložená procedura, která se spouští v souvislosti s provedením nějakého akčního dotazu na tabulce, nebo taky pouze ten spoustec, ktery ma navazanou uálost, a poté proceduru.

• Česky se tomu někdy říká „spoušť“
• Trigger je „uložená procedura“, to znamená, že uvnitř triggeru lze provádět většinu věcí, které umí uložené procedury. V triggeru lze mít například smyčku, podmínku, lokální proměnnou, matematický výpočet a podobně.
• Spouští se „v souvislosti“ s akčním dotazem. Trigger se může spustit buď předtím nebo poté, co jsou změny v datech zapsány do databáze.
• „Akční dotaz“ znamená, že trigger lze spustit při vkládání dat, při jejich aktualizaci nebo při odstraňování dat z databáze
• Mají stejnou syntaxi jako uložené procedury, přesto se od nich v lecčems liší. Triggerům není možné předávat žádné vstupní parametry.
• Triggery navíc narozdíl od uložených procedur nemohou vracet sadu záznamů
• Pro MySQL platí, že se v triggeru nesmí objevit přinejmenším tyto příkazy: Příkazy ALTER, Příkazy pro řízení transakcí (START TRANSACTION, ROLLBACK, COMMIT), Volání procedur (CALL), Příkazy pro nastavování práv (GRANT, REVOKE) a některé další příkazy.

create trigger trSaveRows
before delete
on bezpecna
for each row
begin
insert into bezpecna_zaloha(id, jmeno, plat, cas_odstraneni,
uzivatel)
values (old.id, old.jmeno, old.plat, now(), user());
end;

EMBEDED SQL / dynamic SQL??