MySQL——（4）常见函数

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

好处：1、隐藏了实现细节

           2、提高了代码的重用性

调用：select 函数名（实参列表）[from 表]；

特点：1、叫什么（函数名称）

           2、做什么（函数功能）

分类：1、单行函数，例如：concat、length、iffull等

           2、分组函数，功能：做统计使用，又称统计函数、聚合函数、组函数

一、单行函数

1、字符函数

（1）length()：获取参数的字节个数

select length('join');  #输出的是4
select length('小欣欣hahahah'); #输出的是15

（2）concat()：拼接字符串

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

（3）upper和lower

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

（4）substr和substring

注意：索引从1开始！

#截取从指定索引处后面所有字符 
select substr('今天你开心吗', 2) out_put;    #out_put为：天你开心吗 
#截取从指定索引处指定字符长度的字符
select substr('今天你开心吗', 1,2) out_put;  #out_put为：今天
#案例：姓名中首字母大写，其它字符小写然后用_拼接，显示出来 
select concat(upper(substr(last_name, 1, 1)) ,'_', lower(substr(last_name, 2))) out_put from employees;

（5）instr()

返回子串第一次出现的索引，没有的话返回0 

select instr('今天你开心吗', '今天') as out_put;   #out_put为：1

（6）trim()

去掉前面的子串

select length(trim('    小欣欣    ')) as out_put; #out_put为：9
select trim('a' from 'aaaaaa小aaaa欣aaaa欣aaaa') as out_put;   #out_put为：小aaaa欣aaaa欣aaaa

（7）lpad()和rpad()

用指定字符实现左（右）填充到指定长度，超过长度的话会截断 

select lpad('小欣欣',10, '*') as out_put;  #out_put为：*******小欣欣
select rpad('小欣欣',10, '*') as out_put;  #out_put为：小欣欣******* 

（8）replace()

替换，当被替换的字符串中有很多可以被替换的会全部替换

select replace('你今天开心吗','开心','kaixin') as out_put;  #out_put为：你今天kaixin吗

2、数学函数

（1）round() 四舍五入

select round(1.65);      #out_put为：2
select round(1.45);      #out_put为：1
select round(-1.55);     #out_put为：-2
select round(1.652, 2);  #out_put为：1.65

（2）ceil() 向上取整,返回大于等于该参数的最小整数 

select ceil(1.002);      #out_put为：2
select ceil(1.000);      #out_put为：1
select ceil(-1.56);      #out_put为：-1

（3）floor() 向下取整,返回小于等于该参数的大整数

select ceil(1.002);      #out_put为：1
select ceil(-9.99);      #out_put为：-9

 （4）truncate() 截断

select truncate(1.65, 1) as out_put;    #out_put为：1.6

（5）mod() 取余 a - a / b * b

巧记：被除数是正，结果就是正，被除数为负，结果就是负

select mod(10,3) as out_put;     #out_put为：1
select mod(-10,3) as out_put;    #out_put为：-1
select mod(10,-3) as out_put;    #out_put为：1
select mod(-10,-3) as out_put;   #out_put为：-1

3、 日期函数

（1）now() 返回当前系统日期+时间

select now();        #2021-08-15 15:40:41

（2）curdate() 返回当前系统日期，不包含时间

select curdate();    #2021-08-15

（3）curtime() 返回当前系统时间，不包含日期

select curtime();    #15:42:25
#可以获取指定的部分，年、月、日、小时、分、秒 
select year(now()) 年;     #2021
select year(hiredate) 年 from employees;
select month(now()) 月;    #8

（4） str_to_date() 将日期格式的字符串转换成指定格式的日期

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

（5）date_format() 将日期转换成字符

select date_format(now(), '%y年%m月%d日');   #21年08月15日
#查询有奖金的员工名和入职日期(xx月/xx日 xx年)
select last_name,date_format(hiredate,'%m月/%d日 %y年') 入职日期 from employees where commission_pct is not null;

 4、其他函数

#1、version() 版本号
#2、database() 数据库 
#3、user() 当前用户

5、流程控制函数

（1）if函数：实现if else的效果

select if(10 > 5, '大', '小');   #大 

（2）case函数：

使用一：switch case的效果  

语法：case 要判断的字段或表达式

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

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

           ……

          esle 要显示的值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;

使用二：类似于多重if

语法：case

          when 条件1 then 要显示的值1

          when 条件2 then 要显示的值2

          ……

          else 要显示的值n

#案例：查询员工的工资情况：如果工资>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一般都用于处理数值型

           2、max、min、count可以处理任何类型数据

           3、分组函数都是忽略null值的

           4、可以和distinct搭配实现去重的运算

           5、一般使用count(*)统计表的行数

           6、和分组函数一同查询的字段要求是group by后面的字段

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;  #salary非空的有几个
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;  #给平均值保留两位小数

2、参数支持哪些类型

sum、avg、min、max只支持数值型的，不支持字符型的和日期型的

max、min、count支持数值型、字符型、日期型

但是，count数的是不为null的个数

select sum(last_name), avg(last_name) from employees;  #结果都是0,虽然不报错，但是其实是不可以的，无意义
select max(last_name), min(last_name), count(last_name) from employees;

3、是否忽略null值

select sum(commission_pct), avg(commission_pct) from employees; #输出结果为7.8和0.222857
#发现其实是忽略null值的，因为除以的实际是35
select sum(commission_pct), avg(commission_pct), sum(commission_pct) / 35, sum(commission_pct) / 107 from employees;

注意：所有的分组函数都是忽略null值的

4、与distinct搭配使用

select sum(distinct salary) from employees; #将salary去重求和
select count(distinct salary), count(salary) from employees;  #数有几种salary

5、count函数的详细介绍

select count(salary) from employees;
select count(*) from employees;   #任何字段只要有一个不为null的，其实最后的结果就是表的行数
select count(1) from employees;   #count（1）表示：有多少行就有多少个1，然后统计一共有多少个1，里面可以是任意常量值

效率问题：myisam存储引擎下，count(*)的效率高，innodb存储引擎下，count(1)和count(*)的效率差不多，比count(字段)要高 

总结：一般情况下数表的行数，用count(*)较多