《Visual FoxPro 數(shù)據(jù)庫應(yīng)用教程》教學(xué)課件：第5章結(jié)構(gòu)化查詢語言SQL

1、第5章 結(jié)構(gòu)化查詢語言SQL SQL是結(jié)構(gòu)化查詢語言 Structured Query Language 的縮寫。 5.1 SQL 的特點(diǎn)5.2 SQL的查詢功能5.3 SQL的定義功能5.4 SQL的操作功能本章要點(diǎn)5.1 SQL 的特點(diǎn)SQL語言具有如下主要特點(diǎn)： SQL是一種一體化的語言，它包括了數(shù)據(jù)定義、數(shù)據(jù)查詢、數(shù)據(jù)操縱和數(shù)據(jù)控制等方面的功能，它可以完成與數(shù)據(jù)庫相關(guān)的全部工作。 SQL語言是一種高度非過程化的語言，用戶只需要描述清楚要“做什么”，SQL語言就可以將要求交給系統(tǒng)，自動(dòng)完成全部工作。 SQL語言非常簡潔。 SQL 語言可以直接以命令方式交互使用，也可以嵌入到程序設(shè)計(jì)語言當(dāng)

2、中以程式方式使用。5.2 SQL的查詢功能 SQL語句創(chuàng)建查詢使用的是Select語句 Select All|Distinct | , From Left Right Join On Where Order By Asc Desc Group By Having SELECT ALL|DISTINCT TOP nExpr PERCENTAlias. Select_Item AS Column_Name，Alias.Select_ItemAS Column_NameFROMFORCEDatabaseName！Table AS Local_AliasINNER | LEFT OUTER | RIG

3、HT OUTER |FULL OUTER JOINDatabaseName！Table AS Local_AliasON joinConditionINTO Destination| TO FILE FileName ADDITIVE | TO PRINTER PROMPT | TO SCREENPREFERENCE PreferenceNameNOCONSOLEPLAINNOWAITWHERE JoinCondition AND JoinConditionAND | OR FilterCondition AND | OR FilterConditionGROUP BY GroupColumn

4、 ，GroupColumnHAVING FilterConditionUNION ALL SELECTCommandORDER BY Order_ItemASC | DESC，Order_Item ASC | DESC 5.2.1 簡單查詢 由SELECT和FROM短語構(gòu)成無條件查詢，或 由SELECT,FROM和WHERE短語構(gòu)成條件查詢。例1 從cj表中檢索所有同學(xué)的數(shù)學(xué)成績。SELECT 學(xué)號(hào) ,數(shù)學(xué) FROM cj如果要去掉重復(fù)值只需要指定DISTINCT短語：SELECT DISTINCT 數(shù)學(xué) FROM cj DISTINCT短語的作用是去掉查詢結(jié)果中的重復(fù)值 例2 檢索cj表中的

5、所有記錄。SELECT * FROM cj例3 檢索數(shù)學(xué)成績高于80分的所有記錄。SELECT * FROM cj WHERE 數(shù)學(xué)80用WHERE短語指定了查詢條件，查詢條件可以是任意復(fù)雜的邏輯表達(dá)式。例4 檢索數(shù)學(xué)成績高于80分而且英語成績及格的所有記錄。 SELECT * FROM cj WHERE 數(shù)學(xué)80 and 英語=60 5.2.2 簡單的聯(lián)接查詢聯(lián)接是關(guān)系的基本操作之一，聯(lián)接查詢是一種基于多個(gè)關(guān)系的查詢。例5 檢索數(shù)學(xué)成績和英語成績都及格的所有記錄的學(xué)號(hào)，姓名和數(shù)學(xué)SELECT cj.學(xué)號(hào)，姓名，數(shù)學(xué) FROM xsqk,cj ； WHERE 數(shù)學(xué)=60 and 英語=60 A

6、nd xsqk.學(xué)號(hào)=cj.學(xué)號(hào) 5.2.3 嵌套查詢 查詢的結(jié)果出自一個(gè)關(guān)系，但相關(guān)的條件卻出自多個(gè)關(guān)系。例6 查詢選課成績及格的學(xué)生的姓名SELECT 姓名 FROM xsqk WHERE 學(xué)號(hào) in (sele 學(xué)號(hào) from xk where 成績=60)這個(gè)命令中含有兩個(gè)SELECT-FROM-WHERE查詢塊，即內(nèi)層查詢塊和外層查詢塊，這里IN相當(dāng)于集合運(yùn)算符。例7 查詢所有選課成績都不及格的學(xué)生的姓名。SELECT 姓名 FROM xsqk WHERE 學(xué)號(hào) not in; (sele 學(xué)號(hào) from xk where 成績=60) 注意到剛才的檢索出現(xiàn)了錯(cuò)誤，沒有選課學(xué)生的信息

7、也被檢索出來了。正確的SQL命令。SELECT 姓名 FROM xsqk WHERE 學(xué)號(hào) not in; (sele 學(xué)號(hào) from xk where 成績=60) and 學(xué)號(hào) in (sele 學(xué)號(hào) from xk) 例8 找出和學(xué)生“馬大大”的身高相同的所有學(xué)生。SELECT 姓名 FROM xsqk WHERE 身高=； (SELECT 身高 FROM xsqk WHERE 姓名=馬大大) 5.2.4 排序使用SQL SELECT 可以將查詢結(jié)果排序，排序的短語是ORDER BY，可以按升序(ASC)或降序（DESC）排序，允許按一列或多列排序。例：按學(xué)生的身高升序檢索出全部學(xué)生信息

8、。SELECT * FROM xsqk ORDER BY 身高 這里ORDER BY 是排序子句，如果需要將結(jié)果按降序排列，只有加上DESC即可：SELECT * FROM xsqk ORDER BY 身高 DESC按學(xué)生的身高升序，身高相同的按體重降序檢索出全部學(xué)生信息。這是一個(gè)按多列排序的例子，可以由如下語句完成：SELECT * FROM xsqk ORDER BY 身高,體重 desc 注意：ORDER BY是對最終的查詢結(jié)果進(jìn)行排序，不可以在子查詢中使用該短語。 5.2.5 簡單的計(jì)算查詢SQL 不僅具有一般的檢索能力，而且還有計(jì)算方式的檢索，比如檢索的平均成績、檢索最高身高等。用于

9、計(jì)算檢索的函數(shù)及格式如下：COUNT(*)：計(jì)算記錄個(gè)數(shù)SUM(字段名)：求字段名所指定字段值的總和AVG(字段名)：求字段名所指定字段的平均值MAX(字段名)：求字段名所指定字段的最大值MIN(字段名)：求字段名所指定字段的最小值找出學(xué)生中血型的種類。SELECT COUNT (DISTINCT 血型) FROM xsqk 求學(xué)生的數(shù)學(xué)成績總和。SELECT SUM(數(shù)學(xué)) FROM cj求“O”型和“B”型血的學(xué)生的數(shù)學(xué)成績總和。SELECT SUM(數(shù)學(xué)) FROM cj WHERE 學(xué)號(hào) IN ; (SELECT 學(xué)號(hào) FROM xsqk WHERE 血型=B OR 血型=O) 求“O

10、”型和“B”型血的學(xué)生數(shù)學(xué)成績的平均值SELECT avg(數(shù)學(xué)) FROM cj WHERE 學(xué)號(hào) IN；(SELECT 學(xué)號(hào) FROM xsqk WHERE 血型=B OR 血型=O) 5.2.6 分組與計(jì)算查詢 上面例子是對整個(gè)表的計(jì)算查詢，而利用GROUP BY 子句進(jìn)行分組計(jì)算查詢使用得更加廣泛。求男同學(xué)和女同學(xué)的平均身高。SELECT 性別,avg(身高) FROM xsqk GROUP BY 性別求 “O”型和“B”型血的男同學(xué)和女同學(xué)的平均身高。SELECT 血型,avg(身高) FROM xsqk ;WHERE 血型=B OR 血型=O GROUP BY 性別GROUP BY

11、子句一般跟在WHERE子句之后，沒有WHERE子句時(shí)，跟在FROM子句之后。另外，還可以根據(jù)多個(gè)屬性進(jìn)行分組。在分組查詢時(shí)，有時(shí)要求分組滿足某個(gè)條件時(shí)才檢索，這時(shí)可以用HAVING子句來限定分組。求各種血型(每種血型至少有兩個(gè)同學(xué))的同學(xué)的平均身高。SELECT 血型,avg(身高) FROM xsqk GROUP BY 血型 ; HAVING COUN(*)=2 5.2.7 內(nèi)外層互相關(guān)嵌套查詢有時(shí)需要內(nèi)、外層互相關(guān)的查詢，這時(shí)內(nèi)層查詢的條件需要外層查詢提供值，而外層查詢的條件需要內(nèi)層查詢的結(jié)果。列出每個(gè)學(xué)生選課成績最高分的記錄。SELECT * from xk out where成績=;

12、(SELECT MAX(成績) FROM xk Intr WHERE out.學(xué)號(hào)=intr.學(xué)號(hào))在這個(gè)查詢中，外層查詢和內(nèi)層查詢使用同一個(gè)表，給它們分別指定別名 out 和intr，外層查詢提供out中每個(gè)記錄的學(xué)號(hào)給內(nèi)層查詢使用；內(nèi)層查詢利用這個(gè)學(xué)號(hào)值，確定該學(xué)生的選課成績最高分；隨后外層查詢再根據(jù)out表的同一記錄的選課成績與該最高分進(jìn)行比較，如果相等，則該記錄被選擇。 5.2.8 使用量詞和謂詞的查詢前面已經(jīng)使用過和嵌套查詢或子查詢有關(guān)的IN 和NOT IN運(yùn)算符，除此之外還有兩類和子查詢有關(guān)的運(yùn)算符，它們有以下兩種形式： ANY|ALL|SOME（子查詢） NOT EXISTS（子

13、查詢）ANY、ALL和SOME是量詞。EXISTS是謂詞，EXISTS或NOT EXISTS是用來檢查在子查詢中是否有結(jié)果返回，即存在記錄或不存在記錄。 在表xsqk中檢索沒有選課學(xué)生的信息。SELECT * FROM xsqk WHERE NOT EXISTS ; (SELECT 學(xué)號(hào) FROM xk where xk.學(xué)號(hào)=xsqk.學(xué)號(hào))以上的查詢等價(jià)于：SELECT * FROM xsqk WHERE 學(xué)號(hào) NOT IN ; (SELECT 學(xué)號(hào) FROM xk )以上查詢也可以使用量詞如下：SELECT * FROM xsqk WHERE 學(xué)號(hào) all ; (SELECT 學(xué)號(hào) FR

14、OM xk where xk.學(xué)號(hào)=xsqk.學(xué)號(hào))在表xsqk中檢索有參加選課學(xué)生的信息。這個(gè)查詢可以使用EXISTS或ANY 、SOME。SELECT * FROM xsqk WHERE EXISTS ; (SELECT 學(xué)號(hào) FROM xk where xk.學(xué)號(hào)=xsqk.學(xué)號(hào))或：SELECT * FROM xsqk WHERE 學(xué)號(hào) = any ; (SELECT 學(xué)號(hào) FROM xk where xk.學(xué)號(hào)=xsqk.學(xué)號(hào))檢索選課成績大于學(xué)號(hào)為“1161003”的最低選課成績的所有學(xué)生的學(xué)號(hào)。這個(gè)查詢可以使用ANY或SOME量詞。SELECT DISTINCT 學(xué)號(hào) FROM

15、xk WHERE 成績any ;(SELECT 成績 FROM xk where 學(xué)號(hào)=1161003) 或：SELECT DISTINCT 學(xué)號(hào) FROM xk WHERE 成績some ;(SELECT 成績 FROM xk where 學(xué)號(hào)=1161003)它等價(jià)于：SELECT DISTINCT 學(xué)號(hào) FROM xk WHERE 成績;(SELECT min(成績) FROM xk where 學(xué)號(hào)=1161003)5.2.9 超聯(lián)接查詢在一般 SQL 中超連接運(yùn)算苻 “*=” 和“=*” 。其中“*=”稱為左連接，含義是輸出包含第一個(gè)表中滿足條件的所有記錄，如果第二個(gè)表在連接條件上有

16、匹配記錄，則第二個(gè)表反回相應(yīng)值，否則第二個(gè)表反回空值。注意：VISUAL FOXPOR不支持超級(jí)聯(lián)接運(yùn)算符 “*=” 和“=*”， VISUAL FOXPOR有專門的聯(lián)接運(yùn)算語法格式如下：SELECT 字段表達(dá)式表 FROM 表1 INNER |LEFT| RIGHT |FULL JOIN 表2 ON 連接條件 WHERE其中，INNER JOIN等價(jià)于 JOIN，為普通聯(lián)接；LEFT JOIN稱為左聯(lián)接；RIGHT JION稱為右聯(lián)接；FULL JION可以稱為全聯(lián)接，即兩個(gè)表的記錄不管是否滿足聯(lián)接條件都在目標(biāo)表或查詢表中出現(xiàn)。 輸出cj表和xsqk表的學(xué)號(hào)及姓名，使用普通聯(lián)接。SELECT

17、 cj.學(xué)號(hào),xsqk.學(xué)號(hào) ,姓名 FROM cj join xsqk on cj.學(xué)號(hào)=xsqk.學(xué)號(hào)輸出cj表和xsqk表的學(xué)號(hào)及姓名，使用左聯(lián)接。SELECT cj.學(xué)號(hào),xsqk.學(xué)號(hào) ,姓名 FROM cj LEFT join xsqk on cj.學(xué)號(hào)=xsqk.學(xué)號(hào)輸出cj表和xsqk表的學(xué)號(hào)及姓名，使用右聯(lián)接。SELECT cj.學(xué)號(hào),xsqk.學(xué)號(hào) ,姓名 FROM cj RIGHT join xsqk on cj.學(xué)號(hào)=xsqk.學(xué)號(hào)5.2.10 集合的并運(yùn)算SQL支持集合的并(UNION)運(yùn)算，即可以將兩個(gè)SELECT語句的查詢結(jié)果通過并運(yùn)算合成一個(gè)查詢結(jié)果。為了進(jìn)行并

18、運(yùn)算，要求這樣的兩個(gè)查詢結(jié)果具有相同的字段個(gè)數(shù)，并且對應(yīng)字段的值要出自同一個(gè)值域。輸出數(shù)學(xué)成績大于80分和小于70分的記錄。sele * from cj where 數(shù)學(xué)80 union； sele * from cj where 數(shù)學(xué)705.2.11 SQL SELECT的幾個(gè)特殊選項(xiàng) 只顯示前幾項(xiàng)記錄 (TOP短語要與ORDER BY短語同時(shí)使用 )按學(xué)生的身高降序檢索出身高最高的3位學(xué)生信息。 SELECT * top 3 FROM xsqk ORDER BY 身高 desc按學(xué)生的身高降序檢索出身高最高的3成學(xué)生信息。SELECT * top 30 percent FROM xsqk

19、ORDER BY 身高 desc將查詢結(jié)果存放到永久表中 SELECT * top 3 FROM xsqk ORDER BY 身高 desc into dbf xs3將查詢結(jié)果存放在臨時(shí)文件中SELECT * top 3 FROM xsqk ORDER BY 身高 desc into cursor xs3cur將查詢結(jié)果存放在文本文件中SELECT * top 3 FROM xsqk ORDER BY 身高 desc to file xs3將查詢結(jié)果送到打印機(jī)打印SELECT * top 3 FROM xsqk ORDER BY 身高 desc to printer查詢結(jié)果存到數(shù)組中 SELE

20、CT * FROM cj INTO ARRAY tmp 5.3 SQL的定義功能5.3.1 表的定義 標(biāo)準(zhǔn)SQL的數(shù)據(jù)定義功能非常廣泛，一般包括數(shù)據(jù)庫的定義，表的定義，視圖的定義，存儲(chǔ)過程的定義，規(guī)則的定義和索引的定義等若干部分。在本節(jié)將主要介紹Visual Foxpro支持的定義功能和視圖定義功能。 CREATE TABLE |DBF FREE ( 類型(長度,小數(shù)位數(shù)) CHECK 邏輯表達(dá)式l ERROR 字符表達(dá)式1 DEFAULT 表達(dá)式lPRIMARY KEY|UNIQUEREFERENCES 表名2 TAG 索引名l,PRIMARY KEY 表達(dá)式2 TAG 索引名2|,UNIQ

21、UE 表達(dá)式3 TAG 索引名3,FOREIGN KEY 表達(dá)式4 TAG 索引名4REFERENCES 表名3 TAG 索引名5,CHECK 邏輯表達(dá)式2 ERROR字符表達(dá)式2) 建立表xsqk1.dbf（結(jié)構(gòu)和表xsqk.dbf類似）。create table xsqk1 (xh c(7),xm c(8),rq d ,sg n(4,2),ty l,bz m,zp g)建立表xsqk2.dbf（結(jié)構(gòu)和表xsqk.dbf類似），要求建立主關(guān)鍵字等。只有數(shù)據(jù)庫表才能建立主索引，所以建立表之前先建立數(shù)據(jù)庫ABC。CREATE DATABASE ABCCREATE table xsqk2 (xh

22、c(7) primary key ,xm c(8),rq date ,sg n(4,2) ;check sg1.5 error 身高應(yīng)該大于1.5 米 default 1.7, ty l,bz m,zp g)建立表cj2.dbf，要求該表有字段xh，并以xh字段與表xsqk2.dbf建立關(guān)聯(lián)。CREATE table cj2 (xh c(7) ,sx n(3),yy n(3), ;foreign key xh tag xh references xsqk2)5.3.2 表的刪除刪除表的SQL命令是：DROP TABLE 表名5.3.3 表結(jié)構(gòu)的修改修改表結(jié)構(gòu)的命令是ALTER TABLE，該命

23、令有三種格式。格式1：ALTER TABLE 表名1 ADD | ALTER COLUMN 字段名1 字段類型 (字段寬度 ,小數(shù)位數(shù)) NULL | NOT NULLCHECK 邏輯表達(dá)式1 ERROP 字符表達(dá)式1DEFAULT表達(dá)式1PRIMARY KEY | UNIQUEREFERENCES表名2 TAG 索引名1該命令可以修改字段的類型、寬度、有效性規(guī)則、錯(cuò)誤信息、默認(rèn)值，定義主關(guān)鍵字和聯(lián)系等； 把表xsqk1.dbf的xm字段的寬度改為12 ，sg字段改為n(5,2)。alter tabl xsqk1 alter xm c(12) alter sg n(5,2)給表xsqk1.db

24、f增加字段dz c(20)及df c(8)。alter tabl xsqk1 add dz c(20) add df c(8)格式2：ALTER TABLE 表名1 ALTER COLUMN 字段名2 SET DEFAULT表達(dá)式2 SET CHECK 邏輯表達(dá)式2 ERROR字符表達(dá)式2DROP DEFAULT DROP CHECK從命令格式可以看出，該格式主要用于定義、修改和刪除有效性規(guī)則和默認(rèn)值定義。把表xsqk1.dbf的sg字段的有效性規(guī)則修改為“sg1”。ALTER TABLE xsqk1 ALTER sg SET CHECK sg1刪除表xsqk1.dbf的sg字段的有效性規(guī)則。

25、ALTER TABLE xsqk1 ALTER sg DROP CHECK 格式3：ALTER TABLE 表名1 DROP COLUMN 字段名3SET CHECK 邏輯表達(dá)式3 ERROR字符表達(dá)式3DROP CHECKADD PRIMARY KEY 表達(dá)式3 TAG 索引名2 FOR 邏輯表達(dá)式4DROP PRIMARY KEYADD UNIQUE 表達(dá)式4 TAG索引名3 FOR 邏輯表達(dá)式5DROP UNIQUE TAG 索引名4ADD FOREIGN KEY 表達(dá)式5 TAG TagName4 FOR 邏輯表達(dá)式6REFERENCES 表名2 TAG索引名5DROP FOREIGN

26、 KEY TAG TagName6 RENAME COLUMN字段名4 TO字段名5該格式可以刪除字段（DROP COLUMN）、可以修改字段名（RENAME COLUMN）、可以定義、修改和刪除表一級(jí)的有效性規(guī)則等。將表xsqk1.dbf的sg字段名改為“身高”。ALTER TABLE xsqk1 RENAME COLUMN sg TO 身高刪除表xsqk1.dbf的dz及df字段。alter tabl xsqk1 drop dz drop df5.3.4 視圖的定義及刪除CREATE VIEW view_name (字段名 1,字段名2.) AS select_statement其中select_statement可以是任意的SELECT 查詢語句，它說明和限定了視圖中的數(shù)據(jù)；當(dāng)沒有為視圖指定字段名時(shí)，視圖的字段名將與select_statement中指定的字段名或表中的字段名同名。 從xsqk表中選擇學(xué)號(hào)和姓名字段建立視圖vxsqk。CREATE VIEW vxsqk AS;SELECT 學(xué)號(hào), 姓名 FROM xsqk上面是限定列構(gòu)成的視圖，可以用WHERE限定行定義一個(gè)視圖。例如：CREATE VIEW vxsqk1 AS；SELECT 學(xué)號(hào), 姓名 FROM xsqk WHERE 身高1.7從xsqk表和cj表中選擇學(xué)號(hào)、姓名和總分字段建立視圖vxsqkcj