Lekce 6 - MS-SQL krok za krokem: Výběr dat (vyhledávání)

V předešlém cvičení, Řešené úlohy k 1.-5. lekci MS-SQL, jsme si procvičili nabyté zkušenosti z předchozích lekcí.

Dnes se v MS-SQL tutoriálu 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říklad 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 lekcích).

Testovací data

Vrátíme se opět k naší jednoduché databázi s tabulkou Uzivatele. Před zkoušením dotazů je vždy dobré mít k dispozici nějaká testovací data, abychom měli s čím pracovat a neměli tam jen čtyři uživatele. Pojďme si tedy do naší tabulky Uzivatele vložit nějaké záznamy. Něco jsem pro nás připravil.

Tabulku si nejprve vyprázdníme, abychom měli stejná data a stejné výsledky. Otevřeme si proto okno pro vykonání T-SQL skriptu – v okně SQL Server Object Explorer klikneme na databázi pravým tlačítkem a vybereme New Query...:

Následně na naší tabulce Uzivatele spustíme příkaz TRUNCATE TABLE:

TRUNCATE TABLE [Uzivatele];

A nakonec spustíme následující příkaz pro vložení nových záznamů:

INSERT INTO [Uzivatele] (
    [Jmeno],
    [Prijmeni],
    [DatumNarozeni],
    [PocetClanku]
)
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ítězslav', '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 31 uživatelů. To by mělo stačit k tomu, abychom si na nich vyzkoušeli základy dotazování.

Dotazování

Dotaz na záznamy, tedy jejich vyhledání/výběr, jsme již vlastně viděli, když jsme záznamy poprvé přidávali. Pro rekapitulaci zobrazíme všechny záznamy kliknutím pravým tlačítkem na tabulku v SQL Server Object Explorer a vybráním View Data:

Návrhář Visual Studia byl pro nás zpočátku takovou berličkou, ale nyní pro nás již přestává být zajímavý. Narážíme totiž na jeho hranice, jediné, co zde můžeme nastavit, je počet řádků. Do políčka Max Rows zadejme například hodnotu 10 a potvrďme:

Na databázi se zavolá dotaz, který vybere pouze 10 prvních položek. To se hodí pokud je databáze rozsáhlá, aby se neposílalo úplně všechno. Pokud chceme opravdu všechna data (a nevíme kolik jich je), tak z nabídky zvolíme speciální hodnotu All.

Visual Studio nezobrazuje ve výsledcích veškerou diakritiku, což je normální a nemá to na data žádný vliv.

Příkaz SELECT

Na pozadí Visual Studio posílá do databáze samozřejmě T-SQL dotaz, který může vypadat následovně:

SELECT TOP 10 * FROM [Uzivatele];

Dotaz je asi docela srozumitelný. Používáme zde příkaz SELECT, který slouží k výběru záznamů z tabulky. TOP 10 říká, že chceme 10 řádků od vrchu (prvních 10) a ta hvězdička označuje, že chceme vybrat všechny sloupce. Dotaz tedy česky zní: "Vyber prvních 10 řádků a všechny sloupce z tabulky Uzivatele".

Pokud si daný dotaz zavoláme na naší databázi, uvidíme stejnou tabulku jako při použití funkce View Data a Max Rows:

Id	Jmeno	Prijmeni	DatumNarozeni	PocetClanku
1	Jan	Novák	1984-11-03	17
2	Tomáš	Marný	1942-10-17	12
3	Josef	Nový	1958-07-10	5
4	Alfons	Svoboda	1935-05-15	6
5	Ludmila	Dvoráková	1967-04-17	2
6	Petr	Cerný	1995-02-20	1
7	Vladimír	Pokorný	1984-04-18	1
8	Ondrej	Bohatý	1973-05-14	3
9	Vítezslav	Churý	1969-06-02	7
10	Pavel	Procházka	1962-07-03	8

Klauzule WHERE

Dost často potřebujeme získat data na základě určitých kritérií. Například budeme hledat pouze Jany. K tomuto účelu se používá klauzule WHERE, kde se udávají podmínky. Jednoduché podmínky zadáváme ve formátu sloupec operátor hodnota. Mezi základní operátory patří:

• = – je rovno,
• > – je větší,
• < – je menší,
• >= – je větší nebo rovno,
• <= – je menší nebo rovno,
• != – není rovno.

Složitější operátory si ukážeme dále v této lekci. Dotaz pro vyhledání všech Janů by tedy vypadal následovně:

SELECT * FROM [Uzivatele] WHERE [Jmeno] = 'Jan';

Vypustili jsme TOP 10, abychom dostali všechny Jany.

Specifikace sloupců

Tabulky mají většinou mnoho 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 jen ty sloupce, které chceme. Dejme tomu, že budeme chtít jen příjmení lidí, kteří se jmenují Jan, a ještě počet jejich článků. Dotaz upravíme:

SELECT [Prijmeni], [PocetClanku] FROM [Uzivatele] WHERE [Jmeno] = 'Jan';

Místo hvězdičky * za klíčovým slovem SELECT jsme vyjmenovali požadované sloupce Prijmeni a PocetClanku. 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.

Výsledek:

Prijmeni	PocetClanku
Novák	17
Spáčil	3

Opravdu nebuďme líní a pokud nepotřebujeme téměř všechny sloupce, vyjmenujme v příkazu SELECT ty, jejichž hodnoty vás v tu chvíli zajímají. Vždy se snažme podmínku omezit co nejvíce již na úrovni databáze, ne že si vytaháme celou tabulku do aplikace a tam si ji vyfiltrujeme. Řekněme, že by vaše aplikace poté nebyla úplně rychlá

Bez klauzule WHERE

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

SELECT * FROM [Uzivatele];

Tehdy budou vybráni úplně všichni uživatelé z tabulky.

Složitější podmínky

Teď vyberme všechny uživatele, narozené od roku 1960 a s počtem článků vyšším než 5:

SELECT * FROM [Uzivatele] WHERE [DatumNarozeni] >= '1960-1-1' AND [PocetClanku] > 5;

Výsledek:

Id	Jmeno	Prijmeni	DatumNarozeni	PocetClanku
1	Jan	Novák	1984-11-03	17
9	Vítezslav	Churý	1969-06-02	7
10	Pavel	Procházka	1962-07-03	8
20	Jaroslav	Novotný	1980-08-11	8
21	Petr	Dvořák	1982-09-30	18
26	Pavel	Dušín	1991-05-01	9
27	Otakar	Kovář	1992-12-17	9

Všimněme si v dotazu logického operátoru AND ("a zároveň"). To určuje, že podmínky musí být splněny obě. Pokud bychom chtěli, aby se do výsledku zařadilo vše, co splňuje alespoň jednu podmínku, místo operátoru AND bychom použili logický operátor OR ("nebo"):

SELECT * FROM [Uzivatele] WHERE [DatumNarozeni] >= '1960-1-1' OR [PocetClanku] > 5;

Dotaz by poté česky zněl: "Vyber všechny sloupce uživatelů, kteří se narodili od roku 1960 nebo napsali více než 5 článků".

Priorita logických operátorů

Zkusme vybrat všechny uživatele narozené od roku 1970, kteří napsali 2 nebo 8 článků. S dosavadními znalostmi bychom dotaz napsali třeba takto:

SELECT *
FROM [Uzivatele]
WHERE [DatumNarozeni] >= '1970-1-1' AND [PocetClanku] = 2 OR [PocetClanku] = 8;

Dotaz však nevrací požadované výsledky:

Id	Jmeno	Prijmeni	DatumNarozeni	PocetClanku
10	Pavel	Procházka	1962-07-03	8
20	Jaroslav	Novotný	1980-08-11	8
25	Vera	Svobodová	1983-03-02	2

Pavel Procházka se totiž narodil před rokem 1970 a neměl by se tak ve výsledku vyskytovat. Problém nám zde činí to, že operátor AND má při vyhodnocování podmínky vyšší prioritu než operátor OR. Ve skutečnosti tedy vybíráme uživatele, kteří splňují aspoň jednu z následujících podmínek:

• "narodili se od roku 1970 a napsali 2 články",
• "napsali 8 článků".

Aby dotaz vracel správné výsledky musíme využít závorky () pro změnu priority vyhodnocování logických operátorů:

SELECT *
FROM [Uzivatele]
WHERE [DatumNarozeni] >= '1970-1-1' AND ([PocetClanku] = 2 OR [PocetClanku] = 8);

Výsledek:

Id	Jmeno	Prijmeni	DatumNarozeni	PocetClanku
20	Jaroslav	Novotný	1980-08-11	8
25	Věra	Svobodová	1983-03-02	2

Operátory

V SQL máme i další operátory, ř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), navíc ale můžeme používat dva 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%';

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

Prijmeni
Svoboda
Svobodová
Spáčil

Nyní zkusme najít pětipísmenná příjmení, která mají jako druhý znak "O". Je obecně doporučováno odstraňovat v hodnotách předávaných operátoru LIKE bílé znaky. Toho docílíte funkcemi LTRIM() a RTRIM(), které odstraňují bílé znaky zleva a zprava:

SELECT [Prijmeni] FROM [Uzivatele] WHERE RTRIM([Prijmeni]) LIKE '_o___';

Výsledek:

Prijmeni
Novák
Horák
Kovář

Asi již tušíte, jak LIKE funguje. Použití lze vymyslet mnoho, většinou se používá s procenty na obou stranách pro fulltextové vyhledávání (například 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:

Jmeno	Prijmeni
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 běžně porovnávat. Najděme si uživatele, kteří se narodili mezi lety 1980 a 1990:

SELECT [Jmeno], [Prijmeni], [DatumNarozeni] FROM [Uzivatele] WHERE [DatumNarozeni] BETWEEN '1980-1-1' AND '1990-1-1';

Mezi dvě mezní hodnoty píšeme klíčové slovo AND.

Výsledek:

Jmeno	Prijmeni	DatumNarozeni
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

Dotaz bychom mohli vylepšit porovnáváním jen roku z daného data pomocí funkce YEAR():

SELECT [Jmeno], [Prijmeni], [DatumNarozeni] FROM [Uzivatele] WHERE YEAR([DatumNarozeni]) BETWEEN 1980 AND 1990;

To je pro dnešek vše. U výběru dat zůstaneme ještě několik dílů.

V následujícím cvičení, Řešené úlohy k 6. lekci MS-SQL, si procvičíme nabyté zkušenosti z předchozích lekcí.