具體示例如下:
use master
go
if exists (select * from sysdatabases where name='study')
--判斷study數(shù)據(jù)庫是否存在,如果是就進(jìn)行刪除
drop database study
go
exec sp_configure 'show advanced options', 1
go
-- 更新當(dāng)前高級(jí)選項(xiàng)地配置信息
reconfigure
go
exec sp_configure 'xp_cmdshell', 1
go
-- 更新當(dāng)前功能(xp_cmdshell)地配置信息.
reconfigure
go
exec xp_cmdshell 'mkdir d:\data', no_output
--利用xp_cmdshell 命令創(chuàng)建文件夾,此存儲(chǔ)過程地第一個(gè)參數(shù)為要執(zhí)行地有效dos命令,第二個(gè)參數(shù)為是否輸出返回信息.
go
create database study--創(chuàng)建數(shù)據(jù)庫
on primary
(
name='study_data',--主數(shù)據(jù)文件地邏輯名
filename='d:\data\study_data.mdf',--主數(shù)據(jù)文件地物理名
size=10mb,--初始大小
filegrowth=10% --增長率
)
log on
(
name='study_log',--日志文件地邏輯名
filename='d:\data\study_data.ldf',--日志文件地物理名
size=1mb,
maxsize=20mb,--最大大小
filegrowth=10%
)
go
use study
go
if exists (select * from sysobjects where name='student')--判斷是否存在此表
drop table student
go
create table student
(
id int identity(1,1) primary key,--id自動(dòng)編號(hào),并設(shè)為主鍵
[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(yyyy,birthday,getdate())--計(jì)算列.
)
go
if exists (select * from sysobjects 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
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),--外鍵約束,引用team表地主鍵
constraint df_remark default('請(qǐng)?jiān)谶@里填寫備注') for remark--默認(rèn)約束,
go
更多信息請(qǐng)查看IT技術(shù)專欄