2.1 函数

• 返回目录 : MySQL 学习笔记
• 上一章 : 1.4 数据库初识 DCL

---

函数

字符串函数

MySQL中内置类很多字符串函数，常用的几个如下

函数	功能
CONCAT(S1,S2,...Sn)	字符串拼接，将S1,S2,...Sn拼接成一个字符串
LOWER(str)	将字符串str全部转为小写
UPPER(str)	将字符串str全部转为大写
LPAD(str,n,pad)	左填充,用字符串pad对str的左边进行填充,达到n个字符串长度
RPAD(str,n,pad)	右填充,用字符串pad对str的右边进行填充,达到n个字符串长度
TRIM(str)	去掉字符串头部和尾部的空格
SUBSTRING(str,start,len)	截取字符串,返回字符串str从start位置起len个长度的字符串

• 示例
  • CONCAT拼接 : SELECT CONCAT('Hello', 'MySQL');
    • 执行结果 : HelloMySQL
  • LOWER转小写 : SELECT LOWER('HelloMySQL');
    • 执行结果 : hellomysql
  • UPPER转大写 : SELECT UPPER('HelloMySQL');
    • 执行结果 : HELLOMYSQL
  • LPAD左拼接 : SELECT LPAD('hey', 5, '-');
    • 执行结果 : --hey
  • RPAD右拼接 : SELECT RPAD('hey', 5, '-');
    • 执行结果 : hey--
  • TRIM首尾去空格 : SELECT TRIM(' Hello MySQL ');
    • 执行结果 : 'Hello MySQL'前后空格去掉,中间空格保留
  • SUBSTRING字符串截取 : SELECT SUBSTRING('Hello MySQL', 1, 5);
    • 执行结果 : Hello

数值函数

• 常见函数如下 :

  函数	功能
  CEIL(x)	向上取整
  FLOOR(x)	向下取整
  MOD(x,y)	返回x/y的模
  RAND()	返回0-1内的随机数
  ROUND(x,y)	求参数x的四舍五入,保留y为小数

• 示例

  • CEIL向上取整 : SELECT CEIL(1.1);
    • 执行结果 : 2
  • FLOOR向下取整 : SELECT FLOOR(1.9);
    • 执行结果 : 1
  • MOD取模 : SELECT MOD(7,4);
    • 执行结果 : 3 (7除4取余)
  • RAND随机数 : SELECT RAND();
    • 执行结果 : 随机
  • ROUND四舍五入 : SELECT ROUND(2.345, 2);
    • 执行结果 : 2.35

日期函数

• 常见函数

  函数	功能
  CURDATE()	返回当前日期
  CURTIME()	返回当前时间
  NOW()	返回当前日期和时间
  YEAR(date)	获取指定date的年份
  MONTH(date)	获取指定date的月份
  DAY(date)	获取指定date的日期
  DATE_ADD(date, INTERVAL expr type)	返回一个日期/时间值加一个时间间隔expr后的时间值
  DATEDIFF(date1, date2)	返回起始时间date1和结束时间date2之间的天数

• 示例

  • CURDATE 当前日期 : SELECT CURDATE();
    • 结果 : 2023-09-15
  • CURTIME 当前时间 :SELECT CURTIME();
    • 结果 : 15:41:18
  • NOW 当前时间和日期 :SELECT NOW();
    • 结果 : 2023-09-15 15:41:35
  • YEAR 获取指定date的年份 :SELECT YEAR(NOW());
    • 结果 : 2023
  • MONTH 获取指定date的月份 :SELECT MONTH(NOW());
    • 结果 : 9
  • DAY 获取指定date的日份 :SELECT DAY(NOW())();
    • 结果 : 15
  • DATE_ADD
    1. 当前日期+70天 :SELECT DATE_ADD(NOW(), INTERVAL 70 DAY);
       • 结果 : 2023-11-24 15:45:19
    2. 当前日期+70月 :SELECT DATE_ADD(NOW(), INTERVAL 70 MONTH);
       • 结果 : 2029-07-15 15:46:16
    3. 当前日期+70年 :SELECT DATE_ADD(NOW(), INTERVAL 70 YEAR);
       • 结果 : 2093-07-15 15:46:16
  • DATEDIFF
    1. 日期1和日期2的差(前大,后小) :
       SELECT DATEDIFF('2021-12-01', '2021-11-01');
       • 结果 : 30
    2. 日期1和日期2的差(前小,后大) :
       SELECT DATEDIFF('2021-11-01', '2021-12-01');
       • 结果 : -30

流程控制函数

流程公职函数也是很常用的一类函数，可在SQL语句中实现条件筛选，从而提高语句效率。

• 常见函数

  函数	功能
  IF(value, t, f)	如果value为true, 则返回t,否则返回f
  IFNULL(value1, value2)	如果value1不为空, 返回value1, 否则返回value2
  CASE WHEN [val1] THEN [res1] ... ELSE [default] END	如果val1为true, 返回res1, ... 否则返回default默认值
  CASE [expr] WHEN [val1] THEN [res1] ... ELSE [default] END	如果expr的值等于val1, 返回res1, ... 否则返回default默认值

• 注意

  • '' 为无内容字符串,它不是NULL;

• 示例

  • IF 判断
    1. true: SELECT IF(true, 'OK', 'ERROR');
       • 结果 : OK
    2. false: SELECT IF(false, 'OK', 'ERROR')
       • 结果 : ERROR
  • IFNULL 空判断
    1. 'OK' : SELECT IFNULL('OK', 'Default');
       • 结果 : OK
    2. '' : SELECT IFNULL('', 'Default');
       • 结果 : ''
    3. NULL : SELECT IFNULL(NULL, 'Default');
       • 结果 : Default
  • CASE
    1. true : SELECT CASE WHEN true THEN 'ABC' ELSE 'default' END;
       • 结果 : ABC
    2. false : SELECT CASE WHEN false THEN 'ABC' ELSE 'default' END;
       • 结果 : default
  • CASE 比较 :
    1. 123 : SELECT CASE '123' WHEN '123' THEN 'ABC' ELSE 'default' END
       • 结果 : ABC
    2. 456 : SELECT CASE '456' WHEN '123' THEN 'ABC' ELSE 'default' END;
       • 结果 : default

---

• 返回目录 : MySQL 学习笔记
• 上一章 : 1.4 数据库初识 DCL

---

练习

字符串函数

题1

• 问 : 由于业务需求更变，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0。例如：1号员工的工号应该为00001。
• 答 :

-- 查询 id 对查询结果 补0; 不更改表的数据内容
SELECT lpad(id, 5 , '0') format_id FROM emp;
-- 更改 数据内容 work_no 不足补0;
UPDATE emp SET work_no = LPAD(work_no, 5 , '0');

---

数值函数

题1

• 问 : 通过数据库函数,生产一个六位数的随机验证码.
• 答 :

-- 1. 生成一个随机小数 0-1 RAND()
-- 2. 给随机数 * 1000000 整数位长度变成6位
-- 3. 特殊情况会生成0.00123456, 乘 1000000 得 1234.56
-- 4. 给数值取整.
-- 5. 采用左/右补位,
SELECT LPAD(ROUND(RAND() * 1000000, 0), 6, 0);

---

日期函数

题1

• 问 : 查询所有员工的入职天数,并根据入职天数倒序排序
• 答 :

SELECT name , DATEDIFF(CURDATE() , entry_date) AS entry_diff FROM emp ORDER BY entry_diff DESC ;

---

流程控制函数

题1

• 问 : 查询emp表的员工姓名 和工作地址,如果是北京/上海显示一线城市,否则显示二线城市
• 答 :

SELECT
    name,
    work_address,
    case work_address
        when '北京'
            then '一线城市'
        when '上海'
            then '一线城市'
        else '二线城市'
    end as '工作地址'
FROM emp;

---

题2

• 问 : 统计班级各个学院的成绩,展示规则如下:

  • 大于等于 85, 展示优秀
  • 大于等于 60, 展示及格
  • 否则, 展示不及格

• 准备练习数据 :

  -- 创建学生成绩表
  CREATE TABLE score(
      id      INT         COMMENT 'ID',
      name    VARCHAR(20) COMMENT '姓名',
      math    INT         COMMENT '数学',
      english INT         COMMENT '英语',
      chinese INT         COMMENT '语文'
  ) COMMENT '学生成绩表';
  
  -- 插入学生成绩数据
  INSERT INTO score(id, name, math, english, chinese) VALUES
  (1, 'Tom', 67, 88, 95),
  (1, 'Rose', 23, 66, 90),
  (1, 'Jack', 56, 98, 76);
  
  -- 查看表内容
  SELECT
      id,
      name,
      math,
      english,
      chinese
  FROM score;
  

• 答 :

  
  SELECT
      id,
      name AS '姓名',
      CASE WHEN math >= 85 THEN '优秀' WHEN math >= 60 THEN '及格' ELSE '不及格' END AS '数学',
      CASE WHEN english >= 85 THEN '优秀' WHEN english >= 60 THEN '及格' ELSE '不及格' END AS '英语',
      CASE WHEN chinese >= 85 THEN '优秀' WHEN chinese >= 60 THEN '及格' ELSE '不及格' END AS '语文'
  FROM score;
  

---

• 返回目录 : MySQL 学习笔记
• 上一章 : 1.4 数据库初识 DCL