logo
BIMU
banner
NEWS LETTER

SQL笔记

Scroll down

1. SQL 基本概念

SQL 是一种用于管理关系数据库的标准语言。它主要分为几个部分：

1.1 SQL 语言分类

数据定义语言 (DDL)：用于定义数据库模式（Schema），负责创建、修改、删除表、索引、视图、函数、存储过程和触发器等对象。

示例：CREATE TABLE，DROP TABLE，ALTER TABLE

数据操纵语言 (DML)：负责数据库中数据的插入、修改、删除等操作。

示例：SELECT，INSERT，UPDATE，DELETE

数据控制语言 (DCL)：用来授予和撤销用户权限。

示例：GRANT，REVOKE

事务控制语言 (TCL)：用于管理事务。

示例：COMMIT，ROLLBACK，SAVEPOINT

2. 数据定义语言 (DDL - Data Definition Language)

DDL 用于指定数据库的结构和约束。

2.1 表的创建 (Create Table)

使用 create table 命令定义一个新的关系（表）。

语法：create table r (A1 D1, A2 D2, ..., (integrity_constraint1), ...)

  • r 是关系名
  • Ai 是属性（列）名
  • Di 是该属性的域（数据类型）

示例：

CREATE TABLE instructor (  
    ID          CHAR(5),  
    name        VARCHAR(20) NOT NULL,  
    dept_name   VARCHAR(20),  
    salary      NUMERIC(8,2),  
    PRIMARY KEY (ID),  
    FOREIGN KEY (dept_name) REFERENCES department  
);

2.2 常用数据类型

SQL 提供了多种标准数据类型：

  • CHAR(n)：固定长度的字符串
  • VARCHAR(n)：可变长度的字符串，最大长度为 n
  • INT：整数
  • SMALLINT：小整数
  • NUMERIC(p,d)：定点数，总共 p 位，小数点后 d 位
  • REAL，DOUBLE PRECISION：浮点数
  • DATE：日期型

2.3 完整性约束

  • NOT NULL：约束属性值不能为空
  • PRIMARY KEY：定义主键，唯一标识一个元组
  • FOREIGN KEY：定义外键，建立表之间的参照完整性
  • REFERENCES：声明引用关系

2.4 表的修改

删除表 (Drop Table)：drop table 命令会从数据库中删除关于表的所有信息。

示例：DROP TABLE myTable;

修改表 (Alter Table)：alter table 用于向现有关系中添加属性。

示例：

ALTER TABLE doc_exa ADD column_b VARCHAR(20) NULL;  -- 添加新字段  
ALTER TABLE doc_exb DROP COLUMN column_b;           -- 删除字段

3. 数据查询 (DQL - SELECT 语句)

SQL 查询的基本结构由 select，from，where 三个子句组成。

3.1 基本查询结构

  • SELECT (选择列表)：对应关系代数中的投影 (Π)。它指定了结果中需要包含哪些属性（列）。
    • SELECT * 表示选择所有属性
  • FROM (关系列表)：对应关系代数中的笛卡尔积 (×)。它列出了查询需要用到的关系（表）。
  • WHERE (谓词)：对应关系代数中的选择 (σ)。它是一个条件，用于过滤 FROM 子句中产生的元组（行）。

3.2 基础运算

去重 (Distinct)：SQL 默认允许结果中存在重复元组。使用 SELECT DISTINCT 关键字可以强制消除重复行。ALL 关键字则指定保留重复行（这是默认行为）。

重命名 (Rename)：使用 AS 关键字（别名）可以重命名关系或属性。

字符串操作：LIKE 谓词用于模式匹配。
- % (百分号)：匹配任意子字符串
- _ (下划线)：匹配任意一个字符

排序 (Ordering)：ORDER BY 子句用于对结果元组进行排序。
- ASC：升序（默认）
- DESC：降序
- 可以按多个属性排序

3.3 集合运算

SQL 提供了对应关系代数中 ∪，∩，− 的运算：

  • UNION：并集
  • INTERSECT：交集
  • EXCEPT：差集

注意：默认情况下，这些运算会自动消除重复行。如果要保留所有重复行，必须使用 UNION ALL，INTERSECT ALL，EXCEPT ALL。

3.4 聚集函数

聚集函数对一组值进行操作并返回单个值。

  • AVG：平均值
  • MIN：最小值
  • MAX：最大值
  • SUM：总和
  • COUNT：数量

示例：

SELECT AVG(salary) AS avg_salary FROM instructor WHERE dept_name = 'Comp. Sci.';  
SELECT COUNT(*) FROM students;

3.5 分组与过滤

GROUP BY 子句：
- 用于将具有相同属性值的元组分组，聚集函数将应用于每个分组
- 重要规则：任何出现在 SELECT 子句中但未被聚集的属性，都必须出现在 GROUP BY 子句中

HAVING 子句：
- 用于对 GROUP BY 形成的分组进行过滤
- 与 WHERE 的区别：WHERE 子句在分组之前过滤元组；HAVING 子句在分组之后过滤分组

示例：

SELECT dept_name, AVG(salary) AS avg_salary  
FROM instructor  
GROUP BY dept_name  
HAVING AVG(salary) > 80000;

4. 嵌套子查询

子查询是嵌套在另一个查询中的 SELECT-FROM-WHERE 表达式。

4.1 WHERE 子句中的子查询

集合成员 (IN)：测试一个值是否存在于子查询返回的结果集中。

集合比较 (SOME，ALL)：
- > SOME：大于子查询结果中的至少一个值
- > ALL：大于子查询结果中的所有值

空关系测试 (EXISTS)：
- EXISTS：测试子查询的结果集是否为非空

4.2 FROM 子句中的子查询

子查询可以出现在 FROM 子句中，此时其结果被当作一个临时的关系（表）。必须为 FROM 子句中的子查询提供一个别名。

示例：

SELECT dept_name, avg_salary  
FROM (SELECT dept_name, AVG(salary) AS avg_salary FROM instructor GROUP BY dept_name) AS dept_avg;

4.3 WITH 子句

WITH 子句提供了一种定义临时关系的方式，该定义仅在当前查询中有效。

示例：

WITH max_budget(value) AS (SELECT MAX(budget) FROM department)  
SELECT dept_name FROM department, max_budget WHERE department.budget = max_budget.value;

5. 数据库修改 (DML)

5.1 删除 (Deletion)

语法：DELETE FROM r WHERE P

WHERE 子句是可选的；如果省略，将删除表中的所有元组。

示例：

DELETE FROM instructor WHERE salary < (SELECT AVG(salary) FROM instructor);

5.2 插入 (Insertion)

语法 1：INSERT INTO r VALUES (v1, v2, ...)

语法 2：INSERT INTO r (SELECT ... FROM ... WHERE ...)
- 这种形式允许将一个查询的结果插入到另一个表中
- 系统会先完全计算 SELECT 查询的结果，然后再执行插入

5.3 更新 (Update)

语法：UPDATE r SET A1 = v1, A2 = v2, ... WHERE P

WHERE 子句是可选的；如果省略，将更新表中的所有元组。

示例：

UPDATE instructor SET salary = salary * 1.05 WHERE salary <= 100000;

CASE 表达式：可用于在 UPDATE 中实现复杂的条件逻辑。

示例：

UPDATE instructor  
SET salary = CASE  
    WHEN salary <= 100000 THEN salary * 1.05  
    ELSE salary * 1.03  
END;

6. NULL 值处理

定义：NULL 值表示值未知或不存在。

运算：任何涉及 NULL 的算术运算（如 5 + NULL）结果都是 NULL。

比较：
- 不能使用 = 或 <> 来比较 NULL
- 必须使用 IS NULL 或 IS NOT NULL 谓词来检查

聚集函数：
- 除 COUNT(*) 外，所有聚集函数（SUM，AVG，COUNT(column)，MIN，MAX）在计算时都会忽略 NULL 值
- 如果集合中只有 NULL 值，COUNT 返回 0，其他聚集函数返回 NULL

处理 NULL 值的函数：
- COALESCE(column, 0)：将 NULL 转换为 0
- NVL(column, 0)：在某些数据库系统中将 NULL 转换为 0

示例：

-- AVG 函数默认忽略 NULL 值  
SELECT AVG(commission) FROM employees; -- 只计算非 NULL 的佣金  

-- 包含 NULL 值在计算中  
SELECT AVG(COALESCE(commission, 0)) FROM employees; -- 将 NULL 视为 0 计算

7. 高级特性

7.1 视图

视图是一个虚拟表，它是由一个或几个基本表导出的表，不直接存在于物理存储器上。

创建视图：

CREATE VIEW faculty AS  
SELECT ID, name, dept_name FROM instructor;

7.2 索引

索引用于加速数据检索。

创建索引：

CREATE INDEX idx_name ON students (name);

7.3 事务控制

提交事务：COMMIT
回滚事务：ROLLBACK
设置保存点：SAVEPOINT

总结

SQL 是关系数据库管理的核心语言，具有以下主要特点：

  1. 类似于英语的自然语言，简洁易学
  2. 非过程语言 - 用户只需提出”干什么”，不需要关心”怎么干”
  3. 面向集合的语言 - 操作的对象和结果都是关系
  4. 既可独立使用，又可嵌入到宿主语言中使用
  5. 具有查询、操作、定义和控制四种语言一体化的特点

掌握 SQL 需要理解其基本结构和各种子句的用法，特别是 SELECT 语句的灵活应用，以及如何处理 NULL 值和复杂查询。

具体示例

我将详细讲解每个SQL语句的执行顺序，包括中间步骤和结果。

1. 去重 (DISTINCT) 示例

SELECT DISTINCT major FROM Students;

执行顺序详解：

步骤1：FROM 子句

-- 首先从Students表中读取所有数据  
FROM Students

中间结果：

sid | name | major       | age  
----|------|-------------|----  
1   | 张三 | 计算机科学  | 20  
2   | 李四 | 数学        | 21  
3   | 王五 | 计算机科学  | 22  
4   | 赵六 | 物理        | 20  
5   | 钱七 | 计算机科学  | 21

步骤2：SELECT 子句（选择列）

-- 选择major列  
SELECT major

中间结果：

major  
------  
计算机科学  
数学  
计算机科学  
物理  
计算机科学

步骤3：DISTINCT 操作

-- 去除重复行  
DISTINCT

最终结果：

major  
------  
计算机科学  
数学  
物理

2. 排序 (ORDER BY) 示例

SELECT * FROM Students ORDER BY age DESC, name ASC;

执行顺序：

步骤1：FROM 子句

FROM Students

中间结果：

sid | name | major       | age  
----|------|-------------|----  
1   | 张三 | 计算机科学  | 20  
2   | 李四 | 数学        | 21  
3   | 王五 | 计算机科学  | 22  
4   | 赵六 | 物理        | 20  
5   | 钱七 | 计算机科学  | 21

步骤2：SELECT 子句

SELECT *

中间结果：（与FROM结果相同）

步骤3：ORDER BY 子句

ORDER BY age DESC, name ASC

排序过程：
1. 先按age降序排列：
年龄：22, 21, 21, 20, 20
2. 相同年龄的再按name升序排列：
- 年龄21：李四、钱七
- 年龄20：张三、赵六

最终结果：

sid | name | major       | age  
----|------|-------------|----  
3   | 王五 | 计算机科学  | 22  
2   | 李四 | 数学        | 21  
5   | 钱七 | 计算机科学  | 21  
1   | 张三 | 计算机科学  | 20  
4   | 赵六 | 物理        | 20

3. 分组和聚集函数示例

SELECT s.major, AVG(g.grade) as avg_grade  
FROM Students s  
JOIN Grades g ON s.sid = g.sid  
GROUP BY s.major  
HAVING AVG(g.grade) > 85;

执行顺序详解：

步骤1：FROM 和 JOIN 子句

FROM Students s JOIN Grades g ON s.sid = g.sid

中间结果（连接后的表）：

s.sid | s.name | s.major | s.age | g.sid | g.course | g.grade  
------|--------|---------|-------|-------|----------|--------  
1     | 张三   | 计算机科学 | 20   | 1     | 数据库   | 90  
1     | 张三   | 计算机科学 | 20   | 1     | 算法     | 85  
2     | 李四   | 数学     | 21   | 2     | 数据库   | 88  
2     | 李四   | 数学     | 21   | 2     | 算法     | 92  
3     | 王五   | 计算机科学 | 22   | 3     | 数据库   | 78  
3     | 王五   | 计算机科学 | 22   | 3     | 算法     | 80  
4     | 赵六   | 物理     | 20   | 4     | 数据库   | 95  
4     | 赵六   | 物理     | 20   | 4     | 算法     | 90  
5     | 钱七   | 计算机科学 | 21   | 5     | 数据库   | 82  
5     | 钱七   | 计算机科学 | 21   | 5     | 算法     | 85

步骤2：GROUP BY 子句

GROUP BY s.major

分组结果：

组1：计算机科学专业

s.sid | s.name | s.major | s.age | g.sid | g.course | g.grade  
------|--------|---------|-------|-------|----------|--------  
1     | 张三   | 计算机科学 | 20   | 1     | 数据库   | 90  
1     | 张三   | 计算机科学 | 20   | 1     | 算法     | 85  
3     | 王五   | 计算机科学 | 22   | 3     | 数据库   | 78  
3     | 王五   | 计算机科学 | 22   | 3     | 算法     | 80  
5     | 钱七   | 计算机科学 | 21   | 5     | 数据库   | 82  
5     | 钱七   | 计算机科学 | 21   | 5     | 算法     | 85

组2：数学专业

s.sid | s.name | s.major | s.age | g.sid | g.course | g.grade  
------|--------|---------|-------|-------|----------|--------  
2     | 李四   | 数学     | 21   | 2     | 数据库   | 88  
2     | 李四   | 数学     | 21   | 2     | 算法     | 92

组3：物理专业

s.sid | s.name | s.major | s.age | g.sid | g.course | g.grade  
------|--------|---------|-------|-------|----------|--------  
4     | 赵六   | 物理     | 20   | 4     | 数据库   | 95  
4     | 赵六   | 物理     | 20   | 4     | 算法     | 90

步骤3：HAVING 子句

HAVING AVG(g.grade) > 85

计算每个组的平均成绩并过滤：
- 计算机科学专业：(90+85+78+80+82+85)/6 = 500/6 ≈ 83.33 ❌（不满足条件）
- 数学专业：(88+92)/2 = 180/2 = 90.0 ✅（满足条件）
- 物理专业：(95+90)/2 = 185/2 = 92.5 ✅（满足条件）

步骤4：SELECT 子句

SELECT s.major, AVG(g.grade) as avg_grade

最终结果：

major    | avg_grade  
---------|----------  
数学     | 90.0  
物理     | 92.5

4. 集合运算 (UNION) 示例

SELECT * FROM Students WHERE age > 21  
UNION  
SELECT * FROM Students WHERE major = '数学';

执行顺序详解：

步骤1：执行第一个查询

SELECT * FROM Students WHERE age > 21

WHERE过滤结果：

sid | name | major       | age  
----|------|-------------|----  
3   | 王五 | 计算机科学  | 22  -- 年龄大于21

步骤2：执行第二个查询

SELECT * FROM Students WHERE major = '数学'

WHERE过滤结果：

sid | name | major | age  
----|------|-------|----  
2   | 李四 | 数学  | 21

步骤3：UNION 操作

UNION  -- 合并两个结果集并去重

合并过程：

第一个查询结果：王五(22岁，计算机科学)  
第二个查询结果：李四(21岁，数学)  
合并后：两个不同的记录

最终结果：

sid | name | major       | age  
----|------|-------------|----  
2   | 李四 | 数学        | 21  
3   | 王五 | 计算机科学  | 22

5. 完整SQL执行顺序总结

标准SQL执行顺序：

  1. FROM 和 JOIN - 确定数据来源和连接
  2. WHERE - 行级过滤
  3. GROUP BY - 分组
  4. HAVING - 组级过滤
  5. SELECT - 选择列
  6. DISTINCT - 去重
  7. ORDER BY - 排序
  8. LIMIT/OFFSET - 限制结果集

记忆口诀： “From Where Group Having Select Distinct Order Limit”

重要说明：
- 这个顺序解释了为什么不能在WHERE子句中使用SELECT中定义的别名
- HAVING可以引用SELECT中的聚集函数，因为HAVING在SELECT之后执行
- ORDER BY可以引用SELECT中的别名，因为它在SELECT之后执行

这样的详细分解应该能帮助你更好地理解SQL语句的内部执行机制！

如果您喜欢我的文章，可以考虑打赏以支持我继续创作.

其他文章
cover
计算机
第一章：引言
  • 25/10/22
  • 16:16
cover
Meachine Learning
RL?加权的SFT罢了
  • 25/10/15
  • 09:22
© 2025 - 2026 BIMU
由 Hexo 驱动 v7.3.0 主题 - Async v2.2.6
目录导航 置顶
  1. 1. 1. SQL 基本概念
    1. 1.1. 1.1 SQL 语言分类
  2. 2. 2. 数据定义语言 (DDL - Data Definition Language)
    1. 2.1. 2.1 表的创建 (Create Table)
    2. 2.2. 2.2 常用数据类型
    3. 2.3. 2.3 完整性约束
    4. 2.4. 2.4 表的修改
  3. 3. 3. 数据查询 (DQL - SELECT 语句)
    1. 3.1. 3.1 基本查询结构
    2. 3.2. 3.2 基础运算
    3. 3.3. 3.3 集合运算
    4. 3.4. 3.4 聚集函数
    5. 3.5. 3.5 分组与过滤
  4. 4. 4. 嵌套子查询
    1. 4.1. 4.1 WHERE 子句中的子查询
    2. 4.2. 4.2 FROM 子句中的子查询
    3. 4.3. 4.3 WITH 子句
  5. 5. 5. 数据库修改 (DML)
    1. 5.1. 5.1 删除 (Deletion)
    2. 5.2. 5.2 插入 (Insertion)
    3. 5.3. 5.3 更新 (Update)
  6. 6. 6. NULL 值处理
  7. 7. 7. 高级特性
    1. 7.1. 7.1 视图
    2. 7.2. 7.2 索引
    3. 7.3. 7.3 事务控制
  8. 8. 总结
  • 具体示例
    1. 1. 1. 去重 (DISTINCT) 示例
    2. 2. 2. 排序 (ORDER BY) 示例
    3. 3. 3. 分组和聚集函数示例
    4. 4. 4. 集合运算 (UNION) 示例
    5. 5. 5. 完整SQL执行顺序总结
    • 请输入关键词进行搜索
    • to close