Éléments de SQL

Chapitre 33

Éléments de SQL

33.1

La commande SELECT

Syntaxe :
  SELECT
    attributs
    expressions extraites
    FROM expressions de tables
    WHERE conditions de filtrage
    GROUP BY critères de regroupement
      HAVING conditions de filtrage sur les groupes
    ORDER BY critères de tri

33.1.1

Les attributs

Les attributs permettent de préciser si on souhaite inclure toutes les lignes (attribut ALL par défaut) ou seulement les lignes distinctes (attribut DISTINCT).

Exemple 8 SELECT cltloc FROM clients : on affiche les villes des clients avec répétition (il y a autant de lignes affichées que de clients). SELECT DISTINCT cltloc FROM clients : on affiche les villes des clients sans répétition.

33.1.2

Les expressions extraites

Les expressions extraites sont des champs de table, des expressions calculées faisant intervenir les champs, ou des expressions faisant intervenir des fonctions de calcul. Les fonctions utilisées peuvent être des fonctions qui s'appliquent à la valeur d'un champ (upper, year, ...) ou des fonctions de groupe qui effectuent un calcul sur l'ensemble des valeurs d'un champ dans un groupe (max, min, sum, count, avg).

Exemple 9 SELECT cmdnum, year(cmddate) FROM commandes : on affiche les numéros de commande avec l'année correspondante. SELECT cltnom + `` ``+ cltpnom FROM clients : on affiche la concaténation des nom et prénom des clients. SELECT AVG(artpv) FROM articles : on affiche le prix de vente moyen des articles.

33.1.3

Les expressions de tables

Les expressions de tables se présentent sous la forme d'une table, d'une jointure entre tables, d'une liste de tables. Les opérateurs de jointure seront : INNER JOIN, LEFT JOIN ou RIGHT JOIN qui correspondent respectivement à la jointure, la jointure externe gauche et la jointure externe droite.

Exemple 10 SELECT cltnom,cmdnum FROM clients INNER JOIN commandes ON cltnum=cmdclt
on affiche les noms des clients ayant passé une commande avec le numéro de commande correspondant (autant de fois que de commandes). SELECT cltnom,cmdnum FROM clients LEFT JOIN commandes ON cltnum=cmdclt
on affiche les noms des clients sans autre information s'ils n'ont pas passé de commandes et avec le numéro de commande correspondant (autant de fois que de commandes) sinon.

33.1.4

Les conditions de filtrage

Les conditions de filtrage correspondent à des sélections. On peut utiliser les opérateurs de comparaison usuels. On peut également utiliser les trois opérateurs BETWEEN, LIKE et IN. Remarque : Les conditions de jointure peuvent être introduites à l'intérieur de la clause WHERE. Dans ce cas, l'expression de tables correspond à la liste des tables utilisées. L'écriture obtenue est en général plus simple, mais il faut savoir que l'exécution peut être moins efficace. Par exemple, on peut écrire SELECT cltnom, cmdnum FROM clients, commandes WHERE cltnum=cmdclt en lieu et place de SELECT cltnom,cmdnum FROM clients INNER JOIN commandes ON cltnum=cmdclt.

Exemple 11 SELECT cmdnum FROM commandes WHERE year(cmddate)=1995 :
on affiche les numéros de commande de l'année 1995. SELECT artnom FROM articles WHERE artpv BETWEEN 100 and 200 :
on affiche les noms d'article dont le prix de vente est entre 100 et 200. SELECT cltnom FROM clients WHERE cltloc IN ('PARIS', 'LILLE') :
on affiche les noms des clients parisiens et lillois. SELECT cltnom FROM clients WHERE cltnom LIKE 'M*' :
on affiche les clients dont le nom commence par M.

33.1.5

Les critères de regroupement.

Un groupe est un sous-ensemble des tuples d'une table (qui peut être le résultat d'une requête), pour lesquels les valeurs du champ de regroupement reste constante. Les groupes sont spécifiés par la clause GROUP BY suivie du nom du champ sur lequel on effectue le regroupement. Les fonctions de groupe usuelles sont : MAX, MIN, SUM, COUNT, AVG. La clause HAVING est l'équivalent du WHERE appliqué aux groupes. Les conditions de filtrage de la clause HAVING sont des conditions qui portent sur les fonctions de groupe. Les critères de la clause WHERE s'appliquent aux tuples, ceux de la clause HAVING s'appliquent aux groupes. Le moteur SQL élimine d'abord les tuples qui ne satisfont pas aux conditions du WHERE, puis il constitue les groupes et leur applique les conditions du HAVING.

Exemple 12


    SELECT artnom, SUM(ldcqte) 
      FROM (articles JOIN ligcommandes ON artnum=ldcart) 
            JOIN commandes ON ldccmd=cmdnum
      WHERE YEAR(cmddate)=1995 
      GROUP BY artnom 
        HAVING SUM(ldcqte)>100

on affiche les noms d'articles avec la quantité totale commandée pour les articles commandés en plus de 100 exemplaires dans l'année 1995.

33.1.6

Les critères de tri

Les critères de tri (ascendant ou descendant) sont des noms de champs extraits, ou d'expressions extraites.

33.2

Composition de requêtes

33.2.1

La clause UNION

La clause UNION permet de réaliser la réunion du résultat de deux requêtes. Il faut que les structures sélectionnées soient compatibles.

Exemple 13


       SELECT artnom FROM articles 
         WHERE artpv>200 
     UNION 
       SELECT artnom FROM articles JOIN ligcommandes ON artnum=ldcart 
         WHERE ldcqte>10

on affiche les noms d'article de prix de vente supérieur à 200F ou qui ont été commandé dans une commande en plus de 10 exemplaires.

33.2.2

Imbrication de requêtes

On peut imbriquer les requêtes. Les conditions de filtrage peuvent donc porter sur le résultat de sous-requêtes.

La requête intérieure renvoie une valeur. Cette valeur peut être utilisée comme valeur de comparaison dans une clause WHERE.
Exemple 14
```
      SELECT artnom,artpv FROM articles 
        WHERE artpv < (SELECT artpv FROM articles WHERE artnum='A0056')
```
on affiche les noms et prix d'articles de prix de vente inférieur au prix de l'article de numéro A0056.
La requête intérieure renvoie un ensemble de valeurs. Elle peut être alors utilisée dans une clause WHERE comportant l'un des opérateurs IN, ANY, ALL et EXISTS. La condition expression IN sous-requête teste l'appartenance à un ensemble de valeurs fournies par la sous-requête qui ne doit comporter qu'un seul champ extrait. La condition expression > ANY sous-requête teste si l'expression est supérieure à au moins un élément de l'ensemble de valeurs fournies par la sous-requête qui ne doit comporter qu'un seul champ extrait. La condition expression > ALL sous-requête teste si l'expression est supérieure à tous les éléments de l'ensemble de valeurs fournies par la sous-requête qui ne doit comporter qu'un seul champ extrait. La condition EXISTS sous-requête a la valeur vrai si la sous-requête extrait un ensemble non vide de valeurs.
Exemple 15
```
      SELECT artnom FROM articles 
        WHERE artpv>ANY
          (SELECT artpv FROM articles WHERE artcoul='blanc')
```
on affiche les noms d'article de prix de vente supérieur au prix d'au moins un article de couleur blanche.

33.3

Gestion des transactions en SQL

Un SGBDR est constitué des éléments fondamentaux suivants :

Un moteur relationnel: responsable de toutes les opérations sur les données.
Un dictionnaire: contenant la description de toutes les données de la base.
Un système de gestion des transactions: qui assure que les données restent intègres ( ou cohérentes) quels que soient les évènements qui peuvent intervenir. Le but de ce paragraphe est de décrire succinctement le problème et les solutions apportées en SQL.

Une application est constituée d'une succession de groupes d'opérations. Chaque groupe forme une unité logique de travail qui est indivisible : c'est-à-dire toutes les opérations du groupe sont effectuées ou aucune ne l'est.

Exemple 16 En comptabilité, si une somme doit transiter d'un compte vers un autre, les deux opérations de mise à jour (crédit et débit) doivent s'effectuer toutes les deux ou ne pas s'effectuer du tout pour que les données restent cohérentes.

Quel que soit le système informatique utilisé, divers éléments peuvent perturber l'exécution d'une séquence d'instructions. Parmi ceux-ci, on peut citer : une erreur de programmation, une panne, l'exécution d'une tâche plus prioritaire. Ce dernier point sera très fréquent dans un environnement client-serveur où le nombre de tâches concurrentes est très élevé et dans lequel un client peut, à tout moment, demander l'exécution d'une application. Le mécanisme des transactions résout les problèmes dûs aux accès concurrents. Une transaction est un groupe d'opérations tel que toutes les opérations sont effectuées ou aucune ne l'est. Une transaction est vue de l'extérieur comme une opération atomique (c'est à dire une seule opération). Chaque transaction s'effectue indépendamment des autres (tout se passe comme si les transactions étaient exécutées l'une après l'autre). SQL peut gérer les transactions grace aux deux instructions COMMIT et ROLLBACK. L'instruction COMMIT demande au gestionnaire de transactions de rendre les modifications permanentes (aucun problème n'a été rencontré). L'instruction ROLLBACK signale la fin prématurée de la transaction et demande au gestionnaire de transactions de défaire tout ce qui a été fait depuis le début de la transaction, c'est-à-dire de retourner dans l'état cohérent correspondant au début de la transaction. Chaque instruction SQL forme en soi une transaction. Il n'y a pas d'instruction particulière pour préciser le début d'une transaction. Une transaction démarre chaque fois qu'on exécute une instruction. Par contre, une transaction est cloturée explicitement par les instructions ROLLBACK ou COMMIT. La norme SQL définit deux caractéristiques pour la transaction : son mode et son niveau d'utilisation. Le mode détermine les opérations possibles dans la transaction : READ ONLY (lecture seule) et READ WRITE (lecture et écriture). Le niveau d'isolation indique le comportement de la transaction par rapport aux autres transactions.

33.4

Contrôle de l'accès aux données

Cette partie concerne essentiellement l'administrateur de la base de données, mais un utilisateur intelligent doit posséder quelques notions sur les privilèges. Le principe fondamental est qu'un utilisateur ne peut effectuer une opération quelconque que s'il détient le privilège approprié pour cette opération. C'est l'administrateur qui donne (ou retire) les privilèges grace aux instructions GRANT et REVOKE.

33.4.1

Les privilèges

SELECT: privilège donnant accès à toutes les colonnes d'une table.
INSERT: permet l'insertion dans une table.
UPDATE(colonnes): : permet la modification de toutes les colonnes d'une table. En spécifiant certaines colonnes de la table, le privilège est limité à celles-ci.
DELETE: permet la suppression des lignes d'une table.
ALTER: pour modifier la structure d'une table.
INDEX: pour gérer les index d'une table.
ALL: tous les droits.

33.4.2

Les instructions GRANT et REVOKE

GRANT privilèges ON objet TO liste_utilisateurs: donner les privilèges sur les objets (tables, vues) à certains utilisateurs.
REVOKE privilèges ON objet FROM utilisateurs: retirer les privilèges.

Exemple 17 Permettre à tout le monde de lire la table fournisseurs :


        GRANT SELECT ON fournisseurs TO PUBLIC

Permettre à Fernand de mettre à jour les attributs lcdpdt et lcdqte de ligcommande :


        GRANT UPDATE(lcdpdt,lcdqte) ON ligcommande TO fernand

Donner tous les privilèges à Sophie sur la table clients sauf la suppression de lignes :


        GRANT ALL ON clients to sophie
        REVOKE DELETE ON clients to sophie

En combinant la notion de vue (partie visible de la base de données fournie à un utilisateur) et une gestion adéquate des privilèges, on atteint une grande souplesse de manoeuvre pour sécuriser les données.

D.Gonzalez (gonzalez@univ-lille3.fr) en date du 24 janvier 2002