Pour écrire des requêtes SQL dans une base de données SQLite, vous devez savoir comment fonctionnent les clauses SELECT, FROM, WHERE, GROUP BY, ORDER BY et LIMIT et comment les utiliser.
Au cours de ce didacticiel, vous apprendrez à utiliser ces clauses et à écrire des clauses SQLite.
Dans ce didacticiel, vous apprendrez-
- Lecture de données avec Select
- Noms et alias
- OÙ
- Limitation et commande
- Suppression des doublons
- Agrégat
- Par groupe
- Requête et sous-requête
- Définir les opérations -UNION, intersecter
- Gestion NULL
- Résultats conditionnels
- Expression de table commune
- Requêtes avancées
Lecture de données avec Select
La clause SELECT est l'instruction principale que vous utilisez pour interroger une base de données SQLite. 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ù vous souhaitez 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 à la base de données exemple en cours d'exécution:
Étape 1) Dans cette étape,
- Ouvrez Poste de travail et accédez au répertoire suivant " C: \ sqlite " et
- Ensuite, ouvrez " sqlite3.exe ":
Étape 2) Ouvrez la base de données " TutorialsSampleDB.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 les éléments à sélectionner. Comme suit:
CHOISIR *
Cette commande sélectionne toutes les colonnes de toutes les tables (ou sous-requêtes) référencées dans la clause FROM. Par exemple:
CHOISIR *DES étudiantsINNER 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_table. *
Cela sélectionnera toutes les colonnes de la table "nom_table" uniquement. Par exemple:
SELECT étudiants. *DES étudiantsINNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;
Cela sélectionnera toutes les colonnes de la table des étudiants uniquement:
Une valeur littérale
Une valeur littérale est une valeur constante qui peut être spécifiée dans l'instruction select. Vous pouvez utiliser des valeurs littérales normalement de la même manière que vous utilisez les noms de colonne dans la clause SELECT. Ces valeurs littérales seront affichées pour chaque ligne à partir des 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 NULL.
- Current_TIME - Cela 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 des étudiants, avec une nouvelle colonne appelée un pays qui contient la valeur "USA", vous pouvez faire ceci:
SELECT *, 'USA' COMME Pays FROM Etudiants;
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 en fait une nouvelle colonne ajoutée au tableau. C'est 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 alias
L'alias est un nouveau nom pour la colonne qui vous permet de sélectionner la colonne avec un nouveau nom. Les alias de colonne 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:
SÉLECTIONNEZ 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 " StudentName "; 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 lui, 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 attribuer des alias aux tables, pas seulement des colonnes. Avec le même mot-clé "AS". Par exemple, vous pouvez faire ceci:
SELECT s. * FROM Élèves COMME s;
Cela vous donnera toutes les colonnes du tableau Étudiants:
Cela peut être très utile si vous joignez 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.DepartmentNameDES étudiantsINNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;
Cette requête sélectionnera chaque nom d'étudiant dans la table "Etudiants" 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.DepartmentNameFROM étudiants AS sINNER 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 aussi une explication détaillée de INNER JOIN dans le didacticiel "SQLite Joins".
Cela vous donnera la sortie exacte 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 la 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 en tant qu'expression booléenne, avec un résultat true, false ou null.
- 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 nul seront ignorées et ne seront pas incluses dans le jeu de résultats.
- Pour filtrer l'ensemble de résultats à 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 vous pouvez filtrer à l'aide d'une expression et d'opérateurs.
L'expression est une ou plusieurs valeurs littérales ou colonnes combinées entre elles 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 à la fois dans la clause select et dans 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 avec nom:' || StudentId || StudentName AS StudentIdWithNameDES étudiants;
Cela se concaténera dans 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 "
Opérateur SQLite CAST:
L'opérateur CAST est utilisé pour convertir une valeur d'un type de données en un autre type de données.
Par exemple, si vous avez une valeur numérique stockée en tant que valeur de chaîne comme celle-ci " '12 .5 ' " et que vous souhaitez la convertir en valeur numérique, vous pouvez utiliser l'opérateur CAST pour faire ceci comme ceci " CAST ('12 .5' AS REAL) ". 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 ceci "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 une valeur entière. La partie décimale sera tronquée et devient 12.
Opérateurs arithmétiques SQLite:
Prenez au moins deux 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:
|
Exemple:
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 select:
SÉLECTIONNER 25 + 6, 25-6, 25 * 6, 25% 6, 25/6;
Cela vous donnera:
Remarquez comment nous avons utilisé une instruction SELECT sans clause FROM ici. Et cela est autorisé dans SQLite tant que nous sélectionnons des valeurs littérales.
Opérateurs de comparaison SQLite
Comparez deux opérandes entre eux et retournez un vrai ou un faux comme suit:
|
Notez que SQLite exprime la valeur vraie avec 1 et la valeur fausse avec 0.
Exemple:
SÉLECTIONNER10 <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:
Opérateurs de correspondance de modèle SQLite
" LIKE " - est utilisé pour la correspondance de modèle. 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 une valeur littérale de chaîne ou une colonne de chaîne. Le modèle peut être spécifié comme suit:
- Contient un motif. Par exemple, StudentName LIKE '% a%' - cela recherchera les noms des étudiants qui contiennent la lettre «a» à n'importe quelle position dans la colonne StudentName.
- Commence par le motif. Par exemple, " StudentName LIKE 'a%' " - recherchez les noms des étudiants qui commencent par la lettre "a".
- Se termine par le motif. Par exemple, " StudentName LIKE '% a' " - Recherchez les noms des élèves qui se terminent par la lettre "a".
- Faire correspondre n'importe quel caractère unique dans une chaîne en utilisant la lettre de soulignement "_". Par exemple, " StudentName LIKE 'J___' " - Recherchez les noms d'élèves de 4 caractères. Il doit commencer par la lettre «J» et peut avoir trois autres caractères supplémentaires après la lettre «J».
Exemples de correspondance de modèles:
- Obtenez des noms d'étudiants commençant par la lettre «j»:
SELECT StudentName FROM Students WHERE StudentName LIKE 'j%';
Résultat:
- Obtenez les noms des étudiants se terminant par la lettre `` y '':
SELECT StudentName FROM Students WHERE StudentName LIKE '% y';
Résultat:
- Obtenez les noms des étudiants qui contiennent la lettre «n»:
SELECT StudentName FROM Students WHERE StudentName LIKE '% n%';
Résultat:
"GLOB" - équivaut à 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' COMME '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 est insensible à la casse, donc «j» est égal à «J».
Autres opérateurs:
SQLite ET
Un opérateur logique qui combine une ou plusieurs expressions. Il retournera vrai, seulement si toutes les expressions donnent une valeur "vraie". Cependant, il ne retournera false que si toutes les expressions donnent une valeur "false".
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:
CHOISIR *DES étudiantsWHERE (StudentId> 5) AND (StudentName LIKE 'N%');
En sortie, dans la capture d'écran ci-dessus, cela ne vous donnera que "Nancy". Nancy est la seule étudiante qui remplit les deux conditions.
SQLite OU
Un opérateur logique qui combine une ou plusieurs expressions, de sorte que si l'un des opérateurs combinés donne vrai, alors il retournera vrai. Cependant, si toutes les expressions renvoient false, elle renverra false.
Exemple:
La requête suivante recherchera les étudiants qui ont StudentId> 5 ou StudentName commence par la lettre N, les étudiants renvoyés doivent remplir au moins l'une des conditions:
CHOISIR *DES étudiantsWHERE (StudentId> 5) OU (StudentName LIKE 'N%');
Cela vous donnera:
En sortie, 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 de l'élève 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 comprises dans une plage de deux valeurs. Par exemple, " X BETWEEN Y AND Z " retournera true (1) si la valeur X est entre les deux valeurs Y et Z. Sinon, il retournera false (0). " X BETWEEN Y AND Z " équivaut à " X> = Y AND X <= Z ", X doit être supérieur ou égal à Y et X est inférieur ou égal à Z.
Exemple:
Dans l'exemple de requête suivant, nous écrirons une requête pour obtenir des étudiants avec une valeur d'ID comprise entre 5 et 8:
CHOISIR *DES étudiantsO StudentId ENTRE 5 ET 8;
Cela donnera uniquement aux étudiants avec les identifiants 5, 6, 7 et 8:
SQLite IN
Prend un opérande et une liste d'opérandes. Il retournera true si la première valeur d'opérande est égale à l'une des valeurs des opérandes de la liste. L'opérateur IN renvoie true (1) si la liste d'opérandes contient la première valeur d'opérande dans ses valeurs. Sinon, il retournera false (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 les étudiants avec les identifiants 2, 4, 6, 8 uniquement:
CHOISIR *DES étudiantsO StudentId IN (2, 4, 6, 8);
Comme ça:
La requête précédente donnera le résultat exact comme la requête suivante car ils sont équivalents:
CHOISIR *DES étudiantsWHERE (StudentId = 2) OU (StudentId = 4) OU (StudentId = 6) OU (StudentId = 8);
Les deux requêtes donnent le résultat exact. Cependant, la différence entre les deux requêtes est que la première requête que nous avons utilisée l'opérateur "IN". Dans la deuxième requête, nous avons utilisé plusieurs opérateurs "OR".
L'opérateur IN équivaut à utiliser plusieurs opérateurs OR. " WHERE StudentId IN (2, 4, 6, 8) " équivaut à " WHERE (StudentId = 2) OR (StudentId = 4) OR (StudentId = 6) OR (StudentId = 8); "
Comme ça:
SQLite PAS DANS
L'opérande "NOT IN" est l'opposé de l'opérateur IN. Mais avec la même syntaxe; il prend un opérande et une liste d'opérandes. Il retournera true si la première valeur d'opérande n'est pas égale à l'une des valeurs des opérandes de la liste. c'est-à-dire qu'il retournera vrai (0) si la liste des opérandes ne contient pas le premier opérande. Comme ceci: " col NOT IN (x, y, z) ". Cela équivaut à " (col <> x) AND (col <> y) AND (col <> z) ".
Exemple:
La requête suivante sélectionnera les élèves dont les identifiants ne sont pas égaux à l'un de ces identifiants 2, 4, 6, 8:
CHOISIR *DES étudiantsWHERE 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 ils sont équivalents:
CHOISIR *DES étudiantsWHERE (StudentId <> 2) AND (StudentId <> 4) AND (StudentId <> 6) AND (StudentId <> 8);
Comme ça:
Dans la capture d'écran ci-dessus,
Nous avons utilisé plusieurs opérateurs non égaux "<>" pour obtenir une liste d'étudiants, qui ne sont égaux à aucun des ID suivants 2, 4, 6 ou 8. Cette requête renverra tous les autres étudiants autres que ces listes d'ID.
SQLite EXISTS
Les opérateurs EXISTS ne prennent aucun opérande; il ne prend qu'une clause SELECT après lui. L'opérateur EXISTS retournera true (1) s'il y a des lignes renvoyées par la clause SELECT, et il retournera false (0) s'il n'y a aucune ligne renvoyée par la clause SELECT.
Exemple:
Dans l'exemple suivant, nous sélectionnerons le nom du département, si l'ID du département existe dans la table des étudiants:
SELECT DepartmentNameDES Départements AS dWHERE 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 retournés. Et le nom du département " Math " ne sera pas renvoyé car il n'y a pas d'élève dans ce département, donc l'ID du département n'existe pas dans la table des étudiants. C'est pourquoi l'opérateur EXISTS a ignoré le département " Math ".
SQLite PAS
Inverse le résultat de l'opérateur précédent qui le suit. Par exemple:
- NOT BETWEEN - Il retournera true si BETWEEN renvoie false et vice versa.
- NOT LIKE - Il retournera true si LIKE renvoie false et vice versa.
- NOT GLOB - Il retournera true si GLOB renvoie false et vice versa.
- NOT EXISTS - Il retournera true si EXISTS renvoie false 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 Students, ce qui est le résultat inverse de l'opérateur EXISTS. Ainsi, la recherche sera effectuée via DepartmentId qui n'existe pas dans la table department.
SELECT DepartmentNameDES Départements AS dO N'EXISTE PAS (SELECT DepartmentIdFROM étudiants AS sWHERE d.DepartmentId = s.DepartmentId);
Sortie :
Seul le département " Math " sera retourné. Parce que le département " Math " est le seul département, cela n'existe pas dans la table des étudiants.
Limitation et commande
Ordre SQLite
L'ordre SQLite consiste à trier votre résultat par une ou plusieurs expressions. Pour classer l'ensemble de 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 elle.
- Spécifiez l'expression avec laquelle classer 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 seraient triées par ordre croissant.
- Vous pouvez spécifier plus d'expressions en utilisant le "," entre eux.
Exemple
Dans l'exemple suivant, nous sélectionnerons tous les étudiants classés par leur nom mais par ordre décroissant, puis par le nom du département par ordre croissant:
SELECT s.StudentName, d.DepartmentNameFROM étudiants AS sINNER JOIN Départements AS d ON s.DepartmentId = d.DepartmentIdORDER BY d.DepartmentName ASC, s.StudentName DESC;
Cela vous donnera:
- SQLite classera d'abord tous les étudiants par le nom de leur département dans l'ordre croissant
- Ensuite, pour chaque nom de département, tous les étudiants sous ce nom de département seront affichés dans l'ordre décroissant de leurs noms
Limite SQLite:
Vous pouvez limiter le nombre de lignes renvoyées par votre requête SQL à l'aide de 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, " LIMIT 4 OFFSET 4 " ignorera les 4 premières lignes et retournera 4 lignes à partir des cinquième lignes, vous obtiendrez donc 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 ne retournerons que 3 étudiants à partir de l'ID étudiant 5 en utilisant la requête:
SÉLECTIONNER * DES ÉTUDIANTS LIMIT 4,3;
Cela ne vous donnera que 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 supprimer ces doublons et renvoyer des valeurs distinctes. Vous pouvez spécifier plusieurs colonnes après le travail de la touche 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, Physique et Arts.
SELECT d.DepartmentNameFROM étudiants AS sINNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
Cela vous donnera des valeurs en double pour le nom du département:
Remarquez comment il existe des valeurs en double pour le nom du service. Maintenant, nous allons 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.DepartmentNameFROM étudiants AS sINNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
Cela ne vous donnera que trois valeurs uniques pour la colonne de nom de service:
Agrégat
Les agrégats SQLite sont des fonctions intégrées définies dans SQLite qui regrouperont plusieurs valeurs de plusieurs lignes en une seule valeur.
Voici les agrégats pris en charge par SQLite:
SQLite AVG ()
Renvoyé la moyenne pour toutes les valeurs x.
Exemple:
Dans l'exemple suivant, nous obtiendrons la note moyenne que les étudiants obtiendront pour 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 marques divisées par leur nombre.
COUNT () - COUNT (X) ou COUNT (*)
Renvoie le décompte total du nombre de fois où la valeur x est apparue. Et voici quelques options que vous pouvez utiliser avec COUNT:
- COUNT (x): ne compte que 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 x qui obtiendra le nombre des 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 décompte de tous les identifiants de service 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 de service. 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 a les options suivantes:
- GROUP_CONCAT (X): Ceci concaténera toute la valeur 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, la valeur de y étant 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): Ceci 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 de GROUP_CONCAT (DepartmentName)
La requête suivante concaténera toutes les valeurs du nom de département des étudiants et de la table des départements dans une chaîne séparée par des virgules. Donc, au lieu de renvoyer une liste de valeurs, une valeur sur chaque ligne. Il ne retournera qu'une seule valeur sur une ligne, avec toutes les valeurs séparées par des virgules:
SELECT GROUP_CONCAT (d.DepartmentName)FROM étudiants AS sINNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
Cela vous donnera:
Cela vous donnera la liste des valeurs des noms de 8 départements concaténées en une chaîne séparée par des virgules.
Exemple de GROUP_CONCAT (DISTINCT DepartmentName)
La requête suivante concaténera les valeurs distinctes du nom du département de la table des étudiants et des départements dans une chaîne séparée par des virgules:
SELECT GROUP_CONCAT (DISTINCT d.DepartmentName)FROM étudiants AS sINNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;
Cela vous donnera:
Remarquez comment le résultat est différent du résultat précédent; seulement trois valeurs renvoyées qui sont les noms de départements distincts, et les valeurs en double ont été supprimées.
Exemple de GROUP_CONCAT (DepartmentName, '&')
La requête suivante concaténera toutes les valeurs de la colonne de nom de 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 étudiants AS sINNER 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 MAX () et MIN ()
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 des 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 de la table " Marques ":
SELECT MAX (Mark), MIN (Mark) FROM Marks;
Cela vous donnera:
SOMME SQLite (x), Total (x)
Les deux renverront la somme de toutes les valeurs x. Mais ils sont différents dans ce qui suit:
- 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 nombre entier. Cependant, si les valeurs ne sont pas un nombre entier, il renverra une valeur à virgule flottante.
Exemple
Dans la requête suivante, nous utiliserons SOMME et total pour obtenir la somme de toutes les marques dans les tables " Marques ":
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 "Mark" peuvent être des nombres entiers.
Différence entre l'exemple SUM et TOTAL:
Dans la requête suivante, nous montrerons la différence entre SUM et TOTAL lorsqu'ils obtiendront la somme des valeurs NULL:
SELECT SUM (Mark), TOTAL (Mark) FROM Marks WHERE TestId = 4;
Cela vous donnera:
Notez qu'il n'y a pas de marques pour TestId = 4, donc il y a 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 est utilisée pour 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 (arrangées) ensemble en groupes.
Pour toute autre colonne qui n'est pas incluse dans le groupe par colonnes, 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 StudentsCountFROM étudiants AS sINNER JOIN Départements AS d ON s.DepartmentId = d.DepartmentIdGROUPE PAR d. Nom du département;
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 dessus.
Clause HAVING
Si vous souhaitez filtrer les groupes renvoyés par la clause GROUP BY, vous pouvez 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 StudentsCountFROM étudiants AS sINNER JOIN Départements AS d ON s.DepartmentId = d.DepartmentIdGROUPE PAR d. Nom du départementHAVING COUNT (s.StudentId) = 2;
Cela vous donnera:
La clause HAVING COUNT (S.StudentId) = 2 filtrera les groupes renvoyés et renverra uniquement les groupes contenant exactement deux étudiants. Dans notre cas, le département des Arts compte 2 étudiants, il est donc affiché dans la sortie.
Requête et sous-requête SQLite
Dans n'importe quelle requête, vous pouvez utiliser une autre requête dans une requête SELECT, INSERT, DELETE, UPDATE ou dans une autre sous-requête.
Cette requête imbriquée est appelée une sous-requête. Nous allons maintenant voir quelques exemples d'utilisation de sous-requêtes dans la clause SELECT. Cependant, dans le didacticiel sur la modification des données, nous verrons comment utiliser les 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:
SÉLECTIONNERs.StudentName, t.MarkFROM étudiants AS sJOINTURE INTERNE(SÉLECTIONNER StudentId, MarkFROM Tests AS tINNER JOIN Marks AS m ON t.TestId = m.TestId) ON s.StudentId = t.StudentId;
La requête:
SÉLECTIONNER StudentId, MarkFROM Tests AS tINNER JOIN Marks AS m ON t.TestId = m.TestId
La requête ci-dessus est ici appelée sous-requête car elle est imbriquée dans la clause FROM. Notez que nous lui avons donné un nom d'alias "t" afin de pouvoir 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 DepartmentNameDES Départements AS dO N'EXISTE PAS (SELECT DepartmentIdFROM étudiants AS sWHERE d.DepartmentId = s.DepartmentId);
La requête:
SELECT DepartmentIdFROM étudiants AS sWHERE d.DepartmentId = s.DepartmentId
La requête ci-dessus est ici appelée 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. Quel est le département "Math" ici.
Définir les opérations - UNION, Intersection
SQLite prend en charge les opérations SET suivantes:
UNION & UNION TOUS
Il combine un ou plusieurs ensembles de résultats (un groupe de lignes) renvoyés par plusieurs instructions SELECT en un seul ensemble de résultats.
UNION renverra des valeurs distinctes. Cependant, UNION ALL n'inclura pas et inclura des doublons.
Notez que le nom de la colonne sera le nom de la colonne spécifié dans la première instruction SELECT.
Exemple 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 EtudiantsSYNDICATSELECT DepartmentId FROM Departments;
Cela vous donnera:
La requête ne renvoie que 5 lignes qui sont les valeurs d'ID de service distinctes. Notez la première valeur qui est la valeur nulle.
Exemple SQLite UNION ALL
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 EtudiantsUNION TOUTSELECT 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 y a 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 INTERSECT
Renvoie les valeurs existantes dans les deux jeux de résultats combinés. Les valeurs qui existent dans l'un des jeux 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 EtudiantsCouperSELECT 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 null n'existe que 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 voulez uniquement les lignes de list1 qui n'existe pas dans list2, vous pouvez utiliser la clause "EXCEPT". La clause EXCEPT compare les deux listes et renvoie les lignes qui existent dans list1 et qui n'existent pas dans list2.
Exemple
Dans la requête suivante, nous sélectionnerons les valeurs DepartmentId qui existent dans la table department et qui n'existent pas dans la table Students:
CHOISIR DepartmentId FROM DepartmentsSAUFSELECT DepartmentId FROM étudiants;
Cela vous donnera:
La requête renvoie uniquement la valeur 4. Il s'agit de 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 SQLite. Il est utilisé pour représenter une valeur inconnue ou manquante. Notez que la valeur nulle est totalement différente de la valeur " 0 " ou vide "". Cependant, étant donné que 0 et la valeur vide sont une valeur connue, la valeur nulle est inconnue.
Les valeurs NULL nécessitent une manipulation particulière 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 qui ont une valeur DepartmentId nulle:
SELECT * FROM Etudiants WHERE DepartmentId = NULL;
Cette requête ne donnera aucun résultat:
Étant donné que la valeur NULL n'est égale à aucune autre valeur incluse 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 l' opérateur "IS NULL" pour rechercher des valeurs nulles comme suit:
SELECT * FROM Students WHERE DepartmentId EST NULL;
Cela vous donnera:
La requête renverra les étudiants qui ont une valeur DepartmentId nulle.
- Si vous voulez obtenir ces valeurs qui ne sont pas nulles, vous devez utiliser l' opérateur " IS NOT NULL " comme ceci:
SELECT * FROM Students O DepartmentId N'EST PAS NULL;
Cela vous donnera:
La requête renverra les étudiants qui n'ont pas de valeur DepartmentId NULL.
Résultats conditionnels
Si vous disposez d'une liste de valeurs et que vous souhaitez sélectionner l'une d'entre elles en fonction de certaines conditions. Pour cela, la condition pour 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 la note comme suit:
- «Excellent» si la note est supérieure à 85.
- "Très bien" si la note est comprise entre 70 et 85.
- "Bon" 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 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:
CASQUAND condition1 ALORS résultat1QUAND condition2 ALORS résultat2WHEN condition3 THEN result3… ELSE resultnFINIR
- Vous pouvez également utiliser une seule expression et choisir parmi différentes valeurs possibles:
Expression CASEQUAND valeur1 ALORS résultat1QUAND valeur2 ALORS résultat2WHEN value3 THEN result3… ELSE restulnFINIR
Notez que la clause ELSE est facultative.
Exemple
Dans l'exemple suivant, nous utiliserons l' expression CASE avec la valeur NULL dans la colonne Department Id de la table Students pour afficher le texte `` No Department '' comme suit:
SÉLECTIONNERNom d'étudiant,CASLORSQUE DepartmentId EST NULL ALORS 'No Department'Département ELSEIdEND AS DepartmentIdDES étudiants;
- L'opérateur CASE vérifiera la valeur de 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.
- Si ce n'est pas 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 facilitera la lecture, la maintenance et la compréhension des requêtes.
Une expression de table commune peut être définie en plaçant la clause WITH devant une instruction SELECT comme suit:
AVEC CTEnomCOMME(Instruction SELECT)SELECT, UPDATE, INSERT ou instruction de mise à jour ici FROM CTE
Le " CTEname " est n'importe quel nom que vous pouvez donner au CTE, vous pouvez l'utiliser pour vous y référer plus tard. Notez que vous pouvez définir l'instruction SELECT, UPDATE, INSERT ou DELETE sur les CTE
Voyons maintenant un exemple d'utilisation de 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:
AVEC AllDepartmentsCOMME(SELECT DepartmentId, DepartmentNameDES Départements)SÉLECTIONNERs.StudentId,s.StudentName,a.DepartmentNameFROM étudiants AS sINNER 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 « AllDepartments ». Ce CTE a été défini à partir d'une requête SELECT:
SELECT DepartmentId, DepartmentNameDES Départements
Ensuite, après avoir défini le CTE, nous l'avons utilisé dans la requête SELECT qui vient après.
Notez que les expressions de table communes n'affectent pas la sortie 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 la 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 la sortie 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
- Le nom des élèves est séparé par une virgule et
- Montrant le département comptant au moins trois étudiants
SÉLECTIONNERd.DepartmentName,COUNT (s.StudentId) StudentsCount,GROUP_CONCAT (StudentName) AS étudiantsDES Départements AS dINNER JOIN Students AS s ON s.DepartmentId = d.DepartmentIdGROUP BY d.DepartmentNameHAVING COUNT (s.StudentId)> = 3;
Nous avons ajouté une clause JOIN 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 pour chaque groupe de départements.
- GROUP_CONCAT pour concaténer les étudiants pour 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é:
Il s'agissait d'une introduction à l'écriture de requêtes SQLite et aux bases de l'interrogation de la base de données et à la façon dont vous pouvez filtrer les données renvoyées. Vous pouvez maintenant écrire vos propres requêtes SQLite.