Structured Query Language/Version imprimable
Une version à jour et éditable de ce livre est disponible sur la Wikiversité,
une bibliothèque de livres pédagogiques, à l'URL :
http://fr.wikiversity.org/wiki/Structured_Query_Language
Introduction
Introduction
modifierLe SQL est le langage utilisé pour manipuler des bases de données.
Structure d'une base de données
modifierSur un serveur, on trouve en général plusieurs bases de données.
Chacune contient des schémas, en général l'un d'entre eux se nomme "public", c’est sur celui-là que vous travaillons. Les autres schémas servent, par exemple, à donner aux utilisateurs une description de la base de données, ils sont donc créés et mis à jour directement par le Système de gestion de base de données relationnelles (SGBDR), nous n'avons pas à nous en occuper dans ce cours.
Chaque schéma contient plusieurs objets. Parmi ces objets, on peut trouver entre autres :
- Des tables
- Des vues
- Des fonctions et procédures
- Des domaines
- Des types utilisateur
- Des triggers
- D'autres objets spécifiques aux SGBDR
On effectue des requêtes SQL pour accéder ou modifier les données sur le serveur, à travers une interface graphique (exemples : PHPmyadmin, MS-Access…) ou un langage intermédiaire (exemple : PHP).
Tables
modifierLa table est certainement l’objet le plus manipulé dans une base de données. Elle est composée de plusieurs éléments.
Une table contient plusieurs colonnes avec des types bien précis.
Une table est le seul élément d'une base de données qui puisse contenir des données. Une ligne de données contient plusieurs cellules, en fait autant de cellules qu’il y a de colonnes dans la table. Par exemple, si on crée une table "armoires" avec 3 colonnes : numéro, position, nombre_de_tiroirs, alors chaque ligne sera composée de trois éléments. Par exemple 17, (205,459,309), 5. L'élément du milieu étant une coordonnée tri-dimensionnelle. Nous verrons plus tard comment créer des tables, ce concept de colonnes sera alors revu.
Les lignes de ce tableau sont appelées enregistrements.
Vues
modifierUne vue ne contient pas de données, mais elle en renvoie. Une vue est en fait une requête "pré-établie" qui s'utilise comme une table.
Fonctions / Procédures
modifierLa plupart des SGBDR permettent de créer des fonctions, comme dans presque tous les langages de programmation.
Domaines / types utilisateur
modifierLes domaines et types utilisateur sont des types créés par l'utilisateur. Chaque colonne doit avoir un type bien précis. Pour notre exemple ce serait "integer, Position3D, integer". Le type Integer existe par défaut dans tous les SGBDR, mais pas Position3D, il faut donc le créer, c’est ce qu'on appelle un type utilisateur.
Triggers
modifierLes triggers (mot anglais signifiant "déclencheurs") sont en fait des fonctions qui se déclenchent lorsqu'on insère, modifie ou supprime des données. Ils permettent par exemple de vérifier la validité des données insérées ou d'empêcher la suppression de données cruciales pour le reste de la base de données.
Création d'une base de données
modifierEn guise de test et de premier contact avec le langage SQL, nous allons tout simplement créer notre base de données. Pour cela exécuter l’ordre suivant :
CREATE DATABASE wikitests;
Si on traduit en français cela donne "Crée une base de données appelée wikitests".
À présent, nous devons nous connecter à cette base de données nouvellement créée. La façon de le faire est fortement liée au SGBDR qu'utilisé. Par exemple sous PostGreSQL, il faut faire :
/connect wikitests
Sous MySQL et MS-SQL :
USE wikitests
D'autres SGBDR obligent à se déconnecter du serveur puis à se reconnecter directement à la base.
Il est donc conseillé de se référer à la documentation du SGBDR employé si aucune des deux syntaxes ci-dessus ne fonctionne.
Via phpMyAdmin
modifierÀ partir d'un fichier
modifierDans l'onglet SQL, soit le fichier création.sql contenant les lignes ci-dessus, on crée donc la base de données avec :
source création.sql
Dans l'interface graphique
modifierDans l'onglet Bases de données, remplir le champ sous Créer une base de données, puis cliquer sur Créer.
Modélisation
Principe
modifierPour des raisons de cohérence, avant de se lancer dans la création des tables en SQL, il est impératif de modéliser toute la structure de la base de données relationnelles à l'avance.
Pour ce faire nous utilisons la méthode d'analyse Merise, qui consiste à passer du modèle conceptuel de données (MCD) au modèle logique de données (MLD), puis au modèle physique de données (MPD) qui dépend du logiciel SGBD à utiliser.
Mais il faut savoir qu’il existe aussi une méthode alternative plus internationale, nommée UML RUP.
MCD validé et en 3FN
modifierLe MCD est constitué d'au moins un diagramme de classes "entité" + document par classe, association et attributs. Il peut être dessiné avec un logiciel de traitement de texte, ou avec un logiciel capable de transformer le schéma en code SQL.
- Par classes : définition, identifiant, liste d'attributs, des contraintes d'intégrité (CI), les conditions de création d'un objet, (et de suppression).
- Par association : définition, liste d'attributs, (CI).
- Par attribut : définition, (CI), (domaine de valeur), (propriétés).
Il convient ensuite, après ébauche, de le faire passer à la troisième forme normale (3FN).
MLD brut
modifierOn transforme le diagramme de classe dans lequel on obtient des classes "normales" et des associations binaires.
- Règle de transformation
- Personnaliser les classes association.
- Les degrés > 2 par plusieurs associations binaires.
- Classes énumération → classes normales.
- Casser les associations (n)-(n) par une classe intermédiaire.
- Placer dans chaque classe un identifiant.
MLD optimisé
modifier- Mentionner le nombre d'objets estimés par classe, ainsi que l'accroissement.
- Mentionner le chemin d'accès direct aux objets des classes.
- Type 1 : Identifiant (placer une flèche contre la classe).
- Type 2 : Attribut autre ou groupe d'attributs.
- Type 3 : Une association .
Remarque : d'autres optimisations sont possibles mais comme elles entraînent une dénormalisation du modèle (donc redondance de l'information) on ne les utilisera que si vraiment elles sont indispensables : ex ; diminuer le temps d'accès aux données.
MPD
modifierIci nous sommes dans l'analyse technique : conception des fichiers ou de la base de données.
- Règles de transformation
- 1 classe = 1 table
- Identifiant → clé candidate
- Attributs de la classe → attributs de la table
- Association → clé étrangère
- Accès privilégié → index possible
Instruction CREATE TABLE
Pour la suite de la leçon, nous aurons besoin de quelques données de tests, il faut donc commencer par créer une table :
CREATE TABLE test (
id INTEGER NOT NULL PRIMARY KEY,
nom VARCHAR(30) NOT NULL,
points INTEGER,
quand TIMESTAMP NOT NULL
);
La requête ci-dessus assez standard, elle est établie par une norme que tous les SGBDR devraient respecter. Si toutefois elle ne s'exécutait pas ou renvoyait une erreur, il faudra rechercher dans la documentation spécifique au SGBDR utilisé.
La clé primaire s'incrémente automatiquement à chaque enregistrement, on peut néanmoins la préciser lors de l'insertion des données :
INSERT INTO test ( id, nom, points, quand ) VALUES
( 1, 'shepard', 384, '2007-01-01 00:00:00' ),
( 2, 'shepard', 194, '2007-01-03 12:51:25' ),
( 3, 'visiteur', 156, '2007-01-03 12:54:19' ),
( 4, 'shepard', 16, '2007-01-03 13:04:18' ),
( 5, 'somebody', 958, '2007-01-05 23:16:08' ),
( 6, 'somebody', 1084, '2007-01-06 02:15:59' ),
( 7, 'shepard', 453, '2007-01-10 15:32:06' ),
( 8, 'shepard', 35, '2007-01-10 15:43:15' ),
( 9, 'visiteur', 125, '2007-01-11 12:13:15' ),
( 10, 'somebody', 856, '2007-01-11 22:19:23' );
Autre exemple
modifierCREATE TABLE client
(Prenom char(50),
Nom char(50),
Adresse char(50),
Ville char(50),
Pays char(25),
DateDeNaissance date);
En mysql :
mysql> use test
Database changed
mysql> CREATE TABLE client
-> (Prenom char(50),
-> Nom char(50),
-> Adresse char(50),
-> Ville char(50),
-> Pays char(25),
-> DateDeNaissance date);
Query OK, 0 rows affected (0.08 sec)
mysql> describe client;
+-----------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+----------+------+-----+---------+-------+
| Prenom | char(50) | YES | | NULL | |
| Nom | char(50) | YES | | NULL | |
| Adresse | char(50) | YES | | NULL | |
| Ville | char(50) | YES | | NULL | |
| Pays | char(25) | YES | | NULL | |
| DateDeNaissance | date | YES | | NULL | |
+-----------------+----------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql>
Exemple avec une jointure
modifierOn remplace juste VALUES
par SELECT
:
INSERT INTO test (id, nom, points, quand) SELECT id, nom, points, quand FROM match;
Requêtes SELECT
La requête la plus simple ?
modifierNous allons commencer par analyser une requête simple qui renvoie toutes les données que nous avons créées au chapitre précédent :
SELECT * FROM test;
Remarquez que tous les ordres S.Q.L. se terminent par un point-virgule et que, par convention, les mots-clefs S.Q.L. sont écrits en majuscules.
Votre programme client S.Q.L. devrait vous renvoyer un tableau tel que celui-ci :
id | nom | points | quand |
---|---|---|---|
1 | shepard | 384 | 2007-01-01 00:00:00 |
2 | shepard | 194 | 2007-01-03 12:51:25 |
3 | visiteur | 156 | 2007-01-03 12:54:19 |
4 | shepard | 16 | 2007-01-03 13:04:18 |
5 | somebody | 958 | 2007-01-05 23:16:08 |
6 | somebody | 1084 | 2007-01-06 02:15:59 |
7 | shepard | 453 | 2007-01-10 15:32:06 |
8 | shepard | 35 | 2007-01-10 15:43:15 |
9 | visiteur | 125 | 2007-01-11 12:13:15 |
10 | somebody | 856 | 2007-01-11 22:19:23 |
La clause FROM
modifierLa commande précédente permet de lister toutes les données de la table « test » que nous venons de créer. Le mot-clef FROM permet simplement de dire au SGBDR de quel objet il faut prendre les données, en l’occurrence de l’objet « test » qui se trouve être une table.
Par ailleurs, FROM peut prendre d'autres arguments qu'un objet de type table, par exemple le nom d'une vue ou même une autre requête.
La seule obligation est que l’objet doit contenir des données réparties en colonnes, comme dans une table.
L'opérateur *
modifier- SELECT * peut se traduire par « lister toutes les colonnes des tables citées en clause FROM » en S.Q.L.. Donc la requête veut dire « Lister toutes les colonnes dans la table test ».
SELECT * FROM test;
- Passons à un autre exemple, aussi simple que le précédent :
SELECT id, nom FROM test;
Vous devriez deviner ce que va renvoyer cette requête : les colonnes « id » et « nom » de l’objet « test ».
id | nom |
---|---|
1 | shepard |
2 | shepard |
3 | visiteur |
4 | shepard |
5 | somebody |
6 | somebody |
7 | shepard |
8 | shepard |
9 | visiteur |
10 | somebody |
Mini-conclusion pour ce premier exemple
modifier- SELECT permet de lister les données qu'on veut voir dans le résultat final.
- L'intruction suivante permet de lister le contenu des colonnes "x", "y" et "z" de la table "a".
SELECT x, y, z FROM a;
- Plus loin, dans l'étude des requêtes multi-tabulaires, nous verrons qu’il n’est pas nécessaire d’utiliser des données dans toutes les tables impliquées dans une requête.
- L'opérateur * permet de lister le contenu de toutes les colonnes.
SELECT * FROM a;
- Notez que si vous cherchez à rendre vos requêtes aussi rapides que possible, utiliser * est déconseillé, mieux vaut écrire toutes les colonnes. <à argumenter et référencer>
Renommer des colonnes avec AS
modifierQuelques lignes pour vous présenter le renommage des colonnes.
Par défaut, quand vous faites
SELECT nom FROM test;
Le titre de l'unique colonne retournée est "nom". Si vous préférez que ce titre soit "pseudo", il suffit de faire :
SELECT nom AS pseudo FROM test;
L'intérêt peut vous paraître limité mais utilisé avec des fonctions il peut devenir indispensable pour éviter les confusions :
SELECT UPPER(nom) AS nom_majuscules, nom FROM test;
UPPER renvoie son argument en majuscules.
ou encore
SELECT SUM(cas_palu_enfant + cas_palu_adulte) as 'nb_cas' FROM enquete_palu;
SUM renvoie la somme de l’ensemble des valeurs des deux colonnes impliquées.
DISTINCT
modifierOn va s'amuser un tout petit peu (oui oui, S.Q.L. c’est amusant, vous verrez !)
Essayez la requête suivante :
SELECT nom FROM test;
Vous recevez la liste des 10 noms de la table :
nom |
---|
shepard |
shepard |
visiteur |
shepard |
somebody |
somebody |
shepard |
shepard |
visiteur |
somebody |
Bon imaginez maintenant que vous ne vouliez pas tous ces doublons qui ne servent à rien, que vous ne vouliez que les 3 noms "shepard", "visiteur" et "somebody".
Et bien en S.Q.L. c’est très simple d'obtenir ce résultat, on le fait simplement en ajoutant le mot-clef DISTINCT juste après SELECT :
SELECT DISTINCT nom FROM test;
nom |
---|
shepard |
visiteur |
somebody |
Bien sûr DISTINCT agit sur toute la ligne, ainsi la requête suivante vous renverra 10 lignes puisque les éléments de la colonne id sont uniques :
SELECT DISTINCT id, nom FROM test;
id | nom |
---|---|
1 | shepard |
2 | shepard |
3 | visiteur |
4 | shepard |
5 | somebody |
6 | somebody |
7 | shepard |
8 | shepard |
9 | visiteur |
10 | somebody |
DISTINCT n’est pas une fonction, juste un mot-clef, la requête suivante fonctionnera, mais n'aura pas vraiment de sens :
SELECT DISTINCT(id), nom FROM test;
Syntaxe de base
modifierUne version simplifiée de l’ordre SELECT selon la norme S.Q.L. pourrait être :
SELECT [DISTINCT] { * | col1, col2, ... }
FROM table
[WHERE conditions]
[GROUP BY colonnes groupées]
[HAVING conditions]
[ORDER BY colonnes de tri];
Les éléments entre crochets ne sont pas obligatoires ; il faut prendre un des éléments au choix des éléments entre accolades.
Les deux premières lignes devraient déjà vous être familières, passons à la suite.
WHERE
modifierLa clause WHERE vous permet d'appliquer un filtre sur les données, pour cela il nous faut connaitre les opérateurs de comparaison que voici :
opérateur | signification |
---|---|
a = b | a est égal à b |
a < b | a est plus petit que b |
a <= b | a est plus petit ou égal à b |
a > b | a est plus grand que b |
a >= b | a est plus grand ou égal à b |
a <> b | a est différent de b |
Ces opérateurs fonctionnent évidemment sur les nombres, mais également sur les chaînes de caractères et sur les dates.
Un exemple :
SELECT id, nom FROM test WHERE id >= 5;
id | nom |
---|---|
5 | somebody |
6 | somebody |
7 | shepard |
8 | shepard |
9 | visiteur |
10 | somebody |
Il est également possible de cumuler des conditions grâce aux opérateurs AND, OR et XOR (respectivement et, ou, et ou exclusif (l'un ou l'autre mais pas les deux)). Les parenthèses peuvent servir à organiser ces conditions.
Un autre exemple :
SELECT id, nom FROM test WHERE ( id >= 2 AND id <= 7 ) AND ( nom = 'shepard' OR nom = 'somebody' );
id | nom |
---|---|
2 | shepard |
4 | shepard |
5 | somebody |
6 | somebody |
7 | shepard |
Vous vous souvenez peut-être également que l’on peut définir ses propres types utilisateurs ? Et bien il est également possible de (re)définir la signification des opérateurs pour ces types, et même d’en créer de nouveaux.
BETWEEN
modifierOn aurait pu écrire la requête du dernier exemple différemment, les mots-clefs BETWEEN ... AND ... permettent de sélectionner des valeurs comprises entre deux bornes. Voici une requête qui renverra le même résultat qu'au-dessus mais avec BETWEEN :
SELECT id, nom FROM test WHERE id BETWEEN 2 AND 7 AND ( nom = 'shepard' OR nom = 'somebody' );
id | nom |
---|---|
2 | shepard |
4 | shepard |
5 | somebody |
6 | somebody |
7 | shepard |
IN
modifierEn fait, il y a moyen d'écrire encore plus élégamment cette requête grâce au mot-clef IN qui permet de vérifier qu'une valeur précise se trouve dans une liste de valeurs :
SELECT id, nom FROM test WHERE id BETWEEN 2 AND 7 AND nom IN ( 'shepard', 'somebody' );
id | nom |
---|---|
2 | shepard |
4 | shepard |
5 | somebody |
6 | somebody |
7 | shepard |
LIKE
modifierVoici un mot-clef un peu plus complexe : il permet de vérifier des expressions régulières simples.
Il admet deux types de « jokers » : % et _.
% remplace une chaine de caractères, y compris une chaine vide. _ remplace un caractère, mais pas un caractère vide.
Par exemple, si on veut tous les noms qui commencent par s :
SELECT DISTINCT nom FROM test WHERE nom LIKE 's%';
nom |
---|
shepard |
somebody |
Mettre une variable dans un LIKE au lieu d'un texte, entraine que si elle est nulle, tout sera sélectionné. |
NOT
modifierPour terminer, les 3 mots-clefs que nous venons d’utiliser peuvent tous être précédés d'un NOT qui inverse leur effet. Par exemple :
SELECT DISTINCT nom FROM test WHERE nom NOT LIKE 's%';
nom |
---|
visiteur |
GROUP BY
modifierGROUP BY est une clause de l’ordre SELECT permettant de regrouper des lignes.
En effet, GROUP BY nous permet de travailler non plus sur des lignes, mais sur des groupes de lignes. Le principal avantage que cela procure est qu’il existe des fonctions qu'on applique sur des groupes qui permettent, par exemple, de calculer le nombre de lignes d'un groupe, ou de calculer la moyenne des éléments d'une colonne d'un groupe, leur somme, leur minimum, leur maximum et d'autres fonctions statistiques (écart-type et variance par exemple font partie de la norme S.Q.L.).
SELECT nom FROM test GROUP BY nom;
En exécutant cette requête, vous vous rendrez compte qu'elle agit comme un DISTINCT ... En fait GROUP BY fait beaucoup plus que ça : il a, sans vous le dire, créé 3 groupes avec chacun 3 colonnes (vous pouvez également imaginer 3 « sous-tables ») :
- Un groupe shepard qui contient 5 lignes : (1, 384, "2007-01-01 00:00:00"), (2, 194, "2007-01-03 12:51:25"), (4, 16, "2007-01-03 13:04:18"), …
- Un groupe somebody qui contient 3 lignes : (5, 958, "2007-01-05 23:16:08"), …
- Un groupe visiteur qui contient 2 lignes : (3, 156, "2007-01-03 12:54:19"), …
Voyons maintenant ce que GROUP BY permet de faire :
SELECT COUNT(*), nom FROM test GROUP BY nom;
count | nom |
---|---|
3 | somebody |
2 | visiteur |
5 | shepard |
COUNT permet de compter le nombre de lignes que contient chaque groupe, et en effet ça correspond avec la description des 3 groupes qu'on a faite juste avant.
On pourrait également chercher à connaître le nombre de points accumulés par chaque personne, pour cela on utilise la fonction SUM sur la colonne points, comme ceci :
SELECT SUM(points), nom FROM test GROUP BY nom;
sum | nom |
---|---|
2898 | somebody |
281 | visiteur |
1082 | shepard |
Voici les fonctions de groupage que l’on rencontre sur tous les SGBDR :
nom | description | exemple |
---|---|---|
COUNT(*) | Compte le nombre de lignes dans le groupe | SELECT nom, COUNT(*) FROM test GROUP BY nom;
|
COUNT | Compte le nombre de valeurs dans une colonne d'un groupe | SELECT nom, COUNT(points) FROM test GROUP BY nom;
|
SUM | Somme toutes les valeurs d'une colonne d'un groupe | SELECT nom, SUM(points) FROM test GROUP BY nom;
|
AVG | Calcule la moyenne de toutes les valeurs d'une colonne d'un groupe | SELECT nom, AVG(points) FROM test GROUP BY nom;
|
MIN | Renvoie la valeur minimale d'une colonne d'un groupe | SELECT nom, MIN(quand) FROM test GROUP BY nom;
|
MAX | Renvoie la valeur maximale d'une colonne d'un groupe | SELECT nom, MAX(quand) FROM test GROUP BY nom;
|
Les valeurs NULL
modifierLe langage S.Q.L. accepte l'absence de valeur (ou NULL). Cette valeur n'est pas égale à 0. Dans toutes les fonctions statistiques agissant sur une colonne vues ci-dessus (sauf COUNT(*)), les valeurs NULL ne sont pas prises en compte dans le calcul. C’est la différence entre COUNT(*) et COUNT(points) : si une valeur de la colonne points est NULL, alors le résultat de COUNT(*) et de COUNT(points) ne sera pas le même.
D'autres fonctions statistiques
modifierEn plus de ces 5 fonctions statistiques, les SGBDR implémentent souvent d'autres fonctions. La variance d'une population ou d'un échantillon (VAR_POP et VAR_SAMP) ainsi que leurs écarts-types (STDDEV_POP et STDDEV_SAMP) sont compris dans le standard mais pas implémentés dans tous les SGBDR.
Par ailleurs, certains SGBDR tels que PostGreSQL permettent de créer vos propres fonctions statistiques.
HAVING
modifierHAVING vous permet de filtrer les données.
Vous vous demandez sans doute à quoi il sert puisque WHERE le fait déjà.
En effet, WHERE le fait, mais avant de traiter les données, c'est-à-dire avant le traitement des éventuelles fonctions dans la requête, et donc des fonctions statistiques que nous venons de voir.
HAVING, par contre, s'effectue après. C’est pourquoi il permet, contrairement à WHERE, de filtrer les données calculées.
La requête suivante vous renverra une erreur, en général « la colonne score n'existe pas » :
SELECT points AS score FROM test WHERE score > 100;
Une façon de contourner ce problème est de traiter cette requête comme un tableau et d'y effectuer une nouvelle requête (mais cette pratique alourdit la charge du serveur) en mettant la première requête dans le FROM de la principale :
SELECT score FROM (SELECT points AS score FROM test) s WHERE score > 100;
Le "s" après le FROM est un alias de table, il est obligatoire dans le cas d'une sous-requête (c'est la requête entre parenthèses).
La requête suivante a l'effet escompté, en plus d’être bien plus rapide que celle juste au-dessus :
SELECT points AS score FROM test GROUP BY score HAVING points > 100;
Mysql pourra également accepter la requête :
SELECT points AS score FROM test HAVING score > 100;
Cependant d’un point de vu de la norme SQL cette expression est invalide car l’allias score ne devrait pas être visible dans le bloque de code HAVING. Les systèmes respectant cette spécification comme PostgreSQL retournerons donc une erreur[1].
Dans les deux autres cas pour tous les SGBD SQL le résultat sera :
score |
---|
384 |
194 |
156 |
958 |
1084 |
453 |
125 |
856 |
L'intérêt de HAVING se porte évidemment sur les colonnes aliasées dont le contenu a été calculé, par exemple les colonnes résultantes de fonctions statistiques :
SELECT SUM(points) AS total FROM test GROUP BY nom HAVING SUM(points) > 1000;
total |
---|
1082 |
2898 |
ORDER BY
modifierORDER BY permet, vous vous en doutez, d'ordonner le résultat de la requête. Il suffit de lui donner en argument la colonne selon laquelle il doit ordonner. Par exemple :
SELECT * FROM test ORDER BY nom;
id | nom | points | quand |
---|---|---|---|
1 | shepard | 384 | 2007-01-01 00:00:00 |
2 | shepard | 194 | 2007-01-03 12:51:25 |
4 | shepard | 16 | 2007-01-03 13:04:18 |
7 | shepard | 453 | 2007-01-10 15:32:06 |
8 | shepard | 35 | 2007-01-10 15:43:15 |
5 | somebody | 958 | 2007-01-05 23:16:08 |
6 | somebody | 1084 | 2007-01-06 02:15:59 |
10 | somebody | 856 | 2007-01-11 22:19:23 |
3 | visiteur | 156 | 2007-01-03 12:54:19 |
9 | visiteur | 125 | 2007-01-11 12:13:15 |
Vous constatez qu'en effet, les colonnes sont ordonnées par ordre alphabétique sur le nom, mais vous pouvez également spécifier plusieurs colonnes pour l'ordonnancement du résultat. Par exemple par nom puis par points :
SELECT * FROM test ORDER BY nom, points;
id | nom | points | quand |
---|---|---|---|
4 | shepard | 16 | 2007-01-03 13:04:18 |
8 | shepard | 35 | 2007-01-10 15:43:15 |
2 | shepard | 194 | 2007-01-03 12:51:25 |
1 | shepard | 384 | 2007-01-01 00:00:00 |
7 | shepard | 453 | 2007-01-10 15:32:06 |
10 | somebody | 856 | 2007-01-11 22:19:23 |
5 | somebody | 958 | 2007-01-05 23:16:08 |
6 | somebody | 1084 | 2007-01-06 02:15:59 |
9 | visiteur | 125 | 2007-01-11 12:13:15 |
3 | visiteur | 156 | 2007-01-03 12:54:19 |
Si vous voulez trier une colonne par ordre inverse, il vous suffit d'ajouter le mot-clef DESC après le nom de la colonne concernée. Par exemple la même requête qu'au-dessus avec les points triés par ordre inverse :
SELECT * FROM test ORDER BY nom, points DESC;
id | nom | points | quand |
---|---|---|---|
7 | shepard | 453 | 2007-01-10 15:32:06 |
1 | shepard | 384 | 2007-01-01 00:00:00 |
2 | shepard | 194 | 2007-01-03 12:51:25 |
8 | shepard | 35 | 2007-01-10 15:43:15 |
4 | shepard | 16 | 2007-01-03 13:04:18 |
6 | somebody | 1084 | 2007-01-06 02:15:59 |
5 | somebody | 958 | 2007-01-05 23:16:08 |
10 | somebody | 856 | 2007-01-11 22:19:23 |
3 | visiteur | 156 | 2007-01-03 12:54:19 |
9 | visiteur | 125 | 2007-01-11 12:13:15 |
ORDER BY permet de trier selon les colonnes récupérées uniquement, ainsi la requête suivante n’est pas conforme étant donné que la colonne "quand" ne fait pas partie des colonnes rapatriées :
SELECT id, nom FROM test ORDER BY quand;
Trier des groupes de lignes
modifierSi on cherche à classer des lignes groupées, par exemple par nom selon leurs dates d'apparition, pour avoir :
id | nom | points | quand |
---|---|---|---|
1 | shepard | 384 | 2007-01-01 00:00:00 |
2 | shepard | 194 | 2007-01-03 12:51:25 |
4 | shepard | 16 | 2007-01-03 13:04:18 |
7 | shepard | 453 | 2007-01-10 15:32:06 |
8 | shepard | 35 | 2007-01-10 15:43:15 |
3 | visiteur | 156 | 2007-01-03 12:54:19 |
9 | visiteur | 125 | 2007-01-11 12:13:15 |
5 | somebody | 958 | 2007-01-05 23:16:08 |
6 | somebody | 1084 | 2007-01-06 02:15:59 |
10 | somebody | 856 | 2007-01-11 22:19:23 |
La commande consiste à joindre le résultat final à une sélection triée des noms de groupe :
select * from
(SELECT Groupe, Clé
FROM Table1)
inner join
(SELECT *
FROM Table2)
on Table1.Clé = Table2.Clé
order by Table1.Groupe
Une clause à part : LIMIT ou TOP
modifierUne clause S.Q.L. implémentée par la plupart des SGBDR permet de limiter le nombre de lignes retournées. Pour certains (PostGreSQL, MySQL, SQLite par exemple), c’est LIMIT à la fin de l’ordre SELECT, pour d'autres (Microsoft Access, Microsoft S.Q.L. Server), c’est TOP au début de ce même ordre.
Ces clauses ne font absolument pas partie du standard S.Q.L., en effet le S.Q.L. suit une logique ensembliste, et limiter un ensemble est contraire à cette logique. N'hésitez cependant pas à les utiliser car dans beaucoup de cas ces clauses sont très pratiques.
Un exemple avec LIMIT
modifierSELECT * FROM test LIMIT 5;
id | nom | points | quand |
---|---|---|---|
1 | shepard | 384 | 2007-01-01 00:00:00 |
2 | shepard | 194 | 2007-01-03 12:51:25 |
3 | visiteur | 156 | 2007-01-03 12:54:19 |
4 | shepard | 16 | 2007-01-03 13:04:18 |
5 | somebody | 958 | 2007-01-05 23:16:08 |
Les 5 premières lignes insérées sont renvoyées. Suite à certaines requêtes le SGBDR pourrait tout aussi bien vous renvoyer les lignes dans un ordre totalement aléatoire, S.Q.L. n'admet aucun ordre par défaut, si vous voulez être sur d'obtenir le même résultat à chaque requête sur les mêmes données, vous devez utiliser ORDER BY.
Les SGBDR qui proposent la clause LIMIT proposent généralement un complément nommé OFFSET qui permet de dire de prendre n lignes à partir de la ligne k :
SELECT * FROM test ORDER BY id LIMIT 3 OFFSET 2;
id | nom | points | quand |
---|---|---|---|
3 | visiteur | 156 | 2007-01-03 12:54:19 |
4 | shepard | 16 | 2007-01-03 13:04:18 |
5 | somebody | 958 | 2007-01-05 23:16:08 |
Le même exemple avec TOP
modifierSELECT TOP 5 * FROM test;
id | nom | points | quand |
---|---|---|---|
1 | shepard | 384 | 2007-01-01 00:00:00 |
2 | shepard | 194 | 2007-01-03 12:51:25 |
3 | visiteur | 156 | 2007-01-03 12:54:19 |
4 | shepard | 16 | 2007-01-03 13:04:18 |
5 | somebody | 958 | 2007-01-05 23:16:08 |
Il n'existe pas d'équivalent à OFFSET pour Access et S.Q.L. Server. Il y a toutefois moyen de contourner ce problème de la manière suivante :
SELECT *
FROM ( SELECT TOP 3 *
FROM ( SELECT TOP 5 *
FROM test
ORDER BY id )
ORDER BY id DESC )
ORDER BY id;
id | nom | points | quand |
---|---|---|---|
3 | visiteur | 156 | 2007-01-03 12:54:19 |
4 | shepard | 16 | 2007-01-03 13:04:18 |
5 | somebody | 958 | 2007-01-05 23:16:08 |
Attention toutefois à l’utilisation d'ORDER BY dans MS-SQL :
La clause ORDER BY n’est pas valide dans les vues, les fonctions inline, les tables dérivées, les sous-requêtes et les expressions de table communes, sauf si TOP ou FOR XML est également spécifié.
Application de l’algèbre relationnelle
modifierSélection
modifierÀ partir d'un ensemble A, obtenir un ensemble B dont les valeurs des n-uplets correspondent à des critères donnés.
SELECT *
FROM A
WHERE nuplet = 'critère'
Projection
modifierÀ partir d'un ensemble A, obtenir un ensemble B dont les k-uplets contiennent certaines composantes des n-uplets de A.
SELECT cellule
FROM A
WHERE nuplet = 'critère'
Intersection
modifierÀ partir des ensembles A et B, obtenir un ensemble C qui contient les n-uplets présents à la fois dans A et dans B.
SELECT cellule FROM A
INTERSECT
SELECT cellule FROM B
Union
modifierÀ partir des ensembles A et B, obtenir un ensemble C qui contient les n-uplets présents dans A ou dans B.
SELECT cellule FROM A
UNION ALL
SELECT cellule FROM B
Sans les doublons :
SELECT cellule FROM A
UNION
SELECT cellule FROM B
Différence
modifierÀ partir des ensembles A et B, obtenir un ensemble C qui contient les n-uplets présents dans A mais pas dans B.
SELECT cellule
FROM A
WHERE A.cellule NOT IN (SELECT cellule FROM B);
En S.Q.L. 2 :
SELECT cellule
FROM A
EXCEPT
SELECT cellule
FROM B;
Produit cartésien
modifierÀ partir des ensembles A et B, obtenir un ensemble C qui contient des n-uplets qui sont toutes les combinaisons possibles des j-uplets de A avec les k-uplets de B.
SELECT cellule
FROM A, B
Jointure
modifierÀ partir des ensembles A et B, obtenir un ensemble C qui est un sous-ensemble du produit cartésien de A et B dans lesquels la valeur de la composante M de A est identique à la valeur de la composante N de B.
SELECT A.cellule
FROM A LEFT JOIN B ON A.cellule = B.cellule
WHERE B.cellule IS NULL;
Une jointure à gauche (LEFT JOIN) exclut les données non jointes de la table de droite et vice-versa pour la jointure à droite (RIGHT JOIN).
Une jointure INNER JOIN ne regroupe que les données joignables des deux tables.
Division cartésienne
modifierÀ partir des ensembles A et B, obtenir un ensemble C, où la combinaison de chaque n-uplet de B avec chaque n-uplet de C (produit cartésien de B et C) existe dans A.
Concrètement cet opérateur permet de déterminer des objets remplissant toutes les conditions (ex : clients ayant acheté tous les produits).
En SQL, cela se traduit par une double négation : C contient les n-uplets de A qui n'en contenaient pas de B qui n'en contenaient pas de A.
SELECT DISTINCT A.id
FROM A
WHERE not exists
(select id
from B
where not exists
(select *
from A
inner join B on and B.id = A.id2
)
);
Références
modifier- http://sql.1keydata.com/fr/sql-avance.php
- http://cerig.efpg.inpg.fr/tutoriel/bases-de-donnees/chap21.htm
Intégrité référentielle
Principe
modifierDans une base de données, certaines valeurs doivent respecter des contraintes d'intégrité pour conserver la cohérence entre les tables en cas de modification.
Par exemple si au moins un client fait partie d'une société il n’est pas normal de pouvoir supprimer celle-ci. De même, si une société n’est pas encore dans la base, on ne devrait pas pouvoir ajouter un client dedans.
FOREIGN KEY
modifierREFERENCES
modifierContraintes d'intégrité
modifierUne contrainte d'intégrité impose une restriction sur les valeurs possibles d'un champ.
CONSTRAINT
modifierDEFAULT
modifierNOT NULL
modifierUNIQUE
modifierCHECK
modifier
Vues
En attendant qu'un contributeur rédige ce chapitre, vous pouvez suivre les indications du cadre ci-dessous.
Veuillez consulter l'entrée Programmation SQL/Langage de définition de données#VIEW de Wikilivres, le projet de rédaction de manuels pratiques libres évoluant de concert avec Wikiversité.
Wikiversité ne dispose d'aucune leçon concernant Programmation SQL/Langage de définition de données#VIEW. Mais si vous pensez pouvoir en écrire une au sujet de Programmation SQL/Langage de définition de données#VIEW, n'hésitez pas à la réaliser !
Modification des données
La mise à jour d'enregistrements via la commande UPDATE
modifierCette commande permet une modification de tous les enregistrements d'une table
UPDATE `enquete_palu`
SET `nom` = 'UPPER(nom)';
Cette commande permet une modification des enregistrements d'une table satisfaisant à la restriction : cas_palu_adulte commence par 0
UPDATE `enquete_palu`
SET `nom` = 'UPPER(nom)'
WHERE (
`cas_palu_adulte` LIKE '%0%'
AND `nom` LIKE '%échantillon_test%'
);
Fonctionne aussi avec une jointure sur une autre table :
UPDATE `enquete_palu`, `clients`
SET `enquete_nom` = 'UPPER(enquete_nom)'
WHERE (
`pays_client` = 'Allemagne'
);
La suppression d'enregistrements via la commande DELETE
modifierPour supprimer tous les enregistrements d'une table
DELETE FROM 'enquete_palu';
Pour supprimer les enregistrements d'une table satisfaisant à la restriction : nom contient TOTO
DELETE FROM 'enquete_palu'
WHERE (
`nom` LIKE '%TOTO%'
);
Fonctionne aussi avec un paramètre (%échantillon_test%) :
DELETE FROM 'enquete_palu'
WHERE (
`cas_palu_enfant` LIKE '%0%'
AND `nom` LIKE '%échantillon_test%'
);
GFDL | Vous avez la permission de copier, distribuer et/ou modifier ce document selon les termes de la licence de documentation libre GNU, version 1.2 ou plus récente publiée par la Free Software Foundation ; sans sections inaltérables, sans texte de première page de couverture et sans texte de dernière page de couverture. |