Module 3Protéger la base de données MySQL
Chapitre 12 sur 12
Examen blanc et fiche mémo
Un examen blanc corrigé sur 40 points et la fiche mémo des syntaxes MySQL.
- ≈ 100 min
- 2 leçons
- 4 exercices
- TP
Module 3Protéger la base de données MySQL
Chapitre 12 sur 12
Un examen blanc corrigé sur 40 points et la fiche mémo des syntaxes MySQL.
Objectifs
Avec ce chapitre, tu sauras…
Leçon 1
Le contexte : la base ecole_db d'un centre de formation.
CREATE DATABASE ecole_db;
USE ecole_db;
CREATE TABLE filiere (id INT PRIMARY KEY AUTO_INCREMENT, nom VARCHAR(50) NOT NULL UNIQUE);
CREATE TABLE stagiaire (
id INT PRIMARY KEY AUTO_INCREMENT,
nom VARCHAR(50) NOT NULL,
prenom VARCHAR(50),
date_naissance DATE,
id_filiere INT,
moyenne DECIMAL(4,2),
FOREIGN KEY (id_filiere) REFERENCES filiere(id)
) ENGINE = InnoDB;
CREATE TABLE note (
id_stagiaire INT, module VARCHAR(10), valeur DECIMAL(4,2),
PRIMARY KEY (id_stagiaire, module),
FOREIGN KEY (id_stagiaire) REFERENCES stagiaire(id)
) ENGINE = InnoDB;
INSERT INTO filiere (nom) VALUES ('Développement digital'), ('Infrastructure digitale');
INSERT INTO stagiaire (nom, prenom, date_naissance, id_filiere) VALUES
('Rami', 'Ahmed', '2004-03-23', 1), ('Kamali', 'Ali', '2003-11-02', 1), ('Fahmi', 'Sara', '2005-01-15', 2);
INSERT INTO note VALUES (1, 'M203', 15), (1, 'M204', 12), (2, 'M203', 8), (2, 'M204', 11), (3, 'M203', 17);Leçon 2
| Notion | Syntaxe | ||||
|---|---|---|---|---|---|
| LDD | CREATE TABLE t (…) ENGINE = InnoDB; · ALTER TABLE t ADD col type / MODIFY COLUMN / CHANGE COLUMN a b type / DROP COLUMN / RENAME TO; · DROP TABLE t; | ||||
| Vue / table temporaire | CREATE VIEW v AS SELECT …; · CREATE TEMPORARY TABLE tmp SELECT …; | ||||
| Délimiteur | DELIMITER $$ … END $$ DELIMITER ; | ||||
| Fonction | `CREATE FUNCTION f(p type) RETURNS type DETERMINISTIC | READS SQL DATA BEGIN … RETURN x; END· appel :SELECT f(…)` | |||
| Procédure | CREATE PROCEDURE p(IN a type, OUT b type, INOUT c type) BEGIN … END · CALL p(1, @b, @c); SELECT @b; | ||||
| Variables | DECLARE v type DEFAULT x; · SET v = …; · SELECT col INTO v FROM …; · session : @v | ||||
| Conditions | IF … THEN … ELSEIF … ELSE … END IF; · CASE x WHEN … THEN … ELSE … END CASE; | ||||
| Boucles | lbl: LOOP … LEAVE lbl; … ITERATE lbl; END LOOP lbl; · WHILE c DO … END WHILE; · REPEAT … UNTIL c END REPEAT; | ||||
| Transaction | START TRANSACTION; … COMMIT; / ROLLBACK; · SET autocommit = 0; | ||||
| Événement | CREATE EVENT e ON SCHEDULE EVERY 1 DAY STARTS … DO …; | ||||
| Handler | `DECLARE CONTINUE | EXIT HANDLER FOR 1062 | SQLSTATE '23000' | NOT FOUND | SQLEXCEPTION instruction;` |
| Erreurs | DECLARE nom CONDITION FOR …; · SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '…'; · RESIGNAL; | ||||
| Curseur | variables → DECLARE c CURSOR FOR SELECT …; → handler NOT FOUND → OPEN c; → FETCH c INTO …; → CLOSE c; · FOR UPDATE | ||||
| Déclencheur | `CREATE TRIGGER t BEFORE | AFTER INSERT | UPDATE | DELETE ON table FOR EACH ROW …·NEW(INSERT, UPDATE),OLD` (UPDATE, DELETE) | |
| Optimisation | EXPLAIN SELECT … · CREATE [UNIQUE] INDEX i ON t(col); · DROP INDEX i ON t; · PREPARE s FROM '… ?'; EXECUTE s USING @v; DEALLOCATE PREPARE s; | ||||
| Sauvegarde | mysqldump -u root -p --routines --events base > f.sql · mysql -u root -p base < f.sql | ||||
| Utilisateurs | CREATE USER 'u'@'h' IDENTIFIED BY 'mdp'; · ALTER USER … IDENTIFIED BY …; · RENAME USER …; · DROP USER …; | ||||
| Droits | GRANT SELECT, UPDATE (col) ON base.table TO 'u'@'h'; · REVOKE … ON … FROM …; · SHOW GRANTS FOR …; | ||||
| Rôles | CREATE ROLE r; · GRANT … TO r; · GRANT r TO u; · SET ROLE r; · REVOKE r FROM u; |
1Une fonction MySQL renvoie…
2DECLARE CONTINUE HANDLER FOR NOT FOUND sert surtout avec…
3Un paramètre OUT…
4Dans un trigger UPDATE, OLD contient…
5ROLLBACK annule…
6EXPLAIN montre type = ALL : il faut sans doute…
7'prof'@'192.168.1.%' se connecte…
8SIGNAL SQLSTATE '45000' sert à…
Les erreurs qui coûtent des points
DELIMITER avant un programme de plusieurs instructionsCOUNT (*) avec un espace, ou ELSE IF au lieu de ELSEIFDEFAULT (NULL → boucle infinie)BEGIN au lieu de START TRANSACTION dans une procédureUPDATE / DELETE sans WHEREON table FOR EACH ROW oublié dans un déclencheur(1) Écris la commande qui sauvegarde ecole_db avec ses procédures et déclencheurs. (1 pt) (2) Crée le rôle r_formateur qui peut lire toutes les tables d'ecole_db et modifier la table note. (2 pts) (3) Crée l'utilisateur prof1 (réseau 192.168.1.%) avec ce rôle par défaut, puis retire-lui le droit de modifier note… sans toucher au rôle des autres formateurs. (2 pts)
(1) Écris la fonction uf_mention(p_moy DECIMAL(4,2)) qui renvoie « Très bien » (≥ 16), « Bien » (≥ 14), « Assez bien » (≥ 12), « Passable » (≥ 10) ou « Non admis ». (4 pts) (2) Écris la procédure sp_stagiaires_filiere(IN p_filiere VARCHAR(50), OUT p_nb INT) qui affiche les stagiaires d'une filière (nom, prénom) et renvoie leur nombre. (4 pts) (3) Appelle-la pour « Développement digital » et affiche le nombre. (2 pts)
Écris la procédure sp_calculer_moyennes() qui parcourt les stagiaires avec un curseur, calcule pour chacun la moyenne de ses notes et l'enregistre dans stagiaire.moyenne. Tout le traitement est fait dans une transaction : en cas d'erreur, tout est annulé (EXIT HANDLER FOR SQLEXCEPTION). Affiche ensuite les stagiaires avec leur moyenne et leur mention (uf_mention).
(1) Écris le déclencheur tr_note_valide qui refuse (SIGNAL) une note inférieure à 0 ou supérieure à 20, à l'insertion et à la modification (deux déclencheurs). (4 pts) (2) Écris le déclencheur tr_journal_note qui enregistre dans journal_note (id_stagiaire, module, ancienne, nouvelle, date_modif) chaque modification de note. (3 pts)