Transcript Databázové systémy I

Databázové systémy II
Přednáška č. 8
RNDr. David Žák, Ph.D.
Fakulta elektrotechniky a informatiky
[email protected]
Obsah
•
Operátor CAST
•
Operátory a funkce pro práci s hodnotami NULL
•
Řádkové a tabulkové konstruktory
•
Pokročilé dotazy ve standardu SQL 2
•
Synonyma
Výrazy s operátorem CAST
CAST ( <hodnota_výrazu> AS <datový_typ>)
Tento výraz zajišťuje konverzi hodnoty do určeného datového typu.
Kromě datového typu je možné použít ve výrazu CAST i doménu, což je specifická množina platných
datových hodnot.
Použití výrazů s operátorem CAST:
• zejména při používání jazyka SQL z jiného programovacího jazyka, jehož datové typy
neodpovídají datovým typům podporovaným standardem SQL (například pro převod data a času
na řetězec)
• Konverze dat z databázové tabulky, kde je sloupec definován pomocí špatného datového typu
(například čísla v uložená jako řetězce znaků)
• Odstranění rozdílů mezi různými typy pro stejná data ve 2 různých tabulkách
Výrazy s operátorem CAST
Příklad
SELECT ID, JMENO, CAST ( VYSKA AS VARCHAR(4))
FROM TRPASLICI;
Výrazy s operátorem CASE
CASE
WHEN <vyhledávací_podmínka>
THEN <výsledný_výraz_pravda>
[WHEN <vyhledávací_podmínka2>
THEN <výsledný_výraz_pravda2>]
[ELSE <výsledný_výraz_nepravda>]
END
Tento výraz umožňuje provádět jednoduché rozhodování.
Příklad
SELECT jmeno,
CASE WHEN vyska>110 THEN 'velký'
WHEN vyska>107 THEN 'střední'
ELSE 'malý'
END as velikost
FROM trpaslici
Výrazy s operátorem CASE
Příklad dotazu, který umožňuje prostřednictvím proměnné zvolit vhodnou variantu řazení výsledků
dotazu:
SELECT d.title, a.lastname, a.isbn, cost
FROM authors a, details d
WHERE a.isbn = d.isbn
ORDER BY CASE :byorder
WHEN 'TITLE' then title
WHEN 'LASTNAME' then lastname
WHEN 'ISBN' then isbn
WHEN 'COST' then cost
ELSE NULL
END;
Práce s hodnotami NULL
NVL(testovaný_výraz, výraz_if_NULL)
Pokud není hodnota testovaný_výraz NULL, je vrácena hodnota testovaný_výraz, jinak
výraz_if_NULL
NVL2(testovaný_výraz, výraz_if_NOT_NULL, výraz_if_NULL)
Pokud není hodnota testovaný_výraz NULL, je vrácena hodnota výraz_if_NOT_NULL, jinak
výraz_if_NULL
Příklady:
select NVL(dodavatel_mesto, 'neznámé')
from dodavatele;
select NVL2(dodavatel_mesto, 'vyplněno', ' neznámé ')
from dodavatele;
Výrazy s operátorem COALESCENCE
Podmínky ve výrazech CASE se používají nejčastěji pro zpracování hodnot NULL. Obvykle je potřeba
nahradit hodnotu NULL hodnotou nějakého řetězce nebo výchozí hodnotou.
Operátor COALESCENCE vrací první nenulovou hodnotu. Pokud jsou všechny hodnoty NULL, vrací
NULL.
Příklad a jeho přepis do výrazu s operátorem COALESCENCE
SELECT jmeno,
CASE WHEN (prodej_plan IS NOT NULL)
THEN plan_prodej
WHEN
(prodej_skutecnost IS NOT NULL)THEN prodej_skutecnost
ELSE 0
END as prodej_objem
FROM prodejci;
SELECT jmeno, COALESCENCE(prodej_plan , prodej_skutecnost, 0 )
FROM prodejci;
Funkce NULLIF
NULLIF( <výraz1>, <výraz2> )
NULLIF funkce porovnává výrazy výraz1 a výraz2.
Pokud výraz1 a výraz2 se rovnají, NULLIF funkce vrací NULL.
V opačném případě vrací výraz1.
Příklady využití funkce NULLIF
NULLIF(12, 12)
NULLIF(12, 13)
NULLIF('apples', 'apples')
NULLIF('apples', 'oranges')
NULLIF(NULL, 12)
vrací NULL
vrací 12
vrací NULL
vrací 'apples'
vrací chybové hlášení ORA-00932
Konstruktor řádkové hodnoty
Standard SQL2 umožňuje řádky v SQL výrazech používat stejným způsobem, jako se používají
skalární hodnoty. Standard poskytuje syntaxi pro vytváření řádků dat, umožňuje používat
poddotazy s využitím celých řádků a definuje význam řádků pro operátory porovnání atd.
Konstruktor řádkové hodnoty:
Příkladem je například:
(7, 'Smudla', NULL, 1993)
což představuje řádek se 4 sloupci
Konstruktor tabulkové hodnoty
Příklad ukazuje možnost použití pro porovnání
konstruktoru řádkového hodnoty prezentovaného (vyska, narozen)
a konstruktoru tabulkové hodnoty ((117, 1984), (115,1983))
SELECT jmeno FROM trpaslici
WHERE (vyska, narozen) IN ((117, 1984), (115,1983))
Uvedená konstrukce může významně zkrátit zápis některých komplikovaných podmínek
v dotazech.
SELECT trpaslici.jmeno, count (*) FROM trpaslici
WHERE ('Stydlin','na louce') =
ANY (SELECT jmeno, sachta FROM sachty
WHERE sachty.Id=tezby.Id_sachty
AND trpaslici.Id=tezby.Id_trpaslika
AND tezby.SKUTECNOST>0)
GROUP BY trpaslici.jmeno;
CREATE TABLE AS SELECT
Jednou z možností efektivní práce je vytvoření nové tabulky z tabulky existující.
Použitím této syntaxe budou vytvořeny sloupce stejného typu, jako jsou sloupce ve výsledku
vloženého dotazu SELECT, který je využit pro generování řádků nové tabulky.
CREATE TABLE new_table
AS (SELECT * FROM old_table);
Příklad:
CREATE TABLE dodavatele
AS (SELECT * FROM spolecnosti WHERE id > 1000);
CREATE TABLE AS SELECT
Upřesnění sloupců a řádků vkládaných do nové tabulky :
CREATE TABLE new_table
AS (SELECT column_1, column2, ... column_n FROM old_table WHERE podmínka);
Příklad:
CREATE TABLE trpaslici3
AS (SELECT JMENO, CAST ( VYSKA AS VARCHAR(4)) vyska_ch
FROM TRPASLICI WHERE ID<5);
Samozřejmě je možné, aby vložený dotaz SELECT pracoval s několika tabulkami, výsledná tabulka
bude sloupci a řádky odpovídat výsledku vnořeného dotazu.
INSERT INTO …. SELECT
Stejně jako je možné vytvořit tabulku s více řádky jediným příkazem, je možné i vložit více řádku
do tabulky pomocí jediného výrazu.
Příklad
INSERT INTO trpaslici3 (jmeno, vyska_ch)
SELECT jmeno, vyska
FROM trpaslici WHERE jmeno='Stydlin';
Uvedený příklad zároveň demonstruje i automatickou konverzi formátů
(sloupce vyska a vyska_ch).
JOIN s klauzulí USING
Doplňme si možnosti zápisu spojení tabulek pomocí klauzule JOIN o syntaxi s využitím klauzule
USING či NATURAL JOIN.
Při přirozeném spojení pomocí NATURAL JOIN se porovnávají všechny sloupce z obou
spojovaných tabulek, které mají stejné názvy.
Při spojení pomocí USING se porovnávají ty sloupce z obou spojovaných tabulek, které jsou
uvedeny v závorce za klauzulí USING (sloupec1, sloupec2, …).
Uvedené příklady mohou zpřehlednit zápis dotazů.
SELECT * FROM sachty JOIN rudy2 ON sachty.id_rudy=rudy2.id_rudy
SELECT * FROM sachty JOIN rudy2 USING (id_rudy)
SELECT * FROM sachty NATURAL JOIN rudy2
CROSS JOIN
Křížové spojení odpovídající kartézskému součinu vygenerujeme zápisem spojení s vynecháním
podmínek spojení.
SELECT jmeno, sachta FROM trpaslici, sachty
Při zápisu s využitím klauzule JOIN je nutné použít ještě klíčové slovo CROSS
SELECT jmeno, sachta FROM trpaslici CROSS JOIN sachty
TRUNCATE
Smaže všechny řádky tabulky nebo clusteru, není možné provést rollback
TRUNCATE TABLE nazev_tabulky;
Smazání řádků příkazem TRUNCATE může být více efektivní než odstranění tabulky příkazem
DROP a opětovné vytváření příkazem CREATE TABLE. Důvodem je potřeba vytvoření velkého
množství objektů závislých na tabulce (indexy, omezení, triggery).
Smazání řádků příkazem TRUNCATE může být rychlejší než použití příkazu DELETE a to zvláště v
případě, kdy nad tabulkou jsou vytvořeny triggery, indexy a další závislosti.
Vnořené dotazy
Vnořené dotazy (poddotazy) se mohou vyskytovat prakticky na libovolném místě hlavního dotazu.
Výsledkem vnořeného dotazu je obecně „virtuálního tabulka“ o několika řádcích a několika
sloupcích (v určitých případech pak jednosloupcová či jednořádková) nebo jen jedna jediná
hodnota.
a) Vnořený dotaz v klauzuli FROM nebo JOIN – s výsledkem poddotazu se pracuje stejně jako při
spojení s tabulkou či pohledem, výsledek poddotazu je v tomto případě vhodné označit
aliasem
b) Vnořený dotaz vracející jednu hodnotu v části WHERE – např. výsledek agregační funkce (SUM,
COUNT, MIN, MAX, AVG) se porovnává s hodnotou v určitém sloupci tabulky (nebo je
vnořeným dotazem vyhledána hodnota v určitém sloupci, kdy řádek tabulky je identifikován
pomocí primárního klíče)
c) Použití vnořeného dotazu v části WHERE s využitím množinových operátorů IN, ANY, SOME, ALL
Vnořené dotazy
d) Využití výsledků vnořeného dotazu pomocí operací – UNION, INTERSECT, MINUS, …
e) Použití vnořeného dotazu v části definice výstupních sloupců dotazu, kde se prostřednictvím
vnořeného dotazu doplňují hodnoty na základě ostatních hodnot v daném řádku
SELECT (select c1 from t1 b where a.c1 = b.c1), c2
FROM t1 a WHERE <condition>
Komplexní UPDATE
Můžeme také provádět mnohem komplexnější příkazy UPDATE, například změnit hodnoty v řádku
na základě hodnot v jiných tabulkách.
Poněvadž není možné uvést více než jednu tabulku v příkazu UPDATE, můžeme použít klauzuli
EXISTS.
Příklad:
UPDATE suppliers
SET supplier_name =
(SELECT customers.name FROM customers
WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS
(SELECT customers.name FROM customers
WHERE customers.customer_id = suppliers.supplier_id);
Kdykoli je supplier_id nalezen mezi hodnotami customer_id v tabulce customers, jeho
customers_name přepíše stávající hodnotu supplier_name. Předpokládáme, že sloupce
customer_id je primárním klíčem tabulky customers.
Synonyma
Použijte příkaz CREATE SYNONYM pro vytvoření synonyma, které je alternativním jménem pro
tabulku, pohled, sekvenci, proceduru, funkci, balíček, uživatelem definovaný objekt nebo jiné
synonyma.
Příklad vytvoření synonama offices pro tabulku „locations“ ve schématu „hr“.
CREATE SYNONYM offices FOR hr.locations;
Specifikací PUBLIC vytvoříte veřejné synonyma, které jsou použitelné pro všechny uživatele.
Pro použití synonyma však musí mít uživatel definována přístupová práva k danému objektu, na který
se synonyma odkazuje.
Příklad
CREATE PUBLIC SYNONYM suppliers FOR app.suppliers;
Pseudo-sloupec ROWNUM
ROWNUM – magický sloupec, příčinou řady potíží, proto je nezbytné mu porozumět, pak může být
velmi užitečný.
Lze jej použít:
Pro ladění dotazů,
Pro číslování v rámci dotazu,
pro provádění nejvyšších N- zpracování
Sloupci ROWNUM budou přiřazena čísla 1, 2, 3, 4, .. N
Hodnota ROWNUM není přiřazena řádku, řádek v tabulce nemá číslo.
Hodnota ROWNUM je přiřazena řádku po jeho průchodu fází predikátu dotazu, ale před řazením
nebo souhrnem.
Pseudo-sloupec ROWNUM
Hodnota ROWNUM je zvýšena pouze po jejím přiřazení,
proto následující dotaz nikdy nevrátí žádný řádek
SELECT * FROM zamestnanci WHERE ROWNUM<=5 ORDER BY mzda
Správný zápis pro omezení počtu řádků:
SELECT * FROM
(SELECT * FROM zamestnanci ORDER BY mzda)
WHERE ROWNUM<=5
Správný zápis pro uvedení rozsahu řádků:
SELECT * FROM
(SELECT dotaz.*, ROWNUM as radek FROM
(SELECT * FROM trpaslici ORDER BY jmeno) dotaz)
WHERE radek BETWEEN 3 and 5;
Použití sloupce ROWNUM nahrazuje klauzuli LIMIT známou z MySQL
pro určení rozsahu řádků na výstupu.
Náhrada ROWNUM
Alternativně je možné použít analytické funkce ROW_NUMBER()
SELECT * FROM
(SELECT jmeno, ROW_NUMBER() OVER (ORDER BY jmeno) as radek
FROM trpaslici)
WHERE radek BETWEEN 3 and 5
Příkazy s klauzulí WITH
Klauzule WITH umožňuje přiřadit název určitému poddotazu uvedenému před
vlastním dotazem SELECT. V něm se pak můžete odkazovat na tento poddotaz
zadáním jména poddotazu – obdobně jako při práci s pohledy.
Oracle optimalizuje zpracování poddotazu uvedeného jména - buď jako inline pohled
nebo jako dočasnou tabulku.
Jednotlivé poddotazy se mohou odkazovat na předchozí poddotazy, stejně jako se na
ně odkazuje hlavní dotaz.
Použití klauzule WITH je velice vhodné v případě, kdy je výsledek poddotazu
odkazován vícekrát v rámci jediného dotazu, například, když průměrné hodnoty
zjištěné poddotazem musí být několikrát porovnávány během vykonávání dotazu a
běžné řešení by znamenalo bud zřízení samostatného pohledu nebo uvedení několika
totožných vnořených dotazů v rámci hlavního dotazu.
Architektury a techniky DS – cv. 1
25
Příkazy s klauzulí WITH - příklad
WITH <alias_name> AS (subquery_sql_statement)
SELECT <column_name_list> FROM <alias>;
Příklad:
select
store_name,
sum(quantity) store_sales,
(select sum(quantity) from sales)/(select count(*) from store) avg_sales
from
store s,
sales sl
where
s.store_key = sl.store_key
having
sum(quantity) > (select sum(quantity) from sales)/(select count(*) from store)
group by
store_name
Architektury a techniky DS – cv. 1
26
Příkazy s klauzulemi WITH - příklad
WITH
sum_sales AS
select sum(quantity) all_sales from stores,
number_stores AS
select count(*) nbr_stores from stores,
sales_by_store AS
select store_name, sum(quantity) store_sales from
store natural join sales
SELECT
store_name
FROM
store,
sum_sales,
number_stores,
sales_by_store
WHERE
store_sales > (all_sales / nbr_stores);
Architektury a techniky DS – cv. 1
27
Syntaxe průchodu stromů v Oracle 9i,10
SELECT sloupce FROM tabulka
[WHERE podmínka3]
START WITH podmínka1
CONNECT BY podmínka2
[ORDER BY …]
Řádky vyhovující podmínce ve START WITH jsou považovány za kořenové řádky na první úrovni
vnoření
Pro každou řádku na úrovni i se rekurzivně hledají přímí potomci vyhovující podmínce v klauzuli
CONNECT BY na úrovni i+1
Řádka předka se v podmínce označuje klíčovým slovem PRIOR
Na závěr jsou odstraněny řádky nevyhovující podmínce ve WHERE
Pokud není definováno třídění, odpovídá pořadí průchodu pre-order
Každý řádek obsahuje pseudo-sloupec LEVEL, obsahující úroveň řádku v hierarchii
Architektury a techniky DS – cv. 1
28
Příklad hierarchického dotazu
Příklad:
SELECT PRIJMENI,
JMENO,
level
-- pseudosloupec označující úroveň
FROM A_HR.ZAMESTNANCI
CONNECT BY MANAZER_ID = PRIOR ZAMESTNANEC_ID
-- MANAZER_ID se rovná ZAMESTNANEC_ID u předchůdce
START WITH MANAZER_ID is null;
-- začni od zaměstnance, který nemá nadřízeného
Architektury a techniky DS – cv. 1
29
Doplnění hierarchických dotazů
•
Pro jednotlivé záznamy můžete také získat cestu od nejvyššího záznamu (jak to znáte třeba ze
souborového systému) nebo řadu dalších informací:
•
Funkce SYS_CONNECT_BY_PATH vrací cestu v hierarchii k aktuálnímu záznamu.
•
Klauzule CONNECT_BY_ROOT vrací hodnotu z příslušného záznamu nejvyšší úrovně (tj.
například nejvyššího manažera).
•
Pokud byste chtěli výstup z dotazu použít pro zobrazení ve formě rozbalovací hierarchie tak,
jak to třeba dělá u souborů Windows Explorer, bude se vám hodit i pseudosloupec
CONNECT_BY_ISLEAF, který určuje, zda je aktuální záznam na poslední úrovni hierarchie
(CONNECT_BY_ISLEAF=1) nebo zda má podřízené záznamy (CONNECT_BY_ISLEAF=0).
Architektury a techniky DS – cv. 1
30
Příklad hierarchických dotazů
SELECT
lpad(' ',level*3)||PRIJMENI||' '||JMENO name,
SYS_CONNECT_BY_PATH(PRIJMENI, '/') path,
CONNECT_BY_ROOT PRIJMENI topmgr,
CONNECT_BY_ISLEAF isleaf,
level
FROM A_HR.ZAMESTNANCI
CONNECT BY MANAZER_ID = PRIOR ZAMESTNANEC_ID
START WITH MANAZER_ID is null
ORDER SIBLINGS BY PRIJMENI;
NAME
King Steven
Cambrault Gerald
Bates Elizabeth
Bloom Harrison
Fox Tayler
Kumar Sundita
Ozer Lisa
Smith William
De Haan Lex
Hunold Alexander
Austin David
PATH
/King
/King/Cambrault
/King/Cambrault/Bates
/King/Cambrault/Bloom
/King/Cambrault/Fox
/King/Cambrault/Kumar
/King/Cambrault/Ozer
/King/Cambrault/Smith
/King/De Haan
/King/De Haan/Hunold
/King/De Haan/Hunold/Austin
Architektury a techniky DS – cv. 1
TOPMGR
King
King
King
King
King
King
King
King
King
King
King
ISLEAF
0
0
1
1
1
1
1
1
0
0
1
31
LEVEL
1
2
3
3
3
3
3
3
2
3
4
Hierarchické dotazy dle ANSI SQL
• Hierarchické dotazy dle ANSI SQL používají rekurzívní WITH klauzuli, která se
odkazuje sama na sebe.
Zavedení této syntaxe do Oracle 11gR2 zajišťuje hierarchickým dotazům
Oracle SQL kompatibilitu s ANSI.
Architektury a techniky DS – cv. 1
32
Hierarchické dotazy dle ANSI SQL
with employees (empno, name, job, mgr, hierlevel) as
( select empno, ename, job, mgr, 1
from emp
where mgr is null
union all
select e.empno, e.ename, e.job, e.mgr, m.hierlevel + 1
from emp e
join employees m on (m.empno = e.mgr) -- podmínka spojení a rekurzívní volání
)
select *
from employees
Viz např. http://technology.amis.nl/blog/6104/oracle-rdbms-11gr2-goodbye-connect-by-or-theend-of-hierarchical-querying-as-we-know-it
Architektury a techniky DS – cv. 1
33
Hierarchické dotazy dle ANSI SQL
with org_pracovniku (ZAMESTNANEC_ID , PRIJMENI, JMENO, MANAZER_ID, uroven) as
( select ZAMESTNANEC_ID , PRIJMENI, JMENO, MANAZER_ID , 1
from A_HR.ZAMESTNANCI
where MANAZER_ID is null
-- začni od zaměstnance, který nemá nadřízeného
union all
-- spoj výsledek předchozího dotazu s výsledkem následujícího dotazu
select pracovnici.ZAMESTNANEC_ID , pracovnici.PRIJMENI, pracovnici.JMENO, pracovnici.MANAZER_ID, manazeri.uroven+ 1
from A_HR.ZAMESTNANCI pracovnici
join org_pracovniku manazeri on (manazeri.ZAMESTNANEC_ID = pracovnici.MANAZER_ID) -- podmínka spojení a rekurzívní volání
)
select *
from org_pracovniku
Viz např. http://technology.amis.nl/blog/6104/oracle-rdbms-11gr2-goodbye-connect-by-or-the-end-of-hierarchical-querying-as-we-know-it
Architektury a techniky DS – cv. 1
34