Débuter SQL avec SQLite3 - Partie 3
Comme indiqué à la fin de l'article précédent, il est temps de s'attaquer à la clause WHERE. Cette clause permet d'appliquer à une commande UPDATE ou SELECT, un filtre et de ne retourner qu'une partie des résultats.
Nous l'avions vu brièvement dans le premier article, lorsque nous avions vu la commande UPDATE. Cela nous avait permis de ne mettre à jour que les données de la ligne souhaitée.
Il s'agissait d'un test tout simple mais qui représente en général la majorité des cas, notamment lorsque l'on commence avec SQL.
Aujourd'hui, nous allons d'abord voir l'utilisation de WHERE sur la commande SELECT. Cela va nous permettre de récupérer des résultats optimisés pour nos besoins.
Commençons !
1/ Les opérateurs
Donc comme vu, précédemment WHERE va nous permettre de récupérer une ligne dont on connait l'identifiant.
SELECT * FROM contacts WHERE id=1;
nous renverra le contenu de notre ligne identifié par la valeur 1
1|Tony|Stark|ironman@avengers.assembly|Inconnu|Tour Stark|Dernier étage|10001|New York City
WHERE peut s'appliquer à d'autres colonnes que l'identifiant évidemment. Le test d'égalité s'applique aussi aux colonnes de texte. Par exemple, pour récupérer les habitants de New York City, nous pouvons faire un test sur la colonne adresse_ville
SELECT * FROM contacts WHERE adresse_ville="New York City";
Comme vous pouvez le voir, cela n'empêche pas la commande SELECT de renvoyer plusieurs résultats.
Dans le premier test, nous demandions de nous renvoyer le résultat pour un identifiant unique par ligne. Le résultat était donc, de fait, limité à une seule ligne.
Si, nous voulions limiter ce résultat à une seule ligne ou à un nombre donné de lignes, il faudrait combiner la clause WHERE avec la clause LIMIT vue précédemment.
Par exemple, pour retrouver la première "page" de 4 éléments de notre carnet d'adresses, mais uniquement pour les habitants de New York City, nous aurons :
SELECT id, nom || ' ' || prenom, email FROM contacts WHERE adresse_ville="New York City" ORDER BY nom ASC, prenom ASC LIMIT 0, 4;
D'autres tests que l'égalité sont disponibles, à commencer par la différence. Tout simplement, cela va nous renvoyer les éléments contraires si l'on l'applique aux commandes déjà testées.
Commençons par la première
SELECT * FROM contacts WHERE id!=1;
Ici, nous nous retrouvons avec toutes les lignes de notre table exceptée la première.
Une fois de plus cela s'applique aussi aux textes
SELECT * FROM contacts WHERE adresse_ville!="New York City";
Et hop, nous nous retrouvons avec toutes les lignes dont les habitants n'habitent pas New York City.
Nous avons ensuite deux tests mathématiques qui ne s'appliquent que sur les valeurs numériques (et les dates, mais c'est une autre histoire). Tester la supériorité ou l'infériorité d'une valeur peut être utile.
Notre table exemple n'est pas très bien choisie, dans le sens où il n'y a aucune valeur numérique exceptée l'identifiant. J'avoue que je n'avais pas prévue jusqu'ici, mais qu'à cela ne tienne, nous allons continuer à travailler avec les identifiants.
Imaginons que nous souhaitons récupérer tous les identifiants dont la valeur est inférieure à 4. Voici notre commande SQL
SELECT id FROM contacts WHERE id < 4;
Vous devriez avoir en tête le résultat obtenu
1
2
3
Petite alternative, pour obtenir tous les identifiants inférieurs ou égaux à 4.
SELECT id FROM contacts WHERE id <= 4;
Encore une fois, vous ne devriez pas être surpris du résultat
1
2
3
4
Il est évidemment possible de faire la même chose avec l'opérateur "supérieur à"
SELECT id FROM contacts WHERE id > 4;
ou "supérieur ou égal à"
SELECT id FROM contacts WHERE id >= 4;
Enfin, il est possible de chercher des éléments depuis une liste de valeurs. Ainsi je souhaite retrouver les lignes complètes dont les ids sont 2, 4 et 5. Je peux utiliser la commande
SELECT * FROM contacts WHERE id IN (2,4,5);
Le résultat correspond bien à ce que l'on attend
2|Pepper|Pots|pepper@avengers.assembly|Inconnu|Tour Stark|Dernier étage|10001|New York City
4|Hope|van Dyne|wasp@avengers.assembly|Inconnu|||10470|New York City
5|Howard|Stark||||||New York City
Cet opérateur IN fonctionne aussi avec les chaînes de caractères. Si je cherche toutes les personnes dont le nom est "Stark" ou "Pots", cela donne cela.
SELECT * FROM contacts WHERE nom IN ('Stark', 'Pots');
Et le résultat est, une nouvelle fois, adapté à notre demande
1|Tony|Stark|ironman@avengers.assembly|Inconnu|Tour Stark|Dernier étage|10001|New York City
2|Pepper|Pots|pepper@avengers.assembly|Inconnu|Tour Stark|Dernier étage|10001|New York City
5|Howard|Stark||||||New York City
Concernant les textes, il existe un autre opérateur vraiment très pratique, il s'agit de LIKE. Il permet une recherche sur une portion de texte.
Reprenons notre exemple, et imaginons que notre carnet d'adresses est plein à craquer. Si l'on souhaite récupérer tous les contacts par lettre, commençant par la lettre A. On indiquera
SELECT * FROM contacts WHERE nom LIKE "A%";
Encore une fois, l'exemple n'est pas forcément adapté, puisque nous n'avons aucun contact en A. Avançons jusqu'à la lettre L
SELECT * FROM contacts WHERE nom LIKE "L%";
pour obtenir
3|Scott|Lang|antman@avengers.assembly|Inconnu|||10471|New York City
Le caractère % indique n'importe quelle lettre ou suite de lettres. Il est possible de le placer au début pour obtenir toutes les valeurs qui finissent par notre recherche. Et notre recherche ne se limite pas forcément à une lettre. Regardons par exemples toutes les adresses e-mails dont le TLD est ".assembly"
SELECT * FROM contacts WHERE email LIKE "%.assembly";
Et hop, le tour est joué. Il est possible de chercher une chaine de lettre au milieu d'un texte aussi, en utilisant 2 fois le caractère %. Pour trouver tous les noms qui contiennent un a, je peux écrire
SELECT * FROM contacts WHERE nom LIKE "%a%";
Plus compliqué, regardons tous les e-mails qui sont bien formatés. Pour cela nous allons considérer qu'une adresse e-mail qui contient un texte, puis un @, encore un texte, puis un point et pour finir un texte est valide.
SELECT * FROM contacts WHERE email LIKE "%@%.%";
Si la vérification en elle-même est un peu légère, la commande, elle, revoit exactement cela que l'on souhaite. Comme cela, il n'y a peu d'intérêt, qu'en pensez vous ? Par contre, obtenir les personnes dont les adresses ne sont pas valides, pour leur demander une nouvelle par exemple, peut le devenir. Comment écrire l'inverse de ça ? Ce n'est tout simplement pas possible, par contre, il est tout à fait faisable d'inverser toute la demande avec le mot-clé NOT. Dans notre exemple, ce sera
SELECT * FROM contacts WHERE email NOT LIKE "%@%.%";
Et voilà.
Attention, ici notre table ne dispose que de quelques lignes, et nos textes sont vraiment petits. Utiliser LIKE sur une table disposant d'un grand nombre de lignes sur des textes bien plus grands peut prendre plus de temps. Cela reste tout de même relatif, puisque par exemple le moteur de recherche du site utilise l'opérateur LIKE à plusieurs reprises pour trouver les résultats les plus pertinents. Malgré la longueur des articles et leur quantité, la page est calculée plutôt rapidement. A titre d'exemple, la page d'accueil, qui est la plus complexe en dehors du moteur de recherche, prend environ 15ms pour être créée. Une recherche d'un seul mot prend environ 80ms, avec 2 mots, on passe à 110ms, 130ms avec 3. Même si le résultat est très rapide, vous pouvez voir que les performances ont drastiquement chuté. Si le site subissait les assauts de milliers de personnes effectuants toutes recherches sur recherches, le site serait bien moins réactif.
2/ Tests combinés et complexes
Jusqu'ici nous n'avons toujours fais qu'un seul test à la fois pour obtenir nos résultats. Mais pour aller encore plus loin et obtenir des résultats encore plus précis, il est possible de combiner les tests au sein d'une seule requête. Pour cela nous allons utiliser les opérateurs AND et OR. Ces opérateurs binaires sont identiques à ceux utilisés en mathématique.
L'opérateur AND, qui veut dire et en anglais, va s'assurer que nos deux tests sont validés pour renvoyer le résultat. Par exemple, si je cherche la personne qui habite à la tour Stark et dont le nom n'est pas Stark, j'écris
SELECT * FROM contacts WHERE adresse_voie = "Tour Stark" AND nom != "Stark";
J'obtiens uniquement la ligne de Pepper.
2|Pepper|Pots|pepper@avengers.assembly|Inconnu|Tour Stark|Dernier étage|10001|New York City
Si j'utilise le OR, il suffit que l'une ou l'autre des conditions soit valide pour que la ligne soit retournée.
SELECT * FROM contacts WHERE adresse_voie = "Tour Stark" OR nom != "Stark";
Cette fois, j'obtiens plus de résultats, puisque seul Howard n'apparait pas. En effet, tout les autres habitent à la tour Stark comme Tony et Pepper, ou ne s'appelle pas Stark comme Pepper qui valide les deux et c'est bien ce que l'on a confirmé avec la requête en AND, mais aussi Scott, Hope et James.
1|Tony|Stark|ironman@avengers.assembly|Inconnu|Tour Stark|Dernier étage|10001|New York City
2|Pepper|Pots|pepper@avengers.assembly|Inconnu|Tour Stark|Dernier étage|10001|New York City
3|Scott|Lang|antman@avengers.assembly|Inconnu|||10471|New York City
4|Hope|van Dyne|wasp@avengers.assembly|Inconnu|||10470|New York City
6|James|Rhodes|warmachine@avengers.assembly||Base militaire||93523|Edwards
Il est possible de cumuler plus que deux tests, mais il faut garder en mémoire, que comme en mathématique, le AND prends l'ascendant sur le OR. Et comme en mathématique, il faut ajouter des parenthèses pour conserver l'ordre des tests.
Reprenons un exemple, si je souhaite faire ressortir les habitants de New York dont le code postal est 10470 ou 10471, je demanderai
SELECT * FROM contacts WHERE adresse_ville = "New York City" AND (adresse_code = 10470 or adresse_code = 10471);
Si vous commencez à penser que les possibilités sont infinies, et bien dites vous que vous avez raison, mais que vous n'êtes qu'au début de vos surprises.
Il est possible, en plus de tout cela, d'ajouter des fonctions à nos conditions. Par exemple, si pour une raison donnée, j'ai besoin de ressortir tous mes contacts dont le prénom fait 4 lettres, je peux lancer la requête
SELECT * FROM contacts WHERE LENGTH(prenom) = 4;
avec un résultat toujours aussi précis
1|Tony|Stark|ironman@avengers.assembly|Inconnu|Tour Stark|Dernier étage|10001|New York City
4|Hope|van Dyne|wasp@avengers.assembly|Inconnu|||10470|New York City
Les fonctions sont nombreuses, nous n'aurons pas le temps de toutes les voir ici. Cependant, il est bien de conserver cette idée en tête car, cela vous servira forcément dans le futur, si vous continuer à tripatouiller du SQL.
3/ Tester avant d'appliquer
Nous l'avons vu dans le premier article, avec la commande UPDATE, une fois la commande lancée, il n'y a pas de retour en arrière. Une bonne pratique peut être de tester avant avant d'executer. En effet, avant de lancer votre commande UPDATE, pourquoi ne pas faire un SELECT avec les mêmes paramètres afin de s'assurer que l'on va bien mettre à jour les bonnes lignes.
Ainsi si l'on veut prendre en compte le déménagement de Tony et Pepper dans leur chalet près du lac, autant vérifier que nous avons les bonnes lignes.
Par exemple, si je change l'adresse de tous les Stark et de tous les Pots, que se passerait il ? Vérifions avant de lancer la commande
SELECT * FROM contacts WHERE nom="Stark" or nom="Pots";
Howard, le père de Tony ressort, si j'avais lancé la commande avec ces paramètres, j'aurais donc changer une adresse par erreur.
1|Tony|Stark|ironman@avengers.assembly|Inconnu|Tour Stark|Dernier étage|10001|New York City
2|Pepper|Pots|pepper@avengers.assembly|Inconnu|Tour Stark|Dernier étage|10001|New York City
5|Howard|Stark||||||New York City
Par défaut, la solution la plus viable est toujours de passer par les identifiants si nous les connaissons, ainsi la commande suivante
SELECT * FROM contacts WHERE id=1 or id=2;
permet de vérifier que nous sommes OK.
1|Tony|Stark|ironman@avengers.assembly|Inconnu|Tour Stark|Dernier étage|10001|New York City
2|Pepper|Pots|pepper@avengers.assembly|Inconnu|Tour Stark|Dernier étage|10001|New York City
nous pouvons appliquer notre modification en conservant le même paramètres de la clause WHERE
UPDATE contacts SET adresse_voie="Chalet", adresse_complement="", adresse_ville="Fairburn", adresse_code="30213" WHERE id=1 or id=2;
Si nous faisons un petit SELECT de toute notre table, nous obtenons
1|Tony|Stark|ironman@avengers.assembly|Inconnu|Chalet||30213|Fairburn
2|Pepper|Pots|pepper@avengers.assembly|Inconnu|Chalet||30213|Fairburn
3|Scott|Lang|antman@avengers.assembly|Inconnu|||10471|New York City
4|Hope|van Dyne|wasp@@avengers.assembly|Inconnu|||10470|New York City
5|Howard|Stark||||||New York City
6|James|Rhodes|warmachine@avengers.assembly||Base militaire||93523|Edwards
4/ La commande DELETE
Je ne vais pas tourner autour du pot, je vous ai un peu menti lors du premier article. Il n'y a pas 4 commandes principales, mais bien 5. Et oui, ajouter des données, c'est bien. Mais il faut aussi pouvoir en retirer. Cependant, je souhaitais prendre le temps de vous montrer le principe de vérification du précédent chapitre, car une suppression non vérifiée peut amener à la catastrophe.
La commande DELETE permet de supprimer une ou plusieurs lignes, sans confirmation ni possibilité d'annuler. Il faut donc l'utiliser avec précaution, et évidemment vérifier avant que nous allons supprimer la bonne ligne.
Regardons pour supprimer l'entrée d'Howard Stark de notre base. Comme il est décédé, nous n'avons plus besoin de conserver ses informations.
Veuillez noter que j'ai bien parlé d'Howard et pas de Tony, car je sais que le sujet est sensible auprès de certains fans de la licence. Fin d'aparté et revenons à nos moutons.
SELECT * FROM contacts WHERE nom="Stark" AND prenom="Howard";
le résultat est sans appel
5|Howard|Stark||||||New York City
Nous pouvons effectuer notre commande DELETE sans risque maintenant que nous avons validé notre ligne.
DELETE FROM contacts WHERE nom="Stark" AND prenom="Howard";
A noter que le résultat précédent, nous donnait l'identifiant de la ligne. Et donc, même si nous ne le connaissions pas avant, nous aurions pu l'utiliser puisqu'il nous était renvoyé, tel que
DELETE FROM contacts WHERE id=5;
A partir d'ici, vous devriez avoir toutes les bases pour gérer de petites bases SQLite. Il reste cependant beaucoup à découvrir, et le prochain article traitera des jointures, qui permettent d'obtenir des données de plusieurs tables, avec une seule requête. Tout un programme !
