數(shù)據(jù)庫系統(tǒng)原理及應用教程 第4版 教學課件 作者 劉瑞新第6章-2

6.4.1T-SQL數(shù)據(jù)查詢語句6.4.

2簡單查詢操作6.4.3連接查詢操作6.4.4嵌套查詢操作6.4.5使用函數(shù)和分組查詢操作6.4.6使用計算和匯總查詢操作6.4數(shù)據(jù)查詢操作6.4.

1T-SQL數(shù)據(jù)查詢語句1.數(shù)據(jù)檢索語句的語句格式

SELECT〈查詢列〉

[INTO〈新表名〉]

[FROM〈數(shù)據(jù)源〉]

[WHERE〈元組條件表達式〉]

[GROUPBY<分組項>][HAVING<組選擇條件>]

[ORDERBY<排序條件>]

[COMPUTE〈統(tǒng)計列組〉][BY〈表達式〉]（1）SELECT子句SELECT[ALL|DISTINCT][TOP<數(shù)值>[PERCENT]]<查詢列組>

查詢列為：〈查詢列〉::=*|〈表或視圖〉.*|〈列名或表達式〉[AS]〈列別名〉|〈列別名〉=〈表達式〉所有行表或視圖的全部列全部列返回結果中的百分之〈數(shù)值〉行記錄結果中的前〈數(shù)值〉行代替出現(xiàn)在結果集中的列名或表達式結果中僅有唯一行(2)INTO子句和FROM子句

INTO子句將查詢結果加到創(chuàng)建表中,臨時表的表名前加“#”字。

FROM語法格式為：FROM〈數(shù)據(jù)源組〉數(shù)據(jù)源的語法為：

〈數(shù)據(jù)源〉::=〈表名〉[[AS]〈表別名〉]|〈視圖名〉[[AS]〈視圖別名〉]|〈嵌套的SELECT語句〉[[AS]〈別名〉]|〈連接表〉連接表的語法為：〈連接表〉::=<數(shù)據(jù)源><連接類型><數(shù)據(jù)源>ON<連接條件>

|<數(shù)據(jù)源>CROSSJOIN<數(shù)據(jù)源>|<連接表><連接類型>::=[INNER|{{LEFT|RIGHT|FULL}[OUTER]}]JOIN

笛卡兒積運算內(nèi)連接左外連接全外連接右外連接(3)WHERE子句：WHERE<查詢條件>|<舊格式連接條件>

(4)GROUPBY子句：GROUPBY[ALL]〈分組表達式組〉

(5)HAVING子句：HAVING〈分組或匯總篩選條件〉

(6)ORDERBY：ORDERBY〈排序項〉[ASC|DESC][，…n]

(7)COMPUTE子句：在結果集中后將匯總值放入摘要列，COMPUTE與BY配合，起到換行控制和分段小計的作用。

COMPUTE〈統(tǒng)計函數(shù)組〉[BY〈分組項〉]

其中：BY〈分組項〉表示在結果集中產(chǎn)生換行控制及分段小計。COMPUTEBY必須和ORDERBY配合使用。

2.SELECT語句的操作符(1)算術操作符

+（加號）、－（減號）、*（乘號）和/（除號）。(2)比較操作符

=（等于）、>（大于）、<（小于）、<=（小于等于）、>=（大于等于）、!=（不等于）、<>（小于大于）、!>（不大于）和!<（不小于），共9種操作符。

(3)邏輯操作符

語義操作符使用格式或示例在[不在]其中[NOT]IN〈字段〉IN（〈數(shù)據(jù)表|子查詢〉）任何一個ANY〈字段〉〈比較符〉ANY（數(shù)據(jù)表|子查詢）全部(每個)ALL〈字段〉〈比較符〉ALL（數(shù)據(jù)表|子查詢）[不]存在EXISTSEXISTS(〈子查詢〉)在[不在]范圍BETWEEN…AND…〈字段〉BETWEEN小值AND大值是[不是]空值IS[NOT]NULL〈字段〉IS[NOT]NULL模式比較[NOT]LIKE〈字段〉LIKE〈字符常數(shù)〉其中，“_”單字符通配符和“%”多字符通配符與運算AND〈條件1〉AND〈條件2〉或運算OR〈條件1〉OR〈條件2〉非運算NOTNOT〈條件〉(4)組合查詢操作符和其他SQL操作符UNION：并查詢，并在結果集中去掉重復行。格式為：

〈查詢1〉UNION〈查詢2〉2)*：取全部字段。格式為：*或〈表名〉.*3)ALL：全部。保留重復值（有統(tǒng)計函數(shù)時要求計算重復值）。格式為：ALL〈字段〉或ALL〈字段組〉6.4.

2簡單查詢操作學生課程庫：學生(學號，姓名，年齡，所在系)；課程(課程號，課程名，先行課)；選課(學號，課程號，成績).簡單查詢：查詢過程中只涉及到一個表的查詢語句。【例】求數(shù)學系學生的學號和姓名。

SELECT學號，姓名

FROM學生

WHERE所在系=‘數(shù)學系’【例】求選修了課程的學生學號。

SELECTDISTINCT學號

FROM選課例子【例】求選修C1課程的學生學號和成績，并要求對查詢結果按成績的降序排列，如果成績相同則按學號的升序排列。

SELECT學號，成績

FROM選課

WHERE課程號='C1'

ORDERBY成績DESC，學號ASC【例】求選修課程C1且成績在80～90之間的學生學號和成績，并將成績乘以系數(shù)0.8輸出。

SELECT學號，成績*0.8FROM選課

WHERE課程號='C1'AND成績BETWEEN80AND90學生課程庫：學生(學號，姓名，年齡，所在系)；課程(課程號，課程名，先行課)；選課(學號，課程號，成績).例子【例】求數(shù)學系或計算機系姓張的學生的信息。

SELECT*

FROM學生

WHERE所在系IN(‘數(shù)學系’，‘計算機系’)AND姓名LIKE‘張%’【例】求缺少了成績的學生的學號和課程號。

SELECT學號，課程號

FROM選課

WHERE成績ISNULL

通配符

說明_表示任意單個字符%表示任意長度的字符串[]與特定范圍中的任意單字符匹配。[^]與特定范圍之外的任意單字符匹配。查詢實例設圖書借閱數(shù)據(jù)庫中包括：

圖書(書號，類別，出版社，作者，書名，定價)；

讀者(書證號，姓名，單位，性別，電話)；

借閱(書號，讀者書證號，借閱日期).

【例】將計算機類的書存入永久的計算機圖書表，將借書日期在99年以前的借閱記錄存入臨時的超期借閱表。

SELECT*INTO計算機圖書

FROM圖書

WHERE類別=’計算機’

GO

SELECT*INTO#超期借閱

FROM借閱

WHERE借閱日期<’1999-01-01’

GO例設學生和選課表的數(shù)據(jù)如下，它們的交叉連接、等值連接和自然連接的結果如下。

學號姓名年齡所在系

學號課程名成績98001張三20計算機系98001數(shù)據(jù)庫6298005李四21數(shù)學系98001數(shù)據(jù)結構73

98005微積分80學生.學號姓名年齡所在系選課.學號課名成績980019800198001980059800598005張三張三張三李四李四李四202020212121計算機系計算機系計算機系數(shù)學系數(shù)學系數(shù)學系980019800198005980019800198005數(shù)據(jù)庫數(shù)據(jù)結構微積分數(shù)據(jù)庫數(shù)據(jù)結構微積分627380627380學生

選課

交叉連接6.4.3連接查詢操作學號姓名年齡所在系

學號課程名成績98001張三20計算機系98001數(shù)據(jù)庫6298005李四21數(shù)學系98001數(shù)據(jù)結構73

98005微積分80學生

選課

學生.學號姓名年齡所在系選課.學號課名成績980019800198005張三張三李四202021計算機系計算機系數(shù)學系980019800198005數(shù)據(jù)庫數(shù)據(jù)結構微積分627380

等值連接（學生.學號=選課.學號）學生.學號姓名年齡所在系選課.學號課名成績980019800198005張三張三李四202021計算機系計算機系數(shù)學系980019800198005數(shù)據(jù)庫數(shù)據(jù)結構微積分627380學生.學號姓名年齡所在系課名成績980019800198005張三張三李四202021計算機系計算機系數(shù)學系數(shù)據(jù)庫數(shù)據(jù)結構微積分627380

等值連接（學生.學號=選課.學號）自然連接1.連接條件通過WHERE子句表達：連接條件和元組選擇條件之間用AND操作符銜接。(標準寫法)

等值連接和非等值連接[<表名1>.]<列名1><比較運算符>[<表名2>.]<列名2>比較運算符：=、>、<、>=、<=和!=；列名稱為連接字段?！纠坎樵兠總€學生的情況以及他(她)所選修的課程。

SELECT學生.*，選課.*

FROM學生，選課

WHERE學生.學號=選課.學號例子【例】求學生的學號、姓名、選修的課程名及成績。

SELECT學生.學號，姓名，課程名，成績FROM學生，課程，選課WHERE學生.學號=選課.學號AND課程.課程號=選課.課程號【例】求選修C1課程且成績?yōu)?0分以上的學生學號、姓名及成績。SELECT學生.學號，姓名，成績FROM學生，選課WHERE學生.學號=選課.學號AND課程號=‘C1’AND成績>90學生課程庫：學生(學號，姓名，年齡，所在系)；課程(課程號，課程名，先行課)；選課(學號，課程號，成績).2.用JOIN表示連接條件（T-SQL寫法）內(nèi)連接：列出與連接條件匹配的數(shù)據(jù)行左外部連接：在結果中保留連接表達式左表中的非匹配記錄；右外部連接：在結果中保留連接表達式右表中的非匹配記錄。全外連接：在結果中保留連接表達式兩邊表中的非匹配記錄職工號姓名性別年齡所在部門

部門號部門名稱電話1010李勇男201111生產(chǎn)科5661011劉晨女19

12計劃科5781012王敏女221213一車間4671014張立男211314科研所

職工表部門表

內(nèi)連接的結果集

內(nèi)連接的結果集職工號姓名性別年齡所在部門

部門號部門名稱電話1010李勇男201111生產(chǎn)科5661011劉晨女19

12計劃科5781012王敏女221213一車間4671014張立男211314科研所

職工表部門表

職工號姓名性別年齡所在部門部門名稱電話1010李勇男2011生產(chǎn)科5661012王敏女2212計劃科5781014張立男2113一車間467左外部連接的結果集

左外部連接的結果集職工號姓名性別年齡所在部門

部門號部門名稱電話1010李勇男201111生產(chǎn)科5661011劉晨女19

12計劃科5781012王敏女221213一車間4671014張立男211314科研所

職工表部門表

職工號姓名性別年齡所在部門部門名稱電話1010李勇男2011生產(chǎn)科5661011劉晨女19

1012王敏女2212計劃科5781014張立男2113一車間467右外部連接的結果集

職工號姓名性別年齡所在部門部門名稱電話1010李勇男2011生產(chǎn)科5661012王敏女2212

計劃科

5781014張立男2113一車間46714科研所右外部連接的結果集職工號姓名性別年齡所在部門

部門號部門名稱電話1010李勇男201111生產(chǎn)科5661011劉晨女19

12計劃科5781012王敏女221213一車間4671014張立男211314科研所

職工表部門表

內(nèi)連接：

SELECT職工.*，部門名稱，電話

FROM職工INNERJOIN

部門ON職工.所在部門=部門.部門號左外部連接：

SELECT職工.*，部門名稱，電話

FROM職工LEFTOUTERJOIN部門ON職工.所在部門=部門.部門號右外部連接：

SELECT職工.*，部門名稱，電話

FROM職工RIGHTOUTERJOIN部門ON職工.所在部門=部門.部門號【例】用SQL表達職工和部門之間的內(nèi)連接、左外部連接和右外部連接的語句【例】求學生的學號、姓名、選修的課程名及成績。

SELECT學生.學號，姓名，課程名，成績FROM學生INNERJOIN選課on學生.學號=選課.學號INNERJOIN課程on課程.課程號=選課.課程號【例】求選修C1課程且成績?yōu)?0分以上的學生學號、姓名及成績。SELECT學生.學號，姓名，成績FROM學生INNERJOIN選課on學生.學號=選課.學號WHERE課程號=‘C1’AND成績>90【例】查詢借書者的書證號，姓名，單位，所借書號，書名和借閱日期。

SELECT讀者.書證號，讀者.姓名，讀者.單位，借閱.書號，圖書.書名，借閱.借閱日期

FROM讀者INNERJOIN借閱ON讀者.書證號=借閱.讀者書證號INNERJOIN圖書ON借閱.書號=圖書.書號

ORDERBY讀者.書證號ASC圖書(書號，類別，出版社，作者，書名，定價)；

讀者(書證號，姓名，單位，性別，電話)；

借閱(書號，讀者書證號，借閱日期).查詢例子6.4.4嵌套查詢1.使用IN操作符的嵌套查詢

【例】求選修了高等數(shù)學的學生學號和姓名。

SELECT學號，姓名FROM學生

WHERE學號IN(SELECT學號FROM選課

WHERE課程號IN(SELECT課程號

FROM課程

WHERE課程名=‘高等數(shù)學'))；學生課程庫：學生(學號，姓名，年齡，所在系)；課程(課程號，課程名，先行課)；選課(學號，課程號，成績).高等數(shù)學的課程號選高等數(shù)學的學生學號【例】求C1課程的成績高于張三的學生學號和成績。

SELECT學號，成績

FROM選課

WHERE課程號=‘C1’AND成績>(SELEC成績FROM選課

WHERE課程號=‘C1’AND學號=

(SELECT學號FROM學生

WHERE姓名='張三'))；張三的學號張三C1課的成績學生課程庫：學生(學號，姓名，年齡，所在系)；課程(課程號，課程名，先行課)；選課(學號，課程號，成績).2使用比較符嵌套查詢3使用ANY或ALL操作符的嵌套查詢格式為：〈字段〉〈比較符〉[ANY|ALL]〈子查詢〉

操作符語意>ANY大于子查詢結果中的某個值，即表示大于查詢結果中最小值>ALL大于子查詢結果中的所有值，即表示大于查詢結果中最大值<ANY小于子查詢結果中的某個值，即表示小于查詢結果中最大值<ALL小于子查詢結果中的所有值，即表示小于查詢結果中最小值>=ANY大于等于子查詢結果中的某個值，即表示大于等于結果集中最小值>=ALL大于等于子查詢結果中的所有值，即表示大于等于結果集中最大值<=ANY小于等于子查詢結果中的某個值，即表示小于等于結果集中最大值<=ALL小于等于子查詢結果中的所有值，即表示小于等于結果集中最小值=ANY等于子查詢結果中的某個值，即相當于IN=ALL等于子查詢結果中的所有值(通常沒有實際意義)!=(或<>)ANY不等于子查詢結果中的某個值，!=(或<>)ALL不等于子查詢結果中的任何一個值，即相當于NOTIN例子【例】求其他系中比計算機系某一學生年齡小的學生。

SELECT*

FROM學生

WHERE年齡<ANY(SELECT年齡

FROM學生

WHERE所在系=‘計算機系’)AND

所在系<>‘計算機系’；學生課程庫：學生(學號，姓名，年齡，所在系)；課程(課程號，課程名，先行課)；選課(學號，課程號，成績).計算機系學生的年齡4使用EXISTS操作符的嵌套查詢【例】求沒有選修C2課程的學生姓名。

SELECT姓名

FROM學生

WHERENOTEXISTS(SELECT*FROM選課

WHERE學生.學號=選課.學號AND

課程號='C2')；該學生選C2課的記錄查詢例子【例】查詢借了書的讀者。

SELECT*FROM讀者

WHEREEXISTS(SELECT*FROM借閱

WHERE讀者.書證號=借閱.讀者書證號)或：

SELECT*FROM讀者

WHERE書證號IN(SELECT讀者書證號FROM借閱)圖書(書號，類別，出版社，作者，書名，定價)；

讀者(書證號，姓名，單位，性別，電話)；

借閱(書號，讀者書證號，借閱日期).統(tǒng)計函數(shù)一覽表函數(shù)參數(shù)意義AVG([ALL|DISTINCT]〈數(shù)值表達式〉)求數(shù)值表達式的平均值COUNT([ALL|DISTINCT]〈表達式〉)統(tǒng)計表達式非空值的記錄數(shù)COUNT(*)統(tǒng)計記錄數(shù)MAX(〈表達式〉)求表達式的最大值MIN(〈表達式〉)求表達式的最小值SUM([ALL|DISTINCT]〈算術表達式〉)求算術表達式的和6.4.

5使用函數(shù)和分組查詢操作例子【例】求選修了課程的學生人數(shù)。

SELECTCOUNT(DISTINCT學號)

FROM選課【例】求學生學號及選課門數(shù)。

SELECT學號，COUNT(ALL學號)

FROM選課

GROUPBY學號【例】求選修課超過3門課的學生學號。

SELECT學號

FROM選課

GROUPBY學號HAVINGCOUNT(*)>3學生課程庫：學生(學號，姓名，年齡，所在系)；課程(課程號，課程名，先行課)；選課(學號，課程號，成績).6.4.

6使用計算和匯總查詢操作【例】列出計算機類圖書的書號、名稱及價格，并求出各出版社這類書的總價格，最后求出全部冊數(shù)和總價格。

SELECT書號，名稱，定價

FROM圖書

WHERE類別=’計算機類’

ORDERBY書號ASC

COMPUTECOUNT(*)，SUM(定價)BY出版社

COMPUTECOUNT(*)，SUM(定價)

【例】查詢計算機類和機械工業(yè)出版社出版的圖書。

SELECT*FROM圖書

WHERE類別=’計算機類’

UNIONALLSELECT*FROM圖書

WHERE出版社=’機械工業(yè)出版社’圖書(書號，類別，出版社，作者，書名，定價)；

讀者(書證號，姓名，單位，性別，電話)；

借閱(書號，讀者書證號，借閱日期).查詢例子6.1答：①CREATETABLE職工(職工號CHAR(10)NOTNULLUNIQUE,姓名CHAR(8)NOTNULL,年齡SMALLINT,性別CHAR(2),CONSTRAINTC1CHECK(性別IN('男','女')))GoCREATETABLE社會團體(編號CHAR(8)NOTNULLUNIQUE,名稱CHAR(12)NOTNULL,負責人CHAR(8),活動地點VARCHAR(50),CONSTRAINTC2FOREIGNKEY(負責人)REFERENCES職工(職工號))GoCREATETABLE參加(職工號CHAR(8),編號CHAR(8),參加日期DATE,CONSTRAINTC3PRIMARYKEY(職工號,編號),CONSTRAINC3FOREIGNKEY(職工號)REFERENCES職工(職工號))Go②SELECT職工號,姓名FROM職工,社會團體,參加WHERE職工.職工號=參加.職工號AND參加.編號=社會團體.編號AND社會團體.名稱IN('唱歌隊','籃球隊')③SELECT*FROM職工WHERENOTEXISTS(SELECT*FROM參加WHERE參加.職工號=職工.職工號)④SELECT*FROM職工WHERENOTEXISTS(SELECT*FROM參加WHERENOTEXISTS(SELECT*FROM社會團體WHERE參加.職工號=職工.職工號AND參加.編號=社會團體.編號))

⑤SELECT職工號FROM職工WHERENOTEXISTS(SELECT*FROM參加參加1WHERE參加1.職工號='1001'ANDNOTEXISTS(SELECT*FROM參加參加2WHERE參加2.編號=參加1.編號AND參加2.職工號=職工.職工號))⑥SELECT編號,COUNT(職工號)FROM參加GROUPBY編號⑦SELECT社會團體.名稱,COUNT(*)FROM社會團體，參加AWHERE社會團體.編號=A.編號GROUPBYA.編號HAVINGCOUNT(*)=(SELECTMAX(COUNT(*))FROM參加BGROUPBYB.編號)或：SELECT社會團體.名稱,COUNT(*)FROM社會團體，參加AWHERE社會團體.編號=A.編號GROUPBYA.編號HAVINGCOUNT(*)>=ALL(SELECTCOUNT(*)FROM參加BGROUPBYB.編號)⑧SELECT社會團體.名稱,職工.姓名FROM職工,社會團體,參加WHERE社會團體.編號=參加.編號AND社會團體.負責人=職工.職工號GROUPBY參加.編號HAVINGCOUNT(參加.編號)>100;⑨CREATEVIEW社團負責人(編號,名稱,負責人職工號,負責人姓名,負責人性別)ASSELECT編號,名稱,負責人,姓名,性別

FROM社會團體,職工

WHERE社會團體.負責人=職工.職工號GOCREATEVIEW參加人情況(職工號,姓名,社團編號,社團名稱,參加日期)ASSELECT參加.職工號,姓名,社會團體.編號,名稱,參加日期

FROM職工,社會團體,參加

WHERE職工.職工號=參加.職工號AND參加.編號=社會團體.編號6.2答：①SELECT姓名,聯(lián)系電話FROM供應商

WHERE所在城市='天津②SELECT*FROM工程

WHERE預算BETWEEN50000AND100000ORDERBY預算DESC③SELECT工程代號FROM供應零件

WHERE供應商代碼='S1④SELECT零件.零件名,供應零件.數(shù)量FROM零件，供應零件WHERE零件.零件代碼=供應零件.零件代碼AND供應零件.工程代碼='J2'⑤SELECT零件代號FROM供應商,供應零件WHERE供應商.供應商代碼=供應零件.供應商代碼AND供應商.所在城市='上海'⑥SELECT工程名FROM供應商,供應零件,工程WHERE供應商.供應商代碼=供應零件.供應商代碼AND供應零件.工程代碼=工程.工程代碼AND供應商.所在城市='上海'⑦SELECT工程號碼FROM工程WHERENOTEXISTS(SELECT*FORM供應零件

WHERE工程.工程代碼=供應零件.工程代碼AND供應商代碼IN(SELECT供應商代碼

FROM供應商WHERE所在城市='天津'))⑧UPDATE零件SET顏色='藍色'WHERE顏色='紅色'⑨UPDATE供應零件SET供應商代碼='S3'WHERE供應商代碼='S5'