1、字符函数：
/*
进阶4： 常见函数学习
函数含义：封装常用的有特定功能的一组逻辑语句，对外暴露方法名；
好处：隐藏实现细节；提高代码重用性
调用方法：select 函数名(参数) 【from 表名】
备注： 如果参数是取自特定表， 才需要加from 表名
特点：函数名；功能；
常见函数：
①单行函数，concat(), length(), ifnull()
②分组函数（统计函数、聚合函数、组函数），做统计使用：
区别： 单行函数传入参数为一个值；分组函数传入参数为一组值
*/
# 单行函数： 字符函数、数学函数、日期函数、其他函数、流程控制函数
# 字符函数：
# 1、length(str):获取参数的字节数
select length('john') # 返回4
select length('张三jsjsjs') # 返回3*2+6 = 12，
# utf8编码方式中：一个汉字占3个字节， 一个字母占一个字节（mysql使用utf8）
# DBK编码方式中：一个汉字占2个字节， 一个字母占一个字节
# 查看客户端client 使用的字符集
show variables like '%char%';
# 2、concat(str1,str2,...) : 拼接字符串
select concat(first_name,',',last_name) as name from employees;
# 3、upper(), lower()： 转换大小写
select upper(first_name) from employees;
select lower(job_id) from employees;
# 4、substr() = substring() :截取字符串，pos表示从pos位置开始截取，
/*注意：
1、substr有四个重载函数：名字一样，参数不一样
2、所有的mysql语言中，索引均从1开始
3、除了length()函数，其余函数参数中如果有表示长度的参数，一般指字符长度，而非字节长度
*/
# substr(str,pos) ：截取从指定索引处（pos）后面所有字符
select substr('李莫愁爱上了陆展元',7) as output; # 截取'陆展元',
# substr(str,pos1,pos2):截取从指定索引处（pos1,pos2）指定字符长度的字符
select substr('李莫愁爱上了陆展元',1,3) as output; # 截取李莫愁
# 姓名首字母大写，其他字符小写
select concat(upper(substr(last_name,1,1)),'_', lower(substr(last_name,2))) from employees;
# 5、instr():返回特定字符(子串)在str中的索引位置， 第一次出现的位置， 如果没有，返回0
select instr('杨不悔爱上了殷六侠','殷六侠') as output;
# 6、trim()：消除前后空格/特定字符
select trim(' 张翠山 ') as output;
select trim('a' from 'aaaaaa张aaaaaa翠山aaaaaaa') as output;
# 7、lpad()：left pad左填充，用特定符号将字符串左填充至固定位数
select lpad('殷素素',10,'*') as output;
select lpad('殷素素',2,'*') as output;
# 8、rpad()：right pad左填充，用特定符号将字符串右填充至固定位数
select rpad('殷素素',10,'ab') as output;
# 9、replace(): 替代
select replace('张无忌爱上了周芷若','周芷若','赵敏') as output;
select replace('周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') as output;
# ...
/*
2、 数学函数
*/
# 1、round(): 四舍五入
select round(1.65) as output;
select round(-1.65) as output;
select round(1.567,2) as output; # 四舍五入，小数点后保留两位
# 2、ceil(): 向上取整：返回>=该参数的最小整数
select ceil(1.52) as output;
select ceil(1.12) as output; #2
select ceil(-1.12) as output; #-1
# 3、floor()：向下取整：返回<=该参数的最小整数
select floor(1.12) as output; #1
select floor(-1.12) as output; #-2
# 4、truncate(float,num) 截断，小数点后保留num位数
select truncate(1.599,2) as output;
# 5、mod(num1,num2): 取余 = num1%num2
select mod(10,3) as output; #1
select mod(-10,-3) as output; #-1
select mod(-10,3) as output; #-1
select mod(10,-3) as output; #1
/*
3、日期函数
*/
# 1、now():返回当前系统日期+时间
select now();
# 2、curdate(); current date：返回当前系统日期
select curdate();
# 3、curtime()：current time：返回当前系统时间
# 4、获取指定部分：年、月、日、时、分、秒
select year(now());
select month(now());
select monthname(now());
select day(now());
select hour(now());
select minute(now());
select second(now());
use myemployees;
select year(hiredate) as `hire_year` from employees;
select year(hiredate) as 'hire_year' from employees;
# 5、日期转换函数之 str_to_date: 日期格式的字符转换成指定格式的日期
select str_to_date('5-18-2021','%m-%d-%Y'); # 2021-05-18
select str_to_date('2021-5-18','%Y-%m-%d'); # 2021-05-18
select str_to_date('2021年5月18日','%Y年%m月%d日'); # 2021-05-18
/*
日期格式符及对应功能：
%Y 四位年份
%y 两位年份
%m 月份(01,02,...,12)
%c 月份(1,2,...,12)
%d 日(01,02,..)
%H 小时(24小时制)
%h 小时(12小时制)
%i 分钟(00,01,02,...59)
%s 秒(00,01,02,...59)
*/
# 查询入职日期
desc employees;
select hiredate from employees;
select str_to_date(hiredate,'%Y-%m-%d') as output from employees;
# 查询入职日期为4月3号的员工姓名
select last_name,hiredate from employees where hiredate = '1992-04-03' ; # 成功
select last_name,hiredate from employees where hiredate = '1992-4-3' ; # 成功
# 根据用户输入的日期进行查询，用户输入为字符串
select last_name,hiredate from employees where hiredate = str_to_date('04-03 1992','%m-%d %Y');
select last_name,hiredate from employees where hiredate = str_to_date('4-3 1992','%c-%d %Y');
#date_format: 将日期转化为字符
select date_format('2021-5-18','%Y年%c月%d日'); #2021年5月18日
select date_format('2021/5/18','%Y年%c月%d日'); #2021年5月18日
select date_format('5/18/2021','%c月%d日%Y年'); #错误
# 查询特定日期后转化成字符
select date_format(hiredate,'%Y年%m月%d日') from employees; # 将表格中日期转化为字符
# 查询有奖金的员工名和入职日期
# (xxxx年xx月xx日)；
select last_name,date_format(hiredate,'%Y年%m月%d日'),commission_pct from employees where commission_pct is not null;
# (xx月xx日 xx年)；
select last_name,date_format(hiredate,'%m月/%d日 %y年'),commission_pct from employees where commission_pct is not null;
4、其他函数
use myemployees;
select version(); #查看MySQL版本
select database(); #查看当前库
select user(); #查看用户
-- 分组函数
-- 功能：用作统计，又称为聚合函数、组合函数
-- 常见：sum(),avg(),min(),max(),count()
-- 1、简单应用
use myemployees;
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; # 只统计非空值个数
-- 2、参数支持哪些类型
-- sum(),avg()只支持数值型
-- max(),min()支持数值、字符、日期等可排序的数据类型
-- count()支持各种类型
select min(hiredate) from employees; # max表示最近的日期，min表示最早的日期
select count(hiredate) from employees; # 107
select count(commission_pct) from employees; # 35, 因为commission有null值
-- 3、是否忽略null值： max，min,sum,avg,count均忽略null值
select sum(commission_pct)/count(commission_pct) from employees; # 不考虑null值个数的均值
select avg(commission_pct) from employees;
# 不考虑null值个数的均值，结果与上述代码相同
-- 4、分组函数的使用特点
-- 可以和distinct搭配进行去重操作，用sum实例
select sum(distinct salary),sum(salary) from employees; #两者不同，distinct是去重之后再相加
-- 查看有几种工资类型
select count(distinct salary),count(salary) from employees;
-- 5、count函数的详细介绍
select count(salary) from employees;
select count(*) from employees; # 统计行数，用得最频繁
# 用于统计总行数，由于不同列可能会出现null值，因此只统计一列的count，可能不能反应总行数，因为count不对null进行计数
select count(1) from employees; # 统计行数，也可以用count(2),count(x)， 相当于在表格中加了一列的1然后对其进行计数
-- 效率：
-- INNODB存储引擎下，count(*)和count(1)差不多,比count(字段)高
-- MYISAM存储引擎下，count(*)最高
-- 6、和分组函数一同查询的字段有限制,要求是group by后的字段
select avg(salary),employee_id from employees; # 显示错误
-- 测试题
-- 1、查询公司员工工资的最大值、最小值、平均值、总和
select max(salary) mx_sal,min(salary) mn_sal,round(avg(salary),2) ag_sal,sum(salary) sm_sal from employees;
-- 2、查询员工表中最大入职时间和最小入职时间的相差天数（difference）, 用到函数datediff
select datediff(max(hiredate),min(hiredate)) as difference from employees;
-- 3、查询部门编号为90的员工个数；
select count(department_id) from employees where department_id = 90;
select count(*) from employees where department_id = 90; # 与上一行代码等同