Bases de données (BDD1)

[Link]@[Link]
Année universitaire : 2024-2025

BDD1 1 / 82
Organisation du cours

Volume horaire
6 séances de CM (1h) : lundi de 13h30 à 14h30
6 séances de TD (1h30/semaine) : 6*1.5h = 9h TD
6 séances de TDM (2h/semaine) : 6*2h = 12h TDM

Chaque semaine, il y aura

1h de cours
1,5 heure de TD
et 2h de TP sur ordinateur
Cours commun à la L2 Info, L3 Info-Maths, Master MISO et PEIP2

BDD1 2 / 82
Organisation du cours

Intervenants
Imen Sayar ([Link]@[Link]) : CM/TD/TP
Jean-François Roos ([Link]@[Link]) (Info)
Ioan Marius Bilasco ([Link]@[Link]) (Info)
Hugo Monfleur ([Link]@[Link]) (Info)
Mikael Salson ([Link]@[Link]) (M1 MISO)
Sara Riva ([Link]@[Link]) (Info)
Emilie Allart ([Link]@[Link]) (Info)
Hela Kadri ([Link]@[Link]) (Info + PEIP)
Philippe Lesueur ([Link]@[Link]) (IM)
Pierrick Uro ([Link]@[Link]) (PEIP)
Jean Luc Intumwayase ([Link]@[Link])
(PEIP)

BDD1 3 / 82
Evaluation

pour la première chance

+ 0.51 X DSf + 0.34 X DSi + 0.15 X TP
pour la seconde chance
+ 0.85 X DSf + 0.15 X TP

BDD1 4 / 82
Exemple concret : une bibliothèque
Imaginez une bibliothèque classique où
les informations sur chaque livre sont enregistrées sur des
fiches papier
chaque fiche contient des détails tels que le titre, l’auteur,
l’année de publication, et la catégorie du livre

Si on veut
trouver un livre spécifique ou consulter ses informations
suivre quels livres sont empruntés, qui les ont empruntés, à
quelles dates, etc.
ajouter un nouveau livre ou modifier les informations d’un
livre existant
le/la bibliothécaire doit
parcourir manuellement des centaines, voire des milliers de fiches
et modifier manuellement ces fiches
BDD1 5 / 82
Exemple concret : une bibliothèque

Avec une base de données de bibliothèque

nommée "bibliothèque" pour gérer ses livres
Cette base de données contient plusieurs tables
Table "livres" avec attributs (colonnes) pour le titre,
l’auteur, l’année
Table “auteurs" avec attributs pour le nom, prénom,
biographie, etc.
Table “emprunts” avec attributs pour le titre du livre, nom de
l’adhérent, date de l’emprunt, etc.

Avantages
recherche rapide
gestion simple des données
stockage des données sous une forme structurée et efficace

BDD1 6 / 82
Qu’est-ce qu’une base de données ?

BDD1 7 / 82
Base de données (BD)

Une base de données

est un ensemble d’informations, structurées de façon à pouvoir être
consultées (extraites), modifiées, ajoutées, supprimées.

Avantages
Efficacité : Accès rapide et gestion facile des données.
Fiabilité : Prévention des erreurs et maintien de l’intégrité des
données.
Sécurité : Contrôle d’accès aux informations sensibles.
Scalabilité : Capacité à gérer de grandes quantités de données.

BDD1 8 / 82
Système de Gestion d’une Bases de Données (SGBD)

Un Système de Gestion d’une Bases de Données

est un logiciel qui gère un ensemble de bases de données.
Il permet
la mémorisation et la représentation interne des données.
la manipulation et la consultation de ces données par
l’intermédiaire d’une interface utilisateur (langage
d’interrogation, API...).
Autrement dit, un SGBD est un langage intermédiaire pour
interagir avec une BDD.
Chaque SGBD est fondé sur un modèle de représentation
des données.

BDD1 9 / 82
Modèles de données

Les modèles
Un modèle de données est une façon de structurer les données.
C’est un paradigme, une logique d’organisation.
Il existe plusieurs modèles de données :
relationnel
hiérarchique
réseau
objet
relationnel-objet
... différents modèles dits « NoSQL »

Le cours de BDD
est consacré uniquement au modèle relationnel

BDD1 10 / 82
Bases relationnelles

Le modèle relationnel
formalisé par E.F. CODD, en 1970
devenu le plus utilisé depuis les années 1980.
les données sont structurées en un ensemble
de relations → selon une approche mathématique
de tables → selon une approche plus « graphique »

Quelques SGBD relationnels

Oracle Database
DB2 (IBM)
Microsoft SQL Server
PostgreSQL
MySQL

BDD1 11 / 82
Le schéma d’une relation, dans le modèle relationnel

Le schéma d’une relation définit sa structure

Il comporte le nom de la relation et une suite d’attributs.
Chaque attribut est défini par un nom et un domaine (l’ensemble
des valeurs admises pour cet attribut ; cf le type dans un langage
de programmation)

BDD1 12 / 82
Le schéma d’une relation, dans le modèle relationnel

Le schéma d’une relation définit sa structure

Il comporte le nom de la relation et une suite d’attributs.
Chaque attribut est défini par un nom et un domaine (l’ensemble
des valeurs admises pour cet attribut ; cf le type dans un langage
de programmation)

Exemple : la relation étudiants

possède 3 attributs :

(nom : chaîne, prénom : chaîne, groupe : entier)

Graphiquement :
chaîne chaîne entier
étudiants : nom prénom groupe

BDD1 13 / 82
La relation, dans le modèle relationnel
Une relation contient des tuples
Une relation est un ensemble de n-uplets appelés tuples.
le tuple comporte une valeur pour chaque attribut.
chaque valeur doit appartenir au domaine correspondant.

Exemple : la relation étudiants

Chaque tuple de cette relation est un triplet (car 3 attributs)
composé de 2 chaînes puis d’un entier.
{(Dupont, Alfred, 2), (Smith, John, 1), (Durand, Alfred, 1)}

Graphiquement : chaîne chaîne entier

nom prénom groupe
étudiants : Dupont Alfred 2
Smith John 1
Durand Alfred 1
BDD1 14 / 82
La relation, dans le modèle relationnel

chaque tuple (ligne) est une information que l’on souhaite

représenter dans la base :
Exemple : « un étudiant s’appelle Dupont Alfred et il est dans
le groupe 2 »
une relation est un ensemble ⇒ l’ordre de ses éléments
n’a pas d’importance.
Exemple :
{(Dupont, Alfred, 2), (Smith, John, 1), (Durand, Alfred, 1)} =
{(Smith, John, 1), (Durand, Alfred, 1), (Dupont, Alfred, 2)}
bien distinguer le schéma de la relation qui définit sa
structure (nombre de colonnes, intitulés des colonnes et
domaines) et la relation elle-même (les données : les tuples)

BDD1 15 / 82
Les relations

Schéma de la relation : N-uple :

(Attribut1 : Domaine1 , ... , AttributN : DomaineN )
Domaine1 ... DomaineN
Attribut1 ... AttributN

Attribut ≡ Nom de colonne

Domaine de définition ≡ Type
Relation : ensemble de N-uples (les « tuples »)
{(a1 , ..., aN ), (b1 , ..., bN ), . . . }
Domaine1 ... DomaineN
Attribut1 ... AttributN
a1 ... aN
b1 ... bN
... ... ...

BDD1 16 / 82
Comment filtrer, combiner, et extraire des informations à partir des
bases de données ?
⇒ Besoin d ′ opérations

Algèbre relationnelle

BDD1 17 / 82
L’algèbre relationnelle

5 opérations fondamentales constituent l’algèbre relationnel

pour extraire de l’information d’une base de donnée, on utilise
des opérations sur les tables.
dans le modèle théorique, 5 opérations constituent l’algèbre
relationnel :
projection d’une table (opération unaire)
restriction d’une table (opération unaire)
union de 2 tables (opération binaire)
différence de 2 tables (opération binaire)
produit cartésien de 2 tables (opération binaire)
Le résultat de chaque opération est une table
on ajoutera l’opération de jointure, qui est en fait une
combinaison de 2 autres, mais qui est l’une des plus
couramment utilisée et mérite d’être étudiée en tant que telle.

BDD1 18 / 82
Opérations relationnelles : la projection (π)

La projection d’une relation R est une relation R ′ obtenue en ne

conservant que certains attributs.

Sous-schéma
Si s = (A1 , A2 , ...An ), un sous-schéma de s est un schéma
s ′ = (Ai1 , Ai2 , ...Aim ) où ik ∈ [1, n]
Ex : s ′ = (nom : chaine, groupe : entier )

Projection de R selon un sous-schéma

soit R de schéma (A1 , A2 , ...An ) et s ′ = (Ai1 , Ai2 , ...Aim ) un
sous-schéma de s,

πs ′ (R) = {(x1 , ..xm )|∃(y1 , ...yn ) ∈ R, ∀k ∈ [1, m], yik = xk }

BDD1 19 / 82
Opérations relationnelles : la projection (π)

Exemple de projection
Projection de la relation « étudiants » sur le sous-schéma
(nom, groupe) :

πnom,groupe (etudiants) = {(Dupont, 2), (Smith, 1), (Durand, 1)}

nom prénom groupe nom groupe

Dupont Alfred 2 Dupont 2
⇒
Smith John 1 Smith 1
Durand Alfred 1 Durand 1

BDD1 20 / 82
Opérations relationnelles : la restriction (σ)

La restriction d’une relation R est une relation R ′ de même

schéma obtenue en ne conservant que certains tuples.

Qualification (ou condition)

Une qualification est une expression booléenne dans laquelle
peuvent intervenir des constantes et des noms d’attributs. Ex :
groupe = 1
nom = prenom
prénom=Alfred et groupe >1

Qualification d’un tuple

Un tuple est qualifié si l’évaluation de la qualification pour les
valeurs du tuples a pour résultat VRAI. Par exemple, pour la
qualification « prénom=Alfred et groupe >1 », (Dupont, Alfred, 2)
est qualifié mais pas les 2 autres tuples.
BDD1 21 / 82
Opérations relationnelles : la restriction (σ)

Restriction
La restriction d’une relation R par une qualification q est une
relation définie sur le même schéma que R.

σq (R) = {t| t ∈ R et t qualifié pour q}

BDD1 22 / 82
Opérations relationnelles : la restriction (σ)

Exemple
Restriction de la relation « étudiants » par la qualification
« groupe=1 »

σgroupe=1 (etudiants) = {(Smith, John, 1), (Durand, Alfred, 1)}

nom prénom groupe

Dupont Alfred 2 False
Smith John 1 True
Durand Alfred 1 True

nom prénom groupe

⇒ Smith John 1
Durand Alfred 1

BDD1 23 / 82
Opérations relationnelles : la restriction (σ)

Exemple
Restriction de la relation « étudiants » par la qualification
« nom=prénom »

σnom=prenom (etudiants) = ∅

nom prénom groupe

Dupont Alfred 2 False
Smith John 1 False
Durand Alfred 1 False

⇒ nom prénom groupe

NB : on obtient ici une relation vide. Obtenir une relation

vide n’est pas une erreur.

BDD1 24 / 82
Opérations relationnelles : union (∪)

Schémas
Cette opération fait intervenir 2 tables qui doivent être
définies sur le même schéma
la table résultante est aussi sur le même schéma

R ∪ R ′ : union de deux relations R et R ′

résultat : tous les tuples de R ou de R ′

BDD1 25 / 82
Opérations relationnelles : différence (−)

R − R ′ : différence de deux relations R et R ′

R et R ′ doivent être définies sur le même schéma
résultat : tous les tuples de R qui ne sont pas dans R ′ .

Exemple de différence
R
R’
nom prénom groupe
nom prénom groupe
Dupont Alfred 2 −
Smith John 1
Smith John 1
Smith Arthur 3
Durand Alfred 1
R − R′
nom prénom groupe
Dupont Alfred 2
Durand Alfred 1

BDD1 26 / 82
Produit cartésien (×)

T × T ′ : produit cartésien de deux relations T et T ′

T de schéma s et T ′ de schéma s ′ .
T × T ′ défini sur le schéma s.s ′ (concaténation).
T × T ′ contient les tuples (x1 , . . . xn , y1 , . . . yn′ )
où (x1 , . . . xn ) ∈ R et (y1 , . . . yn′ ) ∈ R ′

Remarque
le nombre d’attributs (« colonnes ») est la somme des
nombres d’attributs des tables d’origine
le nombre de tuples (« lignes ») est le produit des nombres
de tuples des tables d’origine

BDD1 27 / 82
Produit cartésien (×)
Exemple de produit cartésien
étudiants
groupes
nom prénom groupe
numéro salle
Dupont Alfred 2 ×
1 B-27
Smith John 1
2 A-14
Durand Alfred 1
étudiants × groupes
nom prénom groupe numéro salle
Dupont Alfred 2 1 B-27
Smith John 1 1 B-27
Durand Alfred 1 1 B-27
Dupont Alfred 2 2 A-14
Smith John 1 2 A-14
Durand Alfred 1 2 A-14

Quelle signification donner aux données de cette table ?

BDD1 28 / 82
Jointure (⋊
⋉)
Jointure
En pratique on utilise la jointure plutôt que le produit cartésien
Une jointure est une combinaison d’un produit cartésien et
d’une restriction
La jointure fait intervenir 2 tables quelconques et une
condition de jointure
La condition de jointure doit porter sur des attributs de
chacune des tables
La condition de jointure la plus simple est l’égalité entre un
attribut de la table 1 et un attribut de la table 2
La condition de jointure peut être plus complexe mais nous
commencerons par l’égalité simple

Exemple de condition de jointure

é[Link] = [Link]éro
BDD1 29 / 82
Jointure (⋊
⋉)
Exemple de jointure
étudiants
groupes
nom prénom groupe
numéro salle
Dupont Alfred 2
1 B-27
Smith John 1
2 A-14
Durand Alfred 1
jointure sur é[Link] = [Link]éro
nom prénom groupe numéro salle
Smith John 1 1 B-27
Durand Alfred 1 1 B-27
Dupont Alfred 2 2 A-14
Remarque sur la sémantique :
les attributs é[Link] et [Link]éro portent sur
la même "entité" (un numéro de groupe)
on n’a conservé que les tuples significatifs
BDD1 30 / 82
Le langage SQL

Simple Query Langage (SQL)

est un langage d’interrogation des SGBD relationnelles.
Né dans les années 70-80, il a fait l’objet de plusieurs normes
successives (86, 89, 92 [SQL2], 99 [SQL3], . . ., [SQL :2011]).
Par ailleurs les différents SGBD peuvent implémenter tout ou
partie ... ou des extensions de SQL.
Fonctionnalités de SQL :
Définition, modification ou suppression de schémas de tables
(DDL) : create, alter, drop.
Manipulation de données (DML) : select, update,
insert, delete.
Administration et gestion des droits (DCL) : grant, revoke.

BDD1 31 / 82
Select

Select : commande permettant de consulter (« extraire ») les

données
Sa syntaxe minimale est
select noms de colonnes from nom de table
Exemple : select Nom,Groupe from R
Sous cette forme, c’est une projection. Les noms de colonnes
forment le sous-schéma.
Pour le sous-schéma, on peut utiliser la notation * qui désigne
le schéma complet : l’expression select * from R désigne
la projection « identité ».
Le résultat d’une requête « select » est une table

BDD1 32 / 82
Select

Select : premiers exemples

nom prenom groupe
Dupont Alfred 2
select * from etudiants
Smith John 1
Durand Alfred 1
nom groupe
Dupont 2
select nom,groupe from etudiants
Smith 1
Durand 1

BDD1 33 / 82
Restriction (sélection) : where

La clause where
permet de réaliser une restriction du résultat (choix de lignes)
optionnelle
est suivie d’une qualification.

select * from etudiants

where groupe<=1 and prenom='Alfred'
nom prenom groupe
Durand Alfred 1

BDD1 34 / 82
Tris : order by

L’ordre des lignes obtenues est incertain

Sauf si l’on indique explicitement un ordre de classement par la
clause order by .
select * from etudiants order by groupe
Smith John 1 NB : ordre entre les lignes de
Durand Alfred 1 même groupe non garanti
Dupont Alfred 2
select * from etudiants order by groupe desc
Dupont Alfred 2 NB : ordre entre les lignes de
Smith John 1 même groupe non garanti
Durand Alfred 1
select * from etudiants order by groupe desc, nom asc
Dupont Alfred 2
Durand Alfred 1
Smith John 1

BDD1 35 / 82
Du modèle à la pratique...

Différence par rapport au modèle ensembliste strict

Une table peut contenir des doublons.
La clause distinct permet de les éliminer du résultat, si besoin.

exemple
select prenom select distinct prenom
from etudiants from etudiants
prenom
prenom
Alfred
Alfred
Alfred
John
John

BDD1 36 / 82
Du modèle à la pratique...

Absence de valeur
sauf indication contraire, la valeur d’un attribut peut ne pas
être définie (absence de valeur)
l’absence de valeur est un état particulier appelé NULL
testé par le prédicat IS NULL (jamais =NULL, puisque ce
n’est pas une valeur)
négation : IS NOT NULL
NB : on verra ultérieurement que, dans la définition d’une relation,
on peut interdire l’absence de valeur

BDD1 37 / 82
Les spécifications d’attributs

« Renommage » des attributs

le mot clé as placé après la spécification d’un attribut définit
un nom

Renommage
select nom as lastname,
prenom as firstname
from etudiants
lastname firstname
Dupont Alfred
Smith John
Durand Alfred

BDD1 38 / 82
Les spécifications d’attributs
Une spécification d’attribut est une expression
faisant intervenir noms d’attributs, constantes, opérateurs,
appels de fonction
les chaînes sont délimitées par des simples quotes
les noms d’attributs peuvent être délimités par des doubles
quotes
opérateurs
arithmétiques (usuels)
de comparaison (usuels)
de concatenation : ||
test nullité : IS NULL, IS NOT NULL
appels de fonctions
voir doc PostgreSQL (ou du SGBD utilisé)

Il est fortement recommandé d’utiliser le renommage (« as »)

quand la spécification est une expression complexe
BDD1 39 / 82
Les spécifications d’attributs

Exemple
select prenom || ' ' || nom as "Nom complet"
from etudiants
Nom complet
Alfred Dupont
John Smith
Alfred Durand

Délimitation des noms d’attributs par " "

Obligatoire si le nom comporte espaces ou majuscules.

BDD1 40 / 82
Op. ensemblistes : union, except, intersect
combiner 2 relations (ou plus)
union : UNION, différence : EXCEPT, intersection :
INTERSECT

condition
les relations doivent avoir le même nombre de colonnes
les domaines (types) des colonnes doivent être compatibles
(par conversion de type)
(voir le cours sur l’algèbre relationnelle)

les noms d’attributs du select (« premier » select) déterminent

ceux du résultat
les doublons sont éliminés par défaut, sauf si le mot clé ALL
est ajouté (ex : UNION ALL)
une clause « order by » finale peut être utilisée
BDD1 41 / 82
Op. ensemblistes : union, except, intersect
Exemple

mix
select prenom as mix
Alfred
from etudiants
Dupont
union
Durand
select nom from etudiants
John
order by mix
Smith

mix
Alfred
select prenom as mix
John
from etudiants
Alfred
union all
Durand
select nom from etudiants
Smith
Dupont
BDD1 42 / 82
Jointure
Syntaxe SQL
select spécification_attributs
from table1 join table2 on condition

Exemple
select * from etudiants
join groupes on [Link]=[Link]
étudiants
groupes
nom prenom groupe
numero salle
Dupont Alfred 2 ⋊
⋉groupe=numero
1 B-27
Smith John 1
2 A-14
Durand Alfred 1
nom prenom groupe numero salle
Smith John 1 1 B-27
Durand Alfred 1 1 B-27
Dupont Alfred 2 2 A-14
BDD1 43 / 82
Jointure
Les alias de nom de table
Dans une requête select, on peut attribuer un alias à chaque table
(nom local valide uniquement dans la requête)
pour simplifier ou clarifier l’écriture
pour réaliser une auto-jointure (jointure d’une table avec
elle-même)

Exemple
select [Link], [Link], [Link], [Link]
from etudiants as t1
join groupes as t2
on [Link]=[Link]
nom prenom groupe salle
Smith John 1 B-27
Durand Alfred 1 B-27
Dupont Alfred 2 A-14
BDD1 44 / 82
Les différents types de jointure

Jointure interne : inner join

seules les lignes de chaque table qui répondent à la condition
de jointure figurent dans le résultat
c’est la jointure par défaut (le mot inner est optionnel)

Jointures externes : left join, right join, full join

left join
contient tous les tuples de la jointure interne
de plus, les tuples de la 1ère table (« gauche ») qui ne
remplissent jamais la condition de jointure sont conservés
(avec état NULL pour les attributs correspondant à la
deuxième table)
right join : idem, symétriquement
full join : idem, dans les 2 sens.

BDD1 45 / 82
Jointure : join/inner join

Jointure interne (join ou inner join )

étudiants
groupes
nom prenom groupe
numero salle
Dupont Alfred 2
1 B-27
Smith John 1
2 A-14
Cheval Aline 4
3 A-4
Durand Alfred 1
select * from etudiants
join groupes on [Link]=[Link]
nom prenom groupe numero salle
Smith John 1 1 B-27
Durand Alfred 1 1 B-27
Dupont Alfred 2 2 A-14

BDD1 46 / 82
Jointure : left join

Jointure externe gauche (left join ou left outer join )

étudiants
groupes
nom prenom groupe
numero salle
Dupont Alfred 2
1 B-27
Smith John 1
2 A-14
Cheval Aline 4
3 A-4
Durand Alfred 1
select * from etudiants
left join groupes on [Link]=[Link]
nom prenom groupe numero salle
Smith John 1 1 B-27
Durand Alfred 1 1 B-27
Dupont Alfred 2 2 A-14
Cheval Aline 4 NULL NULL

BDD1 47 / 82
Jointure : right join

Jointure externe droite (right join ou right outer join )

étudiants
groupes
nom prenom groupe
numero salle
Dupont Alfred 2
1 B-27
Smith John 1
2 A-14
Cheval Aline 4
3 A-4
Durand Alfred 1
select * from etudiants
right join groupes on [Link]=[Link]
nom prenom groupe numero salle
Smith John 1 1 B-27
Durand Alfred 1 1 B-27
Dupont Alfred 2 2 A-14
NULL NULL NULL 3 A-4

BDD1 48 / 82
Jointure : full join
Jointure externe complète (full join ou full outer join )
étudiants
groupes
nom prenom groupe
numero salle
Dupont Alfred 2
1 B-27
Smith John 1
2 A-14
Cheval Aline 4
3 A-4
Durand Alfred 1
select * from etudiants
full join groupes on [Link]=[Link]
nom prenom groupe numero salle
Smith John 1 1 B-27
Durand Alfred 1 1 B-27
Dupont Alfred 2 2 A-14
Cheval Aline 4 NULL NULL
NULL NULL NULL 3 A-4
BDD1 49 / 82
SQL : Les fonctions de regroupement (ou agrégation)

Exemple
Soit une table de relevé téléphonique (decompte)
date numero cout
2016-01-05 +33320434343 0.40
2016-02-29 +33320434343 0.10
2016-01-05 +33320434343 0.05
2016-01-05 +33328778551 0.12
2016-02-29 +33320434343 0.60
Comment obtenir la somme des coûts ?

select sum(cout) from decompte

sum(cout)
Le résultat comporte une seule ligne :
1.27

BDD1 50 / 82
SQL : Les fonctions de regroupement

les expressions rencontrées jusqu’à maintenant renvoyaient un

calcul pour chaque ligne.
au contraire, la fonction sum() a fourni un seul résultat à
partir de plusieurs lignes (un groupe de lignes)
dans notre exemple toutes les lignes sont réunies en un unique
groupe et la fonction renvoie un résultat pour ce groupe.

Exemple
select sum(cout) from decompte

date numero cout 

sum(cout)
2016-01-05 +33320434343 0.40 

2016-02-29 +33320434343 0.10




2016-01-05 +33320434343 0.05 1.27

2016-01-05 +33328778551 0.12 



2016-02-29 +33320434343 0.60

BDD1 51 / 82
SQL : Les fonctions de regroupement

Fonctions de regroupement prédéfinies

nom rôle argument
COUNT dénombrement quelconque
AVG moyenne numérique
SUM somme numérique
MAX maximum quelconque
MIN minimum quelconque

NB :
count(*) compte le nombre de lignes
count( attribut ) compte le nombre de lignes dont l’attribut n’est
pas NULL

PostgreSQL propose d’autres fonctions de regroupement, qui

n’existent pas nécessairement dans un autre SGBD
BDD1 52 / 82
SQL : Les fonctions de regroupement

NB :
count(*) compte le nombre de lignes
count( attribut ) compte le nombre de lignes dont l’attribut n’est
pas NULL

Exemple
select count(*) from decompte
ou
select count(cout) from decompte

date numero cout 

count(*)
2016-01-05 +33320434343 0.40 

2016-02-29 +33320434343 0.10




2016-01-05 +33320434343 0.05 5

2016-01-05 +33328778551 0.12 



2016-02-29 +33320434343 0.60


BDD1 53 / 82
SQL : Les fonctions de regroupement

NB :
count(*) compte le nombre de lignes
count( attribut ) compte le nombre de lignes dont l’attribut n’est
pas NULL

Exemple
select count(cout) from decompte

date numero cout 

count(cout)
2016-01-05 +33320434343 0.40 

2016-02-29 +33320434343 0.10




2016-01-05 +33320434343 NULL 3

2016-01-05 +33328778551 0.12 



2016-02-29 +33320434343 NULL


BDD1 54 / 82
SQL : Les fonctions de regroupement

Exemple
select avg(cout) as moyenne, count(*) as nombre
from decompte

date numero cout 

moyenne nombre
2016-01-05 +33320434343 0.40 

2016-02-29 +33320434343 0.10




2016-01-05 +33320434343 0.05 0.254 5

2016-01-05 +33328778551 0.12 



2016-02-29 +33320434343 0.60


BDD1 55 / 82
SQL : Les fonctions de regroupement

Exemple
select avg(cout) as moyenne, count(cout) as nombre
from decompte

date numero cout 

moyenne nombre
2016-01-05 +33320434343 0.40 

2016-02-29 +33320434343 0.10




2016-01-05 +33320434343 NULL ?? ??

2016-01-05 +33328778551 0.12 



2016-02-29 +33320434343 NULL


BDD1 56 / 82
SQL : Les fonctions de regroupement

Exemple
select avg(cout) as moyenne, count(cout) as nombre
from decompte

date numero cout 

moyenne nombre
2016-01-05 +33320434343 0.40 

2016-02-29 +33320434343 0.10




2016-01-05 +33320434343 NULL 0.2067 3

2016-01-05 +33328778551 0.12 



2016-02-29 +33320434343 NULL


BDD1 57 / 82
SQL : Les fonctions de regroupement : group by

la clause group by
précise le critère de constitution des groupes
toutes les lignes ayant le même résultat vis à vis du critère
sont dans un même groupe

Exemple (sans le group by)

select sum(cout) from decompte

date numero cout 

sum(cout)
2016-01-05 +33320434343 0.40 

2016-02-29 +33320434343 0.10




2016-01-05 +33320434343 0.05 1.27

2016-02-29 +33320434343 0.60 



2016-01-05 +33328778551 0.12


BDD1 58 / 82
SQL : Les fonctions de regroupement : group by
la clause group by
précise le critère de constitution des groupes
toutes les lignes ayant le même résultat vis à vis du critère
sont dans un même groupe

Exemple (avec le group by)

Question : trouver le coût total des appels du même numéro de
téléphone
select ??
date numero cout ??
2016-01-05 +33320434343 0.40
2016-02-29 +33320434343 0.10
2016-01-05 +33320434343 0.05
2016-02-29 +33320434343 0.60
2016-01-05 +33328778551 0.12
BDD1 59 / 82
SQL : Les fonctions de regroupement : group by

Exemple (avec le group by)

Question : trouver le coût total des appels du même numéro de
téléphone
select sum(cout) from decompte group by numero

numero
date numero cout  sum(cout)
2016-01-05 +33320434343 0.40 


2016-02-29 +33320434343 0.10 
1.15
2016-01-05 +33320434343 0.05 


2016-02-29 +33320434343 0.60 
o
2016-01-05 +33328778551 0.12 0.12

BDD1 60 / 82
SQL : Les fonctions de regroupement : group by
dans la clause group by, on peut
utiliser plusieurs attributs comme critère de regroupement

Exemple
select sum(cout) from decompte group by date,numero

date,numero
date numero cout ) sum(cout)
2016-01-05 +33320434343 0.40
0.45
2016-01-05 +33320434343 0.05 )
2016-02-29 +33320434343 0.60
0.70
2016-02-29 +33320434343 0.10 o
2016-01-05 +33328778551 0.12 0.12
Quelle interprétation de cette requête ?
BDD1 61 / 82
SQL : Les fonctions de regroupement : group by

Exemple
select sum(cout) from decompte group by date,numero

date,numero
date numero cout ) sum(cout)
2016-01-05 +33320434343 0.40
0.45
2016-01-05 +33320434343 0.05 )
2016-02-29 +33320434343 0.60
0.70
2016-02-29 +33320434343 0.10 o
2016-01-05 +33328778551 0.12 0.12
Quelle interprétation de cette requête ?
⇒ le coût total des appels du même numéro aux mêmes dates

BDD1 62 / 82
SQL : Les fonctions de regroupement : group by

Les attributs qui servent de critère de regroupement peuvent faire

partie du schéma

Exemple (correct)
select date, numero, sum(cout) as total
from decompte
group by date,numero
date numero total
2016-01-05 +33320434343 0.45
2016-02-29 +33320434343 0.70
2016-10-05 +33328778551 0.12

Exemple (incorrect) ⇒ erreur SQL

select numero, date, sum(cout) as total
from decompte
group by numero
BDD1 63 / 82
SQL : Les fonctions de regroupement : group by
Exemple (incorrect) ⇒ erreur SQL
select numero, date, sum(cout) as total from decompte
group by numero

En SQL, lorsqu’on utilise une clause group by, toutes les

colonnes non agrégées dans la clause select (c-à-d celles
qui ne sont pas utilisées avec une fonction d’agrégation
comme sum, avg, etc.) DOIVENT ÊTRE INCLUSES dans
la clause group by.
⇒ Dans cet exemple, les colonnes non agrégées dans la clause
select sont : numero et date
⇒ numero et date doivent apparaître dans le group by

Exemple (forme corrigée)

select numero, date, sum(cout) as total from decompte
group by numero, date
BDD1 64 / 82
Les fonctions de regroupement : un autre exemple

>>
Exemple
Trouver le nombre de coureurs dans chaque équipe

BDD1 65 / 82
Les fonctions de regroupement : un autre exemple

>>
Exemple
Trouver le nombre de coureurs dans chaque équipe
select count(dossard) as nombreCoureurs, equipe
from coureurs
group by equipe

>>
BDD1 66 / 82
Les fonctions de regroupement

la clause having
permet de faire une sélection portant sur
le résultat d’une fonction de regroupement.
La clause having est liée à une clause « group by ».

Exemple
date numero cout
2016-01-05 +33320434343 0.40
2016-02-29 +33320434343 0.10
2016-01-05 +33320434343 0.05
2016-01-05 +33328778551 0.12
2016-02-29 +33320434343 0.60
Q1 : Trouver les numéros de téléphones où la somme des coûts est
supérieure à 0.5 pour les mêmes dates

BDD1 67 / 82
Les fonctions de regroupement

la clause having
permet de faire une sélection portant sur
le résultat d’une fonction de regroupement.
La clause having est liée à une clause « group by ».

Exemple
Q1 : Trouver les numéros de téléphones où la somme des coûts
est supérieure à 0.5 pour les mêmes dates
select date,numero,sum(cout) from decompte
group by date,numero
having sum(cout)>0.5
date numero sum(cout)
2016-02-29 +33320434343 0.7

BDD1 68 / 82
Les fonctions de regroupement

Exemple : requête originale

select date,numero,sum(cout) from decompte
group by date,numero
having sum(cout)>0.5

Cette requête peut être décomposée en deux

1- Aggrégation des données : donne une table (decompte_inter )
select date, numero, sum(cout)
from decompte
group by date, numero;
date numero sum(cout)
2016-01-05 +33320434343 0.45
2016-02-29 +33320434343 0.70
2016-10-05 +33328778551 0.12
2- Filtrage des résultats de la première requête
BDD1 69 / 82
Les fonctions de regroupement

Exemple : requête originale

select date,numero,sum(cout) from decompte
group by date,numero
having sum(cout)>0.5

Cette requête peut être décomposée en deux

1- Aggrégation des données :
date numero sum(cout)
2016-01-05 +33320434343 0.45
2016-02-29 +33320434343 0.70
2016-10-05 +33328778551 0.12
2- Filtrage des résultats de la première requête :
date numero sum(cout)
2016-02-29 +33320434343 0.70

BDD1 70 / 82
Les fonctions de regroupement

la clause having
permet de faire une sélection portant sur
le résultat d’une fonction de regroupement.
La clause having est liée à une clause « group by ».

Exemple
date numero cout
2016-01-05 +33320434343 0.40
2016-02-29 +33320434343 0.10
2016-01-05 +33320434343 0.05
2016-01-05 +33328778551 0.12
2016-02-29 +33320434343 0.60
Q2 : Trouver les numéros de téléphones qui ne figurent qu’une
seule fois dans le relevé téléphonique, ainsi que la somme de leurs
coûts ?
BDD1 71 / 82
Les fonctions de regroupement

Réponse à Q2
select numero,sum(cout) from decompte
group by numero
having count(*)=1;
numero sum(cout)
+33328778551 0.12

BDD1 72 / 82
Les fonctions de regroupement

Réponse à Q2 : explication
1- on groupe par numéro de téléphone et on calcule la somme
des coûts pour chaque groupe

numero
date numero cout  sum(cout)
2016-01-05 +33320434343 0.40 
2016-02-29 +33320434343 0.10

1.15
2016-01-05 +33320434343 0.05 

2016-02-29 +33320434343 0.60
2016-01-05 +33328778551 0.12 0.12
2- on conserve le(s) groupe(s) qui sont sur une seule ligne
le groupe +33320434343 est sur 4 lignes, il est donc exclu.
le groupe +33328778551 est sur 1 ligne, il est donc conservé.
numero sum(cout)
+33328778551 0.12

BDD1 73 / 82
Les fonctions de regroupement

where vs having
where est une qualification des lignes de la table source, donc
s’applique
avant regroupement

having est une qualification portant sur le

résultat d’une fonction de regroupement, donc s’applique
après regroupement

BDD1 74 / 82
where vs. having : exemple
date numero cout
2016-01-05 +33320434343 0.40
2016-02-29 +33320434343 0.10
2016-01-05 +33320434343 0.05
2016-01-05 +33328778551 0.12
2016-02-29 +33320434343 0.60

where
select numero, sum(cout) from decompte
where cout > 0.1
group by numero;
La clause where filtre d’abord les lignes où le coût (cout) est
supérieur à 0.1 avant de regrouper les résultats par numéro.
Ensuite, les lignes restantes sont regroupées par numero, et la
somme des coûts est calculée pour chaque groupe.

BDD1 75 / 82
where vs. having : exemple
date numero cout
2016-01-05 +33320434343 0.40
2016-02-29 +33320434343 0.10
2016-01-05 +33320434343 0.05
2016-01-05 +33328778551 0.12
2016-02-29 +33320434343 0.60

having
select numero, sum(cout) from decompte
group by numero
having sum(cout) > 0.5;
La clause group by regroupe d’abord les lignes par numéro de
téléphone.
Ensuite, la clause having filtre les groupes dont la somme des
coûts (sum(cout)) est supérieure à 0.5.

BDD1 76 / 82
Ajout de données : commande insert into

insert permet d’ajouter des tuples dans une table

Forme simple :
insert into nomTable values (liste valeurs)
insert into concerts values ('Zénith','Chose')
Fournir toutes les valeurs, dans l’ordre des attributs.
On peut spécifier les attributs :
insert into nomTable (liste attrs) values (liste valeurs)
insert into concerts (salle,artiste)
values ('Zénith','Chose')
insert into concerts (artiste,salle)
values ('Chose','Zénith')
On peut omettre certains attributs (leur état sera
NULL).

BDD1 77 / 82
Ajout de données : insert ... select

Les valeurs à ajouter peuvent résulter d’un select.

insert into nom de table (liste d’attributs)
select suite du select

Le select doit renvoyer autant de colonnes qu’il y a d’attributs

dans la liste.
Les types des données doivent être compatibles
Cette forme de commande insert permet d’ajouter plusieurs
tuples d’un coup (chaque ligne résultant du select)
Permet de faire simplement une recopie des données d’une
table.

BDD1 78 / 82
Suppression de données : commande delete

supprimer une ou des lignes

delete from nom de table where qualification

(la clause where est optionnelle).

Supprime de la table tous les tuples satisfaisant la qualification.
Exemples
delete from concerts where salle='Splendid'
détruit toutes les lignes correspondantes.
delete from concerts
vide la table...
Cette opération est irréversible !

BDD1 79 / 82
Modification de données : commande update

update nom de table set attribut = expression where

qualification

(la clause where est optionnelle).

Exemple : remplacement du coût TTC par le coût HT
update decompte set cout=cout/1.2
Là aussi, l’opération est irréversible.
update decompte set cout=0

BDD1 80 / 82
Les principaux types de données (variable selon SGBD)
Numériques
INTEGER Entier signé, sur 32 bits
SMALLINT Entier signé, sur 16 bits
BIGINT Entier signé, sur 64 bits
NUMERIC(n,d) n chiffres dont d après la virgule
REAL Flottant
Textuels
CHAR(n) Chaîne de n caractères exactement.
VARCHAR(n) Chaîne de n caractères maximum.
TEXT Texte.
Dates
DATE date A-M-J
TIME temps H:MN:S
TIMESTAMP temps (estampille)
Binaires
BYTEA donnée binaire (NB :dénomination Postgresql)
BDD1 81 / 82
Création de table

commande create table

Syntaxe simplifiée :

create table nom de table ( attribut type, ... )

Exemple

create table etudiants

( nom varchar (20) ,
prenom varchar (30) ,
groupe smallint ,
naissance date
)

BDD1 82 / 82