《Oracle数据库应用》课件8.ppt

1、第第8 8章章 表表Oracle 数据库对象与数据类型数据库对象与数据类型1创建表创建表2更新表数据更新表数据3表的管理表的管理48.1 Oracle 8.1 Oracle 数据库对象与数据类型数据库对象与数据类型n OracleOracle数据库对象数据库对象l用户用户USERUSERl表空间表空间TABLESPACE TABLESPACE l表表TABLETABLEl约束约束CONSTRAINT CONSTRAINT l索引索引INDEXINDEXl视图视图VIEW VIEW l同义词同义词SYNONYMSYNONYMl序列序列SEQUENCESEQUENCEl函数函数FUNCTIONFU

2、NCTIONl存储过程存储过程PROCEDUREPROCEDUREl触发器触发器TRIGGERTRIGGERn OracleOracle数据类型数据类型lCHARCHAR：定长字符类型，：定长字符类型，2KB2KBlVARCHARVARCHAR(同同VARCHAR2)VARCHAR2)：可变长字符串类型，：可变长字符串类型，4KB4KBlVARCHAR2VARCHAR2：可变长字符串类型，：可变长字符串类型，4KB4KBlDATEDATE：固定长度的日期型。注意存储与使用格式：固定长度的日期型。注意存储与使用格式lTIMESTAMPTIMESTAMP：时间戳类型，可更精确的存储与表示时间：时间

3、戳类型，可更精确的存储与表示时间lNUMBERNUMBER：数字型，可存放实型和整型：数字型，可存放实型和整型lintint：NUMBERNUMBER的子类型的子类型lLONGLONG：可变长字符类型，：可变长字符类型，2GB2GBlRAWRAW：可变长二进制数据类型，：可变长二进制数据类型，4KB4KBlLONG RAWLONG RAW：可变长二进制数据类型，：可变长二进制数据类型，2GB2GBlBLOBBLOB：二进制大对象类型，：二进制大对象类型，4GB4GBlCLOBCLOB：字符串大对象类型，：字符串大对象类型，4GB4GBl数据库中的每一条记录有唯一的数据库中的每一条记录有唯一的R

4、OWIDROWID值，值，4 4段段1818个字个字符，表示记录的具体位置，又称为记录的物理地址。符，表示记录的具体位置，又称为记录的物理地址。8.2 8.2 创建表创建表n 前提：用户应获得前提：用户应获得CREATE TABLECREATE TABLE系统特权系统特权n 表命名规则表命名规则l以字母开头，长度为以字母开头，长度为1-301-30个字符，可使用个字符，可使用A-ZA-Z，a-za-z，0-90-9，_ _，$和和#字符字符l不能与已存在的当前用户的其他数据库对象同名不能与已存在的当前用户的其他数据库对象同名l不能用不能用OracleOracle的保留字作为表名的保留字作为表名

5、l表名不分大小写表名不分大小写l可用可用SYSDATESYSDATE、USERUSER等作为列的缺省值等作为列的缺省值n 创建表的语法图创建表的语法图l语法：语法：CREATE GLOBAL TEMPORARY TABLE schema.tableCREATE GLOBAL TEMPORARY TABLE schema.table(relational_properties)(relational_properties)ON COMMIT DELETE|PRESERVE ROWSON COMMIT DELETE|PRESERVE ROWSphysical_propertiesphysical_

6、propertiestable_propertiestable_properties;l其语法图：其语法图：其中可指定其物理存储特性，如分配磁盘空间，保留多少其中可指定其物理存储特性，如分配磁盘空间，保留多少空闲空间等空闲空间等因此，因此，OracleOracle数据库的表定义功能比较强大，可满足某些数据库的表定义功能比较强大，可满足某些特定的需要特定的需要n 1.1.最简单的创建关系表的方法最简单的创建关系表的方法l基本关系表：采用传统方法存储的二维关系表基本关系表：采用传统方法存储的二维关系表l建立的表使用系统默认的参数建立的表使用系统默认的参数l不指定物理存储特性，也不指定分区属性不指定

7、物理存储特性，也不指定分区属性l语法：语法：CREATE TABLE CREATE TABLE 表名表名 (列名列名 类型类型 NULL|NOT NULL PRIMARY KEYNULL|NOT NULL PRIMARY KEY，列名列名 类型类型 NULL|NOT NULLNULL|NOT NULL ，表级完整性约束，表级完整性约束 )；l操作用户需要有操作用户需要有在相应表空间中创建对象的权限在相应表空间中创建对象的权限 例：创建例：创建student/course/scstudent/course/sc表（第表（第3 3章）章）n 2.2.利用带存储参数的方式创建表利用带存储参数的方式创

8、建表l语法：语法：CREATE TABLE schema.tableCREATE TABLE schema.table(COLUMN1 DATAYPEDEFAULT EXPRL(COLUMN1 DATAYPEDEFAULT EXPRLCOLUMN_CONSTRAINT|TABLE_CONSTRAINTCOLUMN_CONSTRAINT|TABLE_CONSTRAINT,COLUMN2 DATAYPEDEFAULT EXPR2,COLUMN2 DATAYPEDEFAULT EXPR2COLUMN_CONSTRAINT|TABLE_CONSTRAINT.)COLUMN_CONSTRAINT|TAB

9、LE_CONSTRAINT.)STORAGE(STORAGE(INITIAL nINITIAL nNEXT nNEXT nMINEXTENTS nMINEXTENTS nMAXEXTENTS nMAXEXTENTS nPCTINCREASE n)PCTINCREASE n)TABLESPACE tablespaceTABLESPACE tablespacePCTFREE nPCTFREE nPCTUSED nPCTUSED nINITRANS nINITRANS nMAXTRANS nMAXTRANS nAS subqueryAS subquery;例：创建例：创建empemp表表n 3.3.

10、利用子查询创建表利用子查询创建表l语法：语法：CREATE TABLE table CREATE TABLE table(column,column.)(column,column.)AS subqueryAS subquery;例：利用子查询创建例：利用子查询创建emp_infoemp_info表表l使用时的限制条件：使用时的限制条件：可关联多个表以及用集合函数生成新表，注意选择出来的字可关联多个表以及用集合函数生成新表，注意选择出来的字段不能重复段不能重复只有非空只有非空NOT NULLNOT NULL的约束条件能继承过来，其它的约束条件的约束条件能继承过来，其它的约束条件和默认值都不能继

11、承和默认值都不能继承不能够为新表指定表空间不能够为新表指定表空间某些数据类型的数据不能够导入，如带有大对象数据类型或某些数据类型的数据不能够导入，如带有大对象数据类型或LongLong数据类型的数据数据类型的数据l使用技巧：使用技巧：利用查询来创建表结构，不导入数据利用查询来创建表结构，不导入数据w 有时候需要复制表的结构，而不需要复制数据。则先利用有时候需要复制表的结构，而不需要复制数据。则先利用SelectSelect语句将相关的列等结构查询出来，然后在语句将相关的列等结构查询出来，然后在WhereWhere中，设置中，设置一些根本不存在的条件。一些根本不存在的条件。n 4.4.完整性约束

12、完整性约束l约束的类型约束的类型NOT NULLNOT NULL：非空约束：非空约束UNIQUEUNIQUE：唯一性约束，指定某列值不能重复：唯一性约束，指定某列值不能重复PRIMARY KEYPRIMARY KEY：主键约束：主键约束FOREIGN KEYFOREIGN KEY：外键约束，也叫参照完整性约束：外键约束，也叫参照完整性约束CHECKCHECK：检查约束，用于限制该列的取值范围：检查约束，用于限制该列的取值范围REFREF：定义列对象的参照关系：定义列对象的参照关系l完整性约束需注意以下几点：完整性约束需注意以下几点：外键外键(参照完整性参照完整性)仅依赖于表间或表内的数据关系，

13、而不依仅依赖于表间或表内的数据关系，而不依赖于物理存储或指针，是纯逻辑上的关系赖于物理存储或指针，是纯逻辑上的关系可在表定义时进行完整性约束定义，或在表定义后使用可在表定义时进行完整性约束定义，或在表定义后使用ALTER ALTER TABLETABLE语句定义语句定义可以启用或禁用某个完整性约束可以启用或禁用某个完整性约束列的缺省值列的缺省值defaultdefault子句不属于约束子句不属于约束数据字典数据字典USER_CONSTRAINTSUSER_CONSTRAINTS和和USER_CONS_COLUMNSUSER_CONS_COLUMNS记载了在哪记载了在哪些列上定义了完整性约束，约

14、束类型列的含义：些列上定义了完整性约束，约束类型列的含义：w C C：检查约束，包括：检查约束，包括CHECKCHECK和和NOT NULLNOT NULLw P P：主键约束：主键约束(PRIMARY KEY)(PRIMARY KEY)w R R：参照完整性约束：参照完整性约束(REFERENCES)(REFERENCES)w U U：唯一性约束：唯一性约束(UNIQUE)(UNIQUE)l完整性完整性约约束的创建束的创建使使用用create tablecreate table时创建时创建使使用用alter tablealter table时创建时创建例例题：题：P132P1328.3 8.

15、3 更新表数据更新表数据n OracleOracle的的DMLDML：lINSERTINSERTlUPDATEUPDATElDELETE/DELETE/TRUNCATETRUNCATElMERGEMERGEn 1.INSERT1.INSERT语句语句l使用使用VALUESVALUES子句的子句的INSERTINSERT语句语句语法：语法：INSERT INTO table (column_1,column_2,.)INSERT INTO table (column_1,column_2,.)VALUES(sql_expression_1 ,sql_expression_2,.);VALUES(

16、sql_expression_1 ,sql_expression_2,.);一次只能向表中插入一条记录一次只能向表中插入一条记录INSERT INTOINSERT INTO子句的子句的tabletable后可省略列名表后可省略列名表建议使用列名表明确要往哪些列插入数据建议使用列名表明确要往哪些列插入数据ValuesValues子句中，字符型、日期型数据要用单引号子句中，字符型、日期型数据要用单引号可在列值处使用关键字可在列值处使用关键字DAFAULTDAFAULT表示该列取缺省值表示该列取缺省值插入空值插入空值w 在在INSERT INTOINSERT INTO子句的子句的tabletable

17、后不指定该列名，默认情况下该后不指定该列名，默认情况下该列取空值列取空值w 在在VALUESVALUES子句中使用子句中使用NULLNULL作为列的值作为列的值l使用子查询的使用子查询的INSERTINSERT语句语句语法：语法：INSERT INTO INSERT INTO (,.),.)SELECT SELECT,.,.FROM FROM WHERE WHERE 条件条件;SELECTSELECT子句中列的数量和列类型应与子句中列的数量和列类型应与INTOINTO子句中指定的列数量子句中指定的列数量和列类型一致和列类型一致例题例题：P134P134n 2.UPDATE2.UPDATE语句语

18、句l语法语法UPDATE tableUPDATE tableSET column=value ,column=value,.SET column=value ,column=value,.WHERE condition;WHERE condition;l使用子查询从其他表获取数据作为列的新值使用子查询从其他表获取数据作为列的新值UPDATE UPDATE SET(SET(,.)=,.)=(SELECT SELECT,.,.FROM FROM WHERE WHERE )WHERE WHERE (SELECT SELECT FROM FROM WHERE WHERE );SETSET子句中的子句中

19、的SELECTSELECT子句只能返回一行数据子句只能返回一行数据例题：例题：P135P135n 3.3.DELETE/DELETE/TRUNCATETRUNCATE语句语句lDELETEDELETE用于删除表中的数据用于删除表中的数据语法：语法：DELETE FROM table DELETE FROM table WHERE condition;WHERE condition;lTRUNCATETRUNCATE用于清空表中所有数据，并释放表的存储用于清空表中所有数据，并释放表的存储空间空间语法：语法：TRUNCATE TABLE table;TRUNCATE TABLE table;例题：

20、例题：P136P136n 4.MERGE4.MERGE语句语句lMERGEMERGE用于合并用于合并UPDATEUPDATE和和INSERTINSERT语句语句根据联合查询对另一个表进行查询，条件匹配的进行根据联合查询对另一个表进行查询，条件匹配的进行UPDATEUPDATE，不匹配的进行，不匹配的进行INSERTINSERT是一条一定会执行的语句是一条一定会执行的语句MERGER=UPDATE+INSERTMERGER=UPDATE+INSERT语法：语法：MERGE INTO MERGE INTO tabletable USINGUSING table|view|subquery tabl

21、e|view|subquery ONON expression expression WHEN MATCHED THEN WHEN MATCHED THEN UPDATE SET clauseUPDATE SET clause WHEN NOT MATCHED THEN WHEN NOT MATCHED THEN INSERT VALUES clause;INSERT VALUES clause;例题：例题：P137P137n 4.4.事务控制事务控制l一系列操作组成的工作单元，该单元内的操作不可一系列操作组成的工作单元，该单元内的操作不可分割，即所有操作要么都做，要么都不做分割，即所有操作要

22、么都做，要么都不做l事务的事务的ACIDACID特性，缺一不可：特性，缺一不可：原子性原子性AtomicityAtomicityw 要么都发生，要么都不发生要么都发生，要么都不发生一致性一致性ConsistencyConsistencyw 数据应该不被破坏数据应该不被破坏隔离性隔离性IsolationIsolationw 用户间操作不相混淆用户间操作不相混淆持久性持久性DurabilityDurabilityw 永久保存永久保存l事务控制的命令事务控制的命令COMMITCOMMITw 事务提交语句事务提交语句w 让已提交的让已提交的INSERT/UPDATE/DELETEINSERT/UPDA

23、TE/DELETE生效，不能被回滚生效，不能被回滚ROLLBACKROLLBACKw 事务回滚语句事务回滚语句w 撤销未提交的事务所做的修改撤销未提交的事务所做的修改SAVEPOINTSAVEPOINTw 保存点，标记事务中的某个点，以便将来可以回滚到该保存点保存点，标记事务中的某个点，以便将来可以回滚到该保存点w 提高系统性能，减少回滚时间提高系统性能，减少回滚时间例题：例题：P138P138n 表的管理和维护操作表的管理和维护操作l修改列定义修改列定义l增加列增加列l删除列删除列l约束维护约束维护l修改存储参数修改存储参数l重命名表和列重命名表和列lALTER TABLEALTER TAB

24、LE语句语句8.4 8.4 表的管理表的管理n 1 1.存储参数存储参数l在创建表之前，应考虑好如下几个问题：在创建表之前，应考虑好如下几个问题：规范表的各字段类型规范表的各字段类型确定各字段的取值范围确定各字段的取值范围确定是否可以节省空间确定是否可以节省空间相关的表是否适合建立簇相关的表是否适合建立簇确定数据块的空间分配确定数据块的空间分配lPCTFREEPCTFREE和和PCTUSEDPCTUSED：对空间利用率有较大影响对空间利用率有较大影响lINITRANSINITRANS、MAXTRANSMAXTRANS：对系统性能有较大影响对系统性能有较大影响l分散存储是分散存储是OracleO

25、racle数据库系统物理设计的灵魂数据库系统物理设计的灵魂l修改的存储参数不会立刻显现出效果修改的存储参数不会立刻显现出效果lALTER TABLE ALTER TABLE MOVE MOVE：将表从一个表空间迁移到另一将表从一个表空间迁移到另一个表空间个表空间例：将例：将STU01STU01用户的表用户的表stustu从从USERSUSERS表空间迁移到表空间迁移到ts1ts1表空间。表空间。conn/orcl as sysdbaconn/orcl as sysdbaalter user stu01 quota unlimited on ts1;alter user stu01 quota

26、unlimited on ts1;conn stu01/stu01pwdorclconn stu01/stu01pwdorclalter table stu move tablespace ts1alter table stu move tablespace ts1storage(initial 10k next 20k minextents 1 pctincrease 0);storage(initial 10k next 20k minextents 1 pctincrease 0);n 2.2.表结构维护表结构维护l修改列定义修改列定义列的类型列的类型宽度宽度缺省值定义缺省值定义l对有数

27、据的列，修改后列宽度不能小于数据的长度对有数据的列，修改后列宽度不能小于数据的长度l一个表只能有一个一个表只能有一个LONGLONG型型(可变长字符型可变长字符型)的列，不能的列，不能增加或修改出第二个增加或修改出第二个LONGLONG型的列型的列l修改列定义的语法：修改列定义的语法：ALTER TABLE tableALTER TABLE tableMODIFY(column datatype DEFAULT expr,.);MODIFY(column datatype DEFAULT expr,.);l增加列增加列新增加的列总是排在列定义的最后新增加的列总是排在列定义的最后对有记录的行增加

28、新列，新列默认取空值对有记录的行增加新列，新列默认取空值增加列语法：增加列语法：ALTER TABLE table ALTER TABLE table ADD(column datatype DEFAULT expr,.);ADD(column datatype DEFAULT expr,.);l删除列删除列一次只能删除一列一次只能删除一列列删除后不能恢复，只能重新建立列删除后不能恢复，只能重新建立删除列语法删除列语法:ALTER TABLE table DROP(column);ALTER TABLE table DROP(column);l重命名表和列重命名表和列只有表的拥有者才可以对表重

29、命名只有表的拥有者才可以对表重命名重命名表语法重命名表语法ALTER TABLE tablename RENAME TO new_tablename;ALTER TABLE tablename RENAME TO new_tablename;或或 RENAME old_tablename TO new_tablename;RENAME old_tablename TO new_tablename;重命名列语法重命名列语法ALTER TABLE tablenameALTER TABLE tablenameRENAME COLUMN old_name TO new_name;RENAME COLU

30、MN old_name TO new_name;l表的删除表的删除删除语法：删除语法：DROP TABLE table CASCADE CONSTRAINTS DROP TABLE table CASCADE CONSTRAINTS；表被删除后，与被删除表相关的索引一起被删除表被删除后，与被删除表相关的索引一起被删除与被删除表相关的视图、同义词、函数等仍然存在，但不可与被删除表相关的视图、同义词、函数等仍然存在，但不可用用 例题：例题：P139P139n OracleOracle常用数据类型常用数据类型n OracleOracle表的创建和修改表的创建和修改n 数据完整性的维护数据完整性的维护n 表中数据的插入、修改、删除和合并表中数据的插入、修改、删除和合并n 事务控制事务控制n 表管理的常用操作表管理的常用操作本章小结本章小结