第6章Oracle数据库基本操作之二

1、第六章 oracle数据库基本操作 本章内容： Oracle数据库的数据类型 Sql /plus环境 Oracle数据库基本操作 建立、删除、修改基本表 建立、删除、修改索引 建立、删除、修改视图本节内容 预备知识1：建表之前必须明确用户和模式的关系； 预备知识2：建表之前必须清晰管理表的策略 ； Oracle数据库基本操作 建立、删除、修改基本表 建立、删除、修改索引 建立、删除、修改视图预备知识1：用户和模式的关系 Oracle9i数据中用方案（schema）来对逻辑对象进行管理。不同的数据库对象具有各自的特点和用途。 方案或模式、模式对象、用户的定义区别： A schema ：a col

2、lection of database objects (used by a user.). 被用户使用的数据库对象的集合。 Schema objects ：the logical structures that directly refer to the databases data.指向数据库数据的逻辑结构集。 A user： a name defined in the database that can connect to and access objects.在数据库中为了能够连接和访问对象而定义的名字。 schema为数据库对象的集合，为了区分各个集合，需要给这个集合起个名字，即方

3、案下看到的许多类似用户名的节点 这些类似用户名的节点其实就是一个schema schema里面包含了各种对象 tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links。 一个用户一般对应一个schema,该用户的schema名等于用户名，并作为该用户缺省schema。 Oracle数据库中不能新创建一个schema，要想创建一个schema，只能通过创建一个用户的方法解决 在创建一个用户的同时为这个用户创建一个与用户名同名的schem并作为该用户的缺省shcema。

4、schema的个数同user的个数相同，而且schema名字同user名字一一 对应并且相同(别名，不准确)。 一个用户还可以使用其他的schema。 select * from emp; 完整写法为select * from scott.emp。 schema.object，而不属user.object。举个例子： schema作为user的别名的依据 SQL Gruant dba to scottSQL create table test(name char(10);Table created.SQL create table system.test(name char(10);Table

5、 created.SQL insert into test values(scott); 1 row created.SQL insert into system.test values(system);1 row created.SQL commit;Commit complete. SQL conn system/managerConnected.SQL select * from test;NAME-systemSQL ALTER SESSION SET CURRENT_SCHEMA = scott; -改变用户缺省schema名Session altered. SQL select *

6、 from test;NAME-scottSQL select owner ,table_name from dba_tables where table_name=upper(test);OWNER TABLE_NAME- -SCOTT TESTSYSTEM TEST-在使用上，shcema与user完全一样。 模式（方案、schema）的建立。 是否用户一建立模式同时建立？ 必须建立对象才能在模式中显示相应的用户名。以system用户登录创建用户net1并以之登录创建表空间netspace在netspace表空间下创建表nettable预备知识2：管理表的策略 1、设计表 开发数据库应用系

7、统时，数据库管理员负责规划和建立表空间，而规划和建立表则往往是由应用开发人员完成的。 应用开发人员设计表时应考虑以下因素: 在为表、列、索引的视图、同义词、触发器等与表有关的对象命名时，应该使用权有意义的名称，其格式要有利于区分这些对象。 为了给用户和其它人员提供有意义的帮助信息,应该使用权Comment命令描述表、列的作用.预备知识2：管理表的策略 设计表时,应该使用权范式来规划每个表.范式分为第一范式 第二范式 第三范式 范式越高,表的设计越规范. 定义表列时,应该选择合适的数据类型和长度,为了节省存储空间,应该将可以取Null值的列放在后面. 确定表中所需的完整性约束。 在合适的情况下,

8、使用簇表以节省存储空间并提高特定类型的SQL语句的性能.预备知识2：管理表的策略 2、规划表的类型 在Oracle数据库中,存储用户数据可以使用标准表 索引表 簇表 和分区表. 标准表:数据按堆组织，以无序方式存放在单独表的表段中.这种表是最基础最常用的数据库表类型,是默认情况下创建的表. 索引表:数据是以”B-树”结构存放在主键约束所对应的索引段中.使用索引表时,索引叶子节点不仅包含了键列的数据还包含了其他非键列的数据。 簇表:簇由共享相同数据块的一组表组成.在合适的情况下,使用簇表可以节省存储空间并提高SQL语句的性能. 分区表:数据被划分为更小的部分,并且存储到相应的分区段中，每个分构段

9、可以独立管理和操作.预备知识2：管理表的策略 3、规划表的位置 如果要在特定表空间上建表,用户必须在该表空间中具有相应的配额或具有Unlimited Tablespace的系统权限 如果建表时不指定TableSpace选项,Oracle会将表建立在用户的默认表空间中.通过指定TableSpace选项,可以将表建立在其他表空间中. 当规划表的存放位置时,应注意以下事项:System表空间中应该只包含数据字典和Oracle系统对象 . 如果将表建立在System表空间,将会影响数据库性能. 建议DBA给每个用户指定其它的默认表空间.设计应用系统时,应该规划存放应用系统的表的表空间,不要随意分散地创

10、建到不同的表空间中,这样会使执行某些数据库管理和维护操作. 使用NoLogging选项建表在创建表的过程中指定nologging子句，避免在创建表过程中产生过多的重做记录。nologging子句适合时在创建大表时使用. 使用ComPress选项建表使用Oracle的表压缩特征(Compress)时, Oracle会将同一个数据块行和列的重复数据放在数据块的头部,压缩了表数据。 4、创建大表时的建议 将表和它的索引分享存放 大表所对应的索引通常比较大,将表和它的索引分别存放在不同的表空间中，这样的重建索引的过程中能够更加容易地获取所需的连续存储空间,并且可以提高查询时的I/O效率. 分配足够的临

11、时空间 如果如果应用程序员在访问大表时需要执行规模较大的排序操作,就必须在数据库中具有一个较大的临时段,临时段的存储参数设置取决于临表空间的默认存储参数设置,即Storage子句中的各种参数的设置 5、预计和规划表的大小可以对表的大小进行估计,包括对索引 回退段和重做日志文件大小的估计,以决定支持期望的数据库所需的磁盘空间大小.通过这些估计,就可以购买合适的硬件和作其它决定6.3创建基本表 1、创建基本表 基本表是数据库最基本的对象，是实际存放数据的地方。以system登录“创建表创建表”对话框包含了七个选项卡对话框包含了七个选项卡 “一般信息一般信息”选项卡，选项卡， “名称名称”文本框：输

12、入新建表的名称，表名在数文本框：输入新建表的名称，表名在数据库的同一方案中是据库的同一方案中是唯一唯一的。表名最长的。表名最长30个字个字符符，以字母开始，后由数字、下划线、，以字母开始，后由数字、下划线、#、$等组成。本例名为等组成。本例名为customer。 “方案方案” 本例取默认值（本例取默认值（SYSTEM）。）。 “表空间表空间” 本例取本例取user。 “表表”单选按钮：单选按钮： “标准标准” 表示建立常规表，表的一列或多列使用一个表示建立常规表，表的一列或多列使用一个索引，为表和索引保留索引，为表和索引保留两个独立的存储空间两个独立的存储空间； “按索引组织的表按索引组织的表

13、” 表示建立表示建立非常规表非常规表，表的数据保表的数据保存在该表的索引中存在该表的索引中，更改表中的数据（如插入、删，更改表中的数据（如插入、删除或修改行等）将使索引更新。本例取默认值（标除或修改行等）将使索引更新。本例取默认值（标准）。准）。创建数据表customer的一般信息使用可编辑的文本区域来创建基于当前表的使用可编辑的文本区域来创建基于当前表的PL/SQL查询语句。查询语句。 创建使用用户定义的对象类型的对象表创建使用用户定义的对象类型的对象表 表示可以创建XML类型的表 “约束条件约束条件”选项卡选项卡，如下页图所示。该选项卡可以使用可编辑，如下页图所示。该选项卡可以使用可编辑的

14、电子表格编辑表的完整性约束条件。完的电子表格编辑表的完整性约束条件。完整性约束条件是一种规则，不占用任何数整性约束条件是一种规则，不占用任何数据库空间，其定义存储在数据字典中，在据库空间，其定义存储在数据字典中，在执行执行 PL/SQL期间使用。该电子表格由以期间使用。该电子表格由以下各列构成：下各列构成：“约束条件约束条件”选项卡选项卡 “名称名称”：要定义的完整性约束条件名，它在数据库中应是：要定义的完整性约束条件名，它在数据库中应是唯一的。本例定义了一个完整性约束条件：唯一的。本例定义了一个完整性约束条件：pk_costomer_1（PRIMARY）-cid为主键为主键 另外举两例另外举

15、两例 xingbie（CHECK）-SSEX IN（男男，女女），）， nianling（CHECK）-SAGE BETWEEN 18 AND 24。 “类型类型”：约束条件类型，下拉列表中显示了可用的约束条：约束条件类型，下拉列表中显示了可用的约束条件类型有件类型有UNIQUE、PRIMARY、FOREIGN、CHECK。本例选择类型为本例选择类型为PRIMARY，表示，表示pk_costomer_1是一是一个类型为主键的完整性约束条件。个类型为主键的完整性约束条件。 “是否禁用是否禁用”：表示创建约束条件时是禁用还是启用该约束：表示创建约束条件时是禁用还是启用该约束条件。条件。 “引用方案

16、引用方案”：为约束条件中的：为约束条件中的FOREIGN（外键）引用。（外键）引用。数据表customer的约束条件“簇列簇列”选项卡选项卡 如果选中如果选中“将表定义为簇的一部分将表定义为簇的一部分”复选复选框，则表示将表作为簇的一部分。选中后框，则表示将表作为簇的一部分。选中后该选项卡的所有选项都可以操作：该选项卡的所有选项都可以操作： 簇的的信息显示在一个包含两列的列表框簇的的信息显示在一个包含两列的列表框中，描述如下中，描述如下 “表列表列”：显示了当前表所有已定义的列。：显示了当前表所有已定义的列。 “簇列簇列”：显示了包含在已选簇内的簇关键字：显示了包含在已选簇内的簇关键字列。列。

17、 本例皆取系统默认值。本例皆取系统默认值。数据表customer的簇列“分区分区”选项卡选项卡 该选项卡的该选项卡的“范围分区范围分区”包含如下设置：包含如下设置： “分区名分区名”：显示分区的名称，但不能编辑该值。：显示分区的名称，但不能编辑该值。 “表空间表空间”：显示存储分区的表空间，但也不能编：显示存储分区的表空间，但也不能编辑该值。辑该值。 该选项卡中包括如下按钮：该选项卡中包括如下按钮： “编辑编辑”：允许对分区进行编辑。选择一行后该按钮即：允许对分区进行编辑。选择一行后该按钮即被启用。被启用。 “添加添加”：允许添加分区。选择最后一行后，该按钮可：允许添加分区。选择最后一行后，该

18、按钮可被启用。被启用。 “删除删除”：允许删除分区。选择一行后，该按钮即被启：允许删除分区。选择一行后，该按钮即被启用。用。 本例皆取系统默认值。本例皆取系统默认值。 数据表customer的分区“存储存储”选项卡选项卡 “存储存储”选项卡，可以定义表的存储方式选项卡，可以定义表的存储方式 在在“区区”选项组内，可以指定下列参数的值：选项组内，可以指定下列参数的值： “初始大小初始大小”：指定表的第一个分区的大小。可以输入：指定表的第一个分区的大小。可以输入一个值，但至少为一个数据块的大小，默认值为一个值，但至少为一个数据块的大小，默认值为64KB。 “下一个大小下一个大小”：指定表的第二个分

19、区的大小。可以输：指定表的第二个分区的大小。可以输入一个值，但至少为一个数据块的大小，默认值为入一个值，但至少为一个数据块的大小，默认值为64KB。 “增量增量”：指定从第三区开始相对于上一个区的增长百：指定从第三区开始相对于上一个区的增长百分比。分比。 “最小数量最小数量”：指定分配给表的最小区数。默认值为：指定分配给表的最小区数。默认值为1。 “最大数量最大数量”：指定分配给表的最大区数。可以输入一：指定分配给表的最大区数。可以输入一个值，也可以是无限制。个值，也可以是无限制。 在在“空间利用率空间利用率”选项组内，可以指定下列参数选项组内，可以指定下列参数的值：的值： “保留用于更新的空

20、闲空间的百分比保留用于更新的空闲空间的百分比”：指定为以后更：指定为以后更新表而保留的空间的百分比，可以输入新表而保留的空间的百分比，可以输入099之间值，之间值，默认为默认为10。 “行插入备选对象的使用空间百分比阈值行插入备选对象的使用空间百分比阈值”：指定为该：指定为该表数据块保留的已用空间的最小百分比，可以输入表数据块保留的已用空间的最小百分比，可以输入099之间的值，默认值为之间的值，默认值为40。 在在“事务处理数量事务处理数量”选项组内，可以指定下列参数的值：选项组内，可以指定下列参数的值： “初始值初始值”：指定该表每个数据块中分配的初始并行处理事务项数，：指定该表每个数据块中

21、分配的初始并行处理事务项数，可以输入可以输入1255之间的值。之间的值。 “最大值最大值”：指定可同时修改表的数据块的最大并行处理事务项：指定可同时修改表的数据块的最大并行处理事务项数，可以输入数，可以输入1255之间的值。之间的值。 在在“空闲列表空闲列表”选项组内，可以指定下列参数的值选项组内，可以指定下列参数的值 “空闲列表空闲列表”：指定表、簇或索引的每个空闲列表组的数量。可：指定表、簇或索引的每个空闲列表组的数量。可以输入一个值，默认值为以输入一个值，默认值为1。 “组组”：指定表、簇或索引的每个空闲列表组的数量。可以输入一：指定表、簇或索引的每个空闲列表组的数量。可以输入一个值，默

22、认值为个值，默认值为1。 在在“缓冲池缓冲池”下拉列表中可以指定默认缓冲池：下拉列表中可以指定默认缓冲池： “KEEP”：表示保留内存中的方案对象以避免：表示保留内存中的方案对象以避免I/O操作。操作。 “RECYCLE”：表示一旦不需要数据块时，立即将它们从内存中清：表示一旦不需要数据块时，立即将它们从内存中清除，以防止对象占用不必要的高速缓存空间。除，以防止对象占用不必要的高速缓存空间。 “DEFAULT”：表示默认缓冲池为高速缓冲存储器，所有对象块：表示默认缓冲池为高速缓冲存储器，所有对象块均存储在指定的高速缓存中。均存储在指定的高速缓存中。“选项选项”选项卡选项卡该选项卡可以定义表的并

23、发操作、是否覆盖和如何审计等特性。该选项卡可以定义表的并发操作、是否覆盖和如何审计等特性。 如果选中如果选中“并行并行”复选框，则表示以并行方式装载，复选框，则表示以并行方式装载，并行执行某种操作。并行执行某种操作。 “程度程度”表示单个例程的表示单个例程的操作并行度操作并行度，即使用的，即使用的查查询服务器询服务器的数量，可以指定默认值（根据的数量，可以指定默认值（根据CPU数数量和存储要求并行扫描的表计算得到），也可以量和存储要求并行扫描的表计算得到），也可以输入一个值。输入一个值。 如果选中如果选中“覆盖默认事件记录值覆盖默认事件记录值”复选框，表示启复选框，表示启用事件记录，用事件记录

24、，将创建重做日志将创建重做日志。该操作比不启用。该操作比不启用事件记录所用时间长，但可以在故障时恢复更新。事件记录所用时间长，但可以在故障时恢复更新。 如果选中如果选中“将经常访问的数据放在缓冲区高速缓存将经常访问的数据放在缓冲区高速缓存的顶端（的顶端（CACHE）”复选框，表示某个用户进程复选框，表示某个用户进程首次需要某一特定数据时，将在数据库的缓冲区首次需要某一特定数据时，将在数据库的缓冲区高速缓存中搜索该数据。高速缓存中搜索该数据。 如果选中如果选中“监视监视收集表上的修改统计信息收集表上的修改统计信息”复选复选框，表示用户希望收集该表的修改统计信息。框，表示用户希望收集该表的修改统计

25、信息。“约束条件存储约束条件存储”选项卡选项卡“约束条件存储约束条件存储”选项卡，如图所示。该选项卡可以为不同的约束条选项卡，如图所示。该选项卡可以为不同的约束条件选择不同的存储方式。件选择不同的存储方式。 “约束条件名称约束条件名称”下拉列表：可以选择不同的约束下拉列表：可以选择不同的约束条件，以便为不同的约束条件指定不同的存储方条件，以便为不同的约束条件指定不同的存储方式式 “表空间表空间”下拉列表：可以为约束条件的存储选择下拉列表：可以为约束条件的存储选择指定表空间。指定表空间。 如果选中如果选中“覆盖默认事件记录值覆盖默认事件记录值”，表示在数据段，表示在数据段中可以覆盖默认的事件记录

26、值。如果选中中可以覆盖默认的事件记录值。如果选中“生成生成CONSTRAINT数据页的完整重做数据页的完整重做 （LOGGING）”复选框，表示在数据段上为约束复选框，表示在数据段上为约束生成完整的重做日志。生成完整的重做日志。 “创建创建”按钮，则开始执行表的创建操作。按钮，则开始执行表的创建操作。“查看查看/编辑目录编辑目录”可以输入数可以输入数据据手工创建表手工创建表CREATE TABLE 用户用户.表名表名 (列名列名 数据类型数据类型 列级完整性约束条件列级完整性约束条件 ,列名数据类型列名数据类型 列级完整性约束条件列级完整性约束条件 ,表级完整性约束条件表级完整性约束条件) P

27、CTFREE 整数整数 INITRANS 整数整数 MAXTRANS 整整数数 TABLESPACE 表空间名表空间名 DEFAULT STORAGE （INITIAL整数整数K|M NEXT整数整数K|M MINEXTENTS 整数整数 MAXEXTENTS 整数整数|UNLIMITED PCTINCREASE 整数整数)CLUSTER 簇名簇名(簇列簇列,) PARALLEL CACHE|NOCACHE LOGGING|NOLOGGING AS 子查询子查询; 上面语法中各参数描述如下：上面语法中各参数描述如下： 约束条件分为列级和表级：约束条件分为列级和表级：前者针对某列，后者针对前者针

28、对某列，后者针对整个表。整个表。 PCTFREE：指定每一块预留的自由空间百分比，默认：指定每一块预留的自由空间百分比，默认值为值为10。即向表中插入新数据行时，该表的每个块都。即向表中插入新数据行时，该表的每个块都只能使用只能使用90%的空间，的空间，10%预留出来供修改该块中预留出来供修改该块中数据行增大空间时使用。数据行增大空间时使用。 INITRANS：指定该表每个数据块中分配的初始并行：指定该表每个数据块中分配的初始并行处理事务项数，范围是处理事务项数，范围是1255。 MAXTRANS：指定可同时修改表的数据块的最大并行：指定可同时修改表的数据块的最大并行处理事务项数，范围是处理事

29、务项数，范围是1255。 TABLESPACE：指定该表所放置：指定该表所放置的表空间的表空间 DEFAULT STORAGE：指定该表的存储方式，具体含：指定该表的存储方式，具体含义同创建表空间语句中的存储子句。义同创建表空间语句中的存储子句。 CLUSTER：指定该表放置在聚簇中。：指定该表放置在聚簇中。 PARALLEL：指定加速该表的扫描可以使用的并：指定加速该表的扫描可以使用的并行查询进程个数。行查询进程个数。 CACHE|NOCACHE：指定是否将该表中的数据：指定是否将该表中的数据放在放在CACHE（当该表经常被存取时使用）。（当该表经常被存取时使用）。 LOGGING|NOLO

30、GGING：前者指定表的创建：前者指定表的创建操作及之后对表的所有操作都记录在重做日志文操作及之后对表的所有操作都记录在重做日志文件中，是默认选项；后者指定表的创建操作和其件中，是默认选项；后者指定表的创建操作和其它操作不记录在重做日志文件中。它操作不记录在重做日志文件中。 AS 子查询子查询：基于一个或多个已存在的表建立新：基于一个或多个已存在的表建立新表，新表列的数据类型和大小、新表中的数据行表，新表列的数据类型和大小、新表中的数据行都由查询结果决定。都由查询结果决定。 如如CREATE TABLE XSDA AS SELECT SNO,SNAME,SSEX,SAGE FROM STUDE

31、NT WHERE SCLASS= 计算机科学与技术计算机科学与技术041 。 上机增加的内容： 1、利用sql语句手工建立customer表（可选）。 2、利用sql语句或理由企业管理控制台来创建最经典的studentcoursescore这三个数据表。尤其注意其中的关系如何建立？（可选）查看、编辑表：查看、编辑表：有两种方式：有两种方式： 1使用使用Oracle企业管理器查看、编辑表企业管理器查看、编辑表 启动启动Oracle企业管理器，以企业管理器，以system身份连接数据库，身份连接数据库，展开展开“方案方案”“SYSTEM”“表表”节点，即可查看节点，即可查看SYSTEM方案中的所有

32、表；右击欲编辑的表，从快捷方案中的所有表；右击欲编辑的表，从快捷菜单中单击菜单中单击“查看查看/编辑详细资料编辑详细资料”即弹出编辑表的即弹出编辑表的对话框可对表进行编辑。对话框可对表进行编辑。 2手工查看、编辑表手工查看、编辑表 手工查看表：手工查看表：Oracle9i提供了若干个视图，用于查提供了若干个视图，用于查询有关表的信息。这些视图的名称及说明如下页表所询有关表的信息。这些视图的名称及说明如下页表所示。示。 从从DBA_TABLES视图中查询所有表的信息，以下脚本视图中查询所有表的信息，以下脚本运行结果如图所示。运行结果如图所示。 select table_name,tablespa

33、ce_name from dba_tables; 图图6.16使用使用DBA_TABLES视图查看表信息视图查看表信息 手工编辑表手工编辑表 语法：语法：ALTER TABLE 表名表名 MODIFY (列名列名 数据类型数据类型列约束列约束, 列名列名 数据类型数据类型列约束列约束) ADD (列名列名 数据类型数据类型列约束列约束, 列名列名 数据类型数据类型 列约束列约束) DROP (列名列名 ,列名列名) DISABLE|ENABLE|DROP CONSTRAINT 表约束名表约束名;手工编辑表手工编辑表 上面语法中各参数描述如下：上面语法中各参数描述如下： MODIFY子句：对表中

34、原有的列或列约束进行修改。子句：对表中原有的列或列约束进行修改。 例例 将学生表将学生表STUDENT中中SNAME的数据类型改为的数据类型改为CHAR(6)，SAGE的数据类型改为的数据类型改为NUMBER(3)。 ALTER TABLE SYSTEM.STUDENT MODIFY (SNAME CHAR(6),SAGE NUMBER(3); ADD子句：增加列或列约束到原有的表中。子句：增加列或列约束到原有的表中。 例例 向学生表向学生表STUDENT中增加家庭地址和政治面貌两中增加家庭地址和政治面貌两列。列。手工编辑表手工编辑表 ALTER TABLE SYSTEM.STUDENT AD

35、D (SADDRESS VARCHAR2(20) NOT NULL, POLITICS VARCHAR2(20) NOT NULL); DROP子句：删除原有表中的列。删除列后关于子句：删除原有表中的列。删除列后关于该列的索引和完整性约束也同时删除。该列的索引和完整性约束也同时删除。 例例 将学生表将学生表STUDENT中中SADDRESS和和POLITICS列删除。列删除。 ALTER TABLE SYSTEM.STUDENT DROP (SADDRESS,POLITICS);手工编辑表手工编辑表 DISABLE|ENABLE|DROP CONSTRAINT子句：子句： 分别表示禁用（存在依

36、赖关系时不可禁用）分别表示禁用（存在依赖关系时不可禁用） 重启和删除表级约束条件重启和删除表级约束条件 用关键字用关键字CASCADE，将级联删除其它表的约束条件。，将级联删除其它表的约束条件。 例例 将学生表将学生表STUDENT中的表级约束条件中的表级约束条件A1。 ALTER TABLE SYSTEM.STUDENT DROP CONSTRAINS A1 CASCADE;表示成绩表表示成绩表SCORE中的表级约束中的表级约束C2将被级联删除。将被级联删除。更新表 1插入数据行插入数据行 语法：语法：INSERT INTO 表名表名(列名列名1,列名列名2,) VALUES(值值1,值值2,); 或或 INSERT INTO 表名表名(列名列名1,列名列名2,) SELECT *|列名列名1,列名列名2, FROM 另一表另一表名名; 2删除数据行删除数据行 语法：语法：DELETE FROM表名表名 WHERE 条件条件; 删除数据行并不能释放删除数据行并不能释放ORACLE数据库中被占用的数数据库中被占用的数据块表空间，只是将那些被删除的数据块标成据块表空间，只是将那些被删除的数据块标成unused。若确实要删除一个表