Oracle数据库入门

线上线下 平台预习

本章任务 安装Oracle数据库 创建员工表和部门表，并对表中数据进行CRUD操作 对员工表进行分页查询 按照业务的需要，使用SQL语言对员工信息数据进行查询

本章目标 理解数据库基本概念 掌握安装、配置和连接数据库 了解数据类型和操作符 会使用SQL语句对数据进行操作 会使用常用内置函数

Oracle简介 Oracle数据库管理系统 Oracle（甲骨文）公司 Oracle公司的核心产品 1977年，三人合伙创办（Software Development Laboratories，SDL） 1979年，更名为Relational Software Inc.，RSI 1983年，为了突出核心产品 ，RSI更名为Oracle 2002年04月26日，启用“甲骨文”作为中文注册商标 Oracle数据库管理系统 Oracle公司的核心产品 目前最流行的数据库 主要版本Oracle8i/9i(internet)、Oracle10g/11g(grid) 基于C/S系统结构

PMON SMON DBWR LGWR CKPT 其他 Oracle 主要组件 实 例 内存结构 用户进程 SGA 共享池 数据缓冲区 日志缓冲区 服务器进程 后台进程 PGA PMON SMON DBWR LGWR CKPT 其他 数据文件 控制文件 日志文件 参数文件 教学指导： 重点讲解数据库、数据库实例 、数据文件 、控制文件 、日志文件 。介绍实例时可以这样讲解：实例启动时要分配内存并要启动相关的后台进程。 至于内存结构和后台进程细分可以弱化。 数据库 磁盘上存储的数据的集合 在物理上表现为数据文件、日志文件和控制文件等 在逻辑上以表空间形式存在 必须首先创建数据库，然后才能使用Oracle 数据库实例 每个启动的数据库都对应一个数据库实例，由这个实例来访问和控制数据库 为了运行数据库，Oracle系统所运行的所有进程和分配的内存结构的组合体 数据文件 扩展名是.DBF，用于存储数据库数据的文件 数据库表和数据文件不存在一对一对应关系 控制文件 扩展名是.CTL，是数据库启动及运行所必需的文件 默认包含3个控制文件，各个控制文件内容相同 日志文件 扩展名是.LOG，它记录了对数据的所有更改信息 多个日志文件组之间循环使用 数据文件 控制文件 日志文件 归档日志文件 口令文件 数据文件 数据库

Oracle基本概念 表空间 全局数据库名 模式和模式对象 表空间 全局数据库名 模式和模式对象 用于区分一个数据库的内部标识 全局数据库名=数据库名+域名 使数据库的取名在整个网络环境中唯一 模式和模式对象 模式为模式对象的集合 每一个用户对应一个模式 模式对象是用户拥有的对象 非模式对象与用户无关，如表空间 数据库 表空间1 表空间2 表空间1包含 1个数据文件 表空间2包含 2个数据文件 data1_01.dbf data2_01.dbf data2_02.dbf 教学指导： 表空间 每个Oracle数据库都是由若干个表空间构成 用户在数据库中建立的所有内容都被存储到表空间中 创建数据库时会自动创建若干表空间 全局数据库名 用于区分一个数据库的内部标识，在安装数据库、创建新数据库等等都需要使用数据库名 全局数据库名=数据库名+域名 使数据库的取名在整个网络环境中唯一 模式和模式对象 模式为模式对象的集合，每一个用户对应一个模式 模式对象是用户拥有的对象，如表、视图、索引、序列、同义词、存储过程和函数等 非模式对象与用户无关，如表空间。在某些情况下，是用户SYS拥有的对象，并且可以被所有用户访问，如公有同义词。

输入的数据库口令最好记录在记事本中，以免使用时忘记 安装Oracle 安装注意事项 选择安装目录最好是非系统盘 1 2 安装时可以直接创建数据库，也可以选择不创建 服务器的主机名不能有特殊字符 3 4 如果在创建数据库阶段提示错误，继续安装 经验 输入的数据库口令最好记录在记事本中，以免使用时忘记 教学指导： 此时提示学生打开书上附录“安装Oracle”，由技术顾问参照教材讲解一遍后，看PPT。 1、安装时不创建数据库可以大大缩短时间，安装完后通过Oracle自带DBCA工具创建。对于刚刚学习的同学，安装时建议最好自动完成全部创建。如果在创建数据库阶段提示错误，继续安装，待安装完毕后，通过DBCA工具重新创建数据库。不必再重新安装数据库软件 2、服务器的主机名不能有特殊字符，例如主机名为lab_01,安装过程中可能因为有“_”而报错。 3、讲解完后，一定提示学生，务必安装时参照附录中的” 安装Oracle”相关内容。

根据安装时创建数据库ORCL，使用Sytem用户登录前做哪些工作？ 启动、配置和链接数据库 问题 根据安装时创建数据库ORCL，使用Sytem用户登录前做哪些工作？ 启动数据库服务 配置客户端网络服务 每个数据库都有SYS和SYSTEM两个默认用户，都具有创建用户权限 使用SYSTEM用户登录ORCL数据库 分析

通过选择打开“服务”窗口，可以看到Oracle服务 启动数据库服务 通过选择打开“服务”窗口，可以看到Oracle服务 OracleService<SID>：数据库服务 OracleOraDb11g_hom1TNSListener ：数据库监听服务 OracleDBConsole<SID>：企业管理器服务 教学指导： 此处讲解这些服务的功能以及是否需要启动。 其它服务可以参考学习平台提供的资料学习。 演示示例1：查看Oracle服务

Oracle中的 Net Manager工具和Net Configuration Assistant都能用来配置监听器和网络服务名 配置客户端网络服务 服务器端配置监听器 客户端配置网络服务名 listener.ora Oracle 服务器 本地网络服务名 注意 tnsnames.ora Oracle 客户端 监听协议 在安装服务器软件时自动配置一个监听器 Oracle中的 Net Manager工具和Net Configuration Assistant都能用来配置监听器和网络服务名 建议使用Net Manager工具 监听协议 地址 地址 端口号 端口号 服务名 全局数据库名称 教学指导： 1、技术顾问演示配置过程，学员可以参考书中附录中的提示 2、配置完后可以打开以下2个文件对比讲解 Oracle服务器端配置文件为listener.ora,代码片段： SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcl) (SID_NAME = ORCL) ) 其中GLOBAL_DBNAME 为全局数据库名。 Oracle客户端配置文件为tnsnames.ora,代码片段： ORCLDB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.160)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = orcl) 其中SERVICE_NAME为服务名，它的值取决于listener.ora中global_dbname的值。 3、可以参照上机参考答案中提供的2个文件：listener.ora 和 tnsnames.ora 演示示例2：连接数据库配置

通过PL/SQL Developer方式连接 连接数据库 SYS和SYSTEM用户 SYS和SYSTEM用户都是Oracle 的系统用户，都使用SYSTEM表空间，SYS拥有更大的权限 通过SQL*Plus方式连接 通过PL/SQL Developer方式连接 SYS用户 SYSTEM用户 地位 Oracle的一个超级用户 Oracle默认的系统管理员，拥有DBA权限 作用 主要用来维护系统信息和管理实例 通常用来管理Oracle数据库的用户、权限和存储等 登录身份 只能以SYSDBA或SYSOPER角色登录 只能以Normal方式登录

学员操作—配置并登录ORCL数据库2-1 训练要点 需求说明 启动数据库 配置数据库 连接数据库 教师机作为服务器，学生机作为客户端 指导 训练要点 启动数据库 配置数据库 连接数据库 需求说明 教师机作为服务器，学生机作为客户端 在客户端创建本地网络服务名myOrcl 以System用户连接服务器的Orcl数据库 访问Scott用户下的emp表 技术顾问讲解需求

学员操作—配置并登录ORCL数据库2-2 实现思路 服务器端启动操作系统中的数据库服务和监听服务 指导 实现思路 服务器端启动操作系统中的数据库服务和监听服务 客户端使用“Net Manager”工具创建本地网络服务名“myOrcl” 客户端使用SQL*Plus工具，用System用户连接Orcl数据库 访问Scott用户下emp表 使用PL/SQL Developer工具，以System用户连接Orcl数据库 SELECT * FROM Scott.emp; 完成时间：20分钟

共性问题集中讲解 常见问题及解决办法 代码规范问题 调试技巧 共性问题集中讲解 教学指导： xxxxxxx

服务器端 客户端 小结 使用 Oracle 数据库的开发流程 安装 Oracle 服务器软件 安装 Oracle 客户端软件 创建数据库(安装时自动创建) 配置监听器(安装时自动配置) 启动Oracle实例(自动启动服务) 创建用户表空间 创建新用户并授权 安装 Oracle 客户端软件 配置网络服务名 以新用户登录 Oracle 提交 SQL 查询 服务器端 客户端 后面章节讲解

不建议使用VARCHAR、 INTEGER、FLOAT、DOUBLE等类型 数据类型 数据类型 描述 CHAR： VARCHAR2： NCHAR和NVARCHAR2： 存储固定长度的字符串 存储可变长度的字符串 存储Unicode字符集类型 字符数据类型 数值数据类型 NUMBER： 存储整数和浮点数，格式为NUMBER(p, s) 日期时间数据类型 DATE ： TIMESTAMP： DATE：存储日期和时间数据 TIMESTAMP：秒值精确到小数点后6位 BLOB： CLOB： BFILE： 存储二进制对象 存储字符格式的大型对象 将二进制数据存储操作系统文件中 LOB数据类型 教学指导： NUMBER数据类型按照书上的案例讲解就可以了。 BFILE：讲解BFILE类型时只要说明 1.可以存图片，也可以存文字。存储的信息放到数据库外的一个文件中，在表中存放路径 2.优点大大缩小了表的大小，提高了所有相关数据库操作的速度 3.缺点：造成了存储信息如图像和其描述信息的分离，称为分离的两个部分，不便管理 解决办法：建立严格的同步管理机制，使得备份等等各种操作都同步进行 经验 不建议使用VARCHAR、 INTEGER、FLOAT、DOUBLE等类型

伪列 AAAR3s AAE AAAACX AAA Oracle 中伪列就像一个表列，但是它并没有存储在表中 伪列可以从表中查询，但不能插入、更新和删除它们的值 常用的伪列有ROWID和ROWNUM ROWNUM 是查询返回的结果集中行的序号，可以使用它来限制查询返回的行数 如何查询stuInfo表中自然排序的第3条记录？ ROWID格式 提问 AAAR3s AAE AAAACX AAA ROWID 是表中行的存储地址，该地址可以唯一地标识数据库中的一行，可以使用 ROWID 伪列快速地定位表中的一行 数据对象编号 文件编号 块编号 行编号 教学指导： 1、可以上机操作，先后查询ROWID列 2、上机操作查询ROWNUM列 再讲解提问的问题。参考答案：SELECT * FROM (SELECT s.*,ROWNUM FROM stuInfo s) WHERE ROWNUM=3 演示示例1：查询ROWID伪列 演示示例2：查询ROWNUM伪列

如何从EMP表中查询出薪水从高到低排序的第5~9条记录？ 分页 提问 如何从EMP表中查询出薪水从高到低排序的第5~9条记录？ 使用伪列ROWNUM实现分页查询 分析 SELECT * FROM ( SELECT e.*,rownum rn FROM ( SELECT * FROM emp ORDER BY sal DESC) e ) WHERE rn>=5 AND rn<=9

小结 Oracle中常用数据类型 Oracle伪列包含 字符类型 数值类型 日期类型 LOB类型 ROWNUM ROWID 此页适用情况：有文字和少部分代码

SQL语言 SQL 简介 数据定义语言（DDL） 数据操纵语言（DML） 事务控制语言（TCL） 数据控制语言（DCL） CREATE ALERT DROP TRUNCATE INSERT UPDATE DELETE SELECT SQL语言 事务控制语言（TCL） 数据控制语言（DCL） COMMIT SAVEPOINT ROLLBACK GRANT REVOKE 教学指导： 数据定义语言用于改变数据库结构，包括创建、更改和删除数据库对象 数据操纵语言用于检索、插入和修改数据，是最常见的SQL命令 事务是最小的工作单元，作为一个整体进行工作，称为事务控制 数据控制语言为用户提供权限控制命令

现有stuInfo表和数据，对该表中数据执行操作 数据定义操纵语言 问题 现有stuInfo表和数据，对该表中数据执行操作 STUNO STUNAME STUAGE STUID STUSEAT 1  张三 18 1 2  李四 20 2 3  王五 15 3 4  4 5  5 选择无重复的行 选择带条件和排序的记录 实用列别名 利用现有的表创建新表 查看表中行数 取出不重复数据的记录 删除指定列重复的行 查看当前用户所有数据量>100万的表的信息 教学指导： 请仔细介绍表中数据，因为后面的查询需要对表中的数据相当的熟悉，才能够理解查询要到达的目的。 演示示例3：创建stuInfo表

按照姓名升序，如果姓名相同按照年龄降序排序 数据操纵语言4-1 示例 不重复显示所有学员姓名和年龄 按照姓名升序，如果姓名相同按照年龄降序排序 SQL>SELECT DISTINCT stuName,stuAge FROM stuInfo; SQL>SELECT stuNo,stuName, stuAge FROM stuInfo WHERE stuAge>17 ORDER BY stuName ASC, stuAge DESC; 升序 降序 演示示例4：操纵stuInfo表

数据操纵语言4-2 使用别名显示姓 名、年 龄和身份证号列 利用现有的表创建新表 示例 SQL> SELECT stuName as "姓 名", stuAge as "年 龄", stuID as 身份证号 FROM StuInfo; 含有特殊字符（如空格）加双引号 SQL> CREATE TABLE newStuInfo1 AS SELECT * FROM StuInfo; 选择所有数据 SQL> CREATE TABLE newStuInfo2 AS SELECT stuName,stuNo,stuAge FROM StuInfo; 选择指定的列所有数据 SQL> CREATE TABLE newStuInfo2 AS SELECT stuName,stuNo,stuAge FROM StuInfo WHERE 1=2; 只留表结构，不留数据

取出stuName,stuAge列不存在重复数据的记录 数据操纵语言4-3 示例 执行效率低 查看表中行数 取出stuName,stuAge列不存在重复数据的记录 SQL>SELECT COUNT (*) FROM stuInfo; SQL>SELECT COUNT (1) FROM stuInfo; 执行效率高 SQL> SELECT stuName,stuAge FROM stuInfo GROUP BY stuName,stuAge HAVING(COUNT(stuName||stuAge) <2); 重复的个数为1

删除stuName、stuAge列重复的行(保留一行) 数据操纵语言4-4 示例 删除stuName、stuAge列重复的行(保留一行) SQL>DELETE FROM stuInfo WHERE ROWID NOT IN( SELECT max(ROWID) GROUP BY stuName,stuAge );

事务控制语言2-1 用于事务控制的语句 下列代码执行后，结果集是否包含50、60和70的记录？为什么？ 演示示例5：事务控制语言 COMMIT ROLLBACK SAVEPOINT ROLLBACK TO <SavePoint_Name> 提问 下列代码执行后，结果集是否包含50、60和70的记录？为什么？ SQL>INSERT INTO dept VALUES(50,'a',null); SQL>INSERT INTO dept VALUES(60,'b',null); SQL>SAVEPOINT a; SQL>INSERT INTO dept VALUES(70,'c',null); SQL>ROLLBACK TO SAVEPOINT a; SQL>COMMIT; SQL>SELECT * FROM dept; 演示示例5：事务控制语言

事务控制语言2-2 何时结束事务？ 数据被提交 数据被撤消 COMMIT命令 执行DDL或DCL后 与Oracle分离 ROLLBACK命令 服务器进程异常结束 DBA停止会话

学员操作—创建员工表并进行操作2-1 训练要点 需求说明 创建员工表 插入、查询数据 添加约束 添加、删除列 指导 训练要点 创建员工表 插入、查询数据 添加约束 添加、删除列 需求说明 使用System用户登录，创建员工表employee 为员工编号创建主键约束，部门编号列创建外键约束 根据提供的资料插入数据，显示员工表中薪水从高到低排序记录 技术顾问讲解需求

学员操作—创建员工表并进行操作2-2 实现思路 使用System用户连接ORCL数据库 创建employee表 插入数据 指导 实现思路 使用System用户连接ORCL数据库 创建employee表 插入数据 可以直接插入数据，也可以利用SCOTT用户下emp中 的数据进行插入 添加约束 向employee 表添加empTel_no 和empAddress 两列 删除empTel_no 和empAddress 两列 按照薪水从高到低显示数据 完成时间：15分钟

学员操作—实现分页查询需求 练习 需求说明 使用System用户登录，根据employee表现有记录，实现查询员工表中薪水从高到低排序的第5~9条记录 完成时间：15分钟

共性问题集中讲解 常见问题及解决办法 代码规范问题 调试技巧 共性问题集中讲解 教学指导： xxxxxxx

Oracle SQL语言分为DDL、DCL、TCL和DML 小结 Oracle SQL语言分为DDL、DCL、TCL和DML DDL对表结构进行管理 DML对表数据进行管理 TCL进行事务控制 DML进行用户权限管理 此页适用情况：有文字和少部分代码

SQL 操作符 Oracle 支持的 SQL 操作符分类 集合操作符 UNION（联合） UNION ALL（联合所有） INTERSECT（交集） MINUS（减集） SQL 操作符 算术操作符 逻辑操作符 连接操作符 比较操作符 集合操作符 连接2个字符串； 连接字符串和数字等 教学指导： 此时回顾其他操作符，重点介绍集合操作符和连接操作符，连接操作符具体例子回顾数据操纵语言示例。 演示示例6：操作符示例

SQL 函数 SQL函数 02.分组函数 03.分析函数 日期函数 数字函数 字符函数 转换函数 其他函数 01.单行函数 每一行只返回一个值 可以出现在 SELECT 子句中和 WHERE 子句中 02.分组函数 根据分组的情况，返回每组里的一个结果值 可以在使用ORDER BY 和 HAVING子句中使用 03.分析函数 分析函数根据一组行来计算聚合值 分析函数为每组记录返回多个行 日期函数 数字函数 字符函数 转换函数 其他函数 SQL 函数 教学指导： 这里介绍转换函数、其他函数和分析函数。在T-SQL中已经学习过相关类似函数。

转换函数将值从一种数据类型转换为另一种数据类型 常用的转换函数 TO_CHAR() TO_DATE() TO_NUMBER() 示例 SELECT TO_CHAR(sysdate,'YYYY"年"fmMM"月"fmDD"日" HH24:MI:SS') FROM dual; SELECT TO_CHAR(1210.7, '$9,999.00') FROM dual; SELECT TO_DATE('2005-12-06','yyyy-mm-dd') FROM dual; SELECT TO_NUMBER('100') FROM dual; 演示示例7：转换函数

其它函数 转换空值的函数 NVL DECODE 演示示例8：其他函数 SELECT ename, sal+NVL(comm,0) sal1, NVL2(comm,sal+comm,sal) sal2, DECODE(to_char(hiredate, ‘MM ’), '01', '一月', '02', '二月', '03', '三月','04','四月', '05','五月','06','六月', '下半年') mon FROM employee; 教学指导： 可以显式employee表的结果给学生看，让学生根据结果说出上例中的查询结果。 演示示例8：其他函数

函数名([ 参数]) OVER( [ 分区子句 ] [ 排序子句]) 分析函数 分析函数根据一组行来计算聚合值 分析函数为每组记录返回多个行 以下三个分析函数用于计算一个行在一组有序行中的排位，序号从1开始 语法 函数名：分析函数名字 参数：函数需要传入的参数 分区子句（PARTITION BY）：将查询结果分为不同的组，功能类似于GROUP BY语句 排序子句（ORDER BY）：将每个分区进行排序 例： 函数名([ 参数]) OVER( [ 分区子句 ] [ 排序子句]) RANK DENSE_RANK ROW_NUMBER 返回连续的排位，不论值是否相等 具有相等值的行排位相同，序数随后跳跃 具有相等值的行排位相同，序号是连续的 RANK( ) OVER (PARTITION BY deptno ORDER BY sal DESC) 演示示例9： 分析函数

学员操作—使用分析函数进行查询 需求说明 实现步骤 公司需要查询每个部门薪水第二高的员工基本信息（包含并列第二） 练习 需求说明 公司需要查询每个部门薪水第二高的员工基本信息（包含并列第二） 实现步骤 使用分析函数将员工按照部门分组并且组内排序 在上面结果集中选择排序编号为2的员工信息 完成时间：15分钟

学员操作—SQL语言综合练习2-1 训练要点 需求说明 SELECT语句 WHERE 语句 GROUP BY语句 ORDER BY 语句 指导 训练要点 SELECT语句 WHERE 语句 GROUP BY语句 ORDER BY 语句 函数 需求说明 公司要获得员工相关信息 技术顾问讲解需求

学员操作—SQL语言综合练习2-2 实现思路 显示职员的就职年度 列出至少有一个雇员的所有部门 列出薪金比“SMITH”多的所有雇员 指导 实现思路 显示职员的就职年度 使用round()函数将入职日期四舍五入到年份 利用连接操作符将获得的年份和“年度”字符串做拼接 列出至少有一个雇员的所有部门 列出薪金比“SMITH”多的所有雇员 列出所有“CLERK”（办事员）的姓名及其部门名称 列出各种工作类别的最低薪金，显示最低薪金大于1500的记录 找出各月最后一天受雇的所有雇员 获得当月最后一天函数last_day(hiredate) 完成时间：20分钟

共性问题集中讲解 常见问题及解决办法 代码规范问题 调试技巧 共性问题集中讲解 教学指导： xxxxxxx

总结 Oracle数据库入门 安装数据库过程分为安装数据库软件和建库环节 在服务器端配置监听服务，客户端配置网络服务名 配置完后在服务器端启动数据库服务和监听服务 连接数据库 SQL*Plus PL/SQL Developer 教学指导； 总结部分主要达到以下几个目的： 1、回顾内容。注意与与本章任务和目标不一样。本章任务和目标是是强调内容概貌，学到技术，告知要学习什么；总结时，要格外强调观点，把每一个知识点的观点结论都尽量突出出来。 2、整理逻辑。还应该把观点之间的逻辑联系梳理出来。从而使知识系统化、逻辑化。要帮助学员整清逻辑是总结的一大任务。

总结 Oracle数据库入门 Oracle中常用数据类型 Oracle伪列包含 ROWNUM、ROWID 字符类型 数值类型 日期类型 LOB类型 Oracle伪列包含 ROWNUM、ROWID SQL语言分为DDL、DCL、TCL和DML SQL函数分为 单行函数 分组函数 分析函数 教学指导； 总结部分主要达到以下几个目的： 1、回顾内容。注意与与本章任务和目标不一样。本章任务和目标是是强调内容概貌，学到技术，告知要学习什么；总结时，要格外强调观点，把每一个知识点的观点结论都尽量突出出来。 2、整理逻辑。还应该把观点之间的逻辑联系梳理出来。从而使知识系统化、逻辑化。要帮助学员整清逻辑是总结的一大任务。

问题及作业 集中问题&课后作业

扫我有更多精彩课程呦