SQLite Requête : Sélectionner, Où, LIMITE, DÉCALAGE, Compter, Regrouper par

Les sections ci-dessous expliquent en détail la lecture des données avec SELECT et FROM, le filtrage des lignes avec WHERE et ses opérateurs, le tri et la limitation des résultats, les fonctions d'agrégation, GROUP BY et HAVING, les sous-requêtes, les opérations ensemblistes, la gestion des valeurs NULL, les expressions CASE et les expressions de table communes.

Écrire requêtes SQL dans un SQLite base de données, vous devez savoir comment fonctionnent les clauses SELECT, FROM, WHERE, GROUP BY, ORDER BY et LIMIT et comment les utiliser.

Au cours de ce tutoriel, vous apprendrez à utiliser ces clauses et à écrire SQLite clauses.

Lecture de données avec Select

La clause SELECT est l'instruction principale que vous utilisez pour interroger un SQLite base de données. Dans la clause SELECT, vous indiquez ce qu'il faut sélectionner. Mais avant la clause select, voyons d'où nous pouvons sélectionner des données à l'aide de la clause FROM.

La clause FROM est utilisée pour spécifier où souhaitez-vous sélectionner les données. Dans la clause from, vous pouvez spécifier une ou plusieurs tables ou sous-requêtes dans lesquelles sélectionner les données, comme nous le verrons plus loin dans les didacticiels.

Notez que, pour tous les exemples suivants, vous devez exécuter sqlite3.exe et ouvrir une connexion à l'exemple de base de données de manière fluide :

Étape 1) Dans cette étape,

Ouvrez « Ordinateur » et accédez au répertoire suivant : « C:\sqlite », puis ouvrez « sqlite3.exe ».

Étape 2) Ouvrez la base de données « TutorialsSampleDB.db » à l’aide de la commande suivante :

Vous êtes maintenant prêt à exécuter n’importe quel type de requête sur la base de données.

Dans la clause SELECT, vous pouvez sélectionner non seulement un nom de colonne, mais vous disposez de nombreuses autres options pour spécifier ce que vous souhaitez sélectionner. Comme suit:

SELECT *

Cette commande sélectionnera toutes les colonnes de toutes les tables (ou sous-requêtes) référencées dans la clause FROM. Par exemple:

SELECT *
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Cela sélectionnera toutes les colonnes des tables des étudiants et des tables des départements :

SELECT nom de la table.*

Cela sélectionnera toutes les colonnes de la table « tablename » uniquement. Par exemple:

SELECT Students.*
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Cela sélectionnera uniquement toutes les colonnes de la table des étudiants :

Une valeur littérale

Une valeur littérale est une valeur constante qui peut être spécifiée dans l'instruction select. Vous pouvez normalement utiliser des valeurs littérales de la même manière que vous utilisez les noms de colonnes dans la clause SELECT. Ces valeurs littérales seront affichées pour chaque ligne parmi les lignes renvoyées par la requête SQL.

Voici quelques exemples de différentes valeurs littérales que vous pouvez sélectionner :

• Littéral numérique – nombres dans n’importe quel format comme 1, 2.55,… etc.
• Littéraux de chaîne – Toute chaîne « USA », « ceci est un exemple de texte »,… etc.
• NULL – valeur NULLe.
• Current_TIME – Il vous donnera l'heure actuelle.
• CURRENT_DATE – cela vous donnera la date actuelle.

Cela peut être pratique dans certaines situations où vous devez sélectionner une valeur constante pour toutes les lignes renvoyées. Par exemple, si vous souhaitez sélectionner tous les étudiants de la table Étudiants, avec une nouvelle colonne appelée pays qui contient la valeur « USA », vous pouvez faire ceci :

SELECT *, 'USA' AS Country FROM Students;

Cela vous donnera toutes les colonnes des étudiants, plus une nouvelle colonne « Pays » comme celle-ci :

Notez que cette nouvelle colonne Pays n’est pas réellement une nouvelle colonne ajoutée au tableau. Il s'agit d'une colonne virtuelle, créée dans la requête pour afficher les résultats et elle ne sera pas créée sur la table.

Noms et pseudonymes

L'alias est un nouveau nom pour la colonne qui vous permet de sélectionner la colonne avec un nouveau nom. Les alias de colonnes sont spécifiés à l'aide du mot-clé « AS ».

Par exemple, si vous souhaitez sélectionner la colonne StudentName à renvoyer avec « Student Name » au lieu de « StudentName », vous pouvez lui donner un alias comme celui-ci :

SELECT StudentName AS 'Student Name' FROM Students;

Cela vous donnera les noms des étudiants avec le nom « Student Name » au lieu de « StudentName », comme ceci :

Notez que le nom de la colonne reste « StudentName » ; la colonne StudentName demeure inchangée, elle ne subit aucune modification due à l’alias.

L'alias ne changera pas le nom de la colonne ; cela changera simplement le nom d'affichage dans la clause SELECT.

Notez également que le mot-clé « AS » est facultatif, vous pouvez mettre le nom d'alias sans celui-ci, quelque chose comme ceci :

SELECT StudentName 'Student Name' FROM Students;

Et cela vous donnera exactement le même résultat que la requête précédente :

Vous pouvez également donner des alias aux tables, pas seulement aux colonnes. Avec le même mot-clé « AS ». Par exemple, vous pouvez faire ceci :

SELECT s.* FROM Students AS s;

Cela vous donnera toutes les colonnes du tableau Étudiants :

Cela peut être très utile si vous rejoignez plusieurs tables ; au lieu de répéter le nom complet de la table dans la requête, vous pouvez attribuer à chaque table un nom d'alias court. Par exemple, dans la requête suivante :

SELECT Students.StudentName, Departments.DepartmentName
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Cette requête sélectionnera le nom de chaque étudiant de la table « Étudiants » avec son nom de département dans la table « Départements » :

Cependant, la même requête peut être écrite comme ceci :

SELECT s.StudentName, d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Nous avons attribué l'alias « s » à la table « Students » et l'alias « d » à la table « Departments ». Ainsi, au lieu d'utiliser le nom complet des tables, nous avons utilisé leurs alias pour y faire référence. La jointure interne (INNER JOIN) permet de joindre deux tables ou plus en utilisant une condition. Dans notre exemple, nous avons joint la table « Students » à la table « Departments » à l'aide de la colonne « DepartmentId ». Vous trouverez également une explication détaillée de la jointure interne dans la documentation.SQLite Joint« tutoriel.

Cela vous donnera le résultat exact de la requête précédente :

OÙ

L'écriture de requêtes SQL en utilisant la clause SELECT seule avec la clause FROM comme nous l'avons vu dans la section précédente vous donnera toutes les lignes des tables. Cependant, si vous souhaitez filtrer les données renvoyées, vous devez ajouter une clause « WHERE ».

La clause WHERE sert à filtrer les résultats renvoyés par la requête SQL. Voici comment elle fonctionne :

• Dans la clause WHERE, vous pouvez spécifier une « expression ».
• Cette expression sera évaluée pour chaque ligne renvoyée par la ou les tables spécifiées dans la clause FROM.
• L'expression sera évaluée comme une expression booléenne, avec un résultat vrai, faux ou nul.
• Ensuite, seules les lignes pour lesquelles l'expression a été évaluée avec une valeur vraie seront renvoyées, et celles avec des résultats faux ou nuls seront ignorées et ne seront pas incluses dans le jeu de résultats.

Pour filtrer les résultats définis à l'aide de la clause WHERE, vous devez utiliser des expressions et des opérateurs.

Liste des opérateurs dans SQLite et comment les utiliser

Dans la section suivante, nous expliquerons comment filtrer à l'aide d'expressions et d'opérateurs.

L'expression est une ou plusieurs valeurs littérales ou colonnes combinées les unes aux autres avec un opérateur.

Notez que vous pouvez utiliser des expressions à la fois dans la clause SELECT et dans la clause WHERE.

Dans les exemples suivants, nous allons essayer les expressions et les opérateurs de la clause select et de la clause WHERE. Afin de vous montrer comment ils fonctionnent.

Il existe différents types d'expressions et d'opérateurs que vous pouvez spécifier comme suit :

SQLite l'opérateur de concaténation « || »

Cet opérateur est utilisé pour concaténer une ou plusieurs valeurs littérales ou colonnes entre elles. Il produira une chaîne de résultats à partir de toutes les valeurs littérales ou colonnes concaténées. Par exemple:

SELECT 'Id with Name: '|| StudentId || StudentName AS StudentIdWithName
FROM Students;

Cela sera concaténé en un nouvel alias « StudentIdWithName » :

• La valeur de chaîne littérale « Id avec nom : »
• avec la valeur de la colonne « StudentId » et
• avec la valeur de la colonne « StudentName »

SQLite Opérateur CAST :

L'opérateur CAST est utilisé pour convertir une valeur d'un type Type de données vers un autre type de données.

Par exemple, si vous avez une valeur numérique stockée sous forme de chaîne de caractères, comme « '12.5' », et que vous souhaitez la convertir en valeur numérique, vous pouvez utiliser l'opérateur CAST : « CAST('12.5' ​​AS REAL) ». De même, si vous avez une valeur décimale, comme 12.5, et que vous souhaitez uniquement extraire la partie entière, vous pouvez la convertir en entier : « CAST(12.5 AS INTEGER) ».

Exemple

Dans la commande suivante, nous allons essayer de convertir différentes valeurs en d'autres types de données :

SELECT CAST('12.5' AS REAL) ToReal, CAST(12.5 AS INTEGER) AS ToInteger;

Cela vous donnera :

Le résultat est le suivant :

• CAST('12.5' ​​AS REAL) – la valeur '12.5' ​​est une valeur de chaîne, elle sera convertie en valeur REAL.
• CAST(12.5 AS INTEGER) – la valeur 12.5 est une valeur décimale, elle sera convertie en valeur entière. La partie décimale sera tronquée et deviendra 12.

SQLite Arithmétique Operateurs :

Prenez deux ou plusieurs valeurs littérales numériques ou colonnes numériques et renvoyez une valeur numérique. Les opérateurs arithmétiques pris en charge dans SQLite sont:

• L'addition « + » donne la somme des deux opérandes.
• Soltraction “–” – soustracil s'agit des deux opérandes et du résultat de la différence.
• Multiplication « * » – le produit des deux opérandes.
• Rappel (modulo) « % » – donne le reste de la division d’un opérande par le second opérande.
• Division « / » – renvoie le quotient de la division de l’opérande de gauche par l’opérande de droite.

Exemple :

Dans l'exemple suivant, nous allons tester les cinq opérateurs arithmétiques avec des valeurs numériques littérales dans la même clause SELECT :

SELECT 25+6, 25-6, 25*6, 25%6, 25/6;

Cela vous donnera :

Remarquez comment nous avons utilisé ici une instruction SELECT sans clause FROM. Et cela est autorisé dans SQLite tant que nous sélectionnons des valeurs littérales.

SQLite Opérateurs de comparaison

Comparez deux opérandes entre eux et renvoyez un vrai ou un faux comme suit :

• « < » – renvoie vrai si l’opérande de gauche est inférieur à l’opérande de droite.
• « <= » – renvoie vrai si l’opérande de gauche est inférieur ou égal à l’opérande de droite.
• « > » – renvoie vrai si l’opérande de gauche est supérieur à l’opérande de droite.
• « >= » – renvoie vrai si l’opérande de gauche est supérieur ou égal à l’opérande de droite.
• Les opérateurs « = » et « == » renvoient vrai si les deux opérandes sont égaux. Notez que ces deux opérateurs sont identiques et qu'il n'y a aucune différence entre eux.
• Les opérateurs « != » et « <> » renvoient vrai si les deux opérandes sont différents. Notez que ces deux opérateurs sont identiques et qu'il n'y a aucune différence entre eux.

Notez que, SQLite exprime la vraie valeur avec 1 et la fausse valeur avec 0.

Exemple :

SELECT
  10<6 AS '<', 10<=6 AS '<=',
  10>6 AS '>', 10>=6 AS '>=',
  10=6 AS '=', 10==6 AS '==',
  10!=6 AS '!=', 10<>6 AS '<>';

Cela donnera quelque chose comme ceci :

SQLite Opérateurs de recherche de motifs

« LIKE » est utilisé pour la recherche de motifs. Grâce à « Like », vous pouvez rechercher des valeurs correspondant à un motif spécifié à l’aide d’un caractère générique.

L'opérande de gauche peut être soit une valeur littérale de chaîne, soit une colonne de chaîne. Le modèle peut être spécifié comme suit :

• Contient un modèle. Par exemple, StudentName LIKE '%a%' – cela recherchera les noms des étudiants contenant la lettre « a » quelle que soit sa position dans la colonne StudentName.
• On commence par le modèle. Par exemple, « NomÉtudiant COMME 'a%' » – recherche les noms des étudiants qui commencent par la lettre « a ».
• Se termine par le modèle. Par exemple, « NomÉtudiant COMME '%a' » – Recherche les noms des étudiants qui se terminent par la lettre « a ».
• Recherche d'un nom d'élève comportant au moins un caractère, précédé du tiret bas « _ ». Par exemple : « StudentName LIKE 'J___' » recherche les noms d'élèves de 4 caractères maximum, commençant par la lettre « J » et pouvant suivre trois caractères quelconques.

Exemples de correspondance de modèles :

Obtenez les noms des étudiants commençant par la lettre « j » :

SELECT StudentName FROM Students WHERE StudentName LIKE 'j%';

Résultat:

Obtenez les noms des étudiants qui se terminent par la lettre « y » :

SELECT StudentName FROM Students WHERE StudentName LIKE '%y';

Résultat:

Obtenez les noms des étudiants contenant la lettre « n » :

SELECT StudentName FROM Students WHERE StudentName LIKE '%n%';

Résultat:

« GLOB » est équivalent à l'opérateur LIKE, mais contrairement à ce dernier, GLOB est sensible à la casse. Par exemple, les deux commandes suivantes renverront des résultats différents :

SELECT 'Jack' GLOB 'j%';
SELECT 'Jack' LIKE 'j%';

Cela vous donnera :

La première instruction renvoie 0 (faux) car l'opérateur GLOB est sensible à la casse, donc « j » n'est pas égal à « J ». Cependant, la deuxième instruction renverra 1 (vrai) car l'opérateur LIKE n'est pas sensible à la casse, donc « j » est égal à « J ».

Autres opérateurs :

SQLite ET

Opérateur logique qui combine une ou plusieurs expressions. Il renverra vrai, seulement si toutes les expressions donnent une valeur « vraie ». Cependant, il ne renverra false que si toutes les expressions donnent une valeur « fausse ».

Exemple :

La requête suivante recherchera les étudiants qui ont StudentId > 5 et StudentName commence par la lettre N, les étudiants renvoyés doivent remplir les deux conditions :

SELECT *
FROM Students
WHERE (StudentId > 5) AND (StudentName LIKE 'N%');

En sortie, dans la capture d'écran ci-dessus, cela vous donnera uniquement « Nancy ». Nancy est la seule étudiante qui remplit les deux conditions.

SQLite OR

Opérateur logique qui combine une ou plusieurs expressions, de sorte que si l'un des opérateurs combinés donne vrai, il renvoie vrai. Cependant, si toutes les expressions renvoient faux, elle renverra faux.

Exemple :

La requête suivante recherchera les étudiants dont StudentId > 5 ou StudentName commence par la lettre N, les étudiants renvoyés doivent remplir au moins une des conditions :

SELECT *
FROM Students
WHERE (StudentId > 5) OR (StudentName LIKE 'N%');

Cela vous donnera :

En guise de résultat, dans la capture d'écran ci-dessus, cela vous donnera le nom d'un étudiant avec la lettre « n » dans son nom plus l'identifiant d'étudiant ayant une valeur > 5.

Comme vous pouvez le voir, le résultat est différent de la requête avec l'opérateur AND.

SQLite ENTRE

La fonction BETWEEN permet de sélectionner les valeurs comprises entre deux valeurs. Par exemple, « X BETWEEN Y AND Z » renvoie vrai (1) si la valeur X est comprise entre Y et Z, et faux (0) sinon. « X BETWEEN Y AND Z » est équivalent à « X >= Y AND X <= Z » : X doit être supérieur ou égal à Y et inférieur ou égal à Z.

Exemple :

Dans l'exemple de requête suivant, nous allons écrire une requête pour obtenir les étudiants dont la valeur Id est comprise entre 5 et 8 :

SELECT *
FROM Students
WHERE StudentId BETWEEN 5 AND 8;

Cela donnera uniquement les étudiants avec les identifiants 5, 6, 7 et 8 :

SQLite IN

Prend un opérande et une liste d'opérandes. Il retournera vrai si la valeur du premier opérande est égale à l'une des valeurs des opérandes de la liste. L'opérateur IN renvoie vrai (1) si la liste d'opérandes contient la première valeur d'opérande parmi ses valeurs. Sinon, il renverra faux (0).

Comme ceci : « col IN(x, y, z) ». Cela équivaut à « (col=x) ou (col=y) ou (col=z) ».

Exemple :

La requête suivante sélectionnera uniquement les étudiants avec les identifiants 2, 4, 6, 8 :

SELECT *
FROM Students
WHERE StudentId IN(2, 4, 6, 8);

J'aime ça:

La requête précédente donnera le résultat exact de la requête suivante car elles sont équivalentes :

SELECT *
FROM Students
WHERE (StudentId = 2) OR (StudentId =  4) OR (StudentId =  6) OR (StudentId = 8);

Les deux requêtes donnent le résultat exact. Cependant, la différence entre les deux requêtes est que dans la première requête, nous avons utilisé l'opérateur « IN ». Dans la deuxième requête, nous avons utilisé plusieurs opérateurs « OU ».

L'opérateur IN est équivalent à l'utilisation de plusieurs opérateurs OR. « WHERE StudentId IN(2, 4, 6, 8) » est équivalent à « WHERE (StudentId = 2) OR (StudentId = 4) OR (StudentId = 6) OR (StudentId = 8); ».

J'aime ça:

SQLite PAS DANS

L'opérateur « NOT IN » est l'inverse de l'opérateur « IN ». Sa syntaxe est identique : il prend un opérande et une liste d'opérandes. Il renvoie vrai si la valeur du premier opérande est différente de celle de l'un des opérandes de la liste. Autrement dit, il renvoie vrai (0) si la liste ne contient pas le premier opérande. Par exemple : « col NOT IN(x, y, z) ». Ceci est équivalent à « (col <> x) AND (col <> y) AND (col <> z) ».

Exemple :

La requête suivante sélectionnera les étudiants dont les identifiants ne sont pas égaux à l'un de ces identifiants 2, 4, 6, 8 :

SELECT *
FROM Students
WHERE StudentId NOT IN(2, 4, 6, 8);

Comme ça

La requête précédente nous donnons le résultat exact comme la requête suivante car elles sont équivalentes :

SELECT *
FROM Students
WHERE (StudentId <> 2) AND (StudentId <> 4) AND (StudentId <> 6) AND (StudentId <> 8);

J'aime ça:

Dans la capture d'écran ci-dessus,

Nous avons utilisé plusieurs opérateurs différents « <> » pour obtenir une liste d'étudiants qui ne sont égaux à aucun des identifiants suivants 2, 4, 6 ou 8. Cette requête renverra tous les autres étudiants autres que cette liste d'identifiants.

SQLite EXISTE

Les opérateurs EXISTS ne prennent aucun opérande ; il suffit d'une clause SELECT après. L'opérateur EXISTS renverra true (1) s'il y a des lignes renvoyées par la clause SELECT, et il renverra false (0) si aucune ligne n'est renvoyée par la clause SELECT.

Exemple :

Dans l'exemple suivant, nous sélectionnerons le nom du département, si l'identifiant du département existe dans la table des étudiants :

SELECT DepartmentName
FROM Departments AS d
WHERE EXISTS (SELECT DepartmentId FROM Students AS s WHERE d.DepartmentId = s.DepartmentId);

Cela vous donnera :

Seuls les trois départements « Informatique », « Physique » et « Arts » seront renvoyés. Le département « Mathématiques » ne sera pas renvoyé car il n'y a pas d'étudiant inscrit ; son identifiant n'existe donc pas dans la table des étudiants. C'est pourquoi l'opérateur EXISTS a ignoré le département « Mathématiques ».

SQLite pas

Reverses est le résultat de l'opérateur précédent qui le suit. Par exemple :

• NOT BETWEEN – Il retournera vrai si BETWEEN renvoie faux et vice versa.
• NOT LIKE – Il retournera vrai si LIKE renvoie faux et vice versa.
• NOT GLOB – Il retournera vrai si GLOB renvoie faux et vice versa.
• NOT EXISTS – Il retournera vrai si EXISTS renvoie faux et vice versa.

Exemple :

Dans l'exemple suivant, nous utiliserons l'opérateur NOT avec l'opérateur EXISTS pour obtenir les noms des départements qui n'existent pas dans la table Etudiants, ce qui est le résultat inverse de l'opérateur EXISTS. Ainsi, la recherche se fera via DepartmentId qui n'existe pas dans la table Department.

SELECT DepartmentName
FROM Departments AS d
WHERE NOT EXISTS (SELECT DepartmentId
                  FROM Students AS s
                  WHERE d.DepartmentId = s.DepartmentId);

Sortie :

Seul le département « Mathématiques » sera renvoyé, car il est le seul à ne pas figurer dans la table des étudiants.

Limitation et commande

SQLite Commande

SQLite L'ordre consiste à trier votre résultat par une ou plusieurs expressions. Pour trier l'ensemble des résultats, vous devez utiliser la clause ORDER BY comme suit :

• Tout d’abord, vous devez spécifier la clause ORDER BY.
• La clause ORDER BY doit être spécifiée à la fin de la requête ; seule la clause LIMIT peut être spécifiée après.
• Spécifiez l'expression avec laquelle trier les données, cette expression peut être un nom de colonne ou une expression.
• Après l'expression, vous pouvez spécifier un sens de tri facultatif. Soit DESC, pour ordonner les données par ordre décroissant, soit ASC pour ordonner les données par ordre croissant. Si vous n’en avez spécifié aucun, les données seront triées par ordre croissant.
• Vous pouvez spécifier plus d'expressions en utilisant le « » entre elles.

Exemple

Dans l'exemple suivant, nous sélectionnerons tous les étudiants classés par leur nom mais en ordre décroissant, puis par nom de département en ordre croissant :

SELECT s.StudentName, d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId
ORDER BY d.DepartmentName ASC , s.StudentName DESC;

Cela vous donnera :

• SQLite classera d'abord tous les étudiants par nom de département par ordre croissant
• Ensuite, pour chaque nom de département, tous les étudiants sous ce nom de département seront affichés par ordre décroissant par leur nom.

SQLite Limite:

Vous pouvez limiter le nombre de lignes renvoyées par votre requête SQL en utilisant la clause LIMIT. Par exemple, LIMIT 10 ne vous donnera que 10 lignes et ignorera toutes les autres lignes.

Dans la clause LIMIT, vous pouvez sélectionner un nombre précis de lignes à partir d'une position spécifique grâce à la clause OFFSET. Par exemple, « LIMIT 4 OFFSET 4 » ignorera les 4 premières lignes et renverra les 4 lignes à partir de la cinquième, soit les lignes 5, 6, 7 et 8.

Notez que la clause OFFSET est facultative ; vous pouvez l'écrire comme « LIMIT 4, 4 » et elle vous donnera les résultats exacts.

Exemple :

Dans l'exemple suivant, nous renverrons seulement 3 étudiants à partir de l'identifiant d'étudiant 5 en utilisant la requête :

SELECT * FROM Students LIMIT 4,3;

Cela vous donnera seulement trois étudiants à partir de la ligne 5. Cela vous donnera donc les lignes avec StudentId 5, 6 et 7 :

Suppression des doublons

Si votre requête SQL renvoie des valeurs en double, vous pouvez utiliser le mot-clé « DISTINCT » pour les supprimer et ne conserver que les valeurs distinctes. Vous pouvez spécifier plusieurs colonnes après l'application de la clé DISTINCT.

Exemple :

La requête suivante renverra des « valeurs de nom de département » en double : ici, nous avons des valeurs en double avec les noms IT, Physics et Arts.

SELECT d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Cela vous donnera des valeurs en double pour le nom du département :

Remarquez qu'il existe des valeurs en double pour le nom du département. Nous allons maintenant utiliser le mot-clé DISTINCT avec la même requête pour supprimer ces doublons et obtenir uniquement des valeurs uniques. Comme ça:

SELECT DISTINCT d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Cela vous donnera seulement trois valeurs uniques pour la colonne du nom du département :

Total

SQLite Les agrégats sont des fonctions intégrées définies dans SQLite qui regroupera plusieurs valeurs de plusieurs lignes en une seule valeur.

Voici les agrégats pris en charge par SQLite:

SQLite AVG()

Renvoie la moyenne de toutes les valeurs x.

Exemple :

Dans l’exemple suivant, nous obtiendrons la note moyenne que les étudiants obtiendront à tous les examens :

SELECT AVG(Mark) FROM Marks;

Cela vous donnera la valeur « 18.375 » :

Ces résultats proviennent de la somme de toutes les valeurs de notes divisée par leur nombre.

COUNT() – COUNT(X) ou COUNT(*)

Renvoie le nombre total de fois où la valeur x est apparue. Et voici quelques options que vous pouvez utiliser avec COUNT :

• COUNT(x) : compte uniquement x valeurs, où x est un nom de colonne. Il ignorera les valeurs NULL.
• COUNT(*) : compte toutes les lignes de toutes les colonnes.
• COUNT (DISTINCT x) : vous pouvez spécifier un mot-clé DISTINCT avant le x qui obtiendra le nombre de valeurs distinctes de x.

Exemple

Dans l'exemple suivant, nous obtiendrons le nombre total de départements avec COUNT(DepartmentId), COUNT(*) et COUNT(DISTINCT DepartmentId) et en quoi ils sont différents :

SELECT COUNT(DepartmentId), COUNT(DISTINCT DepartmentId), COUNT(*) FROM Students;

Cela vous donnera :

Comme suit:

• COUNT(DepartmentId) vous donnera le nombre de tous les identifiants de département et ignorera les valeurs nulles.
• COUNT(DISTINCT DepartmentId) vous donne des valeurs distinctes de DepartmentId, qui ne sont que 3. Quelles sont les trois valeurs différentes du nom du département. Notez qu'il y a 8 valeurs de nom de département dans le nom de l'étudiant. Mais seulement les trois différentes valeurs que sont les mathématiques, l'informatique et la physique.
• COUNT(*) compte le nombre de lignes dans la table des étudiants qui sont 10 lignes pour 10 étudiants.

GROUP_CONCAT() – GROUP_CONCAT(X) ou GROUP_CONCAT(X,Y)

La fonction d'agrégation GROUP_CONCAT concatène plusieurs valeurs en une seule valeur avec une virgule pour les séparer. Il propose les options suivantes :

• GROUP_CONCAT(X) : Cela concatènera toutes les valeurs de x en une seule chaîne, avec la virgule « », utilisée comme séparateur entre les valeurs. Les valeurs NULL seront ignorées.
• GROUP_CONCAT(X, Y) : cela concatènera les valeurs de x en une seule chaîne, avec la valeur de y utilisée comme séparateur entre chaque valeur au lieu du séparateur par défaut ','. Les valeurs NULL seront également ignorées.
• GROUP_CONCAT(DISTINCT X) : cela concatènera toutes les valeurs distinctes de x en une seule chaîne, avec la virgule « », utilisée comme séparateur entre les valeurs. Les valeurs NULL seront ignorées.

Exemple GROUP_CONCAT (NomDépartement)

La requête suivante concatènera toutes les valeurs du nom du département des étudiants et de la table des départements en une seule chaîne séparée par des virgules. Ainsi, au lieu de renvoyer une liste de valeurs, une valeur sur chaque ligne. Il ne renverra qu'une seule valeur sur une ligne, avec toutes les valeurs séparées par des virgules :

SELECT GROUP_CONCAT(d.DepartmentName)
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Cela vous donnera :

Cela vous donnera la liste des valeurs de noms de 8 départements concaténées en une chaîne séparée par des virgules.

GROUP_CONCAT(DISTINCT DepartmentName) Exemple

La requête suivante concatènera les valeurs distinctes du nom du département de la table des étudiants et des départements en une seule chaîne séparée par des virgules :

SELECT GROUP_CONCAT(DISTINCT d.DepartmentName)
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Cela vous donnera :

Remarquez à quel point le résultat est différent du résultat précédent ; seules trois valeurs renvoyées sont les noms des départements distincts, et les valeurs en double ont été supprimées.

GROUP_CONCAT(NomDépartement,'&') Exemple

La requête suivante concatènera toutes les valeurs de la colonne du nom du département de la table des étudiants et des départements en une seule chaîne, mais avec le caractère « & » au lieu d'une virgule comme séparateur :

SELECT GROUP_CONCAT(d.DepartmentName, '&')
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Cela vous donnera :

Remarquez comment le caractère « & » est utilisé à la place du caractère par défaut « » pour séparer les valeurs.

SQLite MAXIMUM MINIMUM()

MAX(X) vous renvoie la valeur la plus élevée parmi les valeurs X. MAX renverra une valeur NULL si toutes les valeurs de x sont nulles. Alors que MIN(X) vous renvoie la plus petite valeur parmi les valeurs X. MIN renverra une valeur NULL si toutes les valeurs de X sont nulles.

Exemple

Dans la requête suivante, nous utiliserons les fonctions MIN et MAX pour obtenir la note la plus élevée et la note la plus basse du tableau « Marks » :

SELECT MAX(Mark), MIN(Mark) FROM Marks;

Cela vous donnera :

SQLite SOMME(x), Total(x)

Les deux renverront la somme de toutes les valeurs x. Mais ils diffèrent sur les points suivants :

• SUM renverra null si toutes les valeurs sont nulles, mais Total renverra 0.
• TOTAL renvoie toujours des valeurs à virgule flottante. SUM renvoie une valeur entière si toutes les valeurs x sont un entier. Cependant, si les valeurs ne sont pas un nombre entier, une valeur à virgule flottante sera renvoyée.

Exemple

Dans la requête suivante, nous utiliserons SUM et total pour obtenir la somme de toutes les notes dans les tables « Marks » :

SELECT SUM(Mark), TOTAL(Mark) FROM Marks;

Cela vous donnera :

Comme vous pouvez le voir, TOTAL renvoie toujours une virgule flottante. Mais SUM renvoie une valeur entière car les valeurs de la colonne « Marque » peuvent être des nombres entiers.

Exemple de différence entre SUM et TOTAL :

Dans la requête suivante, nous montrerons la différence entre SUM et TOTAL lorsqu'ils obtiendront la SUM des valeurs NULL :

SELECT SUM(Mark), TOTAL(Mark) FROM Marks WHERE TestId = 4;

Cela vous donnera :

Notez qu'il n'y a aucune note pour TestId = 4, il y a donc des valeurs nulles pour ce test. SUM renvoie une valeur nulle sous forme de blanc, tandis que TOTAL renvoie 0.

Par groupe

La clause GROUP BY permet de spécifier une ou plusieurs colonnes qui seront utilisées pour regrouper les lignes en groupes. Les lignes avec les mêmes valeurs seront rassemblées (organisées) en groupes.

Pour toute autre colonne qui n'est pas incluse dans les colonnes group by, vous pouvez utiliser une fonction d'agrégation pour celle-ci.

Exemple :

La requête suivante vous donnera le nombre total d'étudiants présents dans chaque département.

SELECT d.DepartmentName, COUNT(s.StudentId) AS StudentsCount
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId
GROUP BY d. DepartmentName;

Cela vous donnera :

La clause GROUPBY DepartmentName regroupera tous les étudiants en groupes, un pour chaque nom de département. Pour chaque groupe de « département », il comptera les étudiants qui en font partie.

Clause AVOIR

Si vous souhaitez filtrer les groupes renvoyés par la clause GROUP BY, vous pouvez alors spécifier une clause « HAVING » avec une expression après GROUP BY. L'expression sera utilisée pour filtrer ces groupes.

Exemple

Dans la requête suivante, nous sélectionnerons les départements qui ne comptent que deux étudiants :

SELECT d.DepartmentName, COUNT(s.StudentId) AS StudentsCount
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId
GROUP BY d. DepartmentName
HAVING COUNT(s.StudentId) = 2;

Cela vous donnera :

La clause HAVING COUNT(S.StudentId) = 2 filtrera les groupes renvoyés et renverra uniquement les groupes qui contiennent exactement deux étudiants. Dans notre cas, le département Arts compte 2 étudiants, cela est donc affiché dans la sortie.

SQLite Requête et sous-requête

Dans n'importe quelle requête, vous pouvez utiliser une autre requête soit dans un SELECT, INSERT, DELETE, UPDATE ou dans une autre sous-requête.

Cette requête imbriquée est appelée sous-requête. Nous allons voir maintenant quelques exemples d'utilisation de sous-requêtes dans la clause SELECT. Cependant, dans le didacticiel Modification des données, nous verrons comment utiliser des sous-requêtes avec les instructions INSERT, DELETE et UPDATE.

Utilisation d'une sous-requête dans l'exemple de clause FROM

Dans la requête suivante, nous inclurons une sous-requête dans la clause FROM :

SELECT
  s.StudentName, t.Mark
FROM Students AS s
INNER JOIN
(
   SELECT StudentId, Mark
   FROM Tests AS t
   INNER JOIN Marks AS m ON t.TestId = m.TestId
)  ON s.StudentId = t.StudentId;

La requête:

   SELECT StudentId, Mark
   FROM Tests AS t
   INNER JOIN Marks AS m ON t.TestId = m.TestId

La requête ci-dessus est appelée ici une sous-requête car elle est imbriquée dans la clause FROM. Notez que nous lui avons donné un alias « t » afin que nous puissions faire référence aux colonnes renvoyées par celui-ci dans la requête.

Cette requête vous donnera :

Donc dans notre cas,

• s.StudentName est sélectionné dans la requête principale qui donne le nom des étudiants et
• t.Mark est sélectionné dans la sous-requête ; qui donne les notes obtenues par chacun de ces étudiants

Utilisation d'une sous-requête dans l'exemple de clause WHERE

Dans la requête suivante, nous inclurons une sous-requête dans la clause WHERE :

SELECT DepartmentName
FROM Departments AS d
WHERE NOT EXISTS (SELECT DepartmentId
                  FROM Students AS s
                  WHERE d.DepartmentId = s.DepartmentId);

La requête:

SELECT DepartmentId
FROM Students AS s
WHERE d.DepartmentId = s.DepartmentId

La requête ci-dessus est appelée ici une sous-requête car elle est imbriquée dans la clause WHERE. La sous-requête renverra les valeurs DepartmentId qui seront utilisées par l'opérateur NOT EXISTS.

Cette requête vous donnera :

Dans la requête ci-dessus, nous avons sélectionné le département dans lequel aucun étudiant n'est inscrit. C'est le département « Mathématiques » ici.

complet » Operation – UNION,Intersect

SQLite prend en charge les opérations SET suivantes :

UNION & UNION TOUS

Il combine un ou plusieurs jeux de résultats (un groupe de lignes) renvoyés par plusieurs instructions SELECT en un seul jeu de résultats.

UNION renverra des valeurs distinctes. Cependant, UNION ALL n'inclura pas et inclura les doublons.

Notez que le nom de la colonne sera le nom de la colonne spécifié dans la première instruction SELECT.

Exemple d'UNION

Dans l'exemple suivant, nous obtiendrons la liste des DepartmentId de la table des étudiants et la liste des DepartmentId de la table des départements dans la même colonne :

SELECT DepartmentId AS DepartmentIdUnioned FROM Students
UNION
SELECT DepartmentId FROM Departments;

Cela vous donnera :

La requête ne renvoie que 5 lignes qui correspondent aux valeurs distinctes de l'identifiant du service. Notez la première valeur qui est la valeur nulle.

SQLite UNION TOUS Exemple

Dans l'exemple suivant, nous obtiendrons la liste des DepartmentId de la table des étudiants et la liste des DepartmentId de la table des départements dans la même colonne :

SELECT DepartmentId AS DepartmentIdUnioned FROM Students
UNION ALL
SELECT DepartmentId FROM Departments;

Cela vous donnera :

La requête renverra 14 lignes, 10 lignes de la table des étudiants et 4 de la table des départements. Notez qu'il existe des doublons dans les valeurs renvoyées. Notez également que le nom de la colonne était celui spécifié dans la première instruction SELECT.

Voyons maintenant comment UNION all donnera des résultats différents si nous remplaçons UNION ALL par UNION :

SQLite COUPER

Renvoie les valeurs qui existent dans les deux jeux de résultats combinés. Les valeurs qui existent dans l’un des ensembles de résultats combinés seront ignorées.

Exemple

Dans la requête suivante, nous sélectionnerons les valeurs DepartmentId qui existent dans les tables Students et Departments dans la colonne DepartmentId :

SELECT DepartmentId FROM Students
Intersect
SELECT DepartmentId FROM Departments;

Cela vous donnera :

La requête ne renvoie que trois valeurs 1, 2 et 3. Quelles sont les valeurs qui existent dans les deux tables.

Cependant, les valeurs null et 4 n'ont pas été incluses car la valeur nulle existe uniquement dans la table des étudiants et non dans la table des départements. Et la valeur 4 existe dans la table des départements et non dans la table des étudiants.

C'est pourquoi les valeurs NULL et 4 ont été ignorées et non incluses dans les valeurs renvoyées.

SAUF

Supposons que si vous avez deux listes de lignes, list1 et list2, et que vous souhaitez uniquement les lignes de list1 qui n'existent pas dans list2, vous pouvez utiliser la clause « EXCEPT ». La clause EXCEPT compare les deux listes et renvoie les lignes qui existent dans la liste1 et qui n'existent pas dans la liste2.

Exemple

Dans la requête suivante, nous sélectionnerons les valeurs DepartmentId qui existent dans la table des départements et qui n'existent pas dans la table des étudiants :

SELECT DepartmentId FROM Departments
EXCEPT
SELECT DepartmentId FROM Students;

Cela vous donnera :

La requête renvoie uniquement la valeur 4. C'est la seule valeur qui existe dans la table des départements et qui n'existe pas dans la table des étudiants.

Gestion NULL

La valeur « NULL » est une valeur spéciale dans SQLiteElle sert à représenter une valeur inconnue ou manquante. Notez que la valeur nulle est totalement différente de « 0 » ou d'une valeur vide. En effet, 0 et une valeur vide sont des valeurs connues, tandis que la valeur nulle est inconnue.

Les valeurs NULL nécessitent un traitement spécial dans SQLite, nous allons voir maintenant comment gérer les valeurs NULL.

Rechercher des valeurs NULL

Vous ne pouvez pas utiliser l'opérateur d'égalité normal (=) pour rechercher les valeurs nulles. Par exemple, la requête suivante recherche les étudiants dont la valeur DepartmentId est nulle :

SELECT * FROM Students WHERE DepartmentId = NULL;

Cette requête ne donnera aucun résultat :

Étant donné que la valeur NULL n’est égale à aucune autre valeur incluant une valeur nulle elle-même, c’est pourquoi elle n’a renvoyé aucun résultat.

Toutefois, pour que la requête fonctionne, vous devez utiliser l’opérateur « IS NULL » pour rechercher les valeurs nulles, comme suit :

SELECT * FROM Students WHERE DepartmentId IS NULL;

Cela vous donnera :

La requête renverra les étudiants qui ont une valeur DepartmentId nulle.

Si vous souhaitez obtenir les valeurs qui ne sont pas nulles, vous devez utiliser l'opérateur « IS NOT NULL » comme ceci :

SELECT * FROM Students WHERE DepartmentId IS NOT NULL;

Cela vous donnera :

La requête renverra les étudiants qui n’ont pas de valeur NULL DepartmentId.

Résultats conditionnels

Si vous avez une liste de valeurs et que vous souhaitez en sélectionner une en fonction de certaines conditions. Pour cela, la condition de cette valeur particulière doit être vraie pour être sélectionnée.

L'expression CASE évaluera cette liste de conditions pour toutes les valeurs. Si la condition est vraie, elle renverra cette valeur.

Par exemple, si vous avez une colonne « Note » et que vous souhaitez sélectionner une valeur de texte basée sur la valeur de note comme suit :

– « Excellent » si la note est supérieure à 85.

– « Très bien » si la note est comprise entre 70 et 85.

– « Bien » si la note est comprise entre 60 et 70.

Ensuite, vous pouvez utiliser l'expression CASE pour ce faire.

Cela peut être utilisé pour définir une certaine logique dans la clause SELECT afin que vous puissiez sélectionner certains résultats en fonction de certaines conditions comme l'instruction if par exemple.

L'opérateur CASE peut être défini avec différentes syntaxes comme suit :

Vous pouvez utiliser différentes conditions :

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  WHEN condition3 THEN result3
  …
  ELSE resultn
END

Ou bien, vous pouvez utiliser une seule expression et choisir parmi différentes valeurs possibles :

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  WHEN value3 THEN result3
  …
  ELSE restuln
END

Notez que la clause ELSE est facultative.

Exemple

Dans l'exemple suivant, nous utiliserons l'expression CASE avec la valeur NULL dans la colonne « Id du département » de la table « Étudiants » pour afficher le texte « Aucun département » comme suit :

SELECT
  StudentName,
  CASE
    WHEN DepartmentId IS NULL THEN 'No Department'
    ELSE DepartmentId
  END AS DepartmentId
FROM Students;

• L'opérateur CASE vérifiera la valeur du DepartmentId si elle est nulle ou non.
• S'il s'agit d'une valeur NULL, il sélectionnera la valeur littérale « No Department » au lieu de la valeur DepartmentId.
• S’il ne s’agit pas d’une valeur nulle, il sélectionnera la valeur de la colonne DepartmentId.

Cela vous donnera la sortie comme indiqué ci-dessous:

Expression de table commune

Les expressions de table communes (CTE) sont des sous-requêtes définies dans l'instruction SQL avec un nom donné.

Il présente un avantage par rapport aux sous-requêtes car il est défini à partir des instructions SQL et rendra les requêtes plus faciles à lire, à maintenir et à comprendre.

Une expression de table commune peut être définie en plaçant la clause WITH devant une instruction SELECT comme suit :

WITH CTEname
AS
(
   SELECT statement
)
SELECT, UPDATE, INSERT, or update statement here FROM CTE

Le nom « CTEname » est le nom que vous pouvez donner à la CTE et utiliser ultérieurement pour y faire référence. Notez que vous pouvez définir des instructions SELECT, UPDATE, INSERT ou DELETE sur les CTE.

Voyons maintenant un exemple de la façon d'utiliser CTE dans la clause SELECT.

Exemple

Dans l'exemple suivant, nous définirons un CTE à partir d'une instruction SELECT, puis nous l'utiliserons plus tard sur une autre requête :

WITH AllDepartments
AS
(
  SELECT DepartmentId, DepartmentName
  FROM Departments
)
SELECT
  s.StudentId,
  s.StudentName,
  a.DepartmentName
FROM Students AS s
INNER JOIN AllDepartments AS a ON s.DepartmentId = a.DepartmentId;

Dans cette requête, nous avons défini une expression de table commune (CTE) et l'avons nommée « AllDepartments ». Cette CTE a été définie à partir d'une requête SELECT :

SELECT DepartmentId, DepartmentName
  FROM Departments

Ensuite, après avoir défini le CTE, nous l'avons utilisé dans la requête SELECT qui le suit.

Notez que les expressions de table communes n’affectent pas le résultat de la requête. C'est un moyen de définir une vue logique ou une sous-requête afin de les réutiliser dans une même requête. Les expressions de table courantes sont comme une variable que vous déclarez et que vous réutilisez comme sous-requête. Seule l'instruction SELECT affecte le résultat de la requête.

Cette requête vous donnera :

Requêtes avancées

Les requêtes avancées sont les requêtes qui contiennent des jointures complexes, des sous-requêtes et certains agrégats. Dans la section suivante, nous verrons un exemple de requête avancée :

Où nous obtenons le,

• Noms des départements avec tous les étudiants de chaque département
• Nom des étudiants séparés par une virgule et
• Montrer le département qui compte au moins trois étudiants

SELECT
  d.DepartmentName,
  COUNT(s.StudentId) StudentsCount,
  GROUP_CONCAT(StudentName) AS Students
FROM Departments AS d
INNER JOIN Students AS s ON s.DepartmentId = d.DepartmentId
GROUP BY d.DepartmentName
HAVING COUNT(s.StudentId) >= 3;

Nous avons ajouté une clause JOIN pour récupérer le nom du département dans la table Departments. Ensuite, nous avons ajouté une clause GROUP BY avec deux fonctions d'agrégation :

• « COUNT » pour compter les étudiants de chaque groupe de département.
• GROUP_CONCAT pour concaténer les étudiants de chaque groupe avec des virgules séparées dans une chaîne.

Après le GROUP BY, nous avons utilisé la clause HAVING pour filtrer les départements et sélectionner uniquement les départements qui comptent au moins 3 étudiants.

Le résultat sera le suivant :

FAQ