Data warehouse I
Week 3
Based on Benoît Groz's slides
SQL: Langage de Manipulation des Données
LMD
Langage de Manipulation de Données
(
DML: Data Manipulation Language)
-
SELECT: pour interroger -
INSERT: pour insérer des tuples dans une table -
UPDATE: pour modifier des données dans une table -
DELETE: pour supprimer des tuples dans une table
Les contraintes d'intégrité peuvent empêcher
INSERT, UPDATE, DELETE.
SQL Mises à jour
UPDATE
UPDATE Prog SET Heure = '21h00'
WHERE Nom_Cine LIKE 'ugc%' AND Titre= 'Dersou Ouzala';
# Autres expressions possibles:
UPDATE Prog SET Heure = Heure+1;
# les séances seront retardées
UPDATE Produits SET PrixTTC = PrixHT * TVA;
Prog avant
NOM_CINE TITRE SALLE HEURE ugc bercy Dersou Ouzala 1 20h00 ugc bercy Dersou Ouzala 2 22h00 ugc bercy Kagemusha 1 14h00 le champo Dersou Ouzala 1 18h00 le champo Kagemusha 1 20h00Prog après mise à jour
NOM_CINE TITRE SALLE HEURE ugc bercy Dersou Ouzala 1 21h00 ugc bercy Dersou Ouzala 2 21h00 ugc bercy Kagemusha 1 14h00 le champo Dersou Ouzala 1 18h00 le champo Kagemusha 1 20h00
Insertions
INSERT
INSERT INTO Prog (Nom_Cine, Titre, Salle, Horaire)
VALUES ('ugc', 'Dersou Ouzala', 1, '20h00'); % ne pas oublier
les ' ' pour les chaînes de caractères- d'insérer la valeur par défaut (NULL sauf mention explicite d'un autre DEFAULT) pour les colonnes non spécifiées.
- de donner les valeurs dans le désordre.
Selon le SGBD, on trouve des commandes mixtes Update-insert.
Suppressions
DELETE
DELETE FROM Prog WHERE Heure >= '20h00';
DELETE FROM Prog WHERE Titre in (SELECT Titre FROM Film
WHERE LOWER(Acteur) Like 'Jean-%');
# Comportements possible en cas de Foreign Key: échec ou cascade.
Pour vider complètement une table,
TRUNCATE est plus
rapide.
TRUNCATE Prog;Langage de requête
syntaxe d'une requête SQL (simplifiée)
SELECT
<liste d'attributs>
attributs du schéma cible ↔︎
π
FROM
<liste de relations>
relations du schéma
source
WHERE
<condition>
conditions de sélection ↔︎
σ
WHERE optionnelle. Garde les tuples évaluant
condition à “Vrai”.Clause
FROM aussi optionnelle pour PostgreSQL (pas
Oracle: FROM DUAL)
SELECT Nom_Cine FROM Prog WHERE Titre='Marion'SELECT 2+3SQL: requêtes simples
Select Project Join (SPJ)
SELECT * FROM FILM WHERE Acteur='Lonsdale'↔ σActeur = ′Lonsdale (FILM)
SELECT Titre FROM FILM WHERE Acteur='Lonsdale' OR Acteur='Astaire' ↔︎
πtitre(σActeur
=
′Lonsdale′ ∨ Acteur
=
′Astaire′(FILM))
Sémantique formelle (cas mono-relation):
SELECT
A1, …, Ak
FROM
R
↔︎ πA1,...,Ak σC (R)
WHERE
C
Illustration de requête SQL: cas mono-relation
Prog
NOM_CINE TITRE SALLE HORAIRE --------- --------------------- ----- ------- ugc bercy Le discours d'un roi 1 20h00 ugc bercy Le discours d'un roi 2 22h00 ugc bercy Inception 1 14h00 le champo Le discours d'un roi 1 18h00 le champo Inception 1 20h00
SELECT Nom_cine, Titre
FROM Prog
WHERE Nom_cine = 'ugc bercy' AND HORAIRE < 23h00 AND HORAIRE > 10h00NOM_CINE TITRE --------- -------------------- ugc bercy Le discours d'un roi ugc bercy Le discours d'un roi ugc bercy Inception
SQL: requête simple
Select Project Join (SPJ)
SELECT Nom_Cine, Film.Titre, Horaire FROM Film,
Prog WHERE Film.Titre = Prog.Titre AND Acteur = 'M.Freeman'
Sémantique formelle (cas multi-relation):
SELECT
A1, …, Ak
↔︎${\pi^*}_{A_1,\dots,A_k}(\sigma_C(R_1^*\times\dots\times R_p^*))$
FROM
R1, …,
Rp
projection multiensembliste
WHERE
C
tous attributs distincts
SQL: requête simple
Select Project Join (SPJ): ambiguïté sur la table d'un attribut
SELECT Nom_Cine, Film.Titre, Horaire
FROM Film, Prog
WHERE Film.Titre = Prog.Titre AND Acteur = 'M.Freeman'- Table ajoutée automatiquement par le système pour chaque attribut
-
Inférence impossible si l'attribut apparait dans plusieurs
tables: en ce cas l'utilisateur doit impérativement spécifier la
table.
↪ sinon erreur
Illustration de requête SQL: cas multi-relation
Prog
NOM_CINE TITRE SALLE HORAIRE --------- --------------------- ----- ------- ugc bercy Le discours d'un roi 1 20h00 ugc bercy Le discours d'un roi 2 22h00 ugc bercy Inception 1 14h00 le champo Le discours d'un roi 1 18h00 le champo Inception 1 20h00
Cine
NOM_CINE ADRESSE TELEPHONE --------- --------- --------- ugc bercy 3 rue... 06043494 le champo 17 av... 01049059 nef chava 18 bd... 04387953
SELECT Telephone, Horaire FROM Prog, Cine
WHERE Prog.Nom_cine = Cine.Nom_cine AND Titre = 'Inception'TELEPHONE HORAIRE --------- ------- 06043494 14h00 01049059 20h00
Renommage d'attributs
Attr AS Attr_alias
Intérêt:- nommer une colonne résultant d'un calcul,
- renommer une colonne de façon plus explicite
- permettre union…
Toutes les personnes ayant participées au tournage du film
“Marion”:
SELECT Acteur AS Personne FROM Film WHERE Titre = 'Marion'
UNION
SELECT MeS AS Personne FROM Film WHERE Titre = 'Marion'Renommage de table
Table Table_alias
Intérêt:- distinger plusieurs copies d'une même table
- donner un nom à une table résultant d'un calcul intermédiaire (sous requête) …
Les films avec leur MeS et leurs acteurs dans lesquels joue M-F
Pisier ?
$\pi_{\rm Titre}(\sigma_{\rm Actrice=M-F.
Pisier}(\mathbf{film}))\Join\mathbf{film}$
SELECT F2.Titre , F2.MeS, F2.Acteur
FROM FILM F1, FILM F2
WHERE F1.Titre = F2.Titre AND F1.Acteur = 'M-F. Pisier'- F1 et F2 sont des copies virtuelles de film
- F1 et F2 sont des variables utilisées pour désigner n'importe quel couple de n-uplets de film
Manipulation des données
Élimination des doublonsSELECT DISTINCT
Liste des films projetés dans chaque cinéma:SELECT Nom_Cine, Titre
FROM ProgNOM_CINE TITRE --------- -------------------- ugc bercy Le discours d'un roi ugc bercy Le discours d'un roi ugc bercy Le discours d'un roi ugc bercy Inception le champo Le discours d'un roi le champo Inception
SELECT DISTINCT Nom_Cine, Titre
FROM ProgNOM_CINE TITRE --------- -------------------- ugc bercy Le discours d'un roi ugc bercy Inception le champo Le discours d'un roi le champo Inception
Opérations sur les attributs
Dans la clause SELECT ou dans les conditions une expression peut être:- une constante
- un attribut
- expr1 * expr2 (ou +,-,/)
-
une opération sur les chaînes de caractères
- ch1 || ch2 : concaténation
- LOWER(ch) : met en minuscules
- UPPER(ch) : met en majuscules
- SUBSTR(ch,i,j) : extrait la sous chaîne de longueur j débutant à l'indice i
- CASE WHEN condition THEN expr ELSE expr END
- …
SELECT 3*montant FROM Ventes;
SELECT SUBSTR('ABCDEFG',3,4) FROM DUAL; # (oracle): 'CDEF'
SELECT substring('Thomas' from 2 for 3); # (postgresql): 'hom'Contenu de la clause WHERE
- attr IS NULL
- expr op expr ∈ (=,<,<=,>=,<>)
- expr BETWEEN val1 AND val2
- expr IN (val1,val2,…)
-
expr LIKE
string-pattern
- % : n'importe quelle chaîne de caractère
- _ : n'importe quel caractère
Films
TITRE ACTEUR ------------------------- ------ Rien à Declarer Boon La plage Canet Bienvenus chez les ch'tis Boon
SELECT Titre
FROM Films
WHERE LOWER(Titre) LIKE '%bien%'
OR Titre LIKE '%declarer%'
# LOWER: convertir en minusculeTITRE ------------------------- Bienvenus chez les ch'tis
Piège 1: produit cartésien
Sémantique de la requête ci-dessous $\stackrel{?}{=}R.A\cap (S.A\cup
T.A)$?
SELECT R.A FROM R,S,T WHERE R.A=S.A OR R.A=T.APiège 2: NULLs
Sémantique de la requête ci-dessous?
SELECT P.name FROM Personnes P WHERE P.age > 10 OR P.age <= 10Trier le résultat de la requête
Relation = multi-ensemble ⟹ ordre d'affichage dans SQL arbitraire si il n'est pas spécifié.Liste des films projetés dans chaque cinéma triés par titre:
SELECT Nom_Cine, Titre
FROM Prog
ORDER BY TitreNOM_CINE TITRE --------- -------------------- ugc bercy Inception le champo Inception ugc bercy Le discours d'un roi ugc bercy Le discours d'un roi ugc bercy Le discours d'un roi le champo Le discours d'un roi
SELECT Nom_Cine, Titre
FROM Prog
ORDER BY Titre DESC,
Nom_Cine ASCNOM_CINE TITRE --------- -------------------- le champo Le discours d'un roi ugc bercy Le discours d'un roi ugc bercy Le discours d'un roi ugc bercy Le discours d'un roi le champo Inception ugc bercy Inception
-
tri croissant (
![image]()
ASCending) par défaut.
- ordre naturel de l'attribut dépend de son type (entier, chaîne de caractères...)
- tri sur plusieurs colonnes: lexicographique
Agrégats
Fonctions d'agrégation
( ![image]()
Aggregates)
But: résumer les données en regroupant les tuples.
Exemple: calculer le nombre de film pour chaque réalisateur.
SELECT
<liste d'attributs>
attributs du schéma cible
FROM
<liste de relations>
relations du schéma source
WHERE
<condition>
conditions de sélection
GROUP BY
<liste d'attributs>
attributs de regroupement
ORDER BY
<liste d'attributs>
ordre d'affichage
-
COUNT(): cardinalité du multiensemble -
SUM(): somme -
MAX/MIN(): valeur max/min -
AVG(): moyenne
Fonctions d'agrégation: principe du regroupement
FilmTITRE ACTEUR ------------------------- --------- Rien à Declarer Boon <-- Brice de Nice Cornillac La vie de Chantier Boon <-- Ensemble c'est tout Canet Mon Idole Canet The dark knight rises Cotillard Les petits mouchoirs Cotillard La plage Canet Inception Cotillard Bienvenus chez les ch'tis Boon <--
SELECT acteur, COUNT(*)
FROM film GROUP BY acteur ACTEUR COUNT(*)
-------- --------
Canet 3
Cotillard 3
--> Boon 3
Cornillac 1
- la fonction est calculée pour chaque groupe indépendamment
-
si fonction d'agrégation sans
GROUP BY: 1 groupe=table entière -
les attributs hors du
GROUP BYne peuvent apparaître dansSELECTqu'à l'intérieur d'une fonction d'agrégation
Fonctions d'agrégation (2)
Film
Exemple de requête avec agrégat sans GROUP BY...
Attention: la clause WHERE filtre les données avant la création des groupes.
TITRE ACTEUR QT ------------------- -------- -- Rien à Declarer Boon 2 Brice de Nice Cornillac 5 La vie de Chantier Boon 7 Ensemble c'est tout Canet 2 Mon Idole Canet 3
Exemple de requête avec agrégat sans GROUP BY...
Attention: la clause WHERE filtre les données avant la création des groupes.
SELECT COUNT(*) c, SUM(QT) s FROM filmc | s --+-- 5 | 19
SELECT SUBSTR(MAX(Titre),0,4) mx,
SUM(QT)/COUNT(*) s
FROM t WHERE Titre>'L'mx | s ----+--- Rie | 4
Fonctions d'agrégation : ordre
Ordre d'évaluation intuitif des clauses :
-
FROM -
WHERE -
GROUP BY -
HAVING -
SELECT -
ORDER BY
Donner le résultat de la requête:
SELECT acteur, nomrealisateur, COUNT(*) NB
FROM film
WHERE acteur < 'D'
GROUP BY acteur, nomrealisateur
ORDER BY NBTITRE ACTEUR NOMREALISATEUR ------------------------- -------- --------------- Les petits mouchoirs Cotillard Canet La plage Canet Boyle Mon Idole Canet Canet The dark knight rises Cotillard Nolan La vie de Chantier Boon Boon Brice de Nice Dujardin Dujardin Ensemble c'est tout Canet Canet Gran Torino Eastwood Eastwood Inception Cotillard Nolan Brice de Nice Cornillac Dujardin Rien à Declarer Boon Boon Bienvenus chez les ch'tis Boon Boon
COUNT
Que compter?
-
COUNT(*)retourne le nombre de lignes. -
COUNT(a)retourne le nombre de lignes non nulles sur colonne a. -
COUNT(DISTINCT a)retourne le nombre de valeurs distinctes sur la colonne a.
Film
titre | acteur | nomrealisateur ----------------------+-----------+--------------- Green Book | Mortensen | Farrelly Seigneur des Anneaux | Mortensen | Jackson Coda | Jones | Heder Nomadland | |Zhao
SELECT COUNT(*) A,
COUNT(acteur) B,
COUNT(DISTINCT acteur) C
FROM film
WHERE Titre != 'Coda'a | b | c --+---+--- 3 | 2 | 1
Compter en SQL les acteurs que chaque réalisateur a encadré
:
Il est possible de calculer SUM(DISTINCT a),
AVG(DISTINCT a).
Autres fonctions d'agrégation
Nb_Films_JouésACTEUR NB_FILMS -------- -------- Canet 3 Cotillard 3 Boon 3 Cornillac 1
Calculer dans une même requête le nombre maximal et moyen de
films par acteur.
Résultat attendu:
Résultat attendu:
NB_MAX NB_MOY ------ ------ 3 2.5
Conditions sur les groupes
HAVING
SELECT
<liste d'expressions1>
attributs du schéma cible
FROM
<liste de relations>
relations du schéma source
WHERE
<conditions1>
conditions de sélection
GROUP BY
<liste d'attributs>
attributs de regroupement
HAVING
<conditions2>
conditions sur les groupes
ORDER BY
<liste d'expressions2>
ordre d'affichage
HAVING permet de ne garder que les groupes satisfaisant
une condition:
SELECT acteur, COUNT(*) NB
FROM film
GROUP BY acteur
HAVING COUNT(*) > 2ACTEUR NB -------- -- Canet 3 Cotillard 3 Boon 3
Agrégats SQL: sémantique (1)
SELECT ... <-- évalué une fois par groupe (pas par ligne) FROM ... GROUP BY A_1 HAVING ... <-- évalué une fois par groupe (pas par ligne)
A_1 | A_2 | ...
---- + ---- + ---
... | ... | ... Le regroupement
---- + ---- + --- partitionne les bindings
the x_i group --> x_i | y_i1 | ... de lignes :
the x_i group --> x_i | y_i2 | ... - il n'y a pas de
---- + ---- + --- groupe vide
the x_j group --> x_j | y_j1 | ... - chaque ligne
the x_j group --> x_j | y_j2 | ... appartient à
---- + ---- + --- exactement un groupe
... | ... | ...
Agrégats SQL: sémantique (2)
Agrégat: opérateur commutatif et associatif (à qques détails près).Pour calculer
agg (comme fold en programmation
fonctionnelle): on initialise un accumulateur à la valeur
zagg.
| Aggregate agg | ∅agg | zagg | ⊕agg(a, x) |
|---|---|---|---|
| COUNT | 0 | 0 | a + 1 |
| SUM | NULL | 0 | a + x |
| AVG | NULL | <0, 0> | <a.1 + x, a.2 + 1> |
| MAX | NULL | −∞ | max2(a, x) |
| MIN | NULL | +∞ | min2(a, x) |
| bool_and | NULL | true | a ∧ x |
| bool_or | NULL | false | a ∨ x |
| ... | ... | ... | ... |
Agrégats SQL: exemples
SELECT a, b, count(*), max(c)
FROM t GROUP BY a, b
# nothing special thereSELECT a, count(*)
FROM t
GROUP BY a, b
# sometimes it makes sense not to display bSELECT a, sum(a), max(b+d)
FROM t
GROUP BY a
/* we can group *and* aggregate on a,
though it seldom makes sense */SELECT a, b, sum(a)
FROM t
GROUP BY a
/* Incorrect query:
b is not a group by expression */NULLs in SQL aggregates
Aggregates (SUM, AVG, MAX) ignore null values.
Propose a table where c1 and c2 will differ:
SELECT SUM(a) + SUM(b) c1, SUM(a+b) c2 FROM t;Quelques outils techniques
Guillemets: simple ou double
SELECT a FROM t; # la colonne nommée a
SELECT 'a'; # constant string 'a'
CREATE TABLE "ma table" ("select" int); # identificateur entre guillemets
SELECT "select" from "ma table"; # faisable mais à éviter.Intérêt d'un identificateur entre guillemets (![image]()
“quoted identifier”):
“quoted identifier”):- sensible à la casse
- permet d'utiliser des espaces
- permet d'échapper des mots clés réservés par le SGBD
Mais je ne recommande pas de les utiliser, et recommande d'éviter les
mélanges.
Remarque: les identifiants qui ne sont pas entre guillements sont par défaut convertis en minuscules sous postgresql, en majuscule sous oracle.
Remarque: les identifiants qui ne sont pas entre guillements sont par défaut convertis en minuscules sous postgresql, en majuscule sous oracle.
Dans une requête SQL, le symbole d'échappement est :
SELECT Titre FROM Film WHERE Acteur = 'O”Brien'; # cherche O'BrienMais les applications utilisent plus souvent une requête
préparée avec paramètres.
Pour plus d'informations (ex: comment échapper des caractères):
https://docs.postgresql.fr/16/sql-syntax.html
Les Schémas sous PostgreSQL
Un cluster de BD postgresql contient une ou plusieurs BD. Chaque BD comporte un ou plusieurs schémas.CREATE SCHEMA mon_schema;
CREATE TABLE mon_schema.t(id int) # voir: base.schema.table
SELECT a FROM mon_schema.t;
CREATE SCHEMA mon_schema2;
CREATE TABLE mon_schema2.t(id int) # valide car pas dans le même schéma
# DROP SCHEMA mon_schema CASCADE; # pour effacer un schéma non-vide- Par défaut, schéma public
- En général on évite les noms qualifiés. Le schéma utilisé sera le premier dans le chemin de parcours:
SET search_path TO mon_schema, mon_schema2, public;
SHOW search_path; # SELECT * FROM t utilisera la table dans mon_schemasearch_path -------------------------------- mon_schema, mon_schema2, public;
Les séquences
Compteur que l'on va pouvoir incrémenter pour générer une suite de nombres.Sert en particulier pour générer des identifiants.
CREATE SEQUENCE serie INCREMENT BY 100 START 101;
SELECT nextval('serie'); # 101
SELECT currval('serie'); # le résultat du dernier nextval: ici, 101CREATE TABLE t ( a SERIAL, b string );
# revient à
CREATE SEQUENCE t_a_seq;
CREATE TABLE t ( a NOT NULL DEFAULT nextval('t_a_seq'), b string );Comportements spécifiques à certains SGBDs (non exhaustif)
Je déconseille fortement d'utiliser ces particularités:-
PostgreSQL:
SELECT country FROM cities GROUP BY city; # valid if city is PK. # Or more generally FD city -> country # Optional extensions of standard -
MySQL, MariaDB (InnoDB engine):
cities(id, city, country) ... FOREIGN KEY REFERENCES (cities.country) # FK referencing a non unique column! # Non standard -
PostgreSQL, MySQL, MariaDB
SELECT a AS bbb FROM t GROUP BY bbb;