Interrogation d'une base de données¶
Requête sur une seule table¶
Les requêtes d'extraction de données, c'est-à-dire les requêtes interrogeant la base afin de ramener des informations à l'utilisateur, s'effectuent à l'aide de l'ordre ''SELECT''.
Un ordre ''SELECT'' peut donner:
-
un résultat vide
-
un résultat ''NULL'', c'est-à-dire sans données "consistantes";
-
une seule donnée
-
une table d'une seule ligne
-
une table de plusieurs lignes
Dans son principe, la commande ''SELECT'' ramène toujours une table non persistente (elle reste simplement en mémoire).
Il est possible d'extraire des données en provenance d'une ou plusieurs tables mises en relation les unes avec les autres.
Pour l'instant nous nous intéressons aux requêtes qui s'effectue sur une seule table.
Eléments de base de la syntaxe SQL¶
Quelques éléments de vocabulaire:
-
Clause. Une clause est une partie optionnelle ou obligatoire d'un ordre SQL
-
Prédicat. Un prédicat est une expression logique pouvant prendre trois valeurs: ''Vrai'', ''Faux'' ou ''NULL''.
Quelques éléments nécessaires à l'écriture des requêtes:
-
Casse. L'écriture des ordres de requête SQL n'est pas sensible à la casse des caractères. On peut donc utiliser des majuscules ou des minuscules à loisir. En revanche, l'écriture des valeurs de données est sensible à la casse.
-
Présentation. SQL est insensible à la césure comme à l'indentation.
-
Commentaires. Pour insérer des commentaires dans une requête, il suffit soit de commencer la ligne par le double caractère ''%%--%%'' lorsque le commentaire tient sur une seule ligne, soit, pour un commentaire multiligne, de commencer le bloc par le couple ''/'' et le terminer par le couple ''/''.
<code mysql| Commentaires en SQL>-- un commentaire sur une seule ligne
/* un
commentaire
sur
plusieurs
lignes */
</code>
-
Chaîne de caractère. Toute chaîne de caractères doit être entourée d'apostrophes '' ' ''...'' ' ''. Pour représenter une apostrophe dans une chaîne, il faut la déboubler:
<code mysql|Chaînes de caractères en SQL>'chaine de caractères en MySQL' 'Aujourd''hui'</code> -
Mots réservés. SQL utilise un grand nombre de mots réservés qui servent d'indicateur au compilateur pour distinguer une donnée, un opérateur, une clause ou un ordre.
-
Nom. Les noms des objets (table, colonne, ...) doivent commencer par une lettre, ne contenir que des caractères de A à Z, les chiffres de 0 à 9 et le blanc souligné (''"_"'' ou underscore).
-
Liste. La virgule est utilisée comme séparateur pour les éléments des listes de valeurs.
-
Chiffre. Il ne doit pas y avoir d'espace entre les chiffres composant un nombre.
-
Séparateur décimal. Le séparateur décimal est le point et non la virgule.
-
Exposant. Pour préciser une puissance de dix dans un nombre réel, il faut utiliser la notation ''E''.
<code mysql|Les décimaux en SQL>0.314E1</code>
L'ordre ''SELECT''¶
L'ordre ''SELECT'' est la commande de base du SQL. Elle sert à extraire des données d'une base ou à calculer de nouvelles données à partir de données existantes.
<code mysql | Syntaxe générale d'une commande ''SELECT''>
SELECT [DISTINCT | ALL] {* | liste_de_colonnes }
FROM nom_de_table
[WHERE predicat]
[GROUP BY liste_des_colonnes_du_groupage]
[HAVING predicat]
[ORDER BY liste_des_colonnes_de_tri]
</code>
La clause ''SELECT'' est obligatoire et recense les colonnes que l'on veut voir figurer dans le résultat de la requête.
La clause ''FROM'' est obligatoire et permet de préciser la table sur laquelle s'applique l'ordre ''SELECT''.
La clause ''WHERE'' est optionnelle et permet de préciser des conditions sur les données. Elle agit comme un filtre. C'est une expression logique (prédicat) qui ne peut aboutir qu'à un calcul booléen et donc un résultat ''VRAI'', ''FAUX'' ou ''NULL'' (absence d'information).
Les clauses optionnelles ''GROUP BY'' et ''HAVING'' s'utilisent en général conjointement et permettent d'ajouter des conditions, non plus sur les données présentes dans les tables en jeu, mais par rapport à des résultats calculés.
La clause ''ORDER BY'', optionnelle, permet de trier le résultat de la requête dans l'ordre des colonnes précisées.
<code mysql|Sélection des noms et prénoms des joueurs de l'OL>
mysql> select Nom, prenom from joueurs;
+------------+-------------------------+
| Nom | prenom |
| --- | ------ |
+------------+-------------------------+
| Coupet | Gregory |
| ------ | ------- |
| Vercoutre | Remy |
| Hartock | Joan |
| Abidal | Eric |
| Berthod | Jeremy |
| Squillaci | Sebastien |
| Muller | Patrick |
| Cris | Cristiano Marques |
| Benhamida | Mourad |
| Clerc | Francois |
| Reveillere | Anthony |
| Cacapa | Claudio Roberto |
| Malouda | Florent |
| Diarra | Alou |
| Toulalan | Jeremy |
| Idangar | Sylvain |
| Hima | Yacine |
| Kallstrom | Kim |
| Juninho | Pernambucano |
| Mendes | Tiago |
| Govou | Sidney |
| Bettiol | Gregory |
| Fred | Frederico Chaves Guedes |
| Benzema | Karim |
| Ben Arfa | Hatem |
| Wiltord | Sylvain |
| Carew | John |
+------------+-------------------------+
27 rows in set (0.00 sec)
</code>
8-) Remarquez la non sensibilité à la casse des champs (colonnes) de la table.
8-) La liste des champs à sélectionner est composé du nom des champs séparés par des virgules.
L'opérateur *¶
Le caractère * (étoile) placé dans la clause ''SELECT'' de l'odre ''SELECT'' récupère toutes les colonnes de la table précisée dans la clause ''FROM'' de la requête.
<code mysql|Utilisation de l'opérateur *>
mysql> select * from joueurs;
+--------+-------------------------+------------+-------------------+-------------------+-------------+
| Numero | Prenom | Nom | Poste | Date_de_naissance | Nationalite |
| ------ | ------ | --- | ----- | ----------------- | ----------- |
+--------+-------------------------+------------+-------------------+-------------------+-------------+
| 1 | Gregory | Coupet | Gardien | 1972-12-31 | Fra |
| - | ------- | ------ | ------- | ---------- | --- |
| 30 | Remy | Vercoutre | Gardien | 1980-06-26 | Fra |
| 25 | Joan | Hartock | Gardien | 1987-02-17 | Fra |
| 20 | Eric | Abidal | Arriere gauche | 1979-07-11 | Fra |
| 23 | Jeremy | Berthod | Arriere gauche | 1984-04-24 | Fra |
| 29 | Sebastien | Squillaci | Defenseur central | 1980-08-11 | Fra |
| 4 | Patrick | Muller | Defenseur central | 1976-12-17 | Sui |
| 3 | Cristiano Marques | Cris | Defenseur central | 1977-06-03 | Bre |
| 38 | Mourad | Benhamida | Arriere droit | 1986-01-18 | Fra |
| 2 | Francois | Clerc | Arriere droit | 1983-04-18 | Fra |
| 12 | Anthony | Reveillere | Arriere droit | 1979-11-10 | Fra |
| 5 | Claudio Roberto | Cacapa | Defenseur central | 1976-05-29 | Bre |
| 10 | Florent | Malouda | Ailier gauche | 1980-06-13 | Fra |
| 15 | Alou | Diarra | Milieu defensif | 1981-07-15 | Fra |
| 28 | Jeremy | Toulalan | Milieu defensif | 1983-09-10 | Fra |
| 36 | Sylvain | Idangar | Milieu offensif | 1984-03-08 | Fra |
| 31 | Yacine | Hima | Milieu defensif | 1984-03-25 | Fra |
| 6 | Kim | Kallstrom | Milieu offensif | 1980-08-24 | Sue |
| 8 | Pernambucano | Juninho | Milieu offensif | 1975-01-30 | Bre |
| 21 | Tiago | Mendes | Milieu defensif | 1981-05-02 | Por |
| 14 | Sidney | Govou | Ailier droit | 1979-07-27 | Fra |
| 39 | Gregory | Bettiol | Avant centre | 1986-03-30 | Fra |
| 11 | Frederico Chaves Guedes | Fred | Avant centre | 1983-10-03 | Bre |
| 19 | Karim | Benzema | Avant centre | 1987-12-19 | Fra |
| 18 | Hatem | Ben Arfa | Ailier gauche | 1987-03-07 | Fra |
| 22 | Sylvain | Wiltord | Avant centre | 1974-05-10 | Fra |
| 9 | John | Carew | Avant centre | 1979-09-05 | Nor |
+--------+-------------------------+------------+-------------------+-------------------+-------------+
27 rows in set (0.00 sec)
</code>
:!: L'utilisation systématique de l'opérateur * dans la clause ''SELECT'' n'est pas conseillée. En effet, cet opérateur oblige le SGBDR à retrouver par lui-même le nom explicite des colonnes et se révèle donc un peu plus coûteux en ressources que la spécification directe du nom des colonnes.
L'opérateur ''DISTINCT'' (ou ''ALL'')¶
Lorsque le moteur du SGBDR construit une réponse à une requête, il rapatrie toutes les lignes correspondantes, généralement dans l'ordre où il les trouve, même si ces dernières sont en double (''ALL'' par défaut). C'est pourquoi il est parfois nécessaire d'utiliser le mot-clé ''DISTINCT'' qui élimine les doublons dans la réponse.
Exemple:
<code mysql|Recherche des postes des joueurs de l'OL (ALL par défaut)>
mysql> SELECT poste FROM joueurs;
+-------------------+
| poste |
| ----- |
+-------------------+
| Gardien |
| ------- |
| Gardien |
| Gardien |
| Arriere gauche |
| Arriere gauche |
| Defenseur central |
| Defenseur central |
| Defenseur central |
| Arriere droit |
| Arriere droit |
| Arriere droit |
| Defenseur central |
| Ailier gauche |
| Milieu defensif |
| Milieu defensif |
| Milieu offensif |
| Milieu defensif |
| Milieu offensif |
| Milieu offensif |
| Milieu defensif |
| Ailier droit |
| Avant centre |
| Avantcentre |
| Avant centre |
| Ailier gauche |
| Avant centre |
| Avant centre |
+-------------------+
27 rows in set (0.00 sec)
</code>
<code mysql|Recherche des postes des joueurs de l'OL avec le mot-clé DISTINCT>
mysql> SELECT DISTINCT poste FROM joueurs;
+-------------------+
| poste |
| ----- |
+-------------------+
| Gardien |
| ------- |
| Arriere gauche |
| Defenseur central |
| Arriere droit |
| Ailier gauche |
| Milieu defensif |
| Milieu offensif |
| Ailier droit |
| Avant centre |
+-------------------+
9 rows in set (0.00 sec)
</code>
L'opérateur ''AS''¶
L'opérateur ''AS'' permet de renommer des colonnes ou/et d'en rajouter.
<code mysql|Exemple d'utilisation de AS>
mysql> SELECT Nom, date_de_naissance AS 'Ne le',
-> 'Olympique Lyonnais' AS Equipe
-> FROM joueurs;
+------------+------------+--------------------+
| Nom | Ne le | Equipe |
| --- | ----- | ------ |
+------------+------------+--------------------+
| Coupet | 1972-12-31 | Olympique Lyonnais |
| ------ | ---------- | ------------------ |
| Vercoutre | 1980-06-26 | Olympique Lyonnais |
| Hartock | 1987-02-17 | Olympique Lyonnais |
| Abidal | 1979-07-11 | Olympique Lyonnais |
| Berthod | 1984-04-24 | Olympique Lyonnais |
| Squillaci | 1980-08-11 | Olympique Lyonnais |
| Muller | 1976-12-17 | Olympique Lyonnais |
| Cris | 1977-06-03 | Olympique Lyonnais |
| Benhamida | 1986-01-18 | Olympique Lyonnais |
| Clerc | 1983-04-18 | Olympique Lyonnais |
| Reveillere | 1979-11-10 | Olympique Lyonnais |
| Cacapa | 1976-05-29 | Olympique Lyonnais |
| Malouda | 1980-06-13 | Olympique Lyonnais |
| Diarra | 1981-07-15 | Olympique Lyonnais |
| Toulalan | 1983-09-10 | Olympique Lyonnais |
| Idangar | 1984-03-08 | Olympique Lyonnais |
| Hima | 1984-03-25 | Olympique Lyonnais |
| Kallstrom | 1980-08-24 | Olympique Lyonnais |
| Juninho | 1975-01-30 | Olympique Lyonnais |
| Mendes | 1981-05-02 | Olympique Lyonnais |
| Govou | 1979-07-27 | Olympique Lyonnais |
| Bettiol | 1986-03-30 | Olympique Lyonnais |
| Fred | 1983-10-03 | Olympique Lyonnais |
| Benzema | 1987-12-19 | Olympique Lyonnais |
| Ben Arfa | 1987-03-07 | Olympique Lyonnais |
| Wiltord | 1974-05-10 | Olympique Lyonnais |
| Carew | 1979-09-05 | Olympique Lyonnais |
+------------+------------+--------------------+
27 rows in set (0.00 sec)
</code>
La clause ''WHERE''¶
Le prédicat de la clause ''WHERE'' doit contenir n'importe quelle expression logique renvoyant une valeur logique. Seules les lignes de la table répondant ''Vrai'' à la condition seront retenues.
On peut y utiliser les opérateurs logiques classiques :
| Opérateurs | Symboles |
| ----------- | -------- |
| Comparaisons | %%= <> < <= > >=%% |
| Logiques | OR AND |
| Négation | NOT |
| Parenthèses | ( ) |
<code mysql|Recherche des joueurs dont le prénom est Gregory>
mysql> SELECT Prenom, Nom FROM joueurs WHERE prenom='Gregory';
+----------+----------+
| Prenom | Nom |
| ------ | --- |
+----------+----------+
| Gregory | Coupet |
| ------- | ------ |
| Gregory | Bettiol |
+----------+----------+
2 rows in set (0.00 sec)
</code>
<code mysql|Recherche de Gregory Coupet>
mysql> SELECT Prenom, Nom FROM joueurs WHERE Prenom='Gregory' AND Nom='Coupet';
+---------+--------+
| Prenom | Nom |
| ------ | --- |
+---------+--------+
| Gregory | Coupet |
| ------- | ------ |
+---------+--------+
1 row in set (0.02 sec)
</code>
<code mysql|Recherche des joueurs dont le numero est inférieur à 11>
mysql> SELECT * FROM joueurs WHERE Numero <= 11;
+--------+-------------------------+-----------+-------------------+-------------------+-------------+
| Numero | Prenom | Nom | Poste | Date_de_naissance | Nationalite |
| ------ | ------ | --- | ----- | ----------------- | ----------- |
+--------+-------------------------+-----------+-------------------+-------------------+-------------+
| 1 | Gregory | Coupet | Gardien | 1972-12-31 | Fra |
| - | ------- | ------ | ------- | ---------- | --- |
| 4 | Patrick | Muller | Defenseur central | 1976-12-17 | Sui |
| 3 | Cristiano Marques | Cris | Defenseur central | 1977-06-03 | Bre |
| 2 | Francois | Clerc | Arriere droit | 1983-04-18 | Fra |
| 5 | Claudio Roberto | Cacapa | Defenseur central | 1976-05-29 | Bre |
| 10 | Florent | Malouda | Ailier gauche | 1980-06-13 | Fra |
| 6 | Kim | Kallstrom | Milieu offensif | 1980-08-24 | Sue |
| 8 | Pernambucano | Juninho | Milieu offensif | 1975-01-30 | Bre |
| 11 | Frederico Chaves Guedes | Fred | Avant centre | 1983-10-03 | Bre |
| 9 | John | Carew | Avant centre | 1979-09-05 | Nor |
+--------+-------------------------+-----------+-------------------+-------------------+-------------+
10 rows in set (0.00 sec)
</code>
<code mysql|Recherche des joueurs dont le prénom commence par une lettre entre A et E>
mysql> SELECT *
-> FROM joueurs
-> WHERE (Prenom <= 'E') AND (Prenom >= 'A');
+--------+-------------------+------------+-------------------+-------------------+-------------+
| Numero | Prenom | Nom | Poste | Date_de_naissance | Nationalite |
| ------ | ------ | --- | ----- | ----------------- | ----------- |
+--------+-------------------+------------+-------------------+-------------------+-------------+
| 3 | Cristiano Marques | Cris | Defenseur central | 1977-06-03 | Bre |
| - | ----------------- | ---- | ----------------- | ---------- | --- |
| 12 | Anthony | Reveillere | Arriere droit | 1979-11-10 | Fra |
| 5 | Claudio Roberto | Cacapa | Defenseur central | 1976-05-29 | Bre |
| 15 | Alou | Diarra | Milieu defensif | 1981-07-15 | Fra |
+--------+-------------------+------------+-------------------+-------------------+-------------+
4 rows in set (0.00 sec)
</code>
La clause ''ORDER BY''¶
La clause ''ORDER BY'' permet de visualiser les réponses dans un ordre donné.
<code mysql | Syntaxe>
ORDER BY colonne1 { [ ASC ] | DESC } [, colonne2 { [ ASC ] | DESC } ] ...
</code>
ou
<code mysql | Syntaxe>
ORDER BY 1 { [ ASC ] | DESC } [, 2 { [ ASC ] | DESC } ] ...
</code>
Les chiffres 1, 2, ... sont relatifs à l'ordre des colonnes exprimées dans la clause ''SELECT''.
<code mysql | Recherche des noms de joueurs comme précedemment, mais trié par leur Nom>
mysql> SELECT * FROM joueurs WHERE (Prenom <= 'E') AND (Prenom >= 'A') ORDER BY Nom;
+--------+-------------------+------------+-------------------+-------------------+-------------+
| Numero | Prenom | Nom | Poste | Date_de_naissance | Nationalite |
| ------ | ------ | --- | ----- | ----------------- | ----------- |
+--------+-------------------+------------+-------------------+-------------------+-------------+
| 5 | Claudio Roberto | Cacapa | Defenseur central | 1976-05-29 | Bre |
| - | --------------- | ------ | ----------------- | ---------- | --- |
| 3 | Cristiano Marques | Cris | Defenseur central | 1977-06-03 | Bre |
| 15 | Alou | Diarra | Milieu defensif | 1981-07-15 | Fra |
| 12 | Anthony | Reveillere | Arriere droit | 1979-11-10 | Fra |
+--------+-------------------+------------+-------------------+-------------------+-------------+
4 rows in set (0.00 sec)
</code>
Par défaut, l'ordre de la clause ''ORDER BY'' est l'ordre ascendant (''ASC''). Pour obtenir un tri décroissant, il faut spécifier le mot-clé ''DESC'' pour chacune des colonnes que l'on veut trier.
<code mysql| Les joueurs du plus jeune au plus vieux>
mysql> SELECT * FROM joueurs ORDER BY Date_de_naissance DESC;
+--------+-------------------------+------------+-------------------+-------------------+-------------+
| Numero | Prenom | Nom | Poste | Date_de_naissance | Nationalite |
| ------ | ------ | --- | ----- | ----------------- | ----------- |
+--------+-------------------------+------------+-------------------+-------------------+-------------+
| 19 | Karim | Benzema | Avant centre | 1987-12-19 | Fra |
| -- | ----- | ------- | ------------ | ---------- | --- |
| 18 | Hatem | Ben Arfa | Ailier gauche | 1987-03-07 | Fra |
| 25 | Joan | Hartock | Gardien | 1987-02-17 | Fra |
| 39 | Gregory | Bettiol | Avant centre | 1986-03-30 | Fra |
| 38 | Mourad | Benhamida | Arriere droit | 1986-01-18 | Fra |
| 23 | Jeremy | Berthod | Arriere gauche | 1984-04-24 | Fra |
| 31 | Yacine | Hima | Milieu defensif | 1984-03-25 | Fra |
| 36 | Sylvain | Idangar | Milieu offensif | 1984-03-08 | Fra |
| 11 | Frederico Chaves Guedes | Fred | Avant centre | 1983-10-03 | Bre |
| 28 | Jeremy | Toulalan | Milieu defensif | 1983-09-10 | Fra |
| 2 | Francois | Clerc | Arriere droit | 1983-04-18 | Fra |
| 15 | Alou | Diarra | Milieu defensif | 1981-07-15 | Fra |
| 21 | Tiago | Mendes | Milieu defensif | 1981-05-02 | Por |
| 6 | Kim | Kallstrom | Milieu offensif | 1980-08-24 | Sue |
| 29 | Sebastien | Squillaci | Defenseur central | 1980-08-11 | Fra |
| 30 | Remy | Vercoutre | Gardien | 1980-06-26 | Fra |
| 10 | Florent | Malouda | Ailier gauche | 1980-06-13 | Fra |
| 12 | Anthony | Reveillere | Arriere droit | 1979-11-10 | Fra |
| 9 | John | Carew | Avant centre | 1979-09-05 | Nor |
| 14 | Sidney | Govou | Ailier droit | 1979-07-27 | Fra |
| 20 | Eric | Abidal | Arriere gauche | 1979-07-11 | Fra |
| 3 | Cristiano Marques | Cris | Defenseur central | 1977-06-03 | Bre |
| 4 | Patrick | Muller | Defenseur central | 1976-12-17 | Sui |
| 5 | Claudio Roberto | Cacapa | Defenseur central | 1976-05-29 | Bre |
| 8 | Pernambucano | Juninho | Milieu offensif | 1975-01-30 | Bre |
| 22 | Sylvain | Wiltord | Avant centre | 1974-05-10 | Fra |
| 1 | Gregory | Coupet | Gardien | 1972-12-31 | Fra |
+--------+-------------------------+------------+-------------------+-------------------+-------------+
27 rows in set (0.00 sec)
</code>
Il est possible de spécifier plusieurs clés de tri: une clé primaire suivi d'une clé secondaire, etc.
Les colonnes clé servant de tri peuvent être référencées dans la clause ''ORDER BY'' par leur numéro d'apparition dans la clause ''SELECT''
<code mysql| Utilisation de plusieurs clés>
mysql> select Nom, Prenom, Poste from joueurs where Poste='Gardien' OR Poste='Avant Centre' ORDER BY 2, 1;
+-----------+-------------------------+--------------+
| Nom | Prenom | Poste |
| --- | ------ | ----- |
+-----------+-------------------------+--------------+
| Fred | Frederico Chaves Guedes | Avant centre |
| ---- | ----------------------- | ------------ |
| Bettiol | Gregory | Avant centre |
| Coupet | Gregory | Gardien |
| Hartock | Joan | Gardien |
| Carew | John | Avant centre |
| Benzema | Karim | Avant centre |
| Vercoutre | Remy | Gardien |
| Wiltord | Sylvain | Avant centre |
+-----------+-------------------------+--------------+
8 rows in set (0.00 sec)
</code>
Traitement des chaînes de caractères¶
Il existe plusieurs opérateurs et fonctions permettant de travailler sur les chaînes de caractères.
Concaténation (CONCAT)¶
La fonction ''CONCAT'' permet de concaténer (ajouter bout à bout) des champs de type de caractères
<code mysql | Utilisation de la fonction CONCAT>
mysql> select CONCAT(Nom, ', ', Prenom) from joueurs
-> where Poste='Gardien';
+---------------------------+
| CONCAT(Nom, ', ', Prenom) |
| ------------------------- |
+---------------------------+
| Coupet, Gregory |
| --------------- |
| Vercoutre, Remy |
| Hartock, Joan |
+---------------------------+
3 rows in set (0.00 sec)
</code>
Recherche partielle (LIKE)¶
L'opérateur ''LIKE'' permet d'effectuer une comparaison partielle. Il est employé pour les colonnes contenant des donnees de type chaîne de caractères et utilise les caractères ''%'' et ''_'' pour désigner n'importe quelle chaîne de caractères (y compris la chaîne vide) ou un seul caractère respectivement.
<code mysql| Recherche des prénoms commençant par G>
mysql> select CONCAT(Nom, ', ', Prenom), Poste
-> from joueurs where Prenom LIKE 'G%';
+---------------------------+--------------+
| CONCAT(Nom, ', ', Prenom) | Poste |
| ------------------------- | ----- |
+---------------------------+--------------+
| Coupet, Gregory | Gardien |
| --------------- | ------- |
| Bettiol, Gregory | Avant centre |
+---------------------------+--------------+
2 rows in set (0.00 sec)
</code>
Distinction entre majuscules et minuscules (LOWER, UPPER)¶
Les fonctions ''LOWER'' et ''UPPER'' permettent de mettre en majuscules ou en minuscules des chaînes de caractères dans les requêtes.
<code mysql | Utilisation de LOWER ou UPPER>
mysql> select UPPER(Nom), Prenom from joueurs where Poste LIKE 'Avant %';
+------------+-------------------------+
| UPPER(Nom) | Prenom |
| ---------- | ------ |
+------------+-------------------------+
| BETTIOL | Gregory |
| ------- | ------- |
| FRED | Frederico Chaves Guedes |
| BENZEMA | Karim |
| WILTORD | Sylvain |
| CAREW | John |
+------------+-------------------------+
5 rows in set (0.00 sec)
</code>
On peut aussi grâce à ces fonctions effectuer des recherches sans tenir compte de la casse:
<code mysql|recherche dans tenir compte de la casse>
mysql> select Nom, Prenom, Nationalite from joueurs
-> where LOWER(Nationalite)='bre';
+---------+-------------------------+-------------+
| Nom | Prenom | Nationalite |
| --- | ------ | ----------- |
+---------+-------------------------+-------------+
| Cris | Cristiano Marques | Bre |
| ---- | ----------------- | --- |
| Cacapa | Claudio Roberto | Bre |
| Juninho | Pernambucano | Bre |
| Fred | Frederico Chaves Guedes | Bre |
+---------+-------------------------+-------------+
4 rows in set (0.00 sec)
</code>
Remplacement (REPLACE)¶
La fonction ''REPLACE'' permet de remplacer une chaîne de caractère par une autre.
Syntax: ''REPLACE'' (chaîne, source, cible)
<code mysql| Exemple d'utilisation de REPLACE>
mysql> select Nom, REPLACE(Poste, 'Gardien', 'Goal')
-> from joueurs where Poste='Gardien';
+-----------+-----------------------------------+
| Nom | REPLACE(Poste, 'Gardien', 'Goal') |
| --- | --------------------------------- |
+-----------+-----------------------------------+
| Coupet | Goal |
| ------ | ---- |
| Vercoutre | Goal |
| Hartock | Goal |
+-----------+-----------------------------------+
3 rows in
set (0.
0 sec)
</code>
Traitement numérique¶
SQL intégre les fonctions élémentaires du calcul numérique +, -, * et /. Mais il existe aussi bien d'autres fonctions.
<code mysql | opérations élémentaires>
mysql> select 3+5, 2-5, ¾, 2*3;
+-----+-----+--------+-----+
| 3+5 | 2-5 | ¾ | 2*3 |
| --- | --- | --- | --- |
+-----+-----+--------+-----+
| 8 | -3 | 0.7500 | 6 |
| - | -- | ------ | - |
+-----+-----+--------+-----+
1 row in set (0.00 sec)
</code>
Les fonctions mathématiques (MySQL): | Syntaxe | Fonction | | ------- | -------- | | ABS(n) | Valeur absolue | | ACOS(n) | Cosinus réciproque | | ASIN(n) | Sinus réciproque | | ATAN(n) | Tangente réciproque | | CEILING(n) | Borne supérieure entière de n | | COS(n) | Cosinus | | EXP(n) | Exponentiel | | FLOOR(n) | Borne inférieure entière de n | | LN(n) | Logarithme népérien | | LOG(n) | Logarithme décimal | | MOD(n, m) | Modulo (reste de la division entière de n par m | | PI() | Valeur de Pi | | POWER(n, m) | n à la puissance m | | RAND() | Nombre aléatoire entre 0 et 1 | | ROUND(n) | Entier le plus proche de n | | ROUND(n,m) | Nombre le plus proche de n, à m chiffres après la virgule près | | SIGN(n) | Renvoie -1, 0 ou 1 suivant que le nombre est négatif, nul ou positive | | SIN(n) | Sinus | | SQRT(n) | Racine carrée | | TAN(n) | Tangente |
Fonctions de traitement des données temporelles¶
En SQL, il est possible de définir er manipuler des données temporelles.
Deux sortes de données sont accessibles: la date (jour, mois, année) et le temps (heures, minutes, secondes), séparément ou ensemble.
<code mysql | Utilisation de la date et du temps courant>
mysql> select current_date, current_time, current_timestamp;
+--------------+--------------+---------------------+
| current_date | current_time | current_timestamp |
| ------------ | ------------ | ----------------- |
+--------------+--------------+---------------------+
| 2006-09-13 | 23:41:16 | 2006-09-13 23:41:16 |
| ---------- | -------- | ------------------- |
+--------------+--------------+---------------------+
1 row in set (0.00 sec)
</code>
La particularité de SQL est d'afficher la date sous la forme d'une chaîne de caractères 'AAAA-MM-JJ' (Année, Jour, Mois). Le temps est donné sous la forme 'HH:MM:SS'.
<code mysql | Sélection des joueurs nés après le 1er janvier 1980>
mysql> select Nom, Date_de_naissance from joueurs where Date_de_naissance > '1980-01-01';
+-----------+-------------------+
| Nom | Date_de_naissance |
| --- | ----------------- |
+-----------+-------------------+
| Vercoutre | 1980-06-26 |
| --------- | ---------- |
| Hartock | 1987-02-17 |
| Berthod | 1984-04-24 |
| Squillaci | 1980-08-11 |
| Benhamida | 1986-01-18 |
| Clerc | 1983-04-18 |
| Malouda | 1980-06-13 |
| Diarra | 1981-07-15 |
| Toulalan | 1983-09-10 |
| Idangar | 1984-03-08 |
| Hima | 1984-03-25 |
| Kallstrom | 1980-08-24 |
| Mendes | 1981-05-02 |
| Bettiol | 1986-03-30 |
| Fred | 1983-10-03 |
| Benzema | 1987-12-19 |
| Ben Arfa | 1987-03-07 |
+-----------+-------------------+
17 rows in set (0.00 sec)
</code>
Il est possible d'extraire une partie d'une date ou d'un temps grâce à la fonction ''EXTRACT''.
Syntaxe: ''EXTRACT( { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND } FROM ...''
<code mysql | Les joueurs nés en mars>
mysql> select Nom, EXTRACT(YEAR FROM Date_de_naissance)
-> from joueurs where EXTRACT(MONTH FROM Date_de_naissance) = 03;
+----------+--------------------------------------+
| Nom | EXTRACT(YEAR FROM Date_de_naissance) |
| --- | ------------------------------------ |
+----------+--------------------------------------+
| Idangar | 1984 |
| ------- | ---- |
| Hima | 1984 |
| Bettiol | 1986 |
| Ben Arfa | 1987 |
+----------+--------------------------------------+
4 rows in set (0.00 sec)
</code>
L'opérateur '-' permet d'effectuer la différence entre deux dates. Cela donne un résultat de type INTERVAL
L'opérateur '+' permet d'ajouter un INTERVAL à une date.
<code mysql| Utilisation de INTERVAL>
mysql> select Nom, Date_de_naissance, Date_de_naissance + INTERVAL 2 DAY
-> from joueurs where EXTRACT(MONTH FROM Date_de_naissance) = 03;
+----------+-------------------+------------------------------------+
| Nom | Date_de_naissance | Date_de_naissance + INTERVAL 2 DAY |
| --- | ----------------- | ---------------------------------- |
+----------+-------------------+------------------------------------+
| Idangar | 1984-03-08 | 1984-03-10 |
| ------- | ---------- | ---------- |
| Hima | 1984-03-25 | 1984-03-27 |
| Bettiol | 1986-03-30 | 1986-04-01 |
| Ben Arfa | 1987-03-07 | 1987-03-09 |
+----------+-------------------+------------------------------------+
4 rows in set (0.00 sec)
</code>
Fonctions d'agrégation¶
Il est possible de réaliser aussi des statistiques sur des colonnes à l'aide de différents opérateurs:
| Opérateur | Signification |
|---|---|
| AVG | Moyenne |
| MAX | Maximum |
| MIN | Minimum |
| SUM | Total |
| COUNT | Nombre |
<code mysql| Nombre de joueurs>
mysql> select count(Nom) from joueurs;
+------------+
| count(Nom) |
| ---------- |
+------------+
| 27 |
| -- |
+------------+
1 row in set (0.00 sec)
</code>
Pour les fonctions ''COUNT'', ''SUM'', ou ''AVG'' il est possible d'effectuer la statistique en évitant les doublons éventuels. Cela est possible grâce au mot-clé ''DISTINCT''.
<code mysql | comptage du nombre de postes différents>
mysql> select count(poste) from joueurs;
+--------------+
| count(poste) |
| ------------ |
+--------------+
| 27 |
| -- |
+--------------+
1 row in set (0.00 sec)
mysql> select count(distinct poste) from joueurs;
+-----------------------+
| count(distinct poste) |
| --------------------- |
+-----------------------+
| 9 |
| - |
+-----------------------+
1 row in set (0.00 sec)
</code>
La clause ''GROUP BY''¶
La clause ''GROUP BY'' permet d'effectuer un regroupement d'enregistrements.
<code mysql| Nombre de joueurs par poste>
mysql> select Poste, Count(Poste) from joueurs group by Poste;
+-------------------+--------------+
| Poste | Count(Poste) |
| ----- | ------------ |
+-------------------+--------------+
| Ailier droit | 1 |
| ------------ | - |
| Ailier gauche | 2 |
| Arriere droit | 3 |
| Arriere gauche | 2 |
| Avant centre | 5 |
| Defenseur central | 4 |
| Gardien | 3 |
| Milieu defensif | 4 |
| Milieu offensif | 3 |
+-------------------+--------------+
9 rows in set (0.00 sec)
</code>
Opérateur IN¶
L'opérateur ''IN'' permet de chercher si une valeur se trouve dans un ensemble donné: une liste.
Syntaxe: valeur ''IN'' (valeur1, [valeur2 ...])
<code mysql| Recherche des joueurs prénommés Grégory ou Rémy>
mysql> select Nom, Prenom from joueurs where Prenom IN ('Gregory', 'Remy');
+-----------+---------+
| Nom | Prenom |
| --- | ------ |
+-----------+---------+
| Coupet | Gregory |
| ------ | ------- |
| Vercoutre | Remy |
| Bettiol | Gregory |
+-----------+---------+
3 rows in set (0.31 sec)
</code>
Opérateur BETWEEN¶
L'opérateur ''BETWEEN'' permet de rechercher si une valeur se situe dans un intervalle donné (valeurs incluses).
Syntaxe: valeure ''BETWEEN'' valeur1 ''AND'' valeur2
<code mysql| Recherche des joueurs dont le numéro est compris entre 5 et 8>
mysql> select Numero, Nom from joueurs where Numero BETWEEN 5 AND 8;
+--------+-----------+
| Numero | Nom |
| ------ | --- |
+--------+-----------+
| 5 | Cacapa |
| - | ------ |
| 6 | Kallstrom |
| 8 | Juninho |
+--------+-----------+
3 rows in set (0.00 sec)
</code>
<code mysql|Recherche des joueurs nés entre le 1er janvier 1980 et le 30 juin 1985>
mysql> select Numero, Nom,Date_de_naissance
-> from joueurs where Date_de_naissance BETWEEN '1980-01-01' AND '1985-06-30';
+--------+-----------+-------------------+
| Numero | Nom | Date_de_naissance |
| ------ | --- | ----------------- |
+--------+-----------+-------------------+
| 30 | Vercoutre | 1980-06-26 |
| -- | --------- | ---------- |
| 23 | Berthod | 1984-04-24 |
| 29 | Squillaci | 1980-08-11 |
| 2 | Clerc | 1983-04-18 |
| 10 | Malouda | 1980-06-13 |
| 15 | Diarra | 1981-07-15 |
| 28 | Toulalan | 1983-09-10 |
| 36 | Idangar | 1984-03-08 |
| 31 | Hima | 1984-03-25 |
| 6 | Kallstrom | 1980-08-24 |
| 21 | Mendes | 1981-05-02 |
| 11 | Fred | 1983-10-03 |
+--------+-----------+-------------------+
12 rows in set (0.00 sec)
</code>
La clause ''HAVING''¶
La clause ''HAVING'' s'exécute après que les clauses ''SELECT'' et ''WHERE'' ont donné des résultats. Elle crée un filtre supplémentaire sur les résultats et non sur les données.
<code mysql| rechercher les postes peu fournies>
mysql> SELECT Poste, Count(Poste)
-> FROM joueurs
-> GROUP BY Poste HAVING Count(Poste) <= 2;
+----------------+--------------+
| Poste | Count(Poste) |
| ----- | ------------ |
+----------------+--------------+
| Ailier droit | 1 |
| ------------ | - |
| Ailier gauche | 2 |
| Arriere gauche | 2 |
+----------------+--------------+
3 rows in set (0.00 sec)
</code>
Requêtes sur plusieurs tables¶
Il est possible d'effectuer en SQL des requêtes sur plusieurs tables à la fois.
Le surnom¶
Il est possible de donner un surnom à une table dans la clause ''FROM''.
Syntaxe: ''FROM'' table surnom
Dès lors, ce surnom peut être utilisé pour préciser de quelle table provient une colonne. Cela s'effectue à l'aide de la notation pointée.
<code mysql|utilisation des surnoms>
mysql> select joueurs.Numero, Nom FROM joueurs WHERE Numero BETWEEN 20 AND 50;
+--------+-----------+
| Numero | Nom |
| ------ | --- |
+--------+-----------+
| 30 | Vercoutre |
| -- | --------- |
| 25 | Hartock |
| 20 | Abidal |
| 23 | Berthod |
| 29 | Squillaci |
| 38 | Benhamida |
| 28 | Toulalan |
| 36 | Idangar |
| 31 | Hima |
| 21 | Mendes |
| 39 | Bettiol |
| 22 | Wiltord |
+--------+-----------+
12 rows in set (0.00 sec)
mysql> select J.Numero, J.Nom FROM joueurs J WHERE J.Numero BETWEEN 20 AND 50;
+--------+-----------+
| Numero | Nom |
| ------ | --- |
+--------+-----------+
| 30 | Vercoutre |
| -- | --------- |
| 25 | Hartock |
| 20 | Abidal |
| 23 | Berthod |
| 29 | Squillaci |
| 38 | Benhamida |
| 28 | Toulalan |
| 36 | Idangar |
| 31 | Hima |
| 21 | Mendes |
| 39 | Bettiol |
| 22 | Wiltord |
+--------+-----------+
12 rows in set (0.00 sec)
</code>
Les clés¶
Clé primaire (PRIMARY KEY)¶
Une clé primaire (ou index primaire ou identifiant) permet de retrouver sans ambiguïté la ligne d'une table. Elle est donc unique au sein de la table.
Clé étrangère (FOREIGN KEY)¶
Une clé étrangère est une clé d'une table insérée dans une autre table afin d'assurer une relation entre les deux tables.
<code mysql| La table Joueurs contenant une clé primaire et une clé étrangère>
mysql> desc Joueurs;
+-------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
| ----- | ---- | ---- | --- | ------- | ----- |
+-------------------+--------------+------+-----+---------+----------------+
| JoueurID | int(11) | NO | PRI | NULL | auto_increment |
| -------- | ------- | -- | --- | ---- | -------------- |
| Numero | int(11) | NO | | NULL | |
| Prenom | varchar(100) | NO | | NULL | |
| Nom | varchar(100) | NO | | NULL | |
| Poste | varchar(100) | NO | | NULL | |
| Date_de_naissance | date | NO | | NULL | |
| Nationalite | varchar(3) | NO | | NULL | |
| ClubID | int(11) | NO | MUL | NULL | |
+-------------------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
</code>
<code mysql| Contenu de la table Joueurs>
mysql> select * from Joueurs;
+----------+--------+-------------------------+------------+-------------------+-------------------+-------------+--------+
| JoueurID | Numero | Prenom | Nom | Poste | Date_de_naissance | Nationalite | ClubID |
| -------- | ------ | ------ | --- | ----- | ----------------- | ----------- | ------ |
+----------+--------+-------------------------+------------+-------------------+-------------------+-------------+--------+
| 1 | 1 | Gregory | Coupet | Gardien | 1972-12-31 | Fra | 1 |
| - | - | ------- | ------ | ------- | ---------- | --- | - |
| 2 | 30 | Remy | Vercoutre | Gardien | 1980-06-26 | Fra | 1 |
| 3 | 25 | Joan | Hartock | Gardien | 1987-02-17 | Fra | 1 |
| 4 | 20 | Eric | Abidal | Arriere gauche | 1979-07-11 | Fra | 1 |
| 5 | 23 | Jeremy | Berthod | Arriere gauche | 1984-04-24 | Fra | 1 |
| 6 | 29 | Sebastien | Squillaci | Defenseur central | 1980-08-11 | Fra | 1 |
| 7 | 4 | Patrick | Muller | Defenseur central | 1976-12-17 | Sui | 1 |
| 8 | 3 | Cristiano Marques | Cris | Defenseur central | 1977-06-03 | Bre | 1 |
| 9 | 38 | Mourad | Benhamida | Arriere droit | 1986-01-18 | Fra | 1 |
| 10 | 2 | Francois | Clerc | Arriere droit | 1983-04-18 | Fra | 1 |
| 11 | 12 | Anthony | Reveillere | Arriere droit | 1979-11-10 | Fra | 1 |
| 12 | 5 | Claudio Roberto | Cacapa | Defenseur central | 1976-05-29 | Bre | 1 |
| 13 | 10 | Florent | Malouda | Ailier gauche | 1980-06-13 | Fra | 1 |
| 14 | 15 | Alou | Diarra | Milieu defensif | 1981-07-15 | Fra | 1 |
| 15 | 28 | Jeremy | Toulalan | Milieu defensif | 1983-09-10 | Fra | 1 |
| 16 | 36 | Sylvain | Idangar | Milieu offensif | 1984-03-08 | Fra | 1 |
| 17 | 31 | Yacine | Hima | Milieu defensif | 1984-03-25 | Fra | 1 |
| 18 | 6 | Kim | Kallstrom | Milieu offensif | 1980-08-24 | Sue | 1 |
| 19 | 8 | Pernambucano | Juninho | Milieu offensif | 1975-01-30 | Bre | 1 |
| 20 | 21 | Tiago | Mendes | Milieu defensif | 1981-05-02 | Por | 1 |
| 21 | 14 | Sidney | Govou | Ailier droit | 1979-07-27 | Fra | 1 |
| 22 | 39 | Gregory | Bettiol | Avant centre | 1986-03-30 | Fra | 1 |
| 23 | 11 | Frederico Chaves Guedes | Fred | Avant centre | 1983-10-03 | Bre | 1 |
| 24 | 19 | Karim | Benzema | Avant centre | 1987-12-19 | Fra | 1 |
| 25 | 18 | Hatem | Ben Arfa | Ailier gauche | 1987-03-07 | Fra | 1 |
| 26 | 22 | Sylvain | Wiltord | Avant centre | 1974-05-10 | Fra | 1 |
| 27 | 9 | John | Carew | Avant centre | 1979-09-05 | Nor | 1 |
| 28 | 10 | Nourdin | Boukhari | Milieu | 1980-06-30 | P-B | 2 |
+----------+--------+-------------------------+------------+-------------------+-------------------+-------------+--------+
28 rows in set (0.00 sec)
</code>
Dans la table ''Joueurs'', la clé ''JoueurID'' est unique par joueur. La clé ''ClubID'' est une clé étrangère dans la table ''Joueurs'' et une clé primaire dans la table ''Club''.
<code mysql|Contenu de la table Club>
mysql> select * from Club;
+--------+--------------------+
| ClubID | Nom |
| ------ | --- |
+--------+--------------------+
| 1 | Olympique Lyonnais |
| - | ------------------ |
| 2 | Nantes |
+--------+--------------------+
2 rows in set (0.00 sec)
</code>
Les jointures¶
Les jointures permettent de relier les tables entre elles.
Sans elles, l'interrogation de différentes tables simultanèment conduit au produit cartesien des enregistrements.
<code mysql | Interrogation sans jointure>
mysql> SELECT C.ClubID, C.Nom, J.Numero, J.Nom, J.ClubID
-> FROM Club C, Joueurs J
-> WHERE J.Numero > 25;
+--------+--------------------+--------+-----------+--------+
| ClubID | Nom | Numero | Nom | ClubID |
| ------ | --- | ------ | --- | ------ |
+--------+--------------------+--------+-----------+--------+
| 1 | Olympique Lyonnais | 30 | Vercoutre | 1 |
| - | ------------------ | -- | --------- | - |
| 2 | Nantes | 30 | Vercoutre | 1 |
| 1 | Olympique Lyonnais | 29 | Squillaci | 1 |
| 2 | Nantes | 29 | Squillaci | 1 |
| 1 | Olympique Lyonnais | 38 | Benhamida | 1 |
| 2 | Nantes | 38 | Benhamida | 1 |
| 1 | Olympique Lyonnais | 28 | Toulalan | 1 |
| 2 | Nantes | 28 | Toulalan | 1 |
| 1 | Olympique Lyonnais | 36 | Idangar | 1 |
| 2 | Nantes | 36 | Idangar | 1 |
| 1 | Olympique Lyonnais | 31 | Hima | 1 |
| 2 | Nantes | 31 | Hima | 1 |
| 1 | Olympique Lyonnais | 39 | Bettiol | 1 |
| 2 | Nantes | 39 | Bettiol | 1 |
+--------+--------------------+--------+-----------+--------+
14 rows in set (0.00 sec)
</code>
Jointure simple¶
La jointure s'effectue en imposant une condition d'égalité entre des valeurs de deux colonnes d'une table.
<code mysql | Un exemple de jointure>
mysql> select C.Nom AS Club, J.Numero, J.Nom
-> from Club C, Joueurs J
-> where J.Numero > 25 AND J.ClubID = C.ClubID;
+--------------------+--------+-----------+
| Club | Numero | Nom |
| ---- | ------ | --- |
+--------------------+--------+-----------+
| Olympique Lyonnais | 30 | Vercoutre |
| ------------------ | -- | --------- |
| Olympique Lyonnais | 29 | Squillaci |
| Olympique Lyonnais | 38 | Benhamida |
| Olympique Lyonnais | 28 | Toulalan |
| Olympique Lyonnais | 36 | Idangar |
| Olympique Lyonnais | 31 | Hima |
| Olympique Lyonnais | 39 | Bettiol |
+--------------------+--------+-----------+
7 rows in set (0.00 sec)
</code>
Jointure interne (INNER JOIN)¶
Il est possible d'écrire de manière équivalente la jointure précédente directement dans la partie ''FROM'' en utilisant ''INNER JOIN'':
<code| Utilisation de INNER JOIN ... ON ...>
mysql> SELECT C.ClubID, C.Nom, J.Numero, J.Nom, J.ClubID
-> FROM Joueurs J INNER JOIN Club C ON J.ClubID=C.ClubID
-> WHERE J.Numero > 25 ;
+--------+--------------------+--------+-----------+--------+
| ClubID | Nom | Numero | Nom | ClubID |
| ------ | --- | ------ | --- | ------ |
+--------+--------------------+--------+-----------+--------+
| 1 | Olympique Lyonnais | 30 | Vercoutre | 1 |
| - | ------------------ | -- | --------- | - |
| 1 | Olympique Lyonnais | 29 | Squillaci | 1 |
| 1 | Olympique Lyonnais | 38 | Benhamida | 1 |
| 1 | Olympique Lyonnais | 28 | Toulalan | 1 |
| 1 | Olympique Lyonnais | 36 | Idangar | 1 |
| 1 | Olympique Lyonnais | 31 | Hima | 1 |
| 1 | Olympique Lyonnais | 39 | Bettiol | 1 |
+--------+--------------------+--------+-----------+--------+
7 rows in set (0.33 sec)
</code>
Dans le cas où les clés sont identiques entre les deux tables:
<code|Utilisation de INNER JOIN ... USING ..>
mysql> SELECT C.ClubID, C.Nom, J.Numero, J.Nom, J.ClubID
-> FROM Joueurs J INNER JOIN Club C USING (ClubID)
-> WHERE J.Numero > 25 ;
+--------+--------------------+--------+-----------+--------+
| ClubID | Nom | Numero | Nom | ClubID |
| ------ | --- | ------ | --- | ------ |
+--------+--------------------+--------+-----------+--------+
| 1 | Olympique Lyonnais | 30 | Vercoutre | 1 |
| - | ------------------ | -- | --------- | - |
| 1 | Olympique Lyonnais | 29 | Squillaci | 1 |
| 1 | Olympique Lyonnais | 38 | Benhamida | 1 |
| 1 | Olympique Lyonnais | 28 | Toulalan | 1 |
| 1 | Olympique Lyonnais | 36 | Idangar | 1 |
| 1 | Olympique Lyonnais | 31 | Hima | 1 |
| 1 | Olympique Lyonnais | 39 | Bettiol | 1 |
+--------+--------------------+--------+-----------+--------+
7 rows in set (0.33 sec)
</code>
Jointure externe (LEFT/RIGHT JOIN)¶
Une jointure externe permet de garder les enregistrements qui n'ont pas de correspondance avec la table sur laquelle on effectue une jointure.
<code | Utilisation de LEFT JOIN >
mysql> SELECT C.ClubID, C.Nom, J.Numero, J.Nom, J.ClubID FROM Joueurs J LEFT JOIN Club C ON J.ClubID=C.ClubID;
+--------+--------------------+--------+------------+--------+
| ClubID | Nom | Numero | Nom | ClubID |
| ------ | --- | ------ | --- | ------ |
+--------+--------------------+--------+------------+--------+
| 1 | Olympique Lyonnais | 1 | Coupet | 1 |
| - | ------------------ | - | ------ | - |
| 1 | Olympique Lyonnais | 30 | Vercoutre | 1 |
| 1 | Olympique Lyonnais | 25 | Hartock | 1 |
| 1 | Olympique Lyonnais | 20 | Abidal | 1 |
| 1 | Olympique Lyonnais | 23 | Berthod | 1 |
| 1 | Olympique Lyonnais | 29 | Squillaci | 1 |
| 1 | Olympique Lyonnais | 4 | Muller | 1 |
| 1 | Olympique Lyonnais | 3 | Cris | 1 |
| 1 | Olympique Lyonnais | 38 | Benhamida | 1 |
| 1 | Olympique Lyonnais | 2 | Clerc | 1 |
| 1 | Olympique Lyonnais | 12 | Reveillere | 1 |
| 1 | Olympique Lyonnais | 5 | Cacapa | 1 |
| 1 | Olympique Lyonnais | 10 | Malouda | 1 |
| 1 | Olympique Lyonnais | 15 | Diarra | 1 |
| 1 | Olympique Lyonnais | 28 | Toulalan | 1 |
| 1 | Olympique Lyonnais | 36 | Idangar | 1 |
| 1 | Olympique Lyonnais | 31 | Hima | 1 |
| 1 | Olympique Lyonnais | 6 | Kallstrom | 1 |
| 1 | Olympique Lyonnais | 8 | Juninho | 1 |
| 1 | Olympique Lyonnais | 21 | Mendes | 1 |
| 1 | Olympique Lyonnais | 14 | Govou | 1 |
| 1 | Olympique Lyonnais | 39 | Bettiol | 1 |
| 1 | Olympique Lyonnais | 11 | Fred | 1 |
| 1 | Olympique Lyonnais | 19 | Benzema | 1 |
| 1 | Olympique Lyonnais | 18 | Ben Arfa | 1 |
| 1 | Olympique Lyonnais | 22 | Wiltord | 1 |
| 1 | Olympique Lyonnais | 9 | Carew | 1 |
| 2 | FC Nantes | 10 | Boukhari | 2 |
| 19 | Toulouse FC | 6 | Fabinho | 19 |
| 11 | Girondins Bordeaux | 18 | Faubert | 11 |
| 14 | OGC Nice | 19 | Vahirua | 14 |
| 9 | FC Lorient | 2 | Ciani | 9 |
+--------+--------------------+--------+------------+--------+
32 rows in set (0.00 sec)
</code>
<code | Utilisation de RIGHT JOIN>
mysql> SELECT C.ClubID, C.Nom, J.Numero, J.Nom, J.ClubID FROM Joueurs J RIGHT JOIN Club C ON J.ClubID=C.ClubID;
+--------+------------------------+--------+------------+--------+
| ClubID | Nom | Numero | Nom | ClubID |
| ------ | --- | ------ | --- | ------ |
+--------+------------------------+--------+------------+--------+
| 1 | Olympique Lyonnais | 1 | Coupet | 1 |
| - | ------------------ | - | ------ | - |
| 1 | Olympique Lyonnais | 30 | Vercoutre | 1 |
| 1 | Olympique Lyonnais | 25 | Hartock | 1 |
| 1 | Olympique Lyonnais | 20 | Abidal | 1 |
| 1 | Olympique Lyonnais | 23 | Berthod | 1 |
| 1 | Olympique Lyonnais | 29 | Squillaci | 1 |
| 1 | Olympique Lyonnais | 4 | Muller | 1 |
| 1 | Olympique Lyonnais | 3 | Cris | 1 |
| 1 | Olympique Lyonnais | 38 | Benhamida | 1 |
| 1 | Olympique Lyonnais | 2 | Clerc | 1 |
| 1 | Olympique Lyonnais | 12 | Reveillere | 1 |
| 1 | Olympique Lyonnais | 5 | Cacapa | 1 |
| 1 | Olympique Lyonnais | 10 | Malouda | 1 |
| 1 | Olympique Lyonnais | 15 | Diarra | 1 |
| 1 | Olympique Lyonnais | 28 | Toulalan | 1 |
| 1 | Olympique Lyonnais | 36 | Idangar | 1 |
| 1 | Olympique Lyonnais | 31 | Hima | 1 |
| 1 | Olympique Lyonnais | 6 | Kallstrom | 1 |
| 1 | Olympique Lyonnais | 8 | Juninho | 1 |
| 1 | Olympique Lyonnais | 21 | Mendes | 1 |
| 1 | Olympique Lyonnais | 14 | Govou | 1 |
| 1 | Olympique Lyonnais | 39 | Bettiol | 1 |
| 1 | Olympique Lyonnais | 11 | Fred | 1 |
| 1 | Olympique Lyonnais | 19 | Benzema | 1 |
| 1 | Olympique Lyonnais | 18 | Ben Arfa | 1 |
| 1 | Olympique Lyonnais | 22 | Wiltord | 1 |
| 1 | Olympique Lyonnais | 9 | Carew | 1 |
| 2 | FC Nantes | 10 | Boukhari | 2 |
| 3 | AJ Auxerre | NULL | NULL | NULL |
| 4 | AS Monaco | NULL | NULL | NULL |
| 5 | AS Nancy Lorraine | NULL | NULL | NULL |
| 6 | Olympique de Marseille | NULL | NULL | NULL |
| 7 | CS Sedan Ardennes | NULL | NULL | NULL |
| 8 | ES Troyes AC | NULL | NULL | NULL |
| 9 | FC Lorient | 2 | Ciani | 9 |
| 10 | FC Sochaux | NULL | NULL | NULL |
| 11 | Girondins Bordeaux | 18 | Faubert | 11 |
| 12 | Le Mans | NULL | NULL | NULL |
| 13 | Lille OSC | NULL | NULL | NULL |
| 14 | OGC Nice | 19 | Vahirua | 14 |
| 15 | Paris Saint-Germain | NULL | NULL | NULL |
| 16 | RC Lens | NULL | NULL | NULL |
| 17 | Rennes | NULL | NULL | NULL |
| 18 | Saint-Etienne | NULL | NULL | NULL |
| 19 | Toulouse FC | 6 | Fabinho | 19 |
| 20 | Valenciennes | NULL | NULL | NULL |
+--------+------------------------+--------+------------+--------+
46 rows in set (0.34 sec)
</code>
<code |Comptage des joueurs par club avec LEFT JOIN>
mysql> SELECT C.Nom AS Club, COUNT(J.Nom) AS "NOMBRE DE JOUEURS"
-> FROM Joueurs J LEFT JOIN Club C USING (ClubID)
-> GROUP BY C.ClubID ;
+--------------------+-------------------+
| Club | NOMBRE DE JOUEURS |
| ---- | ----------------- |
+--------------------+-------------------+
| Olympique Lyonnais | 27 |
| ------------------ | -- |
| FC Nantes | 1 |
| FC Lorient | 1 |
| Girondins Bordeaux | 1 |
| OGC Nice | 1 |
| Toulouse FC | 1 |
+--------------------+-------------------+
6 rows in set (0.00 sec)
</code>
<code | Comptage des joueurs par club avec RIGHT JOIN>
mysql> SELECT C.Nom AS Club, COUNT(J.Nom) AS "NOMBRE DE JOUEURS"
-> FROM Joueurs J RIGHT JOIN Club C USING (ClubID)
-> GROUP BY C.ClubID ;
+------------------------+-------------------+
| Club | NOMBRE DE JOUEURS |
| ---- | ----------------- |
+------------------------+-------------------+
| Olympique Lyonnais | 27 |
| ------------------ | -- |
| FC Nantes | 1 |
| AJ Auxerre | 0 |
| AS Monaco | 0 |
| AS Nancy Lorraine | 0 |
| Olympique de Marseille | 0 |
| CS Sedan Ardennes | 0 |
| ES Troyes AC | 0 |
| FC Lorient | 1 |
| FC Sochaux | 0 |
| Girondins Bordeaux | 1 |
| Le Mans | 0 |
| Lille OSC | 0 |
| OGC Nice | 1 |
| Paris Saint-Germain | 0 |
| RC Lens | 0 |
| Rennes | 0 |
| Saint-Etienne | 0 |
| Toulouse FC | 1 |
| Valenciennes | 0 |
+------------------------+-------------------+
20 rows in set (0.00 sec)
</code>
Les sous-requêtes¶
SQL permet d'imbriquer des ordres ''SELECT'' au sein de requêtes de type ''SELECT''. On parle alors de sous-requêtes.
En fonction des résultats d'une sous-requête SQL qui fournit :
-
soit plusieurs colonnes et plusieurs lignes
-
soit plusieurs colonnes et une seule ligne
-
soit une seule colonne sur plusieurs lignes
-
soit une seule colonne et une seule ligne
-
soit aucun résultat Il est possible d'écrire une requête à l'aide de sous-requêtes dans les clauses SELECT ou WHERE.
<code mysql| Un exemple de clauses SELECT imbriquées>
mysql> SELECT "Olympique Lyonnais" AS Club, Numero, Nom
-> FROM Joueurs
-> WHERE Numero > 25 AND
ClubID = (SELECT ClubID
from Club
WHERE Nom="Olympique Lyonnais");
+--------------------+--------+-----------+
| Club | Numero | Nom |
| ---- | ------ | --- |
+--------------------+--------+-----------+
| Olympique Lyonnais | 30 | Vercoutre |
| ------------------ | -- | --------- |
| Olympique Lyonnais | 29 | Squillaci |
| Olympique Lyonnais | 38 | Benhamida |
| Olympique Lyonnais | 28 | Toulalan |
| Olympique Lyonnais | 36 | Idangar |
| Olympique Lyonnais | 31 | Hima |
| Olympique Lyonnais | 39 | Bettiol |
+--------------------+--------+-----------+
7 rows in set (0.00 sec)
</code>
<code mysql| Autre exemple>
mysql> SELECT (SELECT Nom
FROM Club
WHERE ClubID = (SELECT ClubID
FROM Joueurs
WHERE Nom="Coupet")
) AS Club, Numero, Nom
FROM Joueurs
WHERE Numero > 25 AND
ClubID = (SELECT ClubID
FROM Joueurs
WHERE Nom="Coupet");
+--------------------+--------+-----------+
| Club | Numero | Nom |
| ---- | ------ | --- |
+--------------------+--------+-----------+
| Olympique Lyonnais | 30 | Vercoutre |
| ------------------ | -- | --------- |
| Olympique Lyonnais | 29 | Squillaci |
| Olympique Lyonnais | 38 | Benhamida |
| Olympique Lyonnais | 28 | Toulalan |
| Olympique Lyonnais | 36 | Idangar |
| Olympique Lyonnais | 31 | Hima |
| Olympique Lyonnais | 39 | Bettiol |
+--------------------+--------+-----------+
7 rows in set (0.00 sec)
</code>