前言

在 Oracle 數(shù)據(jù)庫操作中，查詢數(shù)據(jù)是最頻繁、最核心的操作之一。單表查詢，即僅從一個(gè)表中檢索信息，是所有復(fù)雜查詢的基礎(chǔ)。本筆記將系統(tǒng)梳理單表查詢的關(guān)鍵子句及其用法，并特別介紹Oracle中偽列的使用。

思維導(dǎo)圖

一、SELECT 語句基本結(jié)構(gòu)

一個(gè)完整的單表查詢語句通常包含以下按執(zhí)行順序排列 (邏輯上) 的子句：

SELECT <select_list>                -- 5. 選擇要顯示的列或表達(dá)式
FROM <table_name>                  -- 1. 指定數(shù)據(jù)來源表
[WHERE <filter_conditions>]        -- 2. 行過濾條件
[GROUP BY <group_by_expression>]   -- 3. 分組依據(jù)
[HAVING <group_filter_conditions>] -- 4. 分組后的過濾條件
[ORDER BY <order_by_expression>];  -- 6. 結(jié)果排序

• FROM 子句：最先執(zhí)行，確定查詢的數(shù)據(jù)源表。
• WHERE 子句：其次執(zhí)行，根據(jù)指定條件篩選滿足要求的行。
• GROUP BY 子句：在 WHERE 過濾后執(zhí)行，將符合條件的行按一個(gè)或多個(gè)列的值進(jìn)行分組。
• HAVING 子句：在 GROUP BY 分組后執(zhí)行，用于過濾分組后的結(jié)果集 (通常與聚合函數(shù)配合使用)。
• SELECT 子句：在上述操作完成后，選擇最終要顯示的列、表達(dá)式或聚合函數(shù)結(jié)果。
• ORDER BY 子句：最后執(zhí)行，對(duì)最終結(jié)果集進(jìn)行排序。

二、SELECT 子句：選擇列與表達(dá)式

• 選擇所有列: SELECT *

SELECT * FROM employees;

• 選擇特定列: SELECT column1, column2, ...

SELECT employee_id, first_name, salary FROM employees;

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

SELECT employee_id AS "員工編號(hào)", first_name "名", salary "月薪" FROM employees;
SELECT salary * 12 AS annual_salary FROM employees;

• 計(jì)算列/表達(dá)式: 可以在 SELECT 中進(jìn)行算術(shù)運(yùn)算、字符串拼接、函數(shù)調(diào)用等。

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;

• 去除重復(fù)行 (DISTINCT): 只顯示唯一的行組合。

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

• 常量值: 可以在查詢結(jié)果中包含常量。

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

三、FROM 子句：指定表

對(duì)于單表查詢，FROM 子句非常簡單，就是指定要查詢的那個(gè)表名。

FROM employees;

可以為表指定別名，在單表查詢中不常用，但在多表連接或子查詢中非常有用。

FROM employees e;

四、WHERE 子句：行過濾

WHERE 子句用于根據(jù)指定的條件篩選出滿足要求的行。

常用比較運(yùn)算符：= (等于), > (大于), < (小于), >= (大于等于), <= (小于等于), <> 或 != (不等于)。

邏輯運(yùn)算符：AND (與), OR (或), NOT (非)。

其他常用條件：

• BETWEEN ... AND ...: 范圍判斷 (包含邊界值)。

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

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

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

• LIKE: 模糊匹配字符串。
  • %: 匹配任意數(shù)量 (包括零個(gè)) 的字符。
  • _: 匹配任意單個(gè)字符。
  • ESCAPE 'char': 定義轉(zhuǎn)義字符，用于匹配 % 或 _ 本身。

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 提供了一些特殊的列，它們不實(shí)際存儲(chǔ)在表中，但可以像普通列一樣在SQL語句中引用。這些被稱為偽列。

常用的偽列：

• ROWID:
  • 唯一標(biāo)識(shí)數(shù)據(jù)庫中每一行的物理地址。
  • 它是訪問表中行的最快方式。
  • ROWID 的值看起來像一串十六進(jìn)制字符。
  • 雖然唯一，但如果表發(fā)生重組或遷移，行的 ROWID 可能會(huì)改變。因此，不建議將其作為持久的行標(biāo)識(shí)符。

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

• ROWNUM:
  • 對(duì)于查詢返回的每一行，ROWNUM 會(huì)按順序分配一個(gè)從1開始的數(shù)字。
  • ROWNUM 是在數(shù)據(jù)被檢索出來之后，但在任何 ORDER BY 子句應(yīng)用之前分配的。
  • 常用于限制查詢結(jié)果的行數(shù) (分頁查詢的基礎(chǔ))。
  • 重要：不能直接在 WHERE 子句中使用 ROWNUM > n (n>1) 來獲取第n行之后的數(shù)據(jù)，因?yàn)?nbsp;ROWNUM 是逐行分配的。如果第一行不滿足 ROWNUM > 1，那么就沒有第二行可以被分配 ROWNUM = 2。

-- 獲取前5名員工 (基于默認(rèn)順序或ORDER BY之前的順序)
SELECT employee_id, first_name, salary FROM employees WHERE ROWNUM <= 5;

-- 錯(cuò)誤的方式嘗試獲取第6到第10名員工
-- SELECT * FROM employees WHERE ROWNUM > 5 AND ROWNUM <= 10; (通常不會(huì)返回任何結(jié)果)

-- 正確的分頁方式 (使用子查詢)
SELECT *
FROM (SELECT employee_id, first_name, salary, ROWNUM AS rn
      FROM (SELECT employee_id, first_name, salary
            FROM employees
            ORDER BY salary DESC)) -- 內(nèi)層先排序
WHERE rn BETWEEN 6 AND 10;

• LEVEL:
  • 與層次查詢 (Hierarchical Queries) 一起使用 (CONNECT BY 子句)。
  • 表示當(dāng)前行在層次結(jié)構(gòu)中的級(jí)別。根節(jié)點(diǎn)為 LEVEL 1。

-- 假設(shè)employees表有 manager_id 列，形成層級(jí)關(guān)系
SELECT LEVEL, employee_id, first_name, manager_id
FROM employees
START WITH manager_id IS NULL -- 定義根節(jié)點(diǎn)
CONNECT BY PRIOR employee_id = manager_id; -- 定義父子關(guān)系

• NEXTVAL 和 CURRVAL (與序列 Sequence 相關(guān)):
  • sequence_name.NEXTVAL: 獲取序列的下一個(gè)值。每次調(diào)用都會(huì)使序列遞增。
  • sequence_name.CURRVAL: 獲取序列的當(dāng)前值 (必須在當(dāng)前會(huì)話中至少調(diào)用過一次 NEXTVAL 之后才能使用)。
  • 常用于在 INSERT 語句中為主鍵列生成唯一值。

-- 假設(shè)存在一個(gè)名為 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; -- 查看當(dāng)前會(huì)話中序列的當(dāng)前值

六、GROUP BY 子句：數(shù)據(jù)分組

GROUP BY 子句將具有相同值的行組織成一個(gè)摘要組。通常與聚合函數(shù) (如 COUNT(), SUM(), AVG(), MAX(), MIN()) 一起使用，對(duì)每個(gè)組進(jìn)行計(jì)算。

聚合函數(shù)： (與之前版本相同)

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

使用規(guī)則：

• SELECT 列表中所有未包含在聚合函數(shù)中的列，都必須出現(xiàn)在 GROUP BY 子句中。
• WHERE 子句先于 GROUP BY 執(zhí)行；HAVING 子句后于 GROUP BY 執(zhí)行。

代碼案例：查詢每個(gè)部門的員工人數(shù)：

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

七、HAVING 子句：分組過濾

HAVING 子句用于在數(shù)據(jù)分組后對(duì)分組結(jié)果進(jìn)行進(jìn)一步篩選。它通常包含聚合函數(shù)。

代碼案例：查詢平均薪水大于8000的部門：

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

八、ORDER BY 子句：結(jié)果排序

ORDER BY 子句用于對(duì)最終查詢結(jié)果集進(jìn)行排序。它是查詢語句中邏輯上最后執(zhí)行的部分。

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

• ASC (升序, 默認(rèn)), DESC (降序)
• 多列排序, 列別名排序, 列序號(hào)排序 (不推薦)
• NULLS FIRST / NULLS LAST

代碼案例：按薪水降序排列員工信息：

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

總結(jié)： 單表查詢是 Oracle SQL 的基石。熟練掌握各子句的功能、用法、執(zhí)行順序，以及偽列 (特別是 ROWNUM 和 ROWID) 的特性，是編寫高效、準(zhǔn)確查詢的關(guān)鍵。

練習(xí)題

背景表：假設(shè)我們有一個(gè) products 表，結(jié)構(gòu)如下：

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'
);
-- 插入一些樣例數(shù)據(jù) (請(qǐng)自行補(bǔ)充更多數(shù)據(jù)以測(cè)試所有題目)
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;

假設(shè) category_id 10=‘Beverages’, 20=‘Condiments’, 30=‘Confections’, 40=‘Dairy Products’。

請(qǐng)為以下每個(gè)場(chǎng)景編寫相應(yīng)的SQL查詢語句。

題目：

• 查詢 products 表中所有產(chǎn)品的 ROWID 和 product_name。
• 查詢 products 表中前5條記錄的 product_id, product_name, unit_price (基于它們?cè)诒碇械奈锢泶鎯?chǔ)順序，不指定特定排序)。
• 查詢 products 表中按 unit_price 降序排列后的第3到第5條產(chǎn)品記錄的 product_name 和 unit_price。
• 查詢每個(gè) category_id 下有多少種產(chǎn)品，并為每個(gè)類別結(jié)果行分配一個(gè)行號(hào) (基于 category_id 的默認(rèn)分組順序)。
• 查詢所有 category_id 為 20 (Condiments) 的產(chǎn)品名稱和庫存量 (units_in_stock)，并給 product_name 列起別名為 “調(diào)味品名稱”，units_in_stock 列起別名為 “當(dāng)前庫存”。
• 查詢單價(jià) (unit_price) 大于等于20且小于50的所有產(chǎn)品信息 (使用 BETWEEN 或比較運(yùn)算符均可)。
• 查詢產(chǎn)品名稱 (product_name) 以 “Chef Anton” 開頭的所有產(chǎn)品ID和產(chǎn)品名稱。
• 統(tǒng)計(jì)每個(gè) supplier_id 供應(yīng)的產(chǎn)品中，已停產(chǎn) (discontinued = ‘Y’) 的產(chǎn)品數(shù)量。只顯示供應(yīng)了已停產(chǎn)產(chǎn)品的供應(yīng)商ID及其對(duì)應(yīng)的已停產(chǎn)產(chǎn)品數(shù)量。
• 查詢所有產(chǎn)品信息，并按 category_id 升序排序，在同一類別中再按 units_in_stock 降序排序，并將庫存量為 NULL 的產(chǎn)品排在最后。
• (與序列相關(guān)，假設(shè)已創(chuàng)建序列 product_pk_seq) 使用序列 product_pk_seq.NEXTVAL 作為 product_id，插入一條新產(chǎn)品記錄：product_name=‘New Test Product’, category_id=10, unit_price=15.00, units_in_stock=100。然后查詢?cè)撔蛄械漠?dāng)前值。(只需寫INSERT和查詢序列的語句)

答案與解析：

• 查詢 ROWID 和 product_name：

SELECT ROWID, product_name FROM products;

• 解析： ROWID 是一個(gè)偽列，可以直接在 SELECT 列表中引用。

• 查詢前5條記錄 (基于物理順序)：

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

• 解析： ROWNUM 在 WHERE 子句中用于限制返回的行數(shù)。此時(shí)的順序是Oracle獲取數(shù)據(jù)的自然順序，不保證特定排序。

• 分頁查詢 (排序后取特定范圍)：

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分頁的標(biāo)準(zhǔn)寫法。最內(nèi)層查詢先按價(jià)格降序排序，中間層查詢?yōu)榕判蚝蟮慕Y(jié)果分配 ROWNUM (并賦予別名 rn)，最外層查詢根據(jù) rn 篩選出第3到第5條記錄。

• 分組并為組結(jié)果分配行號(hào) (分析函數(shù))：(嚴(yán)格來說，為分組結(jié)果分配行號(hào)通常使用分析函數(shù)如 ROW_NUMBER() OVER()，ROWNUM 在 GROUP BY 之后應(yīng)用是對(duì)聚合后的結(jié)果行進(jìn)行編號(hào))
  如果題目意圖是統(tǒng)計(jì)后給結(jié)果行編號(hào)：

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

• 解析： 先按 category_id 分組并用 COUNT(*) 統(tǒng)計(jì)。然后對(duì)這個(gè)聚合后的結(jié)果集中的每一行分配 ROWNUM。
  如果意圖是在每個(gè)組內(nèi)部分配行號(hào)，則需要分析函數(shù)（超出單表查詢基礎(chǔ)范圍，但可作了解）：

-- 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 "調(diào)味品名稱", units_in_stock AS "當(dāng)前庫存"
FROM products
WHERE category_id = 20;

• 范圍查詢 (多種寫法)：使用 BETWEEN AND:

SELECT * FROM products WHERE unit_price BETWEEN 20 AND 49.99;

使用比較運(yùn)算符:

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” 開頭的所有字符串。

• 分組統(tǒng)計(jì)已停產(chǎn)產(chǎn)品：

SELECT supplier_id, COUNT(*) AS discontinued_product_count
FROM products
WHERE discontinued = 'Y'
GROUP BY supplier_id
HAVING COUNT(*) > 0; -- 或者直接不加HAVING，如果沒有已停產(chǎn)的供應(yīng)商則不會(huì)顯示

• 解析： 先用 WHERE 篩選出已停產(chǎn)產(chǎn)品，然后按 supplier_id 分組并用 COUNT(*) 統(tǒng)計(jì)。HAVING COUNT(*) > 0 確保只顯示那些確實(shí)有已停產(chǎn)產(chǎn)品的供應(yīng)商。

• 多列排序與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的記錄排在每個(gè)類別的最后。

• 使用序列插入并查詢當(dāng)前值：(假設(shè)序列 product_pk_seq 已創(chuàng)建: 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 獲取序列的下一個(gè)值并用于插入。product_pk_seq.CURRVAL 從 dual 表查詢當(dāng)前會(huì)話中該序列的當(dāng)前值 (必須在同一會(huì)話中先調(diào)用過 NEXTVAL)。

總結(jié) 

到此這篇關(guān)于Oracle數(shù)據(jù)庫查詢之單表查詢的關(guān)鍵子句及其用法的文章就介紹到這了,更多相關(guān)Oracle單表查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論