SQL-复杂查询

1. 视图

1.1 视图和表

从SQL角度看，视图就是一张表，两者的区别在于是否保存了实际的数据
a) 创建表时，会通过insert语句将数据保存到数据库中，而数据库中数据保存到存储设备
b) 使用视图时，并不会将数据保存到任何地方，实际上视图保存的时select语句，从视图读取数据时，视图会在内部执行该select语句，并创建出一张临时表。
表：保存实际数据
视图：保存select语句。优点：①节省内存；②可以将频繁使用的select语句保存成视图，不用每次重写。

1.2 创建和使用视图

a)创建视图：
create view view_name (col1, col2 ,…) as select … select后面列的排序与定义视图的列相同
例子：

create view ProductSum (product_type,cnt_product) 
as
select product_type, count(*)
from Product
group by product_type;

as 后面是视图定义的主体
b) 使用视图：

select product_type,cnt_product
from
ProductSum ;

执行from这一步会有2个步骤：①创建视图 ②使用试图，根据结果再执行select子句
还可以以此视图为基础再建视图，但多重视图会降低SQL性能。

1.3 视图的限制

不能用order by， 因为视图和表一样，数据行都没有顺序

• 对视图进行更新
  SQL规定：如果定义视图的select 语句能够满足某些条件，那么这个视图就可以被更新：
  ① select 中未使用distinct
  ② from 子句中只有一个表
  ③ 未使用 group by
  ④ 未使用 having

1.4 删除视图

drop view view_name;

2. 子查询

2.1 子查询视图

子查询与视图: 子查询就是一次性的视图（select 语句)，子查询在select语句执行完毕之后就会消失。

select product_type ,cnt_product --后执行外层
from( -- 先执行内层
	select product_type, count(*) as cnt_product
	from Product
	group by product_type
	) as ProductSum --子查询必须设定alias

2.2 标量子查询

标量子查询：返回单一值的子查询，必须而且只能返回表中某一行的某一列的值。

select * from product where sale_price > AVG(sale_price)
--这是错误的，where 子句中不能使用聚合函数

where 子句中不能使用聚合函数，因此使用标量子查询

select * from product where sale_price > (select AVG(sale_price) from Product) 

2.3 关联子查询

select * from Product as P1
where sale_price > (select AVG(sale_price)
					from Product as P2
					where P1.type = P2.type) --关联条件			

3. 窗口函数

窗口函数：记录集合，在满足某种条件的记录集合上执行的特殊函数。窗口函数的执行顺序（逻辑上）在from,join,where, group by, having 之后。
语法：

win_func_name(expression) 
over(partition by <expression> 
	 order by <expression>
	 frame_unit frame_start|frame_between
	 )

frame_unit有两种取值：ROWS(由开始和结束位置的行决定)，RANGE(frame由某个值区间的行确定)
frame_definition:如果只指定了frame开始的位置，default结束位置为当前行。
frame_start 取值有3种：
① UNBOUNDED PRECEDING 区间第一行
② N PRECEDING 当前行之前的N行（N可为数字/表达式）
③ CURRENT ROW 当前行
frame_between 取值如下：
① frame_start的取值
② UNBOUNDED FOLLOWING 最后一行
③ N FOLLOWING 当前行之后的N行

• 函数对比 row_number(), rank() , dense_rank()

1. select row_number() over(order by amount desc) as rownum_1
2. select rank() over(order by amount desc) as rownum_2
3. select dense_rank() over(order by amount desc) as rownum_3

结果

id	amount	row_num1	row_num2	row_num3
1	800		1			1			1
2	800		2			3			2
3	600		3			3			2

dense_rank 返回的排序是连续的

• 分布函数 percent_rank() / cume_dist()
  percent_rank() =(rank-1)/(rows-1),rank 为rank()函数产生的序号，rows为当前窗口记录总行数。分子分母若有重复的行，取第一次出现
  cume_dist()=rank/rows， 分组内小于当前rank值的行数除以分组内总行数。有重复值取最后一行的位置

4. 变量

4.1 变量的定义

用set或select直接赋值，变量名以@开头，可以在会话的任何地方声明，作用域是整个会话，称为用户变量。

select @var:=1
set @var =1
set @var:=1

以declare关键字声明变量，只能在存储过程中使用，成为存储过程变量

declare var1 int defaul 0

主要用在存储过程中，或是给存储传参数
区别：调用存储过程时，declare变量会被初始化为null
而会话变量@不会再被初始化