數(shù)據(jù)庫軟件Visual Foxpro課件第七章 結構化查詢語言SQL

1、1第七章第七章 結構化查詢語言結構化查詢語言sql sql sql語言功能包括數(shù)據(jù)定義、數(shù)據(jù)查詢、數(shù)據(jù)操縱、和數(shù)據(jù)控語言功能包括數(shù)據(jù)定義、數(shù)據(jù)查詢、數(shù)據(jù)操縱、和數(shù)據(jù)控制四個方面。制四個方面。7.1 sql7.1 sql的數(shù)據(jù)定義功能的數(shù)據(jù)定義功能 sqlsql的數(shù)據(jù)定義功能用于定義數(shù)據(jù)表的結構，如創(chuàng)建、修改、的數(shù)據(jù)定義功能用于定義數(shù)據(jù)表的結構，如創(chuàng)建、修改、刪除數(shù)據(jù)表。刪除數(shù)據(jù)表。一一 創(chuàng)建數(shù)據(jù)表創(chuàng)建數(shù)據(jù)表 命令：命令：create table ( ,), ) 功能：創(chuàng)建數(shù)據(jù)表結構功能：創(chuàng)建數(shù)據(jù)表結構 例例7-1： create table stud(學號學號 c(8),姓名姓名 c(8),性

2、別性別 c(2),出生日期出生日期 d,; 專業(yè)號專業(yè)號 c(2),入?？偡秩胄？偡?n(6,2),團員團員 l,簡歷簡歷 m,照片照片 g)2二二 修改數(shù)據(jù)表修改數(shù)據(jù)表 命令命令1：alter table add/alter column ,小數(shù)位小數(shù)位 命令命令2： alter table drop column rename column to 功能：修改表的結構功能：修改表的結構例例7-2：在：在stud中增加一個中增加一個“電話電話”字段字段 alter table stud add column 電話電話 c(8)例例7-3：將：將stud中電話字段的寬度該為中電話字段的寬度該為1

3、0 alter table stud alter 電話電話 c(10) 例例7-4：刪除：刪除stud中的電話字段中的電話字段 alter table stud drop 電話電話例例7-5：將：將stud中中“入?？偡秩胄？偡帧弊侄卧撁麨樽侄卧撁麨椤案呖汲煽兏呖汲煽儭?alter table stud rename 入?？偡秩胄？偡?to 高考成績高考成績37.2 sql7.2 sql的數(shù)據(jù)查詢功能的數(shù)據(jù)查詢功能一一 select命令的格式命令的格式 格式：格式：select all/distinct top percent. as , as from , inner/left/right/

4、full join on into /to file/to printer/to screen where and and/orand/or group by , having order by asc/desc, asc/desc 功能：實現(xiàn)數(shù)據(jù)查詢。功能：實現(xiàn)數(shù)據(jù)查詢。select語句的執(zhí)行過程為：根據(jù)語句的執(zhí)行過程為：根據(jù)where子子句的連接和檢索條件，從句的連接和檢索條件，從from子句指定的基本表或視圖中選取子句指定的基本表或視圖中選取滿足條件的元組，再按照滿足條件的元組，再按照select子句中指定的列表達式，選出子句中指定的列表達式，選出元組中的屬性值形成結果表。元組中的屬性值

5、形成結果表。4說明：說明：(1)from , : 指明要查詢的所有數(shù)據(jù)表指明要查詢的所有數(shù)據(jù)表(2) . as ：可以可以是字段名是字段名,也可是表達式，也可是表達式， 而而是字段所在的表名；是字段所在的表名； as表示可以給輸出時顯示的列標題重新命名表示可以給輸出時顯示的列標題重新命名二二 投影查詢投影查詢 例例7-7：查詢查詢stud表中前表中前5列的信息列的信息 select 學號學號,姓名姓名,入?？偡秩胄？偡?性別性別,出生日期出生日期 from stud 例例7-8：查詢查詢stud表中入?？偡肿畹偷奈迕麑W生基本情況，按入校表中入?？偡肿畹偷奈迕麑W生基本情況，按入?？偡钟傻偷礁哌M行

6、排列，同時指定表中的字段在查詢結果中的總分由低到高進行排列，同時指定表中的字段在查詢結果中的列標題列標題 sele top 5 學號學號 as 學生編號學生編號,姓名姓名 as 學生姓名學生姓名,性別性別 as 學生性別學生性別,; 入?？偡秩胄？偡?as 高考分數(shù)高考分數(shù) from stud order by 入?？偡秩胄？偡?例例7-9：在在xk中查詢選修了課程的學號中查詢選修了課程的學號 sele distinct 學號學號 from xk5三三 條件查詢條件查詢 當要在數(shù)據(jù)表中找出滿足某些條件的行時，則需使用當要在數(shù)據(jù)表中找出滿足某些條件的行時，則需使用where子句指定查詢條件子句指

7、定查詢條件(格式：格式：where ) 1. 比較大小比較大小 例例7-11：在在stud.dbf中查詢專業(yè)號為中查詢專業(yè)號為“01”的學生記錄的學生記錄 例例7-12：在：在stud.dbf中查詢入學總分大于等于中查詢入學總分大于等于550分的學生學號、分的學生學號、姓名、性別、出生日期和入學總分姓名、性別、出生日期和入學總分 sele 學號學號,姓名姓名,性別性別,出生日期出生日期,入校總分入?？偡?from stud where; 入?？偡秩胄？偡?550sele * from stud where 專業(yè)號專業(yè)號=012. 多重條件查詢多重條件查詢 當當where子句需要指定一個以上的查

8、詢條件時，則需要使用邏子句需要指定一個以上的查詢條件時，則需要使用邏輯運算符輯運算符and，or和和not將其連接成復合的邏輯表達式。將其連接成復合的邏輯表達式。例例7-13：在在stud.dbf中查詢男團員的學號、姓名、性別和入?？偡?。中查詢男團員的學號、姓名、性別和入?？偡帧ele 學號學號,姓名姓名,性別性別,入?？偡秩胄？偡?from stud where 性別性別=男男 and 團員團員63. 確定范圍確定范圍 例例7-14：在在js中查詢工資在中查詢工資在1500至至2000之間的教師的教師號、姓之間的教師的教師號、姓名及職稱。名及職稱。 sele 教師號教師號,姓名姓名,職稱職

9、稱 from js where 工資工資=1500 and 工資工資=80sele 學號學號,課程號課程號,成績成績 from xk where 課程號課程號 in (c140 ,c160); and 成績成績=807例例7-17：在：在xk中查詢沒有選修中查詢沒有選修c140，也沒有選修，也沒有選修c160的課程并且的課程并且成績在成績在85和和90之間的學生的學號、課程號和成績之間的學生的學號、課程號和成績 sele 學號學號,課程號課程號,成績成績 from xk where 課程號課程號 not in; (c140 ,c160) and 成績成績 between 85 and 905.

10、 部分匹配查詢部分匹配查詢 也稱模糊查詢，使用也稱模糊查詢，使用like或或not like子句定義，格式：子句定義，格式： like %：表示任意長度的字符串：表示任意長度的字符串 _：表示任意一個字符或漢字：表示任意一個字符或漢字例例7-18：查詢查詢stud表中所有姓表中所有姓“劉劉”的學生的學號和姓名的學生的學號和姓名 例例7-19：查詢查詢stud表中第二個漢字是表中第二個漢字是“敏敏”的學生的學號和姓名的學生的學號和姓名 sele 學號學號,姓名姓名 from stud where 姓名姓名 like 劉劉%sele 學號學號,姓名姓名 from stud where 姓名姓名 l

11、ike _敏敏%8四四. 統(tǒng)計查詢統(tǒng)計查詢常用函數(shù)及功能：常用函數(shù)及功能： avg sum count max min說明：在以上函數(shù)中，可使用說明：在以上函數(shù)中，可使用all或或distinct distinct：表示在計算時取消指定列中的重復值：表示在計算時取消指定列中的重復值 all(默認值默認值)：表示不取消重復值：表示不取消重復值例例7-20：求學號為求學號為“s0401109”學生的總分和平均分并顯示如圖學生的總分和平均分并顯示如圖 例例7-21：求選修課程號為：求選修課程號為c140學生的最高分、最低分及之間相差學生的最高分、最低分及之間相差的分數(shù)的分數(shù)(顯示課程號顯示課程號)

12、sele 學號學號,sum(成績成績) as 總分總分,avg(成績成績) as 平均分平均分 from xk; where 學號學號=s0401109sele 課程號課程號, max(成績成績) as 最高分最高分,min(成績成績) as 最低分最低分,max(成成; 績績)-min(成績成績) as 相差分數(shù)相差分數(shù) from xk where 課程號課程號=c1409例例7-22：求入?？偡衷冢呵笕胄？偡衷?80分以上的學生的人數(shù)分以上的學生的人數(shù) sele count(學號學號) as 入校總分在入?？偡衷?80分以上的學生的人數(shù)分以上的學生的人數(shù) from; stud where

13、入?？偡秩胄？偡?580例例7-23：統(tǒng)計：統(tǒng)計xk表中有多少門課表中有多少門課 sele count(distinct 課程號課程號) as 選課表中課程數(shù)選課表中課程數(shù) from xk例例7-24：利用特殊函數(shù)：利用特殊函數(shù)count(*)求求js表中表中“教授教授”和和“副教授副教授”的人的人數(shù)數(shù) sele count(*) as 教授和副教授的人數(shù)教授和副教授的人數(shù) from js ; where 職稱職稱 in(“教授教授”,“副教授副教授”)五五. 分組查詢分組查詢 將查詢結果按照指定字段相同的值進行分組，使用將查詢結果按照指定字段相同的值進行分組，使用group by子句子句例例

14、7-25：查詢各位教師的教師號及其任課的門數(shù)查詢各位教師的教師號及其任課的門數(shù) sele 教師號教師號,count(*) as 任課門數(shù)任課門數(shù) from sk group by 教師號教師號10例例7-26：查詢選修兩門以上課程的學生學號和選課門數(shù)：查詢選修兩門以上課程的學生學號和選課門數(shù) 例例7-27：在課程：在課程c120、c140、c150、c160中查詢學生平均成績在中查詢學生平均成績在80分以上課程的學生的平均分（顯示課程號）分以上課程的學生的平均分（顯示課程號） select 課程號課程號,avg(成績成績) as 平均分平均分 from xk group by 課程號課程號;

15、having 課程號課程號 in (c120,c140,c150,c160) and avg(成績成績)=80六六. 查詢的排序查詢的排序 當需要對查詢結果排序時，可以使用當需要對查詢結果排序時，可以使用order by 子句對查詢的結子句對查詢的結果按一個或多個屬性列進行升序果按一個或多個屬性列進行升序(asc)或降序或降序(desc)排列排列例例7-28：查詢選修了課程查詢選修了課程“c140”的學生學號和成績，并按成績降序的學生學號和成績，并按成績降序 排列排列select 學號學號 as 選課門數(shù)在兩門以上學生的學號選課門數(shù)在兩門以上學生的學號,count(*) as; 選課門數(shù)選課門

16、數(shù) from xk group by 學號學號 having count(*)=211 select 學號學號,成績成績 from xk where 課程號課程號=c140 order by 成績成績 desc例例7-29：查詢選修：查詢選修c120、c130和和c150課程學生的學號、課程號和成課程學生的學號、課程號和成績，查詢結果按課程號升序排列，課程號相同再按成績降序排績，查詢結果按課程號升序排列，課程號相同再按成績降序排列列 select 學號學號,課程號課程號,成績成績 from xk where 課程號課程號; in(c120,c130,c150) order by 課程號課程號,

17、成績成績 desc例例7-30：求選課在三門以上且各門課程均及格的學生的學號及平均：求選課在三門以上且各門課程均及格的學生的學號及平均成績，查詢結果按學號降序列出成績，查詢結果按學號降序列出 select 學號學號,avg(成績成績) as 平均成績平均成績 from xk group by 學號學號 having; count(*)=3 and 成績成績=60 order by 學號學號 desc七七 . 連接查詢連接查詢 通過加在連接字段上的條件將多個表連接起來，以便從多個表通過加在連接字段上的條件將多個表連接起來，以便從多個表 中查詢數(shù)據(jù)中查詢數(shù)據(jù)121. 等值連接與非等值連接等值連接與

18、非等值連接例例7-31：查詢陳靜老師所講授課程的課程號，教師號和姓名查詢陳靜老師所講授課程的課程號，教師號和姓名 inner join : 內連接，顯示符合條件的記錄內連接，顯示符合條件的記錄 例例7-32：查詢選修了課程：查詢選修了課程“數(shù)據(jù)庫數(shù)據(jù)庫”或或“電子商務電子商務”學生的學號、學生的學號、姓名選課名和成績姓名選課名和成績 select stud.學號學號,姓名姓名,課程名課程名,成績成績 from stud,xk ,kc where stud.學號學號; =xk.學號學號 and xk.課程號課程號=kc.課程號課程號 and 課程名課程名 in(數(shù)據(jù)庫數(shù)據(jù)庫,; 電子商務電子商務

19、)select js.教師號教師號,js.姓名姓名,sk.課程號課程號 from js inner join sk on js.教師號教師號; =sk.教師號教師號 and 姓名姓名=陳靜陳靜select js.教師號教師號,js.姓名姓名,sk.課程號課程號 from js,sk; where js.姓名姓名=陳靜陳靜 and js.教師號教師號=sk.教師號教師號 132. 自身連接自身連接例例7-33：查詢所有比查詢所有比“陳靜陳靜”工資高的教師姓名、職稱、工資和工資高的教師姓名、職稱、工資和“陳靜陳靜”工資工資 例例7-34:查詢與查詢與“周密周密”職稱相同的教師姓名、性別和職稱職稱相

20、同的教師姓名、性別和職稱 select x.姓名姓名,x.性別性別,x.職稱職稱 from js as x,js as y; where x.職稱職稱=y.職稱職稱 and y.姓名姓名=周密周密select x.姓名姓名,x.工資工資, x.職稱職稱,y.工資工資 as 陳靜工資陳靜工資 from js as x,js as; y where x.工資工資 y.工資工資 and y.姓名姓名=陳靜陳靜xy3. 外連接外連接例例7-35：查詢教師姓名、職稱及所授課程的課程名查詢教師姓名、職稱及所授課程的課程名 select 姓名姓名,職稱職稱,課程名課程名 from js,sk,kc wher

21、e js.教師號教師號=sk.教師號教師號; and sk.課程號課程號=kc.課程號課程號14說明：外連接中，參與連接的表有主從之分，以主表中的每行數(shù)據(jù)說明：外連接中，參與連接的表有主從之分，以主表中的每行數(shù)據(jù)去匹配從表中的數(shù)據(jù)列，符合連接條件的數(shù)據(jù)將直接顯示，對去匹配從表中的數(shù)據(jù)列，符合連接條件的數(shù)據(jù)將直接顯示，對于那些不符合條件的列，將填上于那些不符合條件的列，將填上null值顯示。左連接的主表在值顯示。左連接的主表在關鍵字關鍵字left join的左方，右連接的主表在關鍵字的左方，右連接的主表在關鍵字right join的右的右方方例例7-36：在在js表中添加一條記錄，以外連接的方式

22、查詢教師姓名、表中添加一條記錄，以外連接的方式查詢教師姓名、職稱及所授課程的課程號職稱及所授課程的課程號 select 姓名姓名,職稱職稱,課程號課程號 from js left join sk on js.教師號教師號=sk.教師號教師號 select 姓名姓名,職稱職稱,課程號課程號 from sk left join js on js.教師號教師號=sk.教師號教師號select 姓名姓名,職稱職稱,課程號課程號 from js right join sk on js.教師號教師號=sk.教師號教師號select 姓名姓名,職稱職稱,課程號課程號 from sk right join j

23、s on js.教師號教師號=sk.教師號教師號select 姓名姓名,職稱職稱,課程號課程號 from js full join sk on js.教師號教師號=sk.教師號教師號select 姓名姓名,職稱職稱,課程號課程號 from sk full join js on js.教師號教師號=sk.教師號教師號15八八. 嵌套查詢嵌套查詢 在在where子句中包含一個形如子句中包含一個形如select-from-where的查詢塊，的查詢塊，此查詢塊稱為嵌套查詢或子查詢，包含子查詢的語句稱為外部此查詢塊稱為嵌套查詢或子查詢，包含子查詢的語句稱為外部查詢或父查詢。查詢或父查詢。1. 返回一個

24、值的子查詢返回一個值的子查詢 當子查詢的返回值只有一個時，可以使用比較運算符將父當子查詢的返回值只有一個時，可以使用比較運算符將父查詢和子查詢連接起來查詢和子查詢連接起來 例例7-37：查詢與查詢與“陳靜陳靜”教師職稱相同的教師姓名和職稱教師職稱相同的教師姓名和職稱 2. 返回一組值的子查詢返回一組值的子查詢 當子查詢的返回值不止一個時，則不能直接使用比較運算當子查詢的返回值不止一個時，則不能直接使用比較運算符，可以在比較運算符和子查詢之間插入符，可以在比較運算符和子查詢之間插入any或或allselect 姓名姓名,職稱職稱 from js where 職稱職稱=; (select 職稱職稱

25、 from js where 姓名姓名=陳靜陳靜)16(1) 使用使用any（any的含義為任意一個）的含義為任意一個）例例7-38：查詢講授課程號為查詢講授課程號為“c140”的教師姓名的教師姓名 例例7-39：查詢比男生入?？偡肿畹头指叩呐彰腿胄？偡郑翰樵儽饶猩胄？偡肿畹头指叩呐彰腿胄？偡?select 姓名姓名,入?？偡秩胄？偡?from stud where 性別性別=女女 and 入校總分入?？偡謅ny; (sele 入校總分入?？偡?from stud where 性別性別=男男)說明：可以使用說明：可以使用in 代替代替“=any”select 姓名姓名 from

26、js where 教師號教師號=any; (sele 教師號教師號 from sk where 課程號課程號=c140)17(2) 使用使用all（all含義為全部）含義為全部）例例7-41：查詢比女生入?？偡肿罡叻指叩哪猩彰腿胄？偡植樵儽扰胄？偡肿罡叻指叩哪猩彰腿胄？偡謘elect 姓名姓名,入校總分入?？偡?from stud where 性別性別=男男 and 入校總分入?？偡謅ll; (sele 入?？偡秩胄？偡?from stud where 性別性別=女女)18(4) 使用使用exists exists用于判斷查詢結果是否存在。當用于判斷查詢結果是否存在。當exists

27、的子查詢結果存在的子查詢結果存在即即非空時，則外層的非空時，則外層的where子句返回真值，否則返回假值子句返回真值，否則返回假值例例7-42：查詢講授課程號為：查詢講授課程號為“c140”的教師姓名的教師姓名 select 姓名姓名 from js where exists; (select * from sk ; where js.教師號教師號=sk.教師號教師號 and 課程號課程號=c140) 19九九 查詢結果的輸出查詢結果的輸出 into table ：輸出到數(shù)據(jù)表；：輸出到數(shù)據(jù)表； into cursor ：輸出到臨時表。臨時表只存儲在內存中，：輸出到臨時表。臨時表只存儲在內存中

28、，不作為永久的數(shù)據(jù)表存儲在磁盤中不作為永久的數(shù)據(jù)表存儲在磁盤中例例7-43：查詢陳靜老師所講授課程的課程號，教師號和姓名，輸出：查詢陳靜老師所講授課程的課程號，教師號和姓名，輸出到臨時表到臨時表,然后顯示該表的內容然后顯示該表的內容 7.3 sql7.3 sql的數(shù)據(jù)更新功能的數(shù)據(jù)更新功能一一 插入數(shù)據(jù)記錄插入數(shù)據(jù)記錄1. 插入一行新記錄：插入一行新記錄： insert into 表名表名(字段名字段名1,字段名字段名2, ) values(表達式表達式1,表達表達式式2 )select js.教師號教師號,js.姓名姓名,sk.課程號課程號 from js,sk where js.姓名姓名=

29、陳靜陳靜 ; and js.教師號教師號=sk.教師號教師號 into cursor cx1brow202. 插入多行記錄插入多行記錄 將一個表中的數(shù)據(jù)抽取數(shù)行插入另一表中，可以通過子查詢將一個表中的數(shù)據(jù)抽取數(shù)行插入另一表中，可以通過子查詢來實現(xiàn)來實現(xiàn)例例7-45：求出各學生選修課的平均成績，把結果存放在新表學生平：求出各學生選修課的平均成績，把結果存放在新表學生平 均成績中均成績中 create table 學生平均成績學生平均成績(姓名姓名 c(8),平均成績平均成績 n(3) insert into 學生平均成績學生平均成績 sele 姓名姓名,avg(成績成績) from stud,xk; where stud.學號學