萬盛學電腦網

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

SQL Server 綜合實戰總結:DDL、DML、進階查詢、交易、預存程序與 ADO.NET

面向抽象程式設計架構

介面 → 抽象類別 → 父類別 → 具體類別

1. T-SQL 核心回顧

DDL - 結構定義


-- 建庫 (安全模式)
IF DB_ID('DBName') IS NOT NULL DROP DATABASE DBName;
CREATE DATABASE DBName;

-- 建表 (安全模式)
IF OBJECT_ID('TableName', 'U') IS NULL
CREATE TABLE TableName (
    Col1 TYPE [CONSTRAINT],
    Col2 TYPE [CONSTRAINT],
    ...
);

-- 約束命名慣例與語法
ALTER TABLE T ADD CONSTRAINT PK_T_Col PRIMARY KEY (Col);
ALTER TABLE T ADD CONSTRAINT DF_T_Col DEFAULT (GETDATE()) FOR Col;
ALTER TABLE T ADD CONSTRAINT CK_T_Col CHECK (Col > 0);
ALTER TABLE T ADD CONSTRAINT UQ_T_Col UNIQUE (Col);
ALTER TABLE Child ADD CONSTRAINT FK_Child_Parent_Col 
    FOREIGN KEY (Col) REFERENCES Parent(Col);

DML - 資料操作


-- 新增
INSERT INTO T (cols) VALUES (vals);
SELECT cols INTO NewT FROM OldT;  -- 新表不存在
INSERT INTO T (cols) SELECT ...;

-- 刪除
DELETE FROM T WHERE cond;
TRUNCATE TABLE T;  -- 全清、重設 IDENTITY、最小 Log

-- 修改
UPDATE T SET Col1=Val1, Col2=Val2 WHERE cond;

-- 查詢核心流程 (邏輯執行順序)
FROM → WHERE → GROUP BY → HAVING → SELECT (DISTINCT/TOP/運算) → ORDER BY

2. 進階查詢技巧

CASE 運算式


-- IF-ELSE 結構
CASE 
    WHEN 條件1 THEN 結果1
    WHEN 條件2 THEN 結果2
    ELSE 結果N
END

-- SWITCH-CASE 結構
CASE 欄位
    WHEN 值1 THEN 結果1
    WHEN 值2 THEN 結果2
    ELSE 結果N
END

連接 (JOIN)


-- 交叉聯結
SELECT * FROM A, B;           -- ANSI-89
SELECT * FROM A CROSS JOIN B; -- ANSI-92

-- 內連接
SELECT * FROM A, B WHERE A.id = B.id;  -- ANSI-89
SELECT * FROM A INNER JOIN B ON A.id = B.id; -- ANSI-92

-- 外部連接
SELECT * FROM A LEFT JOIN B ON A.id = B.id;
SELECT * FROM A RIGHT JOIN B ON A.id = B.id;

-- 多表連接
SELECT * FROM A
INNER JOIN B ON A.id = B.a_id
INNER JOIN C ON B.id = C.b_id;

子查詢

  • 獨立標量:WHERE col = (SELECT ...)
  • 獨立多值:WHERE col IN (SELECT ...)
  • 相關子查詢:參考外部查詢欄位,逐列評估。

表運算式


-- 派生表 (不可單獨 ORDER BY,除非搭配 TOP)
SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY stuId) AS rn, * FROM Student) AS t
WHERE t.rn BETWEEN @count*(@page-1)+1 AND @count*@page;

-- CTE (共用表運算式)
WITH cte AS (SELECT ROW_NUMBER() OVER (ORDER BY stuId) AS rn, * FROM Student)
SELECT * FROM cte WHERE rn BETWEEN ...;

3. 檢視表


CREATE VIEW vw_Name AS SELECT ...;  -- 虛擬表,不儲存資料,使用時展開

4. 內嵌表值函數 (參數化檢視表)


CREATE FUNCTION fn_Name(@p TYPE) RETURNS TABLE
AS RETURN SELECT ...;

5. 變數與流程控制


-- 宣告與指派
DECLARE @v TYPE;
SET @v = 值;
SELECT @v = col FROM T ...;  -- 支援多列 (最後一列生效)
SET @v = (SELECT ...);       -- 單值子查詢

-- SQL 2008+ 宣告即指派
DECLARE @name VARCHAR(5) = '大鑫';

-- 判斷
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. 交易 (Transaction)

  • ACID:原子性、一致性、隔離性、持久性。
  • 每條 T-SQL 隱式為一交易;顯式交易:BEGIN TRAN ... COMMIT/ROLLBACK TRAN。
  • 全域變數:@@ERROR (最後錯誤)、@@IDENTITY (最後自增 ID)、@@VERSION。
  • 錯誤處理:BEGIN TRY ... END TRY BEGIN CATCH ... END CATCH。

7. 預存程序模式大全


CREATE PROC usp_Name
    @p1 TYPE [= default] [OUTPUT],
    @p2 TYPE [= default] [OUTPUT]
AS
BEGIN
    -- 邏輯
END
GO

-- 呼叫
EXEC usp_Name @p1 = 'v1', @p2 = @out OUTPUT;
  • 無參無回傳、有參無回傳、預設參數、OUTPUT 參數、結果集、RETURN 狀態碼。

8. ADO.NET 執行 SQL 核心模式


string connStr = "...";
using (var conn = new SqlConnection(connStr))
using (var cmd = new SqlCommand("usp_Name", conn) { CommandType = CommandType.StoredProcedure })
{
    cmd.Parameters.AddWithValue("@p1", val1);
    // OUTPUT 參數
    var pOut = new SqlParameter("@p2", SqlDbType.Int) { Direction = ParameterDirection.Output };
    cmd.Parameters.Add(pOut);
    
    conn.Open();
    cmd.ExecuteNonQuery();  // 無結果集
    // 或
    using (var rdr = cmd.ExecuteReader()) { while (rdr.Read()) { ... } }
    // 取 OUTPUT
    int result = (int)pOut.Value;
}

總結

此總結涵蓋從 DDL/DML 基礎、進階查詢 (CASE/JOIN/子查詢/CTE)、檢視表/函數封裝、變數流程控制、交易與錯誤處理、預存程序完整模式到 ADO.NET 呼叫實戰。將這些模式內化為肌肉記憶,配合實際專案場景靈活組合,即為資料庫開發高手之路。

copyright © 萬盛學電腦網 all rights reserved