MySQL学习记录（四）--常见函数

#进阶4：常见函数
/*
关注函数名和函数功能（叫什么、干什么）
分类
1.单行函数
	如concant、length、ifnull
	①字符函数 length concat substr  instr upper  trimlower lpad rpad replace 
	②数学函数 round ceil floor truncate mod 
	③日期函数 now curdate curtime year month day mintue str_to_date date_format
	④其他函数 version database user 
	⑤流程控制函数 if case 
	
2.分组函数	
	功能：做统计使用,
	又称统计函数、聚合函数、组函数

*/
#一、字符函数

#1.length 
#获取参数值的字节个数
SELECT LENGTH('jhon');
SELECT LENGTH ('张三丰hhh');

#2.concat 
#拼接字符串
SELECT CONCAT(`first_name`,'_',`last_name`) AS 姓名 FROM `employees`

#3. upper\lower
SELECT UPPER('jhon');
SELECT LOWER ('JKl');
SELECT CONCAT(UPPER(`first_name`),'_',LOWER(`last_name`)) FROM `employees`

#4.substr 
#截取字符
注意：索引从1开始
SELECT SUBSTR('李莫愁爱上了包子',7)AS out_put;
SELECT SUBSTR('李莫愁爱上了包子',1,3)AS out_put;
SELECT SUBSTR('李莫愁爱上了包子',3,3)AS out_put;
#练习
SELECT CONCAT(UPPER(SUBSTR(`last_name`,1,1)),'_',SUBSTR(`last_name`,2)) AS `name`
FROM `employees`;

#5.instr 
#返回字串第一次出现的索引，如果找不到返回0
SELECT INSTR('杨不悔爱上了梅子','梅子') AS out_put;

#6.trim
#去除字符串前后特定字符
SELECT TRIM('         张翠山     ') AS out——put;
SELECT LENGTH(TRIM('         张翠山     ') )AS out——put;
SELECT TRIM('a' FROM 'aaaaaaaaa张aaa翠山aaaaaaaaa')AS out_put

#7.lpad
#用指定字符左填充指定长度
SELECT LPAD ('殷素素',10,'*')AS out_put
SELECT LPAD ('殷素素',2,'*')AS out_put

#8.rpad
#用指定字符右填充指定长度
SELECT RPAD ('殷素素',12,'ab')AS out_put
SELECT RPAD ('殷素素',2,'ab')AS out_put

#10.replace
#替换
SELECT REPLACE('小子爱吃包子','包子','梅子')AS out_put；
SELECT REPLACE('小子包子爱吃包子','包子','梅子')AS out_put；


#二、数学函数

#1.round
#四舍五入
SELECT ROUND(1.65)AS put;
SELECT ROUND (1.657,2)AS put;

#2.ceil
#向上取整
SELECT CEIL(1.6)AS put;
SELECT CEIL(1.6565) AS put;

#3.floor
#向下取整
SELECT FLOOR (1.999) AS put;

#4.truncate
#截断
SELECT TRUNCATE(1.65,1);
SELECT TRUNCATE(1.65,0);

#5.mod
#取余
SELECT MOD(10,3);

#三、日期函数
/*
%Y--四位的年份
%y--两位的年份
%m-月份（01，02，03）
%c-月份（1，2，3）
%d-日
%H--24小时制
%h--12小时制
%i--分钟
%s--秒
*/

#1.now
#返回当前系统日期+时间
SELECT NOW();

#2.curdate
#返回当前的系统日期，不包括时间
SELECT CURDATE();

#3.curtime
#返回当前系统的时间，不包括日期
SELECT CURTIME();
#获取指定的年月日
SELECT YEAR(NOW());
SELECT YEAR('1998-4-4');
SELECT YEAR(`hiredate`) FROM `employees`;
	
SELECT MONTH(NOW());
SELECT MONTHNAME(NOW())

#4.str_to_data
#日期格式字符串返回指定格式的日期
SELECT STR_TO_DATE ('1998-2-2','%Y-%c-%d');

#查询入职日期为1992-4-3的员工信息
SELECT *FROM `employees`WHERE`hiredate` = STR_TO_DATE('4-3-1992','%c-%d-%Y')

#5.data_format
#日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日')
#查询有奖金的员工名和入职日期
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,'大','小')
SELECT`last_name`,IF(`commission_pct` IS NULL,'没有',commission_pct) FROM`employees`

#2.case函数
#效果一、switch casse
/*
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 `last_name`,`department_id`,`salary` 原始工资,
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或语句1
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end
*/

#案例：查询员工工资情况
SELECT `last_name`,`salary`,
CASE
WHEN salary >20000 THEN'A'
WHEN salary >15000 THEN'B'
WHEN salary >10000 THEN'C'
ELSE 'D'
END AS 工资级别
FROM `employees`;

SELECT `employee_id`,`last_name`,`salary`,`salary`*1.2 AS `new salary` FROM`employees`
SELECT `last_name`,LENGTH(`last_name`)FROM `employees` ORDER BY `last_name`ASC

#二、分组函数
/*
功能：统计使用；输入一组值得到一个值
分类：sum求和，avg平均值，max，min，count
特点：1.sum、avg处理数值型
        max、min可以处理任何类型
      2.分组函数都忽略null值
      3.和distinct搭配实现去重运算 
      4.count函数；selec count（*）查询行数
      5.和分组函数查询的字段要求使group by 后的字段
      
*/

#1.简单使用
SELECT SUM(`salary`)FROM`employees`;
SELECT COUNT(`salary`)FROM`employees`;
SELECT COUNT(`salary`)AS 个数,AVG(`salary`)AS 平均值 FROM`employees`;

#2.参数支持哪些类型
SELECT SUM(`last_name`),AVG(`last_name`)FROM `employees`;
SELECT MAX(`hiredate`),MIN(`last_name`)FROM `employees`;

#3.是否忽略null值
SELECT SUM(`commission_pct`),AVG(`department_id`)FROM `employees`;
SELECT MAX(`commission_pct`),MIN(`department_id`)FROM `employees`;
SELECT COUNT(`commission_pct`)FROM`employees`;

#4.和distinct搭配
SELECT SUM(DISTINCT(salary)),SUM(salary) FROM `employees`

#5.count函数单独介绍
SELECT COUNT(*)FROM `employees`#统计行数（多）
SELECT COUNT(1)FROM `employees`#统计行数

#6.和分组函数查询有限制


SELECT COUNT(*)FROM`employees` WHERE`department_id` = '90'