MySQL的if，case语句使用总结

示例数据库

Mysql的if既可以作为表达式用，也可在存储过程中作为流程控制语句使用，如下是做为表达式使用：

IF表达式

IF(expr1,expr2,expr3)

如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，则 IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值

SELECT  *,IF(langName='english','英语','非英语') AS languagetype FROM  languages

运行结果

作为表达式的if也可以用CASE when来实现

SELECT *, CASE langName WHEN 'english' THEN '英语' ELSE '非英语'  END AS languagetype FROM languages

如果没有匹配的结果值，则返回结果为ELSE后的结果，如果没有ELSE 部分，则返回值为 NULL。例如：

SELECT CASE 1 WHEN 1 THEN 'one'
  WHEN 2 THEN 'two' 
   ELSE 'more' END
as testCol

将输出one

CASE when的基本用法（新建一个表来说明）

/*
 
mysql> select * from sales;
+-----+------------+--------+--------+--------+------+------------+
| num | name       | winter | spring | summer | fall | category   |
+-----+------------+--------+--------+--------+------+------------+
|   1 | Java       |   1067 |    200 |    150 |  267 | Holiday    |
|   2 | C          |    970 |    770 |    531 |  486 | Profession |
|   3 | JavaScript |     53 |     13 |     21 |  856 | Literary   |
|   4 | SQL        |    782 |    357 |    168 |  250 | Profession |
|   5 | Oracle     |    589 |    795 |    367 |  284 | Holiday    |
|   6 | MySQL      |    953 |    582 |    336 |  489 | Literary   |
|   7 | Cplus      |    752 |    657 |    259 |  478 | Literary   |
|   8 | Python     |     67 |     23 |     83 |  543 | Holiday    |
|   9 | PHP        |    673 |     48 |    625 |   52 | Profession |
+-----+------------+--------+--------+--------+------+------------+
9 rows in set (0.01 sec)
 
mysql> SELECT name AS Name,
    -> CASE category
    -> WHEN "Holiday" THEN "Seasonal"
    -> WHEN "Profession" THEN "Bi_annual"
    -> WHEN "Literary" THEN "Random" END AS "Pattern"
    -> FROM sales;
+------------+-----------+
| Name       | Pattern   |
+------------+-----------+
| Java       | Seasonal  |
| C          | Bi_annual |
| JavaScript | Random    |
| SQL        | Bi_annual |
| Oracle     | Seasonal  |
| MySQL      | Random    |
| Cplus      | Random    |
| Python     | Seasonal  |
| PHP        | Bi_annual |
+------------+-----------+
9 rows in set (0.00 sec)
 
 
*/
Drop table sales;
   
CREATE TABLE sales(
    num MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(20),
    winter INT,
    spring INT,
    summer INT,
    fall INT,
    category CHAR(13),
    primary key(num)
)type=MyISAM;
 
 
insert into sales value(1, 'Java', 1067 , 200, 150, 267,'Holiday');
insert into sales value(2, 'C',970,770,531,486,'Profession');
insert into sales value(3, 'JavaScript',53,13,21,856,'Literary');
insert into sales value(4, 'SQL',782,357,168,250,'Profession');
insert into sales value(5, 'Oracle',589,795,367,284,'Holiday');
insert into sales value(6, 'MySQL',953,582,336,489,'Literary');
insert into sales value(7, 'Cplus',752,657,259,478,'Literary');
insert into sales value(8, 'Python',67,23,83,543,'Holiday');
insert into sales value(9, 'PHP',673,48,625,52,'Profession');
 
select * from sales;
 
 
SELECT name AS Name,
CASE category
WHEN "Holiday" THEN "Seasonal"
WHEN "Profession" THEN "Bi_annual"
WHEN "Literary" THEN "Random" END AS "Pattern"
FROM sales;

  简单语句

SELECT CASE WHEN 10*2=30 THEN '30 correct'
   WHEN 10*2=40 THEN '40 correct'
   ELSE 'Should be 10*2=20'
END;  

  多重表达式

SELECT CASE 10*2
   WHEN 20 THEN '20 correct'
   WHEN 30 THEN '30 correct'
   WHEN 40 THEN '40 correct'
END;

  在SELECT查询中使用CASE WHEN

/*
mysql> SELECT Name, RatingID AS Rating,
    ->    CASE RatingID
    ->       WHEN 'R' THEN 'Under 17 requires an adult.'
    ->       WHEN 'X' THEN 'No one 17 and under.'
    ->       WHEN 'NR' THEN 'Use discretion when renting.'
    ->       ELSE 'OK to rent to minors.'
    ->    END AS Policy
    -> FROM DVDs
    -> ORDER BY Name;
+-----------+--------+------------------------------+
| Name      | Rating | Policy                       |
+-----------+--------+------------------------------+
| Africa    | PG     | OK to rent to minors.        |
| Amadeus   | PG     | OK to rent to minors.        |
| Christmas | NR     | Use discretion when renting. |
| Doc       | G      | OK to rent to minors.        |
| Falcon    | NR     | Use discretion when renting. |
| Mash      | R      | Under 17 requires an adult.  |
| Show      | NR     | Use discretion when renting. |
| View      | NR     | Use discretion when renting. |
+-----------+--------+------------------------------+
8 rows in set (0.01 sec)
 
 
*/
 
Drop table DVDs;
 
CREATE TABLE DVDs (
   ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   Name VARCHAR(60) NOT NULL,
   NumDisks TINYINT NOT NULL DEFAULT 1,
   RatingID VARCHAR(4) NOT NULL,
   StatID CHAR(3) NOT NULL
)
ENGINE=INNODB;
 
INSERT INTO DVDs (Name, NumDisks, RatingID, StatID)
VALUES ('Christmas', 1, 'NR', 's1'),
       ('Doc',       1, 'G',  's2'),
       ('Africa',    1, 'PG', 's1'),
       ('Falcon',    1, 'NR', 's2'),
       ('Amadeus',   1, 'PG', 's2'),
       ('Show',      2, 'NR', 's2'),
       ('View',      1, 'NR', 's1'),
       ('Mash',      2, 'R',  's2');
   
 
SELECT Name, RatingID AS Rating,
   CASE RatingID
      WHEN 'R' THEN 'Under 17 requires an adult.'
      WHEN 'X' THEN 'No one 17 and under.'
      WHEN 'NR' THEN 'Use discretion when renting.'
      ELSE 'OK to rent to minors.'
   END AS Policy
FROM DVDs
ORDER BY Name;

IFNULL(expr1,expr2)

假如expr1 不为 NULL，则 IFNULL() 的返回值为 expr1; 否则其返回值为 expr2。IFNULL()的返回值是数字或是字符串

IF ELSE 做为流程控制语句使用

if实现条件判断，满足不同条件执行不同的操作

IF search_condition THEN 
    statement_list  
[ELSEIF search_condition THEN]  
    statement_list ...  
[ELSE 
    statement_list]  
END IF 

注意：IF作为一条语句，在END IF后需要加上分号“;”以表示语句结束，其他语句如CASE、LOOP等也是相同的。