4. díl - PostgreSQL - Výběr dat (vyhledávání)

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

Unicorn College ONEbit hosting Tento obsah je dostupný zdarma v rámci projektu IT lidem. Vydávání, hosting a aktualizace umožňují jeho sponzoři.

V minulém dílu seriálu tutoriálů o PostgreSQL databázi jsme si ukázali vkládání a mazání záznamů. Dnes 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 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

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. Něco jsem vám připravil. Tabulku si nejprve vyprázdněte.

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);
Testovací uživatelé v pgAdmin

V databázi máme 31 uživatelů. To by mělo stačit k tomu, abyste si na nich vyzkoušeli základy dotazování.

Vacuum

Při častějším vkládání a mazání dat do tabulky se vám může objevit následující okno.

pgAdmin vacuum

V případě, že provedete update nebo mazání záznamu, nová data jsou vložena do databáze, ale starý záznam není nahrazen, ale označen za nevalidní. Vacuum je proces, který projde databázi či tabulku a zjistí (odstraní) záznamy, které již nejsou používané.

Spuštění vacuum

Vacuum je proces údržby databáze, který by se měl spouštět pravidelně. Pokud se vám tedy zobrazí doporučení, klidně jej potrvrďte a vacuum spusťte.

Dotazování

Okno Query, pomocí kterého vkládáme SQL příkazy, nabízí záložku Graphical Query Builder. Přepněte se na tuto záložku a v pravé části (průzkumník) se proklikejte k tabulce uzivatel (Schemas -> public -> uzivatel). Dvakrát na ni klikněte myší. Tabulka se vám zobrazí v pravém horním okně záložky Graphical Query Builder (GQB).

PostgreSQL Graphical Query Builder

Tímto jste GQB řekli, aby vytvořil dotaz na všechny záznamy v tabulce uzivatel. Pokud se nyní přepnete do záložky SQL Editor (SQLE), zjistíte, že v horním okně je vytvořený SQL dotaz.

SELECT
  *
FROM
  public.uzivatel;

Dotaz spusťte (F5). Ve spodní části záložky se vám zobrazí výsledek dotazu.

Výběr všech uživatelů v tabulce

Pokud v GQB zaškrtnete určité sloupce, vygeneruje se vám SQL dotaz na všechny záznamy v tabulce se zobrazením údajů pouze pro dané sloupce.

Výběr jen určitých sloupců v pgAdmin
Výběr jen určitých sloupců v pgAdmin

Graphical Query Builder je určitě zajímavý nástroj, ale nyní se budeme věnovat psaní SQL dotazů, ne jejich vyklikávání.

Ukažme si SQL dotaz pro vyhledání všech záznamů z tabulky uzivatel (vyber všechny sloupce z tabulky uzivatel). Hvězdička označuje, že chceme vybrat všechny sloupce.

SELECT * FROM uzivatel;
Výběr všech sloupců z PostgreSQL tabulky v pgAdmin

SQL dotaz pro vyhledání všech záznamů z tabulky uzivatel a zobrazení pouze sloupců se jménem a příjmením (vyber sloupce jmeno, prijmeni z tabulky uzivatel):

SELECT jmeno, prijmeni FROM uzivatel;
Výběr určitých sloupců v PostgreSQL

Základní dotaz pro výběr všech Janů z tabulky uzivatel (vyber všechny sloupce z tabulky uzivatel kde je jméno Jan):

SELECT * FROM uzivatel WHERE jmeno = 'Jan';
Výběr všech Janů

Tabulky mají většinou 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 jen 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:

Novák     17
Spáčil    3
Počet článků v databázi v PostgreSQL

Opravdu nebuďte líní a pokud nepotřebujete téměř všechny sloupce, vyjmenujte v SELECTu 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á :) 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.

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 uzivatel where datum_narozeni >= '1960-1-1' AND pocet_clanku > 5;

Výsledek:

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 =, >, <, >=, <=, != 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 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í:

Svoboda
Svobodová
Spáčil

Pokud byste chtěli vyhledávat case-insesitive (nezáleželo by na velikosti písmen), použijte 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:

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

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 2 hodnoty píšeme AND.

Výsledek:

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. U výběru dat zůstaneme ještě několik dílů, vlastně většinu tohoto seriálu. Příště se podíváme na řazení a agregační funkce.


 

 

Článek pro vás napsal vita
Avatar
Jak se ti líbí článek?
4 hlasů
vita
Aktivity (1)

 

 

Komentáře

Děláme co je v našich silách, aby byly zdejší diskuze co nejkvalitnější. Proto do nich také mohou přispívat pouze registrovaní členové. Pro zapojení do diskuze se přihlas. Pokud ještě nemáš účet, zaregistruj se, je to zdarma.

Zatím nikdo nevložil komentář - buď první!