COURS SQL
Cours SQL pour BTS SIO option SLAM (1ère et
2ème année)
Table des matières
1. Introduction à SQL
2. Bases de données relationnelles
3. Langage de Définition des Données (DDL)
4. Langage de Manipulation des Données (DML)
5. Requêtes avancées
6. Fonctions et procédures stockées
7. Transactions et gestion de la concurrence
8. Optimisation des performances
9. Sécurité des bases de données
10. Exercices pratiques et corrigés
Introduction à SQL
Qu'est-ce que SQL ?
SQL (Structured Query Language) est un langage standardisé utilisé pour communiquer avec
les bases de données relationnelles. Il permet de créer, modifier, interroger et gérer des bases
de données.
Histoire et évolution de SQL
Origines dans les années 1970 chez IBM
Standardisation par ANSI et ISO
Évolutions majeures : SQL-86, SQL-92, SQL:1999, SQL:2003, SQL:2011, etc.
Importance de SQL dans le développement d'applications
Rôle central dans la gestion des données
Utilisation dans de nombreux domaines : web, entreprise, analyse de données, etc.
Exercice 1 : Comprendre l'importance de SQL
Question : Citez trois domaines d'application où SQL est couramment utilisé et expliquez
brièvement pourquoi SQL est important dans chacun de ces domaines.
Corrigé :
1. Développement Web :
SQL permet de stocker et récupérer des données utilisateur, des contenus de site,
etc.
Il facilite la gestion des sessions utilisateur et des authentifications.
2. Gestion d'entreprise :
SQL est utilisé pour gérer les inventaires, les commandes clients, la comptabilité, etc.
Il permet d'analyser les données pour la prise de décision.
3. Analyse de données :
SQL facilite l'extraction et la transformation de grandes quantités de données.
Il permet de réaliser des requêtes complexes pour l'analyse statistique et le reporting.
Bases de données relationnelles
Concepts fondamentaux
Tables, colonnes, lignes
Clés primaires et étrangères
Relations entre tables
Normalisation des données
Systèmes de Gestion de Base de Données Relationnelles
(SGBDR)
MySQL, PostgreSQL, Oracle, SQL Server, etc.
Comparaison des différents SGBDR
Modélisation de données
Diagrammes Entité-Association
Modèle relationnel
Exercice 2 : Modélisation d'une base de données simple
Énoncé : Créez un modèle Entité-Association pour un système de gestion de bibliothèque avec
les entités suivantes : Livre, Auteur, Emprunteur, Emprunt.
Corrigé :
[Livre]
- ID_Livre (PK)
- Titre
- Année_publication
- ISBN
|
|--< [Auteur_Livre]
| - ID_Livre (FK)
| - ID_Auteur (FK)
|
[Auteur]
- ID_Auteur (PK)
- Nom
- Prénom
[Emprunteur]
- ID_Emprunteur (PK)
- Nom
- Prénom
- Adresse
|
|--< [Emprunt]
- ID_Emprunt (PK)
- ID_Livre (FK)
- ID_Emprunteur (FK)
- Date_emprunt
- Date_retour_prévue
Langage de Définition des Données (DDL)
Création de bases de données
CREATE DATABASE nom_de_la_base;
Création de tables
CREATE TABLE nom_de_la_table (
colonne1 type_de_données contraintes,
colonne2 type_de_données contraintes,
...
);
Types de données courants
Numériques : INT, FLOAT, DECIMAL
Chaînes de caractères : VARCHAR, CHAR, TEXT
Dates et heures : DATE, TIME, DATETIME
Booléens : BOOLEAN
Contraintes
PRIMARY KEY
FOREIGN KEY
UNIQUE
NOT NULL
CHECK
Modification de la structure des tables
ALTER TABLE
DROP TABLE
Exercice 3 : Création d'une table
Énoncé : Créez une table "Livre" avec les colonnes suivantes : ID_Livre (clé primaire), Titre,
Année_publication, ISBN, et ID_Auteur (clé étrangère vers une table Auteur).
Corrigé :
CREATE TABLE Livre (
ID_Livre INT PRIMARY KEY AUTO_INCREMENT,
Titre VARCHAR(255) NOT NULL,
Année_publication INT,
ISBN VARCHAR(13) UNIQUE,
ID_Auteur INT,
FOREIGN KEY (ID_Auteur) REFERENCES Auteur(ID_Auteur)
);
Langage de Manipulation des Données (DML)
Insertion de données
INSERT INTO nom_de_la_table (colonne1, colonne2, ...)
VALUES (valeur1, valeur2, ...);
Sélection de données
SELECT colonne1, colonne2, ...
FROM nom_de_la_table
WHERE condition;
Mise à jour de données
UPDATE nom_de_la_table
SET colonne1 = valeur1, colonne2 = valeur2, ...
WHERE condition;
Suppression de données
DELETE FROM nom_de_la_table
WHERE condition;
Exercice 4 : Manipulation de données
Énoncé :
1. Insérez un nouveau livre dans la table "Livre".
2. Sélectionnez tous les livres publiés après 2000.
3. Mettez à jour le titre d'un livre spécifique.
4. Supprimez un livre de la table.
Corrigé :
-- 1. Insertion
INSERT INTO Livre (Titre, Année_publication, ISBN, ID_Auteur)
VALUES ('Le Petit Prince', 1943, '9782070612758', 1);
-- 2. Sélection
SELECT * FROM Livre WHERE Année_publication > 2000;
-- 3. Mise à jour
UPDATE Livre
SET Titre = 'Le Petit Prince (Édition spéciale)'
WHERE ISBN = '9782070612758';
-- 4. Suppression
DELETE FROM Livre WHERE ISBN = '9782070612758';
Requêtes avancées
Jointures
INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN
Sous-requêtes
Fonctions d'agrégation
COUNT, SUM, AVG, MAX, MIN
Groupement et filtrage
GROUP BY
HAVING
Unions et intersections
Exercice 5 : Requêtes avancées
Énoncé : Écrivez une requête qui retourne le nombre de livres par auteur, en n'affichant que
les auteurs ayant écrit plus de 5 livres, triés par nombre de livres décroissant.
Corrigé :
SELECT [Link], [Link]énom, COUNT(*) as Nombre_de_livres
FROM Auteur a
INNER JOIN Livre l ON a.ID_Auteur = l.ID_Auteur
GROUP BY a.ID_Auteur
HAVING Nombre_de_livres > 5
ORDER BY Nombre_de_livres DESC;
Fonctions et procédures stockées
Création de fonctions
CREATE FUNCTION nom_fonction (paramètres)
RETURNS type_retour
BEGIN
-- Corps de la fonction
RETURN valeur;
END;
Création de procédures stockées
CREATE PROCEDURE nom_procédure (paramètres)
BEGIN
-- Corps de la procédure
END;
Utilisation des fonctions et procédures
Exercice 6 : Fonction stockée
Énoncé : Créez une fonction qui calcule l'âge d'un livre en années à partir de son année de
publication.
Corrigé :
DELIMITER //
CREATE FUNCTION age_livre(annee_publication INT)
RETURNS INT
BEGIN
DECLARE age INT;
SET age = YEAR(CURDATE()) - annee_publication;
RETURN age;
END //
DELIMITER ;
-- Utilisation de la fonction
SELECT Titre, age_livre(Année_publication) as Age FROM Livre;
Transactions et gestion de la concurrence
Principes ACID
Atomicité
Cohérence
Isolation
Durabilité
Gestion des transactions
START TRANSACTION;
-- Opérations SQL
COMMIT; -- ou ROLLBACK;
Niveaux d'isolation
Verrous et deadlocks
Exercice 7 : Transaction
Énoncé : Écrivez une transaction qui transfère un livre d'un emprunteur à un autre, en
s'assurant que le livre est bien disponible avant le transfert.
Corrigé :
START TRANSACTION;
DECLARE @livre_id INT = 1;
DECLARE @ancien_emprunteur INT = 2;
DECLARE @nouvel_emprunteur INT = 3;
-- Vérifier si le livre est bien emprunté par l'ancien emprunteur
IF EXISTS (SELECT 1 FROM Emprunt WHERE ID_Livre = @livre_id AND
ID_Emprunteur = @ancien_emprunteur AND Date_retour IS NULL) THEN
-- Mettre fin à l'emprunt actuel
UPDATE Emprunt
SET Date_retour = CURDATE()
WHERE ID_Livre = @livre_id AND ID_Emprunteur = @ancien_emprunteur AND
Date_retour IS NULL;
-- Créer un nouvel emprunt
INSERT INTO Emprunt (ID_Livre, ID_Emprunteur, Date_emprunt,
Date_retour_prévue)
VALUES (@livre_id, @nouvel_emprunteur, CURDATE(), DATE_ADD(CURDATE(),
INTERVAL 14 DAY));
COMMIT;
ELSE
ROLLBACK;
END IF;
Optimisation des performances
Indexation
Types d'index
Création et suppression d'index
Analyse et optimisation des requêtes
EXPLAIN
Optimiseur de requêtes
Partitionnement des tables
Caching
Exercice 8 : Optimisation
Énoncé : Analysez la requête suivante et proposez des optimisations :
SELECT [Link], [Link]énom, COUNT(*) as Nombre_de_livres
FROM Auteur a
LEFT JOIN Livre l ON a.ID_Auteur = l.ID_Auteur
WHERE [Link]ée_publication > 2000
GROUP BY a.ID_Auteur
HAVING Nombre_de_livres > 10
ORDER BY Nombre_de_livres DESC;
Corrigé :
1. Ajoutez un index sur la colonne Année_publication de la table Livre :
CREATE INDEX idx_annee_publication ON Livre(Année_publication);
2. Ajoutez un index composé sur les colonnes ID_Auteur et Année_publication de la
table Livre :
CREATE INDEX idx_auteur_annee ON Livre(ID_Auteur, Année_publication);
3. Reformulez la requête pour utiliser une sous-requête :
SELECT [Link], [Link]énom, livre_count.Nombre_de_livres
FROM Auteur a
INNER JOIN (
SELECT ID_Auteur, COUNT(*) as Nombre_de_livres
FROM Livre
WHERE Année_publication > 2000
GROUP BY ID_Auteur
HAVING COUNT(*) > 10
) as livre_count ON a.ID_Auteur = livre_count.ID_Auteur
ORDER BY livre_count.Nombre_de_livres DESC;
Cette optimisation permet de filtrer les livres et les auteurs avant de faire la jointure, réduisant
ainsi la quantité de données à traiter.
Sécurité des bases de données
Gestion des utilisateurs et des privilèges
CREATE USER 'nom_utilisateur'@'hôte' IDENTIFIED BY 'mot_de_passe';
GRANT privilège ON base_de_donné[Link] TO 'nom_utilisateur'@'hôte';
Protection contre les injections SQL
Chiffrement des données
Audits et logs
Exercice 9 : Sécurité
Énoncé : Créez un utilisateur avec des privilèges en lecture seule sur la table Livre et en
lecture/écriture sur la table Emprunt .
Corrigé :
-- Création de l'utilisateur
CREATE USER 'bibliothecaire'@'localhost' IDENTIFIED BY
'mot_de_passe_securise';
-- Attribution des privilèges
GRANT SELECT ON [Link] TO 'bibliothecaire'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON [Link] TO
'bibliothecaire'@'localhost';
-- Appliquer les changements
FLUSH PRIVILEGES;
Exercices pratiques et corrigés
Exercice 10 : Projet final
Énoncé : Créez une base de données complète pour un système de gestion de bibliothèque.
Incluez les tables suivantes : Livre, Auteur, Emprunteur, Emprunt, Catégorie. Ajoutez des
données de test, créez des requêtes complexes, une procédure stockée et une fonction.
Corrigé :
1. Création des tables :
CREATE TABLE Auteur (
ID_Auteur INT PRIMARY KEY AUTO_INCREMENT,
Nom VARCHAR(100) NOT NULL,
Prénom VARCHAR(100)
## Exercices pratiques et corrigés (suite)
### Exercice 10 : Projet final
**Énoncé** : Créez une base de données complète pour un système de gestion
de bibliothèque. Incluez les tables suivantes : Livre, Auteur, Emprunteur,
Emprunt, Catégorie. Ajoutez des données de test, créez des requêtes
complexes, une procédure stockée et une fonction.
**Corrigé** :
1. Création des tables :
```sql
CREATE TABLE Auteur (
ID_Auteur INT PRIMARY KEY AUTO_INCREMENT,
Nom VARCHAR(100) NOT NULL,
Prénom VARCHAR(100) NOT NULL,
Date_Naissance DATE
);
CREATE TABLE Catégorie (
ID_Catégorie INT PRIMARY KEY AUTO_INCREMENT,
Nom VARCHAR(50) NOT NULL,
Description TEXT
);
CREATE TABLE Livre (
ID_Livre INT PRIMARY KEY AUTO_INCREMENT,
Titre VARCHAR(255) NOT NULL,
ID_Auteur INT,
ID_Catégorie INT,
Année_Publication INT,
ISBN VARCHAR(13) UNIQUE,
Nombre_Exemplaires INT DEFAULT 1,
FOREIGN KEY (ID_Auteur) REFERENCES Auteur(ID_Auteur),
FOREIGN KEY (ID_Catégorie) REFERENCES Catégorie(ID_Catégorie)
);
CREATE TABLE Emprunteur (
ID_Emprunteur INT PRIMARY KEY AUTO_INCREMENT,
Nom VARCHAR(100) NOT NULL,
Prénom VARCHAR(100) NOT NULL,
Email VARCHAR(255) UNIQUE,
Date_Inscription DATE
);
CREATE TABLE Emprunt (
ID_Emprunt INT PRIMARY KEY AUTO_INCREMENT,
ID_Livre INT,
ID_Emprunteur INT,
Date_Emprunt DATE NOT NULL,
Date_Retour_Prévue DATE NOT NULL,
Date_Retour_Effective DATE,
FOREIGN KEY (ID_Livre) REFERENCES Livre(ID_Livre),
FOREIGN KEY (ID_Emprunteur) REFERENCES Emprunteur(ID_Emprunteur)
);
2. Insertion de données de test :
-- Insertion de catégories
INSERT INTO Catégorie (Nom, Description) VALUES
('Roman', 'Œuvres de fiction narrative en prose'),
('Science-fiction', 'Récits basés sur des progrès scientifiques et
technologiques'),
('Policier', 'Romans axés sur la résolution d'énigmes criminelles');
-- Insertion d'auteurs
INSERT INTO Auteur (Nom, Prénom, Date_Naissance) VALUES
('Hugo', 'Victor', '1802-02-26'),
('Asimov', 'Isaac', '1920-01-02'),
('Christie', 'Agatha', '1890-09-15');
-- Insertion de livres
INSERT INTO Livre (Titre, ID_Auteur, ID_Catégorie, Année_Publication, ISBN,
Nombre_Exemplaires) VALUES
('Les Misérables', 1, 1, 1862, '9782253096337', 5),
('Fondation', 2, 2, 1951, '9782070415700', 3),
('Le meurtre de Roger Ackroyd', 3, 3, 1926, '9782253161110', 4);
-- Insertion d'emprunteurs
INSERT INTO Emprunteur (Nom, Prénom, Email, Date_Inscription) VALUES
('Dupont', 'Jean', '[Link]@[Link]', '2023-01-15'),
('Martin', 'Sophie', '[Link]@[Link]', '2023-02-20');
-- Insertion d'emprunts
INSERT INTO Emprunt (ID_Livre, ID_Emprunteur, Date_Emprunt,
Date_Retour_Prévue) VALUES
(1, 1, '2023-03-01', '2023-03-15'),
(2, 2, '2023-03-05', '2023-03-19');
3. Requêtes complexes :
a. Trouver les livres les plus empruntés :
SELECT [Link], [Link] AS Auteur, COUNT(*) AS Nombre_Emprunts
FROM Livre l
JOIN Auteur a ON l.ID_Auteur = a.ID_Auteur
JOIN Emprunt e ON l.ID_Livre = e.ID_Livre
GROUP BY l.ID_Livre
ORDER BY Nombre_Emprunts DESC
LIMIT 5;
b. Lister les emprunteurs en retard :
SELECT [Link], [Link]énom, [Link], em.Date_Retour_Prévue
FROM Emprunteur e
JOIN Emprunt em ON e.ID_Emprunteur = em.ID_Emprunteur
JOIN Livre l ON em.ID_Livre = l.ID_Livre
WHERE em.Date_Retour_Effective IS NULL
AND em.Date_Retour_Prévue < CURDATE();
4. Procédure stockée :
Création d'une procédure pour emprunter un livre :
DELIMITER //
CREATE PROCEDURE emprunter_livre(
IN p_id_livre INT,
IN p_id_emprunteur INT,
IN p_duree_pret INT
)
BEGIN
DECLARE livre_disponible INT;
-- Vérifier si le livre est disponible
SELECT Nombre_Exemplaires INTO livre_disponible
FROM Livre
WHERE ID_Livre = p_id_livre;
IF livre_disponible > 0 THEN
-- Créer l'emprunt
INSERT INTO Emprunt (ID_Livre, ID_Emprunteur, Date_Emprunt,
Date_Retour_Prévue)
VALUES (p_id_livre, p_id_emprunteur, CURDATE(), DATE_ADD(CURDATE(),
INTERVAL p_duree_pret DAY));
-- Mettre à jour le nombre d'exemplaires disponibles
UPDATE Livre
SET Nombre_Exemplaires = Nombre_Exemplaires - 1
WHERE ID_Livre = p_id_livre;
SELECT 'Emprunt effectué avec succès' AS message;
ELSE
SELECT 'Livre non disponible' AS message;
END IF;
END //
DELIMITER ;
Utilisation de la procédure :
CALL emprunter_livre(1, 2, 14);
5. Fonction :
Création d'une fonction pour calculer les frais de retard :
DELIMITER //
CREATE FUNCTION calculer_frais_retard(p_id_emprunt INT)
RETURNS DECIMAL(10,2)
BEGIN
DECLARE retard INT;
DECLARE frais DECIMAL(10,2);
SELECT DATEDIFF(IFNULL(Date_Retour_Effective, CURDATE()),
Date_Retour_Prévue)
INTO retard
FROM Emprunt
WHERE ID_Emprunt = p_id_emprunt;
IF retard > 0 THEN
SET frais = retard * 0.50; -- 0.50€ par jour de retard
ELSE
SET frais = 0;
END IF;
RETURN frais;
END //
DELIMITER ;
Utilisation de la fonction :
SELECT ID_Emprunt, calculer_frais_retard(ID_Emprunt) AS Frais_Retard
FROM Emprunt
WHERE Date_Retour_Effective IS NULL;
Ce projet final couvre les aspects essentiels de la gestion d'une base de données pour une
bibliothèque, y compris la création de tables avec des relations, l'insertion de données de test,
des requêtes complexes pour l'analyse des données, une procédure stockée pour gérer les
emprunts, et une fonction pour calculer les frais de retard.
Conclusion et prochaines étapes
Félicitations ! Vous avez maintenant une compréhension approfondie de SQL et de son
application dans le contexte du BTS SIO option SLAM. Voici quelques suggestions pour
continuer à développer vos compétences :
1. Pratiquez régulièrement en créant des projets personnels ou en participant à des défis
SQL en ligne.
2. Explorez les fonctionnalités spécifiques des différents SGBDR (MySQL, PostgreSQL, SQL
Server, etc.).
3. Approfondissez vos connaissances en matière d'optimisation des performances et de
sécurité des bases de données.
4. Intégrez SQL dans vos projets de développement d'applications, en utilisant des ORM
(Object-Relational Mapping) comme Hibernate pour Java ou SQLAlchemy pour Python.
5. Restez à jour avec les dernières évolutions de SQL et des technologies de bases de
données.
N'oubliez pas que la maîtrise de SQL est un processus continu. Plus vous pratiquerez et
explorerez ses fonctionnalités, plus vous deviendrez compétent dans la gestion et l'analyse des
données.