#多表查询  772
-- ?显示雇员名,雇员工资及所在部门的名字[ 笛卡尔集]
/*
分析
1. 雇员名,雇员工资 来自 emp 表
2. 部门的名字 来自 dept 表
3. 需求对 emp 和 dept 查询 ename,sal,dname, emp.deptno
4. 当我们需要指定显示某个表的列是，需要 表.列表
*/
SELECT ename,sal,dname,emp.deptno
	FROM emp,dept
	WHERE emp.deptno = dept.deptno
	
SELECT * FROM emp
SELECT * FROM dept
-- 小技巧:多表查询的条件不能少于表的个数-1,否则会出现笛卡尔集
-- ?如何显示部门号为10的部门名、员工名和工资   773
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

-- 练习:显示雇员名,雇员工资及所在部门的名字,并按部门排序[降序排].
SELECT ename,sal,emp.deptno
	FROM emp,dept
	WHERE emp.deptno = dept.deptno
	ORDER BY emp.deptno DESC

#多表查询的自连接  774
-- 显示公司员工和他的.上级的名字
-- 分析： 员工名字 在 emp, 上级的名字的名字 emp
-- 员工和上级是通过 emp 表的 mgr 列关联
-- 这里小结：
-- 自连接的特点 
-- 1. 把同一张表当做两张表使用
-- 2. 需要给表取别名 表名 表别名
-- 3. 列名不明确，可以指定列的别名 列名 as 列的别名
SELECT worker.ename AS '职员名',boss.ename AS '上级名'
	FROM emp worker,emp boss
	WHERE worker.mgr = boss.empno#将员工对应的上级编号和自己的编号empno相等作为过滤条件
SELECT * FROM emp

# 子查询的演示  775
-- 请思考：如何显示与 SMITH 同一部门的所有员工?（单行子查询）
/*
1. 先查询到 SMITH 的部门号得到
SELECT deptno
	FROM emp
	WHERE ename = 'SMITH'
2. 把上面的 select 语句当做一个子查询来使用
*/
SELECT deptno
	FROM emp
	WHERE ename = 'SMITH'#这个是得到smith部门的编号
#所以答案是（单行子查询）
SELECT * 
	FROM emp
	WHERE deptno = (
		SELECT deptno
		FROM emp
		WHERE ename = 'SMITH'
	)

-- 如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号，
-- 但是不含10号部门自己的雇员（多行子查询）
/*
1. 查询到 10 号部门有哪些工作
2. 把上面查询的结果当做子查询使用
*/
SELECT DISTINCT job
	FROM emp
	WHERE deptno = 10#这是10号部门的工作岗位
	
-- 下面是完整语句
SELECT ename,job,sal,deptno
	FROM emp
	WHERE job IN(
		SELECT DISTINCT job
		FROM emp
		WHERE deptno = 10
	) AND deptno != 10

-- 子查询当做临时表使用  776
-- 查询ecshop中各个类别中，价格最高的商品（记住要切换到eshop数据库中）
-- 思路：先得到各个类别中，价格最高的商品max+group by cat_id,当作临时表
SELECT cat_id,MAX(shop_price)
	FROM ecs_goods
	GROUP BY cat_id#当作临时表
-- 最后答案
SELECT goods_id,ecs_goods.cat_id,goods_name,shop_price
	FROM(
		SELECT cat_id,MAX(shop_price) AS max_price
		FROM ecs_goods
		GROUP BY cat_id
	)temp,ecs_goods
	WHERE temp.cat_id = ecs_goods.cat_id 
	AND temp.max_price = ecs_goods.shop_price

#演示all和any的使用  777
-- 请思考:显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
SELECT ename,sal,deptno
	FROM emp
	WHERE sal > ALL(
		SELECT sal
		FROM emp
		WHERE deptno = 30#这里是查询出30号部门所有的工资
	)
-- 还可以这样写
SELECT ename,sal,deptno
	FROM emp
	WHERE sal > (
		SELECT MAX(sal)
		FROM emp
		WHERE deptno = 30#这里是查询出30号部门最高的工资
	)

-- 请思考:如何显示工资比部门30的其中一个员工的工资高的员工的姓名、工资和部门号  777
SELECT ename,sal,deptno
	FROM emp
	WHERE sal > ANY(
		SELECT sal
		FROM emp
		WHERE deptno = 30#这里是查询出30号部门所有的工资
	)
-- 还可以这样写
SELECT ename,sal,deptno
	FROM emp
	WHERE sal > (
		SELECT MIN(sal)
		FROM emp
		WHERE deptno = 30#这里是查询出30号部门最低的工资
	)

-- 多列子查询  778
-- 请思考如何查询与allen的部门和岗位完全相同的所有雇员(并且不含allen本人)
-- (字段1,字段2 .. )=(select字段1,字段2 from。。。。)
-- 分析: 1. 得到 allen 的部门和岗位
SELECT deptno,job
	FROM emp
	WHERE ename = 'ALLEN'
-- 分析: 2 把上面的查询当做子查询来使用，并且使用多列子查询的语法进行匹配
SELECT *
	FROM emp
	WHERE (deptno,job)=(
		SELECT deptno,job#因为要求部门和岗位完全相同，所以就将deptno,job和依据allen查询出来的
		FROM emp		#部门和岗位对应匹配相等作为筛选条件
		WHERE ename = 'ALLEN'
	)AND ename !='ALLEN'
	
-- 请查询和宋江数学，英语，语文成绩完全相同的学生自己写写
SELECT *
	FROM student
	WHERE (math,english,chinese)=(
		SELECT math,english,chinese
			FROM student
			WHERE `name`='宋江'
	)

-- 子查询练习   779
-- 在 from 子句中使用子查询
-- 查找每个部门工资高于本部门平均工资的人的资料这里要用到数据查询的小技巧
-- ，把一个子查询当作一个临时表使用
-- 1. 先得到每个部门的 部门号和 对应的平均工资
SELECT deptno,AVG(sal) AS avg_sal
	FROM emp
	GROUP BY deptno
-- 2. 把上面的结果当做子查询, 和 emp 进行多表查询
SELECT ename,sal,temp.avg_sal,emp.deptno
	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.先将人员信息和部门信息关联显示
-- 2.然后统计
-- 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

#表的复制  780
-- 有时，为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据。
CREATE TABLE my_tab01
	( id INT,
	`name` VARCHAR(32),
	sal DOUBLE,
	job VARCHAR(32),
	deptno INT);
DESC my_tab01  #DESC是显示表的字段功能
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.自我复制
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#将emp表的数据复制进来

SELECT * FROM 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_tab02 的记录 通过 distinct 关键字 处理后 把记录复制到 my_tmp
INSERT INTO my_tmp
	SELECT DISTINCT * FROM my_tab02
SELECT * FROM my_tmp
-- (3) 清除掉 my_tab02 记录
DELETE FROM my_tab02
SELECT * FROM my_tab02
-- (4) 把 my_tmp 表的记录复制到 my_tab02
INSERT INTO my_tab02
	SELECT * FROM my_tmp
-- (5) drop 掉 临时表 my_tmp
DROP TABLE my_tmp
SELECT * FROM my_tab02

#合并查询  781
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
UNION ALL
SELECT ename,sal,job 
	FROM emp 
	WHERE job= 'MANAGER'; -- 一共右8条记录
	
-- union 就是将两个查询结果合并，会去重
SELECT ename,sal,job 
	FROM emp 
	WHERE sal>2500
UNION
SELECT ename,sal,job 
	FROM emp 
	WHERE job= 'MANAGER';  -- 一共右6条记录