数据库实验四：SQL数据查询.doc

数据库实验四：SQL数据查询一、实验目的与要求：1实验目的熟练掌握SQL SELECT语句，能运用查询语句完成各种查询。实验内容 ：在实验三和实验四的基础上完成查询操作，将题目中的x, y等具体化：1、 一般简单查询（1） 不带条件的查询指定字段（考虑去掉和不去掉重复值两种情况）。（2） 查询某个表中的所有记录。（3） 使用单个条件的简单查询。（4） 使用多个条件（AND关系）的查询。（5） 使用多个条件（OR关系）的查询。（6） 使用多个条件（混合AND和OR关系）的查询。（7） 使用带NOT运算的查询。（8） 使用BETWEENAND的查询。（9） 使用NOTBETWEENAND的查询。（10） 使用LIKE运算符的字符串匹配查询。（11） 使用LIKE运算符的模板匹配查询。（12） 查询空值和非空值的查询（13） 结果要求排序的查询（14） 查询结果按多列排序，并分别要求升序和降序的查询。（15） 使用TOP显示前若干记录的查询。（16） 使用TOP显示前若干记录的查询，如果有满足条件的并列记录一并显示。2、连接查询（17） 两个关系的连接查询。（18） 带其他查询条件的两个关系的连接查询。（19） 多个关系（三个以上）的连接查询。（20） 两个关系的广义笛卡尔积运算结果。（21） 根据两个关系的广义笛卡尔积运算结果得到两个关系进行自然连接的结果。（22） 查询教师-课程信息，查询结果中包括教师姓名、职称、课程名称和课程性质等4个字段。（23） 查询教师-课程信息，查询结果中包括教师姓名、职称、课程名称和课程性质等4个字段，要求结果中列出所有教师信息（即包括不是任何课程责任教师的教师信息）。（24） 查询教师-课程信息，查询结果中包括教师姓名、职称、课程名称和课程性质等4个字段，要求结果中能够反映目前没有确定责任教师的课程信息。（25） 查询教师-课程信息，查询结果中包括教师姓名、职称、课程名称和课程性质等4个字段，要求结果中既能够反映目前不是责任教师的教师信息，又能反映目前没有确定责任教师的课程信息。提示：（22）（25）分别用内连接、左连接、右连接完成，分析它们的效果。3、嵌套查询（注意使用ANY、ALL和EXISTS）（26） 使用IN运算的简单嵌套查询。（27） 使用NOT IN运算的简单嵌套查询。（28） 使用关系运算（如等于）的简单嵌套查询。（29） 使用ANY或SOME的简单嵌套查询。（30） 使用ALL的简单嵌套查询。（31） 查询院系名称含“计算机”、职称为教授、所负责教程为必修课的教师姓名、职称、课程名称和课程学时等信息（分别用嵌套查询和连接查询完成，分析各自的效率）。（32） 设计两个内外层互相关的嵌套查询。（33） 使用EXISTS的嵌套查询。（34） 使用NOT EXISTS的嵌套查询。4、汇总和分组查询（35） 使用COUNT统计数目的查询。（36） 使用SUM计算合计的查询。（37） 一次完成求和、计数、计算平均值的查询。（38） 查询所有课程的成绩都大于60分的学生的平均分最高的学生信息。（39） 查询数据库课程的成绩大于70分的、所有课程平均分最高的学生信息。（40） 查询每个学生的平均成绩。（41） 查询每个学生的所有成绩的最高成绩、最低成绩、平均成绩和所考课程的门数。（42） 查询至少有10门必修课程考试成绩的每个学生的平均成绩。（43） 设计2个使用COMPUTEBY和COMPUTE的查询。（44） 设计1个使用COMPUTE的查询。（45） 设计1个使用COMPUTEBY的查询。二、实验内容1、实验原理 SQL SELECT查询语句的一般格式是：SELECT ALL | DISTINCT , FROM ,WHERE GROUP BY , HAVINGORDER BY ASC | DESC, ASC | DESC 2、实验步骤与结果 （1）调出SQL Server2005软件的用户界面，进入SQL Server Management Studio。（2）输入自己编好的程序。（3）检查已输入的程序正确与否。（4）运行程序，并分析运行结果是否合理和正确。在运行时要注意当输入不同的数据时所得到的结果是否正确。（5）输出程序清单和运行结果。实验过程及相关程序：1、 一般简单查询（1） 不带条件的查询指定字段（考虑去掉和不去掉重复值两种情况）。select distinct 学号 from 选课select 学号 from 选课(2) 查询某个表中的所有记录查询教师表中的所有记录(3) 使用单个条件的简单查询查询成绩大于50的学生的学号select 学号 from 选课where 成绩50（4） 使用多个条件（AND关系）的查询select 工资from 教师where 工资200 and 工资500（5） 使用多个条件（OR关系）的查询select 工资from 教师where 工资500（6） 使用多个条件（混合AND和OR关系）的查询select * from 学生where (院系=2 and 生源=淮南) or (院系=6 and 生源=淮北)（7）使用带NOT运算的查询select * from 学生where not (生源=合肥)（8）使用BETWEENAND的查询select * from 选课where 成绩 between 86 and 93（9）使用NOTBETWEENAND的查询select * from 选课where 成绩 not between 76 and 93（10）使用LIKE运算符的字符串匹配查询。select * from 课程where 课程性质 like 专业_（11）使用LIKE运算符的模板匹配查询。select * from 院系where 办公地点 like 4_1（12）查询空值和非空值的select * from 教师where 工资 is nullselect * from 教师where 工资 is not null（13）结果要求排序的查询select * from 选课 -升序order by 成绩select * from 选课 -降序order by 成绩 desc（14）查询结果按多列排序，并分别要求升序和降序的查询。select * from 选课order by 学号, 成绩select * from 选课order by 学号, 成绩 desc（15）使用TOP显示前若干记录的查询select top 3 * from 学生（16）使用TOP显示前若干记录的查询，如果有满足条件的并列记录一并显示select top 3 * from 学生where 生源=淮南（17）两个关系的连接查询。select * from 学生 cross join 选课（18）带其他查询条件的两个关系的连接查询select 学生.学号,院系,姓名,性别,生源,课程编号,成绩from 学生 join 选课 on 选课.学号= 学生.学号（19）多个关系（三个以上）的连接查询select * from 学生,课程,选课where 学生.学号= 选课.学号and 课程.课程编号= 选课.课程编号（20）两个关系的广义笛卡尔积运算结果。select * from 学生 cross join 选课（21）根据两个关系的广义笛卡尔积运算结果得到两个关系进行自连接的结果select e.学号,院系,姓名,性别,生源,课程编号,成绩from 学生 e join 选课 w on e.学号= w.学号（22）查询教师-课程信息，查询结果中包括教师姓名、职称、课程名称和课程性质等个字段select 姓名,职称,课程名称,课程性质from 教师 inner join 课程on 教师.教师编号=课程.责任教师（23）查询教师-课程信息，查询结果中包括教师姓名、职称、课程名称和课程性质等个字段，要求结果中列出所有教师信息（即包括不是任何课程责任教师的教师信息）select 姓名,职称,课程名称,课程性质from 教师 left join 课程on 教师.教师编号=课程.责任教师（24）查询教师-课程信息，查询结果中包括教师姓名、职称、课程名称和课程性质等个字段，要求结果中能够反映目前没有确定责任教师的课程信息。select 姓名,职称,课程名称,课程性质from 教师 right join 课程on 教师.教师编号=课程.责任教师（25）查询教师-课程信息，查询结果中包括教师姓名、职称、课程名称和课程性质等4个字段，要求结果中既能够反映目前不是责任教师的教师信息，又能反映目前没有确定责任教师的课程信息select 姓名,职称,课程名称,课程性质from 教师full join 课程on 课程.责任教师=教师.教师编号（26）使用IN运算的简单嵌套查询。select 教师.姓名from 教师where 教师.教师编号 in (select 课程.责任教师from 课程where 课程性质=专业基础)（27）使用NOT IN运算的简单嵌套查询select 教师.姓名from 教师where 教师.教师编号 not in (select 课程.责任教师from 课程where 课程性质=专业基础)（28）使用关系运算（如等于）的简单嵌套查询select 教师.姓名from 教师where 教师.教师编号=(select 课程.责任教师from 课程where 课程编号=02)（29）使用ANY或SOME的简单嵌套查询select 姓名from 教师where 工资= any( select 工资 from 教师 where 教师编号=20118)select * from 教师（30）使用ALL的简单嵌套查询。select 姓名from 教师where 工资= all (select 工资from 教师 where 教师编号=20115)（31）查询院系名称含“计科系”、职称为教授、所负责教程为专业基础的教师姓名、职称、课程名称和课程学时等信息（分别用嵌套查询和连接查询完成，分析各自的效率）select 姓名,课程名称,学时from 教师 join 课程on 责任教师=教师编号 join 院系on 院系.编号=教师.院系where 名称=计科系 and 职称=教授（32）设计两个内外层互相关的嵌套查询。select * from 教师select 教师编号,姓名,性别,职称,工资from 教师 out where 工资=(select max(工资) from 教师 innera where out.教师编号= innera.教师编号 )（33）使用EXISTS的嵌套查询。select * from 教师where exists(select * from 课程 where 课程.责任教师=教师.教师编号)（34）使用NOT EXISTS的嵌套查询。select * from 教师where not exists(select * from 课程 where 课程.责任教师=教师.教师编号)（35）使用COUNT统计数目的查询。select * from 教师select count (工资) from 教师（36）使用SUM计算合计的查询。select sum (工资) from 教师（37）一次完成求和、计数、计算平均值的查询。select * from 教师compute sum(工资),avg(工资)（38）查询所有课程的成绩都大于60分的学生的平均分最高的学生信息。select 学号,avg(成绩) 平均成绩from 选课where 成绩60 group by 成绩,学号order by 成绩 desc（39）查询数据库课程的成绩大于70分的、所有课程平均分最高的学生信息。select top(1) * from 学生where 学号in ( select 学号from 选课join 课程 on 选课.课程编号=课程.课程编号 where 成绩70 and 课程名称=数据库)（40）查询每个学生的平均成绩。select 学号,avg(成绩) as 平均成绩from 选课group by 学号（41）查询每个学生的所有成绩的最高成绩、最低成绩、平均成绩和所考课程的门数。select 学号,count(*)所选课程数,max(成绩)最高成绩,avg(成绩)平均成绩,min(成绩)最低成绩 from 选课group by 学号（42）查询至少有1门必修课程考试成绩的每个学生的平均成绩。select avg(成绩)from 选课where 课程编号 in(select 课程编号 from 课程 where 课程性质 like %基础)group by 学号（43）设计1个使用COMPUTEBY和COMPUTE的查询。select * from 选课order by 学号compute avg(成绩),sum(成绩) by 学号（44）设计1个使用COMPUTE的查询。select * from 教师compute sum(工资) （45）设计个使用groupBY的查询。select 教师编号,avg(工资)from 教师group by 教师编号三、实验分析与小结：（实验过程中的问题分析、产生的原因以及解决方法；实验结果分析； 有待优化思路）实验过程中出现了很多的错误，但经过自己的不懈努力都解决的了，也正是通