# 进阶4：常见的函数
/*
概念：将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1.隐藏了实现细节 2.提高代码的复用性
调用： select 函数名() 【from 表】;
特点：
（1）叫什么（函数名）
（2）干什么（函数功能）
分类：
1.单行函数
字符函数，数学函数，日期函数，其他函数，流程控制函数
2.分组函数
常见函数总结：
1.单行函数
字符函数：
length,concat,substr,instr,trim,upper,lower,lpad,rpad,replace
数学函数：
round,ceil,floor,truncate,mod
日期函数
now,curdate,curtime,year,month,monthname,day,hour,minute,second,str_to_date,date_format
其他函数：
version()，databases()
流程控制函数
if,case
2.分组函数
*/
# 一、字符函数
#1. length获取参数值的字节个数， utf8下面一个字母占一个字节，一个汉字占3个字节
select length("john");
select length("张三丰hahaha");
show variables like "%char%";
#2.concat拼接字符串
select concat(first_name," ",last_name) from employees;
#3.upper小写变大写、lower大写变小写
select upper("john");
select lower("JOHN");
select lower("JohN");
#示例：将姓变大写，名变小写，然后拼接
select concat(lower(first_name)," ",upper(last_name)) from employees;
#4. substr,substring
# 索引从1开始 substr("字符串",num,length)，num为截取开始的位置,length为截取的长度，可选
select substr("我的名字叫小芳",6);
select substr("我的名字叫小芳",3,2);
#案例：将姓名中首字符大写，其他字符小写，然后用_拼接显示
select concat(upper(substr(first_name,1,1)),lower(substr(first_name,2)),"_",last_name) from employees;
#5.instr 返回字串第一次出现的索引，如果找不到则返回0
select instr("我的名字叫小芳","小芳");
#6.trim 去删掉前后的重复
select trim(" aaa ") as out_put; #获得aaa
select trim("b" from "bbbbbbbbaaaaavbfddsaaabbbb") as out_put;
#获得aaaaavbfddsaaa
#7.lpad 用指定的字符实现左填充
#8.rpad 用指定的字符实现右填充
#9.replace替换
select replace("wqq123wqqhahahahwqq","wqq","ttt") as out_put;
#二、数学函数
# 1.round 四舍五入
select round(-1.55);
select round(-1.566,2);#小数点后保留2位
#2. ceil 向上取整,返回>=该参数的最小整数
select ceil(1.02);
# floor向下取整
select floor(1.02);
#4.truncate截断
select truncate(1.69999,1); #1.6,小数点后1位
#5.mod取余等价于%
# 返回与被除数有关
#mod(a,b) a-a/b*b
# 三、日期函数
# 1. now 返回当前系统的日期和时间
select now();
# 2. curdate 返回当前系统的日期，不包含时间
select curdate();
# 3. curtime 返回当前系统的时间，不包含日期
select curtime();
# 4. 可以获取指定的部分，年，月，日，小时，分，秒
select year(now()) as 年; #年
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="1992-4-3";
# date_format:将日期转换为字符
#四、其他函数
select version();
select database();
#五、流程控制函数
#1.if 函数：if else的效果
select if(10<5,"大","小");
use myemployees;
#案例：显示姓名和奖金率，有奖金哈哈，无奖金呵呵
select last_name,commission_pct, if(commission_pct is null,"没奖金，呵呵","有奖金，哈哈") as 备注 from employees;
#2.case函数的使用1-----switch case的效果
/*
case 要判断的字段或者表达式
when 常量1 then 要显示的值1 或者 语句1;
when 常量2 then 要显示的值2 或者 语句2;
...
else 要显示的值n 或者 语句n;
end
*/
/*案例：查询员工的工资，要求
部门号=30，显示工资的1.1倍
部门号=40，显示工资的1.2倍
其他部门，显示工资的1.3倍
*/
select salary as "原始工资",department_id,
case manager_id
when 30 then salary*1.1
when 40 then salary*1.2
else salary*1.3
end as "新工资" from employees;
#case函数的使用2
/*
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;
#题目1：显示系统时间（注：日期+时间）
select now();
#题目2；查询员工的工号，姓名，工资，以及工资提高20%之后的结果（now salary）
select last_name,employee_id,salary,salary*1.2 as "now salary" from employees;
#题目3：将员工的姓名按照首字母排序，并写出姓名的长度（length）
select last_name,length(last_name) from employees order by substr(last_name,1,1);
#注意 order by 的位置
# 题目4：做一个查询，产生下面的结果
<last_name> earns <salary> monthly but wants <salary*3>
select concat(last_name," earns ",salary," monthly but wants ",salary*3) as "dream salary" from employees;
#题目5：使用case-when，按照下面的条件
job grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
产生 last_name ,job_id,grade
select distinct job_id from employees;
select job_id as "job", case job_id
when "AD_PRES" then 'A'
when "ST_MAN" then 'B'
when "IT_PROG" then 'C'
when "SA_REP" then "D"
when "ST_CLERK" then "E"
end as "grade" from employees;
#2.分组函数
/*
功能：用作统计使用，又称为聚合函数或者统计函数或者组函数
输入多行，输出一行
分类：
sum,avg,max,min,count
特点：
1.sum,avg:数值型
max,min,count:数值型，字符型
2.是否忽略NULL值
以上所有分组函数均忽略null值
3.可以和distinct搭配
4.count函数的详细介绍（这个不忽略null值的）
一般使用count(*)统计行数
5.和分组函数一同查询的字段有限制
一般要求是group by后的字段
*/
# 1. 简单的使用
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 sum(salary)as "和", avg(salary) as "平均",min(salary) as "最小", max(salary) as "最大",count(salary) as "总数" from employees;
#round保留小数位
select sum(salary)as "和", round(avg(salary),2) as "平均",min(salary) as "最小", max(salary) as "最大",count(salary) as "总数" from employees;
#2.参数支持哪些类型：
sum,avg:数值型
max,min,count:数值型，字符型
count值计算非空的字段
select count(commission_pct) from employees; #35个数据，null值不算
select count(salary) from employees; #107个数据。
#3.是否忽略NULL值
select sum(commission_pct), avg(commission_pct),sum(commission_pct)/35,sum(commission_pct)/107 from employees;
#sum,avg:null不参与计算,直接忽略
select max(commission_pct), min(commission_pct)from employees;
#max,min:null不参与计算,直接忽略
#4.和distinct搭配
select sum(distinct salary),sum(salary) from employees;
#397900,691400
select count(distinct commission_pct),count(commission_pct) from employees;
# 7, 35
#5.count函数的详细介绍
select count(salary) from employees;#107行
select count(*) from employees;#统计行数：107行
select count(1) from employees;#统计行数：107行，与count(*)等价
效率问题：
myism存储引擎下，count(*)效率最高
innodb存储引擎下，count(*)和count(1)效率差不多，但是比count(字段)效率高
#6. 和分组函数一同查询的字段有限制
select avg(salary),commission_pct from employees;
这样是没有意义的，avg(salary)是一个数据，commission_pct是107个数据。
#题目1：查询员工表中的最大入职时间和最小入职时间的相差天数（difference）
select max(hiredate)-min(hiredate) as "difference" from employees;#表达错误
select datediff(max(hiredate),min(hiredate)) as "difference" from employees;
select datediff(now(),"2000-01-01");#求相差天数
#题目2：查询部门编号位90的员工个数
select count(*) from employees where department_id=90;
浙公网安备 33010602011771号