函数概念:类似于java的方法,将一组逻辑语句封装在方法体中,对外暴露方法名
函数的好处:
隐藏实现的细节
提高代码的重用性
使用模板
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
特点:
sum、avg一般用于处理数值型
max、min、count 可以处理任何类型
可以和distinct搭配实现去重运算
4.一般用count(*)用作统计行数
分组查询语法
select 分组函数 列(要求出现在group by后面)
from
[where 筛选语句]
group by 分组的列表
[order by 子句]
注意查询的列表按要求是分组函数和group by后出现的字段
分组函数使用例子
简单的分组查询(添加分组前的筛选)
//查询每个位置上的部门个数
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;