Plan

SQL et Python
Fonctions et procédures
Déclencheurs
requêtes récursives
Aggregation avancée
OLAP

Langage SQL avancé

Idriss TAZIGH

CRMEF MARRAKECH SAFI

Cycle de Préparation d’Agrégation
Agrégation Informatique
[email protected]

03/2023

Idriss TAZIGH BD
 Plan
SQL et Python
Fonctions et procédures
Déclencheurs
requêtes récursives
Aggregation avancée
OLAP

1 SQL et Python

2 Fonctions et procédures

3 Déclencheurs

4 requêtes récursives

5 Aggregation avancée

6 OLAP

Idriss TAZIGH BD
 Plan
SQL et Python
Fonctions et procédures
Déclencheurs
requêtes récursives
Aggregation avancée
OLAP

Python et SQLlite
Importer le module sqlite3.
Utiliser la méthode de connexion connect().
la méthode cursor() crée un objet pour l’exécution des
requêtes.
La méthode exécute() : exécute la requête SQl et retourne le
résultat.
Extraction du résultat : Utiliser cursor.fetchall() ou
fetchone() ou fetchmany() pour la lecture de résultat.
fermeture de cursor et la connexion avec la méthode close.
Attrapez l’exception de base de données, le cas échéant, qui
peut se produire pendant ce processus de connexion.
Idriss TAZIGH BD
 Plan
SQL et Python
Fonctions et procédures
Déclencheurs
requêtes récursives
Aggregation avancée
OLAP

Python et SQLlite : Exemple

1 import sqlite3
2 try :
3 Connection = sqlite3 . connect ( ’ SQLite_Python . db ’)
4 cursor = Connection . cursor ()
5 print ( " Base de donnees cree et connectee avec
succes a SQLite " )
6 sqli t e_ s e le c t _Query = " select sqlite_version () ; "
7 cursor . execute ( sqlite_select_Query )
8 resultat = cursor . fetchall ()
9 print ( " La version de la base de donnees SQLite est
" , resultat )
10 cursor . close ()
11 except sqlite3 . Error as erreur :
12 print ( " Erreur lors de la connexion a sqlite " ,
erreur )
13 finally :
14 if Connection : Idriss TAZIGH
ConnectionBD
. close () ; print ( " La
 Plan
SQL et Python
Fonctions et procédures
Déclencheurs
requêtes récursives
Aggregation avancée
OLAP

Python et MySql

téléchargement d’un connecteur : MySQLdb ou bien

mysql.connector Ou bien pymsql
Création de la connexion :
1 import mysql . connector
2 mydb = mysql . connector . connect (
3 host = " localhost " ,
4 user = " username " ,
5 passwd = " password " ,
6 database = " madatabase "
7 )

Pour insérer plusieurs enregistrements executemany()

Idriss TAZIGH BD
 Plan
SQL et Python
Fonctions et procédures
Déclencheurs
requêtes récursives
Aggregation avancée
OLAP

Python et MySql

Idriss TAZIGH BD
 Plan
SQL et Python
Fonctions et procédures
Déclencheurs
requêtes récursives
Aggregation avancée
OLAP

Python et MySql

Il est considéré comme une bonne pratique d’échapper aux

valeurs de toute requête, également dans les instructions
delete/selection.
Ceci permet d’éviter les injections SQL, technique de piratage
Web courante pour détruire ou utiliser à mauvais escient votre
base de données.
Le module mysql.connector utilise l’espace réservé %s pour
échapper aux valeurs de l’instruction delete :

Idriss TAZIGH BD
 Plan
SQL et Python
Fonctions et procédures
Déclencheurs
requêtes récursives
Aggregation avancée
OLAP

Python et MySql

1 import mysql . connector

2 mydb = mysql . connector . connect (
3 host = " localhost " ,
4 user = " username " ,
5 passwd = " password " ,
6 database = " madatabase "
7 )
8 mycursor = mydb . cursor ()
9 sql = " DELETE FROM Client WHERE addresse = % s "
10 adr = ( " Tinghir " , )
11 mycursor . execute ( sql , adr )
12 mydb . commit ()
13 print ( mycursor . rowcount , " enregistrement supprime " )

Idriss TAZIGH BD
 Plan
SQL et Python
Fonctions et procédures
Déclencheurs
requêtes récursives
Aggregation avancée
OLAP

Fonctions et procédures

Les fonctions et procédures permettent à la ≪logique

métier≫ d’être stockée dans la base de données et exécutée à
partir d’instructions SQL.
Ceux-ci peuvent être définis soit par le composant procédural
de SQL, soit par un langage de programmation externe tel que
Python, Java, C ou C ++.
La syntaxe que nous présentons ici est définie par le standard
SQL.
La plupart des bases de données implémentent des versions
non standard de cette syntaxe.

Idriss TAZIGH BD
 Plan
SQL et Python
Fonctions et procédures
Déclencheurs
requêtes récursives
Aggregation avancée
OLAP

Fonctions avec retour entier

Définissons une fonction qui, étant donné le nom d’un

département, renvoie le nombre d’enseignant dans ce
département.
1 create function dept_count ( nom_dept varchar (20) )
2 returns integer
3 begin
4 declare d_count integer ;
5 select count (*) into d_count
6 from Enseignant
7 where Enseignant . nom_dept = nom_dept
8 return d_count ;
9 end

Idriss TAZIGH BD
 Plan
SQL et Python
Fonctions et procédures
Déclencheurs
requêtes récursives
Aggregation avancée
OLAP

Fonctions avec retour entier

La fonction dept count peut être utilisée pour trouver les

noms de département et le budget de tous les départements
avec plus de 12 Enseignants.
1 select nom_dept , budget
2 from departement
3 where dept_count ( nom_dept ) > 12

Idriss TAZIGH BD
 Plan
SQL et Python
Fonctions et procédures
Déclencheurs
requêtes récursives
Aggregation avancée
OLAP

Fonctions avec retour table

Le standard SQL prend en charge les fonctions qui peuvent

renvoyer des tables en tant que résultats.
Renvoyer tous les Enseignant d’un département donné :
1 create function Enseignant_de ( nom_dept char (20) )
2 returns table ( ID varchar (5) ,
3 name varchar (20) ,
4 dept_name varchar (20) ,
5 salary numeric (8 ,2) )
6 return table ( select ID , nom , nom_dept , salaire
7 from Enseignant
8 where Enseignant . nom_dept = Enseignant_de .
nom_dept )

Idriss TAZIGH BD
 Plan
SQL et Python
Fonctions et procédures
Déclencheurs
requêtes récursives
Aggregation avancée
OLAP

Fonctions avec retour table

Utilisation
1 select *
2 from table ( Enseignant_de ( ’ Informatique ’) )

Idriss TAZIGH BD
 Plan
SQL et Python
Fonctions et procédures
Déclencheurs
requêtes récursives
Aggregation avancée
OLAP

Procédures
La fonction dept count pourrait à la place être écrite comme
procédure :
1 create procedure dept_count_proc ( in dept_name
varchar (20) , out d_count integer )
2 begin
3 select count (*) into d_count
4 from Enseignant
5 where Enseignant . nom_dept = dept_count_proc .
nom_dept
6 end
Les mots-clés in et out sont des paramètres auxquels on
s’attend à ce que des valeurs leur soient affectées et des
paramètres dont les valeurs sont définies dans la procédure
afin de renvoyer des résultats.
Idriss TAZIGH BD
 Plan
SQL et Python
Fonctions et procédures
Déclencheurs
requêtes récursives
Aggregation avancée
OLAP

Procédures
Les procédures peuvent être appelées depuis une procédure
SQL ou depuis un langage de programmation , à l’aide de
l’instruction call.
1 declare d_count integer ;
2 call dept_count_proc ( ’ Informatique ’ , d_count ) ;

Les procédures et fonctions peuvent également être appelées à

partir de SQL dynamique
SQL autorise plus d’une procédure du tant que le nombre
d’arguments des procédures portant le même nom est
différent.
Le nom, ainsi que le nombre d’arguments, sont utilisés pour
identifier la procédure.
Idriss TAZIGH BD
 Plan
SQL et Python
Fonctions et procédures
Déclencheurs
requêtes récursives
Aggregation avancée
OLAP

Constructions de langage pour les procédures et les

fonctions

SQL prend en charge les constructions qui lui confèrent

presque toute la puissance d’un langage de programmation à
usage général.
la plupart des systèmes de base de données implémentent leur
propre variante de la syntaxe standard ci-dessous.
Instruction composée : begin end
Peut contenir plusieurs instructions SQL entre le début et la
fin.
Les variables locales peuvent être déclarées dans des
instructions composées

Idriss TAZIGH BD
 Plan
SQL et Python
Fonctions et procédures
Déclencheurs
requêtes récursives
Aggregation avancée
OLAP

Constructions de langage pour les procédures et les

fonctions
while et repeat et Boucle for :
1 while expression do
2 instructions ;
3 end while
4 repeat
5 instructions ;
6 until expression
7 end repeat
Branchement conditionnel : (if-then-else)
1 if expression
2 then instructions
3 elseif expression
4 then instructions2
5 else instructions3
Idriss TAZIGH BD
 Plan
SQL et Python
Fonctions et procédures
Déclencheurs
requêtes récursives
Aggregation avancée
OLAP

Déclencheurs
Un déclencheur est une instruction exécutée automatiquement
par le système en tant qu’effet secondaire d’une modification
de la base de données.
Pour concevoir un mécanisme de déclenchement, il faut :
Spécifiez les conditions dans lesquelles le déclencheur doit être
exécuté.
Spécifiez les actions à entreprendre lors de l’exécution du
déclencheur.
Déclencheurs introduits au standard SQL dans SQL : 1999,
mais pris en charge encore plus tôt en utilisant une syntaxe
non standard par la plupart des bases de données.
La syntaxe illustrée ici peut ne pas fonctionner exactement sur
votre système de base de données ; consultez les manuels du
système.
Idriss TAZIGH BD
 Plan
SQL et Python
Fonctions et procédures
Déclencheurs
requêtes récursives
Aggregation avancée
OLAP

Déclencheurs

L’événement déclencheur peut être une insertion, une

suppression ou une mise à jour.
Les déclencheurs lors de la mise à jour peuvent être limités à
des attributs spécifiques. exemple, champs salaire de
l’enseignant
Les valeurs des attributs avant et après une mise à jour
peuvent être référencées
référencer old row comme : pour les suppressions et les mises
à jour
référencer new row comme : pour les insertions et les mises à
jour

Idriss TAZIGH BD
 Plan
SQL et Python
Fonctions et procédures
Déclencheurs
requêtes récursives
Aggregation avancée
OLAP

Déclencheurs

Les déclencheurs peuvent être activés avant un événement, ce

qui peut servir de contraintes supplémentaires. Par exemple,
convertissez les grades vides en valeurs nulles.
1 create trigger setnull_trigger before update of
Enseignant
2 referencing new row as nrow
3 for each row
4 when ( nrow . grade = ’ ’)
5 begin atomic
6 set nrow . grade = null ;
7 end ;

Idriss TAZIGH BD
 Plan
SQL et Python
Fonctions et procédures
Déclencheurs
requêtes récursives
Aggregation avancée
OLAP

Déclencheurs

Au lieu d’exécuter une action distincte pour chaque ligne

affectée, une seule action peut être exécutée pour toutes les
lignes affectées par une transaction.
Utiliser for each statement au lieu de for each row
Utilisez referencing old table ou referencing new table pour
faire référence à des tables temporaires (appelées tables de
transition) contenant les lignes affectées
Peut être plus efficace lorsqu’il s’agit d’instructions SQL qui
mettent à jour un grand nombre de lignes

Idriss TAZIGH BD
 Plan
SQL et Python
Fonctions et procédures
Déclencheurs
requêtes récursives
Aggregation avancée
OLAP

requêtes récursives
SQL : 1999 permet la définition de vue récursive
Exemple : trouver quels cours sont un prérequis, que ce soit
directement ou indirectement, pour un cours spécifique
1 with recursive rec_prereq ( id_cours , id_prereq ) as
(
2 select id_cours , id_prereq
3 from prereq
4 union
5 select rec_prereq . id_cours , prereq .
id_prereq ,
6 from rec_rereq , prereq
7 where rec_prereq . id_prereq = prereq .
id_cours
8 )
9 select * from rec_prereq ;
Idriss TAZIGH BD
 Plan
SQL et Python
Fonctions et procédures
Déclencheurs
requêtes récursives
Aggregation avancée
OLAP

Aggregation avancée : Rank

Le rang se fait en conjonction avec un ordre par un attribut
spécifié.
Exemple : Trouver le rang des étudiants par rapport a la note :
1 select ID , rank () over ( order by note desc ) as
rang
2 from etudiant

Le classement peut être effectué à l’aide de l’agrégation SQL

de base, mais la requête résultante est très inefficace
1 select ID , (1 + ( select count (*)
2 from etudiant B
3 where B . note > A . note ) ) as rang
4 from etudiant A
5 order by rang ;

Idriss TAZIGH BD
 Plan
SQL et Python
Fonctions et procédures
Déclencheurs
requêtes récursives
Aggregation avancée
OLAP

Aggregation avancée : Rank

Le classement peut être effectué dans la partition des données.
Exemple :Trouvez le rang des étudiants dans chaque
département.
1 select ID , nom_dept ,
2 rank () over ( partition by nom_dept order by note
desc )
3 as dept_rang
4 from etudiant
5 order by nom_dept , dept_rang ;
Plusieurs clauses de rang peuvent apparaı̂tre dans une seule
clause de sélection.
Le classement est effectué après l’application de la clause
group by
Peut être utilisé pour trouver les n premiers meilleurs résultats
Idriss TAZIGH BD
 Plan
SQL et Python
Fonctions et procédures
Déclencheurs
requêtes récursives
Aggregation avancée
OLAP

OLAP

Online Analytical Processing (OLAP)

résumer et de visualiser les données de différentes manières en
ligne (avec un délai négligeable)
Les données qui peuvent être modélisées sous forme
d’attributs de dimension et d’attributs de mesure sont
appelées données multidimensionnelles.

Idriss TAZIGH BD
 Plan
SQL et Python
Fonctions et procédures
Déclencheurs
requêtes récursives
Aggregation avancée
OLAP

Cube de données
Un cube de données est une généralisation
multidimensionnelle d’un tableau croisé
Peut avoir n dimensions ; nous montrons 3 ci-dessous
Les tableaux croisés peuvent être utilisés comme vues sur un
cube de données

Idriss TAZIGH BD
 Plan
SQL et Python
Fonctions et procédures
Déclencheurs
requêtes récursives
Aggregation avancée
OLAP

Hiérarchies sur les dimensions

Hiérarchie sur les attributs de dimension : permet d’afficher

les dimensions à différents niveaux de détail

Idriss TAZIGH BD
 Plan
SQL et Python
Fonctions et procédures
Déclencheurs
requêtes récursives
Aggregation avancée
OLAP

Agrégation étendue pour prendre en charge OLAP

ensemble des attributs spécifiés
Exemple de relation pour cette section
ventes(nom article, couleur , taille, quantité)
Par exemple, considérez la requête
1 select nom_article , couleur , taille , sum ( number )
2 from ventes
3 group by cube ( nom_article , couleur , taille )
Cela calcule l’union de huit groupements différents de la
relation de vente :
{(nom article, couleur , taille), (nom article, couleur ),
(nom article, taille), (couleur, taille),
(nom article), (couleur), (Taille),( ) }
Idriss TAZIGH BD
 Plan
SQL et Python
Fonctions et procédures
Déclencheurs
requêtes récursives
Aggregation avancée
OLAP

Agrégation étendue pour prendre en charge OLAP

Pour chaque regroupement, le résultat contient la valeur nulle

pour les attributs non présents dans le groupement.
La fonction grouping() peut être appliquée sur un attribut
Renvoie 1 si la valeur est une valeur nulle représentant tout et
renvoie 0 dans tous les autres cas.
1 select nom_article , couleur , taille , sum ( number ) ,
2 grouping ( nom_article ) as nom_article_flag ,
3 grouping ( couleur ) as couleur_flag ,
4 grouping ( taille ) as taille_flag ,
5 from ventes
6 group by cube ( nom_article , couleur , taille )

Idriss TAZIGH BD
 Plan
SQL et Python
Fonctions et procédures
Déclencheurs
requêtes récursives
Aggregation avancée
OLAP

Agrégation étendue pour prendre en charge OLAP

Peut utiliser la fonction decode () dans la clause select pour

remplacer ces nulls par une valeur telle que all Par exemple,
remplacez nom article dans la première requête par
decode(grouping (nom article), 1,′ all ′ , nom article)

Idriss TAZIGH BD
 Plan
SQL et Python
Fonctions et procédures
Déclencheurs
requêtes récursives
Aggregation avancée
OLAP

Agrégation étendue pour prendre en charge OLAP

La construction rollup génère une union sur chaque préfixe de

la liste d’attributs spécifiée.
1 select nom_article , couleur , taille , sum ( number )
2 from ventes
3 group by rollup ( nom_article , couleur , taille )

Génère l’union de quatre groupements :

{(nom article, couleur , taille), (nom article, couleur ), (nom article), ()
Le cumul peut être utilisé pour générer des agrégats à
plusieurs niveaux d’un hiérarchie.

Idriss TAZIGH BD
 Plan
SQL et Python
Fonctions et procédures
Déclencheurs
requêtes récursives
Aggregation avancée
OLAP

Implémentation OLAP

Les premiers systèmes OLAP utilisaient des tableaux

multidimensionnels en mémoire pour stocker des cubes de
données et sont appelés systèmes OLAP multidimensionnels
(MOLAP).
Les implémentations OLAP utilisant uniquement les
fonctionnalités de base de données relationnelle sont appelées
systèmes OLAP relationnels (ROLAP).
Les systèmes hybrides, qui stockent certains résumés en
mémoire et stockent les données de base et d’autres résumés
dans une base de données relationnelle, sont appelés systèmes
OLAP hybrides (HOLAP).

Idriss TAZIGH BD