数据库¶

数据是一切分析的起点，而SQL就是从数据库中提取这些数据的工具。

关系数据库¶

数据库（DB）是长期储存在计算机中，有组织、可共享的大量数据的集合。

数据库管理系统（DBMS）是管理数据库的软件，位于用户和操作系统之间，旨在科学高效地维护、获取、组织数据。

DBMS¶

DBMS主要有以下功能：

1. 数据定义：
   • 提供数据定义语言（DDL）
   • 定义数据库中的数据对象
2. 数据操纵：
   • 提供数据操纵语言（DML）
   • 实现对数据库的增删改查
3. 数据库的建立和维护
4. 其他功能
   • 和其他软件系统的通信
   • 数据转换

主流的DBMS有：

• MySQL（开源）
• PostgreSQL（开源）
• SQL Server（微软）
• Oracle（甲骨文）
• DB2（IBM）
• SQLite（轻量化）

当然以上都是SQL数据库，也就是我们主要介绍的关系数据库。也存在一些NoSQL数据库，使用非关系型的数据存储：

• Redis（键值数据库）
• DynamoDB（键值数据库）
• MongoDB（文档数据库）
• Couchbase（文档数据库）
• Apache Cassandra（列族数据库）
• Neo4J（图数据库）
• Milvus（向量数据库）

当然，NoSQL = Not Only SQL，这些数据库也都支持一定程度的SQL查询。

关系模型¶

数据模型描述了数据库的组成对象以及对象之间的联系。常见的数据模型有：

• 层次模型 (Hierarchical Model)
• 网状模型 (Network Model)
• 关系模型 (Relational Model)
• 面向对象模型 (Object-Oriented Model)
• 文档模型 (Document Model)
• 图模型 (Graph Model)
• 列族模型 (Column-Family Model)
• 键值模型 (Key-Value Model)

实际上前面介绍的SQL数据库和NoSQL数据库都是基于这些数据模型设计的。

我们主要关注关系模型（1970年IBM公司提出），它将数据存储在表格中，每个表格由行和列组成。每一行表示一个记录，每一列表示记录的属性。通过键（如主键、外键）来建立表之间的关系。

• 应用：广泛应用于数据库管理系统（如 MySQL、PostgreSQL、Oracle等），特别适用于结构化数据。
• 优点：简单易懂，支持复杂的查询和数据操作。
• 缺点：对于非常复杂的数据结构或大规模数据，性能可能不如其他模型。

关系模型的数据结构包含以下内容：

• 关系：关系对应着一张二维表
• 关系模式：对关系的描述，表头
• 元组：表中的一行
• 属性：表中的一列
• 主码：唯一确定一行
• 域：属性的取值范围
• 分量：元组中的一个属性值

关系可以分为三类：

• 基本表：实际存在的表，是实际存储文件（内模式）的逻辑表示（模式）。
• 视图表：由基本表导出的逻辑表（外模式），不实际存在。
• 查询表：从视图和基本表派生的查询结果。

关系完整性¶

关系需要满足三类完整性约束：

1. 实体完整性：
   • 实体完整性约束确保关系中的每个元组（行）都有唯一的标识符，即主键（Primary Key）。每个表的主键必须是唯一的，且不能为空（NULL）。
2. 参照完整性：
   • 参照完整性约束确保表与表之间的外键关系是有效的，即外键值必须指向另一个表中的有效记录。外键是一种指向其他表主键的字段，用来建立表与表之间的关联。
3. 用户定义的完整性（域完整性）：
   • 域完整性约束保证每个字段的值都属于某个预定义的合法值范围（即域）。这些值必须符合数据类型、长度、格式等约定。

不同关系之间可能存在对应关系：

1. 一对一关系
   • 一对一关系可以通过引入外键来实现，只需要把其中一个表的主键引入为外键即可。
2. 一对多关系
   • 一对多的关系同样可以通过引入外键来实现，只需要在“多”的那个表中引入“一”的主键作为外键即可。
   • 例如，每个学生都属于唯一一个班级，但是一个班级有多个学生。只需要在学生的表中加入班级号作为外键即可。
3. 多对多关系
   • 多对多关系需要引入中间表才能实现。通过两个一对多关系就可以实现多对多啦。
   • 例如，每个老师都可能教学多个班级，每个班级也可能有多个老师。这时候只需要一个老师-班级中间表（类似笛卡尔积）记录对应关系即可实现多对多。

关系代数¶

关系的操作（其实就是对表的操作）分为两类：

• 数据查询
• 数据更新：插入、删除、修改

插入、删除、修改比较简单。

而数据查询较为复杂，可以用关系代数来描述。关系代数是一种抽象的查询语言，它用关系的运算来表达查询。它包含下列运算符：

具体来说：

1. 选择 (Selection, σ)

   • 作用：从关系中选择满足某些条件的元组（行）。
   • 语法：σ_condition(R)
   • 示例：σ(年龄 > 20)(员工) 表示选择所有年龄大于20的员工。

2. 投影 (Projection, π)

   • 作用：从关系中选择特定的属性（列），即去除不需要的列。
   • 语法：π_attribute1, attribute2, ... (R)
   • 示例：π(姓名, 年龄)(员工) 表示只选择员工表中的姓名和年龄列。

3. 连接 (Join, ⨝)

   • 作用：连接两个关系，基于一个共同的属性。常见的连接包括内连接、外连接等。
   • 语法：R1 ⨝_condition R2
   • 示例：员工 ⨝ 部门 表示通过匹配员工表和部门表中的共同属性（如部门ID）来连接两个表。

4. 除法 (Division, ÷)

   • 作用：返回在一个关系中存在，且在另一个关系中全部存在的元组。
   • 语法：R1 ÷ R2
   • 示例：项目 ÷ 员工 表示列出参与了所有项目的员工。

5. 重命名 (Renaming, ρ)

   • 作用：为关系或属性命名，通常用于简化查询。
   • 语法：ρ(new_name, R)
   • 示例：ρ(新员工, 员工) 表示将员工表重命名为“新员工”。

6. 并 (Union, ∪)

   • 作用：将两个关系的所有元组合并，去掉重复的元组。两个关系必须有相同的属性。
   • 语法：R1 ∪ R2
   • 示例：员工 ∪ 合作伙伴 表示合并员工表和合作伙伴表中的所有记录。

7. 交 (Intersection, ∩)

   • 作用：只留下重复的元组。两个关系必须有相同的属性。
   • 语法：R1 ∩ R2
   • 示例：员工 ∩ 合作伙伴 表示即在员工中又在合作伙伴中的元组。

8. 差 (Difference, −)

   • 作用：返回一个关系中有但另一个关系中没有的元组。两个关系必须有相同的属性。
   • 语法：R1 − R2
   • 示例：员工 − 合作伙伴 表示列出在员工表中但不在合作伙伴表中的记录。

9. 笛卡尔积 (Cartesian Product, ×)

   • 作用：将两个关系的所有元组进行组合，生成一个新的关系。返回的关系的元组数为两个关系元组数的乘积。
   • 语法：R1 × R2
   • 示例：员工 × 部门 表示生成员工和部门表的笛卡尔积。

SQL¶

结构化查询语言（SQL）是关系数据库的标准语言。它有以下特点：

1. 综合统一：它集数据定义语言（DDL）、数据操纵语言（DML）、数据查询语言（DQL）的功能于一体。可以独立完成数据库生命周期中的全部活动：
   • 表的定义、修改和删除
   • 增删改查
   • 数据库重构和维护
   • 数据库安全性、完整性控制、事务控制
2. 标准化：SQL 是一个广泛接受的标准，并且有不同的数据库系统实现，但基本语法和功能是相似的。
3. 高度过程化：SQL 让用户声明他们希望从数据库中得到什么结果，而不是如何得到这个结果。存取路径的选择和SQL操作过程由DBMS自动完成。
4. 基于集合：SQL 的操作是基于集合理论（关系代数），能有效处理表与表之间的关系。

SQL有以下常见关键字：

数据定义（DDL）¶

• 创建基本表：

CREATE TABLE statistics (
    id BIGINT NOT NULL AUTO_INCREMENT,
    class_id BIGINT NOT NULL UNIQUE,
    average DOUBLE NOT NULL,
    PRIMARY KEY (id) -- 设置主键
);

在定义基本表的时候可以引入一些完整性约束。

• 修改基本表：

ALTER TABLE students ADD INDEX idx_score (score); -- 增加index
ALTER TABLE students DROP COLUMN birthday;        -- 删除列

• 删除基本表：

DROP TABLE students;

索引¶

建立索引可以加快查询的速度。关系数据库中常见的索引有：

• 顺序文件的索引
• B+树索引：动态平衡
• 散列索引（Hash）：索引速度快
• 位图索引

CREATE INDEX index_name ON table_name (column1, column2, ...);

CREATE INDEX index_name ON table_name (column_name) USING HASH;

索引由数据库的管理员创建，由DBMS自动维护，在查询的时候DBMS自动选取合适的索引来查询。用户无法显式选择索引。

• 创建索引：

CREATE INDEX index_name ON table_name (column1, column2, ...);

• 更改索引：

ALTER INDEX似乎并不存在

-- MySQL 5.7 引入
ALTER TABLE RENAME INDEX old_index_name TO new_index_name

• 删除索引：

DROP INDEX index_name;

增删改查¶

• 增加数据：

INSERT 
INTO students (class_id, name, gender, score) 
VALUES (2, '大牛', 'M', 80);

• 删除数据：

DELETE FROM students WHERE id=1;

• 更新数据：

UPDATE students SET name='大牛', score=66 WHERE id=1;

• 查询数据：

SELECT * FROM students WHERE score >= 80;

空值处理¶

在域定义中，如果有NOT NULL约束，那么这个属性就不会为空。并且加了UNIQUE约束的属性也不能取空。此外，码属性显然不能取空。

可以使用IS NULL和IS NOT NULL来判断一个值是否为空。

空值和其他值的算术运算结果依然是NULL，比较运算的结果为UNKNOWN。

这样一来，SQL的逻辑结构就不再是二值（TRUE-FALSE）的，而是三值逻辑：

• TRUE
• FALSE
• UNKNOWN

他们的运算规则如下：

视图操作¶

视图是从基本表派生的逻辑表（外模式）。使用视图可以：

1. 视图能简化用户的查询操作
2. 适当利用视图可以更清晰地表达查询
3. 提供多种视角看待同一数据
4. 对重构数据库提供了一定程度的逻辑独立性

5. 能一定程度保护机密数据，对不同用户可以定义不同视图

6. 创建视图

CREATE VIEW 视图名 AS SELECT 查询语句;

• 删除视图

DROP VIEW 视图名;

• 查询视图

SELECT * FROM 视图名;

• 更新视图

-- 修改视图
UPDATE 视图名 SET 列名 = 新值 WHERE 条件;

-- 删除数据
DELETE FROM 视图名 WHERE 条件;

-- 插入数据
INSERT INTO 视图名 (列名1, 列名2, ...) VALUES (值1, 值2, ...);

和INDEX类似，VIEW也不存在ALTER操作。

INSERT INTO IS_Student VALUES ('20250125', '阿三', 20);

INSERT INTO IS_Student (Sno, Sname, Sage, Sdept) VALUES ('20250125', '阿三', 20, 'IS');

INSERT INTO IS_Student (Sno, Sname, Sage, Sdept) VALUES ('20250125', '阿三', 20, NULL);

花式查询¶

SELECT * FROM students, classes WHERE students.class_id == classes.id

id class_id name gender score id name
1 1 小明 M 90 1 一班
2 1 小红 F 95 1 一班
3 1 小军 M 88 1 一班
4 1 小米 F 73 1 一班
5 2 小白 F 81 2 二班
6 2 小兵 M 55 2 二班
7 2 小林 M 85 2 二班
8 3 小新 F 91 3 三班
9 3 小王 M 89 3 三班
10 3 小丽 F 88 3 三班

WITH语句¶

生成Common table expression (CTE)，用于后续查询

WITH [RECURSIVE]
    cte_name [(col_name [, col_name] ...)] AS (subquery)
    [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

例如：

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

SELECT语句¶

SELECT是使用频率最高的语句，它的结构如下：

SELECT
    -- 查询选项
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    -- 可以多个表达式
    select_expr [, select_expr] ... 
    [into_option]
    -- FROM子句
    [FROM table_references
      [PARTITION partition_list]]
    -- WHERE子句
    [WHERE where_condition]
    -- GROUP子句
    [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
    -- HAVING字句
    [HAVING where_condition]
    -- 窗口函数
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    -- ORDER字句
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    -- 输出限制
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [into_option]
    -- 锁
    [FOR {UPDATE | SHARE}
        [OF tbl_name [, tbl_name] ...]
        [NOWAIT | SKIP LOCKED]
      | LOCK IN SHARE MODE]
    [into_option]

其中

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}

export_options:
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]

查询选项¶

• ALL / DISTINCT / DISTINCTROW：控制是否去重。
• HIGH_PRIORITY：提高查询的优先级。
• STRAIGHT_JOIN：强制按照指定顺序连接表。
• SQL_SMALL_RESULT / SQL_BIG_RESULT：指定查询结果集的大小，用于优化GROUP等操作。
• SQL_BUFFER_RESULT：将结果存储在内存中的临时表中。
• SQL_NO_CACHE：禁止查询缓存。
• SQL_CALC_FOUND_ROWS：计算总行数但不受 LIMIT 影响。

查询表达式¶

*的用法¶

• 可以单独使用
• 也可以放在表名后面：table.*

-- 单表
SELECT * FROM t1
SELECT id, t1.* FROM t1
SELECT AVG(score), t1.* FROM t1
-- 多表
SELECT t1.*, t2.* FROM t1 INNER JOIN t2

别名¶

在查询表达式中可以使用各种函数，计算结果可以使用别名（alias）：

SELECT CONCAT(last_name,', ',first_name) AS full_name
  FROM mytable ORDER BY full_name;
-- 等同于
SELECT CONCAT(last_name,', ',first_name) full_name
  FROM mytable ORDER BY full_name;

常用的函数¶

CASE语句¶

在查询表达式中，可以嵌套CASE语句来达到if-else的输出效果：

SELECT
    id,
    class_id,
    name,
    (CASE WHEN score < 60 THEN '不及格'
        WHEN score >= 60 AND score < 80 THEN '及格'
        WHEN score >= 80 THEN '优秀'
        ELSE '异常' END) AS REMARK
FROM
    students
ORDER BY REMARK

output:

id class_id name REMARK
6 2 小兵 不及格
1 1 小明 优秀
2 1 小红 优秀
3 1 小军 优秀
5 2 小白 优秀
7 2 小林 优秀
8 3 小新 优秀
9 3 小王 优秀
10 3 小丽 优秀
4 1 小米 及格

FROM子句¶

一个特殊的表：

SELECT 1 + 1 FROM DUAL;
-- 等同于
SELECT 1 + 1;

这个设计类似Linux中的/dev/null

WHERE子句¶

GROUP子句¶

HAVING子句¶

ORDER子句¶

窗口函数¶

输出限制¶

锁¶

集合查询¶

关系数据理论¶

范式¶

模式分解¶