Issuu

Stručný obsah Úvod

K1733.indd 3

ČÁST I Úvod do SQL 1. Seznámení s jazykem SQL 2. Začínáme s dotazy 3. Výrazy, podmínky a operátory 4. Klauzule v dotazech jazyka SQL 5. Spojování tabulek 6. Vkládání poddotazů do dotazů 7. Formování dat pomocí vestavěných funkcí

29 31 45 61 103 135 161 185

ČÁST II Návrh databáze 8. Normalizace databáze 9. Tvorba a údržba tabulek 10. Řízení integrity dat

229 231 241 263

ČÁST III Manipulace s daty 11. Manipulace s daty 12. Datum a čas v jazyku SQL 13. Tvorba pohledů 14. Řízení transakcí

279 281 303 321 341

ČÁST IV Administrace databáze 15. Tvorba indexů na tabulkách pro zlepšení výkonu 16. Racionalizace příkazů jazyka SQL pro zlepšení výkonu 17. Databázová bezpečnost 18. Datový slovník (systémový katalog)

355 357 373 393 413

18.1.2010 16:17:14

K1733.indd 4

Stručný obsah

ČÁST V Další SQL objekty 19. Dočasné tabulky, uložené procedury, spouštěče a kurzory 20. Nové objekty v současném standardu

439 441 459

ČÁST VI Pokročilé techniky SQL 21. Generování příkazů jazyka SQL pomocí jazyka SQL 22. Tvorba komplexních dotazů jazyka SQL 23. Ladění příkazů jazyka SQL 24. Vkládání kódu jazyka SQL při programování aplikací

473 475 497 515 535

ČÁST VII SQL v různých databázových implementacích 25. Použití nástroje SQL*Plus databázového systému Oracle pro generování zpráv 26. Úvod do jazyka PL/SQL databázového systému Oracle 27. Seznámení s jazykem Transact-SQL 28. Databázový systém MySQL na unixovém systému

547 585 613 635

ČÁST VIII Přílohy A. Odpovědi B. Ukázky kódu pro vytvoření tabulek C. Ukázky kódu pro naplnění tabulek D. Instalace databázového systému MySQL pro cvičení E. Přehled nejčastěji používaných příkazů jazyka SQL F. Přehled nejčastěji používaných funkcí jazyka SQL

647 649 677 689 703 705 711

545

18.1.2010 16:17:14

Obsah O autorech Věnování Poděkování Poznámka redakce českého vydání

Úvod

23 24 24 24

Komu je kniha určena Uspořádání knihy Použité konvence Praktická cvičení v databázovém systému MySQL Zdrojový kód

25 25 26 27 27

ČÁST I Úvod do SQL LEKCE 1 Seznámení s jazykem SQL Stručná historie jazyka SQL Stručná historie databází Současná podoba databází Jazyk pro více produktů Prvotní implementace Jazyk SQL a vývoj aplikací typu klient-server

Přehled jazyka SQL Populární implementace jazyka SQL MySQL Oracle Microsoft SQL Server a Sybase IBM DB2

ODBC Pozice kódu jazyka SQL ve vytvářené aplikaci Shrnutí Otázky a odpovědi Úkoly pro vás Kvíz Cvičení

K1733.indd 5

31 31 32 36 37 37 38

38 39 39 39 40 40

40 41 43 43 44 44 44

18.1.2010 16:17:14

Obsah

LEKCE 2 Začínáme s dotazy Pozadí jazyka SQL Osvojení základní syntaxe dotazů Stavební bloky pro získávání dat: SELECT a FROM Dotazy v praxi Píšeme první dotaz Ukončení příkazu jazyka SQL Vybírání jednotlivých sloupců Změna pořadí sloupců Vybírání jiných tabulek

Vybírání odlišných hodnot Shrnutí Otázky a odpovědi Úkoly pro vás Kvíz Cvičení

45 45 47 48 49 50 51 51 53

54 56 56 56 58 59

LEKCE 3 Výrazy, podmínky a operátory

Pracujeme s dotazovými výrazy Podmínky v dotazech Jak používat operátory

61 62 63

Aritmetické operátory Porovnávací operátory Znakové operátory Logické operátory Množinové operátory Ostatní operátory: IN a BETWEEN

Shrnutí Otázky a odpovědi Úkoly pro vás Kvíz Cvičení

LEKCE 4 Klauzule v dotazech jazyka SQL Specifikace kritérií pomocí klauzule WHERE Klauzule ORDER BY Klauzule GROUP BY Klauzule HAVING

K1733.indd 6

64 75 83 89 93 97

99 99 100 101 101

103 104 106 115 121

18.1.2010 16:17:14

Obsah

Kombinování klauzulí Příklad 4.1 Příklad 4.2 Příklad 4.3 Příklad 4.4

Shrnutí Otázky a odpovědi Úkoly pro vás Kvíz Cvičení

LEKCE 5 Spojování tabulek Spojování více tabulek v jediném příkazu SELECT Křížové spojování tabulek Hledání správného sloupce

Spojování tabulek na základě rovnosti Spojování tabulek na základě nerovnosti Vnější a vnitřní spojení Spojení tabulky se sebou Shrnutí Otázky a odpovědi Úkoly pro vás Kvíz Cvičení

LEKCE 6 Vkládání poddotazů do dotazů Sestavujeme poddotazy Agregační funkce v poddotazech Vnořování poddotazů Vnější reference s korelovanými poddotazy Klíčová slova EXISTS, ANY a ALL Shrnutí Otázky a odpovědi Úkoly pro vás Kvíz Cvičení

K1733.indd 7

127 127 128 128 130

132 132 132 133 133

135 135 136 141

142 149 151 155 157 157 158 159 160

161 163 168 170 173 176 181 181 182 182 183

18.1.2010 16:17:14

Obsah

LEKCE 7 Formování dat pomocí vestavěných funkcí Agregační funkce pro sumarizaci dat Funkce COUNT Funkce SUM Funkce AVG Funkce MAX Funkce MIN Funkce VARIANCE Funkce STDDEV

185 186 186 188 189 189 190 191

Funkce pro formátování data a času

192

Funkce ADD_MONTHS/DATE_ADD Funkce LAST_DAY Funkce MONTHS_BETWEEN Funkce NEXT_DAY Funkce SYSDATE

192 194 195 196 197

Funkce pro aritmetické operace Funkce ABS Funkce CEIL a FLOOR Funkce EXP Funkce LN a LOG Funkce MOD Funkce POWER Funkce SIGN Funkce SQRT

Funkce pro změnu vzhledu znakových hodnot Funkce CHR Funkce CONCAT Funkce INITCAP Funkce LOWER a UPPER Funkce LPAD a RPAD Funkce LTRIM a RTRIM Funkce REPLACE Funkce SUBSTR Funkce TRANSLATE Funkce INSTR Funkce LENGTH

Převodní funkce Funkce TO_CHAR Funkce TO_NUMBER

Ostatní funkce

K1733.indd 8

185

198 198 199 200 200 201 202 202 203

204 204 204 206 206 207 208 209 211 215 215 216

216 217 218

218

18.1.2010 16:17:14

Obsah

Funkce GREATEST a LEAST Funkce USER

218 219

Doplňující příklady znakových funkcí databázového systému MySQL Funkce LENGTH Funkce LOCATE Funkce INSTR Funkce LPAD Funkce RPAD Funkce LEFT Funkce RIGHT Funkce SUBSTRING Funkce LTRIM Funkce RTRIM Funkce TRIM

219 220 220 220 220 221 221 221 221 222 222 222

Doplňující příklady funkcí databázového systému MySQL pro práci s datem Funkce DATE_FORMAT Funkce TIME_FORMAT Funkce CURDATE Funkce CURTIME

222 223 224 224 224

Shrnutí Otázky a odpovědi Úkoly pro vás

224 225 225

Kvíz Cvičení

226 227

ČÁST II Návrh databáze LEKCE 8 Normalizace databáze Normalizace databáze Holá databáze Logický návrh databáze Potřeby koncového uživatele Redundance dat

Normální formy První normální forma Druhá normální forma Třetí normální forma

Normalizace v praxi Referenční integrita

Výhody normalizace

K1733.indd 9

231 231 231 231 232 232

233 233 234 235

236 236

237

18.1.2010 16:17:14

Obsah

Nevýhody normalizace Denormalizace databáze Shrnutí Otázky a odpovědi Úkoly pro vás Kvíz Cvičení

LEKCE 9 Tvorba a údržba tabulek Začínáme příkazem CREATE DATABASE

239 239

241 241

Možnosti příkazu CREATE DATABASE Návrh databáze Tvorba datového slovníku (systémového katalogu) Tvorba klíčových polí Rozbití dat

242 243 244 245 245

Definování tabulek pomocí příkazu CREATE TABLE

246

Název tabulky Název pole Datové typy pole Umístění a velikost tabulky Vytvoření tabulky ze stávající tabulky

Změna struktury tabulky pomocí příkazu ALTER TABLE Příkaz DROP TABLE Příkaz DROP DATABASE Práce s příkazy DROP TABLE a DROP DATABASE

Shrnutí Otázky a odpovědi Úkoly pro vás Kvíz Cvičení

LEKCE 10 Řízení integrity dat

247 247 247 252 253

255 258 259 259

259 259 260 260 261

263

Seznámení s omezeními

263

Integrita dat Proč používat omezení

263 264

Typy omezení Omezení NOT NULL Omezení ve formě primárního klíče Omezení ve formě jedinečnosti

K1733.indd 10

237 238 238 239 239

264 265 266 268

18.1.2010 16:17:14

Obsah

Omezení ve formě cizího klíče Omezení ve formě kontroly

Správa omezení

269 270

272

Správné pořadí omezení Různé přístupy ke tvorbě omezení Ukázková hlášení referenční integrity databázového systému Oracle

Shrnutí Otázky a odpovědi Úkoly pro vás

272 273 273

276 277 277

Kvíz Cvičení

278 278

ČÁST III Manipulace s daty LEKCE 11 Manipulace s daty Seznámení s příkazy pro manipulaci s daty Zadávání dat pomocí příkazu INSERT Zadávání jednoho záznamu pomocí příkazu INSERT...VALUES Vkládání hodnot NULL Vkládání jedinečných hodnot Zadávání většího počtu záznamů pomocí příkazu INSERT...SELECT

Modifikace stávajících dat pomoc příkazu UPDATE Odstraňování informací pomocí příkazu DELETE Importování a exportování dat z cizích zdrojů Microsoft Access Microsoft SQL Server Oracle MySQL

Shrnutí Otázky a odpovědi Úkoly pro vás Kvíz Cvičení

281 281 282 282 284 285 286

289 292 296 296 297 298 298

299 299 300 300 301

LEKCE 12 Datum a čas v jazyku SQL

303

Způsob uložení data a času

303

Datové typy standardu ANSI pro datum a čas Prvky datového typu DATETIME Implementace specifických datových typů

K1733.indd 11

303 304 304

18.1.2010 16:17:15

Obsah

Aplikace funkcí pro práci s časem v dotazech

305

Aktuální datum Časová pásma Přičítání času ke kalendářním datům Odečítání kalendářních dat Porovnávání datových a časových období Další funkce pro práci s datem

305 307 307 309 311 311

Převod mezi formáty kalendářních dat Datové obrazy Převod kalendářních dat na znakové řetězce Převod znakových řetězců na kalendářní data

Shrnutí Otázky a odpovědi Úkoly pro vás

313 315 316

317 317 317

Kvíz Cvičení

318 318

LEKCE 13 Tvorba pohledů

321

Seznámení s pohledy Používáme pohledy Jednoduchý pohled Přejmenování sloupců Zpracování pohledů Omezení klauzule SELECT Modifikace dat v pohledu Nejčastější využití pohledů Odstranění pohledu příkazem DROP VIEW

Shrnutí Otázky a odpovědi Úkoly pro vás

321 322 324 326 327 331 331 334 337

338 338 339

Kvíz Cvičení

339 339

LEKCE 14 Řízení transakcí

341

Správa transakcí Bankovní aplikace Zahájení transakce Dokončení transakce Zrušení transakce

K1733.indd 12

312

341 342 343 345 347

18.1.2010 16:17:15

Obsah

Záchytné body transakce Shrnutí Otázky a odpovědi Úkoly pro vás

350 352 353 353

Kvíz Cvičení

353 353

ČÁST IV Administrace databáze LEKCE 15 Tvorba indexů na tabulkách pro zlepšení výkonu Seznámení s indexy Rady pro práci s indexy Vytváření indexů na více než jednom poli

Klíčové slovo UNIQUE v příkazu CREATE INDEX Indexy a spojování tabulek Klastrované indexy Shrnutí Otázky a odpovědi Úkoly pro vás Kvíz Cvičení

LEKCE 16 Racionalizace příkazů jazyka SQL pro zlepšení výkonu Pište příkazy jazyka SQL čitelně Nepoužívejte skenování celé tabulky Přidání nového indexu Uspořádání prvků v dotazu Procedury Nepoužívejte operátor OR

OLAP a OLTP Dolaďování systému OLTP Dolaďování systému OLAP

Dávkové zátěže a transakční zpracování Optimalizace načítání dat zahozením indexů Příkaz COMMIT Přestavování tabulek a indexů v dynamickém prostředí Dolaďování databáze Identifikování výkonnostních překážek

K1733.indd 13

357 357 365 365

368 369 370 371 371 371 371 372

373 374 375 375 376 378 378

379 380 380

380 382 382 384 385 388

18.1.2010 16:17:15

Obsah

Použití vestavěných dolaďovacích nástrojů Shrnutí Otázky a odpovědi Úkoly pro vás Kvíz Cvičení

LEKCE 17 Databázová bezpečnost Role bezpečnosti při správě databáze Oblíbené databázové produkty a bezpečnost Bezpečnost v databázových systémech Oracle Express a MySQL Tvorba uživatelů Tvorba rolí Uživatelská oprávnění Použití pohledů pro účely zabezpečení Synonyma místo pohledů Řešení bezpečnostních problémů pomocí pohledů Klauzule WITH GRANT OPTION

Shrnutí Otázky a odpovědi Úkoly pro vás Kvíz Cvičení

LEKCE 18 Datový slovník (systémový katalog) Seznámení s datovým slovníkem Identifikování uživatelů datového slovníku Obsah datového slovníku Datový slovník databázového systému Oracle Datový slovník databázového systému MySQL

Pohled do datového slovníku databázového systému Oracle Pohledy pro uživatele Pohledy pro správce databáze Pohledy dynamického výkonu

390 391

393 393 394 395 395 397 399 406 407 408 409

410 410 411 411 411

413 413 414 414 415 415

415 416 423 431

Pohled do datového slovníku databázového systému MySQL

432

Příkazy pro zobrazení tabulek v databázovém systému MySQL Databáze INFORMATION_SCHEMA

433 433

Shrnutí Otázky a odpovědi

K1733.indd 14

389 389 390 390

435 436

18.1.2010 16:17:15

Obsah

Úkoly pro vás

436

Kvíz Cvičení

436 437

ČÁST V Další SQL objekty LEKCE 19 Dočasné tabulky, uložené procedury, spouštěče a kurzory Vytváříme dočasné tabulky Používáme kurzory Vytvoření kurzoru Otevření kurzoru Posouvání kurzoru Testování stavu kurzoru Uzavření kurzoru Rozsah platnosti kurzorů

Vytváříme a používáme uložené procedury Odstranění uložené procedury

441 445 446 446 446 447 448 448

449 450

Navrhujeme a používáme spouštěče Spouštěče a transakce

451 452

Omezení při používání spouštěčů Vnořené spouštěče

453 453

Používáme vložený kód jazyka SQL Statický a dynamický kód jazyka SQL

Shrnutí Otázky a odpovědi Úkoly pro vás Kvíz Cvičení

LEKCE 20 Nové objekty v současném standardu Příkaz CREATE ROLE Tvorba spouštěčů Příkaz CREATE TYPE Regulární výrazy Datový typ BLOB Krátký příklad kódu jazyka XML Shrnutí Otázky a odpovědi

K1733.indd 15

441

453 454

455 456 456 456 457

459 459 461 463 467 468 469 470 470

18.1.2010 16:17:15

Obsah

Úkoly pro vás

470

Kvíz Cvičení

471 471

ČÁST VI Pokročilé techniky SQL LEKCE 21 Generování příkazů jazyka SQL pomocí jazyka SQL Generování příkazů jazyka SQL Nové povely nástroje SQL*Plus Povel SET ECHO Povel SET FEEDBACK Povel SET HEADING Povel SPOOL Povel START Povel EDIT

Počítání řádků v tabulkách Udělení systémových práv více uživatelům Udělení práv na vlastní tabulky jinému uživateli Deaktivace omezení tabulky kvůli načtení dat Tvorba více synonym jednou ranou Tvorba pohledů na svých tabulkách Vyprázdnění všech tabulek v daném schématu Generování systémových skriptů pomocí jazyka SQL Praktická aplikace generování kódu jazyka SQL a dalších principů Shrnutí Otázky a odpovědi Úkoly pro vás Kvíz Cvičení

LEKCE 22 Tvorba komplexních dotazů jazyka SQL Příkazy CREATE TABLE Příklady složitých dotazů Výpočet věku z data narození Rozdělení části dne na hodiny, minuty a vteřiny Převod bajtů na kilobajty a megabajty Zpráva o fragmentaci databáze Poddotazy v jazyku DML

K1733.indd 16

475 475 476 477 477 477 477 478 478

478 482 484 486 487 490 491 492 493 494 494 495 495 496

497 497 500 500 501 503 504 504

18.1.2010 16:17:15

Obsah

Formátování kalendářních dat Poddotaz zahrnující maximální hodnotu Více poddotazů Formátování číselných hodnot pomocí lomítek a mezer Zvyšování číselných hodnot o zadaný podíl Zjištění další nejvyšší hodnoty ve sloupci Práce s hodnotami NULL

Tipy pro sestavování komplexních dotazů Shrnutí Otázky a odpovědi Úkoly pro vás Kvíz Cvičení

LEKCE 23 Ladění příkazů jazyka SQL Běžné chyby v příkazech jazyka SQL Neexistující tabulka či pohled Neplatné uživatelské jméno nebo heslo Chybí klíčové slovo FROM Nesprávně použitá seskupující funkce Neplatný název sloupce Chybějící klíčové slovo Chybějící levá závorka Chybějící pravá závorka Chybějící čárka Nejednoznačně definovaný sloupec Nesprávně ukončený příkaz jazyka SQL Chybějící výraz Nedostatek argumentů pro funkci Nedostatek hodnot Porušení integritního omezení – rodičovský klíč nenalezen Databáze Oracle není k dispozici Vkládaná hodnota je pro sloupec příliš velká TNS: Posluchač nemohl vyhodnotit identifikátor SID uvedený v deskriptoru připojení Nedostatečné právo pro udělování práv Přepínací znak v příkazu – neplatný znak Nelze vytvořit soubor operačního systému

K1733.indd 17

505 506 507 507 508 508 510

512 513 513 514 514 514

515 515 515 516 516 517 518 519 519 520 520 521 521 522 522 523 523 524 524 525 525 525 526

18.1.2010 16:17:15

Obsah

Běžné logické chyby Rezervovaná slova v příkazech jazyka SQL Příkaz DISTINCT při výběru více sloupců Zahození nekvalifikované tabulky Veřejná synonyma v databázi s více schématy Obávaný kartézský součin Neschopnost prosadit vstupní standardy Neschopnost prosadit konvence v oblasti struktury systému souborů Rozsáhlé tabulky a výchozí parametry úložiště Umisťování objektů do systémového prostoru tabulek Neschopnost zkomprimovat rozsáhlé soubory zálohy Neschopnost rozplánovat systémové prostředky

Jak se vyhnout problémům s daty Shrnutí Otázky a odpovědi Úkoly pro vás Kvíz Cvičení

LEKCE 24 Vkládání kódu jazyka SQL při programování aplikací Letmý pohled na několik nástrojů pro vývoj aplikací ODBC Oracle Express SQL v jazyku Java přes rozhraní JDBC SQL v prostředí .NET přes rozhraní OleDB Přípravy pro databázový systém Oracle

Tvorba databáze Jazyk SQL v prostředí Javy Jazyk SQL v prostředí .NET Shrnutí Otázky a odpovědi Úkoly pro vás Kvíz Cvičení

K1733.indd 18

526 526 527 527 528 528 529 529 529 530 531 531

531 531 532 532 532 533

535 535 535 536 536 536 536

537 540 542 543 543 544 544 544

18.1.2010 16:17:16

Obsah

ČÁST VII SQL v různých databázových implementacích LEKCE 25 Použití nástroje SQL*Plus databázového systému Oracle pro generování zpráv

547

Seznámení s nástrojem SQL*Plus Paměť nástroje SQL*Plus Zobrazení struktury tabulky pomocí příkazu DESCRIBE Zobrazení nastavení pomocí příkazu SHOW Souborové příkazy pro manipulaci se soubory

547 547 552 553 554

Příkazy SAVE, GET a EDIT Zahájení souboru Nasměrování výstupu dotazu

Přizpůsobení pracovního prostředí pomocí příkazů SET Vynulování nastavení příkazem CLEAR Formátování výstupu

558 561 561

TTITLE a BTITLE Formátování sloupců (COLUMN, HEADING, FORMAT)

561 562

Tvorba zprávy a skupinových souhrnů Příkaz BREAK ON Příkaz COMPUTE

564 564 565

Proměnné v nástroji SQL*Plus

567

Substituční proměnné (&) Příkaz DEFINE Příkaz ACCEPT Povel NEW_VALUE

568 568 569 571

Tabulka DUAL Funkce DECODE Převody kalendářních dat Spuštění série souborů s kódem jazyka SQL Komentáře ve skriptech jazyka SQL Tvorba pokročilých zpráv Shrnutí Otázky a odpovědi Úkoly pro vás Kvíz Cvičení

K1733.indd 19

554 555 556

572 573 575 578 579 580 581 582 582 582 582

18.1.2010 16:17:16

Obsah

LEKCE 26 Úvod do jazyka PL/SQL databázového systému Oracle Seznámení s jazykem PL/SQL Struktura bloku jazyka PL/SQL Oddíl DECLARE Oddíl PROCEDURE Oddíl EXCEPTION

Řízení transakcí v jazyku PL/SQL Praktické příklady Ukázkové tabulky a data Jednoduchý blok jazyka PL/SQL Rozvinutější příklad bloku jazyka PL/SQL

Používáme uložené procedury, balíčky a spouštěče Ukázková procedura Ukázkový balíček Ukázkový spouštěč

Shrnutí Otázky a odpovědi Úkoly pro vás Kvíz Cvičení

LEKCE 27 Seznámení s jazykem Transact-SQL Přehled jazyka Transact-SQL Rozšíření standardu ANSI SQL Kdo může používat jazyk Transact-SQL Základní prvky jazyka Transact-SQL

Datové typy

585 586 587 590 595

598 598 599 599 602

606 606 607 608

610 610 611 611 611

613 613 614 614 614

614

Znakové řetězce Číselné datové typy Datové typy pro práci s kalendářním datem Datové typy pro práci s finančními částkami Binární řetězce Logický datový typ bit

615 615 615 615 616 616

Přístup k databázi pomocí jazyka Transact-SQL

616

Databáze BASEBALL Tabulka BATTERS Tabulka PITCHERS Tabulka TEAMS Deklarace lokálních proměnných

K1733.indd 20

585

617 617 618 618 619

18.1.2010 16:17:16

Obsah

Deklarace globálních proměnných Praktické použití proměnných Příkaz PRINT

Řízení toku programu

619 621 622

623

Příkazy BEGIN a END Příkazy IF...ELSE Podmínka EXISTS Testování výsledku dotazu Cyklus WHILE Příkaz BREAK Příkaz CONTINUE Průchod tabulkou pomocí cyklu WHILE

623 623 625 626 626 627 627 628

Zástupné symboly v jazyku Transact-SQL Převody kalendářních dat Příkazy SET jakožto diagnostické nástroje Shrnutí Otázky a odpovědi Úkoly pro vás

629 630 631 631 631 632

Kvíz Cvičení

LEKCE 28 Databázový systém MySQL na unixovém systému Správa databázového systému MySQL Instalace databázového systému MySQL Spuštění a zastavení databázového systému MySQL Počáteční práva v databázového systému MySQL

Terminálový monitor databázového systému MySQL Připojení k databázi Volby příkazového řádku Zadávání příkazů monitoru databázového systému MySQL Historie příkazového řádku Dávkový režim Příkaz SHOW

Pomocné nástroje databázového systému MySQL Shrnutí Otázky a odpovědi Úkoly pro vás Kvíz Cvičení

K1733.indd 21

632 632

635 635 636 637 637

638 638 639 641 643 643 644

645 645 646 646 646 646

18.1.2010 16:17:16

Obsah

ČÁST VIII Přílohy PŘÍLOHA A Odpovědi

649

PŘÍLOHA B Ukázky kódu pro vytvoření tabulek

677

PŘÍLOHA C Ukázky kódu pro naplnění tabulek

689

PŘÍLOHA D Instalace databázového systému MySQL pro cvičení

703

Pokyny pro instalaci v systému Windows Pokyny pro instalaci v systému Linux

PŘÍLOHA E Přehled nejčastěji používaných příkazů jazyka SQL

705

PŘÍLOHA F Přehled nejčastěji používaných funkcí jazyka SQL

711

Řetězcové funkce Číselné funkce Agregační funkce Funkce pro práci s datem a časem

Rejstřík

K1733.indd 22

703 704

711 713 713 714

715

18.1.2010 16:17:16

O autorech Již více než 10 let se autoři věnují studiu, aplikaci a dokumentaci standardu jazyka SQL a jeho praktického použití na kritické databázové systémy v této knize. Ryan Stephens a Ron Plew jsou provozovateli, mluvčími a spoluzakladateli rychle se rozvíjející firmy Perpetual Technologies, Inc. (PTI), která se orientuje na management a poradenství v oblasti informačních technologií. Společnost PTI se specializuje na databázové technologie, především pak na databázové systémy Oracle a SQL Server provozované na platformách UNIX, Linux a Microsoft. Oba autoři začínali jako analytici dat a správci databáze a nyní vedou tým skvělých odborníků, kteří se starají o databáze klientů po celém světě. Vytvořili kurzy databází pro univerzitu Purdue v Indianapolis a pět let je vyučovali a napsali více než desítku knih o databázovém systému Oracle, jazyku SQL, návrhu databází a o zajištění vysoké dostupnosti kritických systémů. Arie D. Jones je hlavním konzultantem společnosti Microsoft pro firmu PTI. Vede tým společnosti PTI složený z expertů na plánování, návrh, vývoj, nasazení a správu databázových prostředí a aplikací s cílem dosáhnout pro každého z klientů co nejlepší kombinace nástrojů a služeb. Pravidelně přednáší na setkání odborníků a napsal několik knih a článků, v nichž se věnuje tématům souvisejícím s databázemi. Jeho nejnovější kniha vydaná nakladatelstvím Wrox Publishing nese název „SQL Functions Programmer’s Reference“ (Funkce jazyka SQL – příručka programátora).

K1733.indd 23

18.1.2010 16:17:16

Věnování Tato kniha je věnována mým rodičům, Thomasu a Karlyn Stephensovým, kteří mě vždy vedli k tomu, že pokud budu chtít, tak dosáhnu čehokoliv. Tato kniha je věnována také mému úžasnému synu Danielovi a mým nádherným dcerám Autumn a Alivii – nikdy se nespokojte s ničím menším než se svými sny. —Ryan Tato kniha je věnována mé rodině: mé ženě Lindě, mé matce Betty, mým dětem Leslie, Nancy, Angele a Wendy, mým vnukům Andymu, Ryanovi, Holly, Morgan, Schyler, Heather, Gavinovi, Regan, Caleigh a Cameron a mým zeťům Jasonovi a Dallasovi. Děkuji vám, že jste se mnou během tohoto rušného období měli trpělivost. Všechny vás mám rád. —Poppy Tuto knihu bych rád věnoval mé ženě Jackie za to, že mi během těch dlouhých hodin, které jsem věnoval práci na této knize, projevovala pochopení a podporu. —Arie

Poděkování Děkujeme všem lidem v našich životech, kteří byli během všech vydání této knihy nesmírně trpěliví – především našim ženám Tině a Lindě. Děkujeme Ariemu Jonesovi za jeho nedocenitelnou pomoc při práci na tomto vydání. Děkujeme také všem v redakci vydavatelství Sams za jejich tvrdou práci, aby toto vydání bylo ještě lepší než to předchozí. Bylo pro nás potěšení s každým z vás pracovat.

Poznámka redakce českého vydání Nakladatelství Computer Press, které pro vás tuto knihu přeložilo, stojí o zpětnou vazbu a bude na vaše podněty a dotazy reagovat. Můžete se obrátit na následující adresy: Computer Press redakce počítačové literatury Holandská 8 639 00 Brno nebo knihy@cpress.cz. Další informace a případné opravy českého vydání knihy najdete na internetové adrese http://knihy.cpress.cz/K1733. Prostřednictvím uvedené adresy můžete též naší redakci zaslat komentář nebo dotaz týkající se knihy. Na vaše reakce se srdečně těšíme.

K1733.indd 24

18.1.2010 16:17:16

Úvod V průběhu poslední dekády se prostor informačních technologií výrazným způsobem posunul ke světu zaměřenému na data. Společnosti začaly více než kdy předtím hledat způsoby pro využití své vlastní datové sítě k provádění rozumných obchodních rozhodnutí. To zahrnuje schopnost efektivně shromažďovat, uchovávat a vybírat údaje na potenciálně rozsáhlé množině dat v mnoha formátech. Proto nabyla role správců a vývojářů databáze v náležité implementaci a správa těchto systémů přímo strategický význam. Základním kamenem jakéhokoliv databázového projektu je jazyk, který se bude používat pro interakci s databázovým systémem. Naštěstí jisté sdružení ustanovilo standardní dotazovací jazyk pro databázová prostředí známý jako standard ANSI SQL. Dodržováním tohoto známého standardu se všechny databázové dotazovací jazyky setkávají ve společných rysech, což umožňuje vývojářům, aby se tento standard naučili a poté pracovali v libovolném počtu databázových systémů jen s drobnými změnami. V této knize se zaměříme především na to, aby čtenáři získali základní znalosti o jazyku SQL, díky čemuž budou mít pevný základ pro budoucí studium. V současném podnikovém prostředí je na osvojení nových věcí mnohdy velmi málo času, neboť většinu času zhltnou každodenní pracovní činnost. Kniha se soustředí na lekce menšího rozsahu a na logické členění částí ve stylu odrazového můstku, což čtenářům umožní učit se jazyk SQL jejich vlastním tempem a v rámci jejich vlastních časových možností.

Komu je kniha určena Kniha je určena všem, kteří se chtějí rychle naučit základy jazyka SQL (Structured Query Language – strukturovací dotazovací jazyk). Prostřednictvím bezpočtu příkladů jsou představeny všechny hlavní složky jazyka SQL společně s možnostmi, které jsou k dispozici v nejrůznějších databázových implementacích. Takto získané znalosti byste pak měli být schopni využít v relačních databázích tradičního podnikového prostředí.

Uspořádání knihy Kniha je rozdělena na sedm částí, které logicky rozčleňují strukturu jazyka ANSI SQL na snadno osvojitelné celky: Část I, tvořená prvními sedmi lekcemi, se věnuje základním koncepcím v pozadí jazyka SQL a zaměřuje se především na dotazy jazyka SQL. Část II je věnována tématu umění návrhu databáze, jako je správné vytváření databází a databázových objektů, což je často základem pro vývoj aplikace v prostředí relačního databázového systému. Část III se soustřeďuje na manipulaci s daty a na používání jazyka SQL pro aktualizaci (UPDATE), vkládání (INSERT) a mazání (DELETE) dat v databázi. Jedná se o základní příkazy, které budete používat při každodenní práci s databází.

K1733.indd 25

18.1.2010 16:17:16

Úvod Část IV je věnována správě databáze, což zahrnuje témata, jako je bezpečnost, řízení a výkon, která vám umožňují udržovat integritu a výkon své databáze. Část V se zaměřuje na pokročilejší objekty jazyka SQL, kam patří spouštěče a uložené procedury. Díky těmto objektům můžete sáhnout po důmyslnějších technikách pro manipulaci s daty, jejichž realizace by ve standardní syntaxi jazyka SQL byla velice obtížná. Část VI se zabývá pokročilejším programováním v jazyku SQL. Pomocí pokročilejšího programování v jazyku SQL můžete provádět složitější dotazy a manipulaci s daty v databázi. Část VII vám představí jazyk SQL v nejrůznějších databázových implementacích. Rozšíření jazyka SQL (např. PL/SQL) vám umožňují využít jedinečných rysů konkrétního databázového prostředí (např. databázový systém Oracle). V knize se nachází také šest příloh, v nichž kromě správných řešení cvičení každé lekce najdete také ukázky kódu pro vytvoření a naplnění tabulek používaných v celé knize. Po prostudování této knihy se budete skvěle orientovat v jazyku SQL a tyto znalosti budete schopni aplikovat v praxi. POZNÁMKA

Pokud již základy a historii jazyka SQL znáte, pak první lekci jen tak přeleťte očima a začněte naostro až od lekce 2.

Po vysvětlení syntaxe jazyka SQL si ji procvičíme prostřednictvím příkladů pro databázový systém MySQL, jehož implementace se nejvíce přibližuje standardu ANSI SQL, a také pro databázový systém Oracle, na němž si ukážeme některá rozšíření jazyka ANSI SQL.

Použité konvence Kniha používá pro snazší čitelnost a přehlednost textu následující typografické zásady: Názvy nabídek jsou od položek odděleny zvláštním znakem >. Například Soubor > Otevřít znamená zvolit položku Otevřít v nabídce Soubor. Nové pojmy jsou zvýrazněny. V některých výpisech je jak vstup, tak i výstup (Vstup/výstup ). V těchto případech je veškerý kód, který píšete (vstup), zvýrazněn tučným písmem, zatímco výstup zůstává ve standardním písmu se stejnou roztečí. Nadpisy Vstup a Výstup označují povahu uvedeného kódu. Řada termínů souvisejících s kódem jazyka SQL je v textu vysázena také písmem se stejnou roztečí. Zástupné symboly v kódu jsou uváděny skloněným písmem se stejnou roztečí. Odstavce nadepsané jako Analýza vysvětlují předcházející ukázku kódu. Nadpis Syntaxe uvádí syntaxi příkazu. Text knihy je dále doplněn speciálními prvky:

K1733.indd 26

18.1.2010 16:17:17

Úvod

POZNÁMKA

Poznámky vysvětlují zajímavé nebo důležité body, které mohou pomoci při porozumění technikám a koncepcím v pozadí jazyka SQL.

TIP

Tipy jsou malé útržky informací, které vám pomohou v praktických situacích. Tipy často nabízejí zkratky, díky nimž lze danou činnost provést snadněji nebo rychleji.

UPOZORNĚNÍ

Upozornění poskytují informace o problémech s negativním dopadem na výkon nebo o nebezpečných chybách. Varováním proto věnujte zvýšenou pozornost.

Praktická cvičení v databázovém systému MySQL V této edici jsme pro praktická cvičení zvolili databázový systém MySQL. V předchozích edicích jsme nechali na čtenáři, aby si zajistil přístup k libovolné implementaci jazyka SQL. Rozhodli jsme se, že by bylo lepší nabídnout databázi SQL s otevřeným zdrojovým kódem, která by všem čtenářům umožnila začít na stejné úrovni se stejným softwarem. Zvolili jsme databázový systém MySQL, protože jde v současnosti o nejoblíbenější databázi s otevřeným zdrojovým kódem, kterou lze snadno stáhnout a používat. Databázový systém MySQL má však i svá omezení. Existuje několik prvků standardního jazyka SQL, které vůbec nepodporuje. Proto jsme se snažili rozlišovat mezi cvičeními, která databázový systém MySQL podporují, a cvičeními, která jej nepodporují. Ve cvičeních, která MySQL nepodporují, se zaměříme především na edici Express databázového systému Oracle. Krása jazyka SQL spočívá v tom, že se jedná o standardní jazyk, i když každá implementace má své odlišnosti. Pokud si budete základy jazyka SQL procvičovat v databázovém systému MySQL, budete schopni osvojené znalosti snadno využít v libovolné implementaci jazyka SQL.

Zdrojový kód V přílohách najdete zdrojový kód pro vytvoření všech objektů používaných v této knize. To zahrnuje všechny používané tabulky a data. Kromě toho je zdrojový kód možné stáhnout z webové stránky knihy (http://knihy.cpress.cz/K1733). Záznamy si tak můžete jednoduše zkopírovat do svého rozhraní, takže nemusíte trávit většinu svého času psaním, a můžete se tak soustředit na probíranou látku.

K1733.indd 27

18.1.2010 16:17:17

161

LEKCE 6

Vkládání poddotazů do dotazů Poddotaz (subquery) je dotaz, jehož výsledky se předají jako argument jinému dotazu. Díky poddotazům můžete svázat několik dotazů dohromady. Na konci této lekce budete schopni provádět následující: sestavovat poddotazy, používat ve svých poddotazech klíčová slova EXISTS, ANY a ALL, sestavovat a používat korelované poddotazy. V této lekci budeme pracovat s tabulkami PART a ORDERS. K vytvoření a naplnění těchto tabulek proveďte prosím následující činnosti ve svém databázovém systému MySQL. Databázi kuba v následujícím příkladu nahraďte názvem vámi vytvořené databáze, do níž chcete tabulky umístit:

Vstup/výstup

Pro příklady v této kapitole budete potřebovat tabulky PART a ORDERS. Pokud je dosud nemáte, zde je kód pro jejich vytvoření a naplnění:

Vstup create table (partnum description price

K1733.indd 161

part numeric(10) not null, varchar(20) not null, decimal(10,2) not null);

18.1.2010 16:17:36

162

ČÁST I: Úvod do SQL create table orders (orderedon date, name varchar(16) partnum numeric(10) quantity numeric(10) remarks varchar(30) insert (‘54‘, insert (‘42‘, insert (‘46‘, insert (‘23‘, insert (‘76‘, insert (‘10‘,

not not not not

null, null, null, null);

into part values ‘Pedály‘, ‘542.50‘); into part values ‘Sedla‘, ‘245.00‘); into part values ‘Pneu‘, ‘152.50‘); into part values ‘Horské kolo‘, ‘3504.50‘); into part values ‘Silniční kolo‘, ‘5300.00‘); into part values ‘Dvojkolo‘, ‘12000.00‘);

insert into orders values (‘2006-03-15‘, ‘Mega Kola‘, ‘23‘, ‘6‘, ‘Zaplaceno‘); insert into orders values (‘2006-03-19‘, ‘Mega Kola‘, ‘76‘, ‘3‘, ‘Zaplaceno‘); insert into orders values (‘2006-09-02‘, ‘Mega Kola‘, ‘10‘, ‘1‘, ‘Zaplaceno‘); insert into orders values (‘2006-06-30‘, ‘Mega Kola‘, ‘42‘, ‘8‘, ‘Zaplaceno‘); insert into orders values (‘2006-06-30‘, ‘CykloSpec‘, ‘54‘, ‘10‘, ‘Zaplaceno‘); insert into orders values (‘2006-05-30‘, ‘CykloSpec‘, ‘23‘, ‘8‘, ‘Zaplaceno‘); insert into orders values (‘2006-01-17‘, ‘CykloSpec‘, ‘76‘, ‘11‘, ‘Zaplaceno‘); insert into orders values (‘2006-01-17‘, ‘LX Obchůdek‘, ‘76‘, ‘5‘, ‘Zaplaceno‘); insert into orders values (‘2006-06-01‘, ‘LX Obchůdek‘, ‘10‘, ‘3‘, ‘Zaplaceno‘); insert into orders values (‘2006-06-01‘, ‘Cyklo ABC‘, ‘10‘, ‘1‘, ‘Zaplaceno‘); insert into orders values (‘2006-07-01‘, ‘Cyklo ABC‘, ‘76‘, ‘4‘, ‘Zaplaceno‘); insert into orders values (‘2006-07-01‘, ‘Cyklo ABC‘, ‘46‘, ‘14‘, ‘Zaplaceno‘); insert into orders values (‘2006-07-11‘, ‘Cyklo Franta‘, ‘76‘, ‘14‘, ‘Zaplaceno‘);

POZNÁMKA

K1733.indd 162

Příklady v této lekci jsou pro databázový systém MySQL. Ujistěte se, že používáte verzi databázového systému MySQL 4.1 nebo vyšší, protože nižší verze nepodporují poddotazy.

18.1.2010 16:17:36

LEKCE 6: Vkládání poddotazů do dotazů

163

Sestavujeme poddotazy Jednoduše řečeno, poddotazy vám umožňují svázat výslednou sadu jednoho dotazu s jiným. Obecná syntaxe vypadá takto:

Syntaxe SELECT * FROM tabulka1 WHERE tabulka1.nejaky_sloupec = (SELECT jiny_sloupec FROM tabulka2 WHERE jiny_sloupec = nejaka_hodnota)

Všimněte si, jak je druhý dotaz vnořen do prvního. Podívejme se na aktuální obsah tabulek, které budeme používat při konstrukci příkladů z reálného světa:

Vstup/výstup mysql> select * from part; +---------+---------------+----------+ | partnum | description | price | +---------+---------------+----------+ | 54 | Pedály | 542.50 | | 42 | Sedla | 245.00 | | 46 | Pneu | 152.50 | | 23 | Horské kolo | 3504.50 | | 76 | Silniční kolo | 5300.00 | | 10 | Dvojkolo | 12000.00 | +---------+---------------+----------+ 6 rows in set (0.04 sec)

mysql> select * from orders; +------------+--------------+---------+----------+-----------+ | orderedon | name | partnum | quantity | remarks | +------------+--------------+---------+----------+-----------+ | 2006-03-15 | Mega Kola | 23 | 6 | Zaplaceno | | 2006-03-19 | Mega Kola | 76 | 3 | Zaplaceno | | 2006-09-02 | Mega Kola | 10 | 1 | Zaplaceno | | 2006-06-30 | Mega Kola | 42 | 8 | Zaplaceno | | 2006-06-30 | CykloSpec | 54 | 10 | Zaplaceno | | 2006-05-30 | CykloSpec | 23 | 8 | Zaplaceno | | 2006-01-17 | CykloSpec | 76 | 11 | Zaplaceno | | 2006-01-17 | LX Obchůdek | 76 | 5 | Zaplaceno | | 2006-06-01 | LX Obchůdek | 10 | 3 | Zaplaceno | | 2006-06-01 | Cyklo ABC | 10 | 1 | Zaplaceno | | 2006-07-01 | Cyklo ABC | 76 | 4 | Zaplaceno | | 2006-07-01 | Cyklo ABC | 46 | 14 | Zaplaceno | | 2006-07-11 | Cyklo Franta | 76 | 14 | Zaplaceno | +------------+--------------+---------+----------+-----------+ 13 rows in set (0.01 sec)

K1733.indd 163

18.1.2010 16:17:36

164

ČÁST I: Úvod do SQL Tabulky sdílejí společné pole s názvem PARTNUM. Předpokládejme, že neznáme (nebo nechceme vědět) hodnotu pole PARTNUM, ale místo toho chceme pracovat s popisem položky. S využitím poddotazu můžeme napsat následující příkaz:

Vstup/výstup mysql> select * -> from orders where partnum = -> (select partnum -> from part -> where description like ‘Silniční%‘); +------------+--------------+---------+----------+-----------+ | orderedon | name | partnum | quantity | remarks | +------------+--------------+---------+----------+-----------+ | 2006-03-19 | Mega Kola | 76 | 3 | Zaplaceno | | 2006-01-17 | CykloSpec | 76 | 11 | Zaplaceno | | 2006-01-17 | LX Obchůdek | 76 | 5 | Zaplaceno | | 2006-07-01 | Cyklo ABC | 76 | 4 | Zaplaceno | | 2006-07-11 | Cyklo Franta | 76 | 14 | Zaplaceno | +------------+--------------+---------+----------+-----------+ 5 rows in set (0.00 sec)

Podívejme se nyní podrobně na princip poddotazů. K tomu nám poslouží výše uvedený dotaz, který si rozložíme na jednotlivé části:

Vstup/výstup mysql> select partnum -> from part -> where description like ‘Silniční%‘; +---------+ | partnum | +---------+ | 76 | +---------+ 1 row in set (0.04 sec)

Analýza V příkladu pro databázový systém MySQL můžete vidět rozklad poddotazu. Vzhledem k tomu, že poddotaz je vždy uzavřen do závorek, vyhodnotí se jako první. Výsledná sada (76) je poté porovnána (testována na rovnost) se sloupcem PARTNUM tabulky ORDERS. Níže je uveden příklad výsledné sady z vnějšího dotazu:

K1733.indd 164

18.1.2010 16:17:36

LEKCE 6: Vkládání poddotazů do dotazů

165

| 2006-01-17 | CykloSpec | 76 | 11 | Zaplaceno | | 2006-01-17 | LX Obchůdek | 76 | 5 | Zaplaceno | | 2006-07-01 | Cyklo ABC | 76 | 4 | Zaplaceno | | 2006-07-11 | Cyklo Franta | 76 | 14 | Zaplaceno | +------------+--------------+---------+----------+-----------+ 5 rows in set (0.00 sec)

Zde jsme již schopni do podmínky v naší klauzuli WHERE dosadit konkrétní hodnotu, kterou jsme získali pomocí poddotazu. Když jsme začínali, tak jsme věděli jen to, že potřebujeme všechny řádky z tabulky ORDERS, které obsahují položku, jejíž popis začíná slovem „Silniční“. Díky poddotazu máme možnost získat data z obou tabulek, aniž bychom je museli jakkoli spojovat. Zde je příklad, v němž pro dosažení téhož výsledku používáme spojení tabulek:

Vstup/výstup mysql> select o.orderedon, -> o.name, -> o.partnum, -> o.quantity, -> o.remarks -> from orders o, part p -> where o.partnum = p.partnum -> and p.description like ‘Silniční%‘; +------------+--------------+---------+----------+-----------+ | orderedon | name | partnum | quantity | remarks | +------------+--------------+---------+----------+-----------+ | 2006-03-19 | Mega Kola | 76 | 3 | Zaplaceno | | 2006-01-17 | CykloSpec | 76 | 11 | Zaplaceno | | 2006-01-17 | LX Obchůdek | 76 | 5 | Zaplaceno | | 2006-07-01 | Cyklo ABC | 76 | 4 | Zaplaceno | | 2006-07-11 | Cyklo Franta | 76 | 14 | Zaplaceno | +------------+--------------+---------+----------+-----------+ 5 rows in set (0.02 sec)

Ba co víc, pokud použijete principy, které jste se naučili v lekci 5, pak můžete sloupec PARTNUM ve výsledku rozšířit o sloupec DESCRIPTION, což přispěje k lepší čitelnosti výsledku:

Vstup/výstup mysql> -> -> -> -> -> -> -> ->

K1733.indd 165

select o.orderedon, o.partnum, p.description,o.quantity,o.remarks, from orders o, part p where o.partnum=p.partnum and o.partnum = (select partnum from part where description like ‘Silniční%‘);

18.1.2010 16:17:36

166

ČÁST I: Úvod do SQL +------------+----------+---------------+----------+-----------+ | orderedon | partnum | description | quantity | remarks | +------------+----------+---------------+----------+-----------+ | 2006-03-19 | 76 | Silniční kolo | 3 | Zaplaceno | | 2006-01-17 | 76 | Silniční kolo | 11 | Zaplaceno | | 2006-01-17 | 76 | Silniční kolo | 5 | Zaplaceno | | 2006-07-01 | 76 | Silniční kolo | 4 | Zaplaceno | | 2006-07-11 | 76 | Silniční kolo | 14 | Zaplaceno | +------------+----------+---------------+----------+-----------+ 5 rows in set (0.02 sec)

První část dotazu je již více než známá: SELECT O.ORDEREDON, O.PARTNUM, P.DESCRIPTION, O.QUANTITY, O.REMARKS FROM ORDERS O, PART P

Zde pomocí aliasů O a P pro tabulky ORDERS a PART vybíráme pět sloupců, které nás zajímají. V tomto případě aliasy sloupců nepotřebujeme, protože každý z požadovaných sloupců má jedinečný název. Na druhou stranu jsme tak vytvořili poměrně dobře čitelný dotaz, což by později mohlo být mnohem obtížnější. První klauzule WHERE vypadá takto: WHERE O.PARTNUM = P.PARTNUM

Jedná se o standardní tvar pro spojování tabulek PART a ORDERS uvedených v klauzuli FROM. Pokud bychom klauzuli WHERE nepoužili, obdrželi bychom všechny možné kombinace řádků těchto dvou tabulek. Další část obsahuje poddotaz. AND O.PARTNUM = (SELECT PARTNUM FROM PART WHERE DESCRIPTION LIKE „Silniční%“)

Tímto příkazem přidáváme upřesnění, které říká, že se pole O.PARTNUM musí rovnat výsledku našeho jednoduchého poddotazu. V něm hledáme všechna čísla položek, jejichž popis začíná slovem „Silniční“. Operátor LIKE nám šetří úhozy na klávesnici, protože díky němu nemusíme psát „Silniční kolo“. Jenže za okamžik se ukáže, že jsme tentokrát nezvolili příliš šťastně. Představte si, že by někdo v oddělení součástek přidal novou součástku s názvem „Silniční brzdy“. Syntaxe pro přidání řádku se součástkou „Silniční brzdy“ do tabulky PART vypadá takto:

Vstup/výstup mysql> insert into part values -> (77,‘Silniční brzdy‘,79.90); Query OK, 1 row affected (0.00 sec)

Nová verze tabulky PART nyní vypadá následovně:

Vstup/výstup mysql> select * from part;

K1733.indd 166

18.1.2010 16:17:36

LEKCE 6: Vkládání poddotazů do dotazů

167

+---------+----------------+----------+ | partnum | description | price | +---------+----------------+----------+ | 54 | Pedály | 542.50 | | 42 | Sedla | 245.00 | | 46 | Pneu | 152.50 | | 23 | Horské kolo | 3504.50 | | 76 | Silniční kolo | 5300.00 | | 10 | Dvojkolo | 12000.00 | | 77 | Silniční brzdy | 79.90 | +---------+----------------+----------+ 7 rows in set (0.00 sec)

Předpokládejme, že o této změně vůbec nevíme, a zkusme nyní spustit náš dotaz:

Vstup mysql> -> -> -> -> -> -> -> ->

select o.orderedon, o.partnum, p.description, o.quantity, o.remarks from orders o, part p where o.partnum = p.partnum and o.partnum = (select partnum from part where description like ‘Silniční%‘);

Pokud jej zadáme, místo výsledků obdržíme následující chybové hlášení: ERROR 1242 (21000): Subquery returns more than 1 row

Odpověď vámi používaného interpretu jazyka SQL se může malinko lišit. Podstatné ale je, že nevrátí žádné výsledky. Vžijme se nyní do role interpretu jazyka SQL a pojďme zjistit, co se vlastně stalo. Nejdříve vyhodnotí poddotaz, takže vrátí následující výsledek:

Vstup/výstup mysql> select partnum -> from part -> where description like ‘Silniční%‘; +---------+ | partnum | +---------+ | 76 | | 77 | +---------+ 2 rows in set (0.00 sec)

Tento výsledek nyní vezmeme a aplikujeme na výraz O.PARTNUM působí určitý problém.

K1733.indd 167

což je zřejmě krok, který

18.1.2010 16:17:36

168

ČÁST I: Úvod do SQL

Analýza Jak se může pole PARTNUM rovnat hodnotě 76 i 77? Něco takového měl na mysli interpret jazyka SQL, když vracel chybu. Při každém použití klauzule LIKE se otevíráme tomuto typu chyby. Jakmile kombinujeme výsledky relačního operátoru s jiným relačním operátorem (např. =, < >), pak musíme dbát na to, aby byl výsledek singulární. Náš příklad tedy můžeme opravit tak, že v dotazu nahradíme operátor LIKE operátorem =:

Vstup/výstup mysql> select o.orderedon, o.partnum, -> p.description, o.quantity, o.remarks -> from orders o, part p -> where o.partnum=p.partnum -> and -> o.partnum= -> (select partnum -> from part -> where description = ‘Silniční kolo‘); +------------+---------+---------------+----------+-----------+ | orderedon | partnum | description | quantity | remarks | +------------+---------+---------------+----------+-----------+ | 2006-03-19 | 76 | Silniční kolo | 3 | Zaplaceno | | 2006-01-17 | 76 | Silniční kolo | 11 | Zaplaceno | | 2006-01-17 | 76 | Silniční kolo | 5 | Zaplaceno | | 2006-07-01 | 76 | Silniční kolo | 4 | Zaplaceno | | 2006-07-11 | 76 | Silniční kolo | 14 | Zaplaceno | +------------+---------+---------------+----------+-----------+ 5 rows in set (0.02 sec)

Tento poddotaz vrátí pouze jediný výsledek, takže v podmínce = bude jen jediná hodnota. Jak si můžeme být jisti, že poddotaz nevrátí více hodnot, když hledáme jen jedinou hodnotu? Ze všeho nejlepší je nepoužívat operátor LIKE. Další možnost spočívá v zajištění jedinečnosti vyhledávacího pole při návrhu tabulky. Jste-li nedůvěřiví, pak můžete pomocí metody (popsané v předchozí lekci) pro spojení tabulky se sebou ověřit jedinečnost daného pole. Pokud si navrhujete tabulky sami (viz lekce 9) nebo důvěřujete osobě, která je navrhuje, pak můžete vyžadovat, aby měl sloupec, podle něhož vyhledáváte, jedinečné hodnoty. Kromě toho můžete použít jistou část jazyka SQL, která vrací pouze jedinou odpověď: agregační funkci.

Agregační funkce v poddotazech Všechny agregační funkce – SUM, COUNT, MIN, MAX a AVG – vracejí jedinou hodnotu. K nalezení průměrné hodnoty objednávky můžete použít následující příkaz:

Vstup/výstup mysql> -> -> ->

K1733.indd 168

select avg(o.quantity * p.price) from orders o, part p where o.partnum = p.partnum ;

18.1.2010 16:17:37

LEKCE 6: Vkládání poddotazů do dotazů

169

+---------------------------+ | avg(o.quantity * p.price) | +---------------------------+ | 24206.384615 | +---------------------------+ 1 row in set (0.00 sec)

Tento příkaz vrací pouze jedinou hodnotu. Ke zjištění, které objednávky mají nadprůměrnou hodnotu, lze v poddotaze použít výše uvedený příkaz SELECT. Celý dotaz i s výsledkem vypadá takto:

Vstup/výstup mysql> select o.name, o.orderedon, -> o.quantity * p.price total -> from orders o, part p -> where o.partnum = p.partnum -> and -> o.quantity * p.price > -> (select avg(o.quantity * p.price) -> from orders o, part p -> where o.partnum = p.partnum); +---------------+------------+----------+ | name | orderon | total | +---------------+------------+----------+ | CykloSpec | 2006-05-30 | 28036.00 | | CykloSpec | 2006-01-17 | 58300.00 | | LX Obchůdek | 2006-01-17 | 26500.00 | | LX Obchůdek | 2006-06-01 | 36000.00 | | Cyklo Franta | 2006-07-11 | 74200.00 | +---------------+------------+----------+ 5 rows in set (0.02 sec)

Tento příklad obsahuje poněkud všední klauzule SELECT/FROM/WHERE: SELECT O.NAME, O.ORDEREDON, O.QUANTITY * P.PRICE TOTAL FROM ORDERS O, PART P WHERE O.PARTNUM = P.PARTNUM

Tyto řádky představují běžný způsob spojování těchto dvou tabulek. Toto spojení je nezbytné, protože cena je v tabulce PART a množství v tabulce ORDERS. Klauzule WHERE zajišťuje, aby se spojily pouze související řádky. Dále jsme přidali následující poddotaz: AND O.QUANTITY * P.PRICE > (SELECT AVG(O.QUANTITY * P.PRICE) FROM ORDERS O, PART P WHERE O.PARTNUM = P.PARTNUM)

Výše uvedená podmínka porovnává celkovou cenu každé objednávky s průměrem vypočítaným v poddotaze. Všimněte si, že spojení v poddotaze je nutné ze stejného důvodu jako v hlavním příkazu SELECT. Toto spojení má navíc úplně stejný tvar.

K1733.indd 169

18.1.2010 16:17:37

170

ČÁST I: Úvod do SQL V poddotazech nejsou ukryty žádné tajnosti. Mají úplně stejnou syntaxi jako samostatné dotazy. Ve skutečnosti začíná většina poddotazů jako samostatné dotazy, které se po otestování výsledků začleňují jako poddotazy.

Vnořování poddotazů Vnoření znamená vsazení poddotazu do jiného poddotazu.

Syntaxe SELECT * FROM neco WHERE (poddotaz1(poddotaz2(poddotaz3)));

Poddotazy lze vnořovat tak hluboko, jak jen vám dovoluje vámi používaná implementace jazyka SQL. Například k odeslání speciálních oznámení zákazníkům, kteří utratili více než průměrnou částku, lze využít data v tabulce CUSTOMER:

Vstup/výstup mysql> select * -> from customer; +--------------+-----------------+-----------+-------+-----------+---------+ | name | address | town | zip | phone | remarks | +--------------+-----------------+-----------+-------+-----------+---------+ | Mega Kola | Hačice 253 | Hačice | 58702 | 581123456 | Nic | | CykloSpec | Dolní 86 | Brno | 45678 | 771654321 | Nic | | LX Obchůdek | Smetanova 15 | Brno | 54678 | 771333222 | Nic | | Cyklo ABC | Jarní 6 | Prostějov | 56784 | 771111000 | Honza | | Cyklo Franta | Prostějovská 10 | Bedihoš | 34567 | 771789456 | Nic | +--------------+-----------------+-----------+-------+-----------+---------+ 5 rows in set (0.43 sec)

Tyto informace nyní zkombinujeme s malinko upravenou verzí dotazu, který jsme použili k vyhledání objednávek s nadprůměrnou částkou:

Vstup/výstup mysql> select all c.name, c.address, c.town, c.zip -> from customer c -> where c.name in -> (select o.name -> from orders o, part p -> where o.partnum = p.partnum -> and -> o.quantity * p.price > -> (select avg(o.quantity * p.price) -> from orders o, part p -> where o.partnum = p.partnum)); +--------------+-----------------+----------+-------+ | name | address | town | zip | +--------------+-----------------+----------+-------+ | CykloSpec | Dolní 86 | Brno | 45678 |

K1733.indd 170

18.1.2010 16:17:37

LEKCE 6: Vkládání poddotazů do dotazů

171

Zde je to, oč v tomto dotazu žádáme. V nejvnitřnějších závorkách se nachází známý příkaz: SELECT AVG(O.QUANTINTY * P.PRICE) FROM ORDERS O, PART P WHERE O.PARTNUM = P.PARTNUM

Výsledek tohoto dotazu vstupuje do lehce upravené verze již dříve použité klauzule SELECT: SELECT O.NAME FROM ORDERS O, PART P WHERE O.PARTNUM = P.PARTNUM AND O.QUANTINTY * P.PRICE > (...)

Všimněte si, že klauzule SELECT byla upravena tak, aby vracela jediný sloupec NAME, který je ne náhodou společný s tabulkou CUSTOMER. Spuštěním tohoto samotného dotazu obdržíme následující výsledek:

Před chvílí jsme strávili nějaký čas diskuzí nad tím, proč by vaše poddotazy měly vracet jen jedinou hodnotu. Důvod, proč byl tento dotaz schopen vrátit více než jednu hodnotu, bude za okamžik zcela zjevný. Výše uvedené výsledky nakonec vstupují do příkazu: SELECT C.NAME, C.ADDRESS, C.TOWN, C.ZIP FROM CUSTOMER C WHERE C.NAME IN (...)

K1733.indd 171

18.1.2010 16:17:37

172

ČÁST I: Úvod do SQL První dva řádky nejsou ničím zajímavé. Na třetím řádku se znovu setkáváme s klíčovým slovem IN, s nímž jsme naposledy pracovali v lekci 2. Klíčové slovo IN umožňuje používat víceřádkový výstup poddotazu. Jak si jistě pamatujete, hledá klíčové slovo IN shody v sadě hodnot uzavřené do závorek. V tomto případě obdržíme následující hodnoty: CykloSpec CykloSpec LX Obchůdek LX Obchůdek Cyklo Franta

Klíčové slovo IN se v poddotazech používá velice často. K porovnávání používá sadu hodnot, a proto nezpůsobí v interpretu jazyka SQL chybu. Poddotazy lze používat také s klauzulemi GROUP BY a HAVING. Podívejte se na následující dotaz:

Prozkoumejme nyní tento dotaz tak, jak to provádí interpret jazyka SQL. Nejdříve se tedy podíváme na poddotaz:

Vstup/výstup mysql> select avg(quantity) -> from orders; +---------------+ | avg(quantity) | +---------------+ | 6.7692 | +---------------+ 1 row in set (0.00 sec)

K1733.indd 172

18.1.2010 16:17:37

LEKCE 6: Vkládání poddotazů do dotazů

173

Hlavní část dotazu vypadá sama o sobě takto:

Při zkombinování s klauzulí hodnotu v poli QUANTITY.

HAVING

vytvoří poddotaz dva řádky, které mají nadprůměrnou

Vstup/výstup HAVING AVG(QUANTITY) > (SELECT AVG(QUANTITY) FROM ORDERS) NAME -----------CykloSpec Cyklo Franta

AVG ------9.6667 14.0000

Vnější reference s korelovanými poddotazy Poddotazy, které jsme dosud napsali, jsou soběstačné. V žádném z nich nepoužíváme referenci z vnějšku poddotazu. Korelované poddotazy umožňují používat vnější referenci se zvláštními a současně zajímavými výsledky. Podívejte se na následující dotaz:

Vstup/výstup mysql> select * -> from orders o -> where ‘Silniční kolo’ = -> (select description -> from part p -> where p.partnum = o.partnum); +------------+--------------+---------+----------+-----------+ | orderedon | name | partnum | quantity | remarks | +------------+--------------+---------+----------+-----------+ | 2006-03-19 | Mega Kola | 76 | 3 | Zaplaceno | | 2006-01-17 | CykloSpec | 76 | 11 | Zaplaceno |

K1733.indd 173

18.1.2010 16:17:37

174

ČÁST I: Úvod do SQL | 2006-01-17 | LX Obchůdek | 76 | 5 | Zaplaceno | | 2006-07-01 | Cyklo ABC | 76 | 4 | Zaplaceno | | 2006-07-11 | Cyklo Franta | 76 | 14 | Zaplaceno | +------------+--------------+---------+----------+-----------+ 5 rows in set (0.01 sec)

Tento dotaz se ve skutečnosti podobá následujícímu spojení:

Vstup/výstup mysql> select o.orderedon, o.name, -> o.partnum, o.quantity, o.remarks -> from orders o, part p -> where p.partnum = o.partnum -> and p.description = ‘Silniční kolo’; +------------+--------------+---------+----------+-----------+ | orderedon | name | partnum | quantity | remarks | +------------+--------------+---------+----------+-----------+ | 2006-03-19 | Mega Kola | 76 | 3 | Zaplaceno | | 2006-01-17 | CykloSpec | 76 | 11 | Zaplaceno | | 2006-01-17 | LX Obchůdek | 76 | 5 | Zaplaceno | | 2006-07-01 | Cyklo ABC | 76 | 4 | Zaplaceno | | 2006-07-11 | Cyklo Franta | 76 | 14 | Zaplaceno | +------------+--------------+---------+----------+-----------+ 5 rows in set (0.00 sec)

Analýza Výsledky jsou naprosto stejné. Korelovaný poddotaz funguje podobně jako spojení. Korelace je ustavena použitím elementu z dotazu v poddotazu. V tomto příkladu jsme korelaci ustavili příkazem: WHERE P.PARTNUM = O.PARTNUM

Zde porovnáváme pole P.PARTNUM z tabulky uvnitř poddotazu a pole O.PARTNUM z tabulky vně dotazu. Vzhledem k tomu, že O.PARTNUM může mít na každém řádku odlišnou hodnotu, provede se korelovaný poddotaz pro každý řádek dotazu. V následujícím příkladu se každý řádek tabulky ORDERS:

Vstup/výstup mysql> select * -> from orders; +------------+--------------+---------+----------+-----------+ | orderedon | name | partnum | quantity | remarks | +------------+--------------+---------+----------+-----------+ | 2006-03-15 | Mega Kola | 23 | 6 | Zaplaceno | | 2006-03-19 | Mega Kola | 76 | 3 | Zaplaceno | | 2006-09-02 | Mega Kola | 10 | 1 | Zaplaceno | | 2006-06-30 | Mega Kola | 42 | 8 | Zaplaceno | | 2006-06-30 | CykloSpec | 54 | 10 | Zaplaceno | | 2006-05-30 | CykloSpec | 23 | 8 | Zaplaceno |

K1733.indd 174

18.1.2010 16:17:38

LEKCE 6: Vkládání poddotazů do dotazů

175

| 2006-01-17 | CykloSpec | 76 | 11 | Zaplaceno | | 2006-01-17 | LX Obchůdek | 76 | 5 | Zaplaceno | | 2006-06-01 | LX Obchůdek | 10 | 3 | Zaplaceno | | 2006-06-01 | Cyklo ABC | 10 | 1 | Zaplaceno | | 2006-07-01 | Cyklo ABC | 76 | 4 | Zaplaceno | | 2006-07-01 | Cyklo ABC | 46 | 14 | Zaplaceno | | 2006-07-11 | Cyklo Franta | 76 | 14 | Zaplaceno | +------------+--------------+---------+----------+-----------+ 13 rows in set (0.00 sec)

zpracuje podle kritéria poddotazu: SELECT DESCRIPTION FROM PART P WHERE P.PARTNUM = O.PARTNUM

Tato operace vrátí popis (pole DESCRIPTION) každého řádku v tabulce PART, pro který platí P.PARTNUM = O.PARTNUM. Tyto popisy pak porovnáme pomocí klauzule WHERE: WHERE ‘Silniční kolo‘ =

Analýza Prozkoumává se každý řádek, a proto může mít poddotaz v korelovaném poddotazu více než jednu hodnotu. Nepokoušejte se ovšem vracet více sloupců nebo sloupce, které v kontextu klauzule WHERE nedávají smysl. Vrácené hodnoty totiž musí odpovídat operaci uvedené v klauzuli WHERE. Pokud bychom kupříkladu v právě provedeném dotazu vraceli pole PRICE a porovnávali jej s textem „Silniční kolo“, pak bychom obdrželi následující výsledek:

Vstup/výstup SQL> 2 3 4 5 6

SELECT * FROM ORDERS O WHERE ‘Silniční kolo‘ = (SELECT PRICE FROM PART P WHERE P.PARTNUM = O.PARTNUM);

conversion error from string „Silniční kolo“

Zde je další ukázka toho, co byste neměli dělat: SELECT * FROM ORDERS O WHERE ‘Silniční kolo‘ = (SELECT * FROM PART P WHERE P.PARTNUM = O.PARTNUM)

Tento příkaz SELECT způsobí zásadní chybu. Interpret jazyka SQL prostě nedokáže korelovat všechny sloupce v tabulce PART s operátorem =.

K1733.indd 175

18.1.2010 16:17:38

176

ČÁST I: Úvod do SQL Korelované poddotazy lze používat také v klauzulích GROUP BY a HAVING. V následujícím dotazu používáme korelovaný poddotaz ke zjištění průměrné hodnoty objednávky pro konkrétní součástku a tento průměr pak použijeme k odfiltrování celkových hodnot objednávek seskupených podle sloupce PARTNUM:

Vstup/výstup mysql> select o.partnum, sum(o.quantity*p.price), count(p.partnum) -> from orders o, part p -> where p.partnum = o.partnum -> group by o.partnum -> having sum(o.quantity*p.price) > -> (select avg(o1.quantity*p1.price) -> from part p1, orders o1 -> where p1.partnum = o1.partnum -> and p1.partnum = o.partnum); +---------+-------------------------+------------------+ | partnum | sum(o.quantity*p.price) | count(p.partnum) | +---------+-------------------------+------------------+ | 10 | 60000.00 | 3 | | 23 | 49063.00 | 2 | | 76 | 196100.00 | 5 | +---------+-------------------------+------------------+ 3 rows in set (0.01 sec)

Analýza Poddotaz nepočítá jen jeden průměr pomocí funkce AVG(O1.QUANTITY*P1.PRICE). Kvůli korelaci mezi dotazem a poddotazem (AND P1.PARTNUM = O.PARTNUM) je tento průměr počítán pro každou skupinu součástek a poté porovnán: HAVING SUM(O.QUANTITY*P.PRICE) >

TIP

Při použití korelovaných poddotazů s klauzulemi GROUP BY a HAVING se sloupce v klauzuli HAVING musejí nacházet buď v klauzuli SELECT, nebo v klauzuli GROUP BY. V opačném případě obdržíte u řádků s neplatným sloupcem chybovou zprávu, protože poddotaz se vyhodnocuje pro každou skupinu, a ne pro každý řádek. Nemůžete přece provést platné porovnání s něčím, co se nepoužívá v dané skupině.

Klíčová slova EXISTS, ANY a ALL Použití klíčových slov EXISTS, ANY a ALL není pro náhodného pozorovatele intuitivně zřejmé. Operátor EXISTS přijímá jako argument poddotaz a vrací buď hodnotu TRUE, pokud tento poddotaz něco vrátí, nebo FALSE, pokud je jeho výsledná sada prázdná:

Vstup/výstup mysql> select name, orderedon -> from orders

K1733.indd 176

18.1.2010 16:17:38

LEKCE 6: Vkládání poddotazů do dotazů

177

-> where exists -> (select * -> from orders -> where name = ‘Mega Kola’); +--------------+------------+ | NAME | ORDEREDON | +--------------+------------+ | Mega Kola | 2006-03-15 | | Mega Kola | 2006-03-19 | | Mega Kola | 2006-09-02 | | Mega Kola | 2006-06-30 | | CykloSpec | 2006-06-30 | | CykloSpec | 2006-05-30 | | CykloSpec | 2006-01-17 | | LX Obchůdek | 2006-01-17 | | LX Obchůdek | 2006-06-01 | | Cyklo ABC | 2006-06-01 | | Cyklo ABC | 2006-07-01 | | Cyklo ABC | 2006-07-01 | | Cyklo Franta | 2006-07-11 | +--------------+------------+ 13 rows in set (0.00 sec)

Poddotaz uvnitř EXISTS se v tomto nekorelovaném příkladu vyhodnotí pouze jednou. Výsledek poddotazu obsahuje nejméně jeden řádek, a proto se EXISTS vyhodnotí na TRUE a vypíšou se všechny řádky v dotazu. Pokud poddotaz změníme níže uvedeným způsobem, pak neobdržíme žádné výsledky. SELECT NAME, ORDEREDON FROM ORDERS WHERE EXISTS (SELECT * FROM ORDERS WHERE NAME =‘Povětšinou neškodný‘)

Operátor EXISTS se zde vyhodnotí na FALSE. Poddotaz negeneruje žádný výsledek, protože text „Povětšinou neškodný“ neodpovídá žádnému ze jmen. POZNÁMKA

Všimněte si, že v poddotazu uvnitř operátoru EXISTS používáme SELECT *. Operátor EXISTS se totiž nestará o počet vrácených sloupců.

Tímto způsobem lze pomocí operátoru EXISTS ověřit existenci určitých řádků a řídit výstup dotazu na základě jejich přítomnosti či nepřítomnosti. Použijeme-li operátor EXISTS v korelovaném poddotazu, vyhodnotí se pro každý případ definovaný vytvořenou korelací:

Vstup/výstup mysql> select name, orderedon -> from orders o

K1733.indd 177

18.1.2010 16:17:38

178

Tato drobná modifikace prvního, nekorelovaného dotazu vrátí všechny obchody s jízdními koly z Brna, které provedly objednávky. Následující poddotaz se spustí pro každý řádek v dotazu korelovaném podle jmen v tabulkách CUSTOMER a ORDER: (SELECT * FROM CUSTOMER C WHERE TOWN = ‘Brno‘ AND C.NAME = O.NAME)

Operátor EXISTS vrátí hodnotu TRUE pro ty řádky, které mají odpovídající jména v tabulce CUSTOMER s umístěním v Brně. V opačném případě vrátí hodnotu FALSE. Při použití operátoru EXISTS není ani nutné, aby poddotaz vůbec vracel konkrétní data. Pokud jsou podmínky v poddotazu splněny, pak lze jednoduše vrátit libovolně zvolenou hodnotu. V následujícím příkladu vracíme místo všech sloupců (*) číslo 1, čímž zvýšíme výkon poddotazu: SELECT NAME, ORDEREDON FROM ORDERS O WHERE EXISTS (SELECT 1 FROM CUSTOMER C WHERE TOWN = ‘Brno‘ AND C.NAME = O.NAME)

S operátorem EXISTS úzce souvisejí také operátory ANY, ALL a SOME. Operátory ANY a SOME jsou, co se funkčnosti týče, naprosto identické. Optimista by řekl, že uživatel tak má na výběr, který z nich bude používat. Pesimista by tuto situaci viděl jako další komplikaci. Operátor EXISTS kontroluje, zda poddotaz vrátí jakákoli data. Operátory ANY, ALL a SOME se používají k porovnání hodnoty sloupce z dotazu s daty vrácenými poddotazem. Operátory ANY a SOME ověřují, zda se hodnota daného sloupce nachází v datech vrácených poddotazem. Operátor ALL se používá ke kontrole, zda hodnota daného sloupce přesně odpovídá hodnotě či hodnotám vráceným poddotazem. Podívejte se tento dotaz:

K1733.indd 178

18.1.2010 16:17:38

LEKCE 6: Vkládání poddotazů do dotazů

179

Operátor ANY porovnává výstup následujícího poddotazu s každým řádkem dotazu a vrací hodnotu TRUE pro každý řádek dotazu, který obsahuje nějaký výsledek z poddotazu. (SELECT NAME FROM ORDERS WHERE NAME = ‘Mega Kola‘)

Po nahrazení ANY klíčovým slovem SOME obdržíme naprosto stejný výsledek:

Pravděpodobně jste si již všimli podobnosti s operátorem IN. Stejný dotaz využívající operátor IN vypadá takto:

Vstup/výstup mysql> select name, orderedon -> from orders -> where name in

K1733.indd 179

18.1.2010 16:17:38

180

Jak můžete vidět, operátor IN vrací tentýž výsledek jako operátory ANY a SOME. Copak se svět úplně zbláznil? Ještě ne. Dokáže snad operátor IN tohle?

Vstup/výstup mysql> select name, orderedon -> from orders -> where name > any -> (select name -> from orders -> where name = ‘Cyklo Franta’); +-------------+------------+ | NAME | ORDEREDON | +-------------+------------+ | Mega Kola | 2006-03-15 | | Mega Kola | 2006-03-19 | | Mega Kola | 2006-09-02 | | Mega Kola | 2006-06-30 | | CykloSpec | 2006-06-30 | | CykloSpec | 2006-05-30 | | CykloSpec | 2006-01-17 | | LX Obchůdek | 2006-01-17 | | LX Obchůdek | 2006-06-01 | +-------------+------------+ 9 rows in set (0.00 sec)

Odpověď je samozřejmě: nedokáže. Operátor IN funguje jako více rovnítek. Operátory IN a SOME lze použít s dalšími relačními operátory, jako je větší než nebo menší než. Dobře si jej proto zapamatujte. Operátor ALL vrací hodnotu TRUE pouze tehdy, pokud všechny výsledky poddotazu splňují jistou podmínku. Používá se kupodivu jako dvojitý zápor:

Vstup/výstup mysql> -> -> -> ->

K1733.indd 180

select name, orderedon from orders where name <> all (select name from orders

18.1.2010 16:17:38

LEKCE 6: Vkládání poddotazů do dotazů

181

-> where name = ‘Cyklo Franta’); +-------------+------------+ | NAME | ORDEREDON | +-------------+------------+ | Mega Kola | 2006-03-15 | | Mega Kola | 2006-03-19 | | Mega Kola | 2006-09-02 | | Mega Kola | 2006-06-30 | | CykloSpec | 2006-06-30 | | CykloSpec | 2006-05-30 | | CykloSpec | 2006-01-17 | | LX Obchůdek | 2006-01-17 | | LX Obchůdek | 2006-06-01 | | Cyklo ABC | 2006-06-01 | | Cyklo ABC | 2006-07-01 | | Cyklo ABC | 2006-07-01 | +-------------+------------+ 12 rows in set (0.00 sec)

Tento příklad vrací všechny obchody kromě „Cyklo Franta“. Výraz <> ALL se vyhodnotí na TRUE jen tehdy, pokud výsledná sada neobsahuje to, co je uvedeno na levé straně operátoru <>.

Shrnutí V této lekci jste si vyzkoušeli desítky cvičení obsahujících poddotazy. Díky tomu jste se naučili, jak používat jednu z nejdůležitějších součástí jazyka SQL. Poddotaz představuje metodu pro umístění dodatečných podmínek na data vrácená dotazem. Poddotaz poskytuje úžasnou flexibilitu při definování podmínek, především pak podmínek, u nichž neznáte přesnou hodnotu. Představte si, že potřebujete získat seznam všech produktů s nadprůměrnou cenou, přičemž nemusíte okamžitě vědět, jaká je celková průměrná cena. V takovém případě sáhnete po poddotazu, který průměrnou cenu vypočítá. V této lekci jsme též otevřeli jednu z nejobtížnějších částí jazyka SQL: korelované poddotazy. Korelované poddotazy vytvářejí vztah mezi dotazem a poddotazem, který se vyhodnocuje pro každou instanci tohoto vztahu. Kromě toho jste se dozvěděli o operátorech EXISTS, ANY, SOME a ALL, které se používají v poddotazech. Operátor EXISTS ověřuje, zda poddotaz vrací data na základě podmínek v tomto poddotazu. Operátory ANY a SOME jsou podobné jako operátor IN a kontrolují, zda se v datech vrácených poddotazem nachází hodnota daného sloupce. Operátor ALL se používá ke zjištění, zda jsou data sloupce stejná jako ta, která vrací poddotaz. Nenechte se odradit délkou výsledných dotazů. Snadno jim porozumíte, když si je rozdělíte na jednotlivé poddotazy.

Otázky a odpovědi Otázka:

K1733.indd 181

V této lekci jsem si všiml, že v některých případech existuje pro získání téhož výsledku více způsobů. Není tato flexibilita matoucí?

18.1.2010 16:17:38

182

ČÁST I: Úvod do SQL Odpověď: To opravdu není. Díky tomu, že máte k dispozici více způsobů, jak dosáhnout téhož výsledku, můžete vytvářet opravdu parádní příkazy. Flexibilita je předností jazyka SQL. Otázka: Jaké situace vyžadují, abych musel jít pro získání informace mimo dotaz? Odpověď: Poddotazy vám umožňují lépe upřesnit podmínky na data, která váš dotaz vrátí. Pomocí poddotazu můžete umístit podmínku na dotaz, aniž byste znali přesné hodnoty, které chcete použít v porovnání. Otázka: Jaká je skutečná výhoda při používání korelovaných poddotazů oproti běžným poddotazům? Odpověď: Korelované poddotazy vám oproti standardním poddotazům nabízejí větší flexibilitu, protože můžete tabulky v poddotazu spojovat s tabulkami v hlavním dotazu. Podstatná je opět větší flexibilita k vytváření promyšlenějších dotazů.

Úkoly pro vás Tato část nabízí kvízové otázky, které vám pomohou s upevněním získaných znalostí, a dále cvičení, jež vám poskytnou praktické zkušenosti s používáním osvojené látky. Pokuste se před nahlédnutím na odpovědi v příloze A odpovědět na otázky v kvízu a ve cvičení.

Kvíz 1. V části „Vnořování poddotazů“ vracel ukázkový poddotaz několik hodnot: CykloSpec CykloSpec LX Obchůdek LX Obchůdek Cyklo Franta

Některé z nich jsou tu dvakrát. Proč ve výsledné sadě tyto duplicity nejsou? 2. Jsou následující tvrzení pravdivá, či nepravdivá? a. Agregační funkce SUM, COUNT, MIN, MAX a AVG vracejí více hodnot. b. Maximální počet poddotazů, které lze vnořit, je dva. c. Korelované poddotazy jsou zcela nezávislé. 3. Bude následující poddotaz fungovat s níže uvedenými tabulkami ORDERS a PART? SQL> SELECT * FROM PART; PARTNUM DESCRIPTION ------- -----------54 Pedály 42 Sedla 46 Pneu 23 Horské kolo 76 Silniční kolo 10 Dvojkolo 6 rows selected.

K1733.indd 182

PRICE ------542.50 245.00 152.50 3504.50 5300.00 12000.00

18.1.2010 16:17:39

LEKCE 6: Vkládání poddotazů do dotazů

183

SQL> SELECT * FROM ORDERS; ORDEREDON NAME ----------- -----------15-MAY-2006 Mega Kola 19-MAY-2006 Mega Kola 2-SEP-2006 Mega Kola 30-JUN-2006 Mega Kola 30-JUN-2006 CykloSpec 30-MAY-2006 CykloSpec 17-JAN-2006 CykloSpec 17-JAN-2006 LX Obchůdek 1-JUN-2006 LX Obchůdek 1-JUN-2006 Cyklo ABC 1-JUL-2006 Cyklo ABC 1-JUL-2006 Cyklo ABC 11-JUL-2006 Cyklo Franta 13 rows selected.

PARTNUM ------23 76 10 42 54 23 76 76 10 10 76 46 76

QUANTITY -------6 3 1 8 10 8 11 5 3 1 4 14 14

REMARKS --------Zaplaceno Zaplaceno Zaplaceno Zaplaceno Zaplaceno Zaplaceno Zaplaceno Zaplaceno Zaplaceno Zaplaceno Zaplaceno Zaplaceno Zaplaceno

a. SELECT * FROM ORDERS WHERE PARTNUM = SELECT PARTNUM FROM PART WHERE DESCRIPTION = ‘Mega Kola‘;

b. SELECT PARTNUM FROM ORDERS WHERE PARTNUM = (SELECT * FROM PART WHERE DESCRIPTION = ‘LX Obchůdek‘);

c. SELECT NAME, PARTNUM FROM ORDERS WHERE EXISTS (SELECT * FROM ORDERS WHERE NAME = ‘Mega Kola‘);

Cvičení 1. Představte si, že databázový systém MySQL nepodporuje poddotazy. Napište dva samostatné dotazy, které vrátí pole NAME a ORDERON z tabulky ORDERS pro ta jména, která jsou umístěná za jménem „Cyklo Franta“. První krok spočívá ve stanovení dotazu, jenž vytvoří výslednou sadu, která se použije při porovnání. 2. Napište dotaz, který zobrazí název nejdražší součástky.

K1733.indd 183

18.1.2010 16:17:39