一、資料庫與物件管理 (DDL)
| 操作 | 語法範例 | 備註 |
|---|
| 建立資料庫 | CREATE DATABASE MyDB; | 可加 ON PRIMARY ... LOG ON ... 指定檔案 |
| 刪除資料庫 | DROP DATABASE MyDB; | 需獨佔存取 |
| 備份資料庫 | BACKUP DATABASE MyDB TO DISK='C:\Backup\MyDB.bak'; | 完整備份 |
| 建立資料表 | CREATE TABLE Users (ID INT PRIMARY KEY, Name NVARCHAR(50) NOT NULL); | 支援 IDENTITY、DEFAULT、CHECK |
| 修改資料表 | ALTER TABLE Users ADD Email NVARCHAR(100); | 加欄位；刪欄位用 DROP COLUMN |
| 建立索引 | CREATE INDEX IX_Users_Name ON Users(Name); | 唯一索引加 UNIQUE；含含欄位用 INCLUDE |
| 建立檢視表 | CREATE VIEW v_ActiveUsers AS SELECT * FROM Users WHERE IsActive=1; | 加 WITH SCHEMABINDING 可建索引檢視 |
二、資料操作 (DML)
| 操作 | 語法 |
|---|
| 查詢 | SELECT col1, col2 FROM Table WHERE condition ORDER BY col1 DESC; |
| 新增 | INSERT INTO Table (col1, col2) VALUES ('A', 'B'); |
| 更新 | UPDATE Table SET col1='New' WHERE condition; |
| 刪除 | DELETE FROM Table WHERE condition; |
| 合併 | MERGE INTO Target USING Source ON ... WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ...; |
三、集合運算子
| 運算子 | 行為 | 去重 |
|---|
UNION | 合併兩結果集 | 是 |
UNION ALL | 合併兩結果集 | 否（效能較好） |
INTERSECT | 交集 | 是 |
EXCEPT | 差集 (左 - 右) | 是 |
※ 所有運算子要求欄位數、順序、型別相容。
四、連接查詢
-- 內部連接
SELECT * FROM Orders o INNER JOIN Customers c ON o.CustID = c.CustID;
-- 左外部連接（保留左表所有列）
SELECT * FROM Customers c LEFT JOIN Orders o ON c.CustID = o.CustID;
-- 完整外部連接
SELECT * FROM TableA a FULL JOIN TableB b ON a.ID = b.ID;
-- 交叉連接（笛卡兒積）
SELECT * FROM TableA CROSS JOIN TableB;
五、常用彙總與視窗函數
| 函數 | 用途 | 範例 |
|---|
COUNT(*) | 列數 | SELECT COUNT(*) FROM Orders; |
SUM(col) | 加總 | SELECT SUM(Amount) FROM Orders; |
AVG(col) | 平均 | SELECT AVG(Score) FROM Exams; |
ROW_NUMBER() OVER (ORDER BY col) | 排序編號 | 分頁、去重首選 |
RANK() / DENSE_RANK() | 排名 | 並列名次處理不同 |
LEAD() / LAG() | 取得前後列值 | 同期比較 |
六、進階查詢模式
相關子查詢 vs JOIN
-- 相關子查詢（逐列執行，較慢）
SELECT * FROM Orders o
WHERE EXISTS (SELECT 1 FROM OrderDetails d WHERE d.OrderID = o.OrderID);
-- 改寫為 JOIN（集合運算，較快）
SELECT DISTINCT o.* FROM Orders o
INNER JOIN OrderDetails d ON o.OrderID = d.OrderID;
分頁查詢 (SQL Server 2012+)
SELECT * FROM Products
ORDER BY ProductID
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
七、交易控制
BEGIN TRANSACTION;
UPDATE Accounts SET Balance -= 100 WHERE ID = 1;
UPDATE Accounts SET Balance += 100 WHERE ID = 2;
IF @@ERROR <> 0 ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
或使用 TRY...CATCH 區塊 (SQL 2005+)。
八、權限管理
CREATE LOGIN AppUser WITH PASSWORD = 'Str0ng!Pwd';
CREATE USER AppUser FOR LOGIN AppUser;
GRANT SELECT, INSERT, UPDATE ON Schema::dbo TO AppUser;
DENY DELETE ON Schema::dbo TO AppUser;
