SQL 常用語句速查手冊（精簡版）

DDL - 物件定義

-- 資料庫
CREATE DATABASE DBName;
DROP DATABASE DBName;
BACKUP DATABASE DBName TO DISK='Path\File.bak';

-- 資料表
CREATE TABLE T (Id INT PRIMARY KEY, Name NVARCHAR(50) NOT NULL);
CREATE TABLE T2 AS SELECT * FROM T WHERE 1=0;  -- 僅複製結構
ALTER TABLE T ADD Email NVARCHAR(100);
ALTER TABLE T DROP COLUMN Email;
DROP TABLE T;

-- 索引
CREATE INDEX IX_T_Name ON T(Name);
CREATE UNIQUE INDEX IX_T_Email ON T(Email);
DROP INDEX IX_T_Name ON T;

-- 檢視表
CREATE VIEW V AS SELECT Id, Name FROM T WHERE Active=1;
DROP VIEW V;

DML - 資料操作

SELECT Id, Name FROM T WHERE Name LIKE N'%關鍵字%';
INSERT INTO T (Id, Name) VALUES (1, N'測試');
UPDATE T SET Name=N'新名' WHERE Id=1;
DELETE FROM T WHERE Id=1;
TRUNCATE TABLE T;  -- 快速清空，不記錄逐列、重設 IDENTITY

集合運算

SELECT * FROM A UNION SELECT * FROM B;      -- 去重
SELECT * FROM A UNION ALL SELECT * FROM B;  -- 保留重複，快
SELECT * FROM A INTERSECT SELECT * FROM B;  -- 交集
SELECT * FROM A EXCEPT SELECT * FROM B;     -- 差集 (A-B)

連接查詢

SELECT * FROM A INNER JOIN B ON A.Id=B.AId;
SELECT * FROM A LEFT JOIN B ON A.Id=B.AId;
SELECT * FROM A RIGHT JOIN B ON A.Id=B.AId;
SELECT * FROM A FULL JOIN B ON A.Id=B.AId;

彙總與視窗函數

SELECT COUNT(*), SUM(Amt), AVG(Amt), MAX(Amt), MIN(Amt) FROM Orders;
SELECT ROW_NUMBER() OVER (ORDER BY Amt DESC) AS Rn, * FROM Orders;
SELECT LAG(Amt) OVER (ORDER BY OrderDate) AS PrevAmt, * FROM Orders;

分頁 (SQL 2012+)

SELECT * FROM Products ORDER BY Id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

交易控制

BEGIN TRY
    BEGIN TRAN;
        UPDATE A SET ...;
        INSERT B ...;
    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT>0 ROLLBACK;
    THROW;  -- 重新拋出錯誤
END CATCH

動態 SQL 安全執行

DECLARE @SQL NVARCHAR(MAX) = N'SELECT * FROM ' + QUOTENAME(@TableName) + ' WHERE Id=@Id';
EXEC sp_executesql @SQL, N'@Id INT', @Id=@Id;

權限速查

CREATE LOGIN u WITH PASSWORD='P@ssw0rd';
CREATE USER u FOR LOGIN u;
GRANT SELECT, INSERT ON Schema::dbo TO u;
REVOKE DELETE ON Schema::dbo TO u;