to get the file

SQL
« Structured Query Language »
Le Langage de Définition et d’Exploitation
des Bases de Données Relationnelles
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
Bases de Données Relationnelles
Le Langage de Définition et d’Interrogation
des Bases de Données Relationnelles
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
Notion de Base de Données
• Ensemble Structuré de Données
–
–
–
–
centralisation des données
non redondance des données
intégrité des données
diffusion des données
• Systèmes de Gestion de Bases de Données (SGBD)
– interaction avec la base de données
– langage de définition
– langage de manipulation
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
Principe d’une Base de Données
Monde réel
Schéma
externe
Schéma
externe
Modélisation
Schéma
conceptuel
Schéma
externe
Niveau
externe
Schéma
physique
Base de
données
physique
Niveau
conceptuel
Niveau
interne
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
Mise en Oeuvre d'un SGBD
• Langage de Définition (DDL)
– spécification du schéma conceptuel
– correspondance entre schéma conceptuel et schéma interne
• Langage de Manipulation (DML)
– maintenance des données
– interrogation interactive
• sélections
• vues
– utilisation par des programmes
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
SGBD Relationnels
• Modèle : tables ou relations
– Lignes = n-uplets
– Colonnes = domaines de valeurs
• Schéma conceptuel
– Ensemble de tables
– Les clés primaires des tables
• Langage de manipulation
– SQL ("Structured Query Language")
– Interfaces graphiques
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
Schéma Conceptuel
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
Structured Query Language
Le Langage de Définition et d’Interrogation
des Bases de Données Relationnelles
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
SQL
• SQL est de fait :
– le langage de définition (DDL)
– et de manipulation (DML) des SGBD relationnels
• Définition des tables
–
–
–
–
Création
Mise à jour
Suppression
Création de clés et d'index
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
SQL – Exploitation des tables
• Modification du contenu des tables
– Insertion
– Mise à jour
– Suppression
• Exploration des tables
– Projection
– Sélection
– Jointure
• Fonctions supplémentaires
–
–
–
–
Tris (classements)
Expressions de colonnes
Regroupements
Fonctions de groupes
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
Algèbre Relationnelle
• Toute opération licite sur une ou plusieurs tables a
pour résultat une table
• Toute table produite par une opération peut être
réutilisée par une autre opération
• Cependant, les tables produites ne font pas partie du
schéma conceptuel de la base de données
– Production de nouvelles données (redondance)
– Pas de clés
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
Requête SQL
• Forme générale d’une requête SQL
– SELECT colonnes à afficher
– FROM tables à exploiter
– WHERE conditions sur des lignes
• SELECT et FROM sont obligatoires
• WHERE est facultatif
• Ordre logique
1 FROM tables à exploiter
2 SELECT valeurs (éventuellement calculées) à afficher
3 WHERE conditions à respecter
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
Projection
• Extraire un sous-ensemble de colonnes
• Exemples
– Afficher les
• nom,
• prénom et
• sexe
des patients
– Afficher
• L’identification du séjour
• Les SGOT et
• Les SGPT
des bilans hépatiques
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
SELECT "Nom", "Prénom", "Sexe"
FROM "Patient"
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
SELECT "IdSéjour", "SGOT", "SGPT"
FROM "BilanHép"
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
Sélection
• Extraire un sous-ensemble de lignes
• Mise en œuvre de WHERE
• Exemples
– Afficher les nom et prénom des patients de sexe féminin
– Afficher toutes les informations des séjours dont le motif était
C88.0
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
SELECT "Nom", "Prénom", "Sexe" FROM "Patient" WHERE ( ( "Sexe" = 'F' ) )
SELECT "Nom", "Prénom" FROM "Patient" WHERE ( ( "Sexe" = 'F' ) )
Affichage
inutile
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
SELECT *
FROM "Séjour"
WHERE ( "Motif" = 'C88.0' )
Toutes les
colonnes
Double
affichage
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
Critères de Sélection
• Opérateurs de comparaison usuels
– Egalité : = et inégalité : != ou <>
– Supérieur (ou égal) : > (ou >=) et inférieur (ou égal) : < (ou <=)
• Et des opérateurs spécifiques
–
–
–
–
(NOT) BETWEEN x AND y : teste si compris (hors) entre x et y
(NOT) IN : teste la présence (l'absence) dans une liste
LIKE : teste la ressemblance
IS (NOT) NULL : teste l'absence (la présence) d'information
• Combinaisons au moyen d’opérateurs booléens
– AND
– OR
– NOT
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
Les Opérateurs de Comparaison Usuels
• Ils s’appliquent à tous types de données à condition
de comparer des données de même type, par
exemple
– 01/07/2006 < 22/09/2006, antériorité
– Durand > Dupont, ordre alphabétique
• Exemple
– Quels sont les bilans hépatiques pratiqués avant le premier
février 1998 ?
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
SELECT *
FROM "BilanHép"
WHERE ( ( "Date" < {D '1998-02-01' } ) )
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
Les Opérateurs NULL et LIKE
• NULL permet de tester l’absence d’information
– Exemple :
• Rechercher le(s) patient(s) dont l’adresse n’est pas connue
• LIKE permet de rechercher une sous-chaîne de
caractères dans une donnée
– Exemple :
• Rechercher le(s) patient(s) dont le nom commence par la lettre P
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
SELECT "NumDossier", "Nom", "Prénom"
FROM "Patient"
WHERE ( ( "Adresse" IS NULL ) )
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
SELECT "NumDossier", "Nom", "Prénom"
FROM "Patient"
WHERE ( ( "Nom" LIKE 'P%' ) )
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
Combinaisons de Critères de Sélection
• Indiquer les formules de numération sanguine
présentant un nombre de GB inférieur à 5 et un
nombre de GR supérieur à 4,5
• Indiquer les séjours dont le motif est M32.9 ou M33.9
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
SELECT "IdSéjour", "Date", "GB", "GR"
FROM "NFS"
WHERE ( ( "GB" < 5 AND "GR" > 4.5 ) )
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
SELECT "IdSéjour", "NumDossier", "Motif"
FROM "Séjour"
WHERE ( ( "Motif" = 'M32.9' ) OR ( "Motif" = 'M33.9' ) )
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
Jointure
• Se réalise entre des tables ayant une colonne (domaine
de valeur) en commun
– Dans la clause WHERE, exemple
• SELECT "Patient"."NumDossier", "Patient"."Nom",
"Patient"."Prénom", "Séjour"."DateEntrée", "Séjour"."DateSortie"
FROM "Séjour", "Patient"
WHERE ( "Séjour"."NumDossier" = "Patient"."NumDossier" )
– Et/ou par des liens prédéfinis dans le schéma de la base,
exemple
• SELECT "Patient"."NumDossier", "Patient"."Nom",
"Patient"."Prénom", "Séjour"."DateEntrée", "Séjour"."DateSortie"
FROM "Patient" INNER JOIN "Séjour" ON
( "Patient"."NumDossier" = "Séjour"."NumDossier" )
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
SELECT "Patient"."NumDossier", "Patient"."Nom", "Patient"."Prénom",
"Séjour"."DateEntrée", "Séjour"."DateSortie"
FROM "Séjour", "Patient"
WHERE ( "Séjour"."NumDossier" = "Patient"."NumDossier" )
Jointure établie
automatiquement
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
SELECT "Patient"."NumDossier", "Patient"."Nom", "Patient"."Prénom",
"Séjour"."DateEntrée", "Séjour"."DateSortie"
FROM "Patient" INNER JOIN "Séjour" ON
( "Patient"."NumDossier" = "Séjour"."NumDossier" )
Jointure
imposée
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
In Fine : des Résultats Identiques
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
Right Join
Edition
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
SELECT "Séjour"."NumDossier", "BilanHép"."IdSéjour",
"BilanHép"."SGOT", "BilanHép"."SGPT"
FROM { OJ "BilanHép" "BilanHép" RIGHT OUTER JOIN "Séjour" ON
"BilanHép"."IdSéjour" = "Séjour"."IdSéjour" }
ORDER BY "Séjour"."NumDossier" ASC, "BilanHép"."IdSéjour" ASC
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
Left Join
• L'inverse de
RIGHT JOIN
Edition
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
Jointure Multiple
• Se réalise sur plusieurs tables que l’on peut « joindre »
deux à deux
– SELECT "Patient"."Nom", "Patient"."Prénom", "Séjour"."NumDossier",
"NFS"."GB", "NFS"."GR"
FROM ‘Patient’, ‘Séjour’, ‘NFS’
WHERE (‘Séjour’.’NumDossier’=‘Patient’.’NumDossier’) AND
(‘Séjour’.’IdSéjour’=‘NFS’.’IdSéjour’)
– SELECT "Patient"."Nom", "Patient"."Prénom", "Séjour"."NumDossier",
"NFS"."GB", "NFS"."GR"
FROM ("Patient" INNER JOIN "Séjour" ON "Patient"."NumDossier" =
"Séjour"."NumDossier") INNER JOIN "NFS" ON "Séjour"."IdSéjour" =
"NFS"."IdSéjour"
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
Exemple de Jointure Multiple
NumDossier peut être
sélectionné dans Patient
ou dans Séjour
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
Résultat d’une Jointure Multiple
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
Classement
• Présentation du résultat d'une requête selon un ordre
fixé par des valeurs de colonnes
• Exemple
– SELECT "Nom", "Prénom", "Sexe", "DateNaissance",
"NumDossier"
FROM "Patient"
ORDER BY "Nom" ASC, "Prénom" ASC
• Ordonnancement opéré selon l'ordre de gauche à
droite des champs indiqués
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
Exemple de Classement
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
SELECT "Nom", "Prénom", "Sexe", "DateNaissance", "NumDossier"
FROM "Patient"
ORDER BY "NumDossier" ASC
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
Expressions de Colonnes
• Avec des constantes et des données de même type
• Servent à effectuer des calculs et produire de
nouvelles données
• Arithmétiques
– +, -, *, /
– fonctions : ABS, SQRT, …
• Dates
– fonctions : YEAR, MONTH, …
• Chaînes de caractères
– & (concaténation)
– fonctions : CHR, REPLACE, …
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
Exemples d’Expressions de Colonnes
• Calcul de l’age des patients
– SELECT "Nom", "Prénom", 1999 - YEAR( "DateNaissance" )
FROM "Patient"
• Classement dans l’ordre décroissant
– SELECT "Nom", "Prénom", 1999 - YEAR( "DateNaissance" ) AS Age
FROM "Patient"
ORDER BY Age DESC;
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
SELECT "Nom", "Prénom", 1999 - YEAR( "DateNaissance" ) AS Age
FROM "Patient"
ORDER BY ( 1999 - YEAR( "DateNaissance" ) ) DESC
Age
Alias : Age
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
Age
Usage des « alias »
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
Regroupement
• Présentation des résultats d'une sélection regroupés
selon un critère
• Exemple : les différents motifs de séjour
– SELECT "Motif"
FROM "Séjour" "Séjour"
GROUP BY "Motif"
ORDER BY "Motif" ASC
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
SELECT "Motif"
FROM "Séjour"
GROUP BY "Motif"
ORDER BY "Motif" ASC
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
Fonctions de Groupes
• Sont généralement associées à la clause GROUP
BY d'une sélection
– AVG : moyenne
– COUNT(expr) : dénombre les lignes pour lesquelles "expr"
n'est pas null
– MAX : calcule le maximum
– MIN : calcule le minimum
– SUM : calcule la somme
– ......
• Mais pas obligatoirement
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
Exemples de Fonctions de Groupes
• Décompte des motifs de séjour
– SELECT "Motif", COUNT( "IdSéjour" )
FROM "Séjour"
GROUP BY "Motif"
ORDER BY COUNT( "IdSéjour" ) DESC
• Moyenne des Gamma GT
– SELECT AVG( "GammaGT" ) AS "Moyenne"
– FROM "BilanHép"
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
SELECT "Motif", COUNT( "IdSéjour" ) AS Nombre
FROM "Séjour"
GROUP BY "Motif"
ORDER BY COUNT( "IdSéjour" ) DESC
Nombre
NULL
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
SELECT AVG( "GammaGT" ) AS "Moyenne"
FROM "BilanHép"
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
Condition dans le Regroupement
• Les conditions de la clause HAVING opèrent sur les
lignes de la table produite
• Exemple
– SELECT "Motif", COUNT( "IdSéjour" )
FROM "Séjour"
GROUP BY "Motif" HAVING ( "Motif" IS NOT NULL )
ORDER BY COUNT( "IdSéjour" ) DESC
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
SELECT "Motif", COUNT( "IdSéjour" )
FROM "Séjour"
GROUP BY "Motif"
HAVING ( "Motif" IS NOT NULL )
ORDER BY COUNT( "IdSéjour" ) DESC
NULL
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
Notion de Vue
• Une vue est le résultat d'une requête
• Elle se présente comme une table
• Elle ne fait pas partie (du schéma) de la base de
données
• Pas de clé, pas d'index
• Exemple : les patients et les médecins qui les
soignent
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
Exemple de Vue
Alias
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006
Bibliographie
• Livres
– SQL pour les nuls. A Taylor. First Interactive
– SQL. F Brouard. Campus Press
• Sites Internet
– Introduction aux bases de données
http://www.commentcamarche.net/bdd/bddintro.php3
– Conception de bases de données
http://nte-socio.univ-lyon2.fr/Marc_Grange/BDConception.htm
– Liste de sites sur les bases de données et les SGBD
http://sgbd.developpez.com/cours/
Master EISIS – Michel JOUBERT – LERTIM, Faculté de Médecine, Marseille - 2006