第5章 標準結構化查詢語言SQL

1、第第5章章 標準結構化查詢語言標準結構化查詢語言SQLSQL 本章所講的主要內(nèi)容如下本章所講的主要內(nèi)容如下： 5.1 SQL語言概述語言概述 5.2 SQL數(shù)據(jù)定義數(shù)據(jù)定義 5.3 SQL數(shù)據(jù)查詢數(shù)據(jù)查詢 5.4 SQL數(shù)據(jù)操縱數(shù)據(jù)操縱5.1 SQLSQL語言概述語言概述 SQLSQL（Structured Query LanguageStructured Query Language）是人們專門研究）是人們專門研究的一種強有力的查詢語言，它包括的一種強有力的查詢語言，它包括查詢、操縱、定義和控查詢、操縱、定義和控制制四個方面，是關系數(shù)據(jù)庫的標準語言。現(xiàn)在已經(jīng)有四個方面，是關系數(shù)據(jù)庫的標準語言

2、?，F(xiàn)在已經(jīng)有100100多個數(shù)據(jù)庫管理產(chǎn)品支持多個數(shù)據(jù)庫管理產(chǎn)品支持SQLSQL語言。語言。SQLSQL語言是一種非過程化的語言。用語言是一種非過程化的語言。用SQLSQL編寫的程序，編寫的程序，用戶只需要指出用戶只需要指出“干什么干什么”，而不需要知道，而不需要知道“怎么干怎么干”。SQLSQL只有只有9 9個命令動詞，從語法上很接近自然語言，非常易個命令動詞，從語法上很接近自然語言，非常易于掌握。于掌握。 在在Visual FoxProVisual FoxPro的程序中可以直接使用的程序中可以直接使用SQLSQL語句，就語句，就像輸入其他命令一樣在命令窗口中輸入像輸入其他命令一樣在命令窗口

3、中輸入SQLSQL語句語句。5.2 SQLSQL數(shù)據(jù)定義數(shù)據(jù)定義 本節(jié)所講述的主要內(nèi)容：本節(jié)所講述的主要內(nèi)容：基本表的定義基本表的定義1基本表的修改與刪除基本表的修改與刪除25.2 SQLSQL數(shù)據(jù)定義數(shù)據(jù)定義 ( , , )創(chuàng)建一個新表。創(chuàng)建一個新表。1.1.基本表的定義基本表的定義 CREAT TABLE 學生學生; (學號學號 char(10), 姓名姓名 char(10), 性別性別 char(2), 專業(yè)專業(yè) char(10), 出生日期出生日期 date,; 是否保送是否保送 logical, 入學總分入學總分 number(6,1), 照片照片 general, 備注備注 mem

4、o)或者或者 CREAT TABLE 學生學生; (學號學號 C(10), 姓名姓名 C(10), 性別性別 C(2), 專業(yè)專業(yè) C(10), 出生日期出生日期 D, 是否保送是否保送 L,; 入學總分入學總分 N(6,1), 照片照片 G, 備注備注 M) ADD|ALTER DROP RENAME TO p當新當新增加一個字段增加一個字段時，表中記錄在這個字段上的值為時，表中記錄在這個字段上的值為缺省的缺省的空值?？罩怠alter可以可以修改字段類型修改字段類型，rename可以可以修改字段名稱修改字段名稱。2.2.基本表的修改與刪除基本表的修改與刪除5.2 SQL數(shù)據(jù)定義5.2 SQ

5、L數(shù)據(jù)定義ALTER TABLE SCORE ADD 平時成績平時成績 N(3)ALTER TABLE SCORE ALTER 平時成績平時成績 N(3,1)第一步：改名第一步：改名ALTER TABLE SCORE RENAME 平時成績平時成績 TO 平時分平時分第二步：刪除第二步：刪除ALTER TABLE SCORE DROP 平時分平時分5.2 SQLSQL數(shù)據(jù)定義數(shù)據(jù)定義 DROP TABLE 功能：將包含表結構和記錄的表文件全部刪除。功能：將包含表結構和記錄的表文件全部刪除。DROP TABLE STUDENT5.3 SQLSQL數(shù)據(jù)查詢數(shù)據(jù)查詢 本節(jié)所講述的主要內(nèi)容：本節(jié)所講述

6、的主要內(nèi)容：單表無條件查詢單表無條件查詢1單表帶條件查詢單表帶條件查詢2分組查詢和排序查詢分組查詢和排序查詢3多表查詢多表查詢4嵌套查詢嵌套查詢5Visual FoxProVisual FoxPro中的幾個特殊選項中的幾個特殊選項65.3 SQLSQL數(shù)據(jù)查詢數(shù)據(jù)查詢 ALL|DISTINCT ALIAS. | AS 列名稱列名稱 , ALIAS. | AS 列名稱列名稱 AND|OR ASC DESC ， ASC DESC, HAVINGp SELECT：要查詢的目標列（或字段）。：要查詢的目標列（或字段）。p FROM：說明要查詢的數(shù)據(jù)來自哪個或者哪些表。：說明要查詢的數(shù)據(jù)來自哪個或者哪些

7、表。p WHERE：說明多表的連接條件和查詢條件。：說明多表的連接條件和查詢條件。p DISTINCT：只選擇不重復的記錄。：只選擇不重復的記錄。p GROUP BY：用于對查詢結果進行分組。：用于對查詢結果進行分組。p ORDER BY：用來對查詢的結果進行排序。：用來對查詢的結果進行排序。 ALL | DISTINCT 1.1.單表無條件查詢單表無條件查詢5.3 SQLSQL數(shù)據(jù)查詢數(shù)據(jù)查詢 SELECT 學號學號, 姓名姓名, 專業(yè)專業(yè) FROM STUDENT SELECT 課程號課程號, 課程名課程名, 學分學分 FROM COURSE說明：說明：當所查詢的列是關系的當所查詢的列是關

8、系的所有屬性所有屬性時，可以使用時，可以使用* 來表示所顯示的列，來表示所顯示的列，等價于等價于 SELECT * FROM COURSEcourse(course(課程號，課程名，學分課程號，課程名，學分) ) 使用使用DISTINCT保留字可以保留字可以消除消除查詢結果中的查詢結果中的重復行重復行。5.3 SQLSQL數(shù)據(jù)查詢數(shù)據(jù)查詢SELECT 專業(yè)專業(yè) FROM STUDENTSELECT DISTINCT 專業(yè)專業(yè) FROM STUDENT注意比較這兩個語句的區(qū)別注意比較這兩個語句的區(qū)別 SELECT子句的目標列中可以包含帶有子句的目標列中可以包含帶有+、的算術運算表達式。的算術運算

9、表達式。5.3 SQLSQL數(shù)據(jù)查詢數(shù)據(jù)查詢SELECT SELECT 學號學號, , 姓名姓名, 2013-, 2013-year(出生日期出生日期) FROM STUDENTFROM STUDENT說明：說明：可以為可以為列列標題標題設置別名設置別名。SELECTSELECT 學號學號, , 姓名姓名, , year(date()-year(-year(出生日期出生日期) ) ASAS 年齡年齡; ; FROMFROM STUDENT STUDENTstudent(student(學號，姓名，性別，專業(yè)，入學總分，是否保送，出生日期，備注，照片學號，姓名，性別，專業(yè)，入學總分，是否保送，出

10、生日期，備注，照片) ) 5.3 SQLSQL數(shù)據(jù)查詢數(shù)據(jù)查詢 SELECT 課程名課程名, credit, 學分學分 FROM COURSE說明：說明：這種書寫方式可以使查詢結果增加一個這種書寫方式可以使查詢結果增加一個原關系里不存在的原關系里不存在的字符串字符串常量列，元組在該常量列，元組在該列上的每個值列上的每個值就是就是字符串常量字符串常量本身本身。coursecourse( (課程號，課程名，學分課程號，課程名，學分) ) 統(tǒng)計查詢結果中的統(tǒng)計查詢結果中的元組個數(shù)元組個數(shù) 統(tǒng)計查詢結果中統(tǒng)計查詢結果中一個列上值的個數(shù)一個列上值的個數(shù)計算查詢結果中一個列上的最大值計算查詢結果中一個列上

11、的最大值 計算查詢結果中一個列上的最小值計算查詢結果中一個列上的最小值 計算查詢結果中一個計算查詢結果中一個數(shù)值數(shù)值列上的總和列上的總和計算查詢結果中一個計算查詢結果中一個數(shù)值數(shù)值列上的平均值列上的平均值5.3 SQLSQL數(shù)據(jù)查詢數(shù)據(jù)查詢說明：說明：在在前加入前加入DISTINCT保留字，會將查詢結果的列保留字，會將查詢結果的列 去掉重復值在計算。去掉重復值在計算。5.3 SQL數(shù)據(jù)查詢COUNT函數(shù)的使用。函數(shù)的使用。例例5-11 統(tǒng)計學生表中的記錄數(shù)。統(tǒng)計學生表中的記錄數(shù)。 SELECT COUNT(*) FROM STUDENT 等價于等價于 SELECT COUNT(學號學號) FR

12、OM STUDENT 還可以使用別名還可以使用別名： SELECT COUNT(學號學號) AS FROM STUDENT 例例5-12 5-12 統(tǒng)計學生的專業(yè)個數(shù)統(tǒng)計學生的專業(yè)個數(shù)SELECT COUNT(專業(yè)專業(yè)) FROM STUDENT & 統(tǒng)計時會去掉空值統(tǒng)計時會去掉空值若要統(tǒng)計不同專業(yè)個數(shù)：若要統(tǒng)計不同專業(yè)個數(shù)：SELECT COUNT(DISTINCT 專業(yè)專業(yè)) FROM STUDENTSELECT AVG(成績成績) AS 平均成績平均成績, MAX(成績成績) AS 最高分最高分, MIN(成績成績) AS 最低分最低分; FROM SCOREvSQL實驗一 ALL |

13、DISTINCT 一般形式為：一般形式為： 比較運算符比較運算符 或者或者 比較運算符比較運算符 常量值常量值2.2.單表帶條件查詢單表帶條件查詢5.3 SQLSQL數(shù)據(jù)查詢數(shù)據(jù)查詢SELECT * FROM STUDENT WHERE 專業(yè)專業(yè)=計算機計算機SELECT 學號學號, 成績成績 FROM SCORE WHERE 課程號課程號=1001 AND 成績成績=605.3 SQLSQL數(shù)據(jù)查詢數(shù)據(jù)查詢查詢條件謂詞比較確定范圍確定集合字符匹配空值多重條件=,=,!=,!,!= 85 AND = 85 AND 成績成績 = 95 = 95SELECTSELECT * * FROM FROM

14、 STUDENT STUDENT WHEREWHERE 專業(yè)專業(yè)=計算機計算機 OR OR 專業(yè)專業(yè)=金融金融 等價于等價于SELECTSELECT * * FROMFROM STUDENT STUDENT WHEREWHERE 專業(yè)專業(yè) IN IN(計算機計算機, , 金融金融)5.3 SQLSQL數(shù)據(jù)查詢數(shù)據(jù)查詢練習：練習：1 1、查詢、查詢xs.dbfxs.dbf計算機專業(yè)男生的計算機專業(yè)男生的xmxm、 zyzy和和 csrqcsrq。2 2、查詢、查詢zg.dbfzg.dbf基本工資在基本工資在80080010001000之間的職工之間的職工xmxm及及bm.bm. 3 3、查詢投資

15、專業(yè)、金融專業(yè)、財會專業(yè)的學生、查詢投資專業(yè)、金融專業(yè)、財會專業(yè)的學生xmxm和和rxzfrxzf. .注意：注意：ININ實際上是多個實際上是多個OROR的縮寫的縮寫。上題等價于：5.3 SQLSQL數(shù)據(jù)查詢數(shù)據(jù)查詢練習答案：1 1、查詢、查詢xs.dbfxs.dbf計算機專業(yè)男生的計算機專業(yè)男生的xmxm、 zyzy和和 csrqcsrq。 Sele xm,zy,csrq from xs where zySele xm,zy,csrq from xs where zy=“=“計算機計算機” ” and xband xb=“=“男男”2 2、查詢、查詢zg.dbfzg.dbf基本工資在基本工

16、資在80080010001000之間的職工之間的職工xmxm及及bm.bm. Sele xm,bm from zg where jbgz Sele xm,bm from zg where jbgz between 800 and between 800 and 100010003 3、查詢、查詢xs.dbfxs.dbf中投資專業(yè)、金融專業(yè)、財會專業(yè)的學生中投資專業(yè)、金融專業(yè)、財會專業(yè)的學生xmxm和和rxzfrxzf. . Sele xm,rxzf from xs where zy Sele xm,rxzf from xs where zy in ( in (投資投資,金金融融,財會財會)注意

17、：注意：ININ實際上是多個實際上是多個OROR的縮寫的縮寫 上例等價于：Sele xm,rxzf,zy from xs where zySele xm,rxzf,zy from xs where zy = = 投資投資 or zy or zy=金融金融 or zy or zy=財會財會 5.3 SQLSQL數(shù)據(jù)查詢數(shù)據(jù)查詢 基本格式為：基本格式為： likenot like 說明：說明： 可以是可以是一個完整的字符串一個完整的字符串，也，也可以是含有通配符可以是含有通配符% %和和_ _。SELECT 學號學號, 姓名姓名, 性別性別 FROM STUDENT WHERE 姓名姓名 LIKE

18、 王王%通配符通配符含含 義義%表示任意長度的字符串表示任意長度的字符串。如。如 a%ba%b：表示以：表示以a a 開頭，以開頭，以b b結尾的結尾的任意長度任意長度的字符串。的字符串。 acb,addgb,abacb,addgb,ab均滿足要求均滿足要求_ （下劃線）（下劃線）表示任意的單個字符。如表示任意的單個字符。如 a_ba_b：表示以：表示以a a開頭，以開頭，以b b結尾的結尾的長長度為度為3 3的任意字符串。的任意字符串。Acb,afbAcb,afb等都滿足要求等都滿足要求5.3 SQLSQL數(shù)據(jù)查詢數(shù)據(jù)查詢例1：查詢xs.dbf中所有姓劉的學生詳細情況。 例2：查詢xs中，x

19、h為“901800” 學生詳細情況。等價于Sele * from xs where xm like “劉劉%”sele * from xs where xh like “901800”Sele * from xs where xh =“901800”說明：如果說明：如果likelike后面的后面的匹配串中不含有通配符，匹配串中不含有通配符，則可以用則可以用= =（等于）運（等于）運算符取代算符取代likelike謂詞謂詞; ;用！用！= =（不等于）取代（不等于）取代not likenot like謂詞。謂詞。5.3 SQLSQL數(shù)據(jù)查詢數(shù)據(jù)查詢5.3 SQLSQL數(shù)據(jù)查詢數(shù)據(jù)查詢 GROUP

20、 BY 子句和子句和 ORDER BY 子句是分別用于分組和排序操作子句是分別用于分組和排序操作的。的。GROUPGROUP子句和子句和HAVINGHAVING子句：子句： group by group by 子句子句對查詢結果進行分組統(tǒng)計對查詢結果進行分組統(tǒng)計，分組后的結果還可以使，分組后的結果還可以使用用having having 子句子句有選擇地篩選輸出有選擇地篩選輸出。3.3.分組查詢和排序查詢分組查詢和排序查詢SELECT 課程號課程號, AVG(成績成績) FROM SCORE GROUP BY 課程號課程號SELECT 課程號課程號, AVG(成績成績), MAX(成績成績),

21、MIN(成績成績) FROM SCORE;GROUP BY 課程號課程號 HAVING COUNT(*)=3SCORE（學號，課程號，成績）5.3 SQLSQL數(shù)據(jù)查詢數(shù)據(jù)查詢 （2 2）排序排序order byorder by SELECT子句的子句的ORDER BY子句可使輸出的查詢結果子句可使輸出的查詢結果按照要求的順序按照要求的順序 排列排列。由于是控制輸出結果，因此。由于是控制輸出結果，因此ORDER BY子句子句只能用于最終的查詢結果只能用于最終的查詢結果。 列名后面有列名后面有ASC，則表示該列名值以，則表示該列名值以升序升序排列；有排列；有DESC，則表示該列，則表示該列 名值

22、以降序排列。名值以降序排列。默認為升序排列默認為升序排列。SELECT * FROM STUDENT ORDER BY 性別性別 ASC, 學號學號 DESCv上機實驗35.3 SQLSQL數(shù)據(jù)查詢數(shù)據(jù)查詢多表查詢：多表查詢：fromfrom后面的數(shù)據(jù)源涉及到后面的數(shù)據(jù)源涉及到多個表。多個表。 在在WHEREWHERE子句中給出子句中給出多表連接的條件多表連接的條件。在輸出的目標列中，如果有多個表的共同字段，字段名前要加以限定。在輸出的目標列中，如果有多個表的共同字段，字段名前要加以限定。格式為：格式為： 表名表名. .字段名字段名4.4.多表查詢多表查詢SELECT SELECT FROM

23、FROM WHERE WHERE 例例5-22 查詢計算機查詢計算機專業(yè)專業(yè)的學生的的學生的學號學號、選修的、選修的課程號課程號和相應的考試和相應的考試成成績績。SELECT 學號學號, 課程號課程號, 成績成績 FROM STUDENT, SCORE;WHERE STUDENT.學號學號 = SCORE.學號學號 AND 專業(yè)專業(yè) = 計算機計算機score(學號,課程號,成績)student(學號,姓名,性別,專業(yè),入學總分,是否保送,出生日期)STUDENT.STUDENT.5.3 SQLSQL數(shù)據(jù)查詢數(shù)據(jù)查詢SELECTSELECT 姓名姓名, , 課程名課程名, , 成績成績 FRO

24、M FROM STUDENT, SCORE, COURSE; STUDENT, SCORE, COURSE;WHEREWHERE STUDENT.STUDENT.學號學號 = SCORE. = SCORE.學號學號; ;AND SCORE.AND SCORE.課程號課程號=COURSE.=COURSE.課程號課程號 AND AND 專業(yè)專業(yè)=計算機計算機 and and 性別性別=女女score(學號學號,課程號課程號,成績)student(學號學號,姓名,性別,專業(yè),入學總分,是否保送,出生日期)course(課程號課程號,課程名,學分)5.3 SQLSQL數(shù)據(jù)查詢數(shù)據(jù)查詢多表查詢練習：多表

25、查詢練習：1 1、查詢所有、查詢所有女生女生的的xmxm、kcbhkcbh及所選修的各門課程的及所選修的各門課程的cjcj。2 2、查詢所有、查詢所有金融金融專業(yè)的學生專業(yè)的學生xmxm、所選修的、所選修的kcmckcmc及及cjcj。3 3、顯示、顯示成績在成績在8080至至9090之間的之間的xh,xm,kcmc,cjxh,xm,kcmc,cj。 學生表：學生表：Xs (xh, xm, xb, csrq, zy, zf, rxzf, photo,bzXs (xh, xm, xb, csrq, zy, zf, rxzf, photo,bz) )選課表：選課表：Xk (xh, kcbh , c

26、jXk (xh, kcbh , cj) )課程表：課程表：Kc (kcbh, kcmc, xs, xfKc (kcbh, kcmc, xs, xf) )5.3 SQL數(shù)據(jù)查詢 嵌套查詢是指一個嵌套查詢是指一個SELECT-FROM-WHERESELECT-FROM-WHERE查詢塊嵌入在另一個查詢塊嵌入在另一個SELECT-FROM-WHERESELECT-FROM-WHERE查查詢塊詢塊WHEREWHERE子句中的查詢。子句中的查詢。子查詢必須用（）括起來子查詢必須用（）括起來 。這類子查詢的結果與父查這類子查詢的結果與父查詢的屬性用詢的屬性用關系運算符關系運算符來比較。來比較。5.5.嵌套

27、查詢嵌套查詢SELECT 學號學號, 姓名姓名 FROM STUDENT WHERE 專業(yè)專業(yè) = ；( SELECT 專業(yè)專業(yè) FROM STUDENT WHERE 姓姓名名 = 王樂王樂 )SELECT DISTINCT 學號學號 FROM SCORE WHERE 成績成績 (SELECT AVG(成績成績) FROM SCORE)說明：說明：在嵌套查詢中，若能確切知道內(nèi)層查詢返回的是在嵌套查詢中，若能確切知道內(nèi)層查詢返回的是單值單值，才可以直接，才可以直接 使用關系運算符進行比較。使用關系運算符進行比較。score(學號學號,課程號課程號,成績)student(學號學號,姓名,性別,專業(yè)

28、,入學總分,是否保送,出生日期)5.3 SQLSQL數(shù)據(jù)查詢數(shù)據(jù)查詢5.5.嵌套查詢嵌套查詢練習：練習：（分析：張明性別是個未知數(shù)，所以要在子查詢中求解）（分析：張明性別是個未知數(shù)，所以要在子查詢中求解）select xm,xb,zy from xs where xbselect xm,xb,zy from xs where xb=;=;(select xb from xs where xm(select xb from xs where xm=張明張明)5.3 SQLSQL數(shù)據(jù)查詢數(shù)據(jù)查詢 使用使用“TOP 數(shù)值表達式數(shù)值表達式 PERCENT”短語顯示滿足條件的前幾條記錄。短語顯示滿足條件的前幾條記錄。 TOP短語要與短語要與ORDER BY短語同時使用才有效。短語同時使用才有效。6.Visual FoxPro6.Visual FoxPro中的幾個特殊選項中的幾個