oracle学习总结（四）

一、集合
前面，内连接解决的是匹配问题，就是将两张表中相同属性列的值相同的那些记录拼在一起，
显然，只有两张表中有相同的属性列才会进行内连接或者外连接。
问题1：为什么说匹配问题就相当于是求交集呢？
因为两张表中有相同的属性列，相当于对这形同的属性列进行求交集，然后把这些行拼在一起。
同理，不匹配问题就是求差集，匹配问题+不匹配问题就是求并集。


1.union(去重)/uinon all(不去重，保留所有)：并集
2.intersect(相交)：交集
3.minus(相减)：差
例1：当月包在线时长为20小时，单位费用涨5分，为40小时涨3分，其他不变
select base_duration, base_cost, unit_cost+0.05
from cost_bs
where base_duration = 20
union all
select base_duration, base_cost, unit_cost+0.03
from cost_bs
where base_duration = 40
union all
select base_duration, base_cost, unit_cost
from cost_bs
where base_duration not in(20, 40)
or base_duration is null;


例2：列出客户姓名以及他的推荐人
select t1.real_name, t2.real_name
from account_lin t1 join account_lin t2
on t1.recommender_id = t2.id
union all
select real_name, 'No recommender'
from account_lin
where recommender_id is null;


例3：sun_server和sun280上的业务使用了哪些相同的资费标准？
select cost_id 
from service_lin s join host_chen h
on s.unix_host = h.id
and h.name = 'sun-server'
intersect 
select cost_id
from service_lin s join host_chen h
on s.unix_host = h.id
and h.name = 'sun280';


例4：哪些unix服务器上没有开通远程登录业务
select id
from host_chen
minus 
select unix_host
from service_lin;


注：只要在service表中的服务器肯定是开通了远程登录业务的


二、小结
子查询 连接 集合
匹配问题
in  inner join exists intersect
不匹配问题
not in （outer join + where 匹配表.非空列 is null）not exists minus
匹配+不匹配问题
outer join union union all




三、排名分页问题
前面用到的技术都是各大数据库通用的！
1.伪列rownum（注：rownum的数值是在获取每行后再赋予的）
select * from table
where rownum <= 2;
执行过程：
先遍历整张表，一条记录一条记录地看。
先拿出第一条，然后编号为1，再看满不满足条件<=2，满足则在结果集中保留该条记录。
然后取第二条记录，编号2，依次下去，直到表中的所有记录都被遍历完。
整个过程，只要当前的记录不满足条件，下一次取出记录时，rownum就不会+1，而是沿用这次不满足条件的记录的rownum
因此，这个语句可以查询到两条结果。


select * from table
where rownum = 2;
同以上的推理过程，这条语句返回0条记录


例1：查询account表中编号4-6的客户。
select rn, real_name
from(
  select rownum rn, real_name
  from account_lin
  where rownum <= 6
)；


例2：查询account表中最晚开通帐号前3个客户
select rownum, real_name, create_date
from account_lin
where rownum <= 3
order by create_date asc;
注：由于where比order先执行，所以以上语句是先将rownum的前三个取出来再排序的。显然是错的，
    我们必须先将日期进行排序了之后，然后再选出rownum的前三个，只能用到子查询！


正解：
select rownum, real_name, create_date
from(
  select real_name, create_date
  from account_lin
  order by create_date
)
where rownum <= 3;


例3：查询account表中最晚开通帐号的第4到第6的客户
select rn, real_name, create_date
from(
  select rownum rn, real_name, create_date
  from(
    select real_name, create_date
    from account_lin
    order by create_date
  )
  where rownum <= 6
)
where rn >= 4;


四、约束
1.primary key：主键 --> 列级，表级
A.列级约束写法
create table table1(
c 属性 [constraint table1_c_pk(约束名)]primary key
);
B.表级约束写法
create table table2(
c1 属性,
c2 属性,
constraint 约束名 primary key(c1)
// 此处，也可以c1和c2作联合主键！
constraint 约束名 primary key(c1，c2)
);
C.给存在的表增加约束，语法形式根表级约束一样
alter table table2
add constraint primary key(c2);
注：主键约束同时包含了非空约束和唯一约束！


2.not null：非空  --> 只有列级
只有一种写法：列名 not null;


3.uinque：唯一  --> 列级，表级
A.列级约束写法
create table table1(
c 属性 [constraint table1_c_uk(约束名)]unique
);
B.表级约束写法
create table table2(
c1 属性,
c2 属性,
constraint 约束名 unique(c1)
// 此处，也可以c1和c2作联合唯一键！
constraint 约束名 unique(c1，c2)
);
C.给存在的表增加约束，语法形式根表级约束一样
alter table table2
add constraint unique(c2);


4.foreign key：外键 --> 列级，表级
A.列级写法
create table parent(
  c1 number primary key
);


create table child(
  c1 number primary key,
  c2 number constraint child_c2_fk references parent(c1) 
);
B.表级写法
create table parent(
  c1 number primary key
);


create table child(
  c1 number primary key,
  c2 number,
  constraint child_c2_fk foreign key(c2) references parent(c1) 
);
1）父表：被引用列所在的表
2）子表：引用的列所在的表
注：1.引用的列在父表中必须是unique或者是primary key的
    2.引用的列和被引用的列的定义要完全一样
    3.如果在外键约束后面加"on delete cascade",则在删除父表中的数据前，先删除
      子表中相应的记录，然后再删除父表中的该记录。
      如：在service表的account_id中如果定义成"on delete cascade"，则在删除
      一个客户时，这个客户开通的业务也会被相应的删除掉。
    4.如果在外键约束后面加"on delete set null",则在删除父表中的数据时，先将
      子表中相应的值置为null，然后再删除父表中的该记录。
什么时候使用外键约束？
答：在表示同一张表或不同的表的一对多关系时，常常把表示“一”的那一方作为父表，然后它的
    主键列被“多”的那一方所引用。


注：一般地，如果想删除父表，必须先删除子表，这显然对我们有影响，我们在删除表的时候删掉级联约束 
    即可！
    drop table parent cascade constraint;
    等同于先删除子表的外键约束，然后再删除父表
    alter table child drop child_c2_fk;
    drop table parent;
    

 
5.check：检查  --> 列级，表级
A.列级约束写法
create table table1(
c 属性 [constraint table1_c_ck(约束名)]check(c > 3)
);
B.表级约束写法
create table table2(
c1 属性,
c2 属性,
constraint 约束名 check(c1+c2 > 30)
);
C.给存在的表增加约束，语法形式根表级约束一样
alter table table2

add constraint check(c1+c2 > 30);

连接数据库（sqlplus/sql developer/jdbc）
DDL：
create table
 (column 列  datatype 数据类型 constraint 约束)；
alter table 对列 约束
drop table           cascade constraint purge；


DML
insert
update
delete


TCL (transaction)
commit
rollback：回滚是回滚到上一次commit后的状态。
一、事务
1.什么是事务？
事务是由一组DML语句和commit/rollback组成的，是改变数据库数据的最小逻辑单元。如果是commit，则表示数据入库；如果是rollback，表示取消所有的DML操作。
在做DML操作时，数据并没有真正入库，即只是单纯做了这些操作，数据并没有持久化而入库。
只有在完成一个事务后才会将数据真正入库！
比如，有一次，在做一个项目的时候，在jdbc里写了一大堆的insert、delete语句，然后运行无误。
后来，在用sql developer的时候，看不到在jdbc里修改的数据了。


2.事务的开始和结束
开始：上一个事务的结束即是下一个事务的开始
结束：commit/rollback
     DDL语句自动commit（这个是很容易忽略的）
3.事务的特性
1）原子性：一组DML语句，要么全部都执行，要么全部都不执行
2）一致性：事务把数据库从一个一致状态转变到另一个一致性状态。
          比如，从A卡里王B卡里转帐1000，假设原来A卡里有3000，B卡里有100。
          从A卡里有1000，B卡里有100这个一致性状态，转变到后来的A卡里有2000，B卡里有1100
          这个一致性状态。
          一致性状态就是数据库里所有用户都能看到的状态。
3）隔离性：在事务提交之前，其他事务觉察不到事务的影响
  比如说，我再用农行卡取钱，同时在电脑里用网银买东西，在取钱的过程中（即钱还没取出来之前），
  网银这边是不会看到钱少了的，只有等到等我输入了取款金额之后，然后钱出来了，然后网银那边才能
  看到前被扣了。
  一个事务只可以读取在事务开始之前提交的数据和本事务正在修改的数据！
  也就是说，如果不提叫事务，其他的session是看不到这些变更后的数据的！
  注：DDL是自动commit事务的！
4）持久性：一旦事务提交，它是永久的


4.理解事务的运行机制
在与数据库建立连接之后，我们提交给数据库的是一个一个的事务，这些事务是运行在session上的。


二、锁
1.行级锁和表级锁
行级锁：被锁定行不可以进行修改，删除，只可以被其他会话select。在行级锁之前需要先加表结构共享
锁;
表级锁：表结构的共享锁，是不可对该表进行DDL操作，但对DML操作都不限制。
2.什么时候用到锁？
为确保并发用户能正确使用与管理共享资源，如表中的记录，就要用到锁。
当我们做DML操作时，系统会为这张表加表级锁和行级锁。任何一个session操作某条记录时，先加一把
表级锁，以防止DDL语句对该表进行修改删除。然后在该行上加一把行级锁，以防止其他会话对该条记录修改和删除，但可以被其他会话select。如果某条记录已经被某个会话加上了行级锁了(没有释放)，那其他
会话就不能在改行上加行级锁了，知道这个会话把行级锁释放了才能。
当我们做DDL操作时，先检查表上有没有表级锁，如果没有，系统会在表上加表级排它锁。如果有，报错。
3.事务不提交的后果
1）其他会话看不到它的操作结果
2）表和行上加的锁不释放，会阻塞其他事务的操作
3）它所操作的数据可以恢复到之前的状态
4）占用的回滚段资源不释放
   (在做DML操作的时候，上一次提交后的状态（旧数据）是保存在一个公共空间里，如果不进行提交，那
    会话一直占着这段空间，当commit或这rollback之后，之前保存的数据就没用了，它就会释放掉这
    段公共空间了)
    
5.保留点
commit;
insert...
update...
savepoint update_done; // 此处设置一个保留点，以后回滚时，之前的语句不会被回滚
insert ...
rollback to update_done； //回滚到保留点


三、小知识点
1.范式
  1）第二范式：非主属性要完全依赖于主属性。在联合主键时，不能存在某些非主属性只依赖于部分
              的主属性，所以在多对多关系时，不要合表，要分三张表，
  2）第三范式：非主属性不能依赖于另一个非主属性。保证一对多关系时，不要合表，要用到两张表。
  3）第一范式：表中的每一列都是不可分割的数据项。
2.建表时指定默认值
create table(
c1 number default 1; // 指定c1列的默认值为1
);




3.一对一，一对多，多对多关系
1）一对多：就是在“多“的一方以“一”的一方的外键引用“一”的一方的主键或唯一键。
事实上，一对多关系可以用一张表来实现，但是会产生大量的数据冗余。
如：一个account的信息本来只需要写一遍的，但由于它对应多个service，
    所以要写多遍。所以一对多关系就要分为两张表了，以后要合起来就要做join操作了。
第三范式说的就是非主属性不能依赖于另一个非主属性，如果依赖了，就感觉一张表里有两个primary key一样。第三范式就是为了避免在表示一对多关系时写成一张表的，因为非主属性会依赖于另一个非主属性。


2）多对多：如学生和课程
student course stu_cour
id pk id pk sid fk --> sdu(id)
name name cid fk --> cour(id) pk(sid, cid)
也即，建立一张关系表，以学生和课程的id作为联合主键
学生id可以重，课程id可以重，但是他们联合起来不能重。
第二范式就是为了保证多对多关系中不能用一张表的，因为非主属性会只依赖于部分主属性，从而
生大量的数据冗余


3）一对一：如一夫一妻
法一：可以用一张表，这个不解释
法二：用两张表
husband id(pk) wid(uk) --> wife(id) wife id(pk)
注：外键列为unique,才能保证这种一对一关系。







一、数据库对象
1.表table
前面我们学了table的一系列的操作，现在来学一下带子查询的一系列操作。
1）create table后跟子查询
create table 
as 
subquery
注：1.表中的列由子查询select后的列决定，create table指定的列的数量要跟select语句指定的列
      的数量一致，如果create table后面不指定列，则与子查询里select后面的列一样。
    2.表中的记录就是自查询的返回结果
    3.create table定义列只能定义列名，缺省值，约束。其中，not null可以不用定义，
       可以直接从源表中赋值过来。而对于pk和uk约束，不会复制，要自己再加入。
例1：创建表，与service表结构相同，且有unix_host为‘192.168.0.23’的记录
create table service_2
as select * from service
where unix_host = '192.168.0.23';
以上，相当于创建了一张名为service_2的表，表中的列由子查询select后的列
决定，表中的记录就是子查询的返回结果。


例2：创建一张表account_90,表结构与account_lin一致，且没有数据
create table account_cs
as 
select * from account_lin
where 1=2;
小技巧：类似这种创建没有记录的表，在where后面加个永假式即可
               创建的表复制源表中所有的记录，where后面加个永真式（也可以不加where）
               
2）insert后面跟子查询
 insert into table2[(colname1, colname2...)]
 select colname1, colname2...
 from table1
 where condition;
 注：1.insert指定的列的数量要跟select语句指定的列的数量一致
  2.表示的是将子查询返回的结果插入到表中，所以一次可以插入多条记录，不能用values子句。


例1：在刚刚创建的account_90的空表中，准备包含所有的90后的客户
insert into account_cs 
select * from account_lin
where to_char(birthdate, 'yyyy') between 1990 and 1999;


3）缺省值default
在建表的时候可以指定缺省值
colname number default 1
然后在插入或者修改的时候都可以指定为该默认值，或者指定其他值也可以。
insert into table values(default);
update table set colname = default;
               
2.视图view
1）什么是view？
视图其实就是一条查询sql语句，它在数据库中不存储数据值，即不占空间
类似与windows中的快捷方式。


2）什么时候用view？
a.由于view其实就是一个select语句，所以当一个查询条件非常复杂又要经常用到时，
就可以将这个查询定义成一个视图，从而简化了sql查询语句。
b.控制权限。如果只允许查询一张表中的部分数据，就可以为该表创建视图，用where子句
  来控制用户可以查看哪些记录，然后授予用户读视图的权限。即，将表的子集做成试图
c.如果一张表中的数据非常庞大的时候，可以将多张表的记录合并在一起生成一张视图(union all),
  用户看起来就感觉是这几张表中的记录都可以查看一样。即，将多张表的并集做成视图。


3）视图的分类
a.简单试图
注：1）基于单张表并且不包含函数或表达式的试图，在该视图上可以执行增、删、改等DML操作。
在视图上做的DML操作会改变源表的记录，但是视图上不一定能看到这些记录，只有符合创建
视图时的where条件，才能被看到。
如：test表中有c1列(number)和c2列(number)
create or replace view test_v
as
select * from test
where c1 = 1;


此时，如果执行insert into test_v values(2, 2);
该条记录被插到源表里了，但是在视图上看不到，因为视图上只显示c1=1的记录。
如果想要在insert或者delete数据时，也要满足where后面的语句（这样更符合我们的逻辑）
则可以使用with check option约束。
...
where c1 = 1
with check option;


2）视图默认情况下可以对源表进行DML操作，如果想要视图对源表只能做select操作，
  则在创建view时的where语句后面加with read only(只读)
  ...
  where c1 = 1
  with read only;
  
b.复杂视图
包含函数、表达式或者分组数据的视图，在该视图上执行DML语句必须要符合特定的条件。
在定义复杂视图时必须为函数或表达式定义起别名。
c.连接视图
基于多个表建立的视图，一般来说不会在该视图上执行insert、update、delete操作。


4)视图的DDL语句
a.create or replace view view_name
b.alter view(修改视图，主要是对view维护时的编译：alter view compile) 
c.drop view （删除视图）


例：列出每个用户使用了哪些资费？
法一：
create or replace view acc_cost
as
select distinct real_name, name
from account_lin a left join service_lin s
on a.id = s.account_id
left join cost_bs c
on s.cost_id = c.id;


注：1.如果某个用户在不同的服务器上申请的资费是一样的，则只需要列出一个即可，所以使用了
     distinct。
    2.由于要让没有申请远程登录业务的客户也出现在结果集中，所以使用外连接(left join)。
      与cost表连接的时候，还是要用到外连接(left join)。


法二：create or replace view acc_cost
as 
select real_name, name
from account_lin a left join
(
  select distinct account_id, name
  from cost_bs c join service_lin s
  on s.cost_id = c.id 
) t
on a.id = t.account_id;


select real_name, name 
from acc_cost;


这里就体现了视图的第一个优点a，简化了sql语句，程序员只需要写下面的select语句，而
上面的建视图的语句由专门人士负责。


3.索引
1）引入索引的背景
扫描表的方式
a.全表扫描
当我们create table时，会向数据库的数据文件申请一段空间（数据块）来存放数据。这段空间里存储数据的最小单位叫data block(数据块)，每个数据块里会可以存放若干条记录。随着我们不停地往表里insert数据，数据块里的记录不断增多。由于有些数据块没有被使用，当我们想把表里的数据全部读出来时，不是扫描所有的数据块的。
数据库为数据块设置了一个高水位线(HWM:High Water Mark)，它标记曾经插入数据的最远块，就是说如果用delete来删除表里的数据(无论是删除部分还是删除整张表中的记录)，HWM不会往前移，读表里的所有数据时，只会扫描到HWM以下的所有数据块，依次读数据块里的记录。HWM以下的数据块不一定都有记录，因为如果我们可以用delete删除记录，但HWM并不前移（认为这段空间以后这张表可能还会用到）。


如果我们delete from table 来删除整张表中的记录时，HWM不前移，所以表占用的空间并没有释放。
如果是一张数据量很大的表，我们用delete from table 来删除表中记录，显然不合适，甚至是致命的。比如做count统计，过了半天，统计结果为0。
delete from table(大表)的危害：
1.表占用的空间没有释放，HWM 没动，count(*)很耗时，结果为0，吃力不讨好。
2.为保证数据可以恢复(只要没有commit)，会将旧的数据写入回滚段，很耗时。
所以，一般不用delete from tabname删除大表的所有数据，一般用truncate table tabname
truncate的好处：
1.HWM会前移，空间释放
2.不写回滚段，用时短
3.数据是不可恢复的
注：delete是DML操作，truncate是DDL操作


回到我们的读数据，假如我们做
selete real_name from account
where id = 1010;
数据记录在些数据块里存储是完全无序的，所以要查找id=1010的客户，只能是扫描HWM以下的数据块，
知道把所有的数据块扫描完，找到了所有的id=1010的客户为止。所以用全表扫描来在大数据量的表里查找
符合条件的记录是不可取的。


b.通过rowid扫描
可以用rowid伪列来查找记录，任何两条记录，即使他们的所有列的取值都一样，但在oracle中，他们的rowid还是不一样的。所以说rowid就是表中记录的唯一标识，相当于我们的身份证号。
rowid包含了以下信息：
1）该记录属于那张表的：data_object_id
2）该记录在哪个数据文件里：file_id
3）该记录在数据文件的第几个数据块里：block_id
4）该记录在数据块里是第几条记录：row_id
显然，如果我们已经知道一条记录的rowid了，就不用扫描那么多数据块了，只需要到符合条件的数据块里去查找。这种方式查找是最快的，不管表里有多少条记录，也能一下精准定位。
但事实是，我们并不知道某条记录的rowid，但是索引知道，所以引入了索引更方便来查找。


2）索引的创建和简介
create index tabname_colname_index
on tabname(colname);
表示在tabname表上的colname列上创建了索引tabname_colname_index。


索引里存的什么内容呢?
为表的某一列col创建了索引index后，那么每条记录的col对应的的值和该条记录的rowid一起拿出来
作为一个索引项，存放到索引里，所以索引是占存储空间的
key, rowid index entry(索引项) 
所以说索引里记录的都是一个一个的索引项，一张表有几条记录就有几个索引项。
此时，如果我们做
select real_name from account
where real_name = 'weixb';
就相当于是
select real_name from account
where rowid = 'xxx';
搜索十分方便快捷。


3)索引的内部结构
                       根块
    
         分支块1                  分支块1
         
          
    叶子块1   叶子块2      叶子块3     叶子块4
索引项放在叶子块里，所有的叶子块构成双向链表，而且是排序的，叶子块1，2，3，4中的key值依次
增大。
根块和分支块是用来导航的，比如根块里存放的是哪些范围的key存放在那个分支块里的，缩小检索的范围。执行下面的话的时候
select real_name from account
where real_name = 'weixb';
从根块开始扫描，首先确定在哪个分支块里，然后再确定在哪个叶子块，就可以找到rowid了。


4）为什么要使用索引？
a.索引里记录了rowid，所以查找记录非常快
b.通过rowid定位数据能有效降低读取数据块的数量（只需要读符合条件的数据块）
c.索引的使用和维护是自动的，一般情况下不需要用户干预
   比如索引里存的索引项怎么存的我们不用管，新插入数据了，索引怎么添加的，表删了之后索引怎么处
   理的这些都不需要我们关心，都有系统完成。我们只需要知道在表的某列上建立索引就行了。


5）哪些列适合建立索引？
a.经常出现在where子句中的列
b.经常用于表连接的列。
  一般在匹配表上的被引用的列上建索引，匹配的效率增高。驱动表要做全表扫描。
c.该列有很多不同的值
  注：pk列和uk列上一定有索引
d.该列包含很多null值
e.表很大，查询的结果集很小
f.主键列pk、唯一键列uk
g.外键列fk
h.经常需要排序(order by)和分组(group by)的列
i.索引不是万能的，但没有索引是万万不能的！


6）索引的类型
a.唯一性索引
b.非唯一性索引
c.单列索引
d.联合索引
  一般联合主键上有联合唯一索引，当两个列经常被同时作为查询条件时，适合建联合索引。
  如：where c1 = 1 and c2 = 2;
  
）唯一性约束和唯一性索引是一回事，唯一性约束是通过唯一性约束实现的。
  当一个表很大的时候，而要查询的记录很少的时候，索引的效率就非常高了，
  
8）一些写法导致索引不可用
a.函数导致索引用不了
  where upper(colname) = 'xxx';
  如果在colname列上建了索引，不能用了，因为索引上存的是列的值，而不是函数值！
  
b.表达式导致索引用不了
  where colname*12 = 1024;
  
c.部分隐式数据类型转换导致索引用不了
where colname = 2（c1为varchar2类型）
相当于系统做了一个to_number(colname)，就不能用索引了
万一查询的时候必须要用到函数才能，可以建一个函数索引
create index account_birthdate_funidx
on tab(round(birthdate));


d.查询所有的null值
  因为索引里不存null值
e.否定形式：not in、<> !=
   注：not exists可以用索引，因为not exists里用的是一个col1=col2;


3.序列号sequence
在表中的某一列定义了primary key或unique约束，为了保证生成唯一值，引入了sequence。
事实上程序员获得唯一值有两种方式：1.使用sequence 2.在程序中用代码实现
但第一总方式更好。
1）sequence的用法步骤：
   step1：创建sequence对象
   create sequence s
   start with 1 // 起始值为1，必须为整数值！
   increment by 1   // 每一次增长1
   maxvalue 5   // 最大值为5
   [nocycle] // 缺省不循环，取值超过最大值就不取了，报错
   [cycle]       // 循环，取值超过最大值时再从最小值循环取，但不能保证取值唯一了
   [cache 20]        // 此时要保证maxvalue比cache值要大，为什么？老师真心没讲清楚
    step2：往表里插入值时，使用nextval来生成唯一值
    insert into test values(s.nextval); // 由于是第一次，所以插入的值为1
   注：当生成值大于maxvalue时，如果再使用nextval生成值，会报错。
     
    
小技巧：
select object_type from user_objects
where object_name = 's';

查询名字为‘s’的数据库对象是什么类型的对象(table，vie还是sequence?)