Transcript P07-SQL-vyber dat
Datab
á
zov
é
syst
é
my
SQL
Výběr dat
Př
í
kaz SELECT
Jádro databázového jazyka SQL.
Kompletní syntaxe vypadá následovně:
SELECT [DISTINCT|ALL] seznam vybíraných sloupců FROM seznam tabulek [JOIN v případě výběru z více tabulek deklarujeme způsob jejich provázání (left/right join)] [WHERE restrikce-podmínka výběru] [GROUP BY výrazy pro seskupení] [HAVING doplňující podmínky pro skupinu-agregační funkce] [ORDER BY způsob seřazení výsledků dotazu] [LIMIT omezení počtu záznamů]
SELECT – schéma
Logické pořadí vyhodnocení (asociativita SELECT klauzulí):
FROM
WHERE
GROUP BY
HAVING
projekce
SELECT
ORDER BY
SELECT ... FROM ...
nejjednodušší forma dotazu:
SELECT [ALL] | DISTINCT
expression
FROM
tabulka1, tabulka2, ...
výraz může obsahovat sloupce (hvězdička * je zástupce pro všechny neuvedené sloupce) konstanty agregace na výrazech
DISTINCT
eliminuje duplikátní řádky ve výstupu,
ALL
(resp. bez specifikace) povoluje ve výstupu i duplikátní řádky (pozor, má vliv na agregační funkce – u
DISTINCT
vstupuje do agregačních funkcí méně hodnot)
FROM
obsahuje jednu nebo více tabulek, na kterých se dotaz provádí pokud je specifikováno více tabulek, provede se kartézský součin
Netříděný dotaz
netříděný dotaz sestává vždy příkaz
SELECT
(hlavní logika dotazování) případně z příkazů
UNION
popsaným v příkazu ,
INTERSECTION SELECT
) ,
EXCEPT
(sjednocení/průnik/rozdíl dvou nebo více výsledků získaných dotazem výsledky nemají definované uspořádání (resp. jejich pořadí je určeno implementací vyhodnocení dotazu)
Tříděný dotaz
výsledek netříděného dotazu lze setřídit klauzule
ORDER BY
, třídění podle sloupce (
column
) třídit lze vzestupně (
ASC
) nebo sestupně (
DESC
) podle definovaného uspořádání lze definovat více sekundárních třídících kritérií, která se uplatní v případě nedefinovaného lokálního pořadí (shodné primární tříděné hodnoty)
Výběr určitých sloupců - PROJEKCE
Příkaz provede vypsání všech vy jmenovaných sloupců.
SELECT prijmeni, jmeno, ulice, mesto FROM Klient; prijmeni
Nový
jmeno
Jan Nováková Petra Nováček Novotná Pavel Ivana
ulice mesto
Jánská 25 Zlín Poštová 128 Lešná Nezvalova 697 Zlín Pod mlýnem Lukov Obecně zde můžeme uvést výčet sloupců z více tabulek.
Dynamický sloupec
Příkaz nám vydefinuje sloupec, který se spočte z některého z ostatních sloupců. Tento sloupec se pak přidá k vypisované tabulce.
SELECT c_uctu, stav,(stav/25) FROM Ucet; c_uctu
4568517 6585485 3256151
stav
42000 75000 25000
(stav/25)
1680 3000 1000 V tomto příkladě se přidá stav účtu v „Euro", nebo-li stav dělený 25. Nový sloupec tabulky je pojmenován automaticky dle konvencí použité databáze. K definici vypočteného sloupce lze použít i volání některého složitějšího programu, který daný sloupec vypočte.
Pojmenov
á
n
í
nov
é
ho sloupce: AS
Pokud máme potřebu si nově založený sloupec pojmenovat, uděláme to pomocí výše uvedeného postupu .
SELECT c_uctu, stav,(stav/25) AS euro FROM Ucet; c_uctu
4568517 6585485 3256151
stav
42000 75000 25000
euro
1680 3000 1000
SELECT ... FROM ... WHERE ...
logická podmínka selekce, tj. řádek tabulky, který podmínku splňuje, se dostane do výsledku jednoduché podmínky lze kombinovat logickými spojkami
AND
,
OR
,
NOT
lze se ptát srovnávacím predikátem (=, <>, <, >, <=, >=) na hodnoty dvou atributů na interval expr1
[NOT] BETWEEN
(expr2
AND
expr3) řetězcovým predikátem
[NOT] LIKE
obsahující speciální znaky
%
“
maska
“, kde maska je řetězec (reprezentující libovolný podřetězec) a
_
(reprezentující libovolný znak) testem na nedefinovanou hodnotu, (
expr1)
predikátem příslušnosti do množiny
expr1
(
unordered_query
)
IS [NOT] NULL [NOT] IN
jednoduchým existenčním kvantifikátorem
EXISTS
(
unordered_query
) testující prázdnost rozšířenými kvantifikátory
Výběr údajů s podmínkou WHERE-RESTRIKCE
V podmínce
WHERE
je možné použít následující operátory:
Operátor
= > < <= >= != IS NOT NULL IS NULL BETWEEN NOT BETWEEN OR (rovněž ) AND (rovněž &&) NOT (rovněž !)
Popis
Rovná se Je menší než Je větší než Je menší než nebo roven Je větší než nebo roven Nerovná se Není prázdná (obsahuje hodnotu) Je prázdná (neobsahuje hodnotu) V rozmezí Mimo rozmezí Alespoň jedna podmínka je splněná Všechny (obě) podmínky jsou splněny Podmínka není splněna
Zobrazení hodnot v určitém rozpětí: BETWEEN
BETWEEN
slouží k zobrazení hodnot sloupce, které se nacházejí v určitém rozpětí.
SELECT * FROM Ucet WHERE stav BETWEEN 40000 AND 80000 ; c_uctu
4568517 6585485
stav
42000 75000
r_cislo pobocka
826111/5267Zarámí 325 580506/4891 Nám. Míru 12 To samé bychom mohli samozřejmě zapsat bez BETWEEN:
SELECT * FROM Ucet WHERE stav>=40000 AND stav<=80000
Využití operátorů LIKE A NOT LIKE
Využívají se pro porovnání řetězců v širším měřítku.
SELECT prijmeni, jmeno, mesto FROM Klient WHERE mesto LIKE ”L%”;
Vypíše příjmení a jména všech klientů, kteří jsou z města začínajícího na písmeno ‚L‚.
Speciální znaky používané při vyhledávání podřetězců : %
(procenta) libovolný počet libovolných znaků (včetně 0)
_
(podtržítko) jeden libovolný znak
Omezení počtu záznamů ve výsledné sadě
Klauzule LIMIT stanovuje počet záznamů, které budou do výsledné sady zahrnuty:
SELECT * FROM Klient LIMIT 3;
-vypíše první 3 záznamy
SELECT * FROM Klient LIMIT 5,10;
- vypíše 10 záznamů v rozmezí 6-15.
Indexování záznamů probíhá od 0, tj. LIMIT 0,5 vypíše 1. až 6. záznam.
Chceme-li vybrat druhý nejvyšší stav účtu:
SELECT * FROM Ucet ORDER BY stav DESC LIMIT 1,1;
Výpis ř
á
dek s pr
á
zdným sloupcem
Pro nalezení hodnot NULL je potřeba vyzkoušet test IS NULL.
Uvažujme, že v tabulce Klient je pole Email, které může ale nemusí být vyplněno.
Následující příklad ukazuje rozdíl mezi vyhledáním NULL „nulového“ Emailu a prázdného Emailu:
SELECT * FROM Klient WHERE Email IS NULL; SELECT * FROM Klient WHERE Email= ' ';
Spojení tabulek - JOIN
Spojení bez speciálních konstrukcí (SQL 86):
(vnitřní) spojení na podmínku a přirozené spojení lze realizovat jako omezený kartézský součin, tj.
SELECT
...
FROM
tabulka1, tabulka2
WHERE
tabulka1.A = tabulka2.B
levé/pravé polospojení se upřesní v klauzuli
SELECT
, tj. projekcí
Nové konstrukce SQL 92:
kartézský součin přirozené spojení
tabulka2
WHERE
–
SELECT
– ...
SELECT
...
FROM
...
FROM
tabulka1
CROSS JOIN
tabulka1 tabulka2 ...
NATURAL JOIN
vnitřní spojení SELECT
... –
FROM WHERE
...
tabulka1
INNER JOIN
tabulka2
ON search_condition
sjednocení spojení SELECT
...
FROM
– vrací řádky první tabulky doplněné NULL hodnotami v atributech druhé tabulky + řádky druhé tabulky doplněné o NULL hodnoty v atributech první tabulky tabulka1
UNION JOIN
tabulka2
WHERE
...
levé, pravé, plné vnější spojení SELECT
tabulka2 ...
ON FROM
tabulka1 –
LEFT search_condition
...
|
RIGHT WHERE
...
|
FULL OUTER JOIN
Spojování tabulek - JOIN
CROSS JOIN
znamená spojení každého záznamu s každým.
INNER JOIN
vrátí z obou spojovaných tabulek veškeré záznamy, ve kterých klíčový sloupec obsahuje v obou tabulkách stejnou hodnotu.
NATURAL
spojí ty dvojice záznamů, které mají v klíčových sloupcích stejného jména stejnou hodnotu.
LEFT
[OUTER ] JOIN znamená, že každý záznam první tabulky se musí spojit s některým záznamem druhé tabulky. Není-li v druhé tabulce nalezen žádný vyhovující záznam, pak se spojí s NULL.
RIGHT
[OUTER ] JOIN znamená, že každý záznam druhé tabulky se musí spojit s některým záznamem první tabulky. Není-li v první tabulce nalezen žádný vyhovující záznam, pak se spojí s NULL.
FULL
[OUTER ] JOIN znamená, že každý záznam z každé tabulky se musí spojit s některým záznamem protější tabulky. Není-li v protější tabulce vyhovující záznam spojí se s NULL.
S operacemi
OUTER JOIN
se musí zacházet velmi opatrně, protože pořadí v jakém se tabulky spojují má zásadní vliv na to, které řádky se zachovají a které nikoli.
Nejčastěji používané typy spojení:
- vnitřní spojení – - vnější spojení –
inner join outer join
(nebo levé spojení –
left join
).
Poznámka:
Tam, kde se pracuje s více tabulkami, které můžou obsahovat stejnojmenné sloupce, musíme sloupce jednoznačně identifikovat. K tomu se používá „
tečková
“ konvence –
název_tabulky.název sloupce
.
Příklady:
Vnitřní spojení – inner join
Chceme-li vypsat informace o účtě, ale místo rodného čísla klienta, které je v tabulce UCET, chceme vidět jeho jméno a příjmení:
SELECT ucet.c_uctu,klient.prijmeni,klient.jmeno FROM klient INNER JOIN ucet ON klient.r_cislo=ucet.r_cislo;
Používání aliasů
: –
alias
je symbolické přejmenování tabulky nebo sloupce pomocí klíčového slova
AS
(nepovinné) – jsou to řetězce složené z písmen, číslic a podtržítek, ve kterých je potřeba rozlišovat velká a malá písmena:
SELECT u.c_uctu,k.prijmeni,k.jmeno FROM klient AS k INNER JOIN ucet AS u ON k.r_cislo=u.r_cislo;
Příklady:
Vnější spojení – outer join
Levé vnější spojení
žádný účet.
- vypíše všechny klienty, tedy i ty, kteří nemají momentálně
SELECT u.c_uctu,k.prijmeni,k.jmeno FROM klient AS k LEFT JOIN ucet AS u ON k.r_cislo=u.r_cislo;
Pravé vnější spojení
by nebyl nalezen odpovídající klient. Správně fungující systém by ale měl tomuto stavu zabránit – - vrací všechny záznamy z tabulky UCET a tedy i ty, pro které
zachování referenční integrity
.
SELECT u.c_uctu,k.prijmeni,k.jmeno FROM klient AS k LEFT JOIN ucet AS u ON k.r_cislo=u.r_cislo;
Poznámka:
Když obsahují obě tabulky vnějšího spojení stejný název pole (např. r_cislo), bylo by možné dotaz zjednodušit:
SELECT * FROM klient LEFT JOIN ucet USING (r_cislo);
Propojení 3 tabulek
Propojení 3 tabulek použítím podmínky
WHERE
:
SELECT u.c_uctu,k.prijmeni,k.jmeno,p.nazev
FROM klient k, ucet u, pobocka p WHERE k.r_cislo=u.r_cislo AND p.c_p=u.pobocka;
Propojení 3 tabulek použítím
JOIN-u
:
SELECT u.c_uctu,k.prijmeni,k.jmeno,p.nazev
FROM klient k INNER JOIN ucet u ON k.r_cislo=u.r_cislo
INNER JOIN pobocka p ON p.c_p=u.pobocka;
Samosloučení
Výpis seznamu příjmení, stylem "každý z každým":
SELECT k1.prijmeni, k2.prijmeni FROM Klient AS k1, Klient AS k2 WHERE k1.r_cislo != k2.r_cislo
ORDER BY k1.prijmeni
SQL umožňuje otevřít pomocí příkazu SELECT dvakrát tu samou tabulku, je pouze nutné druhé tabulce přiřadit jiný
lokální alias
.