13管理表.pptx
- 文档编号:15802608
- 上传时间:2023-07-08
- 格式:PPTX
- 页数:65
- 大小:738.63KB
13管理表.pptx
《13管理表.pptx》由会员分享,可在线阅读,更多相关《13管理表.pptx(65页珍藏版)》请在冰点文库上搜索。
方案对象管理-管理表,什么是Oracle数据库方案?
方案是用户使用的一系列数据库对象的集合。
而用户是用来连接数据库并完成存取数据库对象的数据。
一个用户一般对应一个方案,该用户的方案名等于用户名,并作为该用户的缺省方案。
这也就是在企业管理器的方案下看到方案名都为数据库用户名的原因。
如果SCOTT用户创建了表EMP,那么SCOTT就是表EMP的所有者,SCOTT访问SCOTT用户下的EMP表时不用在表名前面加上表的所有者:
SELECT*FROMEMP;如果其他用户要访问表EMP,要在表名前面加上表的所有者:
SELECT*FROMscott.emp;否则会显示“没有此表”。
3,Oracle中的方案对象类型,Table:
表Index:
索引View:
视图Synonym:
同义词(方案对象的一个别名,简化访问,提高安全)Sequence:
序列(命名的顺序编号生成器)Procedure、function、trigger等存储过程、函数、触发器(是用户利用PL/SQL编写的命名的程序块),4,本章内容,表概述数据完整性与约束条件管理表的策略管理表创建表、更改表、删除表及删减表管理约束查看表信息及约束信息OEM中管理表,5,15.1表概述,表是Oracle数据库最基本的对象,其他许多数据库对象(如索引、视图)都以表为基础。
表被用于实际存储数据。
表中有列,列中存储多行数据。
在关系数据库中,不同表中的数据彼此之间可能存在一定的关系。
从用户角度看,表是一张二维表,即:
表由行和列两部分组成,表通过行和列来组织数据。
通常称表中的一行为一条记录,称表中的一列为一个字段。
6,表和列的命名规则,长度30个字节之内。
必须以字母开头。
由字母、数字、_、#、$组成。
不能使用保留字。
非法的命名,可以用“”引着。
一个表内列名要唯一。
表名在名称空间内必须唯一。
7,Oracle中的数据类型,字符型char、ncharvarchar2、nvarchar2大对象数据类型clob、nclobblob、bfile(二进制)二进制数据类型Row、LongRow数字型number(p,s)时间和日期Date,.行数据类型RowID、URowID,15.2数据完整性与约束条件,数据完整性是关系数据库模型的基本原则,是用户在表上定义的一系列规则或约束条件,以及在表之间定义的一系列相互关系。
数据完整性的作用,简单地说,就是强制要求数据库中只能接受正确的、合理的数据,防止错误的或无效的数据被插入到表中。
分三种类型:
实体完整性:
主键的各个属性都不能为空值。
参照完整性:
外键或者取空值,或者等于被参照关系中的主键的某个值。
用户自定义完整性:
属性(或属性组)的取值应当满足用户定义的约束条件。
DEPTNO的值必须与表DEPT中的DEPTNO值匹配,ENAME的每列都必须有一个值,EMPNO每行都必须有惟一的值,10,15.2数据完整性与约束条件,数据完整性实体完整性参照完整性用户自定义完整性,约束条件NotNullUniqueCheckPrimarykeyForeignkey约束级别列级约束表级约束,11,15.3管理表的策略,设计表的结构表的类型标准表(按堆组织)索引表(以B树索引段)外部表(操作系统文件中)分区表簇表表的位置及大小使用建表的相关选项(nologging),创建表,如果要在自己的方案中创建表,要求用户必须具有CREATETABLE系统权限。
如果要在其他方案中建表,则要求用户必须具有CREATEANYTABLE系统权限。
创建表时,Oracle会为该表分配相应的表段。
表段的名称与表名完全相同,并且所有的数据都会被存放在该表段中。
要求表的创建者必须指定表空间上具有空间配额或具有UNLIMITEDTABLESPACE系统权限。
13,Createtable语句,Createtableschema.table_name(column1datatype1defaultexp1column1_constraint,column2datatype2defaultexp2column2_constraint,table_constraint)oncommitdelete|preserverowsorganizitionheap.|index|externalpartitionby()tablespacetablespace_nameLogging|nologgingcompress|nocompressasquery,14,创建标准表,createtabledepartment(deptidnumber(9)notnull,deptnochar
(2)default01notnull,namevarchar2(20),addressvarchar2(30),constraintpk_deptidprimarykey(deptid)validate,constraintuniq_nameunique(name)novalidate,constraintchk_addresscheck(addressin(1号楼,2号楼,3号楼,4号楼)validate),15,临时表,Oracle的临时表分为事务型和会话型。
事务型临时表就是指在事务处理过程中插入的记录只在事务提交或回滚之前有效,一旦事务完成,表中的记录便被自动清除。
会话型临时表指临时表中的数据在本次会话期间一直有效,直到会话结束。
会话结束后表中针对此次会话的数据会自动清空。
当在临时表上执行DML操作时,既不会加锁也不会将数据变化写到重做日志中。
因此不能用临时表保存永久数据。
16,创建临时表,Createglobaltemporarytabletable_name()oncommitdeleterows|oncommitpreserverows其中:
globaltemporarytable指定表的类型为临时表。
oncommitdeleterows指定表为事务临时表。
oncommitpreserverows指定表为会话临时表。
事务临时表例子:
会话临时表的例子:
索引表,对于标准表而言,其数据以无序方式存储;而对于索引表来说,其数据以B-树结构来组织,并且其叶子数据块既包含键列数据,也包含非键列数据。
一般情况下,带有索引的标准表的表数据及其索引数据分别存放在对应的表段和索引段中,当在where子句中引用索引列时,首先定位索引数据并取得对应记录的ROWID,然后根据ROWID取得表的数据。
建立索引表时,Oracle会将表数据及其主键索引的数据一起放到索引段中。
当在WHERE子句中引用主键列时,Oracle可以直接根据主键索引值取得表行数据。
所以,如果要经常基于主键列检索表数据,那么Oracle建议使用索引表。
索引表和带索引的标准表是绝对不同的。
索引表的优点:
在执行针对主键查询,索引表远远比具有索引的标准表要快的多。
但索引表不宜存放变动太大的数据。
开销太大。
创建索引表,CreatetableIOT1(idnumber(9),namevarchar2(20),addressvarchar2(30),notevarchar2(40),constraintPK_IDprimarykey(ID)validate)organizationindexpctthreshold40includingaddressoverflowtablespaceusers;,执行上述语句后,会建立索引表IOT1,其键列和非键列会存放到主键约束所对应的索引段PK_ID中,而溢出数据则会存放到溢出段SYS_IOT_OVER_n(n:
索引表的对象号)中。
定义索引表时,主键约束和ORGANIZATIONINDEX选项时必须指定的。
选项的作用如下:
ORGANIZATIONINDEX:
用于指定创建一个索引表,PCTHRESHOLD:
用于指定数据块中为键列和部分非键列数据所预留空间的百分比。
如果数据块剩余空间低于PCTHRESHOLD设置,Oracle会将其他数据存放到溢出段。
OVERFLOWTABLESPACE指定溢出段所在表空间。
INCLUDING:
当有溢出情况时,指定控制哪些非键值列与键值列存储在一起。
INCLUDINGclumn:
该选项用来控制哪些非键列要与键列存储在一起。
OVERFLOWTABLESPACE:
用于指定溢出段所在的表空间。
25,通过查询创建表(CTAS方式),SQLcreatetableemp1asselect*fromempnologging;-在创建大表时,建议带。
查询创建表,约束及默认值定义不会被复制。
查询创建表,不能带tablespace子句。
可以复制表的结构,复制数据。
可以复制表中的个别字段。
26,外部表,Oracle允许以只读方式访问外部表中的数据。
外部表指不是数据库中的表,而是操作系统(简称OS)文件中的表。
使用createtable语句的organizationexternal子句创建外部表。
使用Insert语句将OS文件中的数据装载到数据库中,从而实现SQL*Loader的功能。
最大用途:
通过外部表可以把OS文件中的数据装载到Oracle数据库中。
27,创建外部表,Step0:
在C:
准备一个department.txt的文件。
Step1:
建立目录对象。
connsys/o123assysdba;createdirectorydept_dirasC:
;Step2:
创建外部表(createtable语句)包括两部分,一部分描述表列的数据类型,另外一部分描述OS文件数据与表列对应关系。
Step3:
使用外部表。
Createtableext_department(DeptIdnumber(9),DeptNochar
(2),Namevarchar2(20),Addressvarchar2(30)Organizationexternal(typeORACLE_LOADERDefaultdirectorydept_dirAccessparameters(recordsdelimitedbynewlineFieldsterminatedby,Missingfieldvaluesarenull(DeptId,DeptNo,Name,Address)Location(department.txt);,外部表ext_department里面已经有数据了,我们就可以把这些数据导入到真正的Oracle的表中。
如:
insertintodepartmentselect*fromext_department;可以使用user_external_tables来查看当前用户可以使用的外部表。
更改、删除外部表,修改默认目录对象Createdirectorytest_dir1ASE:
external1;Altertabletest_deptdefaultdirectorytest_dir1;修改文件位置Altertableext_departmentlocation(new.txt);修改访问参数Altertabletest_deptaccessparameters(fieldsterminatedby;);删除外部表Droptabletest_dept;删除目录对象Dropdirectorytest_dir;,32,15.5更改表,修改表Altertabletable_nameAddcolumndatatype,|Modify(columndatatype,.)|Dropcolumncolumn_name|renamecolumncolumn_nametonewname添加表和列注释重命名表和重新组织表,33,添加列,Altertabledepartmentadd(leadervarchar2(20),updateDatedatedefaultsysdate);添加新列的时候,如果表中有记录了,那就不能直接指定NotNull约束。
必须要有一个带Default子句的NotNull约束。
Altertabledepartmentadd(notevarchar2(40)default备注notnull);,34,修改列,把department表的deptno字段改为char(6)、note字段改为varchar2(80)。
AltertabledepartmentModify(deptnochar(6),notevarchar2(80);,35,删除列,单独删除一列:
Altertabledepartmentdropcolumnnotecascadeconstraints;Altertabledepartmentdrop(note)cascadeconstraints;同时删除多列:
Altertabledepartmentdrop(updatedate,note)cascadeconstraints;,36,部分删除,删除某个列,同时释放该列所占用的存储空间。
如果是大表,这种删除操作会很耗时,为了避免在数据库使用高峰期间执行删除列的操作而占用过多的系统资源,可以通过把该列标记为unused状态。
altertabledepartmentsetunused(address)cascadeconstraints;仅仅是逻辑删除,还需要进一步删除释放空间。
altertabledepartmentdropunusedcolumns.,37,添加表和列的注释,添加表注释(user_tab_comments可以查看)Commentontabletable_nameIStext;SQLcommentontableemp1isanewtablecopyfromemp;添加列注释(user_col_comments可以查看)Commentoncolumntable_name.columnIStext;SQLcommentonemp1.empnois雇员编号;,38,重命名表和重新组织表,重命名表:
SQLrenamenew_emptoemp1;(本方案中)SQLaltertableemp1renametonew_emp;重新组织表:
(表放在不适合的区或摆脱碎片,重新组织表迁移到新的数据段中)SQLaltertablenew_empmove;SQLaltertablenew_emptablespacemyts;,39,15.6删减表和删除表,删减表:
删除所有记录,保留表结构。
Deletefromtable_nameTruncatetabletable_name删除表Droptabletable_namecascadeconstraints;,40,删减表,SQLDeletefromemp1;SQLdescemp1;显示表结构SQLTruncatetableemp1;二者的区别:
delete不会释放空间;truncate可以清空数据及索引,释放空间。
41,15.7维护约束,约束种类约束状态管理约束(定义约束、修改和删除约束、设置约束状态、设置约束的延迟检查),42,约束种类,NotNullUniqueCheckPrimarykeyForeignkey,只能列级约束列级、表级均可列级、表级均可列级、表级均可列级、表级均可,43,约束状态,激活与禁用状态激活状态(Enable)禁用状态(Disable)验证和非验证状态,validate:
验证定义或激活约束时,Oracle检查所有记录是否满足条件。
novalidate:
非验证定义或激活约束时,Oracle检查不所有记录是否满足条件。
Enable:
激活Disable:
禁用,约束的状态分为两种:
分类方式状态检查新数据启用ENABLE禁止DISABLE检查老数据验证VALIDATE非验证NOVALIDATE,两种状态组合后形成4种约束状态:
1、ENABLEVALIDATE2、ENABLENOVALIDATE3、DISABLEVALIDATE4、DISABLENOVALIDATE,1、ENABLEVALIDATE,在Department表的Name列上定义了唯一约束,当把该约束状态转为ENABLEVALIDATE状态时,插入新数据和已有旧数据都不允许重复。
2、ENABLENOVALIDATE,在Department表的Name列上定义了唯一约束,当把该约束状态转为ENABLENOVALIDATE状态时,插入新数据不允许重复,已有旧数据允许重复。
3、DISABLEVALIDATE,在Department表的Name列上定义了唯一约束,当把该约束状态转为DISABLEVALIDATE状态时,不允许对表进行任何DML操作。
已有旧数据允许重复。
4、DISABLENOVALIDATE,在Department表的Name列上定义了唯一约束,当把该约束状态转为DISABLENOVALIDATE状态时,插入新数据、已有旧数据允许重复。
约束状态组合,管理约束,添加和删除约束设定约束的状态:
禁用和启用约束(验证、非验证)设定约束的延迟检查,createtablestudent(STUIDNUMBER(9)notnull,STUNOCHAR(10),NAMEVARCHAR2(20)notnull,SEXCHAR
(1),BIRTHDAYDATE,PHOTOBLOB,DEPTIDNUMBER(9),constraintPK_STUDENTprimarykey(STUID)validate,constraintCHK_SEXCHECK(sexin(1,0)validate,constraintFK_DEPTIDForeignkey(DEPTID)referencesdepartment(DEPTID)validate),添加约束,ALTERTABLEtable_nameAddCONSTRAINTconstraint_nameConstraint_type(col1,col2,)condition-约束条件例子:
AltertablestudentAddConstraintUNI_NAMEUNIQUE(name);AlterTablestudentMODIFYbirthdayNOTNULL;,增加NOTNULL约束时,必须用MODIFY代替ADD选项,否则会有错误提示。
在定义和添加FOREIGNKEY约束时,也能够通过ON关键字指定引用行为的类型。
当父表中的一条记录被删除时,需要通过引用行为来确定如何处理子表中的外键列。
ONDELETECASCADE:
级联删除ONDELETESETNULL:
将相关记录的外键列的值设为NULLONDELETENOACTION:
不进行任何操作例:
AlterTablestudentAddConstraintFK_DEPTIDForeignKey(deptid)Referencedepartment(deptid)OnDeleteSetNull;,查看约束信息,查询当前方案中某表的约束信息selecttable_name,constraint_name,constraint_type,deferred,deferrable,statusfromuser_constraintswheretable_name=DEPARTMENT;查询定义了约束的列Selecttable_name,constraint_name,column_name,positionfromuser_cons_columnswheretable_name=DEPARTMENT;,删除约束,ALTERTABLEstudentDropCONSTRAINTCHK_SEX;ALTERTABLEstudentDropUNIQUE(name);ALTERTABLEdepartmentDropPrimaryKeyCascade;ALTERTABLEstudentModifybirthdayNULL;,使用CASCADE关键字同时删除引用它的约束,删除一个NotNUL约束,必须用MODIFY代替drop选项,修改约束名,AlterTableStudentRenameconstraintCHK_SEXtoNew_CHK_Sex;,设置约束状态,禁用和启用约束ALTERTABLEstudentDISABLEconstraintCHK_SEX;ALTERTABLEstudentDISABLEUNIQUE(name);ALTERTABLEstudentENABLEconstraintCHK_SEX;ALTERTABLEstudentENABLEUNIQUE(name);ALTERTABLEdepartmentDISABLEPRIMARYKEYCASCADE;使用CASCADE关键字,在禁用父表UNIQUE或PRIMAYKEY约束的同时,先禁用引用该约束的所有FOREIGNKEY约束。
验证和非验证约束与激活或禁用约束不同,不能在ALTERTABLE语句中单独使用VALIDATE和NOVALIDATE选项ALTERTABLEstudentENABLEVALIDATECONSTAINTCHK_SEX;ALTERTABLEstudentDISABLEVALIDATEPRIMARYKEY;ALTERTABLEstudentENABLENOVALIDATECONSTAINTCHK_SEX;ALTERTABLEstudentDISABLENOVALIDATEPRIMARYKEY;,设置约束的延迟检查,默认情况下,在表中创建的约束都是不可延迟约束,即创建约束时默认使用NOTDEFERRABLE选项。
某些情况下,可能希望以一个事务为单位,只在事务结束时进行约束检查,如级联更新外键、给自参照表装载数据等,需要将约束检查时间进行延迟,即在创建约束时须指定DEFERRABLE选项。
设置约束的延迟检查特性时,注意以下几点:
对于NOTDEFERABLE约束,创建之后就不能改变约束检查时机。
对于DEFERABLE约束,创建时可以指定INITIALLYIMMEDIATE或INITIALLYDEFERRED;前者指定可延迟约束在初始状态下立即检查,后者指定可延迟约束时延迟检查的。
创建之后可以通过ALTERTABLE来改变约束检查的时机。
约束一旦创建,就不能再改变它的可延迟性。
如想更改,只能删除重建该约束。
可针对整个数据库的所有可延迟约束设置延迟或立即检查:
Setconstraintsalldeferred;Setconstraintsallimmediate;,64,OEM中管理表,参看课本,65,END,
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 13 管理