Oracle基础第六章 查询

本章目标 单表查询 算术运算 NULL 比较运算符 逻辑运算符 ORDER BY 子句 使用 SQL 运算符 多表查询 联结条件和联结类型 联结查询使用 SQL/92 语法 行的标识符 1. 行的标识符 a) 在 Oracle 数据库中的每一行都有一个唯一的行标识符，它用于 Oracle 数据库内部

本章目标

单表查询

算术运算

NULL值

比较运算符

逻辑运算符

ORDER BY子句

使用SQL运算符

多表查询

联结条件和联结类型

联结查询使用SQL/92语法

行的标识符

1. 行的标识符

a) 在Oracle数据库中的每一行都有一个唯一的行标识符，它用于Oracle数据库内部存储行的物理位置。

b) 例如，查看customer表的信息

SELECT ROWID, customer_id

FROM customers;

c) 查询效果如下图所示：

d) 需要注意的是ROWID并不出现在表的定义中

e) 例如，可以使用DESCRIBE命令查看customer表的信息

DESCRIBE customers

f) 执行效果如下图所示：

2. ROWNUM

a) ROWNUM可以返回结果集的行号，查询返回的第一行编号为1，第二行编号为2，依此类推

b) 例如，查询customer表的相关信息

SELECT ROWNUM, customer_id, first_name, last_name

FROM customers;

c) 查询效果如下图所示：

d) 可以将ROWNUM使用在WHERE子句中

e) 例如

SELECT ROWNUM, customer_id, first_name, last_name

FROM customers

WHERE customer_id = 3;

f) 查询效果如下图所示：

3. 数据完整性-2

a) 实体完整性

b) 域完整性

c) 引用完整性

d) 自定义完整性

4. 实体完整性

a) 实体完整性

实体完整性要求每一个表中的主键字段都不能为空或者重复的值。

b) 实现方法

i. 唯一约束

ii. 主键约束

5. 域完整性

a) 域完整性

域完整性指列的值域的完整性。如数据类型、格式、值域范围、是否允许空值等

b) 实现方法

i. 限制数据类型

ii. 外键约束

iii. 默认值

iv. 非空约束

6. 引用完整性

a) 引用完整性

i. 也称之为参照完整性，当更新、删除、插入一个表中的数据时，通过参照引用相互关联的另一个表中的数据，来检查对表的数据操作是否正确。

ii. 引用完整性要求关系中不允许引用不存在的实体。

iii. 引用完整性与实体完整性是关系模型必须满足的完整性约束条件。

b) 实现方法

外键约束

7. 自定义完整性

a) 自定义完整性

用户自定义完整性指针对某一具体关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。

b) 实现方法

i. 存储过程

ii. 触发器

8. 选择主键的原则

a) 最少性

尽量选择单个键作为主键

b) 稳定性

尽量选择数值更新少的列作为主键

9. 主外键关系

a) 注意事项

i. 当主表中没有对应的记录时，不能将记录添加到子表

ii. 不能更改主表中的值而导致子表中的记录孤立

iii. 子表存在与主表对应的记录，不能从主表中删除该记录

iv. 删除主表前，先删子表

10. 表约束

a) 表约束的目的：确保表中数据的完整性

b) 常用的约束类型：

i. 主键约束(PRIMARY KEY)：要求主键列数据唯一，并且不允许为空

ii. 非空约束(NOT NULL)：指定的列的值不允许为空

iii. 唯一键约束(UNIQUE)：要求该列唯一，允许为空，但只能出现一个空值

iv. 检查约束(CHECK)：指定表中一列或多列可以接受的数据值格式

v. 默认约束(DEFAULT)：指定某列的默认值

vi. 外键约束(FOREIGN KEY)：用于建立和加强两个表数据之间连接的一列或多列。通过将表中的主键列添加到另一个表中。可以创建两个表之间的连接。这个主键的列就称为第二个表的外键。外键约束就可以确保添加到外键表中的任何行都在主表中都存在相应的行

日期计算

1. 日期计算

a) 可以在日期上使用加法或减法运算符，加减操作的数字表示天数

b) 例如，在日期上加3天

SELECT TO_DATE('16-5月-2012')+3

FROM dual;

c) 在某日期上减3天

SELECT TO_DATE('16-5月-2012')-3

FROM dual;

d) 执行效果如右图所示：

dual表

1. dual表

a) dual表常用在没有查询目标的SELECT语句块中，它只包含一行数据

b) 可以使用DESCRIBE查看dual表结构

DESCRIBE dual

c) 查询dual表的数据

SELECT *

FROM dual;

d) 执行效果如下图所示：

2. 列的别名

a) 通常情况，当数据行从表中查询出来时，Oracle会将列名转换为大写，并显示在数据上方

b) 如果查询语句中有表达式时，表达式的名称将会显示在列位值，可以为其提供别名；别名可以使用双引号，也可以在别名前加AS关键字

c) 例如

SELECT price * 2 AS "DOUBLE_PRICE"

FROM products;

d) 查询效果如下图所示：

单表查询

1. 连接

a) 可以将一些列连接起来显示，连接使用||运算符

b) 例如，查询customer表的first_name列和last_name列

c) SELECT first_name || ' ' || last_name AS "Customer Name"

d) FROM customers;

e) 查询效果如下图所示：

2. NULL值

a) NULL值表示未知的值。它是一个特殊的值，但并不是空字符串，NULL值表示该列是未知的。

b) 当某些查询语句在输出结果列上看不到值的时候，可能就是NULL值

c) 例如

SELECT customer_id, first_name, last_name, dob

FROM customers

WHERE dob IS NULL;

d) 查询效果如下图所示：

3. 处理NULL值

a) NULL值被查询出来的时候没有显示信息，如何告知用户这是空字符串还是NULL，这可以通过NVL()函数来进行处理

b) 例如

SELECT customer_id, first_name, last_name,

NVL(phone, 'Unknown phone number') AS PHONE_NUMBER

FROM customers;

c) 查询效果如下图所示：

4. DISTINCT关键字

a) 在SELECT语句中，可以使用DISTINCT关键字删除重复的行

b) 例如，查询purchases表中，删除重复的customer_id值

SELECT DISTINCT customer_id

FROM purchases;

c) 查询结果如下图所示：

5. 比较运算符

a) 比较运算符

i. =：等于

ii. 或!=：不等于

iii. ：小于

iv. >：大于

v. ：小于或等于

vi. >=：大于或等于

vii. ANY：比较值列表中的任何一个值

viii. ALL：比较列表值中的所有值

b) 运算符

i. 例如，查询customer_id不等于2的客户信息

SELECT *

FROM customers

WHERE customer_id 2;

ii. 查询效果如下图所示：

c) >运算符

i. 例如，查询商品编号大于8的商品

SELECT product_id, name

FROM products

WHERE product_id > 8;

ii. 查询效果如下图所示：

d) 运算符

i. 例如，查询ROWNUM伪列小于等于3，即查询前3件商品信息

SELECT ROWNUM, product_id, name

FROM products

WHERE ROWNUM

ii. 查询效果如下图所示：

e) ANY运算符

i. 在WHERE子句中使用ANY运算符与列表中的任意值进行比较

ii. 必须在使用ANY运算符之前使用= , , , 或 >=运算符

iii. 例如，查询customer表，customer_id值只要大于列表(2，3，4)中的任意一个值即满足条件

SELECT *

FROM customers

WHERE customer_id > ANY (2, 3, 4);

iv. 查询效果如下图所示：

f) ALL运算符

i. 在WHERE子句中使用ALL运算符与值列表中的所有值进行比较

ii. 例如，查询customer表

SELECT *

FROM customers

WHERE customer_id > ALL (2, 3, 4);

iii. 查询效果如下图所示：

6. SQL运算符

a) SQL运算符可以进行模式匹配，像字符串、列表值、NULL值。

i. LIKE：匹配字符串

ii. IN：匹配列表值

iii. BETWEEN AND：匹配范围值

iv. IS NULL：匹配空值

v. IS NAN：匹配非数字值

b) 同样可以在上述运算符前加NOT取反

i. 如：NOT LIKE ,NOT BETWEEN,IS NOT NULL,IS NOT NAN

c) LIKE运算符

i. 可以在WHERE子句中使用LIKE运算符进行字符串的匹配

ii. 可以使用普通字符及通配符进行组合，通配符如下：

iii. 下划线(_)：表示匹配某个位置的字符

iv. 百分号(%)：表示在某个位置的任意个字符

v. 例如，匹配’_o%’，表示第一个字母任意，第二个是o，%表示最后可以是任意个字母

vi. SELECT *

vii. FROM customers

viii. WHERE first_name LIKE '_o%';

ix. 查询效果如下图所示：

d) 转义字符

i. 如果要查询实际的下划线或百分号就需要使用ESCAPE选项区分通配符

ii. ESCAPE选项告知数据库如何区分通配符和要匹配的字符，可以使用反斜杠(\)，例如，

SELECT name

FROM promotions

WHERE name LIKE '%\%%' ESCAPE '\';

iii. 查询效果如下图所示：

e) IN运算符

i. 在WHERE子句中可以使用IN筛选某列的值是否在列表中

ii. 例如，查询customers表，检查customer_id是否在2、3、5中

SELECT *

FROM customers

WHERE customer_id IN (2, 3, 5);

iii. 查询效果如下图所示：

f) BETWEEN运算符

i. 在WHERE子句中可以使用BETWEEN检查某列的值是否在一个指定的范围内。包含区间两端的值。

ii. 例如，查询customers表customer_id在1到3之间的客户

SELECT *

FROM customers

WHERE customer_id BETWEEN 1 AND 3;

iii. 查询效果如下图所示：

7. 逻辑运算符

a) 逻辑运算符允许根据逻辑条件限制行

i. x AND y

ii. x和y都必须是真时，返回真

iii. x OR y

iv. x或y为真时，返回真

v. NOT x

vi. x为假时，返回真

b) AND运算符

i. 例如

ii. 查询customer表中dob列大于’01-1月-1970’年，并且customer_id大于3的客户信息

SELECT *

FROM customers

WHERE dob > '01-1月-1970'

AND customer_id > 3;

iii. 查询效果如下图所示：

c) OR运算符

i. 例如

ii. 查询customer表中dob列大于’01-1月-1970’年，或者customer_id大于3的客户信息

SELECT *

FROM customers

WHERE dob > '01-1月-1970'

OR customer_id > 3;

iii. 查询效果如下图所示：

8. ORDER BY

a) 可以使用ORDER BY对查询的结果进行排序，可以对一列或多列进行排序，ORDER BY必须在FROM子句后或WHERE子句后(如果有WHERE子句)

b) 例如，查询customer表，并对last_name列进行排序

SELECT *

FROM customers

ORDER BY last_name;

c) 查询结果如下所示：

d) 默认情况下，ORDER BY是按照升序(ASC)排序，也可以使用DESC指定为降序排序

e) 例如，查询customers表，先按first_name升序排列，再按last_name降序排列

SELECT *

FROM customers

ORDER BY first_name ASC, last_name DESC;

f) 查询结果如下所示：

g) 同样可以是列的位置编号，指定哪列需要进行排序

h) 例如，查询customer表，要对查询的第2列first_name列进行降序排列

SELECT customer_id, first_name, last_name

FROM customers

ORDER BY 2 DESC;

i) 查询结果如下所示：

多表查询

1. 多表查询

a) 不同的数据存储在不同的表中，通常要查询多张表才能找到需要的数据

i. 例如，要查询product_id为3的商品类型，需要两步骤

ii. 从products表查询出product_id为3的商品的product_type_id类型

iii. 根据上一步骤查询出的product_type_id查询出product_types

iv. 查询顺序如图所示：

b) 联结两张表的查询可以在FROM后面加两张表，然后再指定两张表的关联关系

i. 首先指定查询的两张表，如下

ii. FROM products, product_types

iii. 再通过WHERE条件指定两表的关系

iv. WHERE products.product_type_id = product_types.product_type_id

v. AND products.product_id = 3;

vi. 说明

vii. 通常情况下，是一个表的主键与另外一个表的外键相匹配；再指定产品的编号为3，即products.product_id = 3

c) 两表查询语句如下

SELECT products.name, product_types.name

FROM products, product_types

WHERE products.product_type_id = product_types.product_type_id

AND products.product_id = 3;

查询结果如下图所示：

2. 表的别名

a) products表和product_types表相关字段会用在SELECT语句及WHERE子句上，可以给表起别名，提高代码可读性、降低书写难度

b) 例如，改写查询语句

SELECT p.name, pt.name

FROM products p, product_types pt

WHERE p.product_type_id = pt.product_type_id

AND p.product_id = 3

ORDER BY p.name;

c) 查询效果如下图所示：

3. 笛卡尔乘积

a) 如果查询两张表，并且没有定义联结条件，那么查询的结果集是两表相乘的结果，这样的情况称之为笛卡尔乘积

b) 笛卡尔乘积会产生很多行结果。比如，第一张表的行数为50行，第二张表为100行，那么笛卡尔乘积产生的结果为5000行。因为第一张表的每条记录会对应第二张表的100条记录，即50*100=5000

c) 例如

SELECT pt.product_type_id, p.product_id

FROM product_types pt, products p;

d) 查询效果如下图所示：

4. 多表查询

a) 多表查询，需要查询的信息包含

i. 购买商品信息（在purchases中）

ii. 顾客姓名信息（在customers表中）

iii. 购买的商品名称（在products表中）

iv. 购买的商品类型（在product_types表中）

b) 查询语句如下

SELECT c.first_name, c.last_name, p.name AS PRODUCT, pt.name AS TYPE

FROM customers c, purchases pr, products p, product_types pt

WHERE c.customer_id = pr.customer_id

AND p.product_id = pr.product_id

AND p.product_type_id = pt.product_type_id

ORDER BY p.name;

c) 总结

i. 多表查询WHERE时，联结次数=查询时联结表的数量-1

联结条件和联结类型

1. 联结条件和联结类型

a) 常见的有两类联结条件

i. 等值联结：一般使用等于号（=）操作符

ii. 非等值联结：使用除了等于以外的运算符，例如，、>、BETWEEN等

b) 常见的三种联结类型

i. 内联结

1. 内联结返回的行只有满足联结条件才会返回。如果联结条件的列中有NULL值，那么该行则不会返回

ii. 外联结

1. 外联结返回的行满足联结条件，也包括在联结条件的列包含空值的行

iii. 自联结

1. 联结的表为同一张表

c) 非等值联结

i. 非等值联结一般使用、>、、、>=、LIKE、IN、BETWEEN等运算符

ii. 非等值联结一般使用比较少

iii. 例如，现有工资等级表(salary_grades)

1. 数据如下：

2. 现需要查询出员工的工资等级

iv. 要查询出员工工资等级需要使用非等值联结，使用BETWEEN运算符

1. SELECT e.first_name, e.last_name, e.title, e.salary, sg.salary_grade_id

2. FROM employees e, salary_grades sg

3. WHERE e.salary BETWEEN sg.low_salary AND sg.high_salary

4. ORDER BY salary_grade_id;

v. 查询结果如下图所示：

2. 外联结

a) 外联结可以查询出即使列中包含NULL值的数据也可以查询出来

b) 外部联结运算符 (+)。在使用(+)的字段，可能包含NULL值

c) 例如，

i. SELECT p.name, pt.name

ii. FROM products p, product_types pt

iii. WHERE p.product_type_id = pt.product_type_id (+)

iv. ORDER BY p.name;

d) 查询结果如右图所示：

3. 外联结分类

a) 外联结可以分为

i. 左外联结

ii. 右外联结

b) 左外联结右外联结

i. 现有有以下查询语句

1. SELECT ...

2. FROM table1, table2

3. ...

4. 其中table1.column1和table2.column2为联结条件

ii. 假设1，table1表包含了某行的column1列的值为NULL

1. 左外联结的WHERE条件写作

2. WHERE table1.column1 = table2.column2 (+);

iii. 假设2，table2表包含了某行的column2列的值为NULL

1. 右外联结的WHERE条件写作

2. WHERE table1.column1 (+) = table2.column2;

c) 左外联结

i. 例如，有查询语句如下