4. diel - PostgreSQL - Výber dát (vyhľadávanie)

V minulom dieli seriálu tutoriálov o PostgreSQL databáze sme si ukázali vkladanie a mazanie záznamov. Dnes sa zameriame na výber dát (vyhľadávanie v tabuľke).

Výber dát je kľúčovou funkciou databáz, umožňuje nám totiž pomocou relatívne jednoduchých otázok robiť aj zložité výbery dát. Od prostého výberu užívateľa podľa jeho id (napr. Pre zobrazenie detailov v aplikácii) môžeme vyhľadávať užívateľa spĺňajúce určité vlastnosti, výsledky zoradiť podľa rôznych kritérií alebo dokonca do dotazu zapojiť viac tabuliek, rôzne funkcie a skladať dotazy do seba (o tom až v ďalších dieloch ).

Testovacie dáta

Pred skúšaním otázok je vždy dobré mať nejaké testovacie dáta, aby sme mali s čím pracovať a nemali tam len 4 užívateľov. Poďme si do našej tabuľky uzivatel vložiť nejaké záznamy, aby bolo s čím pracovať. Niečo som vám pripravil. Tabuľku si najprv vyprázdnite.

DELETE FROM uzivatel;

Pokiaľ chcete mať úplne rovnaké dáta ako v nasledujúcom príklade, tabuľku dropněte, vytvorte znovu a vložte dáta pomocou nasledujúceho skriptu (id prvého záznamu bude začínať jednotkou):

INSERT INTO uzivatel (
        jmeno,
        prijmeni,
        datum_narozeni,
        pocet_clanku
)
VALUES
('Jan',  'Novák',  '1984-11-03', 17),
('Tomáš', 'Marný', '1942-10-17', 12),
('Josef', 'Nový', '1958-7-10', 5),
('Alfons', 'Svoboda', '1935-5-15', 6),
('Ludmila', 'Dvořáková', '1967-4-17', 2),
('Petr', 'Černý', '1995-2-20', 1),
('Vladimír', 'Pokorný', '1984-4-18', 1),
('Ondřej', 'Bohatý', '1973-5-14', 3),
('Vítezslav', 'Churý', '1969-6-2', 7),
('Pavel', 'Procházka', '1962-7-3', 8),
('Matěj', 'Horák', '1974-9-10', 0),
('Jana', 'Veselá', '1976-10-2', 1),
('Miroslav', 'Kučera', '1948-11-3', 1),
('František', 'Veselý', '1947-5-9', 1),
('Michal', 'Krejčí', '1956-3-7', 0),
('Lenka', 'Němcová', '1954-2-11', 5),
('Věra', 'Marková', '1978-1-21', 3),
('Eva', 'Kučerová', '1949-7-26', 12),
('Lucie', 'Novotná', '1973-7-28', 4),
('Jaroslav', 'Novotný', '1980-8-11', 8),
('Petr', 'Dvořák', '1982-9-30', 18),
('Jiří', 'Veselý', '1961-1-15', 2),
('Martina', 'Krejčí', '1950-8-29', 4),
('Marie', 'Černá', '1974-2-26', 5),
('Věra', 'Svobodová', '1983-3-2', 2),
('Pavel', 'Dušín', '1991-5-1', 9),
('Otakar', 'Kovář', '1992-12-17', 9),
('Kateřina', 'Koubová', '1956-11-15', 4),
('Václav', 'Blažek', '1953-10-20', 6),
('Jan', 'Spáčil', '1967-5-6', 3),
('Zdeněk', 'Malačka', '1946-3-10', 6);

V databáze máme 31 užívateľov. To by malo stačiť na to, aby ste si na nich vyskúšali základy dopytovania.

Vacuum

Pri častejšom vkladanie a mazanie dát do tabuľky sa vám môže objaviť nasledujúce okno.

V prípade, že vykonáte update alebo mazanie záznamu, nové dáta sú vložené do databázy, ale starý záznam nie je nahradený, ale označený za nevalidný. Vacuum je proces, ktorý prejde databázu či tabuľku a zistí (odstráni) záznamy, ktoré už nie sú používané.

Vacuum je proces údržby databázy, ktorý by sa mal spúšťať pravidelne. Pokiaľ sa vám teda zobrazí odporúčanie, pokojne ho potrvrďte a vacuum spustite.

Dopytovania

Okno Query, pomocou ktorého vkladáme SQL príkazy, ponúka záložku Graphical Query Builder. Prepnite sa na túto záložku a v pravej časti (prieskumník) sa preklikajte k tabuľke uzivatel (schemas -> public -> uzivatel). Dvakrát na ňu kliknite myšou. Tabuľka sa vám zobrazí v pravom hornom okne záložky Graphical Query Builder (GQB).

Týmto ste GQB povedali, aby vytvoril dotaz na všetky záznamy v tabuľke uzivatel. Ak sa teraz prepnete do záložky SQL Editor (SQLE), zistíte, že v hornom okne je vytvorený SQL dotaz.

SELECT
  *
FROM
  public.uzivatel;

Otázka spustite (F5). V spodnej časti záložky sa vám zobrazí výsledok dotazu.

Ak v GQB zaškrtnete určitej stĺpce, vygeneruje sa vám SQL dotaz na všetky záznamy v tabuľke so zobrazením údajov len pre dané stĺpce.

Graphical Query Builder je určite zaujímavý nástroj, ale teraz sa budeme venovať písania SQL dotazov, nie ich vyklikávání.

Ukážme si SQL dotaz pre vyhľadanie všetkých záznamov z tabuľky uzivatel (vyber všetky stĺpce z tabuľky uzivatel). Hviezdička označuje, že chceme vybrať všetky stĺpce.

SELECT * FROM uzivatel;

SQL dotaz pre vyhľadanie všetkých záznamov z tabuľky uzivatel a zobrazenie len stĺpcov s menom a priezviskom (vyber stĺpce Meno, priezvisko z tabuľky uzivatel):

SELECT jmeno, prijmeni FROM uzivatel;

Základná otázka pre výber všetkých Janů z tabuľky uzivatel (vyber všetky stĺpce z tabuľky uzivatel kde je meno Ján):

SELECT * FROM uzivatel WHERE jmeno = 'Jan';

Tabuľky majú väčšinou veľa stĺpcov a väčšinou nás zaujímajú len nejaké. Aby sme databázu nezaťažovali prenášaním zbytočných dát späť do našej aplikácie, budeme sa snažiť vždy špecifikovať len tie stĺpce, ktoré chceme. Dajme tomu, že budeme chcieť len priezvisko ľudí, čo sa volajú Ján a ešte počet ich článkov.

Otázka upravíme:

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

výsledok:

Novák     17
Spáčil    3

Naozaj nebuďte leniví a ak nepotrebujete takmer všetky stĺpce, vymenujte v SELECT tie, ktorých hodnoty vás v tú chvíľu zaujímajú. Vždy sa snažte podmienku obmedziť čo najviac už na úrovni databázy, nie že si vyťaháme celú tabuľku do aplikácie a tam si ju vyfiltrujete. Povedzme, že by vaša aplikácia potom nebola úplne rýchla Výpočet stĺpcov, ktoré má dotaz vrátiť, nemá nič spoločné s ďalšími stĺpci, ktoré v dotaze používame. Môžeme teda vyhľadávať podľa desiatich stĺpcov, ale vrátiť len jeden.

U podmínkování platí to isté, ako u DELETE, klauzula WHERE funguje úplne rovnako. Skúsme si to. Vyberme všetkých užívateľov, narodené po roku 1960 as počtom článkov vyšším ako 5:

SELECT * FROM uzivatel where datum_narozeni >= '1960-1-1' AND pocet_clanku > 5;

výsledok:

1     Jan          Novák        1984-11-03    17
9    Vítezslav    Churý        1969-06-02    7
10    Pavel        Procházka    1962-07-03    8
20    Jaroslav     Novotný      1980-08-11    8
21    Petr         Dvořák       1982-09-30    18
26    Pavel        Dušín        1991-05-01    9
27    Otakar       Kovář        1992-12-17    9

Operátormi

Základný operátormi =,>, <,> =, <=,! = Určite viete použiť. V SQL máme ale ďalšie, povedzme si o LIKE, IN a BETWEEN.

LIKE

Like umožňuje vyhľadávať textové hodnoty len podľa časti textu. Funguje podobne, ako operátor "=" (rovná sa), len môžeme používať 2 zástupné znaky:

• % (Percento) označuje ľubovoľný počet ľubovoľných znakov.
• _ (Podčiarkovník) označuje jeden ľubovoľný znak.

Poďme si vyskúšať niekoľko otázok s operátorom like. Nájdime priezvisko ľudí začínajúce na S:

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

Zadáme normálne text apostrofmi, iba na niektoré miesta môžeme vložiť špeciálne znaky. Na veľkosti písmen záleží (hľadanie je teda case-sensitive). Výsledok dotazu bude nasledujúci:

Svoboda
Svobodová
Spáčil

Ak by ste chceli vyhľadávať case-insesitive (nezáležalo by na veľkosti písmen), použite operátor Ilike.

SELECT prijmeni FROM uzivatel WHERE prijmeni ILIKE 's%';

Teraz skúsme nájsť pětipísmenná priezvisko, ktoré majú ako 2. znak o:

SELECT prijmeni FROM uzivatel WHERE prijmeni LIKE '_o___';

výsledok:

Novák
Horák
Kovář

Asi už tušíte, ako LIKE funguje. Použitie možno vymyslieť veľa, väčšinou sa používa s percentami na oboch stranách pre fulltextové vyhľadávanie (napr. Slová v texte článku).

IN == IN umožňuje vyhľadávať pomocou zoznamu prvkov. Urobme si teda výpočet mien a vyhľadajme užívateľov s týmito menami:

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

výsledok:

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

Operátor IN sa používa ešte u tzv. Poddotazov, ale na tie máme ešte dosť času .

Between

Posledný operátor, ktorý si dnes vysvetlíme, je BETWEEN (teda medzi). Nie je ničím iným, než skráteným zápisom podmienky "> = AND <=". Už vieme, že aj dátumy môžeme normálne porovnávať, nájdime si užívateľov, ktorí sa narodili medzi rokmi 1980 a 1990:

SELECT jmeno, prijmeni, datum_narozeni FROM uzivatel WHERE datum_narozeni BETWEEN '1980-1-1' AND '1990-1-1';

Medzi 2 hodnoty píšeme AND.

výsledok:

Jan         Novák        1984-11-03
Vladimír    Pokorný      1984-04-18
Jaroslav    Novotný      1980-08-11
Petr        Dvořák       1982-09-30
Věra        Svobodová    1983-03-02

To je pre dnešok všetko. U výberu dát zostaneme ešte niekoľko dielov, vlastne väčšinu tohto seriálu. Nabudúce sa pozrieme na radenie a agregačné funkcie.