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 - 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...):

Vacuum - PostgreSQL databáze krok za krokem

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:

View/Edit Data - PostgreSQL databáze krok za krokem

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


 

Předchozí článek
Řešené úlohy k 1.-5. lekci PostgreSQL
Všechny články v sekci
PostgreSQL databáze krok za krokem
Přeskočit článek
(nedoporučujeme)
Řešené úlohy k 6. lekci PostgreSQL
Článek pro vás napsal vita
Avatar
Uživatelské hodnocení:
55 hlasů
vita
Aktivity