oracle基础培训课程ppt课件

Oracle培训PPT 目录 数据库基础Oracle介绍Oracle使用SQL语言Oracle日常管理 目录 数据库基础Oracle介绍Oracle使用SQL语言Oracle日常管理 1 1基本概念 数据所谓数据 Data 就是描述事物的符号 在我们的日常生活中数据无所不在 数字 文字 图表 图像 声音等都是数据 人们通过数据来认识世界 交流信息数据库数据库 DB即Database 顾名思义 就是数据存放的地方 在计算机中 数据库是数据和数据库对象的集合 所谓数据库对象是指表 Table 视图 View 存储过程 StoredProcedure 触发器 Trigger 等 数据库管理系统数据库管理系统 DBMS即DatabaseManagementSystem 是用于管理数据的一套软件 用户通过DBMS查询和操作数据库 DBMS维护数据的安全性和完整性 以及进行多用户下的并发控制和恢复数据库 1 2关系型数据库 关系模型关系模型 RelationalModel 把世界看作是由实体Entity和联系Relationship构成的 实体 现实世界中具有区分于其它事物的特征或属性集合 实体通常是以表的形式来表现的 表的每一行描述实体的一个实例 表的每一列描述实体的一个特征或属性 如 部门 汽车 员工 通讯录联系 实体之间的关系a 一对一b 一对多如班级和学生 一个班级 有多个学生c 多对一相反的 学生对班级 就是多对一的关系通过联系 就可以通过一个实体 查找相关实体 使系统形成一个有机的整体 1 2关系型数据库 关系型数据库既基于关系模型的数据库候选键 由一个或多个字段组成 能唯一标识表中的一行 而又不含多余的字段 主键 PrimaryKey 用来唯一标识表中一行的候选键 一个表只能有一个主键 外键 ForeignKey 一个表的字段 是另外一个表的主键 候选键 外键 主键 目录 数据库基础Oracle介绍Oracle使用SQL语言Oracle日常管理 2 1oracle体系结构 逻辑结构Oracle数据库按照一定的逻辑结构进行组织和管理表空间 tablespace 最高一级的逻辑存储单元 是连接逻辑存储结构和物理存储结构的桥梁段 segment 包括数据段 索引段 临时段区 extent 由一定数量连续的块组成的逻辑存储结构 是Oracle进行存储分配的单元块 block Oracle读写数据文件的最小单位多个数据块组成区 多个区组成段 多个段组成表空间 多个表空间组成逻辑数据库 2 1oracle体系结构 创建表空间 数据文件用表空间createtablespaceum datdatafile D oracle product 10 2 0 oradata dev um dat01 dbf size10MAUTOEXTENDONNEXT10240KMAXSIZEUNLIMITEDextentmanagementlocaluniformsize128konline 索引用表空间createtablespaceum idxdatafile D oracle product 10 2 0 oradata dev um idx01 dbf size10MAUTOEXTENDONNEXT10240KMAXSIZEUNLIMITEDextentmanagementlocaluniformsize128konline 临时表空间createtemporarytablespaceum tmptempfile D oracle product 10 2 0 oradata dev um tmp01 dbf size10mAUTOEXTENDONNEXT10240KMAXSIZEUNLIMITEDextentmanagementlocaluniformsize128k 2 1oracle体系结构 物理结构物理存储结构主要是指在操作系统中 Oracle数据的存储和管理方式 它的组成包括 数据文件 datafile 存储表 索引等实际数据的文件 一个表空间 可以有多个数据文件 一个数据文件 只能属于一个表空间控制文件 controlfile 存储数据库的物理结构等信息的文件 重做日志文件 redofile 记录数据库的修改操作和事务操作的文件其他文件 2 1oracle体系结构 实例实例都是指后台进程与内存的组合 实例是一组计算机程序 启动数据库时 先创建实例 然后通过实例加载 mount 打开 open 物理数据库 然后由这个实例来访问和控制数据库的各个物理结构 2 1oracle体系结构 内存结构系统全局区 SytemGlobalArea 每个实例都只有一个SGA区 当多个用户连接到同一实例时 这些用户进程 服务进程共享SGA区 包括 a 数据高速缓存区b 字典缓存区c 重做日志缓存区d SQL共享池程序全局区PGA PROCESSGLOBALAREA 是一个内存区 包含单个进程的数据和控制信息 所以又称为进程全局区 2 1oracle体系结构 数据字典由一些只读的数据字典表和数据字典视图组成 数据字典表保存的主要信息有 各种方案对象的定义信息存储空间的分配信息安全信息实例运行的性能和统计信息其他关于数据库的基本信息 2 2Oracle数据库安装 1 选择高级安装 2 单击下一步 2 2Oracle数据库安装 2 2Oracle数据库安装 1 指定主目录的名称 用于区别安装的多个oracle 2 指定oracle的安装路径 2 2Oracle数据库安装 这一项未执行 不用管它 检查结果为通过 2 1Oracle数据库安装 选择 是 2 2Oracle数据库安装 2 2Oracle数据库安装 2 2Oracle数据库安装 数据库名和SID 连接数据库时会用到 2 2Oracle数据库安装 2 2Oracle数据库安装 2 2Oracle数据库安装 设置几个系统用户的密码 2 2Oracle数据库安装 2 2Oracle数据库安装 2 2Oracle数据库安装 2 5使用PL SQLDeveloper 客户端配置好网络服务名后 会在这个下拉框显示出来 2 5使用PL SQLDeveloper 这个窗口列出了所有的数据库对象 可以可视化的创建和管理 2 5使用PL SQLDeveloper 执行 2 6用户和方案 oracle中 用户名和方案名相同 可把用户和方案理解为同一个概念 创建用户 同时也创建了一个方案 方案 又叫模式 Schema 是数据库对象的集合 创建用户um 密码oraclecreateuserumidentifiedbyoracledefaulttablespaceum dattemporarytablespaceum tmp 赋予权限grantconnecttoum grantresourcetoum grantunlimitedtablespacetoum 2 7常用数据类型 char 固定长度字符串 不足自动以空格补齐长度 最多2000个字节 如char 10 varchar2 可变长度字符串 最多4000个字节 如varchar2 100 number 数值型 最高精度28为 如number 24 最多24位 number 24 4 最多24位 其中小数最多4位date 存储日期和时间 精确到秒timestamp 存储时间 秒值精确到小数点后6位 目录 数据库基础Oracle介绍Oracle使用SQL语言Oracle日常管理 3 1表 表由行和列组成 也称为二维表例 员工信息表记录 表中一行 称为一条记录字段 构成记录的各数据项 比如姓名 性别 3 1表 创建表createtableEMP EMP IDnumber 24 notnull EMP CODEvarchar2 10 notnull EMP NAMEvarchar2 20 notnull E MAILvarchar2 100 DEPT IDnumber 24 notnull tablespaceUM DAT 3 2约束 主键约束 添加主键altertableEMPaddconstraintpk emp idprimarykey EMP ID 唯一约束altertableEMPaddconstraintuq emp codeunique EMP CODE 外键约束altertableEMPaddconstraintfk dept idforeignkey DEPT ID referencesdept DEPT ID oracle自动为主键和唯一约束创建索引 3 3索引 索引作用类似书的目录 用于快速查找数据索引还可用户数据完整性限制 比如唯一索引 可以保证字段值的唯一性包含以下的类型 标准索引 B树 数据量非常大的情况下 查找依然很快惟一索引 UniqueIndex 比如员工编号 唯一索引查找最快位图索引 Bitmap 适合基数小的字段 比如性别 节约空间基于函数的索引 FBI 3 3索引 创建标准索引createindexIDX DEPT NAMEonDEPT dept name 创建唯一索引createuniqueindexIDX DEPT CODEonDEPT dept code 创建位图索引createbitmapindexIDX EMP SEXonEMP sex 创建函数索引createindexIDX EMP BDATEonEMP TO CHAR B DATE YYYY MM DD 3 3索引 哪些字段建议建立索引呢 selectemp e mail count ctfromempjoindeptonemp dept id dept dept idwheredept dept name IT groupbyemp e mailorderbyemp e mail 1 表间关联字段 外键 2 查询的字段3 groupby的字段4 orderby的字段 3 3索引 身份证这类唯一属性 应建唯一索引性别 只有男 女 未定等少数几种状态值 应创建位图索引 位图索引更节约空间对字段使用函数 会停用索引 可创建函数索引 3 3索引 索引的优缺点优点 某些情况下 数据查找快缺点 a 在某些条件下 全表扫描比索引查找更快b 索引占用空间惊人 甚至超过表数据所占空间 不利于管理 c 创建索引后 会降低插入 修改 删除等操作的效率 3 4分区 分区就是把表和索引分成几大块 每一块存放到一个表空间上 性能调优的重要手段 有三种分区方式1 散列分区均匀分布数据 i o设备负担均衡 2 范围分区按数据值的范围进行分区 比如将员工信息表 按入职时间分区 06年一个区 07年一个区 08年一个区 现在我要找一个06年入职的员工 只需要扫描06年那个分区 时间会快很多 磁盘i o也会减少 3 4分区 3 复合分区范围分区和散列分区结合起来使用 先把数据按范围分区 然后在每个分区内再使用散列分区 把数据均匀分布注意 索引 分区这些调优技术 虽然在数据查询上效率得到了提高 但是 数据在插入 修改操作会更慢 因为在插入的时候 还需要做索引数据 分区数据的额外工作 数据库调优 需要考虑一个度的问题 3 4分区 范围分区例子createtableEMPS SALARYNUMBER 24 4 notnull EMP IDNUMBER 24 notnull partitionbyrange SALARY partitionP SALARY 2000valueslessthan 2000 partitionP SALARY 3000valueslessthan 3000 目录 数据库基础知识Oracle基础表 索引 约束和分区SQL语句自定义函数 存储过程 触发器备份和恢复 4 1oracle常用函数 字符函数upper str 转为大写lower str 转为小写substr str n m 从n位开始 截取m个字符substr str n 从n位开始 截取后面字符length str 得到字符串的长度ltrim str 去掉左边空格rtrim str 去掉右边空格instr str c 得到字符c在str的位置lpad str n c 将str补足为n位长度 不足左边用字符c代替rpad str n c 将str补足为n位长度 不足右边以字符c代替 4 1oracle常用函数 字符函数例子 不区分大小写查询selectemp code emp namefromempwhereupper emp name upper Tom 去掉空格selectrtrim ltrim emp code fromemp 4 1oracle常用函数 数值函数round col n 四舍五入round 457 628 2 小数点后2位四舍五入结果457 63round 457 628 1 小数点前1位四舍五入结果460trunc col n 截断数值trunc 457 628 2 结果457 62trunc 457 628 1 结果450 4 1oracle常用函数 日期函数months between date1 date2 两个日期间的月数 结果为实数add months date m 增加m个月 m可以为负数 结果为减少m个月round 日期四舍五入trunc 截断日期last day 当月最后一天 4 1oracle常用函数 日期函数例子当前日期增加1个月selectadd months sysdate 1 fromdual 去年同月selectadd months sysdate 12 fromdual 得到年初selecttrunc sysdate YYYY fromdual 得到月初selecttrunc sysdate MM fromdual 精确到天 截断小时分秒selecttrunc sysdate fromdual 当月最后一天selectlast day sysdate fromdual 4 1oracle常用函数 转换函数日期转为字符 to char date1 format model format model 转换后的显示格式YYYY年 MM月 DD日 HH24小时 MI分 SS秒例子 selectto char sysdate YYYY MM DDHH24 MI SS rqfromdual 4 1oracle常用函数 转换函数字符转为日期to date 2007 11 11 YYYY MM DD 数值转为字符selectto char 55676 fm99 999 00 fromdualfm表示去掉前面的空格和0结果 55 676 00 4 2SQL SQL结构化查询语言 是访问和处理数据库的标准语言 分为 DDL 数据定义语言用于定义数据库对象如 定义表 视图 存储过程等DML 数据操作语言用于访问和处理数据如 insert update delete select 4 2插入 插入单条insertinto表1 字段1 字段2 values 值1 值2 批量插入insertinto表1 字段1 字段2 select字段1 字段2 from表2 这样可以把后面这个select查询的结果 批量插入表1中 4 2插入 插入例子insertintoemp emp id emp code emp name values 1 231 luodaijun 4 3修改 update表1set字段1 值1 字段2 值2where条件例 updateempsetemp name xiaoluo whereemp id 1 4 4删除 deletefrom表1where条件例 deletefromempwhereemp id 1 删除emp id等于1的记录 4 5select查询 1 用SELECT子句来指定查询所需的列 多个列之间用逗号分开 例如 selectp id p name costfromproducts2 可以使用符号 来选取表的全部列 例如 select fromproducts3 在查询结果中添加列 例如 selectp id p name qty cost cost qtyassum costfromproducts4 使用WHERE子句 例如 selecte namefromempwheresalarybetween2000and3000 salary的值 2000 3000 4 5select查询 5 使用DISTINCT关键字 去掉重复结果 例如 selectdistinctdept idfromempwheresalary 70006 使用IN关键字 例如 selecte namefromemployeewheredept idin 1001 1002 7 使用通配符 模糊查询 例如 selectemp namefromempwhereemp namelike luo 查询所有emp name以luo开头的数据8 使用ORDER子句 对结果排序 例如 selectp id p name cost qtyfromproductswheredept id 1003 orderbycostdesc quantity 默认是升序ASC 4 3多表关联查询 内连接 结果为两表都包含的dept id的行 4 3多表关联查询 内连接ISO标准 oracle9i开始支持ISO标准写法 selecte emp id e emp name d dept namefromempeinnerjoindeptdone dept id d dept idOracle selecte emp id e emp name d dept namefromempe deptdwheree dept id d dept id 4 3多表关联查询 左连接 以左表 emp 为准 右表没有的 为空值null D05 左表有 右表无 4 3多表关联查询 左连接ISO标准 selecte emp id e emp name d dept namefromempeleftjoindeptdone dept id d dept idOracle selecte emp id e emp name d dept namefromempe deptdwheree dept id d dept id 4 3多表关联查询 右连接跟左连接相反 以右表为准ISO selecte emp id e emp name d dept namefromemperightjoindeptdone dept id d dept idOracle selecte emp id e emp name d dept namefromempe deptdwheree dept id d dept id 4 3多表关联查询 全外连接 包含两表的数据 D05 左表有 右表无 D04 右表有 左表无 4 3多表关联查询 全外连接Oracle9i以上版本支持selecte emp id e emp name d dept namefromempefullouterjoindeptdone dept id d dept id 4 4条件表达式 Case表达式语法 case表达式when值1then结果1when值2then结果2 else默认结果end 4 4条件表达式 第二种方式casewhen条件1then结果1when条件2then结果2 else默认结果end 4 4条件表达式 例子 交叉报表 4 4条件表达式 例子 交叉报表selectname sum yuwen yuwen sum shuxue shuxue sum huaxue huaxuefrom selectname casekechenwhen 语文 thenfengshuendyuwen casekechenwhen 数学 thenfengshuendshuxue casekechenwhen 化学 thenfengshuendhuaxuefromtable tgroupbyname 4 4条件表达式 Decode函数Decode 表达式 条件1 结果1 条件2 结果2 默认结果 注 默认值可以省略 4 4条件表达式 Decode例子selectswjg dm decode swjg bz B 税务部门 J 税务机关 fromdm swjg 目录 数据库基础知识Oracle基础表 索引 约束和分区SQL语句视图 自定义函数 存储过程 触发器备份和恢复 5 1视图 视图 View 虚拟表 命名的查询语句视图内部是一条select语句 用户可以像查询表一样查询视图如 createorreplaceviewv 001asselecte empno e ename d dnamefromempejoindeptdone deptno d deptno 然后查询表一样查询视图select fromv 001 5 2存储过程 存储在数据库中 一段由PL SQL语言编写的程序块 参数有三种类型a in输入参数b out输出参数c inout输入输出参数存储过程没有返回值 但可以通过out参数的方式返回结果 用于比较复杂的查询统计以及业务逻辑处理 5 2存储过程 for隐式游标例子 Createorreplaceprocedurep test ls emp codevarchar2 isbeginforxin selectemp id salaryfromempwhereemp code ls emp code loopprocessData x emp id x salary 从游标取出记录 然后处理endloop end 5 2存储过程 显示游标例子createorreplaceprocedurep cursor testiscursormy curisselect fromscott emp cur recodemy cur rowtype 记录类型beginopenmy cur loopfetchmy curintocur recode 抓取数据 并赋值EXITWHENmy cur NOTFOUND 游标没有数据 退出循环dbms output put line cur recode empno endloop closemy cur endp cursor test 5 2存储过程 存储过程返回结果集createorreplaceprocedurep get emp p curoutsys refcursor asbeginopenp curforselect fromemp end 这里通过oracle9i开始提供引用游标 返回结果集在pl sqldeveloper里 选择存储过程 右键选择测试 可以测试及调试存储过程 5 3函数 存储过程类似 存储在数据库中 一段由PL SQL语言编写的程序块 可以有输入参数和返回值与存储过程的区别在于 1 在select语句中 只能使用函数2 函数可以索引 FBI 存储过程不能 5 3函数 Createorreplacefunctionraise salary ls moneynumber RETURNnumberisln numnumber beginln num ls money 10000 RETURNln num end 5 4JDBC调用存储过程 函数 创建用户登录存储过程createorreplaceprocedurep student login ln stu novarchar2 ln passvarchar2 rtn codeoutnumber isbeginselectcount intortn codefromstudentwherestu no ln stu noandpass ln pass end 5 4JDBC调用存储过程 函数 CallableStatemmentcst conn prepareCall callp student login cst setString 1 001 设置输入参数 号位置1cst SetString 2 123 设置输入参数 号位置2cst registerOutParameter 3 java sql Types Integer 注册输出参数 位置3cst excute 执行存储过程inta cst getInt 3 取得存储过程返回输出参数的值 位置3if a 1 通过认证 5 5序列 序列 oracle提供的数字生成器createsequenceSEQ STU IDminvalue1maxvalue99999999999999999999startwith1incrementby1 从序列得到数字 selectseq stu id nextvalfromdual 在insert语句使用序列insertintoemp emp id values seq stu id nextval 5 4触发器 存储在数据库里的带名的PL SQL块 由事件触发 比如表插入修改数据时触发主要用于1 维护复杂的完整性约束比如删除一条员工信息 在删