Oracle 从入门到精通
一、SQL...........................................................................................................................................................8
1.1、基本概念：...................................................................................................................................8
1.2、数据库安全：...............................................................................................................................8
1.3、基本的SQL SELECT 语句.............................................................................................................8
1.4、SELECT语句...................................................................................................................................9
1、语法：.......................................................................................................................................9
2、SQL语句说明：.........................................................................................................................9
3、数字和日期都可以使用数学运算符建立表达式。 ...............................................................9
4、定义空（NULL）值...................................................................................................................9
5、别名...........................................................................................................................................9
6、spool +路径...........................................................................................................................10
7、连接操作符： ||...................................................................................................................10
8、文本字符串.............................................................................................................................10
9、DISTINCT.................................................................................................................................10
1.5、SQLPLUS 与 SQL 的关系...........................................................................................................10
1、SQLPLUS命令的功能：...........................................................................................................10
2、查询 SQLPLUS 命令...............................................................................................................10
3、SQLPLUSW 在 WINDOWS 下运行的分析器。 .........................................................................10
4、SQLPLUS 命令： ..................................................................................................................... 11
1.6、单行函数.....................................................................................................................................12
1、character字符类型函数： ...................................................................................................12
2、number数字类型函数.............................................................................................................15
3、时间类型函数：（date） .......................................................................................................15
1.7、嵌套函数：..................................................................................................................................21
1. 通用函数：.......................................................................................................................21
2. 条件表达式：...................................................................................................................24
3. 从多表中显示数据： .......................................................................................................25
1.8、用字函数产生的总计.................................................................................................................26
1.9、子查询：.....................................................................................................................................28
2.0、替换变量：.................................................................................................................................29
1.&.................................................................................................................................................29
2.&&...............................................................................................................................................29
2.1.环境变量：...................................................................................................................................29
2.2 格式化命令：................................................................................................................................30
2.3 做脚本文件的过程：....................................................................................................................31
2.3 数据操作语句：............................................................................................................................31
1. 插入...................................................................................................................................31
2. 删除...................................................................................................................................31
3. 更新...................................................................................................................................31
4. MERGE语句.........................................................................................................................32
5. 事务（transaction）： ...................................................................................................32
2.4 创建和管理表................................................................................................................................33
1、表（TABLE）基本的存储单位，由行和列组成。 ...............................................................33
2、方案：一个用户所有对象的命名集合。 .............................................................................34
3、CTAS（子查询建表）：...........................................................................................................34
4、截取：.....................................................................................................................................35
资源来自网络，仅供学习！
Oracle 从入门到精通
第 2 页，共 106 页
5、给表加注释：COMMENT...........................................................................................................36
6、约束条件：.............................................................................................................................36
2.5.视图 （VIEW）.............................................................................................................................37
2.6、序列：.........................................................................................................................................39
2.7、索引：.........................................................................................................................................40
2.8 控制用户的访问............................................................................................................................41
1.数据库的安全性.......................................................................................................................41
2.角色：.......................................................................................................................................41
3.使用集合操作...........................................................................................................................42
4.ORDER BY 子句：.....................................................................................................................42
5.GROUP BY 子句的增强.............................................................................................................43
6.GROUPING 函数.........................................................................................................................43
2.9 高级子查询....................................................................................................................................44
1. 成对子查询：...................................................................................................................44
2.层次查询...................................................................................................................................44
二、Management：.......................................................................................................................................45
1.Oracle的构件和组件.......................................................................................................................45
2.数据库的物理结构：.......................................................................................................................46
1.控制文件...................................................................................................................................46
2. 数据文件...........................................................................................................................46
3. 重做日志文件...................................................................................................................46
4. data file 数据文件： .................................................................................................46
5. 作用：存放数据。...........................................................................................................46
6. 数据文件大小可以扩展。 ...............................................................................................46
7. tablespace 表空间：一个或多个数据文件的逻辑组成。 .........................................46
8. redo log file 重做日志文件 .....................................................................................46
9. control file 控制文件 ...............................................................................................46
10. parameter file 初始化参数文件 ...............................................................................46
11. password file 口令文件 ...........................................................................................47
12. archived log file 归档日志文件 .............................................................................47
3.instance 实例/例程.......................................................................................................................47
4、进程结构.........................................................................................................................................49
1. 用户进程：开始于数据库用户请求连接数据库 ...........................................................49
2. 服务进程：与ORA实例连接，开始于用户会话的建立。 .............................................49
3. 后台进程：当ORA实例启动时启动 .................................................................................49
1. DBWR 数据库写进程.......................................................................................................49
2. LGWR 重作日志写进程...................................................................................................50
6.CKPT 检查点进程.................................................................................................................50
7.ARCn 归档进程（可选）.....................................................................................................50
8.LOGICAL STRUCTURE 逻辑结构 ............................................................................................50
5、OEM ORACLE 企业管理器...............................................................................................................51
6.管理ORA实例.....................................................................................................................................51
7.启动过程：.......................................................................................................................................52
1. NOMOUNT 实例启动阶段...................................................................................................52
2. MOUNT 数据库装载阶段...................................................................................................52
3. OPEN 打开数据库.............................................................................................................52
资源来自网络，仅供学习！
Oracle 从入门到精通
第 3 页，共 106 页
8.启动命令：.......................................................................................................................................52
1. 在关闭状态下执行...........................................................................................................53
2. 切换命令：不能跳级切换...................................................................................................53
3. 关闭过程与启动逆向； ...................................................................................................53
9.监视诊断文件：...............................................................................................................................53
10.BACKGROUND TRACE FILES 后台进程跟踪文件 ............................................................................53
11.user TRACE FILES 用户跟踪文件 ................................................................................................54
12.创建数据库.....................................................................................................................................54
1. 创建前的准备：...............................................................................................................54
2. 创建方法：.......................................................................................................................55
13、UNIX 操作系统环境变量 ...........................................................................................................55
14、手动创建数据库...........................................................................................................................55
15、使用数据字典和动态性能视图 ...................................................................................................56
1. 数据字典...........................................................................................................................56
2. 数据字典的分类：...........................................................................................................56
3. 动态性能表：...................................................................................................................56
16、维护重做日志文件.......................................................................................................................57
17、管理表空间和数据文件...............................................................................................................59
18、表空间的空间管理（区的管理）： .............................................................................................61
1、本地管理：.............................................................................................................................61
2、数据字典管理表空间：.........................................................................................................61
3、存储参数：.............................................................................................................................61
4、表空间状态：.........................................................................................................................61
5、查看表空间信息：.................................................................................................................62
6、重定义表空间的大小.............................................................................................................62
7、操作表空间：.........................................................................................................................62
8、移动数据文件：.....................................................................................................................62
9、删除表空间：.........................................................................................................................63
19、存储结构和关系...........................................................................................................................63
1、段类型：.................................................................................................................................63
2、区：.........................................................................................................................................64
3、数据库块.................................................................................................................................64
4、9I提供非标准块.....................................................................................................................64
5、标准块大小.............................................................................................................................64
6、非标准块的大小.....................................................................................................................64
7、数据块的内容：.....................................................................................................................65
8、块的空间利用参数：.............................................................................................................65
9、数据块管理：.........................................................................................................................65
10、管理回滚段（Undo）的数据 ...............................................................................................65
20、Undo段的类型：...........................................................................................................................66
1.NON-SYSTEM类型：...................................................................................................................66
2.SYSTEM类型：...........................................................................................................................66
3.自动UNDO段管理的其他参数： ...............................................................................................67
21、管理表...........................................................................................................................................67
1.创建表提示：...........................................................................................................................67
2.创建临时表...............................................................................................................................68
资源来自网络，仅供学习！
Oracle 从入门到精通
第 4 页，共 106 页
3.修改存储参数和块空间利用参数： .......................................................................................68
4.手动分配区：...........................................................................................................................68
5.非分区表的重组.......................................................................................................................68
6.删除列：...................................................................................................................................68
7.重命名表中的一列：...............................................................................................................68
8.标记列不再使用：...................................................................................................................69
9.删除不使用的列：...................................................................................................................69
10.继续列的删除操作：.............................................................................................................69
11.得到表的信息：.....................................................................................................................69
22、管理索引（index）......................................................................................................................69
1.索引的分类：...........................................................................................................................69
2.索引结构：...............................................................................................................................69
3.存储参数：...............................................................................................................................70
4.创建B-TREE索引：...................................................................................................................70
5.索引PCTFREE的变化：.............................................................................................................70
6.创建索引的提示：...................................................................................................................70
7.创建位图索引：.......................................................................................................................71
8.改变索引参数:.........................................................................................................................71
9.重建索引：...............................................................................................................................71
10.在线重建索引：（建议不使用） ...........................................................................................71
11.合并索引：.............................................................................................................................71
12.删除索引：.............................................................................................................................71
13.确定未使用的索引：.............................................................................................................71
14.查看索引信息：.....................................................................................................................72
23、管理口令安全和资源...................................................................................................................72
1.口令帐户锁定：.......................................................................................................................72
2.自动锁定，可以手动解锁.......................................................................................................72
3.口令的到期和过期：...............................................................................................................72
4.口令历史：................................................................................................................................72
5.口令的校验：...........................................................................................................................73
6.用户提供的校验函数：...........................................................................................................73
7.口令校验函数：.......................................................................................................................73
8.创建profile口令设置：.........................................................................................................73
9.修改 profile ：口令设置 .....................................................................................................73
10.删除 profile： 口令设置 ...................................................................................................73
24、资源管理：...................................................................................................................................73
1.启动资源限制通过：...............................................................................................................74
2.会话级参数：...........................................................................................................................74
3.调用级参数：...........................................................................................................................74
4.创建profile：资源配制.........................................................................................................74
5.查看：.......................................................................................................................................74
24、管理用户.......................................................................................................................................74
1. 用户：...............................................................................................................................74
2.数据库的方案：.......................................................................................................................74
3.创建用户的步骤：...................................................................................................................75
4.创建一个新的用户：数据库认证 ...........................................................................................75
资源来自网络，仅供学习！
Oracle 从入门到精通
第 5 页，共 106 页
评论0