CM1 : Introduction, rappels SQL

1
BASES DE DONNÉES ET
PROGRAMMATION WEB
Frey Gabriel
Licence 3 Informatique
Contenu général
2
— 
— 
— 
Bases de données I (licence 2 Informatique)
Bases de données et programmation web (licence 3
Informatique)
Banques de données multimédia, Entrepôts de
données et informatique décisionnelle, Fouilles de
données, Fouilles de données réparties, …
Organisation du module
3
¨ 
Séances
CM : 26h
¤  TD : 16h
¤  TP : 24h (projet)
¤  Fichiers : http://dpt-info.u-strasbg.fr/~g.frey/BDDPW
¤  Moodle : BDDPW
¤ 
¨ 
Evaluation
note 1 : épreuve écrite (40mn - 1h10), coef. 2/10
¤  note 2 : épreuve pratique, coef. 3/10
¤  note 3 : épreuve pratique, coef. 2/10
¤  note 4 : épreuve écrite (55mn - 1h45), coef. 3/10
¤ 
4
Rappels : Concepts de Bases de
Données
Système de Gestion de Fichiers
5
Programme 1 Données Programme 2 Système de gestion de fichiers Fichier 1 Données Fichier 2 Programme 3 Données Système de Gestion de Fichiers
6
Les données sont stockées dans des fichiers.
Inconvénients pouvant se produire lors du stockage d’une
grande masse de données, accessible par différents
utilisateurs et applications :
—  différents formats de fichier, dépendant des programmes
et généralement faiblement structurés,
—  difficulté d’accès à l’information (index),
—  pas de vérification sur la cohérence des données
(redondances, erreurs, …),
—  pas de contrôle des accès concurrents.
Approche Base de Données
7
Programme 1 Données SGBD Programme 2 Données Requêtes Transactions Programme 3 Données Bases de données Définition
8
— 
Base de données (BD)
Une base de données est un ensemble d’informations de grande taille,
structuré, mémorisé sur un support permanent.
— 
Système de gestion de Bases de Données (SGBD)
Un système de gestion de bases de données est un outil informatique
permettant aux utilisateurs de structurer, d’insérer, de modifier, de
rechercher de manière efficace des données spécifiques au sein d’une
grande quantité d’informations stockées sur mémoires secondaires
partagées de manière transparente par plusieurs utilisateurs.
Fonctions d’un SGBD
9
1. 
Persistance
¤ 
2. 
Stockage des données sur disque
Langage de requête sur la base
¤ 
¤ 
Les requêtes doivent être simples, déclaratives, optimisées avant
l’exécution
SQL (Structured Query Langage), OQL (Object Query Langage),
Xquery (XML Query Langage)
Fonctions d’un SGBD
10
Partage des données
3. 
Chaque utilisateur doit avoir l’impression d’être seul à accéder aux
données
— 
Ø 
Notion de transaction
Cohérence des mises à jour d’un utilisateur
Cohérence collective : sérialisabilité
— 
— 
Fiabilité des données
4. 
Vérification de contrainte d’intégrité
Atomicité des transactions : transaction complètement effectuée ou pas
du tout
Résistance aux pannes
— 
— 
— 
— 
— 
— 
En cas de panne mémoire: restauration automatique des dernières
transactions validées avant la panne
En cas de panne disque : restauration d’une sauvegarde, journal
Mécanisme de réplication des données (mirroring)
Fonctions d’un SGBD
11
Sécurité des données
5. 
Droits des utilisateurs sur la manipulation des données
— 
— 
— 
— 
Notion d’autorisation
Notion de groupe d’utilisateur
Granularité des autorisations
Gestion des disques
6. 
Répartition des données
Optimisation des performances
— 
— 
— 
— 
— 
— 
Index, hachage, bitmap
Regroupement des données
Optimisation des requêtes
Cache mémoire
Fonctions d’un SGBD
12
7. 
Indépendance logique/physique
¤ 
¤ 
Organisation physique de la BD transparente pour le
développeur d’application
Possibilité de changer la structure physique des données sans
changer le code de l’application
SGBD transactionnel
Exemple de transaction :
Begin
//
CEpargne
CCourant
Commit
//
début de la transaction
= CEpargne – 1000
= CCourant + 1000
fin de la transaction
Begin
CEpargne = CEpargne – 1000
>>>> panne
CCourant = CCourant + 1000
Commit T1
⇒ annuler le débit !
⇒ récupération de l’état précédent (panne ?)
13
Transaction : gestion des accès concurrents
14
Utilisateur 1
Lire(CCourant);
if (CCourant >= 100)
{
CCourant =
CCourant – 100;
Ecrire(CCourant);
}
¨ 
Quels sont les risques ?
Utilisateur 2
Lire(CCourant);
if (CCourant >= 100)
{
CCourant =
CCourant – 100;
Ecrire(CCourant);
}
Transaction : propriétés ACID
¨ 
¨ 
¨ 
¨ 
Atomicité : Une transaction est indivisible. Elle est soit complètement exécutée soit
pas du tout (unité atomique de traitement)
Cohérence : une transaction doit effectuer une transition d’un état cohérent de la
base à un autre état cohérent (par ex: pas de violation de contrainte d’intégrité). La
cohérence peut être non respectée pendant l'exécution d'une transaction. En cas
d’échec de la transaction, la base doit retourner dans l’état cohérent initial
Isolation : le résultat d’un ensemble de transactions concurrentes et validées
correspond à une exécution successive des mêmes transactions (pas d’inférence
entre les transactions)
Durabilité : après la fin d'une transaction, les mises à jour sont définitives même en
cas de problèmes matériels (mécanisme de reprise en cas de panne)
15
Architecture SGBD
Utilisateur 1 Utilisateur 2 Vue utilisateur 1 Utilisateur 3 Vue utilisateur 2 …… Utilisateur N Vue utilisateur N Schéma externe Vue conceptuelle Schéma logique Vue interne Schéma interne SGBD 16
Architecture
17
•  Niveau externe : Vues, Environnement de programmation (intégration avec un langage
de programmation), Interfaces conviviales, Outils d’aide (ex : conception de schémas)
•  Niveau logique : Définition de la structure de données, Langages de description des
données (LDD), Consultation et mise à jour des données : Langages de Requêtes (LR) et
Langage de Manipulation de Données (LMD), Gestion de la confidentialité (sécurité),
Intégrité des données
•  Niveau physique : Gestion sur mémoire secondaire (fichiers) des données, du schéma,
des index, Partage des données et gestion de la concurrence d’accès, Reprise sur
pannes (fiabilité), Distribution des données et interopérabilité (accès réseaux).
Types d’utilisateur :
-  Utilisateur de base
-  Concepteur d’application
-  Administrateur de la base
Modèle de données
18
Modèle conceptuel
—  Pour la conception d’applications
—  Merise, Modèle entité-association, UML, …
Modèle logique
—  Pour la définition de BD supportées par un SGBD
—  Modèle hiérarchique, réseau, relationnel, objet, …
Modèle physique
—  Implémentation du SGBD: fichier simple, base de faits, XML, …
—  Organisation et stockage des données : index, …
19
MODÈLE LOGIQUE DE
DONNÉE RELATIONNEL
Modèle Logique de Donnée
20
Définition : le modèle logique décrit la structure de données
Objectifs :
— 
— 
— 
— 
Éviter les incohérences dans les données :
Une personne n’a qu’une date de naissance, le prix d’un produit est unique, ….
Éviter la redondance d’information :
La même information est stockée dans différentes relations
Anomalies: insertion, suppression, modification
Éviter les valeurs nulles :
Difficiles à interpréter : inconnu, connu mais non disponible, inapplicable
Rend les jointures difficiles à spécifier
Éviter les jointures inutiles :
Améliorer les performances : la jointure est une opération coûteuse
Modèle Relationnel
21
¨ 
Modèle de niveau logique simple défini par Edgar
Frank Codd en 1970 (prix Turing en 1986)
¤  une
relation est une table à deux dimensions
¤  n-uplet ó ligne de la table
¤  attribut ó colonne de la table
SGBD Relationnel
22
Oracle database (≈47%), MySQL
¨  Microsoft SQL Server (≈17%), DB2 (≈21%)
¨  PostgreSQL, Firebird, Ingres
¨  SQLite
¨ 
23
ALTERNATIVE AUX SGBD
RELATIONNELS
XML
¨ 
XML (eXtensible Markup Language)
¤ 
Les balises définissent la structure et la sémantique
n 
¤ 
¤ 
Présentation à part (XSL)
Représentation de données semi-structurées
n 
¤ 
¨ 
conformité optionnelle à un schéma
Facilite le traitement informatique et humain
Format normalisé d’échange
¤ 
¨ 
Facilite l’interprétation des données (par programme ou humain)
couplage faible entre les données et les systèmes
Schémas
¤ 
DTD, XSD, XML schéma
24
XML
<?xml version="1.0" encoding="ISO-8859-1" standalone="yes"?>
<!-- Catalogue de la pépinière Plein de Foin -->
<!DOCTYPE Catalogue [
<!ELEMENT Catalogue (Article)+>
<!ELEMENT Article (noArticle,description,prixUnitaire)>
<!ELEMENT noArticle (#PCDATA)>
<!ELEMENT description (#PCDATA)>
<!ELEMENT prixUnitaire (#PCDATA)>]>
<Catalogue>
<Article>
<noArticle>10</noArticle>
<description>Cèdre en boule</description>
<prixUnitaire>10.99</prixUnitaire>
</Article>
<Article>
<noArticle>20</noArticle>
<Catalogue>
<description>Sapin</description>
<prixUnitaire>12.99</prixUnitaire>
</Article>
…
<Article>
<Article>
</Catalogue>
<noArticle>
<description>
10
Cèdre en boule
<prixUnitaire>
10,99
<noArticle>
<description>
20
Sapin
...
<prixUnitaire>
12,99
25
OLTP
¨ 
On-Line Transaction Processing
¤  Requêtes
simples, rapides, fréquentes
¤  Mise à jour des n-uplets assez fréquentes
¤  Nombre de n-uplets relativement important
26
OLAP
¨ 
On-Line Application Processing
¤  Requête
analytique
¤  Très grand ensemble de données
¤  Peu de requêtes mais complexes et longues
¤  Pas nécessaire d’avoir une base de données totalement
à jour
27
Architecture d'entrepôt de données
BD
opérationnelle
(OLTP)
BD
opérationnelle
(OLTP)
Extraction :
épuration,
filtrage,
synthèse,
transformation,
fusion
Entrepôt de données
(« data wharehouse »)
Analyse
(OLAP),
prospection
Autre
source de
données
Chaîne de traitement : extraction, transformations, analyses
28
Schéma en étoile OLAP
<<Table>>
Client
<<PK>> noClient
nomVille
<<Table>>
Ventes
noClient
noArticle
dateVente
montant
<<Table>>
Article
<<PK>> noArticle
catégorie
Table de faits
Tables de dimensions
<<Table>>
Période
<<PK>> dateVente
mois
trimestre
année
29
Extensibilité
30
¨ 
En cas d’ajout de données, il est possible :
¤  d’augmenter
la taille des tables (extensibilité verticale)
¤  d’augmenter le nombre de serveurs de données et de
répartir les tables sur ces serveurs (extensibilité
horizontale)
Bases de données réparties
31
¨ 
¨ 
Bénéfices potentiels
¤  Performance
¤  Fiabilité et disponibilité
¤  Expansion graduelle (scalabilité)
Inconvénients
¤  Complexité accrue
¤  Transactions réparties
¤  Évaluation de requêtes réparties
¤  Répartition du dictionnaire de données
¤  Coût important
n  conception, administration, …
Théorème CAP
32
¨ 
¨ 
¨ 
Trois propriétés d’un système d’information distribué : cohérence
(Consistency), disponibilité (Availability) et distributivité (Partition)
Un système ne peut que garantir deux de ces propriétés à la fois
Lorsqu’un système d’information réparti s’agrandit, il doit choisir
entre la cohérence et la disponibilité :
¤ 
¤ 
Privilégie la cohérence : SGBD relationnel
Privilégie la disponibilité : NoSQL
NoSQL
33
¨ 
¨ 
¨ 
¨ 
Not Only SQL
Bases de données généralement non relationnelles
Schéma non normalisé, non fixé, pas forcément de jointure
Transaction : propriétés ACID pas nécessairement respectées
¤ 
« cohérent au bout d’un certain nombre d’évènements » : BASE
(Basically Available, Soft state, Eventual consistency)
¨ 
Exemple de solutions NoSQL : BigTable (Google),
Cassandra (Facebook, Reddit, …), couchDB, mongoDB
Types de base NoSQL
34
Key-Value data store
¨  Document based store
¨  Column based store
¨  Graph based store
¨ 
35
SQL
SQL
36
¨ 
¨ 
¨ 
¨ 
¨ 
¨ 
Structured Query Langage
Standard établi pour les SGBD relationnel
Dérivé de l’algèbre relationnel
SQL est un langage où l’utilisateur décrit le résultat à
obtenir (Quoi ?)
SQL est non-procédural : le SGBD détermine
l’enchaînement des traitements à effectuer afin d’obtenir le
résultat (Comment)
Avantage d’être un langage déclaratif : pas de boucles
infinies, pas d’explosion mémoire
Standard SQL
37
— 
— 
SQL est un standard ANSI/ISO
Plusieurs mises à jour du langage :
SQL1 : première version standardisée (1986,1987)
—  SQL2 : mise à jour majeure (type date, opérations
ensemblistes, …)
—  SQL3 : déclencheurs, types composites, fonctionnalités
orientées objets, …
—  SQL:2003, SQL:2006, SQL:2008, SQL:2011
— 
— 
Variations par rapport au SQL standard propre à
chaque SGBD
Langages de requêtes
38
— 
Langage de Définition de Données (LDD) :
— 
— 
— 
— 
— 
Langage de Contrôle de données (LCD) :
— 
— 
— 
Définition de relations, contraintes d’intégrité, …
Définition de vues
Définition d’index
UDF (User Defined Function), Procédure
Définitions des droits : GRANT, REVOKE
Contrôle de transaction : BEGIN, COMMIT, ROLLBACK
Langage de Manipulation de Données (LMD) :
— 
— 
Interrogation : SELECT
Définition, modification, suppression : INSERT, UPDATE, DELETE
39
Dictionnaire de données
— 
Tables contenant les informations sur les objets de la base (métadonnées)
— 
Normalisé dans le standard SQL :
— 
— 
— 
Sous Oracle, les vues du dictionnaires ont des noms de la forme :
— 
— 
— 
— 
— 
DEFINITION_SCHEMA (Tables, non directement accessibles)
INFORMATION_SCHEMA (Vues)
USER_<Objet> : objets appartenant à l’utilisateur courant
ALL_<Objet> : objets accessibles à l’utilisateur courant
DBA_<Objet> : tous les objets de la base (vue réservée à l’administrateur)
Exemples de tables du dictionnaires : DICTIONARY (DICT), USER_TABLES
(TABS), USER_TAB_COLUMNS (COLS), USER_USERS, …
Sous PostgreSQL : INFORMATION_SCHEMA (conforme SQL standard)
— 
— 
Liste des tables : SELECT * FROM INFORMATION_SCHEMA.TABLES;
ou \dt
40
Type SQL : Numérique
— 
INTEGER (ou INT)
— 
— 
Entier (précision non standardisée)
SMALLINT
— 
— 
exact
Petit entier (précision non standardisée)
NUMERIC(p, c) (ou DECIMAL(p, c) ou DEC(p, c)
— 
Nombre décimal avec p chiffres au total, dont c chiffres après la
virgule
Type SQL: Numérique Réel
41
¤  REAL
Point flottant (précision non standardisée)
n  Exemples : 3.27E-4, 24E5
n 
¤  DOUBLE
PRECISION
Point flottant à double précision (non standardisée)
n  Exemples : 3.27265378426E-4, 24E12
n 
Type SQL : Chaîne de caractères
42
— 
Chaîne de caractères (VARYING et NATIONAL : SQL2 intermédiaire)
— 
CHARACTER(n)
— 
— 
CHARACTER VARYING (n)
— 
— 
— 
Ensemble de caractères alternatif (autres langues)
NATIONAL CHARACTER VARYING(n)
— 
— 
Taille variable (max de n caractères)
NATIONAL CHARACTER(n)
— 
— 
Chaîne de caractère de taille fixe égale à n
Taille variable
TEXT
Sous Oracle :
—  CHARACTER ó CHAR
—  CHARACTER VARYING ó VARCHAR2
—  NATIONAL CHARACTER ó NCHAR
—  NATIONAL CHARACTER VARYING ó NVARCHAR2
Type SQL : Date
43
— 
Date et temps
— 
DATE
— 
— 
— 
TIME[(p)]
— 
— 
— 
— 
DATE + TIME
Exemple : TIMESTAMP '1998-08-25 14:04:32.25'
INTERVAL
— 
— 
heure (2 chiffres), minutes (2 chiffres), secondes (2 + p chiffres)
Exemple : TIME '14:04:32.25'
TIMESTAMP[(p)]
— 
— 
année (quatre chiffres), mois (2 chiffres) et jour (2 chiffres)
Exemple : DATE '1998-08-25'
Représente un intervalle de temps.
Sous Oracle :
—  DATE : précision jusqu’à la seconde, format par défaut dépend de la configuration du
SGBD (sous Codd : ‘DD-MON-YY’)
—  TIMESTAMP : précision jusqu’à la fraction de seconde
—  TIMESTAMP, TIMESTAMP WITH TIME ZONE, INTERVAL YEAR TO MONTH, INTERVAL DAY
TO SECOND
Type : autres
44
¨ 
Logique à 3 valeurs de vérité : TRUE, FALSE, UNKNOWN
¤ 
¤ 
¤ 
NULL = NULL ?
NULL OR TRUE ?
ROW(1, NULL) = ROW (1, NULL) ?
n 
¨ 
Binaire long (SQL3)
¤ 
¨ 
BINARY LARGE OBJECT (n) (BLOB(n))
Longue chaîne de caractère (SQL3)
¤ 
¤ 
¨ 
IS DISTINCT FROM
CHARACTER LARGE OBJECT (n) (CLOB(n))
NATIONAL CHARACTER LARGE OBJECT (n) (NCLOB(n))
Sous Oracle :
¤ 
¤ 
Pas de type booléen (NUMBER(1) avec contraintes)
BLOB, CLOB, NCLOB disponible (4GB),BFILE (External LOB)
Tableaux
45
Type tableau:
Type[]
Constante tableau:
{val1, val2, val3, …, valn}
Accès:
tab[n] (premier élément a comme indice 1)
Slice:
tab[start:end]
Les tableaux peuvent être concaténés pour obtenir un nouveau tableau
Les fonctions IN, NOT IN, ANY, ALL peuvent être utilisées pour vérifier si un élément
appartient à un tableau
Sous Oracle : VARRAY
Type personnalisé
46
CREATE TYPE rat AS (
p
int,
q
int
);
CREATE TABLE tabRat ( n rat );
INSERT INTO tabRat VALUES ( ROW(1,2) );
SELECT (n).p FROM tabRat;
SELECT (A.n::rat).p FROM (SELECT n FROM tabRat ) AS A;
Rappel SQL
47
CREATE TABLE (
NomCol1
TYPE,
…
NomColn
TYPE [<contraintes colonnes>],
PRIMARY KEY
(Col1, …),
FOREIGN KEY
(Col1, …)
REFERENCES tableEtrangères(ColEt1, …),
[<contraintes lignes>]
);
Mise à jour des données
¨ 
¨ 
¨ 
INSERT : insertion de lignes dans une table
¤  En précisant les lignes à insérer
¤  Les lignes à insérer sont le résultat d’une requête
UPDATE : modification de lignes d’une table
¤  En précisant les lignes à modifier
¤  Les lignes à modifier sont le résultat d’une requête
DELETE : suppression de lignes dans une table
¤  Suppression de toutes les lignes
¤  Suppression des lignes vérifiant une condition de sélection
48
INSERT
INSERT INTO <nom table> [(<nom de colonne>+)]
{ VALUES (<CONSTANTE>+) |
<commande de recherche> };
¨ 
¨ 
¨ 
Généralement dans une table
Si la liste des colonnes n’est pas indiquée, les valeurs doivent être fournies
dans l’ordre de déclaration des colonnes de la table
Les attributs pour lesquels aucune valeur n’est spécifiée auront comme valeur
NULL (l’insertion sera refusée si la valeur NULL n’est pas une valeur valide
pour ces attributs)
49
Exemples
INSERT INTO dpt
VALUES (5, 'Ressource humaine', 15001, 115423);
INSERT INTO dpt (deptno, dnom)
VALUES (6, 'Développement');
INSERT INTO
emp (empno, enom, eprenom, adresse, mgr, deptno)
( SELECT empno, enom, eprenom, adresse, mgr, deptno FROM stagiaire
WHERE duree > 12 );
INSERT ALL
WHEN order_total < 1000000
THEN INTO small_orders
WHEN order_total > 1000000 AND order_total < 2000000
THEN INTO medium_orders
WHEN order_total > 2000000
THEN INTO large_orders
SELECT order_id, order_total, sales_rep_id, customer_id FROM orders;
50
UPDATE
UPDATE <nom_table>
SET
<nom_de_colonne =
{ WHERE
< <valeur> | NULL
> >+
<condition_de_recherche> };
51
Exemples
UPDATE emp
SET mgr = 12;
UPDATE emp
SET salaire = salaire + 500
WHERE mgr = 12;
UPDATE emp
SET salaire = 60000, mgr IS NULL
WHERE empno = 12;
UPDATE emp E
SET (salaire,deptno) =
( SELECT salaire, deptno FROM emp M
WHERE M.empno=E.mgr )
RETURNING sum(salaire) INTO :varOracle;
52
DELETE
DELETE FROM <nom table>
[WHERE <condition de recherche>];
Exemple :
DELETE FROM projet;
DELETE FROM emp
WHERE deptno = 10;
53
TRUNCATE, MERGE (SQL standard)
54
¨ 
Supprimer les n-uplets d’une table et désallouer l’espace
mémoire correspondant
¤  TRUNCATE
¨ 
table1;
Les commandes MERGE ou <UPDATE OR INSERT INTO>
permettent d’ajouter à une relation des n-uplets s’ils ne
sont pas déjà présents dans cette relation et les mettre à
jour sinon :
merge into customers(id,name) c using (select id, name from
customers_delta where id > 10) cd on (c.id = cd.id)
when matched then update set name = cd.name
when not matched then insert (id, name) values (cd.id, cd.name);
55
SELECT
Exemples :
SELECT * FROM emp WHERE salaire > 35000 AND deptno BETWEEN 1 AND 5;
SELECT * FROM emp WHERE enom LIKE 'J%';
SELECT
56
Syntaxe
SELECT [DISTINCT] col1 AS alias_col1, …, coln
[INTO variables]
FROM table t1, …, table tn
WHERE … ;
¨ 
¨ 
Opérations ensemblistes sur le résultat des requêtes
SQL :
<req1> UNION| INTERSECT | EXCEPT <req2>
ORACLE :
<req1> UNION| INTERSECT | MINUS <req2>
UNION, INTERSECT, MINUS supprime les doublons (utiliser UNION ALL,
INTERSECT ALL ou MINUS ALL pour les conserver)
57
ORDER BY, LIMIT
¨ 
Tri des n-uplets résultats :
ORDER BY col1, …, col2 [ASC|DESC]
[NULLS FIRST | NULLS LAST]
¨ 
Ne sélectionner que N n-uplets à partir du Kième :
SELECT * FROM table ORDER BY col1
LIMIT n OFFSET k;
¨ 
Il est aussi possible d’utiliser ROWNUM, ROW_NUMBER() ou RANK() qui
contient l’indice du n-uplet courant (Oracle n’accepte que cette solution)
SELECT *
FROM
(SELECT row_number() OVER (ORDER BY col1)
AS rang FROM t order by t desc) AS tab1
WHERE rang <= 3;
58
— 
— 
— 
— 
— 
— 
— 
Jointure
SELECT * FROM dpt, emp WHERE emp.deptno = dpt.deptno;
Syntaxe jointure :
—  plus explicite sur le type de jointure utilisé
—  Extrait les conditions de jointures des critères de restrictions
Jointure naturelle : NATURAL JOIN
Exemple : SELECT * FROM dpt NATURAL JOIN emp;
Au mins une colonne de même nom doit exister dans les deux tables
Jointure naturelle partielle : JOIN … USING (…)
Exemple : SELECT * FROM dpt JOIN emp USING (deptno);
Jointure : JOIN/ INNER JOIN … ON
Exemple : SELECT * FROM emp e1 INNER JOIN emp e2 ON e1.mgr=e2.emp;
Jointure externe : LEFT/RIGHT/FULL OUTER JOIN
Exemple : SELECT * FROM emp LEFT OUTER JOIN dpt ON emp.deptno = dpt.deptno;
Tous les n-uplet de emp seront affichés même si la condition de jointure n’est pas réalisée. Dans ce cas, les
attributs de dpt associés aux valeurs de emp n’ayant pas de correspondance auront comme valeur NULL.
Produit cartésien : CROSS JOIN
Fonctions d’agrégation SQL
59
¨ 
Partition d’une table avec la clause GROUP BY
SELECT deptno, count(empno) AS nbEmp FROM emp
GROUP BY deptno;
empno
name
deptno
7639
SMITH
20
deptno
7521
CLARK
20
20
2
7834
KING
30
30
1
8456
SCOTT
40
40
2
6210
TURNER
40
50
1
9566
WARD
50
nbEmp
—  Colonnes résultats d’une requête GROUP BY, uniquement colonnes de groupage ou
résultats de fonctions d’agrégation
—  Fonctions d’agrégation : AVG, COUNT, MAX, MIN, SUM, …
Fonctions d’agrégation SQL
60
¨ 
Restriction des partitions sélectionnées avec la clause HAVING
SELECT deptno, count(empno) AS nbEmp FROM emp
GROUP BY deptno
HAVING count(empno) > 1;
empno
name
deptno
7639
SMITH
20
deptno
7521
CLARK
20
20
2
7834
KING
30
40
2
8456
SCOTT
40
6210
TURNER
40
9566
WARD
50
nbEmp
—  Attention à bien distinguer les conditions de restriction sur les n-uplets (WHERE …) des
conditions de restriction sur les groupes (HAVING …)
61
¨ 
¨ 
¨ 
Fonctions fenêtres
Les fonctions fenêtres permettent d’ajouter à un enregistrement les résultats d’une
fonction d’agrégation
Ecrire une requête permettant de visualiser pour chaque employé, son nom, sa fonction,
son âge ainsi que l’âge moyen des employés de l’entreprise :
¤  SELECT enom, job, age, avg(age) OVER () FROM emp;
Ecrire une requête permettant de visualiser pour chaque employé, son nom, sa fonction,
son âge ainsi que l’âge moyen des employés de son département :
¤  SELECT enom, job, age,
avg(age) OVER (PARTITION BY deptno) FROM emp;
¤ 
SELECT enom, deptno, age,
rank() OVER (PARTITION BY deptno ORDER BY age DESC)
FROM emp;
62
Sous-requêtes
¨ 
¨ 
Une sous-requête est une requête SQL imbriquée
dans une autre requête SQL
Une sous-requête peut apparaitre juste après :
¤ 
¤ 
¤ 
¤ 
¨ 
SELECT
FROM
WHERE
…
Si cela est possible, toujours préférer l’utilisation de
jointures à celle de requêtes imbriquées
63
Sous-requêtes dans le SELECT
SELECT E.enom, ( SELECT D.dnom
FROM dpt AS D
WHERE E.deptno=D.deptno )
FROM emp AS E;
—  Que se passe t-­‐il si la sous-­‐requête retourne plusieurs résultats ? —  Si possible, désimbriquer la requête
SELECT enom, dnom
FROM emp NATURAL JOIN dpt;
SELECT D.dnom, ( SELECT count(*)
FROM emp AS E
WHERE E.deptno=D.deptno )
FROM dpt AS D;
64
Sous-requête dans le FROM
SELECT E2.enom
FROM ( SELECT *
FROM emp AS E1
WHERE E1.salaire > 2000) AS E2
WHERE E2.salaire < 3000;
SELECT E.enom
FROM
emp AS E
WHERE E.salaire > 2000 AND E.salaire < 3000;
Sous-requête dans le WHERE
65
SELECT enom FROM emp
WHERE empno IN (SELECT mgr FROM emp);
SELECT E1.enom
FROM emp AS E1 JOIN emp AS E2
ON E1.empno = E2.mgr;
Requêtes corrélées
66
¨ 
Sous-requête corrélée à la requête principale
SELECT E1.empno, E1.enom
FROM emp AS E1
WHERE E1.salaire > (SELECT E2.salaire
FROM emp AS E2
WHERE E1.mgr = E2.empno);
SELECT E1.enom
FROM emp AS E1 JOIN emp AS E2
ON E1.mgr = E2.empno
WHERE E1.salaire > E2.salaire;
—  Il n’est pas forcément facile de désimbriquer toutes les requêtes
SELECT E1.empno, E1.enom
FROM emp AS E1
WHERE E1.salaire > (SELECT max(E2.salaire)
FROM emp AS E2
WHERE E2.mgr = E1.empno);
Tests d’existence
67
¨ 
EXISTS / NOT EXISTS
SELECT dnom FROM dpt
WHERE EXISTS (SELECT * FROM emp
WHERE emp.deptno = dpt.deptno)
SELECT dnom FROM dpt NATURAL JOIN emp;
68
Quantificateurs
— 
ALL : le résultat de la comparaison d’une valeur au résultat d’une sous-requête doit être vérifié pour tous les
n-uplets résultat d’une sous-requête.
SELECT empno, enom FROM emp e1
WHERE e1.salaire > ALL
( SELECT e2.salaire FROM emp e2
WHERE e2.mgr = e1.empno );
— 
SOME/ANY : le résultat de la comparaison d’une valeur au résultat d’une sous-requête doit être vérifié pour
au moins un des n-uplets résultat d’une sous-requête.
SELECT empno, enom FROM emp e1
WHERE e1.salaire > ANY
( SELECT e2.salaire FROM emp e2
WHERE e2.mgr = e1.empno );
¨ 
Transformer les requêtes imbriquées en jointures : ¤ 
¤ 
Facile pour les tests d’existences : IN, EXISTS, SOME Difficile pour les quantificateurs universels : NOT IN, NOT EXISTS, ALL Requête monotone
69
¨ 
¨ 
Une requête est monotone si lorsqu’on ajoute des n-uplets aux tables
en entrée, le nombre de n-uplets de la table résultat ne diminue pas
Parmi les opérations suivantes, lesquelles sont monotones ?
SELECT … FROM … WHERE …
¤  … JOIN …
¤  UNION, INTERSECT, MINUS
¤ 
Requête monotone
70
Emp
Travaille
Projet
¨ 
¨ 
¨ 
¨ 
(empno, enom, salaire, job, deptno)
(empno, projno)
(projno, pnom, deptno)
Les requêtes suivantes sont-elles monotones ?
¤  les employés qui travaillent sur un projet du département 10 mais pas sur
un projet du département 50
¤  les employés dont le métier est analyste et qui n’ont pas le salaire le plus
important de leur département
Les requêtes monotones peuvent être désimbriquées
Les requêtes monotones peuvent donc être résolues par un simple
SELECT
… FROM … WHERE …
Pour les requêtes qui ne sont pas monotones, il est nécessaire d’utiliser quelque
chose en plus : MINUS, NOT IN, ALL, partitionnement, …
Requête monotone
71
¨ 
Les requêtes suivantes sont-elles monotones ?
¤ 
les employés qui travaillent sur un projet du département 10
¤ 
les employés qui ne travaillent que sur des projets du département 10
¤ 
Les employés qui travaillent sur tous les projets du département 10
¤ 
Les employés qui ne travaillent sur aucun projet du département 10
¤ 
Les employés qui travaillent sur un projet qui n’est pas du département 10
Désimbriquation de requête utilisant des fonctions d’agrégation
72
SELECT DISTINCT D.deptno, (SELECT count(*)
FROM emp AS E
WHERE E.deptno=D.deptno)
FROM dpt AS D;
SELECT dnom, count(*)
FROM emp NATURAL JOIN dpt
GROUP BY deptno, dnom;
—  Les requêtes sont-elles équivalentes ?
SELECT dnom, count(*)
FROM emp RIGHT OUTER JOIN dpt
GROUP BY deptno, dnom;
Désimbriquation de requête utilisant des fonctions d’agrégation
73
SELECT DISTINCT E.enom FROM emp AS E
WHERE ( SELECT count(projno)
FROM projet AS P
WHERE P.empno=E.empno )
> 5;
SELECT enom
FROM emp NATURAL JOIN projet
GROUP BY empno
HAVING count(projno) > 5;
74
Sous-requête WITH
¨ 
Permet de déclarer une table temporaire (généralement le résultat d’une sousrequête qui n’existera que le temps de la requête) :
WITH temp1 AS (SELECT … FROM … WHERE … ),
…
tempN AS (SELECT … FROM … WHERE … )
SELECT … FROM temp1, …, tempN, … WHERE …;
¨ 
Exemple :
WITH partavg AS
(SELECT AVG(current_qt) AS avg_qt FROM part)
SELECT part_number, (SELECT avg_qt FROM partavg)
FROM part WHERE current_qt > (SELECT avg_qt FROM partavg);
Requêtes récursives (SQL)
75
Parcours d’arborescence/graphe
WITH RECURSIVE Chemin(x,y) AS
(SELECT x,y FROM Arc)
UNION
(SELECT C1.x, C2.y
FROM Chemin C1, Chemin C2
WHERE C1.y = C2.x)
SELECT * FROM Chemin;
Table Arc
x
y
1
3
2
3
3
4
3
5
Table Ch e m in
x
y
1
3
2
3
3
4
3
5
1
4
1
5
2
4
2
5
Requêtes récursives (Oracle)
76
Parcours d’arborescence/graphe
SELECT x, y
FROM Chemin
START WITH x in (1,2,3)
CONNECT BY PRIOR y=x;
Table Arc
x
y
1
3
2
3
3
4
3
5
Table Ch e m in
x
y
1
3
2
3
3
4
3
5
1
4
1
5
2
4
2
5
—  Possibilité d’utiliser l’attribut level pour connaitre la profondeur de récursivité
—  CONNECT BY NOCYCLE: permet d’éviter de boucler
Correspondance de texte
77
¨ 
LIKE
¤ 
¤ 
¨ 
SIMILAR TO permet d’utiliser des expressions régulières (REGEXP_LIKE sous ORACLE)
¤ 
¨ 
'CAT' LIKE 'C%'
'CAT' LIKE '_A_'
'CAT' SIMILAR TO '(C|R)%’ (préférer ~ sous postgreSQL)
Recherche texte dans des documents
¤ 
Il est préférable que le texte des documents soit indexé et que les termes recherchés soit
des lexèmes
¤ 
Exemple sous postgreSQL:
SELECT to_tsvector(document) @@ to_tsquery('running & dog')
FROM document_list;
Préparer une instruction SQL
78
¨ 
¨ 
¨ 
Avant l’exécution proprement dite d’une instruction SQL, les différents plans
d’évaluation possibles sont évalués. Cette opération est relativement couteuse.
Si une requête est exécutée plusieurs fois, il peut être avantageux de « préparer la
requête » : le meilleur de plan de requête ne sera recherché qu’une seule fois.
Exemple:
PREPARE planexec(text,int) AS
SELECT enom, salaire FROM emp
WHERE job=$1 AND salaire>=$2;
EXECUTE planexec('Analyst',3000);
EXECUTE planexec('Clerk',2000);
79
VUE
Vue : définition
80
¨ 
Table virtuelle de la base de données dont le
contenu est défini par une requête
La vue n’existe pas en tant qu’ensemble de données
stockées sur le disque
⇒  seule la requête décrivant la vue est stockée
¨ 
Vue : avantages
81
— 
indépendance logique
exemple:
infoDepartement_vue(idDept, nomDept, Loc, nbEmp, SalTot);
— 
simplification de requêtes
département de plus de 10 employés et dont la masse salariale
est inférieure à 20 K€ ?
— 
isolation des modifications
- - emp sans indiquer le salaire
emp_vue(idEmp, nom, prénom, job, idDept)
— 
sécurité
— 
intégrité des données
Vue : inconvénients
82
¨ 
performance
n  la
¨ 
traduction de la requête peut être longue
restriction des mises à jour
n  possibilité
de mise à jour qu’à partir de vues simples
Vue : syntaxe
83
CREATE [OR REPLACE]
VIEW nomvue [col,…]
AS SELECT …
[WITH CHECK OPTION [CONSTRAINT nom]];
DROP VIEW nomvue;
RENAME ancien_nom TO nouveau_nom;
84
Vue : exemple
CREATE VIEW emp_recherche
AS
SELECT * FROM emp NATURAL JOIN dpt
WHERE dnom='Recherche' ;
SELECT * FROM emp_recherche ;
Vue : mise à jour
85
— 
Conditions
— 
construite sur une seul table
— 
— 
— 
— 
— 
possible sur vue comportant une jointure si les modifications/
suppressions/insertions ne concernent qu’une table de la jointure
pas de GROUP BY
les colonnes résultats doivent être des colonnes réelles de la table
(non calculées)
la vue contient toutes les colonnes NOT NULL de la table
Permet
— 
— 
— 
d’insérer à travers la vue de nouvelles lignes
de supprimer des lignes de la table à partir de la vue
de mettre à jour la table par l’intermédiaire de la vue
Vue : contrôle d’intégrité
86
CHECK OPTION :
cette clause permet d’interdire d’insérer ou de modifier à travers
la vue des n-uplets qui ne satisferaient pas à la définition de la
vue.
Check Option: exemple
87
> CREATE OR REPLACE VIEW empInge AS
SELECT * FROM emp
WHERE job= 'Ingenieur'
WITH CHECK OPTION
CONSTRAINT Check_inge;
> UPDATE empInge
SET job='manager'
WHERE idEmp='1123' ;
> ORA-01402: view WITH CHECK OPTION
clause violation
Vue matérialisée
88
¨ 
¨ 
¨ 
Vue physique d'une table (SNAPSHOT)
Duplication des données
⇒ nécessité de synchroniser les données
⇒ à n’utiliser que pour des requêtes particulièrement
lourdes
La fréquence des mises à jour de la vue matérialisée est
à préciser
Vue matérialisée : syntaxe
89
CREATE MATERIALIZED VIEW <nomVue>
REFRESH <précisions mise à jour>
AS
<requête>;
Les données de la table sont copiées dans la vue matérialisée à sa
création
90
Vue matérialisée : mode de rafraichissement
Modes de rafraichissement de la vue :
— 
— 
sur commit : clause ON COMMIT
à la demande (asynchrone, mode par defaut) : clause ON DEMAND (utilise des procédures du package standard
DBMS_MVIEW).
execute DBMS_REFRESH.MAKE('group_vue', 'vueMat1');
execute DBMS_REFRESH.REFRESH('group_vue');
— 
périodique : clauses START WITH et NEXT, qui précise une date de début et une période de temps intermédiaire
CREATE MATERIALIZED VIEW vueMat1
REFRESH START WITH SYSDATE NEXT SYSDATE + 1
AS
SELECT * FROM emp
WHERE job = 'ANALYST';
91
LIMITES SQL
92
Embedded SQL
— 
Les manques de SQL
— 
— 
— 
— 
⇒ 
— 
structures de programme
structures de contrôle
structures de données
facilités pour les entrées/sortie
problème pour développer des applications
Approche « Embedded SQL » — 
— 
— 
Insertion de SQL dans un langage de programmation
technique de pré-compilation
normalisation (Pascal, C, …)
— 
— 
Pro*C/C++ pour Oracle
Inconvénients
— 
— 
— 
deux systèmes de types
correspondance ensembliste/unaire
deux styles de programmation
Exemple : JDBC
// Exemple de programme JAVA qui utilise le pilote JDBC OCI8 d'Oracle
// pour insérer une ligne dans la table Client
// Il faut importer le paquetage java.sql pour utiliser JDBC
import java.sql.*;
class ClientInsertJDBC
{
public static void main (String args [])
throws SQLException, ClassNotFoundException, java.io.IOException
{
// Charger le pilote JDBC d'Oracle
Class.forName ("oracle.jdbc.driver.OracleDriver");
// Connexion à une BD
Connection uneConnection =
DriverManager.getConnection ("jdbc:oracle:oci8:@", "Scott", "Tiger");
// Création d'un énoncé associé à la Connection
Statement unEnoncéSQL = uneConnection.createStatement ();
// Insertion d'une ligne dans la table Client
int n = unEnoncéSQL.executeUpdate
("INSERT INTO CLIENT " +
"VALUES (100, 'Smith', '911')");
System.out.println ("Nombre de lignes inserees:" + n);
// Fermeture de l'énoncé et de la connexion
unEnoncéSQL.close();
uneConnection.close();
93
}
}
Exemple: SQLJ (préprocesseur SQL)
// Définition de la classe IteratorClient avec liaison par nom
#sql iterator IteratorClient(int noClient, String nomClient);
// Création d'un objet itérateur
IteratorClient unIteratorClient;
// Liaison de l'énoncé SELECT de l'itérateur
#sql
unIteratorClient =
{ SELECT idClient, nomClient
FROM Client WHERE idClient > 40};
// Accès au résultat du SELECT par itération sur les lignes
while (unIteratorClient.next()){
System.out.println("Numéro du client : " + unIteratorClient.idClient());
System.out.println("Nom du client : " + unIteratorClient.nomClient());
}
// Fermer l'itérateur
unIteratorClient.close();
94
Annexe : SQL intégré dans un langage de programmation
⇒ LINQ (.net)
var ensemblePays = new Pays[]{
new Pays("France",
60000000,
550000),
new Pays("Turquie",
70000000,
750000),
new Pays("Chine",
1300000000, 9600000),
new Pays("USA",
300000000, 9000000),
new Pays("Maroc",
31000000,
450000),
new Pays("Suisse",
7500000,
40000) };
var grosPays = from p in ensemblePays
where p.Superficie > 1000000
select p;
…
foreach (var p in grosPays)
{
Console.WriteLine(p);
}
95
Extensions procédurales SQL
96
Principe :
¨  ajout à SQL de structures de contrôle
⇒ SQL + outils procéduraux
⇒ notion de « procédures stockées » Standardisation : PSM (Persistent Stored Modules)
¨  Exemples :
¤  MySQL
: SQL/PSM
¤  Oracle : PL/SQL
¤  PostgreSQL : PL/PSM et PLpgSQL
¤  SQLserver : Transact-SQL