Databázové modely a jazyky/SQL

Sémantika SQL

SELECT

tabulka s RČ zákazníků a počtem jejich výpůjček, přičemž zákazníci jsou z Prahy a mají vypůjčeny aspoň dvě kopie, tabulka je setřízená sestupně): SELECT rod_č, COUNT(č_kopie) AS počet_kopií FROM Výpůjčky V, zákazníci Z WHERE V.rod_č = Z.rod_č AND Z.adresa LIKE ‘%Praha%‘ GROUP BY V.rod_č HAVING COUNT(č_kopie) > 2 ORDER BY počet_kopií DESC Kde výrazy mohou být sloupce, sloupce s agregačními funkcemi, výsledky dalších funkcí ... výraz = <název sloupce>, <konstanta>, (DISTINCT) COUNT( <název sloupce> ), [DISTINCT] [ SUM | AVG ]( <výraz> ), [ MIN | MAX ]( <výraz> ) a navíc lze použít operátory +,-,*,/. zdroje jsou tabulky nebo vnořené selecty výrazy i zdroje být přejmenovány pomocí AS, např. pro odkazování uvnitř dotazu nebo jména na výstupu (od SQL-92) podmínka je logická podmínka (spojovaná logickými spojkami AND, OR) na hodnoty dat ve zdrojích: podmínka = <výraz> BETWEEN <x> AND <y>, <výraz> LIKE "% ... ", <výraz> IS [NOT] NULL, <výraz> > = <> <= < > [<výraz>/ ALL / ANY <dotaz>], <výraz> NOT IN [<seznam hodnot> / <dotaz>], EXIST ( <dotaz> ) GROUP BY znamená agregaci podle unikátních hodnot jmenovaných sloupců (v ostatních sloupcích vznikají množiny hodnot, které se spolu s oněmi unikátnímí vyskytují na stejných řádkách HAVING označuje podmínku na agregaci ORDER BY definuje, podle hodnot ve kterých sloupcích nebo podle kterých jiných výrazů nad nimi provedených se má výsledek setřídit (ASC požaduje vzestupné setřídění, DESC sestupné)

Structured Query Language je standardní neprocedurální jazyk pro přístup k relačním databázím. Jeho syntaxe odráží snahu o co nejpřirozenější formulace požadavků -- je podobná anglickým větám.

Praci s nim lze rozdelit na dve hlavni pouziti:

Klastrovaný vs. obyčejný index

DDL - data definition language (jazyk pro definici dat) CREATE/ALTER/DROP TABLE/INDEX/TRIGGER/SEQUENCE/VIEW Př.: Oracle DDL syntaxe: create table testovaci_tabulka ( cislo number(5,1) primary key, retezec varchar2(123), datum date not null); vytvori tabulku testovaci_tabulka obsahujici 3 sloupecky - cislo, do ktereho se vejdou cisla se ctyrmi cislicemi pred a jednou za desetinnou carkou. Sloupecek cislo je PK tabulky. Dale retezec pro ulozeni az 123 znaku dlouheho retezce a datum, ktere nemuze byt prazdne. indexy integritní omezení - CREATE DOMAIN/CONSTRAINT ref.integrita - FOREIGN KEY/ON DELETE/ON UPDATE

DML - data manipulation language (manipulace s daty) SELECT A₁,...,Aⱼ FROM R₁,...,Rₖ WHERE φ ≅ (R₁×...×Rₖ)(φ)[A₁,...,Aⱼ] INSERT INTO t (...) VALUES (...) insert into testovaci_tabulka (cislo, retezec, datum) values (4.3, 'ahoj svete!', to_date('17.1.2007', 'dd.mm.yyyy')); UPDATE t SET (...) WHERE ... DELETE FROM t WHERE ... aggregační fce: COUNT/MAX/AVG/SUM hodnotové výrazy CASE-WHEN-THEN COALESCE(vypujcky.cena, "vypiš tohle pokud cena IS NULL") NULLIF prefikáty LIKE/MATCH/UNIQUE/ANY/ALL/SOME kvantifikátory EXISTS množinové operace UNION/INTERSECT/EXCEPT pohledy

JOINy (od SQL92)

INNER - pouze od. řádky z obou tabulek

FULL OUTER JOIN - všechny řádky z obou tabulek, pokud chybí odpovídající na levé/pravé straně doplní se null

LEFT OUTER JOIN - všechny řádky z levé tabulky, pokud chybí odpovídající na pravé straně doplní se null

• analogicky RIGHT

CROSS JOIN - všechno se vším, kartézský součin

• vysvetleni joinů: http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

SQL a jeho standardy (🎓🎓🎓)

Zážitky ze zkoušek

SQL (2015, konzultace) - "když dostanete SQL a dáte špatně syntaxi SELECTu na papír, tak vás na tom u státnic vykostí" SELECT - srovnani SQL-89 a SQL-92 (2014, Skopal) - Az ve standardu SQL-92 pribyly prikazy spojeni, do te doby se musel pouzivat kartezsky soucin jako SELECT * FROM table1, table2 Pomoci klicovych slov definovanych standardem SQL-89 neslo ziskat vysledek ekvivalentni vysledku, kde je pouzit OUTER JOIN. Vnejsi spojovani muze do vysledku zahrnout null hodnoty, ale kartezskym soucinem to neudelame. SQL standardy (2011, Pokorny) - pohoda, rikam si. Nejak jsem to tam vypsal, co se ktery rok udalo. Projizdel to a pak se zarazil u rekurzivniho SQL - to byla zasadni chyba, ze jsem zminil! Hned se v tom zacal hrabat a padala slova jako Minimalni pevny bod, Tranzitivni uzaver a jine sproste vyrazy. Docela jsem si zaplaval, ptz hledat Min PB v SQL vyrazech neni zrovna moje kazdodenni hobby. Neco jsem tam nakonec vzdy vymyslel, zkousejici byl hodny a trpelivy. Nakonec se tvaril v pohode. Ne horsi nez 2, vypadal spokojene SQL SELECT (2009, Skopal) - Mal to byt popis syntaxe. Teda co vsetko sa da selectom zapisat. Kolko ma casti, co robia, opytal sa ako sa vyhodnocuju, popisal som rozne spojenia a pokracovali sme pokecom nad vnorenymi dotazmi a fungovanim IN, ALL, EXIST. Tiez sme niekde po ceste chvilu kecali o agregovani riadkov, agregacnych funkciach. Tiez celkom v pohode.

SQL je neproceduralni jazyk slouzici pro praci se SRBD. Nerika jak se to, co chceme, ma udelat, ale jen co se ma udelat. Implementacni detaily nechava na databazi. Jednotlive databaze (ORACLE, MS-SQL, MySQL, ...) maji mirne odlisne dialekty SQL, i kdyz existuji standardy (sql86, sql89, sql92, sql1999, sql2003, sql2006). V zakladnich vecech se krome nazvu datovych typu ovsem moc nelisi.

Standardy SQL

SQL je standard podle norem ANSI/ISO a existuje v několika (zpětně kompatibilních) verzích (označovaných podle roku uvedení):

• SQL-86 - první nástřel, průnik implementací SQL firmy IBM
• SQL-89 (SQL1) - malá revize motivovaná komerční sférou, mnoho detailů ponecháno implementaci
  • 💡 spojení je možné pouze přes SELECT ... FROM A1,A2 WHERE... což umožňuje pouze vnitřní spojení (INNER) a kart.součin (CROSS) - tj. klíč.slovo JOIN ještě není
• SQL-92 (SQL2) významná revize (∃ ve 4 verzích: Entry, Transitional, Intermediate a Full)
  • přidán JOIN - a jeho další druhy, hlavně OUTER JOIN, ktery standardem SQL-89 neslo provest (kartezskym soucinem nelze ziskat NULL hodnoty) - viz info o JOIN
  • nove datove typy (DATE, TIME, VARCHAR, ...)
  • INFORMATION_SCHEMA.TABLES - metadata tabulek jako tabulka
  • množ.operace
  • kaskádové mazání/aktualizace podle cizích klíčů, kurzory, výjimky
  • Definovany urovne izolace transakci: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ a SERIALIZABLE
• SQL:1999 (SQL3) - regulární výrazy, rekurzivní dotazy, triggery (procedura co se spousti v rekci na nejakou udalost), booleovské typy, ...
  • procedurální rozšíření SQL - některé objektově orientované rysy , stored procedures
  • nové datové typy -- reference, pole, full-text, boolean
  • rekurze (CTE) - konečně můžeme udělat tranzitivní uzávěr
  • podpora pro externí datové soubory, multimédia
• SQL:2003 (některé XML rysy, generátor sekvencí, MERGE, CREATE TABLE LIKE, ...)
  • větší ARRAY, SET, MULTISET
• SQL:2006 – definuje širší využití XML, integrace XQuery, ...
• SQL:2008
• SQL:2011

Komerční systémy implementují SQL podle různých norem, někdy jenom SQL-92 Entry, dnes nejčastěji SQL-99, ale nikdy úplně striktně. Některé věci chybí a naopak mají všechny spoustu nepřenositelných rozšíření -- např. specifická rozšíření pro procedurální, transakční a další funkcionalitu (T-SQL (Microsoft SQL Server), PL-SQL (Oracle) ). S novými verzemi se kompatibilita zlepšuje, často je možné používat obojí syntax. Přenos aplikace za běhu na jinou platformu je ale stále velice náročný -- a to tím náročnější, čím víc věcí mimo SQL-92 Entry obsahuje.Pro otestování, zda je špatně syntax SQL, nebo zda jen daná databázová platforma nepodporuje některý prvek, slouží SQL validátory (které testují SQL podle norem).

Rekurze v SQL. (🎓)

Zážitky ze zkoušek

Rekurze v SQL (2016, Pokorny) - Chcel vediet kedy bolo standardizovane, ako vyzera dotaz a porovnat s rekurziou v datalogu (oproti datalogu je mozna len linearna forma rekurze). Rekurzivní SQL (Pokorny?) - Hned se v tom zacal hrabat a padala slova jako Minimalni pevny bod, Tranzitivni uzaver a jine sproste vyrazy. Docela jsem si zaplaval, ptz hledat Min PB v SQL vyrazech neni zrovna moje kazdodenni hobby. Neco jsem tam nakonec vzdy vymyslel, zkousejici byl hodny a trpelivy. Nakonec se tvaril v pohode. Ne horsi nez 2, vypadal spokojene

vyhody/nevyhody:
+ vše jedním dotazem + lze využít velkou část výsledku - časté využití pouze malé části výsledku - možnost zacyklení rekurze

• novinka v SQL:99 (SQL3)

podpora v reálných DB

ktere DB systemy podporuji rekurzi ( http://stackoverflow.com/questions/324935/mysql-with-clause ) MS SQL podporuje od verze MS SQL Server 2005 Orace od 9i release 2 Oracle pro rekurzi obsahuje také proprietární řešení (s omezenějšími možnostmi) s pomocí speciálních klauzulí START WITH a CONNECT BY příkazu SELECT MySQL ne (feature request od 2006: http://bugs.mysql.com/bug.php?id=16244) SQLite podporuje az od 2014-02-03 (3.8.3) ( http://www.sqlite.org/changes.html ) online vyzkouseni: http://data.stackexchange.com/stackoverflow/query/new priklad s konstrukty bez smyspluplne rekurze: WITH UsersAndPosts (CreationDate, DisplayName) AS ( SELECT p.CreationDate, u.DisplayName FROM Posts AS p INNER JOIN Users AS u ON p.OwnerUserId = u.Id [UNION ALL] // nepovinne rekurzívní člen // nepovinne ) SELECT * FROM UsersAndPosts

Common Table Expression (CTE)

• vytvoří se dočasná tabulka (existuje pouze v době vyhodnocování dotazu)
• deklaruje se klíčovým slovem WITH RECURSIVE jméno_CTE[(jméno_sl[,jméno_sl]…)] AS (CTE_definice_dotazu)
  • V CTE pro tabulku R se lze odkazovat na R

WITH RECURSIVE
  ukotvení (inicializační poddotaz) - spouští se jednou
  UNION ALL
  rekurzívní člen - opakovaně
    •rekurze běží pokud není přidán žádný další záznam anebo není překročený limit rekurze (MAXRECURSION)
    •pozor na zacyklení rekurzívního členu
    INNER JOIN - spojení s minulým krokem
  SELECT
    •Vnější SELECT - dá výsledek dotazu(výstup)

Příklad: Najdi všechny nadřízené Nového (včetně něho sama)

WITH RECURSIVE Nadřízení(jméno, č_nad, č_zam) AS
(SELECT jméno, č_nad, č_zam
  FROM Zaměstnanci
  WHERE jméno = 'Nový'
  UNION ALL
SELECT Z.jméno, Z.č_nad, Z.č_zam
  FROM Zaměstnanci AS Z
  INNER JOIN
  Nadřízení AS N
  ON N.č_nad = Z.č_zam)
SELECT * FROM Nadřízení

Příklady

Příklad z přednášky Tabulka: Zaměstnanci(č_zam, jméno, funkce, č_nad) Najdi všechny nadřízené Nového (včetně něho sama) WITH RECURSIVE Nadřízení(jméno, č_nad, č_zam) AS (SELECT jméno, č_nad, č_zam FROM Zaměstnanci WHERE jméno = 'Nový' UNION ALL SELECT Z.jméno, Z.č_nad, Z.č_zam FROM Zaměstnanci AS Z INNER JOIN Nadřízení AS N ON N.č_nad = Z.č_zam) SELECT * FROM Nadřízení Příklad Mame tabulku Zamestnanci(jmeno, plat, vedouci). Najdete pomoci rekurzivniho dotazu vsechny zamestnance s platem nad 100 000, kteri jsou (i neprimi) podrizeni Ryby. WITH RECURSIVE PodRybou(jméno) AS (SELECT jméno FROM Zamestnanci WHERE vedouci = “Ryba” UNION ALL SELECT jméno FROM Zaměstnanci Z, PodRybou P WHERE Z.vedouci = P.jmeno ) SELECT * FROM PodRybou WHERE plat > 100 000

další zdroje
http://www.ksi.mff.cuni.cz/~pokorny/dj-new/DJ2-3-rekurze.pdf

Algoritmy implementace relacních operací. Vyhodnocování a optimalizace dotazů (🎓)

Zážitky ze zkoušek

Algoritmy implementace operace relacni algebry (2016, Hoksza) - Bez pripravy som z hlavy popisal nieco o hniezdenych cykloch, setrideni a slevani a jemne spomenul hashovanie. Nasledne mi povedal, aby som tieto metody popisal z hladiska vyhodnosti pri rozlicnych velkostiach spojovanych tabuliek. Celkovo bez problemov. SQL + vyhodnocovanie a optimalizácia dotazov (Richta) - bol super, dokonca mu nevadilo, že som zabudol popísať optimalizáciu, stačilo len 3 vetami vysvetliť, o čo ide Implementace operací relační algebry (JOIN)

Vyhodnocování (Algoritmy implementace relacních operací)

CPU je rychle a levne, RAM je taky relativne dost, dulezity je pocet pristupu na disk

indexace je delana b+ stromy s vysokym poctem nasledniku (50-100), ale tak, aby se jeden uzel vesel do jedne stranky na disku a byla nizka uroven (pocet pater) indexu

1. selekce

SELECT * FROM R WHERE A = 'a'

ruzne vysledky podle toho, zda A je PK (unique), sekundarni klic (nemusí být unique), A je hasovany klic:

• sekvencni vyhledavani - v průměrném případě, je- li A PK stačí prohledat půlku (PK zarucuje unikatnost, v okamžiku, kdy naleznu první záznam, nemusím pokračovat) v nejhorším musím projí celé
• binarni vyhledavani (je-li R usporadana podle A) - log₂(#počtu stránek) pro PK, pripadne plus nacteni dalsich bloků se shodnou hodnotou, neni-li to PK
• existuje-li index - průchod stromem + nějaké malé hledání na disku
• vyhledani s hasovanim – přibližně 1 přístup (záleží na hašovací funkci).

2. vypocet spojeni

SELECT * FROM R, S WHERE R.A = S.A

zakladni metody hnizdene cykly, trideni- slevani, hasovane spojeni, kartezsky soucin (spec. případ spojení)

a) hnízděné cykly

• po strankach
• nejdrive selekci a pak spojeni
• spojeni vice relaci - pro vnější cykly použiji menší data

b) setrideni-slevani ()

• klasicke trideni na vnejsi pameti, pak slévání (spojování)
• varianta s ukazateli

c) hasovani (nejlepsi pro malo pameti)

GRACE h(x) = x mod 3

💡 nejsou-li indexy pro R.A a s.A, nemusi-li byt vysledek setrizen (jde taky vyrobit vysledek a pak ho dodatecne setridit)

• klasicke hasovani (predpokladame, ze se tabulka vejde do pameti)
• GRACE algoritmus - rozdel pomoci hashovaci fce obě tabulky do kapes ukazatelu (skupiny ukazatelu na prvky se stejnym hashem). Pro kazdou kapsu zvlast nacti do pameti a otestuj R.A = S.A a pripadne zapis do vystupniho bufferu. Vhodne, pokud se mi jednotlive kapsy vejdou do pameti.
  • vylepseni: jednoduche hashování (hashuju na 2 půlky) - vzdy zahashuju do paměti jen 1. kapsu, zbytek (2. kapsu) hodím na disk, pokud je zbytek moc velký přehashuji na mensí, takhle postupne zahashovávám
  • vylepseni: hybridní - cyklické prehashovani pokud zaplním pamet

dalsi operace - GROUP BY, DISTINCT se resi pres hasovani, nebo rozdelenim pomoci indexu (pokud je), pripadne trizenim

Optimalizace

aneb "Mnoho psů JOINů zajícova smrt."

Faze zpracovani dotazu:

1. prevod do vnitrni formy (typicky nejaka relacni algebra) + 2. konverze do kanonickeho tvaru

3. algebraicka optimalizace - přerovnání selekcí, projekcí, spojení

Rule-based optimalizace

A rule based optimizer is an optimizer that just applies a set of rules to a SQL statement instead of looking at cost estimates in order to determine what the best way is to execute that SQL statement. Oracle actually allows you to use either the rule based or cost based optimizer, although Oracle says that rule based optimization will be deprecated in a future release, so it highly recommends the use of cost based optimization.

Heuristiky:

• selekce co nejdrive
  • nebo lépe: přeskupení relací ve stromu dotazu tak, aby selekce produkující menší relace byly volány dříve
• projekce co nejdrive
  • nebo lépe: sjednotit vice operaci selekce (projekce) do jedne
• transformace $ \times $ na *

Cost-based optimalizace

• databáze spočítá pro všechny možné plány vyhodnocení dotazu jeho odhadovanou cenu (na základě statistik, které si ukládá – např velikost jednotlivých tabulek, blokovací faktor, poměr mezi rychlostí paměti a disku, historgram sloupců – pro rozhodnutí, zda se vyplatí použít index nebo ne, apod.) a následně vybere nejlevnější možnost, může se ukázat, že odhady nebyly správné, v průběhu prvního spuštění dotazu se počítá doba jednotlivých kroků a nakonec se přepočítá volba algoritmu s aktuálními hodnotami - při dalším spuštění již dotaz běží optimálně

• katalogy
• redukční faktor
• histogramy - rozdělení dat

4. Plan vyhodnoceni - strom dotazu + algoritmus pro kazdou operaci

Pro vsechny uvazovane plany se spocita odhadovana cena dotazu. To se udela s vyuzitim statistik o tabulce(tabulkach) a znalosti existence a typu indexu. Plan s nejmensi cenou se vybere.

Priklady ruznych planu a jejich cen (selekce prvni, selekce druha)

Dotazy nad vice tabulkami se resi vybranim nejlepsiho reseni pro jednu kazdou tabulku a pak vybranim nejlepsiho spojeni. spojuje se vzdy s jednou tabulkou (1 s 1, 2 s 1, ... n-1 s 1)

Slozitejsi dotaz je rozlozen na bloky (treba hlavni cast dotazu a vhnizdeny dotaz), ktere se optimalizuji zvlast.

• http://kocour.ms.mff.cuni.cz/~pokorny/vyuka/dj1/DJ1-vyhodnoceni.pdf
• http://kocour.ms.mff.cuni.cz/~pokorny/vyuka/dj1/DJ1-optimalizace.pdf

Objektové rozšíření relačního modelu dat (🎓)

příklad ADT:
CREATE TYPE T_Student AS ( Jm CHAR(30), Adresa CHAR(40), Zac_ studia DATE ) UNINSTANTIABLE NOT FINAL METHOD Poc_Prednasek() RETURNS INTEGER;

Zážitky ze zkoušek

Objektové rozšíření relačního modelu dat (2014, Pokorný) - K této otázce jsem toho upřímně zase tak moc nevěděl. Jen jsem napsal, že lze definovat vlastní typy, třídy, metody, že existují pole a reference (REF) a že je to implementováno v SQL. Pak jsem s panem Pokorným vedl diskusi, kde se vyptával na různé věci, např. chtěl vědět, jestli může existovat třída sama o sobě (ne - musí být vždy uložená v tabulce), chtěl slyšel o ID v třídách, jak jsou uložené v tabulkách, kde se definují metody (ve třídách), apod. Nějak jsem to s jeho pomocí vyplodil a celkově byl myslím spokojený.

svět db technologií

bylo potřeba nějak zlepšit práci s BLOB/CLOB ⇒ rozšiřitelnost o nové datové typy

• Oracle - cartridges
• SQL Server - cartridge/ASSEMBLY

Ukotveno standardem SQL:1999:

• Uživatelské Datové Typy (UDT)
  • Abstraktní Datové Typy (ADT) - "třídy"
    • možnost vytvářet vlastní strukturované datové typy zapouzdřující data a operace (např. porovnávací operace)
    • píšou se v SQL, C, C# ...
    • mohou mít dědění a polymorfismus
    • [UN]INSTANTIABLE - povolení/zakazani vytvareni instanci
    • [NOT]FINAL - povolení/zakazani dědění
    •  ????může existovat třída sama o sobě? ne pouze v tabulce
    • Uživatelské Datové Funkce/Procedury (UDF/UDP)
      • metody svázány s typem, fce ne
    • Pojmenované řádkové typy
      • řádka je identifikována pomocí omělého klíče (OID – Object IDentificator)
  • Kolekce: ARRAY
    • LIST, BAG, SET, MULTISET (až v 2003)
  • Reference - REF - umožňuje chápat data jako objekty
    • operátory DEREF a ->
    • operátor IS DANGLING (odkazovaný objekt už neexistuje)

další řešení: ORM - nevýhoda: 15-20% pomalejší

• př: (N)Hibernate, Entity Framework...

další zdroje
Databázové aplikace - slidy http://www.ksi.mff.cuni.cz/~pokorny/dj-new/DJ1-2-ORSRBD.pdf