Utilisateur:Regards sur sciences/agreg/leçons/23. Requêtes en langage SQL.
SQL (sigle de Structured Query Language, en français langage de requête structurée) est un langage informatique normalisé servant à exploiter des bases de données relationnelles. La partie langage de manipulation des données de SQL permet de rechercher, d'ajouter, de modifier ou de supprimer des données dans les bases de données relationnelles.
Outre le langage de manipulation des données :
- le langage de définition des données permet de créer et de modifier l'organisation des données dans la base de données,
- le langage de contrôle de transaction permet de commencer et de terminer des transactions,
- le langage de contrôle des données permet d'autoriser ou d'interdire l'accès à certaines données à certaines personnes.
Les instructions SQL s'écrivent d'une manière qui ressemble à celle de phrases ordinaires en anglais. Cette ressemblance voulue vise à faciliter l'apprentissage et la lecture5.
C'est un langage déclaratif c'est-à-dire qu'il permet de décrire le résultat escompté, sans décrire la manière de l'obtenir. Les SGBD sont équipés d'optimiseurs de requêtes - des mécanismes qui déterminent automatiquement la manière optimale d'effectuer les opérations, notamment par une estimation de la complexité algorithmique. Celle-ci est fondée sur des statistiques récoltées à partir des données contenues dans la base de données (nombre d'enregistrements, nombre de valeurs distinctes dans une colonne, etc.).
Les instructions SQL couvrent 4 domaines :
- Langage de manipulation de données,
- Langage de définition de données,
- Langage de contrôle de données,
- Langage de contrôle des transactions.
langage de manipulation de données
[modifier | modifier le wikicode]Les instructions de manipulation du contenu de la base de données commencent par les mots clés SELECT, UPDATE, INSERT ou DELETE qui correspondent respectivement aux opérations de recherche de contenu, modification, ajout et suppression.
Divers autres mots-clés tels que FROM, JOIN et GROUP BY permettent d'indiquer les opérations d'algèbre relationnelle à effectuer en vue d'obtenir le contenu à manipuler.
SELECT
est une commande SQL qui permet d'extraire des données des tables d'une base de données relationnelle. Une commande
SELECT
peut obtenir zéro ou plusieurs tuples provenant de tables et de vues. Dû à la nature déclarative du langage SQL, une commande
SELECT
décrit un jeu de résultat voulus, et non la manière de les obtenir. La base de données transforme donc la requête en un plan d’exécution de requête, qui peut varier dans le temps, en fonction de la version du serveur, ou du serveur utilisé.
Forme basique
[modifier | modifier le wikicode]La commande
SELECT
a, classiquement, la syntaxe suivante :
SELECT expr [, expr ...]
FROM tables
WHERE conditions
GROUP BY expr
HAVING conditions
ORDER BY expr
LIMIT
Chaque implémentation de base de données relationnelle ajoute des fonctionnalités et variations spécifiques, cette syntaxe est donc particulièrement incomplète. Il est nécessaire de se référer à la documentation de l'implémentation utilisée afin d'obtenir une syntaxe détaillée.
Exemples de requête utilisant la table
my_table
contenant :
col1 | col2 | col3 |
---|---|---|
foo | bar | 42 |
foo | baz | 84 |
Requête | Résultat | Explication | |||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
SELECT * FROM my_table
|
|
Le joker '*' représente toutes les colonnes. La requête n'ayant pas de condition, toutes les lignes sont récupérées. | |||||||||
SELECT col2 FROM my_table
|
|
Tous les tuples de la colonne sont récupérés.
| |||||||||
SELECT col1, col2 FROM my_table WHERE col3 > 50
|
|
Cette requête utilise une condition, , seul le tuple (foo, baz, 84) est donc récupérés. Aussi, elle ne sélectionne que col1 et col2, ainsi col3 n'est pas affiché dans le résultat.
| |||||||||
SELECT SUM(col3) * 3 FROM my_table
|
|
Il est possible d'appliquer des opérations et des fonctions sur les colonnes sélectionnées. |
SELECT
[modifier | modifier le wikicode]La première partie de la syntaxe d'une requête
SELECT
permet de définir quelles colonnes doivent être extraites des tables, et éventuellement comment les valeurs de ces colonnes doivent être agrégées. Il est possible d'appliquer des opérateurs mathématiques à cette sélection, ou encore des fonctions proposées par le moteur de base de données tel que, par exemple, SUM, ou AVG. Enfin, lors d'un select, il est possible d'utiliser le joker '*' permettant de sélectionner toutes les colonnes d'une table. Lors d'une requête effectuée sur plusieurs table, il est possible de préciser sur quelles tables le joker s'applique, en préfixant le joker d'un nom de la table, par exemple :
SELECT T1.*, T2.foo
.
Requête | Résultat | Explication | ||||||
---|---|---|---|---|---|---|---|---|
SELECT 1
|
|
Cette requête n'utilise aucune table, mais directement une valeur, le résultat est donc composé d'un tableau d'une seule colonne nommée '1' : c'est la première ligne, et ayant une seule valeur, 1 : la seconde ligne. | ||||||
SELECT 1 AS "Colonne1"
|
|
Cette fois la colonne a reçu un alias, "Colonne1", le tableau de résultat est donc plus explicite. | ||||||
SELECT 1 * 2, 3 + 4, CONCAT("Hello ", "World")
|
|
Il est possible d'appliquer des opérations et des fonctions lors d'une requête .
|
FROM
[modifier | modifier le wikicode]Le mot-clé
FROM
permet de définir quelles tables, vues, ou sous requêtes serviront de sources de données. C'est aussi dans la clause
FROM
qu'il est possible de définir des jointures entre tables.
Exemples :
SELECT * FROM table1, table2
Cette requête renvoie toutes les combinaisons possibles des tuples des tables table1 et table2, c'est-à-dire que si table1 contient 1000 tuples et que table2 en contient 2000, le résultat sera de 2 000 000 tuples. Il est donc plus commun de joindre les tables grâce au mot-clé JOIN, permettant d'appliquer des conditions à la jointure entre les deux tables.
SELECT table1.a, table2.b FROM table1 JOIN table2 ON table1.a = table2.a
Avec cette requête, seuls les tuples respectant la condition table1.a = table2.a seront sélectionnés.
WHERE
[modifier | modifier le wikicode]Le mot-clé
WHERE
permet de définir les conditions de sélection des tuples. Différentes conditions peuvent être exprimées, et une équation peut ainsi être créée en reliant les différentes conditions par les mots clefs AND ou OR. Des parenthèses sont utilisables afin de déterminer l'ordre d'application des différentes conditions.
Exemple :
SELECT field1 FROM table1 WHERE table1.field2 > 42 AND table1.field3 = "Gonzo"
GROUP BY
[modifier | modifier le wikicode]Le mot-clé
GROUP BY
permet de définir, via des noms de colonnes ou des expressions, comment les résultats doivent être groupés. La présence d'un GROUP BY dans une requête implique généralement la présence de fonctions d’agrégation, tel que
SUM(expr)
,
AGV(expr)
ou
COUNT(expr)
dans le
SELECT
.
Exemple en utilisant la table my_table contenant :
col1 | col2 | col3 |
---|---|---|
foo | bar | 1 |
foo | baz | 2 |
bar | bar | 4 |
bar | baz | 8 |
Requête | Résultat | Explication | |||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
SELECT col2, COUNT(1), AVG(col3) FROM my_table GROUP BY col2
|
|
Ici deux groupes ont été créés, le groupe pour la valeur bar et le groupe pour la valeur baz. | |||||||||
SELECT COUNT(1), AVG(col3) FROM my_table GROUP BY col3 < 3;
|
|
Ici une expression a été utilisée pour grouper les tuples, deux groupes sont donc créés, l'un pour le cas où l'expression est vraie, l'autre pour le cas où l'expression est fausse. |
HAVING
[modifier | modifier le wikicode]Le mot-clé
HAVING
permet de définir des conditions sur les résultats, agrégés, d'un
GROUP BY
. Il se différencie donc du
WHERE
par le fait que le
WHERE
est utilisé à la sélection des tuples avant le
GROUP BY
et que le
HAVING
est utilisé pour la sélection des tuples après agrégation par le
GROUP BY
.
Exemple en utilisant la table my_table contenant :
col1 | col2 | col3 |
---|---|---|
foo | bar | 1 |
foo | baz | 2 |
bar | bar | 4 |
bar | baz | 8 |
bar | foo | 16 |
Requête | Résultat | Explication | |||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
SELECT col2, COUNT(1), AVG(col3) FROM my_table GROUP BY col2 HAVING COUNT(1) > 1
|
|
Ici trois groupes ont été créés, le groupe pour la valeur et le groupe pour la valeur , et le groupe pour la valeur , mais le n'a sélectionné que les deux premiers, car le groupe ne contient qu'un seul tuple.
| |||||||||
SELECT col2, COUNT(1) AS count, SUM(col3) AS sum FROM my_table GROUP BY col2 HAVING sum = 10
|
|
Ici aussi trois groupes ont été créés, mais un seul a été conservé par la clause HAVING. |
ORDER BY
[modifier | modifier le wikicode]Le mot-clé
ORDER BY
permet de trier les résultats. Le tri est par défaut croissant, mais peut être décroissant en utilisant le mot-clé DESC. Il est possible de tirer sur plusieurs champs, en cas d'égalité sur le premier champ, le second est utilisé et ainsi de suite.
Exemple :
SELECT * FROM my_table ORDER BY field1 ASC, field2 DESC;
Limiter le nombre de résultats
[modifier | modifier le wikicode]La manière de limiter le nombre de résultats d'une requête est dépendante du moteur utilisé, cependant la norme SQL:2008 définit la syntaxe suivante :
SELECT * FROM table1 FETCH FIRST 10 ROWS ONLY
langage de définition de données
[modifier | modifier le wikicode]Les instructions de manipulation des métadonnées - description de la structure, l'organisation et les caractéristiques de la base de données - commencent avec les mots-clés CREATE, ALTER, DROP, RENAME, COMMENT ou TRUNCATE qui correspondent aux opérations d'ajouter, modifier, supprimer, renommer, commenter ou vider une métadonnée. Ces mots clés sont immédiatement suivis du type de métadonnée à manipuler - TABLE, VIEW, INDEX...
INSERT
insère des n-uplets (informellement appelés lignes et appelés tuples en anglais) dans une table existante, exemple :
INSERT INTO a_table (field1, field2, field3)
VALUES ('test', 'N', NULL);
UPDATE
Modifie un ensemble de n-uplets existant dans une table, exemple :
UPDATE a_table
SET field1 = 'updated value'
WHERE field2 = 'N';
DELETE
Supprime un ensemble de n-uplets existant dans une table, exemple :
DELETE FROM a_table
WHERE field2 = 'N';
MERGE
Combine les données de plusieurs tables. C'est la combinaison deINSERT
etUPDATE
. Il peut être nomméUPSERT
,INSERT OR REPLACE INTO
, ou encoreINSERT ON DUPLICATE KEY UPDATE
dans certains moteurs de base de données.
MERGE INTO table_name USING table_reference ON (condition)
WHEN MATCHED THEN
UPDATE SET column1 = value1 [, column2 = value2 ...]
WHEN NOT MATCHED THEN
INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...
langage de contrôle des transactions
[modifier | modifier le wikicode]Tandis que les mots clés COMMIT et ROLLBACK permettent de confirmer ou annuler l'exécution de transactions.
langage de contrôle de données
[modifier | modifier le wikicode]Les mots clés GRANT et REVOKE permettent d'autoriser des opérations à certaines personnes, d'ajouter ou de supprimer des autorisations.