数据更新与简单的数据查询实验答案.doc

实验5 数据更新与简单的数据查询（2学时）三、实验内容与实验步骤如果上节课使用分离数据库的方式备份数据，那么只需要进行附加数据库操作即可，附加后直接跳到（二）。如果使用的是“导出数据”备份的，请执行（一）中的操作。（一）建立数据库studentsdb，并导入数据表（数据表请继续保留到下一次实验）1、建立数据库studentsdb2、找到上节课进行了备份的三个数据文件grade.txt,student_info.txt, curriculum.txt3、在企业管理器中点击数据库studnetsdb的表对象，在“所有任务”中选择“导入数据”。4、在选择数据源对话框中，修改“数据源”的选项为“文本文件”，并选中你要导入的txt文件名，如grade.txt。5、在“选择文件格式”对话框中，选中复选框“第一列含有列名称”。6、其它步骤默认选项，在“选择目的”对话框中，确认要将数据导入到数据库studentsdb中。导入数据成功后，刷新数据库，便可在studentsdb数据库中找到刚导入的数据表grade，student_info和 curriculum。7、点击查看新导入的三个表的结构，会发现各个表的各个属性的数据类型与“实验3”中所建立的表的设定不一致，请按照“实验3”中各个表的数据类型将新导入的三个表进行修改。8、如果上节课没有保存有数据表，请重新建立相关表再进行下面的实验。（二）在studentsdb数据库中使用SELECT语句进行基本查询。请将查询语句填写在实验思考栏中。（三）使用SELECT语句进行条件查询。请将语句填写在实验思考栏中。(四)在studentsdb数据库中,使用下列SQL语句将输出什么? 1.SELECT COUNT(*) FROM grade2.SELECT SUBESTRING(姓名,1,2) FROM student_info SELECT UPPER(kelly)SELECT Replicate(kelly,3)SELECT SQRT(分数) FROM grade WHERE 分数=85SELECT 2,3,POWER(2,3)SELECT YEAR(GETDATE),MONTH(GETDATE(),DAY(GETDATE() （五）数据更新。（1）创建totalgrade表，具有数据列：学号、姓名、总成绩。CREATE TABLE totalgrade(学号 char(4) NOT NULL,姓名 varchar(8) NULL,总成绩decimal(5,2) NULL)（2）使用INSERT INTO语句通过student_info表更新totalgrade表的学号、姓名列数据。INSERT INTO totalgrade SELECT 学号，姓名，总成绩=0 FROM student_info（3）使用UPDATE语句通过grade表更新totalgrade表的总成绩列数据，使totalgrade表中每个学生的总成绩为grade表中该学生各成绩之和。UPDATE totalgrade SET 总成绩=（SELECT SUM（分数） FROM grade WHERE totalgrade.学号=grade.学号）（4）删除totalgrade表中没有总成绩的学生记录。四、实验思考1、将实验内容中（二）的查询语句填写在下面。2、将实验内容中（三）的查询语句填写在下面。3、将实验内容中（四）的结果填写在下面，或者说明各个语句的主要作用。参考答案3. 在student_info表和grade表之间实现交叉连接。select student_info.学号,姓名,课程编号,分数from student_info CROSS JOIN grade4查询所有男同学的选课情况，要求列出学生学号、姓名、课程名称和分数。select student_info.学号,姓名,性别,课程名称,分数from student_infoinner join grade on student_info.学号 =grade.学号inner join curriculum on grade.课程编号=curriculum.课程编号where 性别=男5查询每个学生的所选课程的最高成绩，要求列出学生学号、姓名、课程编号和分数。select student_info.学号,姓名,grade.课程编号,分数from student_infoinner join grade on student_info.学号 =grade.学号where 分数 in (select max(分数) from grade group by grade.学号 having grade.学号=student_info.学号 ) 比较与课堂上给出的两个语句，验证结果是否正确。 l select a.学号,student_info.姓名,a.课程编号,a.分数 from grade a,student_info where a.分数=(select max(b.分数) from grade b where a.学号=b.学号)and student_info.学号=a.学号-这个语句首先使用嵌套查询通过成绩自身的连接得出每个同学的最高分，并将这个-最高分作为连接查询的筛选条件之一。l select 学号,max(分数) as 最高分 into #grade from grade group by 学号 -临时表保存分组查询得到的每个学号的最高分数。Select student_info.学号,student_info.姓名,grade.课程编号,#grade.最高分from student_info inner join #grade on student_info.学号=#grade.学号 inner join grade on #grade.学号=grade.学号 and #grade.最高分=grade.分数-使用分组查询得到的结果与学生表，成绩表进行连接，得到学生的姓名和课程编号以及学号、最高分。l select distinct a.学号,姓名,a.课程编号,a.分数 from grade a inner join student_info on a.学号=student_info.学号 where a.分数=(select max(分数) from grade where a.学号=学号)l select distinct a.学号,student_info.姓名,a.课程编号,a.分数 from grade a inner join student_info on a.学号=student_info.学号 where a.分数=(select max(grade.分数) from grade group by grade.学号 having student_info.学号=grade.学号)6查询所有学生的总成绩，要求列出学生学号、姓名、总成绩，没有选修课程的学生的总成绩为空。select student_info.学号,student_info.姓名,a.分数 into #gradefrom student_infoleft join grade aon student_info.学号 = a.学号select 学号,姓名,sum(分数) as 总成绩from #gradegroup by 学号,姓名方法2：select s.学号,s.姓名,sum(g.分数) as 总分from student_info AS sleft join grade AS gon g.学号=s.学号group by s.学号,s.姓名7查询所有没有选修课程的学生信息，返回结果包括学生学号、姓名和性别。select student_info.学号,student_info.姓名,student_info.性别from student_infowhere student_info.学号 not in(select distinct grade.学号from grade)8为grade表添加数据行：学号0004，课程编号为0006，分数为76。查询所有课程的选修情况，要求列出课程编号、课程名称、选修人数，curriculum表中没有的课程列值为空。insert grade(学号,课程编号,分数)values(0004,0006,76)页：6注意分数的数据类型，插入数据时要怎么样引用？这里分数的单引号是不需要的。select grade.学号,grade.课程编号,curriculum.课程名称 into #biaofrom gradeleft join curriculumon grade.课程编号=curriculum.课程编号select 课程编号,课程名称,count(学号) as 选修人数from #biaogr