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。AL