mysql常用函数总结每个函数附带例子理解

函数概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名

函数的好处:

1. 隐藏实现的细节
2. 提高代码的重用性

使用模板

selelct 函数名(实参列表) from 表;

分类

• 单行函数
• 分组函数

单行函数

一、 字符函数

• length 获取参数值的字节个数

select length('john');
select length('张三丰hahaha');

• concat 拼接字符串

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

• upper,lower

select upper('john') ;
select lower('joHN') ;
//将姓变成大写，名变成小写然后拼接（发现函数可以嵌套）
select concat(upper(last_name),lower(first_name)) 姓名 from employees;

• substr、substring 截取字符串

mysql中索引从1开始

//打印sjy
select substr('hj爱上了sjy',6) output;
//截取从指定索引处指定字符长度的字符 打印hj
select substr('hj爱上了sjy',1,2) output;

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

select instr('hj爱上了sjy','sjy') as out_put;

• trim

select trim('       hj   ') as out_put;
//
select trim('a' from 'aaaaa张aaaaa杰aaaaaaaaa') as out_put;

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

//输出：**hj
select lpad('hj',4,'*') as output;

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

//输出： hjab
select rpad('hj',4,'*') as output;

• replace 替换

select replace('hj爱上了sjy','sjy','hj') as output

二、数学函数

• round 四舍五入

//输出：-2
select round(-1.55);
//小数点保留两位 输出： 1.57
select round(1.567，2)

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

//输出：-1
select ceil(-1.02)

• floor 向上取整,返回<=该参数的最大整数

select floor(-9.99);

• truncate 截断

//输出：1.6
select truncate（1.69999，1）

• mod 取余

公式：mod(a,b) a-a/bb
mod(10,-3) 10-10/(-3)(-3)=1;

//输出：1
select mod(10,-3)

三、日期函数

• year ,month,day可以获取指定的部分，年，月，日

//now() 代表当前时间
select year(now()) 年;
select year('1997-9-19') 年;
select year(hiredate) 年 from employees;
select monthname(now());

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

select str_to_date('1998-3-2','%Y-%c-%d');

//查询入职日期为1992-4-3的员工信息
select * from employees where hiredate=str_to_date('4-3 1992','%c-%d' %Y);

• date_format() 将日期转换成字符

select date_format(now(),'%y年%m月%日') as out_put;
查看有奖金的员工名和入职日期（x月x日 x年）
select last_name,date_format(hiredate,'%m月%d日 %y年') 入职日期 from employee where commission is not null;

四、其他函数

//查看版本号
select version();
//查看数据库
select database();

五、流程控制函数

• if函数 if else效果

//输出：小
select if(10<5),'大','小';
//查看 员工是否有奖金并输出  有钱没钱
select last_name,commission_pct,if(commission_pct is null,'没钱','有钱');

• switch函数 switch case 的效果

case 要判断的字段或者表达式
when 常量1 then 要显示的值1或者语句1
when 常量2 then 要显示的值2或者语句2
else 要显示的值a或者语句n
end 要显示的值n或者语句n

分组函数

• 作用： 用于统计使用，又称为聚合函数或者统计函数或者组函数
• 分类 sum avg max min max
• 特点：

1. sum、avg一般用于处理数值型
2. max、min、count 可以处理任何类型
3. 可以和distinct搭配实现去重运算
   4.一般用count(*)用作统计行数

• 分组查询语法

select 分组函数  列(要求出现在group by后面) 
from 
[where 筛选语句]
group by 分组的列表
[order by 子句]

注意查询的列表按要求是分组函数和group by后出现的字段

• 分组函数使用例子

1. 简单的分组查询（添加分组前的筛选）

//查询每个位置上的部门个数
select count(*),location_id from departments group by location_id;

//查询邮箱中包含a字符，每个部门的平均工资
select avg(salary),department_id from employees where email like '%a%' group by department_id;

//查询有奖金的每个领导手下员工的最高工资
select max(salary), manager_id from empolyees where commission_pct is not null group by manager_id;

//

2. 复杂一些的分组查询(添加分组后的筛选或者混合分组前的)

//查询哪个部门的员工的个数>2
①查询每个部门的员工个数
②再根据①进行筛选，查询结果集里面员工个数>2
//①
select count(*) ,department_id from employees group by department_id
//②
select count(*) ,department_id from employees group by department_id having count(*)>2;

//查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
select max(salary),job_id from employees where commission_pct is not null group by job_id having max(salary)>12000;