Oracle超详细笔记5--表管理

1.模式与模式对象

模式概念(schema)，又称方案：

是指一系列逻辑数据结构或对象的集合。

 

模式与用户的关系：

模式与用户相对应，一个模式只能被一个数据库用户所拥有，并且模式的名称与这个用户的名称相同。

通常情况下，用户所创建数据库对象都保存在与自己同名的模式中。

同一模式中数据库对象的名称必须唯一，而在不同模式中的数据库对象可以同名。

默认情况下，用户引用的对象是与自己同名模式中的对象，如果要引用其他模式中的对象，需要在该对象名之前指明对象所属模式。

模式对象是一种逻辑数据存储结构。模式对象与数据文件并不存在物理上的一一对应关系。

由于Oracle系统将模式对象存储在表空间中，因此一个模式对象可能被存储在一个表空间的多个数据文件中。

模式对象的类型有表、索引、索引化表、簇、触发器、数据库链接、PL/SQL包、序列、同义词、视图和存储函数与存储过程、JAVA类与其他JAVA。

当用户在数据库中创建一个模式对象后，这个模式对象默认地属于这个用户的模式，如果其他用户需要使用这个模式对象，则必须在对象名前加上它所属的模式名。

 

模式选择与切换：

1.如果用户以NORMAL身份登录，则进入同名模式；

2.若以SYSDBA身份登录，则进入SYS模式；

connect  /  as sysdba

3.如果以SYSOPER身份登录，则进入PUBLIC模式。

 

2.数据类型

数据类型：数据类型分为基本数据类型和用户自定义数据类型。 

 

基本数据类型：

数值型  NUMBER、DECIMAL、INTEGER、FLOAT和REAL

字符型   CHAR、NCHAR、VARCHAR2、NVARCHAR2

日期型   DATE、TIMESTAMP

行标识类型  ROWID

LOB类型   BLOB、CLOB和BFILE

 

数字类型

NUMBER(m，n)

用于存储整数和实数。m表示数值的总位数（精度），取值范围为1～38，默认为38；n表示小数位数，若为负数则表示把数据向小数点左边舍入，默认值为0

 

字符类型

CHAR(n)

定长字符串，n的取值范围为1-2000字节

VARCHAR2(n)

可变字符串，n取值范围为1-4000字节，自动调整数据长度

NCHAR(n)

用来存储Unicode类型字符串。

NVARCHAR2(n)

它用来存储Unicode类型字符串。

 

日期类型

DATE

可以存储的日期范围为公元前4712年1月1日到公元4712年1月1日。 Oracle标准日期格式为：DD-MON-YY HH:MI:SS。

按7个字符来保存日期数据，如07-10月-12 表示2012年10月7日。

可用函数to_date指定特定格式，如to_date(‘2012-10-18’,’yyyy-mm-dd’)。

TIMESTAMP[(n)]：

表示时间戳，是DATE 数据类型的扩展，允许存储小数形式的秒值。n表示秒的小数位数，取值范围为1～9，默认值为6。可精确到毫秒级，对时间精度要求高的时候使用，如银行。

TIMESTAMP[(n)] WITH TIME ZONE

通过存储一个时区偏差来扩展TIMESTAMP类型。 时区偏差值为相对于通用协调时间（或称UTC，以前称为格林威治时间或GMT）的时差。

 

行类型

ROWID

行标识符，表中行的物理地址的伪列类型。ROWID类型数据由18位十六进制数构成，包括数据对象编号(1-6)、数据文件编号(7-9)、数据块编号(10-15)和块内行号(16-18)。

Select rowid,empno from scott.emp where empno=7369;

UROWID

行标识符，用于表示索引化表中行的逻辑地址。

 

LOB类型（大对象数据类型）

CLOB

用于存储可变长度的字符数据，如文本文件等，最大数据量为4 GB。

NCLOB

用于存储可变长度的Unicode字符数据，最大数据量为4 GB。

BLOB

用于存储大型的、未被结构化的可变长度的二进制数据（如二进制文件、图片文件、音频和视频等非文本文件），最大数据量为4 GB。

BFILE

用于存储指向二进制格式文件的定位器，该二进制文件保存在数据库外部的操作系统中，文件最大为4 GB。

 

用户自定义数据类型

例：

SQL> create or replace type mytype  as object
    (
       province varchar(20),
       city varchar(30),
       details varchar(40)
     )；
类型已创建。
    SQL> create table studentobj
    (
      stuname varchar(10),
      stuaddress mytype
     )；

 表已创建。
SQL> insert into studentobj values('Tom',MYTYPE('浙江省','宁波','海曙'));

SQL>select * from studentobj;

 

3.创建表

用户可以通过Oracle提供的SQL*Plus中使用CREATE TABLE语句创建表，也可以通过OEM图形化工具创建表。用户使用CREATE TABLE语句创建表必须具有CREATE TABLE系统权限，在其他模式中用户创建表必须有CREATE ANY TABLE权限。

例：在当前模式中创建一个具有4列的Student表

CREATE TABLE Student

(

cNo    CHAR(10),

vName  VARCHAR2(10),

nAge    NUMBER(3),

cDeptid   CHAR(4)

);

用户使用CREATE TABLE语句创建了一个名称为Student的学生表，该表包含cNo(学生学号)、vName(学生姓名)、nAge（年龄）和cDeptid(学生所在院系编号)4个列。

 

例：在当前模式中创建一个具有4列的Student表，并指定它位于USERS表空间。

CREATE TABLE Student

(

cNo    CHAR(10),

vName  VARCHAR2(10),

nAge    NUMBER(3),

cDeptid   CHAR(4)

) TABLESPACE USERS;

 

创建表时仅为表指定了表空间，而没有设置存储参数，它将自动采用所属表空间的默认存储参数设置，但表空间的默认存储参数设置并不一定对表空间中的每个表都适合，因此有时需要在创建表时显式指定存储参数设置，而不使用默认存储参数。对存储参数的设置可以通过在CREATE TABLE语句中使用STORAGE子句来实现。

在STORAGE子句中可以设置下面5个存储参数：

INITIAL

指定为表中数据分配的第一个盘区大小，以KB或MB为单位，默认为5个Oracle数据块的大小，当为已知数量的数据创建表时，可以将INITIAL设置为一个可以容纳所有数据的容量，这样可以将表中所有数据存储在一个盘区中，以免产生碎片。

NEXT

指定为表中数据分配的第二个盘区大小，以KB或MB为单位，默认为5个Oracle数据块的大小。当表的第一个盘区被填满后，NEXT参数将控制Oracle为随后的盘区分配空间大小。

PCTINCREASE

表示从第二个盘区之后，每个盘区相对于上一个盘区的增长百分比，每次分配盘区的时候，下一个盘区的大小都要根据PCTINCREASE比例增长，这意味着每次分配盘区的时候，用户盘区容量都会增长，通常情况下，建议将PCTINCREASE设置为0。

MINEXTENTS

指定允许为表中的数据分配的最小盘区数目。例如，在创建表时设置参数INITIAL和NEXT为1MB，PCTINCREASE设置为0，MINEXTENTS设置为6，则意味着创建表时将分配6个1MB的盘区。

MAXEXTENTS

指定允许为表中的数据分配的最大盘区数目，默认值为UNLIMITED（不受限制）。

 

例：在当前模式中创建一个具有4列的Student表，并通过STORAGE子句为它指定存储参数

CREATE TABLE Student

(

cNo    CHAR(10),

vName  VARCHAR2(10),

nAge    NUMBER(3),

cDeptid   CHAR(4)

) TABLESPACES USERS

STORAGE

(

 INITIAL  64K

 NEXT  50K

 MINEXTENTS  1

 MAXEXTENTS  unlimited

 PCTINCREASE  10

);

 

用户在创建表时，Oracle允许用户可以设置数据块空间管理参数：PCTFREE、PCTUSED、INITRANS和MAXTRANS。

PCTFREE和PCTUSED这两个参数都用于控制数据块中空闲空间的使用，其中PCTFREE用于控制数据块的更新预留的空间；而PCTUSED用于控制数据块在删除时释放空间的门槛。

PCTFREE

参数为数据块中行的更新预留了空闲空间的最小百分比，默认值为10。如果在CREATE TABLE语句中指定PCTFREE为20，则说明在该表的数据段内每个数据块的20%被作为可利用的空闲空间，用于更新已在数据块内存在的数据行。其余80%用于插入新的数据行。若PCTFREE设置得太高，则浪费磁盘空间；若PCTFREE设置得太低，则会导致迁移记录。

PCTUSED

参数设置了数据块是否空闲的界限，当数据块的使用空间低于PCTUSED的值时，此数据块标志为空闲，该空闲空间仅用于插入新的记录。如果数据块的使用空间已经达到PCTUSED的值时，此数据块标志为不可用，该空闲空间无法插入新的记录。

当实际应用UPDATE操作教多，并且更新操作会增加记录的大小时，可将PCTFREE设置大些，PCTUSED设置得小些；当实际应用INSERT和DELETE操作教多，并且更新操作不会增加记录的大小时，可将PCTFREE设置小些，PCTUSED设置得大些。注意设置数据块的PCTFREE和PCTUSED两个参数时，该两个参数值的和必须等于或小于100。

INITRANS和MAXTRANS

这两个参数用于控制能够并发访问数据块的事务数量，同时也会影响到数据块头部的空间使用情况。

 

例：在当前模式中创建一个具有4列的Student表，并将表的PCTFREE和PCTUSED参数设置为20和40,同时指定能够并发访问的事务总数为100，在数据块头部可以存放20个事务条目。

CREATE TABLE Student

(

cNo    CHAR(10),

vName  VARCHAR2(10),

nAge    NUMBER(3),

cDeptid   CHAR(4)

) TABLESPACES USERS

STORAGE

(

 INITIAL  64K

 NEXT  50K

 MINEXTENTS  1

 MAXEXTENTS  unlimited

 PCTINCREASE  10

)

PCTFREE  20

PCTUSED  40

INITRANS  20

MAXTRANS 100;

 

用户在创建表时可以使用NOLOGGING子句，使得对表的操作不会产生重做日志，由于不能在重做日志文件中记录表的操作，无法用数据库恢复操作来恢复表丢失的数据。一般创建表将使用LOGGING子句。

例：在当前模式中创建一个具有4列的Student表，并使表的操作不会被记录到重做日志文件中。

CREATE TABLE Student

(

cNo    CHAR(10),

vName  VARCHAR2(10),

nAge    NUMBER(3),

cDeptid   CHAR(4)

) TABLESPACES USERS

STORAGE

(

 INITIAL  64K

 NEXT  50K

 MINEXTENTS  1

 MAXEXTENTS  unlimited

 PCTINCREASE  10

) NOLOGGING;

 

4.表的修改

增加列

ALTER TABLE 表名 ADD 字段名 字段类型；

 

修改列名

ALTER TABLE 表名 RENAME COLUMN 原字段名 TO 新名；

 

修改列类型

ALTER TABLE 表名 MODIFY 字段名 新类型；

 

修改列默认值

ALTER TABLE 表名 ALTER COLUMN 列名 SET DEFAULT 默认值；

 

重命名表

ALTER TABLE 表名 RENAME TO 新表名；

 

移动表

ALTER TABLE 表名 MOVE TABLESPACE 表空间；

 

删除列

ALTER TABLE 表名 DROP COLUMN 字段名；

ALTER TABLE 表名 DROP COLUMN (字段1名,字段2名,…)；

 

使用unused关键字

例：

(1)将Student表中的nAge和vAddress两个列标记为UNUSED状态。

ALTER TABLE Student SET UNUSED (nAge, vAddress);

(2)删除Student表中被标记为UNUSED状态的字段。

ALTER TABLE Student DROP UNUSED COLUMN;

 

修改表的存储参数

表创建后可以使用ALTER TABLE语句来改变表的块参数和部分存储参数。

例：

修改表Student中的PCTFREE与PCTUSED参数

ALTER TABLE Student

       PCTFREE  30

       PCTUSED  50;

 

用户可能需要明确地为表分配一个指定大小的新盘区，这时可通过使用ALTER TABLE语句的ALLOCATE EXTENT子句以手动方式为表分配存储空间。

例：

(1) 为表Student手动分配存储空间，并且新盘区大小由参数NEXT设置。

ALTER  TABLE  Student  ALLOCATE  EXTENT;

(2) 为表Student手动分配一个大小为500KB的新盘区

ALTER  TABLE  Student  ALLOCATE  EXTENT (SIZE 500K);

(3) 为表Student在指定的数据文件中分配一个大小为1MB的新盘区。

ALTER  TABLE  Student  ALLOCATE  EXTENT (SIZE 1M

DATAFILE  'd:/oracle/data01.dbf');

注意：在ALTER TABLE语句的ALLOCATE EXTENT子句中，如果为分配的新盘区指定所在数据文件时，该数据文件必须属于表所处的表空间。

 

一般情况下，创建表时默认使用NOCACHE子句，但有时为了提高查询执行效率，对比较小而且经常查询的表，用户可以利用CACHE子句将表放入缓存中。

例：

ALTER TABLE Student  ALLOCATE  CACHE;

 

通常情况下Oracle不会自动回收已经为表分配的存储空间，而且没有必要回收表的存储空间，但有时为表分配过多的存储空间，会造成大量资源的浪费和利用率低。如PINCREASE参数设置不当，导致为数据段分配的区越来越大，在分配的区中存在过多的空闲空间。在这些情况下，需要手动回收表的存储空间，以便重新利用被浪费的存储空间。

例：

假设已经为表Student分配了3个盘区：第一个盘区的大小为10KB，第二个盘区的大小为20KB，第三个盘区的大小为30KB，HWM位于第二个区的中央（HWM为已经使用的存储空间与未使用的存储空间的分界线）。现在使用ALTER TABLE语句回收表Student中的空闲空间。

ALTER  TABLE  Student  DEALLOCATE  UNUSED

执行该语句后，Student表中只剩下前两个区，第三个区被完全回收，并且第二个区变为10KB。

 

5.表的删除

用户使用DROP TABLE语句在当前自己的模式（schema）中删除表，必须具有DROP TABLE系统权限，在其他模式中用户删除表必须有DROP ANY TABLE权限。

例：删除表Student

DROP  TABLE  Student；

 

若要求在删除表时，同时删除引用这个表的视图、约束或触发器等，这时将使用DROP TABLE语句中的可选参数CASCADE CONSTRAINTS。

例：

删除表Student以及存储在其中的数据，同时删除引用该表的视图、约束、触发器等。

DROP  TABLE  Student CASCADE CONSTRAINTS；

 

要想截断表，也就是删除表内的所有数据，可以使用TRUNCATE TABLE命令，注意该命令仅删除数据，不删除表的定义。

例：TRUNCATE  TABLE  Student

 

6.完整性约束

实体完整性约束主要指主键约束和唯一性约束

参照完整性约束主要指外键约束

域完整性约束主要指检查约束和非空约束。

 

主键约束的基本语法：

CONSTRAINT 约束名 PRIMARY KEY (列名1【，列名2...】)

（1）在创建Student表的同时指定sno属性为主键，可使用如下语句：

CREATE TABLE Student
(

sno NUMBER(8),
  sname VARCHAR(20),
  sage NUMBER(20),

CONSTRAINT PK_SNO PRIMARY KEY (sno)

);

其中：PRIMARY KEY（SNO）表示SNO是Student表的主键。PK_SNO是此主键约束名。

也可以写成如下语句：

CREATE TABLE Student
(

sno NUMBER(8) PRIMARY KEY,
  sname VARCHAR(20),
  sage NUMBER(20)

);

（2）在修改Student表的同时指定sno属性为主键

ALTER TABLE Student ADD CONSTRAINT PK_SNO PRIMARY KEY (SNO);

（3）删除Student表的主键约束

ALTER TABLE Student DROP CONSTRAINT PK_SNO

通过以下语句可查看约束是否已经被删除

SELECT CONSTRAINT_NAME,COLUMN_NAME

FROM USER_CONS_COLUMNS

WHERE TABLE_NAME=’Student’

（4）关闭Student表的主键约束

ALTER TABLE Student DISABLE CONSTRAINT PK_SNO; 

(5）启用Student表的主键约束

ALTER TABLE Student ENABLE CONSTRAINT PK_SNO;

 

唯一约束的基本语法：

CONSTRAINT 约束名 UNIQUE (列名)

例：

（1）在创建Student表的同时指定sno属性为主键，可使用如下语句：

CREATE TABLE Student
(

sno NUMBER(8) ,
  sname VARCHAR(20),
  sage NUMBER(20),

CONSTRAINT PK_SNO UNIQUE(sno),

CONSTRAINT U_SNAME UNIQUE(sname),

);

也可以写成如下语句：

CREATE TABLE Student
(

sno NUMBER(8) PRIMARY KEY,
  sname VARCHAR(20) UNIQUE,
  sage NUMBER(20)

);
（2）在修改Student表的同时为sname属性添加唯一约束

ALTER TABLE Student ADD CONSTRAINT U_SNAME UNIQUE (SNAME);

（3）删除Student表的唯一约束

ALTER TABLE Student DROP CONSTRAINT U_SNAME

 

外键约束的基本语法：

constriant 约束名 foreign key(列名) references 表名（列名）

例：

（1）创建部门表DEPT和职员表EMP,在EMP表DEPTNO字段上创建外键约束，引用表DEPT中DEPTID字段数据。可使用如下语句：

CREATE TABLE DEPT
(

DEPTID CHAR(10) PRIMARY KEY,
  DEPTNAME VARCHAR(20)

);

CREATE TABLE EMP
(

EMPNO CHAR(8) PRIMARY KEY,
  EMPNAME VARCHAR(20),
  DEPTNO CHAR(10),

CONSTRAINT FK_DEPTNO FOREIGN KEY(DEPTNO)  REFERENCES DEPT(DEPTID)

);

（2）在修改EMP表的同时指定DEPTNO属性为外键

ALTER TABLE EMP ADD CONSTRAINT FK_DEPTNO FOREIGN KEY(DEPTNO)  REFERENCES DEPT(DEPTID);

（3）删除EMP表的外键约束

ALTER TABLE EMP DROP CONSTRAINT FK_DEPTNO

 

检查约束的基本语法：

constriant 约束名 CHECK(条件表达式）

检查约束是用来核查每一行的值必须符合约束条件的完整性约束。Oracle提供CHECK子句，供用户指定关系的检查约束。

例：

（1）创建表EMP并约束部门代号在1-99之间。可使用如下语句：

CREATE TABLE EMP
(

EMPNO CHAR(8) PRIMARY KEY,
  EMPNAME VARCHAR(20),
  DEPTNO CHAR(10),

CONSTRAINT CHK_DEPTNO CHECK(DEPTNO BETWEEN 1 AND 99)

);

当输入数据时，其部门代号不在1-99之间，则会违反CHECK约束。

（2）在修改EMP表的同时约束部门代号在1-99之间

ALTER TABLE EMP ADD CONSTRAINT CHK_DEPTNO CHECK(DEPTNO BETWEEN 1 AND 99);

如果想查询CHECK约束条件，可以查询USER_CONSTRAINTS表中的SEARCH_CONDITION字段，具体语句如下：

SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,SEARCH_CONDITION

FROM USER_CONSTRAINTS  

WHERE TABLE_NAME=’EMP’

（3）删除EMP表的检查约束

ALTER TABLE EMP DROP CONSTRAINT CHK_DEPTNO

 

非空约束的基本语法：

字段  数据类型 NOT NULL

例5-27：

（１）创建表EMP并约束职员姓名不能为空。可使用如下语句：

CREATE TABLE EMP
(

EMPNO CHAR(8) PRIMARY KEY ,
  EMPNAME VARCHAR(20) NOT NULL,
  DEPTNO CHAR(10)

);

当输入数据时，若其职员姓名为空，则会违反非空约束。

（２）修改表EMP并约束职员姓名不能为空

ALTER TABLE EMP MODIFY EMPNAME NOT NULL

注意：对于NOT NULL的ALTER TABLE语句与其他约束稍微有点不同。