8.多表查询

1.内连接引入:
实际开发中往往需要针对两张甚至更多张数据表进行操作,而这多张表之间需要主键和外键关联在一起,
然后使用连接查询多张表中满足要求的数据记录

一条SQL语句查询多个表,得到一个结果,包含多个表的数据,效率高
在SQL99中,连接查询需要使用JOIN关键字实现

提供了多种连接查询的类型:CROSS  NATURAL USING ON

交叉连接(CROSS JOIN)是对两个或者多个表进行笛卡儿积操作,所谓笛卡儿积就是关系代数里的一个概念,表示两个表中的每一行数据任意组合的结果.比如:有两个表,左表有m条数据记录,X个字段,右表有n条数据记录,Y个字段,则执行交叉连接后将返回m*n条数据记录,X+Y个字段

-- 查询员工的编号,姓名,部门编号,部门名称:
SELECT * FROM emp; -- 14条记录
SELECT * FROM dept; -- 4条记录

-- 多表查询:
-- 交叉连接:cross join
SELECT * FROM emp CROSS JOIN dept; -- 14*4 = 56 条 笛卡儿积乘积:没有实际意义,有理论意义

SELECT * FROM emp JOIN dept ; -- cross 可以省略不写,mysql中可以,oracle中不可以

-- 自然连接:natural join
-- 优点:自动匹配所有的同名列,同名列只展示一次,简单
SELECT * FROM emp NATURAL JOIN dept;

SELECT empno,ename,sal,dname,loc FROM emp
NATURAL JOIN dept;

-- 缺点:查询字段的时候,没有指定字段所属的数据库表,效率低
-- 解决:制定表名
SELECT emp.empno,emp.ename,emp.sal,dept.dname,dept.loc,dept.deptno
FROM emp
NATURAL JOIN dept;

-- 缺点:表名太长
-- 解决:表起别名
SELECT e.empno,e.ename,e.sal,d.dname,d.loc,d.deptno
FROM emp e
NATURAL JOIN dept d;

-- 自然连接: natural join 缺点:自动匹配表中所有的同名列,但是有时候我们希望只匹配部分同名列
-- 解决: using 子句
SELECT * FROM emp e
INNER JOIN dept d -- inner可以忽略不写
USING(deptno); -- 这里不能写natural join了,这里是内连接

 -- using 缺点:关联的字段,必须是同名的
 -- 解决:内连接 - on 子句
 SELECT * FROM emp e
 INNER JOIN dept d
 ON (e.deptno = d.deptno);
 
 -- 多表连接查询的类型: 1.交叉连接 cross join  2. 自然连接 natural join 3.内连接 -using 子句
 -- 4.内连接 - on 子句
 -- 综合看:内连接 -on 子句
 SELECT * FROM emp e INNER JOIN dept d 
 ON (e.deptno = d.deptno) WHERE sal > 2000;
 -- 条件:
 -- 1.筛选条件: where 	having
 -- 2.连接条件: on, using , natural

-- inner join - on子句:显示的是所有匹配的信息
SELECT * FROM emp e
INNER JOIN dept d ON e.deptno = d.deptno;

-- 问题:
-- 部分没有员工,没有显示在查询结果中
-- 员工scoot没有部门,没有显示在查询结果中

-- 外连接:除了显示匹配的数据之外,还可以显示不匹配的数据

-- 左外连接: left outer join -- 左面的那个表的信息,即使不匹配也可以查看出效果
SELECT * FROM emp e
LEFT OUTER JOIN dept d
ON e.deptno = d.deptno;

-- 右外连接: left outer join -- 右面的那个表的信息,即使不匹配也可以查看出效果
SELECT * FROM emp e
RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno;

-- 全外连接 full outer join -- 这个语法在MySQL中不支持,在Oracle支持 -- 展示左,右表全部不匹配额数据
-- scoot,40 号部门都可以看到
SELECT * FROM emp e
FULL OUTER JOIN dept d
ON e.deptno = d.dpetno;

-- 解决mysql 中不支持全外连接的问题
-- outer 可以省略不写
SELECT * FROM emp e
LEFT OUTER JOIN dept d
ON e.deptno = d.deptno
UNION -- 并集 去重 效率低
SELECT * FROM emp e
RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno;

SELECT * FROM emp e
LEFT OUTER JOIN dept d
ON e.deptno = d.deptno
UNION ALL -- 并集 不去重 效率高
SELECT * FROM emp e
RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno;

-- mysql中对集合操作支持比较弱,只支持并集操作,交集,差集不支持(Oracle中支持)

-- 查询员工的编号,姓名,薪水,部门编号,部门名称,薪水等级
SELECT * FROM emp;
SELECT * FROM dept;
select * FROM salgrade;

SELECT e.ename,e.sal,e.empno,e.deptno,d.dname,s.*
FROM emp e
RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno
INNER JOIN salgrade s
ON e.sal BETWEEN s.losal AND s.hisal;

自连接

-- 查询员工的编号,姓名,上级编号,上级的姓名
SELECT * FROM emp;
SELECT e1.empno 员工编号,e1.ename 员工姓名,e1.mgr 领导编号,e2.ename 员工领导姓名
FROM emp e1
INNER JOIN emp e2
ON e1.mgr = e2.empno;

-- 左外连接
SELECT e1.empno 员工编号,e1.ename 员工姓名,e1.mgr 领导编号,e2.ename 员工领导姓名
FROM emp e1
LEFT OUTER JOIN emp e2
ON e1.mgr = e2.empno;

92语法多表查询 

-- 92语法的多表查询
-- 查询员工的编号,员工姓名,薪水,员工部门编号,部门名称
SELECT e.empno,e.ename,e.sal,e.deptno,d.dname
FROM emp e,dept d
-- 相当于99语法中的cross join,出现笛卡儿积,没有意义
SELECT e.empno,e.ename,e.sal,e.deptno,d.dname
FROM emp e,dept d
WHERE e.deptno = d.deptno;
-- 相当于99语法中的natural join

-- 查询员工的编号,员工姓名,薪水,员工部门编号,部门名称,查询出工资大于2000的员工
SELECT e.empno,e.ename,e.sal,e.deptno,d.dname
FROM emp e,dept d
WHERE e.deptno = d.deptno AND e.sal > 2000;


-- 查询员工的名字,岗位,上级编号,上级名称(自连接):
SELECT e1.ename,e1.job,e1.mgr,e2.ename
FROM emp e1,emp e2
WHERE e1.mgr = e2.empno;

-- 查询员工的编号,姓名,薪水,部门编号,部门名称,薪水等级
SELECT e.empno,e.ename,e.sal,e.deptno,d.dname,s.grade
FROM emp e,dept d,salgrade s
WHERE e.deptno = d.deptno AND e.sal >= s.losal AND e.sal <= s.hisal;

-- 总结:
-- 1.92语法麻烦
-- 2.92语法中 表的连接条件 和 筛选条件 是放在一起的没有分开
-- 3.99语法提供了更多的查询连接类型:cross , natural ,inner, outer