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

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

V dnešním MySQL tutoriálu se zaměříme na tu nejhezčí část práce s databází a tou je výběr dat. Jedná se o dotazování na data, jinými slovy vyhledávání dat v tabulce.

Vyhledávání 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. 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í. Je možné dokonce do dotazu zapojit více tabulek, různé funkce nebo skládat dotazy do sebe.

Příprava testovacích dat

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 v tabulce jen čtyři uživatele. Pojďme si do naší tabulky uzivatele vložit více záznamů. Původní tabulku si nejprve vyprázdníme, abychom v ní měli stejná data:

TRUNCATE TABLE `uzivatele`;

Dále spustíme následující SQL příkaz:

INSERT INTO `uzivatele` (
    `jmeno`,
    `prijmeni`,
    `datum_narozeni`,
    `pocet_clanku`
)
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ítezslav', '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í.

Už víme, že můžeme za klíčovým slovem VALUES uvést více záznamů a nemusíme tedy pro každého uživatele psát vlastní dotaz.

Dotazování

Jednoduchý dotaz na data, tedy na jejich vyhledání nebo výběr můžeme v prostředí phpMyAdmin vytvořit v záložce Vyhledávání v horní liště. Otevřeme jím následující okno:

Zde stačí zadat nějakou hodnotu do sloupce Hodnota. Pokud v tomto sloupci vyplníme polí více, bude se hledat podle více hodnot. Operátory si vysvětlíme vzápětí.

Nástroj phpMyAdmin byl pro nás zpočátku takovou berličkou, ale nyní pro nás již přestává být zajímavý. Budeme ho používat hlavně pro spouštění dotazů a ukazování jejich výsledků.

Základní dotaz pro výběr všech osob z tabulky s křestním jménem Jan by vypadal takto:

SELECT * FROM `uzivatele` WHERE `jmeno` = 'Jan';

Hvězdička v dotazu označuje, že chceme vybrat všechny sloupce. Dotaz tedy česky zní: "Vyber všechny sloupce z tabulky uzivatele, kde má sloupce jmeno hodnotu Jan".

Výsledek dotazu nám phpMyAdmin zobrazí. Nalezneme v něm dva záznamy:

8    Jan    Novák     1984-11-03    17
37   Jan    Spáčil    1967-05-06    3

Tabulky mají 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 sloupce, které chceme zobrazit.

Výběr sloupců v dotazu

Dejme tomu, že budeme chtít jen příjmení lidí, kteří se jmenují Jan a k tomu počet článků, které napsali. Dotaz upravíme:

SELECT `prijmeni`, `pocet_clanku` FROM `uzivatele` WHERE `jmeno` = 'Jan';

Výsledek:

Novák     17
Spáčil    3

Opravdu je v tomto případě dobré nebýt líní. Pokud nepotřebujeme téměř všechny sloupce, vyjmenujeme v dotazu pouze ty, jejichž hodnoty nás v tu chvíli zajímají.

Vždy se snažíme podmínku omezit co nejvíce již na úrovni databáze. Z hlediska výkonu není dobré získávat data celé tabulky do aplikace a tam si ji teprve vyfiltrovat. Řekněme, že by pak naše aplikace 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.

U podmínkování platí to samé, jako u DELETE, klauzule WHERE zde funguje úplně stejně. Zkusme si to. Vyberme všechny uživatele, narozené v roce 1960 a později s počtem článků vyšším než pět:

SELECT * FROM `uzivatele` WHERE `datum_narozeni` >= '1960-1-1' AND `pocet_clanku` > 5;

Výsledek:

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

Operátory

Základní operátory =, >, <, >=, <=, != si popisovat nemusíme. V SQL máme ale i další operátory:

• LIKE,
• IN
• a BETWEEN.

Pojďme si je popsat.

Operátor LIKE

Tento operátor umožňuje vyhledávat textové hodnoty jen podle části textu. Funguje podobně jako operátor =, ale můžeme s ním používat dva zástupné znaky:

• % 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%';

Všimněme si, že na velikosti hledaného písmena nezáleží. Hledání je case-insensitive.

Výsledek dotazu bude následující:

Svoboda
Svobodová
Spáčil

Nyní zkusme najít pětipísmenná příjmení, která mají jako druhý znak O:

SELECT `prijmeni` FROM `uzivatele` WHERE `prijmeni` LIKE '_o___';

Výsledek:

Novák
Horák
Kovář

Asi již tušíme, jak LIKE funguje. Použití lze vymyslet hodně, většinou se používá s procenty na obou stranách pro fulltextové vyhledávání.

Operátor IN

Operátor 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:

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

Operátor BETWEEN

Poslední operátor, který si dnes vysvětlíme, není ničím jiným, než zkráceným zápisem podmínky >= AND <=. Využijme jej pro nalezení uživatelů, kteří se narodili mezi lety 1980 a 1990:

SELECT `jmeno`, `prijmeni`, `datum_narozeni` FROM `uzivatele` WHERE `datum_narozeni` BETWEEN '1980-1-1' AND '1989-12-31';

Mezi dvě hodnoty píšeme také operátor AND.

Výsledek:

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

Vzorový příklad s operátorem BETWEEN a jeho alternativní zápis přináší očekávané výsledky vztahující se především k datovému typu DATE. U typu DATETIME, jenž bývá používán častěji, však dochází k mírně odlišným výsledkům. Datový typ DATETIME totiž zahrnuje i čas, který má výchozí hodnotu 00:00:00.

Představme si, že máme v tabulce uživatelů osobu s datem narození (typu DATETIME) nastaveným na hodnotu 1989-12-31 12:00:00. Původní dotaz výše, pokud v něm nespecifikujeme také čas, by nám takového uživatele nenalezl. Abychom získali všechny uživatele narozené od roku 1980 do konce roku 1989, museli bychom dotaz upravit takto:

SELECT `jmeno`, `prijmeni`, `datum_narozeni` FROM `uzivatele` WHERE `datum_narozeni` BETWEEN '1980-1-1 00:00:00' AND '1989-12-31 23:59:59';

U výběru dat zůstaneme ještě několik lekcí, vlastně většinu tohoto kurzu.

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

 

Měl jsi s čímkoli problém? Stáhni si vzorovou aplikaci níže a porovnej ji se svým projektem, chybu tak snadno najdeš.