第三章 操作多个表
表连接的内连接和外连接
A表 B表
id name id name
1 a 1 b
2 b 3 c
4 c
内连接就是左表和右表相同的数据,查询结果只有相等的数据：
select * from A inner join B on A.id=B.id
select * from A,B where A.id=B.id
id name id name
1 a 1 b
外连接分为：左外连接、右外连接、全外连接
左外连接就是以左表为准，去匹配右表，左表有多少条数据，结果就是多少条数据
select * from A left join B on A.id=B.id
id name id name
1 a 1 b
2 b null null
4 c null null
右外连接就是与左外连接反之，以右表为准，去匹配左表，右表有多少条数据，结果就是多少条数据
select * from A right join B on A.id=B.id
id name id name
1 a 1 b
null null 3 c
全外连接数据条数不一定，相当与是左外连接 和右外连接 的综合
select * from A full join B on A.id=B.id
id name id name
1 a 1 b
2 b null null
null null 3 c
4 c null null
3.1 表链接
select e.ename,d.loc from emp e,dept d where e.deptno=d.deptno and e.deptno= 10;
这是等值连接是内链接的一种 还有另一种写法
select e.ename,d.loc from emp e inner join dept d on (e.deptno=d.deptno)where e.deptno = 10;
3.2 从一个表中查询另一个表中没有的值 这个相当于两个查询没有表连接
MYSQL
select distinct deptno from dept where dept not in (select deptno from emp);
ORACLE
select deptno from dept minus select deptno from emp;
3.3 在表中查找与其他表不匹配的记录 两个表外连接取出连接后A表中有值B表中没值的记录
MYSQL
select d.* from dept d left outer join emp e on (d.deptno=e.deptno) where e.deptno is null;
ORACLE
select d.* from dept d ,emp e where d.deptno = e.deptno (+) and e.deptno is null; -- oracle 的左连接的写法
3.4 向查询中添加连接又不影响其他链接 内连接外加外连接 不会因为第二个连接造成第一个链接结果改变
MYSQL
select e.ename,d.loc,eb.received from emp e join dept d on (e.deptno=d.deptno) left join emp_bonus eb on(e.empno=eb.empno) order by 2;
ORACLE
select e.ename,d.loc,eb.received from emp e , dept d , emp_bonus eb where e.deptno=d.deptno and e.empno = eb.empno (+) order by 2;
标量子查询
select e.ename ,d.loc,(select eb.received from emp_bonus eb where eb.empno=e.empno)as received from emp e,dept d where e.deptno= d.deptno order by 2;
3.5 聚集与连接 聚集就是求和过程
emp 表中有员工编号和工资等信息 emp_bonus表中有员工号和奖金类型信息 type 1 为奖金为员工工资*10% 2为20% 3为30% 现在要求部门为10的员工工资总和和奖金总和 先链接emp和 emp_bonus两张表 算出各员工奖金 在这个链接过程中由于有的员工有多个奖金，这个连接会产生只有奖金信息不同其他都相同的记录 在求和的时候 工资求和就会发生错误 所以要用去重
MYSQL
select deptno,sum(distinct sal) as total_sal,sum(bouns) as total_bonus from(select e.empno,e.ename,e.sal,e.deptno,e.sal*case when eb.type = 1 then .1 when eb.type =2 then .2 else .3 end as bonus from emp e,emp_bonus eb where e.empno = eb.empno and e.deptno =10)x group by deptno;
ORACLE
select distinct deptno,total_sal,total_bouns from(select e.empno,e.ename,sum(distinct e.sal)over (partition by e.deptno)as total_sal,e.deptno,sum(e.sal*case when eb.type =1 then .1 when eb.type = 2 then .2 else .3 end) over (partition by deptno)as total_bonus from emp e,emp_bonus eb where e.empno=eb.empno and e.deptno = 10) x ;
3.6 聚集与外连接 上一个问题中有的员工没有奖金 这样会影响到 工资的统计
MYSQL
select deptno,sum(distinct sal)as total_sal,sum(bouns) as total_bonus from (select e.empno,e,ename,e.sal,e.deptno,e.sal*case when eb.type is null then 0 when eb.type = 1 then .1 when eb.type = 2 then .2 else .3 end as bonus from emp e left outer join emp_bonus eb on (e.empno = eb.empno) where e.deptno =10) group by deptno;
ORACLE
select deptno,sum(distinct sal)as total_sal,sum(bouns) as total_bonus from (select e.empno,e,ename,e.sal,e.deptno,e.sal*case when eb.type is null then 0 when eb.type = 1 then .1 when eb.type = 2 then .2 else .3 end as bonus from emp e , emp_bonus eb where e.empno = eb.empno (+) and e.deptno =10) group by deptno;
3.7 替换NULL coalesce 函数 将NULL替换成想要的值
select ename,comm from emp where coalesce(comm,0)<(select comm from emp where ename = 'WARD');