SQL复杂查询

目录

视图

视图和表

视图的优点

创建视图的方法

 使用视图查询

视图的限制1——定义视图不能使用ORDER BY子句

视图的限制2——对视图进行更新

删除视图

子查询

子查询和视图

 增加子查询的层数

标量子查询

标量子查询的书写位置

 使用标量子查询时注意的事项

关联子查询

---

视图

视图和表

表中的数据时存储在计算机的存储设备上的，

视图并不会将数据存储到存储设置中，而且也不会保存到将数据保存到其他任何地方。实际上视图就是保存的是SELECT语句。

视图的优点

• 第一：视图无需保存数据，因此可以节约存储设备的容量。
• 第二：可以将频繁使用的SELECT语句白村成视图，这样就不用每次都重新书写。

创建视图的方法

创建视图需要使用CREATE VIEW语句。

语法：

CREATE VIEW 视图名称(<视图列名1>,<视图列名2>,<视图列名3>,...)
AS
<SELECT语句>

SELECT语句需要书写在关键字AS之后，SELECT语句中的列排列顺序和视图中列排列顺序相同。

例如：创建一个ShohinSum的视图

CREATE VIEW ShohinSum(Shohin_bunrui,cnt_shohin)
AS
SELECT Shohin_bunrui,COUNT(*)
	FROM Shohin
	GROUP BY shohin_bunrui;

查询视图：

SELECT * 
FROM ShohinSum;

创建视图的第二行的AS是不可以省略的，这里的AS和别名的AS是不一样的。

实际上视图就是保存好的SELECT语句。

 使用视图查询

在FROM子句中使用视图查询，通常有两个步骤：

• 1.首先执行定义视图的SELECT语句。
• 2.根据得到的结果，再执行FROM子句中使用视图的SELECT语句。

例如：在视图ShohinSum基础上再创建视图ShohinSumJim

CREATE VIEW ShohinSumJim(shohin_bunrui,cnt_shohin)
AS
SELECT shohin_bunrui,cnt_shohin
FROM ShohinSum
WHERE shohin_bunrui = 'stationery';

查询ShohinSumJim

SELECT * 
FROM ShohinSumJim

 虽然语法上没有错，但是我们还是应该尽量避免再视图基础上创建视图，这是因为对多数DBMS来说，多重视图会降低SQL的性能。

视图的限制1——定义视图不能使用ORDER BY子句

因为视图和表一样，数据行都是没有顺序的。

视图的限制2——对视图进行更新

如果定义视图的SELECT语句能够满足某些条件，那么这个视图就可以被更新。

1. SELECT子句中未使用DISTINCT
2. FROM子句中只有一张表
3. 未使用GROUP BY子句
4. 未使用HAVING子句

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

删除视图

语法：

DROP VIEW 视图名称

例如：删除视图ShohinSumJim

DROP VIEW ShohinSumJim

如果想要删除以视图为基础创建出来的多重视图的话，由于存在关联的视图。可以使用

DROP VIEW 视图名称 CASCDE;

子查询

子查询和视图

子查询的特点概括起来就是一张一次性视图。

视图不是保存数据的，而是通过保存读取数据的SELECT语句的方法为用户提供便利的工具。子查询就是将用来定义视图的SELECT语句直接用与FROM子句中。

子查询：

SELECT shohin_bunrui, cnt_shohin
FROM
	(SELECT shohin_bunrui, COUNT(*) AS cnt_shohin
	FROM Shohin
	GROUP BY shohin_bunrui) 
AS ShohinSUM;

子查询就是将用来定义视图的SELECT语句直接用于FROM子句中，虽然“AS ShohinSUm”就是子查询的名称，但是由于该名称时一次性的，因此不会像视图那样保存再存储介质之中，而是再SELECT语句执行之后就消失了。

 增加子查询的层数

由于子查询的层数原则上是没有上限的，因此可以像子查询的FROM子句中还可以继续使用子查询等等。

随着子查询的嵌套层数的增多，SQL语句会变得越来越难读懂，性能也会越来越差。因此建议尽量避免使用多层嵌套的子查询。

标量子查询

标量子查询有一个特殊的限制就是必须而且只能返回一行一列的结果。

标量子查询的书写位置

标量子查询的书写位置不仅仅可以使用再WHERE子句中，通常任何可以使用单一值的位置都可以使用，也就是说能够使用常数或者列名的地方，无论时SELECT子句，GROUP BY子句，HAVING子句还是ORDER BY子句，几乎所有的地方都可以使用。

例如：选取hanbai_tanka高于全部的平均值

SELECT shohin_id ,shohin_mei,hanbai_tanka
FROM Shohin
WHERE hanbai_tanka > (SELECT AVG(hanbai_tanka)FROM Shohin);

 使用标量子查询时注意的事项

那就是该子查询绝对不能返回多行结果。也就是说如果子查询返回了多行结果，那么它就不是标量子查询了，而仅仅是一个普通子查询。

关联子查询

• 关联子查询会在细分的组内进行比较时使用。
• 关联子查询个GROUP BY子句一样，也可以对表中的数据进行切分。
• 关联子查询的结合条件如果未出现在子查询之中就会发生错误。