您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

常用 SQL 查詢語句大全：從基礎查詢到複雜分析

編輯：關於SqlServer

一、基礎查詢

1.1 查看表結構

-- SQL Server
EXEC sp_help 'TableName';
-- 或
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TableName';

1.2 查詢所有/指定欄位

SELECT * FROM Emp;
SELECT EmpNo, EName, Mgr FROM Emp;
SELECT DISTINCT Mgr FROM Emp;

1.3 條件篩選

SELECT * FROM Emp WHERE Job = 'CLERK';
SELECT * FROM Emp WHERE HireDate > '1982-01-01';

1.4 算術運算與別名

SELECT EName, Sal*12 + ISNULL(Comm,0) AS YearSal FROM Emp;

1.5 模糊查詢 (LIKE)

SELECT * FROM Emp WHERE EName LIKE 'S__T%';  -- _ 單字元，% 多字元

1.6 IN / NULL 判斷

SELECT * FROM Emp WHERE Job IN ('CLERK','ANALYST');
SELECT * FROM Emp WHERE Mgr IS NULL;

1.7 邏輯運算與排序

SELECT * FROM Emp WHERE (Sal > 500 OR Job = 'MANAGER') AND EName LIKE 'J%';
SELECT * FROM Emp ORDER BY DeptNo, Sal DESC;

二、彙總與分組

SELECT MAX(Sal), MIN(Sal), AVG(Sal), SUM(Sal), COUNT(*) FROM Emp;
SELECT * FROM Emp WHERE Sal = (SELECT MAX(Sal) FROM Emp);

SELECT DeptNo, MAX(Sal), AVG(Sal) FROM Emp GROUP BY DeptNo;
SELECT DeptNo, Job, AVG(Sal), MIN(Sal) 
FROM Emp 
GROUP BY DeptNo, Job 
HAVING AVG(Sal) < 2000;

規則：SELECT 清單中的非彙總欄位必須出現在 GROUP BY；HAVING 篩選分組結果；執行順序 GROUP BY → HAVING → ORDER BY。

三、多表連接

3.1 內連接

SELECT E.EName, E.Sal, D.DName 
FROM Emp E 
JOIN Dept D ON E.DeptNo = D.DeptNo 
ORDER BY D.DeptNo;

3.2 非等值連接

SELECT E.EName, E.Sal, S.Grade 
FROM Emp 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 Emp E 
JOIN Emp M ON E.Mgr = M.EmpNo;

四、子查詢

4.1 單行子查詢 (=)

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

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

SELECT EName, Job, Sal, DeptNo FROM Emp 
WHERE Job IN (SELECT DISTINCT Job FROM Emp WHERE DeptNo = 10);

SELECT EName, Sal, DeptNo FROM Emp 
WHERE Sal > ALL (SELECT Sal FROM Emp WHERE DeptNo = 30);  -- 大於最高
-- 等價: WHERE Sal > (SELECT MAX(Sal) FROM Emp WHERE DeptNo = 30)  -- 效能較好

SELECT EName, Sal, DeptNo FROM Emp 
WHERE Sal > ANY (SELECT Sal FROM Emp WHERE DeptNo = 30);  -- 大於任一
-- 等價: WHERE Sal > (SELECT MIN(Sal) FROM Emp WHERE DeptNo = 30)

4.3 多欄子查詢

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

4.4 FROM 子查詢 (衍生表)

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

4.5 分頁查詢 (SQL Server 2012+)

SELECT * FROM Emp 
ORDER BY Sal 
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;  -- 第 2 頁，每頁 5 筆

舊版：ROW_NUMBER() OVER (ORDER BY Sal) AS Rn 外層篩選。

4.6 查詢結果建表 (CTAS)

SELECT EmpNo, EName, Sal, Job, DeptNo INTO MyTable FROM Emp;

4.7 集合運算

SELECT EName, Sal, Job FROM Emp WHERE Sal > 2500
UNION          -- 並集 (去重)
-- INTERSECT   -- 交集
-- UNION ALL   -- 並集 (保留重複)
-- EXCEPT      -- 差集 (第一個減第二個)
SELECT EName, Sal, Job FROM Emp WHERE Job = 'MANAGER';

效能：集合運算通常優於複雜 OR 條件。

4.8 子查詢插入/更新

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

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

五、Oracle/PostgreSQL/MySQL 差異備註

ROWNUM (Oracle) / LIMIT (MySQL/PG) / OFFSET/FETCH (SQL Server 2012+ / PG / Oracle 12c+)。
NVL (Oracle) ↔ ISNULL/COALESCE (SQL Server) ↔ COALESCE (標準)。
DUAL 虛擬表 (Oracle) 在 SQL Server 不需要。