SQL.Cookbook 读书笔记3 操作多个表

第三章 操作多个表

表连接的内连接和外连接

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');