SQL

本笔记旨在全面梳理SQL的核心概念，从数据定义、基础查询到复杂的多表连接、子查询及高级特性。所有概念均结合了课程讲义中的理论和项目实践中的具体应用。

一、 SQL与关系模型概述

1. SQL的历史与地位

起源: SQL (Structured Query Language) 于20世纪70年代在IBM的System R项目中诞生。
持久性: 尽管经历了面向对象、XML和NoSQL等技术的挑战，SQL凭借其强大的功能和标准化，至今仍是数据查询领域的核心语言。
核心优势: SQL是一种声明式语言，用户只需说明“想要什么”，而无需关心“如何获取”，具体的执行策略由数据库管理系统（DBMS）负责优化。

2. 关系模型术语

数据库 (Database): 一系列命名表（Relation）的集合。
表 (Table/Relation): 由两部分组成：
- 模式 (Schema): 对表的结构描述，即元数据，定义了列名和数据类型。例如people表储存了球员信息。
- 实例 (Instance): 在某一时刻，表中存储的具体数据，是行的集合。
列 (Column/Attribute/Field): 表的垂直部分，具有唯一的名称和原子数据类型（如INTEGER, TEXT, FLOAT）。
行 (Row/Tuple/Record): 表的水平部分，代表一条具体的记录。
多重集 (Multiset): 关系数据库中的表是行的“多重集”，意味着允许存在完全相同的行（重复元组）。

3. SQL语言的组成

DDL (Data Definition Language): 数据定义语言，用于创建和修改数据库的模式。
- CREATE TABLE: 定义新表的结构。
DML (Data Manipulation Language): 数据操作语言，用于查询和修改数据。
- **SELECT, INSERT, UPDATE, DELETE**。

二、 DDL: 定义数据结构

使用CREATE TABLE来定义表的模式，包括列名、数据类型和约束。在proj1.sql的题目描述中，就给出了dogs和users表的创建语句。

完整示例:

CREATE TABLE users (
    userid INTEGER,
    name VARCHAR,
    age INTEGER,
    PRIMARY KEY (userid)
);

CREATE TABLE dogs (
    dogid INTEGER,
    ownerid INTEGER,
    name VARCHAR,
    breed VARCHAR,
    age INTEGER,
    PRIMARY KEY (dogid),
    FOREIGN KEY (ownerid) REFERENCES users (userid)
);

核心概念:

PRIMARY KEY (主键): 用于唯一标识表中的每一行。主键列的值不能重复，也不能为NULL。可以由单个列或多个列（复合主键）组成。
FOREIGN KEY (外键): 在一个表中建立与另一个表主键的引用链接。用于强制表间的引用完整性，确保关联数据的一致性。

三、 DML: 基础查询与单表操作

1. 基础查询结构: `SELECT` 和 `FROM`

这是所有查询的基石，用于指定从哪个表中选取哪些列的数据。

语法:

1 2	`SELECT <columns> FROM <table>;`

示例: 从people表中选取球员的ID、名和姓。

1 2	`-- "proj1.sql" 中的一个基础操作 SELECT playerid, namefirst, namelast FROM people;`

2. 过滤数据: `WHERE` 子句

WHERE子句用于根据指定的条件（谓词）筛选出我们感兴趣的行。

语法:

1
2
3

SELECT <columns>
FROM <table>
WHERE <predicate>;

示例: 在proj1.sql的q1i中，查询体重超过300磅的球员。

-- Query from q1i
SELECT namefirst, namelast, birthyear
FROM people p
WHERE p.weight > 300;

WHERE子句中还可以使用布尔运算符 AND, OR, NOT 来组合多个条件。建议使用括号()明确指定运算优先级。

3. 结果排序: `ORDER BY`

ORDER BY 用于对输出结果进行排序。默认为升序 (ASC)，可指定降序 (DESC)。可以按多个列排序。

示例: 在proj1.sql的q2i中，将名人堂成员按入选年份降序排列，年份相同时按球员ID升序排列。

-- Query from q2i
SELECT namefirst, namelast, p.playerid, yearid
...
ORDER BY yearid DESC, p.playerid;

4. 限制输出行数: `LIMIT`

LIMIT 子句用于限制查询返回的行数，常与 ORDER BY 结合使用以获取“Top N”结果。

示例: 在proj1.sql的q3i中，获取单赛季打击率（slg）排名前10的记录。

-- Query from q3i
SELECT P.playerid, namefirst, namelast, yearid, slg
...
ORDER BY slg DESC, yearid, P.playerid
LIMIT 10;

四、分组与聚合

1. 聚合函数

SQL提供了一系列内置的聚合函数，用于对一组值进行计算并返回单个值。

COUNT(): COUNT(*)计算所有行数，COUNT(<column>)计算该列非NULL值的数量。
SUM(), AVG(), MAX(), **MIN()**。
除COUNT(*)外，所有聚合函数都会忽略NULL值。

示例: 在proj1.sql的q0中，查找历史最高自责分率（era）。

1
2
3

-- Query from q0
SELECT MAX(era)
FROM pitching;

2. `GROUP BY` 与 `HAVING`

GROUP BY: 将数据按指定列的值进行分组，以便对每个组独立进行聚合计算。
HAVING: 在数据分组后，对这些组进行过滤。WHERE过滤行，HAVING过滤组（聚合之后其实也是行😀）

重要规则 (The Aggregation Rule) :

如果查询中使用了GROUP BY，那么SELECT子句中只能包含分组列和聚合函数。

示例: 在proj1.sql的q1iv中，找出平均身高大于70的出生年份组。

-- Query from q1iv
SELECT birthyear, AVG(height), COUNT(*)
FROM people
GROUP BY birthyear
HAVING AVG(height) > 70
ORDER BY birthyear;

五、多表查询: Joins

当所需信息分布在多个表中时，需要使用 JOIN 将它们连接起来。

1. 连接的类型与语法

连接类型	描述
内连接 (INNER JOIN)	(默认连接类型) 只返回两个表中连接键能匹配上的行。
左外连接 (LEFT OUTER JOIN)	保留左表的所有行。若右表无匹配，则右表列填充为`NULL`。
右外连接 (RIGHT OUTER JOIN)	保留右表的所有行。若左表无匹配，则左表列填充为`NULL`。
全外连接 (FULL OUTER JOIN)	保留两个表的所有行，在无匹配处用`NULL`填充。

示例 (INNER JOIN): 在proj1.sql的q2i中，连接people和halloffame表找出名人堂成员。

值得一提的是，其实FROM多个表默认的就是 INNER JOIN，INNER / OUTER 可省略

-- Query from q2i (implicit syntax)
SELECT namefirst, namelast, p.playerid, yearid
FROM people p, halloffame ha
WHERE p.playerid = ha.playerid AND ha.inducted LIKE 'Y';

示例 (LEFT OUTER JOIN): 在proj1.sql的q2iii中，查询所有名人堂成员及其学校，即使没有上过大学也要包括。

-- Query from q2iii
SELECT q2i.playerid, namefirst, namelast, schoolid
FROM q2i
LEFT OUTER JOIN collegeplaying C ON C.playerid = q2i.playerid
ORDER BY q2i.playerid DESC, schoolid;

2. 自连接 (Self-Join)

将一个表与其自身进行连接，通常需要使用表别名来区分。

示例: 在proj1.sql的q4iii中，通过自连接q4i视图计算每年薪资相对于上一年的变化。

-- Query from q4iii
SELECT Q2.yearid, Q2.min - Q1.min, Q2.max - Q1.max, Q2.avg - Q1.avg
FROM q4i Q1, q4i Q2
WHERE Q1.yearid + 1 = Q2.yearid
ORDER BY Q2.yearid;

六、子查询、集合运算与CTE

1. 子查询 (Subquery)

子查询是嵌套在另一个SQL查询中的查询。

在 WHERE / HAVING 中: 常用于根据一个聚合结果进行过滤。
在 FROM 中: 子查询的结果可以作为一个临时的、可供外部查询的表。
相关子查询 (Correlated Subquery): 子查询的执行依赖于主查询当前处理的行。

示例: 在proj1.sql的q3iii中，查找终身打击率比特定球员'mayswi01'更高的球员。

1 2	`-- Snippet from q3iii ... WHERE L.lslg > (SELECT lslg FROM goat)`

这里的 (SELECT lslg FROM goat) 就是一个子查询。

2. 集合运算 (Set Operations)

用于合并两个或多个SELECT语句的结果集。

UNION: 合并结果集并移除重复行。
UNION ALL: 合并结果集并保留所有行。
INTERSECT: 返回两个结果集的交集。
EXCEPT: 返回在第一个结果集中存在，但在第二个结果集中不存在的行。

3. 通用表表达式 (Common Table Expression - CTE)

使用 WITH 关键字定义的临时命名结果集。CTE可以极大地提高复杂查询的可读性和模块化程度，在proj1.sql中被广泛使用。

示例: 在proj1.sql的q3i中，先用CTE (slg_cal) 计算打击率，再从这个临时结果中查询。

-- Query from q3i
WITH slg_cal AS (
  SELECT playerid, yearid, ((H - H2B - H3B - HR) + 2*H2B + 3*H3B + 4*HR) / CAST(AB AS FLOAT) AS slg
  FROM batting
  WHERE AB > 50
)
SELECT P.playerid, namefirst, namelast, yearid, slg
FROM people P, slg_cal S
WHERE P.playerid = S.playerid
ORDER BY slg DESC, yearid, P.playerid
LIMIT 10;

七、其他重要概念

1. `NULL`值与三值逻辑

NULL的含义: 代表“未知”、“缺失”或“不适用”的值。
三值逻辑 (Three-Valued Logic): WHERE子句只保留计算结果为TRUE的行，FALSE和NULL的行都会被丢弃。
检查NULL: 必须使用 IS NULL 或 IS NOT NULL。

2. 类型转换: `CAST`

这是一个类型转换的关键字 or 关键函数？，用法也很简单，如下示例：

CAST(expression AS target_type)

-- 将字符串转换为整数
SELECT CAST('123' AS INTEGER);

-- 将浮点数转换为整数（会截断小数部分）
SELECT CAST(3.14 AS INT);

-- 将整数转换为浮点数（避免整数除法问题）
SELECT CAST(5 AS FLOAT) / 2; -- 结果是2.5

SELECT 5 / 2;                -- 结果是2（整数除法）

3. 模式匹配: `LIKE` and `~`

LIKE 操作符用于在WHERE子句中搜索列中的指定模式。

%: 匹配任意数量的字符。
_: 匹配单个字符。

示例: 在proj1.sql的q1ii中，查找namefirst字段中包含空格的球员。

1 2	`-- Query from q1ii ... WHERE namefirst LIKE '% %' ...`

SQLite

在SQLite中，并没有~这一种匹配模式，而它是使用[[UNIX_Review_2#^1 | 正则表达式]] 匹配的，示例如下：

1
2
3

-- 匹配以数字结尾的球员ID

2SELECT * FROM players WHERE player_id ~ '[0-9]$';

4. 视图: `CREATE VIEW`

视图是一个虚拟表，其内容由查询定义。proj1.sql中的所有问题都是通过创建视图来解答的，这允许将复杂的查询保存为命名的、可重用的对象。

示例: proj1.sql的q0

CREATE VIEW q0(era)
AS
  SELECT MAX(era)
  FROM pitching;

八、 SQL查询逻辑执行顺序

理解逻辑执行顺序有助于编写和调试复杂查询。

FROM: 处理FROM子句及所有JOIN操作。
WHERE: 过滤行。
GROUP BY: 将过滤后的行分组。
HAVING: 过滤分组。
SELECT: 计算SELECT列表中的表达式。
DISTINCT: 移除结果集中的重复行。
ORDER BY: 对最终结果集进行排序。
LIMIT: 从排序后的结果集中选取指定数量的行。

CS-186

#SQL #MySQL #数据库

SQL

http://pzhwuhu.github.io/2025/07/20/SQL/

本文作者

pzhwuhu

发布于

2025年7月20日

更新于

2025年7月30日

许可协议

TUH数据集下载上一篇

大二下总结下一篇

SQL

一、 SQL与关系模型概述

1. SQL的历史与地位

2. 关系模型术语

3. SQL语言的组成

二、 DDL: 定义数据结构

三、 DML: 基础查询与单表操作

1. 基础查询结构: SELECT 和 FROM

2. 过滤数据: WHERE 子句

3. 结果排序: ORDER BY

4. 限制输出行数: LIMIT

四、 分组与聚合