Oracle数据库基本操作之二.ppt

第六章 oracle数据库基本操作,本章内容： Oracle数据库的数据类型 Sql /plus环境 Oracle数据库基本操作 建立、删除、修改基本表 建立、删除、修改索引 建立、删除、修改视图,本节内容,预备知识1：建表之前必须明确用户和模式的关系； 预备知识2：建表之前必须清晰管理表的策略 ； Oracle数据库基本操作 建立、删除、修改基本表 建立、删除、修改索引 建立、删除、修改视图,预备知识1：用户和模式的关系,Oracle9i数据中用方案（schema）来对逻辑对象进行管理。不同的数据库对象具有各自的特点和用途。 方案或模式、模式对象、用户的定义区别： A schema ：a collection 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为数据库对象的集合，为了区分各个集合，需要给这个集合起个名字，即方案下看到的许多类似用户名的节点 这些类似用户名的节点其实就是一个schema schema里面包含了各种对象 tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links。,一个用户一般对应一个schema,该用户的schema名等于用户名，并作为该用户缺省schema。 Oracle数据库中不能新创建一个schema，要想创建一个schema，只能通过创建一个用户的方法解决 在创建一个用户的同时为这个用户创建一个与用户名同名的schem并作为该用户的缺省shcema。 schema的个数同user的个数相同，而且schema名字同user名字一一 对应并且相同(别名，不准确)。,一个用户还可以使用其他的schema。 select * from emp; 完整写法为select * from scott.emp。 schema.object，而不属user.object。,举个例子： schema作为user的别名的依据,SQL Gruant dba to scott SQL create table test(name char(10); Table created. SQL create table system.test(name char(10); Table 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/manager Connected. SQL select * from test; NAME - system SQL ALTER SESSION SET CURRENT_SCHEMA = scott; -改变用户缺省schema名 Session altered.,SQL select * from test; NAME - scott SQL select owner ,table_name from dba_tables where table_name=upper(test); OWNER TABLE_NAME - - SCOTT TEST SYSTEM TEST -在使用上，shcema与user完全一样。,模式（方案、schema）的建立。,是否用户一建立模式同时建立？ 必须建立对象才能在模式中显示相应的用户名。,以system用户登录,创建用户net1并以之登录,创建表空间netspace,在netspace表空间下创建表nettable,预备知识2：管理表的策略,1、设计表 开发数据库应用系统时，数据库管理员负责规划和建立表空间，而规划和建立表则往往是由应用开发人员完成的。 应用开发人员设计表时应考虑以下因素: 在为表、列、索引的视图、同义词、触发器等与表有关的对象命名时，应该使用权有意义的名称，其格式要有利于区分这些对象。 为了给用户和其它人员提供有意义的帮助信息,应该使用权Comment命令描述表、列的作用.,预备知识2：管理表的策略,设计表时,应该使用权范式来规划每个表.范式分为第一范式 第二范式 第三范式 范式越高,表的设计越规范. 定义表列时,应该选择合适的数据类型和长度,为了节省存储空间,应该将可以取Null值的列放在后面. 确定表中所需的完整性约束。 在合适的情况下,使用簇表以节省存储空间并提高特定类型的SQL语句的性能.,预备知识2：管理表的策略,2、规划表的类型 在Oracle数据库中,存储用户数据可以使用标准表 索引表 簇表 和分区表. 标准表:数据按堆组织，以无序方式存放在单独表的表段中.这种表是最基础最常用的数据库表类型,是默认情况下创建的表. 索引表:数据是以”B-树”结构存放在主键约束所对应的索引段中.使用索引表时,索引叶子节点不仅包含了键列的数据还包含了其他非键列的数据。 簇表:簇由共享相同数据块的一组表组成.在合适的情况下,使用簇表可以节省存储空间并提高SQL语句的性能. 分区表:数据被划分为更小的部分,并且存储到相应的分区段中，每个分构段可以独立管理和操作.,预备知识2：管理表的策略,3、规划表的位置 如果要在特定表空间上建表,用户必须在该表空间中具有相应的配额或具有Unlimited Tablespace的系统权限 如果建表时不指定TableSpace选项,Oracle会将表建立在用户的默认表空间中.通过指定TableSpace选项,可以将表建立在其他表空间中.,当规划表的存放位置时,应注意以下事项: System表空间中应该只包含数据字典和Oracle系统对象 . 如果将表建立在System表空间,将会影响数据库性能. 建议DBA给每个用户指定其它的默认表空间. 设计应用系统时,应该规划存放应用系统的表的表空间,不要随意分散地创建到不同的表空间中,这样会使执行某些数据库管理和维护操作.,使用NoLogging选项建表 在创建表的过程中指定nologging子句，避免在创建表过程中产生过多的重做记录。 nologging子句适合时在创建大表时使用. 使用ComPress选项建表 使用Oracle的表压缩特征(Compress)时, Oracle会将同一个数据块行和列的重复数据放在数据块的头部,压缩了表数据。,4、创建大表时的建议 将表和它的索引分享存放 大表所对应的索引通常比较大,将表和它的索引分别存放在不同的表空间中，这样的重建索引的过程中能够更加容易地获取所需的连续存储空间,并且可以提高查询时的I/O效率. 分配足够的临时空间 如果如果应用程序员在访问大表时需要执行规模较大的排序操作,就必须在数据库中具有一个较大的临时段,临时段的存储参数设置取决于临表空间的默认存储参数设置,即Storage子句中的各种参数的设置,5、预计和规划表的大小 可以对表的大小进行估计,包括对索引 回退段和重做日志文件大小的估计,以决定支持期望的数据库所需的磁盘空间大小.通过这些估计,就可以购买合适的硬件和作其它决定,6.3创建基本表,1、创建基本表 基本表是数据库最基本的对象，是实际存放数据的地方。,以system登录,“创建表”对话框包含了七个选项卡,“一般信息”选项卡， “名称”文本框：输入新建表的名称，表名在数据库的同一方案中是唯一的。表名最长30个字符，以字母开始，后由数字、下划线、#、$等组成。本例名为customer。 “方案” 本例取默认值（SYSTEM）。 “表空间” 本例取user。 “表”单选按钮： “标准” 表示建立常规表，表的一列或多列使用一个索引，为表和索引保留两个独立的存储空间； “按索引组织的表” 表示建立非常规表，表的数据保存在该表的索引中，更改表中的数据（如插入、删除或修改行等）将使索引更新。本例取默认值（标准）。,创建数据表customer的一般信息,使用可编辑的文本区域来创建基于当前表的PL/SQL查询语句。,创建使用用户定义的对象类型的对象表,表示可以创建XML类型的表,“约束条件”选项卡,，如下页图所示。该选项卡可以使用可编辑的电子表格编辑表的完整性约束条件。完整性约束条件是一种规则，不占用任何数据库空间，其定义存储在数据字典中，在执行 PL/SQL期间使用。该电子表格由以下各列构成：,“约束条件”选项卡,“名称”：要定义的完整性约束条件名，它在数据库中应是唯一的。本例定义了一个完整性约束条件：pk_costomer_1（PRIMARY）-cid为主键 另外举两例 xingbie（CHECK）-SSEX IN（男，女）， nianling（CHECK）-SAGE BETWEEN 18 AND 24。 “类型”：约束条件类型，下拉列表中显示了可用的约束条件类型有UNIQUE、PRIMARY、FOREIGN、CHECK。本例选择类型为PRIMARY，表示pk_costomer_1是一个类型为主键的完整性约束条件。 “是否禁用”：表示创建约束条件时是禁用还是启用该约束条件。 “引用方案”：为约束条件中的FOREIGN（外键）引用。,数据表customer的约束条件,“簇列”选项卡,如果选中“将表定义为簇的一部分”复选框，则表示将表作为簇的一部分。选中后该选项卡的所有选项都可以操作： 簇的的信息显示在一个包含两列的列表框中，描述如下 “表列”：显示了当前表所有已定义的列。 “簇列”：显示了包含在已选簇内的簇关键字列。 本例皆取系统默认值。,数据表customer的簇列,“分区”选项卡,该选项卡的“范围分区”包含如下设置： “分区名”：显示分区的名称，但不能编辑该值。 “表空间”：显示存储分区的表空间，但也不能编辑该值。 该选项卡中包括如下按钮： “编辑”：允许对分区进行编辑。选择一行后该按钮即被启用。 “添加”：允许添加分区。选择最后一行后，该按钮可被启用。 “删除”：允许删除分区。选择一行后，该按钮即被启用。 本例皆取系统默认值。,数据表customer的分区,“存储”选项卡,“存储”选项卡，可以定义表的存储方式,在“区”选项组内，可以指定下列参数的值： “初始大小”：指定表的第一个分区的大小。可以输入一个值，但至少为一个数据块的大小，默认值为64KB。 “下一个大小”：指定表的第二个分区的大小。可以输入一个值，但至少为一个数据块的大小，默认值为64KB。 “增量”：指定从第三区开始相对于上一个区的增长百分比。 “最小数量”：指定分配给表的最小区数。默认值为1。 “最大数量”：指定分配给表的最大区数。可以输入一个值，也可以是无限制。 在“空间利用率”选项组内，可以指定下列参数的值： “保留用于更新的空闲空间的百分比”：指定为以后更新表而保留的空间的百分比，可以输入099之间值，默认为10。 “行插入备选对象的使用空间百分比阈值”：指定为该表数据块保留的已用空间的最小百分比，可以输入099之间的值，默认值为40。,在“事务处理数量”选项组内，可以指定下列参数的值： “初始值”：指定该表每个数据块中分配的初始并行处理事务项数，可以输入1255之间的值。 “最大值”：指定可同时修改表的数据块的最大并行处理事务项数，可以输入1255之间的值。 在“空闲列表”选项组内，可以指定下列参数的值 “空闲列表”：指定表、簇或索引的每个空闲列表组的数量。可以输入一个值，默认值为1。 “组”：指定表、簇或索引的每个空闲列表组的数量。可以输入一个值，默认值为1。 在“缓冲池”下拉列表中可以指定默认缓冲池： “KEEP”：表示保留内存中的方案对象以避免I/O操作。 “RECYCLE”：表示一旦不需要数据块时，立即将它们从内存中清除，以防止对象占用不必要的高速缓存空间。 “DEFAULT”：表示默认缓冲池为高速缓冲存储器，所有对象块均存储在指定的高速缓存中。,“选项”选项卡,该选项卡可以定义表的并发操作、是否覆盖和如何审计等特性。,如果选中“并行”复选框，则表示以并行方式装载，并行执行某种操作。 “程度”表示单个例程的操作并行度，即使用的查询服务器的数量，可以指定默认值（根据CPU数量和存储要求并行扫描的表计算得到），也可以输入一个值。 如果选中“覆盖默认事件记录值”复选框，表示启用事件记录，将创建重做日志。该操作比不启用事件记录所用时间长，但可以在故障时恢复更新。 如果选中“将经常访问的数据放在缓冲区高速缓存的顶端（CACHE）”复选框，表示某个用户进程首次需要某一特定数据时，将在数据库的缓冲区高速缓存中搜索该数据。 如果选中“监视收集表上的修改统计信息”复选框，表示用户希望收集该表的修改统计信息。,“约束条件存储”选项卡,“约束条件存储”选项卡，如图所示。该选项卡可以为不同的约束条件选择不同的存储方式。,“约束条件名称”下拉列表：可以选择不同的约束条件，以便为不同的约束条件指定不同的存储方式 “表空间”下拉列表：可以为约束条件的存储选择指定表空间。 如果选中“覆盖默认事件记录值”，表示在数据段中可以覆盖默认的事件记录值。如果选中“生成CONSTRAINT数据页的完整重做 （LOGGING）”复选框，表示在数据段上为约束生成完整的重做日志。 “创建”按钮，则开始执行表的创建操作。,“查看/编辑目录”可以输入数据,手工创建表,CREATE TABLE 用户.表名 (列名 数据类型 列级完整性约束条件 ,列名数据类型 列级完整性约束条件 ,表级完整性约束条件) PCTFREE 整数 INITRANS 整数 MAXTRANS 整数 TABLESPACE 表空间名 DEFAULT STORAGE （INITIAL整数K|M NEXT整数K|M MINEXTENTS 整数 MAXEXTENTS 整数|UNLIMITED PCTINCREASE 整数)CLUSTER 簇名(簇列,) PARALLEL CACHE|NOCACHE LOGGING|NOLOGGING AS 子查询;,上面语法中各参数描述如下： 约束条件分为列级和表级：前者针对某列，后者针对整个表。 PCTFREE：指定每一块预留的自由空间百分比，默认值为10。即向表中插入新数据行时，该表的每个块都只能使用90%的空间，10%预留出来供修改该块中数据行增大空间时使用。 INITRANS：指定该表每个数据块中分配的初始并行处理事务项数，范围是1255。 MAXTRANS：指定可同时修改表的数据块的最大并行处理事务项数，范围是1255。 TABLESPACE：指定该表所放置的表空间 DEFAULT STORAGE：指定该表的存储方式，具体含义同创建表空间语句中的存储子句。,CLUSTER：指定该表放置在聚簇中。 PARALLEL：指定加速该表的扫描可以使用的并行查询进程个数。 CACHE|NOCACHE：指定是否将该表中的数据放在CACHE（当该表经常被存取时使用）。 LOGGING|NOLOGGING：前者指定表的创建操作及之后对表的所有操作都记录在重做日志文件中，是默认选项；后者指定表的创建操作和其它操作不记录在重做日志文件中。 AS 子查询：基于一个或多个已存在的表建立新表，新表列的数据类型和大小、新表中的数据行都由查询结果决定。 如CREATE TABLE XSDA AS SELECT SNO,SNAME,SSEX,SAGE FROM STUDENT WHERE SCLASS=计算机科学与技术041。,上机增加的内容：,1、利用sql语句手工建立customer表（可选）。 2、利用sql语句或理由企业管理控制台来创建最经典的studentcoursescore这三个数据表。尤其注意其中的关系如何建立？（可选）,查看、编辑表：有两种方式： 1使用Oracle企业管理器查看、编辑表 启动Oracle企业管理器，以system身份连接数据库，展开“方案”“SYSTEM”“表”节点，即可查看SYSTEM方案中的所有表；右击欲编辑的表，从快捷菜单中单击“查看/编辑详细资料”即弹出编辑表的对话框可对表进行编辑。 2手工查看、编辑表 手工查看表：Oracle9i提供了若干个视图，用于查询有关表的信息。这些视图的名称及说明如下页表所示。,从DBA_TABLES视图中查询所有表的信息，以下脚本运行结果如图所示。 select table_name,tablespace_name from dba_tables;,图6.16 使用DBA_TABLES视图查看表信息,手工编辑表 语法：ALTER TABLE 表名 MODIFY (列名 数据类型列约束, 列名 数据类型列约束) ADD (列名 数据类型列约束, 列名 数据类型 列约束) DROP (列名 ,列名) DISABLE|ENABLE|DROP CONSTRAINT 表约束名;,手工编辑表,上面语法中各参数描述如下： MODIFY子句：对表中原有的列或列约束进行修改。 例 将学生表STUDENT中SNAME的数据类型改为CHAR(6)，SAGE的数据类型改为NUMBER(3)。 ALTER TABLE SYSTEM.STUDENT MODIFY (SNAME CHAR(6),SAGE NUMBER(3); ADD子句：增加列或列约束到原有的表中。 例 向学生表STUDENT中增加家庭地址和政治面貌两列。,手工编辑表,ALTER TABLE SYSTEM.STUDENT ADD (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子句： 分别表示禁用（存在依赖关系时不可禁用） 重启和删除表级约束条件 用关键字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删除数据行 语法