sql：高级特性

高级特性：

需知：delimiter:改变提交的符号

1.变量：
用户　　　　　　 　　 @ 　　　　　　　　　　　　 声明变量；set @var = 0;
　　　　　　　　　　　　　　　　　　　　　　　　输出变量：select @var
　　　　　　　　　　　　　　　　　　　　　　　　声明变量时，进行赋值：
　　　　　　　　　　　　　　　　　　　　　　　　　　select @var:=5; 返回5，改变值
　　　　　　　　　　　　　　　　　　　　　　　　　　注意：
　　　　　　　　　　　　　　　　　　　　　　　　　　　　select @var=5; 返回1/0，因此此处=作为关系运算符，非算术运算符
　　　　　　　　　　　　　　　　　　　　　　　　修改变量：set @var=@var+1;


全局　　　　 　　　　 @@　　　　　　　　　　　　 set global var = 5;
　　　　　　　　　　　　　　　　　　　　　　　　　set @@var=5;
局部 　　　　　　　　　　　　　　　 函数或存储过程
　　　　　　　　　　　　　　　　　　　　　声明：declare NAME 类型 default VALUE;
　　　　　　　　　　　　　　　　　　　　　如：
                           　　　　　　　　　　　　　　　declare rst int default 0;
　　　　　　　　　　　　　　　　　　　　　改变值：
　　　　　　　　　　　　　　　　　　　　　　  set NAME = .....
　　　　　　　　　　　　　　　　　　　　　如：
　　　　　　　　　　　　　　　　　　　　　　　set rst =rst + 2;

 

2.分支：
　　空值函数：
　　　　nullif(v1,v2) 相同返回null，不同返回v1

　　简单分支：【三元运算符】
　　　　if(condition,v1,v2)
　　　　如：
　　　　　　select if(1=1,1,0);

　　多分支：
　　　　一：
　　　　　　case EXP when v1 then do .... when v2 then do .... else do .... end;
　　　　　　case when condition_1 then v1 when condition_2 then v2 else v3 end;
　　　　　　如：
　　　　　　　　1、
　　　　　　　　　　set @val=2;
　　　　　　　　　　select (case @val when 1 then 'helloWorld' when 2 then 'welcome...' else '你错了' end);
　　　　　　　　2、
　　　　　　　　　　select (
　　　　　　　　　　　　case
　　　　　　　　　　　　　　when @val between 1 and 3 then 'win...'
　　　　　　　　　　　　　　when @val between 4 and 6 then 'haha'
　　　　　　　　　　　　　　else 'def' end
　　　　　　　　　　) as showTime;


　　　　二：
　　　　　　if condition_1 then do ..
　　　　　　else if condition_2 then do...
　　　　　　else do...
　　　　　　end if;

 

 

3.循环：[用于函数的存储过程里]
　　　　###死循环
　　　　　　结构：
　　　　　　　　NAME：loop
　　　　　　循环体：
　　　　　　　　end loop NAME;
　　　　　　案例：
　　　　　　　　#写法一：死循环loop+leave
　　　　　　　　　　　　drop function if exists func2;
　　　　　　　　　　　　delimiter //
　　　　　　　　　　　　create function func2(endSum int) returns int
　　　　　　　　　　　　　　deterministic #确定的,
　　　　　　　　　　　　begin
　　　　　　　　　　　　　　declare i int default 1;
　　　　　　　　　　　　　　declare proSum int default 0;
　　　　　　　　　　　　　　MY_LOOP: loop
　　　　　　　　　　　　　　　　set proSum = proSum+i;
　　　　　　　　　　　　　　　　if proSum >= endSum then
　　　　　　　　　　　　　　　　　　leave MY_LOOP;
　　　　　　　　　　　　　　　　end if;
　　　　　　　　　　　　　　　　set i=i+1;
　　　　　　　　　　　　　　end loop;
　　　　　　　　　　　　　　return i;
　　　　　　　　　　　　end //
　　　　　　　　　　　　delimiter ;

　　　　　###先判断再执行：
　　　　　　　　结构：
　　　　　　　　　　NAME：while CONDITION do
　　　　　　　　循环体；
　　　　　　　　　　leave NAME;[break]
　　　　　　　　　　iterate NAME; [continue]
　　　　　　　　　　CONDITION CHANGE;
　　　　　　　　end while NAME;
　　　　　　　　案例：
　　　　　　　　　　#写法二：while循环
　　　　　　　　　　　　drop function if exists func2;
　　　　　　　　　　　　delimiter //
　　　　　　　　　　　　create function func2(endSum int) returns int
　　　　　　　　　　　　　　deterministic
　　　　　　　　　　　　begin
　　　　　　　　　　　　　　declare i int default 0;
　　　　　　　　　　　　　　declare proSum int default 0;
　　　　　　　　　　　　　　　　while proSum<endSum do
　　　　　　　　　　　　　　　　　　set i=i+1;
　　　　　　　　　　　　　　　　　　set proSum=proSum+i;
　　　　　　　　　　　　　　　　end while;
　　　　　　　　　　　　　　return i;
　　　　　　　　　　　　end //
　　　　　　　　　　　　delimiter ;


　　　　　　###先执行再判断
　　　　　　　　结构：
　　　　　　　　　　NAME:repeat
　　　　　　　　循环体；
　　　　　　　　　　leave NAME;[break]
　　　　　　　　　　iterate NAME; [continue]
　　　　　　　　　　CONDITION CHANGE;
　　　　　　　　　until END_CONDITION
　　　　　　　　　end repeat NAME;
　　　　　　　　案例：
　　　　　　　　　　#写法三：repeat循环
　　　　　　　　　　　　drop function if exists func2;
　　　　　　　　　　　　delimiter //
　　　　　　　　　　　　create function func2(endSum int) returns int
　　　　　　　　　　　　　　deterministic #确定的,
　　　　　　　　　　　　begin
　　　　　　　　　　　　　　declare i int default 0;
　　　　　　　　　　　　　　declare proSum int default 0;
　　　　　　　　　　　　　　repeat
　　　　　　　　　　　　　　　　set i =i +1;
　　　　　　　　　　　　　　　　set proSum=proSum+i;
　　　　　　　　　　　　　　until proSum>=endSum #反条件【结束的条件】
　　　　　　　　　　　　　　end repeat;
　　　　　　　　　　　　　　return i;

　　　　　　　　　　　　end //
　　　　　　　　　　　　delimiter ;

4.函数：【面向DML】
　　　　show function status;查所有函数
　　　　show function status like 'FUNC_NAME';查指定函数
　　　　show create function FUNC_NAME;查函数是如何写的【即：函数体】【用于复杂的函数】
　　　　drop function if exists FUN_NAME;删除函数
　　　　set global log_bin_trust_function_creators=TRUE;创建函数被信任【当无法创建函数时，使用】

　　　　其中，deterministic ：MySQL有一个优化机制，可以在多次调用同一个函数时，只计算一次结果并缓存，以提高查询的效率
　　　　格式：
　　　　　　1.构建函数：
　　　　　　　　drop function if exists func1;
　　　　　　　　delimiter //
　　　　　　　　create function func1(a int,b int) returns int
　　　　　　　　　　deterministic
　　　　　　　　begin
　　　　　　　　#内容
　　　　　　　　　　//声明变量【放在头部】
　　　　　　　　　　//具体内容
　　　　　　　　　　return a+b;
　　　　　　　　end//
　　　　　　　　delimiter ;

　　　　　　2.应用函数：
　　　　　　　　select func1(x,y);

 

5.存储过程：【面向DQL】
　　　　结构：
　　　　　　drop procedure if exits NAME;
　　　　　　delimiter //
　　　　　　create procedure NAME(in|out|inout a int,in|out|inout b int)
　　　　　　begin
　　　　　　　　//内容
　　　　　　end //
　　　　　　delimiter ;
　　　　调用存储过程：
　　　　　　call NAME(x,y,..);

　　　　#案例：分页查询
　　　　　　　　drop procedure if exists pro_find_stu_by_page;
　　　　　　　　delimiter //
　　　　　　　　create procedure pro_find_stu_by_page(
　　　　　　　　　　in pageNo int,
　　　　　　　　　　in pageSize int,
　　　　　　　　　　out pageTotal int
　　　　　　　　　　)
　　　　　　　　begin
　　　　　　　　　　declare fromOffSet int default 0;
　　　　　　　　　　select ceil(count(*)/pageSize) into pageTotal from student_info;

　　　　　　　　　　#赋值
　　　　　　　　　　set pageNo = (
　　　　　　　　　　　　case
　　　　　　　　　　　　　　when pageNo<1 then 1
　　　　　　　　　　　　　　when pageNo>pageTotal then pageTotal
　　　　　　　　　　　　　　else pageNo
　　　　　　　　　　　　end
　　　　　　　　　　);
　　　　　　　　　　set fromOffSet = (pageNo-1)*pageSize;
　　　　　　　　　　select
　　　　　　　　　　　　stu_id,
　　　　　　　　　　　　stu_name,
　　　　　　　　　　　　stu_pid,
　　　　　　　　　　　　stu_gender,
　　　　　　　　　　　　add_province,
　　　　　　　　　　　　add_city,
　　　　　　　　　　　　stu_diploma,
　　　　　　　　　　　　stu_type,
　　　　　　　　　　　　stu_major
　　　　　　　　　　from student_info
　　　　　　　　　　limit fromOffSet,pageSize;
　　　　　　　　end //
　　　　　　　　delimiter ;

　　　　#调用:[用call来调用]
　　　　　　　　set @pageTotal = 0;#输出参数
　　　　　　　　call pro_find_stu_by_page(30,20,@pageTotal);