Le langage SQL
MP
Introduction
Le SQL est un langage de requête structuré (Structured Query Language) destiné à
communiquer avec des bases de données relationnelles implémentées dans des SGBDR
(Système de Gestion de Bases de Données Relationnelles) tels que Oracle, Access, SQL Server,
SQLITE ou encore Sybase.
Le langage SQL permet d'effectuer diverses opérations comme la création, l'extraction, la
modification, la suppression, la fusion, etc., sur des collections de données, par l'intermédiaire
d'instructions particulières appelées des commandes, souvent assistées d'ailleurs par des clauses
ou des options.
Création d’une table
Syntaxe :
CREATE TABLE Nomtable (
colonne1 type1,
colonne2 type2,
.........,
.........);
Le Langage SQL 3
Création d’une table
Les principaux types de données supportés sont les suivants :
chaînes de caractères de taille fixe, complétées à droite par des
espaces ( CHAR(taille) ),
chaînes de taille variable ( VARCHAR(taille) ),
entiers sur 32 bits (INT ou INTEGER),
nombres en précision fixe(NUMERIC(nbChiffres,nbDecimales) ),
nombres en virgule flottante simple précision (REAL),
types date et/ou heure (DATE/TIME/TIMESTAMP).
Le langage SQL 4
Création d’une table
Les contraintes d’intégrité, portant sur une ou plusieurs colonnes,
possibles sont :
PRIMARY KEY, spécifie qu’un ensemble d’attributs est clé primaire,
UNIQUE, spécifie qu’un ensemble d’attributs ne contient pas des
doublons ( comme PRIMARY KEY, sauf qu’Il est possible de spécifier
plusieurs UNIQUE sur la même table)
FOREIGN KEY... REFERENCES, spécifie qu’un ensemble d’attributs
est clé étrangère,
NOT NULL, spécifie qu’un ensemble d’attributs ne contient pas de
valeur manquante (NULL)
Le langage SQL 5
Création d’une table
Exemple: Vente
NumAch
NumVoit
Date_vente
Prix Personne
Voiture NumAch
NumVoit Nom
Marque Age
Type Ville
Couleur
Le langage SQL 6
Création d’une table
Exemple: Vente (NumAch, NumVoit, Date_vente, Prix)
Supposons que les tables Personne et Voiture sont déjà créees
CREATE TABLE Vente (
NumAch INT NOT NULL,
NumVoit INT NOT NULL,
DateVente Date ,
Ville VARCHAR(30),
PRIMARY KEY (NumAch , NumVoit ),
FOREIGN KEY(NumAch) REFERENCES Personne (NumAch),
FOREIGN KEY(NumVoit) REFERENCES Voiture (NumVoit) );
Le langage SQL 7
Suppression d’une table / modification d’une table
Suppression d’une table :
DROP TABLE Nomtable ;
Modification du schéma d’une table :
1. Ajout d’un attribut à la relation en utilisant le mot clé ADD.
Dans ce cas, l'attribut est également ajouté aux tuples existants
et la valeur de l'attribut pour ces tuples est la valeur par défaut ;
ALTER TABLE R
ADD colonne Type;
Le langage SQL 8
Suppression d’une table / modification d’une table
2. Suppression d’un attribut à la relation en utilisant le mot clé DROP.
Dans ce cas, l'attribut est également ajouté aux tuples existants
et la valeur de l'attribut pour ces tuples est la valeur par défaut ;
ALTER TABLE R
DROP colonne ;
Exemple: Ajout de la colonne Tel (de type INT) dans la table Personne
ALTER TABLE Personne
ADD Tel INT ;
Le langage SQL 9
Création d’un index
L’utilisation d’un index permet souvent d'améliorer les performances lors
de requêtes sur une base de données.
Lorsque les relations ont un nombre très important de tuples, il devient
très coûteux lors d'une requête de vérifier que tous les tuples de la
relation vérifient une propriété.
Exemple:
CREATE INDEX IXPrix ON Vente(Prix ASC);
L’index IXPrix est une correspondance entre le contenu de la
colonne Prix et les numéros de ligne :
Le langage SQL 10
Création d’un index
NumAch NumVoit DateVente Prix
1 1 2013-02-12 70000
2 4 2010-12-02 55000
4 5 2009-08-26 40000
5 2 2011-09-10 80000
Index sur la
colonne Prix
N° ligne Prix
3 40000
2 55000
1 70000
4 80000
L’utilisation d’un index peut alourdir le temps de traitement du SGBDR lors
de la saisie des données.
Le langage SQL 11
Création d’une vue
Une « vue » est le résultat d’une requête que l’on peut manipuler de la
même façon qu’une table. On peut considérer une vue comme une
table dynamique (virtuelle) dont le contenu est recalculé à chaque
utilisation.
(les données ne sont pas stockées dans une table de la base).
CREATE VIEW Achteur (Nom, Age)
AS
SELECT Nom, Age
FROM personne ;
Le langage SQL 12
Création d’une vue
Une vue sert à :
♦ remplacer n’importe quelle table dans un select. Les vues permettent
ainsi de décomposer l’écriture des requêtes complexes.
♦ Faciliter et sécuriser l’accès aux données par les utilisateurs
Exemple:
CREATE VIEW Achteur (Nom, Age)
AS SELECT Nom, Age
FROM personne ;
♦ On peut limiter les droits d’accès d’un utilisateur à la consultation
du nom et de l’âge des achteurs.
♦ On peut exploiter cette vue dans une autre requête (SELECT)
Le langage SQL 13
Le langage de manipulation de données
♦ Projection
SELECT Ai ,…., Ap
FROM R ;
Exemple : Projection de la table Personne sur les colonnes Nom et Ville
SELECT Nom, Ville
FROM Personne ;
Personne
NumAch Nom Age Ville Nom Ville
1 yassin 42 Agadir yassin Agadir
2 Imane 33 Casablanca Imane Casablanca
3 Reda 30 Rabat Reda Rabat
4 Meryem 42 Agadir Meryem Agadir
5 Walid 27 Marrakech Walid Marrakech
Le langage SQL 14
Le langage de manipulation de données
♦ Projection
Exemple : Affichage des différents marques de voitures
SELECT DISTINCT Marque
FROM Voiture ;
Voiture
NumVoit Marque Modele Couleur
1 Ford Fiesta Noire Marque
2 Citroën C4 Blanche Ford
3 Renault Clio Rouge Citroën
4 Peugeot 307 cc Noire Renault
5 Citroën C3 Bleue Peugeot
6 Renault Twingo Noire
Le langage SQL 15
Le langage de manipulation de données
♦ Projection
Exemple : On peut demander quels sont les prix en euros des voitures (
les prix sont en dirhams) :
SELECT Prix, DateVente, (Prix / 11.03) AS PrixEuros
FROM Vente ;
Vente
NumAch NumVoit DateVente Prix Prix DateVente Prix_Euros
1 1 2013-02-12 70000 70000 2013-02-12 6342.8778
2 4 2010-12-02 55000 55000 2010-12-02 4983.6897
4 5 2009-08-26 40000 40000 2009-08-26 3624.5016
5 2 2011-09-10 80000 80000 2011-09-10 7249.0032
Le langage SQL 16
Le langage de manipulation de données
♦ Sélection SELECT *
FROM R
WHERE P;
♦ <, <=, =, >=, >, <> ;
♦ AND, OR, NOT ;
♦ IN, par exemple Marque IN (’Citroen’, ’Opel’) ; les voitures dont la
marque est ‘Opel’ ou ‘Citroen’
♦ BETWEEN, par exemple Prix BETWEEN 45000 AND 60000 ;
♦ LIKE pour comparer des chaînes de caractères, par exemple :
Nom LIKE ‘Me%’ (% remplace n’importe quel ensemble de caractère)
renverra les noms commençant par ‘Me’ (Mehdi, Meryem,..).
Le langage SQL 17
Le langage de manipulation de données
♦ Sélection
♦ Exemple 1 : Extraction des enregistrements de la table vente dont le
prix est supérieur à 60 000.
SELECT *
FROM Vente
WHERE Prix > 60000;
Vente
NumAch NumVoit DateVente Prix NumAch NumVoit DateVente Prix
1 1 2013-02-12 70000 1 1 2013-02-12 70000
2 4 2010-12-02 55000 5 2 2011-09-10 80000
4 5 2009-08-26 40000
5 2 2011-09-10 80000
Le langage SQL 18
Le langage de manipulation de données
♦ Sélection
♦ Exemple 2 : Extraction des voitures rouges ou blanches.
SELECT *
FROM Voiture
WHERE Couleur =IN‘Rouge’
(‘Rouge’OR
,’ Blanche’);
Couleur =’ Blanche’;
Voiture
NumVoit Marque Modele Couleur
1 Ford Fiesta Noire
NumVoit Marque Modele Couleur
2 Citroën C4 Blanche
2 Citroën C4 Blanche
3 Renault Clio Rouge
3 Renault Clio Rouge
4 Peugeot 307 cc Noire
5 Citroën C3 Bleue
6 Renault Twingo Noire
Le langage SQL 19
Le langage de manipulation de données
♦ Sélection
♦ Exemple 2 : Extraction des personnes dont l’âge est compris entre 25
et 40 ans .
SELECT *
FROM Personne
WHERE Age BETWEEN 25 AND 40;
Personne
NumAch Nom Age Ville
1 yassin 42 Agadir NumAch Nom Age Ville
2 Imane 33 Casablanca 2 Imane 33 Casablanca
3 Reda 30 Rabat 3 Reda 30 Rabat
4 Meryem 42 Agadir 5 Walid 27 Marrakech
5 Walid 27 Marrakech
Le langage SQL 20
Le langage de manipulation de données
♦ Sélection
♦ Exemple 3 : Extraction des personnes n’habitant pas Agadir
SELECT *
FROM Personne
WHERE NOT (Ville=‘Agadir’);
Personne
NumAch Nom Age Ville
1 yassin 42 Agadir NumAch Nom Age Ville
2 Imane 33 Casablanca 2 Imane 33 Casablanca
3 Reda 30 Rabat 3 Reda 30 Rabat
4 Meryem 42 Agadir 5 Walid 27 Marrakech
5 Walid 27 Marrakech
Le langage SQL 21
Le langage de manipulation de données
♦ Sélection
♦ Exemple 4 : Extraction des personnes dont le 2ème caractère de son
nom est un ‘e’
SELECT *
FROM Personne
WHERE Nom LIKE ‘_e%’;
Personne
NumAch Nom Age Ville
1 yassin 42 Agadir NumAch Nom Age Ville
2 Imane 33 Casablanca 3 Reda 30 Rabat
3 Reda 30 Rabat 4 Meryem 42 Agadir
4 Meryem 42 Agadir
5 Walid 27 Marrakech
Le langage SQL 22
Le langage de manipulation de données
♦ Sélection
♦ Exemple 4 : La requête permettant d'éditer les personnes dans
l'ordre des âges croissants est :
SELECT *
FROM Personne
ORDER BY Age ASC;
Personne
NumAch Nom Age Ville NumAch Nom Age Ville
1 yassin 42 Agadir 5 Walid 27 Marrakech
2 Imane 33 Casablanca 3 Reda 30 Rabat
3 Reda 30 Rabat 2 Imane 33 Casablanca
4 Meryem 42 Agadir 1 yassin 42 Agadir
5 Walid 27 Marrakech 4 Meryem 42 Agadir
Le langage SQL 23
Le langage de manipulation de données
♦ Sélection
♦ Exemple 7 : La requête permettant d'éditer la liste des personnes
dont la ville n’est pas saisie :
SELECT *
FROM Personne
WHERE Ville IS NULL;
Le langage SQL 24
Le langage de manipulation de données
♦ Produit cartésien
SELECT *
FROM R1 , R2 ;
Exemple : Le produit cartésien de la relation Personne et Voiture :
SELECT *
FROM Personne , Voiture ;
Le langage SQL 25
Le langage de manipulation de données
♦ Jointure
Exemple : La liste des voitures vendues (Marque, Couleur et Prix)
SELECT [Link], [Link], [Link]
FROM Voiture, Vente
Marque Couleur Prix
WHERE [Link] = [Link];
Ford Noire 70000
Citroën Blanche 80000
Voiture Peugeot Noire 55000
NumVoit Marque Modele Couleur Citroën Bleue 40000
Vente
1 Ford Fiesta Noire
2 Citroën C4 Blanche NumAch NumVoit DateVente Prix
3 Renault Clio Rouge 1 1 2013-02-12 70000
4 Peugeot 307 cc Noire 2 4 2010-12-02 55000
5 Citroën C3 Bleue 4 5 2009-08-26 40000
6 Renault Twingo Noire 5 2 2011-09-10 80000
Le langage SQL 26
Le langage de manipulation de données
♦ Jointure
Exemple : La liste des voitures vendues (Marque, Couleur et Prix)
SELECT [Link], [Link], [Link]
FROM Voiture, Vente
WHERE [Link] = [Link];
Marque Couleur Prix
SELECT [Link], [Link], [Link]
Ford Noire 70000
FROM Voiture JOIN Vente Citroën Blanche 80000
Voiture ON [Link] = [Link]; Peugeot Noire 55000
NumVoit Marque Modele Couleur Citroën Bleue 40000
Vente
1 Ford Fiesta Noire
NumAch NumVoit DateVente Prix
2 Citroën C4 Blanche
3 Renault Clio Rouge
1 1 2013-02-12 70000
4 Peugeot 307 cc Noire 2 4 2010-12-02 55000
5 Citroën C3 Bleue 4 5 2009-08-26 40000
6 Renault Twingo Noire 5 2 2011-09-10 80000
Le langage SQL 27
Le langage de manipulation de données
♦ Jointure
la requête permettant d’afficher la marque, la couleur
Exemple :
et le prix de la voiture achetée par « Walid » peut être
exprimée de la façon suivante :
SELECT [Link], [Link], [Link]
FROM Voiture Vo , Vente Ve, Personne Pe Marque Couleur Prix
WHERE [Link] = [Link] Ford Noire 70000
AND [Link] = [Link] Citroën Blanche 80000
AND [Link]=’Walid’;
Peugeot Noire 55000
Voiture
Citroën Bleue 40000
NumVoit Marque Modele Couleur
Vente
1 Ford Fiesta Noire
NumAch NumVoit DateVente Prix
2 Citroën C4 Blanche
1 1 2013-02-12 70000
3 Renault Clio Rouge
4 Peugeot 307 cc Noire 2 4 2010-12-02 55000
5 Citroën C3 Bleue 4 5 2009-08-26 40000
6 Renault Twingo Noire 5 2 2011-09-10 80000
Le langage SQL 28
Le langage de manipulation de données
♦ Fonctions d’agrégation
Exemple : La requête suivante permet de récupérer le prix moyen des
voitures
SELECT AVG(Prix)
FROM Vente ;
Dans cet exemple on n’a pas défini de groupe (il existe un groupe par
défaut : c’est la table toute entière)
Le langage SQL 29
Le langage de manipulation de données
♦ Fonctions d’agrégation
Exemple : La requête suivante permet de récupérer les différentes
marques de voitures
SELECT Marque
FROM Voiture
GROUP BY Marque;
On obtient dans ce cas le même résultat que si l’on avait utilisé le mot
clé DISTINCT vu précédemment.
Le langage SQL 30
Le langage de manipulation de données
♦ Fonctions d’agrégation
Exemple : La requête suivante permet de récupérer le nombre de voitures
des différentes marques de la table voiture
SELECT Marque, COUNT(*) AS Compte
FROM Voiture
GROUP BY Marque;
Le langage SQL 31
Le langage de manipulation de données
♦ Fonctions d’agrégation
Exemple 1: La requête suivante permet de récupérer la liste des
marques différentes de voitures avec le nombre d’exemplaires, dont
le nombre est supérieur à 1.
SELECT Marque, COUNT(*) AS Compte
FROM Voiture
GROUP BY Marque
HAVING Compte > 1;
Le langage SQL 32
Le langage de manipulation de données
♦ Fonctions d’agrégation
Exemple 2: Supposons que l’on veuille éliminer les voitures rouges du
résultat de l’exemple 1.
SELECT Marque, COUNT(*) AS Compte
FROM Voiture
WHERE NOT ( Couleur = Rouge)
GROUP BY Marque;
HAVING Compte > 1
La clause HAVING permet une restriction sur un groupe de ligne. Elle est
liée à la clause GROUP BY
Le langage SQL 33
Le langage de manipulation de données
♦ Fonctions d’agrégation
Une requête cumulant WHERE, HAVING, GROUP BY est évaluée dans
l'ordre suivant :
1 – Application de WHERE : sélectionne des lignes
2 – Application de GROUP BY : constitution des groupes
3 – Application de HAVING : sélection des groupes
4 – évaluation des fonctions d'agrégation sur les groupes constitués.
Le langage SQL 34
Le langage de manipulation de données
SQL permet de comparer une expression ou une colonne ou
résultat d’ une autre requête SELECT. Cette condition est dite «
condition de sous-requête » et les 2 requêtes sont dites requête
imbriquées
Les requêtes imbriquées dans SQL peuvent être utilisés pour
comparer une expression ou une liste d’ expression au résultat
d’une autre requête SELECT, déterminer l’appartenance d’une
expression ou l’ existence d’ une ligne dans le résultat d’ une
requête SELECT etc.
Le langage SQL 35
Le langage de manipulation de données
♦ Sous-requêtes (requêtes imbriquées)
=, >,<
SELECT ----
Requête
FROM ----
principale
WHERE ---- < ( SELECT ---- Requête
FROM ----
imbriquée
)
EXISTS, ANY, ALL
SELECT ----
FROM ----
WHERE ---- IN ( SELECT ----
FROM ----
)
Le langage SQL 36
Le langage de manipulation de données
Exemple : la requête suivante permet de récupérer la marque et le
modèle des voitures dont le prix dépasse 45000
JOINTURE!!
SELECT Marque , Modele
FROM Voiture
WHERE NumVoit IN ( SELECT NumVoit
FROM Vente
WHERE Prix > 45000
);
Attention: la sous-requête doit avoir une seule colonne comme résultat
Le langage SQL 37
Le langage de manipulation de données
Exemple : la requête suivante permet de récupérer les noms et les
âges des personnes qui n’ont acheté aucune voiture
SELECT Nom, Age
FROM Personne P
WHERE NOT EXISTS ( SELECT *
FROM Vente V
WHERE [Link] = [Link]
);
Le langage SQL 38
Le langage de manipulation de données
Remarque :
Le SELECT imbriqué dans le WHERE avec opérateur EXISTS (ou
NOT EXISTS ) consiste, pour chaque tuple du SELECT principal, à
tester si la cardinalité du SELECT imbriqué est vide ou pas.
Dans le cas de NOT EXISTS, les tuples du SELECT principal sont
sélectionnés si le SELECT imbriqué ne produit aucun tuples.
Le langage SQL 39
Le langage de manipulation de données
Insertion des données :
L’insertion s’effectue avec la commande INSERT dont la
syntaxe est la suivante :
INSERT INTO R( A1, A2, ... An)
VALUES (v1, v2, ... vn) ;
R est le nom d’une relation, et les A1, ... An sont les noms des attributs
dans lesquels on souhaite placer une valeur. Les autres attributs
seront donc à NULL (ou à la valeur par défaut).
Le langage SQL 40
Le langage de manipulation de données
Insertion des données :
Exemple : la requête suivante permet d’insérer un
enregistrement dans la table voiture
INSERT INTO voiture (NumVoit, Marque, Couleur)
VALUES (10,’Triumph’,’Bleue’);
La colonne ‘Modele’ est omise, elle prendra la valeur NULL
Le langage SQL 41
Le langage de manipulation de données
Suppression des données :
La suppression s’effectue avec la commande DELETE dont la
syntaxe est la suivante :
DELETE FROM R
WHERE C;
Cette requête supprime tous les n-uplets de R satisfaisant la
condition C.
Le langage SQL 42
Le langage de manipulation de données
Suppression des données :
Exemple : la requête suivante permet de supprimer les
voitures rouges de la table voiture
DELETE FROM Voiture
WHERE Couleur=’Rouge’;
Remarque : La clause WHERE est facultative ; si elle n'est pas
précisée, toutes les lignes de la table sont supprimées.
Le langage SQL 43