新SQL——SERVER实验练习答案

SQL-Server实验答案上海师范大学计算机系目录第一部分企业管理器的使用3试验一注册服务器3试验二创建数据库3试验三创建表4实验四数据输入5实验五登录到数据库服务器6第二部分SQL语言7第二部分SQL语言7试验一数据库创建7试验二创建表7试验三创建数据完整性8试验四数据完整性试验9试验五索引12试验六更新数据13试验七Sql查询语句13试验八视图15试验九安全性控制实验15试验十存储过程16试验十二触发器17试验十二恢复技术19试验十三事务20试验十四锁21第一部分企业管理器的使用第二部分SQL语言试验一数据库创建目的：1掌握利用SQL语言进行数据库的创建、维护。2sp_helpdb命令要求：1创建数据库2修改数据库3删除数据库一建立school数据库1使用查询分析器创建数据库schoolCreateDataBaseschool2使用SP_helpdb查询数据库School的信息3使用SQL-Server的企业管理器查看数据库school的信息。4记录：1）school数据库文件所在的文件夹。2）school数据库的文件名二删除School数据库1使用查询分析器删除数据库schoolDROPDATABASEschool2使用SQL-Server的企业管理器删除数据库school。三createDatabase深入研究1建立school数据库,要求数据库存储在c:\data文件夹下，初始大小为5MB,增量为1MB。CREATEDATABASEschoolON(Name=‘school_dat’,Filename='c:\sqldata\school.mdf',SIZE=5,FILEGROWTH=1)2使用SQL-Server的企业管理器，将数据库的每次增量改为20%。试验二创建表目的：1掌握利用SQL语言创建表的方法。2sp_help命令要求：1创建表2修改表结构3删除表一写出使用CreateTable语句创建表student,sc,course的SQL语句。学生表、课程表、选课表属于数据库School，其各自得数据结构如下：学生Student(Sno,Sname,Ssex,Sage,Sdept)序号列名含义数据类型长度1Sno学号字符型(char)62Sname姓名字符型(varchar)83Ssex性别字符型(char)24Sage年龄整数(smallint)5sdept系科字符型(varchar)15课程表course(Cno,Cname,Cpno,Ccredit)序号列名含义数据类型长度1Cno课程号字符型(char)42cname课程名字符型(varchar)203Cpno先修课字符型(char)44Ccredit学分短整数(tinyint)学生选课SC(Sno,Cno,Grade)序号列名含义数据类型长度1Sno学号字符型(char)42Cno课程名字符型(char)63Grade成绩小数(decimal)12,1二把创建表的sql语句的脚本存储到文件school.sql。createtableStudent(Snochar(6),Snamechar(10),Ssexchar(2),Sagesmallint,Sdeptchar(10),)createtablecourse(Cnochar(4),Cnamechar(16),Cpnochar(4),Ccreditint,)createtableSC(Snochar(6),Cnochar(4),Gradeint)三使用SP_HELP查看表student的表结构利用企业管理器查看表sc的表结构四利用sql语句表结构修改1在student表中添加列：家庭地址address长度为60varchar型入学日期inDate日期型ALTERTABLEstudentADDaddressvarchar(60)ALTERTABLEstudentADDinDatedatetime完成后用sp_help查看是否成功。2将家庭地址address长度为50ALTERTABLEstudentALTERCOLUMNvarchar(50)完成后用sp_help查看是否成功。3删除student表的inDate列ALTERTABLEstudentDROPCOLUMNinDate五删除表1删除表sc2删除表student3删除表course试验三创建数据完整性目的：1掌握创建数据完整性约束的命令。2掌握完整性约束的修改、删除。要求：1能建立完整性约束2修改完整性约束3删除完整性约束一写出带有完整性约束的CreateTable命令建立表student、course、sc。要求：1Student表的主码：snostudent的约束：姓名不可为空，且唯一性别不能为空且取值范围为{男，女}年龄大于16岁sdept默认为‘JSJ’系2Course表的主码：cnocourse的约束：Ccredit取值范围{0,1,2,3,4,5}课程表的每一行的Cno与cpno不可相同3Sc表的主码：sno，cno。主码名为PK_SCSc的外码：外码：SC表的sno参照表student的sno外码：sc表的Cno参照表course的cno4把上述创建表的sql语句的脚本存储到文件createSchool.sql。createtableStudent(Snochar(6),Snamechar(10)notnullunique,Ssexchar(2)check(ssex='男'orssex='女'),Sagesmallintcheck(sage16),Sdeptchar(10)notnulldefault'JSJ',primarykey(sno))createtablecourse(Cnochar(4),Cnamechar(16),Cpnochar(4),Ccreditintcheck(Ccredit=0andCcredit=5),check(cnocpno),--约束primarykey(cno))createtableSC(Snochar(6),Cnochar(4),Gradeintcheck(grade=100),constraintpk_scprimarykey(sno,cno),foreignkey(sno)referencesstudent(sno),foreignkey(cno)referencescourse(cno),)二使用SP_HELP查看表student的主码名，约束名，并记录。使用SP_HELP查看表sc的主码名，外码名，并记录。三利用altertable添加、删除完整性约束1删除SC的主码，sc表的主码名为pk_scALTERTABLEscDROPpk_sc复习在Sql-Server企业管理器中如何完成。2删除SC表参照course表的外码。如何知道SC表参照course表的外码的名字。1)SP_HELPcourse找到外码名,假设为：fk_sc_cno_029382)ALTERTABLEscDROPfk_sc_cno_029383添加SC表的主码。主码名为PK_SCALTERTABLEscADDCONSTRAINTpk_scPRIMARYKEY(sno,cno)4添加SC表的Cno的外码，参照表Course的Cno.ALTERTABLEscADDFOREIGNKEY(cno)REFERENCEScourse(cno)5加自定义约束：表SC的成绩只能在0–100分之间。ALTERTABLEscADDcheck(grade=0andgrade=100)四使用Sql-Server企业管理器完成：1删除SC表参照course表的外码。2建立SC表的Cno的外码，参照表Course的Cno.*使该外码具有级联修改的功能。3删除表SC的成绩只能在0–100分之间的约束.3加自定义约束：表SC的成绩只能在0–100分之间。五使用select*fromstudent查看信息select*fromcourse查看信息select*fromsc查看信息试验四数据完整性试验目的：1理解实体完整性、参照完整性、用户自定义完整性的作用2特别掌握外码的作用。要求：记录试验中遇到的问题，并写出原因。实验前需要利用试验三完成的脚本文件createSchool.sql，重新建立数据库school。一实体完整性1student表数据输入学号姓名性别年龄系科3001赵达男20SX3002杨丽女21JSJ3001李寅女21SX输入上述数据，记录出现的问题，说明原因。第三行不能输入。Student的主码为sno,因此sno列的值不能重复。select*fromstudent查看你输入了几行数据。2course表数据的输入CnoCnameCpnoCcredit1081电子商务43SC表数据的输入SnoCnoGrade30011081903001108179输入上述数据，记录出现的问题，说明原因。二用户自定义完整性约束表student有用户自定义约束：性别不能为空且取值范围为{男，女}年龄大于16岁表course的自定义约束：Ccredit取值范围{0,1,2,3,4,5}课程表的每一行的Cno与cpno不可相同1student表数据输入学号姓名性别年龄系科3005赵达男14SX3006杨丽南21JSJ输入上述数据，记录出现的问题，说明原因。3005学生不能输入，年龄问题，有约束sage163006学生不能输入，性别不对。select*fromstudent查看你输入了那些数据。2course表数据的输入CnoCnameCpnoCcredit1085C++91086语文10863输入上述数据，记录出现的问题，说明原因。1086不能输入，因为有约束check(cnocpno)select*fromstudent查看你输入了那些数据。3SC表数据的输入SnoCnoGrade30021081128输入上述数据，记录出现的问题，说明原因。3002这条数据不能输入，因为grade不能大于100分select*fromstudent查看你输入了那些数据。三参照完整性约束掌握表之间建立外码后，对被参照表的如下操作会有何影响：修改主码、插入新行、删除新行？对参照表添加新行、删除行、修改外码值有何影响？掌握级联修改、级联删除的概念。注意：表SC的Sno是外码，参照student的sno。表SC的Cno是外码，参照course的cno。1输入实验前的数据学生表StudentSnoSnameSsexSageSdept4001赵尹男20SX4002杨开女20JSJ课程表courseCnoCnameCpnoCcredit1088Java51089数学3学生选课SCSnoCnoGrade400110889040021088862试验过程1)在SC表中添加新行：SnoCnoGrade4001106676记录试验结果.,写出出现此结果的原因.不能添加，因为在cno是外码，参照course的cno,但在course中没有1066课程。2)在student表中添加新行SnoSnameSsexSageSdept4003赵辉男21SX记录试验结果.,写出出现此结果的原因.可以输入3)删除student表的4001，4002学生记录试验结果.,写出出现此结果的原因.两个学生不能被删除，因为sc的外码sno参照student的sno,sc中已经有4001，4002学生的数据，因此不能删除。思考：删除SC表的记录有限制吗？没有采取什么技术能使不能成功执行的命令变得可以执行，且使数据库保持数据完整性。级联删除4)把student表的学号4003改为4018,4001改为4021。记录试验结果.,写出出现此结果的原因.4003可以改为4018,4001不能改为4021因为sc的外码sno参照student的sno,sc中已经有4001的数据，但没有4003的选课数据。思考：采取什么技术能使本题不能执行的命令可以执行，且使数据库保持数据完整性。级联修改5)把sc