SE day18数据库笔记

                  SE day18 笔记

一.rowid 和rownum:
定义:两者都是虚列，但含义完全不同。rowid是物理地址，用于
定位oracle中具体数的物理存储位置，而rownum则是sql的输出
结果排序。通俗的讲：rowid是相对不变,rownum会变化，尤其是
使用order by的时候。
1.rowid实现重复记录的删除
2.rownum :1、必须排序 2、不能直接取大于1的数
二.多表联合查询:
1.SQL99多表连接查询

1. cross join(交叉连接)
   交叉连接会产生一个笛卡尔积
   select * from emp cross join dept;
   在笛卡尔积中, 有很多数据是无意义的, 所以需要消除, 可以通过 where 子句来消除
   select * from emp cross join dept where
   emp.deptno=dept.deptno;
   可以在查询时, 给表格起别名（如果不是e.，则后面必须直接跟from，否则会报错）
   select e., dname, loc from emp e cross join dept d
   where e.deptno=d.deptno;
2. natural join(自然连接)
   natural join 用于针对多张表的同名字段进行等值连接
   select * from emp e natural join dept d;
   特点:
   a) 在自然连接时, 自动进行所有同名列的等值连接, 不需
   要写连接的条件
   b) 同名列只显示一列, 而且在使用时, 不能加表前缀（e.、d.、）
   查询所有员工的姓名, 部门编号和部门名称
   select e.ename, deptno, d.dname from emp e natural join dept d;
3. inner join(内连接)
   inner可以省略不写
   select * from emp inner join dept
   on emp.deptno=dept.deptno;
4. using 子句
   用来指定进行等值连接的同名字段, 针对自然连接提供的. 同名字段依然不能使用表前缀.
   a) 查询 20 部门员工的姓名, 工资, 部门编号和部门名称

select e.ename, e.sal, deptno, d.dname from emp e
natural join dept d where deptno=20;
select e.ename, e.sal, deptno, d.dname from emp e
join dept d using (deptno) where deptno=20;
5. on 子句
on 子句是使用非常广泛的子句, 它可以被用来指定连接的（特别是非等值连接）
条件. 用于将过滤条件和关联条件分开.
a) 查询所有员工的姓名, 工资和工资等级
select e.ename, e.sal, s.grade from emp e join
salgrade s on e.sal>=s.losal and e.sal<=s.hisal;
select e.ename, e.sal, s.grade from emp e join
salgrade s on e.sal between s.losal and s.hisal;
b) 查询 30 部门员工的编号, 姓名, 部门名称和所在地

select e.empno, e.ename, d.dname, d.loc
from emp e
join dept d
on e.deptno=d.deptno
where d.deptno=30;
c) 查询所有员工的姓名, 部门名称, 工资及工资等级
N 张表连接, 至少需要 N-1 个连接条件
select e.ename, d.dname, e.sal, s.grade
from emp e
join dept d
on e.deptno=d.deptno
join salgrade s
on e.sal between s.losal and s.hisal;
6. 外连接
外连接除了能显示满足连接条件的数据以外, 还用于显示不
满足连接条件的数据
左外连接
left [outer] join, 表示左外连接, 可以显示左表中不满足
连接条件的数据
select e.ename, e.job, d.deptno, d.dname, d.loc
from dept d
left join emp e
on e.deptno=d.deptno;
右外连接
right [outer] join, 表示右外连接, 可以显示右表中不满
足连接条件的数据
select e.ename, e.job, d.deptno, d.dname, d.loc
from emp e
right join dept d
on e.deptno=d.deptno;
全外连接
full [outer] join, 表示全外连接, 可以显示左右两表中不满足连接条件的数据
select e1.empno, e1.ename, e2.empno, e2.ename
from emp e1
full join emp e2
on e1.mgr=e2.empno
order by e1.empno;
自连接
自连接是发生在同一个表格中的连接
a) 查询所有员工的编号, 姓名和领导的编号及姓名
select e1.empno, e1.ename, e2.empno, e2.ename
from emp e1
join emp e2
on e1.mgr=e2.empno
order by e1.empno;
b) 查询所有员工的编号, 姓名和领导的编号及姓名, 并显示
没有领导的员工信息
select e1.empno, e1.ename, e2.empno, e2.ename
from emp e1
left join emp e2
on e1.mgr=e2.empno
order by e1.empno;
2.SQL92多表连接查询
1 笛卡尔积
多张表通过逗号分隔
select * from emp e, dept d;
消除无意义数据
select * from emp e, dept d where e.deptno=d.deptno;
2 等值连接
a) 查询 10 部门员工的姓名, 职位和部门名称

select e.ename, e.job, d.dname from emp e, dept d where
e.deptno=d.deptno and d.deptno=10;
3 非等值连接
a) 查询所有员工的姓名, 工资和工资等级
select e.ename, e.sal, s.grade from emp e, salgrade
s where e.sal between s.losal and s.hisal;
4 外连接
通过”(+)”符号实现外连接
左外连接
select e.ename, d.deptno, d.dname, d.loc
from emp e, dept d
where d.deptno=e.deptno(+);
右外连接
select e.ename, d.deptno, d.dname, d.loc
from emp e, dept d
where e.deptno(+)=d.deptno;
自连接
select e1.empno, e1.ename, e2.empno, e2.ename
from emp e1, emp e2
where e1.mgr=e2.empno(+);
三. 视图
视图:建立在表|结果集|视图上的虚拟表，有以下作用
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
四.索引:
特点: 提高查询速度的一种手段 -->目录
1、唯一性较好字段适合建立索引
2、大数据量才有效果
3、主键|唯一: 唯一索引
注意:数据库的索引是从1开始,java从0开始
1.索引是数据库对象之一，用于加快数据的检索，类似于书籍的索引。在数据库中索引可以减少数据库程序查询结果时需要读取的数据量，类似于在书籍中我们利用索引可以不用翻阅整本书即可找到想要的信息。
2.索引是建立在表上的可选对象；索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率
3.索引在逻辑上和物理上都与相关的表和数据无关，当创建或者删除一个索引时，不会影响基本的表；
4.索引一旦建立，在表上进行DML操作时（例如在执行插入、修改或者删除相关操作时），oracle会自动管理索引，索引删除，不会对表产生影响
5.索引对用户是透明的，无论表上是否有索引，sql语句的用法不变
6.oracle创建主键时会自动在该列上创建索引
7.