Databázové modely a jazyky/SQL
Obsah
Sémantika SQL[editovat | editovat zdroj]
SELECT | |
---|---|
tabulka s RČ zákazníků a počtem jejich výpůjček,
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ýraz = <název sloupce>, <konstanta>, (DISTINCT) COUNT( <název sloupce> ), [DISTINCT] [ SUM | AVG ]( <výraz> ), [ MIN | MAX ]( <výraz> )
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> )
|
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:
create table testovaci_tabulka ( cislo number(5,1) primary key, retezec varchar2(123), datum date not null);
|
|
JOINy (od SQL92)[editovat | editovat zdroj]
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 (🎓🎓🎓)[editovat | editovat zdroj]
Zážitky ze zkoušek |
---|
|
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[editovat | editovat zdroj]
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. (🎓)[editovat | editovat zdroj]
Zážitky ze zkoušek |
---|
|
vyhody/nevyhody: |
---|
|
- novinka v SQL:99 (SQL3)
podpora v reálných DB |
---|
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)[editovat | editovat zdroj]
- 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 |
---|
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í
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 |
---|
|
Algoritmy implementace relacních operací. Vyhodnocování a optimalizace dotazů (🎓)[editovat | editovat zdroj]
Zážitky ze zkoušek |
---|
|
Vyhodnocování (Algoritmy implementace relacních operací)[editovat | editovat zdroj]
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[editovat | editovat zdroj]
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[editovat | editovat zdroj]
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[editovat | editovat zdroj]
- po strankach
- nejdrive selekci a pak spojeni
- spojeni vice relaci - pro vnější cykly použiji menší data
b) setrideni-slevani ()[editovat | editovat zdroj]
- klasicke trideni na vnejsi pameti, pak slévání (spojování)
- varianta s ukazateli
c) hasovani (nejlepsi pro malo pameti)[editovat | editovat zdroj]
💡 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[editovat | editovat zdroj]
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 (🎓)[editovat | editovat zdroj]
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 |
---|
|
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)
- Abstraktní Datové Typy (ADT) - "třídy"
další řešení: ORM - nevýhoda: 15-20% pomalejší
- př: (N)Hibernate, Entity Framework...
další zdroje |
---|