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. lekcii MS-SQL, sme si precvičili získané skúsenosti z predchádzajúcich lekcií.

Dnes sa v MS-SQL tutoriáli zameriame na tú najkrajšiu časť, a tou je výber dát. Ide o dotazovanie 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 používateľa podľa jeho Id (napríklad na 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 lekciách).

Testovacie dáta

Vrátime sa opäť k našej jednoduchej databáze s tabuľkou Pouzivatelia. Pred skúšaním dotazov je vždy dobré mať k dispozícii nejaké testovacie dáta, aby sme mali s čím pracovať a nemali tam iba štyroch používateľov. Poďme si teda do našej tabuľky Pouzivatelia vložiť nejaké záznamy. Niečo som pre nás pripravil.

Tabuľku si najprv vyprázdnime, aby sme mali rovnaké dáta a rovnaké výsledky. Otvoríme si preto okno na vykonanie T-SQL skriptu – v okne SQL Server Object Explorer klikneme na databázu pravým tlačidlom a vyberieme New Query...:

Následne na našej tabuľke Pouzivatelia spustíme príkaz TRUNCATE TABLE:

TRUNCATE TABLE [Pouzivatelia];

A nakoniec spustíme nasledujúci príkaz na vloženie nových záznamov:

INSERT INTO [Pouzivatelia] (
    [Meno],
    [Priezvisko],
    [DatumNarodenia],
    [PocetClankov]
)
VALUES
('Ján',  'Kováč',  '1984-11-03', 17),
('Tomáš', 'Horváth', '1942-10-17', 12),
('Jozef', 'Tóth', '1958-7-10', 5),
('Alfonz', 'Sloboda', '1935-5-15', 6),
('Ľudmila', 'Dvorská', '1967-4-17', 2),
('Peter', 'Čierny', '1995-2-20', 1),
('Vladimír', 'Pokorný', '1984-4-18', 1),
('Ondrej', 'Bohatý', '1973-5-14', 3),
('Víťazoslav', 'Chudý', '1969-6-2', 7),
('Pavol', 'Kráľ', '1962-7-3', 8),
('Matej', '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', 'Krajčí', '1956-3-7', 0),
('Lenka', 'Nemcová', '1954-2-11', 5),
('Viera', 'Marková', '1978-1-21', 3),
('Eva', 'Kučerová', '1949-7-26', 12),
('Lucia', 'Novotná', '1973-7-28', 4),
('Jaroslav', 'Novotný', '1980-8-11', 8),
('Peter', 'Dvorský', '1982-9-30', 18),
('Juraj', 'Veselý', '1961-1-15', 2),
('Martina', 'Krajčí', '1950-8-29', 4),
('Mária', 'Čierna', '1974-2-26', 5),
('Viera', 'Slobodová', '1983-3-2', 2),
('Pavol', 'Dušín', '1991-5-1', 9),
('Otakar', 'Polák', '1992-12-17', 9),
('Katarína', 'Kováčová', '1956-11-15', 4),
('Václav', 'Baláž', '1953-10-20', 6),
('Ján', 'Spáčil', '1967-5-6', 3),
('Zdenko', 'Malačka', '1946-3-10', 6);

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

Dotazovanie

Dotaz na záznamy, teda ich vyhľadanie/výber, sme už vlastne videli, keď sme záznamy prvýkrát pridávali. Na rekapituláciu zobrazíme všetky záznamy kliknutím pravým tlačidlom na tabuľku v SQL Server Object Explorer a vybratím View Data:

Návrhár Visual Studia bol pre nás spočiatku takou barličkou, ale teraz už pre nás 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 zadajme napríklad hodnotu 10 a potvrďme:

Na databáze sa zavolá dotaz, ktorý vyberie iba 10 prvých položiek. To sa hodí, ak je databáza rozsiahla, aby sa neposielalo úplne všetko. Ak chceme naozaj všetky dáta (a nevieme, koľko ich je), z ponuky zvolíme špeciálnu hodnotu All.

Visual Studio nezobrazuje vo výsledkoch všetku diakritiku, čo je normálne a nemá to na dáta žiadny vplyv.

Príkaz SELECT

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

SELECT TOP 10 * FROM [Pouzivatelia];

Dotaz je asi celkom zrozumiteľný. Používame tu príkaz SELECT, ktorý slúži na výber záznamov z tabuľky. TOP 10 hovorí, že chceme 10 riadkov zhora (prvých 10) a 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 Pouzivatelia“.

Ak si daný dotaz zavoláme na našej databáze, uvidíme rovnakú tabuľku ako pri použití funkcie View Data a Max Rows:

Id	Meno	Priezvisko	DatumNarodenia	PocetClankov
1	Ján	Kováč	1984-11-03	17
2	Tomáš	Horváth	1942-10-17	12
3	Jozef	Tóth	1958-07-10	5
4	Alfonz	Sloboda	1935-05-15	6
5	Ľudmila	Dvorská	1967-04-17	2
6	Peter	Čierny	1995-02-20	1
7	Vladimír	Pokorný	1984-04-18	1
8	Ondrej	Bohatý	1973-05-14	3
9	Víťazoslav	Chudý	1969-06-02	7
10	Pavol	Kráľ	1962-07-03	8

Klauzula WHERE

Dosť často potrebujeme získať dáta na základe určitých kritérií. Napríklad budeme hľadať iba Jánov. Na tento účel sa používa klauzula WHERE, kde sa uvádzajú podmienky. Jednoduché podmienky zadávame vo formáte stĺpec operátor hodnota. Medzi základné operátory patria:

• = – rovná sa,
• > – je väčší,
• < – je menší,
• >= – je väčší alebo rovný,
• <= – je menší alebo rovný,
• != – nerovná sa.

Zložitejšie operátory si ukážeme ďalej v tejto lekcii. Dotaz na vyhľadanie všetkých Jánov by teda vyzeral nasledovne:

SELECT * FROM [Pouzivatelia] WHERE [Meno] = 'Ján';

Vypustili sme TOP 10, aby sme dostali všetkých Jánov.

Špecifikácia stĺpcov

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

SELECT [Priezvisko], [PocetClankov] FROM [Pouzivatelia] WHERE [Meno] = 'Ján';

Namiesto hviezdičky * za kľúčovým slovom SELECT sme vymenovali požadované stĺpce Priezvisko a PocetClankov. 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.

Výsledok:

Priezvisko	PocetClankov
Kováč	17
Spáčil	3

Naozaj nebuďme leniví a ak nepotrebujeme takmer všetky stĺpce, vymenujme v príkaze SELECT tie, ktorých hodnoty vás v danej chvíli zaujímajú. Vždy sa snažme podmienku obmedziť čo najviac už na úrovni databázy, nie že si vytiahneme celú tabuľku do aplikácie a tam si ju vyfiltrujeme. Povedzme, že by vaša aplikácia potom nebola úplne rýchla

Bez klauzuly WHERE

Rovnako ako to bolo pri DELETE alebo UPDATE, aj tu bude fungovať iba dotaz:

SELECT * FROM [Pouzivatelia];

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

Zložitejšie podmienky

Teraz vyberme všetkých používateľov narodených od roku 1960 a s počtom článkov vyšším než 5:

SELECT * FROM [Pouzivatelia] WHERE [DatumNarodenia] >= '1960-1-1' AND [PocetClankov] > 5;

Výsledok:

Id	Meno	Priezvisko	DatumNarodenia	PocetClankov
1	Ján	Kováč	1984-11-03	17
9	Víťazoslav	Chudý	1969-06-02	7
10	Pavol	Kráľ	1962-07-03	8
20	Jaroslav	Novotný	1980-08-11	8
21	Peter	Dvorský	1982-09-30	18
26	Pavol	Dušín	1991-05-01	9
27	Otakar	Polák	1992-12-17	9

Všimnime si v dotaze logický operátor AND („a zároveň“). Ten 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 operátora AND by sme použili logický operátor OR („alebo“):

SELECT * FROM [Pouzivatelia] WHERE [DatumNarodenia] >= '1960-1-1' OR [PocetClankov] > 5;

Dotaz by potom po slovensky znel: „Vyber všetky stĺpce používateľov, ktorí sa narodili od roku 1960 alebo napísali viac ako 5 článkov“.

Priorita logických operátorov

Skúsme vybrať všetkých používateľov narodených od roku 1970, ktorí napísali 2 alebo 8 článkov. S doterajšími znalosťami by sme dotaz napísali napríklad takto:

SELECT *
FROM [Pouzivatelia]
WHERE [DatumNarodenia] >= '1970-1-1' AND [PocetClankov] = 2 OR [PocetClankov] = 8;

Dotaz však nevracia požadované výsledky:

Id	Meno	Priezvisko	DatumNarodenia	PocetClankov
10	Pavol	Kráľ	1962-07-03	8
20	Jaroslav	Novotný	1980-08-11	8
25	Viera	Slobodová	1983-03-02	2

Pavol Kráľ sa totiž narodil pred rokom 1970 a nemal by sa tak vo výsledku vyskytovať. Problém nám tu spôsobuje to, že operátor AND má pri vyhodnocovaní podmienky vyššiu prioritu než operátor OR. V skutočnosti teda vyberáme používateľov, ktorí spĺňajú aspoň jednu z nasledujúcich podmienok:

• „narodili sa od roku 1970 a napísali 2 články“,
• „napísali 8 článkov“.

Aby dotaz vracal správne výsledky, musíme využiť zátvorky () na zmenu priority vyhodnocovania logických operátorov:

SELECT *
FROM [Pouzivatelia]
WHERE [DatumNarodenia] >= '1970-1-1' AND ([PocetClankov] = 2 OR [PocetClankov] = 8);

Výsledok:

Id	Meno	Priezvisko	DatumNarodenia	PocetClankov
20	Jaroslav	Novotný	1980-08-11	8
25	Viera	Slobodová	1983-03-02	2

Operátory

V SQL máme aj ďalšie operátory, povedzme si o LIKE, IN a BETWEEN.

LIKE

LIKE umožňuje vyhľadávať textové hodnoty iba podľa časti textu. Funguje podobne ako operátor = (rovná sa), navyše však môžeme používať dva zástupné znaky:

• % (percento) označuje ľubovoľný počet ľubovoľných znakov.
• _ (podčiarkovník) označuje jeden ľubovoľný znak.

Poďme si vyskúšať niekoľko dotazov s operátorom LIKE. Nájdime priezviská ľudí začínajúce na „S“:

SELECT [Priezvisko] FROM [Pouzivatelia] WHERE [Priezvisko] 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:

Priezvisko
Sloboda
Slobodová
Spáčil

Teraz skúsme nájsť päťpísmenové priezviská, ktoré majú ako druhý znak „O“. Vo všeobecnosti sa odporúča odstraňovať v hodnotách odovzdávaných operátoru LIKE biele znaky. To dosiahnete funkciami LTRIM() a RTRIM(), ktoré odstraňujú biele znaky zľava a sprava:

SELECT [Priezvisko] FROM [Pouzivatelia] WHERE RTRIM([Priezvisko]) LIKE '_o___';

Výsledok:

Priezvisko
Kováč
Horák
Polák

Asi už tušíte, ako LIKE funguje. Použití možno vymyslieť veľa, väčšinou sa používa s percentami na oboch stranách na fulltextové vyhľadávanie (napríklad slova v texte článku).

IN

IN umožňuje vyhľadávať pomocou výpočtu prvkov. Urobme si teda výpočet mien a vyhľadajme používateľov s týmito menami:

SELECT [Meno], [Priezvisko] FROM [Pouzivatelia] WHERE [Meno] IN ('Peter', 'Ján', 'Katarína');

Výsledok:

Meno	Priezvisko
Ján	Kováč
Peter	Čierny
Peter	Dvorský
Katarína	Kováčová
Ján	Spáčil

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 používateľov, ktorí sa narodili medzi rokmi 1980 a 1990:

SELECT [Meno], [Priezvisko], [DatumNarodenia] FROM [Pouzivatelia] WHERE [DatumNarodenia] BETWEEN '1980-1-1' AND '1990-1-1';

Medzi dve medzné hodnoty píšeme kľúčové slovo AND.

Výsledok:

Meno	Priezvisko	DatumNarodenia
Ján	Kováč	1984-11-03
Vladimír	Pokorný	1984-04-18
Jaroslav	Novotný	1980-08-11
Peter	Dvorský	1982-09-30
Viera	Slobodová	1983-03-02

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

SELECT [Meno], [Priezvisko], [DatumNarodenia] FROM [Pouzivatelia] WHERE YEAR([DatumNarodenia]) BETWEEN 1980 AND 1990;

To je na dnes všetko. Pri výbere dát zostaneme ešte niekoľko dielov.

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