Exercices

Pour parfaire ses connaissance de MySQL, voici quelques exercices à réaliser :

1. Création d’un utilisateur
2. Importation et rétro-ingénierie d’une base de données
3. Requêtes SQL
4. Procédures stockées
5. Triggers
6. Transactions
7. Verrous

Exercice 1 : Création d’un utilisateur

Créez un utilisateur zoo avec le mot de passe zoo ayant tous les droits sur la base de données zoo.

Étapes :

1. Connectez-vous à MySQL en tant que root

mysql -u root -p

2. Créez l’utilisateur zoo

CREATE USER 'zoo'@'localhost' IDENTIFIED BY 'zoo';

Si MySQL 8.0 ou supérieur, il est possible que ce mot de passe, trop simple, ne soit pas accepté. Pour contourner ce problème, vous pouvez utiliser le plugin mysql_native_password :

CREATE USER 'zoo'@'localhost' IDENTIFIED WITH mysql_native_password BY 'zoo';

3. Donnez tous les droits à l’utilisateur zoo sur la base de données zoo

GRANT ALL PRIVILEGES ON zoo.* TO 'zoo'@'localhost';
FLUSH PRIVILEGES;
exit;

Astuce

L’instruction GRANT pourra être affinée en fonction des besoins de l’application pour limiter les droits de l’utilisateur.

Pour vérifier que l’utilisateur a bien été créé, vous pouvez vous reconnecter à MySQL en tant qu’utilisateur zoo :

mysql -u zoo -pzoo

Danger

Les terminaux comme Bash, Zsh, PowerShell, etc. peuvent conserver l’historique des commandes saisies…

Il est donc recommandé de ne pas saisir de mot de passe en clair dans un terminal ou de supprimer l’historique des commandes après avoir saisi un mot de passe.

Le zoo

Pour la suite des exercices, nous allons utiliser une base de données nommée zoo.

Vous pouvez télécharger le code SQL de cette base ici.

Exercice 2 : Importation de la base de données

Pour importer la base de données, vous pouvez utiliser la commande suivante :

mysql -u zoo -pzoo < zoo.sql

Remarque : cette commande va fonctionner car nous avons créé un utilisateur zoo avec les droits nécessaires sur la base de données zoo.

Rétro-ingénierie

Grâce à la commande db2dbml, vous pouvez générer un fichier DBML à partir de la base de données zoo.

db2dbml mysql 'mysql://zoo:zoo@localhost:3306/zoo' -o zoo.dbml

Prenez le temps de lire le fichier zoo.dbml pour comprendre la structure de la base de données.

Exercice 3 : Requêtes SQL

1. Affichez tous les animaux du zoo
2. Affichez les repas ainsi que les animaux qui ont pu y avoir accès
3. Affichez les animaux qui ont plus de 22 ans
4. Extraire les données temporelles de la date de naissance des animaux (date, jour, jour du mois, jour de la semaine, nom du jour, jour de l’année, mois, année, heure, minute, seconde, semaine de l’année, quart d’heure, milliseconde, etc.)

Solutions

zoo-dql.sql

-- 1. Affichez tous les animaux du zoo
SELECT
    *
FROM
    animal;

-- 2. Affichez les repas ainsi que les animaux qui ont pu y avoir accès
SELECT
    date_repas,
    nom_vulgaire,
    nom,
    designation
FROM
    repas
    JOIN aliment ON repas.aliment_id = aliment.id
    JOIN mange ON mange.aliment_id = aliment.id
    JOIN animal ON mange.animal_id = animal.id
    JOIN espece ON animal.espece_id = espece.id
ORDER BY
    date_repas,
    nom_vulgaire,
    nom,
    designation;

-- 3. Affichez les animaux qui ont plus de 22 ans
SELECT
    *
FROM
    animal
WHERE
    date_naissance < (CURDATE() - INTERVAL 22 YEAR);

-- 4. Extraire les données temporelles de la date de naissance des animaux (date, jour, jour du mois, jour de la semaine, nom du jour, jour de l'année)
SELECT
    nom,
    DATE(date_naissance) AS date_naiss,
    DAY(date_naissance) AS jour,
    DAYOFMONTH(date_naissance) AS jour,
    DAYOFWEEK(date_naissance) AS jour_sem,
    WEEKDAY(date_naissance) AS jour_sem2,
    DAYNAME(date_naissance) AS nom_jour,
    DAYOFYEAR(date_naissance) AS jour_annee,
    WEEK(date_naissance) AS semaine,
    WEEKOFYEAR(date_naissance) AS semaine2,
    YEARWEEK(date_naissance) AS semaine_annee,
    MONTH(date_naissance) AS numero_mois,
    MONTHNAME(date_naissance) AS nom_mois,
    YEAR(date_naissance) AS annee,
    TIME(date_naissance) AS time_complet,
    HOUR(date_naissance) AS heure,
    MINUTE(date_naissance) AS minutes,
    SECOND(date_naissance) AS secondes
FROM
    Animal;

Exercice 4 : Procédures stockées

1. Créez une procédure stockée get_animals qui affiche tous les animaux du zoo
2. Créez une procédure stockée get_animals_by_age qui affiche les animaux qui ont plus de 22 ans
3. Créez une procédure stockée get_animals_by_given_age qui affiche les animaux qui ont plus de l’âge passé en paramètre

Proposez aussi le code pour appeler ces procédures.

Solutions

zoo-sp.sql

-- 1. Créez une procédure stockée `get_animals` qui affiche tous les animaux du zoo

DELIMITER //
CREATE PROCEDURE get_animals()
BEGIN
    SELECT * FROM animal;
END //
DELIMITER ;

-- 2. Créez une procédure stockée `get_animals_by_age` qui affiche les animaux qui ont plus de 22 ans

DELIMITER //
CREATE PROCEDURE get_animals_by_age()
BEGIN
    SELECT * FROM animal
    WHERE date_naissance < (CURDATE() - INTERVAL 22 YEAR);
END //
DELIMITER ;

-- 3. Créez une procédure stockée `get_animals_by_given_age` qui affiche les animaux qui ont plus de l'âge passé en paramètre

DELIMITER //
CREATE PROCEDURE get_animals_by_given_age(IN age INT)
BEGIN
    SELECT * FROM animal
    WHERE date_naissance < (CURDATE() - INTERVAL age YEAR);
END //
DELIMITER ;

-- Proposez aussi le code pour appeler ces procédures.

-- Appel de la procédure `get_animals`
CALL get_animals();

-- Appel de la procédure `get_animals_by_age`
CALL get_animals_by_age();

-- Appel de la procédure `get_animals_by_given_age`
CALL get_animals_by_given_age(20);

Exercice 5 : Triggers

1. Un trigger qui annule l’insertion d’un animal si la date de naissance est supérieure à la date actuelle
2. Un trigger qui diminue la date de naissance d’un animal de 1 jour lorsque un repas est ajouté pour la zone à laquelle il appartient

Proposez aussi des requêtes pour tester ces triggers.

Solutions

zoo-tr.sql

-- 1. Un trigger qui annule l'insertion d'un animal si la date de naissance est supérieure à la date actuelle

DELIMITER //
CREATE TRIGGER annuler_insertion_animal
BEFORE INSERT ON animal
FOR EACH ROW
BEGIN
    IF NEW.date_naissance > CURDATE() THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Impossible d''ajouter un animal né dans le futur';
    END IF;
END;
//
DELIMITER ;

-- requête pour tester le trigger
INSERT INTO animal (nom, date_arrivee, date_naissance, numero, espece_id, zone_id) VALUES ('Toto', CURRENT_DATE, CURRENT_DATE + INTERVAL 1 DAY, 1, 17, 1);

-- 2. Un trigger qui diminue la date de naissance d'un animal de 1 jour lorsque un repas est ajouté pour la zone à laquelle il appartient

DELIMITER //
CREATE TRIGGER diminuer_date_naissance
AFTER INSERT ON repas
FOR EACH ROW
BEGIN
    UPDATE animal
    SET date_naissance = date_naissance - INTERVAL 1 DAY
    WHERE zone_id = NEW.zone_id;
END;
//
DELIMITER ;

-- requête pour tester le trigger

-- Selection des animaux de la zone 1
SELECT * FROM animal WHERE zone_id = 1;

-- insertion d'un repas pour la zone 1 à une date aléatoire
INSERT INTO repas (date_repas, quantite, responsable_id, aliment_id, zone_id) VALUES (CURRENT_DATE - INTERVAL FLOOR(RAND() * 14) DAY, 100, 1, 1, 1);

-- Selection des animaux de la zone 1
SELECT * FROM animal WHERE zone_id = 1;

Exercice 6 : Transactions

1. Créez une transaction qui ajoute un nouvel animal et un repas pour ce dernier
2. Créez une transaction qui ajoute un nouvel animal et un repas pour ce dernier, mais qui échoue lors de l’ajout du repas
3. Créez une transaction qui ajoute un nouvel animal, puis un autre, puis qui annule l’ajout du second animal, puis qui ajoute un troisième animal

Solutions

zoo-tx.sql

-- 1. Créez une transaction qui ajoute un nouvel animal et un repas pour ce dernier

START TRANSACTION;

-- Ajout d'un nouvel animal
INSERT INTO animal (nom, date_arrivee, date_naissance, numero, espece_id, zone_id) VALUES ('Toto', CURRENT_DATE, CURRENT_DATE - INTERVAL 1 DAY, 1, 17, 1);

-- Ajout d'un repas pour l'animal
INSERT INTO repas (date_repas, quantite, responsable_id, aliment_id, zone_id) VALUES (CURRENT_DATE - INTERVAL FLOOR(RAND() * 14) DAY, 100, 1, 1, 1);

COMMIT;


-- 2. Créez une transaction qui ajoute un nouvel animal et un repas pour ce dernier, mais qui échoue lors de l'ajout du repas

START TRANSACTION;

-- Ajout d'un nouvel animal
INSERT INTO animal (nom, date_arrivee, date_naissance, numero, espece_id, zone_id) VALUES ('Toto', CURRENT_DATE, CURRENT_DATE - INTERVAL 1 DAY, 1, 17, 1);

-- Ajout d'un repas pour l'animal
INSERT INTO repas (date_repas, quantite, responsable_id, aliment_id, zone_id) VALUES (CURRENT_DATE - INTERVAL FLOOR(RAND() * 14) DAY, 100, 1, 1, 1);

ROLLBACK;


-- 3. Créez une transaction qui ajoute un nouvel animal, puis un autre, puis qui annule l'ajout du second animal, puis qui ajoute un troisième animal

START TRANSACTION;

-- Ajout d'un nouvel animal
INSERT INTO animal (nom, date_arrivee, date_naissance, numero, espece_id, zone_id) VALUES ('Toto', CURRENT_DATE, CURRENT_DATE - INTERVAL 1 DAY, 1, 17, 1);

SAVEPOINT ajout_animal_1;

-- Ajout d'un autre animal
INSERT INTO animal (nom, date_arrivee, date_naissance, numero, espece_id, zone_id) VALUES ('Titi', CURRENT_DATE, CURRENT_DATE - INTERVAL 1 DAY, 2, 17, 1);

-- Annulation de l'ajout du second animal
ROLLBACK TO ajout_animal_1;

-- Ajout d'un autre animal

INSERT INTO animal (nom, date_arrivee, date_naissance, numero, espece_id, zone_id) VALUES ('Tutu', CURRENT_DATE, CURRENT_DATE - INTERVAL 1 DAY, 3, 17, 1);

COMMIT;

Exercice 7 : Verrous

1. Vérouillez les tables animal et espece en lecture, puis essayez d’insérer un nouvel animal depuis un autre terminal
2. Vérouillez la table animal et espece en écriture, puis essayez de sélectionner tous les animaux depuis un autre terminal

Rédigez des requêtes pour tester ces verrous.

Solutions

zoo-lk.sql

-- 1. Vérouillez les tables `animal` et `espece` en lecture

UNLOCK TABLES;
LOCK TABLES animal READ, espece READ;

-- requête à executer depuis une autre session, par exemple avec l'utilisateur `root`
SELECT * FROM animal;
INSERT INTO animal (nom, date_arrivee, date_naissance, numero, espece_id, zone_id) VALUES ('Toto', CURRENT_DATE, CURRENT_DATE - INTERVAL 1 DAY, 1, 17, 1); -- devrait être bloqué tant que les tables sont verrouillées


-- 2. Vérouillez la table `animal` en écriture

LOCK TABLES animal WRITE, espece READ;

-- requête à executer depuis une autre session, par exemple avec l'utilisateur `root`
SELECT * FROM animal, espece;
INSERT INTO animal (nom, date_arrivee, date_naissance, numero, espece_id, zone_id) VALUES ('Toto', CURRENT_DATE, CURRENT_DATE - INTERVAL 1 DAY, 1, 17, 1); -- devrait être bloqué tant que les tables sont verrouillées

UNLOCK TABLES;