《SQL基础教程》阅读笔记6

新工具：视图
视图从SQL角度来看就是一张表
表存储实际数据，视图本身不存储数据，视图可以说是一张临时生成的表

• 视图的优点
  不用保存数据，节省空间
  可以将频繁使用的SELECT语句保存成视图，这样不用每次都重新书写了

创建视图

CREATE VIEW <view_name> (<view_col_name1>, <view_col_name2>,...) AS <SELECT语句>；

例：

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)

结果：

mysql> SELECT * FROM ProductSUm;
+--------------+-------------+
| product_type | cnt_product |
+--------------+-------------+
| 办公用品     |           2 |
| 厨房用具     |           4 |
| 衣服         |           2 |
+--------------+-------------+
3 rows in set (0.01 sec)

因为视图是一张临时存放的表，大部分表的操作它都可以

视图的限制：

1. 定义视图时候不能使用 ORDER BY 子句（指 在 SELECT 语句中）
2. 对视图的更新：

• 由于视图是从表派生得到的，要保证其一直和表保持一致，因此，对生成自己的表不能做的事情，对视图也不能做
  比如：ProductSum 是 group by 生成的，如果对其进行插入操作，那么对于原表没法处理，因此不可以插入

比较典型的不可以进行更新的：

1. SELECT子句中使用了DISTINCT
2. FROM子句中有多张表
3. 使用GROUP BY 子句
4. 使用HAVING 子句

删除视图

删除单列

DROP VIEW <view_name> (<view_col1>, <view_col2>,...);

全删除

DROP VIEW <view_name>;

子查询

一般来说创建一个视图，和查看视图是两个动作，但是它也可以嵌套，这就是子查询：
创建视图和查看视图：

mysql> CREATE VIEW ProductSUM (product_type, cnt_product) AS
    -> SELECT product_type, COUNT(*)
    -> FROM Product
    -> GROUP BY product_type;

子查询：

mysql> SELECT product_type, cnt_product
    -> FROM(SELECT product_type, COUNT(*) AS cnt_product
    -> FROM Product
    -> GROUP BY product_type) AS ProductSum;
+--------------+-------------+
| product_type | cnt_product |
+--------------+-------------+
| 办公用品     |           2 |
| 厨房用具     |           4 |
| 衣服         |           2 |
+--------------+-------------+
3 rows in set (0.00 sec)

这种语法是可以嵌套但是不容易读懂，不推荐

标量查询

标量子查询就是返回单一值的子查询
由于WHERE 子句中不能使用聚合函数，那么，想要查询销售单价高于平均销售单价的商品

mysql> SELECT product_id,product_name,sale_price
    -> FROM Product
    -> WHERE sale_price > (SELECT AVG(sale_price) FROM Product);
+------------+--------------+------------+
| product_id | product_name | sale_price |
+------------+--------------+------------+
| 0003       | 运动T恤      |       3000 |
| 0004       | 菜刀         |       3000 |
| 0005       | 高压锅       |       6800 |
+------------+--------------+------------+
3 rows in set (0.01 sec)

标量子查询的书写位置不局限于WHERE子句，通常任何可以使用单一值的地方都可以使用

关联子查询

比如我想要知道各种类型中超过它们这种类型东西平均价格的有哪些，通过标量子查询显然就是不行的，因为它只能返回单一数值，但我们想要得到的是一系列的值：
这个时候就需要关联子查询，指的是查询和子查询要有关联条件

mysql> 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);
+--------------+--------------+------------+
| product_type | product_name | sale_price |
+--------------+--------------+------------+
| 办公用品     | 打孔机       |        500 |
| 衣服         | 运动T恤      |       3000 |
| 厨房用具     | 菜刀         |       3000 |
| 厨房用具     | 高压锅       |       6800 |
+--------------+--------------+------------+
4 rows in set (0.00 sec)

注意这里的WHERE子句，将查询和子查询之间关联起来了，并且注意关联句在子查询中，因为P2的作用域只在子查询中