Lekce 6 - PostgreSQL - Výběr dat (vyhledávání)

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

V dnešním PostgreSQL tutoriálu se zaměříme na výběr dat (vyhledávání v tabulce).

Výběr dat je klíčovou funkcí databází. Umožňuje nám 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

Před zkoušením dotazů je vždy dobré mít nějaká testovací data, abychom měli s čím pracovat a neměli tam jen 4 uživatele. Pojďme si do naší tabulky uzivatel vložit nějaké záznamy, aby bylo s čím pracovat. Tabulku si nejprve vyprázdněme:

DELETE FROM uzivatel;

Pokud chcete mít zcela stejná data jako v následujícím příkladu, tabulku dropněte, vytvořte znovu a vložte data pomocí následujícího skriptu (ID prvního záznamu bude začínat jedničkou):

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ázi máme 31 uživatelů. To by mělo stačit k tomu, abychom si na nich vyzkoušeli základy dotazování.

Vacuum

V případě, že provedeme update nebo smazání záznamu, nová data jsou vložena do databáze, avšak starý záznam není nahrazen, ale pouze označen za nevalidní. Vacuum je proces údržby databáze, který v databázi či tabulce najde záznamy, které již nejsou používané, a smaže je.

Ve výchozím nastavení databáze se spouští automaticky a pravidelně.

Tento proces ale můžeme vyvolat i manuálně z okna Maintenance (pravým tlačítkem kliknout na databázi nebo tabulku a zvolit Maintenance...):

Dotazování

Dotaz na data, tedy jejich vyhledání/výběr, jsme již viděli, když jsme si zobrazovali všechny záznamy tabulky po jejich vložení. Pro rekapitulaci zobrazíme všechna data kliknutím pravým tlačítkem na tabulku a vybráním View/Edit Data:

Vidíme zde dotaz pro vyhledání všech záznamů z tabulky uzivatel a jejich následné seřazení podle ID (tím se však zatím zabývat nebudeme). Hvězdička označuje, že chceme vybrat všechny sloupce:

SELECT * FROM uzivatel;

Tento příkaz si můžeme zkusit spustit v okně Query Tool a dostaneme stejný výsledek.

WHERE

Dost často potřebujeme získat data na základě určitých kritérií. Například budeme hledat pouze Jany. K tomuto účelu se používá klauzule WHERE, kde se udávají podmínky ve formátu sloupec operátor hodnota. Základní operátory jsou: =, >, <, >=, <= a != Složitější operátory si ukážeme dále v této lekci.

Dotaz pro vyhledání Janů by vypadal následovně.

SELECT * FROM uzivatel WHERE jmeno = 'Jan';

Dotaz tedy česky zní: "Vyber všechny sloupce z tabulky uzivatel, kde je jméno Jan."

PgAdmin nám zobrazí výsledek dotazu:

uzivatel_id	jmeno	prijmeni	datum_narozeni	pocet_clanku
1	Jan	Novák	1984-11-03	17
30	Jan	Spáčil	1967-05-06	3

Tabulky mají mnohdy hodně sloupců a většinou nás zajímají jen nějaké. Abychom databázi nezatěžovali přenášením zbytečných dat zpět do naší aplikace, budeme se snažit vždy specifikovat ty sloupce, které chceme.

Dejme tomu, že budeme chtít jen příjmení lidí, co se jmenují Jan a ještě počet jejich článků. Dotaz upravíme:

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

Výsledek:

prijmeni	pocet_clanku
Novák	17
Spáčil	3

Vždy se snažíme podmínku omezit co nejvíce již na úrovni databáze. 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.

Teď vyberme všechny uživatele, narozené od roku 1960 a s počtem článků vyšším než 5:

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

Výsledek:

uzivatel_id	jmeno	prijmeni	datum_narozeni	pocet_clanku
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átory

Základní operátory =, >, <, >=, <=, != už umíme použít. Popíšeme si tyto další operátory: 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 navíc 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 "S":

SELECT prijmeni FROM uzivatel 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í:

prijmeni
Svoboda
Svobodová
Spáčil

Pokud budeme chtít vyhledávat case-insensitive (nezáleží na velikosti písmen), použijeme operátor ILIKE.

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

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

SELECT prijmeni FROM uzivatel WHERE prijmeni LIKE '_o___';

Výsledek:

prijmeni
Novák
Horák
Kovář

Asi již tušíme, jak LIKE funguje. Použití lze vymyslet hodně, většinou se používá s procenty na obou stranách pro 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živatele s těmito jmény:

SELECT jmeno, prijmeni FROM uzivatel 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 uzivatel WHERE datum_narozeni BETWEEN '1980-1-1' AND '1990-1-1';

Mezi dvě hodnoty píšeme AND.

Výsledek:

jmeno	prijmeni	datum_narozeni
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 pro dnešek vše.

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