MySQL的DQL语言：4、常见函数

最新推荐文章于 2023-05-29 11:51:02 发布

卓汶

最新推荐文章于 2023-05-29 11:51:02 发布

阅读量181

点赞数

分类专栏： java 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_45804632/article/details/121863385

版权

java 同时被 2 个专栏收录

130 篇文章 0 订阅

订阅专栏

MySQL了解

18 篇文章 1 订阅

订阅专栏

常见函数

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

单行函数

字符函数：
	length、concat、substr、instr、trim、upper、lower、lpad、rpad、replace
数学函数：
	round、ceil、floor、truncate、mod
日期函数：
	now、curdate、curtime、year、month、day、hour、minute、second、str_to_date、date_format
其他函数：
	version、database、user
控制函数：
	if、case

一、字符函数

1、length：获取参数值的字节个数

select length('jhon');#4
select length('中文abc'); #7


#查看客户端使用的字符集
show variables like '%char%';
/*
mysql> show variables like '%char%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | gbk                                                     |
| character_set_connection | gbk                                                     |
| character_set_database   | gb2312                                                  |
| character_set_filesystem | binary                                                  |
| character_set_results    | gbk                                                     |
| character_set_server     | utf8mb4                                                 |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 8.0\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set, 1 warning (0.04 sec)
utf-8 一个字母占用一个字节，一个汉字占用3个字节
gbk 一个汉字占用32个字节
*/

在这里插入图片描述

2、concat ：拼接字符串

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

在这里插入图片描述

3、upper、lower

select upper('john');
select upper('ABcdeFg');

在这里插入图片描述 ##### 示例：将姓变大写，名变小写，然后拼接

select
	concat(upper(last_name),' ',lower(first_name))
from
	employees;

# 函数可以嵌套使用

在这里插入图片描述

4、substr、substring : 截取字符串

#截取从指定索引处后面所有字符 substr(StringName,index)
select substr('abcdefghjiklmn',6) out_put;
#截取从指定索引处指定长度的字符 substr(StringName,StartIndex,EndIndex)
select substr('abcdefghjiklmn',1,5) out_put;
select substr('abcdefghjiklmn',4,3) out_put;#截取从4开始后面的三个字符
#【注】：sql语言中，索引从1开始

案例：姓名中首字符大写，其他字符小写，然后用 “_” 拼接

select
	concat(upper(substr(last_name,1,1)),'_',lower(substr(last_name,2)))
from
	employees;

在这里插入图片描述

5、instr：返回字串第一次出现的索引，如果找不到，则返回0

select instr('abcdefghijklmn','cdef') as out_put;
#返回'cdef'在'abcdefghijklmn'中的起始索引

6、trim：去除前后的指定字符，若没有指定，默认去除前后的空格

# trim(StringName)：去除 StringName 两边的空格的
select trim('   abc  def   ') as out_put;

# trim(str1 from StringName)：去除 StringName 两边的空格的 str
select trim('a' from 'aaaaaabaaaacaaaaa') as out_put;

在这里插入图片描述

7、LPAD(lpad) 、RPAD(rpad)：用指定的字符填充指定的长度，若长度超出，则截去相应长度。

#左填充
select LPAD('abcd',10,'*') as out_put;

#右填充
select RPAD('abcd',10,'*') as out_put;

select rpad('123',10,'abc') as out_put;

在这里插入图片描述

9、replace 替换

select replace('123456,123456','456','abc') as out_put;

在这里插入图片描述

二、数学函数

round ：四舍五入

select round(1.65);
select round(-1.45);
select round(1.5672,2);#保留小数点后两位

在这里插入图片描述

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

select ceil(1.52);
select ceil(1.25);
select ceil(1.02);

在这里插入图片描述

floor ：向下取整，返回 <= 该参数的最大整数

select floor(-9.99);

在这里插入图片描述

truncate 截断

select truncate(1.65,1); # 小数点后保留1位

在这里插入图片描述

mod 取余

# mod(a,b) : 的值就是 a-a/b*b

select mod(10,3);
select 10%3;

在这里插入图片描述

三、日期函数

now 返回当前日期+时间

select now();

在这里插入图片描述

curdate 返回当前系统日期，不包含时间

select curdate();

在这里插入图片描述

curtime 返回当前的时间，不包含日期

select curtime();

在这里插入图片描述

可以获取指定的部分：年、月、日、小时、分钟、秒

获取年

select year(now()) 年;
select year('2021.12.11') 年;
select year('2021-12-11') 年;
select year(hiredate) 年 from employees;

获取月

select month(now()) 月;
select monthname(now()) 月; # 英文显示

获取日

select day(now()) 日;
select dayname(now()) 日; # 英文显示 星期

获取小时 hour、获取分钟 minute 、获取秒 second

select hour(now()) 时;
select minute(now()) 分;
select second(now()) 秒;

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

select str_to_date('2021-1-1','%Y-%m-%d'); # 2021-01-01

在这里插入图片描述

查询入职日期为1992-4-3的员工信息

select * from employees where hiredate = '1992-4-3'; #能查，但是格式固定，无法变化，一般不使用

select * from employees where hiredate = str_to_date('1992-4-3','%Y-%m-%d');

date_format : 将日期转换成字符串

select date_format(now(),'%y年 %m月 %d日') as out_put;

查询有奖金的员工名和入职日期（XX月/XX日/XX年）

select 
	last_name,date_format(hiredate,'%m月 / %d日 /%Y年') 
from 
	employees 
where 
	commission_pct is not null;

四、其他函数

select version(); # 查询数据库版本
select database(); #查询当前使用的数据库
select user();	#查询用户

五、流程控制函数

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

# if(表达式 , 如果为true执行 , 如果为false执行); # 类似于java的三元运算符
select if(10>5,'大','小');

select 
	last_name,commission_pct,if(commission_pct is null,'没奖金','有奖金') as 备注 
from 
	employees;

2、case 函数

case函数的使用一：switch case 的效果

java中:
switch(变量或表达式){
	case 常量1 ： 语句1;break;
	......
	default: 语句n ;break;
}

mysql中:(case 可以当表达式，也可以从当语句)
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
......
else 要显示的值n或语句n
end

实例：查询员工的工资，要求如下

部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=40，显示的工资为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;

在这里插入图片描述

case函数的使用二：类似于java中的多重if

java中：
if(条件1){
	语句1;
}else if(条件2){
	语句2
}
....
else{
	语句n
}

mysql中：
case
when 条件1 then 要显示的值1 或语句1;
when 条件2 then 要显示的值2 或语句2;
......
else 要显示的值n 或语句n;
end

案例：查询员工的工资的情况

如果工资 > 20000 , 显示 A级别
如果工资 > 15000 , 显示 B级别
如果工资 > 10000 , 显示 C级别
否则显示 D级别

select 
	last_name,salary,
	case
	when salary>20000 then 'A级别'
	when salary>15000 then 'B级别'
	when salary>10000 then 'C级别'
	else 'D级别' 
	end as '工资等级'
from 
	employees;

单行函数测试

显示系统时间(注：日期+时间)

select now();

查询员工号，姓名，工资，以及工资提高百分之 20%后的结果（new salary）

select employee_id,last_name,salary,salary*(1+20%) as "new salary" from employees;

将员工的姓名按首字母排序，并写出姓名的长度（length）

select 
	last_name,length(last_name) as length,substr(last_name,1,1) as '首字母' 
from 
	employees 
order by 首字母 asc;

在这里插入图片描述

select 
	concat(last_name,' earns ',salary,' monthly bu wants ',salary*3) 
from 
	employees;

在这里插入图片描述

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'
	end as Grade
from
	employees
where
	job_id='AD_PRES';

分组函数

功能：用作统计使用，又称统计函数、聚合函数、组函数

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

特点：
1、sum、avg 一般用于处理数值型的数据
	max、min、count可以处理任何类型
2、以上分组函数都忽略null
3、可以和 distinct 搭配使用
4、count 函数的单独介绍
	一般使用count(*) 统计行数
5、和分组函数查询的字段要求是 group by 后的字段

简单使用

#简单使用
select sum(salary) from employees; #统计
select avg(salary) from employees;
select min(salary) from employees;
select max(salary) from employees;
select count(salary) from employees;

#组合使用
select
	sum(salary) 和,avg(salary) 平均,max(salary) 最高, min(salary) 最低,count(salary) 个数
from 
	employees;

#搭配单行函数使用
select
	sum(salary) 和,round(avg(salary),2) 平均,max(salary) 最高, min(salary) 最低,count(salary) 个数
from 
	employees;

特点

1、参数支持哪些类型

sum 与 avg 默认只能放置数值型的字段
select sum(last_name),avg(last_name) from employees;    #这是不可以的，没有意义

max 与 min 可以放置的类型是可以排序的（order by）
select max(last_name),min(last_name),max(hiredate),min(hiredate) from employees;

count 可以放置任何类型的，但count只计算null

2、是否忽略null值

select 
	sum(commission_pct),avg(commission_pct),sum(commission_pct)/35,sum(commission_pct)/107,count(commission_pct) ,min(commission_pct),max(commission_pct)
from 
	employees;
可以得出：以上分组函数都忽略null值

3、可以和 distinct 搭配实现去重运算

select sum(distinct salary),sum(salary) from employees;

select count(distinct department_id) from employees;

4、count函数详细介绍

select count(salary) from employees;#统计 salary 字段非空行数

select count(*) from employees;#统计表中的总行数

select count(1) from employees; #统计1的个数，即在原本的表上添加一列值为1，并统计个数，一般不使用

效率：
MYISUM 存储引擎下 ，count(*) 的效率最高
INNDB 存储引擎下，count(*) 与 count(1)效率差不多，但比 count(字段) 高一些

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

select avg(salary),employee_id from employees; # employee_id 无意义

分组函数测试

查询公司员工工资的最大值，最小值，平均值，总和

select
	max(salary),min(salary),round(avg(salary),2),sum(salary)
from
	employees;

查询员工表中的最大入职时间和最小入职时间的相差天数（DIFFRENCE）

max(hiredate) min(hiredate)
datediff(date1,date2) :使用 date1 与 date2 相减，如计算活了几天
select datediff('2021.12.12','2000.1.18');

select datediff(max(hiredate),min(hiredate)) DIFFRENCE from employees;

查询部门编号为 90 的员工个数

select count(*) from employees where department_id=90;

卓汶

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录

MySQL的DQL语言：4、常见函数

常见函数

单行函数

一、字符函数

1、length：获取参数值的字节个数

2、concat ：拼接字符串

3、upper、lower

4、substr、substring : 截取字符串

案例：姓名中首字符大写，其他字符小写，然后用 “_” 拼接

5、instr：返回字串第一次出现的索引，如果找不到，则返回0

6、trim：去除前后的指定字符，若没有指定，默认去除前后的空格

7、LPAD(lpad) 、RPAD(rpad)：用指定的字符填充指定的长度，若长度超出，则截去相应长度。

9、replace 替换

二、数学函数

round ：四舍五入

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

floor ：向下取整， 返回 <= 该参数的最大整数

truncate 截断

mod 取余

三、日期函数

now 返回当前日期+时间

curdate 返回当前系统日期，不包含时间

curtime 返回当前的时间，不包含日期

可以获取指定的部分：年、月、日、小时、分钟、秒

获取年

获取月

获取日

获取小时 hour、获取分钟 minute 、获取秒 second

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

查询入职日期为1992-4-3的员工信息

date_format : 将日期转换成字符串

查询有奖金的员工名和入职日期（XX月/XX日/XX年）

四、其他函数

五、流程控制函数

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

2、case 函数

case函数的使用一：switch case 的效果

实例：查询员工的工资，要求如下

case函数的使用二：类似于java中的多重if

案例：查询员工的工资的情况

单行函数测试

分组函数

简单使用

特点

1、参数支持哪些类型

2、是否忽略null值

3、可以和 distinct 搭配实现去重运算

4、count函数详细介绍

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

分组函数测试

floor ：向下取整，返回 <= 该参数的最大整数