在SQL Server2005中用语句创建数据库和表

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