七、多表连接查询|七、多表连接查询 2019-10-31 七、多表连接查询2019-10-31

为什么要进行多表链接？
需要查询的数据分布在多张表中。
什么是连接？
1）连接就是在多给奥之间通过一定的连接条件，使表之间发生关联，进而能从多个表中获取数据。
2）语法：
select *
from table1,table 2
where table1.colume1=table2.colume2
3）如果在多个表中出现相同的列名，则需要使用表名作为列明前缀。
4）可以为表名起一个别名
语法：表名表别名或表名 as 表别名
注意：在表链接中，一旦给表起了别名，那么在当前的查询中，不能再使用原来的表名称了。
连接的类型：
1）按照连接的类型分：等值连接和非等值连接。
2）按照其他连接方法分：外部连接和内部连接。
非等值连接案列
-- 查询每个员工的姓名、工资、工资等级。

select ename,sal,grade,losal,hisal from emp e,salgrade s where e.sal between s.losal AND s.hisal

笛卡尔积：第一个表中的所有行与第二张表中的所有行都发生连接（两个表连接，不写连接条件）
案例：

select * from emp e,dept d

5, 自连接：是一个表通过某种条件和本身进行连接的一种方式，就如同多个表连接一样。
案例：
查询员工姓名，员工编号，经理姓名和经理编号

select e.ENAME,e.empno,m.ename,e.mgr from emp e,emp m where e.mgr = m.empno.

练习：
①写一个查询，显示所有员工姓名，部门编号，部门名称。

SELECT ename,e.deptno,dname from emp e,dept d where e.deptno = d.deptno

②写一个查询，显示所有工作在CHICAGO并且奖金不为空的员工姓名，工作地点，奖金

SELECT ename,loc,COMM from emp e,dept d where e.deptno = d.deptno AND loc = 'chicago' AND COMM is NOT NULL

③写一个查询，显示所有姓名中含有A字符的员工姓名，工作地点。

SELECT ENAME,LOC FROM emp e,dept d WHERE e.deptno = d.deptno AND ename LIKE '%A%'

【七、多表连接查询|七、多表连接查询 2019-10-31】④查询每个员工的编号，姓名，工资，工资等级，所在工作城市，按照工资等级进行升序排序。

SELECT EMPNO,ename,e.sal,grade,LOC FROM emp e,dept d,salgrade s WHERE e.deptno = d.deptno AND e.sal between s.losal AND s.hisal order by grade ASC

⑤查询所有工作在NEW YORK和CHICAGO的员工姓名，员工编号，以及他们的经理姓名，经理编号。

SELECT e.ename,e.empno,m.ename,m.empno FROM emp e,emp m,dept d WHERE e.deptno = d.deptno and e.mgr = m.empno AND loc IN ('new york','chicago')

ANSI SQL:1999 标准（ANSI：美国国家标准协会）
1）内连接（内部连接）：根据连接条件，在两张表都出现的记录会显示在结果集合中。
案例：

SELECT * FROM emp e,dept d where e.DEPTNO = d.DEPTNOSELECT * FROM emp e INNER JOIN dept d ON e.DEPTNO = d.DEPTNO

2）左外连接：左外连接以from子句中左边的表为基表（主表），该表中所有行数据按照链接条件无论是否与右边的表能否匹配上，都会显示出来。

SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.DEPTNO = d.DEPTNO

2）右外连接：右外连接以from子句中右边的表为基表（主表），该表中所有行数据按照链接条件无论是否与左边的表能否匹配上，都会显示出来。

SELECT * FROM emp eRIGHT OUTER JOIN dept d ON e.DEPTNO = d.DEPTNO

4）全外连接：（mysql不支持）FULL OUTER JOIN
5）自然连接：是对两个表相同名字额数据类型的列进行的等值连接。
语法：
SELECT 列名列表
FROM TABLE1 NATURAL JOIN TABLE2
注意：select后面的列明列表中，mysql中可以对名称相同的列家表别名。oracle不可以
6）using子句：自然连接是使用所有名称和数据类型相匹配的列作为连接条件，而using子句可以只能用某个或某几个相同名字和数据类型作为连接条件。
语法：
select 列名列表
from table1 join table2 using（列名列表）
注意：natural join 子句和 using 子句是互斥的，不能同时使用。
7）交叉连接：就是笛卡尔积。
语法：

select * from table1 cross join table2

练习：
①创建一个员工表和部门表的交叉连接。

SELECT * FROM emp CROSS JOIN dept

②使用自然连接，显示入职日期在80年5月1日之后的员工姓名，部门名称，入职日期。

SELECT ename,dname,hiredate FROM emp NATURAL JOIN dept

③使用USING子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点。

SELECT ename,dname,loc FROM emp JOIN dept USING (deptno) where loc = 'chicago'

④使用ON子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点，薪资等级。

SELECT ename,dname,loc,grade FROM emp e INNER JOIN dept d INNER JOIN salgrade s ON e.deptno = d.deptno AND e.sal between s.losal AND s.hisal WHERE loc = 'chicago'

⑤使用左连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。