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

1、查詢指定列查詢指定列例1 查詢全體學生的學號與姓名。SELECT Sno,SnameFROM Student； 例2 查詢全體學生的姓名、學號、所在系。SELECT Sname,Sno,SdeptFROM Student;查詢全部列查詢全部列例3 查詢全體學生的詳細記錄。SELECT Sno,Sname,Ssex,Sage,Sdept FROM Student; 或SELECT *FROM Student; 查詢經(jīng)過計算的值查詢經(jīng)過計算的值 SELECT子句的為表達式n算術表達式n字符串常量n函數(shù)n列別名n等 查詢經(jīng)過計算的值查詢經(jīng)過計算的值例4 *查全體學生的姓名及其出生年份。select

2、Sname,2009-Sage from student; 輸出結果： Sname 2009-Sage - - 李勇 1976 劉晨 1977 王名 1978 張立 1978 查詢經(jīng)過計算的值查詢經(jīng)過計算的值例5 *查詢全體學生的姓名、出生年份和所有系，要求用小寫字母表示所有系名。SELECT Sname,Year of Birth: ,2009-Sage,LOWER(Sdept)FROM Student; 例題（續(xù)）例題（續(xù)）輸出結果： 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消除取值重復的行n查詢滿足條件的元組 1. 消除取值重復的行消除取值重復的行n在SELECT子句中使用DISTINCT短語假設SC表中有下列數(shù)據(jù) Sno Cno Grade - - - 95001 1 92 95001 2 85 95001 3 88 95002 2 90 95002 3 80ALL 與與 DISTINCT 例6 查詢選修了課程的學生學號。(1) SELECT Sno FROM SC;

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

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

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

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

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

9、配符的字符串例13 查詢所有姓劉學生的姓名、學號和性別。 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 查詢姓歐陽且全名為三個漢字的學生的姓名。 SELECT Sname FROM Student WHERE Sname LIKE 歐陽_ ; SELECT

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

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

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

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

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

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

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

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

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

19、滿足條件的組。 插入數(shù)據(jù)插入數(shù)據(jù)n語句格式INSERTINTO (，)VALUES ( ， )n功能 將新元組插入指定表中。插入數(shù)據(jù)插入數(shù)據(jù)例1 將一個新學生記錄（學號：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語句格式 UPDATE SET =，= WHERE ；n功能修改指定表中滿足WHERE子句條件的元組1. 修改某一個元組的值修改某一個元組的值例1 將學生