在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ǔ)過程的第一個(gè)參數(shù)為要執(zhí)行的有效dos命令，第二個(gè)參數(shù)為是否輸出返回信息。

go

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

on primary

(

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

fileName='D:dataStudy_data.mdf',--主數(shù)據(jù)文件的物理名

size=10MB,--初始大小

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

)

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自動(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

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('小強(qiáng)','男','1982-6-9','23456789451','來(lái)自河北',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

-- 還原當(dāng)前功能（xp_cmdshell）的配置信息為初始狀態(tài).

RECONFIGURE

GO

EXEC sp_configure 'show advanced options', 0

GO

-- 最后，還原當(dāng)前高級(jí)選項(xiàng)的配置信息為初始狀態(tài)

RECONFIGURE

GO