SQL (Fondamentaux)
Structured Query Language — Support de cours
Principes
SQL (Structured Query Language)
- Langage standard de gestion des bases de données relationnelles
- Créé par IBM dans les années 1970, normalisé par ANSI/ISO
- Langage déclaratif : on décrit CE QUE l'on veut, pas COMMENT l'obtenir
Caractéristiques principales :
- Basé sur le modèle relationnel (E.F. Codd)
- Indépendant du SGBD : MySQL, PostgreSQL, Oracle, SQL Server...
- Gestion des données structurées sous forme de tables (relations)
- Chaque table contient des colonnes (champs) et des lignes (tuples/enregistrements)
- Utilise l'algèbre relationnelle : projection, sélection, jointure, union...
Architecture
Architecture Client / Serveur
- Le SGBD (Système de Gestion de Base de Données) fonctionne comme un serveur
- Les applications clientes envoient des requêtes SQL au serveur
- Le serveur traite les requêtes et renvoie les résultats
Architecture 3 niveaux (ANSI/SPARC) :
1. Niveau externe : vues utilisateurs (ce que voit chaque utilisateur)
2. Niveau conceptuel : schéma logique global (tables, relations, contraintes)
3. Niveau interne : stockage physique des données (fichiers, index)
Exemples de SGBD : MySQL, PostgreSQL, Oracle, SQL Server, SQLite
SQL ( Langages et Primitives)
- LDD : Langage de définition de données :
CREATE
ALTER
DROP
- LMD : Langage de manipulation de données
INSERT
UPDATE
DELETE
- LED : Langage d'extraction de données
SELECT
LDD : Langage de Définition de Données
Primitives
CREATE
ALTER
DROP
LDD s'applique à la gestion de la structure des objets de la base de données
-> On y crée, modifie ou supprime des objets (tables, bases, index, vues...)
-> On ne touche pas aux données elles-mêmes, mais à leur contenant
Objets concernés :
- DATABASE (base de données)
- TABLE (table / relation)
- INDEX (index pour accélérer les recherches)
- VIEW (vue : requête sauvegardée)
LDD : Langage de Définition de Données
CREATE DATABASE : crée une nouvelle base de données
Syntaxe : CREATE DATABASE [IF NOT EXISTS] nomBase ;
- IF NOT EXISTS : évite une erreur si la base existe déjà
- La base est un conteneur pour les tables, vues, index, etc.
Exemples :
CREATE DATABASE Ecole ;
CREATE DATABASE IF NOT EXISTS Africa ;
USE : sélectionne la base de données active
USE Ecole ;
-- Toutes les requêtes suivantes porteront sur la base "Ecole"
SHOW DATABASES ; -- Affiche la liste des bases disponibles
LDD : Langage de Définition de Données
CREATE TABLE : crée une nouvelle table dans la base active
Syntaxe :
CREATE TABLE nomTable (
nomChamp1 type [contraintes],
nomChamp2 type [contraintes],
... ) ;
Types de données courants :
- INTEGER / INT : nombre entier
- FLOAT / DOUBLE / DECIMAL(p,s) : nombres décimaux
- VARCHAR(n) : chaîne de caractères variable (max n)
- CHAR(n) : chaîne de caractères fixe (exactement n)
- TEXT : texte long
- DATE / DATETIME / TIMESTAMP : dates et heures
- BOOLEAN : vrai / faux
LDD : Langage de Définition de Données
Contraintes d'intégrité :
- PRIMARY KEY : identifiant unique de chaque tuple (non null, unique)
- FOREIGN KEY : référence vers la clé primaire d'une autre table
- NOT NULL : le champ ne peut pas être vide
- UNIQUE : aucune valeur en double autorisée
- DEFAULT valeur : valeur par défaut si non renseigné
- AUTO_INCREMENT : valeur auto-incrémentée (MySQL)
- CHECK(condition) : vérifie une condition sur les valeurs
Exemple complet :
CREATE TABLE Eleves (
numEleve INT AUTO_INCREMENT PRIMARY KEY,
nom VARCHAR(30) NOT NULL,
age INT CHECK(age > 0),
email VARCHAR(100) UNIQUE
);
LDD : Langage de Définition de Données
FOREIGN KEY (Clé étrangère)
Établit un lien entre deux tables (relation d'intégrité référentielle)
Le champ référencé doit être une PRIMARY KEY dans la table cible
Exemple : table Notes qui référence la table Eleves
CREATE TABLE Notes (
idNote INT AUTO_INCREMENT PRIMARY KEY,
numEleve INT NOT NULL,
matiere VARCHAR(50) NOT NULL,
valeur DECIMAL(4,2) CHECK(valeur BETWEEN 0 AND 20),
FOREIGN KEY (numEleve) REFERENCES Eleves(numEleve)
);
-> On ne peut pas insérer un numEleve dans Notes qui n'existe pas dans Eleves
-> On ne peut pas supprimer un élève s'il a des notes associées
LDD : Langage de Définition de Données
ALTER TABLE : modifie la structure d'une table existante
Ajouter un champ :
ALTER TABLE Eleves ADD prenom VARCHAR(30) ;
Supprimer un champ :
ALTER TABLE Eleves DROP COLUMN email ;
Modifier le type d'un champ :
ALTER TABLE Eleves MODIFY nom VARCHAR(50) ;
Renommer un champ :
ALTER TABLE Eleves CHANGE nom nomComplet VARCHAR(50) ;
Renommer la table :
ALTER TABLE Eleves RENAME TO Etudiants ;
LDD : Langage de Définition de Données
DROP TABLE : supprime définitivement une table et toutes ses données
Syntaxe : DROP TABLE [IF EXISTS] nomTable ;
DROP TABLE Notes ;
-- Supprime la table Notes (erreur si elle n'existe pas)
DROP TABLE IF EXISTS Notes ;
-- Supprime la table si elle existe, sinon ne fait rien
DROP DATABASE : supprime une base de données entière
DROP DATABASE IF EXISTS Ecole ;
-- Supprime la base et TOUTES ses tables. Opération irréversible !
TRUNCATE TABLE : vide une table sans la supprimer
TRUNCATE TABLE Eleves ;
-- Supprime tous les tuples mais conserve la structure de la table
-- Plus rapide que DELETE FROM (pas de log ligne par ligne)
LMD : Langage de manipulation de
données
Primitives
INSERT
UPDATE
DELETE
LMD s'applique à permettre la gestion des tuples sans modifier
ou permettre de modifier leur structure
-> On y change pas les champs ni les contraintes,
mais les valeurs des champs d'enregistrement ou la creation
d'enregistrement, ainsi que leur suppression.
LMD : Langage de manipulation de
données
INSERT : permet le rajout d'enregistrement dans des tables
[Link] : 1 tuple ou enregistrement à la fois.
[Link] : > 1 tuple à la fois
LMD : Langage de manipulation de
données
[Link] : 1 tuple ou enregistrement à la fois
INSERT INTO nomTable [(liste champs)] VALUES (liste de valeurs);
Exemples :
soit la table
CREATE TABLE Eleves ( numEleve integer auto_increment PRIMARY KEY, nom varchar(30), age integer)
;
-- L’ordre des champs est implicite : il s'agit de leur ordre à la création de la table
INSERT INTO Eleves VALUES ( 1, "MBACKE" , 18) ;
-- L'ordre des champs est explicite : l'utilisateur définit quels sont les champs et dans quel ordre les
valeurs devront être citées
INSERT INTO Eleves (age, numEleve, nom) VALUES (25, 2, "DIOP") ;
-- Une utilisation partielle des champs : l'utilisateur ne dit pas quels sont les valeurs pour une partie
des champs. ils prennent alors une valeur par défaut ( NULL si non définit par le créateur de la table)
INSERT INTO Eleves (nom, numEleve) VALUES ("SARR" , 3) ;
LMD : Langage de manipulation de
données
[Link] : > 1 tuple à la fois
-- tuples multiples : Spécification de plusieurs enregistrements dans la même requête
INSERT INTO Eleves VALUES ( 5 , "DIENG", 23), (6, "SECK", 45) , (7, "CISSE" , 19) ;
INSERT INTO Eleves (nom, age) VALUES ("DIENG", 23) , ("SECK", 45) , ("CISSE", 19) ;
-- injection : insérer dans une table le résultat d'une requete SQL
INSERT INTO ElevesVieux SELECT * from Eleves where age > 30 ;
Condition : Eleves et ElevesVieux doivent avoir des schemas equivalents :
- même nombre de champs
- du même type
- dans le même ordre
Rqe: dans les deux cas on peut formater la requête INSERT pour la gestion de l'ordre des champs
LMD : Langage de manipulation de
données
UPDATE : permet de modifier les valeurs des champs des enregistrements
Syntaxe: UPDATE nomTable SET nomChamp = nouvelleValeur / Expression [WHERE <Condition>] ;
La requête permet de modifier la valeur d'un ou plusieurs champs pour les tuples qui respectent la
condition. Si aucune condition n'est spécifiée, la modification est appliquée à tous les tuples.
Exemples:
UPDATE ElevesVieux SET age = 0 ;
-- Modifie tous les tuples
UPDATE ElevesVieux SET ageVieux = 25 , nomVieux ="DDDDDDD" where nomVieux = "CISSE" ;
-- Modifie deux champs
UPDATE ElevesVieux SET nomVieux = "MBENGUE" WHERE numVieux = 49 ;
-- Modifie un champ d'un seul tuple
LMD : Langage de manipulation de
données
DELETE : permet de supprimer des enregistrements
Syntaxe : DELETE FROM nomTable [WHERE <Condition>] ;
La requête permet de supprimer entièrement un ou plusieurs tuples qui respectent la condition.
Si aucune condition n'est explicitée , la requête supprimera tous les tuples de la table.
Exemples :
DELETE FROM ElevesVieux ;
--Supprime tous les tuples
DELETE FROM ElevesVieux WHERE numVieux > 40 ;
-- Supprime tous les tuples respectant la condition
DELETE FROM ElevesVieux WHERE numVieux = 48 ;
-- Supprimer un tuple unique sur la base de son identifiant
LMD : Langage de manipulation de
données
Exercice :
Créer une base de données "Africa" qui contient une copie des données sur tous les pays d'Afrique
avec comme source la base "world". Les noms et structures des tables sont à conserver.
Rqe: la base world peut être installée a partir du script [Link]
voir le dépôt [Link]
LED : Langage d'Extraction de données
SELECT : permet de formater et de renvoyer des tuples
Syntaxe: [Option]
SELECT <OUTPUT>
FROM <INPUT>
[WHERE Condition]
[GROUP BY listeChamps ]
[ORDER BY listechamps + Sens de Tri]
[LIMIT nbLignes, offset]
[HAVING Condition]
[FOR UPDATE ]
;
LED : Langage d'Extraction de données
SELECT <OUTPUT> * <OUTPUT> : Donne les informations à afficher ou
FROM <INPUT> à renvoyer
[WHERE Condition]
[GROUP BY listeChamps ]
Elle peut contenir les informations suivantes:
[ORDER BY listechamps + Sens de Tri] - Champ
[LIMIT nbLignes, offset] - Liste de champs ( séparés par des virgules)
[HAVING Condition]
[FOR UPDATE ] - constantes
; - Expressions
- Fonctions
- Combinaison de tout ce qui précéde
- * ( tous les champs de l'INPUT)
- Sous-Requête / requête imbriquée
Rqe: on peut y rajouter des alias
Syntaxe: champ/expression as Alias
LED : Langage d'Extraction de données
SELECT <OUTPUT> *<INPUT>: Mentionne la source des données
FROM <INPUT> utilisées pour les calculs ou l'output
[WHERE Condition]
[GROUP BY listeChamps ] Elle peut contenir les éléments suivants:
[ORDER BY listechamps + Sens de Tri]
[LIMIT nbLignes, offset] - Table / Liste tables,
[HAVING Condition] - Vue / Liste de vues ,
[FOR UPDATE ]
; - Expression de tables/Vues
- SousRequêtes
- Combinaison de tout ce qui précède ,
- dual ( pile système)
LED : Langage d'Extraction de données
SELECT <OUTPUT> *<INPUT>: Mentionne la source des données utilisées
FROM <INPUT> pour les calculs ou l'output
[WHERE Condition]
[GROUP BY listeChamps ] Elle peut contenir les éléments suivants:
[ORDER BY listechamps + Sens de Tri]
[LIMIT nbLignes, offset] - Table / Liste tables,
[HAVING Condition] - Vue / Liste de vues ,
[FOR UPDATE ]
; - Expression de tables/Vues
> Produit Cartésien : Chercher l'ensemble des combinaisons possibles
entre deux relations
Syntaxe : R = R1 x R2 -- SQL: Select * from R1 , R2 ;
Consequence : Card(R) = Card( R1) * Card(R2)
Si R1 j'ai n enregistrements
et R2 j'ai m enregistrements
alors dans R j'aurais n*m enregistrements
LED : Langage d'Extraction de données
SELECT <OUTPUT> *<INPUT>: Mentionne la source des données utilisées
FROM <INPUT>
[WHERE Condition]
pour les calculs ou l'output
[GROUP BY listeChamps ] Elle peut contenir les éléments suivants:
[ORDER BY listechamps + Sens de Tri]
[LIMIT nbLignes, offset] - Table / Liste tables,
[HAVING Condition]
[FOR UPDATE ]
- Vue / Liste de vues ,
; - Expression de tables/Vues
> Jointure Naturelle : renvoie la liste des tuples sur la base de
combinaisons existantes avec des champs en communs à valeurs égales
Syntaxe : R = R1 * R2. --- SQL : Select * from R1 natural join R2 ;
Condition: Schema(R1) intersect Schema(R2) not null
R1 et R2 doivent avoir des champs en communs avec les mêmes
noms et les mêmes types
Consequence : Card(R) <= max (Card(R1), Card(2))
LED : Langage d'Extraction de données
SELECT <OUTPUT> *<INPUT>: Mentionne la source des données utilisées
FROM <INPUT> pour les calculs ou l'output
[WHERE Condition]
[GROUP BY listeChamps ] Elle peut contenir les éléments suivants:
[ORDER BY listechamps + Sens de Tri]
[LIMIT nbLignes, offset] - Table / Liste tables,
[HAVING Condition] - Vue / Liste de vues ,
[FOR UPDATE ]
; - Expression de tables/Vues
> Theta Jointure : c'est une généralisation de la jointure naturelle qui permet
a son utilisateur de définir la condition de jointure de façon explicite
Syntaxe : R = R1 *[Condition] R2 --SQL: Select * from R1 join R2 on
<Condition> ;
Condition : l'égalité des valeurs de champs et le partage de champs en
communs n'est plus une obligation
LED : Langage d'Extraction de données
SELECT <OUTPUT> * WHERE : Filtre les tuples selon une condition logique
FROM <INPUT> Seuls les tuples respectant la condition sont renvoyés
[WHERE Condition]
[GROUP BY listeChamps ] Opérateurs de comparaison :
[ORDER BY listechamps + Sens de Tri] =, <>, <, >, <=, >=
[LIMIT nbLignes, offset] Opérateurs logiques :
[HAVING Condition]
AND, OR, NOT
[FOR UPDATE ]
Opérateurs spéciaux :
;
BETWEEN val1 AND val2, IN (liste), LIKE 'motif', IS NULL, IS NOT NULL
Exemples :
SELECT * FROM Eleves WHERE age >= 18 AND nom LIKE 'D%' ;
SELECT * FROM Eleves WHERE age BETWEEN 18 AND 25 ;
SELECT * FROM Eleves WHERE nom IN ('MBACKE', 'DIOP', 'SARR') ;
LED : Langage d'Extraction de données
* LIMIT : Restreint le nombre de tuples renvoyés
SELECT <OUTPUT>
Syntaxe : LIMIT nbLignes [OFFSET position]
FROM <INPUT>
[WHERE Condition]
- nbLignes : nombre maximum de tuples à renvoyer
[GROUP BY listeChamps ]
- OFFSET : nombre de tuples à ignorer avant de commencer (défaut = 0)
[ORDER BY listechamps + Sens de Tri]
- Utile pour la pagination des résultats
[LIMIT nbLignes, offset]
[HAVING Condition] Exemples :
[FOR UPDATE ] SELECT * FROM Eleves LIMIT 5 ;
; -- Renvoie les 5 premiers tuples
SELECT * FROM Eleves LIMIT 5 OFFSET 10 ;
-- Ignore les 10 premiers, renvoie les 5 suivants (page 3)
SELECT * FROM Eleves ORDER BY age DESC LIMIT 3 ;
-- Les 3 élèves les plus âgés
LED : Langage d'Extraction de données
* ORDER BY : Trie les résultats selon un ou plusieurs champs
Syntaxe : ORDER BY champ1 [ASC|DESC], champ2 [ASC|DESC], ...
- ASC : tri croissant (par défaut)
- DESC : tri décroissant
- On peut trier sur plusieurs champs (tri secondaire si égalité)
Exemples :
SELECT * FROM Eleves ORDER BY nom ASC ;
-- Tri alphabétique sur le nom
SELECT * FROM Eleves ORDER BY age DESC, nom ASC ;
-- Tri par âge décroissant, puis par nom en cas d'égalité
LED : Langage d'Extraction de données
Fonctions d'agrégation
Opèrent sur un ensemble de tuples et renvoient une valeur unique
- COUNT(*) : nombre de tuples
- SUM(champ) : somme des valeurs
- AVG(champ) : moyenne des valeurs
- MIN(champ) : valeur minimale
- MAX(champ) : valeur maximale
Exemples :
SELECT COUNT(*) FROM Eleves ;
SELECT AVG(age) FROM Eleves ;
SELECT MAX(age), MIN(age) FROM Eleves ;
LED : Langage d'Extraction de données
* GROUP BY : Regroupe les tuples selon les valeurs d'un ou plusieurs champs
Syntaxe : GROUP BY champ1, champ2, ...
- Utilisé conjointement avec les fonctions d'agrégation
- Chaque groupe produit une seule ligne de résultat
Règle : tout champ dans le SELECT qui n'est pas une fonction d'agrégation doit figurer dans le GROUP BY
Exemples :
SELECT Continent, COUNT(*) FROM country GROUP BY Continent ;
-- Nombre de pays par continent
SELECT Continent, AVG(Population) FROM country GROUP BY Continent ;
-- Population moyenne par continent
LED : Langage d'Extraction de données
* HAVING : Filtre les groupes créés par GROUP BY
Syntaxe : HAVING condition_sur_agrégation
Différence WHERE vs HAVING :
- WHERE filtre les tuples AVANT le regroupement
- HAVING filtre les groupes APRÈS le regroupement
- HAVING s'utilise avec des fonctions d'agrégation
Exemples :
SELECT Continent, COUNT(*) FROM country GROUP BY Continent HAVING COUNT(*) > 10 ;
-- Continents ayant plus de 10 pays
SELECT Continent, AVG(Population) as moy FROM country GROUP BY Continent HAVING moy > 50000000 ;
-- Continents avec une population moyenne supérieure à 50 millions
LED : Langage d'Extraction de données
Sous-requêtes (requêtes imbriquées)
Une requête SELECT placée à l'intérieur d'une autre requête
Peut être utilisée dans : WHERE, FROM, SELECT
Sous-requête dans WHERE :
SELECT * FROM Eleves WHERE age > (SELECT AVG(age) FROM Eleves) ;
-- Élèves dont l'âge est supérieur à la moyenne
Opérateurs avec sous-requêtes :
- IN : le champ est dans le résultat de la sous-requête
- EXISTS : vrai si la sous-requête renvoie au moins un tuple
- ANY / ALL : comparaison avec les valeurs de la sous-requête
SELECT Name FROM country WHERE Code IN (SELECT CountryCode FROM city WHERE Population > 1000000) ;
LED : Langage d'Extraction de données
DISTINCT : Éliminer les doublons
Supprime les tuples identiques du résultat
SELECT DISTINCT Continent FROM country ;
-- Liste des continents sans doublons
Alias (AS) : Renommer colonnes et tables
- Alias de colonne : renomme l'en-tête du résultat
SELECT Name AS NomPays, Population AS Pop FROM country ;
- Alias de table : raccourci pour les noms de tables (utile dans les jointures)
SELECT [Link], [Link] FROM country AS c JOIN city AS ci ON [Link] = [Link] ;
-- c est l'alias de country, ci est l'alias de city
LED : Langage d'Extraction de données
Exercices SELECT (base "world") :
1. Afficher le nom et la population de tous les pays d'Afrique, triés par population décroissante
2. Compter le nombre de villes par pays et n'afficher que les pays ayant plus de 20 villes
3. Afficher les 5 langues les plus parlées dans le monde (en nombre de pays)
4. Trouver les pays dont la population est supérieure à la moyenne mondiale
5. Afficher le nom du pays et le nom de sa capitale (jointure country + city)