SQL进阶之一：复杂查询

熟悉SQL的CRUD后，是时候来提高SQL能力了。
本文重点是学习视图和子查询，这两者可以跟表一样进行使用，因此恰当地运用他们可以写出更灵活的SQL语句。

1. 视图

1.1 视图和表

• 视图 VIEW：保存SELECT语句的虚拟表。

Views are stored queries that when invoked produce a
result set. A view acts as a virtual table. 视图保存了查询语句，当调用视图时会产生一个结果集。视图充当了虚拟表的角色。
——来自MySQL官方文档Chapter24.5 Using Views

• 视图和表的区别

  • 表含有真实数据，这些数据存储了硬盘中
  • 视图真正保存的是SELECT语句，当调用视图时，就相当于执行该SELECT语句并创建一张临时表。

• 视图的优点：

  • 节省存储设备容量：视图保存的是SELECT语句而不用保存数据
  • 重用SELECT语句：将频繁使用的SELECT保存成视图，这样就不用每次重新书写了
  • 简化复杂的SQL操作：不用了解基本查询细节直接使用视图即可
  • 保护数据：可以授予用户访问表的特定部分的权限，而不是整个表的访问权限
  • 更改数据格式和表示：视图可返回与底层表的表示和格式不同的数据。

！注意：性能问题
每次调用视图时它实际上会执行查询操作，如果使用多个连接和过滤创建了复杂的视图或者嵌套了视图，性能可能会下降得很厉害。因此，在部署使用了大量视图的应用前，应该进行性能上的测试。

1.2 创建视图

CREATE
 [OR REPLACE]
 [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
 [DEFINER = user]
 [SQL SECURITY { DEFINER | INVOKER }]
 VIEW view_name [(column_list)]
 AS select_statement
 [WITH [CASCADED | LOCAL] CHECK OPTION]

创建测试表product

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, 50,  '2020-09-20');
INSERT INTO product VALUES('0002', '打孔器',  '办公用品', 1000, 32,  '2020-09-20');
INSERT INTO product VALUES('0003', '运动T恤', '衣服',     1000, 280, '2020-09-20');
INSERT INTO product VALUES('0004', '菜刀',    '厨房用具', 1000, 500, '2020-09-20');
INSERT INTO product VALUES('0005', '高压锅',  '厨房用具', 1000, 680, '2020-09-20');
INSERT INTO product VALUES('0006', '叉子',    '厨房用具', 1000, 5,   '2020-09-20');
INSERT INTO product VALUES('0007', '擦菜板',  '厨房用具', 1000, 88,  '2020-09-20');
INSERT INTO product VALUES('0008', '圆珠笔',  '衣服',     1000, 10,  '2020-09-20');
COMMIT;

创建视图

CREATE VIEW productsum(product_type, cnt_product)
AS 
SELECT product_type,COUNT(*)
FROM product
GROUP BY product_type;

1.3 查看视图

查看某个具体的视图

SELECT * FROM productsum;

查看某个数据库下的所有视图：
方法一：通过状态的COMMENT列来查找：

说明：Mysql5.1支持视图，视图被看作一种抽象表，因此显示视图状态的语句与显示表状态的语句相同，只是在COMMENT列中以view区分

SHOW TABLE STATUS WHERE COMMENT='VIEW';

方法二：通过系统表information_schema.tables查询

SELECT * FROM information_schema.tables 
WHERE table_schema='yourDatabaseName' AND table_type='VIEW';

1.4 删除视图

DROP VIEW view_name;

1.5 视图限制条件

1. 定义视图时不要使用ORDER BY子句
2. 更新视图需要满足几个条件

标准SQL中有这样的规定：如果定义视图的SELECT语句能够满足某些条件，那么这个视图就可以被更新，下面列举一些比较具有代表性的条件；

• SELECT子句中未使用DESINCT
• FROM子句中只有一张表
• 未使用GROUP BY子句
• 未使用HAVING子句

视图归根结底还是从表派生出来的，如果原表可以更新，那么视图中的数据也可以更新，反之亦然，视图中的数据可以更新，那么原表的数据也可以更新。如果视图发生了改变，而原表没有进行相应更新的话，就无法保证数据的一致性了。

创建一视图，这个视图可以满足更新的条件

CREATE VIEW productjim(product_id, product_name, product_type, sale_price, purchase_price, regist_date)
AS
SELECT * 
FROM product
WHERE product_type='办公用品';

在视图中插入一组数据

INSERT INTO productjim VALUES('0009','印章','办公用品',95,10,'2020-11-30');

查询该视图

SELECT * FROM productjim;

查询product原表，可以看到在原表中也插入成功了

1.6 好的视图编程习惯

1. 将频繁使用的SELECT语句做成视图
2. 尽量避免多重视图——即在某一视图的基础上创建视图

2. 子查询

2.1 子查询

• 子查询 Subquery 即嵌套在其他查询中的查询。

子查询也可以看做是一张一次性的视图

2.2 标量子查询

• 标量子查询：返回单一值（即一行一列）的子查询。
  由于标量子查询返回值是单一的，因此可以使用=、>、<这样的比较运算符中，这也正是标量子查询的优势所在。

假设我们需要在product表中查询这样的表：
“查询出购买高于平均购买价的商品”
我们一开始可能会考虑这样的SQL语句

SELECT *
FROM product
WHERE purchase_price > AVG(purchase_price);

很可惜，WHERE子句不能使用聚合函数，所以该语句会报错，这时我们可以使用子查询：

SELECT *
FROM product
WHERE purchase_price >( 
	SELECT AVG(purchase_price)
	FROM product
	);

• 标量子查询的特性决定了它的巨大优势，它可以用在任何使用常数或者列名的地方，无论是SELECT子句、GROUP BY子句、HAVING子句，还是ORDER BY子句，几乎所有的地方都可以使用。

2.3 关联子查询

关联子查询比较诡异，知乎上有一篇讲得很好的文章，在此贴上简单易懂教你学会SQL关联子查询