Oracle学习记录4(数据库对象=》表，视图，序列，索引，同义词)

表

    我们在创建和管理表，使用DDL语句管理。

表名和列名的命名规则

• 必须以字母开头
• 必须在1-30个字符之间
• 必须只能包含A-Z，a-z,0-9,_,$,和 #
• 必须不能和用户定义的其他对象重名
• 必须不能是Oracle的保留字段
• Oracle默认存储是都存为大写
• 数据库名只能是1-8位，Databaselink用在分布式数据库里，可以是128位，和其他一些特殊字符。

表创建

    Oracle数据库无论是表创建，还是其他的大部分表级操作，都需要管理员赋予权限，所以在做表级操作的同时，需要确认权限是否满足，以及是否有足够的存储空间。

创建表语句如下：

create table test1
tid number,
tname varchar2(20),
hiredate date default sysdate);

Create table emp10 as select * from emp where 1=2;

表修改

查看表详情

Desc 表

追加列

alter table test1 add photo blob;--alter table 表 add 列名 类型;

修改列

alter table test1 modify tname varchar2(40);--alter table 表名 modify 列名 类型;

删除列

alter table test1 drop column photo;--alter table 表名 drop column 列名；

重命名列

alter table test1 rename column tname to username;--alter table 表明 rename column 旧列名 to 新列名；

重命名表

rename test1 to test2;--rename 旧表名 to 新表明；

删除表

删除表

drop table test2;--drop table 表名；

被删除的表，移除到回收站中，可以使用闪回技术，恢复表。(后续会扩展闪回技术的知识).

管理员删除表，是没有回收站，直接删除。

管理员登陆

管理员登陆dos命令如下：

Sqlplus sys/password as sysdba

    管理员登陆认证分三种。

    主机认证（外部认证）：账号或密码不正确，也可以登陆。主机认证需要数据库管理员，才可以操作。

    系统操作的人员分组里，看到组里有oracle的选项，里面的操作人员是adminstrantor。把ora_dba成员删除，就不可以做主机认证了。

    

    密码认证：账号密码正确。

    全局认证：指纹，虹膜认证，一般用于高级的银行核心数据进场认证使用。

数据类型

 

数据类型	描述
VARCHAR2( SIZE )	可变长字符数据
CHAR( SIZE )	定长字符数据
NUMBER( P,S )	可变长数值数据
DATE	日期型数据
LONG	可变长字符数据，最大可达到2G
CLOB	字符数据，最大可达到4G
RAW and LONG RAW	原始的二进制数据
BLOB	二进制数据，最大可达到4G
BFILE	存储外部文件的二进制数据，最大可到达4G
ROWID	行地址

约束

约束的类型

约束的类型	描述
NOT NULL	非空
UNIQUE	不能重复
PRIMARY KEY	主键，非空且唯一，一般索引。
FOREIGN KEY	子表的外键必须是父表的主键
CHECK	检查性约束

    Oracle约束一般有列级约束，表级约束。

    Check检查性约束，比如一个字段需要限制，只能输入'男'或'女'字符，那么就可以使用检查性字段。

    在创建表时，给字段添加检查性约束，代码如下：

create table test3
(
tid number,
tname varchar2(20),
gender varchar2(2) check (gender in ('男','女')),
sal number check (sal > 0)
);

如果违反了检查性约束，语句会抛出错误，Oracle会默认给抛出的错误信息，赋值名字。当然，我们也可以自定义名字。语句如下：

create table student
(
sid number constraint student_pk primary key,
sname varchar2(20) constraint student_name_notnull not null,
gender varchar2(2) constraint student_gender check (gender in ('男','女')),
email varchar2(40) constraint student_email_unique unique
constraint student_email_notnull not null,
deptno number constraint student_fk references dept(deptno) on delete set null
);

视图

创建视图账号需要授予权限，SQL如下：

Grant create view to scott；--Grant create view to 账号;

创建视图，SQL如下：

create table empinfo
as
select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname
from emp e, dept d
where e.deptno=d.deptno;

视图简介

• 视图是一种虚表。
• 视图建立在已有表的基础上，视图赖以建立的这些表称为基表。
• 向视图提供数据内容的语句为select语句，可以将视图理解为存储起来的select语句。
• 视图向用户提供基表数据的另一种表现形式。
• 视图优点：限制数据访问，简化复杂查询，提供数据的相互独立，同样的数据，可以有不同的显示方式。
• 视图不能提高性能。
• 视图只是简化查询，不建议对视图修改数据。

create [or replace|FORCE|NOFORCE] view empinfoview
as
select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname
from emp e, dept d
where e.deptno=d.deptno
with read only;

    其中的or replace是如果有视图已经存在，则替换视图。
    Force和noforce是决定是否强制创建视图，force是在基表不存在时也可以创建视图的标志，noforce指如果基表不存在，则不能创建视图。Noforce是默认选项。

    With read only是限制视图只能执行查询操作，不能进行增删改操作。可以理解为共享锁。  

    With check option 你通过这个视图 只能操作看得到得数据。

视图使用DML的规定

当视图定义中包含以下元素之一时不能使用

INSERT

• 组函数
• GROUP BY 子句
• DISTINCT 关键字
• ROWNUM 伪列
• 列的定义为表达式
• 表中非空的列在视图定义中未包括

任何一个DML操作都会返回一个Oracle Server错误。

删除视图

删除视图只是删除视图的定义，并不会删除基表的数据。SQL语句如下：

DROP VIEW 视图；

序列

• 自动提供唯一的数值
• 共享对象
• 主要用于提供主键值
• 将序列值装入内存可以提高访问效率

NEXTVAL:返回序列中下一个有效的值，任何用户都可以引用。

CURRVAL:返回序列中的当前值。当最开始没有定义得时候，currval没有值，没有意义。

序列中得指针，如下*就是指针，每取出一个数值，指针往前进一位，但是要记住。

指针是在要取出数得前一位，比如现在取1，那么指针在空白处，现在取2，

指针是在1处。

 【123456】

*

序列写法：http://blog.csdn.net/a4171175/article/details/77368295

使用序列

• 将序列值装入内存可提高访问效率。
• 序列在下列情况下出现裂缝，裂缝就是序列不连续:

1.回滚

2.系统异常

3.多个表同时使用同一序列

• 可使用USER_SEQUENCES查看序列当前的有效值

insert into test values (序列,'aa');

修改序列和删除序列，暂不扩展。

索引

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

    这里需要说明的是，索引是通过Rowid来查询数据。之前在数据类型有提到过。Rowid 行地址伪列，Rowid行像是指针会指向dbf文件位置，可以通过指针找到对应的数据地址。

Select * from emp where rowid=’DASJDBSAKJBDKLN’;

可以直接通过rowid来直接找到相对应的数据。

创建索引

create index myindex on emp(deptno);

什么时候创建索引

• 列中数据值分部范围很广。
• 列经常在WHERE子句或连接条件中出现。
• 表经常被访问而且数据量很大，访问的数据大概占数据总量的2%到4%。

什么时候不要创建索引

• 表很小。
• 列不经常作为连接条件或出现在WHERE子句中。
• 查询的数据大于2%到4%。
• 表经常更新。如果索引建立在频繁更新的字段上，每次修改这个字段的值，那么索引表一直变。

示例

     比如一本书，如果是一本厚的书，那么需要目录可以更快查询到页。如果是一本很少页数的数，那么就不需要目录，目录就站了一部分页数，浪费资源。

   

后续补充：

1.如何取消索引？

2.索引得好处和坏处？

同义词（别名）

• 方便访问其他用户的对象
• 缩短对象名字的长度

使用同义词，需要对账户授权，SQL；如下：

grant create synonym to scott;--grant create synonym to 账号;

授权查其他用户表

grant select on hr.employees to scott;--grant select on 别账号.表 to 本账号

创建同义词

create synonym hremp for hr.employees;--create synonym 自定义名 for 别账号.表

这里作名词得意思是为了安全起见，别人不知道这个表得来源是什么。

那么至于公有和私有表也是为了安全。

以后客户告诉你得表，可能是个视图，可能是个表，也可能是个别名