Naučte se používat několik funkcí MySQL a MariaDB – část 2
Toto je druhá část série o 2 článcích o základech příkazů MariaDB/MySQL. Než budete pokračovat, přečtěte si náš předchozí článek na toto téma.
- Naučte se základy MySQL/MariaDB pro začátečníky – část 1
V této druhé části MySQL/MariaDB série pro začátečníky vysvětlíme, jak omezit počet řádků vrácených SELECT dotazem a jak seřadit sadu výsledků na základě dané podmínky.
Dále se naučíme, jak seskupovat záznamy a provádět základní matematické manipulace s číselnými poli. To vše nám pomůže vytvořit SQL skript, který můžeme použít k vytváření užitečných reportů.
Předpoklady
Chcete-li začít, postupujte takto:
1. Stáhněte si ukázkovou databázi zaměstnanců, která obsahuje šest tabulek sestávajících celkem ze 4 milionů záznamů.
wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2
tar xjf employees_db-full-1.0.6.tar.bz2
cd employees_db
2. Zadejte výzvu MariaDB a vytvořte databázi s názvem zaměstnanci:
mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.14-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> CREATE DATABASE employees;
Query OK, 1 row affected (0.00 sec)
3. Importujte jej na svůj server MariaDB následovně:
MariaDB [(none)]> source employees.sql
Počkejte 1–2 minuty, než se načte vzorová databáze (nezapomeňte, že zde mluvíme o 4 milionech záznamech!).
4. Ověřte, zda byla databáze importována správně, uvedením jejích tabulek:
MariaDB [employees]> USE employees;
Database changed
MariaDB [employees]> SHOW TABLES;
+---------------------+
| Tables_in_employees |
+---------------------+
| departments |
| dept_emp |
| dept_manager |
| employees |
| salaries |
| titles |
+---------------------+
6 rows in set (0.02 sec)
5. Vytvořte si speciální účet pro použití s databází zaměstnanců (neváhejte si zvolit jiný název účtu a heslo):
MariaDB [employees]> CREATE USER empadmin@localhost IDENTIFIED BY 'empadminpass';
Query OK, 0 rows affected (0.03 sec)
MariaDB [employees]> GRANT ALL PRIVILEGES ON employees.* to empadmin@localhost;
Query OK, 0 rows affected (0.02 sec)
MariaDB [employees]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
MariaDB [employees]> exit
Bye
Nyní se přihlaste jako uživatel empadmin do výzvy Mariadb.
mysql -u empadmin -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.14-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> USE employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
Než budete pokračovat, ujistěte se, že byly dokončeny všechny kroky naznačené na obrázku výše.
Objednávání a omezení počtu řádků v sadě výsledků
Tabulka platů obsahuje všechny příjmy každého zaměstnance s datem zahájení a ukončení. Možná budeme chtít zobrazit platy emp_no=10001 v průběhu času. To pomůže odpovědět na následující otázky:
- Dostal/a nějaké zvýšení?
- Pokud ano, kdy?
Chcete-li zjistit, proveďte následující dotaz:
MariaDB [employees]> SELECT * FROM salaries WHERE emp_no=10001 ORDER BY from_date;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 10001 | 60117 | 1986-06-26 | 1987-06-26 |
| 10001 | 62102 | 1987-06-26 | 1988-06-25 |
| 10001 | 66074 | 1988-06-25 | 1989-06-25 |
| 10001 | 66596 | 1989-06-25 | 1990-06-25 |
| 10001 | 66961 | 1990-06-25 | 1991-06-25 |
| 10001 | 71046 | 1991-06-25 | 1992-06-24 |
| 10001 | 74333 | 1992-06-24 | 1993-06-24 |
| 10001 | 75286 | 1993-06-24 | 1994-06-24 |
| 10001 | 75994 | 1994-06-24 | 1995-06-24 |
| 10001 | 76884 | 1995-06-24 | 1996-06-23 |
| 10001 | 80013 | 1996-06-23 | 1997-06-23 |
| 10001 | 81025 | 1997-06-23 | 1998-06-23 |
| 10001 | 81097 | 1998-06-23 | 1999-06-23 |
| 10001 | 84917 | 1999-06-23 | 2000-06-22 |
| 10001 | 85112 | 2000-06-22 | 2001-06-22 |
| 10001 | 85097 | 2001-06-22 | 2002-06-22 |
| 10001 | 88958 | 2002-06-22 | 9999-01-01 |
+--------+--------+------------+------------+
17 rows in set (0.03 sec)
Co když teď potřebujeme zobrazit posledních 5 navýšení? Můžeme provést ORDER BY from_date DESC. Klíčové slovo DESC označuje, že chceme sadu výsledků seřadit sestupně.
LIMIT 5 nám navíc umožňuje vrátit pouze prvních 5 řádků v sadě výsledků:
MariaDB [employees]> SELECT * FROM salaries WHERE emp_no=10001 ORDER BY from_date DESC LIMIT 5;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 10001 | 88958 | 2002-06-22 | 9999-01-01 |
| 10001 | 85097 | 2001-06-22 | 2002-06-22 |
| 10001 | 85112 | 2000-06-22 | 2001-06-22 |
| 10001 | 84917 | 1999-06-23 | 2000-06-22 |
| 10001 | 81097 | 1998-06-23 | 1999-06-23 |
+--------+--------+------------+------------+
5 rows in set (0.00 sec)
Můžete také použít ORDER BY s více poli. Například následující dotaz seřadí sadu výsledků na základě data narození zaměstnance vzestupně (výchozí) a poté podle příjmení v abecedním sestupném tvaru:
MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, gender AS Gender, hire_date AS "Hire date" FROM employees ORDER BY birth_date, last_name DESC LIMIT 10;
+--------------------+--------+------------+
| Name | Gender | Hire date |
+--------------------+--------+------------+
| Whitcomb, Kiyokazu | M | 1988-07-26 |
| Schaad, Ronghao | M | 1988-07-10 |
| Remmele, Supot | M | 1989-01-27 |
| Pocchiola, Jouni | M | 1985-03-10 |
| Kuzuoka, Eishiro | M | 1992-02-12 |
| Decaestecker, Moni | M | 1986-10-06 |
| Wiegley, Mircea | M | 1985-07-18 |
| Vendrig, Sachar | M | 1985-11-04 |
| Tsukuda, Cedric | F | 1993-12-12 |
| Tischendorf, Percy | M | 1986-11-10 |
+--------------------+--------+------------+
10 rows in set (0.31 sec)
Další informace o LIMIT si můžete prohlédnout zde.
Seskupování záznamů/MAX, MIN, AVG a ROUND
Jak jsme již zmínili dříve, tabulka platy obsahuje příjmy jednotlivých zaměstnanců v průběhu času. Kromě LIMIT můžeme pomocí klíčových slov MAX a MIN určit, kdy byl přijat maximální a minimální počet zaměstnanců:
MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, MAX(B.salary) AS "Max. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
+-----------------+-------------+
| Name | Max. salary |
+-----------------+-------------+
| Facello, Georgi | 88958 |
| Simmel, Bezalel | 72527 |
| Bamford, Parto | 43699 |
+-----------------+-------------+
3 rows in set (0.02 sec)
MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, MIN(B.salary) AS "Min. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
+-----------------+-------------+
| Name | Min. salary |
+-----------------+-------------+
| Facello, Georgi | 60117 |
| Simmel, Bezalel | 65828 |
| Bamford, Parto | 40006 |
+-----------------+-------------+
3 rows in set (0.00 sec)
Dokážete na základě výše uvedených sad výsledků odhadnout, co vrátí níže uvedený dotaz?
MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, ROUND(AVG(B.salary), 2) AS "Avg. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
+-----------------+-------------+
| Name | Avg. salary |
+-----------------+-------------+
| Facello, Georgi | 75388.94 |
| Simmel, Bezalel | 68854.50 |
| Bamford, Parto | 43030.29 |
+-----------------+-------------+
3 rows in set (0.01 sec)
Pokud souhlasíte s tím, že bude vracet průměrný (jak je určen AVG) plat v průběhu času zaokrouhlený na 2 desetinná místa (jak je označeno ROUND), máte pravdu.
Pokud chceme zobrazit součet mezd seskupených podle zaměstnance a vrátit top 5, můžeme použít následující dotaz:
MariaDB [employees]> SELECT emp_no, SUM(salary) AS Salary FROM salaries GROUP BY emp_no ORDER BY Salary DESC LIMIT 5;
+--------+---------+
| emp_no | Salary |
+--------+---------+
| 109334 | 2553036 |
| 43624 | 2492873 |
| 66793 | 2383923 |
| 237542 | 2381119 |
| 47978 | 2374024 |
+--------+---------+
5 rows in set (2.22 sec)
Ve výše uvedeném dotazu jsou platy seskupeny podle zaměstnance a poté je proveden součet.
Dát to všechno dohromady
Naštěstí k vytvoření sestavy nemusíme spouštět dotaz po dotazu. Místo toho můžeme vytvořit skript se sérií příkazů SQL, které vrátí všechny potřebné sady výsledků.
Jakmile skript spustíme, vrátí požadované informace bez dalšího zásahu z naší strany. Vytvořme například soubor s názvem maxminavg.sql v aktuálním pracovním adresáři s následujícím obsahem:
--Select database
USE employees;
--Calculate maximum salaries
SELECT CONCAT(last_name, ', ', first_name) AS Name, MAX(B.salary) AS "Max. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
--Calculate minimum salaries
SELECT CONCAT(last_name, ', ', first_name) AS Name, MIN(B.salary) AS "Min. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
--Calculate averages, round to 2 decimal places
SELECT CONCAT(last_name, ', ', first_name) AS Name, ROUND(AVG(B.salary), 2) AS "Avg. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
Řádky začínající dvěma pomlčkami jsou ignorovány a jednotlivé dotazy se provádějí jeden po druhém. Tento skript můžeme spustit buď z příkazového řádku Linuxu:
mysql -u empadmin -p < maxminavg.sql
Enter password:
Name Max. salary
Facello, Georgi 88958
Simmel, Bezalel 72527
Bamford, Parto 43699
Name Min. salary
Facello, Georgi 60117
Simmel, Bezalel 65828
Bamford, Parto 40006
Name Avg. salary
Facello, Georgi 75388.94
Simmel, Bezalel 68854.50
Bamford, Parto 43030.29
nebo z příkazového řádku MariaDB:
mysql -u empadmin -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.14-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> source maxminavg.sql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
souhrn
V tomto článku jsme vysvětlili, jak používat několik funkcí MariaDB ke zpřesnění sad výsledků vrácených příkazy SELECT. Jakmile jsou definovány, lze do skriptu vložit více jednotlivých dotazů, aby se snáze provedl a snížilo se riziko lidské chyby.
Máte nějaké dotazy nebo návrhy k tomuto článku? Neváhejte nám napsat poznámku pomocí formuláře komentáře níže. Těšíme se na setkání s Vámi!