首页 > 数据库 >深入理解 SQL 中的多表查询——以员工信息查询为例

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

时间:2024-11-26 13:11:06浏览次数:8  
标签:多表 为例 查询 dept job emp id INNER

深入理解 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 JOINemp 表和 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 JOINemp 表、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 JOINt 表和 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 JOINt 表和 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 JOINemp 表、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 JOINdept 表和 t 表连接起来,连接条件是 t.dept_id = dept.id
  • 查询结果包含部门编号、部门名称、部门位置和部门人数。

总结

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

进一步思考

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

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

标签:多表,为例,查询,dept,job,emp,id,INNER
From: https://www.cnblogs.com/itcq1024/p/18569916

相关文章

  • 深入理解 SQL 中的 `IN` 和 `BETWEEN` 操作符——以工资等级查询为例
    深入理解SQL中的IN和BETWEEN操作符——以工资等级查询为例引言在SQL查询中,IN和BETWEEN是两个常用的操作符,用于过滤数据。虽然它们都可以用于条件查询,但它们的逻辑和适用场景是不同的。本文将通过一个具体的例子,详细解释IN和BETWEEN的区别,并揭示它们在实际应用中......
  • GaussDB数据库SQL系列-层次递归查询
    一、前言层次递归查询是一种常见的SQL查询方式,特别是在一些层次化的数据存储结构中经常用到。本文主要以GaussDB数据库为实验平台,为大家讲解其使用方法。二、GuassDB数据库层次递归查询概念层次化结构可以理解为树状数据结构,由节点构成。举个简单的例子,如下图所示,由子节点向上......
  • MySQL 查询做了这些优化后,纵享丝滑…… 转载
    一、前言 在程序上线运行一段时间后,一旦数据量上去了,或多或少会感觉到系统出现延迟、卡顿等现象,出现这种问题,就需要程序员或架构师进行系统调优工作了。 其中,大量的实践经验表明,调优的手段尽管有很多,但涉及到SQL调优的内容仍然是非常重要的一环,本文将结合实例,总结一些工作中......
  • 异步与资源调度 以浏览器事件循环为例
    初次发布于我的个人文档参考:chromiun官方文档w3c官方文档针对一个异步的程序应该如何对它进行资源的调度呢?本文以浏览器为典型范例进行简单介绍。1.查看浏览器的多进程图景打开任意一个浏览器这里以edge为例。然后打开Windows的任务管理器,你看到的可能是这样:事实上,在edg......
  • 梧桐数据库之分析IN和EXISTS查询效率
    一、背景描述在数据库查询优化中,IN和EXISTS是两种常用的子查询操作符,它们在某些情况下可以互换使用,但在执行效率和适用场景上存在差异。下面我将通过案例分析来探讨这两种操作符在梧桐云原生分析型数据库中的使用及其效率对比。假设我们有一个电子商务平台,其中包含两个表:orde......
  • GaussDB数据库SQL系列-层次递归查询
    一、前言层次递归查询是一种常见的SQL查询方式,特别是在一些层次化的数据存储结构中经常用到。本文主要以GaussDB数据库为实验平台,为大家讲解其使用方法。二、GuassDB数据库层次递归查询概念层次化结构可以理解为树状数据结构,由节点构成。举个简单的例子,如下图所示,由子节点向上......
  • GaussDB分区表查询性能异常分析
    问题现象使用分区表进行相关查询业务,SQL性能慢。原因分析导致分区表业务慢的常见原因有以下几种:分区索引失效,顺序扫描导致的SQL性能慢分区表无法进行分区剪枝导致的SQL性能慢SQL计划选择非最优导致的SQL性能慢处理方法判断是否存在索引异常的行为部分分区DDL如果不带UP......
  • GaussDB分区表查询性能异常分析
    问题现象使用分区表进行相关查询业务,SQL性能慢。原因分析导致分区表业务慢的常见原因有以下几种:分区索引失效,顺序扫描导致的SQL性能慢分区表无法进行分区剪枝导致的SQL性能慢SQL计划选择非最优导致的SQL性能慢处理方法判断是否存在索引异常的行为部分分区DDL如果不带UP......
  • 以学校数据模型为例,掌握在DAS下使用GaussDB
    @目录题目具体操作一、表的创建二、表数据的插入三、数据查询目的:这里以学校数据库模型为例,介绍GaussDB数据库、表等常见操作,以及SQL语法使用的介绍。题目假设A市B学校为了加强对学校的管理,引入了华为GaussDB数据库。在B学校里,主要涉及的对象有学生、教师、班级、院系和课程......
  • 查询三个ai平台还不如自己解决?
    兄弟们你们有遇到这种情况吗?下面是关于文件操作的代码,这个代码我断断续续研究了有好几天了,硬是找不出什么原因,增加了表头(即学号、姓名和年龄)后就打印不出数据,不使用循环的话就打印出2个乱码,把表头删除后就能打印出数据。之后我去查询了3个不同平台的ai,给的代码打印出来的效......