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 ...: 范围判断 (包含边界值)。
sql SELECT first_name, salary FROM employees WHERE salary BETWEEN 5000 AND 10000; IN (value1, value2, ...): 匹配列表中的任何一个值。
sql SELECT first_name, department_id FROM employees WHERE department_id IN (10, 20, 30);
LIKE: 模糊匹配字符串。 %: 匹配任意数量 (包括零个) 的字符。
_: 匹配任意单个字符。 ESCAPE 'char': 定义转义字符，用于匹配 % 或 _ 本身。

sql 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: 判断是否为空值。
sql SELECT first_name, commission_pct FROM employees WHERE commission_pct IS NULL;

代码案例：
查询薪水大于8000且部门ID为90的员工：
sql SELECT employee_id, first_name, salary, department_id FROM employees WHERE salary > 8000 AND department_id = 90;
查询部门ID为10或20，或者职位ID以 'SA_' 开头的员工：
sql 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。
```sql
-- 获取前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 rn
FROM (SELECT employee_id, first_name, salary
FROM employees
ORDER BY salary DESC)) -- 内层先排序
WHERE rn BETWEEN 6 AND 10;


*   <font color="teal">**`LEVEL`**</font>:
    *   与<font color="brown">层次查询 (Hierarchical Queries)</font> 一起使用 (`CONNECT BY` 子句)。
    *   表示<font color="darkgreen">当前行</font>在<font color="darkred">层次结构中</font>的<font color="navy">级别</font>。根节点为 `LEVEL 1`。
```sql
-- 假设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 语句中为主键列生成唯一值。
```sql
-- 假设存在一个名为 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; -- 查看当前会话中序列的当前值


### <font color="navy">**六、GROUP BY 子句：数据分组**</font>

`GROUP BY` 子句将<font color="blue">具有相同值</font>的行<font color="red">组织成</font>一个<font color="green">摘要组</font>。通常与<font color="orange">聚合函数</font> (如 `COUNT()`, `SUM()`, `AVG()`, `MAX()`, `MIN()`) 一起使用，对<font color="purple">每个组</font>进行计算。

<font color="teal">**聚合函数：**</font> (与之前版本相同)
*   `COUNT(*)`, `COUNT(column_name)`, `COUNT(DISTINCT column_name)`
*   `SUM(column_name)`, `AVG(column_name)`
*   `MAX(column_name)`, `MIN(column_name)`

<font color="brown">**使用规则：**</font>
*   `SELECT` 列表中<font color="darkgreen">所有未包含</font>在<font color="darkred">聚合函数中</font>的列，都<font color="navy">必须出现</font>在 `GROUP BY` 子句中。
*   `WHERE` 子句<font color="olive">先于</font> `GROUP BY` 执行；`HAVING` 子句<font color="darkcyan">后于</font> `GROUP BY` 执行。

<font color="saddlebrown">**代码案例：**</font>
查询每个部门的员工人数：
```sql
SELECT department_id, COUNT(*) AS num_employees
FROM employees
GROUP BY department_id;

七、HAVING 子句：分组过滤

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

代码案例：
查询平均薪水大于8000的部门：
sql 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

代码案例：
按薪水降序排列员工信息：
sql 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查询语句。

题目：

查询 products 表中所有产品的 ROWID 和 product_name。
查询 products 表中前5条记录的 product_id, product_name, unit_price (基于它们在表中的物理存储顺序，不指定特定排序)。
查询 products 表中按 unit_price 降序排列后的第3到第5条产品记录的 product_name 和 unit_price。
查询每个 category_id 下有多少种产品，并为每个类别结果行分配一个行号 (基于 category_id 的默认分组顺序)。
查询所有 category_id 为 20 (Condiments) 的产品名称和库存量 (units_in_stock)，并给 product_name 列起别名为 "调味品名称"，units_in_stock 列起别名为 "当前库存"。
查询单价 (unit_price) 大于等于20且小于50的所有产品信息 (使用 BETWEEN 或比较运算符均可)。
查询产品名称 (product_name) 以 "Chef Anton" 开头的所有产品ID和产品名称。
统计每个 supplier_id 供应的产品中，已停产 (discontinued = 'Y') 的产品数量。只显示供应了已停产产品的供应商ID及其对应的已停产产品数量。
查询所有产品信息，并按 category_id 升序排序，在同一类别中再按 units_in_stock 降序排序，并将库存量为 NULL 的产品排在最后。
(与序列相关，假设已创建序列 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和查询序列的语句)

答案与解析：

查询 ROWID 和 product_name：
```
SELECT ROWID, product_name FROM products;
```
- 解析： ROWID 是一个伪列，可以直接在 SELECT 列表中引用。
查询前5条记录 (基于物理顺序)：
```
SELECT product_id, product_name, unit_price FROM products WHERE ROWNUM <= 5;
```
- 解析： ROWNUM 在 WHERE 子句中用于限制返回的行数。此时的顺序是Oracle获取数据的自然顺序，不保证特定排序。
分页查询 (排序后取特定范围)：
```
SELECT product_name, unit_price
FROM (SELECT product_name, unit_price, ROWNUM AS rn
  FROM (SELECT product_name, unit_price
        FROM products
        ORDER BY unit_price DESC))
WHERE rn BETWEEN 3 AND 5;
```
- 解析： 这是Oracle分页的标准写法。最内层查询先按价格降序排序，中间层查询为排序后的结果分配 ROWNUM (并赋予别名 rn)，最外层查询根据 rn 筛选出第3到第5条记录。
分组并为组结果分配行号 (分析函数)：
(严格来说，为分组结果分配行号通常使用分析函数如 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;
```

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

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

范围查询 (多种写法)：
使用 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，则用第二种更精确。

模糊查询 (LIKE)：

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

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

分组统计已停产产品：

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 确保只显示那些确实有已停产产品的供应商。

多列排序与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的记录排在每个类别的最后。
使用序列插入并查询当前值：
(假设序列 product_pk_seq 已创建: CREATE SEQUENCE product_pk_seq START WITH 11 INCREMENT BY 1;)
```sql
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)。

Oracle 数据库查询：单表查询

思维导图

一、SELECT 语句基本结构

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

三、FROM 子句：指定表

四、WHERE 子句：行过滤

七、HAVING 子句：分组过滤

数据库

热门文章

最新文章

相关电子书