====== AP10, IN10 SQL ======
===== Zadání =====
SQL (syntaxe a sémantika příkazů; vestavěné funkce, triggery, uložené procedury, příkazy pro definici dat; transakční zpracování; atomické operace; optimalizace dotazů)
===== Základní pojmy =====
* **SQL** (dříve SEQUEL) -- Structured Query Language. Jedná se o standardizovaný dotazovací databázový jazyk, jehož vývoj byl započat firmou IBM a který má několik verzí standardů, které postupem času vznikly.
* ANSI -- americký institut, který vydává standardy pro jazyk SQL.
* SQL:1999 (nebo SQL-3) -- standard pro jazyk SQL, který obsahuje objektové prvky. V praxi se tato norma ne vždy plně implementuje a zpravidla bývá rozšiřována prvky, které jsou pro každý databázový systém (DBMS) specifické.
* SQL:2008 -- nejnovější norma SQL. Možno použít ORDER BY vně kurzorů, přidává triggery typu INSTEAD OF a příkaz TRUNCATE.
* **Transakce** -- pojem, který označuje posloupnost operací (část programu, sekvenci příkazů jazyka SQL), která přistupuje a aktualizuje (mění) data. Transakce pracuje s konzistentní databází, během spouštění transakce může být databáze v nekonzistentním stavu, ale po úspěšném dokončení transakce musí být databáze konzistentní. (více v samostatné části níže)
* **DDL** (Data Definition Language) -- podjazyk pro definici dat, který slouží pro specifikaci definice schématu databáze
* **DML** (Data Manipulation Language) -- podjazyk pro manipulaci s daty, který slouží pro práci s obsahem tabulek
* **DCL** (Data Control Language) -- podjazyk pro řízení práce s daty, který obsahuje správu transakcí, nastavení práv atp.
* Syntax -- je dobrou zvyklostí psát příkazy velkými písmeny a názvy objektů obalovat speciálními znaky, aby tak nedocházelo ke kolizím mezi názvy objektů a rezervovanými klíčovými slovy (tzv. escaping). U MS SQL Serveru jsou těmito znaky hranaté závorky, v případě PostgreSQL uvozovky, MySQL používá zpětné apostrofy. Za každým příkazem může a nemusí být oddělující středník. Řádkové komentáře realizujeme dvěma pomlčkami, blokové klasicky lomítkem a hvězdičkou.
===== Vestavěné funkce =====
Jazyk SQL obsahuje i vestavěné funkce, mezi ně patří především tzv. agregační funkce, ale dále do nich patří např. funkce pro práci s řetězci (''SUBSTRING'', ''LOWER'' atd.), pro zjištění aktuálního data a času atd. Množina dostupných funkcí je hodně závislá na zvoleném DBMS, kterým může být Oracle, Postgresql, SQL Server atd.
* ''COUNT'' -- počet záznamů ve vrácené množině. Aplikuje-li se ve formě "COUNT(*)", tak je vrácen počet všech záznamů; pokud je aplikován ve tvaru "''COUNT(nazev_sloupce)''", tak je vrácen počet záznamů, které nemají ve sloupci "nazev_sloupce" hodnotu ''NULL''.
* ''AVG'' -- průměr dané hodnoty ve všech záznamech.
* ''SUM'' -- součet všech hodnot daného sloupce.
* ''MIN, MAX'' -- minimální, resp. maximální, hodnota daného sloupce v záznamech.
**Počet všech produktů, které splňují podmínku, že ''ID'' je větší než ''1''**
''SELECT COUNT(*) FROM [Products] WHERE nRowID > 1''
**Počet všech produktů, které mají vyplněný popis (tj. není ''NULL'')**
''SELECT COUNT(sDescription) FROM [Products]''
**Průměrná mzda zaměstnanců**
''SELECT AVG(mSalary) FROM [Zamestnanci]''
**Počet kusů všech produktů**
''SELECT SUM(nCount) FROM [Products]''
**Minimální a maximální mzda zaměstnanců**
''SELECT MIN(mSalary), MAX(mSalary) FROM [Employees]''
**Přetypování řetězce na celé číslo**
''CAST ('987' AS INT)''
===== Jazyk pro definici dat (DDL) =====
Definuje sadu příkazů, které lze použít pro vytváření, úpravu a odstraňování objektů v databázi. Nejčastěji spravovanými objekty jsou: tabulky, pohledy, procedury, funkce.
* ''CREATE'' -- vytvoření objektu.
* ''ALTER'' -- úprava. V případě změny tabulky se může jednat o přidání/úpravu/odstranění sloupce, změny datového typu sloupce, změny relace a jiných integritních omezení. Příklad přidání sloupce do tabulky: ''ALTER TABLE [Nazev_Tabulky] ADD COLUMN [sValue3] Boolean''.
* ''DROP'' -- odstranění. Příklad odstranění procedury: ''DROP PROCEDURE [Procedura1]''.
**Vytvoření tabulky**
''CREATE TABLE [Nazev_Tabulky] (nRowID Int PRIMARY KEY, sValue1 Varchar, sValue2 Varchar)''
**Vytvoření tabulky s tím, že je vynuceno zadání hodnoty pro název produktu**
''CREATE TABLE [Products] (nProductID Int PRIMARY KEY, sName NOT NULL)''
**Vytvoření tabulky s popisy produktů, které navazují na tabulku s produkty vytvořenou výše**
''CREATE TABLE [ProductDescriptions] (nProductID Int PRIMARY KEY, sDescription Varchar, CONSTRAINT vazbaProdukt FOREIGN KEY (nProductID) REFERENCES [Products] (nProductID))''
**Vytvoření tabulky s kontrolou, že je hodnota rodného čísla unikátní pro každého studenta (''UNIQUE'')**
''CREATE TABLE [Students] (nStudentID Int PRIMARY KEY, sBirthNumber Varchar UNIQUE)''
**Vytvoření tabulky s kontrolou, že je hodnota platu vždy kladná hodnota (CHECK)**
''CREATE TABLE [Employees] (nEmployeeID Int PRIMARY KEY, sName Varchar, mSalary Decimal, CONSTRAINT plat CHECK (mSalary > 0))''
**Vytvoření tabulky s výchozí hodnotou na sloupci (DEFAULT)**
''CREATE TABLE [Nazev_Tabulky] (nRowID Int PRIMARY KEY, sValue1 Varchar, sValue2 Varchar DEFAULT 'výchozí hodnota')''
===== Jazyk pro manipulaci s daty (DML) =====
Množina příkazů, které se využívají pro výběr, vkládání, úpravu a mazání dat v tabulkách.
* ''INSERT'' -- vloží záznam do právě jedné tabulky.
* ''UPDATE'' -- upraví libovolný počet záznamů v právě jedné tabulce.
* ''DELETE'' -- odstraní libovolný počet záznamů z právě jedné tabulky.
* ''SELECT'' -- vybírá data z databáze, umožňuje výběr, agregaci a řazení dat. (odpovídá operaci projekce v relační algebře)
* klíčové slovo ''DISTINCT'' -- slouží k eliminaci duplikátů ve výsledku dotazu (ALL duplikáty ponechává)
* klauzule ''WHERE'' -- odpovídá operaci selekce v relační algebře
* klauzule ''FROM'' -- odpovídá kartézskému součinu z relační algebry
* klauzule ''ORDER BY'' -- uspořádá výpis sestupně (DESC) či vzestupně (ASC)
* klauzule ''GROUP BY'' -- umožňuje aplikovat souhrnné funkce, klauzule HAVING se aplikuje až po vytvoření skupin
* operace ''JOIN'' -- spojí dvě relace a vrátí jednu relaci, hlavní typy spojení: vnitřní, vnější
**Vložení produktu do tabulky**
''INSERT INTO [Products] (nProductID, sName) VALUES (1, "Produkt cislo 1")''
**Úprava produktu**
''UPDATE [Products] SET sName = "Novy nazev produktu 1" WHERE [nProductID] = 1''
**Odstranění všech produktů**
''DELETE FROM [Products]''
**Výběr všech produktů včetně jejich popisu**
''SELECT prod.nProductID, prod.sName, prod.sDescription
FROM [Products] AS prod
INNER JOIN [ProductDescriptions] prodDesc ON prod.nProductID = prodDesc.nProductID''
**Výběr všech produktů bez ohledu na to, mají vyplněný popis či nikoliv**
''SELECT prod.nProductID, prod.sName, prod.sDescription
FROM [Products] AS prod
LEFT OUTER JOIN [ProductDescriptions] prodDesc ON prod.nProductID = prodDesc.nProductID''
**Výběr skladů seřazených podle jejich naplnění sestupně**
Předpokládejme tabulku se stavy produktů ve skladě: ''CREATE TABLE [ProductsInStores] (nStoreID INT, nProductID INT, nAmount INT, PRIMARY KEY (nStoreID, nProductID))''
pak dotaz může vypadat následovně:
''SELECT nStoreID, SUM(nAmount)
FROM [ProductsInStores]
GROUP BY [nStoreID]
ORDER BY SUM(nAmount) DESC''
===== Jazyk pro řízení dat (DCL) =====
Zpravidla se jedná o příkazy, které slouží k řízení transakcí, nastavení práv a jiné.
* **''BEGIN''** -- spustí transakci s uvedeným názvem, který není povinný. Často se ještě uvádí úroveň izolace, která je použitá během zpracování příkazů kvůli předejití konkurenčním operacím. Příklad: ''BEGIN TRAN [MyTran1]''.
Poznámka: většina databázových systémů podporuje tzv. implicitní transakce, které není třeba explicitně spouštět příkazem ''BEGIN''. Na toto je potřeba si dávat pozor, protože pokud provedené změny manuálně nepotvrdíte příkazem ''COMMIT'', neprojeví se. Databázový systém po nějakém specifikovaném timeoutu transakci zruší a změny se zahodí.
* **''COMMIT''** -- potvrdí transakci. Příklad: ''COMMIT TRAN [MyTran1]''
* **''ROLLBACK''** -- vrátí provedené změny. Může se rušit jak celá transakce, tak její část od určitého bodu až po chvíli, kdy je ''ROLLBACK'' volán (viz. ''SAVE''). Příklad: ''ROLLBACK TRAN [MyTran1]''
* ''SAVE'' -- tento příkaz není součástí standardu, ale většina db systémů jej podporuje. Ukládá stav transakce, do něhož je možné se vrátit, pokud není žádoucí vracet úplně celou transakci. Příklad: ''SAVE TRAN [MyCheckpoint1]''.
===== Atomické operace =====
Pro databázi platí, že transakce by měla být atomická ve vztahu k ostatním transakcím. Tedy transakce A nastala buď před transakcí B, nebo po ní, ale nemohla probíhat současně. Přesněji, ve skutečnosti současně probíhala, ale synchronizace provedená databázovým serverem zajišťuje, že databáze bude vypadat jako kdyby všechny transakce proběhly popořadě. Pokud to nebude možné (dojde k deadlocku), provede ROLLBACK některé transakce a zkusí ji provést znovu na novém stavu databáze. Atomicita transakce zároveň znamená, že pokud transakce selže, selže jako celek, tedy všechny zápisy které provedla budou při rollbacku vráceny a žádná jiná transakce žádný z těchto zápisů neuvidí.
Atomická operace znamená, že její vykonání je nedělitelné -- buď proběhne zcela úspěšně, nebo zcela neúspěšně. Atomickými operacemi jsou například: "vlož //jeden// záznam", "zobraz //jeden// záznam", apod.
Oproti tomu, vykonání neatomické operace je dělitelné: dojde-li při vykonávání příkazu k chybě, operace, které již byly vykonány, nebudou vráceny zpět. To se týká například operací, které mají vliv na větší množství řádků.
**Vložení jednoho záznamu do tabulky**
''INSERT INTO [Products] (nProductID, sName) VALUES (1, "Muj produkt 1")''
**Vložení více záznamů do tabulky překopírováním z jiné tabulky**
''INSERT INTO [Products] (nProductID, sName)
SELECT nProductID, sName FROM [Products2]''
===== Transakce =====
Je posloupnost operací (DML příkazů), které převedou datové schéma z jednoho konzistentního stavu do druhého.
O transakci platí, že je ACID:
* **A: Atomic** -- celá se provede, nebo se odvolá, žádné zbytky nebo vedlejší efekty.
* **C: Consistent** -- na konci není porušeno žádné omezení databáze.
* **I: Isolated** -- operace jsou izolovány od ostatních transakcí.
* **D: Durable** -- po ukončení transakce jsou data trvale uložena.
Každá transakce má určitou úroveň izolace. Pokud se při vytvoření nespecifikuje, použije se výchozí úroveň.
==== Souběžné zpracování transakcí ====
Více transakcí může být spouštěno současně. Výhody jsou:
* Zvýšené využití procesoru a disku, které vede k vyšší transakční propustnosti (//jedna transakce může používat procesor a jiná disk//).
* Snížená průměrná doba odezvy (//krátká transakce nemusí čekat na dokončení dlouhé//). ((kapitola 13 strana 3 ve skriptech předmětu PB154 Základy databázových systémů))
Nevýhody:
* Aby se předešlo problému s přepisováním dat, je nutné zamykání řádků či tabulek.
* Je nutná režie na řešení a předcházení deadlocku.
==== Deadlock ====
Neboli uváznutí, označuje stav, kdy transakce A čeká na prostředky, které drží transakce B a transakce B čeká na prostředky, které má uvolnit transakce A. Vždy se reší zaříznutím jedné z transakcí, nelze čistě vyřešit a je nutné jim předcházet.
Neopakovatelné čtení označuje stav, kdy si jedna transakce přečetla záznam, provedla nějaké příkazy a pak si tento záznam přečetla znovu a už měl jiné hodnoty.
Fantom označuje data, která na první pohled "záhadně" unikají aktualizacím.
===== Uložené procedury =====
Jsou databázové objekty, které neobsahují data, ale programy, které se nad daty v databázi mají vykonávat. Mají své parametry a lokální proměnné, které nejsou zvenku vidět.
Uložená procedura je uložená (rozuměj: uložená v databázi). To znamená, že se k ní lze chovat stejně jako ke každému jinému objektu databáze (indexu, pohledu, triggeru apod.). Lze jí založit, upravovat a smazat pomocí příkazů dotazovacího jazyka databáze.
Pro psaní uložených procedur je obvykle používán specifický jazyk konkrétní databáze, který je rozšířením jejího dotazovacího jazyka. V případě Oraclu to je procedurální jazyk PL/SQL, u SQL Serveru se jedná o T-SQL.
===== Funkce =====
Jedná se o uloženou sadu SQL příkazů, kterou lze parametrizovat. Má návratový typ a často se požaduje, aby byly deterministické. V praxi se to projevuje tak, že nesmí obsahovat prvky nedeterminismu jako například aktuální datum a čas atp.
Funkce je objekt jako každý jiný, takže ji lze spravovat pomocí jazyka DDL.
===== Spouště (Triggery) =====
V databázi specifikuje činnosti, které se mají provést v případě definované události nad databázovou tabulkou. Definovanou událostí může být například vložení nebo smazání dat. Často slouží pro složitější kontrolu integrity dat.
Triggery jako takové jsou definovány ve většině moderních databázových systémů, ovšem mírně se liší v sémantice svého provedení.
Klíčové rozdíly jsou zejména v:
* kdy přesně se trigger spustí
* jak proběhne (co ho může přerušit)
* jakým způsobem se řeší vzájemné volání triggerů, pokud je vůbec umožněno
* jak (a jestli vůbec) jsou ošetřeny nekonečné cykly vzájemného volání ((Převzato z [[http://cs.wikipedia.org/wiki/Trigger_(databáze)|Wikipedie --- Spouště]]))
''CREATE [OR REPLACE] TRIGGER jmeno {BEFORE | AFTER | INSTEAD OF}
udalost [FOR EACH ROW] [WHEN (podminka)]
blok''
* událost je ''UPDATE'', ''DELETE'' nebo ''INSERT'', případně jejich kombinace (zpravidla oddělené klíčovým slovem OR)
* ''BEFORE'' triggery jsou spouštěny před provedením události, ''AFTER'' triggery až po provedení události
* Trigger je spuštěn buďto jednou při provádění příkazu (default) nebo pro každý měněný řádek (''FOR EACH ROW'')
* Podmínky: ''INSERTING, UPDATING, DELETING'' ((Převzato z [[http://www.fi.muni.cz/~xdohnal/lectures/PV003/cv8.html|Materiály ke cvičení předmětu PV003]], autorem je RNDr. Vlastislav Dohnal))
**Robin Hood osobně používal tento Trigger**
CREATE TRIGGER uprav_platy
BEFORE
UPDATE OF plat
ON people
FOR EACH ROW
BEGIN
IF (:new.plat>30000) THEN :new.plat:=28000; END IF;
/* prilis by se namlsali */
IF (:new.plat<12000) THEN :new.plat:=15000; END IF;
/* a chudym pridame */
END;
===== Optimalizace dotazů =====
Existuje celá řada způsobů, jakými lze ovlivnit rychlost zpracování dotazu, níže je seznam těch základních.
* **indexy** -- nejdůležitější technika urychlení dotazů, systém si vede informace o záznamech v podobě stromu
* **vyjmenování sloupců** -- typický dotaz "''SELECT * FROM [Products]''" vybere všechny sloupce z tabulky, které možná ani nepotřebujeme, ale **přinese také zpomalení** z toho důvodu, že systém si musí nejdříve vytáhnout seznam sloupců z definice tabulky, aby je mohl zobrazit. Navíc takový dotaz není odolný proti změnám schématu tabulky.
* **použití operátoru ''LIKE''** -- Obecně není vhodné operátor používat ve tvaru LIKE '%Karel%', v tom případě musí databáze projít všechny zbývající výskyty. To při omezení vhodnou podmínkou nemusí vadit. Tvar LIKE 'Karel%' je naprosto v pořádku a indexy v tomto případě fungují, stejně tak tvar bez procent.
* **dočasné tabulky** -- jejich použití může přinést **výrazné zpomalení**, pokud nejsou používány s citem a manipuluje se v nich s velkým množstvím dat.
Většina dnešních nástrojů nabízí možnost zobrazení plánu provedení dotazů, který velice usnadní jejich ladění.
Operátor ''LIKE'' je výběrový operátor používaný na řetězce, který může obsahovat tzv. wildcards.
**Příklad výběru zaměstnanců, jejichž příjmení začíná na "Eich"**
''SELECT * FROM [Employees] WHERE sLastName LIKE 'Eich%'''
===== Podněty, co lze zmínit dále ======
==== Pohledy (Views) ====
Pohled je databázový objekt, který uživateli poskytuje data ve stejné podobě jako tabulka. Na rozdíl od tabulky, kde jsou data přímo uložena, obsahuje pohled pouze předpis, jakým způsobem mají být data získána z tabulek a jiných pohledů.
=== Materializované pohledy ===
Takto se nazývá speciální varianta pohledů, u nichž jsou výsledky volání ukládány. Rozdíl oproti normálním pohledům je ten, že při opakovaném volání přináší znatelné urychlení a navíc je možné v nich upravovat data. Podmínkou úpravy je to, že v pohledu musí být vybrány všechny sloupce primárního klíče tabulky, jejíž data aktualizujeme. Zpravidla se aktualizují pohledy nad jednou tabulkou, aby byly úpravy průhledné a nedocházelo k nechtěným změnám. Tuto možnost podporuje například Oracle, SQL Server už nikoliv.
**Vytvoření pohledu pro výběr produktů včetně jejich popisu**
''CREATE VIEW [View_Product] AS
SELECT prod.nProductID, prod.sName, prod.sDescription
FROM [Products] AS prod
INNER JOIN [ProductDescriptions] prodDesc ON prod.nProductID = prodDesc.nProductID''
==== Použití programovacích jazyků ====
Moderní databázové systémy většinou umožňují integraci klasických programovacích jazyků do procedur, funkcí a triggerů, takže tímto způsobem je možné s výhodou využít například C++ nebo Javu tam, kde je SQL slabé.
Kupříkladu v SQL Serveru se dají do databáze začlenit knihovny vytvořené v prostředí .NET. Těmto knihovnám pak lze nastavit úroveň přístupu, která je při jejich volání použita, aby se zajistilo, že se třeba nebudou mazat soubory z databázového serveru atp.
==== Úrovně izolovanosti transakcí ====
Existují 4 druhy úrovní izolace dle standardu:
* **Read Uncommitted** -- nejvíce tolerantní. Nepovoluje souběžné aktualizace, ale povoluje čtení doposud nepotvrzených změn.
* **Read Committed** -- méně tolerantní, nepovoluje souběžné aktualizace ani čtení nepotvrzených změn. S jejím použitím lze dosáhnout vysoké propustnosti v databázi, ale při jejím použití nelze vyloučit neopakovatelné čtení a fantomy (viz. pojmy výše).
* **Repeatable Read** -- má stejné vlastnosti jako Read Commited jen s tím rozdílem, že podporuje stabilní pohled na čtená data (tj. zamezuje neopakovatelnému čtení), ale stále mohou vznikat fantomy.
* **Serializable** -- nejvyšší úroveň zabezpečení a blokování použitých prostředků. V případě častého používání transakcí se serializovatelným čtením může docházet k uváznutí a mají vliv na výkon systému, protože k jejich zpracování je třeba značené režie ze strany databázového systému.
===== Co byste ještě měli znát? =====
* **Indexy** (jako techniky urychlení dotazů) byste měli být schopni více rozvést na základě znalostí z otázky [[home:prog:ap12|Organizace souborů]].
* Měli byste být schopni **zadefinovat syntax** SQL pomocí nějakého formálního jazyka (v podstatě popsat gramatiku pro SQL).
===== Předměty =====
* [[https://is.muni.cz/auth/predmety/predmet.pl?id=289846|FI:PV003]] Architektura relačních databázových systémů (jaro 2006), RNDr. Milan Drášil, CSc.
* [[https://is.muni.cz/auth/predmety/predmet.pl?id=289863|FI:PV063]] Aplikace databázových systémů (jaro 2006), RNDr. Pavel Hajn
* [[https://is.muni.cz/auth/predmety/predmet.pl?id=427861|FI:PB154]] Základy databázových systémů (podzim 2007), prof. Ing. Pavel Zezula, CSc.
===== Použitá literatura =====
* [[http://cs.wikipedia.org/wiki/Trigger_(datab%C3%A1ze)|Wikipedie - Spouště]]
* [[http://cs.wikipedia.org/wiki/Pohled_(datab%C3%A1ze)|Wikipedie - Pohledy]]
* [[http://cs.wikipedia.org/wiki/Ulo%C5%BEen%C3%A1_procedura|Wikipedie - Uložené procedury]]
* [[http://is.muni.cz/el/1433/podzim2007/PB154/index.qwarp|Osnova předmětu PB154 a z ní odkazované dokumenty k předmětu PB154]] (česky přeložené dokumenty jsou jen pro přihlášené v IS MU)
* [[https://is.muni.cz/auth/el/1433/podzim2007/PB154/um/czech/zezula13.pdf|Kapitola 13: Transakce]] přístupný jen pro přihlášené v IS MU, autorem je prof. Ing. Pavel Zezula, CSc.
* [[https://is.muni.cz/auth/el/1433/podzim2007/PB154/um/czech/zezula04.pdf|Kapitola 4: SQL]] přístupný jen pro přihlášené v IS MU, autorem je prof. Ing. Pavel Zezula, CSc.
===== Vypracuje =====
Petr Koutný, ICQ: 223-009-182. Nemám patent na rozum, takže klidně něco upravte, pokud vám to přijde nesmyslný :-)
Otázku si přečetl pan RNDr. Vlastislav Dohnal a rámcově prošel. Jeho podněty pro doplnění textu, opravy nesrovnalostí a odstranění matoucích či k otázce se nevztahujících textů byly do otázky zaneseny. Tato kontrola je jen **rámcová**, stále se může stát, že v otázce zůstala zapomenutá chybka či nesrovnalost, vyučující za toto nenese odpovědnost, berte tuto rámcovou kontrolu jako formu pomoci od vyučujících pro studenty.
~~DISCUSSION~~