SQL Server 綜合複習筆記：DDL/DML、交易、預存程序、ADO.NET、觸發器

1. DDL 建庫建表約束

-- 建庫
IF DB_ID('DB名') IS NOT NULL DROP DATABASE DB名;
CREATE DATABASE DB名;

-- 建表
IF OBJECT_ID('表名','U') IS NULL
CREATE TABLE 表名 (
    欄位 類型 [約束],
    ...
);

-- 約束命名慣例
-- PK_表名_欄位, DF_表名_欄位, CK_表名_欄位, UQ_表名_欄位, FK_外鍵表_主鍵表_欄位

ALTER TABLE 表名 ADD CONSTRAINT PK_表名_欄位 PRIMARY KEY(欄位);
ALTER TABLE 表名 ADD CONSTRAINT DF_表名_欄位 DEFAULT(值) FOR 欄位;
ALTER TABLE 表名 ADD CONSTRAINT CK_表名_欄位 CHECK(運算式);
ALTER TABLE 表名 ADD CONSTRAINT UQ_表名_欄位 UNIQUE(欄位);
ALTER TABLE 外鍵表 ADD CONSTRAINT FK_外鍵表_主鍵表_欄位
    FOREIGN KEY(欄位) REFERENCES 主鍵表(欄位);

2. DML 增刪改查

-- 增
INSERT INTO 表(欄位) VALUES(值);
SELECT 欄位 INTO 新表 FROM 舊表;  -- 新表不存在
INSERT INTO 表(欄位) SELECT ...;

-- 刪
DELETE FROM 表 WHERE 條件;
TRUNCATE TABLE 表;  -- 重置身分識別、不記錄日誌、速度快

-- 改
UPDATE 表 SET 欄位=值,... WHERE 條件;

-- 查核心流程
FROM → WHERE → GROUP BY → HAVING → SELECT (TOP/DISTINCT/運算) → ORDER BY

3. 進階查詢

CASE 運算式

-- 簡單 CASE
CASE 欄位 WHEN 值1 THEN 結果1 WHEN 值2 THEN 結果2 ELSE 結果N END

-- 搜尋 CASE
CASE WHEN 運算式1 THEN 結果1 WHEN 運算式2 THEN 結果2 ELSE 結果N END

連接查詢

-- 交叉連接
SELECT * FROM 表1 CROSS JOIN 表2;

-- 內連接
SELECT * FROM 表1 INNER JOIN 表2 ON 條件;

-- 外連接
SELECT * FROM 表1 LEFT JOIN 表2 ON 條件;
SELECT * FROM 表1 RIGHT JOIN 表2 ON 條件;

-- 多表連接
表1 INNER JOIN 表2 ON 條件 INNER JOIN 表3 ON 條件...

子查詢與表運算式

-- 派生表 (不支援 ORDER BY)
SELECT * FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY stuId) AS num, *
    FROM Student
) AS tbl WHERE tbl.num BETWEEN @count*(@page-1)+1 AND @count*@page;

-- CTE
WITH 別名 AS (查詢) SELECT ... FROM 別名;

4. 視圖與內聯表值函數

CREATE VIEW vw_名稱 AS 查詢;  -- 虛擬表，不儲存資料
CREATE FUNCTION fn_名稱(@參數 類型) RETURNS TABLE AS RETURN 查詢;  -- 帶參數的視圖

5. 變數與流程控制

DECLARE @變數 類型;
SET @變數 = 值;
SELECT @變數 = 欄位 FROM 表...;  -- 多值取最後一筆
SET @變數 = (單值子查詢);         -- 單值

IF 條件 BEGIN ... END ELSE IF ... BEGIN ... END ELSE BEGIN ... END
WHILE 條件 BEGIN ... END

-- 範例：1 到 100 求和
DECLARE @sum INT=0, @i INT=1;
WHILE @i <= 100 BEGIN SET @sum += @i; SET @i += 1; END
SELECT @sum;

6. 交易控制

BEGIN TRAN;
    -- 業務邏輯
    IF @@ERROR <> 0 ROLLBACK TRAN ELSE COMMIT TRAN;

-- TRY...CATCH 範本
BEGIN TRY
    BEGIN TRAN; ... COMMIT TRAN;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRAN;
    -- 錯誤處理/記錄
END CATCH

-- 常用全域變數
@@VERSION, @@ERROR, @@IDENTITY (最後插入身分識別值)

7. 預存程序模板

CREATE PROC usp_名稱
    @參數1 類型 [=預設] [OUTPUT],
    @參數n 類型 [=預設] [OUTPUT]
AS BEGIN
    -- 多句需 BEGIN...END
    SQL語句
END
GO

EXEC usp_名稱 參數;
EXEC usp_名稱 @參數名=值;
EXEC usp_名稱 參數, 參數n OUTPUT;

8. ADO.NET 執行預存程序

string sql = "usp_TransMoney";
SqlParameter[] ps = {
    new SqlParameter("@from", from),
    new SqlParameter("@to", to),
    new SqlParameter("@money", money),
    new SqlParameter("@isAccess", SqlDbType.Int) { Direction = ParameterDirection.Output }
};
using (var conn = new SqlConnection(connStr))
using (var cmd = new SqlCommand(sql, conn) { CommandType = CommandType.StoredProcedure })
{
    cmd.Parameters.AddRange(ps);
    conn.Open();
    cmd.ExecuteNonQuery();
    int result = (int)cmd.Parameters["@isAccess"].Value;
}

9. 觸發器

-- 插入觸發器範例：備份 inserted 資料
CREATE TRIGGER tr_Ins_Backup ON 表名
FOR INSERT AS
    INSERT INTO BackupTable SELECT * FROM inserted;
GO

-- 刪除觸發器：還原 deleted 資料
CREATE TRIGGER tr_Del_Restore ON 表名
FOR DELETE AS
    INSERT INTO 表名 SELECT * FROM deleted;
GO

-- INSTEAD OF 取代原動作
CREATE TRIGGER tr_Instead ON 表名
INSTEAD OF INSERT AS ...