第8章SQL語言、查詢與視圖

第八章SQL語言、查詢與視圖第八章SQL語言、查詢與視圖本章主要介紹關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL，并在此基礎(chǔ)上介紹查詢與視圖。通過查詢設(shè)計(jì)器和視圖設(shè)計(jì)器，可以從數(shù)據(jù)庫中提取出目前用戶所需要的數(shù)據(jù)，并保存起來，以便日后使用。而借助SQL語言可以更深入地操作數(shù)據(jù)庫，建立更高效與復(fù)雜的查詢與視圖。第八章SQL語言、查詢與視圖8.1結(jié)構(gòu)化查詢語言SQLSQL(StructuredQueryLanguage)語言是1974年由Boyce和Chamberlin提出的，在IBM公司的SanJoseResearchLaboratory研制的SystemR上實(shí)現(xiàn)了這種語言。VisualFoxPro不支持全部的SQL語言，僅支持SELECT、CREAT、ALTER、INSERT、DELETE、UPDATE等幾種命令，其功能如下：第八章SQL語言、查詢與視圖8.1.1SQL的數(shù)據(jù)定義1、定義表命令格式：CREATTABLE<表名>(<字段名1><類型>(<長度>[，<小數(shù)位數(shù)>])[，<字段名2><類型>(<長度>[，<小數(shù)位數(shù)>])]例8.1用SQL創(chuàng)建學(xué)生表

CREATTABLE學(xué)生(學(xué)號C(6)，姓名C(8)，性別L，出生日期D，獎學(xué)金N(6,2))說明：新表將在最低的可用工作區(qū)打開，并可通過它的別名來訪問。新表以獨(dú)占方式打開，與SETEXCLUSIVE的設(shè)置無關(guān)。第八章SQL語言、查詢與視圖2、修改表命令格式：ALTERTABLE<表名>ADD|ALTER[COLUMN]字段名1類型[(字段寬度[，小數(shù)位數(shù)])]參數(shù)說明：ADD[COLUMN]字段名1：指定要添加的字段名，一個(gè)單獨(dú)的表可包含最多255個(gè)字段，如果一個(gè)或更多的字段允許空值，該限制數(shù)降至254上字段。ADD/ALTER[COLUMN]字段名1：指定要修改的字段名。例8.2向?qū)W生表添加年齡字段ALTERTABLE學(xué)生ADD年齡N(2)第八章SQL語言、查詢與視圖8.1.2SQL的數(shù)據(jù)修改VisualFoxPro提供了三條命令進(jìn)行數(shù)據(jù)操作。1、INSERT命令格式1：INSERTINTO表名[(字段名1[，字段名2，])]VALUES(表達(dá)式1[，表達(dá)式2，])第八章SQL語言、查詢與視圖格式2：INSERTINTO表名FROMARRAY數(shù)組名|FROMMEMVAR說明：數(shù)組名，指定輸入到新記錄的字段值。如果存在字段名參數(shù)表，則數(shù)組各個(gè)元素的值依次賦予指定的字段名；否則，數(shù)組各個(gè)元素的值依次賦予表結(jié)構(gòu)定義的字價(jià)。如果數(shù)組元素的個(gè)數(shù)多于字段的個(gè)數(shù)，則忽略多余的數(shù)組元素；如果數(shù)組元素的個(gè)數(shù)少于字段的個(gè)數(shù)，則多余的字段要么為表設(shè)計(jì)中定義的默認(rèn)值，要么為空值。例8.3向?qū)W生表中插入一條記錄。INSERTINTO學(xué)生(學(xué)號，姓名，性別)VALUES(“990203”，“楊陽”，.T.)第八章SQL語言、查詢與視圖2、DELETE命令DELETE命令給表中的記錄加上刪除標(biāo)記。格式：DELETEFROM[數(shù)據(jù)庫名!]表名[WHERE過濾條件1[AND|OR過濾條件2]]例8.4刪除學(xué)生表中的李華DELETEFROM學(xué)生WHERE姓名=“李華”第八章SQL語言、查詢與視圖3、UPDATE命令UPDATE命令用新值替換表中現(xiàn)有記錄的值。格式：UPDATE[數(shù)據(jù)庫名!]表名SET字段名1=表達(dá)式1[，字段名2=表達(dá)式2]WHERE過濾條件1[AND|OR過濾條件2]]例8.5將學(xué)生表中林虹的獎學(xué)金增加100元。UPDATE學(xué)生SET獎學(xué)金=獎學(xué)金+100WHERE姓名=“林虹”第八章SQL語言、查詢與視圖8.1.3SQL的數(shù)據(jù)查詢格式：SELECT[ALL|DISTINCT]<字段名>[AS列名][字段名[AS列名]，]FROM<表或視圖名1>[[INNER|LEFT[OUTER]|RIGHT[OUTER]|FULL[OUTER]JOIN[<表或視圖名2>]ON<聯(lián)接條件>]][WHERE<條件表達(dá)式>[AND<條件表達(dá)式>]][GROUPBY<分組表達(dá)式>[，<分組表達(dá)式>，]][HAVING<條件表達(dá)式>][ORDERBY<字段名>[ASC|DESC]][INTO<輸出目標(biāo)>]|[TOFILE<文件>[ADDITIVE]]|[TOPRINTER[PROMPT]]|[TOSCREEN]第八章SQL語言、查詢與視圖說明：SELECT后緊跟的是將要查詢的數(shù)據(jù)；FROM子句指出查詢數(shù)據(jù)的來源，即屬于哪個(gè)表；WHERE子句表示查詢的數(shù)據(jù)滿足的條件；GROUP子句說明查詢結(jié)果的分組依據(jù)；HAVING子句限定分組所需滿足的條件；ORDERBY子句用來對查詢的結(jié)果進(jìn)行排序。第八章SQL語言、查詢與視圖1、簡單查詢(1)只有FROM子句查詢指定列例8.6查詢學(xué)生表中的學(xué)生的年齡SELCET姓名，年齡FROM學(xué)生查詢?nèi)苛辛谐霰碇械乃凶侄?，一是在SELECT后列出所有字段名，一是使用“*”。(注意，有的數(shù)據(jù)庫管理系統(tǒng)不支持“*”)例8.7查詢學(xué)生表中全體學(xué)生的所有信息。SELCECT*FROM學(xué)生或SELECT學(xué)號，姓名，性別，出生日期，簡歷第八章SQL語言、查詢與視圖(2)WHERE子句說明：關(guān)鍵字LIKE可以用來進(jìn)行字符串的匹配，語法格式如下：[NOT]LIKE‘<匹配串>’{ESCAPE‘<換碼字符>”}在匹配串中可以使用通配符“&”，“_”。%代表任意長度(長度可為0)的字符串_(下劃線)代表任意單個(gè)字符第八章SQL語言、查詢與視圖例8.8查詢9901班的全體學(xué)生SELECT*FORM學(xué)生WHERESUBSTR(學(xué)號,1,4)="9901"例8.9查詢成績在70到80之間的學(xué)生SELECT學(xué)號，成績FROM成績WHERE成績BETWEEN70AND80例8.10查詢20歲和22歲的學(xué)生名單SELECT姓名FROM學(xué)生WHERE年齡IN(20,22)例8.11查詢姓“王”的學(xué)生SELECT姓名FROM學(xué)生WHERE姓名LIKE“王%”第八章SQL語言、查詢與視圖(3)空值查詢例8.12查詢?nèi)笨紝W(xué)生的學(xué)號和課程號SELECT學(xué)號，課程號FROM成績WHERE成績ISNULL(4)GROUPBY子句將查詢結(jié)果按某一列或多列值分組，值相等的為一組。例8.13將學(xué)生表中的學(xué)生按年齡進(jìn)行分組，并統(tǒng)計(jì)各組的人數(shù)SELECT年齡，COUNT(姓名)FROM學(xué)生GROUPBY年齡第八章SQL語言、查詢與視圖(5)ORDERBY子句例8.14查詢獲得獎學(xué)金的學(xué)生姓名和獎學(xué)金，查詢結(jié)果按獎學(xué)金降序排序。SELECT姓名，獎學(xué)金FROM學(xué)生WHERE獎學(xué)金>0的ORDERBY獎學(xué)金DESC(6)計(jì)算列SELECT關(guān)鍵字后不僅可以跟字段名，還可以是表達(dá)式。例8.15查詢?nèi)w學(xué)生的姓名和年齡SELECT姓名，YEAR(DATE())-YEAR(出生日期)AS年齡FROM學(xué)生第八章SQL語言、查詢與視圖2、嵌套查詢例8.16查詢選修了“0001”課程的學(xué)生名單SELECT姓名FROM學(xué)生WHERE學(xué)號INSELECT學(xué)號FROM成績WHERE課程號=“0001”第八章SQL語言、查詢與視圖3、聯(lián)接查詢一個(gè)查詢同時(shí)涉及兩個(gè)以上的表，則稱為聯(lián)接查詢。聯(lián)接有4種類型，在FROM子句指明：INNERJOIN：內(nèi)部聯(lián)接，結(jié)果集中包含聯(lián)接條件左右的兩個(gè)表中滿足聯(lián)接條件的記錄。LEFTJOIN：左聯(lián)接，結(jié)果集中包含聯(lián)接條件左邊的表中所有記錄和聯(lián)接條件右邊的表中滿足聯(lián)接條件的記錄。RIGHTJOIN：右聯(lián)接，結(jié)果集中包含聯(lián)接條件右邊的表中所有記錄和聯(lián)接條件左邊的表中滿足聯(lián)接條件的記錄。FULLJOIN：完全聯(lián)接，結(jié)果集中包含兩個(gè)表中的所有記錄，不論是否滿足聯(lián)接條件。缺省為內(nèi)部聯(lián)接，除了四種連接外，還可以用WHERE條件實(shí)現(xiàn)查詢。第八章SQL語言、查詢與視圖例8.17查詢每個(gè)學(xué)生的選課成績(1)內(nèi)部聯(lián)接SELECT學(xué)生.*,成績.*FROM學(xué)生INNERJOIN成績;ON學(xué)生.學(xué)號=成績.學(xué)號(2)左聯(lián)接，除滿足條件的記錄外，左表中不滿足條件的記錄也出現(xiàn)在結(jié)果中SELECT學(xué)生.*,成績.*FROM學(xué)生LEFTJOIN成績;ON學(xué)生.學(xué)號=成績.學(xué)號(3)右聯(lián)接，除滿足條件的記錄外，右表中不滿足條件的記錄也出現(xiàn)在結(jié)果中SELECT學(xué)生.*,成績.*FROM學(xué)生RIGHTJOIN成績;ON學(xué)生.學(xué)號=成績.學(xué)號第八章SQL語言、查詢與視圖(4)完全聯(lián)接，結(jié)果包括兩個(gè)表中的所有記錄SELECT學(xué)生.*,成績.*FROM學(xué)生FULLJOIN成績;ON學(xué)生.學(xué)號=成績.學(xué)號(5)用WHERE子句實(shí)現(xiàn)，結(jié)果同內(nèi)部聯(lián)接。SELECT學(xué)生.*，成績.*FROM學(xué)生，成績WHERE學(xué)生.學(xué)號=成績.學(xué)號第八章SQL語言、查詢與視圖4、使用列函數(shù)SQL的列函數(shù)：注意：列函數(shù)中可以使用DISTINCT，表示計(jì)算時(shí)取消指定列中的重復(fù)值。在COUNT()函數(shù)中，“*”與DISTINCT不能同時(shí)使用。第八章SQL語言、查詢與視圖5、查詢結(jié)果的輸出定向：第八章SQL語言、查詢與視圖8.2建立查詢利用SQL命令可以方便、迅速地建立查詢，但是對初學(xué)者來說，還是復(fù)雜了些。在VisualFoxPro中，還可以使用查詢向?qū)Ш筒樵冊O(shè)計(jì)器來生成查詢文件。8.2.1查詢向?qū)Ц鶕?jù)第七章建立的數(shù)據(jù)庫，使用查詢向?qū)Ы⑷缦虏樵儯猴@示“學(xué)生”表的學(xué)號、姓名及選修課程的成績，按“學(xué)號”升序排序。第八章SQL語言、查詢與視圖1、打開查詢向?qū)г凇拔募辈藛沃羞x擇“新建”，在彈出的新建對話框中，在文件類型中選擇查詢，選擇向?qū)О粹o，彈出對話框，其中列出了3種向?qū)Ч┯脩暨x擇，查詢向?qū)?、交叉表向?qū)?、圖形向?qū)А＿x擇查詢向?qū)?，按“確定”按鈕，進(jìn)入查詢向?qū)У谝徊?。查詢向?qū)У谝徊剑哼x擇要查詢的表及字段。查詢向?qū)У诙剑簽楸斫㈥P(guān)系查詢向?qū)У谌剑涸O(shè)置聯(lián)接條件查詢向?qū)У谒牟剑汉Y選記錄第八章SQL語言、查詢與視圖查詢向?qū)У谖宀剑河涗浥判虿樵兿驅(qū)У诹剑合拗朴涗洸樵兿驅(qū)У谄卟剑和瓿杀４娌樵儯簝H保存建立的查詢保存并運(yùn)行查詢：保存查詢后立即運(yùn)行查詢保存查詢并在“查詢設(shè)計(jì)器”中修改最后，運(yùn)行查詢第八章SQL語言、查詢與視圖8.2.2查詢設(shè)計(jì)器使用查詢設(shè)計(jì)器的步驟如下：打開查詢設(shè)計(jì)器打開(或添加)數(shù)據(jù)庫或自由表選擇字段設(shè)置聯(lián)接篩選排序依據(jù)分組依據(jù)雜項(xiàng)第八章SQL語言、查詢與視圖1、打開查詢設(shè)計(jì)器在選擇“文件”菜單中選擇“新建”，在彈出的“新建”對話框的文件類型中選擇“查詢”，單擊“新建文件”按鈕，打開查詢設(shè)計(jì)器，同時(shí)還為查詢選擇表或者視圖，單擊“添加”按鈕添加到查詢設(shè)計(jì)器中。每添加一個(gè)新表，如果與查詢設(shè)計(jì)器中已有的表有相同字段，則系統(tǒng)提示在兩表中設(shè)置聯(lián)接條件，建立聯(lián)接的兩表間的聯(lián)接字段有直線相連。如果還想添加自由表，單擊“其他”按鈕。所有的表或視圖添加完畢，則可以通過選項(xiàng)卡來定義輸出結(jié)果，查詢設(shè)計(jì)器中包括字段、聯(lián)接、篩選、排序依據(jù)、分組依據(jù)、雜項(xiàng)6個(gè)選項(xiàng)卡。在查詢設(shè)計(jì)器中，可以在建立查詢的過程中查看查詢結(jié)果，單擊常用工具欄上的！按鈕或在“查詢”菜單中選擇“運(yùn)行查詢”命令即可。第八章SQL語言、查詢與視圖2、字段在該選項(xiàng)卡除了選擇源表中的各字段外，還可以為查詢添加統(tǒng)計(jì)項(xiàng)。3、聯(lián)接如果在創(chuàng)建查詢前已經(jīng)在表之間建立了關(guān)系，VisualFoxPro將自動創(chuàng)建相關(guān)的聯(lián)接，默認(rèn)為內(nèi)部聯(lián)接。如果沒有，在選擇表或視圖時(shí)，系統(tǒng)會提示建立聯(lián)接，聯(lián)接條件有4種類型，建立好聯(lián)接后，在聯(lián)接選項(xiàng)卡中顯示當(dāng)前已經(jīng)建立的聯(lián)接。在聯(lián)接選項(xiàng)卡中可以修改已建立的聯(lián)接、建立新的聯(lián)接及刪除聯(lián)接。第八章SQL語言、查詢與視圖4、篩選在篩選選項(xiàng)卡可以設(shè)置查詢條件。5、排序依據(jù)在該選項(xiàng)中設(shè)置查詢結(jié)果的輸出順序。6、分組依據(jù)所謂分組即是將一組類似的記錄壓縮成一個(gè)記錄，這樣就可以完成基于一組記錄的計(jì)算。分組還可以與其它函數(shù)聯(lián)合使用，如SUM、AVG、COUNT等。第八章SQL語言、查詢與視圖8.3視圖視圖是一個(gè)定制的虛擬表，其數(shù)據(jù)可以來源于一個(gè)表或多個(gè)表，或其他視圖。視圖本身并不保存任何數(shù)據(jù)。視圖可分為本地視圖和遠(yuǎn)程視圖。視圖是可更新的，修改視圖中的數(shù)據(jù)時(shí)，實(shí)際上是直接修改了其數(shù)據(jù)源中的數(shù)據(jù)。第八章SQL語言、查詢與視圖建立視圖有以下優(yōu)點(diǎn)：保障數(shù)據(jù)的安全性和完整性。簡化對數(shù)據(jù)庫的操作。從多角度分析同一數(shù)據(jù)。視圖兼有表和查詢的特點(diǎn)。第八章SQL語言、查詢與視圖8.3.1創(chuàng)建視圖創(chuàng)建視圖也有兩種方式：視圖向?qū)Ш鸵晥D設(shè)計(jì)器，另外，與查詢一樣還可以用SQLSELECT語言來創(chuàng)建視圖。一、視圖設(shè)計(jì)器的使用步驟：打開數(shù)據(jù)庫打開視圖設(shè)計(jì)器選擇字段設(shè)置聯(lián)接篩選排序依據(jù)分組依據(jù)更新條件雜項(xiàng)第八章SQL語言、查詢與視圖二、更新條件選項(xiàng)卡視圖設(shè)計(jì)器同查詢設(shè)計(jì)器很類似，只是多了一個(gè)“更新條件”選項(xiàng)卡，其它選項(xiàng)卡的使用同查詢設(shè)計(jì)器。1、表指定視圖中可以更新的表，系統(tǒng)默認(rèn)是“全部表”。2、“字段名”列表框顯示可標(biāo)志為關(guān)鍵字字段或可更新字段的字段名。第八章SQL語言、查詢與視圖在“字段名”列表框中列出了選定表的所有選定字段，在這些字段名的左面的兩列是標(biāo)記列，其中鑰匙列用來確定關(guān)鍵字字段，鉛筆列確定可更新字段。關(guān)鍵字段：若鑰匙列中顯示“√”標(biāo)記，則表明該字段是關(guān)鍵字段，用鼠標(biāo)單擊字段前的小方塊按鈕進(jìn)行設(shè)定?？筛伦侄危涸阢U筆列中顯示“√”標(biāo)記，則表明該字段是可更新的，也可以通過鼠標(biāo)單擊字段前的小方塊按鈕進(jìn)行設(shè)定。重置關(guān)鍵字：單擊該按鈕，系統(tǒng)自動使用每個(gè)表的主索引作為關(guān)鍵字，在鑰匙列加上“√”標(biāo)記。全部更新：選擇除了關(guān)鍵字字段以外的所有選定字段進(jìn)行更新，并對“字段名”列表框中鉛筆列有“√”標(biāo)記的字段發(fā)送SQL更新。系統(tǒng)通過關(guān)鍵字使視圖和源表相聯(lián)系。只有指定關(guān)鍵字后，系統(tǒng)才允許選擇通過視圖來更新源表的字