《SQL基础教程》笔记

发表于 2020-01-26 更新于 2024-01-08 分类于数据库阅读次数： Waline：

学数据库怎么能不学 SQL 😎

第 1 章数据库和 SQL

1-1 数据库是什么

将大量数据保存起来，通过计算机加工而成的可以进行高效访问的数据集合称为数据库。

数据库（Database，DB）
数据库管理系统（Database Management System，DBMS）
关系数据库管理系统（Relational Database Management System，RDBMS）

DBMS 的种类

层次数据库：（Hierarchical Database，HDB）把数据通过层次结构（树形结构）的方式表现出来
关系数据库：（Relational Database，RDB）由行和列组成的二维表来管理数据，使用专门的 SQL（Structured Query Language，结构化查询语言）对数据进行操作
- Oracle Database：甲骨文公司的 RDBMS
- SQL Server：微软公司的 RDBMS
- DB2：IBM 公司的 RDBMS
- PostgreSQL：开源的 RDBMS
- MySQL：开源的 RDBMS
面向对象数据库：（Object Oriented Database，OODB）面向对象是把数据以及对数据的操作集合起来以对象为单位进行管理，面向对象数据库是用来保存这些对象的数据库
XML 数据库：（XML Database，XMLDB） XML：eXtensible Markup Language，可扩展标记语言，是一种标记语言。标记指计算机所能理解的信息符号，通过此种标记，计算机之间可以处理包含各种信息的文章等。XML 数据库可以对 XML 形式的大量数据进行高速处理
键值存储系统：（Key-Value Store，KVS）单纯用来保存查询所使用的主键（Key）和值（Value）的组合的数据库
- Redis

1-2 数据库的结构

RDBMS 最常见的系统结构是客户端/服务器类型（C/S类型）结构
根据 SQL 语句的内容返回的数据同样必须是二维表的形式
表的列（垂直方向）称为字段，它代表了保存在表中的数据项目；表的行（水平方向）称为记录，它相当于一条数据。关系数据库必须以行为单位进行数据读写
行和列交汇的方格称为单元格，一个单元格中只能输入一个数据
本书介绍的是标准 SQL 的书写方式

1-3 SQL 概要

SQL 语句及其种类

DDL（Data Definition Language，数据定义语言）用来创建或者删除存储数据用的数据库以及数据库中的表等对象。DDL 包含以下几种指令：CREATE（创建数据库和表等对象） DROP（删除数据库和表等对象） ALTER（修改数据库和表等对象的结构）
DML（Data Manipulation Language，数据操纵语言）用来查询或者变更表中的记录。DML 包含以下几种指令：SELECT（查询表中的数据） INSERT（向表中插入新数据） UPDATE（更新表中的数据） DELETE（删除表中的数据）
DCL（Data Control Language，数据控制语言）用来确认或者取消对数据库中的数据进行的变更，还可以对 RDBMS 的用户是否有权限操作数据库中的对象（数据库表等）进行设定。DCL 包含以下几种指令：COMMIT（确认对数据库中的数据进行的变更）ROLLBACK（取消对数据库中的数据进行的变更） GRANT（赋予用户操作权限） REVOKE（取消用户的操作权限）

SQL 的基本书写规则

SQL 语句要以分号(;)结尾
SQL 语句不区分关键字的大小写，但为了理解起来更加容易，一般按以下规则来书写 SQL 语句：关键字大写；表名的首字母大写；其余（列名等）小写
插入到表中的数据是区分大小写的
字符串和日期常数书写时需要使用单引号(‘’)括起来，如‘abc’、‘2010-01-26’；数字常数书写时不需要使用任何符号标识，直接写成 1000 这样的数字即可
单词之间需要使用半角空格或者换行符进行分隔

1-4 表的创建

CREATE TABLE <表名>
(<列名1> <数据类型> <该列所需约束>,
 <列名2> <数据类型> <该列所需约束>,
 ……
 <该表的约束1>, <该表的约束2>, ……);

例：

CREATE TABLE Product
(product_id     CHAR(4)         NOT NULL,
 product_name       VARCHAR(100)    NOT NULL,
 sale_price         INTEGER         ,
 register_date  DATE            ,
 PRIMARY KEY (product_id));

只能使用半角英文字母、数字、下划线(_)作为数据库、表和列的名称，名称必须以半角英文字母开头；所有的列都必须指定数据类型

基本数据类型

INTEGER 型：用来指定存储整数的列的数据类型（数字型），不能存储小数
CHAR 型：用来指定存储字符串的列的数据类型（字符型），可以像 CHAR(10)这样在括号中指定该列可以存储的字符串的长度。字符串以定长字符串的形式存储在被指定为 CHAR 型的列中，当列中存储的字符串长度达不到最大长度时，使用半角空格进行补足
VARCHAR 型：与 CHAR 型类似，但该类型的列以可变字符串的形式保存字符串，即使字符数未达到最大长度也不会用半角空格补足
DATE 型：用来指定存储日期的列的数据类型（日期型）

约束的设置

NOT NULL：必须输入数据的约束
PRIMARY KEY：主键约束，意味着该列数据不能重复，根据该列可以唯一确定一行数据

1-5 表的删除和更新

表的删除

DROP TABLE <表名>;

表定义的更新

添加列 ALTER TABLE <表名> ADD COLUMN <列的定义>

删除列 ALTER TABLE <表名> DROP COLUMN <列的名称>

向表中插入数据

INSERT INTO <表名> VALUES (<value1>,<value2>,……);

第 2 章查询基础

2-1 SELECT 语句基础

列的查询

1 2	SELECT <列名>,…… FROM <表名>;

查询所有的列

1 2	SELECT * FROM <表名>;

为列设定别名

1
2
3

SELECT product_id AS id,
         product_name AS name
FROM Product;

使用双引号可以设定包含空格或中文的别名

1
2
3

SELECT product_id AS "商品编号",
         product_name AS "商品名称"
FROM Product;

从结果中删除重复行

使用 DISTINCT 关键字

1 2	SELECT DISTINCT product_type FROM Product;

使用 DISTINCT 时，NULL 也被视为一类数据，NULL 存在于多行中时，也会被合并为一条 NULL 数据 DISTINCT 也可以在多列之前使用，会将多个列的数据进行组合，将重复的数据合并为一条。此时 DISTINCT 关键字只能用在第一个列名之前

根据 WHERE 语句来选择记录

SELECT 语句通过 WHERE 子句来指定查询数据的条件

1
2
3

SELECT <列名>,……
FROM <表名>
WHERE <条件表达式>;

注释的书写方法

单行注释：书写在“–”之后，只能写在同一行。MySQL 中需要在“–“后加入半角空格
多行注释：书写在/和/之间，可以跨多行

2-2 算术运算符和比较运算符

注意 NULL

所有包含 NULL 的计算，结果肯定是 NULL

比较运算符

比较运算符可以对字符、数字和日期等几乎所有数据类型的列和值进行比较

<>：不等于
=：大于等于
<=：小于等于

字符串类型的数据原则上按照字典顺序进行排序，不能与数字的大小顺序混淆。 ’1‘、’2‘、’3‘、’10‘、’11‘、’222‘按字典顺序排列结果为：‘1’，‘10’，‘11’，‘2’，‘222’，‘3’。即，‘2’>’10’
不能对 NULL 使用比较运算符。在找 NULL 记录时，用“=NULL”或者“<>NULL”是得不到结果的，要使用“IS NULL”和“IS NOT NULL”

2-3 逻辑运算符

三值逻辑

SQL 除了真、假外，还存在第三个逻辑值——不确定（UNKNOWN）

第 3 章聚合与排序

3-1 对表进行聚合查询

聚合函数

COUNT：计算表中的记录数
SUM：计算表中数值列中数据的合计值
AVG：计算表中数值列中数据的平均值
MAX：求出表中任意列中数据的最大值
MIN：求出表中任意列中数据的最小值

COUNT 函数的结果根据参数的不同而不同。COUNT()会得到包含 NULL 的数据行数，而 COUNT(<列名>)会得到 NULL 之外的数据行数
除了 COUNT()之外，聚合函数会将 NULL 排除在外。比如，在计算平均值时，会事先删除 NULL 再进行计算，分母会减少 NULL 的个数

3-2 对表进行分组

GROUP BY 子句

1
2
3

SELECT <列名1>,<列名2>,<列名3>,……
FROM <表名>
GROUP BY <列名1>,<列名2>,<列名3>,……;

GROUP BY 子句中指定的列称为聚合键或者分组列。当聚合键中包含 NULL 时，在结果中会以“不确定”行（空行）的形式表现出来

与聚合函数和 GROUP BY 子句有关的常见错误

在 SELECT 子句中书写了多余的列。使用聚合函数时，SELECT 子句中只能存在以下三种元素：常数、聚合函数、GROUP BY 子句中指定的列名（也就是聚合键）
在 GROUP BY 子句中写了列的别名。这种写法在 MySQL 中不会发生执行错误，但其并不通用，因此最好不要使用
GROUP BY 子句的结果能排序吗？GROUP BY 子句结果的显示是无序的
在 WHERE 子句中使用聚合函数。只有 SELECT 子句和 HAVING 子句（以及 ORDER BY 子句）中能够使用聚合函数

3-3 为聚合结果指定条件

HAVING 子句

HAVING 子句用来为聚合结果指定条件

SELECT <列名1>,<列名2>,<列名3>,……
FROM <表名>
GROUP BY <列名1>,<列名2>,<列名3>,……
HAVING <分组结果对应的条件>;

WHERE 子句用来指定数据行的条件，HAVING 子句用来指定分组的条件

3-4 对查询结果进行排序

ORDER BY 子句

1
2
3

SELECT <列名1>,<列名2>,<列名3>,……
FROM <表名>
ORDER BY <排序基准列1>,<排序基准列2>,……(DESC);

默认按升序排列，加 DESC 后按降序排列
ORDER BY 子句中可以使用存在与表中、但并不包含在 SELECT 子句中的列，也可以使用 COUNT(*)这样的聚合函数

SELECT 语句的书写顺序和执行顺序

书写顺序： 1.SELECT 子句 →2.FROM 子句 →3.WHERE 子句 →4.GROUP BY 子句 → 5. HAVING 子句 → 6. ORDER BY 子句

执行顺序： FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY

第 4 章数据更新

4-1 数据的插入

INSERT 语句的用法

INSERT INTO <表名> (列1, 列2, 列3, ……) VALUES (值1, 值2, 值3, ……);

对表进行全列 INSERT 时，可以省略表名后的列清单，VALUES 子句的值会默认按照从左到右的顺序赋给每一列
SQL 语句执行失败时都不会对表中数据造成影响

从其他表中复制数据

1
2
3

INSERT INTO ProductCopy (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
SELECT product_id, product_name, product_type, sale_price, purchase_price, regist_date
FROM Product;

执行该 INSERT…SELECT 语句时，可以将 Product 表中这六列的数据插入到 ProductCopy 表中。在上述语句中，也可以使用 WHERE 子句或者 GROUP BY 子句等

INSERT INTO ProductType (product_type, sum_sale_price, sum_purchase_price)
SELECT product_type, SUM(sale_price), SUM(purchase_price)
FROM Product
GROUP BY product_type;

将 Product 表中的数据按商品种类进行聚合，计算出销售单价合计值以及进货单价合计值，将结果存储在 ProductType 表中

4-2 数据的删除

DELETE 语句的使用方法

保留数据表，仅删除全部数据行 DELETE FROM <表名>;
DELETE 语句的删除对象不是表或者列，而是行，因此有 FROM
删除部分数据行

1 2	DELETE FROM <表名> WHERE <条件>;

DELETE 语句中只能使用 WHERE 子句

4-3 数据的更新

使用 UPDATE 语句可以更改表中的数据

UPDATE <表名>
SET <列名1> = <表达式1>,
    <列名2> = <表达式2>,
……
WHERE <条件>;

4-4 事务

事务是需要在同一个处理单元中执行的一系列更新处理的集合。通过使用事务，可以对数据库中的数据更新处理的提交和取消进行管理 MySQL 中更新商品信息的事务

START TRANSACTION;
-- 将运动T恤的销售单价降低1000日元
UPDATE Product
SET sale_price = sale_price
WHERE product_name = '运动T恤';

-- 将T恤衫的销售单价上浮1000日元
UPDATE Product
SET sale_price = sale_price
WHERE product_name = 'T恤衫';
COMMIT;

各个 DBMS 的事务开始语句不相同，但事务结束语句都是 COMMIT 和 ROLLBACK

COMMIT——提交处理 提交事务包含的全部更新处理的结束指令，相当于文件处理中的覆盖保存。一旦提交，就无法恢复到事务开始前的状态
ROLLBACK——取消处理 取消事务包含的全部更新处理的结束指令，相当于文件处理中的放弃保存。一旦回滚，数据库就会恢复到事务开始之前的状态
**事务处理的模式：**SQL Server、PostgreSQL 和 MySQL 默认使用自动提交模式，每条 SQL 语句就是一个事务，相当于每一条语句都自动包含在事务的开始语句和结束语句之间 Oracle 直到用户执行 COMMIT 或者 ROLLBACK 为止算作一个事务

ACID 特性

原子性（Atomicity）原子性是指在事务结束时，其中所包含的更新处理要么全部执行，要么完全不执行，也就是要么占有一切要么一无所有。
一致性（Consistency）一致性指的是事务中包含的处理要满足数据库提前设置的约束，如主键约束或者 NOT NULL 约束等
隔离性（Isolation）隔离性指的是保证不同事务之间互不干扰的特性。该特性保证了事务之间不会互相嵌套。此外，在某个事务中进行的更改，在该事务结束之前，对其他事务而言是不可见的
持久性（Durability）持久性指的是在事务(不论是提交还是回滚)结束后，DBMS 能够保证该时间点的数据状态会被保存的特性。即使由于系统故障导致数据丢失，数据库也一定能通过某种手段进行恢复

第 5 章复杂查询

5-1 视图

表中存储的实际数据，而视图中保存的是从表中取出数据所使用的 SELECT 语句。我们从视图中读取数据时，视图会在内部执行该 SELECT 语句并创建出一张临时表

创建视图的方法

1
2
3

CREATE VIEW 视图名称(<视图列名1>, <视图列名2>, ……)
AS
<SELECT语句>

视图的限制：

定义视图时不能使用 ORDER BY 子句。视图和表一样，数据行是没有顺序的，在有些 DBMS 中定义视图的语句可以使用 ORDER BY 子句，但这不是通用的语法，所以最好不要使用
当视图满足以下条件时才可以对视图中的数据进行更新：SELECT 子句中未使用 DISTINCT；FROM 子句中只有一张表；未使用 GROUP BY 子句；未使用 HAVING 子句。因为视图和表需要同时进行更新，因此通过汇总得到的视图无法进行更新

删除视图

DROP VIEW 视图名称;

5-2 子查询

子查询就是将用来定义视图的 SELECT 语句直接用于 FROM 子句当中

视图 ProductSum 和确认用的 SELECT 语句

-- 根据商品种类统计商品数量的视图
CREATE VIEW ProductSum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type;

-- 确认创建好的视图
SELECT product_type, cnt_product
FROM ProductSum;

子查询

SELECT product_type, cnt_product
FROM (SELECT product_type, COUNT(*) AS cnt_product
            FROM Product
            GROUP BY product_type) AS ProductSum;

两种方法得到的结果完全相同

原则上子查询必须设定名称

标量子查询

标量就是单一的意思。标量子查询必须而且只能返回 1 行 1 列的结果要查询出销售单价高于平均销售单价的商品，由于在 WHERE 子句中不能使用聚合函数，因此以下的写法是错误的

1
2
3

SELECT product_id, product_name, sale_price
FROM Product
WHERE sale_price > AVG(sale_price);

用标量子查询替换聚合函数

1
2
3

SELECT product_id, product_name, sale_price
FROM Product
WHERE sale_price > (SELECT AVG(sale_price) FROM Product);

标量子查询的书写位置

能够使用常数或者列名的地方，无论是 SELECT 子句、GROUP BY 子句、HAVING 子句，还是 ORDER BY 子句，几乎所有的地方都可以使用

5-3 关联子查询

在细分的组内进行比较时，需要使用**关联子查询选取出各商品种类中高于该商品种类的平均销售单价的商品。**如果用以下方式

SELECT product_id, product_name, sale_price
FROM Product
WHERE sale_price > (SELECT AVG(sale_price)
                        FROM Product
                        GROUP BY product_type);

会报错，因为该子查询不是标量子查询，会返回多行结果使用关联子查询，在子查询中添加 where 子句的条件

SELECT product_type, product_name, sale_price
FROM Product AS P1
WHERE sale_price > (SELECT AVG(sale_price)
                        FROM Product AS P2
                        WHERE P1.product_type = P2.product_type
                        GROUP BY product_type);

在 WHERE 子句中追加了P1.product_type = P2.product_type这个条件，该条件的意思是在同一商品种类中对各商品的销售单价和平均单价进行比较，使得 AVG 函数按照商品种类进行了平均值计算，即使在子查询中不使用 GROUP BY 子句也能得到正确的结果，但为了跟前面出错的查询进行对比，这里仍然加上了 GROUP BY 子句

增加一列每种商品种类平均售价

SELECT product_id, product_name, product_type, sale_price,
         (SELECT AVG(sale_price) FROM Product AS P2
                WHERE P1.product_type = P2.product_type
         ) AS avg_sale_price
FROM Product AS P1;

结合条件一定要写在子查询中

SELECT product_type, product_name, sale_price
FROM Product AS P1
WHERE P1.product_type = P2.product_type
AND sale_price > (SELECT AVG(sale_price)
                            FROM Product AS P2
                            GROUP BY product_type);

如果将子查询中的关联条件移到外层查询中，会发生错误，因为这样违反了关联名称的作用域。子查询内部设定的关联名称，只能在该子查询内部使用。下图是子查询内的关联名称的有效范围

第 6 章函数、谓词、CASE 表达式

6-1 各种各样的函数

算术函数

ABS——绝对值 ABS(数值)
MOD——求余 MOD(被除数,除数)
ROUND——四舍五入 ROUND(对象数值,保留小数的位数)

字符串函数

CONCAT——拼接 CONCAT(字符串1,字符串2,……)
LENGTH——字符串长度 LENGTH(字符串)MySQL 中，LENGTH 函数返回的是字节的长度，LENGTH(“山田”)返回的结果是 6，因为 UTF-8 编码中一个汉字占 3 个字节。若要在 MySQL 中计算字符的长度，请使用 CHAR_LENGTH 函数，CHAR_LENGTH(“山田”)返回的结果是 2
LOWER——小写转换 LOWER(字符串)
UPPER——大写转换 UPPER(字符串)
REPLACE——字符串的替换 REPLACE(对象字符串,替换前的字符串,替换后的字符串)
SUBSTRING——字符串的截取 SUBSTRING(对象字符串 FROM 截取的起始位置 FOR 截取的字符数)

日期函数

CURRENT_DATE——当前日期没有参数，无需使用括号
CURRENT_TIME——当前时间没有参数，无需使用括号
CURRENT_TIMESTAMP——当前日期和时间获取当前时间戳
EXTRACT——截取日期元素 EXTRACT(日期元素 FROM 日期) eg. EXTRACT(YEAR FROM CURRENT_TIMESTAMP) 该函数的返回值并不是日期类型，而是数值类型

转换函数

CAST——类型转换 CAST(转换前的值 AS 想要转换的数据类型)
COALESCE——将 NULL 转换为其他值 COALESCE(数据1,数据2,数据3,……) 返回参数中左侧开始第一个不是 NULL 的值

6-2 谓词

谓词是满足返回值是真值这个条件的函数

LIKE 谓词——字符串的部分一致查询

前方一致查询

-- 选取出以“ddd”开头的字符串
SELECT *
FROM SampleLike
WHERE strcol LIKE 'ddd%';

中间一致查询

-- 选取出包含“ddd”的字符串
SELECT *
FROM SampleLike
WHERE strcol LIKE '%ddd%';

后方一致查询

-- 选取出以“ddd”结尾的字符串
SELECT *
FROM SampleLike
WHERE strcol LIKE '%ddd';

%代表“0 字符以上的任意字符串”
_(下划线)代表“任意 1 个字符”

BETWEEN 谓词——范围查询

BETWEEN 数值1 AND 数值2 BETWEEN 的结果中会包含数值 1 和数值 2 这两个临界点

IS NULL、IS NOT NULL——判断是否为 NULL

为了选取出某些值为 NULL 的列的数据，不能使用=，而只能使用特定的谓词 IS NULL

IN 谓词——OR 的简便用法

-- 取出进货单价为320、500、5000的商品
SELECT product_name, purchase_price
FROM Product
WHERE purchase_price = 320
OR purchase_price = 500
OR purchase_price = 5000;

和

1
2
3

SELECT product_name, purchase_price
FROM Product
WHERE purchase_price IN (320, 500, 5000);

两者等价

否定形式 NOT IN 可以取出进货单价不是 320、500、5000 的商品

IN 和 NOT IN 无法取出 NULL 数据，NULL 数据需要使用 IS NULL 和 IS NOT NULL 来判断

使用子查询作为 IN 的参数

-- 取得“在大阪店（000C）销售的商品的销售单价”
SELECT product_name, sale_price
FROM Product
WHERE product_id IN (SELECT product_id
                        FROM ShopProduct
                        WHERE shop_id = '000C');

即使 ShopProduct（商店商品）表发生变化，同样的 SELECT 语句依然可以取出“在大阪店销售的商品的销售单价”，使程序变得易于维护

EXIST 谓词

使用 EXIST 谓词实现与“IN 和子查询”相同的功能

-- 使用EXIST谓词选取出“大阪店在售商品的销售单价”
SELECT product_name, sale_price
FROM Product AS P
WHERE EXISTS (SELECT *
                FROM ShopProduct AS SP
                WHERE SP.shop_id = ‘000C’
                AND SP.product_id = P.product_id);

EXIST 只需要在右侧书写一个参数，该参数通常都是一个关联子查询
由于 EXIST 只关心记录是否存在，因此返回哪些列都没有关系。EXIST 只会判断是否存在满足子查询中 WHERE 子句指定的条件“商店编号(shop_id)为 ‘000C’，商品(Product)表和商店商品(ShopProduct)表中商品编号(product_id)相同”的记录，只有存在这样的记录时才返回真(TRUE)。因此子查询中是SELECT *，即使改为SELECT 1也可以。
作为 EXIST 参数的子查询中经常会使用SELECT *

6-3 CASE 表达式

简单 CASE 表达式

CASE <表达式>
        WHEN <表达式> THEN <表达式>
        WHEN <表达式> THEN <表达式>
        WHEN <表达式> THEN <表达式>
……
        ELSE <表达式>
END

使用简单 CASE 表达式将字符串 A ～ C 添加到商品种类中

SELECT product_name,
        CASE product_type
                WHEN '衣服' THEN CONCAT("A:", product_type)
                WHEN '办公用品' THEN CONCAT("B:", product_type)
                WHEN '厨房用具' THEN CONCAT("C:", product_type)
                ELSE NULL
        END AS abc_product_type
FROM Product;

简单 CASE 表达式的缺点是无法在 WHERE 子句中指定不同的列

搜索 CASE 表达式

CASE WHEN <求值表达式> THEN <表达式>
       WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
……
     ELSE <表达式>
END

使用搜索 CASE 表达式将字符串 A ～ C 添加到商品种类中

SELECT product_name,
        CASE WHEN product_type = '衣服'
             THEN CONCAT('A:', product_type)
             WHEN product_type = '办公用品'
             THEN CONCAT('B:', product_type)
             WHEN product_type = '厨房用具'
             THEN CONCAT('C:', product_type)
             ELSE NULL
        END AS abc_product_type
FROM Product;

搜索 CASE 表达式包含了简单 CASE 表达式的全部功能，所以一般都使用搜索 CASE 表达式

利用 CASE 表达式进行行列转换

1
2
3

SELECT product_type, SUM(sale_price) AS sum_price
FROM Product
GROUP BY product_type;

执行结果

product_type | sum_price
-------------+----------
衣服                5000
办公用品              600
厨房用具            11180

如果要得到如下结果

1
2
3

sum_price_clothes | sum_price_kitchen | sum_price_office
------------------+-------------------+-----------------
             5000 |             11180 |              600

可以在 SUM 函数中使用 CASE 表达式

-- 对按照商品种类计算出的销售单价合计值进行行列转换
SELECT SUM(CASE WHEN product_type = '衣服'
            THEN sale_price ELSE 0 END) AS sum_price_clothes,
       SUM(CASE WHEN product_type = '厨房用具'
            THEN sale_price ELSE 0 END) AS sum_price_kitchen,
       SUM(CASE WHEN product_type = '办公用品'
            THEN sale_price ELSE 0 END) AS sum_price_office
FROM Product;

第 7 章集合运算

7-1 表的加减法

表的加法——UNION

-- 用UNION选出两张表中所有的商品ID和商品名称
SELECT product_id, product_name
FROM Product
UNION
SELECT product_id, product_name
FROM Product2;

集合运算的注意事项

集合运算符通常会除去重复记录
作为运算对象的记录的列数必须相同
作为运算对象的记录中列的类型必须一致
可以使用任何 SELECT 语句，但 ORDER BY 子句只能在最后使用一次

包含重复行的集合运算——ALL 选项

在集合运算符后添加 ALL 关键字，可以保留重复行

选取表中公共部分——INTERSECT

语法与 UNION 类似。UNION 相当于求两个表的并集，INTERSECT 相当于求两个表的交集

记录的减法——EXCEPT

-- 用EXCEPT选出Product表中有而Product2表中没有的商品ID和商品名称
SELECT product_id, product_name
FROM Product
EXCEPT
SELECT product_id, product_name
FROM Product2
ORDER BY product_id;

EXCEPT 两个表的位置不一样，结果会不一样。因为减法运算中减数和被减数的位置不同，结果也不同

MySQL 尚不支持 INTERSECT 和 EXCEPT

7-2 联结（以列为单位对表进行联结）

UNION 是以行（纵向）为单位进行操作，而联结是以列（横向）为单位进行的。联结就是将其他表中的列添加进来，进行“添加列”的集合运算。

内联结

1
2
3

SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
FROM ShopProduct AS SP INNER JOIN Product AS P
                            ON SP.product_id = P.product_id;

内联结注意要点

进行联结时需要在 FROM 子句中使用多张表
ON 子句指定联结所使用的列（联结键），且 ON 要紧跟在 FROM 后
使用联结时 SELECT 子句中的列最好按照“<表的别名>.<列名>”的格式进行书写

外联结

1
2
3

SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
FROM ShopProduct AS SP RIGHT OUTER JOIN Product AS P
                            ON SP.product_id = P.product_id;

外联结注意要点

内联结只能选取出同时存在于两张表中的数据，而外联结会选取出单张表中的全部信息
外联结中用 LEFT 和 RIGHT 指定主表，LEFT OUTER JOIN 左边的表或者 RIGHT OUTER JOIN 右边的表是主表，主表中的所有数据将会被取出

三张表的联结

原则上联结表的数量并没有限制

FROM ShopProduct AS SP INNER JOIN Product AS P
    ON SP.product_id = P.product_id
        INNER JOIN InventoryProduct AS IP
            ON SP.product_id = IP.product_id

使用两次 INNER JOIN …… ON ……

第 8 章 SQL 高级处理

8-1 窗口函数

窗口函数也称为OLAP 函数（Online Analytical Processing），可以对数据库数据进行实时分析处理。
目前 MySQL8.0 版本已经支持窗口函数

窗口函数语法

1
2
3

<窗口函数> OVER ([PARTITION BY <列清单>]
                        ORDER BY <排序用列清单>)
-- []中的内容可以省略

窗口函数大体可以分为以下两种：

能够作为窗口函数的聚合函数（SUM、AVG、COUNT、MAX、MIN）
RANK、DENSE_RANK、ROW_NUMBER 等专用窗口函数

排序函数——RANK 函数

/*根据不同的商品种类(product_type)，按照销售单价(sale_price)从低到高的顺序排序*/
SELECT product_name, product_type, sale_price,
    RANK() OVER (PARTITION BY product_type
                    ORDER BY sale_price) AS ranking
FROM Product;

执行结果如下：

PARTITION BY能够设定排序的对象范围，ORDER BY能够指定按照哪一列、何种顺序进行排序。前者在横向上对表进行分组，而后者决定了纵向排序的规则。
窗口函数兼具 GROUP BY 子句的分组功能以及 ORDER BY 子句的排序功能，但 PARTITION BY 子句并不具备 GROUP BY 子句的汇总功能，因此不会减少原表中记录的行数。
通过 PARTITION BY 分组后的记录集合称为窗口，此处的窗口代表范围

无需指定 PARTITON BY

如果将整个表作为一个大的窗口来使用，无需指定 PARTITION BY，与使用没有 GROUP BY 的聚合函数时的效果一样

1
2
3

SELECT product_name, product_type, sale_price,
         RANK() OVER (ORDER BY sale_price) AS ranking
FROM Product;

执行结果如下：

排序方式

RANK 函数计算排序时，如果存在相同位次的记录，则会跳过之后的位次。比如，有 3 条记录排在第 1 位时：1 位、1 位、1 位、4 位……
DENSE_RANK 函数同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。比如，有 3 条记录排在第 1 位时：1 位、1 位、1 位、2 位……
ROW_NUMBER 函数赋予唯一的连续位次。比如，有 3 条记录排在第 1 位时：1 位、2 位、3 位、4 位……

SELECT product_name, product_type, sale_price,
    RANK() OVER (ORDER BY sale_price) AS ranking,
    DENSE_RANK() OVER (ORDER BY sale_price) AS dense_ranking,       ROW_NUMBER() OVER (ORDER BY sale_price) AS row_num
FROM Product;

执行结果如下：

窗口函数的适用范围

原则上窗口函数只能在 SELECT 子句中使用。因为窗口函数是对 WHERE 子句或者 GROUP BY 子句处理后的“结果”进行的操作，在 SELECT 子句之外使用窗口函数是没有意义的。

作为窗口函数使用的聚合函数

将 SUM 函数作为窗口函数使用

1 2	SELECT product_id, product_name, sale_price, SUM(sale_price) OVER (ORDER BY product_id) AS current_sum FROM Product;

执行结果如下：

我们得到的并不仅仅是合计值，而是按照 ORDER BY 子句指定的 product_id 的升序进行排列，计算出商品编号“小于自己”的商品的销售单价的合计值。因此，计算该合计值的逻辑就像金字塔堆积那样，一行一行逐渐添加计算对象。使用 AVG 的效果类似，计算平均值时作为统计对象的只是“排在自己之上”的记录。

在按照时间序列的顺序，计算各个时间的销售额总额等的时候，通常都会使用这种称为累计的统计方法。像这样以“自身记录（当前记录）”作为基准进行统计，就是将聚合函数当作窗口函数使用时的最大特征

计算移动平均

在窗口中可以指定更加详细的汇总范围，该备选功能中的汇总范围称为框架

--指定“最靠近的3行”作为汇总对象
SELECT product_id, product_name, sale_price,
        AVG(sale_price) OVER (ORDER BY product_id
                                ROWS 2 PRECEDING) AS moving_avg
FROM Product;

执行结果如下：

ROWS 2 PRECEDING就是将框架指定为“截止到之前 2 行”，也就是将作为汇总对象的记录限定为“自身（当前记录）、之前 1 行的记录、之前 2 行的记录”这最靠近的 3 行。

这种统计方法称为移动平均（moving average）。ROWS 2 FOLLOWING表示“截止到之后 2 行“，ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING表示从之前 1 行开始到之后 1 行结束，即包括当前记录一共 3 行

8-2 GROUPING 运算符

ROLLUP——同时得出合计和小计

SELECT product_type, SUM(sale_price) AS sum_price
FROM Product
GROUP BY product_type WITH ROLLUP;
--MySQL写法，标准SQL语句为ROLLUP(product_type)

执行结果如下：

ROLLUP 运算符相当于一次计算出不同聚合键组合的结果。上述代码实际上就是一次计算出了如下两种组合的汇总结果：

GROUP BY ()
GROUP BY (product_type)

GROUP BY ()表示没有聚合键，会得到全部数据的合计行的记录，该合计行记录称为超级分组记录（super group row）

在聚合键中使用多个值

1
2
3

SELECT product_type, regist_date, SUM(sale_price) AS sum_price FROM Product
GROUP BY product_type, regist_date WITH ROLLUP;
--MySQL写法，标准SQL语句为ROLLUP(product_type, register_date)

执行结果如下：

相当于一次计算出如下三种组合的汇总结果：

GROUP BY ()
GROUP BY (product_type)
GROUP BY (product_type, regist_date)

如下图所示

GROUPING 函数——让 NULL 更加容易分辨

GROUPING 函数在其参数列的值为超级分组记录所产生的 NULL 时返回 1，其他情况返回 0

SELECT GROUPING(product_type) AS product_type,
         GROUPING(regist_date) AS regist_date,
         SUM(sale_price) AS sum_price
FROM Product
GROUP BY product_type, regist_date WITH ROLLUP;

执行结果如下：

使用 GROUPING 函数和 CASE WHEN 子句在超级分组记录的键值中插入字符串

当 GROUPING 函数的返回值为 1 时，指定“合计”或者“小计”等字符串，其他情况返回通常的列的值

SELECT CASE WHEN GROUPING(product_type) = 1
                THEN ‘商品种类 合计’
                ELSE product_type END AS product_type,
         CASE WHEN GROUPING(regist_date) = 1
                THEN ‘登记日期 合计’
                ELSE regist_date END AS regist_date,                     SUM(sale_price) AS sum_price
FROM Product
GROUP BY product_type, regist_date WITH ROLLUP;

执行结果如下：

CUBE——用数据来搭积木

CUBE 语法和 ROLLUP 相同

SELECT CASE WHEN GROUPING(product_type) = 1
             THEN '商品种类 合计'
             ELSE product_type END AS product_type,          CASE WHEN GROUPING(regist_date) = 1
             THEN '登记日期 合计'
             ELSE regist_date END AS regist_date,            SUM(sale_price) AS sum_price
FROM Product
GROUP BY CUBE(product_type, regist_date);

执行结果如下：

相当于一次计算出如下四种组合的汇总结果：

GROUP BY ()
GROUP BY (product_type)
GROUP BY (regist_date)
GROUP BY (product_type, regist_date)

CUBE 会将 GROUP BY 子句中聚合键的“所有可能的组合”的汇总结果集中到一个结果中，一共有 2^n（n 是聚合键的个数）的组合个数；使用 ROLLUP 时组合的个数是 n+1，ROLLUP 的结果一定包含在 CUBE 的结果之中

GROUPING SETS——取得期望的积木

--只选取出将“商品种类”和“登记日期”各自作为聚合键的结果
SELECT CASE WHEN GROUPING(product_type) = 1
            THEN '商品种类 合计'
            ELSE product_type END AS product_type,       CASE WHEN GROUPING(regist_date) = 1
            THEN '登记日期 合计'
            ELSE regist_date END AS regist_date,             SUM(sale_price) AS sum_price
FROM Product
GROUP BY GROUPING SETS (product_type, regist_date);

执行结果如下：

参考：知乎 - Damien Xiong

第 1 章 数据库和 SQL

1-1 数据库是什么

1-2 数据库的结构

1-3 SQL 概要

1-4 表的创建

基本数据类型

约束的设置

1-5 表的删除和更新

表的删除

表定义的更新

向表中插入数据

第 2 章 查询基础

2-1 SELECT 语句基础

列的查询

查询所有的列

为列设定别名

从结果中删除重复行

根据 WHERE 语句来选择记录

注释的书写方法

2-2 算术运算符和比较运算符

注意 NULL

比较运算符

2-3 逻辑运算符

三值逻辑

第 3 章 聚合与排序

3-1 对表进行聚合查询

聚合函数

3-2 对表进行分组

GROUP BY 子句

与聚合函数和 GROUP BY 子句有关的常见错误

3-3 为聚合结果指定条件

HAVING 子句

3-4 对查询结果进行排序

ORDER BY 子句

SELECT 语句的书写顺序和执行顺序

第 4 章 数据更新

4-1 数据的插入

INSERT 语句的用法

从其他表中复制数据

4-2 数据的删除

DELETE 语句的使用方法

4-3 数据的更新

4-4 事务

ACID 特性

第 5 章 复杂查询

5-1 视图

创建视图的方法

视图的限制：

删除视图

5-2 子查询

标量子查询

标量子查询的书写位置

5-3 关联子查询

结合条件一定要写在子查询中

第 6 章 函数、谓词、CASE 表达式

6-1 各种各样的函数

算术函数

字符串函数

日期函数

转换函数

6-2 谓词

LIKE 谓词——字符串的部分一致查询

BETWEEN 谓词——范围查询

IS NULL、IS NOT NULL——判断是否为 NULL

IN 谓词——OR 的简便用法

使用子查询作为 IN 的参数

EXIST 谓词

6-3 CASE 表达式

简单 CASE 表达式

搜索 CASE 表达式

利用 CASE 表达式进行行列转换

第 7 章 集合运算

7-1 表的加减法

表的加法——UNION

集合运算的注意事项

包含重复行的集合运算——ALL 选项

选取表中公共部分——INTERSECT

记录的减法——EXCEPT

7-2 联结（以列为单位对表进行联结）

内联结

第 1 章数据库和 SQL

第 2 章查询基础

第 3 章聚合与排序

第 4 章数据更新

第 5 章复杂查询

第 6 章函数、谓词、CASE 表达式

第 7 章集合运算