学习之MySQL进阶

一、存储过程

1.存储过程的创建与调用
语法结构：

CREATE
    [DEFINER = { user | CURRENT_USER }]
　PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
 
proc_parameter:
    [ IN | OUT | INOUT ] param_name type
 
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
 
routine_body:
　　Valid SQL routine statement
 
[begin_label:] BEGIN
　　[statement_list]
　　　　……
END [end_label]

• 无参的存储过程

创建：

create procedure pro_name()
begin #存储过程开始符号
  select * from table; #存储过程封装的内容（begin和end之间），可使用控制语句
end;   #存储过程结束符号

调用：

call pro_name();

• in输入参数的存储过程

创建：

create procedure pro_name(in input int)
begin
declare a int; #声明变量
set @a = input;  #变量赋值
select @a;
end;
#注意局部变量与全局变量的区别
#存储过程体中声明的变量为局部变量
#存储过程外声明的变量为全局变量

调用：

call pro_name(传入值); #传入值可以是变量或是具体值

• out输出参数的存储过程

创建：

create procedure pro_name(out output int)
begin
declare a int;
select @a;
set @a = 2;
select @a;
end;

#以上运行有两个结果，结果1返回的为null,结果2返回的为2
#结果1是因为out为输出参数，没有接受输入的参数，因此为null
#结果2是因为调用了存储过程，因此变量被赋值

调用：

call pro_name(@a);#传出值只能为变量

• inout输入参数的存储过程

创建：

create procedure pro_name(inout output int)
begin
declare a int;
select @a;
set @a = 2;
select @a;
end;

#以上代码有两个运行结果，结果1为1，结果2为2
#结果1为传入的值
#结果2为调用了存储过程，输出参数，改变了的变量

调用：

set @a = 1
call pro_name(@a) #该值只能时变量

2.删除存储过程

drop procedure pro_name; #pro_name为存储过程名
drop procedure if exists pro_name; #若该存储过程存在，则删除存储过程

3.存储过程中的控制语句

• 条件语句

if-then-else

if 条件 then ...
else ...
end if;

case

case 变量 when 值 then ...
          when 值 then ...
          ...
          else ...
end case;

• 循环语句

while…end while

while 条件 do
... --循环体
end while;

repeat…end repeat

repeat
... 
until 条件
end repeat;

loop…end loop

loop_xh:loop
...
if 条件 then
leave loop_xh;
end if;
end loop;

二、触发器

1.触发器的创建与调用
语法结构：

CREATE
    [DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
　　[trigger_order]
trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

创建：

create trigger 触发器名 before|after 触发事件 on table_name
for each row
begin
...
end;
#before:触发事件之前，after:触发事件之后
#触发事件：insert，update，delete

示例1：

课程表：

教师表：

触发器功能：
当在课程表中插入数据后，向教师表中插入数据

drop TRIGGER if exists test;
create trigger test after insert on course
for each row
begin
  insert into teacher VALUES(04,'赵六');
end;

insert into course VALUES(05,'xd',03);

结果：
教师表：

课程表：

由于未对数字作引号处理，故出现如上所示结果。

示例2：

成绩表：

触发器功能：
判断输入的成绩，等于0改为60，大于100改为60

drop TRIGGER if exists test;
create trigger test BEFORE insert on score
for each row
begin
  if new.score = 0 then
	   set new.score = 60;
  elseif new.score > 100 then
	   set new.score = 60;
	 end if;
end;

insert into score VALUES('03','03',120);

结果：
成绩表：

2.NEW与OLD

触发事件为INSERT

NEW：将要或者已经插入的新数据；

触发事件为UPDATE

OLD ：将要或已经被修改的原数据；
NEW：将要或已经修改为的新数据；

触发事件为DELETE

OLD：将要或已经被删除的原数据；

3.删除触发器

drop trigger 触发器名;

三、自定义函数

1.函数的创建与调用
无参函数
创建：

create function func_name() returns data_type
begin
...
end;
#func_name为函数名
#data_type为数据类型

调用：

select func_name();

传参函数
创建：

create function func_name(字段 数据类型) returns data_type
begin
...
end;

调用：

select func_name(传参);

2.删除自定义函数

drop function 函数名;

3.查看所有函数

show function status;