MySQL之视图、子查询和关联子查询_视图关联查询_柳小葱的博客

1.1创建视图的方法

CREATE VIEW 视图名称(<视图列名1><视图列名2><视图列名3>......)
<SELECT语句>
例如：创建一个视图 
CREATE VIEW productSum(product_type,cnt_product)
SELECT product_type,COUNT(*)
  FROM product
 GROUP BY product_type;
使用一个视图 
SELECT product_type,cnt_product
  FROM productSum;

 我们这里解释一下，我们首先通过创建了一个视图Productsum，然后通过SELECT语句获取视图中的数据，这样我们就不用了每次需要数据时都SELECT创建新的数据表，需要注意的是，原表product的数据产生变化后，视图数据也会随着变化，非常方便。
 之所以能够实现上述功能，是因为视图就是保存好的SELECT语句定义视图时可以使用任何SELECT语句，既可以使用WHERE,GROUP BY,HAVING,也可以通过SELECT*来指定全部列。 
1.2使用视图的查询
 
在FROM子句中使用视图的查询，通常有如下两个步骤： 
首先执行定义视图的SELECT语句。（创建视图）
根据得到的结果，再执行在FROM子句中使用视图的SELECT语句。（从视图中取数据）
 也就是说视图的查询通常需要执行2条以上的SELECT语句。这里没有用两条，而用了两条以上，是因为还可能出现以视图为基础创建视图的多重视图。例如：我们可以以productsun视图创建productsumjim视图。 
--基于productsum创建新的视图
CREATE VIEW productsumjim(product_type,cnt_product)
SELECT product_type,cnt_product
  FROM productsum
 WHERE product_type="办公用品";
--查看视图
SELECT product_type,cnt_product
  FROM productsumjim;
结果如下：
 
 虽然语法上没有错误，但是我们还是尽量少使用在视图上创建视图，这是因为对于多数DBMS来说，多重视图会降低SQL的性能。因此还是建议大家使用单一视图。 
1.3视图的限制 ——定义时不能使用ORDER BY
 
虽然我们前面说过定义视图时我们能使用任何SELECT语句，但是有一点例外就是ORDER BY，因此下面的语法是错误的。 
CREATE VIEW productsum(product_type,cnt_product)
SELECT product_type,COUNT(*)
  FROM product
 GROUP BY product_type
 OEDER BY product_type --这里是错误的，不能使用ORDER BY语句。
1.4视图的限制——对视图的更新
 
对视图的更新有着很严格的限制： 
SELECT子句中未使用DISTINCT
FROM子句中只有一张表
未使用GROUP BY子句
未使用HAVING子句
 接下来，我们就对视图进行更新。 
--创建一个视图
CREATE productjim(product_id,product_name,product_type,sale_price,purchase_price,regist_date)
SELECT *
  FROM product
 WHERE product_type="办公用品";
--向视图中添加数据行
INSERT INTO productjim VALUES('0009','铅笔','办公用品',95,10,'2009-11-30')
我们可以看见不仅在原数据表product中添加了数据，视图中也添加了数据。
  
1.5删除视图
 
DROP VIEW 视图名称(<视图列名1>，<视图列名2>,......)
DROP VIEW productsum;
删除成功。
  
2.子查询
 
我们先来说一下子查询和视图：子查询是将用来定义视图的SELECT语句直接用于FROM语句中。
 我们使用子查询来实现一个视图 
--在from子句中直接书写定义视图的SELECT语句
SELECT product_type,cnt_product
  FROM(SELECT product_type,COUNT(*) AS cnt_product
          FROM product
         GROUP BY product_type) AS productsum;
可以看出结果一模一样。但是有一个不同点：子查询是一张一次性视图，在数据库中并不会创建productsum,下次使用时需要重新定义。
 
 这里我们使用了一个两层的子查询，原则上来说，子查询的层数没有明确的限制。 
--一个三层的子查询
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.1 子查询的名称
 
原则上，子查询必须设定名称，并使用AS关键字进行名称的设定，该关键字有时候也可以省略。 
2.2 标量子查询
 
标量就是单一的意思，即是指必须而且只能返回1行1列的结果，也就是返回某一行某一列的值，例如“10”、“”办公用品”这样的单个数据。
 我们什么时候使用标量子查询呢？我们来看一个例子。
 “查询出销售单价高于平均销售单价的商品” 
--在where语句中不能使用聚合函数
SELECT product_id,product_name,sale_price
  FROM product
 WHERE sale_price > AVG(sale_price);--这里会出错因为where不能使用聚合函数
这样我们就需要创建标量子查询 
SELECT product_id, product_name, sale_price
  FROM Product
 WHERE sale_price > (SELECT AVG(sale_price)--使用标量子查询
                         FROM Product);
2.3 子查询的书写位置
 
标量子查询的书写位置并不仅仅局限于WHERE子句中，通常任何可以使用单一值位置都可以使用，也就是说，能够使用常数或者列名的地方，无论是SELECT语句，GROUP BY语句，HAVING语句还是ORDER BY语句几乎所有的地方都可以使用。
 例如在SELECT语句中使用标量子查询 
SELECT product_id,product_name,sale_price,(SELECT AVG(sale_price) FROM product) AS avg_price
 FROM product;

 在HAVING语句中使用标量子查询 
SELECT product_type,AVG(sale_price)
  FROM product
 GROUP BY product_type
HAVING AVG(sale_price)>(SELECT AVG(sale_price) FROM product);
这里我们是查询某一种类的产品平均销售价格大于所有产品的平均销售价格。
  
2.4标量子查询时的注意事项
 
子查询绝对不能返回多行结果，一旦出现多行，就不再是标量子查询，不能用= > <等需要单一值运算符中，也不能出现在SELECT等句子当中。 
3.关联子查询
 
介绍一下关联子查询： 
关联子查询会在细分的组内进行比较时使用。
关联子查询和GROUP BY子句一样，也可以对表中的数据进行切分。
关联子查询的结合条件如果未出现在子查询中就会发生错误。
 例如：我们查询各种商品种类中高于该种类的平均销售单价的商品。 
--发生错误的语句
SELECT product_id,product_name,sale_price
  FROM product
 WHERE sale_price > (SELECT AVG(sale_price) FROM product GROUP BY product_type);--这里会报错，因为不是唯一值
正确的方式应该是： 
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子句条件。该条件的意思就是在同一商品种类中对各商品的销售单价和平均单价进行比较。 
图书：《SQL基础教程》
                    前面的章节介绍了数据更新操作，今天介绍MySQL的复杂查询。复杂查询主要包括1.视图先介绍一下视图：从SQL的角度来看，视图和表是相同的，两者的区别在于表中存储的是实际的数据，而视图中保存的是SELECT语句（视图本身并不存储数据）。使用视图可以轻松完成跨多表查询数据等复杂操作。视图中不能包含ORDER BY和对视图的更新操作（INSERT、DELETE、UPDATE）1.1创建视图的方法CREATE VIEW 视图名称(&lt;视图列名1&gt;&lt;视图列名2&gt;&lt;视图列名
				（10）	使用INSERT INTO语句更新view_ avgamount视图，然后再次查询该视图以及查询相关基表检查执行结果。
insert into view_avgamount values('800','zhangsan','200')
select * from view_avgamount
1.	简述为什么要使用视图查看数据？视图的优点有哪些？
答：视图是虚拟的表，视图有如下优点：
为用户集中数据，简化数据库的查询，简化用户权限管理，方便数据的导出。
提高了重用性，简化了用户的操作，就像一个函数。也就是说，重复同一个操作时，只需调用视图，不用再重复写相同的select语句了。
视图本质上就是一条SELECT语句，所以当访问视图时，只能访问到所对应的SELECT语句中涉及到的列，对基表中的其它列起到安全和保密的作用，可以限制数据访问。
节省存储设备的容量。因为使用视图时并不会将数据保存到存储设备以及其他任何地方。而
简单的来说：存储过程就是在数据库中编写代码，目的是为了完成特定的任务或者功能的 SQL 的集合，它是一个
 语句集，经过编辑保存到数据库中，可以让指定的用户对其进行调用，提升效率。
即：是数据库中...
				Alibaba Java 技术图谱: https://developer.aliyun.com/article/878802?spm=a2c6h.12873581.0.dArticle878802.44867e04FDXkMY
原文地址：https://developer.aliyun.com/article/783124?spm=a2c6h.24874632.0.0.21e53ccdPwXu9S
**简介：**本文主要介绍什么是关联子查询以及如何将关联子查询改写为普通语义的sql查询。
				在 MySQL 中，视图（View）可以被看作是一种虚拟的表，它可以从其他一个或多个表中导出。视图并不在数据库中以存储数据的形式存在，只是一个预定义的查询，因此视图本身并不占用存储空间。在查询时，可以像查询表一样查询视图，这样可以方便地获取特定数据集。
视图可以用来隐藏复杂的 SQL 查询，简化查询操作，提高查询效率，同时也可以保护数据的安全性，防止意外的数据修改、删除和插入。
创建视图需要使用 CREATE VIEW 语句，例如：
```sql
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
其中，view_name 是视图的名称，column1、column2 等是需要在视图中显示的列名，table_name 是需要查询的表名，condition 是查询条件。
使用视图时，可以直接对其进行查询操作，例如：
```sql
SELECT * FROM view_name;
也可以使用视图作为其他查询语句的子查询，例如：
```sql
SELECT column1, column2, ...
FROM (SELECT * FROM view_name) AS sub_query
WHERE condition;
需要注意的是，视图是建立在其他表的基础之上的，因此当视图所依赖的表发生变化时，视图的查询结果也会相应地发生变化。此外，由于视图本身并不存储数据，因此在查询时可能会对性能产生一定的影响，需要根据具体情况进行权衡。