LANGAGE SQL : PRÉSENTATION GÉNÉRALE,

DÉFINITION, INSERTION, MODIFICATION

INTERROGATION DE BD – PARTIE II

Cheikh KACFAH
[email protected]
 LMD: LANGAGE DE
MANIPULATION DE DONNÉES
• Les instructions principales du DML sont:

• INSERT

• SELECT

• DELETE

• UPDATE
2
 LMD: SELECT - I
• L’instruction SELECT est l’instruction la plus complexe du langage SQL.

• Néanmoins, elle offre une grande flexibilité pour la recherche d’information dans les tables.

• Elle est constituée de 2 clauses obligatoires et de 4 clauses optionnelles:

SELECT colonne1 [,colonne2] … les colonnes à afficher

FROM table [, ...] les tables impliquées dans la requête

[WHERE expression] les lignes sélectionnées (condition sur les tuples)

[GROUP BY colonne [, …]] les colonnes utilisées pour l’agrégation

[HAVING expression] les agrégats sélectionnés (condition sur les agrégats)

[ORDER BY colonne [{ASC | DESC}] [, ...]]; les colonnes utilisées pour le tri

3
 LMD: SELECT - II
• Éléments généraux sur la clause SELECT :

• Cette clause permet de choisir quelle(s) colonne(s) est(sont) retournée(s);

• On sépare les colonnes à retourner par des virgules;

• Il est possible de préfixer une colonne par : « nom de la table. » (permet de lever l’ambiguïté);

• L’usage de * indique que toutes les colonnes sont sélectionnées;

• ⚠Malgré sa ressemblance, l’instruction SELECT n’est pas lié l’opération de sélection (restriction)
de l’algèbre relationnelle

SELECT * FROM Film; sélectionne toutes les colonnes de la table Film

SELECT nom, dateNaissance FROM Client; sélectionne les colonnes nom et date de naissance de la table Client (Projection)

SELECT Client.nom, Client.dateNaissance FROM Client; Les colonnes sont préfixées par les noms de tables

4
 LMD: SELECT - II
• Précisions sur la clause FROM :

• Cette clause permet de choisir quelle(s) table(s) est(sont) utilisée(s) pour la requête;

• On sépare les tables à utiliser dans la requête par des virgules.

SELECT * FROM Film; sélectionne toutes les colonnes de la table Film

SELECT nom, dateNaissance FROM Client; sélectionne les colonnes nom et date de naissance de la table Client (~Projection)

SELECT Client.nom, Pays.nom FROM Client, Pays; ??? des tables Clients et Pays avec leurs colonnes nom

5
 LMD: SELECT - III
• Précisions générales sur la clause WHERE :

• La clause WHERE permet d’ajouter une restriction sur les lignes retournées; À rapprocher de la
sélection de l’Algèbre relationnelle

• Elle prend la forme d’une expression conditionnelle;

• Lorsque la clause WHERE est omise, toutes les lignes sont affichées (équivalent à WHERE TRUE).

- SELECT * FROM Client

WHERE age > 35 ; lignes de la table Client qui ont dans la colonne age une valeur strictement sup. à 35

- SELECT Film.titre, Artiste.nom FROM Film, Artiste

WHERE Film.realisateur = Artiste.idArtiste AND Artiste.idPays = ‘CM’;

6
 LMD: SELECT - IV
• La clause SELECT TOP :

• Permet de spécifier le nombre de lignes à retourner

• Ce nombre est par défaut à partir de la première ligne, mais on peut

préciser un offset

- SELECT * FROM Client

LIMIT 5 ; renvoi les 5 « premières » lignes de la table Client

- SELECT Film.titre FROM Film;

LIMIT 10 OFFSET 20 ; renvoi les titres des 10 films « suivants » 20 « premiers » films

- SELECT Film.titre FROM Film;

LIMIT 20, 10 ; idem

7
 LMD: SELECT - V
• La clause SELECT DISTINCT :

• SQL n’élimine pas les doublons des lignes retournées;

• La clause DISTINCT permet de retirer les doublons;

• Cette clause peut être utilisée avec les fonctions d’agrégation.

- SELECT nomRealisateur Les noms de réalisateurs ayant réalisé plusieurs films seront dupliqués

FROM Film ;

- SELECT DISTINCT nomRealisateur Chaque nom de réalisateur apparaîtra une seule fois

FROM Film ;

8
 LMD: SELECT - VI
• Les alias de colonne ou de table

• Permettent d’alléger la syntaxe des requêtes

• Ont l’avantage de permettre de proposer un nouveau nom de colonne pour la sortie; l’usage
d’apostrophes permet des mots avec espaces;

• Permettent de lever les ambiguïtés sur les noms de tables lors des requêtes récursives

- SELECT nom_colonne [[AS] alias_colonne] [, ...]

FROM nom_table [[AS] alias_table] [, ...];

- SELECT nomA AS nom, prenomA AS prenom FROM Artiste;

- SELECT CONCAT(nomA , ‘ ‘, prenomA) AS 'Nom Artiste' FROM Artiste;

- SELECT Enfant.nom, Parent.nom, Parent.age

FROM Pers AS Enfant, Pers AS Parent

WHERE Enfant.parent = Parent.nom;

9
 LMD: SELECT - VII
• Opérateurs de la clause WHERE – opérateurs conditionnels :

• Égal : =

• Différent: <> ou != ⚠ <> VS NULL

• Plus grand, plus grand ou égal, plus petit et plus petit ou égal : > , >= , < et <=

• Égalité totale sur une valeur nulle : <=>

• Opérateurs de la clause WHERE – opérateurs logiques :

• Et logique : AND ou &&

• Ou logique : OR ou ||

• Négation logique : NOT ou !

• Ou exclusif logique : XOR

10
 LMD: SELECT - VIII
• Opérateurs de la clause WHERE – autres opérateurs :

• Test un résultat logique (vrai/faux) : IS

• Inversion logique d’un résultat booléen : IS NOT

• Autres variantes : NOT LIKE, NOT IN, NOT BETWEEN

• Si une valeur est NULL : IS NULL

• Si une valeur n’est pas NULL : IS NOT NULL

- SELECT nomPays, capitale

FROM Pays ;

WHERE pib IS NULL AND population BETWEEN 50 AND 300;

11
 LMD: SELECT - IX
• Opérateurs de la clause WHERE – opérateurs de recherche textuelle :

• Recherche d’un patron dans une chaîne de caractères : LIKE

• Chaîne exacte ‘xyz’: LIKE ‘xyz’ (équivalent à =‘xyz’)

• Chaînes contenant ‘xyz’: LIKE ‘%xyz%’ , % équivaut à 0 ou

plusieurs caractères. Pour exactement 1 caractère, utiliser _
(underscore)

• On peut aller plus loin avec les expressions régulières et l’opérateur

REGEXP. Exemple REGEXP ‘[0-9]+’ recherchera toutes les
chaînes de caractères contenant au moins 1 chiffre
12
 LMD: SELECT - X
• Opérateurs de la clause WHERE – appartenance à une liste :

• L’opérateur IN permet de spécifier une liste de valeurs possibles pour

une colonne;

• Il est très utilisé dans les requêtes imbriquées

- SELECT nom_colonne [, …]

FROM nom_table [, ...]

WHERE colonne IN (valeur1, valeur2, ...); Les valeurs possibles sont listées entre () et séparées par des ,

- SELECT titre, duree

FROM Film

WHERE realisateur IN ('Ntedju', 'Ntamack');

13
 LMD: SELECT - XI
• Opérateurs de la clause WHERE – intervalle de valeurs possibles :

• L’opérateur BETWEEN permet de spécifier un intervalle de valeurs

possibles pour une colonne;

• S’applique autant pour les types numériques qu’alpha numériques.

- SELECT nom_colonne [, …]

FROM nom_table [, ...]

WHERE colonne BETWEEN valeurMin AND valeurMax;

- SELECT nom, prenom

FROM Client

WHERE age BETWEEN 17 AND 77;

14
 LMD: SELECT - XII
• Usage de requêtes imbriquées :

• il est possible d’utiliser le résultat d’une requête comme entrée dans une autre;

• ainsi, on peut imbriquer plusieurs requêtes les unes dans les autres;

• ce mécanisme offre des solutions élégantes et puissantes à plusieurs situations;

• Il s’applique à d’autres clauses que le SELECT

- SELECT nomRealisateur, prenomRealisateur

FROM (SELECT * FROM Film WHERE genre = ‘Action’) AS FilmDAction Résultats du SELECT vus comme une table

WHERE annee >= 2018 AND duree < 140;

- SELECT nomClient, prenomClient

FROM Client

WHERE idPays = (SELECT idPays FROM Pays WHERE nomPays = ‘Cameroun’) AND … vu comme une seule valeur*

filmPrefere IN ( SELECT idFilm FROM Film WHERE nomrealisateur=‘NTAMACK’ ); … vu comme une liste de valeurs

15
 LMD: SELECT - XIII
• SQL propose plusieurs fonctions d’agrégation agissant comme des fonctions de calcul
statistique sur les données :

• valeur minimum : MIN

• valeur maximum : MAX

• nombre de valeurs : COUNT

• somme des valeurs : SUM

• moyenne des valeurs : AVG

• Selon les SGBD, plusieurs autres fonctions d’agrégation sont disponibles : valeur
médiane, écart type, variance et plusieurs autres.

16
 LMD: SELECT - XIII
• Les fonctions d’agrégation ne sont possibles que dans la clause
SELECT et HAVING.

• Sans la clause GROUP BY, l’usage des fonctions d’agrégation se fait

en considérant toutes les lignes de la table.

- SELECT MIN(duree) AS ‘Durée Min’, MAX(duree) AS ‘Durée Max’, SUM(duree) AS ‘Total durée’, AVG(duree) AS
‘Durée moyenne’

FROM Film ;

- SELECT COUNT(idFilm) Nombre de film disponibles en DVD. Un film présent sur plusieurs DVDs sera compté autant de fois

FROM Dvd ;

- SELECT COUNT(DISTINCT idFilm) Nombre de film disponibles en DVD, chaque film étant compté une seule fois

FROM Dvd ;

17
 LMD: SELECT - XIV
• Clause GROUP BY :

• Cette clause permet d’appliquer les fonctions d’agrégation aux sous groupes de lignes
qui ont une valeur commune pour une colonne donnée;

• Il est très important de savoir que les fonctions de regroupement et d’agrégation ne

s’appliquent qu’après les jointures de la requête (s’il y en a);

• L‘usage de plusieurs colonnes pour cette clause implique que le regroupement se fera
sur l’ensemble des valeurs provenant de ces colonnes (comme un tout).

- SELECT idFilm AS Film, COUNT(*) AS 'Nombre de DVD’

FROM Dvd

GROUP BY idFilm ;

18
 LMD: SELECT - XIV
• Clause HAVING :

• Applique une restriction sur les agrégations ne respectant pas

l’expression donnée;

• Ne s’utilise qu’avec la clause GROUP BY;

- SELECT Pays.nomPays, COUNT(*) AS NbFilms

FROM Pays, Film

WHERE Pays.idPays = Film.paysOrigine

GROUP BY idPays

HAVING NbFilms>5 ;

19
 LMD: SELECT - XIV
• Clause ORDER BY :

• Permet d’ordonner les lignes selon un critère spécifique;

• Le tri est ascendant ou descendant (ascendant par défaut);

• On peut trier sur une ou plusieurs colonnes.

- SELECT nom_colonne [, ...]

FROM nom_table [, ...]

ORDER BY colonne1 [ASC | DESC] [, colonne2 [ASC | DESC] …] ;

- SELECT CONCAT (nomArtiste, ‘ ‘, prenomArtiste) AS acteur

FROM Artiste

ORDER BY dateNaissance DESC , acteur ASC;

20
 LMD: SELECT - XV
• Requêtes corrélées :

• Le SQL permet des requêtes plus complexes: les requêtes imbriquées corrélées
(ou sous-requêtes corrélées ou requêtes synchronisées).

• Les requêtes imbriquées exposées précédemment sont appelées requêtes

imbriquées non corrélées.

• Une requête imbriquée a la forme suivante :

• Une requête est imbriquée à l’intérieure d’une autre;

• Contrairement aux requêtes non corrélées, la requête interne utilise des

valeurs de la requête externe;

• La résolution de telles requêtes implique que la requête interne est évaluée

pour chaque ligne de la requête externe*
21
 LMD: SELECT - XVI
• Exemples de requêtes corrélées
• SELECT titre, realisateur

FROM Film AS unFilm

WHERE duree >= ( SELECT AVG(duree) FROM Film AS sousGroupeFilm

WHERE unFilm.categorie = sousGroupeFilm.categorie );

• SELECT nomClient FROM Client AS c

WHERE (SELECT COUNT(*) FROM Emprunt AS e

WHERE c.idClient = e.idClient) >= 2 ;

22
LMD: SELECT - XVII (JOINTURES)
• Correspondances entre les jointures de l’algèbre relationnelle
et leur implémentation sous MySQL

• INNER JOIN: Jointure « classique » (thêta jointure)

• NATURAL JOIN: Jointure naturelle

• LEFT [OUTER] JOIN: Jointure externe gauche

• RIGHT [OUTER] JOIN: Jointure externe droite

• FULL [OUTER] JOIN: Jointure externe (non

implémentée sous MySQL)
23
LMD: SELECT - XVIII (JOINTURES)
• Syntaxe

SELECT nom_colonne1 [, nom_colonne2, ...]

FROM nom_table1

{ INNER | { LEFT | RIGHT } [OUTER] } JOIN nom_table2 ON condition_sur_colonnes;

• SELECT CONCAT(a.prenomArtiste, ' ', a.nomArtiste) AS realisateur, f.titre AS film

FROM Artiste a INNER JOIN Film f

ON a.nomArtiste = f.nomRealisateur AND a.prenomArtiste = f.prenomRealisateur ;

• SELECT f.titre, CONCAT(f.prenomRealisateur, ' ', f.nomRealisateur) AS realisateur, d.numeroDvd

FROM Film AS f

NATURAL JOIN Dvd AS d;

24
LMD: SELECT - XVIII (OP. ENSEMBLISTES)
• Union
SELECT A.col1 [, A.col2, ...] FROM TableA AS A
UNION
SELECT B.col1 [, B.col2, ...] FROM TableB AS B

• Intersection ⚠ Les schémas

doivent être les mêmes
SELECT A.col1 [, A.col2, ...]
FROM TableA AS A INNER JOIN TableB AS B
ON A.col1 = B.col1 [AND A.col2 = B.col2, …] ;

• Différence
SELECT DISTINCT A.col1 [, A.col2, ...]
FROM TableA AS A LEFT JOIN TableB AS B
ON A.col1 = B.col1 [AND A.col2 = B.col2, …]
WHERE B.col1 IS NULL ;
25
 LMD: INSERT - I
• L’instruction INSERT :

• Permet l’insertion de lignes i.e. de données dans une table

- INSERT INTO nom_table [(nom_colonne1 [, nom_colonne2...]] [, …)]

VALUES ({valeur_colonne1 | DEFAULT} [, valeur_colonne2 | DEFAULT ...] );

- INSERT INTO Pays(id, nom, langue) On peut préciser les colonnes…

VALUES (‘CM’ , ‘Cameroun’ , ‘Camfranglais’); …Et donc insérer les données dans cet ordre

- INSERT INTO Pays On peut ne pas préciser les colonnes…

VALUES (‘CM’, ‘Cameroun’ , ‘Camfranglais’) , …Et donc insérer les données dans l’ordre de définition*

(‘NG’ , ‘Nigéria’ , ‘Igbo’); … Et on peut insérer plusieurs données à la fois (dans tous les cas)

26
 LMD: INSERT - II
• Même s’il est possible d’omettre le nom des colonnes, il est recommandé de les
spécifier afin de simplifier les cas éventuels où la structure de la table serait modifiée.

• Il peut arriver d’utiliser une requête imbriquée afin d’aller chercher la valeur d’une clé
primaire pour l’utiliser à titre de clé étrangère.

- INSERT INTO Artiste(nom, prenom, idPays, dateNaissance)

VALUES (‘NDO’ ,

‘Daniel’ ,

(SELECT id FROM Pays WHERE Pays.nom=‘Cameroun’) ,

‘1947/05/25’ );

27
 LMD: INSERT - III
• On peut insérer plusieurs lignes grâce à une requête imbriquée.
Il faut cependant que les schémas soient les mêmes…

- INSERT INTO nom_table [(nom_colonne1 [, nom_colonne2...]] [, …)]

SELECT expression ;

- INSERT INTO SatsFilms(pays, nbreFilms, dureeMoyenne)

SELECT P.nomPays AS pays, COUNT(F.paysOrigine) AS nbreFIlms, AVG(F.duree) AS dureeMoyenne

FROM Pays AS P , Film AS F

WHERE P.idPays = F.paysOrigine

GROUP BY F.paysOrigine;

28
 LMD: DELETE
• L’instruction DELETE permet de supprimer des lignes dans une table (une table à la fois).

• Cette instruction ne permet pas de supprimer une table mais son contenu. Si une table est
vidée, elle existe toujours avec le même schéma.

• Si la clause WHERE n’est pas spécifiée, la table est entièrement vidée de son contenu.

• ⚠ Attention aux clés étrangères.

- DELETE FROM nom_table [WHERE expression]

- DELETE FROM Client WHERE ville=‘Yaoundé’ ;

- DELETE FROM Emprunt ; Supprime tous les emprunts

29
 LMD: UPDATE
• L’instruction UPDATE permet de modifier les valeurs d’attributs d’une ou
plusieurs lignes.

• Si la clause WHERE n’est pas spécifiée, toutes les lignes de la table sont
modifiées.

- UPDATE nom_table
📝 Les valeurs peuvent être issues d’une requête
SET nom_colonne1 = valeur1 [, nom_colonne2 = valeur2 …]

[WHERE expression]

- UPDATE Client
📝 On peut réutiliser les valeurs courantes
SET nbMaxEmpt = nbMaxEmpt * 2 , ville = 'Yokadouma'

WHERE idClient = ‘CLI001’ ;

- UPDATE Client SET ville=‘Yaoundé’ ; Change les villes de tous les clients en Yaoundé
30
LMD: FONCTIONS PRÉDÉFINIES
• Il existe un très grand nombre de fonctions disponibles pour le langage SQL.
Voici les plus importantes de MySQL.

• Fonctions mathématiques :

• ABS, SIGN

• DIV, MOD,

• POW, SQRT, EXP, LOG, LOG2, LOG10, LN,

• PI, COS, SIN, TAN, ACOS, ASIN, ATAN, ATAN2, RADIANS, DEGREES

• RAND,

• ROUND, FLOOR, CEIL

31
LMD: FONCTIONS PRÉDÉFINIES
• Fonctions de manipulation de chaînes de caractères :

• ASCII, CHAR, LENGTH, LOWER, UPPER

• CONCAT, TRIM, LEFT, RIGHT,

• SUBSTRING, REPLACE, LOCATE,

• FORMAT

• Fonctions de manipulation de la date et de l’heure :

• NOW, SYSDATE, DATEDIFF, TIMEDIFF

• YEAR, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MICROSECOND

• STR_TO_DATE, DATE_FORMAT
32
 LDD: VUES - I
• Les vues sont des requêtes stockées qui, lorsqu'elles sont
invoquées, produisent un résultat

• Les vues sont de sortes de tables virtuelles dérivées à partir

d’autres tables (appelées tables de base)

• On s’en sert pour éviter la réécriture de requêtes complexes et

fréquentes. Elles sont très utiles pour la gestion des droits sur les
tables ou encore la lecture des tables dont les structures changent

• Une vue peut servir de base à une autre vue

33
 LDD/DML: VUES - II
- CREATE [OR REPLACE] VIEW nom_vue [( colonne1 [, colonne2 ...])]

AS select_statement ;

- DROP VIEW nom_vue1 [, nom_vue2 …] ;

- CREATE OR REPLACE VIEW FilmLong

AS SELECT titre, nomRealisateur, prenomRealisateur, genre, duree FROM Film WHERE duree>140 ;

- CREATE OR REPLACE VIEW StatEmprunteur

AS SELECT c.idClient, CONCAT(prenomClient, ' ', nomClient) AS client, COUNT(*) as 'nombre emprunts'

FROM Client c NATURAL JOIN Emprunt e

GROUP BY e.idClient ; ⚠ Nommage, ALTER TABLE

- DROP VIEW FilmLong, StatEmprunteur; 🔎 Modification, Insertion, etc.

34
 LDD: INDEX
• Un index est une structure de données qui améliore la vitesse des opérations dans une table. Ils
peuvent être créés à l'aide d'une ou de plusieurs colonnes, ce qui permet à la fois des
recherches aléatoires rapides et un tri efficace pour l'accès aux enregistrements.

• Désavantages:

• Ils prennent de la place en mémoire

• Ils ralentissent les requêtes d'insertion, modification et suppression*

• Types d’index

• Simple: index standard, permettant des doublons et des valeurs NULL

• Unique: index ne permettant pas des doublons mais autorisant des valeurs NULL. Lors de
la déclaration d’une PRIMARY KEY le SGBD construit un index UNIQUE n’autorisant pas
les valeurs NULL

• Fulltext: index utilisé pour la recherche textuelle

35
 RÉFÉRENCES
• Chantal Gribaumont, Administrez vos bases de données avec MySQL.
https://openclassrooms.com/

• Stéphane Crozat, Algèbre relationnelle - librecours.net, Costech UTC

• Laurent Audibert, Bases de données : de la modélisation au

SQL, Ellipses, 2009

• M. Chériet et J-C Demers, GPA-775 Bases de données, Université du

Quebec - ETS, 2019

• MySQL Documentation, https://dev.mysql.com/doc/