注:笔记旨在记录
函 数 名 称 | 作 用 | 完 成 | |
---|---|---|---|
1 | IF | 条件判断 | 勾 |
2 | IFNULL | 判空判断 | 勾 |
3 | CASE | 求数量 | 勾 |
CREATE TABLE `anyot`.`Untitled` (`id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,`rand_int_num` int NULL DEFAULT NULL,`rand_decimal_num` decimal(65, 2) NULL DEFAULT NULL,`rand_string_num` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
IF(expr1,expr2,expr3)
: expr1为判断条件,符合则返回expr2,否则expr3SELECT rand_int_num,IF(rand_int_num < 15,"√","×") from num
IFNULL(expr1,expr2)
:expr1为判断的值或字段,为空则返回expr2SELECT rand_int_num,IFNULL(rand_int_num,"√") from num
注: case结合when,else,end使用;其中else可以省略,但end一定要加。
写法一:
CASE case_value WHEN when_value THEN statement_list WHEN ... ELSE statement_list END;
写法二:
CASE WHEN expr1 THEN statement_list WHEN ... ELSE statement_list END;
写法一:
SELECT rand_int_num, CASE rand_int_num WHEN 14 THEN " == 14" WHEN 44 THEN " == 44" ELSE " != 14" END as is14 from num
写法二:
SELECT rand_int_num, CASE WHEN rand_int_num = 14 THEN " == 14" ELSE " != 14" END as is14 ,rand_string_num, CASE WHEN rand_string_num = "a99" THEN " == a99" ELSE " != a99" END as isa99 from num
SELECT CASE WHEN WEEKDAY(NOW())=0 THEN '星期一' WHEN WEEKDAY(NOW())=1 THEN '星期二' WHEN WEEKDAY(NOW())=2 THEN '星期三' WHEN WEEKDAY(NOW())=3 THEN '星期四' WHEN WEEKDAY(NOW())=4 THEN '星期五' WHEN WEEKDAY(NOW())=5 THEN '星期六' WHEN WEEKDAY(NOW())=6 THEN '星期天' END AS COLUMN1,NOW(),WEEKDAY(NOW()),DAYNAME(NOW());