Oracle数据库应用与开发案例教程课件05.第五章 表管理

2023/12/211Oracle数据库应用

与开发案例教程第五章表管理2023/12/21Oracle数据库应用与开发案例教程2第五章表管理5.1表和方案5.2创建表5.3维护表5.4维护约束条件5.5序列和同义词2023/12/21Oracle数据库应用与开发案例教程35.1表和方案常用数据类型在创建表的时候，不仅需要指定表名、列名，而且要根据情况为每个列选择合适的数据类型并定义其长度，用来指定该列可以存取哪种类型的数据。Oracle中常用的数据类型如下所示：字符数据类型数字数据类型日期和时间数据类型二进制数据类型大对象数据类型行数据类型2023/12/21Oracle数据库应用与开发案例教程45.1表和方案用户与方案方案是指用户所拥有一系列逻辑数据结构或数据库对象的集合，在Oracle数据库中对象是以用户来组织的，一个方案对应一个数据库用户，每个用户都有一个单独的方案。用户与方案名称相同，而且是一一对应的关系。【例5-1】在数据库ZCGL中创建用户ZCGL_OPER，密码是admin，默认表空间是zcgl_tbs1，临时表空间是zcgl_temp1，并为该用户在表空间zcgl_tbs1中分配50M的限额。并将DBA角色授予该用户。2023/12/21Oracle数据库应用与开发案例教程55.2

创建表创建表的一般语法所谓创建表，实际上就是在数据库中定义表的结构。表的结构主要包括表与列的名称、列的数据类型，以及建立在表或列上的约束。基本语法格式如下所示：CREATETABLE[schema.]table_name(column1datatype1[DEFAULTexpl][column1constraint],column2datatype2[DEFAULTexp2][column2constraint],…)[TABLESPACEtablespace_name];2023/12/21Oracle数据库应用与开发案例教程65.2

创建表语法说明1）schema：指定表所属的用户方案名称。如果用户在自己的方案中创建表，要求该用户必须具有CREATETABLE系统权限；如果要在其他方案中创建表，则要求该用户必须具有CREATEANYTABLE系统权限。2）table_name：指定要创建的表的名称。3）columnldatatypel：用于指定列的名称和及其数据类型。4）DEFAULTexpl：用于指定列的默认值。5）columnlconstraint：用于为约束命名。如果不使用columnlconstraint子句，Oracle将自动为约束建立默认的约束名。6）TABLESPACEtablespace_name：用于指定存储表或索引的表空间。2023/12/21Oracle数据库应用与开发案例教程75.2

创建表创建标准表【例5-2】在用户ZCGL_OPER的方案中创建一个名为ZICHANMINGXI的标准表。【例5-3】使用DESC命令来显示ZICHANMINGXI表的结构。2023/12/21Oracle数据库应用与开发案例教程85.2

创建表创建临时表如果在创建表时用关键字TEMPORARY或GLOBALTEMPORARY指定了表的类型，Oracle会生成一个临时表。临时表用于存放事务或会话的私有数据，其表结构会一直存在，但其数据只是在当前事务内或会话内有效。临时表分为事务临时表和会话临时表两种。【例5-4】创建事务临时表temp1。【例5-5】创建会话临时表temp2。2023/12/21Oracle数据库应用与开发案例教程95.2

创建表基于已有的表创建新表通过在CREATETABLE语句中嵌套SELECT子查询就可以基于已有的表或视图来创建新表，这种创建表的语法格式是：CREATETABLEtable_name[(column1,column2,……)]ASSELECT<query>语法说明：1）column1,column2,……：定义新表的字段，新表中所有列的数据类型和长度都与原表中相应的列一样；如果省略该子句，那么新表的字段名与查询结果集中的字段名同名。2）ASSELECT<query>：在子查询中可以引用一个或多个表（或视图），查询结果集中包含的列即是新表中定义的列，并且查询到的记录都会插入到新表中。2023/12/21Oracle数据库应用与开发案例教程105.2

创建表基于已有的表创建新表【例5-6】通过复制表ZICHANMINGXI的结构和数据来创建NEW_ZICHANMINGXI新表。【例5-7】创建新表NEW_ZICHANMINGXI_1，其表结构同ZICHANMINGXI表，但不含有该表的任何记录。【例5-8】通过复制表ZICHANMINGXI的ZCID，FLID，BMID，ZTID，YHID和ZCMC等列的结构和数据，来创建新表NEW_ZICHANMINGXI_2，并将列ZCMC改名NAME。2023/12/21Oracle数据库应用与开发案例教程115.3维护表表的结构创建完成以后，如果发现有不满意或者不符合实际需要的地方，可以对表的结构进行修改：可以添加或删除表中的列、修改表中列的名称、数据类型和长度等，还可以对表进行重新命名和重新组织。普通用户只能对自己方案中的表进行更改，而具有ALTERANYTABLE系统权限的用户可以修改任何方案中的表。2023/12/21Oracle数据库应用与开发案例教程125.3维护表字段操作：添加列向一个现有的表中添加一个新列的语法格式是：ALTERTABLE[schema.]table_nameADD(column_definition1,column_definition2﹍);语法说明：新添加的列总是位于表的末尾。column_definition部分包括列名、列的数据类型及其默认值。【例5-9】在ZICHANMINGXI表中添加一个新列：规格型号GGXH。2023/12/21Oracle数据库应用与开发案例教程135.3维护表字段操作：更改列如果需要调整一个表中某些列的数据类型、长度和默认值，就需要更改这些列的属性。更改表中现有列的语法格式是：ALTERTABLE[schema.]table_nameMODIFY(column_name1new_attributesl,column_name2new_attributes2﹍);【例5-10】将ZICHANMINGXI表的BMID列的数据类型改为CHAR(8)，将BZ列的数据类型改为VARCHAR2(90)。2023/12/21Oracle数据库应用与开发案例教程145.3维护表字段操作：删除列当不再需要某些列时，可以将其删除。删除列时Oracle将删除表中每条记录内的相应列的值，并释放其占用的存储空间。在被删除列上建立的索引和约束也会被删除，如果被删除的列是一个多列约束的组成部分，那么就必须指定CASCADECONSTRAINTS选项，这样才会删除相关的约束。直接删除列的语法是：ALTERTABLE[schema.]table_nameDROP(column_name1,column_name2﹍)[CASCADECONSTRAINTS];说明：可以在括号中使用多个列名，每个列名用逗号分隔。【例5-11】下面的语句删除ZICHANMINGXI表中的GGXH列和BZ列 2023/12/21Oracle数据库应用与开发案例教程155.3维护表字段操作：将列标记为UNUSED状态为了避免在数据库使用高峰期间由于执行删除列的操作而占用过多系统资源，可以暂时通过ALTERTABLE--SETUNUSED语句将要删除的列设置为UNUSED状态。当表中某列被设置为UNUSED状态后，用户将无法对该列进行各种DML操作，实际上Oracle并没有释放该列占用的存储空间，该列还是存在于数据库中。该语句的语法格式为：ALTERTABLE[schema.]table_nameSETUNUSED(column_name1,column_name2﹍)[CASCADECONSTRAINTS];

2023/12/21Oracle数据库应用与开发案例教程165.3维护表字段操作：将列标记为UNUSED状态【例5-12】将ZICHANMINGXI表中的ZCYZ列和GRSJ列标记为UNUSED状态： 【例5-13】在数据字典视图USER_UNUSED_COL_TABS、ALL­_UNUSED_COL_TABS和DBA_UNUSED_COL_TABS中可以查看数据库中被标记为UNUSED状态的列所在表的信息。【例5-14】将ZICHANMINGXI表中的被标记为UNUSED状态的列删除：2023/12/21Oracle数据库应用与开发案例教程175.3维护表重命名表当重新命名表时，Oracle会自动把旧表上的视图、对象权限和约束条件转换到新表名上，但所有与旧表相关的视图、同义词、存储过程和函数等对象会失效。可以使用两种方法来重新命名表。 1.RENAME语句使用RENAME语句来修改一个表、视图、序列、专用同义词名称的语法格式为：RNAMEold_tablenameTOnew_tablename【例5-15】把ZICHANMINGXI表的名称更改为ZICHANMINGXI_CHANGE：2023/12/21Oracle数据库应用与开发案例教程185.3维护表重命名表 2.ALTERTABLE语句用RENAME语句只能更改自己方案中对象的名字，而不能重命名其他用户方案中对象的名字。可以使用带有RENAMETO子句的ALTERTABLE语句来重命名其他用户方案中对象的名字。但用户必须对该表拥有ALTER权限或者ALTERANYTABLE系统权限才能实现这一操作。【例5-16】把ZICHANMINGXI_CHANGE表的名称更改为ZICHANMINGXI：2023/12/21Oracle数据库应用与开发案例教程195.3维护表删除表 当不再需要某个表时，可以将该表删除，删除表时该表中的数据也将被删除。其语法格式如下：DROPTABLE[schema.]table_name[CASCADECONSTRAINTS];说明：如果在table_name前面没有加schema，则表示删除自己方案中的表。如果要删除其他方案中的表，则必须加schema，并且还必须具有DROPANYTABLE系统权限。【例5-17】删除ZICHANMINGXI表。【例5-18】删除表ZICHANMINGXI，同时删除所有引用该表的视图，约束，索引和触发器。2023/12/21Oracle数据库应用与开发案例教程205.3维护表移动表 在创建表的时候如果没有指定表所属的表空间，Oracle将把该表存储到默认表空间中。使用ALTERTABLE…MOVE语句将表从一个表空间移动到另一个表空间中。其语法格式为：ALTERTABLEtable_nameMOVETABLESPACEtablespace_name;【例5-19】将ZICHANMINGXI表从当前的表空间移动到USERS表空间中。2023/12/21Oracle数据库应用与开发案例教程215.3维护表查看表信息 创建表时，Oracle会将表的定义存放到数据字典中，常用数据字典有：1）DBA_TABLES：描述数据库中所有关系表。2）ALL_TABLES：描述用户可以访问的所有表。3）USER_TABLES：描述当前用户拥有的表。【例5-20】查询用户ZCGL_OPER的表ZICHANMINGXI信息：2023/12/21Oracle数据库应用与开发案例教程225.4维护约束条件数据库的完整性是指维护数据的正确性、一致性和安全性。例如，当用户执行INSERT、DELETE和UPDATE等操作时，如果将无效的数据添加到数据库的表中就破坏了数据库的完整性。约束条件是在表中定义的，用于维护数据库完整性的一些规则。可以在创建表时在单个表中或多个表之间定义一些约束条件来防止将错误的数据插入到表中，并可以保证数据的一致性。 Oracle数据库的完整性约束主要包括主键约束（PRIMARYKEY），非空约束（NOTNULL），唯一性约束（UNIQUE），检查约束（CHECK）和外键约束（FOREIGNKEY）。如果某约束只作用于一列，该约束可以定义为列约束或表约束；如果某约束作用于多个列，那么该约束应该定义为表约束。2023/12/21Oracle数据库应用与开发案例教程235.4维护约束条件约束条件的定义：非空约束NOTNULL NOTNULL即非空约束，该约束只能定义为列约束，用于限定一个字段的取值不能为空。如果在某列上定义了NOTNULL约束，插入数据时，必须为该列提供数据。同一个表中可以有多个列被定义为NOTNULL约束。【例5-21】在BUMEN表的BMID列上定义NOTNULL约束。【例5-22】在BUMEN表的BMMC列上定义NOTNULL约束。2023/12/21Oracle数据库应用与开发案例教程245.4维护约束条件约束条件的定义：唯一性约束UNIQUE UNIQUE约束即唯一性约束，用于要求所约束的列中不能有重复值。可以为一列或者多列组合定义UNIQUE约束。Oracle会自动为具有UNIQUE约束的列建立一个唯一索引（UniqueIndex)，对同一个列，可以同时定义UNIQUE约束和NOTNULL约束。【例5-23】在BUMEN表的BMMC列上定义UNIQUE约束。

2023/12/21Oracle数据库应用与开发案例教程255.4维护约束条件约束条件的定义：检查约束CHECKCHECK约束即检查约束，用于强制在约束中的列必须满足指定的条件表达式。该条件表达式需要引用表中的一列或多列，其计算结果是一个布尔值。定义CHECK约束列的值需要满足条件表达式，但可以是NULL。CHECK可以定义为列约束或者表约束。在单个列上，可以定义多个CHECK约束。【例5-24】在表BUMEN的BMID列上定义CHECK约束“CK”。2023/12/21Oracle数据库应用与开发案例教程265.4维护约束条件约束条件的定义：主键约束PRIMARYKEYPRIMARYKEY约束为主键约束，用于唯一地标识出表中的每行数据。PRIMARYKEY约束列的值不能重复，并且也不能为NULL。定义PRIMARYKEY约束时，Oracle会自动为具有PRIMARYKEY约束的列建立一个唯一索引和一个NOTNULL约束。一个表中只能有一个PRIMARYKEY约束。【例5-25】在BUMEN表的BMID列上定义主键约束“BUMEN_PK”。2023/12/21Oracle数据库应用与开发案例教程275.4维护约束条件约束条件的定义：外键约束FOREIGNKEYFOREIGNKEY约束即外键约束，用于与其他表（父表）中的列（参照列）建立连接，定义主从表之间的联系。将父表中具有PRIMARYKEY约束或UNIQUE约束的列包含在另一个表（子表）中，这些列就构成了子表的外键。定义了FOREIGNKEY约束的列中只能包含相应的、在其他表中引用的列的值，或者为NULL值。在单个列上，可以同时定义FOREIGNKEY约束和NOTNULL约束。同样可以为一列定义FOREIGNKEY约束，也可以为多列的组合定义FOREIGNKEY约束。【例5-26】在BUMEN表中，将BMID列定义为SJBM列的外键约束。2023/12/21Oracle数据库应用与开发案例教程285.4维护约束条件约束的状态1）ENABLEVALIDATE(启用验证状态）在定义或增加约束时如果不指定约束状态，则ENABLEVALIDATE为约束的默认状态。当将约束转变为此状态后，Oracle会对新、旧数据进行约束检查。这种状态要求表中所有的记录都满足约束规则。2）ENABLENOVALIDATE(启用非验证状态)当将约束转变为此状态后，Oracle会对新数据进行约束检查，但已存在的旧数据可以不满足约束规则。2023/12/21Oracle数据库应用与开发案例教程295.4维护约束条件约束的状态3）DISABLEVALIDATE(禁用验证状态）当将约束转变为此状态后，约束被禁用，不允许对表进行任何DML操作。但是表中已存在的数据仍然满足约束规则。4）DISABLENOVALIDATE(禁用非验证状态）当将约束转变为此状态后，Oracle对新、旧数据都不进行约束检查。这种状态下，表中所有的数据都可以不满足约束规则。2023/12/21Oracle数据库应用与开发案例教程305.4维护约束条件约束的状态【例5-27】对表BUMEN禁用主码。【例5-28】通过约束名称来改变约束状态。【例5-29】对BUMEN表禁用主码并将对该主码的外键引用删除。2023/12/21Oracle数据库应用与开发案例教程315.4维护约束条件添加和删除约束：添加约束为已经建立的表添加新的约束可以使用ALTERTABLE…ADD语句完成，其添加约束语法格式是：ALTERTABLEtable_nameADD[CONSTRAINTconstraint_name]constraint_type(column1,column2,…)[condition]语法说明：1）CONSTRAINT关键字：用于指定约束名，如果没有为约束指定名称，Oracle会自动为约束命名为SYS_C。2）constraint_type和condition：分别用于指定约束类型和约束条件。3）column1等：用于指定添加约束的列。2023/12/21Oracle数据库应用与开发案例教程325.4维护约束条件添加和删除约束：添加约束【例5-30】为BUMEN表添加主键约束。【例5-31】为表ZICHANMINGXI的BMID列添加外键约束：外键为BUMEN表的BMID列。【例5-32】为表BUMEN的name列添加UNIQUE约束。2023/12/21Oracle数据库应用与开发案例教程335.4维护约束条件添加和删除约束：删除约束删除约束是通过执行ALTERTABLE…DROP语句来完成的。删除约束的命令格式为：ALTERTABLEtable_nameDROP[CONSTRAINTconstraint_name];【例5-33】删除BUMEN表的BMMC列上的UNIQUE约束。【例5-34】删除BUMEN表的约束的名称CKK。【例5-35】删除BUMEN表的主键约束。2023/12/21Oracle数据库应用与开发案例教程345.4维护约束条件查看约束信息视图描述DBA_CONSTRAINTS查看数据库中所有约束信息。ALL_CONSTRAINTS查看当前用户可以访问的所有约束信息。USER_CONSTRAINTS查看当前用户的所有约束信息。DBA_CONS_COLUMNS查看数据库中所有约束对应列的信息。ALL_CONS_COLUMNS查看当前用户可以访问的所有约束的列的信息2023/12/21Oracle数据库应用与开发案例教程355.4维护约束条件查看约束信息【例5-36】通过查询视图USER_CONSTRAINTS来查看当前用户的BUMEN表的所有约束。【例5-37】通过查询视图USER_CONS_COLUMNS来查看表BUMEN的约束都定义在哪些列上。2023/12/21Oracle数据库应用与开发案例教程365.5序列和同义词创建和使用序列序列(sequence)是定义在数据字典中用于生成一个整数序列的数据库对象，用来为表中数据类型的主键提供有序的唯一值。多个用户可以共享序列中的序号。序列不占用实际的存储空间，在数据字典中只存储序列的定义描述。2023/12/21Oracle数据库应用与开发案例教程375.5序列和同义词创建和使用序列：创建序列为了在自己的方案中创建序列，要求用户必须具有CREATESEQUENCE系统权限；如果要在其它用户中创建序列，则用户必须具有CREATEANYSEQUENCE系统权限。创建序列使用CREATESEQUENCE语句完成，其语法格式是：CREATESEQUENCEsequence_name[STARTWITHn1][INCREMENTBYn2][{MAXVALUEn3｜NOMAXVALUE}][{MINVALUEn4｜NOMINVALUE}][{CACHEn5｜NOCACHE}][{CYCLE｜NOCYCLE}][ORDER];2023/12/21Oracle数据库应用与开发案例教程385.5序列和同义词创建和使用序列：创建序列语法说明：1）sequence_name：用于指定序列名。2）nl、n2、n3、n4、n5：表示整数。3）STARTWITH：用于指定序列中的序号从哪个数字开始，默认值为序列号的最小值。4）INCREMENTBY：用于指定序号的增量，序列号可以递增也可以递减。5）MAXVALUE：用来指定序列中的最大序列号，如果没有最大序列号，可用NOMAXVALUE代替。同样，MINVALUE用于指定序列中的最小序列号，其最小值必须小于或等于STARTWITH中的开始值nl。2023/12/21Oracle数据库应用与开发案例教程395.5序列和同义词创建和使用序列：创建序列语法说明：6）CACHE：用于缓冲预分配的序列号个数n5。如果不需要对预分配的序列号个数进行缓冲，则可以使用NOCACHE代替。7）CYCLE：用于使序列中的序号可以循环使用，默认为NOCYCLE。8）ORDER：用于指定按顺序生成序列号。【例5-38】SCOTT用户创建ZICHANMINGXI_SEQ序列。2023/12/21Oracle数据库应用与开发案例教程405.5序列和同义词创建和使用序列：使用序列序列提供了NEXTVAL与CURRVAL两个伪列，用来访问序列中的序号。NEXTVAL伪列返回序列生成的下一个值，而CURRVAL返回序列的当前值。在首次使用序列中的序号时，必须引用一次序列的NEXTVAL伪列，用于初始化序列的值，否则会出现错误提示。【例5-39】对序列ZICHANMINGXI_SEQ执行SELECT语句查看其当前序列值。【例5-40】对于已创建的序列ZICHANMINGXI_SEQ执行SELECT语句。【例5-41】创建序列zcseq，然后使用序列zcseq向ZICHANMINGXI表中插入几条记录，最后查询ZICHANMINGXI表。2023/12/21Oracle数据库应用与开发案例教程415.5序列和同义词创建和使用序列：修改和删除序列当需要修改序列的时候，可以使用ALTERSEQUENCE命令完成。如果用户要修改其他方案的序列，则该用户必须具有ALTERANYSEQUENCE系统权限。【例5-42】对ZICHANMINGXI_SEQ序列进行更改。【例5-43】删除ZICHANMINGXI_SEQ序列。2023/12/21Oracle数据库应用与开发案例教程425.5序列和同义词同义词同义词（synonym）是为表、索引和视图等数据库对象定义的别名，使用同义词可以简化SQL语句的书写。同义词的定义存储在数据字典中，不占用任何实际的存储空间。同义词分为以下两