MySQL【触发器、存储过程、函数、范式】

2024-06-23 01:36
文章标签 mysql 函数 触发器 过程 存储 范式 database

本文主要是介绍MySQL【触发器、存储过程、函数、范式】，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

day53

MySQL

触发器

创建触发器：（before ： 前置触发器、after ：后置触发器）

语法：
delimiter xx 指定分隔符xxcreate trigger 触发器名 [before | after] 触发事件 on 表名 for each row 执行语句begin多条执行语句end 加上 “结束符xx”delimiter ;还原分隔符未；

注意：

由某个事件去触发某个操作，这些事件有：insert、update、delete语句；

new.字段：当前写法可以获得添加语句字段对应的值

old.字段：当前写法可以获得删除语句字段对应的值

在MySQL中，DELIMITER是用来指定SQL语句中的语句分隔符的关键字。

默认情况下，分号（;）作为语句的结束符号。

当在一个语句中包含了多个语句，比如在创建存储过程、触发器等时，如果直接使用分号作为结束符号，会导致MySQL将整个语句作为一个语句进行解析，从而导致语法错误。所以才有指定分隔符，又还原这个操作。

补充解释：

数据添加后相应的表数据更新的需求，每次用更新语句十分不便
ps：update class set sum = sum+1 where id =1;
所以使用触发器
begin end类似于{}，有点像函数，多个语句时使用，单个可不用
对于前触发器，例如删除操作，要在删除之前才拿得到，如果删除之后再操作不行，因为数据已经被删了
在第一次操作之后，再进行重复操作不会再触发触发器

# 触发器# 创建场景 ----------------------------------------------------------------------------# 创建学科表，并添加数据
CREATE TABLE course(id INT(3) PRIMARY KEY auto_increment,name VARCHAR(32),sum INT(3) DEFAULT 0
)
INSERT INTO course(name) VALUES('JavaEE');
INSERT INTO course(name) VALUES('Python');
INSERT INTO course(name) VALUES('HTML');# 创建班级表，并添加数据
CREATE TABLE class(id INT(3) PRIMARY KEY auto_increment,name VARCHAR(32),sum INT(3) DEFAULT 0
)
INSERT INTO class(name) VALUES('01JavaEE');
INSERT INTO class(name) VALUES('02JavaEE');
INSERT INTO class(name) VALUES('01Python');
INSERT INTO class(name) VALUES('02Python');
INSERT INTO class(name) VALUES('01HTML');
INSERT INTO class(name) VALUES('02HTML');# 创建学生表
CREATE TABLE student(id INT(3) PRIMARY KEY auto_increment,name VARCHAR(32),class_id INT(3),course_id INT(3)
)# ------------------------------------------------------------------------------------------------------# 需求：添加学生，并更新班级表和学科表的数据# 创建触发器（添加学生数据之后，就会触发班级表和学科表更新数据的操作）
# delimiter xx - 设置SQL命令的结束符
# trigger - 触发器
# after - 后置触发
delimiter xx
create trigger tri01 after insert on student for each rowBEGINUPDATE class SET sum=sum+1 WHERE id=new.class_id;UPDATE course SET sum=sum+1 WHERE id=new.course_id;END xx
delimiter ;INSERT INTO student(name,class_id,course_id) VALUES('小王',1,1);
INSERT INTO student(name,class_id,course_id) VALUES('小马',1,1);
INSERT INTO student(name,class_id,course_id) VALUES('小李',2,1);
INSERT INTO student(name,class_id,course_id) VALUES('麻生希',3,2);
INSERT INTO student(name,class_id,course_id) VALUES('椎名空',4,2);# ------------------------------------------------------------------------------------------------------# 需求：删除学生，并更新班级表和学科表的数据# 创建触发器（删除学生数据之前，就会触发班级表和学科表更新数据的操作）
delimiter xx
create trigger tri02 before delete on student for each rowBEGINUPDATE class SET sum=sum-1 WHERE id=old.class_id;UPDATE course SET sum=sum-1 WHERE id=old.course_id;END xx
delimiter ;DELETE FROM student WHERE id = 2;# ------------------------------------------------------------------------------------------------------# 删除触发器
DROP TRIGGER tri01;
DROP TRIGGER tri02;

运行分析：
后触发器
前触发器

存储过程

理解：类似java里面的方法，但无返回值，有返回参数

参数列表输入类型的参数 -- in输出类型的参数 -- out输入输出类型的参数 -- inout

注意：

对于传入的参数，要与本身参数区分，识别不了会报错

返回参数，需要用到传出参数，into传出参数

调用存储过程，有两种

  1. 一种直接调用查就会直接打印结果
  2. 另一种，拿到参数变量值，不会立即打印结果，还可以进行再查操作

对于传入参数与传出参数类型相同，可用传入传出参数，相当于简写

# 存储过程
# 注意：类似于Java里的方法# 创建场景 ----------------------------------------------------------------------------# 创建学生表，并添加数据
CREATE TABLE student(id INT(3) PRIMARY KEY auto_increment,name VARCHAR(32),sex VARCHAR(32),age INT(3),salary FLOAT(8,2),course VARCHAR(32)
)
INSERT INTO student(name,sex,age,salary,course) VALUES('奇男子','男',23,10000,'Java');
INSERT INTO student(name,sex,age,salary,course) VALUES('小兰','男',20,12000,'Java');
INSERT INTO student(name,sex,age,salary,course) VALUES('奇女子','女',19,17000,'Java');
INSERT INTO student(name,sex,age,salary,course) VALUES('刘德华','男',21,15000,'Java');
INSERT INTO student(name,sex,age,salary,course) VALUES('马德华','男',27,10000,'Java');
INSERT INTO student(name,sex,age,salary,course) VALUES('霍建华','男',19,12000,'Java');
INSERT INTO student(name,sex,age,salary,course) VALUES('华晨宇','男',32,5000,'Python');
INSERT INTO student(name,sex,age,salary,course) VALUES('黄日华','男',45,4000,'Python');
INSERT INTO student(name,sex,age,salary,course) VALUES('任达华','男',28,7000,'Python');
INSERT INTO student(name,sex,age,salary,course) VALUES('周华健','男',30,8000,'Python');
INSERT INTO student(name,sex,age,salary,course) VALUES('欧阳震华','男',23,12000,'Python');
INSERT INTO student(name,sex,age,salary,course) VALUES('麻生希','女',30,7000,'HTML');
INSERT INTO student(name,sex,age,salary,course) VALUES('椎名空','女',23,6000,'HTML');
INSERT INTO student(name,sex,age,salary,course) VALUES('水野朝阳','女',28,8000,'HTML');# ----------------------------------------------------------------------------# 最简单的存储过程
# 查询学生表和用户表里的数据
delimiter xx
CREATE PROCEDURE pro01()BEGINSELECT * FROM student;SELECT * FROM user;END xx
delimiter ;# 调用存储过程
CALL pro01();# ----------------------------------------------------------------------------# SQL中存储过程 与 Java中方法 的区别
# Java方法中的参数指的是外界传入到方法内部的数据，返回值指定是方法结束后返回的具体数据
# SQL存储过程中没有返回值，但是有返回参数# 存储过程中参数的分类：
#		in - 传入参数
#		out - 传出参数
#		inout - 传入传出参数# ----------------------------------------------------------------------------# 需求：创建存储过程，传入学生id，查询出学生姓名
delimiter xx
CREATE PROCEDURE pro02(IN s_id INT(3))BEGINSELECT name FROM student WHERE id=s_id;END xx
delimiter ;# 调用存储过程
CALL pro02(2);# ----------------------------------------------------------------------------# 需求：创建存储过程，传入学生id，返回学生姓名
delimiter xx
CREATE PROCEDURE pro03(IN s_id INT(3),OUT s_name VARCHAR(32))BEGINSELECT name INTO s_name FROM student WHERE id=s_id;END xx
delimiter ;# 调用存储过程
CALL pro03(3,@s_name);
SELECT @s_name;# ----------------------------------------------------------------------------# 需求：创建存储过程，传入学生id，返回学生年龄
delimiter xx
CREATE PROCEDURE pro04(INOUT param INT(3))BEGINSELECT age INTO param FROM student WHERE id=param;END xx
delimiter ;# 调用存储过程
SET @param = 3;
CALL pro04(@param);
SELECT @param;# ----------------------------------------------------------------------------# 删除存储过程
DROP PROCEDURE pro01;
DROP PROCEDURE pro02;
DROP PROCEDURE pro03;
DROP PROCEDURE pro04;

运行分析：

调用存储过程方式1

调用存储过程p1方式1
存储过程p2

调用存储过程方式2

调用存储过程p3方式2
存储过程p4

函数

预定义函数

数学函数：

绝对值函数：select abs(字段) from 表 ps:此函数指的是求绝对值

平方根函数：select sqrt(9) ps:得到的值为3

求余函数：select mod(10,3) / select 10%3 ps:得到的值为1

随机函数：select rand() ps:得到的值为0~1

幂运算函数：select pow(10,2) ps:10的2次方

字符串函数：

长度函数：select length(‘内容’)

合并函数：select concat(’hello’,’world’)

截取字符串函数：select substring(字段,start,end)

日期/时间函数：

​ 当前日期函数：curdate()

​ 当前时间函数：curtime()

​ 当前日期+时间函数：now()

​ 年函数：year(now())

​ 月函数：month(now())

​ 日函数：dayofmonth(now())

​ 时函数：hour(now())

​ 分函数：minute(now())

​ 秒函数：second(now())

​ 时间格式函数：年月日时分秒‘别名

注意：

当然对于在Navbbicat的 函数里可以看到是否删除

# 预定义函数
# 理解：MySQL给我们提供的函数
# 注意：函数必须有返回值# 数学类函数 -----------------------------------------------------
SELECT ABS(-100);# 求绝对值
SELECT sqrt(9);  # 求平方根
SELECT mod(10,3);# 求余数
SELECT pow(2,3); # 求次方
SELECT rand();# 求随机值# 字符串类函数 -----------------------------------------------------SELECT CONCAT("用良心做教育","做真实的自己");# 拼接字符串
SELECT SUBSTR("用良心做教育",2,2);# 截取字符串(目标字符串，第几个字符，截取长度)
SELECT length("用良心做教育");# 获取长度（字节）# 日期时间类函数 -----------------------------------------------------SELECT now();# 获取当前日期时间
SELECT YEAR(NOW());# 获取年
SELECT MONTH(NOW());# 获取月
SELECT DAYOFMONTH(NOW());# 获取日
SELECT HOUR(NOW());# 获取时
SELECT MINUTE(NOW());# 获取分
SELECT SECOND(NOW());# 获取秒
SELECT DATE_FORMAT(NOW(),"%Y年%m月%d日 %H时%i分%s秒");

运行分析：
预定义函数2
预定义函数3

自定义函数

返回值，需要一个变量去接查到的数据，再返回此变量
对于5.7版本，5.5版本之类不加关键字就可运行，8.0必须加不然运行即报错
加了关键字DETERMINISTIC就认为它是一个函数

语法：
create function 函数名([参数列表]) returns 数据类型 DETERMINISTIC
beginsql语句;return 值;
end

# 自定义函数
# 注意：MySQL的函数必须有返回值# 需求：创建一个函数，传入学生id，返回学生信息delimiter xx
CREATE FUNCTION fun(s_id INT(3)) RETURNS VARCHAR(32) DETERMINISTICBEGINDECLARE s_name VARCHAR(32);SELECT name INTO s_name FROM student WHERE id=s_id;RETURN s_name;END xx
delimiter ;# 调用函数SELECT fun(3);# 删除函数：
DROP function fun;

运行分析：
自定义函数

范式

第一范式：理解为所有数据集中在一张表，存在多种问题
第二范式：简单分表，解决数据冗余，但一对多种关系出现多个数据不可避免
第三范式：再细化分表，第一范式存在问题全部解决；但表就会很多，需要多表联合查询，效率很低
反范式：就是把第三范式细化的多张表，又重新融合一些表，就会出现数据冗余，这里就考验程序员的能力考虑融合
空间换时间：对于现在的空间大可不考虑就得考虑时间
对于第三范式和反范式之间选择：根据具体项目而定，哪些该融合，哪些该细分；就简单说用的少的表可以融合到其他表

下面以表的形式理解

第一范式

第一范式

第二范式

第二范式

第三范式

第三范式

反范式

反范式

小结：

  1. 触发器
  2. 存储过程
  3. 函数
  4. 范式

这篇关于MySQL【触发器、存储过程、函数、范式】的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1085963。

相关文章

浅析Spring Security认证过程

浅析Spring Security认证过程

类图 为了方便理解Spring Security认证流程，特意画了如下的类图，包含相关的核心认证类 概述 核心验证器 AuthenticationManager 该对象提供了认证方法的入口，接收一个Authentiaton对象作为参数; public interface AuthenticationManager {Authentication authenticate(Authenti
阅读更多...
SQL中的外键约束

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点： 1.确保子表中的某个字段（外键）只能引用父表中的有效记录2.主表中的列被删除时，子表中的关联列也会被删除3.主表中的列更新时，子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
如何去写一手好SQL

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 博主曾经操作过超过4亿行数据
阅读更多...
异构存储（冷热数据分离）

异构存储（冷热数据分离）

异构存储主要解决不同的数据，存储在不同类型的硬盘中，达到最佳性能的问题。 异构存储Shell操作 （1）查看当前有哪些存储策略可以用 [lytfly@hadoop102 hadoop-3.1.4]$ hdfs storagepolicies -listPolicies （2）为指定路径（数据存储目录）设置指定的存储策略 hdfs storagepolicies -setStoragePo
阅读更多...
HDFS—存储优化（纠删码）

HDFS—存储优化（纠删码）

纠删码原理 HDFS 默认情况下，一个文件有3个副本，这样提高了数据的可靠性，但也带来了2倍的冗余开销。 Hadoop3.x 引入了纠删码，采用计算的方式，可以节省约50％左右的存储空间。 此种方式节约了空间，但是会增加 cpu 的计算。 纠删码策略是给具体一个路径设置。所有往此路径下存储的文件，都会执行此策略。 默认只开启对 RS-6-3-1024k
阅读更多...
作业提交过程之HDFSMapReduce

作业提交过程之HDFSMapReduce

作业提交全过程详解 （1）作业提交 第1步：Client调用job.waitForCompletion方法，向整个集群提交MapReduce作业。 第2步：Client向RM申请一个作业id。 第3步：RM给Client返回该job资源的提交路径和作业id。 第4步：Client提交jar包、切片信息和配置文件到指定的资源提交路径。 第5步：Client提交完资源后，向RM申请运行MrAp
阅读更多...
性能分析之MySQL索引实战案例

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题，本文将一起从代码层去分析为什么没有建立索引？ 开源ERP项目地址：https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置
阅读更多...
MySQL数据库宕机，启动不起来，教你一招搞定！

MySQL数据库宕机，启动不起来，教你一招搞定！

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG、Mongodb数据库运维（如安装迁移，性能优化、故障应急处理等）公众号：老苏畅谈运维欢迎关注本人公众号，更多精彩与您分享。 MySQL数据库宕机，数据页损坏问题，启动不起来，该如何排查和解决，本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志，排查哪个表（表空间
阅读更多...
hdu1171（母函数或多重背包）

hdu1171（母函数或多重背包）

题意：把物品分成两份，使得价值最接近 可以用背包，或者是母函数来解，母函数（1 + x^v+x^2v+.....+x^num*v）（1 + x^v+x^2v+.....+x^num*v）（1 + x^v+x^2v+.....+x^num*v） 其中指数为价值，每一项的数目为（该物品数+1）个 代码如下： #include<iostream>#include<algorithm>
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2