多表查询 笔记

目录

多表查询

 自连接

子查询

（单行子查询）

多行子查询

子查询临时表

 all和any

多列子查询

表复制 

合并查询

 表外连接查询

---

多表查询

 解决多表查询的关键就是过滤出要显示的列 where语句进行过滤

举例

-- 显示雇员名，雇员工资和所在部门的名字 

-- 当需要指定显示某个表的列是，需要 表.列名
SELECT ename,sal,dname,dept.deptno
		FROM emp, dept
WHERE emp.deptno = dept.deptno

-- 显示部门号为10的部门名，员工名，和工资 

SELECT dname,ename,sal,dept.deptno
FROM dept,emp
where emp.deptno = dept.deptno and dept.deptno = 10

 -- 显示各个员工的姓名，工资和工资的级别

SELECT ename,sal,grade
FROM emp,salgrade
WHERE sal BETWEEN losal AND hisal

 自连接

自连接是指在同一张表的连接查询【将一张表看做两张表】

 思考：显示公司员工和他的上级名字

-- 自连接 要给表取别名
-- 显示员工名字和他的上级 把emp表当两个表使用（）

SELECT worker.ename 职员名,boss.ename boss名 
FROM emp worker,emp boss
WHERE worker.mgr = boss.empno

子查询

 

（单行子查询）

-- 如何显示与smith同一个部门的所有员工 

 --         1.先得到与smith的部门

SELECT deptno 
FROM emp
WHERE ename = 'SMITH'

--    2.把上面的sql语句当作一个子查询来使用

SELECT * FROM emp
WHERE deptno = (SELECT deptno 
FROM emp
WHERE ename = 'SMITH')

多行子查询

-- 如何查询和部门10的工作相同的雇员的 名字，岗位，工资，部门号，但不包括10自己的

SELECT ename,job,sal,deptno
FROM emp
WHERE job in (SELECT job FROM emp
WHERE deptno = 10) AND deptno != 10 

子查询临时表

-- 子查询临时表
-- 查询emp表中各个部门工资最高的员工
-- 1.先求出各个部门的最高工资,把他当成一张临时表 max_sal
SELECT deptno,MAX(sal) max FROM emp 
GROUP BY deptno
-- 员工信息
SELECT ename,job,sal,emp.deptno
FROM(SELECT deptno,MAX(sal) max FROM emp 
GROUP BY deptno) max_sal,emp
where max_sal.deptno = emp.deptno AND max_sal.max = emp.sal

 all和any

-- all和any
-- 显示工资比部门30所有员工工资都要高的员工信息
-- 1.部门30工资最高的人
SELECT MAX(sal) FROM emp WHERE deptno = 30
-- 方法1，子查询
SELECT ename,job,sal,deptno FROM emp
WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30)
-- 方法2 all
SELECT ename,job,sal,deptno FROM emp
where sal>ALL(SELECT sal from emp WHERE deptno = 30)
-- 显示工资比部门30的其中一个员工工资高的人
-- any方法，或者用用工资>30部门工资最低的那个员工
SELECT ename,job,sal,deptno FROM emp 
WHERE sal >ANY(SELECT sal FROM emp WHERE deptno = 30)

多列子查询

 -- 得到与smith的部门和岗位完全相同的所有雇员信息，包括smith自己

-- 多列子查询
-- 1.得到smith的部门和岗位
SELECT deptno ,job FROM emp
WHERE ename = 'SMITH'
-- 2.把上面的语句单子查询语句并使用多列查询
SELECT * FROM emp
WHERE (deptno,job) = (SELECT deptno ,job FROM emp
WHERE ename = 'SMITH')

表复制 

 演示如何自我复制表

-- 1.先创建一个空表
CREATE TABLE mytable(
					id INT,
					`name` VARCHAR(20),
					`sal` INT,
					`job` VARCHAR(10),
					deptno int)
-- 将emp表的数据复制过来 使用insert语句
INSERT INTO mytable
(id,`name`,sal,job,deptno)
SELECT empno,ename,sal,job,deptno FROM emp
-- 查询mytable
SELECT * FROM mytable

演示如何删除表中的重复数据

-- 删除mytable表中的重复记录
-- 1.先创建一个临时表 tmp该表的结构和mytable一样
CREATE TABLE tmp LIKE mytable
-- 2.mytable 的记录，通过distinct关键字 处理后 ，把记录复制到tmp
INSERT INTO tmp 
SELECT DISTINCT * FROM mytable
-- 3.清楚mytable表中的数据
DELETE FROM mytable
-- 4.把临时表的数据复制到mytable中，并删除临时表
INSERT INTO mytable
SELECT * FROM tmp;
DROP TABLE tmp

合并查询

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

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

 

如果要将两个查询结果合并起来 可以通过union all或者union

union all 不会去重

 union会去重

 

 表外连接查询

-- 列出部门名称和这些部门的员工名称和工资，同时要求显示出那些没有员工的部门

用之前多表查询的sql 通过两表的deptno进行连接效果如下

 因为编号为40的部门没有与员工，所有没显示，这不能解决问题，故应使用表外连接

练习

-- 左外连接
SELECT dname ,ename,job
FROM dept LEFT JOIN emp
ON dept.deptno = emp.deptno;
-- 右外连接
SELECT dname ,ename,job
FROM emp RIGHT JOIN dept
ON dept.deptno = emp.deptno;

 

 本质上左右连接都是一样的，要确认是哪张表没有的需要显示，然后根据位置在左还是在右选择左右连接