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:

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