MySQL基础08—DQL2
陸風睿
|
|
|
67
|
0
|
文档

2543 字
|
19 分钟

概述

本章，您将学习 MySQL 中有关 DQL 的知识。

在 MySQL 基础篇的整个知识架构中，DQL 的内容最多也最常使用，学习时应该重点掌握与训练。

DQL 主要包含以下内容：

  • 条件查询
  • 排序查询
  • 函数
  • 连接查询
  • 嵌套查询
  • 分页查询
  • 联合查询

受限于篇幅，本篇文档只说明函数（分组查询由于内容太少，于是把分组查询放到了这篇函数文档中）。

函数

函数（有时称为方法）：，简单来说，为了要实现某个功能，用代码块封装成对外暴露名称且可以被调用的一个东西，就称为函数，主要作用是为了方便代码的复用性。对于大多数普通人来说，最早了解到的函数，可能是在 Excel 表格中。

MySQL 中的函数的大致可以划分为：

  • 数学函数
  • 聚合函数
  • 字符函数
  • 日期和时间函数
  • 数据类型转换函数
  • 流程控制函数
  • 格式化函数
  • 系统信息函数

数学函数

  • abs(x) 函数

    返回一个数的绝对值，正数绝对值是它本身；负数的绝对值是它的相反数；0 的绝对值是 0。示例：

    select abs(-100);

  • bin(x) 函数

    返回该数字的二进制表示。示例：

    select bin(100);

  • round(x,y) 函数

    对传入的参数返回一个四舍五入的值，x 指定值，y 指定四舍五入后保留小数点多少位数，默认为 0 。

    # 结果为1.4
select round(1.35,1);

# 结果为它1.35
select round(1.35,2);

# 返回本身6.247      
select round(6.24681,3);

# 返回结果 10
select round(6.66,-1);

# 返回结果 0
select round(-1.11,-1);

# 返回结果 -120
select round(-116.11,-1);

  • ceil(x) 函数

    返回一个向上取整（即大于或者等于 x 的最小整数）且类型为 bigint 的值。示例：

    # 返回结果 3
select ceil(3);

# 返回结果4
select ceil(3.14);

# 返回结果 -3
select ceil(-3.69);

  • floor(x) 函数

    返回一个向下取整（即小于或者等于 x 的最小整数）且类型为 bigint 的值。示例：

    # 返回结果3
select floor(3);

# 返回结果 3
select floor(3.25);

# 返回结果 -4
select floor(-3.47);

  • truncate(x,y) 函数

    截断 x 。当 y 大于0，对 x 的小数位进行操作；当 y=0，对 x 的小数位去除，保留整数部分；当 y 小于0，将 x 的小数位去除，且将整数部分的 y 位用 0 代替。示例：

    # 返回结果3.141
select truncate(3.1415,3);

# 返回结果100
select truncate(123.1415,-2);

  • mod(x,y) 函数

    返回 x 除以 y 的余数，余数的正负由被除数的正负决定。示例：

    # 结果为2
select mod(5,3);

# 结果为 1.14
select mod(3.14,2);

# 返回结果 -0.14
select mod(-3.14,-3);

# 返回结果 0.14
select mod(3.14,-3);

字符函数

  • length(str) 函数

    返回字符的字节长度，注意！是字节长度。在 utf8 编码格式中，一个字母或数字为 1 个字节长度，一个中文汉字为 3 个字节长度。示例：

    # 返回 6
select length("tianci");

# 返回 3
select length("李");

  • concat(str1,str2…) 函数

    将多个字符串合并为一个字符串，拼接时可以使用一些拼接符。示例：

    select concat("tianci","_","李");

  • upper(str) 函数

    将英语字符串变更为大写，如果已经是大写，则不发生变更。示例：

    # 返回结果TIANCI
select upper("TianCi");

  • lower(str) 函数

    将英语字符串变更为小写，如果已经是小写，则不发生变更。

  • substr(str,x,n) 函数

    从字符串的 x 索引位置截取长度为 n 的子字符串。注意！索引是从 1 开始计数的。这里的长度指的是正常的字符长度，而不是字节长度。如果不键入长度参数，则表示所有。示例：

    # 返回 lit
select substr("litianci",1,3);

# 返回 "常见函数"
select substr("mysql的常见函数",7,4);

# # 返回 "mysql的常见函数"
select substr("mysql的常见函数",1);

  • instr(str,subtr) 函数

    返回的结果为子字符串第一次出现的索引位置。如果字符串有多个匹配，则只计算第一次出现的位置；如果没有匹配到字符串，返回 0 。示例：

    # 返回 3
select instr("常见函数常见函数","函数");

  • trim(str) 函数

    默认去掉字符串 str 的开始与结尾处的空格，当然你可以指定开始与结尾应该去除什么。示例：

    select trim("   name  ");

# 返回结果 name123tmp
select  trim(123 from "123name123tmp123");

  • lpad(str1,len,str2) 函数

    在字符串 str1 的开始位置处填充字符串 str2，使字符串长度达到 len。如果指定的长度小于 str1 的字符长度，则会截断而不是填充。示例：

    # 返回结果+++++++pad
select lpad("pad",10,"+");

# 返回结果 pa
select lpad("pad",2,"++");

  • rpad(str1,len,str2) 函数

    在字符串 str1 的结束位置处填充字符串 str2，使字符串长度达到 len。如果指定的长度小于 str1 的字符长度，则会截断而不是填充。示例：

    # 返回结果 pad+++++++
select rpad("pad",10,"+");

# 返回结果 p
select rpad("pad",1,"++");

  • replace(str,str1,str2) 函数

    将字符串 str 中的 str1 替换为 str2，匹配多个字符也会被替换，而不是只有一个。示例：

    # 返回结果 abcxabcx
select replace("abcdabcd","d","x");

日期和时间函数

  • now() 函数

    返回当前系统的日期与时间。示例：

    # 返回 2025-08-06 11:15:25
select now();

  • curdate() 函数

    返回当前系统的日期。示例：

    # 返回 2025-08-06
select curdate();

  • curtime() 函数

    返回当前系统的时间。示例：

    # 返回 11:17:19
select curtime();

  • year(expr) 函数

    从合法的日期或者日期时间表达式中提取年。示例：

    # 返回 2025
select year(now());

  • month(expr) 函数

    从合法的日期或者日期时间表达式中提取月。示例：

    # 返回 8 
select month(now());

# 返回英语名称的月份 August
select monthname(now());

  • day(expr) 函数

    从合法的日期或者日期时间表达式中提取日。示例：

    # 返回 6
select day(now());

  • date(expr) 函数

    从合法的日期或者日期时间表达式中提取日期。示例：

    # 返回 2022-03-17
select date("2022-03-17 09:59:00");

  • hour(expr) 函数

    从合法的日期或者日期时间表达式中提取小时。示例：

    # 返回 11 
select hour(now());

  • minute(expr) 函数

    从合法的日期或者日期时间表达式中提取分钟。示例：

    select minute(now());

  • second(expr) 函数

    从合法的日期或者日期时间表达式中提取秒。示例：

    select second(now());

  • time(expr) 函数

    从合法的日期或者日期时间表达式中提取时间。示例：

    # 返回 11:26:25
select time(now());

  • str_to_date(str,format) 函数

    将合法格式的字符转换为指定格式的日期或者时间。就相当于你要告诉函数，那一部分是年、是月、是日。示例：

    select str_to_date("1994/03/16","%Y/%m/%d");

select str_to_date(curtime(),"%H:%i:%s");

    时间格式符如下：

    时间格式符 说明
    %Y 四位数的年份
    %y 两位数的年份
    %m 月份
    %d 日
    %H 小时（24小时制）
    %h 小时（12小时制）
    %i 分钟
    %s 秒

系统信息函数

常见有：

  • select version(); – 返回数据库的版本信息
  • select connection_id(); – 返回连接数
  • select database(); – 返回当前的数据库名
  • select user(); – 返回当前的登录用户
  • show processlist; – 返回当前连接Mysql服务的用户
  • select md5("字符"); – 返回密文

流程控制函数

  • if(expr,v1,v2) 函数

    条件判断。当 expr 表达式为真时，返回 v1；当为假时，返回 v2。示例：

    # 返回 no
select if(0>1,"yes","no");

  • case 函数

    第一种用法：

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

    注意！ 当你不键入 else 后面的语法时，没有匹配到的则会以 null 进行显示。

    示例：

    select
 name,
 case Continent
    when "Asia" then "亚洲"
    when "north america" then "北美"
    else Continent
 end as temp
from
 country;

    输出为：

    name                   temp
Aruba                  北美
Afghanistan            亚洲
Angola                 Africa
Anguilla               北美
Albania                Europe
Andorra                Europe
Netherlands Antilles   北美
...

    第二种用法：

    case
when  条件1  then 要显示的值1或者语句1
when  条件2  then 要显示的值2或者语句2
...
else  要显示的值n或者语句n
end

    示例：

    select
 name,
 case
    when Continent = "Asia" then "亚洲"
    when Continent in ("north america", "Africa") then "A_N"
    else Continent
 end as temp
from
 country;

聚合函数

聚合，顾名思义，即做统计的函数。

  • sum() 函数

    求和函数，仅针对数值类型。忽略 null 值。示例：

    # 求亚洲国家 GNPold 之和
## 这两条 SQL 等价，都输出结果 8251969.00

select sum(GNPOld)  from country where GNPOld is not null and Continent="Asia";
select sum(GNPOld) from country where Continent="Asia";

  • avg() 函数

    平均值函数，仅针对数值类型。忽略 null 值。示例：

    # 求亚洲国家GNP的平均值，输出结果 150105.725490
select avg(GNP)  from country  where  Continent="Asia";

  • max() 函数

    求最大值函数，可针对数值类型，也可针对字符类型。当为字符类型时，返回最大值（即 a~z 中，a 最小，z 最大，若第一个字符相同，则比较第二个字符，以此类推，例如 abcd 比 abc 大）。忽略 null 值。示例：

    # 返回 Zimbabwe
select max(name)  from country;

  • min() 函数

    求最小值函数，可针对数值类型，也可针对字符类型。忽略 null 值。示例：

    # 返回结果 Afghanistan
select min(name)  from country;

  • count() 函数

    用来计算个数的函数。忽略 null 值。示例：

    # 国家个数，返回 239
select count(name) from country;

聚合函数还支持去重后的求和、平均值、最大值、最小值以及计数。

例如求全世界非重复平均年龄的和，通过 select * from country where LifeExpectancy is not null order by LifeExpectancy; 查询可知，LifeExpectancy 字段有重复的值。

# 查询所有非 null 的和，结果为14759.9
select sum(LifeExpectancy)  from country;

# 去重后的平均年龄，且做了排序（升序）
select distinct LifeExpectancy  from country where  LifeExpectancy is not null order by LifeExpectancy;

# 去重后的求和，返回结果 10407.4
## 下面两条 SQL 等价
select sum(LifeExpectancy)   from (select distinct LifeExpectancy  from country where  LifeExpectancy is not null order by LifeExpectancy)  as `tmptable`;
select sum(distinct LifeExpectancy)  from  country;

分组查询

使用到的关键字为 group by 和 having。

查询所有国家的个数：

# 返回 239
select count(name) from country;

现在我需要求各个大州的国家个数，应该怎么书写 SQL 语句？

可以使用分组查询。

select continent,count(name) from country group by continent;

输出为：

North America   37
Asia            51
Africa          58
Europe          46
South America   14
Oceania         28
Antarctica      5

查询每个大洲里的最大平均年龄，按照升序排列最大平均年龄：

select continent,max(LifeExpectancy) as `a` from country group by continent order by `a` asc;

这类 SQL 语句的执行顺序为：

  • 定位到库
  • 定位到具体的表
  • 执行 where 关键字后面的筛选条件
  • 执行分组
  • 执行查询列表
  • 排序

了解执行顺序在 DQL 中非常非常重要。肌肉记忆（即当你的眼睛扫过自然语言，头脑中就有了相应的 SQL 语句）不是一天两天能完成的，这也是为什么建议新手一定要大量练习的原因。

输出结果为：

Antarctica      NULL
South America   76.1
Africa          76.8
North America   79.4
Oceania         79.8
Asia            81.6
Europe          83.5

having 关键字相当于在分组的基础上添加条件筛选，比如：

# 筛选出平均年龄大于 70 的国家数据，且将这些国家数据按照州进行分组，分组之后筛选出国家个数大于 10 的州信息（按照国家个数升序）
select continent,count(name) as `a` from country where LifeExpectancy > 70 group by continent having count(name)> 10 order by count(name);

输出信息为：

Asia            22
North America   29
Europe          36
Avatar photo

关于 陸風睿

GNU/Linux 从业者、开源爱好者、技术钻研者，撰写文档既是兴趣也是工作内容之一。Q - "281957576"；WeChat - "jiulongxiaotianci"，Github - https://github.com/jimcat8
查看 陸風睿 所有文章 | 网站
版权声明:「自由转载-保持署名-非商业性使用-禁止演绎 3.0 国际」（CC BY-NC-ND 3.0）
用一杯咖啡支持我们，我们的每一篇[文档]都经过实际操作和精心打磨，而不是简单地从网上复制粘贴。期间投入了大量心血，只为能够真正帮助到您。
MySQL
暂无评论

发送评论 编辑评论 


				

			

						

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
							
													

					

				

			

			
			

				

					
				

			

				

											

							
							
						

																

							
							
						

																

							
							
						

										
					
											
						

	

		
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
（╯‵□′）╯︵┴─┴
￣﹃￣
(/ω＼)
∠( ᐛ 」∠)＿
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ｀)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ(￣∇￣o)
ヾ(´･ ･｀｡)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò｡)
Σ(っ °Д °;)っ
( ,,´･ω･)ﾉ"(´っω･｀｡)
╮(╯▽╰)╭
o(*////▽////*)q
＞﹏＜
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
	

	

		
颜文字
Emoji
小恐龙
花!
	


									

			

			
		

	






上一篇
下一篇 

                    

                    
			        推荐文章
		            

		            

							
MySQL基础07—DQL1

							
							

							
应届生面试避坑指南：木子的真实面试观察和建议

							
							

							
MySQL基础06—基础使用与数据导入

							
							

							
密码保护：开源知识库系统选型与 Outline 部署全解

							
							

							
密码保护：Draw.io 本地化部署与 GitLab 集成全攻略

							
							

							
MySQL基础05—客户端

							
							

							
密码保护：Rocky Linux 9 从入门到精通008 —  LDAP 账号管理（Keycloak 统一认证）

							
							

							
MySQL基础04 — 配置文件

							
							

							
MySQL基础03—排错与后续操作

							
							

							
MySQL基础02 — 安装与启动相关