1
UNIL & HEIG-VD Modéliser par l'exemple…
La base de donnée relationnelle est l’implémentation informatique du Modèle
relationnel vu précédemment.
Référence livre
- p. 109
2
UNIL & HEIG-VD Modéliser par l'exemple…
On peut ajouter, mettre à jour, supprimer et surtout rechercher des données avec le
langage SQL.
Apprendre à faire des requêtes (recherches) est le but principal de ces leçons.
Référence livre
- pp. 113-114
3
UNIL & HEIG-VD Modéliser par l'exemple…
SQL permet de faire tout ce dont on a besoin : définition des données (LDD),
modification et interrogation (recherche) (LMD)
Référence livre
- pp. 113-114
4
UNIL & HEIG-VD Modéliser par l'exemple…
Les types de données (ce qui correspond aux domaines) sont limités.
Les plus utiles sont typiquement les nombres (FLOAT, [Link]. pour les nombres à
virgule), les textes (CHAR et VARCHAR) et les DATE et TIME.
Comme on va vous fournir des bases de données préremplies avec des exemples,
les types de données sont normalement déjà créés pour vous.
[Link]., si on gère un catalogue de bibliothèque, on aura créé poru vous des livres avec
des titres, des dates d’emprunt, etc.
Référence livre
- p. 109
5
UNIL & HEIG-VD Modéliser par l'exemple…
Les données qu’on rentre sont des ensembles de valeurs de types identiques (tuples).
La table VEHICULE représente des véhicules de la même classe, c’est à dire ayant des
attributs similaires (NoChassis, Marque...)
La Cylindree est optionnelle ici : quand on ne la connaît pas, on indique <NULL> (=
vide)
Référence livre
- p. 110
6
UNIL & HEIG-VD Modéliser par l'exemple…
Chaque colonne possède des variables d’un même domaine (d’un même type)
- [Link]. la table Vehicule a dans sa colonne NoChassis des numéros de référence sous
forme textuelle : AA123JJ, ABC123YXZ, ZYX987ABC, ZZ-123ABC et 987ABC123
Référence livre
- p. 109
7
UNIL & HEIG-VD Modéliser par l'exemple…
Quand je parle d’UN véhicule particulier, celui-ci correspond à UNE ligne.
Dans la table Vehicule, je saisis un véhicule sur une ligne. Je remplis les colonnes
NoChasis, Marque, Modele, Annee et Cylindree avec les valeurs AA123JJ, VW, Golf,
2003 et 1.9
Référence livre
- p. 110
8
UNIL & HEIG-VD Modéliser par l'exemple…
Exemples de tables pour une assurance :
-Clients
-Véhicules de clients
-Sinistre = un accident : avec un numéro de référence, une date et le montant des
frais de réparation
-Contrat = avec un numéro, un type de couverture d’assurance et la date à laquelle il
a été conclu.
On voit ici qu’il y a [Link]. 3 lignes dans sinistres, donc 3 sinistres ont été rentrés dans la
base de données.
Référence livre
- p. 111
9
UNIL & HEIG-VD Modéliser par l'exemple…
La clé permet de trouver une ligne.
« Si je te donne la clé XXX peux-tu trouver la ligne qui correspond ? »
Pour un Sinistre, deux numéros de Référence peuvent être identiques (2x le chiffre 1
ici). C’est la référence + date qui sera la clé. Autrement dit, pour un numéro de
référence et une date donnés, on est capables de trouver le sinistre.
Ex de clé secondaire : une table des clients possédant chacun un numéro unique. Ce
numéro est la clé primaire. Si la même table possède aussi un noAVS, ce numéro est
également unique (noAVS est le numéro unique utilisé en Suisse pour les personnes
qui travaillent). noAVS pourrait aussi être une clé : c’est donc une clé secondaire.
En général, on n’utilise que des clés primaires qui sont suffisantes.
Référence livre
- pp. 111-112
10
UNIL & HEIG-VD Modéliser par l'exemple…
(Cylindree) entre parenthèses dans l’entête de colonne indique que l’attribut peut-
être vide. On met alors <NULL> dans la ligne
Référence livre
- p. 112
11
UNIL & HEIG-VD Modéliser par l'exemple…
Remarquez les clés qui sont soulignées.
Référence livre
- p. 113
12
UNIL & HEIG-VD Modéliser par l'exemple…
Voici l’écriture pour créer une table en SQL. Vous n’avez pas besoin de la retenir.
On voit :
- les types d’attributs (INTEGER, CHAR...)
- quand l’attribut est obligatoire (NOT NULL)
-quelle est la clé primaire (PRIMARY KEY)
Référence livre
- p. 115
13
UNIL & HEIG-VD Modéliser par l'exemple…
L’écriture SQL pour insérer une ligne (il n’y a pas besoin de la retenir)
J’ajoute une ligne avec 1, Bersier et Lussy dans le même ordre qu’au-dessus.
Référence livre
- p. 115
14
UNIL & HEIG-VD Modéliser par l'exemple…
Exemple facultatif pour créer une base de données soi-même.
Vous pouvez installer SQLite Browser sur toute machine (PC, Mac, etc.) à partir de
[Link]
Créez une nouvelle base comme montré ici.
Comme déjà dit, dans nos exercices et exemples, les bases sont déjà créées pour
vous, vous n’avez pas besoin de SQLite Browser (vous utiliserez notre système SQL
Explorer)
15
UNIL & HEIG-VD Modéliser par l'exemple…
[Link] Définition des tables et des colonnes par l’interface graphique (suite)
La définition de la clé primaire se fait en :
- sélectionnant une ou plusieurs lignes (qui représente(nt) la ou les colonnes du
schéma relationnel…
- cliquant sur le bouton droit (menu contextuel) et en choisissant Clé primaire.
La dernière étape consiste à définir les colonnes qui doivent être obligatoires (càd
celles qui ne sont pas entre parenthèses ou suffixés par [0..1] dans un schéma
relationnel). Pour ce faire, il faut :
- sélectionner la colonne à marquer comme obligatoire
- Dans l’onglet Général :
- choisir Oui pour le champ Null interdit et Non pour Chaîne vide autorisée dans le
cas d’un texte
- choisir Oui pour le champ Null interdit dans le cas d’un numérique
16
UNIL & HEIG-VD Modéliser par l'exemple…
Voici les tables créées dans SQLite. Maintenant, vous savez comment nous créons vos
exercices.
17
UNIL & HEIG-VD Modéliser par l'exemple…
On va maintenant apprendre les bases d’SQL.
SQL ne pose pas de question techniques (i.e., où est mon fichier, etc.) C’est un
langage facile à apprendre. Ce sont les requêtes qui vont nous intéresser.
Référence livre
- p. 116
18
UNIL & HEIG-VD Modéliser par l'exemple…
Commencez toujours votre requête en écrivant :
SELECT
FROM
WHERE
et en complétant ensuite. Vous ne pouvez pas vous tromper !
Référence livre
- p. 116
19
UNIL & HEIG-VD Modéliser par l'exemple…
Comment chercher le nom des clients avec SQL ?
SELECT ce que je cherche
FROM la table où ça se trouve
SELECT Nom (c’est le titre de la colonne que je recopie exactement)
FROM Client (c’est le nom de la table que je recopie exactement)
comme je prends toutes les lignes, il n’y a pas besoin de WHERE.
Je viens de faire une projection (cf cours précédent) où je n’ai retenu que la colonne
Nom
Référence livre
- p. 117
20
UNIL & HEIG-VD Modéliser par l'exemple…
Essayez maintenant cette requête dans SQL Explorer (voir notre site web).
Pour comprendre ce cours, il faut tester dans SQL Explorer.
21
UNIL & HEIG-VD Modéliser par l'exemple…
Je pars de mon
SELECT
FROM
WHERE
Il me faut le nom et l’adresse
SELECT Nom, Adresse (encore une fois, recopiez EXACTEMENT le
titre des colonnes)
où se trouvent-ils ? Dans la table Client
FROM Client
Est-ce que je veux tous les clients ? Non, seulement ceux qui sont à
Lausanne. Comment je sais cela ? L’adresse doit être Lausanne. Comme
Lausanne est un texte, je dois mettre des apostrophes autour '....' :
WHERE Adresse = 'Lausanne'
22
Référence livre
- p. 118
22
UNIL & HEIG-VD Modéliser par l'exemple…
Testez cette requête dans SQL Explorer
23
UNIL & HEIG-VD Modéliser par l'exemple…
On me demande les clients sans préciser quels attributs ou colonnes m'intéressent.
Je peux utiliser * dans SELECT pour récupérer toutes les colonnes.
J'ai sélectionné des lignes, c'est une sélection en modèle relationnel.
------
SELECT *
FROM MaTable
est aussi très pratique pour voir ce que contient une table
Vous pouvez tester dans SQL Explorer.
24
UNIL & HEIG-VD Modéliser par l'exemple…
On voit dans SQL Explorer que l'ordre des lignes est aléatoire.
ORDER BY = Trier les lignes
25
UNIL & HEIG-VD Modéliser par l'exemple…
ORDER BY vient à la fin de la requête, donc après le WHERE.
Référence livre
- p. 119
26
UNIL & HEIG-VD Modéliser par l'exemple…
Le format dates est aussi encadré par des apostrophes '...'
Date > '2009-01-01' renvoie vrai ou faux (suivant si la date est postérieure ou non).
NOT inverse le résultat
AND et OR combinent deux tests (on a déjà vu ces opérateurs)
Référence livre
- p. 120
27
UNIL & HEIG-VD Modéliser par l'exemple…
Je pars de
SELECT
FROM
WHERE
On me parle des sinistres sans préciser de colonnes :
SELECT *
Où il se trouvent ? Dans la table Sinistre
FROM Sinistre
maintenant j'ai deux conditions :
postérieure au 1er janvier 2009 => Date > '1/1/2009'
dont le montant est supérieur à CHF 3'000.- => Montant > 3000
les deux choses doivent être vraies en même temps => AND
la condition est donc, schématiquement
WHERE date postérieure ET montant supérieur
soit en écriture correcte :
WHERE Date > '1/1/2009' AND Montant > 3000
(on peut mettre le AND à la ligne ou non, mettre des majuscules ou non, c'est égal,
c'est juste pour faire joli).
28
Référence livre
- p. 120
28
UNIL & HEIG-VD Modéliser par l'exemple…
NULL teste si une colonne facultative est remplie ou non grâce au test IS NULL / IS
NOT NULL
Pour tester si le Type (chaîne de caractèrse) est 'RC', ou 'Casco partielle' ou
'Casco totale', j'ai fait un groupe avec les trois valeurs et
je vérifie si Type se trouve dedans.
Même idée pour NOT IN
On teste si Montant est compris entre 0 (inclus) et 9000 (inclus)
LIKE est une recherche approximative sur du texte : le signe % signifie "N'importe
quelle série de caractères".
[Link]. Type = 'RC + Casco complète'
Type LIKE 'RC + Casco%' est vrai (% correspond à ' complète')
Type NOT LIKE 'RC + Casco%' est faux
Autre exemple : Nom = 'Jean-Sébastien Monzani' => Nom LIKE
= 'Jean%Monzani' est vrai.
29
Référence livre
- p. 121
29
UNIL & HEIG-VD Modéliser par l'exemple…
J'aimerais chercher les contrats qui commencent par 'RC + Casco' : ils
peuvent être suivis de 'partielle' ou 'complète'.
-> Type LIKE 'RC + Casco%'
Référence livre
- p. 120
30
UNIL & HEIG-VD Modéliser par l'exemple…
Deux nouveautés dans le SELECT :
- SELECT 'Un texte' crée une colonne constante avec le même texte sur toutes les
lignes. Sert juste à rendre les choses plus claires.
- Je peux tout à fait faire des calculs dans le SELECT, comme ailleurs.
Comme d'habitude, vous pouvez tester dans SQL Explorer
Référence livre
- p. 121
31
UNIL & HEIG-VD Modéliser par l'exemple…
A la page précédente, on voit que le titre de la colonne était 0.81 * Montant. Ce n'est
pas très joli.
[Link]., on pourrait mettre, si il s'agit d'euros MontantEnEuros
SELECT colonne AS NouveauNom donne un nouveau nom à la
colonne.
Là aussi, c'est pour rendre les choses plus claires.
Référence livre
- p. 121
32
UNIL & HEIG-VD Modéliser par l'exemple…
Si je ne mets pas le DISTINCT (faites l'essai dans SQL Explorer), j'obtiens plusieurs fois
les mêmes villes :
Lussy
Lausanne
Les Avants
Lausanne
Savigny
SELECT DISTINCT Colonne permet d'éviter les répétitions (ce qu'on appelle des
doublons). La projection est ainsi plus propre.
Référence livre
- p. 121
33
UNIL & HEIG-VD Modéliser par l'exemple…
On connait les fonctions d'agrégation dans les modèles de calcul : à partir d'une série
de valeurs, on va calculer un résultat (la somme [Link].)
Ici, on va appliquer ces fonctions sur des colonnes.
34
UNIL & HEIG-VD Modéliser par l'exemple…
Utilisation du AS pour avoir un titre de colonne plus clair dans le résultat: c'est une
façon de renommer une colonne de la table résultante.
35
UNIL & HEIG-VD Modéliser par l'exemple…
COUNT compte les lignes
Ca n'a rien à voir avec la somme des valeurs , cela produit le nombre de valeurs
trouvées.
36
UNIL & HEIG-VD Modéliser par l'exemple…
COUNT(DISTINCT Marque) évite de compter deux fois VW....
37
UNIL & HEIG-VD Modéliser par l'exemple…
On ne peut pas mettre de fonctions d'agrégation dans WHERE.
Les fonctions d'agrégation ont besoin de plusieurs lignes pour faire un calcul (pour
faire la moyenne des cylindrées, j'ai besoin de les connaître toutes).
WHERE est une condition sur UNE ligne, donc pour une ligne donnée, je ne connais
qu'une cylindrée. Je ne peux donc pas calculer la moyenne.
38
UNIL & HEIG-VD Modéliser par l'exemple…
Je dois procéder en deux étapes :
- calculer la moyenne (appelons-la M)
- choisir les véhicules dont la Cylindrée est supérieure à M
Si je connais M, c'est facile :
SELECT *
FROM Vehicule
WHERE Cylindree > M
Comment calculer M ?
M est simplement la moyenne des cylindrées. Je parcours donc
toutes les cylindrées et je calcule la moyenne.
M = SELECT AVG(Cylindree)
FROM Vehicule
Je combine les deux en mettant ce que M contient entre
39
parenthèses.
SELECT *
FROM Vehicule
WHERE Cylindree > (M)
donc au final ma requête est :
SELECT *
FROM Vehicule
WHERE Cylindree > (
SELECT AVG(Cylindree)
FROM Vehicule)
M est une sous requête.
Référence livre
- p. 120
39
UNIL & HEIG-VD Modéliser par l'exemple…
40