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 刘备
商业转载 请联系作者获得授权，非商业转载 请标明出处，谢谢
844
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
