数据库实验四SQL数据查询.docx
《数据库实验四SQL数据查询.docx》由会员分享，可在线阅读，更多相关《数据库实验四SQL数据查询.docx（11页珍藏版）》请在冰豆网上搜索。
数据库实验四SQL数据查询
数据库实验四：
SQL数据查询
一、实验目的与要求：
1．实验目的
熟练掌握SQLSELECT语句，能运用查询语句完成各种查询。
实验容：
在实验三和实验四的根底上完成查询操作，将题目中的*,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和E*ISTS〕
（26）使用IN运算的简单嵌套查询。
（27）使用NOTIN运算的简单嵌套查询。
（28）使用关系运算〔如等于〕的简单嵌套查询。
（29）使用ANY或SOME的简单嵌套查询。
（30）使用ALL的简单嵌套查询。
（31）查询院系名称含“计算机〞、职称为教授、所负责教程为必修课的教师、职称、课程名称和课程学时等信息〔分别用嵌套查询和连接查询完成，分析各自的效率〕。
（32）设计两个外层互相关的嵌套查询。
（33）使用E*ISTS的嵌套查询。
（34）使用NOTE*ISTS的嵌套查询。
4、汇总和分组查询
（35）使用COUNT统计数目的查询。
（36）使用SUM计算合计的查询。
（37）一次完成求和、计数、计算平均值的查询。
（38）查询所有课程的成绩都大于60分的学生的平均分最高的学生信息。
（39）查询数据库课程的成绩大于70分的、所有课程平均分最高的学生信息。
（40）查询每个学生的平均成绩。
（41）查询每个学生的所有成绩的最高成绩、最低成绩、平均成绩和所考课程的门数。
（42）查询至少有10门必修课程考试成绩的每个学生的平均成绩。
（43）设计2个使用PUTE···BY和PUTE的查询。
（44）设计1个使用PUTE的查询。
（45）设计1个使用PUTE···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工资isnull
select*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选课w
one.**=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教师out
where工资=
（selectma*（工资）
from教师innera
whereout.教师编号=innera.教师编号）
〔33〕使用E*ISTS的嵌套查询。
select*
from教师
wheree*ists
（select*
from课程
where课程.责任教师=教师.教师编号）
〔34〕使用NOTE*ISTS的嵌套查询。
select*
from教师
wherenote*ists
（select*
from课程
where课程.责任教师=教师.教师编号）
〔35〕使用COUNT统计数目的查询。
select*from教师
selectcount（工资）from教师
〔36〕使用SUM计算合计的查询。
selectsum（工资）from教师
〔37〕一次完成求和、计数、计算平均值的查询。
select*from教师
putesum（工资）,avg（工资）
〔38〕查询所有课程的成绩都大于60分的学生的平均分最高的学生信息。
select**,avg（成绩）平均成绩
from选课
where成绩>60
groupby成绩,**
orderby成绩desc
〔39〕查询数据库课程的成绩大于70分的、所有课程平均分最高的学生信息。
selecttop
（1）*from学生where**in
（select**from选课join课程
on选课.课程编号=课程.课程编号
where成绩>70and课程名称='数据库'）
〔40〕查询每个学生的平均成绩。
select**,avg（成绩）as平均成绩
from选课
groupby**
〔41〕查询每个学生的所有成绩的最高成绩、最低成绩、平均成绩和所考课程的门数。
select**,count（*）'所选课程数',ma*（成绩）'最高成绩',avg（成绩）'平均成绩',min（成绩）'最低成绩'
from选课
groupby**
〔42〕查询至少有1门必修课程考试成绩的每个学生的平均成绩。
selectavg（成绩）
from选课
where课程编号in
（select课程编号from课程where课程性质like'%根底'）
groupby**
〔43〕设计1个使用PUTE•••BY和PUTE的查询。
select*
from选课orderby**
puteavg（成绩）,sum（成绩）by**
〔44〕设计1个使用PUTE的查询。
select*from教师
putesum（工资）
〔45〕设计个使用group•••BY的查询。
select教师编号,avg（工资）
from教师
groupby教师编号
三、实验分析与小结：
〔实验过程中的问题分析、产生的原因以及解决方法；实验结果分析；有待优化思路〕
实验过程中出现了很多的错误，但经过自己的不懈努力都解决的了，也正是通过这次的实验学到了很多，比方：
groupby一般跟在where之后，没有where时，跟在from后；groupby中的分组依据必须包含select中的分组标识，一般情况下它们应该是一致的。
四、其它
思考题：
1．连接查询和嵌套查询的区别是什么？
如何转化使用？
区别：
嵌套查询通常是一次查询不能满足条件的前提下使用的，是使用最大围的收敛方法，以减少外查询对存的使用量。
而连接查询要求结果出自一个关系。
将连接查询中需要连接的字段，使用in运算进展嵌套查询进展转化，in根本语法格式：
SELECT…
FROM
WHERE[<其他查询条件>AND]IN
（SELECTFROM
[WHERE…]）
得分〔百分制〕
升级会员 