oracle数据库实用教程.ppt

欢迎参加学习 ORACLE数据库实用教程training 亚信科技 中国 有限公司培训中心 什么是数据库 数据库是管理数据的一种方法 如何组织和存储数据 如何高效地获取和处理数据 数据管理的发展的3个阶段 人工管理阶段无数据管理的专用软件 数据和程序在一起文件系统阶段软件进行数据管理 程序和数据分离数据冗余度大 数据和程序缺乏独立性文件之间不能建立联系数据库系统阶段数据具有更高的独立性数据库管理系统 DBMS 数据库的定义 操作和管理 关系型数据库 关系型数据库支持关系模型 以关系 表 形式组织数据 著名的关系型数据库DB2 ORACLE INFORMIX SYBASE等 关系型数据库 Datafile RDBMS RDBMS OS DB datafile1 datafile2 实验环境 telnet200 1 1 115用户名 口令 oracle8 oracle8 sqlplusscott tigersql connectscott tigersql showusersql quit 表 表 关系型DB数据组织的形式报道表空表 表的结构白纸 设计一张表 多少项内容 列 画表时 每项多宽列 字段 列类型 宽 类型 数据类型 长度 类型 行 记录表 列 行 字段 记录 姓名 编号 单位 性别 表 看scott用户有哪些表然后查看其中一个表的结构和内容 sqlplusscott tigersql selecttable namefromtabs user tables sql descemp sql select fromemp 表 表结构 DDL createtablealtertabledroptabletruncatetable 表内容 DML insertupdatedelete desctable name selectfield namefromtable name 基本数据类型 CHAR n n 255VARCHAR2 n n 2000LONG n n 2G NUMBER m n m精度 1 38 n小数后位数RAW n 二进制数据n 2000LONGRAW n n 2G DATE缺省形式 DD MON YY ROWID内部数据类型 数据库序号 行序号 数据文件号 NULL 字符串用单引号括起来 可以存储声音 图片 日期型数据的格式说明 Daysddnumber12dyabbreviatedfridayspelledoutFridayddspthspelledout ordinaltwelfthMonthmmnumber03monabbreviatedmarmonthspelledoutmarchYearyyyear87yyyyyearandcentury1987 日期型数据的格式说明 缺省日期型 dd mon yyselectsysdatefromdual 系统 date time改变日期形式 1 本次会话SQL ALTERSESSIONSETNLS DATE FORMAT YY MM DD 2 下次启动后永久生效 改文件设置INITSID ORA中的NLS DATE FORMAT参数可改变缺省日期的格式 表结构 1 建立sql createtabletable name column name1datatype notnull unique column name2datatype notnull unique column name2datatype notnull unique 表内容 1 插入记录sql insertintotable name column name a column name x values column value a column value x 表结构 2 变更1 修改sql altertabletable namemodify column namedatatype 2 增加sql altertabletable nameadd column namedatatype 表内容 2 修改记录sql updatetable namesetcolumn name1 values1 column name2 values2 wheresearch conditions 表内容 3 删除记录sql delete from table name wheresearch conditions sql truncatetabletable name 表结构 3 删除1 删结构及内容sql droptabletable name 事物处理 事物处理命令提交 commit回滚 rollback保留点 savepointSQL SAVEPOINTCLARK SAL SQL ROLLBACKTOSAVEPOINTBLAKE SAL 2 命令修改 a当前行末追加c old new更改clbuff清缓存del删除 n last mn i加入 i itext save保存edit vi编辑 start 3 保存命令 4 执行命令 1 显示命令 l显示 l ln lmn llast SQL PLUS 练习 建表注意利用SQL命令缓冲区插入 修改 删除数据 注意条件 提交 回滚 练习 表名txl n结构 学号数字型3唯一姓名可变长字符串10非空出生日期型电话数字型10查看结构 查看内容 练习 1 增加字段单位可变长字符串122 修改字段出生可变长字符串5 原日期 练习 插入数据至少5条修改数据 根据条件 删除数据 根据条件 SQL语句中的函数 字符函数substr length lower rtrim ltrim数值函数abs mod round trunc nvl日期函数months between add months next day转换函数to char to date 数据查询语言 DML 命令 SELECT语句SELECTFROMWHEREGROUPHAVINGORDERBY 简单查询 无条件查询条件查询排序统计分组统计 分组函数 统计函数 完成对一组记录的统计处理count distinct all x 行数sum x 数据列求和avg distinct all x 数据列平均值max x 列的最大值min x 列的最小值除count 外 当使用分组函数时 均忽略空值 分组函数 统计函数 练习 1工资的平均值2奖金的最大值3工资的总和4最低工资5表中数据行的总数6有多少个不同的部门 分组查询与排序 数据分组 对有重复值的列进行分组 查询 SELECTcol1 col2 分组函数FROMtable1WHEREconditionsGROUPBYcol1 col2HAVINGconditionORDERBYcol1 col2SELECT选择的列 必须出现在GROUPBY子句中 GROUPBY子句中的列只能是SELECT选择的列或分组函数中的列 顺序无关 分组查询 练习 1 编写SQL语句 从EMP表中返回 部门编号 部门平均工资 2 编写SQL语句 从EMP表中返回 部门编号 部门工资总和条件是 部门工资总和大于8000 3 编写SQL语句 从EMP表中返回 部门编号 部门平均工资条件是 除了部门10 部门工资平均值大于1500的部门的情况 多表查询 连接 等值连接非等值连接 自连接外连接 selectename dnamefromempe deptdwheree deptno d deptno 等值连接 EQUIJOIN 基于列的相等性来连接两张或多张表 题目 查找雇员姓名及所在部门的名称 非等值连接 NON EQUIJOIN 基于列的不等性来连接两张或多张表 题目 请查找雇员SMITH的工资和工资等级情况 希望显示的信息 员工名 工资 工资等级 非等值连接 NON EQUIJOIN 请分析下列语句 SELECTENAME SAL GRADEFROMEMP SALGRADWHEREENAME SMITH SELECT FROMSALGRAD SELECTENAME SAL GRADEFROMEMP SALGRADWHEREENAME SMITH ANDSALBETWEENLOSALANDHISAL 等值连接和非等值连接 题目 查找工资级别为2的雇员的信息 要求显示的信息 雇员的姓名 部门的名称 工资 工资级别 SELECTENAME DNAME SAL GRADEFROMEMP DEPT SALGRADEWHEREGRADE 2ANDSALBETWEENLOSALANDHISALANDEMP DEPTNO DEPT DEPTNO 自我连接 SELFJOIN 将表连接到自身在SQL语句中一个表暂时重新命名为两个表 题目 EMP中查找员工和其经理的对应关系 要求 员工名称和其经理的名称 同一基表的不同行做联接 如同两个独立的基表 在FROM子句中至少要为一个基表起别名 以区别联接条件中的列名 自连接 SELFJOIN SELECTWORKER ENAME MANAGER ENAMEMANAGERFROMEMPWORKER EMPMANAGERWHEREWORKER MGR MANAGER EMPNO 外连接 OUTERJOIN 用于返回存在在一张表上的所有数据行 甚至相对应的行在连接的表中不存在 号用于在查询中的OUTERJOIN 带 的表是那些没有相应行的表 外连接 OUTERJOIN 题目1 EMP中查找员工和其经理的对应关系 要求 员工名称和其经理的名称 请注意使用外连接及 放置位置 SELECTWORKER ENAME MANAGER ENAMEMANAGERFROMEMPWORKER EMPMANAGERWHEREWORKER MGR MANAGER EMPNO 外连接 OUTERJOIN 题目2 查找哪些部门有叫SMITH的人显示 雇员名称 部门名称 所有部门都要列出来 ENAMEDNAME ACCOUNTINGSMITHRESEARCHSALESOPERATIONS SELECTENAME DNAMEFROMEMPE DEPTDWHEREE DEPTNO D DEPTNOANDE ENAME SMITH 外连接 OUTERJOIN 题目3 查找哪些部门没有叫SMITH的人要求显示信息 雇员名称 部门名称 ENAMEDNAME ACCOUNTINGSMITHRESEARCHSALESOPERATIONS selectename dnamefromempe deptdwheree deptno d deptnoande ename SMITH ande enameisnull 子查询 单行值子查询多行值子查询多列子查询相关子查询 单行值子查询 工资高于7698号雇员 并且工种与他相同的雇员情况SQL SELECTENAME SAL JOBFROMEMPWHERESAL SELECTSALFROMEMPWHEREEMPNO 7698 ANDJOB SELECTJOBFROMEMPWHEREEMPNO 7698 多行值子查询 谓词IN NOTIN哪些部门没有叫SMITH的雇员 SQL selectdept deptno dname locfromdept2wheredept deptnonotin3 selectdeptnofromemp4whereename SMITH 多行值子查询 ANY 子查询返回结果中的任一值满足条件即可 ANY前可以使用 SELECTEMPNO ENAME SAL2FROMEMP3WHERESAL ANY SELECTSALFROMEMP4WHEREJOB SALESMAN 5ANDJOB SALESMAN 多行值子查询 ALL 子查询返回结果中的所有值满足条件才行 查找工资高于20号部门所有雇员的人员信息 SQL SELECTEMPNO ENAME SALFROMEMP2WHERESAL ALL SELECTSALFROMEMPWHEREDEPTNO 20 也可表达为 SQL SELECTEMPNO ENAME SALFROMEMP2WHERESAL SELECTMAX SAL FROMEMPWHEREDEPTNO 20 多行值子查询 EXISTS 子查询至少返回一行时条件成立 NOTEXISTS 子查询不返回任何行时条件成立 用途确定一个表中的哪些信息在另一个表中没有匹配 用NOTEXISTS代替NOTIN 多列子查询 例 查出工资 奖金与20号雇员匹配的人员 SQL selectenamefromempwhere nvl comm 0 sal in selectnvl comm 0 salfromempwheredeptno 20 相关子查询 例 查出工资 奖金与20号雇员匹配的人员 SQL selectenamefromempwhere nvl comm 0 sal in selectnvl comm 0 salfromempwheredeptno 20 其它查询 集合UNIONINTERSECTMINUS树查询 UNION UNION 将表和视图中的记录合并在一起 所有部门中有哪些雇员的工资超过2OOO SQL select fromempwheresal 2000SQL selectename salfromdept10wheresal 2000unionselectename salfromdept20wheresal 2000unionselectename salfromdept30wheresal 2000 视图 SQL createviewdept10as2selectename job salfromemp3wheredeptno 10 dept20 dept30的创建过程同dept10 INTERSECT 返回查询结果中相同的部分 哪些部门有相同的工种 SQL selectdistincta jobfromempa empbwherea deptnob deptnoanda job b jobSQL selectjobfromdept30intersectselectjobfromdept20intersectselectjobfromdept10 MINUS 返回第一个查询结果中与第二个查询结果不相同的那部分行记录有哪些工种是在财会部有 而在销售部中没有 SQL selectjobfromempwheredeptno 10andjobnotin selectjobfromempwheredeptno 30 SQL selectjobfromdept10minusselectjobfromdept30 子查询注意 子查询必须用括号括住 子查询必须只有一列 或者子查询的多列与主查询中用括号括起来的多个列连接 单行值子查询可以使用单值或多值运算符 多行子查询必须使用多值运算符 BETWEENAND不能用于子查询 子查询中不能使用ORDERBY子句 测试操作运算符 单值 LIKE ISNULL ISNOTNULL 多值 IN NOTIN BETWEENAND NOTBETWEENAND ANY NOTANY ALL NOTALL EXISTS NOTEXISTS ORACLE数据库体系结构 ORACLE数据库的存储结构ORACLE数据库的软件体系结构 1 逻辑存储结构2 物理存储结构 1 实例2 Oracle8网络 锁机制和SCN 数据库存储结构 物理结构是实际数据的存储单元 例如数据库文件逻辑结构是数据概念上的组织 例如表空间因为物理和逻辑结构是分开的 数据物理存储的管理并不影响逻辑结构 能灵活的独立管理 表空间和数据文件 由组成数据库的操作系统文件决定数据库的文件提供了数据库信息的实际物理存储三种类型的文件控制文件 记录数据库的物理结构数据文件 包括全部数据库数据日志文件 记录对数据库所做的修改 物理结构 数据库文件 Oracle数据库结构组成 ParameterFile Datafiles ControlFiles RedoLogFiles OracleDatabase 物理结构 控制文件 控制文件 物理结构 控制文件 控制文件 物理结构 控制文件 1 ORACLE HOME dbs目录下 ls ora initTX08 oraconfigTX08 oracontrol files data1 oracle8 oradata TX08 control01 ctl data1 oracle8 oradata TX08 control02 ctl data1 oracle8 oradata TX08 control03 ctl 2 SQL select fromV controlfile 物理结构 日志文件 联机重做日志文件 归档日志文件 以ORACLEFORSOLARIS为例 数据文件 mount point oradata ora sid system01 dbfmount point oradata ora sid tools01 dbfmount point oradata ora sid users01 dbfmount point oradata ora sid temp01 dbfmount point oradata ora sid rbs01 dbf控制文件 mount point oradata ora sid control01 ctlmount point oradata ora sid control02 ctlmount point oradata ora sid control03 ctl日志文件 mount point oradata ora sid redoora sid01 logmount point oradata ora sid redoora sid02 logmount point oradata ora sid redoora sid03 log 逻辑组成 多个表空间 tablespace 组成一个表空间是一个逻辑存储区数据库模式 schema 对象模式是对象的集合 模式对象直接涉及数据库数据的逻辑结构 包括表 视图 序列 存储过程 同义词 索引等 逻辑存储结构包括表空间 段 segment 区 extent 块 block 指明如何使用数据库物理空间 表空间的几个概念 表空间分类 System表空间 其它表空间只读表空间 可读写表空间表空间的状态 脱机 联机 控制数据的可用性表空间大小的管理 建立 增加 修改 表空间的几个概念 表空间的状态 脱机 联机 控制数据的可用性SQL altertablespacenameofflinenormal SQL select fromuser tablespaces SQL altertablespacetestonline SQL selecttable name tablespace namefromtabs 单一表空间 system表空间 多个表空间 system表空间和其它表空间 表空间中数据文件的自动增长 表空间的物理存储区 数据文件 数据库对象的物理存储区 段 例子 表空间与表空间中对象 CREATETABLESPACEuser dataDATAFILE data user1Tx08 dbf SIZE5MONLINEDEFAULTSTORAGE INITIAL10KNEXT10KMINEXTENTS2MAXEXTENTS50PCTINCREASE0 逻辑结构 段 区和数据块 数据库空间的分配单元是段 区和数据块 段 决定数据库对象使用的全部区的集合 区 由连续的数据块的集合 块 Oracle数据库磁盘存取的最小的单元 保存表和簇的所有数据的区的聚集保存特殊索引的所有数据的区的聚集 1 数据段 2 索引段 逻辑结构 段类型 4 临时段 回滚段记录一个事务的回滚数据 事务可以使用提交或回滚结束 回滚即撤消事务的全部作用 就象事务从未发生过 要实现回滚 必须跟踪事务所更改的数据 直到提交或回滚 保存属于临时对象的数据的区的聚集SQL语句经常要求临时工作区 例如 当为一个大表创建索引时 必须分配临时空间 排序全部的索引项 当处理一条需要临时工作空间的SQL 就从数据库的表空间中分配小的临时段 语句完成后 将段释放回表空间 3 回滚段 逻辑结构 段类型 查询有哪些类型的段 SQL selectdistinctsegment typefromdba segments 以SYS和SYSTEM用户身份登录 逻辑结构 段类型 查询某一用户在某一表空间中段的占有情况 SQL columnsegment nameformata10SQL select fromuser segments SQL select fromuser segmentswheretablespace name TEST 数据段每张非聚簇表有一个单独的数据段来存放所有的数据 SQL selectsegment namefromdba segmentswheresegment type TABLE 逻辑结构 段类型 索引段每个索引有单独的索引段来存放数据 SQL selectsegment namefromdba segmentswheresegment type INDEX ROLLBACKTEMPORARY 逻辑结构 区 当创建新的数据对象 表 索引等 时 Oracle为对象的段分配一个或多个区 区是存储对象段的表空间数据文件中的一系列连续的数据块 逻辑结构 区 区的设置 决定区的参数 创建段时 可以决定分配区的数目 maxextents最大分配的区数 minextents最小分配的区数可以控制区的大小 能够设置段的初始区间和后续区间的大小initial最初分配的空间数 缺省为10k 5个数据块 next下一步分配的空间数pctincrease增长率 指数级增长 逻辑结构 区 SQL select fromuser extents SQL select fromdba extents SYS用户和SYSTEM用户 逻辑结构 数据块 最小的磁盘存取单元 当操作一个数据库时 Oracle使用数据块存储和提取磁盘上的数据 当查询一个表时 Oracle将含有查询结果集记录的全部数据快读取到服务器的内存中 由一个或多个O S块组成在数据库创建时设定块大小 块大小必须等于O S块的大小或它的倍数 如果服务器的操作系统块大小是512K 数据块大小可以是512K 1024K和2048K等 Inserts Inserts Inserts 1 2 3 4 PCTFREE 20 80 80 40 逻辑结构 数据块空间 PCTUSED 40 Locationof Free Extent Madeupof In Locationof Partof Madeupof Controlling Controlledby Partof Madeupof Partof Madeupof Madeupof Partof Madeupof Partof Partof Partof 数据库结构组成 实例 实例 PGA SGA 后台进程 程序全局区 PGA 程序全局区 PGA 用于保存服务器进程的数据和控制信息 当用户进程要连接到Oracle数据库服务器时 会在实例中为其分配相应的服务器进程 SGA组成 SharedPool DatabaseBufferCache RedoLogBuffer SystemGlobalArea SGA SharedSQLAreas DataDictionary 系统全局区 SGA 1 数据库高速缓存 DataBlockBufferCache 系统全局区 SGA 2 重做日志缓冲区 RedoLogBuffers 系统全局区 SGA 3 数据字典高速缓存 DictionaryCache 该高速缓存用于存放最近使用和经常使用的数据字典信息 当下次需要这些信息时 就可以直接从数据字典高速缓存中读取到这些信息 从而提高系统性能 系统全局区 SGA 共享SQL池用于保存SQL语句的执行计划和分析树 当执行SQL语句时 系统会将相应的执行计划和分析树保存到共享SQL池中 然后当再次执行这些SQL语句时 系统会自动使用原有执行计划和分析树 从而提高了系统性能 4 共享SQL池 SharedSQLPool Oracle后台线程 后台进程 1 DBWR 后台进程 2 LGWR 后台进程 3 ARCH 实例与数据库的关系 一对一 一对多 创建实例 OPEN MOUNT NOMOUNT SHUTDOWN Allfilesopenedasdescribedbythecontrolthisinstance Controlforthisinstance Instancestarted STARTUP 启动一个实例 STARTUP 数据库的启动 启动实例 nomount打开初始化参数文件svrmgr connectinternalsvrmgr startupnomount svrmgr select fromv process svrmgr select fromv controlfile svrmgr select fromv logfile svrmgr select fromv datafile 数据库的启动 装配数据库 mount打开控制文件方法1 svrmgr alterdatabasemount 方法2 svrmgr connectinternalsvrmgr startupmount 查询操作同前页 数据库的启动 打开数据库 open打开数据文件和日志文件方法1 svrmgr alterdatabaseopen 方法2 svrmgr connectinternalsvrmgr startup svrmgr select fromuser tablespaces svrmgr select fromdba data files OPEN MOUNT NOMOUNT SHUTDOWN Allfilesopenedasdescribedbythecontrolthisinstance Controlforthisinstance Instancestarted SHUTDOWN 关闭一个实例 SHUTDOWN 数据库实例的关闭 关闭数据库关闭数据文件和日志文件svrmgr shutdown 卸下数据库关闭控制文件svrmgr shutdown 停止实例关闭初始化参数文件svrmgr shutdown Oracle8网络与Net8 Net8 Oracle的网络连接软件连接描述器与tnsnames ora监听进程与listener ora监听进程的操作命令配置服务器配置客户机调试连接问题 Net8 SQL NET原理及所需配置文件 listener ora tnsnames ora Server 连接描述器与tnsnames ora TX08 DESCREPTION ADDRESS PROTOCOL TCP HOST 200 1 1 115 PORT 1521 CONNECTDATA SID TX08 ORACLE HOME network admin tnsnames oratnsnames ora2个要求 监听进程与listener ora Listener ADDRESSLIST ADDRESS PROTOCOL TCP HOST 200 1 1 115 SID LIST LISTENER SIDLIST SID DESC SID NAME TX08ORACLE HOME DATA1 ORACLE TX08 1 地址列表 2 监听服务的实例列表 监听进程的操作命令 lsnrctllsnrctl startlistenernamestoplistenernamestatuslistenernameversionlistenernamesetcurrent listenerlistenername 检查监听进程是否启动 ps ef greptnslsnr 配置服务器 识别可用宿主 etc hosts 解决地址映射识别可用服务 etc services listener1521识别可用数据库 var opt oracle oratabOracle SID ORACLE HOME N Y启动Net8 lsnrctl 配置客户机 识别可用宿主 etc hosts or 解决地址映射识别可用服务 etc services or listener1521NIC 网络接口卡 OS TCP IP NET8运行NET8 远程访问数据库 Sqlplus用户名 密码 服务名 连接串 Sqlplusscott tiger TX08 调试 连接问题 可达 telnet ping 客户端 远程主机 监听 服务器 通过 连自己客户端 tnsping服务名次数 安全管理 用户管理创建用户createuser修改用户alteruser删除用户dropuser 用户与模式 用户分类 超级用户数据库管理员普通用户 超级用户 internal 数据库管理员 数据库管理员 DBA 是指具有DBA角色的用户 它们用于管理发生在数据库内的操作 如SYS SYSTEM 普通用户 普通用户是指根据特定需求使用命令CREATEUSER所创建的用户 数据库验证 CREATEUSERacaIDENTIFIEDBYasiainfo CONNECTaca asiainfo 外部验证 CREATEUSEROPS administratorIDENTIFIEDEXTERNALLY 企业验证 企业验证是指由OracleSecurityService OSS 来验证数据库用户的方式 当使用这种方式验证数据库用户时 由Oracle数据库来管理用户帐户 并且口令和用户鉴定则由OSS来完成 创建用户 创建用户ACA 创建用户 CREATEUSERusernameIDENTIFIEDBYuserpassword DEFAULTTABLESPACEtablespace1 TEMPORARYTABLESPACEtablespace2 QUOTAnKONtablespace1 MUNLIMITED 空间限额 修改用户 修改用户username所使用的缺省表空间修改用户的口令 ALTERUSERusernameTABLESPACEuser data ALTERUSERusernameIDENTIFIEDBYuserpasswd 删除用户 DROPUSERusername cascade 特权管理 特权简介系统特权的授予和收回对象特权的授予和收回 系统特权 对象特权 特权管理 系统权限 systemprivilege 针对整个系统操作的权限dba sys privs 用户权限 objectprivilege 针对具体object操作权限dba tab privs 视图dba col privs 系统特权的授予和收回 授予用户系统特权GRANTsystemprivsTOusername WITHADMINOPTION 收回系统特权REVOKEsystemprivsFROMusername 对象特权的授予和收回 授予用户对象特权GRANT对象特权TOusername WITHGRANTOPTION 收回对象特权REVOKE对象特权FROMusername 授于用户连接Oracle数据库的权限 Sgrantconnectto username withgrantoption rolename privilege ObjectPrivilegeSELECTUPDATEINSERTALTERDELETEEXECUTEINDEXREFERENCES TypesofObjectPrivileges TableViewSequenceProcedure 1 2 3 1包括存储过程 函数和包2该权限不能授予角色3授予快照 对象权限种类表 角色管理 角色简介创建 修改 删除角色授予和收回角色特权授予和收回用户角色禁止和激活角色 角色简介 角色简介 角色分类角色特权授予和收回用户角色角色口令禁止和激活角色 角色 连接角色connect连上Oracle 做最基本操作资源角色resource具有