3 / 21

SQL | 自在学

SQL

如果把数据库比作一座巨大的图书馆，那么SQL就是我们与图书管理员沟通的标准语言——无论你想要查找特定的书籍、添加新书、修改书籍信息，还是整理书架，都需要通过这种语言来表达你的需求。

SQL

SQL的强大之处不仅仅在于它能够帮助我们查询数据。事实上，SQL是一门功能完备的数据库管理语言，它能够定义数据的结构（就像设计图书馆的分类系统），修改存储的信息（比如更新图书的借阅状态），甚至设置访问权限（决定哪些读者可以进入特定的书库）。

SQL虽然被称为查询语言，但它的功能远超查询本身。它是一个完整的数据库管理工具集，涵盖了数据定义、操作、安全控制等各个方面。

SQL的发展历程

SQL的历史可以追溯到20世纪70年代初期。当时，IBM的研究团队在开发“System R”项目时，亟需一种高效且结构化的语言来管理和操作关系型数据库。正是在这样的背景下，SQL（Structured Query Language，结构化查询语言）应运而生。最初，这门语言被称为Sequel，意为“结构化英语查询语言”，它以接近自然语言的表达方式，极大地降低了数据库操作的门槛。

随着数据库技术的不断发展，SQL逐步从实验室走向工业界，并成为关系型数据库领域的事实标准。1986年，美国国家标准协会（ANSI）和国际标准化组织（ISO）联合发布了首个SQL标准——SQL-86，为SQL的规范化奠定了基础。此后，SQL标准经历了多次重要升级：SQL-89对基础功能进行了补充，SQL-92引入了更丰富的语法和特性，SQL:1999则首次将面向对象的思想融入SQL体系。

每一次标准的演进都推动了SQL语言的专业化和现代化，使其能够应对日益复杂的数据管理需求。到了SQL:2008，标准中已经包含了窗口函数、递归查询等诸多现代数据库必备的高级功能。不过需要注意的是，尽管SQL标准为行业提供了统一的规范，不同数据库厂商在具体实现上仍存在差异，这些“方言”体现了各自对标准的扩展和优化。

SQL的核心组成

在SQL体系中，核心组成部分主要包括数据定义语言（DDL）与数据操纵语言（DML）。数据定义语言（DDL，Data Definition Language）用于描述和管理数据库的结构。通过DDL，我们可以创建、修改和删除数据库中的表、视图以及索引。例如，在构建一个在线书店的数据库时，DDL负责定义“图书”、“作者”、“订单”等表的结构，明确每个字段的数据类型、约束条件以及表之间的关联关系。这一过程相当于为数据库搭建坚实的基础框架，确保数据能够有序、规范地存储。

数据操纵语言（DML，Data Manipulation Language）则专注于对数据库中数据的实际操作。DML涵盖了插入（INSERT）、查询（SELECT）、更新（UPDATE）和删除（DELETE）等基本功能。以书店系统为例，当有新书入库时，我们通过DML插入相关数据；客户下单时，DML用于记录订单信息；而在进行销售数据分析时，DML则负责高效地检索和汇总所需数据。DML的灵活性和强大功能，使得数据库能够动态响应业务需求的变化，实现数据的高效管理与利用。

完整性约束在数据库系统中扮演着至关重要的角色，它通过一系列规则确保数据的准确性和一致性。例如，在高校教务系统中，学号字段通常被设置为唯一约束，防止出现重复的学生记录；而课程成绩表中的外键约束，则保证每条成绩记录都必须对应实际存在的学生和课程。

视图定义为我们提供了灵活的数据抽象手段。通过视图，我们可以将复杂的查询结果封装成一个虚拟表，便于不同角色的用户按需访问数据。例如，教务处可以通过视图快速获取全校学生的成绩分布，而任课教师则可以只关注自己所授课程的学生名单和成绩。视图的存在极大地提升了数据访问的安全性和便利性，同时也有助于简化应用层的开发工作。

事务控制机制是保障数据库操作原子性、一致性、隔离性和持久性的核心。以银行转账为例，只有在资金从甲账户扣除并成功转入乙账户后，整个操作才算完成；如果中途发生异常，系统会自动回滚，确保不会出现资金丢失或数据不一致的情况。在实际业务中，事务控制为我们提供了可靠的数据处理保障，尤其适用于订单处理、库存管理等对数据一致性要求极高的场景。

权限管理体系则为数据库安全保驾护航。通过精细化的权限分配，我们可以严格控制不同用户对数据的访问和操作范围。例如，普通教师只能查询和录入本班学生成绩，而系统管理员则拥有修改数据库结构和管理用户权限的最高权限。合理的权限设计不仅防止了数据泄露和误操作，也有助于满足合规性和审计要求。

综上，SQL通过完整性约束、视图定义、事务控制和权限管理等机制，为各类信息系统提供了专业、可靠且高效的数据管理能力。无论是科研数据平台还是企业级应用，SQL都能为其数据安全与业务连续性提供坚实的技术支撑。

虽然不同的数据库系统（如MySQL、PostgreSQL、Oracle等）在SQL的具体实现上可能存在差异，但核心概念和基本语法是通用的。掌握了标准SQL，你就拥有了在各种数据库系统间自由切换的能力。

在接下来的学习中，我们将深入探索SQL的各个方面，从最基础的数据定义开始，逐步掌握这门强大语言的精髓。

数据定义

在正式进行数据存储之前，数据库系统首先需要明确数据的结构与规范。这一过程在SQL中被称为“数据定义”，即通过精确描述数据表的字段、数据类型、约束条件等，为后续的数据管理和操作奠定基础。数据定义不仅决定了信息的组织方式，还直接影响到数据的完整性、一致性与系统性能，是数据库设计中至关重要的环节。

数据定义

理解数据类型

SQL语言为我们提供了丰富且严谨的数据类型体系，每一种类型都针对特定的数据存储需求进行了优化。合理的数据类型选择不仅直接影响数据库的物理存储效率，还对数据的完整性、检索性能以及后续的数据处理有着深远影响。

在实际数据库设计中，字符型数据类型是最为基础且常用的类型之一。以高校信息管理系统为例，学生姓名、课程名称等字段通常采用字符型存储。SQL标准中，字符型主要分为定长的char和变长的varchar两类。

char(n)类型为定长字符型，无论实际存储内容长度如何，系统都会为每条记录分配固定的n个字符空间。例如，char(10)字段即使只存储“张伟”两个汉字，也会占用10个字符的空间，剩余部分由空格自动填充。这种类型适用于字段长度固定、对齐显示要求较高的场景，比如身份证号、邮政编码等。

而varchar(n)类型则为变长字符型，仅根据实际存储内容分配空间。例如，varchar(10)字段存储“张伟”时只占用2个字符，存储“王小明”时占用3个字符。varchar类型极大提升了存储利用率，尤其适合姓名、地址、备注等长度不确定的字段，因此在实际业务系统中应用更为广泛。

在比较字符类型数据时，char类型会自动补齐空格进行比较，而varchar类型不会。这可能导致一些意想不到的结果，因此建议优先使用varchar类型。

int类型用于存储范围较大的整数，常见于学号、年龄等字段，能够保证数据的完整性与高效索引。对于取值范围有限的整数，例如学期编号或课程学分，smallint类型则更为节省存储空间，提升了数据库的整体性能。

当业务需求涉及到精确的小数运算时，numeric(p, d)类型成为首选。参数p代表总位数，d表示小数位数。例如，numeric(7,2)可以精确存储最大到99999.99的金额，非常适合学费、奖学金、商品价格等对精度要求极高的场景。该类型采用定点存储方式，能够有效避免浮点数带来的舍入误差，确保财务数据的准确性。

对于科学计算、统计分析等对数值范围和运算速度有更高要求的场景，real与double precision类型则提供了浮点数支持。real通常为单精度浮点数，double precision为双精度浮点数，它们能够表示极大或极小的数值，但由于采用二进制近似存储，可能存在微小的精度损失。因此，在涉及财务、计量等精度敏感的应用中，建议优先使用numeric类型，而在大数据量、科学建模等对性能要求更高的场合，则可选择real或double precision。

空值情况

在实际业务场景中常常存在信息暂时缺失或尚未确定的情况。例如，学生入学初期专业尚未分配，或某门课程的成绩尚未录入。针对这类“未知”或“不可用”的数据，SQL标准引入了null值作为专门的标记。

需要特别强调的是，null并不等同于空字符串（''），也不等同于数值0。它代表的是“值缺失”或“不可用”的状态，是一种独立于其他数据类型的特殊存在。在数据库设计与查询过程中，null的处理尤为重要。比如在条件判断、聚合运算和数据统计时，null值会影响结果的准确性，因此我们在编写SQL语句时，常常需要用is null或is not null等专门的语法来进行判断和处理，确保数据分析的科学性和严谨性。

创建数据表

在深入理解各类数据类型的基础上，接下来我们将正式进入数据表结构的设计与实现环节。create table语句在SQL中扮演着核心角色，它不仅明确了表的名称，还精确规定了每个字段的数据类型、约束条件以及表间的逻辑关系，相当于为整个数据库系统绘制了严谨的蓝图。

下面，我们以一个典型的高校学生选课管理系统为例，系统性地演示数据表的规范化创建过程：

|
create table 学院 (
    学院代码 varchar(10),
    学院名称 varchar(50),
    办公楼 varchar(30),
    预算 numeric(12,2),
    primary key (学院代码)
);

上述SQL语句定义了一个名为“学院”的数据表，专门用于存储各学院的基础信息。primary key约束明确指定“学院代码”为主键字段，确保该字段在表内的唯一性和非空性。

接下来，我们将进一步设计学生信息表：

|
create table 学生 (
    学号 varchar(10),
    姓名 varchar(20) not null,
    学院代码 varchar(10),
    入学年份 int,
    primary key (学号),
    foreign key (学院代码) references 学院(学院代码)
);

这里我们看到了更多的约束条件。not null约束确保每个学生都必须有姓名，不能为空。foreign key（外键）约束则建立了学生表与学院表之间的关联关系，确保每个学生的学院代码都必须在学院表中真实存在。

让我们再创建一个课程表来完善我们的系统：

|
create table 课程 (
    课程代码 varchar(8),
    课程名称 varchar(100),
    学院代码 varchar(10),
    学分 numeric(2,0),
    primary key (课程代码),
    foreign key (学院代码) references 学院(学院代码)
);

通过这些表的创建，我们建立了一个基本的数据结构。每个表都有明确的主键来唯一标识记录，外键约束确保了数据的关联性和一致性。

维护数据结构

数据库的结构并不是一成不变的。随着业务需求的变化，我们可能需要添加新的字段、修改现有的结构，甚至删除不再需要的表。

当我们需要为学生表添加一个新的字段时，可以使用alter table命令：

|
alter table 学生 add 手机号码 varchar(15);

这条命令会为学生表增加一个手机号码字段，所有现有的学生记录在这个新字段上的值都会被设置为null。

如果某个字段不再需要，我们也可以将其删除：

|
alter table 学生 drop 手机号码;

并非所有数据库系统都支持删除字段的操作。在实际项目中，删除字段前一定要备份数据，并测试相关的应用程序是否会受到影响。

当某个表完全不再需要时，我们可以使用drop table命令将其完全删除：

|
drop table 课程;

这个操作会彻底删除表及其所有数据，因此使用时必须格外小心。与之相对的是delete from命令，它只删除表中的数据但保留表的结构：

|
delete from 学生;

这条命令会删除学生表中的所有记录，但表的结构依然存在，我们随时可以向其中插入新的数据。通过合理使用这些数据定义语言的命令，我们能够构建出结构清晰、约束完整的数据库系统。

SQL查询

在构建好严谨的数据结构之后，SQL的核心价值便体现在数据查询环节。查询不仅是数据库操作中最为关键的部分，更是数据分析与决策的基础。我们可以通过科学的查询语句，从庞杂的数据集中精准提取所需的信息，实现数据的高效利用与深度洞察。

SQL查询

SQL查询的基本架构

在SQL查询的设计中，通常采用“选择-来源-条件”三段式结构。首先，select子句明确指定需要检索的字段或表达式；接着，from子句定义数据的来源表或视图；最后，where子句用于设定筛选条件，实现对数据的精确过滤。这种结构不仅提升了查询的可读性和可维护性，也为后续的数据分析和业务逻辑处理奠定了坚实的基础。

让我们先为刚才创建的学生选课系统添加一些示例数据，这样查询才有意义：

|
-- 插入学院信息
insert into 学院 values 
('CS', '计算机学院', '信息大楼', 2500000.00),
('MATH', '数学学院', '理学大楼', 1800000.00),
('PHYS', '物理学院', '理学大楼', 2200000.00);
 
-- 插入学生信息
insert into 学生 values

单表查询

在实际数据库操作中，最基础且常见的查询形式便是针对单一数据表的信息检索。例如，若我们希望获取学生表中所有学生的姓名:

|
select 姓名 from 学生;

这个查询告诉数据库：“我想看到学生表中所有学生的姓名”。结果会是一个包含所有学生姓名的列表。

|
+--------+
| 姓名   |
+--------+
| 张三   |
| 李四   |
| 王五   |
| 赵六   |
+--------+

有时候我们想要看到更多信息，比如同时显示学号和姓名：

|
select 学号, 姓名 from 学生;

|
+---------+--------+
| 学号    | 姓名   |
+---------+--------+
| 2021001 | 张三   |
| 2021002 | 李四   |
| 2021003 | 王五   |
| 2022001 | 赵六   |
+---------+--------+

如果我们想要查看学生表中的所有信息，可以使用星号（*）作为通配符：

|
select * from 学生;

这个查询会返回学生表中所有字段的所有记录。

|
+---------+--------+--------------+--------------+
| 学号    | 姓名   | 学院代码     | 入学年份     |
+---------+--------+--------------+--------------+
| 2021001 | 张三   | CS           |         2021 |
| 2021002 | 李四   | MATH         |         2021 |
| 2021003 | 王五   | CS           |         2021 |
| 2022001

条件过滤

在实际的数据分析与管理过程中，我们通常并不关心表中所有的数据，而是希望根据具体的业务需求筛选出符合条件的记录。此时，where子句就成为了我们精准检索数据的重要工具。

举例来说，假设我们需要查询所有属于计算机学院的学生信息：

|
select 姓名, 学号 from 学生 
where 学院代码 = 'CS';

这个查询会返回所有属于计算机学院（学院代码为'CS'）的学生信息。where子句就像一个过滤器，只有满足条件的记录才会出现在结果中。

|
+--------+---------+
| 姓名   | 学号    |
+--------+---------+
| 张三   | 2021001 |
| 王五   | 2021003 |
+--------+---------+

我们也可以使用更复杂的条件，比如查找2021年入学的计算机学院学生：

|
select 姓名, 学号 from 学生 
where 学院代码 = 'CS' and 入学年份 = 2021;

这里使用了and连接词来组合多个条件，只有同时满足两个条件的记录才会被选中，我们可以看到同样也只有张三和王五符合条件。

去除重复

有时候查询结果中可能出现重复的记录。比如当我们查询所有学生所属的学院代码时：

|
select 学院代码 from 学生;

|
+--------------+
| 学院代码     |
+--------------+
| CS           |
| CS           |
| MATH         |
| PHYS         |
+--------------+

由于多个学生可能属于同一个学院，结果中会出现重复的学院代码。如果我们只想看到不重复的学院代码列表，可以使用distinct关键字：

|
select distinct 学院代码 from 学生;

|
+--------------+
| 学院代码     |
+--------------+
| CS           |
| MATH         |
| PHYS         |
+--------------+

distinct关键字会自动去除结果中的重复行。但要注意，去重操作可能会消耗额外的计算资源，在处理大量数据时需要谨慎使用。

多表查询

虽然单表查询能够满足基本的数据检索需求，但关系型数据库的核心优势在于能够通过多表关联实现复杂的数据整合与分析。例如，若需查询每位学生的姓名及其所属学院的名称，必须同时访问“学生”表与“学院”表，并通过两表间的关联字段进行连接。

实现多表关联的基础方式，是在FROM子句中同时指定多个表，并在WHERE子句中明确给出它们之间的连接条件：

|
select 学生.姓名, 学生.学院代码, 学院.学院名称
from 学生, 学院
where 学生.学院代码 = 学院.学院代码;

该查询实现了典型的等值连接操作。首先，FROM子句指定了需要联合查询的“学生”和“学院”两张表。随后，WHERE子句通过“学生.学院代码 = 学院.学院代码”这一连接条件，将两表中“学院代码”字段值相等的记录进行配对，从而实现数据的关联。最后，SELECT子句明确列出需要返回的字段，包括学生姓名、学院代码以及学院名称。

在多表查询中，常常会出现字段重名的情况（如“学院代码”同时存在于两表中）。为避免歧义，SQL标准推荐采用“表名.字段名”的限定写法，以精确指定所引用的字段来源。

自然连接

有时候我们会遇到两张表中有同名字段的情况，这时可以用SQL的自然连接（natural join）来让写法更简单。自然连接会自动用那些同名的字段作为连接条件，省去了手动写where的麻烦：

|
select 姓名, 学院代码, 学院名称
from 学生 natural join 学院;

|
+--------+--------------+-----------------+
| 姓名   | 学院代码     | 学院名称        |
+--------+--------------+-----------------+
| 张三   | CS           | 计算机学院      |
| 王五   | CS           | 计算机学院      |
| 李四   | MATH         | 数学学院        |
| 赵六   | PHYS         | 物理学院        |
+--------+--------------+-----------------+

这个查询的效果与前面使用where子句的查询完全相同，但写法更加简洁。自然连接会自动识别两个表中相同的字段（在这里是学院代码），并将其作为连接条件。

让我们看一个更复杂的例子。假设我们想要显示每门课程的名称以及开设该课程的学院名称：

|
select 课程名称, 学院名称
from 课程 natural join 学院;

|
+--------------------+-----------------+
| 课程名称           | 学院名称        |
+--------------------+-----------------+
| 程序设计基础       | 计算机学院      |
| 高等数学           | 数学学院        |
| 大学物理           | 物理学院        |
+--------------------+-----------------+

查询的执行过程

要深入理解多表查询的本质，我们首先需要掌握SQL查询的标准执行流程。尽管实际数据库在底层会进行多种优化，但从理论上讲，SQL查询的处理顺序大致如下：

FROM 子句：首先确定参与查询的数据表。如果涉及多张表，系统会生成这些表的笛卡尔积，即将所有表的每一行进行组合，形成所有可能的行对。
WHERE 子句：在笛卡尔积的基础上，应用筛选条件，过滤出满足条件的记录，避免无意义的数据组合。
SELECT 子句：最后，选取需要返回的字段，构建最终的结果集。

这种执行顺序有助于我们理解为什么在多表查询中合理设置连接条件至关重要，否则会导致数据量暴增，影响查询效率和结果的准确性。

笛卡尔积可能会产生非常大的中间结果。比如一个有1000行的表与另一个有1000行的表进行笛卡尔积运算，会产生100万行的结果。这就是为什么在多表查询中必须要有合适的where条件来限制结果的原因。

连接查询的实际应用

让我们通过一个更实际的例子来巩固理解。假设我们想要找出所有计算机学院学生的详细信息，包括他们的姓名和所属学院的办公楼位置：

|
select 学生.姓名, 学生.学号, 学院.学院名称, 学院.办公楼
from 学生, 学院
where 学生.学院代码 = 学院.学院代码 
  and 学院.学院代码 = 'CS';

或者使用自然连接的方式：

|
select 姓名, 学号, 学院名称, 办公楼
from 学生 natural join 学院
where 学院代码 = 'CS';

这两种写法都会得到相同的结果，但自然连接的方式更加简洁，也更容易理解。查询不仅仅是获取数据的工具，更是我们理解数据、发现模式、获得洞察的重要手段。

进阶查询

在掌握了基本的SQL查询语法后，我们可以进一步学习和应用SQL的高级功能。SQL语言为数据分析和处理提供了丰富的扩展能力，使我们能够以更灵活、更精确的方式表达复杂的业务需求和数据操作逻辑。

重命名

重命名的

在实际的SQL查询中，为了提升结果集的可读性和后续处理的便利性，常常需要对输出的列名进行重命名，或为表指定简洁的别名以优化查询语句的结构。此时，可以通过as关键字实现列或表的重命名。

例如，若需基于入学年份计算每位学生的在校年数，并为该计算结果赋予具有业务含义的列名：

|
select 姓名, 2024 - 入学年份 as 在校年数
from 学生;

|
+--------+--------------+
| 姓名   | 在校年数     |
+--------+--------------+
| 张三   |            3 |
| 李四   |            3 |
| 王五   |            3 |
| 赵六   |            2 |
+--------+--------------+

这里的as 在校年数为计算结果指定了一个清晰的列名。如果没有这个重命名，查询结果的列名可能是类似“2024 - 入学年份”这样不够直观的表达式。我们也可以为表起别名，这在处理复杂查询时特别有用：

|
select s.姓名, c.学院名称
from 学生 as s, 学院 as c
where s.学院代码 = c.学院代码;

这里我们把学生表重命名为s，学院表重命名为c，这样在引用字段时就可以使用更简短的前缀。

有时候我们需要比较同一张表中的不同记录，这时候表的重命名就变得必不可少。比如我们想要找出与张三在同一个学院的其他学生：

|
select distinct s2.姓名
from 学生 as s1, 学生 as s2
where s1.学院代码 = s2.学院代码 
  and s1.姓名 = '张三' 
  and s2.姓名 != '张三';

这个查询通过给学生表起两个不同的别名（s1和s2），让我们能够将同一张表当作两个不同的数据源来处理。

字符串模式匹配与文本搜索

在处理文本数据时，常常需要进行灵活的字符串模式匹配。SQL标准提供了LIKE操作符，结合通配符，可实现高效的模糊查询与文本检索。 LIKE支持两种常用通配符：百分号（%）用于匹配任意长度（包括零长度）的任意字符序列，下划线（_）用于精确匹配单个字符。

例如，若需检索所有姓“王”的学生记录：

|
select 姓名, 学号 from 学生
where 姓名 like '王%';

|
+--------+---------+
| 姓名   | 学号    |
+--------+---------+
| 王五   | 2021003 |
+--------+---------+

这个查询可以帮我们找出所有姓“王”的学生，比如“王五”、“王小明”等。如果我们想要查找名字中包含“明”字的学生：

|
select 姓名, 学号 from 学生
where 姓名 like '%明%';

这个查询会匹配“小明”、“明华”、“王明强”等所有包含“明”字的姓名。下划线通配符用于精确匹配单个字符的位置。比如我们想要查找所有三个字的姓名：

|
select 姓名 from 学生
where 姓名 like '___';

此处的三个下划线（'___'）用于精确匹配姓名长度为三个字符的记录。在实际数据库应用中，若需检索包含通配符本身（如百分号 %）的文本内容，可通过指定转义字符实现。例如，若课程名称中包含实际的百分号字符，可以使用 escape 关键字配合反斜杠进行转义：

|
select 课程名称 from 课程
where 课程名称 like '%\%%' escape '\';

这里的escape '\'指定反斜杠为转义字符，\%表示要匹配的是实际的百分号字符，而不是通配符。 SQL也提供了not like操作符用于反向匹配。比如查找所有不是姓“李”的学生：

|
select 姓名 from 学生
where 姓名 not like '李%';

结果排序

在实际的数据查询中，结果集的排序通常具有重要意义。通过使用 order by 子句，可以根据一个或多个指定字段对查询结果进行有序排列，以满足数据分析、报表生成等业务需求。

最基本的用法是按照单一字段进行排序：

|
select 姓名, 入学年份 from 学生
order by 姓名;

|
+--------+--------------+
| 姓名   | 入学年份     |
+--------+--------------+
| 张三   |         2021 |
| 李四   |         2021 |
| 王五   |         2021 |
| 赵六   |         2022 |
+--------+--------------+

这个查询会按照学生姓名的字母顺序（对于中文是拼音顺序）来显示结果。我们可以指定排序的方向。默认是升序（从小到大），也可以显式指定：

|
select 姓名, 入学年份 from 学生
order by 入学年份 desc;

desc表示降序排列，会按照入学年份从新到旧的顺序显示学生。相对应的，asc表示升序排列，但由于升序是默认的，通常我们会省略这个关键字。更有趣的是多字段排序。假设我们想要先按学院代码排序，相同学院内的学生再按入学年份排序：

|
select 姓名, 学院代码, 入学年份 from 学生
order by 学院代码, 入学年份 desc;

|
+--------+--------------+--------------+
| 姓名   | 学院代码     | 入学年份     |
+--------+--------------+--------------+
| 张三   | CS           |         2021 |
| 王五   | CS           |         2021 |
| 李四   | MATH         |         2021 |
| 赵六   | PHYS         |         2022 |
+--------+--------------+--------------+

这个查询会首先按学院代码进行分组排序，然后在每个学院内部按入学年份降序排列。这种多层排序在生成报表时特别有用。

范围查询

between操作符用于实现区间（范围）条件判断，是SQL中常用的范围查询工具。例如，若需检索2021年至2022年间入学的学生，可使用如下方式：

|
select 姓名, 入学年份 from 学生
where 入学年份 between 2021 and 2022;

这比使用传统的比较操作符更加简洁和易读：

|
select 姓名, 入学年份 from 学生
where 入学年份 >= 2021 and 入学年份 <= 2022;

between操作符包含边界值，也就是说上面的查询会包括2021年和2022年入学的学生。我们也可以使用not between来排除某个范围：

|
select 姓名, 入学年份 from 学生
where 入学年份 not between 2020 and 2021;

这个查询会找到所有不在2020年到2021年之间入学的学生。

多属性条件

SQL允许我们同时比较多个属性，这在处理复合条件时特别有用。假设我们想要查找计算机学院2021年入学的学生，可以使用元组比较：

|
select 姓名, 学号 from 学生
where (学院代码, 入学年份) = ('CS', 2021);

|
+--------+---------+
| 姓名   | 学号    |
+--------+---------+
| 张三   | 2021001 |
| 王五   | 2021003 |
+--------+---------+

这种写法等价于：

|
select 姓名, 学号 from 学生
where 学院代码 = 'CS' and 入学年份 = 2021;

但在某些复杂情况下，元组比较会更加简洁和直观。

通配符

星号（*）通配符用于检索表中的全部字段，常用于数据探索或需要获取完整记录时：

|
select * from 学生
where 学院代码 = 'CS';

我们也可以混合使用星号和具体字段名。比如我们想要看到学生表的所有信息，同时还想看到他们所属学院的名称：

|
select 学生.*, 学院.学院名称
from 学生, 学院
where 学生.学院代码 = 学院.学院代码;

这里的学生.*表示选择学生表的所有字段，同时我们还额外选择了学院表的学院名称字段。

虽然星号通配符很方便，但在生产环境中建议明确列出需要的字段名。这样不仅能提高查询性能，还能让查询意图更加清晰，并且在表结构发生变化时更加稳定。

综合应用

下面我们通过一个综合性示例，演示如何利用上述SQL技巧生成学生信息报告。目标是查询所有学生的详细信息，按学院分组，在同一学院内按入学年份降序排列，并筛选出2021年及以后入学的学生：

|
select s.学号 as 学生学号, 
       s.姓名 as 学生姓名,
       c.学院名称 as 所属学院,
       s.入学年份,
       2024 - s.入学年份 as 在校年数
from 学生 as s, 学院 as c
where s.学院代码 = c.学院代码
  and

|
+--------------+--------------+-----------------+--------------+--------------+
| 学生学号     | 学生姓名     | 所属学院        | 入学年份     | 在校年数     |
+--------------+--------------+-----------------+--------------+--------------+
| 2021002      | 李四         | 数学学院        |         2021 |            3 |
| 2022001      | 赵六         | 物理学院        |         2022 |            2 |
| 2021001      | 张三

集合

在数学中，集合（Set）是由若干互不相同元素组成的整体。SQL 语言借鉴了集合论的思想，允许我们对查询结果集进行类似数学集合的操作，如并集、交集和差集等。这些集合操作为复杂数据分析和多表查询提供了强有力的工具。

为了系统演示 SQL 的集合操作，下面我们首先为学生选课系统引入一张开课信息表：

|
create table 开课信息 (
    课程代码 varchar(8),
    学期 varchar(10),
    年份 int,
    primary key (课程代码, 学期, 年份),
    foreign key (课程代码) references 课程(课程代码)
);
 
-- 插入一些开课数据
insert into 开课信息 values 
('CS101', '春季', 2024),
('CS101',

合并 —— Union

union操作用于将多个查询的结果集合并为一个结果集，并自动去除重复记录。它常用于需要整合来自不同来源或条件的数据时。

例如，若需查询2024年春季和秋季学期开设的所有课程代码，可以分别检索两个学期的课程，再通过union合并结果：

|
select 课程代码 from 开课信息 where 学期 = '春季' and 年份 = 2024
union
select 课程代码 from 开课信息 where 学期 = '秋季' and 年份 = 2024;

|
+--------------+
| 课程代码     |
+--------------+
| CS101        |
| MATH201      |
| PHYS101      |
+--------------+

这个查询会返回所有在2024年春季或秋季开设的课程代码。需要注意的是，union操作会自动去除重复的记录。如果某门课程既在春季开设又在秋季开设（比如CS101），它在结果中只会出现一次。有时候我们希望保留重复的记录，这时可以使用union all：

|
select 课程代码 from 开课信息 where 学期 = '春季' and 年份 = 2024
union all
select 课程代码 from 开课信息 where 学期 = '秋季' and 年份 = 2024;

使用union all后，如果CS101既在春季又在秋季开设，它会在结果中出现两次:

|
+--------------+
| 课程代码     |
+--------------+
| CS101        |
| MATH201      |
| PHYS101      |
| CS101        |
| PHYS101      |
+--------------+

Intersect操作

intersect操作用于获取多个查询结果的交集，即仅返回同时出现在所有查询结果中的记录。例如，若需查询同时在春季和秋季学期开设的课程：

|
select 课程代码 from 开课信息 where 学期 = '春季' and 年份 = 2024
intersect
select 课程代码 from 开课信息 where 学期 = '秋季' and 年份 = 2024;

这个查询会返回那些在2024年春季和秋季都开设的课程代码。根据我们的示例数据，只有CS101和PHYS101会出现在结果中。

与union类似，intersect也会自动去除重复项。如果要保留重复项，可以使用intersect all。在这种情况下，结果中每个元素出现的次数等于它在各个查询结果中出现次数的最小值。

Except操作

except操作用于计算两个查询结果的差集，即返回仅存在于第一个查询结果、而不存在于第二个查询结果的记录。该操作常用于数据排除分析，例如筛选独有项。

例如，若需查询仅在2024年春季学期开设、而未在秋季学期开设的课程：

|
select 课程代码 from 开课信息 where 学期 = '春季' and 年份 = 2024
except
select 课程代码 from 开课信息 where 学期 = '秋季' and 年份 = 2024;

查询将返回仅在2024年春季学期开设、而未在秋季学期开设的课程代码。例如，根据示例数据，MATH201 只在春季开设，因此会被返回。

如果需要查询仅在秋季学期开设、而未在春季学期开设的课程，可以使用如下方式：

|
select 课程代码 from 开课信息 where 学期 = '秋季' and 年份 = 2024
except
select 课程代码 from 开课信息 where 学期 = '春季' and 年份 = 2024;

根据我们的数据，这个查询不会返回任何结果，因为所有秋季开设的课程在春季也都有开设。

同样，except all会保留重复项。结果中每个元素出现的次数等于它在第一个查询结果中的出现次数减去它在第二个查询结果中的出现次数（如果结果为正数的话）。

集合操作的注意事项

在使用集合操作（如 union、intersect、except）时，需要注意以下技术要点：

所有参与集合操作的子查询，必须返回相同数量的列，且对应位置的列数据类型必须兼容，否则会导致语法错误或隐式类型转换带来的性能问题。
集合操作的排序（order by）只能出现在整个集合操作的最外层，不能出现在各个子查询内部。例如，若需对最终结果排序，应将 order by 子句放在所有集合操作之后：

|
select 课程代码 from 开课信息 where 学期 = '春季'
union
select 课程代码 from 开课信息 where 学期 = '秋季'
order by 课程代码;

集合操作的优先级是从左到右，但你可以使用括号来改变优先级：

|
(select 课程代码 from 开课信息 where 学期 = '春季'
 intersect
 select 课程代码 from 开课信息 where 学期 = '秋季')
union
select 课程代码 from 课程 where 学分 = 4;

虽然集合操作非常强大，但在处理大量数据时可能会影响性能。在生产环境中，有时候使用连接查询或子查询可能会更高效。选择哪种方法取决于具体的数据量和查询复杂度。

空值

在实际数据库应用中，数据的不完整性是常态。例如，学生的专业代码可能尚未确定，课程的最终成绩可能尚未录入，或教师的联系方式尚未采集。SQL 采用特殊的 null 值来表示“未知”或“缺失”的数据状态。

null值的本质

null值在SQL中是一种特殊的标记，用于表示数据缺失、未知或不适用。它不同于任何具体的数据类型值（如0、空字符串等），而是用于表达“该字段当前没有确定的值”。在数据库操作和逻辑判断中，null值具有独特的语义和处理规则。

下面我们通过在学生选课系统中插入包含null值的数据，进一步说明null值的实际应用：

|
-- 添加一些包含null值的学生记录
insert into 学生 values 
('2023001', '陈七', null, 2023),  -- 还未确定学院
('2023002', '李八', 'CS', null); -- 入学年份未登记
 
-- 更新某个学院的预算为null（可能正在重新评估）
update 学院 set 预算 = null where 学院代码 = 'PHYS';

null值在运算中的表现

在SQL中，null值参与任何算术运算时，运算结果必为null。这是因为null代表未知或缺失，任何与未知值相关的计算，其结果同样不可确定。因此，涉及null的算术表达式在结果集中的对应字段将返回null。

|
select 姓名, 
       2024 - 入学年份 as 在校年数,
       预算 * 1.1 as 调整后预算
from 学生, 学院
where 学生.学院代码 = 学院.学院代码;

在这个查询中，对于入学年份为null的学生，计算出的在校年数也会是null。同样，对于预算为null的学院，调整后预算也是null：

|
+--------+--------------+-----------------+
| 姓名   | 在校年数     | 调整后预算      |
+--------+--------------+-----------------+
| 张三   |            3 |     2750000.000 |
| 王五   |            3 |     2750000.000 |
| 李八   |         NULL |     2750000.000 |
| 李四   |            3 |     1980000.000 |
| 赵六   |

三值逻辑

在经典的二值布尔逻辑体系中，任何逻辑表达式的结果只能是真（true）或假（false）。然而，在关系数据库及SQL标准中，为了准确表达数据的不确定性和缺失，逻辑系统被扩展为三值逻辑（Three-Valued Logic, 3VL），引入了第三种逻辑值：未知（unknown）。三值逻辑能够更严谨地建模现实世界中因null值导致的信息不完全性。

例如，考虑如下的比较操作：

|
select 姓名, 入学年份
from 学生
where 入学年份 > 2021;

对于入学年份为null的学生，条件入学年份 > 2021的结果既不是true也不是false，而是unknown。在SQL中，只有条件结果为true的记录才会出现在查询结果中，因此入学年份为null的学生不会出现在结果中。

三值逻辑的运算规则遵循以下原则：

表达式	结果
true AND unknown	unknown
false AND unknown	false
unknown AND unknown	unknown
true OR unknown	true
false OR unknown	unknown
unknown OR unknown	unknown
NOT unknown	unknown

这些规则确保了逻辑运算的一致性和可预测性。

检测null值

由于null值的特殊性，我们不能使用普通的比较操作符来检测它。表达式入学年份 = null不会返回我们期望的结果，因为任何与null的比较都会返回unknown。 SQL提供了专门的操作符来检测null值：

|
-- 查找入学年份未知的学生
select 姓名, 学号
from 学生
where 入学年份 is null;

|
+--------+---------+
| 姓名   | 学号    |
+--------+---------+
| 李八   | 2023002 |
+--------+---------+

|
-- 查找入学年份已知的学生
select 姓名, 学号, 入学年份
from 学生
where 入学年份 is not null;

这些操作符是检测null值的唯一可靠方法。

永远不要使用 = null 或 != null 来检测空值，这些表达式总是返回unknown，不会给出你期望的结果。始终使用IS NULL和IS NOT NULL。

null值在去重中的处理

在使用distinct关键字进行去重操作时，SQL标准规定所有的null值被视为等价，即在去重结果集中，所有null值仅保留一条。这保证了结果集的唯一性和一致性，无论原始数据中出现多少个null，最终结果中只会出现一个null。

|
select distinct 学院代码
from 学生;

如果有多个学生的学院代码为null，这个查询结果中null只会出现一次。这种处理方式在逻辑上是合理的，因为我们通常希望将所有“未知”的值归为一类。

null值在排序中的位置

在对包含null值的列进行排序时，数据库管理系统（DBMS）需根据内部实现确定null值在有序结果集中的具体位置。不同的DBMS对null排序的默认行为可能存在差异，通常会将null值统一置于排序结果的首部（NULLS FIRST）或尾部（NULLS LAST）。

|
select 姓名, 入学年份
from 学生
order by 入学年份;

在这个查询中，入学年份为null的学生可能会出现在结果的开头或结尾，具体位置取决于数据库系统的实现：

|
+--------+--------------+
| 姓名   | 入学年份     |
+--------+--------------+
| 李八   |         NULL |
| 张三   |         2021 |
| 李四   |         2021 |
| 王五   |         2021 |
| 赵六   |         2022 |
| 陈七   |         2023 |
+--------+--------------+

null值的连锁反应

null值在SQL查询中具有传递性，其影响可能扩展至整个结果集。以多表连接（join）为例：

|
select s.姓名, s.学院代码, c.学院名称
from 学生 s, 学院 c
where s.学院代码 = c.学院代码;

|
+--------+--------------+-----------------+
| 姓名   | 学院代码     | 学院名称        |
+--------+--------------+-----------------+
| 张三   | CS           | 计算机学院      |
| 王五   | CS           | 计算机学院      |
| 李八   | CS           | 计算机学院      |
| 李四   | MATH         | 数学学院        |
| 赵六   |

如果某个学生的学院代码为null，那么这个学生不会出现在连接查询的结果中，因为null值不等于任何值（包括另一个null值）。

这种行为有时是我们想要的（只显示有效数据），但有时也可能导致重要信息的丢失。因此在设计查询时，我们需要仔细考虑null值的影响。

聚合函数

在关系型数据库中，聚合函数（Aggregate Functions）用于对一组数据执行计算，返回单一的统计结果。与普通查询返回原始数据行不同，聚合函数能够对数据集进行汇总、统计和分析，例如计算平均值、最大值、最小值、总和以及计数等。这些函数在数据分析、报表生成和业务决策中具有重要作用。

为便于演示聚合函数的实际应用，我们需要先创建课程成绩表来存储学生的学习成绩：

|
-- 创建课程成绩表
create table 课程成绩 (
    学号 varchar(10),
    课程代码 varchar(8),
    成绩 numeric(5,2),  -- 成绩精确到小数点后两位
    primary key (学号, 课程代码),  -- 复合主键，确保每位学生每门课只有一条成绩记录
    foreign key (学号) references 学生(学号),
    foreign key (课程代码) references 课程(课程代码)
);

现在我们为学生选课系统补充一组课程成绩数据：

|
-- 插入一些课程成绩数据
insert into 课程成绩 values
('2021001', 'CS101', 85.5),
('2021001', 'MATH201', 92.0),
('2021002', 'MATH201', 78.5),
('2021003', 'CS101', 88.0),
('2021003'

基础聚合

SQL提供了五个核心聚合函数，每个都有其独特的作用：

计算平均值：avg函数 avg函数帮助我们计算数值的平均值，这在教育统计中特别有用：

|
select avg(成绩) as 平均成绩
from 课程成绩;

这个查询会计算所有已评分课程的平均成绩。需要注意的是，null值会被自动忽略，不参与平均值计算。

|
+--------------+
| 平均成绩     |
+--------------+
|    87.083333 |
+--------------+

求和计算：sum函数 sum函数用于计算数值的总和：

|
select sum(预算) as 总预算
from 学院
where 预算 is not null;

这个查询计算所有学院的预算总和，同样会忽略null值。

|
+------------+
| 总预算     |
+------------+
| 4300000.00 |
+------------+

计数统计：count函数 count函数有两种主要用法，它们的行为略有不同：

|
-- 统计总记录数（包括null值）
select count(*) as 总选课记录数
from 课程成绩;
 
-- 统计非null值的数量
select count(成绩) as 已评分记录数
from 课程成绩;

|
+--------------------+
| 总选课记录数       |
+--------------------+
|                  7 |
+--------------------+
 
+--------------------+
| 已评分记录数       |
+--------------------+
|                  6 |
+--------------------+

第一个查询统计所有记录，第二个查询只统计成绩不为null的记录。

寻找极值：min和max函数 这两个函数帮助我们找到最小值和最大值：

|
select 
    min(成绩) as 最低分,
    max(成绩) as 最高分
from 课程成绩
where 成绩 is not null;

|
+-----------+-----------+
| 最低分    | 最高分    |
+-----------+-----------+
|     78.50 |     92.00 |
+-----------+-----------+

分组

聚合函数在与group by子句结合使用时能够实现对数据集的分组汇总分析。单独使用聚合函数是对整体数据的统计，而结合group by后，可以针对不同分组分别进行统计和分析，从而实现更细粒度的数据洞察。

按学院统计 假设我们想要了解每个学院的学生数量：

|
select 学院代码, count(*) as 学生数量
from 学生
where 学院代码 is not null
group by 学院代码;

这个查询按学院代码对学生进行分组，然后统计每个组的学生数量：

|
+--------------+--------------+
| 学院代码     | 学生数量     |
+--------------+--------------+
| CS           |            3 |
| MATH         |            1 |
| PHYS         |            1 |
+--------------+--------------+

按课程统计成绩 我们可以计算每门课程的平均成绩：

|
select 
    课程代码,
    count(成绩) as 参与评分人数,
    avg(成绩) as 平均成绩,
    min(成绩) as 最低分,
    max(成绩) as 最高分
from 课程成绩
group by 课程代码;

这个查询为每门课程生成一个完整的统计报告:

|
+--------------+--------------------+--------------+-----------+-----------+
| 课程代码     | 参与评分人数       | 平均成绩     | 最低分    | 最高分    |
+--------------+--------------------+--------------+-----------+-----------+
| CS101        |                  2 |    86.750000 |     85.50 |     88.00 |
| MATH201      |                  2 |    85.250000 |     78.50 |     92.00 |
| PHYS101      |                  2

多字段分组

在实际数据分析中，常常需要基于多个维度进行分组统计，以便获得更具洞察力的细分结果：

|
select 
    学院代码,
    入学年份,
    count(*) as 学生数量
from 学生
where 学院代码 is not null and 入学年份 is not null
group by 学院代码, 入学年份
order by 学院代码, 入学年份;

这个查询按学院和入学年份的组合进行分组，让我们能够看到每个学院每年的招生情况：

|
+--------------+--------------+--------------+
| 学院代码     | 入学年份     | 学生数量     |
+--------------+--------------+--------------+
| CS           |         2021 |            2 |
| MATH         |         2021 |            1 |
| PHYS         |         2022 |            1 |
+--------------+--------------+--------------+

having子句：对分组结果进行过滤

在实际的数据分析过程中，我们经常需要对分组后的结果进行进一步筛选。此时，可以使用 having 子句对聚合后的分组结果进行过滤。需要注意的是，where 子句用于分组前对原始数据进行筛选，而 having 子句则作用于分组和聚合之后，专门针对分组结果进行条件约束。

例如，若需筛选出平均成绩大于85分的课程：

|
select 
    课程代码,
    count(成绩) as 参与评分人数,
    avg(成绩) as 平均成绩
from 课程成绩
group by 课程代码
having avg(成绩) > 85;

|
+--------------+--------------------+--------------+
| 课程代码     | 参与评分人数       | 平均成绩     |
+--------------+--------------------+--------------+
| CS101        |                  2 |    86.750000 |
| MATH201      |                  2 |    85.250000 |
| PHYS101      |                  2 |    89.250000 |
+--------------+--------------------+--------------+

或者找出学生数量超过1人的学院：

|
select 
    学院代码,
    count(*) as 学生数量
from 学生
where 学院代码 is not null
group by 学院代码
having count(*) > 1;

|
+--------------+--------------+
| 学院代码     | 学生数量     |
+--------------+--------------+
| CS           |            3 |
+--------------+--------------+

记住having子句和where子句的区别：where用于过滤原始记录，having用于过滤分组结果。在having子句中，只能使用分组字段或聚合函数的结果。

复杂分组查询

在实际的数据分析与报表需求中，通常需要跨多个数据表进行关联查询与统计分析。以下示例展示了如何通过多表连接，按学院维度统计学生人数、已评分科目数及平均成绩等关键指标：

|
select 
    c.学院名称,
    count(distinct s.学号) as 学生总数,
    count(cj.成绩) as 已评分科目数,
    avg(cj.成绩) as 平均成绩
from 学院 c
natural join 学生 s
left join 课程成绩 cj on s.学号 =

|
+-----------------+--------------+--------------------+--------------+
| 学院名称        | 学生总数     | 已评分科目数       | 平均成绩     |
+-----------------+--------------+--------------------+--------------+
| 计算机学院      |            3 |                  4 |    89.250000 |
| 物理学院        |            1 |                  1 |    87.000000 |
| 数学学院        |            1 |                  1 |    78.500000 |

该查询通过对学院、学生和课程成绩三张表进行关联，按学院维度分组，分别统计每个学院的学生总数、已评分课程数及平均成绩，并根据平均成绩进行降序排序。

聚合函数是数据分析的核心工具。掌握了聚合函数，你就能够从大量的原始数据中提取出有价值的统计信息，为决策提供数据支持。

嵌套子查询

随着业务需求的不断提升，单一的SQL查询往往难以满足复杂的数据分析和业务逻辑需求。此时，嵌套子查询（Subquery）成为构建复杂查询的重要手段。通过在一个查询语句中嵌入另一个查询，可以实现多层次的数据筛选、聚合与关联，从而高效地解决如分组统计、条件过滤、层级分析等复杂场景。嵌套子查询不仅提升了SQL的表达能力，也有助于将复杂问题分解为多个有机协作的子问题，使查询结构更加清晰、可维护。

子查询的基本概念

子查询，顾名思义，就是嵌套在主查询中的查询语句。它可以出现在SQL语句的多个位置：WHERE子句、FROM子句、SELECT子句，甚至HAVING子句中。子查询为主查询提供数据，就像是在回答主问题之前先回答几个子问题。

让我们从一个简单的例子开始。假设我们想要找到所有成绩高于平均成绩的学生：

|
select 学号, 课程代码, 成绩
from 课程成绩
where 成绩 > (select avg(成绩) from 课程成绩 where 成绩 is not null);

这个查询中，括号内的select avg(成绩) from 课程成绩就是一个子查询，它先计算出平均成绩，然后主查询使用这个结果来筛选高于平均成绩的记录：

|
+---------+--------------+--------+
| 学号    | 课程代码     | 成绩   |
+---------+--------------+--------+
| 2021001 | MATH201      |  92.00 |
| 2021003 | CS101        |  88.00 |
| 2021003 | PHYS101      |  91.50 |
+---------+--------------+--------+

IN和NOT IN

IN操作符用于判断某个值是否存在于子查询返回的结果集中，常用于实现集合成员资格的判断。

例如，若需查询所有选修了计算机学院课程的学生：

|
select distinct 学号, 姓名
from 学生 
where 学号 in (
    select distinct cj.学号
    from 课程成绩 cj, 课程 k
    where cj.课程代码 = k.课程代码 
      and k.学院代码 = 'CS'
);

这个查询首先通过子查询找出所有选修了计算机学院课程的学号，然后主查询根据这些学号找出对应的学生信息。

|
+---------+--------+
| 学号    | 姓名   |
+---------+--------+
| 2021001 | 张三   |
| 2021003 | 王五   |
| 2022001 | 赵六   |
+---------+--------+

相反，如果我们想要找到没有选修任何计算机学院课程的学生：

|
select 学号, 姓名
from 学生 
where 学号 not in (
    select distinct cj.学号
    from 课程成绩 cj, 课程 k
    where cj.课程代码 = k.课程代码 
      and k.学院代码 = 'CS'
      and cj.学号 is not null
);

|
+---------+--------+
| 学号    | 姓名   |
+---------+--------+
| 2021002 | 李四   |
| 2023001 | 陈七   |
| 2023002 | 李八   |
+---------+--------+

使用NOT IN时要特别小心NULL值。如果子查询的结果中包含NULL值，NOT IN可能不会返回期望的结果。在使用NOT IN时，最好在子查询中排除NULL值。

比较运算符

SOME（或ANY）表示“与子查询结果集中的至少一个值满足条件”。例如，若要检索出成绩高于任意一门计算机学院课程平均成绩的所有选课记录，可以使用如下查询：

|
select 学号, 课程代码, 成绩
from 课程成绩
where 成绩 > some (
    select avg(cj.成绩)
    from 课程成绩 cj, 课程 k
    where cj.课程代码 = k.课程代码 
      and k.学院代码 = 'CS'
      and cj.成绩 is not null
    group by cj.课程代码

|
+---------+--------------+--------+
| 学号    | 课程代码     | 成绩   |
+---------+--------------+--------+
| 2021001 | MATH201      |  92.00 |
| 2021003 | CS101        |  88.00 |
| 2021003 | PHYS101      |  91.50 |
| 2022001 | PHYS101      |  87.00 |
+---------+--------------+--------+

ALL 运算符用于判断某个条件是否对子查询结果集中的所有值都成立。例如，若要查询成绩高于所有计算机学院课程平均成绩的选课记录，可以使用如下语句：

|
select 学号, 课程代码, 成绩
from 课程成绩
where 成绩 > all (
    select avg(cj.成绩)
    from 课程成绩 cj, 课程 k
    where cj.课程代码 = k.课程代码 
      and k.学院代码 = 'CS'
      and cj.成绩 is not null
    group by cj.课程代码

|
+---------+--------------+--------+
| 学号    | 课程代码     | 成绩   |
+---------+--------------+--------+
| 2021001 | MATH201      |  92.00 |
| 2021003 | CS101        |  88.00 |
| 2021003 | PHYS101      |  91.50 |
| 2022001 | PHYS101      |  87.00 |
+---------+--------------+--------+

存在性测试：EXISTS

EXISTS 运算符用于判断子查询是否返回至少一行结果。与直接比较不同，EXISTS 只关心子查询是否存在满足条件的记录，而不关心具体的返回值。在 SQL 查询优化和复杂逻辑表达中，EXISTS 常用于高效地进行存在性测试，尤其适合相关子查询的场景。

示例：查询至少选修过一门课程的学生：

|
select 学号, 姓名
from 学生 s
where exists (
    select 1
    from 课程成绩 cj
    where cj.学号 = s.学号
);

注意这里的子查询使用了相关变量s.学号，这种子查询被称为相关子查询，它会为外层查询的每一行都执行一次：

|
+---------+--------+
| 学号    | 姓名   |
+---------+--------+
| 2021001 | 张三   |
| 2021002 | 李四   |
| 2021003 | 王五   |
| 2022001 | 赵六   |
+---------+--------+

NOT EXISTS用于测试不存在性。找到没有选修任何课程的学生：

|
select 学号, 姓名
from 学生 s
where not exists (
    select 1
    from 课程成绩 cj
    where cj.学号 = s.学号
);

|
+---------+--------+
| 学号    | 姓名   |
+---------+--------+
| 2023001 | 陈七   |
| 2023002 | 李八   |
+---------+--------+

FROM子句中的子查询

子查询同样可以出现在FROM子句中，此时该子查询会被数据库系统当作一个派生表（临时表）处理。这种写法常用于需要对聚合结果进行进一步分析或筛选的场景。

例如，我们想查询平均成绩最高的学院：

|
select 学院名称, 平均成绩
from (
    select 
        c.学院名称,
        avg(cj.成绩) as 平均成绩
    from 学院 c
    natural join 课程 k
    join 课程成绩 cj on k.课程代码 = cj.课程代码
    where cj.成绩 is not null
    group by c.

|
+--------------+--------------+
| 学院名称     | 平均成绩     |
+--------------+--------------+
| 物理学院     |    89.250000 |
+--------------+--------------+

SELECT子句中的子查询

当子查询在SELECT子句中返回单个值（即标量）时，这种用法称为“标量子查询”（Scalar Subquery），常用于为每一行动态计算相关的聚合或派生值。

例如，统计每位学生所选课程的数量：

|
select 
    s.学号,
    s.姓名,
    (select count(*) 
     from 课程成绩 cj 
     where cj.学号 = s.学号) as 选课数量
from 学生 s;

|
+---------+--------+--------------+
| 学号    | 姓名   | 选课数量     |
+---------+--------+--------------+
| 2021001 | 张三   |            2 |
| 2021002 | 李四   |            1 |
| 2021003 | 王五   |            2 |
| 2022001 | 赵六   |            2 |
| 2023001 |

计算每个学生成绩与全体平均成绩的差值：

|
select 
    学号,
    课程代码,
    成绩,
    成绩 - (select avg(成绩) from 课程成绩 where 成绩 is not null) as 与平均分差值
from 课程成绩
where 成绩 is not null;

|
+---------+--------------+--------+--------------------+
| 学号    | 课程代码     | 成绩   | 与平均分差值       |
+---------+--------------+--------+--------------------+
| 2021001 | CS101        |  85.50 |          -1.583333 |
| 2021001 | MATH201      |  92.00 |           4.916667 |
| 2021002 | MATH201      |  78.50 |          -8.583333 |
| 2021003

WITH子句

WITH子句（Common Table Expression, CTE，公共表表达式）是一种在SQL查询中定义临时结果集的机制，常用于分解复杂查询、提升可读性和可维护性。CTE在查询执行期间相当于临时的命名结果集，可被主查询或后续的CTE引用，功能类似于临时视图，但作用范围仅限于当前语句块。

|
with 学院平均成绩 as (
    select 
        k.学院代码,
        avg(cj.成绩) as 平均成绩
    from 课程 k
    join 课程成绩 cj on k.课程代码 = cj.课程代码
    where cj.成绩 is not null
    group by k.学院代码
),
全校平均成绩

|
+-----------------+--------------+-----------------+-----------------+
| 学院名称        | 平均成绩     | 总平均成绩      | 与全校差值      |
+-----------------+--------------+-----------------+-----------------+
| 物理学院        |    89.250000 |       87.083333 |        2.166667 |
| 计算机学院      |    86.750000 |       87.083333 |       -0.333333 |
| 数学学院        |    85.250000 |       87.083333 |       -1.833333 |

这个查询使用两个CTE来分别计算学院平均成绩和全校平均成绩，然后在主查询中使用它们。

嵌套子查询是SQL中最强大的功能之一，但也是最容易出错的地方。编写复杂子查询时，建议先从内到外逐层测试，确保每个子查询都能返回正确的结果，然后再组合成完整的查询。

数据库修改

在数据库管理系统（DBMS）中，数据操作分为查询（读操作）和数据修改（写操作）两大类。查询用于检索和分析数据，而数据修改则用于对数据库中的数据进行增、删、改等变更。 SQL（结构化查询语言）中，数据修改主要通过三种基本语句实现：插入（INSERT）、更新（UPDATE）和删除（DELETE）。这三类操作统称为数据操作语言（DML, Data Manipulation Language），是数据库日常维护和业务处理的核心工具。

插入操作

INSERT语句用于向数据库表中新增数据记录，是实现数据持久化的基本手段。最常见的用法是向表中插入一条完整的数据行。其实我们前面已经很多次使用过插入操作了，比如我们给学生选课系统添加学院信息、学生信息、课程信息等。

基础插入操作

让我们为学生选课系统再来添加一个新学生：

|
insert into 学生 
values ('2024001', '王小华', 'CS', 2024);

这种方式要求我们按照表定义时字段的顺序提供所有值。更安全的做法是明确指定字段名：

|
insert into 学生 (学号, 姓名, 学院代码, 入学年份)
values ('2024002', '李小明', 'MATH', 2024);

这种方式的好处是即使表结构发生变化（比如增加了新字段），我们的INSERT语句仍然能正常工作。

处理部分字段的插入

在实际应用中，部分情况下我们仅掌握部分字段的数据，此时可通过指定字段名，仅插入已知信息，未指定的字段将自动赋予默认值或NULL：

|
insert into 学生 (学号, 姓名, 入学年份)
values ('2024003', '张三丰', 2024);

这里我们没有提供学院代码，该字段将被设置为null（假设该字段允许null值）。

批量插入操作

我们可以在一条INSERT语句中插入多条记录：

|
insert into 课程成绩 (学号, 课程代码, 成绩) values
('2024001', 'CS101', 88.5),
('2024001', 'MATH201', 92.0),
('2024002', 'MATH201', 85.5),
('2024002', 'PHYS101', 90.0);

这种批量插入方式比多条单独的INSERT语句更高效。

基于查询结果的插入

在实际数据库应用中，常见的高级插入操作之一是通过查询结果将数据批量插入到目标表中。假设我们需要根据业务规则（如筛选出所有数学学院且平均成绩高于85分的学生）生成荣誉名单，可以利用INSERT ... SELECT语句实现：

|
-- 首先创建荣誉名单表
create table 荣誉名单 (
    学号 varchar(10),
    姓名 varchar(20),
    学院名称 varchar(50),
    平均成绩 numeric(4,1),
    primary key (学号)
);
 
-- 基于查询结果插入数据
insert into 荣誉名单 (学号, 姓名, 学院名称, 平均成绩)
select 
    s.学号,

更新操作

UPDATE 语句用于对数据库中已存在的记录进行修改。在实际应用中，数据经常因业务需求发生变动，例如学生更换专业、课程学分调整等，这些场景都需要通过 UPDATE 操作来确保数据库信息的实时性和准确性。

基础更新操作

假设需要将学号为"2024001"的学生（王小华）所属学院调整为物理学院：

|
update 学生 
set 学院代码 = 'PHYS'
where 学号 = '2024001';

UPDATE 语句也支持一次性修改多列的值，实现对多字段的原子性更新：

|
update 学生 
set 学院代码 = 'PHYS', 入学年份 = 2023
where 学号 = '2024001';

条件更新操作

更复杂的更新可能涉及多个条件。比如我们要为所有2021年入学的计算机学院学生的某门课程成绩加5分（可能是因为考试难度调整）：

|
update 课程成绩 
set 成绩 = 成绩 + 5
where 课程代码 = 'CS101' 
  and 学号 in (
      select 学号 
      from 学生 
      where 学院代码 = 'CS' and 入学年份 = 2021
  )
  and 成绩 is not null;

基于计算的更新

我们可以使用复杂的表达式来更新字段值。比如根据通胀率调整学院预算：

|
update 学院 
set 预算 = 预算 * 1.05  -- 增加5%
where 预算 is not null;

条件更新：CASE语句的妙用

有时候我们需要根据不同条件进行不同的更新。假设要根据成绩等级给予不同的奖励分数：

|
update 课程成绩 
set 成绩 = case 
    when 成绩 >= 90 then 成绩 + 2  -- 优秀学生额外加2分
    when 成绩 >= 80 then 成绩 + 1  -- 良好学生额外加1分
    else 成绩  -- 其他学生成绩不变
end
where 成绩 is not null;

删除操作

DELETE 语句用于从关系表中删除满足特定条件的记录。由于删除操作具有不可逆性，执行时需特别谨慎，建议在生产环境操作前做好数据备份，并严格确认删除条件以防误删。

基础删除操作

删除一个特定的学生记录：

|
delete from 学生 
where 学号 = '2024003';

条件删除操作

删除所有没有成绩记录的选课记录（可能是学生退课了）：

|
delete from 课程成绩 
where 成绩 is null;

基于子查询的删除

删除所有没有选修任何课程的学生记录：

|
delete from 学生 
where 学号 not in (
    select distinct 学号 
    from 课程成绩 
    where 学号 is not null
);

删除操作是不可逆的！在生产环境中执行删除操作前，一定要先备份相关数据，并且建议先用SELECT语句测试WHERE条件，确认要删除的记录确实是你想要删除的。

复杂修改操作的实际应用

场景一：学期末成绩处理

学期结束时，我们需要处理各种成绩相关的操作：

|
-- 1. 为缺席考试的学生记录0分
update 课程成绩 
set 成绩 = 0 
where 成绩 is null;
 
-- 2. 插入补考机会记录
insert into 课程成绩 (学号, 课程代码, 成绩)
select 学号, 课程代码, null
from 课程成绩 
where 成绩 < 60
  and 学号 not in (
      select 学号 
      from 课程成绩 as 内层
      where 内层

场景二：数据迁移和清理

在系统升级或数据整理时，我们可能需要进行批量的数据迁移：

|
-- 创建历史数据归档表
create table 历史成绩 as 
select * from 课程成绩 where 1=0;  -- 复制结构但不复制数据
 
-- 将2021年以前的成绩数据迁移到历史表
insert into 历史成绩 
select cj.*
from 课程成绩 cj
join 学生 s on cj.学号 = s.学号
where s.入学年份 < 2021

修改操作的执行顺序和注意事项

UPDATE 与 DELETE 语句的标准执行流程如下

优先解析并评估 WHERE 子句，精确筛选出待处理的数据行；
对于 UPDATE 语句，依次计算 SET 子句中各目标列的新值（基于已选定的数据行）；
最后，数据库系统对筛选出的数据行执行实际的更新或删除操作。

这种顺序确保了操作的一致性。比如在这个UPDATE语句中：

|
update 课程成绩 
set 成绩 = 成绩 * 1.1 
where 成绩 < (select avg(成绩) from 课程成绩);

系统会首先计算平均成绩，然后找出低于平均成绩的记录，最后对这些记录进行更新。

避免修改操作中的常见陷阱

|
-- 错误示例：可能导致无限循环或意外结果
update 学生 
set 学号 = 学号 + 1000
where 入学年份 = 2024;
 
-- 正确做法：使用更明确的标识符
update 学生 
set 学号 = concat('NEW', 学号)
where 入学年份 = 2024 and 学号 not like 'NEW%';

事务

在进行复杂的数据修改时，通常需要保证一组操作具备原子性，即要么全部成功提交，要么全部回滚以保持数据一致性。为实现这一点，我们需引入事务（Transaction）机制。

|
-- 开始事务
begin transaction;
 
-- 转账操作示例：从学院A的预算转移到学院B
update 学院 set 预算 = 预算 - 100000 where 学院代码 = 'CS';
update 学院 set 预算 = 预算 + 100000 where 学院代码 = 'MATH';
 
-- 检查操作结果，如果正确则提交
commit;
 
-- 如果出现问题则回滚
-- rollback;

SQL的完整拼图

这一部分我们涵盖了很多内容，我们无法在一个篇幅中涵盖所有细节，如果你想更细节的学习SQL，你也可以在我们的平台中学习我们专门的SQL课程。

实战演练

练习一：创建员工管理系统数据库

请你为一家公司设计员工管理系统，需要创建员工表和部门表。

员工表包含以下字段：

员工编号（employee_id, varchar(10), 主键）
姓名（name, varchar(50), 非空）
部门编号（department_id, varchar(10)）
入职日期（hire_date, date）
薪资（salary, numeric(10,2)）

部门表包含以下字段：

部门编号（department_id, varchar(10), 主键）
部门名称（department_name, varchar(100), 非空）
部门经理（manager_id, varchar(10)）
预算（budget, numeric(12,2)）

请编写SQL语句创建这两个表，包含适当的约束条件。

|
-- 创建部门表
create table 部门 (
    部门编号 varchar(10) primary key,
    部门名称 varchar(100) not null,
    部门经理 varchar(10),
    预算 numeric(12,2)
);
 
-- 创建员工表
create table 员工 (
    员工编号 varchar(

练习二：插入测试数据

请你使用上题创建的表结构，插入以下测试数据：

部门数据：

部门编号: 'HR001', 部门名称: '人力资源部', 预算: 500000.00
部门编号: 'IT001', 部门名称: '信息技术部', 预算: 1200000.00
部门编号: 'FIN001', 部门名称: '财务部', 预算: 800000.00

员工数据：

张三, 员工编号: 'E001', 部门: 'HR001', 入职日期: '2023-01-15', 薪资: 8000.00
李四, 员工编号: 'E002', 部门: 'IT001', 入职日期: '2023-03-01', 薪资: 12000.00
王五, 员工编号: 'E003', 部门: 'IT001', 入职日期: '2023-06-15', 薪资: 10000.00
赵六, 员工编号: 'E004', 部门: 'FIN001', 入职日期: '2022-12-01', 薪资: 15000.00
孙七, 员工编号: 'E005', 部门: 'HR001', 入职日期: '2024-01-01', 薪资: 7500.00

请编写SQL语句插入这些数据。

|
-- 插入部门数据
insert into 部门 values
('HR001', '人力资源部', null, 500000.00),
('IT001', '信息技术部', null, 1200000.00),
('FIN001', '财务部', null, 800000.00);
 
-- 插入员工数据
insert into 员工 values

练习三：基础查询练习

请你基于上述员工和部门数据，完成以下查询：

查询所有员工的姓名、部门名称和薪资（需要连接两个表）
查询薪资高于10000的员工信息
查询人力资源部的所有员工
按薪资降序排列所有员工信息

|
-- 1. 查询所有员工的姓名、部门名称和薪资
select e.姓名, d.部门名称, e.薪资
from 员工 e
join 部门 d on e.部门编号 = d.部门编号;
 
-- 2. 查询薪资高于10000的员工信息
select 姓名, 部门编号, 薪资
from 员工
where 薪资 > 10000;
 
-- 3. 查询人力资源部的所有员工
select e

练习四：聚合查询练习

请基于员工和部门数据，完成以下统计查询：

计算每个部门的平均薪资
统计每个部门有多少员工
找出薪资最高和最低的员工
计算公司总薪资支出和平均薪资

|
-- 1. 计算每个部门的平均薪资
select d.部门名称, avg(e.薪资) as 平均薪资
from 员工 e
join 部门 d on e.部门编号 = d.部门编号
group by d.部门名称;
 
-- 2. 统计每个部门有多少员工
select d.部门名称, count(e.员工编号

练习五：子查询练习

使用子查询完成以下需求：

找出薪资高于部门平均薪资的员工
查询存在员工的部门信息
找出没有员工的部门
查询薪资排名前两名的员工

|
-- 1. 找出薪资高于部门平均薪资的员工
select e.姓名, e.薪资, d.部门名称
from 员工 e
join 部门 d on e.部门编号 = d.部门编号
where e.薪资 > (
    select avg(e2.薪资)
    from 员工 e2
    where e2

练习六：数据修改练习

基于现有数据，完成以下数据修改操作：

为所有员工增加5%的薪资
将赵六调到信息技术部
删除薪资低于8000的员工记录
为财务部增加预算（原有基础上增加10%）

|
-- 1. 为所有员工增加5%的薪资
update 员工
set 薪资 = 薪资 * 1.05;
 
-- 2. 将赵六调到信息技术部
update 员工
set 部门编号 = 'IT001'
where 姓名 = '赵六';
 
-- 3. 删除薪资低于8000的员工记录（注意：先检查谁会被删除）
-- 先查询要删除的记录
select 姓名, 薪资 from 员工 where 薪资 < 8000;
 
-- 然后删除
delete