企业管理表格范本.ppt

方案对象管理 管理表 什么是Oracle数据库方案 方案是用户使用的一系列数据库对象的集合 而用户是用来连接数据库并完成存取数据库对象的数据 一个用户一般对应一个方案 该用户的方案名等于用户名 并作为该用户的缺省方案 这也就是在企业管理器的方案下看到方案名都为数据库用户名的原因 如果SCOTT用户创建了表EMP 那么SCOTT就是表EMP的所有者 SCOTT访问SCOTT用户下的EMP表时不用在表名前面加上表的所有者 SELECT FROMEMP 如果其他用户要访问表EMP 要在表名前面加上表的所有者 SELECT FROMscott emp 否则会显示 没有此表 Oracle中的方案对象类型 Table 表Index 索引View 视图Synonym 同义词 方案对象的一个别名 简化访问 提高安全 Sequence 序列 命名的顺序编号生成器 Procedure function trigger等存储过程 函数 触发器 是用户利用PL SQL编写的命名的程序块 本章内容 表概述数据完整性与约束条件管理表的策略管理表创建表 更改表 删除表及删减表管理约束查看表信息及约束信息OEM中管理表 15 1表概述 表是Oracle数据库最基本的对象 其他许多数据库对象 如索引 视图 都以表为基础 表被用于实际存储数据 表中有列 列中存储多行数据 在关系数据库中 不同表中的数据彼此之间可能存在一定的关系 从用户角度看 表是一张二维表 即 表由行和列两部分组成 表通过行和列来组织数据 通常称表中的一行为一条记录 称表中的一列为一个字段 表和列的命名规则 长度30个字节之内 必须以字母开头 由字母 数字 组成 不能使用保留字 非法的命名 可以用 引着 一个表内列名要唯一 表名在名称空间内必须唯一 Oracle中的数据类型 字符型 char ncharvarchar2 nvarchar2大对象数据类型 clob nclobblob bfile 二进制 二进制数据类型 Row LongRow数字型 number p s 时间和日期 Date 行数据类型 RowID URowID 15 2数据完整性与约束条件 数据完整性是关系数据库模型的基本原则 是用户在表上定义的一系列规则或约束条件 以及在表之间定义的一系列相互关系 数据完整性的作用 简单地说 就是强制要求数据库中只能接受正确的 合理的数据 防止错误的或无效的数据被插入到表中 分三种类型 实体完整性 主键的各个属性都不能为空值 参照完整性 外键或者取空值 或者等于被参照关系中的主键的某个值 用户自定义完整性 属性 或属性组 的取值应当满足用户定义的约束条件 DEPTNO的值必须与表DEPT中的DEPTNO值匹配 ENAME的每列都必须有一个值 EMPNO每行都必须有惟一的值 15 2数据完整性与约束条件 数据完整性实体完整性参照完整性用户自定义完整性 约束条件NotNullUniqueCheckPrimarykeyForeignkey约束级别列级约束表级约束 15 3管理表的策略 设计表的结构表的类型标准表 按堆组织 索引表 以B树索引段 外部表 操作系统文件中 分区表簇表表的位置及大小使用建表的相关选项 nologging 创建表 如果要在自己的方案中创建表 要求用户必须具有CREATETABLE系统权限 如果要在其他方案中建表 则要求用户必须具有CREATEANYTABLE系统权限 创建表时 Oracle会为该表分配相应的表段 表段的名称与表名完全相同 并且所有的数据都会被存放在该表段中 要求表的创建者必须指定表空间上具有空间配额或具有UNLIMITEDTABLESPACE系统权限 Createtable语句 Createtable schema table name column1datatype1 defaultexp1 column1 constraint column2datatype2 defaultexp2 column2 constraint table constraint oncommit delete preserve rows organizition heap index external partitionby tablespacetablespace name Logging nologging compress nocompress asquery 创建标准表 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 临时表 Oracle的临时表分为事务型和会话型 事务型临时表就是指在事务处理过程中插入的记录只在事务提交或回滚之前有效 一旦事务完成 表中的记录便被自动清除 会话型临时表指临时表中的数据在本次会话期间一直有效 直到会话结束 会话结束后表中针对此次会话的数据会自动清空 当在临时表上执行DML操作时 既不会加锁也不会将数据变化写到重做日志中 因此不能用临时表保存永久数据 创建临时表 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 用于指定溢出段所在的表空间 通过查询创建表 CTAS方式 SQL createtableemp1asselect fromempnologging 在创建大表时 建议带 查询创建表 约束及默认值定义不会被复制 查询创建表 不能带tablespace子句 可以复制表的结构 复制数据 可以复制表中的个别字段 外部表 Oracle允许以只读方式访问外部表中的数据 外部表指不是数据库中的表 而是操作系统 简称OS 文件中的表 使用createtable语句的organizationexternal子句创建外部表 使用Insert语句将OS文件中的数据装载到数据库中 从而实现SQL Loader的功能 最大用途 通过外部表可以把OS文件中的数据装载到Oracle数据库中 创建外部表 Step0 在C 准备一个department txt的文件 Step1 建立目录对象 connsys o123assysdba createdirectorydept diras C 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 dir1AS E external1 Altertabletest deptdefaultdirectorytest dir1 修改文件位置Altertableext departmentlocation new txt 修改访问参数Altertabletest deptaccessparameters fieldsterminatedby 删除外部表Droptabletest dept 删除目录对象Dropdirectorytest dir 15 5更改表 修改表Altertabletable nameAddcolumndatatype Modify columndatatype Dropcolumncolumn name renamecolumncolumn nametonewname添加表和列注释重命名表和重新组织表 添加列 Altertabledepartmentadd leadervarchar2 20 updateDatedatedefaultsysdate 添加新列的时候 如果表中有记录了 那就不能直接指定NotNull约束 必须要有一个带Default子句的NotNull约束 Altertabledepartmentadd notevarchar2 40 default 备注 notnull 修改列 把department表的deptno字段改为char 6 note字段改为varchar2 80 AltertabledepartmentModify deptnochar 6 notevarchar2 80 删除列 单独删除一列 Altertabledepartmentdropcolumnnotecascadeconstraints Altertabledepartmentdrop note cascadeconstraints 同时删除多列 Altertabledepartmentdrop updatedate note cascadeconstraints 部分删除 删除某个列 同时释放该列所占用的存储空间 如果是大表 这种删除操作会很耗时 为了避免在数据库使用高峰期间执行删除列的操作而占用过多的系统资源 可以通过把该列标记为unused状态 altertabledepartmentsetunused address cascadeconstraints 仅仅是逻辑删除 还需要进一步删除释放空间 altertabledepartmentdropunusedcolumns 添加表和列的注释 添加表注释 user tab comments可以查看 Commentontabletable nameIS text SQL commentontableemp1is anewtablecopyfromemp 添加列注释 user col comments可以查看 Commentoncolumntable name columnIS text SQL commentonemp1 empnois 雇员编号 重命名表和重新组织表 重命名表 SQL renamenew emptoemp1 本方案中 SQL altertableemp1renametonew emp 重新组织表 表放在不适合的区或摆脱碎片 重新组织表迁移到新的数据段中 SQL altertablenew empmove SQL altertablenew emptablespacemyts 15 6删减表和删除表 删减表 删除所有记录 保留表结构 Deletefromtable nameTruncatetabletable name删除表Droptabletable name cascadeconstraints 删减表 SQL Deletefromemp1 SQL descemp1 显示表结构 SQL Truncatetableemp1 二者的区别 delete不会释放空间 truncate可以清空数据及索引 释放空间 15 7维护约束 约束种类约束状态管理约束 定义约束 修改和删除约束 设置约束状态 设置约束的延迟检查 约束种类 NotNullUniqueCheckPrimarykeyForeignkey 只能列级约束列级 表级均可列级 表级均可列级 表级均可列级 表级均可 约束状态 激活与禁用状态激活状态 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 nameAdd CONSTRAINTconstraint name Constraint 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 ALT