介面 → 抽象類別 → 父類別 → 具體類別
-- 建庫 (安全模式)
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);
-- 新增
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
-- 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
-- 交叉聯結
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 ...;
CREATE VIEW vw_Name AS SELECT ...; -- 虛擬表,不儲存資料,使用時展開
CREATE FUNCTION fn_Name(@p TYPE) RETURNS TABLE
AS RETURN SELECT ...;
-- 宣告與指派
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;
BEGIN TRAN ... COMMIT/ROLLBACK TRAN。@@ERROR (最後錯誤)、@@IDENTITY (最後自增 ID)、@@VERSION。BEGIN TRY ... END TRY BEGIN CATCH ... END CATCH。
CREATE PROC usp_Name
@p1 TYPE [= default] [OUTPUT],
@p2 TYPE [= default] [OUTPUT]
AS
BEGIN
-- 邏輯
END
GO
-- 呼叫
EXEC usp_Name @p1 = 'v1', @p2 = @out OUTPUT;
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 呼叫實戰。將這些模式內化為肌肉記憶,配合實際專案場景靈活組合,即為資料庫開發高手之路。