VIII.LE LANGAGE SQL
C'est un langage fourni avec tout SGBD relationnel commercialisé. C'est un standard reconnu par l'IS0 depuis 87 (standard donc portabilité). On en est à la version 2 (SQL92) et la version 3 est annoncée pour bientôt. SQL est un LDD et un LMD. Il est aussi utilisé pour définir des vues, les droits d'accès, manipulation de schéma physique…
4.1) Structure de base
Une requête SQL typique est de la forme
SELECT A1,, ... , An
FROM r1,... ,rm
WHERE P
Les Ai sont des attributs, les rj sont des noms de relations et P est un prédicat.
Cette requête est équivalente à A1 ,…, An ( P(r1 x … x rm))
La clause SELECT
La clause SELECT correspond à la projection de l'algèbre.
Les titres des films :
SELECT Titre
FROM film
L'utilisation de l'astérisque permet de sélectionner tous les attributs :
SELECT *
FROM film
SQL autorise par défaut les doublons. Pour le forcer à les éliminer, on utilise la clause DISTINCT : SELECT DISTINCT Titre
FROM film
SELECT peut contenir des expressions arithmétiques ainsi que le renommage d'attributs :
SELECT Prix_HT * 1.206 AS Prix TTC
FROM produit
La clause WHERE
Elle correspond au prédicat de sélection dans l'algèbre. La condition porte sur des attributs des relations qui apparaissent dans la clause FROM
SELECT DISTINCT Titre
FROM film
WHERE Réalisateur "Bergman" AND Acteur = "Stewart"
SQL utilise les connecteurs AND, OR et NOT. Pour simplifier la clause WHERE, on peut utiliser la clause BETWEEN.
SELECT Num
FROM compte
WHERE Solde BETWEEN 0 AND 10000
La clause FROM
Elle correspond au produit cartésien de l'algèbre.
Le titre et le réalisateur des films programmés à l'UGC de Bordeaux.
SELECT Titre, Réalisateur
FROM film, programme
WHERE film.titre = programme.titre AND programme.NomCiné = "UGC"
Les variables n-uplets
Elles sont définies dans la clause FROM
SELECT Titre, Réalisateur
FROM film AS f, programme AS p
WHERE f.titre = p.titre AND p.NomCiné = "UGC"
Soit Emp (Id, Nom, Id_chef)
SELECT el.Nom, e2.Nom AS Nom_Chef
FROM emp el, emp e2
WHERE el.Id_chef = e2.Id
La clause ORDER BY
SQL permet de trier les résultats de requête
SELECT *
FROM programme
WHERE NomCiné="UGC"
ORDER BY Horaire ASC, Titre DESC
4.2) Opérateurs ensemblistes
SELECT ...
…
UNION/ INTERSECT/ EXCEPT
SELECT ...
Attention : Ces opérations éliminent les doublons, pour pouvoir les garder, utiliser à la place INTERSECT ALL... Si t apparaît m fois dans r et n fois dans s alors il apparaît :
m + n fois dans r UNION ALL s
min(m, n) fois dans r INTERSECT ALL s
max(0, m - n) fois dans r EXCEPT ALL s
4.3) Les fonctions d’agrégats
Ce sont des fonctions qui agissent sur des ensembles (multi-ensembles) de valeurs :
AVG : la valeur moyenne de l'ensemble
MIN : la valeur minimale
MAX : la valeur maximale
SUM : le total des valeurs de l'ensemble
COUNT : le nombre de valeur dans l'ensemble
SELECT COUNT(Titre) FROM Programme
Cette requête retourne le nombre de films programmés à Bordeaux.
Attention : Un même titre peut être compté plusieurs fois s'il est programmé à des heures différentes et dans des salles différentes.
SELECT COUNT( DISTINCT Titre) FROM Programme
Agrégats et GROUP BY
Le nombre de films programmés dans chaque salle :
SELECT NomCiné, COUNT (DISTINCT Titre)
FROM Programme
GROUP BY NomCiné
Les attributs qui apparaissant dans la clause SELECT en dehors des agrégats doivent être associés à la clause GROUP BY
Agrégats et la clause HAVING
Les salles où sont programmés plus de 3 films :
SELECT NomCiné, COUNT(DISTINCT Titre)
FROM Programme
GROUP BY NomCiné
HAVING COUNT (DISTINCT Titre) > 3
Le prédicat associé à la clause HAVING est testé après la formation des groupes définis dans la clause GROUP BY.
4.4) Requêtes imbriquées
SQL fournit un mécanisme qui permet d'imbriquer les requêtes. Une sous requête est une requête SQL (SELECT-FROM-WHERE) qui est incluse dans une autre requête. Elle apparaît au niveau de la clause WHERE de la première requête.
Les films programmés à l'UGC non programmés au Trianon
SELECT Titre
FROM Programme
WHERE NomCiné="UGC" and Titre NOT IN (
SELECT Titre
FROM Programme
WHERE NomCiné ="Trianon" )
Trouver les comptes dont les soldes sont supérieurs aux soldes des comptes de Durand :
Compte (Num, Solde, NomTit)
SELECT *
FROM Compte
WHERE Solde > ALL (
SELECT Solde
FROM Compte
WHERE NomTit = "Durand" )
En remplaçant ALL par SOME, on obtient les comptes dont les soldes sont supérieur au solde d'au moins un compte de Durand.
Les cinémas qui passent tous les films programmés àl'UGC
SELECT NomCiné
FROM programme pi
WHERE NOT EXISTS (
(SELECT DISTINCT Titre
FROM programme
WHERE NomCiné-"UGC")
EXCEPT
(SELECT DISTINCT Titre
FROM programme p2
WHERE pl.NomCiné = p2.NomCiné))
Test d'absence de doublons
La clause UNIQUE permet de tester Si une sous requête contient des doublons.
Les titres de films programmés dans une seule salle et un seul horaire :
SELECT p.Titre
FROM programme p
WHERE UNIQUE (
SELECT pl.Titre
FROM programme p1
WHERE p.Titre = pl Titre)
Les relations dérivées
Titulaire (Nom, Adresse)
Compte (Num, Solde, NomTit)
Donner le solde moyen des comptes de chaque personne ayant un solde moyen > à 1000
SELECT NomTit, SoldeMoyen
FROM (
SELECT NomTit, AVG(Solde)
FROM Compte
GROUP BY NomTit )
AS Result (NomTit, SoldeMoyen)
WHERE SoldeMoyen > 1000
Noter qu'on aurait pu exprimer cette requête en utilisant la clause HAVING
4.5) Les vues
Equivalent à une requête Access. Une vue peut être considérée comme une relation quelconque lors de l’expression des requêtes. Une vue est une relation virtuelle dans le sens où elle ne contient pas effectivement des « tuples ». Elles permettent de définir des relations virtuelles dans le but de :
Cacher certaines informations à des utilisateurs,
Faciliter l'expression de certaines requêtes,
Améliorer la présentation de certaines données.
Une vue est définie par une expression de la forme :
CREATE VIEW V AS requête
Requête est une expression quelconque de requête et V est le nom de la vue.
Emp (NumE, Salaire, Dept, Adresse)
CREATE VIEW EmpGen AS (
SELECT NumE, Dept, Adresse
FROM Emp )
Toutes les informations concernant les employés du département 5 :
SELECT *
FROM EmpGen
WHERE Dept = 5
4.6) Modification des relations Suppression :
Supprimer tous les employés du département 5 : DELETE FROM Emp
WHERE Dept = 5
Supprimer du programme tous les films programmés à l'UGC où un des acteurs est DiCaprio :
DELETE FROM programme
WHERE NomCiné = "UGC' AND EXISTS (
SELECT Titre
FROM film
WHERE programme.Tite = film.Titre AND film.Acteur = "DiCaprio" )
Supprimer les comptes dont le solde est < à la moyenne des soldes de tous les comptes :
DELETE FROM compte
WHERE Solde < (SELECT AVG (Solde) FROM compte)
Problème : Si les n-uplets sont supprimés un à un de la relation compte, alors à chaque suppression, on a une nouvelle valeur de AVG (Solde). La solution de SQL est de d'abord, calculer AVG(Solde) et ensuite de supprimer les tuples satisfaisant le test sans recalculer à chaque fois la nouvelle valeur de AVG (Solde).
Insertion
Insérer un n-uplet dans la relation "compte" :
INSERT INTO compte (Num, Solde, NomTit) VALUES (511,1000, "Dupont")
ou bien INSERT INTO compte VALUES (511,1000, «Dupont")
Insère un n-uplet avec un solde inconnu.
INSERT INTO compte VALUES (511, NULL, "Dupont")
ou bien INSERT INTO compte(Num, NomTit) VALUES (511, "Dupont")
Les 2 dernières MAJ sont équivalentes sauf si une valeur par défaut du Solde a été spécifiée lors de la définition de la table compte.
Supposons qu'on a crée une relation TitMoy (NomTit, Moyenne) qui doit contenir le nom des clients de la banque ainsi que la moyenne des soldes de leurs comptes.
INSERT INTO TitMoy (NomTit, Moyenne)
SELECT NomTit, AVG(Solde)
FROM compte
GROUP BY NomTit
Update
Rajouter 1% à tous les comptes dont le solde est inférieur à 500 :
UPDATE compte
SET Solde = Solde * 1.01
WHERE Solde 500
La condition qui suit la clause WHERE peut être une requête SQL.
SQL EN TANT QUE LDD
Le schéma des relations
Les domaines des attributs
Les contraintes d'intégrité
La gestion des autorisations
La gestion du stockage physique
Les index associés à chaque relation
5.1) Domaines
char(n) : chaîne de caractères de taille fixe n
varchar(n) : chaîne de caractères de taille variable mais inférieure à n
int : Entier (un sous ensemble fini des entiers, dépend de la machine)
smallint : Entier. Sous ensemble de int
numeric(p,d) : Réel codé sur p digits et max d digits pour partie à droite de la décimale.
date : YYYY-MM-DD (année, mois, jours)
time : HH :MM :SS (heure, minute, seconde)
Les valeurs nulles (NULL) sont possibles dans tous les domaines. Pour déclarer qu'un attribut ne doit pas être nul, il faut rajouter NOT NULL
CREATE DOMAIN nom-client char(20)
5.2) Création des tables
On utilise la clause CREATE TABLE
CREATE TABLE compte (
Num int NOT NULL,
Solde int,
NomTit varchar( 20))
CREATE TABLE compte (
Num int NOT NULL,
Solde int DEFAULT 0,
NomTit varchar(20),
PRIMARY KEY (Num),
CHECK (Num > 1) )
En SQL92, si un attribut est clé alors il est différent de NULL.
Manipulation de schéma
La commande DROP TABLE permet de supprimer une table.
Ex: DROP TABLE compte.
Si une vue est définie sur la table compte alors il faut utiliser
DROP TABLE compte CASCADE
La commande ALTER TABLE permet de modifier le schéma d'une relation.
Exemple : ALTER TABLE compte ADD Date_ouverture
date
ALTER TABLE compte DROP Solde CASCADE
Clé étrangère
Soient Personne (NSS, Nom) et Voiture (Matricule, modèle, Proprio).
« Proprio » correspond au NSS du propriétaire. C'est une clé étrangère dans le schéma Voiture car c'est une clé dans un autre schéma.
CREATE TABLE Voiture (
Matricule CHAR(8),
Modele CHAR(10),
Proprio CHAR(3),
PRIMARY KEY(Matricule),
FOREIGN KEY(Proprio) REFERENCES Personne
ON [DELETE | UPDATE] CASCADE |
RESTRICT |
SET NULL
)
CASCADE: Si une personne est supprimée, alors les voitures qu'elle possède sont supprimées.
RESTRICT : Le système refuse la suppression d'une personne s'il y a des voitures qui lui sont rattachées. C'est l'option par défaut.
SET NULL: Si une personne est supprimée, alors l'attribut Proprio prend la valeur NULL.
L'insertion d'une voiture ne peut se faire que si le «proprio » existe dans Personne (ou bien valeur nulle).
Va1eurs nu11es
-
Employé
|
Nom
|
Salaire
|
|
Dupont
|
10 000
|
|
Martin
|
NULL
|
SELECT *
FROM Employé
WHERE Salaire > 12000 Ne retourne aucun tuple.
Pareil si la condition est WHERE Salaire < 8000
SELECT SUM (Salaire) FROM Employé; Retourne 10000
SELECT COUNT (Salaire) FROM Employé; Retourne 2
SELECT AVG (Salaire) FROM Employé; Retourne 10000
Très différent de SELECT SUM (Salaire) / COUNT (Salaire) FROM Employé car COUNT prend en compte la valeur NULL donc cela fera 10000/2=5000.
En fait c’est équivalent à :SELECT SUM (Salaire) / COUNT (Salaire) FROM Employé
WHERE Salaire IS NOT NULL
SELECT COUNT(*) FROM Employé
WHERE Salaire IS NOT NULL; Retourne 1
Mise à jour des vues
Personne (Nom, Salaire). Supposons que la table Personne est vide.
CREATE VIEW Gros_Salaire AS
SELECT *
FROM Personne
WHERE Salaire > 10000
INSERT INTO Gros_Salaire VALUES("Martin", 5000)
L'effet de cette commande est d'insérer dans la table Personne le tuple < "Martin", 5000>.
Noter que si l'on fait :
SELECT * FROM Gros Salaire; on n'obtient aucun tuple.
Si à la création de la vue on rajoute l’option :
WITH CHECK OPTION alors l'insertion est refusée.
Les mises à jours des vues sont traduites en des mises à jours des tables sous-jacente. La traduction n'est pas toujours unique. Quand nous avons plusieurs manières de traduire une mise à jour alors celle-ci est rejetée. Certaines vues ne permettent pas des mises à jour. Il faut une relation biunivoque entre la mise à jour de la vue et la mise à jour de la table.
Jointure externe
Si on fait : Personne IXI Voiture, on n'aura que les personnes qui ont une(des) voiture(s) qui sont dans le résultat.
SELECT *
FROM Personne P Left Outer Join Voiture V
ON P.NSS = V.Proprio
Cette requête retourne aussi les personnes n'ayant pas de voiture. Ces tuples auront des valeurs nulles pour les champs provenant de Voiture. Si on met juste Outer Join alors on aura les personnes sans voitures et les voitures sans Propriétaire.
La jointure est exprimée par : T1 Inner Join T2 On Condition
Dans l'exemple, si l'on veut joindre Personne et Voiture alors
SELECT *
FROM Personne P Inner Join Voiture V
ON P.NSS = V.Proprio
Si l’on mets Right à la place de Left dans la jointure, alors on aura les voitures sans les personnes. Si on ne mets ni Left ni Right, on aura les voitures et les personnes qui ne sont pas dans la jointure.
5.3) Mécanisme des droits
Soit la table pers (Num, Nom, Adr, Num_Serv, Salaire)
1. Dupont ne peut pas accéder à Pers.
2. Dupont peut lire une partie de Pers mais ne peut rien modifier.
3. Dupont Peut lire un seul tuple (celui le concernant) sans pouvoir le modifier.
4. Dupont peut en plus modifier l'attribut Adr.
5. Dupont peut accéder à l'attribut salaire mais seulement entre 9h et 17h à partir du terminal 25.
6. Dupont peut modifier salaire si celui-ci est inférieur à 8000.
7. Dupont peut modifier la relation s’il est responsable du Num_Serv du tuple qu'il veut modifier.
Les droits dépendent du contenant, du contexte et/ou du contenu.
Les droits dans SQL
SELECT: privilège qu'il faut posséder pour lire une table
INSERT, DELETE, UPDATE: privilèges nécessaires pour mettre à jour une table.
INSERT(X), UPDATE(X): privilège nécessaire pour insérer, mettre à jour l'attribut X.
Octroi et retrait de privilèges
GRANT privilège ON objet TO utilisateur [WITH GRANT OPTION]
REVOKE [GRANT OPTION FOR] privilège ON objet FROM utilisateur
RESTRICT | CASCADE
Exemples :
GRANT ALL ON TABLE résultat TO directeur WITH GRANT OPTION;
GRANT INSERT ON TABLE résultat TO sec_1;
GRANT SELECT, UPDATE(points) ON TABLE resultat TO prof_1;
Remarque : Un utilisateur peut recevoir le même privilège de plusieurs sources. Cela est utile quand l'une d'elles veut le lui retirer.
Exemple : soit la séquence
A: GRANT SELECT ON TABLE T TOBWITH GRANT OPTION
B: GRANT SELECT ON TABLE T TOCWITH GRANT OPTION
C: GRANT SELECT ON TABLE T TODWITH GRANT OPTION
A: REVOKE SELECT ON TABLE T FROM B
Ni B ni C ne pourront lire T
Utilisation des vues
CREATE VIEW Informations_Perso
AS SELECT *
FROM Employé
WHERE nom = USER;
GRANT SELECT, UPDATE(adresse)
ON Informations_Perso
TO PUBLIC
|
