Oracle学习笔记--day04

偏执的太偏执、 2022-06-17 14:14 241阅读 0赞

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？？？