USE master;
GO
-- 1. 刪除舊資料庫(存在時)
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'Study')
DROP DATABASE Study;
GO
-- 2. 啟用進階選項與 xp_cmdshell(建立資料夾用)
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;
GO
-- 3. 建立資料檔案目錄
EXEC xp_cmdshell 'mkdir D:\Data', NO_OUTPUT;
GO
-- 4. 建立資料庫(指定檔案群組、初始大小、成長率)
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
-- 5. 建立學生表
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'Student' AND type = 'U')
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 DATE NOT NULL,
phone CHAR(11) NOT NULL,
remark TEXT,
tId INT NOT NULL,
age AS DATEDIFF(YEAR, birthday, GETDATE()) -- 計算欄位
);
GO
-- 6. 建立組別表
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'Team' AND type = 'U')
DROP TABLE Team;
GO
CREATE TABLE Team (
id INT IDENTITY(1,1) PRIMARY KEY,
tName VARCHAR(20) NOT NULL,
captainId INT
);
GO
-- 7. 加入約束
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
-- 8. 插入測試資料
INSERT INTO Team (tName, captainId) VALUES
('第一組',1), ('第二組',2), ('第三組',3), ('第四組',4), ('第五組',5);
INSERT INTO Student ([name], sex, birthday, phone, remark, tId) VALUES
('小強','男','1982-06-09','23456789451','來自河北',1),
('小昭','男','1987-06-09','78945678945','山東',4),
('小溪','男','1982-06-09','65987845651','撫順',3),
('小憐','男','1981-06-09','25487965423','天津',5),
('李真','男','1984-06-09','25487965423',5); -- 使用預設 remark
-- 9. 驗證查詢
SELECT * FROM Team;
SELECT * FROM Student;
GO
IDENTITY(1,1):自動遞增種子 1、遞增 1AS DATEDIFF(...):計算欄位,不佔儲存空間,查詢時即時計算CHECK:資料完整性約束(域完整性)FOREIGN KEY ... REFERENCES:參考完整性DEFAULT:缺值時自動填入UNIQUE:候選鍵,允許一個 NULL| 檢視 | 用途 |
|---|---|
| sys.databases | 資料庫清單 |
| sys.objects | 資料庫物件(type='U' 為使用者資料表) |
| sys.columns | 欄位定義 |
| sys.indexes | 索引資訊 |
| sys.foreign_keys | 外鍵關係 |
此腳本涵蓋**資料庫全生命週期建立**:刪重、檔案配置、表結構、約束、測試資料、驗證。可作為專案初始化腳本模板,納入版控自動化部署。