MySQL的基本学习(四)——多表查询的练习

前言

SQL中的DQL中的多表查询是数据库学习中的重点、难点,因此,我特地多开一篇文章来记录学习多表查询的练习。

 

 

 

 


准备工作

将如下命令复制到SQLyog中:

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

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



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

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



-- 员工表
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)
);

-- 添加员工
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);



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

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

然后我们将这些命令在一个干净的数据库中全部选中执行,执行完成后,我们可以利用架构设计器查看一下效果:

 

开始练习

— 需求:

— 1.查询所有员工信息。查询员工编号,员工姓名,工资,职务名称,职务描述

— 2.查询员工编号,员工姓名,工资,职务名称,职务描述,部门名称,部门位置

— 3.查询员工姓名,工资,工资等级

— 4.查询员工姓名,工资,职务名称,职务描述,部门名称,部门位置,工资等级

— 5.查询出部门编号、部门名称、部门位置、部门人数

— 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询

 

任务一

查询所有员工信息。查询员工编号,员工姓名,工资,职务名称,职务描述

SELECT 
t1.`id`,  -- 员工编号
t1.`ename`,  -- 员工姓名
t1.`salary`,  -- 工资
t2.`jname`,   -- 职务名称
t2.`description`  -- 职务描述
FROM 
emp t1,job t2
WHERE
t1.`job_id`=t2.`id`;

隐式内连接就搞定。

    id  ename      salary    jname      description                  
------  ---------  --------  ---------  -----------------------------
  1009  罗贯中        50000.00  董事长        管理整个公司,接单  
  1004  唐僧         29750.00  经理         管理部门员工           
  1006  宋江         28500.00  经理         管理部门员工           
  1007  刘备         24500.00  经理         管理部门员工           
  1002  卢俊义        16000.00  销售员        向客人推销产品        
  1003  林冲         12500.00  销售员        向客人推销产品        
  1010  吴用         15000.00  销售员        向客人推销产品        
  1001  孙悟空        8000.00   文员         使用办公软件           
  1005  李逵         12500.00  文员         使用办公软件           
  1008  猪八戒        30000.00  文员         使用办公软件           
  1011  沙僧         11000.00  文员         使用办公软件           
  1012  李逵         9500.00   文员         使用办公软件           
  1013  小白龙        30000.00  文员         使用办公软件           
  1014  关羽         13000.00  文员         使用办公软件   

 

任务二

查询员工编号,员工姓名,工资,职务名称,职务描述,部门名称,部门位置

SELECT 
t1.`id`, -- 员工编号
t1.`ename`, -- 员工姓名
t1.`salary`, -- 工资
t2.`jname`,  -- 职务名称
t2.`description`,  -- 职务描述
t3.`dname`,  -- 部门名称
t3.`loc`  -- 部门位置
FROM
emp t1,job t2,dept t3
WHERE
t1.`job_id`=t2.`id` 
AND
t1.`dept_id`=t3.`id`;

还是隐式内连接,在条件部分利用AND关键字连接了两个条件。

    id  ename      salary    jname      description        dname      loc     
------  ---------  --------  ---------  ---------------------------  ---------  --------
  1009  罗贯中        50000.00  董事长  管理整个公司,接单   教研部        北京  
  1004  唐僧         29750.00  经理     管理部门员工        学工部        上海  
  1006  宋江         28500.00  经理     管理部门员工        销售部        广州  
  1007  刘备         24500.00  经理     管理部门员工        教研部        北京  
  1002  卢俊义        16000.00  销售员   向客人推销产品     销售部        广州  
  1003  林冲         12500.00  销售员   向客人推销产品      销售部        广州  
  1010  吴用         15000.00  销售员   向客人推销产品      销售部        广州  
  1001  孙悟空        8000.00   文员    使用办公软件        学工部        上海  
  1005  李逵         12500.00  文员    使用办公软件         销售部        广州  
  1008  猪八戒        30000.00  文员     使用办公软件       学工部        上海  
  1011  沙僧         11000.00  文员     使用办公软件        学工部        上海  
  1012  李逵         9500.00   文员     使用办公软件        销售部        广州  
  1013  小白龙        30000.00  文员     使用办公软件       学工部        上海  
  1014  关羽         13000.00  文员     使用办公软件       教研部        北京  

 

任务三

查询员工姓名,工资,工资等级

SELECT 
t1.`ename`, -- 员工姓名
t1.`salary`, -- 工资
t2.`grade`    -- 工资等级
FROM
emp t1,salarygrade t2
WHERE
t1.`salary`
BETWEEN 
t2.`losalary`
AND
t2.`hisalary`;

隐式内连接,利用 BETWEEN…AND… 语法

ename      salary     grade  
---------  --------  --------
孙悟空        8000.00          1
卢俊义        16000.00         3
林冲         12500.00         2
唐僧         29750.00         4
李逵         12500.00         2
宋江         28500.00         4
刘备         24500.00         4
猪八戒        30000.00         4
罗贯中        50000.00         5
吴用         15000.00         3
沙僧         11000.00         1
李逵         9500.00          1
小白龙        30000.00         4
关羽         13000.00         2

任务四

查询员工姓名,工资,职务名称,职务描述,部门名称,部门位置,工资等级

SELECT 
t1.`ename`, -- 员工姓名
t1.`salary`, -- 工资
t2.`jname`,  -- 职务名称
t2.`description`, -- 职务描述
t3.`dname`,  -- 部门名
t3.`loc`, -- 部门位置
t4.`grade` -- 工资等级
FROM
emp t1,job t2,dept t3,salarygrade t4
WHERE
t1.`job_id`=t2.`id`
AND
t1.`dept_id`=t3.`id`
AND
t1.`salary` BETWEEN t4.`losalary` AND t4.`hisalary` 
ename      salary    jname      description                  dname      loc      grade  
---------  --------  ---------  ---------------------------  ---------  ------  --------
孙悟空        8000.00   文员         使用办公软件             学工部        上海             1
卢俊义        16000.00  销售员        向客人推销产品          销售部        广州             3
林冲         12500.00  销售员        向客人推销产品           销售部        广州             2
唐僧         29750.00  经理         管理部门员工             学工部        上海             4
李逵         12500.00  文员         使用办公软件             销售部        广州             2
宋江         28500.00  经理         管理部门员工             销售部        广州             4
刘备         24500.00  经理         管理部门员工             教研部        北京             4
猪八戒        30000.00  文员         使用办公软件             学工部        上海             4
罗贯中        50000.00  董事长        管理整个公司,接单       教研部        北京             5
吴用         15000.00  销售员        向客人推销产品           销售部        广州             3
沙僧         11000.00  文员         使用办公软件             学工部        上海             1
李逵         9500.00   文员         使用办公软件             销售部        广州             1
小白龙        30000.00  文员         使用办公软件             学工部        上海             4
关羽         13000.00  文员         使用办公软件             教研部        北京             2

 

任务五

查询出部门编号、部门名称、部门位置、部门人数

这里的难点在于部门人数不好查,我们的思路是使用分组查询。按照emp.dept_id完成分组,查询count(id),最后我们还需要利用子查询将刚才的结果和dept表进行关联查询。

SELECT 
	t1.`id`,t1.`dname`,t1.`loc`,t2.total
FROM
	dept t1,
	(SELECT   -- 虚拟子表
	dept_id,COUNT(id) total -- 给总人数也起一个别名
	FROM
	emp
	GROUP BY dept_id) t2
WHERE t1.`id`=	t2.dept_id
    id  dname      loc      total  
------  ---------  ------  --------
    10  教研部        北京             3
    20  学工部        上海             5
    30  销售部        广州             6

 

任务六

查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询

在emp表中的mgr字段即为当前员工的上级的id,例如林冲的mgr是1006,故林冲的上级是id为1006的员工宋江。

分析:员工的姓名和mgr对应的id的姓名,这两个概念都是在emp表中,故emp是自关联,可以通过起别名的方式来虚拟一个相同的表。然后因为必须要左表的所有数据,所以我们需要左外连接查询。

SELECT
	t1.`ename`, -- 员工姓名
	t1.`mgr`, -- 员工职务
	t2.`id`, -- 上级的id
	t2.`ename` -- 上级的姓名
FROM 
	emp t1
LEFT JOIN 
	emp t2
ON 	
	t1.`mgr`=t2.`id`;

ename mgr id ename
——— —— —— ———–
孙悟空 1004 1004 唐僧
卢俊义 1006 1006 宋江
林冲 1006 1006 宋江
唐僧 1009 1009 罗贯中
李逵 1006 1006 宋江
宋江 1009 1009 罗贯中
刘备 1009 1009 罗贯中
猪八戒 1004 1004 唐僧
罗贯中 (NULL) (NULL) (NULL)
吴用 1006 1006 宋江
沙僧 1004 1004 唐僧
李逵 1006 1006 宋江
小白龙 1004 1004 唐僧
关羽 1007 1007 刘备


 

 

 

 

 

商业转载 请联系作者获得授权,非商业转载 请标明出处,谢谢

发表评论