oracle5 alter、序列、视图、索引

1.alter

1.1 概述

在表创建好的情况下,可以使用alter关键字,来修改表的信息

主要包括:

• 在表中添加新列
• 删除表中的列
• 给表中添加约束
• 删除表中的约束
• 修改表名
• 修改列的数据类型
• 设置约束失效

注意,alter属于DDL语句,会结束当前事务

1.2 使用

测试用的表，如下：

create table t_user(
id number constraint user_id_pk primary key,
name varchar2(100),
salary number
);
drop table t_user;

例如,在表中添加一个新的列

alter table t_user
add birthday date;

例如，删除表的某列

alter table t_user
drop column birthday;

例如，给表中的列添加约束

alter table t_user
add constraint user_name_un
unique(name);

注意，这个约束相当于之前的表级约束

然后，再测试刚添加的唯一约束是否生效

insert into t_user(id,name) values(1,'zs');
insert into t_user(id,name) values(2,'zs');

例如，删除表中的约束

alter table t_user
drop constraint user_name_un;

例如，修改表的名字

rename t_user to mytest;
rename mytest to t_user;

例如,修改表中某列的数据类型

alter table t_user
modify(name varchar2(500))

例如，截断表中的数据(删除)

truncate table t_user;
-- 相当于:
delete from t_user;
commit;

truncate操作，不需要提交，默认已经提交，并且不能回滚，因为truncate属于DDL操作

2.注释

在表已经创建完成之后，可以使用 comment 关键字，给表或者列添加注释，将来这个注释还可 以查询出来。

例如，给表添加注释

comment on table t_user is '很好';

例如，给列添加注释

comment on column t_user.name is 'good';

例如，查看表中注释

select * from user_tab_comments where table_name=upper('t_user');

例如，查看列中的注释

select * from user_col_comments
where
comments is not null
and
table_name=upper('t_user');

3.序列

3.1概述

序列（Sequence），它也是一种数据库对象。

它作用主要用来帮助表去创建自动增长的主键。 序列是oracle数据库所特有的对象，其他数据库中是没有的。

3.2创建

创建序列的默认语法:

create sequence 序列名;

索引也可以设置数值:

create sequence 序列名
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}]

说明，

1. INCREMENT BY ，用于定义序列的步长，如果省略，则默认为1
2. START WITH ， 定义序列的初始值(即产生的第一个值)，默认为1
3. MAXVALUE 定义序列能产生的最大值。NOMAXVALUE是默认值，代表没有最大值定义
4. MINVALUE 定义序列能产生的最小值。NOMAXVALUE是默认值，代表没有最小值定义
5. CYCLE 和 NOCYCLE 表示当序列的值达到限制值后是否循环
6. CACHE 定义存放序列值的缓冲区的大小，默认为20。 NOCACHE 表示不对序列进行内存缓冲

3.3 使用

使用序列产生id值

insert into t_user(id,name,salary) values(t_user_seq.nextval,'tom',2000);

查询当前用户的序列

select sequence_name
from user_sequences;

3.4 删除

删除序列语法：

drop sequence 序列名

4.视图

4.1 概述

视图（view），它也是一种数据库对象

视图其实就是提取一张表或者多张表的数据生成一个映射。

操作视图从而达到操作原表的效果，方便数据管理和安全操作。

视图的主要作用是隐藏表中的重要数据、代替比较长的sql语句。

4.2 分类

视图分为俩种：

• 简单视图
• 复杂视图

简单视图：

视图所代表的sql中，如果没有group by语句，没有组函数，查询的只有一张表，那么这样的视图就是简 单视图

复杂视图:

视图所代表的sql，中如果有group by语句，或者有组函数，或者查询的是多张表，那么这样的视图就是 复杂视图

简单视图和复杂视图的区别：

通过简单视图可以修改原来表中的数据，如果复杂视图是多表,且提取的数据没有任何关系则删除视图不影响原表,如果多表的数据存在键值连接,删除视图内容会影响存在外键(有键值)的原表.

4.3创建

创建前需要当前用户有权限,如果没有权限需要登录system用户进行授权

grant create view to user_name;

创建视图，语法：

create or replace view 视图名字
as
sql语句

简单视图:

create or replace view v_test
as
select id,last_name,salary
from t_user
where id < 10;

查看视图内容

select * from v_test;

可以通过 简单视图 对原来的表进行数据的删除、更新、插入

delete from v_test where id=6;
update v_test set last_name = 'zhangsan' where id = 8;
insert into v_test(id,last_name,salary) values(6,'tom1',3000);
select * from v_test;

复杂视图:

例如:

create or replace view v_test
as
select avg(salary) avgSal
from t_user;

复杂视图只能用来查询，不能修改

select * from v_test;

修改数据（失败）

update v_test
set avgSal=2022;

此时会直接报错：ORA-01732: 此视图的数据操纵操作非法

4.4 删除

删除视图的语法：

drop view 视图名称

drop view v_test;

5.索引

5.1 概述

索引的概念:

• 类似书的目录结构
• oracle的索引是一种对象,是与表关联的可选对象,能提高sql查询语句的速度
• 索引直接指向包含所查询值的行的位置,减少磁盘I/O
• 索引和表是相互独立的物理结构
• oracle自动使用并维护索引,插入 删除更新表后,自动更新索引.

5.2 原理

例如：一个表中有name字段，假设要查找name='tom’的数据，但是数据可能会有多条

1. 如果没有索引，查找这个记录时，需要搜索表中所有的记录，因为不能保证只有一个tom，那么 就必须将表中数据全部搜索一遍
2. 如果在name上建立索引，oracle会对全表进行一次搜索，将每条记录的name值在什么位置按照一 定的规则进行排列，然后构建索引条目，并存储起来，在查询name为tom时，可以直接查找该数 据所在的对应地方。

注意，创建了索引并不一定就会使用，因为oracle在自动统计表的信息后，会决定是否使用索引， 表中数据很少时，使用全表扫描速度已经很快了，那么就没有必要使用索引了。

5.3 创建

创建索引有两种情况:

1. 自动创建索引

   当在表中指定了primary key 或者unique约束时,会自动创建唯一值索引

2. 用户创建索引

   用户可以创建非唯一值索引以提高在访问数据时的效率.

语法要求:

create index 索引名
on 表名(列名);

索引创建成功后,索引就由数据库来管理,用户无法控制

在某一个列上加入上了索引，那么也只有在数据量很大的时候，才能有所体现出这个查询 的效率

给某列创建索引的原则:

1. 列经常作为where子句的限定条件或者作为连接条件
2. 列包含的数据量很多,并且很多非空的值.
3. 在经常需要order by,group by,distinct列上创建索引,可以利用索引加快排序查询时间.
4. 要建立索引的表不经常进行修改操作
5. 索引不是越多越好，不是索引越多越能加速查找。

5.4 删除

删除索引的语法：

drop index 索引名;

5.5 种类

常见的索引种类有：

1. 唯一值索引 unique index

   创建索引时候 列的约束时唯一约束或者时主键约束

2. 非唯一值索引 nounique index

   创建索引时候 列的约束时不是唯一约束或者不是主键约束

3. 单行索引 single index

4. 多行索引 concatenated index

5.6 结构

索引的常见结构有:

1. B-tree
2. 位图
3. 反序
4. 函数

B-tree索引:

默认的索引就是这种结构

1. 适合大量的增 删 改
2. 不能用包含or操作符的查询