MySQL函数学习(五)-----流程控制函数

mysql 文章 2022-07-15 14:24 0 全屏看文

# md函数笔记五

注:笔记旨在记录

五、MySQL 流程控制函数

函 数 名 称 作 用 完 成
1 IF 条件判断
2 IFNULL 判空判断
3 CASE 求数量

0.

0.1 num表:

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;

聚合函数00_num表.png


1. IF() -- 条件判断

1.1. 函数:

  • IF(expr1,expr2,expr3) : expr1为判断条件,符合则返回expr2,否则expr3

1.2. sql示例:

SELECT rand_int_num,IF(rand_int_num < 15,"√","×") from num

流程控制函数01_IF.png


2. IFNULL() -- 判空判断

2.1. 函数:

  • IFNULL(expr1,expr2) :expr1为判断的值或字段,为空则返回expr2

2.2. sql示例:

SELECT rand_int_num,IFNULL(rand_int_num,"√") from num

流程控制函数02_IFNULL.png


3. CASE -- 求总数

3.1. 函数:

注: 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;

3.2. sql示例:

  • 写法一:

      SELECT rand_int_num,   CASE rand_int_num       WHEN 14 THEN " == 14"      WHEN 44 THEN " == 44"      ELSE " != 14"  END as is14  from num

流程控制函数03_CASE_01.png

  • 写法二:

      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

流程控制函数03_CASE_02.png

    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());

流程控制函数03_CASE_03.png


-EOF-