简明 PostgreSQL

1. 使用 psql
2. SQL 概要
3. 创建数据库与表
4. 查询 SELECT
5. 分组与聚合
- 5.1. 聚合函数
- 5.2. 对表进行分组 GROUP BY
6. 排序 ORDER BY
7. 数据更新
8. 视图 VIEW
9. 子查询
- 9.1. 标量子查询
- 9.2. 关联子查询 AS
  - 9.2.1. 关联名称的作用域
10. 函数
11. 谓词
12. 条件分支 CASE WHEN THEN ELSE END
13. 多表查询
14. OLAP
- 14.1. 窗口函数 OVER PARTITION BY ORDER BY ASC DESC
- 14.2. 大分组复用小分组 GROUP BY
15. TODO 应用程序通过 API 访问 RDBMS

1. 使用 `psql`

1.1. 打开 `psql`

代码1 运行安装时自带的 runpsql.bat (以 MS-Windows 为例)

PS C:/Users/shynur> ./bin/PostgreSQL/16/scripts/runpsql.bat

1.2. 退出 `psql`

可直接叉掉窗口, 或

database_name=# \q

1.3. TODO `psql` 命令

这些命令以 \ 开头.

代码2 查看帮助文档

database_name=# \?

2. SQL 概要

2.1. 三类语句

2.1.1. DDL¹

`CREATE`	`DROP`	`ALTER`
创建数据库, 表, etc.	删除	修改

2.1.2. DML²

`SELECT`	`INSERT`	`UPDATE`	`DELETE`
查询表中的数据	向表中插入新数据	更新表中的数据	删除表中的数据

2.1.3. DCL³

确认/取消对数据库中的数据进行的变更

COMMIT ROLLBACK

确认取消
设定 RDBMS 中用户操作数据库中的对象 (e.g., 表) 的权限

GRANT REVOKE

赋予用户操作权限取消

`COMMIT`	`ROLLBACK`
确认	取消

`GRANT`	`REVOKE`
赋予用户操作权限	取消

2.2. 基本书写规则

语句以分号 (‘;’) 结尾
字符串&日期这两类常数须用单引号 (‘'’) 扩起

2.3. 命名约定

数据库的名字, 表名, 和列名只能使用:
- 字母
- 数字
- 下划线 (‘_’)
名称必须以字母开头
数据库的名字只能使用小写字母

2.4. 注释

名副其实, 注释对 SQL 的执行没有任何影响.

2.4.1. 行尾注释

该类注释以 -- 打头⁴, e.g.,

SELECT --1;
         3;

2.4.2. 块注释

该类注释被成对的 /* & */ 包裹, e.g.,

SELECT /* what? */
       1 /*
FROM
         where? */;

3. 创建数据库与表

3.1. 数据库的创建 CRAETE DATABASE

代码3 创建名为“shop”的数据库.

CREATE DATABASE shop;

3.2. 表的创建 CREATE TABLE

代码4 在数据库“shop”中创建名为“Products”的表.

CREATE TABLE Products (
  -- 列名       数据类型     [约束]
  product_id      CHAR(4)   NOT NULL,
  product_name VARCHAR(100) NOT NULL,
  product_type VARCHAR(32)  NOT NULL,
      sale_price INTEGER            ,
  purchase_price INTEGER            ,
  regist_date    DATE               ,
  -- 除了 ‘NOT NULL’, 其余约束可在末尾设置:
  PRIMARY KEY (product_id)
);

执行以下代码⁵以插入本文使用的示例数据:

BEGIN TRANSACTION;     /* product_id         product_type    purchase_price
                              |   product_name    |    sale_price  |   regist_date */
INSERT INTO Products VALUES('0001', 'T恤衫'  , '衣服'    , 1000,  500, '2009-09-20');
INSERT INTO Products VALUES('0002', '打孔器' , '办公用品',  500,  320, '2009-09-11');
INSERT INTO Products VALUES('0003', '运动T恤', '衣服'    , 4000, 2800,         NULL);
INSERT INTO Products VALUES('0004', '菜刀'   , '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO Products VALUES('0005', '高压锅' , '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO Products VALUES('0006', '叉子'   , '厨房用具',  500, NULL, '2009-09-20');
INSERT INTO Products VALUES('0007', '擦菜板' , '厨房用具',  880,  790, '2008-04-28');
INSERT INTO Products VALUES('0008', '圆珠笔' , '办公用品',  100, NULL, '2009-11-11');
COMMIT;

3.2.1. 数据类型

INTEGER

存储整数.
CHAR

存储定长字符串, 用法: CHAR(最大长度). 不同的 RDBMS 可能使用不同的长度单位 (e.g., 字符⁶个数, 字节数).

当要被存储的字符串长度小于最大长度时, 会使用空格补足. E.g., 向 CHAR(3) 类型的列中输入 'ab' 时, 实际存储的是 'ab '.
VARCHAR⁷

存储变长字符串, 和 CHAR 型的区别是: 不会使用空格补足. E.g., 向 VARCHAR(3) 类型的列中输入 'ab' 时, 实际存储的还是它.
DATE

存储年月日⁹.

3.2.2. 约束

NULL

能否输入空白.
KEY

所谓键, 就是在指定特定数据时使用的列的组合.
1. PRIMARY KEY

3.3. 删除表 DROP TABLE

代码5 删除“Products”表

DROP TABLE Products;

N.b., 删除的表是无法恢复的. 只能重新创建, 然后重新插入数据.

3.4. 更新表的定义 ALTER TABLE

N.b., ALTER TABLE 语句和 DROP TABLE 语句一样, 执行之后无法恢复. 误添的列可以通过 ALTER TABLE 语句删除, 或者将表全部删除之后再重新创建.

3.4.1. 添加列 ADD COLUMN

-- 给 ‘Products’ 表添加一列, 表示商品名称的拼音.
ALTER TABLE Products
ADD COLUMN product_name_pinyin VARCHAR(100);

Oracle 中的写法¹⁰:

ALTER TABLE Products ADD (product_name_pinyin VARCHAR2(100), 更多的新列, …);

SQL Server 中的写法:

ALTER TABLE Products ADD product_name_pinyin VARCHAR(100);

它们都省略了 COLUMN 关键字.

3.4.2. 删除列 DROP COLUMN

代码6 删除“Products”表中的“product_name_pinyin”列.

ALTER TABLE Products
DROP COLUMN product_name_pinyin;

Oracle 中的写法¹⁰:

ALTER TABLE Products DROP (product_name_pinyin, 更多的旧列, …);

4. 查询 SELECT

I.e., 查询 (query) 表中的记录.

4.1. 列的查询

4.1.1. 查询指定的列

基本的 SELECT 语句:

SELECT product_name,  -- 查询结果中 column 的顺序
       sale_price     -- 与此处指定的顺序一致.
FROM Products;

包含了 SELECT 和 FROM 两个子句 (clause).

4.1.2. 查询所有列

SELECT * FROM Products;

但这样就无法设定列的显示顺序了, 这时就会按创建表时使用的 CREATE TABLE 对列进行排序.

4.1.3. 为列取别名 AS

SELECT product_id     AS id,
       product_name   AS "商品名",
       purchase_price AS "价  格"
FROM Products;

使用双引号¹¹可以包含空格, 取中文别名, etc.

4.1.4. 常数的查询

SELECT '商品'        -- 字符串常数
         AS goods,
       42            -- 数字常数
         AS "the answer to the universe",
       '2023-12-03'  -- 日期常数
         AS "today’s date",
       product_name
FROM Products;

使用字符串/日期常数时, 必须用单引号 (') 将其括起.

4.2. 筛选

4.2.1. 去重 DISTINCT

SELECT DISTINCT purchase_price
FROM Products;

这个示例说明, 在使用 DISTINCT 时, NULL 也被视为一类数据. I.e., 当 NULL 存在于多行中时, 也会被合并为一条 NULL 数据.

对单列的结果去重, 本质上是对多列组合的去重的特殊情形.

SELECT DISTINCT product_type,
                regist_date
FROM Products;

查询结果中的行与行之间, 如果各列数据都是重复的, 那么就会被合并为一条. 所以 DISTINCT 关键字只能用在第一个列名之前.

4.2.2. 过滤条件 WHERE

SELECT product_name
FROM Products
WHERE product_type = '衣服';

N.b., WHERE 子句必须紧跟在 FROM 子句之后¹².

4.3. 数学运算符

4.3.1. 算术运算符

可使用四则运算, e.g.,

SELECT product_name,
       (sale_price + 50) * 0.5 AS "近乎半价"
FROM Products;

含 NULL 的计算 NULL
所有包含 NULL 的计算, 结果肯定是 NULL. E.g.,
```
SELECT 1 + NULL,
       1 * NULL,
       1 / NULL,
       NULL / 0;
```
通常情况下, 类似 1/0 这样除数为 $ 0 $ 会发生错误, 只有像上述示例代码¹³这样用 $ 0 $ 除 NULL 不会发生错误.

4.3.2. 比较运算符

有 <, <=, =, >=, >, 和 <>¹⁴ 这些.

SELECT product_name
FROM Products
WHERE sale_price - purchase_price >= 500;

SELECT product_name, regist_date
FROM Products
-- 选取出登记日期_早于_ 2009 年 9 月 27 日的记录.
WHERE '2009-09-27' > regist_date;

N.b., 字符串类型¹⁵的数据原则上按照字典顺序进行排序, 不能与数字/日期的大小顺序混淆.

含 NULL 的比较 NULL
还记得“叉子”和“圆珠笔”的进货单价 (purchase_price) 是 NULL 吗?¹⁶ 我们来尝试根据 purchase_price 进行选取:
```
SELECT product_name
FROM Products
-- 即使删去下面两行中的注释符, 还是不能选出进价为 NULL 的商品.
WHERE /* NOT ( */ purchase_price = 2800
      OR purchase_price <> 2800 /* ) */;
```
执行结果中并没有“叉子”和“圆珠笔”. 因为这两件商品的进货单价不明 (NULL), 所以无法判定是不是指定的价格.¹⁷ 不过, SQL 提供了专门用来判断是否为 NULL 的 IS NULL 和 IS NOT NULL 运算符, 见11.3.

4.3.3. 逻辑运算符 AND OR NOT

有 AND, OR, 和 NOT 这些. N.b., AND 的结合性强于 OR.

前文中介绍了查询 NULL 时不该使用 =/<>; 实际上, 使用逻辑运算符时也需要特别对待 NULL. 因为 SQL 中与 NULL 比较的结果是不确定 (UNKNOWN), 所以:

表1 三值逻辑中的 `AND` 和 `OR` 真值表
$ P $	$ Q $	`AND`	`OR`
真	真	真	真
真	假	假	真
真	unknown	unknown	真
假	假	假	假
假	unknown	假	unknown
unknown	unknown	unknown	unknown

5. 分组与聚合

5.1. 聚合函数

所谓聚合, 就是将多行汇总为一行; 用于计算汇总的数据的函数称为聚合函数.

5.1.1. 常用的聚合函数

COUNT 函数 COUNT
聚合函数通常会对 non-NULL 的对象进行汇总, 但是只有 COUNT(*) 是例外. 它可以查出包含 NULL 在内的全部数据的行数, e.g.,
```
SELECT COUNT(*) AS "表的行数",
       COUNT(purchase_price) AS "进价列非空的行数"
FROM Products;
```
该特性是 COUNT 函数所特有的, 其它函数并不能将 * 作为参数!
SUM 函数 SUM
```
SELECT SUM(sale_price),
       SUM(purchase_price)  -- 该列含 NULL.
FROM Products;
```
注意 purchase_price 列中含 NULL, 我们来看下 PostgreSQL 是如何计算 SUM(purchase_price) 的:

T恤衫打孔器运动T恤菜刀高压锅叉子擦菜板圆珠笔合计

500 320 2800 2800 5000 ~~NULL~~ 790 ~~NULL~~

前文说过: “所有包含 NULL 的计算, 结果肯定是 NULL.” 但我们看到, 这里的结果并非 NULL, 这说明:

所有的聚合函数, 如果以列名为参数, 那么在计算之前就已经把 NULL 排除在外了. 因此, 无论有多少个 NULL 都会被无视. 这与“等价为 $ 0 $”并不相同.
AVG 函数 AVG
```
SELECT AVG(purchase_price)
FROM Products;
```
计算时对待 NULL 的方式类似 5.1.1.2:

T恤衫打孔器运动T恤菜刀高压锅叉子擦菜板圆珠笔进价总和 ÷ 6

500 320 2800 2800 5000 ~~NULL~~ 790 ~~NULL~~ 2035.
MIN 函数和 MAX 函数 MIN MAX
同样地, 这两个函数预先排除了 NULL:
```
SELECT MIN(sale_price),
       MAX(purchase_price)
FROM Products;
```
N.b., MIN/MAX 函数跟 AVG/SUM 函数对参数类型有不同的要求: AVG/SUM 函数只能对数值类型的列使用, 而 MIN/MAX 函数原则上可以用于任何数据类型的列. E.g.,
```
SELECT MIN(regist_date),
       MAX(regist_date)
FROM Products;
```
只要是能够排序的数据, 就肯定有最值, 也就能够使用这俩函数. 对日期来说, 平均值和合计值并没有实际意义, 因此不能使用 AVG/SUM 函数. 这个观点对于字符串类型的数据也适用.

T恤衫	打孔器	运动T恤	菜刀	高压锅	叉子	擦菜板	圆珠笔	合计
500	320	2800	2800	5000	~~NULL~~	790	~~NULL~~

T恤衫	打孔器	运动T恤	菜刀	高压锅	叉子	擦菜板	圆珠笔	进价总和 ÷ 6
500	320	2800	2800	5000	~~NULL~~	790	~~NULL~~	2035.

5.1.2. 向聚合函数传入互异的参数 DISTINCT

代码7 计算去重后的数据行数

SELECT COUNT(DISTINCT product_type) AS "品类数目"
FROM Products;

这里 DISTINCT 写在括号中, 是因为必须要在计算行数之前删除 product_type 列中的重复数据. 如果像SELECT DISTINCT 语句那样写在括号外的话, 就会先计算出数据行数, 再删除重复数据, 最终得到的是 product_type 列的所有行数:

代码8 先计算行数, 再删除重复的结果

SELECT DISTINCT COUNT(product_type)
FROM Products;

5.1.3. 不要在 `WHERE` 子句中使用聚合函数

代码9 错误的语句[fn:: 正确的写法见5.2.4.1.]: 在 WHERE 子句中使用了聚合函数

SELECT product_type AS "含有两种商品的品类"
FROM Products
WHERE COUNT(*) = 2
GROUP BY product_type;

原因类似5.2.1.2; 再者, WHERE 是用来对行 (而不是分组) 进行过滤的. 实际上, 只有在

SELECT
HAVING
ORDER BY

这 $ 3 $ 条子句中能使用聚合函数.

5.2. 对表进行分组 GROUP BY

5.2.1. 使用 `GROUP BY` 子句按列汇总数据

我们可以用 GROUP BY 子句像这样:

| (厨房用具) |   (衣服)   |
|            |    T恤衫   |
|   菜刀     |   运动T恤  |
|   高压锅   +------------|
|   叉子     | (办公用品) |
|   擦菜板   |   打孔器   |
|            |   圆珠笔   |

对表进行切分, e.g.,

SELECT product_type,
       COUNT(*) AS "该品类含有多少种商品"
FROM Products
GROUP BY product_type;

在 GROUP BY 子句中指定的列称为聚合键/分组列. 和 SELECT 子句一样, 可以通过逗号分隔以指定多列, 这些列的组合决定分组的方式. ¹⁸

N.b., GROUP BY 子句一定要写在 FROM (and if existing WHERE) 子句之后¹².

不要在 SELECT 子句中包含非聚合键的列
在对数据进行分组时²⁰, SELECT 子句中只能包含
- 常数 (e.g., 123, '测试')
- 聚合函数
- 聚合键
这 $ 3 $ 类. 常见错误是将非聚合键写在 SELECT 子句中²¹, e.g.,
```
SELECT product_name /* <-- 不应该包含该列 */ ,
       123, '测试', product_type, COUNT(*)
FROM Products
GROUP BY product_type;
```
其实很容易理解为什么说它是错误. 通过聚合键将表分组后, 结果中的一行数据就代表 $ 1 $ 组. 上述代码的问题就出在这里, 它的聚合键 $ \not \leftrightarrow $ 商品名, 所以从情理上讲, 你也不知道该怎么画查询结果的表格.
不要在 GROUP BY 子句中包含列的别名
```
SELECT product_type AS "品类",
       COUNT(*)
FROM Products
GROUP BY "品类" /* <-- 应该换成“product_type” */ ;
```
上述错误²²的原因在于 SQL 语句的执行顺序²³: RDBMS 在执行 SELECT 子句前, 先执行 GROUP BY 子句, 而此时 RDBMS 还不知道有什么别名²⁴.

5.2.2. 聚合键是 `NULL` 的情况

SELECT regist_date /* 含 NULL */,
       COUNT(*) AS "该日登记的商品数目"
FROM Products
GROUP BY regist_date;

从结果可以看出, 当聚合键中包含 NULL 时, 也会将其作为一组特定的数据²⁵.

5.2.3. 先过滤再分组

有 WHERE 子句时, 会先根据它指定的条件进行过滤, 然后再进行汇总. E.g.,

SELECT purchase_price,
       COUNT(*)
FROM Products
WHERE product_type = '衣服'
GROUP BY purchase_price;

总结一下上述 SQL 语句的执行顺序²⁶: FROM → WHERE → GROUP BY → SELECT.

5.2.4. 为聚合结果指定条件 HAVING

使用 HAVING 子句过滤分组
在前文的例子中, 如果我们只想取出含有两种商品的品类, 请用 HAVING 子句, e.g.,
```
SELECT product_type AS "含有两种商品的品类",
       AVG(sale_price)
FROM Products
GROUP BY product_type
HAVING COUNT(*) = 2;
```
HAVING 子句必须写在 GROUP BY 子句之后, 其在 RDBMS 内部的执行顺序也排在 GROUP BY 子句之后.
书写 HAVING 子句的限制
HAVING 子句和后接 GROUP BY 子句的 SELECT 子句一样, 能够使用的要素也有限制, 而且限制内容也完全相同²⁷. 道理是相同的, 但在此提供另一种理解的角度:

有以下错误的代码:
```
SELECT product_type,
       COUNT(*)
FROM Products
GROUP BY product_type
-- 该注释以上的部分, 看作是 汇总结果;
-- 以下的部分, 是对 汇总结果 进行筛选.
HAVING product_name = '圆珠笔';
```
我们可以认为, 汇总后的结果是 HAVING 子句能看到的数据, 而这里的汇总结果, i.e.,
```
SELECT product_type, COUNT(*) FROM Products GROUP BY product_type;
```
中, 并没有名为 product_name 的列供 HAVING 子句进行筛选.

6. 排序 ORDER BY

通常从表中抽取数据时, 如果没有特别指定顺序, 最终排列顺序便无从得知. ³⁰ 我们可以通过在 SELECT 语句末尾添加 ORDER BY 子句来明确指定排列顺序, e.g.,

代码10 按照销售单价升序排列

SELECT product_id,
       product_name,
       sale_price
FROM Products
ORDER BY sale_price /* ASC */ ;

ORDER BY 子句中书写的列名称为排序键.

无论如何, ORDER BY 子句都只写在 SELECT 语句的末尾, 因为对数据行进行排序的操作必须在结果即将返回时执行³¹. 书写该子句与其它子句的顺序为: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY.

6.1. 指定顺序 ASC DESC

如代码“按照销售单价升序排列”所示, 默认使用升序 (ascendent) 排列³²; 想要按照 sale_price 降序 (descendent) 排列时, 在列名后面使用 DESC 关键字, i.e.,

代码11 按照销售单价降序排列

SELECT product_id,
       product_name,
       sale_price
FROM Products
ORDER BY sale_price DESC;

6.2. 指定多个排序键

如代码“按照销售单价升序排列”和代码“按照销售单价降序排列”所示, “打孔器”和“叉子”的先后顺序是随机的, 因为它们的销售单价都是 $ 500 $. 我们可以指定多个排序键以实现更细致的排序, e.g.,

SELECT product_id,
       product_name,
       sale_price
FROM Products
ORDER BY sale_price ASC,
         product_id ASC;

规则是优先使用 1st 排序键, 如果该列存在相同值的话, 再接着参考下一个排序键.

6.3. `NULL` 的顺序 NULL

“叉子”和“圆珠笔”的进价都是 NULL, 究竟 NULL 会排在哪里? 是最大还是最小呢?

SELECT product_name,
       purchase_price
FROM Products
ORDER BY purchase_price;

前文说过, 含 NULL 的比较结果是 unknown, 因此干脆把 NULL 显示在开头/末尾. 究竟是在开头显示还是在末尾, 并没有特殊规定³³.

6.4. 哪些要素可以作为排序键

6.4.1. 使用别名作为排序键 AS

第5.2.1.2节说过, GROUP BY 子句中不能使用 SELECT 子句中定义的别名. 但在 ORDER BY 子句中是允许使用别名的, e.g.,

代码12 按字典序列出商品名

SELECT product_name AS "商品名"
FROM Products
ORDER BY "商品名";

6.4.2. `SELECT` 子句中未包含的列作为排序键

E.g.,

SELECT product_name FROM Products ORDER BY product_id;

6.4.3. 使用聚合函数作为排序键 GROUP BY

SELECT product_type,
       COUNT(*) AS "该品类含有多少种商品"
FROM Products
GROUP BY product_type
ORDER BY COUNT(*) /* 当然也可以用第二行定义的别名 */ ;

6.4.4. 不要使用列编号

在 SQL-92³⁴ 中已明确指出该排序功能将来会被删除.

7. 数据更新

7.1. 插入新行 INSERT INTO

7.1.1. 插入时指定各列的值 VALUES

新建示例用表“ProductsInsert”:

代码13 用来学习 INSERT INTO … VALUES 语句的示例用表

CREATE TABLE ProductsInsert (
  product_id      CHAR(4)   NOT NULL PRIMARY KEY,
  product_name VARCHAR(100) NOT NULL            ,
  product_type VARCHAR(32)  NOT NULL            ,
      sale_price INTEGER   DEFAULT 0            ,
  purchase_price INTEGER                        ,
  regist_date    DATE    -- 注意不能有多余的逗号!
);

在插入新行时手动指定各列的值, 使用

INSERT INTO <表名> [(列清单…)] VALUES (值清单…);

原则上³⁵, 执行一次上述 INSERT 语句仅会插入一行数据, 因此插入多行通常需要循环执行相应的次数.

按照列清单指定各列的值

例如, 我们要插入

`product_id`	`product_name`	`product_type`	`sale_price`	`purchase_price`	`regist_date`
(商品编号)	(商品名称)	(商品种类)	(销售单价)	(进货单价)	(登记日期)
0001	T恤衫	衣服	1000	500	2009-09-20

这样一条数据, 可以使用:

INSERT INTO ProductsInsert /* 列清单 */ (
  product_id,   product_name, product_type,
  sale_price, purchase_price,  regist_date
) VALUES /* 值清单 */ (
      '0001',        'T恤衫',       '衣服',
        1000,            500, '2009-09-20'
);

注意值清单与列清单是一一对应的.

按照表定义指定各列的值

对表进行全列 INSERT 时, 可以省略列清单. 这时值清单与表定义中的列是一一对应的. E.g., 下面两段代码执行相同的操作:

代码14 手写列清单

INSERT INTO ProductsInsert (
  product_id,   product_name, product_type,
  sale_price, purchase_price,  regist_date
) VALUES (
      '0005',       '高压锅',   '厨房用具',
        6800,           5000, '2009-01-15'
);

代码15 参考表定义以省略列清单

INSERT INTO ProductsInsert VALUES (
  '0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15'
);

插入多行

按照 ISO SQL 书写的下列语句

代码16 一句插入一行

INSERT INTO ProductsInsert VALUES (
  '0002',  '打孔器', '办公用品',  500,  320, '2009-09-11'
); INSERT INTO ProductsInsert VALUES (
  '0003', '运动T恤',     '衣服', 4000, 2800,         NULL
); INSERT INTO ProductsInsert VALUES (
  '0004',    '菜刀', '厨房用具', 3000, 2800, '2009-09-20'
);

在一些方言中可以写在一条语句中, i.e.,

代码17 一句插入多行

INSERT INTO ProductsInsert VALUES
('0002',  '打孔器', '办公用品',  500,  320, '2009-09-11'),
('0003', '运动T恤',     '衣服', 4000, 2800,         NULL),
('0004',    '菜刀', '厨房用具', 3000, 2800, '2009-09-20');

毕竟是方言, 该语法并不适用于所有 RDBMS. (DB2, SQL, SQL Server, PostgreSQL, 和 MySQL 支持它.) 在 Oracle 中需要这样写:

代码18 Oracle 中的 multi row INSERT

INSERT ALL INTO ProductsInsert VALUES (
  '0002',  '打孔器', '办公用品',  500,  320, '2009-09-11'
)          INTO ProductsInsert VALUES (
  '0003', '运动T恤',     '衣服', 4000, 2800,         NULL
)          INTO ProductsInsert VALUES (
  '0004',    '菜刀', '厨房用具', 3000, 2800, '2009-09-20'
) SELECT * FROM DUAL;

其中 DUAL 是 Oracle 特有的一种临时表³⁶ (安装时的必选项), 因此 SELECT * FROM DUAL 部分也只是临时性的, 并没有实际意义.

插入 NULL NULL

直接书写即可, e.g.,

INSERT INTO ProductsInsert (
  product_id, product_name  , product_type,
  sale_price, purchase_price, regist_date
) VALUES (
      '0006',         '叉子',   '厨房用具',
         500,           NULL, '2009-09-20'
);                   -- ^^^^

注意, 设置了 NOT NULL 约束的列是不能插入 NULL 的, 硬要执行相应的 INSERT 语句会导致插入失败³⁷.

插入默认值 DEFAULT

我们在创建“ProductsInsert”表时, 对 sale_price 列设置了 DEFAULT 约束, 所以插入新行时, 也可以直接给该列赋默认值 (此处是 $ 0 $).

显式地插入默认值

INSERT INTO ProductsInsert (
  product_id,   product_name, product_type,
  sale_price, purchase_price,  regist_date
) VALUES (
      '0007',       '擦菜板',   '厨房用具',
     DEFAULT,            790, '2009-04-28'
);/* ^^^^^^^ */

检查一下:

SELECT *
FROM ProductsInsert
WHERE product_id = '0007';

插入时省略对应的列以使用缺省值

先将刚刚插入的 0007 号商品从“ProductsInsert”表中删除:

DELETE FROM ProductsInsert
WHERE product_id = '0007';

再重新插入:

INSERT INTO ProductsInsert (
   product_id,     product_name, product_type,
/* sale_price */ purchase_price,  regist_date
) VALUES (
       '0007',         '擦菜板',   '厨房用具',
/*     0      */            790, '2009-04-28'
);

检查一下:

SELECT *
FROM ProductsInsert
WHERE product_id = '0007';

说到省略列名, 有一点要注意一下. 如果省略了没有 DEFAULT 约束的列, 则该列的值就会被设定为 NULL. (因此, 如果省略的是设置了 NOT NULL 约束的列 (e.g., “ProductsInsert”表中的 product_name 列), 则会出错.) E.g.,

INSERT INTO ProductsInsert (
  product_id,    product_name,    product_type,
  sale_price, /* purchase_price */ regist_date
) VALUES (
      '0008',        '圆珠笔',      '办公用品',
         100, /*    DEFAULT     */ '2009-11-11'
);

代码19 检查一下“ProductsInsert”表中 8 号商品的插入结果

SELECT *
FROM ProductsInsert
WHERE product_id = '0008';

7.1.2. 复制给定表中的行 SELECT

新建示例用表“ProductsCopy”:

代码20 用来学习 INSERT INTO … SELECT 语句的示例用表

CREATE TABLE ProductsCopy (
  product_id      CHAR(4)   NOT NULL PRIMARY KEY,
  product_name VARCHAR(100) NOT NULL            ,
  product_type VARCHAR(32)  NOT NULL            ,
      sale_price INTEGER                        ,
  purchase_price INTEGER                        ,
  regist_date    DATE
);

将“Products”表的数据备份过来:

INSERT INTO ProductsCopy
SELECT *
FROM Products;

检查一下:

SELECT *
FROM ProductsCopy;

实际上 INSERT INTO … SELECT 中的 SELECT 子句的书写同 SELECT 语句的书写, SELECT 语句中的各种子句都可以使用³⁸. E.g.,

新建示例用表“ProductsType”:

代码21 根据商品种类进行汇总的表

CREATE TABLE ProductsType (
  product_type   VARCHAR(32) NOT NULL PRIMARY KEY,
      sum_sale_price INTEGER                     ,
  sum_purchase_price INTEGER
);

插入汇总后的数据:

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

检查一下:

SELECT *
FROM ProductsType;

7.2. 删除旧行³⁹ DELETE

语句格式⁴⁰为

DELETE FROM <表名> [WHERE …];

省略掉 WHERE 子句会删除所有行.⁴¹
根据 WHERE 子句过滤出⁴⁵要删除的行. 这种语句的正式称呼是“搜索型 DELETE”.

以“Products”表为例,

代码22 删除 “销售单价”≥4000 的商品

DELETE FROM Products
WHERE sale_price >= 4000;

代码23 检查一下是否真的删除了

SELECT *
FROM Products;

7.3. 修改旧行 UPDATE SET

语句格式为

UPDATE <表名>  SET <列名> = <表达式>[, …]   [WHERE …];

省略掉 WHERE 子句会修改所有行.
E.g.,
代码24 将登记日期全部更新为“2009-10-10”
```
UPDATE Products
SET regist_date = '2009-10-10';
```
根据 WHERE 子句过滤出⁴⁵要修改的行. 这种语句的正式称呼是“搜索型 UPDATE”.
E.g.,
```
UPDATE Products
SET sale_price = sale_price * 10
WHERE product_type = '厨房用具';
```

7.3.1. `NULL` 清空 NULL

代码25 将编号为 8 的登记日期清空

UPDATE Products
SET regist_date = NULL
WHERE product_id = '0008';

注意, 只有未设置 NOT NULL 约束和 PRIMARY KEY 约束的列才可以清空.

7.3.2. 多列更新

有两种书写方式:

UPDATE Products
SET sale_price     = sale_price / 10,
    purchase_price = purchase_price / 2
WHERE product_type = '厨房用具';

和

UPDATE Products
SET (sale_price,      purchase_price)
  = (sale_price / 10, purchase_price / 2)
WHERE product_type = '厨房用具';

所有的 RDBMS 都支持前一种写法; 后一种写法不太常用 (但 PostgreSQL 和 DB2 是支持的).

7.4. Transaction TRANSACTION

7.4.1. 创建 transaction BEGIN START COMMIT ROLLBACK

代码26 TRANSACTION 的语法

开始 TRANSACTION;
  DML 语句 1;
  DML 语句 2;
  ...       ;
COMMIT | ROLLBACK; -- 结束该 transaction.

标准 SQL 并没有定义 transaction 的起始语句, 而是由各个 RDBMS 自己来定义的:

SQL Server, PostgreSQL:
```
BEGIN TRANSACTION;
```
MySQL:
```
START TRANSACTION;
```
Oracle, DB2:
无

Oracle 和 DB2 没有定义特定的起始语句, 其实是因为标准 SQL 中确实规定了一种悄悄开始 transaction 处理的方法. 这两种数据库每执行完 COMMIT/ROLLBACK 语句, 便会进入下一段 transaction.
与这种模式相对的是自动提交模式.

7.4.2. 自动提交模式

Transaction 并没有标准的起始语句, 而是根据 RDBMS 的不同而不同. 实际上, 几乎所有 RDBMS 的 transaction 都无需起始语句 (这里说的是执行那些在 TRANSACTION 块以外的语句), 因为在大部分情况下, 在数据库连接建立时 transaction 就已经悄悄开始了, 所以并不需要用户再明确指出起始点.

在 SQL Server, PostgreSQL, 和 MySQL 中, 每条 SQL 语句就是一个 transaction, 这称为自动提交模式. 与这种模式相对的是7.4.1.

E.g., PostgreSQL 官方文档说:

PostgreSQL actually treats every SQL statement as being executed within a transaction. If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it. A group of statements surrounded by BEGIN and COMMIT is sometimes called a transaction block.

我们之前发送给 PostgreSQL 的那些 SQL 语句, 在执行时都被悄悄括在 BEGIN 和 COMMIT 之间了, 毕竟当时我们可没有显式地写出 TRANSACTION 块.

7.4.3. Consistency of Transaction

在 PostgreSQL 的 TRANSACTION 块中, 执行 DML 语句会改变数据的状态 (但在 COMMIT 之前, 这都是暂时的), 如果执行了一条导致当前的数据状态违反了 constraint 的语句, 则该 transaction 会被立刻舍弃.

7.4.4. Isolation of Transaction

指的是不同 transaction 之间互不干扰的特性. 该特性保证了 transaction 之间不会互相嵌套.

此外, 在某个 TRANSACTION 块中进行的更改, 在该 transaction 结束之前, 对其它 transaction 而言是不可见的. 因此, 即使某个 transaction 修改了数据, 只要没 COMMIT, 其它 transaction 都是看不到这些修改的.

7.4.5. Durability of Transaction

指的是在 transaction 结束 (COMMIT/ROLLBACK) 后, RDBMS 能保证该时间点的数据状态会被保存的特性. 即使由于系统故障导致数据丢失, RDB 也大概率能通过某种手段进行恢复.

保证持久性的方法根据实现的不同而不同. 最常见的做法是, 将 transaction 的执行记录 (日志) 保存到硬盘等存储介质中; 当发生故障时, 可能通过日志恢复到故障发生前的状态.

8. 视图 VIEW

视图保存的是 SELECT 语句. 当我们从视图中查询数据时, 视图会在内部执行该 SELECT 语句并创建出一张临时表. 因此, 视图中的数据会随着原表的变化而自动更新.

8.1. 视图的创建与查询 CREATE AS

代码27 重建示例用表“Products”

DELETE FROM Products;
BEGIN TRANSACTION;     /* product_id         product_type    purchase_price
                              |   product_name    |    sale_price  |   regist_date */
INSERT INTO Products VALUES('0001', 'T恤衫'  , '衣服'    , 1000,  500, '2009-09-20');
INSERT INTO Products VALUES('0002', '打孔器' , '办公用品',  500,  320, '2009-09-11');
INSERT INTO Products VALUES('0003', '运动T恤', '衣服'    , 4000, 2800,         NULL);
INSERT INTO Products VALUES('0004', '菜刀'   , '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO Products VALUES('0005', '高压锅' , '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO Products VALUES('0006', '叉子'   , '厨房用具',  500, NULL, '2009-09-20');
INSERT INTO Products VALUES('0007', '擦菜板' , '厨房用具',  880,  790, '2008-04-28');
INSERT INTO Products VALUES('0008', '圆珠笔' , '办公用品',  100, NULL, '2009-11-11');
COMMIT;

视图的创建语法示例如下:

代码28 创建视图“ProductsTypeInfo”, 统计各品类下的商品种数

CREATE VIEW ProductsTypeInfo (
            product_type, cnt_product
) AS SELECT product_type,    COUNT(*)
     FROM Products
     GROUP BY product_type;

其中, AS SELECT 后可以接除了 ORDER BY 以外⁴⁶的任何查询子句. 在视图中存储 SELECT 语句用来查询另一张视图也是可以的, e.g.,

代码29 以视图“ProductsTypeInfo”为基础, 创建多重视图“ProductsTypeInfoOffice”, 筛选出“办公用品”的信息

CREATE VIEW ProductsTypeInfoOffice (
            product_type, cnt_product
) AS SELECT *
     FROM ProductsTypeInfo
     WHERE product_type = '办公用品';

视图的查询与表的查询别无二致, 只是性能会差很多.

SELECT * FROM ProductsTypeInfoOffice;

8.2. 更新视图中的数据行

在 SELECT 语句中视图可以和表一样使用. 那么, 对于 INSERT/DELETE/UPDATE 这类语句, 会怎么样呢?
实际上, 虽然这其中有很严格的限制, 但确实可以对视图进行更新. 标准 SQL 中有这样的规定: 如果定义视图的 SELECT 语句能够满足某些条件, 那么这个视图就可以被更新. 下面列举一些具有代表性的条件:

没有使用 DISTINCT 修饰 SELECT 子句
FROM 子句中只有一张表⁴⁷
未使用 GROUP BY 子句⁴⁸

来看一个正面的例子.
首先新建示例视图“ProductsOffice”:

代码30 用“Products”表中的“办公用品”类商品创建一个视图

CREATE VIEW ProductsOffice (
  product_id, product_name, product_type, sale_price, purchase_price, regist_date
) AS SELECT *
     FROM Products
     WHERE product_type = '办公用品';

然后, 由于 PostgreSQL (的某些版本) 中的视图在创建时默认被设定为只读, 所以需要先修改⁴⁹这个选项:

代码31 允许更新视图“ProductsOffice”的数据

CREATE OR REPLACE RULE insert_rule
AS ON INSERT TO ProductsOffice
DO INSTEAD
INSERT INTO Products VALUES (
  new.product_id,
  new.product_name,
  new.product_type,
  new.sale_price,
  new.purchase_price,
  new.regist_date
);

现在可以插入新行了:

INSERT INTO ProductsOffice VALUES
('0009',   '印章', '办公用品',    95,    10, '2009-11-30'),
('1010', '羽绒服',     '衣服', 19600, 10980, '2023-12-21');

代码32 查看是否添加成功

SELECT * FROM Products WHERE product_type = '办公用品' OR product_id = '1010';
\qecho
SELECT * FROM ProductsOffice;

8.3. 删除视图 DROP

DROP VIEW <视图名字>;

视图“ProductsTypeInfoOffice”是在另一张视图“ProductsTypeInfo”的基础上创建的, 前者依赖后者. 因此, 必须先删除前者, 才能删除后者.
不过, 在 PostgreSQL 中, 可以在删除一张视图的同时, 级联地删除所有依赖它的视图, e.g.,

DROP VIEW ProductsTypeInfo CASCADE;

9. 子查询

一言以蔽之, 子查询 (subquery) 就是一次性视图⁵⁰. 它的用法是, 将用来定义视图的 SELECT 语句直接写到各种子句⁵¹当中, 如果是写在 FROM 子句中, 还需要取上合适的别名.

我们将8.1中的例子修改为使用子查询实现:

代码33 相当于查询视图“ProductsTypeInfo”的数据

  SELECT product_type,
         "商品种数"
  FROM (SELECT product_type,
               COUNT(*) AS "商品种数"
        FROM Products
        GROUP BY product_type) AS ProductsTypeInfo;
-- 在 Oracle 的 FROM 子句中,  ^^^^ 不能使用 AS, 因此需要删去它.

9.1. 标量子查询

就是返回结果是 $ 1 \times 1 $ 矩阵⁵²的子查询. 因此, 也能当作单一的值 (scalar) 来处理. 通常, 任何可以使用标量的位置都可以使用标量子查询, 也就是说几乎所有的地方都可以使用⁵³.

我们要选取 $ \text{售价} \le \text{均价} $ 的商品, 可能会想这么写:

代码34 (错误的写法) 从“Products”表中选取 $ \text{售价} \le \text{均价} $ 的商品

SELECT product_name,
       sale_price
FROM Products
WHERE sale_price <= AVG(sale_price);

但5.1.3说过这是错误的写法, 以及为什么是错的. 正确的写法是用标量子查询:

代码35 从“Products”表中选取 $ \text{售价} \le \text{均价} $ 的商品

SELECT product_name,
       sale_price
FROM Products
WHERE sale_price <= (SELECT AVG(sale_price)
                     FROM Products);

9.2. 关联子查询 AS

E.g., 我们要选出各“品类”下 $ \text{售价} \le \text{品类均价} $ 的商品⁵⁴, 可能会想这么写:

代码36 (错误的写法) 从“Products”表中选取 $ \text{售价} \le \text{品类均价} $ 的商品

SELECT product_type,
       product_name,
       sale_price
FROM Products
--      scalar  v.s.  vector:
WHERE sale_price <= (SELECT AVG(sale_price)
                     FROM Products
                     GROUP BY product_type);

这是错的, 因为标量与向量是不可比较的, 而上述代码中的 WHERE 子句中却将标量与一个返回多行表的子查询进行比较. 正确的写法是

代码37 从“Products”表中选取 $ \text{售价} \le \text{品类均价} $ 的商品

SELECT product_type,
       product_name,
       sale_price
FROM Products /* AS ProductsAlias */
WHERE sale_price <= (SELECT AVG(sale_price)
                     FROM Products AS ProductsOfType
                     -- Oracle 中 ^^^^ 请删掉‘AS’关键字.  (后文不再赘述.)
                     WHERE ProductsOfType.product_type = Products.product_type);

其中 ProductsOfType 是子查询为自己的“Products”表起的别名, 子查询使用这个别名在 WHERE 子句中指定关联条件. (Table 的别名会 shadow 原本的名⁵⁵, 所以在上述代码中, 出现在子查询的 WHERE 子句中的“Products”指的就是外层查询中的那张表, 并不会产生歧义⁵⁶.) 在该语境下, 别名被称为关联名称, 子查询被称为关联子查询.

9.2.1. 关联名称的作用域

SQL 是按照“先内层子查询, 后外层查询”的顺序来执行的, 因此, 子查询执行结束时只会留下查询结果, 而 RDBMS 会忘记子查询中为 table 定义的别名. IOW, 子查询内部设定的关联名称, 只能在该子查询内部使用.

代码38 (错误的写法[fn:: 正确的写法见37]) 子查询中定义的关联名称出现在了其作用域 (scope) 以外

SELECT product_type,
       product_name,
       sale_price
FROM Products
WHERE ProductsOfType.product_type = Products.product_type
      AND sale_price <= (SELECT AVG(sale_price)
                         FROM Products AS ProductsOfType);

10. 函数

函数可以大致分类为

算术函数
字符串函数
日期函数
转换函数 (转换数据类型/值)
聚合函数 (在第5.1节已经介绍过)
谓词 (后文介绍)

本章介绍聚合函数/谓词以外的常规函数. (下文的“函数”一词仅指代这些会在本章介绍的函数.)

10.1. 算术函数

代码39 新建示例用表“SampleMath”

CREATE TABLE SampleMath (
  m NUMERIC(10,3),  -- 十进制小数‘NUMERIC(总位数,小数位数)’常用于金额.
  p INTEGER      ,
  q INTEGER
);

BEGIN TRANSACTION;
INSERT INTO SampleMath(m, p, q) VALUES ( 500    ,    0, NULL);
INSERT INTO SampleMath(m, p, q) VALUES (-180    ,    0, NULL);
INSERT INTO SampleMath(m, p, q) VALUES (    NULL, NULL, NULL);
INSERT INTO SampleMath(m, p, q) VALUES (    NULL,    7,    3);
INSERT INTO SampleMath(m, p, q) VALUES (    NULL,    5,    2);
INSERT INTO SampleMath(m, p, q) VALUES (    NULL,    4, NULL);
INSERT INTO SampleMath(m, p, q) VALUES (   8    , NULL,    3);
INSERT INTO SampleMath(m, p, q) VALUES (   2.27 ,    1, NULL);
INSERT INTO SampleMath(m, p, q) VALUES (   5.555,    2, NULL);
INSERT INTO SampleMath(m, p, q) VALUES (    NULL,    1, NULL);
INSERT INTO SampleMath(m, p, q) VALUES (   8.76 , NULL, NULL);
COMMIT;

(算术运算符也可以被看作算术函数, 下文将不再单独介绍.)

10.1.1. `ABS` 函数

代码40 ABS 函数的用法示例

SELECT m,
       ABS(m) AS "‘m’的绝对值"
FROM SampleMath;

10.1.2. `MOD` 函数

就是数学中的 modulo. 由于小数运算并没有余数的概念, 所以只能对整数使用该函数.

代码41 MOD 函数的用法示例

SELECT p AS "被除数",
       q AS "除数",
       MOD(p, q) AS "余数"  -- SQL Server 使用‘%’算术运算符表示取模.
FROM SampleMath;

被除数	除数	余数
0
0

7	3	1
5	2	1
4
3
1
2
1

10.1.3. `ROUND` 函数

I.e., 四舍五入.

代码42 ROUND 函数的用法示例

SELECT m AS "实际值",
       p AS "保留几位小数",
       ROUND(m, p) AS "近似值"
FROM SampleMath;

实际值	保留几位小数	近似值
500.000	0	500
-180.000	0	-180

7
5
4
8.000
2.270	1	2.3
5.555	2	5.56
1
8.760

N.b., PostgreSQL 中 ROUND 函数的 1st 参数只接受 NUMERIC 类型, 2nd 参数可以是负数;

10.2. `[20%]` 字符串函数

代码43 新建示例用表“SampleString”

CREATE TABLE SampleString (s1 VARCHAR(40),
                           s2 VARCHAR(40),
                           s3 VARCHAR(40));

BEGIN TRANSACTION;
INSERT INTO SampleString (s1, s2, s3) VALUES (      'opx',   'rt',  NULL );
INSERT INTO SampleString (s1, s2, s3) VALUES (      'abc',  'def',  NULL );
INSERT INTO SampleString (s1, s2, s3) VALUES (       '碇', '真嗣', '探病');
INSERT INTO SampleString (s1, s2, s3) VALUES (      'aaa',  NULL ,  NULL );
INSERT INTO SampleString (s1, s2, s3) VALUES (      NULL ,  'xyz',  NULL );
INSERT INTO SampleString (s1, s2, s3) VALUES (    '@!#$%',  NULL ,  NULL );
INSERT INTO SampleString (s1, s2, s3) VALUES (      'ABC',  NULL ,  NULL );
INSERT INTO SampleString (s1, s2, s3) VALUES (      'aBC',  NULL ,  NULL );
INSERT INTO SampleString (s1, s2, s3) VALUES (  'abc太郎',  'abc',  'ABC');
INSERT INTO SampleString (s1, s2, s3) VALUES ('abcdefabc',  'abc',  'ABC');
INSERT INTO SampleString (s1, s2, s3) VALUES (   'micmic',    'i',    'I');
COMMIT;

10.2.1. DONE 拼接字符串 CONCAT

代码44 演示如何拼接字符串

SELECT s1, s2, s3,
       s1 || s2 || s3 AS "拼接“s1”, “s2”, 和“s3”"
--         SQL Server       : s1 + s2 +s3
-- SQL Server 2012+ 或 MySQL: CONCAT(s1, s2, s3)
FROM SampleString;

s1	s2	s3	拼接“s1”, “s2”, 和“s3”
opx	rt
abc	def
碇	真嗣	探病	碇真嗣探病
aaa
xyz
@!#$%
ABC
aBC
abc太郎	abc	ABC	abc太郎abcABC
abcdefabc	abc	ABC	abcdefabcabcABC
micmic	i	I	micmiciI

N.b., || 并没有特殊对待 NULL, i.e., 若 NULL 是操作数则结果仍为 NULL.

10.2.2. TODO 获取字符串长度 LENGTH

10.2.3. TODO 大小写转换 LOWER UPPER

10.2.4. TODO 文本替换 REPLACE

10.2.5. TODO 字符串的子串 SUBSTRING

10.3. `[0%]` 日期函数

大部分日期函数的接口与行为都是实现相关的, 因此无法统一说明. 因此, 本节只介绍那些非常常见且用法在各 RDBMS 中差不多的.

10.3.1. TODO 获取当前日期 CURRENT_DATE

10.3.2. TODO 获取当前时间 CURRENT_TIME

10.3.3. TODO 获取当前时间戳 CURRENT_TIMESTAMP

10.3.4. TODO 提取指定时间单位的值 EXTRACT

10.4. 转换函数

10.4.1. 数据类型的转换 CAST

代码45 CAST 函数的用法示例: 字符串 → 数值

-- SQL Server 和 PostgreSQL:
  SELECT CAST('001' AS        INTEGER); /*
MySQL:
  SELECT CAST('001' AS SIGNED INTEGER);
Oracle:
  SELECT CAST('001' AS        INTEGER) FROM DUAL;
DB2:
  SELECT CAST('001' AS        INTEGER) FROM SYSIBM.SYSDUMMY1; */

int4
1

代码46 CAST 函数的用法示例: 字符串 → 日期

-- SQL Server, PostgreSQL, 和 MySQL:
  SELECT CAST('Dec 10, 2002' AS DATE); /*
Oracle
  SELECT CAST('Dec 10, 2002' AS DATE) FROM DUAL;
DB2
  SELECT CAST('Dec 10, 2002' AS DATE) FROM SYSIBM.SYSDUMMY1; */

date
2002-12-10

N.b., 书写 SQL 时必须要保证查询结果中的所有行在同一列上的数据类型是一致的!* 这也是 CAST 函数的另一个用武之地.

10.4.2. 返回第一个 non-`NULL` 值 COALESCE

将 NULL 作为参数时, 绝大多数函数的返回值都仍是 NULL. 作为对比, COALESCE (接受任意多个参数) 会返回左数第一个 non-NULL 值, 只有当参数都是 NULL 时才返回 NULL.

代码47 COALESCE 函数的用法示例

-- SQL Server, PostgreSQL, 和 MySQL:
  SELECT COALESCE(NULL, 17),
         COALESCE(NULL, 'C++', '23'); /*
Oracle
  SELECT COALESCE(NULL, 17),
         COALESCE(NULL, 'C++', '23') FROM DUAL;
DB2
  SELECT COALESCE(NULL, 17),
         COALESCE(NULL, 'C++', '23') FROM SYSIBM.SYSDUMMY1; */

coalesce	coalesce
17	C++

代码48 COALESCE 函数的用法示例: 打印 NULL 值, 而不是留空

SELECT s2,
       COALESCE(s2, 'NULL')
FROM SampleString;

s2	coalesce
rt	rt
def	def
嗣	嗣
	NULL
xyz	xyz
	NULL
	NULL
	NULL
abc	abc
abc	abc
i	i

N.b., 在 PostgreSQL 中, COALESCE 函数只接受相同类型的参数.

11. 谓词

谓词 (predicate) 就是返回值是 boolean 的函数, e.g., 比较运算符就被称为比较谓词.

11.1. 字符串的模式匹配 LIKE

代码49 新建示例用表“SampleLike”

CREATE TABLE SampleLike (s VARCHAR(6) PRIMARY KEY);

BEGIN TRANSACTION;
INSERT INTO SampleLike (s) VALUES ('abcddd');
INSERT INTO SampleLike (s) VALUES ('dddabc');
INSERT INTO SampleLike (s) VALUES ('abdddc');
INSERT INTO SampleLike (s) VALUES ('abcdd' );
INSERT INTO SampleLike (s) VALUES ('ddabc' );
INSERT INTO SampleLike (s) VALUES ('abddc' );
COMMIT;

11.1.1. 视为任意文本的通配符

代码50 通配符 '%' 的用法示例

SELECT s AS " ddd…" FROM SampleLike WHERE s LIKE 'ddd%' ; \qecho
SELECT s AS "…ddd…" FROM SampleLike WHERE s LIKE '%ddd%'; \qecho
SELECT s AS "…ddd " FROM SampleLike WHERE s LIKE  '%ddd';

ddd…
dddabc

…ddd…
abcddd
dddabc
abdddc

…ddd
dddabc
abdddc
abcdd
ddabc
abddc

11.1.2. 视为单一字符的通配符

代码51 通配符 '_' 的用法示例

SELECT s AS "abc__ " FROM SampleLike WHERE s LIKE 'abc__' ; \qecho
SELECT s AS "abc___" FROM SampleLike WHERE s LIKE 'abc___';

abc__
abcdd

abc___
abcddd

11.2. 值是否在闭区间内 BETWEEN

代码52 选取 $ 100 \le \text{售价} \le 1000 $ 的商品

SELECT product_name,
       sale_price
FROM Products
WHERE sale_price BETWEEN 100 AND 1000;

product_name	sale_price
T恤衫	1000
打孔器	500
叉子	500
擦菜板	880
圆珠笔	100

11.3. 值是否为 `NULL` IS NOT NULL

代码53 仅选取记录了“进价”的商品

SELECT product_name, purchase_price
FROM Products
WHERE purchase_price IS NOT NULL;

product_name	purchase_price
T恤衫	500
打孔器	320
运动T恤	2800
菜刀	2800
高压锅	5000
擦菜板	790
印章	10
羽绒服	10980

11.4. 值是否在集合中 NOT IN

代码54 选出“进价”是 $ 320 $/$ 500 $/$ 5000 $元的商品

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

product_name	purchase_price
T恤衫	500
打孔器	320
高压锅	5000

代码55 选出“进价”不是 $ 320 $/$ 500 $/$ 5000 $元的商品

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

product_name	purchase_price
运动T恤	2800
菜刀	2800
擦菜板	790
印章	10
羽绒服	10980

11.4.1. `IN` 无法判断值是否为 `NULL` NULL

不管是 IN 还是 NOT IN 都无法判断值是否为 NULL.

代码56 将前文中 IN 的例子和 NOT IN 的例子中的行数相加, 会发现并不等于 $ \text{商品总数} $

SELECT (SELECT COUNT(*)
        FROM Products
        WHERE purchase_price IN (320, 500, 5000))
       + (SELECT COUNT(*)
          FROM Products
          WHERE purchase_price NOT IN (320, 500, 5000))
       = COUNT(*)  -- 改成“COUNT(purchase_price)”就会返回‘TRUE‘.
FROM Products;

?column?
f

但集合中是可以含 NULL 的, 根据真值表计算布尔值即可. E.g.,

SELECT 1     IN (NULL),     -- unknown
       1     IN (NULL, 1),  -- unknown OR true
       1 NOT IN (NULL),     -- NOT (unknown)
       1 NOT IN (NULL, 1);  -- NOT (unknown OR true)

?column?	?column?	?column?	?column?
	t		f

11.4.2. 将子查询作为 `IN` 的宾语 SELECT

代码57 新建示例用表“ShopsInventory”

CREATE TABLE ShopsInventory (
  shop_id      CHAR(4)   NOT NULL,
  shop_name VARCHAR(200) NOT NULL,
  product_id   CHAR(4)   NOT NULL,
  quantity  INTEGER      NOT NULL,
  PRIMARY KEY (shop_id, product_id)
);

BEGIN TRANSACTION;
INSERT INTO ShopsInventory (shop_id, shop_name, product_id, quantity) VALUES ('000A',   '东京', '0001',  30);
INSERT INTO ShopsInventory (shop_id, shop_name, product_id, quantity) VALUES ('000A',   '东京', '0002',  50);
INSERT INTO ShopsInventory (shop_id, shop_name, product_id, quantity) VALUES ('000A',   '东京', '0003',  15);
INSERT INTO ShopsInventory (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0002',  30);
INSERT INTO ShopsInventory (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0003', 120);
INSERT INTO ShopsInventory (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0004',  20);
INSERT INTO ShopsInventory (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0006',  10);
INSERT INTO ShopsInventory (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0007',  40);
INSERT INTO ShopsInventory (shop_id, shop_name, product_id, quantity) VALUES ('000C',   '大阪', '0003',  20);
INSERT INTO ShopsInventory (shop_id, shop_name, product_id, quantity) VALUES ('000C',   '大阪', '0004',  50);
INSERT INTO ShopsInventory (shop_id, shop_name, product_id, quantity) VALUES ('000C',   '大阪', '0006',  90);
INSERT INTO ShopsInventory (shop_id, shop_name, product_id, quantity) VALUES ('000C',   '大阪', '0007',  70);
INSERT INTO ShopsInventory (shop_id, shop_name, product_id, quantity) VALUES ('000D',   '福冈', '0001', 100);
COMMIT;

代码58 选取大阪店 (“000C”) 正在销售的商品的售价

SELECT product_name,
       sale_price
FROM Products
WHERE product_id IN (SELECT product_id
                     FROM ShopsInventory
                     WHERE shop_id = '000C');

product_name	sale_price
运动T恤	4000
菜刀	3000
叉子	500
擦菜板	880

11.5. 表中是否存在记录 EXISTS

EXISTS/NOT EXISTS 用于判断表是否非空 (i.e., $ \text{行数} \gt 0 $).

代码59 选取大阪店 (“000C”) 正在销售的商品的售价

SELECT product_name,
       sale_price
FROM Products
WHERE EXISTS (SELECT *  -- 并不关心到底选取了哪些列, 因此填‘NULL’也无所谓.
              FROM ShopsInventory
              WHERE shop_id = '000C'
                    AND ShopsInventory.product_id = Products.product_id);

product_name	sale_price
运动T恤	4000
菜刀	3000
叉子	500
擦菜板	880

12. 条件分支 CASE WHEN THEN ELSE END

12.1. 搜索型 `CASE` 表达式

代码60 搜索型 CASE 表达式的基本结构

CASE
  WHEN <布尔表达式_1> THEN
    <表达式_1>
  WHEN <布尔表达式_2> THEN
    <表达式_2>
  … …
  [ELSE <表达式_0>]
END

它的执行相当于 Common Lisp 中的 cond 宏⁵⁷:

(cond
 ((布尔表达式_1) (表达式_1))
 ((布尔表达式_2) (表达式_2))
 (t              (表达式_0)))

来看一个列向量转置成行向量的例子:

代码61 用一个列向量列出每种品类下商品的总售价

SELECT product_type,
       SUM(sale_price) AS "该品类商品的总售价"
FROM Products
GROUP BY product_type;

product_type	该品类商品的总售价
衣服	24600
Other Type
办公用品	695
厨房用具	11180

代码62 用一个行向量列出每种品类下商品的总售价

 SELECT SUM(CASE
              WHEN product_type = '衣服' THEN
                sale_price
              ELSE
                0
            END),
        SUM(CASE
              WHEN product_type = '厨房用具' THEN
                sale_price
            END),
        SUM(CASE
              WHEN product_type = '办公用品' THEN
                sale_price
            END)
FROM Products;

sum	sum	sum
24600	11180	695

12.2. 简单 `CASE` 表达式

代码63 简单 CASE 表达式的基本结构

CASE <表达式_Z>
  WHEN <表达式_A> THEN
    <表达式_1>
  WHEN <表达式_B> THEN
    <表达式_2>
  … …
  [ELSE <表达式_0>]
END

它的执行相当于 Ruby 中的 case 表达式⁵⁷:

case <表达式_Z>
  when <表达式_A>
    <表达式_1>
  when <表达式_B>
    <表达式_2>
  else
    <表达式_0>
end

12.3. TODO [C] 某些方言特有的条件分支 DECODE IF

Oracle 中的 DECODE 函数
MySQL 中的 IF 表达式

13. 多表查询

13.1. 表的加减法

此处的“加减法”指的是对查询结果⁵⁸作

并集 UNION
交集 INTERSECT
差集 EXCEPT

运算, 这些运算符的结合性同数学. 可以想象成将两张表纵向拼接的 $ 3 $ 种方式⁵⁹, 因此, 需要保证两张表的结构相同⁶⁰:

列数相同;
对应列的数据类型一致⁶¹.

和数学中的集合运算一样, 运算结果中

不包含重复行, 除非在集合运算符后书写 ALL 关键字.⁶²
行与行之间没有顺序关系, 因此, ORDER BY 子句只能写成最后一句, 作用于整个运算结果⁶³.

代码64 新建示例用表“Products2”: 定义与“Products”一致; 编号 1-3 的商品与“Products”表中的商品相同, 而编号 9-10 的则是“Products”表中没有的商品.

CREATE TABLE Products2 (
  product_id      CHAR(4) PRIMARY KEY ,
  product_name VARCHAR(100)   NOT NULL,
  product_type VARCHAR(32)    NOT NULL,
      sale_price INTEGER              ,
  purchase_price INTEGER              ,
  regist_date     DATE
);

BEGIN TRANSACTION;
INSERT INTO Products2 VALUES ('0001',   'T恤衫',     '衣服', 1000,  500, '2009-09-20');
INSERT INTO Products2 VALUES ('0002',  '打孔器', '办公用品',  500,  320, '2009-09-11');
INSERT INTO Products2 VALUES ('0003', '运动T恤',     '衣服', 4000, 2800              );
INSERT INTO Products2 VALUES ('0009',    '手套',     '衣服',  800,  500              );
INSERT INTO Products2 VALUES ('0010',    '水壶', '厨房用具', 2000, 1700, '2009-09-20');
COMMIT;

13.1.1. 表的加法 UNION

代码65 合并“Products”和“Products2”

  SELECT product_id, product_name
  FROM Products
UNION /* ALL */
  SELECT product_id, product_name
  FROM Products2
ORDER BY product_id;

13.1.2. 选取同时出现在多张表中的行 INTERSECT

(MySQL 尚不支持该功能.)

代码66 选取“Products”表和“Products2”中都有的商品

  SELECT product_id, product_name
  FROM Products
INTERSECT
  SELECT product_id, product_name
  FROM Products2;

13.1.3. 排除也出现在其它表中的行 EXCEPT MINUS

(MySQL 尚不支持该功能; Oracle 使用 MINUS 关键字而非 EXCEPT.)

代码67 选取“Products2”表中的商品, 但排除那些在“Products”中出现过的

  SELECT product_id, product_name
  FROM Products2
EXCEPT
  SELECT product_id, product_name
  FROM Products;

13.2. 联结: 表的多种乘法 JOIN ON

可以把联结想象成将两张表横向拼接⁵⁹. 作为对比, “纵向拼接”要求列数相同, 而联结对行数没有要求.

13.2.1. 内联结 INNER

代码68 整合“ShopsInventory”表和“Products”表的数据, 取出商品的部分信息与各家店中该商品的在售数量. 若某条记录表明, 某店正在销售的商品不在“Products”表内 / 某商品不被“ShopsInventory”表内的任何一家店铺售卖, 则忽略该条记录.

 SELECT shop_id, shop_name,
        ShopsInventory.product_id,
        product_name, sale_price
FROM ShopsInventory  -- 联结键: vvvvvvvvvv                  vvvvvvvvvv
INNER JOIN Products ON Products.product_id = ShopsInventory.product_id
--           联结条件: ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
/* WHERE sale_price > 900 */;

shop_id	shop_name	product_id	product_name	sale_price
000A	东京	0001	T恤衫	1000
000A	东京	0002	打孔器	500
000A	东京	0003	运动T恤	4000
000B	名古屋	0002	打孔器	500
000B	名古屋	0003	运动T恤	4000
000B	名古屋	0004	菜刀	3000
000B	名古屋	0006	叉子	500
000B	名古屋	0007	擦菜板	880
000C	大阪	0003	运动T恤	4000
000C	大阪	0004	菜刀	3000
000C	大阪	0006	叉子	500
000C	大阪	0007	擦菜板	880
000D	福冈	0001	T恤衫	1000

(上述的内联结示例代码还有一种过时的写法, 见脚注⁶⁴. 虽然能在所有的 RDBMS 中执行, 但早晚会被淘汰.)

联结时, ON 子句指出联结条件⁶⁵, 联结条件所涉及的列称为联结键. INNER JOIN <表名> ON 必须写在 FROM 子句中, 因为它也是用来指出从哪张表中选取记录的.

13.2.2. 外联结 OUTER LEFT RIGHT

代码69 整合“ShopsInventory”表和“Products”表的数据, 取出商品的部分信息与各家店中该商品的在售数量. 若某条记录表明, 某店正在销售的商品在“Products”表以外 ~~某商品只被“ShopsInventory”表以外的店铺售卖~~, 则忽略该条记录.

 SELECT shop_id, shop_name,
        Products.product_id,
        product_name, sale_price
FROM ShopsInventory
RIGHT OUTER JOIN Products ON ShopsInventory.product_id = Products.product_id;

shop_id	shop_name	product_id	product_name	sale_price
000A	东京	0001	T恤衫	1000
000A	东京	0002	打孔器	500
000A	东京	0003	运动T恤	4000
000B	名古屋	0002	打孔器	500
000B	名古屋	0003	运动T恤	4000
000B	名古屋	0004	菜刀	3000
000B	名古屋	0006	叉子	500
000B	名古屋	0007	擦菜板	880
000C	大阪	0003	运动T恤	4000
000C	大阪	0004	菜刀	3000
000C	大阪	0006	叉子	500
000C	大阪	0007	擦菜板	880
000D	福冈	0001	T恤衫	1000
		0008	圆珠笔	100
		0009	印章	95
		1010	羽绒服	19600
		0005	高压锅	6800

上述结果与内联结的查询示例的结果相比, 不同点显而易见: 右外联结的查询结果引用了“Products”表中的所有记录.

左/右外联结以 OUTER JOIN 左/右侧的表为主表; 主表中的所有记录都会被外联结的查询结果所引用.⁶⁶

13.2.3. 交叉联结 CROSS

交叉联结是所有联结运算的基础. 它对两张表作 Cartesian product, 因此 CROSS JOIN 没有 ON 子句.

代码70 $ \{0, 1\} \times \{1, 2, 3\} = \{(0, 1), (0, 2), (0, 3), (1, 1), (1, 2), (1, 3)\} $

SELECT *
FROM (SELECT 0 UNION SELECT 1)
CROSS JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3);

?column?	?column?
0	1
0	2
0	3
1	1
1	2
1	3

INNER 与 OUTER 的解释:

内联结是交叉联结的结果的子集;
外联结是交叉联结的结果的超集.

13.2.4. 联结三张以上的表

代码71 新建示例用表“WarehouseInventory”: 货仓库存

CREATE TABLE WarehouseInventory (
  inventory_id       CHAR(4) NOT NULL,
    product_id       CHAR(4) NOT NULL,
  inventory_quantity INTEGER NOT NULL,
  PRIMARY KEY (inventory_id, product_id)
);

BEGIN TRANSACTION;
INSERT INTO WarehouseInventory VALUES ('P001', '0001',   0);
INSERT INTO WarehouseInventory VALUES ('P001', '0002', 120);
INSERT INTO WarehouseInventory VALUES ('P001', '0003', 200);
INSERT INTO WarehouseInventory VALUES ('P001', '0004',   3);
INSERT INTO WarehouseInventory VALUES ('P001', '0005',   0);
INSERT INTO WarehouseInventory VALUES ('P001', '0006',  99);
INSERT INTO WarehouseInventory VALUES ('P001', '0007', 999);
INSERT INTO WarehouseInventory VALUES ('P001', '0008', 200);
INSERT INTO WarehouseInventory VALUES ('P002', '0001',  10);
INSERT INTO WarehouseInventory VALUES ('P002', '0002',  25);
INSERT INTO WarehouseInventory VALUES ('P002', '0003',  34);
INSERT INTO WarehouseInventory VALUES ('P002', '0004',  19);
INSERT INTO WarehouseInventory VALUES ('P002', '0005',  99);
INSERT INTO WarehouseInventory VALUES ('P002', '0006',   0);
INSERT INTO WarehouseInventory VALUES ('P002', '0007',   0);
INSERT INTO WarehouseInventory VALUES ('P002', '0008',  18);
COMMIT;

代码72 在货仓“P001”中有库存, 且正在各家商店中售卖, 且出现在“Products”中的商品的一些信息

SELECT shop_id, shop_name,
       Products.product_id, Products.product_name, Products.sale_price,
       inventory_quantity
FROM ShopsInventory
INNER JOIN Products           ON Products.product_id = ShopsInventory.product_id
INNER JOIN WarehouseInventory ON Products.product_id = WarehouseInventory.product_id
WHERE inventory_id = 'P001';

13.3. 关系代数中的除法操作

集合运算中的除法通常称为 Division Operation in Relational Algebra, SQL 并没有定义类似的运算符. 不过, 我们可以使用已有的 SQL 关键字来实现这种运算, e.g.,

代码73 新建示例用表“PromotionNeededSkills”: 某公司升职所需的专业技能

CREATE TABLE PromotionNeededSkills (skill VARCHAR(32) PRIMARY KEY);

BEGIN TRANSACTION;
INSERT INTO PromotionNeededSkills VALUES('Oracle');
INSERT INTO PromotionNeededSkills VALUES(  'UNIX');
INSERT INTO PromotionNeededSkills VALUES(  'Java');
COMMIT;

代码74 新建示例用表“EmployeesSkills”

CREATE TABLE EmployeesSkills (
  employee VARCHAR(32),
     skill VARCHAR(32),
  PRIMARY KEY(employee, skill)
);

BEGIN TRANSACTION;
INSERT INTO EmployeesSkills VALUES('虎杖', 'Oracle');
INSERT INTO EmployeesSkills VALUES('虎杖',   'UNIX');
INSERT INTO EmployeesSkills VALUES('虎杖',   'Java');
INSERT INTO EmployeesSkills VALUES('虎杖',     'C#');
INSERT INTO EmployeesSkills VALUES('钉崎', 'Oracle');
INSERT INTO EmployeesSkills VALUES('钉崎',   'UNIX');
INSERT INTO EmployeesSkills VALUES('钉崎',   'Java');
INSERT INTO EmployeesSkills VALUES('惠惠',   'UNIX');
INSERT INTO EmployeesSkills VALUES('惠惠', 'Oracle');
INSERT INTO EmployeesSkills VALUES('惠惠',    'PHP');
INSERT INTO EmployeesSkills VALUES('惠惠',   'Perl');
INSERT INTO EmployeesSkills VALUES('惠惠',    'C++');
INSERT INTO EmployeesSkills VALUES('五条',   'Perl');
INSERT INTO EmployeesSkills VALUES('乙骨', 'Oracle');
COMMIT;

下面就是关系除法:

代码75 有资格升职的员工

SELECT DISTINCT employee
FROM EmployeesSkills AS ES1
WHERE NOT EXISTS (SELECT skill
                  FROM PromotionNeededSkills
                EXCEPT
                  SELECT skill
                  FROM EmployeesSkills AS ES2
                WHERE ES1.employee = ES2.employee);

14. OLAP

全称 OnLine Analytical Processing.

14.1. 窗口函数 OVER PARTITION BY ORDER BY ASC DESC

有时需要对表中的记录进行分类⁶⁷但不汇总 (i.e., 行数不变⁶⁸), 我们称该操作为 partition, 称同一类别下的记录属于一个 window⁶⁹.

代码76 window function 的语法

<窗口函数>(参数…) OVER (
  [PARTITION BY <供 partition 参考的列清单>]
       ORDER BY <供   排序    参考的列清单> [ASC|DESC]
  [ROWS … PRECEDING|FOLLOWING …]    -- 默认: ^^^
) -- ^^^^^^^^^^^^^^^^^^^^^^^^ 供充当窗口函数的聚合函数使用

窗口函数就是为了实现 OLAP 而添加的标准 SQL 功能, 因此又称 OLAP 函数⁷⁰.

14.1.1. [A] 专用窗口函数⁷¹

为每一行提供有序编号 RANK ROW_NUMBER DENSE_RANK

SELECT product_name, product_type, sale_price,
       RANK() OVER (PARTITION BY product_type ORDER BY sale_price)
FROM Products;

product_name	product_type	sale_price	rank
No name	Other Type		1
印章	办公用品	95	1
圆珠笔	办公用品	100	2
打孔器	办公用品	500	3
叉子	厨房用具	500	1
擦菜板	厨房用具	880	2
菜刀	厨房用具	3000	3
高压锅	厨房用具	6800	4
T恤衫	衣服	1000	1
运动T恤	衣服	4000	2
羽绒服	衣服	19600	3

上述代码中, 我们

使用 PARTITION BY, 将记录按照 product_type 分隔成多个窗口;
然后使用 ORDER BY, 分别为各窗口内的记录按照 sale_price 的升序提供编号.

N.b.,

RANK() OVER (… ORDER BY …)

并不保证查询结果中记录之间的顺序, 它只负责提供编号.⁷²

代码77 $ 3 $ 种常见的编号方式: RANK, ROW_NUMBER, and DENSE_RANK

SELECT product_name, sale_price,
             RANK() OVER (ORDER BY sale_price),
       ROW_NUMBER() OVER (ORDER BY sale_price),
       DENSE_RANK() OVER (ORDER BY sale_price)
FROM Products
WHERE sale_price BETWEEN 100 AND 999;

product_name	sale_price	rank	row_number	dense_rank
圆珠笔	100	1	1	1
打孔器	500	2	2	2
叉子	500	2	3	2
擦菜板	880	4	4	3

14.1.2. [B] 书写窗口函数的位置

通常是书写在 SELECT 子句中.⁷³

IOW, 它不能在 WHERE/GROUP BY 子句中使用.
这是因为, 在 RDBMS 内部, 窗口函数是对 WHERE/GROUP BY 子句处理后的“结果”进行的操作. 以 RANK 为例, 在得到用户想要的结果之前, 即使进行了排序处理以获取编号, 那样的编号也是错误的; 在得到编号之后, 如果通过 WHERE 子句过滤掉了某些记录, 或者使用 GROUP BY 子句进行了汇总处理, 那么先前得到的排序结果就没有参考价值了.
也正因如此, 在 SELECT 子句之外使用窗口函数是没有意义的.

14.1.3. [A] 聚合函数充当窗口函数 COUNT AVG SUM MIN MAX

只有部分聚合函数能作为窗口函数使用⁷⁴, 本节仅以 AVG 举例.

代码78 以“当前记录”为基准, 计算 $ \text{product_id} \le \text{当前的 product_id} $ 的商品们的平均售价

SELECT product_id, product_name, sale_price,
       AVG(sale_price) OVER (ORDER BY product_id),  -- ‘AVG’依旧会忽视‘NULL’值.
       '前 ' || CAST(ROW_NUMBER() OVER (ORDER BY product_id)
                     AS VARCHAR) || ' 件商品的平均售价'
FROM Products
ORDER BY product_id;

product_id	product_name	sale_price	avg	?column?
0001	T恤衫	1000	1000.0000000000000000	前 1 件商品的平均售价
0002	打孔器	500	750.0000000000000000	前 2 件商品的平均售价
0003	运动T恤	4000	1833.3333333333333333	前 3 件商品的平均售价
0004	菜刀	3000	2125.0000000000000000	前 4 件商品的平均售价
0005	高压锅	6800	3060.0000000000000000	前 5 件商品的平均售价
0006	叉子	500	2633.3333333333333333	前 6 件商品的平均售价
0007	擦菜板	880	2382.8571428571428571	前 7 件商品的平均售价
0008	圆珠笔	100	2097.5000000000000000	前 8 件商品的平均售价
0009	印章	95	1875.0000000000000000	前 9 件商品的平均售价
1001	No name		1875.0000000000000000	前 10 件商品的平均售价
1010	羽绒服	19600	3647.5000000000000000	前 11 件商品的平均售价

在窗口中滑动框架 ROWS PRECEDING FOLLOWING BETWEEN AND

代码79 计算“Products”表中上方两个和自身的平均售价 (i.e., 统计学中的移动平均)

SELECT product_id, product_name, sale_price,
       AVG(sale_price) OVER (ORDER BY product_id
                             ROWS 2 PRECEDING) AS "moving average"
FROM Products;

product_id	product_name	sale_price	moving average
0001	T恤衫	1000	1000.0000000000000000
0002	打孔器	500	750.0000000000000000
0003	运动T恤	4000	1833.3333333333333333
0004	菜刀	3000	2500.0000000000000000
0005	高压锅	6800	4600.0000000000000000
0006	叉子	500	3433.3333333333333333
0007	擦菜板	880	2726.6666666666666667
0008	圆珠笔	100	493.3333333333333333
0009	印章	95	358.3333333333333333
1001	No name		97.5000000000000000
1010	羽绒服	19600	9847.5000000000000000

框架宽度为 $ 3 $ 且包含当前记录时, ROWS 子句有三种写法:

```
ROWS 2 PRECEDING
```
上上一个 + 上一个 + 当前记录.

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

上一个 + 当前记录 + 下一个.

```
ROWS 2 FOLLOWING
```
当前记录 + 下一个 + 下下一个.

14.2. 大分组复用小分组 GROUP BY

代码80 计算出小分组与大分组的售价总和

  SELECT '所有品类 合计' AS product_type, SUM(sale_price)
  FROM Products
UNION ALL
  SELECT product_type || ' 合计', SUM(sale_price)
  FROM Products
  GROUP BY product_type;

注意到, 上述代码中的大分组 (i.e., 所有品类下的商品的售价的总和) 其实就是所有小分组 (i.e., 单个品类下的商品的售价的总和) 的总和. IOW, 计算大分组时, 我们或许应该寻找某种能复用小分组的计算结果的方法, 从而避免重新地扫描整张表.

为了解决类似的问题, 标准 SQL 引入了 $ 3 $ 个 GROUPING 运算符:

ROLLUP
CUBE
GROUPING SETS

14.2.1. 由小计得出合计 ROLLUP

我们可以使用

GROUP BY ROLLUP(列名_1, 列名_2, …)

计算出小分组, 再根据小分组计算出中分组, 再根据中分组计算出更大的分组的售价总和:

代码81 计算: • 各品类下不同日期登记的商品售价的总和; • 各品类下所有日期登记的商品售价的总和; • 所有品类下所有日期登记的商品售价的总和

  SELECT product_type,
         regist_date,
         SUM(sale_price)
  FROM Products
-- Oracle, SQL Server, DB2, and PostgreSQL:
  GROUP BY ROLLUP(product_type, regist_date) /*
MySQL:
  GROUP BY product_type, regist_date WITH ROLLUP
*/;

product_type	regist_date	sum
36475
Other Type
办公用品	2009-11-30	95
衣服	2023-12-21	19600
衣服		4000
厨房用具	2008-04-28	880
衣服	2009-09-20	1000
办公用品	2009-11-11	100
厨房用具	2009-01-15	6800
办公用品	2009-09-11	500
厨房用具	2009-09-20	3500
衣服		24600
Other Type
办公用品		695
厨房用具		11180

从结果中看, 上述代码相当于分别执行了

按“品类”和“登记日期”分组
```
GROUP BY product_type, regist_date
```
按“品类”分组
```
GROUP BY product_type
```
不分组
```
GROUP BY ()
```

这三种情况下的聚合查询.

由此容易得出 GROUP BY ROLLUP(聚合键清单) 会按 |聚合键清单|+1 种方式对表进行分组, 从最细粒度的聚合集逐步扩大到将整个表作为一个聚合单位.

14.2.2. 超级分组记录 GROUPING

之前提到过不要在 SELECT 子句中包含非聚合键的列, 而代码2却作为特例无视这一规则.

E.g., 在

GROUP BY ROLLUP(product_type, regist_date)

所产生的一种分组方式

GROUP BY product_type

中, regist_date 明显不是聚合键, 但仍然出现在了 SELECT 子句中, RDBMS 也只好把它打印为 NULL, 我们将这样的记录称为 super group row.

然而, 把 regist_date 打印为 NULL 是可能导致歧义的. 因为在

GROUP BY ROLLUP(product_type, regist_date)

所产生的另一种分组方式

GROUP BY product_type, regist_date

中, regist_date 确实是聚合键, 而且聚合后的值确实可能是 NULL, e.g., 代码2的结果中就出现了两条符合

`product_type`	`regist_date`
衣服	`NULL`

的记录. 这样, 我们就分辨不出哪条才是 super group row 了.

为了避免这样的混淆, 我们可以使用 GROUPING 函数判断 SELECT 子句中的某列

若使该条记录成为 super group row, 则返回 $ 1 $;
否则返回 $ 0 $.

将代码2修改一下, 使用 GROUPING 函数:

SELECT CAST(GROUPING(product_type) AS CHAR) || ' ' || COALESCE(product_type, ' '),
       CAST(GROUPING(regist_date)  AS CHAR) || ' ' || COALESCE(CAST(regist_date AS VARCHAR), ' '),
       SUM(sale_price)
FROM Products
GROUP BY ROLLUP(product_type, regist_date);

14.2.3. |幂集|个分组方式 CUBE

GROUP BY CUBE(聚合键清单) 会按 2^{|聚合键清单|} 种方式对表进行分组, e.g.,

GROUP BY CUBE(product_type, regist_date)

将记录按照

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

这 $ 4 $ 种方式分组.

14.2.4. 每种分组仅参考单个聚合键 GROUPING SETS

GROUP BY GROUPING SETS(聚合键清单) 会按 |聚合键清单| 种方式对表进行分组, e.g.,

GROUP BY GROUPING SETS(product_type, regist_date)

将记录按照

```
GROUP BY (              regist_date)
```
```
GROUP BY (product_type             )
```

这两种方式分组.

15. TODO 应用程序通过 API 访问 RDBMS

代码82 File Local Variables (非 Emacs 用户请忽略它)

Local Variables:
eval: '(progn
         (browse-url-default-browser "file://localhost/D:/Desktop/ToRead/SQL基础教程-MICK/TextBook.pdf")
         (w32-notification-close
          (w32-notification-notify :title "读到了第 296 页"
                                   :body " ")))
eval: (require 'ob-sql)
org-confirm-babel-evaluate: nil
sql-postgres-program: "D:/Progs/PostgreSQL/16/bin/psql.exe"
org-babel-default-header-args:sql: ((:engine   . "postgresql")
                                    (:dbhost   . "localhost")
                                    (:dbport   . 5432)
                                    (:dbuser   . "postgres")
                                    (:database . "shop"))
eval: (advice-add 'org-babel-execute:sql  ; and modify its definition when on MS-Windows.
                  :around (let ((my/PostgreSQL.org (current-buffer)))
                            (lambda (fn &rest args)
                              "类似“$env:PGPASSWORD=' '”."
                              (with-environment-variables (("PGPASSWORD" (getenv "PGPASSWORD")))
                                (when (eq (current-buffer) my/PostgreSQL.org)
                                  (setenv "PGPASSWORD" " "))
                                (apply fn args))))
                  '((name . "~shynur/Documents/CheatSheets/PostgreSQL.org")))
eval: (define-abbrev org-mode-abbrev-table
        "begsql" "#+BEGIN_SRC sql\n#+END_SRC"
        (lambda ()
          (previous-line 1)
          (end-of-line)))
eval: (abbrev-mode)
eval: (electric-quote-local-mode)
eval: (imenu-add-menubar-index)
eval: (advice-add 'org-html-export-to-html :around
                  (let ((my/PostgreSQL.org (current-buffer)))
                    (lambda (fn &rest args)
                      "导出时采用浅色主题的配色, 以适应 PDF 的背景色."
                      (if (eq (current-buffer) my/PostgreSQL.org)
                          (let ((using-light-theme? (memq 'modus-operandi custom-enabled-themes))
                                (inhibit-redisplay t))
                            (unless using-light-theme?
                              (load-theme 'modus-operandi))
                            (unwind-protect
                                (apply fn args)
                               (unless using-light-theme?
                                 (disable-theme 'modus-operandi))))
                        (apply fn args))))
                  '((name . "~shynur/Documents/CheatSheets/PostgreSQL.org")))
End:

脚注:

Data Definition Language

Data Manipulation Language

Data Control Language

⁴

MySQL 中需要在 -- 之后加空格, 否则不会被认为是注释.

⁵

对于 BEGIN TRANSACTION; 这行, 在 MySQL 中使用 START TRANSACTION;; 在 Oracle 和 DB2 中不需要这句, 后文不再赘述. 见7.4.

⁶

一个字符通常需要 1-3 bytes 来表示.

⁷

Oracle⁸ 中使用 VARCHAR2 型. (Oracle 中也有 VARCHAR 型, 但并不推荐使用.)

⁸

本文在介绍 PostgreSQL 的同时, 也会将其与 Oracle Database, SQL Server, DB2, 和 MySQL 进行对比. 若无特别声明, 前述的 $ 4 $ 个 RDBMS 以及 PostgreSQL 皆可正常执行文中的示例代码.

⁹

Oracle 中的 DATE 型还包含时分秒.

¹⁰

Oracle 可一次性增删多列. 当仅涉及 $ 1 $ 列的增删时, 可省略左右的括号.

¹¹

N.b., 不是单引号.

¹²

SQL 中子句的书写顺序是固定的!

¹³

实际上 FROM 子句在 SELECT 语句中并不是必不可少的, 可以只使用 SELECT 子句, 当成一个简陋的计算器. 但是, 在 Oracle 中, FROM 子句是必须的, 不过可以用 DUAL 这个临时表; 而在 DB2 中, 可以使用 SYSIBM.SYSDUMMY1 这个临时表.

¹⁴

有很多 RDBMS 把 != 作为该运算符的同义词, 但这是不被标准 SQL 所承认的.

¹⁵

该规则对定长字符串和可变长字符串都适用.

¹⁶

参见 Products 的建表语句.

¹⁷

更具体的分析, 参见后文

¹⁸

当指定单分组列时, 从结果上看, SELECT regist_date FROM Products GROUP BY regist_date; 与 SELECT DISTINCT regist_date FROM Products; 是一样的 (包括它们对待 NULL 的方式), 甚至执行速度¹⁹也差不多. 但是它们的语义不一样, 注意根据实际意义选择合适的写法, 不要本末倒置.

¹⁹

它们都是数据的内部处理, 都是通过排序处理来实现的.

²⁰

只要在 SELECT 子句中出现了聚合函数, 就可以看作是已经对 table 进行了分组, 哪怕并没有使用 GROUP BY 子句.

²¹

不过, MySQL 倒是认同这种写法, 在多列候补中只要有一列满足查询要求即可.

²²

不过, 这样的写法在 PostgreSQL 和 MySQL 中倒是不会发生执行错误.

²³

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

²⁴

不确定是否可以在 SELECT 子句的后半部分中引用前半部分定义的别名, 见 Question: “Can I use alias name in SELECT clause?”.

²⁵

其实这是容易推理出来的, 前文说过, GROUP BY 子句可以指定多个分组列. 如果这些列的某一种组合中包含一个 NULL 列, 从情理上讲, 我们当然不应该舍弃这种组合; 而按照单列进行分组, 不过是按照多列的组合来进行分组的一种特例.

²⁶

这与语法所规定的书写顺序并不相同. 就是这样的, SQL 的书写顺序和 RDBMS 内部的执行顺序并不相同.

²⁷

但是, 在根据聚合键作简单的过滤时, e.g., SELECT COUNT(*) AS "衣服种数" FROM Products GROUP BY product_type HAVING product_type = '衣服';, (不考虑语义的话) 更推荐将 HAVING 子句后的条件书写到 WHERE 子句中, i.e., SELECT COUNT(*) AS "衣服种数" FROM Products WHERE product_type = '衣服';. 因为后者的执行速度更快²⁸.

²⁸

这有两个主要原因: • RDBMS 进行聚合操作时, 其内部会进行排序处理²⁹. 通过 WHERE 子句过滤可以减少排序的数据量; 而 HAVING 子句是在排序之后对分组进行筛选的的. 虽然各类 RDBMS 的内部处理不尽相同, 但在排序处理这方面, 基本上都是一样的. • 可以对 WHERE 子句中条件所对应的列创建索引, 这能大幅提高处理速度. 创建索引是一种非常普遍的提高 DBMS 性能的方法, 且效果明显.

²⁹

虽然 Oracle 等 DBMS 会使用散列处理来代替排序, 但那同样也是加重机器负担的处理.

³⁰

即使是同一条 SELECT 语句, 每次执行时排列顺序很可能发生改变.

³¹

对非结果的集合 (类似中间产物) 排序似乎也没有意义.

³²

这可能是因为实际应用中按照升序排序的情况更多吧.

³³

某些 RDBMS 提供了强制 NULL 在开头/末尾显示的选项.

³⁴

1992 年制定的 SQL 标准.

³⁵

但也仅仅是原则而已, 其实很多 RDBMS 都支持一次性插入多行数据, 这样的功能称为“multi row INSERT”. See 7.1.1.3.

³⁶

在书写没有参照表的 SELECT 语句时, 写在 FROM 子句中的表并没有实际意义. 它不保存任何数据, 不能作为 INSERT/UPDATE 的宾语.

³⁷

SQL 语句执行失败时不会对表中原有的数据造成影响

³⁸

虽说如此, 但使用 ORDER BY 是没有意义的, 因为无法保证表内部记录的顺序与插入顺序是一致的.

³⁹

若要将整个表连同数据都删除, 参见3.3.

⁴⁰

与 SELECT 语句不同的是, DELETE 语句中不能使用 GROUP BY, HAVING, 和 ORDER BY 三类子句, 而只能使用 WHERE 子句. 因为 GROUP BY 和 HAVING 是用来改变查询时的数据抽取形式的, ORDER BY 是用来指定取得的结果的显示顺序的, 而在删除旧行时, 这些子句都起不到什么作用.

⁴¹

ISO SQL 中只有 DELETE 语句可以删除表中的旧行, 但很多 RDBMS (including Oracle, SQL Server, PostgreSQL, MySQL, and DB2) 还提供了标准之外的 TRUNCATE 语句⁴²^,⁴³.

⁴²

书写格式为 TRUNCATE <表名>;. 它只能用来删除表中的所有行, IOW 无法使用 WHERE 子句来过滤出要删除的行. 正因为它不能具体地控制每一行的删除与否, 所以执行速度比 DELETE 要快得多.

⁴³

尽管 TRUNCATE 语句比 DELETE 语句的性能⁴⁴要好很多, 但使用时需要多加注意. E.g., 在 Oracle 上, TRUNCATE 语句被定义为是 DDL 而不是 DML, 因此在执行 TRUNCATE 语句时会默认执行 COMMIT, 导致无法 ROLLBACK.

⁴⁴

实际上, DELETE 语句的耗时在 DML 中确实算比较久的.

⁴⁵

See 4.2.2 for WHERE 子句的语法.

⁴⁶

为什么不能使用 ORDER BY 子句呢? 因为视图和表一样, 行都是没有顺序的. 虽然有些 RDBMS 没有该限制, 但这并不是通用的语法.

⁴⁷

第4章只介绍了如何查询 $ 1 $ 张表的数据, 实际上 $ 1 $ 条 SELECT 语句可以同时查询多张表.

⁴⁸

所以肯定也没使用 HAVING 子句.

⁴⁹

DB2 和 MySQL 等其它 RDBMS 则不需要.

⁵⁰

所以, 子查询当然也是可以嵌套的, 就像视图“ProductsTypeInfoOffice”那样.

⁵¹

除了标量子查询, 其余主要是写到 FROM 子句中.

⁵²

不考虑用来显示列的名字的那一行.

⁵³

但未必会获得预期的结果, e.g., Question: “~ORDER BY~ a scalar subquery”.

⁵⁴

作为对比, 选出 $ \text{售价} \le \text{均价} $ 的代码, 见35

⁵⁵

见 https://stackoverflow.com/a/15318375/20212483.

⁵⁶

如果担心自己会混淆, 你当然可以给外层查询中的表也起个别名.

⁵⁷

在 CASE 表达式中省略 ELSE 子句时, 相当于显式的 ELSE NULL. 这两种语言在这一点上也是类似的.

⁵⁸

查询结果看上去就是一张表.

⁵⁹

A4 纸上下对折, 上半张填入 table_1, 下半张填入 table_2, 就像 Unicode 字符‘⊟’, 此为纵向拼接. 同理, 横向拼接就是左右对折, 就像 Unicode 字符‘◫’.

⁶⁰

(TODO) 对列名似乎没有要求?

⁶¹

如果类型不同, 此时有些 RDBMS 会进行隐式类型转换.

⁶²

所以, 包含 ALL 时性能更好, 因为此时不需要对查询结果进行排序/比较.

⁶³

即使能写在中间, 也没有意义, 因为集合运算可能会打乱原本的顺序.

⁶⁴

SELECT shop_id, shop_name, ShopsInventory.product_id, product_name, sale_price FROM ShopsInventory, Products WHERE Products.product_id = ShopsInventory.product_id /* AND sale_price > 900 */;

⁶⁵

可以用与/或/非组合多个联结条件.

⁶⁶

因此, <表1> LEFT OUTER JOIN <表2> 与 <表2> RIGHT OUTER JOIN <表1> 没啥区别, 通常使用 LEFT 的情况会多一些.

⁶⁷

跟 GROUP BY 一样, 是不重复, 不遗漏地分割查询结果.

⁶⁸

作为对比, GROUP BY 就会多出一个汇总的操作, 可能导致行数发生改变.

⁶⁹

含义跟 group 很像, 这么称呼其实是为了避免混淆 PARTITION 与 GROUP BY.

⁷⁰

窗口函数在 Oracle 和 SQL Server 中也称为分析函数.

⁷¹

标准 SQL 定义的 OLAP 专用函数.

⁷²

虽然经常见到的情况是, 查询结果被有序地打印了出来.

⁷³

语法上讲, 窗口函数除了能在 SELECT 子句中, 还能在 ORDER BY 和 UPDATE’s SET 子句中书写. 后二者跟 SELECT 一样都符合下文将解释的原因, 只不过, 它们使用窗口函数并没有实际意义.

⁷⁴

这些聚合函数列在本节标题后的 tag 清单中.

简明 PostgreSQL

目录

1. 使用 psql

1.1. 打开 psql

1.2. 退出 psql

1.3. TODO psql 命令

2. SQL 概要

2.1. 三类语句

2.1.1. DDL1

2.1.2. DML2

2.1.3. DCL3

2.2. 基本书写规则

2.3. 命名约定

2.4. 注释

2.4.1. 行尾注释

2.4.2. 块注释

3. 创建数据库与表

3.1. 数据库的创建 CRAETE DATABASE

3.2. 表的创建 CREATE TABLE

3.2.1. 数据类型

3.2.2. 约束

3.3. 删除表 DROP TABLE

3.4. 更新表的定义 ALTER TABLE

3.4.1. 添加列 ADD COLUMN

3.4.2. 删除列 DROP COLUMN

4. 查询 SELECT

4.1. 列的查询

4.1.1. 查询指定的列

4.1.2. 查询所有列

4.1.3. 为列取别名 AS

4.1.4. 常数的查询

4.2. 筛选

4.2.1. 去重 DISTINCT

4.2.2. 过滤条件 WHERE

4.3. 数学运算符

4.3.1. 算术运算符

4.3.2. 比较运算符

4.3.3. 逻辑运算符 AND OR NOT

5. 分组与聚合

5.1. 聚合函数

5.1.1. 常用的聚合函数

5.1.2. 向聚合函数传入互异的参数 DISTINCT

5.1.3. 不要在 WHERE 子句中使用聚合函数

5.2. 对表进行分组 GROUP BY

5.2.1. 使用 GROUP BY 子句按列汇总数据

5.2.2. 聚合键是 NULL 的情况

5.2.3. 先过滤再分组

5.2.4. 为聚合结果指定条件 HAVING

6. 排序 ORDER BY

6.1. 指定顺序 ASC DESC

6.2. 指定多个排序键

6.3. NULL 的顺序 NULL

6.4. 哪些要素可以作为排序键

6.4.1. 使用别名作为排序键 AS

6.4.2. SELECT 子句中未包含的列作为排序键

6.4.3. 使用聚合函数作为排序键 GROUP BY

6.4.4. 不要使用列编号

7. 数据更新

7.1. 插入新行 INSERT INTO

7.1.1. 插入时指定各列的值 VALUES

7.1.2. 复制给定表中的行 SELECT

7.2. 删除旧行39 DELETE

7.3. 修改旧行 UPDATE SET

7.3.1. NULL 清空 NULL

7.3.2. 多列更新

7.4. Transaction TRANSACTION

7.4.1. 创建 transaction BEGIN START COMMIT ROLLBACK

7.4.2. 自动提交模式

7.4.3. Consistency of Transaction

7.4.4. Isolation of Transaction

7.4.5. Durability of Transaction

8. 视图 VIEW

8.1. 视图的创建与查询 CREATE AS

8.2. 更新视图中的数据行

8.3. 删除视图 DROP

9. 子查询

9.1. 标量子查询

9.2. 关联子查询 AS

9.2.1. 关联名称的作用域

10. 函数

1. 使用 `psql`

1.1. 打开 `psql`

1.2. 退出 `psql`

1.3. TODO `psql` 命令

2.1.1. DDL¹

2.1.2. DML²

2.1.3. DCL³

5.1.3. 不要在 `WHERE` 子句中使用聚合函数

5.2.1. 使用 `GROUP BY` 子句按列汇总数据

5.2.2. 聚合键是 `NULL` 的情况

6.3. `NULL` 的顺序 NULL

6.4.2. `SELECT` 子句中未包含的列作为排序键

7.2. 删除旧行³⁹ DELETE

7.3.1. `NULL` 清空 NULL

10.1.1. `ABS` 函数

10.1.2. `MOD` 函数

10.1.3. `ROUND` 函数

10.2. `[20%]` 字符串函数

10.3. `[0%]` 日期函数

10.4.2. 返回第一个 non-`NULL` 值 COALESCE

11.2. 值是否在闭区间内 BETWEEN

11.3. 值是否为 `NULL` IS NOT NULL

11.4.1. `IN` 无法判断值是否为 `NULL` NULL

11.4.2. 将子查询作为 `IN` 的宾语 SELECT

12.1. 搜索型 `CASE` 表达式

12.2. 简单 `CASE` 表达式

14.1.1. [A] 专用窗口函数⁷¹

14.1.2. [B] 书写窗口函数的位置

14.1.3. [A] 聚合函数充当窗口函数 COUNT AVG SUM MIN MAX

14.2.4. 每种分组仅参考单个聚合键 GROUPING SETS