Mise en exploitation : contenu du chapitre Modèle Relationnel Notions de table, clés et contraintes Schéma d’une base Langage SQL Création de table Types de données SQL LDD Mise à jour des données SQL LMD de Mise à jour Extraction de données SQL LMD d’extraction Programmation serveur Fonctions utilisateurs Triggers
Maîtriser : • Création tables avec contraintes • Création des fonctions stockées • Création de triggers
Base de données
45
45
Jérôme Fessy
Extraction de données Extraction de données Requêtes simples Sélection, Projection Fonctions PostgreSQL Fonctions d’agrégats Requêtes multi-tables Jointure Auto-jointure Objectif : Maîtriser l’extraction de données quelle que soit la question posée
Requêtes complexes Groupement Sous-requêtes
Données
46
46
Jérôme Fessy
La restriction ou sélection La restriction d'une table R selon un critère Q donne une table de même schéma, avec pour tuples résultats ceux de R qui satisfont le critère Q. Q est une expression composée de connecteurs (AND, OR, NOT) et de prédicats (<, >, =) portant sur les colonnes, évaluables à Vrai ou à Faux pour un tuple donné.
SOCIETE
RaisonSociale
Adresse
CP
Ville.
INDUS
13 rue basse
14000
Paris
PARCO
1 allée J.B. Lulli
75012
Paris
FINFO
3 rue de Verson
75016
Caen
CASIO
Bât 7, ZI Conex
95200
Sarcelles
Critère de restriction: Ville = ‘Paris’'
47
47
Jérôme Fessy
La projection La projection d'une table R sur un ensemble de colonnes donne une table ayant pour schéma cet ensemble de colonnes. Ses tuples sont ceux de la table R, réduits aux colonnes de la projection.
SOCIETE
RaisonSociale
Adresse
CP
Ville.
INDUS
13 rue basse
14000
Paris
PARCO
1 allée J.B. Lulli
75012
Paris
FINFO
3 rue de Verson
75016
Caen
CASIO
Bât 7, ZI Conex
95200
Sarcelles
Critère de projection: RaisonSociale et Adresse 48
48
Jérôme Fessy
Combinaison des deux opérations
SOCIETE
RaisonSociale
Adresse
CP
Ville.
INDUS
13 rue basse
14000
Paris
PARCO
1 allée J.B. Lulli
75012
Paris
FINFO
3 rue de Verson
75016
Caen
CASIO
Bât 7, ZI Conex
95200
Sarcelles
Les opérateurs de restriction et de projection peuvent se combiner pour sélectionner un résultat: Raison Sociale et Adresse des sociétés parisiennes ?
49
49
Jérôme Fessy
Sélection de lignes et colonnes
Sélection sans critère :
Sélection avec critère simple :
SELECT IntituleSem, DateSem FROM SEMINAIRE
SELECT * FROM SEMINAIRE WHERE Prix > 50
Colonnes résultat Critère booléen de sélection
Table(s) concernée(s) par la question
Règle : Les colonnes utilisées dans le SELECT et dans le WHERE appartiennent obligatoirement aux tables citées dans le FROM 50
Jérôme Fessy
* permet la sélection de toutes les colonnes des tables citées •
Le principe d’évaluation est le suivant : Chaque ligne de la table citée dans le « FROM » est examinée; lorsque le « WHERE » est vrai pour une ligne, celle-ci est sélectionnée. On ne garde en résultat pour cette ligne que les colonnes ou informations citées dans le « SELECT ».
•
Avec PostgreSQL seule la clause « SELECT » est obligatoire.
50
Expressions dans un SELECT Exemple 1 : SELECT IntituleSem, NbrePlaceMax * PrixSem FROM SEMINAIRE Exemple 2 (concaténation de chaînes ) : SELECT RaisonSociale, ‘Situé à ‘ || Adresse || ‘ ‘ || CP || ‘ ‘|| Ville FROM SOCIETE
51
Jérôme Fessy
Des
alias de colonnes sont recommandés pour les expressions
Une
expression prend la valeur NULL si l'un des opérandes vaut NULL
Certaines
fonctions PostgreSQL peuvent être utilisées pour l’élaboration des expressions du SELECT
51
Utilisation d’alias de colonnes Sélection sans alias de colonne : SELECT IntituleSem, DateSem FROM SEMINAIRE
Renommage de la colonne pour l’affichage
Sélection avec alias de colonne : SELECT IntituleSem AS Séminaire, DateSem AS ‘’Fait le’’ FROM SEMINAIRE
Règle : Une colonne en résultat peut être renommée par un alias pour - gagner en lisibilité - obligatoirement pour utiliser le résultat d’un calcul en programmation
52
Un
Jérôme Fessy
alias de colonne sert en terme de résultat et ne peut donc pas être utilisé dans les autres clauses de la requête (SAUF le ORDER BY).
Le
terme AS est optionnel, il aide à la lisibilité
52
Tri des résultats (1) Tri (par défaut dans l’ordre croissant) : SELECT * FROM SEMINAIRE ORDER BY IntituleSem
Tri dans l’ordre alphabétique croissant des intitulés
Tri multi-critères : SELECT IntituleSem, DateSem, PrixSem FROM SEMINAIRE ORDER BY DateSem DESC, IntituleSem ASC
Tri dans l’ordre décroissant de date, puis par ordre alphabétique croissant d’intitulés
Règle : Les colonnes utilisées dans le ORDER BY permettent de trier les résultats de manière croissante ASC (par défaut) ou décroissante DESC.
53
53
Jérôme Fessy
Tri des résultats (2) Limit sélectionne « n » 1ères lignes : SELECT * FROM SEMINAIRE ORDER BY DateSem DESC, IntituleSem LIMIT 10
Affichage des 10 premières lignes : 10 séminaires les plus récents
Offset saute les « n » 1ères lignes : SELECT IntituleSem, DateSem, PrixSem FROM SEMINAIRE ORDER BY PrixSem DESC, IntituleSem OFFSET 5
Affichage des séminaires triés par prix décroissants en ignorant les 5 premiers
54
Clauses
intéressantes volumineuses.
Si
pour
des
Jérôme Fessy
interrogations
de
tables
très
ces clauses sont utilisées ensemble : La
clause LIMIT précède la cause OFFSET
le
nombre de lignes fixé par la clause LIMIT ne sera pas compté avant le nombre de ligne fixé par la cause OFFSET
54
Gestion des doublons Liste des animateurs : SELECT ALL NomAnim, PrenomAnim FROM ANIMATEUR ORDER BY NomAnim, PrenomAnim
Il peut y avoir des homonymes, toutes les lignes sont pertinentes
Liste des villes des sociétés participantes : SELECT DISTINCT Ville
Élimination des doublons
FROM SOCIETE ORDER BY Ville
Règle : La liste des résultats : - présente toutes les lignes sélectionnées : ALL (par défaut), - ou élimine les lignes doublons non pertinentes : DISTINCT 55
55
Jérôme Fessy
Exercices
Exercices
Présenter les résultats en triant et/ou en éliminant les doublons non pertinents
Exercice 1 : Quelles sont les dates de séminaire ? Exercice 2 : Liste alphabétique des animateurs répertoriés (Concaténer le nom et le prénom) ? Exercice 3 : Quels sont les 3 séminaires les moins chers ?
56
56
Jérôme Fessy