小徐带你了解mysql常见函数(单行函数，数学函数，字符函数，日期函数，流程控制函数)

Mysql常见函数

**功能：**类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名

**好处：**隐藏了实现细节，提高代码的重用性

调用： select 函数名(实参列表) from 表;

特点:叫什么(函数名) 干什么(函数功能)

分类：

一：单行函数

如：concat	length	ifnull等

二：分组函数：

功能：做统计使用，又称为统计函数，聚合函数，组函数。

一：字符函数

length 获取字符数值的字节个数

select	length('hello');
select length('许雅苑');

show variables like '%char%';

CONCAT拼接字符串

select concat(last_name,'_',first_name) as 姓名 from employees;

upper变大写 lower变小写

select upper('ac');
select lower('AS');

实例，将姓大写，名小写，然后拼接

select concat(upper(last_name),'_',lower(first_name)) as 姓名 from employees;

substr,substring

注意：索引从1开始

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

substr(str,pos)解释：

	从pos位置开始一直到最后注意包括pos

select substr('杨过和小龙女',3) as out_put;

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

substr(str,pos,len)解释

从pos位置开始截取len的长度的字符

select substr('杨过和小龙女',1,3) as out_put;

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

select concat(upper(substr(last_name,1,1)),'_',lower(substr(last_name,2)))
as out_put
from employees;

instr返回子串第一次出现的索引，如果找不到返回0

select instr('杨过和小龙女的爱情故事','小龙女') as out_put;

trim 去除字符两端的元素

select length(trim(' 许雅苑 ')) as out_put;
select trim('a' from 'aaaa许雅苑aaaaaaa亚远aaaaaa') as out_put;

lpad用指定的字符实现左填充指定长度

select lpad('徐亚远',10,'*')as out_put;

rpad用指定的字符实现右填充指定长度

SELECT RPAD('徐亚远',12,'*')as out_put;

replace 替换

replace(str,s,t)解释

把str中的包含s的字符替换为t所表示的字符
此处sql语句把小龙女替换为周芷若

select replace('杨过和小龙女的爱情故事','小龙女','周芷若') as out_put;

二：数学函数

round四舍五入

select round(1.2);
select round(-1.2);

ceil向上取整，返回>=该参数的最小整数

select ceil(-1.02);
select ceil(1.2);

floor向下取整，返回<=该参数的最大整数

select floor(-1.02);
select floor(-9.99);
select floor(9.99);

truncate截断

select truncate(1.222223,1.22);

mod取余

select mod(10,3);
select mod(-10,-3);

三：日期函数

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

select now();

curdate返回当前系统日期，不包含时间

select curdate();

curtime返回当前时间，不包含日期

select curtime();

可以获取指定的部分，年，月，日，小时，分钟，秒

获取年的日期

select year(now()) as 年;

select year('2020-3-26') as 年;

select year(hiredate) as 年 from employees;

获取月的日期

select month(now()) as 月;
select monthname(now()) as 月;

str_to_date将字符通过指定格式转换成日期

select str_to_date('2020-2-22','%Y-%c-%d')as out_put;

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

select hiredate from employees where hiredate='1992-4-3';

select hiredate from employees where hiredate=str_to_date('4-3-1992','%c-%d-%Y');

date_format 将日期转换成字符

select date_format(now(),'%Y年%m月%d日') as out_put;

查询有奖金的员工名和入职日期(xx月xx日xx年)

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函数 if else 的效果

select if(10>2,'大','小');

select last_name,commission_pct,if(commission_pct is null,'没有奖金','有奖金') as 备注 from employees;

二：case函数的使用：switch case 的效果

mysql中

case 要判断的字段或表达式

when 常量一：then 要显示的值1或语句1；

when 常量二：then 要显示的值2或语句2；

..........

else 要显示的值n或语句n；

end

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

部门号=30，显示的工资为1.1倍

部门号=40，显示的工资为1.2倍

部门号=50，显示的工资为1.3倍

其他部门，显示的工资为原工资

select salary as 原工资,department_id,
case department_id
when department_id=30 
then salary*1.1
when department_id=40
then salary*1.2
when department_id=50
then salary*1.3
else salary
end as 新工资
from employees;

case函数的使用二：类似于多重if

mysql中：

case

when 条件一	then	要显示的值1或语句1

when 条件二	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;

常见函数：一：单行函数

字符函数：

length
concat
substr
instr
trim
upper
lower
lpad
rpad
replace

数学函数:

round
ceil
floor
truncate
mod

日期函数：

now
curdate
curtime
year
month
monthname
day
hour
minute
second
str_to_date
date format

其他函数：

version
database
user

控制函数:

if
case