Oracle学习笔记--day04

Oracle分页
MySQL：
select *
from xxx
limit ?,?

pageNo start pageSize
1 0 3
2 3 3
3 6 3
4 9 3
start:0 + (pageNo - 1) * pageSize

oracle中，没有limit关键字，但每个查询都有一个可以使用的列：rownum


伪列
创建表时，没有定义过该列
表结构中，不存在该列，表的存储空间中没有该列的存储空间
查询用到的时候，自动生成该列及该列的值
只能查询，不能update、delete、insert

rownum
行号

A ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. Also, a ROWNUM value is incremented only after it is assigned.
如果查询中用到了rownum，在where判断之后，(分组或)排序之前会为每一行生成rownum的值
例：
select rownum,ename from emp;
rownum的值即行的编号
例：
select rownum,emp.* from emp order by sal;
rownum在排序之前生成，在排序之前，每行记录的rownum已生成，排序后，顺序会被打乱
* 需要使用表名指定是哪个表的所有列
rownum
不是表的列，不能加表名限定是表的列
直接写，表示当前查询生成的行号

如果在where条件中指定了关于rownum的条件，在生成每一行的rownum的值时会进行rownum条件判断，不满足rownum条件的记录会被舍弃
rownum的值总是从1开始，且是连续的，即先有1，后有2，再有3...
where条件筛选完毕，检查需要用到rownum
开始编号，如果没有任何有效的编号，则从1开始
判断rownum条件是否成
成立 保留
不成立 舍弃
例：
select rownum,t_student_a.* from t_student_a where rownum <=3;
<= 3
1 保留
2 保留
3 保留 停止

> 3
1 舍弃
1 舍弃
1 舍弃
....
没有任何记录满足条件

= 1 or =3
1 保留
2 舍弃
2 舍弃
....
不会有满足rownum=3的记录
练习：
取emp表的前5条数据
select rownum,emp.* from emp where rownum <=5;
取emp表的中间5条数据
select rownum,emp.* from emp where rownum >=5 and rownum <= 10;

select *
from
(select rownum,emp.* from emp)
where rownum >= 5 and rownum <= 10;

select *
from
(select rownum,emp.* from emp) e
where e.rownum >= 5 and e.rownum <= 10;rownum始终被认为是伪列rownum的名字

select *
from
(select rownum rn,emp.* from emp) e --假如总共1w条数据，每次都要将1w条数据取出来，然后从1w条数据中筛选满足条件的记录
--效率低
where e.rn >= 5 and e.rn <= 10;  

select *
from
(select rownum rn,emp.* from emp where rownum <= 10) e  --最后一次还是会取出1w条数据，但概率小，如百度搜索
 考虑where中能不能使用别名rn
where e.rn >= 5;

取emp表的最后5条数据
select * 
from
(select rownum rn,emp.* from emp where rownum <= 15) e
where e.rn >= 11;

pageNo pageSize >= <=
1 5 1 5
2 5 6 10
3 5 11 15
4 5 16 20

>=:1 + (pageNo - 1)*pageSize
<=:pageNo*pageSize

排序后，再获取分页数据
EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
----- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH      CLERK           7902 17-12月-80            800                    20
7900 JAMES      CLERK           7698 03-12月-81            950                    30
7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20
7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
 
RN      EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
-- ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
1       7369 SMITH      CLERK           7902 17-12月-80            800                    20
3       7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
5       7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
2       7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
4       7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
 
select * 
from
(select rownum rn,emp.* from emp where rownum <= 5 order by sal) e
where e.rn >= 1;
原因：
rownum列的值在排序之前就已经生成，并筛选出满足rownum条件的记录
之后才去排的序
也就是说ORDER BY无法对所有满足条件的记录排序，只能对当前的rownum<=N中的N条记录排序

select *
from
(select rownum rn,e.*
from 
(select emp.* from emp order by sal) e 最内层的查询语句，只关心要查询的数据，不关心分页
外层的两个查询，只关心分页，不关心数据是怎么查到的
where rownum <= 5) e
where e.rn >= 1

Oracle分页：
三层子查询
select *
            from
            (select rownum rn,t.*
            from 
            (查询数据的select) t
            where rownum <= pageNo*pageSize) t
            where t.rn >= (pageNo - 1)*pageSize + 1
select rowid, from
rowid
base64编码
一种编码方式
是对二进制数据的编码
将二进制数据 按照某种规则 转换成新的字符
编码表：
A-Z 0-25
a-z 26-51
0-9 52-61
+ / 62 63
编码过程：
s12
将每个字符变成ascii码十进制值  Integer.toHexString() unicode码
115 49 50
将每个ASCII码转换成8位二进制，高位补0
01110011 00110001 00110010
对上面的二进制重新拆分，每6位一组
011100 110011 000100 110010
对每组数高位补2个0
00011100 00110011 00000100 00110010
将上面的数再转换回十进制
28 51 4 50
将转换的结果和base64编码表进行对应
czEy

是可逆的，可以按规则逆向还原成原内容，即解码
rowid就是base64编码后的结果


s123
xxxxxx xxxxxx xxxxxx xxxxxx xxxxxx xx0000
s1234
xxxxxx xxxxxx xxxxxx xxxxxx xxxxxx xxxxxx xxxx00
n * 8 / 6  n*4/3

如果要编码的二进制数据不是3的倍数，最后会剩下2位或4位，base64会在末尾补4个或2个0
在编码完成后，会在末尾添加1个或2个=,表示补了几个00(两个0) 


JDK提供了一个类，BASE64Encoder，是jdk内部使用的一个类，不建议程序员使用，是存在的
BASE64Decoder


数据库中数据行的物理地址，物理上是存在的
进行insert操作时，确定
根据物理地址，可快速查找到数据

表示记录在数据文件中的物理地址，物理上是存在的
表结构中没有rowid列
记录的物理地址在数据写入数据文件时确定
rowid的值是当前数据行的物理地址经过base64编码后的结果
根据物理地址，可快速查找到数据 加速查询
full table scan 全表扫描
检索数据，从表的第一条数据，一条一条的开始判断，直到最后一条
index lookup 索引查找
rowid 物理地址查找
索引
index

select contents from t_java_book where title = '异常'
t_java_book
-------------------------------------------------
id title contents           
1 基本数据类型 xxxxxx
2 集合 yyyyyyy
3 面向对象 zzzzzzzzz
4 IO dfasdfadsfdas
5 异常 dasfasdfasdkflj
t_index
-----------------------------------------------------
full table scan 全表扫描
检索数据，从表的第一条数据，一条一条的开始判断，直到最后一条
index lookup 索引查找
rowid 物理地址查找

title pageNo
基本数据类型 1
   面向对象 4
  异常 8
集合            10
   IO 50
对应到数据库中，目录就是对某一列的值进行排序后的结果
页码就是该列的值所在的行的物理地址
可看成一个表，称之为索引表

数据排序用的是二叉树的排序和查找算法
二叉树 B-Tree
树节点：排了序的某列的值、值对应的行的物理存储地址

创建索引：
create index indexName 
on tableName(colName1,colName2,...) 
[tablespace tbsName]
创建索引时，需要一张表来存储加了索引的列的值的排序后的结果，及索引列的值所在行的物理地址，这张表称为索引表
所以创建索引时，可以指定索引表所使用的表空间
索引是Oracle对象中的一种
create index indexName on tableName(colName1,colName2,...)
-->生成一个索引表：排了序的某列的值、物理地址

索引是与表相关、可选
不能脱离表独立存在
用于加速对表的访问

当在某个表的某列上创建了索引，在使用该列作为查询条件时，Oracle会
自动引用索引表进行查询，根据指定的值查到对应记录的物理地址，
根据物理地址直接取数据

索引有两面性：
加速查询
维护成本
索引表要占用存储空间
当往原表中，插入、删除一条记录时，要重新调整索引表中索引列值的顺序

索引表由Oracle自己维护，无须用户干预

什么时候用索引：
经常使用某个列作为查询条件
数据量大的时候

表：
主键约束列 查询表数据，一般都以主键作为查询条件，创建表后，会自动为主键列添加索引
唯一约束列 自动加索引

删除索引：
drop index indexName

user_objects
user_indexes


视图
view
Oracle对象
预定义的查询语句

select 
o.id,
o.no,
o.totalPrice orderTotalPrice,
u.username,
i.num,
i.totalPrice itemTotalPrice,
p.name,
(select count(1) from t_item where order_id = o.id) count
from t_order o
join t_user u on o.user_id = u.id
join t_item i on i.order_id = o.id
join t_product p on p.id = i.product_id

视图
select语句用于从表中检索视图要显示的数据
是一个或多个表的数据的逻辑显示
表数据的展示窗口，显示的是查询的结果
没有物理存储空间，不能存储数据，只能用于展示数据          表用于存储数据，创建表时要为表分配存储空间
创建视图对象时，指定的select语句并不执行
每访问一次视图，执行一次关联的select语句

创建：
create view 视图名 as select语句 [with read only|with check option]
with read only
指定视图是只读的，只能从视图查询数据，不能修改
with check option
指定对视图可以执行DML操作
是对视图对应的表执行DML操作
执行DML操作之前会先检查视图中的列在对应的表中是否能找到对应(没经过任何处理)的列，找不到则不能执行DML
默认选项
create table t_test (id, name);
create view v_test as select id,name,1 some from t_test;
创建视图时，如果select语句中查询的列经过了逻辑处理(常量、运算、函数)，则必须起别名，
desc v_test;
insert into v_test values (1,'wangwu',3);
虚拟列，表中的列的值，经过逻辑处理后，对应到视图中的列，因为经过了逻辑处理，所以表中不再有对应的列
这时再对视图进行DML操作，是对视图对应的表执行DML操作，在对应的表中无法找到对应的列，所以不能执行

此处不允许虚拟列，表中没有对应的列，没有对应的存储空间
表中列的值经过逻辑处理后，对应到视图中的列，则会变成虚拟列

连接查询
视图一般是只读的
查询
同 表的查询
删除：
drop view 视图名
优点：
表、数据更安全
隐藏了查询的复杂性
简化sql语句书写
通过视图查找订单详情

当视图的查询语句中用到了伪列、分组时，默认with read only？？？