数据库对象管理.ppt

数据库对象管理 在Oracle系统中 从某种程度上来讲 表是数据库中最重要 最基本的对象 是实际存储数据的地方 对数据库的许多管理和操纵 实际上就是对数据库中表的管理和操纵 完整性约束是提高数据质量的最重要和最常用的手段 索引是加快对表中数据检索的方式 也是一种重要的数据库对象 在Oracle系统中 表是数据库中的主要对象 是真正存储信息的对象 一般而言 表具有以下特征 代表实体表名在数据库中是唯一的由行和列组成行的顺序是任意的列的顺序是任意的列名在表中是唯一的 表属性的特点 对于所有类型的表 Oracle都允许开发人员和管理员规定各种表属性 他们会确定如下内容 哪一个表空间包含表 Oracle怎样将表物理存储在磁盘上 当从磁盘读取表数据时 Oracle怎样将它们与内存进行映射 Oracle怎样控制表上特定操作的日志 表管理 创建表修改表删除表插入数据修改数据删除数据 数据类型NUMBER可以用来表示所有的数值数据 fieldnameNUMBER precision scale VARCHAR2用来表示变长的字符串数据类型 fieldnameVARCHAR2 max length CHAR 保存固定长度字符串 最大长度为2KB DATE 保存固定长度的日期数据 BLOB 保存二进制大对象 通常用来保存图像和文档等二进制数据 CLOB 保存字符型大对象 varchar2数据类型最多只能保存4000个字符 如果要保存的字符串数据超过此范围 应使用clob数据类型 数值类型 数值类型可以用于存储整数 浮点数以及实数 在Oracle系统中 number数据类型具有精度 precision 和范围 scale 精度是数值中的数字总和 可以是1 38个数字之间的任何值 范围表示任何给定数值的小数点右边的位数 number 8 2 或number 缺省表示取最大取值范围 表示总位数8位 小数点后2位在创建表时 可以使用如下的语法格式声明number列 number precision scale 字符类型 字符数据类型可以用于声明包含了字母 数字数据的列 包括char nchar varchar2 nvarchar2 raw long和longraw 大对象等 char 保存固定长度字符串 最大宽度为2000B 默认宽度1个字节 nchar 保存固定长度字符串 用于存储unicode字符集数据 1 2000字节varchar2 与char类似 但它可以存储可变长的字符串 1 4000个字节nvarchar2 存储可变长的双字节字符数据 1 4000个字节raw 存储二进制数据 传输数据不需要数据类型的转换 最大宽度2000字节 long 存储变长字符串 长度达到2G longraw 存储图像 视频 音频等较大的数据 方便网络传输 最大宽度2G BLOB 保存二进制大对象 通常用来保存图像和文档等二进制数据 CLOB 保存字符型大对象 varchar2数据类型最多只能保存4000个字符 如果要保存的字符串数据超过此范围 应使用clob数据类型 最大宽度为4G 日期和时间 与1993年5月18日或2005年1月1日这样的时间点相关联的数据类型称为日期时间类型 可以使用以下数据类型创建表 timestamp和date两者区别 timestamp类型的秒精确到6位数字date类型没有小数部分的秒 系统提供了3种方式获取时间值 1 SYSDATE 函数获取当前系统时间 数据库服务器 默认格式是DD MON YY 2 TO DATE 函数用非默认格式输入日期值TO DATE 9月16 2008 monthdd yyyy TO DATE 9月16 2008 3 30 monthdd yyyy HH MIAM 3 DATE方法DATE 2009 03 10 将字符串转换成日期值日期格式为YYYY MM DD格式 createtabletemp tempdate1date tempdate2timestamp insertintotemp tempdate1 values SYSDATE insertintotemp tempdate1 values DATE 2009 03 10 insertintotempvalues SYSDATE SYSDATE 表的常见形式 标准表以堆形式组织的标准表示普通表 表中的数据存储的是未排序的集合 堆的含义是乱七八糟 在Oracle系统中 最基本的表类型是堆表 这里堆的含义是 数据是在磁盘上随机存储的 一般情况下 Oracle系统在将数据行写入数据块时不会考虑其它行的存储位置 当向堆表中插入数据时 数据库会将该数据写入第一个有足够自由空间的段中 当修改和删除数据行时 系统将为新的插入提供可用的空间 临时表临时表是那些只在事务处理和会话期间存在数据的表 数据会在事务处理或会话开始之后插入临时表 当事务处理或会话完成之后该表就会被删除 通过采用这种方式 开发人员可以在需要执行的应用逻辑运行期间访问临时存储区域 直到用户实际向表中插入数据时 系统才会为临时表分配空间 而与此相对的堆表会在createtable语句执行之后就分配一个区域 索引表 indexorganizationtable IOT 以索引形式组织的表 可以存储索引 以便提高查询性能 索引组织表是以牺牲插入性能和更新性能为代价而提供查询性能的方式 假如 要使用 英汉大词典 查询一些词的含义 那么当用户在词典中搜索词时 用户就要将书打开到这个词的附近位置 用户可以根据词的字母顺序指导找到这个词 然后再基于用户打开书的位置进行前后搜索 这是B树的实际示例 也是索引组织表的典型示例 创建表 创建表的最简单 最常见的方法的语法格式如下 createtable schema name default default 表Users的结构 创建表 例 创建表Users SQL语句如下 CREATETABLEUsers UserIdNumberPrimaryKey UserNameVarchar2 40 NOTNULL UserTypeNumber 1 UserPwdVarchar2 40 表的特性 CREATETABLEUsers UserIdNumberPrimaryKey UserNameVarchar2 40 NOTNULL UserTypeNumber 1 UserPwdVarchar2 40 tablespacedatatest 这里datatest是表空间 表示在datatest表空间中创建users表 系统默认在用户默认表空间中创建表 否则会在SYSTEM表空间中创建表 tablespace子句指定创建的表存放的表空间 storage子句设置系统存储表的存储参数 Oracle允许用户使用存储参数来在磁盘上存储表 如果只指定了表 没有设置存储参数 则系统自动采用所属表空间的默认存储参数设置 格式 storage INITIALNEXTPCTINCREASEMINEXTENTSMAXEXTENTS INITIAL 指定表中的数据分配的第一个盘区大小 以KB或MB为单位NEXT 指定存储表中的数据分配的第二个盘区大小 以KB或MB为单位PCTINCREASE 从第二个盘区之后 每个盘区相对于上一个盘区的增长比MINEXTENTS 指定允许为表中的数据所分配的最小盘区数目MAXEXTENTS 指定允许为表中的数据所分配的最大盘区数目 缺省为unlimited CREATETABLEUsers UserIdNumberPrimaryKey UserNameVarchar2 40 NOTNULL UserTypeNumber 1 UserPwdVarchar2 40 storage INITIAL50KNEXT100KMINEXTENTS2MAXEXTENTS10PCTINCREASE10 设置数据子块PCTFREE 设置数据块中空闲空间的使用方法 控制数据块的更新预留的空间 例 30 表示每个数据块的30 作为可利用的空闲空间 用于更新以在数据块内的数据行 其余70 用于插入新的数据行 默认为10 PCTUSED 设置数据块是否空闲的界限 例 40 表示当数据块的使用空间大于或等于40 该数据块被标识为不可用 CREATETABLEUsers UserIdNumberPrimaryKey UserNameVarchar2 40 NOTNULL UserTypeNumber 1 UserPwdVarchar2 40 storage INITIAL50KNEXT100KMINEXTENTS2MAXEXTENTS10PCTINCREASE10 PCTFREE15PCTUSED70 查询表的结构 Describetablename例 describeusers查询users表的结构 修改表 ALTERTABLE语句修改表 1 添加列 例 在表Users中添加一个列tmpcol SQL ALTERTABLEUsersADD tmpcolNUMBER 5 2 2 修改列名 例 将表Users中tmpcol列的名称修改为tmpcol 1 SQL ALTERTABLEUsersRENAMECOLUMNtmpcolTOtmpcol 1 修改表 3 删除列 例 将表Users中的列tmpcol 1删除 SQL ALTERTABLEUsersDROPCOLUMNtmpcol 1 4 将列设置为不可用 例 将表Users中的列tmpcol 1设置为不可用 SQL ALTERTABLEUsersSETUNUSED tmpcol 1 5 删除不可用的列 例 删除表Users中所有的不可用列 SQL ALTERTABLEUsersDROPUNUSEDCOLUMNS 修改列的定义 格式 altertablemodify 数据类型 约束条件 例 向users表增加一列 地址addressaltertableusersadd addresschar 30 更改该列的宽度altertableusersmodify addresschar 50 复制表结构 只复制表的结构 不需要原表中的数据 createtableusers2asselect fromuserswhere1 2 复制部分表createtableusers3asselectUserId UserNamefromusers 删除表 DROPTABLE语句删除表 例 删除表Users DROPTABLEUsers 数据完整性 1 定义primarykey 主键 约束 CREATETABLEUsers UserIdNumberPrimaryKey UserNameVarchar2 40 NOTNULL UserTypeNumber 1 UserPwdVarchar2 40 CREATETABLEUsers UserIdNumberconstraintPK IDPrimaryKey UserNameVarchar2 40 NOTNULL UserTypeNumber 1 UserPwdVarchar2 40 这里PK ID为主键约束名 CREATETABLEUsers UserIdNumber UserNameVarchar2 40 NOTNULL UserTypeNumber 1 UserPwdVarchar2 40 constraintPK IDprimarykey userid username 将多个列设为主键约束 删除主键约束altertableusersdropconstraintPK ID 2 定义NOTNULL约束 CREATETABLEUsers UserIdNumberconstraintPK IDPrimaryKey UserNameVarchar2 40 constraintEMP NAMENOTNULL UserTypeNumber 1 UserPwdVarchar2 40 这里EMP NAME为约束名 3 定义unique约束列值的唯一性约束 CREATETABLEUsers UserIdNumberPrimaryKey UserNameVarchar2 40 NOTNULL UserTypeNumber 1 UserPwdVarchar2 40 Phonevarchar2 12 constraintEMP phoneunique 4 定义CHECK约束createtableemployee IDnumber 5 primarykey namevarchar2 10 notnull agenumber 3 CHECK age20 addressvarchar2 30 5 定义FOREIGNKEY约束Createtabledepartment DIDnumber 5 primarykey Dnamevarchar2 20 notnull Createtableemployee EIDnumber 5 primarykey Enamevarchar2 10 notnull addressvarchar2 30 DIDnumber 5 constraintEMP DIDforeignkey DID referencesdepartment DID ondeletecascade 利用EM完成数据表的维护 可以利用EM完成数据表的创建 修改 删除 创建表 表管理器 管理页面 创建表 表管理页面 创建表 选择方案 创建表 选择表组织 修改表 查看和编辑表Users中的数据 删除表 删除选项窗口 插入数据 INSERT语句的基本使用方法如下所示 INSERTINTO 列名1 列名2 列名n VALUES 值1 值2 值n 例 向表Users中插入数据INSERTINTOUsers UserId UserName UserType UserPwd VALUES 1 Admin 1 Admin INSERTINTOUsers UserId UserName UserType UserPwd VALUES 2 Lee 1 Lee INSERTINTOUsers UserId UserName UserType UserPwd VALUES 3 User 2 Users INSERTINTOUsers UserId UserName UserType UserPwd VALUES 4 Zxh 2 Zxh COMMIT 将数据提交给数据库完成数据插入SELECT FROMUsers 插入数据 运行结果USERIDUSERNAMEUSERTYPEUSERPWD 1Admin1Admin2Lee1Lee3User2Users4Zxh2Zxh 使用select语句插入数据insertintousers2select fromusers insertintousers2 UserId Username selectUserId Usernamefromusers 修改数据 UPDATE语句的基本使用方法如下所示 UPDATESET列名1 值1 列名2 值2 列名n 值nWHERE 例 将用户Admin的密码修改为111111 UPDATEUsersSETUserPwd 111111 WHEREUserName Admin COMMIT 提交操作到数据库中修改SELECTUserName UserPwdFROMUsers 运行结果为 USERNAMEUSERPWD Admin111111LeelEEUserUsersZxhZxh 删除数据 DELETE命令删除表中的数据 DELETEWHERE 例 删除表Users中列UserName等于空 的数据 DELETEFROMUsersWHEREUserName COMMIT truncatetable语句 删除表中所有数据例 truncatetabletruncatetableusers 删除表 数据查询 SELECT语句的基本应用 SELECT语句的基本语法结构如下 SELECT子句 INTO子句 FROM子句 WHERE子句 GROUPBY子句 HAVING子句 ORDERBY子句 SELECT语句的基本应用 SELECT语句中各子句的说明 SELECT语句的基本应用 简单SELECT语句的执行结果 SELECT语句的基本应用 在SELECT语句中查询指定的列名 定义显示标题 在SELECT语句中设置列标题 设置查询条件 查询用户Admin 设置查询条件 使用LIKE关键字实现模糊查询oracle支持的通配符有 和 两种 表示0或多个字符 表示1个字符 例 从表Users中查询用户名为A开头的数据 SELECTUserNameAS用户名 UserPwdAS密码FROMUsersWHEREUserNamelike A 对查询结果排序 ORDERBY子句对指定结果集的排序 例 在查询用户信息时 按照用户名排序 SELECTUserNameAS用户名 UserPwdAS密码FROMUsersORDERBYUserName 使用统计函数 统计函数 使用统计函数 例 查询表Users中的员工总数 SELECTCOUNT FROMUsers 连接查询 简单的连接查询SELECTFROMINNERJOINON 序列管理 什么是序列序列是一个oracle整数 最多可有38个数字 序列的作用是由系统自动生成整形数值 作为表中标识列的值 当插入一条新纪录时 此类型的列的值自动加1 利用序列主要实现自动产生的主键值 创建序列 CREATESEQUENCE语句创建序列 例 创建序列USER S CREATESEQUENCEUSER S1startwith1序列初始值 默认为MINVALUE的值MINVALUE1序列最小值NOMAXVALUE序列最大值 无最大值 INCREMENTBY1序列增长NOCYCLE序列是否循环产生CACHE20 在缓冲中预分配20个序列值 创建序列 序列管理页面 创建序列 创建序列页面 修改序列 编辑序列 删除序列 确认删除页面DROPSEQUENCE语句也可以删除序列 例 删除序列USER S DROPSEQUENCEUSERMAN USER S 序列的使用 得到序列的新值 USER S NEXTVAL例如 设置一条SQL语句 作用是向表USERS中插入一个新的记录 INSERTINTOUSERS USER ID USER NAME values USER S NEXTVAL test 获取当前的序列值USER S currval例如 设置一条SQL语句 作用是向表USERS中插入一个新的记录INSERTINTOUSERS USER ID USER NAME VALUES USER S currval test 查看当前的序列值 selectUSER S1 currvalfromdual 修改序列参数创建序列之后可以修改以调整参数 ALTERSEQUENCEUSER Sincrementby10maxvalue2000cyclenocache 要改变序列的起始值start 需要先删除序列 在重新创建 dropsequenceUSER S 查询序列序列定义在USER SEQUENCE数据字典中 可以通过数据字典查询序列结构 descuser sequences select fromuser sequences 序列生成器与任何一个表都是无关的 一旦创建 每个表都可以引用 每引用一次 序列值就会改变一次 对于多用户对同一个表的插入操作非常有效 每个用户引用的序号不同 不会插入相同的行 同义词 同义词是表 索引 视图 序列 存储过程 包等模式对象的别名 创建同义词的语法为 create public synonym同义词for对象public 公共同义词 所有用户都可以使用 默认是private同义词 只能为某一个用户使用 同义词 为对象起的别名 可以用别名来代替对象名 对象 表 视图 索引 序列 过程 存储函数 存储包等 1 创建私有同义词为USERS表创建同义词usercreatesynonymusertest1forusers 如果指定为其他用户的对象 如 sss 创建如下 createsynonymusertestforsss users 创建公共同义词createpublicsynonymusertestforusers 同义词的使用 当定义了同义词后 就可以使用同义词代替基表的引用 insertintousertestvalues 5 uuuu 3 aaaa dfdsfs select fromusertest 当基表的名字改变后 只需要修改同义词的定义 查看同义词的定义在数据字典DBA SYNONYMS中可以查看同义词的信息 descdba synonyms select fromdba synonymswhereowner KKK 同义词的删除 格式 drop public synonym同义词dropsynonymusertest dropsysnonymsss usertest删除某一个用户的同义词 索引索引是关于纪录的位置信息的关键字表 在查询中如果有索引 则会先使用索引 将加快查询的速度 索引管理 索引的概念 UserName索引的存储和工作情况 创建索引 CREATEINDEX语句创建索引 create unique index索引名ontable表名 列名1 列名2 其中unique是创建唯一索引的参数 1 创建主键索引 PRIMARYKEY关键词指定主键列 2 创建惟一索引 UNIQUE关键词指定惟一索引 3 创建非唯一索引 1 创建主键索引主键是表中一列或一组列 它们的值可以唯一地标识表中的每一行 在创建和修改表时 可以定义主键索引 主键列的值不允许为空 createtableteachers tidnumberprimarykey namevarchar2 10 agenumber 3 addressvarchar2 100 2 创建唯一索引createuniqueindexindex usernameonusers username tablespaceusers tablespaceusers 用户可以指定索引存储的表空间 如果未指定会在用户的默认表空间中创建一个索引段 3 创建非唯一索引createindexindex usernameonusers username 定义组合列的索引createuniqueindexindex usernameonusers userid username 修改索引 ALTERINDEX语句可以修改索引 例 设置索引index username不可用 ALTERINDEXindex usernameUNUSABLEALTERINDEX中使用REBUILD关键词可重新使用索引index username ALTERINDEXindex usernameREBUILDRENAMETO子句可以重命名索引 例 将索引名index username重命名为index username 1 ALTERINDEXindex usernameRENAMETOindex username 1 索引的删除 Dropindexindex username 索引管理页面 索引管理页面 创建索引 创建索引页面 创建索引 索引选项页面 删除索引 DROPINDEX语句删除指定索引 例 删除索引index username DROPINDEXindex username 视图管理 视图的基本概念视图管理页面创建视图修改视图删除视图 视图概念视图是以不同的角度观察表中的数据 用视图观察表 基表没有变化 变化的是视图的设计 利用视图可以将表中用户访问的某些行和某些列创建视图 提供给用户 而不需要直接将基表提供给用户 视图特点着重于特定数据简化数据操作自定义数据组合分区数据 创建视图 CREATEVIEW语句创建视图 CREATEVIEWASSELECT语句 创建四个数据表Createtablestudent sidnumber 10 primarykey snamevarchar2 10 genderchar 1 agenumber Createtablecourse cidnumber 10 primarykey cnamevarchar2 20 tidnumber 10 Createtablecourseresult cidnumber 10 scorenumber 5 1 sidnumber 10 Createtableteacher tidnumber 10 primarykey tnamevarchar2 10 notnull genderchar 1 agenumber birthplacevarchar2 200 增加安全性的视图创建创建查询dennis成绩的视图createviewdennis ame cr scorefromcourseresultcr courseco studentstwherecr cid co cidandcr sid st sidandst sname dennis Select fromdennis view1 查询结果为姓名为 dennis 的学生的选课名和成绩 其他人的信息没有显示 保护了别人的隐私 为用户编辑数据创建视图Createviewstudent part1asselect fromstudentwheresidbetween1000and1100 对数据表中的数据实现了部分提取 还可以进行编辑操作 updatestudent part1setage 23wheresid 1000 commit 隐藏数据的复杂性所使用的sql语句很复杂 所查看的结果可能要多次用到 可以创建视图来隐藏数据的复杂性 将查询结果做成视图selectst sid st sname cr scorefromcourseresultcr courseco studentstwherecr cid co cidandcr sid ame 操作系统 Select fromos result 简化字段的书写createviewtsasselecttid tnametn gender age birthplacebpfromteacher select fromts insertintots tid tn values 1000 王强 commit 利用视图完成表的数据插入 修改 删除操作createviewteacher viewasselecttid tname gender agefromteacher insertintoteacher viewvalues 102 王刚 m 28 updateteacher viewsettname 邓云 age 30wheretname 王刚 Deletefromteacher viewwheretid 120 Commit createorreplaceviewteacher part2asselecttid tname decode sign 36 age 1 yes 1 no as youngteacher fromteacher deletefromteacher part2where youngteacher no commit 将年龄高于36岁的青年教师从表中删除 修改视图 在CREATEVIEW语