POSTGRESQL
1.安装
下载地址：https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
本环境安装目录为：D:\Program Files (x86)\PostgreSQL
#启动psql
d:
cd /Program Files (x86)/PostgreSQL/13/
./bin/pg_ctl -D ./data start
./bin/psql postgres
2.PLSQL常用操作命令
| 命令 | 含义 |
|---|---|
| \l | 查看已经存在的数据库 |
| \c <数据库名> | 连接指定数据库 |
| \d | 查看已经存在的表 |
| \q | 退出pssql |
3.创建数据库
CREATE DATABASE shop; --创建数据库shop
4.创建表
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));
5.基本数据类型
| 数据类型 | 含义 |
|---|---|
| INTEGER | 数字型，不能存储小数 |
| CHAR | 定长字符型，CHAR(10)表示最多输入10长度的字符，不足10长度，以空格补齐 |
| VARCHAR | 可变长字符型 |
| DATE | 日期型 |
6.删除表
DROP TABLE Product;
7.表中列的操作
ALTER TABLE Product ADD COLUMN product_name_pinyin VARCHAR(100); --表中增加列
ALTER TABLE Product DROP COLUMN product_name_pinyin; --表中删除列
BEGIN TRANSACTION; --表中插入数据
INSERT INTO Product VALUES('0001','T恤衫'，'衣服',1000,500,'2009-09-20');
COMMIT;
练习题
练习一：
编写一条CREATE TABLE语句，用来创建一个包含下表中所列各项的表Addressbook（地址簿），并为regist_no（注册编号）列设置主键约束。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nviUCB74-1610353893932)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20201214182721962.png)]
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));
练习二：
假设在创建练习1.1中的Addressbook表时忘记添加如下一列postal_code（邮政编码）了，请把此列添加到Addressbook表中。列名：postal_code数据类型：定长字符串类型（长度为8）约束：不能为NULL。
ALTER TABLE Addressbook ADD COLUMN postal_code CHAR(8) NOT NULL;
练习三：
编写SQL语句来删除Addressbook表。
DROP TABLE Addressbook;
8.SELECT语句
语法：SELECT <列名> FROM <表名>；
子句：DISTINCT：从查询结果中删除重复行
WEHRE：指定行所对应的条件
AS：为列设置别名
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-09-20
0006 | 叉子 | 厨房用具 | 500 | | 2009-09-20
0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28
0008 | 圆珠笔 | 办公用品 | 100 | | 2009-11-11
(8 行记录)
SELECT DISTINCT product_type FROM Prodcut;
product_type
--------------
衣服
办公用品
厨房用具
(3 行记录)
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 | 圆珠笔 |
(8 行记录)
SELECT product_name, product_type WHERE product_type='衣服';
product_name | product_type
--------------+--------------
T恤衫 | 衣服
运动T恤 | 衣服
(2 行记录)
9.注释
| 注释符 | 含义 |
|---|---|
| –<注释内容> | 单行注释 |
| /*<注释内容>*/ | 多行注释 |
10.运算符
四则运算
| 运算符 | 含义 |
|---|---|
| + | |
| - | |
| * | |
| / |
示例
SELECT (100+200)*3 AS colculation;
calculation
-------------
900
(1 行记录)
比较运算
| 运算符 | 含义 |
|---|---|
| = | 等于 |
| <> | 不等于 |
| >= | 大于等于 |
| > | 大于 |
| <= | 小于等于 |
| < | 小于 |
| IS NULL | 空 |
| IS NOT NULL | 非空 |
示例
SELECT product_id, product_name, purchase_price FROM product WHERE purchase_price=500;
product_id | product_name | purchase_price
------------+--------------+----------------
0001 | T恤衫 | 500
(1 行记录)
SELECT product_id, product_name, purchase_price FROM product WHERE purchase_price<>500;
product_id | product_name | purchase_price
------------+--------------+----------------
0002 | 打孔器 | 320
0003 | 运动T恤 | 2800
0004 | 菜刀 | 2800
0005 | 高压锅 | 5000
0007 | 擦菜板 | 790
(5 行记录)
字符串比较运算
‘1’<‘10’<‘2’<‘222’<‘3’
示例
CREATE TABLE Chars(
chr CHAR(3) NOT NULL,
PRIMARY KEY (chr)); --创建表Chars
BEGIN TRANSACTION;
INSERT INTO Chars VALUES ('1');
INSERT INTO Chars VALUES ('2');
INSERT INTO Chars VALUES ('3');
INSERT INTO Chars VALUES ('10');
INSERT INTO Chars VALUES ('11');
INSERT INTO Chars VALUES ('222');
COMMIT; --插入6行数据
SELECT * FROM Chars; --查询Chars表中数据
chr
-----
1
2
3
10
11
222
(6 行记录)
SELECT chr FROM Chars WHERE chr>'2'; --从Chars表中查询大于'2'的字符
chr
-----
3
222
(2 行记录)
注意
不能对NULL进行比较运算，如果需要选取不是NULL的记录时，需要使用IS NOT 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
(6 行记录)
逻辑运算
| 运算符 | 含义 |
|---|---|
| NOT | 非 |
| AND | 且 |
| OR | 或 |
示例
SELECT product_name, purechase_price FROM product WHERE product_type='厨房用具' AND sale_price>=3000;
product_name | purchase_price
--------------+----------------
菜刀 | 2800
高压锅 | 5000
(2 行记录)
SELECT product_name, purchase_price FROM product WHERE product_type='厨房用具' OR sale_price>=30000;
--------------+----------------
菜刀 | 2800
高压锅 | 5000
叉子 |
擦菜板 | 790
(4 行记录)
练习题
练习题一：
编写一条SQL语句，从Product（商品）表中选取出“登记日期（regist_date）在2009年4月28日之后”的商品。查询结果要包含product_name和regist_date两列。
SELECT product_name, regist_date FROM Product WHERE regist_date>'2009-04-28';
product_name | regist_date
--------------+-------------
T恤衫 | 2009-09-20
打孔器 | 2009-09-11
菜刀 | 2009-09-20
高压锅 | 2009-09-20
叉子 | 2009-09-20
圆珠笔 | 2009-11-11
(6 行记录)
练习题二：
请说出对Product表执行如下3条SELECT语句时的返回结果。
SELECT * FROM product WHERE purchase_price=NULL;
--无结果
SELECT * FROM product WHERE purchase_price<>NULL;
--无结果
SELECT * FROM product WHERE purchase_price>NULL;
--无结果
--原因为不能与NULL做比较运算，如果需要的话，可以使用IS NOT NULL或IS NULL
练习题三：
从Product表中取出“销售单价（sale_price）比进货单价（purchase_price）高出500日元及以上”的商品。请写出两条可以得到相同结果的SELECT语句。执行结果如下所示。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vyDvJ0Pg-1610353893935)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20201214184915126.png)]
SELECT product_name, sale_price, purchase_price FROM Product WHERE (sale_price-purchase)>=500;
练习题四：
请写出一条SELECT语句，从Product表中选取出满足“销售单价打九折之后利润高于100日元的办公用品和厨房用具”条件的记录。查询结果要包括product_name列、product_type列以及销售单价打九折之后的利润（别名设定为profit）。
SELECT product_name, product_type, (sale_price*0.9) AS profit
FROM Product
WHERE (sale_price*0.9-purchase_price)>100
AND (product_type='办公用品' OR product_type='厨房用具');
product_name | product_type | profit
--------------+--------------+--------
打孔器 | 办公用品 | 450.0
高压锅 | 厨房用具 | 6120.0
(2 行记录)
11.聚合函数
将多行汇总为一行的函数，即输入多行、输出一行
注意，聚合行数会将NULL排除在，COUNT(*)例外。
| 常用函数 | 含义 | 备注 |
|---|---|---|
| COUNT | 计算表中的记录数（行数） | COUNT(*)会得到所有列（即一张表）的行数，COUNT(<列名>)会得到NULL之外的数据行数 |
| SUM | 计算表中数值列中数据的合计值 | NULL为0，只适用于数值类型 |
| AVG | 计算表中数值列中数据的平均值 | NULL所在的行不算在分母中，只适用于数值类型 |
| MAX | 计算表中任意列数据的最大值 | |
| MIN | 计算表中任意列数据的最小值 |
示例
SELECT COUNT(*) FROM product;
count
-------
8
(1 行记录)
SELECT COUNT(purchase_price) FROM product; //purchase_price列中有两行值为NULL
count
-------
6
(1 行记录)
SELECT SUM(sale_price) FROM product;
sum
-------
16780
(1 行记录)
SELECT SUM(sale_price), SUM(purchase_price) FROM product;
sum | sum
-------+-------
16780 | 12210
(1 行记录)
SELECT AVG(sale_price), AVG(purchase_price) FROM product;
avg | avg
-----------------------+-----------------------
2097.5000000000000000 | 2035.0000000000000000
(1 行记录)
SELECT MAX(sale_price), min(purchase_price) FROM product;
max | min
------+-----
6800 | 320
(1 行记录)
12.GROUP BY子句
对指定列的数据进行分组。
注意：
- GROUP BY子句中指定的列名被称之为聚合键。当使用GROUP BY子句时，SELECT子句中不能出现聚合键之外的列名。
- GROUP BY子句结果的显示是无序的。
- 只有SELECT子句和HAVING子句（以及ORDER BY子句）中能够使用聚合函数。
示例：
SELECT product_type, COUNT(*) FROM product GROUP BY product_type;
product_type | count
--------------+-------
衣服 | 2
办公用品 | 2
厨房用具 | 4
(3 行记录)
13.HAVING子句
指定组所对应的条件，一般用于对分组后的数据进行过滤。
示例：
SELECT product_type, COUNT(*) FROM product GROUP BY product_type HAVING COUNT(*)=2;
--按照product_type分组之后，筛选出行数为2的组
product_type | count
--------------+-------
衣服 | 2
办公用品 | 2
(2 行记录)
SELECT product_type, AVG(sale_price) FROM product GROUP BY product_type HAVING AVG(sale_price)>=2500; --按照product_type分组之后，筛选出平均价格大于等于2500的组
product_type | avg
--------------+-----------------------
衣服 | 2500.0000000000000000
厨房用具 | 2795.0000000000000000
(2 行记录)
14.ORDER BY子句
对查询结果进行排序。默认升序（关键字ASC）。
ORDER BY子句被称为排序键。
ORDER BY可以指定多个排序键，排序规则为优先使用左侧的键。
排序键中包含NULL时，会在开头或末尾进行汇总。
排序键可以使用别名。
SELECT product_id, product_name, sale_price, purchase_price FROM product ORDER BY sale_price;
product_name | product_type | sale_price | purchase_price
--------------+--------------+------------+----------------
圆珠笔 | 办公用品 | 100 |
叉子 | 厨房用具 | 500 |
打孔器 | 办公用品 | 500 | 320
擦菜板 | 厨房用具 | 880 | 790
T恤衫 | 衣服 | 1000 | 500
菜刀 | 厨房用具 | 3000 | 2800
运动T恤 | 衣服 | 4000 | 2800
高压锅 | 厨房用具 | 6800 | 5000
(8 行记录)
SELECT product_id, product_name, sale_price, purchase_price FROM product ORDER BY sale_price DESC; --对查询结果进行降序
product_name | product_type | sale_price | purchase_price
--------------+--------------+------------+----------------
高压锅 | 厨房用具 | 6800 | 5000
运动T恤 | 衣服 | 4000 | 2800
菜刀 | 厨房用具 | 3000 | 2800
T恤衫 | 衣服 | 1000 | 500
擦菜板 | 厨房用具 | 880 | 790
打孔器 | 办公用品 | 500 | 320
叉子 | 厨房用具 | 500 |
圆珠笔 | 办公用品 | 100 |
(8 行记录)
SELECT product_id, product_name, sale_price, purchase_price FROM product ORDER BY sale_price, product_id; --当sale_price相同时，按照product_id排序
product_name | product_type | sale_price | purchase_price
--------------+--------------+------------+----------------
圆珠笔 | 办公用品 | 100 |
打孔器 | 办公用品 | 500 | 320
叉子 | 厨房用具 | 500 |
擦菜板 | 厨房用具 | 880 | 790
T恤衫 | 衣服 | 1000 | 500
菜刀 | 厨房用具 | 3000 | 2800
运动T恤 | 衣服 | 4000 | 2800
高压锅 | 厨房用具 | 6800 | 5000
(8 行记录)
SELECT product_id AS id, product_name, sale_price AS sp, purchase_price FROM product ORDER BY sp, id; --排序键可以使用sale_price的别名sp和product_id的别名id
id | product_name | sp | purchase_price
------+--------------+------+----------------
0008 | 圆珠笔 | 100 |
0002 | 打孔器 | 500 | 320
0006 | 叉子 | 500 |
0007 | 擦菜板 | 880 | 790
0001 | T恤衫 | 1000 | 500
0004 | 菜刀 | 3000 | 2800
0003 | 运动T恤 | 4000 | 2800
0005 | 高压锅 | 6800 | 5000
(8 行记录)
练习题
练习题一：
请指出下述SELECT语句中所有的语法错误。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XZQXSuTE-1610353893937)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20201215145414493.png)]
WHERE子句是对行进行过滤。如果要对分组后的结果进行过滤，需要使用HAVING子句。
练习题二：
请编写一条SELECT语句，求出销售单价（sale_price列）合计值是进货单价（purchase_price列）合计值1.5倍的商品种类。执行结果如下所示。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Iu9scPuS-1610353893939)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20201215145606607.png)]
SELECT prodcut_type, SUM(sale_price), SUM(purchase_price) FROM product GROUP BY product_type HAVING SUM(sale_price)>SUM(purchase_price)*1.5;
product_type | sum | sum
--------------+------+------
衣服 | 5000 | 3300
办公用品 | 600 | 320
(2 行记录)
练习三：
此前我们曾经使用SELECT语句选取出了Product（商品）表中的全部记录。当时我们使用了ORDER BY子句来指定排列顺序，但现在已经无法记起当时如何指定的了。请根据下列执行结果，思考ORDER BY子句的内容。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5kzbXrUE-1610353893941)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20201215161510676.png)]
SELECT * FROM product ORDER BY regist_date DESC;
product_id | product_name | product_type | sale_price | purchase_price | regist_date
------------+--------------+--------------+------------+----------------+-------------
0003 | 运动T恤 | 衣服 | 4000 | 2800 |
0008 | 圆珠笔 | 办公用品 | 100 | | 2009-11-11
0001 | T恤衫 | 衣服 | 1000 | 500 | 2009-09-20
0006 | 叉子 | 厨房用具 | 500 | | 2009-09-20
0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-09-20
0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20
0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11
0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28
(8 行记录)
15.INSERT语句
向表中插入数据（行）。可以插入多行，原则上每次执行插入一行。
语法：INSERT INTO <表明> (列1, 列2, 列3…) VALUES (值1, 值2, 值3…)
DEFAULT关键字：创建表的时候，可以设置DEFAULT约束来设定初始值，在后续插入值的时候，可以不指定该值。
示例：
CREATE TABLE ProductIns(
product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
prodcut_type VARCHAR(32) NOT NULL,
sale_price INTEGER DEFAULT 0, --设置该值默认为0
purchase_price INTEGER,
regist_date DATE,
PRIMARY KEY (product_id)); --创建表ProductIns
INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20'); --插入一行数据(值清单)
INSERT INTO ProductIns VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11'),
('0003', '运动T恤', '衣服', 4000, 2800, NULL),
('0004', '菜刀', '厨房用品', 3000, 2800, '2009-09-20'); --插入多行数据，每个值清单用,隔开
INSERT INTO ProductIns VALUES('0007', '擦菜板', '厨房用具', DEFAULT, 790, '2009-04-28'); --通过DEFAULT关键字插入默认值
SELECT product_id, product_name, sale_price FROM ProductIns WHERE product_id='0007';
product_id | product_name | sale_price
------------+--------------+------------
0007 | 擦菜板 | 0
(1 行记录)
CREATE TABLE ProductCopy(
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)); --创建表ProductCopy
INSERT INTO ProductCopy(product_id, product_name, product_type, sale_price, purchase_price, regist_date) SELECT product_id, product_name, product_type, sale_price, purchase_price, regist_date FROM product;
--将product表中的数据复制黏贴到ProductCopy表中
SELECT * FROM ProductCopy;
product_id | product_name | product_type | sale_price | purchase_price | regist_date
------------+--------------+--------------+------------+----------------+-------------
0001 | T恤衫 | 衣服 | 1000 | | 2009-09-20
0002 | 打孔器 | 办公用品 | 500 | | 2009-09-11
0003 | 运动T恤 | 衣服 | 4000 | |
0004 | 菜刀 | 厨房用具 | 3000 | | 2009-09-20
0005 | 高压锅 | 厨房用具 | 6800 | | 2009-09-20
0006 | 叉子 | 厨房用具 | 500 | | 2009-09-20
0007 | 擦菜板 | 厨房用具 | 880 | | 2008-04-28
0008 | 圆珠笔 | 办公用品 | 100 | | 2009-11-11
(8 行记录)
16.DELETE语句
删除数据（行）。DELETE
会删除所有行，留下表头，而DROP语法：DELETE FROM <表名> WHERE <条件>
示例：
DELETE FROM product WHERE sale_price>=4000; --删除sale_price大于等于4000的行
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
0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20
0006 | 叉子 | 厨房用具 | 500 | | 2009-09-20
0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28
0008 | 圆珠笔 | 办公用品 | 100 | | 2009-11-11
(6 行记录)
17.UPDATE语句
更改（更新）表中的数据。可以使用WHERE子句和SET子句，SET子句可用于更新多列数据。
语法：UPDATE <表名> SET <列名>=<表达式> WEHERE <条件>;
使用UPDATE语句可以将值清空为NULL（但只限于未设置NOT NULL约束的列）。
示例：
UPDATE product SET regist_date='2009-10-10'; --修改regist_date列的数据为“2009-10-10”
SELECT * FROM product ORDER BY poroduct_id;
product_id | product_name | product_type | sale_price | purchase_price | regist_date
------------+--------------+--------------+------------+----------------+-------------
0001 | T恤衫 | 衣服 | 1000 | 500 | 2009-10-10
0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-10-10
0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-10-10
0006 | 叉子 | 厨房用具 | 500 | | 2009-10-10
0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2009-10-10
0008 | 圆珠笔 | 办公用品 | 100 | | 2009-10-10
(6 行记录)
UPDATE product SET sale_price=sale_price*10 WHERE product_type='厨房用具';
SELECT * FROM product ORDER BY product_id;
shop=# SELECT * FROM product ORDER BY product_id;
product_id | product_name | product_type | sale_price | purchase_price | regist_date
------------+--------------+--------------+------------+----------------+-------------
0001 | T恤衫 | 衣服 | 1000 | 500 | 2009-10-10
0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-10-10
0004 | 菜刀 | 厨房用具 | 30000 | 2800 | 2009-10-10
0006 | 叉子 | 厨房用具 | 5000 | | 2009-10-10
0007 | 擦菜板 | 厨房用具 | 8800 | 790 | 2009-10-10
0008 | 圆珠笔 | 办公用品 | 100 | | 2009-10-10
(6 行记录)
UPDATE product SET sale_price=sale_price*10, purchase_price=purchase_price/2 WHERE product_type='厨房用具'; --更新多列数据
SELECT * FROM product ORDER BY product_id;
product_id | product_name | product_type | sale_price | purchase_price | regist_date
------------+--------------+--------------+------------+----------------+-------------
0001 | T恤衫 | 衣服 | 1000 | 500 | 2009-10-10
0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-10-10
0004 | 菜刀 | 厨房用具 | 300000 | 1400 | 2009-10-10
0006 | 叉子 | 厨房用具 | 50000 | | 2009-10-10
0007 | 擦菜板 | 厨房用具 | 88000 | 395 | 2009-10-10
0008 | 圆珠笔 | 办公用品 | 100 | | 2009-10-10
(6 行记录)
18.事务
语法：
事务开始语句;
DML语句①；
DML语句②；
DML语句③；
…
事务结束语句;
| 数据库 | 事务开始语句 |
|---|---|
| MSSQL、PLSQL | BEGIN TRANSACTION; |
| MYSQL | START TRANSACTION; |
| ORACLE、DB2 | 无 |
| 事务结束语句 | 含义 |
|---|---|
| COMMIT | 提交事务 |
| ROLLBACK | 回滚 |
DBMS的ACID特性
| 特性 | 含义 |
|---|---|
| Atomicity（原子性） | 事务结束时，其中所包含的更新处理要么全部执行，要么完全不执行，也就是要么占有一切要么一无所有。 |
| Consisty（一致性） | 事务中包含的处理要满足数据库提前设置的约束，如主键约束或者NOT NULL约束等。 |
| Isolation（隔离性） | 保证不同事务之间互不干扰的特性。该特性保证了事务之间不会互相嵌套。此外，在某个事务中进行的更改，在该事务结束之前，对其他事务而言是不可见的。因此，即使某个事务向表中添加了记录，在没有提交之前，其他事务也是看不到新添加的记录的。 |
| Durability（持久性） | 在事务（不论是提交还是回滚）结束后，DBMS能够保证该时间点的数据状态会被保存的特性。即使由于系统故障导致数据丢失，数据库也一定能通过某种手段进行恢复。练习题 |
练习题
练习题一：
A先生在自己的计算机（电脑）上，使用CREATE TABLE语句创建出了一张空的Product（商品）表，并执行了如下的SQL语句向其中插入数据。
GEBIN TRANACTION:
INSERT INTO Product VALUES(
'0001', 'T恤衫', '衣服', 1000, 500, '2008-09-20');
INSERT INTO Product VALUES(
'0002', '打孔器', '办公用品', 500, 320, '2008-09-11');
INSERT INTO Product VALUES(
'0003', '运动T恤', '衣服', 4000, 2800, NULL);
紧接着，B先生使用其他的计算机连接上该数据库，执行了如下SELECT语句。这时B先生能得到怎样的查询结果呢？
SELECT * FROM Product;
无结果。由于DBMS的隔离性，当前的事务没有结束之前，对其他事务是不可见的。
练习题二：
如下所示，有一张包含3条记录的Product表
| 商品编号 | 商品名称 | 商品种类 | 销售单价 | 进货单价 | 登记日期 |
|---|---|---|---|---|---|
| 0001 | T恤衫 | 衣服 | 1000 | 500 | 2009-09-20 |
| 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 |
| 0003 | 运动T恤 | 衣服 | 40000 | 2800 |
使用如下的INSERT语句复制这3行数据，应该能将表中的数据增加为6行，请说出该语句的执行结果。
INSERT INTO Product SELECT * FROM Product;
结果只包含3条记录，因为主键重复。该操作违反了DBMS的一致性，事务的改变需要遵循原表的约束。
练习题三：
以练习二的Product表为基础，再创建另外一张包含利润列的新表ProductMargin（商品利润）。
--商品利润表
CREATE TABLE ProductMargin(
product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
sale_price INTEGER,
pruchase_price INTEGER,
margin INTEGER,
PRIMARY KEY(product_id));
请写出上述表中插入如下数据的SQL语句，其中的利润可以简单的通过Product表中的数据进行计算（销售单价-进货单价）得出。
| product_id | product_name | sale_price | purchase_price | margin |
|---|---|---|---|---|
| 0001 | T恤衫 | 1000 | 500 | 500 |
| 0002 | 打孔器 | 500 | 320 | 180 |
| 0003 | 运动T恤 | 4000 | 2800 | 1200 |
INSERT INTO ProductMargin(
product_id, product_name, sale_price, purchase_price, margin)
SELECT product_id, product_name, sale_price, purchase_price, sale_price-purchase_price FROM Product;
练习题四：
对练习三中的ProductMargin表的数据进行如下更改。
- 将运动T恤的销售单价从4000日元下调至3000日元。
- 根据上述结果再次计算运动T恤的利润。
更改后的ProductMargin表如下所示。请写出能够实现该变更的SQL语句。
| product_id | product_name | sale_price | purchase_price | margin |
|---|---|---|---|---|
| 0001 | T恤衫 | 1000 | 500 | 500 |
| 0002 | 打孔器 | 500 | 320 | 180 |
| 0003 | 运动T恤 | 3000 | 2800 | 200 |
--下调销售单价
UPDATE ProductMargin SET sale_price=3000 WHERE product_name='运动T恤';
--重新计算利润
UPDATE ProductMargin SET margin=saleprice-purchase_price;
19.视图
- 视图中保存的是SELECT语句，表中保存的是实际的数据。
- 使用视图可以完成跨表查询数据等操作。
- 创建试图需要使用CREATE VIEW语句，删除需要使用DROP VIEW语句。
- 视图中不能使用ORDER BY语句。
- 表中数据更新之后，视图也会自动更新。
- 如果要更新视图，需要对
--创建productsum(商品类型总计)视图
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
(3 行记录)
20.子查询
- 子查询是一次性视图（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
(3 行记录)
--WHERE子句中不能使用聚合函数
SELECT product_id, product_name, sale_price FROM product WHERE sale_price>AVG(sale_price);
2020-12-20 14:37:00.600 HKT [3536] 错误: 聚合函数不允许出现在WHERE中 第 75 个字符处
2020-12-20 14:37:00.600 HKT [3536] 语句: SELECT product_id, product_name, sale_price FROM product WHERE sale_price>AVG(sale_price);
错误: 聚合函数不允许出现在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
0005 | 叉子 | 6800
0004 | 菜刀 | 3000
(3 行记录)
--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
(2 行记录)
--当子查询返回多条（多行多列）数据时，会报错
SELECT product_id, product_name, sale_price FROM product WHERE sale_price>(SELECT AVG(sale_price) FROM product GROUP BY product_type);
2020-12-20 15:01:31.205 HKT [3536] 错误: 作为一个表达式使用的子查询返回了多列
2020-12-20 15:01:31.205 HKT [3536] 语句: SELECT product_id, product_name, sale_price FROM product WHERE sale_price>(SELECT AVG(sale_price) FROM product GROUP BY product_type);
错误: 作为一个表达式使用的子查询返回了多列
--以上情况可以使用关联子查询
SELECT product_type, product_name, sale_price FROM product AS p1 WHERE sale_price>(SELECT AVG(sale_price) FROM product AS p2 WHERE p1.product_type=p2.product_type GROUP BY product_type);
product_type | product_name | sale_price
--------------+--------------+------------
衣服 | 运动T恤 | 4000
办公用品 | 打孔器 | 500
厨房用具 | 叉子 | 6800
厨房用具 | 菜刀 | 3000
(4 行记录)
练习题
练习题一：
创建出满足下述三个条件的视图（视图名称为ViewPractice5_1）。使用Product（商品）表作为参照表，假设表中包含初始状态的8行数据。
-
条件1：销售单价大于等于1000日元。
-
条件2：登记日期是2009年9月20日。
-
条件3：包含商品名称、销售单价和登记日期三列。
CREATE VIEW ViewPractice5_1 AS SELECT product_name, sale_price, regist_date FROM product WHERE sale_price>=1000 AND regist_date='2009-09-20';
SELECT * FROM ViewPractice5_1;
product_name | sale_price | regist_date
--------------+------------+-------------
T恤衫 | 1000 | 2009-09-20
菜刀 | 3000 | 2009-09-20
(2 行记录)
练习题二：
向练习题一中创建的视图ViewPractice5_1中插入如下数据，会得到什么样的结果呢？
INSERT INTO ViewPractice5_1 VALUES('刀子', 300, '2009-11-02');
对视图的更新归根结底是对视图所对应的表进行更新。因此，该INSERT语句实质上和下面的INSERT语句相同。
INSERT INTO product VALUES(NULL, '刀子', NULL, 300, NULL, '2009-11-02');
而product_id，product_type都设置了NOT NULL的约束，因此，插入以上数据会发生错误。
练习题三：
编写SELECT语句，其中sale_price_all列为全部商品的平均销售单价。
SLECT product_id, product_name, product_type, sale_price, (SELECT AVG(sale_price) FROM product) AS sale_price_all FROM product ORDER BY product_id;
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
(8 行记录)
练习题四：
根据练习题一的条件编写一条SQL语句，创建一幅包含如下数据的视图（名称为AvgPriceByType）。
CREATE VIEW AvgPriceType AS SELECT product_id, product_name, product_type, sale_price, (SELECT AVG(sale_price) FROM product AS p2 WHERE p1.product_type=p2.product_type GROUP BY product_type) AS avg_sale_price FROM product AS p1 ORDER BY product_id;
SELECT * FROM AvgPriceType;
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 行记录)
21.函数
数学函数
| 函数 | 含义 |
|---|---|
| ABS(数值) | 数值的绝对值 |
| MOD(被除数， 除数) | 计算余数（SQL SERVER不支持该函数） SQL SERVER使用%取余数 |
| ROUND(对象数值， 保留小数的位数) | 四舍五入操作 |
--创建SampleMath表，表中内容如下：
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 | |
(11 行记录)
--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
(11 行记录)
--MOD示例
SELECT n, p, MOD(n, p) AS mod_col FROM SampleMath;
n | p | mod_col
---+---+---------
0 | |
0 | |
| |
7 | 3 | 1 --MOD(7, 3)=1
5 | 2 | 1 --MOD(5, 2)=1
4 | |
| 3 |
1 | |
2 | |
1 | |
| |
(11 行记录)
--ROUND示例
SELECT m, n, ROUND(m, n) AS round_col FROM SampleMath;
m | n | round_col
----------+---+-----------
500.000 | 0 | 500 --500.000四舍五入，取小数点后0位
-180.000 | 0 | -180
| |
| 7 |
| 5 |
| 4 |
8.000 | |
2.270 | 1 | 2.3 --2.270四舍五入，取小数点后1位
5.555 | 2 | 5.56 --5.555四舍五入，取小数点后两位
| 1 |
8.760 | |
(11 行记录)
字符串函数
| 函数 | 含义 |
|---|---|
| str1||str2 | str1和str2拼接 SQL SERVER使用“+”拼接 MYSQL及SQL SERVER 2012之后的版本使用CONCAT函数拼接 CONCAT(str1, str2, str3) |
| LENGTH(str) | 显示srt的长度 SQL SERVER使用LEN函数 |
| LOWER(str) | 将str中的字母全部转换位小写 |
| UPPER(str) | 将str中的字母全部转换位大写 |
| REPLACE(对象字符串， 替换前的字符串， 替换后的字符串) | 将对象字符串中包含替换前的字符串替换为替换后的字符串 |
| SUBSTRING(对象字符串 FROM 截取的起始位置 FOR截取的字符数) | 在字符串的指定位置，截取指定长度的字符串 |
--创建SampleStr表，表中内容如下：
SELECT * FROM SampleStr;
str1 | str2 | str3
-----------+------+------
opx | rt |
abc | def |
山田 | 太郎 | 是我
aaa | |
| xyz |
@!#$% | |
ABC | |
aBC | |
abc太郎 | abc | ABC
abcdefabc | abc | ABC
micmic | i | I
(11 行记录)
--拼接函数示例
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 行记录)
--LENGTH函数示例
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 行记录)
--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 行记录)
--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太郎 --abc太郎中的abc替换为ABC
abcdefabc | abc | ABC | ABCdefABC --abcdefabc中的abc替换为ABC
micmic | i | I | mIcmIc --micmicz中的i替换为I
(11 行记录)
--SUBSTRING函数示例
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
(11 行记录)
日期函数
| 函数 | 含义 |
|---|---|
| CURRENT_DATE | 当前日期 |
| CURRENT_TIME | 当前时间 |
| CURRENT_TIMESTAMP | 当前日期和时间 |
| EXTRACT(日期元素 FROM 日期) | 从日期中取出指定的日期元素 |
--示例
SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP;
current_date | current_time | current_timestamp
--------------+--------------------+-------------------------------
2020-12-20 | 23:30:22.804072+08 | 2020-12-20 23:30:22.804072+08
(1 行记录)
SELECT CURRENT_TIMESTAMP AS now_time,
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_time | year | month | day | hour | minute | second
-------------------------------+------+-------+-----+------+--------+-----------
2020-12-20 23:44:16.770124+08 | 2020 | 12 | 20 | 23 | 44 | 16.770124
(1 行记录)
转换函数
| 函数 | 含义 |
|---|---|
| CAST(转换前的值 AS 想要转换的数据类型) | 类型转换，将指定的值转换为别的数据类型 |
| COALESCE(数据1， 数据2， 数据3…) | 返回参数中左侧开始第一个不是NULL的值，可以用来将NULL转换为指定的字符串 |
--示例
ELECT COALESCE(NULL, 1) AS col_1,
COALESCE(NULL, 'test', NULL) AS col_2,
COALESCE(NULL, NULL, '2009-11-01') AS col_3;
col_1 | col_2 | col_3
-------+-------+------------
1 | test | 2009-11-01
(1 行记录)
SELECT COALESCE(str2, 'not_null') FROM SampleStr;
coalesce
----------
rt
def
太郎
not_null --NULL被转换为'not_null'
xyz
not_null --NULL被转换为'not_null'
not_null --NULL被转换为'not_null'
not_null --NULL被转换为'not_null'
abc
abc
i
(11 行记录)
22.谓词
谓词的作用就是“判断是否存在满足某种条件的记录”。如果存在这样的记录就返回指定值，如果不存在就返回假（FALSE）。
| 谓词 | 含义 |
|---|---|
| LIKE（字符串的部分一致查询） | 查询包含某些字符的数据。常与通配符搭配使用 百分号%：匹配一个或多个字符 下划线_：匹配一个字符 |
| BETWEEN（范围查询） | 选取介于两个值之间的数据范围内的值，值可以是数值、文本、日期 |
| IS NULL、IS NOT NULL | 判断是否为NULL |
| IN | 查询范围内的多个值，一般用于WHERE子句中，相当与OR。可以用于子查询 |
| EXISTS、NOT EXISTS | 与IN含义一样，可以用于子查询 |
示例：
--创建SampleLike表
CREATE TABLE SampleLike(
strcol NOT NULL,
PRIMARY KEY(strcol));
--插入数据
BEGIN 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;
--LIKE示例
SELECT * FROM SampleLike LIKE 'ddd%';
strcol
--------
dddabc
(1 行记录)
SELECT * FROM SampleLike LIKE '%ddd%';
strcol
--------
abcddd
dddabc
abdddc
SELECT * FROM SampleLike LIKE '%ddd';
strcol
--------
abcddd
(1 行记录)
SELECT * FROM SampleLike LIKE 'abc__';
strcol
--------
abcdd
(1 行记录)
SELECT * FROM SampleLike WHERE strcol LIKE 'abc___';
strcol
--------
abcddd
(1 行记录)
--BETWEEN示例
SELECT product_name, sale_price FROM product WHERE sale_price BETWEEN 100 AND 1000;
product_name | sale_price
--------------+------------
T恤衫 | 1000
打孔器 | 500
圆珠笔 | 100
擦菜板 | 880
叉子 | 500
(5 行记录)
--IS NULL和IS NOT NULL示例
SELECT product_name, purchase_price FROM product WHERE purchase_price IS NULL;
product_name | purchase_price
--------------+----------------
圆珠笔 |
叉子 |
(2 行记录)
SELECT product_name, purchase_price FROM product WHERE purchase_price IS NOT NULL;
product_name | purchase_price
--------------+----------------
运动T恤 | 2800
T恤衫 | 500
打孔器 | 320
叉子 | 5000
菜刀 | 2800
擦菜板 | 790
(6 行记录)
--IN示例
SELECT product_name, purchase_price FROM product WHERE purchase_price IN (320, 500, 5000);
product_name | purchase_price
--------------+----------------
T恤衫 | 500
打孔器 | 320
叉子 | 5000
(3 行记录)
--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));
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', 20);
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;
SELECT product_name, sale_price FROM product WHERE product_id IN (SELECT product_id FROM ShopProduct WHERE shop_id='000A');
--EXIST示例
SELECT product_name, sale_price FROM product AS p WHERE EXISTS (SELECT product_id FROM ShopProduct AS sp WHERE sp.shop_id='000C' AND sp.product_id=p.product_id);
23.CASE
语法：
CASE WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
…
ELSE <表达式>
END
示例：
--CASE示例
SELECT product_type,
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:衣服
T恤衫 | A:衣服
打孔器 | B:办公用品
叉子 | C:厨房用具
圆珠笔 | B:办公用品
菜刀 | C:厨房用具
擦菜板 | C:厨房用具
叉子 | C:厨房用具
(8 行记录)
--使用CASE进行行列转换
SELECT product_type, SUM(sale_price) AS sum_sale_price FROM product GROUP BY product_type;
product_type | sum_sale_price
--------------+----------------
衣服 | 5000
办公用品 | 600
厨房用具 | 11180
(3 行记录)
SELECT SUM(CASE WHEN product_type='衣服' THEN sale_price ELSE 0 END) AS sum_clothes,
SUM(CASE WHEN product_type='办公用品' THEN sale_price ELSE 0 END) AS sum_offce,
SUM(CASE WHEN product_type='厨房用具' THEN sale_price ELSE 0 END) AS sum_kitchen FROM product;
sum_clothes | sum_offce | sum_kitchen
-------------+-----------+-------------
5000 | 600 | 11180
(1 行记录)
--简单CASE表达式
SELECT product_type, 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;
product_type | abc_product_type
--------------+------------------
衣服 | A:衣服
衣服 | A:衣服
办公用品 | B:办公用品
厨房用具 | C:厨房用具
办公用品 | B:办公用品
厨房用具 | C:厨房用具
厨房用具 | C:厨房用具
厨房用具 | C:厨房用具
(8 行记录)
练习题
练习题一：
对本章中使用的Product（商品）表执行如下2条SELECT语句，能够得到什么样的结果呢？
SELECT product_name, purchase_price FROM product WHERE purchase_price NOT IN (500, 2800, 5000);
SELECT product_name, purchase_price FROM product WHERE purchase_price NOT IN (500, 2800, 5000, NULL);
第一条SELECT语句可以得出正常结果，第二条为空，应为对于NULL的判断应该使用IS NOT NULL。
练习题二：
按照销售单价（sale_price）对练习题一中的Product（商品）表中的商品进行如下分类。
● 低档商品：销售单价在1000日元以下（T恤衫、办公用品、叉子、擦菜板、圆珠笔）
● 中档商品：销售单价在1001日元以上3000日元以下（菜刀）
● 高档商品：销售单价在3001日元以上（运动T恤、高压锅）
请编写出统计上述商品种类中所包含的商品数量的SELECT语句，结果如下所示。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-s6EiJt6z-1610353893942)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20201225003117185.png)]
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>=3000 THEN 1ELSE 0 END) as high_price FROM product;
24.集合运算
| 分类 | 运算符 | 含义 |
|---|---|---|
| 以行为单位的表运算 | UNION | 表的并集 默认会去重，UNION ALL可以保留重复项 |
| 以行为单位的表运算 | INTERSET | 表的交集 |
| 以行为单位的表运算 | EXCEPT | 表的差集 MYSQL不支持，Oracle使用MINUS |
| 以列为单位的表运算 | INNER JOIN | 根据连接谓词结合两个表（table1 和 table2）的列值来创建一个新的结果表。查询会把 table1 中的每一行与 table2 中的每一行进行比较，找到所有满足连接谓词的行的匹配对。 内联结只能取出同时存在于两张表中的数据 在FROM子句中使用多张表 ON为联结键，指定多张表连接所使用的列，必须要书写在FROM和WHERE之间，不可省略。 使用联结时，需要按照“<表的别名>.<列名>”的格式 |
| 以列为单位的表运算 | OUTER JOIN | 首先，执行内部连接。然后，对于表 T1 中不满足表 T2 中任何行连接条件的每一行，如果 T2 的列中有 null 值也会添加一个到结果中。此外，对于 T2 中不满足与 T1 中的任何行连接条件的每一行，将会添加 T1 列中包含 null 值的到结果中。 由LEFT和RIGHT指定主表，使用LEFT时，FROM子句中写在左侧的表为主表，使用RIGHT时，右侧的表为主表。 |
| 以列为单位的表运算 | CROSS JOIN | 又被称为外联结，一般不用于实际业务中。 第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有 x 和 y 行，则结果表有 x*y 行。 |
示例：
--创建表product2
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,
RIMARY KEY (product_id));
--表中插入数据
BEGIN TRANSACTION;
INSERT INTO product2 VALUES('0001', 'T恤衫', '衣服', 1000, 500, '2008-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用法示例
SELECT product_id, product_name FROM product UNION SELECT product_id, product_name FROM product2 ORDER BY product_id;
product_id | product_name --查询结果可以看出，UNION默认会去重
------------+--------------
0001 | T恤衫
0002 | 打孔器
0003 | 运动T恤
0004 | 菜刀
0005 | 叉子
0006 | 叉子
0007 | 擦菜板
0008 | 圆珠笔
0009 | 手套
0010 | 水浒
(10 行记录)
SELECT product_id, product_name FROM product UNION ALL SELECT product_id, product_name FROM product2 ORDER BY product_id; --ALL选项不会去重
product_id | product_name
------------+--------------
0001 | T恤衫
0001 | T恤衫
0002 | 打孔器
0002 | 打孔器
0003 | 运动T恤
0003 | 运动T恤
0004 | 菜刀
0005 | 叉子
0006 | 叉子
0007 | 擦菜板
0008 | 圆珠笔
0009 | 手套
0010 | 水浒
(13 行记录)
--INTERSECT用法示例
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恤
(3 行记录)
--EXCEP用法示例
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 | 圆珠笔
(5 行记录)
/*INNER JION用法示例
两张表内联结(shopproduct和product)*/
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.prodcut_name, P.sale_price FROM shopproduct AS SP INNER JOIN product AS P 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 | 名古屋 | 0003 | 运动T恤 | 4000
000B | 名古屋 | 0002 | 打孔器 | 500
000B | 名古屋 | 0006 | 叉子 | 500
000B | 名古屋 | 0007 | 擦菜板 | 880
000C | 大阪 | 0003 | 运动T恤 | 4000
000C | 大阪 | 0004 | 菜刀 | 3000
000C | 大阪 | 0006 | 叉子 | 500
000C | 大阪 | 0007 | 擦菜板 | 880
000D | 福冈 | 0001 | T恤衫 | 1000
000B | 名古屋 | 0004 | 菜刀 | 3000
(13 行记录)
--三张表内联结(shopproduct、product、inventoryproduct)
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 ON SP.product_id=P.product_id INNER JOIN inventoryproduct AS IP 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 | 名古屋 | 0003 | 运动T恤 | 4000 | 200
000B | 名古屋 | 0002 | 打孔器 | 500 | 120
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
000B | 名古屋 | 0004 | 菜刀 | 3000 | 3
(13 行记录)
--OUTER JOIN用法示例
--RIGHT OUTER JOIN
SELECT SP.shop_id, SP.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;
shop_id | shop_name | product_id | product_name | sale_price
---------+-----------+------------+--------------+------------
000A | 东京 | 0001 | T恤衫 | 1000
000A | 东京 | 0002 | 打孔器 | 500
000A | 东京 | 0003 | 运动T恤 | 4000
000B | 名古屋 | 0003 | 运动T恤 | 4000
000B | 名古屋 | 0002 | 打孔器 | 500
000B | 名古屋 | 0006 | 叉子 | 500
000B | 名古屋 | 0007 | 擦菜板 | 880
000C | 大阪 | 0003 | 运动T恤 | 4000
000C | 大阪 | 0004 | 菜刀 | 3000
000C | 大阪 | 0006 | 叉子 | 500
000C | 大阪 | 0007 | 擦菜板 | 880
000D | 福冈 | 0001 | T恤衫 | 1000
000B | 名古屋 | 0004 | 菜刀 | 3000
| | 0008 | 圆珠笔 | 100
| | 0005 | 叉子 | 6800
(15 行记录)
--LEFT OUTER JOIN
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price FROM shopproduct AS SP LEFT OUTER JOIN product AS P 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 | 名古屋 | 0003 | 运动T恤 | 4000
000B | 名古屋 | 0002 | 打孔器 | 500
000B | 名古屋 | 0006 | 叉子 | 500
000B | 名古屋 | 0007 | 擦菜板 | 880
000C | 大阪 | 0003 | 运动T恤 | 4000
000C | 大阪 | 0004 | 菜刀 | 3000
000C | 大阪 | 0006 | 叉子 | 500
000C | 大阪 | 0007 | 擦菜板 | 880
000D | 福冈 | 0001 | T恤衫 | 1000
000B | 名古屋 | 0004 | 菜刀 | 3000
(13 行记录)
--CROSS JOIN用法示例
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name FROM shopproduct AS SP CROSS JOIN product AS P;
shop_id | shop_name | product_id | product_name
---------+-----------+------------+--------------
000A | 东京 | 0001 | 运动T恤
000A | 东京 | 0002 | 运动T恤
000A | 东京 | 0003 | 运动T恤
000B | 名古屋 | 0003 | 运动T恤
000B | 名古屋 | 0002 | 运动T恤
000B | 名古屋 | 0006 | 运动T恤
000B | 名古屋 | 0007 | 运动T恤
000C | 大阪 | 0003 | 运动T恤
000C | 大阪 | 0004 | 运动T恤
......
000C | 大阪 | 0006 | 叉子
000C | 大阪 | 0007 | 叉子
000D | 福冈 | 0001 | 叉子
000B | 名古屋 | 0004 | 叉子
(104 行记录) --13行*8行，总共有104行记录
练习题
练习题一：
请说出下述SELECT语句的结果。
SELECT * FROM product UNION SELECT * FROM product INTERSECT SELECT * FROM product ORDER BY product_id;
/*先取并集，结果为product表中所有数据，再与product表取交集，结果为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
(8 行记录)
练习题二：
上述RIGHT OUTER JOIN外联结的结果中，高压锅和圆珠笔2条记录的商店编号（shop_id）和商店名称（shop_name）都是NULL。请使用字符串“不确定”替换其中的NULL。期望结果如下所示。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-L1neF3Id-1610353893943)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20201226174104574.png)]
SELECT COALESCE(SP.shop_id, '不确定') AS shop_id, COALESCE(SP.shop_name, '不确定') 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;
shop_id | shop_name | product_id | product_name | sale_price
---------+-----------+------------+--------------+------------
000A | 东京 | 0001 | T恤衫 | 1000
000A | 东京 | 0002 | 打孔器 | 500
000A | 东京 | 0003 | 运动T恤 | 4000
000B | 名古屋 | 0003 | 运动T恤 | 4000
000B | 名古屋 | 0002 | 打孔器 | 500
000B | 名古屋 | 0006 | 叉子 | 500
000B | 名古屋 | 0007 | 擦菜板 | 880
000C | 大阪 | 0003 | 运动T恤 | 4000
000C | 大阪 | 0004 | 菜刀 | 3000
000C | 大阪 | 0006 | 叉子 | 500
000C | 大阪 | 0007 | 擦菜板 | 880
000D | 福冈 | 0001 | T恤衫 | 1000
000B | 名古屋 | 0004 | 菜刀 | 3000
不确定 | 不确定 | 0008 | 圆珠笔 | 100
不确定 | 不确定 | 0005 | 叉子 | 6800
(15 行记录)
25.OLAP函数
-
含义：
OLAP(OnLine Analytial Processing)，对数据库数据进行实时分析处理。
窗口函数是为了实现OLAP而添加的标准SQL功能。
-
语法：
<窗口函数> OVER ([PARTITION BY <列清单>] ORDER BY <排序用列清单>)
窗口函数兼具分组和排序两种功能；
通过PARTITION BY分组后的记录集合称为窗口；
窗口函数只能在SELECT子句中使用，不能在WEHRE和GROUP BY子句中；
窗口函数类型 窗口函数 聚合函数 SUM、AVG、COUNT、MAX、MIN等 专用窗口函数 RANK：计算排序时，如果存在相同位次的记录，则会跳过之后的位次。例如，有3条记录排在第1位时：1位、1位、1位、4位…
DENE_RANK：计算排序，即使存在相同位次的记录，也不会跳过之后的位次。例如：有3条记录排在第1位时：1位、1位、1位、2位……
ROW_NUMBER：赋予唯一的连续位次。例如，有3条记录排在第1位时：1位、2位、3位、4位…… -
示例
--根据不同的商品种类，按照销售单价从低到高的顺序创建排序表 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 (8 行记录) --PARTITION BY可以省略 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 (8 行记录) --比较RANK、DENSE_RANK、ROW_NUMBER的区别 SELECT product_name, product_type, sale_price, RANK() OVER (ORDER BY sale_price) AS ranking, DENSE_RANK() OVER (ORDER BY sale_price), ROW_NUMBER() OVER (ORDER BY sale_price) FROM product; product_name | product_type | sale_price | ranking | dense_rank | row_number --------------+--------------+------------+---------+------------+------------ 圆珠笔 | 办公用品 | 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 (8 行记录) --聚合函数示例 --使用SUM函数作为窗口函数 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 0002 | 打孔器 | 500 | 1500 0003 | 运动T恤 | 4000 | 5500 0004 | 菜刀 | 3000 | 8500 0005 | 叉子 | 6800 | 15300 0006 | 叉子 | 500 | 15800 0007 | 擦菜板 | 880 | 16680 0008 | 圆珠笔 | 100 | 16780 (8 行记录) --使用AVG函数作为窗口函数 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 0002 | 打孔器 | 500 | 750.0000000000000000 0003 | 运动T恤 | 4000 | 1833.3333333333333333 0004 | 菜刀 | 3000 | 2125.0000000000000000 0005 | 叉子 | 6800 | 3060.0000000000000000 0006 | 叉子 | 500 | 2633.3333333333333333 0007 | 擦菜板 | 880 | 2382.8571428571428571 0008 | 圆珠笔 | 100 | 2097.5000000000000000 (8 行记录) --指定框架（汇总对象） SELECT product_id, product_name, sale_price, AVG(sale_price) OVER (ORDER BY product_id ROWS 1 PRECEDING) AS moving_avg FROM product; --ROWS 1 PRECEDING表示以当前行及之前的1行作为框架 product_id | product_name | sale_price | moving_avg ------------+--------------+------------+----------------------- 0001 | T恤衫 | 1000 | 1000.0000000000000000 --1000/1 0002 | 打孔器 | 500 | 750.0000000000000000 --(500+1000)/2 0003 | 运动T恤 | 4000 | 2250.0000000000000000 --(4000+500)/2 0004 | 菜刀 | 3000 | 3500.0000000000000000 --(3000+4000)/2 0005 | 叉子 | 6800 | 4900.0000000000000000 0006 | 叉子 | 500 | 3650.0000000000000000 0007 | 擦菜板 | 880 | 690.0000000000000000 0008 | 圆珠笔 | 100 | 490.0000000000000000 (8 行记录) --PRECEING换为FOLLOWING表示当前与之后行 --ROWS 1 PRECEDING AND 1 FOLLOWING表示当前行与前后1行 SELECT product_id, product_name, sale_price, AVG(sale_price) OVER (ORDER BY product_id ROWS BETWENN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg FROM product; product_id | product_name | sale_price | moving_avg ------------+--------------+------------+----------------------- 0001 | T恤衫 | 1000 | 750.0000000000000000 --(1000+500)/2 0002 | 打孔器 | 500 | 1833.3333333333333333 --(500+1000+4000)/3 0003 | 运动T恤 | 4000 | 2500.0000000000000000 --(4000+500+3000)/3 0004 | 菜刀 | 3000 | 4600.0000000000000000 0005 | 叉子 | 6800 | 3433.3333333333333333 0006 | 叉子 | 500 | 2726.6666666666666667 0007 | 擦菜板 | 880 | 493.3333333333333333 0008 | 圆珠笔 | 100 | 490.0000000000000000 (8 行记录)
26.GROUPING运算符
对聚合键组合的结果进行运算
| 运算符 | 含义 |
|---|---|
| ROLLUP | 一次计算出不同聚合键组合的结果。可以同时得出合计和小计。 |
| CUBE | 将GROUP BY子句中聚合键的“所有可能的组合”的汇总结果集中到一个结果中。 |
| GROUPING SETS | 用于从ROLLUP或者CUBE的结果中取出部分记录。 |
-
ROLLUP
一次计算出不同聚合键组合的结果。可以同时得出合计和小计。
SELECT product_type, SUM(sale_price) AS sum_price FROM product GROUP BY ROLLUP(product_type); /*执行流程 先计算GROUP BY()，再计算GROUP BY(product_type) */ /*MYSQL的写法： SELECT product_type, SUM(sale_price) AS sum_price FROM product GROUP BY product_type WIHT ROLLUP; */ product_type | sum_price --------------+----------- | 16780 衣服 | 5000 办公用品 | 600 厨房用具 | 11180 (4 行记录)GROUP()表示没有聚合键，相当于没有GROUP BY子句(即全部数据行的记录)，因此被称之为超级分组记录。其实，聚合键默认为NULL。
SELECT product_type, regist_date, SUM(sale_price) AS sum_price FROM product GROUP BY ROLLUP(product_type, regist_date); /*执行流程： 先计算GROUP BY(),再计算GROUP BY(product_type)，再计算GROUP BY(product_type, regist_date) */ product_type | regist_date | sum_price --------------+-------------+----------- | | 16780 衣服 | | 4000 厨房用具 | 2008-04-28 | 880 衣服 | 2009-09-20 | 1000 办公用品 | 2009-11-11 | 100 厨房用具 | 2009-01-15 | 6800 办公用品 | 2009-09-11 | 500 厨房用具 | 2009-09-20 | 3500 衣服 | | 5000 办公用品 | | 600 厨房用具 | | 11180 (11 行记录) -
GROUPING
GROUPING函数在其参数列的值为超级分组记录所产生的NULL时返回1，其他情况返回0。因此使用GROUPING函数能够便捷地分辨出原始数据中的NULL和超级分组记录中的NULL。
SELECT GROUPING(product_type) AS product_type, GROUPING(regist_date) AS regist_type, SUM(sale_price) AS sum_price FROM product GROUP BY ROLLUP(product_type, regist_date); product_type | regist_type | sum_price --------------+-------------+----------- 1 | 1 | 16780 0 | 0 | 4000 0 | 0 | 880 0 | 0 | 1000 0 | 0 | 100 0 | 0 | 6800 0 | 0 | 500 0 | 0 | 3500 0 | 1 | 5000 0 | 1 | 600 0 | 1 | 11180 (11 行记录) --在超级分组记录的键值中插入恰当的字符串 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 衣服 | | 4000 厨房用具 | 2008-04-28 | 880 衣服 | 2009-09-20 | 1000 办公用品 | 2009-11-11 | 100 厨房用具 | 2009-01-15 | 6800 办公用品 | 2009-09-11 | 500 厨房用具 | 2009-09-20 | 3500 衣服 | 登记日期 合计 | 5000 办公用品 | 登记日期 合计 | 600 厨房用具 | 登记日期 合计 | 11180 (11 行记录) -
CUBE
将GROUP BY子句中聚合键的“所有可能的组合”的汇总结果集中到一个结果中。因此，组合的个数就是2n（n是聚合键的个数）。
SELECT product_type, regist_date, SUM(sale_price) AS sum_price FROM product GROUP BY CUBE(product_type, regist_date); /*执行流程： 先计算GROUP BY()，再计算GROUP BY(product_type)，再计算GROUP BY(regist_date)，再计算GROUP BY(product_type, regist_date) */ product_type | regist_date | sum_price --------------+-------------+----------- | | 16780 衣服 | | 4000 厨房用具 | 2008-04-28 | 880 衣服 | 2009-09-20 | 1000 办公用品 | 2009-11-11 | 100 厨房用具 | 2009-01-15 | 6800 办公用品 | 2009-09-11 | 500 厨房用具 | 2009-09-20 | 3500 衣服 | | 5000 办公用品 | | 600 厨房用具 | | 11180 | | 4000 | 2009-11-11 | 100 | 2009-09-20 | 4500 | 2009-09-11 | 500 | 2009-01-15 | 6800 | 2008-04-28 | 880 (17 行记录) -
GROUPING SETS
用于从ROLLUP或者CUBE的结果中取出部分记录。
SELECT CASE WHEN GROUPING(product_type)=1 THEN '商品种类 合计' ELSE product_type END AS product_type, CASE WHEN GROUPING(regist_date)=1 THEN '登记日期 合计' ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date, SUM(sale_price) AS sum_price FROM product GROUP BY GROUPING SETS(product_type, regist_date); product_type | regist_date | sum_price ---------------+---------------+----------- 衣服 | 登记日期 合计 | 5000 办公用品 | 登记日期 合计 | 600 厨房用具 | 登记日期 合计 | 11180 商品种类 合计 | | 4000 商品种类 合计 | 2009-11-11 | 100 商品种类 合计 | 2009-09-20 | 4500 商品种类 合计 | 2009-09-11 | 500 商品种类 合计 | 2009-01-15 | 6800 商品种类 合计 | 2008-04-28 | 880 (9 行记录)
练习题
练习题一：
请说出针对product（商品）表执行如下SELECT语句所能得到的结果。
SELECT product_id, product_name, sale_price, MAX(sale_price) OVER (ORDER BY product_id) AS current_price FROM product;
product_id | product_name | sale_price | current_price
------------+--------------+------------+---------------
0001 | T恤衫 | 1000 | 1000
0002 | 打孔器 | 500 | 1000
0003 | 运动T恤 | 4000 | 4000
0004 | 菜刀 | 3000 | 4000
0005 | 叉子 | 6800 | 6800
0006 | 叉子 | 500 | 6800
0007 | 擦菜板 | 880 | 6800
0008 | 圆珠笔 | 100 | 6800
(8 行记录)
练习题二：
继续使用product表，计算出按照登记日期（regist_date）升序进行排列的各日期的销售单价（sale_price）的总额。排序是需要将登记日期为NULL的“运动T恤”记录排在第1位（也就是将其看作比其他日期都早）。
SELECT product_name, sale_price, regist_date, SUM(sale_price) OVER (ORDER BY regist_date NULLS FIRST) AS current_sum_price FROM product;
product_name | sale_price | regist_date | current_sum_price
--------------+------------+-------------+-------------------
运动T恤 | 4000 | | 4000
擦菜板 | 880 | 2008-04-28 | 4880
叉子 | 6800 | 2009-01-15 | 11680
打孔器 | 500 | 2009-09-11 | 12180
叉子 | 500 | 2009-09-20 | 16680
T恤衫 | 1000 | 2009-09-20 | 16680
菜刀 | 3000 | 2009-09-20 | 16680
圆珠笔 | 100 | 2009-11-11 | 16780
(8 行记录)
401
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
