SQLite Requête : Sélectionner, Où, LIMITE, DÉCALAGE, Compter, Regrouper par
Marcus Allen
Pour écrire des 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 Poste de travail et accédez au répertoire suivant «C:\sqlite" et
- Ouvrez ensuite "sqlite3.exe"
Étape 2) Ouvrez la base de données "TutorielsSampleDB.db» par 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 est toujours «Nom d'étudiant« ; la colonne StudentName est toujours la même, elle ne change pas par 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 donné à la table des étudiants un alias « s » et à la table des départements un alias « d ».
- Ensuite, au lieu d'utiliser le nom complet de la table, nous avons utilisé leurs alias pour y faire référence.
- INNER JOIN joint deux ou plusieurs tables ensemble à l’aide d’une condition. Dans notre exemple, nous avons joint la table Students avec la table Departments avec la colonne DepartmentId. Il y a également une explication détaillée de INNER JOIN dans le « SQLite Tutoriel « Joindres ».
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 est utilisée pour filtrer le jeu de résultats renvoyé par le Requête SQL. Voici comment fonctionne la clause WHERE :
- 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é dans un nouvel alias "StudentIdWithName"
- La valeur de chaîne littérale "Identifiant avec nom : "
- avec la valeur du "Carte d'étudiantcolonne " et
- avec la valeur de "Nom d'étudiant" colonne
SQLite Opérateur CAST :
L'opérateur CAST est utilisé pour convertir une valeur d'un type de données à un autre Type de données.
Par exemple, si vous avez une valeur numérique stockée sous forme de valeur de chaîne comme celle-ci » «12.5 ' " et vous souhaitez le convertir en valeur numérique, vous pouvez utiliser l'opérateur CAST pour le faire comme ceci "CAST ('12.5' COMME RÉEL)« . Ou si vous avez une valeur décimale comme 12.5 et que vous avez besoin d'obtenir uniquement la partie entière, vous pouvez la convertir en un entier comme celui-ci « 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:
|
Mise en situation :
Dans l'exemple suivant, nous allons essayer les cinq opérateurs arithmétiques avec des valeurs numériques littérales dans le même
clause de sélection :
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 :
|
Notez que, SQLite exprime la vraie valeur avec 1 et la fausse valeur avec 0.
Mise en situation :
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
"COMME» – est utilisé pour la correspondance de modèles. En utilisant "J'aime", vous pouvez rechercher des valeurs qui correspondent à un modèle 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 motif. Par exemple, Nom de l'étudiant LIKE '%a%' – cela recherchera les noms des étudiants qui contiennent la lettre « a » à n'importe quelle position dans la colonne StudentName.
- Commence par le modèle. Par exemple, "Nom de l'étudiant LIKE 'a%'» – recherchez les noms des élèves qui commencent par la lettre « a ».
- Se termine par le motif. Par exemple, "Nom de l'étudiant LIKE '%a'» – Recherchez les noms des élèves qui se terminent par la lettre « a ».
- Correspondance avec n'importe quel caractère d'une chaîne à l'aide de la lettre de soulignement « _ ». Par exemple, "Nom de l'étudiant COMME 'J___'» – Recherchez les noms d'élèves comportant 4 caractères. Il doit commencer par la lettre « J » et peut comporter trois autres caractères après la lettre « J ».
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 GLOB est sensible à la casse, contrairement à l'opérateur LIKE. 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 ».
Mise en situation :
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.
Mise en situation :
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
BETWEEN est utilisé pour sélectionner les valeurs qui se situent dans une plage de deux valeurs. Par exemple, "X ENTRE Y ET Z" renverra vrai (1) si la valeur X est comprise entre les deux valeurs Y et Z. Sinon, il renverra faux (0). "X ENTRE Y ET Z" est équivalent à "X >= Y ET X <= Z», X doit être supérieur ou égal à Y et X est inférieur ou égal à Z.
Mise en situation :
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 ça: "col IN(x, y, z)« . Cela équivaut à « (col=x) ou (col=y) ou (col=z) " .
Mise en situation :
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 équivaut à utiliser plusieurs opérateurs OR. Le "OÙ StudentId IN(2, 4, 6, 8)" est équivalent à " OÙ (StudentId = 2) OU (StudentId = 4) OU (StudentId = 6) OU (StudentId = 8) ;"
J'aime ça:
SQLite PAS DANS
L'opérande "NOT IN" est l'opposé de l'opérateur IN. Mais avec la même syntaxe ; il faut un opérande et une liste d'opérandes. Il retournera vrai si la valeur du premier opérande n'est pas égale à l'une des valeurs des opérandes de la liste. c'est-à-dire qu'il renverra vrai (0) si la liste des opérandes ne contient pas le premier opérande. Comme ça: "col PAS DANS (x, y, z)« . Cela équivaut à «(col<>x) ET (col<>y) ET (col<>z)" .
Mise en situation :
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.
Mise en situation :
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 :
Uniquement les trois départements »Informatique, physique et arts» sera restitué. Et le nom du département «Mathématique" ne sera pas renvoyé car il n'y a aucun étudiant dans ce département, donc l'identifiant du département n'existe pas dans la table des étudiants. C'est pourquoi l'opérateur EXISTS a ignoré le "Mathématique" département.
SQLite ne pas
Revinverse le résultat de l'opérateur précédent qui vient après lui. 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.
Mise en situation :
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ématique » sera restitué. Parce que le "Mathématique« Le département est le seul département qui n'existe pas dans la table des étudiants.
Limitation et commande
SQLite Order
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 spécifique de lignes à partir d'une position spécifique à l'aide de la clause OFFSET. Par exemple, "LIMITE 4 DECALAGE 4" ignorera les 4 premières lignes et renverra 4 lignes à partir des cinquièmes lignes, vous obtiendrez donc les lignes 5,6,7, 8, et .
Notez que la clause OFFSET est facultative, vous pouvez l’écrire comme «LIMIT 4, 4» et il 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 « DISTINCT» pour supprimer ces doublons et revenir sur des valeurs distinctes. Vous pouvez spécifier plusieurs colonnes après le travail de la clé DISTINCT.
Mise en situation :
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.
Mise en situation :
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 «Les marques" tableau:
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 le «Les marques" les tables:
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.
Mise en situation :
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.
Ensemble 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
Le "NULL" La valeur est une valeur particulière dans SQLite. Il est utilisé pour représenter une valeur inconnue ou manquante. Notez que la valeur nulle est totalement différente de «0" ou une valeur " " vide. Cependant, étant donné que 0 et la valeur vide sont une valeur connue, 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.
- Cependant, pour que la requête fonctionne, vous devez utiliser le « EST NULL » opérateur 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 des valeurs qui ne sont pas nulles, vous devez utiliser le "EST NON NULLE" opérateur 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 le Adhésion expression avec NULL dans la colonne Identifiant 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 CTE» est n'importe quel nom que vous pouvez donner au CTE, vous pourrez l'utiliser pour y faire référence plus tard. Notez que vous pouvez définir une instruction 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 un CTE et lui avons donné le nom «Tous les départements« . Ce CTE a été défini à 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é un INSCRIPTION clause pour obtenir le DepartmentName de la table Departments. Après cela, 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 :
Résumé
C'était une introduction à l'écriture SQLite requêtes et les bases de l'interrogation de la base de données et comment filtrer les données renvoyées. Vous pouvez maintenant écrire le vôtre SQLite requêtes.
