Améliorez vos connaissances en bases de données avec cette aide-mémoire MariaDB et MySQL

Lisez cet article et téléchargez notre aide-mémoire gratuite pour commencer à utiliser une base de données open source.

Lorsque vous écrivez une application ou en configurez une pour un serveur, vous devrez éventuellement stocker des informations persistantes. Parfois, un fichier de configuration, tel qu'un fichier INI ou YAML , fera l'affaire. D'autres fois, un format de fichier personnalisé conçu en XML ou JSON ou similaire est préférable.

Mais parfois, vous avez besoin de quelque chose qui puisse valider les entrées, rechercher rapidement des informations, établir des liens entre les données associées et, de manière générale, gérer efficacement le travail de vos utilisateurs. C'est pour cela qu'une base de données est conçue, et MariaDB (un fork de MySQL par certains de ses développeurs d'origine) est une excellente option. J'utilise MariaDB dans cet article, mais les informations s'appliquent également à MySQL.

Il est courant d'interagir avec une base de données via des langages de programmation. Pour cette raison, il existe des bibliothèques SQL pour Java, Python, Lua, PHP, Ruby, C++ et bien d'autres. Cependant, avant d'utiliser ces bibliothèques, il est utile de comprendre ce qui se passe avec le moteur de base de données et pourquoi votre choix de base de données est important. Cet article présente MariaDB et la commande mysql pour vous familiariser avec les bases de la façon dont une base de données gère les données.

Si vous n'avez pas encore MariaDB, suivez les instructions de mon article sur l'installation de MariaDB sous Linux. Si vous n'êtes pas sous Linux, utilisez les instructions fournies sur la page de téléchargement de MariaDB.

Interagissez avec MariaDB

Vous pouvez interagir avec MariaDB à l'aide de la commande mysql. Tout d'abord, vérifiez que votre serveur est opérationnel à l'aide de la sous-commande ping, en saisissant votre mot de passe MariaDB lorsque vous y êtes invité :

$ mysqladmin -u root -p ping
Enter password:
mysqld is alive

Pour faciliter l'exploration de SQL, ouvrez une session MariaDB interactive :

$ mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.
Commands end with ; or \g.
[...]
Type 'help;' or '\h' for help.
Type '\c' to clear the current input statement.

MariaDB [(none)]>

Cela vous place dans un sous-shell MariaDB et votre invite est désormais une invite MariaDB. Vos commandes Bash habituelles ne fonctionnent pas ici. Vous devez utiliser les commandes MariaDB. Pour voir une liste des commandes MariaDB, tapez help (ou simplement ?). Ce sont des commandes administratives pour votre shell MariaDB, elles sont donc utiles pour personnaliser votre shell, mais elles ne font pas partie du langage SQL.

Apprendre les bases de SQL

Le langage de requête structuré (SQL) doit son nom à ce qu'il fournit : une méthode permettant de se renseigner sur le contenu d'une base de données dans une syntaxe prévisible et cohérente afin de recevoir des résultats utiles. SQL se lit un peu comme une phrase anglaise ordinaire, bien qu'un peu robotique. Par exemple, si vous êtes connecté à un serveur de base de données et que vous avez besoin de comprendre avec quoi vous devez travailler, tapez SHOW DATABASES; et appuyez sur Entrée pour afficher les résultats.

Les commandes SQL se terminent par un point-virgule. Si vous oubliez le point-virgule, MariaDB suppose que vous souhaitez continuer votre requête sur la ligne suivante, où vous pouvez soit le faire, soit terminer la requête par un point-virgule.

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.000 sec)

Cela montre que quatre bases de données sont présentes : information_schema, mysql, performance_schema et test. Pour émettre des requêtes sur une base de données, vous devez sélectionner la base de données que vous souhaitez que MariaDB utilise. Cela se fait avec la commande MariaDB use. Une fois que vous avez choisi une base de données, votre invite MariaDB change pour refléter la base de données active.

MariaDB [(none)]> use test;
MariaDB [(test)]> 

Afficher les tables de la base de données

Les bases de données contiennent des tableaux, qui peuvent être visualisés de la même manière qu'une feuille de calcul : comme une série de lignes (appelées enregistrements dans une base de données) et de colonnes. L'intersection d'une ligne et d'une colonne est appelée un champ.

Pour voir les tables disponibles dans une base de données (vous pouvez les considérer comme des onglets dans une feuille de calcul multi-feuilles), utilisez à nouveau le mot-clé SQL SHOW :

MariaDB [(test)]> SHOW TABLES;
empty set

La base de données test n'a pas grand chose à regarder, utilisez donc la commande use pour basculer vers la base de données mysql.

MariaDB [(test)]> use mysql;
MariaDB [(mysql)]> SHOW TABLES;

+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| column_stats              |
| columns_priv              |
| db                        |
[...]
| time_zone_transition_type |
| transaction_registry      |
| user                      |
+---------------------------+
31 rows in set (0.000 sec)

Il y a beaucoup plus de tables dans cette base de données ! La base de données mysql est la base de données de gestion système pour cette instance MariaDB. Il contient des données importantes, notamment une structure d'utilisateurs complète pour gérer les privilèges de la base de données. Il s'agit d'une base de données importante et vous n'êtes pas toujours obligé d'interagir directement avec elle, mais il n'est pas rare de la manipuler dans des scripts SQL. Il est également utile de comprendre la base de données mysql lorsque vous apprenez MariaDB, car elle peut vous aider à démontrer certaines commandes SQL de base.

Examiner un tableau

La dernière table répertoriée dans la base de données mysql de cette instance est intitulée user. Ce tableau contient des données sur les utilisateurs autorisés à accéder à la base de données. Pour le moment, il n'y a qu'un utilisateur root, mais vous pouvez ajouter d'autres utilisateurs avec différents privilèges pour contrôler si chaque utilisateur peut afficher, mettre à jour ou créer des données. Pour avoir une idée de tous les attributs qu'un utilisateur MariaDB peut avoir, vous pouvez afficher les en-têtes de colonnes dans un tableau :

> SHOW COLUMNS IN user;
MariaDB [mysql]> SHOW columns IN user;
+-------------+---------------+------+-----+----------+
| Field       | Type          | Null | Key | Default  |
+-------------+---------------+------+-----+----------+
| Host        | char(60)      | NO   | PRI |          |
| User        | char(80)      | NO   | PRI |          |
| Password    | char(41)      | NO   |     |          |
| Select_priv | enum('N','Y') | NO   |     | N        |
| Insert_priv | enum('N','Y') | NO   |     | N        |
| Update_priv | enum('N','Y') | NO   |     | N        |
| Delete_priv | enum('N','Y') | NO   |     | N        |
| Create_priv | enum('N','Y') | NO   |     | N        |
| Drop_priv   | enum('N','Y') | NO   |     | N        |
[...]
47 rows in set (0.001 sec)

Créer un nouvel utilisateur

Que vous ayez besoin de l'aide d'un autre humain pour administrer une base de données ou que vous configuriez une base de données pour qu'un ordinateur l'utilise (par exemple, dans une installation WordPress, Drupal ou Joomla), il est courant d'avoir besoin d'un compte utilisateur supplémentaire dans MariaDB. . Vous pouvez créer un utilisateur MariaDB soit en l'ajoutant à la table user dans la base de données mysql, soit en utilisant le mot-clé SQL CREATE pour demander MariaDB pour le faire pour vous. Ce dernier propose quelques fonctions d'assistance pour que vous n'ayez pas à générer toutes les informations manuellement :

> CREATE USER 'tux'@'localhost' IDENTIFIED BY 'really_secure_password';

Afficher les champs du tableau

Vous pouvez afficher les champs et les valeurs dans une table de base de données avec le mot-clé SELECT. Dans cet exemple, vous avez créé un utilisateur appelé tux, sélectionnez donc les colonnes dans la table user :

> SELECT user,host FROM user;
+------+------------+
| user | host       |
+------+------------+
| root | localhost  |
[...]
| tux  | localhost  |
+------+------------+
7 rows in set (0.000 sec)

Accorder des privilèges à un utilisateur

En consultant la liste des colonnes de la table user, vous pouvez explorer le statut d'un utilisateur. Par exemple, le nouvel utilisateur tux n'a pas la permission de faire quoi que ce soit avec la base de données. À l'aide de l'instruction WHERE, vous pouvez afficher uniquement l'enregistrement de tux :

> SELECT user,select_priv,insert_priv,update_priv FROM user WHERE user='tux';
+------+-------------+-------------+-------------+
| user | select_priv | insert_priv | update_priv |
+------+-------------+-------------+-------------+
| tux  | N           | N           | N           |
+------+-------------+-------------+-------------+

Utilisez la commande GRANT pour modifier les autorisations des utilisateurs :

> GRANT SELECT on *.* TO 'tux'@'localhost';
> FLUSH PRIVILEGES;

Vérifiez votre modification :

> SELECT user,select_priv,insert_priv,update_priv FROM user WHERE user='tux';
+------+-------------+-------------+-------------+
| user | select_priv | insert_priv | update_priv |
+------+-------------+-------------+-------------+
| tux  | Y           | N           | N           |
+------+-------------+-------------+-------------+

L'utilisateur tux a désormais les privilèges nécessaires pour sélectionner des enregistrements dans toutes les tables.

Créer une base de données personnalisée

Jusqu'à présent, vous avez interagi uniquement avec les bases de données par défaut. La plupart des gens interagissent rarement avec les bases de données par défaut en dehors de la gestion des utilisateurs. Habituellement, vous créez une base de données et la remplissez avec des tables remplies de données personnalisées.

Créer une base de données MariaDB

Vous savez peut-être déjà comment créer une nouvelle base de données dans MariaDB. C'est un peu comme créer un nouvel utilisateur :

> CREATE DATABASE example;
Query OK, 1 row affected (0.000 sec)
> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| example            |
[...]

Faites de cette nouvelle base de données votre base de données active avec la commande use :

> use example;

Créer un tableau

La création d'un tableau est plus complexe que la création d'une base de données car vous devez définir les en-têtes de colonnes. MariaDB fournit de nombreuses fonctions pratiques que vous pouvez utiliser lors de la création de colonnes, notamment des définitions de types de données, des options d'incrémentation automatique, des contraintes pour éviter les valeurs vides, des horodatages automatisés, etc.

Voici un tableau simple pour décrire un ensemble d'utilisateurs :

> CREATE table IF NOT EXISTS member (
    -> id INT auto_increment PRIMARY KEY,
    -> name varchar(128) NOT NULL,
    -> startdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.030 sec)

Ce tableau fournit un identifiant unique à chaque ligne en utilisant une fonction d'incrémentation automatique. Il contient un champ pour le nom d'un utilisateur, qui ne peut pas être vide (ou null), et génère un horodatage lors de la création de l'enregistrement.

Remplissez cette table avec quelques exemples de données à l'aide du mot-clé SQL INSERT :

> INSERT INTO member (name) VALUES ('Alice');
Query OK, 1 row affected (0.011 sec)
> INSERT INTO member (name) VALUES ('Bob');
Query OK, 1 row affected (0.011 sec)
> INSERT INTO member (name) VALUES ('Carol');
Query OK, 1 row affected (0.011 sec)
> INSERT INTO member (name) VALUES ('David');
Query OK, 1 row affected (0.011 sec)

Vérifiez les données dans le tableau :

> SELECT * FROM member;
+----+-------+---------------------+
| id | name  | startdate           |
+----+-------+---------------------+
|  1 | Alice | 2020-10-03 15:25:06 |
|  2 | Bob   | 2020-10-03 15:26:43 |
|  3 | Carol | 2020-10-03 15:26:46 |
|  4 | David | 2020-10-03 15:26:51 |
+----+-------+---------------------+
4 rows in set (0.000 sec)

Ajouter plusieurs lignes à la fois

Créez maintenant un deuxième tableau :

> CREATE table IF NOT EXISTS linux (
    -> id INT auto_increment PRIMARY KEY,
    -> distro varchar(128) NOT NULL,
Query OK, 0 rows affected (0.030 sec)

Remplissez-le avec quelques exemples de données, cette fois en utilisant un petit raccourci VALUES afin de pouvoir ajouter plusieurs lignes en une seule commande. Le mot-clé VALUES attend une liste entre parenthèses, mais il peut prendre plusieurs listes séparées par des virgules :

> INSERT INTO linux (distro)
 -> VALUES ('Slackware'), ('RHEL'),('Fedora'),('Debian');
Query OK, 4 rows affected (0.011 sec)
Records: 4  Duplicates: 0  Warnings: 0
> SELECT * FROM linux;
+----+-----------+
| id | distro    |
+----+-----------+
|  1 | Slackware |
|  2 | RHEL      |
|  3 | Fedora    |
|  4 | Debian    |
+----+-----------+

Créer des relations entre les tables

Vous disposez désormais de deux tables, mais il n’existe aucune relation entre elles. Ils contiennent chacun des données indépendantes, mais vous devrez peut-être associer un membre de la première table à un élément spécifique répertorié dans la seconde.

Pour ce faire, vous pouvez créer une nouvelle colonne pour le premier tableau qui correspond à quelque chose dans le second. Étant donné que les deux tables ont été conçues avec des identifiants uniques (le champ id auto-incrémenté), le moyen le plus simple de les connecter est d'utiliser le champ id de l'une d'elles comme sélecteur pour le autre.

Créez une nouvelle colonne dans le premier tableau pour représenter une valeur dans le deuxième tableau :

> ALTER TABLE member ADD COLUMN (os INT);
Query OK, 0 rows affected (0.012 sec)
Records: 0  Duplicates: 0  Warnings: 0
> DESCRIBE member;
DESCRIBE member;
+-----------+--------------+------+-----+---------+------+
| Field     | Type         | Null | Key | Default | Extra|
+-----------+--------------+------+-----+---------+------+
| id        | int(11)      | NO   | PRI | NULL    | auto_|
| name      | varchar(128) | NO   |     | NULL    |      |
| startdate | timestamp    | NO   |     | cur[...]|      |
| os        | int(11)      | YES  |     | NULL    |      |
+-----------+--------------+------+-----+---------+------+

À l'aide des identifiants uniques de la table linux, attribuez une distribution à chaque membre. Étant donné que les enregistrements existent déjà, utilisez le mot-clé SQL UPDATE plutôt que INSERT. Plus précisément, vous souhaitez sélectionner une ligne, puis mettre à jour la valeur d'une colonne. Syntaxiquement, cela s'exprime un peu à l'envers, la mise à jour ayant lieu en premier et la sélection correspondant en dernier :

> UPDATE member SET os=1 WHERE name='Alice';
Query OK, 1 row affected (0.007 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Répétez ce processus pour les autres noms de la table member afin de la remplir avec des données. Pour plus de variété, attribuez trois distributions différentes sur les quatre lignes (en doublant une).

Joindre des tables

Maintenant que ces deux tables sont liées, vous pouvez utiliser SQL pour afficher les données associées. Il existe de nombreux types de jointures dans les bases de données, et vous pouvez toutes les essayer une fois que vous connaissez les bases. Voici une jointure de base pour corréler les valeurs trouvées dans le champ os de la table member au champ id de linuxtableau :

SELECT * FROM member JOIN linux ON member.os=linux.id;
+----+-------+---------------------+------+----+-----------+
| id | name  | startdate           | os   | id | distro    |
+----+-------+---------------------+------+----+-----------+
|  1 | Alice | 2020-10-03 15:25:06 |    1 |  1 | Slackware |
|  2 | Bob   | 2020-10-03 15:26:43 |    3 |  3 | Fedora    |
|  4 | David | 2020-10-03 15:26:51 |    3 |  3 | Fedora    |
|  3 | Carol | 2020-10-03 15:26:46 |    4 |  4 | Debian    |
+----+-------+---------------------+------+----+-----------+
4 rows in set (0.000 sec)

Les champs os et id forment la jointure.

Dans une application graphique, vous pouvez imaginer que le champ os puisse être défini par un menu déroulant dont les valeurs sont tirées du contenu du champ distro de la table linux. En utilisant des tables distinctes pour des ensembles de données uniques mais liés, vous garantissez la cohérence et la validité des données, et grâce à SQL, vous pouvez les associer dynamiquement ultérieurement.

Téléchargez l'aide-mémoire MariaDB et MySQL

MariaDB est une base de données de niveau entreprise. Il est conçu et s'est avéré être un moteur de base de données robuste, puissant et rapide. L'apprendre est une grande étape vers son utilisation pour faire des choses comme gérer des applications Web ou des bibliothèques de langages de programmation. Comme référence rapide lorsque vous utilisez MariaDB, téléchargez notre aide-mémoire MariaDB et MySQL.