DDL与数据库对象

数据库对象

常见数据库对象

• 表:存放数据的基本数据库对象,由行(记录)和列(字段)组成
• 约束条件:执行数据校验,保证数据完整性的系列规则
• 视图:表中数据的逻辑显示
• 索引:根据表中指定的字段建立起来的顺序,用于提供查询性能
• 序列:一组有规律的整数值
• 同义词:对象的别名

命名规则:

• 必须以字母开头
• 可以包含字母,数字,_,$,和#
• 同一方案(用户)下的对象不能重名
• 不能使用Oracle的保留字

创建表

创建表的前提条件

具备创建表的权限

有可用的存储空间

创建表语法

create table [schema.]table (column datatype[default expr][,...]);
-----------------------------------------------------------------
create table scott.test1(
     eid number(10),
     name varchar2(20),
     hiredate date default sysdate,
     salary number(8,2) default 0
);

说明: 

创建表时必须指定表名,字段名,字段类型

create table为DDL语句,一经执行不可撤销

使用子查询创建表

在创建表的同时,可以将子查询的结果直接插入其中:

新建表与子查询结果的字段列表必须匹配

新建表的字段列表可以缺省

语法格式

create table [schema.] table (column[,...])
as subquery;
----------------------------------------------
create table myemp(编号,姓名,年薪)
as select empno,ename,sal*12 from emp;
----------------------------------------------
create table myemp(编号,姓名,年薪)
as select empno,ename,sal*12 annsal from emp;

修改表结构

使用alter table 语句可以修改表的结构,包括:

添加字段

修改字段

删除字段

alter语句为DDL语句,一经执行不可撤销

添加字段

在alter table语句中,使用add子句添加新字段,新字段只能被加到整个表的最后。

语法:

alter table table
add (column datatype [default expr] [,column datatype]...);
-----------------------------------------------------------
alter table test1
add(
   grade number(3),
   phone varchar2(20) default '无'
);

 修改字段

在alter table语句中,使用modify子句修改现有字段,包括字段的数据类型,大小和默认值.

语法:

alter table table
modify (column datatype [default expr] [,column datatype]...);
--------------------------------------------------------------
alter table test1
modify(
   grade number(2),
   phone varchar2(15) default '021-12212121'
);

说明: 

修改操作会受到当前表中已有数据的影响——当已有记录的相应字段只包含空值,类型,大小都可以修改,否则修改可能失败.

修改的缺省值设置,只对此后新插入的记录有效

删除字段

在alter table 语句中,使用drop子句删除字段——从每行中删除掉该字段占据的长度和数据,释放在数据库中占用的存储空间.

语法:

alter table table
drop(column[,column]...);
---------------------------------------------------
alter table test1
drop(grade,phone);

清空表中数据 

truncate table语句用于清空表中数据:

清楚表中所有记录

释放表的存储空间

为DDL语句,一经执行不可撤销

语法:

truncate table table;
------------------------------------------------------------------
truncate table test1;

删除表 

drop table语句用于删除表:

表中所有数据将被删除

此前未完成的事务将被提交

所有相关的索引被删除

为DDL语句,一经执行不可撤销

语法:

drop table table;
--------------------------------------------------------------------
drop table test1;

重命名表 

使用rename语句可以改变现有表的名称

也可修改其他数据库对象(视图,序列,同义词等)的名称

执行重命名操作的必须是对象的所有者

为DDL语句,一经执行不可撤销

语法:

rename old_name to new name;
-------------------------------------------------------
rename test1 to test00;

Oracle数据库中的表

用户定义的表

用户自己创建并维护的一组表

包含了用户所需的信息

数据字典表

由Oracle数据库自动创建并维护的一组表

包含数据库信息

数据字典

什么事数据字典

数据字典是Oracle数据库的核心,用于描述数据库及其所有对象.

数据字典由一系列只读的表盒视图组成,这些表盒视图属sys用户用于,由Oracle server负责维护,用户可以通过select语句进行访问.

数据字典的内容

数据库的物理和逻辑结构

对象的定义和空间分配

完整性约束条件

用户

角色

权限

审计记录

数据字典视图主要可分为三类

dba.所有反感包含的对象信息

all.用户可以访问的对象信息

user.用户方案的对象信息

举例:

--查看当前用户拥有的所有表的名字
select table_name from user_tables;

--查看当前用户可以访问的所有表的名字
select table_name from all_tables;

--查看当前用户拥有的所有对象的类型
select distinct object_type from user_objects;

--查看所有用户拥有的所有对象的类型
select table_name from dba_tables;

约束

约束(Constraint)是在表上强制执行的数据校验规则,用于保护数据的完整性,具体包括如下五种:

• not null                       (非空)
• unique key                 (唯一键)
• primary key               (主键)
• foreign key                (外键)
• check                          (检查)

相关说明

Oracle 使用SYS_Cn格式命名约束,也可以由用户命名

创建约束的时机

• 在建表的同时创建
• 建表后单独添加

可以在表级或列级定义约束

可以通过数据字典视图查看约束

建表的同时创建约束

语法格式

create table [schema.] table(
            column datatype [defaule expr] [column_constraint],
            ...
            [table_constraints]
);

非空约束(not null)

非空约束特点

确保字段值不能为空(null)

只能在字段级定义

create table student(
         sid number(3) not null,
         name varchar2(20),
         birth date constraint student_birth_nn not null
);

唯一性约束(unique) 

唯一性约束特点:

唯一性约束用于确保所在的字段(或字段组合)不出现重复值

唯一性约束字段允许出现空值

Oracle会自动为唯一性约束创建对应的唯一性索引

唯一性约束既可以在字段级定义，也可以在表级定义

create table student(
        sid number(3) unique,
        name varchar2(20)
);
----------------------------------------------------
create table student(
        sid number(3),
        name varchar2(20),
        constraint student_sid_un unique(sid)
);

主键约束(primary key)

 主键约束特点:

• 主键用于唯一标示表中的某一行记录,功能上相当于非空且唯一
• 一个表中只允许一个主键,主键可以是单个字段或多字段的组合
• Oracle会自动为主键字段创建对应的唯一性索引
• 主键约束既可以在字段级定义,也可以在表级定义

create table student(
        sid number(3) primary key,
        name varchar2(20)
);
---------------------------------------------------
create table student(
        sid number(3),
        name varchar2(20),
        constraint student_sid_pk primary key(sid)
);

 联合主键:

• 由多个字段组合而成的主键也称为联合主键
• 联合主键中每一个字段都不能为空
• 联合主键字段组合的值不能出现重复
• 联合主键只能定义为表级约束

create table record(
        student_id number(3),
        subject_id varchar2(20),
        record number(3),
        constraint record_stuId_subId_pk primary key(student_id,subject_id)
);

外键约束(foreign key)

外键约束特点:

• 外键用于确保相关的两个字段之间的参照关系,以实现参照完整性约束;
• 外键约束通常构建于来自不同的两个字段之间
• 子表外键列的值必须在主表参照列植的范围内,或者为空;
• 外键参照的必须是主表的主键或者唯一键;
• 主表主键/唯一键值被子表参照时,主表相应记录不允许被删除;

create table empinfo(
           eid number(3) primary key,
           ename varchar2(20),
           job varchaer2(20),
           birth date
);
--------------------------------------------------------------
create table salary(
           eid number(3)  primary key,
           basic_salary number(8,2),
           job_allowance number(8,2),
           travelling_allowance number(8,2),
           personal_income_tax number(8,2),
           constraint salary_eid_fk foreign key(eid) references empinfo(eid)
);
--------------------------------------------------------------
create table salary(
           eid number(3) primary key references empinfo(eid),
           ...
);

检查约束(check)

检查约束特点:

• 定义每一行(的指定字段)都必须满足的条件
• 以条件表达式的形式给出数据需要符合的条件
• 只能在字段级定义

条件表达式中不允许出现如下内容:

• currval,nextval,level,rownum等伪列
• sysdate,uid,user,userenv等函数
• 对其它字段值的引用

create table test1(
          name varchar2(20),
          age number(3) check(age>=0 and age<=120)
);

域完整性约束: not null, check

实体完整性约束: unique,primary key

参照完整性约束:foreign key

查看约束

查询用户字典视图user_constrains

可得到用户的所有约束

查询用户字典视图user_cons_columns

可获知约束建立在那些字段上

建表后添加约束

基本语法

alter table table
add[constraint constraint_name] constraint_type(column);
----------------------------------------------------------------------------------
create table student(
          sid number(10),
          name varchar2(20)
);
alter table student
add constraint student_sid_pk primary key(sid);

特例:非空约束必须使用modify子句添加

alter table student
modify(name char(15) default 'n/a' not null)

删除约束

基本语法 

alter table table
drop constraint constraint_name;
-------------------------------------------------------------------------------------
create table student(
           sid number(10),
           name varchar2(20),
           constraint student_sid_pk primary key(sid)
);
alter table studnet drop constraint studnet_sid_pk;

删除主键约束的另一种方式:

alter table table drop primary key;
------------------------------------------------------------------
alter table student drop primary key;

删除级联约束

在删除约束是,如果还存在与该约束相关的其他约束则删除操作会失败,此时可使用cascade子句将其他关联约束一并删除.

alter table table
drop constraint constraint_name[cascade];
------------------------------------------------
create table empinfo(
   eid number(3) constraints empinfo_eid_pk primary key,
   ename varchar2(20)
   ...
);
------------------------------------------------
create table salary(
    eid number(3) references empinfo(eid)
    ...
);
------------------------------------------------
alter table empinfo
drop constraints empinfo_eid_pk cascade;

在删除表中字段时,如果该字段处于多字段联合约束条件(联合主键,联合唯一键,存在参照当前字段的外键)中,则删除会失败,此时可使用cascade constraints子句将与该字段相关的约束一并删除.

alter table table
drop (column[,column]...) cascade constraints;
-------------------------------------------------
create table record(
        student_id number(3),
        subject_id varchar2(20),
        record number(3),
        constraint record_stuId_pk primary key(student_id,subject_id)
);
alter table record
drop (student_id) cascade constraints;

禁用约束

在alter table语句中,还可适应disable constraint子句禁用已有约束.

也可以使用cascade选项将相关联的约束一并禁用.

alter table table
disable constraint constraint_name[cascade];
---------------------------------------------------------
create table student(
         sid number(10),
         name varchar2(20),
         constraint student_sid_pk primary key(sid)
);
alter table student
disable constraint student_sid_pk;

启用约束

在alter table语句中,可使用enable constraint子句启用先前被禁用的约束.

alter table table
enable constraint constraint_name[cascade];
---------------------------------------------------------
create table student(
         sid number(10),
         name varchar2(20),
         constraint student_sid_pk primary key(sid)
);
alter table student disable constraint student_sid_pk;

alter table student enable constraint student_sid_pk;

说明:

和关闭约束操作的情况有所不同,此时无法再使用cascade选项一并启用相关的其它约束.

视图

什么是视图(view)?

• 视图由一个或多个表(或视图)中提取数据而成
• 视图是一种虚拟表
• 视图一经创建,可以当做表来使用.

使用视图的好处

• 简化复杂数据查询
• 提高运行效率
• 屏蔽数据库表结构,实现数据逻辑独立性
• 限制数据访问
• 在相同数据上提高不同的视图,便于数据共享

创建/删除视图

创建视图

• 通过在create view语句中嵌入子查询的方式创建视图
• 基本语法:

create [or replace] view [schema.] view [(alias[,aliasx]...)]
as subquery;
----------------------------------------------------------
create or replace view myview(编号,姓名,职位,工资)
as select empno,ename,job,salfrom emp where deptno=20;

查看视图结构

desc myview;

删除视图

drop view myview;

查询视图

select * from myview;

强制创建视图

可使用force选项强制创建视图

语法格式

create [or replace] [force | noforce] view [schema.] view [(alias[,aliasx]...)]
as subquery;
------------------------------------------------------------------
create or replace force view myview
as select empno,ename,job,sal from emp where depton = 20;

更新视图

在可更新视图上进行DML操作,可以修改基表中数据 

• 可更新视图的定义中不能使用分组函数,group by 子句,distinct关键字,rownum伪列,字段的定义不能为表达式....
• 由两个以上基表中导出的视图不可更新
• 基表非空的列在视图定义中未包括,则不可在视图上进行insert操作...

在视图上进行DML操作,语法与在表上操作相同

• inset
• update
• delete

创建只读视图

在创建视图时,可使用with read only 选项将之设置为只读

create [or replace] [force | noforce] view [schema.] view [(alias[,aliasx]...)]
as subquery
[with read only];
---------------------------------------------------------------------------------
create or replace force view myview
as select empno,ename,job,salfrom emp where deptno=20
with read only;

临时视图

嵌入到sql语句中的子查询是临时视图

临时视图不是数据库对象,其定义不会长久保持在数据库中,本次运行后即被清除.

create table myemp(编号,姓名,年薪)
as select empno,ename,sal*12 from emp;
----------------------------------------------------------
select rownum,a.*
from (select * from emp order by sal)a
where rownum<=5;

索引

什么是索引(Index)？

• 一种用于提升查询效率的数据库对象;
• 通过快速定位数据的方法,减少磁盘I/O操作;
• 索引信息与表独立存放;
• Oracle数据库自动使用和维护索引.

索引分类

• 唯一性索引
• 非唯一索引

创建索引的两种方式

• 自动创建--在定义主键或唯一键约束时系统会自动在相应的字段上创建唯一性索引。
• 手动创建--用户可以在其它列上创建非唯一的索引,以加速查询.

创建/删除索引

可使用create index语句手动创建索引

create index [schema.] index
on table (column[,column]...);
---------------------------------------
create index myindex
on emp(ename);

删除索引

• 使用drop index语句删除索引
• 操作者须是索引的所有者,或拥有drop该index的权限
• 删除表时相关的索引(和约束)将被自动删除,但视图和序列将保留

drop index myindex;

创建索引的原则

下述情况可以创建索引

• 字段取值分布范围很广
• 字段中包含大量空值
• 字段经常出现在where子句或链接条件中
• 表经常被访问,数据流很大,且通常每次访问的数据量小于记录总量的2%-4%

下列情况不适合创建索引

• 表很小
• 字段不经常出现在where子句中
• 每次访问的数据量大于记录总数的2%-4%
• 表经常更新
• 被索引的字段作为表达式的一部分被引用

查看索引

查询用户字典视图user_indexes

可得到用户的所有索引

查询用户字典视图user_ind_columns

可获知索引建立在那些字段上

基于函数的索引

基于表达式的索引被统称为基于函数的索引--索引表达式由表中的字段,变量,sql函数和自定义函数构建而成.

创建函数索引

create index[schema.] index
on table (function(column));
------------------------------------------------------------
create index myindex
on emp(lower(ename));

使用函数索引

select * from emp
where lower(ename)='king';

序列

什么事序列(Sequence)？

• 系统自动生成的,不重复的整数值
• 序列是一种数据库对象,可以被多个用户共享
• 典型用图是做为主键值,它对于每一行必须是唯一的
• 序列可以代替应用程序编号
• 可以对序列值进行缓冲存储,以提高访问效率.

创建序列

使用create sequence语句创建序列

create sequence [schema.]sequence
[increment by n]
[start with n]
[{maxvalue n | nomaxvalue}]
[{minvalue n | nominvalue}]
[{cycle | nocache}]
[{order | noorder}];
--------------------------------------------------------
create sequence mysequence1
increment by 1
start with 1
nomaxvalue nocycle;
--------------------------------------------------------
create sequence mysequence2;

查询数据字典视图user_sequences可获得用户序列信息

使用序列

nextval/currval伪列

• nextval伪列用于从指定的序列数值中取出下一个值
• currval伪列引用的是指定序列的"当前值"

语法格式

select mysequence1.currval from dual;
select mysequence1.nextval from dual;
insert into test1 values(mysequence1.nextval,'Tom');

说明: 

使用缓存(cache n)可提高访问效率

序列在下列情况下可能出现不连续的情况:

• 回滚
• 系统异常
• 多个表同时使用同一序列

使用nocache和order设置会降低运行效率

修改序列

语法格式

alter sequence [schema.]sequence
[increment by n]
[{maxvalue n | nomaxvalue}]
[{minvalue n | nominvalue}]
[{cycle | nocycle}]
[{cache n | nocache}]
[{order | noorder}];

注意事项:

• 操作者必须是序列的所有者,或者拥有alter该序列的权限;
• 只有未来再生成的序列数受影响;
• 序列的初始值不可更改
• 更改中会进行一些验证,比如新的maxvalue如果小于当前的序列值就会报错.

删除序列

删除序列

• 使用drop sequence语句删除序列
• 操作者是序列的所有者,或拥有drop该sequence的权限.

同义词

同义词相当对象的别名,使用同义词可以:

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

创建同义词

create [public] synonym synonym
for object;
------------------------------------------------
create synonym gt1 for emp;

使用同义词

select * from gt1;

删除同义词

drop synonym gt1;