MySQL 内置函数详解 + 复合查询 + 内外连接（内 / 左 / 右外连接）实战----《Hello MySQL!》(5)

前言

MySQL 内置函数是数据处理的 “快捷工具”，复合查询（含内外连接）是复杂业务的 “核心解法”，二者都是 MySQL 从入门到进阶的必备知识 —— 尤其是多表关联场景下，内外连接的选择直接决定查询结果的准确性。

本文不讲冗余理论，只聚焦实战：先覆盖日期、字符串、数学、常用工具等 MySQL 内置函数，再详解多表查询、自连接、子查询、union/union all 合并查询、内外连接（内 / 左 / 右外连接）等复合查询核心技能，清晰区分易混概念（如内连接 vs 外连接）、标注踩坑点，附带经典面试题与作业解析，帮你快速掌握 MySQL 进阶查询能力，开发 & 面试两不误。

MySQL内置函数

函数一般可以用在select,having,where语句中

但是如果是聚合函数的话，不能用在where语句中

各函数之间是可以嵌套使用的

日期函数

引申: 日期一般指的是年月日 时间一般指的是时分秒

MySQL里面日期的标准表示:'YYYY-MM-DD'

MySQL里面时间的标准表示:'HH:MM:SS'

用法举例:
select current_timestamp;
select now();
这俩的结果是一模一样的

select date_add('2025-12-21', interval 30 day);返回值是在这个日期上加30天之后的日期

select date_add(now(),interval 10 second);

select datediff('2025-12-11','2005-11-14');
返回值是前面的日期减后面的日期

引申:虽然eg:current_time()只显示时间，但是可以通过隐式类型转换成日期＋时间

–但是不建议，有些地方好像会日期不准确

字符串函数

charset(str) 返回字符串的字符集编码

concat(string2[,...]) 连接字符串
instr(string,substring) 返回substring在string中出现的位置，没有就返回0

ucase(string2) 转换成大写

lcase(string2) 转换成小写

left(string2,length) 从string2中的左边起取length个字符

length(string) string的长度,返回的是字节数!不是字符数

replace(str,search_str,replace_str) 从str中用replace_str替换search_str

strcmp(string1,string2) 逐字符比较两字符串大小

substring(str,position[,length]) 从str的position开始，取length个字符,没加length那就是把后面的截完

ltrim(string):去除前空格 rtrim(string)去除后空格 trim(string)去除前后空格

注意:MySQL里面字符函数下标是从1开始的

举例:
  select charset('abcd');

select concat(name,'的数学成绩是',math,'分') '分数' from student;

select instr('adcd1234acbd','1234');//返回的是5

select substring(ename,2,2) from emp;从把下标2，3位置截出来

select replace(ename,'a','A') from emp;把所有'a'都换成'A'--'alice'这种的也会换

select ename,concat(lcase (substring(ename,1,1),'a') ) from emp;

数学函数

abs:绝对值函数

bin(decimal_number):十进制转换成二进制

hex(decimal_number):十进制转换成十六进制

conv(number,from_base,to_base):进制转换

ceiling(number):向上取整

floor(number):向下取整

format(number,decimal_places):格式化，保留decimal_places位小数位数，四舍五入去保留–eg:传0，表示保留0位小数

rand():返回范围在[0,1)的随机浮点数 --会返回eg:0.123这种

mod(number,denominator):取模

select format(1.1111111,2);返回值就是1.11

select conv(255, 10, 16); 10进制转16进制

select conv('1010', 2, 10); 2进制转10进制

注意:非10进制的数在MySQL里最好是当作字符串来传

MySQL里面负数取模的规则跟C++一样

都是结果的符号跟原来被除数的符号一样，

select mod(-10,3);结果就是-1   先10%3的结果带上-

其他函数

user() 查询当前用户

database() 查询当前使用的数据库

ifnull(val1,val2) 如果val1为null就返回val2,不然就返回val1

md5(str) 会对str进行md5摘要，得到一个32位字符串–算是一种加密

password(...) 会对…进行加密

举例:
select password(1234);  select password('acsd');

MySQL复合查询

多表查询

语法:就是在select的from后面放多个表

这样的话，显示出来是内容会是多个表笛卡尔积之后的结果

(就是eg:第一张表的所有数据跟第二张表的所有数据结合一遍…)

可以用where去筛选出符合条件的组合

eg:select * from emp,dept where emp.deptno=dept.deptno

注意:之后显示出来的deptno就只有一个了

在select其他地方，如果列名在多个表里有的话，需要指定表名才行eg: emp.salary

解决多表问题的本质:就是把多表转换成单表

引申:任何时刻，查询出来的临时结构，本质在逻辑上也是表结构

(MySQL下一切皆表)

自连接

就是同一张表搞两张来做笛卡尔积

eg:select * from emp e1,emp e2;

注意:自连接时表必须取别名

自连接的应用场景:比如查跟某个员工有关联的员工这种(比如:这个领导的下属有哪些)

子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

子查询的结果就理解成临时表就行了

子查询通常用在where那，其他地方的话也有

单行子查询:就是子查询返回的只有一行数据,但是子查询只返回一列

多行子查询:就是子查询返回的有多行数据,但是子查询只返回一列

多列子查询:就是子查询返回的结果有多列

多列子查询举例:
select * from emp where (deptno,job)=(select deptno,job from emp where ename = '张三');
注意:(deptno,job)会按返回的结果的顺序匹配，不能随意乱写顺序!

引申:

还有三个关键字，只能用在where和having后面 in all any

其中all和any只能用在子查询里面(并且是子查询的主查询里面)

in:里面任意一个值满足就行了

all:里面的要全部满足才行

any:满足任意一个条件就行了(跟in的区别是any可以搭配eg:>这些)

举例:
select * from emp where job in (10,20,30);//满足其中一个就行了

select * from emp where sal > all(select sal from emp where deptno = 30);
显示工资高于部门30里面所有员工的员工(也就是大于部门30的最大值)

select * from emp where sal> any(select sal from emp where deptno = 30);
显示工资高于部门30里面随便一个员工的员工(也就是大于部门30的最小值)

还能在from里面用子查询

eg:
select * from EMP,(select max(sal) ms, deptno from EMP group by deptno) tmp
where EMP.deptno=tmp.deptno and EMP.sal=tmp.ms;
查询每个部门里面薪资最高的人的数据

合并查询

为了合并多个select的执行结果，可以使用集合操作符union,union all

union：该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行

eg:
select ename, sal, job from EMP where sal>2500 union
select ename, sal, job from EMP where job='主管';
这样会把工资高于2500或job是主管的人的数据给显示出来(数据不会重复显示)

union all:该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行

eg:
select ename, sal, job from EMP where sal>2500 union all
select ename, sal, job from EMP where job='主管';
这样会把工资高于2500或job是主管的人的数据给显示出来
(数据会重复显示--如果一个人两个条件都满足，就会显示出来两次)

引申: explain可以用来分析SQL的执行计划

在要分析的SQL语句前加explain:

eg:exlain select * from emp;

作业部分

某查询语句运行后返回的结果集为：
1班 72
2班 75
3班 NULL
则有可能的查询语句是以下：(BD)
A.SELECT class, AVG(score) FROM test WHERE class<4
B.SELECT class, AVG(score) FROM test WHERE class<4 GROUP BY class
C.SELECT class, AVG(score) FROM test WHERE class<4 GROUP BY ALL class
//all不能这样错，所以错误
D.SELECT class, AVG(score) FROM test GROUP BY class HAVING class<4

有订单表orders，包含字段用户信息userid，字段产品信息productid
查询至少被订购过两回的productid(D)

A.select productid from orders where count（productid）>1
B.select productid from orders where max（productid）>1
C.select productid from orders where having count（productid）>1 group by productid
D.select productid from orders group by productid having count（productid）>1

A是因为聚合函数不能在where使用，直接报错

编写一个SQL查询，获取Employee表中第二高的薪水（Salary）【陌陌科技2020届校招笔试题】

select
max( Salary ) as SecondHighestSalary 
from Employee 
where Salary < ( select max( Salary ) from Employee );

select那里用聚合函数是在where筛选之后用的，所以那里的max其实取得是总体的第二大

MySQL内外连接

内连接

内连接实际上就是利用where子句对两种表形成的笛卡儿积进行筛选

标准语法:select 字段 from 表1 [inner] join 表2 on 连接条件 [and 其他条件];(还能再后面继续用where)

前面那种from ... where 条件 [and 条件];也算内连接，只是写法不是很规范而已

加不加inner都一样

eg:
select * from emp join dept on emp.deptno = dept.deptno where ename = '张三';
select * from emp join dept on emp.deptno = dept.deptno and ename = '张三';
这俩表示的意思是一样的

外连接

外连接分为左外连接和右外连接

左外连接

以左表为核心，左表完全显示，右表没有与之对应的就补null

右表有，左表没有的那种右表也不显示

语法:select 字段名 from 表名1(左表) left join 表名2(右表) on 连接条件

右外连接

以右表为核心，右表完全显示，左表没有与之对应的就补null

左表有，右表没有的那种左表也不显示

语法:select 字段名 from 表名1(左表) right join 表名2(右表) on 连接条件

作业部分

Mysql查询时，只有满足联接条件的记录才包含在查询结果，这种联接是(C)
A.左联接
B.右联接
C.内联接
D.全联接

eg:左连接是左表全保留，右表满足条件的保留,不满足的填null