跳到主要内容

多表查询练习

准备数据​

-- 创建库
CREATE DATABASE IF NOT EXISTS test_multidql;
USE test_multidql;
DROP TABLE IF EXISTS `t_department`;

CREATE TABLE `t_department` (
  `did` INT NOT NULL AUTO_INCREMENT COMMENT '部门编号',
  `dname` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '部门名称',
  `description` VARCHAR(200) DEFAULT NULL COMMENT '部门简介',
  PRIMARY KEY (`did`),
  UNIQUE KEY `dname` (`dname`)
);

/*Data for the table `t_department` */
INSERT  INTO `t_department`(`did`,`dname`,`description`) 
VALUES (1,'研发部','负责研发工作'),
(2,'人事部','负责人事管理工作'),
(3,'市场部','负责市场推广工作'),
(4,'财务部','负责财务管理工作'),
(5,'后勤部','负责后勤保障工作'),
(6,'测试部','负责测试工作');


/*Table structure for table `t_job` */

DROP TABLE IF EXISTS `t_job`;

CREATE TABLE `t_job` (
  `jid` INT NOT NULL AUTO_INCREMENT COMMENT '职位编号',
  `jname` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '职位名称',
  `description` VARCHAR(200) DEFAULT NULL COMMENT '职位简介',
  PRIMARY KEY (`jid`),
  UNIQUE KEY `jname` (`jname`)
);


/*Data for the table `t_job` */
INSERT  INTO `t_job`(`jid`,`jname`,`description`) 
VALUES (1,'技术总监','负责技术指导工作'),
(2,'项目经理','负责项目管理工作'),
(3,'程序员','负责开发工作'),
(4,'测试员','负责测试工作'),
(5,'人事主管','负责人事管理管理'),
(6,'人事专员','负责人事招聘工作'),
(7,'运营主管','负责市场运营管理工作'),
(8,'市场员','负责市场推广工作'),
(9,'财务主管','负责财务工作'),
(10,'出纳','负责出纳工作'),
(11,'后勤主管','负责后勤管理工作'),
(12,'网络管理员','负责网络管理');

/*Table structure for table `t_employee` */

DROP TABLE IF EXISTS `t_employee`;

CREATE TABLE `t_employee` (
  `eid` INT PRIMARY KEY  AUTO_INCREMENT COMMENT '员工编号',
  `ename` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '员工姓名',
  `salary` DOUBLE NOT NULL COMMENT '薪资',
  `commission_pct` DECIMAL(3,2) DEFAULT NULL COMMENT '奖金比例',
  `birthday` DATE NOT NULL COMMENT '出生日期',
  `gender` ENUM('男','女') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '男' COMMENT '性别',
  `tel` CHAR(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '手机号码',
  `email` VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '邮箱',
  `address` VARCHAR(150) DEFAULT NULL COMMENT '地址',
  `work_place` SET('北京','深圳','上海','武汉') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '北京' COMMENT '工作地点',
  `hiredate` DATE NOT NULL COMMENT '入职日期',
  `job_id` INT DEFAULT NULL COMMENT '职位编号',
  `mid` INT DEFAULT NULL COMMENT '领导编号',
  `did` INT DEFAULT NULL COMMENT '部门编号'
);

/*Data for the table `t_employee` */

INSERT  INTO `t_employee`(`eid`,`ename`,`salary`,`commission_pct`,`birthday`,`gender`,`tel`,`email`,`address`,`work_place`,`hiredate`,`job_id`,`mid`,`did`) 
VALUES (1,'孙洪亮',28000,'0.65','1980-10-08','男','13789098765','shl@example.com','白庙村西街','北京,深圳','2011-07-28',1,1,1),
(2,'何进',7001,'0.10','1984-08-03','男','13456732145','hj@example.com','半截塔存','深圳,上海','2015-07-03',2,1,1),
(3,'邓超远',8000,NULL,'1985-04-09','男','18678973456','dcy666@example.com','宏福苑','北京,深圳,上海,武汉','2014-07-01',3,7,1),
(4,'黄熙萌',9456,NULL,'1986-09-07','女','13609876789','hxm@example.com','白庙村东街','深圳,上海,武汉','2015-08-08',8,22,3),
(5,'陈浩',8567,NULL,'1978-08-02','男','13409876545','ch888@example.com','回龙观','北京,深圳,上海','2015-01-01',3,7,1),
(6,'韩庚年',12000,NULL,'1985-04-03','男','18945678986','hgn@example.com','龙泽','深圳,上海','2015-02-02',3,2,1),
(7,'贾宝玉',15700,'0.24','1982-08-02','男','15490876789','jby@example.com','霍营','北京,武汉','2015-03-03',2,1,1),
(8,'李晨熙',9000,'0.40','1983-03-02','女','13587689098','lc@example.com','东三旗','深圳,上海,武汉','2015-01-06',4,1,1),
(9,'李易峰',7897,NULL,'1984-09-01','男','13467676789','lyf@example.com','西山旗','武汉','2015-04-01',3,7,1),
(10,'陆风',8789,NULL,'1989-04-02','男','13689876789','lf@example.com','天通苑一区','北京','2014-09-03',2,1,1),
(11,'黄冰茹',15678,NULL,'1983-05-07','女','13787876565','hbr@example.com','立水桥','深圳','2014-04-04',4,1,1),
(12,'孙红梅',9000,NULL,'1986-04-02','女','13576234554','shm@example.com','立城苑','上海','2014-02-08',3,7,1),
(13,'李冰冰',18760,NULL,'1987-04-09','女','13790909887','lbb@example.com','王府温馨公寓','北京','2015-06-07',3,2,1),
(14,'谢吉娜',18978,'0.25','1990-01-01','女','13234543245','xjn@example.com','园中园','上海,武汉','2015-09-05',5,14,2),
(15,'董吉祥',8978,NULL,'1987-05-05','男','13876544333','djx@example.com','小辛庄','北京,上海','2015-08-04',6,14,2),
(16,'彭超越',9878,NULL,'1988-03-06','男','18264578930','pcy@example.com','西二旗','深圳,武汉','2015-03-06',8,22,3),
(17,'李诗雨',9000,NULL,'1990-08-09','女','18567899098','lsy@example.com','清河','北京,深圳,武汉','2013-06-09',8,22,3),
(18,'舒淇格',16788,'0.10','1978-09-04','女','18654565634','sqg@example.com','名流花园','北京,深圳,武汉','2013-04-05',9,18,4),
(19,'周旭飞',7876,NULL,'1988-06-13','女','13589893434','sxf@example.com','小汤山','北京,深圳','2014-04-07',10,18,4),
(20,'章嘉怡',15099,'0.10','1989-12-11','女','15634238979','zjy@example.com','望都家园','北京','2015-08-04',11,20,5),
(21,'白露',9787,NULL,'1989-09-04','女','18909876789','bl@example.com','西湖新村','上海','2014-06-05',12,20,5),
(22,'刘烨',13099,'0.32','1990-11-09','男','18890980989','ly@example.com','多彩公寓','北京,上海','2016-08-09',7,22,3),
(23,'陈纲',13090,NULL,'1990-02-04','男','18712345632','cg@example.com','天通苑二区','深圳','2016-05-09',3,2,1),
(24,'吉日格勒',10289,NULL,'1990-04-01','男','17290876543','jrgl@163.com','北苑','北京','2017-02-06',12,20,5),
(25,'额日古那',9087,NULL,'1989-08-01','女','18709675645','ergn@example.com','望京','北京,上海','2017-09-01',3,2,1),
(26,'李红',5000,NULL,'1995-02-15','女','15985759663','lihong@example.com','冠雅苑','北京','2021-09-01',12,23,1),
(27,'周洲',8000,NULL,'1990-01-01','男','13574528569','zhouzhou@example.com','冠华苑','北京,深圳','2020-08-15',3,NULL,NULL);

一、合并结果集（UNION / UNION ALL）​

语法特点​

-- 去重合并
SELECT 列1, 列2 FROM 表1
UNION
SELECT 列1, 列2 FROM 表2;

-- 不去重合并
SELECT 列1, 列2 FROM 表1
UNION ALL
SELECT 列1, 列2 FROM 表2;

注意事项​

  • 多个结果集的列数和类型必须一一对应
  • 重复行判定：一行中所有数据都相同才视为重复
  • 主要用于数据汇总、垂直拼接

二、多表连接查询​

1. 内连接（INNER JOIN）​

标准语法：

SELECT 列 FROM 表1 别名 
[INNER] JOIN 表2 别名 ON 主键 = 外键;

传统语法：

SELECT 列 FROM 表1 别名, 表2 别名 
WHERE 主键 = 外键;

核心要点：

  • 必须存在主外键关系
  • 只返回两个表都匹配的记录
  • N张表需要N-1个连接条件

多表连接示例：

-- 3表连接（2个连接条件）
SELECT e.eid, e.ename, d.dname, j.jname 
FROM t_employee e
JOIN t_department d ON e.did = d.did
JOIN t_job j ON e.job_id = j.jid;

2. 外连接（OUTER JOIN）​

语法：

-- 左外连接
表1 LEFT [OUTER] JOIN 表2 ON 条件;

-- 右外连接  
表1 RIGHT [OUTER] JOIN 表2 ON 条件;

特点：

  • 左连接：左表为逻辑主表，显示全部记录
  • 右连接：右表为逻辑主表，显示全部记录
  • 推荐使用左连接，逻辑更清晰

多表外连接技巧：

-- 逻辑主表放在第一位，后续都用LEFT JOIN
SELECT e.eid, e.ename, d.dname, j.jname 
FROM t_employee e  -- 逻辑主表
LEFT JOIN t_department d ON e.did = d.did
LEFT JOIN t_job j ON e.job_id = j.jid;

3. 自然连接（NATURAL JOIN）​

语法：

-- 自然内连接
SELECT * FROM 表1 NATURAL JOIN 表2;

-- 自然外连接
SELECT * FROM 表1 NATURAL LEFT JOIN 表2;

注意事项：

  • 自动查找相同列名作为连接条件
  • 不够灵活，容易出错
  • 生产环境不推荐使用

4. 自连接（SELF JOIN）​

应用场景： 同一张表内数据关联（如员工-领导关系）

语法：

SELECT e1.员工信息, e2.领导信息 
FROM 员工表 e1 
LEFT JOIN 员工表 e2 ON e1.领导ID = e2.员工ID;

多级自连接示例：

-- 查询员工及其领导、领导的领导
SELECT e1.eid, e1.ename, e2.ename 领导, e3.ename 领导的领导
FROM t_employee e1 
LEFT JOIN t_employee e2 ON e1.mid = e2.eid
LEFT JOIN t_employee e3 ON e2.mid = e3.eid;

三、子查询（嵌套查询）​

1. 标量子查询（单行单列）​

应用： 条件判断、值替换

-- 查询研发部门员工
SELECT * FROM t_employee 
WHERE did = (SELECT did FROM t_department WHERE dname = '研发部');

-- 计算部门平均工资与公司平均工资的差值
SELECT did, AVG(salary), 
       AVG(salary) - (SELECT AVG(salary) FROM t_employee) AS 差值
FROM t_employee GROUP BY did;

2. 行子查询（单行多列）​

应用： 多列整体比较

-- 查询与某员工性别和部门都相同的员工
SELECT * FROM t_employee 
WHERE (gender, did) = (SELECT gender, did FROM t_employee WHERE ename = '白露');

3. 列子查询（多行单列）​

应用： 配合IN、ANY、ALL使用

-- IN 用法（相当于 = ANY）
SELECT * FROM t_employee 
WHERE did IN (SELECT did FROM t_employee WHERE ename IN ('白露','谢吉娜'));

-- ALL 用法（比所有都大/小）
SELECT * FROM t_employee 
WHERE salary > ALL(SELECT salary FROM t_employee WHERE ename IN ('白露','李诗雨'));

-- 等价写法（使用MAX）
SELECT * FROM t_employee 
WHERE salary > (SELECT MAX(salary) FROM t_employee WHERE ename IN ('白露','李诗雨'));

4. 表子查询（多行多列）​

应用： 作为临时表参与连接

-- 查询部门信息及平均工资
SELECT d.did, d.dname, temp.平均工资
FROM t_department d 
LEFT JOIN (SELECT did, AVG(salary) AS 平均工资 FROM t_employee GROUP BY did) temp
ON d.did = temp.did;

四、DML操作中的子查询​

UPDATE + 子查询​

-- 更新测试部员工薪资
UPDATE t_employee 
SET salary = salary * 1.5 
WHERE did = (SELECT did FROM t_department WHERE dname = '测试部');

-- 解决"同表更新"限制的技巧
UPDATE t_employee 
SET salary = (SELECT salary FROM (SELECT salary FROM t_employee WHERE ename = '孙红梅') AS temp)
WHERE ename = '李冰冰';

DELETE + 子查询​

-- 删除测试部员工
DELETE FROM t_employee 
WHERE did = (SELECT did FROM (SELECT did FROM t_department WHERE dname = '测试部') temp);

INSERT + 子查询​

-- 复制表结构
CREATE TABLE employee LIKE t_employee;

-- 复制数据
INSERT INTO employee (SELECT * FROM t_employee);

-- 同时复制结构和数据
CREATE TABLE employee1 AS (SELECT * FROM t_employee);

五、重要技巧和注意事项​

1. 性能优化​

  • 多表连接时，WHERE条件要尽早过滤数据
  • 避免在连接条件中使用函数
  • 合理使用索引

2. 常见错误规避​

同表操作限制：

-- 错误：直接在同表子查询中更新
UPDATE t_employee SET salary = (SELECT salary FROM t_employee WHERE ename = '孙红梅') 
WHERE ename = '李冰冰';

-- 正确：嵌套子查询释放表引用
UPDATE t_employee SET salary = (
    SELECT salary FROM (SELECT salary FROM t_employee WHERE ename = '孙红梅') AS temp
) WHERE ename = '李冰冰';

3. 连接查询核心规则​

  • N张表连接需要N-1个连接条件
  • 逻辑主表确定查询结果的完整性
  • 表别名提高可读性和避免列名冲突

4. 子查询选择原则​

  • 标量子查询：单一条件判断
  • 行子查询：多列整体比较
  • 列子查询：多值条件筛选
  • 表子查询：复杂数据预处理