VF中SQL復(fù)雜查詢的四步教學(xué)法研究

賈麗萍

（山西師范大學(xué)臨汾學(xué)院，山西臨汾 041000）

Visual FoxPro是一個(gè)集數(shù)據(jù)庫(kù)技術(shù)與程序設(shè)計(jì)為一體的關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)開發(fā)軟件，在眾多的數(shù)據(jù)庫(kù)系統(tǒng)軟件中有著不可替代的作用，長(zhǎng)久以來享有“大眾數(shù)據(jù)庫(kù)”的美譽(yù).

SQL是結(jié)構(gòu)化查詢語言，是操作關(guān)系數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)數(shù)據(jù)庫(kù)查詢語言，提供了關(guān)系數(shù)據(jù)庫(kù)定義、數(shù)據(jù)操作和數(shù)據(jù)查詢等功能.其中數(shù)據(jù)查詢是SQL的核心，也是教學(xué)的重點(diǎn)和難點(diǎn).本文主要針對(duì)SQL中的復(fù)雜查詢，從查詢涉及的字段、字段來自哪些表、表之間的關(guān)聯(lián)、查詢條件分析四個(gè)方面闡述了如何利用四步法編寫復(fù)雜的查詢語句.

1 多表聯(lián)接查詢

假設(shè)“教學(xué)管理”數(shù)據(jù)庫(kù)中有5個(gè)表，分別是學(xué)生表（學(xué)號(hào)c(7)主索引，姓名c(8)，性別c(2)，出生日期d，專業(yè)c(10)普通索引，入學(xué)成績(jī)n(5，1)，貸款否l，照片g，簡(jiǎn)歷m），任課表（課程代號(hào)c(5)普通索引，教師代號(hào)c(5)普通索引）其中課程代號(hào)+教師代號(hào)為主索引，課程表（課程代號(hào)c(5)主索引，課程名c(16)普通索引，周學(xué)時(shí)n(1，0)，學(xué)分n(1，0)），教員表（教師代號(hào)c(5)主索引，姓名c(8)，性別c(2)，出生日期d，職稱c(6)），成績(jī)表（學(xué)號(hào)c(7)普通索引，課程代號(hào)c(5)普通索引，平時(shí)n(3，0)，期中n(3，0)，期末n(3，0)）其中學(xué)號(hào)+課程代號(hào)為主索引[1].

題目1：檢索所有選修了“大學(xué)語文”課程的學(xué)生姓名、學(xué)號(hào)和課程名.

教學(xué)法：

（1）查詢涉及的字段

（學(xué)生）姓名、學(xué)號(hào)、課程名

（2）字段來自哪些表

學(xué)生表、課程表

（3）表之間的關(guān)聯(lián)

學(xué)生表、課程表之間沒有關(guān)聯(lián).應(yīng)該尋找中間表將這兩個(gè)表關(guān)聯(lián)起來.觀察數(shù)據(jù)庫(kù)中的表，分析得出：學(xué)生表通過成績(jī)表和課程表關(guān)聯(lián).

（4）查詢條件分析

課程名=“大學(xué)語文”

最后，編寫SQL語句為：select姓名，學(xué)生表.學(xué)號(hào)，課程名from學(xué)生表，成績(jī)表，課程表where學(xué)生表.學(xué)號(hào)=成績(jī)表.學(xué)號(hào) and課程表.課程代號(hào)=成績(jī)表.課程代號(hào)and課程名=“大學(xué)語文”，或者也可編寫為：select姓名，學(xué)生表.學(xué)號(hào)，課程名from學(xué)生表join成績(jī)表join課程表on課程表.課程代號(hào)=成績(jī)表.課程代號(hào) on學(xué)生表.學(xué)號(hào)=成績(jī)表.學(xué)號(hào)where課程名=“大學(xué)語文”.

題目 2：檢索選修“大學(xué)語文”課程的學(xué)生姓名、課程名和教師姓名.

教學(xué)法：

（1）查詢涉及的字段

(學(xué)生)姓名、課程名、教師姓名

（2）字段來自哪些表

學(xué)生表、課程表、教員表

（3）表之間的關(guān)聯(lián)

學(xué)生表、課程表、教員表之間沒有關(guān)聯(lián).應(yīng)該尋找中間表將這三個(gè)表關(guān)聯(lián)起來.觀察數(shù)據(jù)庫(kù)中的表，分析得出：學(xué)生表通過成績(jī)表和課程表關(guān)聯(lián)，教員表通過任課表和課程表關(guān)聯(lián).

（4）查詢條件分析

課程名=“大學(xué)語文”

最后，編寫SQL語句為：select學(xué)生表.姓名as學(xué)生姓名，課程名，教員表.姓名as教師姓名from學(xué)生表，成績(jī)表，課程表，教員表，任課表 where學(xué)生表.學(xué)號(hào)=成績(jī)表.學(xué)號(hào) and課程表.課程代號(hào)=成績(jī)表.課程代號(hào)and教員表.教師代號(hào)=任課表.教師代號(hào)and課程表.課程代號(hào)=任課表.課程代號(hào)and課程名=“大學(xué)語文”，或者也可編寫為：select學(xué)生表.姓名as學(xué)生姓名，課程名，教員表.姓名as教師姓名from學(xué)生表join成績(jī)表join課程表join任課表join教員表 on教員表.教師代號(hào)=任課表.教師代號(hào)on課程表.課程代號(hào)=任課表.課程代號(hào)on課程表.課程代號(hào)=成績(jī)表.課程代號(hào) on學(xué)生表.學(xué)號(hào)=成績(jī)表.學(xué)號(hào)where課程名=“大學(xué)語文”

注意：join連接多個(gè)表時(shí)，join的順序要和On的順序（相應(yīng)的連接條件）正好相反.

2 嵌套查詢

題目 3：檢索學(xué)生中入學(xué)成績(jī)高于平均入學(xué)成績(jī)的學(xué)生姓名、學(xué)號(hào)、專業(yè)、入學(xué)成績(jī).

教學(xué)法：

（1）查詢涉及的字段

(學(xué)生)姓名、學(xué)號(hào)、專業(yè)、入學(xué)成績(jī)

（2）字段來自哪些表

學(xué)生表

（3）表之間的關(guān)聯(lián)

無（一個(gè)表）

（4）查詢條件分析

入學(xué)成績(jī)高于平均入學(xué)成績(jī)：先利用avg()函數(shù)計(jì)算學(xué)生的平均入學(xué)成績(jī)，再將每個(gè)學(xué)生的入學(xué)成績(jī)與之比較.

最后，編寫SQL語句為：select姓名，學(xué)號(hào)，專業(yè)，入學(xué)成績(jī)from學(xué)生表where入學(xué)成績(jī)>（select avg(入學(xué)成績(jī)) from學(xué)生表）

題目 4：檢索每個(gè)職工經(jīng)手的具有最高總金額的訂購(gòu)單信息.

假設(shè)“訂購(gòu)單”表（職工號(hào)c(5)，訂購(gòu)單號(hào) c(5)主索引，供應(yīng)商號(hào)c(5)，訂購(gòu)日期 d，總金額 n(10，4)）[2]

教學(xué)法：

（1）查詢涉及的字段

職工號(hào)、訂購(gòu)單號(hào)、供應(yīng)商號(hào)、訂購(gòu)日期、總金額

（2）字段來自哪些表

訂購(gòu)單表

（3）表之間的關(guān)聯(lián)

無（一個(gè)表）

（4）查詢條件分析

每個(gè)職工經(jīng)手的最高總金額：這個(gè)查詢中外層查詢和內(nèi)層查詢使用同一訂購(gòu)單表，所以給它們分別指定別名out1和in1.用外層查詢提供out1中的每個(gè)記錄的職工號(hào)值給內(nèi)層查詢用，內(nèi)層查詢利用這個(gè)職工號(hào)值確定該職工經(jīng)手的具有最高總金額的訂購(gòu)單的總金額，然后外層查詢?cè)俑鶕?jù)out1的同一記錄的總金額值與該總金額值進(jìn)行比較，如果相等，則該記錄被選擇.

最后，編寫SQL語句為：select out1.職工號(hào)，out1.訂購(gòu)單號(hào)，out1.供應(yīng)商號(hào)，out1.訂購(gòu)日期，out1.總金額 from訂購(gòu)單 out1 where 總金額=(select max(總金額) from 訂購(gòu)單 in1 where out1.職工號(hào)=in1.職工號(hào))

3 其他查詢

設(shè)有學(xué)生表（學(xué)號(hào)，姓名，性別，出生日期）和選課表（學(xué)號(hào)，課程號(hào)，成績(jī)），并假定學(xué)號(hào)的第3、4位為專業(yè)代碼.

題目5：計(jì)算各專業(yè)學(xué)生選修課程號(hào)為“101”課程的平均成績(jī).

教學(xué)法：

（1）查詢涉及的字段（隱含在表中）

專業(yè)：subs(學(xué)號(hào)，3，2)

平均成績(jī)：avg(成績(jī))

（2）字段來自哪些表

選課表

（3）表之間的關(guān)聯(lián)

無（一個(gè)表）

（4）查詢條件分析

各專業(yè)學(xué)生選修課程號(hào)為“101”：用group by短語將專業(yè)分組，課程號(hào)為“101”。

最后，編寫SQL語句為：select subs(學(xué)號(hào)，3，2) as 專業(yè)，avg(成績(jī)) as 平均成績(jī) from 選課表where 課程號(hào)="101" group by 1

題目6：查詢選修課程號(hào)為"101"課程得分最高的學(xué)生學(xué)號(hào)、姓名.

教學(xué)法：

（1）查詢涉及的字段

學(xué)生學(xué)號(hào)、姓名

（2）字段來自哪些表

學(xué)生表、選課表

（3）表之間的關(guān)聯(lián)

兩個(gè)表中的學(xué)號(hào)

（4）查詢條件分析

找出選修課程號(hào)為"101"的所有成績(jī)，利用 all找出成績(jī)得分最高的；并且課程號(hào)="101"。

最后，編寫SQL語句為：select 學(xué)生表.學(xué)號(hào)，姓名 from 學(xué)生表，選課表 where 學(xué)生表.學(xué)號(hào)=選課表.學(xué)號(hào) and 課程號(hào)="101" and 成績(jī)>=all(select 成績(jī) from 選課表 where 課程號(hào)="101")

題目 7：檢索選修的每門課程的成績(jī)都高于或等于85分的學(xué)生的學(xué)號(hào)、姓名和性別.

教學(xué)法：

（1）查詢涉及的字段

學(xué)生的學(xué)號(hào)、姓名和性別

（2）字段來自哪些表

學(xué)生表、選課表

（3）表之間的關(guān)聯(lián)

兩個(gè)表中的學(xué)號(hào)

（4）查詢條件分析

查找成績(jī)小于85分的選課信息，用not exists找出每門課程的成績(jī)都高于或等于85分。

最后，編寫SQL語句為：select 學(xué)號(hào)，姓名，性別 from 學(xué)生表 where not exists (select * from選課表 where 選課表.學(xué)號(hào)=學(xué)生表.學(xué)號(hào) and 成績(jī)<85 )

4 總 結(jié)

在該方法中，首先仔細(xì)觀察找出題目中出現(xiàn)的所有字段；然后觀察這些字段來自哪些表；如果來自兩個(gè)表及以上，則要看這些表是否有關(guān)聯(lián)，如果沒有關(guān)聯(lián)，則要尋找中間表將這些表關(guān)聯(lián)起來；接下來，分析查詢條件，利用計(jì)算檢索的函數(shù)、查詢中常用的運(yùn)算符等寫出查詢條件；最后編寫SQL語句.

在課程教學(xué)的最后安排一些相關(guān)的練習(xí)加強(qiáng)學(xué)生用該方法編寫復(fù)雜查詢語句的能力，進(jìn)而鞏固對(duì)該方法的理解和掌握，要特別強(qiáng)調(diào)對(duì)查詢條件的分析.

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

[2]教育部考試中心.全國(guó)計(jì)算機(jī)等級(jí)考試二級(jí)教程——Visual FoxPro數(shù)據(jù)庫(kù)程序設(shè)計(jì)[M].北京：高等教育出版社，2011.