SQLite 查询：Select, Where, LIMIT, OFFSET, Count, Group By

要编写SQLite数据库中的SQL查询，您需要了解SELECT、FROM、WHERE、GROUP BY、ORDER BY和LIMIT子句的工作原理以及如何使用它们。

在本教程中，您将学习如何使用这些子句以及如何编写SQLite子句。

使用Select读取数据

SELECT 子句是您用来查询SQLite数据库的主要语句。在SELECT子句中，您说明要选择什么。但在SELECT子句之前，让我们看看如何使用FROM子句选择数据。

FROM 子句用于指定要从中选择数据的位置。在FROM子句中，您可以指定一个或多个表或子查询来选择数据，我们将在后续教程中看到。

请注意，对于以下所有示例，您都需要运行 sqlite3.exe 并按如下方式打开与示例数据库的连接

步骤 1)在此步骤中，

打开“我的电脑”并导航到目录“C:\sqlite”，然后
接着打开“sqlite3.exe”

第 2 步：使用以下命令打开数据库“TutorialsSampleDB.db”

现在您已准备好在数据库上运行任何类型的查询。

在SELECT子句中，您不仅可以选择列名，还有许多其他选项来指定要选择的内容。如下所示：

SELECT *

此命令将从FROM子句中引用的所有表（或子查询）中选择所有列。例如：

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

这将从students和departments两个表中选择所有列。

SELECT tablename.*

这将仅从“tablename”表中选择所有列。例如：

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

这将仅从students表中选择所有列。

文字值

文字值是可以在SELECT语句中指定的常量值。您可以使用文字值，就像在SELECT子句中使用列名一样。这些文字值将为SQL查询返回的每一行显示。

以下是一些您可以选择的不同文字值的示例：

数字文字 – 任何格式的数字，例如 1、2.55 等。
字符串文字 – 任何字符串 ‘USA’、‘this is a sample text’ 等。
NULL – NULL 值。
Current_TIME – 这将提供当前时间。
CURRENT_DATE – 这将提供当前日期。

这在您需要为所有返回的行选择常量值的情况下可能很有用。例如，如果您想从Students表中选择所有学生，并添加一个名为country的新列，其值为“USA”，您可以这样做：

SELECT *, 'USA' AS Country FROM Students;

这将为您提供所有学生列，以及一个名为Country的新列，如下所示：

请注意，这个名为Country的新列实际上并不是添加到表中的新列。它是一个虚拟列，在查询中创建用于显示结果，并且不会在表中创建。

名称和别名

别名是列的新名称，允许您使用新名称选择该列。列别名使用“AS”关键字指定。

例如，如果您想将StudentName列显示为“Student Name”而不是“StudentName”，您可以为其指定别名，如下所示：

SELECT StudentName AS 'Student Name' FROM Students;

这将以“Student Name”而不是“StudentName”给出您的学生姓名，如下所示：

请注意，列名仍然是“StudentName”；StudentName列本身没有改变，它不会因别名而改变。

别名不会更改列名；它只会更改SELECT子句中的显示名称。

另外，请注意，“AS”关键字是可选的，您可以省略它直接使用别名，如下所示：

SELECT StudentName 'Student Name' FROM Students;

它将为您提供与上一个查询完全相同的输出。

您也可以为表指定别名，而不仅仅是列。使用相同的“AS”关键字。例如，您可以这样做：

SELECT s.* FROM Students AS s;

这将给出Students表中的所有列。

如果您连接多个表，这会非常有用；您不必在查询中重复完整的表名，可以为每个表指定一个简短的别名。例如，在以下查询中：

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

此查询将从“Students”表和“Departments”表中选择每个学生的名字及其部门名称。

然而，相同的查询可以这样写：

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

我们将Students表命名为别名“s”，并将departments表命名为别名“d”。
然后，我们使用它们的别名来引用表，而不是使用完整的表名。
INNER JOIN 使用条件将两个或多个表连接在一起。在我们的示例中，我们将Students表与Departments表通过DepartmentId列连接。在“SQLite Joins”教程中也有对INNER JOIN的深入解释。

这将为您提供与上一个查询完全相同的输出。

WHERE

仅使用SELECT子句和FROM子句（如前一节所示）编写SQL查询将返回表中的所有行。但是，如果您想过滤返回的数据，则必须添加“WHERE”子句。

WHERE 子句用于过滤由 SQL查询返回的结果集。这就是WHERE子句的工作原理：

在WHERE子句中，您可以指定一个“表达式”。
该表达式将针对FROM子句中指定的表返回的每一行进行计算。
该表达式将作为布尔表达式进行计算，结果为真、假或NULL。
然后，只有表达式计算结果为真的行将被返回，而结果为假或NULL的行将被忽略，不包含在结果集中。
要使用WHERE子句过滤结果集，您必须使用表达式和运算符。

SQLite运算符列表及其使用方法

在下一节中，我们将解释如何使用表达式和运算符进行过滤。

表达式是一个或多个文字值或列，它们与运算符组合在一起。

请注意，您可以在SELECT子句和WHERE子句中使用表达式。

在以下示例中，我们将在SELECT子句和WHERE子句中尝试表达式和运算符。以向您展示它们的性能。

有不同类型的表达式和运算符，您可以如下指定：

SQLite 字符串连接运算符“||”

此运算符用于连接一个或多个文字值或列。它将从所有连接的文字值或列中生成一个字符串结果。例如：

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

这将连接成一个名为“StudentIdWithName”的新别名。

文字字符串值“Id with Name: ”
与“StudentId”列的值，以及
来自“StudentName”列的值。

SQLite CAST 运算符

CAST 运算符用于将一个数据类型的值转换为另一个数据类型。

例如，如果有一个数字值存储为字符串值，如“‘12.5’”，您想将其转换为数字值，您可以使用CAST运算符执行此操作，例如“CAST( ‘12.5’ AS REAL)”。或者，如果您有一个小数如12.5，并且需要只获取整数部分，您可以将其转换为整数，如下所示“CAST(12.5 AS INTEGER)”。

示例

在以下命令中，我们将尝试将不同值转换为其他数据类型。

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

这将为您提供：

结果如下：

CAST(‘12.5’ AS REAL) – ‘12.5’ 是字符串值，它将被转换为 REAL 值。
CAST(12.5 AS INTEGER) – 12.5 是一个小数，它将被转换为整数值。小数部分将被截断，变为 12。

SQLite 算术运算符

取两个或多个数字文字值或数字列，并返回一个数字值。SQLite 支持的算术运算符是：

加法“+” – 提供两个操作数的和。减法“–” – 减去两个操作数，得到差。乘法“*” – 两个操作数的乘积。余数（模）“%” – 返回一个操作数除以第二个操作数后得到的余数。除法“/” – 返回左操作数除以右操作数得到的商。

示例

在以下示例中，我们将使用相同的字面数字值测试所有五个算术运算符。

SELECT 子句。

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

这将为您提供：

请注意，此处我们使用了没有 FROM 子句的 SELECT 语句。在 SQLite 中这是允许的，只要我们选择字面值。

SQLite 比较运算符

比较两个操作数，并返回真或假，如下所示：

“<” – 如果左操作数小于右操作数，则返回真。 “<=” – 如果左操作数小于或等于右操作数，则返回真。 “>” – 如果左操作数大于右操作数，则返回真。 “>=” – 如果左操作数大于或等于右操作数，则返回真。 “=” 和 “==” – 如果两个操作数相等，则返回真。请注意，这两个运算符是相同的，没有任何区别。 “!=” 和 “<>” – 如果两个操作数不相等，则返回真。请注意，这两个运算符是相同的，没有任何区别。

请注意，SQLite 将真值表示为 1，将假值表示为 0。

示例

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 '<>';

这将产生类似这样的结果：

SQLite 模式匹配运算符

“LIKE” – 用于模式匹配。使用“Like”，您可以搜索与使用通配符指定的模式匹配的值。

左侧的操作数可以是字符串文字值或字符串列。模式可以指定如下：

包含模式。例如，StudentName LIKE ‘%a%’ – 这将搜索包含字母“a”的学生姓名，无论其在StudentName列的哪个位置。
以模式开头。例如，“StudentName LIKE ‘a%’” – 搜索以字母“a”开头的学生姓名。
以模式结尾。例如，“StudentName LIKE ‘%a’” – 搜索以字母“a”结尾的学生姓名。
使用下划线字符“_”匹配字符串中的任何单个字符。例如，“StudentName LIKE ‘J___’” – 搜索长度为4个字符的学生姓名。它必须以字母“J”开头，并且后面可以有另外三个字符。

模式匹配示例

获取以字母‘j’开头的学生姓名。

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

结果

获取以字母‘y’结尾的学生姓名。

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

结果

获取包含字母‘n’的学生姓名。

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

结果

“GLOB” – 等同于LIKE运算符，但GLOB区分大小写，而LIKE运算符不区分。例如，以下两个命令将返回不同的结果：

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

这将为您提供：

第一个语句返回0（假），因为GLOB运算符区分大小写，所以‘j’不等于‘J’。但是，第二个语句将返回1（真），因为LIKE运算符不区分大小写，所以‘j’等于‘J’。

其他运算符

SQLite AND

一个逻辑运算符，用于组合一个或多个表达式。它仅当所有表达式都产生“真”值时才返回真。但是，仅当所有表达式都产生“假”值时才返回假。

示例

以下查询将搜索StudentId大于5且StudentName以字母N开头的学生，返回的学生必须满足这两个条件：

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

在上面的屏幕截图中，输出只会显示“Nancy”。Nancy是唯一满足两个条件的学生。

SQLite OR

一个逻辑运算符，用于组合一个或多个表达式，因此如果其中一个组合的运算符产生真，那么它将返回真。但是，如果所有表达式都产生假，它将返回假。

示例

以下查询将搜索StudentId大于5或StudentName以字母N开头的学生，返回的学生必须满足至少一个条件：

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

这将为您提供：

在上面的屏幕截图中，输出将显示名称中包含字母“n”的学生名称，以及Student ID值为大于5的学生。

您可以看到结果与AND运算符的查询不同。

SQLite BETWEEN

BETWEEN 用于选择那些在两个值范围内的值。例如，“X BETWEEN Y AND Z”当值X在Y和Z两个值之间时，将返回真（1）。否则，它将返回假（0）。“X BETWEEN Y AND Z”等同于“X >= Y AND X <= Z”，X必须大于或等于Y，X必须小于或等于Z。

示例

在以下示例查询中，我们将编写一个查询来获取ID值在5到8之间的学生：

SELECT *
FROM Students
WHERE StudentId BETWEEN 5 AND 8;

这将仅返回ID为5、6、7和8的学生。

SQLite IN

接受一个操作数和一个操作数列表。如果第一个操作数的值等于列表中的某个操作数的值，则返回真。IN运算符在操作数列表中包含第一个操作数的值时，返回真（1）。否则，它将返回假（0）。

如下：“col IN(x, y, z)”。这等同于“(col=x) or (col=y) or (col=z)”。

示例

以下查询将仅选择ID为2、4、6、8的学生。

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

像这样：

之前的查询将给出与以下查询完全相同的结果，因为它们是等效的：

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

两个查询都给出相同的结果。但是，两个查询之间的区别在于，第一个查询使用了“IN”运算符。第二个查询使用了多个“OR”运算符。

IN运算符等同于使用多个OR运算符。“WHERE StudentId IN(2, 4, 6, 8)”等同于“WHERE (StudentId = 2) OR (StudentId = 4) OR (StudentId = 6) OR (StudentId = 8);”。

像这样：

SQLite NOT IN

“NOT IN”操作数是IN操作数的相反。但语法相同；它接受一个操作数和一系列操作数。如果第一个操作数的值不等于列表中的任何一个操作数的值，则返回真。即，如果操作数列表不包含第一个操作数，则返回真（0）。如下：“col NOT IN(x, y, z)”。这等同于“(col<>x) AND (col<>y) AND (col<>z)”。

示例

以下查询将选择ID不等于2、4、6、8这些ID之一的学生。

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

像这样：

之前的查询与以下查询给出相同的结果，因为它们是等效的：

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

像这样：

在上面的屏幕截图中：

我们使用了多个不等于运算符“<>”来获取一个学生列表，这些学生不等于以下ID中的任何一个：2、4、6或8。此查询将返回除这些ID列表之外的所有其他学生。

SQLite EXISTS

EXISTS运算符不接受任何操作数；它只接受其后的SELECT子句。如果SELECT子句返回任何行，EXISTS运算符将返回真（1），如果SELECT子句没有返回任何行，它将返回假（0）。

示例

在以下示例中，如果部门ID存在于students表中，我们将选择部门名称。

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

这将为您提供：

只有“IT、Physics 和 Arts”这三个部门会被返回。“Math”部门名称将不会被返回，因为该部门没有学生，所以部门ID不存在于students表中。这就是为什么EXISTS运算符忽略了“Math”部门。

SQLite NOT

反转其后面的运算符的结果。例如：

NOT BETWEEN – 如果BETWEEN返回假，它将返回真，反之亦然。
NOT LIKE – 如果LIKE返回假，它将返回真，反之亦然。
NOT GLOB – 如果GLOB返回假，它将返回真，反之亦然。
NOT EXISTS – 如果EXISTS返回假，它将返回真，反之亦然。

示例

在以下示例中，我们将使用NOT运算符和EXISTS运算符来获取不存在于Students表中的部门名称，这是EXISTS运算符的相反结果。因此，搜索将通过不存在于department表中的DepartmentId进行。

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

输出:

将只返回“Math”部门。因为“Math”部门是唯一一个不存在于students表中的部门。

限制和排序

SQLite Order

SQLite Order 用于按一个或多个表达式对结果进行排序。要对结果集进行排序，您必须使用ORDER BY子句，如下所示：

首先，您必须指定ORDER BY子句。
ORDER BY子句必须放在查询的末尾；只有LIMIT子句可以放在它后面。
指定用于排序数据的表达式，该表达式可以是列名或表达式。
在表达式之后，您可以指定一个可选的排序方向。可以是DESC（降序）或ASC（升序）。如果您没有指定任何一个，数据将按升序排序。
您可以使用“,”在表达式之间指定更多表达式。

示例

在以下示例中，我们将按学生姓名降序排序，然后按部门名称升序排序，选择所有学生：

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;

这将为您提供：

SQLite将首先按升序排序所有学生，按其部门名称。
然后，对于每个部门名称，该部门下的所有学生将按姓名降序显示。

SQLite Limit

您可以使用LIMIT子句限制SQL查询返回的行数。例如，LIMIT 10 只返回10行，忽略所有其他行。

在LIMIT子句中，您可以使用OFFSET子句选择从特定位置开始的特定数量的行。例如，“LIMIT 4 OFFSET 4”将忽略前4行，并从第五行开始返回4行，因此您将获得第5、6、7和8行。

请注意，OFFSET子句是可选的，您可以将其写成“LIMIT 4, 4”，它将为您提供完全相同的结果。

示例:

在以下示例中，我们将使用查询返回从学生ID 5开始的3个学生：

SELECT * FROM Students LIMIT 4,3;

这将只为您提供从第5行开始的三个学生。因此，它将为您提供ID为5、6和7的学生。

删除重复项

如果您的SQL查询返回重复值，您可以使用“DISTINCT”关键字删除这些重复项并仅返回唯一值。您可以在DISTINCT关键字后指定多个列。

示例

以下查询将返回重复的“部门名称值”：这里我们有IT、Physics和Arts等重复值。

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

这将为您提供部门名称的重复值。

注意部门名称有多少重复值。现在，我们将使用与上述查询相同的查询来使用DISTINCT关键字，以删除这些重复项并仅获取唯一值。如下：

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

这将为您提供部门名称列的三个唯一值。

聚合

SQLite Aggregates 是SQLite中定义的内置函数，它将多个行的多个值分组为一个值。

以下是SQLite支持的聚合函数：

SQLite AVG()

返回x值的平均值。

示例

在以下示例中，我们将计算所有学生在所有考试中获得的平均分数。

SELECT AVG(Mark) FROM Marks;

这将为您提供值“18.375”。

这些结果来自所有标记值的总和除以它们的计数。

COUNT() – COUNT(X) 或 COUNT(*)

返回x值出现次数的总计数。以下是一些您可以与COUNT一起使用的选项：

COUNT(x): 仅计算x值，其中x是列名。它将忽略NULL值。
COUNT(*): 计算所有列的所有行。
COUNT (DISTINCT x): 您可以在x之前指定DISTINCT关键字，它将计算x的唯一值的计数。

示例

在以下示例中，我们将使用COUNT(DepartmentId)、COUNT(*)和COUNT(DISTINCT DepartmentId)获取Departments的总计数，并说明它们的区别。

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

这将为您提供：

如下：

COUNT(DepartmentId) 将计算所有部门ID的计数，并忽略NULL值。
COUNT(DISTINCT DepartmentId) 计算DepartmentId的唯一值，只有3个。即部门名称的三个不同值。请注意，在学生姓名中有8个部门名称值。但只有三个不同的值，即Math、IT和Physics。
COUNT(*) 计算students表中的行数，即10名学生共有10行。

GROUP_CONCAT() – GROUP_CONCAT(X) 或 GROUP_CONCAT(X,Y)

GROUP_CONCAT聚合函数将多个值连接为一个值，并用逗号分隔它们。它具有以下选项：

GROUP_CONCAT(X): 这将连接x的所有值到一个字符串，使用逗号“,”作为值之间的分隔符。NULL值将被忽略。
GROUP_CONCAT(X, Y): 这将连接x的值到一个字符串，使用y的值作为每个值之间的分隔符，而不是默认分隔符“,”。NULL值也将被忽略。
GROUP_CONCAT(DISTINCT X): 这将连接x的所有唯一值到一个字符串，使用逗号“,”作为分隔符。NULL值也将被忽略。

GROUP_CONCAT(DepartmentName) 示例

以下查询将把students表和departments表中的所有部门名称值连接成一个以逗号分隔的字符串。所以，它不会返回多行列表，而是只在一行中返回一个值，其中所有值都用逗号分隔。

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

这将为您提供：

这将为您提供8个部门名称值的列表，用逗号分隔。

GROUP_CONCAT(DISTINCT DepartmentName) 示例

以下查询将把students表和departments表中的部门名称的唯一值连接成一个以逗号分隔的字符串。

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

这将为您提供：

注意结果与之前的结果不同；只返回了三个值，即唯一的部门名称，并且删除了重复值。

GROUP_CONCAT(DepartmentName ,’&’) 示例

以下查询将把students表和departments表中的部门名称列的所有值连接成一个字符串，但使用字符‘&’作为分隔符，而不是逗号。

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

这将为您提供：

注意字符“&”被用作分隔符，而不是默认字符“,”。

SQLite MAX() & MIN()

MAX(X) 返回X值中的最高值。如果x的所有值都为null，MAX将返回NULL值。而MIN(X) 返回X值中的最小值。如果X的所有值都为null，MIN将返回NULL值。

示例

在以下查询中，我们将使用MIN和MAX函数从“Marks”表中获取最高分和最低分。

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

这将为您提供：

SQLite SUM(x), TOTAL(x)

两者都将返回所有x值的总和。但它们在以下方面有所不同：

如果所有值都为NULL，SUM将返回NULL，但Total将返回0。
TOTAL始终返回浮点值。如果x的所有值都是整数，SUM将返回整数值。但是，如果值不是整数，它将返回一个浮点值。

示例

在以下查询中，我们将使用SUM和TOTAL来计算“Marks”表中的所有分数的总和。

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

这将为您提供：

如您所见，TOTAL始终返回浮点数。但SUM返回整数值，因为“Mark”列中的值可能是整数。

SUM和TOTAL区别示例

在以下查询中，我们将展示SUM和TOTAL在计算NULL值总和时的区别。

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

这将为您提供：

请注意，TestId = 4没有分数，因此该测试的分数为NULL值。SUM返回NULL值（空白），而TOTAL返回0。

Group BY

GROUP BY 子句用于指定一个或多个用于将行分组的列。具有相同值的行将被收集（排列）在一起分组。

对于未包含在group by列中的任何其他列，您可以使用聚合函数。

示例

以下查询将给出每个部门的学生总数。

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;

这将为您提供：

GROUPBY DepartmentName子句会将所有学生分组，每个部门名称一个组。对于每个“部门”组，它将计算该部门的学生数量。

HAVING子句

如果您想过滤由GROUP BY子句返回的组，那么您可以指定一个“HAVING”子句，后面跟着一个表达式。该表达式将用于过滤这些组。

示例

在以下查询中，我们将选择只有两个学生的部门。

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;

这将为您提供：

COUNT(S.StudentId) = 2 的 HAVING 子句将过滤返回的组，并且只返回包含恰好两个学生的组。在我们的案例中，Arts部门有2名学生，因此它会显示在输出中。

SQLite查询与子查询

在任何查询内部，您都可以在SELECT、INSERT、DELETE、UPDATE或另一个子查询中使用另一个查询。

这个嵌套查询称为子查询。我们现在将看到在SELECT子句中使用子查询的示例。但是，在修改数据教程中，我们将看到如何在INSERT、DELETE和UPDATE语句中使用子查询。

在FROM子句中使用子查询示例

在以下查询中，我们将在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;

查询：

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

上面的查询之所以称为子查询，是因为它嵌套在FROM子句中。请注意，我们为其指定了一个别名“t”，以便我们可以在查询中引用它返回的列。

此查询将为您提供：

所以，在我们的例子中：

s.StudentName 是从主查询中选择的，它提供学生姓名，而
t.Mark 是从子查询中选择的，它提供这些学生获得的分数。

在WHERE子句中使用子查询示例

在以下查询中，我们将在WHERE子句中包含一个子查询。

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

查询：

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

上面的查询之所以称为子查询，是因为它嵌套在WHERE子句中。子查询将返回DepartmentId值，这些值将被NOT EXISTS运算符使用。

此查询将为您提供：

在上面的查询中，我们选择了没有学生注册的部门，即这里的“Math”部门。

集合操作 – UNION, INTERSECT

SQLite 支持以下 SET 操作：

UNION & UNION ALL

它将多个SELECT语句返回的一个或多个结果集（一组行）组合成一个结果集。

UNION 将返回唯一值。然而，UNION ALL 不会，并且会包含重复项。

请注意，列名将是第一个SELECT语句中指定的列名。

UNION 示例

在以下示例中，我们将获取students表中的DepartmentId列表和departments表中的DepartmentId列表在同一列中。

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

这将为您提供：

该查询仅返回5行，即唯一的部门ID值。注意第一个值是NULL值。

SQLite UNION ALL 示例

在以下示例中，我们将获取students表中的DepartmentId列表和departments表中的DepartmentId列表在同一列中。

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

这将为您提供：

该查询将返回14行，10行来自students表，4行来自departments表。请注意，返回的值中有重复项。另外，请注意列名是第一个SELECT语句中指定的。

现在，让我们看看如果用UNION替换UNION ALL会产生什么不同的结果。

SQLite INTERSECT

返回两个组合结果集中都存在的值。存在于一个组合结果集中的值将被忽略。

示例

在以下查询中，我们将选择DepartmentId列中同时存在于Students和Departments表中的DepartmentId值。

SELECT DepartmentId FROM Students
Intersect
SELECT DepartmentId FROM Departments;

这将为您提供：

该查询仅返回三个值：1、2和3。这些是同时存在于两个表中的值。

但是，NULL和4这两个值没有被包含，因为NULL值仅存在于students表中，而不存在于departments表中。值4存在于departments表中，而不存在于students表中。

因此，NULL和4这两个值都被忽略，没有包含在返回的值中。

EXCEPT

假设您有两个行列表，list1和list2，并且您只想要list1中不存在于list2中的行，您可以使用“EXCEPT”子句。EXCEPT子句比较这两个列表，并返回存在于list1而不存在于list2中的行。

示例

在以下查询中，我们将选择存在于departments表而不存在于students表中的DepartmentId值。

SELECT DepartmentId FROM Departments
EXCEPT
SELECT DepartmentId FROM Students;

这将为您提供：

该查询仅返回值4。这是唯一存在于departments表而不存在于students表中的值。

NULL 处理

“NULL”值是SQLite中的一个特殊值。它用于表示未知或缺失的值。请注意，null值与“0”或空字符串“”值完全不同。因为0和空字符串是已知值，而null值是未知的。

NULL值在SQLite中需要特殊处理，我们现在将看到如何处理NULL值。

搜索NULL值

您不能使用正常的相等运算符（=）来搜索null值。例如，以下查询搜索具有null DepartmentId值的学生：

SELECT * FROM Students WHERE DepartmentId = NULL;

此查询不会返回任何结果。

因为NULL值不等于任何其他值，包括它本身，所以它没有返回任何结果。

但是，为了使查询正常工作，您必须使用“IS NULL”运算符来搜索null值，如下所示：

SELECT * FROM Students WHERE DepartmentId IS NULL;

这将为您提供：

该查询将返回具有null DepartmentId值的学生。

如果您想获取非NULL值，则必须使用“IS NOT NULL”运算符，如下所示：

SELECT * FROM Students WHERE DepartmentId IS NOT NULL;

这将为您提供：

该查询将返回没有NULL DepartmentId值的学生。

条件结果

如果您有一个值列表，并且想根据某些条件选择其中的任何一个。为此，该特定条件的值必须为真才能被选中。

CASE表达式将为所有值评估这些条件列表。如果条件为真，它将返回该值。

例如，如果您有一个“Grade”列，并且想根据Grade值选择一个文本值，如下所示：

– 如果分数高于85，则为“优秀”。

– 如果分数在70到85之间，则为“非常好”。

– 如果分数在60到70之间，则为“良好”。

然后您可以使用CASE表达式来实现这一点。

这可以用来在SELECT子句中定义一些逻辑，以便您可以根据某些条件选择结果，例如if语句。

CASE运算符可以根据以下不同的语法进行定义：

您可以使用不同的条件：

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

或者，您可以使用一个表达式并提供不同的可能值来选择：

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

请注意，ELSE子句是可选的。

示例

在以下示例中，我们将使用CASE表达式和Students表中的DepartmentId列的NULL值来显示文本“No Department”，如下所示：

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

CASE运算符将检查DepartmentId的值是否为NULL。
如果它是NULL值，它将选择文字值‘No Department’而不是DepartmentId值。
如果不是NULL值，它将选择DepartmentId列的值。

这将为您提供如下所示的输出：

通用表表达式

通用表表达式（CTE）是在SQL语句内部使用给定名称定义的子查询。

它比子查询具有优势，因为它定义在SQL语句之外，并且可以使查询更易于阅读、维护和理解。

可以通过在SELECT语句前加上WITH子句来定义通用表表达式，如下所示：

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

“CTEname”是您可以为CTE指定的任何名称，您可以在以后引用它。请注意，您可以在CTE上定义SELECT、UPDATE、INSERT或DELETE语句。

现在，让我们看一个如何使用CTE在SELECT子句中的示例。

示例

在以下示例中，我们将定义一个来自SELECT语句的CTE，然后我们将在另一个查询中使用它。

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;

在此查询中，我们定义了一个CTE并将其命名为“AllDepartments”。此CTE是从SELECT查询定义的。

SELECT DepartmentId, DepartmentName
  FROM Departments

然后，在定义CTE之后，我们在它后面的SELECT查询中使用它。

请注意，通用表表达式不会影响查询的输出。它是一种定义逻辑视图或子查询以便在同一查询中重用的方法。通用表表达式就像您声明的一个变量，并将其用作子查询。只有SELECT语句会影响查询的输出。

此查询将为您提供：

高级查询

高级查询是包含复杂连接、子查询和一些聚合的查询。在接下来的部分中，我们将看到一个高级查询的示例。

其中我们获取：

每个部门的部门名称和所有学生。
学生姓名用逗号分隔，以及
显示至少有三个学生的部门。

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;

我们添加了一个JOIN子句以从Departments表中获取DepartmentName。之后，我们添加了一个GROUP BY子句和两个聚合函数。

“COUNT”用于计算每个部门组的学生数量。
GROUP_CONCAT用于将每个组的学生连接成一个逗号分隔的字符串。
在GROUP BY之后，我们使用了HAVING子句来过滤部门，只选择至少有3名学生的部门。

结果将如下所示：

摘要

这是关于编写SQLite查询以及数据库查询基础的介绍，以及如何过滤返回的数据。您现在可以编写自己的SQLite查询了。

使用Select读取数据

名称和别名

相关文章

WHERE

限制和排序

删除重复项

聚合

Group BY

SQLite查询与子查询

集合操作 – UNION, INTERSECT

NULL 处理

条件结果

通用表表达式

高级查询

摘要

订阅新闻通讯