SQL Server 查詢語句大全：從基礎 SELECT 到進階連接、子查詢與 DML 實戰

本文系統整理 SQL Server 核心查詢與資料操作語法，涵蓋簡單查詢、聯合查詢、多表連接、子查詢、分頁、DML 操作與資料型別對照，適合開發人員速查與進階學習。

一、簡單查詢

1. 選擇列表

-- 全欄位
SELECT * FROM testtable;
-- 指定欄位與順序
SELECT nickname, email FROM testtable;
-- 別名（中文標題）
SELECT 昵稱 = nickname, 電子郵件 = email FROM testtable;
-- 去重
SELECT DISTINCT city FROM usertable;
-- 限制筆數
SELECT TOP 10 * FROM testtable;
SELECT TOP 20 PERCENT * FROM testtable;

2. FROM 子句與別名

SELECT u.username, c.cityname
FROM usertable AS u
JOIN citytable AS c ON u.cityid = c.cityid;

-- 衍生表
SELECT a.au_fname + a.au_lname AS author
FROM authors a
JOIN titleauthor ta ON a.au_id = ta.au_id
JOIN (SELECT title_id, title FROM titles WHERE ytd_sales > 10000) AS t
  ON ta.title_id = t.title_id;

3. WHERE 條件與排序

SELECT * FROM usertable WHERE age > 20;
SELECT * FROM usertable ORDER BY age DESC, userid ASC;

二、聯合查詢

SELECT city FROM suppliers WHERE country = 'Brazil'
UNION
SELECT city FROM customers WHERE country = 'Brazil';  -- 去重

SELECT city FROM suppliers WHERE country = 'Brazil'
UNION ALL
SELECT city FROM customers WHERE country = 'Brazil'; -- 保留重複

三、連接查詢

-- 等值內連接
SELECT a.*, p.pub_name 
FROM authors a INNER JOIN publishers p ON a.city = p.city;

-- 左外連接（保留所有論壇貼文）
SELECT f.*, u.username 
FROM luntan f LEFT JOIN usertable u ON f.username = u.username;

四、子查詢

-- 1. 單一值比較
SELECT * FROM products 
WHERE unit_price > ANY (SELECT unit_price FROM order_details WHERE discount >= 0.25);

-- 2. IN 子查詢
SELECT product_name FROM products 
WHERE product_id IN (SELECT product_id FROM order_details WHERE unit_price * quantity >= 1000);

-- 3. EXISTS 相關子查詢
SELECT c.companyname 
FROM customers c 
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customerid = c.customerid AND o.shipcountry = 'UK');

五、資料操作語言

-- 新增
INSERT INTO mytable (col1, col2) VALUES ('v1', 'v2');
INSERT INTO newtable SELECT * FROM oldtable WHERE status = 'active';

-- 修改
UPDATE mytable SET col1 = 'new' WHERE col2 = 'old';

-- 刪除
DELETE FROM mytable WHERE col1 = 'del';
TRUNCATE TABLE mytable;  -- 快速清空表，不記錄日誌

-- 利用 @@IDENTITY 取得新增身分識別值
INSERT INTO orders (cust_id) VALUES (100);
INSERT INTO order_items (order_id, item) VALUES (@@IDENTITY, 'Book');

六、SQL Server 常用資料型別速查

七、DDL 常用語法

-- 建表
CREATE TABLE demo (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(50) NOT NULL,
    created_at DATETIME DEFAULT GETDATE()
);

-- 增加/修改/刪除欄位
ALTER TABLE demo ADD email NVARCHAR(100);
ALTER TABLE demo ALTER COLUMN name NVARCHAR(100) NOT NULL;
ALTER TABLE demo DROP COLUMN email;

-- 索引
CREATE INDEX idx_demo_name ON demo(name);
DROP INDEX idx_demo_name ON demo;

這份大全涵蓋了日常開發 90% 以上的 SQL 場景，建議收藏備查。實務上請搭配執行計畫與索引調教，發揮最大效能。