大學(xué)數(shù)據(jù)庫(kù)知識(shí)點(diǎn)

1、第六章數(shù)據(jù)庫(kù)檢索簡(jiǎn)單查詢SELECT語(yǔ)句的基本語(yǔ)法格式：SELECT查詢項(xiàng)列表INTO新表名FROM數(shù)據(jù)源表列表WHERE條件表達(dá)式ORDER BY排序依據(jù)排序方式GROUP BY分組依據(jù)HAVING條件表達(dá)式基本查詢格式：SELECT查詢項(xiàng)列表FROM數(shù)據(jù)源表說(shuō)明查詢項(xiàng)列表中各選項(xiàng)用逗號(hào)(，)隔開(kāi)；查詢項(xiàng)中可以包含：*、DISTINCT. TOP n、AS別名的使用； 例：在學(xué)生管理數(shù)據(jù)庫(kù)系統(tǒng)(student)中，有學(xué)生信息表(student)、學(xué)生成績(jī)表(stgrade)、課程信息表(course)，具體結(jié)構(gòu)如下顯示，完成該數(shù)據(jù)庫(kù)系統(tǒng)中的查詢。STUDENT (Sno,Sname,Sex,

2、Birth,Addr,Dept)，分別表示學(xué)號(hào)、姓名、性別、出生 日期、地址、專業(yè)；STGRADE(Sno,Cno,Grade,Score)，分別表示學(xué)號(hào)、課程號(hào)、成績(jī)、所得學(xué)分；COURSE(Cno,Cname,Term,Point,Tname)，分別表示課程號(hào)、課程名稱、基本學(xué)分、 任課教師姓名。從student表中查詢學(xué)生的學(xué)號(hào)，姓名，性別，出生日期信息。SELECT sno,sname,sex,birth FROM student從student表中查詢所有學(xué)生的所有信息。SELECT * FROM student(*代表表中的所有字段)從student表中查詢專業(yè)信息SELECT d

3、ept FROM student(結(jié)果中會(huì)出現(xiàn)重復(fù)值，即學(xué)號(hào)有重復(fù))對(duì)比：SELECT DISTINCT dept FROM student (DISTINCT 用于去掉查詢結(jié)果中的 重復(fù)值)從stgrade表中查詢選修了課程的學(xué)生學(xué)號(hào)。(同上小題)SELECT sno FROM stgrade對(duì)比：SELECT DISTINCT sno FROM stgrade從student表中查詢前10個(gè)學(xué)生的學(xué)號(hào)，姓名，性別信息。(或前10%個(gè)學(xué)生的信息)SELECT TOP 10 sno,sname,sex FROM studentSELECT TOP 10 PERCENT sno,sname,se

4、x FROM studentTOP n用于限制在結(jié)果中只顯示前面的n條記錄；TOP n PERCENT用于限制在結(jié)果中 只顯示前面的n%條記錄。從student表中查詢學(xué)生的學(xué)號(hào)，姓名，性別，年齡信息。提示：年齡可通過(guò)birth字段的計(jì)算得到，age=YEAR(GETDATE()-YEAR(birth)SELECT sno,sname,sex,YEAR(GETDATE()-YEAR(birth) FROM studentSELECT sno,sname,sex,YEAR(GETDATE()-YEAR(birth) AS age FROM studentSELECT sno,sname,sex,

5、age=YEAR(GETDATE()-YEAR(birth) FROM student 其中：為結(jié)果集內(nèi)的列指定別名有3中寫(xiě)法，如下：、select列名(表達(dá)式)列別名from數(shù)據(jù)源、select列名(表達(dá)式)AS列別名from數(shù)據(jù)源、select列別名=列名(表達(dá)式)from數(shù)據(jù)源帶條件的查詢：WHERE子句的應(yīng)用格式：SELECT查詢項(xiàng)列表FROM數(shù)據(jù)源表 WHERE條件表達(dá)式說(shuō)明：查詢中希望得到表中滿足特定條件的一些記錄時(shí)，用戶可以在查詢語(yǔ)句中使用WHERE 子句(2)常用的查詢條件及運(yùn)算符如下:查詢條件運(yùn)算符意義例比較=,=(!),),(!=),或NOT+上述運(yùn)算符比較大小成績(jī)50確定

6、范圍NOT BETWEEN AND 判斷值是否在范圍中成績(jī) BETWEEN 60 AND 100確定集合IN(列表)/ NOT IN例表)判斷值是否為列表中的值專業(yè)IN物流，財(cái)會(huì))字符匹配LIKE / NOT LIKE判斷值是否與指定的字符通配格式相符姓名LIKE ，王專業(yè)NOT LIKE 數(shù)據(jù)空值IS NULL / NOT IS NULL判斷值是否為空成績(jī)IS NULL多重條件AND,OR,NOT用于多重條件判斷姓名LIKE ，王 AND專業(yè)IN( 物流，財(cái)會(huì))例：數(shù)據(jù)庫(kù)表同上從student表中查詢所有男生記錄。SELECT * FROM student WHERE sex=男從stude

7、nt表中查詢所有非“計(jì)算機(jī)”專業(yè)的學(xué)生信息。SELECT * FROM student WHERE dept計(jì)算機(jī)從student表中查詢1985年之后(包括1985年)出生的學(xué)生信息。SELECT * FROM student WHERE birth=1985/1/1SELECT * FROM student WHERE YEAR(birth)=1985從student表中查詢年齡在18至20歲之間的學(xué)生學(xué)號(hào)、姓名、性別、專業(yè)信息。SELECT sno,sname,sex,dept FROM studentWHERE YEAR(GETDATE()-YEAR(birth) BETWEEN 18

8、 AND 20從student表中查詢所有姓“王”的學(xué)生學(xué)號(hào)、姓名、性別、專業(yè)信息。SELECT sno,sname,sex,dept FROM student WHERE sname LIKE 王SELECT sno,sname,sex,dept FROM student WHERE sname=K()從student表中查詢所有姓名中的第二個(gè)字不是“小”的學(xué)生信息。SELECT * FROM student WHERE sname NOT LIKE _小從student表中查詢專業(yè)為“數(shù)學(xué)”，“英語(yǔ)”和“計(jì)算機(jī)”專業(yè)的學(xué)生信息。SELECT * FROM student WHERE dep

9、t IN (數(shù)學(xué)，英語(yǔ)，計(jì)算機(jī))或：SELECT * FROM student WHERE dept=數(shù)學(xué)OR dept=英語(yǔ)OR dept=計(jì)算機(jī)從student表中查詢除“數(shù)學(xué)”，“英語(yǔ)”和“計(jì)算機(jī)”以外專業(yè)的學(xué)生信息。SELECT * FROM student WHERE dept NOT IN (數(shù)學(xué)，英語(yǔ)，計(jì)算機(jī))或：SELECT * FROM studentWHERE dept數(shù)學(xué)AND dept英語(yǔ)AND dept計(jì)算機(jī)從stgrade表中查詢課程號(hào)為“C01”，成績(jī)合格的記錄信息。SELECT * FROM stgrade WHERE cno= C01 AND grade=60

10、從stgrade表中查詢所有成績(jī)?yōu)榭盏挠涗?。SELECT * FROM stgrade WHERE grade IS NULLSELECT * FROM stgrade WHERE grade NULL注意：如果要查詢的字符串本身含有%或_，需要使用ESCAPE換碼字符對(duì)通配符進(jìn)行轉(zhuǎn)義。例如：SELECT CNO,CNAMEFROM COURSEWHERE CNAME like VB/_6.0 ESCAPE /對(duì)查詢結(jié)果排序一一ORDER BY子句的應(yīng)用格式：SELECT查詢項(xiàng)列表FROM數(shù)據(jù)源表WHERE條件表達(dá)式ORDER BY排序字段排序方式說(shuō)明：排序方式默認(rèn)為“升序”；其中ASC表示升

11、序排列，DESC表示降序排列若需按多個(gè)字段排序，則多個(gè)字段間用“，”隔開(kāi)，具體可參見(jiàn)例題。舉例：數(shù)據(jù)庫(kù)同上從student表中查詢所有學(xué)生的學(xué)號(hào)、姓名、性別、出生日期和專業(yè)，并將結(jié)果按出 生日期升序顯示(即按年齡從大到小排序)。SELECT sno,sname,sex,birth,dept FROM student ORDER BY birth從stgrade表中查詢選修了課程號(hào)為“C01”課程的學(xué)生的學(xué)號(hào)和成績(jī)，并將結(jié)果按 成績(jī)降序顯示(即按成績(jī)從高到低排序)。SELECT sno,grade FROM stgrade WHERE cno=c01 ORDER BY grade DESC從st

12、udent表中查詢所有學(xué)生的信息，并將結(jié)果按專業(yè)的降序排列，專業(yè)相同的學(xué)生 的學(xué)號(hào)升序排列。SELECT * FROM student ORDER BY dept DESC,sno ASC /* 其中 ASC 可省略 */從stgrade表中查詢所有信息，并將結(jié)果按課程號(hào)的降序排列，課程號(hào)相同的按成績(jī) 由高到低排列(降序)。SELECT * FROM stgrade ORDER BY cno desc,grade DESC四、對(duì)數(shù)據(jù)進(jìn)行統(tǒng)計(jì)一一聚合函數(shù)的應(yīng)用聚合函數(shù)及功能：COUNT(*):統(tǒng)計(jì)記錄的個(gè)數(shù)COUNT(列名)：統(tǒng)計(jì)一列中非空值的個(gè)數(shù)SUM(列名)：統(tǒng)計(jì)一列值的總和(該列必須是數(shù)值

13、型)AVG(列名)：統(tǒng)計(jì)一列值的平均值(該列必須是數(shù)值型)MAX(列名)：統(tǒng)計(jì)一列值的最大值MIN(列名)：統(tǒng)計(jì)一列值的最小值說(shuō)明：允許使用(DISTINCT|ALL列名)，其中DISTINCT表示去掉指定列中的重復(fù)值再進(jìn) 行統(tǒng)計(jì)；ALL表示不取消重復(fù)值，默認(rèn)是ALL。例：從student表中查詢學(xué)生的總?cè)藬?shù)。SELECT COUNT(*) FROM student從stgrade表中查詢選修了課程號(hào)為“C01”課程的學(xué)生人數(shù)。SELECT COUNT(*) FROM stgrade WHERE cno=c01從stgrade表中查詢成績(jī)不為空的記錄個(gè)數(shù)。SELECT COUNT(grade)

14、 FROM stgradeSELECT COUNT(*) FROM stgrade WHERE grade IS NOT null從student表中查詢學(xué)生專業(yè)個(gè)數(shù)。SELECT COUNT(DISTINCT DEPT) FROM student從stgrade表中查詢成績(jī)的平均值、總分、最高分及最低分。SELECT AVG(grade),SUM(grade),MAX(grade),MIN(grade) FROM stgrade五、對(duì)結(jié)果進(jìn)行分組一一GROUP BY子句的應(yīng)用格式：SELECT查詢項(xiàng)列表FROM數(shù)據(jù)源表列表WHERE條件表達(dá)式GROUP BY分組依據(jù)HAVING條件表達(dá)式OR

15、DER BY排序依據(jù)排序方式說(shuō)明：(具體參見(jiàn)例題)SELECT子句中的列名必須是GROUP BY子句中已有的列名或是包含在聚合函數(shù)中的 列名；HAVING子句必須用于GROUP BY子句的后面；HAVING子句與WHERE子句的區(qū)別:WHERE子句用于對(duì)表中記錄進(jìn)行條件篩選；HAVING 子句用于對(duì)分組統(tǒng)計(jì)后的結(jié)果進(jìn)行條件篩選；如果查詢語(yǔ)句中既有ORDER BY子句又有GROUP BY子句，則ORDER BY子句用于GROUP BY子句之后。例：從student表中查詢每個(gè)專業(yè)的學(xué)生人數(shù)(即按dept值分組，統(tǒng)計(jì)記錄個(gè)數(shù))。SELECT dept,COUNT(*) FROM student G

16、ROUP BY dept從stgrade表中查詢每門(mén)課程的成績(jī)平均值，并將結(jié)果按成績(jī)平均值從高到低排列。SELECT cno,AVG(grade) FROM stgrade GROUP BY cno ORDER BY AVG(grade) DESCSELECT cno,AVG(grade) as 平均成績(jī) FROM stgradeGROUP BY cno ORDER BY 平均成績(jī) DESC從student表中查詢專業(yè)為“Computer”的學(xué)生人數(shù)。(即從student表中統(tǒng)計(jì)各 個(gè)專業(yè)的學(xué)生人數(shù)，并篩選出專業(yè)為“Computer”的結(jié)果。)SELECT COUNT(*) FROM stud

17、ent WHERE dept=Computer GROUP BY deptSELECT COUNT(*) FROM student GROUP BY dept HAVING dept=ComputerSELECT COUNT(*) FROM student HAVING dept=Computer從student表中查詢學(xué)生人數(shù)超過(guò)10人的專業(yè)及對(duì)應(yīng)的學(xué)生人數(shù)。SELECT dept,COUNT(*)FROMstudentGROUPBY dept HAVING COUNT(*)=10SELECT dept,COUNT(*)FROMstudentGROUPBY dept WHERE COUNT(

18、*)=10SELECT dept,COUNT(*)FROMstudentWHERECOUNT(*)=10 GROUP BY dept從stgrade表中查詢每個(gè)學(xué)生成績(jī)合格的課程數(shù)目。SELECT sno,count(*) FROM stgrade WHERE grade=60 GROUP BY sno從stgrade表中查詢每個(gè)學(xué)生成績(jī)合格的課程數(shù)目，并從結(jié)果中篩選出合格數(shù)目超 過(guò)3(包括3)人的學(xué)號(hào)及課程數(shù)。SELECT sno,count(*) FROM stgrade WHERE grade=60 GROUP BY sno HAVING count(*)=3其他子句 INTO子句、UN

19、ION、COMPUTE的應(yīng)用格式：SELECT查詢項(xiàng)列表INTO新表名FROM數(shù)據(jù)源表列表 SELECT查詢項(xiàng)列表FROM數(shù)據(jù)源表列表 UNION SELECT查詢項(xiàng)列表FROM數(shù)據(jù)源表列表 SELECT查詢項(xiàng)列表 FROM數(shù)據(jù)源表列表COMPUTE集合函數(shù)BY列名說(shuō)明：INTO子句用于將查詢結(jié)果生成新表(或臨時(shí)表：表名前加#)UNION用于將查詢結(jié)果合并顯示；要求各結(jié)果集中的列數(shù)必須相同；且對(duì)應(yīng)的數(shù)據(jù)類 型也必須相同。COMPUTE自子句用于對(duì)查詢結(jié)果的所有記錄進(jìn)行匯總統(tǒng)計(jì)，并顯示所有參加匯總的詳 細(xì)信息；“BY列名”是指按列名字段進(jìn)行分組計(jì)算，并顯示被統(tǒng)計(jì)記錄的詳細(xì)信息； BY選項(xiàng)必須與O

20、RDER BY子句一起使用，即必須按某一字段進(jìn)行排序后才能分組。例：SELECT sno,sname,sex INTO xs FROM student WHERE dept=ComputerSELECT sno,sname,sex FROM student WHERE dept=ComputerUNIONSELECT sno,sname,sex FROM student WHERE dept=English對(duì)比： SELECT sno,sname,sex FROM student WHERE dept= Computer AND dept= EnglishSELECT * FROM xsUNI

21、ONSELECT sno,sname,sex FROM student WHERE dept= EnglishSELECT * FROM stgrade COMPUTE sum(grade),avg(grade),count(grade)SELECT * FROM stgrade ORDER BY sno COMPUTE sum(grade),avg(grade),count(grade)BY sno高級(jí)查詢一.連接查詢概念及分類若一個(gè)查詢同時(shí)涉及多個(gè)表，則稱之為連接查詢。包括非等值查詢、自然連接、自身查詢、外連接等應(yīng)用范圍：查詢項(xiàng)來(lái)自于多個(gè)表、或查詢項(xiàng)及查詢條件來(lái)自于多個(gè)表。交叉連接=廣義笛卡爾積(不帶連接條件的多表連接，一般無(wú)實(shí)際應(yīng)用意義)語(yǔ)法格式：SELECT查詢項(xiàng)列表FROM表1,表2SELECT 查詢項(xiàng)列表 FROM 表 1 CROSS JOIN 表 2（2）實(shí)例：（略）等值與非等值連接（帶連接條件的多表連接（1）語(yǔ)法格式：SELECT查詢項(xiàng)列表FROM表1 連接方式表2 ON連接條件SELECT查詢項(xiàng)列表FROM表1,表2 WHERE連接條件 （僅用于內(nèi)連接）（2）說(shuō)明：連接條件的一般格式：表1.列名 比較運(yùn)算符 表2.列名其中的比較運(yùn)算符有：=,=,=,=,!=（僅用于單值子查詢）IN、比較運(yùn)算符結(jié)合A