Udržitelnost v podnikání je bez řádné správy dat obtížná. Vybudování jasně definovaného systému je zásadní pro řízení interních i externích operací. Detailní vhled do obchodních dat pomáhá pochopit celkový pracovní postup a měřit výkonnost. Používání databází navíc šetří čas a umožňuje snadný přístup k informacím.
Co je to databáze?
A databáze je organizovaný systém sloužící k bezpečnému ukládání informací. Nabízí kategorizaci a umožňuje vytváření tabulek pro třídění podobných druhů položek do jedné skupiny. Tabulky poskytují možnosti výpisu informací v několika řádcích a sloupcích. V důsledku toho se zvyšuje kvalita a konzistence dat. Kromě ukládání a snadného přístupu hrají databáze významnou roli při ochraně soukromí a zabezpečení systémů. Získávání dat, ať už ad hoc, nebo jako součást procesu zakódovaného do aplikace, je jedním z nejkritičtějších aspektů správy databází.
A dotaz v relačních DMS je jakýkoli příkaz používaný k získávání dat z tabulky. SELECT příkazy se téměř vždy používají v Structured Query Language (SQL) dotazech. Existuje několik metod, jak získat informace z databáze. Vývojáři obecně dávají přednost možnosti příkazového řádku, protože je rychlá a efektivní. Umožňuje bezproblémové odesílání dotazů.
V této příručce vás seznámíme s MySQL a probereme, jak pracovat s dotazy v MySQL. Začněme!
Požadavky
Chcete-li postupovat podle tohoto návodu, budete potřebovat následující:
-
Nejnovější verzi Ubuntu nainstalovanou ve vašem systému.
-
Uživatelé systému musí mít oprávnění sudo.
-
Pokud máte potíže s přístupem k účtu sudo, zvažte změnu hesla root pro MySQL.
-
-
MySQL je nastaveno na vašem serveru.
Krok 1 — Vytvoření a nastavení databáze
Než začneme a napíšeme náš první SQL dotaz, musíme vytvořit databázi a přidat do ní tabulky. Poté tabulky naplníme ukázkovými daty. Práce s databázemi vám pomůže pochopit základní nastavení a dodá vám jistotu při práci s dotazy.
Praktické zkušenosti jsou nejúčinnějším způsobem, jak si vybudovat představu a pochopit význam databází. V této příručce vás provedeme používáním databází a jejich významem pro řešení reálných problémů. Podívejme se na scénář, abychom pochopili, jak mohou databáze pomoci organizovat záznamy strukturovaným způsobem.
Scénář: Vytvoříme databázi, kde skupina vysokoškoláků odjíždí na dovolenou oslavit své narozeniny. Před odjezdem na dovolenou se plánují zúčastnit přátelských plaveckých závodů. Každý kamarád ve skupině si také naplánuje seznam úkolů a aktivit, aby byl jejich výlet napínavý a zábavný.
Nejprve otevřete MySQL jako uživatel root:
|
1 |
$ sudo mysql |
Poté vytvořte databázi spuštěním následujícího příkazu:
|
1 2 3 |
mysql> CREATE DATABASE `vacation`; Query OK, 1 row affected (0.52 sec) |
Dále použijeme naši databázi pomocí následujícího příkazu:
|
1 |
mysql> USE vacation; |
Poté v naší databázi vytvoříme tabulky. První tabulku pojmenujte jako celebration. Naše tabulka bude mít sloupce pro jména našich přátel (name), turnaje, které vyhráli (wins) a také sloupec pro jejich nejlepší čas (time):
|
1 2 3 4 5 6 7 |
mysql> CREATE TABLE celebration ( -> name varchar(50), -> wins real, -> time real -> ); Query OK, 0 rows affected (2.03 sec) |
Poté naši tabulku naplníme daty:
|
1 2 3 4 5 6 7 8 9 |
mysql> INSERT INTO celebration (name, wins, time) VALUES('Austin', '4', '15.5'), ('Ivan','7','9'), ('Aisha','10','5'), ('Zane','13','7.5'); Query OK, 4 rows affected (0.29 sec) Records: 4 Duplicates: 0 Warnings: 0 |
Poté v naší databázi vytvořte další tabulku pro uložení informací o jejich oblíbených narozeninových aktivitách. Vytvoříme tabulku s názvem vacation a bude mít následující sloupce:
|
Seznam |
Podrobnosti |
|
name: |
Uloží jméno každého přítele. |
|
birthdate: |
Sleduje datum narození každého jednotlivce. |
|
activity: |
Uchovává záznam o jejich oblíbené aktivitě. |
|
Destination: |
Ukládá informace o oblíbené destinaci každého jednotlivce. |
|
meal: |
Sleduje oblíbené jídlo daného jednotlivce. |
|
1 2 3 4 5 6 7 8 |
mysql> CREATE TABLE vacation ( name varchar(50), birthdate date, activity varchar(50), destination varchar(50), meal varchar(50) ); Query OK, 0 rows affected (0.09 sec) |
Naplňte tabulku daty:
|
1 2 3 4 5 6 7 8 9 10 |
mysql> CREATE TABLE vacation ( name varchar(50), birthdate date, activity varchar(50), destination varchar(50), meal varchar(50) ); Query OK, 0 rows affected (0.09 sec) Records: 4 Duplicates: 0 Warnings: 0 |
Nyní jste dokončili nastavení databáze.
Krok 2 — Začínáme s příkazy SELECT
Dotazy v SQL obvykle začínají s SELECT. Používá se v dotazech k určení, které sloupce v tabulce mají být vráceny s výsledky. Dotaz musí také vždy obsahovat FROM, který se používá k určení tabulky, na kterou se bude dotaz dotazovat.
Dotazy se řídí následující syntaxí:
|
1 |
mysql> SELECT column_to_select FROM table_to_select WHERE certain_conditions_apply; |
Použijeme syntaxi dotazu k vrácení sloupce meal z tabulky vacation:
|
1 |
mysql> SELECT meal FROM vacation; |
Náš výstup bude následující:
|
1 2 3 4 5 6 7 8 9 10 |
+-------+ | meal | +-------+ | Steak | | Sushi | | Fries | | Tofu | +-------+ 4 rows in set (0.00 sec) |
Můžete také vybrat více sloupců tak, že je oddělíte čárkou:
|
1 |
mysql> SELECT name, destination FROM vacation; |
Výstup:
|
1 2 3 4 5 6 7 8 9 10 |
+--------+-------------+ | name | destination | +--------+-------------+ | Austin | Maldives | | Ivan | Mauritius | | Aisha | Colorado | | Zane | Bora Bora | +--------+-------------+ 4 rows in set (0.00 sec) |
Můžete také použít hvězdičku (*), pokud chcete reprezentovat všechny sloupce v tabulce:
|
1 |
mysql> SELECT * FROM celebration; |
Výstup:
|
1 2 3 4 5 6 7 8 9 10 |
+--------+------+------+ | name | wins | time | +--------+------+------+ | Austin | 4 | 15.5 | | Ivan | 7 | 9 | | Aisha | 10 | 5 | | Zane | 13 | 7.5 | +--------+------+------+ 4 rows in set (0.00 sec) |
Pokud chcete filtrovat záznamy, které splňují zadanou podmínku, použijte WHERE. Řádky, které nesplňují zadanou podmínku, jsou z výsledků vyřazeny. Klauzule WHERE používá následující syntaxi:
|
1 |
mysql> . . . WHERE column_name comparison_operator value |
Jedná se o porovnávací operátor, který definuje, jak má být zadaný sloupec porovnán s hodnotou. Mezi běžné porovnávací operátory SQL patří:
|
Operátor |
Použití |
|
= |
Rovnost |
|
!= |
Nerovnost |
|
< |
Menší než |
|
> |
Větší než |
|
<= |
Menší než nebo rovno |
|
>= |
Větší než nebo rovno |
|
BETWEEN |
Testuje, zda hodnota leží v daném rozsahu. |
|
IN |
Testuje, zda je hodnota řádku obsažena v sadě zadaných hodnot. |
|
EXISTS |
Testuje, zda řádek existuje |
|
LIKE |
Testuje, zda hodnota odpovídá zadanému řetězci |
|
IS NULL |
Testuje na hodnoty null |
|
IS NOT NULL |
Testuje na všechny hodnoty kromě NULL |
Pokud byste chtěli najít oblíbenou destinaci Aishy, mohli byste použít níže uvedený dotaz:
|
1 |
mysql> SELECT destination FROM vacation WHERE name ='Aisha'; |
Dotaz poté vrátí:
|
1 2 3 4 5 6 7 |
+-------------+ | destination | +-------------+ | Colorado | +-------------+ 1 row in set (0.03 sec) |
SQL podporuje použití zástupných znaků, které jsou obzvláště užitečné v WHERE klauzulích. Znaky procenta ( %) označují nulu nebo více neznámých znaků, zatímco podtržítka ( _) označují jeden neznámý znak. Tyto znaky jsou užitečné, pokud chcete najít konkrétní záznam v tabulce, ale nejste si jím jisti.
Pokud jste například zapomněli oblíbenou destinaci svého přítele a věděli jste pouze písmeno, kterým začíná, například “m”, můžete název destinace najít pomocí následujícího dotazu:
|
1 |
mysql> SELECT destination FROM vacation WHERE destination LIKE 'm%'; |
Dotaz vrátí:
|
1 2 3 4 5 6 7 8 |
+-------------+ | destination | +-------------+ | Maldives | | Mauritius | +-------------+ 2 rows in set (0.04 sec) |
Při práci s databázemi se můžete setkat se sloupci nebo tabulkami s relativně dlouhými nebo obtížně čitelnými názvy. V těchto případech můžete názvy zpřehlednit použitím klíčového slova AS pro vytvoření aliasu. Aliasy vytvořené pomocí AS jsou platné pouze po dobu trvání dotazu, pro který byly vytvořeny:
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> SELECT name AS E, destination as D, activity as A FROM vacation; +--------+-----------+-------------------+ | E | D | A | +--------+-----------+-------------------+ | Austin | Maldives | skiing | | Ivan | Mauritius | paragliding | | Aisha | Colorado | mountain climbing | | Zane | Bora Bora | fishing | +--------+-----------+-------------------+ 4 rows in set (0.00 sec) |
Krok 3 — Úvod do agregačních funkcí
Při práci s daty nechcete vždy vidět samotná data. Raději byste měli informace o těchto datech. Zadáním dotazu SELECT můžete interpretovat nebo provádět výpočty nad vašimi daty pomocí syntaxe SQL. Tyto funkce se označují jako agregační funkce.
Funkce COUNT spočítá a vrátí počet řádků, které splňují určitou sadu kritérií. Pokud například chcete vědět, kolik přátel dává přednost cestě na Maldives, můžete použít následující dotaz:
|
1 |
mysql> SELECT COUNT(destination) FROM vacation WHERE destination = 'Maldives'; |
Budou vráceny následující výsledky:
|
1 2 3 4 5 6 7 |
+--------------------+ | COUNT(destination) | +--------------------+ | 1 | +--------------------+ 1 row in set (0.06 sec) |
MIN se používá k nalezení nejmenší hodnoty v zadaném sloupci:
|
1 |
mysql> SELECT MIN(wins) FROM celebration; |
Dotaz vypíše:
|
1 2 3 4 5 6 7 |
+-----------+ | MIN(wins) | +-----------+ | 4 | +-----------+ 1 row in set (0.02 sec) |
MAX se používá k nalezení největší číselné hodnoty v daném sloupci:
|
1 |
mysql> SELECT MAX(wins) FROM celebration; |
Očekávaný výstup je:
|
1 2 3 4 5 6 7 |
+-----------+ | MAX(wins) | +-----------+ | 13 | +-----------+ 1 row in set (0.03 sec) |
Obě funkce MIN a MAX lze použít na číselná i abecední data. Při aplikaci na sloupec řetězcových hodnot vrátí funkce MIN první hodnotu v abecedním pořadí.
Funkce MIN vrátí první hodnotu v abecedním pořadí:
|
1 |
mysql> SELECT MIN(name) FROM celebration; |
Zde je ukázka výstupu:
|
1 2 3 4 5 6 7 |
+-----------+ | MIN(name) | +-----------+ | Aisha | +-----------+ 1 row in set (0.00 sec) |
Funkce MAX vrátí poslední hodnotu v abecedním pořadí:
|
1 |
mysql> SELECT MAX(name) FROM celebration; |
Výstup bude vypadat takto:
|
1 2 3 4 5 6 7 |
+-----------+ | MAX(name) | +-----------+ | Zane | +-----------+ 1 řádek v sadě (0.00 s) |
Krok 4 — Manipulace s výstupy dotazů
Další populární klauzule, která se používá, je GROUP BY . Používá se při provádění agregační funkce nad jedním sloupcem, ale ve vztahu k odpovídajícím hodnotám v jiném:
|
1 |
mysql> SELECT COUNT(name), activity FROM vacation GROUP BY activity; |
Výstup bude:
|
1 2 3 4 5 6 7 8 9 10 |
+-------------+-------------------+ | COUNT(name) | activity | +-------------+-------------------+ | 1 | lyžování | | 1 | paragliding | | 1 | horské lezení | | 1 | rybaření | +-------------+-------------------+ 4 řádky v sadě (0.04 s) |
Chcete-li seřadit výsledky dotazu, použijte klauzuli ORDER BY . Číselné hodnoty se ve výchozím nastavení řadí vzestupně, zatímco textové hodnoty se řadí abecedně. Níže uvedený dotaz vypisuje sloupce name a birthdate, ale výsledky řadí podle birthdate:
|
1 |
mysql> SELECT name, birthdate FROM vacation ORDER BY birthdate; |
Výstup bude následující:
|
1 2 3 4 5 6 7 8 9 10 |
+--------+------------+ | name | birthdate | +--------+------------+ | Zane | 1996-01-01 | | Aisha | 1999-07-24 | | Austin | 2002-01-07 | | Ivan | 2010-12-18 | +--------+------------+ 4 řádky v sadě (0.04 s) |
Výstup je ve vzestupném pořadí. Chcete-li jej seřadit sestupně, ukončete dotaz slovem DESC:
|
1 |
mysql> SELECT name, birthdate FROM vacation ORDER BY birthdate DESC; |
Podívejte se na výstup:
|
1 2 3 4 5 6 7 8 9 10 |
+--------+------------+ | name | birthdate | +--------+------------+ | Ivan | 2010-12-18 | | Austin | 2002-01-07 | | Aisha | 1999-07-24 | | Zane | 1996-01-01 | +--------+------------+ 4 řádky v sadě (0.00 s) |
Klauzule HAVING byla do SQL přidána, aby poskytovala podobnou funkčnost jako klauzule WHERE, přičemž je zároveň kompatibilní s agregačními funkcemi. Rozdíl mezi těmito dvěma klauzulemi je v tom, že WHERE slouží k odkazování na jednotlivé záznamy a HAVING odkazuje na skupinové záznamy. Z tohoto důvodu musí být klauzule GROUP BY přítomna vždy, když je použita klauzule HAVING:
|
1 |
mysql> SELECT COUNT(name), activity FROM vacation GROUP BY activity HAVING COUNT(name) >= 1; |
Dotaz vrátí výstup:
|
1 2 3 4 5 6 7 8 |
+-------------+-------------------+ | COUNT(name) | activity | +-------------+-------------------+ | 1 | lyžování | | 1 | paragliding | | 1 | horské lezení | | 1 | rybaření | +-------------+-------------------+ |
Hodnota COUNT je u všech 1, protože žádní dva přátelé nemají rádi stejnou aktivitu.
Krok 5 — Dotazování nad více tabulkami
Klauzuli JOIN lze použít ve výsledku dotazu ke spojení řádků ze dvou nebo více tabulek. Dosahuje toho vyhledáním souvisejícího sloupce mezi tabulkami a odpovídajícím seřazením výstupu.
SELECT příkazy, které obsahují klauzuli JOIN, se řídí níže uvedenou syntaxí:
|
1 2 3 |
mysql> SELECT table1.column1, table2.column2 mysql> FROM table1 mysql> JOIN table2 ON table1.related_column=table2.related_column; |
Pokud byste chtěli každému ze svých přátel koupit trofej za jejich wins při plavání k jejich narozeninám, mohli byste vytvořit dotaz, který spojí obě tabulky a pomůže vám najít všechny požadované informace pomocí jediného dotazu:
|
1 2 3 |
mysql> SELECT celebration.name, celebration.wins, vacation.birthdate FROM celebration JOIN vacation ON celebration.name=vacation.name; |
Výstup bude:
|
1 2 3 4 5 6 7 8 9 10 |
+--------+------+------------+ | name | wins | birthdate | +--------+------+------------+ | Austin | 4 | 2002-01-07 | | Ivan | 7 | 2010-12-18 | | Aisha | 10 | 1999-07-24 | | Zane | 13 | 1996-01-01 | +--------+------+------------+ 4 rows in set (0.00 sec) |
Toto je vnitřní JOIN klauzule. Je to proto, že vybere všechny záznamy, které mají odpovídající hodnoty v obou tabulkách, a vypíše je do sady výsledků. Záznamy, které neodpovídají dotazu, nejsou zahrnuty. Do našich tabulek můžeme vložit nový řádek, který neodpovídá žádnému záznamu:
|
1 2 3 4 |
mysql> INSERT INTO celebration(name,wins,time) VALUES('Ella', '1', '120'); Query OK, 1 row affected (0.01 sec) |
|
1 2 3 4 |
mysql> INSERT INTO celebration(name,wins,time) VALUES('Ella', '1', '120'); Query OK, 1 row affected (0.01 sec) |
Poté znovu spusťte příkaz SELECT s klauzulí JOIN :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> SELECT celebration.name, celebration.wins, vacation.birthdate -> FROM celebration -> JOIN vacation ON celebration.name=vacation.name; +--------+------+------------+ | name | wins | birthdate | +--------+------+------------+ | Austin | 4 | 2002-01-07 | | Ivan | 7 | 2010-12-18 | | Aisha | 10 | 1999-07-24 | | Zane | 13 | 1996-01-01 | +--------+------+------------+ 4 rows in set (0.00 sec) |
Protože tabulka celebration nemá žádný záznam pro Petra a tabulka vacation nemá žádný záznam pro Ellu, tyto záznamy chybí.
Všechny záznamy z jedné z tabulek můžeme vrátit pomocí vnější JOIN klauzule. Může se jednat buď o LEFT JOIN, nebo o RIGHT JOIN. Klauzule LEFT JOIN vrací všechny záznamy z levé tabulky a pouze odpovídající záznamy z pravé tabulky. Levá tabulka v kontextu vnějších spojení je ta, na kterou odkazuje klauzule FROM, a pravá tabulka je jakákoli tabulka uvedená za příkazem JOIN.
Spusťte dotaz znovu, ale použijte klauzuli LEFT JOIN :
|
1 2 3 |
mysql> SELECT celebration.name, celebration.wins, vacation.birthdate FROM celebration LEFT JOIN vacation ON celebration.name=vacation.name; |
Příkaz vrátí všechny záznamy z levé tabulky ( celebration) i v případě, že nemá odpovídající záznam v pravé tabulce. Pokud v pravé tabulce neexistuje odpovídající záznam, vrátí se jako NULL:
|
1 2 3 4 5 6 7 8 9 10 11 |
+--------+------+------------+ | name | wins | birthdate | +--------+------+------------+ | Austin | 4 | 2002-01-07 | | Ivan | 7 | 2010-12-18 | | Aisha | 10 | 1999-07-24 | | Zane | 13 | 1996-01-01 | | Ella | 1 | NULL | +--------+------+------------+ 5 rows in set (0.00 sec) |
Toto je nyní klauzule RIGHT JOIN :
|
1 2 3 |
mysql> SELECT celebration.name, celebration.wins, vacation.birthdate FROM celebration RIGHT JOIN vacation ON celebration.name=vacation.name; |
Vrátí se všechny hodnoty z pravé tabulky (vacation). Protože Peterovo birthdate je zaznamenán v pravé tabulce a nikoli v levé tabulce, pro sloupce name a wins se v těchto řádcích vrátí NULL hodnoty:
|
1 2 3 4 5 6 7 8 9 10 11 |
+--------+------+------------+ | name | wins | birthdate | +--------+------+------------+ | Austin | 4 | 2002-01-07 | | Ivan | 7 | 2010-12-18 | | Aisha | 10 | 1999-07-24 | | Zane | 13 | 1996-01-01 | | NULL | NULL | 1991-05-03 | +--------+------+------------+ 5 řádků v sadě (0.01 s) |
Můžete použít UNION klauzuli namísto JOIN pro dotazování na záznamy z více tabulek. Operátor UNION se liší od klauzule JOIN v tom, že kombinuje výsledky dvou příkazů SELECT do jednoho sloupce, namísto tisku výsledků z více tabulek jako jedinečných sloupců pomocí jediného příkazu SELECT.
Pro ilustraci můžete spustit tento dotaz:
|
1 |
mysql> SELECT name FROM celebration UNION SELECT name FROM vacation; |
Dotaz odstraní duplicitní záznamy. Toto je výchozí chování operátoru UNION :
|
1 2 3 4 5 6 7 8 9 10 11 12 |
+--------+ | name | +--------+ | Austin | | Ivan | | Aisha | | Zane | | Ella | | Peter | +--------+ 6 řádků v sadě (0.00 s) |
Chcete-li vrátit všechny záznamy (včetně duplicit), použijte operátor UNION ALL :
|
1 |
mysql> SELECT name FROM celebration UNION ALL SELECT name FROM vacation; |
Výstup:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
+--------+ | name | +--------+ | Austin | | Ivan | | Aisha | | Zane | | Ella | | Austin | | Ivan | | Aisha | | Zane | | Peter | +--------+ 10 řádků v sadě (0.00 s) |
Poddotazy jsou další metodou pro dotazování do více tabulek. Poddotazy jsou dotazy, které jsou vloženy do jiného dotazu (označované také jako vnitřní nebo vnořené dotazy). Ty jsou užitečné, pokud chcete porovnat výsledky dotazu s výsledky samostatné agregační funkce.
Použijeme příklad, kdy se snažíme zjistit, který přítel vyhrál více plaveckých turnajů než Ella. Namísto zjišťování, kolik zápasů Ella vyhrála, a následného spuštění dalšího dotazu, abyste zjistili, kdo vyhrál více her než ona, můžete obojí spočítat pomocí jediného dotazu:
|
1 2 3 4 |
mysql> SELECT name, wins FROM celebration -> WHERE wins > ( -> SELECT wins FROM celebration WHERE name = 'Ella' -> ); |
Dotaz vrátí:
|
1 2 3 4 5 6 7 8 9 10 |
+--------+------+ | name | wins | +--------+------+ | Austin | 4 | | Ivan | 7 | | Aisha | 10 | | Zane | 13 | +--------+------+ 4 řádky v sadě (0.06 s) |
Pokud byste chtěli vzít své přátele na překvapivou dovolenou, můžete pomocí dotazu zjistit, kdo má nejvíce výher, a vrátit jejich destinaci:
|
1 2 3 4 |
mysql> SELECT name, destination, activity, meal -> FROM vacation -> WHERE name = ( SELECT name FROM celebration -> WHERE wins = (SELECT MAX(wins) FROM celebration)); |
Dotaz vrátí:
|
1 2 3 4 5 6 7 |
+------+-------------+----------+------+ | name | destination | activity | meal | +------+-------------+----------+------+ | Zane | Bora Bora | rybaření | Tofu | +------+-------------+----------+------+ 1 řádek v sadě (0.00 s) |
Tento příkaz obsahuje poddotaz uvnitř poddotazu.
Závěr
Generování dotazů je jedním z nejčastějších úkolů při správě databází. Existuje několik nástrojů pro správu databází, jako je phpMyAdmin a pgAdmin, které můžete použít k práci s dotazy a vizualizaci jejich výsledků. Příkaz SELECT z příkazové řádky je však nejpreferovanější volbou kvůli snadnému použití a vynikající kontrole.
Kromě toho existuje mnoho návodů o databázích, které můžete prozkoumat na našem blogu:
- SQLite vs MySQL vs. PostgreSQL: Srovnání relačních databázových systémů
- Rychlá řešení pro opravu poškozených tabulek v MySQL: Návod
- Jak nainstalovat MySQL na Centos 7
- Uživatel MySQL — Vytvoření a udělení oprávnění
- Nastavení replikace v MySQL na Ubuntu
Příjemnou práci s počítačem!
Komentáře
Zatím žádné komentáře. Buďte první.