mysql基础复习——流程控制函数

1、IF()

IF( 表达式, true, false )
if else 的效果
当表达式结果为真，返回true的值，否则返回false的值

SELECT 
	IF(LENGTH(student.`stu_name`)>5, "yes", "no") AS "IF", 
	student.`stu_name` 
FROM 
	student 

2、CASE()

写法一：
CASE 表达式
WHEN [常量1] THEN 表达式1或值1
WHEN [常量2] THEN 表达式2或值2
WHEN [常量3] THEN 表达式3或值3
WHEN [常量4] THEN 表达式4或值4
WHEN [常量5] THEN 表达式5或值5
ELSE 其他表达式或值
END

SELECT id,
	CASE id
	WHEN 100 THEN "一百"
	WHEN 101 THEN "一百零一"
	WHEN 102 THEN "一百零二"
	WHEN 104 THEN "一百零四"
	WHEN 105 THEN "一百零五"
	ELSE "不到一百"
	END AS "CASE"
FROM student

写法二：
CASE
WHEN [表达式1] THEN 表达式1或值1
WHEN [表达式2] THEN 表达式2或值2
WHEN [表达式3] THEN 表达式3或值3
WHEN [表达式4] THEN 表达式4或值4
WHEN [表达式5] THEN 表达式5或值5
ELSE 其他表达式或值
END

SELECT stu_name,
	CASE
	WHEN LENGTH(stu_name)>0&&LENGTH(stu_name)<=3 THEN RPAD(LENGTH(stu_name),LENGTH(LENGTH(stu_name))+3,"*")
	WHEN LENGTH(stu_name)>0&&LENGTH(stu_name)<=6 THEN RPAD(LENGTH(stu_name),LENGTH(LENGTH(stu_name))+6,"*")
	WHEN LENGTH(stu_name)>0&&LENGTH(stu_name)<=9 THEN RPAD(LENGTH(stu_name),LENGTH(LENGTH(stu_name))+9,"*")
	WHEN LENGTH(stu_name)>0&&LENGTH(stu_name)<=12 THEN RPAD(LENGTH(stu_name),LENGTH(LENGTH(stu_name))+12,"*")
	WHEN LENGTH(stu_name)>0&&LENGTH(stu_name)<=15 THEN RPAD(LENGTH(stu_name),LENGTH(LENGTH(stu_name))+15,"*")
	ELSE "太长了"
	END AS "CASE"
FROM student