新SQLSERVER实验练习答案.docx

1、新SQLSERVER实验练习答案SQL-Server实验答案上海师范大学 计算机系 第一部分 企业管理器的使用第二部分 SQL语言试验一 数据库创建目的：1掌握利用SQL语言进行数据库的创建、维护。 2 sp_helpdb 命令要求：1 创建数据库 2 修改数据库 3 删除数据库一 建立school 数据库1 使用查询分析器创建数据库 school Create DataBase school2 使用 SP_helpdb 查询数据库 School 的信息3 使用SQL-Server 的企业管理器查看数据库 school 的信息。 4 记录：1）school 数据库文件所在的文件夹。 2）sch

2、ool 数据库的文件名二 删除School数据库1 使用查询分析器删除数据库 school DROP DATABASE school2 使用SQL-Server 的企业管理器删除数据库 school 。 三 create Database 深入研究1 建立school数据库,要求数据库存储在c:data文件夹下，初始大小为5MB ,增量为 1MB。 CREATE DATABASE school ON( Name = school_dat, Filename = c:sqldataschool.mdf,SIZE = 5, FILEGROWTH = 1 )2使用SQL-Server 的企业管理器，

3、将数据库的每次增量改为20%。试验二 创建表目的：1 掌握利用SQL语言创建表的方法。 2 sp_help 命令要求：1 创建表 2 修改表结构 3 删除表一 写出使用 Create Table 语句创建表 student , sc,course 的SQL语句。学生表、课程表、选课表属于数据库 School ，其各自得数据结构如下：学生 Student (Sno,Sname,Ssex,Sage,Sdept)序号列名含义数据类型长度1Sno学号字符型(char)62Sname姓名字符型(varchar)83Ssex性别字符型(char)24Sage年龄整数 (smallint)5sdept系科字

4、符型(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 。create table Student ( Sno char(6) ,

5、 Sname char(10) , Ssex char(2) , Sage smallint , Sdept char(10) ,) create table course( Cno char(4) , Cname char(16) , Cpno char(4) , Ccredit int,)create table SC( Sno char(6), Cno char(4) , Grade int )三 使用 SP_HELP 查看表 student 的表结构 利用企业管理器查看表 sc 的表结构四 利用 sql 语句表结构修改1 在student 表中添加列：家庭地址 address 长度为

6、60 varchar 型入学日期 inDate 日期型 ALTER TABLE student ADD address varchar(60) ALTER TABLE student ADD inDate datetime完成后用sp_help 查看是否成功。2 将家庭地址 address 长度为 50 ALTER TABLE student ALTER COLUMN varchar(50) 完成后用sp_help 查看是否成功。3 删除 student 表的 inDate 列 ALTER TABLE student DROP COLUMN inDate五 删除表 1 删除表 sc 2 删除表

7、 student 3 删除表 course试验三 创建数据完整性目的：1掌握创建数据完整性约束的命令。 2 掌握完整性约束的修改、删除。要求：1 能建立完整性约束 2 修改完整性约束 3 删除完整性约束一 写出带有完整性约束的 Create Table 命令建立表student、course、sc 。要求：1 Student表的主码：sno student 的约束： 姓名不可为空，且唯一性别 不能为空且取值范围为男，女年龄大于16岁sdept 默认为 JSJ 系 2Course表的主码：cno course 的约束： Ccredit 取值范围 0 ,1,2,3,4,5 课程表的每一行的 Cno

8、 与 cpno 不可相同3 Sc表的主码：sno，cno 。主码名为 PK_SCSc的外码：外码：SC 表的sno 参照表 student 的 sno外码：sc 表的Cno 参照表 course 的 cno 4 把上述创建表的sql 语句的脚本存储到文件 createSchool.sql 。create table Student ( Sno char(6) , Sname char(10) not null unique , Ssex char(2) check (ssex=男 or ssex=女) , Sage smallint check(sage16) , Sdept char(10)

9、 not null default JSJ , primary key (sno) ) create table course( Cno char(4) , Cname char(16) , Cpno char(4) , Ccredit int check (Ccredit =0 and Ccredit=5), check( cnocpno) , -约束 primary key (cno) )create table SC( Sno char(6), Cno char(4) , Grade int check(grade=0 and grade163006学生不能输入，性别不对。select

10、* from student 查看你输入了那些数据。2 course 表数据的输入CnoCnameCpnoCcredit1085C+91086语文10863输入上述数据，记录出现的问题，说明原因。1086 不能输入，因为有约束 check(cnocpno) select * from student 查看你输入了那些数据。3 SC 表数据的输入SnoCnoGrade30021081128输入上述数据，记录出现的问题，说明原因。3002 这条数据不能输入，因为 grade不能大于 100分select * from student 查看你输入了那些数据。三 参照完整性约束掌握表之间建立外码后，对

11、被参照表的如下操作会有何影响：修改主码、插入新行、删除新行？对参照表添加新行、删除行、修改外码值有何影响？掌握级联修改、级联删除的概念。注意：表SC的 Sno是外码，参照student的sno。表SC的 Cno是外码，参照course的cno。1 输入实验前的数据学生表 StudentSnoSnameSsexSageSdept4001赵尹男20SX4002杨开女20JSJ课程表 course CnoCnameCpnoCcredit1088Java51089数学3学生选课 SC SnoCnoGrade400110889040021088862 试验过程 1) 在SC表中添加新行：SnoCnoGr

12、ade4001106676 记录试验结果.,写出出现此结果的原因. 不能添加，因为在 cno是外码，参照course的 cno , 但在course 中没有 1066课程。2) 在student表中添加新行 SnoSnameSsexSageSdept4003赵辉男21SX 记录试验结果.,写出出现此结果的原因.可以输入3) 删除student 表的 4001 ，4002学生 记录试验结果.,写出出现此结果的原因. 两个学生不能被删除，因为sc的外码 sno 参照student的sno, sc中已经有4001，4002学生的数据，因此不能删除。思考：删除SC表的记录有限制吗？没有 采取什么技术能

13、使不能成功执行的命令变得可以执行，且使数据库保持数据完整性。 级联删除4) 把 student 表的学号 4003 改为 4018 , 4001改为4021。 记录试验结果.,写出出现此结果的原因.4003 可以改为 4018, 4001不能改为 4021 因为sc的外码 sno 参照student的sno, sc中已经有4001的数据，但没有 4003的选课数据。 思考：采取什么技术能使本题不能执行的命令可以执行，且使数据库保持数据完整性。 级联修改5) 把sc表中的如下记录的学号从4001改为4011。SnoCnoGrade4001108890 记录试验结果.,写出出现此结果的原因.不能修

14、改，因为sc的外码 sno 参照student的sno, 4011在 student中不存在。如不成功，则可以采取什么方法来实现此要求。 需要在student表中添加 4011学生。如不成功，那么把4001修改为4003，能成功吗？能成功！思考: 参照完整性规则中,外码可以为空, 但SC表中的外码可以为空吗?为什么?举一个外码可以为空的例子。不可以，因为 sc表的主码为 sno+cno, 即sno,cno为主属性，所以不能为空。试验五 索引目的：掌握索引的建立、删除的方法。一 创建索引1 建 student 的索引为姓名建立索引，索引名：Ix_student_sname为系科建立索引，索引名：

15、Ix_student_sdeptcreate index ix_student_sname ON student(sname)create index ix_student_sdept ON student(sdept)2 SC 的索引为课程号建立索引： ix_sc_cno create index ix_sc_cno ON sc(cno)3 Course 的索引为课程名建立唯一性索引 ：Ix_course_cname create unique index ix_course_cname ON course( cname)4 如何 SP_HELP 查看索引刚才建立的索引？ 如何在企业管理器中

16、查看索引？ 二 删除索引 course 表的索引 IX_course_cname DROP INDEX course.ix_course_cname三 思考：如何把索引 IX_student_sname 修改为唯一性索引？ 可以使用企业管理器 或先删除索引，再重新建立。 *四 思考建立索引的目的1 输入下列存储过程，该程序生成大量数据供测试：create procedure usp_makedata asdeclare nCnt int , sNo varchar(6) , sname varchar(8)set nCnt =12000 -计数器 while nCnt999999begin s

17、et nCnt = nCnt + 1 set sNo = convert(varchar(6) ,nCnt) set sName = 张+sno insert into student (sno,sname,ssex,sage) values ( sno,sname,男,20)endreturn 2 exec usp_makedata -生成测试数据 3 输入下述测试程序：create procedure usp_test as declare nCount int ,data intset nCount=0while nCount100begin select data=count(*) f

18、rom student where sname 张8800 set nCount =nCount + 1end 4 测试1）建立姓名的索引，查看运行时间(8秒). create index ix_student_sname on student(sname) -建立索引 exec usp_test2) 删除姓名索引，查看运行时间（2分11秒），比较与1）的时间长短。drop index student.ix_student_sname -删除索引 exec usp_test试验六 更新数据目的：掌握insert,update ,delete 语句的使用。一 insert 1 写出把下述学生的信

19、息添加到student表中的命令。 学号姓名性别年龄系科4001赵茵男20SX4002杨华女21Insert into student (sno,sname,ssex,sage,sdept) values (4001 ,赵茵,男,20, SX)Insert into student (sno,sname,ssex,sage) values (4002 ,杨华,女,21)2 批量插入数据1） 建立一个新表 sc_name ，有属性 sno , sname , ssex , cno , grade 。 CREATE TABLE sc_name (Sno char(6) , Sname varcha

20、r(20),Ssex char(2) ,cno char(4) ,grade int )2） 把 SX 系学生的sno,sname,ssex, cno , grade 插入到表 sc_name 中。 Insert into sc_name (sno,sname,ssex,cno , grade) select student.sno,sname , ssex,cno,grade from student,sc where student.sno=sc.sno and sdept=SX 3） 察看 sc_name 表的数据 select * from sc_name二 Update1 修改 00

21、01 学生的系科为: JSJUpdate student set sdept=JSJ where sno=00012 把陈小明的年龄加1岁，性别改为女。Update student set sage=sage+1 , ssex=女 where sname= 陈小明3 修改李文庆的1001课程的成绩为 93 分update sc set grade=93 where cno=1001 and sno in ( select sno from student where sname= 李文庆)4 把“数据库原理”课的成绩减去1分 update sc set grade=grade - 1 wher

22、e cno in ( select cno from course where cname=数据库原理 )三 Delete1 删除所有 JSJ 系的男生 delete from student where sdept=JSJ2 删除“数据库原理”的课的选课纪录 Delete from sc where cno in (select cno from course where cname=数据库原理 ) 思考：修改数据的命令与修改表结构的命令有何区别？ 试验七 Sql 查询语句目的: 掌握 Select 查询语句。一 单表1查询年龄在19至21岁之间的女生的学号,姓名,年龄,按年龄从大到小排列。

23、select sno,sname,sage from student where sage between 19 and 21 and ssex=女 order by sage desc2查询姓名中第戎2个字为“明”字的学生学号、性别。select sname ,ssex from student where sname like _明%3查询 1001课程没有成绩的学生学号、课程号select sno,cno from sc where grade is null and cno=10014查询JSJ 、SX、WL 系的学生学号,姓名，结果按系及学号排列select sno,sname from student where sdept in (JSJ,SX,WL)order by sdept,sno5按10分制查询学生的sno,cno,10分制成绩