Aflați cum să utilizați mai multe funcții ale MySQL și MariaDB - Partea 2
Aceasta este a doua parte a unei serii de 2 articole despre elementele esențiale ale comenzilor MariaDB/MySQL. Vă rugăm să consultați articolul nostru anterior pe acest subiect înainte de a continua.
- Aflați elementele de bază MySQL/MariaDB pentru începători – Partea 1
În această a doua parte a seriei pentru începători MySQL/MariaDB, vom explica cum să limitați numărul de rânduri returnate de o interogare SELECT și cum să ordonăm setul de rezultate pe baza unei anumite condiții.
În plus, vom învăța cum să grupăm înregistrările și să efectuăm manipulări matematice de bază pe câmpuri numerice. Toate acestea ne vor ajuta să creăm un script SQL pe care îl putem folosi pentru a produce rapoarte utile.
Cerințe preliminare
Pentru a începe, vă rugăm să urmați acești pași:
1. Descărcați baza de date exemplu de angajați, care include șase tabele formate din 4 milioane de înregistrări în total.
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. Introduceți solicitarea MariaDB și creați o bază de date numită angajați:
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. Importați-l în serverul dvs. MariaDB după cum urmează:
MariaDB [(none)]> source employees.sql
Așteptați 1-2 minute până când baza de date eșantion este încărcată (rețineți că aici vorbim despre 4M înregistrări!).
4. Verificați dacă baza de date a fost importată corect, listând tabelele acesteia:
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. Creați un cont special pe care să îl utilizați cu baza de date angajați (simțiți-vă liber să alegeți alt nume de cont și parolă):
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
Acum conectați-vă ca utilizator empadmin în promptul 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
Asigurați-vă că toți pașii descriși în imaginea de mai sus au fost finalizați înainte de a continua.
Ordonarea și limitarea numărului de rânduri din setul de rezultate
Tabelul de salarii conține toate veniturile fiecărui angajat cu date de începere și de încheiere. Este posibil să dorim să vedem salariile pentru emp_no=10001 în timp. Acest lucru vă va ajuta să răspundeți la următoarele întrebări:
- A primit el/ea vreo mărire de salariu?
- Dacă da, când?
Executați următoarea interogare pentru a afla:
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)
Acum ce se întâmplă dacă trebuie să vedem ultimele 5 măriri? Putem face ORDER BY from_date DESC. Cuvântul cheie DESC indică faptul că dorim să sortăm setul de rezultate în ordine descrescătoare.
În plus, LIMIT 5 ne permite să returnăm numai primele 5 rânduri din setul de rezultate:
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)
De asemenea, puteți utiliza ORDER BY cu mai multe câmpuri. De exemplu, următoarea interogare va ordona setul de rezultate pe baza datei de naștere a angajatului în formă crescătoare (implicit) și apoi după nume de familie în formă alfabetică descrescătoare:
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)
Puteți vedea mai multe informații despre LIMIT aici.
Gruparea înregistrărilor/MAX, MIN, AVG și ROUND
După cum am menționat mai devreme, tabelul salarii conține veniturile fiecărui angajat în timp. Pe lângă LIMIT, putem folosi cuvintele cheie MAX și MIN pentru a determina când au fost angajați numărul maxim și minim de angajați:
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)
Pe baza setului de rezultate de mai sus, puteți ghici ce va returna interogarea de mai jos?
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)
Dacă sunteți de acord că va returna salariul mediu (așa cum este specificat de AVG) în timp, rotunjit la 2 zecimale (după cum este indicat de ROUND), aveți dreptate.
Dacă dorim să vedem suma salariilor grupate pe angajat și să returnăm primele 5, putem folosi următoarea interogare:
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)
În interogarea de mai sus, salariile sunt grupate pe angajat și apoi se realizează suma.
Adunând totul împreună
Din fericire, nu trebuie să rulăm interogare după interogare pentru a produce un raport. În schimb, putem crea un script cu o serie de comenzi SQL pentru a returna toate seturile de rezultate necesare.
Odată ce executăm scriptul, acesta va returna informațiile necesare fără intervenții suplimentare din partea noastră. De exemplu, să creăm un fișier numit maxminavg.sql în directorul de lucru curent cu următorul conținut:
--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;
Liniile care încep cu două liniuțe sunt ignorate, iar interogările individuale sunt executate una după alta. Putem executa acest script fie din linia de comandă Linux:
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
sau din promptul 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
rezumat
În acest articol am explicat cum să folosiți mai multe funcții MariaDB pentru a rafina seturile de rezultate returnate de instrucțiunile SELECT. Odată definite, mai multe interogări individuale pot fi inserate într-un script pentru a-l executa mai ușor și pentru a reduce riscul de eroare umană.
Aveți întrebări sau sugestii despre acest articol? Simțiți-vă liber să ne trimiteți o notă folosind formularul de comentarii de mai jos. Asteptam sa auzim de tine!