MySQL学习笔记之流程控制

一、分支结构

  1. if函数
  语法：if(表达式1,表达式2,表达式3)
  如果表达式1成立，则返回表达式2的值，否则返回表达式3的值
  mysql> select if(2<3,'True','False');
  +------------------------+
  | if(2<3,'True','False') |
  +------------------------+
  | True                   |
  +------------------------+
  1 row in set (0.00 sec)

  mysql> select if(2>3,'True','False');
  +------------------------+
  | if(2>3,'True','False') |
  +------------------------+
  | False                  |
  +------------------------+
  1 row in set (0.00 sec)

  2. case结构
  case 有两种用法：
        ① 语法：
        case 表达式|变量|字段
        when 判断的值1 then 返回值1
        when 判断的值2 then 返回值2
        ...
        when 判断的值n then 返回值n
        else 返回值
        end case
        ② 语法：
        case
        when 条件1 then 返回值1或语句1
        when 条件2 then 返回值2或语句2
        ...
        when 条件n then 返回值n或语句n
        else 返回值或语句
        end case
        如果作为表达式使用，case结构可以在任何地方使用，如果作为独立的语句使用，则只能放在begin...end中使用。
        例1：作为表达式使用
        mysql> select name,case gender when 1 then '男' when 2 then '女' else '未知' end 性别 from students;
        +-----------+--------+
        | name      | 性别   |
        +-----------+--------+
        | 李四      | 男     |
        | 周芷若    | 女     |
        | 赵敏      | 女     |
        | Lucy      | 女     |
        | Tony      | 男     |
        | Lucy      | 女     |
        | 萧峰      | 男     |
        +-----------+--------+
        7 rows in set (0.00 sec)

        例2：作为独立语句使用
        mysql> delimiter $$
        mysql> create procedure get_students_grade(in name varchar(20),in subject varchar(20))
              -> begin
              -> declare su_score int default 0;
              -> select s.score into su_score from students st left join score s on st.id=s.student_id left join subject su on su.id=s.subject_id where st.name=name and su.name=subject;
              -> case 
              -> when su_score >= 90 then select '优秀';
              -> when su_score >= 80 then select '良好';
              -> when su_score >= 60 then select '及格';
              -> else select '不及格';
              -> end case;
              -> end $$
        Query OK, 0 rows affected (0.11 sec)

        mysql> delimiter ;
        mysql> call get_students_grade('周芷若','数学');
        +-----------+
        | 不及格    |
        +-----------+
        | 不及格    |
        +-----------+
        1 row in set (0.00 sec)

  3. if结构
        语法：
        if expr1 then 语句1
        elseif expr2 then 语句2
        ...
        [else 语句n]
        end if;
        if结构只能在begin...end中使用
        mysql> delimiter $$
        mysql> create procedure getGradre(in score int)
              -> begin
              -> if score >= 90 then select 'A';
              -> elseif score >= 80 then select 'B';
              -> elseif score >= 60 then select 'C';
              -> else select 'D';
              -> end if;
              -> end $$
        Query OK, 0 rows affected (0.02 sec)

        mysql> delimiter ;
        mysql> call getGradre(85);
        +---+
        | B |
        +---+
        | B |
        +---+
        1 row in set (0.00 sec)

二、循环结构
MySQL的循环结构分为while、loop、repeat三种，循环控制结构有：iterate（类似于continue）和leave（类似于break）。

  1. while循环结构
  语法：
        [标签:]while 循环条件 do
              循环体;
        end while [标签];
  
  2. loop循环结构
  语法：
        [标签:]loop
              循环体;
        end loop [标签];
  可以用来模拟简单的死循环，搭配leave结束循环。

  3. repeat循环结构
  语法：
        [标签:]repeat
              循环体;
        until 结束循环条件
        end repeat [标签]

三、MySQL循环结构使用示例

  1. 创建测试表
  create table admin(
        id int auto_increment primary key,
        name varchar(20),
        passwd char(32)
  ) engine=innodb default charset=utf8mb4 collate=utf8mb4_unicode_ci;

  2. 创建存储过程，使用while循环结构，批量插入指定数量的数据
  set autocommit = 0;#关闭事务的自动提交
  delimiter $
  create procedure insertAdmin(in insertCount int)
  begin
        declare i int default 1;
        start transaction;
        while insertCount >= i do
        insert into admin(name,passwd) values(concat('abcd',i),md5('aaaa'));
        set i = i+1;
        end while;
        commit;
  end $
  delimiter ;
  call insertAdmin(2);

  mysql> select * from admin;
  +----+-------+----------------------------------+
  | id | name  | passwd                           |
  +----+-------+----------------------------------+
  |  1 | abcd1 | 74b87337454200d4d33f80c4663dc5e5 |
  |  2 | abcd2 | 74b87337454200d4d33f80c4663dc5e5 |
  +----+-------+----------------------------------+
  2 rows in set (0.00 sec)

  3. 创建存储过程，使用loop循环结构，批量插入指定数量的数据

  delimiter $
  create procedure insertAdminLoop(in insertCount int)
  begin
        declare i int default 1;
        loopName:loop
              insert into admin(name,passwd) values(concat('efgh',i),md5('bbbb'));
              if insertCount <= i then leave loopName;
              end if;
              set i = i+1;
        end loop loopName;
        commit;
  end $
  delimiter ;
  call insertAdminLoop(2);

  4. 创建存储过程，使用repeat循环结构，批量插入指定数量的数据

  mysql> delimiter $
  mysql> create procedure insertAdminRepeat(in insertCount int)
        -> begin
        -> declare i int default 1;
        -> repeat
        -> insert into admin(name,passwd) values(concat('fwcweAX',i),md5(concat('sdfewf',i)));
        -> set i = i+1;
        -> until insertCount < i
        -> end repeat;
        -> commit;
        -> end $
  Query OK, 0 rows affected (0.01 sec)

  mysql> delimiter ;

四、使用存储函数和存储过程灌入数据

  1. 创建部门表
  create table dept(
        id int auto_increment primary key,
        deptName varchar(30) default null,
        address varchar(40) default null
  )engine=innodb default charset=utf8mb4 collate=utf8mb4_unicode_ci;

  2. 创建员工信息表
  create table emp(
        id int auto_increment primary key,
        name varchar(20) default null,
        age tinyint(3) default null,
        deptid int default null,
        empno int not null,
        key idx_dept_id (deptid)
  )engine=innodb default charset=utf8mb4 collate=utf8mb4_unicode_ci;

  3. 创建函数，获取随机字符串
  delimiter $
  create function rand_str(n int) returns varchar(255) reads sql data
  begin
        declare chars_str varchar(60) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
        declare return_str varchar(60) default '';
        declare i int default 1;
        while i <= n do
              set return_str = concat(return_str,substr(chars_str,floor(rand()*52+1),1));
              set i = i+1;
        end while;
        return return_str;
  end $

  4. 创建函数，获取部门随机编号
  create function rand_number(from_num int,to_num int) returns int no sql
  begin
        declare i int default 0;
        set i = floor(from_num + rand() * (to_num - from_num + 1));
        return i;
  end $

  5. 创建存储过程，批量插入员工信息数据
  create procedure insertEmp(in start_num int, in end_num int)
  begin
        declare i int default 0;
        declare name_len int default 1;
        set name_len = floor(5 + rand()*6);
        set autocommit = 0;
        start transaction;
        repeat
              set i = i+1;
              insert into emp(name,age,deptid,empno) values(rand_str(name_len),rand_number(30,50),rand_number(1,100000),(start_num+i));
              until end_num <= i
        end repeat;
        commit;
        set autocommit=1;
  end $
  call insertEmp(100000,5000000);

  6. 创建存储过程，批量插入部门信息数据
  create procedure insertDept(in total int)
  begin
        declare i int default 0;
        declare deptName_len int default 1;
        declare addr_len int default 1;
        set deptName_len = floor(6+rand()*7);
        set addr_len = floor(10+rand()*11);
        set autocommit = 0;
        start transaction;
        repeat
              set i = i+ 1;
              insert into dept(deptName,address,ceo) values(rand_str(deptName_len),rand_str(addr_len),rand_number(1,5000000));
        until i>=total
        end repeat;
        commit;
        set autocommit=1;
  end $
  call insertDept(100000);