sql2008试验2.docVIP

试验2 use master go --(1)创建数据库 if DB_ID(s_student)is not null drop database s_student create database s_student on ( name=s_student, filename=e:\sqldb2005\s_student.mdf, size=5, maxsize=15, filegrowth=5 ) log on ( name=s_studentlog, filename = e:\sqldb2005\s_student.ldf, size=5, maxsize=15, filegrowth=5 ) Go --(2)创建表s,c,sc use s_student go if OBJECT_ID(S)IS NOT NULL drop table s if OBJECT_ID(SC)IS NOT NULL drop table sc if OBJECT_ID(c)IS NOT NULL drop table c go create table s ( sno char(6) primary key, sn char(20), sa smallint check (sa=12 and sa=50) ) create table c ( cno char(4) primary key, cn char(30), pcno char(4) ) create table sc ( sno char(6)not null, cno char(4)not null, g smallint check (g in(1,2,3,4,5)), primary key(sno,cno), foreign key (sno) references s(sno), foreign key (cno) references c(cno) ) go alter table sc add constraint fk_sc_cno foreign key (cno) references c(cno) --自行另外添加约束constraint go -- 验证用户数据表确实建立 SELECT name FROM sys.objects WHERE type = U; GO --(3)添加项删除项 alter table s add sd char(8) select * from s go alter table c add teacher char(8) select * from c alter table C drop column teacher go --column列 select * from c --(4)创建索引 if exists (select * from dbo.sysindexes where name=s_s) drop index s_s on s if exists (select * from dbo.sysindexes where name=s_c) drop index s_c on c if exists (select * from dbo.sysindexes where name=s_sc) drop index s_sc on sc create unique index s_s on s(sno) create unique index s_c on c(cno) create unique index s_sc on sc(sno,cno) go --(5)约束 sp_help sc --查看约束 select name from sys.objects where type=c --删除约束 alter table sc drop constraint CK__sc__g__0AD2A005 select name from sys.objects where type=c --添加约束 alter table sc add check (g between 1 and 5) --(6)图形界面操作。 --（）加入数据 insert into s values(990104,shanwang,20,cs) -- insert into c(cno,cn) values(c135,math) -- insert into sc values (990104,c135,4)