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= ;

这是等值连接是内链接的一种还有另一种写法

select e.ename,d.loc from emp e inner join dept d on (e.deptno=d.deptno)where e.deptno = ;

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 ;

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

巴特西

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

最新文章

热门文章