实验2-数据定义——基本表

1贵州大学计算机科学与技术学院软件工程专业实验报告《数据库原理》实验报告题目数据定义——基本表姓名班级日期实验环境：操作系统MicrosoftWindows7(64位/ServicePack1)CPU(英特尔)Intel(R)Core(TM)i7-4710MQCPU@2.50GHz(2501MHz)内存8.00GB(1600MHz)数据库MicrosoftSQLServer2012实验内容与完成情况：一、实验目的本次实验了解DDL语言的CREATE、DROP、ALTER对表进行操作，学会在SQLServer2012中使用DDL语言进行对表的创建、删除和改动。二、实验内容1.在文本编译窗口编写SQL语句，将下列数据分别插入Student，Course，SC和Teacher表中，操作方法同上。CREATETABLEStudent(SnoVARCHAR(9)PRIMARYKEY,SnameVARCHAR(20)UNIQUE,SsexVARCHAR(2)CHECK(Ssex='男'orSsex='女'),SageSMALLINT,SdeptVARCHAR(20),)2CREATETABLECourse(CnoVARCHAR(4)PRIMARYKEY,CnameVARCHAR(40),CpnoVARCHAR(4),CcreditSMALLINT,FOREIGNKEY(Cno)REFERENCESCourse(Cno))CREATETABLESC(SnoVARCHAR(9),CnoVARCHAR(4),GradeSMALLINTCHECK(Grade=0ANDGrade=100),PRIMARYKEY(SnO,Cno),FOREIGNKEY(Sno,Cno)REFERENCESSC(Sno,Cno)3)CREATETABLETeacher(TnoVarchar(6)PRIMARYKEY,TnameVarchar(20),TsexVARCHAR(2)CHECK(Tsex='男'orTsex='女'),TageINT,TdeptVARCHAR(20),TtitlesVARCHAR(20),TwageINT,TdnoVARCHAR(6),FOREIGNKEY(Tdno)REFERENCESTeacher(Tno),)42.在数据里面插入数据；INSERTINTOStudent(Sno,Sname,Ssex,Sage,Sdept)VALUES('200215121','李勇','男',20,'CS')INSERTINTOStudent(Sno,Sname,Ssex,Sage,Sdept)VALUES('200215122','刘晨','女',19,'CS')INSERTINTOStudent(Sno,Sname,Ssex,Sage,Sdept)VALUES('200215123','王敏','女',18,'MA')INSERTINTOStudent(Sno,Sname,Ssex,Sage,Sdept)VALUES('200215125','张立','男',19,'IS')INSERTINTOStudent(Sno,Sname,Ssex,Sage,Sdept)VALUES('200215126','欧阳丽','女',21,'FL')INSERTINTOCourse(Cno,Cname,Cpno,Ccredit)VALUES('1','数据库',NULL,4)INSERTINTOCourse(Cno,Cname,Cpno,Ccredit)VALUES('2','数学',NULL,2)INSERTINTOCourse(Cno,Cname,Cpno,Ccredit)VALUES('3','信息系统',NULL,4)INSERTINTOCourse(Cno,Cname,Cpno,Ccredit)VALUES('4','操作系统',NULL,3)INSERTINTOCourse(Cno,Cname,Cpno,Ccredit)VALUES('5','数据结构',NULL,4)INSERTINTOCourse(Cno,Cname,Cpno,Ccredit)VALUES('6','数据处理',NULL,2)INSERTINTOCourse(Cno,Cname,Cpno,Ccredit)VALUES('7','PASCAL',NULL,4)INSERTINTOCourse(Cno,Cname,Cpno,Ccredit)VALUES('8','DB_Design',NULL,2)UPDATECourseSETCpno='5'WHERECno='1'5UPDATECourseSETCpno='1'WHERECno='3'UPDATECourseSETCpno='6'WHERECno='4'UPDATECourseSETCpno='7'WHERECno='5'UPDATECourseSETCpno='6'WHERECno='7'UPDATECourseSETCpno='1'WHERECno='8'INSERTSC(Sno,Cno,Grade)VALUES('200215121','1',92)INSERTSC(Sno,Cno,Grade)VALUES('200215121','2',85)INSERTSC(Sno,Cno,Grade)VALUES('200215121','3',88)INSERTSC(Sno,Cno,Grade)VALUES('200215122','2',90)INSERTSC(Sno,Cno,Grade)VALUES('200215122','3',80)INSERTSC(Sno,Cno,Grade)VALUES('200215122','1',NULL)INSERTSC(Sno,Cno,Grade)VALUES('200215123','2',50)INSERTSC(Sno,Cno,Grade)VALUES('200215123','3',70)INSERTTeacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno)VALUES('110001','钟灵','女',27,'CS','讲师',2800,NULL)INSERTTeacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno)VALUES('110002','杨毅','男',42,'CS','副教授',3500,NULL)INSERTTeacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno)VALUES('110003','周倩','女',25,'CS','讲师',2800,NULL)INSERTTeacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno)VALUES('110005','陈文茂','男',48,'CS','教授',4000,NULL)INSERTTeacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno)VALUES('120001','江南','男',30,'IS','副教授',3500,NULL)6INSERTTeacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno)VALUES('120002','刘洋','男',28,'IS','讲师',2800,NULL)INSERTTeacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno)VALUES('120003','汪明','男',44,'IS','教授',4000,NULL)INSERTTeacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno)VALUES('120004','张蕾','女',35,'IS','副教授',3500,NULL)INSERTTeacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno)VALUES('130001','邹佳羽','女',25,'MA','讲师',2800,NULL)INSERTTeacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno)VALUES('130002','王力','男',30,'MA','讲师',2800,NULL)INSERTTeacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno)VALUES('130003','王小峰','男',35,'MA','副教授',3500,NULL)INSERTTeacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno)VALUES('130004','魏昭','男',40,'MA','副教授',3500,NULL)INSERTTeacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno)VALUES('140001','王力','男',32,'FL','副教授',3500,NULL)INSERTTeacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno)VALUES('140002','张小梅','女',27,'FL','讲师',2800,NULL)INSERTTeacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno)VALUES('140003','吴娅','女',27,'FL','讲师',2800,NULL)INSERTTeacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno)VALUES('140004','陈姝','女',35,'FL','副教授',3500,NULL)INSERTTeacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno)VALUES('140005','周斌','男',44,'FL','教授',3500,NULL)UPDATETeacherSETTtitles='教授'WHERETno='140005'UPDATETeacherSETTdno='110005'WHERETno='110001'UPDATETeacherSETTdno='110005'WHERETno='110002'UPDATETeacherSETTdno='110005'WHERETno='110003'7UPDATETeacherSETTdno='110005'WHERETno='110005'UPDATETeacherSETTdno='120003'WHERETno='120001'UPDATETeacherSETTdno='120003'WHERETno='120002'UPDATETeacherSETTdno='120003'WHERETno='120003'UPDATETeacherSETTdno='120003'WHERETno='120004'UPDATETeacherSETTdno='130003'WHERETno='130001'UPDATETeacherSETTdno='130003'WHERETno='130002'UPDATETeacherSETTdno='130003'WHERETno='130003'UPDATETeacherSETTdno='130003'WHERETno='130004'UPDATETeacherSETTdno='140005'WHERETno='140001'UPDATETeacherS