Apprenez à utiliser plusieurs fonctions de MySQL et MariaDB - Partie 2
Ceci est la deuxième partie d'une série de 2 articles sur l'essentiel des commandes MariaDB/MySQL. Veuillez vous référer à notre article précédent sur ce sujet avant de continuer.
- Apprenez les bases de MySQL/MariaDB pour les débutants – Partie 1
Dans cette deuxième partie de la série pour débutants MySQL/MariaDB, nous expliquerons comment limiter le nombre de lignes renvoyées par une requête SELECT et comment trier l'ensemble de résultats en fonction d'une condition donnée.
De plus, nous apprendrons comment regrouper les enregistrements et effectuer des manipulations mathématiques de base sur des champs numériques. Tout cela nous aidera à créer un script SQL que nous pourrons utiliser pour produire des rapports utiles.
Conditions préalables
Pour commencer, veuillez suivre ces étapes :
1. Téléchargez l'exemple de base de données employés, qui comprend six tables composées de 4 millions d'enregistrements au 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. Entrez l'invite MariaDB et créez une base de données nommée employés :
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. Importez-le dans votre serveur MariaDB comme suit :
MariaDB [(none)]> source employees.sql
Attendez 1 à 2 minutes jusqu'à ce que l'exemple de base de données soit chargé (n'oubliez pas que nous parlons ici de 4 millions enregistrements !).
4. Vérifiez que la base de données a été importée correctement en répertoriant ses tables :
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. Créez un compte spécial à utiliser avec la base de données des employés (n'hésitez pas à choisir un autre nom de compte et un autre mot de passe) :
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
Connectez-vous maintenant en tant qu'utilisateur empadmin à l'invite 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
Assurez-vous que toutes les étapes décrites dans l'image ci-dessus ont été terminées avant de continuer.
Ordre et limitation du nombre de lignes dans l'ensemble de résultats
Le tableau des salaires contient tous les revenus de chaque employé avec les dates de début et de fin. Nous souhaiterons peut-être consulter les salaires de emp_no=10001 au fil du temps. Cela aidera à répondre aux questions suivantes :
- A-t-il/elle obtenu des augmentations ?
- Si oui, quand ?
Exécutez la requête suivante pour le savoir :
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)
Et si nous avions besoin de consulter les 5 dernières augmentations ? Nous pouvons faire ORDER BY from_date DESC. Le mot-clé DESC indique que nous souhaitons trier l'ensemble de résultats par ordre décroissant.
De plus, LIMIT 5 nous permet de renvoyer uniquement les 5 premières lignes de l'ensemble de résultats :
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)
Vous pouvez également utiliser ORDER BY avec plusieurs champs. Par exemple, la requête suivante classera l’ensemble de résultats en fonction de la date de naissance de l’employé sous forme ascendante (valeur par défaut), puis selon les noms de famille sous forme alphabétique décroissante :
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)
Vous pouvez consulter plus d'informations sur LIMIT ici.
Regroupement des enregistrements/MAX, MIN, AVG et ROUND
Comme nous l'avons mentionné précédemment, la table salaires contient les revenus de chaque employé au fil du temps. Outre LIMIT, nous pouvons utiliser les mots-clés MAX et MIN pour déterminer quand le nombre maximum et minimum d'employés ont été embauchés :
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)
Sur la base des ensembles de résultats ci-dessus, pouvez-vous deviner ce que la requête ci-dessous renverra ?
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)
Si vous acceptez qu'il renvoie le salaire moyen (tel que spécifié par AVG) au fil du temps, arrondi à 2 décimales (comme indiqué par ROUND), vous avez raison.
Si nous voulons afficher la somme des salaires regroupés par employé et renvoyer les 5 premiers, nous pouvons utiliser la requête suivante :
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)
Dans la requête ci-dessus, les salaires sont regroupés par employé, puis la somme est effectuée.
Rassembler tout cela
Heureusement, nous n’avons pas besoin d’exécuter requête après requête pour produire un rapport. Au lieu de cela, nous pouvons créer un script avec une série de commandes SQL pour renvoyer tous les jeux de résultats nécessaires.
Une fois le script exécuté, il renverra les informations requises sans autre intervention de notre part. Par exemple, créons un fichier nommé maxminavg.sql dans le répertoire de travail actuel avec le contenu suivant :
--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;
Les lignes commençant par deux tirets sont ignorées et les requêtes individuelles sont exécutées l'une après l'autre. Nous pouvons exécuter ce script soit à partir de la ligne de commande 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
ou depuis l'invite 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
Résumé
Dans cet article, nous avons expliqué comment utiliser plusieurs fonctions MariaDB afin d'affiner les jeux de résultats renvoyés par les instructions SELECT. Une fois définies, plusieurs requêtes individuelles peuvent être insérées dans un script pour l'exécuter plus facilement et réduire le risque d'erreur humaine.
Avez-vous des questions ou des suggestions concernant cet article ? N'hésitez pas à nous envoyer une note en utilisant le formulaire de commentaires ci-dessous. Nous avons hâte d'avoir de tes nouvelles!