那些以为知道的SQL基础

CREATE TABLE Addressbook

(

regist_no INTEGER NOT NULL,

name VARCHAR(128) NOT NULL,

address VARCHAR(256) NOT NULL,

tel_no CHAR(10) ,

mail_address CHAR(20) ,

PRIMARY KEY (regist_no));

删除表表删除

DROP TABLE Product;

增加列新增列

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

修改表名重命名变更表名

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 Server PostgreSQL

-- DML ：插入数据

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;

在MySQL 中运行时，需要把①中的BEGIN TRANSACTION;改写成START TRANSACTION;

在Oracle 和DB2中运行时，无需使用①中的BEGIN TRANSACTION;（请予以删除）。

Oracle中不能直接将字符串类型的数据作为DATE类型插入，要使用to_date()

INSERT INTO Product VALUES ('0008', '圆珠笔', '办公用品', 100, NULL, to_date('2009-11-11','yyyy-mm-dd'));

第2章查询基础

SELECT语句基础

SELECT product_id AS id,

product_name AS name,

purchase_price AS price

FROM Product;

Oracle中AS可以省略，直接指定别名。

删除重复行（DISTINCT）

SELECT DISTINCT product_type

FROM Product;

DISTINCT 也可以像下列代码那样在多列之前使用。此时，会将多个列的数据进行组合，将重复的数据合并为一条。DISTINCT 关键字只能用在第一个列名之前。

SELECT DISTINCT product_type, regist_date

FROM Product;

注释

单行注释--注释内容

多行注释/*注释内容 */

算术运算符（+-*/）和比较运算符(> < >= <= <>)

SELECT product_name, sale_price,

sale_price * 2 AS "sale_price_x2"

FROM Product;

包含NULL的算术运算结果均为NULL

SELECT product_name, sale_price, purchase_price

FROM Product

WHERE sale_price - purchase_price >= 500;

字符串类型的数据原则上按照字典顺序进行排序，不能与数字的大小顺序混淆。（即一个字符一个字符的比）

SQL提供了专门用来判断是否为NULL 的IS NULL和IS NOT NULL 运算符，NULL不用于上面提到的四个比较运算符。

逻辑运算符（OR AND NOT）

SQL中的逻辑运算是包含对真、假和不确定进行运算的三值逻辑。

NOT运算符用来否定某一条件，但是不能滥用。与<>不同

SELECT product_name, product_type, sale_price

FROM Product

WHERE NOT sale_price >= 1000;

NULL ：purchase_price为空值的两条记录，通过purchase_price = 2800和NOT purchase_price = 2800都查不出这两条记录，即这两条记录处于第三种状态——不确定（UNKNOWN）

select * from product t；
select * from product t where t.purchase_price=2800;
select * from product t where NOT t.purchase_price=2800;

第3章　聚合与排序

对表进行聚合查询

对表进行分组

为聚合结果指定条件

对查询结果进行排序

3-1 对表进行聚合查询（聚合函数）

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

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

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

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

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

通常，聚合函数会对NULL以外的对象进行汇总。但是只有COUNT函数例外，使用COUNT（*）可以查出包含NULL在内的全部数据的行数。

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

该特性是COUNT 函数所特有的，其他函数并不能将星号作为参数（如果使用星号会出错）。

SELECT SUM(sale_price), SUM(purchase_price)

FROM Product;

聚合函数会将NULL排除在外。但COUNT(*)例外，并不会排除NULL。

SELECT AVG(sale_price), AVG(purchase_price)

FROM Product;

计算进货单价平均值的情况与SUM 函数相同，会事先删除NULL再进行计算。

SELECT MAX(sale_price), MIN(purchase_price)

FROM Product;

SUM/AVG 函数只能对数值类型的列使用，而MAX/MIN 函数原则上可以适用于任何数据类型的列。

使用聚合函数删除重复值（关键字DISTINCT）

SELECT COUNT(DISTINCT product_type)

FROM Product;

3-2 对表进行分组（GROUP BY）

聚合键中包含NULL时，在结果中会以“不确定”行（空行）的形式表现出来。

使用聚合函数和GROUP BY子句时需要注意以下4点。

① 只能写在SELECT子句之中

② GROUP BY子句中不能使用SELECT子句中列的别名

③ GROUP BY子句的聚合结果是无序的

④ WHERE子句中不能使用聚合函数

SELECT <列名1>, <列名2>, <列名3>, ……

FROM <表名>

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

SELECT product_type, COUNT(*)

FROM Product

GROUP BY product_type;

未使用GROUP BY 子句时，结果只有1 行，而这次的结果却是多行。这是因为不使用GROUP BY 子句时，是将表中的所有数据作为一组来对待的。而使用GROUP BY 子句时，会将表中的数据分为多个组进行处理。

在GROUP BY 子句中指定的列称为聚合键或者分组列。由于能够决定表的切分方式，所以是非常重要的列。当然，GROUP BY 子句也和SELECT 子句一样，可以通过逗号分隔指定多列。

当聚合键中包含NULL 时，也会将NULL 作为一组特定的数据。

子句的书写顺序（暂定）

1. SELECT → 2. FROM → 3. WHERE → 4. GROUP BY

GROUP BY 和WHERE 并用时SELECT 语句的执行顺序

FROM → WHERE → GROUP BY → SELECT

使用聚合函数时，SELECT 子句中只能存在以下三种元素：

● 常数

● 聚合函数

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

SELECT 子句中的列可以通过AS 关键字来指定别名。但是，在GROUP BY 子句中是不能使用该别名的。错误示例如下：

SELECT product_type AS pt, COUNT(*)

FROM Product

GROUP BY pt;

在WHERE子句中使用聚合函数会引发错误，错误示例：

SELECT product_type, COUNT(*)

FROM Product

WHERE COUNT(*) = 2

GROUP BY product_type;

只有SELECT子句和HAVING子句（以及ORDER BY子句）中能够使用聚合函数。

在“想要删除选择结果中的重复记录”时使用DISTINCT，在“想要计算汇总结果”时使用GROUP BY。

3-3 为聚合结果指定条件（HAVING）

●使用COUNT函数等对表中数据进行汇总操作时，为其指定条件的不是

WHERE子句，而是HAVING子句。

●聚合函数可以在SELECT子句、HAVING子句和ORDER BY子句中使用。

● HAVING子句要写在GROUP BY子句之后。

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

使用HAVING 子句时SELECT 语句的顺序

SELECT → FROM → WHERE → GROUP BY → HAVING

执行顺序

FROM → WHERE → GROUP BY → HAVING → SELECT

SELECT product_type, COUNT(*)

FROM Product

GROUP BY product_type

HAVING COUNT(*) = 2;

SELECT product_type, AVG(sale_price)

FROM Product

GROUP BY product_type

HAVING AVG(sale_price) >= 2500;

HAVING 子句中能够使用的3 种要素如下所示。

● 常数

● 聚合函数

● GROUP BY子句中指定的列名（即聚合键）

有些条件既可以写在HAVING 子句当中，又可以写在WHERE 子句当中。这些条件就是聚合键所对应的条件。原表中作为聚合键的列也可以在HAVING 子句中使用。

SELECT product_type, COUNT(*)

FROM Product

GROUP BY product_type

HAVING product_type = '衣服';


SELECT product_type, COUNT(*)

FROM Product

WHERE product_type = '衣服'

GROUP BY product_type;

聚合键所对应的条件还是应该书写在WHERE 子句之中。

WHERE 子句 = 指定行所对应的条件

HAVING 子句 = 指定组所对应的条件（聚合函数对应的条件COUNT（*）>2）

WHERE子句和HAVING子句的执行速度

在WHERE 子句和HAVING 子句中都可以使用的条件，最好写在WHERE 子

句中的另一个理由与性能即执行速度有关系。通常情况下，为了得到相同的结果，将条件写在WHERE 子句中要比写在HAVING 子句中的处理速度更快，返回结果所需的时间更短。

为了理解其中原因，就要从DBMS 的内部运行机制来考虑。使用COUNT 函数等对表中的数据进行聚合操作时，DBMS 内部就会进行排序处理。排序处理是会大大增加机器负担的高负荷的处理A。因此，只有尽可能减少排序的行数，才能提高处理速度。

通过WHERE 子句指定条件时，由于排序之前就对数据进行了过滤，因此能够减少排序的数据量。但HAVING 子句是在排序之后才对数据进行分组的，因此与在WHERE 子句中指定条件比起来，需要排序的数据量就会多得多。虽然DBMS的内部处理不尽相同，但是对于排序处理来说，基本上都是一样的。

此外，WHERE 子句更具速度优势的另一个理由是，可以对WHERE 子句指定条件所对应的列创建索引，这样也可以大幅提高处理速度。创建索引是一种非常普遍的提高DBMS 性能的方法，效果也十分明显，这对WHERE 子句来说也十分有利。

3-4 对查询结果进行排序（ORDER BY）

●使用ORDER BY子句对查询结果进行排序。

●在ORDER BY子句中列名的后面使用关键字ASC可以进行升序排序，使

用DESC关键字可以进行降序排序。

● ORDER BY子句中可以指定多个排序键。

●排序健中包含NULL时，会在开头或末尾进行汇总。

● ORDER BY子句中可以使用SELECT子句中定义的列的别名。

● ORDER BY子句中可以使用SELECT子句中未出现的列或者聚合函数。

● ORDER BY子句中不能使用列的编号。

ORDER BY子句中书写的列名称为排序键。该子句与其他子句的顺序关系如下所示。

子句的书写顺序：

1. SELECT 子句 → 2. FROM 子句 → 3. WHERE 子句 → 4. GROUP BY 子句 →

5. HAVING 子句 → 6. ORDER BY 子句

在ORDER BY子句中可以使用SELECT子句中未使用的列和聚合函数。

第4章　数据更新

数据的插入（INSERT语句的使用方法）

数据的删除（DELETE语句的使用方法）

数据的更新（UPDATE语句的使用方法）

事务

4-1　数据的插入（INSERT语句的使用方法）

■什么是INSERT

■INSERT语句的基本语法

■列清单的省略

■插入NULL

■插入默认值

■从其他表中复制数据

4-2　数据的删除（DELETE语句的使用方法）

■DROP TABLE语句和DELETE语句

■DELETE语句的基本语法

■指定删除对象的DELETE语句（搜索型DELETE）

4-3　数据的更新（UPDATE语句的使用方法）

■UPDATE语句的基本语法

■指定条件的UPDATE语句（搜索型UPDATE）

■使用NULL进行更新

■多列更新

4-4　事务

■什么是事务

■创建事务

■ACID特性

4-1 数据的插入（INSERT）

●使用INSERT语句可以向表中插入数据（行）。原则上，INSERT语句每次执行一行数据的插入。

●将列名和值用逗号隔开，分别括在（）内，这种形式称为清单。

●对表中所有列进行INSERT操作时可以省略表名后的列清单。

●插入NULL时需要在VALUES子句的值清单中写入NULL。

●可以为表中的列设定默认值（初始值），默认值可以通过在CREATE TABLE语句中为列设置DEFAULT约束来设定。

●插入默认值可以通过两种方式实现，即在INSERT语句的VALUES子句中指定DEFAULT关键字（显式方法），或省略列清单（隐式方法）。

●使用INSERT…SELECT可以从其他表中复制数据。

-- 通常的INSERT

INSERT INTO ProductIns VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');

INSERT INTO ProductIns VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);

INSERT INTO ProductIns VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');

-- 多行INSERT （Oracle以外）

INSERT INTO ProductIns VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11'),

('0003', '运动T恤', '衣服', 4000, 2800, NULL),

('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');

-- Oracle中的多行INSERT

INSERT ALL INTO ProductIns VALUES ('0002','打孔器','办公用品', 500, 320, to_date('2009-09-11','yy-mm-dd'))

INTO ProductIns VALUES ('0003', '运动T恤','衣服', 4000, 2800, NULL)

INTO ProductIns VALUES ('0004', '菜刀','厨房用具', 3000, 2800, to_date('2009-09-20','yy-mm-dd'))

SELECT * FROM DUAL;

--显式插入默认值

INSERT INTO ProductIns (product_id, product_name, product_type,sale_price, purchase_price, regist_date) 
VALUES 
('0007', '擦菜板', '厨房用具', DEFAULT, 790,to_date('2009-04-28','yy-mm-dd'));

--隐式插入默认值

只需隐藏相应的列和值

--从其他表中复制数据

INSERT ... SELECT语句

4-2 数据的删除（DELETE和TRUNCATE）

DELETE FROM <表名>

WHERE <条件>;

很多数据库产品中还存在另外一种被称为TRUNCATE 的语句。这些产品主要包括Oracle、SQL

Server、PostgreSQL、MySQL 和DB2。

与DELETE 不同的是，TRUNCATE 只能删除表中的全部数据，而不能通过WHERE 子句指定条件来删除部分数据。也正是因为它不能具体地控制删除对象，所以其处理速度比DELETE 要快得多。

实际上，DELETE 语句在DML 语句中也属于处理时间比较长的，因此需要删除全部数据行时，使用TRUNCATE 可以缩短执行时间。

在Oracle 中，把TRUNCATE定义为DDL，而不是DML。

4-3 数据的更新（UPDATE）

UPDATE <表名>

SET <列名> = <表达式>

WHERE <条件>;

UPDATE Product

SET sale_price = sale_price * 10,

purchase_price = purchase_price / 2

WHERE product_type = '厨房用具';

4-4 事务

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

在标准SQL 中并没有定义事务的开始语句，而是由各个DBMS 自己来定义的。比较有代表性的语法如下所示。

● SQL Server、PostgreSQL

BEGIN TRANSACTION

● MySQL

START TRANSACTION

● Oracle、DB2

无

COMMIT 是提交事务包含的全部更新处理的结束指令，相当于文件处理中的覆盖保存。一旦提交，就无法恢复到事务开始前的状态了。

ROLLBACK 是取消事务包含的全部更新处理的结束指令，相当于文件处理中的放弃保存。一旦回滚，数据库就会恢复到事务开始之前的状态。

实际上，几乎所有的数据库产品的事务都无需开始指令。这是因为大部分情况下，事务在数据库连接建立时就已经悄悄开始了，并不需要用户再明确发出开始指令。例如，使用Oracle 时，数据库连接建立之后，第一条SQL 语句执行的同时，事务就已经悄悄开始了。

两种模式：

A 每条SQL语句就是一个事务（自动提交模式）

B 直到用户执行COMMIT或者ROLLBACK为止算作一个事务（非自动提交模式）

ACID特性

原子性（Atomicity）

在事务结束时，其中所包含的更新处理要么全部执行，要么完全不执行。

一致性（Consistency）又称完整性

一致性指的是事务中包含的处理要满足数据库提前设置的约束，如主键约束或者NOT NULL 约束等

隔离性（Isolation）

隔离性指的是保证不同事务之间互不干扰的特性。该特性保证了事务之间不会互相嵌套。此外，在某个事务中进行的更改，在该事务结束之前，对其他事务而言是不可见的。

持久性（Durability）

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

如果不能保证持久性，即使是正常提交结束的事务，一旦发生了系统故障，也会导致数据丢失，一切都需要从头再来。保证持久性的方法根据实现的不同而不同，其中最常见的就是将事务的执行记录保存到硬盘等存储介质中（该执行记录称为日志）。当发生故障时，可以通过日志恢复到故障发生前的状态。

第5章　复杂查询

视图

子查询

关联子查询

5-1　视图

■视图和表

■创建视图的方法

■视图的限制① ——定义视图时不能使用ORDER BY子句

■视图的限制② ——对视图进行更新

■删除视图

5-2　子查询

■子查询和视图

■子查询的名称

■标量子查询

■标量子查询的书写位置

■使用标量子查询时的注意事项

5-3　关联子查询

■普通的子查询和关联子查询的区别

■关联子查询也是用来对集合进行切分的

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

5-1 视图

●从SQL的角度来看，视图和表是相同的，两者的区别在于表中保存的是实

际的数据，而视图中保存的是SELECT语句（视图本身并不存储数据）。

●使用视图，可以轻松完成跨多表查询数据等复杂操作。

●可以将常用的SELECT语句做成视图来使用。

●创建视图需要使用CREATE VIEW语句。

●视图包含“不能使用ORDER BY”和“可对其进行有限制的更新”两项限制。

●删除视图需要使用DROP VIEW语句。

由于视图无需保存数据，是在实际的业务中数据量往往非常大，这时使用视图所节省的容量就会非常可观了。

表中存储的是实际数据，而视图中保存的是从表中取出数据所使用的SELECT语句。

多重视图会降低SQL 的性能。

定义视图时不要使用ORDER BY子句。

CREATE VIEW ProductSum (product_type, cnt_product)

AS

SELECT product_type, COUNT(*)

FROM Product

GROUP BY product_type;

DROP VIEW ProductSum;

定义视图的SELECT 语句能够满足某些条件，那么这个视图就可以被更新（INSERT、DELETE、UPDATE）。

① SELECT 子句中未使用DISTINCT

② FROM 子句中只有一张表

③ 未使用GROUP BY 子句

④ 未使用HAVING 子句

5-2 子查询

●一言以蔽之，子查询就是一次性视图（SELECT语句）。与视图不同，子查询在SELECT语句执行完毕之后就会消失。

●由于子查询需要命名，因此需要根据处理内容来指定恰当的名称。

●标量子查询就是只能返回一行一列的子查询。

SELECT product_type, cnt_product

FROM (

SELECT Product_type, COUNT(*) AS cnt_product

FROM Product

GROUP BY product_type

) AS ProductSum;

Oracle 没有AS。

标量子查询则有一个特殊的限制，那就是必须而且只能返回1 行1列的结果，也就是返回表中某一行的某一列的值，例如“10”或者“东京都”这样的值。

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

-- 在WHERE子句中不能使用聚合函数

SELECT product_id, product_name, sale_price

FROM Product

where sale_price > (select AVG(sale_price) from Product);

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

5-3 关联子查询

●关联子查询会在细分的组内进行比较时使用。

●关联子查询和GROUP BY子句一样，也可以对表中的数据进行切分。

●关联子查询的结合条件如果未出现在子查询之中就会发生错误。

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

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

6-1　各种各样的函数

■函数的种类

■算术函数

■字符串函数

■日期函数

■转换函数

6-2　谓词

■什么是谓词

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

■BETWEEN谓词——范围查询

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

■IN谓词——OR的简便用法

■使用子查询作为IN谓词的参数

■EXIST谓词

6-3　CASE表达式

■什么是CASE表达式

■CASE表达式的语法

■CASE表达式的使用方法

6-1 各种各样的函数

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

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

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

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

● 转换函数（用来转换数据类型和值的函数）

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

算术函数：

● +（加法）

● -（减法）

● *（乘法）

● /（除法）

●ABS(数值) 绝对值

●MOD(被除数，除数) 只能对整数类型的列使用MOD 函数求余数。SQL Server 不支持该函数，使用%代替。

●ROUND(对象数值，保留小数的位数) 小数点后的四舍五入

字符串函数：

●||函数字符串拼接 SQL Server使用“+”运算符（函数）来连接字符串。MySQL使用CONCAT函数

来完成字符串的拼接。

●LENGTH(str1) str1的字符长度 SQL Server使用LEN函数来计算字符串的长度

●LOWER(字符串) 只能针对英文字母使用，它会将参数中的字符串全都转换为小写

●UPPER(字符串) 大写转换函数。

●REPLACE(对象字符串，替换前的字符串，替换后的字符串) 字符串函数

●SUBSTRING（对象字符串 FROM 截取的起始位置 FOR 截取的字符数） PostgreSQL/MySQL专用语法。SUBSTRING(对象字符串，截取的起始位置，截取的字符数) SQL Server专用语法。SUBSTR(对象字符串，截取的起始位置，截取的字符数) Oracle/DB2专用语法。

日期函数：

●获得当前日期

PostgreSQL MySQL

SELECT CURRENT_DATE;

SQL Server

-- 使用CAST（后述）函数将CURRENT_TIMESTAMP转换为日期类型

SELECT CAST(CURRENT_TIMESTAMP AS DATE) AS CUR_DATE;

Oracle

SELECT CURRENT_DATE

FROM dual;

DB2

SELECT CURRENT DATE

FROM SYSIBM.SYSDUMMY1;

●CURRENT_TIME——当前时间

PostgreSQL MySQL

SELECT CURRENT_TIME;

SQL Server

-- 使用CAST函数（后述）将CURRENT_TIMESTAMP转换为时间类型

SELECT CAST(CURRENT_TIMESTAMP AS TIME) AS CUR_TIME;
Oracle

-- 指定临时表（DUAL）

SELECT CURRENT_TIMESTAMP

FROM dual;

DB2

/* CURRENT和TIME之间使用了半角空格，指定临时表SYSIBM.SYSDUMMY1 */

SELECT CURRENT TIME

FROM SYSIBM.SYSDUMMY1;

●CURRENT_TIMESTAMP——当前日期和时间

SQL Server PostgreSQL MySQL

SELECT CURRENT_TIMESTAMP;

Oracle

-- 指定临时表（DUAL）

SELECT CURRENT_TIMESTAMP

FROM dual;

DB2

/* CURRENT和TIME之间使用了半角空格，指定临时表SYSIBM.SYSDUMMY1 */

SELECT CURRENT TIMESTAMP

FROM SYSIBM.SYSDUMMY1;

●EXTRACT(日期元素 FROM 日期) 截取日期

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;

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

NUMBERIC( 全体位数, 小数位数) 一种数据类型，用于指定数值大小位数

转换函数：

一是数据类型的转换，简称为类型转换；另一层意思是值的转换。

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


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;

●COALESCE——将NULL转换为其他值

COALESCE(数据1，数据2，数据3……) 返回可变参数A 中左侧开始第1 个不是NULL 的值。参数个数是可变的，因此可以根据需要无限增加。

将NULL转换为其他值

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 也用NVL() 功能相同

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;

6-2 谓词

●谓词就是返回值为真值（TRUE/FALSE/UNKNOWN）的函数。

●掌握LIKE的三种使用方法（前方一致、中间一致、后方一致）。

●需要注意BETWEEN包含三个参数。

●想要取得NULL数据时必须使用IS NULL。

●可以将子查询作为IN和EXISTS的参数。

● LIKE

% 是代表“0 字符及以上的任意字符串”的特殊符号。 _ 是代表“1个任意字符串”的特殊符号。

前方一致：

SELECT *

FROM SampleLike

WHERE strcol LIKE 'ddd%';

中间一致：

SELECT *

FROM SampleLike

WHERE strcol LIKE '%ddd%';

后方一致：

SELECT *

FROM SampleLike

WHERE strcol LIKE '%ddd'; ---LIKE 'abc_ _';

● BETWEEN

SELECT product_name, sale_price

FROM Product

WHERE sale_price BETWEEN 100 AND 1000;---包括100和1000

SELECT product_name, sale_price

FROM Product

WHERE sale_price > 100

AND sale_price < 1000;

● IS NULL、IS NOT NULL

SELECT product_name, purchase_price

FROM Product

WHERE purchase_price IS NULL;

● IN 、NOT IN

SELECT product_name, purchase_price

FROM Product

WHERE purchase_price IN (320, 500, 5000);

在使用IN 和NOT IN 时是无法选取出NULL 数据的。

-- 取得“在大阪店销售的商品的销售单价”

SELECT product_name, sale_price

FROM Product

WHERE product_id IN (SELECT product_id

FROM ShopProduct

WHERE shop_id = '000C');

● EXISTS、NOT EXIST

EXISTS 通常都会使用关联子查询作为参数。作为EXIST参数的子查询中经常会使用SELECT *。

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

EXISTS外层先执行，执行完将每条记录带入内层，存在记录则返回true并保留当前记录，否则返回false并不保存当前记录。

NOT RXISTS 常用于插入判断，防止插入重复数据。

适用于外层表小内层表大的情况，利用索引。

而IN与NOT IN则先执行内层，再执行外层，不利用索引，适用于内层表小外层表大的情况。

大部分情况下由于索引的缘故，EXISTS性能好于IN。

6-3 CASE表达式

用于（条件）分支。

CASE表达式的语法分为简单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;

-- 对按照商品种类计算出的销售单价合计值进行行列转换

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;

两者的不同之处在于，简单CASE 表达式最初的“CASE< 表达式 >”也会作为求值的对象。

下面就让我们来看一看搜索CASE表达式和简单CASE表达式是如何实现相同含义的 SQL 语句的。

代码清单6-A　使用CASE表达式将字符串A ～ C添加到商品种类中

-- 使用搜索CASE表达式的情况（重写代码清单6-41）

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;

像“CASE product_type”这样，简单 CASE 表达式在将想要求值的表达式（这里是列）书写过一次之后，就无需在之后的 WHEN 子句中重复书写“product_type”了。虽然看上去简化了书写，但是想要在 WHEN 子句中指定不同列时，简单 CASE 表达式就无能为力了。

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

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

第7章　集合运算

表的加减法

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

通过以行方向（竖）为单位的集合运

算符和以列方向（横）为单位的联结，就可以将分散在多张表中的数据组合成

为期望的结果。

7-1　表的加减法

■什么是集合运算

■表的加法——UNION

■集合运算的注意事项

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

■选取表中公共部分——INTERSECT（MySQL不支持）

■记录的减法——EXCEPT（MySQL不支持 Oracle用minus）

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

■什么是联结

■内联结——INNER JOIN

■外联结——OUTER JOIN

■ 3张以上的表的联结

■交叉联结——CROSS JOIN

■联结的特定语法和过时语法

7-1 表的加减法 UNION（并集）、 INTERSECT（交集）、 EXCEPT（差集）

● 集合运算就是对满足同一规则的记录进行的加减等四则运算。

● 使用UNION（并集）、 INTERSECT（交集）、 EXCEPT（差集）等集合运算符来进行集合运算。

● 集合运算符可以去除重复行。

● 如果希望集合运算符保留重复行，就需要使用ALL选项。

集合运算的注意事项

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

一部分记录包含 2 列，另一部分记录包含 3 列时会发生错误，无法进行加法运算。

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

从左侧开始，相同位置上的列必须是同一数据类型。虽然列数相同，但是第 2 列的数据类型并不一致（一个是数值类型，一个是日期类型），因此会发生错误。

一定要使用不同数据类型的列时，可以使用类型转换函数CAST。

■注意事项③ ——可以使用任何SELECT语句，但ORDER BY子句只能在最后使用一次。通过 UNION 进行并集运算时可以使用任何形式的 SELECT 语句，之前学过的 WHERE、GROUP BY、 HAVING 等子句都可以使用。但是ORDER BY 只能在最后使用一次。

SELECT product_id, product_name

FROM Product

WHERE product_type = '厨房用具'

UNION

SELECT product_id, product_name

FROM Product2

WHERE product_type = '厨房用具'

ORDER BY product_id;

注：实际上，在有些 DBMS 中，即使数据类型不同，也可以通过隐式类型转换来完成操作。但由于并非所有的 DBMS 都支持这样的用法，因此还是希望大家能够使用恰当的数据类型来进行运算。

与使用 AND 可以选取出一张表中满足多个条件的公共部分不同，INTERSECT 应用于两张表，选取出它们当中的公共记录。

SELECT product_id, product_name FROM Product 
INTERSECT 
SELECT product_id, product_name FROM Product2 
ORDER BY product_id;

记录的减法——EXCEPT

代码清单7-7　使用EXCEPT对记录进行减法运算 SQL Server DB2 PostgreSQL SELECT product_id, product_name FROM Product EXCEPT SELECT product_id, product_name FROM Product2 ORDER BY product_id;

EXCEPT 有一点与 UNION 和 INTERSECT 不同，需要注意一下。那就是在减法运算中减数和被减数的位置不同，所得到的结果也不相同。

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

● 联结（ JOIN）就是将其他表中的列添加过来，进行“添加列”的集合运算。

UNION是以行（纵向）为单位进行操作，而联结则是以列（横向）为单位进行的。

● 联结大体上分为内联结和外联结两种。首先请大家牢牢掌握这两种联结的使用方法。

内联结——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 
WHERE SP.shop_id = '000A'; 
--Oracle中没有as

外联结——OUTER JOIN

代码清单7-11　将两张表进行外联结 
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 
ON SP.product_id = P.product_id; 
--Oracle没有as

内联结只能选取出同时存在于两张表中的数据。对于外联结来说，只要数据存在于某一张表当中，就能够读取出来。在实际的业务中，例如想要生成固定行数的单据时，就需要使用外联结。如果使用内联结的话，根据 SELECT 语句执行时商店库存状况的不同，结果的行数也会发生改变，生成的单据的版式也会受到影响，而使用外联结能够得到固定行数的结果。

外联结还有一点非常重要，那就是要把哪张表作为主表。最终的结果中会包含主表内所有的数据。指定主表的关键字是 LEFT 和 RIGHT。顾名思义，使用 LEFT 时 FROM 子句中写在左侧的表是主表，使用 RIGHT时右侧的表是主表。

所谓的除法实现：

代码清单7-B　选取出掌握所有3个领域的技术的员工
SELECT DISTINCT emp 
FROM EmpSkills ES1 
WHERE NOT EXISTS 
    (SELECT skill 
        FROM Skills 
        EXCEPT 
    SELECT skill 
    FROM EmpSkills ES2 
    WHERE ES1.emp = ES2.emp);

第8章　SQL高级处理

窗口函数

GROUPING运算符

本章重点本章将要学习的是 SQL 中的高级聚合处理。 8-1　窗口函数 ■什么是窗口函数 ■窗口函数的语法 ■语法的基本使用方法——使用RANK函数 ■无需指定PARTITION BY ■专用窗口函数的种类 ■窗口函数的适用范围 ■作为窗口函数使用的聚合函数 ■计算移动平均 ■两个ORDER BY 8-2　GROUPING运算符 ■同时计算出合计值 ■ ROLLUP——同时得出合计和小计 ■ GROUPING函数——让NULL更加容易分辨 ■ CUBE——用数据来搭积木 ■ GROUPING SETS——取得期望的积木

8-1 窗口函数

● 窗口函数可以进行排序、生成序列号等一般的聚合函数无法实现的高级操作。

● 理解PARTITION BY和ORDER BY这两个关键字的含义十分重要。

窗口函数也称为OLAP 函数，OLAP 是OnLine Analytical Processing 的简称，意思是对数据库数据

进行实时分析处理。例如，市场分析、创建财务报表、创建计划等日常性商务工作。

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

<窗口函数> OVER ([PARTITION BY <列清单>] ORDER BY <排序用列清单>)

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

① 能够作为窗口函数的聚合函数（SUM、AVG、COUNT、MAX、MIN）

② RANK、DENSE_RANK、ROW_NUMBER 等专用窗口函数。由于专用窗口函数无需参数，因此通常括号中都是空的。

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;

PARTITION BY 能够设定排序的对象范围。本例中，为了按照商品种类进行排序，我们指定了product_type。

ORDER BY 能够指定按照哪一列、何种顺序进行排序。为了按照销售单价的升序进行排列，我们指定了sale_price。OVER 子句中的ORDER BY 只是用来决定窗口函数按照什么样的顺序进行计算的，对结果的排列顺序并没有影响。

product_name | product_type | sale_price | ranking

------------+--------------+-------------+--------

叉子 | 厨房用具 | 500 | 1

擦菜板 | 厨房用具 | 880 | 2

菜刀 | 厨房用具 | 3000 | 3

高压锅 | 厨房用具 | 6800 | 4

T恤衫 | 衣服 | 1000 | 1

运动T恤 | 衣服 | 4000 | 2

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

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

通过PARTTION BY分组后的记录的集合可以称为窗口。PARTTION BY可以省略，则默认将所有记录作为一组结果进行排序。

Oracle SQL Server DB2 PostgreSQL 
SELECT product_name, product_type, sale_price, RANK () 
OVER (ORDER BY sale_price) AS ranking 
FROM Product;

窗口函数兼具之前我们学过的GROUP BY 子句的分组功能以及ORDER BY 子句的排序功能。但是，PARTITION BY 子句并不具备GROUP BY 子句的汇总功能（合并重复行）。因此，使用RANK 函数并不会减少原表中记录的行数。

●RANK函数

计算排序时，如果存在相同位次的记录，则会跳过之后的位次。

例）有3 条记录排在第1 位时：1 位、1 位、1 位、4 位……

●DENSE_RANK函数

同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。

例）有3 条记录排在第1 位时：1 位、1 位、1 位、2 位……

●ROW_NUMBER函数

赋予唯一的连续位次。

例）有3 条记录排在第1 位时：1 位、2 位、3 位、4 位……

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

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;

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

计算current_sum该合计值的逻辑就像金字塔堆积那样，一行一行逐渐添加计算对象。在按照时间序列的顺序，计算各个时间的销售额总额等的时候，通常都会使用这种称为累计的统计方法。

同理SUM可以换成AVG等函数。

计算移动平均指定“最靠近的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;

product_id product_name sale_price moving_avg

----------- ------------- ------------- ------------

0001 T恤衫 1000 1000 ←(1000)/1

0002 打孔器 500 750 ←(1000+500)/2

0003 运动T恤 4000 1833 ←(1000+500+4000)/3

0004 菜刀 3000 2500 ←(500+4000+3000)/3

0005 高压锅 6800 4600 ←(4000+3000+6800)/3

0006 叉子 500 3433

0007 擦菜板 880 2726

0008 圆珠笔 100 493

使用了ROWS（“行”）和PRECEDING（“之前”）两个关键字，将框架指定为“截止到之前~ 行”，因此“ROWS 2 PRECEDING”就是将框架指定为“截止到之前2 行”，也就是将作为汇总对象的记录限

定为如下的“最靠近的3 行”。

● 自身（当前记录）

● 之前1行的记录

● 之前2行的记录

由于框架是根据当前记录来确定的，因此和固定的窗口不同，其范围会随着当前记录的变化而变化。

这样的统计方法称为移动平均（moving average）。由于这种方法在希望实时把握“最近状态”时非常方便，因此常常会应用在对股市趋势的实时跟踪当中。

使用关键字FOLLOWING（“之后”）替换PRECEDING，就可以指定“截止到之后~ 行”作为框架了。

同时使用PRECEDING（“之前”）和FOLLOWING（“之后”）关键字来实现将当前记录的前后行作为汇总对象。

Oracle SQL　Server DB2 PostgreSQL 
SELECT product_id, product_name, sale_price, AVG (sale_price) 
OVER (ORDER BY product_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg 
FROM Product;

“ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING”

将如下3 行作为汇总对象来进行计算：

● 之前1行的记录

● 自身（当前记录）

● 之后1行的记录

OVER 子句中的ORDER BY 只是用来决定窗口函数按照什么样的顺序进行计算的，对结果的排列顺序并没有影响。这两个ORDER BY 看上去是相同的，但其实它们的功能却完全不同。

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

8-2 GROUPING运算符

●只使用GROUP BY子句和聚合函数是无法同时得出小计和合计的。如果想要同时得到，可以使用GROUPING运算符。

●理解GROUPING运算符中CUBE的关键在于形成“积木搭建出的立方体”的印象。

●虽然GROUPING运算符是标准SQL的功能，但还是有些DBMS尚未支持这一功能。

GROUPING 运算符包含以下 3 种：

●ROLLUP

●CUBE

●GROUPING SETS

目前 PostgreSQL 和 MySQL 并不支持GROUPING运算符（MySQL 仅支持ROLLUP）。

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

Oracle SQL Server DB2 PostgreSQL 
SELECT product_type, SUM(sale_price) AS sum_price 
FROM Product
GROUP BY ROLLUP(product_type);

product_type sum_price

-------------- ---------

16780

厨房用具 11180

办公用品 600

衣服 5000

在MySQL中，GROUP BY子句改写为“GROUP BY product_type WITH ROLLUP;”。

相当于

SELECT '合计' AS product_type, SUM(sale_price) 
FROM Product 
UNION ALL 
SELECT product_type, SUM(sale_price) 
FROM Product 
GROUP BY product_type;

ROLLUP（ < 列 1>,< 列 2>,...）该运算符的作用就是“一次计算出不同聚合键组合的结果”。例如，在本例中就是一次计算出了如下两种组合的汇总结果。

① GROUP BY () 所有记录作为一组

② GROUP BY (product_type)按照产品类型分组

代码清单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);

结果相当于使用 UNION 对如下 3 种模式的聚合级的不同结果进行连接。

① GROUP BY ()

② GROUP BY (product_type)

③ GROUP BY (product_type, regist_date)

SQL 提供了一个用来判断超级分组记录的 NULL 的特定函数 —— GROUPING 函数。该函数在其参数列的值为超级分组记录（合计累类的）所产生的 NULL 时返回 1，其他情况返回 0。

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

代码清单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,                     
    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

CUBE

① GROUP BY ()

② GROUP BY (product_type)

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

④ GROUP BY (product_type, regist_date)

所谓 CUBE，就是将 GROUP BY 子句中聚合键的“所有可能的组合”的汇总结果集中到一个结果中。因此，组合的个数就是 2＾n（n 是聚合键的个数）。本例中聚合键有 2 个，所以 2＾2 = 4。如果再添加 1 个变为 3 个聚合键的话，就是 2＾3 = 8。

代码清单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);

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

之前的 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);