常见函数

通常分为 单行函数，和多行函数

根据使用方式的不同，可分为字符函数，数学函数

日期函数

字符函数

1 length

意义：获取参数值的字节个数

1
2
SELECT 
	LENGTH("join")

2 concat

意义：拼接字符串

1
2
3
4
SELECT 
	CONCAT(last_name," ",first_name) AS 姓名
FROM 
	employees

3 upper && lower

意义：全部大写或者小写

1
2
3
4
SELECT 
	CONCAT(UPPER(last_name), '_',LOWER(first_name)) AS 姓名
FROM 
	employees

4 substr substring

含义：索引从指定索引开始 最小为1

1
SELECT SUBSTR('李莫愁爱上xxx',2) AS output

输出：莫愁爱上xxx

5 instr

含义：返回字串得第一次出现得索引，如果找不到放回0

1
2
SELECT 
	INSTR('xxnnll','xx') AS output

输出：1

6 trim

含义：去掉字符串前后制定的字符

1
2
SELECT 
	TRIM('11' FROM '11刘迪迪11') AS output

输出：刘迪迪

7 lpad

含义：用指定的字符实现左边的字符填充
参数解释：第一个参数是源字符串。第二个参数是要填入的字符串长度，第三个参数就是填充的字符串

1
2
SELECT 
	LPAD('22',14,'13') AS output

输出：1313131313131322

8 rpad

含义：用制定的字符实现右边的字符填充 与7相同

1
2
SELECT 
	RPAD('22',14,'13') AS output

9 replace

含义：替换指定字符，
参数解释：第一个参数：源字符串，第二个参数，第二个字符串是源字符串中的字串，第三个字符串是要替换的字符串

1
2
SELECT
	REPLACE('赵敏爱上张无忌','赵敏','周芷若') AS output

输出：周芷若爱上张无忌

数学函数

1 round

含义：四舍五入，

参数解释：

一个参数的是直接对这个数进行四舍五入,

二个参数中第一个参数就是原目标数，第二个参数就是四舍五入要保留多少位有效数字

1
2
SELECT ROUND(12.2)
SELECT ROUND(12.231324,2) # 小数点保留两位

输出：

第一个输出为：12

第二个输出为：12.23

2 ceil

含义: 向上取整

1
SELECT CEIL(123.00)

3 floor

含义：向下取整 向数学中的值比较小进行取整，而不是绝对值

1
SELECT FLOOR(123.2)

4 truncat

含义：截断数字的串

1
SELECT TRUNCATE(1.62345,1);# 小数点后保留1位

输出：1.6

5.mod

含义：取余 mod(a,b) a-a/b*b

参数解释：

第一个参数就是除数

第二个参数就是被除数

1
2
3
4
SELECT MOD(10,-3)
SELECT MOD(10,3)
SELECT MOD(-10,3)
SELECT MOD(-10,-3)

输出

第一个输出：1

第二个输出：1

第三个输出：-1

第四个输出：-1

最终结果符号：只与第一个参数有关

日期函数

1 date

1
2
3
SELECT DATE(200811) # 输出 2020-08-11
select date(20080811)# 输出 2008-08-11
select date(2008811)# 输出 null

2 now

1
2
# now 放回当前系统日期
SELECT NOW();

3 curdate

1
2
# curdate 返回当前系统日期，不包含时间
SELECT CURDATE();

4 curtime

1
2
3
# curtime 返回当前时间 不包含日期

SELECT CURTIME();

5 year

1
2

SELECT YEAR(NOW()) AS 年

输出：

|年|

|2020|

1
SELECT YEAR('1998-1-1') AS 年

输出 1998

1
2
SELECT YEAR(hiredate) AS 年
FROM employees;

输出

年

1992

1992

6Month

1
2
# 输出月份
SELECT MONTH(NOW()) AS 月

输出：5

7 Monthname

1
2
# 输出月份的英文名
SELECT MONTHNAME(NOW()) AS 月

输出

月
2020

8 str_to_date

1
2
# str_to_date 将日期格式的字符转换成指定格式
SELECT STR_TO_DATE('2020-1-10','%Y-%c-%d') AS 格式

输出：2020-01-10

详细例子

1 查询入职日期为1999-4-3的员工信息
1
2
3
4
5
6
SELECT
	*
FROM
	employees
WHERE
	hiredate = '1992-4-3'

输出：

employee_id first_name last_name email phone_number job_id salary commission_pct manager_id department_id hiredate
100 Steven K_ing SKING 515.123.4567 AD_PRES 24000 90 ######
101 Neena Kochhar NKOCHHAR 515.123.4568 AD_VP 17000 100 90 ######
2 查询日期格式为 4-3 1999
1
2
3
4
5
6
SELECT
	*
FROM
	employees
WHERE
	hiredate = STR_TO_DATE('3-3 1998','%c-%d %Y');

输出

employee_id first_name last_name email phone_number job_id salary commission_pct manager_id department_id hiredate
105 David Austin DAUSTIN 590.423.4569 IT_PROG 4800 103 60 ######
106 Valli Pataballa VPATABAL 590.423.4560 IT_PROG 4800 103 60 ######

9 date_format

1
2
3
# date_format 将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日')
# 输出为 2020年06月30日

例子

1
2
3
4
5
6
7
8
9
# 查询有奖金的员工和入职日期(xx月/xx日 xx年）

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

输出

last_name date_format(hiredate,’%m月/%d日 %y年’)
Russell 12月/23日 02年
Partners 12月/23日 02年

其他函数

1
2
3
4
5
# 其他函数

SELECT VERSION();
SELECT DATABASE();
SELECT USER();

流程控制函数

1 if

1
2
3
4
5
6
SELECT IF(10 > 5,'大','小') # 输出为大
SELECT 
	last_name,
	IF(commission_pct IS NULL,'有奖金','没有') AS 备注
FROM
	employees

2 case

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
/*
	mysql语法
	case 要判断的表达式
	when 常量1 then 要显示的值1或语句
	when 常量2 then 要显示的值2或语句
	
	else 要显示的值n或语句n；
	end 结束
*/
/* 案例 ： 查询员工的工资 要求
部门号 = 30 显示的工资为1.1倍
部门号 = 40 显示的工资为1.2倍
部门号 = 50 显示的工资为1.3倍
其他部门： 显示原始工资
*/

SELECT 
	salary AS 原始工资,
	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
	
/* mysql 多重if else if 大于小于都判断
	case
	when 条件1 then 要显示的值1或语句1
	when 条件2 then 要显示的值2或语句2
	end
	
*/

/*
	查询员工的工资级别
	如果工资>20000 显示A级别
	如果工资>15000 显示B级别
	如果工资>10000 显示C级别
	否则显示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
	
	
SELECT 
	last_name,
	SUBSTR(last_name,1,1) AS 子字符串
FROM 
	employees
ORDER BY 
	子字符串