当前位置：首页 > backend >正文

mysql复合查询mysql子查询

backend 2025/6/29 0:21:27

基础表结构创建

表结构包含主外键约束和字符集配置，确保数据完整性
部门表

CREATE TABLE `dept` (`deptno` int NOT NULL COMMENT '部门编号',`dname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '部门名称',`loc` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '部门属地',PRIMARY KEY (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

用户表（含部门外键）

CREATE TABLE `emp` (`empno` int NOT NULL COMMENT '编号',`ename` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '名称',`job` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '工作',`mgr` int DEFAULT NULL COMMENT '上司编号',`hiredate` date DEFAULT NULL COMMENT '入职时间',`sal` decimal(10,2) DEFAULT NULL COMMENT '薪资',`deptno` int DEFAULT NULL COMMENT '部门编号',PRIMARY KEY (`empno`),KEY `deptno` (`deptno`),CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

薪资等级表

CREATE TABLE `salgrade` (`grade` int NOT NULL COMMENT '等级',`losal` decimal(10,2) DEFAULT NULL COMMENT '最小薪资',`hisal` decimal(10,2) DEFAULT NULL COMMENT '最大薪资',PRIMARY KEY (`grade`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

给表补充数据

部门表

INSERT INTO dept VALUES 
(10,'ACCOUNTING','NEW YORK'),
(20,'RESEARCH','DALLAS'),
(30,'SALES','CHICAGO');

用户表

INSERT INTO emp VALUES
(7369,'张三1','develop',7499,'2025-05-06 16:16:53',8600,20),
(7499,'李四1','reception',0,'2025-05-06 16:16:53',8300,30),
(7521,'王先生1','develop',0,'2025-05-06 16:16:53',6000,30),
(7566,'李强1','develop',7499,'2025-05-06 16:16:53',8600,20),
(7698,'寇1','develop',7521,'2025-05-06 16:16:53',8600,30);

薪资表

INSERT INTO salgrade VALUES
(1,700,1200),
(2,1201,1400),
(3,1401,2000),
(4,2001,3000),
(5,3001,9999);

复合查询SQL演示

多表联合查询‌

通过薪资范围关联等级表，查询员工姓名、部门及薪资等级：

SELECT e.ename, d.dname, s.grade
FROM emp e
JOIN dept d ON e.deptno = d.deptno
JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;

在这里插入图片描述
查询员工名称是张三1员工的姓名、部门及薪资等级

SELECT e.ename, d.dname, s.grade
FROM emp e
JOIN dept d ON e.deptno = d.deptno
JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
where e.ename = '张三1';

在这里插入图片描述
查询名称结尾是1 员工的姓名、部门及薪资等级

SELECT e.ename, d.dname, s.grade
FROM emp e
JOIN dept d ON e.deptno = d.deptno
JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
where e.ename like '%1';

在这里插入图片描述

笛卡尔积（联表查询需要关注的概念）

概念

笛卡尔积是指两个集合中所有可能的有序对组合，在数据库中表现为两个表的每一行都与另一个表的每一行进行组合。数学表达式为：A × B = {(a,b) | a ∈ A ∧ b ∈ B}

语法

-- 1.显式语法：
SELECT * FROM emp CROSS JOIN dept;
-- 2.隐式语法：
SELECT * FROM emp , dept;

在这里插入图片描述
两种方式都会产生m×n行的结果集（m为表1行数，n为表2行数）

注意

风险：百万级表连接可能产生万亿级结果
优化方案：
添加WHERE条件限制结果集
使用子查询替代多表连接
建立合适的索引

‌子查询应用‌

关联子查询实现分组筛选，查询各部门薪资高于该部门平均工资的员工：

SELECT e.ename, e.sal, d.dname
FROM emp e
JOIN dept d ON e.deptno = d.deptno
WHERE e.sal > (SELECT AVG(sal) FROM emp WHERE deptno = e.deptno
);

在这里插入图片描述
多列子查询：查找同部门同岗位薪资更高的员工

SELECT a.ename, a.sal, a.job
FROM emp a
WHERE EXISTS (SELECT 1 FROM emp bWHERE a.deptno = b.deptno AND a.job = b.jobAND a.sal < b.sal
);

在这里插入图片描述

优化技巧

‌索引策略‌

-- 部门关联字段索引
CREATE INDEX idx_emp_deptno ON emp(deptno);
-- 薪资查询复合索引
CREATE INDEX idx_emp_sal_dept ON emp(sal, deptno);

优先为连接条件和筛选字段建索引

‌执行计划分析‌

使用EXPLAIN检查查询效率：

EXPLAIN SELECT ... FROM emp JOIN dept ...;

例如

EXPLAIN SELECT e.ename, d.dname, s.grade
FROM emp e
JOIN dept d ON e.deptno = d.deptno
JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;

在这里插入图片描述

重点关注type列避免ALL扫描

子查询复杂应用

‌EXISTS优化IN‌

查询有下属的管理者：

SELECT ename FROM emp e1
WHERE EXISTS (SELECT 1 FROM emp e2 WHERE e2.mgr = e1.empno
);

比IN更高效的关联查询

‌派生表实现复杂统计‌

各部门薪资等级分布统计：

SELECT d.dname, s.grade, COUNT(*) count
FROM dept d
JOIN emp e ON d.deptno = e.deptno
JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
GROUP BY d.dname, s.grade WITH ROLLUP;