一天学会sqlite--Chapter 6: 选择, 其中, 限定, 计数, 分组, 联合

要在 SQLite 数据库中编写 SQL 查询，必须了解 SELECT、FROM、WHERE、GROUP BY、ORDER BY 和 LIMIT 子句的工作原理和使用方法。

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

使用 select 读取数据

SELECT 子句是用于查询 SQLite 数据库的主要语句。在 SELECT 子句中，要说明选择什么。但在使用 SELECT 子句之前，我们先来看看使用 FROM 子句可以从哪里选择数据。

FROM 子句用于指定从何处选择数据。在 FROM 子句中，你可以指定从一个或多个表或子查询中选择数据，我们将在后面的教程中看到这一点。

请注意，以下所有示例都必须运行 sqlite3.exe，并按照流程打开与示例数据库的连接：

步骤 1） 在此步骤中、

1. 打开 “我的电脑”，并导航到以下目录 “ E:\sqlite ” 并点击 “”。
2. 然后打开 “ sqlite3.exe ”：

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

现在你可以在数据库中运行任何类型的查询了。

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

SELECT *

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

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

这将同时选择学生表和部门表中的所有列：

SELECT tablename.*

这将只选择表 “tablename ”中的所有列。例如

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

这将只选择学生表中的所有列：

A literal value

字面值是可以在 SELECT 语句中指定的常量值。使用字面值的方式与在 SELECT 子句中使用列名的方式相同。这些字面值将在 SQL 查询返回的每一行中显示。

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

• Numeric Literal – 任何格式的数字，如 1、2.55、......等。
• String literals – 任何字符串 “USA”、“this is a sample text”...等。
• NULL – 空值。
• Current_TIME – 显示当前时间。
• CURRENT_DATE – 显示当前日期。

在某些情况下，如果需要为所有返回行选择一个恒定值，这将非常方便。例如，如果要从学生表中选择所有学生，并在新列 country 中包含值 “USA”，就可以这样做：

SELECT *, 'USA' AS Country FROM Students;

这样就可以得到所有学生的列，再加上一个新列 “Country”，就像这样：

如果要连接多个表，这将非常有用；与其在查询中重复表的全名，不如给每个表取一个简短的别名。例如，在下面的查询中

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

该查询将从 “学生 ”表中选择每个学生姓名，并从 “系 ”表中选择其系名：

不过，同样的查询可以这样写：

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

• 我们给学生表取了一个别名 “s”，给部门表取了一个别名 “d”。
• 这样，我们就不再使用完整的表名，而是用它们的别名来指代它们了。
• 内连接（INNER JOIN）使用一个条件将两个或多个表连接在一起。在我们的示例中，我们将 “学生 ”表与带有 “DepartmentId ”列的 “部门 ”表连接起来。在 “SQLite 连接 ”教程中也有关于 INNER JOIN 的深入解释。

这样就可以得到与前面查询完全相同的输出结果：

WHERE

在编写 SQL 查询时，如果只使用 SELECT 子句和 FROM 子句（如上一节所述），就可以获得表中的所有记录。但是，如果要过滤返回的数据，就必须添加 “WHERE ”子句。

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

• 在 WHERE 子句中，可以指定一个 “表达式”。
• 从 FROM 子句中指定的表返回的每一条记录都将对该表达式进行评估。
• 该表达式将作为布尔表达式进行求值，求值结果为 true、false 或 null。
• 只有表达式求值为 true 的记录才会被返回，求值为 false 或 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 支持的算术运算符有

• 加法“+”——给出两个操作数的和。

• 减法（-）——将两个操作数相减，得到差值。□乘法（Multiplication）：

• *：两个操作数的乘积。

• 取余(模)“%”——给出一个操作数除以第二个操作数的余数。

• 除法“/”——返回左操作数除以右操作数的商。

示例：

在下面的示例中，我们将尝试在同一个选择子句中使用五个算术运算符和字面数值：

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

这将为您提供

请注意我们在这里使用了不带 FROM 子句的 SELECT 语句。在 SQLite 中，只要我们选择的是字面值，就允许这样做。

SQLite 比较操作符

将两个操作数进行比较，并返回如下的 “true ”或 “false”：

” < " - 如果左操作数小于右操作数，则返回 true。” <= " - 如果左操作数小于或等于右操作数，则返回 true。” > " - 如果左操作数大于右操作数，返回 true。” >= " - 如果左操作数大于或等于右操作数，则返回 true。” = “ 和 ” == " - 如果两个操作数相等，则返回 true。请注意，这两个运算符是相同的，它们之间没有区别。” != “ 和 ” <> " - 如果两个操作数不相等，则返回 true。请注意，这两个操作符是相同的，它们之间没有区别。

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

Example:

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%' - 这将搜索在 StudentName 列的任意位置包含字母 “a ”的学生姓名。
• 以模式开头。例如，“ StudentName LIKE ‘a%’ ” - 查找以字母 “a ”开头的学生姓名。
• 以模式结束。例如，“ StudentName LIKE ‘%a’ ” - 查找以字母 “a ”结尾的学生姓名。

• 使用下划线字母“_”匹配字符串中的任何单字符。例如，“ StudentName LIKE ‘J___’ ” - 搜索长度为 4 个字符的学生姓名。必须以字母 “J ”开头，并且在 “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 操作符，但与 LIKE 操作符不同，GLOB 区分大小写。例如，以下两条命令将返回不同的结果：

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

这会得到

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

其他运算符：

SQLite AND

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

示例：

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

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

在上面的截图中，输出结果将只显示 “Nancy”。南希是唯一同时满足这两个条件的学生。

SQLite OR

逻辑运算符，用于组合一个或多个表达式，如果组合运算符中的一个表达式为真，则返回 true。但是，如果所有表达式都为假，则返回假。

示例：

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

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

这将为您提供

在上面的截图中，输出结果是姓名中包含字母 “n ”的学生的姓名，以及值大于 5 的学生 ID。

可以看出，结果与使用 AND 运算符的查询不同。

SQLite BETWEEN

BETWEEN 用于选择在两个值范围内的值。例如，“ X BETWEEN Y AND Z ”将在值 X 位于两个值 Y 和 Z 之间时返回 true (1)，否则返回 false (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;

这将只给出编号为 5、6、7 和 8 的学生：

SQLite IN

接收一个操作数和一个操作数列表。如果第一个操作数的值等于操作数列表中的一个值，则返回 true。如果操作数列表中包含第一个操作数的值，IN 运算符将返回 true (1)。否则，返回 false (0)。

像这样 “ col IN(x, y, z) "。这相当于 “ (col=x) 或 (col=y) 或 (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 操作符相反。但语法相同；它接受一个操作数和一个操作数列表。也就是说，如果操作数列表中不包含第一个操作数，它将返回 true (0)。就像下面这样 “ col NOT IN(x, y, z) "。这相当于 “ (col<>x) AND (col<>y) AND (col<>z) ”。

示例：

下面的查询将选择 id 不等于 2、4、6、8 的学生：

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

像这样

The previous query we give the exact result as the following query, because they are equivalent:

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 操作符将返回 true (1)；如果 SELECT 子句没有返回任何记录，那么 EXISTS 操作符将返回 false (0)。

**示例

在下面的示例中，如果系的 id 存在于学生表中，我们将选择系的名称：

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

这将为您提供

只会返回 “IT、物理和艺术”这三个系。而 “数学”系名不会返回，因为该系没有学生，所以系名不存在于学生表中。这就是 EXISTS 运算符忽略 “数学”系的原因。

SQLite NOT

将前面操作符的结果与后面操作符的结果相反。例如

• NOT BETWEEN - 如果 BETWEEN 返回 false，则返回 true，反之亦然。
• NOT LIKE - 如果 LIKE 返回 false，则返回 true，反之亦然。
• NOT GLOB - 如果 GLOB 返回 false，则返回 true，反之亦然。
• NOT EXISTS - 如果 EXISTS 返回 false，则返回 true，反之亦然。

**示例

在下面的示例中，我们将使用 NOT 运算符和 EXISTS 运算符来获取学生表中不存在的系名，这是 EXISTS 运算符的反向结果。因此，将通过系表中不存在的 DepartmentId 进行搜索。

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

输出 ：

只返回 “数学”系。因为 “数学”系是学生表中唯一不存在的系。

限制和排序

SQLite 排序

SQLite 排序是通过一个或多个表达式对结果进行排序。要对结果集排序，必须使用 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 子句，可以限制 SQL 查询返回的行数。例如，LIMIT 10 将只返回 10 条记录，而忽略所有其他记录。

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

请注意，OFFSET 子句是可选的，你可以写成“ LIMIT 4, 4 ”，这样就会得到准确的结果。

示例 ：

在下面的示例中，我们将使用查询语句从学生编号 5 开始只返回 3 名学生：

SELECT * FROM Students LIMIT 4,3;

这样，从第 5 行开始就只有三个学生了。因此，它将给出带有 StudentId 5、6 和 7 的行：

删除重复值

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

**示例

下面的查询将返回重复的 “部门名称值”： 在这里，我们有名称为 IT、物理和艺术的重复值。

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 聚合是 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 ) 获得各部门的总计数以及它们的不同之处：

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

这将为您提供

如下所示：

• COUNT( DepartmentId ) 将给出所有部门 ID 的计数，并忽略空值。
• COUNT(DISTINCT DepartmentId ) 给出部门 ID 的不同值，只有 3 个。请注意，学生姓名中有 8 个系名的值。但只有三个不同的值，分别是数学、信息技术和物理。
• COUNT(*) 计算学生表中的行数，10 个学生有 10 行。

GROUP_CONCAT() - GROUP_CONCAT(X) 或 GROUP_CONCAT(X,Y)

GROUP_CONCAT 聚合函数用逗号将多个值连接成一个值。它有以下选项：

• group_concat(x)： 将 x 的所有值连接成一个字符串，值与值之间用逗号“, ”分隔。空值将被忽略。
• group_concat(x,y)： 将 x 的值连接成一个字符串，每个值之间的分隔符是 y，而不是默认的分隔符“,”。NULL 值也将被忽略。
• group_concat(distinct x)： 将 x 的所有不同值连接成一个字符串，在各值之间使用逗号“, ”作为分隔符。空值将被忽略。

GROUP_CONCAT( DepartmentName ) 示例*

下面的查询将把学生表和院系表中的所有院系名称值连接成一个以逗号分隔的字符串。这样就不会返回一个值列表，而是每行一个值。它将只返回一行中的一个值，所有值用逗号分隔：

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

这将为您提供

这样就可以将 8 个部门的名称值连接成一个字符串，用逗号分隔。

GROUP_CONCAT(DISTINCT DepartmentName ) 示例*

下面的查询将把学生和院系表中不同的院系名称值连接成一个以逗号分隔的字符串：

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

这将为您提供

请注意结果与前一个结果的不同之处：只返回三个值，即不同的部门名称，重复值已被删除。

GROUP_CONCAT( DepartmentName ,'&') 示例*

下面的查询将把学生和院系表中院系名称列的所有值连接成一个字符串，但用字符“&”代替逗号作为分隔符：

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 的所有值都为空，MAX 将返回一个 NULL 值。而 MIN(X) 将返回 X 值中最小的值。如果 X 的所有值都为空，MIN 将返回一个 NULL 值。

示例

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

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

这将为您提供

SQLite SUM(x)、Total(x)

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

• 如果所有值都为空，SUM 将返回空值，但 Total 将返回 0。
• TOTAL 总是返回浮点数。如果所有 x 值都是整数，SUM 返回整数值。但是，如果数值不是整数，它将返回浮点数。

示例

在下面的查询中，我们将使用 SUM 和 total 获得 “分数”表中所有分数的总和：

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

这将为您提供

可以看出，TOTAL 返回的总是浮点数。但 SUM 返回的是整数值，因为 “Mark ”列中的值可能是整数。

SUM 和 TOTAL 的区别示例：

在下面的查询中，我们将展示 SUM 和 TOTAL 在获取 NULL 值的 SUM 时的区别：

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

这将为您提供

请注意，TestId = 4 没有标记，因此该测试的值为空。SUM 返回空值，而 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 子句返回的分组，可以在 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;

这将为您提供

)

子句 HAVING COUNT( S.StudentId ) = 2 将对返回的组进行过滤，只返回其中包含两名学生的组。在我们的例子中，艺术系有 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 ;
The query:
SELECT StudentId , Mark
FROM Tests AS t
INNER JOIN Marks AS m ON t.TestId = m.TestId

上述查询被称为子查询，因为它嵌套在 FROM 子句中。请注意，我们给它起了一个别名 “t”，以便在查询中引用它返回的列。

该查询将为您提供

因此，在我们的案例中

• s.StudentName 从提供学生姓名的主查询中选取，t.Mark 从提供每个学生分数的子查询中选取。
• 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 子句中。子查询将返回操作符 NOT EXISTS 使用的 DepartmentId 值。

该查询将为您提供

在上述查询中，我们选择了没有学生注册的系。这就是 “数学 ”系。

集合操作 - UNION,Intersect

SQLite 支持以下 SET 操作：

union & union all

它将多个 SELECT 语句返回的一个或多个结果集（一组记录）合并为一个结果集。

UNION 将返回不同的值。但是，UNION ALL 不会，而且会包含重复值。

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

UNION 示例

在下面的示例中，我们将在同一列中获取学生表中的系Id列表和部门表中的系Id列表：

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

这将为您提供

查询只返回 5 行，即不同的部门 ID 值。注意第一个值是空值。

SQLite UNION ALL 示例

在下面的示例中，我们将在同一列中获取学生表中的部门 ID 列表和部门表中的部门 ID 列表：

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

这将为您提供

查询将返回 14 条记录，其中 10 条来自学生表，4 条来自院系表。请注意，返回的值有重复。另外，请注意列名是第一条 SELECT 语句中指定的列名。

现在，让我们看看如果将 UNION ALL 替换为 UNION，UNION ALL 会得到怎样不同的结果：

SQLite INTERSECT

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

**示例

在下面的查询中，我们将选择同时存在于 “学生 ”表和 “部门 ”表的 DepartmentId 列中的 DepartmentId 值：

SELECT DepartmentId FROM Students
Intersect
SELECT DepartmentId FROM Departments;

这将为您提供

查询只返回三个值 1、2 和 3。这三个值同时存在于两个表中。

但是，不包括 null 和 4 值，因为 null 值只存在于学生表中，而不存在于院系表中。而值 4 存在于系表中，不存在于学生表中。

这就是为什么 NULL 和 4 这两个值都被忽略，没有包含在返回值中。

例外

假设有两个记录列表（list1 和 list2），如果只想要 list1 中不存在于 list2 中的记录，可以使用 “EXCEPT ”子句。EXCEPT 子句比较两个列表，然后返回 list1 中存在而 list2 中不存在的记录。

示例

在下面的查询中，我们将选择部门表中存在而学生表中不存在的 DepartmentId 值：

SELECT DepartmentId FROM Departments
EXCEPT
SELECT DepartmentId FROM Students;

这将为您提供

查询只返回值 4。这是系表中唯一存在的值，而学生表中并不存在。

NULL 处理

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

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

搜索 NULL 值

不能使用普通的相等运算符 (=) 搜索空值。例如，下面的查询会搜索具有空 DepartmentId 值的学生：

SELECT * FROM Students WHERE DepartmentId = NULL;

该查询不会有任何结果：

因为 NULL 值不等于任何其他值，包括空值本身，这就是为什么它没有返回任何结果。

• 但是，为了使查询生效，必须使用“IS NULL ”操作符搜索空值，如下所示：

SELECT * FROM Students WHERE DepartmentId IS NULL;

这将为您提供

查询将返回那些 DepartmentId 值为空的学生。

• 如果要获取那些不为空的值，则必须像这样使用“ IS NOT NULL ”操作符：

SELECT * FROM Students WHERE DepartmentId IS NOT NULL;

这将为您提供

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

条件结果

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

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

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

• 如果成绩高于 85，则为 “Excellent”。

• “Very Good”（非常好），如果成绩在 70 到 85 之间。

• 如果成绩在 60 和 70 之间，则为 “Good”。

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

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

CASE 操作符可以用以下不同语法定义：

1. 可以使用不同的条件：

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

1. 或者，您也可以只使用一个表达式，然后输入不同的可能值供您选择：

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

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

示例

在下面的示例中，我们将在学生表的系 Id 列中使用CASE表达式和NULL值，以显示文本 “无系”，如下所示：

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

• CASE 运算符将检查 DepartmentId 的值是否为空。
• 如果是空值，则将选择字面值 “无部门”，而不是 DepartmentId 值。
• 如果不是空值，则会选择 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 语句。

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

示例

在下面的示例中，我们将在 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 子句，以便从部门表中获取部门名称。然后，我们添加了一个 GROUP BY 子句和两个聚合函数：

• COUNT "用于统计每个系组的学生人数。
• GROUP_CONCAT "用于将每个组的学生用逗号分隔成一个字符串。
• 在 GROUP BY 子句之后，我们使用 HAVING 子句过滤系科，只选择至少有 3 名学生的系科。

结果如下

摘要：

本文介绍了如何编写 SQLite 查询、查询数据库的基础知识以及如何过滤返回的数据。现在，你可以编写自己的 SQLite 查询了。