Lekce 4 - SQLite - Výběr dat (vyhledávání)

V minulé lekci, SQLite - Vkládání a mazání dat v tabulce, jsme si ukázali vkládání a mazání záznamů.

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

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 uzivatele vložit nějaké záznamy, aby bylo s čím pracovat. Něco jsem vám připravil. Tabulku si nejprve vyprázdněte (abychom měli stejná data):

DELETE FROM "uzivatele";

Dále spusťte následující SQL dotaz:

INSERT INTO "uzivatele" (
        "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 38 uživatelů. To by mělo stačit k tomu, abyste si na nich vyzkoušeli základy dotazování.

(Mimochodem, všimněte si, že se dá VALUES v příkazu INSERT vložit najednou více, vloží se tak více položek v jednom dotazu).

Dotazování

Dotaz na data, tedy jejich vyhledání/výběr naleznete v prostředí DB Browseru na panelu Browse Data. Můžete si to zkusit, stačí zadat nějakou hodnotu do nějakého pole Filter. Operátory, které ve vyhledávání hrají velkou roli, si vysvětlíme dále.

DB Browser byl pro nás zpočátku takovou berličkou, ale nyní pro nás již přestává být zajímavý. Budeme ho používat hlavně pro spouštění dotazů a ukazování jejich výsledků.

Základní dotaz pro výběr všech Janů z tabulky by vypadal takto:

SELECT * FROM "uzivatele" WHERE "jmeno" = 'Jan';

Příkaz je asi srozumitelný, ta hvězdička označuje, že chceme vybrat všechny sloupce. Dotaz tedy česky zní: "Vyber všechny sloupce z tabulky uzivatele, kde je jméno Jan".

Výsledek dotazu nám DB Browser zobrazí, měli byste vidět toto:

6    Jan    Novák     1984-11-03    17
35   Jan    Spáčil    1967-05-06    3

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 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 "uzivatele" WHERE "jmeno" = 'Jan';

Výsledek:

Novák     17
Spáčil    3

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.

Stejně jako tomu bylo u DELETE, i zde bude fungovat pouze dotaz:

SELECT * FROM "uzivatele";

Tehdy budou vybráni úplně všichni uživatelé 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" >= '1960-1-1' AND "pocet_clanku" > 5;

Výsledek:

8     Jan          Novák        1984-11-03    17
16    Vítezslav    Churý        1969-06-02    7
17    Pavel        Procházka    1962-07-03    8
27    Jaroslav     Novotný      1980-08-11    8
28    Petr         Dvořák       1982-09-30    18
33    Pavel        Dušín        1991-05-01    9
34    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 "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 nezáleží (hledání je tedy case-insensitive). 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:

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 "uzivatele" 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 "uzivatele" 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.

V následujícím kvízu, Kvíz - Tvorba, mazání, vkládání a výběr dat v SQLite, si vyzkoušíme nabyté zkušenosti z předchozích lekcí.