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ť 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 otázok 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ť otázky do seba (o tom až v ďalších lekciách ).
Testovacie dáta
Pred skúšaním otázok 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):
DELETE FROM [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-7-10', 9), ('Jeanette','Fraser', '10/30/1963', 7), ('Mead', 'McFetridge', '9/9/1950', 5), ('Griselda', 'Ashelford', '11/1/1986', 8), ('Terrijo', 'Chittenden', '5/22/1972', 3), ('Katlin', 'Edmands', '6/14/1988', 5), ('Frannie', 'Jzhakov', '6/16/1958', 0), ('Rosemarie', 'Mansbridge', '10/20/1978', 4), ('Bessie', 'McLice', '7/31/1965', 2), ('Melvin', 'Campey', '1/16/1977', 3), ('Read', 'Pane', '10/10/1991', 2), ('Michaeline', 'Olivey', '2/8/1948', 0), ('Helli', 'Douty', '9/5/1987', 1), ('Guy', 'Maris', '5/16/1956', 5), ('Cordie', 'Farryn', '12/27/1990', 8), ('Antoni', 'Burlingame', '3/24/1983', 1), ('Gilligan', 'Durrant', '12/2/1950', 7), ('Bambie', 'Goring', '10/24/1954', 14), ('Priscilla', 'Kingsnod', '2/22/1989', 5), ('Ian', 'Stroban', '12/17/1976', 8), ('Cart', 'Sinnett', '7/14/1972', 17), ('Lorna', 'Britian', '2/20/1954', 8), ('Any', 'Warburton', '5/19/1992', 4), ('Marjorie', 'Bohlje', '3/11/1978', 6), ('John', 'Hyam', '7/6/1964', 8), ('Bud', 'Dubarry', '9/7/1989', 4), ('Tadio', 'Arent', '4/30/1948', 5), ('Lenard', 'Weathers', '7/12/1986', 9), ('Abigale', 'Shale', '6/20/1982', 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
Otázku 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 10 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. Otázka teda po slovensky znie: "Vyber prvých 10 riadkov a
všetky stĺpce z tabuľky Používateľa".
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 | 1989-02-01 | 6 |
| 3 | Joseph | Miller | 1972-12-20 | 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 |
Pozn.: Visual Studio nezobrazuje vo výsledkoch všetku diakritiku, čo je normálne a nemá to na dáta žiadny vplyv.
WHERE
Dosť často potrebujeme získať dáta na základe určitých kritérií.
Napríklad budeme hľadať iba Jany. Na tento účel sa používa klauzula
WHERE, kde sa udávajú podmienky vo formáte
sloupec operátor hodnota. Základných operátorov =,
>, <, >=, <= a
!= určite viete použiť. Zložitejšie si ukážeme ďalej v
tejto lekcii. Otázka pre vyhľadanie Jánov by vyzerala 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ú Ján, a ešte počet ich článkov. Otázku 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 SELECTe 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
otázka:
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 as počtom článkov vyšším ako 5:
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 |
| 2 | Thomas | Williams | 1989-02-01 | 6 |
| 3 | Joseph | Miller | 1972-12-20 | 9 |
| 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. Otázka by potom po slovensky znel: "Vyber všetky stĺpce
užívateľov, ktorí sa narodili od roku 1960 alebo napísali viac ako 5
č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ť 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 [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 2. 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 '1985-1-1' AND '1990-1-1';
Medzi 2 hodnoty píšeme AND.
Výsledok:
| FirstName | LastName | BirthDate |
|---|---|---|
| Thomas | Williams | 1989-02-01 |
| Griselda | Ashelford | 1986-11-01 |
| Katlin | Edmands | 1988-06-14 |
| Helli | Douty | 1987-09-05 |
| Priscilla | Kingsnod | 1989-02-22 |
| Bud | Dubarry | 1989-09-07 |
| Lenard | Weathers | 1986-07-12 |
Otázku 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 1985 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í.
