oracle 创建同义词_序列、索引、同义词、用户

一.序列

1.序列的定义

• 序列是用户创建的数据库对象，序列会产生唯一的整数。序列的一个典型的用途是创建一个主键的值，它对于每一行必须是唯一的。序列由一个Oracle 内部程序产生并增加或减少
• 序列是一个节省时间的对象，因为它可以减少应用程序中产生序列程序的代码量。序列号独立于表被存储和产生，因此，相同的序列可以被多个表使用

2.创建序列

INCREMENT BY n ：序列增量的步长

START WITH n：要产生的第一个序列数 (如果该子句被省略，序列从 1 开始)

MAXVALUE n：指定序列能产生的最大值

NOMAXVALUE：对于升序序列指定 10^27 为最大值，对于降序序列指定-1 为最大值(默认)

MINVALUE n：指定最小序列值

NOMINVALUE：对于升序序列指定 1 为最小值，对于降序序列指定-(10^26)为最小值(默认)

CYCLE|NOCYCLE：指定序列在达到它的最大或最小值之后，是否继续产生【循环产生从头开始的与原来一样的序列】(NOCYCLE 是默认选项)

CACHE n|NOCACHE：指定 Oracle 服务器预先分配多少值，并且保持在内存中(默认情况下，Oracle 服务器缓冲20个值)

e.g. 创建一个序列名称为：dept_seq,增长间隔为10，从 120 开始，最大值为 9999，不缓存。不循环使用

create sequence dept_seq increment by 10 start with 120 maxvalue 9999 nocache nocycle;

注意：nocache 和nocycle的先后顺序没关系

---

当然我们也可以通过可视化工具创建序列

3.操作序列

3.1查询序列

Last_number显示的是下一次你去取值是多少

e.g.

select sequence_name,increment_by,max_value,min_va lue,last_number from user_sequences;

3.2使用序列

首先我们先介绍一下 NEXTVAL 和 CURRVAL 伪列

• NEXTVAL 返回下一个可用的序列值，它每次返回一个唯一的被引用值，即使对于不同的用户也是如此
• CURRVAL 获得当前的序列值
• 在 CURRVAL 获得一个值以前，NEXTVAL 对该序列必须发布【我CURRVAL要想获得这个序列当前的值，前提条件是NEXTVAL必须已经产生过这个值了，例如：一个新创建的序列还没有用NEXTVAL取值，但是我就想用CURRVAL取值，这时会报错的】

e.g. 在 location ID 2500 中插入一个新部门名称 Support

insert into departments(department_id,department_name,location_id) values(dept_seq.nextval,'Support',2500);

4.修改与删除序列

4.1修改序列

4.2修改序列的原则

• 必须是被修改序列的所有者，或者有 ALTER 权限
• 用 ALTER SEQUENCE 语句，只有以后的序列数会受影响（前面已经生成的不会有改变了）
• 用 ALTER SEQUENCE 语句，START WITH 选项不能被改变。为了以不同的数重新开始一个序列，该序列必须被删除和重新创建

e.g. 将 dept_seq 序列中的增长量修改 20，最大值修改为 999999

alter sequence dept_seq increment by 20 maxvalue 999999 nocache nocycle;

4.3删除序列

e.g. 删除 dept_seq 序列

drop sequence dept_seq;

二.索引

1.索引的概念

索引指的就是目录，以字典的目录进行比喻：创建索引就是把表中的谁加入加到索引中。建索引的目的就是为了加快查询速度

专业的说就是：在关系型数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

索引提供对表中行的直接和快速访问，它的目的是用已索引的路径快速定位数据以减少磁盘I/O。索引由 Oracle 服务器自动使用和维护，索引逻辑地和物理地独立于他们索引的表，这意味者索引可以在任何时候被创建或删除，并且不影响基表或其它的索引。当删除表时，相应的索引也被删除

2.索引的类型

• 唯一性索引：当你在一个表中定义一个列为主键，或者定义一个唯一键约束时 Oracle 服务器自动创建该索引，索引的名字习惯上是约束的名字
• 非唯一索引：由用户创建，例如，可以创建一个 FOREIGN KEY 列索引用于一个查询中的连接来改进数据取回的速度

3.创建索引的方式

• 自动：在一个表的定义中，当定义一个 PRIMARY KEY 或 UNIQUE 约束时，一个唯一索引被自动创建
• 手动：用户能够在列上创建非唯一的索引来加速对行的访问

4.使用索引

适当地使用索引，不要滥用

在表上建立更多的索引并不意味者更快地查询，在带索引的表上被提交的每个 DML 操作意味者索引必须更新；与表联系的索引越多，对 Oracle 数据库的（性能）影响越大，Oracle 数据库在每次 DML 操作之后必须更新所有的索引

4.1建议当此时建索引

• 一个列包含一个大范围的值
• 一个列包含很多的空值（索引不存空值）
• 一个或多个列经常同时在一个 WHERE 子句中或一个连接条件中被使用表很大，并且经常的查询期望取回少于百分之 2 到 4 的行

4.2建议以下情况不要建立索引

• 表很小
• 不经常在查询中作为条件被使用的列
• 大多数查询期望取回多于表中百分之 2 到 4 的行
• 表经常被更新
• 被索引的列作为表达式的的一部分被引用（例如：<>、+、-、*、/...进行运算了不走索引）

5.操作索引

5.1非唯一性索引的类型

• 单行索引
• 复合索引(组合索引)
• 函数索引

5.2创建索引的语法

e.g. 为 employees 表中的 last_name 创建一个索引并命名为 emp_index(单行索引)

create index emp_index on employees(last_name);

e.g. 为 departments 表 创 建 一 个 包 括 manager_id 与 location_id 复合索引并命名为dept_man_loc(复合索引:索引中包含两个或者两个以上的列；索引什么时候生效：当你的链接条件中，包含了manager_id和location_id会去执行相应的索引，并且查找的时候要按照创建索引的顺序去写条件)

create index dept_man_loc on departments(manager_id,location_id);

e.g. 为 departments 表中的 department_name 创建一个带有大写函数的索引 dept_upper2(创建函数索引)

create index dept_upper2 on departments(upper(department_name));

这时会走函数索引：

select * from departments d where upper(d.department_name)='aaaa';

5.3查询索引

e.g.

select IC.INDEX_NAME ,ic.COLUMN_NAME,ic.COLUMN_POSITION,ix.uniqueness from user_indexes ix ,
user_ind_columns ic where ix.index_name = ic.INDEX_NAME and ic.TABLE_NAME = 'DEPARTMENTS';

5.4删除索引

e.g. 删除名称为 dept_upper 的索引

drop index dept_upper;

三.同义词

1.同义词的概念

同义词可以除去对象名必须带的方案限制，并提供给你一个可替换表名、视图名、序列 名和存储过程名或其它对象名。该方法对具有特别长的对象的名字很有用。其实就是一个永久的别名

2.创建同义词

e.g.

select * from em;

3.删除同义词

e.g.

drop synonym em;

四.用户

1.用户的概念

• 用户其实就是一个权限的集合
• 用户也是一个对象
• Oracle 用户是用来连接数据库和访问数据库对象的

2.操作用户

2.1创建用户

需要具备创建用户的权限可以使用 sys 或者 system 用户来创建新用户

使用默认表空间（永久表空间就是数据文件。如果我们在创建用户时，并没有给他分配永久表空间，那么这个用户将使用的是oracle默认的永久表空间，我们也可以在创建用户时给他分配一个永久表空间）

e.g. 创建一个用户名称为 u_test，永久表空间使用 Oracle 默认的永久表空间

create user u_test identified by oracle;

e.g. 创建一个用户名为 u_bjsxt,使用 bjsxt 表空间为他的表空间(使用指定表空间)

create user u_bjsxt identified by oracle default tablespace bjsxt temporary tablespace temp;

如果临时表空间不想指定，把temporary tablespace temp去掉即可，系统会给你分配一个默认的临时表空间

2.2删除用户

e.g. 删除 u_test 用户

drop user u_test;

e.g. 删除用户的同时将该用户下的其他对象一并删掉

drop user u_test cascade;