MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】（2）

MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】（1）https://developer.aliyun.com/article/1534299

1.5、if 函数

MySQL 的 IF 函数和 HQL 是一样的，这里不做介绍。

1.6、窗口函数

       窗口函数一般都是配合聚合函数使用的，毕竟使用窗口就是为了把一类时间或者其它属性有关系的数据联系在一起。这里同样只介绍一些我没用过的函数：

1.6.1、CUME_DIST

用途：分组内小于、等于当前rank值的行数 / 分组内总行数

公式：rank / rows

场景：查询小于等于当前行某个值的记录占总记录的比例

解释：其实就是用 rank 函数的结果 / 记录总数

-- 该员工的薪资超过了相同部门多少人
SELECT emp_id,
       emp_name,
       department,
       salary,
       ROUND(CUME_DIST() OVER (PARTITION BY department ORDER BY salary),2) AS rate
FROM emp;

1.6.2、FIRST_VALUE 和 LAST_VALUE

用途：分组内的第一条/最后一条记录的某个字段的值

应用场景：截止目前按照xx排序后的第一名/最后一名的值

注意：它是对每一条记录都进行一次计算（相当于 rows between unbounded preceding and current row），而不是全局进行计算（它不会把该行之后考虑进计算范围）

-- 查询出同部门内工资最低和最高的人的薪资
SELECT emp_name,
       salary,
       FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary) AS first,
       LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary) AS last
FROM emp;

1.6.3、NTH_VALUE

用途：返回窗口内地 exper 个值。（expr 可以是表达式，也可以是数字）

SELECT emp_name,
       department,
       salary,
       NTH_VALUE(salary,2) over (PARTITION BY department ORDER BY salary) AS second
FROM emp;

1.6.4、NTILE

用途：将分区中的有序数据分为n个等级，记录等级数（等级规则取决于是否分区和排序字段）

应用场景：将员工按照薪资进行划分等级

SELECT emp_name,
       department,
       salary,
       NTILE(3) over (ORDER BY salary) AS grade
FROM emp;

2、视图

2.1、视图的概念

介绍

• 视图（view）是一个虚拟表，非真实存在，其本质是根据SQL语句获取动态的数据集，并为其命名，用户使用时只需使用视图名称即可获取结果集，并可以将其当作表来使用。
• 数据库中只存放了视图的定义，而并没有存放视图中的数据。
• 这些数据存放在原来的表中。 使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。因此，视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。

作用

• 简化代码，可以把重复使用的查询封装成视图重复使用，同时可以使复杂的查询易于理解和使用。
• 安全原因，如果一张表中有很多数据，很多信息不希望让所有人看到，此时可以使用视图视，如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等，可以对不同的用户，设定不同的视图。

2.2、创建视图

这里只介绍常用的创建方法：

语法：

CREATE [OR REPLACE]
VIEW 视图名称
AS SELECT语句

案例：

CREATE OR REPLACE
VIEW salary_grade
AS SELECT *, NTILE(3) over (ORDER BY salary) AS grade FROM emp;

2.3、查看所有表和视图

SHOW FULL TABLES;

2.4、修改视图

-- 1.通过alter修改视图
ALTER VIEW 视图名 AS SELECT语句;
-- 2.通过 create or replace 覆盖视图
CREATE OR REPLACE
VIEW 视图名
AS SELECT语句;

某些视图是可更新的，比如基表。对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。如果视图包含下述结构中的任何一种，那么它就是不可更新的：

• 聚合函数（SUM(), MIN(), MAX(), COUNT()等）
• DISTINCT
• GROUP BY
• HAVING
• UNION或UNION ALL
• 位于选择列表中的子查询
• JOIN
• FROM子句中的不可更新视图
• WHERE子句中的子查询，引用FROM子句中的表。
• 仅引用文字值（在该情况下，没有要更新的基本表）

注意：视图中虽然可以更新数据，也就是说，可以在UPDATE、DELETE或INSERT等语句中使用它们，以更新基表的内容。但是有很多的限制。一般情况下，最好将视图作为查询数据的虚拟表，而不要通过视图更新数据。因为，使用视图更新数据时，如果没有全面考虑在视图中更新数据的限制，就可能会造成数据更新失败。

-- 往视图中插入数据
INSERT INTO emp_order_by_salary(emp_id, emp_name, salary, department) VALUES (1001,'谢永强',3500,'人事部');
-- 修改视图数据
UPDATE emp_order_by_salary SET emp_name='狄仁杰' WHERE emp_id=1001;
-- 删除视图数据
DELETE FROM emp_order_by_salary WHERE emp_id=1001;

注意：

• 视图永远只是一个虚拟表，不存储数据，修改视图就是修改基表！

MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】（3）https://developer.aliyun.com/article/1534304