文件名称：oracle从入门到精通
文件大小：12.45MB
文件格式：PDF
更新时间：2019-01-05 10:18:13
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 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