进阶4：常见函数（单行函数和分组函数）

1.函数

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

• 函数好处：
  * 1.隐藏了实现细节
  * 2.提高代码的重用性

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

• 函数特点：
  * 1.叫什么（函数名)
  * 2.干什么（函数功能）

• 函数分类：
  1. 单行函数： （字符函数、数学函数、日期函数、其他函数【补充】、流程控制函数【补充】）
  如concat、length、ifnull等
  2. 分组函数
  功能：做统计使用，又称为统计函数，聚合函数，组函数

2.函数

2.1：字符函数

1. length 获取参数值的字节个数

select length('john');
select length('张三丰hahaha');    //15个字节，采用utf8编码，一个汉字代表三个子节，一个字母代表一个字节
show VARIABLES like '%char%';    //查看字符编码集

2. concat拼接字符串

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

3. upper、lower
   select upper('john');
select lower('joHn');

   示例：将姓变大写，名变小写，然后拼接
   select concat(upper(last_name),'_',lower(first_name)) as 姓名 from employees;

4. substr、substring截取字符串
   注意：索引从1开始

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

select substr('李莫愁爱上了陆展元',7) out_put; ------out_put值为 ‘陆展元’

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

select substr('李莫愁爱上了陆展元',1,3) out_put; ------out_put值为 ‘李莫愁’

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

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

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

select instr('杨不悔爱上了殷六侠'，'殷六侠') as out_put; -----out_put 为 7

6. trim 去除前后空格或某个字符

select trim(' 张翠山 ') as out_put;
select trim('aa' from 'aaaaaaa张aaaaaa翠aaaaaa山aaaa') as out_put;

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

select lpad('殷素素',2,'*') as out_put; //out_put为'殷素' 字符长度多于要填充的长度时，从右侧截取

8. rpad 用指定的字符实现左填充指定长度

select rpad('殷素素',5,'*') as out_put; //out_put为'殷素素**'

9. replace替换
   select replace('周芷若张无忌爱上了周芷若','周芷若','赵敏') as out_put; -------out_put为'赵敏张无忌爱上了赵敏'

2.2.数学函数

1. round 四舍五入

`select round(-1.45);   //'-1'`
`select round(1.567,2);    //保留两位小数 1.57`

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

`select ceil(-1.02);   //'-1'`

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

`select floor(-9.99);   //'-10'`

4. truncate 截断,指定位置后的字符全部去掉，从0开始

`select truncate(1.6999999,2);     //'1.69'`

5. mod 取余
   mod(a,b) a-a/b*b

`select mod(10,3);        //'1'`
`select mod(10,-3);       //'1'`

2.3.日期函数

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

`select now();`

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

`select curdate();`

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

`select curtime();`

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

`select year(now()) 年;`
`select year('1980-1-1') 年;`

`select year(hiredate) 年 from employees;`

`select month(now()) 月;`
`select monthname(now()) 月;    //返回英文名`

  同理hour，minute，second

5. str_to_date: 将日期格式的字符转换成指定格式的日期

   str_to_date('9-13-1999','%m-%d-%Y'); --------1999-09-03

%Y ---四位的年份    %y ---2位的年份   %m ---月份（01，02...11，12）  %c ---月份（1，2...11，12）  %d ---日（01，02...）  %H ---小时（24小时制）
%h ---小时（12小时制）  %i ---分钟（00，01...59）  %s ---秒（oo，01...59）

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

6. 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;`

2.4. 其他函数

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

2.5. 流程控制函数

1. if函数：if else的效果 类似于三元运算符

`select if(10<5,'大','小');  //小`

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

2. case函数的使用一：switch case的效果 等值判断

补充：java中    switch(变量或表达式){
                       case 常量1:语句1;break;
                       ...
                       default:语句n;break;

             }

    mysql中    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 as 原始工资,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;

3. case函数的使用二：类似于多重if 区间判断

java中

if(条件1){
        语句1;
}else if(条件2){
        语句2;
}
...
else{
        语句n;
}

mysql中

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;

3.分组函数

• 功能：用作统计使用，又称为聚合函数或统计函数或组函数

• 分类
  sum 求和、avg 平均值、max 最大值、min 最小值、count 计算个数

• 特点

  1. sum,avg一般用于处理数值型
     max,min,count可以处理任何类型

  2. 是否忽略null值

     以上分组函数都忽略null值

  3. 可以和distinct搭配实现去重的运算

  4. count函数的单独介绍
     一般使用count(*)用于统计行数

  5. 和分组函数一同查询的字段要求是group by后的字段

3. 1 简单的使用

select sum(salary) from employees;
select avg(salary) from employees;
select max(salary) from employees;
select min(salary) from employees;
select count(salary) from employees;

select sum(salary) 和,avg(salary) 平均,max(salary) 最高,min(salary) 最低,count(salary) 个数 from employees;
select sum(salary) 和,round(avg(salary),2) 平均,max(salary) 最高,min(salary) 最低,count(salary) 个数 from employees;

3.2 参数支持哪些类型

select sum(last_name),avg(last_name) from employees; 无意义

select max(last_name),min(last_name) from employees;

3.3 忽略null值

select sum(commission_pct),avg(commission_pct) from employees;

3.4.和distinct搭配使用

select sum(distinct salary),sum(salary) from employees;
select count(distinct salary),count(salary) from employees;

3.5.count函数的详细介绍

select count(salary) from employees;
select count(*) from empoloyees; ----统计行数
select count(1) from employees; ----统计1的个数
select count('崔霞') from employees;

注意：
MYISAM存储引擎下，count()的效率高
INNODB存储引擎下，count()和count(1)效率差不多，比count(字段)要求高一些

3.6.和分组函数一同查询的字段有限制

select avg(salary),employee_id from employees; //行数不一致