mysql控制流程函数_MySQL流程控制函数的使用举例

MySQL流程控制函数的使用举例

MySQL提供了case、if、ifnull等流程控制函数。现对这些函数的功能及用法进行介绍。创建数据表emp并输入数据：

create table dept(

dept_id int primary key auto_increment comment '部门编号',

dept_name char(20) comment '部门名称'

);

insert into dept(dept_name) values('销售部'),('财务部'),('生产部'),('人事部');

create table emp(

emp_id int primary key auto_increment comment '员工号',

emp_name char(20) not null default '' comment '员工姓名',

gender tinyint not null default 1 comment '性别', --1表示'男'，0表示'女'

birth datetime not null default '1990-1-1' comment '出生日期',

salary decimal(10,2) not null default 0 comment '工资',

dept_id int comment '部门编号'

);

insert into emp(emp_name,gender,birth,salary,dept_id)

values('张晓红',0,'1980-1-23',5800,1),('张静静',0,'1987-10-3',5400,1),('王云飞',1,'1992-11-15',5600,1),

('王鹏飞',1,'1987-10-1',6800,1),('王大鹏',1,'1989-2-11',5900,1),('王萌萌',0,'1986-12-30',5000,2),

('王大光',1,'1988-11-8',6200,2),('王小明',1,'1998-1-3',4800,2),('王娜娜',0,'1994-3-5',5200,2),

('刘云飞',1,'1992-8-13',6800,3),('张陆军',1,'1991-9-6',6200,3);

一、if函数

if函数的语法如下：

if(逻辑表达式,结果表达式1,结果表达式2);

说明：如果【逻辑表达式】成立，返回【结果表达式1】，如果不成立则返回【结果表达式2】。

举例：

mysql> select if(100%2=0,'偶数','奇数');

+-------------------------------+

| if(100%2=0,'偶数','奇数') |

+-------------------------------+

| 偶数 |

+-------------------------------+

1 row in set (0.00 sec)

mysql> select if(15%2=0,'偶数','奇数');

+------------------------------+

| if(15%2=0,'偶数','奇数') |

+------------------------------+

| 奇数 |

+------------------------------+

1 row in set (0.00 sec)

mysql> select emp_id,emp_name,if(gender=1,'男','女') as gender,dept_name

from emp inner join dept on emp.dept_id=dept.dept_id

where dept_name='销售部';

+--------+-----------+--------+-----------+

| emp_id | emp_name | gender | dept_name |

+--------+-----------+--------+-----------+

| 1 | 张晓红 | 女 | 销售部 |

| 2 | 张静静 | 女 | 销售部 |

| 3 | 王云飞 | 男 | 销售部 |

| 4 | 王鹏飞 | 男 | 销售部 |

| 5 | 王大鹏 | 男 | 销售部 |

+--------+-----------+--------+-----------+

5 rows in set (0.00 sec)

二、ifnull函数

ifnull函数的语法如下：

ifnull(表达式1,表达式2);

说明：首先判断【表达式1】的值是否为空，如果不为空，就返回【表达式1】的值，否则返回【表达式2】的值。

举例：

mysql> select dept_name,count(*) as emp_count,sum(salary) as total_salary

from emp inner join dept on emp.dept_id=dept.dept_id

group by dept_name with rollup;

+-----------+-----------+--------------+

| dept_name | emp_count | total_salary |

+-----------+-----------+--------------+

| 生产部 | 2 | 13000.00 |

| 财务部 | 4 | 21200.00 |

| 销售部 | 5 | 29500.00 |

| NULL | 11 | 63700.00 |

+-----------+-----------+--------------+

4 rows in set (0.00 sec)

mysql> select ifnull(dept_name,'total') as dept_name,

count(*) as emp_count,sum(salary) as total_salary

from emp inner join dept on emp.dept_id=dept.dept_id

group by dept_name with rollup;

+-----------+-----------+--------------+

| dept_name | emp_count | total_salary |

+-----------+-----------+--------------+

| 生产部 | 2 | 13000.00 |

| 财务部 | 4 | 21200.00 |

| 销售部 | 5 | 29500.00 |

| total | 11 | 63700.00 |

+-----------+-----------+--------------+

4 rows in set, 1 warning (0.00 sec)

mysql> select ifnull(dept_name,'total') as '部门',

sum(if(gender=1,1,0)) as '男',sum(if(gender=0,1,0)) as '女',

count(gender) as '总人数'

from emp inner join dept on emp.dept_id=dept.dept_id

group by dept_name with rollup;

+-----------+------+------+-----------+

| 部门 | 男 | 女 | 总人数 |

+-----------+------+------+-----------+

| 生产部 | 2 | 0 | 2 |

| 财务部 | 2 | 2 | 4 |

| 销售部 | 3 | 2 | 5 |

| total | 7 | 4 | 11 |

+-----------+------+------+-----------+

4 rows in set (0.00 sec)

mysql> select ifnull(dept_name,'total') as '部门',

sum(if(gender=1,salary,0)) as '男',sum(if(gender=0,salary,0)) as '女',

sum(salary) as '工资总额'

from emp inner join dept on emp.dept_id=dept.dept_id

group by dept_name with rollup;

+-----------+----------+----------+--------------+

| 部门 | 男 | 女 | 工资总额 |

+-----------+----------+----------+--------------+

| 生产部 | 13000.00 | 0.00 | 13000.00 |

| 财务部 | 11000.00 | 10200.00 | 21200.00 |

| 销售部 | 18300.00 | 11200.00 | 29500.00 |

| total | 42300.00 | 21400.00 | 63700.00 |

+-----------+----------+----------+--------------+

4 rows in set (0.00 sec)

三、case函数

case函数的语法如下：

格式一： case 表达式 when [value1] then 表达式1 [when [value2] then 表达式2 ...] [else 表达式n] end

格式二： case when [逻辑表达式1] then 表达式1 [when [逻辑表达式2] then 表达式2 ...] [else 表达式n] end

说明：

1、对于第一种格式：如果【value1】的值和【表达式】的值相等，就返回【表达式1】的值，如果【value2】的值和【表达式】的值相等，就返回【表达式2】的值，以此类推，如果所有的【value】都不等于【表达式】的值，就返回【else】后面的【表达式n】的值，如果没有【else】就返回NULL值。

2、对于第二种格式：如果【逻辑表达式1】成立则返回【表达式1】的值，如果【逻辑表达式2】成立就返回【表达式2】的值，以此类推，如果所有的【逻辑表达式】都不成立，就返回【else】后面的【表达式n】的值，如果没有【else】就返回NULL值。

3、第二种格式更为灵活一些。

mysql> set @score=3;

Query OK, 0 rows affected (0.00 sec)

mysql> select case @score

when 5 then '优秀'

when 4 then '良好'

when 3 then '及格'

else '不及格' end as '测试成绩';

+--------------+

| 测试成绩 |

+--------------+

| 及格 |

+--------------+

1 row in set (0.00 sec)

mysql> set @score=62;

Query OK, 0 rows affected (0.00 sec)

mysql> select case when @score>=90 and @score<=100 then '优秀'

when @score>=80 then '良好'

when @score>=60 then '及格'

else '不及格' end as '测试成绩';

+--------------+

| 测试成绩 |

+--------------+

| 及格 |

+--------------+

1 row in set (0.00 sec)

mysql> select ifnull(dept_name,'total') as '部门',

sum(case when gender=1 then salary else 0 end) as '男',

sum(case when gender=0 then salary else 0 end) as '女',

sum(salary) as '工资总额'

from emp inner join dept on emp.dept_id=dept.dept_id

group by dept_name with rollup;

+-----------+----------+----------+--------------+

| 部门 | 男 | 女 | 工资总额 |

+-----------+----------+----------+--------------+

| 生产部 | 13000.00 | 0.00 | 13000.00 |

| 财务部 | 11000.00 | 10200.00 | 21200.00 |

| 销售部 | 18300.00 | 11200.00 | 29500.00 |

| total | 42300.00 | 21400.00 | 63700.00 |

+-----------+----------+----------+--------------+

4 rows in set (0.00 sec)