03.函数
函数是指一段可以直接被另一段程序调用的程序或代码。
MySQL中的函数主要分为以下四类： 字符串函数、数值函数、日期函数、流程函数。
1. 字符串函数
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", "!");lower : 全部转小写
SELECT LOWER("Hello MySQL!");upper : 全部转大写
SELECT UPPER("Hello MySQL!");lpad : 左填充
SELECT LPAD("Hello MySQL!", 50, "-");rpad : 右填充
SELECT RPAD("Hello MySQL!", 50, "-");trim : 去除空格
SELECT TRIM(" Hello World!!! ");substring : 截取子字符串
SELECT SUBSTRING("Hello World!", 1, 2);
由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0。
比如： 1号员工的工号应该为00001。
UPDATE emp SET workno = LPAD(workno, 5, '0');
2. 数值函数
常见的数值函数如下：
| 函数 | 功能 |
|---|---|
CEIL(x) |
向上取整 |
FLOOR(x) |
向下取整 |
MOD(x,y) |
返回x/y的模 |
RAND() |
返回0~1内的随机数 |
ROUND(x,y) |
求参数x的四舍五入的值，保留y位小数 |
ceil：向上取整
SELECT CEIL(3.1415926);floor：向下取整
SELECT FLOOR(3.1415926);mod：取模
SELECT MOD(2,4);rand：获取随机数
SELECT RAND();round：四舍五入
SELECT ROUND(3.1415926, 2);
通过数据库的函数，生成一个六位数的随机验证码。
思路： 获取随机数可以通过rand()函数，但是获取出来的随机数是在0-1之间的，所以可以在其基础上乘以1000000，然后舍弃小数部分，如果长度不足6位，补0SELECT LPAD(ROUND(RAND() * 1000000, 0), 6, '0');
3. 日期函数
常见的日期函数如下：
| 函数 | 功能 |
|---|---|
CURDATE() |
返回当前日期 |
CURTIME() |
返回当前时间 |
NOW() |
返回当前日期和时间 |
YEAR(date) |
获取指定date的年份 |
MONTH(date) |
获取指定date的月份 |
DAY(date) |
获取指定date的日期 |
DATE_ADD(date, INTERVAL export type) |
返回一个日期/时间值加上一个时间间隔expr后的时间值 |
DATEDIFF(date1, date2) |
返回起始时间date1 和 结束时间date2之间的天数 |
curdate：当前日期
SELECT CURDATE();curtime：当前时间
SELECT CURTIME();now：当前日期和时间
SELECT NOW();YEAR , MONTH , DAY：当前年、月、日
SELECT YEAR(NOW()); SELECT MONTH(NOW()); SELECT DAY(NOW());date_add：增加指定的时间间隔
SELECT DATE_ADD(NOW(), INTERVAL 70 YEAR);datediff：获取两个日期相差的天数
SELECT DATEDIFF('2023-01-02', '2022-03-15');
查询所有员工的入职天数，并根据入职天数倒序排序。
思路： 入职天数，就是通过
当前日期 - 入职日期，所以需要使用datediff函数来完成。SELECT `name`,DATEDIFF(CURDATE(), entrydate) AS 'workno_day' FROM `emp` ORDER BY workno_day DESC;
4. 流程函数
流程函数也是很常用的一类函数，可以在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默认值 |
if
SELECT IF(FALSE, "OK", "ERROR"); SELECT IF(TRUE, "OK", "ERROR");
ifnull
SELECT IFNULL('OK', "DEFAULT"); SELECT IFNULL('', "DEFAULT"); SELECT IFNULL(NULL, "DEFAULT");
case when then else
查询emp表的员工姓名和工作地址 (北京/上海 ----> 一线城市 , 其他 ----> 二线城市)
SELECT `name`, `workaddress`, (CASE workaddress WHEN "北京" THEN "一线城市" WHEN "上海" THEN "一线城市" ELSE "二线城市" END) AS "城市等级" FROM `emp`;
为学生的成绩划分等级（60分及以下为不及格，60-80分为及格，80及以上为优秀）
-- 建表SQL CREATE TABLE score( id int COMMENT 'ID', name VARCHAR(20) COMMENT '姓名', math INT COMMENT '数学', english INT COMMENT '英语', chinese INT COMMENT '语文' ) COMMENT '学员成绩表'; -- 数据SQL INSERT INTO score(id, name, math, english, chinese) VALUES (1, 'Tom', 67, 88, 95 ), (2, 'Rose' , 23, 66, 90),(3, 'Jack', 56, 98, 76);SELECT `name`, `math` AS "数学成绩", (CASE WHEN `math` >= 80 THEN "优秀" WHEN `math` >= 60 THEN "及格" ELSE "不及格" END) AS "数学等级", `english` AS "英语成绩", (CASE WHEN `english` >= 80 THEN "优秀" WHEN `english` >= 60 THEN "及格" ELSE "不及格" END) AS "英语等级", `chinese` AS "语文成绩", (CASE WHEN `chinese` >= 80 THEN "优秀" WHEN `chinese` >= 60 THEN "及格" ELSE "不及格" END) AS "语文等级" FROM score;
