深入理解 SQL 中的多表查询——以员工信息查询为例

深入理解 SQL 中的多表查询——以员工信息查询为例

引言

在数据库查询中，多表查询是常见的需求。通过多表查询，我们可以从多个表中获取相关联的数据，从而实现更复杂的数据分析和报表生成。本文将通过一个具体的案例，详细解释如何进行多表查询，并揭示一些常见的误区。

数据库设计

首先，我们来看一下数据库的设计。假设我们有一个数据库 db_design_3，其中包含以下几张表：

• dept：部门表，包含部门的基本信息。
• job：职务表，包含职务的基本信息。
• emp：员工表，包含员工的基本信息。
• salarygrade：工资等级表，包含工资等级的基本信息。

创建数据库和表

让我们从创建数据库和表开始。以下是创建数据库和表的 SQL 代码：

CREATE DATABASE IF NOT EXISTS db_design_3;

USE db_design_3;

DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
DROP TABLE IF EXISTS job;
DROP TABLE IF EXISTS salarygrade;

-- 部门表
CREATE TABLE dept (
  id INT PRIMARY KEY, -- 部门id
  dname VARCHAR(50), -- 部门名称
  loc VARCHAR(50) -- 部门所在地
);

-- 职务表，职务名称，职务描述
CREATE TABLE job (
  id INT PRIMARY KEY,
  jname VARCHAR(20),
  description VARCHAR(50)
);

-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY, -- 员工id
  ename VARCHAR(50), -- 员工姓名
  job_id INT, -- 职务id
  mgr INT, -- 上级领导
  joindate DATE, -- 入职日期
  salary DECIMAL(7,2), -- 工资
  bonus DECIMAL(7,2), -- 奖金
  dept_id INT, -- 所在部门编号
  CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
  CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);

-- 工资等级表
CREATE TABLE salarygrade (
  grade INT PRIMARY KEY,   -- 级别
  losalary INT,  -- 最低工资
  hisalary INT -- 最高工资
);

插入示例数据

接下来，我们插入一些示例数据：

-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');

-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');

-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);

-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

查询问题

现在，我们已经创建了数据库和表，并插入了示例数据。接下来，我们将通过一系列查询问题来深入理解多表查询。

问题 1：查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述

SELECT
  e.`id`,
  e.`ename`,
  e.`salary`,
  j.`jname`,
  j.`description`
FROM
  emp e
  INNER JOIN job j
    ON e.`job_id` = j.`id`;

问题 2：查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置

SELECT
  e.`id`,
  e.`ename`,
  e.`salary`,
  j.`jname`,
  j.`description`,
  d.dname,
  d.loc
FROM
  emp e
  INNER JOIN job j ON e.`job_id` = j.`id`
  INNER JOIN dept d ON e.dept_id = d.`id`;

问题 3：查询员工姓名，工资，工资等级

SELECT t.*, s.grade 
FROM (SELECT ename, salary FROM emp) AS t 
INNER JOIN salarygrade s 
ON t.salary BETWEEN s.`losalary` AND s.`hisalary`;

SELECT t.*, s.grade 
FROM (SELECT ename, salary FROM emp) AS t 
INNER JOIN salarygrade s 
ON t.salary IN (s.`losalary`, s.`hisalary`);

问题 4：查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

SELECT
  e.`id`,
  e.`ename`,
  e.`salary`,
  j.`jname`,
  j.`description`,
  d.dname,
  d.loc,
  s.grade
FROM
  emp e
  INNER JOIN job j ON e.`job_id` = j.`id`
  INNER JOIN dept d ON e.dept_id = d.`id`
  INNER JOIN salarygrade s ON e.`salary` BETWEEN s.`losalary` AND s.`hisalary`;

问题 5：查询出部门编号、部门名称、部门位置、部门人数

SELECT d.`id`, d.`dname`, d.`loc`, t.`count` 
FROM dept d 
INNER JOIN (SELECT e.`dept_id`, COUNT(*) `count` FROM emp e GROUP BY e.`dept_id`) t
ON t.dept_id = d.`id`;

深入分析

让我们深入分析每个查询问题，并解释其背后的逻辑。

问题 1：查询所有员工信息

SELECT
  e.`id`,
  e.`ename`,
  e.`salary`,
  j.`jname`,
  j.`description`
FROM
  emp e
  INNER JOIN job j
    ON e.`job_id` = j.`id`;

解释：

• 通过 INNER JOIN 将 emp 表和 job 表连接起来，连接条件是 emp.job_id = job.id。
• 查询结果包含员工的编号、姓名、工资、职务名称和职务描述。

问题 2：查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置

SELECT
  e.`id`,
  e.`ename`,
  e.`salary`,
  j.`jname`,
  j.`description`,
  d.dname,
  d.loc
FROM
  emp e
  INNER JOIN job j ON e.`job_id` = j.`id`
  INNER JOIN dept d ON e.dept_id = d.`id`;

解释：

• 通过两次 INNER JOIN 将 emp 表、job 表和 dept 表连接起来。
• 第一次连接条件是 emp.job_id = job.id，第二次连接条件是 emp.dept_id = dept.id。
• 查询结果包含员工的编号、姓名、工资、职务名称、职务描述、部门名称和部门位置。

问题 3：查询员工姓名，工资，工资等级

SELECT t.*, s.grade 
FROM (SELECT ename, salary FROM emp) AS t 
INNER JOIN salarygrade s 
ON t.salary BETWEEN s.`losalary` AND s.`hisalary`;

解释：

• 使用子查询 (SELECT ename, salary FROM emp) 获取员工的姓名和工资，并将结果集命名为 t。
• 通过 INNER JOIN 将 t 表和 salarygrade 表连接起来，连接条件是 t.salary BETWEEN s.losalary AND s.hisalary。
• 查询结果包含员工的姓名、工资和对应的工资等级。

误区

SELECT t.*, s.grade 
FROM (SELECT ename, salary FROM emp) AS t 
INNER JOIN salarygrade s 
ON t.salary IN (s.`losalary`, s.`hisalary`);

解释：

• 使用子查询 (SELECT ename, salary FROM emp) 获取员工的姓名和工资，并将结果集命名为 t。
• 通过 INNER JOIN 将 t 表和 salarygrade 表连接起来，连接条件是 t.salary IN (s.losalary, s.hisalary)。
• 查询结果包含员工的姓名、工资和对应的工资等级，但只有当员工的工资恰好等于某个工资等级的最低工资或最高工资时，才会返回结果。所以这个SQL语句不满足要求

问题 4：查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

SELECT
  e.`id`,
  e.`ename`,
  e.`salary`,
  j.`jname`,
  j.`description`,
  d.dname,
  d.loc,
  s.grade
FROM
  emp e
  INNER JOIN job j ON e.`job_id` = j.`id`
  INNER JOIN dept d ON e.dept_id = d.`id`
  INNER JOIN salarygrade s ON e.`salary` BETWEEN s.`losalary` AND s.`hisalary`;

解释：

• 通过三次 INNER JOIN 将 emp 表、job 表、dept 表和 salarygrade 表连接起来。
• 第一次连接条件是 emp.job_id = job.id，第二次连接条件是 emp.dept_id = dept.id，第三次连接条件是 emp.salary BETWEEN salarygrade.losalary AND salarygrade.hisalary。
• 查询结果包含员工的编号、姓名、工资、职务名称、职务描述、部门名称、部门位置和工资等级。

问题 5：查询出部门编号、部门名称、部门位置、部门人数

SELECT d.`id`, d.`dname`, d.`loc`, t.`count` 
FROM dept d 
INNER JOIN (SELECT e.`dept_id`, COUNT(*) `count` FROM emp e GROUP BY e.`dept_id`) t
ON t.dept_id = d.`id`;

解释：

• 使用子查询 (SELECT e.dept_id, COUNT(*) count FROM emp e GROUP BY e.dept_id) 获取每个部门的员工人数，并将结果集命名为 t。
• 通过 INNER JOIN 将 dept 表和 t 表连接起来，连接条件是 t.dept_id = dept.id。
• 查询结果包含部门编号、部门名称、部门位置和部门人数。

总结

通过本文的引导式教学，我们深入理解了 SQL 中的多表查询，并通过具体的例子揭示了一些常见的误区。希望大家在实际应用中能够更加谨慎地编写查询语句，确保查询结果的准确性和可靠性。

进一步思考

• 如何优化查询语句以提高性能？
• 在实际应用中，如何处理更复杂的多表连接和子查询？

希望这篇文章能够帮助大家更好地掌握 SQL 中的多表查询，并在实际工作中灵活运用。