Oracle的常用数据库对象
数据库对象是数据库的组成部分，常常用create命令进行创建，使用alter命令修改，用drop执行删除操作。本文将记录7个常用的数据库对象，讲解它们的概念和用法等，如下：
1.用户（user）
2.数据库表（table）
3.数据库表的约束（constraints）
4.序列（sequence）
5.索引（index）
6.视图（view）
7.同义词（synonym）
详细如下：
1.用户（user）
1.1用户的创建
语法：create user 用户名 identified by 密码;
示例：create user abortre identified by password;
1.2用户的授权
Oracle中的权限很多，为了方便用户管理权限，提供了角色这个概念。角色代表一个身份，该身份拥有固定的权限。常见的角色：DBA(数据库管理员)，CONNECT(临时用户，拥有少量的权限)，RESOURCE(比较靠谱的权限，拥有更多的权限，不能管理数据库)。
示例：
给abortre用户授予普通用户的权限：
grant connect,resource to abortre;
从abortre用户撤销resource权限：
revoke resource from abortre;
1.3删除用户
drop user abortre;
1.4修改用户
修改用户密码：
alter user abortre identified by password123;
锁定用户：
alter user abortre account lock;
解锁用户：
alter user abortre account unlock;
2.数据库表（table）
2.1Oracle中的数据类型
a.字符类型：
varchar2 可变长度的字符串
char 不可变长度的字符串，效率较高
b.数值类型：
number 既可以表示整数，也可以表示浮点数
c.日期时间类型：
date 存放日期和时间
timestamp 比date更精确的日期时间类型
d.lob类型：
blob 存放二进制数据，例如文件、图片、音频、视频等
clob 用于存放文本信息
2.2创建表
示例：创建学生表，字段包含学号、姓名、性别、年龄、入学日期、班级、email等信息
create table student(
sno number(4),
sname varchar(12),
gender char(3),
age number(3),
sdate date,
clazz varchar2(20),
email varchar2(50)
);
2.3修改表
a.添加字段
alter table student add(score number(3));
b.修改字段类型
alter table student modify(score number(5,2));
c.重命名字段
alter table student rename column score to fenshu;
d.删除表格
drop table student;
3.数据库表的约束（constraints）
约束用于对表格的数据进行限制，保证表格数据的完整性和一致性。语法格式为：
constraints 约束名 约束类型（约束字段）
3.1主键约束（primary key）
主键约束用于唯一标识一条记录的字段，必须保证即非空有唯一，一张表中只能有一个主键。
a.在表级别定义主键约束，对约束进行统一管理
create table student (
sno number(4),
sname varchar2(12),
gender char(3),
age number(3),
sdate date,
clazz varchar2(20),
email varchar2(50),
constraints pk_student primary key(sno)
);
b.在列级别添加主键约束
create table student (
sno number(4) constraints pk_student primary key,
sname varchar2(12),
gender char(3),
age number(3),
sdate date,
clazz varchar2(20),
email varchar2(50)
);
c.在列级别添加主键约束简化版
create table student (
sno number(4) primary key,
sname varchar2(12),
gender char(3),
age number(3),
sdate date,
clazz varchar2(20),
email varchar2(50)
);
d.联合主键，只能在表级别定义
create table student (
sno number(4),
sname varchar2(12),
gender char(3),
age number(3),
sdate date,
clazz varchar2(20),
email varchar2(50),
constraints pk_student primary key(sno,email)
);
3.2非空约束（not null）
字段值不允许为空，非空约束只能在列级别定义
create table student (
sno number(4),
sname varchar2(12) not null,
gender char(3),
age number(3),
sdate date,
clazz varchar2(20),
email varchar2(50),
constraints pk_student primary key (sno)
);
3.3唯一约束（unique）
要求字段值不能重复
create table student (
sno number(4),
sname varchar2(12) not null,
gender char(3),
age number(3),
sdate date,
clazz varchar2(20),
email varchar2(50), -- unique,
constraints pk_student primary key (sno),
constraints uk_student_email unique(email)
);
3.4检查约束（check）
用于限定字段值的取值范围
create table student (
sno number(4),
sname varchar2(12) not null,
gender char(3) check (gender in ('男', '女')),
age number(3),
sdate date,
clazz varchar2(20),
email varchar2(50),
constraints pk_student primary key (sno),
constraints uk_student_email unique (email),
constraints ck_student_age check(age between 18 and 30)
);
3.5外键约束（foreign key)
用于约束表和表之间的关系，两张标的依赖关系，以emp表和dept表为例，emp表依赖dept表，因此，dept表可以被称之为主表，emp表被称之为从表。
注意：
主表中，只有主键和唯一键才能被从表参考；
从表中作为外键的列，类型一定要和主表中被参考的列的类型相同；
a.建立主表
create table clazz (
cno number(3) primary key,
cname varchar2(20) not null,
croom number(3)
);
b.建立从表student并在表级别定义外键
create table student (
sno number(4),
sname varchar2(12) not null,
gender char(3) default '男' check (gender in ('男', '女')),
age number(3),
sdate date,
email varchar2(50),
cno number(3),
constraints pk_student primary key (sno),
constraints uk_student_email unique (email),
constraints ck_student_age check (age between 18 and 30),
constraints fk_student_cno foreign key(cno) references clazz(cno)
);
c.在列级别定义外键
create table student (
sno number(4),
sname varchar2(12) not null,
gender char(3) default '男' check (gender in ('男', '女')),
age number(3),
sdate date,
email varchar2(50),
cno number(3) references clazz(cno),
constraints pk_student primary key (sno),
constraints uk_student_email unique (email),
constraints ck_student_age check (age between 18 and 30)
);
d.外键的删除问题
》主表中的数据在被引用时，无法直接删除，默认情况下，需要先删除主表中的数据
》可以在定义外建时，设置删除策略为cascade（级联），表示当删除主表的信息时，同时删除从表中的所有关联的信息
create table student (
sno number(4),
sname varchar2(12) not null,
gender char(3) default '男' check (gender in ('男', '女')),
age number(3),
sdate date,
email varchar2(50),
cno number(3),
constraints pk_student primary key (sno),
constraints uk_student_email unique (email),
constraints ck_student_age check (age between 18 and 30),
constraints fk_student_cno foreign key (cno) references clazz (cno) on delete cascade
);
》可以在定义外建时，设置删除策略为set null（设空），表示当删除主表的信息时，将从表中的相关数据设置为null
create table student (
sno number(4),
sname varchar2(12) not null,
gender char(3) default '男' check (gender in ('男', '女')),
age number(3),
sdate date,
email varchar2(50),
cno number(3),
constraints pk_student primary key (sno),
constraints uk_student_email unique (email),
constraints ck_student_age check (age between 18 and 30),
constraints fk_student_cno foreign key (cno) references clazz (cno) on delete set null
);
3.6通过修改表的方式添加约束
a.主键约束
alter table student add constraints pk_student primary key(sno);
b.唯一约束
alter table student add constraints uk_student_email unique(email);
c.检查约束
alter table student add constraints ck_student_age check(age between 18 and 30);
alter table student add constraints ck_student_gender check(gender in ('男','女'));
d.外键约束
alter table student add constraints fk_student_cno foreign key(cno) references clazz(cno);
4.序列（sequence）
Oracle中，当需要建立一个自增字段时，需要用到sequence。sequence也可以在mysql中使用，但是有些差别。
4.1创建sequence
create sequence seq_on_emp
minvalue 1
maxvalue 999999999
start with 1
increment by 1
nocache;
参数说明：
increment by:指定序列增长步长，能够为正（升序）、负整数（降序），但不能为0，最高精度28。
start with:指定序列起始数，默认序列最小值。
minvalue:指定序列最小值。
nominvalue:无最小值（实际为1或-10^26）。default
maxvlaue:指定序列最大值，最大28位，必须大于等于起始值且大于等于序列最小值。
nomaxvalue:无最大值（实际为10^27或-1）。default
cycle:指定序列达到最大值或最小值后继续从头开始生成。
nocycle:不循环生成。default
cache:指定数据库内存中预分配的序列值个数，以便高速获取。最小cache值为2，最大值为：(ceil(maxvalue-minvalue)/abs(increment))。
注意：假设系统发生问题。全部缓存的没有被DML语句使用并提交的序列值将丢失。潜在丢失值数量等于cache的数量。
nocache:不指定缓存数，默认缓存20。
order:指定order条件保证序列按请求顺序生成。此条件适用于RAC环境。
noorder:不保证序列按请求顺序生成。
4.2查看已有的sequence
select * from user_sequences;
4.3删除指定的sequence
drop sequence seq_on_emp;
4.4查看sequence的当前值
select last_number from user_sequences where sequence_name='SEQ_ON_EMP';（注意大写）
4.5sequence的使用案例
在雇员表emp表创建触发器，使用sequence设置主键自动插入，再次向emp表插入数据时就不用考虑主键值应该为多少了。
create trigger seq_on_emp_trigger
before insert on emp for each row
begin
select seq_on_emp.nextval into :new.empno from dual
end seq_on_emp_trigger;
5.索引（index）
5.1简介
a.索引是数据库对象之一，用于加快数据的检索，类似于书籍的索引。在数据库中索引可以减少数据库程序查询结果时需要读取的数据量，类似于在书籍中我们利用索引可以不用翻阅整本书即可找到想要的信息。
b.索引是建立在表上的可选对象；索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率。
c.索引在逻辑上和物理上都与相关的表和数据无关，当创建或者删除一个索引时，不会影响基本的表。
d.索引一旦建立，在表上进行DML操作时（例如在执行插入、修改或者删除相关操作时），oracle会自动管理索引，索引删除，不会对表产生影响。
e.索引对用户是透明的，无论表上是否有索引，sql语句的用法不变。
f.oracle创建主键时会自动在该列上创建索引。
5.2原理
a.若没有索引，搜索某个记录时（例如查找name='wish'）需要搜索所有的记录，因为不能保证只有一个wish，必须全部搜索一遍。
b.若在name上建立索引，oracle会对全表进行一次搜索，将每条记录的name值按照升序排列，然后构建索引条目（name和rowid），存储到索引段中，查询name为wish时即可直接查找对应地方。
c.创建了索引并不一定就会使用，oracle自动统计表的信息后，决定是否使用索引，表中数据很少时使用全表扫描速度已经很快，没有必要使用索引
5.3使用
a.创建
create [unique]|[bitmap] index index_name --unique表示唯一索引，bitmap表示位图索引
on table_name([column1[asc|desc],column2[asc|desc],...]|[express])
[tablespace tablespace_name]
[pctfree n1] --指定索引在数据块中空闲空间
[storage (initial n2)]
[nologging] --表示创建和重建索引时允许对表做DML操作，默认情况下不应该使用
[noline]
[nosort]; --表示创建索引时不进行排序，默认不适用，如果数据已经是按照该索引顺序排列的可以使用
b.修改
》重命名索引：
alter index index_name rename to index_mingcheng;
》合并索引（表使用一段时间后在索引中会产生碎片，此时索引效率会降低，可以选择重建索引或者合并索引,合并索引方式更好些，无需额外存储空间，代价较低）
alter index index_name coalesce;
》重建索引：
方式一：删除原来的索引，重新建立索引
方式二：alter index index_name rebuild;
c.删除
drop index index_name;
d.查看
select * from all_indexes where table_name = 'student';
5.4分类
a.B树索引
》说明
1.Oracle的默认索引，保存排序过的索引列和对应的rowid值。
2.Oracle中最常用的索引。B树索引就是一颗二叉树，叶子节点（双向链表）包含索引列和指向表中每个匹配行的ROWID值。
3.所有叶子节点具有相同的深度，因而不管查询条件怎样，查询速度基本相同。
4.能够适应精确查询、模糊查询和比较查询。
》分类
unique,non-unique（默认）,reverse key（数据列中的数据是反向存储的）
》例子
create index index_name on student(sno);
》适用场景
列基数（列不重复值的个数）大的时候使用B树索引。
b.位图索引
》说明
创建位图索引时，Oracle会扫描整张表，并为索引列的每个取值建立一个位图（位图中，对表中每一行使用一位（bit，0或者1）来标识该行是否包含该位图的索引列的取值，如果为1，表示对应的rowid所在的记录包含该位图索引列值），最后通过位图索引中的映射函数完成位到行的ROWID的转换。
》例子
create bitmap index index_name on student(sno);
》适用场景
对于基数小的列（例如性别）适合使用位图索引。
c.单列索引和复合索引
》注意：如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引，即至少要包含组合索引的第一列。
d.函数索引
》说明：
1.当经常要访问一些函数或者表达式时，可以将其存储在索引中，这样下次访问时，该值已经计算出来了，可以加快查询速度。
2.函数索引既可以使用B数索引，也可以使用位图索引。当函数结果不确定时采用B树索引，结果是固定的某几个值时使用位图索引。
3.函数索引中可以使用len、trim、substr、upper（每行返回独立结果），不能使用如sum、max、min、avg等。
》例子
create index index_uppername on student(upper(name));
select * from student where upper(name)='wish';
5.5原则
a.如果有两个或者以上的索引，其中有一个唯一性索引，而其他是非唯一，这种情况下Oracle将使用唯一性索引而完全忽略非唯一性索引。
b.至少要包含组合索引的第一列（即如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引）。
c.小表不要使用索引
d.对于基数大的列适合建立B树索引，对于基数小的列适合简历位图索引。
e.列中有很多空值，但经常查询该列上非空记录时应该建立索引。
f.经常进行连接查询的列应该创建索引。
g.使用create index时要将最常查询的列放在最前面。
h. LONG（可变长字符串数据，最长2G）和LONG RAW（可变长二进制数据，最长2G）列不能创建索引。
i.限制表中索引的数量（创建索引耗费时间，并且随数据量的增大而增大；索引会占用物理空间；当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度）。
5.6注意
a.通配符在搜索词首出现时，Oracle不能使用索引。例：
--在name上创建索引
create index index_name on student(sname);
--下面的方式Oracle不使用name索引
select * from student where sname like '%wish%';
--通配符出现在字符串的其他位置时，优化器能够使用索引。例：
select * from student where sname like 'wish%';
b.不要在索引列上使用not，可以采用其他方式代替（Oracle碰到not会停止使用索引，而采用全表扫描）。例：
select * from student where not (score=100);
select * from student where score <> 100;
--替换为
select * from student where score>100 or score<100;
c.索引上使用空值比较将停止使用索引。例：
select * from student where sname is not null;
6.视图（view）
6.1视图的定义和简介
a.视图(view)，也称虚表, 不占用物理空间，这个也是相对概念，因为视图本身的定义语句还是要存储在数据字典里的。视图只有逻辑定义。每次使用的时候,只是重新执行SQL。
b.视图是从一个或多个实际表中获得的，这些表的数据存放在数据库中。那些用于产生视图的表叫做该视图的基表。一个视图也可以从另一个视图中产生。
c.视图的定义存在数据库中，与此定义相关的数据并没有再存一份于数据库中。通过视图看到的数据存放在基表中。
d.视图看上去非常象数据库的物理表，对它的操作同任何其它的表一样。当通过视图修改数据时，实际上是在改变基表中的数据。相反地，基表数据的改变也会自动反映在由基表产生的视图中。由于逻辑上的原因，有些Oracle视图可以修改对应的基表，有些则不能（仅仅能查询）。
e.还有一种视图：物化视图（MATERIALIZED VIEW ），也称实体化视图，快照 （8i 以前的说法） ，它是含有数据的，占用存储空间。
f.查询视图没有什么限制, 插入/更新/删除视图的操作会受到一定的限制。所有针对视图的操作都会影响到视图的基表。为了防止用户通过视图间接修改基表的数据, 可以将视图创建为只读视图(带上with read only选项)。
6.2视图的作用
a.提供各种数据表现形式, 可以使用各种不同的方式将基表的数据展现在用户面前, 以便符合用户的使用习惯(主要手段: 使用别名)。
b.隐藏数据的逻辑复杂性并简化查询语句, 多表查询语句一般是比较复杂的, 而且用户需要了解表之间的关系, 否则容易写错。 如果基于这样的查询语句创建一个视图, 用户就可以直接对这个视图进行"简单查询"而获得结果。这样就隐藏了数据的复杂性并简化了查询语句。这也是oracle提供各种"数据字典视图"的原因之一,all_constraints就是一个含有2个子查询并连接了9个表的视图(在catalog.sql中定义)。
c.执行某些必须使用视图的查询。 某些查询必须借助视图的帮助才能完成。比如, 有些查询需要连接一个分组统计后的表和另一表, 这时就可以先基于分组统计的结果创建一个视图, 然后在查询中连接这个视图和另一个表就可以了。
d.提供某些安全性保证。视图提供了一种可以控制的方式, 即可以让不同的用户看见不同的列, 而不允许访问那些敏感的列, 这样就可以保证敏感数据不被用户看见。
f.简化用户权限的管理。可以将视图的权限授予用户, 而不必将基表中某些列的权限授予用户, 这样就简化了用户权限的定义。
6.3视图的创建
a.权限：要在当前方案中创建视图, 用户必须具有create view系统权限。要在其他方案中创建视图, 用户必须具有create any view系统权限。视图的功能取决于视图拥有者的权限。
b.语法：create [or replace][force] view [schema.]viewname [column1,column2,..] as select ... [with check option] [constraint constraint name] [with read only];
说明：or replace:如果存在同名的视图, 则使用新视图"替代"已有的视图。
force:"强制"创建视图,不考虑基表是否存在,也不考虑是否具有使用基表的权限。
column1,column2:视图的列名, 列名的个数必须与select查询中列的个数相同。如果select查询包含函数或表达式, 则必须为其定义列名。此时, 既可以用column1, column2指定列名, 也可以在select查询中指定列名。
with check option:指定对视图执行的dml操作必须满足“视图子查询”的条件即,对通过视图进行的增删改操作进行"检查",要求增删改操作的数据, 必须是select查询所能查询到的数据,否则不允许操作并返回错误提示。默认情况下, 在增删改之前"并不会检查"这些 行是否能被select查询检索到。
with ready only:创建的视图只能用于查询数据, 而不能用于更改数据。
c.创建简单视图
》简单视图定义：是指基于单个表建立的，不包含任何函数、表达式和分组数据的视图。
》创建简单视图过程：
SQL> sqlplus /nolog;
SQL> conn /as sysdba;
SQL> grant create view to scott;
SQL> conn scott/tiger
SQL> create view vw_emp as select empno,ename,empjob,hiredate,deptno from emp;
》注意：对简单视图进行DML操作，基表也会发生响应的更改。
》创建只读视图：视图只能用来查询数据，不能更改数据表。但更新基表，只读视图也会发生相应的更改。例：
SQL> create view vw_emp_readonly as select empno,ename from emp with read only;
》创建检查约束视图：对通过视图进行的增删改操作进行检查，要求增删改操作的数据必须是select查询所能查询到的数据。例（20号部门不在查询范围内，违反检查约束，所以无法插入）：
SQL> create view vw_emp_check as select empno,ename,deptno from emp where deptno=10 with check option;
SQL> insert into vw_emp_check values('7111','aname',20);
ERROR at line 1:
ORA-01402:view WITH CHECK OPTION where -clause violation
d.创建连接视图
》连接视图的定义：指基于多个表所创建的视图，即定义视图的查询是一个连接查询。 主要目的是为了简化连接查询。
》创建连接视图（查询部门编号为10和30的部门及雇员信息）：
SQL> create view vw_dept_emp as
2 select a.deptno,a.dname,a.loc,b.empno,b.ename,b.sal from dept a,emp b where a.deptno=b.deptno and a.deptno in (10,30);
》连接视图上的DML操作：
1.在视图上进行的所有DML操作，最终都会在基表上完成。select 视图没有什么限制，但insert/delete/update有一些限制。
2.关于键值保存表：如果连接视图中的一个“基表的键”(主键、唯一键)在它的视图中仍然存在，并且“基表的键”仍然是“连接视图中的键”(主键、唯一键)。即某列在基表中是主键|唯一键，在视图中仍然是主键|唯一键，则称这个基表为“键值保存表”。一般地，由主外键关系的2个表组成的连接视图，外键表就是键值保存表，而主键表不是。
3.关于连接视图上的DML操作及视图更多详细信息，可查看：https://www.cnblogs.com/jinloooong/p/7687632.html。
7.同义词（synonym）
7.1概念
同义词（Synonym）是数据库对象的一个别名，Oracle可以为表、视图、序列、过程、函数、程序包等指定一个别名。同义词有两种类型：
私有同义词：拥有CREATE SYNONYM权限的用户（包括非管理员用户）即可创建私有同义词，创建的私有同义词只能由当前用户使用。
公有同义词：系统管理员可以创建公有同义词，公有同义词可以被所有用户访问。
7.2创建语法
create [or replace] [public] synonym [schema.]synonym_name for [schema.]object_name;
7.3示例（创建和删除同义语）：
c:/> sqlplus /nolog
SQL> conn /as sysdba
SQL> create user abortre identified by password;（创建用户abortre）
SQL> grant connect to aborte;
SQL> grant resource to abortre;
SQL> grant create synonym to abortre;（给用户授权）
SQL> conn abortre/password@orcl;
SQL> create synonym myEmp for scott.emp;（为scott用户下的emp表创建同义语，注意该同义语只有在abortre用户下可用）
SQL> select * from myEmp;
ORA-00942:表或视图不存在（由于abortre没有scott用户下emp表的权限，所以查询视图不存在）
SQL> conn scott/tiger@orcl;
SQL> grant all on emp to abortre;（给abortre用户授予emp表的所有权限）
SQL> conn abortre/password@orcl;
SQL> select * from myEmp;（可正确查出表中的数据）
SQL> drop synonym abortre.myEmp;（删除同义语myEmp）
注：本博客所有内容源自网络收集和整理。
