SQL 的简单教程(Mac 下 PostgreSQL 的安装与使用)(3)_mac 连接poestgrelsql

SQL 的简单教程(Mac 下 PostgreSQL 的安装与使用)(2)

七、集合运算

7-1 表的加减法

• 集合运算就是对满足同一规则的记录进行的加减等四则运算。
• 使用UNION(并集)、INTERSECT(交集)、EXCEPT(差集)等集合运算符来进行集合运算。
• 集合运算符可以去除重复行。
• 如果希望集合运算符保留重复行，就需要使用ALL选项

什么是集合运算

对满足同一规则的记录进行的加减等四则运算。
通过集合运算，可以得到两张表中记录的集合或者公共记录的集合，
又或者其中某张表中的记录的集合。
像这样用来进行集合运算的运算符称为集合运 算符。
1
2
3
4

表的加法——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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

集合运算的注意事项

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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

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

SELECT product_id, product_name FROM Product
UNION ALL
SELECT product_id, product_name
FROM Product2;
1
2
3
4

选取表中公共部分——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;
1
2
3
4
5

记录的减法——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;
1
2
3
4
5

注意事项

-- 被减数和减数位置不同，得到的结果也不同
-- 从Product2的记录中除去Product中的记录 SELECT product_id, product_name
FROM Product2 EXCEPT
SELECT product_id, product_name FROM Product
ORDER BY product_id;
1
2
3
4
5

7-2 联结(以列为单位对表进行联结)

• 联结(JOIN)就是将其他表中的列添加过来，进行“添加列”的集合运算。 UNION 是以行(纵向)为单位进行操作，而联结则是以列(横向)为单位 进行的。
• 联结大体上分为内联结和外联结两种。首先请大家牢牢掌握这两种联结的 使用方法。
• 请大家一定要使用标准SQL的语法格式来写联结运算，对于那些过时的 或者特定 SQL 中的写法，了解一下即可，不建议使用。

什么是联结

将其他表中的 列添加过来，进行“添加列”的运算
1

内联结——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;
1
2
3

内联结要点

1. FROM 子句: 联结的表
2. ON 子句: 联结条件(ON 必须书写在 FROM 和 WHERE 之间)
3. 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';
1
2
3
4

外联结——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
1
2
3

外联结要点

1. 选取出单张表中全部的信息
2. 哪张表作为主表: 指定主表的关键字是 LEFT 和 RIGHT
3. 外联结中使用 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';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

交叉联结——CROSS JOIN (笛卡儿积)

-- 在实际业务中不会用到,但交叉联结是其他联结的基础

SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name 
FROM ShopProduct AS SP 
CROSS JOIN 
Product AS P;
1
2
3
4
5
6

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

-- 使用过时语法的内联结(结果与代码 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';
1
2
3
4
5
6

不推荐大家使用，理由主要有以下三点。

第一，使用这样的语法无法马上判断出到底是内联结还是外联结(又或者是其他种类的联结)。
第二，由于联结条件都写在 WHERE 子句之中，因此无法在短时间内分辨出哪部分是联结条件，哪部分是用来选取记录的限制条件。 
第三，我们不知道这样的语法到底还能使用多久。
1
2
3

八、SQL 高级处理

8-1 窗口函数

• 窗口函数可以进行排序、生成序列号等一般的聚合函数无法实现的高级操作。
• 理解PARTITION BY和ORDER BY这两个关键字的含义十分重要。

什么是窗口函数

窗口函数也称为 OLAP 函数 。

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

窗口函数就是为了实现 OLAP 而添加的标准 SQL 功能

目前 MySQL 还不支持窗口函数
1
2
3
4
5
6
7
8

窗口函数的语法

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

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

1 能够作为窗口函数的聚合函数(SUM、AVG、COUNT、MAX、MIN) 
2 RANK、DENSE_RANK、ROW_NUMBER 等专用窗口函数
1
2

语法的基本使用方法——使用 RANK 函数

-- 根据不同的商品种类，按照销售单价从低到高的顺序创建排序表
SELECT 
	product_name, 
	product_type, 
	sale_price, 
	RANK () OVER (PARTITION BY product_type ORDER BY sale_price) AS ranking
FROM Product;
1
2
3
4
5
6
7

无需指定PARTITION BY

-- 不指定 PARTITION BY
SELECT 
	product_name, 
	product_type, 
	sale_price,
	RANK () OVER (ORDER BY sale_price) AS ranking
FROM Product;
1
2
3
4
5
6
7

专用窗口函数的种类

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

	有 3 条记录排在第 1 位时:1 位、1 位、1 位、4 位......
1

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

有 3 条记录排在第 1 位时:1 位、1 位、1 位、2 位......
1

• ROW_NUMBER 函数 赋予唯一的连续位次。

有 3 条记录排在第 1 位时:1 位、2 位、3 位、4 位......
1

比较 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;
1
2
3
4
5

窗口函数的适用范围

语 法 上，除 了SELECT子 句， ORDER BY子句或者UPDATE语 句的 SET 子句中也可以使用。
但 因为几乎没有实际的业务示例， 所以开始的时候大家只要记得
“只能在 SELECT 子句中使用”就 可以了。
1
2
3

作为窗口函数使用的聚合函数

1. 将 SUM 函数作为窗口函数使用

SELECT product_id, product_name, sale_price,
SUM (sale_price) OVER (ORDER BY product_id) AS current_sum 
FROM Product;
1
2
3

2. 将 AVG 函数作为窗口函数使用

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

计算移动平均
窗口函数就是将表以窗口为单位进行分割，并在其中进行排序的函数。
其实其中还包含在窗口中指定更加详细的汇总范围的备选功能，
该备选功能中的汇总范围称为 框架

-- 指定“最靠近的 3 行”作为汇总对象
SELECT product_id, product_name, sale_price,
AVG (sale_price) OVER (ORDER BY product_id
ROWS 2 PRECEDING) AS moving_avg
FROM Product;
1
2
3
4
5

指定框架(汇总范围)

ROWS 2 PRECEDING: 就是将框架指定为“截止到之前 2 行”，也就是将作为汇总对象的记录限定为如下的“最靠近的 3 行”

● 自身(当前记录) 
● 之前1行的记录
● 之前2行的记录
1
2
3
4
5

将当前记录的前后行作为汇总对象

-- 将当前记录的前后行作为汇总对象
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;
1
2
3
4
5
6

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
● 之前1行的记录
● 自身(当前记录) 
● 之后1行的记录
1
2
3
4

两个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;
1
2
3
4
5

-- 在语句末尾使用ORDER BY子句对结果进行排序
SELECT product_name, product_type, sale_price,
RANK () OVER (ORDER BY sale_price) AS ranking
FROM Product
ORDER BY ranking;
1
2
3
4
5

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;
1
2
3
4
5
6
7

GROUPING 运算符包含以下 3 种

1. ROLLUP
2. CUBE
3. 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;
1
2
3
4
5
6
7

将“登记日期”添加到聚合键当中

-- 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);
1
2
3

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);
1
2
3
4
5
6
7

-- 在超级分组记录的键值中插入恰当的字符串
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 表达式所有分支的返回值必须一致的条件。如果不这样的话，那么各个分支会分别返回日期类型和字符串类型的值，执行时就会发生语法错误
1
2
3
4
5
6
7
8
9
10

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);
1
2
3
4
5
6
7

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);
1
2
3
4
5
6
7

十、 最后福利，干货来袭

附赠MySql中的几点操作小技巧

1. TRUNCATE tableName ； 清除表数据，不影响标识列。
2. mysql将查询结果转换成一行
   方法一:

select id,group_concat(id order by id separator ",") as ids
from t_user
group by pwd
1
2
3

方法二：

SELECT
group_concat(user_list.id) AS ids
FROM
(SELECT id FROM t_user) AS user_list
1
2
3
4

3. mysql数据库最大连接数查看与修改
   1、查看最大数据库最大连接数

   	show variables like '%max_connections%';
   1

   2、修改全局的数据库最大连接数（仅限本次，重启无效）

   	set global max_connections=200;
   1

4. 价格类数据保留精度

-- 设计数据库字段的时候使用数据类型decimal(18,2)参数含义：数据长度，数据精度；
select id,name, convert(price,decimal(18,2)) as price from product;
1
2

特别说明:本文所有内容都是来至 《SQL基础教程（第2版）》这本书.
完结!!