MySQL学习笔记-进阶部分

//定义名称为proc_add的存储过程，想该存储过程传入两个int类型的变量a、b
create procedure proc_add (in a int,in b int)
	begin
		//declare申明局部变量c
		declare c int default 0;
		set c = a+b;
		select c as 'result';
	end;

set @x=0,@y=3;
set @color:='red',@size:='small';
select @x,@y,@color,@size;

select @count:=count(*) from Student;

//查看指定变量
select @@var_name;
select @@session.var_name;
//查看满足条件的部分变量
show session variables like '%var%';
//查看当前会话所有变量
show session variables;
show variables;

select @@autocommit;

set session var_name = value;
set @@session.var_name = value;
set var_name = value;

set session autocommit=off;
select @@session.autucommit;

select @@global.var_name;
show global variables like '%var%';

show global variables;
show global variables like '%char%'

//set 命令设置变量时若不指定 global、session、local，默认使用session。
set global var_name = value;
set @@global.var_name = value;

1、十六进制值的默认类型是字符串，如果想要确保该值作为数字处理，可使用cast()函数。
如果要将一个字符串或数字转换成十六进制格式的字符串，可以用hex()函数。

语法格式：
if(条件,结果1,结果2);

set @a='';
set @b = if(@a='','nothing',@a);
select @b;

//search_condition：表示搜索表达式条件，如果值为真，则执行相应的SQL语句列表
//如果没有search_condition匹配，else子句被执行。
//statement_list 可包含一个或多个语句。
if search_condition then statement_list
	[elseif search_condition then statement_list]...
	[else statement_list]
end if

语法1：

case 字段名称
　　when e1
　　then v1
　　when e2
　　then e2
　　...
　　else vn
end [as 字段别名]
　　case表示函数开始，end表示函数结束。如果e1成立，则返回v1,如果e2成立，则返回v2，当全部不成立则返回vn，而当有一个成立之后，后面的就不执行了。

select case 
　　when 1 > 0
　　then '1 > 0'
　　when 2 > 0
　　then '2 > 0'
　　else '3 > 0'
　　end
->1 > 0

语法2：

case expr 
　　when e1 then v1
　　when e1 then v1
　　...
　　else vn
end [as 字段别名]
　　如果表达式expr的值等于e1，返回v1；如果等于e2,则返回e2。否则返回vn。

select cno,case cno
	 when '3011' then '线性代数'
    when '3012' then '计算机原理'
    when '3013' then 'Java语言'
    when '3014' then '物理学'
	 when '3015' then '会计学'
    when '3016' then '商务英语'
    when '3017' then 'C语言'
    when '3018' then '世界地理'
    when '3019' then '高等数学'
    else '待更新'
    end as '课程名称'
from Students.StudentCourse

第一种语法：

case 字段名
	when value1 then statement_list
	[when value1 then statement_list]...
	[else statement_list]
end case

第二种语法：

case
	when search_condition then statement_list
	[when search_condition then statement_list]...
	[else statement_list]
end case

语法格式：
[begin_lable:] while condition do
    statement_list
end while [end_lable];

set @i = 1;
while @i 
    if @i % 2  0 then
        select @i;
    end if;
    set @i = @i + 1;
end while;

语法格式：
[begin_lable:]  repeat
    statement_list
until expression
end repeat [end_lable];

语法格式：
[begin_lable:] loop
    statements
end loop [end_lable];

//创建名称为Pro的存储过程
delimiter $$
create procedure pro()
begin
	declare num int default 1;
    labe11:loop
		if num < 6 then
			select num;
			set num= num+1;
			iterate labe11;//循环迭代labe11的内容
        end if;
	leave labe11;
    end loop labe11;
end$$ delimiter

1、iterate是重新启动循化的意思，使用在循 loop、 repeate 和 while语句内，用法leave label。类比Java的continue。

label 是标签 可以用在表示一个循环体。

2、leave 语句用于退出循环。类比Java的break。

在SQL中还有return关键字，但只用于函数，存储过程是不能使用的。

语法格式：
create function function_name([func_parameter[,...]])
returns type
[characteristic ...] 
begin
	//函数实现的语句
	declare '变量名' '变量类型' default '变量默认值';
	set '参数设置'; 
	'SQL语句';
end

（1）function_name：自定义函数的名称。
（2）func_parameter：自定义函数的参数列表。这些参数都是输入参数，运算结果通过 returns 语句返回，并且该语句只能返回一个结果。
（3）returns type：指定返回值的类型，可以是字符串、也可以是整数、还可以是其他类型。
（4）begin 和 end ：分别标记 SQL 代码的开始和结束。

create function Sayhello(name varchar(50))
return varchar(100)
begin
	return concat(name,'说：很高兴认识大家');
end	

create function Sayhello(name varchar(50))
return varchar(100)
begin
	return concat(name,'说：很高兴认识大家');
end	

创建这个函数时，会报错“Error 1418: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe 

问题原因：创建自定义函数时，函数中没有包含 deterministic、nosql、reads sql语句，即没有涉及修改数据时，会报这个错误

解决方法：
（1）将log_bin_trust_function_creators设置为1，默认值是0，开启了log-bin日志，关闭后就正常创建自定义函数

语法格式：
select fuc_name(参数列表);

select Sayhello('jimimy') as '打招呼';

-- 查看自定义函数
show create function 函数名;

show create function Sayhello;

-- 删除自定义函数
drop function 函数名;

drop function Sayhello;

语法格式：
create procedure proc_name([proc_parameter[,...]])
[characteristic...] routine_body

上述语法说明如下：
（1）proc_name：存储过程的名称。

（2）proc_parameter：参数列表，如果没有参数，就使用一个空看书列表()。多个参数用逗号隔开。参数列表中每个参数都有输入、输出类型、参数名称、参数类型三部分组成。语法如下：
[in | out | inout] param_name type
in 表示 输入参数，out表示输出参数，inout表示既可以是输入也可以是输出。
param_name 和 type 分别表示参数名称和参数类型。

（3）routine_body：sql代码的内容，可以用begin...end来标识SQL代码的开始和结束。
（4）characteristic：指定存储过程的特性，包括多个取值，如下说明

delimiter $$
create procedure show_cno_grade(Son int,Cno varchar(10))
reads sql data
comment '查询学生课程的成绩'
begin
	select * from Students.StudentCourse where students.StudentCourse.Sno=Sno and students.StudentCourse.Cno= Cno;
end
delimiter ;

//获取学号：3，课程号：3012的成绩
call show_cno_grade(3,3012);

语法格式：
show create {procedure | function } sp_name;

sp_name：储存过程的名称

语法格式：
show create {procedure | function } status [like 'pattren'];

pattren:用来匹配存储过程和自定义函数名称。

语法格式：
select * from information_schema.routines where routine_name = 'sp_name';

语法格式：
/使用if exists防止因不存在的存储过程而出错，给定了该子句时，将为每个不存在的存储过程生成NOTE.
drop procedure [if exists] sp_name;

语法格式：
alter procedure sp_name [characteristic...];

alter procedure show_cno_grade reads sql data comment '查询学生课程的成绩-1';

select specific_name,sql_data_access,routine_comment from information_schema.routines where routine_name = 'show_cno_grade';

语法格式：
declare cursor_name cursor for select_statement;

cursor_name：游标名称。
select_statement：select 语句内容。

语法格式：
fetch cursor_name into var_name[, var_name...];

cursor_name：游标名称。
var_name：表示将游标中的select 语句查询出来的信息存入到该参数中。var_name参数必须在声明光标之前就可以定义好。

delimiter $$
drop procedure if exists proc_test $$

create procedure proc_test(in date_day datetime)
begin
	declare done int default 0;
	declare _prono varchar(10) default '';
	declare _proname varchar(50) default '';
	declare mycursor cursor for select proNo,proName from product;
	-- done用来跟踪是否有数据（not found），将值设为1表示找不到数据
	declare continue handler for not found set done=1;
	
	-- if语句根据date_day 参数把数据取出来，并放在result表中。否则打开光标，通过repeat循环从光标中取数据，将数据合并起来放到result表中。
	if date_day then
		create table result (select concat_ws(',', proNo, proName) from 
		product where datediff(proPubDate,date_day)=0);
	else
		open mycursor;
		repeat
			fetch mycuror into _prono,_proname;
			if not done then
				create table result (select concat_ws('_',result, _prono, 			_proname));
			end if;
		until done end repeat;
		close mycursor;
	end if;
	select * from result;
end $$

delimiter ;

语法格式：
create trigger trigger_name
{before | after}
{insert | update | delete}
on tabl_name
for each row trigger_stmt

上述语法说明：
（1）trigger_name：触发器名称。
（2）before | after：触发程序的动作时间，在触发事件之前还是之后触发语句。
（3）insert | update | delete：触发条件。
（4）for each row ：表示任何一条记录上的操作满足触发事件都会触发该触发器。
（5）trigger_stmt：触发器被触发后执行的语句。

-- 创建触发器
-- delimiter $$
-- create trigger trigger_deleteForStudent after delete 
-- on Students.Student for each row
-- begin
-- 	insert into Students.student_log(content,create_time) values(concat('将id为：',old.Sname,'已删除'),now());
-- end$$
-- delimiter ;

delimiter $$
create trigger trigger_deleteForStudent after delete 
on Students.Student for each row
begin
	-- select Sno from Students.Student into @p;
    select concat_ws('_',Sno,Sname) from Students.Student  order by Sno desc limit 1 into @p;
	call insertStudent('已删除','男',0,'已删除',0,'已删除');
end$$
delimiter ;

报错1：创建这个触发器时会报错：‘Error Code: 1415. Not allowed to return a result set from a trigger’，

原因：从MySQL5开始不支持触发器返回结果集。

解决方法：在结果集后加上 into @p，需要用一个变量接收结果集输出;

select concat_ws('_',Sno,Sname) from Students.Student  order by Sno desc limit 1 into @p;

报错2：Error Code: 1442. Can't update table 'student' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

原因：存储的函数或触发器不能修改已被调用该函数或触发器的语句（用于读取或写入）使用的表。

解决办法：student表上的触发器里 不能再更新（insert、update、delete）student表，只能操作其他表。

-- 查看触发器(所有)
show triggers;
-- 查看触发器(按触发器名称)
select * from information_schema.triggers where trigger_name='trigger_deleteForStudent';

-- 删除触发器
-- drop trigger if exists trigger_deleteForStudent;

-- 列出当前用户对当前目录的所有模式中所有表的权限
-- show grants;

语法格式如下：
declare condition_name condition for 错误码(condition_value)

condition_name：参数表示条件的名称；
condition_value：参数表示条件的类型；
sqlstate_value 参数和 mysql_error_code 参数都可以表示 MySQL 的错误。
sqlstate_value：表示长度为 5 的字符串类型错误代码，
mysql_error_code：表示数值类型错误代码。例如 ERROR 1146(42S02) 中，

下面定义“error 1089 (HU123)”这个错误，名称为 can_not_find。 可以用两种不同的方法来定义，代码如下：

//方法一：使用sqlstate_value
declare can_not_find condition for sqlstate 'HU123';

//方法二：使用 mysql_error_code
declare can_not_find condition for 1089 ;

基本语法如下：
declare handler_type handler for condition_value[...] sp_statement

handler_type：continue | exit | undo
condition_value：
sqlstate [value] sqlstate_value | condition_name | sqlwarning | not found | sqlexception | mysql_error_code

其中，handler_type 参数指明错误的处理方式，该参数有 3 个取值。这 3 个取值分别是 continue、exit 和 undo。
（1）continue 表示遇到错误不进行处理，继续向下执行；
（2）exit 表示遇到错误后马上退出；
（3）undo 表示遇到错误后撤回之前的操作，mysql 中暂时还不支持这种处理方式。

参数指明错误类型，该参数有 6 个取值：
（1）sqlstate_value：包含 5 个字符的字符串错误值；
（2）condition_name：表示 declare 定义的错误条件名称；
（3）sqlwarning：匹配所有以 01 开头的 sqlstate_value 值；
（4）not found：匹配所有以 02 开头的 sqlstate_value 值；
（5）sqlexception：匹配所有没有被 sqlwarning 或 not found 捕获的 sqlstate_value 值；
（6）mysql_error_code：匹配数值类型错误代码。
sp_statement 参数为程序语句段，表示在遇到定义的错误时，需要执行的一些存储过程或函数。

下面是定义处理程序的几种方式，代码如下：
//方法一：捕获 sqlstate_value
declare continue handler for sqlstate '42s02' set @info='can not find';

//方法二：捕获 mysql_error_code
declare continue handler for 1146 set @info='can not find';

//方法三：先定义条件，然后调用
declare can_not_find condition for 1146;
declare continue handler for can_not_find set @info='can not find';

//方法四：使用 sqlwarning
declare exit handler for sqlwarning set @info='error';

//方法五：使用 not found
declare exit handler for not found set @info='can not find';

//方法六：使用 sqlexception
declare exit handler for sqlexception set @info='error';