萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL 查詢大全:從基礎單表到複雜多表、子查詢、分頁、集合運算的標準化寫法速查手冊

SQL 查詢是數據分析、後端開發、報表製作的核心技能。本文按複雜度遞進,系統整理單表查詢、多表連接、子查詢、分頁、集合運算、數據變更等高頻場景的標準寫法,附帶性能提示與避坑指南,適合作為桌面速查手冊。

📋 一、基礎單表查詢

1.1 查看表結構

-- SQL Server / PostgreSQL
EXEC sp_help 'TableName'; -- 或
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TableName';

-- MySQL
DESC TableName;
-- 或
SHOW COLUMNS FROM TableName;

1.2 基礎 SELECT

-- 全列(生產環境禁用,列明具體列名)
SELECT * FROM Employees;

-- 指定列 + 別名
SELECT EmpNo, EName AS Name, Sal * 12 + ISNULL(Comm,0) AS AnnualSal
FROM Employees;

-- 去重
SELECT DISTINCT DeptNo FROM Employees;

-- 算術運算與空值處理
-- ISNULL(Comm,0) / COALESCE(Comm,0) / NVL(Comm,0) -- 不同 DB 語法

1.3 條件篩選

-- 比較運算
SELECT * FROM Employees WHERE Sal > 5000;

-- 字符模糊匹配
SELECT * FROM Employees WHERE EName LIKE 'S__T%'; -- _ 單字符,% 任意字符

-- 集合包含
SELECT * FROM Employees WHERE Job IN ('CLERK','ANALYST');

-- 空值判斷(只能用 IS NULL/IS NOT NULL,不能用 =/!=)
SELECT * FROM Employees WHERE Mgr IS NULL;

-- 邏輯組合(注意優先級:NOT > AND > OR,建議显式加括號)
SELECT * FROM Employees 
WHERE (Sal > 5000 OR Job = 'MANAGER') AND EName LIKE 'J%';

1.4 排序

-- 多字段排序:按部門升序,薪資降序
SELECT * FROM Employees ORDER BY DeptNo ASC, Sal DESC;

-- 按別名排序
SELECT Sal * 12 AS AnnualSal FROM Employees ORDER BY AnnualSal DESC;

📊 二、聚合與分組

2.1 基礎聚合

SELECT MAX(Sal), MIN(Sal), AVG(Sal), SUM(Sal), COUNT(*) FROM Employees;

2.2 GROUP BY + HAVING

-- 按部門統計
SELECT DeptNo, MAX(Sal), AVG(Sal) FROM Employees GROUP BY DeptNo;

-- 多字段分組 + 組過濾
SELECT DeptNo, Job, AVG(Sal), MIN(Sal) 
FROM Employees 
GROUP BY DeptNo, Job 
HAVING AVG(Sal) < 2000;

分組鐵律:
1. SELECT 列表中的非聚合列必須出現在 GROUP BY 中
2. WHERE 不能用聚合函數,組過濾用 HAVING
3. 執行順序:WHERE → GROUP BY → HAVING → ORDER BY

🔗 三、多表查詢

3.1 內連接(標準 ANSI-92 語法)

SELECT E.EName, E.Sal, D.DName
FROM Employees E
INNER JOIN Departments D ON E.DeptNo = D.DeptNo
ORDER BY D.DeptNo;

3.2 非等值連接

SELECT E.EName, E.Sal, S.Grade
FROM Employees E
JOIN SalGrade S ON E.Sal BETWEEN S.LoSal AND S.HiSal;

3.3 自連接

SELECT E.EName AS EmpName, M.EName AS MgrName
FROM Employees E
LEFT JOIN Employees M ON E.Mgr = M.EmpNo;

🔍 四、子查詢全類型詳解

4.1 單行子查詢(返回 1 行 1 列)

SELECT EName FROM Employees 
WHERE DeptNo = (SELECT DeptNo FROM Employees WHERE EName = 'SMITH');

4.2 多行子查詢(IN / ANY / ALL)

-- IN:返回結果集中任意值匹配
SELECT EName, Job, Sal, DeptNo FROM Employees
WHERE Job IN (SELECT DISTINCT Job FROM Employees WHERE DeptNo = 10);

-- ALL:大於子查詢所有值 = 大於最大值
SELECT EName, Sal, DeptNo FROM Employees
WHERE Sal > ALL (SELECT Sal FROM Employees WHERE DeptNo = 30);
-- 等價優化寫法:
WHERE Sal > (SELECT MAX(Sal) FROM Employees WHERE DeptNo = 30);

-- ANY:大於子查詢任一值 = 大於最小值
WHERE Sal > ANY (SELECT Sal FROM Employees WHERE DeptNo = 30);
-- 等價:
WHERE Sal > (SELECT MIN(Sal) FROM Employees WHERE DeptNo = 30);

IN vs EXISTS 性能法則:
• 子查詢結果集小、外表大 → IN 優(可用索引)
• 子查詢結果集大、外表小 → EXISTS 優(早期終止)
• 現代優化器多能自動轉換,但 EXISTS 语义更清晰:
WHERE EXISTS (SELECT 1 FROM ... WHERE correlation_condition)

4.3 多列子查詢

SELECT * FROM Employees
WHERE (Job, DeptNo) = (SELECT Job, DeptNo FROM Employees WHERE EName = 'SMITH');

4.4 FROM 子查詢(派生表)

SELECT E.DeptNo, E.EName, E.Sal, T.AvgSal
FROM Employees E
JOIN (
    SELECT DeptNo, AVG(Sal) AS AvgSal FROM Employees GROUP BY DeptNo
) T ON E.DeptNo = T.DeptNo
WHERE E.Sal > T.AvgSal
ORDER BY E.DeptNo;

4.5 分頁查詢(通用邏輯)

-- 標準思路:ROW_NUMBER() 窗口函數 (SQL Server 2012+, PG, Oracle 12c+)
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY Sal DESC) AS RN
    FROM Employees
) T
WHERE RN BETWEEN 6 AND 10; -- 第 2 頁,每頁 5 條

-- MySQL / PostgreSQL 簡寫
SELECT * FROM Employees ORDER BY Sal DESC LIMIT 5 OFFSET 5;

-- SQL Server 2012+ OFFSET-FETCH
SELECT * FROM Employees ORDER BY Sal DESC OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

4.6 集合運算

-- UNION:並集(去重)
SELECT EName, Sal, Job FROM Employees WHERE Sal > 2500
UNION
SELECT EName, Sal, Job FROM Employees WHERE Job = 'MANAGER';

-- UNION ALL:並集(保留重複,性能更好)
-- INTERSECT:交集
-- EXCEPT / MINUS:差集(第一個結果集減去第二個)

✏️ 五、數據操作 (DML)

5.1 基於查詢創建新表

CREATE TABLE MyEmp AS 
SELECT EmpNo, EName, Sal, Job, DeptNo FROM Employees; -- PG/Oracle/MySQL

-- SQL Server
SELECT EmpNo, EName, Sal, Job, DeptNo INTO MyEmp FROM Employees;

5.2 子查詢插入

INSERT INTO MyEmp (EmpID, Name, Sal, Job, Dept)
SELECT EmpNo, EName, Sal, Job, DeptNo FROM Employees WHERE DeptNo = 10;

5.3 子查詢更新

UPDATE Employees
SET (Job, Sal, Comm) = (SELECT Job, Sal, Comm FROM Employees WHERE EName = 'SMITH')
WHERE EName = 'SCOTT';

🎯 性能與規範速查表

場景推薦寫法避免寫法
存在性檢查EXISTS (SELECT 1 ...)IN (SELECT ...)、COUNT(*) > 0
分頁大偏移量鍵集分頁 / 游標分頁OFFSET 大數值
多表關聯ANSI JOIN 語法 + 索引舊式 WHERE 連接、隱式轉換
字符串拼接參數化查詢字符串拼接 SQL(注入風險)
列名顯式列名SELECT *

記住:SQL 是聲明式語言,描述「要什麼」而非「怎麼做」。理解優化器如何將邏輯轉為物理執行計劃,比背誦語法更重要。EXPLAIN / 顯示執行計劃,是通往 SQL 專家之路的必經之路。

copyright © 萬盛學電腦網 all rights reserved