函数

字符函数

LENGTH(str)：获取参数值的字节个数

SELECT LENGTH("高桥李依TAKAHASHI") # 21

CONCAT(str1,str2,…)：拼接字符串

SELECT CONCAT(last_name,"_",first_name) FROM employees

UPPER(str)/LOWER(str)：大写/小写

SELECT UPPER("aimer")
SELECT LOWER("AIMER")
SELECT CONCAT(UPPER(last_name),"_",LOWER(first_name)) FROM employees

SUBSTR()/SUBSTRING()：截取字符

索引从1开始
截取从指定索引处的后面所有字符

SELECT SUBSTR("高桥李依小原好美",5)

截取从指定索引处的指定字符长度的字符

SELECT SUBSTR("高桥李依小原好美",1,4)

案例：姓名中首字符大写，其他字符小写，然后用下划线拼接

SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),"_",LOWER(SUBSTR(last_name,2)))
FROM employees

INSTR()：返回要求的第一次出现的索引

SELECT INSTR("高桥李依小原好美","小原好美") # 5

TRIM()：清除前后空格或指定字符

SELECT TRIM("   高桥李依   ")
SELECT TRIM("a" FROM "aaa高桥李依aaa") # 只清除前后字符

LPAD()：用指定字符来左填充指定长度

SELECT LPAD("高桥李依",10,"*")

RPAD()：用指定字符来右填充指定长度

SELECT RPAD("高桥李依",10,"*")

REPLACE()：替换所有指定字符

SELECT REPLACE("高桥李依Aimer","高桥李依","小原好美")

数学函数

ROUND(X)：四舍五入/保留小数点位数

SELECT ROUND(1.65)
SELECT ROUND(1.567,2) # 保留两位小数

CEIL(X)：向上取整

# 返回大于等于该参数的最小整数
SELECT CEIL(1.0002) # 2
SELECT CEIL(-1.02) # -1

FLOOR(X)：向下取整

# 返回小于等于该参数的最大整数
SELECT FLOOR(1.98) # 1
SELECT FLOOR(-9.99) # -10

TRUNCATE()：截断

# 保留小数点后指定的位数
SELECT TRUNCATE(1.65,1) # 1.6

MOD()：取余

# MOD(a,b) = a-a/b*b
# 10-10/3*3 = 10-3*3 = 1
SELECT MOD(10,3) # 1
SELECT MOD(-10,-3) # -1

日期函数

NOW()：返回当前系统日期和时间

SELECT NOW()

CURDATE()：返回当前系统日期(不包含时间)

SELECT CURDATE()

CURTIME()：返回当前时间(不包含日期)

SELECT CURTIME()

年、月、日、小时、分钟、秒

SELECT YEAR("2020-04-29")
SELECT MONTH("2020-04-29")
SELECT MONTHNAME("2020-04-29") # 出现月名
SELECT DAY()
SELECT HOUR()
SELECT MINUTE()
SELECT SECOND()

STR_TO_DATE()：将日期格式的字符转换成指定格式的日期

SELECT STR_TO_DATE("20-06-2020","%d-%m-%Y")

项目	Value
%Y	四位数年份
%y	两位数年份
%m	月份(01,02,03…)
%c	月份(1,2,3…)
%d	日
%H	小时(24小时制)
%h	小时(12小时制)
%i	分钟
%s	秒

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

SELECT * FROM employees WHERE hiredate = STR_TO_DATE("4-3-1992","%m-%d-%Y")

DATE_FORMAT():将日期转换为字符

SELECT DATE_FORMAT(NOW(),"%Y年%m月%d日")

案例：查询有奖金的员工名和入职日期

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

其他函数

SELECT VERSION() # 查看当前版本号
SELECT DATABASE() # 查看当前数据库
SELECT USER() # 查看当前用户

流程控制函数

IF函数

# 类似三元运算符
SELECT IF(10 > 5,"大","小")
SELECT last_name,commission_pct,IF(commission_pct IS NULL,"无奖金","有奖金")
FROM employees

CASE函数

使用方法一

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

/* 案例：查询员工的工资
部门号 = 30，显示工资为1.1倍
部门号 = 40，显示工资为1.2倍
部门号 = 50，显示工资为1.3倍
其他部门，显示工资为原工资
*/
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
FROM employees

使用方法二

/*
	CASE
	WHEN 条件1 THEN 显示的值1或语句1
	WHEN 条件2 THEN 显示的值2或语句2
	...
	ELSE 显示的值n或语句n
	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

分组函数

统计使用，又称为聚合函数或统计函数或组函数
SUM 求和 AVG 平均值 MAX 最大值 MIN 最小值 COUNT 计算个数

SELECT SUM(salary) FROM employees;
SELECT COUNT(salary),AVG(salary) FROM employees;

SUM，AVG 一般用于处理数值型
MAX，MIN，COUNT 可以处理任何类型
以上分组函数都忽略null值

可以和DISTINCT搭配使用

SELECT SUM(DISTINCT salary) FROM employees

一般使用 count(*) 来统计行数
和分组函数一同查询的字段一般要求使用group by后的字段

DATEDIFF()：查询相差天数

SELECT DATEDIFF("2020-12-24","2020-06-20")