Codes SQL Essentiels – Cours Gestion des Bases de Données
Licence d'Excellence – Semestre 6 – FSJES Meknès | Pr. Aicha MAJDA
=== Partie 1 – Scripts DDL de Base ===
-- Création de la base de données
CREATE DATABASE IF NOT EXISTS gestion_RH DEFAULT CHARACTER SET utf8mb4;
-- Sélection de la BD
USE gestion_RH;
-- Table services
CREATE TABLE services (
id_service INT AUTO_INCREMENT PRIMARY KEY,
nom_service VARCHAR(50) NOT NULL UNIQUE
);
-- Table employes
CREATE TABLE employes (
id_emp INT AUTO_INCREMENT PRIMARY KEY,
nom VARCHAR(50),
prenom VARCHAR(50),
age INT CHECK (age BETWEEN 18 AND 65),
id_service INT,
FOREIGN KEY (id_service) REFERENCES services(id_service)
ON UPDATE CASCADE ON DELETE SET NULL
);
-- Ajout champ e■mail
ALTER TABLE employes ADD email VARCHAR(100) UNIQUE;
-- Suppression colonne
ALTER TABLE employes DROP COLUMN email;
-- Suppression table
DROP TABLE IF EXISTS employes;
=== Partie 2 – Requêtes DML & DQL ===
-- Insertion de données
INSERT INTO services(nom_service) VALUES ('Comptabilité'), ('RH'), ('IT');
INSERT INTO employes(nom, prenom, age, id_service)
VALUES ('FARID', 'Sara', 29, 2);
-- Sélection simple
SELECT * FROM employes;
-- Sélection conditionnelle
SELECT nom, prenom FROM employes WHERE age > 30;
-- Jointure interne (INNER JOIN)
SELECT [Link], s.nom_service
FROM employes e
INNER JOIN services s ON e.id_service = s.id_service;
-- Jointure externe (LEFT JOIN)
SELECT s.nom_service, [Link]
FROM services s
LEFT JOIN employes e ON s.id_service = e.id_service;
-- Agrégation
SELECT id_service, AVG(age) AS age_moyen
FROM employes
GROUP BY id_service
HAVING AVG(age) > 30;
-- Mise à jour
UPDATE employes SET age = age + 1 WHERE id_emp = 1;
-- Suppression
DELETE FROM employes WHERE id_emp = 10;
-- Transaction
START TRANSACTION;
UPDATE employes SET age = age - 1 WHERE id_emp = 1;
ROLLBACK; -- ou COMMIT;
=== Partie 3 – Cas Pratiques : Scripts de Création ===
-- A. Gestion de la scolarité
CREATE DATABASE IF NOT EXISTS scolarite;
USE scolarite;
CREATE TABLE departement (
id_dep INT AUTO_INCREMENT PRIMARY KEY,
nom_dep VARCHAR(60) UNIQUE NOT NULL
);
CREATE TABLE cours (
id_cours INT AUTO_INCREMENT PRIMARY KEY,
intitule VARCHAR(100) NOT NULL,
id_dep INT NOT NULL,
FOREIGN KEY(id_dep) REFERENCES departement(id_dep)
);
CREATE TABLE etudiant (
apogee CHAR(8) PRIMARY KEY,
nom VARCHAR(50),
prenom VARCHAR(50),
genre ENUM('H','F'),
date_naiss DATE
);
CREATE TABLE inscription (
apogee CHAR(8),
id_cours INT,
annee_scolaire CHAR(9),
note DECIMAL(4,2),
PRIMARY KEY(apogee, id_cours, annee_scolaire),
FOREIGN KEY(apogee) REFERENCES etudiant(apogee),
FOREIGN KEY(id_cours) REFERENCES cours(id_cours)
);
-- Exemple de requête
SELECT [Link], [Link], [Link]
FROM inscription i
JOIN etudiant e ON [Link] = [Link]
JOIN cours c ON c.id_cours = i.id_cours
WHERE i.annee_scolaire = '2024/2025';
-- B. Bibliothèque
CREATE DATABASE IF NOT EXISTS bibliotheque;
USE bibliotheque;
CREATE TABLE auteur(
id_auteur INT AUTO_INCREMENT PRIMARY KEY,
nom VARCHAR(60)
);
CREATE TABLE livre(
id_livre INT AUTO_INCREMENT PRIMARY KEY,
titre VARCHAR(120),
annee INT
);
CREATE TABLE ecrire (
id_livre INT,
id_auteur INT,
PRIMARY KEY(id_livre, id_auteur),
FOREIGN KEY(id_livre) REFERENCES livre(id_livre),
FOREIGN KEY(id_auteur) REFERENCES auteur(id_auteur)
);
CREATE TABLE client(
id_client INT AUTO_INCREMENT PRIMARY KEY,
nom VARCHAR(60)
);
CREATE TABLE emprunter(
id_livre INT,
id_client INT,
date_emprunt DATE,
date_retour DATE,
PRIMARY KEY(id_livre, id_client, date_emprunt),
FOREIGN KEY(id_livre) REFERENCES livre(id_livre),
FOREIGN KEY(id_client) REFERENCES client(id_client)
);
-- Livres en retard
SELECT [Link], [Link], e.date_emprunt
FROM emprunter e
JOIN client c ON c.id_client = e.id_client
JOIN livre l ON l.id_livre = e.id_livre
WHERE e.date_retour IS NULL AND DATEDIFF(CURDATE(), e.date_emprunt) > 30;
=== Partie 4 – Sécurité & Administration ===
-- Création utilisateur applicatif
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'mot_de_passe';
GRANT SELECT, INSERT, UPDATE, DELETE ON scolarite.* TO 'app_user'@'localhost';
FLUSH PRIVILEGES;
-- Sauvegarde
mysqldump -u root -p scolarite > scolarite_backup.sql
-- Restauration
mysql -u root -p scolarite < scolarite_backup.sql
-- Index pour accélérer les recherches
CREATE INDEX idx_nom ON etudiant(nom);
=== Partie 5 – Cheatsheet Rapide ===
-- Comparaison
SELECT * FROM t WHERE col LIKE '%abc%';
SELECT * FROM t WHERE col BETWEEN 10 AND 20;
-- Limiter résultats
SELECT * FROM t LIMIT 10 OFFSET 20;
-- Sous■requête corrélée
SELECT e.*, (SELECT COUNT(*) FROM inscription i WHERE [Link] = [Link]) AS nb_inscriptions
FROM etudiant e;
-- Vue
CREATE VIEW v_notes AS
SELECT apogee, id_cours, note FROM inscription WHERE note IS NOT NULL;
-- Procédure stockée
DELIMITER //
CREATE PROCEDURE ajouter_service(IN lib VARCHAR(50))
BEGIN
INSERT INTO services(nom_service) VALUES(lib);
END //
DELIMITER ;