数据库连表查询

一、rowid 和rownum
ROWID是 ORACLE中的一个重要的概念。用于定位数据库中一条记录的一个 相对唯一地址值。通常情况下，该值在该行数据插入到数据库表时即被确定且唯一。 ROWID它是一个伪列，它并不实际存在于表中。它是ORACLE在读取表中数据行时， 根据每一行数据的物理地址信息编码而成的一个伪列。所以根据一行数据的ROWID能 找到一行数据的物理地址信息。从而快速地定位到数据行。数据库的大多数操作都是 通过 ROWID来完成的，而且使用 ROWID来进行单记录定位速度是最快的。我们可以 将其用于删除重复数据。
ROWNUM是一种伪列，它会根据返回记录生成一个序列化的数字。排序后的 结果集的顺序号 ，每一个结果集 都有自己顺序号 ，不能直接查询大于1的数。利用 ROWNUM，我们可以生产一些原先难以实现的结果输出。 例如实现分页操作。
ps: oracle 中 索引从1开始，java 程序 从0开始
1、删除重复数据 :删除重复记录
– 每条记录的唯一标识
select s.* , rowid from tb_student s;
–找出 保留的rowid
select min(rowid) from tb_student group by name,course;
–删除
delete from tb_student where rowid not in (select min(rowid) from tb_student group by name,course)

2、rownum
rownum :1、必须排序 2、不能直接取大于1 的数
–最底层 rownum 数据库默认顺序号 -->没有用的
select emp., rownum from emp；
select emp., rownum from emp order by sal ;

–自己 排序后结果集的顺序号
select e.*, rownum from (select * from emp order by sal desc) e;

–取出工资前5名
select e.*, rownum from (select * from emp order by sal desc) e where rownum <= 5;

–取出 工资 3-5 名
select e., rownum from (select * from emp order by sal desc) e where rownum <= 5 and rownum >= 3
–三层模板 (分页)
select e. from (select e., rownum rn from (select * from emp order by sal desc) e where rownum <= 5) e where rn >= 3;
/ select 字段列表 from (select e.,rownum rn from (select from 表 order by 字段) e where rownum<= 最大值) where rn>=最小值 / select e. from (select e., rownum rn from (select * from emp order by sal desc) e where rownum <= 10) e where rn >= 6;

二、表连接（92）
当我们获取的数据不是来自于同一张表而是来自于多张表时就需要使用到表连接
select * from emp;
select * from dept;
注意:同名列 非 * 必须区分 数据源 、关系列、 过滤条件、字段
1、笛卡尔积
–非 * 必须区分 使用表名 或别名.区分
select * from emp , dept;
select ename , dname from emp , dept;
select ename, dname, e.deptno from emp e, dept d;
2、等值连接(在笛卡尔积基础上 取条件列相同的值)
–员工名称及部门名称
select ename, dname, e.deptno from emp e, dept d where e.deptno=d.deptno;
– 找出 30 部门的员工名称及部门名称 : 先关联后过滤
select ename, dname, e.deptno from emp e, dept d where e.deptno=d.deptno and e.deptno=30;

– 记录很多时 : 先过滤后关联
– 数据来源： emp (select * from emp where deptno=30) e , dept (select * from dept where deptno=30) d
select * from emp where deptno=30;
select * from dept where deptno=30;
– 查询的字段： ename, dname, e.deptno
– 条件： e.deptno=d.deptno , deptno=30
select ename, dname, e.deptno from (select * from emp where deptno=30) e ,(select * from dept where deptno=30) d where e.deptno=d.deptno

3、非等值连接 > < != <>between and
– 查询员工姓名，工资及等级
– 数据源： emp e, salgrade s
– 字段： ename, grade, sal
– sal between losal and hisal
select ename, grade, sal from salgrade s, emp e where sal between losal and hisal
4、自连接: 特殊的等值连接 (来自于同一张表)
– 找出 存在上级的员工姓名 及上级名称
– 数据来源： emp e, emp m
– 字段： e.ename, m.ename
– 条件： e.mgr=m.empno
select e.ename, m.ename from emp e, emp m where e.mgr=m.empno

5、外连接
–找出 所有的员工姓名 及上级名称
–找出 所有部门的员工数 及部门名称
select dname, nu from dept d, (select count(1) nu, deptno from emp group by deptno) e where d.deptno(+)=e.deptno;
看+（加号）和,（逗号） 主表在,的左边就叫左外连接 主表在,的右边叫右连接 from 后面的逗号

三、99 连接
交叉连接 cross join —>笛卡尔积
自然连接(主外键、同名列) natural join -->等值连接
join using连接(同名列) -->等值连接
[inner]join on 连接 -->等值连接 非等值 自连接 (解决一切) 关系列必须区分
left|right [outer] join on|using -->外连接
full join on|using -->全连接 满足直接匹配，不满足 相互补充null ，确保 所有表的记录 都至少出现一次

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

2、自然连接
select * from emp natural join dept;
–在指定列过程中同名列归共同所有(*除外)
select deptno,e.ename,d.dname from emp e natural join dept d;

3、using 连接
select deptno,e.ename,d.dname from emp e join dept d using(deptno);

4、on 连接
– natrual 等值
select ename, dname from emp natural join dept where deptno = 30;
–using
select ename, dname from emp join dept using (deptno) where deptno = 30;
–on
select ename, dname from emp join dept on emp.deptno = dept.deptno where emp.deptno = 30;
on 非等值连接 、自连接
–部门编号为30的员工名称 工资等级
select ename, grade from emp e join salgrade s on e.sal between s.losal and s.hisal where deptno=30;
–部门编号为30的员工名称 上级名称
select e.ename,m.ename mname from emp e join emp m on e.mgr =m.empno where e.deptno =30;
–部门编号为30的员工 员工名称 部门名称 工资等级 上级名称
select e.ename, dname, grade, m.ename mname 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 e.mgr = m.empno where e.deptno = 30

5、外连接
–所有部门的 部门名称，员工数
–左外
select dname, n from dept d left outer join (select deptno, count(1) n from emp group by deptno) i on d.deptno = i.deptno;
select dname, n from dept d left outer join (select deptno, count(1) n from emp group by deptno) i using (deptno);
–右外
select dname, n from (select deptno, count(1) n from emp group by deptno) i right outer join dept d on d.deptno = i.deptno;

6、全连接
select * from (select 1 no, ‘a’ “name” from dual union select 2 no, ‘b’ “name” from dual) a full join (select 1 no, ‘c’ “name” from dual union select 3 no, ‘d’ “name” from dual) b on a.no=b.no;
select * from (select 1 no, ‘a’ “name” from dual union select 2 no, ‘b’ “name” from dual) a full join (select 1 no, ‘c’ “name” from dual union select 3 no, ‘d’ “name” from dual) b using(no);

四、视图
视图:建立在表|结果集|视图上的虚拟表，有以下作用
1、简化:select 查询语句
2、重用:封装select语句 命名
3、隐藏:内部细节
4、区分:相同数据不同查询
不是所有的用户都有创建视图的权限：
1、前提： create view -->组 connect resource dba
2、授权: -->sqlplus /nolog
a)、sys登录 conn sys/123456@orcl as sysdba
b)、授权: grant dba to scott; 回收: revoke dba from scott;
c)、重新登录 create or replace view 视图名 as select语句 [with read only];
要求:
所有列必须存在名称。
对视图的删除不会删除原有表的数据
drop view 视图名;

求部门经理人中平均薪水最低的部门名称
–1)、部门经理人 -->mgr
create or replace view vw_emp_mgr as select distinct mgr from emp where mgr is not null with read only;
–2)、部门经理人的薪水
create or replace view vw_emp_mgr_sal as select * from emp where empno in (select mgr from vw_emp_mgr) with read only
–3)、 按部门平均
create or replace view vw_emp_sal_group as select deptno,avg(sal) avgsal from vw_emp_mgr_sal group by deptno with read only;
–4)、过滤组
–找出最低的 平均薪水
1)、 使用第三个视图
select min(avgsal) from vw_emp_sal_group
2)、使用第二个视图
select min(avg(sal)) minsal from vw_emp_mgr_sal group by deptno;
–获取部门编号及平均薪水
select * from vw_emp_sal_group where avgsal = (select min(avg(sal)) minsal from vw_emp_mgr_sal group by deptno);
–部门名称
select dname from vw_emp_sal_group vw, dept d where vw.deptno = d.deptno and avgsal = (select min(avg(sal)) minsal from vw_emp_mgr_sal group by deptno);

五、索引（了解）
1、索引是数据库对象之一，用于加快数据的检索，类似于书籍的索引。在数据库中索引可以减少数据 库程序查询结果时需要读取的数据量。
2、类似于在书籍中我们利用索引可以不用翻阅整本书即可找到 想要的信息。
3、索引是建立在表上的可选对象；索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检 索方式，从而提高检索效率 。
4、索引在逻辑上和物理上都与相关的表和数据无关，当创建或者删除一个索引时，不会影响基本的表。
5、索引一旦建立，在表上进行DML操作时（例如在执行插入、修改或者删除相关操作时），oracle会 自动管理索引，索引删除，不会对表产生影响 。
6、索引对用户是透明的，无论表上是否有索引，sql 语句的用法不变。
7、oracle创建主键时会自动在该列上创建索引。

索引: 提高查询速度的一种手段
–>目录
1、唯一性较好字段适合建立索引
2、大数据量才有效果
3、主键|唯一: 唯一索引
create index 索引名 on表名 (字段列表…) drop index 索引名
create index idx_emp on emp(sal,ename);
drop index idx_emp;
select * from emp order by sal,ename;