sql2008试验2.doc

试验2use mastergo-(1)创建数据库if DB_ID(s_student)is not nulldrop database s_studentcreate database s_studenton (name=s_student,filename=e:sqldb2005s_student.mdf,size=5,maxsize=15,filegrowth=5)log on(name=s_studentlog,filename = e:sqldb2005s_student.ldf,size=5,maxsize=15,filegrowth=5)Go-(2)创建表s,c,scuse s_studentgoif OBJECT_ID(S)IS NOT NULLdrop table sif OBJECT_ID(SC)IS NOT NULLdrop table scif OBJECT_ID(c)IS NOT NULLdrop table cgocreate 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)goalter table scadd constraint fk_sc_cno foreign key (cno) references c(cno)-自行另外添加约束constraintgo- 验证用户数据表确实建立SELECT name FROM sys.objectsWHERE type = U;GO-(3)添加项删除项alter table sadd sd char(8)select *from sgoalter table cadd teacher char(8)select *from calter table Cdrop column teachergo -column列select *from c-(4)创建索引if exists (select * from dbo.sysindexes where name=s_s)drop index s_s on sif exists (select * from dbo.sysindexes where name=s_c)drop index s_c on cif exists (select * from dbo.sysindexes where name=s_sc)drop index s_sc on sccreate 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_0AD2A005select name from sys.objects where type=c-添加约束alter table sc add check (g between 1 and 5)-(6)图形界面操作。-（）加入数据insert into svalues(990104,shanwa