7、多表查询

七、多表查询

现实生活中，我们遇到的信息基本都是复合得信息，比如一个商品信息，不仅包含了手机的信息，还包括了一些评论等等，所以涉及到多表查询。

多表查询是指基于两个和两个以上的表查询.在实际应用中查询单个表可能不能满足你的需求，(如下面的课堂练习)，需要使用到(dept表和emp表)

-- 多表查询
-- 显示雇员名,雇员工资及所在部门的名字 【笛卡尔集】

SELECT * FROM emp, dept -- 图1
-- 如果直接使用SELECT COUNT(*) FROM emp, dept 得到的结果是52，就是emp的每一条信息与dept的每一条进行组合。如下图1，这样的结构显然不是我们想要的，所以我们还需要对信息进行过滤。

SELECT ename,sal,dname,emp.deptno -- 图2
	FROM emp, dept 
	WHERE emp.deptno = dept.deptno  -- 只筛选部门一样的信息，这样得到的结果就是我们想要的
	
-- 小技巧：多表查询的条件不能少于 表的个数-1, 否则会出现笛卡尔集 两个表至少需要1个过滤条件，三个表需要2个...
-- ?如何显示部门号为10的部门名、员工名和工资 
SELECT ename,sal,dname,emp.deptno
	FROM emp, dept 
	WHERE emp.deptno = dept.deptno and emp.deptno = 10

-- ?显示各个员工的姓名，工资，及其工资的级别
-- 思路 姓名，工资 来自 emp 13
--      工资级别 salgrade 5
-- 写sql , 先写一个简单，然后加入过滤条件...
select ename, sal, grade 
	from emp , salgrade
	where sal between losal and hisal; 

自连接

我们上面遇到的多表查询，是将很多不同的表，连接起来组成一张新的表。自连接就是将本表进行多次连接。什么时候会用到呢，我们来理解一下下面这个案例，还是用到之前的emp表

我们看这个表，每个员工信息都有一个mgr信息即是上级领导的编号，那我要是想不显示编号，直接显示领导的名称呢，是不是可以利用多表查询，只不过两个表是同一个表。

-- 多表查询的 自连接

-- 思考题: 显示公司员工名字和他的上级的名字

-- 员工和上级是通过 emp表的 mgr 列关联
-- 自连接的特点 1. 把同一张表当做两张表使用
--             2. 需要给表取别名 表名  表别名 
--             3. 列名不明确，可以指定列的别名 列名 as 列的别名	添加别名可以直接在后面写上别名即可	
SELECT worker.ename AS '职员名' ,  boss.ename AS '上级名'
	FROM emp worker, emp boss
	WHERE worker.mgr = boss.empno;
SELECT * FROM emp;

子表查询

子查询是指嵌入在其它 sql 语句中的 select 语句,也叫嵌套；接下来直接理解案例。

-- 子查询的演示
-- 请思考：如何显示与SMITH同一部门的所有员工?
/*
	1. 先查询到 SMITH的部门号得到
	2. 把上面的select 语句当做一个子查询来使用
*/
SELECT deptno -- 先查到SMITH的部门号
	FROM emp 
	WHERE ename = 'SMITH'

-- 下面的答案.	
SELECT * 
	FROM emp
	WHERE deptno = (
		SELECT deptno 
		FROM emp 
		WHERE ename = 'SMITH'
	)
/*
上面这个案例就是子表查询
查询与SMITH同一部门的所有员工
首先要得到SMITH的部门号
	SELECT deptno 
		FROM emp 
		WHERE ename = 'SMITH'
	即使得到部门号
在利用得到的部门号，查询所有员工
	SELECT * 
	FROM emp
	WHERE deptno = (
		部门号；
	)
*/

-- 课堂练习:如何查询和部门10的工作相同的雇员的
-- 名字、岗位、工资、部门号, 但是不含10号部门自己的雇员.

/*
	1. 查询到10号部门有哪些工作
	2. 把上面查询的结果当做子查询使用
*/
select distinct job  -- 先查到10号部门有哪些工作   去重
	from emp 
	where deptno = 10;
	
--  下面语句完整

select ename, job, sal, deptno
	from emp
	where job in (      -- 是一个集合
		SELECT DISTINCT job 
		FROM emp 
		WHERE deptno = 10
	) and deptno <> 10 -- 不等于的两种表达 !=  <>

临时表

直接理解一个案例，还是利用上面用到的emp表

-- 请思考：查找每个部门工资高于本部门平均工资的人的资料
-- 这里要用到数据查询的小技巧，把一个子查询当作一个临时表使用

-- 1. 先得到每个部门的 部门号和 对应的平均工资

SELECT deptno, AVG(sal) AS avg_sal  -- 我们应该把emp表和我们整理的这个新的表进行组合筛选
	FROM emp GROUP BY deptno		-- 图一  假设这个表的名字叫做temp，那接下来该怎么做呢
/*
SELECT ename, sal, temp.avg_sal, emp.deptno
	FROM emp, temp 
	where emp.deptno = temp.deptno and emp.sal > temp.avg_sal
*/

-- 所以我们应该先做一个新的临时表，再利用这个临时表temp和emp进行多表查询   格式如下
-- 2. 把上面的结果当做子查询, 和 emp 进行多表查询
SELECT ename, sal, temp.avg_sal, emp.deptno  -- temp.avg_sal表示temp表中的avg_sal这一列
	FROM emp, (
		SELECT deptno, AVG(sal) AS avg_sal
		FROM emp 
		GROUP BY deptno
	) temp 
	where emp.deptno = temp.deptno and emp.sal > temp.avg_sal
	
-- 查找每个部门工资最高的人的详细资料

SELECT ename, sal, temp.max_sal, emp.deptno
	FROM emp, (
		SELECT deptno, max(sal) AS max_sal
		FROM emp 
		GROUP BY deptno
	) temp 
	WHERE emp.deptno = temp.deptno AND emp.sal = temp.max_sal
	

-- 查询每个部门的信息(包括：部门名,编号,地址)和人员数量

-- 1. 部门名,编号,地址 来自 dept表
-- 2. 各个部门的人员数量 -》 构建一个临时表

select count(*), deptno 
	from emp
	group by deptno;
	

select dname, dept.deptno, loc , tmp.per_num as '人数'
	from dept, (
		SELECT COUNT(*) as per_num, deptno 
		FROM emp
		GROUP BY deptno
	) tmp 
	where tmp.deptno = dept.deptno

-- 还有一种写法 表.* 表示将该表所有列都显示出来, 可以简化sql语句
-- 在多表查询中，当多个表的列不重复时，才可以直接写列名

SELECT tmp.* , dname, loc
	FROM dept, (
		SELECT COUNT(*) AS per_num, deptno 
		FROM emp
		GROUP BY deptno
	) tmp 
	WHERE tmp.deptno = dept.deptno

常用关键字

all

any(some)

exists / not exists

union

-- all 和 any的使用
-- some 和any 相同，这里以any举例

-- 请思考:显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

SELECT ename, sal, deptno
	FROM emp
	WHERE sal > ALL(  -- 比所有都高
		SELECT sal 
			FROM emp
			WHERE deptno = 30
		) 
-- 也可以这样写
SELECT ename, sal, deptno
	FROM emp
	WHERE sal > (
		SELECT MAX(sal) 
			FROM emp
			WHERE deptno = 30
		) 

-- 请思考:如何显示工资比部门30的其中一个员工的工资高的员工的姓名、工资和部门号

SELECT ename, sal, deptno
	FROM emp
	WHERE sal > any(   -- 比一个高
		SELECT sal 
			FROM emp
			WHERE deptno = 30
		)
-- 同理与
 SELECT ename, sal, deptno
	FROM emp
	WHERE sal > (
		SELECT min(sal) 
			FROM emp
			WHERE deptno = 30
		)
		
-- exists
CREATE table test1(num INT);
INSERT INTO test1 VALUES(1),(2);
CREATE table test2(num INT);
INSERT INTO test2 VALUES(3),(4);

SELECT * FROM test1 WHERE EXISTS(SELECT * FROM test2 WHERE num>1);  -- 执行前面的查询语句，相当于 SELECT * FROM test1
SELECT * FROM test1 WHERE EXISTS(SELECT * FROM test2 WHERE num>4); -- 不执行前面的查询语句，返回空
SELECT * FROM test1 WHERE NOT EXISTS(SELECT * FROM test2 WHERE num>4);  -- 取反
-- 即exists后面是true，就执行前面的语句，为false就不执行前面的语句；exists后面的查询语句没查到信息，就相当于返回false
-- exists可以省略，但不建议


-- union  两个查询结果合并，前提是字段相同
SELECT * FROM test1
union           -- 去重
SELECT * FROM test2;

SELECT * FROM test1  
union all       -- 不去重
SELECT * FROM test2

多列子查询

-- 多列子查询

-- 请思考如何查询与allen的部门和岗位完全相同的所有雇员(并且不含allen本人)
-- (字段1， 字段2 ...) = (select 字段 1，字段2 from 。。。。)
-- 每个字段要对应相等才算满足要求

-- 分析: 1. 得到smith的部门和岗位

SELECT deptno , job
	FROM emp 
	WHERE ename = 'ALLEN'
	
-- 分析: 2  把上面的查询当做子查询来使用，并且使用多列子查询的语法进行匹配
SELECT * 
	FROM emp
	WHERE (deptno , job) = (
		SELECT deptno , job
		FROM emp 
		WHERE ename = 'ALLEN'
	) AND ename != 'ALLEN'


-- 请查询 和宋江数学，英语，语文   
-- 成绩 完全相同的学生
SELECT * 
	FROM student
	WHERE (math, english, chinese) = (
		SELECT math, english, chinese
		FROM student
		WHERE `name` = '宋江'
	)

SELECT * FROM student;

表复制

-- 表的复制
-- 为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据

CREATE TABLE my_tab01 
	( id INT,
	  `name` VARCHAR(32),
	  sal DOUBLE,
	  job VARCHAR(32),
	  deptno INT);
DESC my_tab01
SELECT * FROM my_tab01;

-- 演示如何自我复制
-- 1. 先把emp 表的记录复制到 my_tab01
INSERT INTO my_tab01 
	(id, `name`, sal, job,deptno)
	SELECT empno, ename, sal, job, deptno FROM emp;
	
-- 2. 自我复制  数量变成了原来的2倍
INSERT INTO my_tab01
	SELECT * FROM my_tab01; 
	
SELECT COUNT(*) FROM my_tab01;

-- 如何删除掉一张表重复记录
-- 1. 先创建一张表 my_tab02, 
-- 2. 让 my_tab02 有重复的记录

-- 复制表结构
CREATE TABLE my_tab02 LIKE emp; -- 这个语句 把emp表的结构(列)，复制到my_tab02

desc my_tab02;

insert into my_tab02
	select * from emp;
	
select * from my_tab02;
-- 3. 考虑去重 my_tab02的记录  
/*
	思路 
	(1) 先创建一张临时表 my_tmp , 该表的结构和 my_tab02一样
	(2) 把my_tmp 的记录 通过 distinct 关键字 处理后 把记录复制到 my_tmp
	(3) 清除掉 my_tab02 记录
	(4) 把 my_tmp 表的记录复制到 my_tab02
	(5) drop 掉 临时表my_tmp
*/
-- (1) 先创建一张临时表 my_tmp , 该表的结构和 my_tab02一样

create table my_tmp like my_tab02
-- (2) 把my_tmp 的记录 通过 distinct 关键字 处理后 把记录复制到 my_tmp
insert into my_tmp 
	select distinct * from my_tab02;

-- (3) 清除掉 my_tab02 记录
delete from my_tab02;
-- (4) 把 my_tmp 表的记录复制到 my_tab02
insert into my_tab02
	select * from my_tmp;
-- (5) drop 掉 临时表my_tmp
drop table my_tmp;
-- 就相当于a = 1; b = 2 现在要求交换值一样

合并查询

-- 合并查询

SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5

SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3

-- union all 就是将两个查询结果合并，不会去重
SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5
UNION ALL
SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3

-- union  就是将两个查询结果合并，会去重
SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5
UNION 
SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3