Dr.

Lilia SFAXI

Les Bases de Données Relationnelles

Application avec MySQL

Dr. Lilia SFAXI 1

[Link]/liliasfaxi

Initiation aux Bases de Données Relationnelles
Dr. Lilia SFAXI

Chapitre 3
Le Langage SQL

2
Chp3 - Le Langage SQL

PRÉSENTATION DE SQL

3
SQL: Structured Query Language
Présentation de SQL

• Le langage SQL (Structured Query Language) est le langage d’accès

normalisé aux bases de données.
• Permet la définition, manipulation et contrôle des bases de données
relationnelles
• Il est aujourd’hui supporté par la plupart des SGBD que ce soit par les
SGBD micro comme Access ou par les SGBD plus professionnels
comme Oracle.
• Il a fait l’objet de plusieurs normes ANSI/ISO dont la plus répandue
aujourd’hui est la norme SQL2 (1992).
• Basé sur l’algèbre relationnelle

4
SQL: Un peu d’histoire
Présentation de SQL
• 1977 : Création du langage SEQUEL (Structured English Query
Language)
▪ SEQUEL évolue pour devenir SQL, mais la prononciation en anglais reste la
même
• 1981: IBM sort sa première version de SQL
• 1986: Le premier standard SQL (SQL-1) de ISO (International Standard
Organization) apparaît
• 1992: SQL2 (support amélioré de l’intégrité) —> le plus commun
• 1999: SQL3 (ajout des extensions objet, triggers, etc.)
• 2003: Introduction des fonctions pour la manipulation XML, etc.
• SQL est à présent implémenté par les vendeurs de SGBDR les plus
connus (Oracle, IBM DB2, Microsoft SQL Server, MySQL, Sybase, etc.)

5
Quelques Remarques
Présentation de SQL

• SQL est un langage de requêtage (déclaratif ), pas un langage de

programmation

• Une requête SQL peut être écrite sur plusieurs lignes.

▪ Sa terminaison est définie par un point-virgule (;)

• SQL est intégré dans les langages de programmation tels que C, Java
ou PL/SQL.

6
Sous-Ensembles de SQL
Présentation de SQL

• SQL est à la fois un langage de:

▪ Définition des données:
o DDL (Data Definition Language)
▪ Manipulation des données:
o DML (Data Manipulation Language)
▪ Contrôle de l’accès aux données:
o DCL (Data Control Language)
▪ Gestion des transactions:
o TCL (Transaction Control Language)

7
Sous-Ensembles de SQL
Présentation de SQL

8
Types de Données
Présentation de SQL

9
Chp3 - Le Langage SQL

DML: DATA MANIPULATION LANGUAGE

10
Définition
DML: Data Manipulation Language

• DML (Data Manipulation Language) représente le sous-ensemble de

SQL permettant la consultation et modification du contenu de la base
de données

• Instructions principales
▪ INSERT INTO: Insertion des données
▪ SELECT: Affichage/Sélection des données
▪ UPDATE: Modification des données
▪ DELETE: Suppression des données

11
À vous de jouer!
Base de données: ClassicModels

• Télécharger la base suivante:

▪ [Link]
wp-content/uploads/2018/03/
[Link]
• Décompresser le fichier
obtenu, vous trouverez un
fichier SQL
• Dans MySQLWorkbench,
exécuter le code SQL fourni,
une nouvelle base de donnée
sera créée, dont le modèle
est présenté ci-contre.

12
DML: Data Manipulation Language

INSERT, DELETE & UPDATE

13
INSERT INTO
Insert, delete & Update
• La commande INSERT INTO permet d'ajouter un ou plusieurs Tuples à une table.
• Sa syntaxe est la suivante:

INSERT INTO table [(attribute1[,attribute2]…)]

VALUES (val1, val2,…) | <SELECT clause>

• Les éléments entre [] sont optionnels, et le symbole | veut dire “ou”

• Il est possible de ne pas lister les colonnes si les données insérées concernent
un tuple complet
• Il est possible d’utiliser une requête SELECT pour insérer des données à partir
d’une autre table
• L’insertion dans une table contenant une clef auto-incrémentée ne doit pas
fixer de valeur pour ce champ.

14
INSERT INTO: Exemples
Insert, delete & Update

• INSERT INTO Car VALUES('123ABC75', 'Renault', 'Rouge', 7, 10 000);

• INSERT INTO Car VALUES('123ABC92', 'Peugeot', 'Black', 5, 2 500);

• INSERT INTO Car (registration, brand, colour, power, price)

VALUES('123ATT77', 'Rover', 'Blue', 6, 13 000);

• INSERT INTO Car VALUES

('698KLM45', 'Citroën', 'Green', 5, 5 000),

('956BZD59', 'Peugeot', 'Yellow', 5, 16 000),

('156AMN86', 'Renault', 'Blue',7, 5 250);

• INSERT INTO Car (registration, price) SELECT reg, price FROM

UsedCar ;

15
DELETE
Insert, delete & Update

• La commande DELETE permet de supprimer un ou plusieurs Tuples

d’une table.
• Sa syntaxe est la suivante:

DELETE FROM table [WHERE condition]

• Le WHERE est optionnel, cela dit, si on le omet, tous les tuples de la

relation seront supprimés.

16
UPDATE
Insert, delete & Update

• La commande UPDATE permet de modifier un ou plusieurs Tuples d’une

table.
• Sa syntaxe est la suivante:

UPDATE table SET attribute1=expression1, …

[WHERE condition]

• Le WHERE est optionnel, cela dit, si on le omet, tous les tuples de la

relation seront modifiés.

17
 À vous de jouer!
Insert, delete & Update

1. Insérer dans la base de données ClassicModels la ligne suivante dans la

table Employees

employeeNumber: 2000
lastName: Doe
f rstName: John
extension: x222
email: [Link]@[Link]
off ceCode: 2
reportsTo: 1143

2. Modifier cet enregistrement pour que John Doe rende compte à

l’employé 1002.
3. Supprimer ensuite cet enregistrement.
18
i
i
DML: Data Manipulation Language

LA COMMANDE SELECT

19
SELECT: Définition
SELECT
• La commande SELECT permet de sélectionner plusieurs tuples d’une ou
plusieurs tables
• Sa syntaxe est la suivante:

SELECT * [ | DISTINCT | ALL] <attributes> FROM <data collection>

[WHERE <Tuple level logical condition>]
[GROUP BY <attributes>
[HAVING <group level logical condition>] ]
[ORDER BY <attributes>] [LIMIT <value>];

• Une collection de données <data collection> peut être composée de tables

et/ou de requêtes SELECT.
• Les éléments entre [] sont optionnels, et le symbole | veut dire “ou”

20
-
SELECT: Paramètres
SELECT

• DISTINCT et ALL: Permettent respectivement d’éliminer ou de garder les

duplicatas après une projection
• FROM : Désigne les tables concernées par la requête
• WHERE: Spécifie les critères de sélection qui sont des prédicats de
restriction ou jointure
▪ Il est possible d’insérer un SELECT imbriqué
• GROUP BY: Partitionne les relations en groupes
▪ HAVING: définit le critères de sélection relatives aux fonctions de
groupes
• ORDER BY: Permet de trier les résultats selon un ordre ascendant (ASC)
ou descendant (DESC)

21
SELECT: Exemples
SELECT

• Simple sélection sans conditions

▪ SELECT * FROM car;

• Projection: sélection d’une colonne

▪ SELECT brand FROM car;

• Projection sans duplicatas

▪ SELECT DISTINCT brand FROM car;

22
Clause WHERE
SELECT

• WHERE spécifie une condition sur la sélection

▪ Une condition sur la sélection définit un critère qui, appliqué à
un tuple, peut être vrai, faux ou inconnu
▪ Cette condition peut inclure des opérateurs booléens (AND, OR,
NOT), des conditions élémentaires et des parenthèses
▪ Les opérateurs logiques peuvent être combinés par ordre de
priorité

23
Prédicats (1)
SELECT

• Un prédicat (condition) permet de comparer deux expressions :

▪ La première, appelée terme, contenant des spécifications de
colonnes
▪ La deuxième, appelée constante, contient uniquement les
spécifications constantes

24
Prédicats (2)
SELECT
• Types de prédicats:
▪ Comparaison: =, !=, >, >=, <, <= (valide pour INTEGER, FLOAT, CHAR, VARCHAR and
DATE)
▪ Test d’intervalles: permet de tester si la valeur d’un terme est incluse entre deux
valeurs constantes (BETWEEN)
▪ Comparaison de texte: pour tester si un terme de type chaîne contient une ou
plusieurs sous-chaînes (LIKE)
o Il est possible d’utiliser des wildcards (% remplace plusieurs caractères, _ remplace
un seul caractère)
▪ Test de nullité: qui permet de tester si un terme a une valeur nulle, ce qui signifie que sa
valeur est inconnue ou que le champ n'a pas été rempli (IS NULL)
▪ Test d’appartenance: qui permet de tester si la valeur d'un terme appartient à une liste de
valeurs constantes (IN)

25
Exemples de Prédicats (1)
SELECT

• Considérons la table Car ci-après: Registration Brand Color Power Price

123ABC75 Renault Red 7 10 000
123ABC92 Peugeot Black 5 2 500
123ATT77 Rover Blue 6 13 000
698KLM45 Citroën Green 5 5 000
956BZD59 Peugeot Yellow 5 16 000
156AMN86 Renault Blue 7 5 250

• Q1: Afficher les tuples dont la puissance (power) est inférieure ou égale à
6 chevaux, ou dont le prix est supérieur ou égal à 10.000 €.

SELECT * FROM car WHERE power 6 OR price 10000;

26
<
=
>
=
Exemples de Prédicats (2)
SELECT

• Considérons la table Car ci-après: Registration Brand Color Power Price

123ABC75 Renault Red 7 10 000
123ABC92 Peugeot Black 5 2 500
123ATT77 Rover Blue 6 13 000
698KLM45 Citroën Green 5 5 000
956BZD59 Peugeot Yellow 5 16 000
156AMN86 Renault Blue 7 5 250

• Q2: afficher les tuples de la table CAR dont la puissance n'est pas égale à
6 chevaux.

SELECT * FROM car WHERE NOT (power = 6);

27
Exemples de Prédicats (3)
SELECT

• Considérons la table Car ci-après: Registration Brand Color Power Price

123ABC75 Renault Red 7 10 000
123ABC92 Peugeot Black 5 2 500
123ATT77 Rover Blue 6 13 000
698KLM45 Citroën Green 5 5 000
956BZD59 Peugeot Yellow 5 16 000
156AMN86 Renault Blue 7 5 250

• Q3: afficher les tuples de la table CAR dont la marque commence par
"Ren", dont la couleur est noire, bleue ou jaune et dont le prix est connu.

SELECT * FROM car WHERE brand LIKE ‘Ren%’ AND color

IN (‘Black’,’Blue’,’Yellow’) AND price IS NOT NULL;

28
À vous de jouer!
Commande SELECT

• Donner les requêtes nécessaires pour extraire les informations

suivantes:
1. Donner la liste des commandes qui ont reçu des commentaires.
2. Donner la liste des paiements qui ont eu lieu en 2003.
3. Donner la liste des lignes de produits qui concernent les voitures.
4. Donner la liste des produits dont la quantité en stock est inférieure à
100.

29
Agrégation (1)
SELECT

• Les agrégations sont appliquées à une colonne (ou un groupe)

entière et fournissent une valeur unique.
• Les agrégations peuvent être utilisées dans les clauses SELECT,
WHERE et HAVING.
• Il existe plusieurs agrégations :
▪ MAX : maximum
▪ MIN : minimum
▪ SUM : somme
▪ AVG : moyenne
▪ COUNT : nombre de lignes

30
Agrégation (2)
SELECT

• Les fonctions MAX et MIN retournent respectivement le maximum et le

minimum d'un champ (utilisable pour les types CHAR, VARCHAR, DATE et
NUMBER)
• La fonction SUM effectue, pour un ensemble de Tuples, la somme des valeurs
d'un attribut (utilisable uniquement pour le type NUMBER)
• La fonction COUNT compte le nombre de lignes pour lesquelles l'attribut est
non NULL.
• La fonction AVG calcule, pour un ensemble de Tuples, la moyenne
arithmétique des valeurs d'un attribut (utilisable uniquement pour le type
NUMBER).
▪ Elle vérifie la formule suivante :
AVG = Somme des valeurs non NULL / nombre de valeurs non NULL.

31
Exemples d’Agrégations (1)
SELECT

• Considérons la table Car ci-après: Registration Brand Color Power Price

123ABC75 Renault Red 7 10 000
123ABC92 Peugeot Black 5 2 500
123ATT77 Rover Blue 6 13 000
698KLM45 Citroën Green 5 5 000
956BZD59 Peugeot Yellow 5 16 000
156AMN86 Renault Blue 7 5 250

• Q1: quel est le prix maximum d’une voiture?

SELECT MAX(price) FROM car;

• Q2: quel est le prix maximum d’une Renault?

SELECT MAX(price) FROM car WHERE brand = ‘Renault’;

32
Exemples d’Agrégations (2)
SELECT

• Considérons la table Car ci-après: Registration Brand Color Power Price

123ABC75 Renault Red 7 10 000
123ABC92 Peugeot Black 5 2 500
4
123ATT77 Rover Blue 6 13 000
6 698KLM45 Citroën Green 5 5 000
956BZD59 Peugeot Yellow 5 16 000
156AMN86 Renault Blue 7 5 250

• Q3: quel est le nombre de tuples dans la table Car?

SELECT COUNT(*) FROM car;

• Q4: quel est le nombre de marques différentes de voitures?

SELECT COUNT(DISTINCT (brand)) FROM car;

33
Exemples d’Agrégations (3)
SELECT

• Considérons la table Car ci-après: Registration Brand Color Power Price

123ABC75 Renault Red 7 10 000
123ABC92 Peugeot Black 5 2 500
123ATT77 Rover Blue 6 13 000
698KLM45 Citroën Green 5 5 000
956BZD59 Peugeot Yellow 5 16 000
156AMN86 Renault Blue 7 5 250

• Q5: Quelle est la somme des prix des voitures de 5 chevaux ?

SELECT SUM(price) FROM car WHERE power = 5; SUM = 23500

• Q6: quel est la moyenne des prix des voitures de 5 chevaux ?

SELECT AVG(price) FROM car WHERE power = 5; AVG = 7833,33

34
À vous de jouer!
Agrégations

• Donner les requêtes nécessaires pour extraire les informations

suivantes:
1. Quelle est la moyenne des prix d’achat proposés par le vendeur “Second
Gear Diecast”?
2. Quel est le nombre de lignes de produits différentes disponibles en
stock actuellement?
3. Quel est le montant le plus élevé qui ait été payé en 2004?

35
Group BY (1)
SELECT

• GROUP BY : permet de partitionner le résultat en fonction des

valeurs d'un ou plusieurs attributs.
▪ Les seuls noms de colonnes (sauf pour les fonctions statistiques) qui
peuvent apparaître dans le GROUP BY sont ceux qui apparaissent dans le
SELECT.
• HAVING : définit les conditions que doivent respecter les groupes
pour être retenus, elle sélectionne les partitions souhaitées
▪ Elle ne peut inclure que les conditions dont le premier terme est une
fonction statistique.
▪ La clause HAVING est aux groupes (GROUP BY) ce que la clause WHERE
est aux lignes (SELECT)

36
Group BY (2)
SELECT

• Il est à noter que:

▪ HAVING filtre les enregistrements qui fonctionnent sur les
résultats groupés de GROUP BY.
▪ HAVING s'applique aux enregistrements de groupes, tandis que
WHERE s'applique aux enregistrements individuels.
▪ Seuls les groupes qui répondent aux critères de l'option HAVING
seront retournés.
▪ HAVING nécessite la présence d'une clause GROUP BY.
▪ WHERE et HAVING peuvent être dans la même requête.

37
Exemples de GROUP BY (1)
SELECT

• Considérons la table Car ci-après: Registration Brand Color Power Price

123ABC75 Renault Red 7 10 000
123ABC92 Peugeot Black 5 2 500
123ATT77 Rover Blue 6 13 000
698KLM45 Citroën Green 5 5 000
956BZD59 Peugeot Yellow 5 16 000
156AMN86 Renault Blue 7 5 250

• Q1: Quelle est la somme des prix des voitures par puissance dans la table
CAR ?
Power SUM (price)
SELECT power, SUM(price) FROM car 5 23500
GROUP BY power; 6 13000
7 15250

38
Exemples de GROUP BY (2)
SELECT

• Considérons la table Car ci-après: Registration Brand Color Power Price

123ABC75 Renault Red 7 10 000
123ABC92 Peugeot Black 5 2 500
123ATT77 Rover Blue 6 13 000
698KLM45 Citroën Green 5 5 000
956BZD59 Peugeot Yellow 5 16 000
156AMN86 Renault Blue 7 5 250

• Q2: Quelle est la somme des prix des voitures selon la puissance dans la
table CAR (limiter l'affichage aux partitions contenant au moins 2
éléments) ?
Power SUM (price)
SELECT power, SUM(price) FROM car
5 23500
GROUP BY power HAVING COUNT(*) > 1;
7 15250

39
Group BY avec ROLLUP
SELECT

• L’ajout de la commande WITH ROLLUP avec GROUP BY permet

d’ajouter une ligne représentant une “super-agrégation” sur
l’ensemble des lignes.
▪ Par exemple, si la fonction d’agrégation permet de calculer la
moyenne du prix des voitures par puissance, le ROLLUP permet
d’ajouter une ligne affichant la moyenne des prix de toutes les
voitures, indépendamment du groupement.
▪ Exemple: Power AVG (price)
4 111000
5 7833.33
SELECT power, AVG(price) FROM car 6 13000
GROUP BY power WITH ROLLUP; 7 7625
NULL 23250

40
À vous de jouer!
Group By

• Donner les requêtes nécessaires pour extraire les informations

suivantes:
1. Donner le nombre de commerciaux (Sales Rep) par bureau.

2. Calculer les revenus des ventes réalisées pour les produits dont le total
des ventes a dépassé 1100 unités (table orderDetails).

41
Sémantique d’Agrégation et de Groupement
SELECT

• L’ordre d’exécution des requêtes d’agrégation et de groupement se

fait comme suit:
1. Exécuter le FROM et le WHERE
2. Grouper selon la clause GROUP BY
3. Appliquer la condition dans HAVING à chaque groupe (et
calculer les agrégats s’ils existent)
4. Calculer les agrégats dans la requête SELECT et afficher le
résultat

42
Sémantique d’Agrégation et de Groupement
SELECT

Registration Brand Color Power Price

SELECT power, SUM(price) FROM car 123ABC75 Renault Red 7 10 000

123ABC92 Peugeot Black 5 2 500

WHERE price 5000
123ATT77 Rover Blue 6 13 000
GROUP BY power HAVING COUNT(*) > 1;
698KLM45 Citroën Green 5 5 000

956BZD59 Peugeot Yellow 5 16 000

156AMN86 Renault Blue 7 5 250

1. Exécuter le FROM et le WHERE

Registration Brand Color Power Price

123ABC75 Renault Red 7 10 000

123ATT77 Rover Blue 6 13 000

956BZD59 Peugeot Yellow 5 16 000

156AMN86 Renault Blue 7 5 250

43
>
=
Sémantique d’Agrégation et de Groupement
SELECT

Registration Brand Color Power Price

SELECT power, SUM(price) FROM car 123ABC75 Renault Red 7 10 000

WHERE price 5000 123ATT77 Rover Blue 6 13 000

GROUP BY power HAVING COUNT(*) > 1; 956BZD59 Peugeot Yellow 5 16 000

156AMN86 Renault Blue 7 5 250

2. Grouper selon la clause GROUP BY Power Registration Brand Color Price

5 956BZD59 Peugeot Yellow 16 000

6 123ATT77 Rover Blue 13 000

123ABC75 Renault Red 10 000

7
156AMN86 Renault Blue 5 250

44
>
=
Sémantique d’Agrégation et de Groupement
SELECT

Power Registration Brand Color Price

SELECT power, SUM(price) FROM car 1
5 956BZD59 Peugeot Yellow 16 000
WHERE price 5000 6 123ATT77 Rover Blue 13 000 1
GROUP BY power HAVING COUNT(*) > 1; 123ABC75 Renault Red 10 000
7 2
156AMN86 Renault Blue 5 250

3. Appliquer la condition dans HAVING Power Registration Brand Color Price

123ABC75 Renault Red 10 000

à chaque groupe 7
156AMN86 Renault Blue 5 250

45
>
=
Sémantique d’Agrégation et de Groupement
SELECT

SELECT power, SUM(price) FROM car Power Registration Brand Color Price

123ABC75 Renault Red 10 000

WHERE price 5000 7
156AMN86 Renault Blue 5 250
GROUP BY power HAVING COUNT(*) > 1;

4. Calculer les agrégats dans la Power SUM (Price)

7 15 250
requête SELECT et afficher le résultat

46
>
=
SELECT: Autres Opérations
SELECT
• Calcul sur des colonnes
▪ Il est possible d’effectuer des calcul sur des colonnes en utilisant:
o Des constantes
o Des noms de champs
o Des opérateurs: +, -, *, /, div, mod, etc.
o Des fonctions: sin, exp, etc.
• Exemple:
SELECT registration, price * 1.19 FROM car ;

Registration Brand Color Power Price Registration Price * 1.19

123ABC75 Renault Red 7 10 000 123ABC75 11 900

123ABC92 Peugeot Black 5 2 500 123ABC92 2 975

123ATT77 Rover Blue 6 13 000 123ATT77 15 470

47
SELECT: Autres Opérations
SELECT
• (Re)Nommage d’une colonne
▪ Il est possible de (re)nommer une colonne dans une requête SELECT en
utilisant le mot clef AS.

SELECT <expression> AS <nom> FROM <table> … ;

• Exemple:

SELECT registration, price * 1.19 AS price_TTC

FROM car ;

Registration Brand Color Power Price Registration Price_TTC

123ABC75 Renault Red 7 10 000 123ABC75 11 900

123ABC92 Peugeot Black 5 2 500 123ABC92 2 975

123ATT77 Rover Blue 6 13 000 123ATT77 15 470

48
SELECT: Autres Opérations
SELECT
• Tri
▪ Il est possible de trier les résultats d’un ou plusieurs attributs par
ordre ascendant (par défaut) ou descendant en utilisant le mot clef
ORDER BY.
SELECT <expression> FROM <table> ORDER BY
<colonne> [DESC | ASC] ;

• Exemple:
SELECT registration, price FROM car ORDER BY
price DESC;

Registration Brand Color Power Price Registration Price

123ABC75 Renault Red 7 10 000 123ATT77 13 000

123ABC92 Peugeot Black 5 2 500 123ABC75 10 000

123ATT77 Rover Blue 6 13 000 123ABC92 2 500

49
Exemple de ORDER BY
SELECT

• Considérons la table Car ci-après: Registration Brand Color Power Price

123ABC75 Renault Red 7 10 000

123ABC92 Peugeot Black 5 2 500

123ATT77 Rover Blue 6 13 000

698KLM45 Citroën Green 5 5 000

956BZD59 Peugeot Yellow 5 16 000

156AMN86 Renault Blue 7 5 250

• Q: Affiche tous les Tuples de la table CAR triés sur l'attribut Puissance par
ordre décroissant, puis sur l'attribut Couleur (ordre alphabétique) par
ordre croissant. Registration Brand Color Power Price

156AMN86 Renault Blue 7 5 250

123ABC75 Renault Red 7 10 000

123ATT77 Rover Blue 6 13 000

SELECT * FROM car
123ABC92 Peugeot Black 5 2 500
ORDER BY power DESC, color; 698KLM45 Citroën Green 5 5 000

956BZD59 Peugeot Yellow 5 16 000

50
À vous de jouer!
SELECT: Autres Opérations

• Calculer les revenus des ventes de chaque commande (dans la

table orderdetails) dans une nouvelle colonne Revenus, et les
classer par ordre descendant de valeur.

51
DML: Data Manipulation Language

OPÉRATEURS ET FONCTIONS

52
Fonctions et Opérateurs Numériques
Opérateurs et Fonctions
• Plusieurs fonctions et opérateurs numériques sont supportés par SQL
▪ Diffèrent d’un SGBD à un autre
▪ Pour MYSQL, en plus des opérateurs classiques (+, -, *, /), plusieurs autres :
o MOD ou % : Modulo
o DIV: Division entière
o ABS() : valeur absolue
o CEIL() ou CEILING(): Retourne la plus petite valeur entière non inférieure à
l’argument
o FLOOR(): Retourne la plus grande valeur entière non supérieure à l’argument
o POW() ou POWER(): Retourne l'argument élevé à la puissance spécifiée
o …
▪ La liste exhaustive se trouve ici: [Link]
[Link]

53
Fonctions et Opérateurs Numériques
Opérateurs et Fonctions

• Considérons la table Car ci-après: Registration Brand Color Power Price

123ABC75 Renault Red 7 10 000

123ABC92 Peugeot Black 5 2 500

123ATT77 Rover Blue 6 13 000

698KLM45 Citroën Green 5 5 000

956BZD59 Peugeot Yellow 5 16 000

156AMN86 Renault Blue 7 5 250

SELECT
price,
price / 3 AS ‘Division Std’, Price Division Std Division Reste
price DIV 3 AS ‘Division’, 10 000 3333.3333 3333 1
MOD(price, 3) AS ‘Reste’
5 250 1750.0000 1750 0
FROM car
WHERE power = 7;

54
Fonctions et Opérateurs pour Chaînes de Caractères
Opérateurs et Fonctions

• Opérateurs de chaînes de caractères:

▪ Plusieurs opérateurs sont définis, on cite par exemple:
o Length(): Retourne le nombre de caractères de la chaîne
o Concat(): Retourne la concaténation des chaînes données en paramètre
o Lower(): Retourne l'argument en minuscules
o Repeat(): Répète une chaîne le nombre de fois spécifié
o …
▪ La liste exhaustive se trouve ici: [Link]
en/[Link]

55
Fonctions et Opérateurs pour Chaînes de Caractères
Opérateurs et Fonctions

• Considérons la table Car ci-après: Registration Brand Color Power Price

123ABC75 Renault Red 7 10 000

123ABC92 Peugeot Black 5 2 500

123ATT77 Rover Blue 6 13 000

698KLM45 Citroën Green 5 5 000

956BZD59 Peugeot Yellow 5 16 000

156AMN86 Renault Blue 7 5 250

SELECT
Text
concat(‘The car ‘, registration,
‘ is ‘,lower(color)) AS text The car 123ABC75 is red
FROM car The car 156AMN86 is blue
WHERE power = 7;

56
Fonctions et Opérateurs pour Date et Heure
Opérateurs et Fonctions

• Opérateurs de Date et Heure:

▪ Plusieurs opérateurs sont définis, on cite par exemple:
o Date(), Year(), Month(), Day(), Hour()…: Retourne les éléments associés de
la date/heure
o Str_to_date(): Convertit une chaîne en date
o Now(): Retourne la date et heure courante
o Date_Sub(): Soustrait une valeur temporelle (intervalle) d'une date
o Datediff (): Soustrait deux dates
o …
▪ La liste exhaustive se trouve ici: [Link]
en/[Link]

57
Fonctions et Opérateurs pour Date et Heure
Opérateurs et Fonctions

• Considérons la table Achat ci-après: Num_proprio Num_vehicule Date

1000 90FE75 1989-09-10

1500 43XY97 1996-02-05

1000 56GT98 1991-03-06

1350 43ZT88 2007-12-28

1500 57TG92 1991-06-26

SELECT
concat(‘The car ‘, num_vehicule,
‘ was bought on ‘,dayname(date),’, ’,
monthname(date),
‘ ‘,day(date),’, ’,year(date), ‘ - Text

‘,datediff(now(),date),’ days ago’) The car 43ZT88 was bought on Friday,

December 28, 2007 - 5491 days ago
AS text
FROM achat
WHERE num_proprio = 1350;

58
Fonctions et Opérateurs de Contrôle de Flux
Opérateurs et Fonctions
• Ces fonctions et opérateurs permettent de réaliser des tests sur les
valeurs des objets dans les requêtes SQL.
• On distingue principalement quatre (4) fonctions
▪ CASE: Opérateur case
CASE <val> WHEN <val_comp> THEN <res>
[WHEN <val_comp> THEN <res> ] [ELSE <res> ]
END

▪ IF(exp1, exp2, exp3): si exp1 est vraie, retourne exp2, sinon retourne
exp3
▪ IFNULL(exp1, exp2): si exp1 est nulle, retourne exp2, sinon retourne
exp1
▪ NULLIF(exp1, exp2): retourne NULL si exp1 = exp2

59
.
.
.
Fonctions et Opérateurs de Contrôle de Flux
Opérateurs et Fonctions

• Considérons la table Achat ci-après: Num_proprio Num_vehicule Date

1000 90FE75 1989-09-10

1500 43XY97 1996-02-05

1000 56GT98 1991-03-06

1350 43ZT88 NULL

1500 57TG92 1991-06-26

Num_proprio Année
SELECT 1000 1989
num_proprio, IFNULL(year(date),’Année 1500 1996
non déf nie’) AS année 1000 1991
FROM achat 1350 Année non définie

1500 1991

Alain?
SELECT Alain
CASE num_proprio WHEN 1000 THEN ‘Alain’ Pas Alain
ELSE ‘Pas Alain’ END AS ‘Alain?’ Alain
FROM achat Pas Alain

Pas Alain

60
i
Fonction ISNULL() vs Opérateur IS NULL
Opérateurs et Fonctions
• Il existe deux façons de vérifier si un objet dans la base a une valeur nulle:
▪ La fonction isnull(obj) -> retourne 1 si l’objet est nul, 0 sinon

SELECT * FROM car WHERE isnull(brand);

▪ L’opérateur obj IS NULL

SELECT * FROM car WHERE brand ISNULL;

▪ Il est recommandé d'utiliser IS NULL au lieu de la fonction isnull() dans un prédicat (tel
que la clause WHERE), car la première utilise une recherche d'index alors que la seconde
effectue un balayage complet des enregistrements.
o Utilisez la fonction isnull() dans les requêtes sélectionnées, par exemple :
SELECT registration, isnull(brand) AS ‘No Brand Defined’ FROM car;

61
À vous de jouer!
Opérateurs et Fonctions

• Donner pour chaque produit acheté la somme totale (arrondie)

de son achat par commande, l’année de cette dernière ainsi que
la première lettre (en minuscule) de son statut.

62
DML: Data Manipulation Language

JOINTURES

63
JOIN: Définition
Jointures
• Une jointure entre deux tables retourne toutes les combinaisons de
leurs tuples qui remplissent certaines conditions spécifiées
• Sa syntaxe est la suivante :

SELECT champs,… FROM table1 [AS tab1]

[INNER | {{LEFT | RIGHT | FULL} [OUTER]}] JOIN
table2 [AS tab2] {ON Condition | USING(Colonnes)};

• La condition de la commande ON permet de comparer les tables jointes

à travers des champs dont les valeurs sont identiques et comparables.
• USING peut être utilisé si la condition de jointure concerne une colonne
avec le même nom dans les deux collections de données de la jointure.
• AS : est optionnel, il permet d'utiliser un alias sur la table.

64
JOIN: Exemple
Jointures
Car
• Considérons les tables ci-après: Registration Brand Color Power Price

123ABC75 Renault Red 7 10 000

Brands
123ABC92 Peugeot Black 5 2 500
Brand Headquarters
123ATT77 Rover Blue 6 13 000
Renault Paris
698KLM45 Citroën Green 5 5 000
Peugeot Lille
956BZD59 Peugeot Yellow 5 16 000
BMW Bern
156AMN86 Renault Blue 7 5 250

SELECT registration, [Link], color

• Q: Afficher l'immatriculation, la marque et la couleur
FROM car AS c JOIN brand AS b
des voitures qui existent dans la table Brand. ON [Link] = [Link]

≡
Registration Brand Color SELECT registration, [Link], color
FROM car AS c, brand AS b
123ABC75 Renault Red
WHERE [Link] = [Link]
123ABC92 Peugeot Black
≡
956BZD59 Peugeot Yellow
SELECT registration, brand, color
156AMN86 Renault Blue FROM car JOIN brand
USING (brand);

65
Sémantique de Jointure
Jointures

SELECT R.A A
FROM R,S Résulat 3
WHERE R.A = S.B;
3

R Projection
A
A B C
1
1 2 3
3
1 3 4 A B C
Produit Cartésien 1 3 5 Sélection 3 3 4
S
B C 3 2 3 3 3 5

2 3 3 3 4

3 4 3 3 5

3 5

66
Opérateurs de Jointure (1)
Jointures
• Considérons trois tables, ayant une colonne commune A:

R S
T
SELECT * SELECT *
FROM R,S,T FROM R,S,T
WHERE R.A = S.A; WHERE R.A S.A;

R S R S
T T

67
<
>
Opérateurs de Jointure (2)
Jointures

SELECT * SELECT *
FROM R,S,T
R S FROM R,S,T
R S
WHERE R.A = S.A WHERE R.A S.A
OR R.A = T.A; T AND R.A T.A; T

SELECT * R S SELECT * R S
FROM R,S,T FROM R,S,T
WHERE R.A = S.A T WHERE R.A = T.A T
AND R.A = T.A; AND R.A S.A;

SELECT * SELECT *
FROM R,S,T R S FROM R,S,T R S
WHERE NOT (R.A = S.A WHERE NOT (R.A = S.A
AND R.A = T.A); T OR R.A = T.A T
OR S.A = T.A);

68
<
<
>
>
<
>
Types de Jointure (1)
Jointures
• Cross Join : Jointure sans conditions (produit cartésien)
R S

SELECT *
FROM R,S;

• Inner Join : Retourne les tuples combinés des deux tables dont les valeurs
correspondent à la condition de jointure. R S

SELECT *
FROM R INNER JOIN S
WHERE [Link] = [Link];

69
Types de Jointure (2)
Jointures
• Left Join : Retourne les tuples combinés des deux tables dont les valeurs correspondent à la
condition de jointure, ainsi que ceux de la table de gauche qui ne se trouvent pas dans la
table de droite. R S

SELECT *
FROM R LEFT JOIN S X
WHERE [Link] = [Link]; X

• Right Join : Retourne les tuples combinés des deux tables dont les valeurs correspondent à la
condition de jointure, ainsi que ceux de la table de droite qui ne se trouvent pas dans la table
R S
de gauche.
SELECT *
FROM R RIGHT JOIN S
X
WHERE [Link] = [Link];
X

70
Types de Jointure (3)
Jointures
• Full Outer Join (non supportée par MySQL) : Retourne:
▪ les tuples combinés des deux tables dont les valeurs
correspondent à la condition de jointure,
▪ ceux de la table de gauche qui ne se trouvent pas dans la table
de droite.
▪ ceux de la table de droite qui ne se trouvent pas dans la table
de gauche. R S

SELECT *
FROM R FULL OUTER JOIN S
X X
WHERE [Link] = [Link];
X X

71
Types de Jointure (4)
Jointures

• Natural Join : Équivalente à un Inner Join, mais sans explicitement

spécifier la condition.
▪ Valable uniquement si les colonnes à joindre ont le même nom.
▪ Ne marchera pas correctement si plusieurs colonnes ont le même nom!

R S
SELECT *
FROM R NATURAL JOIN S;

72
À vous de jouer!
Jointures

• Tester tous les types de jointures (inner, right, left, natural) sur les
tables Customers et Payments de la base de données classicModels.

73
DML: Data Manipulation Language

REQUÊTES IMBRIQUÉES

74
Définition
Requêtes Imbriquées

• Une requête imbriquée est appelée en anglais Subquery ou Inner query

ou Nested query
• C’est une requête SELECT imbriquée dans la clause WHERE d’une autre
requête.
• Utilisée pour retourner des données qui seront utilisées dans la
requête principale comme condition, pour restreindre davantage les
données à extraire.

SELECT <expression> FROM <table>

WHERE <expression> <opérateur>
( SELECT <expr2> FROM <table2>);

75
Exemple
Requêtes Imbriquées

• Considérons la table Car ci-après: Registration Brand Color Power Price

123ABC75 Renault Red 7 10 000

123ABC92 Peugeot Black 5 2 500

123ATT77 Rover Blue 6 13 000

698KLM45 Citroën Green 5 5 000

956BZD59 Peugeot Yellow 5 16 000

156AMN86 Renault Blue 7 5 250

• Q: Afficher les numéros des voitures dont le prix est inférieur au prix
moyen de toutes les voitures de la base de données.

SELECT registration FROM car Registration

123ABC92
WHERE price <
698KLM45
(SELECT AVG(price) FROM car);
156AMN86

AVG = 8625

76
Prédicats
Requêtes Imbriquées

• En plus des opérateurs de base, il est possible d’utiliser d’autres

prédicats pour représenter les conditions sur les valeurs retournées
par la requête imbriquée:
▪ IN: L’opérateur de comparaison est l’égalité, et l’opération logique entre
les valeurs est OR
▪ ANY: Permet de vérifier si au moins une valeur de la liste satisfait la
condition
▪ ALL: Permet de vérifier si la condition est satisfaite pour toutes les
valeurs de la liste
▪ EXISTS: Si la sous-requête retourne un résultat, la valeur retournée est
True, sinon c’est False

77
Exemple avec Prédicats
Requêtes Imbriquées
Car

• Considérons les tables ci-après: Registration Brand Color Power Price

123ABC75 Renault Red 7 10 000

Brands
123ABC92 Peugeot Black 5 2 500
Brand Headquarters
123ATT77 Rover Blue 6 13 000
Renault Paris
698KLM45 Citroën Green 5 5 000
Peugeot Lille
956BZD59 Peugeot Yellow 5 16 000
BMW Bern
156AMN86 Renault Blue 7 5 250

• Q: Afficher les informations des voitures dont nous connaissons le QG.

SELECT * FROM car
Registration Brand Color Power Price WHERE EXISTS
123ABC75 Renault Red 7 10 000 ( SELECT * FROM brands
123ABC92 Peugeot Black 5 2 500 WHERE brand = [Link] );
956BZD59 Peugeot Yellow 5 16 000 ≡
156AMN86 Renault Blue 7 5 250
SELECT * FROM car
WHERE brand IN
( SELECT brand FROM brands );

78
À vous de jouer!
Requêtes Imbriquées

• Utiliser les requêtes imbriquées pour donner la liste des

produits qui ont été commandés en grande quantité (plus de 40
pièces par commande)

79
DML: Data Manipulation Language

OPÉRATEURS ENSEMBLISTES

80
Opérateurs Ensemblistes: Définition
Opérateurs Ensemblistes

• Les opérateurs ensemblistes sont utilisés entre deux requêtes pour

former des combinaisons booléennes entre leurs résultats.

• Ces opérateurs sont:

▪ INTERSECT
▪ UNION
▪ EXCEPT

• Les résultats retournés par ces opérateurs ne contiennent pas de

duplicatas par défaut.
▪ Pour conserver les duplications, ajouter ALL.

81
INTERSECT
Opérateurs Ensemblistes

• INTERSECT permet de faire une intersection entre deux ensembles.

▪ Est supporté dans MySQL à partir de la version 8.0.31

{ FROM R,S WHERE R.A = S.A

SELECT R.A
E1
INTERSECT
E1 E2
{ FROM R,T WHERE R.A = T.A
SELECT R.A
E2

82
UNION
Opérateurs Ensemblistes

• UNION permet de retourner tous les tuples des deux ensembles.

{ FROM R,S WHERE R.A = S.A

SELECT R.A
E1
UNION
E1 E2
{ FROM R,T WHERE R.A = T.A
SELECT R.A
E2

83
EXCEPT
Opérateurs Ensemblistes

• EXCEPT permet de retourner tous les tuples dans le premier ensemble

qui n’existent pas dans le deuxième.
▪ Est supporté dans MySQL à partir de la version 8.0.31

{ FROM R,S WHERE R.A = S.A

SELECT R.A
E1
EXCEPT
E1 E2
{ FROM R,T WHERE R.A = T.A
SELECT R.A
E2

84
Opérateurs Ensemblistes : Exemple
Opérateurs Ensemblistes
Car
• Considérons les tables ci-après: Registration Brand Color Power Price

123ABC75 Renault Red 7 10 000

Brands
123ABC92 Peugeot Black 5 2 500
Brand Headquarters
123ATT77 Rover Blue 6 13 000
Renault Paris
698KLM45 Citroën Green 5 5 000
Peugeot Lille
956BZD59 Peugeot Yellow 5 16 000
BMW Bern
156AMN86 Renault Blue 7 5 250

• Q: Afficher tous les Tuples de la table CAR dont la puissance est égale à 5 ou 7
chevaux.
Registration Brand Color Power Price
SELECT * FROM car WHERE power = 5;
UNION
123ABC75 Renault Red 7 10 000
SELECT * FROM car WHERE power = 7;
123ABC92 Peugeot Black 5 2 500

698KLM45 Citroën Green 5 5 000 ≡

956BZD59 Peugeot Yellow 5 16 000 SELECT * FROM car

156AMN86 Renault Blue 7 5 250

WHERE power = 5 OR power = 7;

85
 Opérateurs Ensemblistes : Exemple (2)
Opérateurs Ensemblistes
Car
• Considérons les tables ci-après: Registration Brand Color Power Price

123ABC75 Renault Red 7 10 000

Brands
123ABC92 Peugeot Black 5 2 500
Brand Headquarters
123ATT77 Rover Blue 6 13 000
Renault Paris
698KLM45 Citroën Green 5 5 000
Peugeot Lille
956BZD59 Peugeot Yellow 5 16 000
BMW Bern
156AMN86 Renault Blue 7 5 250
Citroën Paris

• Q: Afficher les QG des marques qui produisent des voitures Rouges et Vertes.

Attention! Paris est retournée!

{
SELECT headquarters FROM car, brands
E1 WHERE [Link] = [Link] AND color=“Red”
Headquarters
INTERSECT

{
Paris SELECT headquarters FROM car, brands
E2 WHERE [Link] = [Link] AND color=“Green”;

86
Opérateurs Ensemblistes : Solution
Opérateurs Ensemblistes

• On choisit bien les attributs sélectionnés!

SELECT [Link], headquarters FROM car, brands

WHERE [Link] = [Link] AND color = “Red”
INTERSECT
SELECT [Link], headquarters FROM car, brands
WHERE [Link] = [Link] AND color = “Green”;

• Ou bien, on utilise les requêtes imbriquées (nested queries).

SELECT DISTINCT headquarters

FROM brands
WHERE brand IN (
SELECT brand FROM car WHERE color = ‘Red’ )
AND brand IN (
SELECT brand FROM car WHERE color = ‘Green’ )

87
À vous de jouer!
Opérateurs Ensemblistes

• Donner les noms (uniques, sans redondance) des produits qui ont été
commandés en février 2003 ou en juillet 2004:

▪ En utilisant les opérateurs ensemblistes.

▪ En utilisant les requêtes imbriquées.

▪ En utilisant les prédicats.

88
Chp3 - Le Langage SQL

DDL: DATA DESCRIPTION LANGUAGE

89
DDL: Définition
DDL: Data Definition Language
• C’est l’ensemble des commandes SQL qui peuvent être utilisées pour définir le schéma de la
base de données
• Utilisé pour créer et modifier la structure des objets de la base de données
▪ On parle ici de la structure, pas des données.
▪ Un objet de la base peut être: une table, un indexe, une fonction, une vue, une procédure
stockée ou un déclencheur
• Commandes DDL:
▪ CREATE : Créer la base de données ou ses objets.
▪ DROP : Supprimer des objets de la base de données.
▪ ALTER : Modifier la structure de la base de données.
▪ TRUNCATE : Supprimer tous les enregistrements d'une table, y compris tous les espaces alloués
aux enregistrements.
▪ COMMENT : Ajouter des commentaires au dictionnaire de données.
▪ RENAME : Renommer un objet existant dans la base de données.

90
DDL: Data Description Language

COMMANDES DE BASE

91
Structure d’une Table
Commandes de Base

• Les tables peuvent êtres créées à n’importe quel moment, même lorsque
des utilisateurs utilisent la base de données.

• Vous n’avez pas besoin de spécifier la taille de la table. Cette taille est
limitée par l’espace alloué à la base de données.

• La structure d’une table peut être modifiée dynamiquement.

92
Règles de Nommage d’une Table
Commandes de Base

• Les noms des tables et des colonnes doivent suivre les règles de
nommage suivantes :
▪ Commencer par une lettre
▪ Avoir une taille entre 1 et 30 caractères
▪ Contenir uniquement les caractères : A-Z, a-z, 0-9, _, $ et #
▪ Ne pas avoir un nom d’un mot réservé par le SGBD (select par exemple)
▪ Pas de duplication de nom pour un autre objet du même utilisateur
• Remarques :
▪ Les noms ne sont pas sensibles à la casse.
▪ Les noms devraient être significatifs.

93
SHOW, USE & DESCRIBE
Commandes de Base
• La commande SHOW DATABASES; permet d’afficher la
liste des bases de données existantes dans votre
serveur MySQL.
▪ Elle va afficher un résultat semblable au suivant dans
MySQL Workbench

• La commande USE <nom_bd>; permet de sélectionner

la base de données à utiliser pour les commandes SQL
à venir.
• La commande DESCRIBE <nom_table>; permet de
donner la structure de la table donnée.
▪ Elle va afficher un résultat semblable au suivant dans
MySQL Workbench

94
 CREATE DATABASE
Commandes de Base
• La commande CREATE DATABASE permet de créer une nouvelle base de données dans MySQL.

CREATE DATABASE [IF NOT EXISTS] nom_bd

[CHARACTER SET nom_charset]
[COLLATE nom_collation]
• Syntaxe * :
• Le character set représente l’ensemble de caractères autorisés dans une chaîne.
▪ MySQL prend en charge différents jeux de caractères qui vous permettent de stocker
presque tous les caractères d'une chaîne. Par défaut, c’est latin1
• Une collation MySQL est un ensemble de règles utilisées pour comparer les caractères dans un
character set particulier.
▪ Chaque jeu de caractères dans MySQL possède au moins une collation par défaut. Par
défaut, c’est latin1_swedish_ci
• Il est possible d’afficher les character set et collation de votre base courante avec la commande:
▪ SELECT @@character_set_database, @@collation_database;

* La syntaxe MySQL complète se trouve ici : [Link] 95

 CREATE TABLE
Commandes de Base
• La commande CREATE TABLE permet de créer une table et de définir ses colonnes, leurs types
et leurs contraintes d’intégrité.

CREATE TABLE [IF NOT EXISTS] nom_table (

<colonne1>, <colonne2>,........,
<contraintes>
);
• Syntaxe * :

nom_col type_col
[NOT NULL | NULL]
[DEFAULT {literal | (expr)} ]
[AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
…
• Syntaxe des colonnes:

[CONSTRAINT [nom_cont]] CHECK (expr) [[NOT] ENFORCED]

• Syntaxe des contraintes:

* La syntaxe MySQL complète se trouve ici : [Link] 96

Types de Données
Commandes de Base
• Le type de données d'une colonne définit la valeur que la colonne peut contenir :
nombre entier, caractère, monnaie, date et heure, binaire, etc.
▪ Vous trouverez la liste exhaustive des types supportés par MYSQL ici: https://
[Link]/doc/refman/8.0/en/[Link]
• Voici les types les plus communs:
Type Description
Une chaîne de longueur FIXE. Le paramètre taille spécifie la longueur de la colonne en caractères - peut
Char (<taille>) être compris entre 0 et 255. La valeur par défaut est 1
Chaîne de longueur VARIABLE. Le paramètre taille spécifie la longueur maximale de la colonne en
Varchar (<taille>) caractères - peut être compris entre 0 et 65535.
BOOL / BOOLEAN Le zéro est considéré comme faux, les valeurs non nulles sont considérées comme vraies.
Un nombre entier moyen. La plage signée va de -2147483648 à 2147483647. La plage non signée est
INT/INTEGER comprise entre 0 et 4294967295.
FLOAT Un nombre réel à virgule flottante.
DATE Une date. Format : AAAA-MM-JJ. La plage prise en charge va de '1000-01-01' à '9999-12-31'.

97
Exemple de Création (1)
Commandes de Base

• Considérons la table Car ci-après: Registration Brand Color Power Price

123ABC75 Renault Red 7 10 000
123ABC92 Peugeot Black 5 2 500
123ATT77 Rover Blue 6 13 000
698KLM45 Citroën Green 5 5 000
956BZD59 Peugeot Yellow 5 16 000
156AMN86 Renault Blue 7 5 250

• Q1: Créer la base de données car_rentals.

CREATE DATABASE car_rentals;

USE car_rentals;

98
Exemple de Création (2)
Commandes de Base
Registration Brand Color Power Price
• Considérons la table Car ci-après:
123ABC75 Renault Red 7 10 000
123ABC92 Peugeot Black 5 2 500
123ATT77 Rover Blue 6 13 000
698KLM45 Citroën Green 5 5 000
956BZD59 Peugeot Yellow 5 16 000
156AMN86 Renault Blue 7 5 250

• Q2: Créer la table car, en spécifiant que “registration” représente la clef primaire, et que
le prix doit être non nul et supérieur à 1000.

CREATE TABLE car (

registration Varchar(8) PRIMARY KEY,
brand Varchar(10),
color Varchar(10),
power int,
price int NOT NULL
CONSTRAINT c_price CHECK (price > 1000)
);

99
 ALTER TABLE
Commandes de Base
• La commande ALTER TABLE permet de changer la structure d’une table en:
▪ Ajoutant ou supprimant des colonnes
▪ Créant ou détruisant des indexes
▪ Changeant le type de colonnes existantes
▪ Renommant la table ou des colonnes
▪ Changeant le moteur de stockage
▪ etc.
• Syntaxe simplifiée * :

ALTER TABLE nom_table

[ADD <colonne> <type> |
DROP [COLUMN] <colonne> |
MODIFY <colonne> <type> |
CHANGE <old_col> <new_col> <type> |
RENAME <old_col> TO <new_col>]

* La syntaxe MySQL complète se trouve ici : [Link] 100

Exemple de Modification (1)
Commandes de Base

• Considérons la table Car ci-après: Registration Brand Color Power Price

123ABC75 Renault Red 7 10 000
123ABC92 Peugeot Black 5 2 500
123ATT77 Rover Blue 6 13 000
698KLM45 Citroën Green 5 5 000
956BZD59 Peugeot Yellow 5 16 000
156AMN86 Renault Blue 7 5 250

• Q1: Renommer la colonne color en colour.

ALTER TABLE car

RENAME COLUMN color TO colour;

101
Exemple de Modification (2)
Commandes de Base

• Considérons la table Car ci-après: Registration Brand Color Power Price

123ABC75 Renault Red 7 10 000
123ABC92 Peugeot Black 5 2 500
123ATT77 Rover Blue 6 13 000
698KLM45 Citroën Green 5 5 000
956BZD59 Peugeot Yellow 5 16 000
156AMN86 Renault Blue 7 5 250

• Q2: Modifier le type de la colonne price en Float.

ALTER TABLE car

MODIFY Price F oat;

Attention, la contrainte NOT NULL

est enlevée ici

102
l
DROP, TRUNCATE et RENAME
Commandes de Base
• La commande DROP <OBJET> <nom_obj> permet de supprimer un objet :
▪ DROP DATABASE <nom_db>: supprime une base de données et tout son contenu.
▪ DROP TABLE <nom_table>: supprime une table et tous ses enregistrements.
▪ DROP VIEW <nom_view>: supprime une vue.
▪ Etc.
• La commande TRUNCATE TABLE <nom_table> permet de vider une table de tout son
contenu.
▪ Similaire à DELETE, mais est classée comme commande DDL plutôt que DML, car elle
supprime la table et la recrée, plutôt que de supprimer les lignes une par une, ce qui est
plus rapide.
• La commande RENAME TABLE <nom_table> permet de renommer une table
▪ Similaire à ALTER <table> RENAME TO <nouv_nom_table>

103
Exemple de DROP, TRUNCATE et RENAME
Commandes de Base

• Considérons la table Car ci-après: Registration Brand Color Power Price

123ABC75 Renault Red 7 10 000
123ABC92 Peugeot Black 5 2 500
123ATT77 Rover Blue 6 13 000
698KLM45 Citroën Green 5 5 000
956BZD59 Peugeot Yellow 5 16 000
156AMN86 Renault Blue 7 5 250

• Q1: Renommer la table Car en Voiture.

RENAME TABLE car TO voiture;

104
Exemple de DROP, TRUNCATE et RENAME (2)
Commandes de Base

• Considérons la table Voiture ci-après:

Registration Brand Color Power Price
123ABC75 Renault Red 7 10 000
123ABC92 Peugeot Black 5 2 500
123ATT77 Rover Blue 6 13 000
698KLM45 Citroën Green 5 5 000
956BZD59 Peugeot Yellow 5 16 000
156AMN86 Renault Blue 7 5 250

• Q2: Vider la table Voiture, puis la supprimer.

• Q3: Supprimer ensuite la base de données Car_rentals;

TRUNCATE TABLE voiture;

DROP TABLE voiture;
DROP DATABASE car_rentals;

105
 À vous de jouer!
Commandes DDL de base

• Refaire les exemples vus précédemment dans MySQL Workbench:

1. Créer la base de données CarRentals.

2. Créer la table Car, en spécifiant que Registration représente la clef primaire, et que le prix doit être non
nul et supérieur à 1000.

3. Insérer quelques enregistrements en vérifiant que les contraintes sont bien respectées

4. Renommer la colonne Color en Colour Registration Brand Color Power Price

5. Modifier le type de la colonne Price en Float 123ABC75 Renault Red 7 10 000

123ABC92 Peugeot Black 5 2 500
6. Renommer la table Car en Voiture
123ATT77 Rover Blue 6 13 000
7. Vider la table Voiture, puis la supprimer. 698KLM45 Citroën Green 5 5 000
956BZD59 Peugeot Yellow 5 16 000
8. Supprimer ensuite la base de données CarRentals;
156AMN86 Renault Blue 7 5 250

106
DDL: Data Description Language

CLEFS ÉTRANGÈRES

107
 Clefs Étrangères: Définition
Clefs Étrangères
• Une clef étrangère (Foreign Keys) permet d'établir des références croisées entre des données
connexes entre les tables
• Les contraintes de clé étrangère (Foreign Key Constraints), aident à maintenir la cohérence des
données connexes.
• Une relation de clé étrangère implique une table parent, qui détient les valeurs initiales de la
colonne, et une table enfant dont les valeurs de colonne font référence aux valeurs de la colonne
parent.
▪ Une contrainte de clé étrangère est définie sur la table enfant.

[CONSTRAINT [nom_contr]] FOREIGN KEY

[nom_indexe] (nom_col, )
REFERENCES nom_tbl (nom_col,…)
[ON DELETE <ref>]
[ON UPDATE <ref>]

<ref
RESTRICT | CASCADE | SET NULL | NO ACTION

108
>
:
.
.
.
Actions Référentielles
Clefs Étrangères

• Lorsqu'une opération UPDATE ou DELETE affecte une valeur de clé dans la table
parent qui a des lignes correspondantes dans la table enfant, le résultat dépend
de l'action référentielle spécifiée par les sous-clauses ON UPDATE et ON DELETE de
la clause FOREIGN KEY.
▪ CASCADE: supprime ou met à jour automatiquement les rangées correspondantes
dans la table enfant
▪ SET NULL: définit la ou les colonnes de la clé étrangère dans la table enfant à NULL
▪ RESTRICT | NO ACTION: Rejette l'opération de suppression ou de mise à jour pour la
table parente. (Par défaut)
• MySQL rejette toute opération INSERT ou UPDATE qui tente de créer une valeur de clé
étrangère dans une table enfant s'il n'existe aucune valeur de clé candidate
correspondante dans la table parent.

109
Restrictions
Clefs Étrangères

• Nous listons ci-après les restrictions les plus communes des clefs
étrangères:
▪ Les tables parent et enfant doivent utiliser le même moteur de
stockage.
▪ Les colonnes correspondantes de la clef étrangère et de la clef
référencée doivent avoir des types de données similaires (de même
taille!).
▪ Une colonne ne peut pas avoir une référence de clef étrangère vers elle-
même.
▪ MySQL requiert des index sur les clefs étrangères et les clefs
référencées afin que les vérifications des clefs étrangères soient
rapides et ne nécessitent pas de balayage de la table.
110
Exemple de Clefs Étrangères
Clefs Étrangères
Car
• Considérons les tables ci-après: Registration Brand Color Power Price

123ABC75 Renault Red 7 10 000

Brands
123ABC92 Peugeot Black 5 2 500
Brand Headquarters
123ATT77 Rover Blue 6 13 000
Renault Paris
698KLM45 Citroën Green 5 5 000
Peugeot Lille
956BZD59 Peugeot Yellow 5 16 000
BMW Bern
156AMN86 Renault Blue 7 5 250

• Q1: Créer les deux tables.

CREATE TABLE brands (

brand Varchar(10) PRIMARY KEY, headquarters Varchar(10)
);
CREATE TABLE Car (
registration Varchar(8) PRIMARY KEY,
brand Varchar(10), color Varchar(10), power int, price int,
FOREIGN KEY (brand) REFERENCES brands(brand),
);

111
Exemple de Clefs Étrangères
Clefs Étrangères
Car
• Considérons les tables ci-après: Registration Brand Color Power Price

123ABC75 Renault Red 7 10 000

Brands
123ABC92 Peugeot Black 5 2 500
Brand Headquarters
123ATT77 Rover Blue 6 13 000
Renault Paris
698KLM45 Citroën Green 5 5 000
Peugeot Lille
956BZD59 Peugeot Yellow 5 16 000
BMW Bern
156AMN86 Renault Blue 7 5 250

• Q2: En supposant que les deux tables sont déjà créées, ajouter la contrainte de
clef étrangère adéquate, en vous assurant que la suppression d’une marque de
la table Brands supprimera automatiquement toutes les voitures appartenant à
cette marque.

ALTER TABLE car ADD FOREIGN KEY (brand) REFERENCES brands(brand) ON

DELETE CASCADE;

112
 À vous de jouer!
Commandes DDL de base

• Ré-exécuter les instructions 1-2 de l’exercice précédent

• Créer maintenant la table Brands.

• Modifier la table Car pour créer la relation entre les deux tables.

• Peupler maintenant les deux tables.

• Essayer de supprimer des enregistrements de la table Brands et observer ce qui se passe.

• Changer les actions référentielle de la clef étrangère et observer les résultats.

Car

Registration Brand Color Power Price

Brands 123ABC75 Renault Red 7 10 000

123ABC92 Peugeot Black 5 2 500

Brand Headquarters
123ATT77 Rover Blue 6 13 000
Renault Paris
698KLM45 Citroën Green 5 5 000
Peugeot Lille
956BZD59 Peugeot Yellow 5 16 000
BMW Bern
156AMN86 Renault Blue 7 5 250

113