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ř. 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

Před zkoušením dotazů je vždy dobré mít nějaká testovací data, abychom měli s čím pracovat a neměli tam jen 4 uživatele. Pojďme si do naší tabulky Uzivatele vložit nějaké záznamy, aby bylo s čím pracovat. Něco jsem pro nás připravil. Tabulku si nejprve vyprázdněte (abychom měli stejná data):

DELETE FROM [Uzivatele];

Dále spusťte následující SQL dotaz:

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í.

(Mimochodem, všimněte si, že se dá VALUES v příkazu INSERT vložit najednou více, vloží se tak více položek v jednom dotazu).

Dotazování

Dotaz na data, tedy jejich vyhledání/výběr, jsme již vlastně viděli, když jsme řádek přidávali. Pro rekapitulaci zobrazíte všechna data kliknutím pravým tlačítkem na tabulku a vybráním Show Table Data:

Designer 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 zadejte např. 10 a potvrďte. Na databázi se zavolá dotaz, který vybere pouze 10 prvních položek. To se hodí pokud je databáze rozsáhlá, abyste neposílali úplně všechno. Pokud chcete opravdu všechna data (a nevíte kolik jich je), tak z nabídky zvolíte All.

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]

Příkaz je asi docela srozumitelný, TOP 10 říká, že chceme 10 řádku z 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".

Výsledek dotazu nám Visual Studio zobrazí, měli byste vidět takovouto tabulku:

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

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

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 ve formátu sloupec operátor hodnota. Základní operátory =, >, <, >=, <= a != určitě umíte použít. Složitější si ukážeme dále v této lekci. Dotaz pro vyhledání Janů by vypadal následovně.

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

Zde jsme vypustili TOP 10, abychom dostali všechny Jany.

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

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

Výsledek:

Prijmeni	PocetClanku
Novák	17
Spáčil	3

Opravdu nebuďte líní a pokud nepotřebujete téměř všechny sloupce, vyjmenujte v SELECTu ty, jejichž hodnoty vás v tu chvíli zajímají. Vždy se snažte podmínku omezit co nejvíce již na úrovni databáze, ne že si vytaháte celou tabulku do aplikace a tam si ji vyfiltrujete. Řekněme, že by vaše aplikace poté nebyla úplně rychlá 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.

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

SELECT * FROM [Uzivatele];

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

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
8	Jan	Novák	1984-11-03	17
16	Vítezslav	Churý	1969-06-02	7
17	Pavel	Procházka	1962-07-03	8
27	Jaroslav	Novotný	1980-08-11	8
28	Petr	Dvořák	1982-09-30	18
33	Pavel	Dušín	1991-05-01	9
34	Otakar	Kovář	1992-12-17	9

Všimněte si v dotazu slova AND. 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 AND bychom použili OR. 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ů".

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), pouze můžeme používat 2 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 2. znak "O". Je obecně doporučováno odstraňovat v hodnotách předávaných 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ř. 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živatelé 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 2 hodnoty píšeme 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ů, vlastně většinu tohoto on-line MS-SQL kurzu.

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í.