MySQL数据库-SQL编程

一、触发器

1.触发器简介

触发器（trigger）是一个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， 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 total=total+1;

mysql> insert into student1(name) values('jenny');

mysql> select * from student_total;
+-------+
| total |
+-------+
|     2 |
+-------+

（3）查看触发器

（1）. 通过SHOW TRIGGERS语句查看
mysql> show triggers\G

（2）. 通过系统表triggers查看
mysql> use information_schema

mysql> select * from triggers\G

mysql> select * from triggers where trigger_name='student_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 操作，以免递归循环触发
• 尽量少使用触发器，假设触发器触发每次执行1s，insert 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]参数名 类型...)
    begin
        SQL语句
    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(i<10000)
    do 
    insert into passwd values(i, md5(i)); 
    set i=i+1; 
    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 id=1003; 
    select var_name02; 
end $$

mysql> delimiter ;

mysql> call proc04();
+------------+
| var_name02 |
+------------+
|       李四 |
+------------+

（3）用户变量

用户自定义，当前会话（连接）有效。类比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 |
+-------------+

（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.参数传递

（1）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;

（2）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 |
+-------+

（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 dept2=d_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 dept2=d_num and incoming>=wages;
    -> 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)

（4）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 |
+------+

（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)
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：输入员工的名字，判断工资的情况

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))
begin
    declare result varchar(20);
    declare var_sal int;
    select incoming into var_sal from emp_new where name = in_ename;
    if var_sal < 3000
    then set result = '试用薪资';
    elseif var_sal<8000
    then set result = '转正薪资';
    else
        set result = '元老薪资';
    end if;
    select result;
end$$

mysql> delimiter ;

mysql> call proc12_if('赵六');

mysql> call proc12_if('荣八');

2.case判断

case是另一个条件判断的语句，类似于编程语言中的switch语法，其语法格式如下：

# 语法一（类比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

• 例1

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

mysql> delimiter $$
mysql> create procedure proc14_case(in score int)
begin
    case
        when 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）循环分类

• while
• repeat
• loop

（3）循环控制

• leave 类似于 break，跳出，结束当前所在的循环
• iterate类似于 continue，继续，结束本次循环，继续下一次

（4）while循环格式

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

示例：

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 $$

（5）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 $$

（6）loop循环格式

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

示例：

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 $$

四、存储函数

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 ...]
begin
    routine_body
END;

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

调用存储函数：

• 在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法基本相同
• 用户自定义的存储函数与MySQL内部函数性质相同。
• 区别在于，存储函数是用户自定义的。而内部函数由MySQL自带。其语法结构如下：

select func_name([parameter[,…]]);

• 例1：无参数存储函数

mysql> set global log_bin_trust_function_creators=TRUE; # 信任子程序的创建者，否则可能
报错,执行一次即可

# 创建存储函数-没有输输入参数

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();
+---------------+
| 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)
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);
+-------------------+
| myfunc2_emp(1776) |
+-------------------+
|              王五 |
+-------------------+

3.删除存储函数

MySQL中使用drop function语句来删除存储函数。

• 示例：删除存储函数

mysql> drop function if exists myfunc1_emp;

mysql> drop function if exists myfunc2_emp;