在SQL Server2005中用語句創建數據庫和表：

具體示例如下：

use master

go

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

--判斷Study數據庫是否存在，如果是就進行刪除

drop database Study

go

EXEC sp_configure 'show advanced options', 1

GO

-- 更新當前高級選項的配置信息

RECONFIGURE

GO

EXEC sp_configure 'xp_cmdshell', 1

GO

-- 更新當前功能（xp_cmdshell）的配置信息。

RECONFIGURE

GO

exec xp_cmdshell 'mkdir D:data', NO_OUTPUT

--利用xp_cmdshell 命令創建文件夾，此存儲過程的第一個參數為要執行的有效dos命令，第二個參數為是否輸出返回信息。

go

create database Study--創建數據庫

on primary

(

name='Study_data',--主數據文件的邏輯名

fileName='D:dataStudy_data.mdf',--主數據文件的物理名

size=10MB,--初始大小

filegrowth=10% --增長率

)

log on

(

name='Study_log',--日志文件的邏輯名

fileName='D:dataStudy_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自動編號，并設為主鍵

[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())--計算列。

)

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('請在這里填寫備注') for remark--默認約束，

go

alter table Team

add

constraint UK_captainId unique(captainId)--唯一約束

go

insert into Team values('第一組',1)

insert into Team values('第二組',2)

insert into Team values('第三組',3)

insert into Team values('第四組',4)

insert into Team values('第五組',5)

insert into Student values('小強','男','1982-6-9','23456789451','來自河北',1)

insert into Student values('小昭','男','1987-6-9','78945678945','山東',4)

insert into Student values('小溪','男','1982-6-9','65987845651','撫順',3)

insert into Student values('小憐','男','1981-6-9','25487965423','天津',5)

insert into Student(name,sex,birthday,phone,tId) values('李真','男','1984-6-9','25487965423',5)

select * from Team

select * from Student

if exists (select * from sysobjects 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')

insert into teacher values('wang','hubei')

insert into teacher values('li','hubei')

insert into teacher values('chen','hunan')

insert into teacher values('zhao','hunan')

insert into teacher values('tian','guangdong')

insert into teacher values('ma','guangdong')

insert into teacher values('chang','tianjin')

insert into teacher values('liang','beijing')

select * from teacher

select count(*),address from teacher group by address having address<>'hunan'

--按地址分組查詢并用having字句篩選出地址不是‘hunan’的

EXEC sp_configure 'xp_cmdshell', 0

GO

-- 還原當前功能（xp_cmdshell）的配置信息為初始狀態.

RECONFIGURE

GO

EXEC sp_configure 'show advanced options', 0

GO

-- 最后，還原當前高級選項的配置信息為初始狀態

RECONFIGURE

GO