1 第 5 章 SQL 语句基础 在 Oracle 数据库中，为了方便管理用户所创建的 数据库对象，引入了模式的概念，这样用户所创建的 数据库对象就都属于该用户模式。对于一般的用户而 言，数据库中的数据是以表、视图行等方式存储的（ 表和视图就是最基本的用户模式对象），用户只需要 根据自己的需求查询数据库，然后由数据库根据请求.

Presentation on theme: "1 第 5 章 SQL 语句基础 在 Oracle 数据库中，为了方便管理用户所创建的 数据库对象，引入了模式的概念，这样用户所创建的 数据库对象就都属于该用户模式。对于一般的用户而 言，数据库中的数据是以表、视图行等方式存储的（ 表和视图就是最基本的用户模式对象），用户只需要 根据自己的需求查询数据库，然后由数据库根据请求."— Presentation transcript:

1 1 第 5 章 SQL 语句基础 在 Oracle 数据库中，为了方便管理用户所创建的 数据库对象，引入了模式的概念，这样用户所创建的 数据库对象就都属于该用户模式。对于一般的用户而 言，数据库中的数据是以表、视图行等方式存储的（ 表和视图就是最基本的用户模式对象），用户只需要 根据自己的需求查询数据库，然后由数据库根据请求 执行相关的处理，并且将处理结果返回给特定的用户 。另外，用户还可以向数据库中添加数据、删除特定 数据。在 Oracle 中，对这些用户模式对象的操作都是 通过 SQL 语句来完成的。

2 2 本章知识要点： Oracle 示例中的用户模式 使用 SELECT 语句检索数据 检索多个表中的数据 在 Oracle 中各种函数的使用 正规表达式的使用 分组查询 插入、更新和删除数据 控制 Oracle 中的事务处理

3 3 5.1 使用的用户模式 为了更好理解 Oracle 的各种具体操作，下面介绍 一下 Oracle 自带的示例模式（也称示例方案）。这些 模式在安装数据库时由用户选择自动安装，所有这些 模式一起形成了相同的虚拟公司的一部分，它们各自 都有自己的侧重点。

4 5.1.1 SCOTT 模式 该模式演示了一个很简单的公司人力资源管理， 它也是 Oracle 早期版本中最经常使用的示例模式，该 用户模式的连接密码为 TIGER 。通过连接到 SCOTT 用 户模式，查询数据字典视图 USER_TABLES 可以获知 该模式所包含的表。 4

5 5.1.2 HR 模式 HR 类似于 SCOTT 模式，也是一个基本的关系数据 库的示例模式，其中有部门和员工数据表。在 HR 模式 中有 7 个表：雇员、部门、地点、国家、地区、工作和 工作历。 5

6 6 5.1.3 其他模式 除了上面介绍的两种常用模式外， Oracle 附带的 示例方案中还包括：订单目录（ OE ）模式、产品媒 体（ PM ）模式、信息交换（ IX ）模式和销售记录（ SH ）模式。

7 7 5.2 SELECT 语句的用法 用户对表或视图的操作是通过 SQL 语句来实现的 ， SQL 语句是一种标准的结构化查询语言。在众多的 SQL 语句中，使用频率最高的是 SELECT 语句，该语 句主用于检索数据。

8 5.2.1 检索单表数据 检索单表数据是指从单个表中检索数据，检索的结 果都是来自于同一个表中，检索单表数据是检索数据的 最基础的操作。 8

9 5.2.2 过滤数据 在 SELECT 语句中可以使用 WHERE 子句过滤数据 ，只检索那些满足过滤条件的数据。当表中的数据非 常大时，这种过滤操作是非常有意义的。通过过滤数 据，可以从大量的数据中获取自己所需要的数据。 9

10 5.2.3 排序数据 在前面介绍的数据检索技术中，只是把数据库中的 数据直接取出来。这时，结果集中数据的排列顺序是由 数据的物理存储顺序所决定的。这种存储顺序是比较混 乱的，并且可以不符合用户的各种业务需求，因此需要 对检索到的结果集进行排序。在 SELECT 语句中，可以 使用 ORDER BY 子句对检索的结果集进行排序。 10

11 5.2.4 多表检索 在实际应用中，经常会碰到需要检索的数据存在 于两个或两个以上的表中。这时就需要使用 SELECT 语句执行多表检索。多表检索操作比单表检索复杂得 多。为了更好地理解多表检索操作，需要理解表的别 名，笛卡尔积、内连接、外连接、自然连接和交叉连 接等概念。 11

12 5.3 函数的使用 与其他编程语言一样， SQL 提供了许多内置的函 数，使用这些函数可以大大提高这些计算机语言的运 算、判断功能。例如，使用字符串函数可以对字符串 进行处理、使用数学函数进行数值运算、使用转换函 数对数据类型进行转换、使用日期函数处理日期和时 间等。 12

13 5.3.1 字符函数 字符函数用指用于对字符表达式进行处理的函数 ，它也是 Oracle 系统中广泛使用的函数。在使用字符 串函数时，其输入值一般是字符数据类型，而其输出 结果则是经过处理的字符表达式。 13

14 5.3.2 数字函数 数学函数可以用于执行各种数据计算。在其他编 程语言中提供了大量的数学函数，这也是编程语言最 早的功能之一。在 Oracle 系统中，也提供了大量的数 学函数，这些函数大大增强了 Oracle 系统的科学计算 能力。 14 函数说明 ABS(X) 返回 X 的绝对值 CEIL(X) 返回大于等于数值 X 的最小整数 COSH(X) 返回数值 X 的双曲余弦值 EXP(X) 返回 e 的 X 次冥。（ e=2.71828183… ） FLOOR(X) 返回小于等于数值 X 的最大整数 LN(X) 返回数值 X 的自然对数。（ X 必须大于 0 ） LOG(X,Y) 返回以 X 为底的数值 Y 的对数。（ X>1,Y>0 ） MOD(X,Y) 返回 X/Y 后的余数，若 Y=0 ，则返回 X （求模运算） POWER(X,Y) 返回 X 的 Y 次冥 ROUND(X,[ Y]) 执行四舍五入运算， Y 可以省略，当省略 Y 时，四舍五入到整数位；当 X 为正数 时，四舍五入到小数点后 Y 位；当 X 为负数时，四舍五入到小数点前 Y 位 SIGN(X) 检测数值的正负，当 X 0 则返回 1 ，当 X=0 返回 0 SQRT(X) 返回数值 X 的平方根。 (X>=0) TRUNC(X,[Y ]) 截取数值 X ， Y 可以省略，当省略 Y 时则截取 X 的小数部分；当 Y 为正数时则将 X 截取到小数点后 Y 位；当 X 为负数时则将 X 截取到小数点前 Y 位

15 5.3.3 时间和日期函数 在默认情况下，日期数据的格式是 DD-MON-YY 。其中， DD 表示两位数字的日， MON 表示 3 位数的月 份， YY 表示两位数字的年。在插入数据时，默认也采 用 DD-MON-YY 格式插入数据。 15 函数说明 add_months(x,y) 在 X 给定的日期上增加 Y 个月。如果 Y 为负数，则表示从 X 中减去 Y 个月 last_day(x) 返回包含在 X 中的月份的最后一天 months_between(x,y) 返回 X 和 Y 之间的月数 next_day(x,day) 返回紧接着 X 的下一天，参数 DAY 是一个字符串 sysdate() 返回当前系统的日期 current_date() 返回本地时区的当前日期 new_time(x,time_zone1,time_zone 2) 将时间 X 从时区 TIME_ZONE1 转变成时区 TIME_ZONE2 localtimestamp() 返回会话中的日期和时间

16 5.3.4 转换函数 在操作表中的数据时，经常需要将某个数据从一种 数据类型转变为另外一种数据类型。这时就需要使用数 据转换函数。例如，如果希望把表示价格的数字数据转 变为字符数据，就需要使用 TO_CHAR() 函数。通常这 类函数遵循如下惯例：函数名称后面跟着待转换类型以 及输出类型。 16

17 5.3.5 统计函数 使用统计函数可以针对一组数据进行计算，并得到 相应的结果。使用这些统计函数，可以计算表中数据列 的平均值、最大值和最小值等数据。 17 函数说明函数说明 AVG(X) 平均值 MIN(X) 最小值 COUNT(X) 统计数量 STDDEV(X) 标准差 MAX(X) 最大值 SUM(X) 汇总值 MEDIAN(X) 中位数 VARIANCE(X) 方差

18 18 5.3.6 分组技术 在 SELECT 语句中，可以使用 GROUP BY 子句进 行分组操作，并可以使用 HAVING 子句提供分组条件。 在下面的查询中，将对 SCOTT 模式中的 EMP 表进 行分组，以统计各部门的员工人数： SQL> select deptno,count(*) as 员工数量 2 from emp 3 group by deptno; DEPTNO 员工数量 ---------- ---------- 30 6 20 5 10 3

19 5.4 子查询 在执行数据操纵的过程中，如果某个操作需要依赖 于另外一个 SELECT 语句的结果，那么可以把 SELECT 语句嵌入到该操纵语句中，这就形成了一个子查询。实 际上，在操作表中的数据时，数据并不是孤立的，而是 互相关联的。这样就可以根据数据之间的关联使用相应 的子查询，从而实现复杂的查询。 19

20 5.4.1 子查询的概念 在一个 SELECT 语句被嵌套在另外一个 SELECT 、 UPDATE 或 DELETE 等 SQL 语句中时，被嵌套的 SELECT 语句就是子查询。使用子查询的原因是，如 果希望执行某 SQL 语句，但是该 SQL 语句还需要依赖 于另外一个 SELECT 语句的执行结果。 20

21 5.4.2 单行子查询 在单行子查询中，该内查询只返回单行单列值，因 此可以把这种子查询作为一个学量。在 WHERE 子句中 ，可以使用单行比较运算符来比较某个表达式与子查询 的结果。可以使用的单行比较运算符包括：等于 “=” 、大 于 “>” 、大于或等于 “>=” 、小于 “ /!=” 。 21

22 5.4.3 多行子查询 多行子查询可以返回单列多行的数据。在这种多 行子查询中，必须使用多行运算符来判断，而不能使 用单行运算符。使用多行运算符可以执行与一个或多 个数据的比较操作。在 Oracle 系统中，可以使用的多 行比较运算符包括： IN （等于列表中的任何一值）、 ANY （与子查询返回的每一个值进行比较）和 ALL （ 与子查询返回的所有值进行比较）。 22

23 5.4.4 关联子查询 在前面介绍的子查询中，内查询和外查询是分开 执行的，即内查询的执行与外查询的执行是没有关系 的，而外查询仅仅使用内查询的最终结果。如果在子 查询语句中，内查询的执行需要借助于外查询，而外 查询的执行又离不开内查询的执行。这时，内查询和 外查询是相互关联的，这种子查询称为关联子查询。 23

24 5.5 操作数据 SQL 语句除可以查询数据外，还可以对完成插入 、更新和删除数据等操作。在 Oracle 11g 中，在创建 表后，只有在表中插入数据之后，该表的才有意义。 如果表中的数据不合适，还可以对那些不合适的数据 进行更新。如果某些数据已经不再需要，则可以删除 这些数据。在操作数据的过程中， Oracle 是通过种事 务来进行管理。 24

25 5.5.1 插入数据 插入数据就是将数据放置到已经创建的表中， Oracle 数据库是通过 INSERT 语句来实现插入数据的。一般情况 下，使用 INSERT 语句可以一次插入一行数据。 25

26 5.5.2 更新数据 在更新数据时，即可以一次更新一列，也可以一 次更新多列。如果在 UPDATE 语句中使用了 WHERE 条件表达式，那么只有符合条件的记录才会被更新； 如果没有使用 WHERE 条件表达式，那么表示更新表 中所有行的数据。 26

27 5.5.3 删除数据 在删除操作中，即可以一次删除一行数据，也可 以一次删除多行数据，更可以删除表中的所有数据。 在 DELETE 语句中，如果没有使用 WHERE 条件表达 式，那么将会删除表中的所有数据。例如，下面语句 将删除 EMP 表中的 ENAME 列为 ATG 的记录行： 27 SQL> delete from emp 2 where ename='ATG'; 已删除 1 行。

28 5.6 Oracle 中事务处理 在 Oracle 系统中，在使用 INSERT 、 UPDATE 和 DELETE 语句操作数据时，数据库中的数据并不会立 即改变，用户还可以通过控制事务确认是否提交或取 消先前的操作。 28

29 5.6.1 事务的基本概念 事务数据库系统工作的一个逻辑单元，它由一个 或多个 SQL 语句组成。对于数据库而言，事务是不可 分割的工作单元，一个事务中的所有 SQL 语句要么全 部执行，要么全部不执行。也就是说，当事务被提交 后，该事务的操作才直正被保存到数据库中。如果某 个事务被回退了，那么该事务的所有操作都被取消。 事务的回退和提交可以由用户显式执行，也可以隐含 地执行。 29

30 5.6.2 事务控制 Oracle 中的事务是隐式自动开始，它不需要用户显式 地使用语句开始事务处理。当发生如下情况时， Oracle 认 为一个事务结束： 执行 COMMIT 语句提交事务。 执行 ROLLBACK 语句撤销事务。 执行一条数据定义语句（例如 CREAE 、 DROP 或 ALTER 语 句等）。如果该语句执行成功，那么表示系统自动执行 COMMIT 命令；如果这种操作失败，那么表示系统自动执 行 ROLLBACK 命令。 执行一个数据控制命令（例如 GRANT 、 REVOKE 等），这 种操作表示自动执行 COMMIT 命令。 断开数据库的连接。如果使用 EXIT 命令正常退出 SQL*Plus ，则系统自动执行 COMMIT 命令；如果退出 SQL*Plus 出现 异常，则系统自动执行 ROLLBACK 命令。 30