sql——复杂查询

视图

• 视图 vs 表

优点：

1. 无需保存数据，节省容量
2. 创建好视图之后，只需在 SELECT 语句中进行调用，无需重新书写
3. 视图中的数据会随原表的变化自动更新
   

视图的创建

create view 视图名称(视图列名1,视图列名2,...)
AS 
select语句      ---- select语句中列的排列顺序和视图中的一致

例：
Products表

创建商品种类汇总视图

create view Product_Sum (商品种类,个数合计)
as 
select 商品种类,count(*)
	from Products
group by 商品种类

视图的查询——from子句

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

create view Product_Sum (商品种类,个数合计)
as 
select 商品种类,count(*)
	from Product_Sum1
where 商品种类='办公用品'

视图的限制

1. 定义视图时不可使用order by 语句。
2. 视图和表需要同时进行更新，因此通过汇总得到的视图无法进行更新。

INSERT INTO Product_Sum VALUES ('电器制品', 5);   ----报错。原因为视图Produ，ct_Sum是通过group by子句对原表进行汇总得到的，而原表中虽然可以添加商品种类为电器制品的5行数据，但其他内容均布清楚

删除视图

drop view 视图名称(视图列名1,视图列名2, ……)

子查询——一次性视图

子查询在SELECT语句执行完毕之后就会消失。

视图查询

----- 创建视图
create view Product_Sum (商品种类,个数合计)
as 
select 商品种类,count(*)
	from Products
 group by 商品种类
 
----确认创建好的视图
select 商品种类,个数合计
	from ProductSum

子查询（一次性视图）

执行顺序为先执行内层select语句，再执行外层select语句。

select 商品种类,个数合计
from(
	select 商品种类,count(*) as 个数合计 
		from Products
 		group by 商品种类
 	) as Product_Sum

标量子查询——返回单一值的子查询（某一单元格的值）

Q：查询销售单价>平均销售单价的商品?
直接使用select语句会报错

这时可以用标量子查询

select 商品id,商品名称,销售单价
from Products
where 销售单价>(select Avg(销售单价) from Products);   --------括号里是一个select语句，程序执行时先执行内层select语句计算均价，再执行外层语句进行销售单价和均价的比较

标量子查询可以被用在=,<,>后面使用、可以在几乎所有地方使用
标量子查询不可返回多行结果，否则系统将把它看做一个普通子查询

关联子查询

——弥补了标量子查询的不足，可在细分的组内进行比较。
接上例，若想得到每个商品种类中高于此种类平均销售价格的商品，直接用标量子查询出现错误

select 商品id,商品名称,销售单价
from Products
where 销售单价>(select Avg(销售单价) 
				from Products
			   group by 商品种类) as 平均价格;

由于select子句返回结果不止一行，该程序报错。
利用关联子查询实现上述操作：
注意：要联条件一定要写在子查询内

select 商品id,商品名称,销售单价
from Products as P1
where 销售单价>(select Avg(销售单价) 
				from Products as P2    ----P1,P2为关联名称
				where P1.商品种类=P2.商品种类  ---关联条件
			   group by 商品种类);