SQL Structured Query Language
Spouště
DML
Spouště
Řízení transakcí SQL
Integritní omezení Vložené a dynamické SQL
DDL Bezpečnost
Přístupová práva
SQL
SEQUEL-XRM, IBM v roce 1974
Neprocedurální jazyk
říkáme co chceme, ne jak to udělat
Nepoužívá se jen u relačních databází Interakční a hostitelská verze
V interakční verzi se příkazy dají používat samostatně v režimu online. V hostitelské se příkazy SQL stávají součástí hostitelského jazyka (např. COBOL, FORTRAN, C/C++, apod. Implementace SQL se liší ve způsobu práci s dotazem.
Vytvoření databáze
CREATE DATABASE jméno_databáze USE master CREATE DATABASE Sales ON ( NAME = Sales_dat, FILENAME = 'c:\mssql7\data\saledat.mdf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ON ( NAME = 'Sales_log', FILENAME = 'c:\mssql7\data\salelog.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB )
Definice dat v SQL
Datové typy
Integer – celé číslo se znaménkem; Decimal(p,q) – číslo s p ciframi a desetinou čárkou na q pozici zprava; Float – reálné číslo; Char(n) – řetězec znaků délky n; Varchar(n) – řetězec znaků max. délky n; Standartní: Numeric, Real, Double precision, Smallint; Nestandarní: Date,Money.
Vytvoření relace
CREATE TABLE jméno_relace (atribut datový_typ [NOT NULL[UNIQUE]] [, atribut datový_typ [NOT NULL [UNIQUE]] ...] [,UNIQUE seznam_atributů])
CREATE TABLE osoby (
jmeno varchar(25), prijmeni varchar(25), vek integer)
Změna a smazaní relace
ALTER TABLE jméno_relace {ADD | DROP} atribut datový_typ [NULL,..]
ALTER TABLE osoby ADD pocet_deti integer
DROP TABLE jmeno_relace
DROP TABLE osoby
Definice Indexů
CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX jméno_indexu ON jméno_relace (jméno_atributu [ ASC | DESC ] [, jméno_atributu [ ASC | DESC ]]…)
[CLUSTERED] záznamy jsou uspořádány podle tohoto atributu/ů
[UNIQUE] jde o primární index v běžné terminologii souborů dat (UNIQUE v kombinaci s CLUSTER == index sekvenční soubor).
CREATE UNIQUE INDEX rc ON osoby (rodne_cislo) CREATE INDEX jp ON osoby (jmeno,prijmeni)
Plná syntaxe SELECT - MySQL
SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr, ... [INTO OUTFILE 'file_name' export_options | INTO DUMPFILE 'file_name'] [FROM table_references [WHERE where_definition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_definition] [ORDER BY {col_name | expr | position} [ASC | DESC] , ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)] [FOR UPDATE | LOCK IN SHARE MODE]]
SQL - SELECT
SELECT a1,a2,...,a3 FROM r1,r2,...,rk WHERE Ø V relační alegře : (r1 x r2 x...xrk)(Ø)[a1,a2...,aj]
Konstruktor SELECT
SELECT [DISTINCT | ALL] {* | jméno_atributu1[,jméno_atributu2]…} FROM název_relace[,název_relace2]… [WHERE podmínka] [GROUP BY atributy [HAVING podmínka_agregace]] [ORDER BY jméno_atributu1[,jméno_atributu2]…]
Manipulace dat v SQL
Sémantika příkazu SELECT
vytvoří se kartézský součin z relací – FROM Provede se SELECT (redukuje počet vrácených dat) vyhodnotí se podmínka – WHERE rozdělení do podskupin – GROUP BY relace se omezí dle HAVING Uspořádá se pomocí ORDER BY
Manipulace dat v SQL
Jednoduché příklady SELECT * FROM osoba WHERE vek>50 ORDER BY jmeno ASC SELECT * FROM osoba WHERE prijmeni LIKE ‘VL%’
Manipulace dat v SQL
PROJEKCE
SELECT jmeno FROM osoby
SELEKCE SELECT RC,jmeno FROM osoby WHERE jmeno=´jan´
KARTÉZKÝ SOUČIN SELECT * FROM osoby,byty
SPOJENÍ SELECT jmeno FROM osoby,byty WHERE osoby.rc=byty.rc
Spojení pomocí konstruktu JOIN SELECT produkt_c AS jmeno FROM tProdukty JOIN tCeny ON (tProdukty.produkt_id=tCeny.Cena_pr) WHERE produkt_e LIKE ‘%product%’ ORDER BY tProdukty.produkt_e
UNION (spojení) INTERSECT(průnik)* EXCEPT(rozdíl)* Přirozený, levý, pravý JOIN
Pozn. )* často není DB podporováno
Manipulace dat v SQL
Aritmetické funkce +,-,*,/ SELECT rc, plat / 30 FROM osoba
Agregační funkce COUNT, SUM, MAX, MIN, AVG SELECT COUNT (*) FROM osoba
Manipulace dat v SQL
Konstrukt GROUP BY a HAVING
SELECT S.rating, MIN (S.age) AS minage FROM Sailors S WHERE S.age>=18 GROUP BY S.rating HAVING COUNT (*) > 1
Vyhodnocení příkladu Sid
sname
rating
age
rating
minage
rating
minage
22
Petr
7
45
7
45
1
33
29
Brutus
1
33
1
33
3
25
31
Karel
8
55
8
55
3
63
32
Andy
8
25
8
25
7
45
58
Kuba
10
35
10
35
7
35
64
Horacio
7
35
7
35
8
55
9
40
8
25
71
Jana
10
16
3
25
9
40
74
Pepa
9
40
3
25
10
35
85
Michala
3
25
95
Karolina
3
63
Výsledek příkladu rating
3
7
8
minage
25
35
25
Sid
sname
rating
age
22
Petr
7
45
29
Brutus
1
33
31
Karel
8
55
32
Andy
8
25
58
Kuba
10
35
64
Horacio
7
35
71
Jana
10
16
74
Pepa
9
40
85
Michala
3
25
95
Karolina
3
63
Manipulace s množinami a vložené dotazy
Manipulace s množinami
p IN A p je prvkem množiny A množinu tvoří výčet nebo SELECT-FROM-WHERE
SELECT * FROM osoba,byt WHERE (osoba.rc=byt.rc) AND (byt.ulice IN (‘Hálkova’,’Pražská’)) SELECT * FROM osoba,byt WHERE (osoba.rc=byt.rc) AND ( byt.ulice IN ( SELECT * FROM byt WHERE (ulice LIKE ‘H%’)))
Manipulace dat v SQL
Kvantifikátory
Univerzální – není v SQL Existenční
Manipulace dat v SQL
Kvantifikátory exists y∈R (…) ≈ EXISTS (SELECT DISTINCT Y.* FROM R Y WHERE … ) SELECT nazev FROM Firmy F WHERE NOT EXISTS (SELECT * FROM Faktury A WHERE A.nazev=F.nazev)
Manipulace dat v SQL
Prázdné hodnoty
Rozšíření RMD Booleovské výrazy -> 3-stavová logika Aritmetický výraz ->NULL Agregační funkce aplikované na prázdnou množinu vracejí NULL Zakázáno : spojení přes NULL, dotaz bydliste=NULL, spec.příkaz IS NULL
Syntaxe INSERT
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] VALUES ({expr | DEFAULT},...),(...),... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
INSERT INTO osoba (jmeno,prijmeni) VALUE (‘Jan’,’Novak’)
INSERT INTO osoba SELECT * FROM novydata
Syntaxe UPDATE
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]
UPDATE osoba SET jmeno=‘Jan’ WHERE jmeno=‘Honza’
Syntaxe DELETE
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]
DELETE FROM osoba WHERE jmeno=‘Jan’
Definice pohledů
Virtuální relace, pohled na aplikaci uživatelskými očima CREATE VIEW jméno_pohledu [(njméno_atr1[,njméno_atr2]...)]
AS SELECT [DISTINCT] {* jméno_atr1[,jméno_atr2]...} FROM jméno_relace1[,jméno_relace2]... [WHERE podmínka1] [WITH CHECK OPTION] [GROUP BY gjméno_atr1[,gjméno_atr2]... [HAVING podmínka2]] [ENABLE UPDATE] CREATE VIEW pohled AS SELECT * FROM t1 WHERE a > 0 DROP VIEW jméno_pohledu
Pohledy II. (SQL-92)
Logická datová nezávislost Bezpečnost UPADTE
Updatable views – pouze nad jednou tabulkou
INSERT DELETE
Aktualizace pohledů
Nad základní relací == problémy nevznikají.
Zachovává-li projektivní omezení primární klíč, pak je dovolena jakákoli aktualizace. je třeba vložit prázdnou hodnotu do atributu def. NOT NULL?
Selektivní omezení zachovává primární klíč vždy == bez problémů, Agregační omezení nevedou k rozumě aktualizovatelným pohledům
Pohledy na více relacích (ve většině případu opět nevede aktualizace k jednoznačně (a smysluplně) definovaným aktualizacím základních relací)
CREATE VIEW Prazak AS SELECT cislo,jmeno,adresa FROM Ctenar WHERE adresa LIKE '%PRAHA%'
Provedeme-li aktualizaci adresy(INSERT a nebude nová adresa pražská) nezmění se pohled, nýbrž pouze základní relace.
WITH CHECK OPTION, kontroluje, zda aktualizací nebyla narušena podmínka v definici pohledu.
Současné SŘBD s SQL připouštějí pouze omezené možností pro aktualizaci pohledů. V DB2 nebo v dBASE IV je aktualizace pouze na projektivním a/nebo selektivním omezení.
Integritní omezení
CHECK - kontrola vstupních dat DEFALUT - výchozí hodnota FOREIGN KEY - definice cizího klíče PRIMARY KEY - definice primárního klíče CREATE TABLE osoba ( index INTEGER NOT NULL, jmeno VARCHAR(25) NOT NULL, dat_nar DATE DEFAULT 1/1/2004, poc_det INTEGER CHECK (poc_det<=6), byt INTEGER, PRIMARY KEY (index), FOREIGN KEY (byt) REFERENCES bydliste)
Ochrana dat Požadavek více uživatelského SŘBD Přístup k relaci(pohledu), definice práv
GRANT {ALL | {DELETE|INSERT|SELECT| UPDATE [(atribut1[, atribut2]…)]}…} ON relace TO {PUBLIC | uživatel1[,uživatel2]…}
Odebrání práv REVOKE
Transakce v SQL
Pouze v hostitelské verzi TRANSACTION jméno_transakce WHENEVER {ERROR|podmínka} ROLLBACK příkazy COMMIT END
Příkazy z množiny manipulačních
TRANSACTION dalsi_rok WHENEVER ERROR ROLLBACK UPDATE osoba SET vek=vek+1 COMMIT END
Manipulace dat v SQL
Systémový katalog
Informace o SŘBD SYSTABLE
SYSCOLUMNS
jméno indexu, jméno relace,...
COMMENT
jméno atributu,jméno relace,...
SYSINDEXES
jméno relace, zakladatele,počet atributů,...
Komentáře
Jedná se o relace -> přístup pomocí SQL
GROUP BY SELECT "column_name1", SUM("column_name2") FROM "table_name" GROUP BY "column_name1" Store_Information store_name
Sales
Date
Los Angeles
$1500
Jan-05-1999
San Diego
$250
Jan-07-1999
Los Angeles
$300
Jan-08-1999
Boston
$700
Jan-08-1999
SELECT store_name, SUM(Sales) FROM Store_Information GROUP BY store_name store_name
SUM(Sales)
Los Angeles
$1800
San Diego
$250
Boston
$700
HAVING SELECT "column_name1", SUM("column_name2") FROM "table_name" GROUP BY "column_name1" HAVING (arithmetic function condition) Store_Information store_name
Sales
Date
Los Angeles
$1500
Jan-05-1999
San Diego
$250
Jan-07-1999
Los Angeles
$300
Jan-08-1999
Boston
$700
Jan-08-1999
SELECT store_name, SUM(sales) FROM Store_Information GROUP BY store_name HAVING SUM(sales) > 1500 Result: store_name
SUM(Sales)
Los Angeles
$1800
JOIN Table Store_Information store_name
Sales
Date
Los Angeles
$1500
Jan-05-1999
San Diego
$250
Jan-07-1999
Los Angeles
$300
Jan-08-1999
Boston
$700
Jan-08-1999
Table Geography region_name
store_name
East
Boston
East
New York
West
Los Angeles
West
San Diego
Chceme zjistit celkové prodeje pro jednotlivá oddělení. Při standartním JOIN bude chybět New York, protože není v Store_Information. Outer join (syntaxe ORACLE) : SELECT A1.store_name, SUM(A2.Sales) SALES FROM Geography A1, Store_Information A2 WHERE A1.store_name = A2.store_name (+) GROUP BY A1.store_name Result: store_name
SALES
Boston
$700
New York
NULL
Los Angeles
$1800
San Diego
$250
Užitečné odkazy
http://www.cs.vsb.cz/amalka/tomica/sql/index.html http://home.zcu.cz/~kotouc/vyuka/dbm1/pr11.html http://msdn.microsoft.com/library/ http://www.oracle.com/technology/documentation/index.html http://dev.mysql.com/doc/
