Oracle的对象——表，同义词，序列，视图，索引和簇

最新推荐文章于 2024-09-20 12:42:36 发布

iteye_2382

最新推荐文章于 2024-09-20 12:42:36 发布

阅读量167

点赞数

文章标签： Oracle 数据结构 SQL 能源 Cache

一：表

a.创建表语法格式

create table table_name
(
字段1 类型1,
字段2 类型2,
字段3 类型3（也可以给字段设置默认值）
...
);

create table student
(
 stuno int,
 stuname varchar(10) not null,
 stuBirth date default to_date('1980-1-1','YYYY-MM-DD')
);

查询一下表的结构：desc table_name

SQL> desc student;
Name     Type         Nullable Default                          Comments 
-------- ------------ -------- -------------------------------- -------- 
STUNO    INTEGER      Y                                                  
STUNAME  VARCHAR2(10)                                                    
STUBIRTH DATE         Y        to_date('1980-1-1','YYYY-MM-DD')

b.给已存在的表增加新列：alter table tablen_ame add(列名1 列类型, 列名2 列类型...);

alter table student add(t3 varchar(10),t4 varchar2(10), t5 number(18,2));

查询结果如下：

SQL> desc student;
Name     Type         Nullable Default                          Comments 
-------- ------------ -------- -------------------------------- -------- 
STUNO    INTEGER      Y                                                  
STUNAME  VARCHAR2(10)                                                    
STUBIRTH DATE         Y        to_date('1980-1-1','YYYY-MM-DD')          
T3       VARCHAR2(10) Y                                                  
T4       VARCHAR2(10) Y                                                  
T5       NUMBER(18,2) Y

新增了T3，T4和T5三列。

c.给已存在的表删除一个列：alter table table_name drop column column_name;

alter table student drop column t3;

查询结果将发现T3列已经删除。

d.给已存在的表中的列重命名：alter table table_name rename column ole_col_name to new_col_name;

alter table student rename column t4 to hello;

查询结果将发现T4列已经改名为hello。

e.给已存在的表中的列更改类型：
如果表中无数据：alter table 表名 modify 列名新类型;

alter table student modify(hello number);

查询结果将发现hello列的类型由varchar2类型变成了number类型。

如果表中有数据：
alter table 表名 add 新列名新列类型;
update 表名 set 新列名=旧列名;
alter table 表名 drop column 旧;
alter table 表名 rename column 旧列名 to 新列名;（这句有玄机，好好理解）

这种方法会使列名发生变化,而且字段顺序增加有可能发生行迁移,对应用程序会产生影响,使用起来要慎重。

f.设置某字段无用：alter table table_name set unused column column_name;

alter table student set unused column t5;

这个时候你无论查询表中的数据还是查看表的结构，你都看不到t5这个列了。但是此列仍然存在表中，只不过被Oracle给“关”起来了。无用的字段是无法恢复的，因为这是DDL语句，要想恢复，只能恢复库了。

删除无用字段(会把字段删除掉)：alter table student drop unused column;

alter table student drop unused column;

这时候这个无用字段被Oracle给“枪毙”了。再也不存在了。

g.给表增加约束：alter table table_name add constraint constraint_name primary key(column_name);

给一个表中的某个字段增加主键或者外键约束。

alter table student add constraint pk_stuno primary key(stuno);

删除约束：alter table table_name drop constraint constraint_name;

alter table student drop constraint pk_stuno;

表的数据字典为user_tables,

约束的数据字典为user_constraints,

另外同样也可以给一个表中的多个列同时设置为主键，即复合主键，这里就不做介绍了。

二：同义词

同义词是简化的SQL语句，隐藏对象的名称和所有者，为分布式数据库的远程对象提供了位置透明性，提供对对象的公共访问。其语法如下：
create synonym 命令用于创建同义词
drop synonym 命令用于删除同义词
user_synonyms 包含同义词的数据字段

创建一个同义词，就用上面的student表。如果你的当前用户没有创建同义词的权限，你要首先用SYS用户登录授权。我用的是scott用户，我就需要SYS用户给scott用户授权创建同义词。

grant create synonym to scott;

然后切换回scott用户，创建student表的同义词：

create synonym ghk_student for student;

这个时候，同义词ghk_student和表student效力是一样的。但是我隐藏了student表（还有他的用户scott，我只需要对同义词进行操作即可）。

为了说明问题，向ghk_student表中插入试验数据：

insert into ghk_student values(12, 'huangkai', to_date('19830326','YYYYMMDD'), 322);

这个时候查询student表和ghk_student同义词的结果是一样的。

删除同义词：drop synonym synonym_name;

drop synonym ghk_student;

三：序列

序列是数据库提供一种对象，能够提供自动的连续的唯一的值。其创建于法格式如下：

create sequence sequence_name
	[increment by n] 指定增长间隔数
	[start with n] 初始值
	[{maxvalue n | nomaxvalue}] 最大值，再增长转为起始值
	[{minvalue n | nominvalue}] 最小值
	[{cycle | nocycle}] 是否循环，在最大值和初始值间轮回
	[{cache n | nocache}]; 自动在缓冲区生成，速度快

序列两个重要属性：nextval，currval，其实是两个函数
nextval产生下一个值，初始化序列值；
currval获取当前序列值

下面我们来做实验，创建一个序列：

create sequence numseq increment by 1 start with 1 maxvalue 999;

建个临时表：

create table temp_test(sno int);

然后向表中插入序列，可以反复插入：

insert into temp_test(sno) values(numseq.nextval);
insert into temp_test(sno) values(numseq.nextval);
insert into temp_test(sno) values(numseq.nextval);
insert into temp_test(sno) values(numseq.nextval);
insert into temp_test(sno) values(numseq.nextval);

查询temp_test表中的内容，可以看到sno是连续的1，2，3，4，5。这里我就不截图了。

查询序列当前值：select numseq.currval from dual;

查询序列下一个值：select numseq.nextval from dual;

再次插入序列，看看结果如何？答曰：temp_test中的sno已经出现了跳点，不再是6，7，8……等连续的了。

当你在select numseq.nextval from dual时候序列已经增加，再次插入的时候就是增加后的值了，所以和前面不连续了。但是连续插入的话，插入的值还是连续的。

删除序列语法：drop sequence sequence_name。

序列的数据字典：user_sequence。

四：视图

视图用来显示一个或多个表中的数据。视图不真正存储数据，只是一些查询语句，成为“虚表”或“已存储的查询”。

视图优点：提供另外一种级别的表安全性查询；隐藏数据的复杂性；简化用户的SQL命令；将应用程序与基表定义的修改隔离开来；从另一个角度提供数据。

scott用户没有创建视图的权限，可以用SYS用户授予其权限，授权语句：

grant create any view to scott;

然后切换回scott用户，创建一个视图：

create or replace view v_student as select stuno,stuname from student;

一个视图建立完毕。

这个视图就是截取表中的stuno字段和stuname字段，我们查询视图的内容和查询表中的内容是一样的（除了列数不同）。向表中插入数据在查询视图的时候也能查出来，向视图中插入数据在查询表的时候也能查出来（没有的列以默认或空作为显示，前提是视图中没有的列在表中是允许为空的，否则视图插不进去数据）。

删除视图：drop view view_name;

重新编译视图：alter view view_name compile；

为什么要重新编译呢？
我们创建视图，在后台数据库创建了视图对象，把语句进行编译，把编译后的对象存在视图里面，时间长了以后，可能源表发生变化，导致无效，所以需要定期重新编译视图。

视图的数据字典：user_views。

五：索引

索引，又称为“快表”，提供快速检索表中数据的机制，Oracle有索引段用于存储索引。表更新时，索引亦会连带更新。索引会加快SQL语句的执行，减少磁盘I/O，create index语句用于创建索引，在物理上和逻辑上独立于表中的数据，oracle自动维护索引。

a.唯一值索引：定义在索引列中的值是不重复的；
oracle自动为主键和唯一键创建唯一索引；
create unique index 语句用于创建唯一索引。

给上面的student表创建唯一值索引：

create unique index idx_stuno on student(stuno);

原来student表中的stuno不是主键，也不是唯一的，这回为其建立唯一值索引。向表中插入数据时候若stuno重复会报错。

b.组合索引：将表中某几列字段的值合在一起：

create index idx_stunoandstuname on student(stuno, stuname);

如果我要查询stuno和stuname，Oracle会从索引段中查询，而不是从整个表中查询，提高效率。

c.反向键索引：会把索引表中的值按位反转，适合向表中填入数据而不是更新输入的场合：

create reverse index idx_xx on table(column);

d.位图索引：创建重复率比较大的数据列：

create bitmap index idx_job on emp(job);

索引数据字典：user_indexes。

——（索引需要继续完善）——

六：簇

簇的含义是聚集的意思，其实和表差不多，create cluster语句用于创建簇，应首先创建簇，然后创建组成簇的表。
优点：减少磁盘I/O，节省磁盘空间；
缺点：插入操作的性能降低；

有主外键关系表可以考虑创建簇。

例子：

create cluster class_cluster
(
	classno varchar2(10)
);

create table stuClass(classno varchar2(10), classname varchar2(20))
cluster class_cluster(classno);

create table stuInfo(studentno varchar2(10), stuname varchar(20),
classno varchar2(10)) cluster class_cluster(classno);

说明：首先创建一个簇class_cluster，簇包括一个字段classno。再创建两个表stuClass和stuInfo，这两个表都含有classno字段，类型也和簇一样，语法格式如上。那么向表中操作数据和普通表一样，如果插入数据到表中，那么classno是存储在簇当中的。

另外，删除簇时应先删除含有簇的表。

簇的数据字典：user_clasters。