MySQL数据库，触发器、窗口函数、公用表表达式

触发器

触发器是由事件来触发某个操作（也包含INSERT、UPDATE、DELECT事件），如果定义了触发程序，当数据库执行这些语句时，就相当于事件发生了，就会自动激发触发器执行相应的操作。

当对数据表中的数据执行 插入、更新和删除操作，需要自动执行一些数据库逻辑时，就可以使用触发器来实现。

触发器的创建：

格式：

例：创建触发器before_insert，向表一插入数据之前，向表二中插入日志信息。

DELIMITER $CREATE TRIGGER before_insert_triBEFORE INSERT ON test_firstFOR EACH ROWBEGININSERT INTO test_second(t_log)VALUES('before insert ……');END $DELIMITER ;

创建触发器after_insert，向表一插入数据之后，向表二中插入日志信息。

DELIMITER $CREATE TRIGGER after_insert_triAFTER INSERT ON test_firstFOR EACH ROWBEGININSERT INTO test_second(t_log)VALUES('after insert ……');END $DELIMITER ;

例：在添加员工信息时，判断员工信息是否大于他领导的薪资，如果大于，则报'HY000'的错误，使得添加失败。

-- 创建触发器DELIMITER $CREATE TRIGGER sal_check_tri2BEFORE INSERT ON emp_test_triFOR EACH ROWBEGINDECLARE mgr_sal DECIMAL(7,2);SELECT sal INTO mgr_sal FROM emp_test_triWHERE empno = NEW.mgr;IF NEW.sal > mgr_salTHEN SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '薪资不能比领导高';END IF;END $DELIMITER ;-- 插入数据INSERT INTO emp_test_tri(empno,ename,mgr,sal)VALUES(8000,'Tom', 7788,2500);INSERT INTO emp_test_tri(empno,ename,mgr,sal)VALUES(8001,'Tom', 8000,3200);

注：触发器中的NEW表示当前正在添加的记录。OLD表示删除前、更新前的记录。

查看触发器：

查看当前数据库的所有触发器的定义：

SHOW TRIGGERS;

查看当前数据库中某个数据库的定义：

SHOW CREATE TRIGGER 触发器名;

从系统库information_schema的TRIGGERS表中查询触发器的信息：

SELECT * FROM  information_schema.TRIGGERS;

删除触发器

DROP TRIGGER 触发器名;

窗口函数

窗口函数的作用类似于在查询中对数据进行分组。与分组操作不同的是，分组操作会把分组的结果聚合成一条记录，而窗口函数是将结果置于每一条数据记录中。

例：查询员工信息，按部门分类，在每个员工前显示其所在部门的平均工资。

SELECT empno,ename,deptno,SUM(sal) OVER(PARTITION BY deptno) '部门平均工资'FROM emp;

可以发现，查询中确实对数据进行了分组，但是只是将每个组并列在了一起，然后在每个员工的后面显示其部门平均工资

窗口函数的语法格式：

函数 OVER (PARTITION BY 字段名 ORDER BY 字段名 ASC/DESC)

或者是

函数 OVER 窗口名 …… WINDOW 窗口名 AS (PARTITION BY 字段名 ORDER BY 字段名 ASC/DESC)

窗口的使用：

OVER括号中的分组排序规则的内容可以以一个窗口代替，最后在使用窗口的多个函数声明完后，用WINDOW 窗口名 AS (PARTITION BY 字段名 ORDER BY 字段名 ASC/DESC)指明窗口的具体规则的内容。

PARTITION BY子句：

指定窗口函数按照哪些字段分组，分组后，窗口函数在每个分组中分别执行。

ORDER BY：

指定窗口函数按照那些字段进行排序，也是在组内排序。

函数的分类：

序号函数：

ROW_NUMBER( )函数

ROW_NUMBER( )能够对数据中的序号进行顺序显示。按分组分别显示序号。

例：查看员工信息，以员工部门分组，在每个员工前显示其在部门的序号。每个部门中按员工工资排序。

SELECT ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal) 序号,empno,ename,deptnoFROM emp;

也可以利用新生成的序号，在后面加上WHERE 序号 < 3，求出每个部门工资排名前三的员工信息。

RANK( )函数

使用RANK( )函数能够对序号进行并列排序，并且会跳过重复的序号（比如序号为1，1，3……）

例：查看员工信息，以员工部门分组，每个部门中按员工工资排序，并显示其在工资的排名（跳过重复的排名序号）。

SELECT empno,ename,deptno,RANK() OVER(PARTITION BY deptno ORDER BY sal) '部门工资排名'FROM emp;

与前面的ROW_NUMBER( )函数不同的是，当遇到相同的值比较时，会判为相同值的记录排序序号一样，并跳过重复的排序再计数。

DENSE_RANK( )函数

DENSE_RANK( )函数对序号进行并列排序，并且不会跳过重复的序号（比如序号为1，1，2……）

例：查看员工信息，以员工部门分组，每个部门中按员工工资排序，并显示其在工资的排名（不跳过重复的排名序号）。

SELECT empno,ename,deptno,DENSE_RANK() OVER(PARTITION BY deptno ORDER BY sal) '部门工资排名'FROM emp;

与前面的RANK( )函数不同的是，不跳过重复的排序再计数。

分布函数：

PERCENT_RANK( )函数

PERCENT_RANK( )函数是等级值百分比函数。

计算方式：(rank - 1) / (rows - 1)

其中，rank的值是使用RANK( )函数产生的序号，rows的值为当前窗口的总记录数。

例：查看员工信息，以员工部门分组，每个部门中按员工工资排序，并显示其在工资的排名（跳过重复的排名序号），并显示其序号的等级值百分比。

SELECT empno,ename,deptno,RANK() OVER(PARTITION BY deptno ORDER BY sal) '部门工资排名',PERCENT_RANK() OVER(PARTITION BY deptno ORDER BY sal) '排名比例'FROM emp;

使用窗口的格式：

SELECT empno,ename,deptno,RANK() OVER w '部门工资排名',PERCENT_RANK() OVER w '排名比例'FROM emp WINDOW w AS (PARTITION BY deptno ORDER BY sal);

CUME_DIST( )函数

CUME_DIST( )函数主要用于查询小于或等于本记录的某个值的组内的记录的比例。

例：查询工资小于或等于当前员工的薪资的员工的比例

SELECT empno,ename,sal,deptno,CUME_DIST() OVER(PARTITION BY deptno ORDER BY sal ASC) '比例'FROM emp;

以MILLER为例，在10号部门中员工工资小于或等于1300的员工比例为0.3333……

以CLARK为例，在10号部门中员工工资小于或等于2450的员工比例为0.6666……

前后函数

LAG(expr,n)函数

LAG(expr,n)函数返回当前行的第前n行记录的expr的值。

例：查询上一个员工与当前员工的薪资的差值。

SELECT empno,ename,deptno,sal,pre_sal,sal - pre_sal diff_salFROM(SELECT empno,ename,deptno,sal,LAG(sal,1) OVER w pre_salFROM empWINDOW w AS (PARTITION BY deptno ORDER BY sal)) t;

子查询中的pre_sal即为上一个记录的薪资。将1改为2即为上两个记录的工资，找不到相应的记录结果为NULL。

首尾函数

FIRST_VALUES(expr)函数

FIRST_VALUES(expr)函数返回第一个记录的expr的值（分组内的第一个），会在每一行都显示第一个记录的expr的值。

例：

SELECT empno,ename,deptno,sal,FIRST_VALUE(sal) OVER(PARTITION BY deptno ORDER BY sal) '部门工资排名最高'
FROM emp;

LAST_VALUES(expr)函数

LAST_VALUES(expr)函数返回最后一个记录的expr的值。

其他函数

NTH_VALUES(expr,n)函数

NTH_VALUES(expr,n)函数返回第n个记录的expr的值。

NTILE(n)函数

NTILE(n)函数将分区中的有序数据分为n个桶，记录桶编号。

例：将员工按薪资分为三组。

SELECT NTILE(3) OVER w 桶编号,empno,deptno,ename,salFROM emp WINDOW w AS (PARTITION BY deptno ORDER BY sal);

即自动按薪资再分一个等级，按照要分的组数来均分等级。

公用表表达式

公用表表达式（或通用表表达式）简称为CTE（Common Table Expressions）。CTE是一个命名的临时结果集，作用范围是当前语句。CTE可以理解为一个可以复用的子查询。

公用表表达式分为普通公用表表达式和递归公用表表达式。

普通公用表表达式：

例：

WITH test_cteAS (SELECT DISTINCT deptno FROM emp)SELECT *FROM dept dJOIN test_cte eON d.deptno = e.deptno;

将查询结果放在WITH CTE名 AS ( )的括号中，就可以在下面的查询语句中将CTE当作一个表使用。可以有多个CTE，CTE可以引用其他CTE。

递归公用表表达式：

在WITH和CTE名中间插入RECURSIVE

例：

WITH RECURSIVE cteAS(-- 若UNION ALL前面的查询语句为A部分SELECT empno,ename,mgr,1 AS 第几代 FROM emp WHERE empno = 7839 -- 种子查询，设置第一代领导UNION ALL-- 若UNION ALL后面的查询语句为B部分SELECT a.empno,a.ename,a.mgr,第几代+1 FROM emp AS a JOIN cteON (a.mgr = cte.empno) -- 递归查询，找出以递归公用表表达式的人为领导的人，即找出A部分的下一代-- 执行完后，B部分变为新的A部分，继续找新的B部分，直到找不到任何记录为止。)SELECT empno,ename,第几代 FROM cte;-- 可以在此处加上WHERE子句，查询指定的第几代数。

A部分先设置查询的第一代，B部分再设置下一代的查询方法，当A、B执行完后，B会成为新的A部分，查找新的B部分，以此类推，直到找不到下一代记录为止。A、B部分用UNION ALL连接。

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/220962.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

02.Git常用基本操作

02.Git常用基本操作

一、基本配置 &#xff08;1&#xff09;打开Git Bash &#xff08;2&#xff09;配置姓名和邮箱 git config --global user.name "Your Name" git config --global user.email "Your email" 因为Git是分布式版本控制工具&#xff0c;所以每个用户都需要…
阅读更多...
Pytorch nn.Linear()的基本用法与原理详解及全连接层简介

Pytorch nn.Linear()的基本用法与原理详解及全连接层简介

主要引用参考&#xff1a; https://blog.csdn.net/zhaohongfei_358/article/details/122797190 https://blog.csdn.net/weixin_43135178/article/details/118735850 nn.Linear的基本定义 nn.Linear定义一个神经网络的线性层&#xff0c;方法签名如下&#xff1a; torch.nn.Li…
阅读更多...
Linux - 非root用户使用systemctl管理服务

Linux - 非root用户使用systemctl管理服务

文章目录 方式一 &#xff08;推荐&#xff09;1. 编辑sudoers文件&#xff1a;2. 设置服务文件权限&#xff1a;3. 启动和停止服务&#xff1a; 方式二1. 查看可用服务&#xff1a;2. 选择要配置的服务&#xff1a;3. 创建自定义服务文件&#xff1a;4. 重新加载systemd管理的…
阅读更多...
el-date-picker限制选择7天内禁止内框选择

el-date-picker限制选择7天内禁止内框选择

需求&#xff1a;elementPlus时间段选择框需要满足&#xff1a;①最多选7天时间。②不能手动输入。 <el-date-picker v-model"timeArrange" focus"timeEditable" :editable"false" type"datetimerange" range-separator"至&qu…
阅读更多...
福德植保无人机工厂：创新科技与绿色农业的完美结合

福德植保无人机工厂：创新科技与绿色农业的完美结合

亲爱的读者们&#xff0c;欢迎来到福德植保无人机工厂的世界。这里&#xff0c;科技与农业的完美结合为我们描绘出一幅未来农业的新篇章。福德植保无人机工厂作为行业的领军者&#xff0c;以其领先的无人机技术&#xff0c;创新的理念&#xff0c;为我们展示了一种全新的农业服…
阅读更多...
使用Httpclient来替代客户端的jsonp跨域解决方案

使用Httpclient来替代客户端的jsonp跨域解决方案

最近接手一个项目&#xff0c;新项目需要调用老项目的接口&#xff0c;但是老项目和新项目不再同一个域名下&#xff0c;所以必须进行跨域调用了&#xff0c;但是老项目又不能进行任何修改&#xff0c;所以jsonp也无法解决了&#xff0c;于是想到了使用了Httpclient来进行服务端…
阅读更多...
Vue简介

Vue简介

聚沙成塔每天进步一点点 ⭐ 专栏简介 Vue学习之旅的奇妙世界 欢迎大家来到 Vue 技能树参考资料专栏&#xff01;创建这个专栏的初衷是为了帮助大家更好地应对 Vue.js 技能树的学习。每篇文章都致力于提供清晰、深入的参考资料&#xff0c;让你能够更轻松、更自信地理解和掌握 …
阅读更多...
[密码学]AES

[密码学]AES

advanced encryption standard&#xff0c;又名rijndael密码&#xff0c;为两位比利时数学家的名字组合。 分组为128bit&#xff0c;密钥为128/192/256bit可选&#xff0c;对应加密轮数10/12/14轮。 基本操作为四种&#xff1a; 字节代换&#xff08;subBytes transformatio…
阅读更多...
PyQt6 QFontDialog字体对话框控件

PyQt6 QFontDialog字体对话框控件

锋哥原创的PyQt6视频教程&#xff1a; 2024版 PyQt6 Python桌面开发 视频教程(无废话版) 玩命更新中~_哔哩哔哩_bilibili2024版 PyQt6 Python桌面开发 视频教程(无废话版) 玩命更新中~共计50条视频&#xff0c;包括&#xff1a;2024版 PyQt6 Python桌面开发 视频教程(无废话版…
阅读更多...
【docker】修改docker的数据目录

【docker】修改docker的数据目录

背景 主节点是分配了较少内存和存储的低配机器&#xff0c;因为我们系统的rancher是用docker镜像启动的&#xff0c;而rancher和docker的默认目录都放在/var/lib下面&#xff0c;而这个/var目录目前只分配10G的存储&#xff0c;导致节点存储报警。因此想修改docker的数据目录&…
阅读更多...
中国高分辨率土壤侵蚀因子K

中国高分辨率土壤侵蚀因子K

中国高分辨率土壤侵蚀因子K 土壤可蚀性因子&#xff08;K&#xff09;数据&#xff0c;基于多种土壤属性数据计算&#xff0c;所用数据包括土壤黏粒含量&#xff08;%&#xff09;、粉粒含量&#xff08;%&#xff09;、砂粒含量&#xff08;%&#xff09;、土壤有机碳含量&…
阅读更多...
鸿蒙系统（HarmonyOS）之方舟框架（ArkUI）介绍

鸿蒙系统（HarmonyOS）之方舟框架（ArkUI）介绍

鸿蒙开发官网&#xff1a;HarmonyOS应用开发官网 - 华为HarmonyOS打造全场景新服务 方舟开发框架&#xff08;简称&#xff1a;ArkUI&#xff09;&#xff0c;是一套构建HarmonyOS应用界面的UI开发框架&#xff0c;它提供了极简的UI语法与包括UI组件、动画机制、事件交互等在内…
阅读更多...
音视频参数介绍

音视频参数介绍

一、视频参数概念 单个视频帧&#xff1a;可以简单地理解成为一张图片 单个视频帧主要的参数概念&#xff1a; 分辨率&#xff1a; 分辨率是指图像或显示器上像素的数量&#xff0c;通常用横向像素数乘以纵向像素数表示。例如&#xff0c;1920x1080 表示宽度为1920像素&…
阅读更多...
多维时序 | MATLAB实现WOA-CNN-LSTM-Multihead-Attention多头注意力机制多变量时间序列预测

多维时序 | MATLAB实现WOA-CNN-LSTM-Multihead-Attention多头注意力机制多变量时间序列预测

多维时序 | MATLAB实现WOA-CNN-LSTM-Multihead-Attention多头注意力机制多变量时间序列预测 目录 多维时序 | MATLAB实现WOA-CNN-LSTM-Multihead-Attention多头注意力机制多变量时间序列预测预测效果基本介绍模型描述程序设计参考资料 预测效果 基本介绍 MATLAB实现WOA-CNN-LST…
阅读更多...
新增工具箱管理功能、重构网站证书管理功能，1Panel开源面板v1.9.0发布

新增工具箱管理功能、重构网站证书管理功能，1Panel开源面板v1.9.0发布

2023年12月18日&#xff0c;现代化、开源的Linux服务器运维管理面板1Panel正式发布v1.9.0版本。 在这一版本中&#xff0c;1Panel引入了新的工具箱管理功能&#xff0c;包含Swap分区管理、Fail2Ban管理等功能。此外&#xff0c;1Panel针对网站证书管理功能进行了全面重构&…
阅读更多...
Django(一）

Django(一）

1.web框架底层 1.1 网络通信 注意&#xff1a;局域网 个人一般写程序&#xff0c;想要让别人访问&#xff1a;阿里云、腾讯云。 去云平台租服务器&#xff08;含公网IP&#xff09;程序放在云服务器 先以局域网为例 我的电脑【服务端】 import socket# 1.监听本机的IP和…
阅读更多...
机器学习算法---时间序列

机器学习算法---时间序列

类别内容导航机器学习机器学习算法应用场景与评价指标机器学习算法—分类机器学习算法—回归机器学习算法—聚类机器学习算法—异常检测机器学习算法—时间序列数据可视化数据可视化—折线图数据可视化—箱线图数据可视化—柱状图数据可视化—饼图、环形图、雷达图统计学检验箱…
阅读更多...
条款5：了解c++默默编写并调用了哪些函数

条款5：了解c++默默编写并调用了哪些函数

如果你不自己声明&#xff0c;编译器会替你声明&#xff08;编译器版本的&#xff09;拷贝构造函数、拷贝赋值运算符和析构函数。此外&#xff0c;如果你没有声明任何构造函数&#xff0c;编译器会为你声明一个默认构造函数。 class Empty{};本质上和写成下面这样是一样的: c…
阅读更多...
Java小案例-基于javaweb宿舍寝室管理系统

Java小案例-基于javaweb宿舍寝室管理系统

目录 前言 项目介绍 源码获取 前言 《基于javaweb宿舍管理系统》该项目采用技术&#xff1a;jsp servlet mysqljdbccssjs等相关技术 系统功能分为学生管理&#xff0c;宿管管理&#xff0c;楼宇管理&#xff0c;宿舍管理&#xff0c;住宿管理&#xff0c;管理员功能包括学…
阅读更多...
MongoDB的查询分析explain和hint

MongoDB的查询分析explain和hint

本文主要介绍MongoDB的查询分析explain和hint。 目录 MongoDB的查询分析explainhint MongoDB的查询分析 在MongoDB中&#xff0c;"explain"和"hint"是两个用于查询优化和分析的关键指令。 explain 在MongoDB中&#xff0c;explain()是一个用于查询分析的…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023