Oracle数据库入门之DDL与数据库对象

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

　　数据库对象

　　常见：表：存放数据的基本数据库对象，由行(记录)和列(字段)组成

　　约束条件：执行数据校验，保证数据完整性的系列规则

　　视图：表中数据的逻辑显示

　　索引：根据表中指定的字段建立起来的顺序，用于提高查询性能

　　序列：一组有规律的整数值

　　同义词：对象的别名

　　命名：必须以字母开头。可以包含字母、数据、下划线、$、#

　　同一方案(用户)下的对象不能重名。不能使用Oracle的保留字

　　补充：Oracle数据库中的表分为用户定义的表和数据字典表

　　用户定义的表：用户自己创建并维护的一组表，包含了用户所需的信息

　　数据字典表：由Oracle数据库自动创建并维护的一组表，包含数据库信息

　　创建表

　　概述：创建表需要两个前提条件，即具备创建表的权限和有可用的存储空间

　　创建表时必须指定表名、字段名、字段类型。create table为DDL语句，一经执行不可撤销

　　语法：create table [schema.]table(column datatype [default expr][,...]);--缺省是将表创建在当前方案(用户)下

　　举例：create table scott.test1(name varchar2(20),hiredata date default sysdate,salary number(8,2) default 0);

　　向test1插入新记录的时候，若没有指定hiredate值，缺省就会取当前的系统时间。同样也设置了salsry的缺省值为0

　　如果插入记录的时候也没有指定eid的值，那么eid的缺省值是null。注意数值型的缺省值不是0，而是null

　　补充：还可以使用子查询创建表。这是创建表的另一种方式，但不是很常用。即创建表的同时将子查询的结果直接插入其中

　　新建表与子查询结果的字段列表必须匹配。新建表的字段列表可以缺省，这时字段名就跟子查询的结果的字段名相同

　　语法：create table [schema.]table(column[,...]) as subquery;--括号中不可以定义字段类型

　　举例：create table myemp2 as select empno,ename,sal*12 from emp;--非法。表达式不可以充当字段，需要指定别名

　　create table myemp2 as select empno,ename,sal*12 annsal from emp;--合法

　　create table myemp(编号,姓名,年薪) as select empno,ename,sal*12 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 '010-12345678');

　　修改的缺省值设置，只对此后新插入的记录有效。修改操作会受到当前表中已有数据的影响

　　当已有记录的相应字段只包含空值时，类型和大小都可以修改。如果该字段已包含数值，，则修改可能失败

　　删除：在alter table语句中使用drop子句删除字段。从每行中删除该字段占据的长度和数据，释放在数据块中占用的存储空间

　　alter table table drop(column[,column]...);

　　alter table test1 drop(grade,phone);

　　清空表数据

　　概述：使用truncate table可以清空表中数据。清除表中所有记录，释放表的存储空间，它是DDL语句，一经执行不可撤销

　　它与DML中的delete语句有很大的差别。delete可以进行条件性的删除，也可以定义到事务中，对其进行回滚或撤销

　　语法：truncate table table;--它清除的并不是表格本身，表的结构还是存在的，只不过变成了一个空表

　　删除表

　　概述：使用drop table语句删除表，它是DDL语句，一经执行不可撤销

　　表中所有数据将被删除，此前未完成的事务将被提交，所有相关的索引被删除

　　语法：drop table table;

　　重命名表

　　概述：使用rename语句可以改变现有表的名称，它是DDL语句，一经执行不可撤销

　　也可修改其它数据库对象(视图、序列、同义词等)的名称。执行重命名操作的必须是对象的所有者

　　语法：rename old_name to new_name;

　　举例：rename test1 to test88;

　　数据字典

　　概述：数据字典是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 distinct object_type from all_objects;--返回当前用户可以查看的所有的对象的类型

　　select table_name frome dba_tables;--查看所有用户拥有的所有表的名字

　　select * from user_constraints;--查看当前方案(用户)下所有的约束的信息

　　select * from user_constraints where table_name='student';--查看当前方案(用户)下的student表中的约束信息

　　约束(Constraint)

　　概述：约束是在表上强制执行的数据校验规则，用于保护数据的完整性

　　具体包括五种，即not null(非空)、unique key(唯一键)、primary key(主键)、foreign key(外键)、check(检查)

　　分类：域完整性约束：not null、check。实体完整性约束：unique、primary key。参照完整性约束：foreign key

　　说明：约束也是一种数据库对象。如果创建约束时，用户没有指定它的名字，那么系统会自动的为其命名

　　在Oracle使用SYS_Cn格式命名约束，也可以由用户命名。也可以通过数据字典视图查看约束

　　可以在建表的同时添加约束，也可以在建表后单独添加约束。可以在表级或列级定义约束

　　通常并不太建议在建表之后再添加约束或者建表之后再修改表的结构

　　查看：查询用户字典视图user_constraints可得到当前用户的所有约束。即select * from user_constraints;

　　查询用户字典视图user_cons_columns可获知约束建立在哪些字段上。即select * from user_cons_columns;

　　创建：create table [schema.]table(column datatype[default expr][column_constraint],...[table_constraint]);

　　alter table table add [constraint constraint_name] constraint_type(column);--这是建表后添加约束

　　比如alter table stu add constraint stu_sid_pk primary key(sid);--原stu表中有sid和name两个字段

　　等价alter table stu add primary key(sid);--只不过此时的约束名就会由系统自动设定了

　　特例：建表后添加约束时，非空约束必须使用modify子句添加。实际上相当于重新定义了某个字段

　　如alter table stu modify(name not null);或alter table stu modify(name char(8) default 'N/A' not null);

　　也可以修改多个字段，如alter table stu modify(sid not null,name default 'Stone' not null);

　　删除：语法为alter table table drop constraint constraint_name;

　　alter table table drop primary key;--删除主键的另一种方式。只有主键才可以这样删除

　　因为一个表中只可以定义一个主键，所以不会有二义性。而其它的约束，都可能定义多个

　　续一：删除约束时，若存在与该约束相关联的其它约束，则删除操作会失败。可用cascade子句将其它关联约束一并删除

　　语法为alter table table drop constraint constraint_name cascade;

　　这个时候一共删除了两个约束，一个是主表中的主键，一个是子表中的外键

　　续二：删除表中字段时，若该字段处于多字段联合约束条件(联合主键、联合唯一键、存在参照当前字段的外键)中时

　　则删除会失败。此时可使用cascade constraints子句将与该字段相关的约束一并删除

　　语法为alter table table drop(column[,column]...) cascade constraints;

　　禁用：在alter table中，可使用disable constraint子句禁用已有约束。也可用cascade选项将相关联的约束也一并删除

　　语法为alter table table disable constraint constraint_name [cascade];

　　禁用跟删除不同，它还可以启用。而且约束的具体内容或约束的定义等，还是存在的，只是临时不起作用了

　　启用：在alter table中，可使用enable constraint子句启用已被禁用的约束

　　语法为alter table table enable constraint constraint_name;

　　启用约束后，就无法再使用cascade选项一并启用相关联的其它约束。若仍想使用其它约束，则只能重建其它约束

　　非空约束(not null)

　　特点：只能在字段级定义。确保字段值不能为空。一个表中可以定义多个非空约束

　　举例：create table strudent(sid number(3) not null,name varchar2(20),birth date constraint nn not null);

　　说明：constraint nn not null也是非空约束，只不过birth的约束名字是由用户命名的，而sid的约束是由系统命名的

　　constraint是一个保留字，说明这里要添加一个约束，nn是约束的名字，后面的not null是约束的类型

　　按照惯例，约束名习惯命名为“表名_字段名_约束的类型简称”这里就应该是student_birth_nn

　　唯一性约束(unique)

　　特点：既可以在字段级定义，也可以在表级定义。用于确保所在的字段(或字段组合)不出现重复值

　　唯一性约束的字段允许出现空值。Oracle会自动为唯一性约束创建对应的唯一性索引。一个表中可以定义多个唯一键

　　举例：create table student(sid number(3) unique,name varchar2(20));--字段级定义

　　create table student(sid number(3),name varchar2(20),constraint strudent_sid_un unique(sid));--表级约束

　　说明：对一个字段进行唯一性约束时，这两种效果相同的。若需要对多个字段定义唯一性约束的话，则只能定义成表级约束

　　如create table fenshu(a number(3),b varchar2(20),c number(4),constraint fenshu_a_b_un unique(a,b));

　　也就是说a和b两个字段的值组合起来不能出现重复。可以把它想象成是一张学生分数表，a和b相当于学号和科目

　　主键约束(primary key)

　　特点：主键既可以在字段级定义，也可以在表级定义。主键用于唯一标识表中的某一行记录，功能上相当于非空且唯一

　　一个表中只允许一个主键，主键可以是单个字段或多字段的组合。Oracle会自动为主键字段创建对应的唯一性索引

　　举例：create table student(sid number(3) primary key, name varchar2(20));

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

　　联合：由多个字段组合而成的主键也称联合主键。联合主键中每一个字段都不能为空

　　联合主键字段组合的值不能出现重复。联合主键只能定义为表级约束

　　create table fenshu(a number(3),b varchar2(20),c number(3),constraint fenshu_a_b_pk primary_key(a,b));

　　同样是学生分数表。其实学号和科目组合起来更应该设成主键，而不是唯一键，因为它们的值也不应该为空

　　外键约束(foreign key)

　　特点：外键用于确保相关的两个字段之间的参照关系，以实现参照完整性约束。外键参照的必须是主表的主键或者唯一键

　　外键约束通常构建于来自不同表的两个字段之间。子表外键列的值必须在主表参照列值的范围内，或者为空

　　主表的主键或唯一键被子表参照时，主表相应记录不允许被删除。

　　参照：所谓的参照完整性约束，比如说员工信息表和工资表，二者是通过员工编号建立连接的

　　这时工资表中的员工编号是受限制的，即必须是出现在员工信息表中的，这种关系称之为参照关系

　　举例：create table info(id number(3) primary key,name varchar2(20),job varchar2(20),birth date);

　　create table sal(a number(3) references info(id),b number(8,2));--将外键约束定义成字段级约束

　　create table sal(a number(3),b number(8,2),constraint sal_a_fk foreign key(a) references info(id));

　　说明：子表sal中的a字段被定义为外键，它参照的是主表info中的id字段。这里是把外键约束定义成了表级约束

　　执行完这两行语句之后，两个表中均没有数据。若再执行insert into sal(a,b) values(1,2);则会出现错误

　　因为系统并没有在子表所参照的主表中的某一条记录上找到id为1的字段的值

　　此时可以在刚才的插入语句之前先执行insert into info values(1,'Tom','Adv',sysdate);就不会出现错误了

　　如果此时再执行insert into sal(null,8);则该记录插入成功。外键毕竟不同于主键，这也不算违背参照规则

　　接着再执行一次insert into sal(1,8)则记录插入成功。这种插入null和重复记录的方式都是不合理的

　　为了实现数据的合理化，此时也可以进行其它的限制。即字段a在作为外键的同时，还可以进行主键的限制

　　即create table sal(a number(3) primary key references info(id),b number(8,2));

　　或create table sal(a number(3) primary key,b number(8,2),constraint sal_a_fk foreign key(a) references info(id));

　　检查约束(check)

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

　　条件中不允许出现currval,nextval,level,rownum等伪列或sysdate,uid,user,userenv等函数或对其它字段值的引用

　　currval即序列当前的值。nextval即序列生成器的下一个序列值。level即在层次化查询的过程中标明查询的层数

　　sysdate即当前系统时间。uid即当前用户的id号。user即当前用户名。userenv用来查询客户端的环境，包括语言或用户名等等

　　举例：create table test1(name varchar2(20) check(length(name)>=6),age number(3) check(age>=0 and age

　　create table test1(name varchar2(20) check(name is not null),age number(3));--这就等价于非空约束了

　　视图(View)

　　概述：视图由一个或多个表(或视图)中提取数据而成。视图是一种虚拟表。视图一经创建就可以当作表来使用

　　使用视图可以简化复杂的数据查询。能够提高运行效率。可以屏蔽数据库表结构，实现数据逻辑独立性

　　还可以限制数据库访问。也可以在相同数据上提供不同的视图，便于数据共享。视图也可以有主键等等

　　可以通过在create view语句中嵌入子查询的方式创建视图

　　创建：create [or replace] view [schema.]view [(alias[,aliasx]...)] as subquery;

　　举例：create or replace view v1(编号,姓名,工资) as select empno,ename,sal from emp where deptno=20;

　　建议在创建视图时加上or replace，即如果存在重名视图，则替换掉重名视图

　　而且不允许在括号中列出字段类型，因为具体的字段类型由子查询的结果决定

　　强调：可用force选项强制创建视图。即无论预期中的字段或基表是否存在，都要强制创建视图

　　也就是先给出一个视图的定义。但此时不能对这个连基表都不存在的视图进行查询，原因不言自明

　　语法为create [or replace] [force|noforce] view [schema.]view [(alias[,aliasx]...)] as subquery;

　　比如create or replace force view v2 as select empno,ename,job,sal from emp2 where deptno=20;

　　假定此时不存在emp2表，当然这些字段存不存在就无从谈起了，如果不使用force选项，肯定无法创建视图

　　缺省为不强制(即noforce)创建视图。实际上不建议强制创建视图

　　查询：和查询表数据一样，可以使用select * from v1;来查询视图中的数据

　　它的实现细节是这样的，当数据库服务器接收到应用程序或SQLPlus等客户端软件发送过来的查询视图的指令的时候

　　首先会在当前方案下寻找名字叫v1的视图定义，即定义视图的create语句，也就是create or replace view v1...

　　找到v1的定义之后，便执行定义中的子查询，再把查询的结果返回给客户端

　　这意味着每次引用或者访问视图的时候，都会执行一次子查询，都会查一下底层的物理表

　　所以物理表中的任何数据更新，都会立即在视图的查询中体现出来

　　续一：可以用desc v1查看视图结构。可以用drop view v1删除视图

　　所谓的临时表是在程序运行的过程中，根据需要而临时创建的一张表。通常是利用create table创建临时表

　　临时表只是临时用到它，用完了便删除。类似于临时文件，但临时表所保存的是真正的数据，这跟虚拟表不同

　　续二：创建视图的时候，也可以不指定字段名。缺省视图的字段属性都与子查询结果中的字段属性相同

　　若子查询中使用了别名，视图则采用别名作为它的字段名。而且子查询中的表达式或函数不允许作为视图的字段名

　　续三：实际上视图的定义和表的定义一样，都应该在应用程序开发的时候，在数据库设计阶段就确定下来它们数目个功能

　　即创建一定数量的表，以保存最底层基础的数据。并设定好哪些数据需要以视图的方式交由具体的程序模块去使用

　　复杂：也可以创建复杂视图，也就是说子查询还可以复杂一些

　　如果某业务经常需要查询统计信息，那么只需要创建一个这样的视图就可以了，而不必每次都执行子查询以获得数据

　　比如create or replace v_emp(工号,姓名,职位,年薪,工龄(月),部门编号,部门名称,)

　　as select empno, ename, job, sal*12, month_between(sysdate,hiredate), emp.deptno, dname

　　from emp, dept where emp.deptno=dept.deptno;

　　更新：在可更新视图上进行DML操纵，可以修改基表中的数据。语法与在表上操作相同，分别为insert、update、delete语句

　　可更新视图的定义中不能使用分组函数、group by子句、distinct关键字、rownum伪列，而且字段的定义不能为表达式

　　由两个以上基表中导出的视图不可更新。基表中非空的列在视图定义中未包括，则不可在视图上进行insert操作

　　这种操作很容易出错，实际上在真实的开发中很少这么做，也不建议通过视图去更新底层数据

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

　　语法为create [or replace] view [schema.]view [(alias[,aliasx]...)] as subquery [with read only];

　　临时：嵌入到SQL语句中的子查询都是临时视图。比如说用子查询建表或TopN分析等等所涉及到的子查询都属于临时视图

　　临时视图不是数据库对象，其定义不会长久保存在数据库中，本次运行后即被清除。它类似于Java语言中创建的匿名类

　　索引(Index)

　　概述：是一种用于提升查询效率的数据库对象。索引信息与表独立存放。它可以通过快速定位数据的方法，减少磁盘I/O操作

　　索引分为两类，即唯一性索引和非唯一索引。Oracle数据库自动使用和维护索引

　　创建：自动创建：在定义主键或唯一键约束时，系统会自动在相应的字段上创建唯一性索引

　　手动创建：用户可以在其它列上创建非唯一的索引，以加速查询

　　语法为create index [schema.]index on table(column[,column]...);

　　比如说create index myindex on emp(ename);--查询表时，若以ename作为查询条件的话，就很容易定位到某行记录了

　　删除：使用drop index语句删除索引，如drop index myindex;操作者必须是索引的所有者，或拥有drop该index的权限

　　删除表时，相关的索引(和约束)将被自动删除，但视图和序列将保留

　　原则：适合于创建索引：字段取值的分布范围很广。字段中包含大量空值。字段经常出现在where子句或连接条件中

　　表经常被访问，或表数据量很大时，且通常每次访问的数据量小于记录总量的2%～4%

　　不适合创建索引：表很小。字段不经常出现在where子句中。每次访问的数据量大于记录总数的2%～4%

　　表经常更新。被索引的字段作为表达式的一部分被引用

　　查看：查询用户字典视图user_indexes可得到用户的所有索引。如select * from user_indexes;

　　查询用户字典视图user_ind_columns可获知索引建立在哪些字段上。如select * from 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';

　　在使用时，条件中的字段需要与创建索引时指定的相同，索引才会起作用。即lower(ename)需要前后一致

　　序列(Sequence)

　　概述：是由系统自动生成的，不重复的整数值。序列是一种数据库对象，可以被多个用户共享。序列可以代替应用程序编号

　　序列的最典型的用途是作为主键值，它对于每一行必须是唯一的。可以对序列值进行缓冲存储，以提高访问效率

　　但在应用程序中生成编号的话，会产生性能瓶颈的。也就是说比较麻烦，要确保它的唯一性，还要它的保证效率

　　所谓的缓冲存储是指，在使用序列之前，先生成指定的多个序列的取值作为备用，这样能提高访问效率

　　从实际应用的角度来讲，序列的重要性远远超过视图和索引，跟约束条件以及表的重要性相当

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

　　创建：create sequence [schema.]sequence [increment by n][start with n]

　　[{maxvalue n|nomaxvalue}][{minvalue n}|nominvalue][{cycle|nocycle}][{cache n|nocache}][{order|noorder}];

　　举例：create sequence mysequence2;

　　create sequence mysequence1 increment by 1 start with 1 nomaxvalue nocycle;

　　释一：increment指明当前序列号递增的值，缺省为1。start with指定当前序列号的起始值，缺省为1

　　maxvlaue设定序列号的最大值。缺省为nomaxvalue，即没有最大值。同理minvalue就是设定序列号的最小值

　　cycle设置在序列达到最大值之后，是否重新从1开始循环，缺省为nocycle，即不循环

　　释二：实际上nomaxvalue在实现的过程中，是有最大值的，大约是10的27次方。同理nominvalue是10的负27次方

　　而且在使用序列的时候，一般不应该让它循环。避免出现预期以外的数据的重复

　　释三：cache设置是否进行缓冲存储。它的缺省值不是nocache，而是cache 20

　　也就是说cache会先生成20个序列号备用，当序列号被用掉了一个之后，它就会再补充上一个

　　这种通过备用的方式，能够使程序运行效率会高一点。但是缓存序列号有可能会有丢失，倒不是出错，只是不连续而已

　　释四：order保证生成的序列号一定是按照请求的顺序。其实对于单进程或者单线程的程序(即非并发性的访问)来说没有什么差别

　　若在并发(也叫并行)工作模式下，多个请求同时请求同一个序列号的时候，分配给它们序列号就有可能是不保证顺序的

　　只是顺序不保证，但值还是唯一的，不会重复。实际上它对我们来说影响不大。比如有两个请求先后同时到达

　　如果用order来保证的话，那么先到达的请求所请求到的序列号就会是101，后到达的就是102

　　但如果nooder就可能出现先到达的请求得到的反而是102，后到达的可能是101，因为中间有一个交替的过程

　　缺省为noorder，即不保证序列号是按照请求顺序生成的

　　使用：select mysequence1.nextval from dual;--其实它会新生成一个序列号，返回一个整数值

　　select mysequence1.currval from dual;--取出当前的序列号。这种直接的查询，意义不大

　　insert into test1 values(mysequence1.nextval,'Tom');--这才是真正的使用序列

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

　　使用缓存(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 fro object;

　　create synonym gt1 for emp;

　　使用：select * from gt1;

　　删除：drop synonym gt1;

　　说明：若不加public，那么定义的同义词则只能在当前方案(用户)的环境中可用，其它用户则无法使用该同义词

　　而加了public后，其它的方案(用户)便也可以使用这个同义词了

　　但必须是具有相应权限的用户(如DBA)才有资格将同义词定义为public类型