在sql server2005中用語(yǔ)句創(chuàng)建數(shù)據(jù)庫(kù)和表：

具體示例如下：

use master

go

if exists (select * from sysdatabases where name='study')

--判斷study數(shù)據(jù)庫(kù)是否存在，如果是就進(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ǔ)過(guò)程地第一個(gè)參數(shù)為要執(zhí)行地有效dos命令，第二個(gè)參數(shù)為是否輸出返回信息.

go

create database study--創(chuàng)建數(shù)據(jù)庫(kù)

on primary

(

name='study_data',--主數(shù)據(jù)文件地邏輯名

filename='d:\data\study_data.mdf',--主數(shù)據(jù)文件地物理名

size=10mb,--初始大小

filegrowth=10% --增長(zhǎng)率

)

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)谶@里填寫(xiě)備注') for remark--默認(rèn)約束，

更多信息請(qǐng)查看IT技術(shù)專欄