SQL. strukturovaný dotazovací jazyk. Structured Query Language (SQL)

SQL strukturovaný dotazovací jazyk

Structured Query Language (SQL)

SQL - historie 1974-75

- IBM - 1.prototyp - SEQUEL od 1979 - do praxe - ORACLE (1979) IBM - SQL/DS (1981), DB/2 (1983) postupně přijímán jako standard pro přístup k datům v relačních databázích Pozn. - QBE = QUERY BY EXAMPLE

Vladimíra Zádová, KIN, EF TUL - DBS

SQL - normy SQL

86 - DDL, DML, DCL 1986 - schválena ANSI norma SQL, základem dialekt DB2, 1987 - přijato mezinárodní standardizační organizací ISO SQL 89 - revize - 1989 rozšíření o specifikaci IO SQL2 - r. 1992 revize, SQL3 - nyní snaha po standardizaci - možnost kompatibility,přenositelnost - SAG konsorcium Vladimíra Zádová, KIN, EF TUL - DBS

Standardizace SQL

Od r. 86 …..

3 hlavní edice standardů - SQL 86, SQL92, SQL99

Menší edice

SQL89

Pozn. : » po přijetí standardu SQL 92 se mluvilo o SQL jako o SQL3

– od roku 1999 mají být standardy označovány : SQL: rrrr (SQL:1999) – SQL3 - přestává se používat, nyní SQL4 » SQL4 chápána jako kontejner, kde všechny budoucí standardy – standard 92 je implementován pouze částečně Vladimíra Zádová, KIN, EF TUL - DBS

SQL je neprocedurální jazyk specifikuje CO požadujeme nikoli jak toho dosáhneme. – DATA DEFINITION LANGUAGE - DDL – DATA MANIPULATION LANGUAGE - DML – DATA CONTROL LANGUAGE - DCL


DATA DEFINITION LANGUAGE - DDL vytváření databáze a relační struktury – vytváření, rušení a aktualizace relací (tabulek) – vytváření a rušení indexů – vytváření a rušení pohledů - (ne alter) – Integritní omezení a DDL (create domain, check, Primary key ) – Datové typy podporované SQL-92


DATA MANIPULATION LANGUAGE DML příkazy

k provádění základní manipulace s

daty : vkládání, modifikace, rušení dat z relací – INSERT – UPDATE – DELETE k výběru dat z databáze - jednoduché i složité dotazy: – SELECT Vladimíra Zádová, KIN, EF TUL - DBS

DATA CONTROL LANGUAGE - DCL příkazy

GRANT, REVOKE

Přikazy pro tvorbu aplikací pro spolupráci SQL s hostitelským jazykem -- příkazy DECLARE CURSOR OPEN CURSOR - před 1. z množiny FETCH CURSOR - přesun o 1 CLOSE CURSOR

příkazy pro uživatelskou transakci ROLLBACK, COMMIT Vladimíra Zádová, KIN, EF TUL - DBS

SQL příkazy – z rezervovaných (klíčových) slov a uživatelem definovaných slov. –

Rezervovaná slova jsou nedílnou součástí příkazů SQL

–

uživatelem definovaná slova jsou slova používaná pro pojmenování databázových relací, sloupců, pohledů 14


Psaní SQL příkazů

Klíčová slova příkazu - velkými písmeny

Uživatelem definovaná slova - malými písmeny

Každá klauzule příkazu začíná na novém řádku

[text] - volitelná část příkazu

[, ....] - možné opak. předchozí části uzavřené v { }

[text | text] možný výběr některé z variant

{text | text} nutný výběr některé z variant

( ) součást příkazu 15


DATA MANIPULATION LANGUAGE


INSERT - vstup dat INSERT INTO název relace [(výčet atributů)] {VALUES( výčet hodnot atributů)| SELECT-příkaz} kde: VALUES -pro vstup právě jednoho řádku relace - pokud všech atributů , pak nemusí být uveden výčet atributů specifikovaných ve výčtu SELECT - pro vstup více řádků najednou ( z 1 či více relací) struktura řádků vrácených vnořeným příkazem SELECT musí být kompatibilní se strukturou naplňované relace .

128


výčet atributů- nepovinné - pokud jsou vkládány hodnoty atributů v tom pořadí, v jakém byly definovány a zároveň budu vkládat hodnoty všech atributů výčet hodnot atributů - je oddělen čárkou, pořadí hodnot atributů a datový typ souhlasí s definicí relace, resp. s pořadím uvedeným ve výčtu atributů; u atributu typu CHAR je hodnota v “ ” pro neznámou hodnotu - použití NULL


Není-li do atributu relace vložena hodnota při INSERTu - pak bude obsahovat : DEFAULT -hodnotu- pokud je určena při definici atributu NULL - není-li určena DEFAULT hodnota

signalizace chyby : pokud u atributu určeno NOT NULL, zároveň není určena DEFAULT hodnota a nebyla-li vložena hodnota byla-li vložena hodnota, která porušuje definovanou jedinečnost hodnoty atributu.


INSERT Př.

INSERT INTO sql-sport.ZAKAZNIK (adresa, discount, mesto, czak) VALUES(" Horní ulice 777", 9, "Brno", 1).

INSERT INTO sql-sport.ZAKAZNIK (adresa,discount, mesto, czak) SELECT Address, Discount, City,Cust-Num FROM sport19.Customer.


UPDATE

- aktualizace dat

UPDATE název relace SET jméno atributu= {NULL | výraz} [, jméno atributu = {NULL | výraz}]... [WHERE podmínky]

Jedním

příkazem lze měnit hodnotu jednoho či více atributů v rámci jedné relace Vladimíra Zádová, KIN, EF TUL - DBS

SET - přiřazuje atributu / ům novou hodnotu ( konstanta, NULL, název atributu či proměnné, aritmetický výraz) WHERE -nepovinné, aktualizuje všechny řádky, které vyhovují podmínce pokud není uvedeno WHERE -aktualizuje dané atributy ve všech řádcích

Systém odmítne aktualizaci, pokud nová hodnota nebude respektovat některá z def. omezení atributu Vladimíra Zádová, KIN, EF TUL - DBS

UPDATE Př.

UPDATE zakaznik SET zeme = zeme + "/EU”

UPDATE radek-obj SET qty = gty + qty/12, cena = cena * 2 WHERE c-pol = 125.


DELETE

-

rušení dat

DELETE FROM název relace [WHERE podmínky] Jedním příkazem lze zrušit jeden či více řádků jedné relace WHERE - vymaže z relace všechny řádky, které vyhovují podmínce Pokud nebude uvedena podmínka , zruší se všechny řádky relace - ale nezruší se definice relace Vladimíra Zádová, KIN, EF TUL - DBS

příkaz SELECT - použití pro výběr dat z jedné relace pro výběr dat z více relací • vnější spojení, vnitřní spojení, samospojení provádění operací (UNION, INTERSECT, EXCEPT). Hnízdění ( nesting) dotazů - do příkazu SELECT, INSERT, UPDATE, DELETE • strukturované dotazy jednoduché a souvztažné

3


SELECT příkaz

SELECT klauzule FROM klauzule [WHERE klauzule ] [GROUP BY klauzule] [HAVING klauzule ] [ORDER BY klauzule] 17


SELECT příkaz

SELECT- povinné, specifikuje sloupce výstupu atributy, konstanty, aritmetické výrazy, agregační funkce FROM - povinné, uvádí relace, z nichž data vybírána WHERE - nepovinné, uvádí podmínky výběru GROUP BY - nepovinné, vytváří skupiny řádků, z jejichž hodnot jsou odvozeny souhrnné hodnoty - s agr.funkcemi HAVING - nepovinné, uvádí výběrové podmínky, vztahuje se ke GROUP BY ORDER BY - nepovinné, k uspořádání řádků výstupní tabulky ( je ve většině implementací, není ve výchozí normě) Vladimíra Zádová, KIN, EF TUL - DBS

SELECT příkaz

Pořadí

klauzulí nemůže být měněno.

Postup

při vyhodnocování : FROM WHERE GROUP BY HAVING ORDER BY

19


SELECT příkaz SELECT - FROM SELECT [ALL|DISTINCT] {*|jméno atributu| výraz}[,...] FROM { jméno relace1 [alias]….. alias (correlation-name)-pokud se budu chtít na danou relaci v příkazu SELECT odvolávat jiným jménem než je jméno tabulky (důvod- např.dlouhé jméno tabulky), nebo při self-join (samospojení)


SELECT klauzule SELECT ALL|DISTINCT – ALL znamená všechny - i opakující se řádky , je to defaultní zadání

– DISTINCT » pokud nechci opakování řádků se stejnými hodnotami ( = duplicitních řádků) » potlačuje duplicitní řádky -až při zobrazení výsledné relace

* pokud chci zobrazit všechny atributy z relace/relací


SELECT příkaz SELECT jméno

atributu - jednoznačný název - v rámci databáze : jméno relace.jméno atributu

Výraz

- konstanty , numerické, znakové, datumové, logické, funkce aritmetické výrazy +, -, *, /, ( )

Pozn.: mezi operátory minimálně 1 mezera, ve výrazech kombinace atributů , konstant Vladimíra Zádová, KIN, EF TUL - DBS

SELECT příkaz SELECT .. {jméno atributu | výraz } FORMAT " AS " “ , atribut1......

"

od

SQL2 lze přejmenovat názvy sloupců ( u atributů) , či u výrazů pojmenovat jméno atributu| výraz AS název sloupce

FORMAT - pokud chci jiný než definovaný v DDL


SELECT příkaz WHERE spojení více relací ( implicitní ) ( porovnání atributů, podle kterých se mají spojit pro podmíněný výběr prvků ke

pozn: z hlediska relační algebry SELEKCE je-li více podmínek výběru spojení AND, OR


SELECT příkaz WHERE podmínky výběru: klasicky - pomocí NOT, AND, OR , > , <, <=, >= ,<> výraz1 relační_operátor výraz2 výrazem může být : konstanta, atribut, funkce, aritmetický výraz


SELECT -WHERE BETWEEN /NOT BETWEEN pro spojitý interval , výraz1 -dolní, výraz2 - horní hranice intervalu, - za výrazx lze uvést konstantu, atribut, aritmetický výraz...WHERE výraz [ NOT] BETWEEN výraz1 AND výraz2 Př.:

czak BETWEEN 1 AND 5 . ............... C-zak >= 1 AND c-zak <= 5 name BETWEEN "a" AND "dz" odmena NOT BETWEEN plat AND plat +1400


SELECT -WHERE IN /NOT IN • • • •

pro nespojité hodnoty zjednodušuje zápis výběrových podmínek (OR) výběr ze seznamu hodnot, výčet hodnot -nelze uvádět jména atributů, výrazy

WHERE výraz [ NOT] IN (výčet hodnot) Př.: c-zak IN (1,2,3,4,5) ...c-zak = 1 OR c-zak = 2 OR ...... Vladimíra Zádová, KIN, EF TUL - DBS

SELECT - WHERE NULL / NOT NULL pouze

pro atribut relace WHERE jméno atributu IS [ NOT] NULL

Př.: všechny, které nemají uveden údaj - dat-odesl IS NULL Pozn.: není pravda, že NULL hodnota je rovna jiné NULL hodnotě (t.j. neurčené hodnotě), NULL hodnoty si nemohou být rovny navzájem ale při potlačení výstupních řádků s duplicitními hodnotami jsou NULL hodnoty v rámci atributu považovány výjimečně za duplicitní Vladimíra Zádová, KIN, EF TUL - DBS

SELECT -WHERE LIKE /NOT LIKE

porovnává se vzorovou hodnotou lze jím testovat jen atributy pro řešení dotazů, které nelze řešit přímým srovnáváním

WHERE jméno atributu [ NOT] LIKE "vzor" [ ESCAPE "oddělovač"] kde:

% - více znaků _ - jeden znak Př.: atribut LIKE " % OV" atribut LIKE " 13 / %" ESCAPE "/" vyhledá hodnoty atributu 13% Vladimíra Zádová, KIN, EF TUL - DBS

SELECT * FROM zakaznik WHERE mesto LIKE '%dec%';


SELECT ORDER BY klauzule

implicitně : výstup podle PK tabulky uvedené první za FROM, hodnoty jsou uvedeny vzestupně. ORDER BY slouží ke změně implicitních podmínek:

ORDER BY {{ jméno atributu |n } ASC|DESC} [,..] n - číslo pořadí sloupce za SELECT klauzulí - výhoda pro odvozené hodnoty ASC - vzestupně - standardně DESC - sestupně Vladimíra Zádová, KIN, EF TUL - DBS

ORDER BY

Kriteria

oddělena čárkou, prvně uvedený je nadřazený následujícím

Některé

implementace jazyka vyžadují, aby atribut použitý jako kriterium třídění byl obsažen v klauzuli SELECT.


BOOLEOVSKÉ OPERÁTORY AND

, OR

- užití při více výběrových podmínkách AND - musí být pravdivé všechny uvedené podmínky OR - alespoň jedna z podmínek pravdivá Relační

operátory : <, >, <=, >=, <>

Pořadí vyhodnocování

závorky násobení, dělení odčítání, sčítání NOT AND OR


SQL SELECT DML - agregační funkce klauzule GROUP BY, HAVING

spojení vnitřní spojení vnější spojení Samospojení

strukturované příkazy jednoduché souvztažné

operace UNION, INTERSECT, EXCEPT dynamické a statické relace Vladimíra Zádová, KIN, EF TUL - DBS

SELECT příkaz a agregace

SELECT klauzule FROM klauzule [WHERE klauzule ] [GROUP BY klauzule] [HAVING klauzule ] [ORDER BY klauzule] 17


SELECT příkaz AGREGAČNÍ FUNKCE

= aggregate functions, column functions SUM AVG MIN MAX COUNT

použití v klauzuli SELECT umožní z hodnot atributů v databázi odvodit souhrnné , agregované údaje



Rozdíl agregačních funkcí a aritmetických výrazů aritmetické výrazy za klauzulí SELECT vypočítávají nové údaje z hodnot v rámci jednotlivých řádků agregační funkce - souhrnná hodnota je odvozena z hodnot určitého atributu relace: - ze všech řádků relace - z konkrétních řádků určených podmínkami za WHERE - ze skupin řádků definovaných klauzulí GROUP BY



agregace bez klíče (agregačního) – ze všech řádků původní relace či z řádků vyhovujících podmínkám za WHERE se vytvoří jeden agregát. Výsledkem je jeden řádek.

agregace s klíčem – častěji , vyplývá z požadavku zjištění hodnot agregovaných funkcí za menší shluky (seskupení). – Výsledkem je více řádků - a to tolik řádků, kolik různých hodnot bude obsaženo ve sloupci (sloupcích) podle kterých chceme agregovat.



Specifikace agregačního klíče

– v klauzuli GROUP BY – jednoduchý klíč ( 1 atribut), složený klíč (více atributový)



DISTINCT – u agregační funkce potlačuje duplicitní hodnoty argumentu funkce při výběru dat z databáze před vlastním provedením výpočtu ( při použití v klauzuli SELECT potlačuje duplicitní řádky až při zobrazení výsledné relace). NULL - neznámé, nedefinované hodnoty – se nezapočítávají do počtu, součtu, průměru, max, min – Výjimka COUNT( * ) - zjistí počet řádků bez ohledu na nedefinovanost obsahu. Vladimíra Zádová, KIN, EF TUL - DBS

SELECT příkaz AGREGAČNÍ FUNKCE - SUM

SUM ( [ DISTINCT ] argument) - vrací součet hodnot argumentu - argument: - numerický atribut - aritmetický výraz vracející numerickou hodnotu


SELECT příkaz AGREGAČNÍ FUNKCE - AVG

AVG ( [ DISTINCT ] argument) Vrací průměrnou hodnotu z určených ( NOT NULL) hodnot argumentu argument: - numerický atribut

- aritmetický výraz vracející numerickou hodnotu


SELECT příkaz AGREGAČNÍ FUNKCE - MAX, MIN

MIN ( [ DISTINCT ] argument) MAX ( [ DISTINCT ] argument) vrací minimální, resp. maximální hodnotu argumentu argument: atribut aritmetický výraz neobsahující znakovou konstantu


SELECT příkaz AGREGAČNÍ FUNKCE - COUNT COUNT ( * ) Vrací počet hodnot v rámci atributů relace, tedy počet řádků relační tabulky .

COUNT ( [ DISTINCT ] atribut) Funkce COUNT s argumentem je v různých systémech implementována odlišně. Standardně vrací počet určených hodnot daného atributu- t.j. počet hodnot atributu různých od NULL ( = NOT NULL). Některé implementace připouští pouze syntaxi s povinným užitím DISTINCT: COUNT ( DISTINCT atribut). Vladimíra Zádová, KIN, EF TUL - DBS

SELECT příkaz AGREGAČNÍ FUNKCE - COUNT Rozdíl COUNT ( * ) a COUNT ( DISTINCT atribut) – je-li atribut povinný a neobsahuje duplicitní údaje » výsledky obou jsou totožné. – Je-li atribut povinný a obsahuje duplicitní údaje » pak odlišný výsledek – není-li atribut povinný - budou -li nedefinované (NULL) hodnoty atributu v některých prvcích relace » pak nejsou shodné.


SELECT příkaz AGREGAČNÍ FUNKCE - COUNT PŘ. SELECT COUNT(*),COUNT(DISTINCTc-obj), COUNT( DISTINCT c-zak), COUNT(DISTINCT dexp) FROM objednavky – výsledek: 214, 214, 71, 53 – Jak byste interpretovali tento výsledek, co z toho vyplývá


SELECT příkaz- AGREGAČNÍ FUNKCE -

určení atributů a funkcí za SELECT

Není možné za SELECT uvádět atributy / výrazy , jejichž hodnoty jsou vztaženy k prvkům relace a uvádět agregační funkce, které jsou vztaženy buď k celé relaci, či pouze k některým řádkům dané relace. Při agregaci není nutné se omezit jen na jednotlivé atributy , lze je použít na jakýkoli výraz , v němž se budeme odkazovat na více atributů původní relace příkaz GROUP BY slouží k vytváření dílčích shluků, neuvedu-li GROUP BY pak se jedná o agregaci bez klíče výběru, potom je nutné se zamyslet nad poli uvedenými za select - např. budu chtít objem zboží celkem , ale uvedu ještě atr. objednávka, či zákazník. Toto je nepřípustné. Vladimíra Zádová, KIN, EF TUL - DBS

SELECT příkaz GROUP BY, HAVING HAVING

- pouze užití s klauzulí GROUP BY

– omezuje výstup agregovaných skupin obdoba

WHERE, ale WHERE filtruje řádky

jména

atributů za HAVING musí být obsaženy v GROUP BY nebo musí být obsaženy v agregované funkci.

Použití

[NOT]BETWEEN, [NOT]IN, ... viz WHERE 61


Druhy spojení

SPOJENÍ (join )

je-li třeba pracovat s daty z více relací - zřetězení řádků relací dle určité podmínky. Mluvíme o všeobecném joinu

Nejčastější spojení dle shody hodnot atributů – tzv. ekvivalentní join

explicitně spojení v SQL neexistuje, vyjadřuje se pomocí SELECT příkazu: – ve FROM - uvedením spojovaných relací

–

za WHERE uvedením podmínky spojení – ale jen vnitřní spojení

– (atributy relací musí být kompatibilní) Vladimíra Zádová, KIN, EF TUL - DBS

SPOJENÍ (join )

– od standardu SQL2 kromě operátorů <, >, >=, <= lze použít pro spojení join operátory: pro vnější spojení - outer join

LEFT JOIN, RIGHT JOIN, FULL JOIN (ve většině současných relací zatím není)

– spojení přes rovnost (ekvivalentní join ), přirozený join (pojení max množiny shodných atributů)


SPOJENÍ (join ) druhy join :

vnitřní –inner join – zobrazí jen ty řádky dvou či více relací, které splňují podmínky spojení

– vnitřní spojení lze specifikovat 2 způsoby: » implicitně : pomocí klauzule FROM (uvedení tabulek) a WHERE (uvedení podmínek spojení) » explicitně: specifikací v klauzuli FROM ( uvedením tabulek včetně typu a podmínek spojení) :


SPOJENÍ (join ) inner join - explicitně jméno relace1[INNER] JOIN jméno relace2 ON podmínky spojení [[INNER] JOIN jméno relace3 ON podmínky spojení ........] – ON klauzule může specifikovat více podmínek -pak spojení logickými operátory AND, OR, NOT – lze kombinovat LEFT JOIN a INNER JOIN – je-li spojeno více tabulek- vyhodnocování spojení se provádí zleva doprava – pokud za FROM bude uvedeno více tabulek bez podmínek spojení - pak výstup odpovídá kartézskému součinu Vladimíra Zádová, KIN, EF TUL - DBS

SPOJENÍ (join )

SELECT zakaznik.c-zak, zakaznik.jmeno, objednavka.c-obj, objednavka.d-obj FROM zakaznik, objednavka WHERE zakaznik.c-zak = objednavka.C-zak.

SELECT zakaznik.c-zak, zakaznik.jmeno, objednavka.c-obj, objednavka.d-obj FROM zakaznik INNER JOIN objednavka ON zakaznik.c-zak = objednavka.C-zak


SPOJENÍ (join ) vnější – outer join

u vnějšího spojení je důležité, zda se jedná o levé či pravé vnější spojení - není symetrické

levé vnější spojení lze uvést pomocí LEFT, event. LEFT OUTER JOIN SELECT zakaznik.c-zak,zakaznik.jmeno, objednavka.c-obj, objednavka.d-obj FROM zakaznik LEFT JOIN objednavka ON zakaznik.c-zak = objednavka.C-zak.


SPOJENÍ (join ) levé vnější spojení: jméno relace1[alias ] [INNER|LEFT[OUTER]] JOIN jméno relace2 [alias] ON podmínky spojení [INNER|LEFT[OUTER]] JOIN jméno relace3 [alias] ON podmínky spojení..... pravé vnější spojení: jméno relace1[alias ] RIGHT[OUTER] JOIN jméno relace2 [alias] ON podmínky spojení Vladimíra Zádová, KIN, EF TUL - DBS

SPOJENÍ (join ) SELECT klauzule FROM { jméno relace [correlation-name] | implicit-join | explicit-join } [WHERE klauzule] [GROUP BY atribut [,atribut2]...] [HAVING klauzule] [ORDER BY klauzule] Vladimíra Zádová, KIN, EF TUL - DBS

Vyjádření různých spojení

SQL92 - rozšíření o – spojení křížem » …… FROM r CROSS JOIN s

( totéž jako FROM a,b)

– přirozené spojení » …… FROM r NATURAL JOIN s

– spojení přes rovnost hodnot zadaných stejnojmenných sloupců » …. ..FROM r JOIN s USING (a,b)

– spojení přes obecnou spojovací podmínku » ……FROM r JOIN s ON a<=b » Tendence = oddělit SELEKCI a SPOJENÍ

– LZE POUŽÍT PRO VNITŘNÍ I VNĚJŠÍ SPOJENÍ ( INNER, OUTER - LEFT , RIGHT, FULL) Vladimíra Zádová, KIN, EF TUL - DBS

SPOJENÍ (join ) - vlastní –self join

užití,

pokud budu potřebovat vícekrát do téže relace

je možné uvést tuto relaci (za FROM ) vícekrát - avšak s použitím alias


Strukturované dotazy

STRUKTUROVANÉ DOTAZY

Jazyk SQL je označován jako strukturovaný právě proto, že je možné vnořovat SELECT příkazy

Vnořování se užívá pro složitější dotazy, vyhodnocování probíhá postupně po jednotlivých subdotazech


STRUKTUROVANÉ DOTAZY

Subdotaz ( = vnitřní dotaz, vnitřní příkaz ) – je příkaz SELECT vnořený do výběrové podmínky WHERE SQL příkazu, může být i za HAVING – Vnitřní příkaz dodává hodnoty pro vyhodnocení podmínky

Vnější příkaz – je příkaz, do kterého je příkaz SELECT vnořen. – vnější příkaz - je zejména SELECT, užívá se pro INSERT, UPDATE, DELETE Vladimíra Zádová, KIN, EF TUL - DBS

STRUKTUROVANÉ DOTAZY vzájemné vztahy vnějších a vnitřních dotazů

jednoduché » vyhodnocování dotazů zevnitř ven: vnitřní příkaz je vyhodnocen jako první, nezávisle na vnějším příkazu vnější příkaz následně využívá výsledků vnořeného dotazu souvztažné » vyhodnocování dotazů složitější: vnitřní příkaz pracuje na základě hodnot dodaných vnějším příkazem vnitřní dotaz využívá a tedy závisí na hodnotách dodávaných do subdotazu vnějším dotazem Vladimíra Zádová, KIN, EF TUL - DBS

STRUKTUROVANÉ DOTAZY - vnitřní dotaz Podle

typů výsledků, které vrací lze rozlišit

subdotazy:

– vracející množinu hodnot - vždy jim předchází IN nebo relační operátor s ALL, ANY nebo SOME – vracející maximálně jednu hodnotu uvozené relačním operátorem bez modifikace – subdotazy fungující jako test existence uvozené klíčovým slovem EXISTS Vladimíra Zádová, KIN, EF TUL - DBS

STRUKTUROVANÉ DOTAZY - syntaxe příkazu: SELECT {název atributu | výraz | * } [,....] FROM název relace [,...] WHERE výraz { [ NOT ] IN | relační operátor [ ALL| ANY | SOME ]| [ NOT] EXISTS } ( SELECT {název atributu | výraz | * } [,....] FROM název relace [,...] WHERE výběrové podmínky] )


STRUKTUROVANÉ DOTAZY omezení pro subdotazy

vnořený SELECT je uzavřený v ( ) v klauzuli WHERE vnějšího příkazu musí platit kompatibilita mezi hodnotou výrazu a hodnotou vrácenou subdotazem klauzule SELECT vnořeného subdotazu, které předchází IN , nebo pouze relační operátor může obsahovat maximálně jeden atribut nebo výraz – subdotaz, kterému předchází ALL, ANY, SOME může obsahovat klauzule GROUP BY a HAVING – v subdotazu obsahujícím klauzuli GROUP BY nelze použít klíčového slova DISTINCT – subdotaz nemůže obsahovat klauzuli ORDER BY Vladimíra Zádová, KIN, EF TUL - DBS

STRUKTUROVANÉ DOTAZY subdotazy uvozené [ NOT ] IN WHERE výraz [ NOT ] IN ( SELECT příkaz) výsledkem subdotazu může být množina hodnot

ve vnějším dotazu tato množina hodnot nahrazuje výčet hodnot v seznamu, se kterým pracuje IN.

Vyhodnocení podmínky vnějšího dotazu: použití IN - pravdivá, je-li zjištěna shoda hodnoty výrazu z WHERE klauzule alespoň s jednou hodnotou z množiny hodnot vrácených subdotazem použití NOT IN - pravdivá, pokud nebude rovna žádné z vrácené množiny hodnot Vladimíra Zádová, KIN, EF TUL - DBS

STRUKTUROVANÉ DOTAZY uvozené operátory : <, >, =, < >, < =, > =

WHERE výraz relační operátor [ ALL | ANY | SOME ] ( SELECT příkaz)

bez ALL, ANY , SOME – smí vrátit maximálně jednu hodnotu – použití v dotazech, kdy s jistotou víme, že dotaz nemůže vrátit více hodnot. ( v případě vrácení více hodnot - chyba) s ALL, ANY, SOME – smí vrátit více jak jednu hodnotu – ALL, ANY , SOME přesněji specifikují způsob porovnání vrácené množiny hodnot s hodnotou výrazu v klauzuli WHERE Vladimíra Zádová, KIN, EF TUL - DBS

STRUKTUROVANÉ DOTAZY uvozené operátory : <, >, =, < >, < =, > = ALL

výběrová podmínka vnějšího příkazu bude pravdivá, pokud je výsledek srovnání pravdivý pro všechny hodnoty vrácené vnořeným subdotazem. 4 > ALL (1, 2,3 ) .....pravda 2 > ALL (1,2,3 ) .....nepravda


STRUKTUROVANÉ DOTAZY uvozené operátory : <, >, =, < >, < =, > =

ANY, SOME – SOME je pouze ekvivalentem ANY (zavedení SOME vychází ze standardu ANSI / ISO SQL) – výběrová podmínka vnějšího příkazu bude pravdivá, pokud je výsledek srovnání pravdivý pro alespoň pro jednu z hodnot vrácených vnořeným subdotazem 4 > ANY (1, 2, 3 ) .....pravda 2 > ANY (1,2,3 ) .....pravda


STRUKTUROVANÉ DOTAZY subdotazy uvozené [ NOT ] EXISTS WHERE [ NOT ] EXISTS ( SELECT příkaz) výsledkem subdotazu existence či neexistence hodnot

u EXISTS podmínka pravdivá - je-li výsledkem subdotazu alespoň 1 řádek př.:

SELECT jmeno-zak, kontakt FROM zakaznik WHERE EXISTS(SELECT * FROM objednavky WHERE objednavky.c-zak = zakaznik.c-zak ) Vladimíra Zádová, KIN, EF TUL - DBS

SQL - strukturovaný dotazovací jazyk

DML - operace INTERSECTION UNION EXCEPT DML - dynamické a statické relace DCL ochrana před neautorizovaným přístupem (GRANT, REVOKE)

SQL a tvorba aplikací Vladimíra Zádová, KIN, EF TUL - DBS

Union, Intersect, and Difference (Except)

114


SQL - DML

ke zpracování dotazů z více relací – operace spojení JOIN – strukturované příkazy – množinové operace - od verze SQL2 » sjednocení ( UNION) » průnik ( INTERSECT) » rozdíl ( EXCEPT ) » tyto operace nejsou součástí všech implementací

» použití spolu s příkazem SELECT » Za oběma klauzulemi SELECT musí být navzájem kompatibilní atributy Vladimíra Zádová, KIN, EF TUL - DBS

SQL -DML operace UNION SELECT……. UNION SELECT….. př. SELECT c-pol, nazev, cena-j FROM katalog UNION SELECT c-pol, nazev, cena-j FROM novy-katalog Vladimíra Zádová, KIN, EF TUL - DBS

SQL -DML operace INTERSECT SELECT……. INTERSECT SELECT….. př. SELECT c-pol, nazev, cena-j FROM katalog INTERSECT SELECT c-pol, nazev, cena-j FROM novy-katalog Vladimíra Zádová, KIN, EF TUL - DBS

SQL -DML operace INTERSECT není operátor INTERSECT – řešení pomocí struktur. dotazu:

pokud

př. SELECT c-pol, nazev, cena-j FROM katalog WHERE EXISTS ( SELECT * FROM novy-katalog WHERE katalog.c-pol = novy-katalog.c-pol SELECT k.c-pol, k.nazev, k.cena-j FROM katalog k, novy-katalog n WHERE k.c-pol = n.c-pol


)

SQL -DML operace EXCEPT SELECT……. EXCEPT SELECT….. př. SELECT c-pol, nazev, cena-j FROM katalog EXCEPT SELECT c-pol, nazev, cena-j FROM novy-katalog Vladimíra Zádová, KIN, EF TUL - DBS

SQL -DML operace EXCEPT není operátor EXCEPT – řešení pomocí struktur. dotazu: př. pokud

SELECT c-pol, nazev, cena-j FROM katalog WHERE NOT EXISTS ( SELECT * FROM novy-katalog WHERE katalog.c-pol = novy-katalog.c-pol )


SQL A RELACE relace definované CREATE TABLE

– jsou považovány za základní, fyzické relace( base tables).

odvozené relace – dynamické odvozené relace = view – statické odvozené relace = snap-shots


DATA DEFINITION LANGUAGE


DATA DEFINITION LANGUAGE - DDL

vytváření databáze a relační struktury – vytváření, rušení a aktualizace relací (tabulek) – vytváření a rušení indexů – vytváření a rušení pohledů - (ne alter) – Integritní omezení a DDL (create domain, check, Primary key ) – Datové typy podporované SQL-92.


DDL vytvoření

struktury databaze – CREATE DATABASE či CREATE SCHEMA, DROP DATABASE vytvoření struktury databáze – CREATE TABLE, CREATE VIEW předefinování a rušení databáze – ALTER TABLE, DROP TABLE, DROP VIEW indexace - od SQL89 – CREATE INDEX, DROP INDEX Vladimíra Zádová, KIN, EF TUL - DBS

DDL - CREATE CREATE TABLE jmeno-relace ({ nazev-atributu datovy-typ [NOT NULL [UNIQUE]] [ DEFAULT hodnota ] [ [NOT] CASE-SENSITIVE] [ FORMAT řetězec ] [ LABEL řetězec ] [ COLUMN-LABEL řetězec ]} [,...] [, { UNIQUE ( {nazev-atributu } [,...] }] ) Vladimíra Zádová, KIN, EF TUL - DBS

CREATE TABLE PCE U SLO Í N EZE

CREATE TABLE název OM ( jméno atr. datový typ [CONSTRAINT název] {[NULL|NOT NULL]| [PRIMARY KEY|UNIQUE}| [FOREIGN KEY] REFERENCES jm tab. (jméno sloupce) ON DELETE CASCADE…. Y LK U B [,…] Í TA N EZE M O [,[CONSTRAINT název] PRIMARY KEY (výčet jmen atributů – odděl. čárkou)…..,] [,[CONSTRAINT název] FOREIGN KEY….] [, [CONSTRAINT název] CHECK …) Vladimíra Zádová, KIN, EF TUL - DBS

datový

typ – CHARACTER, CHARACTER – NUMERIC – DECIMAL - pro numerické s des. mistem – INTEGER, INTEGER SMALLINT - celá čísla – FLOAT, REAL, DOUBLE PRECISION od SQL 92 - DATE, DATE TIME, YEAR, MONTH, DAY, HOUR, SECOND LOGICAL Vladimíra Zádová, KIN, EF TUL - DBS

Ochrana integrity databáze Integritní omezení (integrity constraints, integrity rules) Specifikace IO musí být podle teorie relačního modelu součástí definice databáze, uloženy v DD (Data Dictionary = slovníku dat =katalogu dat) Integritu rozlišujeme : - doménovou (domain integrity) - Entitní integrita (entity integrity) - Referenční ( referential integrity ) Vladimíra Zádová, KIN, EF TUL - DBS

Ochrana integrity databáze doménová integrita (domain integrity) - každá hodnota musí být z hodnot pro atribut přípustných - DBS by měl poskytovat prostředky pro definici omezení v Data Dictionary, - v okamžiku definice relace - třeba zabudovat

Entitní integrita (entity integrity) - požadavek na zajištění identifikace každého prvku relace (řádek = konkrétní výskyt entity či vztahu) = PK Vladimíra Zádová, KIN, EF TUL - DBS

DDL doménová integrita Pomocí

klauzule CHECK

CREATE TABLE jm (.... Mesto CHAR(15) CHECK mesto IN („Praha“ , „Liberec“), c-pol INTEGER CHECK c-pol > 5 AND c-pol <11)


DDL doménová integrita

Pomocí CREATE DOMAIN (od SQL2)

CREATE DOMAIN pocet INTEGER CHECK VALUE >0 AND VALUE < = 5 CREATE TABLE katalog ( ......, qty pocet,...... pocet


DDL entitní integrita

PRIMARY KEY(od SQL89)

CREATE TABLE katalog ( ......, c-pol INTEGER PRIMARY KEY,......

Pozn. pokud není tato klauzule - pak: – klíčové atributy musí být definovány pomocí NOT NULL UNIQUE Vladimíra Zádová, KIN, EF TUL - DBS

Ochrana integrity databáze Referenční ( referential integrity ) - řeší logické vztahy mezi více relacemi - logická konzistence obsahu databáze - pomocí FK - řešení logické integrity databáze - 1. Restriktivní řešení - 2. kaskádovité řešení - 3. Nullify řešení Vladimíra Zádová, KIN, EF TUL - DBS

1. Restriktivní řešení -

-

-

není dovoleno zrušit řádek, pokud existují v jiných relacích řádky logicky související hodnotu PK nadřízené tabulky -není možné aktualizovat do podřízené relace nelze uložit řádek, u něhož hodnota FK neodpovídá řádku v nadřízene tabulce


2. kaskádovité řešení -

-

-

v případě rušení řádku v nadřazené tabulce -ruší se řádky v podřízené relaci se stejnou hodnotou FK hodnotu PK možné aktualizovat - ale aktualizují se i FK v souvisejících řádcích v případě ukládání řádku, u něhož hodnota FK neodpovídá řádku v nadřízené tabulce - se doplní řádek v nadřízené tabulce


3. Nullify řešení

-

-

při rušení řádků dochází ke změně hodnot všech souvisejících klíčů na neurčené - tedy NULL hodnoty v případě ukládání řádku, u něhož hodnota FK neodpovídá řádku v nadřízené tabulce - se naplní FK hodnotou neurčenou


DDL referenční integrita

klauzule REFERENCES (od SQL89) ..restriktivní řešení

ON DELETE CASCADE, ON UPDATE CASCADE ON DELETE SET NULL, ON UPDATE SET NULL ( od SQL2 )

implementace

výjimečně


DDL referenční integrita CREATE TABLE zakaznici ( c-zak INTEGER PRIMARY KEY,.... ......) CREATE TABLE objednavky (... c-zak INTEGER REFERENCES zakaznici(czak) CREATE TABLE objednavky (... c-zak INTEGER REFERENCES zakaznici(czak) ON DELETE CASCADE Vladimíra Zádová, KIN, EF TUL - DBS

DDL pokud SQL jediný jazyk pro práci s daty (ORACLE, SYBASE) - pak i jiná řešení - např. triggery CREATE TRIGGER jmeno-trig ON nazev relace FOR {INSERT | DELETE| UPDATE[výčet atributů]} [,...] AS výčet SQL příkazů


DDL - ALTER TABLE ALTER TABLE jmeno-relace {ADD COLUMN nazev-atributu datovy-typ [NOT NULL [UNIQUE]] [ DEFAULT hodnota ] [ [NOT] CASE-SENSITIVE] [ FORMAT řetězec ] | DROP COLUMN nazev-atributu | ALTER COLUMN nazev-atributu [ DEFAULT hodnota ] [ {NOT] CASE-SENSITIVE] [ FORMAT řetězec ] [ LABEL řetězec ] [ COLUMN-LABEL řetězec ] } Vladimíra Zádová, KIN, EF TUL - DBS

DDL - ALTER TABLE

ADD část – má-li již relace záznamy » neurčená hodnota, či hodnota DEFAULT » není -li DEFAULT, pak nelze NOT NULL » nelze UNIQUE

DROP část

zruší atribut včetně hodnot pokud se na něj odkazuje některá z odvozených relací - pak chybové hlášení

ALTER část

DEFAULT- vliv až na dodatečně vkládané řádky⇒ ⇒nelze NOT NULL a UNIQUE Vladimíra Zádová, KIN, EF TUL - DBS

DDL - DROP TABLE DROP TABLE nazev relace

ruší definici relace včetně obsahu Po zrušení relace budou nepřístupné všechny odvozené relace definované nad zrušenou relací změna definice relace i její zrušení vede k aktualizaci v systémových relacích


DDL - indexy

ovlivňuje rychlost práce s daty při aktualizaci dat - doba odezvy (souvisí s počtem řádků relace)


DDL - indexy z

hlediska potřebnosti: – pro klíčové atributy – atributy, které jsou při výstupu dat používány jako třídící kriterium – atributy vystupující při join operacích jako spojovací – atributy s vysokou frekvencí přístupu – atributy, které jsou častým předmětem dotazů na četnost Vladimíra Zádová, KIN, EF TUL - DBS

DDL - indexy

indexy - jednoduché, skupinové, unikátní

CREATE [ UNIQUE] INDEX nazev-indexu ON nazev-relace ( {nazev-atr} [,...]) DROP INDEX nazev-indexu


SQL a relace

ZÁKLADNÍ (BÁZICKÉ) STATICKÉ DYNAMICKÉ

DYNAMICKÉ A STATICKÉ ODVOZENÉ RELACE DYNAMICKÉ

ODVOZENÉ RELACE =

VIEW – fyzicky neexistují – s každou aktualizací obsahu výchozích relací se mění i hodnoty relací odvozených – smyslem – zjednodušení práce s daty, zajištění nezávislosti na logické struktuře databáze STATICKÉ

ODVOZENÉ RELACE = SNAP-

SHOTS – fyzicky existují Vladimíra Zádová, KIN, EF TUL - DBS

SQL DYNAMICKÉ ODVOZENÉ RELACE (virtuální relace, pohledy, viewed tables) Představuji jiný pohled na uložená data,

vytvořeny z jedné či více základních relací – z hlediska relační algebry: projekce, restrikce (selekce), join mohou být vytvořeny i z jiných VIEW nejsou naplněny daty (fyzicky neexistují), jsou uloženy pouze ve formě definice v DATA DICTIONARY Nad odvozenou relací nelze vytvářet indexy definici nelze modifikovat ( ALTER ) Vladimíra Zádová, KIN, EF TUL - DBS

SQL ODVOZENÉ RELACE - DDL

VYTVOŘENÍ ODVOZENÉ RELACE CREATE VIEW název odvozené relace [ (názvy atributů odvozené relace) ] AS vnořený příkaz SELECT [ WITH CHECK OPTION ] kde: WITH CHECK OPTION – verifikuje případné aktualizace obsahu prostřednictvím odvozené relace (v některých implementacích chybí ) RUŠENÍ ODVOZENÉ RELACE DROP VIEW název odvozené relace Vladimíra Zádová, KIN, EF TUL - DBS

SQL ODVOZENÉ RELACE

použití SELECTu – celý vyjma ORDER BY – v klauzuli SELECT - atributy relace, aritmetické výrazy , agregační funkce – názvy atributů odvozené relace se uvedou, pokud nebudou totožné s názvy uvedenými za klauzulí SELECT

Pokud bude daný pohled výchozí pro jiná VIEW nesmíme daný pohled rušit (abychom se nepřipravili o přístup k odvozeným relacím vyšší úrovně) - nemusí být vždy ochrana proti zrušení Vladimíra Zádová, KIN, EF TUL - DBS

SQL - ODVOZENÉ RELACE práce s odvozenými relacemi výběr

dat z odvozených relací – obdoba práce s relacemi základními

aktualizace dat - INSERT, UPDATE, DELETE – přípustná pouze v některých implementacích jazyka – za splnění určitých podmínek (mohou se lišit) – aktualizací pohledu - dochází k aktualizaci původních relací Vladimíra Zádová, KIN, EF TUL - DBS

SQL ODVOZENÉ RELACE – aktualizace dat

Standard SQL pro aktualizaci: – SELECT ve VIEW nesmí obsahovat GROUP BY , HAVING – v klauzuli SELECT nesmí být DISTINCT, agregační funkce, aritmetický výraz – klauzule FROM nesmí obsahovat více jak jednu výchozí relaci (pokud touto relací je VIEW, musí být opět aktualizovatelné) – za WHERE klauzulí nesmí být vnořený SELECT


SQL ODVOZENÉ RELACE Př. CREATE VIEW obj-kat AS SELECT katalog.c-pol, katalog.jmeno, c-obj, qty FROM katalog, r-obj WHERE katalog.c-pol = r-obj.c-pol SELECT * FROM obj-kat WHERE katalog.jmeno = “bunda ADIDAS”.


STATICKÉ ODVOZENÉ RELACE = SNAP-SHOTS – – – – –

pouze v některých implementacích SQL v databázi fyzicky existují jsou naplněny vybranými daty jsou vždy aktualizovatelné, jsou na výchozích relacích nezávislé ( jejich obsah se při aktualizaci výchozích relací nemění) – vytvoří se příkazem SELECT klauzuli INTO


statické odvozené relace = snap-shots SELECT výčet atributů INTO název odvozené relace FROM název relací [WHERE výběrové podmínky ] – struktura relace dána klauzulí SELECT, jednotlivé prvky výsledné relace vyhovují podmínkám za WHERE. – Podle standardu SQL není možné uvést GROUP BY a HAVING některé implementace tato omezení nemají (SYBASE )


statické odvozené relace = snap-shots – v momentě svého vytvoření je jejich obsah totožný s obsahem relací, z nichž je tato relace odvozena – vypovídají o stavu databáze v daném okamžiku ⇒ mohou sloužit jako časový snímek obsahu databáze – použití pro testování , zálohování – k definici relace s obdobnou strukturou pokud za WHERE dám podmínky, které nevyberou ani jeden řádek (např. 1 = 3 )

– pokud příkaz neobsahuje klauzuli INTO - lze obejít » pomocí CREATE TABLE a INSERT s vnořeným SELECT příkazem


statické odvozené relace = snap-shots Př. SELECT katalog.c-pol, katalog.jmeno, c-obj, qty INTO obj-kat FROM katalog, r-obj WHERE katalog.c-pol = r-obj.c-pol


Data Control Language

Přístupová privilegia příkazy GRANT a REVOKE

vlastník relace - považován její tvůrce- t.j. uživatelské jméno, pod kterým byla relace definována

GRANT – k přidělení přístupových privilegií

REVOKE – k odebrání přístupových privilegií


GRANT GRANT {ALL [PRIVILEGES] | {SELECT | INSERT | DELETE | UPDATE [(výčet atributů)]} [, ...]} ON název relace TO { seznam uživatelů|PUBLIC} [ WITH GRANT OPTION ]


REVOKE

REVOKE {ALL [PRIVILEGES ]| {SELECT | INSERT | DELETE | UPDATE [(výčet atributů)]} [ ,...]} ON název relace FROM { seznam uživatelů|PUBLIC}


GRANT, REVOKE Příkazy

se vztahují pouze k jedné relaci (klauzule ON)

lze přiřadit, či odvolat najednou – všechna privilegia - ALL – nebo explicitně vyjmenovaná – pro UPDATE lze udělit privilegia až na hodnotu atributu PUBLIC

- v GRANT přiděluje privilegia všem současným i budoucím uživatelům PUBLIC v REVOKE - odjímá privilegia udělená pomocí PUBLIC


GRANT, REVOKE WITH

GRANT OPTION u příkazu GRANT

– povoluje uživatelům převádět příslušná privilegia (i část) na další uživatele – může odejímat privilegia těm uživatelům, kterým je sám přidělil – pokud jsou odejmuta privilegia uživateli s oprávněním GRANT OPTION - jsou odejmuta automaticky i těm, kterým je přidělil Vlastníkovi relace nelze přístupová privilegia odejmout


GRANT, REVOKE

Privilegia lze udělovat i odebírat i odvozeným relacím při užití příkazů GRANT, REVOKE se účinky kumulují

Př. GRANT INSERT, UPDATE ON zakaznik TO referent GRANT SELECT ON zakaznik TO referent REVOKE UPDATE ON zakaznik TO referent

uživatel referent má možnosti INSERT, UPDATE a SELECT v relaci zakaznik

uživatel referent má možnosti INSERT a SELECT v relaci zakaznik


SQL a tvorba aplikací SQL

- pracuje s celou množinou řádků relací

Χ 4GL

(příkazy hostitelských jazyků pracují s jedním řádkem relace ⇒ pomocí principu cursoru kurzor určuje běžný řádek záznamy jsou zpřístupňovány hostitelským jazykům postupně Vladimíra Zádová, KIN, EF TUL - DBS

SQL a tvorba aplikací DECLARE CURSOR, FETCH, OPEN, CLOSE DECLARE název-kurzoru CURSOR FOR příkaz SELECT definuje kurzor

OPEN název-kurzoru nastavuje ukazovátko před první z množiny vybraných řádků

FETCH název-kurzoru INTO {nazev parametru| nazev-promenne}[,.....]} zpřístupnění řádků dané množiny, příkaz vždy přesune ukazovátko na další řádek hodnotami řádku naplní předem definované proměnné .. (klauzule INTO)

CLOSE název-kurzoru ukončení práce kurzoru Vladimíra Zádová, KIN, EF TUL - DBS

SQL. strukturovaný dotazovací jazyk. Structured Query Language (SQL)

Recommend Documents