MySQL DQL 语言 - 常见函数（2 - 单行函数）

MySQL DQL 语言 - 常见函数（2 - 单行函数）

单行函数定义

下面主要分为：字符函数，数学函数，日期函数，流程控制函数，其他函数，包括：

1. 字符函数：LENGTH CONCAT SUBSTR INSTR TRIM UPPER LOWER LPAD RPAD REPLACE
2. 数学函数：ROUND CEIL FLOOR TRUNCATE MOD
3. 日期函数：NOW CURDATE CURTIME YEAR MONTH MONTHNAME DAY HOUR MINUTE SECOND STR_TO_DATE DATE_FORMAT
4. 其他函数：VERSION DATABASE USER
5. 控制函数：IF CASE

---

字符函数

LENGTH

LENGTH(str)；

功能：
返回参数的字节个数。

字符编码
utf8	一个汉字 3 个字节，一个英文字母 1 个字节。
gbk	一个汉字 2 个字节。

SELECT LENGTH('abc');	#3
SELECT LENGTH('列bv');	#5

---

CONCAT

CONCAT(str1, str2, ...);

功能：
拼接字符。

SELECT CONCAT('last_name', '_', 'end');	#last_name_end

---

UPPER，LOWER

UPPER(str);
LOWER(str);

功能：
UPPER 将小写字符转为大写。
LOWER 将大写字符转为小写。

案例：将姓变大写，名变小写，然后拼接：

SELECT CONCAT(UPPER(last_name), LOWER(first_name)) name 
FROM employees;

name
K_INGsteven
KOCHHARneena

函数可以嵌套调用，即：一个函数的返回值可以作为另一个函数的参数。

---

SUBSTR，SUBSTRING

SUBSTR(str);

截取从指定索引处后面 所有字符。
SUBSTR(str, pos);

截取从指定索引处 指定 字符长度 的字符
SUBSTR(str, pos, len);

含义：

1. pos ：位置。
2. len ：长度。

注：

1. 除了 LENGTH 是 字节 长度，其他基本都是 字符 长度。
2. SUBSTR 和 SUBSTRING 功能相同。

功能：

SELECT SUBSTR('一个函数的返回', 5) out_put;

output
的返回

注：

1. 索引均从 1 开始。

SELECT SUBSTR('一个函数的返回', 5, 1) out_put;

output
的

案例：姓名中首字符大写，其他字符小写，然后用 _ 拼接，显示出来：

SELECT CONCAT(UPPER(SUBSTR(last_name, 1, 1)),'_',LOWER(SUBSTR(last_name, 2))) FROM employees 姓名;

姓名
K__ing
K_ochhar
D_e haan
H_unold

---

INSTR

INSTR(str, substr);

功能：
返回字串 substr 第一次在 str 中出现的索引，如果找不到则返回 0。

SELECT INSTR('1索引均从1开始','1') AS out_put;

out_put
1

---

TRIM

功能：
去前后空格。

SELECT LENGTH(TRIM('    SS    ')) AS output;	#2
SELECT TRIM('a' FROM 'aaBaaCaaa') AS output;		#BaaC
SELECT TRIM('aa' FROM 'aaBaaCaaa') AS output;		#BaaCa

LPAD

LPAD(str, len, padstr);

功能：
用指定的字符 padstr 实现 左 填充，使达到 len 个字符（注意不是字节。）如果 padstr 长度大于 len 则从 右侧截断到剩下 len 个字符。（两个都是从 右侧 截断。）

SELECT LPAD('什么', 7, '*') AS output;

output
*****什么

SELECT LPAD('什么呀', 1, '*') AS output;

output
什

---

RPAD

RPAD(str, len, padstr);

功能：
用指定的字符 padstr 实现 右 填充，使达到 len 个字符（注意不是字节。）如果 padstr 长度大于 len 则从 右侧截断到剩下 len 个字符。

SELECT RPAD('什么', 7, '*') AS output;

output
什么*****

SELECT RPAD('什么呀', 1, '*') AS output;

output
什

---

REPLACE

REPLACE(str, from_str, to_str);

功能：
将 str 中的所有 from_str 替换为 to_str。

SELECT REPLACE("AAAAAABC", "A", "D") AS out_put;

out_put
DDDDDDBC

---

数学函数

ROUND

ROUND(X);
ROUND(X, D)

功能：

1. 四舍五入。
2. D ：保留 D 位小数。没有 D 可以看作 D 取 0。
3. 看 D + 1 位 <= 4 则舍，>= 5 则进。

SELECT ROUND(1.45);			#1
SELECT ROUND(-1.55);		#-2
SELECT ROUND(1.5446, 2);	#1.54 4舍
SELECT ROUND(1.5356, 2);	#1.54 5进位 3->4

---

CEIL

CEIL(X);

功能：

1. 向上取整，返回 >= X 的最小整数。

SELECT CEIL(1.01);	#2
SELECT CEIL(1.00);	#1
SELECT CEIL(-1.01);	#-1

---

FLOOR

FLOOR(X);

功能：

1. 向下取整，返回 <= X 的最小整数。

SELECT FLOOR(-9.9);	#-10;

---

TRUNCATE

TRUNCATE(X, D);

功能：

1. 截断：保留 X 的小数点后 D 位。

SELECT TRUNCATE(1.23456, 3);	#1.234
SELECT TRUNCATE(123456, 3);		#123456

---

MOD

MOD(N, M);

功能：

1. 返回 N % M 。
2. MOD(N, M) 相当于 N - N / M *M。

日期函数

参数的类型是日期类型。

---

NOW

返回当前系统日期 + 时间。

SELECT NOW();

---

CURDATE()

返回当前系统日期，不包括时间。

---

CURTIME

返回当前时间，不包括日期。

---

YEAR

获取指定的部分：年 YEAR。 月 MONTH，日 DAY，小时 HOUR，分钟 MINUTE，秒 SECOND。

SELECT YEAR(NOW());							#2021
SELECT YEAR('1998-1-1');					#1998
SELECT YEAR(hiredate) 年 FROM employees;	#1992...

MONTH，MONTHNAME

获取指定的部分： 月。
MONTH 返回中文。
MONTHNAME 返回英文。

---

STR_TO_DATE

STR_TO_DATE(str, format);

将日期格式的字符 str 转换成指定格式 format 的日期。

STR_TO_DATE('9-13-1999', '%m-%d-%Y');	#1999-09-03

格式符	功能
%Y	4 位的年份（1999）
%y	2 位的年份（99）
%m	月份（01，02，… 12）
%c	月份（1， 2，… 12）
%d	日（01， 02，… ）
%H	小时（24 小时制）
%h	小时（12 小时制）
%i	分钟（00， 01，… 59）
%s	秒（00，01，… 59）

案例：查询入职日期为 1992-4-3 的员工信息：

SELECT * 
FROM employees	
WHERE hiredate = '1992-4-3';

可以直接使用 = 是因为默认的日期格式就是 '1992-4-3' 这种。但当获取不定格式的日期进行查询时，需要处理，如 4-3 1992 这种。

SELECT * 
FROM employees	
WHERE STR_TO_DATE('4-3 1992', '%c-%d %Y');

---

DATE_FORMAT

DATE_FORMAT(date, format);

将日期 date 转换成 format 样式的字符。

SELECT DATE_FORMAT('1998-4-6', '%Y年-%m月-%d日');	#1998年-04月-06日

案例：查询有奖金的员工名和入职日期（格式：xx月/xx日 xx年）：

SELECT last_name, DATE_FORMAT(hiredate, '%m月/%d日 %y年') AS 入职日期
FROM employees
WHERE commission_pct IS NOT NULL;

---

其他函数

SELECT VERSION();
SELECT DATEBASE();
SELECT USER();

---

流程控制函数

IF

IF(expr1, expr2, expr3);

对 expr1 进行判断，为 TRUE 则返回 expr2，FALSE 则返回 expr3。

SELECT IF(10 > 5, 'a', 'b');	#a

---

CASE 1

功能：
1.java 中 switch case 的效果：主要用于 等值 判断

switch(变量或表达式){
	case 变量1: 语句1; break;
	...
	default: 语句n; break;
}

在 SQL 中：

CASE 要判断的字段或表达式
WHEN 常量1 THEN 要显示的值1或语句1;
WHEN 常量2 THEN 要显示的值2或语句2;
...
ELSE 要显示的值n或语句n;
END

注：
THEN 后加值，则不用加 ;，语句，则加 ;。

案例：查询员工的工资，要求：

1. 部门号 = 30，显示的工资为 1.1 倍
2. 部门号 = 40，显示的工资为 1.2 倍
3. 部门号 = 50，显示的工资为 1.3 倍
4. 其他部门，显示工资为原工资。

所以，CASE 那些，就要放到 SELECT 后面，放到 SELECT 后面就相当于作为一个 表达式 使用，所以 THEN 后面就不能放 表达式 了 为什么？。
在存储过程里，CASE 可以单独作为 语句，不在需要和 SELECT 配合使用，自身作为 表达式 使用。就可以在 THEN 后面加表达式了。

SELECT salary 原始工资, department_id,
CASE department_id
WHEN 30 THEN salary * 1.1
WHEN 40 THEN salary * 1.2
WHEN 50 THEN salary * 1.3
ELSE salary
END AS 新工资
FROM employees;

整条 ELSE 作为 SELECT 的一个表达式。
|原始工资 |department_id | 新工资|
|–|--|–|
|7800.00 |100| 7800.00|
|6900.00 |100 |6900.00|
|11000.00| 30 |12100.00|

---

CASE 2

类似于 java 中的多重 if ：主要用于 区间 判断。

if(条件1) {
	语句1:
} else if(条件2) {
	语句2:
} else {
	语句n:
}

在 SQL 中

CASE
WHEN 条件1(true or false) THEN 要显示的值1或语句1;
WHEN 条件2(true or false) THEN 要显示的值2或语句2;
...
ELSE 要显示的值n或语句n
END

注：

1. 上一个 WHEN 后面加的 值 ，这个加的 条件。
2. THEN 后加值，则不用加 ;，语句，则加 ;。
3. END 表示结束。
4. 如果不写 ELSE ，如果 WHEN 条件都不符合，则自动设为 NULL。

案例：查询员工的工资情况：

1. 如果工资 > 20000，显示 A 级别。
2. 如果工资 > 15000，显示 B 级别。
3. 如果工资 > 10000，显示 C 级别。
4. 否则，显示 D 级别。

SELECT salary, CASE
WHEN salary > 20000 THEN 'A'
WHEN salary > 15000 THEN 'B'
WHEN salary > 10000 THEN 'C'
ELSE 'D'
END AS 公司级别
FROM employees;

salary	公司级别
24000.00	A
17000.00	B
17000.00	B
9000.00	D

为什么 24000 应该即 > 20000 也 > 15000… 最终显示 为 A?

将 WHEN 的顺序调换一下：

SELECT salary, CASE
#WHEN salary > 20000 THEN 'A'
#WHEN salary > 15000 THEN 'B'
WHEN salary > 10000 THEN 'C'
WHEN salary > 20000 THEN 'A'
WHEN salary > 15000 THEN 'B'
ELSE 'D'
END AS 公司级别
FROM employees;

salary	公司级别
24000.00	C
17000.00	C
17000.00	C
9000.00	D

== 执行到 THEN 后，直接开始查表中的下一列数据？==