VF中SQL复杂查询的四步教学法研究

贾丽萍

（山西师范大学临汾学院，山西临汾 041000）

Visual FoxPro是一个集数据库技术与程序设计为一体的关系数据库系统开发软件，在众多的数据库系统软件中有着不可替代的作用，长久以来享有“大众数据库”的美誉.

SQL是结构化查询语言，是操作关系数据库的标准数据库查询语言，提供了关系数据库定义、数据操作和数据查询等功能.其中数据查询是SQL的核心，也是教学的重点和难点.本文主要针对SQL中的复杂查询，从查询涉及的字段、字段来自哪些表、表之间的关联、查询条件分析四个方面阐述了如何利用四步法编写复杂的查询语句.

1 多表联接查询

假设“教学管理”数据库中有5个表，分别是学生表（学号c(7)主索引，姓名c(8)，性别c(2)，出生日期d，专业c(10)普通索引，入学成绩n(5，1)，贷款否l，照片g，简历m），任课表（课程代号c(5)普通索引，教师代号c(5)普通索引）其中课程代号+教师代号为主索引，课程表（课程代号c(5)主索引，课程名c(16)普通索引，周学时n(1，0)，学分n(1，0)），教员表（教师代号c(5)主索引，姓名c(8)，性别c(2)，出生日期d，职称c(6)），成绩表（学号c(7)普通索引，课程代号c(5)普通索引，平时n(3，0)，期中n(3，0)，期末n(3，0)）其中学号+课程代号为主索引[1].

题目1：检索所有选修了“大学语文”课程的学生姓名、学号和课程名.

教学法：

（1）查询涉及的字段

（学生）姓名、学号、课程名

（2）字段来自哪些表

学生表、课程表

（3）表之间的关联

学生表、课程表之间没有关联.应该寻找中间表将这两个表关联起来.观察数据库中的表，分析得出：学生表通过成绩表和课程表关联.

（4）查询条件分析

课程名=“大学语文”

最后，编写SQL语句为：select姓名，学生表.学号，课程名from学生表，成绩表，课程表where学生表.学号=成绩表.学号 and课程表.课程代号=成绩表.课程代号and课程名=“大学语文”，或者也可编写为：select姓名，学生表.学号，课程名from学生表join成绩表join课程表on课程表.课程代号=成绩表.课程代号 on学生表.学号=成绩表.学号where课程名=“大学语文”.

题目 2：检索选修“大学语文”课程的学生姓名、课程名和教师姓名.

教学法：

（1）查询涉及的字段

(学生)姓名、课程名、教师姓名

（2）字段来自哪些表

学生表、课程表、教员表

（3）表之间的关联

学生表、课程表、教员表之间没有关联.应该寻找中间表将这三个表关联起来.观察数据库中的表，分析得出：学生表通过成绩表和课程表关联，教员表通过任课表和课程表关联.

（4）查询条件分析

课程名=“大学语文”

最后，编写SQL语句为：select学生表.姓名as学生姓名，课程名，教员表.姓名as教师姓名from学生表，成绩表，课程表，教员表，任课表 where学生表.学号=成绩表.学号 and课程表.课程代号=成绩表.课程代号and教员表.教师代号=任课表.教师代号and课程表.课程代号=任课表.课程代号and课程名=“大学语文”，或者也可编写为：select学生表.姓名as学生姓名，课程名，教员表.姓名as教师姓名from学生表join成绩表join课程表join任课表join教员表 on教员表.教师代号=任课表.教师代号on课程表.课程代号=任课表.课程代号on课程表.课程代号=成绩表.课程代号 on学生表.学号=成绩表.学号where课程名=“大学语文”

注意：join连接多个表时，join的顺序要和On的顺序（相应的连接条件）正好相反.

2 嵌套查询

题目 3：检索学生中入学成绩高于平均入学成绩的学生姓名、学号、专业、入学成绩.

教学法：

（1）查询涉及的字段

(学生)姓名、学号、专业、入学成绩

（2）字段来自哪些表

学生表

（3）表之间的关联

无（一个表）

（4）查询条件分析

入学成绩高于平均入学成绩：先利用avg()函数计算学生的平均入学成绩，再将每个学生的入学成绩与之比较.

最后，编写SQL语句为：select姓名，学号，专业，入学成绩from学生表where入学成绩>（select avg(入学成绩) from学生表）

题目 4：检索每个职工经手的具有最高总金额的订购单信息.

假设“订购单”表（职工号c(5)，订购单号 c(5)主索引，供应商号c(5)，订购日期 d，总金额 n(10，4)）[2]

教学法：

（1）查询涉及的字段

职工号、订购单号、供应商号、订购日期、总金额

（2）字段来自哪些表

订购单表

（3）表之间的关联

无（一个表）

（4）查询条件分析

每个职工经手的最高总金额：这个查询中外层查询和内层查询使用同一订购单表，所以给它们分别指定别名out1和in1.用外层查询提供out1中的每个记录的职工号值给内层查询用，内层查询利用这个职工号值确定该职工经手的具有最高总金额的订购单的总金额，然后外层查询再根据out1的同一记录的总金额值与该总金额值进行比较，如果相等，则该记录被选择.

最后，编写SQL语句为：select out1.职工号，out1.订购单号，out1.供应商号，out1.订购日期，out1.总金额 from订购单 out1 where 总金额=(select max(总金额) from 订购单 in1 where out1.职工号=in1.职工号)

3 其他查询

设有学生表（学号，姓名，性别，出生日期）和选课表（学号，课程号，成绩），并假定学号的第3、4位为专业代码.

题目5：计算各专业学生选修课程号为“101”课程的平均成绩.

教学法：

（1）查询涉及的字段（隐含在表中）

专业：subs(学号，3，2)

平均成绩：avg(成绩)

（2）字段来自哪些表

选课表

（3）表之间的关联

无（一个表）

（4）查询条件分析

各专业学生选修课程号为“101”：用group by短语将专业分组，课程号为“101”。

最后，编写SQL语句为：select subs(学号，3，2) as 专业，avg(成绩) as 平均成绩 from 选课表where 课程号="101" group by 1

题目6：查询选修课程号为"101"课程得分最高的学生学号、姓名.

教学法：

（1）查询涉及的字段

学生学号、姓名

（2）字段来自哪些表

学生表、选课表

（3）表之间的关联

两个表中的学号

（4）查询条件分析

找出选修课程号为"101"的所有成绩，利用 all找出成绩得分最高的；并且课程号="101"。

最后，编写SQL语句为：select 学生表.学号，姓名 from 学生表，选课表 where 学生表.学号=选课表.学号 and 课程号="101" and 成绩>=all(select 成绩 from 选课表 where 课程号="101")

题目 7：检索选修的每门课程的成绩都高于或等于85分的学生的学号、姓名和性别.

教学法：

（1）查询涉及的字段

学生的学号、姓名和性别

（2）字段来自哪些表

学生表、选课表

（3）表之间的关联

两个表中的学号

（4）查询条件分析

查找成绩小于85分的选课信息，用not exists找出每门课程的成绩都高于或等于85分。

最后，编写SQL语句为：select 学号，姓名，性别 from 学生表 where not exists (select * from选课表 where 选课表.学号=学生表.学号 and 成绩<85 )

4 总 结

在该方法中，首先仔细观察找出题目中出现的所有字段；然后观察这些字段来自哪些表；如果来自两个表及以上，则要看这些表是否有关联，如果没有关联，则要寻找中间表将这些表关联起来；接下来，分析查询条件，利用计算检索的函数、查询中常用的运算符等写出查询条件；最后编写SQL语句.

在课程教学的最后安排一些相关的练习加强学生用该方法编写复杂查询语句的能力，进而巩固对该方法的理解和掌握，要特别强调对查询条件的分析.

[1]周永恒.Visual FoxPro基础教程：第 3版[M].北京：高等教育出版社，2013.

[2]教育部考试中心.全国计算机等级考试二级教程——Visual FoxPro数据库程序设计[M].北京：高等教育出版社，2011.