Databázové systémy Cvičení 5.3 SQL jako jazyk pro manipulaci s daty
SQL jako jazyk pro manipulaci s daty Aktualizace dat v SQL • úprava záznamů v relacích (tabulkách) – vložení záznamu – oprava záznamu – vymazání záznamu
• příklady: – vložení nového čtenáře:
INSERT INTO UPDATE DELETE FROM
INSERT INTO CTENAR (RC,JMENO,PRIJMENI) VALUES ("750525/1234","Josef", "Novák") – změna příjmení čtenáře: UPDATE CTENAR SET PRIJMENI="Nováková" WHERE RC="755105/1000" – vymazání čtenáře: DELETE FROM CTENAR WHERE RC="755105/1000"
• lze vložit více řádků najednou INSERT INTO CTENAR (RC,JMENO,PRIJMENI) VALUES ("750525/1234","Josef", "Novák"), ("755520/4567",„Anna", "Nováková");
• pokud se některé atributy vynechají, do políčka se nevloží žádná hodnota (NULL), resp. defaultní hodnota, je-li definována integritním omezením
INSERT INTO CTENAR (RC,PRIJMENI) VALUES ("750525/1234","Novák");
• zdvojnásobení platů všech zaměstnanců UPDATE ZAMESTNANEC SET plat = 2*plat;
• zdvojnásobení platů a umazání dluhu všem ženám starších 60 let UPDATE ZAMESTNANEC SET plat = 2*plat dluh = 0 WHERE pohlavi=‘Z’ and vek>60; • vymazání všech čtenářů: DELETE FROM CTENAR;
ČTENÁŘ RČ
JMÉNO
PŘÍJMENÍ
320612/1234
František
Kuldanů
521006/5678
Josef
Novák
KNIHA ISBN
TITUL
AUTOR
80-11111-22-3
U nás
A. Jirásek
80-85190-38-9
Babička
B. Němcová
EXEMPLÁŘ CENA
D_NÁK
ISBN
1
100
25.3.1990
80-85190-38-9
2
100
25.3.1990
80-85190-38-9
3
150
26.3.1990
80-11111-22-3
PŘÍR_Č
SI_VYPŮJČIL RČ
PŘÍR_Č
DAT
320612/1234
1
10.11.2005
521006/5678
3
20.11.2005
Manipulace s daty - SELECT • dotazy pomocí příkazu SELECT FROM SELECT [DISTINCT|ALL]{* jméno_atr1, [jméno_atr2,…]} FROM jméno_rel1, [jméno_rel2,…] [WHERE podmínka][ORDER BY jméno_atr1, [ jméno_atr2,…]]
Manipulace s daty - SELECT • DISTINCT – ve výsledku potlačí duplicitní řádky
• ALL – implicitní volba, ve výsledku ponechá duplicitní řádky
•* – vybere všechny sloupce
Příklady: • výpis všech atributů všech čtenářů SELECT * FROM CTENAR
• výpis jmen a příjmení všech čtenářů SELECT JMENO, PRIJMENI FROM CTENAR
• výpis jmen a příjmení všech čtenářů s vynecháním opakujících se SELECT DISTINCT JMENO, PRIJMENI FROM CTENAR
• výpis všech čtenářů s příjmením Novák SELECT JMENO, PRIJMENI FROM CTENAR WHERE PRIJMENI="Novák" Pozn: lze použít operátory <, >, <>, <=, >=, logické operátory AND, OR, NOT
• výpis všech čtenářů Josef Novák SELECT * FROM CTENAR WHERE JMENO="Josef" AND PRIJMENI="Novák"
• výpis všech čtenářů s příjmením Novák nebo Pleticha SELECT JMENO, PRIJMENI FROM CTENAR WHERE PRIJMENI="Novák" OR PRIJMENI= "Pleticha"
• výpis všech čtenářů seřazených abecedně dle příjmení (ASC znamená řadit vzestupně – implicitní volba, není-li uvedeno jinak)
SELECT PRIJMENI, JMENO FROM CTENAR ORDER BY PRIJMENI ASC
SELECT PRIJMENI, JMENO FROM CTENAR ORDER BY PRIJMENI
ASC není nutné psát
• výpis všech čtenářů seřazených sestupně abecedně dle příjmení a jména SELECT PRIJMENI, JMENO FROM CTENAR ORDER BY PRIJMENI DESC, JMENO DESC
• výpis všech exemplářů, které jsou dražší než 100 Kč SELECT PRIR_C, CENA FROM EXEMPLAR WHERE CENA > 100 • výpis všech exemplářů, které stojí mezi 100 Kč a 1000 Kč včetně SELECT PRIR_C, CENA FROM EXEMPLAR WHERE CENA >= 100 AND CENA <= 1000
• porovnání v rámci intervalu je možné nahradit v SQL pomocí klíčového slova BETWEEN – předchozí dotaz můžeme napsat také:
SELECT PRIR_C, CENA FROM EXEMPLAR WHERE CENA BETWEEN 100 AND 1000
• každý výraz je možné negovat pomocí klíčového slova NOT: • výpis všech exemplářů, které jsou levnější než 100 Kč a dražší než 1000 Kč SELECT PRIR_C, CENA FROM EXEMPLAR WHERE CENA NOT BETWEEN 100 AND 1000
Realizace operace spojení relací • spojení relací se provádí pomocí dotazu SELECT • původní SQL86 nemělo žádné klíčové slovo pro operaci spojení
• spojení se realizovalo uvedením tabulek, které chceme spojit a podmínky • v SQL92 existuje navíc klíčové slovo JOIN
• výpis jmen a příjmení čtenářů, kteří mají půjčenou nějakou knihu SELECT DISTINCT JMENO, PRIJMENI FROM CTENAR,SI_VYPUJCIL WHERE CTENAR.RC = SI_VYPUJCIL.RC
• zapsat podmínku rovnosti atributů (přirozené spojení) je nutné, jinak SŘBD provede kartézský součin – spojí každý řádek s každým, což vede k zatížení sytému (např. má-li 1 tabulka 1000 řádek a druhá 10000, výsledkem je tabulka o 10 mil. řádků)
• pokud je cizím klíčem dvojice atributů, musíme zapsat podmínku rovnosti pro oba atributy a spojit spojkou AND WHERE TAB1.K1 = TAB2.K1 AND TAB1.K2 = TAB2.K2
• totéž platí, spojujeme-li více než dvě relace (viz dále) – zapisujeme rovnost pro všechny cizí klíče
• některé dotazy je výhodné zapsat pomocí množinové operace IN: – rychlejší zpracování
• stejný dotaz: výpis jmen a příjmení čtenářů, kteří mají půjčenou nějakou knihu pomocí IN SELECT DISTINCT JMENO, PRIJMENI FROM CTENAR WHERE RC IN (SELECT DISTINCT RC FROM SI_VYPUJCIL)
• množinovou operaci IN lze negovat – rychlejší zpracování
• stejný dotaz: výpis jmen a příjmení čtenářů, kteří nemají půjčenou žádnou knihu: SELECT DISTINCT JMENO, PRIJMENI FROM CTENAR WHERE RC NOT IN (SELECT DISTINCT RC FROM SI_VYPUJCIL) – některé dotazy by nešlo bez NOT IN realizovat
• výpis všech exemplářů knihy Babička, které jsou dražší než 120 Kč SELECT PRIR_C, TITUL, CENA FROM EXEMPLAR,KNIHA WHERE EXEMPLAR.ISBN=KNIHA.ISBN AND CENA > 120 AND TITUL= "Babička" Poznámka: relace je možné pojmenovat aliasem (pomocným jménem) a odkazovat se na ně tímto jménem
• výpis všech knih se stejným názvem, ale různým ISBN (např. od různých nakladatelů) SELECT PRVNI.ISBN, PRVNI.TITUL FROM KNIHA AS PRVNI, KNIHA AS DRUHY WHERE (PRVNI.NAZEV=DRUHY.NAZEV AND PRVNÍ.ISBN <> DRUHY.ISBN) – realizuje theta – spojení relace „sama se sebou“
Diskuse: Je dobře navržený model knihovny?
• výpis všech čtenářů, kteří mají půjčen nějaký exemplář knihy Psohlavci SELECT CTENAR.RC,JMENO,PRIJMENI FROM CTENAR, EXEMPLAR, SI_VYPUJCIL,KNIHA WHERE (KNIHA.NAZEV = "Psohlavci" AND KNIHA.ISBN = EXEMPLAR.ISBN AND EXEMPLAR.PRIR_C = SI_VYPUJCIL.PRIR_C AND SI_VYPUJCIL.RC = CTENAR.RC)
• úloha: přepište dotaz pomocí množinového konstruktu IN SELECT CTENAR.RC,JMENO,PRIJMENI FROM CTENAR, SI_VYPUJCIL WHERE SI_VYPUJCIL.RC = CTENAR.RC AND PRIR_C IN (SELECT PRIR_C FROM KNIHA, EXEMPLAR WHERE KNIHA.NAZEV = "Psohlavci" AND KNIHA.ISBN = EXEMPLAR.ISBN )
Rozšíření operace spojení • levé / pravé vnější spojení (LEFT / RIGHT OUTER JOIN) – výsledek zahrnuje i ty n-tice, které nemají v druhé relaci „partnera“ pro přirozené spojení – atributy z druhé relace se doplní hodnotami NULL
Příklad: EXEMPLÁŘ CENA
D_NÁK
ISBN
1
100
25.3.1990
80-85190-38-9
2
100
25.3.1990
80-85190-38-9
3
150
26.3.1990
80-11111-22-3
PŘÍR_Č
SI_VYPŮJČIL RČ
PŘÍR_Č
DAT
320612/1234
1
10.11.2005
521006/5678
2
20.11.2005
Levé vnější spojení EXEMPLÁŘ *L SI_VYPŮJČIL PŘÍR_Č CENA
D_NÁK
ISBN
RČ
DAT
1
100
25.3.1990
80-85190-38-9
320612/1234
10.11.2005
2
100
25.3.1990
80-85190-38-9
521006/5678
20.11.2005
3
150
26.3.1990
80-11111-22-3
– v posledním řádku jsou ve sloupcích RČ a DAT hodnoty NULL
FULL OUTER JOIN
R *F S = (R *L S) U (R *R S)
Operace spojení v SQL92 • nová klíčová slova v dotazech: – NATURAL JOIN • přirozené spojení
– CROSS JOIN • spojení křížem
– [INNER] JOIN • vnitřní spojení
– [INNER] JOIN ON • vnitřní spojení přes podmínku
Operace spojení v SQL92 – { LEFT | RIGHT | FULL } [OUTER] JOIN [ON] • vnější spojení (levé, pravé, plné)
– JOIN USING • spojení přes vyjmenované sloupce
– JOIN UNION • spojení sjednocením
– slova v závorkách [ ] jsou nepovinná
Přirozené spojení • výpis jmen a příjmení čtenářů, kteří mají půjčenou nějakou knihu – v SQL86 SELECT DISTINCT JMENO, PRIJMENI FROM CTENAR, SI_VYPUJCIL WHERE CTENAR.RC = SI_VYPUJCIL.RC
– v SQL92 pomocí přirozeného spojení SELECT DISTINCT JMENO, PRIJMENI FROM CTENAR NATURAL JOIN SI_VYPUJCIL • spojuje automaticky přes cizí klíče
– nebo pomocí spojení s podmínkou SELECT DISTINCT JMENO, PRIJMENI FROM CTENAR INNER JOIN SI_VYPUJCIL ON CTENAR.RC = SI_VYPUJCIL.RC
– sloupce, přes které se má provést spojení, se mohou uvést také za USING SELECT DISTINCT JMENO, PRIJMENI FROM CTENAR JOIN SI_VYPUJCIL USING (RC)
– hodí se pro případ, kdy přirozené spojení provede automaticky spojení přes dva atributy a je potřeba spojit pouze přes jeden
Kartézský součin • spojí každý řádek s každým – v SQL86 SELECT DISTINCT JMENO, PRIJMENI FROM CTENAR, SI_VYPUJCIL
– v SQL92 SELECT DISTINCT JMENO, PRIJMENI FROM CTENAR INNER JOIN SI_VYPUJCIL SELECT DISTINCT JMENO, PRIJMENI FROM CTENAR CROSS JOIN SI_VYPUJCIL
• výpis všech exemplářů knihy Babička, které jsou dražší než 120 Kč SELECT PRIR_C, TITUL, CENA FROM EXEMPLAR JOIN KNIHA ON EXEMPLAR.ISBN=KNIHA.ISBN WHERE CENA > 120 AND TITUL= "Babička„
• Poznámka: – za ON může být libovolná podmínka, např. A1
• výpis všech čtenářů, kteří mají půjčen nějaký exemplář knihy Psohlavci SELECT CTENAR.RC,JMENO,PRIJMENI FROM CTENAR JOIN SI_VYPUJCIL ON SI_VYPUJCIL.RC = CTENAR.RC JOIN EXEMPLAR ON EXEMPLAR.PRIR_C = SI_VYPUJCIL.PRIR_C JOIN KNIHA ON KNIHA.ISBN = EXEMPLAR.ISBN WHERE KNIHA.NAZEV = "Psohlavci"
Kartézský součin • spojí každý řádek s každým – v SQL86 SELECT DISTINCT JMENO, PRIJMENI FROM CTENAR, SI_VYPUJCIL
– v SQL92 SELECT DISTINCT JMENO, PRIJMENI FROM CTENAR INNER JOIN SI_VYPUJCIL SELECT DISTINCT JMENO, PRIJMENI FROM CTENAR CROSS JOIN SI_VYPUJCIL
Vnější spojení • EXEMPLÁŘ *L SI_VYPŮJČIL SELECT * FROM EXEMPLAR LEFT OUTER JOIN SI_VYPUJCIL ON EXEMPLAR.PRIR_C = SI_VYPUJCIL.PRIR_C
Spojení sjednocením • nespojí žádné řádky, do výsledku vloží řádky z obou relací a doplní je zprava/zleva hodnotami NULL SELECT * FROM EXEMPLAR UNION JOIN SI_VYPUCIL
Spojení sjednocením
PŘÍR _Č
CENA
D_NÁK
ISBN
1
100
25.3.1990
80-85190-38-9
2
100
25.3.1990
80-85190-38-9
3
150
26.3.1990
80-11111-22-3
RČ
PŘÍR_Č
DAT
320612/1234
1
10.11.2005
521006/5678
2
20.11.2005
