第4章結構化查詢語言4.1SQL概述4.2數據查詢4.3數據操作4.4數據定義4.2數據查詢4.2.1select命令基本格式SELECT<*>|[DISTINCT]<查詢項>FROM<數據表>[WHERE<條件>][GROUPBY<分組表達式>[HAVING<篩選條件>][ORDERBY<排序項>[ASC|DESC]INTO<目的地>SELECT命令的基本結構是SELECT…FROM…WHERE，它包含輸出字段…數據來源…查詢條件。其中SELECT和FROM是必須的。說明：（1）SELECT子句:*表示選出的記錄中包括重復記錄，DISTINCT表示選出的記錄中不包括重復記錄。（2）FROM子句及其選項：用于指定查詢的表。（3）WHERE子句：WHERE子句中指定篩選條件。（4）GROUPBY子句：對記錄按<組表達式>值分組，常用于分組統計。（5）HAVING子句：當含有GROUPBY子句時，HAVING子句可用做記錄查詢的限制條件。（6）ORDERBY子句：指定查詢結果中記錄按<表達式>排序，默認升序。4.2.2簡單查詢主要基于單個表，可以有簡單的查詢條件。這樣的查詢用SELECT和FROM短語構成無條件查詢。或由SELECT、FROM和WHERE短語構成條件查詢。1.查詢部分字段例例：

找出成績表中學生的學號、姓名、計算機成績。SELECT學號,姓名,計算機FROM成績表2.使用distinct短句Distinct的作用是去掉查詢結果的重復值。例：列出所有學生姓名，去掉重名。SELECTDISTINCT姓名AS學生名單FROM學生3.查詢所有字段例：列出所有學生信息。SELECT*FROM學生&&“*”是通配符，表示所有字段4.條件查詢格式：select<數據項>from數據表where<條件表達式>例：列出所有姓陳的學生名單。SELECT姓名,學號FROM學生WHEREleft(名,2)=“陳”4.2.3帶特殊運算符的條件查詢WHERE子句中的條件表達，除了使用關系比較運算和邏輯運算符號之外，還可以使用特殊運算符號，如IN、BETWEEN…AND…和LIKE等。Between…and…在”…”和”…”之間例：

在學生表中檢索出入學成績在500~520分范圍內的學生信息。SELECT*FROM學生WHERE入學成績BETWEEN500AND520這個查詢條件等價與:(入學成績>=500)and(入學成績<=520)2.LikeLike是字符串匹配運算符，通配符”%”表示0個或多個字符，另外還有一個通配符”-”表示一個字符。例:從學生表中檢索出全部姓李的信息。SELECT*FROM學生WHERE姓名LIKE"李%“3.InIn運算符后面接一個集合，集合形式為（元素1，元素2，元素3，….)例:從學生表中檢索出福建、江西、湖南籍學生的信息SELECT*FROM學生WHERE姓名LIKE"李%“4.not4.2.4簡單的計算查詢COUNT——計數SUM——求和AVG——計算平均值MAX——計算最大值MIN——計算最小值例：將所有的學生數學成績四舍五入，只顯示學號、姓名和數學成績SELECT

學號，姓名，ROUND(數學,0)AS數學成績FROM

學生例：求出所有學生的數學成績平均分、最高分、最低分。

SELECTAVG(數學)AS數學成績平均分,MAX(數學)AS數學成績最高分,MIN(數學)AS數學成績最低分FROM學生4.2.5.分組統計（GROUP）查詢查詢結果可以分組，其格式是：GROUPBY<分組選項1>[,<分組選項2>…]其中<分組選項>可以是字段名，SQL函數表達式，也可以是列序號（最左邊為1）。例分別統計男女人數。SELECT性別,COUNT(性別)FROM學生GROUPBY性別例分別統計男女中少數民族學生人數。SELECT性別,COUNT(性別)FROM學生GROUPBY性別WHERE少數民族否4.2.6排序查詢輸出ORDERBY短語的格式如下：ORDERBY<排序項1>[ASC|DESC][,<排序項2>[ASC|DESC]…]可以按一列或多列排序，ASC：升序，DESC：降序。默認為ASC。例例對學生表，請輸出數學成績最高的前五名學生的信息SELECT*TOP5FROM學生ORDERBY數學DESC4.2.7簡單聯接查詢（多表查詢）

聯接查詢是基于多個表的查詢。表之間的聯系是通過字段值來體現的，這種字段通常稱為聯接字段。1、等值聯接與非等值聯接查詢當聯接運算符為“=”時，稱為等值聯接，使用其他運算符（>、<、>=、<=、?。r稱為非等值聯接。12例：查詢所有學生的成績單，要求給出學號、姓名、性別、計算機成績、數學成績。SELECTa.學號,a.姓名,b.計算機,b.數學FROM學生a,成績表b

WHEREa.學號=b.學號其中：a.學號=b.學號是聯接條件注：短語FROM學生a,表示選擇學生表，并將學生表的別名設為a;短語SELECTa.學號，表示取學生表的學號字段。說明：·當FROM之后有多個關系時，各個關系相互之間肯定有一種聯系，否則無法構成檢索表達式。

當FORM指定的不同關系中含有相同的屬性名時，必須加表名作為屬性名的前綴，以區別屬性所屬的關系。例如“a.學號”，“b.學號”。2.自聯接查詢一個表與其自已進行聯接，稱為自身聯接。例：列出成績表中，計算機成績高于數學成績的學生的學號、計算機成績和數學成績。

SELECTa.學號,a.計算機,a.數學FROM成績表a,成績bWHEREa.計算機>b.數學

ANDa.學號=b.學號

注：在自聯接時，對數據表必須使用別名。4.2.8超連接查詢

VisualFoxPro提供的SELECT命令，在FROM子句中提供一種稱之為聯接的子句。聯接分為內部聯接和外部聯接。外部聯接又分為左外聯接、右外聯接和全外聯接。

格式為：Select…from<數據表1>innerjoin|leftjoin|rightjoin|fulljoin<數據表2>on條件

1.內部聯接（InnerJoin）實際上，上面例子全部都是內部聯接。所謂內部聯接是指包括符合條件的每個表格中的記錄。也就是說是所有滿足聯接條件的記錄都包含在查詢結果中。例

列出少數民族學生的學號、姓名及計算機成績。SELECTa.學號,a.姓名,b.計算機FROM學生a,成績表bWHEREa.學號=b.學號ANDa.少數民族否如采用內部聯接方式？SELECTa.學號,a.姓名,b.計算機FROM

學生aINNERJOIN成績表bONa.學號=b.學號WHEREa.少數民族否所得到的結果完全相同2.外部聯接（OuterJoin）(1)左外聯接也叫左聯接（LeftJoin），其系統執行過程是左表的某條記錄與右表的所有記錄依次比較，若有滿足聯接條件的，則產生一個真實值記錄。若都不滿足，則產生一個含有NULL值的記錄。接著，左表的下一記錄與右表的所有記錄依次比較字段值，重復上述過程，直到左表所有記錄都比較完為止。聯接結果的記錄個數與左表的記錄個數一致。(左表記錄全部顯示，右表只顯示滿足條件的記錄）SELECTa.學號,a.姓名,b.計算機FROM

學生aleftJOIN成績表bONa.學號=b.學號（2）右外聯接也叫右聯接（RightJoin），其系統執行過程是右表的某條記錄與左表的所有記錄依次比較，若有滿足聯接條件的，則產生一個真實值記錄；若都不滿足，則產生一個含有NULL值的記錄。接著，右表的下一記錄與左表的所有記錄依次比較字段值，重復上述過程，直到左表所有記錄都比較完為止。聯接結果的記錄個數與右表的記錄個數一致。。(右表記錄全部顯示，左表只顯示滿足條件的記錄）SELECTa.學號,a.姓名,b.計算機FROM

學生arightJOIN成績表bONa.學號=b.學號（3）全外聯接也叫完全聯接（FullJoin），其系統執行過程是先按右聯接比較字段值，然后按左聯接比較字段值，重復記錄不記入查詢結果中。SELECTa.學號,a.姓名,b.計算機FROM學生afullJOIN成績表bONa.學號=b.學號

6.3.3嵌套查詢有時候一個SELECT命令無法完成查詢任務，需要一個子SELECT的結果作為條件語句的條件，即需要在一個SELECT命令的WHERE子句中出現另一個SELECT命令，這種查詢稱為嵌套查詢。通常把僅嵌入一層子查詢的SELECT命令稱為單層嵌套查詢，把嵌入子查詢多于一層的查詢稱為多層嵌套查詢。VisualFoxPro只支持單層嵌套查詢。1.返回單值的子查詢例列出計算機成績為85分的所有學生的籍貫。select姓名,籍貫fromh:\vfp\學生where姓名in(sele姓名fromh:\vfp\成績表where計算機=85)selea.姓名,a.籍貫fromh:\vfp\學生a,h:\vfp\成績表bwherea.姓名=b.姓名andb.計算機=85202.返回一組值的子查詢若某個子查詢返回值不止一個，則必須指明在WHERE子句中應怎樣使用這些返回值。通常使用條件ANY（或SOME）、ALL和IN（1）ANY運算符的用法例:列出選修“計算機”課的學生中期末成績比選修“數學”的最低成績要高的學生的學號和計算機成績及數學成績。SELECT學號,計算機,數學FROM成績表WHERE計算機>ANY

(SELECT數學FROM成績表)（2）ALL運算符的用法例:例:列出選修“計算機”課的學生中期末成績比選修“數學”的最高成績還要高的學生的學號和計算機成績及數學成績。SELECT學號,計算機,數學FROM成績表WHERE計算機>all

(SELECT數學FROM成績表)（3）IN運算符的用法例:列出江西籍或福建籍的所有學生的姓名、計算機成績、數學成績。SELECT姓名、計算機、數學FROM成績表WHERE籍貫IN;(SELECT籍貫FROM學生WHERE籍貫=″江西″OR籍貫=″福建″)注：IN是屬于的意思，等價于“=ANY”，即等于子查詢中任何一個值。輸出合并是指將兩個查詢結果進行集合并操作，其子句格式是：[UNION[ALL]<SELECT命令>]其中ALL表示結果全部合并。若沒有ALL，則重復的記錄將被自動取掉。合并的規則是：（1）不能合并子查詢的結果。（2）兩個SELECT命令必須輸出同樣的列數。（3）兩個表各相應列出的數據類型必須相同，數字和字符不能合并。（4）僅最后一個<SELECT命令>中可以用ORDERBY子句，且排序選項必須用數字說明。4.2.11.輸出合并（UNION）例列出“福建”籍或計算機成績為85分所有學生的學號及姓名。SELECT學號,姓名FROM學生WHERE籍貫=江西UNION

SELECT學號,姓名FROM成績表WHERE計算機=85各子句的含義：①<目標>有如下3種形式：a.ARRAY<數組名>。將查詢結果存到指定數組名的內存變量數組中。b.CURSOR<臨時表>。將輸出結果存到一個臨時表。c.DBF<表>|TABLE<表>。將結果存到一個表，如該表已經打開，則系統自動關閉它。如執行SETSAFETYOFF,則重新打開。沒有指定后綴，則默認為.dbf。②TOFILE<文件名>[ADDITIVE]將結果輸出到指定文本文件，ADDITIVE表示將結果添加到文件后面。③TOPRINTER將結果送打印機輸出。4.2.12查詢結果輸出命令：[INTO<目標>|[TOFILE<文件名>[ADDITIVE]|TOPRINTER]例將上例的查詢結果保存到test1.txt文本文件中。SELECTa.學號,a.姓名,a.性別,c.課程名,b.成績FROM學生a,選課b,課程c;WHEREa.學號=b.學號ANDb.課程號=c.課程號ORDERBYa.性別,c.課程名,b.成績DESCTOFILEtest1

例查詢學生所學課程和成績，輸出學號、姓名、課程名和成績，并將查詢結果存入testtable表中。SELECTa.學號,a.姓名,b.課程號,b.成績FROM學生a,選課bWHEREa.學號=b.學號INTOCURSORtestSELECTa.學號,a.姓名,b.課程名,a.成績FROMtesta,課程bWHEREa.課程號=b.課程號INTOTABLEtesttableORDERBYa.學號VisualFoxPro支持兩種SQL插入命令，其格式是：格式1：INSERTINTO<表名>[(字段名1[<字段名2>[,…]])]VALUES(<表達式1>[,<表達式2>[,…]])該命令在指定的表尾添加一條新記錄，其值為VALUES后面表達式的值。當需要插入表中所有字段的數據時，表名后面的字段名可以缺省，但插入數據的格式及順序必須與表的結構完全吻合；若只需要插入表中某些字段的數據，就需要列出插入數據的字段名，當然相應表達式的數據位置應與之對應。4.3數據操縱4.3.1插入記錄例向學生表中添加記錄。INSERTINTO學生(學號,姓名)VALUES(″231109″,″李成功″)格式2：INSERTINTO<表名>FROMARRAY<數組名>|FROMMEMVAR]

該命令在指定的表尾添加一條新記錄，其值來自于數組或對應的同名內存變量。例已經定義了數組A（5），A中各元素的值分別是：AA（1）=″231013″，A（2）=″張陽″，A（3）=″女″，A（4）={^1988-09-10}，A（5）=.T.。利用該數組向學生表中添加記錄。INSERTINTO學生FROMARRAYA

在VisualFoxPro中，DELETE可以為指定的數據表中的記錄加刪除標記。命令格式是：DELETEFROM[<數據庫名>!]<表名>[WHERE<條件表達式>該命令從指定表中，根據指定的條件邏輯刪除記錄。例將“學生”表所有男生的記錄邏輯刪除。DELETEFROM學生WHERE性別=″男″4.3.2刪除記錄

4.3.3更新記錄格式：UPDATE[<數據庫名>!]<表名>SET<字段名1>=<表達式1>[,<字段名2>=<表達式2>…][WHERE<邏輯表達式>]功能：更新記錄時對存儲在表中的記錄進行修改例將“學生”表中姓名為楊陽的學生的外語成績改為93。UPDATE學生SET外語=93WHERE姓名=“楊陽”例所有男生的各科成績加20分

UPDATE選課SET成績=成績+20WHERE學號IN(SELECT學號FROM學生WHERE性別=男)4.4數據定義4.4.1建立數據表格式：CREATETABLE|DBF<表名1>[NAME<長表名>][FREE](<字段名1><類型><寬度>[,<小數位數>])[NULL|NOTNULL][CHECK<條件表達式1>[ERROR<出錯顯示信息>]][DEFAULT<表達式1>][PRIMARYKEY|UNIQUE]REFERENCES<表名2>[TAG<標識1>][<字段名2><類型>(<寬度>[,<小數位數>])[NULL|NOTNULL][CHECK<條件表達式2>[ERROR<出錯顯示信息>]][DEFAULT<表達式2>][PRIMARYKEY|UNIQUE]REFERENCES<表名3>[TAG<標識2>參數說明：FREE：指明所創建的表為自由表。NULL、NOTNULL——該字段是否允許“空值”，其默認值為NULL。CHECK<條件表達式>——用來檢測字段的值是否有效，這是實行數據庫的一種完整性檢查。ERROR<出錯顯示信息>——當完整性檢查有錯誤，即條件表達式的值為假時的提示信息。DEFAULT<表達式>——為一個字段指定的默認值。PRIMARYKEY——指定該字段為關鍵字段，它能保證關鍵字段的唯一性和非空性，非數據庫表不能使用該參數。UNIQUE——指定該字段為一個侯選關鍵字段。注意，指定為關鍵或侯選關鍵的字段都不允許出現重復值，這稱為對字段值的唯一性約束。REFERENCES<表名>——這里指定的表作為新建表的永久性父表，新建表作為子表。TAG<標識>——父表中的關聯字段，若缺省該參數，則默認父表的主索引字段作為關聯字段。2023/2/435數據類型說明字段類型字段寬度小數位說明CN-字符型字段的寬度位ND--日期型(Date)T--日期時間型(Datetime)NND數值字段類型(Numeric)，寬度位N,小數位DFND浮點數值字段類型(Float)，寬度位N,小數位DI--整數類型(Integer)B-D雙精度類型(Double)Y--貨幣型(Currency)L--邏輯型(Logic)M--備注型(Memo)G--通用型(General)2023/2/4操作步驟如下：（1）用CREATE命令建立數據庫。CREATEDATABASEf:\vfp\圖書管理（2）用CREATE命令建立讀者表。createtablef:\vfp\讀者(讀者證號c(3)primkey,姓名c(8),性別c(2)check性別='男'or性別='女'error"性別只能為男或為女"default"男",身份c(8),電話號碼c(11))例

利SQL命令建立圖書管理數據庫，其中包含3個表：讀者表、圖書表和借閱表。2023/2/4（3）建立圖書表。createtablef:\vfp\圖書(條形碼c(8)primkey,書名c(40),分類號c(20),作者c(20),出版時間d)（4）建立借閱表createtablef:\vfp\借閱(條形碼c(8)reference圖書,讀者證號c(3)reference讀者,借閱時間d,還書時間d)4.4.2修改數據表修改表結構的命令是ALTERTABLE，該命令有一下幾種種格式：1.增加字段ALTERTABLE<表名1>ADD[COLUMN]<字段名><字段類型>[(<寬度>[,<小數位數>])][NULL|NOTNULL][CHECK<邏輯表達式>[ERROR<出錯顯示信息>]][DEFAULT<表達式>][PRIMARYKEY|UNIQUE][REFERENCES<表名2>[TAG<標識名>]]例：為成績表增加一個數值型的數學分析字段。ALTERTABLE成績表ADD數學分析n（6，1）CHECK數學分析>=0and數學分析<=100ERROR″學時應該大于16！″)2.修改字段ALTERTABLE<表名1>ALTER[COLUMN]<字段名><字段類型>[(<寬度>[,<小數位數>])][NULL|NOTNULL][CHECK<邏輯表達式>[ERROR<出錯顯示信息>]][DEFAULT<表達式>][PRIMARYKEY|UNIQUE][REFERENCES<表名2>[TAG<標識名>]]OPENDATABASE圖書管理ALTERTABLE學生ALTER

學號C(10）例：將圖書表中條形碼字段的寬度由原來的8改為10命令可以修改字段的類型、寬度、有效性規則、錯誤信息、默認值，定義主關鍵字和聯系等；但是不能修改字段名.3.定義和刪除字段的有效性規則、默認值ALTERTABLE<表名>ALTER[COLUMN]<字段名>[NULL|NOTNULL][SETDEFAULT<表達式>[SETCHECK<邏輯表達式>[ERROR<出錯顯示信息>]][DROPDEFAULT][DROPCHECK]該格式命令主要用于定