Java týden
30 % bodů zdarma na online výuku díky naší Slevové akci!
Pouze tento týden sleva až 80 % na e-learning týkající se Javy.

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

V minulé lekci, MS-SQL krok za krokem: Vkládání a mazání dat v tabulce, jsme si ukázali vkládání a mazání záznamů.

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.

Data ve Visual Studiu

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

Tento výukový obsah pomáhají rozvíjet následující firmy, které dost možná hledají právě tebe!

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 příští lekci, MS-SQL krok za krokem: Řazení, Limit a agregační funkce, se podíváme na řazení a agregační funkce.


 

Předchozí článek
MS-SQL krok za krokem: Vkládání a mazání dat v tabulce
Všechny články v sekci
MS-SQL databáze krok za krokem
Článek pro vás napsal Michal Žůrek - misaz
Avatar
Jak se ti líbí článek?
19 hlasů
Autor se věnuje tvorbě aplikací pro počítače, mobilní telefony, mikroprocesory a tvorbě webových stránek a webových aplikací. Nejraději programuje ve Visual Basicu a TypeScript. Ovládá HTML, CSS, JavaScript, TypeScript, C# a Visual Basic.
Aktivity (6)

 

 

Komentáře
Zobrazit starší komentáře (7)

Avatar
mkub
Redaktor
Avatar
mkub:20.7.2014 20:01

myslis? mimochodom, ja na pridavanie kodu nepotrebujem ani mys... tam, kde chcem hodit kod, tam vlozim znacku, ze dalsi text sa jedna o kod... a vysledok je rovnaky ;)

 
Odpovědět
20.7.2014 20:01
Avatar
Ľubomír Prokopovič:26.8.2014 12:13

Len taka mensia poznamka v databaze je 31 uzivatelov nie 38 :)

Odpovědět
26.8.2014 12:13
"Počítače jsou jako Bůh ve Starém zákoně - hodně příkazů a žádné slitování." Joseph Campbell
Avatar
 
Odpovědět
26.8.2014 12:16
Avatar
Milan Jiroušek:17.7.2017 12:36

Malá oprava. Výběr uživatelů, kteří se narodili mezi lety 1980 a 1990 (včetně) by měl vypadat takto:
SELECT [Jmeno], [Prijmeni], [DatumNarozeni] FROM [Uzivatele] WHERE [DatumNarozeni] BETWEEN '1980-1-1' AND '1990-12-31';
nebo
SELECT [Jmeno], [Prijmeni], [DatumNarozeni] FROM [Uzivatele] WHERE YEAR([DatumNa­rozeni]) BETWEEN 1980 AND 1990;

 
Odpovědět
17.7.2017 12:36
Avatar
David Čápka
Tým ITnetwork
Avatar
Odpovídá na Milan Jiroušek
David Čápka:8.11.2017 20:23

Díky za postřeh, přidám tam i tuto variantu.

Odpovědět
8.11.2017 20:23
Jsem moc rád, že jsi na síti, a přeji ti top IT kariéru, ať jako zaměstnanec nebo podnikatel. Máš na to! :)
Tento výukový obsah pomáhají rozvíjet následující firmy, které dost možná hledají právě tebe!
Avatar
Miloš Etlík:20. února 14:58

Ahoj mám dotaz s jazykem SQL začínám a mám problém s vybráním dat která mám ve formátu viz obrázek. Problém je že se opakujíé data se stejnou časovou známkou potřebuji vybrat data která budou mít jedinečnou časoovou známku a v případě že je více stejné čas. známky vzít ty hodnotu z ostatních sloupců nejvyšší? Muže někdo navést jakým směrem se dát prosím ?

 
Odpovědět
20. února 14:58
Avatar
Odpovídá na Miloš Etlík
Miloš Etlík:20. února 15:07

Takhle to mám ted jedná se o dotaz v programu Ignition

SELECT NSP5_Ignition.NSP5_Machine_Tem­p_ActTemperatu­re_TIMESTAMP AS time,
NSP5_Ignition.NSP5_Machine_Tem­p_ActTemperatu­re_VALUE AS Teplota_v_kabině,
NSP5_Ignition2. NSP5_Machine_Ti­me_Remaining_ti­me_Total_VALUE AS Čas_celkový
FROM NSP5_Ignition2
where NSP5_Ignition.NSP5_Machine_Tem­p_ActTemperatu­re_TIMESTAMP > DATEADD(HOUR,-12,getdate())
GROUP BY NSP5_Ignition.NSP5_Machine_Tem­p_ActTemperatu­re_TIMESTAMP ,
NSP5_Ignition.NSP5_Machine_Tem­p_ActTemperatu­re_VALUE ,
NSP5_Ignition2. NSP5_Machine_Ti­me_Remaining_ti­me_Total_VALUE
order by time asc

 
Odpovědět
20. února 15:07
Avatar
Mouser
Člen
Avatar
Odpovídá na Miloš Etlík
Mouser:21. února 0:09

Ignition neznám, ale tohle se dělá všude stejně:

SELECT NSP5_Ignition2.NSP5_Machine_Temp_ActTemperature_TIMESTAMP AS time,
MAX(NSP5_Ignition2.NSP5_Machine_Temp_ActTemperature_VALUE) AS Teplota_v_kabině,
MAX(NSP5_Ignition2. NSP5_Machine_Time_Remaining_time_Total_VALUE) AS Čas_celkový
FROM NSP5_Ignition2
where NSP5_Ignition2.NSP5_Machine_Temp_ActTemperature_TIMESTAMP > DATEADD(HOUR,-12,getdate())
GROUP BY NSP5_Ignition2.NSP5_Machine_Temp_ActTemperature_TIMESTAMP

Obecně to funguje takhle:

SELECT
        MAX(SloupecA),  -- vezmi maximum pro stejné hodnoty
        MIN(SloupecB),  -- u jiného sloupce nemusím chtít maximum, ale třeba minimum
        SloupecC,
        SloupecD        -- všechny ostatní sloupce, co chci vybrat
FROM MojeTabulka
GROUP BY
        SloupecC,
        SloupecD        -- ty všechny ostatní sloupce, co chci vybrat

Kdyžtak je to popsané v následující lekci. :-)

 
Odpovědět
21. února 0:09
Avatar
Miloš Etlík:21. února 6:45

Ahoj děkuju Ignition je SCADA v průmyslu ale máš pravdu mělo by to být stejné bohužel s max jsem zkoušel taky ale můj problém zůstane u toho že mě dotaz vybere i stejné časové známky Ja bych potřeboval vybrat jen řádky kde není stejná časová známka bohužel Kepware(OPC server) do databáze ukládá hodnoty pokaždé když se změní sledovaný parametr a to se stává i se stejnou časovou známkou. já potřebuji pomocí Ignition data zobrazovat v průmyslu na panelech a stejná časová známka mě dělá problém s objemem dat celé zobrazení je pak pomalé a doba na dotaz hrozně dlouhá.

 
Odpovědět
21. února 6:45
Avatar
Mouser
Člen
Avatar
Odpovídá na Miloš Etlík
Mouser:21. února 13:57

Když to sgrupneš (to je slovo!) podle časové známky, tak by to každou její hodnotu mělo vybrat jen jednou. Koukni na obrázek níž (ukázka z SQL Serveru).

Jedině, že by Ignition používalo nějaký divný dialekt SQL, kde se nepoužívá funkce MAX tímhle způsobem, ale to se mi nezdá.
Anebo mě napadá, že se možná ty časové známky liší v tisícinách vteřiny, ale v náhledu to vypadá, že jsou stejné, protože tam jsou vidět jen celé sekundy.

 
Odpovědět
21. února 13:57
Děláme co je v našich silách, aby byly zdejší diskuze co nejkvalitnější. Proto do nich také mohou přispívat pouze registrovaní členové. Pro zapojení do diskuze se přihlas. Pokud ještě nemáš účet, zaregistruj se, je to zdarma.

Zobrazeno 10 zpráv z 17. Zobrazit vše