数据库实验四SQL数据查询.pdf

《数据库实验四SQL数据查询.pdf》由会员分享，可在线阅读，更多相关《数据库实验四SQL数据查询.pdf（28页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、 数据库实验四：SQL 数据查询 一、实验目的与要求：1实验目的 熟练掌握 SQL SELECT 语句，能运用查询语句完成各种查询.实验内容：在实验三和实验四的基础上完成查询操作，将题目中的 x,y 等具体化：1、一般简单查询（1）不带条件的查询指定字段（考虑去掉和不去掉重复值两种情况）。（2）查询某个表中的所有记录。（3）使用单个条件的简单查询。（4）使用多个条件（AND 关系）的查询。（5）使用多个条件(OR 关系）的查询.（6）使用多个条件（混合 AND 和 OR 关系）的查询。（7）使用带 NOT 运算的查询。（8）使用 BETWEEN AND 的查询。（9）使用 NOT BETWEE

2、N AND 的查询.（10）使用 LIKE 运算符的字符串匹配查询.（11）使用 LIKE 运算符的模板匹配查询.（12）查询空值和非空值的查询（13）结果要求排序的查询（14）查询结果按多列排序，并分别要求升序和降序的查询。（15）使用 TOP 显示前若干记录的查询。（16）使用 TOP 显示前若干记录的查询，如果有满足条件的并列记录一并显示。2、连接查询（17）两个关系的连接查询。（18）带其他查询条件的两个关系的连接查询。（19）多个关系（三个以上）的连接查询。（20）两个关系的广义笛卡尔积运算结果。（21）根据两个关系的广义笛卡尔积运算结果得到两个关系进行自然连接的结果.（22）查询教

3、师课程信息，查询结果中包括教师姓名、职称、课程名称和课程性质等4 个字段。（23）查询教师-课程信息，查询结果中包括教师姓名、职称、课程名称和课程性质等 4 个字段，要求结果中列出所有教师信息（即包括不是任何课程责任教师的教师信息）.（24）查询教师课程信息，查询结果中包括教师姓名、职称、课程名称和课程性质等4 个字段，要求结果中能够反映目前没有确定责任教师的课程信息。（25）查询教师-课程信息，查询结果中包括教师姓名、职称、课程名称和课程性质等 4 个字段，要求结果中既能够反映目前不是责任教师的教师信息，又能反映目前没有确定责任教师的课程信息。提示:(22）（25)分别用内连接、左连接、右连

4、接完成，分析它们的效果。3、嵌套查询（注意使用 ANY、ALL 和 EXISTS）（26）使用 IN 运算的简单嵌套查询。（27）使用 NOT IN 运算的简单嵌套查询。（28）使用关系运算（如等于）的简单嵌套查询.（29）使用 ANY 或 SOME 的简单嵌套查询。（30）使用 ALL 的简单嵌套查询。（31）查询院系名称含“计算机、职称为教授、所负责教程为必修课的教师姓名、职称、课程名称和课程学时等信息（分别用嵌套查询和连接查询完成，分析各自的效率）。（32）设计两个内外层互相关的嵌套查询。（33）使用 EXISTS 的嵌套查询.（34）使用 NOT EXISTS 的嵌套查询。4、汇总和分

5、组查询（35）使用 COUNT 统计数目的查询。（36）使用 SUM 计算合计的查询.（37）一次完成求和、计数、计算平均值的查询。（38）查询所有课程的成绩都大于 60 分的学生的平均分最高的学生信息。（39）查询数据库课程的成绩大于 70 分的、所有课程平均分最高的学生信息。（40）查询每个学生的平均成绩。（41）查询每个学生的所有成绩的最高成绩、最低成绩、平均成绩和所考课程的门数。（42）查询至少有 10 门必修课程考试成绩的每个学生的平均成绩。（43）设计 2 个使用 COMPUTE BY 和 COMPUTE的查询。（44）设计 1 个使用 COMPUTE的查询。（45）设计 1 个使

6、用 COMPUTE BY 的查询。二、实验内容 1、实验原理 SQL SELECT 查询语句的一般格式是:SELECT ALL|DISTINCT 表达式，表达式 FROM，表名 WHERE 逻辑表达式 GROUP BY 列名，HAVING ASC DESC，列名 ASC DESC 2、实验步骤与结果 （1）调出 SQL Server2005 软件的用户界面，进入 SQL Server Management Studio。（2)输入自己编好的程序。（3)检查已输入的程序正确与否.（4）运行程序，并分析运行结果是否合理和正确.在运行时要注意当输入不同的数据时所得到的结果是否正确。(5）输出程序清单

7、和运行结果.实验过程及相关程序：1、一般简单查询（1）不带条件的查询指定字段(考虑去掉和不去掉重复值两种情况）。select distinct 学号 from 选课 select 学号 from 选课 (2)查询某个表中的所有记录 查询教师表中的所有记录 (3)使用单个条件的简单查询 查询成绩大于50的学生的学号 select 学号 from 选课 where 成绩50 （4）使用多个条件（AND关系）的查询 select 工资 from 教师 where 工资200 and 工资500 （6）使用多个条件（混合AND和OR关系）的查询 select*from 学生 where(院系=2 an

8、d 生源=淮南）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

9、办公地点 like 4_1 （12）查询空值和非空值的 select*from 教师 where 工资 is null select*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

10、 *from 学生 （16）使用TOP显示前若干记录的查询，如果有满足条件的并列记录一并显示 select top 3 *from 学生 where 生源=淮南 (17）两个关系的连接查询。select*from 学生 cross join 选课 （18)带其他查询条件的两个关系的连接查询 select 学生。学号，院系，姓名，性别，生源，课程编号,成绩 from 学生 join 选课 on 选课。学号=学生。学号 （19）多个关系（三个以上）的连接查询 select*from 学生，课程，选课 where 学生。学号=选课。学号 and 课程。课程编号=选课。课程编号 （20)两个关系的广义

11、笛卡尔积运算结果。select from 学生 cross join 选课 (21）根据两个关系的广义笛卡尔积运算结果得到两个关系进行自连接的结果 select e。学号，院系，姓名,性别，生源，课程编号,成绩 from 学生 e join 选课 w on e.学号=w。学号 (22）查询教师-课程信息,查询结果中包括教师姓名、职称、课程名称和课程性质等个字段 select 姓名，职称，课程名称，课程性质 from 教师 inner join 课程 on 教师.教师编号=课程.责任教师 (23)查询教师-课程信息,查询结果中包括教师姓名、职称、课程名称和课程性质等个字段，要求结果中列出所有教师

12、信息（即包括不是任何课程责任教师的教师信息)select 姓名,职称，课程名称,课程性质 from 教师 left join 课程 on 教师.教师编号=课程.责任教师 （24）查询教师-课程信息，查询结果中包括教师姓名、职称、课程名称和课程性质等个字段,要求结果中能够反映目前没有确定责任教师的课程信息。select 姓名，职称,课程名称，课程性质 from 教师 right join 课程 on 教师。教师编号=课程。责任教师 (25）查询教师课程信息，查询结果中包括教师姓名、职称、课程名称和课程性质等4个字段,要求结果中既能够反映目前不是责任教师的教师信息,又能反映目前没有确定责任教师的课

13、程信息 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 教师.姓名

14、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）查询院系名称含“计科系”、职称为教授、所负责教程为专业基础的教师姓名、职称、课程名称和课程学时等信

15、息（分别用嵌套查询和连接查询完成,分析各自的效率）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

16、（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）查询所有课程的成绩

17、都大于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)查询每个学

18、生的所有成绩的最高成绩、最低成绩、平均成绩和所考课程的门数。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（成绩），s

19、um（成绩）by 学号 （44)设计1个使用COMPUTE的查询。select*from 教师 compute sum（工资)(45)设计个使用groupBY的查询.select 教师编号，avg（工资)from 教师 group by 教师编号 三、实验分析与小结：（实验过程中的问题分析、产生的原因以及解决方法；实验结果分析;有待优化思路)实验过程中出现了很多的错误,但经过自己的不懈努力都解决的了,也正是通过这次的实验学到了很多,比如:group by一般跟在 where 之后，没有 where 时，跟在 from 后；group by 中的分组依据必须包含select中的分组标识，一般情况下它们应该是一致的。四、其它 思考题:1连接查询和嵌套查询的区别是什么？如何转化使用?区别：嵌套查询通常是一次查询不能满足条件的前提下使用的,是使用最大范围的收敛方法，以减少外查询对内存的使用量。而连接查询要求结果出自一个关系。将连接查询中需要连接的字段，使用in 运算进行嵌套查询进行转化,in 基本语法格式：SELECT FROM table_source WHERE 其他查询条件 AND expression1 IN （SELECT expression2 FROM WHERE）得分（百分制）