Datab zov syst my ii
This presentation is the property of its rightful owner.
Sponsored Links
1 / 34

Databázové systémy II PowerPoint PPT Presentation


  • 45 Views
  • Uploaded on
  • Presentation posted in: General

Databázové systémy II. Přednáška č. 8 RNDr. David Žák, Ph.D . Fakulta elektrotechniky a informatiky david.zak @ upce.cz. 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.

Download Presentation

Databázové systémy II

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Datab zov syst my ii

Databázové systémy II

Přednáška č. 8

RNDr. David Žák, Ph.D.

Fakulta elektrotechniky a [email protected]


Obsah

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

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 cast1

Výrazy s operátorem CAST

Příklad

SELECT ID, JMENO,CAST ( VYSKA AS VARCHAR(4))

FROM TRPASLICI;


V razy s oper torem case

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 case1

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

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 c oalescence

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

Funkce NULLIF

NULLIF( <výraz1>, <výraz2> )

NULLIFfunkce porovnává výrazyvýraz1 a výraz2.Pokudvýraz1 a výraz2se rovnají, NULLIF funkce vrací NULL. V opačném případě vrací výraz1.

Příklady využití funkce NULLIF

NULLIF(12, 12) vrací NULL

NULLIF(12, 13) vrací 12

NULLIF('apples', 'apples') vrací NULL

NULLIF('apples', 'oranges') vrací 'apples'

NULLIF(NULL, 12) vrací chybové hlášení ORA-00932


Konstruktor dkov hodnoty

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

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_trpaslikaAND tezby.SKUTECNOST>0)

GROUP BY trpaslici.jmeno;


Create table as select

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 dodavateleAS (SELECT * FROM spolecnosti WHERE id > 1000);


Create table as select1

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

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

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í USINGse 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

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 JOINsachty


Truncate

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

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 dotazy1

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

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.nameFROM customersWHERE customers.customer_id = suppliers.supplier_id)

WHERE EXISTS(SELECT customers.nameFROM customersWHERE customers.customer_id = suppliers.supplier_id);

Kdykoli je supplier_id nalezen mezihodnotami 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

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í synonamaoffices 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 suppliersFOR app.suppliers;


Pseudo sloupec rownum

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 rownum1

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<=5ORDER 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 radekFROM

(SELECT * FROM trpaslici ORDER BY jmeno) dotaz)

WHERE radek BETWEEN 3 and 5;

Použití sloupce ROWNUM nahrazuje klauzuli LIMIT známou z MySQLpro určení rozsahu řádků na výstupu.


N hrada rownum

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

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


P kazy s klauzul with p klad

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


P kazy s klauzulemi with p klad

Příkazy s klauzulemi WITH - příklad

WITH

sum_salesAS

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


Syntaxe pr chodu strom v oracle 9i 10

Syntaxe průchodu stromů v Oracle 9i,10

SELECTsloupceFROMtabulka[WHEREpodmínka3]START WITH podmínka1CONNECT BYpodmí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


P klad hierarchick ho dotazu

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


Dopln n hierarchick ch dotaz

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


P klad hierarchick ch dotaz

Příklad hierarchických dotazů

  • SELECT lpad(' ',level*3)||PRIJMENI||' '||JMENO name,

  • SYS_CONNECT_BY_PATH(PRIJMENI, '/') path,

  • CONNECT_BY_ROOT PRIJMENItopmgr,

  • 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;

Architektury a techniky DS – cv. 1


Hierarchick dotazy dle ansi sql

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


Hierarchick dotazy dle ansi sql1

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-the-end-of-hierarchical-querying-as-we-know-it

Architektury a techniky DS – cv. 1


Hierarchick dotazy dle ansi sql2

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

joinorg_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


  • Login