MYSQL--函数

最新推荐文章于 2024-06-21 21:22:44 发布
最新推荐文章于 2024-06-21 21:22:44 发布
阅读量339 收藏
点赞数 1
分类专栏： 函数 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45215602/article/details/106117615
版权

进阶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%'

在这里插入图片描述

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;

在这里插入图片描述
在这里插入图片描述

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

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

在这里插入图片描述
8.rpad

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

9.replace 替换

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

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

在这里插入图片描述

二、数学函数

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);

在这里插入图片描述

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')

在这里插入图片描述

查询入职时间日期位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日');

在这里插入图片描述

查询有奖金的员工名和入职日期(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');

在这里插入图片描述

四、其它函数

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对多，没意义

在这里插入图片描述

#练习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;

#---------------------------------------------------------
田大能耐
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql常见函数
ywl470812087的博客
05-10 11万+
#进阶4：常见函数 /* 概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名 好处：1、隐藏了实现细节 2、提高代码的重用性 调用：select 函数名(实参列表) 【from 表】; 特点： ①叫什么（函数名） ②干什么（函数功能） 分类： 1、单行函数 如 concat、length、ifnull等 2、分组函数 功能：做统计使用，又称为统计函数、聚合函数、组函数 常见函数： 一、单行函数 ...
MySQL —— 函数
weixin_46540606的博客
02-01 2269
概述 在MySQL中，为了提高代码重用性和隐藏实现细节，MySQL提供了很多函数，函数可以理解为别人封装好的模板代码 分类 在MySQL中，函数非常多，主要可以分为以下几类： 聚合函数 数学函数 字符串函数 日期函数 控制流函数 窗口函数 聚合函数 在MySQL中聚合函数主要是：count、sum、min、max、avg…在前面的学习中都有学习过，还有一个特殊的函数，是没有学习过的 —— group_concat()，该函数用于实现行的合并，group_concat()函数首先根据 group by指定
MySQL基础知识8——聚合函数
lihaoen666的博客
04-18 219
COUNT（统计数量） 统计满足条件的记录数有多少条，一般按照分组的形式统计。 基本语法： select count(*) | count(列名) from tbl-name 例子： 1. 统计班级里的学生 select count(*) AS nums from class; 2. 显示员工总数，以及获得奖金的员工人数 select ...
MySQL[笔记][基础部分四]
SwaeLeeUknow的博客
07-31 226
常见函数： 字节函数： length concat substr instr trim upper lower lpad rapd replace 数学函数： round ceil floor truncate mod 日期函数： now curdate curtime year month monthname day DATEDIFF#日期相差天数 hour minute second str_to_date date_fromat 其他函数： version database user 控制函数 if
SQL语句之函数查询--SQL常用函数
traveler-leon的博客
04-20 4124
sql语句select之函数 字符函数 一、字符函数 1.length 获取参数值的字节个数 select length(‘john’); select length(‘张三丰hahahah’) 2.concat拼接多个字段 slect concat(last_name,’_’,first_name) as 姓名 from employees; 目的：拼接last_name和first_name字段，中间用下划线连接 3.upper将字符转为大写 select upper(‘john’); 4.lower将
DQL（数据查询语言）语言学习
weixin_43572928的博客
11-26 1094
数据查询，单表查询，多表查询
MYSQL之函数
m0_62528678的博客
07-05 975
目录一. 函数定义一、常用函数 1.日期函数 2.字符串函数 3.数字函数二、条件判断函数三、聚合函数(SUM/COUNT/AVG/MAX/MIN)四、合并(union) 1.类似Java定义方法 2.可以带参数 3.必须有返回值(一行一列) ，重点：java方法可以没有返回值，数据库函数必须要有返回值 select max(score) from sc; 4.函数可以嵌套到sql语句中 注：存储过程则不可以 5.函数可嵌套调用 round(
mysql-窗口函数概述
12-21
mysql中窗口函数的概述以及一些数据练习，可以更好的对窗口函数理解。 什么是窗口函数：可以像聚合函数一样对一组数据进行分析并返回结果，二者的不同之处在于，窗口函数不是将一组数据汇总成单个结果，而是为每一行...
mysql-窗口函数-实验三.docx
06-15
窗口函数的使用
mysql-connector-odbc
06-05
在mysql中，odbc全称“Open Database Connectivity”，中文意思为“开放式数据库连接” ODBC是用于访问数据库的开放式标准应用程序编程接口 (API)。1992年，微软与Simba合作打造了世界上第一个ODBC驱动；SIMBA.DLL ...
MySql-窗口函数-面试题.md
08-09
MySql-窗口函数-面试题，很细，有函数讲解，有题干和解答
mysql-installer-community-8.0.30.0.msi
09-04
2020年1月13日，MySQL官方...在JSON函数中使用多值索引（JSON functions using multi-valued indexes） MySQL shell （管理MGR的MySQL客户端 ） Innodb Cluster （来自官方的MGR产品解决方案） MGR 在复制上的提升
MySQL学习之路（六）：常见函数
qq_42826747的博客
05-21 192
常见函数 概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名 好处： 隐藏了实现细节 提高代码的重用性 调用： select函数名（实参列表）【FROM 表名】（如果实参列表中用到的字段属于某个表，则需要加入某个表） 学习的关键： 叫什么（函数名） 干什么（函数功能） 分类： 单行函数，如concat、length、ifnull等 分组函数（统计函数、聚合函数、组函数），功能：做统计使用 一、单行函数 1.字符函数 length(参数)：返回字节数长度
mysql单句函数
qq_56299755的博客
07-26 114
#进阶4:常见函数 / 功能:类似于Java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名 好处：1.隐藏了实现细节 2.提高代码的重用性 调用：select 函数名(实参列表) [from 库]; 特点： 1.叫什么（函数名） 2.干什么（函数功能） 分类： 1.单行函数 如：concat，length，ifnull等 2.分组函数 功能：做统计使用，又称为统计函数，聚合函数，组函数 / #一.字符函数 #1.length #结果的是字节数 SELECT LENGTH('john'); SELECT
关于在Mysql数据库中查询某表中第二大的数据的记录的方法及实现的sql语句
热门推荐
努力前进
10-29 1万+
在学习过程中，遇到一个问题，查询最大的数据时十分容易，使用MAX函数语句即可，但是如何查询第二大的记录呢， 抱着这个问题我开始查找资料 首先建表语句，我建立了一个employee的表，属性有id，name和age，其中id为int 自增 主键，name为char(5)，age为int型，创建及插入数据语句略去 首先我看到了以下方法 方法一： select top 1 height fro...
MySQL数据库
weixin_34362875的博客
05-14 108
MySQL概述 　　1、什么是数据库 ：存储数据的仓库 　　2、使用公司 ：金融、游戏、购物、论坛 ... 　　3、提供数据库服务的软件 　　　　MySQL、Oracle、MongoDB、Mariadb、SQL_Server、Redis 　　4、生产环境中,如何选择 　　　　1、是否开源 　　　　　　开源 ：MySQL、MariaDB、M...
在JPA项目启动时新增MySQL字段
VioletKiss
06-18 949
我用的方案1，如果你也是这种项目，那么我也推荐你用方案1，简单粗暴又方便；方案2是问了chatgpt得出来的答案，感觉不错，但是报错了又不想改；不建议使用 schema.sql 和 data.sql 对数据库结构及数据进行初始化，因为在每一次项目启动，这两个文件都必须执行一次，大大减慢了项目的启动速度，替代方案可以是使用 flyway 进行数据库版本控制。
MySQL笔记——事务
最新发布
君莫笑lucky的博客
06-21 197
学习黑马MySQL课程，记录笔记，用于复习。
使用消息队列（MQ）实现MySQL持久化存储与MySQL server has gone away问题解决
qq_33665793的博客
06-16 575
而其中一个常见的需求是将消息队列中的数据持久化到数据库中，以确保数据的安全性和可靠性。通过使用消息队列（MQ）将消息持久化存储到MySQL数据库，我们可以提高系统的可靠性和可维护性。它提供了可靠的事务支持和高性能的查询功能。2. 数据可靠性：通过将消息队列中的数据存储到MySQL数据库中，可以确保消息被可靠地处理和传递给消费者。4. 消息消费：编写一个消费者程序，从MQ的队列中获取消息，并将消息存储到MySQL数据库中。5. 消息确认：在成功将消息存储到数据库后，向MQ发送消息确认，告知MQ消息已被处理。
mysql -e 自定义函数
07-28
MySQL提供了创建和使用自定义函数的功能。您可以使用`CREATE FUNCTION`语句来定义自定义函数，然后使用`SELECT`或其他查询语句来调用它们。 以下是一个示例，展示如何在MySQL中创建一个简单的自定义函数： ```sql ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值