數(shù)據(jù)庫(kù)原理及應(yīng)用(第四章-SQL示例)

1、查詢指定列查詢指定列例1 查詢?nèi)w學(xué)生的學(xué)號(hào)與姓名。SELECT Sno,SnameFROM Student； 例2 查詢?nèi)w學(xué)生的姓名、學(xué)號(hào)、所在系。SELECT Sname,Sno,SdeptFROM Student;查詢?nèi)苛胁樵內(nèi)苛欣? 查詢?nèi)w學(xué)生的詳細(xì)記錄。SELECT Sno,Sname,Ssex,Sage,Sdept FROM Student; 或SELECT *FROM Student; 查詢經(jīng)過(guò)計(jì)算的值查詢經(jīng)過(guò)計(jì)算的值 SELECT子句的為表達(dá)式n算術(shù)表達(dá)式n字符串常量n函數(shù)n列別名n等 查詢經(jīng)過(guò)計(jì)算的值查詢經(jīng)過(guò)計(jì)算的值例4 *查全體學(xué)生的姓名及其出生年份。select

2、Sname,2009-Sage from student; 輸出結(jié)果： Sname 2009-Sage - - 李勇 1976 劉晨 1977 王名 1978 張立 1978 查詢經(jīng)過(guò)計(jì)算的值查詢經(jīng)過(guò)計(jì)算的值例5 *查詢?nèi)w學(xué)生的姓名、出生年份和所有系，要求用小寫(xiě)字母表示所有系名。SELECT Sname,Year of Birth: ,2009-Sage,LOWER(Sdept)FROM Student; 例題（續(xù)）例題（續(xù)）輸出結(jié)果： Sname Year of Birth: 2009-Sage LOWER(Sdept) - - - - 李勇 Year of Birth: 1976 cs

3、劉晨 Year of Birth: 1977 is 王名 Year of Birth: 1978 ma 張立 Year of Birth: 1977 is 選擇表中的若干元組選擇表中的若干元組 n消除取值重復(fù)的行n查詢滿足條件的元組 1. 消除取值重復(fù)的行消除取值重復(fù)的行n在SELECT子句中使用DISTINCT短語(yǔ)假設(shè)SC表中有下列數(shù)據(jù) Sno Cno Grade - - - 95001 1 92 95001 2 85 95001 3 88 95002 2 90 95002 3 80ALL 與與 DISTINCT 例6 查詢選修了課程的學(xué)生學(xué)號(hào)。(1) SELECT Sno FROM SC;

4、 結(jié)果： Sno - 95001 95001 95001 95002 95002 例題（續(xù)）例題（續(xù)）(2) SELECT DISTINCT Sno FROM SC; 結(jié)果： Sno - 95001 95002 例題（續(xù)）例題（續(xù)）n注意 DISTINCT短語(yǔ)的作用范圍是所有目標(biāo)列例：查詢選修課程的各種成績(jī)錯(cuò)誤的寫(xiě)法SELECT DISTINCT Cno,DISTINCT GradeFROM SC;正確的寫(xiě)法 SELECT DISTINCT Cno,Grade FROM SC; 查詢滿足條件的元組查詢滿足條件的元組WHERE子句常用的查詢條件子句常用的查詢條件(1) 比較大小比較大小在WHER

5、E子句的中使用比較運(yùn)算符n=，=，=，!= 或 ，!，!， n邏輯運(yùn)算符NOT + 比較運(yùn)算符例7 查詢所有年齡在20歲以下的學(xué)生姓名及其年齡。 SELECT Sname,Sage FROM Student WHERE Sage = 20; (2) 確定范圍確定范圍n使用謂詞 BETWEEN AND NOT BETWEEN AND 例8 查詢年齡在2023歲（包括20歲和23歲）之間的學(xué)生的姓名、系別和年齡。 SELECT Sname,Sdept,SageFROM StudentWHERE Sage BETWEEN 20 AND 23; 例題（續(xù)）例題（續(xù)）例9 查詢年齡不在2023歲之間（包

6、括20歲和23歲）的學(xué)生姓名、系別和年齡。SELECT Sname,Sdept,SageFROM StudentWHERE Sage NOT BETWEEN 20 AND 23; (3) 確定集合確定集合使用謂詞 IN , NOT IN ：用逗號(hào)分隔的一組取值例10查詢信息系（IS）、數(shù)學(xué)系（MA）和計(jì) 算機(jī)科學(xué)系（CS）學(xué)生的姓名和性別。SELECT Sname,SsexFROM StudentWHERE Sdept IN ( IS ,MA ,CS );(3) 確定集合確定集合例11查詢既不是信息系、數(shù)學(xué)系，也不是計(jì)算 機(jī)科學(xué)系的學(xué)生的姓名和性別。SELECT Sname,SsexFROM

7、Student WHERE Sdept NOT IN ( IS,MA,CS );(4) 字符串匹配字符串匹配n NOT LIKE ESCAPE ：指定匹配模板：指定匹配模板 匹配模板：固定字符串或含匹配模板：固定字符串或含通配符通配符的字符串的字符串 當(dāng)匹配模板為固定字符串時(shí)，當(dāng)匹配模板為固定字符串時(shí)， 可以用可以用 = 運(yùn)算符取代運(yùn)算符取代 LIKE 謂詞謂詞 用用 != 或或 運(yùn)算符取代運(yùn)算符取代 NOT LIKE 謂詞謂詞通配符通配符w% (百分號(hào)百分號(hào)) 代表任意長(zhǎng)度（長(zhǎng)度可以為代表任意長(zhǎng)度（長(zhǎng)度可以為0）的字符串）的字符串n例：a%b表示以a開(kāi)頭，以b結(jié)尾的任意長(zhǎng)度的字符串。如acb

8、，addgb，ab 等都滿足該匹配串_ (下橫線下橫線) 代表任意單個(gè)字符代表任意單個(gè)字符n例：a_b表示以a開(kāi)頭，以b結(jié)尾的長(zhǎng)度為3的任意字符串。如acb，afb等都滿足該匹配串ESCAPE 短語(yǔ)：短語(yǔ)：n*當(dāng)用戶要查詢的字符串本身就含有 % 或 _ 時(shí)，要使用ESCAPE 短語(yǔ)對(duì)通配符進(jìn)行轉(zhuǎn)義。例題例題1) 匹配模板為固定字符串 例12 查詢學(xué)號(hào)為95001的學(xué)生的詳細(xì)情況。 SELECT * FROM Student WHERE Sno LIKE 95001;等價(jià)于： SELECT * FROM Student WHERE Sno = 95001;例題（續(xù)）例題（續(xù)）2) 匹配模板為含通

9、配符的字符串例13 查詢所有姓劉學(xué)生的姓名、學(xué)號(hào)和性別。 SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE 劉%;SELECT *FROM 信息表WHERE 姓名 like %紅%;SELECT * from 信息表 where 籍貫 like %山%;SELECT * from 信息表 where 民族 not like 漢族;例題（續(xù)）例題（續(xù)）匹配模板為含通配符的字符串（續(xù)）例14 查詢姓歐陽(yáng)且全名為三個(gè)漢字的學(xué)生的姓名。 SELECT Sname FROM Student WHERE Sname LIKE 歐陽(yáng)_ ; SELECT

10、* FROM 信息表 WHERE 姓名 LIKE _陽(yáng)%;例題（續(xù)）例題（續(xù)）匹配模板為含通配符的字符串（續(xù)）例15 查詢名字中第2個(gè)字為陽(yáng)字的學(xué)生的姓名和學(xué)號(hào)。 SELECT Sname,Sno FROM Student WHERE Sname LIKE _陽(yáng)%;例題（續(xù)）例題（續(xù)）匹配模板為含通配符的字符串（續(xù)）例16 查詢所有不姓劉的學(xué)生姓名。 SELECT Sname,Sno,Ssex FROM Student WHERE Sname NOT LIKE 劉%;例題（續(xù)）例題（續(xù)）3) 使用換碼字符將通配符轉(zhuǎn)義為普通字符 例17 * 查詢DB_Design課程的課程號(hào)和學(xué)分。 SELEC

11、T Cno,Ccredit FROM Course WHERE Cname LIKE DB_Design ESCAPE ;例題（續(xù)）例題（續(xù)）使用換碼字符將通配符轉(zhuǎn)義為普通字符(續(xù))例18 * 查詢以DB_開(kāi)頭，且倒數(shù)第3個(gè)字符為 i的課程的詳細(xì)情況。 SELECT *FROM Course WHERE Cname LIKE DB_%i_ _ ESCAPE ;(5) 涉及空值的查詢涉及空值的查詢n 使用謂詞 IS NULL 或 IS NOT NULLn “IS NULL” 不能用 “= NULL” 代替例19 某些學(xué)生選修課程后沒(méi)有參加考試，所以有選課記錄，但沒(méi)有考試成績(jī)。查詢?nèi)鄙俪煽?jī)的學(xué)生的

12、學(xué)號(hào)和相應(yīng)的課程號(hào)。 SELECT Sno,Cno FROM SC WHERE Grade IS NULL;例題例題(續(xù)續(xù)) 例20 查所有有成績(jī)的學(xué)生學(xué)號(hào)和課程號(hào)。 SELECT Sno,Cno FROM SC WHERE Grade IS NOT NULL;(6) 多重條件查詢多重條件查詢用邏輯運(yùn)算符AND和 OR來(lái)聯(lián)結(jié)多個(gè)查詢條件n AND的優(yōu)先級(jí)高于ORn 可以用括號(hào)改變優(yōu)先級(jí)可用來(lái)實(shí)現(xiàn)多種其他謂詞n NOT INn NOT BETWEEN AND 例題例題例21 查詢計(jì)算機(jī)系年齡在20歲以下的學(xué)生姓名。 SELECT Sname FROM Student WHERE Sdept= C

13、S AND Sage=20 AND Sage=23;三、對(duì)查詢結(jié)果排序三、對(duì)查詢結(jié)果排序 使用ORDER BY子句n 可以按一個(gè)或多個(gè)屬性列排序n 升序：ASC；降序：DESC；缺省值為升序缺省值為升序當(dāng)排序列含空值時(shí)nASC：排序列為空值的元組最后顯示nDESC：排序列為空值的元組最先顯示 對(duì)查詢結(jié)果排序（續(xù)）對(duì)查詢結(jié)果排序（續(xù)） 例24 查詢選修了3號(hào)課程的學(xué)生的學(xué)號(hào)及其成績(jī)，查詢結(jié)果按分?jǐn)?shù)降序排列。 SELECT Sno,Grade FROM SC WHERE Cno= 3 ORDER BY Grade DESC; 查詢結(jié)果查詢結(jié)果 Sno Grade - - 95010 95024 9

14、5007 92 95003 82 95010 82 95009 75 95014 61 95002 55對(duì)查詢結(jié)果排序（續(xù)）對(duì)查詢結(jié)果排序（續(xù)） 例25 查詢?nèi)w學(xué)生情況，查詢結(jié)果按所在系的系號(hào)升序排列，同一系中的學(xué)生按年齡降序排列。 SELECT * FROM Student ORDER BY Sdept,Sage DESC;四、使用集函數(shù)四、使用集函數(shù) 5類主要集函數(shù)n計(jì)數(shù)COUNT（DISTINCT|ALL *）COUNT（DISTINCT|ALL ）n計(jì)算總和SUM（DISTINCT|ALL ）n 計(jì)算平均值A(chǔ)VG（DISTINCT|ALL ）使用集函數(shù)（續(xù)）使用集函數(shù)（續(xù)） 求最大值

15、MAX（DISTINCT|ALL ） 求最小值MIN（DISTINCT|ALL ）DISTINCT短語(yǔ)：在計(jì)算時(shí)要取消指定列中的重復(fù)值A(chǔ)LL短語(yǔ)：不取消重復(fù)值A(chǔ)LL為缺省值使用集函數(shù)使用集函數(shù) （續(xù)）（續(xù)）例26 查詢學(xué)生總?cè)藬?shù)。 SELECT COUNT(*) FROM Student; 例27 查詢選修了課程的學(xué)生人數(shù)。 SELECT COUNT(DISTINCT Sno) FROM SC;注：用DISTINCT以避免重復(fù)計(jì)算學(xué)生人數(shù)使用集函數(shù)使用集函數(shù) （續(xù)）（續(xù)）例28 計(jì)算1號(hào)課程的學(xué)生平均成績(jī)。 SELECT AVG(Grade) FROM SC WHERE Cno= 1 ; 例2

16、9 查詢選修1號(hào)課程的學(xué)生最高分?jǐn)?shù)。 SELECT MAX(Grade) FROM SC WHERE Cno= 1 ; 五、對(duì)查詢結(jié)果分組五、對(duì)查詢結(jié)果分組 使用GROUP BY子句分組 細(xì)化集函數(shù)的作用對(duì)象n 未對(duì)查詢結(jié)果分組，集函數(shù)將作用于整個(gè)查詢結(jié)果n 對(duì)查詢結(jié)果分組后，集函數(shù)將分別作用于每個(gè)組 使用使用GROUP BY子句分組子句分組例30 求各個(gè)課程號(hào)及相應(yīng)的選課人數(shù)。 SELECT Cno,COUNT(Sno) FROM SC GROUP BY Cno; 結(jié)果 Cno COUNT(Sno) 1 22 2 34 3 44 4 33 5 48對(duì)查詢結(jié)果分組對(duì)查詢結(jié)果分組 （續(xù)）（續(xù)）n

17、GROUP BY子句的作用對(duì)象是查詢的中間結(jié)果表n分組方法：按指定的一列或多列值分組，值相等的為一組n使用GROUP BY子句后，SELECT子句的列名列表中只能出現(xiàn)分組屬性和集函數(shù) 使用使用HAVING短語(yǔ)篩選最終輸出結(jié)果短語(yǔ)篩選最終輸出結(jié)果例31 查詢選修了3門(mén)以上課程的學(xué)生學(xué)號(hào)。 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) 3; 思考題思考題:查詢選修了查詢選修了3門(mén)以上（包括門(mén)以上（包括3門(mén)）課程及平均分在門(mén)）課程及平均分在80分以上的學(xué)生學(xué)號(hào)分以上的學(xué)生學(xué)號(hào)及平均成績(jī)。及平均成績(jī)。SELECT Sno,avg(Grade) FROM

18、 SC GROUP BY Sno HAVING COUNT(*) =3 and avg(Grade)80; 例題例題例32 *查詢有3門(mén)以上課程是90分以上的 學(xué)生的學(xué)號(hào)及（90分以上的）課程數(shù) SELECT Sno, COUNT(*) FROM SC WHERE Grade=90 GROUP BY Sno HAVING COUNT(*)=3; 使用使用HAVING短語(yǔ)篩選最終輸出結(jié)果短語(yǔ)篩選最終輸出結(jié)果n只有滿足HAVING短語(yǔ)指定條件的組才輸出nHAVING短語(yǔ)與WHERE子句的區(qū)別：作用對(duì)象不同nWHERE子句作用于基表或視圖，從中選擇滿足條件的元組。nHAVING短語(yǔ)作用于組，從中選擇

19、滿足條件的組。 插入數(shù)據(jù)插入數(shù)據(jù)n語(yǔ)句格式INSERTINTO (，)VALUES ( ， )n功能 將新元組插入指定表中。插入數(shù)據(jù)插入數(shù)據(jù)例1 將一個(gè)新學(xué)生記錄（學(xué)號(hào)：95020；姓名：陳冬；性別：男；所在系：IS；年齡：18歲）插入到Student表中。 INSERT INTO Student(Sno,Sname,Ssex,Sdept,Sage) VALUES (95020,陳冬,男,IS,18);注意注意:若未列出屬性名若未列出屬性名,則數(shù)據(jù)順序必須與表中屬性順序一則數(shù)據(jù)順序必須與表中屬性順序一致致nINSERT INTO Studentn VALUES (95020,陳冬,男,18,IS)修改數(shù)據(jù)修改數(shù)據(jù)n語(yǔ)句格式 UPDATE SET =，= WHERE ；n功能修改指定表中滿足WHERE子句條件的元組1. 修改某一個(gè)元組的值修改某一個(gè)元組的值例1 將學(xué)生