Obsah

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

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.

Příklady

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.

Příklady

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.

  • 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ší

Příklady

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é.

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í.

Atomické operace

Definice dle Wikipedie:

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ů.

Příklad atomické a neatomické operace:

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:

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:

Nevýhody:

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:

Syntax (databáze Oracle):

CREATE [OR REPLACE] TRIGGER jmeno {BEFORE | AFTER | INSTEAD OF}
udalost [FOR EACH ROW] [WHEN (podminka)]
blok

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.

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.

Příklady

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:

Co byste ještě měli znát?

Předměty

Použitá literatura

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.

1)
kapitola 13 strana 3 ve skriptech předmětu PB154 Základy databázových systémů
2)
Převzato z Wikipedie --- Spouště
3)
Převzato z Materiály ke cvičení předmětu PV003, autorem je RNDr. Vlastislav Dohnal