Créer et administrer une base de données : CREATE, ALTER, DROP et droits d’accès

Partie 1 — Créer une base et des tables

CREATE DATABASE et CREATE TABLE définissent la structure d’une base, avant même d’y placer la moindre donnée.

1.1 — CREATE DATABASE : créer une base

CREATE DATABASE lycee;
USE lycee;

1.2 — Les types de données

Type	Contenu
INT	Nombre entier
DECIMAL(4,2)	Nombre décimal (4 chiffres, 2 après la virgule)
VARCHAR(n)	Texte de longueur variable, max n caractères
DATE	Date (AAAA-MM-JJ)
BOOLEAN	Vrai / Faux

1.3 — Les contraintes d’intégrité

• PRIMARY KEY : identifie une ligne de façon unique et non nulle
• FOREIGN KEY : fait référence à la clé primaire d’une autre table
• NOT NULL : la colonne doit obligatoirement contenir une valeur
• UNIQUE : aucune valeur ne peut être dupliquée dans cette colonne
• DEFAULT : valeur utilisée automatiquement si aucune n’est fournie
• CHECK : valide une condition personnalisée

1.4 — Exemple complet

CREATE TABLE classe (
    id_classe INT PRIMARY KEY,
    nom_classe VARCHAR(20) NOT NULL UNIQUE
);

CREATE TABLE etudiant (
    id_etudiant INT PRIMARY KEY,
    nom VARCHAR(30) NOT NULL,
    prenom VARCHAR(30) NOT NULL,
    moyenne DECIMAL(4,2) DEFAULT NULL CHECK (moyenne BETWEEN 0 AND 20),
    id_classe INT,
    FOREIGN KEY (id_classe) REFERENCES classe(id_classe)
);

Q1. Ajoute une colonne date_naissance de type DATE, obligatoire, à la table etudiant ci-dessus (écris l’instruction complète). La table est encore vide à ce stade.

1.5 — Manipuler les données : INSERT, UPDATE, DELETE

Une table vide ne sert à rien : les instructions du DML permettent d’y écrire des données (INSERT), de les corriger (UPDATE) et de les supprimer (DELETE). Elles complètent SELECT, vu au cours précédent.

INSERT INTO ajoute une ligne. On insère d’abord les lignes de classe, puis celles de etudiant : la clé étrangère id_classe doit pointer vers une classe qui existe déjà.

INSERT INTO classe (id_classe, nom_classe)
VALUES (1, 'SISR1');

INSERT INTO etudiant
    (id_etudiant, nom, prenom, moyenne, id_classe)
VALUES (1, 'Martin', 'Léa', 14.5, 1);

UPDATE modifie des lignes existantes, DELETE en supprime. Dans les deux cas, la clause WHERE désigne les lignes concernées.

UPDATE etudiant
SET moyenne = 15
WHERE id_etudiant = 1;

DELETE FROM etudiant
WHERE id_etudiant = 5;

Q2. Écris les deux requêtes qui ajoutent l’élève (6, Leroy, Sam) dans la classe 3, sans moyenne, puis lui attribuent la moyenne 9.

1.6 — Bilan de la partie 1

Instruction	Rôle
CREATE DATABASE nom;	Crée une nouvelle base de données
CREATE TABLE nom (…);	Crée une table avec ses colonnes et leurs types
PRIMARY KEY, FOREIGN KEY	Définissent les clés et les liens entre tables
NOT NULL, UNIQUE, DEFAULT, CHECK	Fiabilisent les données dès leur insertion
INSERT, UPDATE, DELETE	Ajoutent, modifient ou suppriment des lignes (WHERE indispensable pour UPDATE et DELETE)

Partie 2 — Faire évoluer une structure

Une base de données vit : ALTER TABLE permet de modifier une structure existante sans tout reconstruire.

2.1 — Ajouter une colonne

ALTER TABLE etudiant
ADD COLUMN telephone VARCHAR(15);

La nouvelle colonne est ajoutée à toutes les lignes existantes, avec la valeur NULL par défaut (sauf si DEFAULT est précisé).

2.2 — Modifier ou supprimer une colonne

ALTER TABLE etudiant MODIFY COLUMN telephone VARCHAR(20);
ALTER TABLE etudiant DROP COLUMN telephone;

2.3 — Contraintes et renommage

ALTER TABLE etudiant ADD CONSTRAINT uq_tel UNIQUE (telephone);
ALTER TABLE etudiant RENAME COLUMN nom TO nom_famille;
ALTER TABLE etudiant RENAME TO eleve;

2.4 — Piège fréquent : modifier une colonne avec des données existantes

Réduire ou changer le type d’une colonne qui contient déjà des données peut échouer, ou tronquer silencieusement des valeurs.

-- Si nom contient déjà « Chevrollier-Dupont-Martin » (25 caractères)
ALTER TABLE etudiant MODIFY COLUMN nom VARCHAR(10);
-- échoue, ou tronque la valeur selon le SGBD

Q3. D’après toi, quelle vérification faire AVANT de réduire la taille d’une colonne existante ?

2.5 — Bilan de la partie 2

Instruction	Rôle
ADD COLUMN	Ajoute une nouvelle colonne à une table existante
MODIFY COLUMN / ALTER COLUMN	Change le type ou la taille d’une colonne
DROP COLUMN	Supprime définitivement une colonne et ses données
RENAME COLUMN / RENAME TO	Renomme une colonne ou une table

Partie 3 — Supprimer proprement

Trois instructions permettent de supprimer, mais à des niveaux très différents : les distinguer évite des erreurs graves.

3.1 — DROP TABLE et DROP DATABASE

DROP TABLE etudiant;
DROP DATABASE lycee;

3.2 — TRUNCATE, DELETE, DROP : ne pas confondre

	TRUNCATE	DELETE	DROP
Portée	Toutes les lignes	Lignes filtrées (ou toutes)	La table entière
Structure conservée ?	Oui	Oui	Non
Type d’instruction	DDL	DML	DDL
Annulable (ROLLBACK) ?	Non (ou rarement)	Oui, en transaction	Non

3.3 — Suppression et clés étrangères

Impossible de supprimer une ligne de classe si des étudiants y sont encore rattachés, sauf si on prévoit ce cas.

• ON DELETE RESTRICT : refuse la suppression tant que des lignes y font référence (comportement par défaut)
• ON DELETE CASCADE : supprime automatiquement les lignes qui font référence à la ligne supprimée
• ON DELETE SET NULL : remet la clé étrangère à NULL dans les lignes qui y faisaient référence

FOREIGN KEY (id_classe)
    REFERENCES classe(id_classe)
    ON DELETE CASCADE;

Q4. Écris la requête qui supprime la classe SISR1 (id_classe = 1) de la table classe.

3.4 — Bonnes pratiques avant de supprimer

• Toujours tester d’abord le WHERE d’un DELETE avec un SELECT identique.
• Sauvegarder avant tout DROP ou toute modification de structure en production.
• Préférer une transaction (BEGIN … COMMIT / ROLLBACK) pour pouvoir annuler en cas d’erreur.

Partie 4 — La gestion des droits

Utilisateurs, privilèges et sécurité des accès à une base de données.

4.1 — Pourquoi gérer les droits ?

4.2 — Créer un utilisateur SQL

CREATE USER 'vie_scolaire'@'localhost'
IDENTIFIED BY 'un_mot_de_passe_solide';

• 'utilisateur'@'hôte' : l’hôte précise depuis où la connexion est autorisée.
• À ce stade, l’utilisateur existe mais n’a aucun droit.

4.3 — GRANT : accorder des privilèges

GRANT SELECT ON lycee.etudiant TO 'vie_scolaire'@'localhost';
GRANT SELECT, INSERT, UPDATE ON lycee.* TO 'vie_scolaire'@'localhost';
GRANT ALL PRIVILEGES ON lycee.* TO 'admin_lycee'@'localhost';

4.4 — Les niveaux de granularité

Niveau	Syntaxe
Serveur	GRANT … ON *.* TO …
Base de données	GRANT … ON lycee.* TO …
Table	GRANT … ON lycee.etudiant TO …
Colonne	GRANT SELECT (nom) ON lycee.etudiant TO …

4.5 — REVOKE : retirer des privilèges

REVOKE DELETE ON lycee.etudiant FROM 'vie_scolaire'@'localhost';

Q5. Écris la requête qui retire tous les privilèges de l’utilisateur ‘ancien_stagiaire’@‘localhost’ sur la base lycee.

4.6 — WITH GRANT OPTION

Par défaut, un utilisateur qui reçoit un privilège ne peut pas le retransmettre. WITH GRANT OPTION lève cette limite : à utiliser avec prudence.

GRANT SELECT ON lycee.* TO 'responsable'@'localhost'
WITH GRANT OPTION;

4.7 — Les rôles : regrouper des privilèges

CREATE ROLE 'role_lecture';
GRANT SELECT ON lycee.* TO 'role_lecture';
CREATE USER 'prof_martin'@'localhost' IDENTIFIED BY '...';
GRANT 'role_lecture' TO 'prof_martin'@'localhost';

4.8 — Exemple concret : les comptes du lycée

Compte	Droits
role_lecture (enseignants)	SELECT uniquement, sur toutes les tables
vie_scolaire (service scolarité)	SELECT, INSERT, UPDATE sur etudiant, classe (pas DELETE)
admin_lycee (administrateur)	ALL PRIVILEGES, avec WITH GRANT OPTION

4.9 — Bonnes pratiques de sécurité

Synthèse — Ce qu’il faut retenir

Auto-évaluation rapide

Q1. Quelle instruction crée une nouvelle table ?

Q2. Quelle est la différence essentielle entre TRUNCATE TABLE et DROP TABLE ?

Q3. Quelle instruction accorde un privilège à un utilisateur ?

Q4. Qu’est-ce que le principe du moindre privilège ?