MySQL中常见的流程控制函数

IF(expr1,expr2,expr3)

说明：如果expr1为true，返回expr2;如果expr1为false，则返回expr3

// 由于条件为false,所以返回的结果为shanghai
SELECT IF(false,'beijing','shanghai') FROM DUAL;

// 由于条件为true，所以返回结果为beijing
SELECT IF(true,'beijing','shanghai') FROM DUAL;

IFNULL(expr1,expr2)

说明：如果expr1为非空，则返回expr1;如果expr1为空,则返回expr2

//第一个表达式不为空，所以返回小名
SELECT IFNULL("小名","大名") FROM DUAL; 
//第一个表达式为空，所以返回大名
SELECT IFNULL(NULL,"大名") FROM DUAL; 

SELECT CASE WHEN expr1 THEN expr2

WHEN expr3 THEN expr4...

ELSE expr5 END;

说明：该条件类似于if-else的多分支语句，when后面的是条件，then后面的是结果

// 先创建一张emp表备用
 CREATE TABLE emp ( 
     empno INT PRIMARY KEY, 
     ename NVARCHAR(30), 
     job NVARCHAR(30), 
     mgr INT , 
     hiredate DATETIME,
     sal DECIMAL(6.2),
     comm DECIMAL(6.2), 
     deptno INT ,
     FOREIGN KEY(deptno) REFERENCES dept(deptno)) DEFAULT CHARACTER SET utf8;

//给emp表插入数据
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7359,'SMITH','CLERK',7902,'1980-12-17',800.00,NULL,20);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7449,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES ( 7231, 'WARD', 'SALESMAN', 7698, '1981-2-22', 1250, 500, 30);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES ( 7246, 'JONES', 'MANAGER', 7839, '1981-4-2', 2975, NULL, 20);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES ( 7254, 'MARTIN', 'SALESMAN', 7698, '1981-9-28', 1250, 1400, 30);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES ( 7238, 'BLAKE', 'MANAGER', 7839, '1981-5-1', 2850, NULL, 30);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES ( 7242, 'CLARK', 'MANAGER', 7839, '1981-6-9', 2450, NULL, 10);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES ( 7788, 'SCOTT', 'ANALYST', 7566, '1987-4-19', 3000, NULL, 20);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES ( 7819, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES ( 7873, 'TURNER', 'SALESMAN', 7698, '1981-9-8', 1500, 0, 30);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7996, 'ADAMS', 'CLERK', 7788, '1987-5-23', 1100, NULL, 20);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7990, 'JAMES', 'CLERK', 7698, '1981-12-3', 950, NULL, 30);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES ( 7262, 'FORD', 'ANALYST', 7566, '1981-12-3', 3000, NULL, 20);
INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-1-23', 1300, NULL, 10);



//查询emp表中的job字段，如果为`clerk`则显示`职员`，
//如果为`MANAGER`则返回`经理`,如果为`SALESMAN`则返回`销售人员`，其它情况就自然返回
//最终字段结果以job返回
SELECT (SELECT CASE WHEN job = 'clerk' THEN '职员' 
WHEN job = 'MANAGER' THEN '经理' 
WHEN job = 'SALESMAN' THEN '销售人员' 
ELSE job END) AS 'job' FROM emp;