-vf結(jié)構(gòu)化查詢語言SQL-PPT課件

1、Visual FoxPro程序設(shè)計(jì)第四章 結(jié)構(gòu)化查詢語言SQL本章主要內(nèi)容SQL語言概述4.1 數(shù)據(jù)查詢4.2 數(shù)據(jù)操作4.3 數(shù)據(jù)定義1.SQL的中英文名稱是什么? 2.SQL的語言功能是什么? -結(jié)構(gòu)化查詢語言-Structured Query LanguageSQL語言概述表4-1 SQL語言的9個(gè)命令動(dòng)詞 本節(jié)主要內(nèi)容1、基本查詢2、排序查詢3、帶特殊運(yùn)算符的條件查詢4、計(jì)算與分組查詢5、嵌套查詢6、利用空值查詢7、查詢中的特殊選項(xiàng)4.1 數(shù)據(jù)查詢 selectfromwhere查詢內(nèi)容（字段名）來自哪（表名）查詢條件（表達(dá)式）一、基本查詢格式：1、單表無條件查詢：格式： SELECT

2、 FROM 例1：將“學(xué)生”表中的學(xué)號、姓名信息檢索出來 select 學(xué)號,姓名 from 學(xué)生例2：將“學(xué)生”表中的專業(yè)信息檢索出來，并去掉重復(fù)元組。 select 專業(yè) from 學(xué)生distinct例3：查詢“學(xué)生”表中的所有信息。方法一： select 學(xué)號,姓名,性別,出生日期,專業(yè),家庭住址 ; from 學(xué)生方法二： select * from 學(xué)生注意：去掉重復(fù)元組distinct所有屬性*，Select后多字段名之間2、單表?xiàng)l件查詢格式：SELECT FROM WHERE 例1：查詢成績表中成績大于80分的學(xué)號。 select 學(xué)號 from score ; where 成

3、績80注：distinct 短語的位置distinct例2：從成績表中檢索出選修了課程編號為“02”的，并且成績大于80分的所有信息。 select * from 成績; where 課程編號=02 and 成績80例3：檢索出選修了課程編號為“02”或“05”的，并且成績大于80分的所有信息。 select * from 成績 ; where (課程編號=02 or 課程編號=05) ; and 成績80例4：從學(xué)生表中檢索出性別為“男” 的學(xué)生的學(xué)號、姓名、性別、專業(yè)和年齡信息。select 學(xué)號,姓名,性別,專業(yè) , ; year(date()-year(出生日期) as 年齡 ;fro

4、m 學(xué)生 ;where 性別=“男”2、單表?xiàng)l件查詢： （1）條件中，涉及到字符型字段值，加定界符(“”)（2）多個(gè)條件之間用and/or相連（3）一行書寫不下，除最后一行，各行結(jié)尾加分號（;）注意3、多表連接查詢：格式: SELECT FROM 表1,表2, ； WHERE 查詢條件 and 連接條件例如學(xué)生表與成績表連接條件為： 學(xué)生.學(xué)號=成績.學(xué)號.=.連接條件例1:檢索出成績大于80分的學(xué)號、姓名、性別和成績select 學(xué)生.學(xué)號,姓名,性別,成績; from 學(xué)生,成績;where 成績80 and 學(xué)生.學(xué)號=成績.學(xué)號例2:檢索學(xué)號為“201931001”的學(xué)生姓名以及所選修

5、的課程名稱和授課教師姓名。select 姓名,課程名稱,教師姓名;from 學(xué)生,課程,成績,教師 ;where 學(xué)生.學(xué)號=成績.學(xué)號 and ; 成績.課程編號=課程.課程編號 and ; 課程. 教師編號=教師.教師編號 and ; 學(xué)生.學(xué)號=“201931001 格式：注： Asc 表示升序 Desc 表示降序selectfrom whereorder by 字段名1 asc|desc,字段名2asc|desc二、排序查詢：例1：查詢學(xué)生表中的全部信息，并按出生日期升序排序。select * ; from 學(xué)生 ; order by 出生日期 asc例2：查詢學(xué)生信息，按學(xué)生的專業(yè)升

6、序排序，若專業(yè)相同，則按學(xué)生的出生日期降序進(jìn)行排序。 select * from 學(xué)生 ; order by 專業(yè) asc , 出生日期 desc1）order by對最終結(jié)果進(jìn)行排序，位置在最后2）asc/desc在排序的字段名后，默認(rèn)為升序3）按多列排序時(shí)，各字段名之間用“，”相隔注意1、確定范圍格式：BETWEENAND 表示在和之間包含界值NOT BETWEENAND 表示不在和之間注意三、帶特殊運(yùn)算符的條件查詢例1：查詢出生日期在1988年8月1日至1990年12月30日之間的學(xué)生的信息。select * from 學(xué)生 ;Where 出生日期 between 1988-08-01

7、; and 1990-12-30select * from 學(xué)生;Where 出生日期= 1988-08-01 ; and 出生日期=80例3：查詢沒有選修課程編號為“02”或“05”并且成績不及格的學(xué)生的學(xué)號、課程編號和成績。select * from score;where 課程編號 not in (02,05) and 成績60 3、部分匹配查詢格式：字段名 like 字符型常量 通配符：%：0個(gè)或多個(gè)字符 _：一個(gè)字符 例4：查詢所有姓“李”的同學(xué)的學(xué)號、姓名、性別、專業(yè)。select 學(xué)號,姓名,性別,專業(yè) from 學(xué)生;where 姓名 like 李%例5：查詢第二個(gè)漢字是“?！?/p>

8、的學(xué)生的學(xué)號、姓名、性別、專業(yè)。select 學(xué)號,姓名,性別,專業(yè) from 學(xué)生;where 姓名 like _海% 4、不等于（!=）例6:查詢家庭住址在哈爾濱并且不是學(xué)商務(wù)英語專業(yè)的學(xué)生信息。select * from 學(xué)生 ;where 家庭住址=哈爾濱 ; and 專業(yè)!=“商務(wù)英語 1、簡單的計(jì)算查詢(用在select中，對查詢結(jié)果中數(shù)值型字段值進(jìn)行計(jì)算 )注: as 新字段名：給結(jié)果指定字段別名（1）count (distinct 字段名)：統(tǒng)計(jì)數(shù)目 count() : 統(tǒng)計(jì)元組個(gè)數(shù)（2）sum (字段名) ：求總和（3）avg (字段名) ：求平均值（4）max (字段名)

9、：求最大值（5）min (字段名) ：求最小值四、計(jì)算與分組查詢例1：統(tǒng)計(jì)學(xué)生表中有多少個(gè)學(xué)生記錄。select count(*) as 人數(shù) from 學(xué)生例2：求學(xué)號為0104的學(xué)生的平均成績。select avg(成績) as 平均分 from 成績;where 學(xué)號=201931001例3：統(tǒng)計(jì)成績表中的最高分和最低分。select max(成績) as 最高分,min(成績) as 最低分;from 成績用在select后count(*)：計(jì)算關(guān)系中所含元組個(gè)數(shù)as 新字段名：給計(jì)算結(jié)果指定字段名注意2、分組與計(jì)算查詢格式：GROUP BY HAVING 例1：檢索出每個(gè)學(xué)生的學(xué)號平

10、均平均分。select 學(xué)號, avg(成績) as 平均分;from 成績 ;group by 學(xué)號例2：檢索出每個(gè)學(xué)生的學(xué)號、總分、平均分、最高分和最低分。select 學(xué)號, sum(成績) as 總分 , ; avg(成績) as 平均分 , ; max(成績) as 最高分 , ; min(成績) as 最低分 ;from 成績 ;group by 學(xué)號例3：在成績表中查詢選修課程在3門以上（含3門）的每個(gè)學(xué)生的學(xué)號和平均成績，并按平均成績升序排序注意：order by后只能接字段名，不能接函數(shù)select 學(xué)號,avg(成績) as 平均成績 from 成績;group by 學(xué)號

11、 having count(*)=3;order by 平均成績select 學(xué)號,avg(成績) as 平均成績 from 成績;group by 學(xué)號 having count(*)=3;order by 平均成績分組條件select 學(xué)生. 學(xué)號,avg(成績) as 平均成績 from 學(xué)生,成績;where 學(xué)生.學(xué)號= 成績.學(xué)號 and; 專業(yè)=“英語”; group by 學(xué)生. 學(xué)號 having count(*)=3;order by 平均成績 desc查詢條件分組條件連接條件注意：先用where選擇滿足條件的元組，然后分組，再計(jì)算，最后去掉不滿足分組條件的記錄例4：檢索英

12、語專業(yè)的學(xué)生選修課程在3門以上（含3門）的每個(gè)學(xué)生的學(xué)號和平均成績，并按平均成績降序排序例5：檢索除“商務(wù)英語”專業(yè)之外的，選修課程在3門以上（含3門）的每個(gè)學(xué)生的學(xué)號、姓名和平均成績，并按將結(jié)果按平均成績降序排序，保存到表aa.dbf中select 學(xué)生.學(xué)號,姓名,avg(成績) as 平均成績;from 學(xué)生,成績;where 學(xué)生.學(xué)號=成績.學(xué)號 and 專業(yè)!=”商務(wù)英語”;group by student. 學(xué)號 having count(*)=3;order by 平均成績 descinto table aa（1）位置：where后，order by前（2）having子句總是

13、跟在group by子句之后,不可以單獨(dú)使用（3）先where,再group by,然后計(jì)算,最后having.注意（查詢結(jié)果出自一個(gè)表，條件涉及多個(gè)表）格式： SELECTFROM 表1 WHERE 字段名 in ； (SELECT 字段名 FROM 表2 WHERE)五、嵌套查詢本節(jié)所用的四個(gè)表例1：檢索沒有授課的教師信息。例2:檢索選修了課程的學(xué)生信息。例3:找出沒有選課的學(xué)生的信息。select * from teacher; where 教師編號 not in (select 教師編號 from course)select * from student; where 學(xué)號 not i

14、n (select 學(xué)號 from score )select * from student; where 學(xué)號 in (select 學(xué)號 from score )格式： IS NULL IS NOT NULL六、利用空值查詢例1：查詢出還沒有確定聯(lián)系方式的學(xué)生記錄。例2：查詢已經(jīng)確定了聯(lián)系方式的學(xué)生信息。select * from student where 聯(lián)系方式 is nullselect * from student where 聯(lián)系方式 is not null不能寫成“=NULL”或“!=NULL” 注意1、顯示部分結(jié)果2、將結(jié)果存放在臨時(shí)文件中 3、將結(jié)果存放在永久表中七、查詢

15、中的特殊選項(xiàng)1、顯示部分結(jié)果（顯示前幾項(xiàng)）格式：top n percent位置：緊跟select后top短語要與order by 同時(shí)使用例1：顯示成績最低的兩名學(xué)生成績信息。例2：顯示成績最高的那40%的學(xué)生成績信息select * top 2 from score order by 成績 ascselect * top 40 percent from score order by 成績 desc2、將結(jié)果存放在臨時(shí)文件中格式：into cursor 臨時(shí)文件名注意:（1）文件關(guān)閉后，自動(dòng)刪除（2）位置：from短語后或最后3、將結(jié)果存放在臨時(shí)文件中例3：將teacher表中的信息保存在臨時(shí)

16、文件“教師”中。select * from teacher into cursor 教師4、將結(jié)果存放在永久表中 格式：into table 表名注意：（1）查看結(jié)果可以使用下面的命令操作use 表名browse（2）通過該子句可實(shí)現(xiàn)表的復(fù)制。 例4 :將所有課程信息按學(xué)分降序保存永久表“課程”中。select * from course order by 學(xué)分 desc； into table 課程Select .From 表名where 查詢條件 and 聯(lián)接條件group by .having.order by. into cursor | into table 字段名, as , 函數(shù)

17、說明查詢內(nèi)容來自哪個(gè)些表說明條件按某列分組,分組條件對查詢結(jié)果排序保存查詢結(jié)果SQL 查詢語句的總格式4.2 數(shù)據(jù)操作本節(jié)主要內(nèi)容格式1：insert into 表名; values(表達(dá)式1,表達(dá)式2,表達(dá)式n)格式2：insert into 表名(字段名1,字段名2,); values(表達(dá)式1,表達(dá)式2,)一、插入記錄例1：向score表中插入一條記錄(2019468006,05,98)例2:向score表中插入一條記錄(05,98)insert into score(課程編號,成績) ;values (05, 98)insert into score ;values(201946800

18、6,05,98)insert into score(學(xué)號,課程編號,成績) ;values (2019468006“ ,05, 98)格式：update 表名;set 字段名1=表達(dá)式1，字段名2=表達(dá)式2. ;where說明：（1）當(dāng)不使用where時(shí)，則更新全部記錄二、更新記錄例1:給score表中所有成績加10分例2:給score表中所有不及格的成績加10分update score set 成績=成績+10update score set 成績=成績+10;where 成績70格式: delete from 表名 where 說明:（1）當(dāng)不使用where時(shí)，邏輯刪除表中全部記錄（2）物

19、理刪除：Pack邏輯刪除三、刪除記錄例1：將teacher表中職稱為助教的教師信息刪除。delete from teacher where 職稱=助教例2：物理刪除student表中年齡為20的學(xué)生記錄delete from student;where year(date()-year(出生日期)=20pack本節(jié)主要內(nèi)容1、定義表2、刪除表3、修改表4、定義視圖4.3 數(shù)據(jù)定義格式： create table|dbf 表名 ( 字段名1 類型(寬度,小數(shù)位數(shù)) null | not null check 規(guī)則 | error 信息 default 表達(dá)式 primary key | uniq

20、ue ,字段名2 , foreign key 表達(dá)式 tag 表達(dá)式 references 表名1 )一、表的定義例1:用SQL CREATE命令建立“倉庫”表create table 倉庫 (; 倉庫號 C(5) primary key,; 面積 N(20),; 城市 C(20) )例2:用SQL CREATE命令建立“職工”表create table 職工 (; 倉庫號 C(5),; 職工號 C(5) primary key,; 工資 I check (工資=1000 and 工資0; error “總金額應(yīng)該大于0!”check 規(guī)則 | error 信息 default 表達(dá)式prim

21、ary key | unique三、修改表結(jié)構(gòu)格式二：修改已有字段的類型和寬度alter table 表名；alter 字段名 類型(寬度)例2:將訂購單表的訂購單號字段的寬度由原來5改為6alter table 訂購單;alter 訂購單號 C(6)格式三：更改字段名alter table 表名；rename column 字段名 to 新字段名例3:將訂購單表的總金額字段名改為金額alter table 訂購單;rename column 總金額 to 金額格式四：刪除已有字段alter table 表名drop column 字段名例4:刪除訂購單1表中的金額字段alter table

22、訂購單1;drop column 金額格式五：修改或定義有效性規(guī)則alter table 表名alter 字段名 set checkerror例5:修改或定義總金額字段的有效性規(guī)則 alter table 訂購單1;alter 總金額 set check 總金額100; error “總金額應(yīng)該大于100!”格式六：刪除有效性規(guī)則alter table 表名alter 字段名 drop check例6:刪除總金額字段的有效性規(guī)則alter table 訂購單1;alter 總金額 drop check格式： CREATE VIEW 視圖名 AS SELECT-select1、從單個(gè)表派生出來的

23、視圖CREATE VIEW chengji_view AS ; SELECT 學(xué)號,課程編號,成績 FROM score基本表四、視圖的定義2、從多個(gè)表派生出來的視圖例：建立一個(gè)視圖xuesheng_view，要求該視圖包含1980年1月1日之后出生的學(xué)生的學(xué)號，姓名，平均成績。 CREATE VIEW xuesheng_view AS; SELECT student.學(xué)號,姓名,avg(成績) as 平均成績; FROM student,score;WHERE student.學(xué)號 = score.學(xué)號and ； 出生日期1980-01-01;GROUP BY score.學(xué)號格式：DROP

24、 VIEW 例：刪除視圖xuesheng_viewDROP VIEW xuesheng_view五、視圖的刪除復(fù)習(xí)1-學(xué)生管理1. 查詢student表中學(xué)生的姓名和年齡。2. 查詢出生日期在1983年2月2日到1985年2月2日之間的學(xué)生的信息。3. 查詢“計(jì)算機(jī)科學(xué)與技術(shù)系”和“中文系”學(xué)生的信息。4、從student表中檢索出性別為“男”且入學(xué)時(shí)間為2019年9月7日的學(xué)生的學(xué)號、姓名、性別、專業(yè)和年齡信息。（注：用表達(dá)式y(tǒng)ear(date()-year(出生日期)可以計(jì)算出學(xué)生的年齡。）answer3、select * from student ;where 專業(yè)=計(jì)算機(jī)科學(xué)與技術(shù) o

25、r 專業(yè)=中文2、select * from student ；where 出生日期= 1983-02-02 and ； 出生日期=1985-02-021、 select 姓名,year(date()-year(出生日期) as 年齡; from student4、select 學(xué)號,姓名,性別,專業(yè),; year(date()-year(出生日期) as 年齡 from student; where 性別=“.F.” and 入學(xué)時(shí)間=2019-9-7復(fù)習(xí)1-訂貨管理1、從職工表中檢索所有工資信息。2、查詢倉庫表中的所有信息。3、查詢工資多于1230的職工號。4、查詢哪些倉庫有工資多于121

26、0元的職工。5、查詢在倉庫“WH1”或“WH2”工作，并且工資少于1250元的職工號。6、查詢工資多于1230元的職工號和他們所在的城市。7、按職工的工資值升序檢索出全部職工信息。8、先按倉庫號排序，再按工資排序并輸出全部職工信息。9、找出工作在面積大于400的倉庫的職工號以及這些職工工作所在的城市。訂貨管理復(fù)習(xí)2-訂貨管理4、查詢哪些倉庫有工資多于1210元的職工。8、先按倉庫號排序，再按工資排序并輸出全部職工信息。9、找出工作在面積大于400的倉庫的職工號以及這些職工工作所在的城市。 14、求在北京和上海的倉庫職工的工資總和。15、找出地址不在北京的全部供應(yīng)商信息 。復(fù)習(xí)2-學(xué)生管理1、檢

27、索出選修了“藝術(shù)設(shè)計(jì)”的學(xué)生的信息及成績，并將結(jié)果按出生日期升序排列，出生日期相同的按成績降序排列, 最后將查詢結(jié)果保存到表cc.dbf中。5、檢索出姓郭的學(xué)生的姓名和入學(xué)時(shí)間，并按入學(xué)時(shí)間降序排列，最后將查詢結(jié)果保存到表xs.dbf中4、檢索出成績在60和80之間的學(xué)生的姓名、課程名稱和成績,最后將查詢結(jié)果保存到表dd.dbf中“學(xué)生管理”數(shù)據(jù)庫復(fù)習(xí)2-分組和計(jì)算（學(xué)生管理）Eg1：檢索出所有學(xué)生的學(xué)號、總分、平均分、最高分和最低分。select 學(xué)號, sum(成績) as 總分,; avg(成績) as 平均分,; max(成績) as 最高分,; min(成績) as 最低分;from

28、 score復(fù)習(xí)2-分組和計(jì)算（學(xué)生管理）Eg2：檢索出所有學(xué)生的學(xué)號、姓名、總分、平均分、最高分和最低分。select student.學(xué)號, 姓名,; sum(成績) as 總分,; avg(成績) as 平均分,; max(成績) as 最高分,; min(成績) as 最低分;from student, score;where student.學(xué)號= score.學(xué)號復(fù)習(xí)2-分組和計(jì)算（學(xué)生管理）Eg3：檢索出“計(jì)算機(jī)科學(xué)與技術(shù)系”所有學(xué)生的學(xué)號、姓名、總分、平均分、最高分和最低分，并將結(jié)果按總分降序排序。select student.學(xué)號, 姓名, sum(成績) as 總分,; avg(成績) as