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

完整建庫建表腳本

-- 切換至 master 並啟用進階選項
USE master;
GO
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;

-- 建立資料檔目錄
EXEC xp_cmdshell 'mkdir D:\data', NO_OUTPUT;
GO

-- 刪除舊資料庫（若存在）
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'Study')
    DROP DATABASE Study;
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

USE Study;
GO

-- 建立 Team 表
IF OBJECT_ID('Team', 'U') IS NOT NULL DROP TABLE Team;
CREATE TABLE Team (
    Id       INT IDENTITY(1,1) PRIMARY KEY,
    TName    VARCHAR(20) NOT NULL,
    CaptainId INT
);
GO

-- 建立 Student 表
IF OBJECT_ID('Student', 'U') IS NOT NULL DROP TABLE Student;
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

-- 約束條件
ALTER TABLE Student ADD
    CONSTRAINT CH_Sex CHECK (Sex IN (N'男', N'女')),
    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 (N'請在這裡填寫備註') FOR Remark;
GO

ALTER TABLE Team ADD CONSTRAINT UK_CaptainId UNIQUE (CaptainId);
GO

-- 測試資料
INSERT INTO Team (TName, CaptainId) VALUES
(N'第一組',1), (N'第二組',2), (N'第三組',3), (N'第四組',4), (N'第五組',5);

INSERT INTO Student (Name, Sex, Birthday, Phone, Remark, TId) VALUES
(N'張三', N'男', '1982-06-09', '23456789451', N'來自天津', 1),
(N'李四', N'男', '1987-06-09', '78945678945', N'安徽', 4),
(N'王五', N'男', '1982-06-09', '65987845651', N'大連', 3),
(N'趙六', N'男', '1981-06-09', '25487965423', N'湖南', 5),
(N'江七', N'男', '1984-06-09', '25487965423', DEFAULT, 5);  -- 使用預設備註

-- 驗證查詢
SELECT * FROM Team;
SELECT * FROM Student;
GO

-- 額外範例：Teacher 表與 GROUP BY HAVING
IF OBJECT_ID('Teacher', 'U') IS NOT NULL DROP TABLE Teacher;
CREATE TABLE Teacher (
    Id      INT IDENTITY(1,1) PRIMARY KEY,
    Name    VARCHAR(20),
    Address VARCHAR(20)
);
INSERT INTO Teacher VALUES
('zhang','hubei'),('wang','hubei'),('li','hubei'),
('chen','hunan'),('zhao','hunan'),
('tian','guangdong'),('ma','guangdong'),
('chang','tianjin'),('liang','beijing');

SELECT Address, COUNT(*) AS Cnt
FROM Teacher
GROUP BY Address
HAVING Address <> 'hunan';  -- 過濾群組

-- 還原伺服器設定
EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE;
EXEC sp_configure 'show advanced options', 0; RECONFIGURE;

關鍵語法重點

• FILEGROWTH = 10%：百分比成長適合生產環境，避免頻繁擴充
• MAXSIZE：記錄檔限制最大值防止佔滿磁碟
• AS DATEDIFF(...)：計算欄位不佔儲存空間，查詢時即時運算
• CONSTRAINT ... CHECK：資料完整性在資料庫層強制
• DEFAULT：減少應用層判斷邏輯
• IF OBJECT_ID(...) IS NOT NULL：安全重建模式，適用部署腳本
• xp_cmdshell：需啟用進階選項，生產環境用畢即關閉

部署建議

1. 將腳本存為 .sql 檔，用 sqlcmd -i script.sql 執行
2. 生產環境嚴禁啟用 xp_cmdshell，改用預先建立目錄或檔案群組管理
3. 資料/記錄檔放置於不同磁碟陣列（RAID 10/5）
4. 定期 DBCC CHECKDB 驗證完整性