多表查询

最新推荐文章于 2022-09-26 20:58:58 发布

h294590501

最新推荐文章于 2022-09-26 20:58:58 发布

阅读量499

点赞数 1

分类专栏： SQL 文章标签：多表查询 SQL

本文链接：https://blog.csdn.net/h294590501/article/details/80385945

版权

SQL 专栏收录该内容

20 篇文章 0 订阅

订阅专栏

多表查询

-- 数据准备

# 创建部门表

CREATE TABLE dept(

  id INT PRIMARY KEY AUTO_INCREMENT,

  NAME VARCHAR(20)

);

INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');  

# 创建员工表

CREATE TABLE emp (

  id INT PRIMARY KEY AUTO_INCREMENT,

  NAME VARCHAR(10),

  gender CHAR(1),   -- 性别

  salary DOUBLE,   -- 工资

  join_date DATE,  -- 入职日期

  dept_id INT,

  FOREIGN KEY (dept_id) REFERENCES dept(id) -- 外键，关联部门表(部门表的主键)

);  

 

INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);

INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);

INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);

INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);

INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);

笛卡尔积现象

多表查询时左表的每条数据和右表的每条数据组合，这种效果成为笛卡尔积

不管我们查询几张表，表连接查询会产出笛卡尔积，我们需要消除笛卡尔积，拿到正确的数据。
我们需要找到表与表之间通过哪个字段关联起来的（通常是外键=主键）

关联的条件数：消除笛卡尔积规律：
2 张表需要 1 个条件，3 张表需要 2 个条件，4 张表需要 3 个条件。
（条件数量=表的数量-1），每张表都要参与进来

需求：查询所有的员工和所有的部门

SELECT * FROM emp,dept;  --求出的结果就是笛卡尔积

如何清除笛卡尔积现象的影响

我们发现不是所有的数据组合都是有用的，
只有**员工表.dept_id = 部门表.id** 的数据才是有用的。
所以需要通过条件过滤掉没用的数据。 

-- 指定条件进行过滤，这就是内连接，隐式内连接
SELECT * FROM dept d ,emp e WHERE d.id = e.dept_id;

内连接查询

用左边表的记录去匹配右边表的记录，如果符合条件的则显示,使用到的就是内连接

隐式内连接：
看不到 JOIN 关键字，条件使用 
WHERE 指定 SELECT 字段名 FROM 左表, 右表 WHERE 条件；

SELECT * FROM dept d i, emp e WHERE d.id = e.dept_id;


显式内连接：使用INNER JOIN ... ON语句, 可以省略INNER
INNER SELECT 字段名 FROM 左表 INNER JOIN右表 ON 条件；

SELECT * FROM dept d INNER JOIN emp e ON d.id = e.dept_id;

-- 我们发现需要联合2张表同时才能查询出需要的数据，使用内连接
-- 1. 确定查询哪些表: dept和emp   这里是笛卡尔积
SELECT * FROM dept d INNER JOIN emp e;

 
-- 2. 确定过滤的条件，这是显式内连接
SELECT * FROM dept d INNER JOIN emp e ON d.id = e.dept_id;

 
-- 3. 确定查询哪些字段
SELECT e.id, e.name, e.gender,e.salary, d.name FROM dept d INNER JOIN emp e ON d.id = e.dept_id;

 
-- 4. 可选：给字段起别名
SELECT e.id 编号, e.name 姓名, e.gender 性别,e.salary 工资, d.name 部门名 FROM dept d INNER JOIN emp e ON d.id = e.dept_id;

 
-- 5. 可以指定Where条件：查询唐僧
SELECT e.id 编号, e.name 姓名, e.gender 性别,e.salary 工资, d.name 部门名 FROM dept d INNER JOIN emp e ON d.id = e.dept_id WHERE e.name = '唐僧';

内连接查询步骤：

-- 1) 确定查询哪些表
-- 2) 确定表连接的条件
-- 3) 确定查询哪些字段

左外连接

左外连接：使用LEFT OUTER JOIN ... ON，OUTER可以省略 
SELECT 字段名 FROM 左表 LEFT OUTER

select * from dept left join emp on dept.id = emp.dept_id;


JOIN 右表 ON 条件； 用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示 NULL

可以理解为：在内连接的基础上保证左表的数据全部显示

SELECT * FROM dept;  -- 左表

SELECT * FROM emp;  -- 右表

-- 左表和右表没有固定的要求，哪张表都可以是左表

-- 左连接

-- 在部门表中增加一个销售部，将部门表设置成左表，员工表设置成右表

INSERT INTO dept VALUES (NULL, '销售部');

-- 使用内连接查询

SELECT * FROM dept INNER JOIN emp ON dept.id = emp.dept_id;

-- 要求：要显示所有的部门信息，使用左外连接查询

SELECT * FROM dept LEFT JOIN emp ON dept.id = emp.dept_id;

右外连接

右外连接：使用 RIGHT OUTER JOIN ... ON，OUTER 可以省略 
SELECT 字段名 FROM 左表 RIGHT OUTER JOIN 右表 ON 条件；

select * from dept right join emp on dept.id = emp.dept_id;

用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示 NULL 

可以理解为：在内连接的基础上保证右表的数据全部显示

-- 左表是部门表，右表是员工表

-- 在员工表中增加一个员工:'沙僧','男',6666,'2013-02-24',null  ,他不在任何一个部门

INSERT INTO emp VALUES(NULL,'沙僧','男',6666,'2013-02-24',NULL );

SELECT * FROM dept;  -- 左表

SELECT * FROM emp;  -- 右表

 

-- 使用内连接查询 
SELECT * FROM dept INNER JOIN emp ON dept.id = emp.dept_id;

 
-- 使用右外连接查询 
SELECT * FROM dept RIGHT JOIN emp ON dept.id = emp.dept_id;

-- 在oracle和sql server中还有一种外连接：全连接，相当于左连接+右连接，让左表和右表都出现，

-- 没有匹配的数据使用NULL，mysql中没有全连接

-- SELECT * FROM dept FULL JOIN emp ON dept.id = emp.dept_id;

子查询

什么是子查询

1) 一条语句的查询结果做为另一条查询语句的条件

2) 存在查询的嵌套，内部的查询称为子查询，外部的查询称为父查询

3) 子查询外面要使用括号

-- 需求：查询开发部中有哪些员工

SELECT * FROM dept;

SELECT * FROM emp;

-- 1. 在部门表中查询开发的id

SELECT id FROM dept WHERE NAME='开发部';

-- 2. 在员工表中查询dept_id=1的员工

SELECT * FROM emp WHERE dept_id=1;

-- 使用子查询

SELECT * FROM emp WHERE dept_id=(SELECT id FROM dept WHERE NAME='开发部');

子查询结果的三种情况：

1) 子查询的结果是1个值，单行单列

2) 子查询的结果是多行单列，可以理解为一个集合或数组

3) 子查询的结果是多行多列的情况，可以理解为是一张虚拟表。可以进行再次的查询。

子查询的结果是一个值的时候

子查询结果只要是单行单列，肯定在WHERE后面作为条件，父查询使用：比较运算符，如：> 、<、<>、= 等

-- 查询工资大于"蜘蛛精"的员工

-- 1. 查询蜘蛛精的工资是多少

SELECT salary FROM emp WHERE NAME='蜘蛛精';

-- 2. 查询大于这个工资的员工

SELECT * FROM emp WHERE salary > (SELECT salary FROM emp WHERE NAME='蜘蛛精');

 

-- 列出与孙悟空在同一个部门的员工

-- 1. 找出孙悟空在哪个部门的编号

SELECT dept_id FROM emp WHERE NAME='孙悟空';

-- 2. 查询部门编号与上面结果相同的员工

SELECT * FROM emp WHERE dept_id = (SELECT dept_id FROM emp WHERE NAME='孙悟空');

子查询结果是多行单例的时候

-- 子查询结果是单例多行，结果集类似于一个数组，父查询使用in、any、all运算符 
-- 查询工资大于5000的员工，来自于哪些部门的名字
-- 1. 先查询大于5000的员工所在的部门id
SELECT dept_id FROM emp WHERE salary > 5000;


-- 2. 再查询在这些部门id中部门的名字  Subquery returns more than 1 row  子查询返回了大于1条的记录
SELECT NAME FROM dept WHERE id IN (SELECT dept_id FROM emp WHERE salary > 5000);

 

-- 列出工资高于在1号部门工作的所有员工，显示员工姓名和薪金、部门名称。

-- 1. 查询1号部门所有员工的工资，得到多行单列
SELECT salary FROM emp WHERE dept_id=1;


-- 2. 使用大于号不能计算，怎么办？使用all运算符
SELECT * FROM emp WHERE salary > ALL (SELECT salary FROM emp WHERE dept_id=1);


-- 只要大于任何一个都可以
SELECT * FROM emp WHERE salary > ANY (SELECT salary FROM emp WHERE dept_id=1);

子查询的结果是多行多列

子查询结果只要是多列，肯定在FROM后面作为表 

子查询作为表需要取别名，否则这张表没有名称则无法访问表中的字段

-- 查询出2011年以后入职的员工信息，包括部门名称
-- 1. 在员工表中查询大于2011-1-1以后入职的员工
SELECT * FROM emp WHERE join_date > '2011-01-01'

-- 2. 查询所有的部门信息，与上面的虚拟表中的信息组合，找出所有部门id等于的dept_id
SELECT * FROM dept d , (SELECT * FROM emp WHERE join_date > '2011-01-01') e WHERE d.id = e.dept_id;


-- 也可以使用表连接：
SELECT * FROM dept d INNER JOIN emp e ON d.id = e.dept_id WHERE e.join_date>'2011-01-01';
 
SELECT * FROM dept d INNER JOIN emp e ON d.id = e.dept_id AND e.join_date>'2011-01-01';

子查询小结

-- 子查询结果只要是单列，则在WHERE后面作为条件 

-- 子查询结果只要是多列，则在FROM后面作为表

多表查询的案例

准备数据

-- 部门表
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) 确定要查询哪些表：员工表和职务表emp e, job j

2) 确定表连接条件： e.job_id=j.id

3) 确定查询字段：员工编号，员工姓名，工资，职务名称，职务描述

SELECT e.id 编号, e.ename 姓名, e.salary 工资, j.jname 职务名称, j.description 描述 FROM emp e INNER JOIN job j ON e.job_id = j.id

需求：查询经理的信息。

显示经理姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

1) 确定要查询哪些表，
emp e, job j, dept d, salarygrade s

2) 确定表连接条件
e.job_id=j.id AND e.dept_id=d.id AND e.salary BETWEEN s.losalary AND hisalary
额外条件：只需要查询经理的信息（j.jname='经理'） 

3) 确定查询字段：员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
SELECT e.ename,e.salary,j.jname,j.description,d.dname,d.loc,s.grade FROM emp e INNER JOIN dept d INNER JOIN job j INNER JOIN salarygrade s 
ON d.id = e.dept_id AND j.id=e.job_id AND e.salary BETWEEN s.losalary AND s.hisalary WHERE j.jname = '经理';

多表查询规律小结：

关联字段：
不管我们查询几张表，表连接查询会产出笛卡尔积，需要消除笛卡尔积，拿到正确的数据。
需要找到表与表之间通过哪个字段关联起来的（通常是外键=主键）

关联的条件数：
消除笛卡尔积规律：2张表需要1个条件，3张表需要2个条件，4张表需要3个条件。
（条件数量=表的数量-1），每张表都要参与进来。

多表连接查询步骤：

1) 确定查询哪些表

2) 确定表连接的条件

3) 确定查询哪些字段

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

1) 查员工表，按部门编号进行分组，找到每个部门的人数和部门id 
SELECT dept_id, COUNT(*) c FROM emp GROUP BY dept_id

2) 将上面的查询结果做为虚拟表再和部门表进行表连接查询
SELECT * FROM dept d, (SELECT dept_id, COUNT(*) c FROM emp GROUP BY dept_id) e WHERE d.id = e.dept_id

3) 显示对应的字段
SELECT d.id,d.dname,d.loc,e.c 人数 FROM dept d, (SELECT dept_id, COUNT(*) c FROM emp GROUP BY dept_id) e WHERE d.id = e.dept_id

需求：列出所有员工的姓名及其直接上级的姓名,没有领导的员工也需要显示。

-- 用到了自连接，不是一种新的连接。左表与右表是同一张表。也有主外键关系

1) 确定要查询哪些表，emp e, emp m

2) 确定表连接条件 e.mgr=m.id

-- 发现员工表中少了一条数据，因为罗贯中是董事长没有上司，
-- 没有领导的员工也需要显示，所以左表的数据需要全部显示。应该改成左外连接。

3) 确定查询字段：员工的姓名及其直接上级的姓名

 注：IFNULL函数的作用是，如果前面的参数有值，则显示原有的值，如果没有值，则显示后面的参数

-- 列出所有员工的姓名及其直接上级的姓名,没有领导的员工也需要显示。最高级没有上司，mgr是NULL

-- 其中mgr是外键，主表是上级，从表是员工表

SELECT e.ename 员工, m.ename 上司 FROM emp e INNER JOIN emp m ON m.id = e.mgr;

-- 换成左连接，因为左表中所有的数据要全部出现

SELECT e.ename 员工,IFNULL(m.ename,'没有上司') 上司 FROM emp e LEFT JOIN emp m ON m.id = e.mgr;

需求：查询工资高于公司平均工资的所有员工列：显示员工信息，部门名称，上级领导，工资等级

1) 要使用子查询，先统计公司平均工资
SELECT AVG(salary) FROM emp
2) 确定要查询哪些表：emp e, emp m, dept d, salarygrade s
注：为了让所有的员工都显示出来，e与m的查询要使用左连接，后面的d和s使用内连接。
先将e与m进行左连接，得到结果再与d进行内连接，得到结果再与s进行内连接

3) 确定表连接条件 e.dept_id=d.id、e.mgr=m.id、e.salary BETWEEN s.losalary AND hisalary、e.salary>公司平均薪金
SELECT * FROM emp WHERE salary >(SELECT AVG(salary) FROM emp);

4) 确定查询字段：员工所有信息，部门名称，上级领导，工资等级。

-- 多表连接查询，每次连接两张表，再连第三表，再连第四表

SELECT  e.*, d.dname,m.ename,s.grade  FROM emp e LEFT JOIN emp m ON m.id = e.mgr INNER JOIN dept d 

ON e.dept_id = d.id INNER JOIN salarygrade s ON e.salary BETWEEN s.losalary AND s.hisalary

WHERE e.salary >(SELECT AVG(salary) FROM emp);

-- 准备数据
CREATE DATABASE test;
USE test;

-- 部门表
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
-- 需求：查询所有员工信息。显示员工编号,员工姓名,工资,职务名称,职务描述
-- 具体操作：  
-- 1)确定要查询哪些表：员工表和职务表emp e, job j
SELECT * FROM emp e INNER JOIN job j;

-- 2)确定表连接条件： e.job_id=j.id
SELECT * FROM emp e INNER JOIN job j ON e.job_id=j.id;

-- 3)确定查询字段：员工编号,员工姓名,工资,职务名称,职务描述
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
-- 需求：查询所有员工信息。显示员工编号,员工姓名,工资,职务名称,职务描述,部门名称,部门位置
-- 具体操作：
-- 1)确定要查询哪些表,emp e, job j, dept d
SELECT * FROM emp e INNER JOIN job j INNER JOIN dept d;


-- 2)确定表连接条件 e.job_id=j.id and e.dept_id=d.id
SELECT * FROM emp e INNER JOIN job j INNER JOIN dept d ON e.`job_id`=j.`id` AND e.`dept_id`=d.`id`;


-- 3)确定查询字段：员工编号,员工姓名,工资,职务名称,职务描述,部门名称,部门位置
SELECT * FROM emp e INNER JOIN job j


-- 练习3
-- 需求：查询所有员工信息。显示员工姓名,工资,工资等级
-- 具体操作：
-- 1)确定要查询哪些表,emp e, salarygrade s
SELECT * FROM emp e INNER JOIN  salarygade s  ;

-- 2)确定表连接条件：员工表中工资数额与工资等级表之间的关联条件是：工资在最低与最高工资之间
SELECT * FROM emp e INNER JOIN salarygrade s ON e.`salary` BETWEEN s.`losalary`AND s.`hisalary`;


-- 3)确定查询字段：员工姓名,工资,工资等级
SELECT e.`ename` 员工姓名,e.`salary` 工资,s.`grade` 工资等级 FROM emp e INNER JOIN salarygrade s ON e.`salary` BETWEEN s.`losalary` AND s.`hisalary`;

-- 
-- 
-- 多表查询规律小结：
-- 1.关联字段：不管我们查询几张表,表连接查询会产出笛卡尔积,需要消除笛卡尔积,拿到正确的数据。
-- 需要找到表与表之间通过哪个字段关联起来的（通常是外键=主键）
-- 2.关联的条件数：消除笛卡尔积规律：2张表需要1个条件,3张表需要2个条件,4张表需要3个条件。
-- （条件数量=表的数量-1）,每张表都要参与进来。

-- 3.多表连接查询步骤：
-- 1)确定要查询哪些表
-- 2)确定表连接条件
-- 3)确定查询字段


-- 练习4
-- 需求：查询经理的信息。
-- 显示员工姓名,工资,职务名称,职务描述,部门名称,部门位置,工资等级
-- 具体操作：
-- 1)确定要查询哪些表,emp e, job j, dept d, salarygrade s
SELECT * FROM emp e INNER JOIN job j INNER JOIN  dept d  INNER JOIN salarygrade s;

-- 2)确定表连接条件
-- e.job_id=j.id AND e.dept_id=d.id AND e.salary BETWEEN s.losalary AND hisalary
-- 额外条件：只需要查询经理的信息（j.jname='经理'）
SELECT * FROM emp e INNER JOIN job j INNER JOIN dept d INNER JOIN salarygrade s 
ON e.`job_id`= j.`id` AND e.`dept_id` =d.`id` AND e.`salary` BETWEEN s.`losalary` AND s.`hisalary` WHERE j.`jname`='经理';

-- 3)确定查询字段：员工姓名,工资,职务名称,职务描述,部门名称,部门位置,工资等级
SELECT e.`ename` 员工姓名,e.`salary` 工资,j.`jname` 职务名称,j.`description` 职务描述,d.`dname` 部门名称,d.`loc` 部门位置,s.`grade` 工资等级  
FROM emp e INNER JOIN job j INNER JOIN dept d INNER JOIN salarygrade s 
ON e.`job_id`= j.`id` AND e.`dept_id` =d.`id` AND e.`salary` BETWEEN s.`losalary` AND s.`hisalary` WHERE j.`jname`='经理';



-- 练习5
-- 需求：查询出部门编号,部门名称,部门位置,部门人数
-- 具体操作：
-- 1)查员工表,按部门编号进行分组,找到每个部门的人数和部门id
SELECT COUNT(*) c ,`dept_id` FROM emp  GROUP BY `dept_id`;

SELECT * FROM emp;

-- 2)将上面的查询结果做为虚拟表再和部门表进行表连接查询
SELECT *FROM (SELECT COUNT(*) c ,`dept_id` FROM emp  GROUP BY `dept_id`) e INNER JOIN dept d ON e.dept_id =d.`id`;

-- 3)显示对应的字段
SELECT d.`id`部门编号,d.`dname` 部门名称,d.`loc` 部门位置,e.c 部门人数 FROM (SELECT COUNT(*) c ,`dept_id` FROM emp  GROUP BY `dept_id`) e INNER JOIN dept d ON e.dept_id =d.`id`;


-- 练习6
-- 需求：查询所有员工信息。
-- 显示员工信息和部门名称,没有员工的部门也要显示
-- 具体操作：
-- 1)确定要查询哪些表,emp e, dept d
-- 2)确定表连接条件 e.dept_id=d.id,没有员工的部门也要显示。右边数据要全部显示所以使用右外连接
-- 注意：没有员工的部门也要显示。右边数据要全部显示所以使用右外连接
SELECT *FROM emp e RIGHT  JOIN dept d ON e.`dept_id`=d.`id`;

-- 3)确定查询字段：员工信息,部门名称
SELECT e.*,d.`dname` FROM emp e RIGHT JOIN dept d ON e.`dept_id`=d.`id`;


-- 练习7
-- 需求：查询所有员工信息。
-- 显示员工姓名,员工工资,职务名称,工资等级,并按工资升序排序
-- 具体操作：
-- 1)确定要查询哪些表,emp e, job j, salarygrade s
-- 2)确定表连接条件 e.job_id=j.id AND e.salary BETWEEN s.losalary AND s.hisalary
SELECT * FROM emp e INNER JOIN job j INNER JOIN salarygrade s
 ON e.`job_id`=j.`id` AND e.`salary` BETWEEN s.`losalary`AND s.`hisalary`;

-- 3)确定查询字段：员工姓名,员工工资,工资等级,并按工资升序排序
SELECT  e.`ename` 员工姓名,e.`salary` 员工工资,s.`grade` 工资等级
FROM emp e INNER JOIN job j INNER JOIN salarygrade s
 ON e.`job_id`=j.`id` AND e.`salary` BETWEEN s.`losalary`AND s.`hisalary`
 ORDER BY e.`salary` ASC;


-- 练习8
-- 需求：
-- 列出所有员工的姓名及其直接上级的姓名,没有领导的员工也需要显示
-- 具体操作：
-- 1)确定要查询哪些表,emp e, emp m
-- 2)确定表连接条件 e.mgr=m.id
-- 发现员工表中少了一条数据,因为罗贯中是董事长没有上司,没有领导的员工也需要显示,所以左表的数据需要全部显示。应该改成左外连接。
SELECT *FROM emp e LEFT JOIN emp m ON e.`mgr`=m.`id`;

-- 3)确定查询字段：员工的姓名及其直接上级的姓名
-- 注：IFNULL函数的作用是,如果前面的参数有值,则显示原有的值,如果没有值,则显示后面的参数
SELECT  e.`ename` 姓名,IFNULL(m.ename,'没有上级') 上级
FROM emp e LEFT JOIN emp m ON e.`mgr`=m.`id`;

-- 练习9
-- 需求：
-- 查询入职时间早于直接上级的所有员工编号,姓名,入职日期,上司入职时间,部门名称
-- 具体操作：
-- 1)确定要查询哪些表：emp e, emp m, dept d
-- 2)确定表连接条件 e.mgr=m.id AND e.dept_id=d.id AND e.joindate<m.joindate
SELECT * FROM emp e INNER JOIN emp m INNER JOIN dept d ON e.`mgr`=m.`id`AND e.`dept_id` =d.`id` AND e.`joindate`<m.`joindate`;

-- 3)确定查询字段：员工编号,姓名,部门名称,入职时间,上司入职时间
SELECT e.`id` 员工编号,e.`ename` 姓名,d.`dname` 部门名称,e.`joindate` 入职时间,m.`joindate` 上司入职时间 
FROM emp e INNER JOIN emp m INNER JOIN dept d ON e.`mgr`=m.`id`AND e.`dept_id` =d.`id` AND e.`joindate`<m.`joindate`;



-- 练习10
-- 需求：
-- 查询工资高于公司平均工资的所有员工列：显示员工信息,部门名称,上级领导,工资等级
-- 具体操作：
-- 1)本例要使用子查询,先统计公司平均工资
SELECT AVG(`salary`) FROM emp ;
-- 2)确定要查询哪些表：emp e, emp m, dept d, salarygrade s
-- 3)确定表连接条件 e.dept_id=d.id AND e.mgr=m.id AND e.salary BETWEEN s.losalary AND hisalary AND e.salary>公司平均薪金
SELECT * FROM emp e INNER JOIN emp m INNER JOIN dept d INNER JOIN salarygrade s 
ON e.`dept_id`=d.`id` AND e.`mgr`=m.`id` AND e.`salary` BETWEEN s.`losalary` AND s.`hisalary` 
WHERE e.`salary`>(SELECT AVG(`salary`) FROM emp);

-- 4)确定查询字段：员工所有信息,部门名称,上级领导,工资等级。
SELECT e.`id` 员工编号,e.`ename` 姓名,d.`dname` 部门名称,m.`ename` 上级领导,s.`grade` 工资等级
FROM emp e INNER JOIN emp m INNER JOIN dept d INNER JOIN salarygrade s 
ON e.`dept_id`=d.`id` AND e.`mgr`=m.`id` AND e.`salary` BETWEEN s.`losalary` AND s.`hisalary` 
WHERE e.`salary`>(SELECT AVG(`salary`) FROM emp);