Oracle学习笔记

Oracle 学习笔记学习笔记 Oracle 学习笔记 1 这是我的 Oracle 学习笔记系列的第一篇 主要总结了 Oracle 的体系结构 要 学好 Oracle 就要先了解 Oracle 的运行机制和原理 把今天学习的内容整理 了一个图 备忘 点击可放大 另外 还有几个语句另外 还有几个语句 sql plus sql plus SYS 用户登录 SQL as sysdba 解锁 scott 用户 SQL alter user scott identified by tiger account unlock 切换到 scott 用户 SQL conn scott tiger Null 值参与列计算 SQL select ename sal comm sal nvl comm 0 as salary from emp sql plus 中用变量查询 SQL variable va number SQL declare va 7788 SQL select empno ename sal from emp where empno va 看内存中包含 select from 的 SQL 语句 SQL select sql text from v sqlarea where sql text like select from 看内存 SGA 资源情况 SQL select from v sgastat 刷内存共享池 SQL alter system flush shared pool 强制重启实例 SQL startup force 在会话中修改日期格式 SQL alter session set nls date format yyyy mm dd 如果要永久修改 windows 下可在注册表中建立一个名为 nls date format 的 DWORD 值 UNIX LINUX 直接配置环境变量 关于共享池和关于共享池和 SQLSQL 语句同性能的关系 语句同性能的关系 由于共享池中缓存最近 SQL 语句的特性 所以在构建 SQL 语句时要特别注意 大小写不同 空格 顺序不同都会被 Oracle 认为是不同的 SQL 语 句 只有完 全一样的 SQL 语句才会从共享池中保存的 SQL 语句中重复使用 而由于编写 SQL 语句的不规范将会产生性能问题 所以要做到 1 尽量规范 SQL 语句 用统一的格式 大小写 顺序 空格 缩进 变量名格 式都有相应的规范并严格遵守 2 在大量的同一格式的 SQL 语句查询时 比如只有 where 条件的值不同 应 尽量使用变量 否则共享池将被大量的垃圾查询占满而使常用的 SQL 不被缓存 影响效率 3 PL SQL 或者是复杂的数据查询 可以写存储过程 用带进参数的方式保证 共享池中的 SQL 语句一致 4 适当调整 shared pool size 的大小 一句话观点 一句话观点 备份和恢复数据库应该用物理备份的方式而不要用导出和导入备份和恢复数据库应该用物理备份的方式而不要用导出和导入 功能 除非你的数据库数据量很少 而且都不太重要 功能 除非你的数据库数据量很少 而且都不太重要 Oracle 学习笔记 学习笔记 2 今天的内容有点多 分成了两篇 这里是比较简单的第一篇 是总结安装 启 动和关闭的过程 先上图 点击放大 一些一些 SQLSQL 命令和语句 命令和语句 建立数据库时加 cache 子句可以把表同时建立在内存中 读取时优先读取该表 SQL create table emp1 cache as select from emp 查看内存中的表 SQL select table name cache from user tables 查询参数信息 show parameter 参数类型 SQL show parameter process select from v parameter where name like 参数类型 SQL select from v parameter where name like session 生成 pfile 和 spfile 从 spfile 生成 pfile SQL create pfile from spfile 从 pfile 生成 spfile SQL create spfile from pfile 按步骤启动数据库 SQL startup nomount 仅启动例程 SQL alter database mount 再加载数据库 SQL alter database open read only 再打开数据库 只读模式 启动时指定参数文件 SQL startup pfile database initSID2 ora 以受限方式启动 SQL startup restrict 把数据库设置为受限模式 SQL alter system enable restricted 修改参数文件内容 SQL alter system set control files control01 ctl scope spfile 关于关于 OracleOracle 的故障查找 的故障查找 1 发生故障时的第一步要做的是备份当前的数据 这叫保留事故现场 以免处 理故障失败时无法再现初始故障 2 分步骤启动 确定故障所在 如果第 1 步失败 则要先检查共享内存 参数 文件配置等 如果在第 2 步失败 则说明极有可能是控制文件出错 要先检查 警告文件中的警告信息 恢复控制文件再说 如果在第 3 步失败 则要检查数 据文件和日志文件是否完整可用 也可根据警告文件中的警告信息来排查故障 3 确认三大核心文件 控制文件 数据文件 日志文件 的检查点是否一致 这是数据库能启动的唯一标志 一句话观点 一句话观点 Oracle Oracle 中的绝大部分故障 都可以从启动的过程中分析出故障中的绝大部分故障 都可以从启动的过程中分析出故障 的原因并加以解决 的原因并加以解决 Oracle 学习笔记 学习笔记 3 这一篇是总结 Oracle 的数据库管理 database 重点是其物理存储部分 图在 下面 点击放大 一些一些 SQLSQL 命令 命令 手工切换重做日志文件组 SQL alter system switch logfile 添加联机重做日志文件组 SQL alter database add logfile group 4 oradata log4a log oradata log4b log size 10M 其中的 10M 是指每个日志成员文件的大小 添加联机重做日志文件组成员 SQL alter database add logfile member oradata log4c log to group 4 oradata log3c log to group 3 删除联机重做日志文件组 当前组不能删除 至少保留两组 SQL alter database drop logfile group 4 删除联机重做日志文件组成员 只有一个成员时不能删除 SQL alter database drop logfile member oradata log4a log 清除日志文件内容 SQL alter database clear logfile oradata log3c log 关于三大核心文件的几点注意事项 关于三大核心文件的几点注意事项 1 控制文件最多可以有 8 个 多个控制文件最好放在不同的物理磁盘上 有一 个出错时可以从其他文件恢复 2 重做日志文件组是按顺序写 循环写 至少需要两个组 可以有多个组 每 个组可以有多个成员 每个成员文件最好也放在不同的物理磁盘上 当归档模 式下日志文件占用空间过大时 可以删除部分组来节省空间 但记住从控制文 件中删除之后还必须删除物理文件才能腾出空间 3 不可以删除当前正在使用的重做日志文件组 所以要删除时可手动切换 switch 当前日志文件组后再删除 4 不可以删除重做日志文件组的最后一个成员 若要删除 可直接删除该组 5 必须要保留两个重做日志文件组支持正常运行 如果要想删除其中一组是不 可能的 但可以清除 clear 其中的数据 一句话观点 一句话观点 成功启动成功启动 OracleOracle 数据库的唯一条件是三大核心文件 控制文件 数据库的唯一条件是三大核心文件 控制文件 数据文件 日志文件 的检查点一致 数据文件 日志文件 的检查点一致 Oracle 和和 SQL 基础基础 1 Windows1 Windows 下安装 卸载下安装 卸载 OracleOracle 的相关事项 的相关事项 1 Oracle 1 Oracle 服务管理 服务管理 Oracle10gOracle10g 运行时需要运行时需要 800MB800MB 左右的内存 安装完左右的内存 安装完 OracleOracle 后 会发现在后 会发现在 WindowsWindows 服务中自动开启了很多个服务中自动开启了很多个 OracleOracle 相关的服务 其实相关的服务 其实 OracleOracle 一般只需一般只需 要启动要启动 OracleOracle 和和 OracleOracle 监听两个服务就可以了 将其他的监听两个服务就可以了 将其他的 OracleOracle 服务停止 服务停止 可以减轻内存占用量 加快机器运行速度 可以减轻内存占用量 加快机器运行速度 2 2 卸载卸载 OracleOracle 时注意事项 时注意事项 a a 停止所有的停止所有的 OracleOracle 服务 服务 b b 使用使用 OracleOracle 卸载工具卸载工具 oracleoracle universaluniversal installerinstaller 卸载卸载 OracleOracle c c 修改修改 windowswindows 注册表 删除注册表 删除 OracleOracle 相关信息 相关信息 OracleOracle 软件相关键值 软件相关键值 HKEY LOCAL MACHINE SOFTWARE OracleHKEY LOCAL MACHINE SOFTWARE Oracle OracleOracle 服务相关键值 服务相关键值 HKEY LOCAL MACHINE SYSTEM CurrentControl Set ServicesHKEY LOCAL MACHINE SYSTEM CurrentControl Set Services OracleOracle 事件日志键值 事件日志键值 HKEY LOCAL MACHINE SYSTEM CurrentControl Set EventLog ApplicationHKEY LOCAL MACHINE SYSTEM CurrentControl Set EventLog Application d d 删除删除 OracleOracle 系统目录 系统目录 e e 删除删除 OracleOracle 环境变量 环境变量 f f 删除程序菜单项中的删除程序菜单项中的 oracleoracle 项 项 g g 重启机器后删除重启机器后删除 oracleoracle 的工作目录 的工作目录 注意 注意 WindowsWindows 环境下卸载环境下卸载 OracleOracle 一定要按上述步骤 否则有可能卸载失败 一定要按上述步骤 否则有可能卸载失败 或者重新安装失败 或者重新安装失败 2 Oracle2 Oracle 安装后默认是没有安装图形化操作工具的 如果使用图形化操作界面 安装后默认是没有安装图形化操作工具的 如果使用图形化操作界面 可以安装 可以安装 1 Oracle 1 Oracle HomeHome Client OracleClient Oracle 官方提供的一个用官方提供的一个用 javajava swingswing 做的图形化界做的图形化界 面管理工具 比较庞大面管理工具 比较庞大 侧重于 侧重于 OracleOracle 日志归档 高级复制 方案优化等底日志归档 高级复制 方案优化等底 层管理 层管理 2 Sql 2 Sql developerdeveloper OracleOracle 提供一款也是基于提供一款也是基于 JavaJava 开发的图形化界面工具 开发的图形化界面工具 侧重于数据管理和侧重于数据管理和 SqlSql 语句编写执行等 语句编写执行等 3 PL SQL 3 PL SQL developerdeveloper 和 和 sqlsql developerdeveloper 类似 侧重于类似 侧重于 OracleOracle 的的 plpl sqlsql 语语 句编写和执行 句编写和执行 4 Navicat 4 Navicat LiteLite 是一款比较优秀的图形化数据库管理软件 分为商业版和 是一款比较优秀的图形化数据库管理软件 分为商业版和 个人免费版 功能强大 即可提供底层管理 也同时提供了强大的数据管理 个人免费版 功能强大 即可提供底层管理 也同时提供了强大的数据管理 sqlsql 语句编写和执行等功能 语句编写和执行等功能 3 3 使用使用 OracleOracle 的的 plsqlplsql 命令行执行注意事项 命令行执行注意事项 1 Oracle 1 Oracle 数据库插入 删除和更新操作后必须使用数据库插入 删除和更新操作后必须使用 commit commit 命令提交事务命令提交事务 后 才能使数据库真正发生改变 后 才能使数据库真正发生改变 2 Oracle 2 Oracle 数据库对数据库对 SQLSQL 语句的大小写不敏感 同时也支持运算符 日期型的语句的大小写不敏感 同时也支持运算符 日期型的 数据类型也可以进行算术运算 日期型数据类型直接和整数运算相当于加减多数据类型也可以进行算术运算 日期型数据类型直接和整数运算相当于加减多 少天 少天 3 3 查询语句中使用字符串和日期时 要用单引号查询语句中使用字符串和日期时 要用单引号 括起来 字符串大小写括起来 字符串大小写 敏感 日期类型格式敏感 缺省日期格式为 敏感 日期类型格式敏感 缺省日期格式为 DD MON YY DD MON YY 获取当前缺省日 获取当前缺省日 期格式期格式 sqlsql 语句为 语句为 selectselect sysdatesysdate fromfrom dualdual 4 Oracle4 Oracle 的字符串拼接符 的字符串拼接符 是是 OracleOracle 的字符串拼接符 相当于的字符串拼接符 相当于 javajava 中的字符串拼接符中的字符串拼接符 5 5 OracleOracle 查询排序 查询排序 使用使用 orderorder byby 列名列名 1 1 列名 列名 2 2 默认按升序默认按升序 ASC ASC 排列 可以指定为降序排列 可以指定为降序 DESC DESC 排列 排列 注意 升序注意 升序 ASC ASC 时时 nullnull 在最后 降序在最后 降序 DESC DESC 时时 nullnull 在最前 在最前 6 SQL6 SQL 语句通配符和运算符 语句通配符和运算符 1 1 表示 表示 0 0 个或多个字符 个或多个字符 2 2 表示 表示 1 1 个字符 个字符 3 3 特殊字符使用特殊字符使用 ESCAPEESCAPE 进行转义 单引号进行转义 单引号 使用两个单引号转义 使用两个单引号转义 4 4 运算符的优先级由高到低为 运算符的优先级由高到低为 NotNot AndAnd OrOr 7 Oracle7 Oracle 数据库的函数 数据库的函数 OracleOracle 数据库的函数都有返回值 数据库的函数都有返回值 1 1 单行函数 单行函数 指对每一返回行起作用的函数 包括字符 数值 日期转换和通用函数 指对每一返回行起作用的函数 包括字符 数值 日期转换和通用函数 a a 字符函数 字符函数 lower lower 将字符转换为小写 将字符转换为小写 upper upper 将字符转换为大写 将字符转换为大写 initcap initcap 字符单词首字母大写 字符单词首字母大写 concat concat 字符串连接 字符串连接 instr instr 定位字符串 定位字符串 lpad lpad 左侧填充 左侧填充 rpad rpad 右侧填充 右侧填充 trim trim 过滤首尾空格 过滤首尾空格 replace replace 字符替换 字符替换 b b 数值函数 数值函数 abs abs 取绝对值 取绝对值 round round 四舍五入 四舍五入 trunc trunc 截断 截断 ceil ceil 向上取整 向上取整 floor floor 向下取整 向下取整 sign sign 判断正负 判断正负 c c 数值和字符串转换函数 数值和字符串转换函数 数字数字 字符串 字符串 to char number to char number to char number to char number 模式串模式串 字符串字符串 数字 数字 to number to number to number char to number char 模式串模式串 d d 日期函数 日期函数 日期可以直接加减整数 两个日期之间也可以直接相减 日期可以直接加减整数 两个日期之间也可以直接相减 round round 四舍五入到月 四舍五入到月 日期日期 字符串转换 字符串转换 to char to char 确实转换为确实转换为 dd mm yy dd mm yy 格式 格式 to char date to char date 日期格式日期格式 转换为模式串指定格式 转换为模式串指定格式 字符串字符串 日期 日期 to date to date to date char to date char 日期格式日期格式 按模式串指定格式进行解析 按模式串指定格式进行解析 e e 通用函数 通用函数 nvl nvl 将空值 将空值 nullnull 替换为特定的缺省值 适用于字符 数字 日期等 替换为特定的缺省值 适用于字符 数字 日期等 nvl exp1 nvl exp1 exp2 exp2 若 若 exp1exp1 值为值为 nullnull 则返回 则返回 exp2exp2 的值 的值 nvl2 nvl2 用于实现条件表达式功能 用于实现条件表达式功能 nvl2 exp1 nvl2 exp1 exp2 exp2 exp3 exp3 若 若 exp1exp1 的值不为的值不为 nullnull 则返回 则返回 exp2exp2 的值 否则返的值 否则返 回回 exp3exp3 的值 的值 nullif nullif 用于数据等价性比较 并根据比较结果返回 用于数据等价性比较 并根据比较结果返回 nullnull 或其中一个比较的或其中一个比较的 数值 数值 nullif exp1 nullif exp1 exp2 exp2 若 若 exp1exp1 与与 exp2exp2 相等 则返回相等 则返回 nullnull 否则返回 否则返回 exp1exp1 的的 值 值 coalesce coalesce 用于实现数据接合功能 用于实现数据接合功能 coalesce exp1 coalesce exp1 exp2 exp2 依次考察各参数表达式 遇到非 依次考察各参数表达式 遇到非 nullnull 值即停止并返回值即停止并返回 该值 该值 2 2 分组函数 分组函数 针对多行记录只返回一个结果针对多行记录只返回一个结果 通常忽略空值通常忽略空值 avg avg 计算平均值 计算平均值 count count 计算记录数 计算记录数 max max 计算最大值 计算最大值 min min 计算最小值 计算最小值 sum sum 求和 求和 注意 过滤分组函数必须只能使用注意 过滤分组函数必须只能使用 havinghaving 而不能使用而不能使用 wherewhere 分组函数最多只 分组函数最多只 可嵌套两层 可嵌套两层 8 Oracle8 Oracle 中实现多路分支条件 中实现多路分支条件 1 case 1 case 表达式可以实现多路分支结构 表达式可以实现多路分支结构 casecase expexp whenwhen exp1exp1 thenthen returnreturn exp1 exp1 whenwhen exp2exp2 thenthen returnreturn exp2 exp2 elseelse expn expn endend 2 decode 2 decode 函数和函数和 casecase 表达式类似 也可以实现多路分支 表达式类似 也可以实现多路分支 decode exp1 decode exp1 search1 search1 result1result1 search2 search2 result2result2 default default 9 Oracle9 Oracle 中的连接 中的连接 1 1 外连接外连接 outer outer join join 外连接可以看到参与连接的某一方中不满足连接条件的记录 外连接运算符为外连接可以看到参与连接的某一方中不满足连接条件的记录 外连接运算符为 a a 右外连接右外连接 right right join join 如 如 wherewhere table1 column1 table1 column1 table2 column2table2 column2 table1table1 中不满足条件的也可以查询到 中不满足条件的也可以查询到 b b 左外连接左外连接 left left join join 如 如 wherewhere table1 column1table1 column1 table2 column2 table2 column2 table2table2 中不满足条件的也可以查询到 中不满足条件的也可以查询到 c c 交叉连接交叉连接 cross cross join join 相当于连接时设置限制条件 相当于连接时设置限制条件 d d 自然连接自然连接 natural natural join join 等值连接 等值连接 e using e using 列名列名 指定列自然连接 指定列自然连接 f on exp1 exp2 f on exp1 exp2 设置任意连接条件 设置任意连接条件 2 2 内连接内连接 inner inner join join 内连接只返回满足连接条件的数据 内连接只返回满足连接条件的数据 10 TopN10 TopN 查询 查询 TopNTopN 查询是指查询数据库表中符合查询条件的前查询是指查询数据库表中符合查询条件的前 N N 条记录 条记录 OracleOracle 通常采用子通常采用子 查询方式来实现查询方式来实现 TopNTopN 查询 具体语法 查询 具体语法 selectselect 字段列表字段列表 fromfrom select select 字段列表字段列表 fromfrom tabletable orderorder byby 排列字段排列字段 wherewhere rownumrownum n 10 10 andand rowrow 20 20 11 11 数据库表操作 数据库表操作 1 1 复制表数据 复制表数据 insertinsert intointo 表名表名 1 1 列名列名 1 1 selectselect 列名列名 1 1 fromfrom 表名表名 2 2 2 2 复制表结构 复制表结构 CreateCreate tabletable 表名表名 1 1 asas selectselect fromfrom 表名表名 2 2 wherewhere 1 1 1 1 3 3 数据合并 数据合并 mergemerge 关键字用于数据合并 若记录在目标表中已存在则更新 否则插入记录 关键字用于数据合并 若记录在目标表中已存在则更新 否则插入记录 语法 语法 mergemerge intointo 表名表名 using using 表表 视图视图 子查询子查询 onon 连接条件连接条件 whenwhen matchedmatched thenthen updateupdate setset whenwhen notnot matchedmatched thenthen insert insert 列列 values values 值值 4 4 保存点 保存点 保存点保存点 savepointsavepoint 相当于跳转标记 可以回退到指定保存点 实现事务的部分相当于跳转标记 可以回退到指定保存点 实现事务的部分 回滚 回滚 5 5 方案方案 SchemaSchema OracleOracle 中的方案中的方案 SchemaSchema 相当于其他数据库中的相当于其他数据库中的 databasedatabase 6 6 使用子查询创建表 使用子查询创建表 createcreate table table 字段列表字段列表 asas selectselect 7 7 修改表结构 修改表结构 alteralter tabletable add modify drop add modify drop 注意 只能添加字段 或者修改约束条件 不能修改字段名称 若要修改字段注意 只能添加字段 或者修改约束条件 不能修改字段名称 若要修改字段 名 则只能先删除再添加 名 则只能先删除再添加 8 8 修改表名称 修改表名称 renamerename old nameold name toto new name new name 7 7 删除表和数据 删除表和数据 1 1 删除表数据 删除表数据 a deletea delete fromfrom 表名表名 b truncateb truncate tabletable 表名表名 相同点 相同点 只删除数据库 表结构保留 只删除数据库 表结构保留 区别 区别 deletedelete 是数据操作语言是数据操作语言 DML DML 只清空表中数据 效率低 但是可以撤销回滚 只清空表中数据 效率低 但是可以撤销回滚 truncatetruncate 是数据定义语言是数据定义语言 DDL DDL 清空表中所有数据 释放表的存储空间 一 清空表中所有数据 释放表的存储空间 一 旦执行不可回滚撤销 旦执行不可回滚撤销 2 2 删除表结构 删除表结构 dropdrop tabletable 表名表名 也是数据定义语言也是数据定义语言 DDL DDL 数据表的结构和数据都被删除 数据表的结构和数据都被删除 8 8 OracleOracle 中查看表和对象 中查看表和对象 OracleOracle 中表 视图 存储过程 触发器等都是对象 可以通过以下方式查看 中表 视图 存储过程 触发器等都是对象 可以通过以下方式查看 1 1 查看当前用户拥有的所有表名称 查看当前用户拥有的所有表名称 SelectSelect table nametable name fromfrom user tables user tables 2 2 查看当前用户可以访问的所有表名称 查看当前用户可以访问的所有表名称 SelectSelect table nametable name fromfrom all tables all tables 3 3 查看当前用户拥有的所有对象的类型 查看当前用户拥有的所有对象的类型 SelectSelect distinctdistinct object typeobject type fromfrom user objects user objects 4 4 查看所有用户拥有的所有表名称 查看所有用户拥有的所有表名称 SelectSelect table nametable name fromfrom dba tables dba tables 5 5 查看所有对象 查看所有对象 SelectSelect fromfrom user catalog user catalog 9 9 OracleOracle 的的 5 5 种约束 种约束 1 not 1 not nullnull 字段值非空约束 字段值非空约束 2 unique 2 unique 字段值唯一约束 字段值唯一约束 3 primary 3 primary keykey 主键约束 主键约束 字段级 字段名字段级 字段名 类型类型 primaryprimary key key 表级 表级 primaryprimary key key 列名列名 4 foreign 4 foreign keykey 外键约束 外键约束 字段级 字段名字段级 字段名 referencesreferences 表名表名 字段名字段名 表级 表级 foreignforeign key key 列名列名 referencesreferences 表名表名 列名列名 5 check 5 check 自定义约束 自定义约束 15 Oracle15 Oracle 数据库中的动态替换变量 数据库中的动态替换变量 动态替换变量是指在运行时由外部传入替换的变量 动态替换变量是指在运行时由外部传入替换的变量 1 注意 字符和日期类型的替换变量需要使用单引号注意 字符和日期类型的替换变量需要使用单引号 引起来 引起来 2 2 使用使用 definedefine 关键字预先定义动态变量 关键字预先定义动态变量 语法 语法 definedefine 变量名变量名 如 如 selectselect fromfrom empemp wherewhere empnoempno 3 3 使用使用 注意 在创建视图时 加上注意 在创建视图时 加上 withwith checkcheck optionoption 可以限制视图条件 可以限制视图条件 2 2 修改视图 修改视图 createcreate oror replacereplace viewview 视图名视图名 17 17 序列 序列 OracleOracle 数据库中 要使字段值自增 是通过一个叫做序列数据库中 要使字段值自增 是通过一个叫做序列 sequence sequence 来控制的 来控制的 1 1 创建序列 创建序列 createcreate sequencesequence 序列名序列名 incrementincrement byby 1 1 startstart withwith 1 1 2 2 修改序列 修改序列 alteralter sequencesequence 序列名序列名 注意 序列的起始序号不能修改 注意 序列的起始序号不能修改 3 3 删除序列 删除序列 dropdrop sequencesequence 序列名序列名 4 4 使用序列 使用序列 selectselect 序列名序列名 nextVal nextVal fromfrom dualdual 18 18 同义词 同义词 OracleOracle 中同义词中同义词 synonymsynonym 是给某个对象名一个别名 是给某个对象名一个别名 语法 语法 createcreate public privatepublic private synonymsynonym 同义词名同义词名 forfor 19 Oracle19 Oracle 的权限管理 的权限管理 1 1 授权 授权 grantgrant 权限权限 1 1 权限权限 toto useruser rolerole publicpublic with with adminadmin option option 权限可以传递权限可以传递 2 2 更改口令 更改口令 alteralter useruser 用户名用户名 identifiedidentified byby 口令口令 3 3 授予对象权限 授予对象权限 grantgrant 对象权限对象权限 onon 对象对象 toto useruser rolerole with with grantgrant option option 传递权限传递权限 4 4 将某个表上所有权限授予某个用户 将某个表上所有权限授予某个用户 grantgrant allall onon 表名表名 toto 用户名用户名 5 5 授予表中某列权限给某用户 授予表中某列权限给某用户 grantgrant 权限权限 列名列名 onon 表名表名 toto 用户名用户名 注意 只有插入和更新权限可以限制到列 其他权限不行 注意 只有插入和更新权限可以限制到列 其他权限不行 6 6 回收权限 回收权限 revokerevoke 权限权限 onon 对象对象 fromfrom useruser rolerole 20 20 使用使用 DatabaseDatabase linkslinks 建立远端数据库连接 建立远端数据库连接 createcreate publicpublic databasedatabase linklink usingusing 21 21 登录登录 OracleOracle 数据库几种方式 数据库几种方式 1 1 以默认的以默认的 syssys 帐号登录 帐号登录 sqlplussqlplus asas sysdbasysdba 2 2 以某个用户登录 以某个用户登录 sqlplussqlplus 用户名用户名 密码密码 3 3 不用用户名和密码方式登录 不用用户名和密码方式登录 sqlplussqlplus nolognolog 注意 该种方式只能权限很低 几乎不能做什么操作 注意 该种方式只能权限很低 几乎不能做什么操作 4 4 使用操作系统验证登录 使用操作系统验证登录 connconn asas sysdbasysdba 22 Oracle22 Oracle 数据库的三种验证机制 数据库的三种验证机制 1 1 操作系统验证 操作系统验证 2 2 密码文件验证 密码文件验证 3 Oracle 3 Oracle 数据库验证 数据库验证 注意 操作系统和密码文件都是用注意 操作系统和密码文件都是用 sys dba sys dba 用户验证方式 用户验证方式 23 Oracle23 Oracle 密码文件验证 密码文件验证 在在 OracleOracle 的安装目录下的的安装目录下的 databasedatabase 子目录中 有一个子目录中 有一个 PWDOracle oraPWDOracle ora 文件即文件即 为密码文件 为密码文件 生成密码文件命令 生成密码文件命令 orapwdorapwd file file 文件路径文件路径 password password 密码密码 注意 生成的密码文件名必须和原来的密码文件名一样 注意 生成的密码文件名必须和原来的密码文件名一样 24 Oracle24 Oracle 系统管理员系统管理员 dba dba 对用户的控制 对用户的控制 1 1 创建用户 创建用户 createcreate useruser 用户名用户名 identifiedidentified byby 密码密码 2 2 修改用户 修改用户 alteralter useruser 用户名用户名 identifiedidentified byby 密码密码 3 3 用户加锁 用户加锁 alteralter useruser 用户名用户名 AccountAccount lock lock 4 4 用户解锁 用户解锁 alteralter useruser 用户名用户名 AccountAccount unlock unlock 5 5 用户口令立即失效 用户口令立即失效 alteralter useruser 用户名用户名 passwordpassword Expire Expire 6 6 删除用户 删除用户 dropdrop useruser 用户名用户名 cascade cascade 25 25 使用使用 sqlpluswsqlplusw sqlpluswsqlplusw 是是 OracleOracle 数据库的操作窗口 其常用操作如下 数据库的操作窗口 其常用操作如下 1 1 编辑文件 编辑文件 eded 文件名文件名 2 2 执行文件 执行文件 文件名文件名 3 3 继续执行上一条操作命令 继续执行上一条操作命令 26 Oracle26 Oracle 数据库的文件结构 数据库的文件结构 1 1 物理结构 物理结构 参数文件参数文件 控制文件控制文件 数据文件 日志文件数据文件 日志文件 2 2 内存结构 内存结构 系统全局区系统全局区 SGA SGA DB DB 高速缓存池 共享池 高速缓存池 共享池 RedoRedo 缓存池 大共享区 固定缓存池 大共享区 固定 SGASGA DBDB 告诉缓冲池告诉缓冲池 默认缓存池 保持缓存池 再生缓存池 默认缓存池 保持缓存池 再生缓存池 共享池共享池 共享共享 SQLSQL 区 库缓存区 字典缓冲区 区 库缓存区 字典缓冲区 3 3 逻辑结构 逻辑结构 块 盘区 段 表空间 数据文件 块 盘区 段 表空间 数据文件 26 Oracle26 Oracle 的启动和停止命令 的启动和停止命令 1 startup 1 startup mountmount 只启动控制文件 不启动数据文件 只启动控制文件 不启动数据文件 2 startup 2 startup nomountnomount 不启动控制文件 不启动控制文件 3 shutdown 3 shutdown immediateimmediate 停止数据库 停止数据库 27 Linux Unix27 Linux Unix 下下 OracleOracle 数据库启动过程 数据库启动过程 1 lsnrctl 1 lsnrctl startstart 启动 启动 OracleOracle 监听 监听 2 sqlplus 2 sqlplus 用户名用户名 密码密码 asas sysdbasysdba 3 startup 3 startup 28 28 子查询的限制 子查询的限制 1 1 在子查询中使用在子查询中使用 inin 关键字时 子查询中不能使用关键字时 子查询中不能使用 2 2 在子查询中使用在子查询中使用 existsexists 关键字时 可以使用关键字时 可以使用 29 29 并集合交集运算 并集合交集运算 1 union 1 union 并集 将结果合并 并自动去掉重复行 并集 将结果合并 并自动去掉重复行 2 intersect 2 intersect 交集 将两者共有的记录保留 交集 将两者共有的记录保留 PL SQL 编程基础编程基础 PL SQLPL SQL 编程基础 编程基础 1 PL SQL1 PL SQL 块基本结构 块基本结构 declaredeclare 可选项 用于声明变量可选项 用于声明变量 beginbegin exceptionexception 异常处理异常处理 endend 2 PL SQL2 PL SQL 变量声明 变量声明 变量声明所要做的主要工作 赋予变量适当的名称 正确的数据类型 控制变变量声明所要做的主要工作 赋予变量适当的名称 正确的数据类型 控制变 量的作用范围 量的作用范围 变量命名规则 由字母开头 可以包含数字 下划线变量命名规则 由字母开头 可以包含数字 下划线 等 不区分大小等 不区分大小 写 不可以是系统关键字 写 不可以是系统关键字 一个简单的一个简单的 PL SQLPL SQL 变量声明的例子如下 变量声明的例子如下 declaredeclare x x varchar2 10 varchar2 10 beginbegin x Thisx This isis x x 为变量为变量 x x 赋值 赋值符号为赋值 赋值符号为 DBMS output put line xDBMS output put line x 的值为 的值为 x x 在命令行控制台打印输出在命令行控制台打印输出 变量变量 x x 的值的值 end end 执行执行 该该 PL SQLPL SQL 程序块程序块 3 PL SQL3 PL SQL 中的分支语句 中的分支语句 PL SQLPL SQL 中有两种分支语句 中有两种分支语句 ifif 分支和分支和 casecase 分支 分支 1 if 1 if 分支 分支 ifif thenthen elsifelsif thenthen 注意注意 OracleOracle 中的中的 elseifelseif 写法为写法为 elsif elsif 比较特殊比较特殊 elseelse endend if if 2 case 2 case 分支 分支 casecase whenwhen thenthen whenwhen thenthen elseelse endend case case 4 PL SQL4 PL SQL 中的循环 中的循环 PL SQLPL SQL 中有三种循环 基本循环 中有三种循环 基本循环 whilewhile 循环和循环和 forfor 循环 循环 1 1 基本循环基本循环 loop loop looploop endend loop loop 2 While 2 While 循环 循环 whilewhile expressionexpression looploop endend loop loop 3 for 3 for 循环 循环 forfor countercounter in reverse in reverse start valuestart value end valueend value looploop 注意 注意 reversereverse 是从大到小 默认是从小是从大到小 默认是从小 到大 到大 start valuestart value 和和 end valueend value 之间是两个点之间是两个点 endend loop loop 注意 若想要在注意 若想要在 PL SQLPL SQL 循环中跳出循环 可以使用以下两种方法 循环中跳出循环 可以使用以下两种方法 a a ifif thenthen exit exit endend if if b exitb exit whenwhen 5 PL SQL5 PL SQL forfor 循环的简单例子 循环的简单例子 设置设置 OracleOracle 控制台屏幕输出命令 控制台屏幕输出命令 setset serveroutputserveroutput on on PL SQLPL SQL forfor 循环的简单例子 循环的简单例子 beginbegin forfor i