premier cours SQL - Laboratoire d`Informatique de l`Université du

Initiation à SQL
Le langage de communication avec une base de
données relationnelles
Application avec PostgreSQL
Nathalie Camelin – 2011/2012
Université du Maine
Institut Claude Chappe
D'après Levy Estève Plew&Stephens Audibert
Des données aux SGBD-R
●
●
Qu'est-ce qu'une donnée ?
–
« Dans les technologies de l'information (TI), une donnée est
une description élémentaire, souvent codée, d'une chose, d'une
transaction d'affaire, d'un événement, etc. » Wikipédia
–
« Représentation d'une information sous une forme
conventionnelle destinée à faciliter son traitement. » Médiadico
Une Base de Données (BD) est une collection de
données
–
mécanisme organisé de stockage
–
ex : l'annuaire téléphonique
Introduction à SQL - N.Camelin
2
Des données aux SGBD-R
●
Modèle relationnel
–
1970 : Edgar F. Codd, mathématicien chez IBM
●
A Relational Model of Data for Large Shared Data Banks
●
Pourquoi ?
–
–
●
accès performant à de grandes quantités de données
réponse aux problèmes de redondance ou d'intégrité des données
Une Base de Données Relationnelles (BD-R)
–
suit les principes de l'algèbre relationnel (Codd)
–
données stockées dans des unités logiques, les tables
–
relations entre tables
Introduction à SQL - N.Camelin
3
Des données aux SGBD-R
●
Le Système de Gestion de Bases de Données
(SGBD)
–
gestion et accès à la BD : ajout, modification et recherche
–
quelques objectifs :
●
non redondance des données
●
cohérence des données
●
partage des données
●
sécurité des données
Introduction à SQL - N.Camelin
4
Et SQL dans tout ça?
●
SQL : Structured Query Language
un langage de requêtes pour communiquer avec une BDR
–
●
De nombreuses fonctionnalités !
–
Gestion, insertion, suppression, modification de données
–
Opérations arithmétiques et de comparaison
–
Affichage des données
–
Affectation
–
Fonctions d'aggrégations
Introduction à SQL - N.Camelin
5
Bref historique du SQL
●
À la suite des travaux de Codd ...
–
1974 : Lancement du projet System/R chez IBM
●
●
Basé sur les travaux de Chamberlin et Boyce
1er prototype nommé SEQUEL
–
–
Structured English Query Language
1976 : Nouvelle implémentation SEQUEL/2
●
Fonctionnalités multi-utilisateurs et multi-tables
●
renommé SQL
Introduction à SQL - N.Camelin
6
Implémentation du langage SQL
●
Différentes implémentations
–
1978 : Tests concluants chez des clients pour IBM
●
–
–
Utilité et faisabilité
IBM : Développement de produits commerciaux basé sur le
prototype System/R
●
1981 : SQL/DS
●
1983 : BD2
1979 : Oracle, première version commerciale par Software
Development Laboratories
Introduction à SQL - N.Camelin
7
Quelques SGBD connus et utilisés
●
Voici une liste non-exhaustive :
–
PostgreSQL
http ://www.postgresql.org/
–
MySQL
http ://www.mysql.org/
–
Oracle
http ://www.oracle.com/
–
IBM DB2
–
Microsoft SQL
–
Informix
–
...
http://www-306.ibm.com/software/data/db2/
http ://www.microsoft.com/sql/
http://www-306.ibm.com/software/data/informix/
Introduction à SQL - N.Camelin
8
La norme SQL
–
1986 : Approbation ANSI de l'implémentation IBM
–
1987 : Norme ISO
–
1989 : Norme ISO avec révision mineure → SQL/89 ou SQL1
–
1992 : Norme ISO avec révision majeure → SQL/92 ou SQL2
●
–
1999 : Norme ISO → SQL/99 ou SQL3
●
●
–
Intégration de parties plus avancées (SGBDRO, interface de
programmation, gestion d'intégrité des données...)
2 niveaux de conformité
2003 : ISO → SQL:2003, manipulations XML
●
–
3 niveaux de conformité
Mises à jour en 2006
2008 : dernière norme ISO en date, SQL:2008 ??
Introduction à SQL - N.Camelin
9
PostgreSQL
●
●
Des atouts majeurs
–
Projet de SGBD-R non commercial le plus avancé
–
Projet Open Source toujours en développement
–
Existence de distributions commerciales (support tech.)
Historique
–
1977 : Projet Ingres débuté par l’Université de Berkeley
●
–
1986 : Nouvelle équipe de l'Université de Berkeley
●
–
Relation Technologies/Ingres Corporation
Ingres → Postgres
1996 : Ajout de nouvelles fonctionnalités par la communauté du
logiciel libre
●
Postgres → PostgreSQL
Introduction à SQL - N.Camelin
10
PostgreSQL et la norme SQL ?
●
Version installée à l'institut Claude Chappe
–
Côté serveur : 8.3.7
●
–
●
Documentation : http://docs.postgresql.fr/8.3/
Côté clients : psql (PostgreSQL) 8.4.X et 9.0.X
Conformité avec la norme SQL
–
standard SQL : ISO/IEC 9075 « Database Language SQL »
–
http://docs.postgresql.fr/8.3/features.html
–
Un extrait :
PostgreSQL supporte la plupart des fonctionnalités majeures de SQL:2003. Sur les 164
fonctionnalités requises pour une conformité « centrale » complète (full Core
conformance), PostgreSQL se conforme à plus de 150. De plus, il existe une longue
liste de fonctionnalités optionnelles supportées. À la date de rédaction de ce document,
aucune version de quelque système de gestion de bases de données que ce soit
n'affiche une totale conformité à SQL:2003 « centrale »
Introduction à SQL - N.Camelin
11
Ce que n'est pas SQL
●
Un langage procédural, de type C ?
–
●
Coder comment obtenir ce que l'on veut?
–
●
Non, c'est un langage déclaratif
Non, on dit simplement ce que l'on veut
C'est le SGBDR qui définit comment obtenir de façon
optimale ce qui lui est demandé
Introduction à SQL - N.Camelin
12
Concepts de base
●
●
●
table : structure de données formée de colonnes et de lignes
champ :
–
plus petit élément d'information (insécable)
–
intersection d'une colonne et d'une ligne
enregistrement : collection de champs dont l'unité repose sur
une ou plusieurs relations → ligne
●
les clés :
–
–
clé primaire : identifiant unique d'un enregistrement dans
une table
clé étrangère : attribut correspondant à la clé primaire
d'une autre table
Introduction à SQL - N.Camelin
13
Concepts de base
clé primaire
Base de données
id_emp nom
prenom ville
1 smith
joe
Los Angeles
2
allen mickael
New York
3
ward
bob
New York
4 jones
brad
Los Angeles
5 martin
tom San Franscico
id_job
job
M1
S1
P
paie
manager
salesman
president
colonne
enregistrement
(ligne)
champ
(cellule)
id_emp
45k€
26k€
100k€
5
3
2
tables
clé étrangère
Introduction à SQL - N.Camelin
14
Concepts de base
●
●
●
identifiant : autre nom du concept de clé
mot-clé : terme réservé de sql (commande, clause, opérateur, …)
–
longueur max 31 caractères (lettres, chiffres ou _ )
–
débute par une lettre
identificateur :
–
nom d'objets définis en interne (table, fonction, …)
longueur max 31 caractères (lettres, chiffres ou _ )
–
débute par une lettre ou _
Introduction à SQL - N.Camelin
15
Concepts de base (étendus...)
●
contrainte : mécanisme de contrôle de validité des contraintes
interdisant l'insertion de données violant les contraintes
●
index : construction physique d'une structure de données sur une
ou plusieurs colonnes d'une table
→ Accélère la recherche d'information
→ Construction pour chaque clés primaire et étrangères d'une
table
●
vue : table virtuelle, non stockée physiquement
●
trigger : ensemble d'instructions sql permettant d'assurer l'intégrité
des données
Introduction à SQL - N.Camelin
16
Contraintes d'intégrité
 Assurer précision et logique des données de BDR
 Préciser généralement à la création d'une table
 Quelques exemples :
• PRIMARY KEY
– un ou plusieurs champs
– unicité de la clé → unicité de l'enregistrement
• UNIQUE
– valeur d'un champ unique pour chaque enregistrement
• NOT NULL
– obligation de saisir une valeur dans le champ à chaque
nouvel enregistrement
Introduction à SQL - N.Camelin
17
SQL : un langage fortement typé
●
Une donnée ↔ Un type
●
Un type → Un ensemble d'opérations applicables
●
Type : contrainte définit lors de la création de la
table
–
●
!! important, y penser dès la modélisation !!
Plus d'une 30aine de types définis dans PosgreSQL
–
Certains basés sur une norme ISO
–
D'autres non standards
●
!! compatibilité avec d'autres SGBDR ...
Introduction à SQL - N.Camelin
18
Types de données
 chaînes de caractères :
• longueur fixe : CHARACTER(n), CHAR(n)
• longueur variable avec limite: CHARACTER VARYING(n),
VARCHAR(n),
• sans limite : TEXT
 chaînes numériques :
• entier : SMALLINT, INTEGER, BIGINT
• flottant non précis: REAL, DOUBLE PRECISION
• flottant précis : NUMERIC
 autres :
• booléen : BOOLEAN
Introduction à SQL - N.Camelin
• valeurs de dates et heures : TIMESTAMP[(p)], DATE[(p)]
• et encore : types géométriques (coordonnées), tableaux, ...
19
Types de données
●
Booléens et binaires
Type
Description
Norme
boolean, bool
Valeur unique : true ou false
(différents libellés)
SQL99
bit(n)
Chaîne de bits de longueur n
SQL92
bit varying(n),
varbit(n)
chaîne de bits de longueur n variable
(n=taille max)
SQL92
Introduction à SQL - N.Camelin
20
Types de données
●
Caractères
Type
Description
Norme
character(n),
char(n)
Chaîne de caractères de
longueur n
SQL89
character
varying(n),
varchar(n)
text
Chaîne de caractères de
longueur max n variable
SQL92
Chaîne de caractères de
longueur variable et
illimitée
Spécificité
PostgreSQL
Introduction à SQL - N.Camelin
21
Types de données
●
Numériques entiers
Description
Norme
smallint, int2
Entier sur 2 octets
SQL89
integer, int, int4
Entier sur 4 octets
SQL92
bigint, int8
Entier sur 8 octets (jusqu’à 18 Spécif.
chiffres)
P_SQL
Type
Introduction à SQL - N.Camelin
22
Types de données
●
Numériques réels
Description
Norme
real, float4
Flottant sur 4 octets
SQL89
double
precision,
float8, float
numeric(p,s),
decimal(p,s)
Flottant sur 8 octets
SQL89
Type numérique exact avec
précision p quelconque et facteur
d’échelle s
Spécif.
P_SQL
Type
Introduction à SQL - N.Camelin
23
Types de données
●
Date et heure
Type
Description
Norme
date
Date du calendrier
(différents formats possibles)
SQL92
time
Heure
SQL92
time with time zone
Heure avec informations sur la
zone horaire
SQL92
interval
Délai quelconque
SQL92
Introduction à SQL - N.Camelin
24
Types de données
●
Autres types spécifiques à PosgreSQL
–
Numériques
●
–
Géométriques
●
–
box, line, lseq, circle, path, point, polygon
Pour le réseau
●
–
money et serial
cidr (Adresse/masque IP), inet (Adresse IP), macadr
(Adresse MAC)
Et bien d'autres encore ...
Introduction à SQL - N.Camelin
25
Le type NULL
●
NULL correspond à :
–
●
Affectation :
–
Tout champ indépendamment de son type
● !! sauf contrainte NOT NULL
●
Référencement :
●
NULL n'est pas
–
–
–
●
méta-valeur représentant une absence de valeur
le mot clé NULL
la valeur booléenne false
La chaîne de caractères vide ''
attention : 'NULL' n'est pas NULL
Valeur par défaut d'un champ (si autorisé!)
Introduction à SQL - N.Camelin
26
Instruction SQL
●
Base de la communication avec le serveur
PosgreSQL
●
Du code facile à lire
●
Syntaxe :
–
Commence par une commande → définition de l'action
–
Contient des clauses → modificateurs formels précisant la
fonction
–
Finit par un ;
Introduction à SQL - N.Camelin
27
5 catégories de commandes
●
DDL - Data Definition Language
–
●
DML - Data Manipulation Language
–
●
gestion des droits d'accès aux données
DCL - Data Control Language
–
●
manipulation des données : insertion, suppression,
modification, extraction, ...
DQL - Data Query Language
–
●
définition des éléments de la base de données : tables,
champs, clés,...
gestion des transactions
SQL intégré
Introduction à SQL - N.Camelin
28
Commandes DDL
 principalement, manipulation des tables :
• créer (CREATE TABLE)
• modifier (ALTER TABLE)
• supprimer (DROP TABLE)
 l'instruction CREATE TABLE
• questions préalables lors de la modélisation:
– quels types de données va contenir la table?
– nom de la table ? noms des champs?
– quel type de données est assignée à chaque
colonne?
Introduction à SQL - N.Camelin
29
Exemple de création de table
CREATE TABLE emp
( empno
integer
name
varchar(10)
job
varchar(10)
mgr
integer,
hiredate
date,
sal
numeric(7,2),
comm
numeric(7,2),
deptno
integer
);
PRIMARY KEY,
NOT NULL,
NOT NULL,
NOT NULL
Introduction à SQL - N.Camelin
30
Commandes DML
 Langage de manipulation des données,
3 commandes de bases:
• INSERT : insertion de nouvelles données dans une table
• UPDATE : mise à jour des données
• DELETE : suppression d'enregistrements
 Exemples d'insertion d'enregistrements
• tous les champs sont renseignés:
INSERT INTO emp
VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
• déclaration explicite des champs :
INSERT INTO emp (empno, name, job,mgr,hiredate,sal,deptno)
VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,null,20);
Introduction à SQL - N.Camelin
31
Commandes DML
 La commande SELECT
●
–
consultation de la base de données
–
associé à des mots clés et des clauses pour trouver et
visualiser quasiment toutes les informations possibles
–
instruction la plus puissante et la plus complexe!
Pour l'instant : les requêtes « simples »
–
rappel des notions : projection et sélection
–
filtrage simple des données
–
les opérateurs
–
quelques fonctions
Introduction à SQL - N.Camelin
32
Exemple : la table des employés
SELECT * FROM emp;
empno name
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
job
CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
CLERK
ANALYST
CLERK
mgr
hiredate
sal
comm deptno
7902 1980-12-17
800
20
7698 1981-02-20 1600
300
30
7698 1981-02-22 1250
500
30
7839 1981-04-02 2975
20
7698 1981-09-28 1250 1400
30
7839 1981-05-01 2850
30
7839 1981-06-09 2451
10
7566 1982-12-09 3000
20
1981-11-17 5000
10
7698 1981-09-08 1500
0
30
7788 1983-01-12 1100
20
7698 1981-12-03
950
30
7566 1981-12-03 3000
20
7782 1982-01-23 13000
10
Introduction à SQL - N.Camelin
33
La commande Select
●
Projection : clause SELECT
–
Rappel : En algèbre relationnelle, la projection élimine des
attributs d'une relation
–
Syntaxe :
SELECT att1, att2, ... attN FROM nom_table ;
●
Sélection : clause WHERE
–
Rappel : En algèbre relationnelle, la sélection sur la condition C
permet de garder les n-uplets qui satisfont C.
–
Syntaxe :
SELECT * FROM nom_table WHERE condition ;
Introduction à SQL - N.Camelin
34
Exemple d'une projection
SELECT name, hiredate, sal FROM emp;
empno
name
job
mgr
hiredate
sal comm deptno
7369 SMITH
CLERK
7902 1980-12-17 800
7499 ALLEN SALESMAN 7698 1981-02-20 1600 300
7521 WARD SALESMAN 7698 1981-02-22 1250 500
7566 JONES MANAGER 7839 1981-04-02 2975
7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400
7698 BLAKE MANAGER 7839 1981-05-01 2850
7782 CLARK MANAGER 7839 1981-06-09 2450
7788 SCOTT
ANALYST 7566 1982-12-09 3000
7839
KING
PRESIDENT
1981-11-17 5000
7844 TURNER SALESMAN 7698 1981-09-08 1500
0
7876 ADAMS
CLERK
7788 1983-01-12 1100
7900 JAMES
CLERK
7698 1981-12-03 950
7902 FORD
ANALYST 7566 1981-12-03 3000
7934 MILLER
CLERK
1300
Introduction7782
à SQL - 1982-01-23
N.Camelin
20
30
30
20
30
30
10
20
10
30
20
30
20
10
35
Exemple d'une projection
SELECT name, hiredate, sal FROM emp;
name
hiredate
sal
SMITH 1980-12-17 800
ALLEN 1981-02-20 1600
WARD 1981-02-22 1250
JONES 1981-04-02 2975
MARTIN 1981-09-28 1250
BLAKE 1981-05-01 2850
CLARK 1981-06-09 2450
SCOTT 1982-12-09 3000
KING
1981-11-17 5000
TURNER 1981-09-08 1500
ADAMS 1983-01-12 1100
JAMES 1981-12-03 950
FORD 1981-12-03 3000
MILLER
1982-01-23 1300
Introduction à SQL - N.Camelin
36
Exemple d'une sélection
SELECT * FROM emp where sal>=1500;
empno
name
job
mgr
hiredate
sal comm deptno
7369 SMITH
CLERK
7902 1980-12-17 800
7499 ALLEN SALESMAN 7698 1981-02-20 1600 300
7521 WARD SALESMAN 7698 1981-02-22 1250 500
7566 JONES MANAGER 7839 1981-04-02 2975
7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400
7698 BLAKE MANAGER 7839 1981-05-01 2850
7782 CLARK MANAGER 7839 1981-06-09 2450
7788 SCOTT
ANALYST 7566 1982-12-09 3000
7839
KING
PRESIDENT
1981-11-17 5000
7844 TURNER SALESMAN 7698 1981-09-08 1500
0
7876 ADAMS
CLERK
7788 1983-01-12 1100
7900 JAMES
CLERK
7698 1981-12-03 950
7902 FORD
ANALYST 7566 1981-12-03 3000
7934 MILLER
CLERK
7782 1982-01-23 1300
Introduction à SQL - N.Camelin
20
30
30
20
30
30
10
20
10
30
20
30
20
10
37
Exemple d'une sélection
SELECT * FROM emp where sal>=1500;
empno
name
job
mgr
hiredate
sal comm deptno
7499 ALLEN SALESMAN 7698 1981-02-20 1600
7566 JONES MANAGER 7839 1981-04-02 2975
7698 BLAKE MANAGER 7839 1981-05-01 2850
7782 CLARK MANAGER 7839 1981-06-09 2450
7788 SCOTT
ANALYST 7566 1982-12-09 3000
7839
KING
PRESIDENT
1981-11-17 5000
7844 TURNER SALESMAN 7698 1981-09-08 1500
7902 FORD
ANALYST 7566 1981-12-03 3000
Introduction à SQL - N.Camelin
300
0
30
20
30
10
20
10
30
20
38
Exemple de projection ET sélection
SELECT name, hiredate, sal FROM emp WHERE sal>=1500 ;
empno
name
job
mgr
hiredate
sal comm deptno
7369 SMITH
CLERK
7902 1980-12-17 800
7499 ALLEN SALESMAN 7698 1981-02-20 1600 300
7521 WARD SALESMAN 7698 1981-02-22 1250 500
7566 JONES MANAGER 7839 1981-04-02 2975
7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400
7698 BLAKE MANAGER 7839 1981-05-01 2850
7782 CLARK MANAGER 7839 1981-06-09 2450
7788 SCOTT
ANALYST 7566 1982-12-09 3000
7839
KING
PRESIDENT
1981-11-17 5000
7844 TURNER SALESMAN 7698 1981-09-08 1500
0
7876 ADAMS
CLERK
7788 1983-01-12 1100
7900 JAMES
CLERK
7698 1981-12-03 950
7902 FORD
ANALYST 7566 1981-12-03 3000
7934 MILLER
CLERK
7782
1300
Introduction
à SQL1982-01-23
- N.Camelin
20
30
30
20
30
30
10
20
10
30
20
30
20
10
39
Exemple de projection ET sélection
SELECT name, hiredate, sal FROM emp WHERE sal>=1500 ;
name
hiredate
sal
ALLEN
JONES
BLAKE
CLARK
SCOTT
KING
TURNER
FORD
1981-02-20
1981-04-02
1981-05-01
1981-06-09
1982-12-09
1981-11-17
1981-09-08
1981-12-03
1600
2975
2850
2450
3000
5000
1500
3000
Introduction à SQL - N.Camelin
40
La clause SELECT et ses opérateurs
 * : tous les champs de la table
 ALL : retourne toutes les lignes (par défaut)
 DISTINCT : suppression des doublons
 +, -, *, / : opérations mathématiques de base
 || : concaténation de champs de type caractères
 AS : nommer une colonne calculée
Introduction à SQL - N.Camelin
41
Les mots clés DISTINCT et AS
SELECT empno || '_' || name AS id_nom
FROM emp where job = 'ANALYST';
SELECT sal+comm FROM emp ;
? column ?
id_nom
7788_SCOTT
7902_FORD
1900
1750
2650
SELECT DISTINCT job FROM emp ;
job
ANALYST
CLERC
MANAGER
PRESIDENT
SALESMAN
1500
Introduction à SQL - N.Camelin
42
Clause WHERE « simple »
 poser une condition sur les lignes
 syntaxe :
WHERE expression1 OPERATEUR expression2
 Les opérateurs logiques
• comparaison : 6 opérateurs : =, <>, <, >, <=, >=
• étendue : BETWEEN valeur1 AND valeur2
• appartenance : IN (ensemble_valeurs)
• correspondance à un modèle : LIKE modele
• IS NULL
Introduction à SQL - N.Camelin
43
Exemple de clause WHERE - étendue
Qui a été embauché de août 1981 à août 1982?
SELECT * FROM emp WHERE hiredate BETWEEN '1981-08-01' AND '1982-08-01' ;
empno name
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
job
mgr
hiredate
sal
comm deptno
CLERK
7902 1980-12-17
800
20
SALESMAN
7698 1981-02-20 1600
300
30
SALESMAN
7698 1981-02-22 1250
500
30
MANAGER
7839 1981-04-02 2975
20
SALESMAN
7698 1981-09-28 1250 1400
30
MANAGER
7839 1981-05-01 2850
30
MANAGER
7839 1981-06-09 2451
10
ANALYST
7566 1982-12-09 3000
20
PRESIDENT
1981-11-17 5000
10
SALESMAN
7698 1981-09-08 1500
0
30
CLERK
7788 1983-01-12 1100
20
CLERK
7698 1981-12-03
950
30
ANALYST
7566 1981-12-03 3000
20
CLERK
13000
10
Introduction7782
à SQL - 1982-01-23
N.Camelin
44
Exemple de clause WHERE - étendue
Qui a été embauché de août 1981 à août 1982?
SELECT * FROM emp WHERE hiredate BETWEEN '1981-08-01' AND '1982-08-01' ;
empno name
7654 MARTIN
7839 KING
7844 TURNER
7900 JAMES
7902 FORD
7934 MILLER
job
mgr
hiredate
sal
comm deptno
SALESMAN
7698 1981-09-28 1250 1400
30
PRESIDENT
1981-11-17 5000
10
SALESMAN
7698 1981-09-08 1500
0
30
CLERK
7698 1981-12-03
950
30
ANALYST
7566 1981-12-03 3000
20
CLERK
7782 1982-01-23 13000
10
Introduction à SQL - N.Camelin
45
Exemple de clause WHERE - appartenance
Qui est vendeur ou employé de bureau?
SELECT * FROM emp WHERE job IN ('SALESMAN','CLERK') ;
empno name
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
job
CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
CLERK
ANALYST
CLERK
mgr
hiredate
sal
comm deptno
7902 1980-12-17
800
20
7698 1981-02-20 1600
300
30
7698 1981-02-22 1250
500
30
7839 1981-04-02 2975
20
7698 1981-09-28 1250 1400
30
7839 1981-05-01 2850
30
7839 1981-06-09 2451
10
7566 1982-12-09 3000
20
1981-11-17 5000
10
7698 1981-09-08 1500
0
30
7788 1983-01-12 1100
20
7698 1981-12-03
950
30
7566 1981-12-03 3000
20
7782 1982-01-23 13000
10
Introduction à SQL - N.Camelin
46
Exemple de clause WHERE - appartenance
Qui est vendeur ou employé de bureau?
SELECT * FROM emp WHERE job IN ('SALESMAN','CLERK') ;
empno name
7369 SMITH
7499 ALLEN
7521 WARD
7654 MARTIN
7844 TURNER
7876 ADAMS
7900 JAMES
job
CLERK
SALESMAN
SALESMAN
SALESMAN
SALESMAN
CLERK
CLERK
mgr
hiredate
sal
comm deptno
7902 1980-12-17
800
20
7698 1981-02-20 1600
300
30
7698 1981-02-22 1250
500
30
7698 1981-09-28 1250 1400
30
7698 1981-09-08 1500
0
30
7788 1983-01-12 1100
20
7698 1981-12-03
950
30
 Attention à respecter la casse !
SELECT * FROM emp WHERE job IN ('salesman','clerk') ;
empno name
job
mgr
hiredate
Introduction à SQL - N.Camelin
sal
comm deptno
47
Exemple de clause WHERE –
correspondance à un modèle
Quel employé a son nom commençant par la lettre A ?
SELECT * FROM emp WHERE name LIKE 'A%' ;
empno name
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
job
mgr
hiredate
sal
comm deptno
CLERK
7902 1980-12-17
800
20
SALESMAN
7698 1981-02-20 1600
300
30
SALESMAN
7698 1981-02-22 1250
500
30
MANAGER
7839 1981-04-02 2975
20
SALESMAN
7698 1981-09-28 1250 1400
30
MANAGER
7839 1981-05-01 2850
30
MANAGER
7839 1981-06-09 2451
10
ANALYST
7566 1982-12-09 3000
20
PRESIDENT
1981-11-17 5000
10
SALESMAN
7698 1981-09-08 1500
0
30
CLERK
7788 1983-01-12 1100
20
CLERK
7698 1981-12-03
950
30
ANALYST
7566 1981-12-03 3000
20
CLERK
1982-01-23 13000
10
Introduction à 7782
SQL - N.Camelin
48
Exemple de clause WHERE –
correspondance à un modèle
Quel employé a son nom commençant par la lettre A ?
SELECT * FROM emp WHERE name LIKE 'A%' ;
empno name
job
mgr
hiredate
sal
comm deptno
7499 ALLEN SALESMAN
7698 1981-02-20 1600
300
30
7876 ADAMS
CLERK
7788 1983-01-12 1100
20
 % correspond à un ensemble de caractères
• 'A%' match 'ALLEN'
• '%O%' match 'FORD', 'SCOTT' et 'JONES'
• '%K' match 'CLARK' mais pas 'BLAKE'
 _ correspond à un seul caractère
• 'K_NG' match 'KING' et 'KONG'
• '_LA%' match 'BLAKE' et 'CLARK'
Introduction à SQL - N.Camelin
49
Opérateurs de négation et de conjonction
 négation d'une condition : NOT
• exclure des enregistrements d'un ensemble de résultats
• NOT BETWEEN, NOT IN, NOT LIKE, IS NOT NULL
 conditions multiples : OR et AND
• expression1 AND expression2
– vrai si expression1 ET expression2 à TRUE
• expression1 OR expression2
– vrai si expression1 OU expression2 à TRUE
Introduction à SQL - N.Camelin
50
Opérateurs ensemblistes
 combiner le résultat de 2 requêtes ou plus
• UNION: mettre en communs tous les n-uplets
• INTERSECT : identifier les n-uplets similaires
• EXCEPT : identifier les n-uplets appartenant à un ensemble mais
pas à l'autre
 syntaxe :
requête_1 { UNION | INTERSECT | EXCEPT } [ALL] requête_2 [...]
• même schéma pour requête_1 et requête_2
 !! DISTINCT par défaut → ALL
 possibilité de chainer plusieurs opérateurs : évaluer de gauche à droite
Introduction à SQL - N.Camelin
51
Exemple de l'opérateur EXCEPT
SELECT * FROM emp WHERE job LIKE 'SALESMAN'
empno name
mgr
hiredate
sal
comm deptno
job
7499 ALLEN
SALESMAN
7698
1981-02-20
1600
300
30
7521 WARD
SALESMAN
7698
1981-02-22
1250
500
30
7654 MARTIN SALESMAN
7698
1981-09-28
1250
1400
30
7844 TURNER SALESMAN
7698
1981-09-08
1500
0
30
SELECT * FROM emp WHERE sal < 1300
empno name
mgr
hiredate
sal
comm deptno
job
7369 SMITH
CLERK
7902
1980-12-17
800
20
7521 WARD SALESMAN
7698
1981-02-22 1250
500
30
7654 MARTIN SALESMAN
7698
1981-09-28 1250
1400
30
7876 ADAMS
CLERK
7788
1983-01-12 1100
20
7900 JAMES
CLERK
7698
1981-12-03
950
30
SELECT * FROM emp WHERE job LIKE 'SALESMAN'
EXCEPT
SELECT * FROM emp WHERE sal < 1300
empno name
mgr
hiredate
sal
comm deptno
job
7499 ALLEN SALESMAN
7698
1981-02-20 1600
300
30
7844 TURNER SALESMAN
7698
1981-09-08 1500
0
30
Introduction à SQL - N.Camelin
52
Opérateurs arithmétiques
 4 opérateurs arithmétiques
• addition(+), soustraction(-), multiplication(*) et division (/)
• attention à la valeur NULL !
SELECT * FROM emp WHERE sal + comm >500;
empno name
7499 ALLEN
7521 WARD
7654 MARTIN
7844 TURNER
job
mgr
hiredate
sal
comm deptno
SALESMAN
7698 1981-02-20 1600
300
30
SALESMAN
7698 1981-02-22 1250
500
30
SALESMAN
7698 1981-09-28 1250 1400
30
SALESMAN
7698 1981-09-08 1500
0
30
Introduction à SQL - N.Camelin
53
Ordre d'évaluation des conditions
Ordre d'évaluation
1
2
3
4
5
6
7
Type d'opérateur
signe positif (+), signe négatif (-)
multiplication (*), division(/)
addition(+), soustraction(-)
BETWEEN, IN, LIKE, IS NULL, =, <> ,< ,> ,<= ,>=
NOT
AND
OR
Introduction à SQL - N.Camelin
54
Exemple de requête à conditions multiples
SELECT * FROM emp WHERE job NOT LIKE '_A%' OR mgr = '7698' AND sal>1300;
empno name
job
mgr
hiredate
sal
comm deptno
7369 SMITH
CLERK
7902 1980-12-17
800
20
7499 ALLEN
SALESMAN
7698 1981-02-20
1600
300
30
7521 WARD
SALESMAN
7698 1981-02-22
1250
500
30
7566 JONES
MANAGER
7839 1981-04-02
2975
20
7654 MARTIN SALESMAN
7698 1981-09-28
1250
1400
30
7698 BLAKE
MANAGER
7839 1981-05-01
2850
30
7782 CLARK
MANAGER
7839 1981-06-09
2451
10
7788 SCOTT
ANALYST
7566 1982-12-09
3000
20
7839
KING
PRESIDENT
1981-11-17
5000
10
7844 TURNER SALESMAN
7698 1981-09-08
1500
0
30
7876 ADAMS
CLERK
7788 1983-01-12
1100
20
7900 JAMES
CLERK
7698 1981-12-03
950
30
7902 FORD
ANALYST
7566 1981-12-03
3000
20
7934 MILLER
CLERK
7782 1982-01-23
1300
10
?
empno name
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839
KING
7844 TURNER
7876 ADAMS
Introduction
7900
JAMES à
7902 FORD
7934 MILLER
?
job
mgr
hiredate
sal
comm deptno
CLERK
7902 1980-12-17
800
20
SALESMAN
7698 1981-02-20
1600
300
30
SALESMAN
7698 1981-02-22
1250
500
30
MANAGER
7839 1981-04-02
2975
20
SALESMAN
7698 1981-09-28
1250
1400
30
MANAGER
7839 1981-05-01
2850
30
MANAGER
7839 1981-06-09
2451
10
ANALYST
7566 1982-12-09
3000
20
PRESIDENT
1981-11-17
5000
10
SALESMAN
7698 1981-09-08
1500
0
30
CLERK
7788 1983-01-12
1100
20
SQL
- N.Camelin7698 1981-12-03
CLERK
950
30
ANALYST
7566 1981-12-03
3000
20
CLERK
7782 1982-01-23
1300
10
55
Fonctions d'agrégation
 COUNT
• compte du nombre de valeurs non NULL d'une colonne
 SUM
• total des valeurs d'une colonne pour un nombre de lignes
donné
 AVG
• moyenne d'un groupe de lignes
 MIN et MAX
• retourne la valeur minimale/maximale d'une colonne dans un
groupe de lignes
!! une fonction d'aggregation ne renvoie qu'une seule valeur !!
Introduction à SQL - N.Camelin
56
Exemples de fonctions d'agrégation
SELECT COUNT (DISTINCT job) FROM emp ;
count
5
SELECT AVG (comm) FROM emp ;
avg
550
SELECT name, MAX (sal) FROM emp ;
!! ne fonctionne pas !!
Introduction à SQL - N.Camelin
57
Quelques fonctions de caractères
 remplacement de caractères
• TRANSLATE(chaine, val1,val2),
REPLACE(chaine,char1,char2)
 modification de la casse
• UPPER(chaine), LOWER(chaine)
 extraction de sous-chaine
• SUBSTR(chaine, départ, longueur)
 longueur de chaine
• LENGTH(chaine)
Introduction à SQL - N.Camelin
58
Principales fonctions de caractères
SELECT name, SUBSTR(job,1,3)
FROM emp WHERE sal>2000;
name
job
JONES
MAN
BLAKE
MAN
CLARK
MAN
SCOTT
ANA
KING
PRE
FORD
ANA
SELECT DISTINCT lower(translate(job,'A','K'))
FROM emp ;
job
knklyst
clerc
mknkger
president
sklesmkn
SELECT replace(job,'s','z') FROM emp ;
!! ne fonctionne pas !!
Introduction à SQL - N.Camelin
59
Et encore d'autres fonctions...
 ex. de fonctions mathématiques :
• valeur absolue ABS, arrondi ROUND, racine carré SQRT,
puissance POWER
• SIN(x), COS(x), TAN(x), EXP(x), LOG(x)
 ex. de fonctions de conversions :
• caractères en numérique : TO_NUMBER
• numérique en caractère : STR
 fonctions de dates
 fonctions système
 ...
Introduction à SQL - N.Camelin
60