Task03：复杂一点的查询（一）
教程地址
https://github.com/datawhalechina/wonderful-sql
https://gitee.com/datawhalechina/wonderful-sql
1. 视图
1.1 什么是视图
视图是一个虚拟的表结构，其并不真实存在于数据库当中，可以视作是一个 select 语句的结构
1.2 视图与表的区别
其主要区别就是，「是否保存了实际的数据」，「视图不是表，视图是虚表，视图依赖于表」
1.3 为什么会存在视图
-
存储频繁使用的 select 语句来提高效率
-
挑选展示指定的数据，使得整个表的信息更明确，例如学生信息表就不需要其选课信息
-
限制访问部分数据，保护隐私数据，例如班级表不必展示宿舍号
-
降低冗余度，可以使得多个虚拟表都依赖于同一个真实表，而不是实际存储多个真实表
1.4 如何创建视图
CREATE VIEW <视图名称>(<列名1>,<列名2>,…) AS <SELECT语句>
视图可以根据真实表来创建，也可以根据其他视图来创建，即多级视图。
虽然这样的操作是被允许的，但是我们还是应该尽量避免，因其会降低 sql 的性能
我们需要注意的是，一般的数据库系统中，定义视图时不能使用 order by 语句
CREATE VIEW productsum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type
ORDER BY product_type;
像上面的这个例子，就是错误的，因为视图和表一样，数据行都是没有顺序的
但是在 mysql 当中，是存在例外情况的。mysql 的视图定义是允许使用 order by 的
但是如果我们从这样的表当中选取数据的时候使用了自己的 order by 数据
那么视图中定义的 order by 就将会被忽略掉
我们先来创建单表的视图，即按照上文的语法：
mysql> drop view productsum;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE VIEW productsum (product_type, cnt_product)
-> AS
-> SELECT product_type, COUNT(*)
-> FROM product
-> GROUP BY product_type ;
Query OK, 0 rows affected (0.02 sec)
默认导入的文件里面好像已经创建了这个视图，所以我们先删一下
除了单表之外，我们也可以有多表这样的情况，先建一个表：
CREATE TABLE shop_product
(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));
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0001', 30);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0002', 50);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0003', 15);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0002', 30);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0003', 120);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0004', 20);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0006', 10);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0007', 40);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0003', 20);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0004', 50);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0006', 90);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0007', 70);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000D', '福冈', '0001', 100);
我们将在这product表和shop_product表的基础上创建视图：
mysql> CREATE VIEW view_shop_product(product_type, sale_price, shop_name)
-> AS
-> SELECT product_type, sale_price, shop_name
-> FROM product,
-> shop_product
-> WHERE product.product_id = shop_product.product_id;
Query OK, 0 rows affected (0.01 sec)
对，我刚刚就想说这个问题，mysql 里面 show tables 直接把表和视图混杂显示
但是删表 drop 的是table，删视图 drop 的是 view，这两个还是很不一样的
所以说建视图的时候能在名字里面写明 view 是最好的
我们在这个视图上执行的查询操作如下：
mysql> select * from view_shop_product;
+--------------+------------+-----------+
| product_type | sale_price | shop_name |
+--------------+------------+-----------+
| 衣服 | 1000 | 东京 |
| 办公用品 | 500 | 东京 |
| 衣服 | 4000 | 东京 |
| 办公用品 | 500 | 名古屋 |
| 衣服 | 4000 | 名古屋 |
| 厨房用具 | 3000 | 名古屋 |
| 厨房用具 | 500 | 名古屋 |
| 厨房用具 | 880 | 名古屋 |
| 衣服 | 4000 | 大阪 |
| 厨房用具 | 3000 | 大阪 |
| 厨房用具 | 500 | 大阪 |
| 厨房用具 | 880 | 大阪 |
| 衣服 | 1000 | 福冈 |
+--------------+------------+-----------+
13 rows in set (0.00 sec)
mysql> SELECT sale_price, shop_name
-> FROM view_shop_product
-> WHERE product_type = '衣服';
+------------+-----------+
| sale_price | shop_name |
+------------+-----------+
| 1000 | 东京 |
| 4000 | 东京 |
| 4000 | 名古屋 |
| 4000 | 大阪 |
| 1000 | 福冈 |
+------------+-----------+
5 rows in set (0.00 sec)
1.5 如何修改视图结构
照常使用 alter 关键字，语法结构如下：
ALTER VIEW <视图名> AS <SELECT语句>
实践如下：
mysql> ALTER VIEW productsum
-> AS
-> SELECT product_type, sale_price
-> FROM product
-> WHERE regist_date > '2009-09-11';
Query OK, 0 rows affected (0.02 sec)
1.6 如何更新视图内容
这里的更新修改是一个危险的操作，毕竟视图只是一个虚拟的表，其更新可能会引起原真实表的一些错误
例如，假如视图包含了以下结构，那很显然是不能够被更改的：
聚合函数 SUM()、MIN()、MAX()、COUNT() 等
DISTINCT 关键字。
GROUP BY 子句。
HAVING 子句。
UNION 或 UNION ALL 运算符。
FROM 子句中包含多个表。
我感觉此时的视图更像是一种对结果的展示，我们不太可能直接去修改结果，进而修改原始数据
而且视图的数据可能也是有限的，其修改并不是针对全体，但确确实实修改了原表的部分数据：
mysql> select * from productsum;
+--------------+------------+
| product_type | sale_price |
+--------------+------------+
| 衣服 | 1000 |
| 厨房用具 | 3000 |
| 厨房用具 | 500 |
| 办公用品 | 100 |
+--------------+------------+
4 rows in set (0.00 sec)
mysql> UPDATE productsum
-> SET sale_price = '5000'
-> WHERE product_type = '办公用品';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from productsum;
+--------------+------------+
| product_type | sale_price |
+--------------+------------+
| 衣服 | 1000 |
| 厨房用具 | 3000 |
| 厨房用具 | 500 |
| 办公用品 | 5000 |
+--------------+------------+
4 rows in set (0.00 sec)
mysql> 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 | 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 | 圆珠笔 | 办公用品 | 5000 | NULL | 2009-11-11 |
+------------+--------------+--------------+------------+----------------+-------------+
8 rows in set (0.00 sec)
mysql>
我们可以看到透过视图进行修改，只改出来了圆珠笔的售价，同为办公用品，打孔器的售价没有变化
1.7 如何删除视图
这就是我上面提到的，与删除表不同的部分，语法如下：
DROP VIEW productSum;
2. 子查询
2.1 什么是子查询
子查询其实也就是嵌套，一个 select 套另外一个 select，例如：
SELECT stu_name
FROM (
SELECT stu_name, COUNT(*) AS stu_cnt
FROM students_info
GROUP BY stu_age) AS studentSum;
2.2 子查询和视图的关系
子查询可以看作是一次性的视图(?)，大概。总之就是子查询执行完毕之后就没有了，但是视图却可以多次使用。
2.3 嵌套子查询
反正子查询已经是嵌套了，那我们多叠几层也是可以的吧，例如：
SELECT product_type, cnt_product
FROM (SELECT *
FROM (SELECT product_type,
COUNT(*) AS cnt_product
FROM product
GROUP BY product_type) AS productsum
WHERE cnt_product = 4) AS productsum2;
当然需要注意的是，这个东西毕竟和视图差不多，叠的多了会变得臃肿，而且难以阅读和理解
2.4 标量子查询
标量子查询的意思就是，要返回某一个具体的值，而不是一行记录或者一张表
2.5 标量子查询有什么用
那当然是，作为筛选条件，例如：
SELECT product_id, product_name, sale_price
FROM product
WHERE sale_price > (SELECT AVG(sale_price) FROM product);
我们先查出了商品的均价，然后以此为条件过滤表，再选出来 id，name，和 price
所以这段代码的目的就是找出售价高于均价的商品的相关信息
反正标量子查询得到的是一个值，那就意味着可以在 SELECT，GROUP BY，HAVING 子句，ORDER BY 等地方使用
例如
SELECT product_id,
product_name,
sale_price,
(SELECT AVG(sale_price)
FROM product) AS avg_price
FROM product;
所以说，这段代码就是加了一列 avg_price，然后其代表商品的均值
mysql> SELECT product_id,
-> product_name,
-> sale_price,
-> (SELECT AVG(sale_price)
-> FROM product) AS avg_price
-> FROM product;
+------------+--------------+------------+-----------+
| product_id | product_name | sale_price | avg_price |
+------------+--------------+------------+-----------+
| 0001 | T恤 | 1000 | 2710.0000 |
| 0002 | 打孔器 | 500 | 2710.0000 |
| 0003 | 运动T恤 | 4000 | 2710.0000 |
| 0004 | 菜刀 | 3000 | 2710.0000 |
| 0005 | 高压锅 | 6800 | 2710.0000 |
| 0006 | 叉子 | 500 | 2710.0000 |
| 0007 | 擦菜板 | 880 | 2710.0000 |
| 0008 | 圆珠笔 | 5000 | 2710.0000 |
+------------+--------------+------------+-----------+
8 rows in set (0.01 sec)
嘛，果然，就是这样
2.6 关联子查询
我的理解，就是联表查询，但是不同于 join 那种，例如：
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);
大概就是先按照商品的种类分个组，然后对每个组的售价取一个均值
然后选出售价大于自身种类均价的商品记录，再展示其信息
其中的关联在于子查询求得的均价，是通过商品种类与外面的查询连接起来的
其语句执行的顺序大概是：
-
执行不带 where 的主查询，主要是选出用于连接的列，例如上面的 product_type
-
根据得到的这个连接关键词，执行子查询进行匹配，得到结果
-
再返回到主查询当中，执行 where 语句，用于过滤
A. 练习题
A.1
创建出满足下述三个条件的视图（视图名称为 ViewPractice5_1）。使用 product（商品）表作为参照表，假设表中包含初始状态的 8 行数据。
条件 1：销售单价大于等于 1000 日元。
条件 2：登记日期是 2009 年 9 月 20 日。
条件 3：包含商品名称、销售单价和登记日期三列。
对该视图执行 SELECT 语句的结果如下所示。
mysql> CREATE VIEW ViewPractice5_1 (product_name,sale_price,regist_date)
-> AS
-> SELECT product_name,sale_price,regist_date
-> FROM product
-> WHERE (sale_price>=1000) AND (regist_date='2009-09-20')
-> ;
Query OK, 0 rows affected (0.02 sec)
mysql> SELECT * FROM ViewPractice5_1;
+--------------+------------+-------------+
| product_name | sale_price | regist_date |
+--------------+------------+-------------+
| T恤 | 1000 | 2009-09-20 |
| 菜刀 | 3000 | 2009-09-20 |
+--------------+------------+-------------+
2 rows in set (0.00 sec)
mysql>
简单用个 where 就好
A.2
向习题一中创建的视图 ViewPractice5_1 中插入如下数据，会得到什么样的结果？为什么？
INSERT INTO ViewPractice5_1 VALUES (’ 刀子 ', 300, ‘2009-11-02’);
会出错的吧，这没有主键啊，主键不允许为空的
mysql> INSERT INTO ViewPractice5_1 VALUES (' 刀子 ', 300, '2009-11-02');
ERROR 1423 (HY000): Field of view 'shop.ViewPractice5_1' underlying table doesn't have a default value
mysql>
噢噢，返回值给的提示意思是没有设置缺省值，那也就是说，如果要设置了的话，默认是用缺省值的
嗯，这个我好像确实见到过，之前在 fun-rec 的时候，那个主键 id 的缺省值就是自增的
A.3
mysql> SELECT product_id,
-> product_name,
-> product_type,
-> sale_price,
-> (SELECT AVG(sale_price)
-> FROM product) AS sale_price_avg
-> FROM product;
+------------+--------------+--------------+------------+----------------+
| product_id | product_name | product_type | sale_price | sale_price_avg |
+------------+--------------+--------------+------------+----------------+
| 0001 | T恤 | 衣服 | 1000 | 2710.0000 |
| 0002 | 打孔器 | 办公用品 | 500 | 2710.0000 |
| 0003 | 运动T恤 | 衣服 | 4000 | 2710.0000 |
| 0004 | 菜刀 | 厨房用具 | 3000 | 2710.0000 |
| 0005 | 高压锅 | 厨房用具 | 6800 | 2710.0000 |
| 0006 | 叉子 | 厨房用具 | 500 | 2710.0000 |
| 0007 | 擦菜板 | 厨房用具 | 880 | 2710.0000 |
| 0008 | 圆珠笔 | 办公用品 | 5000 | 2710.0000 |
+------------+--------------+--------------+------------+----------------+
8 rows in set (0.00 sec)
mysql>
啊，就是上面 2.5 节，标量子查询的应用，多增加了一列值
不过我这个结果和体重不一样，主要是之前把圆珠笔的售价改了
A.4
mysql> CREATE VIEW ViewPractice5_2 (
-> product_id,
-> product_name,
-> product_type,
-> sale_price,
-> sale_price_avg
-> )
-> 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 sale_price_avg
-> FROM product AS p1 ;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from ViewPractice5_2;
+------------+--------------+--------------+------------+----------------+
| product_id | product_name | product_type | sale_price | sale_price_avg |
+------------+--------------+--------------+------------+----------------+
| 0001 | T恤 | 衣服 | 1000 | 2500.0000 |
| 0002 | 打孔器 | 办公用品 | 500 | 2750.0000 |
| 0003 | 运动T恤 | 衣服 | 4000 | 2500.0000 |
| 0004 | 菜刀 | 厨房用具 | 3000 | 2795.0000 |
| 0005 | 高压锅 | 厨房用具 | 6800 | 2795.0000 |
| 0006 | 叉子 | 厨房用具 | 500 | 2795.0000 |
| 0007 | 擦菜板 | 厨房用具 | 880 | 2795.0000 |
| 0008 | 圆珠笔 | 办公用品 | 5000 | 2750.0000 |
+------------+--------------+--------------+------------+----------------+
8 rows in set (0.00 sec)
mysql>
一个简单的关联子查询，位置放在 select 部分就好
那比较神奇的还是执行顺序问题，比较这里不是 where
感觉大概还是，现在执行主查询中，与子查询无关的部分
或者说先得到关联的关键字，然后再去执行子查询
最后再把两个结果连接起来
与教程不一致的地方是因为，圆珠笔的售价被改过了
