-vf結構化查詢語言SQL-PPT課件

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

2、 FROM 例1：將“學生”表中的學號、姓名信息檢索出來 select 學號,姓名 from 學生例2：將“學生”表中的專業(yè)信息檢索出來，并去掉重復元組。 select 專業(yè) from 學生distinct例3：查詢“學生”表中的所有信息。方法一： select 學號,姓名,性別,出生日期,專業(yè),家庭住址 ; from 學生方法二： select * from 學生注意：去掉重復元組distinct所有屬性*，Select后多字段名之間2、單表條件查詢格式：SELECT FROM WHERE 例1：查詢成績表中成績大于80分的學號。 select 學號 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：從學生表中檢索出性別為“男” 的學生的學號、姓名、性別、專業(yè)和年齡信息。select 學號,姓名,性別,專業(yè) , ; year(date()-year(出生日期) as 年齡 ;fro

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

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

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

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

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

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

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

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

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

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

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

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

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

17、說明查詢內容來自哪個些表說明條件按某列分組,分組條件對查詢結果排序保存查詢結果SQL 查詢語句的總格式4.2 數據操作本節(jié)主要內容格式1：insert into 表名; values(表達式1,表達式2,表達式n)格式2：insert into 表名(字段名1,字段名2,); values(表達式1,表達式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(學號,課程編號,成績) ;values (2019468006“ ,05, 98)格式：update 表名;set 字段名1=表達式1，字段名2=表達式2. ;where說明：（1）當不使用where時，則更新全部記錄二、更新記錄例1:給score表中所有成績加10分例2:給score表中所有不及格的成績加10分update score set 成績=成績+10update score set 成績=成績+10;where 成績70格式: delete from 表名 where 說明:（1）當不使用where時，邏輯刪除表中全部記錄（2）物

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

20、ue ,字段名2 , foreign key 表達式 tag 表達式 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 “總金額應該大于0!”check 規(guī)則 | error 信息 default 表達式prim

21、ary key | unique三、修改表結構格式二：修改已有字段的類型和寬度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 “總金額應該大于100!”格式六：刪除有效性規(guī)則alter table 表名alter 字段名 drop check例6:刪除總金額字段的有效性規(guī)則alter table 訂購單1;alter 總金額 drop check格式： CREATE VIEW 視圖名 AS SELECT-select1、從單個表派生出來的

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

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

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

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

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

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