1、第七講 SQL高級查詢語句,西南林業(yè)大學:魯 瑩 學時：2節(jié),教學內(nèi)容,7.1 特殊查詢語句 7.2 聯(lián)接查詢 7.2.1 Where聯(lián)接 7.2.2 JOIN聯(lián)接 7.2.3 自聯(lián)接 7.3 嵌套查詢 7.4 查詢結(jié)果輸出 7.5 數(shù)據(jù)定義語言 7.6 綜合應(yīng)用示例,教學目標及重點難點,教學目標 掌握特殊查詢語句的使用方法 掌握聯(lián)接查詢的使用方法 掌握嵌套查詢中IN關(guān)鍵字的使用方法 了解數(shù)據(jù)表創(chuàng)表語句 掌握視圖的創(chuàng)建和使用方法 能靈活應(yīng)用SQL語句解決實際問題 重點難點 聯(lián)接查詢、子查詢的理解及應(yīng)用 創(chuàng)表語句靈活使用,7.1.1 空值查詢,空值判斷查詢條件常用來搜索某一字段值為空值的記錄，可

2、以使用IS NULL或IS NOT NULL關(guān)鍵字來指定這種查詢條件。 注：NULL值表示字段的數(shù)據(jù)值未知或不可用，它并不表示零（數(shù)字值或二進制值）、零長度的字符串或空白（字符值）。 Select * From ClassInfo Where ClassDesc is null Select * From ClassInfo Where ClassDesc is Not null,7.1.2 集合的并運算(Union),UNION 運算符將兩個或多個 SELECT 語句的結(jié)果組合成一個結(jié)果集。使用 UNION 組合的結(jié)果集都必須滿足下列條件： 具有相同的結(jié)構(gòu) 字段數(shù)目相同 結(jié)果集中相應(yīng)字段的數(shù)

3、據(jù)類型必須兼容 UNION運算符的指定格式如下： SELECT 語句 UNION ALL SELECT 語句,UNION示例,-Union聯(lián)接多個結(jié)果集 Select * From StudScoreInfo Where StudScore=60 And StudScore=90 And StudScore=90 And StudScore=60 And StudScore=70,學生各分數(shù)段等級人數(shù),Select 優(yōu)秀,Count(*) From StudScoreInfo; Where StudNo=99070470 And StudScore=90; Union All; Select

4、良好,Count(*) From StudScoreInfo; Where StudNo=99070470 And StudScore=80 And StudScore=70 And StudScore80; Union All; Select 不及格,Count(*) From StudScoreInfo; Where StudNo=99070470 And StudScore60,7.2 聯(lián)接查詢,當一個查詢同時涉及兩個以上的表時，稱為聯(lián)接查詢。 聯(lián)接查詢是通過各個表之間共同屬性列的關(guān)聯(lián)來查詢數(shù)據(jù)，數(shù)據(jù)表之間的聯(lián)系是通過表的字段值來體現(xiàn)的 聯(lián)接操作的目的就是通過加在聯(lián)接字段上的條件將多個表

5、聯(lián)接起來，以便從多個表中查詢數(shù)據(jù)。 聯(lián)接查詢是關(guān)系數(shù)據(jù)庫中最主要的查詢，包括等值與非等值聯(lián)接查詢、自身聯(lián)接查詢、外聯(lián)接查詢等。 表的聯(lián)接方法有兩種： 方法1：表之間滿足一定條件的行進行聯(lián)接，此時FROM子句中指明進行聯(lián)接的表名，WHERE子句指明聯(lián)接的列名及其聯(lián)接條件。 方法2：利用關(guān)鍵字JOIN進行聯(lián)接。,7.2.1 Where聯(lián)接,Select StudInfo.StudNo,StudInfo.StudName,StudInfo.ClassID,ClassInfo.ClassName From StudInfo,ClassInfo Where StudInfo.ClassID=ClassI

6、nfo.ClassID And StudInfo.StudNo in(99070470,99070401) StudInfo.StudNo in(99070470,99070401)為查詢條件，而“StudInfo.ClassID=ClassInfo.ClassID為聯(lián)接條件，“ClassID”為聯(lián)接字段。 聯(lián)接條件的一般格式為： . . 其中,比較運算符主要有：、！。 引用兩個表中的列名相同的字段時必須用表名前綴限制字段來自于哪個表,Where聯(lián)接,Select S.Studno,S.StudName,C.ClassName,SI.CourseID,SI.StudScore From Stu

7、dInfo S,ClassInfo C,StudScoreInfo SI Where S.Studno=SI.StudNo And C.ClassID=S.ClassID And S.StudNo=99070470,7.2.2 JOIN聯(lián)接,具體分為以下幾種： INNER JOIN ：顯示符合條件的記錄，此為默認值； LEFT JOIN：顯示符合條件的數(shù)據(jù)行以及左邊表中不符合條件的數(shù)據(jù)行，此時右邊數(shù)據(jù)行會以NULL來顯示，此稱為左聯(lián)接； RIGHT JOIN：顯示符合條件的數(shù)據(jù)行以及右邊表中不符合條件的數(shù)據(jù)行，此時左邊數(shù)據(jù)行會以NULL來顯示，此稱為右聯(lián)接； FULL JOIN：顯示符合條件的

8、數(shù)據(jù)行以及左邊表和右邊表中不符合條件的數(shù)據(jù)行，此時缺乏數(shù)據(jù)的數(shù)據(jù)行會以NULL來顯示； 當將JOIN 關(guān)鍵詞放于FROM子句中時，應(yīng)有關(guān)鍵詞ON與之相對應(yīng)，以表明聯(lián)接的條件。,Join聯(lián)接,Select StudInfo.StudNo,StudInfo.StudName,StudInfo.ClassID,ClassInfo.ClassName From StudInfo Inner Join ClassInfo On StudInfo.ClassID=ClassInfo.ClassID Classinfo.classname, Courseinfo.courseid, Courseinfo.c

9、oursename, Studscoreinfo.studscore; FROM classinfo INNER JOIN StudInfo; INNER JOIN studscoreinfo INNER JOIN courseinfo ; ON Courseinfo.courseid = Studscoreinfo.courseid ; ON Studinfo.studno = Studscoreinfo.studno ; ON Classinfo.classid = Studinfo.classid And StudInfo.StudNo=99070470,Join聯(lián)接,Select St

10、udInfo.StudNo,StudInfo.StudName,StudSex,StudScoreInfo.CourseID,StudScore From StudInfo left join StudScoreInfo On StudInfo.Studno=StudScoreinfo.StudNo Select StudInfo.StudNo,StudInfo.StudName,StudSex,StudScoreInfo.CourseID,StudScore From StudInfo Right join StudScoreInfo On StudInfo.Studno=StudScore

11、info.StudNo Select StudInfo.StudNo,StudInfo.StudName,StudSex,StudScoreInfo.CourseID,StudScore From StudInfo Full join StudScoreInfo On StudInfo.Studno=StudScoreinfo.StudNo,7.2.3 自聯(lián)接,當一個表與其自已進行聯(lián)接操作時，稱為表的自聯(lián)接。表的自身聯(lián)接主要用于同一個表中數(shù)據(jù)的比較。 Select Si.StudNo,Si.StudName,Si.StudSex From StudInfo S,StudInfo SI Wher

12、e S.StudNo=99070478 And SI.StudNameS.StudName Select Si.StudNo,Si.StudName,Si.StudSex From StudInfo S,StudInfo SI Where S.StudNo=99070478 And S.StudSex=SI.StudSex,自聯(lián)接,select * from studinfoback Select A.StudNo,A.StudName,A.StudSex,B.StudNo,B.StudName,B.Manager From StudInfoBack A,StudInfoBack B Wher

13、e A.StudName=B.Manager,7.3 嵌套查詢,在WHERE子句中包含一個形如SELECT-FROM-WHERE的查詢塊，此查詢塊稱為嵌套查詢或子查詢，包含子查詢的語句稱為父查詢或外部查詢，子查詢的嵌套層次最多可達到255層 嵌套查詢在執(zhí)行時由里向外處理，即先執(zhí)行子查詢再執(zhí)行父查詢，父查詢要用到子查詢的結(jié)果。 1. 返回一個值的子查詢 當子查詢的返回值只有一個時，可以使用比較運算符（=, , =, (Select Avg(StudScore) From StudScoreInfo Where StudNo=99070470),2.返回一組值的子查詢,如果子查詢的返回值不止一個，

14、而是一個集合時，則不能直接使用比較運算符，可以在比較運算符和子查詢之間插入ANY或ALL。其具體含義詳見以下各例。 （1）使用ANY， 含義為任意一個 Select * From StudScoreInfo Where StudScore= Any(Select StudScore From StudScoreInfo Where StudNo=99070470) ( StudNo C (15) PRIMARY KEY NOT NULL; StudName C (20) NOT NULL,; StudSex C (2) DEFAULT 男 CHECK StudSex=男 OR StudSex=

15、女 ERROR StudSEX必須是男或女 ,; StudBirthDay D CHECK（StudBirthDay=1970-01-01,; StudScore N (5,1) ,; Level L.; StudDesc M,; Photo G ) 為續(xù)行號，字段之間用逗號隔開,7.5.2 修改數(shù)據(jù)表,SQL語言使用ALTER TABLE命令修改基本表的結(jié)構(gòu)，增加、刪除和修改字段，還可以增加、刪除和修改如主索引、侯選索引、有效性規(guī)則等結(jié)構(gòu) 主要有兩種格式 語法一： ALTER TABLE ADD /ALTERCOLUMN ，小數(shù)位 NULL/NOT NULL CHECK ERROR DEFA

16、ULT PRIMARY KEY/UNIQUE 功能：修改基本表的結(jié)構(gòu)。 ADD用于增加新列。 ALTER用于修改某列。,修改表結(jié)構(gòu)命令2,ALTER TABLE DROPCOLUMN SET DEFAULT DROP DEFAULT SET CHECK ERROR DROP CHECK ADD PRIMARY KEY DROP PRIMARY KEY ADD UNIQUE DROP UNIQUE TAG RENAME COLUMN TO 說明 DROP用于刪除列、默認值、有效性規(guī)則、主鍵、候選索引。 SET 用于為已有的字段設(shè)置默認值和有效性規(guī)則。 ADD 用于添加主索引和候選索引。 RENA

17、ME 用于修改表中的一個字段名。,修改表示例,ALTER TABLE StudInfo1; ALTER COLUMN LastStudScore N(3) CHECK(LastStudScore =500) ERROR “入?？偡直仨毚笥?00” ALTER TABLE StudInfo1； RENAME COLUMN LastStudScore TO 高考成績,7.5.3 刪除數(shù)據(jù)表,當某個數(shù)據(jù)表無用時，可將其刪除。刪除后，該表中的數(shù)據(jù)和在此表上所建的索引都被刪除。 語法 DROP TABLE 功能 刪除數(shù)據(jù)表 示例 DROP TABLE StudInfo1,7.6 綜合應(yīng)用示例,假設(shè)圖書管

18、理數(shù)據(jù)庫中有3個表，圖書.dbf、讀者.dbf和借閱.dbf。它們的結(jié)構(gòu)分別如下： 圖書（總編號C(6)，書名C（16），作者C（6），出版單位C（20），單價N（6，2） 讀者（借書證號C（4），姓名C（6），性別C（2），地址C（20） 借閱（借書證號C（4），總編號C（6），借書日期D（8） 在上述圖書管理數(shù)據(jù)庫中，圖書的主索引是總編號，讀者的主索引是借書證號，借閱的主索引是借書證號和總編號。,題目內(nèi)容,1.使用SQL語句創(chuàng)建圖書管理數(shù)據(jù)庫和數(shù)據(jù)表 2.使用數(shù)據(jù)庫設(shè)計器建立圖書管理數(shù)據(jù)表間關(guān)系和數(shù)據(jù)參照完整性 3.寫出向圖書表中添加一條記錄的SQL語句，總編號為A001，書名為二級Visual FoxPro，作者為李明，出版單位是清華大學，單價是33。,綜合應(yīng)用示例,4寫出查詢姓名、書名、借書日期的的SQL語句 SELECT 讀者.姓名，圖書.書名，借閱.借書日期； FROM 圖書管理!讀者，圖書管理!借閱，圖書管理!圖書； WHERE讀者.借書證號=借閱.借書證號 .And. 圖書.