Jazyk S Q L základy, příkazy pro práci s daty

Jazyk S Q L – základy, příkazy pro práci s daty Základní pojmy 

jazyk – množina řetězců nad abecedou



gramatika popisuje syntaxi výrazů jazyka – pravidla, jak vytvářet platné řetězce jazyka.



dotazovací jazyk je svázán s databází tj. dotazy, které výrazy jazyka označují, musejí být vyhodnotitelné.



dotaz (databázový dotaz) – funkce definovaná nad prostorem všech přípustných databází s daným schématem. Hodnota této funkce je vlastně odpovědí na dotaz (určitá databáze s daným schématem) Vlastnosti této funkce (dotazu) -

je vyčíslitelná

-

odpověď obsahuje pouze hodnoty z databáze

-

odpověďnezávisí na reprezentaci databáze



omezenost jazyka – každému výrazu jazyka odpovídá dotaz



expresivní jazyk by byl takový jazyk, kterým by se dal vyjádřit libovolný dotaz (platí např. pro programovací jazyky, nikoliv však pro běžné dotazovací jazyky)



vyjadřovací síla jazyka – množina dotazů, která se dá v daném jazyce vyjádřit



perzistence jazyka Úplný jazyk = expresivní + omezený



silnější jazyk (slabší)



ekvivalentní jazyky



ortogonalita jazyka -

nezávislost v používání různých konstrukcí jazyka

-

existence konstruktoru (nižší objekt → vyšší objekt)

-

existence selektoru

-

možnost přiřazení hodnot jedné třídy jinému objektu téže třídy

-

možnost porovnání objektů v rámci třídy

Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

-

podpora rekurzivně definované syntaxe a uzávěrových vlastností

Vývoj počátky používání – rok 1974 ( Sequel ) systémy IBM

- DB2 ( operační systém MVS/370 ) - SQL/DS ( systémy VM/CMS, DOS/VSE ) - QMF obsahuje SQL jako uživatelské rozhraní pro počítače třídy PC

- INFORMIX SQL

- INGRES pro PC - ORACLE - xBASE - SQLBase - XDB II - XQL databázové servery - INFORMIX - PROGRESS - INGRES Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

- ORACLE

- SQL Server od Sybase - SQL Server Microsoft

Standardizace ANSI v r. 1986 – SQL86 ( průnik existujících implementací ), rozšíření definičního jazyka s možností definovat RI - SQL89, další standard přijatý ANSI SQL92.

Části SQL -

jazyk pro definici dat

-

interaktivní jazyk pro manipulaci dat

-

jazyk pro manipulaci dat v hostitelské verzi

-

možnost definice pohledů

-

možnost definice přístupových práv

-

možnost definice integritních omezení

-

řízení transakcí

Manipulace dat v SQL SELECT, INSERT, DELETE,UPDATE, OPEN, CLOSE, FETCH, COMMIT, ROLLBACK.



Základním konstruktem pro výběr dat je příkaz SELECT - FROM - WHERE. Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti



Klauzule SELECT odpovídá operaci projekce (eventuelně spojení nebo kartézskému součinu)



Klauzule FROM obsahuje seznam jmen relací, nad kterými je dotaz definován



Klauzule WHERE obsahuje podmínku definující kritéria pro vyhledávaná data (selekční podmínka).Tvar dotazu

SELECT A1 , A2 , . . . . , Aj FROM

R1 , R2 , . . . . . , Rk

WHERE 

Ekvivalentní dotaz v relační algebře

( R1 , R2 , . . . . . , Rk )  [A1 , A2 , . . . . , Aj ]

Zjednodušená syntaxe SELECT [ DISTINCT | ALL ] { * | atribut1 [ , atribut2 ] . . . } FROM relace1 [ , relace2 ] . . . [ WHERE podmínka ] [ ORDER BY klíč1 [ , klíč2 ] . . . ] Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

Příklad : Uvažujme následující schéma relační databáze KNIHOVNA ČTENÁŘ (ČísloČ, JménoČ, PříjmeníČ, AdresaČ, DatumOd, DatumDo ) KNIHA ( ISBN, Název, Autor, Rok, Žánr) AUTOR (ČísloA, JménoA, PříjmeníA, StátA) VÝTISK (InvCis, ISBN, Nakladatel, DatNákup, Cena ) NAKLADATELSTVÍ (ČísloN, NázevN, AdresaN, StátN) ŽÁNR (ČísloŽ, NázevŽ) VÝPŮJČKA (ČísloČ, InvCis, DatPůjčeno, DatVráceno ) REZERVACE (ČísloČ, ISBN, DatRez )

Příklad 5.1 :

Vypsat seznam čtenářů a jejich adres SELECT PříjmeníČ , JménoČ, AdresaČ FROM ČTENÁŘ

Příklad 5.2 :

Vypsat seznam čtenářů z Českých Budějovic SELECT * FROM ČTENÁŘ WHERE AdresaČ LIKE “%České Budějovice%“

Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

Příklad 5.3a : Vypsat seznam čtenářů přihlášených letos SELECT * FROM ČTENÁŘ WHERE YEAR(DatumOd) = YEAR(GETDATE()) Příklad 5.3b : Vypsat seznam nových čtenářů přihlášených o prázdninách SELECT * FROM ČTENÁŘ WHERE DatumOd BETWEEN “01.07.02“ AND “31.08.02”

Příklad 5.4 :

Vypsat názvy knih rezervovaných do konce října SELECT DISTINCT Název FROM KNIHA , REZERVACE WHERE KNIHA.ISBN = REZERVACE.ISBN AND DatRez < “31.10.02“

Poznámka: Klauzule DISTINCT zaručuje výběr neduplicitních záznamů

Příklad 5.5a : Vypsat seznam všech výtisků vydaných v USA a jejich cenu SELECT DISTINCT ISBN, NázevN, Cena FROM VÝTISK, NAKLADATELSTVÍ WHERE StátN = “USA” AND ČísloN = Nakladatel

Poznámka: Jedná se vlastně o výpis údajů ze dvou tabulek, takže musíme v klauzuli WHERE uvést, jakým způsobem jsou tabulky propojeny. V podstatě se jedná o kartézský součin filtrovaný podmínkou uvedenou ve WHERE. Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

Příklad 5.5b : Vypsat všechny důležité informace o knihách vytištěných v USA SELECT DISTINCT VÝTISK.ISBN, InvCis, JménoA, PříjmeníA, Název, Cena FROM NAKLADATELSTVÍ, VÝTISK, KNIHA, AUTOR WHERE StátN = “USA” AND ČísloN = Nakladatel AND Výtisk.ISBN = KNIHA.ISBN AND Autor = ČísloA

Agregační, statistické funkce COUNT(), SUM(), AVG(), MAX(), MIN(), STDEV(), VAR()

Příklad 5.6 :

Celkový počet čtenářů knihovny SELECT COUNT (*) FROM ČTENÁŘ

Příklad 5.6’ : Celkový počet čtenářů knihovny – lépe SELECT COUNT (*) AS počet_čtenářů FROM ČTENÁŘ WHERE DatumDo <= GetDate()

Příklad 5.7a : Počet čtenářů, kteří mají rezervovanou nějakou knihu SELECT COUNT (DISTINCT ČísloČ) FROM REZERVACE

Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

Příklad 5.7b : Počet čtenářů, kteří mají půjčenou nějakou knihu SELECT COUNT (DISTINCT ČísloČ) FROM VÝPŮJČKA WHERE DatVráceno IS NULL

Příklad 5.8 :

Počet knih vydaných v USA v nakladatelství McGraw Hill SELECT COUNT (*) AS knihy_McGrawHill FROM VÝTISK, NAKLADATEL WHERE NázevN = ”McGraw Hill” AND StátN = “USA” AND ČísloN = Nakladatel

Příklad 5.9 :

Zjisti celkový počet výtisků knih Aloise Jiráska v knihovně SELECT COUNT (*) FROM AUTOR, KNIHA, VÝTISK WHERE PříjmeníA=“Jirásek“ AND JménoA=“Alois“ AND Autor=ČísloA AND KNIHA.ISBN = VÝTISK.ISBN

Příklad 5.9’ : Zjisti celkový počet knih Aloise Jiráska v knihovně SELECT COUNT (DISTINCT VÝTISK.ISBN) AS knihy_jirásek FROM AUTOR, KNIHA, VÝTISK WHERE PříjmeníA=“Jirásek“ AND JménoA=“Alois“ AND Autor=ČísloA AND KNIHA.ISBN = VÝTISK.ISBN

Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

Konstrukt GROUP BY dává možnost aplikace agregačních funkcí na podmnožiny relace zkonstruované podle výběrového kritéria - klasifikace podle hodnot vybraného atributu. Relace se konceptuálně, nikoliv fyzicky rozdělí na skupiny, pro které je hodnota zvoleného atributu konstantní. (Řádky s prázdnou hodnotou daného atributu tvoří zpravidla jednu skupinu.) PRAVIDLO : Použitím DISTINCT nic nezkazíš !

Příklad 5.10 : Vypsat celkový počet dosud vypůjčených různých knih pro každého čtenáře SELECT VÝPUJČKA.ČisloČ, JménoČ, PříjmeníČ, COUNT (DISTINCT InvCis) AS knihy_celkem FROM VÝPUJČKA, ČTENÁŘ WHERE VÝPUJČKA.ČísloČ = ČTENÁŘ. ČísloČ GROUP BY VÝPUJČKA.ČisloČ

Příklad 5.11a : Vypsat počty různých vydaných knih podle jednotlivých žánrů SELECT NázevŽ, COUNT (DISTINCT ISBN ) FROM ŽÁNR, KNIHA WHERE ČísloŽ = Žánr GROUP BY Žánr

Příklad 5.11b : Vypsat počty různých knih (ne výtisků), které jsou k dispozici v knihovně podle jednotlivých žánrů Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

SELECT NázevŽ, COUNT (DISTINCT VÝTISK.ISBN ) FROM ŽÁNR, KNIHA, VÝTISK WHERE ČísloŽ = Žánr AND KNIHA.ISBN = VÝTISK.ISBN GROUP BY Žánr

Příklad 5.12a : Vypsat jména čtenářů, kteří „přečetli“ víc jak 500 knih SELECT PříjmeníČ, JménoČ, COUNT (DISTINCT InvCis) FROM VÝPŮJČKA, ČTENÁŘ WHERE VÝPŮJČKA.ČísloČ = ČTENÁŘ.ČísloČ GROUP BY VÝPŮJČKA.ČísloČ HAVING 500 < COUNT (DISTINCT InvCis )

Příklad 5.12b : Vypsat jména čtenářů, kteří „přečetli“ víc jak 500 knih SELECT PříjmeníČ, JménoČ, COUNT (DISTINCT InvCis) AS počet FROM VÝPŮJČKA, ČTENÁŘ WHERE VÝPŮJČKA.ČísloČ = ČTENÁŘ.ČísloČ GROUP BY VÝPŮJČKA.ČísloČ HAVING 500 < počet

Příklad 5.12c : Vypsat jména čtenářů, kteří „přečetli“ víc jak 500 knih SELECT PříjmeníČ, JménoČ FROM VÝPŮJČKA, ČTENÁŘ WHERE VÝPŮJČKA.ČísloČ = ČTENÁŘ.ČísloČ Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

GROUP BY VÝPŮJČKA.ČísloČ HAVING 500 < COUNT (DISTINCT InvCis )

Příklad 5.13 : Celkové měsíční přírůstky v roce 2000 SELECT MONTH(DatNákup) AS Měsíc, COUNT(InvCis) AS Počet, SUM(Cena) AS Celkem_Kč FROM VÝTISK WHERE YEAR(DatNákup) = 2000 GROUP BY Měsíc

Příklad 5.14 : Celkový denní počet výpůjček v říjnu 2002 SELECT DatPůjčeno, COUNT(InvCis) AS Denní_výpůjčka FROM VÝPŮJČKA WHERE DatPůjčeno BETWEEN “01.10.02“ AND “31.10.02“ GROUP BY DatPůjčeno

Příklad 5.15 : Celkový měsíční počet výpůjček pro každého čtenáře jednotlivě SELECT PříjmeníČ, JménoČ, YEAR(DatPůjčeno) AS Rok, MONTH(DatPůjčeno) AS Měsíc, COUNT(InvCis) AS Výpůjčka FROM VÝPŮJČKA, ČTENÁŘ WHERE VÝPŮJČKA.ČísloČ = ČTENÁŘ.ČísloČ GROUP Rok, , Měsíc, VÝPŮJČKA.ČísloČ Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

Příklad 5.16a : Názvy knih, které přečetlo víc jak 200 čtenářů SELECT ISBN, NázevK FROM KNIHA K WHERE 200 < (SELECT COUNT (*) FROM VÝPŮJČKA V, VÝTISK T WHERE V.ISBN = T.ISBN AND C.ČísloČ = V. ČísloČ AND C.ČísloK = V.ČísloK)

Příklad 5.16b : Názvy knih, které přečetlo víc jak 200 čtenářů SELECT ISBN, NázevK FROM KNIHA K , VÝPŮJČKA V, VÝTISK T WHERE V.ISBN = T.ISBN AND C.ČísloČ = V. ČísloČ AND C.ČísloK = V.ČísloK GROUP BY V. ČísloČ HAVING 200 < Count(InvCis)

Příklad 5.17a : Průměrný celkový počet vypůjčených knih připadajících na jednoho čtenáře v roce 2002 SELECT COUNT(InvCis) / COUNT(DISTINCT ČísloČ) AS Počet FROM VÝPŮJČKA WHERE YEAR(DatPůjčeno) = 2002 Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

Příklad 5.17b : Průměrný počet vypůjčených knih během jedné návštěvy knihovny (tj. v jednom dni) připadajících na jednoho čtenáře Nejde realizovat jedním příkazem – jedná se totiž o průměr z celkových počtů tj. agregační funkce AVG by se měla použít na agregační fci COUNT – není přípustné INSERT INTO POCTY (Datum, Čtenář, Počet) SELECT DatPůjčeno, ČísloČ, Počet= COUNT(InvCis) FROM VÝPŮJČKA GROUP BY DatPůjčeno, ČísloČ SELECT AVG(Počet) AS Prům_výpůjčka FROM POCTY

Příklad 5.18a : Největší knihomol (čtenář, co si půjčil nejvíc knih) SELECT TOP 1 PříjmeníČ, JménoČ, COUNT(InvCis) AS Počet FROM VÝPŮJČKA, ČTENÁŘ WHERE VÝPŮJČKA.ČísloČ = ČTENÁŘ.ČísloČ GROUP BY VÝPŮJČKA.ČísloČ ORDER BY Počet DESCENDING

Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

Příklad 5.18b : Největší knihomol (čtenář, co si půjčil nejvíc knih) INSERT INTO PŘEČTENO (Příjmení, Jméno, Počet) SELECT PříjmeníČ, JménoČ, Počet = COUNT(InvCis) FROM VÝPŮJČKA, ČTENÁŘ WHERE VÝPŮJČKA.ČísloČ = ČTENÁŘ.ČísloČ GROUP BY VÝPŮJČKA.ČísloČ SELECT Příjmení, Jméno FROM PŘEČTENO WHERE Počet = SELECT MAX(Počet) FROM PŘEČTENO

Příklad 5.19a : Autoři, kteří napsali více jak 5 knih SELECT JménoA, PříjmeníA FROM AUTOR, KNIHA WHERE ČísloA = Autor GROUP BY ČísloA HAVING 5 < COUNT(*)

Příklad 5.19b : Autoři, kteří napsali více jak 5 knih SELECT JménoA, PříjmeníA FROM AUTOR WHERE 5 < ( SELECT COUNT (DISTINCT ISBN) FROM KNIHA WHERE ČísloA = Autor)

Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

Příklad 5.20a : Vypsat čtenáře, kteří mají vypůjčeny nějaké knihy a současně mají v aktuálním měsíci rezervaci SELECT DISTINCT ČísloČ FROM REZERVACE WHERE MONTH(YEAR) = MONTH(DatRez) AND ČísloČ IN ( SELECT ČísloČ FROM VÝPUJCKA WHERE DatVráceno IS NULL )

Příklad 5.20b : Lze napsat i v opačném pořadí podmínek SELECT DISTINCT ČísloČ FROM VÝPUJCKA WHERE DatVráceno IS NULL AND ČísloČ IN (SELECT ČísloČ FROM REZERVACE WHERE MONTH(YEAR) = MONTH(DatRez) )

Příklad 5.21 : Vypsat všechny výtisky vydané v GB, SRN a FR SELECT ISBN, InvCis, NázevK, StátN FROM KNIHA, VÝTISK, NAKLADATELSTVÍ WHERE KNIHA.ISBN = VÝTISK.ISBN AND Nakladatel = ČísloN AND StátN IN (‘GB’,’SRN’, ‘FR’ )

Příklad 5.22a : Vypsat jména čtenářů, kteří mají rezervovanou knihu Průvodce SQL Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

(dva poddotazy) SELECT JménoČ, PříjmeníČ FROM ČTENÁŘ C WHERE ČísloČ IN ( SELECT ČísloČ FROM REZERVACE WHERE ISBN = ( SELECT ISBN FROM KNIHA WHERE NázevK = “Průvodce SQL“ ) )

Příklad 5.22b : Vypsat jména čtenářů, kteří mají rezervovanou knihu Průvodce SQL (jeden poddotaz) SELECT JménoČ, PříjmeníČ FROM ČTENÁŘ C WHERE ČísloČ IN ( SELECT ČísloČ FROM REZERVACE R, KNIHA K WHERE K.ISBN = R.ISBN AND NázevK = “ Průvodce SQL“)

Příklad 5.22c : Vypsat jména čtenářů, kteří mají rezervovanou knihu Průvodce SQL SELECT JménoČ, PříjmeníČ FROM ČTENÁŘ C, REZERVACE R, KNIHA K WHERE C.ČísloČ = R.ČísloČ AND R.ISBN = K.ISBN AND NázevK = “ Průvodce SQL“ ) Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

Příklad 5.22d : Vypsat jména čtenářů, kteří mají rezervovanou knihu Průvodce SQL SELECT JménoČ, PříjmeníČ FROM ČTENÁŘ INNER JOIN REZERVACE INNER JOIN KNIHA ON REZERVACE.ISBN = KNIHA ISBN ON ČTENÁŘ.ČísloČ = REZERVACE.ČísloČ WHERE NázevK = “ Průvodce SQL“

Příklad 5.23 : Vypsat čísla všech knih dražších než jakákoliv kniha vydaná u nás SELECT InvCis FROM VÝTISK WHERE Cena > ALL (SELECT V.Cena FROM VÝTISK V, NAKLADATELSTVÍ N WHERE StátN = “CZ“ AND N.Číslo = V.Nakladatel)

Příklad 5.24a : Vypsat knihy, které jsou rezervovány SELECT DISTINCT Název FROM KNIHA WHERE ISBN IN ( SELECT ISBN FROM REZERVACE )

Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

Příklad 5.24b : SELECT DISTINCT Název FROM KNIHA WHERE EXISTS (SELECT * FROM REZERVACE WHERE KNIHA.ISBN = REZERVACE .ISBN)

Příklad 5.24c : SELECT DISTINCT Název FROM KNIHA INNER JOIN REZERVACE ON KNIHA.ISBN = REZERVACE .ISBN

Příklad 5.25a : Vypsat čísla čtenářů, kteří si letos ještě nic nepůjčili SELECT ČísloČ FROM ČTENÁŘ C WHERE NOT EXISTS ( SELECT * FROM VÝPŮJČKA V WHERE YEAR(V.DatPůjčeno) = YEAR (DATE ()) AND C.ČísloČ = V.ČísloČ AND V.ČísloK=C.ČísloC)

Příklad 5.25b : Vypsat čísla čtenářů, kteří si letos ještě nic nepůjčili SELECT ČísloČ FROM ČTENÁŘ WHERE ČísloČ NOT IN (SELECT ČísloČ FROM VÝPŮJČKA WHERE YEAR(DatPůjčeno) = YEAR (DATE ()))

Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

Příklad 5.26 : Kniha, která se nikdy nepůjčila SELECT Název FROM KNIHA WHERE ISBN NOT IN (SELECT DISTINCT VÝTISK.ISBN FROM VÝPŮJČKA, VÝTISK WHERE VÝPŮJČKA.InvCis = VÝTISK.InvCis)

Příklad 5.27 : Vypsat čtenáře, kteří mají půjčenou nebo rezervovanou knihu o PHP SELECT ČísloČ, JménoČ, PříjmeníČ FROM REZERVACE, KNIHA, ČTENÁŘ WHERE ČTENÁŘ.ČísloČ = REZERVACE.ČísloČ AND KNIHA.ISBN = REZERVACE.ISBN AND NázevK LIKE “%PHP%” UNION SELECT ČísloČ, JménoČ, PříjmeníČ FROM VYPUJČKY, VÝTISK, KNIHA, ČTENÁŘ WHERE ČTENÁŘ.ČísloČ = REZERVACE.ČísloČ AND KNIHA.ISBN = VÝTISK.ISBN AND VYPUJČKY.InvCis = VÝTISK.InvCis AND NázevK LIKE “%PHP%”

Příklad 5.28 : Zjistit celkovou hodnotu knih v knihovně SELECT SUM(Cena) AS Celkova_Cena FROM VYTISK

Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

Problémy k zamyšlení : P1 : Najít knihu s největším počtem výtisků P2 : Vypsat seznam autorů, u nichž má knihovna k dispozici všechny jejich vydané publikace P3 : Vypsat seznam autorů, kteří nejsou v knihovně vůbec zastoupeni žádným výtiskem P4 : Najít a vypsat název nejfrekventovanějšího žánru

Práce s řetězci Podmínky typu atribut LIKE řetězcová konstanta

Autor LIKE “JI%“ Autor LIKE “K_“ Autor LIKE “%p__“ Adresa LIKE “%Budějovice%“

Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti