#进阶四 常见函数
/*
概念：将一组逻辑语句封装在方法体中，对外暴露 方法名
好处：1、隐藏了实现细节 2、提高了代码的重复性
调用：select 函数名(实参列表) 【from 表】;
特点：
①叫什么（函数名）
②干什么（功能）
分类：
1、单行函数
做处理使用，如concat、length、ifnull
2、分组函数
做统计使用，又称为统计函数（聚合函数、组函数）
1、单行函数介绍：字符函数、数学函数、日期函数、其他函数、流程控制函数
*/
#一、字符函数
#1.length 获取参数值的字节个数
#注意：一般指字节的只有length 其余的比较多的都是字符
SELECT LENGTH('john')
SELECT LENGTH('张三')#utf8下，一个字母占一个字节，一个汉字占三个字节
#2.concat 拼接字符串
SELECT CONCAT(last_name,'_',first_name) AS 姓名
FROM
`employees`
#3.upper lower 大小写
SELECT UPPER('john')
#实例：姓大写，名小写(函数可以嵌套函数)
SELECT CONCAT( UPPER(last_name),' ',LOWER(first_name)) 姓名
FROM
`employees`
#4.substr substring
#注意：索引从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))) out_put
FROM
`employees`
#5.instr 返回子串第一次出现的索引，如果找不到返回0.
SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') out_put; ---->7
#6.trim
SELECT TRIM(' 张翠山 ') AS out_put ---->张翠山
#去掉前后的字符
SELECT TRIM('a' FROM 'aaaaaa张aaaaaa翠山aaaaa') AS out_put
#7.lpad 用指定的字符实现左填充指定长度
SELECT LPAD('殷素素',10,'*') out_put ----->*******殷素素
#8.rpad 用指定的字符实现右填充指定长度
SELECT RPAD('殷素素',12,'*') out_put
#9.replace
SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏') AS output;
----->张无忌爱上了赵敏
#二 数学函数
#round 四舍五入
SELECT ROUND(1.655)
#保留两位
SELECT ROUND(1.655,2)
#ceil 向上取整,返回大于等于该参数的最小整数
SELECT CEIL(1.002);
SELECT CEIL(-1.002);
#floor 向下取整 ，返回小于等于该参数的最大整数
SELECT FLOOR(9.99);
#truncate 截断
SELECT TRUNCATE(1.6999,1) ------>1.6
#mod取余
SELECT MOD (10,3)
#三.日期函数
#1.now 返回当前系统日期+时间
SELECT NOW();
#2.curdate 返回当前系统日期，不包含时间
SELECT CURDATE();
#3.curtime 返回当前时间，不包含日期
#可以获取指定的部分，年月日小时分钟...
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT YEAR(`hiredate`) FROM employees;
SELECT MONTHNAME(NOW()) 月; #返回英文表达
#4.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' ;
SELECT
*
FROM
`employees`
WHERE `hiredate`=STR_TO_DATE('4-3 1992','%c-%d %Y');
#date_format：将日期转化为字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%c日') AS out_put;
#查询有奖金的员工名和入职日期（xx月/xx日/xx年）
SELECT last_name,DATE_FORMAT(`hiredate`,'%m月/%d日/%Y年') 入职日期
FROM `employees`
WHERE `commission_pct` IS NOT NULL;
#四.其他函数
SELECT VERSION();
SELECT DATABASE();
SELECT USER();
#五.流程控制函数
#1.if函数 ：if else的效果
SELECT IF(10>5,'大','小') AS out_put;
#查看员工是否有奖金
SELECT last_name,`commission_pct`,IF(`commission_pct`IS NULL,'没奖金','有奖金') AS 备注
FROM employees;
#2.case函数的使用一： switch case的效果（类似java）
/*
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` 原始工资,`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
/*
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函数的单独介绍
一般使用count(*)用作统计行数
5、和分组函数一同查询的字段 要求是group by后的字段
6、
*/
#1.简单使用
SELECT SUM(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT COUNT(salary) FROM employees;
SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高
FROM employees;
#2.参数类型 支持哪些类型
SELECT SUM(last_name),AVG(last_name) FROM employees;
#这样做没有意义
SELECT MAX(last_name) FROM employees;
SELECT MAX(`hiredate`) FROM employees;
SELECT COUNT(last_name) FROM employees;
#3.是否忽略null
SELECT COUNT(`commission_pct`) FROM employees;#只计算不为null的个数
SELECT AVG(`commission_pct`) FROM `employees`;##只计算不为null的部分
#4.和distinct搭配
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary), COUNT(salary) FROM employees;
#5.count函数的详细介绍
SELECT COUNT(salary) FROM employees; #count 加上字段
SELECT COUNT(*) FROM employees; #可以计算行数
SELECT COUNT(1) FROM employes; #同样可以计算行数 count中加任何一个值都相当于多加了一列常量值，统计个数
#效率
MYISAM存储引擎下，COUNT(*)效率最高
INNODB存储引擎下，COUNT(*)和COUNT(1)效率差不多，比COUNT(字段)高一些。
综上，用COUNT(*)效率比价高
#6.和分组函数一同查询的字段限制
SELECT AVG(salary),employee_id FROM employees;
#例题：查询员工表中最大入职时间与最小入职时间的相差天数。函数datadiff
SELECT
DATEDIFF(MAX(`hiredate`), MIN(`hiredate`))
FROM
`employees` ;
