devbox
从前慢现在也慢
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

MySQL基础之触发器，函数，存储过程_mysql触发器调用存储过程

作者：从前慢现在也慢 | 2024-06-14 10:00:50

赞

踩

mysql触发器调用存储过程

目录

1 MySQL触发器

2 存储过程

2.1 创建存储过程

2.2 变量

2.2.1 存储过程内使用变量

2.2.2 赋值变量

2.2.3 变量作用域

2.3 查看并使用存储

2.3.1 查看调用

2.3.2 存储过程控制语句

2.4 修改删除存储

3 函数

3.1 创建函数

3.2 查看并使用

3.3 修改删除函数

3.4 自带基本函数

3.4.1 字符串类

3.4.2 数学类

3.4.3 日期时间类

3.4.4 系统信息函数

3.4.5 窗口函数

3.4.5.1 排序类

3.4.5.2 获取下一行Lead

1 MySQL触发器

触发器创建：

  1. create trigger trigger_name
  2.     before|after trigger_event 
  3.     on TableName 
  4.     for each row 
  5.     trigger_stmt;

参数说明：

  • trigger_event : 就是insert|update|delete
  • trigger_stmt : 就是要执行的操作
  • for each row : 表示任何一条记录上的操作满足触发事件都会触发该触发器

创建触发器包含多条执行语句：

  1. delimiter $$  
  2. 此处操作时说在触发器内部要用分号结束操作，为了不和系统分号;冲突就修改用$$
  3. create trigger 
  4.     trigger_name 
  5.     before|after trigger_event 
  6.     on TableName for each row 
  7.     begin
  8.      insert into log_table()...;
  9.      insert into log1_table()....;
  10.     end
  11. delimiter ;
  12. 把结束操作符合还修改为原来的分号 ;

  1. 触发器查看：
  2. show triggers;
  3.  
  4. 触发器删除：
  5. drop trigger trigger_name;


2 存储过程

2.1 创建存储过程

  1. CREATE PROCEDURE procedure_name([procedure_paramter[,…]])
  2.     [characteristic…] routine_body

参数说明：

  • procedure_paramter：参数表示存储过程的参数，为[ IN | OUT | INOUT ] <参数名> <类型> ，in：输入类型，out：输出类型，inout：输入输出类型
  • characteristic:表示存储过程的特性：如language SQL等
  • routine_body：表示存储过程序的sql语句

示例例子

  1. delimiter $$
  2. create procedure procedure_name (参数[可选])
  3. begin 
  4.     select * from temp_table;
  5. end$$
  6. delimiter ;

2.2 变量

2.2.1 存储过程内使用变量

在 MySQL中使用declare声明变量，语法：

  1. delcare var_name type [default vaule];
  2. 示例例子
  3. declare employee_sal int default 1000;

2.2.2 赋值变量

在MySQL中通过关键字set或select ... into...来给变量赋值
set var_name=expr[,...];或者使用select ... into...语句实现赋值，语法如下：

  1. select field_name into var_name from table_name where conditon ;
  2. 示例例子
  3. SELECT 'Hello World' into @x;  

存储过程中使用的例子

  1. delimiter $$
  2. CREATE PROCEDURE proc1(OUT s int)  
  3.      BEGIN 
  4.      SELECT COUNT(*) INTO s FROM user;  
  5.     END 
  6.      $$  
  7. delimiter ;
  8.  
  9. set @s :=0;//给一个用户变量赋值
  10. CALL proc1(@s);//执行存储过程
  11. select @s;//查看结果

2.2.3 变量作用域

内部的变量在其作用域范围内享有更高的优先权，当执行到end时，内部变量消失，此时已经在其作用域外，变量不再可见了，应为在存储过程外再也不能找到这个申明的变量，但是可以通过out参数或者将其值指派给会话变量来保存其值。

  1. DELIMITER //  
  2. CREATE PROCEDURE proc3()  
  3.     begin 
  4.     declare x1 varchar(5) default 'outer';  
  5.     begin 
  6.     declare x1 varchar(5) default 'inner';  
  7.     select x1;  
  8.     end;  
  9.     select x1;  
  10.     end;  
  11.   //  
  12. DELIMITER ;  

点击了解MySQL用户变量、系统变量、局部变量的理解

2.3 查看并使用存储

2.3.1 查看调用

查看存储过程：

  1. show procedure status where db='数据库名' 查看状态信息
  2. show create procddure procedure_name 查看定义信息
  3.  
  4. 查看当前数据库中存储过程
  5. SELECT * FROM mysql.proc WHERE db='数据库名'; 
  6. select * from information_schema.routines where routine_schema='数据库名'; 

调用存储

CALL  procedure_name ( [  parameter  [ ,  ...  ] ] )

2.3.2 存储过程控制语句

if-then -else语句

  1. DELIMITER //  
  2.  CREATE PROCEDURE proc2(IN parameter int)  
  3.     begin 
  4.     declare var int;  
  5.     set var=parameter+1;  
  6.     if var=0 then 
  7.     insert into t values(17);  
  8.     end if;  
  9.     if parameter=0 then 
  10.     update t set s1=s1+1;  
  11.     else 
  12.     update t set s1=s1+2;  
  13.      end if;  
  14.      end;  
  15.      //  
  16. DELIMITER ;

case语句

  1. DELIMITER //  
  2. CREATE PROCEDURE proc3 (in parameter int)  
  3.     begin 
  4.     declare var int;  
  5.     set var=parameter+1;  
  6.     case var  
  7.     when 0 then   
  8.     insert into t values(17);  
  9.     when 1 then   
  10.     insert into t values(18);  
  11.     else   
  12.     insert into t values(19);  
  13.     end case;  
  14.     end;  
  15.     //  
  16. DELIMITER ; 

循环语句
while ···· end while语句

  1. DELIMITER //  
  2. CREATE PROCEDURE proc4()  
  3.    begin 
  4.    declare var int;  
  5.    set var=0;  
  6.    while var<6 do  
  7.    insert into t values(var);  
  8.    set var=var+1;  
  9.    end while;  
  10.    end;  
  11.    //  
  12. DELIMITER ; 

repeat···· end repeat：
它在执行操作后检查结果，而while则是执行前进行检查

  1. DELIMITER //  
  2.  CREATE PROCEDURE proc5 ()  
  3.       begin   
  4.       declare v int;  
  5.       set v=0;  
  6.       repeat  
  7.       insert into t values(v);  
  8.       set v=v+1;  
  9.       until v>=5  
  10.       end repeat;  
  11.       end;  
  12.      //  
  13. DELIMITER ; 

loop ·····end loop:
loop循环不需要初始条件，这点和while 循环相似，同时和repeat循环一样不需要结束条件, leave语句的意义是离开循环。

  1. DELIMITER //  
  2.  CREATE PROCEDURE proc6 ()  
  3.       begin 
  4.      declare v int;  
  5.       set v=0;  
  6.       LOOP_LABLE:loop  
  7.       insert into t values(v);  
  8.       set v=v+1;  
  9.       if v >=5 then 
  10.       leave LOOP_LABLE;  
  11.       end if;  
  12.       end loop;  
  13.       end;  
  14.      //  
  15. DELIMITER ; 

ITERATE迭代
ITERATE : 通过引用复合语句的标号,来从新开始复合语句，在MySQL的LOOP，WHILE和REPEAT语句中，当想再次执行循环主体时，可以使用ITERATE语句，即：当不满足条件时，要继续执行的意思
LABLES 标号：标号可以用在begin repeat while或者loop 语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步

  1. DELIMITER //  
  2.  CREATE PROCEDURE proc10 ()  
  3.       begin 
  4.       declare v int;  
  5.       set v=0;  
  6.       LOOP_LABLE:loop  
  7.       if v=3 then   
  8.       set v=v+1;  
  9.       ITERATE LOOP_LABLE;  
  10.       end if;  
  11.       insert into t values(v);  
  12.       set v=v+1;  
  13.       if v>=5 then 
  14.       leave LOOP_LABLE;  
  15.       end if;  
  16.       end loop;  
  17.      end;  
  18.      //  
  19. DELIMITER ; 

2.4 修改删除存储

修改存储过程：

alter procedure procedure_name [sql语句]

删除存储过程：

drop procedure procedure_name;

3 函数

3.1 创建函数

在MySQL中创建函数通过SQL语句CREATE Function来实现，其语法形式如下：

  1. CREATE FUNCTION function function_name([function_paramter[,…]])
  2. [returns 数据类型]
  3.     [characteristic…] routine_body

参数说明：

  • procedure_paramter：与存储过程相比，不需要有in或者out或者inout声明
  • characteristic:表示存储过程的特性：如language SQL等
  • routine_body：表示存储过的的sql语句

创建函数示例

  1. DELIMITER $$
  2. CREATE FUNCTION func_employee_sal (empno INT(11))
  3.     RETURNS DOUBLE(10,2)
  4. COMMENT'查询某个雇员的工资'
  5. BEGIN
  6.     RETURN (SELECT sal
  7.         FROM t_employee
  8.         WHERE t_employee.empno=empno);
  9. END$$
  10. DELIMITER ;

3.2 查看并使用

查看函数：

  1. show function status查看状态信息
  2. show create function function_name查看定义信息

调用函数

select function_name ( [  parameter  [ ,  ...  ] ] )

3.3 修改删除函数

修改存储过程：

alter function function_name [sql语句]

删除存储过程：

drop function function_name;

3.4 自带基本函数

3.4.1 字符串类

  1. 查找字符集
  2. CHARSET(str); //返回字串字符集
  3.  
  4. 查询长度
  5. LENGTH (string ); //string长度  按照字节来统计的
  6. char_length(); //按照字符来统计的
  7.  
  8. 连接字串
  9. CONCAT (string2 [,... ]); //连接字串
  10. concat_ws(',','2021','04'); //是带连字符的字符串连接
  11.  
  12. 比较字符串
  13. STRCMP (string1 ,string2 ); //逐字符比较两字串大小,（实际是ascii码值比较）
  14.  
  15. 查找字符串
  16. find_in_set('mysql','order,mysql,sql'); //是在集合中查找字符串位置
  17. field('mysql','oracle','sql server','mysql'); //查找第一个字符串在后面跟随字符串中匹配的位置
  18. INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存在返回0，这和LOCATE的双参数形式相同，除非参数的顺序被颠倒
  19. LOCATE (substring , string [,start_position ] ) ;//同INSTR,但可指定开始位置，
  20. position(str1 in str); //position(substr IN str)是 LOCATE(substr,str)同义词
  21.  
  22. 截取字符串
  23. LEFT (string2 ,length ); //从string2中的左边起取length个字符
  24. right(str,num); //从右边指定位置截取字符串
  25. SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符, 注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1 
  26. mid(str,num,len); //和substring同义
  27. TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2); //去除指定位置的指定位置空格
  28. ltrim(str); //去除左边空格
  29. rtrim(str); //去除右边空格
  30.  
  31. 填充替换字符串
  32. insert(str,pos,len,newstr); //指定位置和长度替换
  33. replace(str,substr,newstr); //用新字符串替换原字符串中子字符串
  34. LPAD (string ,length ,pad ); //重复用pad加在string开头,直到字串长度为length
  35. RPAD (string ,length ,pad); //在string后用pad补充,直到长度为length
  36. REPEAT (string ,count ); //重复count次
  37. SPACE(count); //生成count个空格
  38.  
  39. 读取文件
  40. LOAD_FILE (file_name ); //从文件读取内容
  41.  
  42. 大小写转换
  43. UCASE (string2 ); //转换成大写
  44. upper(s);//转换为大写
  45. lower(s);//转换为小写
  46. lcase(s);//转换为小写

3.4.2 数学类

  1. ABS (number2 ); //绝对值
  2. BIN (decimal_number ); //十进制转二进制
  3. ceil(x);//向上取整
  4. CEILING (number2 ); //向上取整
  5. CONV(number2,from_base,to_base); //进制转换
  6. FLOOR (number2 ); //向下取整
  7. FORMAT (number,decimal_places ); //保留小数位数
  8. HEX (DecimalNumber ); //转十六进制
  9. 注：HEX()中可传入字符串，则返回其ASC-11码，如HEX('DEF')返回4142143，也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19
  10. LEAST (number , number2 [,..]); //求最小值
  11. MOD (numerator ,denominator ); //求余
  12. POWER (number ,power ); //求指数
  13. RAND(); //随机数 ，返回[0,1)之间的小数
  14. RAND([seed]);//当调用一个整数参数时，RAND()使用该随机数发生器种子值。每次在给定值种子生成，RAND()会产生一个可重复的一系列数字
  15. SQRT(number2); //开平方
  16. truncate(x,y); //对x进行小数点后y位的舍取
  17. ROUND (number [,decimals ]); //四舍五入,decimals为小数位数]

注：ROUND函数返回类型并非均为整数，如：

  1. (1)默认变为整形值
  2. mysql> select round(1.23);  
  3. +-------------+  
  4. | round(1.23) |  
  5. +-------------+  
  6. |           1 |  
  7. +-------------+  
  8. 1 row in set (0.00 sec)  
  9.  
  10. mysql> select round(1.56);  
  11. +-------------+  
  12. | round(1.56) |  
  13. +-------------+  
  14. |           2 |  
  15. +-------------+  
  16. 1 row in set (0.00 sec) 
  17.  
  18. 可以设定小数位数，返回浮点型数据
  19. mysql> select round(1.567,2);  
  20. +----------------+  
  21. | round(1.567,2) |  
  22. +----------------+  
  23. |           1.57 |  
  24. +----------------+  
  25. 1 row in set (0.00 sec) 
  26. SIGN (number2 ) //

3.4.3 日期时间类

  1. 获取时间
  2. NOW (); //当前时间  日期+时间
  3. SYSDATE();//取的是动态的实时时间  日期+时间
  4. localtime();//日期+时间
  5. localtimestamp();//日期+时间
  6. CURRENT_DATE ( ); //当前日期
  7. curdate(); //当前日期
  8. CURRENT_TIME ( ); //当前时间
  9. curtime(); //当前时间
  10. CURRENT_TIMESTAMP ( ); //当前时间戳
  11. FROM_UNIXTIME(毫秒值/1000,'%Y-%m-%d %T');//毫秒值转换成日期数据类型
  12. UNIX_TIMESTAMP(日期);//日期转秒值 若要毫秒需要乘以1000
  13.  
  14. 计算日期和时间函数：
  15. ADDTIME (date2 ,time_interval ); //将time_interval加到date2
  16. DATE (datetime ); //返回datetime的日期部分
  17. DATEDIFF(date1,date2);//比较两个日期的天数
  18. DATE_ADD (date2 , INTERVAL d_value d_type ); //在date2中加上日期或时间
  19. DATE_SUB (date2 , INTERVAL d_value d_type ); //在date2上减去一个时间
  20. DATE_FORMAT(date,'日期格式');//用于一不同格式显示日期、时间数据
  21. DAY (date); //返回日期的天
  22. DAYNAME (date ); //英文星期
  23. DAYOFWEEK (date ); //星期(1-7) ,1为星期天
  24. DAYOFMONTH(datetime) //月的第几天
  25. DAYOFYEAR (date ); //一年中的第几天
  26. EXTRACT (interval_name FROM date ) //从date中提取日期的指定部分
  27. FROM_DAYS(天数);//从TO_DAYS的天数中获取日期
  28. MAKEDATE (year ,day ) //给出年及年中的第几天,生成日期串
  29. MAKETIME (hour ,minute ,second ) //生成时间串
  30. MONTHNAME (date ) //英文月份名
  31. SEC_TO_TIME (seconds ) //秒数转成时间
  32. STR_TO_DATE (string ,format ) //字串转成时间,以format格式显示
  33. TO_DAYS(date);//返回从0年（公元1年）至当前日期总天数
  34. TIMEDIFF (datetime1 ,datetime2 ) //两个时间差
  35. TIME_TO_SEC (time ) //时间转秒数
  36. WEEK (date_time [,start_of_week ]) //第几周
  37. YEAR (datetime ) //年份
  38. HOUR(datetime) //小时
  39. LAST_DAY(date) //date的月的最后日期
  40. MICROSECOND(datetime) //微秒
  41. MONTH(datetime) //月
  42. MINUTE(datetime) //分返回符号,正负或0
  43.  
  44. 时间和时区转换
  45. DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式显示datetime
  46. CONVERT_TZ (datetime2 ,fromTZ ,toTZ ); //转换时区

3.4.4 系统信息函数

  1. 登陆后使用 \h可以查看mysql的帮助信息
  2. \s 获取当前MySQL实例信息
  3. version():获取版本号信息
  4. database():数据库名
  5. user()：用户名
  6. last_insert_id()：获取最近插入的id值
  7. password(str):对密码进行加密

3.4.5 窗口函数

窗口函数不能直接用于WHERE子句，这是因为窗口函数需要在数据分组后进行计算。因此，它们原则上只能在SELECT子句中使用。然而，可以通过使用子查询的方式，先对满足WHERE条件的数据进行筛选，然后再在这些过滤后的数据上应用窗口函数。

3.4.5.1 排序类

在mysql8.0中有相关的内置函数，而且考虑了各种排名问题：

  • row_number(): 同记录不同名，相当于行号，例如3000、2000、2000、1000排名后为1、2、3、4
  • rank(): 同记录同名，有跳级，例如3000、2000、2000、1000排名后为1、2、2、4
  • dense_rank(): 同薪同名，无跳级，例如3000、2000、2000、1000排名后为1、2、2、3
  • ntile(): 分桶排名，即首先按桶的个数分出第一二三桶，然后各桶内从1排名，实际不是很常用

另外这三个函数必须要要与其搭档over()配套使用，over()中的参数常见的有两个，分别是

  • partition by，按某字段切分
  • order by，与常规order by用法一致，也区分ASC(默认)和DESC，因为排名总得有个依据
3.4.5.2 获取下一行Lead

LEAD() 函数是一个窗口函数，用于获取指定列的下一行值。它的语法如下：

LEAD(expression, offset, default_value) OVER (PARTITION BY partition_expression ORDER BY sort_expression)

  • expression：要获取下一行值的列或表达式。
  • offset：可选参数，表示要获取的下一行相对于当前行的偏移量，默认为 1。
  • default_value：可选参数，在没有下一行时返回的默认值，默认为 NULL。
  • PARTITION BY partition_expression：可选子句，用于将结果集分区并在每个分区内进行计算。如果不指定该子句，则整个结果集被视为一个分区。
  • ORDER BY sort_expression：可选子句，用于对结果集进行排序。

LEAD() 函数在查询结果集中根据指定的排序条件获取下一行值。它可以用于比较相邻行之间的差异、计算行与下一行之间的差值等操作。

  1. SELECT 
  2.     column_name, LEAD(column_name) OVER (ORDER BY column_name) AS next_value
  3. FROM table_name;

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/从前慢现在也慢/article/detail/717508
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号