資料庫開發中，語法細節易忘、效能寫法易混、特殊場景易卡關。這份筆記記錄實戰驗證過的 SQL 片段，拒絕學院派空談，只收錄「能直接複製跑、能解決問題、能優化效能」的硬核內容。持續更新，直到不再需要新增為止。
-- 精確查詢
SELECT col1, col2 FROM table_name WHERE col1 = 'value';
-- 模糊查詢（前綴索引友善）
SELECT * FROM users WHERE username LIKE 'john%';
-- 分頁查詢 (SQL Server 2012+ 標準語法)
SELECT * FROM orders
ORDER BY order_date DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;連接是關聯資料庫的靈魂。以下以 Orders (訂單) 與 Customers (客戶) 為例：
| 連接類型 | 語法關鍵字 | 結果集特徵 | 適用場景 |
|---|---|---|---|
| 內連接 | INNER JOIN | 僅返回雙邊均匹配的行 | 主從表關聯查詢（訂單+客戶資料） |
| 左外連接 | LEFT [OUTER] JOIN | 左表全保留，右表無匹配則 NULL | 所有客戶及其訂單（含無訂單客戶） |
| 右外連接 | RIGHT [OUTER] JOIN | 右表全保留，左表無匹配則 NULL | 所有訂單及其客戶（含無客戶訂單） |
| 全外連接 | FULL [OUTER] JOIN | 雙表全保留，無匹配處填 NULL | 雙向完整性對帳 |
| 交叉連接 | CROSS JOIN | 笛卡爾積（無 ON 條件） | 生成測試資料、排程矩陣 |
ON a.id = b.id（最常用）ON a.price > b.budget（範圍匹配、分級統計）鐵律：text、ntext、image 型別不可直接出現在 ON 條件。
-- 錯誤示範
SELECT * FROM docs d1 INNER JOIN docs d2 ON d1.content = d2.content; -- 報錯
-- 正確變通：比較長度、校驗和、或轉型
SELECT d1.id, d2.id
FROM docs d1 INNER JOIN docs d2
ON DATALENGTH(d1.content) = DATALENGTH(d2.content);| 運算符類別 | 運算符 | 範例 | 效能提示 |
|---|---|---|---|
| 比較 | = <> > >= < <= | age > 18 | 配合索引最佳 |
| 範圍 | BETWEEN ... AND | age BETWEEN 18 AND 65 | 等價於 >= AND <=，含邊界 |
| 列表 | IN / NOT IN | status IN (1,2,3) | 列表過長 (＞1000) 改用暫存表 JOIN |
| 模式 | LIKE / NOT LIKE | name LIKE 'A%' | 前綴 % 可用索引，前導 % 導致掃描 |
| 空值 | IS NULL / IS NOT NULL | phone IS NULL | 索引通常不含 NULL，需過濾索引 |
| 邏輯 | AND / OR / NOT | age>18 AND city='NY' | AND 優先級高於 OR，善用括號 |
-- 結尾匹配
WHERE filename LIKE '%.pdf'
-- 單字元佔位
WHERE code LIKE 'AB_' -- 匹配 AB0, AB1, ABX...
-- 字元集合
WHERE grade LIKE '[ABC]%' -- 以 A/B/C 開頭
WHERE grade LIKE '[^A]%' -- 不以 A 開頭
-- 範圍
WHERE serial LIKE '[0-9][0-9][0-9]' -- 三位數字
-- 中文雙位元組特殊處理
WHERE cname LIKE '%中文%' -- 直接寫入
-- 部分舊版驅動需雙百分號
WHERE cname LIKE '%%中文%%'-- 單列顯式指定欄位（推薦，防 schema 變更失效）
INSERT INTO publishers (pub_id, pub_name, city, state)
VALUES ('9001', 'Acme Publishing', 'New York', 'NY');
-- 多列單次插入 (SQL Server 2008+)
INSERT INTO publishers (pub_id, pub_name, city, state)
VALUES
('9002', 'Beta Books', 'Chicago', 'IL'),
('9003', 'Gamma Press', 'Boston', 'MA');
-- 從查詢結果插入 (跨表複製/歸檔)
INSERT INTO pub_archive (pub_id, pub_name, city, state, archived_at)
SELECT pub_id, pub_name, city, state, GETDATE()
FROM publishers
WHERE state IN ('CA', 'TX');-- 永遠先跑 SELECT 確認影響範圍
SELECT * FROM products WHERE category_id = 5 AND price < 100;
-- 確認無誤後執行 UPDATE
UPDATE products
SET price = price * 1.1,
updated_at = GETDATE(),
updated_by = SYSTEM_USER
WHERE category_id = 5 AND price < 100;
-- 多表 UPDATE (SQL Server 語法)
UPDATE p
SET p.stock = p.stock - o.qty
FROM products p
INNER JOIN order_details o ON p.product_id = o.product_id
WHERE o.order_id = 12345;-- 軟刪除優於硬刪除
UPDATE orders SET is_deleted = 1, deleted_at = GETDATE() WHERE order_id = 999;
-- 必須硬刪時：先 SELECT 驗證，再包在交易中
BEGIN TRAN;
DELETE FROM order_details WHERE order_id = 999;
DELETE FROM orders WHERE order_id = 999;
COMMIT TRAN;
-- 子查詢刪除（刪除某教室下的所有套餐關聯）
DELETE FROM suite_to_classroom
WHERE classroom_sect_id IN (
SELECT classroom_sect_id FROM classroom_sections
WHERE classroom_id = '112'
);CREATE VIEW vw_GroupActivitySummary AS
SELECT
g.group_id,
g.group_name,
gt.main_type,
gt.group_type_name,
g.group_icon_url,
-- 會員數權重 50
ISNULL((SELECT COUNT(*) FROM group_users gu WHERE gu.group_id = g.group_id), 0) * 50 +
-- 基金數權重 1
ISNULL(g.fund_count, 0) +
-- 文章數權重 5
ISNULL((SELECT COUNT(*) FROM topics t WHERE t.board_id = g.subforum_id), 0) * 5 +
-- 相簿數權重 10
ISNULL((SELECT COUNT(*) FROM group_photos gp WHERE gp.group_id = g.group_id), 0) * 10 +
-- 主題數權重 2
ISNULL(g.topic_account, 0) * 2 +
-- 點擊數權重 1
ISNULL(g.hit_account, 0) AS group_active_degree,
-- 明細欄位
ISNULL((SELECT COUNT(*) FROM group_users gu WHERE gu.group_id = g.group_id), 0) AS group_member_num,
ISNULL(g.fund_count, 0) AS fund_count,
(SELECT COUNT(*) FROM topics t WHERE t.board_id = g.subforum_id) AS group_article_num,
(SELECT COUNT(*) FROM group_photos gp WHERE gp.group_id = g.group_id) AS group_photo_num,
g.topic_account,
g.hit_account,
(SELECT user_name FROM users u WHERE u.user_id = g.creator_id) AS group_creator_name,
g.create_time
FROM group_info g
INNER JOIN group_type gt ON g.group_type_id = gt.group_type_id;(SELECT COUNT...) 若頻繁查詢大量群組，應改用 APPLY 或預先彙總表-- 命名參數呼叫（推薦，順序無關、可讀性高）
EXEC usp_DeleteMiniClassroom @miniClassroom_id = 123;
-- 位置參數呼叫（僅簡單測試用）
EXEC usp_DeleteMiniClassroom 123;CREATE PROCEDURE usp_DeleteMiniClassroom
@miniClassroom_id INT
AS
BEGIN
SET NOCOUNT ON; -- 抑制「影響 n 行」訊息，降低網路負載
SET XACT_ABORT ON; -- 任何運行時錯誤自動回滾整個交易
-- 參數驗證
IF @miniClassroom_id IS NULL
BEGIN
RAISERROR('miniClassroom_id 不可為 NULL', 16, 1);
RETURN -1;
END
BEGIN TRY
BEGIN TRANSACTION;
-- 1. 刪除套餐關聯
DELETE FROM suite_to_classroom
WHERE classroom_sect_id IN (
SELECT classroom_sect_id FROM classroom_sections
WHERE classroom_id = @miniClassroom_id
);
-- 2. 刪除課堂段落
DELETE FROM classroom_sections
WHERE classroom_id = @miniClassroom_id;
-- 3. 刪除用戶購買記錄
DELETE FROM user_buy_classroom
WHERE classroom_id = @miniClassroom_id;
-- 4. 刪除年級學科關聯
DELETE FROM classroom_grade_subject
WHERE classroom_id = @miniClassroom_id;
-- 5. 刪除課堂討論
DELETE FROM classroom_discuss
WHERE classroom_id = @miniClassroom_id;
-- 6. 刪除課程討論 (透過課程表關聯)
DELETE FROM course_discuss
WHERE course_id IN (
SELECT course_id FROM course_info
WHERE classroom_id = @miniClassroom_id
);
-- 7. 刪除用戶課程收藏
DELETE FROM user_course_favorite
WHERE course_id IN (
SELECT course_id FROM course_info
WHERE classroom_id = @miniClassroom_id
);
COMMIT TRANSACTION;
RETURN 0; -- 成功
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
DECLARE @err_msg NVARCHAR(4000) = ERROR_MESSAGE();
RAISERROR('刪除小課堂失敗: %s', 16, 1, @err_msg);
RETURN 99; -- 失敗
END CATCH
END;FAST_FORWARD 唯讀順向游標sp_executesql 參數化，防注入、利用計畫快取STRING_AGG 字串聚合與 JSON_VALUE 解析範例 (SQL 2017+)MERGE UPSERT 模式與 OUTPUT 子句捕獲異動資料QUERY_STORE 強制計畫實戰案例這份筆記將伴隨專案成長持續演進。歡迎在團隊 Wiki 同步維護，讓知識在實戰中沉澱、在分享中增值。
