函数

# 函数

指一段可以被直接调用的程序或代码，mysql已经内置了很多函数，直接调用即可。

## 常用函数分类

- 字符串函数
- 数值函数
- 日期函数
- 流程函数

### 字符串函数

CONCAT(S1,S2),字符串拼接，将S1,S2拼接成一个字符串
```sql
SELECT CONCAT('Hello',' ','World !') AS '问候';
```

LOWER(str),将字符串全部转为小写
```sql
SELECT LOWER('HELLO');
```

UPPER(str),将字符串全部转为大写
```sql
SELECT UPPER('hello');
```

LPAD(str,n,pad),左填充，用字符串pad对str左边进行填充，达到n个字符串长度
```sql
SELECT LPAD('joan',7,'-');
```

RPAD(str,n,pad),右填充，用字符串pad对str右边进行填充，达到n个字符串长度
```sql
SELECT RPAD('joan',7,'-');
```

TRIM(str),去掉字符串头部和尾部的空格
```sql
SELECT TRIM('  My name is chuck  ');
```

SUBSTRING(str,start,len),返回从字符串str从start位置起的len个长度的字符串,截取
```sql
SELECT SUBSTRING('ABCDEFGHIJKLMN',3,6); # 注意是从3开始数6个字符，而不是第三到第六个字符
```

示例：
```sql
-- 示例，使用0填充工号到5位数，工号字段需要为字符串类型
UPDATE employee SET workno = LPAD(workno,5,'0') WHERE id =3;
```

### 数值函数

CEIL(X),向上取整
```sql
-- CEIL
SELECT CEIL(1.5);
```

FLOOR(X),向下取整
```sql
-- FLOOR
SELECT FLOOR(3.14);
```

MOD(X,Y),返回x/y的模
```sql
-- MOD
SELECT MOD(6,4);
```

RAND(),返回0-1内的随机数
```sql
-- RAND
SELECT RAND();
```

ROUND(X,Y),求参数x的四舍五入的值，保留y位小数
```sql
-- ROUND
SELECT ROUND(3.14159,4);
```

示例：使用函数生成随机六位验证码
```sql
# 使用RAND()生成随机0-1的数乘以100万，再使用ROUND四舍五入取整,使用LPAD处理不足6位补0的操作
SELECT LPAD(ROUND(RAND()*1000000,0),6,0);
```

示例：生成随机六位大写字母验证码
```sql
# 使用RAND()生成随机数乘以26，再加上65使结果为65-90，通过CHAR将数字转换成ASCII中的大写字母，使用CONCAT连接6位随机字母
SELECT CONCAT(CHAR(ROUND((rand() * 26)+65,0)),CHAR(ROUND((rand() * 26)+65,0)),CHAR(ROUND((rand() * 26)+65,0)),CHAR(ROUND((rand() * 26)+65,0)),CHAR(ROUND((rand() * 26)+65,0)),CHAR(ROUND((rand() * 26)+65,0)));
```

### 日期函数

CURDATE(),返回当前日期
```sql
SELECT CURDATE();
```

CURTIME(),返回当前时间
```sql
SELECT CURTIME();
```

NOW(),返回当前日期和时间
```sql
SELECT NOW();
```

YEAR(date),获取指定date的年份
```sql
SELECT YEAR(CURDATE());
```

MONTH(date),获取指定date的月份
```sql
SELECT MONTH(CURDATE());
```

DAY(date),获取指定date的日期
```sql
SELECT DAY(CURDATE());
```

DATE_ADD(date,INTERVAL expr type),获取一个日期/时间值加上一个时间间隔expr后的时间值
```sql
# type支持多种格式，YEAR,MONTH,DAY,或者组合 YEAR_MONTH,中间的值需要'1,2,3'格式
SELECT DATE_ADD(NOW(),INTERVAL 70 DAY);
# 修改日期
UPDATE employee SET date = DATE_ADD(date,INTERVAL 10 DAY) WHERE name = 'joan';
```

DATEDIFF(date1,date2),返回起始时间date1和结束时间date2之间的天数
```sql
# 将date1-date2计算天数
SELECT DATEDIFF(curdate(),'2019-10-07');
# 计算出生天数
SELECT DATEDIFF(curdate(),'1998-08-24') AS '出生天数';
```

示例：
```sql
# 查询员工的入职天数并排序
SELECT name '姓名',date AS '入职日期', DATEDIFF(CURDATE(),date) AS '入职天数' FROM employee ORDER BY DATEDIFF(CURDATE(),date) DESC LIMIT 10;
```

### 流程函数

IF(value,t,f),如果value为true则返回t,否则返回f
```sql
SELECT IF(CURDATE()='2023-05-07','OK','ERROR');
```

IFNULL(value1,value2),如果value1不为空返回value1,否则返回value2
```sql
# 判断第一个value是否为空
SELECT IFNULL((SELECT workno FROM employee WHERE name='joan'),'FALSE');
```

CASE WHEN [VAL1] THEN [RES1] ... ELSE [Default] END,如果val1为true返回res1,否则返回default
```sql
# 格式，简单的 CASE 语句就是指使用简单 CASE 语句来检查表达式的值与一组唯一值的匹配。
CASE  <表达式>
   WHEN <值1> THEN <操作>
   WHEN <值2> THEN <操作>
   ...
   ELSE <操作>
END CASE;

# 示例
SELECT 
CASE WEEKDAY(NOW()) 
WHEN 0 THEN '星期一' 
WHEN 1 THEN '星期二'
WHEN 2 THEN '星期三' 
WHEN 3 THEN '星期四'
WHEN 4 THEN '星期五'
WHEN 5 THEN '星期六'
ELSE '星期天' END AS COLUMN1,NOW(),WEEKDAY(NOW()),DAYNAME(NOW());
```

CASE [expr] when [val1] then [res1] else [default] END,如果expr等于val1，返回res1,否则返回default
```sql
# 格式，为了执行更复杂的匹配，如范围，则可以使用可搜索 CASE 语句。 可搜索 CASE 语句等同于 IF 语句，但是它的构造更加可读。
CASE
    WHEN <条件1> THEN <命令>
    WHEN <条件2> THEN <命令>
    ...
    ELSE commands
END CASE;

# 示例
SELECT
name,date,
(CASE WHEN date <='2000-01-01' THEN '老员工'
WHEN date BETWEEN '2000-01-01' AND '2022-12-31' THEN '骨干员工'
ELSE '新入职员工' END) AS '员工属性'
FROM employee ORDER BY date;
```

### 其他
MD5(),将一个数字或字符进行md5运算，生成唯一32位字符串
```sql
SELECT MD5(1);
```

CHAR(),将数字转换为ASCII对照的字符
```sql
SELECT CHAR(65);# A
```