Chapter 1 数据库和SQL
1.1 数据库是什么
- 数据库是将大量数据保存起来，通过计算机加工而成的可以进行高效访问的数据集合。
- 用来管理数据库的计算机系统称为数据库管理系统（DBMS）。
- 通过使用DBMS，多个用户便可安全、简单地操作大量数据。
- 数据库有很多种类，本书将介绍如何使用专门的SQL语言来操作关系数据库。
- 关系数据库通过关系数据库管理系统（RDBMS）进行管理。
DBMS 的种类
DBMS 主要通过数据的保存格式（数据库的种类）来进行分类，现阶段主要有以下 5 种类型。
- 层次数据库（Hierarchical Database，HDB）：最古老的数据库之一，它把数据通过层次结构（树形结构）的方式表现出来。层次数据库曾经是数据库的主流，但随着关系数据库的出现和普及，现在已经很少使用了。
- 关系数据库（Relational Database，RDB）：关系数据库是现在应用最广泛的数据库。和 Excel 工作表一样，它也采用由行和列组成的二维表来管理数据。同时，它还使用专门的 SQL（Structured Query Language，结构化查询语言）对数据进行操作。
- 面向对象数据库（Object Oriented Database，OODB）：编程语言当中有一种被称为面向对象语言的语言。把数据以及对数据的操作集合起来以对象为单位进行管理，因此得名。面向对象数据库就是用来保存这些对象的数据库。
- XML 数据库（XML Database，XMLDB）：XML 数据库可以对 XML 形式的大量数据进行高速处理。
- 键值存储系统（Key-Value Store，KVS）：这是一种单纯用来保存查询所使用的主键（Key）和值（Value）的组合的数据库。具有编程语言知识的读者可以把它想象成关联数组或者散列（hash）。
1.2 数据库的结构
- RDBMS通常使用客户端/服务器这样的系统结构。
- 通过从客户端向服务器端发送SQL语句来实现数据库的读写操作。
- 关系数据库采用被称为数据库表的二维表来管理数据。
- 数据库表由表示数据项目的列（称为字段）和表示一条数据的行（称为记录）所组成，以记录为单位进行数据读写。
- 本书将行和列交汇的方格称为单元格，每个单元格只能输入一个数据。
1.3 SQL概要
- SQL是为操作数据库而开发的语言。
- 虽然SQL也有标准，但实际上根据RDBMS的不同SQL也不尽相同。
- SQL通过一条语句来描述想要进行的操作，发送给RDBMS。
- 原则上SQL语句都会使用分号结尾。
- SQL根据操作目的可以分为DDL、DML和DCL。
SQL 语句及其种类
SQL 用关键字、表名、列名等组合而成的一条语句（SQL 语句）来描述操作的内容。关键字是指那些含义或使用方法已事先定义好的英语单词，存在包含“对表进行查询”或者“参考这个表”等各种意义的关键字。
根据对 RDBMS 赋予的指令种类的不同，SQL 语句可以分为以下三类。
- DDL（Data Definition Language，数据定义语言）：用来创建或者删除存储数据用的数据库以及数据库中的表等对象。DDL 包含以下几种指令：
CREATE：创建数据库和表等对象DROP：删除数据库和表等对象-
ALTER：修改数据库和表等对象的结构 -
DDL（Data Manipulation Language，数据操纵语言）：用来查询或者变更表中的记录。DML 包含以下几种指令：
SELECT：查询表中的数据INSERT：向表中插入新数据UPDATE：更新表中的数据-
DELETE：删除表中的数据 -
DCL（Data Control Language，数据控制语言）：用来确认或者取消对数据库中的数据进行的变更。除此之外，还可以对 RDBMS 的用户是否有权限操作数据库中的对象（数据库表等）进行设定。DCL 包含以下几种指令：
COMMIT：确认对数据库中的数据进行的变更ROLLBACK：取消对数据库中的数据进行的变更GRANT：赋予用户操作权限REVOKE：取消用户的操作权限
SQL 的基本书写规则
- SQL 语句要以分号
;结尾。 - SQL 语句不区分大小写。
- 字符串和日期常数需要使用单引号
'括起来，数字常数无需加注单引号（直接书写数字即可）。 - 单词之间需要使用半角空格或者换行符进行分隔。
1.4 表的创建
- 表通过
CREATE TABLE语句创建而成。 - 表和列的命名要使用有意义的文字。
- 指定列的数据类型（整数型、字符型和日期型等）。
- 可以在表中设置约束（主键约束和
NOT NULL约束等）。
数据库的创建（CREATE DATABASE 语句）
前面提到，在创建表之前，一定要先创建用来存储表的数据库。运行 CREATE DATABASE 语句就可以在 RDBMS 上创建数据库了。CREATE DATABASE 语句的语法如下所示：
CREATE DATABASE <数据库名称>;
代码清单1-1 创建数据库shop的CREATE DATABASE 语句
CREATE DATABASE shop;
表的创建（CREATE TABLE 语句）
语法1-2 创建表的CREATE TABLE 语句
CREATE TABLE <表名>
（<列名1> <数据类型> <该列所需约束>，
<列名2> <数据类型> <该列所需约束>，
<列名3> <数据类型> <该列所需约束>，
<列名4> <数据类型> <该列所需约束>，
.
.
.
<该表的约束1>， <该表的约束2>，……）；
代码清单1-2 创建 Product 表的CREATE TABLE 语句
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));
命名规则
- 我们只能使用半角英文字母、数字、下划线
_作为数据库、表和列的名称。例如，不能将product_id写成product-id，因为标准 SQL 并不允许使用连字符作为列名等名称。$、#、?这样的符号同样不能作为名称使用。 - 名称必须以半角英文字母开头。
数据类型的指定
Product 表所包含的列，定义在 CREATE TABLE Product( ) 的括号中。列名右边的 INTEGER 或者 CHAR等关键字，是用来声明该列的数据类型的，所有的列都必须指定数据类型。
四种基本的数据类型：
- INTEGER型：用来指定存储整数的列的数据类型（数字型），不能存储小数。
- CHAR型：
CHAR是 CHARACTER（字符）的缩写，是用来指定存储字符串的列的数据类型（字符型）。可以像CHAR(10)或者CHAR(200)这样，在括号中指定该列可以存储的字符串的长度（最大长度）。字符串以定长字符串的形式存储在被指定为CHAR型的列中。字符串超出最大长度的部分是无法输入到该列中的；当列中存储的字符串长度达不到最大长度的时候，使用半角空格进行补足。RDBMS 不同，长度单位也不一样，既存在使用字符个数的情况，也存在使用字节长度的情况。 - VARCHAR型：同
CHAR类型一样，VARCHAR型也是用来指定存储字符串的列的数据类型（字符串类型），也可以通过括号内的数字来指定字符串的长度（最大长度）。但该类型的列是以可变长字符串的形式来保存字符串的。定长字符串在字符数未达到最大长度时会用半角空格补足，但可变长字符串不同，即使字符数未达到最大长度，也不会用半角空格补足。 - DATE型：用来指定存储日期（年月日）的列的数据类型（日期型）。
约束的设置
约束是除了数据类型之外，对列中存储的数据进行限制或者追加条件的功能。
Product 表的 product_id 列、product_name 列和 product_type 列的定义如下所示。
product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
数据类型的右侧设置了 NOT NULL 约束。就是给该列设置了不能输入空白。
在创建 Product 表的 CREATE TABLE 语句的后面，还有下面这样的记述。
PRIMARY KEY (product_id)
这是用来给 product_id 列设置主键约束的。所谓键，就是在指定特定数据时使用的列的组合。键种类多样，主键（primary key）就是可以确定一行数据的列。也就是说，如果把 product_id 列指定为主键，就可以通过该列取出特定的商品数据了。
1.5 表的删除和更新
- 使用
DROP TABLE语句来删除表。 - 使用
ALTER TABLE语句向表中添加列或者从表中删除列。
表的删除（DROP TABLE 语句）
语法1-3 删除表时使用的 DROP TABLE 语句
DROP TABLE <表名>；
代码清单1-3 删除 Product 表
DROP TABLE Product;
表定义的更新（ALTER TABLE 语句）
语法1-4 添加列的 ALTER TABLE语句
ALTER TABLE <表名> ADD COLUMN <列的定义>；
特定的 SQL
Oracle 和 SQL Server 中不用写 COLUMN。
ALTER TABLE <表名> ADD <列名> ；
另外，在 Oracle 中同时添加多列的时候，可以像下面这样使用括号。
ALTER TABLE <表名> ADD （<列名>，<列名>，……）；
代码清单1-4 添加一列可以存储100位的可变长字符串的product_name_pinyin列
-- DB2 PostgreSQL MySQL
ALTER TABLE Product ADD COLUMN product_name_pinyin VARCHAR(100);
-- Oracle
ALTER TABLE Product ADD (product_name_pinyin VARCHAR2(100));
-- SQL Server
ALTER TABLE Product ADD product_name_pinyin VARCHAR(100);
语法1-5 删除列的 ALTER TABLE 语句
ALTER TABLE <表名> DROP COLUMN <列名>；
特定的 SQL
Oracle 中不用写 COLUMN。
ALTER TABLE <表名> DROP <列名> ；
另外，在 Oracle 中同时删除多列的时候，可以像下面这样使用括号来实现。
ALTER TABLE <表名> DROP （<列名>，<列名>，……）；
代码清单1-5 删除 product_name_pinyin 列
-- SQL Server DB2 PostgreSQL MySQL
ALTER TABLE Product DROP COLUMN product_name_pinyin;
-- Oracle
ALTER TABLE Product DROP (product_name_pinyin);
表的修改
很多数据库都提供了可以修改表名的指令（RENAME）来解决这样的问题。例如，如果想把 Poduct表的名称变为 Product：
-- Oracle PostgreSQL
ALTER TABLE Poduct RENAME TO Product;
-- DB2
RENAME TABLE Poduct TO Product;
-- SQL Server
sp_rename 'Poduct', 'Product';
-- MySQL
RENAME TABLE Poduct to Product;
因为标准 SQL 并没有 RENAME，各个数据库的语法都不尽相同，于是各个数据库便使用了各自惯用的语法。
Chapter 2 查询基础
2.1 SELECT语句基础
列的查询
从表中选取数据时需要使用 SELECT 语句，也就是只从表中选出（SELECT）必要数据的意思。通过 SELECT 语句查询并选取出必要数据的过程称为匹配查询或查询（query）。
语法 2-1 基本的 SELECT 语句
SELECT <列名>，……
FROM <表名>；
该 SELECT 语句包含了 SELECT 和 FROM 两个子句（clause）。子句是 SQL 语句的组成要素，是以 SELECT 或者 FROM 等作为起始的短语。
SELECT 子句中列举了希望从表中查询出的列的名称，而 FROM 子句则指定了选取出数据的表的名称。
代码清单 2-1 从 Product 表中输出 3 列
SELECT product_id, product_name, purchase_price
FROM Product;
执行结果：
product_id | product_name | purchase_price
------------+--------------+----------------
0001 | T恤 | 500
0002 | 打孔器 | 320
0003 | 运动T恤 | 2800
0004 | 菜刀 | 2800
0005 | 高压锅 | 5000
0006 | 叉子 |
0007 | 擦菜板 | 790
0008 | 圆珠笔 |
SELECT 语句第一行的 SELECT product_id, product_name, purchase_price 就是 SELECT 子句。查询出的列的顺序可以任意指定。查询多列时，需要使用逗号进行分隔。查询结果中列的顺序和 SELECT 子句中的顺序相同。
查询出表中所有的列
语法 2-2 查询全部的列
SELECT *
FROM <表名>;
代码清单 2-2 输出 Product 表中全部的列
SELECT *
FROM Product;
执行结果：
product_id | product_name | product_type | sale_price | purchase_price | regist_date
------------+--------------+--------------+------------+----------------+-------------
0001 | T恤 | 衣服 | 1000 | 500 | 2009-09-20
0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11
0003 | 运动T恤 | 衣服 | 4000 | 2800 |
0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20
0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15
0006 | 叉子 | 厨房用具 | 500 | | 2009-09-20
0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28
0008 | 圆珠笔 | 办公用品 | 100 | | 2009-11-11
如果使用星号的话，就无法设定列的显示顺序了。这时就会按照 CREATE TABLE 语句的定义对列进行排序。
为列设定别名
SQL 语句可以使用 AS 关键字为列设定别名。
代码清单 2-4 为列设定别名
SELECT product_id AS id,
product_name AS name,
purchase_price AS price
FROM Product;
执行结果：
id | name | price
------+---------+-------
0001 | T恤 | 500
0002 | 打孔器 | 320
0003 | 运动T恤 | 2800
0004 | 菜刀 | 2800
0005 | 高压锅 | 5000
0006 | 叉子 |
0007 | 擦菜板 | 790
0008 | 圆珠笔 |
别名可以使用中文，使用中文时需要用双引号"括起来。使用双引号可以设定包含空格（空白）的别名。
代码清单 2-5 设定中文别名
SELECT product_id AS "商品编号",
product_name AS "商品名称",
purchase_price AS "进货单价"
FROM Product;
执行结果：
商品编号 | 商品名称 | 进货单价
----------+----------+----------
0001 | T恤 | 500
0002 | 打孔器 | 320
0003 | 运动T恤 | 2800
0004 | 菜刀 | 2800
0005 | 高压锅 | 5000
0006 | 叉子 |
0007 | 擦菜板 | 790
0008 | 圆珠笔 |
常数的查询
SELECT 子句中不仅可以书写列名，还可以书写常数。
代码清单 2-6 查询常数
SELECT '商品' AS string, 38 AS number, '2009-02-24' AS date,
product_id, product_name
FROM Product;
执行结果：
string | number | date | product_id | product_name
--------+--------+------------+------------+--------------
商品 | 38 | 2009-02-24 | 0001 | T恤
商品 | 38 | 2009-02-24 | 0002 | 打孔器
商品 | 38 | 2009-02-24 | 0003 | 运动T恤
商品 | 38 | 2009-02-24 | 0004 | 菜刀
商品 | 38 | 2009-02-24 | 0005 | 高压锅
商品 | 38 | 2009-02-24 | 0006 | 叉子
商品 | 38 | 2009-02-24 | 0007 | 擦菜板
商品 | 38 | 2009-02-24 | 0008 | 圆珠笔
从结果中删除重复行
在SELECT语句中使用DISTINCT可以删除重复行。
代码清单 2-7 使用 DISTINCT 删除 product_type 列中重复的数据
SELECT DISTINCT product_type
FROM Product;
执行结果：
product_type
--------------
衣服
办公用品
厨房用具
在使用 DISTINCT 时，NULL 也被视为一类数据。NULL 存在于多行中时，也会被合并为一条 NULL 数据。
代码清单 2-8 对含有 NULL 数据的列使用 DISTINCT 关键字
SELECT DISTINCT purchase_price
FROM Product;
执行结果：
purchase_price
----------------
320
500
2800
5000
790
DISTINCT 也可以像代码清单 2-9 那样在多列之前使用。此时，会将多个列的数据进行组合，将重复的数据合并为一条。
代码清单 2-9 在多列之前使用DISTINCT
SELECT DISTINCT product_type, regist_date
FROM Product;
执行结果：
product_type | regist_date
--------------+-------------
衣服 |
厨房用具 | 2008-04-28
衣服 | 2009-09-20
办公用品 | 2009-11-11
厨房用具 | 2009-01-15
办公用品 | 2009-09-11
厨房用具 | 2009-09-20
根据 WHERE 语句来选择记录
SELECT 语句通过 WHERE 子句来指定查询数据的条件。在 WHERE 子句中可以指定“某一列的值和这个字符串相等”或者“某一列的值大于这个数字”等条件，就可以查询出只符合该条件的记录了。
语法 2-3 SELECT 语句中的 WHERE 子句
SELECT <列名>, ……
FROM <表名>
WHERE <条件表达式>;
代码清单 2-10 用来选取product_type列为'衣服'的记录的SELECT语句
SELECT product_name, product_type
FROM Product
WHERE product_type = '衣服';
执行结果：
product_name | product_type
--------------+--------------
T恤 | 衣服
运动T恤 | 衣服
代码清单 2-11 也可以不选取出作为查询条件的列
SELECT product_name
FROM Product
WHERE product_type = '衣服';
执行结果：
product_name
--------------
T恤
运动T恤
SQL 中子句的书写顺序是固定的，不能随意更改。
注释的书写方法
- 单行注释：书写在“
--”之后，只能写在同一行。 - 多行注释：书写在
/*和*/之间，可以跨多行。
代码清单 2-13 1行注释的使用示例
-- 本SELECT语句会从结果中删除重复行。
SELECT DISTINCT product_id, purchase_price
FROM Product;
代码清单 2-14 多行注释的使用示例
/* 本SELECT语句，
会从结果中删除重复行。*/
SELECT DISTINCT product_id, purchase_price
FROM Product;
代码清单 2-15 在SQL 语句中插入1行注释
SELECT DISTINCT product_id, purchase_price
-- 本SELECT语句会从结果中删除重复行。
FROM Product;
代码清单 2-16 在SQL 语句中插入多行注释
SELECT DISTINCT product_id, purchase_price
/* 本SELECT语句，
会从结果中删除重复行。*/
FROM Product;
2.2 算术运算符和比较运算符
- 运算符就是对其两边的列或者值进行运算（计算或者比较大小等）的符号。
- 使用算术运算符可以进行四则运算。
- 括号可以提升运算的优先顺序（优先进行运算）。
- 包含
NULL的运算，其结果也是NULL。 - 比较运算符可以用来判断列或者值是否相等，还可以用来比较大小。
- 判断是否为
NULL，需要使用IS NULL或者IS NOT NULL运算符。
算术运算符
SQL 语句中可以使用计算表达式。
代码清单 2-17 SQL语句中也可以使用运算表达式
SELECT product_name, sale_price,
sale_price * 2 AS "sale_price_x2"
FROM Product;
执行结果：
product_name | sale_price | sale_price_x2
--------------+------------+---------------
T恤 | 1000 | 2000
打孔器 | 500 | 1000
运动T恤 | 4000 | 8000
菜刀 | 3000 | 6000
高压锅 | 6800 | 13600
叉子 | 500 | 1000
擦菜板 | 880 | 1760
圆珠笔 | 100 | 200
SQL 语句中可以使用的四则运算的主要运算符如下：
+运算符-运算符*运算符/运算符
所有包含 NULL 的计算，结果肯定是 NULL。
实际上FROM子句在SELECT语句中并不是必不可少的，只使用SELECT子句进行计算也是可以的。
代码清单2-A 只包含SELECT子句的SELECT语句
-- SQL Server PostgreSQL MySQL
SELECT (100 + 200) * 3 AS calculation;
执行结果：
calculation
-------------
900
特定的SQL
但是也存在像 Oracle 这样不允许省略 SELECT 语句中的 FROM 子句的 RDBMS。
在 Oracle 中，FROM 子句是必需的，这种情况下可以使用 DUAL 这个临时表。另外，DB2 中可以使用SYSIBM.SYSDUMMY1这个临时表。
比较运算符
在 WHERE 子句中通过使用比较运算符可以组合出各种各样的条件表达式。
| 运算符 | 含义 |
|---|---|
= |
和 ~ 相等 |
<> |
和 ~ 不相等 |
>= |
大于等于 ~ |
> |
大于 ~ |
<= |
小于等于 ~ |
< |
小于 ~ |
代码清单 2-20 选取出销售单价大于等于1000日元的记录
SELECT product_name, product_type, sale_price
FROM Product
WHERE sale_price >= 1000;
执行结果：
product_name | product_type | sale_price
---------------+--------------+--------------
T恤 | 衣服 | 1000
运动T恤 | 衣服 | 4000
菜刀 | 厨房用具 | 3000
高压锅 | 厨房用具 | 6800
代码清单 2-21 选取出登记日期在 2009 年 9 月27日之前的记录
SELECT product_name, product_type, regist_date
FROM Product
WHERE regist_date < '2009-09-27';
执行结果：
product_name | product_type | regist_date
---------------+--------------+-----------
T恤 | 衣服 | 2009-09-20
打孔器 | 办公用品 | 2009-09-11
菜刀 | 厨房用具 | 2009-09-20
高压锅 | 厨房用具 | 2009-01-15
叉子 | 厨房用具 | 2009-09-20
擦菜板 | 厨房用具 | 2008-04-28
除此之外，还可以使用比较运算符对计算结果进行比较。
代码清单 2-22 WHERE 子句的条件表达式中也可以使用计算表达式
SELECT product_name, sale_price, purchase_price
FROM Product
WHERE sale_price - purchase_price >= 500;
执行结果：
product_name | sale_price | purchase_price
---------------+-------------+---------------
T恤 | 1000 | 500
运动T恤 | 4000 | 2800
高压锅 | 6800 | 5000
2.3 逻辑运算符
- 通过使用逻辑运算符，可以将多个查询条件进行组合。
- 通过
NOT运算符可以生成“不是~”这样的查询条件。 - 两边条件都成立时，使用
AND运算符的查询条件才成立。 - 只要两边的条件中有一个成立，使用
OR运算符的查询条件就可以成立。 - 值可以归结为真（
TRUE）和假（FALSE）其中之一的值称为真值。比较运算符在比较成立时返回真，不成立时返回假。但是，在SQL中还存在另外一个特定的真值——不确定（UNKNOWN）。 - 将根据逻辑运算符对真值进行的操作及其结果汇总成的表称为真值表。
- SQL中的逻辑运算是包含对真、假和不确定进行运算的三值逻辑。
NOT 运算符
NOT 不能单独使用，必须和其他查询条件组合起来使用。
代码清单 2-30 选取出销售单价大于等于1000日元的记录
SELECT product_name, product_type, sale_price
FROM Product
WHERE sale_price >= 1000;
执行结果：
product_name | product_type | sale_price
--------------+--------------+------------
T恤 | 衣服 | 1000
运动T恤 | 衣服 | 4000
菜刀 | 厨房用具 | 3000
高压锅 | 厨房用具 | 6800
代码清单 2-31 向代码清单 2-30 的查询条件中添加 NOT 运算符
SELECT product_name, product_type, sale_price
FROM Product
WHERE NOT sale_price >= 1000;
执行结果：
product_name | product_type | sale_price
---------------+--------------+-------------
打孔器 | 办公用品 | 500
叉子 | 厨房用具 | 500
擦菜板 | 厨房用具 | 880
圆珠笔 | 办公用品 | 100
AND 运算符和 OR 运算符
在 WHERE 子句中使用 AND 运算符或者 OR 运算符，可以对多个查询条件进行组合。
AND 运算符在其两侧的查询条件都成立时整个查询条件才成立，其意思相当于“并且”。
OR 运算符在其两侧的查询条件有一个成立时整个查询条件都成立，其意思相当于“或者”。
例如，从 Product 表中选取出“商品种类为厨房用具（product_type = '厨房用具'），并且销售单价大于等于 3000 日元（sale_price >= 3000）的商品”的查询条件中就使用了 AND 运算符（代码清单 2-33）。
代码清单 2-33 在 WHERE子句的查询条件中使用 AND 运算符
SELECT product_name, purchase_price
FROM Product
WHERE product_type = '厨房用具'
AND sale_price >= 3000;
执行结果：
product_name | purchase_price
---------------+---------------
菜刀 | 2800
高压锅 | 5000
选取出“商品种类为厨房用具（product_type = '厨房用具'），或者销售单价大于等于 3000 日元（sale_price >= 3000）的商品”的查询条件中使用了 OR 运算符（代码清单 2-34）。
代码清单 2-34 在 WHERE 子句的查询条件中使用OR 运算符
SELECT product_name, purchase_price
FROM Product
WHERE product_type = '厨房用具'
OR sale_price >= 3000;
执行结果：
product_name | purchase_price
---------------+---------------
运动T恤 | 2800
菜刀 | 2800
高压锅 | 5000
叉子 |
擦菜板 | 790
通过括号强化处理
AND运算符的优先级高于OR运算符。想要优先执行OR运算符时可以使用括号。
代码清单 2-36 通过使用括号让 OR 运算符先于 AND 运算符执行
SELECT product_name, product_type, regist_date
FROM Product
WHERE product_type = '办公用品'
AND ( regist_date = '2009-09-11'
OR regist_date = '2009-09-20');
执行结果：
product_name | product_type | regist_date
---------------+--------------+------------
打孔器 | 办公用品 | 2009-09-11
含有 NULL 时的真值
查询 NULL 时不能使用比较运算符（= 或者 <>），需要使用 IS NULL 运算符或者 IS NOT NULL 运算符。实际上，使用逻辑运算符时也需要特别对待 NULL。
其实这是 SQL 中特有的情况。这时真值是除真假之外的第三种值——不确定（UNKNOWN）。一般的逻辑运算并不存在这第三种值。SQL 之外的语言也基本上只使用真和假这两种真值。与通常的逻辑运算被称为二值逻辑相对，只有 SQL 中的逻辑运算被称为三值逻辑。
表 2-6 三值逻辑中的 AND 和OR 真值表
AND
| P | Q | P AND Q |
|---|---|---|
| 真 | 真 | 真 |
| 真 | 假 | 假 |
| 真 | 不确定 | 不确定 |
| 假 | 真 | 假 |
| 假 | 假 | 假 |
| 假 | 不确定 | 假 |
| 不确定 | 真 | 不确定 |
| 不确定 | 假 | 假 |
| 不确定 | 不确定 | 不确定 |
OR
| P | Q | P OR Q |
|---|---|---|
| 真 | 真 | 真 |
| 真 | 假 | 真 |
| 真 | 不确定 | 真 |
| 假 | 真 | 真 |
| 假 | 假 | 假 |
| 假 | 不确定 | 不确定 |
| 不确定 | 真 | 真 |
| 不确定 | 假 | 不确定 |
| 不确定 | 不确定 | 不确定 |
Chapter 3 聚合与排序
3.1 对表进行聚合查询
- 使用聚合函数对表中的列进行计算合计值或者平均值等的汇总操作。
- 通常，聚合函数会对
NULL以外的对象进行汇总。但是只有COUNT函数例外，使用COUNT(*)可以查出包含NULL在内的全部数据的行数。 - 使用
DISTINCT关键字删除重复值。
聚合函数
通过 SQL 对数据进行某种操作或计算时需要使用函数。以下是 5 个常用的函数：
-
COUNT：计算表中的记录数（行数） -
SUM：计算表中数值列中数据的合计值 -
AVG：计算表中数值列中数据的平均值 -
MAX：求出表中任意列中数据的最大值 -
MIN：求出表中任意列中数据的最小值
如上所示，用于汇总的函数称为聚合函数或者聚集函数。所谓聚合，就是将多行汇总为一行。实际上，所有的聚合函数都是这样，输入多行输出一行。
计算表中数据的行数
使用 COUNT 函数时，输入表的列，就能够输出数据行数。
代码清单 3-1 计算全部数据的行数
SELECT COUNT(*)
FROM Product;
执行结果：
count
-------
8
此处的输入值称为参数或者 parameter，输出值称为返回值。
计算 NULL 之外的数据的行数
Chapter 5 复杂查询
5.1 视图
使用视图时并不会将数据保存到存储设备之中，而且也不会将数据保存到其他任何地方。实际上视图保存的是 SELECT 语句。我们从视图中读取数据时，视图会在内部执行该 SELECT 语句并创建出一张临时表。
而且，视图中的数据会随着原表的变化自动更新。视图归根到底就是 SELECT 语句，所谓“参照视图”也就是“执行 SELECT 语句”的意思，因此可以保证数据的最新状态。
- 从SQL的角度来看，视图和表是相同的，两者的区别在于表中保存的是实际的数据，而视图中保存的是
SELECT语句（视图本身并不存储数据）。 - 使用视图，可以轻松完成跨多表查询数据等复杂操作。
- 应该将常用的
SELECT语句做成视图来使用。 - 创建视图需要使用
CREATE VIEW语句。 - 视图包含“不能使用
ORDER BY”和“可对其进行有限制的更新”两项限制。 - 删除视图需要使用
DROP VIEW语句。
创建视图
语法
创建视图需要使用 CREATE VIEW 语句，其语法如下所示。
CREATE VIEW 视图名称 (<视图列名1>, <视图列名2>, ……)
AS
<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;
执行结果：
product_type | cnt_product
--------------+------------
衣服 | 2
办公用品 | 2
厨房用具 | 4
在视图的基础上创建视图，即 多重视图 ：
CREATE VIEW ProductSumJim (product_type, cnt_product)
AS
SELECT product_type, cnt_product
FROM ProductSum
WHERE product_type = '办公用品';
-- 确认创建好的视图
SELECT product_type, cnt_product
FROM ProductSumJim;
执行结果：
product_type | cnt_product
--------------+------------
办公用品 | 2
注意：虽然语法上没有错误，但是我们还是应该尽量避免在视图的基础上创建视图。这是因为对多数 DBMS 来说，多重视图会降低 SQL 的性能。
视图的限制
- 定义视图时不要使用
ORDER BY子句。 - 标准 SQL 中有这样的规定：如果定义视图的
SELECT语句能够满足某些条件，那么这个视图就可以被更新。下面是一些比较具有代表性的条件： SELECT子句中未使用DISTINCTFROM子句中只有一张表- 未使用
GROUP BY子句。因为视图和表需要同时进行更新，因此通过汇总得到的视图无法进行更新。 - 未使用
HAVING子句
实例
不能更新的视图：
可以更新的视图：
向视图中添加数据行：
特定的SQL
PostgreSQL
注意：由于 PostgreSQL 中的视图会被初始设定为只读，所以执行前面的INSERT语句时，会发生下面这样的错误。
ERROR: 不能向视图中插入数据
HINT: 需要一个无条件的ON INSERT DO INSTEAD规则
这种情况下，在INSERT语句执行之前，需要使用代码清单5-A中的指令来允许更新操作。在 DB2 和 MySQL 等其他 DBMS 中，并不需要执行这样的指令。
-- PostgreSQL
CREATE OR REPLACE RULE insert_rule
AS ON INSERT
TO ProductJim DO INSTEAD
INSERT INTO Product VALUES (
new.product_id,
new.product_name,
new.product_type,
new.sale_price,
new.purchase_price,
new.regist_date);
UPDATE 语句和 DELETE 语句当然也可以像操作表时那样正常执行，但是对于原表来说却需要设置各种各样的约束（主键和 NOT NULL 等），需要特别注意。
删除视图
语法
删除视图的 DROP VIEW 语句：
DROP VIEW 视图名称(<视图列名1>, <视图列名2>, ……)
实例
DROP VIEW ProductSum;
特定的SQL
PostgreSQL
在 PostgreSQL 中，如果删除以视图为基础创建出来的多重视图，由于存在关联的视图，因此会发生如下错误。
ERROR: 由于存在关联视图，因此无法删除视图productsum
DETAIL: 视图productsumjim与视图productsum相关联
HINT: 删除关联对象请使用DROP…CASCADE
这时可以使用CASCADE选项来删除关联视图。
-- PostgreSQL
DROP VIEW ProductSum CASCADE;
5.2 子查询
- 一言以蔽之，子查询就是一次性视图（
SELECT语句）。与视图不同，子查询在SELECT语句执行完毕之后就会消失。 - 原则上子查询必须设定名称，因此需要根据处理内容来指定恰当的名称。
- 标量子查询（scalar subquery）就是只能返回一行一列的子查询。
语法
SQL Server, DB2, PostgreSQL, MySQL
--SQL Server, DB2, PostgreSQL, MySQL
--在FROM子句中直接书写定义视图的SELECT语句
SELECT product_type, cnt_product
FROM (SELECT product_type, COUNT(*) AS cnt_product
FROM Product
GROUP BY product_type) AS ProductSum;
执行结果：
product_type | cnt_product
--------------+------------
衣服 | 2
办公用品 | 2
厨房用具 | 4
特定的SQL
Oracle
在 Oracle 的FROM子句中，不能使用 AS（会发生错误），因此，在Oracle中执行代码清单5-9时，需要将①中的“) AS ProductSum;”变为“) ProductSum; ”
—-Oracle
--在FROM子句中直接书写定义视图的SELECT语句
SELECT product_type, cnt_product
FROM (SELECT product_type, COUNT(*) AS cnt_product
FROM Product
GROUP BY product_type) ProductSum;
包含子查询的SELECT语句的执行顺序
实际上，该 SELECT 语句包含嵌套的结构，子查询作为内层查询会首先执行，然后才会执行外层的 SELECT 语句。
增加子查询的层数
由于子查询的层数原则上没有限制，因此可以无限嵌套下去。
SQL Server, DB2, PostgreSQL, MySQL:
-- SQL Server DB2 PostgreSQL MySQL
SELECT product_type, cnt_product
FROM (SELECT *
FROM (SELECT product_type, COUNT(*) AS cnt_product
FROM Product
GROUP BY product_type) AS ProductSum -----①
WHERE cnt_product = 4) AS ProductSum2; -----------②
Oracle
-- Oracle
SELECT product_type, cnt_product
FROM (SELECT *
FROM (SELECT product_type, COUNT(*) AS cnt_product
FROM Product
GROUP BY product_type) ProductSum -----①
WHERE cnt_product = 4) ProductSum2; -----------②
执行结果
product_type | cnt_product
--------------+------------
厨房用具 | 4
标量子查询
标量就是单一的意思，在数据库之外的领域也经常使用。
标量子查询有一个特殊的限制，那就是必须而且只能返回 1 行 1 列的结果，也就是返回表中某一行的某一列的值。
在 WHERE 子句中使用标量子查询
查询出销售单价高于全部商品平均销售单价的商品：
SELECT product_id, product_name, sale_price
FROM Product
WHERE sale_price > (SELECT AVG(sale_price)
FROM Product);
执行结果：
product_id | product_name | sale_price
------------+--------------+-----------
0003 | 运动T恤 | 4000
0004 | 菜刀 | 3000
0005 | 高压锅 | 6800
包含标量子查询的SELECT语句的执行顺序
标量子查询的书写位置
标量子查询的书写位置并不仅仅局限于 WHERE 子句中，通常任何可以使用单一值的位置都可以使用。也就是说，能够使用常数或者列名的地 方，无论是 SELECT 子句、GROUP BY 子句、HAVING 子句，还是 ORDER BY 子句，几乎所有的地方都可以使用。
实例1
在 SELECT 子句当中使用之前计算平均值的标量子查询的 SQL 语句：
SELECT product_id,
product_name,
sale_price,
(SELECT AVG(sale_price)
FROM Product) AS avg_price
FROM Product;
执行结果：
product_id | product_name | sale_price | avg_price
------------+---------------+------------+----------------------
0001 | T恤衫 | 1000 | 2097.5000000000000000
0002 | 打孔器 | 500 | 2097.5000000000000000
0003 | 运动T恤 | 4000 | 2097.5000000000000000
0004 | 菜刀 | 3000 | 2097.5000000000000000
0005 | 高压锅 | 6800 | 2097.5000000000000000
0006 | 叉子 | 500 | 2097.5000000000000000
0007 | 擦菜板 | 880 | 2097.5000000000000000
0008 | 圆珠笔 | 100 | 2097.5000000000000000
实例2
在 HAVING 子句中使用标量子查询：
SELECT product_type, AVG(sale_price)
FROM Product
GROUP BY product_type
HAVING AVG(sale_price) > (SELECT AVG(sale_price)
FROM Product);
执行结果：
product_type | avg
--------------+----------------------
衣服 | 2500.0000000000000000
厨房用具 | 2795.0000000000000000
5.3 关联子查询
- 在细分的组内进行比较时，需要使用关联子查询。
- 关联子查询和
GROUP BY子句一样，也可以对表中的数据进行切分。 - 关联子查询的结合条件必须出现在子查询之中。
语法
SQL Server, DB2, PostgreSQL, MySQL:
--SQL Server, DB2, PostgreSQL, MySQL
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); -- 此例中，不使用此行的GROUP BY子句也能得到相同的结果。
注意：实际上，上面
SELECT语句，即使在子查询中不使用GROUP BY子句，也能得到正确的结果。 这是因为在WHERE子句中追加了“P1.product_type=P2.product_type”这个条件，使得AVG函数按照商品种类进行了平均值计算。但是为了跟前面出错的查询进行对比，这里还是加上了GROUP BY子句。
执行结果：
product_type | product_name | sale_price
---------------+---------------+------------
办公用品 | 打孔器 | 500
衣服 | 运动T恤 | 4000
厨房用具 | 菜刀 | 3000
厨房用具 | 高压锅 | 6800
Oracle
--Oracle
SELECT product_type, product_name, sale_price
FROM Product P1
WHERE sale_price > (SELECT AVG(sale_price)
FROM Product P2
WHERE P1.product_type = P2.product_type
GROUP BY product_type); -- 此例中，不使用此行的GROUP BY子句也能得到相同的结果。
Oracle 中不能使用
AS（会发生错误）。因此，在Oracle中执行代码清单5-16时，请大家把①中的FROM Product AS P1变为FROM Product P1，把②中的FROM Product AS P2变为FROM Product P2。
关联子查询执行时DBMS内部的执行情况
我们首先需要计算各个商品种类中商品的平均销售单价，由于该单价会用来和商品表中的各条记录进行比较，因此关联子查询实际只能返回 1 行结果。这也是关联子查询不出错的关键。
结合条件一定要写在子查询中
该书写方法违反了关联名称的作用域。关联名称就是像 P1、P2 这样作为表别名的名称，作用域（scope）就是生存范围（有效范围）。
具体来讲，子查询内部设定的关联名称，只能在该子查询内部使用（如下图）。换句话说，就是“内部可以看到外部，而外部看不到内部”。
如前所述，SQL 是按照先内层子查询后外层查询的顺序来执行的。这样，子查询执行结束时只会留下执行结果，作为抽出源的 P2 表其实已经不存在了。因此，在执行外层查询时，由于 P2 表已经不存在了，因此就会返回“不存在使用该名称的表”这样的错误。
更多用例
用例1
查询在各product_type中sale_price最高的产品的所有字段：
SELECT *
FROM Product AS P1
WHERE sale_price=(SELECT max(sale_price)
FROM Product AS P2
WHERE P1.product_type=P2.product_type
GROUP BY product_type); -- 此例中，不使用此行的GROUP BY子句也能得到相同的结果。
执行结果：
product_id | product_name | product_type | sale_price | purchase_price | regist_date
------------+--------------+--------------+------------+----------------+-------------
0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11
0003 | 运动T恤 | 衣服 | 4000 | 2800 |
0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15
(3 行记录)
练习题
5.1 创建出满足下述三个条件的视图（视图名称为ViewPractice5_1）。使用Product（商品）表作为参照表，假设表中包含初始状态的8 行数据。
条件1： 销售单价大于等于1000 日元。
条件2： 登记日期是2009 年9 月20 日。
条件3： 包含商品名称、销售单价和登记日期三列。
对该视图执行SELECT 语句的结果如下所示。
SELECT * FROM ViewPractice5_1;
执行结果
product_name | sale_price | regist_date
--------------+------------+-------------
T恤衫 | 1000 | 2009-09-20
菜刀 | 3000 | 2009-09-20
答案：
-- 创建视图的语句
CREATE VIEW ViewPractice5_1 AS
SELECT product_name, sale_price, regist_date
FROM Product
WHERE sale_price >= 1000
AND regist_date = '2009-09-20';
5.3 请根据如下结果编写 SELECT 语句，其中 sale_price_all 列为全部商品的平均销售单价。
执行结果
product_id | product_name | product_type | sale_price | sale_price_all
------------+--------------+--------------+------------+----------------------
0001 | T恤衫 | 衣服 | 1000 | 2097.5000000000000000
0002 | 打孔器 | 办公用品 | 500 | 2097.5000000000000000
0003 | 运动T恤 | 衣服 | 4000 | 2097.5000000000000000
0004 | 菜刀 | 厨房用具 | 3000 | 2097.5000000000000000
0005 | 高压锅 | 厨房用具 | 6800 | 2097.5000000000000000
0006 | 叉子 | 厨房用具 | 500 | 2097.5000000000000000
0007 | 擦菜板 | 厨房用具 | 880 | 2097.5000000000000000
0008 | 圆珠笔 | 办公用品 | 100 | 2097.5000000000000000
答案：
SELECT product_id,
product_name,
product_type,
sale_price,
(SELECT AVG(sale_price) FROM Product) AS sale_price_all
FROM Product;
5.4 请根据习题 5.1 中的条件编写一条 SQL 语句，创建一幅包含如下数据的视图（名称为 AvgPriceByType）。
执行结果：
product_id | product_name | product_type | sale_price | avg_sale_price
------------+--------------+--------------+------------+-----------------------
0001 | T恤 | 衣服 | 1000 | 2500.0000000000000000
0002 | 打孔器 | 办公用品 | 500 | 300.0000000000000000
0003 | 运动T恤 | 衣服 | 4000 | 2500.0000000000000000
0004 | 菜刀 | 厨房用具 | 3000 | 2795.0000000000000000
0005 | 高压锅 | 厨房用具 | 6800 | 2795.0000000000000000
0006 | 叉子 | 厨房用具 | 500 | 2795.0000000000000000
0007 | 擦菜板 | 厨房用具 | 880 | 2795.0000000000000000
0008 | 圆珠笔 | 办公用品 | 100 | 300.0000000000000000
(8 行记录)
答案：
CREATE VIEW AvgPriceByType (product_id,product_name,product_type,sale_price, avg_sale_price)
AS
SELECT product_id,product_name,product_type,sale_price, (SELECT avg(sale_price) AS avg_sale_price
FROM Product
WHERE P.product_type=product_type
GROUP BY product_type)
FROM Product AS P;
结果验证：
shop=# SELECT * FROM AvgPriceByType;
product_id | product_name | product_type | sale_price | avg_sale_price
------------+--------------+--------------+------------+-----------------------
0001 | T恤 | 衣服 | 1000 | 2500.0000000000000000
0002 | 打孔器 | 办公用品 | 500 | 300.0000000000000000
0003 | 运动T恤 | 衣服 | 4000 | 2500.0000000000000000
0004 | 菜刀 | 厨房用具 | 3000 | 2795.0000000000000000
0005 | 高压锅 | 厨房用具 | 6800 | 2795.0000000000000000
0006 | 叉子 | 厨房用具 | 500 | 2795.0000000000000000
0007 | 擦菜板 | 厨房用具 | 880 | 2795.0000000000000000
0008 | 圆珠笔 | 办公用品 | 100 | 300.0000000000000000
(8 行记录)
Chapter 6 函数、谓词、CASE 表达式
6.1 各种各样的函数
- 根据用途，函数可以大致分为算术函数、字符串函数、日期函数、转换函聚合函数。
- 函数的种类很多，无需全都记住，只需要记住具有代表性的函数就可以了，其他的可以在使用时再进行查询。
函数的种类
函数大致可以分为以下几种：
- 算术函数（用来进行数值计算的函数）
- 字符串函数（用来进行字符串操作的函数）
- 日期函数（用来进行日期操作的函数）
- 转换函数（用来转换数据类型和值的函数）
- 聚合函数（用来进行数据聚合的函数）：
COUNT、SUM、AVG、MAX、MIN
算术函数
本节所使用到的示例表：
-- DDL ：创建表
CREATE TABLE SampleMath
(m NUMERIC (10,3),
n INTEGER,
p INTEGER);
--- SQL Server, PostgreSQL
-- DML ：插入数据
BEGIN TRANSACTION; -- START TRANSACTION;
INSERT INTO SampleMath(m, n, p) VALUES (500, 0, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (-180, 0, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, NULL, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 7, 3);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 5, 2);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 4, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (8, NULL, 3);
INSERT INTO SampleMath(m, n, p) VALUES (2.27, 1, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (5.555,2, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 1, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (8.76, NULL, NULL);
COMMIT;
执行结果：
shop=# SELECT * FROM SampleMath;
m | n | p
----------+---+---
500.000 | 0 |
-180.000 | 0 |
| |
| 7 | 3
| 5 | 2
| 4 |
8.000 | | 3
2.270 | 1 |
5.555 | 2 |
| 1 |
8.760 | |
500.000 | 0 |
-180.000 | 0 |
| |
| 7 | 3
| 5 | 2
| 4 |
8.000 | | 3
2.270 | 1 |
5.555 | 2 |
| 1 |
8.760 | |
(22 行记录)
绝对值函数ABS
SELECT m,
ABS(m) AS abs_col
FROM SampleMath;
执行结果：
m | abs_col
----------+---------
500.000 | 500.000
-180.000 | 180.000
|
|
|
|
8.000 | 8.000
2.270 | 2.270
5.555 | 5.555
|
8.760 | 8.760
500.000 | 500.000
-180.000 | 180.000
|
|
|
|
8.000 | 8.000
2.270 | 2.270
5.555 | 5.555
|
8.760 | 8.760
(22 行记录)
ABS 函数的参数为 NULL 时，结果也是 NULL。并非只有 ABS 函数如此，其实绝大多数函数对于 NULL 都返回 NULL。
取余函数MOD
语法：
MOD(被除数，除数)
计算除法（n ÷ p）的余数：
-- Oracle DB2 PostgreSQL MySQL
SELECT n, p,
MOD(n, p) AS mod_col
FROM SampleMath;
执行结果：
n | p | mod_col
---+---+---------
0 | |
0 | |
| |
7 | 3 | 1
5 | 2 | 1
4 | |
| 3 |
1 | |
2 | |
1 | |
| |
0 | |
0 | |
| |
7 | 3 | 1
5 | 2 | 1
4 | |
| 3 |
1 | |
2 | |
1 | |
| |
(22 行记录)
特定的SQL
SQL Server使用特殊的运算符（函数）“%”来计算余数，使用如下的专用语法可以得到相同的结果。
-- SQL Server
SELECT n, p,
n % p AS mod_col
FROM SampleMath;
四舍五入ROUND
语法：
ROUND(对象数值，保留小数的位数)
对 m 列的数值进行 n 列位数的四舍五入处理：
SELECT m, n,
ROUND(m, n) AS round_col
FROM SampleMath;
执行结果：
m | n | round_col
----------+---+-----------
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 | |
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 | |
(22 行记录)
字符串函数
本节所使用的示例表：
-- DDL ：创建表
CREATE TABLE SampleStr
(str1 VARCHAR(40),
str2 VARCHAR(40),
str3 VARCHAR(40));
-- SQL Server PostgreSQL
-- DML ：插入数据
BEGIN TRANSACTION; -- START TRANSACTION;
INSERT INTO SampleStr (str1, str2, str3) VALUES ('opx' ,'rt',NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abc' ,'def' ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('山田' ,'太郎' ,'是我');
INSERT INTO SampleStr (str1, str2, str3) VALUES ('aaa' ,NULL ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES (NULL ,'xyz',NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('@!#$%' ,NULL ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('ABC' ,NULL ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('aBC' ,NULL ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abc太郎' ,'abc' ,'ABC');
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abcdefabc' ,'abc' ,'ABC');
INSERT INTO SampleStr (str1, str2, str3) VALUES ('micmic' ,'i' ,'I');
COMMIT;
执行结果：
shop=# SELECT * FROM SampleStr;
str1 | str2 | str3
-----------+------+------
opx | rt |
abc | def |
山田 | 太郎 | 是我
aaa | |
| xyz |
@!#$% | |
ABC | |
aBC | |
abc太郎 | abc | ABC
abcdefabc | abc | ABC
micmic | i | I
(11 行记录)
拼接函数：||
语法：
字符串1||字符串2
拼接两个字符串（str1+str2）：
-- Oracle DB2 PostgreSQL
SELECT str1, str2,
str1 || str2 AS str_concat
FROM SampleStr;
执行结果：
str1 | str2 | str_concat
-----------+------+--------------
opx | rt | opxrt
abc | def | abcdef
山田 | 太郎 | 山田太郎
aaa | |
| xyz |
@!#$% | |
ABC | |
aBC | |
abc太郎 | abc | abc太郎abc
abcdefabc | abc | abcdefabcabc
micmic | i | micmici
(11 行记录)
进行字符串拼接时，如果其中包含
NULL，那么得到的结果也是NULL。
拼接三个字符串（str1+str2+str3）：
-- Oracle DB2 PostgreSQL
SELECT str1, str2, str3,
str1 || str2 || str3 AS str_concat
FROM SampleStr
WHERE str1 = '山田';
执行结果：
str1 | str2 | str3 | str_concat
------+------+------+-----------
山田 | 太郎 | 是我 | 山田太郎是我
特定的SQL
SQL Server使用+运算符（函数）来连接字符串。在SQL Server 2012及其之后的版本中也可以使用CONCAT函数。
-- SQL Server
SELECT str1, str2, str3,
str1 + str2 + str3 AS str_concat
FROM SampleStr;
MySQL使用 CONCAT 函数来完成字符串的拼接。
-- MySQL SQL, Server 2012 及之后
SELECT str1, str2, str3,
CONCAT(str1, str2, str3) AS str_concat
FROM SampleStr;
字符串长度函数：LENGTH
语法：
LENGTH(字符串)
计算字符串长度：
-- Oracle, DB2, PostgreSQL, MySQL
SELECT str1,
LENGTH(str1) AS len_str
FROM SampleStr;
执行结果：
str1 | len_str
-----------+---------
opx | 3
abc | 3
山田 | 2
aaa | 3
|
@!#$% | 5
ABC | 3
aBC | 3
abc太郎 | 5
abcdefabc | 9
micmic | 6
(11 行记录)
特定的SQL
SQL Server使用LEN函数来计算字符串的长度。
-- SQL Server
SELECT str1,
LEN(str1) AS len_str
FROM SampleStr;
小写转换函数LOWER
语法：
LOWER(字符串)
大写转换为小写：
SELECT str1,
LOWER(str1) AS low_str
FROM SampleStr
WHERE str1 IN ('ABC', 'aBC', 'abc', '山田');
执行结果：
str1 | low_str
------+---------
abc | abc
山田 | 山田
ABC | abc
aBC | abc
(4 行记录)
大写转换函数UPPER
语法：
UPPER(字符串)
将小写转换为大写：
SELECT str1,
UPPER(str1) AS up_str
FROM SampleStr
WHERE str1 IN ('ABC', 'aBC', 'abc', '山田');
执行结果：
str1 | up_str
------+--------
abc | ABC
山田 | 山田
ABC | ABC
aBC | ABC
字符串的替换REPLACE
语法：
REPLACE(对象字符串，替换前的字符串，替换后的字符串)
替换字符串的一部分：
SELECT str1, str2, str3,
REPLACE(str1, str2, str3) AS rep_str
FROM SampleStr;
执行结果：
str1 | str2 | str3 | rep_str
-----------+------+------+---------
opx | rt | |
abc | def | |
山田 | 太郎 | 是我 | 山田
aaa | | |
| xyz | |
@!#$% | | |
ABC | | |
aBC | | |
abc太郎 | abc | ABC | ABC太郎
abcdefabc | abc | ABC | ABCdefABC
micmic | i | I | mIcmIc
字符串的截取SUBSTRING
语法：
SUBSTRING（对象字符串 FROM 截取的起始位置 FOR 截取的字符数）
虽然上述 SUBSTRING 函数的语法是标准 SQL 承认的正式语法，但是现在只有 PostgreSQL 和 MySQL 支持该语法。
截取出字符串中第 3 位和第 4 位的字符：
-- PostgreSQL MySQL
SELECT str1,
SUBSTRING(str1 FROM 3 FOR 2) AS sub_str
FROM SampleStr;
执行结果：
str1 | sub_str
-----------+--------
opx | x
abc | c
山田 |
aaa | a
|
@!#$% | #$
ABC | C
aBC | C
abc太郎 | c太
abcdefabc | cd
micmic | cm
特定的SQL
SQL Server 将语法6-8a中的内容进行了简化：
SUBSTRING函数（SQL Server专用语法）：
SUBSTRING(对象字符串，截取的起始位置，截取的字符数)
截取出字符串中第 3 位和第 4 位的字符：
-- SQL Server
SELECT str1,
SUBSTRING(str1, 3, 2) AS sub_str
FROM SampleStr;
Oracle 和 DB2 将该语法进一步简化，得到了如下结果。
Oracle/DB2专用语法：
SUBSTR(对象字符串，截取的起始位置，截取的字符数)
截取出字符串中第 3 位和第 4 位的字符：
-- Oracle DB2
SELECT str1,
SUBSTR(str1, 3, 2) AS sub_str
FROM SampleStr;
日期函数
当前日期函数CURRENT_DATE
语法：
CURRENT_DATE
CURRENT_DATE 函数能够返回 SQL 执行的日期，也就是该函数执行时的日期。由于没有参数，因此无需使用括号。
该函数无法在 SQL Server 中执行。此外，Oracle 和 DB2 中的语法略有不同。
获得当前日期：
-- MySQL PostgreSQL
SELECT CURRENT_DATE;
执行结果：
date
------------
2016-05-25
特定的SQL
SQL Server 使用如下的CURRENT_TIMESTAMP（后述）函数来获得当前日期。
-- SQL Server
-- 使用CAST（后述）函数将CURRENT_TIMESTAMP转换为日期类型
SELECT CAST(CURRENT_TIMESTAMP AS DATE) AS CUR_DATE;
执行结果：
CUR_DATE
----------
2010-05-25
在Oracle中使用该函数时，需要在FROM子句中指定临时表（DUAL）。
-- Oracle
SELECT CURRENT_DATE
FROM dual;
而在DB2中使用时，需要在CRUUENT和DATE之间添加半角空格，并且还需要指定临时表SYSIBM.SYSDUMMY1（相当于Oracle中的DUAL）。这些容易混淆的地方请大家多加注意。
-- DB2
SELECT CURRENT DATE
FROM SYSIBM.SYSDUMMY1;
当前时间函数CURRENT_TIME
语法：
CURRENT_TIME
CURRENT_TIME 函数能够取得 SQL 执行的时间，也就是该函数执行时的时间。由于该函数也没有参数，因此同样无需使用括号。
该函数同样无法在 SQL Server 中执行，在 Oracle 和 DB2 中的语法同样略有不同。
取得当前时间：
-- PostgreSQL MySQL
SELECT CURRENT_TIME;
执行结果：
timetz
-----------------
17:26:50.995+09
特定的SQL
SQL Server 使用如下的CURRENT_TIMESTAMP函数（后述）来获得当前日期。
-- 使用CAST函数（后述）将CURRENT_TIMESTAMP转换为时间类型
SELECT CAST(CURRENT_TIMESTAMP AS TIME) AS CUR_TIME;
执行结果：
CUR_TIME
----------------
21:33:59.3400000
在 Oracle 中使用时的语法如下所示。需要注意的地方和CURRENT_DATE函数相同。在Oracle中使用时所得到的结果还包含日期。
-- Oracle
-- 指定临时表（DUAL）
SELECT CURRENT_TIMESTAMP
FROM dual;
在 DB2 中使用时的语法如下所示：
-- DB2
/* CURRENT和TIME之间使用了半角空格，指定临时表SYSIBM.SYSDUMMY1 */
SELECT CURRENT TIME
FROM SYSIBM.SYSDUMMY1;
当前日期和时间CURRENT_TIMESTAMP
语法：
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP 函数具有 CURRENT_DATE + CURRENT_TIME 的功能。使用该函数可以同时得到当前的日期和时间，当然也可以从结果中截取日期或者时间。
该函数可以在 SQL Server 等各个主要的 DBMS 中使用。但是，在 Oracle 和 DB2 中该函数的语法略有不同。
取得当前日期和时间：
-- SQL Server PostgreSQL MySQL
SELECT CURRENT_TIMESTAMP;
执行结果：
now
---------------------------
2016-04-25 18:31:03.704+09
特定的SQL
Oracle和DB2使用如下写法可以得到相同的结果。其中需要注意的地方与CURRENT_DATE时完全相同。
-- Oracle
-- 指定临时表（DUAL）
SELECT CURRENT_TIMESTAMP
FROM dual;
-- DB2
/* CURRENT和TIME之间使用了半角空格，指定临时表SYSIBM.SYSDUMMY1 */
SELECT CURRENT TIMESTAMP
FROM SYSIBM.SYSDUMMY1;
截取日期元素函数EXTRACT
语法：
EXTRACT(日期元素 FROM 日期)
使用EXTRACT函数可以截取出日期数据中的一部分，例如“年”“月”，或者“小时”“秒”等。该函数的返回值并不是日期类型而是数值类型。
截取日期元素：
-- PostgreSQL MySQL
SELECT CURRENT_TIMESTAMP,
EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year,
EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS month,
EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS day,
EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour,
EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS minute,
EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;
执行结果：
now | year | month | day | hour | minute | second
---------------------------+------+-------+-----+------+--------+-------
2010-04-25 19:07:33.987+09 | 2010 | 4 | 25 | 19 | 7 | 33.987
特定的SQL
SQL Server使用如下的DATEPART函数会得到与前面代码相同的结果。
-- SQL Server
SELECT CURRENT_TIMESTAMP,
DATEPART(YEAR , CURRENT_TIMESTAMP) AS year,
DATEPART(MONTH , CURRENT_TIMESTAMP) AS month,
DATEPART(DAY , CURRENT_TIMESTAMP) AS day,
DATEPART(HOUR , CURRENT_TIMESTAMP) AS hour,
DATEPART(MINUTE , CURRENT_TIMESTAMP) AS minute,
DATEPART(SECOND , CURRENT_TIMESTAMP) AS second;
Oracle和DB2想要得到相同结果的话，需要进行如下改变。注意事项与CURRENT_DATE时完全相同。
-- Oracle
-- 在FROM子句中指定临时表（DUAL）
SELECT CURRENT_TIMESTAMP,
EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year,
EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS month,
EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS day,
EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour,
EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS minute,
EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second
FROM DUAL;
-- DB2
/* CURRENT和TIME之间使用了半角空格，指定临时表SYSIBM.SYSDUMMY1 */
SELECT CURRENT TIMESTAMP,
EXTRACT(YEAR FROM CURRENT TIMESTAMP) AS year,
EXTRACT(MONTH FROM CURRENT TIMESTAMP) AS month,
EXTRACT(DAY FROM CURRENT TIMESTAMP) AS day,
EXTRACT(HOUR FROM CURRENT TIMESTAMP) AS hour,
EXTRACT(MINUTE FROM CURRENT TIMESTAMP) AS minute,
EXTRACT(SECOND FROM CURRENT TIMESTAMP) AS second
FROM SYSIBM.SYSDUMMY1;
转换函数
类型转换函数CAST
语法：
CAST（转换前的值 AS 想要转换的数据类型）
进行类型转换需要使用 CAST 函数。
之所以需要进行类型转换，是因为可能会插入与表中数据类型不匹配的数据，或者在进行运算时由于数据类型不一致发生了错误，又或者是进行自动类型转换会造成处理速度低下。这些时候都需要事前进行数据类型转换。
将字符串类型转换为数值类型：
-- SQL Server PostgreSQL
SELECT CAST('0001' AS INTEGER) AS int_col;
-- MySQL
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;
-- Oracle
SELECT CAST('0001' AS INTEGER) AS int_col
FROM DUAL;
-- DB2
SELECT CAST('0001' AS INTEGER) AS int_col
FROM SYSIBM.SYSDUMMY1;
执行结果：
int_col
---------
1
将字符串类型转换为日期类型：
-- SQL Server PostgreSQL MySQL
SELECT CAST('2009-12-14' AS DATE) AS date_col;
-- Oracle
SELECT CAST('2009-12-14' AS DATE) AS date_col
FROM DUAL;
-- DB2
SELECT CAST('2009-12-14' AS DATE) AS date_col
FROM SYSIBM.SYSDUMMY1;
执行结果：
date_col
------------
2009-12-14
从上述结果可以看到，将字符串类型转换为整数类型时，前面的000消失了，能够切实感到发生了转换。但是，将字符串转换为日期类型时，从结果上并不能看出数据发生了什么变化，理解起来也比较困难。类型转换其实并不是为了方便用户使用而开发的功能，而是为了方便 DBMS 内部处理而开发的功能。
将 NULL 转换为其他值：COALESCE函数
语法：
COALESCE(数据1，数据2，数据3……)
COALESCE 是 SQL 特有的函数。该函数会返回可变参数中左侧开始第 1 个不是 NULL 的值。参数个数是可变的，因此可以根据需要无限增加。
其实转换函数的使用还是非常频繁的。在 SQL 语句中将 NULL 转换为其他值时就会用到转换函数。就像之前我们学习的那样，运算或者函数中含有 NULL 时，结果全都会变为 NULL。能够避免这种结果的函数就是 COALESCE。
将 NULL 转换为其他值：
```sql -- SQL Server PostgreSQL MySQL SELECT COALESCE(NULL, 1) AS col_1, COALESCE(NULL, 'test', NULL) AS col_2, COALESCE(NULL, NULL, '2009-11-01') AS col_3;
-- Oracle SELECT COALESCE(NULL, 1) AS col_1, COALESCE(NULL, 'test', NULL) AS col_2, COALESCE(NULL, NULL, '2009-11-01') AS col_3 FROM DUAL;
-- DB2 SELECT COALESCE(NULL, 1) AS col_1, COALESCE(NULL, 'test', NULL) AS col_2, COALESCE(NULL, NULL, '2009-11-01') AS col_3 FROM SYSIBM.SYSDUMMY1; ```
执行结果：
col_1 | col_2 | col_3
-------+-------+-----------
1 | test | 2009-11-01
使用SampleStr 表中的列作为例子：
sql
SELECT COALESCE(str2, 'NULL')
FROM SampleStr;
执行结果：
``` coalesce
rt def 太郎 'NULL' aaa 'NULL' 'NULL' 'NULL' abc abc i ```
这样，即使包含 NULL 的列，也可以通过 COALESCE 函数转换为其他值之后再应用到函数或者运算当中，这样结果就不再是 NULL 了。
此外，多数 DBMS 中都提供了特有的 COALESCE 的简化版函数（如 Oracle 中的 NVL 等），但由于这些函数都依存于各自的 DBMS，因此还是推荐大家使用通用的 COALESCE 函数。
6.2 谓词
- 谓词（predicate）就是返回值为真值的函数。一言以蔽之，谓词的作用就是“判断是否存在满足某种条件的记录”。如果存在这样的记录就返回真（
TRUE），如果不存在就返回假（FALSE）。对通常的函数来说，返回值有可能是数字、字符串或者日期等，但是谓词的返回值全都是真值（TRUE/FALSE/UNKNOWN）。这也是谓词和函数的最大区别。 - 掌握
LIKE的三种使用方法（前方一致、中间一致、后方一致）。 - 需要注意
BETWEEN包含三个参数。 - 想要取得
NULL数据时必须使用IS NULL。 - 可以将子查询作为
IN和EXISTS的参数。
LIKE 谓词——字符串的部分一致查询
当需要进行字符串的部分一致查询时需要使用LIKE谓词。
本节所使用的样例表：
-- DDL ：创建表
CREATE TABLE SampleLike
( strcol VARCHAR(6) NOT NULL,
PRIMARY KEY (strcol));
-- SQL Server PostgreSQL
-- DML ：插入数据
BEGIN TRANSACTION; -- START TRANSACTION;
INSERT INTO SampleLike (strcol) VALUES ('abcddd');
INSERT INTO SampleLike (strcol) VALUES ('dddabc');
INSERT INTO SampleLike (strcol) VALUES ('abdddc');
INSERT INTO SampleLike (strcol) VALUES ('abcdd');
INSERT INTO SampleLike (strcol) VALUES ('ddabc');
INSERT INTO SampleLike (strcol) VALUES ('abddc');
COMMIT;
执行结果：
shop=# SELECT * FROM SampleLike;
strcol
--------
abcddd
dddabc
abdddc
abcdd
ddabc
abddc
(6 行记录)
使用 LIKE 进行前方一致查询
SELECT *
FROM SampleLike
WHERE strcol LIKE 'ddd%';
其中的
%是代表“0 字符以上的任意字符串”的特殊符号，本例中代表“以ddd开头的所有字符串”。
执行结果：
strcol
--------
dddabc
使用 LIKE 进行中间一致查询：
查询出包含字符串“ddd”的记录：
SELECT *
FROM SampleLike
WHERE strcol LIKE '%ddd%';
执行结果：
strcol
--------
abcddd
dddabc
abdddc
使用 LIKE 进行后方一致查询：
选取出以字符串“ddd”结尾的记录：
SELECT *
FROM SampleLike
WHERE strcol LIKE '%ddd';
执行结果：
strcol
--------
abcddd
使用 LIKE 和 _（下划线）进行后方一致查询
选取出 strcol 列的值为“abc+ 任意 2 个字符”的记录：
SELECT *
FROM SampleLike
WHERE strcol LIKE 'abc__';
此外，我们还可以使用
_（下划线）来代替%，与%不同的是，它代表了“任意 1 个字符”。
执行结果：
strcol
--------
abcdd
查询“abc+任意 3 个字符”的字符串：
SELECT *
FROM SampleLike
WHERE strcol LIKE 'abc___';
执行结果：
strcol
--------
abcddd
BETWEEN 谓词——范围查询
选取销售单价为100 ～ 1000日元的商品
SELECT product_name, sale_price
FROM Product
WHERE sale_price BETWEEN 100 AND 1000;
执行结果：
product_name | sale_price
-----------0-+-------------
T恤衫 | 1000
打孔器 | 500
叉子 | 500
擦菜板 | 880
圆珠笔 | 100
BETWEEN 的特点就是结果中会包含 100 和 1000 这两个临界值。如果不想让结果中包含临界值，那就必须使用 < 和 >。
IS NULL、IS NOT NULL——判断是否为NULL
为了选取出某些值为 NULL 的列的数据，不能使用 =，而只能使用特定的谓词 IS NULL。
选取出进货单价（purchase_price）为NULL的商品：
SELECT product_name, purchase_price
FROM Product
WHERE purchase_price IS NULL;
执行结果：
product_name | purchase_price
-------------+-------------
叉子 |
圆珠笔 |
与此相反，想要选取 NULL 以外的数据时，需要使用 IS NOT NULL。
选取进货单价（purchase_price）不为 NULL 的商品
SELECT product_name, purchase_price
FROM Product
WHERE purchase_price IS NOT NULL;
执行结果：
product_name | purchase_price
-------------+---------------
T恤衫 | 500
打孔器 | 320
运动T恤 | 2800
菜刀 | 2800
高压锅 | 5000
擦菜板 | 790
IN 谓词——OR 的简便用法
通过 OR 指定多个进货单价进行查询：
选取出进货单价（purchase_price）为 320 日元、500 日元、5000 日元的商品。
SELECT product_name, purchase_price
FROM Product
WHERE purchase_price = 320
OR purchase_price = 500
OR purchase_price = 5000;
执行结果：
product_name | purchase_price
-------------+---------------
T恤衫 | 500
打孔器 | 320
高压锅 | 5000
通过 IN 来指定多个进货单价进行查询
SELECT product_name, purchase_price
FROM Product
WHERE purchase_price IN (320, 500, 5000);
执行结果：
product_name | purchase_price
-------------+---------------
T恤衫 | 500
打孔器 | 320
高压锅 | 5000
反之，希望选取出“进货单价不是 320 日元、500 日元、5000 日元”的商品时，可以使用否定形式 NOT IN 来实现。
使用NOT IN 进行查询时指定多个排除的进货单价进行查询：
SELECT product_name, purchase_price
FROM Product
WHERE purchase_price NOT IN (320, 500, 5000);
执行结果：
product_name | purchase_price
-------------+---------------
运动T恤 | 2800
菜刀 | 2800
擦菜板 | 790
注意，在使用 IN 和 NOT IN 时是无法选取出 NULL 数据的。
使用子查询作为 IN 谓词的参数
本节中使用的样例表：
CREATE TABLE ShopProduct
(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));
-- SQL Server PostgreSQL
BEGIN TRANSACTION; --------①
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0001', 30);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0002', 50);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0003', 15);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0002', 30);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0003', 120);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0004', 20);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0006', 10);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0007', 40);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0003', 20);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0004', 50);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0006', 90);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0007', 70);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000D', '福冈', '0001', 100);
COMMIT;
执行结果：
shop=# SELECT * FROM ShopProduct;
shop_id | shop_name | product_id | quantity
---------+-----------+------------+----------
000A | 东京 | 0001 | 30
000A | 东京 | 0002 | 50
000A | 东京 | 0003 | 15
000B | 名古屋 | 0002 | 30
000B | 名古屋 | 0003 | 120
000B | 名古屋 | 0004 | 20
000B | 名古屋 | 0006 | 10
000B | 名古屋 | 0007 | 40
000C | 大阪 | 0003 | 20
000C | 大阪 | 0004 | 50
000C | 大阪 | 0006 | 90
000C | 大阪 | 0007 | 70
000D | 福冈 | 0001 | 100
(13 行记录)
IN 和子查询
使用子查询作为IN的参数：
-- 取得“在大阪店销售的商品的销售单价”
SELECT product_name, sale_price
FROM Product
WHERE product_id IN (SELECT product_id
FROM ShopProduct
WHERE shop_id = '000C');
执行结果：
product_name | sale_price
--------------+------------
叉子 | 500
运动T恤 | 4000
菜刀 | 3000
擦菜板 | 880
该 SELECT 语句也是从内层的子查询开始执行，然后像下面这样展开。
-- 子查询展开后的结果
SELECT product_name, sale_price
FROM Product
WHERE product_id IN ('0003', '0004', '0006', '0007');
NOT IN 和子查询
IN 的否定形式 NOT IN 同样可以使用子查询作为参数，其语法也和 IN 完全一样。
使用子查询作为 NOT IN 的参数：
SELECT product_name, sale_price
FROM Product
WHERE product_id NOT IN (SELECT product_id
FROM ShopProduct
WHERE shop_id = '000A');
执行结果：
product_name | sale_price
--------------+-----------
菜刀 | 3000
高压锅 | 6800
叉子 | 500
擦菜板 | 880
圆珠笔 | 100
注意：NOT IN 的参数中不能包含 NULL，NOT IN 的参数中包含 NULL 时结果通常会为空，也就是无法选取出任何记录。不仅仅是指定 NULL 的情况，使用子查询作为 NOT IN 的参数时，该子查询的返回值也不能是 NULL。
SELECT product_name, purchase_price
FROM Product
WHERE purchase_price NOT IN (NULL);
执行结果：
product_name | purchase_price
--------------+----------------
(0 行记录)
EXIST 谓词
EXIST 谓词的使用方法
使用 EXIST 选取出“大阪店（000C）在售商品（product_id）的销售单价（sale_price）”：
-- SQL Server DB2 PostgreSQL MySQL
SELECT product_name, sale_price
FROM Product AS P -- Oracle的FROM子句中不能使用AS（会发生错误）
WHERE EXISTS (SELECT *
FROM ShopProduct AS SP -- Oracle的FROM子句中不能使用AS（会发生错误）
WHERE SP.shop_id = '000C'
AND SP.product_id = P.product_id);
执行结果：
product_name | sale_price
-------------+-------------
叉子 | 500
运动T恤 | 4000
菜刀 | 3000
擦菜板 | 880
特定的SQL
Oracle的FROM子句中不能使用AS（会发生错误）：
-- Oracle
SELECT product_name, sale_price
FROM Product P -- Oracle的FROM子句中不能使用AS（会发生错误）
WHERE EXISTS (SELECT *
FROM ShopProduct SP -- Oracle的FROM子句中不能使用AS（会发生错误）
WHERE SP.shop_id = '000C'
AND SP.product_id = P.product_id);
EXIST的参数
EXIST 只需要在右侧书写 1 个参数，该参数通常都会是一个关联子查询。
子查询中的 SELECT *
大家可以把在 EXIST 的子查询中书写 SELECT * 当作 SQL 的一种习惯，EXIST并不关心具体是哪些列被返回。EXIST 只会判断是否存在满足子查询中 WHERE子句指定的条件“商店编号（shop_id）为 '000C'，商品（Product）表和商店商品（ShopProduct）表中商品编号（product_id）相同”的记录，只有存在这样的记录时才返回真（TRUE）。
因此，即使写成下面这样，结果也不会发生改变：
代码清单 6-39 这样的写法也能得到与代码清单 6-38 相同的结果
-- SQL Server DB2 PostgreSQL MySQL
SELECT product_name, sale_price
FROM Product AS P -- Oracle的FROM子句中不能使用AS（会发生错误）
WHERE EXISTS (SELECT 1 -- 这里可以书写适当的常数，因为EXIST并不关心具体是哪些列被返回。
FROM ShopProduct AS SP -- Oracle的FROM子句中不能使用AS（会发生错误）
WHERE SP.shop_id = '000C'
AND SP.product_id = P.product_id);
执行结果：
product_name | sale_price
-------------+-------------
叉子 | 500
运动T恤 | 4000
菜刀 | 3000
擦菜板 | 880
使用 NOT EXIST替换 NOT IN
就像 EXIST 可以用来替换 IN 一样，NOT IN 也可以用 NOT EXIST 来替换。NOT EXIST 与 EXIST 相反，当“不存在”满足子查询中指定条件的记录时返回真（TRUE）。
使用 NOT EXIST 来编写一条 SELECT 语句，读取出“东京店（000A）在售之外的商品（product_id）的销售单价（sale_price）”：
-- SQL Server DB2 PostgreSQL MySQL
SELECT product_name, sale_price
FROM Product AS P -- Oracle的FROM子句中不能使用AS（会发生错误）
WHERE NOT EXISTS (SELECT *
FROM ShopProduct AS SP -- Oracle的FROM子句中不能使用AS（会发生错误）
WHERE SP.shop_id = '000A'
AND SP.product_id = P.product_id);
执行结果：
product_name | sale_price
-------------+------------
菜刀 | 3000
高压锅 | 6800
叉子 | 500
擦菜板 | 880
圆珠笔 | 100
6.3 CASE表达式
和“1 + 1”或者“120 / 4”这样的表达式一样，CASE 表达式是一种进行运算的功能。这就意味着 CASE 表达式也是函数的一种。CASE 表达式是在区分情况时使用的，这种情况的区分在编程中通常称为（条件）分支。
CASE表达式分为简单CASE表达式和搜索CASE表达式两种。搜索CASE表达式包含简单CASE表达式的全部功能。- 虽然
CASE表达式中的ELSE子句可以省略，但为了让SQL语句更加容易理解，还是希望大家不要省略。 CASE表达式中的END不能省略。- 使用
CASE表达式能够将SELECT语句的结果进行组合。 - 虽然有些DBMS提供了各自特有的
CASE表达式的简化函数，例如Oracle中的DECODE和MySQL中的IF，等等，但由于它们并非通用的函数，功能上也有些限制，因此有些场合无法使用。
CASE 表达式的语法
CASE 表达式的语法分为简单 CASE 表达式和搜索 CASE 表达式两种。
搜索 CASE 表达式语法
CASE WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
.
.
.
ELSE <表达式>
END
WHEN 子句中的“< 求值表达式 >”就是类似“列 = 值”这样，返回值为真值（TRUE/FALSE/UNKNOWN）的表达式。我们也可以将其看作使用 =、!= 或者 LIKE、BETWEEN 等谓词编写出来的表达式。
CASE 表达式会从对最初的 WHEN 子句中的< 求值表达式 >进行求值开始执行。如果结果为真（TRUE），那么就返回 THEN 子句中的表达式，CASE 表达式的执行到此为止。如果结果不为真，那么就跳转到下一条 WHEN 子句的求值之中。如果直到最后的 WHEN 子句为止返回结果都不为真，那么就会返回 ELSE 中的表达式，执行终止。
简单CASE表达式语法
简单 CASE 表达式比搜索 CASE 表达式简单，但是会受到条件的约束，因此通常情况下都会使用搜索 CASE 表达式。
简单CASE表达式语法：
CASE <表达式>
WHEN <表达式> THEN <表达式>
WHEN <表达式> THEN <表达式>
WHEN <表达式> THEN <表达式>
.
.
.
ELSE <表达式>
END
与搜索 CASE 表达式一样，简单 CASE 表达式也是从最初的 WHEN 子句开始进行，逐一判断每个 WHEN 子句直到返回真值为止。此外，没有能够返回真值的 WHEN 子句时，也会返回 ELSE 子句指定的表达式。两者的不同之处在于，简单 CASE 表达式最初的“CASE< 表达式 >”也会作为求值的对象。
CASE 表达式的使用方法
搜索CASE 表达式的使用方法
现在 Product（商品）表中包含衣服、办公用品和厨房用具 3 种商品类型，请大家考虑一下怎样才能够得到如下结果。
A ：衣服
B ：办公用品
C ：厨房用具
通过 CASE 表达式将 A ～ C的字符串加入到商品种类当中：
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;
执行结果：
product_name | abc_product_type
--------------+------------------
T恤 | A:衣服
打孔器 | B:办公用品
运动T恤 | A:衣服
菜刀 | C:厨房用具
高压锅 | C:厨房用具
叉子 | C:厨房用具
擦菜板 | C:厨房用具
圆珠笔 | B:办公用品
CASE 表达式的书写位置
CASE 表达式是一个表达式，而表达式可以书写在任意位置，也就是像“1 + 1”这样写在什么位置都可以。
使用 CASE 表达式进行行列转换：
-- 对按照商品种类计算出的销售单价合计值进行行列转换
SELECT SUM(CASE WHEN product_type = '衣服'
THEN sale_price ELSE 0 END) AS sum_price_clothes,
SUM(CASE WHEN product_type = '厨房用具'
THEN sale_price ELSE 0 END) AS sum_price_kitchen,
SUM(CASE WHEN product_type = '办公用品'
THEN sale_price ELSE 0 END) AS sum_price_office
FROM Product;
执行结果：
sum_price_clothes | sum_price_kitchen | sum_price_office
------------------+-------------------+-----------------
5000 | 11180 | 600
上述结果是根据商品种类计算出的销售单价的合计值，通常我们将商品种类列作为 GROUP BY 子句的聚合键来使用，但是这样得到的结果会以“行”的形式输出，而无法以列的形式进行排列。
通常使用 GROUP BY 也无法实现行列转换：
SELECT product_type,
SUM(sale_price) AS sum_price
FROM Product
GROUP BY product_type;
执行结果：
product_type | sum_price
--------------+----------
衣服 | 5000
办公用品 | 600
厨房用具 | 11180
简单CASE 表达式的使用方法
下面就让我们来看一看搜索 CASE 表达式和简单 CASE 表达式是如何实现相同含义的 SQL 语句的。将前面的搜索 CASE 表达式的 SQL 改写为简单 CASE 表达式，结果如下所示。
使用CASE 表达式将字符串A ～ C 添加到商品种类中：
-- 使用搜索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 子句中重复书写“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;
特定的CASE表达式
由于 CASE 表达式是标准 SQL 所承认的功能，因此在任何 DBMS 中都可以执行。但是，有些 DBMS 还提供了一些特有的 CASE 表达式的简化函数，例如 Oracle 中的 DECODE、MySQL 中的 IF 等。
使用 Oracle 中的 DECODE 将字符串 A ～ C 添加到商品种类（product_type）中的 SQL 语句：
代码清单 6-B 使用 CASE 表达式的特定语句将字符串 A ～ C 添加到商品种类中
Oracle
-- Oracle中使用DECODE代替CASE表达式
SELECT product_name,
DECODE(product_type,
'衣服', 'A ：' || product_type,
'办公用品', 'B ：' || product_type,
'厨房用具', 'C ：' || product_type,
NULL) AS abc_product_type
FROM Product;
使用 MySQL 中的 IF 将字符串 A ～ C 添加到商品种类（product_type）中的 SQL 语句：
MySQL
-- MySQL中使用IF代替CASE表达式
SELECT product_name,
IF( IF( IF(product_type = '衣服',
CONCAT('A ：', product_type), NULL)
IS NULL AND product_type = '办公用品',
CONCAT('B ：', product_type),
IF(product_type = '衣服',
CONCAT('A ：', product_type), NULL))
IS NULL AND product_type = '厨房用具',
CONCAT('C ：', product_type),
IF( IF(product_type = '衣服',
CONCAT('A ：', product_type), NULL)
IS NULL AND product_type = '办公用品',
CONCAT('B ：', product_type),
IF(product_type = '衣服',
CONCAT('A ：', product_type),
NULL))) AS abc_product_type
FROM Product;
练习题
6.2 按照销售单价（sale_price）对 Product（商品）表中的商品进行如下分类。
- 低档商品 ：销售单价在 1000 日元以下（T 恤衫、办公用品、叉子、擦菜板、圆珠笔）
- 中档商品 ：销售单价在 1001 日元以上 3000 日元以下（菜刀）
- 高档商品 ：销售单价在 3001 日元以上（运动 T 恤、高压锅）
请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句，结果如下所示。
执行结果：
low_price | mid_price | high_price
-----------+-----------+----------
5 | 1 | 2
答案：
SELECT SUM(CASE WHEN sale_price <= 1000
THEN 1 ELSE 0 END) AS low_price,
SUM(CASE WHEN sale_price BETWEEN 1001 AND 3000
THEN 1 ELSE 0 END) AS mid_price,
SUM(CASE WHEN sale_price >= 3001
THEN 1 ELSE 0 END) AS high_price
FROM Product;
Chapter 7 集合运算
通过以行方向（竖）为单位的集合运算符和以列方向（横）为单位的联结，就可以将分散在多张表中的数据组合成为期望的结果。
7.1 表的加减法
- 集合运算就是对满足同一规则的记录进行的加减等四则运算。
- 使用
UNION（并集）、INTERSECT（交集）、EXCEPT（差集）等集合运算符来进行集合运算。 - 集合运算符可以去除重复行。
- 如果希望集合运算符保留重复行，就需要使用
ALL选项。
什么是集合运算
集合在数学领域表示“（各种各样的）事物的总和”，在数据库领域表示记录的集合。具体来说，表、视图和查询的执行结果都是记录的集合。
所谓集合运算，就是对满足同一规则的记录进行的加减等四则运算。通过集合运算，可以得到两张表中记录的集合或者公共记录的集合，又或者其中某张表中的记录的集合。像这样用来进行集合运算的运算符称为集合运算符。
集合运算的注意事项
- 作为运算对象的记录的列数必须相同。
- 作为运算对象的记录中列的类型必须一致。
- 可以使用任何
SELECT语句，但ORDER BY子句只能在最后使用一次。
表的加法运算：UNION（并集）
本节所使用的样例表Product2，其结构与Product相同，只是插入的数据中，头3条与Product表中的相同，其余2条数据是Product表中所没有的：
CREATE TABLE Product2
(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));
--SQL Server PostgreSQL
BEGIN TRANSACTION; --START TRANSACTION;
INSERT INTO Product2 VALUES ('0001', 'T恤' ,'衣服', 1000, 500, '2009-09-20');
INSERT INTO Product2 VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO Product2 VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO Product2 VALUES ('0009', '手套', '衣服', 800, 500, NULL);
INSERT INTO Product2 VALUES ('0010', '水壶', '厨房用具', 2000, 1700, '2009-09-20');
COMMIT;
使用 UNION 对上述两张表进行“Product 表 + Product2 表”的加法运算：
SELECT product_id, product_name
FROM Product
UNION
SELECT product_id, product_name
FROM Product2;
执行结果：
product_id | product_name
------------+--------------
0006 | 叉子
0001 | T恤
0002 | 打孔器
0007 | 擦菜板
0003 | 运动T恤
0004 | 菜刀
0005 | 高压锅
0008 | 圆珠笔
0010 | 水壶
0009 | 手套
(10 行记录)
上述结果包含了两张表中的全部商品。这就是集合中的并集运算，通过文氏图会看得更清晰：
注意，集合运算符会除去重复的记录。
包含重复行的集合运算：ALL 选项
要想在 UNION 的结果中保留重复行，只需要在 UNION 后面添加 ALL关键字就可以了。这里的 ALL 选项，在 UNION 之外的集合运算符中同样可以使用。
保留重复行：
SELECT product_id, product_name
FROM Product
UNION ALL
SELECT product_id, product_name
FROM Product2;
执行结果：
product_id | product_name
------------+--------------
0001 | T恤
0002 | 打孔器
0003 | 运动T恤
0004 | 菜刀
0005 | 高压锅
0006 | 叉子
0007 | 擦菜板
0008 | 圆珠笔
0001 | T恤
0002 | 打孔器
0003 | 运动T恤
0009 | 手套
0010 | 水壶
(13 行记录)
选取表中公共部分：INTERSECT
集合运算符INTERSECT能够选取两个记录集合中公共部分的 INTERSECT（交集）。
希望保留重复行时同样需要使用 INTERSECT ALL。
使用 INTERSECT 选取出表中公共部分：
-- Oracle SQL Server DB2 PostgreSQL
SELECT product_id, product_name
FROM Product
INTERSECT
SELECT product_id, product_name
FROM Product2
ORDER BY product_id;
执行结果：
product_id | product_name
------------+--------------
0001 | T恤
0002 | 打孔器
0003 | 运动T恤
可以看到，结果中只包含两张表中记录的公共部分。该运算的文氏图如下所示：
记录的减法：EXCEPT
最后要给大家介绍的集合运算符就是进行减法运算的 EXCEPT（差集），其语法也与 UNION 相同。
只有 Oracle 不使用
EXCEPT，而是使用其特有的MINUS运算符。使用 Oracle 的用户，请用MINUS代替EXCEPT。此外，MySQL 还不支持EXCEPT，因此也无法使用。
使用EXCEPT 对记录进行减法运算：
-- SQL Server DB2 PostgreSQL
-- 从Product的记录中除去Product2中的记录
SELECT product_id, product_name
FROM Product
EXCEPT
SELECT product_id, product_name
FROM Product2
ORDER BY product_id;
执行结果：
product_id | product_name
------------+--------------
0004 | 菜刀
0005 | 高压锅
0006 | 叉子
0007 | 擦菜板
0008 | 圆珠笔
特定的 SQL
在Oracle中请将EXCEPT改为MINUS：
-- Oracle中使用MINUS而不是EXCEPT
SELECT …
FROM …
MINUS
SELECT …
FROM …;
可以看到，结果中只包含 Product 表中记录除去 Product2 表中记录之后的剩余部分。该运算的文氏图如图：
被减数和减数位置不同，得到的结果也不同：
-- SQL Server DB2 PostgreSQL
-- 从Product2的记录中除去Product中的记录
SELECT product_id, product_name
FROM Product2
EXCEPT
SELECT product_id, product_name
FROM Product
ORDER BY product_id;
执行结果：
product_id | product_name
------------+--------------
0009 | 手套
0010 | 水壶
上述运算的文氏图如图所示：
7.2 联结（以列为单位对表进行联结）
- 联结（
JOIN）就是将其他表中的列添加过来，进行“添加列”的集合运算。UNION是以行（纵向）为单位进行操作，而联结则是以列（横向）为单位进行的。 - 联结大体上分为内联结和外联结两种。
- 请大家一定要使用标准SQL的语法格式来写联结运算，对于那些过时的或者特定 SQL 中的写法，了解一下即可，不建议使用。
什么是联结
前一节我们学习了 UNION 和 INTERSECT 等集合运算，这些集合运算的特征就是以行方向为单位进行操作。通俗地说，就是进行这些集合运算时，会导致记录行数的增减。使用 UNION 会增加记录行数，而使用 INTERSECT 或者 EXCEPT 会减少记录行数。但是这些运算不会导致列数的改变，所以作为集合运算对象的表的前提就是列数要一致。
联结（JOIN） 运算，简单来说，就是将其他表中的列添加过来，进行“添加列”的运算（如下图所示）。该操作通常用于期望得到的数据（列）分散在不同表中的情况。使用联结就可以从多张表中选取数据了。
内联结：INNER JOIN
本节将使用Product表和ShopProduct表：
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));
BEGIN TRANSACTION;
INSERT INTO Product VALUES ('0001', 'T恤' ,'衣服', 1000, 500, '2009-09-20');
INSERT INTO Product VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO Product VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO Product VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO Product VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO Product VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO Product VALUES ('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO Product VALUES ('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');
COMMIT;
-- DDL：创建表
CREATE TABLE ShopProduct
(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));
-- DML：插入数据
BEGIN TRANSACTION;
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0001', 30);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0002', 50);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0003', 15);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0002', 30);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0003', 120);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0004', 20);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0006', 10);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0007', 40);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0003', 20);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0004', 50);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0006', 90);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0007', 70);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000D', '福冈', '0001', 100);
COMMIT;
执行结果：
shop=# SELECT * FROM Product;
product_id | product_name | product_type | sale_price | purchase_price | regist_date
------------+--------------+--------------+------------+----------------+-------------
0001 | T恤 | 衣服 | 1000 | 500 | 2009-09-20
0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11
0003 | 运动T恤 | 衣服 | 4000 | 2800 |
0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20
0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15
0006 | 叉子 | 厨房用具 | 500 | | 2009-09-20
0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28
0008 | 圆珠笔 | 办公用品 | 100 | | 2009-11-11
shop=# SELECT * FROM ShopProduct;
shop_id | shop_name | product_id | quantity
---------+-----------+------------+----------
000A | 东京 | 0001 | 30
000A | 东京 | 0002 | 50
000A | 东京 | 0003 | 15
000B | 名古屋 | 0002 | 30
000B | 名古屋 | 0003 | 120
000B | 名古屋 | 0004 | 20
000B | 名古屋 | 0006 | 10
000B | 名古屋 | 0007 | 40
000C | 大阪 | 0003 | 20
000C | 大阪 | 0004 | 50
000C | 大阪 | 0006 | 90
000C | 大阪 | 0007 | 70
000D | 福冈 | 0001 | 100
如上表所示，两张表中的列可以分为如下两类：
-
两张表中都包含
product_id列； -
除了
product_id列之外，其他列都只存在于一张表内；
所谓联结运算，一言以蔽之，就是“以多张表都包含的列作为桥梁，从各自存在于多张表中不同的列中选取满足同样条件的列汇集到同一结果之中”。
将两张表进行内联结，从 Product 表中取出商品名称（product_name）和销售单价（sale_price），并与 ShopProduct 表中的内容进行结合：
-- SQL Server DB2 PostgreSQL MySQL
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 -- 在Oracle的FROM子句中不能使用AS（会发生错误）
ON SP.product_id = P.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
特定的SQL
-- Oracle
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
FROM ShopProduct SP INNER JOIN Product P -- 在Oracle的FROM子句中不能使用AS（会发生错误）
ON SP.product_id = P.product_id;
内联结的注意点
- 在
FROM子句中使用INNER JOIN子句将多张表联结在一起，并可以使用AS为多张表设置别名，但别名并不是必须的。 ON子句是专门用来指定联结条件的，在ON之后指定多张表联结所使用的列（联结键），本例中使用的是product_id。需要指定多个键时，可以使用AND、OR。在进行内联结时ON子句是必不可少的（如果没有ON会发生错误），并且ON必须书写在FROM和WHERE之间。- 在
SELECT子句中，像SP.shop_id和P.sale_price这样使用< 表的别名 >.< 列名 >的形式来指定列。从语法上来说，只有那些同时存在于两张表中的列（这里是product_id）必须使用这样的书写方式，其他的列像shop_id这样直接书写列名也不会发生错误。
内联结和 WHERE 子句结合使用
如果并不想了解所有商店的情况，例如只想知道东京店（000A）的信息时，可以像之前学习的那样在 WHERE 子句中添加条件，这样我们就可以选取出东京店的记录了。
-- SQL Server DB2 PostgreSQL MySQL
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 -- 在Oracle的FROM子句中不能使用AS（会发生错误）
ON SP.product_id = P.product_id
WHERE SP.shop_id = '000A';
执行结果：
shop_id | shop_name | product_id | product_name | sale_price
---------+-----------+------------+--------------+-----------
000A | 东京 | 0001 | T恤衫 | 1000
000A | 东京 | 0002 | 打孔器 | 500
000A | 东京 | 0003 | 运动T恤 | 4000
注意，像这样使用联结运算将满足相同规则的表联结起来时，WHERE、GROUP BY、HAVING、ORDER BY等工具都可以正常使用。我们可以将联结之后的结果想象为新创建出来的一张临时表，对这张表使用 WHERE 子句等工具。
也就是说，具有WHERE SP.shop_id = '000A'子句的查询结果，实际上是对以下SQL语句结果的条件过滤：
-- SQL Server DB2 PostgreSQL MySQL
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 -- 在Oracle的FROM子句中不能使用AS（会发生错误）
ON SP.product_id = P.product_id
外联结：OUTER JOIN
把之前内联结的 SELECT 语句转换为外联结：
-- SQL Server DB2 PostgreSQL MySQL
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
FROM ShopProduct AS SP RIGHT OUTER JOIN Product AS P -- 在Oracle的FROM子句中不能使用AS（会发生错误）
ON SP.product_id = P.product_id;
特定的SQL：
-- Oracle
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
FROM ShopProduct SP RIGHT OUTER JOIN Product P -- 在Oracle的FROM子句中不能使用AS（会发生错误）
ON SP.product_id = P.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
| | | 圆珠笔 | 100
| | | 高压锅 | 6800
外联结的注意点
- 内联结只能选取出同时存在于多张表中的数据，外联结取出单张表中全部的数据。
- 最终的结果中会包含主表内所有的数据。 指定主表的关键字是
LEFT和RIGHT。顾名思义，使用LEFT时FROM子句中写在左侧的表是主表，使用RIGHT时右侧的表是主表。
改写后外联结的结果完全相同：
-- SQL Server DB2 PostgreSQL MySQL
SELECT SP.shop_id, SP.shop_name, P.product_id, P.product_name, P.sale_price
FROM Product AS P LEFT OUTER JOIN ShopProduct AS SP -- 在Oracle的FROM子句中不能使用AS（会发生错误）
ON SP.product_id = P.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
| | 0005 | 高压锅 | 6800
3 张以上的表的联结
通常联结只涉及 2 张表，但有时也会出现必须同时联结 3 张以上的表的情况。原则上联结表的数量并没有限制。
本节中使用到的第3张表InventoryProduct：
-- DDL ：创建表
CREATE TABLE InventoryProduct
( inventory_id CHAR(4) NOT NULL,
product_id CHAR(4) NOT NULL,
inventory_quantity INTEGER NOT NULL,
PRIMARY KEY (inventory_id, product_id));
-- SQL Server PostgreSQL
-- DML ：插入数据
BEGIN TRANSACTION; -- START TRANSACTION;
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P001', '0001', 0);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P001', '0002', 120);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P001', '0003', 200);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P001', '0004', 3);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P001', '0005', 0);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P001', '0006', 99);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P001', '0007', 999);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P001', '0008', 200);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P002', '0001', 10);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P002', '0002', 25);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P002', '0003', 34);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P002', '0004', 19);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P002', '0005', 99);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P002', '0006', 0);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P002', '0007', 0);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P002', '0008', 18);
COMMIT;
执行结果：
shop=# SELECT * FROM InventoryProduct;
inventory_id | product_id | inventory_quantity
--------------+------------+--------------------
P001 | 0001 | 0
P001 | 0002 | 120
P001 | 0003 | 200
P001 | 0004 | 3
P001 | 0005 | 0
P001 | 0006 | 99
P001 | 0007 | 999
P001 | 0008 | 200
P002 | 0001 | 10
P002 | 0002 | 25
P002 | 0003 | 34
P002 | 0004 | 19
P002 | 0005 | 99
P002 | 0006 | 0
P002 | 0007 | 0
P002 | 0008 | 18
(16 行记录)
从上表中取出保存在 P001 仓库中的商品数量，并将该列添加到前面内联结代码所得到的结果中。联结方式为内联结（外联结的使用方法完全相同），联结键为商品编号（product_id）。
--SQL Server, DB2, PostgreSQL, MySQL
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price, IP.inventory_quantity
FROM ShopProduct AS SP INNER JOIN Product AS P -- 在Oracle的FROM子句中不能使用AS（会发生错误）
ON SP.product_id = P.product_id
INNER JOIN InventoryProduct AS IP -- 在Oracle的FROM子句中不能使用AS（会发生错误）
ON SP.product_id = IP.product_id
WHERE IP.inventory_id = 'P001';
执行结果：
shop_id | shop_name | product_id | product_name | sale_price | inventory_quantity
---------+-----------+------------+--------------+------------+--------------------
000A | 东京 | 0001 | T恤 | 1000 | 0
000A | 东京 | 0002 | 打孔器 | 500 | 120
000A | 东京 | 0003 | 运动T恤 | 4000 | 200
000B | 名古屋 | 0002 | 打孔器 | 500 | 120
000B | 名古屋 | 0003 | 运动T恤 | 4000 | 200
000B | 名古屋 | 0004 | 菜刀 | 3000 | 3
000B | 名古屋 | 0006 | 叉子 | 500 | 99
000B | 名古屋 | 0007 | 擦菜板 | 880 | 999
000C | 大阪 | 0003 | 运动T恤 | 4000 | 200
000C | 大阪 | 0004 | 菜刀 | 3000 | 3
000C | 大阪 | 0006 | 叉子 | 500 | 99
000C | 大阪 | 0007 | 擦菜板 | 880 | 999
000D | 福冈 | 0001 | T恤 | 1000 | 0
交叉联结：CROSS JOIN (笛卡尔积)
交叉联结（CROSS JOIN）是所有联结运算的基础，同时交叉联结就是集合运算中的乘法。但其实这种联结在实际业务中基本使用不到。
将 Product 表和 ShopProduct 表进行交叉联结：
-- SQL Server DB2 PostgreSQL MySQL
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name
FROM ShopProduct AS SP CROSS JOIN Product AS P; -- 在Oracle的FROM子句中不能使用AS（会发生错误）
执行结果：
shop_id | shop_name | product_id | product_name
---------+-----------+------------+--------------
000A | 东京 | 0001 | T恤
000A | 东京 | 0002 | T恤
000A | 东京 | 0003 | T恤
000B | 名古屋 | 0002 | T恤
000B | 名古屋 | 0003 | T恤
000B | 名古屋 | 0004 | T恤
000B | 名古屋 | 0006 | T恤
000B | 名古屋 | 0007 | T恤
000C | 大阪 | 0003 | T恤
000C | 大阪 | 0004 | T恤
000C | 大阪 | 0006 | T恤
000C | 大阪 | 0007 | T恤
000D | 福冈 | 0001 | T恤
000A | 东京 | 0001 | 打孔器
000A | 东京 | 0002 | 打孔器
000A | 东京 | 0003 | 打孔器
000B | 名古屋 | 0002 | 打孔器
000B | 名古屋 | 0003 | 打孔器
000B | 名古屋 | 0004 | 打孔器
000B | 名古屋 | 0006 | 打孔器
000B | 名古屋 | 0007 | 打孔器
000C | 大阪 | 0003 | 打孔器
000C | 大阪 | 0004 | 打孔器
000C | 大阪 | 0006 | 打孔器
000C | 大阪 | 0007 | 打孔器
000D | 福冈 | 0001 | 打孔器
000A | 东京 | 0001 | 运动T恤
000A | 东京 | 0002 | 运动T恤
000A | 东京 | 0003 | 运动T恤
000B | 名古屋 | 0002 | 运动T恤
000B | 名古屋 | 0003 | 运动T恤
000B | 名古屋 | 0004 | 运动T恤
000B | 名古屋 | 0006 | 运动T恤
000B | 名古屋 | 0007 | 运动T恤
000C | 大阪 | 0003 | 运动T恤
000C | 大阪 | 0004 | 运动T恤
000C | 大阪 | 0006 | 运动T恤
000C | 大阪 | 0007 | 运动T恤
000D | 福冈 | 0001 | 运动T恤
000A | 东京 | 0001 | 菜刀
000A | 东京 | 0002 | 菜刀
000A | 东京 | 0003 | 菜刀
000B | 名古屋 | 0002 | 菜刀
000B | 名古屋 | 0003 | 菜刀
000B | 名古屋 | 0004 | 菜刀
000B | 名古屋 | 0006 | 菜刀
000B | 名古屋 | 0007 | 菜刀
000C | 大阪 | 0003 | 菜刀
000C | 大阪 | 0004 | 菜刀
000C | 大阪 | 0006 | 菜刀
000C | 大阪 | 0007 | 菜刀
000D | 福冈 | 0001 | 菜刀
000A | 东京 | 0001 | 高压锅
000A | 东京 | 0002 | 高压锅
000A | 东京 | 0003 | 高压锅
000B | 名古屋 | 0002 | 高压锅
000B | 名古屋 | 0003 | 高压锅
000B | 名古屋 | 0004 | 高压锅
000B | 名古屋 | 0006 | 高压锅
000B | 名古屋 | 0007 | 高压锅
000C | 大阪 | 0003 | 高压锅
000C | 大阪 | 0004 | 高压锅
000C | 大阪 | 0006 | 高压锅
000C | 大阪 | 0007 | 高压锅
000D | 福冈 | 0001 | 高压锅
000A | 东京 | 0001 | 叉子
000A | 东京 | 0002 | 叉子
000A | 东京 | 0003 | 叉子
000B | 名古屋 | 0002 | 叉子
000B | 名古屋 | 0003 | 叉子
000B | 名古屋 | 0004 | 叉子
000B | 名古屋 | 0006 | 叉子
000B | 名古屋 | 0007 | 叉子
000C | 大阪 | 0003 | 叉子
000C | 大阪 | 0004 | 叉子
000C | 大阪 | 0006 | 叉子
000C | 大阪 | 0007 | 叉子
000D | 福冈 | 0001 | 叉子
000A | 东京 | 0001 | 擦菜板
000A | 东京 | 0002 | 擦菜板
000A | 东京 | 0003 | 擦菜板
000B | 名古屋 | 0002 | 擦菜板
000B | 名古屋 | 0003 | 擦菜板
000B | 名古屋 | 0004 | 擦菜板
000B | 名古屋 | 0006 | 擦菜板
000B | 名古屋 | 0007 | 擦菜板
000C | 大阪 | 0003 | 擦菜板
000C | 大阪 | 0004 | 擦菜板
000C | 大阪 | 0006 | 擦菜板
000C | 大阪 | 0007 | 擦菜板
000D | 福冈 | 0001 | 擦菜板
000A | 东京 | 0001 | 圆珠笔
000A | 东京 | 0002 | 圆珠笔
000A | 东京 | 0003 | 圆珠笔
000B | 名古屋 | 0002 | 圆珠笔
000B | 名古屋 | 0003 | 圆珠笔
000B | 名古屋 | 0004 | 圆珠笔
000B | 名古屋 | 0006 | 圆珠笔
000B | 名古屋 | 0007 | 圆珠笔
000C | 大阪 | 0003 | 圆珠笔
000C | 大阪 | 0004 | 圆珠笔
000C | 大阪 | 0006 | 圆珠笔
000C | 大阪 | 0007 | 圆珠笔
000D | 福冈 | 0001 | 圆珠笔
对满足相同规则的表进行交叉联结的集合运算符是 CROSS JOIN（笛卡儿积） 。进行交叉联结时无法使用内联结和外联结中所使用的 ON 子句，这是因为交叉联结是对两张表中的全部记录进行交叉组合，因此结果中的记录数通常是两张表中行数的乘积。本例中，因为 ShopProduct 表存在 13 条记录，Product 表存在 8 条记录，所以结果中就包含了 13 × 8 = 104 条记录。
内联结是交叉联结的一部分，“内”也可以理解为“包含在交叉联结结果中的部分”。相反，外联结的“外”可以理解为“交叉联结结果之外的部分”。
联结的特定语法和过时语法
使用过时语法的内联结：
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
FROM ShopProduct SP, Product P
WHERE SP.product_id = P.product_id
AND SP.shop_id = '000A';
上面过时的语法结果与下面内联结代码的结果相同：
-- SQL Server DB2 PostgreSQL MySQL
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 -- 在Oracle的FROM子句中不能使用AS（会发生错误）
ON SP.product_id = P.product_id;
练习题
7-2 节的代码清单 7-11 中列举的外联结的结果中，高压锅和圆珠笔 2 条记录的商店编号（shop_id）和商店名称（shop_name）都是NULL。请使用字符串“不确定”替换其中的NULL。期望结果如下所示。
答案：
解法1：
SELECT COALESCE(SP.shop_id, '不确定') AS shop_id, -- COALESCE 函数可以将 NULL 变换为其他的值。
COALESCE(SP.shop_name, '不确定') AS shop_name,
P.product_id,
P.product_name,
P.sale_price
FROM ShopProduct SP RIGHT OUTER JOIN Product P
ON SP.product_id = P.product_id
ORDER BY shop_id;
解法2：
SELECT (CASE WHEN SP.shop_id is NULL THEN '不确定' ELSE SP.shop_id END) AS shop_id,
(CASE WHEN SP.shop_name is NULL THEN '不确定' ELSE SP.shop_name END) AS shop_name,
P.product_id,
P.product_name,
P.sale_price
FROM ShopProduct AS SP RIGHT OUTER JOIN Product AS P
ON SP.product_id = P.product_id
ORDER BY shop_id;
Chapter 8 SQL高级处理
8.1 窗口函数
窗口函数也称为 OLAP 函数 （在 Oracle 和 SQL Server 中称为分析函数）。OLAP 是 OnLine Analytical Processing 的简称，意思是对数据库数据进行实时分析处理。例如，市场分析、创建财务报表、创建计划等日常性商务工作。窗口函数就是为了实现 OLAP 而添加的标准 SQL 功能。
窗口函数的语法
<窗口函数> OVER ([PARTITION BY <列清单>]
ORDER BY <排序用列清单>)
能够作为窗口函数使用的函数
窗口函数大体可以分为以下两种：
- 能够作为窗口函数的聚合函数（SUM、AVG、COUNT、MAX、MIN）；
- RANK、DENSE_RANK、ROW_NUMBER 等标准 SQL 定义的 OLAP 专用函数，即窗口函数 。
语法的基本使用方法——使用 RANK 函数
RANK 是用来计算记录排序的函数。
代码清单 8-1 根据不同的商品种类product_type，按照销售单价sale_price从低到高的顺序创建排序表：
-- Oracle SQL Server DB2 PostgreSQL
SELECT product_name, product_type, sale_price,
RANK () OVER (PARTITION BY product_type
ORDER BY sale_price) AS ranking
FROM Product;
执行结果：
product_name | product_type | sale_price | ranking
--------------+--------------+------------+---------
圆珠笔 | 办公用品 | 100 | 1
打孔器 | 办公用品 | 500 | 2
叉子 | 厨房用具 | 500 | 1
擦菜板 | 厨房用具 | 880 | 2
菜刀 | 厨房用具 | 3000 | 3
高压锅 | 厨房用具 | 6800 | 4
T恤 | 衣服 | 1000 | 1
运动T恤 | 衣服 | 4000 | 2
-
PARTITION BY 能够设定排序的对象范围。本例中，为了按照商品种类进行排序，我们指定了
product_type。但是，PARTITION BY子句并不具备GROUP BY子句的汇总功能。因此，使用RANK函数并不会减少原表中记录的行数，结果中仍然包含 8 行数据。通过PARTITION BY分组后的记录集合称为窗口，代表范围。 -
ORDER BY 能够指定按照哪一列、何种顺序进行排序。为了按照销售单价的升序进行排列，我们指定了
sale_price。此外，窗口函数中的ORDER BY也可以通过关键字ASC/DESC来指定升序和降序。省略该关键字时会默认按照ASC。
如图所示，PARTITION BY 在横向上对表进行分组，而 ORDER BY 决定了纵向排序的规则。
无需指定 PARTITION BY
PARTITION BY 并不是必需的，即使不指定也可以正常使用窗口函数。不指定 PARTITION BY 时，和使用没有 GROUP BY 的聚合函数时的效果一样，也就是将整个表作为一个大的窗口来使用。
代码清单 8-2 不指定 PARTITION BY
-- Oracle SQL Server DB2 PostgreSQL
SELECT product_name, product_type, sale_price,
RANK () OVER (ORDER BY sale_price) AS ranking
FROM Product;
执行结果：
product_name | product_type | sale_price | ranking
--------------+--------------+------------+---------
圆珠笔 | 办公用品 | 100 | 1
叉子 | 厨房用具 | 500 | 2
打孔器 | 办公用品 | 500 | 2
擦菜板 | 厨房用具 | 880 | 4
T恤 | 衣服 | 1000 | 5
菜刀 | 厨房用具 | 3000 | 6
运动T恤 | 衣服 | 4000 | 7
高压锅 | 厨房用具 | 6800 | 8
专用窗口函数的种类
RANK函数：计算排序时，如果存在相同位次的记录，则会跳过之后的位次。例如，有 3 条记录排在第 1 位时：1 位、1 位、1 位、4 位……DENSE_RANK函数：同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。例如，有 3 条记录排在第 1 位时：1 位、1 位、1 位、2 位……ROW_NUMBER函数：赋予唯一的连续位次。例如，有 3 条记录排在第 1 位时：1 位、2 位、3 位、4 位……
代码清单 8-3 比较 RANK、DENSE_RANK、ROW_NUMBER的结果
-- Oracle SQL Server DB2 PostgreSQL
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
窗口函数的使用位置
语法上，窗口函数通常（绝大部分情况下）在 SELECT子句中使用，还可以在ORDER BY子句或者UPDATE语句的 SET子句中使用，但不能在 WHERE 子句或者 GROUP BY 子句中使用。
其理由是，在 DBMS 内部，窗口函数是对 WHERE 子句或者 GROUP BY 子句处理后的“结果”进行的操作。
作为窗口函数使用的聚合函数
所有的聚合函数都能用作窗口函数，其语法和专用窗口函数完全相同。
将聚合函数作为窗口函数使用时，会以当前记录为基准来决定汇总对象的记录。
代码清单 8-4 将SUM函数作为窗口函数使用
-- Oracle SQL Server DB2 PostgreSQL
SELECT product_id, product_name, sale_price,
SUM (sale_price) OVER (ORDER BY product_id) AS current_sum
FROM Product;
将
SUM函数作为窗口函数使用时，需要提供列作为参数。
执行结果：
product_id | product_name | sale_price | current_sum
------------+--------------+------------+------------
0001 | T恤衫 | 1000 | 1000 ←1000
0002 | 打孔器 | 500 | 1500 ←1000+500
0003 | 运动T恤 | 4000 | 5500 ←1000+500+4000
0004 | 菜刀 | 3000 | 8500 ←1000+500+4000+3000
0005 | 高压锅 | 6800 | 15300 ·
0006 | 叉子 | 500 | 15800 ·
0007 | 擦菜板 | 880 | 16680 ·
0008 | 圆珠笔 | 100 | 16780 ·
注意到，将SUM 函数作为窗口函数使用可以达到累加的效果。
代码清单 8-5 将 AVG 函数作为窗口函数使用
-- Oracle SQL Server DB2 PostgreSQL
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 ·
计算移动平均
窗口函数就是将表以窗口为单位进行分割，并在其中进行排序的函数。其实其中还包含在窗口中指定更加详细的汇总范围的备选功能，该备选功能中的汇总范围称为框架。
其语法如代码清单 8-6 所示：
代码清单 8-6 在 ORDER BY 子句之后指定“最靠近的 3 行”作为汇总对象
-- Oracle SQL Server DB2 PostgreSQL
SELECT product_id, product_name, sale_price,
AVG (sale_price) OVER (ORDER BY product_id
ROWS 2 PRECEDING) AS moving_avg
FROM Product;
两个 ORDER BY
OVER 子句中的 ORDER BY 只是用来决定窗口函数按照什么样的顺序进行计算的，对结果的排列顺序并没有影响。必须在语句末尾使用ORDER BY子句对结果进行排序。
代码清单 8-9 在语句末尾使用 ORDER BY 子句对结果进行排序
-- Oracle SQL Server DB2 PostgreSQL
SELECT product_name, product_type, sale_price,
RANK () OVER (ORDER BY sale_price) AS ranking
FROM Product
ORDER BY ranking;
执行结果：
product_name | product_type | sale_price | ranking
--------------+--------------+------------+---------
圆珠笔 | 办公用品 | 100 | 1
叉子 | 厨房用具 | 500 | 2
打孔器 | 办公用品 | 500 | 2
擦菜板 | 厨房用具 | 880 | 4
T恤 | 衣服 | 1000 | 5
菜刀 | 厨房用具 | 3000 | 6
运动T恤 | 衣服 | 4000 | 7
高压锅 | 厨房用具 | 6800 | 8
8.2 GROUPING运算符
得到合计行
代码清单 8-11 分别计算出合计行和汇总结果再通过 UNION ALL 进行连接
SELECT '合计' AS product_type, SUM(sale_price)
FROM Product
UNION ALL
SELECT product_type, SUM(sale_price)
FROM Product
GROUP BY product_type;
执行结果：
product_type | sum
--------------+------
合计 | 16780
衣服 | 5000
办公用品 | 600
厨房用具 | 11180
同时得出合计和小计：ROLLUP
GROUPING 运算符包含以下 3 种：
ROLLUPCUBEGROUPING SETS
ROLLUP 的使用方法
使用 ROLLUP 就可以通过非常简单的 SELECT 语句同时计算出合计行了
代码清单 8-12 使用 ROLLUP同时得出合计和小计
-- Oracle SQL Server DB2 PostgreSQL
SELECT product_type, SUM(sale_price) AS sum_price
FROM Product
GROUP BY ROLLUP(product_type);
特定的SQL
在MySQL中执行代码清单8-12时，请将GROUP BY子句改写为GROUP BY product_type WITH ROLLUP;。
-- MySQL
SELECT product_type, SUM(sale_price) AS sum_price
FROM Product
GROUP BY product_type WITH ROLLUP;
执行结果：
product_type | sum_price
--------------+-----------
| 16780
衣服 | 5000
办公用品 | 600
厨房用具 | 11180
从语法上来说，就是将 GROUP BY 子句中的聚合键清单像 ROLLUP(< 列 1>,< 列 2>,...) 这样使用。该运算符的作用，一言以蔽之，就是“一次计算出不同聚合键组合的结果”。例如，在本例中就是一次计算出了如下两种组合的汇总结果。
GROUP BY ()GROUP BY (product_type)
合计行记录称为超级分组记录（super group row），超级分组记录默认使用NULL作为聚合键。
将“登记日期”添加到聚合键当中
代码清单 8-14 在 GROUP BY中添加“登记日期”（使用ROLLUP）
-- Oracle SQL Server DB2 PostgreSQL
SELECT product_type, regist_date, SUM(sale_price) AS sum_price
FROM Product
GROUP BY ROLLUP(product_type, regist_date);
-- MySQL
SELECT product_type, regist_date, SUM(sale_price) AS sum_price
FROM Product
GROUP BY product_type, regist_date WITH ROLLUP;
执行结果：
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
将上述两个结果进行比较后我们发现，使用 ROLLUP 时多出了最上方的合计行以及 3 条不同商品种类的小计行（也就是未使用登记日期作为聚合键的记录），这 4 行就是我们所说的超级分组记录。也就是说，该 SELECT 语句的结果相当于使用 UNION 对如下 3 种模式的聚合级的不同结果进行连接。
GROUP BY ()GROUP BY (product_type)GROUP BY (product_type, regist_date)
GROUPING 函数：让 NULL 更加容易分辨
使用GROUPING函数能够简单地分辨出原始数据中的NULL和超级分组记录中的NULL。
在“衣服”的分组之中，有两条记录的 regist_date 列为 NULL，但其原因却并不相同。
sum_price 为 4000 日元的记录，因为商品表中运动 T 恤的注册日期为 NULL，所以就把 NULL 作为聚合键了。
相反，sum_price 为 5000 日元的记录，毫无疑问就是超级分组记录的 NULL 了（具体为 1000 日元 + 4000 日元 = 5000 日元）。但两者看上去都是“NULL”，实在是难以分辨。
product_type regist_date sum_price
-------------- ------------ ----------
衣服 5000 ←因为是超级分组记录，所以登记日期为NULL
衣服 2009-09-20 1000
衣服 4000 ←仅仅因为“运动T恤”的登记日期为NULL
为了避免混淆，SQL 提供了一个用来判断超级分组记录的 NULL 的特定函数 —— GROUPING 函数。该函数在其参数列的值为超级分组记录所产生的 NULL 时返回 1，其他情况返回 0（代码清单 8-15）。
代码清单 8-15 使用GROUPING函数来判断 NULL
-- Oracle SQL Server DB2 PostgreSQL
SELECT GROUPING(product_type) AS product_type,
GROUPING(regist_date) AS regist_date, SUM(sale_price) AS sum_price
FROM Product
GROUP BY ROLLUP(product_type, regist_date);
执行结果：
product_type regist_date sum_price
-------------- ------------ ----------
1 1 16780
0 1 11180
0 0 880
0 0 6800
0 0 3500
0 1 600
0 0 500
0 0 100
0 1 5000 ←碰到超级分组记录中的NULL时返回1
0 0 1000
0 0 4000 ←原始数据为NULL时返回0
代码清单 8-16 在超级分组记录的键值中插入恰当的字符串
-- Oracle SQL Server DB2 PostgreSQL
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, -- CASE 表达式所有分支的返回值必须一致的条件
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 ←将超级分组记录中的NULL替换为“登记日期 合计”
衣服 2009-09-20 1000
衣服 4000 ←原始数据中的NULL保持不变
CUBE用数据来搭积木
CUBE 的语法和 ROLLUP 相同，只需要将 ROLLUP 替换为 CUBE 就可以了。
代码清单 8-17 使用 CUBE 取得全部组合的结果
-- Oracle SQL Server DB2 PostgreSQL
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);
执行结果（在DB2中执行）
product_type regist_date sum_price
-------------- ------------ ----------
商品种类 合计 登记日期 合计 16780
商品种类 合计 2008-04-28 880 ←追加
商品种类 合计 2009-01-15 6800 ←追加
商品种类 合计 2009-09-11 500 ←追加
商品种类 合计 2009-09-20 4500 ←追加 3500+1000
商品种类 合计 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
与 ROLLUP 的结果相比，CUBE 的结果中多出了几行记录。多出来的记录就是只将 regist_date 作为聚合键所得到的结果进行了汇总小计。
GROUP BY ()GROUP BY (product_type)GROUP BY (regist_date)←添加的组合GROUP BY (product_type, regist_date)
GROUPING SETS——取得期望的积木
最后要介绍给大家的 GROUPING 运算符是 GROUPING SETS。该运算符可以用于从 ROLLUP 或者 CUBE 的结果中取出部分记录。
例如，之前的 CUBE 的结果就是根据聚合键的所有可能的组合计算而来的。如果希望从中选取出将“商品种类”和“登记日期”各自作为聚合键的结果，或者不想得到“合计记录和使用 2 个聚合键的记录”时，可以使用 GROUPING SETS（代码清单 8-18）。
代码清单 8-18 使用GROUPING SETS取得部分组合的结果
-- Oracle SQL Server DB2 PostgreSQL
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 GROUPING SETS (product_type, regist_date);
执行结果（在DB2中执行）
product_type regist_date sum_price
-------------- ------------ ----------
商品种类 合计 2008-04-28 880
商品种类 合计 2009-01-15 6800
商品种类 合计 2009-09-11 500
商品种类 合计 2009-09-20 4500
商品种类 合计 2009-11-11 100
商品种类 合计 4000
厨房用具 登记日期 合计 11180
办公用品 登记日期 合计 600
衣服 登记日期 合计 5000
练习题
8.1 请说出针对本章中使用的 Product（商品）表执行如下 SELECT 语句所能得到的结果。
SELECT product_id, product_name, sale_price,
MAX (sale_price) OVER (ORDER BY product_id) AS current_max_price
FROM Product;
执行结果：
product_id | product_name | sale_price | current_max_price
------------+--------------+------------+-------------------
0001 | T恤衫 | 1000 | 1000 ←(1000)的最大值
0002 | 打孔器 | 500 | 1000 ←(1000, 500)的最大值
0003 | 运动T恤 | 4000 | 4000 ←(1000, 500, 4000)的最大值
0004 | 菜刀 | 3000 | 4000 ←(1000, 500, 4000, 3000)的最大值
0005 | 高压锅 | 6800 | 6800
0006 | 叉子 | 500 | 6800
0007 | 擦菜板 | 880 | 6800
0008 | 圆珠笔 | 100 | 6800
本题中 SELECT 语句的含义是“按照商品编号（product_id）的升序进行排序，计算出截至当前行的最高销售单价”。
8.2 继续使用Product表，计算出按照登记日期（regist_date）升序进行排列的各日期的销售单价（sale_price）的总额。排序是需要将登记日期为 NULL 的“运动 T 恤”记录排在第 1 位（也就是将其看作比其他日期都早）。
解法①：regist_date 为 NULL 时，显示“1年 1月 1 日”
SELECT regist_date, product_name, sale_price,
SUM (sale_price) OVER (ORDER BY COALESCE(regist_date, CAST('0001-01-01' AS DATE))) AS current_sum_price
FROM Product;
解法② regist_date 为 NULL 时，将该记录放在最前显示
SELECT regist_date, product_name, sale_price,
SUM (sale_price) OVER (ORDER BY regist_date NULLS FIRST) AS current_sum_price
FROM Product;
通过在 ORDER BY 子句中指定NULLS FIRST选项，可以显式地给 DBMS 下达指令，在排序时将 NULL 放在最前面。目前该方法也是在支持窗口函数的 DBMS 中通用的方法。该功能并不是标准 SQL 支持的功能，而是依存于 DBMS 的实现。
shop=# select * from Product ORDER BY regist_date NULLS FIRST;
product_id | product_name | product_type | sale_price | purchase_price | regist_date
------------+--------------+--------------+------------+----------------+-------------
0003 | 运动T恤 | 衣服 | 4000 | 2800 |
0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28
0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15
0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11
0001 | T恤 | 衣服 | 1000 | 500 | 2009-09-20
0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20
0006 | 叉子 | 厨房用具 | 500 | | 2009-09-20
0008 | 圆珠笔 | 办公用品 | 100 | | 2009-11-11
(8 行记录)
shop=# select * from Product ORDER BY purchase_price NULLS FIRST;
product_id | product_name | product_type | sale_price | purchase_price | regist_date
------------+--------------+--------------+------------+----------------+-------------
0006 | 叉子 | 厨房用具 | 500 | | 2009-09-20
0008 | 圆珠笔 | 办公用品 | 100 | | 2009-11-11
0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11
0001 | T恤 | 衣服 | 1000 | 500 | 2009-09-20
0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28
0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20
0003 | 运动T恤 | 衣服 | 4000 | 2800 |
0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15
(8 行记录)