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

本文涉及的产品
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用版 2核4GB 50GB
简介: MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】

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

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
15天前
|
JSON 关系型数据库 MySQL
✅MySQL用了函数到底会不会导致索引失效
MySQL 8.0 引入了函数索引,打破了传统观念,允许在索引中使用函数,提升查询性能。通过创建基于表达式的索引,如 `CONCAT`、`SUBSTRING_INDEX`、`YEAR`、`MONTH` 等,可以优化涉及这些函数的查询。虽然提高了某些查询速度,但也会增加数据维护成本。应谨慎使用,确保表达式确定且适用于常见查询模式。示例包括基于字符串、日期、数学运算和JSON属性的索引。
✅MySQL用了函数到底会不会导致索引失效
|
6天前
|
存储 关系型数据库 MySQL
如何理解Mysql的索引及他们的原理--------二叉查找树和平衡二叉树和B树和B+树
如何理解Mysql的索引及他们的原理--------二叉查找树和平衡二叉树和B树和B+树
|
16天前
|
关系型数据库 MySQL 数据库
MySQL索引的类型与优化方法
MySQL索引的类型与优化方法
|
16天前
|
存储 关系型数据库 MySQL
MySQL索引设计原则与优化策略
MySQL索引设计原则与优化策略
|
16天前
|
存储 关系型数据库 MySQL
MySQL删除索引的方法与注意事项
MySQL删除索引的方法与注意事项
|
1月前
|
存储 关系型数据库 MySQL
MySQL触发器实战:自动执行的秘密
MySQL触发器实战:自动执行的秘密
44 3
|
1月前
|
存储 SQL 关系型数据库
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(1)
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】
|
1月前
|
存储 SQL 关系型数据库
MySQL数据库进阶第四篇(视图/存储过程/触发器)
MySQL数据库进阶第四篇(视图/存储过程/触发器)
|
1月前
|
存储 SQL 关系型数据库
MySQL周内训参照4、触发器-插入-修改-删除
MySQL周内训参照4、触发器-插入-修改-删除
18 1
|
1月前
|
SQL 存储 关系型数据库
MYSQL--触发器
MYSQL--触发器