Oracle 数据库查询：单表查询

作者：IvanCodes
日期：2025年6月22日
专栏：Oracle教程

在 Oracle 数据库操作中，查询数据是最频繁、最核心的操作之一。单表查询，即仅从一个表中检索信息，是所有复杂查询的基础。本笔记将系统梳理单表查询的关键子句及其用法，并特别介绍Oracle中伪列的使用。

思维导图

一、SELECT 语句基本结构

一个完整的单表查询语句通常包含以下按执行顺序排列 (逻辑上) 的子句：

SELECT <select_list>                -- 5. 选择要显示的列或表达式
FROM <table_name>                  -- 1. 指定数据来源表
[WHERE <filter_conditions>]        -- 2. 行过滤条件
[GROUP BY <group_by_expression>]   -- 3. 分组依据
[HAVING <group_filter_conditions>] -- 4. 分组后的过滤条件
[ORDER BY <order_by_expression>];  -- 6. 结果排序

• FROM 子句：最先执行，确定查询的数据源表。
• WHERE 子句：其次执行，根据指定条件筛选满足要求的行。
• GROUP BY 子句：在 WHERE 过滤后执行，将符合条件的行按一个或多个列的值进行分组。
• HAVING 子句：在 GROUP BY 分组后执行，用于过滤分组后的结果集 (通常与聚合函数配合使用)。
• SELECT 子句：在上述操作完成后，选择最终要显示的列、表达式或聚合函数结果。
• ORDER BY 子句：最后执行，对最终结果集进行排序。

二、SELECT 子句：选择列与表达式

• 选择所有列: SELECT *

SELECT * FROM employees;

• 选择特定列: SELECT column1, column2, ...

SELECT employee_id, first_name, salary FROM employees;

• 使用列别名 (AS): 提高可读性或避免重名。

SELECT employee_id AS "员工编号", first_name "名", salary "月薪" FROM employees;
SELECT salary * 12 AS annual_salary FROM employees;

• 计算列/表达式: 可以在 SELECT 中进行算术运算、字符串拼接、函数调用等。

SELECT last_name || ', ' || first_name AS full_name, salary / 30 AS daily_rate FROM employees;
SELECT SYSDATE - hire_date AS days_employed FROM employees;
SELECT UPPER(first_name) AS upper_first_name FROM employees;

• 去除重复行 (DISTINCT): 只显示唯一的行组合。

SELECT DISTINCT department_id FROM employees;
SELECT DISTINCT department_id, job_id FROM employees;

• 常量值: 可以在查询结果中包含常量。

SELECT first_name, salary, 'Oracle Corp' AS company_name FROM employees;

三、FROM 子句：指定表

对于单表查询，FROM 子句非常简单，就是指定要查询的那个表名。

FROM employees;

可以为表指定别名，在单表查询中不常用，但在多表连接或子查询中非常有用。

FROM employees e;

四、WHERE 子句：行过滤

WHERE 子句用于根据指定的条件筛选出满足要求的行。

常用比较运算符：
= (等于), > (大于), < (小于), >= (大于等于), <= (小于等于), <> 或 != (不等于)。

逻辑运算符：
AND (与), OR (或), NOT (非)。

其他常用条件：

• BETWEEN ... AND ...: 范围判断 (包含边界值)。

SELECT first_name, salary FROM employees WHERE salary BETWEEN 5000 AND 10000;

• IN (value1, value2, ...): 匹配列表中的任何一个值。

SELECT first_name, department_id FROM employees WHERE department_id IN (10, 20, 30);

• LIKE: 模糊匹配字符串。
  • %: 匹配任意数量 (包括零个) 的字符。
  • _: 匹配任意单个字符。
  • ESCAPE 'char': 定义转义字符，用于匹配 % 或 _ 本身。

SELECT first_name FROM employees WHERE first_name LIKE 'A%';
SELECT last_name FROM employees WHERE last_name LIKE '_o%';
SELECT note FROM notes WHERE note LIKE '100\%%' ESCAPE '\';

• IS NULL / IS NOT NULL: 判断是否为空值。

SELECT first_name, commission_pct FROM employees WHERE commission_pct IS NULL;

代码案例：
查询薪水大于8000且部门ID为90的员工：

SELECT employee_id, first_name, salary, department_id
FROM employees
WHERE salary > 8000 AND department_id = 90;

查询部门ID为10或20，或者职位ID以 ‘SA_’ 开头的员工：

SELECT employee_id, department_id, job_id
FROM employees
WHERE department_id IN (10, 20) OR job_id LIKE 'SA\_%';

五、Oracle 伪列 (Pseudocolumns)

Oracle 提供了一些特殊的列，它们不实际存储在表中，但可以像普通列一样在SQL语句中引用。这些被称为伪列。

常用的伪列：

• ROWID:
  • 唯一标识数据库中每一行的物理地址。
  • 它是访问表中行的最快方式。
  • ROWID 的值看起来像一串十六进制字符。
  • 虽然唯一，但如果表发生重组或迁移，行的 ROWID 可能会改变。因此，不建议将其作为持久的行标识符。

SELECT ROWID, employee_id, first_name FROM employees WHERE ROWNUM <= 5;

• ROWNUM:
  • 对于查询返回的每一行，ROWNUM 会按顺序分配一个从1开始的数字。
  • ROWNUM 是在数据被检索出来之后，但在任何 ORDER BY 子句应用之前分配的。
  • 常用于限制查询结果的行数 (分页查询的基础)。
  • 重要：不能直接在 WHERE 子句中使用 ROWNUM > n (n>1) 来获取第n行之后的数据，因为 ROWNUM 是逐行分配的。如果第一行不满足 ROWNUM > 1，那么就没有第二行可以被分配 ROWNUM = 2。

-- 获取前5名员工 (基于默认顺序或ORDER BY之前的顺序)
SELECT employee_id, first_name, salary FROM employees WHERE ROWNUM <= 5;-- 错误的方式尝试获取第6到第10名员工
-- SELECT * FROM employees WHERE ROWNUM > 5 AND ROWNUM <= 10; (通常不会返回任何结果)-- 正确的分页方式 (使用子查询)
SELECT *
FROM (SELECT employee_id, first_name, salary, ROWNUM AS rnFROM (SELECT employee_id, first_name, salaryFROM employeesORDER BY salary DESC)) -- 内层先排序
WHERE rn BETWEEN 6 AND 10;

• LEVEL:
  • 与层次查询 (Hierarchical Queries) 一起使用 (CONNECT BY 子句)。
  • 表示当前行在层次结构中的级别。根节点为 LEVEL 1。

-- 假设employees表有 manager_id 列，形成层级关系
SELECT LEVEL, employee_id, first_name, manager_id
FROM employees
START WITH manager_id IS NULL -- 定义根节点
CONNECT BY PRIOR employee_id = manager_id; -- 定义父子关系

• NEXTVAL 和 CURRVAL (与序列 Sequence 相关):
  • sequence_name.NEXTVAL: 获取序列的下一个值。每次调用都会使序列递增。
  • sequence_name.CURRVAL: 获取序列的当前值 (必须在当前会话中至少调用过一次 NEXTVAL 之后才能使用)。
  • 常用于在 INSERT 语句中为主键列生成唯一值。

-- 假设存在一个名为 employee_seq 的序列
CREATE SEQUENCE employee_seq START WITH 200 INCREMENT BY 1;INSERT INTO employees (employee_id, first_name, last_name, email)
VALUES (employee_seq.NEXTVAL, 'New', 'Employee', 'new.emp@example.com');SELECT employee_seq.CURRVAL FROM dual; -- 查看当前会话中序列的当前值

六、GROUP BY 子句：数据分组

GROUP BY 子句将具有相同值的行组织成一个摘要组。通常与聚合函数 (如 COUNT(), SUM(), AVG(), MAX(), MIN()) 一起使用，对每个组进行计算。

聚合函数： (与之前版本相同)

• COUNT(*), COUNT(column_name), COUNT(DISTINCT column_name)
• SUM(column_name), AVG(column_name)
• MAX(column_name), MIN(column_name)

使用规则：

• SELECT 列表中所有未包含在聚合函数中的列，都必须出现在 GROUP BY 子句中。
• WHERE 子句先于 GROUP BY 执行；HAVING 子句后于 GROUP BY 执行。

代码案例：
查询每个部门的员工人数：

SELECT department_id, COUNT(*) AS num_employees
FROM employees
GROUP BY department_id;

七、HAVING 子句：分组过滤

HAVING 子句用于在数据分组后对分组结果进行进一步筛选。它通常包含聚合函数。

代码案例：
查询平均薪水大于8000的部门：

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 8000;

八、ORDER BY 子句：结果排序

ORDER BY 子句用于对最终查询结果集进行排序。它是查询语句中逻辑上最后执行的部分。

排序方式： (与之前版本相同)

• ASC (升序, 默认), DESC (降序)
• 多列排序, 列别名排序, 列序号排序 (不推荐)
• NULLS FIRST / NULLS LAST

代码案例：
按薪水降序排列员工信息：

SELECT employee_id, first_name, salary
FROM employees
ORDER BY salary DESC;

总结： 单表查询是 Oracle SQL 的基石。熟练掌握各子句的功能、用法、执行顺序，以及伪列 (特别是 ROWNUM 和 ROWID) 的特性，是编写高效、准确查询的关键。

---

练习题

背景表：
假设我们有一个 products 表，结构如下：

CREATE TABLE products (
product_id NUMBER PRIMARY KEY,
product_name VARCHAR2(100) NOT NULL,
category_id NUMBER,
supplier_id NUMBER,
unit_price NUMBER(10,2),
units_in_stock NUMBER,
discontinued CHAR(1) DEFAULT 'N' -- 'Y' or 'N'
);
-- 插入一些样例数据 (请自行补充更多数据以测试所有题目)
INSERT INTO products VALUES (1, 'Chai', 10, 1, 18.00, 39, 'N');
INSERT INTO products VALUES (2, 'Chang', 10, 1, 19.00, 17, 'N');
INSERT INTO products VALUES (3, 'Aniseed Syrup', 20, 1, 10.00, 13, 'N');
INSERT INTO products VALUES (4, 'Chef Anton''s Cajun Seasoning', 20, 2, 22.00, 53, 'N');
INSERT INTO products VALUES (5, 'Chef Anton''s Gumbo Mix', 20, 2, 21.35, 0, 'Y');
INSERT INTO products VALUES (6, 'Grandma''s Boysenberry Spread', 30, 3, 25.00, 120, 'N');
INSERT INTO products VALUES (7, 'Northwoods Cranberry Sauce', 20, 3, 40.00, 6, 'N');
INSERT INTO products VALUES (8, 'Mishi Kobe Niku', 40, 4, 97.00, 29, 'Y');
INSERT INTO products VALUES (9, 'Ikura', 40, 4, 31.00, 31, 'N');
INSERT INTO products VALUES (10, 'Queso Cabrales', 40, 5, 21.00, 22, 'N');
COMMIT;

假设 category_id 10=‘Beverages’, 20=‘Condiments’, 30=‘Confections’, 40=‘Dairy Products’。

请为以下每个场景编写相应的SQL查询语句。

题目：

1. 查询 products 表中所有产品的 ROWID 和 product_name。
2. 查询 products 表中前5条记录的 product_id, product_name, unit_price (基于它们在表中的物理存储顺序，不指定特定排序)。
3. 查询 products 表中按 unit_price 降序排列后的第3到第5条产品记录的 product_name 和 unit_price。
4. 查询每个 category_id 下有多少种产品，并为每个类别结果行分配一个行号 (基于 category_id 的默认分组顺序)。
5. 查询所有 category_id 为 20 (Condiments) 的产品名称和库存量 (units_in_stock)，并给 product_name 列起别名为 “调味品名称”，units_in_stock 列起别名为 “当前库存”。
6. 查询单价 (unit_price) 大于等于20且小于50的所有产品信息 (使用 BETWEEN 或比较运算符均可)。
7. 查询产品名称 (product_name) 以 “Chef Anton” 开头的所有产品ID和产品名称。
8. 统计每个 supplier_id 供应的产品中，已停产 (discontinued = ‘Y’) 的产品数量。只显示供应了已停产产品的供应商ID及其对应的已停产产品数量。
9. 查询所有产品信息，并按 category_id 升序排序，在同一类别中再按 units_in_stock 降序排序，并将库存量为 NULL 的产品排在最后。
10. (与序列相关，假设已创建序列 product_pk_seq) 使用序列 product_pk_seq.NEXTVAL 作为 product_id，插入一条新产品记录：product_name=‘New Test Product’, category_id=10, unit_price=15.00, units_in_stock=100。然后查询该序列的当前值。(只需写INSERT和查询序列的语句)

答案与解析：

1. 查询 ROWID 和 product_name：

SELECT ROWID, product_name FROM products;

• 解析： ROWID 是一个伪列，可以直接在 SELECT 列表中引用。

2. 查询前5条记录 (基于物理顺序)：

SELECT product_id, product_name, unit_price FROM products WHERE ROWNUM <= 5;

• 解析： ROWNUM 在 WHERE 子句中用于限制返回的行数。此时的顺序是Oracle获取数据的自然顺序，不保证特定排序。

3. 分页查询 (排序后取特定范围)：

SELECT product_name, unit_price
FROM (SELECT product_name, unit_price, ROWNUM AS rnFROM (SELECT product_name, unit_priceFROM productsORDER BY unit_price DESC))
WHERE rn BETWEEN 3 AND 5;

• 解析： 这是Oracle分页的标准写法。最内层查询先按价格降序排序，中间层查询为排序后的结果分配 ROWNUM (并赋予别名 rn)，最外层查询根据 rn 筛选出第3到第5条记录。

4. 分组并为组结果分配行号 (分析函数)：
   (严格来说，为分组结果分配行号通常使用分析函数如 ROW_NUMBER() OVER()，ROWNUM 在 GROUP BY 之后应用是对聚合后的结果行进行编号)
   如果题目意图是统计后给结果行编号：

SELECT category_id, COUNT(*) AS product_count, ROWNUM AS group_row_num
FROM products
GROUP BY category_id;

• 解析： 先按 category_id 分组并用 COUNT(*) 统计。然后对这个聚合后的结果集中的每一行分配 ROWNUM。
  如果意图是在每个组内部分配行号，则需要分析函数（超出单表查询基础范围，但可作了解）：

-- SELECT product_name, category_id, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY product_name) AS rn_in_category
-- FROM products;

5. 使用列别名并过滤 (同前)：

SELECT product_name AS "调味品名称", units_in_stock AS "当前库存"
FROM products
WHERE category_id = 20;

6. 范围查询 (多种写法)：
   使用 BETWEEN AND:

SELECT * FROM products WHERE unit_price BETWEEN 20 AND 49.99;

使用比较运算符:

SELECT * FROM products WHERE unit_price >= 20 AND unit_price < 50;

• 解析： BETWEEN 包含边界。如果题目是大于等于20且小于50，则用第二种更精确。

7. 模糊查询 (LIKE)：

SELECT product_id, product_name FROM products WHERE product_name LIKE 'Chef Anton%';

• 解析： LIKE 'Chef Anton%' 匹配以 “Chef Anton” 开头的所有字符串。

8. 分组统计已停产产品：

SELECT supplier_id, COUNT(*) AS discontinued_product_count
FROM products
WHERE discontinued = 'Y'
GROUP BY supplier_id
HAVING COUNT(*) > 0; -- 或者直接不加HAVING，如果没有已停产的供应商则不会显示

• 解析： 先用 WHERE 筛选出已停产产品，然后按 supplier_id 分组并用 COUNT(*) 统计。HAVING COUNT(*) > 0 确保只显示那些确实有已停产产品的供应商。

9. 多列排序与NULLS LAST：

SELECT *
FROM products
ORDER BY category_id ASC, units_in_stock DESC NULLS LAST;

• 解析： 先按 category_id 升序，再按 units_in_stock 降序，NULLS LAST 确保 units_in_stock 为NULL的记录排在每个类别的最后。

10. 使用序列插入并查询当前值：
    (假设序列 product_pk_seq 已创建: CREATE SEQUENCE product_pk_seq START WITH 11 INCREMENT BY 1;)

INSERT INTO products (product_id, product_name, category_id, unit_price, units_in_stock)
VALUES (product_pk_seq.NEXTVAL, 'New Test Product', 10, 15.00, 100);SELECT product_pk_seq.CURRVAL FROM dual;

• 解析： product_pk_seq.NEXTVAL 获取序列的下一个值并用于插入。product_pk_seq.CURRVAL 从 dual 表查询当前会话中该序列的当前值 (必须在同一会话中先调用过 NEXTVAL)。