查询和视图的建立与使用

实验六　查询和视图的建立与使用

实验目的

1．掌握查询的设计与运行。

2．掌握视图的建立与修改。

3．了解查询与视图的用处与区别。

实验题目

1．利用“学生”、“选课”和“课程”表，查询选修了“数学建模”课程的学生的学号、姓名及平均成绩，查询结果按平均成绩的降序排列并存储到表results中，查询文件保存为XS1.QPR。

2．利用查询设计器，建立查询XS2.QPR，用来查询“学生”表中入校总分排在前5名的学生的学号、姓名、年龄和入校总分（表中无年龄字段，只有出生日期字段）。

3．在“学生管理”数据库中，建立本地视图XS3，显示入校总分在580分以上（包含580）的学生的学号、姓名、课程名和成绩4个字段，先按学号的升序排列，再按成绩的降序排列，并且可利用该视图来更新源表中的姓名和成绩字段。

4．利用视图设计器创建一个学生成绩查询视图XS4，通过输入任意学生的学号，即可显示该学生的学号、姓名和最高分。

实验步骤

6-1　实验步骤

1．选择“文件\新建\查询\新建文件”，打开查询设计器。

2．弹出“添加表或视图”对话框，如图6.1所示，依次选中“学生”表、“选课”表和“课程”表，添加到查询设计器窗口。

3．在“连接条件”对话框中（如图6.2所示），确定表的连接条件分别为“学生.学号=选课.学号”、“选课.课程号=课程.课程号”，连接类型为“内部连接”。

4．数据表添加完毕后，关闭“添加表或视图”对话框，进入查询设计器。

图6.1　“添加表或视图”对话框

图6.2　“连接条件”对话框

5．在“查询设计器”的“字段”选项卡中，选择字段“学生.学号”和“学生.姓名”，添加到“选定字段”中，然后在“函数和表达式”框中输入“AVG（成绩）AS平均成绩”，单击“添加”按钮，将其也添加到“选定字段”中，如图6.3所示。

图6.3　查询设计器“字段”选项卡

6．在“筛选”选项卡中输入查询条件：课程.课程名=“数学建模”。

7．在“排序依据”选项卡中，将“选定字段”框中的“AVG（成绩）AS 平均成绩”添加到“排序条件”中，然后选择为“降序”。

8．在“分组依据”选项卡中，选择分组字段为“学生.学号”。

9．选择“查询\查询去向”，弹出“查询去向”对话框，如图6.4所示。选择查询去向为“表”，输入表名results。

10．选择“查询\运行查询”，查看查询结果。

11．关闭查询设计器窗口，保存查询文件为XS1.QPR。

图6.4　“查询去向”对话框

6-2　实验步骤

1．选择“文件\新建\查询\新建文件”，打开查询设计器。

2．弹出“添加表或视图”对话框，选中“学生”表，添加到查询设计器窗口。

3．在“查询设计器”的“字段”选项卡中，选中“学生.学号”、“学生.姓名”、“学生.入校总分”等字段，依次添加到“选定字段”中，然后在“函数和表达式”框中输入“year（date（））-year（出生日期）as 年龄”，也添加到“选定字段”中。

4．在“查询设计器”的“排序依据”选项卡中，将“选定字段”框中的“入校总分”字段添加到“排序条件”中，然后选择为“降序”。

5．在“查询设计器”的“杂项”选项卡中，取消“全部”选项，在“记录”个数中输入数字5。

6．选择“查询\运行查询”，查看查询结果。

7．关闭查询设计器窗口，保存查询文件为XS2.QPR。

6-3　实验步骤

1．先打开“学生管理”数据库，然后选择“文件\新建\视图\新建文件”，打开视图设计器。

2．弹出“添加表或视图”对话框，依次将“学生”表、“选课”表和“课程”表添加到视图设计器。

3．设定连接条件分别为“学生.学号=选课.学号”、“选课.课程号=课程.课程号”，连接类型为“内部连接”。

4．在“视图设计器”的“字段”选项卡中，将“学生.学号”、“学生.姓名”、“课程.课程名”、“选课.成绩”等字段添加到“选定字段”中。

5．在“排序依据”选项卡中，先选择排序字段为“学生.学号”、“升序”；再选择“选课.成绩”、“降序”。

6．在“更新条件”选项卡中，分别选择“学生.学号”和“选课.课程号”为关键字，“学生.姓名”、“选课.成绩”为可更新字段，并选中“发送SQL更新”，如图6.5所示。

7．关闭视图设计器，保存视图为XS3。

8．在“学生管理”数据库设计器窗口双击视图XS3，修改“姓名”或“成绩”字段值，然后再双击原表，观察相应字段的修改结果。

图6.5　视图设计器“更新条件”选项卡

6-4　实验步骤

1．先打开“学生管理”数据库，然后选择“文件\新建\视图\新建文件”，打开视图设计器。

2．弹出“添加表或视图”对话框，依次将“学生”表、“选课”表添加到视图设计器。

3．设定连接条件分别为“学生.学号=选课.学号”，连接类型为“内部连接”。

4．在“字段”选项卡中，将“学生.学号”、“学生.姓名”、“MAX（成绩）AS 最高分”添加到“选定字段”中。

5．在“筛选”选项卡中，设置筛选条件为：学生.学号=？学号。

6．关闭视图设计器，保存视图为XS4。

7．在“学生管理”数据库设计器窗口双击视图XS4，弹出如图6.6所示的“视图参数”对话框。在该对话框中输入要查询的学号值，即可查询该学生的相关信息。

图6.6　“视图参数”对话框

思考题

1．比较查询与视图的区别。

2．利用“学生管理”数据库，查询选修了两门课程以上且每门课程的课时数不小于70的学生的学号、姓名、选课门数和平均成绩，查询结果按平均成绩的升序排列，并放到临时表XSCJ中。

3．在“学生管理”数据库中，建立视图XSQK，显示所有非三好生的学号（选课.学号）、姓名、课程名和成绩，要求以“学号”作为关键字，对“成绩”字段进行更新，并允许发送到原表“选课”表。