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

V minulej lekcii, SQLite - Vkladanie a mazanie dát v tabuľke, sme si ukázali vkladanie a mazanie záznamov.

Dnes sa zameriame na tú najkrajšiu časť, a tou je výber dát. Ide o dopytovanie na dáta, ak chcete, tak 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 dotazoch robiť aj zložité výbery dát. Od jednoduchého výberu používateľa podľa jeho id (napr. pre zobrazenie detailov v aplikácii) môžeme vyhľadávať používateľov spĺňajúcich určité vlastnosti, výsledky radiť 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 dotazov je vždy dobré mať nejaké testovacie dáta, aby sme mali s čím pracovať a nemali tam len 4 používateľov. Poďme si do našej tabuľky users vložiť nejaké záznamy, aby bolo s čím pracovať. Niečo som vám pripravil. Tabuľku si najskôr vyprázdnite (aby sme mali rovnaké dáta):

DELETE FROM "users";

Ďalej spustite nasledujúci SQL dotaz:

INSERT INTO "users" (
        "first_name",
        "last_name",
        "birth_date",
        "article_count"
)
VALUES
    ('John', 'Smith', '1984-11-03', 17),
    ('Thomas', 'Williams', '1942-10-17', 6),
    ('Joseph', 'Miller', '1958-07-10', 9),
    ('Jeanette', 'Fraser', '1963-10-30', 7),
    ('Mead', 'McFetridge', '1950-09-09', 5),
    ('Griselda', 'Ashelford', '1986-11-01', 8),
    ('Terrijo', 'Chittenden', '1972-05-22', 3),
    ('Katlin', 'Edmands', '1988-06-14', 5),
    ('Frannie', 'Jzhakov', '1958-06-16', 0),
    ('Rosemarie', 'Mansbridge', '1978-10-20', 4),
    ('Bessie', 'McLice', '1965-07-31', 2),
    ('Melvin', 'Campey', '1977-01-16', 3),
    ('Read', 'Pane', '1991-10-10', 2),
    ('Michaeline', 'Olivey', '1948-02-08', 0),
    ('Helli', 'Douty', '1987-09-05', 1),
    ('Guy', 'Maris', '1956-05-16', 5),
    ('Cordie', 'Farryn', '1990-12-27', 8),
    ('Antoni', 'Burlingame', '1983-03-24', 1),
    ('Gilligan', 'Durrant', '1950-12-02', 7),
    ('Bambie', 'Goring', '1954-10-24', 14),
    ('Priscilla', 'Kingsnod', '1989-02-22', 5),
    ('Ian', 'Stroban', '1976-12-17', 8),
    ('Cart', 'Sinnett', '1972-07-14', 17),
    ('Lorna', 'Britian', '1954-02-20', 8),
    ('Any', 'Warburton', '1992-05-19', 4),
    ('Marjorie', 'Bohlje', '1978-03-11', 6),
    ('John', 'Hyam', '1964-07-06', 8),
    ('Bud', 'Dubarry', '1989-09-07', 4),
    ('Tadio', 'Arent', '1948-04-30', 5),
    ('Lenard', 'Weathers', '1986-07-12', 9),
    ('Abigale', 'Shale', '1982-06-20', 7);

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

(Mimochodom, všimnite si, že sa dá VALUES v príkaze INSERT vložiť naraz viac, vloží sa tak viac položiek v jednom dotaze).

Dopytovanie

Dotaz na dáta, teda ich vyhľadanie/výber nájdete v prostredí DB Browsera na paneli Browse Data. Môžete si to skúsiť, stačí zadať nejakú hodnotu do nejakého poľa Filter. Operátory, ktoré vo vyhľadávaní hrajú veľkú úlohu, si vysvetlíme ďalej:

DB Browser bol pre nás spočiatku takou berličkou, ale teraz pre nás už prestáva byť zaujímavý. Budeme ho používať hlavne na spůštěnie dotazov a ukazovanie ich výsledkov.

Základný dotaz pre výber všetkých Johnov z tabuľky by vyzeral takto:

SELECT * FROM "users" WHERE "first_name" = 'John';

Príkaz je asi zrozumiteľný, tá hviezdička označuje, že chceme vybrať všetky stĺpce. Dotaz teda po slovensky znie: "Vyber všetky stĺpce z tabuľky používateľa, kde je meno John".

Výsledok dotazu nám DB Browser zobrazí, mali by ste vidieť toto:

1    John    Smith     1984-11-03    17
27   John    Hyam      1964-07-06    8

Tabuľky majú väčšinou veľa stĺpcov a väčšinou nás zaujímajú iba 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ť tie stĺpce, ktoré chceme. Dajme tomu, že budeme chcieť len priezvisko ľudí, čo sa volajú John a ešte počet ich článkov. Dotaz upravíme:

SELECT "last_name", "article_count" FROM "users" WHERE "first_name" = 'John';

Výsledok:

Smith     17
Hyam      8

Naozaj nebuďte leniví a pokiaľ nepotrebujete takmer všetky stĺpce, vymenujte v príkazu SELECT tie, ktorých hodnoty vás v tej chvíli zaujímajú. Vždy sa snažte podmienku obmedziť čo najviac už na úrovni databázy, nie že si vyťaháte 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ĺpcami, ktoré v dotaze používame. Môžeme teda vyhľadávať podľa desiatich stĺpcov, ale vrátiť iba jeden.

Rovnako ako to bolo u DELETE, aj tu bude fungovať iba dotaz:

SELECT * FROM "users";

Vtedy budú vybraní úplne všetci používatelia z tabuľky.

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

SELECT * FROM "users" WHERE "birth_date" >= '1960-1-1' AND "article_count" > 5;

Výsledok:

1   John        Smith       1984-11-03  17
4   Jeanette    Fraser      1963-10-30  7
6   Griselda    Ashelford   1986-11-01  8
17  Cordie      Farryn      1990-12-27  8
22  Ian         Stroban     1976-12-17  8
23  Cart        Sinnett     1972-07-14  17
26  Marjorie    Bohlje      1978-03-11  6
27  John        Hyam        1964-07-06  8
30  Lenard      Weathers    1986-07-12  9
31  Abigale     Shale       1982-06-20  7

Operátorov

Základných operátorov =, >, <, >=, <=, != 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), iba môžeme používať 2 zástupné znaky:

• % (percento) označuje ľubovoľný počet ľubovoľných znakov.
• _ (podčiarknutie) 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 "last_name" FROM "users" WHERE "last_name" LIKE 's%';

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

Smith
Stroban
Sinnett
Shale

Teraz skúsme nájsť šesťpísmenné priezviská, ktoré majú ako 2. znak O:

SELECT "last_name" FROM "users" WHERE "last_name" LIKE '_o____';

Výsledok:

Goring
Bohlje

Asi už tušíte, ako LIKE funguje. Použitie je možné 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 zoznam mien a vyhľadajme užívateľa s týmito menami:

SELECT "first_name", "last_name" FROM "users" WHERE "first_name" IN ('Melvin', 'John', 'Marjorie');

Výsledok:

John        Smith
Melvin      Campey
Marjorie    Bohlje
John        Hyam

Operátor IN sa používa ešte pri tzv. poddotazoch, 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 "first_name", "last_name", "birth_date" FROM "users" WHERE "birth_date" BETWEEN '1980-1-1' AND '1990-1-1';

Medzi 2 hodnoty píšeme AND.

Výsledok:

John        Smith       1984-11-03
Griselda    Ashelford   1986-11-01
Katlin      Edmands     1988-06-14
Helli       Douty       1987-09-05
Antoni      Burlingame  1983-03-24
Priscilla   Kingsnod    1989-02-22
Bud         Dubarry     1989-09-07
Lenard      Weathers    1986-07-12
Abigale     Shale       1982-06-20

To je pre dnešok všetko. Pri výbere dát zostaneme ešte niekoľko dielov, vlastne väčšinu tohto seriálu.

V nasledujúcom kvíze, Kvíz - Tvorba, mazanie, vkladanie a výber dát v SQLite, si vyskúšame nadobudnuté skúsenosti z predchádzajúcich lekcií.