OracleSQL基础培训.ppt

ORACLE系统管理培训 让中国软件影响世界 研发三部潘春宇亚信联创科技 中国 有限公司2011 09 提升 创新 2009 亚信 2 培训目的 了解Oracle数据库的基本组成和运行原理了解SQL基本的常用功能 提升 创新 2009 亚信 3 培训大纲 第一部分 系统体系结构基本组件概览管理Oracle实例使用数据字典和动态性能视图管理表空间和数据文件 培训大纲 提升 创新 2009 亚信 4 第二部分 SQL基础基本的查询语句限制和排序数据函数表关联子查询处理数据 OracleSQL基础培训 提升 创新 2009 亚信 5 第一部分 系统体系结构 Oracle体系结构决定了数据库如何使用内存 硬盘 cpu和网络 一个Oracle服务器 server 是由Oracle实例 instance 和数据库两部分组成 实例 内存结构 一组后台进程一个Oracle数据库是一个数据单位的集合 主要目的是为了储存和读取信息 Oracle数据库可以分为逻辑结构和物理结构 Oracle服务器的启动过程是 先分配共享内存 然后启动后台进程 最后启动数据库 Oracle的体系结构 提升 创新 2009 亚信 Oracle数据库服务器结构 实例结构 提升 创新 2009 亚信 8 数据库文件 提升 创新 2009 亚信 9 实例管理 提升 创新 2009 亚信 10 参数文件 参数文件有两种 spfile和pfile文件 spfile是二进制文件 由数据库server读写控制 而pfile是文本文件 数据库服务器只能读不能写 实例启动时会在ORACLE HOME database目录下查找参数文件 顺序是spfile ora spfile ora init ora参数文件的参数包括基础参数和高级参数两部分 其中基础参数是系统运行的必备参数 不可以更改 高级参数可以用来优化系统的运行 比如调整共享池的大小 最大并发连接数等 实例管理 提升 创新 2009 亚信 11 启动过程 打开参数文件 打开控制文件 打开数据文件 实例管理 提升 创新 2009 亚信 12 关闭数据库 Abort 数据库立即关闭 肯定会丢数据 紧急情况才会执行 比如马上要断电了 下次启动需要恢复 由系统自动完成 Immediate 大多数的执行方式 创建检查点 关闭文件 没提交的事务都要被回滚 Transactional 在immediate的基础上 会等待事务结束 Normal 在transactional的基础上 会等待会话结束 最慢的方式 数据字典和动态性能视图 提升 创新 2009 亚信 13 数据字典定义 描述数据库和里面的对象包含只读的表和视图保存在SYSTEM表空间里属于用户SYS由Oracle服务器来维护用户通过Select语句来操作 数据字典和动态性能视图 DBA xxx 保存数据库里所有用户下的对象信息ALL xxx 保存当前用户能查看的所有对象的信息USER xxx 保存当前用户拥有的对象的信息 数据字典分类 数据字典和动态视图 总览 DICTIONARY DICT COLUMNS用户对象 DBA TABLES DBA INDEXES DBA TAB COLUMNS DBA CONSTRAINTS空间分配 DBA SEGMENTS DBA EXTENTS数据库结构 DBA TABLESPACES DBA DATA FILES 数据字典应用示例 数据字典和动态性能视图 虚拟表记录当前的数据库活动在数据库可操作时不断更新通过内存和控制文件访问信息用于监控和优化数据库由SYS用户拥有同义词以V 开头在V FIXED TABLE中列出 动态性能视图 数据字典和动态性能视图 V CONTROLFILEV DATABASEV DATAFILEV INSTANCEV PARAMETERV SESSIONV SGAV TABLESPACEV THREADV VERSION 动态性能视图示例 表空间和数据文件 提升 创新 2009 亚信 18 每个逻辑块大小2KB 32KB 默认8KB 连续逻辑块构成一个extent 一组extent构成一个segment 表空间和数据文件 提升 创新 2009 亚信 19 Table数据的存储 一个表用一个segment存储 分为多个extent 同时 一条记录可能存储在多个block中 表空间和数据文件 提升 创新 2009 亚信 20 关联关系 表空间由数据文件构成一个表空间只能属于一个数据库一个数据库最多可以有65534个数据文件 表空间和数据文件 提升 创新 2009 亚信 21 表空间的空间管理方式 分为本地式管理和字典式管理两种 本地式管理就是用位映射方式管理extents 字典式管理就是用数据字典方式管理extents 仅用于向后兼容 推荐用本地式管理 表空间和数据文件 提升 创新 2009 亚信 22 创建表空间 使用CREATETABLESPACE命令创建表空间 CREATETABLESPACEuserdataDATAFILE u01 oradata userdata01 dbf SIZE100MAUTOEXTENDONNEXT5MMAXSIZE200M CREATEUNDOTABLESPACEundo1DATAFILE u01 oradata undo01 dbf SIZE40M CREATETEMPORARYTABLESPACEtempTEMPFILE u01 oradata temp01 dbf SIZE500MEXTENTMANAGEMENTLOCALUNIFORMSIZE4M 表空间和数据文件 提升 创新 2009 亚信 23 使用AUTOEXTEND使数据文件自动扩展ALTERDATABASEDATAFILE u01 oradata userdata dbf AUTOEXTENDON修改数据文件的大小ALTERDATABASEDATAFILE u01 oradata userdata dbf RESIZE1024M给表空间添加新的数据文件ALTERTABLESPACEuserdataADDDATAFILE u02 oradata userdata2 dbf SIZE1024M 调整表空间的大小 表空间和数据文件 提升 创新 2009 亚信 24 不能删除下列表空间 SYSTEM表空间 具有活动段的表空间INCLUDINGCONTENTS将删除段INCLUDINGCONTENTSANDDATAFILES将删除数据文件CASCADECONSTRAINTS将删除所有引用完整性约束DROPTABLESPACEuserdataINCLUDINGCONTENTSANDDATAFILES 删除表空间和数据文件 Oracle系统管理培训 提升 创新 2009 亚信 25 第二部分 SQL基础 基础查询语句 提升 创新 2009 亚信 26 SELECTisalistofoneormorecolumns selectsallcolumnsDISTINCTsuppressesduplicatescolumn expressionselectsthenamedcolumnortheexpressionaliasgivesselectedcolumnsdifferentheadingsFROMtablespecifiesthetablecontainingthecolumns SELECT DISTINCT column expression alias FROMtable 基础查询语句 提升 创新 2009 亚信 27 示例 selectfirst name last name Name salary salary 1 percent 12 10000as AnnualSalary fromhr employees 假设我们需要一个报表 从雇员表中获取信息 显示以下内容 员工的姓名 由员工的first name和last name拼接得到月薪 表中记录的员工月薪年薪 给每个员工同样比例的加薪 具体比例执行时输入 调整后的月薪乘以12 再加上1万的奖金 限制和排序数据 提升 创新 2009 亚信 28 限制数据 where子句 SELECT DISTINCT column expression alias FROMtable WHEREcondition s WHERE限制查询结果要满足一定的条件Condition限制条件 可以由列名 比较运算符 表达式 常数组成 限制和排序数据 提升 创新 2009 亚信 29 比较条件运算符 限制和排序数据 提升 创新 2009 亚信 30 示例 selectlast name salary job id emailfromhr employeeswherejob idlike S CLERK escape 其中 是通配符 代表任一一个字符 Escape字句声明 为转义符 限制和排序数据 提升 创新 2009 亚信 31 逻辑条件运算符 限制和排序数据 提升 创新 2009 亚信 32 示例 SELECTemployee id last name job id salaryFROMhr employeesWHEREsalary 10000ANDjob idLIKE MAN ANDmanager idISNOTNULL 限制和排序数据 提升 创新 2009 亚信 33 运算符优先级 限制和排序数据 提升 创新 2009 亚信 34 ORDERBY子句 使用orderby子句可以对数据进行排序ASC 升序 默认设置 DESC 降序Orderby子句在SELECT语句的最后 SELECTlast name job id department id hire dateFROMhr employeesORDERBY2desc salary 函数 提升 创新 2009 亚信 35 单行函数 处理数据项接收参数并返回一个值作用于每个返回的行为每行返回一个值可以用于修改数据类型可以嵌套接受参数 这些参数可以是列 也可以是表达式Function name arg1 arg2 函数 提升 创新 2009 亚信 36 单行函数 字符函数 接受字符输入 返回字符或数字值数字函数 接受数字输入 返回数字值日期函数 处理DATE类型数据 返回日期值或数字值转换函数 转换数据类型常规函数 NVL NVL2 NULLIF COALSECE CASE DECODE 函数 提升 创新 2009 亚信 37 字符函数 大小写处理函数 LOWERUPPERINITCAP 字符处理函数 CONCAT SUBSTR LENGTH INSTR LPAD RPAD TRIM REPLACE 函数 提升 创新 2009 亚信 38 数字函数 ROUND 将数值4舍5入到指定的小数位ROUND 45 926 2 45 93TRUNC 将数值截断到指定的小数位TRUNC 45 926 2 45 92MOD 返回除法计算的余数MOD 1600 300 100 函数 提升 创新 2009 亚信 39 日期函数 函数 提升 创新 2009 亚信 40 转换函数 函数 提升 创新 2009 亚信 41 转换函数 函数 提升 创新 2009 亚信 42 常规函数 NVL expr1 expr2 将空值转换为实际值NVL2 expr1 expr2 expr3 如果expr1非空 则返回expr2 否则返回expr3NULLIF expr1 expr2 如果expr1和expr2相等 则返回NULL 否则返回expr1COALESCE expr1 expr2 exprn 返回参数列表中第一个非空的表达式 函数 提升 创新 2009 亚信 43 常规函数 SQL中实现IF THEN ELSE逻辑 CASEexprWHENcomparison expr1THENreturn expr1 WHENcomparision expr2THENreturn expr2 WHENcomparision exprnTHENreturn exprnELSEelse expr END 函数 提升 创新 2009 亚信 44 常规函数 SQL中实现IF THEN ELSE逻辑 DECODE col expr search1 result1 search2 result2 searchn resultn default 函数 分组函数 函数 分组函数语法 SELECT column group function column FROMtable WHEREcondition GROUPBYcolumn ORDERBYcolumn GROUPBY子句准则 SELECT子句中未经分组函数处理的列必须包含在GROUPBY子句中不能在GROUPBY子句中使用别名 函数 分组函数 selectdepartment id job id avg salary fromhr employeesgroupbydepartment id job idhavingavg salary 5000统计各个部门不同职位的平均薪水 取其中平均薪水在5000以上的记录 表关联 提升 创新 2009 亚信 48 关联类型 等值连接非等值连接外连接自连接 表关联 提升 创新 2009 亚信 49 等值连接 selecte employee id e last name e salary d department name l cityfromhr employeese hr departmentsd hr locationslwheree department id d department idandd location id l location idandlast name Matos 表关联 提升 创新 2009 亚信 50 非等值关联 selecte employee id e last name e salary t tax percentfromhr employeese taxtwheree salarybetweent min salaryandt max salary 表关联 提升 创新 2009 亚信 51 外连接 selectd department id d department name e first name e last namenamefromhr departmentsd hr employeesewhered department id e department id 表关联 提升 创新 2009 亚信 52 自连接 selecte employee id e first name e last nameemployee name m employee idmanager id m first name m last namemanager namefromhr employeese hr employeesmwheree manager id m employee id 子查询 提升 创新 2009 亚信 53 使用子查询解决问题 谁的薪水比Abel高 子查询 提升 创新 2009 亚信 54 子查询 子查询是嵌入到另外一个SELECT语句的子句中的一个SELECT语句 通过使用子查询 可以用简单的语句组成功能强大的语句 当你需要从表中选择行 而选择条件却取决于该表自身或者其他表中的数据时 子查询非常有用 可以把子查询放在许多select子句中 包括 WHERE子句HAVING子句FROM子句 子查询 提升 创新 2009 亚信 55 单行子查询 SELECTlast nameFROMemployeesWHEREsalary SELECTsalaryFROMemployeesWHERElast name Abel 单行子查询比较运算符 子查询 提升 创新 2009 亚信 56 多行子查询 SELECTlast name salary department idFROMemployeesWHEREsalaryIN SELECTMIN salary FROMemployeesGROUPBYdepartment id 多行子查询运算符INANYALL 子查询 提升 创新 2009 亚信 57 内联视图 selectt from selectemployee id first name last namename salaryfromhr employeesorderby3desc twhererownum 5 显示薪水最高的5个员工 子查询 提升 创新 2009 亚信 58 关联子查询 selecte employee id e first name e last namename e salary e department idfromhr employeesewheree salary selectavg t salary fromhr employeestwheret department id e department id orderby4 2desc 子查询 提升 创新 2009 亚信 59 WITH子句 WITHdept costsAS SELECTd department name SUM e salary ASdept totalFROMhr employeese hr departmentsdWHEREe department id d department idGROUPBYd department name avg costAS SELECTSUM dept total COUNT ASdept avgFROMdept costs SELECT FROMdept costsWHEREdept total SELECTdept avgFROMavg cost ORDERBYdepartment name 统计部门成本在平均水平之上的部门 数据处理 向表中插入数据 处理数据 提升 创新 2009 亚信 61 向表中插入数据 INSERT语句 INSERTINTOtable column column VALUES value value insertintoemployees employee id first name last name email phone number hire date job id salary commission pct manager id department id values 207 Harry Potter POTTER 515 123 4599 to date 1981 02 09 yyyy mm dd AD VP 16000 null 100 90 处理数据 提升 创新 2009 亚信 62 使用带子查询的INSERT语句 INSERTINTOsales reps id name salary commission pct SELECTemployee id last name salary commission pctFROMhr employeesWHEREjob idLIKE REP 处理数据 提升 创新 2009 亚信 63 INSERT语句扩展 将数据插入多个表 INSERTALLINTOsal historyVALUES EMPID HIREDATE SAL INTOmgr historyVALUES EMPID MGR SAL SELECTemployee idEMPID hire dateHIREDATE salarySAL manager idMGRFROMemployeesWHEREemployee id 200 处理数据 提升 创新 2009 亚信 64 INSERT语句扩展 有条件插入数据INSERTALLWHENSAL 10000THENINTOsal historyVALUES EMPID HIREDATE SAL WHENMGR 200THENINTOmgr historyVALUES EMPID MGR SAL SELECTemployee idEMPID hire dateHIREDATE salarySAL manager idMGRFROMemployeesWHEREemployee id 200 数据处理 INSERT语句扩展 有条件插入数据INSERTFIRSTWHENSAL 25000THENINTOspecial salVALUES DEPTID SAL WHENHIREDATElike 00 THENINTOhiredate history 00VALUES DEPTID HIREDATE WHENHIREDATElike 99 THENINTOhiredate history 99VALUES DEPTID HIREDATE ELSEINTOhiredate historyVALUES DEPTID HIREDATE SELECTdepar