一、資料庫核心概念
ACID 特性
- 原子性：全部完成或全部不做。
- 一致性：交易前後資料庫保持一致狀態。
- 隔離性：並發交易互不干擾。
- 持久性：認可後永久生效。
系統資料庫
| 名稱 | 用途 |
|---|
| master | 執行個體級中繼資料、登入、端點、連結伺服器 |
| model | 新資料庫範本 |
| msdb | SQL Agent 作業、備份歷史、維護計畫 |
| tempdb | 暫存表、表變數、排序、版本存放區 |
二、DDL 常用語法
建立/刪除資料庫
CREATE DATABASE [Demo] ON PRIMARY
(NAME = N'Demo', FILENAME = N'D:\Data\Demo.mdf', SIZE = 3072KB, FILEGROWTH = 1024KB)
LOG ON (NAME = N'Demo_log', FILENAME = N'D:\Log\Demo.ldf', SIZE = 1024KB, FILEGROWTH = 10%);
GO
DROP DATABASE [Demo];
資料表約束
-- 主鍵
ALTER TABLE dbo.T ADD CONSTRAINT PK_T PRIMARY KEY (Id);
-- 唯一
ALTER TABLE dbo.T ADD CONSTRAINT UQ_T_Code UNIQUE (Code);
-- 檢查
ALTER TABLE dbo.T ADD CONSTRAINT CK_T_Age CHECK (Age BETWEEN 0 AND 150);
-- 預設
ALTER TABLE dbo.T ADD CONSTRAINT DF_T_Created DEFAULT (GETDATE()) FOR CreatedAt;
-- 外鍵
ALTER TABLE dbo.Order ADD CONSTRAINT FK_Order_Cust FOREIGN KEY (CustId) REFERENCES dbo.Customer(Id);
三、DML 與查詢進階
通配符與運算子
| 符號 | 說明 | 範例 |
|---|
| _ | 單一字元 | LIKE 'C_' |
| % | 任意長度 | LIKE 'CO_%' |
| [] | 範圍內單一字元 | LIKE '9W0[1-2]' |
| [^] | 不在範圍內 | LIKE '%[A-Z][^1-2]' |
身分識別欄位行為
種子 1、遞增 2，插入 3 列 → 刪除 2 列 → 下一筆值為 7（不回收間隙）。
分頁查詢（ROW_NUMBER）
DECLARE @PageIndex INT = 2, @PageSize INT = 10;
SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY Id) AS rn, *
FROM dbo.BigTable
) t WHERE rn BETWEEN (@PageIndex-1)*@PageSize+1 AND @PageIndex*@PageSize;
四、程式設計結構
變數與流程控制
DECLARE @avg DECIMAL(5,2);
SELECT @avg = AVG(Score) FROM dbo.Score;
IF @avg > 70
PRINT '優秀';
ELSE
PRINT '需加強';
WHILE 迴圈範例：分數遞增至平均 85
WHILE (1=1)
BEGIN
UPDATE dbo.Score SET Score = CASE
WHEN Score < 60 THEN Score + 5
WHEN Score BETWEEN 60 AND 69 THEN Score + 3
WHEN Score BETWEEN 70 AND 79 THEN Score + 2
WHEN Score BETWEEN 80 AND 89 THEN Score + 1
ELSE Score END;
IF (SELECT AVG(Score) FROM dbo.Score) >= 85 BREAK;
END
CASE 運算式
SELECT StudentId,
Grade = CASE
WHEN Score < 60 THEN 'E'
WHEN Score BETWEEN 60 AND 69 THEN 'D'
WHEN Score BETWEEN 70 AND 79 THEN 'C'
WHEN Score BETWEEN 80 AND 89 THEN 'B'
ELSE 'A' END
FROM dbo.Score;
五、多表聯結
-- 內聯結
SELECT s.Name, sc.CourseId, sc.Score
FROM dbo.Student s INNER JOIN dbo.Score sc ON s.Id = sc.StudentId;
-- 左外聯結（找無帳戶客戶）
INSERT INTO dbo.Account (CardId, Balance)
SELECT u.CardId, 200
FROM dbo.[User] u LEFT JOIN dbo.Account a ON u.CardId = a.CardId
WHERE a.CardId IS NULL;
六、子查詢與 CTE
-- 遞迴 CTE 查詢組織架構
WITH OrgTree AS (
SELECT Id, Name, ParentId, 0 AS Lvl FROM dbo.Dept WHERE ParentId IS NULL
UNION ALL
SELECT d.Id, d.Name, d.ParentId, ot.Lvl + 1
FROM dbo.Dept d INNER JOIN OrgTree ot ON d.ParentId = ot.Id
)
SELECT * FROM OrgTree;
七、預存程序與觸發器
帶輸出參數的預存程序
CREATE PROC dbo.usp_GetFailCount
@PassLine INT = 60,
@FailCount INT OUTPUT
AS
BEGIN
SELECT @FailCount = COUNT(*) FROM dbo.Score WHERE Score < @PassLine;
END
GO
-- 呼叫
DECLARE @cnt INT;
EXEC dbo.usp_GetFailCount 60, @cnt OUTPUT;
PRINT @cnt;
觸發器範例：單筆交易超過 2 萬自動回滾
CREATE TRIGGER trg_CheckAmount ON dbo.Bank
FOR UPDATE
AS
BEGIN
DECLARE @before MONEY, @after MONEY;
SELECT @before = Balance FROM deleted;
SELECT @after = Balance FROM inserted;
IF ABS(@after - @before) > 20000
BEGIN
RAISERROR('單筆交易不得超過 20000', 16, 1);
ROLLBACK TRANSACTION;
END
END
八、系統函數速查
| 函數 | 用途 | 範例 |
|---|
| GETDATE() | 目前日期時間 | SELECT GETDATE() |
| DATEADD(interval, n, date) | 日期加減 | DATEADD(month, 4, '2009-01-01') |
| DATEDIFF(interval, d1, d2) | 日期差距 | DATEDIFF(month, '2005-04-20', '2010-08-05') |
| DATENAME(dw, date) | 星期名稱 | DATENAME(dw, '2009-01-15') → Saturday |
| ROUND(val, precision) | 四捨五入 | ROUND(3.14159, 4) → 3.1416 |
| CONVERT(type, expr, style) | 型別轉換 | CONVERT(VARCHAR(10), GETDATE(), 120) |
九、權限與安全性
-- 建立登入與使用者
CREATE LOGIN [app_user] WITH PASSWORD = 'P@ssw0rd';
CREATE USER [app_user] FOR LOGIN [app_user];
-- 授權
GRANT SELECT, INSERT, UPDATE ON dbo.Orders TO [app_user];
-- 刪除約束
ALTER TABLE dbo.Orders DROP CONSTRAINT FK_Orders_Cust;
十、常見實用腳本
- 列出所有資料庫：
EXEC sp_databases;
- 檢視表結構：
EXEC sp_help 'dbo.Student';
- 查詢目前連線數：
SELECT login_name, COUNT(*) FROM sys.dm_exec_sessions GROUP BY login_name;
- 取得當天 00:00:00：
SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0);
