MySQL（4.常见函数）

#四.常见函数
#4.1 字符串函数
-- 1、CONCAT 拼接字符
#CONCAT(S1,S2,......,Sn)连接S1,S2,......,Sn为一个字符串
select concat(first_name,' & ',last_name) as name from employees;

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

#CONCAT_WS(s, S1,S2,......,Sn)同CONCAT(s1,s2,...)函数，但是每个字符串之间要加上s
select CONCAT_WS('#','a','b','c');

select CONCAT_WS('*','A','B','C','D');

#模糊查询员工姓名中包含字符a
select first_name from employees where first_name like '%a%';
select first_name from employees where first_name like concat('%','a','%'); -- 用的多

-- 2、LENGTH 获取字节长度
select length('abc'); -- 3
select length('你好'); -- 3+3=6个字节  一个UTF-8的字符（中文）占三个字节

-- 3、CHAR_LENGTH 获取字符个数
select char_length('abc'); -- 3
select char_length('你好'); -- 2个字符

-- 4、SUBSTRING 截取子串  注意：起始索引从1开始
#SUBSTRING(str,pos,len) str将要截取的字符串，pos起始下标，len截取的长度
select first_name , SUBSTRING(first_name,2,3) from employees;
select substring(last_name,2,5) from employees;
#SUBSTRING(str,pos) str将要截取的字符串，pos起始下标，默认截取至最后
select first_name , SUBSTRING(first_name,2) from employees;

-- 5、INSTR获取字符第一次出现的索引  注意：起始索引从1开始，若未查询到则直接返回0
#INSTR(str,substr) 查询子串substr在字符串str出现的索引位置
select first_name,INSTR(first_name,'a') from employees;
select last_name , instr(last_name,'b') from employees;

-- 6、TRIM去前后指定的字符，默认是去空格
#TRIM(s) 去掉字符串s开始与结尾的空格
select concat('###','   aaa   ','###'),concat('###',trim('   aaa   '),'###');
#LTRIM(s) 去掉字符串s左侧的空格
select concat('###','   aaa   ','###'),concat('###',LTRIM('   aaa   '),'###');
#RTRIM(s) 去掉字符串s右侧的空格
select concat('###','   aaa   ','###'),concat('###',RTRIM('   aaa   '),'###');


#TRIM(【BOTH 】s1 FROM s) 去掉字符串s开始与结尾的s1
select TRIM(BOTH '$' from '$$$aaa$$$');
select trim(both '*' from '**aa**bb**cc****'); -- aa**bb**cc
#TRIM(【LEADING】s1 FROM s) 去掉字符串s开始处的s1
select TRIM(LEADING '$' from '$$$aaa$$$');
select trim(leading '*' from '**aa**bb**cc****'); -- aa**bb**cc****
#TRIM(【TRAILING】s1 FROM s) 去掉字符串s结尾处的s1
select TRIM(TRAILING '$' from '$$$aaa$$$');
select trim(trailing '*' from '**aa**bb**cc****'); -- **aa**bb**cc

-- 7、LPAD/RPAD  左填充/右填充
#LPAD(str,len,padstr) 使用指定的padstr，在str字符串的左边填充数据，直至长度为len为止
select LPAD('aaa',10,'#');
select lpad('abc',10,'&*^');
#RPAD(str,len,padstr) 使用指定的padstr，在str字符串的右边填充数据，直至长度为len为止
select RPAD('aaa',6,'#');
select rpad('abcc',9,'!@'); -- abcc!@!@!

-- 8、UPPER/LOWER  变大写/变小写
#案例：查询员工表的姓名，要求格式：姓首字符大写，其他字符小写，名所有字符大写，且姓和名之间用_分割，最后起别名“OUTPUT”
select first_name , UPPER(first_name) '大写' from employees;
select first_name , LOWER(first_name) '小写' from employees;
select first_name , last_name , concat(UPPER(substring(last_name,1,1)) , LOWER(substring(last_name,2)) ,'_' , UPPER(first_name)) 'OUTPUT' from employees;

-- 9、STRCMP 比较两个字符大小
select STRCMP('aaa','abc');
select STRCMP('aaa','AAA');
select STRCMP('aaa','aaaa');

select strcmp('a','b'); -- -1
select strcmp('A','A'); -- 0
select strcmp('aa','cc'); -- -1

-- 10、LEFT/RIGHT  截取子串
#LEFT(s,n) 返回字符串s最左边的n个字符
select LEFT('aaabbb',3);
select left('update',5); -- updat
#RIGHT(s,n) 返回字符串s最右边的n个字符
select RIGHT('aaabbb',3);
select right('abcd',2); -- cd

-- 11、INSERT(str, index , len, instr) 将字符串str从第index位置开始，len个字符长的子串替换为字符串instr
select INSERT('aaabbbccc',4,3,'BBB');
select insert('employees',2,5,'xxx'); -- exxxees

-- REPLACE(str, a, b) 用字符串b替换字符串str中所有出现的字符串a
select REPLACE('你好你不好','你','我我我');
select replace('hhh好吗','h好','真的'); -- hh真的吗

-- 12、REPEAT(str, n) 返回str重复n次的结果
select REPEAT('abc',3);
select repeat('xiba你',2);

#4.2 数学函数
#二、数学函数
-- 1、ABS 绝对值
select ABS(-12) , ABS(0) , ABS(10);

-- 2、CEIL 向上取整  返回>=该参数的最小整数
select CEIL(12.3) , CEIL(12.7) , CEIL(-12.5);
select ceil(50.999), ceiling(50.011); -- 51 51

-- 3、FLOOR 向下取整，返回<=该参数的最大整数
select FLOOR(12.3) , FLOOR(12.7) , FLOOR(-12.5);

-- 4、ROUND 四舍五入
select ROUND(12.2) , ROUND(12.5) , ROUND(12.45,1);

-- 5、TRUNCATE 截断
select TRUNCATE(12.2,0) , TRUNCATE(12.5,0) , TRUNCATE(12.45,1);
select truncate(15.111,2) , truncate(15,1), TRUNCATE(-99.99,1); -- 15.11 15 -99.9

-- 6、MOD 取余
select MOD(10,3);


#4.3 日期时间函数
-- 1、NOW  系统当前时间
select SYSDATE();
select NOW(); //获取系统当前时间 2021-11-01 19:57:06

select YEAR(now()) as 年; 
select MONTH(now()) as 月;
select DAY(now()) as 日;
select HOUR(now()) as 小时; 
select MINUTE(now()) as 分钟; 
select SECOND(now()) as 秒; 

-- WEEKDAY(date)返回指定的时间是周几，注意，周1是0，周2是1，。。。周日是6
select WEEKDAY(now()) as 星期; 
-- DAYOFWEEK()返回当前是周几，注意：周日是1，周一是2，。。。周六是7
select DAYOFWEEK(now()) as 星期;
select concat('星期' , 
	case dayofweek(now())
		when 1 then '日'
		when 2 then '一'
		when 3 then '二'
		when 4 then '三'
		when 5 then '四'
		when 6 then '五'
		when 7 then '六'
	end)as '星期';
-- DAYNAME(date)返回指定的时间是星期几：MONDAY,TUESDAY.....SUNDAY
select DAYNAME(now());
-- MONTHNAME(date)返回指定的时间的月份：January，。。。。。
select MONTHNAME(NOW());

-- WEEK(date)返回一年中的第几周 WEEKOFYEAR(date)
select WEEK(now()) , WEEKOFYEAR(now());

-- 2、CURDATE  系统当前日期
-- 3、CURTIME  系统当前时间
select CURDATE();
select CURTIME();
select CURRENT_DATE();
select CURRENT_TIME();
select CURRENT_TIMESTAMP();

-- 4、DATEDIFF(date1,date2)  返回date1 - date2的日期间隔
-- TIMEDIFF(time1, time2)返回time1 - time2的时间间隔
select DATEDIFF('2020-10-10',NOW());
select DATEDIFF('2020-10-10','2020-11-11');
select TIMEDIFF('16:24:43','16:24:24');
select DATEDIFF('2020-10-10',CURDATE());
select TIMEDIFF('16:24:43',CURTIME());

-- 5、DATE_FORMAT(datetime ,fmt)  按照字符串fmt格式化日期datetime值
#案例：查看100号员工入职日期
select employee_id , hiredate , DATE_FORMAT(hiredate,'%y-%m-%d %H:%i:%s 星期%w') from employees where employee_id=100;
select hiredate , DATE_FORMAT(hiredate,'%Y_%m_%d %H_%i_%S 星期:%W') from employees; -- 1992_04_03 00_00_00 星期:Friday
-- 6、STR_TO_DATE 按指定格式解析字符串为日期类型
#案例：查看1998年6月以前入职的员工信息
select employee_id , hiredate 
from employees 
where hiredate < STR_TO_DATE('1998年6月','%Y年%m月');

select hiredate
from employees
where hiredate < STR_TO_DATE('1998年5月30日','%Y年%c月');



-- 7、DATE_ADD(datetime, INTERVALE  expr  type)  返回与给定日期时间相差INTERVAL时间段的日期时间
/*
	使用 DATE_ADD(NOW(),INTERVAL 1 MONTH) 这个函数来进行修改时间
	第一个参数是要修改的时间;
	第二个参数固定写法;
	第三个参数的修改的值 : 如果正数就是加,负数就是减;
	第四个参数可填YEAR,MONTH,DAY,HOUR,MINUTE,SECOND;
*/
select now() , DATE_ADD(now(),INTERVAL 1 MONTH);
select now() , DATE_ADD(now(),INTERVAL -1 DAY);
select now() , DATE_ADD(now(),INTERVAL 1 HOUR);
SELECT NOW() , DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH);

select now() , date_add(now(),INTERVAL -2 month);


#4.4 流程控制函数
-- 1、IF函数
#需求：如果有奖金，则显示最终奖金，如果没有，则显示0
#IF(expr1,expr2,expr3) expr1条件,expr1成立则显示expr2的值，否则显示expr3的值
select commission_pct , if(commission_pct is null ,0 , commission_pct) from employees;
select manager_id , if(manager_id is null,'Boss',manager_id) from employees;
select first_name , salary , if(salary>10000,'白领','蓝领') from employees;

#IFNULL(expr1,expr2) 若expr1为null，则显示expr2值，若不为null则显示自身的值
select commission_pct , IFNULL(commission_pct,0) from employees;
select manager_id , IFNULL(manager_id,'Boss') from employees;

select manager_id , IFNULL(manager_id,'Boss') from employees;

-- 2、CASE函数
/*
	情况1 ：类似于switch语句，可以实现等值判断
	CASE 表达式
	WHEN 值1 THEN 结果1
	WHEN 值2 THEN 结果2
	...
	ELSE 结果n
	END
*/
#案例：部门编号是30，工资显示为2倍；部门编号是50，工资显示为3倍；部门编号是60，工资显示为4倍；否则不变；
#显示部门编号，新工资，旧工资
select 
	department_id '部门编号' , 
	salary '旧工资' , 
	case department_id
		when 30 then salary*2
		when 50 then salary*3
		when 60 then salary*4
		else salary 
	end as 'newSalary'
from employees;

/*
	情况2：类似于多重IF语句，实现区间判断
	CASE 
	WHEN 条件1 THEN 结果1
	WHEN 条件2 THEN 结果2
	...
	ELSE 结果n
	END
*/
#案例：如果工资>20000,显示级别A；工资>15000,显示级别B；工资>10000,显示级别C；否则，显示D
select 
	salary as '工资',
	case
		when salary>20000 then '级别A' 
		when salary>15000 then '级别B' 
		when salary>10000 then '级别C' 
		else '级别D'
	end as 'level' 
from employees;

#4.5 分组函数
-- 案例：查询员工信息表中，所有员工的工资和、工资平均值、最低工资、最高工资、有工资的个数
select sum(salary) '工资和' , avg(salary) '工资平均值' ,
min(salary) '最低工资'  , max(salary) '最高工资' , count(salary)
from employees;

-- count() 函数
#添加筛选条件
-- 案例1:查询emp表中记录数
select count(*) from employees;

-- 案例2:查询emp表中有佣金的人数  count()会自动去除null值
select count(commission_pct) from employees;

-- 案例3:查询emp表中月薪大于2500的人数
select count(first_name) from employees where salary>2500;

-- 案例4:查询有领导的人数
select count(manager_id) from employees;

#其它用途
-- 案例5:统计结果集的行数，推荐使用count(*)。需求：查询员工表中30号部门的人数
select count(*) from employees where department_id=30;

-- 案例6:搭配distinct实现去重的统计。需求：查询有员工的部门个数
select count(distinct department_id) from employees;

-- SUM() 求和函数
-- 案例7:查询所有员工月薪和
select sum(salary) from employees;

-- AVG() 平均值函数
-- 案例8:统计所有员工平均工资
select AVG(salary) from employees;

-- MAX() 和 MIN()  最大值和最小值函数
-- 案例9:查询最高工资和最低工资
select MAX(salary) from employees;
select AVG(salary) from employees;


#4.6 作业
-- 1. 显示系统时间(注：日期+时间) 
select concat(now(),' 星期',weekday(now())+1);
-- 2. 查询员工号，姓名，工资，以及工资提高百分之 20%后的结果（new salary）
select department_id , first_name , salary , salary*(1+0.2) as newSalary
from employees;
-- 3. 将员工的姓名按首字母排序，并写出姓名的长度（length）
select first_name , length(first_name) as length
from employees
order by substring(first_name,1,2);
-- 4. 做一个查询，产生下面的结果
-- <last_name> earns <salary> monthly but wants <salary*3>
-- Dream Salary
-- King earns 24000 monthly but wants 72000
select concat(last_name , ' earns ' ,  salary , ' monthly but wants ' , salary*3 )
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
-- king AD_PRES A
select 
	last_name , 
	job_id , 
	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'
	else 'Other'
	end as 'grade'
from employees;

-- 1. 查询公司员工工资的最大值，最小值，平均值，总和
select first_name , max(salary) as max , min(salary) as min , avg(salary) as avg , sum(salary) as sum
from employees;

-- 2. 查询员工表中的最大入职时间和最小入职时间的相差天数 （DIFFRENCE）
select max(hiredate) , min(hiredate) ,abs(datediff(max(hiredate),min(hiredate))) as '相差天数'
from employees;
-- 3. 查询部门编号为 90 的员工个数
select  count(department_id)
from employees
where department_id = 90;