6. diel - MS-SQL krok za krokom: Výber dát (vyhľadávanie)

V predchádzajúcom cvičení, Riešené úlohy k 1.-5. lekciu MS-SQL, sme si precvičili získané skúsenosti z predchádzajúcich lekcií.

Dnes sa v MS-SQL tutoriále zameriame na tú najkrajšiu časť, 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 dotazov robiť aj zložité výbery dát. Od jednoduchého výberu užívateľa podľa jeho Id (napr. pre zobrazenie detailov v aplikácii) môžeme vyhľadávať užívateľov spĺňajúcich určité vlastnosti, výsledky zoradiť 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 lekciách).

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 užívateľov. Poďme si do našej tabuľky Users vložiť nejaké záznamy, aby bolo s čím pracovať. Niečo som pre nás pripravil. Tabuľku si najskôr vyprázdnite (aby sme mali rovnaké dáta):

TRUNCATE TABLE [Users];

Ďalej spustite nasledujúci SQL dotaz:

INSERT INTO [Users] (
    [FirstName],
    [LastName],
    [BirthDate],
    [NumberOfArticles]
)
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 užívateľov. To by malo stačiť na to, aby sme si na nich vyskúšali základy dopytovania.

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

Dopytovanie

Dotaz na dáta, teda ich vyhľadanie/výber, sme už vlastne videli, keď sme riadok pridávali. Pre rekapituláciu zobrazíte všetky dáta kliknutím pravým tlačidlom na tabuľku a vybraním Show Table Data:

Designer Visual Studia bol pre nás spočiatku takou berličkou, ale teraz pre nás už prestáva byť zaujímavý. Narážame totiž na jeho hranice, jediné, čo tu môžeme nastaviť, je počet riadkov. Do políčka Max Rows zadajte napr. 10 a potvrďte. Na databázu sa zavolá dotaz, ktorý vyberie iba desať prvých položiek. To sa hodí pokiaľ je databáza rozsiahla, aby ste neposielali úplne všetko. Pokiaľ chcete naozaj všetky dáta (a neviete koľko ich je), tak z ponuky zvolíte All.

Na pozadí Visual Studio posiela do databázy samozrejme T-SQL dotaz, ktorý môže vyzerať nasledovne:

SELECT TOP 10 * FROM [Users]

Príkaz je asi celkom zrozumiteľný, TOP 10 hovorí, že chceme 10 riadkov z vrchu (prvých 10) a tá hviezdička označuje, že chceme vybrať všetky stĺpce. Dotaz teda po slovensky znie: "Vyber prvých 10 riadkov a všetky stĺpce z tabuľky Users".

Výsledok dotazu nám Visual Studio zobrazí, mali by ste vidieť takúto tabuľku:

Id	FirstName	LastName	BirthDate	NumberOfArticles
1	John	Smith	1984-11-03	17
2	Thomas	Williams	1942-10-17	6
3	Joseph	Miller	1958-07-10	9
4	Jeanette	Fraser	1963-10-30	7
5	Mead	McFetridge	1950-09-09	5
6	Griselda	Ashelford	1986-11-01	8
7	Terrijo	Chittenden	1972-05-22	3
8	Katlin	Edmands	1988-06-14	5
9	Frannie	Jzhakov	1958-06-16	0
10	Rosemarie	Mansbridge	1978-10-20	4

WHERE

Dosť často potrebujeme získať dáta na základe určitých kritérií. Napríklad budeme hľadať iba užívateľov s menom John. Na tento účel sa používa klauzula WHERE, kde sa udávajú podmienky vo formáte meno stĺpca/operátor/hod­nota. Základných operátorov =, >, <, >=, <= a != určite viete použiť. Zložitejšie si ukážeme ďalej v tejto lekcii. Dotaz pre vyhľadanie Johnov by vyzeral nasledovne:

SELECT * FROM [Users] WHERE [FirstName] = 'John';

Tu sme vypustili TOP 10, aby sme dostali všetky Johny.

Tabuľky majú väčšinou mnoho stĺpcov a väčšinou nás zaujímajú len 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 [LastName], [NumberOfArticles] FROM [Users] WHERE [FirstName] = 'John';

Výsledok:

LastName	NumberOfArticles
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.

Teraz vyberme všetkých užívateľov, narodených od roku 1960 a s počtom článkov vyšším ako päť:

SELECT * FROM [Users] WHERE [BirthDate] >= '1960-1-1' AND [NumberOfArticles] > 5;

Výsledok:

Id	FirstName	LastName	BirthDate	NumberOfArticles
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

Všimnite si v dotaze slová AND. To určuje, že podmienky musia byť splnené obe. Ak by sme chceli, aby sa do výsledku zaradilo všetko, čo spĺňa aspoň jednu podmienku, namiesto AND by sme použili OR. Dotaz by potom po slovensky znel: "Vyber všetky stĺpce užívateľov, ktorí sa narodili od roku 1960 alebo napísali viac ako päť článkov".

Operátorov

V SQL máme aj ďalších operátorov, 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ť dva 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 dotazov s operátorom LIKE. Nájdime priezvisko ľudí začínajúce na "S":

SELECT [LastName] FROM [Users] WHERE [LastName] LIKE 's%';

Text zadáme ako vždy 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:

LastName
Smith
Stroban
Sinnett
Shale

Teraz skúsme nájsť päťpísmenové priezviská, ktoré majú ako druhý znak O. Je všeobecne odporúčané odstraňovať v hodnotách odovzdávaných LIKE biele znaky. Toho docielite funkciami LTRIM() a RTRIM(), ktoré odstraňujú biele znaky zľava a sprava:

SELECT [LastName] FROM [Users] WHERE RTRIM([LastName]) LIKE '_o___';

Výsledok:

LastName
Douty

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žívatelia s týmito menami:

SELECT [FirstName], [LastName] FROM [Users] WHERE [FirstName] IN ('Joseph', 'John', 'Priscilla');

Výsledok:

FirstName	LastName
John	Smith
Joseph	Miller
Priscilla	Kingsnod
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 bežne porovnávať. Nájdime si užívateľov, ktorí sa narodili medzi rokmi 1980 a 1990:

SELECT [FirstName], [LastName], [BirthDate] FROM [Users] WHERE [BirthDate] BETWEEN '1980-1-1' AND '1990-12-31';

Medzi dve hodnoty píšeme AND.

Výsledok:

FirstName	LastName	BirthDate
John	Smith	1984-11-03
Griselda	Ashelford	1986-11-01
Katlin	Edmands	1988-06-14
Helli	Douty	1987-09-05
Cordie	Farryn	1990-12-27
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

Dotaz by sme mohli vylepšiť porovnávaním len roku z daného dátumu pomocou funkcie YEAR():

SELECT [FirstName], [LastName], [BirthDate] FROM [Users] WHERE YEAR([BirthDate]) BETWEEN 1980 AND 1990;

To je pre dnešok všetko. Pri výbere dát zostaneme ešte niekoľko dielov, vlastne väčšinu tohto on-line MS-SQL kurzu.

V nasledujúcom cvičení, Riešené úlohy k 6. lekcii MS-SQL, si precvičíme nadobudnuté skúsenosti z predchádzajúcich lekcií.