第三节----常见函数

功能：类似于Java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1.隐藏了实现细节
     2.提高代码的重用性
调用：select（）from 表；
特点：1.函数名
     2.函数功能
分类：1.单行函数：concat、ifnull
     2.分组函数：做统计使用，又称为统计函数、聚合函数、组函数

一、单行函数
①字符函数

#length：获取参数值的字节个数
select length ('jhon');
select length ('zhangsanfenghahaha');
#concat拼接字符串
select concat(last_name,'_',first_name) from employees;
#upper\lower:
select upper ('jhon');
select lower ('jHon');
#案例：将姓名变大写，名字小写，然后拼接
select concat(upper(last_name),lower(first_name)) from employees;
#substr:
#注意：索引从1开始
#截取从指定索引处后面所有字符
select substr('李莫愁爱上了陆展元',7) out_put; 
#截取从指定索引处指定字符长度的字符
select substr('李莫愁爱上了陆展元',1,3) out_put;
#案例：姓名中首字符大写，其他字符小写然后用_拼接
select concat (upper(substr(last_name,1,1)),'_',lower(substr(last_name,2))) from employees;
#instr:返回子串第一次出现的索引，如果找不到返回0
select instr ('杨不悔爱上了阴六侠','阴六侠') as out_put;
#trim
select length(trim('       张翠山            ')) as out_put;
select trim('a' from 'aaaaaaaaaaaaaaaaaaaaaaaaaaa张aaaaaaaaaaa翠山aaaaaaaaaaaaaaaaaaaa') as out_put;
#lpad:用指定的字符实现左填充指定的长度
select lpad('殷素素',10,'*') as out_put;
select lpad('殷素素',2,'*') as out_put;
#rpad:用指定的字符实现右填充指定的长度
select rpad('殷素素',12,'ab') as out_put;
#replace:替换
select replace('张无忌爱上了周芷若','周芷若','赵敏') as out_put;

②数学函数

#round：四舍五入
select round(-1.65);
select round(1.567,2);
#ceil:向上取整，返回>=该参数的最小整数
select round(-1.65);
select round(1.567,2);
#floor:向下取整，返回<=该参数的最大整数
select floor(-9.99);
#truncate：截断
select truncate(1.6999,1);
#mod:取余，
#mod(a,b):a-a/b*b
select mod(10,3);

③日期函数

#now:返回当前系统日期+时间
select now();
#curdate:返回当前系统日期，不包含时间
select curdate();
#curtime:返回当前时间，不包日期
select curtime();
#获取指定的部分：年、月、日、小时、分钟、秒
select year(now()) as 年;、
select year('1998-1-1') as 年;
select year(hiredate) 年 from employees;
select month(now()) 月;
select monthname(now()) 月;
#str_to_date:将字符通过指定的格式转换为日期
select str_to_date('1998-3-2','%Y-%c-%d') as out_put;
#案例：查询入职日期为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月%d日') as uot_put;
#查询有奖金的员工名和入职日期
select last_name,date_format(hiredate,'%m月/%d日 %y年') 入职日期 from employees where commission_pct is not null;

④其他函数

#版本号
select version();
#查看当前的数据库
select database();
#当前用户
select user();

⑤流程控制函数

#if函数：类似于if else的效果
select if(10>5,'大','小');
select last_name,commission_pct,if(commission_pct is null,'没奖金，呵呵','有奖金，嘻嘻') 备注 from employees;
#case函数的使用一： switch 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 as 新工资 from employees;
#case函数的使用二： 类似于多重if
语法：
   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计算个数
特点：1.sum、avg一般用于处理数值型
       max、min、count可以处理任何类型
     2.以上分组函数都忽略null值
     3.可以和distinct实现去重的运算
     4.一般用count(*)统计行数
　　　5.和分组函数一同查询的字段要求是group by后的字段

#简单的使用
select sum(salary) from employees;
select avg(salary) from employees;
select min(salary) from employees;
select max(salary) from employees;
select count(salary) from employees;
select count(salary) 和,avg(salary) 平均,max(salary) 最高,min(salary) 最低,count(salary) 个数 from employees;
#参数支持的类型：
select max(last_name),min(last_name) from employees;
select max(hiredate),min(hiredate) from employees;
select count(last_name) from employees;
#是否忽略null值
select sum(commission_pct), avg(commission_pct),sum(commission_pct)/35,avg(commission_pct)/107 from employees;
#和distinct搭配
select sum(distinct salary),sum(salary) from employees;
select count(distinct salary),count(salary) from employees;
#count函数
select count(salary) from employees;
select count(*) from employees;--MYISAM存储引擎下效率高
select count(1) from employees;--INNODB存储引擎下和count(*)的效率差不多，比count（字段）要高一点