数据库之表连接

一、rowid 和rownum
1、rowid
rowid是ORACLE在读取表中数据行时， 根据每一行数据的物理地址信息编码而成的一个伪列。所以根据一行数据的rowid能 找到一行数据的物理地址信息。从而快速地定位到数据行。
用于定位数据库中一条记录的一个 相对唯一地址值。
rowid是一个伪列，它并不实际存在于表中。

--rowid相当于对象的地址,在数据插入到表中时候已经存在,rowid不是地址,根据地址转换的
select ename,job,rowid from emp;
--要求：删除重复记录，一条记录只保留一次
--思路：将所有记录按照某种特定规律分组（相同的记录为一组），保留下每组中的一条记录即可，其他记录删除
--查询要保留的数据
select * from emp where rowid in(select rowid from emp);
--查询要删除的数据
select * from emp where  rowid not in(select rowid from emp);
--删除数据
delete from emp where rowid not in(select rowid from emp);

2、rownum
分页，结果集的序号从1开始
按照主键进行排序 先排序再确定rownum
按照非主键排序，先确定rownum再排序

select deptno,rownum from emp order by deptno;
select deptno,ename,rownum from emp order by empno;
select deptno,ename,rownum from emp order by ename;
--按照工资降序排序，找第三页的数据 一页显示两条
--正确写法1
select ename, sal, rownum, m, n
  from (select ename, sal, rownum m, n
          from (select ename, rownum n, sal from emp order by sal desc))
 where m >= 5
   and m <= 6;
--易犯错误写法2
select ename, sal, rownum, n
  from (select ename, rownum n, sal from emp order by sal desc)
 where n >= 5
   and n <= 6;
--注意此时语句执行顺序为from -> where -> select ,写法二中，ename不是主键
--因此此时rownum n 为先确定rownum再排序，为乱序，通过n筛选的记录是错误记录。
--正确写法为筛选出记录之后要重新确定rownum m,然后再通过m筛选得到正确结果

二、表连接
使用场景：当我们获取的数据不是来自于同一张表而是来自于多张表时就需要使用到表连接
1、92语法
笛卡尔积

--笛卡尔积  emp中有12条记录，dept表中有4条记录，则最终查询记录为48条记录
select * from emp,dept;

等值连接(在笛卡尔积基础上 取条件列相同的值)

--等值连接 可以使相同字段 也可以是不同字段，但是要保证两个字段的数据类型相同
select * from emp e,dept d where e.deptno=d.deptno;
--没有满足条件的数据
select * from emp e,dept d where e.ename=d.dname;
--字段数据类型不一致会报错
select * from emp e,dept d where e.ename=d.deptno;

非等值连接（ > < != <>between and ）

--非等值连接
--薪资2500的等级信息
select * from salgrade s where 2500 between s.losal and s.hisal;
--查询所有员工的信息以及工资等级信息
select * from salgrade s,emp e where e.sal between s.losal and s.hisal;
--查询所有有上级的员工信息以及上级信息
select * from emp e1,emp e2 where e1.mgr=e2.empno;

自连接 特殊的连接,自己连接自己 也可以自外链接

--查询所有有上级的员工信息以及上级信息
select * from emp e1,emp e2 where e1.mgr=e2.empno;

外连接
左外连接
右外连接

--查询所有员工的信息及上级信息  假设e1是员工表  e2是上级表
--需求:想要期中的某张表中所有的数据全部展示,无论是否满足等值连接条件 --外链接 
--外连接：确认主表(主表中的内容会全部展示)   +对面的表为主表,+所在的表为从表
--    左外连接   ->主表在,的左边叫做左连接    
select * from emp e1,emp e2 where e1.mgr=e2.empno(+);
--    右外连接   ->主表在,的右边叫做左连接                                                       
select * from emp e2,emp e1 where e1.mgr=e2.empno(+);
--这个+号可以这样来理解： + 表示补充，即哪个表有加号，这个表就是匹配表。所以加号写在右表，左表就是全部显示，故是左连接。
--找出 所有部门的员工数 及部门名称
select deptno,count(1) from emp group by deptno;
select d.deptno, d.dname, nvl(c, 0)
  from dept d, (select deptno, count(1) c from emp group by deptno) e
 where e.deptno(+) = d.deptno;

注意：同名列 非* 必须区分 数据源 、关系列、 过滤条件、字段

--注意：连表查询最好使用别名指定字段来源，因为使用的字段是在多个表中存在的
select e.deptno,d.deptno from emp e ,dept d;

2、99语法
交叉连接

--交叉连接  cross join
select * from emp cross join dept;

等值连接
自然连接
using

--等值连接
--     自然连接  natural join 内部自动查找同名字段|
--           不能指定限定词
select * from emp natural join dept;
--     using 连接 指定字段等值连接
select * from emp join dept using(deptno);

非等值连接

--非等值连接  join...on...(Inner join 内连接)（也可以用来进行等值连接）
--查询10和30部门的员工的名称,部门名称,薪资,薪资等级,上级名称 
select e.ename,e.deptno,d.dname,e.sal,s.grade,m.ename 
       from emp e 
        join dept d on e.deptno=d.deptno
         join salgrade s on e.sal between s.losal and s.hisal
          join emp m on m.empno=e.mgr
           where e.deptno in(20,30);

外连接

--外连接
--         左外连接 left join on
select * from emp e1 left join emp e2 on e1.mgr=e2.empno;
--         右外连接 right join on
select * from emp e2 right join emp e1 on e1.mgr=e2.empno;