SQL编程

1.触发器

1.1触发器简介

        触发器（trigger）是一个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。
触发器经常用于加强数据的完整性约束和业务规则等。
例如，当学生表中增加了一个学生的信息时，学生的总数就应该同时改变。因此可以针对学生表创建一个触发器，每次增加一个学生记录时，就执行一次学生总数的计算操作，从而保证学生总数与记录数的一致性。

1.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语句，可用顺序，判断，循环等语句实现一般程序需要的逻辑功能

1.3NEW和OLD的区别

触发器类型触发器类型NEW 和OLD 的使用
INSERT 型触发器：NEW 表示将要或者已经新增的数据
UPDATE 型触发器：OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据
DELETE 型触发器：OLD 表示将要或者已经删除的数据

1.4注意

MYSQL中触发器中不能对本表进行 insert ,update ,delete 操作，以免递归循环触发
尽量少使用触发器，假设触发器触发每次执行1s，insert table 500条数据，那么就需要触发500次触发器，光是触发器执行的时间就花费了500s，而insert 500条数据一共是1s，那么这个insert的效率就非常低了。
触发器是针对每一行的；对增删改非常频繁的表上切记不要使用触发器，因为它会非常消耗资源。

2.存储过程

2.1概念

存储过程和函数是事先经过编译并存储在数据库中的一段sql语句集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
简单的说，存储过程就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法，是SQL 语言层面的代码封装与重用。
存储过程和函数的区别：
函数必须有返回值，而存储过程没有。
存储过程的参数可以是IN、OUT、INOUT类型，函数的参数只能是IN

2.2优点

存储过程只在创建时进行编译；而SQL语句每执行一次就编译一次，所以使用存储过程可以提高数据库执行速度
简化复杂操作，结合事务一起封装
复用性好
安全性高，可指定存储过程的使用权

2.3存储过程的创建与调用

# 1.定义
delimiter 自定义结束符号
create procedure 储存名([IN | OUT | INOUT]参数名 类型...)
begin
SQL语句
end 自定义结束符号
delimiter ;

# 2.调用：
call 存储过程名(实参列表)
# 注1：自定义符号可以用除了分号的符号，一般用$$ 或 //
# 注2：存储过程的参数形式：
IN 输入参数
OUT 输出参数
INOUT 输入输出参数

示例

# 使用存储过程，插如多条数据
mysql> create table passwd(id int,pwds varchar(50));
mysql> delimiter $$

mysql> create procedure proc02() 
    begin 
        declare i int default 1; 
        while(i<10000)
        do 
        insert into passwd values(i, md5(i)); 
        set i=i+1; 
        end while; 
    end $$

mysql> delimiter ;
mysql> call proc02();
mysql> select * from passwd;

2.4变量

局部变量: 用户自定义，在begin/end块中有效，格式：

# 声明变量
declare var_name type [default var_value];
# 举例
declare nickname varchar(32);

MySQL 中还可以使用 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 id=1003; 
            select var_name02; 
        end $$
mysql> delimiter ;
mysql> call proc04();
+------------+
| var_name02 |
+------------+
| 李四 |
+------------+

 用户变量：用户自定义，当前会话（连接）有效。类比java的成员变量，格式

@var_name
# 不需要提前声明，使用即声明，即无declare子句

mysql> delimiter $$
mysql> create procedure proc05() begin set @var_name03 = 'openlab'; end $$

mysql> delimiter ;
mysql> call proc05() ;
mysql> select @var_name01 ;
+-------------+
| @var_name01 |
+-------------+
|   openlab   |
+-------------+

 系统变量：

语法：
@@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一般用于查看全局变量的当前值

# 查看会话变量
show session variables;

# 查看某会话变量
select @@session.auto_increment_increment;

# 修改会话变量的值
set session sort_buffer_size = 50000;
set @@session.sort_buffer_size = 50000 ;

2.5参数传递

IN：表示传入的参数， 可以传入数值或者变量，即使传入变量，并不会更改变量的值，可以内部更改，仅仅作用在函数范围内。

mysql> delimiter $$
mysql> create procedure proc06(in a int) begin declare i int default 1;
while(i<=a) do insert into passwd values(i,md5(i)); set i=i+1; end while;
end $$
mysql> delimiter ;
mysql> set @num=100; # 定义用户变量
mysql> select @num; # 查看
+------+
| @num |
+------+
| 100 |
+------+
mysql> call proc06(@num); # 执行过程，传递变量值到过程中
mysql> select * from passwd;

out：表示从存储过程内部传值给调用者,in的反向传递

mysql> delimiter $$
mysql> create procedure proc07(out cnt1 int) begin select count(*) into cnt1
from passwd; end $$
mysql> delimiter ;
mysql> call proc07(@cnt2);
mysql> select @cnt2;
+-------+
| @cnt2 |
+-------+
| 10099 |
+-------+

inout：

mysql> delimiter $$
mysql> create procedure proc10(inout p1 int) begin if (p1 is not null) then set
p1=p1+1; else select 100 into p1; end if; end$$
mysql> delimiter ;
mysql> select @h;
+------+
|  @h  |
+------+
| NULL |
+------+
mysql> call proc10(@h);
mysql> select @h;
+------+
|  @h  |
+------+
|  100 |
+------+

总结：
in ：输入参数，意思说你的参数要传到存过过程的过程里面去，在存储过程中修改该参数的值不能被返回
out ：输出参数:该值可在存储过程内部被改变，并向外输出
inout ：输入输出参数，既能输入一个值又能传出来一个值)

2.6if判断

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

示例

mysql> delimiter $$

mysql> create procedure proc11_if(in score int)
     begin
        if score < 60
            then
                select '不及格';
        elseif score < 80
            then
                select '及格' ;
        elseif score >= 80 and score < 90
            then
                select '良好';
        elseif score >= 90 and score <= 100
            then
                select '优秀';
        else
            select '成绩错误';
        end if;
     end $$
mysql> delimiter ;
mysql> call proc11_if(120);
mysql> call proc11_if(86);

2.7case判断

# 语法一（类比java的switch）：
case case_value
when when_value then statement_list
[when when_value 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

mysql> delimiter $$
mysql> create procedure proc13_case(in pay_type int)
    begin
        case pay_type
        when 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.8循环

1.while循环格式

[标签:]while 循环条件 do
循环体;
end while[标签];

示例：

mysql> create table user (uid int primary key,username varchar(50),password
varchar(50));
# 存储过程-while

mysql> delimiter $$
mysql> create procedure proc15_while1(in insertcount int)
    begin
        declare i int default 1;
        label:while i<=insertcount do
        insert into user(uid,username,password) values(i,concat('user-',i),'123456');
        set i=i+1;
        end while label;
    end $$
mysql> delimiter ;
mysql> call proc15_while1(10);
mysql> select * from user;
+-----+----------+----------+
| uid | username | password |
+-----+----------+----------+
| 1 | user-1 | 123456 |
| 2 | user-2 | 123456 |
| 3 | user-3 | 123456 |
| 4 | user-4 | 123456 |
| 5 | user-5 | 123456 |
| 6 | user-6 | 123456 |
| 7 | user-7 | 123456 |
| 8 | user-8 | 123456 |
| 9 | user-9 | 123456 |
| 10 | user-10 | 123456 |
+-----+----------+----------+


# 存储过程-while + leave
mysql> truncate table user; # 清空表内容
mysql> delimiter $$
mysql> create procedure proc16_while2(in insertcount int)
    begin
        declare i int default 1;
        label:while i<=insertcount do
        insert into user(uid,username,`password`) values(i,concat('user-',i),'123456');
        if i=5
            then
                leave label;
        end if;
        set i=i+1;
        end while label;
    end $$
mysql> delimiter ;
mysql> call proc16_while2(10);
mysql> select * from user;
+-----+----------+----------+
| uid | username | password |
+-----+----------+----------+
| 1 | user-1 | 123456 |
| 2 | user-2 | 123456 |
| 3 | user-3 | 123456 |
| 4 | user-4 | 123456 |
| 5 | user-5 | 123456 |
+-----+----------+----------+


# 存储过程-while+iterate
mysql> truncate table user;
mysql> delimiter $$
mysql> create procedure proc17_while3(in insertcount int)
    begin
        declare i int default 1;
        label:while i<=insertcount do
        set i=i+1;
        if i=5
            then
                iterate label;
        end if;
        insert into user(uid,username,`password`) values(i,concat('user-',i),'123456');
        end while label;
    end $$
mysql> delimiter ;
mysql> call proc17_while3(10);
mysql> select * from user; # 没有第五条记录
+-----+----------+----------+
| uid | username | password |
+-----+----------+----------+
| 2 | user-2 | 123456 |
| 3 | user-3 | 123456 |
| 4 | user-4 | 123456 |
| 6 | user-6 | 123456 |
| 7 | user-7 | 123456 |
| 8 | user-8 | 123456 |
| 9 | user-9 | 123456 |
| 10 | user-10 | 123456 |
| 11 | user-11 | 123456 |
+-----+----------+----------+

2.repeat循环格式

[标签:]repeat
循环体;
until 条件表达式
end repeat [标签];

mysql> create procedure proc18_repeat(in insertCount int)
    begin
        declare i int default 1;
        label:repeat
        insert into user(uid, username, password) values(i,concat('user-',i),'123456');
        set i = i + 1;
        until i > insertCount
        end repeat label;
        select '循环结束';
    end $$
mysql> delimiter ;
mysql> call proc18_repeat(100);

 3.loop循环格式

[标签:] loop
循环体;
if 条件表达式
then
leave [标签];
end if;
end loop;

# 存储过程-循环控制-loop
mysql> truncate table user;
mysql> delimiter $$
mysql> create procedure proc19_loop(in insertCount int)
    begin
        declare i int default 1;
        label:loop
        insert into user(uid, username, password) values(i,concat('user-',i),'123456');
        set i = i + 1;
        if i > 5
        then
        leave label;
        end if;
        end loop label;
        select '循环结束';
    end $$
mysql> delimiter ;
mysql> call proc19_loop(10);

3.存储函数

3.1概念

MySQL存储函数（自定义函数）一般用于计算和返回一个值，可以将经常需要使用的计算或功能写成一个函数。函数和存储过程类似。

存储函数与存储过程的区别

存储函数有且只有一个返回值，而存储过程可以有多个返回值，也可以没有返回值。
存储函数只能有输入参数，而且不能带in, 而存储过程可以有多in,out,inout参数。
存储过程中的语句功能更强大，存储过程可以实现很复杂的业务逻辑，而函数有很多限制，如不能在函数中使用insert,update,delete,create等语句；
存储函数只完成查询的工作，可接受输入参数并返回一个结果，也就是函数实现的功能针对性比较
强。
存储过程可以调用存储函数。但函数不能调用存储过程。
存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用.

3.2存储函数的创建与调用

create function func_name ([param_name type[,...]])
returns type
[characteristic ...]
begin
routine_body
END;
# 参数说明：
（1）func_name ：存储函数的名称。
（2）param_name type：可选项，指定存储函数的参数。type参数用于指定存储函数的参数类型，该类型可
以是MySQL数据库中所有支持的类型。
（3）returns type：指定返回值的类型。
（4）characteristic：可选项，指定存储函数的特性。
（5）routine_body：SQL代码内容。

 示例：

mysql> delimiter $$
mysql> create function myfunc1_emp()
    returns int
    begin
        declare cnt int default 0;
        select count(*) into cnt from emp_new;
        return cnt;
    end $$
mysql> delimiter ;
# 调用存储函数
mysql> select myfunc1_emp();

mysql> create function myfunc2_emp(in_sid int)
    returns varchar(50)
    begin
        declare 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);