广东建设官方网站,wordpress编辑文章,网站站点是什么,wordpress 多域名 插件一、触发器
1.触发器简介
触发器#xff08;trigger#xff09;是一个特殊的存储过程#xff0c;它的执行不是由程序调用#xff0c;也不是手工启动#xff0c;而是由事件来触发#xff0c;比如当对一个表进行操作#xff08; insert#xff0c;delete#xff0c; u…一、触发器
1.触发器简介
触发器trigger是一个特殊的存储过程它的执行不是由程序调用也不是手工启动而是由事件来触发比如当对一个表进行操作 insertdelete update时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。
例如当学生表中增加了一个学生的信息时学生的总数就应该同时改变。因此可以针对学生表创建一个触发器每次增加一个学生记录时就执行一次学生总数的计算操作从而保证学生总数与记录数的一致性。
2.创建触发器
语法结构
1、创建只有一个执行语句的触发器
create trigger 触发器名 before|after 触发事件
on 表名 for each row
执行语句;2、创建有多个执行语句的触发器
create trigger 触发器名 before|after 触发事件
on 表名 for each row
begin执行语句列表
end;# 说明触发器名称 最多64个字符它和MySQL中其他对象的命名方式一样{ before | after } 触发器时机{ insert | update | delete } 触发的事件on 表名称 标识建立触发器的表名即在哪张表上建立触发器for each row 触发器的执行间隔通知触发器每隔一行执行一次动作而不是对整个表执行一次触发器程序体 要触发的SQL语句可用顺序判断循环等语句实现一般程序需要的逻辑功能
3.示例
1建库建表并插入数据
mysql create database mydb16_trigger;mysql use mydb16_trigger;mysql create table student1(id int unsigned auto_increment primary key not
null,name varchar(50));mysql insert into student1(name) values(jack);mysql create table student_total(total int);mysql insert into student_total values(1);2创建触发器student_insert_trigger并测试
mysql create trigger student_insert_trigger after insert on student1 for each
row update student_total set totaltotal1;mysql insert into student1(name) values(jenny);mysql select * from student_total;
-------
| total |
-------
| 2 |
-------
3查看触发器
1. 通过SHOW TRIGGERS语句查看
mysql show triggers\G2. 通过系统表triggers查看
mysql use information_schemamysql select * from triggers\Gmysql select * from triggers where trigger_namestudent_insert_trigger\G
4通过drop triggers语句删除触发器
mysql use d_trigger;mysql drop trigger student_insert_trigger;
4.NEW于OLD
MySQL 中定义了 NEW 和 OLD用来表示触发器的所在表中触发了触发器的那一行数据来引用触发器中发生变化的记录内容。 使用方法 NEW.columnName columnName为相应数据表某一列名
5.注意事项
MYSQL中触发器中不能对本表进行 insert ,update ,delete 操作以免递归循环触发尽量少使用触发器假设触发器触发每次执行1sinsert table 500条数据那么就需要触发500次触发器光是触发器执行的时间就花费了500s而insert 500条数据一共是1s那么这个insert的效率就非常低了。触发器是针对每一行的对增删改非常频繁的表上切记不要使用触发器因为它会非常消耗资源。
二、存储过程
1.概念
存储过程和函数是事先经过编译并存储在数据库中的一段sql语句集合调用存储过程可以简化应用开发人员的很多工作减少数据在数据库和应用服务器之间的传输对于提高数据处理的效率是有好处的。
简单的说存储过程就是一组SQL语句集功能强大可以实现一些比较复杂的逻辑功能类似于JAVA语言中的方法是SQL 语言层面的代码封装与重用。
存储过程和函数的区别
函数必须有返回值而存储过程没有。存储过程的参数可以是IN、OUT、INOUT类型函数的参数只能是IN
2.优点
存储过程只在创建时进行编译而SQL语句每执行一次就编译一次所以使用存储过程可以提高数据库执行速度简化复杂操作结合事务一起封装复用性好安全性高可指定存储过程的使用权
注意并发量少的情况下很少使用存储过程。并发量高的情况下为了提高效率用存储过程比较多。
3.存储过程创建和调用
1语法结构
# 1.定义
delimiter 自定义结束符号
create procedure 储存名([IN | OUT | INOUT]参数名 类型...)beginSQL语句end 自定义结束符号
delimiter ;# 2.调用
call 存储过程名(实参列表)# 注1自定义符号可以用除了分号的符号一般用$$ 或 //# 注2存储过程的参数形式IN 输入参数OUT 输出参数INOUT 输入输出参数
注意
在MySQL的存储过程中delimiter的作用是改变执行语句的分隔符默认情况下是分号 ;
但在编写存储过程、函数或其他SQL代码块时可能会遇到需要在一行或多行中编写包含分号的语句而默认的分号作为语句结束符会导致MySQL在遇到分号时立即执行语句而不是等待整个代码块输入完成后再执行。为了解决这个问题可以使用delimiter命令来更改默认的分隔符例如将分隔符更改为 // 或 $$ 这样MySQL就会将 // 或 $$ 之后的分号视为代码块的结束而不是单个语句的结束。
2示例
定义查询员工编号、姓名的存储过程
mysql delimiter $$mysql create procedure proc01() # 定义存储过程
begin select empno,ename from emp;
end $$mysql delimiter ; # 注意空格mysql call proc01(); # 调用存储过程
使用存储过程插入多条数据
mysql create table passwd(id int,pwds varchar(50)); # 建新表mysql delimiter $$mysql create procedure proc02()
begin declare i int default 1; # 定义变量while(i10000)do insert into passwd values(i, md5(i)); set ii1; end while;
end $$mysql delimiter ;mysql call proc02();
删除格式
mysql drop procedure proc01; # 不用加括号 4.变量
1局部变量
用户自定义在begin/end块中有效格式
# 声明变量
declare var_name type [default var_value];# 举例
declare nickname varchar(32);
示例
mysql delimiter $$# 定义局部变量
mysql create procedure proc03()
begin declare var_name01 varchar(20) default aaa; set var_name01 zhangsan; select var_name01;
end $$mysql delimiter ;mysql call proc03();
------------
| var_name01 |
------------
| zhangsan |
------------
2使用 SELECT..INTO 语句为变量赋值
基本语法如下
select col_name [...] into var_name[,...]
from table_name wehre condition# 注col_name 参数表示查询的字段名称var_name 参数是变量的名称table_name 参数指表的名称condition 参数指查询条件。# 注意当将查询结果赋值给变量时该查询语句的返回结果只能是单行单列。示例
mysql delimiter $$mysql create procedure proc04()
begin declare var_name02 varchar(20) ; select name into var_name02 from student1 where id1003; select var_name02;
end $$mysql delimiter ;mysql call proc04();
------------
| var_name02 |
------------
| 李四 |
------------
3用户变量
用户自定义当前会话连接有效。类比java的成员变量格式
var_name# 不需要提前声明使用即声明即无declare子句
示例
mysql delimiter $$mysql create procedure proc05()
beginset var_name03 openlab;
end $$mysql delimiter ;mysql call proc05() ;mysql select var_name01 ;
-------------
| var_name01 |
-------------
| openlab |
-------------
4系统变量
系统变量又分为全局变量与会话变量在MYSQL启动的时候由服务器自动将它们初始化为默认值这些默认值可以通过更改my.ini这个文件来更改。
会话变量在每次建立一个新的连接的时候由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份。来做为会话变量。也就是说如果在建立会话以后没有手动更改过会话变量与全局变量的值那所有这些变量的值都是一样的。
全局变量与会话变量的区别就在于对全局变量的修改会影响到整个服务器但是对会话变量的修改只会影响到当前的会话也就是当前的数据库连接。
有些系统变量的值是可以利用语句来动态进行更改的但是有些系统变量的值却是只读的对于那些可以更改的系统变量我们可以利用set语句进行更改。
系统变量-全局变量由系统提供在整个数据库有效
# 语法
global.var_name
示例
# 查看全局变量
show global variables;# 查看某全局变量
select global.auto_increment_increment;# 修改全局变量的值
set global sort_buffer_size 40000;
set global.sort_buffer_size 40000;# 注意
global 与 global 等价都是系统全局变量
SET global 用于更改全局系统变量的值影响所有客户端连接
SET GLOBAL一般用于查看全局变量的当前值
系统变量-会话变量由系统提供当前会话连接有效
# 语法
session.var_name
示例
# 查看会话变量
show session variables;# 查看某会话变量
select session.auto_increment_increment;# 修改会话变量的值
set session sort_buffer_size 50000;
set session.sort_buffer_size 50000;
5.mysql变量种类总结
用户变量以””开始形式为”变量名”。用户变量跟mysql客户端是绑定的设置的变量只对当前用户使用的客户端生效全局变量定义时以如下两种形式出现set GLOBAL 变量名 或者 set global.变量名对所有客户端生效。只有具有super权限才可以设置全局变量会话变量只对连接的客户端有效。局部变量作用范围在begin到end语句块之间。在该语句块里设置的变量。declare语句专门用于定义局部变量。
可以使用set语句是设置不同类型的变量包括会话变量和全局变量
6.参数传递
1IN
表示传入的参数 可以传入数值或者变量即使传入变量并不会更改变量的值可以内部更改仅仅作用在函数范围内。
mysql delimiter $$mysql create procedure proc06(in a int)
begin declare i int default 1;while(ia) do insert into passwd values(i,md5(i)); set ii1; end while;
end $$
mysql delimiter ;mysql set num100; # 定义用户变量mysql select num; # 查看
------
| num |
------
| 100 |
------mysql call proc06(num); # 执行过程传递变量值到过程中mysql select * from passwd;
2OUT
表示从存储过程内部传值给调用者,in的反向传递
mysql delimiter $$mysql create procedure proc07(out cnt1 int)
begin select count(*) into cnt1from passwd;
end $$mysql delimiter ;mysql call proc07(cnt2);mysql select cnt2;
-------
| cnt2 |
-------
| 10099 |
-------
3示例in和out的综合使用
例1统计指定部门的员工数
mysql use mydb_openlab;
Database changed
mysql select * from dept;
-----------------
| dep1 | dept_name |
-----------------
| 101 | 财务 |
| 102 | 销售 |
| 103 | 运维 |
| 104 | 行政 |
-----------------
4 rows in set (0.00 sec)mysql select * from emp_new;
---------------------------------------------------
| sid | name | age | worktime_start | incoming | dept2 |
---------------------------------------------------
| 1789 | 张三 | 35 | 1980-01-01 | 4000 | 101 |
| 1674 | 李四 | 32 | 1983-04-01 | 3500 | 101 |
| 1776 | 王五 | 24 | 1990-07-01 | 2000 | 101 |
| 1568 | 赵六 | 57 | 1970-10-11 | 7500 | 102 |
| 1564 | 荣七 | 64 | 1963-10-11 | 8500 | 102 |
| 1879 | 牛八 | 55 | 1971-10-20 | 7300 | 103 |
---------------------------------------------------
6 rows in set (0.00 sec)mysql delimiter //
mysql create procedure proc08(in d_num int,out cnt1 int)- begin- select count(*) into cnt1 from emp_new where dept2d_num;- end //
Query OK, 0 rows affected (0.01 sec)mysql delimiter ;
mysql call proc08(101,cnt2);
Query OK, 1 row affected (0.01 sec)mysql select cnt2;
-------
| cnt2 |
-------
| 3 |
-------
1 row in set (0.00 sec)
例2统计指定部门工资超过例如5000的总人数
mysql delimiter //
mysql create procedure proc09(in d_num int, in wages int, out cnt1 int)- begin- select count(*) into cnt1 from emp_new where dept2d_num and incomingwages;- end //
Query OK, 0 rows affected (0.01 sec)mysql delimiter ;
mysql call proc09(102,5000,wa);
Query OK, 1 row affected (0.00 sec)mysql select wa;
------
| wa |
------
| 2 |
------
1 row in set (0.00 sec)
4INOUT
mysql delimiter $$
mysql create procedure proc10(inout p1 int)
begin if (p1 is not null) then set p1p11; elseselect 100 into p1; end if;
end$$mysql delimiter ;
mysql select h;
------
| h |
------
| NULL |
------mysql call proc10(h);mysql select h;
------
| h |
------
| 100 |
------
5总结
in 输入参数意思说你的参数要传到存过过程的过程里面去在存储过程中修改该参数的值不能被返回out 输出参数:该值可在存储过程内部被改变并向外输出inout 输入输出参数既能输入一个值又能传出来一个值
三、流程控制
1.if判断
IF语句包含多个条件判断根据结果为TRUE、FALSE执行语句与编程语言中的if、else if、else语法类似其语法格式如下
if search_condition_1 then statement_list_1[elseif search_condition_2 then statement_list_2] ...[else statement_list_n]
end if
例1输入学生的成绩来判断成绩的级别
/*score 60 :不及格score 60 , score 80 :及格score 80 , score 90 :良好score 90 , score 100 :优秀score 100 : 成绩错误
*/mysql delimiter $$
mysql create procedure proc11_if(in score int)
beginif score 60thenselect 不及格;elseif score 80thenselect 及格 ;elseif score 80 and score 90thenselect 良好;elseif score 90 and score 100thenselect 优秀;elseselect 成绩错误;end if;
end $$mysql delimiter ;mysql call proc11_if(120);mysql call proc11_if(86);
例2输入员工的名字判断工资的情况
mysql use mydb_openlab;mysql select * from emp_new;
---------------------------------------------------
| sid | name | age | worktime_start | incoming | dept2 |
---------------------------------------------------
| 1789 | 张三 | 35 | 1980-01-01 | 4000 | 101 |
| 1674 | 李四 | 32 | 1983-04-01 | 3500 | 101 |
| 1776 | 王五 | 24 | 1990-07-01 | 2000 | 101 |
| 1568 | 赵六 | 57 | 1970-10-11 | 7500 | 102 |
| 1564 | 荣七 | 64 | 1963-10-11 | 8500 | 102 |
| 1879 | 牛八 | 55 | 1971-10-20 | 7300 | 103 |
---------------------------------------------------mysql delimiter $$
mysql create procedure proc12_if(in in_ename varchar(50))
begindeclare result varchar(20);declare var_sal int;select incoming into var_sal from emp_new where name in_ename;if var_sal 3000then set result 试用薪资;elseif var_sal8000then set result 转正薪资;elseset result 元老薪资;end if;select result;
end$$mysql delimiter ;mysql call proc12_if(赵六);mysql call proc12_if(荣八);
2.case判断
case是另一个条件判断的语句类似于编程语言中的switch语法其语法格式如下
# 语法一类比java的switch
case case_valuewhen when_value then statement_list[when when_value then statement_list] ...[else statement_list]
end case# 语法二
casewhen search_condition then statement_list[when search_condition then statement_list] ...[else statement_list]
end case
例1
mysql delimiter $$
mysql create procedure proc13_case(in pay_type int)
begincase pay_typewhen 1 then select 微信支付 ;when 2 then select 支付宝支付 ;when 3 then select 银行卡支付;else select 其他方式支付;end case ;
end $$mysql delimiter ;mysql call proc13_case(2);mysql call proc13_case(4);例2
mysql delimiter $$
mysql create procedure proc14_case(in score int)
begincasewhen score 60 then select 不及格;when score 80 then select 及格 ;when score 80 and score 90 then select 良好;when score 90 and score 100 then select 优秀;else select 成绩错误;end case;
end $$mysql delimiter;mysql call proc14_case(88);mysql call proc14_case(120);
3.循环
1概述
循环是一段在程序中只出现一次,但可能会连续运行多次的代码。循环中的代码会运行特定的次数,或者是运行到特定条件成立时结束循环
2循环分类
whilerepeatloop
3循环控制
leave 类似于 break跳出结束当前所在的循环iterate类似于 continue继续结束本次循环继续下一次 4while循环格式
[标签:]while 循环条件 do循环体;
end while[标签];
示例
mysql create procedure proc16_while2(in insertcount int)
begindeclare i int default 1;label:while iinsertcount doinsert into user(uid,username,password) values(i,concat(user-
,i),123456);if i5thenleave label; # 结束当前循环end if;set ii1;end while label;
end $$
5repeat循环格式
[标签:]repeat循环体;until 条件表达式
end repeat [标签];
示例
mysql create procedure proc18_repeat(in insertCount int)
begindeclare i int default 1;label:repeatinsert into user(uid, username, password) values(i,concat(user-
,i),123456);set i i 1;until i insertCountend repeat label;select 循环结束;
end $$6loop循环格式
[标签:] loop循环体;if 条件表达式thenleave [标签];end if;
end loop;
示例
mysql create procedure proc19_loop(in insertCount int)
begindeclare i int default 1;label:loopinsert into user(uid, username, password) values(i,concat(user-
,i),123456);set i i 1;if i 5thenleave label;end if;end loop label;select 循环结束;
end $$
四、存储函数
1.概念
MySQL存储函数自定义函数一般用于计算和返回一个值可以将经常需要使用的计算或功能写成一个函数。函数和存储过程类似。
存储函数与存储过程的区别
存储函数有且只有一个返回值而存储过程可以有多个返回值也可以没有返回值。存储函数只能有输入参数而且不能带in, 而存储过程可以有多in,out,inout参数。存储过程中的语句功能更强大存储过程可以实现很复杂的业务逻辑而函数有很多限制如不能在函数中使用insert,update,delete,create等语句。存储函数只完成查询的工作可接受输入参数并返回一个结果也就是函数实现的功能针对性比较强。存储过程可以调用存储函数。但函数不能调用存储过程。存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用。
2.存储函数的创建和调用
创建存储函数在MySQL中创建存储函数使用create function关键字其基本形式如下
create function func_name ([param_name type[,...]])
returns type
[characteristic ...]
beginroutine_body
END;# 参数说明
1func_name 存储函数的名称。
2param_name type可选项指定存储函数的参数。type参数用于指定存储函数的参数类型该类型可
以是MySQL数据库中所有支持的类型。
3returns type指定返回值的类型。
4characteristic可选项指定存储函数的特性。
5routine_bodySQL代码内容。
调用存储函数
在MySQL中存储函数的使用方法与MySQL内部函数的使用方法基本相同用户自定义的存储函数与MySQL内部函数性质相同。区别在于存储函数是用户自定义的。而内部函数由MySQL自带。其语法结构如下
select func_name([parameter[,…]]);
例1无参数存储函数
mysql set global log_bin_trust_function_creatorsTRUE; # 信任子程序的创建者否则可能
报错,执行一次即可# 创建存储函数-没有输输入参数mysql delimiter $$
mysql create function myfunc1_emp()
returns int
begindeclare cnt int default 0;select count(*) into cnt from emp_new;return cnt;
end $$mysql delimiter ;# 调用存储函数
mysql select myfunc1_emp();
---------------
| myfunc1_emp() |
---------------
| 6 |
---------------
例2有参数存储函数
mysql select * from emp_new;
---------------------------------------------------
| sid | name | age | worktime_start | incoming | dept2 |
---------------------------------------------------
| 1789 | 张三 | 35 | 1980-01-01 | 4000 | 101 |
| 1674 | 李四 | 32 | 1983-04-01 | 3500 | 101 |
| 1776 | 王五 | 24 | 1990-07-01 | 2000 | 101 |
| 1568 | 赵六 | 57 | 1970-10-11 | 7500 | 102 |
| 1564 | 荣七 | 64 | 1963-10-11 | 8500 | 102 |
| 1879 | 牛八 | 55 | 1971-10-20 | 7300 | 103 |
---------------------------------------------------mysql delimiter $$
mysql create function myfunc2_emp(in_sid int)
returns varchar(50)
begindeclare out_name varchar(50);select name into out_name from emp_new where sid in_sid;return out_name;
end $$mysql delimiter ;
mysql select myfunc2_emp(1776);
-------------------
| myfunc2_emp(1776) |
-------------------
| 王五 |
-------------------
3.删除存储函数
MySQL中使用drop function语句来删除存储函数。
示例删除存储函数
mysql drop function if exists myfunc1_emp;mysql drop function if exists myfunc2_emp;
