• Le Langage SQL
• Le Langage de Définition de Données
• Le Langage de Manipulation de Données
• Le Module SQLite3 de Python
SQL (Structured Query Language)
Introduit par IBM
SQL est devenu le langage standard pour décrire et manipuler les BDR
SQL est l'interface logiciel/logiciel entre les applications et les BDR
Applications
SQL
ORACLE DB2 INGRES
SYBASE INFORMIX
3
SQL (Structured Query Language)
Les commandes SQL :
- De définition des données :
CREATE
DROP
ALTER
- De manipulation des données :
SELECT
INSERT
UPDATE
DELETE
- De contrôle des données :
Contrôle des accès concurrents
COMMIT ROLLBACK
Contrôle des droits d’accès
GRANT REVOKE
4
SQL-Langage de Définition de Données
CRÉATION DE TABLES
La commande CREATE TABLE crée la définition d'une table
Syntaxe :
CREATE TABLE table
( -- définition des colonnes
colonne type [ NOT NULL]
[ DEFAULT valeur ]
[ PRIMARY KEY ]
[ REFERENCES table ]
[ CHECK condition ] ,
-- contraintes de table
[ PRIMARY KEY (liste de colonnes) ],
... ,
[ FOREIGN KEY (liste de colonnes) REFERENCES table
... ,
[ CHECK condition ] ,
)
5
SQL-Langage de Définition de Données
CRÉATION DE TABLES
▪Principaux types de données
CHAR(n) , TEXT , SMALLINT , INTEGER , FLOAT , DATE
▪Contraintes d'intégrité
NOT NULL valeur null impossible
PRIMARY KEY clé primaire
FOREIGN KEY clé étrangère
CHECK plage ou liste de valeurs
6
SQL-Langage de Définition de Données
EXEMPLES
CREATE TABLE client
( IdCli CHAR(4) PRIMARY KEY ,
nom CHAR(20) ,
ville CHAR(30),
CHECK (ville IN ('Nice', 'Paris', 'Rome')) );
------------------------------------------------------------------------------
CREATE TABLE produit
( IdPro CHAR(6) PRIMARY KEY ,
nom CHAR(30) NOT NULL,
marque CHAR(30) ,
prix FLOAT,
qstock SMALLINT,
CHECK (qstock BETWEEN 0 AND 100) ,
CHECK (marque <> 'IBM' OR qstock < 10) );
SQL-Langage de Définition de Données
CREATE TABLE vente
(
IdCli CHAR(4) NOT NULL REFERENCES client ,
IdPro CHAR(6) NOT NULL ,
date DATE NOT NULL ,
qte INTEGER,
CHECK (qte BETWEEN 1 AND 10) ,
-- contrainte de table
PRIMARY KEY (IdCli, IdPro, date) ,
FOREIGN KEY (IdPro) REFERENCES produit
);
8
SQL-Langage de Définition de Données
SUPPRESSON DE TABLES
La commande DROP TABLE supprime une table
Syntaxe :
DROP TABLE nom_table
9
SQL-Langage de Manipulation de Données
INSERTION DE N-UPLETS
INSERT est la commande d'insertion des données en SQL
Exemple:
INSERT INTO client (IdCli, nom, ville)
VALUES ('c100', 'Duduche', 'Nice‘)
MODIFICATION DE N-UPLETS
UPDATE est la commande de mise à jour des données en SQL
Exemple:
UPDATE produit P SET [Link] = [Link] * 1.20 WHERE [Link] = 'p2'
SUPPRESSION DE N-UPLETS
DELETE est la commande de suppression des données en
SQL
Exemple:
DELETE FROM produit P WHERE [Link] = 'p4'
10
SQL-Langage de Manipulation de Données
INTERROGATION DES DONNEES
La commande SELECT permet de rechercher des données à partir
de plusieurs tables ; le résultat est présenté sous forme d'une table
réponse
La forme générale de SELECT
SELECT [DISTINCT] liste d'attributs, expressions
FROM liste de tables
WHERE qualification
GROUP BY attributs de partitionnement
HAVING qualification de groupe
ORDER BY liste de colonnes [ ASC | DESC ]
Exemple:
SELECT [Link]
FROM produit P
WHERE [Link] = 'p1'
11
SQL-Langage de Manipulation de Données
Exemple d’une requête dans les deux langages: Algébrique et SQL
Donner les noms des clients qui ont acheté le produit 'p1' ?
ALGÉBRIQUE
R1 = CLIENTVENTE ([Link]=[Link])
R2 = R1 (IdPro = 'p1')
R3 = R2 (Nom)
SQL
SELECT [Link]
FROM client C, vente V
WHERE [Link] = [Link]
AND [Link] = 'p1'
12
SQL-Langage de Manipulation de Données
LA COMMANDE SELECT
La commande SELECT permet de rechercher des données à
partir de plusieurs tables ; le résultat est présenté sous forme d'une
table réponse
Expression des projections
EXEMPLES
Q1 Donner les noms, marques et prix des produits
SELECT [Link], [Link], [Link]
FROM produit P
▪Contrairement à l’algèbre relationnelle, SQL n'élimine pas les
doublons
▪Pour éliminer les doublons il faut spécifier DISTINCT
Q2 Donner les différentes marques de produit
SELECT DISTINCT [Link]
FROM produit P
13
SQL-Langage de Manipulation de Données
▪Il est possible d'effectuer des opérations arithmétiques
(+, -, *, /) sur les colonnes extraites
Q3 Donner les références des produits et leurs prix majorés de 20%
SELECT [Link], [Link] * 1.20
FROM produit P
▪Une étoile (*) permet de lister tous les attributs
Q4 Donner tous les renseignements sur les clients
SELECT *
FROM client
14
SQL-Langage de Manipulation de Données
Expression des restrictions
Q5 Donner les noms des produits de marque IBM
SELECT [Link]
FROM produit P
WHERE [Link] = 'IBM'
▪La condition de recherche (qualification) est spécifiée après la clause
WHERE par un prédicat
▪Un prédicat simple peut-être :
- un prédicat d’égalité ou d’inégalité (=, <>, <, >, <=, >=)
- un prédicat LIKE
- un prédicat BETWEEN
- un prédicat IN
- un test de valeur NULL
- un prédicat EXISTS
- …
▪Un prédicat composé est construit à l’aide des connecteurs AND, OR
et NOT
15
SQL-Langage de Manipulation de Données
▪Exemples
Q6 Lister les clients dont le nom comporte la lettre A en 2ième position
SELECT *
FROM client C
WHERE [Link] LIKE '_A%'
Q7 Lister les produits dont le prix est compris entre 5000 et 12000
SELECT *
FROM produit P
WHERE [Link] BETWEEN 5000 AND 12000
Q8 Lister les produits de marque IBM, Apple ou Dec
SELECT *
FROM produit P
WHERE [Link] IN ('IBM', 'Apple', 'Dec')
Q9 Lister les produits dont le prix est inconnu
SELECT *
FROM produit P
WHERE [Link] IS NULL
16
SQL-Langage de Manipulation de Données
Tri du résultat d'un SELECT
La clause ORDER BY permet de spécifier les colonnes définissant les
critères de tri
▪Le tri se fera d'abord selon la première colonne spécifiée, puis
selon la deuxième colonne etc...
▪L'ordre de tri est précisé par ASC (croissant) ou DESC (décroissant) ;
par défaut ASC
▪Exemple
Q10 Lister les produits en les triant par marques et à l'intérieur d'une marque par prix
décroissants
SELECT *
FROM produit P
ORDER BY [Link], [Link] DESC
17
SQL-Langage de Manipulation de Données
Expression des jointures
Le produit cartésien s'exprime simplement en incluant plusieurs
tables après la clause FROM
▪La condition de jointure est exprimée après WHERE
▪Exemples :
Q11 Donner les références et les noms des produits vendus
SELECT [Link], [Link]
FROM produit P , vente V
WHERE [Link] = [Link]
Q12 Donner les noms des clients qui ont acheté le produit de nom 'PS1'
SELECT [Link]
FROM client C , produit P, vente V
WHERE [Link] = [Link]
AND [Link] = [Link]
AND [Link] = 'PS1'
18
SQL-Langage de Manipulation de Données
Auto-jointure
Q13 Donner les noms des clients de la même ville que John
SELECT [Link]
FROM client C1 , client C2
WHERE [Link] = [Link]
AND [Link] = 'John'
AND [Link] <> 'John‘
Cet exemple utilise, pour le couplage des villes, la jointure de la table
Client avec elle-même (auto-jointure)
19
SQL-Langage de Manipulation de Données
Sous-requêtes
SQL permet l'imbrication de sous-requêtes au niveau de la clause WHERE
d'où le terme "structuré" dans Structured Query Language
▪Les sous-requêtes sont utilisées :
dans des prédicats de comparaison (=, <>, <, <=, >, >=)
dans des prédicats IN
dans des prédicats EXISTS …
▪Une sous-requête dans un prédicat de comparaison doit se réduire à une
seule valeur ("singleton select" )
▪Une sous-requête dans un prédicat INdoit représenter une table à colonne
unique
▪L'utilisation de constructions du type "IN sous-requête" permet
d'exprimer des jointures de manière procédurale ...
20
SQL-Langage de Manipulation de Données
▪Exemple
Q14 Donner les noms des clients qui ont acheté le produit 'p1'
Avec sous-requête
SELECT [Link]
FROM client C
WHERE IdCli IN (
SELECT [Link]
FROM vente V
WHERE [Link] = 'p1'
)
Avec jointure
SELECT [Link]
FROM client C , vente V
WHERE [Link] = [Link]
AND [Link] = 'p1'
De préférence, utiliser la jointure
21
SQL-Langage de Manipulation de Données
Requêtes quantifiées
Le prédicat EXISTS
Il permet de tester si le résultat d'une sous-requête est vide ou non
Q15 Donner les noms des produits qui n'ont pas été acheté
SELECT [Link]
FROM produit P
WHERE NOT EXISTS
( SELECT *
FROM vente V
WHERE [Link] = [Link] )
22
SQL-Langage de Manipulation de Données
Il permet de répondre à des questions quantifiées par "pour tous..." :
x | P(x) ( x | P(x) )
Q16 Donner les noms des produits qui ont été achetés par tous les clients de Nice
SELECT [Link]
FROM produit P
WHERE NOT EXISTS
( SELECT *
FROM client C
WHERE [Link] = 'Nice‘
AND NOT EXISTS
(
SELECT *
FROM vente V
WHERE [Link] = [Link]
AND [Link] = [Link]
)
)
23
SQL-Langage de Manipulation de Données
Expression des unions
SQL permet d'exprimer l'opération d'union en connectant des
SELECT par des UNION
Q17 Donner les nos des produits de marque IBM ou ceux achetés par le client no 'c1'
SELECT [Link]
FROM produit P
WHERE [Link] = 'IBM'
UNION
SELECT [Link]
FROM vente V
WHERE [Link] = 'c1'
▪L'union élimine les doublons, pour obtenir les doublons
il faut spécifier ALL après UNION
24
SQL-Langage de Manipulation de Données
Fonctions de calculs
SQL fournit des fonctions de calcul opérant sur l'ensemble des
valeurs d'une colonne de table:
COUNT nombre de valeurs
SUM somme des valeurs
AVG moyenne des valeurs
MAX plus grande valeur
MIN plus petite valeur
Q18 Donner le nombre total de clients
SELECT COUNT ( IdCli )
FROM client
Ou bien :
SELECT COUNT ( * )
FROM client
Q19 Donner le nombre total de clients ayant acheté des produits
SELECT COUNT ( DISTINCT IdCli )
FROM vente
25
SQL-Langage de Manipulation de Données
▪Si l'argument est un ensemble vide, la fonction COUNT renvoie
la valeur 0, les autres fonctions renvoyant la valeur NULL
▪Exemples :
Q20 Donner le nombre total de 'PS1' vendus
SELECT SUM ( [Link] )
FROM vente V , produit P
WHERE [Link] = [Link]
AND [Link] = 'PS1'
Q21 Donner les noms des produits moins chers que la moyenne des prix de tous les
produits
SELECT [Link]
FROM produit P1
WHERE [Link] <
(
SELECT AVG ( [Link] )
FROM produit P2
)
26
SQL-Langage de Manipulation de Données
La clause GROUP BY
La clause GROUP BY permet de partitionner une table en plusieurs
groupes
▪Toutes les lignes d'un même groupe ont la même valeur pour la liste
des attributs de partitionnement spécifiés après GROUP BY
▪Les fonctions de calcul opèrent sur chaque groupe de valeurs
▪Exemples :
Q22 Donner pour chaque référence de produit la quantité totale vendue
SELECT [Link], SUM ( [Link] )
FROM vente V
GROUP BY [Link]
27
SQL-Langage de Manipulation de Données
La clause HAVING
La clause HAVING permet de spécifier une condition de restriction des
groupes
▪Elle sert à éliminer certains groupes, comme WHERE sert à éliminer
des lignes
▪Exemples
Q23 Donner les noms des marques dont le prix moyen des produits est < 5000
SELECT [Link], AVG ( [Link] )
FROM produit P
GROUP BY [Link]
HAVING AVG ( [Link] ) < 5000
28
Le module SQLite de Python
Avec python, pour une application modeste, le plus simple est d'utiliser le
dialecte SQLite: une base de données qui est stockée dans seul fichier (pas
besoin de serveur):
29
Le module SQLite de Python
Importation du module SQLite3
import sqlite3
Création / Ouverture de la Base de Données
conn = [Link]("[Link]")
Création d'une interface avec la Base de Données
cur =[Link]()
• Création d'un objet-interface qui assure le dialogue entre le
programme et la BD.
• Appelé curseur.
• une sorte de tampon (mémoire intermédiaire), destinée à mémoriser
temporairement les données en cours de traitement avant leur
transfert définitif dans la BD.
30
Le module SQLite de Python
Exécution d'une requête SQL
[Link](“Une Requête SQL")
Attention :
A ce stade, les enregistrements sont dans le tampon du curseur,
mais ils n’ont pas encore été transférés véritablement dans la
Sauvegarde
base de données.
des données dans la BD
[Link]()
Le transfert effectif dans la base de données est déclenché par la
méthode commit() de l’objet connexion :
Fermeture du Curseur et de la BD
[Link]()
[Link]()
31
