数据库-进阶4-常见函数

数据库-进阶4-常见函数

进阶4：常见函数
/*
   概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
   好处：1.隐藏了实现细节
	 2.提高代码的重用性
   调用：select 函数名(实参列表)【from表】；
   特点：
	 ① 叫什么（函数名）
	 ② 干什么（函数功能）
   分类：
	 1.单行函数
	    如 concat、length、ifnull等
	 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
		database
		user
	控制函数：
		if
		case
    2.分组函数：
	/*
	功能：用作统计使用，又称为聚合函数或统计函数或组函数
	分类：
		sum 求和
		avg 平均值
		max 最大值
		min 最小值
		count 计算个数
			select count(*) from student;#统计行数(一般统计个数的时候都用该行代码)
	特点：
		1.sum、avg 一般用于处理数值型
		  max、min、count 可以处理任何类型
		2.以上分组函数都忽略null值  
		3.可以和 distinct 搭配实现去重的运算
		4.count函数的单独介绍
		    效率：
			MYISAM存储引擎下，count(*)效率高
			ISNODB存储引擎下，count(*) 和 count(1) 效率差不多，比 count(字段) 要高一些
		    一般使用count(*)统计行数
		5.和分组函数一同查询的字段要求是 group by 后的字段    	
		
	*/
	#1.简单实用
		SELECT SUM(salary) AS 工资之和 FROM student;
		SELECT AVG(salary) AS 平均工资 FROM studetn;
		SELECT MAX(salary) AS 最高工资 FROM student;
		SELECT MIN(salary) AS 最低工资 FROM student;
		SELECT COUNT(salary) AS 统计个数 FROM student;
		
		SELECT SUM 和,AVG(salary) 平均,MAX(salary) 最高, MIN(salary) 最低, COUNT(salary) 个数 FROM student;
		
		SELECT SUM 和,ROUND(AVG(salary),2) 平均,MAX(salary) 最高, MIN(salary) 最低, COUNT(salary) 个数 FROM student;
	#2.参数支持哪些类型	
*/

#一、字符函数

	# 1.length 获取参数值的字节个数
		SELECT LENGTH('john');
		SELECT LENGTH('张三1a');# 一个汉字占三个字节

		SHOW VARIABLES LIKE '%char%'# 获取客户端字符集
		
	# 2.concat 拼接字符串
		SELECT CONCAT(last_name,'_',first_name) FROM student;	

	# 3.upper、lower
		SELECT UPPER('join');#变大写
		SELECT LOWER('joHN');#变小写
	# 将姓变大写，名变小写，然后拼接
		SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM student;

	# 4.substr、substring
		# 注意：sql 索引从 1 开始
		# 截取指定索引处后面的所有字符
		SELECT SUBSTR('李莫愁爱上了路展元',7) out_put;
		# 截取指定索引处后指定长度的字符
		SELECT SUBSTR('李莫愁爱上了路展元',1,3) out_put;# 这里的 3 是保留长度
		
		# 姓名中首字母大写，其他字母小写，且用'_'连接，显示处理
		SELECT UPPER(SUBSTR(last_name),1,1),'_',UPPER(SUBSTR(first_name),2)) out_put FROM student
		
	# 5.instr 返回字串第一次出现的索引，如果找不到返回0
		SELECT INSTR('杨不悔爱上了因留下','因留下') AS out_put;	#7
		SELECT INSTR('杨不悔爱上了因留下','因留上') AS out_put;	#0
		
	# 6.trim
		SELECT LENGTH(TRIM('   张三丰   ')) AS put_out;	#一个汉字占 3 个字符
		SELECT TRIM('a' FROM 'aaaaa张aa三丰aaaaaaaaaa') AS put_out;#张aa三丰
		SELECT TRIM('aa' FROM 'aaaaa张aa三丰aaaaaaaaaa') AS put_out;#a张aa三丰
		
	# 7.lpad 左填充 用指定的字符实现左填充，达到指定长度
		SELECT LPAD('殷素素',10,'*') AS put_out;#*******殷素素
		SELECT LPAD('殷素素',2,'*') AS put_out;#殷素
		
	# 8.rpad 右填充 用指定的字符实现右填充指定长度
		SELECT RPAD('殷素素',12,'ab') AS put_out;#殷素素ababababa

	# 9.replace
		SELECT REPLACE('周芷若张无忌爱上了周芷若周芷若','周芷若','赵敏') AS put_out;#全部替换
		
#二、数字函数
	#round 四舍五入
		SELECT ROUND(-1.45) AS put_out;#-1
		SELECT ROUND(-1.55) AS put_out;#-2
		SELECT ROUND(1.567,2) AS put_out;#1.57
	#ceil 向上取整 返回 >= 该参数的最小整数
		SELECT CEIL(1.001) AS put_out;#2
		SELECT CEIL(-1.01) AS put_out;#-1
		SELECT CEIL(1.00) AS put_out;#1
	#floor 向下取整 返回 <= 该参数的最大整数
		SELECT FLOOR(9.99) AS put_out;#9
		SELECT FLOOR(-9.99) AS put_out;#-10
	#truncate
		#保留 1 位
		SELECT TRUNCATE(1.4999,1);#1.4
	#mod取余
	/*
		mod(a,b);---> a-a/b*b
		mod(-10,-3);--->-10-(-10)/(-3)*(-3)--->-1
	*/
		SELECT MOD(10,3);#1
		SELECT MOD(-10,-3);#-1
		SELECT MOD(10,-3);#1
		SELECT MOD(-10,3);#-1
		SELECT 10%3;#1
#三、日期函数
	#now 返回当前系统日期＋时间
		SELECT NOW();#2021-08-04 15:35:50
	#curdate 返回系统当前日期不包含时间
		SELECT CURDATE();#2021-08-04
	#curtime 返回系统当前时间不包含日期
		SELECT CURTIME();#15:37:30
	#可以获取指定的部分：年、月、日、时、分、秒
		SELECT YEAR(NOW()) 年;
		SELECT YEAR('1998-1-1') 年;
		SELECT YEAR(hiredate) 年 FROM student;
		
		SELECT MONTH(NOW()) 月;
		SELECT MONTHNAME(NOW()) 月;#August
		...
	#str_to_date 将字符通过指定的格式转换成日期
		%Y 四位数的年份		%Y 两位数的年份
		%m 月份(01,02...12)	%c 月份(1,2...12)
		%d 日(01,02...)		
		%H 小时(24小时)		%h 小时(12小时)
		%i 分钟(00,01....)	%c 秒(00,01...)
		SELECT STR_TO_DATE('1992-1-1','%Y-%c-%d') AS put_out;#1992-01-01
		#查询入职日期为 1992-4-3 的员工
			SELECT * FROM student WHERE hiredate = '1992-1=1'; 
			SELECT * FROM student WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');
	#date_format 将日期转换成字符
		SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日') AS put_out ;#2021年08月04日
	#查询有奖金的员工名和入职日期(xx月/xx日 xx年)
		SELECT last_name,DATE_FORMAT(hiredate,'%m月%d日 %Y年') 入职日期
		FROM student
		WHERE commission_pct IS NOT NULL;
		
#四、其他函数
	SELECT VERSION();#查看版本  8.0.25
	SELECT DATABASE();#当前数据库
	SELECT USER();#当前用户  root@localhost

#五、流程控制函数
	#1.if函数：if else 的效果
		SELECT IF(10 > 5,'大','小');#大
		
		SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，呵呵','有奖金，嘻嘻') 备注
		FROM student;
	#2.case函数
		#使用 1 switch case 的效果
			/*
			java中
				switch(变量或表达式){
					case 常量 1: 语句 1; break;
					default: 语句 n; break;
				}
			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 student;
		#使用 2：类似于 多重 if
			/*
			java中：
				if(条件 1){
					语句 1;
				} else(条件 2){
					语句 2;
				}
				
			mysql中：
				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 student;
			
#1.显示系统时间(注：日期+时间)	
	SELECT NOW();#2021-08-06 14:15:36
#2.查询员工号，姓名，工资，以及工资提高20%后的结果(new salary)
	SELECT id,last_name,salary,salary*1.2 "new salary"
	FROM student;
#3.将员工的姓名按首字母排序，并写出姓名的长度(LENGTH)
	SELECT LENGTH(last_name) 长度,SUBSTR(last_name,1,1) 首字符,last_name
	FROM student
	ORDER BY 首字符;
#4.做一个查询，产生下面的结果
	/*
		<last_name> earns <salary> moneys but wants <salary*3>
		Dream Salary
		King earns 24000 monthly but wants 72000
	*/
	SELECT CONCAT(last_name,' earns ',salary,' but wants ',salary*3) AS "Dream Salary"
	FROM student
	WHERE salary = 24000;	
#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 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 grand
	FROM student
	WHERE job_id = 'AD_PRES';

#1.查询公司员工工资的最大值，最小值，平均值，总和
	SELECT MAX(salary) ma_sal,MIN(salary) mi_sal,ROUND(AVG(salary),2) av_sal,SUM(salary) su_sal
	FROM student;
#2.查询员工表中的最大入职时间和最小入职天数的相差天数(DIFFRENCE)
	SELECT DATEDIFF(MAX(hiredate)-MIN(hiredate)) DIFFRENCE
	FROM student;
#3.查询部门编号为 90 的员工个数
	SELECT COUNT(*) 员工个数
	FROM student
	WHERE department_id = 90;