Transcript Popis jazyka SQL
Popis jazyka SQL
Doc. Ing. Juraj Vaculík, PhD.
Jazyk pre manipuláciu s údajmi
Na rozdiel od programovacích
procedurálnych
alebo
objektových
jazykov (C++, PowerBuilder a pod.) radíme štruktúrovaný dopytovací jazyk SQL (Structured Query Language) do kategórie
neprocedurálnych
jazykov, ktoré popisujú
čo
požadujeme od databázy a nie
ako
to potrebujeme vykonať.
2
Jazyk SQL
Počiatky jazyka SQL
siahajú do roku 1974, keď bol definovaný firmou IBM jeho predchodca Sequel. v roku 1987 bol SQL prijatý organizáciou ANSI ako ISO štandard. Tento sa rozšíril v roku 1989 (SQL89) a 1992 (SQL92), čo je aj jeho dnešná platná norma. zložitosť SQL92 ilustruje aj fakt, že do dnešného dňa žiadna databázová firma nenaprogramovala do svojich systémov jeho plnú implementáciu.
3
Jazyk SQL
Základné črty SQL
uloženie údajov v databáze vo forme
tabuliek
reálnych
alebo
virtuálnych
pohľady); (neexistujúcich, sú to tzv. poloha tabuliek nie je dôležitá, identifikované sú svojim menom; poradie stĺpcov v tabuľkách nie je dôležité, identifikácia prebieha cez meno stĺpca; poradie riadkov v tabuľkách nie je dôležité; údaje sú vždy prezentované koncovému užívateľovi vo forme tabuliek, bez ohľadu na ich vnútornú štruktúru, Jazyk SQL
4
Indexy, kľúče
sú indexové
štruktúry
zamerané na zrýchlenie prístupu k riadkom v tabuľkách pri spracovaní užívateľského požiadavku. Samotný jazyk je však
nezávislý
na
fyzickej organizácii
údajov a o samotnom optimálnom spôsobe využitia indexov rozhoduje systém riadenia databázy.
5
Jazyk SQL
Členenie jazyka SQL
jazyk pre
definíciu údajov DDL
; interaktívny jazyk pre
manipuláciu s údajmi
a možnosť definície
pohľadov prístupových
práv
DML
; a možnosť definície integritných
obmedzení
riadenie transakcií TCC
.
6
Jazyk SQL
Definície údajov v SQL
deklarácii relačného dátového modelu zodpovedá popis dvojrozmernej
tabuľky
s označenými
stĺpcami a riadkami
, v relačnom dátovom modeli môžeme okrem základných tabuliek definovať aj
globálne
alebo
lokálne dočasné
tabuľky. Ich obsah nie je stály a po skončení práce s databázou sa vymaže.
Pri definovaní môžeme použiť nasledovné rezervované slová: CREATE TABLE, ALTER TABLE, DROP TABLE
7
Jazyk SQL
CREATE TABLE
CREATE TABLE
NOT NULL
hodnotu); meno_tabuľky(zoznam_prvkov_tabuľky) stĺpec nesmie mať hodnotu NULL (prázdnu
DEFAULT
určenie implicitnej hodnoty pre stĺpec;
UNIQUE
všetky hodnoty v stĺpci musia byť unikátne (ani jedna sa nesmie zopakovať);
PRIMARY KEY
stĺpec je definovaný ako primárny kľúč pri zabezpečení referenčnej integrity;
FOREIGN KEY
stĺpec je definovaný ako cudzí kľúč pri zabezpečení referenčnej integrity;
CHECK
zadaný logický výraz pre definovanie prídavných integritných obmedzení; Jazyk SQL
8
ALTER TABLE
Zmena tabuľky
ADD
-
DROP
pridať stĺpec; zrušiť stĺpec;
ALTER
zmeniť stĺpec;
ADD CONSTRAINT
obmedzenie; pridať integritné
DROP CONSTRAINT
obmedzenie.
zrušiť integritné Jazyk SQL
9
DROP TABLE
Zabezpečí vymazanie tabuľky z databázy. Nejedná sa o jej fyzické odstránenie, len o prepísanie údajov v systémovom katalógu. Zároveň dôjde aj k zrušeniu naväzujúcich indexov a pohľadov, čo môžeme ovplyvniť rezervovanými príkazmi
RESTRICT
alebo
CASCADE
(tzv. kaskádovité mazanie).
10
Jazyk SQL
Manipulácia s údajmi
Príkazy pre manipuláciu s údajmi obsahujú možnosti ich selektívneho výberu (
SELECT
), aktualizačné príkazy (
UPDATE
,
INSERT
,
DELETE
) a prípadne ďalšie rozšírenia.
Jazyk SQL
11
Príkaz SELECT ...
Základná konštrukcia príkazu SELECT sa skladá minimálne z nasledujúcich troch zložiek
SELECT FROM WHERE
, pričom každé z nich je vyhradené slovo:
SELECT
obsahuje zoznam mien
stĺpcov
vytvárajúcich schému tabuľky pre výsledok dopytu reprezentovaným príkazom;
FROM
obsahuje zoznam mien
tabuliek
, nad ktorými je dopyt definovaný;
WHERE
obsahuje obecne definovanú
podmienku vyhľadávanie údajov
. V podmienku s na rámci nej môžeme zadať ľubovoľnú operátormi =, <>, >, <, <=, >= a AND, OR, NOT.
12
Jazyk SQL
Príkaz SELECT ...
Príkaz SELECT neeliminuje automaticky z výsledku duplicitné riadky a ako výsledok získame tabuľku z riadkami údajov (vyjadríme pomocou slova
ALL
). Pokiaľ chceme ako výsledok získať reláciu, musíme použiť slovo
DISTINCT
(z výberu odstránime duplicity). Za zoznamom príkazov môžeme ešte uviesť pre konkrétny stĺpec podmienku
GROUP BY
, ktorá zabezpečí zoradenie celého výberu podľa daného stĺpca a to vzostupne (
ASC
) alebo zostupne (
DESC
),
13
Jazyk SQL
Príkaz SELECT
SELECT
[{
DISTINCT
mena_atributov}] |
ALL
}] [{* |
FROM
mena_relacii [
WHERE
podmienka] [
ORDER BY
meno_atributu zoradenie] Jazyk SQL
14
Zoskupovacie funkcie
COUNT
spočíta prvky výberu, vrátane duplicitných alebo prázdnych (NULL) hodnôt;
SUM
vráti súčet hodnôt (sumu) stĺpca;
MAX MIN
vráti maximum sady hodnôt stĺpca; vráti minimum sady hodnôt stĺpca;
AVG
vypočíta aritmetický priemer sady hodnôt stĺpca v dotaze.
agregačná_funkcia ([{ALL | DISTINCT}] meno_stĺpca)
15
Jazyk SQL
Zoskupovacie funkcie
GROUP BY
je špeciálny prípad agregačnej funkcie, ktorú aplikujeme na celú tabuľku alebo jej podmnožinu. Výsledná tabuľka sa konceptuálne (nie fyzicky) rozdelí na skupiny pre ktoré je hodnota zvoleného stĺpca konštantná. Vytvorí sa virtuálna tabuľka, pričom každá skupina tvorí jeden jej riadok. Pokiaľ chceme aby sa výsledok skladal iba z niektorých skupín, môžeme použiť klauzulu uvedené na začiatku zoznamu.
HAVING
(ekvivalent klauzuly WHERE pri príkaze SELECT). Samozrejme aj za ňou môžeme použiť doplňujúce agregačné funkcie
16
Jazyk SQL
Predikáty (prísudky) SQL
LIKE
- pracuje s stĺpcoch typu CHAR (textové). Jej použitím môžeme dosiahnuť rôznych výberov, napr. vo forme LIKE ‘%reťazec%’, sa vyhľadajú všetky záznamy, ktoré na ľubovoľnom mieste v poli obsahujú vyhľadávaný reťazec. podreťazcami hodnôt v definovaných
MATCH
môžeme ním kontrolovať, či vstupné hodnoty dané ako riadkové výrazy sú z určitej množiny.
UNIQUE
v použijeme, pokiaľ chceme otestovať, či danej množine existujú duplicity. Pokiaľ áno, nadobúda hodnotu FALSE, ináč TRUE.
17
Jazyk SQL
Predikáty (prísudky) SQL
IN
používa sa pri zložitých dopytov, ktoré obsahujú viacnásobné konštrukcie SELECT FROM WHERE. Minimálne jeden z nich je daný nasledovne:
meno_stĺpca [NOT] IN poddopyt
alebo
meno_stĺpca [NOT] IN (zoznam_hodnôt)
Pokiaľ sa
IN
aplikuje na prázdne tabuľky, návratová hodnota je FALSE a pri tabuľkách s prázdnymi riadkami (aj prázdny riadok je hodnota - obsahuje medzery) vracia UNKNOWN.
18
Jazyk SQL
Množinové operácie
Okrem základnej operácie štandard SQL92 priniesol aj operácie zodpovedajúce prieniku a rozdielu (
UNION INTERSECT
a (zjednotenie)
EXCEPT
).
Aby bolo možné tieto operácie vykonať, tabuľky musia byť navzájom kompatibilné (rovnaký počet stĺpcov a v nich rovnaké typy údajov). Operácia UNION priamo eliminuje duplikáty bez nutnosti zadávania podmienky DISTINCT.
Treba však rovno povedať, že v súčasných systémoch nie sú väčšinou tieto operácie implementované.
Jazyk SQL
19
Aktualizácie údajov v SQL
INSERT
operácia vkladania riadkov do tabuliek, pričom je nutné špecifikovať hodnoty pre jednotlivé stĺpce alebo kompletne celý riadok podľa špecifikovaného dopytu;
UPDATE
operácia zmeny hodnôt v riadkoch;
DELETE
operácia vymazania definovaných riadkov z tabuliek.
20
Jazyk SQL
Relačné spojenia tabuliek
Relačné operácie spojenia sú postavené na Coddovej algebre. V SQL92 môžeme použiť nasledovné varianty: prirodzené spojenie krížové spojenie spojenie cez podmienku spojenie cez vymenované stĺpce vnútorné spojenie vonkajšie spojenie spojenie zjednotením Jazyk SQL
21
Prirodzené spojenie
použije sa príkaz NATURAL JOIN.
SELECT * FROM U NATURAL JOIN V
; V SQL89 bolo nutné použiť
SELECT FROM WHERE
s operáciou = pre spájacie stĺpce. Jazyk SQL
22
Krížové spojenie
vytvára kartézsky súčin (spojenie každého riadku z R s každým z S) dvoch tabuliek:
SELECT * FROM R CROSS JOIN S;
23
Jazyk SQL
Spojenie cez podmienku
definujeme spájaciu podmienku. Syntax v SQL92 (v SQL89 sa definuje ináč):
SELECT * FROM R JOIN S ON A
Jazyk SQL
24
Spojenie cez vymenované stĺpce
rozširuje možnosti prirodzeného spojenia. Pokiaľ máme tabuľku U so stĺpcami (A,B,C) a tabuľku V so stĺpcami (E,A,B) a požadujeme spojenie cez stĺpce (A,B), môžeme napísať:
SELECT * FROM U JOIN V USING (A, B);
25
Jazyk SQL
Vnútorné spojenie
predchádzajúce spojenie môžeme nazvať aj ako vnútorné spojenie a napísať ako
INNER JOIN
alebo
INNER NATURAL JOIN
.
26
Jazyk SQL
Vonkajšie spojenie
slúži k pridávaniu niektorých riadkov, ktoré sa ničím nespojili, do výsledku. Označujeme ako
OUTER JOIN NATURAL OUTER JOIN
. alebo Riadky sa pridávajú podľa označenia
LEFT
,
RIGHT
alebo
FULL
a to buď z ľavého, pravého alebo obidvoch operátorov. Napr.:
SELECT * FROM U NATURAL LEFT OUTER JOIN V;
27
Jazyk SQL
Spojenie zjednotením
každý riadok z ľavého alebo pravého operátora je vo výsledku doplnený hodnotami NULL, a to buď sprava alebo zľava.
SELECT * FROM U UNION JOIN V;
Jazyk SQL
28
Integritné obmedzenia
CHECK
- kontroluje hodnoty komponentov riadku tabuľky. Môže byť lokálny pre daný stĺpec alebo globálny pre všetky stĺpce jednej tabuľky (rozšírenie SQL92). Akceptuje riadok pokiaľ podmienka nadobudne hodnoty TRUE alebo UNKNOWN.
DEFAULT
- kontroluje zadanie implicitnej hodnoty pri vkladaní riadku do tabuľky.
FOREIGN
alebo
PRIMARY KEY
- kontroluje zadanie referenčnej integrity pomocou explicitne definovaných cudzích alebo primárnych kľúčov.
29
Jazyk SQL
Referenčná integrita
určuje logické vzťahy medzi dvoma tabuľkami, pričom jedna z nich je vždy hlavná a druhá vedľajšia. stĺpec ktorý je viazaný vo vedľajšej (závislej) tabuľke označujeme ako cudzí kľúč (
FOREIGN KEY
) a z hlavnej tabuľky ako primárny kľúč (
PRIMARY KEY
). Zároveň je možné aj definovanie reakcie systému na narušenie referenčnej integrity a to aj pri aktualizačných operáciách (INSERT, DELETE, UPDATE).
30
Jazyk SQL
Referenčná integrita
Pre
INSERT
môžeme určiť obmedzenie vloženia riadku do závislej tabuľky.
Pre operáciu
DELETE
(ekvivalentne aj pre
UPDATE
): kaskádové odstránenie riadku náhrada cudzieho kľúča prázdnou hodnotou nahradenie cudzieho kľúča implicitnou hodnotou odstránenie riadku z hlavnej tabuľky z upozornením
31
Jazyk SQL
Kaskádové odstránenie riadku
ON DELETE CASCADE
pri odstránení riadku z hlavnej tabuľky dôjde k automatickému odstráneniu súvisiacich riadkov aj zo závislých tabuliek;
32
Jazyk SQL
Náhrada kľúča prázdnou hodnotou
SET NULL
pri odstraňovaní riadkov v závislej tabuľke, ktoré odkazujú na vymazávaný riadok hlavnej tabuľky, sa hodnota cudzieho kľúča nahradí prázdnou hodnotou;
33
Jazyk SQL
Nahradenie implicitnou hodnotou
SET DEFAULT
pri odstraňovaní riadkov v závislej tabuľke, ktoré odkazujú na vymazávaný riadok hlavnej tabuľky, sa hodnota cudzieho kľúča nahradí vopred definovanou hodnotou; Jazyk SQL
34
Odstránenie riadku
odstránenie riadku z hlavnej tabuľky z upozornením (
NO ACTION
) operácia vymazania sa nevykoná, pokiaľ pre primárny kľúč odstraňovaného riadku hlavnej tabuľky existuje ekvivalentná hodnota cudzieho kľúča v závislej tabuľke.
Niektoré systémy majú túto vlastnosť nastavenú implicitne.
35
Jazyk SQL
Práca s pohľadmi
Pohľady chápeme ako
virtuálne
(neexistujúce)
tabuľky
. Sú zamerané hlavne pre potreby užívateľa na definovanie dopytov (údaje sa nedajú meniť), môžu však slúžiť napríklad aj na aktualizáciu údajov a pod. Pre administrátora sa dajú s výhodou použiť na selektívne určenie ktorá skupina užívateľov má k čomu prístup
36
Jazyk SQL
Práca s pohľadmi ...
Vytvoriť pohľad môžeme pomocou CREATE VIEW, pričom samozrejme môžeme použiť rezervované slová a voľby popísané v predchádzajúcich bodoch. Syntax:
CREATE VIEW
meno_pohľadu [(mená_stĺpcov)]
AS
výraz_dopytu [WITH {CASCADE | LOCAL} CHECK OPTION]
37
Jazyk SQL
Práca s pohľadmi
za „výraz_dopytu“ dosadíme požadovaný dopyt (napr. SELECT názov_stĺpca1, názov_stĺpca2...- FROM názov_tabuľky - GROUP BY...) doplnený o možné agregačné funkcie. Pokiaľ uvedieme klauzulu kontroly podmienok v vykonávaní operácií
WITH CHECK OPTION
definičnej časti pohľadu pri
INSERT
a
UPDATE
.
, musí byť pohľad aktualizovateľný. Zaisťuje dodržiavanie Vymazanie pohľadu je možné pomocou príkazu
DROP VIEW
, za ktorým uvedieme názov pohľadu.
38
Jazyk SQL
Ochrana údajov
Každý systém má naprogramované rôzne možnosti ochrany a definovania prístupových práv pre jednotlivých užívateľov (alebo skupiny), čo vyplynulo hlavne z nutnosti viacpoužívateľského prístupu do databáz. SQL definuje ochranu pre nasledovné akcie: výber údajov (
SELECT
), ich modifikácia (
INSERT
,
UPDATE
,
DELETE
), odkazovanie (
REFERENCES
) a použitie.
39
Jazyk SQL
Ochrana údajov - syntax
GRANT
{
ALL PRIVILEGES
| zoznam_privilégií}
ON
objekt
TO
{PUBLIC user_identif1[,user_identif2]...} [
WITH
GRAND OPTION] zoznam_privilégií:=privilegium[,privilegium]...
privilegium:=
SELECT
|
DELETE
|
INSERT
[(meno_stlpca1[,meno_stlpca2]...)] |
UPDATE
[(meno_stlpca1[,meno_stlpca2]...)] |
REFERENCES USAGE
[(meno_stlpca1[,meno_stlpca2]...)] |
40
Jazyk SQL
Ochrana údajov - popis
Klauzula
WITH GRAND OPTION
umožňuje prideľovanie prístupových práv aj ďalším užívateľom, okrem tých, ktorý nie sú vymenovaný za klauzulou
TO
vtedy, keď sa požaduje prístup k jednej tabuľke rôznym skupinám užívateľov. . Je veľmi vhodná Samozrejme, stupeň privilégií definujeme na najnižšej možnej úrovni, aby nedošlo k úniku informácií.
41
Jazyk SQL
Budúcnosť jazyka SQL
Hlavné smery rozšírenia, ktoré prináša SQL, sú nasledovné:
relačné
procedurálne
objektové 42
Jazyk SQL
Relačné rozšírenia
pridanie ďalších predikátov a hlavne
triggerov
(procedúry, ktoré sú spustené pri výskytu určenej udalosti), ktoré databázové firmy implementovali každá vlastným spôsobom a teda rozdielne. Jedná sa o dlhodobý problém, lebo mali byť už včlenené v norme SQL.
Jazyk SQL
43
Procedurálne rozšírenia
rozšírenie o
uložené procedúry
, čo sú príkazy SQL preložené, optimalizované a uložené na databázovom servery. Ich používanie výrazne zlepšuje dobu odozvy a to hlavne u tých, ktoré vyžadujú komplikovanú optimalizáciu databázovým serverom.
44
Jazyk SQL
Objektové rozšírenia
rozšírenie o objektovo - orientované rysy jazyka abstraktné typy údajov, dedenie, polymorfizmus a pod. Pri rozšírení sa postupuje na základe Manifestu objektovo orientovaných databázových systémov. Jazyk SQL
45