一起来学MySQL—常用内置函数

单行函数

字符函数

函数名	作用
concat	拼接字符串。注意：NULL 和 任何字符串拼接，最后的结果都是 NULL
substr	截取字符串。索引从 1开始
upper	变大写
lower	变小写
replace	替换
length	获取 字节长度
trim	默认是去除前后空格，也可以去除指定的字符：TRIM (‘H’ FROM ‘HelloWorld’)，结果为 elloWorld
ltrim	去除左边空格
rtrim	去除右边空格
lpad	左填充，LPAD(24000,10,’*’) 结果为 *****24000
rpad	右填充，RPAD(24000,10,’*’) 结果为 24000*****
instr	获取子串第一次出现的索引

数学函数

函数名	作用
ceil	向上取整
floor	向下取整
round	四舍五入
truncate	截断
mod	取余
rand	获取随机数，返回 0-1 之间的小数

日期函数

函数名	作用
now	返回当前日期+时间
curdate	返回当前日期
curtime	返回当前时间
year	返回年
month	返回月
day	返回日
hour	小时
minute	分钟
second	秒
date_format	将日期转换成字符，DATE_FORMAT(2018/6/6,%Y 年%m 月%d 日) 2018 年06 月06 日。注意：转换成的字符串就是自己定义的格式
str_to_date	将字符转换成日期，STR_TO_DATE(‘9-13-1999’,’%m-%d-%Y’) 1999-09-13。注意：DATE 的格式要和 STR 的格式一一对应才能转换
datediff	返回两个日期相差的天数
monthname	以英文形式返回月

分组函数

函数名	作用
max	最大值
min	最小值
sum	求和
avg	平均值
count	计算个数

特点：

• 以上分组函数都忽略 null（count(*) 除外）
• 都可以搭配 distinct 使用，实现去重的统计
  select sum(distinct 字段) from 表;

注意：

• sum 和 avg 一般用于处理数值型，max、min、count 可以处理任何数据类型。
• 对于 count() 函数：MyISAM 存储引擎，count(*) 效率最高；InnoDB 存储引擎，count(*) 和 count(1) 效率 > count(字段)

流程控制函数

• if() 函数
  类似于三元表达式，if(条件表达式，表达式1，表达式2)：如果条件表达式成立，返回表达式1，否则返回表达式2

• case 语句
  case语句的使用有两种情况，一种是等值判断，一种是条件判断。

case情况1：等值判断

case 变量 或 表达式 或 字段
when 常量1 then 值1
when 常量2 then 值2
...
else 值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 AS 新工资
FROM employees;

case情况2：条件判断

case 
when 条件1 then 值1
when 条件2 then 值2
...
else 值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;

其他函数

函数名	作用
version	当前数据库服务器的版本
database	当前打开的数据库
user	当前用户
password(‘字符’)	返回该字符的密码形式
md5(‘字符’)	返回该字符的 md5 加密形式