SQL 的简单教程(Mac 下 PostgreSQL 的安装与使用)(2)
七、集合运算
7-1 表的加减法
- 集合运算就是对满足同一规则的记录进行的加减等四则运算。
- 使用UNION(并集)、INTERSECT(交集)、EXCEPT(差集)等集合运算符来进行集合运算。
- 集合运算符可以去除重复行。
- 如果希望集合运算符保留重复行,就需要使用ALL选项
什么是集合运算
对满足同一规则的记录进行的加减等四则运算。
通过集合运算,可以得到两张表中记录的集合或者公共记录的集合,
又或者其中某张表中的记录的集合。
像这样用来进行集合运算的运算符称为集合运 算符。
表的加法——UNION
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));
BEGIN 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 对表进行加法运算
SELECT product_id, product_name FROM Product
UNION
SELECT product_id, product_name
FROM Product2;
集合运算的注意事项
1. 作为运算对象的记录的列数必须相同
-- 列数不一致时会发生错误
SELECT product_id, product_name
FROM Product UNION
SELECT product_id, product_name, sale_price FROM Product2;
2. 作为运算对象的记录中列的类型必须一致
-- 数据类型不一致时会发生错误
SELECT product_id, sale_price
FROM Product UNION
SELECT product_id, regist_date FROM Product2;
3. 可以使用任何SELECT语句,但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;
包含重复行的集合运算——ALL 选项
SELECT product_id, product_name FROM Product
UNION ALL
SELECT product_id, product_name
FROM Product2;
选取表中公共部分——INTERSECT
-- Oracle SQL Server DB2 PostgreSQL MySQL 不支持此语法
SELECT product_id, product_name FROM Product
INTERSECT
SELECT product_id, product_name
FROM Product2 ORDER BY product_id;
记录的减法——EXCEPT
-- SQL Server DB2 PostgreSQL MySQL不支持此语法 Oracle为MINUS
SELECT product_id, product_name FROM Product
EXCEPT
SELECT product_id, product_name
FROM Product2 ORDER BY product_id;
注意事项
-- 被减数和减数位置不同,得到的结果也不同
-- 从Product2的记录中除去Product中的记录 SELECT product_id, product_name
FROM Product2 EXCEPT
SELECT product_id, product_name FROM Product
ORDER BY product_id;
7-2 联结(以列为单位对表进行联结)
- 联结(JOIN)就是将其他表中的列添加过来,进行“添加列”的集合运算。 UNION 是以行(纵向)为单位进行操作,而联结则是以列(横向)为单位 进行的。
- 联结大体上分为内联结和外联结两种。首先请大家牢牢掌握这两种联结的 使用方法。
- 请大家一定要使用标准SQL的语法格式来写联结运算,对于那些过时的 或者特定 SQL 中的写法,了解一下即可,不建议使用。
什么是联结
将其他表中的 列添加过来,进行“添加列”的运算
内联结——INNER JOIN (内联结只能选取出同时存在于两张表中的数据)
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
FROM ShopProduct SP INNER JOIN Product P
ON SP.product_id = P.product_id;
内联结要点
- FROM 子句: 联结的表
- ON 子句: 联结条件(ON 必须书写在 FROM 和 WHERE 之间)
- SELECT 子句: 列需要按照“< 表的别名 >.< 列名 >”的格式进行书写
内联结和 WHERE 子句结合使用
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
FROM ShopProduct SP INNER JOIN Product P
ON SP.product_id = P.product_id
WHERE SP.shop_id = '000A';
外联结——OUTER JOIN (只要数据存在于某一张表当中,就能够读取 出来)
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
FROM ShopProduct SP RIGHT OUTER JOIN Product P
ON SP.product_id = P.product_id
外联结要点
- 选取出单张表中全部的信息
- 哪张表作为主表: 指定主表的关键字是 LEFT 和 RIGHT
- 外联结中使用 LEFT、RIGHT 来指定主表。使用二者所得到的结果完全相同
3 张以上的表的联结
-- 创建模拟数据
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));
-- 插入数据
BEGIN 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;
-- 对三张表进行外联
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';
交叉联结——CROSS JOIN (笛卡儿积)
-- 在实际业务中不会用到,但交叉联结是其他联结的基础
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name
FROM ShopProduct AS SP
CROSS JOIN
Product AS P;
联结的特定语法和过时语法
-- 使用过时语法的内联结(结果与代码 INNER JOIN 相同)
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';
不推荐大家使用,理由主要有以下三点。
第一,使用这样的语法无法马上判断出到底是内联结还是外联结(又或者是其他种类的联结)。
第二,由于联结条件都写在 WHERE 子句之中,因此无法在短时间内分辨出哪部分是联结条件,哪部分是用来选取记录的限制条件。
第三,我们不知道这样的语法到底还能使用多久。
八、SQL 高级处理
8-1 窗口函数
- 窗口函数可以进行排序、生成序列号等一般的聚合函数无法实现的高级操作。
- 理解PARTITION BY和ORDER BY这两个关键字的含义十分重要。
什么是窗口函数
窗口函数也称为 OLAP 函数 。
OLAP 是 OnLine Analytical Processing 的简称,意思是对数据库数据 进行实时分析处理。
例如,市场分析、创建财务报表、创建计划等日常性 商务工作。
窗口函数就是为了实现 OLAP 而添加的标准 SQL 功能
目前 MySQL 还不支持窗口函数
窗口函数的语法
< 窗口函数 > OVER ([PARTITION BY < 列清单 >]
ORDER BY < 排序用列清单 >)
窗口函数大体可以分为以下两种
1 能够作为窗口函数的聚合函数(SUM、AVG、COUNT、MAX、MIN)
2 RANK、DENSE_RANK、ROW_NUMBER 等专用窗口函数
语法的基本使用方法——使用 RANK 函数
-- 根据不同的商品种类,按照销售单价从低到高的顺序创建排序表
SELECT
product_name,
product_type,
sale_price,
RANK () OVER (PARTITION BY product_type ORDER BY sale_price) AS ranking
FROM Product;
无需指定PARTITION BY
-- 不指定 PARTITION BY
SELECT
product_name,
product_type,
sale_price,
RANK () OVER (ORDER BY sale_price) AS ranking
FROM Product;
专用窗口函数的种类
- RANK 函数 计算排序时,如果存在相同位次的记录,则会跳过之后的位次。
有 3 条记录排在第 1 位时:1 位、1 位、1 位、4 位......
- DENSE_RANK 函数 同样是计算排序,即使存在相同位次的记录,也不会跳过之后的位次
有 3 条记录排在第 1 位时:1 位、1 位、1 位、2 位......
- ROW_NUMBER 函数 赋予唯一的连续位次。
有 3 条记录排在第 1 位时:1 位、2 位、3 位、4 位......
比较 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) AS dense_ranking,
ROW_NUMBER () OVER (ORDER BY sale_price) AS row_num
FROM Product;
窗口函数的适用范围
语 法 上,除 了SELECT子 句, ORDER BY子句或者UPDATE语 句的 SET 子句中也可以使用。
但 因为几乎没有实际的业务示例, 所以开始的时候大家只要记得
“只能在 SELECT 子句中使用”就 可以了。
作为窗口函数使用的聚合函数
- 将 SUM 函数作为窗口函数使用
SELECT product_id, product_name, sale_price,
SUM (sale_price) OVER (ORDER BY product_id) AS current_sum
FROM Product;
2. 将 AVG 函数作为窗口函数使用
SELECT product_id, product_name, sale_price,
AVG (sale_price) OVER (ORDER BY product_id) AS current_avg
FROM Product;
计算移动平均
窗口函数就是将表以窗口为单位进行分割,并在其中进行排序的函数。
其实其中还包含在窗口中指定更加详细的汇总范围的备选功能,
该备选功能中的汇总范围称为 框架
-- 指定“最靠近的 3 行”作为汇总对象
SELECT product_id, product_name, sale_price,
AVG (sale_price) OVER (ORDER BY product_id
ROWS 2 PRECEDING) AS moving_avg
FROM Product;
指定框架(汇总范围)
ROWS 2 PRECEDING: 就是将框架指定为“截止到之前 2 行”,也就是将作为汇总对象的记录限定为如下的“最靠近的 3 行”
● 自身(当前记录)
● 之前1行的记录
● 之前2行的记录
将当前记录的前后行作为汇总对象
-- 将当前记录的前后行作为汇总对象
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
● 之前1行的记录
● 自身(当前记录)
● 之后1行的记录
两个ORDER BY
-- 无法保证如下 SELECT 语句的结果的排列顺序
-- Oracle SQL Server DB2 PostgreSQL
SELECT product_name, product_type, sale_price,
RANK () OVER (ORDER BY sale_price) AS ranking
FROM Product;
-- 在语句末尾使用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尚未支持这一功能。
同时计算出合计值
-- 使用GROUP BY无法得到合计行
-- 分别计算出合计行和汇总结果再通过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;
GROUPING 运算符包含以下 3 种
- ROLLUP
- CUBE
- GROUPING SETS
ROLLUP——同时得出合计和小计
-- Oracle SQL Server DB2 PostgreSQL
SELECT product_type, SUM(sale_price) AS sum_price FROM Product
GROUP BY ROLLUP(product_type);
-- MySQL
SELECT product_type, SUM(sale_price) AS sum_price FROM Product
GROUP BY product_type WITH 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);
GROUPING 函数——让 NULL 更加容易分辨
-- mysql 不支持
-- ROUPING 函数来判断 NULL
-- 该函数在其参数列的值为超级分组记录 所产生的 NULL 时返回 1,其他情况返回 0
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);
-- 在超级分组记录的键值中插入恰当的字符串
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);
-- CAST(regist_date AS VARCHAR(16))
-- 这是为了满足 CASE 表达式所有分支的返回值必须一致的条件。如果不这样的话,那么各个分支会分别返回日期类型和字符串类型的值,执行时就会发生语法错误
CUBE——用数据来搭积木
-- 使用 CUBE 取得全部组合的结果 MySQL 不支持
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);
GROUPING SETS——取得期望的积木
-- 使用GROUPING SETS取得部分组合的结果 MySQL 不支持
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);
十、 最后福利,干货来袭
附赠MySql中的几点操作小技巧
- TRUNCATE tableName ; 清除表数据,不影响标识列。
- mysql将查询结果转换成一行
方法一:
select id,group_concat(id order by id separator ",") as ids
from t_user
group by pwd
方法二:
SELECT
group_concat(user_list.id) AS ids
FROM
(SELECT id FROM t_user) AS user_list
- mysql数据库最大连接数查看与修改
1、查看最大数据库最大连接数
2、修改全局的数据库最大连接数(仅限本次,重启无效)show variables like '%max_connections%';
set global max_connections=200;
- 价格类数据保留精度
-- 设计数据库字段的时候使用数据类型decimal(18,2)参数含义:数据长度,数据精度;
select id,name, convert(price,decimal(18,2)) as price from product;
特别说明:本文所有内容都是来至 《SQL基础教程(第2版)》这本书.
完结!!