SQL 学习笔记
2018-06-09 本文已影响67人
Hsinwong
基础
SELECT
书写顺序
- SELECT 子句 → 2. FROM 子句 → 3. WHERE 子句 → 4. GROUP BY 子句 →
- HAVING 子句 → 6. ORDER BY 子句
执行顺序
FROM → INNER JOIN/LEFT OUTER JOIN/RIGHT OUTER JOIN/CROSS JOIN → WHERE → GROUP BY → HAVING → SELECT → UNION( ALL)/INTERSECT( ALL)/EXCEPT → ORDER BY
DELETE
DELETE 与 TRUNCATE
DELETE FROM <表名>; 和 TRUNCATE <表名>; 都可以清空表的所有记录，它们的不同之处（MySQL 5.7）：
- 后者执行效率更高
- 后者会重置自增值
- 前者属于 DML，可以回滚，可以激发触发器；后者属于 DDL，与事务无关，不会激发触发器
NULL 处理函数 COALESCE
SELECT 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 |
CASE 表达式
语法
CASE WHEN < 求值表达式 > THEN < 表达式 >
WHEN < 求值表达式 > THEN < 表达式 >
WHEN < 求值表达式 > THEN < 表达式 >
.
.
.
ELSE < 表达式 >
END
其中 <求值表达式> 就是类似“列=值”这样，返回值为真值（TRUE/FALSE/UNKNOWN）的表达式。我们也可以将其看作使用 =、!= 或者 LIKE、BETWEEN 等谓词编写出来的表达式。
<表达式> 则会返回一个值，作为 CASE 表达式的最终值。
CASE 表达式会从对最初的 WHEN 子句中的“<求值表达式>”进行求值开始执行。当返回值为 TRUE 时，中止执行，并返回 THEN 子句中的“<表达式>”。如果以上“<求值表达式>”均不为 TRUE，返回 ELSE 子句中的“<表达式>”。
用法示例
假设有如下一张表，名为 Product（DDL 见本文结尾）：
| 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 | NULL |
| 0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20 |
| 0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15 |
| 0006 | 叉子 | 厨房用具 | 500 | NULL |
2009-09-20 |
| 0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28 |
| 0008 | 圆珠笔 | 办公用品 | 100 | NULL |
2009-11-11 |
-- 对按照商品种类计算出的销售单价合计值进行行列转换
SELECT SUM(CASE WHEN product_type = '衣服' THEN sale_price
ELSE 0
END) AS sum_price_clothes,
SUM(CASE WHEN product_type = '厨房用具' THEN sale_price
ELSE 0
END) AS sum_price_kitchen,
SUM(CASE WHEN product_type = '办公用品' THEN sale_price
ELSE 0
END) AS sum_price_office
FROM Product;
执行结果为：
| sum_price_clothes | sum_price_kitchen | sum_price_office |
|---|---|---|
| 5000 | 11180 | 600 |
窗口函数
MySQL 5.7 还不支持窗口函数，本节使用 MariaDB 10.3.7 进行测试。
语法
<窗口函数> OVER ([PARTITION BY <列清单>] ORDER BY <列清单>)
其中，<窗口函数> 可以使用：
- 聚合函数：SUM、AVG、COUNT、MAX、MIN
- 专用窗口函数：RANK、DENSE_RANK、ROW_NUMBER 等
假设有如下一张表，名为 Product（DDL 见本文结尾）：
| 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 | NULL |
| 0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20 |
| 0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15 |
| 0006 | 叉子 | 厨房用具 | 500 | NULL |
2009-09-20 |
| 0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28 |
| 0008 | 圆珠笔 | 办公用品 | 100 | NULL |
2009-11-11 |
聚合函数
-- 累计平均值
SELECT product_id, product_name, sale_price,
AVG(sale_price) OVER (ORDER BY product_id) AS current_avg
FROM Product
ORDER BY product_id;
/* 结果为
product_id | product_name | sale_price | current_avg
0001 | T恤 | 1000 | 1000 // (1000) / 1
0002 | 打孔器 | 500 | 750 // (1000 + 500) / 2
0003 | 运动T恤 | 4000 | 1833.3333 // (1000 + 500 + 4000) / 3
0004 | 菜刀 | 3000 | 2125 // (1000 + 500 + 4000 + 3000) / 4
0005 | 高压锅 | 6800 | 3060 // ...
0006 | 叉子 | 500 | 2633.3333
0007 | 擦菜板 | 880 | 2382.8571
0008 | 圆珠笔 | 100 | 2097.5
*/
-- 滚动平均值（当前行和前 1 行）
SELECT product_id, product_name, sale_price,
AVG(sale_price) OVER (ORDER BY product_id ROWS 1 PRECEDING) AS moving_avg
FROM Product
ORDER BY product_id;
/* 结果为
product_id | product_name | sale_price | moving_avg
0001 | T恤 | 1000 | 1000 // (1000) / 1
0002 | 打孔器 | 500 | 750 // (1000 + 500) / 2
0003 | 运动T恤 | 4000 | 2250 // (500 + 4000) / 2
0004 | 菜刀 | 3000 | 3500 // (4000 + 3000) / 2
0005 | 高压锅 | 6800 | 4900 // ...
0006 | 叉子 | 500 | 3650
0007 | 擦菜板 | 880 | 690
0008 | 圆珠笔 | 100 | 490
*/
-- 滚动平均值（当前行、前 1 行和后 1 行）
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
ORDER BY product_id;
/* 结果为
product_id | product_name | sale_price | moving_avg
0001 | T恤 | 1000 | 750 // (1000 + 500) / 2
0002 | 打孔器 | 500 | 1833.3333 // (1000 + 500 + 4000) / 3
0003 | 运动T恤 | 4000 | 2500 // (500 + 4000 + 3000) / 3
0004 | 菜刀 | 3000 | 4600 // (4000 + 3000 + 6800) / 3
0005 | 高压锅 | 6800 | 3433.3333 // ...
0006 | 叉子 | 500 | 2726.6667
0007 | 擦菜板 | 880 | 493.3333
0008 | 圆珠笔 | 100 | 490
*/
由执行结果可以看出：
- 默认：聚合累计的数据
- ROWS n PRECEDING：聚合当前行和前 n 行的数据
- ROWS BETWEEN n PRECEDING AND m FOLLOWING：聚合当前行、前 n 行和后 m 行的数据
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
ORDER BY row_num;
执行结果为：
| product_name | product_type | sale_price | ranking | dense_ranking | row_num |
|---|---|---|---|---|---|
| 圆珠笔 | 办公用品 | 100 | 1 | 1 | 1 |
| 叉子 | 厨房用具 | 500 | 2 | 2 | 2 |
| 打孔器 | 办公用品 | 500 | 2 | 2 | 3 |
| 擦菜板 | 厨房用具 | 880 | 4 | 3 | 4 |
| T恤 | 衣服 | 1000 | 5 | 4 | 5 |
| 菜刀 | 厨房用具 | 3000 | 6 | 5 | 6 |
| 运动T恤 | 衣服 | 4000 | 7 | 6 | 7 |
| 高压锅 | 厨房用具 | 6800 | 8 | 7 | 8 |
由执行结果可以看出：
- RANK()：排序遇到相同值时，名次相同，但保留名次数目（擦菜板为 4）
- DENSE_RANK()：排序遇到相同值时，名次相同，不保留名次数目（擦菜板为 3）
- ROW_NUMBER()：排序遇到相同值时，名次也正常递增
事务
事务处理何时开始
- 每条 SQL 语句就是一个事务（自动提交模式）
MySQL 默认开启自动提交模式，要想查询自动提交模式是否开启：
-- 会话属性（当前会话生效）
SHOW SESSION VARIABLES LIKE 'autocommit';
-- 全局属性（当前 MySQL 实例生效，新会话会继承全局属性，但已建立的会话不受影响）
SHOW GLOBAL VARIABLES LIKE 'autocommit';
ACID 特性
DBMS 的事务具有原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）四种特性。通常将这四种特性的首字母结合起来，统称为 ACID 特性。
-
原子性（Atomicity）
原子性是指在事务结束时，其中所包含的更新处理要么全部执行，要么完全不执行，也就是要么占有一切要么一无所有。 -
一致性（Consistency）
一致性指的是事务中包含的处理要满足数据库提前设置的约束，如主键约束或者 NOT NULL 约束等。
一致性也称为完整性。 -
隔离性（Isolation）
隔离性指的是保证不同事务之间互不干扰的特性。 -
持久性（Durability）
持久性也可以称为耐久性，指的是在事务（不论是提交还是回滚）结束后，DBMS 能够保证该时间点的数据状态会被保存的特性。
笔试题
问答题
-
在使用聚合函数时，SELECT 子句中可以使用的元素种类：
- 常数
- 聚合函数
- GROUP BY 子句中指定的列名（也就是聚合键）
即把聚合键之外的列名书写在 SELECT 子句之中是不允许的。
只有 MySQL 支持在 SELECT 子句中使用聚合键之外的列名，其它的 DBMS 均不支持这样的语法。因此不建议使用。
-
在 GROUP BY 子句中是否可以使用 SELECT 子句中 AS 关键字指定的别名？
不可以。因为 SQL 在 DBMS 内部的执行顺序是：SELECT 子句在 GROUP BY 子句之后执行。在执行 GROUP BY 子句时， SELECT 子句中定义的别名，DBMS 还并不知道。
只有 PostgreSQL 和 MySQL 支持在 GROUP BY 中使用别名。但是这样的写法在其他 DBMS 中并不是通用的，因此不建议使用。
编程题
假设有如下一张表，名为 Product（DDL 见本文结尾）：
| 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 | NULL |
| 0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20 |
| 0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15 |
| 0006 | 叉子 | 厨房用具 | 500 | NULL |
2009-09-20 |
| 0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28 |
| 0008 | 圆珠笔 | 办公用品 | 100 | NULL |
2009-11-11 |
还有如下一张表，名为 ShopProduct（DDL 见本文结尾）：
| shop_id | shop_name | product_id | quantity |
|---|---|---|---|
| 000A | 东京 | 0001 | 30 |
| 000A | 东京 | 0002 | 50 |
| 000A | 东京 | 0003 | 15 |
| 000B | 名古屋 | 0002 | 30 |
| 000B | 名古屋 | 0003 | 120 |
| 000B | 名古屋 | 0004 | 20 |
| 000B | 名古屋 | 0006 | 10 |
| 000B | 名古屋 | 0007 | 40 |
| 000C | 大阪 | 0003 | 20 |
| 000C | 大阪 | 0004 | 50 |
| 000C | 大阪 | 0006 | 90 |
| 000C | 大阪 | 0007 | 70 |
| 000D | 福冈 | 0001 | 100 |
-- 计算商品种类的个数
SELECT COUNT(DISTINCT product_type)
FROM Product;
-- 结果为 3
-- 销售单价高于全部商品平均销售（2097.5）的商品
SELECT product_type, product_name, sale_price
FROM Product
WHERE sale_price > (SELECT AVG(sale_price)
FROM Product);
/* 结果为
product_type | product_name | sale_price
衣服 | 运动T恤 | 4000
厨房用具 | 菜刀 | 3000
厨房用具 | 高压锅 | 6800
*/
-- 各商品种类的平均销售单价
SELECT product_type, AVG(sale_price)
FROM Product
GROUP BY product_type;
/* 结果为
product_type | AVG(sale_price)
办公用品 | 300
厨房用具 | 2795
衣服 | 2500
*/
-- 销售单价高于商品种类平均销售销售单价的商品
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);
/* 结果为
product_type | product_name | sale_price
办公用品 | 打孔器 | 500
衣服 | 运动T恤 | 4000
厨房用具 | 菜刀 | 3000
厨房用具 | 高压锅 | 6800
*/
-- 000A 号店出售的商品名称和价格
SELECT product_name, sale_price
FROM Product AS P
JOIN ShopProduct AS SP
ON P.product_id = SP.product_id
AND SP.shop_id = '000A';
-- ------------- 多解分隔线 -------------
SELECT product_name, sale_price
FROM Product
WHERE product_id IN (SELECT product_id
FROM ShopProduct
WHERE shop_id = '000A');
-- ------------- 多解分隔线 -------------
SELECT product_name, sale_price
FROM Product AS P
WHERE EXISTS (SELECT *
FROM ShopProduct AS SP
WHERE P.product_id = SP.product_id
AND shop_id = '000A');
/* 结果为
product_name | sale_price
T恤 | 1000
打孔器 | 500
运动T恤 | 4000
*/
假设有如下一张表，名为 Skills（DDL 见本文结尾）：
| skill |
|---|
| Java |
| Oracle |
| UNIX |
还有如下一张表，名为 EmpSkills（DDL 见本文结尾）：
| emp | skill |
|---|---|
| 平井 | C++ |
| 平井 | Oracle |
| 平井 | Perl |
| 平井 | PHP |
| 平井 | UNIX |
| 渡来 | Oracle |
| 相田 | C# |
| 相田 | Java |
| 相田 | Oracle |
| 相田 | UNIX |
| 神崎 | Java |
| 神崎 | Oracle |
| 神崎 | UNIX |
| 若田部 | Perl |
-- 掌握 Skills 表中所有三个技术的员工名称
SELECT DISTINCT ES1.emp
FROM EmpSkills AS ES1
WHERE NOT EXISTS(SELECT S.*
FROM Skills AS S
WHERE NOT EXISTS(SELECT ES2.*
FROM EmpSkills AS ES2
WHERE S.skill = ES2.skill
AND ES2.emp = ES1.emp));
-- -------------------------- 多解分隔线 --------------------------
SELECT DISTINCT emp
FROM EmpSkills ES1
WHERE NOT EXISTS(SELECT skill
FROM Skills
EXCEPT
SELECT skill
FROM EmpSkills ES2
WHERE EP1.emp = ES2.emp);
/* 结果为
emp
相田
神崎
*/
附录
Product 表 DDL
-- MySQL 5.7
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));
START 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;
ShopProduct 表 DDL
-- MySQL 5.7
-- DDL：创建表
CREATE TABLE ShopProduct
(shop_id CHAR(4) NOT NULL,
shop_name VARCHAR(200) NOT NULL,
product_id CHAR(4) NOT NULL,
quantity INTEGER NOT NULL,
PRIMARY KEY (shop_id, product_id));
-- DML：插入数据
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0001', 30);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0002', 50);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0003', 15);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0002', 30);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0003', 120);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0004', 20);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0006', 10);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0007', 40);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0003', 20);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0004', 50);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0006', 90);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0007', 70);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000D', '福冈', '0001', 100);
COMMIT;
