目录内容如下:
这一部分主要是数据库表的联接和集合的使用,主要使用的字段为union,union all,group,join等SQL字段。
3.1,记录集的叠加
解决方案:
select ename,deptno from emp
where deptno = 10
union all
select '--------',null from t1
union all
select dname,deptno from dept
---union all 要求组合的行数据类型或者数据项数相同--------
select deptno from dept
union all
select ename,deptno from emp
--------------------------------该查询将失败
--------------------------------------------------
select deptno from dept
union all
select deptno from dept
---------------------------------该查询将失败
union 与union all 的区别
union 将对查询的数据进行去重操作,union all 则是将所有的行数据组合到一张表中去
3.2,组合相关的行
解决方案:
select ename,loc from emp e,dept d
where e.deptno =d.deptno
and d.deptno =10
还可以使用显式的join方法
select e.ename ,d.loc
from emp e join dept d on e.deptno =d.deptno
where e.deptno=10
3.3,两个表中查找共同行
考虑建立这个视图
create view v as
select ename,sal,job from emp
select * from v
解决方案:
select empno.ename,job,sal,deptno
from emp e join view v on e.ename=v.ename and e.job=v.job and e.deptno=v.deptno
group by deptno
order by deptno desc
------------------------mysql//sqlsever
----------------------------------------------------------------------------------------
select empno.ename,job,sal,deptno
from emp
where (ename,job,deptno) in
(select ename,job,deptno from emp
intersect
select ename ,job,deptno from v
)
group by deptno
order by deptno desc----------------oracle,postgresql
3.4,从一个表中查找另一个表没有的值
1.DB2,POSTGRESQL
-------------------------------------------------------------------
select deptno from emp
except
select deptno from dept
-------------------------------------------------------------------
a,使用except函数操作会进行去重操作,与 not in 不同,null值不会有处理问题
b,使用except要求数据类型相同
--------------------------------------------------------------------
2.oracle
select deptno from emp
MINUS
select deptno from dept
---------------------------------------------------------------------
3.mysql,sqlsever
select deptno from emp
where deptno not in (
select deptno from dept)
----------------------------------------------------------------------------
使用not in 语句时要考虑去重操作与对null值的操作
select d.deptno
from dept
where not exists(
select null from emp e
where d.deptno =e.deptno)