基于數(shù)據(jù)庫模式導(dǎo)航的select多表查詢教學(xué)法

摘要:查詢語句select是SQL的教學(xué)重點(diǎn)。基于關(guān)系自然聯(lián)接運(yùn)算的工作原理,本文提出借助關(guān)系數(shù)據(jù)庫模式圖中表之間的關(guān)聯(lián),來“導(dǎo)航”編寫select查詢語句方法,借助導(dǎo)航提示功能,在待求問題和select語句間構(gòu)成映射,通過已知條件沿?cái)?shù)據(jù)表間的關(guān)聯(lián)來求未知,最終得到正確的select語句代碼。本教學(xué)法在實(shí)踐中取得良好效果。

關(guān)鍵詞:SQL查詢;教學(xué)法;數(shù)據(jù)庫模式;select語句;自然聯(lián)接

中圖分類號(hào):G642 文獻(xiàn)標(biāo)識(shí)碼:A

SQL是關(guān)系數(shù)據(jù)庫的標(biāo)準(zhǔn)查詢語言,查詢語句select是SQL的教學(xué)重點(diǎn),它具有功能強(qiáng),結(jié)構(gòu)復(fù)雜等特點(diǎn)。國內(nèi)多數(shù)大學(xué)已經(jīng)將SQL語句納入財(cái)經(jīng)、管理類的數(shù)據(jù)庫教學(xué)大綱中。江西財(cái)經(jīng)大學(xué)公共基礎(chǔ)課程“數(shù)據(jù)庫應(yīng)用”中,SQL查詢是教學(xué)的重要內(nèi)容。通過多年教學(xué)摸索,我們總結(jié)出基于數(shù)據(jù)庫模式導(dǎo)航的select多表查詢教學(xué)法。這里“導(dǎo)航”的含義是通過已知來求解未知,導(dǎo)航中方向的識(shí)別是借助數(shù)據(jù)庫模式圖。從教學(xué)反饋來看,學(xué)生能夠理解關(guān)系數(shù)據(jù)庫多表查詢的真正含義,教學(xué)效果良好。

1數(shù)據(jù)庫模式的核心概念

1.1關(guān)系數(shù)據(jù)庫

(1) 關(guān)系:一個(gè)關(guān)系就是一張二維表,通常將一個(gè)沒有重復(fù)行、重復(fù)列的二維表看成一個(gè)關(guān)系,每個(gè)關(guān)系都有一個(gè)關(guān)系名。和關(guān)系密不可分的兩個(gè)概念是描述關(guān)系行的元組(也稱為記錄)和描述關(guān)系列的屬性(也稱為字段)。只有屬性和元組確定后,才能夠唯一確定一個(gè)關(guān)系(也稱為表)。后面我們將混用關(guān)系和表這兩個(gè)術(shù)語。

(2) 主關(guān)鍵字:一個(gè)關(guān)系中能唯一區(qū)分、確定不同元組的屬性或?qū)傩越M合,稱為該關(guān)系的主關(guān)鍵字或主鍵PK (Primary Key)。主關(guān)鍵字對(duì)應(yīng)的屬性稱為主屬性。單個(gè)屬性組成的關(guān)鍵字稱為單關(guān)鍵字,多個(gè)屬性組合的關(guān)鍵字稱為組合關(guān)鍵字。需要強(qiáng)調(diào)的是,關(guān)鍵字的屬性值不能取“空值”。所謂空值就是“不知道”或“不確定”的值,因空值無法唯一區(qū)分和確定元組,它不能是組成關(guān)鍵字的屬性。

(3) 外部關(guān)鍵字:一個(gè)關(guān)系中某個(gè)屬性或?qū)傩越M合不是這個(gè)關(guān)系的主關(guān)鍵字,但卻是另一個(gè)關(guān)系的主關(guān)鍵字,稱此屬性或?qū)傩越M合為這個(gè)關(guān)系的外部關(guān)鍵字或外鍵FK (Foreign Key)。兩個(gè)關(guān)系之間的聯(lián)系是通過外部關(guān)鍵字實(shí)現(xiàn)的。

主關(guān)鍵字和外部關(guān)鍵字的作用是實(shí)現(xiàn)數(shù)據(jù)庫中多個(gè)關(guān)系間的完整性約束。

1.2關(guān)系完整性約束

關(guān)系完整性約束是為保證數(shù)據(jù)庫中多個(gè)關(guān)系的數(shù)據(jù)的正確性和相容性,對(duì)數(shù)據(jù)庫提出的某種約束條件或規(guī)則。關(guān)系完整性通常包括:實(shí)體完整性、參照完整性、域完整性和用戶定義完整性,其中實(shí)體完整性和參照完整性,是數(shù)據(jù)庫必須滿足的完整性約束條件。實(shí)際上,數(shù)據(jù)庫關(guān)系完整性約束實(shí)際上是定義數(shù)據(jù)必須滿足的基本要求,當(dāng)數(shù)據(jù)違反數(shù)據(jù)庫關(guān)系完整性約束時(shí),數(shù)據(jù)庫將拒絕違反關(guān)系完整性的數(shù)據(jù)的插入或更新,通過關(guān)系完整性可以保證數(shù)據(jù)庫中沒有垃圾數(shù)據(jù)。

或者說通過定義關(guān)系的完整性約束,使得數(shù)據(jù)庫有了一定的行為能力。當(dāng)用戶提交那些違背數(shù)據(jù)庫關(guān)系完整性約束的數(shù)據(jù)時(shí),數(shù)據(jù)庫將拒絕用戶提交的操作,這樣保證數(shù)據(jù)庫中的數(shù)據(jù)是真實(shí)有效的。

(1) 實(shí)體完整性:實(shí)體完整性是指一個(gè)關(guān)系中不能存在兩個(gè)完全相同的記錄。實(shí)體完整性是通過關(guān)系的主關(guān)鍵字(PK)來實(shí)現(xiàn)的。

(2) 參照完整性:關(guān)系數(shù)據(jù)庫中通常都包含多個(gè)存在相互聯(lián)系的關(guān)系(表),關(guān)系與關(guān)系之間的聯(lián)系是通過公共屬性來實(shí)現(xiàn)的。所謂公共屬性(聯(lián)接屬性),它是一個(gè)關(guān)系R(稱為被參照關(guān)系或目標(biāo)關(guān)系,常被稱為一表)的主關(guān)鍵字,同時(shí)又是另一關(guān)系K(稱為參照關(guān)系,常被稱為多表)的外部關(guān)鍵字。所謂參照完整性是指參照關(guān)系K中外部關(guān)鍵字的取值必須與被參照關(guān)系R中某元組主關(guān)鍵字的值相同,否則違反了參照完整性約束。

可以看到,主關(guān)鍵字實(shí)現(xiàn)了對(duì)單個(gè)關(guān)系的不含重復(fù)元組的約束,外部關(guān)鍵字實(shí)現(xiàn)了具有公共屬性的兩個(gè)關(guān)系間的約束。

1.3關(guān)系運(yùn)算

關(guān)系運(yùn)算中,輸入是一個(gè)或多個(gè)關(guān)系,輸出是經(jīng)過關(guān)系運(yùn)算后得到的一個(gè)新關(guān)系。常見關(guān)系運(yùn)算包括:選擇、投影、自然聯(lián)接。由于選擇和投影運(yùn)算只要求一個(gè)輸入關(guān)系,故稱為一元關(guān)系運(yùn)算符,而自然聯(lián)接運(yùn)算要求二個(gè)輸入關(guān)系,故稱為二元關(guān)系運(yùn)算符。

(1)選擇運(yùn)算:給定一個(gè)關(guān)系,從中篩選出滿足某種條件的記錄(或元組)的過程稱為選擇。(2)投影運(yùn)算:給定一個(gè)關(guān)系,從中只檢索期望得到的字段(或?qū)傩?的過程稱為投影。(3)自然聯(lián)接運(yùn)算:不同于選擇、投影運(yùn)算僅需一個(gè)輸入關(guān)系,二元自然聯(lián)接運(yùn)算有前提條件,既參與自然聯(lián)接運(yùn)算的兩個(gè)輸入關(guān)系間必須有一個(gè)公共的屬性(稱為聯(lián)接屬性),在一個(gè)關(guān)系(稱為一表)中它是主鍵,而在另一個(gè)關(guān)系(稱為多表)中它是外鍵。自然聯(lián)接運(yùn)算的結(jié)果:在屬性上是兩個(gè)參與運(yùn)算關(guān)系的屬性疊加(部分系統(tǒng)會(huì)剔除重復(fù)的聯(lián)接屬性);在元組上是在多表元組的基礎(chǔ)上,擴(kuò)展與一表聯(lián)接屬性取值相同的對(duì)應(yīng)一表元組數(shù)據(jù)值,既先取一條多表元組,再以該元組的聯(lián)接屬性取值為條件,搜索一表中的元組,當(dāng)一表的聯(lián)接屬性與已知條件相同時(shí),將該一表的元組拼接到給定多表元組上,形成一條新關(guān)系中的元組。如此反復(fù)對(duì)多表元組進(jìn)行相同操作,最后得到新的關(guān)系表。可以認(rèn)為是一表被融合到了多表。自然聯(lián)接操作是關(guān)系數(shù)據(jù)庫多表查詢的理論基礎(chǔ)。

1.4數(shù)據(jù)庫模式圖實(shí)例

數(shù)據(jù)庫模式圖是從全局的角度來描述多個(gè)關(guān)系之間的實(shí)體完整性和參照完整性。關(guān)系之間的參照完整性約束連線構(gòu)成了關(guān)系間的導(dǎo)航圖。

圖1所示的是一個(gè)教學(xué)管理數(shù)據(jù)庫,它涉及4個(gè)關(guān)系,這些關(guān)系之間的完整性約束由施加在這些關(guān)系間的連線表示。各表中小鑰匙表示該表的主關(guān)鍵字。圖中不同關(guān)系間的連線表示關(guān)系間的外鍵約束。圖1中,記號(hào)“+”表示主鍵中的壹,記號(hào) 表示外鍵中的多。兩表之間的連線表示兩表之間的參照完整性約束,既這兩個(gè)表之間可以進(jìn)行自然聯(lián)接運(yùn)算。而沒有連線的兩個(gè)表之間,則不能夠直接進(jìn)行自然聯(lián)接運(yùn)算。例如,“學(xué)生”關(guān)系中,“學(xué)號(hào)”是主關(guān)鍵字,則“學(xué)號(hào)”唯一確定“學(xué)生”關(guān)系中的一條記錄。實(shí)際情況是一個(gè)班級(jí)的“學(xué)生表”中可能存在姓名相同的人,由于他們是兩個(gè)不同的人,因此用“姓名”作為主關(guān)鍵字是不行的,但“學(xué)號(hào)”能唯一標(biāo)識(shí)他們。

如圖1所示,“成績表”作為參照關(guān)系,“學(xué)生表”作為被參照關(guān)系,“學(xué)號(hào)”是這兩個(gè)關(guān)系聯(lián)接屬性,則這兩個(gè)關(guān)系進(jìn)行自然聯(lián)接運(yùn)算的結(jié)果是形成一個(gè)新的關(guān)系。具體解釋為:新關(guān)系中,元組為“多”表的元組,即成績表的元組,屬性為兩個(gè)關(guān)系屬性疊加。新關(guān)系中,一條元組的“一”表的屬性取值是“一”表的聯(lián)接屬性值等于“多”表的聯(lián)接屬性值。新關(guān)系可以認(rèn)為是“一”表被合并到了多表。

2基于數(shù)據(jù)庫模式導(dǎo)航的SQL查詢教學(xué)法

2.1單表和兩表自然聯(lián)接的select語句編寫

對(duì)單表的運(yùn)算包括投影和選擇。講解重點(diǎn)是select條件構(gòu)造,既關(guān)系運(yùn)算符、邏輯運(yùn)算符、集合運(yùn)算in、字符匹配運(yùn)算符like和between…and…等。

兩個(gè)表的自然聯(lián)接運(yùn)算必須有參照完整性約束,這兩個(gè)表才能夠進(jìn)行自然聯(lián)接操作。

select字段列表;

from 兩個(gè)數(shù)據(jù)表;

where 條件;

group by 條件;

having條件;

order by 條件

注意,一定要求規(guī)范select語句格式。分行使得select語句易于理解,縮格表示被縮格的子句是上面語句的子部分。

兩個(gè)表的自然聯(lián)接運(yùn)算重點(diǎn)是where條件中如何反映兩個(gè)表的聯(lián)接屬性。這里方法是一表的聯(lián)接屬性必須等于多表的聯(lián)接屬性,一般表述為:

where 一表. 聯(lián)接屬性 = 多表.聯(lián)接屬性;

例如,學(xué)生表和成績表的自然聯(lián)接運(yùn)算,對(duì)應(yīng)的select語句為:

select *;

from 學(xué)生表,成績表;

where學(xué)生表 . 學(xué)號(hào) =成績表. 學(xué)號(hào)

2.2多表查詢的select編寫方法

多表查詢可以認(rèn)為是兩表自然聯(lián)接例子的擴(kuò)展,首先是其中兩個(gè)表實(shí)現(xiàn)自然聯(lián)接運(yùn)算形成一個(gè)新的表,然后這個(gè)新表再和其它表格進(jìn)行自然聯(lián)接運(yùn)算,又形成一個(gè)新的表,如此反復(fù)最終形成一個(gè)表。其一般形式為:

select字段列表;

from 多個(gè)數(shù)據(jù)表;

where 條件;

group by 條件;

having條件;

order by 條件

由于兩個(gè)具有主、外鍵約束的表之間自然聯(lián)接的結(jié)果是一個(gè)新表,其字段為這兩個(gè)表字段的疊加,記錄為多表的記錄。當(dāng)給的查詢涉及多個(gè)數(shù)據(jù)表時(shí),通過給定的已知,沿?cái)?shù)據(jù)表間的關(guān)聯(lián)來求未知,這就是基于數(shù)據(jù)庫模式的導(dǎo)航select編寫方法。一個(gè)更為簡單的方法是在數(shù)據(jù)模式的基礎(chǔ)上使用填字格方式,具體為:

select字段列表:要求輸出的字段出現(xiàn)在“字段列表”。

from 數(shù)據(jù)表列表:將所有具備輸出字段的數(shù)據(jù)表加入到“數(shù)據(jù)表列表”,如果這樣選擇的多個(gè)數(shù)據(jù)表在給定的數(shù)據(jù)模式中不存在關(guān)聯(lián),則必須將關(guān)聯(lián)這些數(shù)據(jù)表的中間數(shù)據(jù)表也加入到“數(shù)據(jù)表列表”,雖然它們不需要輸出字段值。

where 條件:使用“表名.主鍵 =表名.外鍵”實(shí)現(xiàn)二表間的關(guān)聯(lián),多表間的聯(lián)接使用“表名.主鍵 =表名.外鍵 and 表名.主鍵 =表名.外鍵”方式,最后再“and已知條件”。

下面,我們通過一個(gè)例子來說明求解過程。

案例:求計(jì)算機(jī)科學(xué)技術(shù)2003-01班的所有成績單。

分析:“計(jì)算機(jī)科學(xué)技術(shù)2003-01班”對(duì)應(yīng)為“班級(jí)表”中的“專業(yè)名稱”,這是已知條件。待求為“學(xué)生表”的“姓名”;“課程”表的“課程號(hào)、課程名”;“成績表”的“成績”。由此得到對(duì)應(yīng)的select語句為:

select 姓名,課程表.課程號(hào),課程名,成績;

from 學(xué)生表,成績表,課程表,班級(jí)表;

where 學(xué)生表.學(xué)號(hào) = 成績表.學(xué)號(hào);

and 課程表.課程號(hào) = 成績表.課程號(hào);

and 班級(jí)表.班級(jí)號(hào) = 學(xué)生表.班級(jí)號(hào);

and 專業(yè)名稱 = "計(jì)算機(jī)科學(xué)技術(shù)2003-01班"

3結(jié)語

數(shù)據(jù)庫模式圖就是數(shù)據(jù)庫管理員預(yù)先在數(shù)據(jù)庫中定義的行為模式。本文引入“數(shù)據(jù)庫導(dǎo)航”概念,讓學(xué)生自己學(xué)會(huì)利用數(shù)據(jù)庫模式圖,通過已知來求未知。將select含多表的查詢轉(zhuǎn)換為兩兩表間的自然聯(lián)接運(yùn)算,進(jìn)而,變成兩個(gè)表的自然聯(lián)接運(yùn)算,最終,轉(zhuǎn)換成為一個(gè)表的投影和選擇操作。這樣,學(xué)生能夠理解并編寫正確的SQL查詢語句。

參考文獻(xiàn):

[1] 黎升洪,楊波,沈波. Visual FoxPro面向?qū)ο蟪绦蛟O(shè)計(jì)教程[M]. 2版. 北京:科學(xué)出版社,2007.

[2] 王珊,薩師煊. 數(shù)據(jù)庫系統(tǒng)概論[M]. 4版. 北京:高等教育出版社,2006.

Didactics of Multi-Tables Select Sentence in SQL Based on the Navigation of Database Scheme

LI Sheng-hong

(School of Information & Technology, Jiangxi University of Finance & Economics, Nanchang 330013, China)

Abstract: The didactics of select sentence in SQL is a keystone. A new didactics is proposed for the didactics based on the relationships, “navigation”, among the tables of a database scheme according to the principle of natural joint in relationship algebra. The mapping between a problem and a select sentence can be constructed by means of the navigation between tables, where a solution can be solved from a given condition. The effect of the didactics is remarkable.

Key words: SQL query; didactics; database scheme; select sentence in SQL; natural joint