oracle学习笔记(三)

原作者: [db:作者] 来自: [db:来源] 收藏邀请

一、创建和管理表
1、创建表语法
create table 表名(column datatype [default expr][,column ...])
default 约束说明略

2、利用子查询创建表
create table 表名
as
select * from u表

二、修改表
1、添加一个新列
oracle：alter table table_name
add(column datatype [default expr][,column datatype]...);

2、修改现有的列(修改类型，大小，默认值)
oracle:alter table table_name
modify(column datatype [defaullt expr][,column datatype]...)

3、删除列
alter table table_name drop column(关键字) 列1
集中删除列
alter table table_name set unused column 列1
alter table table_name drop unused columns;

三、删除表
drop table 表名;删除表后自动提交，不能rollback回来。
重名表:rename 表名 to 新表名
高效清空一张表，用truncate 删除时不做日志记录也就是不能用rollback回滚回来。
truncate table 表名

用delete删除后，可以后悔，回滚回来。

给一张表做注释
comment on table tb_u_1 is '用户表用来存放用户信息....'

四，添加约束
create table table_name
(id int not null,
lname varchar（20），
fname varchar（20）,
constraint un_key_1 unique(lname,fname)
)

alter table 表名 add constraint ch_1 check(column1 like 'k%');
在添加数据时如果column1的数据中没有k则添加失败

添加主键约束
oracle/sql:aleter table 表名1 add constraint pk_1 primary key(column1);
添加外键
oracle/sql:aleter table 表名2 add constraint fk_1 foreign key(column1) references 表名1（column1）;

删除主键约束
oracle/sql:alter table tb_name drop constraint p1
alter table 表名2 drop constraint fk_1 cascade;
sql:查询主键是否存在p1
if exists(select * from sysobjects where name='p1')

select * from user_constraint

select constraint_name,column_name from user_cons_columns where table_name=''

五、创建视图Create View

create [or replace] [force|noforce] view view_name
[]
as subquery
[with check option [constraint 11]]
[with read only [constraint 22]]

创建一个视图或重定义一个视图
create or replace view view_name as subquery

如果表table_1不存在，则使用force 创建视图会成功，否则会报表不存在错误。
create force view v_name as select * from table_1

使用视图修改数据
create view view_001
as
select * from table_001 where id<10
修改不在视图范围内的数据同样会成功。
update view_001 set column1='123' where id=10;
修改不在视图范围内的数据则会失败（只能修改id<10），报：“视图 with check option 违反 where 字句”
create view view_001
as
select * from table_001 where id<10
with check option;
update view_001 set column1='123' where id=10;

创建只读视图 Create a read only view
create view view_001
as
select * from table_001 where id<10
with read only;

删除视图 Drop View
drop view view_Name

临时视图 inline views
select * from (select * from table_Name)

六、序列(sequence)、索引、同义词

创建序列(sequence)
create sequence seq_name
[increment by n]
[start with n]
[{maxvalue|nomaxvalue}]
[{minvalue|nominvalue}]
[{cycle|nocycle}]
[{cache|nocache}]
create sequence seq_test1
increment by 1
start with 1
查询序列Select a Sequence
select seq_test1.currval from daul
select seq_test1.nextvall from daul
select * from user_sequences(用户序列视图)

使用序列Using a Sequence
create table tb_1(a int);
insert into tb_1(seq_test1.currval);

修改序列Modifying a Sequence
alter sequence seq_test1
increment by 20
maxvalue 999999
nocache
nocycle;

创建索引Creating an Index
在一列或多列上创建索引Create an index on one or more columns
Create index index_test1
on table(column[,column]...);

索引能提高数据检索的速度，但是降低了UPDATE,DELETE,INSERT数据操作的性能！！！

索引创建原则（摘自csdn）

一.B-Tree索引:
1. 选项择索引字段的原则:
l 在WHERE子句中最频繁使用的字段
l 联接语句中的联接字段
l 选择高选择性的字段(如果很少的字段拥有相同值,即有很多独特值,则选择性很好)
l ORACLE在UNIQUE和主键字段上自动建立索引
l 在选择性很差的字段上建索引只有在这个字段的值分布非常倾斜的情况下下才有益(在这种情况下,某一,两个字段值比其它字值少出现很多)
l 不要在很少独特值的字段上建B-TREE索引,在这种情况下,你可以考虑在这些字段上建位图索引.在联机事务处理环境下,所由并发性非常高,索引经常被修改,所以不应该建位图索引
l 不要在经常被修改的字段上建索引.当有UPDATE,DELETE,INSETT操作时,ORACLE除了要更新表的数据外,同时也要更新索引,而且就象更新数据一样,或产生还原和重做条目
l 不要在有用到函数的字段上建索引,ORACLE在这种情况,优化器不会用到索引,除非你建立函数索引
l 可以考虑在外键字段上建索引,这些索引允许当在主表上UPDATE,DELETE操作时,不需要共享子表的锁,这非常适用于在父表和子表上有很多并发的INSERT,UPDATE和DELETE操作的情况
l 当建立索引后,请比较一下索引后所获得的查询性能的提高和UPDATE,DELETE,INSERT操作性能上的损失,比较得失后,再最后决定是否需建立这个索引
2. 选择建立复合索引
复合索引的优点:
l 改善选择性:复合索引比单个字段的索引更具选择性
l 减少I/O:如果要查询的字段刚好全部包含在复合索引的字段里,则ORACLE只须访问索引,无须访问表
什么情况下优化器会用到复合索引呢?
(a)当SQL语句的WHERE子句中有用到复合索引的领导字段时,ORACLE优化器会考虑用到复合索引来访问.
(b)当某几个字段在SQL语句的WHERE子句中经常通过AND操作符联合在一些使用作为过滤谓词,并且这几个字段合在一起时选择性比各自单个字段的选择性要更好时,可能考虑用这几个字段来建立复合索引.
(c)当有几个查询语句都是查询同样的几个字段值时,则可以考虑在这几个字段上建立复合索引.
复合索引字段排序的原则:
l 确保在WHERE子句中使用到的字段是复合索引的领导字段
l 如果某个字段在WHERE子句中最频繁使用,则在建立复合索引时,考虑把这个字段排在第一位(在CREATE INDEX语句中)
l 如果所有的字段在WHERE子句中使用频率相同,则将最具选择性的字段排在最前面,将最不具选择性的字段排在最后面
l 如果所有的字段在WHERE子句中使用频率相同,如果数据在物理上是按某一个字段排序的,则考虑将这个字段放在复合索引的第一位
二.位图索引
什么情况下位图索引能够改善查询的性能呢?
l WHERE子句包含多个谓词于中低基数的字段
l 单个的谓词在这些中低基数的字段上选取大量的行
l 已经有位图索引创建于某些或全部的这些中低基数的字段上
l 被查询的表包含很多行
l 可以在单一个表上建立多个位图索引,因此,位图索引能够改善包含冗长WHERE子句的复杂查询的性能,在合计查询和星形模型的联接查询语句中,位图索引也可以提供比较优良的性能
位图索引与B-TREE索引的比较
l 位图索引更节省存储空间
l 位图索引比较适用于数据仓库环境,但不适于联机事务处理环境.在数据仓库环境,数据维护通常上通过批量INSERT和批量UPDATE来完成的,所以索引的维护被延迟直互DML操作结束.举例:当你批量插入1000行数据时,这些插入的行被放置到排序缓存中(SORT BUFFER),然后批处理更新这1000个索引条目,所以,每一个位图段在每一个DML操作中只需更新一次,即使在那个位图段里有多行被更新
l 一个健值的压缩位图是由一个或多个位图段所组成,每一个位图段大约相当于半个BLOCK SIZE那么大,锁的最小粒度是一个位图段,在联机事务处理环境,如果多个事务执行同时的更新(即并发的更新),使用位图索引就会影响UPDATE,INSERT,DELETE性能了
l 一个B-TREE索引的条目只包含一个ROWID,因此,当一个索引条目被锁定,即一行被锁定.但是对于位图索引, 一个索引条目潜在地有可能包含一段ROWID(即某一个范围内的ROWID,有多个ROWID),当一个位图索引条目被锁定时,则这个条目包含的那一段ROWID都被锁定,从而影响并发性.当一个位图段内的ROWID的数量越多时,并发性就越差.虽然如此,对于BULK INSERT,UPDATE和DELETE,位图索引的性能还是比B-TREE索引要好
三,索引和NULL
NULL值在索引中是被看做一个独特值的除非当一个索引的两行或多行的NON-NULL值是相等的情况下.在那种情况下,行被看做是相等的,因此,唯一索引不允许行包含空值以怕被看做是相等的.但是,当所有的行都是空值时,这个规则就不适用.ORACLE并不索引所有健值都为NULL的表的行,除非是位图索引或当簇键字段值是NULL时

创建同义词Create a Synongms
create [public] synonym sy_name for object
创建一个别名为table_001
create synonym tb1 for table_001
删除同义词Drop a synonym
Drop synonym tb1
Oracle同义词创建及其作用（摘自https://www.ogeek.net/database/201109/106257.html）

Oracle的同义词（synonyms）从字面上理解就是别名的意思，和试图的功能类似，就是一种映射关系。本文介绍如何创建同义词语句，删除同义词以及查看同义词语句。
　　oracle的同义词总结：
　　从字面上理解就是别名的意思，和试图的功能类似。就是一种映射关系。
　　1.创建同义词语句：
　　create public synonym table_name for user.table_name;
　　其中第一个user_table和第二个user_table可以不一样。
　　此外如果要创建一个远程的数据库上的某张表的同义词，需要先创建一个Database Link(数据库连接)来扩展访问，然后在使用如下语句创建数据库同义词：create synonym table_name for table_name@DB_Link;
　　当然，你可能需要在user用户中给当前用户(user2)授权： grant select/delete/update on user2
　　2.删除同义词：
　　drop public synonym table_name;
　　3.查看所有同义词：
　　select * from dba_synonyms
　　同义词拥有如下好处：节省大量的数据库空间，对不同用户的操作同一张表没有多少差别;扩展的数据库的使用范围，能够在不同的数据库用户之间实现无缝交互;同义词可以创建在不同一个数据库服务器上，通过网络实现连接。
Oracle数据库中提供了同义词管理的功能。Oracle同义词是数据库方案对象的一个别名，经常用于简化对象访问和提高对象访问的安全性。

AD：

在Oracle中对用户的管理是使用权限的方式来管理的，也就是说，如果我们想使用数据库，我们就必须得有权限，但是如果是别人将权限授予了我们，我们也是能对数据库进行操作的，但是我们必须要已授权的表的名称前键入该表所有者的名称，所以这就是比较麻烦的，遇到这种情况，我们该怎么办呢？创建个Oracle同义词吧！这样我们就可以直接使用同义词来使用表了。
1.同义词的概念
Oracle数据库中提供了同义词管理的功能。同义词是数据库方案对象的一个别名，经常用于简化对象访问和提高对象访问的安全性。在使用同义词时，Oracle数据库将它翻译成对应方案对象的名字。与视图类似，同义词并不占用实际存储空间，只有在数据字典中保存了同义词的定义。在Oracle数据库中的大部分数据库对象，如表、视图、同义词、序列、存储过程、包等等，数据库管理员都可以根据实际情况为他们定义同义词。
2.Oracle同义词的分类
Oracle同义词有两种类型，分别是公用Oracle同义词与私有Oracle同义词。
1)公用Oracle同义词:由一个特殊的用户组Public所拥有。顾名思义，数据库中所有的用户都可以使用公用同义词。公用同义词往往用来标示一些比较普通的数据库对象，这些对象往往大家都需要引用。
2)私有Oracle同义词:它是跟公用同义词所对应，他是由创建他的用户所有。当然，这个同义词的创建者，可以通过授权控制其他用户是否有权使用属于自己的私有同义词。
3.Oracle同义词创建及删除
创建公有Oracle同义词的语法：Create [public] synonym 同义词名称 for [username.]objectName；
Drop [public] synonym 同义词名称
4.Oracle同义词的作用
1) 多用户协同开发中，可以屏蔽对象的名字及其持有者。如果没有同义词，当操作其他用户的表时，必须通过user名.object名的形式，采用了Oracle同义词之后就可以隐蔽掉user名，当然这里要注意的是：public同义词只是为数据库对象定义了一个公共的别名，其他用户能否通过这个别名访问这个数据库对象，还要看是否已经为这个用户授权。
2) 为用户简化sql语句。上面的一条其实就是一种简化sql的体现，同时如果自己建的表的名字很长，可以为这个表创建一个Oracle同义词来简化sql开发。
3)为分布式数据库的远程对象提供位置透明性。
5.Oracle同义词在数据库链中的作用
数据库链是一个命名的对象，说明一个数据库到另一个数据库的路径，通过其可以实现不同数据库之间的通信。
Create database link 数据库链名 connect to user名 identified by 口令 using ‘Oracle连接串'; 访问对象要通过 object名@数据库链名。同义词在数据库链中的作用就是提供位置透明性。