《SQL基础教程》+《SQL进阶教程》学习笔记

写在前面：本文主要注重 SQL 的理论、主流覆盖的功能范围及其基本语法/用法。至于详细的 SQL 语法/用法，因为每家 DBMS 都有些许不同，我会在以后专门介绍某款DBMS（例如 PostgreSQL）的时候写到。

第 1 章 DBMS 与 SQL

1、DBMS 是什么 ?

数据库管理系统（Database Management System, DBMS) 是用来管理数据库的计算机系统。

本文采用 PostgreSQL 为 DBMS。

2、为什么要用 DBMS ?

问：为什么不用 文本文件 或者 excel（+VBA）?

答：DBMS 的好处有：

  • 多人共享

  • 海量存储

  • 可编程

  • 容灾机制

  • ……

3、DBMS 分类

（1）层次数据库（Hierarchical Database, HDB)
（2）关系数据库（Relational Database, RDB)

1969年诞生，形式为行列二维表，类似 excel，包括：

  • SQL（Structured Query Language，结构化查询语言）

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

如无特殊说明，本文所提到的 DBMS 都是指 RDBMS。

（3）面向对象数据库（Object Oriented Database, OODB)
（4）XML 数据库（XML Database, XMLDB)
（5）键值存储系统（Key- Value Store, KVS)

4、DBMS 常见架构

C/S 结构。

5、搭建 DBMS 环境

参考我的文章 《PostgreSQL 安装 & 用户配置》

6、SQL

（1）SQL 介绍

SQL（Structured Query Language : 结构化查询语言）是一种特定目的编程语言，用于管理关系数据库管理系统（RDBMS）。

（2）SQL 历史

1986 年，ANSI 首次制定了 SQL 的标准，之后又进行了数次修订。

1987 年成为国际标准化组织（ISO）标准。称为 标准SQL。

原则上，本书介绍的都是 标准SQL 的书写方式.

1986年，ANSI X3.135-1986，ISO/IEC 9075:1986，SQL-86

1989年，ANSI X3.135-1989，ISO/IEC 9075:1989，SQL-89

1992年，ANSI X3.135-1992，ISO/IEC 9075:1992，SQL-92（SQL2）

1999年，ISO/IEC 9075:1999，SQL:1999（SQL3）

2003年，ISO/IEC 9075:2003，SQL:2003

2008年，ISO/IEC 9075:2008，SQL:2008

2011年，ISO/IEC 9075:2011，SQL:2011

2016年，ISO/IEC 9075:2016，SQL:2016

截止目前，最新的为 SQL:2016。

（3）SQL 分类

1、DDL（Data Definition Language，数据定义语言）

CREATE： 创建数据库和表等对象

DROP： 删除数据库和表等对象

ALTER： 修改数据库和表等对象的结构


2、DML（Data Manipulation Language，数据操纵语言）

SELECT：查询表中的数据

INSERT：向表中插入新数据

UPDATE：更新表中的数据

DELETE：删除表中的数据


3、DCL（Data Control Language，数据控制语言）

COMMIT： 确认对数据库中的数据进行的变更

ROLLBACK： 取消对数据库中的数据进行的变更

GRANT： 赋予用户操作权限

REVOKE： 取消用户的操作权限


总结：实际使用的 SQL 语句当中有 90% 属于 DML。


注意：不同的数据库产品划分可能不尽相同。例如在 Oracle 中，把 TRUINCATE 定义为 DDL，而不是 DML。而事务只对 DML 有效，因此，Oracle 中的 TRUNCATE 不能使用 ROLLBACK。（执行 TRUNCATE 的同时会默认执行 COMMIT 操作。）

（4）SQL 注释

  • 单行注释 —— -- 之后

  • 多行注释 —— /*和*/之间

（5）SQL 书写建议

1、关键字大写（虽然 SQL 不区分关键字的大小写。）

2、前置逗号

SELECT col_1
, col_2
, col_3
, col_4
FROM tbl_A;

好处：方便选中和快速添删。

虽然分号或句号是表示语句结束的终止符，但是逗号是一种连接符，用于连接要素，从这一点来说，逗号的作用与 AND 或 OR 等是一样的。

第２章 SQL 基础

1、数据库 / 表

（1）数据库

CREATE DATABASE shop;

DROP DATABASE shop;

（2）表
CREATE TABLE Product
(
    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 ,
    PRIMARY KEY (product_id)
);

DROP TABLE Product;

2、SELECT

  • select * 的星号无法设定列的显示顺序，这时就会按照 CREATE TABLE 语句的定义对列进行排序。

  • DISTINCE 针对的是 select 后的所有列的去重，只需要在第一列的前面加就好（或者 * 前面）。因此，select DISTINCT "a","b" 不可以写成 select "a", DISTINCT "b" 。

    下面介绍的 GROUP BY 也可以达到去重的效果。

  • 大多数人都喜欢先写 SELECT 再写 FROM，但推荐先写 FROM 再写 SELECT，因为符合 SQL 的执行顺序，方便理解。

    如果把从 SELECT 子句开始写的方法称为自顶向下法，那么从 FROM 子句开始写的方法就可以称为自底向上法。

第 3 章 聚合与排序

1、对表进行聚合查询 —— 聚合函数

所谓聚合，就是将多行汇总为一行。

（1）常用聚合函数

  • COUNT：计算表中的记录数（行数）

  • SUM：计算表中数值列中数据的合计值

  • AVG：计算表中数值列中数据的平均值

  • MAX：求出表中任意列中数据的最大值

  • MIN：求出表中任意列中数据的最小值

注意：MAX/MIN 函数和 SUM/AVG 函数有一点不同，那就是 SUM/ AVG 函数只能对数值类型的列使用，而 MAX/MIN 函数原则上可以适用于任何数据类型的列。

（2）注意 NULL

聚合函数普遍会将 NULL 排除在外（ COUNT 函数例外），具体如下：

1、COUNT 函数的结果根据参数的不同而不同。COUNT(*)会得到包含 NULL 的数据行数，而 COUINT(<列名>) 会得到不包含 NULL的数据行数。

SELECT COUNT(DISTINCT <列名>) 可以得到不包含 NULL 且 不重复的数据行数。

2、SUM 函数将 NULL 忽略，也可理解成视为 0。

3、AVG 函数将 NULL 忽略，即不参与分母也不参与分子。

2、对表进行分组 —— GROUP BY

GROUP BY 一般习惯跟聚合函数搭配使用。

（1）进阶 - GROUP BY 与 数学中的 类

  1. 它们全都是非空集合。

  2. 所有子集的并集等于划分之前的集合。

  3. 任何两个子集之间都没有交集。

在数学（群论）中，满足以上3 个性质的各子集称为“类”（partition），将原来的集合分割成若干个类的操作称为“分类”。

所以 GROUP BY 和 下面要介绍的 PARTITION BY 都是用来划分 类 的函数。

问：去重用 GROUP BY 还是 DISTINCT ？

如果用了 GROUP BY 却没用聚合函数，多半是为了去重，但有 DISTINCT 呀：

-- 1、DISTINCT
SELECT DISTINCT "product_type"
FROM "Product"

-- 2、GROUP BY
SELECT "product_type"
FROM "Product"
GROUP BY "product_type"

注意：上面两种结果，都会保留 NULL 行。

答：用 DISTINCT。可读性优先。

常见错误 1 一一 在 SELECT 子句中书写了多余的列

原因：多余的列并没有被聚合，当然无法显示。

解决方案：SELECT 子句中只能存在以下三种元素：

  • 常数

  • 聚合函数

  • GROUP BY 子句中指定的列名（也就是聚合键）

常见错误 2 一一 在 GROUP BY 子句中写了在 SELECT 里指定的列的别名

原因：跟执行顺序有关：FROM→ WHERE→ GROUP BY→ SELECT

解决方案：其实 PostgreSQL 支持这种写法。但推荐为了遵循 标准 SQL ，尽量不要这样写。

常见错误 3 一一 在 WHERE 子句中使用聚合函数

例子：

-- 错误1：
SELECT "product_type", COUNT(*) 
FROM "Product" 
WHERE COUNT(*) = 2 
GROUP BY "product_type"

-- 错误2：
SELECT "product_type", COUNT(*) 
FROM "Product" 
GROUP BY "product_type"
WHERE COUNT(*) = 2

-- 正确：
SELECT "product_type", COUNT(*) 
FROM "Product"  
GROUP BY "product_type"
HAVING COUNT(*) = 2

原因：WHERE 子句不可以使用聚合函数，因为他针对的是行而不是组。

解决方案：请用下面会介绍的 HAVING 子句代替这里的 WHERE。

3、为聚合结果指定条件 —— HAVING

（1）WHERE vs. HAVING

  • WHERE 子句 —— 指定行条件

  • HAVING 子句 —— 指定组条件

例子：

-- WHERE 子句
SELECT *
FROM "Product"
WHERE "product_type" = '体育'

-- HAVING 子句
SELECT "product_type", COUNT(*)
FROM "Product"
GROUP BY "product_type"
HAVING COUNT(*) = 2

-- WHERE + HAVING 子句
SELECT "product_type", COUNT(*)
FROM "Product"
GROUP BY "product_type"
WHERE "product_price" > 10
HAVING COUNT(*) = 2
（2）HAVING 也可不加 GROUP BY

HAVING 不加 GROUP BY（也可认为是对空字段进行了 GROUP BY 操作），整张表会被视为一个组。

SELECT '存在缺失的编号' AS gap
FROM "Product" 
HAVING COUNT(*) <> MAX("product_id");

这种情况下，就不能在SELECT 子句里引用原来的表里的列了，要么就得像示例里一样使用常量，要么就得像 SELECT COUNT(*) 这样使用聚合函数。

类似于使用窗口函数时不指定 PARTITION BY 子句，就是把整个表当作一个窗口来处理。

（3）对聚合键的筛选是放 WHERE 还是 HAVING ？

例子：

-- 1、放在 HAVING
SELECT "product_type", COUNT (*)
FROM "Product"
GROUP BY "product_type" 
HAVING "product_type" <> '衣服'

-- 2、放在 WHERE
SELECT "product_type", COUNT (*)
FROM "Product"
WHERE "product_type" <> '衣服'
GROUP BY "product_type"

结论：放在 WHERE 子句。（能写在 WHERE 子句里的条件就不要写在 HAVING 子句里）

理由：

  • 1、通过 WHERE 子句指定条件时，由于排序之前就对数据进行了过滤，因此能够减少排序的数据量。但 HAVING 子句是在排序之后オ对数据进行分组的，因此与在 WHERE 子句中指定条件比起来，需要排序的数据量就会多得多。

  • 2、可以对 WIHERE 子句指定条件所对应的列创建索引，这样也可以大幅提高处理速度。

    GROUP BY 生成的是派生表，HAVING 无法使用索引。

4、对查询结果进行排序 —— ORDER BY

（1）用法

SELECT 语句末尾添加 ORDER BY 子句来明确指定排列顺序。

select * FROM "Activity"
ORDER BY "id" DESC

  • ASC —— ascendent（上升的）【省略即默认】

  • DESC —— descendent（下降的）

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

注意：ORDER BY 子句中也可以使用聚合函数，跟 SELECT 里一样。

（2）注意 NULL

排序键中包含 NULL 时，会在开头或末尾进行汇总。究竟是在开头显示还是在末尾显示，并没有特殊规定。每家 DBMS 可能不一样。

PostgreSQL 是 ASC 在末尾，DESC 在开头。

第 4 章 数据更新

1、数据的插入 —— INSERT

方法1：使用 VALUES 子句指定具体的数据
INSERT INTO ProductIns 
(product_id, product_name, product_type, sale_price, purchase_price, regist_date) 
VALUES 
('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');

  • 列清单→ (product_id, product_name, product_type, sale_price, purchase_price, regist_date)

  • 值清单→ ('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20')

注意：

1、对表进行全列 INSERT 时，可以省略表名后的列清单。

2、对于指定了默认值的列，可以在列清单和值清单中都省略它，或者仅在值清单里键入 DEFAULT。


拓展：多行插入

原则上，执行一次 INSERT 语句仅会插入一行数据。

但有的 RDBMS 支持 多行 INSERT。

语法即：多行的值清单，用逗号隔开。如下例：

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

该语法适用于 DB2 SQL、SOL Server、Postgresql 和 MYSQL，但不适用于 Oracle。

好处：减少了书写语句的数量，且直观方便理解。

坏处：排错困难。若发生 INSERT 错误，和单一行插入相比，找出到底是哪行哪个地方出错了，变得更加困难。

方法2：从其他表中复制数据 —— INSERT.. SELECT

例如，创建了一个 ProductCopy 表，结构与之前使用的 Product 表完全一样，只是更改了一下表名而己。然后：

-- 将 Product 中的数据复制到 ProductCopy 中
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 中的 SELECT 语句，可以使用 WHERE 子句或者 GROUP BY 子句等任何 SQL 语法（但使用 ORDER BY 子句并不会产生任何效果）。

2、数据的删除 —— DELETE / TRUNCATE

-- 删除表中数据
DELETE FROM Product WHERE sale_price >= 4000;

-- 删除表中所有数据
TRUNCATE Product;

3、数据的更新 —— UPDATE

（1）更新 单列
UPDATE Product
SET sale_price = sale_price * 10
WHERE product_type = '厨房用具';
（2）更新 多列
-- 使用逗号对列进行分隔排列
UPDATE Product
SET sale_price = sale_price * 10,
purchase_price = purchase_price / 2
WHERE product_type = '厨房用具';

-- 将列用()括起来的清单形式 （只能在 Postgresql 和 DB2 中使用)
UPDATE Product
SET (sale_price, purchase_price) = (sale_price * 10, purchase_price / 2)
WHERE product_type = '厨房用具';

4、事务

简单来讲，事务就是需要在同一个处理单元中执行的一系列更新处理的集合。

（1）写法

1、事务开始语句

  • 标准 SQL —— 无

  • SQL Server、PostgreSQL —— BEGIN TRANSACTION

  • MySQL —— START TRANSACTION

  • Oracle、DB2 —— 无


2、DML 语句


3、事务结束语句

COMMIT —— 提交处理

一旦提交，就无法恢复到事务开始前的状态了。

请在执行 DELETE 语句时尤其小心。

ROLLBACK —— 取消处理


4、自动提交模式

自动提交模式 —— 每条 SQL 语句就是一个事务。

几乎所有的数据库产品的事务都默认开启了自动提交模式。

（2）事务的 ACID 特性

DBMS 的事务都遵循四种特性，将这四种特性的首字母结合起来统称为 ACID 特性。这是所有 DBMS 都必须遵守的规则。

1、原子性（Atomicity)

原子性是指在事务结東时，其中所包含的更新处理要么全部执行，要么完全不执行，也就是要么占有一切要么一无所有。

2、一致性（Consistency)

一致性指的是事务中包含的处理要满足数据库提前设置的约束（即从一个正确的状态到另一个正确的状态）。

一致性也称为完整性。

关于一致性的解释，其实网上有好几种版本，更多讨论见：如何理解数据库事务中的一致性的概念？

3、隔离性（Isolation)

隔离性指的是保证不同事务之间互不干扰的特性。

4、持久性（Durability)

持久性也可以称为耐久性，指的是在事务（不论是提交还是回滚）结束后，DBMS 能够保证该时间点的数据状态会被保存的特性。即使由于系统故障导致数据丢失，数据库也一定能通过某种手段进行恢复（如通过执行日志恢复）。

第 5 章 复杂查询

1、视图

（1）视图跟表的区别

使用视图时并不会将数据保存到存储设备之中（正常的表），而且也不会将数据保存到其他任何地方。实际上视图保存的是 SELECT 语句，我们从视图中读取数据时，视图会在内部执行该 SELECT 语句并创建出张临时表。

那么视图和表到底有什么不同呢？区别只有一个，那就是“是否保存了实际的数据”。

（2）优点

1、由于视图无需保存数据，因此可以节省存储设备的容量。

2、由于视图保存的只是 SELECT 语句，因此表中的数据更新之后，视图也会自动更新，非常灵活方便。

3、可以将频繁使用的 SELECT 语句保存成视图，这样就不用每次都重新书写了。

（3）创建视图
CREATE VIEW ProductSum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type;

注意：

1、其实可以在视图的基础上再创建新的视图，但是我们还是应该尽量避免。这是因为对多数 DBMS 来说，多重视图会降低 SQL 的性能。推荐使用仅使用单一视图。

2、定义视图时不能使用 ORDER BY 子句（也没有意义，因为”表“数据本来就没有顺序的概念）。

但在 PostgreSQL 中可以。

（4）使用视图

1、查询 —— SELECT

SELECT product_type, cnt_product
FROM ProductSum;

2、（同步）更新 —— INSERT、DELETE、UPDATE

视图和表会同时进行更新。

注意：通过汇总得到的视图无法进行更新，比如视图存在：

  • SELECT 子句中未使用DISTINCT

  • FROM 子句中只有一张表

  • 未使用 GROUP BY 子句

  • 未使用 HAVING 子句

PostgreSQL 如果要同步更新，需要事先执行一些语句，有点麻烦，这里略过不赘述了。

（5）删除视图
DROP VIEW ProductSum;

-- 如果删除多重视图，可能会因为关联导致删除失败，这时可以使用 CASCADE 
DROP VIEW ProductSum CASCADE;
（6）拓展 - 物化视图

上面的视图有个问题，如果没有经过深入思考就定义复杂的视图，可能会带来巨大的性能问题。特别是视图的定义语句中包含以下运算的时候：

  • 聚合函数（AVG、COUNT、SUM、MIN、MAX）

  • 集合运算符（UNION、INTERSECT、EXCEPT 等）

最近越来越多的数据库为了解决视图的这个缺点，实现了物化视图（materialized view）技术。

PostgreSQL v9.3 才支持。

物化视图既真的是一个实实在在存在的表。

创建方法：

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

其余方法与普通视图类似，不赘述了。

2、子查询

（1）子查询 概述

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

可以理解成一张一次性视图，在 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;

注意：

  • 子查询作为内层查询会首先执行

  • 子查询可以继续嵌套子查询，甚至无限嵌套下去

  • 为子查询设定名称时需要使用 AS 关键字，该关键字有时也可以省略

（2）标量子查询

标量子查询 必须返回表中某一行的某一列的值。

标量就是单一的意思，在数据库之外的领域也经常使用。

应用：由于返回的是单一的值，因此标量子查询可以用在 = 或者 <> 这样需要单一值的比较运算符之中。

-- 错误写法：在 WHERE 子句中不能使用聚合函数
SELECT product_id, product＿name, sale_price
FROM Product
WHERE sale_price > AVG(sale_price);

-- 正确写法
SELECT product_id, product_name, sale_price
FROM Product
WHERE sale_price > (SELECT AVG(sale_price) FROM Product);
（3）关联子查询【重难点】

问：关联子查询 和 非关联子查询的区别：

答：

  • 非关联子查询：先执行内层查询，再执行外层查询

  • 关联子查询：先执行外层查询，再执行内层查询（内层查询必须引用外层查询的变量）


例子：选取出 product_type 商品中高于该类商品的平均销售单价的商品了。

-- 错误写法：因为是 WHERE 比较的值不是标量
SELECT product_id, product_name, sale_price
FROM Product
WHERE sale_price > (
    SELECT AVG(sale_price)
    FROM Product
    GROUP BY product_type
);

-- 正确写法
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 -- 这句可要可不要
); 

-- 错误写法：作用域错误。子查询内部可以看到外部，而外部看不到内部。
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 表达式

为什么把这三块合并成一章，因为 谓词 和 CASE 表达式 本质上也是函数。

1、函数

函数大致可以分为以下几种：

1、算术函数（用来进行数值计算的函数）

2、字符串函数（用来进行字符串操作的函数）

3、日期函数（用来进行日期操作的函数）

4、转换函数（用来转换数据类型和值的函数

5、聚合函数（用来进行数据聚合的函数）

（1）COALESC —— 将 NULL 转换为其他值
SELECT 
    COALESCE ( NULL, 1 ) AS col_1,
    COALESCE ( NULL, 'test', NULL ) AS col_2,
    COALESCE ( NULL, NULL, '2009-11-01' ) AS col_3;

2、谓词（运算符）与 NULL【重难点】

（1）三值逻辑

普通语言里的布尔型只有 true 和 false 两个值，这种逻辑体系被称为二值逻辑。

而 SQL 语言里，除此之外还有第三个值 unknown，这种逻辑体系被称为三值逻辑（three-valued logic）。关系数据库里引进了NULL，所以不得不同时引进第三个布尔值（但是 unknown 值不能被直接引用，直接使用的只能是 NULL）。

历史上最早提出三值逻辑（three-valued-logic）体系的是波兰的著名逻辑学家卢卡西维茨（Jan Lukasiewicz, 1878—1956）。在二十世纪二十年代，他定义了“真”和“假”之外的第三个逻辑值“可能”。

（2）谓词逻辑

谓词逻辑中，原子命题分解成个体词和谓词。 个体词是可以独立存在的事或物，包括现实物、精神物和精神事三种。谓词则是用来刻划个体词的性质的词，即刻画事和物之间的某种关系表现的词。如“苹果”是一个现实物个体词，"苹果可以吃"是一个原子命题，“可以吃”是谓词，刻划“苹果”的一个性质，即与动物或人的一个关系。

所以，在谓词逻辑中，谓词的作用是，“判断（个体词）是否存在满足某种条件”，且返回真值（在三值逻辑里，即 TRUE/ FALSE/ UNKNOWN）。

在逻辑中，真值（truth value），又称逻辑值（logical value），是指示一个陈述在什么程度上是真的。在计算机编程上多称做布林值、布尔值。

拓展 —— 排中律（Law of Excluded Middle）就是指不认可中间状态，对命题真伪的判定黑白分明。是否承认这一定律被认为是古典逻辑学和非古典逻辑学的分界线。如，约翰的年龄，在现实世界中，“要么是20 岁，要么不是20 岁”——这样的常识在三值逻辑里却未必正确，也有可能未知，即 unknown。故，在 SQL 的世界里，排中律是不成立的。

谓词逻辑的出现具有划时代的意义，原因就在于为命题分析提供了函数式的方法。所以谓词可以通俗理解为函数，区别在于返回值：

  • 函数的返回值有可能是数字、字符串或者日期等

  • 谓词的返回值全都是真值

（3）谓词逻辑在 SQL 中的应用

表常常被认为是行的集合，但从谓词逻辑的观点看，也可以认为是命题的集合。

同样，如 WHERE 子句，其实也可以看成是由多个谓词组合而成的新谓词。只有能让WHERE 子句的返回值为真的命题，才能从表（命题的集合）中查询到。

（4）SQL 中的谓词 —— 比较谓词

=、<>、>=、>、<=、<

不等于也可以写作 != ，但是为了兼容性，还是推荐使用 标准sql 里的 <>。

（5）SQL 中的谓词 —— 其他谓词

1、LIKE


2、BETWEEN

如：WHERE sale_price BETWEEN 100 AND 1000;

左闭右闭


3、IS NULL、IS NOT NULL

判断是否为 NULL 就不要用 <> 了，而是用这个。


4、限定谓词 - IN （ANY）

IN 是多个 OR 的简便用法，如 "col" IN (320, 500, 5000); 或 200 IN ("col1", "col2", "col3");

IN 还有个别称叫 ANY，为了跟下面的 ALL 对应。


5、限定谓词 - ALL

ALL 是 多个AND 的简便用法，如 "col" ALL (320, 500, 5000); 或 200 ALL ("col1", "col2", "col3");

拓展：推荐使用极值函数代替 ALL

SELECT *
FROM Class_A
WHERE age < ( 
    SELECT MIN(age)
    FROM Class_B
    WHERE city = '东京' 
);

推荐原因：极值函数在统计时会把为 NULL 的数据排除掉，避免出错。

ALL 跟 极值函数在语义上还是有细微区别的：

● ALL 谓词：他的年龄比在东京住的所有学生都小
● 极值函数：他的年龄比在东京住的年龄最小的学生还要小

但是极值函数也有隐患，极值函数（聚合函数）在输入为空表（空集）时会返回 NULL。

建议：使用 COALESCE 函数将极值函数返回的 NULL 处理成合适的值。

很像 lodash 的 get 方法，给个返回的默认值。


6、EXISTS

例子：有 Product 产品表 和 ShopProduct 店铺表，选取出“大阪店（shop_id:000C）在售商品的销售单价”。

-- IN 写法
SELECT product_name, sale_price
FROM Product WHERE product_id IN (    
    SELECT product_id    
    FROM ShopProduct      
    WHERE shop_id = '000C'
);

-- EXISTS 写法 [推荐]
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
);

-- NOT EXISTS 写法 —— “东京店（shop_id:000A）在售之外的商品的销售单价”
SELECT product_name, sale_price
FROM Product AS P  
WHERE NOT EXISTS (    
    SELECT *    
    FROM ShopProduct AS SP     
    WHERE SP.shop_id = '000A'    
    AND SP.product_id = P.product_id
);

注意：① 这里的 SELECT * ，返回哪些列都没有关系（当然惯例还是用 * 最好），因为 EXIST 只关心记录是否存在。

拓展：NOT EXISTS 具备有差集运算的功能。


7、NOT、AND、OR

NOT 运算符用来否定某一条件，但是不能滥用。否则会降低可读性。

（6）拓展 - N 阶谓词的划分

谓词逻辑中，根据输入值的阶数（order）对谓词进行分类。

= 或者 BETWEEEN 等大多数输入值为一行的谓词叫作“一阶谓词”，

而像 IN（ANY）、ALL 、EXISTS 还有 HAVING 这样输入值为行的集合的谓词叫作“二阶谓词”。

二阶谓词一般都习惯跟 关联子查询 搭配使用。

二阶谓词，如 IN 和 EXISTS 和 HAVING 在很多情况下都是可以互换的，

三阶谓词＝输入值为“集合的集合”的谓词

四阶谓词＝输入值为“集合的集合的集合”的谓词

我们可以像上面这样无限地扩展阶数，但是SQL 里并不会出现三阶以上的情况，所以不用太在意。

使用过List、Hakell 等函数式语言或者Java 的读者可能知道“高阶函数”这一概念。它指的是不以一般的原子性的值为参数，而以函数为参数的函数。

（7）运算符

上面从谓词的角度分类，这里我们按照运算符的角度来划分的话：

1、算术运算符

+、-、*、/、%


2、比较运算符

即上面介绍的 比较谓词。


3、逻辑运算符

即上面介绍的 其他谓词。


4、其他运算符

||：拼接字符串


运算符的优先级：（圆括号）> 算术运算符 > 比较运算符 > 逻辑运算符。

其中，逻辑运算符中的优先级：NOT > AND > OR。

（8）特殊的 NULL

1、NULL 不是值

NULL 容易被认为是值的原因恐怕有两个。

第一个是在 C 语言等编程语言里面，NULL 被定义为了一个常量（很多语言将其定义为了整数0），这导致了人们的混淆。但是，其实 SQL 里的 NULL 和其他编程语言里的 NULL 是完全不同的东西。

第二个原因是，IS NULL 这样的谓词是由两个单词构成的，所以人们容易把 IS 当作谓词，而把 NULL 当作值。我们应该把 IS NULL 看作是一个谓词。因此，如果可以的话，写成 IS_NULL 这样也许更合适。


2、因为 NULL 不是值，所以常见的对 NULL 的说法也是错的：“列的值为NULL”、“NULL 值”……


3、算术运算符 遇上 NULL 结果都是 NULL


4、比较运算符 和 逻辑运算符 遇上 NULL 结果基本上是 unknown，或者说，对 NULL 使用谓词后的结果基本上是 unknown。

为什么说基本上？ 因为有特殊情况，如遇到 OR 和 AND，还是会分别出现结果是 TRUE 和 FALSE 的；或者 EXIST

具体参考下面的真值表：

三值逻辑的真值表（AND）

AND t u f
t t u f
u u u f
f f f f

三值逻辑的真值表（OR）

OR t u f
t t t t
u t u u
f t u f
（9）避免使用 NULL

从上面的叙述，你可以看出 NULL 是有多么特殊和多么容易引起错误了。

NULL 最恐怖的地方就在于即使你认为自己已经完全驾驭它了，但还是一不小心就会被它在背后捅一刀。

1、避免使用的方法

  • 加上 NOT NULL 约束

  • 使用默认值

  • 编号：使用异常编号

    例如 ISO 的性别编号中，除了 “1: 男性”，“2: 女性”，还定义了 “0: 未知”，“9: 不适用” 这两个用于异

    常情况的编号。

  • 名字：使用“无名氏”

    例如名字用 “未知” or “UNKNOWN” 代替，类别用"-"代替。

  • 数值：使用 0

  • 日期：用最大值或最小值代替

    例如开始日期和结束日期，可以使用 0000-01-01 或者 9999-12-31。


2、但你无法100%避免

无法完全消除 NULL 的原因是它扎根于关系数据库的底层中。仅靠上面提到的方法并不足够。

例如，使用外连接，或者 SQL-99 中添加的带 CUBE 或 ROLLUP 的 GROUP BY 时，还是很容易引入 NULL 的。


3、结论

因此我们能做的最多也只是 “尽量”去避免 NULL 的产生，并在不得不使用时适当使用。

（10）拓展 —— EXISTS vs. IN

注意：由于有 NULL 捣鬼，所以 IN 会返回 true / fasle / unknown，而 EXISTS 只会返回 true / false。因此，IN 和 EXISTS 可以互相替换使用，而 NOT IN 和NOT EXISTS 却不可以。

具体原因可以回去翻阅原书，这里不赘述。


问：那参数是子查询时，用 IN 还是 EXISTS 更好呢？

-- IN
SELECT *
FROM Class_A
WHERE id IN 
(
    SELECT id
    FROM Class_B
);

-- EXISTS
SELECT *
FROM Class_A A
WHERE EXISTS
(
    SELECT *
    FROM Class_B B
    WHERE A.id = B.id
);

如果把上例的 Class_A 看成外表，Class_B 看成内表的话。

答：

维度一：从外表和内表的数据行大小的关系来看

1、IN 只执行一次，此内表查出后就缓存了，所以 IN 适合 外表 > 内表 的情况；

2、EXISTS 是针对外表去作循环，每次循环会跟内表作关联子查询，所以 EXISTS 适合 外表 < 内表 的情况；

3、当 内外表 数据差不多大时，IN 与 EXISTS 也差不多。


维度二：索引的角度

EXISTS 可以用到索引，而 IN 不行。所以 EXISTS 更佳。


维度三： 是否全表遍历

针对内表，EXISTS 只要查到一行数据满足条件就会终止遍历，而 IN 必须遍历整个内表。 所以 EXISTS 更佳。


维度四： 可读性

IN 更佳。


综上所述：还是考虑实际情况。但是 EXISTS 替代 IN 提高性能的可能性更大。

1、要想改善 IN 的性能，除了使用 EXISTS，还可以使用连接。

2、最近有很多数据库也尝试着改善了 IN 的性能。例如，在 Oracle 数据库中，如果我们使用了建有索引的列，那么即使使用 IN 也会先扫描索引；PostgreSQL 从版本 7.4 起也改善了使用子查询作为 IN 谓词参数时的查询速度。


注意：其实这个问题也可以当成 非关联子查询 vs. 关联子查询 来看待（除了维度三是 EXISTS 特有的优势外，其他的维度都适用）。

3、CASE 表达式

CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种。

由于 搜索CASE 表达式 包含了 简单CASE 表达式 的全部功能，所以有更强大的表达能力。

注意：CASE 表达式是一种表达式而不是语句，CASE 表达式经常会因为同编程语言里的 CASE 混淆而被叫作 CASE 语句，其实是不对的。（你也可以把 CASE 表达式理解成一种函数，运行后会返回值）

编程语言中的 CASE 语句，还有 break 的概念，而 SQL 中的 CASE 表达式没有。


例子：

-- 使用 搜索CASE表达式 的情况【推荐】
SELECT product_name,
CASE 
WHEN product_type = '衣服'
THEN 'A ：' || product_type
WHEN product_type = '办公用品'
THEN 'B ：' || product_type
WHEN product_type = '厨房用具'
THEN 'C ：' || product_type
ELSE NULL
END AS abc_product_type
FROM Product;

-- 使用 简单CASE表达式 的情况
SELECT product_name,
CASE product_type
WHEN '衣服' THEN 'A ：' || product_type
WHEN '办公用品' THEN 'B ：' || product_type
WHEN '厨房用具' THEN 'C ：' || product_type
ELSE NULL
END AS abc_product_type
FROM Product;

注意：

1、统一各分支返回的数据类型。例如用 CAST 函数。

2、ELSE 子句可以省略不写，这时会被默认为 ELSE NULL。但还是建议 养成写 ELSE 子句的习惯，减少失误。

3、记得写 END 。

4、WHEN NULL 错误，WHEN IS NULL正确。

案例 1、用一条 SQL 语句进行不同条件的统计

统计不同县的男女比例（“县名”的列为：pref_name，“人口”的列为：population）

-- == old 写法：

-- 男性人口
SELECT pref_name,
SUM(population)
FROM PopTbl2
WHERE sex = '1'
GROUP BY pref_name;
-- 女性人口
SELECT pref_name,
SUM(population)
FROM PopTbl2
WHERE sex = '2'
GROUP BY pref_name;

-- == new 写法：

SELECT pref_name,
-- 男性人口
SUM( CASE WHEN sex = '1' THEN population ELSE 0 END) AS cnt_m,
-- 女性人口
SUM( CASE WHEN sex = '2' THEN population ELSE 0 END) AS cnt_f
FROM PopTbl2
GROUP BY pref_name;

新手用 WHERE 子句进行条件分支，高手用 SELECT 子句进行条件分支。

案例 2、在 CASE 表达式中使用聚合函数

std_id ( 学号) 、club_id ( 社团ID) 、club_name ( 社团名) 、main_club_flg ( 主社团标志)

  1. 获取只加入了一个社团的学生的社团ID。

  2. 获取加入了多个社团的学生的主社团ID。

-- == old 写法：

-- 条件1 ：选择只加入了一个社团的学生
SELECT std_id, MAX(club_id) AS main_club
FROM StudentClub
GROUP BY std_id
HAVING COUNT(*) = 1;

-- 条件2 ：选择加入了多个社团的学生
SELECT std_id, club_id AS main_club
FROM StudentClub
WHERE main_club_flg = 'Y' ;

-- == new 写法：

SELECT std_id,
CASE 
WHEN COUNT(*) = 1 THEN MAX(club_id)
ELSE MAX(
    CASE 
    WHEN main_club_flg = 'Y' THEN club_id
    ELSE NULL 
    END
)
END AS main_club
FROM StudentClub
GROUP BY std_id;

新手用 HAVING 子句进行条件分支，高手用 SELECT 子句进行条件分支。

案例 3、用 CHECK 约束定义多个列的条件关系

假设某公司规定 “女性员工的工资必须在 20 万日元以下”

CONSTRAINT check_salary CHECK
( 
    CASE WHEN sex = '2' THEN 
        CASE WHEN salary <= 200000 THEN 1 
        ELSE 0 
        END
    ELSE 1 
    END = 1 
)
案例 4、在 UPDATE 语句里进行条件分支，避免多次循环更新的出错

例子1：多次循环更新

例如你要：

  1. 对当前工资为 30 万日元以上的员工，降薪 10%。

  2. 对当前工资为 25 万日元以上且不满 28 万日元的员工，加薪 20%。

-- 错误写法：（问题在于，第一次的 UPDATE 操作执行后，“当前工资”发生了变化，如果还用它当作第二次 UPDATE 的判定条件，结果就会出错。）

UPDATE Salaries
SET salary = salary * 0.9
WHERE salary >= 300000;

UPDATE Salaries
SET salary = salary * 1.2
WHERE salary >= 250000 AND salary < 280000;

-- 正确写法：

UPDATE Salaries
SET salary = 
    CASE 
    WHEN salary >= 300000 THEN salary * 0.9
    WHEN salary >= 250000 AND salary < 280000 THEN salary * 1.2
    ELSE salary -- 注意这里的 `ELSE salary` 非常重要
    END;

例子2：两个值交换（替代传统的使用中间值的做法）

UPDATE SomeTable
SET p_key =
    CASE WHEN p_key = 'a' THEN 'b'
    WHEN p_key = 'b' THEN 'a'
    ELSE p_key
    END
WHERE p_key IN ('a', 'b');

第 7 章 集合运算【重难点】

面向对象语言以对象的方式来描述世界，而面向集合语言SQL 以集合的方式来描述世界。

1、集合运算符（以集合为单位）

  • 表的加法（并集） —— UNION （UNION ALL）

  • 表的减法（差集） —— EXCEPT （EXCEPT ALL）

  • 表的（交集） —— INTERSECT （INTERSECT ALL）

表的乘法、除法下面会提到。

上面运算符后加了 ALL 的表示算出结果后，不会除去重复记录。

加了 ALL 就不会为了除去重复行而发生排序，所以性能会有提升。


注意事项 ① —— 作为运算对象的记录的列数必须相同

注意事项 ② —— 作为运算对象的记录中列的类型必须一致

注意事项 ③ —— ORDER BY 子句只能在最最后使用一次

注意事项 ④ —— UNION 和 INTERSECT 都具有幂等性，而 EXCEPT 不具有

（1）应用 - 删除重复行
-- 方法一 ： 通过 集合运算符 EXCEPT 求补集
DELETE FROM Products
WHERE rowid IN ( 
    SELECT rowid -- 全部rowid
    FROM Products
    
    EXCEPT -- 减去
    
    SELECT MAX(rowid) -- 要留下的rowid
    FROM Products
    GROUP BY name, price
);

-- 方法二 ： 或者省略集合运算符 EXCEPT ，直接通过 NOT IN 求补集
DELETE FROM Products
WHERE rowid NOT IN ( 
    SELECT MAX(rowid)
    FROM Products
    GROUP BY name, price
);

2、联结（以列为单位）

联结其实属于 表的乘法（笛卡尔积）。

（1）内联结（INNER JOIN）

它是应用最广泛的联结。

例子：

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;
（2）外联结 （OUTER JOIN）

外联结分：

  • LEFT OUTER JOIN —— 简写 LEFT JOIN

  • RIGHT OUTER JOIN —— 简写 RIGHT JOIN

外联结指定主表的关键字是 LEFT 和 RIGHT。最终的结果中会包含主表的所有数据。

平时还是习惯用左联结多一些。左联结有一个优势：一般情况下表头都出现在左边（笔者没遇见过表头出现在右边的情况）。使用左边的表作为主表的话，SQL 就能和执行结果在格式上保持一致。这样一来，在看到 SQL 语句时，我们很容易就能想象出执行结果的格式。

（3）交叉联结（CROSS JOIN）

例子：

SELECT P1.name AS name_1, P2.name AS name_2
FROM Products P1 CROSS JOIN Products P2;

-- 旧写法
SELECT P1.name AS name_1, P2.name AS name_2
FROM Products P1, Products P2;

进行交叉联结时无法使用内联结和外联结中所使用的 ON 子句，这是因为交叉联结是对两张表中的全部记录进行交叉组合，因此结果中的记录数通常是两张表行数的乘积（笛卡儿积）。

交叉联结在实际业务中几乎并不会使用，那为什么还要在这里进行介绍呢？这是因为交叉联结是所有联结
运算的基础。内联结是交叉联结的一部分，“内”也可以理解为“包含在交叉联结结果中的部分”。相反，外联结的“外”可以理解为“交叉联结结果之外的部分”。

（4）全外联结（FULL OUTER JOIN）

全外联结 = 左外联结 UNION 右外联结

全外联结是能够从这样两张内容不一致的表里，没有遗漏地获取全部信息的方法，所以也可以理解成“把两张表都当作主表来使用”的连接。

-- 全外联结
SELECT COALESCE(A.id, B.id) AS id,
A.name AS A_name,
B.name AS B_name
FROM Class_A A FULL OUTER JOIN Class_B B
ON A.id = B.id;

-- 数据库不支持全外联结时的替代方案
SELECT A.id AS id, A.name, B.name
FROM Class_A A LEFT OUTER JOIN Class_B B
ON A.id = B.id

UNION

SELECT B.id AS id, A.name, B.name
FROM Class_A A RIGHT OUTER JOIN Class_B B
ON A.id = B.id;

拓展：A 和 B 的异或

一种是 (A UNION B) EXCEPT (A INTERSECT B)，另一种是 (A EXCEPT B) UNION (B EXCEPT A)。

两种方法都比较麻烦，性能开销也大。建议用 FULL OUTER JOIN 来做：

SELECT COALESCE(A.id, B.id) AS id,
COALESCE(A.name , B.name ) AS name
FROM Class_A A FULL OUTER JOIN Class_B B
ON A.id = B.id
WHERE A.name IS NULL OR B.name IS NULL;
（5）多表联结
-- ……
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

现在是 3 张表，即使把联结的表增加到 4 张、5 张以上也是完全相同的写法。

3、集合的除法

截至目前并没有 DBMS 实现集合的除法。

因此，必须自己实现。方法比较多，其中具有代表性的：

  1. 嵌套使用 NOT EXISTS。

  2. 使用 HAVING 子句转换成一对一关系。

  3. 把除法变成减法。

4、进阶 - 自连接 与 非等值连接

（1）自连接

针对相同的表进行的连接被称为“自连接”（self join）。

原书 《SQL 基础教程》里都叫 xx 联结，到《SQL 进阶教程》又都变成了 xx 连接。

可见 联结 和 连接 可以通用。 本文又跟着使用混乱，请谅解。

应用1：可重排列、去重排列、组合

假如有 Products 表：

name（商品名称） price（价格）
苹果    50
橘子    100
香蕉    80

1、可重排列

SELECT P1.name AS name_1, P2.name AS name_2
FROM Products P1, Products P2;

结果：

name_1 name_2

苹果 苹果
苹果 橘子
苹果 香蕉
橘子 苹果
橘子 橘子
橘子 香蕉
香蕉 苹果
香蕉 橘子
香蕉 香蕉

2、去重排列（考虑顺序，即有序对）

SELECT P1.name AS name_1, P2.name AS name_2
FROM Products P1, Products P2
WHERE P1.name <> P2.name;

结果：

name_1 name_2

苹果 橘子

苹果 香蕉

橘子 苹果

橘子 香蕉

香蕉 苹果

香蕉 橘子

3、去重排列（不考虑顺序，即无序对）

SELECT P1.name AS name_1, P2.name AS name_2
FROM Products P1, Products P2
WHERE P1.name > P2.name;

结果：

name_1 name_2

苹果 橘子
香蕉 橘子
香蕉 苹果

4、去重排列（不考虑顺序，即无序对）且 扩展成 3 列

SELECT P1.name AS name_1, P2.name AS name_2, P3.name AS name_3
FROM Products P1, Products P2, Products P3
WHERE P1.name > P2.name
AND P2.name > P3.name;

结果：

name_1 name_2

香蕉 苹果 橘子

（2）非等值连接

上面 应用1 里的 2、3、4 都是使用除 “＝” 以外的其他比较运算符，如 “<、>、<>”，这样进行的连接称为 "非等值连接"。

应用2：删除重复行

假如有个 Products 表，有 name 和 price 两列：


方法一：关联子查询

需要使用由数据库独自实现的行ID。

例如， Oracle 数据库里的 rowid，或者 PostgreSQL 里的 ctid 。

DELETE FROM Products P1
WHERE rowid < ( 
    SELECT MAX(P2.rowid)
    FROM Products P2
    WHERE P1.name = P2.name
    AND P1.price = P2.price 
);

方法二：EXISTS（关联子查询） + 非等值连接

DELETE FROM Products P1
WHERE EXISTS ( 
    SELECT *
    FROM Products P2
    WHERE P1.name = P2.name
    AND P1.price = P2.price
    AND P1.rowid < P2.rowid 
);
应用3：查找局部不一致的列

假如有个 Products 表，有 name 和 price 两列：

从 Products 表里查找价格相等但商品名称不同的记录

SELECT DISTINCT P1.name, P1.price
FROM Products P1, Products P2
WHERE P1.price = P2.price
AND P1.name <> P2.name;
应用4：排序

方法一：用窗口函数

SELECT name, price,
RANK() OVER (ORDER BY price DESC) AS rank_1,
DENSE_RANK() OVER (ORDER BY price DESC) AS rank_2
FROM Products;

方法二：自连接 + 非等值连接

-- 排序从 1 开始。如果已出现相同位次，则跳过之后的位次


-- 1、关联子查询
SELECT P1.name, P1.price,
(
    SELECT COUNT(P2.price)
    FROM Products P2
    WHERE P2.price > P1.price
) + 1 AS rank_1
FROM Products P1
ORDER BY rank_1;

-- 2、表的连接
SELECT P1.name, 
MAX(P1.price) AS price,
COUNT(P2.name) +1 AS rank_1
FROM Products P1 LEFT OUTER JOIN Products P2
ON P1.price < P2.price
GROUP BY P1.name
ORDER BY rank_1;

此处蕴含了递归集合的思想。

第 8 章 SQL 高级处理

本章介绍的 窗口函数 和 GROUPING 运算符都是为了实现 OLAP 用途而添加的功能，是 SQL 里比较新的功能。

截止到 2016 年 5 月，Oracle、SQL Server、DB2、PostgreSQL 的最新版本都已经支持这些功能了，但MySQL 的最新版本 5.7 还是不支持这些功能。

OLAP 是 OnLine Analytical Processing 的简称，意思是对数据库数据进行实时分析处理，用来诸如生成报表。例如，市场分析、创建财务报表、创建计划等日常性商务工作。

1、窗口函数

下面会结合原书 + 我之前的一篇文章《 PostgreSQL 窗口函数 ( Window Functions ) 如何使用？》+ 自己的理解，梳理下。

（1）窗口函数和聚合的区别

窗口函数跟聚合还是挺像的，但区别是：

窗口函数不会像聚合一样将参与计算的行合并成一行输出，而是将计算出来的结果带回到了计算行上。

（2）用法

完整示例：

SELECT "product_name", "product_type", "sale_price",
AVG ("sale_price") OVER ( 
    PARTITION BY "product_type"
    ORDER BY "sale_price" 
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS "avg"
FROM "Product";

1、AVG (sale_price) 为窗口函数。

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

  • 所有的聚合函数都能用作窗口函数，如（SUM、AVG、COUNT、MAX、MIN）

  • RANK、DENSE_RANK、ROW_NUMBER 等专用窗口函数

问：专用窗口函数 跟 聚合函数 的用法区别 ？

答：

  • 由于专用窗口函数无需参数，因此通常括号中都是空的。而聚合函数一般都需要传参来指定列名。

  • 原则上窗口函数只能在 SELECT 子句中使用。其理由是，在 DBMS 内部，窗口函数是对 WHERE 子句或者 GROUP BY 子句处理后的“结果”进行的操作。


2、PARTITION BY "product_type" 的 PARTITION BY，类似于 GROUP BY。通过 PARTITION BY 分组后的记录集合称为窗口。此处的窗口并非“窗户”的意思，而是代表范围。

PARTITION BY 可以省略，代表全部记录集合为一个窗口。


3、ORDER BY "sale_price" 的 ORDER BY，是在窗口函数调用前，先把每个窗口内的记录集合排序。

问：为什么用 GROUP BY 的时候不需要加 ORDER BY ？

答：因为跟 GROUP BY 一起使用的聚合函数针对的记录集合是每一个分组，排不排序不影响最终结果，而窗口函数针对的记录集合是每一个窗口里的子范围（这个子范围即”框架“，下面即将介绍 ），所以排序很关键。

ORDER BY 可以省略，即默认排序。


4、ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 中，ROWS 用来定义窗口内的（行）范围，称为框架。

有三种写法：

① ROWS 2 PRECEDING -- 之前

② ROWS 2 FOLLOWING -- 之后

③ ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING -- 之间

可用 UNBOUNDED 代替数字表示无边界。

以 ① 为例，ROWS 2 PRECEDING 就是将窗口内的范围指定为“截止到之前 2 行”，也就是将作为汇总对象的记录限定为如下的 “最靠近的 3 行”：

● 自身（当前记录）

● 之前1行的记录

● 之前2行的记录

这样的统计方法称为移动平均（moving average）。

由于这种方法在希望实时把握“最近状态”时非常方便，因此常常会应用在对股市趋势的实时跟踪当中。

ROWS 可以省略，默认值为：

  • 若不指定 ORDER BY，默认使用窗口内所有行，等于 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  • 若指定了 ORDER BY，默认使用窗口内第一行到当前值 ，等于ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
（3）应用场景

例子1（用于累计）：

SELECT product_id, product_name, sale_price,
AVG (sale_price) OVER (ORDER BY product_id) AS current_avg
FROM Product;

结果：

product_id | product_name | sale_price | current_avg
----------+-----------+-------------+-----------------------
0001 | T恤衫 | 1000 | 1000.0000000000000000 ←(1000)/1
0002 | 打孔器 | 500 | 750.0000000000000000 ←(1000+500)/2
0003 | 运动T恤 | 4000 | 1833.3333333333333333 ←(1000+500+4000)/3
0004 | 菜刀 | 3000 | 2125.0000000000000000 ←(1000+500+4000+3000)/4
0005 | 高压锅 | 6800 | 3060.0000000000000000 ←(1000+500+4000+3000+6800)/5
0006 | 叉子 | 500 | 2633.3333333333333333
0007 | 擦菜板 | 880 | 2382.8571428571428571
0008 | 圆珠笔 | 100 | 2097.5000000000000000

例子2（用于排名）：

先介绍 3 个专用窗口函数，用来排名的。

  • 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;

结果：

product_name | product_type | sale_price | ranking | dense_ranking | row_num

圆珠笔 | 办公用品 | 100 | 1 | 1 | 1

叉子 | 厨房用具 | 500 | 2 | 2 | 2

打孔器 | 办公用品 | 500 | 2 | 2 | 3

擦菜板 | 厨房用具 | 880 | 4 | 3 | 4

T恤衫 | 衣服 | 1000 | 5 | 4 | 5

菜刀 | 厨房用具 | 3000 | 6 | 5 | 6

运动T恤 | 衣服 | 4000 | 7 | 6 | 7

高压锅 | 厨房用具 | 6800 | 8 | 7 | 8

上面的 累计 和 排名，本质上都属于同一种计算逻辑，即冯·诺依曼型递归集。

（4）提取 OVER 变量

如果在 SQL 里写了很多重复的 OVER()，可以提取成一个 window 变量，简化代码。

SELECT *, 
    avg("score") OVER window_frame as "subject_avg_score",
    avg("score") OVER window_frame as "subject_avg_score_2",
    avg("score") OVER window_frame as "subject_avg_score_3"
FROM "testScore" 
window window_frame as (PARTITION BY "subject")

2、GROUPING 运算符

1、ROLLUPーー同时得出合计和小计 ( GROUPING 函数ーー让 NULL 更加容易分辨 )

ROLLUP 可以用来同时得出合计和小计。而避免用 UNION 繁琐的方式。

（1）只用 ROLLUP
SELECT product_type, regist_date, SUM(sale_price) AS sum_price
FROM Product
GROUP BY ROLLUP(product_type, regist_date);

结果：

product_type regist_date sum_price
16780
厨房用具 11180
厨房用具 2008-04-28 880
厨房用具 2009-01-15 6800
厨房用具 2009-09-20 3500
办公用品 600
办公用品 2009-09-11 500
办公用品 2009-11-11 100
衣服 5000
衣服 2009-09-20 1000
衣服 4000

GROUP BY ROLLUP (product_type, regist_date); 的结果等于：

① GROUP BY ()

② GROUP BY (product_type)

③ GROUP BY (product_type, regist_date)

三者的 UNION。

其中 ① 中的 GROUP BY () 表示没有聚合键，也就相当于没有 GROUP BY 子句（这时会得到全部数据的合计行的记录）。

上面结果中，第 1、2、3、7、10、12 行称为超级分组记录（super group row）。

（2）用 ROLLUP + GROUPING
SELECT

 CASE WHEN GROUPING(product_type) = 1
THEN '商品种类 合计'
ELSE product_type END AS product_type,

 CASE WHEN GROUPING(regist_date) = 1
THEN '登记日期 合计'
ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,

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

上面 （1）只用 ROLLUP 的例子，超级分组记录都存在 null 数据的情况，为了避免阅读的混淆，SQL 提供了一个用来判断超级分组记录的 NULL 的特定函数—— GROUPING 函数。该函数在其参数列的值为超级分组记录所产生的 NULL 时返回 1 ，其他情况返回 0。


结果：

product_type regist_date sum_price
商品种类 合计 登记日期 合计 16780
厨房用具 登记日期 合计 11180
厨房用具 2008-04-28 880
厨房用具 2009-01-15 6800
厨房用具 2009-09-20 3500
办公用品 登记日期 合计 600
办公用品 2009-09-11 500
办公用品 2009-11-11 100
衣服 登记日期 合计 5000
衣服 2009-09-20 1000
衣服 4000
2、CUBE——用数据来搭积木

上面 （2）用 ROLLUP + GROUPING 的例子，直接把 ROLLUP 改写成 CUBE 就行：

SELECT

 CASE WHEN GROUPING(product_type) = 1
THEN '商品种类 合计'
ELSE product_type END AS product_type,

 CASE WHEN GROUPING(regist_date) = 1
THEN '登记日期 合计'
ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,

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

GROUP BY CUBE (product_type, regist_date); 的结果等于

① GROUP BY ()

② GROUP BY (product_type)

③ GROUP BY (regist_date) ←新添的组合

④ GROUP BY (product_type, regist_date)

三者的 UNION。

CUBE 生成的 GROUP BY 组合，是 2 的 n 次方（n 是聚合键的个数）。

这就是 CUBE 如此起名的由来。


结果（第3-8行是比之前多出来的）：

product_type regist_date sum_price
商品种类 合计 登记日期 合计 16780
商品种类 合计 2008-04-28 880
商品种类 合计 2009-01-15 6800
商品种类 合计 2009-09-11 500
商品种类 合计 2009-09-20 4500
商品种类 合计 2009-11-11 100
商品种类 合计 4000
厨房用具 登记日期 合计 11180
厨房用具 2008-04-28 880
厨房用具 2009-01-15 6800
厨房用具 2009-09-20 3500
办公用品 登记日期 合计 600
办公用品 2009-09-11 500
办公用品 2009-11-11 100
衣服 登记日期 合计 5000
衣服 2009-09-20 1000
衣服 4000
3、GROUPING SETS——取得期望的积木

因为 GROUPING SETS 会获得不固定结果，因此与 ROLLUP 或者CUBE 比起来，使用GROUPING SETS 的机会很少。

这里姑且略过。

第 9 章 通过应用程序连接数据库

1、驱动

驱动就是应用和数据库这两个世界之间的桥梁。

2、驱动标准

现在广泛使用的驱动标准主要有 ODBC（Open DataBase Connectivity）和 JDBC（Java Data Base Connectivity）两种。ODBC 是1992 年微软公司发布的 DBMS 连接标准，后来逐步成为了业界标准。JDBC 是在此基础上制定出来的 Java 应用连接标准。

3、PostgreSQL <=> Node.js

（1）针对 PostgreSQL 的驱动

以 PostgreSQL 为例，它的官网列有针对各种编程语言（应用）的驱动：

https://www.postgresql.org/docs/current/external-interfaces.html

分类：

1、使用纯语言实现的 Postgresql 驱动，如 JDBC 等方式。这种连接方式不需要 libpq 库。

2、通过包装 PostgreSQL 的 C 语言接口库 libpg 实现的驱动，比如，Python 下的 psycopg 库、ODBC 、（下面要介绍的） node-postgres 等。所以在安装这些驱动之前，需要先安装 PostgreSQL 的 libpq 库。

（2）node-postgres

在上面的官网资料中可以查到，node-postgres 是针对 Node.js 的驱动，官网：https://node-postgres.com/

安装：$ npm install pg

（3）Sequelize

Node.js 应用一般不直接用 node-postgres，而常用 sequelize，但 sequelize 本质也是对 node-postgres 等一些驱动的封装。

正如 sequelize 的安装步骤：

1、先安装 sequelize

npm install --save sequelize

2、为所选数据库安装驱动程序：

# One of the following:
$ npm install --save pg pg-hstore # Postgres
$ npm install --save mysql2
$ npm install --save mariadb
$ npm install --save sqlite3
$ npm install --save tedious # Microsoft SQL Server

十、安全

1、SQL注入（SQL injection）

有效的防御方法，就是全面改用参数化查询。

参数化查询的原理是预处理，先将 SQL 语句进行编译，这样注入的数据就不会被当做 SQL 语句执行，而只当做 参数值 来处理。

更多讨论见：知乎 - 为什么参数化SQL查询可以防止SQL注入?

十一、进阶 - 关系数据库的理论世界

1、关系模型 与 关系

（1）诞生

埃德加·弗兰克·科德（英语：Edgar Frank Codd， 1923年8月23日－2003年4月18日），下简称 Codd，是关系模型（Relational model）和关系数据库的祖师爷。

数据库因采用了关系模型，才被称为关系数据库。

Codd 写了两篇与关系模型相关的论文。第一篇是写于1969年的《大型数据库中关系存储的可推导性、冗余与一致性》。遗憾的是这篇论文发表在 IBM 公司内部期刊 IBM Research Report 上了，因此并没有引起外界的注意。
在接下来的 1970 年，Codd 又在权威学术杂志 Communications of ACM 上，以《大型共享数据库的关系模型》为题发表了第二篇论文。至此，关系模型真正地问世了。现在人们读到的论文基本上都是这一篇。但是，就像 C.J. Date 说的那样，这篇论文充满了学术味道，而且比较偏重理论和数学，所以即使是数据库方面的专家，一般也不会去阅读。

后来，Codd 凭借在关系型数据库方面的贡献获得了 1981 年的图灵奖。

（2）什么是关系 and 关系模型

① 关系

关系：两个不同数据域上的值的集合通过一定的条件得到一个所有可能组合的子集。

比如，一个包含所有棒球队名字的集合，和一个包含所有城市的集合。将每个城市和球队的组合都列出来，这个列表可以很长很长。但我们只关注这个列表的一个子集：球队和其所属城市的组合。有效的组合包括 Chicago/White Sox、Chicago/Cubs 或者 Boston/Red Sox，但没有 Miami/Red Sox。


② 关系模型

1、维基百科解释：关系模型是基于谓词逻辑和集合论的一种数据模型，主要用于关系型数据库。

谓词逻辑（准确地说是“一阶谓词逻辑”）和集合论的知识在上文都介绍了。

2、教科书《数据库系统原理》解释：关系模型是用关系的形式表示实体和实体间联系的数据模型。

（3）关系跟表的区别？

虽然关系和表看上去很像，但是还是有区别的：

1、关系中不允许存在重复的元组（tuple），而表中可以存在。

2、关系中的记录不存在顺序，而表存在。即：关系中的元组没有从上往下的顺序，而表中的行有从上往下的顺序；关系中的属性没有从左往右的顺序，而表中的列有从左往右的顺序。

3、关系可能需要满足范式，而表无所谓。（下面会介绍范式）


但是我们平常的日常语言，还是会混淆的称呼，下面是严格的对应关系：

关系（relation） 表（table）
元组（tuple） 行（row）或记录（record）
势（cardinality） 行数（number of rows）
属性（attribute） 列（column）或字段（field）
度（degree） 列数（number of columns）
定义域（domain） 列的取值集合（pool of legal values）
（4）关系的性质

关系不只是集合，它还有许多非常有趣的性质。

其中之一就是“封闭性”（closure property）。这个性质简单地说就是“运算的输入和输出都是关系”，换句话来说，就是“保证关系世界永远封闭”的性质。

2、范式（NF）

（1）诞生

上面提到关系模型诞生历史，即 Codd 在 1970 年的第二篇论文里，首次出现了范式的概念，不过只有第一范式的想法（第二范式、第三范式的定义陆续出现在他之后的论文中）。

（2）什么是范式？

范式是“符合某一种级别的关系模式的集合，表示一个关系内部各属性之间的联系的合理化程度”。通俗理解就是：一张数据表的表结构所符合的某种设计标准的级别。


目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般说来，数据库只需满足到第三范式 (3NF）就行了。

（3）前三个范式

1、1NF：列是最小的单元（原子性约束），不可再分。

如：

  • 一个”省市区“字段，同时存了省市区（可能是用逗号分隔的字符串类型、或者是用了数组类型），应该拆分成"省"、"市"和“区”。
  • 同一个收货地址信息，在一张表里，重复出现在两个及其以上的字段。

一般来说，在宿主语言中可以灵活选择数组、结构体、对象等多种数据类型来表现非规范化的数据。但是在插入到数据库中时，必须将它们分解成标量值，即按照第一范式进行规范化，然后再存入数据库。

但在关系数据库诞生三十年后，SQL-99 进行了扩展，使得我们可以定义不满足第一范式的“数组类型”。（还有后来的 JSON/JSONB 类型）这个扩展对关系模型来说究竟是好还是坏，还不能轻易下判断。

然而在宿主语言和数据库之间传递和接收数据时，应该有很多读者因为双方支持的数据结构不一致而苦恼过吧？特别是面向对象语言和关系数据库不一致的问题，这种问题称为“阻抗不匹配”。由此可见，希望数据库能支持在宿主语言中可用的数据结构这种需求也是有道理的。


2、2NF：满足1NF。表中要有主键（惟一性约束），且非主键列必须完全依赖于全部主键而非部分主键。

如：一个订单表【OrderDetail】（OrderID，ProductID，ProductName，ProductUnitPrice，Quantity），OrderID + ProductID 是主键，虽然 Quantity 是完全依赖于 OrderID + ProductID 主键的，但 ProductName、ProductUnitPrice 只部分依赖于 ProductID 主键，所以应该把 OrderDetail 表拆分为 Order 表 和 Product 表。


3、3NF：满足2NF。非主键列是直接依赖于主键，而不是直接依赖于非主键列。

如：还是上面的例子， 一个订单表【OrderDetail】（OrderID，ProductID，ProductName，ProductUnitPrice，Quantity），仅 OrderID 是主键，ProductID，ProductName，ProductUnitPrice，Quantity 都确实完全依赖 OrderID 主键，但其中，ProductName，ProductUnitPrice 是通过先依赖 ProductID，再通过 ProductID 依赖 OrderID 的方式来传递依赖的。所以还是应该把 OrderDetail 表拆分为 Order 表 和 Product 表。

（4）后三个范式

1、BCNF范式（博伊斯—科德范式）：满足3NF。非主键列是直接依赖于所有主键（只有主键是多个才生效），而不是直接依赖于非主键列。

介绍略


2、第四范式

介绍略


3、第五范式

介绍略


（5）更多范式

1、DK 范式（Domain-Key normal form）

介绍略


2、第六范式

介绍略

（6）范式的利弊

因为范式的主要目的是为了消除冗余（范式级别越高，冗余越小），所以：

好处：

  • 降低存储成本（数据库范式是在20世纪提出的，当时的磁盘存储成本还很高。）

  • 提高拓展性

坏处：

  • 降低性能。没有任何冗余的表设计会产生更多的查询行为。

  • 增加设计表结构的难度

（7）反范式设计

既然范式是为了消除冗余，那么反范式就是通过增加冗余、聚合的手段来提升性能。尤其对现在的互联网应用来说，性能比存储成本的要求更高。

参考最近又流行的 noSQL 数据库，就是大大的冗余。


建议：还是根据自身的业务特点在范式和反范式中找到平衡点。

十二、进阶 - 性能优化

1、尽量避免排序

与编程（面向过程的）语言不同，在 SQL 语言中，用户不能显式地命令数据库进行排序操作。

所以，能触发排序的代表性的运算有下面这些：

  • GROUP BY 子句

  • ORDER BY 子句

  • 聚合函数（SUM、COUNT、AVG、MAX、MIN）

  • DISTINCT

  • 集合运算符（UNION、INTERSECT、EXCEPT）没加 ALL

  • 窗口函数（RANK、ROW_NUMBER 等）

为了性能，请尽量避免触发排序，如果不能，也尽量针对索引字段的排序。

2、没有用到索引的几种情况

（1）在索引字段上进行运算

使用索引时，条件表达式的左侧应该是原始字段。

错误：WHERE col_1 * 1.1 > 100;
正确：WHERE col_1 > 100 / 1.1
（2）使用 IS NULL / IS NOT NULL 谓词

因为 NULL 并不是值 ，所以索引字段并不会索引 NULL。

此处存疑，可见这篇辩驳：https://juejin.im/post/5d5defc2518825591523a1db

（3）使用否定形式

例如：

  • <>

  • NOT IN

  • NOT EXISTS

（4）使用联合索引时，列的顺序错误

假设存在这样顺序的一个联合索引：“col_1, col_2, col_3”。

× SELECT * FROM SomeTable WHERE col_1 = 10 AND col_3 = 500 ;
× SELECT * FROM SomeTable WHERE col_2 = 100 AND col_3 = 500 ;
× SELECT * FROM SomeTable WHERE col_2 = 100 AND col_1 = 10 ;

○ SELECT * FROM SomeTable WHERE col_1 = 10; 
○ SELECT * FROM SomeTable WHERE col_1 = 10 AND col_2 = 100 ;
○ SELECT * FROM SomeTable WHERE col_1 = 10 AND col_2 = 100 AND col_3 = 500;
（5）使用 OR

在 col_1 和 col_2 上分别建立了不同的索引，或者建立了（col_1, col_2）这样的联合索引时，如果使用 OR 连接条件，那么要么用不到索引，要么用到了但是效率比 AND 要差很多。

WHERE col_1 > 100 OR col_2 = 'abc';

如果无论如何都要使用OR，那么有一种办法是位图索引。但是这种索引的话更新数据时的性能开销会增大，所以使用之前需要权衡一下利弊。

（6）使用 LIKE 谓词进行后方一致或中间一致的匹配

使用 LIKE 谓词时，只有前方一致的匹配才能用到索引。

× SELECT * FROM SomeTable WHERE col_1 LIKE '%a';
× SELECT * FROM SomeTable WHERE col_1 LIKE '%a%';
○ SELECT * FROM SomeTable WHERE col_1 LIKE 'a%';

十三、进阶 - SQL 与 集合论

SQL 其中一个数学基础就是构建在集合论上。

我们通过画维恩图，可以很大程度地加深对 SQL 的理解。

1、全称量化和存在量化

（1）判断集合之间的包含关系

SQL 并没有提供任何用于检查集合的包含关系或者相等性的谓词。IN 谓词只能用来检查元素是否属于某个集合（∈），而不能检查集合是否是某个集合的子集（∪）。

据说，IBM 过去研制的第一个关系数据库实验系统——System R 曾经实现了用 CONTAINS 这一谓词来检查集合间的包含关系，但是后来因为性能原因被删除掉了，直到现在也没有恢复。

而判断集合之间的包含关系，就是下面要提到的：全称量化。

（2）全称量词和存在量词

“所有的 x 都满足条件P”或者“存在（至少一个）满足条件 P 的 x”。

前者称为“全称量词”，后者称为“存在量词”，分别记作 ∀ 、∃。

其实，全称量词的符号其实是将字母 A 上下颠倒而形成的，存在量词则是将字母 E 左右颠倒而形成的。
“对于所有的x，……”的英语是“for All x，…”，而“存在满足……的x”的英语是“there Exists x that…”，这就是这两个符号的由来。

但可惜，SQL 只支持 EXISTS（存在量词），不支持 FORALL（全称量词）。

但全称量词和存在量词只要定义了一个，另一个就可以被推导出来。

（3）全称量化 ⇔ 存在量化

通过德·摩根定律，来进行 “肯定⇔双重否定” 之间的转换。

即在 SQL 中，为了表达全称量化，需要将 “所有的行都满足条件 P” 这样的命题转换成 “不存在不满足条件 P 的行“，然后使用存在量词。


例子1：

查询条件为肯定的：“所有科目分数都在50 分以上”，转换成它的双重否定：“没有一个科目分数不满50 分”，然后用 NOT EXISTS 来表示转换后的命题，即：

SELECT DISTINCT student_id
FROM TestScores TS1
WHERE NOT EXISTS -- 不存在满足以下条件的行
(
    SELECT *
    FROM TestScores TS2
    WHERE TS2.student_id = TS1.student_id
    AND TS2.score < 50 -- 分数不满 50 分的科目
);

例子2：

“所有队员都处于待命状态”转化成“不存在不处于待命状态的队员”

不光可以用 NOT EXISTS ，也可以有其他方式：

-- 方法一：用谓词表达全称量化命题
SELECT team_id, member
FROM Teams T1
WHERE NOT EXISTS
(
    SELECT *
    FROM Teams T2
    WHERE T1.team_id = T2.team_id
    AND status <> '待命' 
);

-- 方法二：用集合表达全称量化命题(1)
SELECT team_id
FROM Teams
GROUP BY team_id
HAVING COUNT(*) = SUM(
    CASE WHEN status = '待命'
    THEN 1
    ELSE 0 
    END
);

-- 方法三：用集合表达全称量化命题(2)
SELECT team_id
FROM Teams
GROUP BY team_id
HAVING MAX(status) = '待命'
AND MIN(status) = '待命';

-- 方法四、比上面方式的更直观的展示方式（但性能比上面差)：列表显示各个队伍是否所有队员都在待命
SELECT team_id,
CASE 
    WHEN MAX(status) = '待命' AND MIN(status) = '待命'
    THEN '全都在待命'
    ELSE '队长！人手不够' 
    END AS status
FROM Teams
GROUP BY team_id;

NOT EXISTS 写法跟 其他方法（ HAVING 子句或者 ALL 谓词）的区别：

  • NOT EXISTS 写法可读性差

  • NOT EXISTS 性能更好

2、调查集合性质

下面是整理的在调查集合性质时经常用到的条件。

这些条件可以在 HAVING 子句中使用，也可以通过SELECT 子句写在CASE 表达式里使用。

No 条件表达式 用途
1 COUNT (DISTINCT col) = COUNT (col) col 列没有重复的值
2 COUNT(*) = COUNT(col) col 列不存在NULL
3 COUNT(*) = MAX(col) col 列是连续的编号（起始值是1）
4 COUNT(*) = MAX(col) - MIN(col) + 1 col 列是连续的编号（起始值是任意整数）
5 MIN(col) = MAX(col) col 列都是相同值，或者是NULL
6 MIN(col) * MAX(col) > 0 col 列全是正数或全是负数
7 MIN(col) * MAX(col) < 0 col 列的最大值是正数，最小值是负数
8 MIN(ABS(col)) = 0 col 列最少有一个是0
9 MIN(col - 常量) = - MAX(col - 常量) col 列的最大值和最小值与指定常量等距

十四、其他知识点

1、生成连续编号

先建一张 Digits 表：

digit
0
1
2
3
4
5
6
7
8
9 
-- 方法一：直接求（如1~542） 

SELECT D1.digit + (D2.digit * 10) + (D3.digit * 100) AS seq
FROM Digits D1 
CROSS JOIN Digits D2
CROSS JOIN Digits D3
WHERE D1.digit + (D2.digit * 10) + (D3.digit * 100) BETWEEN 1 AND 542
ORDER BY seq;

-- 方法二、先生成视图待用

-- 1、生成序列视图（包含0~999）
CREATE VIEW Sequence (seq)
AS SELECT D1.digit + (D2.digit * 10) + (D3.digit * 100)
FROM Digits D1 CROSS JOIN Digits D2
CROSS JOIN Digits D3;

-- 2、从序列视图中获取1~100
SELECT seq
FROM Sequence
WHERE seq BETWEEN 1 AND 100
ORDER BY seq;

有了这个连续编号，我们可以接着干很多事，具体可参考原书。

2、想尽一切办法隐藏地址

精巧的数据结构搭配笨拙的代码，远远好过笨拙的数据结构搭配精巧的代码。——大教堂与集市

（1）大家一起摆脱地址

即使放眼 SQL 之外的其他编程语言，各个编程语言的历史中也都一直存在着“如何对程序员隐藏地址”的课题。与 C 语言以及汇编语言相比，Pascal、Java、Perl 等新一代的语言都在努力地对用户隐藏指针。在这一点上，关系数据库与 SQL 的发展轨迹是一致的。

如 C 的指针，在 Java / Python 中被引用代替。

引用类似指针，只是不能进行指针运算，比如不能用 p + 1 指向下一个元素。

可能会有人列举出用户可以使用的指针，比如 Oracle 中的 rowid 或 PostgreSQL 中的 oid 来反对。确实，用户可以使用这些指针，但是它们都是个别数据库厂商违反 SQL 标准而进行的扩展，而标准 SQL一直在努力摆脱指针。

（2）为什么要隐藏地址

例如，SQL 和数据库都在极力提升数据在表现层的抽象度，以及对用户隐藏物理层的概念。

因此放弃地址的深刻意义是，通过放弃掉系统中没有意义的东西，创造出一个易于人类理解的有意义的世界。

（3）隐藏地址要怎么做

《程序设计能从冯·诺依曼风格中解放出来吗？程序的函数风格及其代数》中提到，只要使用变量，就无法逃出地址的魔咒。反过来说，之所以SQL 能成为不依赖于地址的自由的语言，也是因为它不使用变量。

其实 SQL 还是有变量的，也可以理解成它是个别数据库厂商违反 SQL 标准而进行的扩展吧。

与 SQL 一样不使用变量的语言还有 Lisp。它是一种年龄仅次于 Fortran 的高级语言，已经可以称得上是编程语言中的“老将”。

3、SQL 是一种什么语言

SQL 很大程度上是一种声明式编程，但是其也含有过程式编程的元素。

例如，关联子查询是为了使 SQL 能够实现类似面向过程语言中循环的功能而引入的。

SQL 在设计之初，就有意地避免了循环。所以 SQL 中没有专门的循环语句。

虽然可以使用游标实现循环，但是这样的话还是面向过程的做法。

所以，我们用好 SQL，就要有从面向过程思维向声明式思维、面向集合思维转变的意识。

4、[拓展] 命令式编程 vs 声明式编程

区别：

  • 命令式编程（Imperative）（也叫：过程式编程）：详细的去命令机器怎么（How）去处理一件事情以达到你想要的结果（What）
  • 声明式编程（Declarative）：只告诉你想要的结果（What），机器自己摸索过程（How）

例如：

  • c / c++，JAVA，JavaScript 等都是命令式编程语言。
  • SQL、正则表达式，或者逻辑语言（如 Prolog）等都是声明式语言。

我的观点：

1、所有的 命令式编程 本质上也是 声明式编程。底层还是会有交给机器自己处理的（How）步骤。

2、在命令式编程 也可以”创造”出 声明式编程，如 JavaScript 中使用 lodash 库，等于（How）步骤交给 lodash 去处理。

posted @ 2020-02-27 16:31  小蒋不素小蒋  阅读(7391)  评论(0)  编辑  收藏  举报

ICP证：沪ICP备20014317号