Transcript Chapitre 1 : Bases de données et SGBD

UE LMI 2.02
:
Algorithmique et programmation impérative
Introduction aux
Bases de Données relationnelles
Laszlo Szathmary
[email protected]
Module construit sur la base de travail de Malika Smaïl-Tabbone
Plan du cours
1. Introduction aux Bases de Données (BD)
2. Modèle relationnel de données
3. Algèbre relationnelle
4. De l’algèbre relationnelle à SQL
Travaux pratiques sur le système postgreSQL
(logiciel libre ou open source sous licence BSD)
2
Quelques références
-
«Bases de données et systèmes d’information » N.
Boudjlida, Dunod, 1999.
«Introduction aux bases de données» C.J. Date, Vuibert,
2004.
«Bases de données : les systèmes et leurs langages» G.
Gardarin, Eyrolles, 1984.
3
1. Introduction aux Bases de Données (BD)
Base de données (BD) : collection volumineuse de données
 cohérentes
 structurées indépendamment d’une application
 de redondance minimale
 accessibles par plusieurs utilisateurs à la fois
Système de Gestion de Bases de données (SGBD) :
logiciel assurant structuration, stockage, maintenance, mise
à jour et consultation de BDs
4
Fonctions d’un SGBD (1/4)
 Définition des données :
Un Langage de Définition de Données permet de décrire :
- des objets (personnes, véhicules)
- Des attributs sur les objets (nom, n° d’immatr.)
- Des liens entre objets (personne possède véhicule)
- Des contraintes sur objets, attributs et liens (un
véhicule n’a qu’un seul propriétaire)
Schéma d’une BD = description d’une BD à l’aide du LDD
5
Fonctions d’un SGBD (2/4)
 Manipulation des données

Un Langage de Manipulation de Données permet de
créer, rechercher, supprimer, modifier des données

Il existe d’autres façons de manipuler les données
 interface graphique orientée utilisateur final
 interface orientée programmeur d'applications
(SQL immergé dans un langage de programmation).
Le langage SQL comporte des commandes permettant de
définir ou de manipuler des données.
Fonctions d’un SGBD (3/4)
 Contrôler l'intégrité des données
Les données enregistrées doivent vérifier certaines
propriétés appelées contraintes d’intégrité (CI)
Assurer la sécurité de fonctionnement
Remettre rapidement une BD dans un état
opérationnel après un incident hardware/software
7
Fonctions d’un SGBD (4/4)
 Gérer les accès concurrents
Permettre des accès simultanés par plusieurs
utilisateurs
Assurer la confidentialité
Cacher certaines informations à certains utilisateurs
8
Quelques applications des BDs
 Gestion (salaires, stock…)
 Transactionnel (comptes bancaires, centrales d'achat…)
 Librairies électroniques (bibliothèques, journaux…)
 Documentation technique (plans, dessins…)
 Génie logiciel (programmes, manuels…)
…
9
Quelques métiers dans le domaine des BDs
 Utilisateur de BD (naïf, occasionnel, spécialisé)
 Concepteur et développeur de BD
 Administrateur de (SG)BD
 Réalisateur de SGBD ou d’outils périphériques (ex:
outils de conception)
…
10
Autres enseignements liés aux BDs
Pour ceux qui suivront le parcours Informatique
 En Licence 2 : UE Données Persistantes
 En Licence 3 : UE SGBD, UE Conception de BD
 En Master 2 Pro spécialité IL : UE Architecture et
administration des SGBD
11
Plan du cours
1.
2.
3.
4.
Introduction aux Bases de Données (BD)
Modèle relationnel de données
Algèbre relationnelle
De l’algèbre relationnelle à SQL
12
2. Modèle relationnel de données
Introduction intuitive
Une Base de Données Relationnelle (BDR) peut être vue par
l’utilisateur comme un ensemble de tableaux ou de tables.

Une table est un ensemble de lignes et de colonnes
Exemple de BDR :
fournisseur est une table contenant le numéro (nof),
le nom (nomf) et la ville (ville) de chaque fournisseur

pièce est une table contenant le numéro (nop), le nom
(nomp) et le prix (prix) de chaque pièce

vente est une table indiquant qu’une pièce (nop) est
vendue par un fournisseur (nof)

13
vente
fournisseur
nof
nomf
ville
nop
nof
1
2
3
Girard
Blanc
Merlin
Lyon
Paris
Nancy
1
1
1
2
2
2
3
2
3
1
nop
nomp
prix
1
2
3
vis
écrou
boulon
1.5
2
2.5
3
3
2
3
pièce
Une base de données relationnelle
Formalisation du modèle relationnel de
données (théorie des ensembles)

Un domaine est un ensemble de valeurs
–

Le produit cartésien des domaines D1, D2, …, Dn noté
D1xD2x…x Dn est l’ensemble des tuples (v1,v2,…, vn) tel que
vi  Di , 1  i  n
–

ex : L’ensemble des nombres entiers (Z) ; l’ensemble des chaînes de
caractères de longueur 50 ; {jaune, vert, bleu} ; {x,y,z}
ex : n=2, D1 = {1,2}, D2 = {x,y}, D1xD2 = {(1,x), (1,y), (2,x), (2,y)}
Une relation ou table est un sous-ensemble du produit cartésien
d’un ou plusieurs domaines :
R  D1xD2x…x Dn
–
ex :
R = {(1,x), (1,y), (2,y)}
15
Formalisation du modèle relationnel de
données
Une relation R  D1xD2x…x Dn est caractérisée
par :




son nom R
Son arité n
Un ensemble d'attributs A1, A2, … An
correspondant aux composantes d'un tuple
Di est le domaine de l'attribut Ai c'est à dire
l'ensemble des valeurs possibles de Ai (1  i  n)

Le schéma de la relation : R (A1, A2, … An)
16
Formalisation du modèle relationnel de
données

n=2, D1 = {1,2}, D2 = {x,y}, R = {(1,x), (1,y), (2,y)}

attribut chiffre pour la première composante

attribut lettre pour la seconde composante

schéma : R(chiffre,lettre)
domaine(chiffre)= D1 et domaine(lettre)= D2

Autre exemple de schéma de relation :
Pièce (nop, nomp, prix)
domaine (nop) : entiers positifs
domaine (nomp) : chaînes de caractères
domaine (prix) : réels positifs
nop
nomp
prix
1
vis
1.5
2
écrou
2
3
boulon
2.5
17
Formalisation du modèle relationnel de
données
L'extension d'une relation :


est l'ensemble de ses tuples
est représentée par un tableau à deux dimensions
 chaque colonne correspond à un attribut
 chaque ligne correspond à un tuple
Extension possible
de la table pièce
nop
nomp
prix
1
vis
1.5
2
écrou
2
3
boulon
2.5
18
Extension d'une table (ou relation) Vin
Nom de la relation
Vin
Nom d'attribut
Cru
Millésime
Région
Couleur
Chenas
1983
Beaujolais
Rouge
Tokay
1980
Alsace
Blanc
tuple
Tavel
1986
Rhône
Rosé
Chablis
1986
Bourgogne
Blanc
St-émilion
1987
Bordelais
Rouge
19
Notion de valeur NULL
La valeur d'un attribut dans un tuple peut être absente
–
Cette absence de valeur est notée NULL


Cas de l'attribut no-téléphone pour personne n'ayant pas de téléphone
Cas de l'attribut prix pour une pièce dont le prix n'est pas encore fixé
Pièce
nop
nomp
prix
1
Vis10
1.5
2
écrou
2
3
boulon
2.5
4
vis15
NULL
20
Autres notions dans un schéma de relation :
Clé primaire
 Clé primaire d'une relation : Groupe d’attributs
minimum qui identifie de manière unique tout tuple
dans une relation
ex :

{nop} dans la relation pièce
{nof} dans la relation fournisseur.
Convention : clé primaire soulignée dans le schéma
ex :
Pièce (nop, nomp, prix)
21
Autres notions dans un schéma de relation :
Clé étrangère

Clé étrangère d’une relation : l’attribut ou les attributs
qui constituent la clé primaire d’une autre relation.

Convention : clé étrangère en italique dans le schéma
Soit la base de données :
pièce (nop, nomp, prix)
fournisseur (nof,nomf,ville)
vente (nop,nof)
vente.nop est une clé étrangère et référence pièce.nop
vente.nof est une clé étrangère et référence fournisseur.nof
22
Plan du cours
1.
2.
3.
4.
Introduction aux Bases de Données (BD)
Modèle relationnel de données
Algèbre relationnelle
De l’algèbre relationnelle à SQL
23
3. Algèbre relationnelle (A.R.)
A.R. : Langage de manipulation de données relationnelles
A.R. : huit opérateurs s'appliquant à une ou deux
relations et donnant une relation comme résultat
- Union, intersection, différence
- Restriction (ou sélection)
- Projection
- Produit cartésien
- Jointure
24
Opérateur d'union
Définition
L'union de deux relations R et S de même
schéma est une relation T de même schéma
contenant l'ensemble des tuples de R et S.
T
Notation
T = UNION (R,S)

R
S
25
Exemple d’union de relations
VINS-1
Numéro
100
110
120
Cru
Chablis
Mecurey
Mâcon
Millésime
1974
1978
1977
Degré
12
13
12
VINS-2
Numéro Cru
Millésime Degré
100
Chablis 1974
12
200
Sancerre 1979
11
VINS-3 = UNION (VINS-1,VINS-2)
Numéro Cru
Millésime Degré
100
Chablis 1974
12
110
Mecurey 1978
13
120
Mâcon
1977
12
200
Sancerre 1979
11
26
Opérateur d'intersection
Définition
L'intersection de deux relations R et S de
même schéma est une relation T de même
schéma contenant l'ensemble des tuples
appartenant simultanément à R et à S.
T
Notation

T = INTERSECT (R,S)
R
S
27
Exemple d’intersection de relations
VINS-1
Numéro
100
110
120
Cru
Chablis
Mecurey
Mâcon
Millésime
1974
1978
1977
Degré
12
13
12
VINS-2
Numéro Cru
Millésime Degré
100
Chablis 1974
12
200
Sancerre 1979
11
VINS-4 = INTERSECT(VINS-1,VINS-2)
Numéro Cru
Millésime Degré
100
Chablis 1974
12
28
Opérateur de différence
Définition
La différence de deux relations R et S de
même schéma est une relation T de même
schéma contenant l'ensemble des tuples
appartenant à R et n'appartenant pas à S.
T
Notation
-
T = MINUS (R,S)
R
S
29
Exemple de différence de relations
VINS-1
Numéro
100
110
120
Cru
Chablis
Mecurey
Mâcon
Millésime
1974
1978
1977
Degré
12
13
12
VINS-2
Numéro Cru
Millésime Degré
100
Chablis 1974
12
200
Sancerre 1979
11
VINS-5 = MINUS (VINS-1, VINS-2)
Numéro Cru
Millésime Degré
110
Mecurey 1978
13
120
Mâcon
1977
12
30
Opérateur de projection
Définition
La projection d'une relation R de schéma R(A1, A2,...,An)
sur les attributs {Ai1, Ai2, ... , Aip} est une relation R' de
schéma R' (Ai1, Ai2, ..., Aip) dont les tuples sont obtenus
par élimination des attributs de R n'appartenant pas à R'
et par suppression des tuples en double.
Notation
T = PROJECT (R / {Ai1, Ai2, ..., Aip})
T
Ai1, Ai2, …Aip
R
31
Exemple de projection de relation
VINS
Numéro
100
110
120
200
Cru
Chablis
Mecurey
Mâcon
Sancerre
Millésime
1974
1978
1977
1977
Degré
12
13
12
12
VINS-7 = PROJECT (VINS/ {Millésime, Degré})
Millésime Degré
1974
12
1978
13
1977
12
32
Opérateur de restriction
Définition
La restriction d'une relation R à l'aide d'une
condition C est une relation R' de même schéma
dont les tuples sont ceux de R satisfaisant la
condition C.
T
Notation
T = RESTRICT (R / C)
C
R
33
Exemple de restriction de relation
VINS
Numéro
100
110
120
200
Cru
Chablis
Mecurey
Mâcon
Sancerre
Millésime
1974
1978
1977
1977
Degré
12
13
12
12
VINS-8 = RESTRICT (VINS / Degré = 12)
Numéro Cru
Millésime Degré
100
Chablis 1974
12
120
Mâcon
1977
12
200
Sancerre 1977
12
34
Condition de restriction
La condition C d'une restriction est une formule logique
quelconque avec des connecteurs ET () et OU () entre
conditions simples de la forme Ai  a où



Ai est un nom d’attribut
a est un élément du domaine de Ai (constante)
 est un opérateur de comparaison (=, <, >, , >=, <=)
ex. (Cru="Chablis"  Cru="Mâcon")
 Millésime<1988
35
Opérateur de produit cartésien
Définition
Le produit cartésien de deux relations R et S (de schémas
quelconques) est une relation T ayant pour attributs la
concaténation de ceux de R et de S et dont les tuples sont
toutes les concaténations d'un tuple de R à un tuple de S
(renommage des attributs de même nom).
T
Notation
x
T = PRODUCT (R,S)
R
S
36
Exemple de produit cartésien de relations
VINS
Numéro Cru
Millésime Degré
100
Chablis 1974
12
200
Sancerre 1979
11
VITICULTEURS
Nom
Ville
Région
Nicolas Pouilly
Bourgogne
Martin Bordeaux Bordelais
VIGNOBLE = PRODUCT (VINS, VITICULTEURS)
Numéro Cru
Millésime Degré Nom
Ville
100
Chablis 1974
12
Nicolas Pouilly
100
Chablis 1974
12
Martin Bordeaux
200
Sancerre 1979
11
Nicolas Pouilly
200
Sancerre 1979
11
Martin Bordeaux
Région
Bourgogne
Bordelais
Bourgogne
Bordelais
37
Opérateur de jointure
Définition
La jointure de deux relations R et S selon une
condition C est l'ensemble des tuples du produit
cartésien R X S satisfaisant la condition C.
T
Notation
T = JOIN (R, S/C)
C
R
S
JOIN(R, S/C) = RESTRICT (PRODUCT (R,S)/C)
38
Exemple de jointure de relations
VINS
Numéro
100
110
120
Cru
Chablis
Mecurey
Mâcon
Millésime
1974
1978
1977
Degré
12
13
12
VITICULTEURS
Nom
Ville
Région
Nicolas Pouilly Bourgogne
Félix
Mâcon Bourgogne
V-BIS = JOIN (VINS, VITICULTEURS / Cru = Ville)
Numéro Cru
Millésime Degré Nom Ville
Région
120
Mâcon 1977
12
Félix Mâcon Bourgogne
39
Jointure naturelle
Définition
La jointure naturelle de deux relations R et S est
l 'équi-jointure (opérateur d'égalité) de R et S sur
tous leurs attributs communs.
Notation
T = JOIN (R, S)
T
R
S
40
Exemple de jointure naturelle de relations
VINS-1
Numéro
150
110
120
Cru
Riesling
Mecurey
Mâcon
Millésime
1984
1978
1977
Degré
11
13
12
VITIC
Nom
Numéro Région
Nicolas 150
Alsace
Félix
120
Bourgogne
VINS-C = JOIN (VINS-1, VITIC )
Numéro Cru
Millésime Degré Nom
Région
150
Riesling 1984
11
Nicolas Alsace
120
Mâcon
1977
12
Félix
Bourgogne
41
Exemples de requêtes algébriques (1/3)
Soit la base de données :
pièce (nop, nomp, prix)
fournisseur (nof,nomf,ville)
vente (nop,nof )
Exemple de question : Trouver le numéro des fournisseurs vendant
le produit boulon.
Requête algébrique :
PROJECT ( JOIN (vente , RESTRICT (pièce / nomp = 'boulon'))
/ {nof})
42
vente
fournisseur
nof
nomf
ville
nop
nof
1
2
3
Girard
Blanc
Merlin
Lyon
Paris
Nancy
1
1
1
2
2
2
3
2
3
1
nop
nomp
prix
1
2
3
vis
écrou
boulon
1.5
2
2.5
3
3
2
3
pièce
Une base de données relationnelle
Exemples de requêtes algébriques (2/3)
Base de données VPC (Vente par Correspondance)
Client (numCli, nom, prénom, rue, CP, ville)
Produit (numProd, libellé, pu)
Commande (numCli, numProd, date, quantité)
Exemple de question : Trouver les produits commandés en
quantité supérieure à 100 et dont le prix dépasse 1000€ . On
affichera les numéros de produit, leur libellé et leur prix
unitaire ainsi que la date de la commande.
Notons Res la relation résultat et R1, R2, R3 des
relations intermédiaires
44
Exemple de requête algébrique (3/3)
Res
R1 = RESTRICT(Produit/ pu >1000)
numProd,libellé,pu,date
R2 = RESTRICT (Commande/qté > 100)
R3
R3 = JOIN(R1,R2)
Res = PROJECT (R3/{numProd,libellé,pu,date})
R1
pu > 1000
Produit
R2
qté > 100
Commande
45
A vous : construire les requêtes algébriques
répondant aux questions (BD VPC)
1) Libellé et prix unitaire de tous les produits
2) Libellé des produits de prix inférieur à 50€
3) Nom et prénom des clients ayant commandé le
produits numéro 56.
4) Nom des clients n'ayant pas commandé le produit
numéro 56.
46
Plan du cours
1.
2.
3.
4.
Introduction aux Bases de Données (BD)
Modèle relationnel de données
Algèbre relationnelle
De l’algèbre relationnelle à SQL
47
4. De l’algèbre relationnelle à SQL



Standard SQL
Interrogation de données
Définition de données
Mise à jour de données
48
a) Standard SQL


SQL : Structured Query Language
SQL est un standard ANSI/ISO depuis
1986




Version SQL-92 ou SQL2 (standard bien supporté)
Version SQL-99 ou SQL3 (dernier standard, peu
supporté)
postgreSQL implémente bien SQL2
Dans ce cours : commandes SQL2 pour
a)
b)
c)
Interrogation de données
Définition de données
Mise à jour de données
49
a) Interrogation de données
SQL : langage pour les BDR basé sur l’algèbre
relationnelle
 Traduction
des opérateurs de l’algèbre en SQL
(projection, restriction, jointure …)
o commande SELECT
 Opérateurs
supplémentaires pour trier ou agréger
des données
50
Forme générale de la commande SELECT
SELECT {* | [ALL|DISTINCT] expr1 [AS nomCol1]
[,expr2 [AS nomCol2]]…}
FROM relation1 [alias1] [,relation2 [alias2] …]
[WHERE condition]
[GROUP BY nomAttribut1 [,nomAttribut2]…]
[HAVING
condition]
[ORDER BY nomAttr1 [ASC|DESC][,nomAttr2 [ASC|DESC]…]
51
Notations pour la syntaxe des commandes
Notation
Signification
[élément]
élément est optionnel
élément1|élément2
on a le choix entre élément1
et élément2
{ élément1|élément2}
idem
{élément1}|{élément2}
idem
52
vente
fournisseur
nof
nomf
ville
nop
nof
1
2
3
Girard
Blanc
Merlin
Lyon
Paris
Nancy
1
1
1
2
2
2
3
2
3
1
nop
nomp
prix
1
2
3
vis
écrou
boulon
1.5
2
2.5
3
3
2
3
pièce
Base de données exemple
Traduction de la projection (1/2)
Trouver le nom et la ville de tous les fournisseurs
SELECT
FROM
nomf, ville
fournisseur
SELECT
FROM
nomf
ville
ALL nomf, ville
Girard
Blanc
Lyon
Paris
fournisseur
Merlin
Nancy
ou
Attention : les doublons ne sont pas supprimés !
54
Traduction de la projection (2/2)
Trouver les numéros de fournisseur vendant au moins une pièce
(sans les doublons)
SELECT
DISTINCT nof FROM
vente
nof
1
2
3
Expression
algébrique
équivalente
PROJECT (vente / {nof})
55
Traduction de la restriction (1/2)
Trouver les fournisseurs de Lyon
SELECT
FROM
WHERE
*
fournisseur
ville = ‘Lyon’
nof
nomf
ville
1
Girard
Lyon
Exp. algéb.
équivalente
RESTRICT (fournisseur / ville = ‘Lyon’)
56
Restriction : forme de la condition
SELECT
WHERE

*
FROM nom-relation
condition
Syntaxe de condition
conditionSimple | (condition) | NOT (condition) |
condition {AND|OR} condition

Syntaxe de conditionSimple
expression {= | <|>|<=|>=|<>|!=} expression |
expression [NOT]BETWEEN expression AND expression|
expression IS [NOT] NULL
expression [NOT]LIKE patron
57
Restriction : Exemples

Pièces dont le numéro est compris entre 1 et 100
SELECT *
FROM
pièce
WHERE nop >= 1 AND nop <= 100
ou
SELECT *
FROM
pièce
WHERE nop BETWEEN 1 AND 100

Pièces dont le numéro est inférieur à 10 ou supérieur à 20
SELECT
FROM
*
pièce
WHERE
nop < 10 OR nop > 20
58
Restriction : Opérateurs IS NULL et IS
NOT NULL
IS NULL : Teste si la valeur d'un attribut est absente
IS NOT NULL : Teste si la valeur d'un attribut est
présente (ou définie)

Pièces dont le prix n'est pas fixé (null)
SELECT
FROM
WHERE
*
pièce
prix IS NULL
59
Restriction : Opérateur LIKE
LIKE teste si une chaîne de caractères correspondant à un patron
où :
% : correspond à zéro à n caractères quelconques
_ : correspond un caractère quelconque

Fournisseurs dont le nom commence par B, se termine par B et
contient au moins 3 caractères
SELECT
FROM
WHERE
*
fournisseur
nomf LIKE ‘B_%B’
60
Traduction de restriction et projection

Nom et prix des pièces dont le numéro est supérieur ou égal à 2
SELECT
FROM
WHERE
nomp, prix
pièce
nop >= 2
nomp
prix
écrou
boulon
2
2.5
Exp. Algébr.
équivalente
PROJECT (RESTRICT (pièce/nop >=2) / {nomp,prix})
61
Traduction du produit cartésien
SELECT *
FROM
relation1, relation2
Produire toutes les combinaisons possibles de fournisseur et de pièce
SELECT
*
FROM
fournisseur, pièce
Exp. Algébr.
équivalente
PRODUCT ( fournisseur, pièce )
62
Traduction de la jointure
SELECT
FROM
WHERE

attribut1 [,attribut2, …]
relation1,relation2 [,relation3,…]
condition
Cette commande SELECT combine
–
–
–
produit cartésien entre relation1,relation2, relation3…
restriction sur condition
Projection sur attribut1,attribut2…
N.B. Il faut préfixer par un nom de relation chaque attribut
commun à plusieurs relations
63
Jointure : exemple de requête
Liste des ventes avec le nom du fournisseur
SELECT nop,vente.nof,nomf
FROM vente, fournisseur
WHERE vente.nof = fournisseur.nof
nop,vente.nof…
vente.nof =
founisseur.nof
x
vente
fournisseur
Arbre algébrique correspondant à
l’expression SQL (SGBDR)
64
Jointure : utilisation d'alias
Utilisation d’alias pour alléger l’écriture d'une
requête incluant des jointures
Liste des ventes avec le nom du fournisseur
SELECT
FROM
WHERE


v.nop,v.nof,f.nomf
vente v, fournisseur f
v.nof = f.nof
vente alias v
fournisseur alias f
65
Opérations ensemblistes
(UNION, INTERSECT, EXCEPT)

Noms et prénoms des employés qui sont aussi des passagers
Employé
Passager
noEmp
nomEmp
prénomEmp
noPass
nomPass
prénomPass
10
Henry
John
4
Harry
Peter
15
Conrad
James
78
Conrad
James
35
Jenqua
Jessica
9
Land
Robert
46
Leconte
Jean
466
Leconte
Jean
(SELECT nomEmp as nom, prénomEmp as prénom
FROM Employé)
INTERSECT
(SELECT nomPass as nom, prénomPass as prénom
FROM Passager)
nom
prénom
Conrad
James
Leconte
Jean
66
Expression de calcul dans la liste de
projection (partie SELECT)

Liste des numéros de pièce avec le prix avant et après
inclusion d’une taxe de 10%
SELECT
FROM
nop, prix, prix*1.1 as prixTTC
pièce
nop
prix
prixTTC
1
1.5
1.65
2
2
2.2
3
2.5
2.75
67
Expression de calcul dans la condition
(partie WHERE ou partie SELECT)
Une condition peut comporter une expression de calcul
Liste des numéros de pièce dont le prix TTC dépasse 2€
SELECT
FROM
WHERE
nop
pièce
prix*1.1 > 2
nop
2
3
68
Expression de calcul dans la condition
(partie WHERE)
Une expression peut aussi faire appel à des fonctions
Numéro et nom des pièces dont le nom comporte 4 caractères ou plus
SELECT
FROM
WHERE
nop, nomp
pièce
CHARACTER_LENGTH(nomp) >= 4
nop
nomp
2
3
écrou
boulon
CHARACTER_LENGTH ( c ) : fonction retournant le nombre de caractères
de la chaîne c
69
Fonctions d’agrégation (ou de groupe)
Elles opèrent sur un groupe de valeurs d’attributs et produisent
une valeur résultat (extension de l’algèbre relationelle)
Nombre total de pièces dans la relation pièce
SELECT COUNT(*) AS nb_pieces
FROM
pièce
nb_pieces
3
Prix moyen des pièces
SELECT AVG (prix) AS prix_moyen
FROM
pièce
prix_moyen
2
Prix maximal des pièces (maximum de la colonne prix)
SELECT MAX(prix) AS prix_maxi
FROM
pièce
prix_maxi
2.5
70
Fonctions d’agrégation (ou de groupe)
Prix minimal des pièces (minimum de la colonne prix)
prix_mini
SELECT MIN(prix) AS prix_mini
FROM
pièce
1.5
Somme des prix des pièces
SELECT SUM(prix) AS somme_totale
FROM
pièce
somme_totale
6
Somme des prix des pièces dont le numéro est supérieur ou égal à 2
SELECT SUM (prix) AS somme
FROM
pièce
WHERE nop >= 2
somme
4.5
71
Tri du résultat d’une requête (ORDER BY)

Possibilité de trier les résultats d’une requête par rapport à une
ou plusieurs colonnes
SELECT colonne(s)
FROM relation(s) [WHERE condition]
ORDER BY colonne(s) [ASC|DESC]
Où
ASC : ordre ascendant (par défaut)
DESC : ordre descendant
liste des pièces par ordre décroissant du prix nop
SELECT *
3
FROM
pièce
ORDER BY prix DESC
2
1
nomp
prix
boulon
2.5
écrou
vis
2
1.5
72
Requêtes imbriquées
Opérateur IN / NOT IN

Le résultat d’une commande SELECT peut être utilisé
dans la condition d’une autre commande SELECT
SELECT
FROM
WHERE
attribut(s)
relation(s)
expression [NOT]IN (sous-requête)
On teste l’appartenance (ou non appartenance) de la valeur
de l'expression à l'ensemble retourné par la sous-requête
73
Requêtes imbriquées
Opérateur IN / NOT IN
Nom des pièces commandées par le fournisseur numéro 1
SELECT
FROM
WHERE
nomp
pièce
nop IN
(SELECT nop
FROM
vente
WHERE
nof = 1)
74
b) Définition de données
1.
Création d'un schéma de relation

2.
CREATE TABLE
Suppression d'un schéma de relation

DROP TABLE
75
b) Définition de données
Création d'un schéma de relation
Créer les schémas des tables pièce, fournisseur,vente
pièce (nop, nomp, prix)
fournisseur (nof,nomf,ville)
vente (nop,nof )
CREATE TABLE pièce
(nop integer,
nomp varchar(50) NOT NULL,
prix float,
PRIMARY KEY (nop))
CREATE TABLE fournisseur
(nof integer,
nomf varchar(80) NOT NULL,
ville varchar(60),
PRIMARY KEY (nof))
76
b) Définition de données
Création d'un schéma de relation
CREATE TABLE
(nop integer
nof integer
PRIMARY KEY
vente
REFERENCES pièce(nop),
REFERENCES fournisseur(nof),
(nop,nof))
–
L'attribut nop dans la table vente fait référence à l'attribut
nop de la table pièce
–
L'attribut nof dans la table vente fait référence à l'attribut
nof de la table fournisseur
77
b) Définition de données
Types de données
Type de données d'un attribut = domaine des valeurs
INTEGER : nombres entiers
 FLOAT : nombres réels
 CHAR(n) : chaînes de caractères de longueur fixe n
 VARCHAR(n) : chaînes de caractères de longueur
variable et de longueur maximale n

78
b) Définition de données
Suppression d'un schéma de relation
DROP TABLE
nom-relation
suppression du schéma de la relation
 suppression des tuples de la relation

Supprimer le schéma de la table vente
DROP TABLE vente

Suppression de tous les tuples de la table Client

Suppression du schéma de la table vente

On ne plus peut insérer de nouveaux tuples dans la table vente
79
c) Mise à jour des données
Ajout de tuples (INSERT)
INSERT INTO nom-relation [(attr1, attr2…)]
VALUES (val-attr1, val-attr2, … )
Ajout de deux pièces dans la relation pièce
INSERT INTO pièce VALUES (4,'clou’,1.99)
INSERT INTO pièce (nop,nomp) VALUES (5,'cheville’)
nop
nomp
prix
1
vis
1.5
2
écrou
2
3
boulon
2.5
4
clou
1.99
5
cheville
NULL
N.B. Les attributs non insérés
sont positionnés à NULL
80
c) Mise à jour des données
Modification de tuples (UPDATE)
UPDATE nom_relation
SET attr1=exp1 [,attr2=exp2 … ]
[WHERE condition ]
Positionner à 0.99 le prix de la pièce numéro 4
UPDATE
pièce
SET
prix= 0.99 WHERE nop=4
Augmenter de 5% le prix des pièces dont le nom commence par c
UPDATE pièce
SET prix= prix*1.05
WHERE nomp LIKE 'c%'
81
c) Mise à jour des données
Suppression de tuples (DELETE)
DELETE
[FROM]
relation
[WHERE condition]
Supprimer les fournisseurs de Lyon:
DELETE
fournisseur
WHERE
ville =
‘Lyon’
Supprimer tous les tuples de la table vente :
DELETE
vente
 suppression de tous les tuples de la table Client
 le schéma de la table vente existe toujours
 On peut insérer des nouveaux tuples dans la table vente
82