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

数据库实验四：SQL数据查询一、实验目的与要求：1．实验目的熟练掌握SQLSELECT语句，能运用查询语句完成各种查询。实验内容：在实验三和实验四的基础上完成查询操作，将题目中的x,y等具体化：1、一般简单查询（1）不带条件的查询指定字段（考虑去掉和不去掉重复值两种情况）。（2）查询某个表中的所有记录。（3）使用单个条件的简单查询。（4）使用多个条件（AND关系）的查询。（5）使用多个条件（OR关系）的查询。（6）使用多个条件（混合AND和OR关系）的查询。（7）使用带NOT运算的查询。（8）使用BETWEEN···AND···的查询。（9）使用NOT···BETWEEN···AND···的查询。（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）使用NOTIN运算的简单嵌套查询。（28）使用关系运算（如等于）的简单嵌套查询。（29）使用ANY或SOME的简单嵌套查询。（30）使用ALL的简单嵌套查询。（31）查询院系名称含“计算机”、职称为教授、所负责教程为必修课的教师姓名、职称、课程名称和课程学时等信息（分别用嵌套查询和连接查询完成，分析各自的效率）。（32）设计两个内外层互相关的嵌套查询。（33）使用EXISTS的嵌套查询。（34）使用NOTEXISTS的嵌套查询。4、汇总和分组查询（35）使用COUNT统计数目的查询。（36）使用SUM计算合计的查询。（37）一次完成求和、计数、计算平均值的查询。（38）查询所有课程的成绩都大于60分的学生的平均分最高的学生信息。（39）查询数据库课程的成绩大于70分的、所有课程平均分最高的学生信息。（40）查询每个学生的平均成绩。（41）查询每个学生的所有成绩的最高成绩、最低成绩、平均成绩和所考课程的门数。（42）查询至少有10门必修课程考试成绩的每个学生的平均成绩。（43）设计2个使用COMPUTE···BY和COMPUTE的查询。（44）设计1个使用COMPUTE的查询。（45）设计1个使用COMPUTE···BY的查询。二、实验内容1、实验原理SQLSELECT查询语句的一般格式是：SELECT[ALL|DISTINCT]表达式[,表达式…]FROM表名[,表名…][WHERE逻辑表达式][GROUPBY列名[,列名…][HAVING谓词]][ORDERBY列名[ASC|DESC],[列名[ASC|DESC]]…]2、实验步骤与结果（1）调出SQLServer2005软件的用户界面，进入SQLServerManagementStudio。（2）输入自己编好的程序。（3）检查已输入的程序正确与否。（4）运行程序，并分析运行结果是否合理和正确。在运行时要注意当输入不同的数据时所得到的结果是否正确。（5）输出程序清单和运行结果。实验过程及相关程序：1、一般简单查询（1）不带条件的查询指定字段（考虑去掉和不去掉重复值两种情况）。selectdistinct学号from选课select学号from选课(2)查询某个表中的所有记录查询教师表中的所有记录(3)使用单个条件的简单查询查询成绩大于50的学生的学号select学号from选课where成绩50（4）使用多个条件（AND关系）的查询select工资from教师where工资200and工资500（5）使用多个条件（OR关系）的查询select工资from教师where工资200or工资500（6）使用多个条件（混合AND和OR关系）的查询select*from学生where(院系='2'and生源='淮南')or(院系='6'and生源='淮北')（7）使用带NOT运算的查询select*from学生wherenot(生源='合肥')（8）使用BETWEEN•••AND•••的查询select*from选课where成绩between86and93（9）使用NOT•••BETWEEN•••AND•••的查询select*from选课where成绩notbetween76and93（10）使用LIKE运算符的字符串匹配查询。select*from课程where课程性质like'专业__'（11）使用LIKE运算符的模板匹配查询。select*from院系where办公地点like'4_1'（12）查询空值和非空值的select*from教师where工资isnullselect*from教师where工资isnotnull（13）结果要求排序的查询select*from选课--升序orderby成绩select*from选课--降序orderby成绩desc（14）查询结果按多列排序，并分别要求升序和降序的查询。select*from选课orderby学号,成绩select*from选课orderby学号,成绩desc（15）使用TOP显示前若干记录的查询selecttop3*from学生（16）使用TOP显示前若干记录的查询，如果有满足条件的并列记录一并显示selecttop3*from学生where生源='淮南'（17）两个关系的连接查询。select*from学生crossjoin选课（18）带其他查询条件的两个关系的连接查询select学生.学号,院系,姓名,性别,生源,课程编号,成绩from学生join选课on选课.学号=学生.学号（19）多个关系（三个以上）的连接查询select*from学生,课程,选课where学生.学号=选课.学号and课程.课程编号=选课.课程编号（20）两个关系的广义笛卡尔积运算结果。select*from学生crossjoin选课（21）根据两个关系的广义笛卡尔积运算结果得到两个关系进行自连接的结果selecte.学号,院系,姓名,性别,生源,课程编号,成绩from学生ejoin选课wone.学号=w.学号（22）查询教师-课程信息，查询结果中包括教师姓名、职称、课程名称和课程性质等个字段select姓名,职称,课程名称,课程性质from教师innerjoin课程on教师.教师编号=课程.责任教师（23）查询教师-课程信息，查询结果中包括教师姓名、职称、课程名称和课程性质等个字段，要求结果中列出所有教师信息（即包括不是任何课程责任教师的教师信息）select姓名,职称,课程名称,课程性质from教师leftjoin课程on教师.教师编号=课程.责任教师（24）查询教师-课程信息，查询结果中包括教师姓名、职称、课程名称和课程性质等个字段，要求结果中能够反映目前没有确定责任教师的课程信息。select姓名,职称,课程名称,课程性质from教师rightjoin课程on教师.教师编号=课程.责任教师（25）查询教师-课程信息，查询结果中包括教师姓名、职称、课程名称和课程性质等4个字段，要求结果中既能够反映目前不是责任教师的教师信息，又能反映目前没有确定责任教师的课程信息select姓名,职称,课程名称,课程性质from教师fulljoin课程on课程.责任教师=教师.教师编号（26）使用IN运算的简单嵌套查询。select教师.姓名from教师where教师.教师编号in(select课程.责任教师from课程where课程性质='专业基础')（27）使用NOTIN运算的简单嵌套查询select教师.姓名from教师where教师.教师编号notin(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教师outwhere工资=(selectmax(工资)from教师innerawhereout.教师编号=innera.教师编号)（33）使用EXISTS的嵌套查询。select*from教师whereexists(select*from课程where课程.责任教师=教师.教师编号)（34）使用NOTEXISTS的嵌套查询。select*from教师wherenotexists(select*from课程where课程.责任教师=教师.教师编号)（35）使用COUNT统计数目的查询。select*from教师selectcount(工资)from教师（36）使用SUM计算合计的查询。selectsum(工资)from教师（37）一次完成求和、计数、计算平均值的查询。select*from教师computesum(工资),avg(工资)（38）查询所有课程的成绩都大于60分的学生的平均分最高的学生信息。select学号,avg(成绩)平均成绩from选课where成绩60groupby成绩,学号orderby成绩desc（39）查询数据库课程的成绩大于70分的、所有课程平均分最高的学生信息。selecttop(1)*from学生where学号in(select学号from选课join课程on选课.课程编号=课程.课程编号where成绩70and课程名称='数据库')（40）查询每个学生的平均成绩。select学号,avg(成绩)as平均成绩from选课groupby学号（41）查询每个学生的所有成绩的最高成绩、最低成绩、平均成绩和所考课程的门数。select学号,count(*)'所选课程数',max(成绩)'最高成绩',avg(成绩)'平均成绩',min(成绩)'最低成绩'from选课groupby学号（42）查询至少有1门必修课程考试成绩的每个学生的平均成绩。selectavg(成绩)f