Créer et manipuler des bases de données avec MariaDB

Dans mon précédent article, nous avons vu l’installation et la configuration initiale du serveur de bases de données MariaDB sous Rocky Linux 8. Aujourd’hui nous allons prendre en main la création et la manipulation de base d’une série de bases de données.

Nous allons faire fi des interfaces graphiques comme PHPMyAdmin et autres clicodromes pour gérer notre serveur. Au lieu de cela, nous utiliserons la console en ligne de commande, et au passage nous apprendrons un peu de SQL (Structured Query Language), le langage servant à manipuler les bases de données.

Télécharger des bases de données

Pour l’instant nous n’avons pas de base de données sous la main, mais il est possible d’en importer une depuis le Web. Le site SQL.sh propose une série de bases de données libres et gratuites au téléchargement en SQL, CSV ou XML :

• liste des départements de France
• liste des pays du monde
• liste des villes de France
• liste des fournisseurs d’adresses e-mail jetables
• etc.

Atelier pratique : les départements

Sur mon serveur, je crée un répertoire ~/sql pour ranger mes fichiers SQL téléchargés :

$ mkdir -v sql
mkdir: created directory 'sql'
$ cd sql/

• Je repère le lien de téléchargement Liste des départements français au format SQL.
• Je copie l’adresse du lien.
• Je télécharge le fichier directement sur mon serveur en utilisant wget.

$ wget https://sql.sh/ressources/sql-departement-france/departement.sql

Je me connecte au moniteur MariaDB en tant qu’utilisateur root MariaDB :

$ mysql -u root -p
Enter password: **********
Welcome to the MariaDB monitor.  
...
MariaDB [(none)]>

Je crée une base de données departements :

MariaDB [(none)]> create database departements;
Query OK, 1 row affected (0.000 sec)

J’affiche la liste des bases de données et je repère ma nouvelle base :

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| departements       |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)

Pour ne pas utiliser le compte root MariaDB pour la connexion, je crée un utilisateur deptuser qui aura tous les droits sur la base departements. Attention, le mot de passe défini apparaîtra en clair dans le moniteur MariaDB lors de la saisie :

MariaDB [(none)]> grant all on departements.*
    -> to deptuser@localhost
    -> identified by '**********';
Query OK, 0 rows affected (0.000 sec)

Vous remarquerez que j’ai noté cette requête SQL un peu plus longue sur plusieurs lignes, pour plus de lisibilité. J’aurais très bien pu l’écrire sur une seule ligne. Chaque requête SQL se termine par un point-virgule. Tant que vous n’avez pas saisi ce dernier et que vous appuyez sur Entrée, le moniteur MariaDB affiche une invite de continuation et attend la suite de la requête.

Je quitte le moniteur MariaDB :

MariaDB [(none)]> quit;
Bye

Ma base de données est créée, mais pour l’instant c’est un conteneur vide et non structuré, sans la moindre donnée.

Affichez le fichier departement.sql et examinez sommairement son contenu :

$ less departement.sql

Même si vous ne comprenez pas tout, vous devinez grâce à un peu de linguistique intuitive que ce fichier se charge essentiellement de deux choses :

• Construire la structure de la base (CREATE TABLE IF NOT EXISTS `departement`)
• La remplir de données (INSERT INTO `departement` (...) VALUE ...)

Il ne me reste plus qu’à exécuter ces requêtes sur ma base pour la construire et la remplir de données. Je vais me connecter en tant que deptuser pour cette opération :

$ mysql -u deptuser -p departements < departement.sql 
Enter password: **********

Je me reconnecte au moniteur MariaDB :

$ mysql -u deptuser -p
Enter password: **********
Welcome to the MariaDB monitor.  
...
MariaDB [(none)]> 

Étant donné que je suis connecté en tant qu’utilisateur non privilégié, je ne vois que les bases de données auxquelles j’ai effectivement accès :

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| departements       |
| information_schema |
+--------------------+
2 rows in set (0.001 sec)

Je souhaite utiliser la base departements :

MariaDB [(none)]> use departements;
...
Database changed

Je ne connais pas encore la structure de ma base. Je commence par afficher les tables qu’elle contient :

MariaDB [departements]> show tables;
+------------------------+
| Tables_in_departements |
+------------------------+
| departement            |
+------------------------+
1 row in set (0.000 sec)

Ma base est donc constituée d’une seule table departement. À quoi ressemble-t-elle ?

MariaDB [departements]> describe departement;
+---------------------------+--------------+------+-----+---------+----------------+
| Field                     | Type         | Null | Key | Default | Extra          |
+---------------------------+--------------+------+-----+---------+----------------+
| departement_id            | int(11)      | NO   | PRI | NULL    | auto_increment |
| departement_code          | varchar(3)   | YES  | MUL | NULL    |                |
| departement_nom           | varchar(255) | YES  |     | NULL    |                |
| departement_nom_uppercase | varchar(255) | YES  |     | NULL    |                |
| departement_slug          | varchar(255) | YES  | MUL | NULL    |                |
| departement_nom_soundex   | varchar(20)  | YES  | MUL | NULL    |                |
+---------------------------+--------------+------+-----+---------+----------------+
6 rows in set (0.001 sec)

Ma table departement est constituée d’une série de colonnes aux noms assez parlants comme departement_code, departement_nom, etc.

Pour commencer, j’affiche la liste complète de tous les départements avec les codes respectifs :

MariaDB [departements]> select departement_code, departement_nom
    -> from departement;
+------------------+-------------------------+
| departement_code | departement_nom         |
+------------------+-------------------------+
| 01               | Ain                     |
| 02               | Aisne                   |
| 03               | Allier                  |
| 05               | Hautes-Alpes            |
| 04               | Alpes-de-Haute-Provence |
| 06               | Alpes-Maritimes         |
| 07               | Ardèche                 |
| 08               | Ardennes                |
| ...              | ...                     |

À partir de là, je peux affiner les requêtes. Par exemple, je souhaite savoir quel est le département associé au code 24 :

MariaDB [departements]> select departement_code, departement_nom
    -> from departement
    -> where departement_code = "24";
+------------------+-----------------+
| departement_code | departement_nom |
+------------------+-----------------+
| 24               | Dordogne        |
+------------------+-----------------+
1 row in set (0.000 sec)

Ou alors je souhaite connaître le numéro du département du Finistère :

MariaDB [departements]> select departement_code, departement_nom
    -> from departement
    -> where departement_nom = "Finistère";
+------------------+-----------------+
| departement_code | departement_nom |
+------------------+-----------------+
| 29               | Finistère       |
+------------------+-----------------+
1 row in set (0.000 sec)

Enfin, je quitte le moniteur MariaDB :

MariaDB [departements]> quit;
Bye

Atelier pratique : les e-mails jetables

Pour consolider nos compétences, nous allons les mettre en pratique en créant et en exploitant une autre base de données : celle des e-mails jetables.

• Repérez le lien Liste des emails jetables au format SQL.
• Récupérez le fichier email_jetable-sql et rangez-le dans le répertoire ~/sql du serveur.
• Connectez-vous en tant que root au moniteur MariaDB.
• Créez la base vide junkmail.
• Définissez un utilisateur non privilégié junkmailuser qui aura accès à cette base.
• Importez la structure et le contenu de la base depuis le fichier téléchargé.
• Connectez-vous au moniteur MariaDB en tant qu’utilisateur non privilégié.
• Affichez les bases auxquelles vous avez accès.
• Utilisez la base junkmail.

Ma base contient une seule table email_disposable :

MariaDB [junkmail]> show tables;
+--------------------+
| Tables_in_junkmail |
+--------------------+
| email_disposable   |
+--------------------+
1 row in set (0.000 sec)

Cette table est constituée de deux colonnes id et domain :

MariaDB [junkmail]> describe email_disposable;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| domain | varchar(255)     | YES  |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+
2 rows in set (0.001 sec)

J’affiche la liste complète des fournisseurs d’adresses e-mail jetables :

MariaDB [junkmail]> select domain from email_disposable;
+--------------------------------+
| domain                         |
+--------------------------------+
| 0815.ru0clickemail.com         |
| 0-mail.com                     |
| 0wnd.net                       |
| 0wnd.org                       |
| 10minutemail.com               |
| 20minutemail.com               |
| 2prong.com                     |
| 3d-painting.com                |
| 4warding.com                   |
| 4warding.net                   |
| 4warding.org                   |
| 9ox.net                        |
| a-bc.net                       |
| ag.us.to                       |
| ...                            |

Cette liste est assez longue. Je peux filtrer les résultats en affichant uniquement les domaines qui commencent par la lettre A :

MariaDB [junkmail]> select domain from email_disposable
    -> where domain like "a%";
+---------------+
| domain        |
+---------------+
| a-bc.net      |
| ag.us.to      |
| amilegit.com  |
| anonbox.net   |
| anonymbox.com |
| antichef.com  |
| antichef.net  |
| antispam.de   |
+---------------+
8 rows in set (0.001 sec)

Ou encore, j’affiche uniquement les domaines en .org :

MariaDB [junkmail]> select domain from email_disposable
    -> where domain like "%.org";
+-------------------+
| domain            |
+-------------------+
| 0wnd.org          |
| 4warding.org      |
| dodgit.org        |
| guerillamail.org  |
| guerrillamail.org |
| inboxclean.org    |
| ipoo.org          |
| jetable.org       |
| mailzilla.org     |
| spambob.org       |
| spamcowboy.org    |
| spamfree24.org    |
| spamgourmet.org   |
| wegwerfmail.org   |
| wh4f.org          |
+-------------------+
15 rows in set (0.001 sec)

Enfin, je quitte le moniteur MariaDB :

MariaDB [departements]> quit;
Bye

Supprimer une base de données

La suppression d’une base de données s’effectue à l’aide de la commande drop. Connectez-vous au moniteur MariaDB :

$ mysql -u root -p
Enter password: **********
Welcome to the MariaDB monitor.  
...
MariaDB [(none)]> 

En théorie, rien ne vous empêche de vous connecter en tant qu’utilisateur non privilégié. Mais dans ce cas, vous ne pourrez supprimer que la ou les bases de données auxquelles vous avez pleinement accès.

Affichez la liste des bases de données :

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| departements       |
| information_schema |
| junkmail           |
| mysql              |
| performance_schema |
+--------------------+
5 rows in set (0.001 sec)

Supprimez la base junkmail et vérifiez le résultat de l’opération :

MariaDB [(none)]> drop database junkmail;
Query OK, 1 row affected (0.023 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| departements       |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.001 sec)

Procédez de même pour la base departements :

MariaDB [(none)]> drop database departements;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.001 sec)

Nous disposons encore de deux utilisateurs non privilégiés stockés dans la table user de la base mysql :

MariaDB [(none)]> use mysql;
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
MariaDB [mysql]> select user, host, password from user;
+--------------+-----------+-------------------------------------------+
| user         | host      | password                                  |
+--------------+-----------+-------------------------------------------+
| root         | localhost | *F424CF5D03C9AAE965E86FBF001AAB078229EDEB |
| junkmailuser | localhost | *F424CF5D03C9AAE965E86FBF001AAB078229EDEB |
| deptuser     | localhost | *F424CF5D03C9AAE965E86FBF001AAB078229EDEB |
+--------------+-----------+-------------------------------------------+
3 rows in set (0.000 sec)

Nous pouvons faire un peu de ménage et supprimer ces deux utilisateurs :

MariaDB [mysql]> delete from user where user = "junkmailuser";
Query OK, 1 row affected (0.001 sec)

MariaDB [mysql]> delete from user where user = "deptuser";
Query OK, 1 row affected (0.001 sec)

MariaDB [mysql]> select user, host, password from user;
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *F424CF5D03C9AAE965E86FBF001AAB078229EDEB |
+------+-----------+-------------------------------------------+
1 row in set (0.000 sec)

Lire la suite : Configurer un accès distant pour MariaDB

La rédaction de cette documentation demande du temps et des quantités significatives de café espresso. Vous appréciez ce blog ? Offrez un café au rédacteur en cliquant sur la tasse.