SQL Server 2005 T-SQL 建立資料庫與資料表完整腳本範例

完整可執行腳本：從建庫、建表、約束、測試資料到清理

1. 環境準備與建立資料庫

USE master;
GO

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'Study')
    DROP DATABASE Study;
GO

-- 啟用 xp_cmdshell 建立資料夾 (需高權限，生產環境建議手動建立)
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;
EXEC xp_cmdshell 'mkdir D:\Data', NO_OUTPUT;
GO

CREATE DATABASE Study
ON PRIMARY (
    NAME = 'Study_data',
    FILENAME = 'D:\Data\Study_data.mdf',
    SIZE = 10MB,
    FILEGROWTH = 10%
)
LOG ON (
    NAME = 'Study_log',
    FILENAME = 'D:\Data\Study_log.ldf',
    SIZE = 1MB,
    MAXSIZE = 20MB,
    FILEGROWTH = 10%
);
GO

2. 建立資料表與核心約束

USE Study;
GO

-- 學生表
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'Student') DROP TABLE Student;
GO
CREATE TABLE Student (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    [Name] VARCHAR(20) NOT NULL,
    Sex CHAR(2) NOT NULL,
    Birthday DATETIME NOT NULL,
    Phone CHAR(11) NOT NULL,
    Remark TEXT,
    TID INT NOT NULL,
    Age AS DATEDIFF(YEAR, Birthday, GETDATE())  -- 計算欄位
);
GO

-- 班級表
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'Team') DROP TABLE Team;
GO
CREATE TABLE Team (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    TName VARCHAR(20) NOT NULL,
    CaptainID INT
);
GO

-- 附加約束 (分離 DDL 利於管理)
ALTER TABLE Student ADD
    CONSTRAINT CH_Sex CHECK (Sex IN ('男','女')),
    CONSTRAINT CH_Birthday CHECK (Birthday BETWEEN '1950-01-01' AND '1988-12-31'),
    CONSTRAINT CH_Phone CHECK (LEN(Phone) = 11),
    CONSTRAINT FK_TID FOREIGN KEY (TID) REFERENCES Team(ID),
    CONSTRAINT DF_Remark DEFAULT ('請在這裡填寫備註') FOR Remark;
GO

ALTER TABLE Team ADD
    CONSTRAINT UK_CaptainID UNIQUE (CaptainID);
GO

3. 測試資料插入

INSERT INTO Team (TName, CaptainID) VALUES
('第一組',1), ('第二組',2), ('第三組',3), ('第四組',4), ('第五組',5);

INSERT INTO Student ([Name], Sex, Birthday, Phone, Remark, TID) VALUES
('小強','男','1982-6-9','23456789451','來自河北',1),
('小昭','男','1987-6-9','78945678945','山東',4),
('小溪','男','1982-6-9','65987845651','撫順',3),
('小憐','男','1981-6-9','25487965423','天津',5),
('李真','男','1984-6-9','25487965423',NULL,5);  -- Remark 用預設值
GO

4. 驗證查詢

SELECT * FROM Team;
SELECT * FROM Student;

5. 額外範例：教師表與 GROUP BY / HAVING

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'Teacher') DROP TABLE Teacher;
GO
CREATE TABLE Teacher (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(20),
    Address VARCHAR(20)
);
GO
INSERT INTO Teacher VALUES
('zhang','hubei'), ('wang','hubei'), ('li','hubei'),
('chen','hunan'), ('zhao','hunan'),
('tian','guangdong'), ('ma','guangdong'),
('chang','tianjin'), ('liang','beijing');

-- 分組統計並過濾非湖南
SELECT COUNT(*), Address FROM Teacher
GROUP BY Address
HAVING Address <> 'hunan';
GO

6. 清理：還原 xp_cmdshell 關閉

EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE;
EXEC sp_configure 'show advanced options', 0; RECONFIGURE;
GO

腳本亮點

• 冪等性： IF EXISTS ... DROP 確保可重複執行不報錯。
• 約束分離： ALTER TABLE ... ADD CONSTRAINT 獨立管理，利於版本控制與啟用/停用。
• 計算欄位： Age AS DATEDIFF(...) 自動衍生，無儲存開銷。
• 預設值約束： DF_Remark 避免應用層硬編碼預設字串。
• 安全收尾： 關閉 xp_cmdshell 還原面積。