lec_6 Advanced SQL - 数据库 - 计算机科学

# 嵌套查询（Nested Queries）

# 1. 嵌套查询的定义

嵌套查询是指在一个 SQL 查询的 WHERE 、 FROM 、或 SELECT 子句中嵌套另一个查询（子查询）。子查询先执行，其结果用于主查询。嵌套查询常用于需要多步过滤、动态生成条件或复杂的数据处理的场景。

# 2. 嵌套查询的类型

嵌套查询可以根据使用位置分为几种类型：

# (1) `WHERE` 子句中的子查询

这种子查询返回单个值或一组值，主查询会根据这些返回的结果进行过滤。

示例：

row

SELECT name
FROM Students
WHERE student_id IN (
    SELECT student_id
    FROM Enrollments
    WHERE course_code = 'COMP1234'
);

解释：此查询查找所有选修了 COMP1234 课程的学生姓名。子查询返回所有注册该课程的学生 ID，主查询根据这些 ID 过滤学生表。

# (2) `FROM` 子句中的子查询

子查询可以作为临时表使用。主查询可以基于这个临时表进行查询。

示例：

row

SELECT AVG(total_marks)
FROM (
    SELECT SUM(marks) AS total_marks
    FROM Grades
    GROUP BY student_id
) AS StudentTotals;

解释：子查询计算每个学生的总分数，然后主查询计算所有学生总分数的平均值。子查询作为临时表 StudentTotals 使用。

# (3) `SELECT` 子句中的子查询

在 SELECT 子句中，子查询会为主查询的每一行执行一次，用于动态生成列值。

示例：

row

SELECT name, 
       (SELECT COUNT(*) 
        FROM Enrollments 
        WHERE Enrollments.student_id = Students.student_id) AS num_courses
FROM Students;

解释：此查询返回每个学生的名字以及他们注册的课程数量。子查询为每个学生执行一次。

# 3. 相关子查询（Correlated Subqueries）

相关子查询是一种特殊的嵌套查询，它依赖于外部查询中的每一行，因此每次执行都会使用主查询的当前行的值。与普通子查询不同，相关子查询不能独立执行。

示例：

row

SELECT name
FROM Students S
WHERE EXISTS (
    SELECT 1
    FROM Enrollments E
    WHERE E.student_id = S.student_id
    AND E.course_code = 'COMP1234'
);

解释：对于每个学生 S ，子查询检查该学生是否注册了 COMP1234 课程。子查询依赖于主查询的当前学生行。

# 4. 子查询与运算符

子查询可以与多种运算符结合使用，常见的运算符有：

IN ：用于检查一个值是否在子查询的结果集内。
- 示例： WHERE column_name IN (SELECT ...)
EXISTS ：用于检查子查询是否返回任何行。
- 示例： WHERE EXISTS (SELECT ...)
ANY 和 ALL ：用于与比较运算符结合使用，判断某值是否与子查询结果集中的任意值或所有值进行比较。
- 示例： WHERE column_name > ANY (SELECT ...)

# 5. 嵌套查询的性能

虽然嵌套查询在处理复杂逻辑时非常强大，但它们可能会对性能产生影响，特别是当子查询涉及大量数据时。因此，在某些情况下，可以将嵌套查询重构为 JOIN 操作以提高性能。

示例：

row

SELECT S.name
FROM Students S
JOIN Enrollments E ON S.student_id = E.student_id
WHERE E.course_code = 'COMP1234';

这种 JOIN 查询有时可以替代子查询，提高查询效率，特别是在数据量较大时。

# 6. 嵌套查询的用途

嵌套查询非常适合用于以下场景：

需要多层过滤条件。
需要根据另一张表动态生成数据。
需要在查询中嵌入复杂的逻辑判断。

# 总结

嵌套查询提供了强大的数据处理能力，允许你在查询中使用其他查询的结果进行过滤、计算或动态生成数据。Lecture 6 详细讲解了如何利用嵌套查询处理复杂的业务需求，以及如何通过 IN 、 EXISTS 、 JOIN 等技术优化查询性能。

# 视图（Views）

# 1. 什么是视图？

视图是基于 SQL 查询结果生成的 虚拟表。视图本身不存储实际数据，它保存的是一个 SQL 查询定义。每当访问视图时，数据库会执行该查询并返回结果。
视图用于简化复杂查询、提升数据安全性、以及为数据提供抽象层。

# 2. 创建视图

视图使用 CREATE VIEW 语句创建。创建视图时，定义的查询可以像表一样使用。

语法：

row

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

示例：

row

CREATE VIEW high_salary_employees AS
SELECT employee_id, name, salary
FROM Employees
WHERE salary > 50000;

这个视图筛选出所有工资超过 50,000 的员工信息。

# 3. 使用视图

视图可以像物理表一样被查询和操作。
示例：
row
1
SELECT * FROM high_salary_employees;
这会返回所有工资超过 50,000 的员工数据。

# 4. 修改视图

视图可以通过 CREATE OR REPLACE VIEW 语句修改，不需要先删除再创建。

示例：

row

CREATE OR REPLACE VIEW high_salary_employees AS
SELECT employee_id, name, salary, department
FROM Employees
WHERE salary > 60000;

这段代码更新了视图的定义，改变了工资过滤条件并增加了一个 department 列。

# 5. 视图的优势

简化复杂查询：可以通过视图封装复杂查询，使其更易于复用。
数据安全性：可以通过视图隐藏表中的某些列，限制用户只能访问特定数据。
提高可读性：视图为复杂的数据处理提供了抽象层，代码更加简洁易读。

# 6. 视图的局限性

性能问题：视图是动态生成的，涉及复杂查询的视图可能会降低查询性能，尤其是涉及大量数据时。
只读视图：有些视图是只读的，不能进行 INSERT 、 UPDATE 或 DELETE 操作，尤其是当视图基于多表、聚合函数或 DISTINCT 时。

# 7. 删除视图

可以使用 DROP VIEW 语句删除视图。
语法：
row
1
DROP VIEW view_name;

# 8. 表和视图的区别

特性	表（Table）	视图（View）
数据存储	实际存储数据	不存储数据，基于查询的虚拟表
物理存储	有独立的存储空间	没有独立的存储空间，依赖底层表
数据操作	支持 `INSERT` 、 `UPDATE` 、 `DELETE` 操作	视图可能只读，有些视图可更新
用途	存储原始数据	简化查询、提供数据抽象、安全性控制
性能	取决于表的大小和索引	取决于视图的查询复杂度
安全性	通过权限管理控制对表的访问	通过视图控制用户对特定数据的访问

# 聚合与分组（Aggregation and Grouping）

# 1. 聚合函数（Aggregation Functions）

聚合函数用于对一组数据执行计算，并返回一个单一值。这些函数通常用于统计、求和、平均值等操作。常见的聚合函数包括：

COUNT() ：统计行的数量。
SUM() ：计算数值列的总和。
AVG() ：计算数值列的平均值。
MAX() ：返回列中的最大值。
MIN() ：返回列中的最小值。

# 示例：

row

SELECT COUNT(*) AS total_students, 
       AVG(age) AS average_age, 
       MAX(salary) AS highest_salary 
FROM Employees;

解释：
- COUNT(*) : 统计表中的总行数。
- AVG(age) : 计算员工的平均年龄。
- MAX(salary) : 返回最高的工资。

# 2. 分组（GROUP BY）

GROUP BY 用于将查询结果按一列或多列的值进行分组。然后可以对每个分组应用聚合函数进行汇总计算。 GROUP BY 常用于分类统计、按类别聚合数据等场景。

# 基本语法：

row

1
2
3

SELECT column1, column2, aggregate_function(column3)
FROM table_name
GROUP BY column1, column2;

# 示例 1：按部门统计员工数量

row

1	SELECT department, COUNT(*) AS num_employees FROM Employees GROUP BY department;

解释：
- GROUP BY department ：将员工按部门分组。
- COUNT(*) ：计算每个部门的员工数量。

# 示例 2：按课程统计学生注册人数

row

1
2
3

SELECT course_id, COUNT(student_id) AS num_students
FROM Enrollments
GROUP BY course_id;

解释：
- GROUP BY course_id ：按课程 ID 分组。
- COUNT(student_id) ：统计每个课程注册的学生数量。

# 3. 结合 `GROUP BY` 和聚合函数

在 GROUP BY 后，可以结合聚合函数对每个分组的数据进行统计计算。这种组合非常适用于分类汇总数据。

# 示例 3：按城市统计平均工资

row

1
2
3

SELECT city, AVG(salary) AS average_salary
FROM Employees
GROUP BY city;

解释：
- GROUP BY city ：将员工按所在城市分组。
- AVG(salary) ：计算每个城市的平均工资。

# 4. `HAVING` 子句

HAVING 子句用于在分组之后对分组后的数据进行过滤。与 WHERE 子句不同， HAVING 主要用于处理聚合后的结果集，通常结合 GROUP BY 使用。

# 示例 4：过滤出注册人数大于 100 的课程

row

SELECT course_id, COUNT(student_id) AS num_students
FROM Enrollments
GROUP BY course_id
HAVING COUNT(student_id) > 100;

解释：
- GROUP BY course_id ：将数据按课程 ID 分组。
- HAVING COUNT(student_id) > 100 ：过滤出注册人数超过 100 的课程。

# 5. `GROUP BY` 与 `ORDER BY` 一起使用

可以使用 GROUP BY 和 ORDER BY 组合，对分组后的结果进行排序。通常用于根据聚合值对结果进行排序。

# 示例 5：按部门统计员工数量并按数量排序

row

SELECT department, COUNT(*) AS num_employees
FROM Employees
GROUP BY department
ORDER BY num_employees DESC;

解释：
- GROUP BY department ：按部门分组。
- COUNT(*) ：计算每个部门的员工数量。
- ORDER BY num_employees DESC ：按员工数量降序排序。

# 总结

聚合函数：用于对一组数据进行汇总操作，如求和、计数、最大值、最小值、平均值等。
GROUP BY ：用于将查询结果按列值分组，并对每个分组应用聚合函数进行统计。
HAVING 子句：用于在分组之后对结果进行进一步的过滤，常与 GROUP BY 结合使用。
ORDER BY 子句：可以与 GROUP BY 一起使用，用于对分组后的结果进行排序。

# set 运算符

# 1. UNION：并集运算符

作用： UNION 运算符用于合并两个查询的结果集，并去除重复的行。换句话说， UNION 返回两个查询结果的并集，并只保留唯一的行。

语法：

row

SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;

特点：
- 默认去除重复行。
- 列的数量、顺序、数据类型必须在两个查询中一致。

示例：

row

1
2
3

SELECT name FROM Employees
UNION
SELECT name FROM Customers;

# 2. UNION ALL：并集（包含重复值）

作用： UNION ALL 与 UNION 类似，但它不会去除重复行，它会返回所有结果，包括重复的行。

语法：

row

SELECT column1, column2, ...
FROM table1
UNION ALL
SELECT column1, column2, ...
FROM table2;

示例：

row

1
2
3

SELECT name FROM Employees
UNION ALL
SELECT name FROM Customers;

# 3. INTERSECT：交集运算符

作用： INTERSECT 运算符用于返回两个查询结果的交集，即两个结果集中都包含的行。

语法：

row

SELECT column1, column2, ...
FROM table1
INTERSECT
SELECT column1, column2, ...
FROM table2;

特点：
- 只返回两个查询结果中都存在的行。
- 列的数量、顺序、数据类型必须在两个查询中一致。

示例：

row

1
2
3

SELECT name FROM Employees
INTERSECT
SELECT name FROM Customers;

返回既在 Employees 表中也在 Customers 表中的 name 。

# 4. EXCEPT（或 `MINUS` ）：差集运算符

作用： EXCEPT （在某些数据库中也叫 MINUS ）运算符用于返回第一个查询结果集中有，但第二个查询结果集中没有的行。它相当于集合的差集操作。

语法：

row

SELECT column1, column2, ...
FROM table1
EXCEPT
SELECT column1, column2, ...
FROM table2;

特点：
- 返回只在第一个查询中出现的行，而在第二个查询中不存在的行。
- 列的数量、顺序、数据类型必须在两个查询中一致。

示例：

row

1
2
3

SELECT name FROM Employees
EXCEPT
SELECT name FROM Customers;

返回在 Employees 表中但不在 Customers 表中的 name 。

# 注意事项：

列的数量和类型：使用 SET 运算符的查询中，两个查询返回的列的数量、顺序、数据类型必须相同。
排序：通常情况下， ORDER BY 子句只能应用在最终的结果集上，而不能分别应用到每个单独的查询。

# 示例场景：

查询在两个表中都有的记录（交集）：

row

1
2
3

SELECT email FROM Employees
INTERSECT
SELECT email FROM Customers;

返回同时存在于 Employees 和 Customers 表中的电子邮件地址。

查询只存在于一个表中的记录（差集）：
row
1
2
3
SELECT email FROM Employees
EXCEPT
SELECT email FROM Customers;
返回存在于 Employees 表中但不存在于 Customers 表中的电子邮件地址。

合并两个表中的记录（并集）：

row

1
2
3

SELECT email FROM Employees
UNION
SELECT email FROM Customers;

返回 Employees 和 Customers 表中所有唯一的电子邮件地址。

# 总结：

UNION ：合并两个查询的结果集，去除重复行。
UNION ALL ：合并两个查询的结果集，保留所有行（包括重复行）。
INTERSECT ：返回两个查询结果集的交集（都存在的行）。
EXCEPT （或 MINUS ）：返回只存在于第一个查询结果集中的行。

SET 运算符在 SQL 查询中非常有用，特别是在需要组合、比较或处理多个数据集时。

# NULL Values and Three-valued Logic

# 1. NULL 值

NULL 表示缺失的、未知的或不可用的值。它不是空字符串 ( '' ) 或零 ( 0 )，而是一个特殊的值，表示该列在某行中没有数据。

# 如何理解 NULL：

NULL 不等于任何值，包括它自身。也就是说， NULL != NULL ， NULL = NULL 的结果也是 FALSE 或 UNKNOWN ，因为 NULL 表示未知，因此两个 NULL 不能被比较为相等。
在计算中，涉及 NULL 的表达式结果通常也是 NULL。例如， 5 + NULL 或 NULL + NULL 的结果都是 NULL。

# 查询中的 NULL：

在查询中处理 NULL 值时，需要使用 IS NULL 或 IS NOT NULL 来检查某列是否为空。

示例：

row

1	SELECT * FROM Employees WHERE salary IS NULL;

这将返回所有 salary 列中值为 NULL 的员工。

row

1	SELECT * FROM Employees WHERE salary IS NOT NULL;

这将返回所有 salary 列中值不为 NULL 的员工。

# 2. 三值逻辑（Three-Valued Logic, 3VL）

在 SQL 中，逻辑运算符（ AND 、 OR 、 NOT 等）遵循三值逻辑。SQL 中的逻辑结果不仅有 TRUE 和 FALSE ，还有第三个可能的值 UNKNOWN ，当表达式中涉及 NULL 时，结果可能是 UNKNOWN 。

# 三值逻辑表

表达式	结果
`TRUE AND TRUE`	`TRUE`
`TRUE AND FALSE`	`FALSE`
`TRUE AND NULL`	`UNKNOWN`
`FALSE AND FALSE`	`FALSE`
`FALSE AND NULL`	`FALSE`
`NULL AND NULL`	`UNKNOWN`
`TRUE OR TRUE`	`TRUE`
`TRUE OR FALSE`	`TRUE`
`TRUE OR NULL`	`TRUE`
`FALSE OR FALSE`	`FALSE`
`FALSE OR NULL`	`UNKNOWN`
`NULL OR NULL`	`UNKNOWN`

# 三值逻辑在 SQL 中的行为：

AND 运算符：只要有一个操作数为 FALSE ，结果就是 FALSE 。如果有一个为 NULL ，而另一个为 TRUE ，结果是 UNKNOWN 。
OR 运算符：只要有一个操作数为 TRUE ，结果就是 TRUE 。如果有一个为 NULL ，另一个为 FALSE ，结果是 UNKNOWN 。
NOT 运算符：当对 NULL 使用 NOT 时，结果仍然是 UNKNOWN 。

# 示例：

AND 运算示例：

row

1 2	SELECT * FROM Employees WHERE salary > 50000 AND commission IS NULL;

在这种情况下，如果 commission 列为 NULL 且 salary > 50000 ，则逻辑判断为 TRUE AND UNKNOWN ，结果为 UNKNOWN ，因此不会返回该行。

OR 运算示例：

row

1 2	SELECT * FROM Employees WHERE salary > 50000 OR commission IS NULL;

如果 salary > 50000 为 TRUE 或 commission 为 NULL ，查询会返回符合条件的行，因为 TRUE OR UNKNOWN 的结果是 TRUE 。

# 3. 聚合函数和 NULL

在 SQL 中，聚合函数处理 NULL 值的方式各不相同：

COUNT(column_name) ：只统计非 NULL 值的行。
COUNT(*) ：统计所有行，包括 NULL 值。
SUM() 、 AVG() ：忽略 NULL 值，仅对非 NULL 值进行计算。
MAX() 、 MIN() ：同样忽略 NULL 值，只考虑非 NULL 的最大值或最小值。

# 示例：

row

1 2	SELECT COUNT(*), COUNT(salary), AVG(salary) FROM Employees;

COUNT(*) ：计算所有行的总数，包括 salary 为 NULL 的行。
COUNT(salary) ：只计算 salary 不为 NULL 的行。
AVG(salary) ：只计算 salary 不为 NULL 的行，并求其平均值。

# 4. WHERE 与 HAVING 的 NULL 处理

在查询中处理 NULL 需要特别注意，尤其是 WHERE 和 HAVING 子句。SQL 中不能使用 = 来比较 NULL，而必须使用 IS NULL 或 IS NOT NULL 。

# 示例：

row

SELECT department, AVG(salary)
FROM Employees
GROUP BY department
HAVING AVG(salary) IS NOT NULL;

这将只返回 AVG(salary) 不为 NULL 的分组。

# 总结

NULL 是表示缺失或未知值的特殊标记，在 SQL 中不能使用普通的等式比较，需要使用 IS NULL 和 IS NOT NULL 。
三值逻辑（TRUE、FALSE、UNKNOWN） 是 SQL 中的基本逻辑系统，主要用于处理涉及 NULL 值的逻辑运算。
聚合函数会自动忽略 NULL 值（如 SUM() 、 AVG() ），但 COUNT(*) 例外，它会包括所有行。

了解 NULL 值和三值逻辑对编写复杂的 SQL 查询至关重要，尤其是在处理可能包含缺失数据的数据库时。

# 嵌套查询（Nested Queries）

# 1. 嵌套查询的定义

# 2. 嵌套查询的类型

# (1) WHERE 子句中的子查询

# (2) FROM 子句中的子查询

# (3) SELECT 子句中的子查询

# 3. 相关子查询（Correlated Subqueries）

# 4. 子查询与运算符

# 5. 嵌套查询的性能

# 6. 嵌套查询的用途

# 总结

# 视图（Views）

# 1. 什么是视图？

# 2. 创建视图

# 3. 使用视图

# 4. 修改视图

# 5. 视图的优势

# 6. 视图的局限性

# 7. 删除视图

# 8. 表和视图的区别

# 聚合与分组（Aggregation and Grouping）

# 1. 聚合函数（Aggregation Functions）

# 示例：

# 2. 分组（GROUP BY）

# 基本语法：

# 示例 1：按部门统计员工数量

# 示例 2：按课程统计学生注册人数

# 3. 结合 GROUP BY 和聚合函数

# 示例 3：按城市统计平均工资

# 4. HAVING 子句

# 示例 4：过滤出注册人数大于 100 的课程

# 5. GROUP BY 与 ORDER BY 一起使用

# 示例 5：按部门统计员工数量并按数量排序

# 总结

# set 运算符

# 1. UNION：并集运算符

# 2. UNION ALL：并集（包含重复值）

# 3. INTERSECT：交集运算符

# 4. EXCEPT（或 MINUS ）：差集运算符

# 注意事项：

# 示例场景：

# 总结：

# NULL Values and Three-valued Logic

# 1. NULL 值

# 如何理解 NULL：

# 查询中的 NULL：

# 2. 三值逻辑（Three-Valued Logic, 3VL）

# 三值逻辑表

# 三值逻辑在 SQL 中的行为：

# 示例：

# 3. 聚合函数和 NULL

# 示例：

# 4. WHERE 与 HAVING 的 NULL 处理

# 示例：

# 总结

lecture_6Network Layer 3

lec_7 Python连接数据库

# (1) `WHERE` 子句中的子查询

# (2) `FROM` 子句中的子查询

# (3) `SELECT` 子句中的子查询

# 3. 结合 `GROUP BY` 和聚合函数

# 4. `HAVING` 子句

# 5. `GROUP BY` 与 `ORDER BY` 一起使用

# 4. EXCEPT（或 `MINUS` ）：差集运算符