三、常见的数据库对象

1、常见的数据库对象

表    基本的数据存储集合，由行和列组成。

视图   从表中抽出的逻辑上相关的数据集合。

序列   提供有规律的数值。

索引   提高查询的效率  

同义词   给对象起别名

详解

    序列: 可供多个用户用来产生唯一数值的数据库对象，自动提供唯一的数值共享对象，主要用于提供主键值

将序列值装入内存可以提高访问效率。

2、序列：

定义序列的语法:

Create sequence 序列名

    Increment by 数字  --每次增长的数值

    Start with  数字   --从那个值开始

    Maxvalue  数字|nomaxvalue

    Minvalue  数字|nominvalue

    Cycle|nocycle      --是否需要循环

    Cache 数字|nocache --是否产生序列号预分配，并存储在内存中

    Order |noorder     --可保证生成的序列值是按照序列产生的

|实例1|

步骤1：

创建序列：student_xuehao_seq

create sequence student_xuehao_seq

       increment by 1

       start with 1

       maxvalue 9999

       nocache

       nocycle;

备注：在使用序列时，需要用到序列的两个伪序列：NEXTVAL与CURRVAL

NEXTVAL:返回序列生成的下一个序列号值

CURRVAL:返回序列的当前序列号值

 

步骤2:

insert into t_emp(employee_id,name,sal)

values(student_xuehao_seq.nextval,'马达',30000);

select * from t_emp where name='马达';--此时马达的employee_id是1

备注：实际中在为表生成主键时，通常是为表创建一个行级触发器，然后再触发器主体中使用序列值替换用户提供的值。

|实例2|

管理序列：使用alter sequence对序列进行修改，除序列的起始值不能被修改外，其他的都进行修改。如要修改序列的起始值需要先删除序列，然后重建该序列。

--修改序列

alter sequence student_xuehao_seq

  increment by 17;

insert into t_emp(employee_id,name,sal)

values(student_xuehao_seq.nextval,'马达',30000);

select * from t_emp where name='马达';--此时马达的employee_id是18

--查询序列

desc user_sequences;-- user_sequences为数据字典的名字

--删除序列

Drop sequence student_xuehao_seq;

备注：

oracle中可以用create or replace的对象有：functions, procedures, packages, types, synonyms, trigger and views，就是没有table，也没有sequence。所以这里会提示选项缺失或无效。如果表不存在则直接去掉or replace进行表的创建，如果表已经存在，则先删除后再进行创建。

 

3、索引

1）索引的理解：

一种独立于表的模式对象, 可以存储在与表不同的磁盘或表空间中，索引被删除或损坏, 不会对表产生影响, 其影响的只是查询的速度。索引一旦建立, Oracle 管理系统会对其进行自动维护, 而且由 Oracle 管理系统决定何时使用索引。用户不用在查询语句中指定使用哪个索引。在删除一个表时,所有基于该表的索引会自动被删除。通过指针加速 Oracle 服务器的查询速度。通过快速定位数据的方法，减少磁盘 I/O。

查询时，没有索引情况下，必须遍历整个表，然后将读取的每条记录与查询条件进行对比，最后返回满足条的记录，如果查询条件中有索引取值条件，就可以通过保存在索引中的rowid快速找到表中对应的记录。

2）索引建立的原则：

         a、在大表上建立索引

b、在where字句或是连接条件上经常引用的列上建立索引

c、索引的层次不需要超过4层  

3）索引的缺点：

a、建立索引，系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引；

b、更新数据的时候：系统必须要有额外的时间来同时对索引进行更新，以维持数据和索引的一致性

不恰当的索引不但于事无补，反而会降低系统性能，因为大量的索引在进行插入、修改和删除操作时，

比没有索引花费更多的系统时间；

 

|实例1|

创建索引：

自动创建: 在定义 PRIMARY KEY 或 UNIQUE 约束后系统自动在相应的列上创建唯一性索引

手动创建: 用户可以在其它列上创建非唯一的索引，以加速查询

 

--创建单例索引：是基于单个列所建立的索引，

create index 索引名 on 表明（列名字）

create index goodsIndex on goods(GOODSID);

create index emp_id_index on t_emp(employee_id);

建索引以后相当于它会自动的将这张表按照该索引所在的列名进行排序；

 

--创建复合索引：复合索引是基于两列或是多列的索引，在同一张表上可以有多个索引但是要求列的组合必须不同

create index emp_indx1 on emp (ename,job);

 

--查看索引：
select index_name,table_name,table_owner,tablespace_name from user_indexes where table_name='T_EMP';

Dba_indexes:可显示数据库的所有索引

All_indexes:可以显示当前用户可以访问的所有索引

User_indexes:可以显示当前用户的索引信息

 

--删除索引

drop index EMP_ID_INDEX;

--如何显示系统权限：

select * form system_privilege_map order by name;

4、同义词-synonym

同义词是表、索引、视图等模式对象的一个别名。与视图、序列一样，同义词只在oracle数据字典中保存其定义描述，因此同义词也不占用任何的存储空间。

--创建私有同义词：需要具有create synonym 的权限

 create synonym private_t_emp for t_emp;

--使用同义词进行表的访问：

select * from private_t_emp;

--删除私有同义词：

drop synonym private_t_emp;

--创建公有同义词：

create public synonym public_t_dept for zhangjunbo.t_emp;

--使用同义词进行表的访问:

select * from public_t_dept;

--删除公有同义词：

drop public synonym public_t_dept;

5、  视图

1）视图概念：视图是一个虚拟表，它由存储的查询构成，可以将它的输出看做一个表。视图同真实表一样，也可以包含一系列带有名称的列和行数据。但是视图并不在数据库中存储数据值，其数据值来自定义视图的查询语句所引用的表，数据库只在数据字典中存储视图的定义信息。

视图也可以进行insert、update、delete 操作，通过视图修改数据时，实际上就是修改基本表中的数据，与之相对应，改变表中的数据也会反应到由该表组成的视图中。

2）创建视图的语法：

create [or replace] view <视图名字> [视图列明的别名,视图的列明的别名,...]

as                                                                          --subquery用于指定视图对应的子查询语句

[with check option] [constraint constraint_name]    --with check option用于指定在视图上定义的check约束

with read only                                                                             --定义只读视图

 

|实例1|

1）简单视图：指基于单表建立的，不包含任何的函数、表达式和分组数据的视图。

--授权

grant create view to zhangjunbo;

--创建视图

create or replace view t_emp_view as

select * from t_emp where sal>9000;

创建视图时，只是将视图的定义信息存入到数据字典，并不会执行其中的select语句，在对视图进行查询时，系统才会根据视图的定义才会从基本表中获取数据。

--查看视图

select * from t_emp_view;

备注：由于在创建视图的时候没有给t_emp_view视图指定别名，所以视图的列明分别为t_emp表中列的名字

--给视图添加数据：

insert into t_emp_view

values(21,'慢慢','12232');

2）复杂视图：指包含函数、表达式或分组数据的视图。创建复杂视图主要是为了简化查询操作。

注意：当视图子查询包含函数或者表达式时，必须为其定义列别名。

--创建复杂视图

create or replace view student_view_complex as

select classid 班级,max(sal) 最高工资,avg(sal) 平均工资

from student

group by classid

--查看复杂视图：

Select * from student_view_complex;

--创建连接视图

准备工作

create  table student_info(stu_no,stu_name,stu_sal) as select xuehao,xingming,sal from student;

 

select st.xuehao,si.stu_name,si.stu_sal from student st,student_info si where st.xuehao=si.stu_no;

创建视图

create or replace view student_info_union as

select st.xuehao,si.stu_name,si.stu_sal from student st,student_info si where st.xuehao=si.stu_no;

查看视图

select * from student_info_union;

3）管理视图：查看视图、修改视图、重新编译视图和删除视图

--查看视图定义的信息：

select text,uv.* from user_views uv where view_name=upper('student_info_union');

--修改视图：

create or replace view student_info_union as

select st.xuehao,si.stu_name,si.stu_sal from student st,student_info si where st.xuehao=si.stu_no;

--重新编译视图：

alter view student_info_union compile;

--删除视图：

drop view student_info_union;

 

6、表

- - 查看用户定义的表：

select ut.table_name,ut.tablespace_name from user_tables ut;

 

--查看用户定义的各类数据库对象：

select distinct object_type from user_objects;

select object_name, object_type from user_objects;

--查看用户定义的表, 视图, 同义词和序列:

select * from user_catalog;

--查看表结构：

desc student;

 

--删除表

drop table student

方法一：drop table student；

表的结构和数据都被删除，所有相关索引被删除，所有正在运行的相关事务被提交；

drop table 语句不能被回滚

 

方法二：truncate table student

删除表中的所有记录，表结构还在，不写日志，释放表的存储空间，无法找回删除的记录，速度快；

Truncate table不能回滚

 

--给表中添加字段：

alter table student add (classId number(2));

alter table student add (fenshu number(10));

 

--修改字段的长度：

alter table 表明 modify (fenshu varchar2(30));

 

--修改字段的类型/或是名字(不能有数据)

alter table student modify (fenshu char(30));

 

--删除一个字段：

alter table student drop column address;;

 

--修改表的名字：

rename student to stu;

 

--向表中添加数据：

insert into student values ('A001','张三'，'男'，'01-5月-05'，10);

 

--把查询的结果一次性插入到另外一个表中

insert into t_emp   select xuehao,xingming,sal from student;

 

--改日期的默认格式：

alter session set nls_date_format ='yyyy-mm-dd';

oracle 中默认的日期格式'DD-MON-YY' dd日子（天），mon 月份，yy 2位的年'09-6月-99'表示1999年6月9号

修改后，可以用我们熟悉的格式添加日期类型；

insert into  student values ('A002','MIKE','男'，'1905-05-06',10);

 

--设置保存点：

savepoint drop_student;

delete from student;

rollback to drop_student;

备注：

1）设置保存点后，不能进行提交否则当你回滚的时候提示："从未创建保存点 'drop_student'"

2）CREATE TABLE 创建表

ALTER TABLE 修改表结构

DROP TABLE 删除表    

RENAME  TO   重命名表

TRUNCATE TABLE 删除表中的所有数据，并释放存储空间

注意:以上这些DDL的命令，就算设置了保存点，操作后，皆不可回滚！