没有合适的资源?快使用搜索试试~ 我知道了~
Oracle8i_9i数据库基础.doc
需积分: 9 29 下载量 199 浏览量
2007-04-16
12:07:48
上传
评论
收藏 4.66MB DOC 举报
温馨提示
试读
64页
Oracle8i_9i数据库基础.doc
资源详情
资源评论
资源推荐
Oracle8i/9i
数据库基础
(初稿)
目 录
第一部分 Oracle SQL*PLUS 基础...................................................................................................21
第一章 Oracle 数据库基础................................................................................................................21
§1.1 理解关系数据库系统(RDBMS)..................................................................................21
§1.1.1 关系模型..................................................................................................................21
§1.1.2 Codd 十二法则........................................................................................................22
§1.2 关系数据库系统(RDBMS)的组成..............................................................................22
§1.2.1 RDBMS 内核...........................................................................................................23
§1.2.2 数据字典概念..........................................................................................................23
§1.3 SQL、SQL*Plus 及 PL/SQL.............................................................................................23
§1.3.1 SQL 和 SQL*PLUS 的差别....................................................................................23
§1.3.2 PL/SQL 语言...........................................................................................................25
§1.4 登录到 SQL*PLUS............................................................................................................26
§1.4.1 UNIX 环境...............................................................................................................26
§1.4.2 Windows NT 和 WINDOWS/2000 环境................................................................28
§1.5 常用 SQL*PLUS 附加命令简介.......................................................................................30
§1.5.1 登录到 SQL*PLUS.................................................................................................31
§1.5.2 EXIT 和 QUIT.........................................................................................................31
§1.5.3 DESCRIBE(显示表、视图结构)...........................................................................32
§1.5.4 LIST(列出)命令......................................................................................................32
§1.5.5 Change(替换字符串)命令......................................................................................33
§1.5.6 Append(追加字符串)命令.................................................................................33
§1.5.7 Save 保存当前缓冲区命令到文件.........................................................................34
§1.5.8 GET 将命令文件读到缓冲区.................................................................................34
§1.5.9 SPOOL 将信息记录到文件中................................................................................35
§1.5.10 再运行当前缓冲区的命令...................................................................................35
§1.6 常用数据字典简介.............................................................................................................36
§1.7 ORACLE 数据类型............................................................................................................38
§1.8 SQL 语句基础....................................................................................................................44
§1.8.1 SQL 语句所用符号.................................................................................................44
§1.8.2 简单 select 查询.......................................................................................................45
§1.9 伪列及伪表.........................................................................................................................46
§1.10 使用 SQL Worksheet 工作...............................................................................................46
第二章 查询基础................................................................................................................................50
§2.1 SELECT 语句.....................................................................................................................50
§2.2 SQL 中的单记录函数........................................................................................................50
§2.2.1 单记录字符函数.....................................................................................................51
§2.2.2 单记录数字函数.....................................................................................................56
§2.2.3 单记录日期函数.....................................................................................................62
§2.2.4 单记录转换函数.....................................................................................................65
§2.2.5 其它的单记录函数.................................................................................................69
§2.3 SQL 中的组函数................................................................................................................74
§2.3.1 多记录组函数..........................................................................................................74
§2.3.2 带 GROUP BY 的计算...........................................................................................75
§2.3.3 用 HAVING 来限制分组的计算............................................................................76
§2.4 控制和格式化输出.............................................................................................................77
§2.4.1 用 ORDER BY 子句来对结果进行排序...............................................................77
§2.4.2 用 BREAK 命令 来对结果进行排列....................................................................77
§2.4.3 用 COMPUTE 命令对结果进行格式化................................................................80
§2.5 配置会话环境.....................................................................................................................83
§2.5.1 ARRAYSIZE(取回的行数)...............................................................................84
§2.5.2 AUTOCOMMIT(自动提交)...................................................................................84
§2.5.3 LINESIZE(行显示宽度).........................................................................................84
§2.5.4 LONG(长类型显示字节数)....................................................................................84
§2.5.5 PAGESIZE(页行数)................................................................................................85
§2.5.6 PAUSE(暂停)...........................................................................................................85
§2.5.7 SPACE(列间空格)...................................................................................................85
§2.5.8 Termout (启/停屏幕显示).......................................................................................85
§2.5.9 ECHO (启/停命令显示)..........................................................................................86
§2.5.10 TRANSACTION (启动事务)...............................................................................86
§2.5.11 SHOW ALL(列出所有参数).................................................................................86
§2.6 格式化输出.........................................................................................................................88
§2.6.1 一般数据的格式化输出.........................................................................................88
§2.6.2 日期的格式化输出.................................................................................................90
§2.7 加标题.................................................................................................................................91
§2.8 建立简单报告.....................................................................................................................91
§2.9 输入变量.............................................................................................................................93
第三章 表及索引的定义操作...........................................................................................................96
§3.1 建立表结构.........................................................................................................................96
§3.1.1 建立表结构命令.....................................................................................................96
§3.1.2 建立表结构例子.....................................................................................................98
§3.1.3 建立临时表结构.....................................................................................................99
§3.3 修改表结构.......................................................................................................................100
§3.3.1 修改表结构命令...................................................................................................101
§3.3.2 修改表结构例子...................................................................................................101
§3.3.3 删除表结构............................................................................................................103
§3.3.4 使用 CHECK 作限制约束....................................................................................104
§3.3.5 使用 UNRECOVERABLE 创建表.......................................................................104
§3.3.6 将表移动到新的数据段或新的表空间...............................................................105
2
§3.3.7 手工分配表的存储空间.......................................................................................106
§3.3.8 标记不使用的列和删除不使用的列...................................................................107
§3.3 主键...................................................................................................................................109
§3.3.1 创建主键................................................................................................................109
§3.3.2 改变主键................................................................................................................111
§3.3.3 删除主键................................................................................................................112
§3.4 外部键...............................................................................................................................112
§3.4.1 建立外部键............................................................................................................113
§3.4.2 修改外部键............................................................................................................115
§3.4.3 删除外部键............................................................................................................115
§3.5 索引...................................................................................................................................115
§3.5.1 建立索引................................................................................................................115
§3.5.2 修改索引................................................................................................................117
§3.5.3 删除索引................................................................................................................118
§3.6 新索引类型.......................................................................................................................118
§3.6.1 基于函数的索引....................................................................................................118
§3.6.2 反向键索引............................................................................................................120
§3.6.3 索引组织表............................................................................................................120
§3.7 抽象数据类型的使用......................................................................................................122
§3.8 大数据类型的使用...........................................................................................................123
§3.8.1 可用数据类型.......................................................................................................123
§3.8.2 为 LOB 数据类型指定存储..................................................................................123
§3.8.3 操作和检索 LOB 数据..........................................................................................125
§3.9 表和索引有关的数据字典..............................................................................................128
§3.9.1 表和索引数据字典...............................................................................................128
§3.9.2 数据字典查询例子...............................................................................................128
第四章 视图、同义词和序列.........................................................................................................132
§4.1 视图...................................................................................................................................132
§4.1.1 使用视图来修改表中数据...................................................................................132
§4.1.2 创建一个新视图...................................................................................................132
§4.1.3 删除一个视图.......................................................................................................134
§4.1.4 改变视图................................................................................................................135
§4.2 实体视图(MATERIALIZED VIEW)...............................................................................135
§4.2.1 创建实体视图.......................................................................................................135
§4.2.2 创建实体视图日志...............................................................................................142
§4.2.3 修改实体视图.......................................................................................................143
§4.2.4 修改实体视图日志...............................................................................................146
§4.2.5 实体视图完整例子...............................................................................................146
§4.3 序号(sequence).................................................................................................................150
§4.3.1 建立序号................................................................................................................150
§4.3.2 修改序号................................................................................................................151
§4.3.3 使用序号................................................................................................................152
§4.3.4 删除序号................................................................................................................152
§4.4 同义词...............................................................................................................................152
3
§4.4.1 建立同义词............................................................................................................153
§4.4.2 删除同义词............................................................................................................154
§4.5 视图、同义词和序列有关的数据字典..........................................................................154
第五章 簇与分区..............................................................................................................................155
§5.1 簇( cluster ).......................................................................................................................155
§5.1.0 簇概念....................................................................................................................155
§5.1.1 建立簇....................................................................................................................157
§5.1.2 改变簇....................................................................................................................160
§5.1.3 删除簇....................................................................................................................160
§5.1.4 删除簇索引............................................................................................................161
§5.1.5 收集簇信息............................................................................................................161
§5.2 分区...................................................................................................................................163
§5.2.1 分区的优点............................................................................................................163
§5.2.2 分区的方法............................................................................................................164
§5.2.3 创建表的分区.......................................................................................................165
§5.2.3 创建索引的分区...................................................................................................170
§5.2.4 维护表分区和索引分区.......................................................................................173
§5.3 簇与分区有关的数据字典..............................................................................................174
§5.3.1 分区、簇数据字典列表.......................................................................................175
§5.3.2 基本的分区、簇信息查询...................................................................................175
第六章 使用 SQL 进行数据操作....................................................................................................176
§6.1 INSERT 操作....................................................................................................................176
§6.1.1 用文字插入操作...................................................................................................177
§6.1.2 用子查询插入操作...............................................................................................177
§6.2 UPDATE 操作..................................................................................................................178
§6.2.1 用文字更新操作...................................................................................................179
§6.2.2 用查询更新操作...................................................................................................179
§6.2.3 用相关子查询更新操作.......................................................................................180
§6.3 DETELE 操作...................................................................................................................181
§6.3.1 用 delete 删除全部记录的操作............................................................................181
§6.3.2 用 delete 有条件删除部分记录............................................................................181
§6.3.3 用 delete 分段删除大量记录................................................................................182
§6.4 insert、delete 及 update 的提交和撤消...........................................................................183
§6.4.1 自动提交的设置...................................................................................................183
§6.4.2 保留点和撤消.......................................................................................................184
第七章 复杂查询语句的使用.........................................................................................................187
§7.1 复杂查询语句的使用......................................................................................................187
§7.1.1 相关子查询............................................................................................................187
§7.1.2 外连接....................................................................................................................187
§7.1.3 自我连接................................................................................................................188
§7.1.4 UNION,INTERSECT 及 MINUS.........................................................................189
§7.2 创建复杂的视图...............................................................................................................190
§7.2.1 分组视图................................................................................................................190
§7.2.2 合计视图................................................................................................................191
4
§7.2.3 组合视图................................................................................................................191
§7.3 家族树...............................................................................................................................191
§7.3.1 排除单一体和分枝...............................................................................................192
§7.3.2 遍历至根................................................................................................................193
§7.4 在 from 中使用视图.........................................................................................................194
第八章 一些高级的用法.................................................................................................................196
§8.1 关于 DECODE..................................................................................................................196
§8.1.1 DECODE 中的 if-then-else 逻辑..........................................................................196
§8.1.2 DECODE 的简单例子..........................................................................................196
§8.1.3 DECODE 实现表的转置......................................................................................197
§8.2 关于访问远程数据库......................................................................................................200
§8.2.1 数据库链接............................................................................................................200
§8.2.2 使用同义词获得本地透明...................................................................................202
§8.2.3 在视图中使用 user 伪列.......................................................................................202
§8.2.4 使用 COPY 功能...................................................................................................203
§8.2.5 管理 Oracle 名称服务器.......................................................................................204
§8.3 关于上下文的使用...........................................................................................................205
§8.3.1 设置上下文选项...................................................................................................205
§8.3.2 为上下文查询设置表...........................................................................................206
§8.3.3 优化文本索引.......................................................................................................208
§8.4 关于维数(DIMENSION)............................................................................................208
§8.4.1 CREATE DIMENSION 语法................................................................................208
§8.4.2 创建维的例子.......................................................................................................210
第九章 安全管理..............................................................................................................................211
§9.1 CREATE USER 命令.......................................................................................................211
§9.2 建立用户...........................................................................................................................212
§9.2.1 外部验证(Authenticated )用户........................................................................212
§9.2.2 全局(Globally)验证用户-企业验证.....................................................................212
§9.3 ALTER USER 命令..........................................................................................................213
§9.4 DROP USER 命令............................................................................................................213
§9.5 GRANT 命令与 REVOKE 命令......................................................................................214
§9.5.1 GRANT 命令.........................................................................................................214
§9.5.2 REVOKE 命令......................................................................................................215
§9.6 权限和角色.......................................................................................................................215
§9.6.1 建立角色................................................................................................................216
§9.6.2 给角色授权............................................................................................................217
§9.6.3 授权角色给用户...................................................................................................217
§9.7 有关的数据字典...............................................................................................................217
§9.7.1 与用户、角色与权限有关的数据字典...............................................................218
§9.7.2 查询数据字典信息...............................................................................................218
第十章 其它一些常见问题及技巧.................................................................................................220
§10.1 一些常见问题.................................................................................................................220
§10.1.1 Oracle 与 2000 年问题........................................................................................220
§10.1.2 如何正确插入日期数据.....................................................................................221
5
剩余63页未读,继续阅读
yuchenhan
- 粉丝: 0
- 资源: 4
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功
评论0