Débuter SQL avec SQLite3 - Partie 2
Pour continuer avec cette seconde partie, il est important d'avoir bien compris la première partie. Nous allons utiliser la même base de données, si vous l'avez supprimé, n'hésitez pas à reprendre le premier article pour la re-créer. Et si vous vous êtes entrainé, et que vous avez ajouté des lignes dans la base, ce n'est pas un soucis, c'est même très bien. A noter que les résultats des commandes que nous allons voir aujourd'hui peuvent varier selon le nombre de ligne que vous avez ajouté.
Mais, ne perdons pas plus de temps, et commençons !
Dans un terminal, lancez l'application sqlite3 avec le chemin vers votre base de données.
Nous l'avons vu dans la première partie, la commande select renvoie un tableau complet de toutes les colonnes.
SELECT * FROM contacts;
Il est cependant possible de n'obtenir que la ou les colonnes que l'on souhaite. En effet, si nous souhaitons par exemple, n'avoir que les adresses email, nous pouvons demander
SELECT email FROM contacts;
La réponse attendue est
ironman@avengers.assembly
pepper@avengers.assembly
antman@avengers.assembly
wasp@avengers.assembly
Il est aussi possible, de demander plusieurs colonne, ainsi si l'on veut savoir à quoi les adresses email correspondent, nous pouvons aussi demander les identifiants
SELECT id, email FROM contacts;
Cette fois-ci, la réponse est
1|ironman@avengers.assembly
2|pepper@avengers.assembly
3|antman@avengers.assembly
4|wasp@avengers.assembly
Nous pouvons aller plus loin en demandant les noms et prénoms en plus
SELECT id, prenom, nom, email FROM contacts;
avec la réponse
1|Tony|Stark|ironman@avengers.assembly
2|Pepper|Pots|pepper@avengers.assembly
3|Scott|Lang|antman@avengers.assembly
4|Hope|van Dyne|wasp@avengers.assembly
Notez que les colonnes dans la réponse sont dans le même ordre que celui indiqué dans la commande SQL. Vous pouvez confirmer cela en modifiant votre commande et tester avec un ordre différent.
Finalement, l'étoile * de notre toute première commande select voulait simplement dire "toutes les colonnes".
La commande SELECT permet d'aller un peu plus loin. Dans nos colonnes nous avons bien un nom et un prénom, ce qui est très pratique, nous verrons pourquoi un peu plus tard dans l'article.
Mais pour nos besoins, il peut être nécessaire d'avoir les deux colonnes regroupées au sein de nos résultats. Le langage SQL prévoit une fonction nommée CONCAT qui permet de faire cela, mais SQLite ne l'utilise pas et propose quelque chose de différent. Voici comment cela doit être indiqué dans SQLite
SELECT id, prenom || ' ' || nom, email FROM contacts;
Il faut tout simplement mettre les différentes colonnes séparée par un double pipe (||), si vous n'aviez jamais utilisé ce caractère, sachez que sur Mac, nous l'obtenons avec alt+maj+L.
Voici le résultat
1|Tony Stark|ironman@avengers.assembly
2|Pepper Pots|pepper@avengers.assembly
3|Scott Lang|antman@avengers.assembly
4|Hope van Dyne|wasp@avengers.assembly
Sur cette dernière commande, vous devriez avoir noté 2 choses. La première c'est que la virgule entre nom et prenom a disparu. Comme il s'agit du séparateur de colonnes, c'est bien normal, puisque nous souhaitons un résultat groupé. La seconde chose est que nous avons ajouté un espace vide entre le prénom et nom. Je vous laisse imaginer ce qu'il se passe si nous ne l'ajoutons pas. Vous pouvez aussi le tester.
Lorsque nous insérons des contacts dans un carnet d'adresses, nous ne le faisons jamais dans l'ordre alphabétique. En général, nous le faisons au fur et à mesure des informations que nous recevons. Cependant, pour retrouver les informations dans une liste, il est toujours plus pratique de les avoir dans l'ordre alphabétique.
Voici comment trier nos lignes dans l'ordre souhaité.
SELECT * FROM contacts ORDER BY nom ASC;
et le résultat souhaité
3|Scott|Lang|antman@avengers.assembly|Inconnu|||10471|New York City
2|Pepper|Pots|pepper@avengers.assembly|Inconnu|Tour Stark|Dernier étage|10001|New York City
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 indexes ne sont plus dans l'ordre, cependant, nous avons bien Lang avant Pots, Pots avant Stark et ainsi de suite. Telle quelle, la liste n'est pas vraiment lisible.
Peut être pouvons nous combiner ce que nous avons vu précédemment ?
SELECT nom, prenom FROM contacts ORDER BY nom ASC;
voir
SELECT nom || ' ' || prenom FROM contacts ORDER BY nom ASC;
Le résultat est plutôt chouette, non ?
Si vous souhaitez les avoir dans l'ordre inverse, c'est aussi possible, il suffit de remplacer ASC (ascendant) par DESC (descendant).
SELECT nom || ' ' || prenom FROM contacts ORDER BY nom DESC;
Pour les besoins de notre exercice, ajoutons une ligne :
INSERT INTO contacts (
prenom,
nom,
email,
telephone,
adresse_voie,
adresse_complement,
adresse_code,
adresse_ville
) VALUES (
'Howard',
'Stark',
'',
'',
'',
'',
'',
'New York City'
);
J'imagine que vous l'avez compris, le but est d'avoir une seconde personne disposant du même nom de famille. Regardons maintenant comme faire pour trier nos contacts par nom de famille, puis par prénom et ainsi obtenir une liste parfaitement ordonnée.
SELECT nom || ' ' || prenom FROM contacts ORDER BY nom ASC, prenom ASC;
Il suffit tout simplement d'ajouter une nouvelle colonne dans notre tri, une fois de plus séparée par une virgule.
Lang Scott
Pots Pepper
Stark Howard
Stark Tony
van Dyne Hope
Attaquons une nouvelle fonction pour obtenir des listes un peu différentes. Pour le besoin de notre exercice, nous allons ajouté un nouveau contact
INSERT INTO contacts (
prenom,
nom,
email,
telephone,
adresse_voie,
adresse_complement,
adresse_code,
adresse_ville
) VALUES (
'James',
'Rhodes',
'warmachine@avengers.assembly',
'',
'Base militaire',
'',
'93523',
'Edwards'
);
Imaginons un petit instant, que nous souhaitons retrouver les villes où habite l'ensemble de nos contacts. Avec nos connaissances actuelles, nous pourrions simplement demander
SELECT adresse_ville FROM contacts;
avec le résultat suivant
New York City
New York City
New York City
New York City
New York City
Edwards
C'est sympa mais New York City revient tout de même pas mal de fois. Imaginons, une fois de plus que nous avons 100 ? 1000 ? 10000 lignes dans nos contacts ?
Ce serait vite le bazar. Heureusement SQL dispose d'une fonction qui permet de grouper les résultat, regardons immédiatement comment faire
SELECT adresse_ville FROM contacts GROUP BY adresse_ville;
Le résultat obtenu est une liste réduite, bien plus lisible.
Edwards
New York City
Evidemment, nous pouvons ajouter un ordre, si nous le souhaitons :
SELECT adresse_ville FROM contacts GROUP BY adresse_ville ORDER BY adresse_ville ASC;
Ce qui ne change pas grand chose par rapport à notre résultat précédent, car la liste n'est pas très longue.
Plus subtil, nous pouvons nous servir de ce GROUP BY pour savoir combien de personnes dans nos contacts habite dans chacune des villes.
SELECT COUNT(id), adresse_ville FROM contacts GROUP BY adresse_ville;
Et voici le résultat.
1|Edwards
5|New York City
Nous sommes désormais catégorique quant au fait que 5 personnes habite New York et une seule habite Edwards.
Poussons le vice un peu plus loin, en ordonnant ce résultat par ordre décroissant.
SELECT COUNT(id), adresse_ville FROM contacts GROUP BY adresse_ville ORDER BY COUNT(id) DESC;
Et hop, le résultat est comme attendu
5|New York City
1|Edwards
C'est magique, non ?
Par contre, la lecture de la commande est un peu lourde, avoir une fonction en tant qu'ordre peut brouiller la clarté de l'ensemble. SQL permet d'utiliser des alias pour les colonnes renvoyées, ce qui est vraiment très pratique lorsque nous demandons un résultat avec fonction telle qu'une concaténation, vue en début d'article, ou encore ici, avec une fonction de comptage.
Voici comment l'utiliser, je donne le nom compte à ma colonne qui reverra le résultat de comptage. L'avantage, c'est que je peux ensuite m'en servir avec ORDER BY.
SELECT COUNT(id) as compte, adresse_ville FROM contacts GROUP BY adresse_ville ORDER BY compte DESC;
Le résultat de la commande lui, restera parfaitement le même.
L'alias peut aussi être utilisé sur une colonne de donnée directe, par exemple
SELECT COUNT(id) as compte, adresse_ville as ville FROM contacts GROUP BY ville ORDER BY compte DESC;
Nous renommons la colonne adresse_ville en ville, pour simplifier son utilisation dans le GROUP BY.
Toujours dans un but de lisibilité, il est possible d'obtenir des fragments de liste. Cela se fait avec la clause LIMIT.
En usage simple, elle permet d'obtenir les x premiers éléments d'une liste. Par exemple, récupérons les 2 premières lignes, en gardant les ordres alphabétiques.
SELECT id, nom || ' ' || prenom, email FROM contacts ORDER BY nom ASC, prenom ASC LIMIT 2;
Le résultat obtenu contient bien deux lignes
3|Lang Scott|antman@avengers.assembly
2|Pots Pepper|pepper@avengers.assembly
Maintenant, regardons comment obtenir les 2 lignes suivantes
SELECT id, nom || ' ' || prenom, email FROM contacts ORDER BY nom ASC, prenom ASC LIMIT 2 OFFSET 2;
dont voici le résultat
6|Rhodes James|warmachine@avengers.assembly
5|Stark Howard|
La valeur d'OFFSET va donner la position de départ, et LIMIT conservera le nombre maximal de ligne à retourne. Ainsi si je veux la dernière portion de ma liste
SELECT id, nom || ' ' || prenom, email FROM contacts ORDER BY nom ASC, prenom ASC LIMIT 2 OFFSET 4;
avec nos deux derniers contacts
1|Stark Tony|ironman@avengers.assembly
4|van Dyne Hope|wasp@@avengers.assembly
Il existe une écriture courte qui permet de se passer du mot OFFSET, mais il faut bien faire attention, car, dans ce cas, ils sont écrits dans l'ordre inverse, à savoir que l'OFFSET est indiqué avant la LIMIT
SELECT id, nom || ' ' || prenom, email FROM contacts ORDER BY nom ASC, prenom ASC LIMIT 0, 4;
avec cette commande, je demande les 4 lignes à partir de 0, donc en résumé les 4 premières lignes. Pour avoir les 4 suivantes, je demanderai
SELECT id, nom || ' ' || prenom, email FROM contacts ORDER BY nom ASC, prenom ASC LIMIT 4, 4;
A noter que ce résultat ne renvoit que 2 lignes. En effet, nous n'avons que 6 lignes dans notre table, et donc, la base ne renverra que les résultats qui concordent. Si vous demandiez les 4 lignes suivantes
SELECT id, nom || ' ' || prenom, email FROM contacts ORDER BY nom ASC, prenom ASC LIMIT 8, 4;
Le résultat sera tout simplement vide.
N'hésitez pas à faire d'autres essais avec les connaissances que vous avez acquises. Jouer avec la commande SELECT est totalement sans risque, puisqu'elle ne permet que de lire les données, et ne sera jamais capable d'écrire.
Cela vous a plu ? Dans le prochain épisode, nous verrons comment aller encore plus loin, avec la clause WHERE qui permet de multiplier les possibilités.
