Le langage SQL
M. Nassar
Le langage de définition des données (LDD)
CREATE
ALTER
DROP
Le langage de manipulation des données (LMD)
SELECT
INSERT
UPDATE
DELETE
Le langage de contrôle des données (LCD) GRANT
REVOKE
COMMIT
ROLLBACK
2
Langage d’interrogation des données
Syntaxe simplifiée
SELECT [ DISTINCT ] * | expr{, expr}
FROM table
[ WHERE condition ]
[ ORDER BY expr| position [ASC| DESC]] ;
4
Condition
Permettent de comparer une colonne ou une expression à une autre
colonne ou expression
–Comparaison de valeurs =, >, <, >=, <=, <>
exp op_relationnel exp
–Intervalle BETWEEN
exp [NOT] BETWEEN exp AND exp
–Liste de valeurs IN
exp [NOT] IN (liste_de_valeurs)
–Comparaison avec filtre LIKE
char_exp [NOT] LIKE «chaine» ( _ un car; % n caractère)
–Indétermination IS NULL
colonne IS [NOT] NULL
5
Exemples
SELECT nom
FROM personnes
WHERE nom Like 'R_v%' ;
SELECT DISTINCT Prénom
FROM personnes;
SELECT nom, prenom
FROM personnes
WHERE taille > 180
ORDER BY nom ASC, naissance DESC ;
6
Fonctions de groupe
Fonction Description
Count(*|[ DISTINCT|ALL] expr) Le nombre de ligne de expr
Avg( [ DISTINCT | ALL] expr) Valeur moyenne de expr, en ignorant les valeurs
NULL
Min( [ DISTINCT | ALL] expr) Valeur minimale de expr, en ignorant les valeurs
NULL
Max( [ DISTINCT | ALL] expr) Valeur maximale de expr, en ignorant les valeurs
NULL
Sum( [ DISTINCT | ALL] expr) Somme des valeurs de expr, en ignorant les
valeurs NULL
7
Exemples
SELECT Avg(salaire),Sum(salaire),Min(salaire),Max(salaire)
FROM personnes ;
SELECT Min(naissance), Max(naissance)
FROM personnes ;
SELECT Min(nom), Max(nom)
FROM personnes ;
SELECT Count(*)
FROM personnes ;
8
Exemples
SELECT Count( telephone)
FROM personnes
SELECT Count( DISTINCT prenom)
FROM personnes;
SELECT Count(telephone), COUNT(*)
FROM personnes
WHERE ville = ‘Rabat’;
9
EMP Deptno sal
Question:
10 5000
10 1500 Quel est le salaire moyen
10 1300 Pour chaque département de
20 2975
la table EMP ?
20 3000
20 1100
30 2850
30 1250
30 1600
30 1500
30 950
30 1250
Table EMP
10
La clause GROUP BY
SELECT column, group_fonction
FROM table
[ WHERE condition ]
[ GROUP BY group_by_expression ]
[ ORDER BY column];
Remarque
Les attributs du select ne peuvent être que
– L’attribut qui crée le groupe
– Une fonction de groupe.
11
Exemples
SELECT deptno, AVG( sal )
FROM emp
GROUP BY deptno;
EMP Deptno sal
10 2600
20 2175
30 1566.7
12
EMP Deptno Poste sal
10 Dir technique 5000
10 Chef projet 1500
Question
10 programmeur 1300 Qelle est la somme des
20 Chef projet 2975 salaires pour
20 Analyste 3000 chaque poste, regroupés
20 programmeur 1100 par département ?
30 Chef projet 2850
30 commercial 1250
30 commercial 1600
30 commercial 1500
30 programmeur 950
30 commercial 1250
13
SELECT deptno, poste, SUM( sal )
FROM emp
GROUP BY deptno, poste;
Deptno poste SUM(sal)
10 programmeur 1300
10 Chef projet 1500
10 Dir technique 5000
20 Analyste 6000
20 programmeur 1900
20 Chef projet 2975
30 programmeur 950
30 Chef projet 2850
30 Commercial 5600
14
GROUP BY avec HAVING
SELECT column, group_fonction
FROM table
[ WHERE condition ]
[ GROUP BY group_by_expression
[ HAVING group_condition ] ]
[ ORDER BY column];
15
Exemple
SELECT deptno, MAX( sal )
FROM emp
EMP Deptno Poste sal
GROUP BY deptno
10 Dir technique 5000
HAVING MAX( sal ) > 2900;
10 Chef projet 1500
Deptno MAX(sal) 10 programmeur 1300
10 5000 20 Chef projet 2975
20 3000 20 Analyste 3000
30 2850 20 programmeur 1100
30 Chef projet 2850
30 commercial 1250
30 commercial 1600
Deptno MAX(sal)
30 commercial 1500
10 5000
30 programmeur 950
20 3000
30 commercial 1250
16
Synthèse
SELECT column, group_fonction
FROM tables
[ WHERE condition ]
[ GROUP BY group_by_expression
[ HAVING group_condition ] ]
[ ORDER BY column];
17
Requêtes sur plusieurs tables
Les opérateurs de jointures
L’imbrication de requêtes
Les opérateurs ensemblistes
18
Requêtes sur plusieurs tables: la jointure
Equijointure ( jointure naturelle)
Autojointure (jointure sur la même table)
Non-équijointure (jointure par non égalité, théta jointure)
19
Requêtes sur plusieurs tables: la jointure
Equijointure ( jointure naturelle)
SELECT expr
FROM table1, table 2
WHERE table1.col1= table2.col2
tab1 col1 tab2 col2
20
Equijointure ( jointure naturelle)
Liste des patients ayant un RDV
avec le docteur ‘Ouazzani’
SELECT [Link]
FROM PAT, RDV, DOC
WHERE [Link] = [Link]
and [Link] = [Link]
and [Link] =‘Ouazzani’;
21
Autojointure
EMP Deptno NOM sal
Liste des employés ayant un salaire 10 Ouazzani 5000
égal à celui de « Filali» 10 Rachidi 1500
10 Filali 1250
20 Youssfi 2975
20 Anwar 3000
20 Radi 1100
30 Rabii 2850
30 Naciri 1250
30 Ghazali 1600
30 Farabi 1500
30 Bahi 950
30 Bahiri 1250
22
Requêtes sur plusieurs tables: la jointure
Autojointure
SELECT expr
FROM table1 Alias1, table1 Alias 2
WHERE Alias1.col1= Alias2.col1
tab1 col1
23
Autojointure
EMP Deptno NOM sal
Liste des employés ayant un salaire 10 Ouazzani 5000
égale à celui de « Filali » 10 Rachidi 1500
10 Filali 1250
20 Youssfi 2975
20 Anwar 3000
SELECT [Link] 20 Radi 1100
FROM EMP E1, EMP E2 30 Rabii 2850
WHERE [Link]=[Link]
30 Naciri 1250
and
[Link] =‘Filali’; 30 Ghazali 1600
30 Farabi 1500
30 Bahi 950
30 Bahiri 1250
24
Autojointure
EMP Deptno NOM sal
Liste des employés ayant un salaire 10 Ouazzani 5000
<= à celui de « Filali » 10 Rachidi 1500
10 Filali 1250
20 Youssfi 2975
20 Anwar 3000
SELECT [Link] 20 Radi 1100
FROM EMP E1, EMP E2 30 Rabii 2850
WHERE [Link]<=[Link]
30 Naciri 1250
and
[Link] =‘Filali’; 30 Ghazali 1600
30 Farabi 1500
30 Bahi 950
30 Bahiri 1250
25
Non Equijointure (thêta jointure)
La liste des employés et leurs grades
SELECT [Link], [Link]
FROM EMP, GRADE
WHERE [Link] BETWEEN
[Link] and [Link]
EMP nom salemp GRADE salmin salmax GRA
Radi 90 50 100 1
Naciri 210 101 220 2
Filali 110 221 300 3
Alaoui 200 301 500 4
26
Requêtes imbriquées
27
Syntaxe générale
SELECT colonnes_de_projection
FROM table
sens d’exécution
WHERE expr operator (
SELECT colonnes_de_projection
FROM table
WHERE …..
);
Remarque:
Pas de ORDER BY ou UNION dans la sous requête
28
Type de sous requête opérateur
ramène une seule ligne avec =, >, >=, >, <=, <>
une seule colonne
ramène plusieurs lignes avec IN : appartenance
une seule colonne ALL: à tous
ANY: au moins un
EXISTS: non vide
plusieurs lignes avec plusieurs EXISTS: non vide
colonnes.
Type de sous requêtes et opérateurs possibles
29
Exemples
Le nom des employés qui gagnent plus que ’Filali’ ?
SELECT nom EMP nom salemp
FROM EMP
WHERE salemp > (SELECT salemp Radi 100
Alaoui 220
FROM EMP
Filali 110
WHERE nom=‘Filali’);
Sari 200
Les employés ayant un salaire supérieur à la
moyenne?
EMP nom salemp
SELECT nom
FROM EMP Radi 100
WHERE salemp < (SELECT AVG(salemp) Alaoui 220
Filali 110
FROM EMP);
Sari 200
30
Exemples
Le nom des employés qui ne sont pas les moins payés ?
SELECT nom EMP nom salemp
FROM EMP
WHERE salemp > ANY (SELECT salemp Radi 100
Alaoui 220
FROM EMP );
Filali 110
Sari 200
Le nom de l’employé le mieux payé?
EMP nom salemp
SELECT nom
Radi 100
FROM EMP Alaoui 220
WHERE salemp >= ALL (SELECT salemp Filali 110
FROM EMP); Sari 200
31
IN : la condition est vraie si l’élément appartient au résultat retourné
par la sous-requête
ANY : la condition est vraie si la comparaison est vérifiée pour au moins
un élément du résultat retourné par la sous-requête
ALL : la condition est vraie si la comparaison est vérifiée pour tous les
éléments du résultat retourné par la sous-requête
EXISTS (sous-requête) :
à Vraie si Résultat(sous-requête)< > Ensemble vide
à Faux dans le cas contraire
32
Exemples :
Q1 : Les docteurs n’ ayant pas eu des RDV en 2010 ?
Select * from DOC
Where NumDoc NOT IN (select NumDoc from RDV where DateRDV
Between ‘01/01/2010’ and ‘31/12/2010’)
Select * from DOC D
Where NOT EXISTS (select * from RDV R where
[Link]=[Link] and DateRDV Between ‘01/01/2010’ and
‘31/12/2010’)
33
Opérateurs ensemblistes
INTERSECT
UNION
UNION ALL
MINUS
Requête SELECT
<Opérateur ensembliste>
Requête SELECT
34
Exemples
Q1 : les Numeros de docteurs n’ayant jamais eu de RDV ?
Select NumDoc from DOC
MINUS
Select NumDoc from RDV;
Q2 : les docteurs patients ?
Select CIN, Nom, Prénom from DOC
INTERSECT
Select CIN, Nom, Prénom from PAT;
35
Langage de Manipulation des Données
(LMD)
Le langage de définition des données (LDD)
CREATE
ALTER
DROP
Le langage de manipulation des données (LMD)
SELECT
INSERT
UPDATE
DELETE
GRANT
REVOKE
Le langage de contrôle des données (LCD) COMMIT
ROLLBACK
Syntaxe INSERT INTO
INSERT INTO <nom table>
[( colonne1 [, colonne2] … )]
VALUES
(<valeur1> [, <valeur2>] … )
| <requête>
;
Exemples:
Table: DOC(NumDoc, NomDoc, VilleDoc)
INSERT INTO DOC (NumDoc, NomDoc, VilleDoc)
values (123, ‘Filali’,’Rabat’);
<-> INSERT INTO DOC values (123, ‘Filali’,’Rabat’);
INSERT INTO DOC (NumDoc, NomDoc) values (444, ‘Alaoui’);
INSERT INTO DOC select * from tabledesmedecins;
Syntaxe UPDATE
UPDATE <nom table>
SET <colonne> = valeur
[, <colonne> = valeur ] …
[WHERE <condition de modification> ];
Exemples:
Table: DOC(NumDoc, NomDoc, VilleDoc)
UPDATE DOC
SET NomDoc=‘Rachidi’, NomVille=‘Casablanca’
Where NumDoc=444;
UPDATE DOC
SET VilleDoc=Null;
Syntaxe DELETE
DELETE FROM <nom table>
[WHERE <condition>]
Exemples:
Table: DOC(NumDoc, NomDoc, VilleDoc)
DELETE FROM DOC WHERE NumDoc=444;
DELETE FROM DOC
WHERE NomDoc IN ( select NomDOc
from DOC
WHERE VilleDoc=NULL
);
Langage de Définition des Données
(LDD)
Le langage de définition des données (LDD)
CREATE
ALTER
DROP
Le langage de manipulation des données (LMD)
SELECT
INSERT
UPDATE
DELETE
Le langage de contrôle des données (LCD)
GRANT
REVOKE
COMMIT
ROLLBACK
Syntaxe de la commande CREATE TABLE
CREATE TABLE nom Table
(
colonne type [contrainte de la colonne]
[, colonne type [contrainte de la colonne]]
…
[, contrainte de la table] …);
Contrainte sur une colonne
[[ CONSTRAINT <nom de la contrainte> ]
[ NOT NULL |
UNIQUE |
PRIMARY KEY |
CHECK (condition) |
REFERENCES <nom de la table> (colonne)
]
]
Contrainte sur une table
[ CONSTRAINT <nom de la contrainte>
[
UNIQUE (liste de colonnes) |
PRIMARY KEY (liste de colonnes) |
CHECK (condition) |
FOREIGN KEY (liste de colonnes)
REFERENCES <nom de la table> (liste colonnes) [ <mode>]
]
]
[<mode>::=[ON DELETE {CASCADE|SET DEFAULT|SET NULL}]
| [ON UPDATE {CASCADE| SET DEFAULT| SET NULL}]
]
Remarque:
Sur la colonne ou la table: si la contrainte ne fait intervenir qu’un
SEUL ATTRIBUT.
sur la table: si la contrainte fait intervenir PLUSIEURS ATTRIBUTS.
Create Table DOC(
NumDOC NUMBER PRIMARY KEY,
NomDOC VARCHAR2(20),
VilleDOC VARCHAR2(20)
);
Create Table DOC(
NumDOC integer,
NomDOC VARCHAR2(20),
VilleDOC VARCHAR2(20),
Constraint PK_DOC Primary Key (NumDOC)
);
Create Table DOC(
NumDOC Number Constraint PK_DOC PRIMARY KEY,
NomDOC VARCHAR2(20),
VilleDOC VARCHAR2(20)
);
Create Table DET(
NumORD Number,
NumLigne Number,
NumMED Number,
QTE Number Not Null,
Constraint PK_DET Primary Key (NumORD, NumLigne),
Constraint NbMaxMed Check (NumLigne < 5),
Constraint Ref_ORD
Foreign Key (NumORD) References ORD(NumORD)
on update cascade,
Constraint Ref_MED
Foreign Key (NumMED) References MED(NumMED)
on update cascade
);
Syntaxe de la commande ALTER TABLE
ALTER TABLE <nom de la Table>
{
ADD COLUMN <def Colonne> |
DROP COLUMN <nom Colonne> [RESTRICT|CASCADE] |
ADD CONSTRAINT <def Contrainte> |
DROP <nom Contrainte> [RESTRICT|CASCADE] |
}
RESTRICT: pas de destruction si l’objet est référencé ou utilisé ailleurs
CASCADE: propage la destruction
Exemples
ALTER TABLE DOC ADD COLUMN TEL NUMBER NOT NULL;
ALTER TABLE DOC DROP COLUMN TEL;
ALTER TABLE DOC ADD CONSTRAINT NN_NOM NomDoc NOT NULL;
ALTER TABLE DOC DROP CONSTRAINT NN_NOM;
Syntaxe DROP TABLE
DROP TABLE <Nom de la table>
Exemple:
Drop table DOC;
LES VUES
LES VUES:
Table virtuelle calculée à partir d’autres tables ou vues par une requête
Pas d’existence physique mais recalculée chaque fois qu’elle est
invoquée
Vue mono table
Vue multi-tables
Intérêts:
Indépendance application/données
Personnalisation des données selon les besoins des utilisateurs
Confidentialité
Rapidité des requêtes
Utilisation:
Pour les sélections, comme une table ordinaire
Pour les maj. (insert, update, delete), y a des restrictions
Syntaxe de CREATE VIEW
CREATE VIEW <nom vue> [(liste des attributs)]
AS <requête de sélection>
[WITH CHECK OPTION]
WITH CHECK OPTION
Permet de vérifier que les mises à jour ou les insertions faites à
travers la vue ne produisent que des lignes qui feront partie de la
sélection de la vue.
Exemples:
CREATE VIEW MedecinsDeRabat AS
Select *
From DOC
Where villeDoc=‘Rabat’;
CREATE VIEW DocPat AS
Select NomDOc, NomPat
FROM DOC D, RDV R, PAT P
WHERE [Link]=R. NumDoc and [Link]=[Link];
Règles d’utilisations des VUES
DELETE
SELECT
UPDATE
Le SELECT principal de
INSERT
la vue contient
Plusieurs tables OUI NON NON NON
GROUP BY OUI NON NON NON
DISTINCT OUI NON NON NON
fonction de groupe OUI NON NON NON
Attribut calculé OUI NON OUI NON
Attribut NOT NULL pas dans le OUI OUI OUI NON
SELECT
UNION, INTERSETC, MINUS OUI NON NON NON