sql实例教程+课件-第4章-表和视图.ppt

第4章 表和视图,4.1 表的创建和操作4.2 数据完整性和约束条件4.3 修改表结构4.4 分区表简介4.5 视图创建和操作4.6 阶段训练4.7 练 习,第4章 表和视图4.1 表的创建和操作,4.1 表的创建和操作,表由记录(行row)和字段(列column)构成，是数据库中存储数据的结构。要进行数据的存储和管理，首先要在数据库中创建表，即表的字段(列)结构。有了正确的结构，就可以用数据操作命令，插入、删除表中记录或对记录进行修改。比如，要进行图书管理，就需要创建图书和出版社等表，这里给出用于示范和训练的图书和出版社表的结构和内容，如表4-1、表4-2所示。,4.1 表的创建和操作表由记录(行row)和字段(列co,表4-1 图书表,表4-1 图书表,表4-2 出版社表,表4-2 出版社表,4.1.1 表的创建1创建表的语法表的创建需要CREATE TABLE 系统权限，表的基本创建语法如下：CREATE TABLE 表名(列名 数据类型(宽度)DEFAULT 表达式COLUMN CONSTRAINT,.TABLE CONSTRAINTTABLE_PARTITION_CLAUSE)；,4.1.1 表的创建,由此可见，创建表最主要的是要说明表名、列名、列的数据类型和宽度，多列之间用“，”分隔。可以是用中文或英文作为表名和列名。表名最大长度为30个字符。在同一个用户下，表不能重名，但不同用户表的名称可以相重。另外，表的名称不能使用Oracle的保留字。在一张表中最多可以包含2000列。该语法中的其他部分根据需要添加，作用如下：DEFAULT 表达式：用来定义列的默认值。COLUMN CONSTRAINT：用来定义列级的约束条件。TABLE CONSTRAINT：用来定义表级的约束条件。TABLE_PARTITION_CLAUSE：定义表的分区子句。,由此可见，创建表最主要的是要说明表名、列名、列的数据类型,【训练1】 创建图书和出版社表。步骤1：创建出版社表，输入并执行以下命令：CREATE TABLE 出版社(编号 VARCHAR2(2),出版社名称 VARCHAR2(30),地址 VARCHAR2(30),联系电话 VARCHAR2(20);执行结果：表已创建。,【训练1】 创建图书和出版社表。,步骤2：创建图书表，输入并执行以下命令：CREATE TABLE 图书(图书编号 VARCHAR2(5),图书名称 VARCHAR2(30),出版社编号 VARCHAR2(2),作者 VARCHAR2(10),出版日期 DATE,数量 NUMBER(3),单价 NUMBER(7,2);执行结果：表已创建。,步骤2：创建图书表，输入并执行以下命令：,步骤3：使用DESCRIBE显示图书表的结构，输入并执行以下命令：DESCRIBE 图书执行结果为：名称 是否为空? 类型 - - - 图书编号 VARCHAR2(5) 图书名称 VARCHAR2(30) 出版社编号 VARCHAR2(2) 作者 VARCHAR2(10) 出版日期 DATE 数量 NUMBER(3) 单价 NUMBER(7,2),步骤3：使用DESCRIBE显示图书表的结构，输入并执行,说明：在以上训练中，列名和数据类型之间用空格分隔，数据类型后的括号中为宽度(日期类型除外)。对于有小数的数字型，前一个参数为总宽度，后一个参数为小数位。用逗号分隔各列定义，但最后一列定义后不要加逗号。2通过子查询创建表如果要创建一个同已有的表结构相同或部分相同的表，可以采用以下的语法：CREATE TABLE 表名(列名.) AS SQL查询语句;该语法既可以复制表的结构，也可以复制表的内容，并可以为新表命名新的列名。新的列名在表名后的括号中给出，如果省略将采用原来表的列名。复制的内容由查询语句的WHERE条件决定。,说明：在以上训练中，列名和数据类型之间用空格分隔，数,【训练2】 通过子查询创建新的图书表。步骤1：完全复制图书表到“图书1”，输入并执行以下命令：CREATE TABLE 图书1 AS SELECT *FROM 图书;执行结果：表已创建。步骤2：创建新的图书表“图书2”，只包含书名和单价，输入并执行以下命令：CREATE TABLE 图书2(书名,单价) AS SELECT 图书名称,单价 FROM 图书;执行结果：表已创建。,【训练2】 通过子查询创建新的图书表。,步骤3：创建新的图书表“图书3”，只包含书名和单价，不复制内容，输入并执行以下命令：CREATE TABLE 图书3(书名,单价) AS SELECT 图书名称,单价 FROM 图书 WHERE 1=2;执行结果：表已创建。说明：“图书1”表的内容和结构同“图书”表完全一致，相当于表的复制。,步骤3：创建新的图书表“图书3”，只包含书名和单价，不复,“图书2”表只包含“图书”表的两列 “图书名称”和“单价”，并且对字段重新进行了命名，“图书2”表的“书名”对应“图书”表的“图书名称”，“图书2”表的“单价”对应“图书”表的“单价”。“图书3”表同“图书2”表的结构一样，但表的内容为空。因为WHERE条件始终为假，没有满足条件的记录，所以没有复制表的内容。3设置列的默认值可以在创建表的同时指定列的默认值，这样在插入数据时，如果不插入相应的列，则该列取默认值，默认值由DEFAULT部分说明。,“图书2”表只包含“图书”表的两列 “图书名称”和,【训练3】 创建表时设置默认值。步骤1：创建表时，设置表的默认值。CREATE TABLE 图书4(图书编号 VARCHAR2(5) DEFAULT NULL,图书名称 VARCHAR2(30) DEFAULT 未知,出版社编号 VARCHAR2(2) DEFAULT NULL,出版日期 DATE DEFAULT 01-1月-1900,作者 VARCHAR2(10) DEFAULT NULL,数量 NUMBER(3) DEFAULT 0,单价 NUMBER(7,2) DEFAULT NULL,借出数量 NUMBER(3) DEFAULT 0);执行结果：表已创建。,【训练3】 创建表时设置默认值。,步骤2：插入数据。INSERT INTO 图书4(图书编号) VALUES(A0001);执行结果：已创建 1 行。步骤2：查询插入结果。SELECT * FROM 图书4;,步骤2：插入数据。,执行结果：图书 图书名称 出版日期 作者 数量 单价 借出数量 - - - - - -A0001 未知 01-1月-00 0 0 0 说明：本训练中，只插入图书编号，其他部分取的是默认值。图书名称默认为“未知”，出版日期默认为1900年1月1日，数量默认为0，出版社编号、作者和单价的默认值为NULL。,执行结果：,【练习1】创建图书出借信息表，设置适当的默认值，并插入数据。结构如下：名称 是否为空? 类型 - - - 图书编号 VARCHAR2(10) 借书人 VARCHAR2(10) 借书日期 DATE 归还日期 DATE,【练习1】创建图书出借信息表，设置适当的默认值，并插入数,4删除已创建的表删除表的语法如下：DROP TABLE 表名CASCADE CONSTRAINTS；表的删除者必须是表的创建者或具有DROP ANY TABLE权限。CASCADE CONSTRAINTS表示当要删除的表被其他表参照时，删除参照此表的约束条件。有关内容请参考下一节。,4删除已创建的表,【训练4】 删除“图书1”表。DROP TABLE 图书1; 执行结果：表已丢弃。【练习2】删除“图书2”、“图书3”和“图书4”表。,【训练4】 删除“图书1”表。,4.1.2 表的操作1表的重命名语法如下：RENAME 旧表名 TO 新表名;只有表的拥有者，才能修改表名。【训练1】 修改“图书”表为“图书5”表：RENAME 图书 TO 图书5;执行结果：表已重命名。,4.1.2 表的操作,2清空表清空表的语法为：TRUNCATE TABLE 表名；清空表可删除表的全部数据并释放占用的存储空间。有关训练请参照DELETE语句部分，注意两者的区别。3添加注释(1) 为表添加注释的语法为：COMMENT ON TABLE 表名 IS .;该语法为表添加注释字符串。如IS后的字符串为空，则清除表注释。,2清空表,【训练2】 为emp表添加注释：“公司雇员列表”。COMMENT ON TABLE emp IS 公司雇员列表;执行结果：注释已创建。(2) 为列添加注释的语法为：COMMENT ON COLUMN 表名.列名 IS .该语法为列添加注释字符串。如IS后的字符串为空，则清除列注释。,【训练2】 为emp表添加注释：“公司雇员列表”。,【训练3】 为emp表的deptno列添加注释：“部门编号”。COMMENT ON COLUMN emp.deptno IS 部门编号;执行结果：注释已创建。【练习1】清除emp表的注释。4.1.3 查看表使用以下语法可查看表的结构：DESCRIBE 表名；DESCRIBE可以简写为DESC。可以通过对数据字典USER_OBJECTS的查询，显示当前模式用户的所有表。,【训练3】 为emp表的deptno列添加注释：“部门,【训练1】 显示当前用户的所有表。SELECT object_name FROM user_objects WHERE object_type=TABLE;执行结果：OBJECT_NAME-BONUSDEPTEMPSALGRADE出版社图书,【训练1】 显示当前用户的所有表。,4.2 数据完整性和约束条件,4.2.1 数据完整性约束 表的数据有一定的取值范围和联系，多表之间的数据有时也有一定的参照关系。在创建表和修改表时，可通过定义约束条件来保证数据的完整性和一致性。约束条件是一些规则，在对数据进行插入、删除和修改时要对这些规则进行验证，从而起到约束作用。,4.2 数据完整性和约束条件 4.2.1 数据完整性约,完整性包括数据完整性和参照完整性，数据完整性定义表数据的约束条件，参照完整性定义数据之间的约束条件。数据完整性由主键(PRIMARY KEY)、非空(NOT NULL)、惟一(UNIQUE)和检查(CHECK)约束条件定义，参照完整性由外键(FOREIGN KEY)约束条件定义。,完整性包括数据完整性和参照完整性，数,4.2.1 数据完整性约束表的数据有一定的取值范围和联系，多表之间的数据有时也有一定的参照关系。在创建表和修改表时，可通过定义约束条件来保证数据的完整性和一致性。约束条件是一些规则，在对数据进行插入、删除和修改时要对这些规则进行验证，从而起到约束作用。完整性包括数据完整性和参照完整性，数据完整性定义表数据的约束条件，参照完整性定义数据之间的约束条件。数据完整性由主键(PRIMARY KEY)、非空(NOT NULL)、惟一(UNIQUE)和检查(CHECK)约束条件定义，参照完整性由外键(FOREIGN KEY)约束条件定义。,4.2.1 数据完整性约束,4.2.2 表的五种约束表共有五种约束，它们是主键、非空、惟一、检查和外键。1主键(PRIMARY KEY)主键是表的主要完整性约束条件，主键惟一地标识表的每一行。一般情况下表都要定义主键，而且一个表只能定义一个主键。主键可以包含表的一列或多列，如果包含表的多列，则需要在表级定义。主键包含了主键每一列的非空约束和主键所有列的惟一约束。主键一旦成功定义，系统将自动生成一个B*树惟一索引，用于快速访问主键列。比如图书表中用“图书编号”列作主键，“图书编号”可以惟一地标识图书表的每一行。,4.2.2 表的五种约束,主键约束的语法如下：CONSTRANT 约束名 PRIMARY KEY-列级CONSTRANT 约束名 PRIMARY KEY(列名1,列名2,.)-表级2非空(NOT NULL)非空约束指定某列不能为空，它只能在列级定义。在默认情况下，Oracle允许列的内容为空值。比如“图书名称”列要求必须填写，可以为该列设置非空约束条件。非空约束语法如下：CONSTRANT 约束名 NOT NULL-列级,主键约束的语法如下：,约束分为两级，一个约束条件根据具体情况，可以在列级或表级定义。列级约束：约束表的某一列，出现在表的某列定义之后，约束条件只对该列起作用。表级约束：约束表的一列或多列，如果涉及到多列，则必须在表级定义。表级约束出现在所有列定义之后。,约束分为两级，一个约束条件根据具体情况，可以在列级或表级,4.2.2 表的五种约束表共有五种约束，它们是主键、非空、惟一、检查和外键。1主键(PRIMARY KEY)主键是表的主要完整性约束条件，主键惟一地标识表的每一行。一般情况下表都要定义主键，而且一个表只能定义一个主键。主键可以包含表的一列或多列，如果包含表的多列，则需要在表级定义。主键包含了主键每一列的非空约束和主键所有列的惟一约束。主键一旦成功定义，系统将自动生成一个B*树惟一索引，用于快速访问主键列。比如图书表中用“图书编号”列作主键，“图书编号”可以惟一地标识图书表的每一行。,4.2.2 表的五种约束,主键约束的语法如下：CONSTRANT 约束名 PRIMARY KEY-列级CONSTRANT 约束名 PRIMARY KEY(列名1,列名2,.)-表级,主键约束的语法如下：,2非空(NOT NULL)非空约束指定某列不能为空，它只能在列级定义。在默认情况下，Oracle允许列的内容为空值。比如“图书名称”列要求必须填写，可以为该列设置非空约束条件。非空约束语法如下：CONSTRANT 约束名 NOT NULL-列级3惟一(UNIQUE)惟一约束条件要求表的一列或多列的组合内容必须惟一，即不相重，可以在列级或表级定义。但如果惟一约束包含表的多列，则必须在表级定义。比如出版社表的“联系电话”不应该重复，可以为其定义惟一约束。,2非空(NOT NULL),惟一约束的语法如下：CONSTRANT 约束名 UNIQUE-列级CONSTRANT 约束名 UNIQUE(列名1,列名2,.)-表级,惟一约束的语法如下：,4检查(CHECK)检查约束条件是用来定义表的一列或多列的一个约束条件，使表的每一列的内容必须满足该条件(列的内容为空除外)。在CHECK条件中，可以调用SYSDATE、USER等系统函数。一个列上可以定义多个CHECK约束条件，一个CHECK约束可以包含一列或多列。如果CHECK约束包含表的多列，则必须在表级定义。比如图书表的“单价”的值必须大于零，就可以设置成CHECK约束条件。,4检查(CHECK),检查约束的语法如下：CONSTRANT 约束名 CHECK(约束条件) -列级，约束条件中只包含本列CONSTRANT 约束名 CHECK(约束条件) -表级，约束条件中包含多列5外键(FOREIGN KEY)指定表的一列或多列的组合作为外键，外键参照指定的主键或惟一键。外键的值可以为NULL，如果不为NULL，就必须是指定主键或惟一键的值之一。外键通常用来约束两个表之间的数据关系，这两个表含有主键或惟一键的称为主表，定义外键的那张表称为子表。如果外键只包含一列，则可以在列级定义；如果包含多列，则必须在表级定义。,检查约束的语法如下：,外键的列的个数、列的数据类型和长度，应该和参照的主键或惟一键一致。比如图书表的“出版社编号”列，可以定义成外键，参照出版社表的“编号”列，但“编号”列必须先定义成为主键或惟一键。如果外键定义成功，则出版社表称为主表，图书表称为子表。在表的创建过程中，应该先创建主表，后创建子表。,外键的列的个数、列的数据类型和长度，应该和参照的主键或惟,外键约束的语法如下：第一种语法，如果子记录存在，则不允许删除主记录：CONSTRANT 约束名 FOREIGN KEY(列名1,列名2,.)REFERENCES 表名(列名1,列名2,.)第二种语法，如果子记录存在，则删除主记录时，级联删除子记录：CONSTRANT 约束名 FOREIGN KEY(列名1,列名2,.)REFERENCES 表名(列名1,列名2,.)on delete cascade,外键约束的语法如下：,第三种语法，如果子记录存在，则删除主记录时，将子记录置成空：CONSTRANT 约束名 FOREIGN KEY(列名1,列名2,.)REFERENCES 表名(列名1,列名2,.)on delete set null其中的表名为要参照的表名。在以上5种约束的语法中，CONSTRANT关键字用来定义约束名，如果省略，则系统自动生成以SYS_开头的惟一约束名。约束名的作用是当发生违反约束条件的操作时，系统会显示违反的约束条件名称，这样用户就可以了解到发生错误的原因。,第三种语法，如果子记录存在，则删除主记录时，将子记,4.2.3 约束条件的创建在表的创建语法中可以定义约束条件：CREATE TABLE 表名(列名 数据类型DEFAULT 表达式COLUMN CONSTRAINT,.TABLE CONSTRAINT)；其中，COLUMN CONSTRAINT用来定义列级约束条件；TABLE CONSTRAINT用来定义表级约束条件。,4.2.3 约束条件的创建,【训练1】 创建带有约束条件的出版社表(如果已经存在，先删除)：CREATE TABLE 出版社(编号 VARCHAR2(2) CONSTRAINT PK_1 PRIMARY KEY,出版社名称 VARCHAR2(30) NOT NULL ,地址 VARCHAR2(30) DEFAULT 未知,联系电话 VARCHAR2(20);执行结果：表已创建。 说明：出版社表的主键列是“编号”列，主键名为PK_1。“出版社名称”必须填写，地址的默认值为“未知”。,【训练1】 创建带有约束条件的出版社表(如果已经存在，,【训练2】 创建带有约束条件(包括外键)的图书表(如果已经存在，先删除)：CREATE TABLE 图书(图书编号 VARCHAR2(5) CONSTRAINT PK_2 PRIMARY KEY,图书名称 VARCHAR2(30) NOT NULL,出版社编号 VARCHAR2(2) CHECK(LENGTH(出版社编号)=2) NOT NULL,作者 VARCHAR2(10) DEFAULT 未知,出版日期 DATE DEFAULT 01-1月-1900,【训练2】 创建带有约束条件(包括外键)的图书表(如果,数量 NUMBER(3) DEFAULT 1 CHECK(数量0),单价 NUMBER(7,2),CONSTRAINT YS_1 UNIQUE(图书名称,作者),CONSTRAINT FK_1 FOREIGN KEY(出版社编号) REFERENCES 出版社(编号) ON DELETE CASCADE);执行结果：表已创建。,数量 NUMBER(3) DEFAULT 1 CHECK,说明：因为两个表同属于一个用户，故约束名不能相重，图书表的主键为“图书编号”列，主键名为PK_2。其中，约束条件CHECK(LENGTH(出版社编号)=2)表示出版社编号的长度必须是2，约束条件UNIQUE(图书名称,作者)表示“图书名称”和“作者”两列的内容组合必须惟一。FOREIGN KEY(出版社编号) REFERENCES 出版社(编号) 表示图书表的“出版社编号”列参照出版社的“编号”主键列。出版社表为主表，图书表为子表，出版社表必须先创建。ON DELETE CASCADE表示当删除出版社表的记录时，图书表中的相关记录同时删除，比如删除清华大学出版社，则图书表中清华大学出版社的图书也会被删除。如果同时出现DEFAULT和CHECK，则DEFAULT需要出现在CHECK约束条件之前。,说明：因为两个表同属于一个用户，故约束名不能相重，图书表,【训练3】 插入数据，验证约束条件。步骤1：插入出版社信息：INSERT INTO 出版社 VALUES(01,清华大学出版社,北京,010-83456272);执行结果：已创建1行。继续插入INSERT INTO 出版社 VALUES(01,电子科技大学出版社,西安,029-88201967);执行结果：ERROR 位于第1行:ORA-00001: 违反惟一约束条件 (SCOTT.PK_1),【训练3】 插入数据，验证约束条件。,第二个插入语句违反约束条件PK_1，即出版社表的主键约束，原因是主键的值必须是惟一的。修改第二个语句的编号为“02”，重新执行：INSERT INTO 出版社 VALUES(02,电子科技大学出版社,西安,029-88201967);执行结果：已创建 1 行。步骤2：插入图书信息：INSERT INTO 图书(图书编号,图书名称,出版社编号,作者,单价) VALUES(A0001,计算机原理,01,刘勇,25.30);执行结果：已创建1行。,第二个插入语句违反约束条件PK_1，即出版社表的主键约束,继续插入：INSERT INTO 图书(图书编号，图书名称，出版社编号，作者，单价) VALUES(A0002, C语言程序设计,03,马丽, 18.75);执行结果：ERROR 位于第 1 行:ORA-02291: 违反完整约束条件 (SCOTT.FK_1) - 未找到父项关键字 第二个插入语句违反外键约束关系FK_1，因为在出版社表中，被参照的主键列中没有“03”这个出版社，所以产生未找到父项关键字的错误，修改后重新插入：,继续插入：,INSERT INTO 图书(图书编号，图书名称，出版社编号，作者，单价) VALUES(A0002, C语言程序设计,02,马丽, 18.75);执行结果：已创建 1 行。继续插入：INSERT INTO 图书(图书编号,图书名称,出版社编号,作者,数量,单价) VALUES(A0003,汇编语言程序设计,02,黄海明,0,20.18);,INSERT INTO 图书(图书编号，图书名称，出版社,执行结果：ERROR 位于第 1 行:ORA-02290: 违反检查约束条件 (SCOTT.SYS_C003114)插入的数量为0，违反约束条件CHECK(数量0)。该约束条件没有命名，所以约束名SYS_C003114为系统自动生成。修改后重新执行：INSERT INTO 图书(图书编号,图书名称,出版社编号,作者,数量,单价) VALUES(A0003,汇编语言程序设计,02,黄海明,15,20.18);,执行结果：,执行结果：已创建 1 行。步骤3：显示插入结果：SELECT * FROM 出版社;执行结果：编号 出版社名称 地址 联系电话- - - -,执行结果：,01 清华大学出版社 北京 010-8345627202 电子科技大学出版社 西安 029-88201967继续查询：SELECT * FROM 图书;执行结果：图书编号 图书名称 出版社编号 作者 出版日期 数量 单价- - - - - - -,01 清华大学出版社 北,A0001 计算机原理 01 刘勇 01-1月 -00 1 25.3A0002 C语言程序设计 02 马丽 01-1月 -00 1 18.75A0003 汇编语言程序设计 02 黄海明 01-1月 -00 15 20.18步骤4：提交插入的数据：COMMIT;执行结果：提交完成。说明：在图书表中，没有插入的数量取默认值1，没有插入的出版日期取默认值01-1月-00(即1900年1月1日)。,A0001 计算机原理 01 刘勇,【训练4】 通过删除数据验证ON DELETE CASCADE的作用。步骤1：删除出版社01(清华大学)：DELETE FROM 出版社 WHERE 编号=01;执行结果：已删除 1 行。,【训练4】 通过删除数据验证ON DELETE CAS,步骤2：显示删除结果：显示出版社表结果：SELECT * FROM 出版社;执行结果：编号 出版社名称 地址 联系电话- - - -02 电子科技大学出版社 西安 029-88201967显示图书表结果：SELECT * FROM 图书;,步骤2：显示删除结果：,执行结果：图书编号 图书名称 出版社编号 作者 出版日期 数量 单价- - - - - - -A0002 C语言程序设计 02 马丽 01-1月 -00 1 18.75A0003 汇编语言程序设计 02 黄海明 01-1月 -00 15 20.18,执行结果：,步骤3：恢复删除:ROLLBACK；回退已完成。说明：参见训练2，外键约束FK_1带有ON DELETE CASCAD选项，删除清华大学出版社时，对应的图书也自动删除。其他两种情况用户可自行验证。【练习1】创建学生、系部表，添加必要主键、外键等约束条件。,步骤3：恢复删除:,4.2.4 查看约束条件数据字典USER_CONSTRAINTS中包含了当前模式用户的约束条件信息。其中，CONSTRAINTS_TYPE 显示的约束类型为：C：CHECK约束。P：PRIMARY KEY约束。U：UNIQUE约束。R：FOREIGN KEY约束。其他信息可根据需要进行查询显示，可用DESCRIBE命令查看USER_CONSTRAINTS的结构。,4.2.4 查看约束条件,【训练1】 检查表的约束信息：SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,SEARCH_CONDITION FROM USER_CONSTRAINTSWHERE TABLE_NAME=图书;执行结果：CONSTRAINT_NAME C SEARCH_CONDITION- - -SYS_ C003111 C 图书名称 IS NOT NULLSYS_C003112 C 出版社编号 IS NOT NULLSYS_C003113 C LENGTH(出版社编号)=2SYS_C003114 C 数量0PK_2 PYS_1 UFK_1 R,【训练1】 检查表的约束信息：,说明：图书表共有个约束条件，一个PRIMARY KEY(P)约束PK_2，一个FOREIGN KEY(R)约束FK_1，一个UNIQUE(R)约束YS_1和4个CHECK(C)约束SYS_C003111、SYS_C003112、SYS_C003113和SYS_C003114，4个CHECK约束的名字是由系统命名的。,说明：图书表共有个约束条件，一个PRIMARY KEY,4.2.5 使约束生效和失效约束的作用是保护数据完整性，但有的时候约束的条件可能不再适用或没有必要，如果这个约束条件依然发生作用就会影响操作的效率，比如导出和导入数据时要暂时关闭约束条件，这时可以使用下面的命令关闭或打开约束条件。使约束条件失效：ALTER TABLE 表名 DISABLE CONSTRANT 约束名;使约束条件生效：ALTER TABLE 表名 ENABLE CONSTRANT 约束名;,4.2.5 使约束生效和失效,【训练1】 使图书表的数量检查失效。步骤1：使约束条件SYS_C003114(数量0)失效：ALTER TABLE 图书 DISABLE CONSTRAINT SYS_C003114;执行结果：表已更改。步骤2：修改数量为0：UPDATE 图书 SET 数量=0 WHERE 图书编号=A0001;执行结果：已更新 1 行。,【训练1】 使图书表的数量检查失效。,步骤3：使约束条件SYS_C003114生效：ALTER TABLE 图书 ENABLE CONSTRAINT SYS_C003114;执行结果：ERROR 位于第 1 行:ORA-02293: 无法验证 (SCOTT.SYS_C003114) - 违反检查约束条件继续执行：UPDATE 图书 SET 数量=5 WHERE 图书编号=A0001;执行结果：已更新 1 行。,步骤3：使约束条件SYS_C003114生效：,继续执行：ALTER TABLE 图书 ENABLE CONSTRAINT SYS_C003114;执行结果：表已更改。说明：在步骤1中，先使名称为SYS_C003114 (数量0)的检查条件暂时失效，所以步骤2修改第1条记录的数量为0才能成功。步骤3使该约束条件重新生效，但因为表中有数据不满足该约束条件，所以发生错误，通过修改第一条记录的数量为5，使约束条件重新生效。,继续执行：,4.3 修改表结构,4.3.1 增加新列增加新列的语法如下：ALTER TABLE 表名ADD 列名 数据类型DEFAULT 表达式COLUMN CONSTRAINT;如果要为表同时增加多列，可以按以下格式进行：ALTER TABLE 表名ADD (列名 数据类型DEFAULT 表达式COLUMN CONSTRAINT.);,4.3 修改表结构4.3.1 增加新列,通过增加新列可以指定新列的数据类型、宽度、默认值和约束条件。增加的新列总是位于表的最后。假如新列定义了默认值，则新列的所有行自动填充默认值。对于有数据的表，新增加列的值为NULL，所以有数据的表，新增加列不能指定为NOT NULL约束条件。【训练1】 为“出版社”增加一列“电子邮件”：ALTER TABLE 出版社ADD 电子邮件 VARCHAR2(30) CHECK(电子邮件 LIKE %);显示结果：表已更改。,通过增加新列可以指定新列的数据类型、宽度、默认值和约束条,说明：为出版社新增加了一列“电子邮件”，数据类型为VARCHAR2，宽度为30。CHECK(电子邮件 LIKE %)表示电子邮件中必须包含字符“”。可用DESCRIBE命令查看表的新结构。,说明：为出版社新增加了一列“电子邮件”，数据类型为VAR,4.3.2 修改列修改列的语法如下：ALTER TABLE 表名MODIFY 列名 数据类型 DEFAULT 表达式COLUMN CONSTRAINT如果要对表同时修改多列，可以按以下格式进行：ALTER TABLE 表名MODIFY (列名 数据类型DEFAULT 表达式COLUMN CONSTRAINT.);其中，列名是要修改的列的标识，不能修改。如果要改变列名，只能先删除该列，然后重新增加。其他部分都可以进行修改，如果没有给出新的定义，表示该部分属性不变。,4.3.2 修改列,修改列定义还有以下一些特点：(1) 列的宽度可以增加或减小，在表的列没有数据或数据为NULL时才能减小宽度。(2) 在表的列没有数据或数据为NULL时才能改变数据类型，CHAR和VARCHAR2之间可以随意转换。(3) 只有当列的值非空时，才能增加约束条件NOT NULL。(4) 修改列的默认值，只影响以后插入的数据。,修改列定义还有以下一些特点：,【训练1】 修改“出版社”表“电子邮件”列的宽度为40。ALTER TABLE 出版社MODIFY 电子邮件 VARCHAR2(40);执行结果：表已更改。说明：将“电子邮件”列的宽度由原来的30修改为40，约束条件保持不变。可用DESCRIBE命令查看新结构。,【训练1】 修改“出版社”表“电子邮件”列的宽度为40,4.3.3 删除列删除列的语法如下：ALTER TABLE 表名DROP COLUMN 列名CASCADE CONSTRAINTS；如果要同时删除多列，可以按以下格式进行：ALTER TABLE 表名DROP(COLUMN 列名 数据类型DEFAULT 表达式COLUMN CONSTRAINT.)CASCADE CONSTRAINTS;当删除列时，列上的索引和约束条件同时被删除。但如果列是多列约束的一部分，则必须指定CASCADE CONSTRAINTS才能删除约束条件。,4.3.3 删除列,【训练1】 删除“出版社”表的“电子邮件”列。ALTER TABLE 出版社DROP COLUMN 电子邮件;执行结果：表已更改。说明：此训练将“电子邮件”列删除。可用DESCRIBE命令查看新结构。,【训练1】 删除“出版社”表的“电子邮件”列。,使用以下语法，可以将列置成UNUSED状态，这样就不会在表中显示出该列：ALTER TABLE 表名 SET UNUSED COLUMN 列名 CASCADE CONSTRAINTS;以后可以重新使用或删除该列。通过数据字典可以查看标志成UNUSED的列。删除标志成UNUSED的列：ALTER TABLE 表名 DROP UNUSED COLUMNS;,使用以下语法，可以将列置成UNUSED状态，这样就不会在,【训练2】 将“图书”表的“出版日期”列置成UNUSED，并查看。步骤1：设置“出版日期”列为UNUSED：ALTER TABLE 图书 SET UNUSED COLUMN 出版日期;步骤2：显示结构：DESC 图书;执行结果：名称 是否为空? 类型- - -,【训练2】 将“图书”表的“出版日期”列置成UNUSE,图书编号 NOT NULL VARCHAR2(5)图书名称 NOT NULL VARCHAR2(30)出版社编号 NOT NULL VARCHAR2(2)作者 VARCHAR2(10)数量 NUMBER(3) 单价 NUMBER(7,2)步骤3：删除UNUSED列：ALTER TABLE 图书 DROP UNUSED COLUMNS;执行结果：表已更改。,图书编号,4.3.4 约束条件的修改可以为表增加或删除表级约束条件。1增加约束条件增加约束条件的语法如下：ALTER TABLE 表名 ADD CONSTRAINT 约束名 表级约束条件;【训练1】 为emp表的mgr列增加外键约束：ALTER TABLE emp ADD CONSTRAINT FK_3 FOREIGN KEY(mgr) REFERENCES emp(empno);执行结果：表已更改。说明：本训练增加的外键为参照自身的外键，含义是mgr(经理编号)列的内容必须是empno(雇员编号)之一。,4.3.4 约束条件的修改,2删除约束条件删除约束条件的语法如下：ALTER TABLE 表名DROP PRIMARY_KEY|UNIQUE(列名)|CONSTRAINT 约束名CASCADE;【训练2】 删除为emp表的mgr列增加的外键约束：ALTER TABLE emp DROP CONSTRAINT FK_3;执行结果：表已更改。,2删除约束条件,4.4 分区表简介,4.4.1 分区的作用在某些场合会使用非常大的表，比如人口信息统计表。如果一个表很大，就会降低查询的速度，并增加管理的难度。一旦发生磁盘损坏，可能整个表的数据就会丢失，恢复比较困难。根据这一情况，可以创建分区表，把一个大表分成几个区(小段)，对数据的操作和管理都可以针对分区进行，这样就可以提高数据库的运行效率。分区可以存在于不同的表空间上，提高了数据的可用性。,4.4 分区表简介4.4.1 分区的作用,分区的依据可以是一列或多列的值，这一列或多列称为分区关键字或分区列。所有分区的逻辑属性是一样的(列名、数据类型、约束条件等)，但每个分区可以有自己的物理属性(表空间、存储参数等)。分区有三种：范围分区、哈斯分区和混合分区。范围分区(RANGE PARTITIONING)：根据分区关键字值的范围建立分区。比如，根据省份为人口数据表建立分区。哈斯分区(HASH PARTITIONING)：在分区列上使用HASH算法进行分区。混合分区(COMPOSITE PARTITIONING)：混合以上两种方法，使用范围分区建立主分区，使用HASH算法建立子分区。,分区的依据可以是一列或多列的值，这一列或多列称为分区关键,4.4.2 分区的实例由于分区用到了很多存储参数，故不在这里进行详细讨论，只给出一个范围分区的简单训练实例。 【训练1】 创建和使用分区表。步骤1：创建按成绩分区的考生表，共分为3个区：CREATE TABLE 考生 (考号 VARCHAR2(5),姓名 VARCHAR2(30),成绩 NUMBER(3),4.4.2 分区的实例,PARTITION BY