MYSQL--函数

程序员文章站 2024-03-23 12:46:10

...

进阶4：常见函数

概念：将一组逻辑语句封装在方法体中，对外暴露方法名
好处：
1.隐藏了实现细节
2.提高代码重用性
调用：select 函数名（实参列表）【from 表】（可省略【】）
特点：
1.叫什么（函数名）
2.干什么（函数功能）
分类：
1.单行函数
如:concat、length、ifnull等
2.分组函数
功能：做统计使用，又称为统计函数，聚合函数，组函数

单行函数

一、字符函数
1.length 获取参数值的字节个数

select LENGTH('jion');
select LENGTH('金毛狮王');

查看使用的字符集（utf8字母占一个字节，汉字占三个字节,GDK字母占一个字节，汉字占两个字节）

SHOW VARIABLES LIKE '%char%'

MYSQL--函数

2.concat 拼接字符串

SELECT CONCAT(last_name,'_',first_name) 姓名 from employees;

3.upper 字母转成大写、lower 字母转成小写

SELECT UPPER('JiHn');
SELECT LOWER('JoHn');

#示例：将姓变成大写，名小写，然后拼接
SELECT concat(UPPER(last_name),'_',LOWER(first_name)) 姓名 from employees;

4.substr 字符串截取、substring

注意：索引从1开始

#截取从指定索引处后面所有字符串
SELECT SUBSTR('小伙子牛逼啊',4) out_put;      #----> 牛逼啊

#截取从指定索引处指定字符长度的字符串
SELECT SUBSTR('小伙子牛逼啊',4,2) out_put;    #----> 牛逼

#案例：姓名中首字符大写，其他字符小写，然后用_拼接，显示出来
SELECT CONCAT(UPPER(substr(last_name,1,1)),'_',LOWER(substr(last_name,2))) 姓名 from employees;

5.instr 返回子串第一次出现的索引，如果找不到返回0

SELECT INSTR('小伙子牛逼啊','牛逼') out_put;  #-----> 4

6.trim 去掉前后的指定字符（默认为空格）

SELECT TRIM('       去掉前后的字符默认为空格        ' ) AS out_put;

SELECT TRIM('a' from 'aaaaaaa去掉前后aaaaa的字符默认为空格aaaaaaa' ) AS out_put;

MYSQL--函数

7.lpad 用指定字符实现左填充指定长度

SELECT LPAD('左填充%到达20个字符长度',20,'%') AS out_put;

MYSQL--函数
8.rpad

SELECT RPAD('右填充',20,'%') AS out_put;

9.replace 替换

replace（‘str’,‘想被替换的内容’,‘想换成的内容’）（匹配到的所有内容全部替换）

SELECT replace('我去给你买个橘子,大橘子','橘子','屎') AS out_put;

MYSQL--函数

二、数学函数

1.round 四舍五入 round(数,小数点后保留几位)

SELECT ROUND(1.65);
SELECT ROUND(1.6599,2);

2.ceil 向上取整，返回>=该参数的最小整数

SELECT CEIL(-1.02);

3.floor 向下取整，返回<=该参数的最小整数

SELECT floor(-1.02);

4.truncate 截断 truncate(数,小数点后保留几位)

SELECT truncate(1.456,1);

MYSQL--函数

5.mod 取余 mod(a,b) --> a-a/b*b

SELECT mod(10,3);

三、日期函数

1.now 返回当前系统日期+时间

SELECT NOW();

2.curdate 返回当前系统日期，不包括时间

SELECT CURDATE();

3.curtimr 返回当前的时间，不包括日期

SELECT CURTIME();

4.获取指定的部分，年，月，日，小时，分钟，秒

SELECT YEAR(now()) 年;
SELECT YEAR('1892-1-15') 年;
SELECT year(hiredate) 年 from employees;

SELECT month(now()) 月;   			#返回数字月份
SELECT monthname(now()) 月; 		#返回英文月份

5.str_to_date 将日期格式的字符转换成指定格式的日期

%Y:4位的年份
%y:2位的年份
%m:月份(01,02,11,12)
%c:月份(1,2,11,12)
%d:日(01,02)
%H:小时(24小时制)
%h:小时(12小时制)
%i:分钟(00,01,59)
%s:秒(00,01,59)

SELECT STR_TO_DATE('9-13-1454','%m-%d-%Y')

MYSQL--函数

查询入职时间日期位1992-4-3的员工信息
SELECT * FROM employees WHERE hiredate = '1992-4-3';

SELECT * FROM employees WHERE hiredate = STR_TO_DATE('3-4 1992','%d-%c %Y');

6.date_format 将日期转换成字符

SELECT DATE_FORMAT('2018/3/4','%Y年%m月%d日');

MYSQL--函数

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

7.datediff 求日期差

SELECT DATEDIFF('2020-5-20','2020-5-1');

MYSQL--函数

四、其它函数

SELECT VERSION();
SELECT DATABASE();  #查询当前的库
SELECT user();			#当前的用户

五、流程控制函数

1.if函数： if else 的效果

SELECT IF(10<5,'大','小');

2.case函数的使用一：类似于 swhich then

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后的字段

1.简单的使用

SELECT sum(salary) 和,avg(salary) 平均,concat(max(salary),'_',min(salary)) 最大最小,count(salary) 个数 
from employees;

2.和distinct(去重复)搭配

SELECT SUM(salary),sum(DISTINCT(salary)),count(salary),count(DISTINCT(salary)) 
from employees;

3.count函数的详细介绍

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

SELECT COUNT(commission_pct) from employees;
SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM employees;

4.和分组函数一同查询的字段有限制

SELECT AVG(salary),employee_id FROM employees;  #  1对多，没意义

MYSQL--函数

#练习1：查询员工表中的最大入职时间和最小入职时间的相差天数
SELECT datediff(MAX(hiredate),MIN(hiredate)) from employees;

#练习2：查询部门编号为90的员工个数
SELECT COUNT(*) from employees WHERE department_id = 90;

进阶5：分组函数

语法：
			SELECT 			分组函数，列（要求出现在group by 的后面）
			FROM				表
			【where 		筛选条件】
			GROUP BY		分组列表
			【order by 	子句】
注意：
			查询列表必须特殊，要求是分组函数和group by后出现的字段
特点：
			1.分组查询中的筛选条件分为两类

	数据源	位置	关键字
分组前筛选	原始表	group by 子句的前	where
分组后筛选	分组后的结果集	group by 子句的后	having

				①分组函数做条件肯定放在having子句中
				②能用分组前筛选的，就优先考虑分组前筛选
			2.group by子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开没有顺序要求），表达式或函数（用的较少）
			3.也可以添加排序（排序放在整个分组查询的最后）

#引入：查询每个部门的平均工资
SELECT AVG(salary),department_id from employees GROUP BY department_id;

#案例1：查询每个工种的最高工资
SELECT MAX(salary),job_id from employees GROUP BY job_id;

#案例2：查询每个位置上的部门个数
SELECT COUNT(*),location_id from departments GROUP BY location_id;

添加筛选条件

#案例1：查询邮箱中包含a字符的，每个部门的平均工资
SELECT 
	AVG(salary),department_id 
FROM
	employees
WHERE
	email LIKE '%a%'
GROUP BY
	department_id;

#案例2：查询有奖金的每个领导手下员工的最高工资
SELECT 
	last_name,MAX(salary),manager_id
FROM
	employees
WHERE
	commission_pct is not null
GROUP BY
	manager_id;

添加复杂的筛选条件

#案例1：查询哪个部门的员工个数>2
#①查询每个部门的员工个数
SELECT 
	COUNT(*),department_id
from	
	employees
GROUP BY
	department_id;
#②根据①的结果筛选，查询哪个部门的员工个数>2
SELECT 
	COUNT(*),department_id
from	
	employees
GROUP BY
	department_id
HAVING
	COUNT(*) > 2;

#案例2：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT
	MAX(salary),job_id
FROM
	employees
WHERE
	commission_pct is not NULL
GROUP BY
	job_id
HAVING
	MAX(salary) > 12000;

#案例3：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资
SELECT
	MIN(salary),manager_id
FROM
	employees
WHERE
	manager_id > 102
GROUP BY
	manager_id
HAVING
	MIN(salary) > 5000;

按表达式或函数分组

(MYSQL：WHERE不支持用别名，group by、having支持别名，其他数据库可能不支持)

#案例：按员工姓名的长度进行分组，查询每一组的员工个数，筛选员工个数>5的有哪些
SELECT
	LENGTH(last_name) len_name,COUNT(*) 人数
from	
	employees
GROUP BY
	len_name
HAVING
	人数 > 5;

按多个字段分组

#案例：查询每个部门每个工种的员工的平均工资
SELECT
	AVG(salary),department_id,job_id
FROM
	employees
GROUP BY
	department_id,job_id;

添加排序

#案例：查询每个部门每个工种的员工的平均工资，并且>10000的按平均工资的高低显示
SELECT
	AVG(salary),department_id,job_id
FROM
	employees
WHERE
	department_id is not NULL
GROUP BY
	department_id,job_id
HAVING
	AVG(salary) > 10000
ORDER BY
	AVG(salary) desc;

#---------------------------练习---------------------------
#1.查询个job_id的员工工资的最大值，最小值，平均值，总和，并按job_id升序
SELECT
	MAX(salary),MIN(salary),AVG(salary),SUM(salary),job_id
from	
	employees
GROUP BY
	job_id
ORDER BY
	job_id ASC;

#2.查询员工最高工资和最低工资的差距（DIFFRENCE)
SELECT
	MAX(salary)-MIN(salary) 差距
FROM
	employees

#3.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
SELECT
	MIN(salary),manager_id
FROM
	employees
WHERE
	manager_id is not null
GROUP BY
	manager_id
HAVING
	min(salary) >= 6000;

#4.查询所有部门的编号，员工数量和工资平均值，并按平均工资降序
SELECT
	department_id,COUNT(*),AVG(salary)
FROM
	employees
GROUP BY
	department_id
ORDER BY
	AVG(salary) desc;

#5.选择具有各个job_id的员工人数
SELECT
	COUNT(*) 个数,job_id
FROM
	employees
GROUP BY
	job_id;

#---------------------------------------------------------

MYSQL--函数

进阶4：常见函数

单行函数

进阶5：分组函数

Swift 函数指定参数默认值

MYSQL--函数

Mysql相关函数----concat、concat_ws、group_concat

《从零开始学Swift》学习笔记（Day 42）——构造函数调用规则

Python学习笔记四（函数）

带你学MySQL系列 | 这份MySQL函数大全，真的超有用！

Python学习（四）——函数

MFC单文档应用程序子线程向主线程发送消息调用函数

关于MFC中AfxGetApp函数是怎么得到全局对象的指针的简要分析

Swift函数式编程详解