《Oracle数据库管理应用表和视图》由会员分享，可在线阅读，更多相关《Oracle数据库管理应用表和视图（121页珍藏版）》请在金锄头文库上搜索。
1、第第4章章 表和视图表和视图第第4章章 表和视图表和视图4.1 表的创建和操作表的创建和操作4.2 数据完整性和约束条件数据完整性和约束条件4.3 修改表结构修改表结构4.4 分区表简介分区表简介4.5 视图创建和操作视图创建和操作4.6 阶段训练阶段训练4.7 练练 习习第第4章章 表和视图表和视图4.1 表的创建和操作表的创建和操作表由记录(行row)和字段(列column)构成，是数据库中存储数据的结构。要进行数据的存储和管理，首先要在数据库中创建表，即表的字段(列)结构。有了正确的结构，就可以用数据操作命令，插入、删除表中记录或对记录进行修改。比如，要进行图书管理，就需要创建图书和出版
2、社等表，这里给出用于示范和训练的图书和出版社表的结构和内容，如表4-1、表4-2所示。第第4章章 表和视图表和视图表4-1图书表第第4章章 表和视图表和视图表4-2出版社表第第4章章 表和视图表和视图4.1.1表的创建1创建表的语法表的创建需要CREATETABLE系统权限，表的基本创建语法如下：CREATETABLE表名(列名数据类型(宽度)DEFAULT 表达式COLUMNCONSTRAINT,.TABLECONSTRAINTTABLE_PARTITION_CLAUSE)；第第4章章 表和视图表和视图由此可见，创建表最主要的是要说明表名、列名、列的数据类型和宽度，多列之间用“，”分隔。可以
3、是用中文或英文作为表名和列名。表名最大长度为30个字符。在同一个用户下，表不能重名，但不同用户表的名称可以相重。另外，表的名称不能使用Oracle的保留字。在一张表中最多可以包含2000列。该语法中的其他部分根据需要添加，作用如下：DEFAULT表达式：用来定义列的默认值。COLUMNCONSTRAINT：用来定义列级的约束条件。TABLECONSTRAINT：用来定义表级的约束条件。TABLE_PARTITION_CLAUSE：定义表的分区子句。第第4章章 表和视图表和视图【训练1】创建图书和出版社表。步骤1：创建出版社表，输入并执行以下命令：CREATETABLE出版社(编号VARCHAR
4、2(2),出版社名称VARCHAR2(30),地址VARCHAR2(30),联系电话VARCHAR2(20);执行结果：表已创建。第第4章章 表和视图表和视图步骤2：创建图书表，输入并执行以下命令：CREATETABLE图书(图书编号VARCHAR2(5),图书名称VARCHAR2(30),出版社编号VARCHAR2(2),作者VARCHAR2(10),出版日期DATE,数量NUMBER(3),单价NUMBER(7,2);执行结果：表已创建。第第4章章 表和视图表和视图步骤3：使用DESCRIBE显示图书表的结构，输入并执行以下命令：DESCRIBE图书第第4章章 表和视图表和视图说明：在以上
5、训练中，列名和数据类型之间用空格分隔，数据类型后的括号中为宽度(日期类型除外)。对于有小数的数字型，前一个参数为总宽度，后一个参数为小数位。用逗号分隔各列定义，但最后一列定义后不要加逗号。2通过子查询创建表如果要创建一个同已有的表结构相同或部分相同的表，可以采用以下的语法：CREATETABLE表名(列名.)ASSQL查询语句;该语法既可以复制表的结构，也可以复制表的内容，并可以为新表命名新的列名。新的列名在表名后的括号中给出，如果省略将采用原来表的列名。复制的内容由查询语句的WHERE条件决定。第第4章章 表和视图表和视图【训练2】通过子查询创建新的图书表。步骤1：完全复制图书表到“图书1”
6、，输入并执行以下命令：CREATETABLE图书1ASSELECT*FROM图书;执行结果：表已创建。步骤2：创建新的图书表“图书2”，只包含书名和单价，输入并执行以下命令：CREATETABLE图书2(书名,单价)ASSELECT图书名称,单价FROM图书;执行结果：表已创建。第第4章章 表和视图表和视图步骤3：创建新的图书表“图书3”，只包含书名和单价，不复制内容，输入并执行以下命令：CREATETABLE图书3(书名,单价)ASSELECT图书名称,单价FROM图书WHERE1=2;执行结果：表已创建。说明：“图书1”表的内容和结构同“图书”表完全一致，相当于表的复制。第第4章章 表和视
7、图表和视图“图书2”表只包含“图书”表的两列“图书名称”和“单价”，并且对字段重新进行了命名，“图书2”表的“书名”对应“图书”表的“图书名称”，“图书2”表的“单价”对应“图书”表的“单价”。“图书3”表同“图书2”表的结构一样，但表的内容为空。因为WHERE条件始终为假，没有满足条件的记录，所以没有复制表的内容。3设置列的默认值可以在创建表的同时指定列的默认值，这样在插入数据时，如果不插入相应的列，则该列取默认值，默认值由DEFAULT部分说明。第第4章章 表和视图表和视图【训练3】创建表时设置默认值。步骤1：创建表时，设置表的默认值。CREATETABLE图书4(图书编号VARCHAR2
8、(5)DEFAULTNULL,图书名称VARCHAR2(30)DEFAULT未知,出版社编号VARCHAR2(2)DEFAULTNULL,出版日期DATEDEFAULT01-1月-1900,作者VARCHAR2(10)DEFAULTNULL,数量NUMBER(3)DEFAULT0,单价NUMBER(7,2)DEFAULTNULL,借出数量NUMBER(3)DEFAULT0);执行结果：表已创建。第第4章章 表和视图表和视图步骤2：插入数据。INSERTINTO图书4(图书编号)VALUES(A0001);执行结果：已创建1行。步骤2：查询插入结果。SELECT*FROM图书4;第第4章章 表和
9、视图表和视图说明：本训练中，只插入图书编号，其他部分取的是默认值。图书名称默认为“未知”，出版日期默认为1900年1月1日，数量默认为0，出版社编号、作者和单价的默认值为NULL。第第4章章 表和视图表和视图【练习1】创建图书出借信息表，设置适当的默认值，并插入数据。结构如下：名称是否为空?类型-图书编号VARCHAR2(10)借书人VARCHAR2(10)借书日期DATE归还日期DATE第第4章章 表和视图表和视图4删除已创建的表删除表的语法如下：DROPTABLE表名CASCADECONSTRAINTS；表的删除者必须是表的创建者或具有DROPANYTABLE权限。CASCADECONST
10、RAINTS表示当要删除的表被其他表参照时，删除参照此表的约束条件。有关内容请参考下一节。第第4章章 表和视图表和视图【训练4】删除“图书1”表。DROPTABLE图书1;执行结果：表已丢弃。【练习2】删除“图书2”、“图书3”和“图书4”表。第第4章章 表和视图表和视图4.1.2表的操作1表的重命名语法如下：RENAME旧表名TO新表名;只有表的拥有者，才能修改表名。【训练1】修改“图书”表为“图书5”表：RENAME图书TO图书5;执行结果：表已重命名。第第4章章 表和视图表和视图2清空表清空表的语法为：TRUNCATETABLE表名；清空表可删除表的全部数据并释放占用的存储空间。有关训练
11、请参照DELETE语句部分，注意两者的区别。3添加注释(1)为表添加注释的语法为：COMMENTONTABLE表名IS.;该语法为表添加注释字符串。如IS后的字符串为空，则清除表注释。第第4章章 表和视图表和视图【训练2】为emp表添加注释：“公司雇员列表”。COMMENTONTABLEempIS公司雇员列表;执行结果：注释已创建。(2)为列添加注释的语法为：COMMENTONCOLUMN表名.列名IS.该语法为列添加注释字符串。如IS后的字符串为空，则清除列注释。第第4章章 表和视图表和视图【训练3】为emp表的deptno列添加注释：“部门编号”。COMMENTONCOLUMNemp.de
12、ptnoIS部门编号;执行结果：注释已创建。【练习1】清除emp表的注释。4.1.3查看表使用以下语法可查看表的结构：DESCRIBE表名；DESCRIBE可以简写为DESC。可以通过对数据字典USER_OBJECTS的查询，显示当前模式用户的所有表。第第4章章 表和视图表和视图【训练1】显示当前用户的所有表。SELECTobject_nameFROMuser_objectsWHEREobject_type=TABLE;执行结果：OBJECT_NAME-BONUSDEPTEMPSALGRADE出版社图书第第4章章 表和视图表和视图4.2 数据完整性和约束条件数据完整性和约束条件4.2.1数据完
13、整性约束表的数据有一定的取值范围和联系，多表之间的数据有时也有一定的参照关系。在创建表和修改表时，可通过定义约束条件来保证数据的完整性和一致性。约束条件是一些规则，在对数据进行插入、删除和修改时要对这些规则进行验证，从而起到约束作用。第第4章章 表和视图表和视图完整性包括数据完整性和参照完整性，数据完整性定义表数据的约束条件，参照完整性定义数据之间的约束条件。数据完整性由主键(PRIMARYKEY)、非空(NOT NULL)、惟一(UNIQUE)和检查(CHECK)约束条件定义，参照完整性由外键(FOREIGNKEY)约束条件定义。第第4章章 表和视图表和视图4.2.1数据完整性约束表的数据有
14、一定的取值范围和联系，多表之间的数据有时也有一定的参照关系。在创建表和修改表时，可通过定义约束条件来保证数据的完整性和一致性。约束条件是一些规则，在对数据进行插入、删除和修改时要对这些规则进行验证，从而起到约束作用。完整性包括数据完整性和参照完整性，数据完整性定义表数据的约束条件，参照完整性定义数据之间的约束条件。数据完整性由主键(PRIMARYKEY)、非空(NOTNULL)、惟一(UNIQUE)和检查(CHECK)约束条件定义，参照完整性由外键(FOREIGNKEY)约束条件定义。第第4章章 表和视图表和视图4.2.2表的五种约束表共有五种约束，它们是主键、非空、惟一、检查和外键。1主键(
15、PRIMARYKEY)主键是表的主要完整性约束条件，主键惟一地标识表的每一行。一般情况下表都要定义主键，而且一个表只能定义一个主键。主键可以包含表的一列或多列，如果包含表的多列，则需要在表级定义。主键包含了主键每一列的非空约束和主键所有列的惟一约束。主键一旦成功定义，系统将自动生成一个B*树惟一索引，用于快速访问主键列。比如图书表中用“图书编号”列作主键，“图书编号”可以惟一地标识图书表的每一行。第第4章章 表和视图表和视图主键约束的语法如下：CONSTRANT约束名PRIMARYKEY-列级CONSTRANT约束名PRIMARYKEY(列名1,列名2,.)-表级2非空(NOTNULL)非空约
16、束指定某列不能为空，它只能在列级定义。在默认情况下，Oracle允许列的内容为空值。比如“图书名称”列要求必须填写，可以为该列设置非空约束条件。非空约束语法如下：CONSTRANT约束名NOTNULL-列级第第4章章 表和视图表和视图约束分为两级，一个约束条件根据具体情况，可以在列级或表级定义。列级约束：约束表的某一列，出现在表的某列定义之后，约束条件只对该列起作用。表级约束：约束表的一列或多列，如果涉及到多列，则必须在表级定义。表级约束出现在所有列定义之后。第第4章章 表和视图表和视图4.2.2表的五种约束表共有五种约束，它们是主键、非空、惟一、检查和外键。1主键(PRIMARYKEY)主键
17、是表的主要完整性约束条件，主键惟一地标识表的每一行。一般情况下表都要定义主键，而且一个表只能定义一个主键。主键可以包含表的一列或多列，如果包含表的多列，则需要在表级定义。主键包含了主键每一列的非空约束和主键所有列的惟一约束。主键一旦成功定义，系统将自动生成一个B*树惟一索引，用于快速访问主键列。比如图书表中用“图书编号”列作主键，“图书编号”可以惟一地标识图书表的每一行。第第4章章 表和视图表和视图主键约束的语法如下：CONSTRANT 约束名PRIMARYKEY-列级CONSTRANT约束名PRIMARYKEY(列名1,列名2,.)-表级第第4章章 表和视图表和视图2非空(NOTNULL)非
18、空约束指定某列不能为空，它只能在列级定义。在默认情况下，Oracle允许列的内容为空值。比如“图书名称”列要求必须填写，可以为该列设置非空约束条件。非空约束语法如下：CONSTRANT 约束名 NOTNULL-列级3惟一(UNIQUE)惟一约束条件要求表的一列或多列的组合内容必须惟一，即不相重，可以在列级或表级定义。但如果惟一约束包含表的多列，则必须在表级定义。比如出版社表的“联系电话”不应该重复，可以为其定义惟一约束。第第4章章 表和视图表和视图惟一约束的语法如下：CONSTRANT 约束名UNIQUE-列级CONSTRANT约束名UNIQUE(列名1,列名2,.)-表级第第4章章 表和视图
19、表和视图4检查(CHECK)检查约束条件是用来定义表的一列或多列的一个约束条件，使表的每一列的内容必须满足该条件(列的内 容 为 空 除 外 )。 在 CHECK条 件 中 ， 可 以 调 用SYSDATE、USER等系统函数。一个列上可以定义多个CHECK约束条件，一个CHECK约束可以包含一列或多列。如果CHECK约束包含表的多列，则必须在表级定义。比如图书表的“单价”的值必须大于零，就可以设置成CHECK约束条件。第第4章章 表和视图表和视图检查约束的语法如下：CONSTRANT约束名CHECK(约束条件)-列级，约束条件中只包含本列CONSTRANT约束名CHECK(约束条件)-表级，
20、约束条件中包含多列5外键(FOREIGNKEY)指定表的一列或多列的组合作为外键，外键参照指定的主键或惟一键。外键的值可以为NULL，如果不为NULL，就必须是指定主键或惟一键的值之一。外键通常用来约束两个表之间的数据关系，这两个表含有主键或惟一键的称为主表，定义外键的那张表称为子表。如果外键只包含一列，则可以在列级定义；如果包含多列，则必须在表级定义。第第4章章 表和视图表和视图外键的列的个数、列的数据类型和长度，应该和参照的主键或惟一键一致。比如图书表的“出版社编号”列，可以定义成外键，参照出版社表的“编号”列，但“编号”列必须先定义成为主键或惟一键。如果外键定义成功，则出版社表称为主表，
21、图书表称为子表。在表的创建过程中，应该先创建主表，后创建子表。第第4章章 表和视图表和视图外键约束的语法如下：第一种语法，如果子记录存在，则不允许删除主记录：CONSTRANT 约束名 FOREIGN KEY(列名1,列名2,.)REFERENCES表名(列名1,列名2,.)第二种语法，如果子记录存在，则删除主记录时，级联删除子记录：CONSTRANT 约束名 FOREIGN KEY(列名1,列名2,.)REFERENCES表名(列名1,列名2,.)ondeletecascade第第4章章 表和视图表和视图第三种语法，如果子记录存在，则删除主记录时，将子记录置成空：CONSTRANT约束名FO
22、REIGNKEY(列名1,列名2,.)REFERENCES表名(列名1,列名2,.)ondeletesetnull其中的表名为要参照的表名。在以上5种约束的语法中，CONSTRANT关键字用来定义约束名，如果省略，则系统自动生成以SYS_开头的惟一约束名。约束名的作用是当发生违反约束条件的操作时，系统会显示违反的约束条件名称，这样用户就可以了解到发生错误的原因。第第4章章 表和视图表和视图4.2.3约束条件的创建在表的创建语法中可以定义约束条件：CREATETABLE表名(列名数据类型DEFAULT表达式COLUMNCONSTRAINT,.TABLECONSTRAINT)；其中，COLUMNC
23、ONSTRAINT用来定义列级约束条件；TABLECONSTRAINT用来定义表级约束条件。第第4章章 表和视图表和视图【训练1】创建带有约束条件的出版社表(如果已经存在，先删除)：CREATETABLE出版社(编号 VARCHAR2(2) CONSTRAINT PK_1 PRIMARYKEY,出版社名称VARCHAR2(30)NOTNULL,地址VARCHAR2(30)DEFAULT未知,联系电话VARCHAR2(20);执行结果：表已创建。说明：出版社表的主键列是“编号”列，主键名为PK_1。“出版社名称”必须填写，地址的默认值为“未知”。第第4章章 表和视图表和视图【训练2】创建带有约束
24、条件(包括外键)的图书表(如果已经存在，先删除)：CREATETABLE图书(图书编号 VARCHAR2(5)CONSTRAINTPK_2PRIMARYKEY,图书名称VARCHAR2(30)NOTNULL,出版社编号VARCHAR2(2)CHECK(LENGTH(出版社编号)=2)NOTNULL,作者VARCHAR2(10)DEFAULT未知,出版日期DATEDEFAULT01-1月-1900,第第4章章 表和视图表和视图数量NUMBER(3)DEFAULT1CHECK(数量0),单价NUMBER(7,2),CONSTRAINTYS_1UNIQUE(图书名称,作者),CONSTRAINTFK
25、_1FOREIGNKEY(出版社编号)REFERENCES出版社(编号)ONDELETECASCADE);执行结果：表已创建。第第4章章 表和视图表和视图说明：因为两个表同属于一个用户，故约束名不能相重，图书表的主键为“图书编号”列，主键名为PK_2。其中，约束条件CHECK(LENGTH(出版社编号)=2)表示出版社编号的长度必须是2，约束条件UNIQUE(图书名称,作者)表示“图书名称”和“作者”两列的内容组合必须惟一。FOREIGNKEY(出版社编号)REFERENCES出版社(编号)表示图书表的“出版社编号”列参照出版社的“编号”主键列。出版社表为主表，图书表为子表，出版社表必须先创建
26、。ONDELETECASCADE表示当删除出版社表的记录时，图书表中的相关记录同时删除，比如删除清华大学出版社，则图书表中清华大学出版社的图书也会被删除。如果同时出现DEFAULT和CHECK，则DEFAULT需要出现在CHECK约束条件之前。第第4章章 表和视图表和视图【训练3】插入数据，验证约束条件。步骤1：插入出版社信息：INSERTINTO出版社VALUES(01,清华大学出版社,北京,010-83456272);执行结果：已创建1行。继续插入INSERTINTO出版社VALUES(01,电子科技大学出版社,西安,029-88201467);执行结果：ERROR位于第1行:ORA-00
27、001:违反惟一约束条件(SCOTT.PK_1)第第4章章 表和视图表和视图第二个插入语句违反约束条件PK_1，即出版社表的主键约束，原因是主键的值必须是惟一的。修改第二个语句的编号为“02”，重新执行：INSERTINTO出版社VALUES(02,电子科技大学出版社,西安,029-88201467);执行结果：已创建1行。步骤2：插入图书信息：INSERTINTO图书(图书编号,图书名称,出版社编号,作者 ,单 价 ) VALUES(A0001,计 算 机 原 理 ,01,刘 勇,25.30);执行结果：已创建1行。第第4章章 表和视图表和视图继续插入：INSERTINTO图书(图书编号，图
28、书名称，出版社编号，作者，单价)VALUES(A0002,C语言程序设计,03,马丽,18.75);执行结果：ERROR位于第1行:ORA-02291:违反完整约束条件(SCOTT.FK_1)-未找到父项关键字第二个插入语句违反外键约束关系FK_1，因为在出版社表中，被参照的主键列中没有“03”这个出版社，所以产生未找到父项关键字的错误，修改后重新插入：第第4章章 表和视图表和视图INSERTINTO图书(图书编号，图书名称，出版社编号，作者，单价)VALUES(A0002,C语言程序设计,02,马丽,18.75);执行结果：已创建1行。继续插入：INSERTINTO图书(图书编号,图书名称,
29、出版社编号,作者,数量,单价)VALUES(A0003,汇编语言程序设计,02,黄海明,0,20.18);第第4章章 表和视图表和视图执行结果：ERROR位于第1行:ORA-02290:违反检查约束条件(SCOTT.SYS_C003114)插入的数量为0，违反约束条件CHECK(数量0)。该约束条件没有命名，所以约束名SYS_C003114为系统自动生成。修改后重新执行：INSERTINTO图书(图书编号,图书名称,出版社编号,作者,数量,单价)VALUES(A0003,汇编语言程序设计,02,黄海明,15,20.18);第第4章章 表和视图表和视图执行结果：已创建1行。步骤3：显示插入结果：
30、SELECT*FROM出版社;第第4章章 表和视图表和视图继续查询：SELECT*FROM图书;第第4章章 表和视图表和视图步骤4：提交插入的数据：COMMIT;执行结果：提交完成。说明：在图书表中，没有插入的数量取默认值1，没有插入的出版日期取默认值01-1月-00(即1900年1月1日)。第第4章章 表和视图表和视图【训练4】 通过删除数据验证ON DELETECASCADE的作用。步骤1：删除出版社01(清华大学)：DELETEFROM出版社WHERE编号=01;执行结果：已删除1行。第第4章章 表和视图表和视图步骤2：显示删除结果：显示出版社表结果：SELECT*FROM出版社;执行结
31、果：显示图书表结果：SELECT*FROM图书;第第4章章 表和视图表和视图步骤3：恢复删除:ROLLBACK；回退已完成。说 明 ： 参 见 训 练 2， 外 键 约 束 FK_1带 有 ONDELETECASCAD选项，删除清华大学出版社时，对应的图书也自动删除。其他两种情况用户可自行验证。【练习1】创建学生、系部表，添加必要主键、外键等约束条件。第第4章章 表和视图表和视图4.2.4查看约束条件数据字典USER_CONSTRAINTS中包含了当前模式用户的约束条件信息。其中，CONSTRAINTS_TYPE显示的约束类型为：C：CHECK约束。P：PRIMARYKEY约束。U：UNIQU
32、E约束。R：FOREIGNKEY约束。其他信息可根据需要进行查询显示，可用DESCRIBE命令查看USER_CONSTRAINTS的结构。第第4章章 表和视图表和视图【训练1】检查表的约束信息：SELECTCONSTRAINT_NAME,CONSTRAINT_TYPE,SEARCH_CONDITIONFROMUSER_CONSTRAINTSWHERETABLE_NAME=图书;第第4章章 表和视图表和视图说明：图书表共有个约束条件，一个PRIMARYKEY(P)约束PK_2，一个FOREIGNKEY(R)约束FK_1，一个 UNIQUE(R)约 束 YS_1和 4个 CHECK(C)约 束SY
33、S_C003111、SYS_C003112、SYS_C003113和SYS_C003114，4个CHECK约束的名字是由系统命名的。第第4章章 表和视图表和视图4.2.5使约束生效和失效使约束生效和失效约束的作用是保护数据完整性，但有的时候约束的条件可能不再适用或没有必要，如果这个约束条件依然发生作用就会影响操作的效率，比如导出和导入数据时要暂时关闭约束条件，这时可以使用下面的命令关闭或打开约束条件。使约束条件失效：使约束条件失效：ALTER TABLE 表表名名 DISABLE CONSTRANT 约约束束名名;使约束条件生效：使约束条件生效：ALTER TABLE 表表名名 ENABLE
34、CONSTRANT 约约束束名名;第第4章章 表和视图表和视图【训练1】使图书表的数量检查失效。步骤1：使约束条件SYS_C003114(数量0)失效：ALTER TABLE 图 书 DISABLE CONSTRAINTSYS_C003114;执行结果：表已更改。步骤2：修改数量为0：UPDATE图书SET数量=0WHERE图书编号=A0001;执行结果：已更新1行。第第4章章 表和视图表和视图步骤3：使约束条件SYS_C003114生效：ALTER TABLE 图 书 ENABLE CONSTRAINTSYS_C003114;执行结果：ERROR位于第1行:ORA-02293:无法验证(SC
35、OTT.SYS_C003114)-违反检查约束条件继续执行：UPDATE图书SET数量=5WHERE图书编号=A0001;执行结果：已更新1行。第第4章章 表和视图表和视图继续执行：ALTER TABLE 图 书 ENABLE CONSTRAINTSYS_C003114;执行结果：表已更改。说明：在步骤1中，先使名称为SYS_C003114(数量0)的检查条件暂时失效，所以步骤2修改第1条记录的数量为0才能成功。步骤3使该约束条件重新生效，但因为表中有数据不满足该约束条件，所以发生错误，通过修改第一条记录的数量为5，使约束条件重新生效。第第4章章 表和视图表和视图4.3 修改表结构修改表结构4
36、.3.1增加新列增加新列的语法如下：ALTERTABLE表名ADD 列列 名名 数数 据据 类类 型型 DEFAULT 表表 达达 式式 COLUMN CONSTRAINT;如果要为表同时增加多列，可以按以下格式进行：ALTERTABLE表名ADD (列 名 数 据 类 型 DEFAULT 表 达 式 COLUMNCONSTRAINT.);第第4章章 表和视图表和视图通过增加新列可以指定新列的数据类型、宽度、默认值和约束条件。增增加加的的新新列列总总是是位位于于表表的的最最后后。假如新列定义了默认值，则新列的所有行自动填充默认值。对于有数据的表，新增加列的值为NULL。【训练1】为“出版社”增
37、加一列“电子邮件”：ALTERTABLE出版社ADD电子邮件VARCHAR2(30);显示结果：表已更改。第第4章章 表和视图表和视图说明：为出版社新增加了一列“电子邮件”，数据类型为VARCHAR2，宽度为30。可用DESCRIBE命令查看表的新结构。第第4章章 表和视图表和视图4.3.2修改列修改列的语法如下：ALTERTABLE表名MODIFY列名数据类型DEFAULT表达式COLUMNCONSTRAINT如果要对表同时修改多列，可以按以下格式进行：ALTERTABLE表名MODIFY(列名数据类型DEFAULT表达式COLUMNCONSTRAINT.);其中，列列名名是是要要修修改改的
38、的列列的的标标识识，不不能能修修改改。如果要改变列名，只能先删除该列，然后重新增加。其他部分都可以进行修改，如果没有给出新的定义，表示该部分属性不变。第第4章章 表和视图表和视图修改列定义还有以下一些特点：(1)列的宽度可以增加或减小，只有在表的列没有数据或数据为NULL时才能减小宽度。(2)在表的列没有数据或数据为NULL时才能改变数据类型，CHAR和VARCHAR2之间可以随意转换。(3)只有当列的值非空时，才能增加约束条件NOTNULL。(4)修改列的默认值，只影响以后插入的数据。修改列的默认值，只影响以后插入的数据。第第4章章 表和视图表和视图【训练1】修改“出版社”表的“电子邮件”列
39、的宽度，从30变为40。ALTERTABLE出版社MODIFY电子邮件VARCHAR2(40);执行结果：表已更改。说明：将“电子邮件”列的宽度由原来的30修改为40，约束条件保持不变。可用DESCRIBE命令查看新结构。第第4章章 表和视图表和视图4.3.3删除列删除列的语法如下：ALTERTABLE表名DROP COLUMN列名CASCADE CONSTRAINTS；如果要同时删除多列，可以按以下格式进行：ALTERTABLE表名DROPCOLUMN列名.)CASCADECONSTRAINTS;当当删删除除列列时时，列列上上的的索索引引和和约约束束条条件件同同时时被被删删除除。但 如如 果
40、果 列列 是是 多多 列列 约约 束束 的的 一一 部部 分分 ， 则则 必必 须须 指指 定定CASCADE CONSTRAINTS才能删除约束条件。才能删除约束条件。第第4章章 表和视图表和视图【训练1】删除“出版社”表的“电子邮件”列。ALTERTABLE出版社DROPCOLUMN电子邮件;执行结果：表已更改。说明：此训练将“电子邮件”列删除。可用DESCRIBE命令查看新结构。第第4章章 表和视图表和视图使用以下语法，可以将列置成UNUSED状态，这样就不会在表中显示出该列：ALTERTABLE表名SET UNUSED COLUMN列名CASCADE CONSTRAINTS;以后可以重
41、新使用或删除该列。通过数据字典可以查看标志成UNUSED的列。删除删除UNUSED列：列：ALTERTABLE表 名 DROP UNUSED COLUMNS;第第4章章 表和视图表和视图【训练2】 将“图书”表的“出版日期”列置成UNUSED，并查看。步骤1：设置“出版日期”列为UNUSED：ALTERTABLE图书SET UNUSED COLUMN 出出版版日日期期;步骤2：显示结构：DESC图书;执行结果：名称是否为空?类型-第第4章章 表和视图表和视图图书编号NOTNULLVARCHAR2(5)图书名称NOTNULLVARCHAR2(30)出版社编号NOTNULLVARCHAR2(2)作
42、者VARCHAR2(10)数量NUMBER(3)单价NUMBER(7,2)步骤3：删除UNUSED列：ALTERTABLE图书DROPUNUSEDCOLUMNS;第第4章章 表和视图表和视图4.3.4约束的修改可以为表增加或删除表级约束可以为表增加或删除表级约束。1增加约束增加约束条件的语法如下：ALTERTABLE表名ADDCONSTRAINT约束名表级约束条件;【训练1】为emp表的mgr列增加外键约束：ALTERTABLEempADDCONSTRAINTFK_3FOREIGNKEY(mgr)REFERENCESemp(empno);执行结果：表已更改。说说明明：本本训训练练增增加加的的外
43、外键键为为参参照照自自身身的的外外键键，含含义义是是mgr(经理编号经理编号)列的内容必须是列的内容必须是empno(雇员编号雇员编号)之一。之一。第第4章章 表和视图表和视图2删除约束删除约束的语法如下：ALTERTABLE表名DROPPRIMARY_KEY|UNIQUE(列名) |CONSTRAINT约束名CASCADE;【训练2】删除为emp表的mgr列增加的外键：ALTERTABLEempDROPCONSTRAINTFK_3;执行结果：表已更改。表已更改。第第4章章 表和视图表和视图4.4 分区表简介分区表简介4.4.1分区的作用在某些场合会使用非常大的表，比如人口信息统计表。如果一个
44、表很大，就会降低查询的速度，并增加管理的难度。一旦发生磁盘损坏，可能整个表的数据就会丢失，恢复比较困难。根据这一情况，可以创建分区表，把把一一个个大大表表分分成成几几个个区区(小小段段)，对数据的操作和管理都可以针对分区进行，这样就可以提高数据库的运行效率。分区可以存在于不同的表空间上，提高了数据的可用性。第第4章章 表和视图表和视图分区的依据可以是一列或多列的值，这一列或多列称为分分区关键字区关键字或分区列分区列。所有分区的逻辑属性是一样的(列名、数据类型、约束条件等)，但每个分区可以有自己的物理属性(表空间、存储参数等)。分区有三种：范围分区、哈斯分区和混合分区分区有三种：范围分区、哈斯分
45、区和混合分区。范范围围分分区区(RANGEPARTITIONING)：根据分区关键字值的范围建立分区。比如，根据省份为人口数据表建立分区。哈哈斯斯分分区区(HASHPARTITIONING)：在分区列上使用HASH算法进行分区。混混合合分分区区(COMPOSITEPARTITIONING)：混合以上两种方法，使用范围分区建立主分区，使用使用范围分区建立主分区，使用HASH算法建立子分区。算法建立子分区。第第4章章 表和视图表和视图4.4.2分区的实例由于分区用到了很多存储参数，故不在这里进行详细讨论，只给出一个范围分区的简单训练实例一个范围分区的简单训练实例。【训练1】创建和使用分区表。步骤1
46、：创建按成绩分区的考生表，共分为3个区：CREATETABLE考生(考号VARCHAR2(5),姓名VARCHAR2(30),成绩NUMBER(3)第第4章章 表和视图表和视图PARTITIONBYRANGE(成绩成绩)(PARTITIONAVALUESLESSTHAN(300)TABLESPACEUSERS,PARTITIONBVALUESLESSTHAN(500)TABLESPACEUSERS,PARTITIONCVALUESLESSTHAN(MAXVALUE)TABLESPACEUSERS);第第4章章 表和视图表和视图步骤2：插入不同成绩的若干考生：INSERTINTO考生VALUES
47、(10001,王明,280);INSERTINTO考生VALUES(10002,李亮,730);INSERTINTO考生VALUES(10003,赵成,550);INSERTINTO考生VALUES(10004,黄凯,490);INSERTINTO考生VALUES(10005,马新,360);INSERTINTO考生VALUES(10006,杨丽,670);共6条记录，它它们们在在被被插插入入到到表表的的时时候候，自自动动被被存存储储到到相相应的分区内。应的分区内。第第4章章 表和视图表和视图步骤3：检查A区中的考生：SELECT*FROM考生PARTITION(A) ;执行结果：考号姓名成绩
48、-10001王明280步骤4：检查全部的考生：SELECT*FROM考生;执行结果：考号姓名成绩-第第4章章 表和视图表和视图10001王明28010004黄凯49010005马新36010002李亮73010003赵成55010006杨丽670说明：共创建A、B、C三个区，A区的分数范围为300分以下，B区的分数范围为300至500分，C区的分数范围为500分以上。共插入6名考生，插入时根据考生分数将自动插入不同的区。第第4章章 表和视图表和视图4.5 视图创建和操作视图创建和操作4.5.1视图的概念视图是基于一张表或多张表或另外一个视图的逻辑表。视图不同于表，视视图图本本身身不不包包含含任
49、任何何数数据据，只只是是用用来来反反映映数数据据。表是用于存储数据的基本结构。而视视图图只只是是一一种种定定义义，对对应应一一个个查查询询语语句句。视图的数据都来自于某些表，这些表被称为基表（基基表表是是用用create table语语句句创创建建的的表表）。通过视图来查看表，就像是从不同的角度来观察一个(或多个)表。视图有如下一些优点：*可以提高数据访问的安全性，通过视图往往只可以访问数据库中表的特定部分，限制了用户访问表的全部行和列。第第4章章 表和视图表和视图*简化了对数据的查询，隐藏了查询的复杂性。视图的数据来自一个复杂的查询，用户对视图的检索却很简单。*一个视图可以检索多张表的数据，
50、因此用户通过访问一个视图，可完成对多个表的访问。*视图是相同数据的不同表示，通过为不同的用户创建同一个表的不同视图，使用户可分别访问同一个表的不同部分。视图可以在表能够使用的任何地方使用，但在对视图的操作上同表相比有些限制，特别是插入和修改操作。对视图的操作将传递到基表，所以在表上定义的约束条件和触发器在视图上将同样起作用。第第4章章 表和视图表和视图4.5.2视图的创建创建视图需要CREAE VIEW系统权限，视图的创建语法如下：CREATE OR REPLACE FORCE|NOFORCE VIEW 视视图图名名(别别名名1，别名，别名2.)AS 子查询子查询 WITH CHECK OPT
51、ION CONSTRAINT 约束名约束名WITH READ ONLY其中：ORREPLACE表示替代已经存在的视图。FORCE表示不管基表是否存在，创建视图表示不管基表是否存在，创建视图。NOFORCE表示只有基表存在时，才创建视图，是默认值。表示只有基表存在时，才创建视图，是默认值。第第4章章 表和视图表和视图别别名名是为子查询中选中的列新定义的名字，替代原来的列名。子查询是一个用于定义视图的SELECT查询语句，可可以以包含连接、分组及子查询。包含连接、分组及子查询。WITH CHECK OPTION表表示示进进行行视视图图插插入入或或修修改改时时必必须须满满足足子子查查询询的的约约束束
52、条条件件。后面的约束名是该约束条件的名字。WITH READ ONLY 表示视图是只读的。表示视图是只读的。删除视图的语法如下：DROP VIEW 视图名视图名;删除视图者需要是视图的建立者或者拥有DROPANYVIEW权限。视图的删除不影响基表，不会丢失数据。视图的删除不影响基表，不会丢失数据。第第4章章 表和视图表和视图1创建简单视图【训练1】创建图书作者图书作者视图。步骤1：创建图书作者视图：CREATEVIEW图书作者图书作者(书名,作者)ASSELECT图书名称图书名称,作者作者FROM图书;步骤2：查询视图全部内容SELECT*FROM图书作者图书作者;查询视图的实质查询视图的实质
53、是查询创建该视图时的select语句的结果集。第第4章章 表和视图表和视图步骤3：查询部分视图：SELECT作者FROM图书作者;第第4章章 表和视图表和视图说明：本训练创建的视图名称为“图书作者”，视图只包含两列，为“书名”和“作者”，对应图书表的“图书名称”和“作者”两列。如果省略了视图名称后面的列名，则视图会采用和表一样的列名。对视图查询和对表查询一样，但通过视图最多只能看到表的两列，可见视图隐藏了表的部分内容。第第4章章 表和视图表和视图【训练2】创建清华大学出版社的图书视图。步骤1：创建清华大学出版社（编号是01）的图书视图：CREATEVIEW清华图书AS SELECT 图书名称,
54、作者,单价 FROM 图书WHERE出版社编号=01;步骤2：查询清华图书视图：SELECT*FROM清华图书;第第4章章 表和视图表和视图步骤3：删除视图：DROPVIEW清华图书;说明：该视图包含了对记录的约束条件。【练习1】创建部门30的雇员名称和职务的视图，并查询。【练习2】创建职务为“MANAGER”的雇员名称和工资的视图，并查询。第第4章章 表和视图表和视图2创建复杂视图【训练3】修改作者视图，加入出版社名称。步骤1：重建图书作者视图：CREATE OR REPLACEVIEW图书作者(书名,作者,出版社)ASSELECT图书名称,作者,出版社名称出版社名称FROM图书,出版社WH
55、ERE图书.出版社编号=出版社.编号;第第4章章 表和视图表和视图步骤2：查询新视图内容：SELECT*FROM图书作者;说明：本训练中，使用了ORREPLACE选项，使新的视图替代了同名的原有视图，同时在查询中使用了相等连接，使得视图的列来自于两个不同的基表。第第4章章 表和视图表和视图【训练4】创建一个统计视图。步骤1：创建emp表的一个统计视图：CREATEVIEW统计表(部门名,最大工资,最小工资,平均工资)ASSELECTDNAME,MAX(SAL),MIN(SAL),AVG(SAL)FROMEMPE,DEPTDWHEREE.DEPTNO=D.DEPTNOGROUP BY DNAME
56、;第第4章章 表和视图表和视图步骤2：查询统计表：SELECT*FROM统计表;说明：本训练中，使用了分组查询和连接查询作为视图的子查询，每次查询该视图都可以得到统计结果。第第4章章 表和视图表和视图3创建只读视图只读视图创建只读视图要用创建只读视图要用WITH READ ONLY选项。选项。【训练5】创建只读视图。步骤1：创建emp表的经理视图：CREATEORREPLACEVIEWmanagerASSELECT*FROMempWHEREjob=MANAGERWITH READ ONLY;第第4章章 表和视图表和视图步骤2：进行删除：DELETEFROMmanager;执行结果：ERROR位
57、于第1行:ORA-01752:不能从没有一个键值保存表的视图中删除4创建基表不存在的视图创建基表不存在的视图正常情况下，不能创建错误的视图，特别是当基表还不存在时。但使用FORCE选选项项就可以在创建基表前先创建视图。创建的视图是无无效效视视图图，当访问无效视图时，Oracle将重新编译无效的视图。第第4章章 表和视图表和视图【训练6】使用FORCE选项创建带有错误的视图：CREATE FORCE VIEW班干部ASSELECT*FROM班级WHERE职务ISNOTNULL;执行结果：警告:创建的视图带有编译错误。（注意：基表“班级”还没有创建。）4.5.3视图的操作视图的操作对视图经常进行的
58、操作是查询操作，但也也可可以以在在一一定定条条件件下下对对视视图图进进行行插插入入、删删除除和和修修改改操操作作。对对视视图图的的这这些些修修改改数数据据的的操操作作最最终终传传递递到到基基表表。但是对视图的操作有很多限定。如果视图设置了只读，则对视图只能进行查询，不能进行修改操作。第第4章章 表和视图表和视图1视图的插入【训练1】视图插入练习。步骤1：创建清华大学出版社的图书视图：CREATEORREPLACEVIEW清华图书清华图书ASSELECT*FROM图图书书WHERE出版社编号=01;第第4章章 表和视图表和视图步骤2：插入新图书：INSERT INTO 清清华华图图书书 VALU
59、ES(A0005,软件工程,01,冯娟,5,27.3);执行结果：已创建1行。步骤3：显示视图：SELECT*FROM清华图书;第第4章章 表和视图表和视图步骤4：显示基表SELECT*FROM图书;执行结果：A0001计算机原理01刘勇525.3A0002C语言程序设计02马丽118.75A0003汇编语言程序设计02黄海明1520.18A0005软件工程01冯娟527.3第第4章章 表和视图表和视图说明：通过查看视图，可见新图书插入到了视图中。通过查看基表，看到该图书也出现在基表中，说明成功地进行了插入。新图书的出版社编号为“01”，仍然属于“清华大学出版社”。但是有一个问题，就是如果在“
60、清华图书”的视图中插入其他出版社的图书，结果会怎么样呢？结果是允许插入，但是在视图中看不见，在基表中可以看见，这显然是不合理的。第第4章章 表和视图表和视图2使用WITHCHECKOPTION选项为了避免上述情况的发生，可以使用WITHCHECKOPTION选项。使用该选项，可以对视图的插入或更新进行限制，即该数据必须满足视图定义中的子查询中的WHERE条件，否则不允许插入或更新。比如“清华图书”视图的WHERE条件是出版社编号要等于“01”(01是清华大学出版社的编号)，所以如果设置了WITHCHECKOPTION选项，那么只有出版社编号为“01”的图书才能通过清华视图进行插入。第第4章章
61、表和视图表和视图【训练2】使用WITHCHECKOPTION选项限制视图的插入。步骤1：重建清华大学出版社的图书视图，带WITHCHECKOPTION选项：CREATEORREPLACEVIEW清华图书ASSELECT*FROM图书WHERE出版社编号=01WITHCHECKOPTION;执行结果：视图已建立。第第4章章 表和视图表和视图步骤2：插入新图书：INSERTINTO清华图书VALUES(A0006,Oracle数据库,02,黄河,3,39.8);执行结果：ERROR位于第1行:ORA-01402:视图WITHCHECKOPTIDN违反where子句说明：可见通过设置了WITHCHE
62、CKOPTION选项，“02”出版社的图书插入受到了限制。如果修改已有图书的出版社编号情况会如何？答案是将同样受到限制。要是删除视图中已有图书，结果又将怎样呢？答案是可以，因为删除并不违反WHERE条件。第第4章章 表和视图表和视图3来自基表的限制除了以上的限制，基表本身的限制和约束也必须要考虑。如果生成子查询的语句是一个分组查询，或查询中出现计算列，这时显然不能对表进行插入。另外，主键和NOTNULL列如果没有出现在视图的子查询中，也不能对视图进行插入。在视图中插入的数据，也必须满足基表的约束条件。第第4章章 表和视图表和视图【训练3】基表本身限制视图的插入。步骤1：重建图书价格视图：CRE
63、ATEORREPLACEVIEW图书价格ASSELECT图书名称,单价FROM图书;执行结果：视图已建立。第第4章章 表和视图表和视图步骤2：插入新图书：INSERT INTO 图 书 价 格 VALUES(Oracle数 据 库,39.8);执行结果：ERROR位于第1行:ORA-01400:无法将NULL插入(SCOTT.图书.图书编号)说明：在视图中没有出现的基表的列，在对视图插入时，自动默认为NULL。该视图只有两列可以插入，其他列将默认为空。插入出错的原因是，在视图中不能插入图书编号，而图书编号是图书表的主键，是必须插入的列，不能为空，这就产生了矛盾。第第4章章 表和视图表和视图4.
64、5.4视图的查看USER_VIEWS字典中包含了视图的定义。USER_UPDATABLE_COLUMNS字典包含了哪些列可以更新、插入、删除。USER_OBJECTS字典中包含了用户的对象。可以通过DESCRIBE命令查看字典的其他列信息。在这里给出一个训练例子。第第4章章 表和视图表和视图【训练1】查看清华图书视图的定义：SELECT TEXT FROM USER_VIEWS WHEREVIEW_NAME=清华图书;结果：SELECT图书名称,作者,单价FROM图书WHERE出版社编号=01第第4章章 表和视图表和视图【训练2】查看用户拥有的视图：SELECT object_name FRO
65、M user_objects WHEREobject_type=VIEW;执行结果：OBJECT_NAME-清华图书图书作者第第4章章 表和视图表和视图4.6 阶段训练阶段训练【训练1】创建学生、系部、课程和成绩表，根据需要设置默认值、约束条件、主键和外键。步骤1：创建系部表，编号为主键，系部名称非空，电话号码惟一：CREATETABLE系部(编号NUMBER(5)PRIMARYKEY,系部名VARCHAR2(20)NOTNULL,第第4章章 表和视图表和视图地址VARCHAR2(30),电话VARCHAR2(15)UNIQUE,系主任VARCHAR2(10);步骤2：创建学生表，学号为主键，
66、姓名非空，性别只能是男或女，电子邮件包含并且惟一，系部编号参照系部表的编号：CREATETABLE学生(学号VARCHAR2(10)PRIMARYKEY,姓名VARCHAR2(10)NOTNULL,第第4章章 表和视图表和视图性别VARCHAR2(2)CHECK(性别=男OR性别=女),生日DATE,住址VARCHAR2(30),电子邮件VARCHAR2(20)CHECK(电子邮件LIKE%)UNIQUE,系部编号NUMBER(5),CONSTRAINTFK_XBBHFOREIGNKEY(系部编号)REFERENCES系部(编号);第第4章章 表和视图表和视图步骤3：创建课程表，编号为主键，课
67、程名非空，学分为1到5：CREATETABLE课程(编号NUMBER(5)PRIMARYKEY,课程名VARCHAR2(30)NOTNULL,学分NUMBER(1)CHECK(学分0AND学分=5);第第4章章 表和视图表和视图步骤4：创建成绩表，学号和课程编号为主键，学号参照学生表的学号，课程编号参照课程表的编号：CREATETABLE成绩(学号VARCHAR2(10),课程编号NUMBER(5),成绩NUMBER(3),CONSTRAINTPKPRIMARYKEY(学号,课程编号),第第4章章 表和视图表和视图CONSTRAINTFK_XHFOREIGNKEY(学 号 )REFERENCE
68、S学生(学号),CONSTRAINT FK_KCBH FOREIGN KEY(课程编号)REFERENCES课程(编号);第第4章章 表和视图表和视图说明：注意表之间的主从关系，对于系部和学生表，系部表为主表，学生表为子表。学生表的外键表示插入学生的系部编号必须是系部表的编号。对于成绩表，主键是学号和课程编号，表示如果学号相同课程编号必须不同，这样就可以惟一地标识记录。课程表有两个外键，分别参照学生表和课程表，表示成绩表的学号必须是学生表的学号，成绩表的课程编号必须是课程表的编号。【练习1】向表中插入数据，保证满足约束条件。第第4章章 表和视图表和视图4.7 练练 习习1创建表时，用来说明字段
69、默认值的是：A.CHECKB.CONSTRAINTC.DEFAULTD.UNIQUE2表的主键特点中，说法错误的是：A.一个表只能定义一个主键B.主键可以定义在表级或列级C.主键的每一列都必须非空D.主键的每一列都必须惟一第第4章章 表和视图表和视图2表的主键特点中，说法错误的是：A.一个表只能定义一个主键B.主键可以定义在表级或列级C.主键的每一列都必须非空D.主键的每一列都必须惟一3建立外键时添加ONDELETECASCADE从句的作用是：A.删除子表的记录，主表相关记录一同删除B.删除主表的记录，子表相关记录一同删除C.子表相关记录存在，不能删除主表记录D.主表相关记录存在，不能删除子表记录第第4章章 表和视图表和视图4下面有关表和视图的叙述中错误的是：A.视图的数据可以来自多个表B.对视图的数据修改最终传递到基表C.基表不存在，不能创建视图D.删除视图不会影响基表的数据5以下类型的视图中，有可能进行数据修改的视图是：A.带WITHREADONLY选项的视图B.子查询中包含分组统计查询的视图C.子查询中包含计算列的视图D.带WITHCHECKOPTION选项的视图
