BASES DE DONNÉES

Ingénieur Docteur
Lamine BOUGUEROUA
 Le langage SQL

 Introduction
 Norme
 Définition des données
 Manipulation des données

2
 Introduction
 Le langage SQL :(Structured Query Language)

 Définition : c’est un langage permettant la définition, la manipulation et

le contrôle des bases de données
 Il est se base sur l’algèbre relationnel
 En 1977, création du langage SEQUEL (Structured English Query
Language)
 SEQUEL continue de s’enrichir pour devenir SQL
 IBM sort sa 1ère version SQL en 1981
 En 1986, la première norme SQL (SQL-1) de l’ISO (International
Standard Organisation) apparaît
 SQL est actuellement implémenté par les principaux éditeurs de
SGBDR (Oracle, IBM DB2, Microsoft SQL Server, MySQL, Sybase,
etc.)

3
 SQL vs. Algèbre relationnel
 On se focalise sur l’implantation de l’algèbre relationnelle dans un
langage pratique nommé SQL.
 Plus de distinction entre schéma de relation et relation : notion de table
pour désigner les deux
 Une table SQL n’est pas un ensemble de tuples, mais un multi-
ensemble de tuples (ou bag)
 Exemple { 1,2,2,3 } est un multi-set, i.e. plusieurs occurrences possibles de
la même valeur, pas d’ordre.
 Problème : les propriétés usuelles sur les ensembles ne sont plus toujours
vérifiées sur les multi ensembles.

4
 Norme
 Le langage SQL :(Structured Query Language)

 SQL86
– version préliminaire

• SQL89 (SQL, SQL1)

– niveau minimal supporté

• SQL92 (SQL2)
– Support accru de l ’intégrité
– Le standard le plus répandu

• SQL-99 (SQL3)
– Extensions objet (UDT),
– requêtes récursives, déclencheurs,…

• SQL:2003 :
– introduction de fonctions pour la manipulation XML,…

5
 Définition
 Le langage SQL :(Structured Query Language)

 Remarques :
 SQL est un langage de requête, pas un langage de programmation
 Une instruction SQL peut s'écrire sur plusieurs lignes. Pour être
exécutée, l'instruction doit se terminer par un point-virgule
 Il n'y pas de variables ni de structures de contrôles (if, while, for)
 SQL est intégré dans les langages de programmation comme
PL/SQL, C ou Java

6
 Définition
 Le langage SQL :(Structured Query Language)

 Terminologie :
 Quelque terme change entre algèbre relationnelle et SQL

Terme relationnel formel SQL

Relation Table
Tuple Ligne / enregistrement
Attribut Colonne / champ
Clé primaire Primary Key
Ensemble de schémas Catalogue

7
 Types des données
 Dans les normes de SQL nous distinguons plusieurs types de données,
quelques exemples:

Type Intitulé Taille

INTEGER entiers signés 4 octets

BIGINT entiers signés 8 octets
REAL réels 4 octets
DOUBLE PRECISION réels 8 octets
NUMERIC [(précision, [longueur])] données entières et réelles
CHAR(longueur) chaînes de caractères de longueur fixe Long <255
VARCHAR(longueur) chaînes de caractères de longueur variable Long <2000
DATE date
TIMESTAMP date + heure
MONEY valeurs monétaires
TEXT chaînes de caractères de longueur variable
BOOLEAN valeurs Booléenne

8
 Types des données

 Exemple :

Type Exemple

INTEGER 2, 3, 459
REAL 3.27E-4, 24E5
DOUBLE PRECISION 3.27265378426E-4, 24E12
NUMERIC [p, l] , DEC[p,l] 2.5, 456.342, 6
DATE '1998-08-25'
TIMESTAMP '1998-08-25 [Link].25'

9
 Instructions SQL
 Les instructions SQL sont regroupées en catégories en fonction de leur
utilité et des entités manipulées. Nous pouvons distinguer cinq
catégories, qui permettent :
 La définition des éléments d’une base de données (tables, colonnes,
clefs, index, contraintes, . . .)
 La manipulation des données (insertion, suppression, modification, …)
 La gestion des droits d’accès aux données (acquisition et révocation
des droits)
 La gestion des transactions
 Le SQL intégré

10
 Instructions SQL
 SQL est alors décomposé en 5 parties :
1. LDD (langage de définition des données) : permet de modifier la structure de
la base de données

2. LMD (langage de manipulation des données) : permet de consulter /

modifier le contenu de la base de données

3. LCD (langage de contrôle des données) : permet de gérer les privilèges,

c'est-à-dire les utilisateurs et les actions qu'ils peuvent entreprendre
4. LCT (langage de contrôle des transactions) : permet de gérer les
transactions, c'est-à-dire rendre atomique divers ordres enchaînés en séquence

5. SQL procedural : est un ensemble d'outils pour que SQL s'interface avec des
langages hôtes

Remarque :

Nous allons se focaliser sur le LMD dans ce module

11
Langage de manipulation des données
 LMD (langage de manipulation des données) : permet de consulter /
modifier le contenu de la base de données

 Les instructions du LMD sont : INSERT INTO, SELECT, UPDATE et

DELETE

 Plan :
– Insertion => INSERT INTO
– Affichage / sélection => SELECT
– Mise à jour => UPDATE
– Suppression => DELETE

12
 SQL – Insert Into
 Le commande INSERT INTO permet d'ajouter un ou plusieurs Tuples à une
table
 La syntaxe de la primitive INSERT INTO en SQL est la suivante :
INSERT INTO nom_table [(attribut1 [, attribut2]...)]
VALUES (val1, val2,...) | <clause SELECT>;

 Il est possible de ne pas énumérer les noms de colonnes si l'ajout de

données concerne un enregistrement complet de la table

 Il est également possible d'insérer des données provenant d'une autre table
par l'intermédiaire d'une commande select

 Les éléments entre [ ] sont optionnels et le symbole "|" signifie "ou"

13
 SQL – Insert Into
 INSERT INTO ([Link])

14
 SQL – Insert Into
 Le commande INSERT INTO – Exemple :

 Quelques exemples de requêtes d’insertion de données dans une table :

INSERT INTO Voiture values(‘123ABC75’, ‘Renault’, ‘Rouge’, 7, 10 000);

INSERT INTO Voiture values(‘123ABC92’, ‘Peugeot’, ‘Noire’, 5, 2 500);

INSERT INTO Voiture (immatriculation, marque, couleur, puissance, prix)

values(‘123ATT77’, ‘Rover’, ‘Bleue’, 6, 13 000);

INSERT INTO Voiture values(‘698KLM45’, ‘Citroën’, ‘Verte’, 5, 5

000),(‘956BZD59’, ‘Peugeot’, ‘Jaune’, 5, 16 000),(‘156AMN86’, ‘Renault’, ‘Bleue’,
7, 5 250);

INSERT INTO Voiture (immatriculation, prix)

SELECT immat, prix FROM VoitureOccasion ;

15
 SQL - Select
 Le commande SELECT permet de sélectionner des tuples d’une ou
plusieurs tables

 La syntaxe de la primitive SELECT en SQL est la suivante :

SELECT * [ | DISTINCT | ALL] <attributs>

FROM <collection de données>
[WHERE <condition logique de niveau Tuple>]
[GROUP BY <attributs>
[HAVING <condition logique de niveau groupe>] ]
[ORDER BY <attributs>] [LIMIT <valeur>];

 Les éléments entre [ ] sont optionnels et le symbole "|" signifie « ou »

 Une collection de données peut être composée de tables et/ou des
requêtes « Select »

16
 SQL - Select
 SELECT ([Link])

17
 SQL - Select
 Paramètres :

 DISTINCT et ALL : permettent respectivement d'éliminer ou de conserver les

doublons après une projection mise en œuvre par SELECT

 FROM : désigne les tables concernées par la requête

 WHERE : spécifie les critères de sélection (optionnelle) qui peuvent être des
prédicats (conditions) de restriction ou de jointure

 GROUP BY : partitionne la relation en groupes (en sous ensemble)

– HAVING : lorsque les critères portent sur des fonctions de groupe

 ORDER BY : permet de trier les résultats obtenus selon un tri ascendant

(ASC) ou descendant (DESC)

Remarque : nous pouvons également insérer dans la clause WHERE, une

nouvelle instruction SELECT (requête imbriquée) ce qui permet de
travailler sur un ensemble de Tuples restreints par cette sélection

18
 SQL - Select
 Affichage - Exemples :

 Sélection simple sans conditions (tous les Tuples):

SELECT * FROM VOITURE;

 Sélection d’une colonne :

SELECT Marque FROM VOITURE;
- Cette requête risque d’afficher des doublons

 Sélection d’une colonne sans doublons :

SELECT DISTINCT Marque FROM VOITURE;

19
 SQL - Select
 Affichage – Condition (la clause WHERE):

 WHERE : spécifie une condition de sélection

– Une condition de sélection définit un critère qui, appliqué à un Tuple, est
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 entre eux en respectant un

ordre de priorité (NOT, AND et OR)

20
 SQL - Select
 Affichage – Prédicats :

 Prédicat (condition) : permet de comparer 2 expressions de valeurs :

– La première expression contenant des spécifications de colonne est
appelée terme
– La seconde expression contenant seulement des spécifications de
constants est appelée constante

 Il existe plusieurs types de prédicats :

– De comparaison : =, !=, , , ,  (valables pour INTEGER, FLOAT, CHAR,
VARCHAR et DATE)

– D’intervalle : permet de tester si la valeur d’un terme est comprise entre la

valeur de 2 constantes (BETWEEN)

– De comparaison de texte : permettant de tester si un terme de type chaîne

de caractères contient une ou plusieurs sous-chaînes (LIKE)

 Une utilisation de jokers est possible : le % remplace un nombre

quelconque de caractères (0 compris) tandis que le _ remplace un
caractère unique.

21
 SQL - Select
 Affichage – Prédicats - suite :

 Il existe plusieurs types de prédicats - suite :

– De test de nullité : qui permet de tester si un terme a une valeur convenue
nulle, signifiant que sa valeur est inconnue ou que le champ n'a pas été
renseigné (IS NULL)

– D'appartenance : qui permet de tester si la valeur d'un terme appartient à

une liste de valeurs constantes (IN)

22
 SQL - Select
 Affichage - Exemples :

 Quelques exemple de requêtes sur la table Voiture :

Voiture Immatriculation Marque Couleur Puissance Prix

123ABC75 Renault Rouge 7 10 000
123ABC92 Peugeot Noire 5 2 500
123ATT77 Rover Bleue 6 13 000
698KLM45 Citroën Verte 5 5 000
956BZD59 Peugeot Jaune 5 16 000
156AMN86 Renault Bleue 7 5 250

23
 SQL - Select
 Affichage - Exemples :

 Question : afficher les tuples de la table VOITURE dont la puissance

est inférieure ou égale à 6 chevaux ou dont le prix est supérieur ou
égal à 10 000 €

 Réponse :
SELECT * FROM VOITURE
WHERE Puissance <= 6 OR Prix >= 10 000;
 Affichage

Voiture Immatriculation Marque Couleur Puissance Prix

123ABC75 Renault Rouge 7 10 000
123ABC92 Peugeot Noire 5 2 500
123ATT77 Rover Bleue 6 13 000
698KLM45 Citroën Verte 5 5 000
956BZD59 Peugeot Jaune 5 16 000
156AMN86 Renault Bleue 7 5 250

24
 SQL - Select
 Affichage - Exemples :

 Question : afficher les tuples de la table VOITURE dont la puissance

n’est pas égale à 6 chevaux

 Réponse :
SELECT * FROM VOITURE
WHERE NOT (Puissance= 6);
 Affichage

Voiture Immatriculation Marque Couleur Puissance Prix

123ABC75 Renault Rouge 7 10 000
123ABC92 Peugeot Noire 5 2 500
123ATT77 Rover Bleue 6 13 000
698KLM45 Citroën Verte 5 5 000
956BZD59 Peugeot Jaune 5 16 000
156AMN86 Renault Bleue 7 5250

25
 SQL - Select
 Affichage - Exemples :

 Question : afficher les tuples de la table VOITURE dont le prix est

compris entre 2000 et 6000 euros

 Réponse : SELECT * FROM VOITURE

WHERE Prix BETWEEN 2 000 AND 6 000;

 Affichage

Voiture Immatriculation Marque Couleur Puissance Prix

123ABC75 Renault Rouge 7 10 000
123ABC92 Peugeot Noire 5 2 500
123ATT77 Rover Bleue 6 13 000
698KLM45 Citroën Verte 5 5 000
956BZD59 Peugeot Jaune 5 16 000
156AMN86 Renault Bleue 7 5 250

26
 SQL - Select
 Affichage - Exemples :

 Question : afficher les tuples de la table VOITURE dont la marque

commence par 'Ren' dont la couleur est noire, bleue ou jaune et dont le
prix est connu

SELECT * FROM VOITURE

 Réponse :
WHERE Marque LIKE 'Ren%' AND Couleur IN ('Noire',
'Bleue', 'Jaune') AND Prix IS NOT NULL;

 Affichage

Voiture Immatriculation Marque Couleur Puissance Prix

123ABC75 Renault Rouge 7 10 000
123ABC92 Peugeot Noire 5 2 500
123ATT77 Rover Bleue 6 13 000
698KLM45 Citroën Verte 5 5 000
956BZD59 Peugeot Jaune 5 16 000
156AMN86 Renault Bleue 7 5 250

27
 SQL – fonctions statistiques
 Affichage – Fonctions statistiques :

 Les fonctions statistiques sont appliquées à l'ensemble d'une colonne

(ou d'un groupe) et fournissent une valeur unique Les fonctions
statistiques

 Les fonctions statistiques peuvent être utilisées dans les clauses

SELECT, WHERE et HAVING

 Il existe plusieurs fonctions statistiques :

− MAX : maximum
− MIN : minimum
− SUM : somme
− AVG : moyenne
− COUNT : nombre de lignes

28
 SQL – fonctions statistiques
 Affichage – Fonctions statistiques :

 Les fonctions MAX et MIN renvoient respectivement le maximum et le

minimum d'un champ (utilisables 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 comptabilise 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.

29
 SQL – fonctions statistiques
 Affichage - Exemples :

 Questions :
− Quel est le prix maximal d’une voiture?
− Quel est le prix maximal d’une Renault?

 Réponses :
SELECT MAX(prix) FROM VOITURE;

SELECT MAX(prix) FROM VOITURE

WHERE Marque=‘Renault’;

 Affichages
Max(prix)
16000

Max(prix)
10000

30
 SQL – fonctions statistiques
 Affichage - Exemples :

 Questions :
− Quel est le nombre de Tuples de la table VOITURE?
− Quel est le nombre de marque de voitures ?

 Réponses :
SELECT COUNT(*) FROM VOITURE;

SELECT COUNT(DISTINCT marque) FROM VOITURE;

 Affichages : Count(*)
6
Count(distinct marque)
4
 Attention : sans distinct, les doublons seront considérés

31
 SQL – fonctions statistiques
 Affichage - Exemples :

 Questions :
− Quel est la somme des prix des voitures ayant une Puissance de 5
chevaux?
− Quel est le prix moyen des voitures de 5 chevaux?

 Réponses :
SELECT SUM(Prix) FROM VOITURE WHERE
Puissance=5;

SELECT AVG(Prix) FROM VOITURE WHERE

Puissance=5;
 Affichages :
SUM(Prix)
23500
AVG(Prix)
7833,33333

32
 SQL – Group By / Having
 Affichage :

 GROUP BY : permet de partitionner la relation résultat selon les

valeurs d'un ou de plusieurs attributs
– Les seuls noms de colonnes (en dehors des fonctions statistiques) qui
peuvent apparaître dans le SELECT sont celles qui figurent dans le GROUP
BY

 HAVING : définit les conditions que les groupes doivent respecter pour
être retenus, elle sélectionne les partitions désirées
– Elle ne peut comprendre que des 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)

33
 SQL – Group By / Having
 Fonctions statistiques - Exemples :

 Questions :
− Quel est la somme des prix des voitures selon la Puissance dans la
table VOITURE?

 Réponses :

SELECT SUM(Prix), Puissance FROM VOITURE

GROUP BY Puissance;

 Affichages :
SUM(Prix) Puissance
23 500 5
13000 6
15250 7

34
 SQL – Group By / Having
 Fonctions statistiques - Exemples :

 Questions :
− Quel est la somme des prix des voitures selon la Puissance dans la
table VOITURE (calculer le prix maximum sur ces partitions et limiter
l’affichage aux partitions contenant au moins 2 éléments)?

 Réponses :
SELECT SUM(Prix), Puissance FROM VOITURE
GROUP BY Puissance HAVING COUNT(*) > 1 ;

 Affichages :

SUM(Prix) Puissance
23 500 5
15250 7

35
 SQL – Tri
 Affichage :

 ORDER BY : permet d'ordonner le résultat sur un ou plusieurs

attributs, l'ordre pouvant être croissant (grâce au mot-clé ASC) ou
décroissant (grâce au mot-clé DESC), ASC étant l'ordre par défaut

 Dans une requête SQL, la clause ORDER BY se situe juste après une
clause WHERE ou après une clause GROUP BY

36
 SQL – Tri
 Fonctions statistiques - Exemples :

 Questions :
− Afficher l'ensemble des Tuples de la table VOITURE triés sur
l'attribut Puissance selon un ordre croissant

 Réponses :

SELECT * FROM VOITURE ORDER BY Puissance ASC;

 Affichages :

Immatriculation Marque Couleur Puissance Prix

123ABC92 Peugeot Noire 5 2 500
698KLM45 Citroën Verte 5 5 000
956BZD59 Peugeot Jaune 5 16 000
123ATT77 Rover Bleue 6 13 000
123ABC75 Renault Rouge 7 10 000
156AMN86 Renault Bleue 7 5 250

37
 SQL – Tri
 Fonctions statistiques - Exemples :

 Questions :
− Afficher l'ensemble des Tuples de la table VOITURE triés sur
l'attribut Puissance selon un ordre décroissant puis sur l'attribut
Couleur (ordre alphabétique) selon un ordre croissant

 Réponses :
SELECT * FROM VOITURE ORDER BY Puissance DESC, Couleur;

 Affichages :
Immatriculation Marque Couleur Puissance Prix
156AMN86 Renault Bleue 7 5 250
123ABC75 Renault Rouge 7 10 000
123ATT77 Rover Bleue 6 13 000
956BZD59 Peugeot Jaune 5 16 000
123ABC92 Peugeot Noire 5 2 500
698KLM45 Citroën Verte 5 5 000

38
 SQL – opérateurs relationnels
 Opérateurs relationnels :

 Les principaux opérateurs sont:

- union, intersection et différence

 Ces opérations sont applicables sur deux relations R1 et R2 qui

ont le même schéma de table (nombre équivalent d'attributs et
attributs identiques deux à deux)

39
 SQL – opérateurs relationnels
 Opérateurs relationnels - Exemples :

 Questions :
− Afficher l'ensemble des Tuples de la table VOITURE dont la
Puissance est égale à 5 ou à 7 chevaux

 Réponses :
SELECT * FROM VOITURE WHERE Puissance=‘5’
UNION SELECT * FROM VOITURE WHERE Puissance=‘7’;

SELECT * FROM VOITURE WHERE Puissance = 5 OR Puissance = 7 ;

 Affichages :
Immatriculation Marque Couleur Puissance Prix
123ABC75 Renault Rouge 7 10 000
123ABC92 Peugeot Noire 5 2 500
698KLM45 Citroën Verte 5 5 000
956BZD59 Peugeot Jaune 5 16 000
156AMN86 Renault Bleue 7 5 250

40
 SQL – Jointure
 La jointure :

 Les opérateurs de jointures donnent la possibilité d'appliquer des

requêtes sur plusieurs tables d'une base de données

 La syntaxe est la suivante :

SELECT nom_champ FROM nom_table [As tab1]
[INNER | {{LEFT | RIGHT | FULL } [OUTER]}] JOIN
nom_table2 [As tab2] ON Condition;

SELECT nom_champ FROM nom_table [As tab1]

[INNER | {{LEFT | RIGHT | FULL } [OUTER]}] JOIN
nom_table2 [As tab2] USING (colonnes);

 La condition de la commande ON permet de comparer les tables jointes par

l'intermédiaire de champs dont les valeurs sont identiques et comparables

 USING peut être utilisé si la condition de jointure concerne une colonne ayant 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

41
 SQL – Jointure
 La jointure : les types de jointures possibles :

 INNER JOIN (par défaut) : toutes les paires correspondantes des lignes
renvoyées et supprime les lignes n'ayant pas de correspondance entre les deux
tables
 NATURAL JOIN : s’effectue à la condition qu’il y ai des colonnes du même
nom et de même type dans les 2 tables

 FULL JOIN : une ligne de la table de gauche ou de droite, ne respectant pas la

condition de jointure, est comprise dans le jeu de résultats et que les colonnes de
sortie correspondant à l'autre table comportent des valeurs NULL

 LEFT JOIN : toutes les lignes de la table de gauche ne respectant pas la

condition de jointure sont incluses dans le jeu de résultats, et que les colonnes de
sortie de l'autre table ont des valeurs NULL en plus de toutes les lignes renvoyées
par la jointure interne

 RIGHT JOIN : toutes les lignes de la table de droite ne respectant pas la

condition de jointure sont comprises dans le jeu de résultats, et que les colonnes
de sortie correspondant à l'autre table ont des valeurs NULL en plus de toutes les
lignes renvoyées par la jointure interne

 CROSS JOIN (produit cartésien) : toutes les paires possibles sont renvoyées.
42
 SQL – Jointure
 Jointure - Exemples :

Voiture Immatriculation Marque Couleur Puissance Prix

123ABC75 Renault Rouge 7 10 000
123ABC92 Peugeot Noire 5 2 500
123ATT77 Rover Bleue 6 13 000
698KLM45 Citroën Verte 5 5 000
956BZD59 Peugeot Jaune 5 16 000
156AMN86 Renault Bleue 7 5 250

Marques Marque siège

Renault Paris
Peugeot Lille
BMW Berne

43
 SQL – Jointure
 Jointure - Exemples :

 Questions :
− Afficher l’immatriculation, la marque et la couleur d’une voiture qui
existe dans la table Marque (jointure interne)

 Réponses :
SELECT immatriculation, [Link], couleur FROM Voiture, Marque
WHERE [Link]=[Link];

SELECT immatriculation, [Link], couleur FROM Voiture

INNER JOIN Marques ON [Link]=[Link];

 Affichages :
Immatriculation Marque couleur
123ABC75 Renault Rouge
123ABC92 Peugeot Noire
956BZD59 Peugeot Jaune
156AMN86 Renault Bleue

44
 SQL – Jointure
 Jointure - Exemples :

 Questions :
− Afficher les informations d’une voiture (jointure externe gauche avec
la table Marque)

 Réponses :
SELECT * FROM Voiture LEFT JOIN Marques USING (marque);

 Affichages :

Immatriculation Marque Couleur Puissance Prix Siège

123ABC92 Peugeot Noire 5 2 500 Lille
698KLM45 Citroën Verte 5 5 000
956BZD59 Peugeot Jaune 5 16 000 Lille
123ATT77 Rover Bleue 6 13 000
123ABC75 Renault Rouge 7 10 000 Paris
156AMN86 Renault Bleue 7 5 250 Paris

45
 SQL – requête imbriquée
 Remarque :

 Il est possible d'imbriquer plusieurs requêtes. L'imbrication se fait dans

la clause WHERE ou bien dans la clause FROM

 Exemples :

 Questions :
− Donner la requête qui permet d'afficher tous les Tuples de la table
VOITURE dont le prix est supérieur à la moyenne des prix de tous
les Tuples de cette même table

 Réponses :
SELECT * FROM VOITURE
WHERE Prix > (SELECT AVG(Prix) FROM VOITURE) ;

46
 SQL – requête imbriquée
 Remarque :

 Une sous-interrogation (requête imbriquée) peut retourner un sous

ensemble de zéro à n valeurs

 En fonction des conditions que l'on veut exprimer au niveau des valeurs
renvoyées par la requête, on peut utiliser les prédicats IN, ANY, ALL ou
EXISTS :

 IN : l'opérateur de comparaison est l'égalité et l'opération logique entre les

valeurs est OU

 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 réalisée pour toutes les valeurs de
la liste

 EXISTS : si la sous-interrogation renvoie un résultat, la valeur retournée est

Vrai sinon la valeur Faux est retournée

47
 SQL – requête imbriquée
 Exemple:

 Questions :
− Produire les informations au sujet des CLIENTS qui ont passé au
moins une commande
− Commandes passées après la dernière Livraison (date ultérieure)

 Réponses :

SELECT * FROM CLIENTS WHERE EXISTS

(SELECT * FROM COMMANDE WHERE nClient =
[Link]) ;

SELECT * FROM COMMANDE WHERE dateCommande > ALL

(SELECT dateLivraison FROM LIVRAISON) ;

48
 SQL – Update
 Mise à jour (UPDATE):

 Il est possible de faire des mise à jour des données d’une BDD

 La syntaxe est la suivante :

UPDATE <nom_table>
SET <attribut1> = <expression1>, <attribut2> = <expression2> ...
[WHERE <condition>];

 Remarque : la clause WHERE est optionnelle, cependant sans le

WHERE, la mise à jour affectera TOUS les Tuples de la relation.

49
 SQL – Update
 Mise à jour (UPDATE):

50
 SQL – Update
 Update - Exemples :

 Questions :
− Donner la requête qui permet d'augmenter le Prix de vente de 10%
de tous les Tuples de la table VOITURE
− Donner la requête qui permet d'augmenter le Prix de vente de 10%
des voiture de marque Renault

 Réponses :

UPDATE VOITURE SET Prix = Prix x 1.10;

UPDATE VOITURE SET Prix = Prix x 1.10

WHERE Marque='Renault';

51
 SQL – Delete
 Suppression (DELETE):

 Il est possible de supprimer des Tuples ou une partie des Tuples d’une
table

 La syntaxe est la suivante :

DELETE FROM <nom_table> [WHERE <condition>];

 Remarque : la clause WHERE est optionnelle, cependant sans le

WHERE, la suppression affectera TOUS les Tuples de la relation.

52
 SQL – Delete
 DELETE ([Link]):

53
 SQL – Delete
 DELETE - Exemples :

 Questions :
− Donner la requête qui supprime tous les Tuples de la table VOITURE
− Donner la requête qui supprime les voitures de marque Renault

 Réponses :

DELETE FROM VOITURE;

DELETE FROM VOITURE

WHERE Marque='Renault';

54
 Résumé
 Manipulation des données
1. Création des données => INSERT INTO
2. Consultation des données => SELECT
3. Modification des données => UPDATE
4. Suppression des données => DELETE

 Manipulation des structures (TABLE, …)

1. Création => CREATE
2. Consultation => SHOW / DESCRIBE
3. Modification => ALTER
4. Suppression => DROP

55
 Recommandations
 Quelques recommandations :
1. Utilisez les bases de données relationnelles pour gérer des relations =>
ce n’ai pas pour faire de la gestion de fichiers.
2. Privilégiez une clé purement informatique à une clé purement
sémantique.
3. Sélectionnez que les données utiles => évitez ce type de requêtes :
SELECT * .
4. Utilisez du SQL dynamique pour des requêtes SQL simples =>
construisez votre requête comme une chaîne de caractères et
exécutez-la.
5. Utilisez des index, mais à bon escient.
6. Simplifier et réécrivez les questions => optimise les requêtes.
7. Utilisez le moins possible d’objets temporaires => requête imbriquée
et/ou des fonctions.
8. N’utilisez pas le SELECT MAX(…) + 1 pour générer une clé => plutôt
auto incrément ou séquence selon le SGBD utilisé.

56
ANNEXE

57
 Langage de définition des données
 LDD (langage de définition des données) : permet de modifier la
structure de la base de données
 Les instructions du LDD sont : CREATE, ALTER, DROP, RENAME,
AUDIT, NOAUDIT, ANALYZE, TRUNCATE

 Plan :
− Les objets : table, schéma, vues
− Création des objets
− Modification des objets
− Suppression

58
Langage de définition des données - objet
 Schéma: BDD
Schéma1 Schéma2 accès
accès tableB
tableA
tableA
utilisateur2
utilisateur1

 Un schéma est une collection d'objets, associée à un nom d'utilisateur de la

base de données

 Dans la pratique, tout utilisateur créant un objet dans une base de données,
engendre systématiquement un schéma

 L'accès aux objets d'un schéma par un utilisateur quelconque, s'effectue par
l'intermédiaire du nom du propriétaire associé à celui de l'objet :
nom_utilisateur.nom_objet

 Chaque objet dans une base de données créé par un utilisateur est ainsi
clairement identifié par le couple proprié[Link], évitant d'une part
d'éventuels noms doubles et d'autre part, des problèmes de sécurité

59
Langage de définition des données - objet
 Vues (VIEW): BDD
Schéma1 Schéma2
tableB
tableA utilisateur1
accès
vueB

 Une vue est une relation virtuelle au sens où ses instances n'existent
pas physiquement mais sont calculées dynamiquement à chaque
invocation de la vue
 Une vue est définie par une requête ‘SELECT’ qui utilise des relations
(tables) ou des vues existantes
 A la différence d'une table, une vue ne consomme pas d'espace de
stockage physique
 Les vues peuvent servir à recueillir des données régulièrement
consultées et mises à jour
 Les vues sont souvent employées pour des raisons de sécurité (il
possible de ne montrer qu'une partie des données d'une table)

60
Langage de définition des données - objet
 Création d’un objet(CREATE) :

 La Syntaxe pour la création d'une table en SQL est la suivante :

CREATE TABLE nom_table (

attribut1 type1 [DEFAULT valeur_par_défaut] [contrainte sur attribut1],
attribut2 type2 [DEFAULT valeur_par_défaut] [contrainte sur attribut2],
…
[CONSTRAINT contrainte1 sur relation],
….
[CONSTRAINT contrainteN sur relation]
);

 Les éléments entre [ ] sont optionnels

 Chaque table est définie par : un nom, des d'attributs et des contraintes
 Chaque attribut est défini par : un type, une valeur par défaut et des
contraintes sur attribut

61
Langage de définition des données - objet
 CREATE ([Link]) :

relational_properties

column_definition

62
Langage de définition des données - objet

 Création d’un objet : CREATE

 On veut créer la table personne contenant pour chaque personne un

ID, un prénom, un nom et l’âge

CREATE TABLE Personne(

ID CHAR(8),
prénom CHAR (20),
nom CHAR (30))

 La table Personne existe physiquement mais elle est encore vide de

données

63
Langage de définition des données - objet
 Création d’un objet : CREATE

 Les contraintes possibles sur les attributs:

PRIMARY KEY
NOT NULL
UNIQUE
REFERENCES nom_de_relation2 //pour les clés étrangères
CHECK (expression logique)

 S'il y a plusieurs contraintes sur un attribut, elles sont séparées par des
virgules

 Exemple :
//l'âge d'une personne ne peut pas être négatif et ne dépasse pas les 150 ans
CHECK (age>=0 and age<=150)

64
Langage de définition des données - contraintes
 Règles d’usage des contraintes :

 Un attribut ne peut pas être NULL et NOT NULL en même temps

 UNIQUE et PRIMARY KEY doivent être spécifiés qu’avec des attributs
NOT NULL
 CHECK ne peut contenir que des prédicats de comparaison d'attributs
avec des constantes

 Exemple :

age INTEGER CHECK (age>=0 and age<=150),

NuméroSS INTEGER NOT NULL UNIQUE,

65
Langage de définition des données - contraintes
 Création d’un objet :

 Les contraintes possibles sur les relations (plusieurs attributs):

UNIQUE (liste d'attributs)

PRIMARY KEY (liste d'attributs)
//clé étrangère composé de plusieurs attributs
FOREIGN KEY (liste d'attributs) REFERENCES nom_de_relation2 (liste d'attributs)

 S'il y a plusieurs contraintes, elles sont séparées par des virgules

 Exemple :

//le nom et le prénom doivent être unique dans la table

CONSTRAINT maContraineNom UNIQUE (nom, prénom),
CONSTRAINT maContrainePrim PRIMARY KEY (nom, prénom, dateNaissance)

66
Langage de définition des données - contraintes
 Règles d’usage des contraintes :

 FOREIGN KEY et REFERENCES doivent apparaître simultanément

 Les attributs de FOREIGN KEY doivent correspondre à des attributs
déclarés comme PRIMARY KEY ou UNIQUE dans une autre table
 Si les attributs de FOREIGN KEY correspondent à une clé primaire dans
une autre table, la clause REFERENCES ne spécifie que le nom de cette
dernière
 Si les attributs de FOREIGN KEY correspondent à une clé candidate dans
une autre relation, la clause REFERENCES doit mentionner le nom de
cette table ainsi que la liste des attributs composant la clé candidate

 Exemple : Table Affectation (affectation de voiture à des personnes)

CONSTRAINT C1 PRIMARY KEY (num, numPersonne),

CONSTRAINT C2 FOREIGN KEY (num) REFERENCES VOITURE,
CONSTRAINT C3 FOREIGN KEY (numPersonne) REFERENCES Personne(identifiant)

67
Langage de définition des données - contraintes
 Création des tables – Exemple 1 :

Immatriculation Marque couleur Prix Puissance

Voiture

 La requête SQL suivante crée la table VOITURE :

CREATE TABLE VOITURE(

Immatriculation VARCHAR(8),
Marque VARCHAR(20),
Couleur VARCHAR(15),
Prix INTEGER CONSTRAINT C1 CHECK (Prix>=0),
Puissance INTEGER,
CONSTRAINT C2 PRIMARY KEY (Immatriculation)
);

68
Langage de définition des données - contraintes
 Création des tables – Exemple 2 :

NumImmatriculation NumPersonne dateAchat

Achat

 La requête SQL suivante crée la table ACHAT :

CREATE TABLE ACHAT(

numImmatriculation VARCHAR(8),
numPersonne INTEGER,
dateAchat DATE,
CONSTRAINT C1 PRIMARY KEY (numImmatriculation, numPersonne),
CONSTRAINT C2 FOREIGN KEY (numImmatriculation) REFERENCES
VOITURE(Immatriculation),
CONSTRAINT C3 FOREIGN KEY (numPersonne) REFERENCES
Personne(NuméroSS)
);

69
Langage de définition des données - vues
 VUES:

 La création d'une vue se fait grâce à la clause CREATE VIEW suive :

− du nom que nous donnons à la vue,
− du nom des colonnes dont nous désirons afficher dans cette vue,
− d'une clause AS précédant la sélection

 La syntaxe de création d'une vue est la suivante :

CREATE VIEW nom_de_la_vue (colonneA, colonneB, colonneC, colonneD)

AS <clause SELECT>;

CREATE OR REPLACE VIEW nom_de_la_vue (colonneA, colonneB, colonneC,

colonneD) AS <clause SELECT>;

70
Langage de définition des données - vues
 VUES:

 Après sa création, une vue peut être utilisée de la même façon qu'une
table

 Des requêtes de sélection ou de manipulation de données peuvent être

appliquées à la vue

 Les vues sont calculées, à partir des tables, à chaque nouvelle

réutilisation

 La vue représente une sorte d'intermédiaire entre la BDD et l'utilisateur.

Cela a de nombreuses conséquences:
– une sélection des données à afficher (simplification des requêtes),
– une restriction d'accès à la table pour l'utilisateur, c'est-à-dire une
sécurité des données accrue,
– un regroupement d'informations au sein d'une entité (intégrité des
données),
– Transparence pour l’utilisateur il les utilise comme des tables

71
Langage de définition des données - vues
 VUES - Exemples:

 Exemple 1: la requête suivante nous permet de créer la vue

VOITURES_CHERES, cette vue regroupe tous les enregistrements de
la table VOITURE dont le prix est supérieur à 100 000 € :

CREATE VIEW VOITURES_CHERES

AS SELECT * FROM VOITURE
WHERE Prix > 100 000;

 Exemple 2 : la requête suivante nous permet de créer la vue

VOITURES_ PUISSANTES mais avec des noms d’attributs différents
de celui de la table VOITURE :

CREATE VIEW VOITURES_PUISSANTES (immat_voiture, marque_voiture)

AS SELECT Immatriculation, Marque FROM VOITURE

WHERE Puissance > 6;

72
Langage de définition des données - schéma
 Schéma:

 Il est possible de créer explicitement un schéma à l'aide de la

commande CREATE SCHEMA :
CREATE SCHEMA AUTHORIZATION nom_schéma{
instruction_CREATE_TABLE | instrustion_CREATE_VIEW |
instruction_GRANT
}

 La commande CREATE SCHEMA propose un moyen de créer des

tables, des vues et d'accorder des privilèges pour les objets à partir
d'une unique instruction

 GRANT : privilèges d'accès

 Remarques :
//Permet de connaître le schéma d'une table créée précédemment (sous Oracle) :
DESC nom_schéma
// sous MySQL :
SHOW nom_schéma

73
Langage de définition des données - MAJ
 Modification d’un objet table :

 La Syntaxe pour la mise à jour d'une table en SQL est la suivante :

ALTER TABLE nom_table opération;

 L’opération peut être :

// Ajout ou modification d’un attribut
ADD COLUMN attribute_name data_type
MODIFY COLUMN attribute_name data_type
// Suppression d’un attribut
DROP COLUMN attribute_name
//Modification d’une valeur par défaut
ALTER COLUMN attribute_name SET DEFAULT value

//Renommer un attribut
RENAME COLUMN old_attribute_name TO new_attribute_name
// Renommer une table
RENAME TO new_table_name
74
Langage de définition des données - MAJ
 Modification d’un objet table - suite :

 L’opération peut être :

// Suppression d’une contrainte

DROP CONSTRAINT constraint_name

// Ajout d’une contrainte

ADD CHECK (attribute_name condition);
ADD CONSTRAINT constraint_name UNIQUE (attribute_name);
ADD FOREIGN KEY (attribute_name) REFERENCES table_name (attribute_name);

 Modification d’un objet vue :

// Il est possible de renommer une vue
RENAME nom_vue TO nouveau_nom

75
Langage de définition des données - SUP
 Suppression d’un objet(DELETE) :
 La Syntaxe pour la suppression d'une table en SQL est la suivante :
DROP TABLE nom_table;
DROP TABLE nom_table CASCADE;

− Avant de supprimer une table, il faut supprimer toutes les références à cette
table dans les autres tables (ou utiliser le CASCADE CONSTRAINTS)
− Avant de supprimer une table, il faut avoir redéfini ou supprimé toutes les
vues impliquant cette table (nous reviendrons un peu plus loin sur la
définition des vues)
− La suppression d'une table implique de redéfinir ou supprimer toutes les
requêtes impliquant la table supprimée.

 La Syntaxe pour la suppression d'une vue en SQL est la suivante :

DROP VIEW nom_vue [CASCADE | RESTRICT];

 La Syntaxe pour la suppression d'un schéma en SQL est la suivante :

DROP SHEMA nom_shema [CASCADE | RESTRICT];

76
 Langage de contrôle des données
 LCD (langage de contrôle des données) : permet de gérer les privilèges,
c'est-à-dire les utilisateurs et les actions qu'ils peuvent entreprendre

 Les instructions du LCD sont : GRANT, REVOKE

 Plan :
– GRANT : permet d'attribuer un privilège à différents utilisateurs sur
différents objets
– REVOKE : permet de révoquer, c'est-à-dire "retirer" un privilège

77
 Langage de contrôle des données

 LCD – GRANT:

 La syntaxe est la suivante :

GRANT liste_des_privilèges | ALL PRIVILEGES
ON objets
TO nom_autorisé | PUBLIC
[WITH GRANT OPTION];

Liste des privilèges

SELECT |
objets DELETE |
[TABLE] nomTable | INSERT [listeColonnes]|
DOMAIN nomDomaine | UPDATE [listeColonnes]|
CHARACTER SET nomCharacterSet REFERENCES listeColonnes|
COLLATION nomCollation USAGE
TRANSLATION nomTranslation

78
 Langage de contrôle des données
 GRANT - Exemples :

− Requête qui donne l'ensemble des droits d'accès aux utilisateurs user2
et user3 uniquement sur les colonnes Immatriculation et Prix de la table
VOITURE
GRANT ALL PRIVILEGES (Immatriculation, Prix) ON VOITURE TO user2, user3;

− Requête qui donne uniquement le droit d’insertion de Tuples aux

utilisateurs sur la table VOITURE

GRANT INSERT ON PERSONNE TO PUBLIC;

− La requête suivante permet de donner à user2 tous les droits sur la table
VOITURE, il lui accorde également l'autorisation de transférer ces
privilèges
GRANT ALL PRIVILEGES ON VOITURE TO user2 WITH GRANT OPTION;

79
 Langage de contrôle des données
 LCD – REVOKE:

 La syntaxe est la suivante :

REVOKE ALL PRIVILEGES | liste_des_privilèges | GRANT OPTION FOR listepriv

ON objets
FROM nom_utilisateur | PUBLIC
[ RESTRICT | CASCADE ];

CASCADE
Entraînera la révocation des
RESTRICT
droits cédés à la manière d'un
Si le ou les utilisateurs visés ont cédés château de carte
leurs droits à d'autres, un message d'erreur
apparaîtra et le SGBDR refusera de
révoquer le ou les droits

80
Langage de contrôle des transactions

 LCT (langage de contrôle des transactions) : permet de gérer les

transactions, c'est-à-dire rendre atomique divers ordres enchaînés en séquence

 Les instructions du LCT sont : COMMIT, ROLLBACK

 Plan :
– Propriétés ACID
– COMMIT : permet à l'utilisateur de fixer le moment où les
modifications en cours affecteront la base de données
– ROLLBACK : permet à l'utilisateur de ne pas valider les dernières
modifications en cours dans la base de données

81
Langage de contrôle des transactions

 LCT (langage de contrôle des transactions) :

Ouverture d'une Fermeture de la
connexion SQL connexion SQL

Début d'une Début d'une Début d'une

COMMIT COMMIT COMMIT
transaction SQL transaction SQL transaction SQL

Transaction Transaction Transaction

SQL SQL SQL

Session
SQL

− Début de transaction (implicite en SQL)

− Fin de transaction
− Confirmer (COMMIT WORK en SQL)
− Annuler (ROLLBACK WORK en SQL)

82
Langage de contrôle des transactions
 ACID :

 Atomicité : une transaction s'effectue ou pas (tout ou rien), une transaction ne

peut être partiellement effectuée

 Cohérence : le résultat ou les changements induits par une transaction doivent

impérativement préserver la cohérence de la base de données

 Isolation : les transactions sont isolées les unes des autres. Les effets de la
transaction ne sont pas perceptibles tant que celle-ci n’est pas terminée

 Durabilité : une fois validée, une transaction doit perdurer, c'est-à-dire que les
données sont persistantes même s'il s'ensuit une défaillance dans le système

83
Langage de contrôle des transactions
 COMMIT :

 La commande COMMIT permet à l'utilisateur de terminer et de valider

(écrit) la transaction

 Une transaction (ou une partie de transaction) qui n'a pas encore été
enregistrée définitivement n'est visible que pour l'utilisateur qui
l'introduit

 Avant l'exécution de l'instruction COMMIT, il est possible de restaurer la

base par ROLLBACK, c'est-à-dire d'éliminer les modifications récentes

 Après l'enregistrement définitif d'une transaction par COMMIT, il n'est

plus possible de restaurer l'état antérieur par ROLLBACK

 S'il apparaît après coup qu'une transaction doit être modifiée ou

corrigée, nous ne pourrons pas effectuer cette modification qu'au
moyen d'une autre instruction SQL comme UPDATE ou DELETE.

84
Langage de contrôle des transactions
 ROLLBACK :

 La commande ROLLBACK permet à l'utilisateur de terminer et

d’annuler la transaction

 Dans le cas d'une défaillance du système, l'intégrité de la base peut

être préservée par une option ROLLBACK automatique qui élimine les
transactions inachevées et empêche donc qu'elles soient introduites
dans la base

85
 Division
 Exemple :

 Les étudiants ont des notes dans plusieurs modules

 Question :
Quels sont les étudiants qui sont notés sur l’ensemble des modules ?

Et1 BDD

Et2 Java

Et3 STAT

Et4 ELEC

Réponse : Et2 et Et3 86

 Division
 Exemple :

 Il suffit de trouver les étudiants qui sont reliés à TOUS les modules.

 Table notes :
 IdEtudiant, idModule, note

 Table modules :
 IdModule, nom module

 Solution :
Division de notes par modules

87
 Division
 Implémentation 1 :

 Compter le nombre d’occurrence de notes des étudiants et de le faire

coïncider avec le nombre de modules.

SELECT IdEtudiant FROM notes

GROUP BY idEtudiant
HAVING COUNT(*) = (SELECT COUNT(*) FROM modules)

Cette solution est correcte si :

1. Il n’y a pas de redondance de données dans la table diviseur.
• Solution : DISTINCT
2. Il n’y a pas de notes, non recensé dans la table modules

88
 Division
 Implémentation 2 :

 L’utilisation de la double négation.

SELECT DISTINCT IdEtudiant FROM notes AS N1

WHERE NOT EXISTS
(SELECT * FROM modules M1
WHERE NOT EXISTS
(SELECT * FROM notes AS N2 WHERE [Link] = [Link]
AND [Link] = [Link] )

Cette requête recherche les étudiants pour qui il n’existe pas de module
qu’ils ne sont pas notés

89
 Division
 Implémentation 3 :

 Différence entre tables.

SELECT DISTINCT IdEtudiant FROM notes AS N1

WHERE
(SELECT idModule FROM modules M1
EXCEPT
(SELECT idModule FROM notes AS N2 WHERE [Link] =
[Link] AND) IS NULL

90