（MySQL学习笔记）常见函数-单行函数、分组函数

常见函数

/*
类似于java方法，将一组逻辑语句封装在方法体中，对外暴露方法名
1、隐藏了实现细节
2、提高代码的重用性

调用：
	select 函数名(实参列表) [from 表]；
	什么时候用 from 表 ： 实参列表中用到了表中的字段
分类：
	1、单行函数
		功能：返回一个值
		如concat(),length(),ifnull()等
		①字符函数
		②数学函数
		③日期函数
		④其他函数
		⑤流程控制函数
	2、分组函数-统计函数-聚合函数-组函数
		功能：做统计使用
*/

单行函数

一、字符函数

1、length(str):用于获取参数值的字节

SELECT LENGTH('length');
SELECT LENGTH('数据库');

2、concat():拼接字符

SELECT 
	CONCAT(last_name,'_',first_name)
FROM 
	employees;

3、upper、lower

#变大写
SELECT UPPER('joHn');
#变小写
SELECT LOWER('joHn');
#例：将姓变大写，名变小写，然后拼接
SELECT
	CONCAT(UPPER(first_name),LOWER(last_name))
FROM 
	employees;

4、substr、substring 截取字符

注意：索引从1开始

#substr(str,pos):从pos开始截取后面所有字符
SELECT SUBSTR('一二三四五六七八九十',6);

#substr(str,pos,len):从pos开始截取len个字符长度
SELECT SUBSTR('一二三四五六七八九十',1,5);

#substr(str FROM pos)
SELECT SUBSTR('一二三四五六七八九十' FROM 6);

#substr(str\nstr FROM pos FOR len)
SELECT SUBSTR('一二三四五六七八九十' FROM 1 FOR 5);

#例：姓名中首字母大写，其他字母小写然后用_拼接
SELECT
	CONCAT(UPPER(SUBSTR(last_name,1,1)),LOWER(SUBSTR(last_name,2)),'_',LOWER(first_name))
FROM
	employees;

5、instr：返回子串在字符串中第一次出现的起始索引

#instr(str,substr)
SELECT INSTR('一二三四五六七八九十','八九十');
SELECT INSTR('八九十一二三四五六七八九十','八九十');

6、trim去掉前后空格

#trim([{both | leading | trailing} [remstr] from] str)
SELECT TRIM('    一二三     ');

#去掉前后端指定字符
SELECT TRIM('a' FROM 'aaaaaMMMaaaaMMMaaaaaa');

#去掉头部的指定字符
SELECT TRIM(LEADING 'a' FROM 'aaaaaMMMaaaaMMMaaaaaa');

#去掉尾端的指定字符
SELECT TRIM(TRAILING 'a' FROM 'aaaaaMMMaaaaMMMaaaaaa');

#
SELECT TRIM('abc' FROM 'abccbaabc');

7、lpad 左填充

#LPAD(str,len,padstr):将str补够10个，在左边（L）进行填充padstr
SELECT LPAD('123',10,'0');

#str超过了len，则会进行截断
SELECT LPAD('123',2,'0');

#padstr可以不为单个字符
SELECT LPAD('123',10,'*&');

8、rpad 右填充

#RPAD(str,len,padstr):将str补够10个，在左边（L）进行填充padstr
SELECT RPAD('123',10,'0');

#str超过了len，则会进行截断
SELECT RPAD('123',2,'0');

#padstr可以不为单个字符
SELECT RPAD('123',10,'*&');

9、replace 替换

#replace(str,from_str,to_str):将str中的所有from_str替换为to_str
SELECT REPLACE('123123123123456789','123','$$');

二、数学函数

1、round四舍五入

#①round(X);

SELECT ROUND(-1.5);
#②round(X,D); D:在第D位进行四舍五入，后面全置0；D>0是小数部分，D<0是整数部分
SELECT ROUND(-2.225234,2);
SELECT ROUND(-25532,-2);

2、ceil向上取整

#ceil(x):返回大于等于x的最小整数
SELECT CEIL(-1.002);

3、floor向下取整

#floor(x)：返回小于等于x的最大整数
SELECT FLOOR(-9.98);

4、truncate 截断

#truncate(X,D)：X小数点后保留D位
SELECT TRUNCATE(1.22345,4);

5、mod取余

#mod(n,m):n对m取余，结果为n-n/m*m,n为正则结果为正，n为负则结果为负；
SELECT MOD(4,3);

三、日期函数

1、now()返回当前系统日期+时间

SELECT NOW();

2、curdate() 返回当前系统日期，不包含时间

SELECT CURDATE();

3、curtime()返回当前时间，不包含日期

SELECT CURTIME();

可以获得制定的部分：年 月 日 小时 分钟 秒

4、year() 年

SELECT YEAR(NOW());

5、month(),monthname()月

SELECT MONTH(NOW());
SELECT MONTHNAME(NOW());  #英文显示

6、day()日

SELECT DAY(NOW());

7、hour()小时

SELECT HOUR(NOW());

8、minute()分钟

SELECT MINUTE(NOW());

9、second()秒

SELECT SECOND(NOW());

10、str_to_date:将字符转换为日期

#STR_TO_DATE(str,FORMAT)：format为指定的格式
SELECT STR_TO_DATE('9-12-2000','%d-%m-%Y');

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

11、date_format：将日期转换为字符

#date_format(data,format)
SELECT DATE_FORMAT(NOW(),'%m %d %Y');

#例：查询有奖金的员工的员工名和入职日期（xx月/xx日/xx年）
SELECT
	last_name,
	DATE_FORMAT(hiredate,'%m月/%d日/%y年')；
FROM
	employees
WHERE 
	commission_pct IS NOT NULL;

四、其他函数

1、version查看版本号

SELECT VERSION();

2、database查看当前的库

SELECT DATABASE();

3、user当前的用户

SELECT USER();

五、流程控制函数

1、if函数：实现if else 的效果

#if(expr1,expr2,expr3)
#expr1:条件表达式，true->expr2,false->expr3
SELECT IF(10>5,'对','错');

#例：查询
SELECT 
	last_name,IF(commission_pct IS NOT NULL,'y','n')
FROM
	employees;

2、case函数

①与switch case效果类似

/*	case 需要判断的字段或者表达式
	when 常量1 then 要显示的值1或语句1
	when 常量2 then 要显示的值1或语句2
	...
	else 要显示的值n或者语句n
	end
*/

/*例：查询员工的工资，要求：

部门号为30：显示的工资为1.1倍
部门号为40：显示的工资为1.2倍
部门号为50：显示的工资为1.3倍
其他部门，显示的工资为原工资

*/
SELECT 
	salary,
	CASE department_id
	WHEN 30 THEN 1.1*salary
	WHEN 40 THEN 1.2*salary
	WHEN 50 THEN 1.3*salary
	ELSE salary
	END
	AS 新工资
FROM 
	employees;

②多重if

/*
case
when 条件 then 要显示的值(结尾不加分号)或者语句（结尾需要加分号）
when 条件 then 要显示的值或者语句
else 要显示的值或者语句
...
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 '级别'
FROM
	employees
ORDER BY 
	salary DESC;

#例：显示系统时间（注：日期+时间）
SELECT NOW();

#例：查询员工号，姓名，工资，以及工资提高百分之二十之后的结果(new salary)
SELECT 
	employee_id,
	last_name,
	salary,
	salary*1.2 'new salary'
FROM 
	employees;

#例：将员工的姓名按首字母排序，并写出姓名的长度
SELECT 
	last_name,
	LENGTH(last_name) AS 'length'
FROM 
	employees
ORDER BY 
	SUBSTR(last_name,1,1) ASC;

#例：
SELECT 
	CONCAT(last_name,' earns ',salary,' monthly but wants ',salary*3,' Dream salary ')
FROM 
	employees;

#例：
SELECT 
	job_id,
	CASE job_id
	WHEN 'AD_PRES' THEN 'A'
	WHEN 'ST_MAN' THEN 'B'
	WHEN 'IT_PROG' THEN 'C'
	END grade
FROM 
	employees
WHERE
	job_id IN ('AD_PRES','ST_MAN','IT_PROG')
ORDER BY 
	grade ASC;

分组函数

/*
功能：用作统计实用，又称为聚合函数或统计函数或组函数

分类：
	sum求和
	avg平均值
	max最大值
	min最小值
	count计算个数
*/

1、简单的使用

#sum([distinct] expr)
#avg([distinct] expr)
#min([distinct] expr)
#max([distinct] expr)
/*
COUNT(X)
COUNT(X,X)
COUNT(expr)
COUNT(DISTINCT expr,[expr...])
*/

/*
特点：
1、#①sum，avg用于处理数值型
   #②max，min，count任何类型都支持，count计算不为null的个数
2、以上分组函数都忽略了null
3、和distinct搭配-去重
4、count函数的单独介绍
	一般使用count(*)统计行数
5、和分组函数一同查询的字段要求是group by后的字段
*/

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;	#salary中非null的个数

SELECT SUM(salary) 'sum',MIN(salary) 'min',MAX(salary) 'max',COUNT(salary) 'count'
FROM employees;	

2、参数支持哪些类型

#①sum，avg用于处理数值型
#②max，min，count任何类型都支持，count计算不为null的个数

3、是否忽略null

#以上运算忽略了null

4、和distinct搭配-去重

SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;

5、count函数的详细介绍

/*
COUNT(X)
COUNT(X,X)
COUNT(expr)
COUNT(DISTINCT expr,[expr...])
*/

SELECT COUNT(salary) FROM employees;

#计算总行数
SELECT COUNT(*) FROM employees;

#count里面参数为常量值，相当于在表中加了一列这个常量值
SELECT COUNT(1) FROM employees;

/*

效率:
MYISAM存储引擎下，count(*)效率最高
INNODB存储引擎下，count(*)和count(1)效率差不多，比count(字段)高

*/

6、和分组函数一同查询的字段有限制

#****报错****
SELECT AVG(salary),employee_id FROM employees;

#例：查询公司员工工资的最大值，最小值，平均值，总和
SELECT MAX(salary),MIN(salary),AVG(salary) FROM employees;

#例：查询员工表中的最大入职时间和最小入职时间的相差天数（difference）
SELECT MAX(hiredate),MIN(hiredate) FROM employees;

#例：查询部门编号为90的员工个数
SELECT 
	last_name
FROM 
	employees
WHERE 
	department_id = 90;
SELECT COUNT(*) FROM employees WHERE department_id=90;