第五阶段 -- 数据库：day20_07/02/19【多表查询；join on】

• 各种连接的区别：
  

• 多表查询：

  员工表：emp(empno, dal, ename, job, hiredate, deptno)

  部门表：dept(deptno, dname, loc)

-- 创建dept部门表
create table dept(
	deptno int(4) primary key,
	dname varchar(20),
	loc varchar(20)
);
-- 为dept部门表同时插入多条数据
insert into dept values
	(10, 'Accounting', 'New York'),
	(20, 'Research', 'Dallas'),
	(30, 'Sales', 'Chicago'),
	(40, 'Operations', 'Boston');
-- 查看部门表中的数据
select * from dept

-- 创建员工表
create table emp(
	empno int(4) primary key,
	ename varchar(10),
	job varchar(9),
	mgr int(4),
	hiredate date,
	sal decimal(7,2),
	comm decimal(7,2),
	deptno int(4),
	constraint fk_deptno foreign key(deptno) references dept(deptno)
);
-- 为emp员工表插入多条数据
insert into emp values
	(7369, 'Smith', 'clerk', 7902, '1980-12-17', 800, null, 20),
	(7499, 'Allen', 'salesman', 7698, '1981-02-20', 1600, 300, 30),
	(7521, 'Ward', 'salesman', 7698, '1981-02-22', 1250, 500, 30),
	(7566, 'Jones', 'manager', 7839, '1981-04-02', 2975, null, 20),
	(7654, 'Maritn', 'salesman', 7698, '1981-09-28', 1250, 1400, 30),
	(7698, 'Blake', 'manager', 7839, '1981-05-01', 2850, null, 30),
	(7782, 'Clark', 'manager', 7839, '1981-06-09', 2450, null, 10),
	(7788, 'Scott', 'analyst', 7566, '1987-04-19', 3000, null, 20),
	(7839, 'King', 'president', null, '1981-11-17', 5000, null, 10),
	(7844, 'Turner', 'salesman', 7698, '1981-09-08', 1500, 0, 30),
	(7876, 'Adams', 'clerk', 7788, '1987-05-23', 1100, null, 20),
	(7900, 'James', 'clerk', 7698, '1981-12-03', 950, null, 30),
	(7902, 'Ford', 'analyst', 7566, '1981-12-03', 3000, null, 20),
	(7934, 'Miller', 'clerk', 7782, '1982-01-23', 1300, null, 10);
-- 查看emp中的数据
select * from emp

• 为什么要建立连接 ？

1. 交叉连接（[cross] join）

1. 交叉连接指的是两张或者多张表进行的“笛卡尔积”，即两张或者多张表中每一行的数据任意组合的结果。
   

2. 语法：

   select 查询内容 from table1 join table2

   • 例如：emp和dept表进行交叉连接

select * from emp join dept
-- 有4*14=56条信息

• 特点：总记录条数 = table1记录条数 * table2记录条数

  ​ 总列数 = table1列数 + table2列数

2. 自然连接

1. 自然连接是关系表中相同名称的字段进行自动匹配产生的结果，自然连接会去除重复的列。

2. 语法：select 查询内容

   ​ from table1 natural join table2

• 例如：讲emp和dept进行自然连接

select * from emp natural join dept
-- 总记录条数是14条

• 注意：
  1. 关联的表中必须有相同名称的字段（字段名称相同，字段数据类型相同）。
  2. 查询结果中去掉重复的相同的字段。

3. 内连接

1. 内连接（INNER JOIN）是使用频率最高的连接查询操作，所谓内连接就是指在两张或
   多张表生成的笛卡儿积记录中筛选出与连接条件相匹配的数据记录，过滤掉不匹配的记录。
   也就是说使用内连接的查询结果中只存在满足条件的记录。

   1. SQL92：select 查询内容

      ​ from table1, table2 where 关联条件

      -- 例如：查询雇员信息，及雇员所在部门的信息
      select * from emp, dept where emp.deptno=dept.deptno
      -- 使用别名作为条件
      select * from emp e,dept d where e.deptno=d.deptno
      	```
      
      
      

   2. SQL99：select查询内容
from table1 inner join table2
on 关联条件

      -- 例如：查询雇员信息，及雇员所在部门的信息
      select * from emp e inner join dept d on e.deptno=d.deptno;
      -- 例如：查询部门号为30的雇员信息，及部门名称
      select e.*, d.dname from emp e 
      	inner join dept d on e.deptno=d.deptno 
      	where e.deptno=30
      

• 注意：inner join关键词中的inner可以省略

4. 自连接（self join）

• 一篇不错的关于self join的解释：http://www.360doc.com/content/17/0612/22/43754018_662268826.shtml

1. 连接的两张表是同一张表，一张表不够用，所以模拟生成了另外一张表（还是原来的表）来完成连接。

• 例如：查询雇员姓名，雇员薪资，雇员领导姓名，雇员领导薪资

-- SQL92
select e.empno,e.ename,e.sal,m.empno,m.ename.m.sal 
	from emp e,emp m
	where e.mgr=m.empno
-- SQL99
select e.empno,e.ename,e.sal,m.empno,m.ename.m.sal 
	from emp e join emp m 
	on e.mgr=m.empno

• 例如：雇员号大于领导号的雇员姓名，雇员号，领导号，领导姓名。

-- SQL92:
select e.empno,e.ename,m.empno,m.ename
	from emp e,emp m
	where e.mgr=m.empno and e.empno>e.empno
-- SQL99:
select e.empno,e.ename,m.empno,m.ename
	from emp e join emp m
	on e.mgr=m.empno and e.empno>m.empno
-- 使用where:
select e.empno,e.ename,m.empno,m.ename
	from emp e join emp m
	on e.mgr=m.empno
	wheree.empno>m.empno

• 例子2：
  
• 分布查询也可以获得相同的结果
  
  
  
• 例子3：来自SqlZoo – self join
  
  
  
• 第七题方法01
  
• 第七题方法02
  
  
  【SQLZOO答案】https://github.com/codyloyd/sqlzoo-solutions/blob/master/SQLZOO_solutions.md#self-join

5. 外连接

1. 不仅要查询两张或者多张表中满足条件的纪录，还要查询其中某张表中不满足连接条件的纪录

2. 语法：select 查询内容

   ​ from table1 left|right [outer] join table2

   ​ on 连接条件

   • left join：左外连接，将放到left join左边表中不满足条件的纪录也显示。
   • right join：右外连接，将放到right join右边表中不满足条件的记录也显示。

-- 例如：查询雇员信息，及雇员所在部门的信息
select * from emp e,dept d where e.deptno=d.deptno
-- 例如：查询雇员信息，及雇员所在部门的信息，没有雇员的部门也要显示
-- left join:
select *
	from dept d left join emp e
	on e.deptno=d.deptno
-- right join:
select *
	from emp e right join dept d
	on e.deptno=d.deptno

-- 例如：查询雇员信息，及雇员领导信息，没有领导的雇员也要显示
-- left join:
select *
	from emp e left join emp m 
	on e.mgr=m.empno
-- right join:
select * 
	from emp m left join emp e 
	on e.mgr=m.empno