Bonjour à vous,
On se retrouve pour le deuxième tutoriel sur le SQL et aujourd’hui, on va s'intéresser au SELECT.
Je vous invite vivement à lire le premier chapitre, qui constitue une petite introduction sur ce qu'est le SQL ici :
Revenons à nos moutons, voici de quoi sera composé ce cours :
Projection
Restriction/Sélection
Jointures
De plus, pendant ce cours, il se peut que je fasse référence à l'algèbre relationnelle. Ce n'est pas obligatoire, je ne m'attarderai pas dessus, mais si vous connaissez, ça ne peut être qu'un plus pour vraiment bien visualiser ce que vous voulez récupérer comme données.
En ayant vu ça, nous aurons déjà fait pas mal le tour des possibilités offertes par le SELECT.
I - Cas général :
Le SELECT est donc une requête SQL. En faisant un SELECT, on réalise une projection des données de la base de données, en fonction de différents critères.
On l'écrit de la forme suivante :
SELECT <liste des attributs projetés>
FROM <liste des relations touchées par la projection>
[ WHERE <liste des critères de restriction>
GROUP BY <liste des attributs d'agrégation>
HAVING <liste des critères de restriction des agrégats>
ORDER BY <liste des attributs du tri du résultat> ]
Pour ceux qui connaissent l'algèbre relationnelle, il est facile d'établir une correspondance avec un SELECT dans les requêtes simples.
Si vous n'avez pas compris grand chose de ce que je viens d'expliquer, pas de panique, il s'agit juste de la structure de la requête. Les éléments entre crochets sont facultatifs.
Tout au long de ce tutoriel, j'utiliserai le schéma de base de données suivant :
DEPT(DEPTNO, DNAME, LOC)
EMP(EMPNO, ENAME, JOB, #MGR, HIREDATE, SAL, COMM, #DEPTNO)
Les tables soulignées sont des clés primaires, et celles avec un #devant sont des clés étrangères. Pour mieux comprendre, je vous invite à jeter un coup d'oeil par ici : http://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql/cles-primaires-et-etrangeres
II - Projection :
On va sélectionner les données à l'aide de la commande SELECT.
Comme vu plus haut, dans un SELECT, il faut au minimum un FROM afin de dire dans quelle table on se situe. L'ordre s'écrit donc :
SELECT <liste des attributs>
FROM <nom des tables>
Pour prendre un exemple, mettons que je veuille récupérer toutes les entrées de la table DEPT, la requête sera la suivante :
SELECT *
FROM dept
J'utilise le joker * afin de dire que je veux tous les attributs de la table. Cependant, il est préférable de ne garder que ce dont on a vraiment besoin lorsque l'on effectue la requête, c'est une question d'optimisation.
SELECT deptno, dname
FROM dept
Ici, je sélectionne uniquement les attributs deptno et dname de la table dept.
Ce qui nous embête ici, et contrairement à l'algèbre relationnelle, C'est que cette requête n'élimine pas les doublons. On va donc ajouter un mot-clé juste après le SELECT, il s'agit de DISTINCT.
On obtient donc ;
SELECT DISTINCT job
FROM emp
Voila la différence :
Sans DISTINCT :
mysql> SELECT job FROM emp;
+-----------+
| job |
+-----------+
| EMPLOYE |
| VENDEUR |
| VENDEUR |
| MANAGER |
| EMPLOYE |
| PRESIDENT |
+-----------+
6 rows in set
Avec Distinct :
SELECT DISTINCT job FROM emp;
+-----------+
| job |
+-----------+
| EMPLOYE |
| VENDEUR |
| MANAGER |
| PRESIDENT |
+-----------+
4 rows in set
Intéressons nous maintenant au FROM, l'utilisation est très simple, mais on va voir comment créer un alias de table.
Concrètement, on va renommer la table provisoirement (le temps de la requête). C'est pour plus de lisibilité et plus de simplicité.
Comment on fait ? Il suffit d'écrire ça :
FROM nomdelatable alias
Ainsi, nous pourrons écrire des requêtes qui ressemblent à ça :
SELECT d.deptno
FROM dept d
Le "d.deptno" peut se traduire par "la colonne deptno de la table d".
Simple non ?
Aller, au suivant !
Maintenant qu'on a renommé une table, on va chercher comment renommer une colonne, toujours provisoirement ! C'est un peu plus subtil, mais rien de bien compliqué.
Ca va se passer comme ça :
SELECT deptno AS Service, dname AS "Nom Service"
FROM dept;
On fait suivre le nom de la table par le mot clé "AS" et ensuite, si c'est un seul mot, pas de guillemets, sinon, on en met.
Note : Le AS est optionnel, on peut uniquement séparer le nom de table de l'alias par un espace.
Regardons le résultat :
mysql> SELECT deptno AS Service, dname AS "Nom Service"
FROM dept;
+---------+--------------+
| Service | Nom Service |
+---------+--------------+
| 1 | Juridique |
| 2 | Recherche |
| 3 | Comptabilité |
| 4 | Support |
+---------+--------------+
4 rows in set
On a bien les colonnes qui sont renommées dans le résultat.
On peut aussi faire des opérations directement dans un SELECT. Attention, on ne modifie pas le contenu de la table, on affiche juste des valeurs !
On retrouve les 4 opérateurs bien connus :
: Addition
: Soustraction
* : Multiplication
/ : Division
Ces opérateurs sont utilisables pour tout attribut numérique ! N'essayez pas de jouer avec les chaînes de caractères.
Exemple :
mysql> SELECT (sal*1.1)+comm FROM emp;
+----------------+
| (sal*1.1)+comm |
+----------------+
| 880 |
| 2060 |
| 2200 |
| 3372.5 |
| 1430 |
| 16000 |
+----------------+
6 rows in set
On va maintenant s'intéresser à comment trier les résultats d'une requête, avec la clause ORDER BY :
Le classement par défaut est ascendant (ASC). Pour inverser, on rajoutera DESC (comme descendant).
Une seule clause ORDER BY et TOUJOURS à la fin du SELECT
Les éléments de tri ne figurent pas forcément dans le SELECT
Les valeurs NULL se trouvent à la fin si on a un tri ASC, où au début en cas de tri DESC
Les éléments de tri peuvent être le nom des colonnes, le numéro des colonnes ou même une expression.
Quelques exemples :
Premier exemple, on trie les résultats par numéro de département, puis par salaire.
mysql> SELECT ename, sal, deptno FROM emp ORDER BY deptno, sal;
+---------+-------+--------+
| ename | sal | deptno |
+---------+-------+--------+
| JONES | 2975 | 2 |
| FORD | 1300 | 3 |
| SMITH | 800 | 4 |
| ALLEN | 1600 | 4 |
| RICARDO | 2000 | 6 |
| KING | 10000 | 6 |
+---------+-------+--------+
6 rows in set
Deuxième exemple, on trie par salaire (du plus petit au plus grand).
mysql> SELECT ename, sal, deptno FROM emp ORDER BY 2;
+---------+-------+--------+
| ename | sal | deptno |
+---------+-------+--------+
| SMITH | 800 | 4 |
| FORD | 1300 | 3 |
| ALLEN | 1600 | 4 |
| RICARDO | 2000 | 6 |
| JONES | 2975 | 2 |
| KING | 10000 | 6 |
+---------+-------+--------+
6 rows in set
2 est le numéro de colonne de sal dans le SELECT (il est a la seconde position)
III - Restriction/sélection :
On commence directement par la clause WHERE. Avec le WHERE, on va réduire le nombre de lignes concernées par la recherche.
Par exemple, on sélectionne tous les employés du service 1 :
SELECT ename, sal
FROM emp
WHERE deptno = 10
Les conditions de recherche sont des expressions logiques vraies ou fausses, en fonction des enregistrements de la table. Une condition peut aussi être appelée prédicat.
Une condition est toujours composée de 3 membres : deux expressions encadrant un opérateur de comparaison.
On peut comparer un attribut avec une valeur constante (comme dans l'exemple), mais aussi deux attributs entre eux.
On retrouve les opérateurs de comparaison usuels à savoir :
Comparaison : =, !=, <>, >, >=, <, <=
Intervalles : BETWEEN
Concordance de caractères : LIKE avec les jokers _ et %
Gestion des colonnes "NULL" : IS NULL
Enumération : IN
Négation : NOT IN, NOT LIKE, NOT BETWEEN, IS NOT NULL
Intéressons-nous aux opérateurs de comparaison suivants : <, >, <= et >=
On peut les utiliser avec les types suivants :
numérique
Alphabétique
Chronologique (avec les dates)
Exemple avec les dates :
SELECT ename, hiredate
FROM emp
WHERE hiredate < '1981-01-01'
-- Le format de la date depend du SGBD
Pour les comparaisons de chaînes de caractères, les lettres majuscules et minuscules sont supérieures aux nombres et la casse peut importer, tout dépend de comment le SGBD est réglé.
Exemple avec les chaînes :
SELECT ename
FROM emp
WHERE ename > 'MARTIN'
Passons au LIKE :
Il est très utilisé, notamment pour tout ce qui est scripts de recherche, le tout est de bien savoir l'utiliser !
Comme vous l'avez vu un peu plus haut, on utilise deux jokers :
_ qui remplace une lettre
% qui remplace tout chaîne de caractère
Peu compréhensible n'est-ce pas ?
Regardons un exemple pour mieux comprendre :
SELECT ename
FROM emp
WHERE ename LIKE ('_AR%')
Que demande cette requête ? On cherche les noms d'employés qui ont pour deuxième lettre A et troisième lettre R dans leur nom de famille. soit :
SELECT ename
FROM emp
WHERE ename LIKE ('_AR%');
+--------+
| ename |
+--------+
| WARD |
| MARTIN |
+--------+
2 rows in set
Déjà un peu plus compréhensible non ? C'est en jouant avec ces deux jokers qu'on arrive à trouver ce qu'on veut.
On va maintenant s'intéresser au IN :
Il s'agit en fait d'une énumération. Seulement, on peut l'utiliser avec différents types.
Par exemple, on sélectionne les employés travaillant dans les services 3 et 4 :
SELECT ename, deptno
FROM emp
WHERE deptno IN (3,4);
+--------+--------+
| ename | deptno |
+--------+--------+
| SMITH | 4 |
| ALLEN | 4 |
| FORD | 3 |
| WARD | 3 |
| MARTIN | 3 |
+--------+--------+
5 rows in set
Les membres sont entre parenthèses et séparés par une virgule, on peut mettre autant de termes qu'on veut.
Le fonctionnement pour le NOT IN est le même. Seulement, on ne prendra que les données dont la valeur du champ n'est pas dans les parenthèses.
BETWEEN et NOT BETWEEN
On recherche un champ dont la valeur est entre deux valeurs. Il faut savoir que les bornes du BETWEEN sont inclues dans la recherche.
Exemple : On recherche les employés qui ont un salaire compris entre 1600 et 3000 :
SELECT ename, sal
FROM emp
WHERE sal BETWEEN 1600 AND 3000;
+---------+------+
| ename | sal |
+---------+------+
| ALLEN | 1600 |
| RICARDO | 2000 |
| JONES | 2975 |
| MARTIN | 1650 |
+---------+------+
4 rows in set
On passe maintenant à l'opérateur de comparaison IS NULL
Il faut savoir quelques petites choses avant de commencer :
Une valeur NULL est une valeur inconnue
NULL <> 0
NULL = valeur non renseignée
On teste par IS NULL et non = NULL
Exemple : Les employés et leur département qui ont une commission NULL
SELECT ename, deptno FROM emp WHERE comm IS NULL;
+--------+--------+
| ename | deptno |
+--------+--------+
| WARD | 3 |
| MARTIN | 3 |
+--------+--------+
2 rows in set
Seulement, une colonne NULL n'est pas très pratique pour faire des calculs. C'est pourquoi chaque SGBD permet de remplacer NULL par une autre valeur. Seulement, cette fonction dépend des SGBD :
Pour Oracle : NVL()
Pour SQL Server : ISNULL()
Pour PostgreSQL : COALESCE()
Pour MySQL : IFNULL()
Dans ce tutoriel, j'utilise MySQL donc je prendrai pour exemple IFNULL().
Regardons :
SELECT ename, sal, comm, sal + comm, sal + IFNULL(comm,0) FROM emp;
+---------+-------+------+------------+----------------------+
| ename | sal | comm | sal + comm | sal + IFNULL(comm,0) |
+---------+-------+------+------------+----------------------+
| SMITH | 800 | NULL | NULL | 800 |
| ALLEN | 1600 | 300 | 1900 | 1900 |
| RICARDO | 2000 | NULL | NULL | 2000 |
| JONES | 2975 | 100 | 3075 | 3075 |
| FORD | 1300 | NULL | NULL | 1300 |
| KING | 10000 | 5000 | 15000 | 15000 |
| WARD | 1500 | NULL | NULL | 1500 |
| MARTIN | 1650 | NULL | NULL | 1650 |
+---------+-------+------+------------+----------------------+
8 rows in set
Pratique non ?
Passons à la suite !
Les opérateurs logiques
Dans une requête SQL, on pourra utiliser des opérateurs logiques afin de lier deux prédicats. Cela permet d'avoir un résultat plus précis avec des conditions simples.
Les principaux opérateurs logiques sont (par ordre d'évaluation) :
NOT
AND
OR
Ils s'utilisent comme cela :
SELECT * FROM emp WHERE deptno = 1 AND sal > 1000;
Il est plus que conseillé d'utiliser des parenthèses pour "imposer" l'ordre d'exécution des opérateur logiques.
On arrive à la fin de ce chapitre sur la restriction et la sélection des données. Le prochain chapitre sur les jointures sera plus pointu, et plus complet. N'hésitez pas à relire plusieurs fois certains points, quitte à demander un éclaircissement sur quelque chose que vous n'auriez pas compris.
Avant de passer au prochain chapitre, je vais ajouter un dernier point :
Le résultat d'un SELECT est en fait une relation. On pourra donc utiliser le résultat d'un SELECT dans plusieurs cas :
Sous requête
Jointures
UNION (et opérations ensemblistes)
Voila, le chapitre est terminé, on passe à la suite !
IV - Jointures
Passons au point le plus marrant de ce tutoriel, et ce sera le dernier. Écoutez bien, les jointures sont des notions importantes pour bien manipuler une base de données !
En effet, les jointures vont nous permettre de mettre en corrélation plusieurs tables au sein d'une même requête, donc en économisant (si c'est bien fait) des ressources système et sans avoir besoin de traitement dans un autre langage (tel que PHP, par exemple).
Il existe trois "grandes familles" de jointures :
Le produit cartésien
Les jointures naturelles
Les théta-jointures (ou équi-jointures)
Je vais tous vous les présenter, mais celui qui nous intéresse est bien évidemment le dernier.
Le produit cartésien : Il s'agit d'une opération entre les relations qui combine toutes les occurrences. C'est à dire que le nombre de lignes du résultat est égal au produit du nombre de lignes de chacune des relations. Autrement dit, si on a une table qui contient 3 entrées, et qu'on fait un produit cartésien avec une autre table qui en contient 4, le résultat fera 12 lignes.
Exemple :
mysql> SELECT *
-> FROM lettre,chiffre;
+--------+---------+
| lettre | chiffre |
+--------+---------+
| a | 1 |
| b | 1 |
| c | 1 |
| a | 2 |
| b | 2 |
| c | 2 |
| a | 3 |
| b | 3 |
| c | 3 |
| a | 4 |
| b | 4 |
| c | 4 |
+--------+---------+
12 rows in set
Vous avouerez que, quand on manipule de grandes bases de données, cette méthode est très gourmande en ressources et en plus, il est très probable que nous n'utilisions pas tous les résultats.
La jointure naturelle : En fait, ce que nous allons appeler jointure peut s'apparenter à un produit cartésien suivi d'une restriction, puis d'une projection. On va combiner des relations ayant des valeurs en commun. Dès maintenant, nous allons beaucoup utiliser les alias afin de simplifier un petit peu nos requêtes. Ne soyez donc pas étonnés lorsque j'en utilise, c'est devenu une habitude.
Dès maintenant, nous utiliserons aussi la syntaxe SQL 2 qui introduit justement ces différents types de jointures. Avant, les jointures n'étaient en fait que des produits cartésiens.
Trêve de papotages futiles, voici comment s'écrit une jointure naturelle :
SELECT deptno, ename
FROM dept
NATURAL JOIN emp
-- Fonctionne sous MySQL, Oracle et PostgreSQL mais pas sous MS SQL Server
En fait, une jointure naturelle cherche des champs avec le même nom afin de les mettre en commun. C'est dangereux à utiliser si des colonnes portant le même nom ne se réfèrent pas aux mêmes données. Je vous déconseille de l'utiliser, sauf si vous êtes surs de la base de données que vous utilisez et qu'elle est bien modélisée.
La thêta-jointure : C'est le type de jointure le plus souvent utilisé. Nous sommes vraiment maîtres de ce que l'on recherche. La syntaxe est simple à comprendre :
-- Nom et salaire des employés gagnant plus que l'employé 7566
SELECT e1.ename, e1.sal
FROM emp e1
JOIN emp e2 ON e1.sal > e2.sal -- On fait la jointure avec la condition
WHERE e2.empno = 7566
Plutôt simple non ?
Pour l'équi-jointure, c'est le même principe, juste la condition change !
-- On cherche le nom de chaque employé avec le nom de son département
SELECT e.ename, d.dname
FROM emp e
JOIN dept d ON e.deptno=d.deptno
Alors, convaincus ? Ne vous inquiétez pas, j'ai bientôt fini ! Je vous présente juste une dernière notion
Left/right join : En fait,une jointure élimine par défaut les résultats ou il n'y a pas de correspondance (exemple, si un employé n'a pas de département, il ne sera pas affiché). Pour pallier ce problème, il faut dire à SQL de garder les champs à gauche, même si il n'y a pas de correspondance.
Exemple :
mysql> SELECT ename, e.deptno, dname
-> FROM emp e
-> LEFT JOIN dept d ON e.deptno=d.deptno;
+---------+--------+--------------+
| ename | deptno | dname |
+---------+--------+--------------+
| JONES | 2 | Recherche |
| FORD | 3 | Comptabilité |
| WARD | 3 | Comptabilité |
| MARTIN | 3 | Comptabilité |
| SMITH | 4 | Support |
| ALLEN | 4 | Support |
| KING | 6 | HeadOffice |
| RICARDO | NULL | NULL |
+---------+--------+--------------+
8 rows in set
Ici, on a dit : "Même si il n'y a pas de correspondance, tu gardes les membres étant à gauche (LEFT) de la jointure" et Ricardo est apparu. C'est exactement le même principe pour RIGHT, tout dépend de comment vous construisez vos requêtes.
Voilà, je pense avoir fait le tour pour ce tutoriel. N'hésitez pas à réagir, contester, demander des éclaircissements...
Bon courage dans vos requêtes SQL
Merci d'avoir lu,
Pirkoa
