基础表结构创建
表结构包含主外键约束和字符集配置，确保数据完整性
部门表
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;
多维度分组统计
建议通过EXPLAIN ANALYZE验证优化效果，避免超过3层嵌套子查询
)
