MySQL : comment manipuler une base de données

MySQL est un système de gestion de bases de données relationneles, il fait partie avec PostgreSQL, Oracle et Microsoft SQL Server des logicels les plus utilisés dans le monde pour gérer des bases de données.

Je vais essayer de vous montrer les fonctionnalités de MySQL par l'intermédiaire de cet article.

C'est quoi une base de données ?

Une base de données permet de stocker des informations, elles sont très utilisées, et particulièrement dans le monde de l'informatique, on peut imaginer par exemple une base de données regrouppant les identifiants et mots de passe d'utilisateurs d'un site web ou une application.

Une base de données est organisée en regrouppant une ou plusieurs tables permettant de séparrer différentes informations. Ces tables contiennent des colonnes afin d'associer une valeur à un nom ou mot-clé.

Imaginons une base de données test, dans laquelle se trouve la table orders, avec comme colonnes order_id, title, order_date et status. Voici le contenu de cette table :

mysql> select * from orders; #permet d'afficher le contenu de la table 'orders'
+----------+---------------------+------------+--------+
| order_id | title               | order_date | status |
+----------+---------------------+------------+--------+
|        1 | Rubik's Cube        | 2019-05-16 |      1 |
|        2 | Bluetooth headphone | 2019-06-05 |      3 |
|        3 | USB key             | 2019-08-11 |      1 |
+----------+---------------------+------------+--------+

On remarque la présence de 3 commandes dans la base de données, mais on imagine que dans la vraie vie il y en a bien plus.

Commençons à créer notre propre base de données MySQL !

Créer notre première base de données

Tout d'abord il faut un serveur MySQL à disposition, vous pouvez soit utiliser un logiciel, une page web (comme phpmyadmin) ou tout faire en lignes de commandes. Ici j'ai décidé de tout faire en lignes de commandes afin de comprendre et expliquer ce que je fais avec ma base de données. J'utilise Docker pour faire tourner le serveur, mais vous pouvez très bien utiliser une machine virtuelle.

Création de la base de données

Comme dit plus haut, une base de données se compose de table(s), une fois connecté avec votre serveur MySQL, vous pourrez taper vos commandes, pour créer une base de données :

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.18 sec)

On peut vérifier la présence de la nouvelle base de données sur le système :

mysql> SHOW DATABASES; #permet d'afficher toutes les bases de données du serveur
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               | #<-- on voit bien notre base 'test'
+--------------------+
5 rows in set (0.00 sec)

Création d'une table

Nous allons maintenant voir comment créer une table dans notre base de données. Tous d'abord nous devons spécifier que nous travaillons avec la nouvelle base de données :

mysql> USE test;

Afin de créer une table, nous devons d'abord savoir ce que nous allons y stocker, car nous devons donner un type de valeur à enregistrer dans les différentes colonnes, il existe un grand nombre de types, essayons d'en utiliser quelque uns : INT, TEXT et DATE.

  • INT : entier sur 4 octets
  • TEXT : chaîne de charactères
  • DATE : chaîne de charactères à entrer sous le format AAAA-MM-JJ

Imaginons la strucutre suivante : une colonne order_id contenant un identifiant unique permettant de retrouver une commande d'un client, une colonne title afin de connaître l'article commandé, une colonne order_date afin de savoir la date de la commande et enfin une colonne status pour avoir un aperçu de la commande (on peut imaginer que 0 signifie commande expédiée, 1 signifie commande en cours de livraison etc...).

#syntaxe pour créer une table :
CREATE TABLE table_name (
    row1_name TYPE,
    row2_name TYPE,
    #...
);

#création de la table 'orders'
CREATE TABLE orders (
    order_id INT,
    title TEXT,
    order_date DATE,
    status INT
);

#ou alors en une seule ligne :
CREATE TABLE orders (order_id INT, title TEXT, order_date DATE, status INT);

Note : ne pas oublier le point virgule à la fin de chaque commande SQL :^)

Vérifions que notre table est bien créée :

mysql> SHOW TABLES; #permet d'afficher les tables de la base de données actuelle
+----------------+
| Tables_in_test | #<-- on voit le nom de la base de données actuelle
+----------------+
| orders         | #<-- la nouvelle table est bien présente
+----------------+
1 row in set (0.01 sec)

Commandes de base

Voici un apreçu des commandes "basiques" en SQL pour administrer votre base de données.

SELECT

J'utilise cette commande depuis le début de l'article mais je n'ai pas expliquer son fonctionnement. On peut afficher toutes sortes de valeurs avec un SELECT.

#syntaxe
SELECT column1, column2, column3 FROM table_name WHERE condition;

#exemples
//affiche tout le contenu de la table 'orders'
SELECT * from orders;
//affiche tout le contenu de la table 'orders' où l'identifiant 'order_id' est inférieur à 50
SELECT * from orders WHERE order_id < 50;
//affiche l'identifiant 'order_id' de la table 'orders' où la date 'order_date' est le 15 mars 2019
SELECT order_id FROM orders WHERE order_date = "2019-03-15";

INSERT

Cette commande permet d'ajouter des éléments (rows) dans une table.

#syntaxe
INSERT INTO table_name (column1, column2, column3) VALUES (column1_value, column2_value, column3_value);

#exemples
//ajoute une ligne à la table 'orders' avec comme valeur la clé associée, ici on a la clé order_id qui vaudra 1
INSERT INTO orders (order_id, title, order_date, status) VALUES (1, "Rubik's Cube", "2019-05-16", 1);
//permet d'ajouter plusieurs lignes d'un coup
INSERT INTO orders (*) VALUES (2, "Bluetooth headphone", "2019-06-05", 3), (3, "USB key", "2019-08-11", 1);

UPDATE

Vous aurrez besoin de cette commande si vous voullez modifier la valeur d'une ou plusieurs colonnes/lignes de votre table.

#syntaxe
UPDATE table_name SET column1=value1, column2=value2, column3=value3 WHERE condition;

#exemple
//modifie le titre et la date où l'identifiant 'order_id' est 3
UPDATE orders SET title="USB hub 5 ports", order_date="2019-05-12" WHERE order_id=3;

DELETE

Comme son nom l'indique, vous pouvez supprimer une ou plusieurs lignes d'une table.

#syntaxe
DELETE FROM table_name WHERE condition;

#exemple
//supprime les lignes de la table 'orders' qui sont plus anciens que le 10 mai 2019
DELETE FROM orders WHERE order_date > "2019-05-10";

Commandes et mots clé utiles

En plus de ces quelques commandes de base, il existe de nombreux tricks à apprendre pour maîtriser le langage SQL, je vais essayer de lister les commandes qui me semblent les plus utilisées pour l'administration de bases de données.

UNION

Cette comande permet de mettre bout à bout les résultats de plusieurs requêtes SQL utilisant des SELECT.

#syntaxe
SELECT column1, column2 FROM table_name WHERE condition UNION SELECT column3, column4 FROM table_name WHERE condition;

#exemples
//affiche le titre et la date des lignes où l'identifiant 'order_id' est 3 et 4
SELECT title, order_date FROM orders WHERE order_id=3 UNION SELECT title, order_date FROM orders WHERE order_id=4;
//affiche le titre de la ligne où l'identifiant 'order_id' est 3 et affiche tous les identifiants 'order_id' de la table 'orders'
SELECT title FROM orders WHERE order_id=3 UNION SELECT order_id FROM orders;

AND (&), OR (|) et NOT (!)

Ces mots clé vont permettre d'optimiser certaines requêtes, ce sont des mots pour créer des conditions. Peut s'utiliser avec toutes les requêtes SQL.

#syntaxe
SELECT column1, column2 FROM table_name WHERE condition;

#exemples
//affiche le contenu de la table 'orders' où l'identifiant 'order_id' n'est ni 3 ni 4
SELECT * FROM orders WHERE order_id != 3 AND order_id != 4;
//modifie le statut d'une commande à 3 si son identifiant 'order_id' est 1 ou si la date est inférieure au 15 février 2019
UPDATE orders SET status=3 WHERE order_id=1 | order_date < "2019-02-15";