定义、修改、删除表(转)

 

    一、创建表(Creat Table)

    可以使用CREATE TABLE命令创建一个新表。此命令最简单的形式之一是：只包括定义各列的名字、型和大小的基本信息。

语法：CREATE TABLE 〈表名〉

(〈列名〉〈数据类型〉(宽度)，[，〈列名〉〈数据类型〉...])

[〈其它参数〉]

其中：〈表名〉为要创建的表名，〈其它参数〉后两陆续讲。

例2.4：创建一个雇员表DEPT：

CREATE TABLE DEPT

(员工号 NUMBER(2)，

雇员名 VARCHAR2(12)，

住址 VARCHAR2(12))；

在一个表中的那些列名必须是唯一的，不能重复。

1．列的型

    创建表时，必须指定各个列的数据类型。例5.4中显示了最重要的数据型。

    数据类型的宽度可以由跟在其后的括号中的一个或多个数来表示。列的宽度决定列中所具有值的最大宽度。VARCHAR2列必须指定宽度大小。NUMBER和CHAR可以给宽度大小，也可以缺省。

    下面示出ORACLE7支持主要内部数据类型：

    ．VARCHAR2 变长字符串，最长为2000字符。

   （或VARCHAR）

    ．NUMBER 数值型。

    ．LONG 变长字符数据，最长为2G字节。

    ．DATE 日期型。

    ．RAW 二进制数据，最长为255字节。

    ．LONG RAW 变长二进制数据，最长为2G字节。

    ．ROWID 十六进制串，表示表的行的唯一地址。

    ．CHAR 定长字符数据，最长为255，缺省值为1。

(1)字符数据类型：

    数据类型用于存贮数据库字符集里的字符数据(字母数字)，可操作词和自由格式的文本。与其它数据类型比较，其限制量最少。

    ORACLE支持单字节和双字节两种字符集。

    字符数据可使用的数据类型为：

   ．CHAR

   ．VARCHR2

   ．VARCHAR

   目前VARCHAR数据类型与VARCHAR2意义相同，在ORACLE未来的版本中，VARCHAR可以是一种不同数据类型，用于变长字符串，具有不同比较语义。

(2)NUMBER 数据类型：

   NUMBER数据类型用于存贮零、正负定点或浮点数，其大小在1.0×10-130和9.9...9×10125 之间，最大精度有38数字。如果一算术表达式其值大于或等于1.0×10126 时，ORACLE将返回一个出错信息。用下列语法可指定点数据类型：

    NUMBER（P，S）

其中：P是精度，或总的数字数。精度范围是从1到38。S是比例，或是小数点右边的数字位。比例的范围从一84至127。

例2.5：如果有一个真实数据7,456,123.89，

   若指定数据 则存贮数据

NUMBER(9) 7456124

NUMBER9(9，2) 7456123.89

NUMBER(9，－2) 7456100

(3)LONG数据类型:

   LONG数据类型的列存贮变长字符串，其最大长度为2G字节(或231－1个字节)。LONG列具有VARCHAR2列的许多特征，用它可存贮长的文本串。LONG类型的值受到计算机可用内存的限制。

   在SQL语句中在下列方面可引用LONG列：

   ．SELECT的选择表。

   ．UPDATE语句的SET子句。

   ．INSERT语句的VALUES子句。LONG值使用的限制：

   ．一个表中不能有多于一个LONG列。

   ．LONG列不能出现在完整性约束中。

   ．LONG列不能索引。

   ．过程或存贮函数不能接收LONG类型的变元(或叫变量)。

   ．存贮函数不能返回一个LONG类型的值。

   ．在单个SQL语句中，全部LONG列、序列、被修改的表和被封锁表必须位于同一个数据库。

而且，LONG列不能出现在SQL语句的某些部分：

   ．在WHERE、GROUP BY、ORDER BY、CONNECT BY子句以及在具有DISTINCT操作的SELECT语句中。

   ．SQL函数(如SUBSTR或INSTR)。

   ．表达式或条件。

   ．包含有GROUP BY子句的查询的选择表。

   ．在子查询的选择表或由集合操作组合查询选择表。

   ．CREATE TABLE AS SELECT语句的选择表。

   ORACLE在本书5.6节要讲到的数据字典中，利用LONG数据型存贮视图定义的文本。

(4)DATE数据类型

   DATE数据类型用于存贮日期和时间信息。虽然日期和时间信息也可以表示成CHAR和NUMBER数据类型，但DATE数据类型有特殊关联特性。每一个DATE值可存贮下列信息：

   ．世纪(Century)

   ．年 (year)

   ．月 (month)

   ．日 (day)

   ．时 (hour)

   ．分 (minute)

   ．秒 (second)

    没有日期型直接量，如果要指定日期值，必须用函数TO_DATE将字符型的值或数值转换成一个日期型的值。在日期型表达式中，默认日期格式由初始化参数NLS_DATE_FORMAT所指定，是字符串，如‘DD_MON_YY’。在指定一个日期型值时，如果未带有时间成分，则默认时间为12：00：00ａｍ。如果指定一个日期型值，不带有日期成分，则默认的日期是当月的第一天。

   日期函数SYSDATE返回当时的日期和时间。日期的算术运算：日期值可加减一个NUMBER型常数，ORACLE日期算术表达式中NUMBER型常数解释为天数(number of days)。

例2.6：

SYSDATA＋2 为后天的日期

SYSDATA－7 为一星期以前的日期

SYSDATE＋(10／1440) 为10分钟之后的时间

(5) RAW和LONG RAW数据类型

   RAW和LONG RAW数据类型表示面向字节数据(如二进制数据或字节串)，可存贮字符串、浮点数，二进制数据(如图像、数字化的声音)等。ORACLE返回的RAW值为十六进制字符值。RAW数据仅可存贮和检索，不能执行串操作。

   RAW类型等价于VARCHAR2，LONG RAW等价于LONG，其差别在于数据库字符集与会话(session)字符集之间没有转换。CHAR、VARCHAR2和LONG数据在数据库字符集和会话时用户方的字符集之间自动转换，而RAW和LONG RAW数据没有如此的转换。LONG RAW数据受到像LONG数据一样的限制。

(6)ROWID数据类型

   数据库中的每一行(row)有一个地址，通过查询伪列ROWID获得行地址，该伪列的值为十六进制字符串，该串的数据类型为ROWID类型，其字符值的形为：

BLOCK.ROW.FILE

其中：

BLOCK 包含该行的数据块的十六进制串的标识，串的长度依赖于OS。

ROW 为行在数据块中4位十六进制串的标识，在块中的第一行则为0。

FILE 为包含该行的数据库文件的十六进制的标识。第一个数据文件 为1，串的长度依赖于OS。

例2.7： 执行查询

SELECT ROWID，雇员名 FROM EMP

WHERE 部门号＝20

可返回结果：

ROWID 雇员名

0000000F．0000．0002 SMITH

0000000F．0003．0002 JONES

0000000F．0007．0002 SCOTT

0000000F．000A．0002 ADAMS

2．约束型 (Constraint Type)

可以定义下面的约束型：

_ NULL/NOT NULL

_UNIQUE

_PRIMARY KEY

_FOREIGN KEY (引用完整性Referential Integrity)

_CHECK

(1) UNIQUE 约束

   唯一(UNIQUE)约束指定一列或几列的组合作为一个唯一码。表中没有两行在唯一码上有同一值。如果唯一码基于单个列，则允许NULLS值。

表约束语法：

[CONSTRAINT <约束名>]

UNIQUE(<列名>,<列名2>,...)

列约束语法：

[CONSTRAINT constraint_name] UNIQUE

例5.8: 确保不存在两个独立的部门在一个地址:

CREATE TABLE DEPT

(部门号NUMBER,部门名 VARCHAR2(9),

地址 VARCHAR2(10),

CONSTRAINT UNQ_DEPT_LOC UNIQUE

(部门名,地址))

    在这个例子中,约束 UNQ_DEPT_LOC是一个表约束。注意逗号冠以列名的前面.数据类型为LONG或LONG RAW的列不能构成唯一码。不能将同一列或同一组列指定为唯一码而又是主码,或者一个唯一码和一个聚集码,但可指定为一个唯一码和一个外来码。UNIQUE约束引起ORACLE创建一个唯一索引去处理规则.

(2)PRIMARY KEY 约束

    主码(PRIMARY KEY)约束指定一列或组列为表的主码。如同唯一码一样，主码强制列或列组合具有唯一性，且建立唯一索引去管理它。每个表只能有一个主码。在主码列中不允许NULL值.

表约束语法:

[CONSTRAINT <约束名>]PRIMARY KEY(<列名1>,<列名2>,...)

列约束语法:

[CONSTRAINT <约束名>]PRIMARY KEY

注意: 相同的列组合不能同时用于主码(Primary key)和唯一码(unique key)两者。主码的列其数据类型不能是LONG或LONG RAW。

例2.9: 用列约束定义`部门号'为主码约束:

CREATE TABLE DEPT

(部门号 NUMBER(2) CONSTRAINT

DEPT_PRIM PRIMARY KEY,...)

(3) FOREIGN KEY 约束

   外来码(FOREIGN KEY)约束既在表中又在表之间提供引用完整性(Referential Integrity,即RI)规则, 所以也叫引用完整性约束。引用完整约束指定一列或组列作为一个外来码，并在外来码和一个指定的主码或唯一码(称为引用码)之间建立一联系。在这种联系中包含外来码的表称为子表(Child table),包含引用码的表称为双亲表(Parent table),其间要求:

   ．子表和双亲表必须是在同一数据库,不能在分布式数据库的不同结点上。但可使用数据库触发器在分布式数据库不同结束之间实施引用完整性。

   ．外来码和引用码可在同一表中,即双亲表和子表为同一表。为了满足引用完整性约束,子表的每一行要满足下列条件之一:

    (a)行的外来码值必须在一双亲表行的引用码值出现,即子表中行从属于双亲表的引用码。

    (b)组成外来码的列之一的值必须是空值。

在子表中定义的引用完整性约束中包含下列关键字:

FOREIGN KEY:标识子表中组成外来码的列或组列,仅在定义外来码的表约束时才使用。

REFERENCES:标识双亲表及组成引用码的列或组列。如果仅标识双亲表而没有列名,外来码自动地引用双亲表的主码。引用码的列数和数据类型同外来码列相同.

ON DELETE CASCADE:允许在双亲表中删除引用码值,在子表的从属行,ORACLE自动地将其删去,维护了引用完整性。如果忽略该选项,ORACLE禁止删除双亲表中的这些引用码值(当它们在子表中有从属行时)。

   在子表中定义引用完整性约束,所引用的UNIQUE或PRIMARY KEY的约束在双亲表中必须已定义。在具有AS子查询子句的CREATE TABLE语句中不能定义引用完整性约束。外来码的列不能是LONG或LNG RAW数据类型。在一个表中可定义多个外来码,一个列可能为多个外来码的成分.

表约束语法:

[CONSTRAINT <约束名>]FOREIGN KEY

(<列名1>,<列名2>,...)

REFERENCES table (<列名1>,<列名2>,...)

列约束语法:

[CONSTRAINT <约束名>]

REFERENCES table(<列名>)

注意: 关键词`FOREIGN KEY'在此语法的列约束中使用.

例 2.10： 建立EMP表,在DEPTNO列上定义为外来码并实施约束,该列引用DEPT表的主码列DEPTNO。

CREATE TABLE EMP (empno NUMBER(4),ename VARCHAR2(10),job VARCHAR2(9),mgr NUMBER(4),hiredate DATE,sal NUMBER(7,2),comm 

NUMBER(7,2),deptno NUMBER(2)

CONSTRAINT fk_deptno REFERENCES dept(deptno))

此例，约束fk_deptno 保证EMP表中全部职工是在DEPT表中的部门工作,然而允许职工有空的部门号。

(4)CHECK 约束

   CHECK约束显式地定义一条件。为了满足该约束,表的每一行必须使该条件为TRUE或未知(NULL)。此条件可以用作查询限制中的相同构造,除了下面的例外:

   ．不允许子查询。

   ．不允许引用伪例和SYSDATE、USER、UID等函数。

   ．没有完全指定的日期常数。

语法：

[CONSTRAINT 约束名]CHECK(条件)

(5) 其它约束选项

(a)DISABLE:

    添加DISABLE到一个约束条件意味着ORACLE并不强制它,仅定义约束,但不实施。约束仍然可被ORACLE工具读来构造应用程序中的规则。并且稍后可由ALTER TABLE命令去ENABLE约束。

例2.11： 用CHECK约束和DISABLE选项来建立DEP表。

CREAT TABLE EMP

(......,

ENAME VARCHAR2(10) CONSTRAINT

CHK_UPP_NAM CHECK(ENAME=UPPER(ENAME))

DISABLE,

......);

(b) EXCEPTIONS INTO tablename

确定一个存在的表,用来放置违反约束的那些行的信息。

例2.12:

CREATE TABLE EMP

(......,

ENAME VARCHAR2(10) CONSTRAINT CHK_UPP_NAM

CHECK (ENAME=UPPER(ENAME))

EXCEPTIONS INTO CON_VIOLATE,

......);

   二、修改表结构

   使用ALTER TABLE 命令可改变表的定义。

语法:

(1) ALTER TABLE <表名>

MODIFY(<列名><数据类型>[列约束][,<列名><数据类型>,...])

(2) ALTER TABLE <表名>

ADD(<列名><数据类型>[列约束]

[,<列名><数据类型>,...])

(3) ALTER TABLE <表名>

DROP[<选项>](<列名><数据类型>[列约束])

[,<列名><数据类型>,...])

1. MODIFY子句

   使用MODIFY 关键字来对表中原有属性做修改。

例2.13： 改动Sprice的定义,使之定义为8位数字长度,其中有两位小数:

ALTER TABLE,Stock

MODIFY (Sprice Number (8,2));

2. ADD子句

   使用ADD关键字来增加列和(或)约束到一个已经存在的表。

例2.14： 增加雇员配偶名列SPOUSES_NAME到EMP表:

ALTER TABLE EMP

ADD (SPOUES_NAME CHAR(10));

Table altered.

    为了看被修改后的表结构描述(如表5.1),可进键入如下命令:

DESCRIBE EMP

表2.1:

字段名 NULL? 数据类型

EMPNO NOT NULL NUMBER(4)

ENAME CHAR(25)

JOB CHAR(9)

MGR NUMBER(4)

HIREDATE DATE

SAL NUMBER(7,2)

COMM NUMBER(7,2)

DEPTNO NUMBER(2)

SPOUSES_NAME CHAR(10)

例2.15： 增加一个表约束到一个已存的表,它规定月工资必须不能超过键入$5000:

ALTER TABLE EMP

ADD (CHECK (SAL<=5000));

3.DROP 子句

使用DROP子句从表中去掉约束。

语法:

ALTER TABLE<表名字>

DROP [CONSTRAINT 约束名

PRIMARY KEY

UNIQUE (<列名1>,<列名2>,...)

例2.15:

ALTER TABLE EMP

DROP CONSTRAINT EMP_MGR;

ALTER TABLE EMP

DROP PRIMARY KEY;

4.CASCADE 选项(在DROP子句中)

如果DROP子句有CASCADE选项,将会引起任何附属的约束也被去掉。

例 2.16:

ALTER TABLE DEPT

DROP PRIMARY KEY CASCADE;

这例也将引起在EMP.DEPTNO上的外来码约束(foreign key constraint)也被去掉。

   三、索引的定义

   索引是加速表的查询的有效手段。索引就犹如书的目录,而数据库文件就相当于一本书。在翻看书时，若想找到自己所要的信息，可先按目录找到所需信息的对应页码，再从页码所指页找出所要信息。这样可大大加快查找速度，索引的作用也在于此。当一个表建立时，系统会在某分区中给它开辟一定的空间，其中包括数据和索引两部分。

   对于一个基本表，可以根据应用环境的需要建立若干索引,以提供多种存取路径。通常索引的建立和删除由DBA或表的拥有者进行。索引建好后，由系统自动地选择使用。用户不能也不必在存取数据时选择索引。

   建立索引的语句格式为：

CREATE [UNIQUE] INDEX <索引名>

ON <表名>(<列名1>[次序][,<列名2>[次序]...)

[<其它参数>]

这里任选项<其它参数>是与物理有关的参数,不是标准SQL所规定的内容。

  索引可以建在一列或几列上，圆括号内是索引列的顺序说明表,其中任选项[次序],指定了索引值排序的次序,可取ASC(升序)或DESC(降序),缺省值为升序。

UNIQUE表示索引列值必须是唯一的,即任何两个索引列值不能相同。

例2.17: 在表Customers的Address列上和表Stock的Sname列建索引:

CREATE UNIQUE INDEX Caddress

ON Customes(Address)

CREATE UNIQUE INDEX Itemname

ON Stock (Sname);

在建立以上索引后,比如要进行如下的查询:

SELECT Custname FROM Customers

WHERE Address=`Hong Kong';

   系统会首先在Address上的索引Caddress中找到所有Hong Kong记录的位置,然后在表Customers中取到实际的记录值。利用索引的查找会大大缩短存取时间。此例在建立索引时选用了UNIQUE来指明建立唯一索引,即索引值不允许重复。这在码属性上建立索引时是尤为必要的。本例建立了这样的索引后，用户若想在Address、Sname属性上输入重复值,即会导致错误信息,由此保证了这些属性取值的唯一性。索引虽能加快表上查找速度,但相应降低了修改的速度。因为在建有索引的表上对索引属性做更新操作时，相应地会带来系统对索引的维护，从而降低其执行速度。因此在修改频繁的属性上不易建立索引。同样对那些很少在其上做查询的属性也不易建立索引。　

   四、删除表、索引

   Drop命令可把用户的表、索引从数据库中删掉，相应地这些表和索引在数据字典中的记载信息也将消失。

例2.18: 把表Customer从系统中删除

Drop TABLE Customes;

   此例同时将建立在表上的索引和视图(见本章5.5节)也一起删除掉。此时视图只是不能再被查询,但视图定义还是存在的。这样在Customer重建后,原有视图即能恢复使用。

例2.19: 删除索引的例子:

Drop INDEX Itemname;

索引 Itemname即被删除。