流程处理函数可以根据不同的条件,执行不同的处理流程,可以在sQL语句中实现不同的条件选择。MySQL中的流程处理函数主要包括lF()、IFNULL()和CASE()函数。

    函数 用法
    IF(value,value1,value2) 如果value的值为TRUE,返回value1,否则返回value2
    IFNULL(value1,value2) 如果value1不为NULL,返回value1,否则返回value2
    CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2……[ELSE resultn] END 相当于Java的if..else if…else…
    CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1……[ELSE 值n] END 相当于Java的switch…case…
    1. # 流程控制函数
    2. # IF(VALUE,VALUE1,VALUE2)
    3. SELECT last_name,salary,IF(salary >= 6000,'高工资','低工资') "details"
    4. FROM employees;
    5. SELECT last_name,commission_pct,IF(commission_pct IS NOT NULL,commission_pct,0) "details",
    6. salary * 12 * (1 + IF(commission_pct IS NOT NULL,commission_pct,0)) "annual_sal"
    7. FROM employees;
    8. # IFNULL(VALUE1,VALUE2):看做是IF(VALUE,VALUE1,VALUE2)的特殊情况
    9. SELECT last_name,commission_pct,IFNULL(commission_pct,0) "details"
    10. FROM employees;
    11. # CASE WHEN ... THEN ...WHEN ... THEN ... ELSE ... END
    12. # 类似于java的if ... else if ... else if ... else
    13. SELECT last_name,salary,CASE WHEN salary >= 15000 THEN '白骨精'
    14. WHEN salary >= 10000 THEN '潜力股'
    15. WHEN salary >= 8000 THEN '小屌丝'
    16. ELSE '草根' END "details",department_id
    17. FROM employees;
    18. SELECT last_name,salary,CASE WHEN salary >= 15000 THEN '白骨精'
    19. WHEN salary >= 10000 THEN '潜力股'
    20. WHEN salary >= 8000 THEN '小屌丝'
    21. END "details"
    22. FROM employees;
    23. # CASE ... WHEN ... THEN ... WHEN ... THEN ... ELSE ... END
    24. # 类似于java的swich ... case...
    25. /*
    26. 练习1
    27. 查询部门号为 10,20, 30 的员工信息,
    28. 若部门号为 10, 则打印其工资的 1.1 倍,
    29. 20 号部门, 则打印其工资的 1.2 倍,
    30. 30 号部门,打印其工资的 1.3 倍数,
    31. 其他部门,打印其工资的 1.4 倍数
    32. */
    33. SELECT employee_id,last_name,department_id,salary,CASE department_id WHEN 10 THEN salary * 1.1
    34. WHEN 20 THEN salary * 1.2
    35. WHEN 30 THEN salary * 1.3
    36. ELSE salary * 1.4 END "details"
    37. FROM employees;
    38. /*
    39. 练习2
    40. 查询部门号为 10,20, 30 的员工信息,
    41. 若部门号为 10, 则打印其工资的 1.1 倍,
    42. 20 号部门, 则打印其工资的 1.2 倍,
    43. 30 号部门打印其工资的 1.3 倍数
    44. */
    45. SELECT employee_id,last_name,department_id,salary,CASE department_id WHEN 10 THEN salary * 1.1
    46. WHEN 20 THEN salary * 1.2
    47. WHEN 30 THEN salary * 1.3
    48. END "details"
    49. FROM employees
    50. WHERE department_id IN (10,20,30);