多表连接

2018-07-29
多表连接

1.等值连接
natural join 自然连接两个表必须有数据类型和表名一样的列，否则会报错
如果列明不相同使用 join on
如果列名和数据类型都不相同使用
join using

join on 是万能的，什么时候都可以用

2.非等值连接
只能使用join on
查询员工的薪水等级
select ename,sal,grade from emp e join salgrade s on sal between losal and hisal;

3.自连接
查询员工领导的姓名
因为是一个表用两个所以使用表的别名
select e.ename sname,w.ename ename from emp e join emp w on e.empno=w.mgr;

如果有n个表需要连接，那么连接条件是
n-1个连接条件
eg: 需要取出ename,dname,sal,grade
那么需要使用三个表
emp dept salgrade
操作语句
select ename,dname,sal,grade from emp join dept using(deptno) join salgrade on sal between losal and hisal;

4.外连接
左外连接 右外连接 全外连接
left join right join full join
select ename,dname from emp right join dept using(deptno);
select ename,dname from dept left join emp using(deptno);
select ename,dname from emp full join dept using (deptno);

join on 语句的后面可以跟and字符作为附加条件作用和where差不多，其他的则是不可以的只能跟where,所以尽量后面的附加条件使用where不适用and
select ename,dname from emp join dept using(deptno) where empno=7566;
select ename,dname from emp join dept on emp.deptno=dept.deptno and empno=7566;

5.交叉连接 cross join
select ename,dname from emp cross join dept where emp.deptno=dept.deptno;
#交叉连接的后面必须添加where条件过滤，否则就会出现行乘以行的结果

6.,的使用
使用 ， 将两个表分隔开也是可以的
select ename,dname from emp,dept where emp.deptno=dept.deptno;

select ename,dname,sal,grade from emp,dept,salgrade where emp.deptno=dept.deptno and sal between losal and hisal;

子查询
子查询分为单行子查询和多行子查询
select ename from emp where sal=(select max(sal) from emp);
select ename,sal from emp where sal in(select sal from emp where sal>2999);

set操作符
新建两个表e1 e2
e1存放10 20部门
e2存放10 30部门
1.合并表union/union all
去除出重复行使用union，这个是排序的按照第一个列进行升序排列
不去除重复行使用union all，这个是不排序的
2.两个表的交集intersect
select * from e1
intersect
select * from e2;

3.两个表的补集minus
select * from e1
minus
select * from e2;
结果是20，意思就是e1对于e2的补集，因为e1中有20，而e2中没有
#补集的话数据类型必须是相同的

select deptno,job,max(sal) from emp group by grouping sets(deptno,sal);