数据库触发器与函数的使用
触发器
1. 基本简介
-
触发器(TRIGGER)是由事件来触发某一个操作(包括INSERT、UPDATE、DELETE语句),当数据库系统执行这些事件时,会激活触发其执行相应的操作
-
当对某表做增删改操作时,可以使用触发器自定义关联行为,注意使用触发器时也需要使用
delimiter
将sql的结束符修改了先 -
可以理解为是编程中的事件一样
-
语法
-
create trigger 触发器名称 触发时间(after/befor) 触发事件(insert/update/delete) -- 当填写一种时,其它操作不会激活触发器 on 监视的表(即在那一张表中建立触发器) for each roe (行级触发器) begin sql语句(希望改变的数据,可写多条语句,每条语句必须使用;结尾) -- 所以我们需要将sql语句默认的结束符给修改掉 end; -- 触发器一般不对同一张表进行操作
-
2. 基本使用
-
一旦拥有触发器的数据表执行插入、修改和删除操作,就会自动触发相应的事件
-
delimiter $$ -- 修改sql语句的结束符 CREATE TRIGGER tr AFTER INSERT ON students -- 表示当students插入一条数据后激活触发器 FOR EACH ROW -- 行级触发器,修改一行触发一次 BEGIN -- ---------------------触发器要执行的操作-------------------------- INSERT INTO grade VALUES(0,"students表中的触发器") -- 激活触发其后,向grade插入行数据 END$$ delimiter ; -- 恢复sql语句的结束符 -- -----------------辅助语句------------------ INSERT INTO students VALUES(0,"wangwu",2); SELECT * FROM students;
3. 查看触发器 show triggers
4. 删除触发器 drop trigger 触发器名称
函数
函数的基本了解
MySQL数据库提供的内部函数,可以帮助用户更加方便地处理表中的数据
函数的分类:
数值型函数、字符串型函数、日期和时间函数、集合函数、流程判断函数等
1.常用的数值型函数
-
函数名称 作用 RAND 取随机数,可设置参数种子 ABS 求x的绝对值 SIGN 求x的正负符号 SQRT 求X的平方根 POW 求x的y次方 MOD 取余,求N除以M的模 LOG 求以B为底X的对数 CEIL,FLOOR 向上,向下取整 ROUND 对X的第D位四舍五入 TRUNCATE 保留X小数点后D位 PI 取π的值 DEGREES,RADIANS 弧度,角度间的转换 SIN,COS,TAN 正弦,余弦,正切 ASIN,ACOS,ATAN,COT 反正弦,反余弦,反正切
2.字符串函数
-
函数名称 作用 LENGTH 返回字符串长度 CONCAT 合并字符串函数,参数可以是一个或多个 INSERT 替换字符串函数设 LOWER 将字符串中的字母转换成小写 UPPER 将字符串中的字母转换成大写 LEFT 从左侧截取,返回字符串左边的若干个字符 RIGHT 从右侧截取,返回字符串右边的若干个字符 TRIM 删除字符前后空格 REPLACE 字符串的替换 SUBSTRING 截取字符串,从指定位置开始的指定长度字符串 REVERSE 反转字符串
3.日期和时间函数
-
函数名称 作用 CURDATE和CURRENT_DATE 两个函数的作用相同,返回当前系统的日期值 CURTIME和 CURRENT_TIME 两个函数的作用相同,返回当前系统的时间值 NOW 和 SYSDATE 两个函数的作用相同,返回当前系统的日期和时间值 UNIX TIMESTAMP 获取UNIX时间戳函数,返回一个以UNIX时间戳为基础的无符号整数 FROM UNIXTIME 将UNIX时间戳转换为时间格式,与UNIX_TIMESTAMP互为反函数 MONTH 获取指定日期中的月份 MONTHNAME 获取指定日期中的月份的英文名称 DAYNAME 获取指定日期对应的星期几的英文名称 DAYOFWEEK 获取指定日期对应的一周的索引位置值 WEEK 获取指定日期是一年中的第几周,返回值的范围是0~52或1~53 DAYOFYEAR 获取指定日期是一年中的第几天,返回值范围是1~366 DAYOFMONTH 获取指定日期是一个月中的第几天,返回值范围是1~31 YEAR 获取年份,返回值是 1970~2069 TIME TO SEC 将时间参数转换为秒数 SEC_ TO_TIME 将秒数转换为时间,与TIME TO SEC互为反函数 DATE ADD 和 ADDDATE 两个函数的功能相同,都是向日期中添加指定的时间间隔 DATE_SUB 和 SUBDATE 两个函数的功能相同,都是从日期中减去指定的时间间隔 ADDTIME 时间加法运算,在原始时间上添加指定的时间 SUBTIME 时间减法运算,在原始时间上减去指定的时间 DATEDIFF 获取两个日期之间的间隔,返回参数1减去参数2的值 DATE_FORMAT 格式化指定的日期,根据参数返回指定格式的值 WEEKDAY 获取指定日期在一周内的对应的工作日索引
4.常用的聚合函数
-
函数名称 作用 MAX 查询指定列的数据的最大值 MIN 查询指定列的数据的最小值 COUNT 统计查询结果的行数 SUM 求和,返回指定列的数据的总和 AVG 求平均值,返回指定列的数据的平均值
5.常用的流程判断函数
-
函数名称 作用 F 根据判断条件的结果为TRUE或FALSE,返回第一个值或第二个值,也可以进行嵌套使用。 例 如:IF(value, valuel, value2),如果 value的值为TRUE,则返回 valuel;否则,返 回 value2 IFNULL 接受两个参数,如果第一个参数不为 NULL,返回第一个参数;如果第一个参数是 NULL,则 返回第二个参数。例如:IFNULL(valuel, value2),如果 valuel 不为NULL,返 回 value1;否 则,返回value2 NULLIF 接受两个参数,如果第一个参数等于第二个参数,则返回 NULL;否则,返回第一个参 数。例 如:NULLIF(valuel, value2),如果 valuel= value2,则返回NULL;否则,返回 valuel CASE WHEN 进行多重判断。例如:CASE WHEN 条件1THEN 结果1WHEN 条件2THEN 结果 2-ELSE [结果 N] END,如果条件1为TRUE,返回结果1……如果前面所有条件都不成立,则返回 结果N
函数的基本使用(常用)
自定义函数
语法格式:
-
create function 函数名([参数,...]) returns 类型 函数体 -- 参数:中有名称和类型,不能指定输入输出 -- 类型:声明函数的返回值类型 -- 函数体:由SQL语句和流程语句组成,必须包含returns语句 create function 函数名([参数,...]) returns 类型 begin -- sql语句或流程控制语句 return -- 返回值,必须得有 end;