Vydělávej až 160.000 Kč měsíčně! Akreditované rekvalifikační kurzy s garancí práce od 0 Kč. Více informací.
Hledáme nové posily do ITnetwork týmu. Podívej se na volné pozice a přidej se do nejagilnější firmy na trhu - Více informací.

Lekce 6 - Oracle krok za krokem: Výběr dat (vyhledávání)

V předešlém cvičení, Řešené úlohy k 1.-5. lekci Oracle, jsme si procvičili nabyté zkušenosti z předchozích lekcí.

Dnes se zaměříme na tu nejhezčí část a tou je výběr dat. Jedná se o dotazování na data, jestli chcete, tak vyhledávání v tabulce.

Výběr dat je klíčovou funkcí databází, umožňuje nám totiž pomocí relativně jednoduchých dotazů dělat i složité výběry dat. Od prostého výběru uživatele podle jeho id (např. pro zobrazení detailů v aplikaci) můžeme vyhledávat uživatele splňující určité vlastnosti, výsledky řadit dle různých kritérií nebo dokonce do dotazu zapojit více tabulek, různé funkce a skládat dotazy do sebe (o tom až v dalších dílech).

Testovací data

Než do tabulky vložíme testovací data, nejdříve si ji smažeme a vytvoříme znovu, tentokrát s automaticky se navyšujícím ID, což je v praxi samozřejmostí:

DROP TABLE UZIVATELE;

CREATE TABLE UZIVATELE
(
  UZIVATELE_ID INT GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1)
, JMENO VARCHAR2(60) NOT NULL
, PRIJMENI VARCHAR2(60) NOT NULL
, DATUM_NAROZENI DATE NOT NULL
, POCET_CLANKU INT NOT NULL
, CONSTRAINT UZIVATELE_PK PRIMARY KEY
  (
    UZIVATELE_ID
  )
  ENABLE
);

Syntaxí START with 1 za klauzulí IDENTITY, že začínáme počítat od čísla 1. INCREMENT by 1 nám zase říká, že budeme inkrementovat (zvyšovat číslo) číslem 1. Nastavujeme pak také tzv. PRIMARY KEY, což znamená jednoznačný identifikátor řádku. Můžeme pak díky jednoho čísla v tabulce odkazovat na konkrétní záznam.

Pokud nám zde SQL Developer bude hlásit, že nemáme dostatečná oprávnění, přepneme si připojení na IT_NETWORK_ORACLE_DB (heslo admin), ve kterém jsme si v druhé lekci tvořili schéma, a do worksheetu vložíme a spustíme následující příkaz:

GRANT ALL PRIVILEGES TO noveSchema;

Tímto jsme si zajistili veškerá potřebná oprávnění. Nyní si do nové tabulky vložíme předpřipravená testovací data. Využijeme k tomu jednu z metod vložení více záznamů najednou, kterou jsem zde zatím neukazoval, jelikož je o něco složitější než INSERT ALL:

INSERT INTO uzivatele (jmeno, prijmeni, datum_narozeni, pocet_clanku)
  WITH zaznamy AS (
    SELECT 'Jan', 'Novák', '3.11.1984', 17        FROM dual UNION ALL
    SELECT 'Tomáš', 'Marný', '17.10.1942', 12     FROM dual UNION ALL
    SELECT 'Josef', 'Nový', '10.7.1958', 5        FROM dual UNION ALL
    SELECT 'Alfons', 'Svoboda', '15.5.1935', 6    FROM dual UNION ALL
    SELECT 'Ludmila', 'Dvořáková', '17.4.1967', 2 FROM dual UNION ALL
    SELECT 'Petr', 'Černý', '20.2.1995', 1        FROM dual UNION ALL
    SELECT 'Vladimír', 'Pokorný', '18.4.1984', 1  FROM dual UNION ALL
    SELECT 'Ondřej', 'Bohatý', '14.5.1973', 3     FROM dual UNION ALL
    SELECT 'Vítězslav', 'Churý', '2.6.1969', 7    FROM dual UNION ALL
    SELECT 'Pavel', 'Procházka', '3.7.1962', 8    FROM dual UNION ALL
    SELECT 'Matěj', 'Horák', '10.9.1974', 0       FROM dual UNION ALL
    SELECT 'Jana', 'Veselá', '2.10.1976', 1       FROM dual UNION ALL
    SELECT 'Miroslav', 'Kučera', '3.11.1948', 1   FROM dual UNION ALL
    SELECT 'František', 'Veselý', '9.5.1947', 1   FROM dual UNION ALL
    SELECT 'Michal', 'Krejčí', '7.3.1956', 0      FROM dual UNION ALL
    SELECT 'Lenka', 'Němcová', '11.2.1954', 5     FROM dual UNION ALL
    SELECT 'Věra', 'Marková', '21.1.1978', 3      FROM dual UNION ALL
    SELECT 'Eva', 'Kučerová', '26.7.1949', 12     FROM dual UNION ALL
    SELECT 'Lucie', 'Novotná', '28.7.1973', 4     FROM dual UNION ALL
    SELECT 'Jaroslav', 'Novotný', '11.8.1980', 8  FROM dual UNION ALL
    SELECT 'Petr', 'Dvořák', '30.9.1982', 18      FROM dual UNION ALL
    SELECT 'Jiří', 'Veselý', '15.1.1961', 2       FROM dual UNION ALL
    SELECT 'Martina', 'Krejčí', '29.8.1950', 4    FROM dual UNION ALL
    SELECT 'Marie', 'Černá', '26.2.1974', 5       FROM dual UNION ALL
    SELECT 'Věra', 'Svobodová', '2.3.1983', 2     FROM dual UNION ALL
    SELECT 'Pavel', 'Dušín', '1.5.1991', 9        FROM dual UNION ALL
    SELECT 'Otakar', 'Kovář', '17.12.1992', 9     FROM dual UNION ALL
    SELECT 'Kateřina', 'Koubová', '15.11.1956', 4 FROM dual UNION ALL
    SELECT 'Václav', 'Blažek', '20.10.1953', 6    FROM dual UNION ALL
    SELECT 'Jan', 'Spáčil', '6.5.1967', 3         FROM dual UNION ALL
    SELECT 'Zdeněk', 'Malačka', '10.3.1946', 6    FROM dual
  )
  SELECT * FROM zaznamy;

Syntaxi dotazu nyní více rozebírat nebudeme, zkrátka vloží hromadně záznamy do tabulky uzivatele. Na rozdíl třeba od MySQL se syntaxe hodně liší a nemusí dávat pořádně smysl (voláme INSERT a v něm pak příkaz SELECT na výběr tabulky a tím vložíme uživatele). Do databáze se nám tímto vložilo 31 uživatelů. To by mělo stačit k tomu, abychom si na nich vyzkoušeli základy dotazování.

Dotazování

Dotaz na data, tedy jejich vyhledání/výběr, naleznete v prostředí SQL Developeru při rozkliknutí dané tabulky pod položkou Data. Zkusme si to, stačí zadat název_sloupce='hod­nota'. Pokud chceme filtrovat skrze více sloupců, můžeme použít klauzuli AND:

Oracle

SQL dotaz bude poté vypadat takto:

SELECT * FROM uzivatele WHERE jmeno='Jan' AND prijmeni='Novák';

Příkaz je asi srozumitelný. Hvězdička po příkazu SELECT označuje, že chceme vybrat všechny sloupce. Dotaz tedy česky zní: "Vyber všechny sloupce z tabulky uzivatele, kde je jméno Jan a příjmení Novák". Jelikož se jedná o tzv. textové řetězce, musíme dávat hodnoty do uvozovek ' nebo ". Čísla tedy dávat do uvozovek nedáváme (pokud nejde třeba o datum).

Tabulky mají většinou hodně sloupců a většinou nás zajímají jen některé. Abychom databázi nezatěžovali přenášením zbytečných dat zpět do naši aplikace, budeme se snažit vždy specifikovat ty sloupce, které chceme. Dejme tomu, že budeme chtít příjmení a počet napsaných článků lidí, kteří se jmenují Jan. Dotaz bude vypadat následovně:

SELECT prijmeni, pocet_clanku FROM uzivatele WHERE jmeno='Jan';

Výsledek:

JMENO POCET_CLANKU
Novák 17
Spáčil 3

Opravdu nebuďte líní a pokud nepotřebujete téměř všechny sloupce, vyjmenujte v SELECT ty, jejichž hodnoty vás v tu chvíli zajímají. Vždy se snažte podmínku omezit co nejvíce již na úrovni databáze, ne že si vytaháte celou tabulku do aplikace a tam si ji vyfiltrujete. Řekněme, že by vaše aplikace poté nebyla úplně rychlá a databáze samotná vás nebude mít ráda :) Výčet sloupců, které má dotaz vrátit, nemá nic společného s dalšími sloupci, které v dotazu používáme. Můžeme tedy vyhledávat podle deseti sloupců, ale vrátit jen jeden.

Stejně jako tomu bylo u příkazu DELETE, i zde bude fungovat pouze dotaz:

SELECT * FROM uzivatele;

Tehdy budou vybráni úplně všichni uživatelé (záznamy) z tabulky.

U podmínkování platí to samé jako u DELETE, klauzule WHERE funguje úplně stejně. Zkusme si to. Vyberme všechny uživatele, narozené po roce 1960 a s počtem článků vyšším než 5:

SELECT * FROM uzivatele WHERE datum_narozeni >= '1.1.1960' AND pocet_clanku > 5;

Výsledek:

UZIVATELE_ID JMENO PRIJMENI DATUM_NAROZENI POCET_CLANKU
1 Jan Novák 03.11.84 17
9 Vítězslav Churý 02.06.69 7
10 Pavel Procházka 03.07.62 8
20 Jaroslav Novotný 11.08.80 8
21 Petr Dvořák 30.09.82 18
26 Pavel Dušín 01.05.91 9
27 Otakar Kovář 17.12.92 9

Operátory

Základní operátory =, >, <, >=, <=, != určitě umíte použít. V SQL máme ale další, řekněme si o LIKE, IN a BETWEEN.

LIKE

LIKE umožňuje vyhledávat textové hodnoty jen podle části textu. Funguje podobně, jako operátor "=" (rovná se), pouze můžeme používat 2 zástupné znaky:

  • % (procento) označuje libovolný počet libovolných znaků.
  • _ (podtržítko) označuje jeden libovolný znak.

Pojďme si vyzkoušet několik dotazů s operátorem LIKE. Najděme příjmení lidí začínající na písmeno S:

SELECT prijmeni FROM uzivatele WHERE prijmeni LIKE 'S%';

Zadáme normálně text v apostrofech, pouze na některá místa můžeme vložit speciální znaky. Na velikosti písmen záleží (hledání je tedy case-sensitive). Výsledek dotazu bude následující:

Svoboda
Svobodová
Spáčil

Nyní zkusme najít pětipísmenná příjmení, která mají jako 2. znak O:

SELECT prijmeni FROM uzivatele WHERE prijmeni LIKE '_o___';

Výsledek:

JMENO
Novák
Horák
Kovář

Asi již tušíte, jak LIKE funguje. Použití lze vymyslet hodně, většinou se používá s procenty na obou stranách pro tzv. fulltextové vyhledávání (např. slova v textu článku).

IN

IN umožňuje vyhledávat pomocí výčtu prvků. Udělejme si tedy výčet jmen a vyhledejme uživatelé s těmito jmény:

SELECT jmeno, prijmeni FROM uzivatele WHERE jmeno IN ('Petr', 'Jan', 'Kateřina');

Výsledek:

JMENO PRIJMENI
Jan Novák
Petr Černý
Petr Dvořák
Kateřina Koubová
Jan Spáčil

Operátor IN se používá ještě u tzv. poddotazů, ale na ty máme ještě dost času :)

BETWEEN

Poslední operátor, který si dnes vysvětlíme, je BETWEEN (tedy mezi). Není ničím jiným, než zkráceným zápisem podmínky >= AND <=. Již víme, že i datumy můžeme normálně porovnávat, najděme si uživatele, kteří se narodili mezi lety 1980 a 1990:

SELECT jmeno, prijmeni, datum_narozeni FROM uzivatele WHERE datum_narozeni BETWEEN '1.1.1980' AND '1.1.1990';

Výsledek:

JMENO PRIJMENI DATUM_NAROZENI
Jan Novák 03.11.84
Vladimír Pokorný 18.04.84
Jaroslav Novotný 11.08.80
Petr Dvořák 30.09.82
Věra Svobodová 02.03.83

To je pro dnešek vše. U výběru dat zůstaneme ještě několik dílů, vlastně většinu tohoto seriálu.

V následujícím cvičení, Řešené úlohy k 6. lekci Oracle, si procvičíme nabyté zkušenosti z předchozích lekcí.


 

Stáhnout

Stažením následujícího souboru souhlasíš s licenčními podmínkami

Staženo 207x (3.94 kB)

 

Předchozí článek
Řešené úlohy k 1.-5. lekci Oracle
Všechny články v sekci
Oracle
Přeskočit článek
(nedoporučujeme)
Řešené úlohy k 6. lekci Oracle
Článek pro vás napsal Matěj Kadlec
Avatar
Uživatelské hodnocení:
19 hlasů
Autor se programování věnuje od střední školy, nyní studuje informatiku na VŠB-TUO a pracuje jako ETL developer pro společnost IDC. Mezi jeho oblíbené jazyky patří Python a SQL.
Aktivity