oracle数据库基本操作讲义

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

：thelogicalstructuresthatdirectlyrefertothedatabase’sdata.指向数据库数据的逻辑结构集。Auser：anamedefinedinthedatabasethatcanconnecttoandaccessobjects.在数据库中为了能够连接和访问对象而定义的名字。schema为数据库对象的集合，为了区分各个集合，需要给这个集合起个名字，即方案下看到的许多类似用户名的节点这些类似用户名的节点其实就是一个schemaschema里面包含了各种对象tables,views,sequences,storedprocedures,synonyms,indexes,clusters,anddatabaselinks。一个用户一般对应一个schema,该用户的schema名等于用户名，并作为该用户缺省schema。Oracle数据库中不能新创建一个schema，要想创建一个schema，只能通过创建一个用户的方法解决在创建一个用户的同时为这个用户创建一个与用户名同名的schem并作为该用户的缺省shcema。schema的个数同user的个数相同，而且schema名字同user名字一一对应并且相同(别名，不准确)。

一个用户还可以使用其他的schema。select*fromemp;完整写法为select*fromscott.emp。schema.object，而不属user.object。举个例子：schema作为user的别名的依据SQL>Gruantdbatoscott

SQL>createtabletest(namechar(10));

Tablecreated.

SQL>createtablesystem.test(namechar(10));

Tablecreated.

SQL>insertintotestvalues('scott');

1rowcreated.

SQL>insertintosystem.testvalues('system');

1rowcreated.

SQL>commit;

Commitcomplete.SQL>connsystem/manager

Connected.

SQL>select*fromtest;

NAME

----------

system

SQL>ALTERSESSIONSETCURRENT_SCHEMA=scott;--改变用户缺省schema名

Sessionaltered.SQL>select*fromtest;

NAME

----------

scott

SQL>selectowner,table_namefromdba_tableswheretable_name=upper(‘test’);

OWNERTABLE_NAME

------------------------------------------------------------

SCOTTTEST

SYSTEMTEST

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