2.2 MySQL高级应用

1. 窗口函数

1.1 定义

窗口函数，也就是在满足某种条件的记录集合上执行的特殊函数。

对于每条记录都要在此窗口内执行函数，窗口大小都是固定的，这种属于静态窗口（只指定了partition by）；
不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。

函数名（[expr]） over(partition by <要分列的组> order by <要排序的列> rows between <数据范围>)

over是关键字，用来指定函数执行的窗口范围，包含三个分析子句：分组(partition by)子句，排序(order by)子句，窗口(rows)子句

rows between 2 preceding and current row # 取当前行和前面两行
rows between unbounded preceding and current row # 包括本行和之前所有的行
rows between current row and unbounded following # 包括本行和之后所有的行
rows between 3 preceding and current row # 包括本行和前面三行
rows between 3 preceding and 1 following # 从前面三行和下面一行，总共五行
# 当order by后面缺少窗口从句条件，窗口规范默认是rows between unboundedpreceding and current row.
# 当order by和窗口从句都缺失, 窗口规范默认是 rows between unbounded precedingand unbounded following

1.2 窗口函数的应用

• 专有窗口函数
  rank()
  dense_rank()
  row_number()
• 聚合类窗口函数：普通场景下，聚合函数往往和group by一起使用，但是窗口环境下，聚合函数也可以应用进来，那么此时它们就被称之为聚合类窗口函数，属于窗口函数的一种
  sum()
  count()
  avg()
  max()
  min()

窗口函数和普通场景下的聚合函数区别

• 普通场景下的聚合函数是将多条记录聚合为一条（多到一）；窗口函数是每条记录都会执行，有几条记录执行完还是几条（多到多）
• 分组(partition by)：记录按照字段进行分组，窗口函数在不同的分组上分别执行。
• 排序(order by)：按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号，可以和partition子句配合使用，也可以单独使用。如果没有partition子句，数据范围则是整个表的数据行。
• 窗口(rows)：就是进行函数分析时要处理的数据范围，属于当前分区的一个子集，通常用来作为滑动窗口使用。比如要根据每个订单动态计算包括本订单和按时间顺序前后两个订单的移动平均支付金额，则可以设置rows子句来创建滑动窗口(rows)。

1.2.1 累计计算函数

• 累计求和，sum() over()
• 移动平均，avg() over()
• 最大/最小，max()/min() over()

1.2.2 排序函数

• row_number() over(……)
• rank() over(……)
• dense_rank() over(……)

注意:row_number()、rank() 和dense_rank()紧邻的括号内是不加任何字段名称的

1. row_number：它会为查询出来的每一行记录生成一个序号，依次排序且不会重复。
2. rank&dense_rank:如果使用rank函数来生成序号，over子句中排序字段值相同的序号是一样的，后面字段值不相同的序号将跳过相同的排名号排下一个，也就是相关行之前的排名数加一。
3. dense_rank函数在生成序号时是连续的，而rank函数生成的序号有可能不连续。
4. dense_rank函数 出现相同排名时，将不跳过相同排名号，rank值紧接上一次的rank值。
5. 在各个分组内，rank()是跳跃排序，有两个第一名时接下来就是第三名，dense_rank()是连续排序，有两个第一名时仍然跟着第二名。

• ntile(n) over(……)

n:切分的片数
A:分组的字段名称
B:排序的字段名称

ntile(n)，用于将分组数据按照顺序切分成n片，返回当前切片值，ntile不支持rows between

1.2.3 偏移分析函数

Lag和Lead函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为独立的列。

• lag(exp_str,offset,defval) over(partion by （分组字段）order by 分析字段)
• lead(exp_str,offset,defval) over(partion by （分组字段）order by 分析字段)

exp_str——分析字段名称；
offset——偏移量，即是上1个或上N个的值，假设当前行在表中排在第5行，则offset为3，则表示我们所要找的数据行就是表中的第2行(即5-3=2)；offset默认值为1；
defval——默认值，当两个函数取上N/下N个值，当在表中从当前行位置向前数N行已经超出了表的范围时，lag()函数将defval这个参数值作为函数的返回值

2.MySQL索引

2.1 常见索引分类

• 从索引的存储结构划分：B Tree索引、Hash索引、fulltext全文索引、R Tree索引（了解）
• 从应用层次划分：主键索引、唯一索引、普通索引、复合索引从索引的键值（字段）
• 类型划分：主键索引、辅助索引（二级索引）
• 从索引数据和内容数据逻辑关系划分：聚集索引（聚簇索引）、非聚集索引（非聚簇索引）

索引名称	说明
主键索引 (primarykey)	主键是一种唯一性索引,每个表只能有一个主键, 用于标识数据表中的每一条记录
唯一索引 (unique)	唯一索引指的是 索引列的所有值都只能出现一次, 必须唯一.
普通索引 (index)	最常见的索引,作用就是 加快对数据的访问速度

2.1.1 主键索引 (PRIMARY KEY)

• 一个表可以没有主键，但最多只能有一个主键，并且主键值不能包含NULL。
• 添加主键的时候会自动创建主键索引，主键索引是一种唯一性索引
• 根据主键进行where条件查询，效率高

-- 直接添加主键索引
CREATE TABLE test_index(
did INT primary key,
dname VARCHAR(20),
hobby VARCHAR(30)
);

-- 修改表结构 添加主键索引
ALTER TABLE 表名 ADD PRIMARY KEY ( 列名 )

2.1.2 唯一索引(UNIQUE)

特点: 索引列的所有值都只能出现一次, 必须唯一

-- 直接添加唯一索引
CREATE TABLE 表名(
列名 类型(长度),
UNIQUE [索引名称] (列名)
);

-- 使用create语句创建: 在已有的表上创建索引
create unique index 索引名 on 表名(列名(长度))
create unique index index_dname on test_index(dname);

-- 修改表结构添加索引
ALTER TABLE 表名 ADD UNIQUE 索引名(列名)

2.1.3 普通索引 (NORMAL INDEX)

普通索引的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHERE column=）或排序条件（ORDER BY column）中的数据列创建索引。

--使用create index 语句创建: 在已有的表上创建索引
create index 索引名 on 表名(列名[长度])

-- 修改表结构添加索引
ALTER TABLE f ADD INDEX 索引名 (列名)
alter table test_index add index dname_indx(dname);

2.1.4 删除索引

ALTER TABLE table_name DROP INDEX index_name;

2.1.5 索引在查询中如何使用

SELECT * FROM test_tab WHERE name = 一个外部输入的数据
刚开始，数据不多的时候，执行效果还不错。随着数据量的增加，这个查询，执行起来，越来越慢了。

然后在 name 上面建立了索引
CREATE INDEX idx_test4_name ON test_tab (name );
这样， 可以加快前面那个查询的速度。

但是，某天，你执行了下面这个SQL， 发现速度又慢了
SELECT * FROM test_tab WHERE age = 25
为啥呢？ 因为 age 字段上面，没有索引，索引只在 name 上面有

换句话说， 也就是 WHERE 里面的条件， 会自动判断，有没有 可用的索引，如果有， 该不该用。

2.2 索引的优缺点总结

• 索引的优点
• 大大的提高查询速度
• 可以显著的减少查询和排序的时间。
• 索引的缺点
  当对表中的数据进行增加，修改，删除的时候，索引要同时进行维护，数据量越大维护时
  间越长

3.MySQL视图

3.1 定义

• 视图是一种虚拟表。
• 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
• 向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句.
• 视图向用户提供基表数据的另一种表现形式

3.2 视图的作用

• 权限控制时可以使用
  比如,某几个列可以运行用户查询,其他列不允许,可以开通视图查询特定的列, 起到权限控制的作用
• 简化复杂的多表查询
  • 视图本身就是一条查询SQL,我们可以将一次复杂的查询构建成一张视图, 用户只要查询视图就可以获取想要得到的信息(不需要再编写复杂的SQL)
  • 视图主要就是为了简化多表的查询

3.3 视图的使用

create view 视图名 [column_list] as select语句;
view: 表示视图
column_list: 可选参数，表示属性清单，指定视图中各个属性的名称，默认情况下，与
SELECT语句中查询的属性相同
as : 表示视图要执行的操作
select语句: 向视图提供数据内容

3.4 视图与表的区别

• 视图是建立在表的基础上，表存储数据库中的数据，而视图只是做一个数据的展示
• 通过视图不能改变表中数据（一般情况下视图中的数据都是表中的列经过计算得到的结果, 不允许更新）
• 删除视图，表不受影响，而删除表，视图不再起作用