oracle数据库基本操作讲义

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

2、或模模式、模式对象象、用户的定义区区别：A schema：a collectionofdatabaseobjects(usedbya user.).被用户使使用的数数据库对对象的集集合。Schemaobjects：thelogicalstructures thatdirectlyrefertothe databasesdata.指向数据据库数据据的逻辑辑结构集集。A user：a namedefinedinthedatabasethatcanconnecttoandaccess objects.在数据库库中为了了能够连连接和访访问对象象而定义义的名字字。schema为数据库对对象的集集合，为了

3、区区分各个个集合，需要给给这个集集合起个个名字，即方案案下看到到的许多多类似用用户名的的节点这些类似似用户名名的节点点其实就就是一个个schemaschema里面包含含了各种种对象tables, views,sequences, storedprocedures,synonyms,indexes,clusters, anddatabaselinks。一个用户户一般对对应一个个schema,该用户的的schema名等于用用户名，并作为为该用户户缺省schema。Oracle数据库中中不能新新创建一一个schema，要想创创建一个个schema，只能通通过创建建一个用用户的方方法解决决在创建一个个

4、用户的的同时为这个用用户创建建一个与与用户名名同名的的schem并作为该该用户的的缺省shcema。schema的个数同同user的个数相同同，而且schema名字同user名字一一对对应并且相同同(别名，不不准确)。一个用户户还可以以使用其其他的schema。select*from emp;完整写法法为select*from scott.emp。schema.object，而不属属user.object。举个例子子：schema作为user的别名的的依据SQL GruantdbatoscottSQLcreatetabletest(name char(10);Tablecreated.SQLc

5、reatetablesystem.test(namechar(10);Table created.SQLinsert intotestvalues(scott);1 rowcreated.SQL insertinto system.test values(system);1row created.SQLcommit;Commitcomplete.SQL connsystem/managerConnected.SQL select* fromtest;NAME-systemSQL ALTER SESSIONSET CURRENT_SCHEMA =scott; -改变用户户缺省schema名Ses

6、sion altered.SQL select* fromtest;NAME-scottSQLselect owner ,table_name fromdba_tableswheretable_name=upper(test);OWNER TABLE_NAME- -SCOTTTESTSYSTEMTEST-在使用上上，shcema与user完全一样样。模式（方方案、schema）的建立立。是否用户户一建立立模式同同时建立立？必须建立立对象才才能在模模式中显显示相应应的用户户名。以system用户登登录创建用户户net1并以以之登录录创建表空空间netspace在netspace表空间下下创建表表

7、nettable预备知识识2：管理表表的策略略1、设计表表开发数据据库应用用系统时时，数据库管管理员负责规划划和建立立表空间，而规划和建建立表则往往是是由应用用开发人员员完成的。应用开发发人员设设计表时时应考虑虑以下因因素:在为表、列、索索引的视视图、同同义词、触发器器等与表表有关的的对象命命名时，应该使使用权有意义的的名称，其格式式要有利利于区分分这些对对象。为了给用用户和其其它人员员提供有有意义的的帮助信信息,应该使用用权Comment命令描述表、列的作作用.预备知识识2：管管理表的的策略设计表时时,应该使用用权范式来规划每每个表.范式分为为第一范范式第第二范式式 第三三范式范范式越越高,

8、表的设计计越规范范.定义表列列时,应该选择择合适的的数据类类型和长长度,为了节省省存储空空间,应该将可可以取Null值的列放放在后面面.确定表中中所需的的完整性性约束。在合适的的情况下下,使用簇表表以节省省存储空空间并提提高特定定类型的的SQL语句的性性能.预备知识识2：管理表表的策略略2、规划表表的类型型在Oracle数据库中中,存储用户户数据可可以使用用标准表表 索引引表簇簇表和和分区表表.标准表:数据按堆组织，以无序方式式存放在单单独表的的表段中中.这种表是是最基础础最常用用的数据据库表类类型,是默认情情况下创创建的表表.索引表:数据是以以”B-树”结构存存放在主主键约束束所对应应的索引

9、引段中.使用索引引表时,索引叶子子节点不不仅包含含了键列列的数据据还包含含了其他他非键列列的数据据。簇表:簇由共享相同同数据块块的一组表表组成.在合适的的情况下下,使用簇表表可以节节省存储储空间并并提高SQL语句的性性能.分区表:数据被划划分为更更小的部部分,并且存储储到相应应的分区区段中，每个分分构段可可以独立立管理和和操作.预备知识识2：管管理表的的策略3、规划表表的位置置如果要在在特定表表空间上上建表,用户必须须在该表表空间中中具有相相应的配配额或具具有UnlimitedTablespace的系统权权限如果建表表时不指指定TableSpace选项,Oracle会将表建建立在用用户的默默认

10、表空空间中.通过指定定TableSpace选项,可以将表表建立在在其他表表空间中中.当规划表表的存放放位置时时,应注意以以下事项项:System表空间中中应该只只包含数据字典典和Oracle系统对象象.如果将表表建立在在System表空间,将会影响响数据库库性能.建议DBA给每个用用户指定定其它的的默认表表空间.设计应用用系统时时,应该规划划存放应应用系统统的表的的表空间间,不要随意意分散地创建到到不同的的表空间间中,这样会使使执行某某些数据据库管理理和维护护操作.使用NoLogging选项建表表在创建表表的过程程中指定定nologging子句，避避免在创创建表过过程中产产生过多多的重做做记录

11、。nologging子句适合合时在创创建大表表时使用用.使用ComPress选项建表表使用Oracle的表压缩缩特征(Compress)时,Oracle会将同一一个数据据块行和和列的重重复数据据放在数数据块的的头部,压缩了表表数据。4、创建大大表时的的建议将表和它它的索引引分享存存放大表所对对应的索索引通常常比较大大,将表和它它的索引引分别存存放在不不同的表表空间中中，这样样的重建建索引的的过程中中能够更更加容易易地获取取所需的的连续存存储空间间,并且可以以提高查查询时的的I/O效率.分配足够够的临时时空间如果如果果应用程程序员在在访问大大表时需需要执行行规模较较大的排排序操作作,就必须在在数

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

13、在在数据库库的同一一方案中中是唯一的。表名名最长30个字符，以字母母开始，后由数数字、下下划线、#、$等组成。本例名名为customer。“方案”本本例取取默认值值（SYSTEM）。“表空间” 本例例取user。“表”单选选按钮：“标准” 表示示建立常常规表，表的一一列或多多列使用用一个索索引，为为表和索索引保留留两个独立立的存储储空间；“按索引引组织的的表”表表示建建立非常规表表，表的数据据保存在在该表的的索引中中，更改表表中的数数据（如如插入、删除或或修改行行等）将将使索引引更新。本例取取默认值值（标准准）。创建数据据表customer的一般信信息使用可编编辑的文文本区域域来创建建基于当当

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

15、ie（CHECK）-SSEXIN（男，女女），nianling（CHECK）-SAGEBETWEEN18AND24。“类型”：约束条条件类型型，下拉拉列表中中显示了了可用的的约束条条件类型型有UNIQUE、PRIMARY、FOREIGN、CHECK。本例选选择类型型为PRIMARY，表示pk_costomer_1是一个类类型为主主键的完完整性约约束条件件。“是否禁用用”：表表示创建建约束条条件时是是禁用还还是启用用该约束束条件。“引用方案案”：为为约束条条件中的的FOREIGN（外键）引用。数据表customer的约束条条件“簇列”选选项卡如果选中中“将表表定义为为簇的一一部分”复选框框，则表

16、表示将表表作为簇簇的一部部分。选选中后该该选项卡卡的所有有选项都都可以操操作：簇的的信信息显示示在一个个包含两两列的列列表框中中，描述述如下“表列”：显示了了当前表表所有已已定义的的列。“簇列”：显示了了包含在在已选簇簇内的簇簇关键字字列。本例皆取取系统默默认值。数据表customer的簇簇列“分区”选选项卡该选项卡卡的“范范围分区区”包含含如下设设置：“分区名”：显示示分区的的名称，但不能能编辑该该值。“表空间”：显示示存储分分区的表表空间，但也不不能编辑辑该值。该选项卡卡中包括括如下按按钮：“编辑”：允许对对分区进进行编辑辑。选择择一行后后该按钮钮即被启启用。“添加”：允许添添加分区区。选

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

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

19、行行处理事事务项数数，可以以输入1255之间的值值。“最大值”：指定定可同时时修改表表的数据据块的最最大并行行处理事事务项数数，可以以输入1255之间的值值。在“空闲列表表”选项组组内，可可以指定定下列参参数的值值“空闲列表表”：指指定表、簇或索索引的每每个空闲闲列表组组的数量量。可以以输入一一个值，默认值值为1。“组”：指指定表、簇或索索引的每每个空闲闲列表组组的数量量。可以以输入一一个值，默认值值为1。在“缓冲池”下拉列列表中可可以指定定默认缓缓冲池：“KEEP”：表示保保留内存存中的方方案对象象以避免免I/O操作。“RECYCLE”：表示一一旦不需需要数据据块时，立即将将它们从从内存中中

20、清除，以防止止对象占占用不必必要的高高速缓存存空间。“DEFAULT”：表示默默认缓冲冲池为高高速缓冲冲存储器器，所有有对象块块均存储储在指定定的高速速缓存中中。“选项”选选项卡该选项卡卡可以定定义表的的并发操操作、是是否覆盖盖和如何何审计等等特性。如果选中中“并行行”复选选框，则则表示以以并行方方式装载载，并行行执行某某种操作作。“程度”表示单单个例程程的操作并行行度，即使用用的查询服务务器的数量，可以指指定默认认值（根根据CPU数量和存存储要求求并行扫扫描的表表计算得得到），也可以以输入一一个值。如果选中中“覆盖盖默认事事件记录录值”复复选框，表示启启用事件件记录，将创建重重做日志志。该操

21、作作比不启启用事件件记录所所用时间间长，但但可以在在故障时时恢复更更新。如果选中中“将经经常访问问的数据据放在缓缓冲区高高速缓存存的顶端端（CACHE）”复选选框，表表示某个个用户进进程首次次需要某某一特定定数据时时，将在在数据库库的缓冲冲区高速速缓存中中搜索该该数据。如果选中中“监视视收集表上上的修改改统计信信息”复复选框，表示用用户希望望收集该该表的修修改统计计信息。“约束条件件存储”选项卡卡“约束条件件存储”选项卡卡，如图图所示。该选项项卡可以以为不同同的约束束条件选选择不同同的存储储方式。“约束条条件名称称”下拉拉列表：可以选选择不同同的约束束条件，以便为为不同的的约束条条件指定定不同

22、的的存储方方式“表空间”下拉列列表：可可以为约约束条件件的存储储选择指指定表空空间。如果选中中“覆盖盖默认事事件记录录值”，表示在在数据段段中可以以覆盖默默认的事事件记录录值。如如果选中中“生成成CONSTRAINT数据页的的完整重重做（LOGGING）”复选选框，表表示在数数据段上上为约束束生成完完整的重重做日志志。“创建”按钮，则开始始执行表表的创建建操作。“查看/编辑目录录”可以以输入数数据手工创建建表CREATETABLE用户.表名(列名数数据类类型列级完整整性约束束条件,列名数据据类型列级完整整性约束束条件 ,表级完整整性约束束条件)PCTFREE整数 INITRANS整数 MAXT

23、RANS整数TABLESPACE表空间名名DEFAULTSTORAGE（INITIAL整数K|MNEXT整数K|MMINEXTENTS整数 MAXEXTENTS整数|UNLIMITEDPCTINCREASE整数)CLUSTER簇名(簇列,)PARALLELCACHE|NOCACHELOGGING|NOLOGGINGAS子查询;上面语法法中各参参数描述述如下：约束条件件分为列列级和表表级：前者针对对某列，后者针针对整个个表。PCTFREE：指定每每一块预预留的自自由空间间百分比比，默认认值为10。即向表表中插入入新数据据行时，该表的的每个块块都只能能使用90%的空间，10%预留出来来供修改改该块

24、中中数据行行增大空空间时使使用。INITRANS：指定该该表每个个数据块块中分配配的初始始并行处处理事务务项数，范围是是1255。MAXTRANS：指定可可同时修修改表的的数据块块的最大大并行处处理事务务项数，范围是是1255。TABLESPACE：指定该该表所放放置的表空间间DEFAULT STORAGE：指定该该表的存存储方式式，具体体含义同同创建表表空间语语句中的的存储子子句。CLUSTER：指定该该表放置置在聚簇簇中。PARALLEL：指定加加速该表表的扫描描可以使使用的并并行查询询进程个个数。CACHE|NOCACHE：指定是是否将该该表中的的数据放放在CACHE（当该表表经常被被存

25、取时时使用）。LOGGING|NOLOGGING：前者指指定表的的创建操操作及之之后对表表的所有有操作都都记录在在重做日日志文件件中，是是默认选选项；后后者指定定表的创创建操作作和其它它操作不不记录在在重做日日志文件件中。AS子查询：基于一一个或多多个已存存在的表表建立新新表，新新表列的的数据类类型和大大小、新新表中的的数据行行都由查查询结果果决定。如CREATETABLEXSDAASSELECTSNO,SNAME,SSEX,SAGEFROM STUDENTWHERESCLASS=计算机科科学与技技术041。上机增加加的内容容：1、利用用sql语句手手工建立立customer表表（可选选）。2

26、、利用用sql语句或或理由企企业管理理控制台台来创建建最经典典的studentcoursescore这三个个数据表表。尤其其注意其其中的关关系如何何建立？（可选选）查看、编编辑表：有两种方方式：1使用Oracle企业管理理器查看看、编辑辑表启动Oracle企业管理理器，以以system身份连接接数据库库，展开开“方案案”“SYSTEM”“表”节点点，即可可查看SYSTEM方案中的的所有表表；右击击欲编辑辑的表，从快捷捷菜单中中单击“查看/编辑详细细资料”即弹出出编辑表表的对话话框可对对表进行行编辑。2手工查查看、编编辑表手工查看看表：Oracle9i提供了若若干个视视图，用用于查询询有关表表的

27、信息息。这些些视图的的名称及及说明如如下页表表所示。从DBA_TABLES视图中查查询所有有表的信信息，以以下脚本本运行结结果如图图所示。selecttable_name,tablespace_namefromdba_tables;图6.16使用DBA_TABLES视图查看看表信息息手工编辑辑表语法：ALTERTABLE表名MODIFY (列名数数据类类型列约束,列名数数据类类型列约束)ADD (列名数数据类类型列约束,列名数数据类类型列约束)DROP(列名,列名)DISABLE|ENABLE|DROPCONSTRAINT表约束名名;手工编辑辑表上面语法法中各参参数描述述如下：MODIFY子句

28、：对对表中原原有的列列或列约约束进行行修改。例将学生表表STUDENT中SNAME的数据类类型改为为CHAR(6)，SAGE的数据类类型改为为NUMBER(3)。ALTERTABLESYSTEM.STUDENTMODIFY(SNAME CHAR(6),SAGENUMBER(3);ADD子句：增增加列或或列约束束到原有有的表中中。例向学生表表STUDENT中增加家家庭地址址和政治治面貌两两列。手工编辑辑表ALTERTABLESYSTEM.STUDENTADD(SADDRESS VARCHAR2(20)NOTNULL,POLITICSVARCHAR2(20)NOT NULL);DROP子句：删删除

29、原有有表中的的列。删删除列后后关于该该列的索索引和完完整性约约束也同同时删除除。例将学生表表STUDENT中SADDRESS和POLITICS列删除。ALTERTABLESYSTEM.STUDENTDROP (SADDRESS,POLITICS);手工编辑辑表DISABLE|ENABLE|DROP CONSTRAINT子句：分别表示示禁用（存在依依赖关系系时不可可禁用）重启和删删除表级级约束条条件用关键字字CASCADE，将级联联删除其其它表的的约束条条件。例将学生表表STUDENT中的表级级约束条条件A1。ALTERTABLESYSTEM.STUDENTDROPCONSTRAINS A1 CASCADE;表示成绩绩表SCORE中的表级级约束C2将被级联联删除。更新表1插入数数据行语法：INSERTINTO表名(列名1,列名2,)VALUES(值1,值2,)