5.
Le langage SQL
elazami © 2022-2023 1
Introduction
SQL : Structured Query Language
Inventé chez IBM (centre de recherche d'Almaden en Californie),
en 1974 par Astrahan & Chamberlin dans le cadre de System R
Le langage SQL est normalisé
SQL2: adopté (SQL 92)
SQL3: adopté (SQL 99)
Standard d'accès aux bases de données relationnelles
elazami © 2022-2023 2
SQL : Trois langages en un
SQL = Langage de définition de données (LDD)
CREATE TABLE
ALTER TABLE
DROP TABLE
SQL = Langage de manipulation de données (LMD)
INSERT INTO
UPDATE
DELETE FROM
SQL = Langage de requêtes (LMD)
SELECT … FROM … WHERE ...
Sélection
Projection
Jointure
Les agrégats
elazami © 2022-2023 3
Terminologie
Relation Table
Tuple Ligne
Attribut Colonne
elazami © 2022-2023 4
SQL (LDD)
Un langage de définition de données
elazami © 2022-2023 5
Types de données
Une base de données contient des tables
Une table est organisée en colonnes
Une colonne stocke des données
Les données sont séparées en plusieurs types !
elazami © 2022-2023 6
Type des colonnes (en MySQL)
Numériques
NUMERIC : idem DECIMAL //valeur exacte
DECIMAL. Possibilité DECIMAL(M,D) M chiffre au total //valeur exacte
INTEGER
TINYINT 1 octet (de -128 à 127)
SMALLINT 2 octets (de -32768 à 32767)
MEDIUMINT 3 octets (de -8388608 à 8388607)
INT 4 octets (de -2147483648 à 2147483647)
BIGINT 8 octets (de -9223372036854775808 à 9223372036854775807)
Possibilité de donner la taille de l’affichage : INT(6) => 674 s’affiche 000674
Possibilité de spécifier UNSIGNED
INT UNSIGNED => de 0 à 4294967296
FLOAT : 4 octets par défaut. Possibilité d’écrire FLOAT(P) //valeur approchée
REAL : 8 octets (4 octets dans d’autres SGBD) //valeur approchée
DOUBLE : 8 octets //valeur approchée
elazami © 2022-2023 7
Type des colonnes (en MySQL)
Date et Heure
DATETIME
AAAA-MM-JJ HH:MM:SS
de 1000-01-01 [Link] à '9999-12-31 [Link]
DATE
AAAA-MM-JJ
de 1000-01-01 à 9999-12-31
TIMESTAMP
Date sans séparateur AAAAMMJJHHMMSS
TIME
HH:MM:SS (ou HHH:MM:SS)
de -[Link] à [Link]
YEAR
YYYY
de 1901 à 2155
elazami © 2022-2023 8
Type des colonnes (en MySQL)
Chaînes CHAR(4) VARCHAR(4)
CHAR(n) 1 ≤ n ≤ 255
VARCHAR(n) 1 ≤ n ≤ 255 Valeur Stockée Taille Stockée Taille
Exemple : '' ' ' 4 octets '' 1 octets
'ab' 'ab ' 4 octets 'ab' 3 octets
'abcd' 'abcd' 4 octets 'abcd' 5 octets
'abcdef' 'abcd' 4 octets 'abcd' 5 octets
elazami © 2022-2023 9
Type des colonnes (en MySQL)
Chaînes
TINYBLOB Taille < 2^8 caractères
BLOB Taille < 2^8 caractères
MEDIUMBLOB Taille < 2^24 caractères
LONGBLOB Taille < 2^32 caractères
TINYTEXT Taille < 2^8 caractères
TEXT Taille < 2^8 caractères
MEDIUMTEXT Taille < 2^24 caractères
LONGTEXT Taille < 2^32 caractères
Les tris faits sur les BLOB tiennent compte de la
casse, contrairement aux tris faits sur les TEXT.
elazami © 2022-2023 10
Type des colonnes (en MySQL)
ENUM //valeur décimal
Enumération
ENUM("un", "deux", "trois")
Valeurs possibles : "" , "un", "deux", "trois"
Au plus 65535 éléments
SET //valeur binaire
Ensemble
SET("un", "deux")
Valeurs possibles : "" , "un", "deux", "un,deux"
Au plus 64 éléments
elazami © 2022-2023 11
Type des colonnes (en MySQL)
Dans quelles situations faut-il utiliser ENUM ou SET ?
JAMAIS !!
il faut toujours éviter autant que possible les
fonctionnalités propres à un seul SGBD.
elazami © 2022-2023 12
Un langage de définition de données
Commandes pour Créer et supprimer une base de données:
CREATE DATABASE nom_base: créer une base de données,
CREATE DATABASE bibliotheque CHARACTER SET 'utf8' :
créer une base de données et encoder les tables en UTF-8
-------------------
DROP DATABASE bibliotheque : supprimer la base de données,
DROP DATABASE IF EXISTS bibliotheque ;
-------------------
Utilisation d'une base de données
USE bibliotheque ;
elazami © 2022-2023 13
Un langage de définition de données
Commandes pour créer, modifier et supprimer les
éléments du schéma:
CREATE TABLE : créer une table (une relation),
CREATE VIEW : créer une vue particulière sur les
données à partir d’un SELECT,
DROP {TABLE ¦ VIEW } : supprimer une table ou une
vue,
ALTER {TABLE ¦ VIEW } : modifier une table ou une
vue.
elazami © 2022-2023 14
CREATE TABLE
Commande créant une table en donnant son nom, ses attributs et
ses contraintes:
CREATE TABLE [IF NOT EXISTS] nom_table (
colonne1 description_colonne1,
[colonne2 description_colonne2,
colonne3 description_colonne3,
...,]
[PRIMARY KEY (colonne_clé_primaire)]
)
[ENGINE=moteur]; elazami © 2022-2023 15
Les moteurs de tables
• Les moteurs de tables sont une spécificité de MySQL. Ce sont
des moteurs de stockage. Cela permet de gérer différemment les
tables selon l'utilité qu'on en a.
• Les deux moteurs les plus connus sont MyISAM et InnoDB.
• MyISAM : C'est le moteur par défaut. Les commandes sont
particulièrement rapides sur les tables utilisant ce moteur.
Cependant, il ne gère pas certaines fonctionnalités importantes
comme les clés étrangères.
• InnoDB : Plus lent et plus gourmand en ressources que
MyISAM, ce moteur gère les clés étrangères
elazami © 2022-2023 16
CREATE TABLE
Exemples:
CREATE TABLE Emprunteur(
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
nom VARCHAR(20) NOT NULL,
prenom VARCHAR(15) NOT NULL,
annee_insc YEAR DEFAULT 2021,
PRIMARY KEY (id)
)
ENGINE=INNODB;
elazami © 2022-2023 17
CREATE TABLE
Exemples: Autre possibilité
CREATE TABLE Emprunteur(
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
nom VARCHAR(20) NOT NULL,
prenom VARCHAR(15) NOT NULL,
annee_insc YEAR DEFAULT 2021,
)
ENGINE=INNODB;
elazami © 2022-2023 18
Vérifications
Deux commandes pour vérifier la création des tables :
• SHOW TABLES;
– liste les tables de la base de données
• DESCRIBE Emprunteur;
– liste les colonnes de la table avec leurs caractéristiques
elazami © 2022-2023 19
DROP TABLE
DROP TABLE : Supprimer une table
supprime la table et tout son contenu
DROP TABLE nom_table [CASCADE CONSTRAINTS];
CASCADE CONSTRAINTS
Supprime toutes les contraintes référençant une clé primaire
(primary key) ou une clé unique (UNIQUE) de cette table
Si on cherche à détruire une table dont certains attributs sont
référencés sans spécifier CASCADE CONSTRAINT, on a un
message d'erreur.
elazami © 2022-2023 20
ALTER TABLE
Modifier la définition d’une table:
Changer le nom de la table
mot clé : RENAME
Ajouter une colonne ou une contrainte
mot clé : ADD
Modifier une colonne ou une contrainte
mot clé : MODIFY/CHANGE
Supprimer une colonne ou une contrainte
mot clé : DROP
renommer une colonne ou une contrainte
mot clé : RENAME elazami © 2022-2023 21
ALTER TABLE
Syntaxe :
ALTER TABLE nom-table
{ RENAME TO nouveau-nom-table
| ADD (( nom-col type-col [DEFAULT valeur] [contrainte-col])*)
| MODIFY (nom-col [type-col] [DEFAULT valeur] [contrainte-col])*
| DROP COLUMN nom-col [CASCADE CONSTRAINTS]
| RENAME COLUMN old-name TO new-name
};
elazami © 2022-2023 22
Ajout et suppression d'une colonne
ALTER TABLE nom_table
ADD [COLUMN] nom_colonne description_colonne;
Exemple :
ALTER TABLE Emprunteur
ADD COLUMN date_emprunt DATE NOT NULL ;
elazami © 2022-2023 23
Ajout et suppression d'une colonne
ALTER TABLE nom_table
DROP [COLUMN] nom_colonne;
Exemple :
ALTER TABLE Emprunteur
DROP COLUMN date_emprunt ;
elazami © 2022-2023 24
Modification d'une colonne
ALTER TABLE nom_table
CHANGE ancien_nom nouveau_nom description_colonne;
Exemple :
ALTER TABLE Emprunteur
CHANGE nom nom_famille VARCHAR(10) NOT NULL ;
elazami © 2022-2023 25
Changement du type de données
ALTER TABLE nom_table
CHANGE ancien_nom nouveau_nom description_colonne;
Ou
ALTER TABLE nom_table
MODIFY nom_colonne description_colonne;
elazami © 2022-2023 26
Des exemples pour illustrer :
CREATE TABLE Emprunteur(
ALTER TABLE Emprunteur
id SMALLINT UNSIGNED
CHANGE nom nom_famille VARCHAR(10) NOT NULL ; AUTO_INCREMENT PRIMARY KEY,
nom VARCHAR(20) NOT NULL,
–> Changement du type + changement du nom
prenom VARCHAR(15) NOT NULL,
annee_insc YEAR DEFAULT 2021,
ALTER TABLE Emprunteur )
ENGINE=INNODB;
CHANGE id id BIGINT NOT NULL ;
–> Changement du type sans renommer
ALTER TABLE Emprunteur
MODIFY id BIGINT NOT NULL AUTO_INCREMENT;
–> Ajout de l'auto-incrémentation
ALTER TABLE Emprunteur
MODIFY nom VARCHAR(30) NOT NULL DEFAULT ‘Anonyme';
–> Changement de la description
elazami © 2022-2023 27
Renommer une table
... RENAME TO nouveau-nom-table
Exemple :
ALTER TABLE Emprunteur RENAME TO Emprunteurs ;
elazami © 2022-2023 28
Les clé étrangères
CREATE TABLE [IF NOT EXISTS] Nom_table (
colonne1 description_colonne1,
[colonne2 description_colonne2,
colonne3 description_colonne3,
...,]
[ [CONSTRAINT [symbole_contrainte]] FOREIGN KEY
(colonne(s)_clé_étrangère) REFERENCES table_référence
(colonne(s)_référence)]
)
[ENGINE=moteur];
elazami © 2022-2023 29
Exemple
• On imagine les tables Client et Commande,
• pour créer la table Commande avec une clé étrangère ayant pour référence la colonne
numero de la table Client, on utilisera :
CREATE TABLE Commande (
numero INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
client INT UNSIGNED NOT NULL,
produit VARCHAR(40),
quantite SMALLINT DEFAULT 1,
CONSTRAINT fk_client_numero -- On donne un nom à notre clé
FOREIGN KEY (client) -- Colonne sur laquelle on crée la clé
REFERENCES Client(numero) -- Colonne de référence
)
ENGINE=InnoDB; -- MyISAM interdit, je le rappelle encore une fois !
elazami © 2022-2023 30
Après création de la table
ALTER TABLE Commande
ADD CONSTRAINT fk_client_numero FOREIGN KEY
(client) REFERENCES Client(numero);
Suppression d'une clé étrangère
ALTER TABLE nom_table
DROP FOREIGN KEY symbole_contrainte;
elazami © 2022-2023 31
Petit TP
Créer la base de données et les différentes tables de ce schéma relationnel:
Personnes(PersonneID, Nom, Age, Adresse);
Commandes(CommandeID,NumCommande,PersonneID);
Commandes Personnes
CommandeID PersonneID
NumCommande Nom
PersonneID Age
Adresse
elazami © 2022-2023 32
Petit TP
Solution:
CREATE DATABASE gestion;
USE gestion;
CREATE TABLE Personnes (
PersonneID int AUTO_INCREMENT PRIMARY KEY,
Nom VARCHAR(20) NOT NULL,
Age int,
Adresse VARCHAR(100)
);
CREATE TABLE Commandes (
CommandeID int AUTO_INCREMENT PRIMARY KEY,
NumCommande int NOT NULL,
PersonneID int,
FOREIGN KEY (PersonneID) REFERENCES Personnes(PersonneID)
);
elazami © 2022-2023 33
SQL (LMD)
Un langage de manipulation de
données
elazami © 2022-2023 34
Manipulation des données
INSERT INTO : ajouter un tuple dans une
table ou une vue
UPDATE : changer les tuples d’une table ou
d'une vue
DELETE FROM : éliminer les tuples d’une
table ou d'une vue
elazami © 2022-2023 35
INSERT INTO
Syntaxe :
INSERT INTO
{nom_table ¦ nom_vue}
[ (nom_col (, nom_col)*) ]
{ VALUES (valeur (, valeur)*) ¦ sous-requête
};
elazami © 2022-2023 36
Insertion sans préciser les colonnes
Nous travaillons toujours sur la table
Emprunteur composée de 4 colonnes : id, nom,
prenom, annee_insc
INSERT INTO Emprunteur
VALUES (1, 'Buard', 'Jeremy', '2018');
INSERT INTO Emprunteur
VALUES (NULL, 'Zuckerberg', 'Mark', NULL);
–> Insert un tuple avec un id=2 et une année = NULL
elazami © 2022-2023 37
Insertion en précisant les colonnes
INSERT INTO Emprunteur (nom, prenom,
annee_insc)
VALUES ('Chan', 'Priscilla', '2018');
INSERT INTO Emprunteur (nom, prenom)
VALUES ('Gates', 'Bill');
–> Insert un tuple avec une année = 2018
elazami © 2022-2023 38
Insertion multiple
INSERT INTO Emprunteur (nom, prenom,
annee_insc)
VALUES ('Jobes', 'Steve', '2010'),
('Moskovitz', 'Dustin', '2011'),
('Musk', 'Elon', '2013');
elazami © 2022-2023 39
UPDATE
Syntaxe :
UPDATE {nom_table ¦ nom_vue}
SET { (nom_col)* = (sous-requête)
¦ nom_col = { valeur ¦ (sous-requête)} }*
WHERE condition;
Exemples :
UPDATE Emprunteur
SET annee_insc = '2019'
WHERE nom = ‘Musk’
UPDATE Emprunteur
SET annee_insc = annee_insc+2
WHERE id < 3
elazami © 2022-2023 40
DELETE FROM
Exemple :
DELETE FROM Emprunteur
WHERE annee_insc < 2000
Syntaxe :
DELETE FROM {nom_table ¦ nom_vue}
WHERE condition;
elazami © 2022-2023 41
SQL
Un langage de requêtes
elazami © 2022-2023 42
Structure générale d'une requête
Structure d'une requête formée de trois clauses:
SELECT <liste_attributs>
FROM <liste_tables>
WHERE <condition>
SELECT définit le format du résultat cherché
FROM définit à partir de quelles tables le résultat
est calculé
WHERE définit les prédicats de sélection du résultat
elazami © 2022-2023 43
Exemple de requête
SELECT * FROM Emprunteur
Afficher tous les attributs de tous les tuples
dans la table “Emprunteur”
elazami © 2022-2023 44
Opérateurs de comparaison
= égal
WHERE id = 2
<> différent
WHERE nom <> ‘Ahmad’
> plus grand que
WHERE annee_insc > 2010
>= plus grand ou égal
WHERE annee_insc >= 2018
< plus petit que
WHERE id < 3
<= plus petit ou égal
WHERE id <= 2
elazami © 2022-2023 45
Opérateurs logiques
AND
WHERE annee_insc < 2010 AND id<5
OR
WHERE annee_insc < 2010 OR id<5
Négation de la condition : NOT
SELECT *
FROM Emprunteur
WHERE nom = 'Badr'
AND NOT annee_insc = '2019' ;
elazami © 2022-2023 46
Expressions logiques
Combinaisons:
WHERE
( ensoleillement > 80 AND pluviosité < 200 )
OR température > 30
WHERE
ensoleillement > 80
AND ( pluviosité < 200 OR température > 30 )
elazami © 2022-2023 47
Appartenance à un ensemble : IN
WHERE monnaie = ‘Dollar’
OR monnaie = ‘Dirham’
OR monnaie = ‘Euro’
Équivalent à:
WHERE monnaie IN (‘Dollar’, ‘Dirham’, ‘Euro')
NOT IN: non appartenance à un ensemble
elazami © 2022-2023 48
Comparaison à un ensemble : ALL
SELECT * FROM Employe
WHERE salaire >= 1400
AND salaire >= 3000 ;
Équivalent à:
SELECT * FROM Employe
WHERE salaire >= ALL (1400, 3000);
elazami © 2022-2023 49
Valeur dans un intervalle : BETWEEN
WHERE population >= 50 AND population <= 60
Équivalent à:
WHERE population BETWEEN 50 AND 60
NOT BETWEEN
elazami © 2022-2023 50
Conditions partielles (joker)
% : un ou plusieurs caractères
WHERE nom LIKE ‘%med’
WHERE prenom LIKE ‘%rem%’
_ : exactement un caractère
WHERE nom LIKE ‘B_dr’
NOT LIKE
elazami © 2022-2023 51
Valeurs calculées
SELECT nom, population, surface, natalité
FROM Pays
WHERE (population * 1000 / surface) < 50
AND (population * natalité / surface) > 0
SELECT nom, (population * 1000 / surface )
FROM Pays
elazami © 2022-2023 52
Les jointures
Principe :
– Joindre plusieurs tables
– On utilise les informations communes des tables
elazami © 2022-2023 53
Les jointures
Prenons pour exemple un ouvrage de V. Hugo
Si l'on souhaite des informations sur la cote d'un
exemplaire il faudrait le faire en 2 temps:
– 1) je récupère l'id de l'ouvrage :
SELECT id FROM ouvrage where auteur LIKE 'V. Hugo'
– 2) Je récupère la ou les cote avec l'id récupéré
SELECT cote FROM exemplaire WHERE ouvrageId = id_récupéré
elazami © 2022-2023 54
Les jointures
On peux faire tout ça (et plus encore) en une seule requête
C'est là que les jointures entrent en jeu:
SELECT [Link]
FROM exemplaire
INNER JOIN ouvrage
ON [Link] = [Link]
WHERE [Link] LIKE 'V. Hugo' ;
elazami © 2022-2023 55
Les jointures
elazami © 2022-2023 56
Les jointures
Ville Email
INNER JOIN
LEFT JOIN
RIGHT JOIN
elazami © 2022-2023 57
SQL
Requêtes avec blocs emboîtés
elazami © 2022-2023 58
BD exemple
Produit(np,nomp,couleur,poids,prix) les produits
Usine(nu,nomu,ville,pays) les usines
Fournisseur(nf,nomf,type,ville,pays) les fournisseurs
Livraison(np,nu,nf,quantité) les livraisons
np référence [Link]
nu référence [Link]
nf référence [Link]
elazami © 2022-2023 59
Jointure par blocs emboîtés
Requête: Nom et couleur des produits livrés par le fournisseur 1
Solution 1 : la jointure déclarative
SELECT nomp, couleur FROM Produit,Livraison
WHERE ([Link] = [Link]) AND nf = 1 ;
Solution 2 : la jointure procédurale (emboîtement)
Nom et couleur des produits livrés par le fournisseur 1
SELECT nomp, couleur FROM Produit
WHERE np IN
(SELECT np FROM Livraison WHERE nf = 1) ;
Numéros de produits livrés par le fournisseur 1
elazami © 2022-2023 60
Jointure par blocs emboîtés
SELECT nomp, couleur FROM Produit
WHERE np IN
( SELECT np FROM Livraison
WHERE nf = 1) ;
IN compare chaque valeur de np avec l'ensemble (ou multi-ensemble)
de valeurs retournés par la sous-requête
IN peut aussi comparer un tuple de valeurs:
SELECT nu FROM Usine
WHERE (ville, pays)
IN (SELECT ville, pays FROM Fournisseur);
elazami © 2022-2023 61
Composition de conditions
Requête: Nom des fournisseurs qui approvisionnent une
usine de Londres ou de Paris en un produit rouge
SELECT nomf
FROM Livraison, Produit, Fournisseur, Usine
WHERE
couleur = 'rouge'
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND ([Link] = 'Londres’ OR [Link] = 'Paris');
elazami © 2022-2023 62
Composition de conditions
Requête: Nom des fournisseurs qui approvisionnent une usine de Londres ou
de Paris en un produit rouge
SELECT nomf FROM Fournisseur
WHERE nf IN
(SELECT nf FROM Livraison
WHERE np IN (SELECT np FROM Produit
WHERE couleur = 'rouge')
AND nu IN
(SELECT nu FROM Usine
WHERE ville = 'Londres' OR ville = 'Paris')
);
elazami © 2022-2023 63
Quantificateur ALL
Requête: Numéros des fournisseurs qui ne fournissent que des
produits rouges
SELECT nf FROM Fournisseur
WHERE 'rouge' = ALL
(SELECT couleur FROM Produit
WHERE np IN
(SELECT np FROM Livraison
WHERE [Link] = [Link] ) ) ;
La requête imbriquée est ré-évaluée pour chaque tuple de la requête (ici
pour chaque nf)
ALL: tous les éléments de l'ensemble doivent vérifier la condition
elazami © 2022-2023 64
Condition sur des ensembles : EXISTS
Test si l’ensemble n’est pas vide (E )
Exemple : Noms des fournisseurs qui fournissent au moins un produit
rouge
SELECT nomf
FROM Fournisseur
WHERE EXISTS ce fournisseur
( SELECT *
FROM Livraison, Produit
WHERE [Link] = [Link]
AND [Link] = [Link]
Le produit fourni
AND [Link] = 'rouge' ); est rouge
elazami © 2022-2023 65
Blocs emboîtés - récapitulatif
SELECT …
FROM …
WHERE …
attr IN requête
attr NOT IN requête
attr opérateur ALL requête
EXISTS requête
NOT EXISTS requête
elazami © 2022-2023 66
SQL
Traitement des résultat
elazami © 2022-2023 67
Fonctions sur les colonnes
Attributs calculés
Exemple : SELECT nom, population*1000/surface FROM Pays
Opérateurs sur attributs numériques
SUM: somme des valeurs des tuples sélectionnés
AVG: moyenne
Opérateurs
Opérateurs sur tous types d’attributs d'agrégation
MIN: minimum
MAX: maximum
COUNT: nombre de tuples sélectionnés
elazami © 2022-2023 68
Opérateurs d'agrégation
pays
Nom Capitale Population Surface Continent
Irlande Dublin 5 70 Europe
Autriche Vienne 10 83 Europe
UK Londres 50 244 Europe
Suisse Berne 7 41 Europe
USA Washington 350 441 Amérique
SELECT MIN(population), MAX(population), AVG(population),
SUM(surface), COUNT(*)
FROM Pays WHERE continent = ‘Europe’
Donne le résultat :
MIN(population) MAX(population) AVG(population) SUM(surface) COUNT(*)
5 50 18 438 4
elazami © 2022-2023 69
DISTINCT
pays
Nom Capitale Population Surface Continent
Irlande Dublin 5 70 Europe
Autriche Vienne 10 83 Europe
UK Londres 50 244 Europe
Suisse Berne 7 41 Europe
USA Washington 350 441 Amérique
Suppression des doubles
SELECT DISTINCT continent
FROM Pays
Donne le résultat : Continent
Europe
Amérique
elazami © 2022-2023 70
ORDER BY
Tri des tuples du résultat
SELECT continent, nom, population
FROM Pays
WHERE surface > 60
ORDER BY continent, nom ASC
2 possibilités : ASC / DESC
Continent Nom Population
Amérique USA 350
Europe Autriche 10
Europe Irlande 5
Europe UK 50
elazami © 2022-2023 71
GROUP BY
Partition de l'ensemble des tuples en groupes homogènes:
SELECT continent, MIN(population), MAX(population),AVG(population),
SUM(surface), COUNT(*)
FROM Pays GROUP BY continent ;
Continent MIN(population) MAX(population) AVG(population) SUM(surface) COUNT(*)
Europe 5 50 18 438 4
Amérique 350 350 350 441 1
A noter : cette commande doit toujours s’utiliser après la commande WHERE et avant la
commande HAVING.
elazami © 2022-2023 72
HAVING
Conditions sur les fonctions d'agrégation
• Il n'est pas possible d'utiliser la clause WHERE pour faire des conditions sur une
fonction d'agrégation.
• Donc, si l'on veut afficher les pays dont on possède plus de 3 individus, la requête
suivante ne fonctionnera pas.
SELECT continent, COUNT(*)
FROM Pays
WHERE COUNT(*) > 3
GROUP BY continent ;
• Il faut utiliser HAVING qui se place juste après le GROUP BY
SELECT continent, COUNT(*)
FROM Pays
GROUP BY continent
HAVING COUNT(*) > 3;
elazami © 2022-2023 73
Renommage des attributs : AS
SELECT MIN(population) AS min_pop,
MAX(population) AS max_pop,
AVG(population) AS avg_pop,
SUM(surface) AS sum_surface,
COUNT(*) AS count
FROM Pays
WHERE continent = ‘Europe’ ;
min_pop max_pop avg_pop sum_surface count
5 50 18 438 4
elazami © 2022-2023 74
Opérateurs ensemblistes en SQL
1. UNION
Syntaxe:
requête_SELECT
UNION [ ALL] requête_SELECT
[ UNION [ ALL] requête_SELECT ….]
L’opérateur UNION supprime les données redondantes par défaut, sauf si l’option ALL est explicité.
Exemple: lister tous les clients appartenant aux tables CLIENT et CLIENT_CASA.
SELECT idclient , nom, tel
FROM Client
UNION
SELECT idclient , nom, tel
FROM Client_CASA;
elazami © 2022-2023 75
Opérateurs ensemblistes en SQL
2. INTERSECTION
Syntaxe:
requête_SELECT
INTERSECT
requête_SELECT
Exemple: afficher les livres dont le prix est superieur à 500 et qui sont toujours empruntés.
SELECT noliv
FROM livre l
WHERE [Link]>500
INTERSECT
SELECT noliv
FROM emprunt e
WHERE [Link] IS NULL
elazami © 2022-2023 76
Opérateurs ensemblistes en SQL
3. DIFFERENCE
Syntaxe:
requête_SELECT
MINUS
requête_SELECT
Exemple: trouver les livres non encore empruntés
SELECT noliv
FROM livre
MINUS
SELECT noliv
FROM emprunt
WHERE retour IS NULL
elazami © 2022-2023 77
SQL
Les Vues
elazami © 2022-2023 78
Les Vues en SQL
Une vue est une construction logique (table virtuelle) faites à
partir de tables existantes (tables de base);
elle ne contient aucune données en soit, elle n’est qu’une
représentation indirecte de données contenues dans
d’autres tables;
les vues sont très utilisées pour simplifier et optimiser
l’usage de structures intermédiaires souvent sollicitées;
elles sont constamment mises à jour par le SGBD.
elazami © 2022-2023 79
Les Vues en SQL
Création par:
CREATE VIEW nom (renommage facultatif des colonnes) AS
requête SELECT
Syntaxe générale:
CREATE [OR REPLACE]
[FORCE | NOFORCE] VIEW
nom-de-vue [(attr1, …, attrn)]
AS requête
[WITH CHECK OPTION
[CONSTRAINT nom-contrainte]]
[WITH READ ONLY]
elazami © 2022-2023 80
Les Vues en SQL
L'instruction OR REPLACE crée à nouveau la vue si elle existe déjà.
Les clauses FORCE et NOFORCE indiquent respectivement que :
• la vue est créée sans se soucier de l'existence des tables qu'elle
référence ou des privilèges adéquats sur les tables,
• la vue est créée seulement si les tables existent et si les permissions
requises sont données.
La clause WITH CHECK OPTION limite les insertions et les mises à jour
exécutées par l'intermédiaire de la vue.
La clause CONSTRAINT est un nom optionnel donné à la contrainte WITH
CHECK OPTION.
elazami © 2022-2023 81
Les Vues en SQL
Les vues peuvent être créées à partir d'autres vues.
Pour cela il suffit de référencer les vues dans la clause
FROM de l'instruction select.
CREATE VIEW nom_vue
AS
SELECT * FROM nom_vue2;
elazami © 2022-2023 82
Les Vues en SQL
Exemples simple:
elazami © 2022-2023 83
Les Vues en SQL
Interrogation avec: SELECT ..
FROM nom_de_la_vue
WHERE ..
comme pour les tables de base.
Exemple: Résultat:
select nom
from rbati
where numero = 'E1‘;
elazami © 2022-2023 84
Les Vues en SQL
Suppression d’une vue
DROP VIEW nom-de-vue
La suppression d’une vue n’entraîne pas la suppression des données
Renommer une vue
RENAME ancien-nom TO nouveau-nom
elazami © 2022-2023 85
Les Vues en SQL
Exemples 1:
• CREATE VIEW vue_personnel
AS SELECT sexe, nom, prenom, adresse, cp, ville
FROM tbl_personnel
WHERE service = 'commercial'
• CREATE VIEW vue_pers_serv
AS SELECT [Link], [Link], [Link], [Link], [Link]
FROM vue_personnel v, tbl_service t
WHERE code_service = 'SC02354L' GROUP BY [Link];
elazami © 2022-2023 86
Les Vues en SQL
Utilisation d'une vue :
• UPDATE vue_pers_serv
SET sexe = 'masculin' WHERE nom = 'Frédérique' AND prenom = 'Jean'
• SELECT [Link], [Link], [Link]
FROM vue_personnel AS v1, vue_services AS v2
WHERE v1.id_service = v2.id_service
• CREATE TABLE tbl_employes
AS SELECT v1.id_service, [Link], [Link], [Link], [Link]
FROM vue_personnel AS v1, vue_services AS v2
WHERE v1.id_service = v2.id_service
elazami © 2022-2023 87
Les Vues en SQL
Exemple 2:
elazami © 2022-2023 88
Les Vues en SQL
Création de la vue:
CREATE OR REPLACE VIEW
exemplairePlus (num, titre, real, support)
AS
SELECT numExemplaire, titre,nomIndividu, codesupport
FROM Exemplaire E, Film F, Individu
WHERE [Link] = [Link]
AND realisateur = numIndividu
AND probleme IS NULL;
elazami © 2022-2023 89
Les Vues en SQL
Sélection:
SELECT num, titre, dateLocation, login
FROM exemplairePlus, Location
WHERE num = numExemplaire
AND real = ‘Scorces'
AND dateRetour IS NULL;
elazami © 2022-2023 90
Les Vues en SQL
Insertion:
INSERT INTO exemplairePlus (num, support)
VALUES (150346, 'DVD');
Suppression:
DROP VIEW exemplairePlus;
elazami © 2022-2023 91
Les Vues en SQL
Contraintes d’intégrité (CHECK OPTION) :
CREATE VIEW anciensExemplaires
AS SELECT * FROM Exemplaire
WHERE numExemplaire < 2000
WITH CHECK OPTION;
UPDATE anciensExemplaires
SET numExemplaire = 3812
WHERE numExemplaire = 1318;
Sans 'WITH CHECK OPTION', c’est possible.
Avec 'WITH CHECK OPTION', c’est impossible.
elazami © 2022-2023 92
Bases de données
Fin du cours
elazami © 2022-2023 93
