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.