6. diel - MySQL krok za krokom - Výber dát (vyhľadávanie)

V predchádzajúcom cvičení, Riešené úlohy k 1.-5. lekciu MySQL / MariaDB, sme si precvičili získané skúsenosti z predchádzajúcich lekcií.

V dnešnom MySQL tutoriále sa zameriame na tú najkrajšiu časť práce s databázou a tou je výber dát. Ide o dotazovanie na dáta, inými slovami vyhľadávanie dát v tabuľke.

Vyhľadávanie 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. Od jednoduchého výberu užívateľa podľa jeho ID (napríklad 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í. Je možné dokonca do dotazu zapojiť viac tabuliek, rôzne funkcie alebo skladať otázky do seba.

Príprava testovacích dát

Pred skúšaním otázok je vždy dobré mať nejaké testovacie dáta, aby sme mali s čím pracovať a nemali v tabuľke len štyroch užívateľov. Poďme si do našej tabuľky users vložiť viac záznamov. Pôvodnú tabuľku si najskôr vyprázdnime, aby sme v nej mali rovnaké dáta:

TRUNCATE TABLE `users`;

Ďalej spustíme nasledujúci SQL príkaz:

INSERT INTO `users` (
        `first_name`,
        `last_name`,
        `birthdate`,
        `articles_count`
)
VALUES
('John',  'Smith',  '1984-11-03', 17),
('Steven', 'Murphy', '1942-10-17', 12),
('George', 'Lam', '1958-7-10', 5),
('Linda', 'Martin', '1935-5-15', 6),
('Donald', 'Brown', '1967-4-17', 2),
('Aron', 'Heaven', '1995-2-20', 1),
('Paul', 'Lee', '1984-4-18', 1),
('David', 'Clark', '1973-5-14', 3),
('Mark', 'Wilson', '1969-6-2', 7),
('Sarah', 'Johnson', '1962-7-3', 8),
('Charles', 'Lopez', '1974-9-10', 0),
('Jennifer', 'Williams', '1976-10-2', 1),
('Daniel', 'Jones', '1948-11-3', 1),
('Betty', 'Miller', '1947-5-9', 1),
('Michelle', 'Davis', '1956-3-7', 0),
('Mary', 'Taylor', '1954-2-11', 5),
('Barbara', 'Thomas', '1978-1-21', 3),
('Donna', 'Johnson', '1949-7-26', 12),
('Joseph', 'Murphy', '1973-7-28', 4),
('Helen', 'Murphy', '1980-8-11', 8),
('Jeff', 'Moore', '1982-9-30', 18),
('Anthony', 'Jackson', '1961-1-15', 2),
('Nancy', 'Thompson', '1950-8-29', 4),
('Edward', 'White', '1974-2-26', 5),
('Lucy', 'Harris', '1983-3-2', 2),
('Paul', 'Walker', '1991-5-1', 9),
('Carol', 'Young', '1992-12-17', 9),
('James', 'Baker', '1956-11-15', 4),
('Patricia', 'Adams', '1953-10-20', 6),
('Lisa', 'Green', '1967-5-6', 3),
('John', 'Johnson', '1946-3-10', 6);

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.

Už vieme, že môžeme za kľúčovým slovom VALUES uviesť viac záznamov a nemusíme teda pre každého používateľa písať vlastnú otázku.

Dopytovanie

Jednoduchý dotaz na dáta, teda na ich vyhľadanie alebo výber môžeme v prostredí phpMyAdmin vytvoriť v záložke Search v hornej lište. Otvoríme ním nasledujúce okno:

Tu stačí zadať nejakú hodnotu do stĺpca Value. Pokiaľ v tomto stĺpci vyplníme polí viac, bude sa hľadať podľa viacerých hodnôt. Operátory si vysvetlíme vzápätí.

Nástroj phpMyAdmin bol pre nás spočiatku takou berličkou, ale teraz pre nás už prestáva byť zaujímavý. Budeme ho používať hlavne na spúšťanie otázok a ukazovanie ich výsledkov.

Základná otázka pre výber všetkých osôb z tabuľky s krstným menom John by vyzerala takto:

SELECT * FROM `users` WHERE `first_name` = 'John';

Hviezdička v dotaze označuje, že chceme vybrať všetky stĺpce. Otázka teda po slovensky znie: "Vyber všetky stĺpce z tabuľky users, kde má stĺpce first_name hodnotu John ".

Výsledok dotazu nám phpMyAdmin zobrazí. Nájdeme v ňom dva záznamy:

1    John    Smith     1984-11-03    17
31   John    Johnson   1946-03-10     6

Tabuľky majú 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ť stĺpce, ktoré chceme zobraziť.

Výber stĺpcov v dotaze

Dajme tomu, že budeme chcieť len priezvisko ľudí, ktorí sa volajú John ak tomu počet článkov, ktoré napísali. Otázku upravíme:

SELECT `last_name`, `article_count` FROM `users` WHERE `first_name` = 'John';

Výsledok:

Smith     17
Johnson    6

Naozaj je v tomto prípade dobré nebyť leniví. Pokiaľ nepotrebujeme takmer všetky stĺpce, vymenujeme v dotaze iba tie, ktorých hodnoty nás v tej chvíli zaujímajú.

Vždy sa snažíme podmienku obmedziť čo najviac už na úrovni databázy. Z hľadiska výkonu nie je dobré získavať dáta celej tabuľky do aplikácie a tam si ju ešte len vyfiltrovať. Povedzme, že by potom naša aplikácia 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.

Pri podmienkovaní platí to isté, ako pri DELETE, klauzula WHERE tu funguje úplne rovnako. Skúsme si to. Vyberme všetkých užívateľov, narodených v roku 1960 a neskôr s počtom článkov vyšším ako päť:

SELECT * FROM `users` WHERE `birthdate` >= '1960-1-1' AND `article_count` > 5;

Výsledok:

1     John          Smith       1984-11-03    17
9     Mark          Wilson      1969-06-02    7
10    Sarah         Johnson     1962-07-03    8
20    Helen         Murphy      1980-08-11    8
21    Jeff          Moore       1982-09-30    18
26    Paul          Walker      1991-05-01    9
27    Carol         Young       1992-12-17    9

Operátorov

Základných operátorov =, >, <, >=, <=, != si popisovať nemusíme. V SQL máme ale aj ďalších operátorov:

• LIKE,
• IN,
• a BETWEEN.

Poďme si ich popísať.

Operátor LIKE

Tento operátor umožňuje vyhľadávať textové hodnoty len podľa časti textu. Funguje podobne ako operátor =, ale môžeme s ním používať dva zástupné znaky:

• % 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úcich na S:

SELECT `last_name` FROM `users` WHERE `last_name` LIKE 's%';

Všimnime si, že na veľkosti hľadaného písmena nezáleží. Hľadanie je case-insensitive.

Výsledok dotazu bude nasledujúci:

Smith

Teraz skúsme nájsť päťpísmenové priezviská, ktoré majú ako druhý znak O:

SELECT `last_name` FROM `users` WHERE `last_name` LIKE '_o___';

Výsledok:

Lopez
Jones
Moore
Young

Asi už tušíme, 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.

Operátor IN

Operátor IN umožňuje vyhľadávať pomocou zoznamu prvkov. Urobme si teda zoznam mien a vyhľadajme užívatelia s týmito menami:

SELECT `first_name`, `last_name` FROM `users` WHERE `first_name` IN ('Paul', 'John', 'Barbara');

Výsledok:

John        Smith
Paul        Lee
Barbara     Thomas
Paul        Walker
John        Johnson

Operátor IN sa používa ešte pri tzv. poddotazoch, ale na tie máme ešte dosť času

Operátor BETWEEN

Posledný operátor, ktorý si dnes vysvetlíme, nie je ničím iným, než skráteným zápisom podmienky >= AND <=. Využime ho na nájdenie užívateľov, ktorí sa narodili medzi rokmi 1980 a 1990:

SELECT `first_name`, `last_name`, `birthdate` FROM `users` WHERE `birthdate` BETWEEN '1980-1-1' AND '1989-12-31';

Medzi dve hodnoty píšeme aj operátor AND.

Výsledok:

John        Smith        1984-11-03
Paul        Lee          1984-04-18
Helen       Murphy       1980-08-11
Jeff        Moore        1982-09-30
Luccy       Harris       1983-03-02

Vzorový príklad s operátorom BETWEEN a jeho alternatívny zápis prináša očakávané výsledky vzťahujúce sa predovšetkým k dátovému typu DATE. Pri type DATETIME, ktorý býva používaný častejšie, však dochádza k mierne odlišným výsledkom. Dátový typ DATETIME totiž zahŕňa aj čas, ktorý má východiskovú hodnotu 00:00:00.

Predstavme si, že máme v tabuľke užívateľov osobu s dátumom narodenia (typu DATETIME) nastaveným na hodnotu 1989-12-31 12:00:00. Pôvodný dotaz vyššie, pokiaľ v ňom nešpecifikujeme tiež čas, by nám takého užívateľa nenašiel. Aby sme získali všetkých užívateľov narodených od roku 1980 do konca roku 1989, museli by sme otázku upraviť takto:

SELECT `first_name`, `last_name`, `birthdate` FROM `users` WHERE `birthdate` BETWEEN '1980-1-1 00:00:00' AND '1989-12-31 23:59:59';

Pri výbere dát zostaneme ešte niekoľko lekcií, vlastne väčšinu tohto kurzu.

V nasledujúcom cvičení, Riešené úlohy k 6. lekcii MySQL/MariaDB, si precvičíme nadobudnuté skúsenosti z predchádzajúcich lekcií.

 

Mal si s čímkoľvek problém? Stiahni si vzorovú aplikáciu nižšie a porovnaj ju so svojím projektom, chybu tak ľahko nájdeš.