數(shù)據(jù)查詢(xún)和更新

1、關(guān)于數(shù)據(jù)查詢(xún)與更新第一張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.1 簡(jiǎn)單數(shù)據(jù)查詢(xún)SELECT語(yǔ)句的組成子句解釋SELECT后跟一組列的列表，或者一個(gè)星號(hào)表示想要返回所有的列FROM后跟一個(gè)表或者視圖的名字，或者帶有連接表達(dá)式的多個(gè)表WHERE后跟過(guò)濾規(guī)則ORDER BY后跟一組列的列表進(jìn)行排序第二張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.1.1 SELECT和FROM子句1、指定返回列名【例 5.1】返回XS表中所有的記錄的XH（學(xué)號(hào)）、XM(姓名)、CSRQ(出生日期)列。SELECT XH, XM, CSRQ FROM XS第三張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.1.1

2、SELECT和FROM子句2、返回所有列SELECT * FROM XS第四張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.1.1 SELECT和FROM子句3、在SELECT子句后加入計(jì)算列（1）SELECT XH, XM+ (+XB+) , CSRQ FROM XS（2）SELECT XH, XM,GETDATE(),10, CSRQ FROM XS第五張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.1.2 列別名和表別名1、 原始列名 AS 別名。SELECT XH AS 學(xué)號(hào), XM+(+XB+) AS 姓名性別,CSRQ AS 出生日期FROM XS第六張，PPT共七十九頁(yè)，創(chuàng)作于202

3、2年6月5.1.2 列別名和表別名2、 原始列名 別名 SELECT XH 學(xué)號(hào), XM 姓名, CSRQ 出生日期 FROM XS3、 別名=原始列名SELECT學(xué)號(hào)=XH, 姓名= XM, 出生日期= CSRQ FROM XS第七張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.1.2 列別名和表別名表別名：1、“始表名 AS 別名” SELECT XH, XM, CSRQFROM XS a 2、 “原始表名 別名” SELECT XH, XM, CSRQ FROM XS AS a第八張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.1.3 WHERE子句例：從XS表中查找姓名為“曹敏”的同學(xué)的學(xué)

4、號(hào)，姓名，性別。SELECT XH,XM,XB FROM XS WHERE XM=曹敏第九張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.1.3 WHERE子句N(xiāo)ULL值參與的比較：當(dāng)字段未設(shè)置值或變量未設(shè)值時(shí)，其值為NULL,即不包含任何值（1）、當(dāng)ANSI_NULLS為OFF時(shí)(默認(rèn))，任何與NULL值的運(yùn)算結(jié)果都為NULL,任何與NULL的比較結(jié)果都為UNKNOW(不確定)。（2）當(dāng)ANSI_NULLS為ON時(shí)，除了=和!=或者比較運(yùn)算符外，其他比較運(yùn)算符和NULL比較也會(huì)得到UNKNOWN值。第十張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.1.3 WHERE子句例1：SELECT *

5、FROM XS WHERE SFZ=NULL OR SFZ !=NULL結(jié)果：空例2： SELECT * FROM XS WHERE SFZ is NULL第十一張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.1.3 WHERE子句例3：SET ANSI_NULLS OFFSELECT * FROM XS WHERE SFZ=NULL第十二張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.1.4 ORDER BY子句1、單個(gè)字段排序SELECT * FROM XS ORDER BY XH -按學(xué)號(hào)進(jìn)行排序NULL排最前面第十三張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.1.4 ORDER BY子句

6、2、多字段排序ORDER BY后面可以指定多個(gè)排序字段，字段之間以“，”分隔。在指定了多列情況下，首先按第一列排序，在第一列相同的情況下按第二列排序，以此類(lèi)推。第十四張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.1.4 ORDER BY子句例：SELECT XH,XM,XB,DATEDIFF(DAY,CSRQ,GETDATE()/365 AS NL FROM XS ORDER BY XM, NL注意：漢字按拼音首字母排序此處有空格第十五張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.1.4 ORDER BY子句降序排序：如果沒(méi)有特別說(shuō)明，值是以升序序列進(jìn)行排序的。你也可以選擇使用ASC關(guān)鍵字指定

7、升序排序。如果你希望以降序排列，則在ORDER BY后面使用DESC關(guān)鍵字。例：SELECT * FROM XS ORDER BY CSRQ DESC 第十六張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.1.5 TOP子句TOP子句用來(lái)返回排在前面特定數(shù)量的記錄，而不管符合條件的行是多少。有兩種方式用來(lái)指定返回的行：指定排在前面的絕對(duì)記錄數(shù)，如：SELECT TOP (5)* FROM XS ORDER BY CSRQ DESC指定排在前面的記錄數(shù)占總行數(shù)的百分比SELECT TOP(0.1) PERCENT * FROM XS ORDER BY CSRQ DESC第十七張，PPT共七十九頁(yè)，

8、創(chuàng)作于2022年6月5.1.5 TOP子句WITH TIES子句WITH TIES子句用來(lái)解決在使用TOP子句按百分比返回后，與最后一名數(shù)據(jù)相同的記錄不能出現(xiàn)在結(jié)果集中的問(wèn)題。例：SELECT TOP(5) WITH TIES * FROM XS ORDER BY CSRQ DESC第十八張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.1.6 DISTINCT關(guān)鍵字DISTINCT用來(lái)從結(jié)果集中去除重復(fù)的記錄。例：統(tǒng)計(jì)學(xué)生共來(lái)自于哪些民族。語(yǔ)句1：SELCET MZ FROM XS語(yǔ)句2：SELCET DISTINCT MZ FROM XS第十九張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.2

9、分組查詢(xún)5.2.1 聚合函數(shù)特點(diǎn)：作用在多條記錄上。換句話(huà)說(shuō)，函數(shù)的返回值建立在對(duì)多條記錄進(jìn)行統(tǒng)計(jì)的基礎(chǔ)上。常用聚合函數(shù)：SUM, COUNT, MAX, MIN, AVG1、計(jì)算總分SELECT SUM(FS) FROM CJ WHERE XQDM=21 AND XSDM=20第二十張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.2.1 聚合函數(shù)2、計(jì)算最高分、最低分和平均分：SELECT MAX(FS) 最高分,MIN(FS) 最低分,AVG(FS) 平均分 FROM CJ WHERE XQDM=5 AND XSDM=163483、統(tǒng)計(jì)班級(jí)人數(shù)SELECT COUNT(*) FROM XS

10、WHERE BJDM=30第二十一張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.2.2 GROUP BY 子句GROUP BY 子句將查詢(xún)結(jié)果按某一列或多列值分組輸出，值相等的為一組。對(duì)查詢(xún)結(jié)果分組的目的是使集函數(shù)作用于每一個(gè)組，即每一個(gè)組都有一個(gè)函數(shù)值。第二十二張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.2.2 GROUP BY 子句例：查詢(xún)每個(gè)同學(xué)的最高分、最低分和平均分。SELECT XSDM,MAX(FS) 最高分,MIN(FS) 最低分,AVG(FS) 平均分 FROM CJ GROUP BY XSDM第二十三張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.2.2 GROUP BY

11、子句例：每個(gè)同學(xué)在不同學(xué)期的最高分、最低分、平均分：SELECT XSDM,XQDM,MAX(FS) 最高分,MIN(FS) 最低分,AVG(FS) 平均分FROM CJ GROUP BY XSDM,XQDM第二十四張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.2.2 GROUP BY 子句注意：出現(xiàn)在GROUP BY 子句后面的字段列表應(yīng)該也出現(xiàn)在SELECT子句中，否則獲得的數(shù)據(jù)可能沒(méi)有意義。如：SELECT MAX(FS) 最高分,MIN(FS) 最低分,AVG(FS) 平均分 FROM CJ GROUP BY XSDM上述語(yǔ)句執(zhí)行后的結(jié)果集中你將無(wú)法確定成績(jī)屬于哪位學(xué)生第二十五張，PP

12、T共七十九頁(yè)，創(chuàng)作于2022年6月5.2.2 GROUP BY 子句出現(xiàn)在SELECT子句中的字段列表除了聚合函數(shù)外，都必須出現(xiàn)在GROUP BY 子句后面。例如，下面的語(yǔ)句將會(huì)報(bào)錯(cuò)：SELECT XSDM,XQDM,MAX(FS) 最高分,MIN(FS) 最低分,AVG(FS) 平均分FROM CJ GROUP BY XSDM第二十六張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.2.3 HAVING子句HAVING子句用來(lái)對(duì)分組后的數(shù)據(jù)進(jìn)行篩選例： 查詢(xún)平均成績(jī)大于65分的學(xué)生的最高分、最低分和平均成績(jī)。SELECT XSDM,MAX(FS) 最高分,MIN(FS) 最低分,AVG(FS)

13、平均分FROM CJ GROUP BY XSDM HAVING AVG(FS)65第二十七張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.2.3 HAVING子句HAVING子句和WHERE子句很相似，均用于設(shè)置數(shù)據(jù)篩選條件。WHERE子句對(duì)分組前的數(shù)據(jù)進(jìn)行篩選，條件中不能包含聚合函數(shù)；HAVING子句對(duì)分組過(guò)后的數(shù)據(jù)進(jìn)行篩選，條件中經(jīng)常包含聚合函數(shù)。 HAVING子句必須和GROUP BY子句聯(lián)合使用第二十八張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.3 聯(lián)接查詢(xún)當(dāng)返回的數(shù)據(jù)集中需要包含位于多個(gè)表中的數(shù)據(jù)時(shí)，需要用到聯(lián)接查詢(xún)。及聯(lián)接查詢(xún)用于在一個(gè)查詢(xún)語(yǔ)句中同時(shí)返回多個(gè)表中的數(shù)據(jù)第二十九張，P

14、PT共七十九頁(yè)，創(chuàng)作于2022年6月5.3.1 交叉聯(lián)接(CROSS JOIN)交叉聯(lián)接返回兩個(gè)表的笛卡爾積。即：返回的數(shù)據(jù)集為一個(gè)表中的每一行與另一個(gè)表中每一行的組合。例：簡(jiǎn)單交叉聯(lián)接SELECT * FROM KC CROSS JOIN XQ返回的記錄數(shù)量為兩個(gè)表記錄數(shù)的乘積上面語(yǔ)句可以省略CROSS JOIN關(guān)鍵字,用“，”將兩個(gè)表連接起來(lái)，即：SELECT * FROM KC , XQ該返回結(jié)果和交叉聯(lián)接是基本是一致的，只是沒(méi)有重復(fù)的字段。第三十張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.3.2 內(nèi)聯(lián)接(INNER JOIN)內(nèi)聯(lián)接是聯(lián)接兩個(gè)表最常用的方法，使用比較運(yùn)算符進(jìn)行表間某(

15、些)列數(shù)據(jù)的比較操作，并列出這些表中與聯(lián)接條件相匹配的數(shù)據(jù)行。內(nèi)聯(lián)接可以理解為從兩個(gè)表的笛卡爾積中篩選出完全符合聯(lián)接條件的記錄例： 顯示成績(jī)表中的所有數(shù)據(jù)，要求包含學(xué)生學(xué)號(hào)和姓名。SELECT XS.XH 學(xué)號(hào),XS.XM 姓名,KCDM 課程代碼,FS 分?jǐn)?shù),CJ.XSDM,XS.XSDM FROM XS INNER JOIN CJ ON CJ.XSDM=XS.XSDM 第三十一張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.3.3 外聯(lián)接外聯(lián)接分為三種：左外聯(lián)接(LEFT OUTER JOIN或LEFT JOIN)右外聯(lián)接(RIGHT OUTER JOIN或RIGHT JOIN)全外聯(lián)接(F

16、ULL OUTER JOIN或FULL JOIN) 與內(nèi)聯(lián)接不同，外聯(lián)接不僅列出與聯(lián)接條件相匹配的行，還會(huì)顯示左表(左外聯(lián)接時(shí))、右表(右外聯(lián)接時(shí))或兩個(gè)表(全外聯(lián)接時(shí))中所有符合搜索條件的記錄。第三十二張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.3.3 外聯(lián)接1、左外聯(lián)接左邊表中的行都會(huì)出現(xiàn)在結(jié)果數(shù)據(jù)集中，如果左邊表中的某一行在右邊表中沒(méi)有匹配的行（不滿(mǎn)足聯(lián)接條件），則以空值取代右邊表中的值。例：查看每個(gè)專(zhuān)業(yè)有哪些班級(jí)。使用外聯(lián)接：SELECT a.ZYDM, a.ZYMC, b.BJDM, b.ZYDM AS Expr1, b.BJMCFROM ZY AS a LEFT JOIN BJ

17、AS b ON a.ZYDM = b.ZYDM使用內(nèi)聯(lián)接：SELECT a.ZYDM, a.ZYMC, b.BJDM, b.ZYDM AS Expr1, b.BJMCFROM ZY AS a JOIN BJ AS b ON a.ZYDM = b.ZYDM第三十三張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.3.3 外聯(lián)接2、右外聯(lián)接右外聯(lián)接和左外聯(lián)接類(lèi)似，不同的是把右邊的表作為外部表(所有右邊表中的行包含在結(jié)果數(shù)據(jù)集中)。例：查看每個(gè)專(zhuān)業(yè)有哪些班級(jí)。SELECT a.ZYDM, a.ZYMC, b.BJDM, b.ZYDM AS Expr1, b.BJMCFROM ZY AS a RIGHT

18、JOIN BJ AS b ON a.ZYDM = b.ZYDM第三十四張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.3.3 外聯(lián)接3、全外聯(lián)接在全外聯(lián)接中，位于FULL JOIN兩邊的表中的行都會(huì)包含在結(jié)果數(shù)據(jù)集中。第三十五張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.3.4 自聯(lián)接列名說(shuō)明YGDM員工代碼YGXM員工姓名SJLD該員工上級(jí)領(lǐng)導(dǎo)代碼員工表自聯(lián)接所聯(lián)接的兩個(gè)表在物理上為同一張表，但在邏輯上可看作兩個(gè)表，用不同的表別名加以區(qū)分第三十六張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.3.4 自聯(lián)接例：查詢(xún)每位員工上級(jí)領(lǐng)導(dǎo)SELECT A.*,B.YGXM FROM YG A LEFT

19、JOIN YG B ON A.SJLD=B.YGDM第三十七張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.3.5 多表聯(lián)接例 ：顯示學(xué)生信息，包括學(xué)生所在班級(jí)名稱(chēng)、專(zhuān)業(yè)名稱(chēng)和和系部名稱(chēng)SELECT XB.XBMC, ZY.ZYMC, BJ.BJMC, XS.*FROM XS INNER JOIN BJ ON BJ.BJDM = XS.BJDM INNER JOIN ZY ON BJ.ZYDM = ZY.ZYDM INNER JOIN XB ON ZY.XBDM = XB.XBDM第三十八張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.4 子查詢(xún) 有兩種子查詢(xún)類(lèi)型：標(biāo)準(zhǔn)子查詢(xún)和相關(guān)子查詢(xún)。標(biāo)準(zhǔn)子查

20、詢(xún)執(zhí)行一次，結(jié)果反饋給外層查詢(xún)，它的返回值跟其外層查詢(xún)沒(méi)有聯(lián)系（不相關(guān)）。相關(guān)子查詢(xún)?cè)谧硬樵?xún)中引用了外層查詢(xún)中的表，因此它的執(zhí)行次數(shù)取決于其外層查詢(xún)的查詢(xún)次數(shù)。第三十九張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.4.1 標(biāo)準(zhǔn)子查詢(xún)與相關(guān)子查詢(xún)1、標(biāo)準(zhǔn)子查詢(xún) 例 ： 查詢(xún)與學(xué)號(hào)為“0630160219”的同學(xué)同班的學(xué)生信息。步驟1：我們先求這個(gè)同學(xué)所在班級(jí)的班級(jí)代碼：SELECT BJDMFROM XSWHERE XH = 0630160219步驟2：面語(yǔ)句查詢(xún)結(jié)果為23。從XS表中查詢(xún)BJDM為23的學(xué)生信息：SELECT *FROM XSWHERE BJDM=23第四十張，PPT共七十九頁(yè)

21、，創(chuàng)作于2022年6月5.4.1 標(biāo)準(zhǔn)子查詢(xún)與相關(guān)子查詢(xún)步驟3：將前兩個(gè)語(yǔ)句整合：SELECT *FROM XSWHERE BJDM = (SELECT BJDM FROM XS WHERE XH = 0630160219)注意事項(xiàng)：1、必須用“（”和“）”將子查詢(xún)擴(kuò)起來(lái)。2、處于比較運(yùn)算符其中一邊的子查詢(xún)，其返回值應(yīng)該只能有一行、一列，其返回值的數(shù)據(jù)類(lèi)型應(yīng)該和運(yùn)算符另一邊的值（列）是一致或可以被系統(tǒng)自動(dòng)轉(zhuǎn)換的。3、子查詢(xún)中不能檢索包含數(shù)據(jù)類(lèi)型為text和image的列。第四十一張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.4.1 標(biāo)準(zhǔn)子查詢(xún)與相關(guān)子查詢(xún)2、相關(guān)子查詢(xún)例 ： 查詢(xún)存在同名的學(xué)生

22、信息1)：語(yǔ)句基本形式：SELECT * FROM XS WHERE 該同學(xué)存在同名2)判斷指定學(xué)生是否重名：（SELECT COUNT(*) FROM XS WHERE XM=XXX）13）將2）代入1），并給xs表別名SELECT *FROM XS aWHERE (SELECT COUNT(*) FROM XS b WHERE (b.XM = XXX) 1)第四十二張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.4.1 標(biāo)準(zhǔn)子查詢(xún)與相關(guān)子查詢(xún)4)將指定學(xué)生xxx替換為外層被查詢(xún)的學(xué)生SELECT *FROM XS aWHERE (SELECT COUNT(*) FROM XS b WHERE

23、 (b.XM = a.XM) 1)第四十三張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.4.2 使用ALL、ANY、SOME關(guān)鍵字ALL 在條件語(yǔ)句中，表示必須滿(mǎn)足每一個(gè)條件項(xiàng)例： 查詢(xún)XS表中年齡最大的學(xué)生。SELECT *FROM XSWHERE CSRQ = ALL (SELECT CSRQ FROM XS )第四十四張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.4.2 使用ALL、ANY、SOME關(guān)鍵字ANY（SOME） 在條件語(yǔ)句中，表示滿(mǎn)足其中任何一個(gè)條件項(xiàng)例：取要出生日期不是最小的學(xué)生信息SELECT *FROM XSWHERE CSRQ ANY (SELECT CSRQ FRO

24、M XS )第四十五張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.4.3 EXISTS 和IN子查詢(xún)EXISTS 子查詢(xún)被稱(chēng)為“存在子查詢(xún)”,它用來(lái)判斷子查詢(xún)相關(guān)表中是否存在滿(mǎn)足子查詢(xún)條件的行，而對(duì)于這些行的具體數(shù)據(jù)，子查詢(xún)并不關(guān)心也不會(huì)被返回。當(dāng)內(nèi)層查詢(xún)結(jié)果為“存在”滿(mǎn)足子查詢(xún)條件的行，則外層的WHERR子句返回真值，否則返回假值。由于EXISTS 子查詢(xún)并不返回滿(mǎn)足條件的數(shù)據(jù)，因此SELECT子句后的字段列表并無(wú)多大意義，通常將通過(guò)EXISTS引入的子查詢(xún)的字段列表設(shè)為“*”，而不使用具體列名。EXISTS 子查詢(xún)語(yǔ)法如下：NOT EXISTS (子查詢(xún))第四十六張，PPT共七十九頁(yè)，創(chuàng)作

25、于2022年6月5.4.3 EXISTS 和IN子查詢(xún)例：查詢(xún)XS表中曾參加過(guò)考試的學(xué)生信息。SELECT *FROM XSWHERE EXISTS (SELECT * FROM CJ WHERE XSDM = XS.XSDM)第四十七張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.4.3 EXISTS 和IN子查詢(xún)IN關(guān)鍵字用來(lái)確定給定的值（測(cè)試表達(dá)式）是否與子查詢(xún)或列表中的值相匹配，它的作用相當(dāng)于 “=ANY”。IN子查詢(xún)語(yǔ)法如下：測(cè)試表達(dá)式 NOT IN ( 子查詢(xún) 或者 表達(dá)式列表 ) 第四十八張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.4.3 EXISTS 和IN子查詢(xún)例 ：查詢(xún)位于

26、班級(jí)代碼為22、23或24的班級(jí)中所有學(xué)生的信息。SELECT *FROM XSWHERE BJDM IN (22,23，24)例：查詢(xún)屬于計(jì)算機(jī)信息管理專(zhuān)業(yè)（專(zhuān)業(yè)代碼為6）所有學(xué)生的信息。SELECT *FROM XSWHERE BJDM IN (SELECT BJDM FROM BJ WHERE ZYDM=6)第四十九張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.4.4 派生表一個(gè)子查詢(xún)能夠返回一個(gè)由行和列構(gòu)成的數(shù)據(jù)集，我們可以將這個(gè)數(shù)據(jù)集再看作一個(gè)表來(lái)處理，這個(gè)由子查詢(xún)得出的新表就是我們說(shuō)的“派生表”派生表有利于提高查詢(xún)速度、簡(jiǎn)化操作派生表不是存儲(chǔ)在數(shù)據(jù)庫(kù)中的對(duì)象，它只在查詢(xún)期間有效。第

27、五十張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.4.4 派生表例 查詢(xún)班級(jí)代碼為22的班級(jí)（06級(jí)商務(wù)英語(yǔ)1班）在學(xué)期代碼為5的學(xué)期（20062007學(xué)年第1期）所有學(xué)生的考試成績(jī)，包含學(xué)號(hào)，姓名，課程名稱(chēng)和分?jǐn)?shù)。步驟1：我們從CJ表中取出符合條件的記錄SELECT *FROM CJWHERE XQDM = 5 AND XSDM IN (SELECT XSDM FROM XS WHERE BJDM = 22)第五十一張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.4.4 派生表步驟2：將上步結(jié)果集當(dāng)作虛擬表a使用，并與KC、XS表聯(lián)接以獲取姓名、課程名稱(chēng)等信息SELECT XS.XH, XS.

28、XM, KC.KCMC, a.FSFROM a JOIN XS ON XS.XSDM = a.XSDM JOIN KC ON a.KCDM = KC.KCDM第五十二張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.4.4 派生表步驟3：將第一步的語(yǔ)句替換第二步中虛擬表aSELECT XS.XH, XS.XM, KC.KCMC, a.FSFROM (SELECT XSDM, KCDM, XQDM, FS FROM CJ WHERE XQDM = 5 AND XSDM IN (SELECT XSDM FROM XS WHERE BJDM = 22) AS a JOIN XS ON XS.XSDM =

29、 a.XSDM JOIN KC ON a.KCDM = KC.KCDM第五十三張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.5 聯(lián)合查詢(xún)UNION關(guān)鍵字可以將兩個(gè)或更多相互獨(dú)立的SELECT語(yǔ)句的查詢(xún)結(jié)果合并成一個(gè)集合，即執(zhí)行聯(lián)合查詢(xún)。聯(lián)合查詢(xún)要求合并的幾個(gè)數(shù)據(jù)集之間應(yīng)該具有相同的字段數(shù)目和類(lèi)型。 例：在同一個(gè)數(shù)據(jù)集中返回賀清青同學(xué)所有科目的考試成績(jī)和平均成績(jī)。第五十四張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.5 聯(lián)合查詢(xún)第一步，返回賀清青同學(xué)所有科目的成績(jī)：SELECT KC.KCMC 科目, ABS(CJ.FS) 成績(jī)FROM CJ INNER JOIN KC ON CJ.KCDM =

30、 KC.KCDM INNER JOIN XS ON CJ.XSDM = XS.XSDMWHERE XS.XM = 賀清青第二步，返回賀清青同學(xué)所有科目的平均分：SELECT AVG(ABS(CJ.FS)FROM CJ INNER JOIN XS ON CJ.XSDM = XS.XSDMWHERE XS.XM = 賀清青第五十五張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.5 聯(lián)合查詢(xún)第三步，為了滿(mǎn)足兩個(gè)結(jié)果集具有相同類(lèi)型的字段，我們對(duì)上一語(yǔ)句改為下面這種形式：SELECT 科目= 平均分, 成績(jī)=(SELECT AVG(ABS(CJ.FS)FROM CJ INNER JOIN XS ON CJ

31、.XSDM = XS.XSDMWHERE XS.XM = 賀清青)第四部,將第一步和第三步的結(jié)果用UNION關(guān)鍵字集合并：SELECT KC.KCMC 科目, ABS(CJ.FS) 成績(jī)FROM CJ INNER JOIN KC ON CJ.KCDM = KC.KCDM INNER JOIN XS ON CJ.XSDM = XS.XSDMWHERE XS.XM = 賀清青UNIONSELECT 科目= 平均分, 成績(jī)=(SELECT AVG(ABS(CJ.FS)FROM CJ INNER JOIN XS ON CJ.XSDM = XS.XSDMWHERE XS.XM = 賀清青)第五十六張，P

32、PT共七十九頁(yè)，創(chuàng)作于2022年6月5.6 通用表表達(dá)式CTE CTE 與派生表類(lèi)似，不存儲(chǔ)為數(shù)據(jù)庫(kù)對(duì)象，并且只在查詢(xún)期間有效。與派生表的不同，CTE 可在同一查詢(xún)中引用多次，可自引用。使用 CTE 可以獲得提高可讀性和輕松維護(hù)復(fù)雜查詢(xún)的優(yōu)點(diǎn)。CTE 由表示CTE的表達(dá)式名稱(chēng)、可選列列表和定義CTE的查詢(xún)組成。CTE的基本語(yǔ)法結(jié)構(gòu)如下：WITH CTE_NAME ( 列名1 ,.n ) AS( CTE查詢(xún)語(yǔ)句 )運(yùn)行 CTE 的語(yǔ)句為：SELECT * FROM CTE_NAME 第五十七張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.6 通用表表達(dá)式CTE例 查詢(xún)班級(jí)代碼為22的班級(jí)（06級(jí)商

33、務(wù)英語(yǔ)1班）在學(xué)期代碼為5的學(xué)期（20062007學(xué)年第1期）所有學(xué)生的考試成績(jī)，包含學(xué)號(hào)，姓名，課程名稱(chēng)和分?jǐn)?shù)。WITH CTE_CJAS(SELECT *FROM CJWHERE XQDM = 5 AND XSDM IN (SELECT XSDM FROM XS WHERE BJDM = 22) )SELECT XS.XH, XS.XM, KC.KCMC, a.FSFROM CTE_CJ AS a JOIN XS ON XS.XSDM = a.XSDM JOIN KC ON a.KCDM = KC.KCDM第五十八張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.8 排名函數(shù)5.8.1 ROW

34、_NUMBERROW_NUMBER函數(shù)用來(lái)返回結(jié)果集內(nèi)每行的行號(hào)，第一行從 1 開(kāi)始。其語(yǔ)法為：ROW_NUMBER ( ) OVER ( )位于OVER后的括號(hào)中需要一個(gè)ORDER BY 子句，該子句用來(lái)確定為行分配唯一 ROW_NUMBER 的順序。例 ：查詢(xún)06級(jí)商務(wù)英語(yǔ)1班（班級(jí)代碼為22）的所有學(xué)生的學(xué)號(hào)、姓名、出生日期、家庭是地址，要求第一列為行號(hào),并按學(xué)號(hào)排序。SELECT ROW_NUMBER ( ) OVER ( ORDER BY XH ) 行號(hào),XH 學(xué)號(hào),XM 姓名,CSRQ 出生日期,JTDZ 家庭地址FROM XS WHERE BJDM=22 第五十九張，PPT共七十

35、九頁(yè)，創(chuàng)作于2022年6月5.8.2 RANK與DENSE_RANKRANK與DENSE_RANK函數(shù)都用來(lái)進(jìn)行名次的排列。RANK函數(shù)在排定名次時(shí)會(huì)以排在前面的記錄數(shù)為依據(jù)，而DENSE_RANK在排定名次時(shí)只考慮排在前面的不同數(shù)值的個(gè)數(shù)作為依據(jù)。RANK與DENSE_RANK的語(yǔ)法和ROW_NUMBER函數(shù)相似，即：RANK ( ) OVER ( ) 或DENSE_RANK ( ) OVER ( ) 第六十張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.8.2 RANK與DENSE_RANK例 查詢(xún)班級(jí)代碼為22的班級(jí)（06級(jí)商務(wù)英語(yǔ)1班）在學(xué)期代碼為5的學(xué)期（20062007學(xué)年第1期）所

36、有學(xué)生的總分和名次。步驟1：查詢(xún)學(xué)生的總分信息：SELECT XS.XH 學(xué)號(hào), XS.XM 姓名,SUM(CJ.FS) 總分FROM CJ INNER JOIN XS ON XS.XSDM = CJ.XSDM INNER JOIN KC ON CJ.KCDM = KC.KCDMGROUP BY XS.XH,XS.XM ORDER BY 總分第六十一張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.8.2 RANK與DENSE_RANK步驟2：在SELECT子句中添加RANK 函數(shù)，并在ORDER BY子句中指定排名的依據(jù)：RANK ( ) OVER (ORDER BY SUM(CJ.FS) 最終

37、結(jié)果如下：SELECT XS.XH 學(xué)號(hào), XS.XM 姓名,SUM(CJ.FS) 總分,RANK() OVER(ORDER BY SUM(CJ.FS) 名次FROM CJ INNER JOIN XS ON XS.XSDM = CJ.XSDM INNER JOIN KC ON CJ.KCDM = KC.KCDMGROUP BY XS.XH,XS.XM 第六十二張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.8.2 RANK與DENSE_RANK將上述語(yǔ)句中RANK替換為DENSE_RANK，則執(zhí)行結(jié)果為：第六十三張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.9 數(shù)據(jù)更新5.9.1 使用INSER

38、T語(yǔ)句插入數(shù)據(jù)1、簡(jiǎn)單數(shù)據(jù)插入使用INSERT語(yǔ)句向表中插入單行數(shù)據(jù)的語(yǔ)法如下：INSERT INTO （字段列表） VALUES （值列表） 例 學(xué)生李金龍剛轉(zhuǎn)到我校06級(jí)經(jīng)濟(jì)信息管理班（BJDM =26 ），請(qǐng)將該生數(shù)據(jù)添加到向XS表中。該生的登記信息如下：姓名：李金龍，性別：男，出生日期：1982-2-1。INSERT INTO XS (XM, XB, CSRQ, BJDM)VALUES (李金龍, 男, 1982-2-1, 26)第六十四張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.9.1 使用INSERT語(yǔ)句插入數(shù)據(jù)如果需要給每個(gè)字段指定值，則可省略字段列表，但要保證值列表與表中字段

39、一一對(duì)應(yīng)例 BJ表中包括BJDM(班級(jí)代碼),ZYDM（專(zhuān)業(yè)代碼）,BJMC（班級(jí)名稱(chēng)）,NJ（年級(jí)）,ZT（狀態(tài)）四個(gè)字段。新增班級(jí) 07軟件技術(shù)4班，專(zhuān)業(yè)代碼13，年級(jí)2007，狀態(tài)值為0。INSERT INTO BJ VALUES (1001,13,07軟件技術(shù)班,2007,0)第六十五張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.9.1 使用INSERT語(yǔ)句插入數(shù)據(jù)2、批量數(shù)據(jù)插入在INSERT語(yǔ)句中使用一個(gè)SELECT查詢(xún)將一個(gè)查詢(xún)結(jié)果的內(nèi)容添加到表中。其語(yǔ)法格式為：INSERT INTO （字段列表） 例 在數(shù)據(jù)庫(kù)中新建表XS_2,用來(lái)存放已畢業(yè)學(xué)生信息，其結(jié)構(gòu)和XS表完全相同。請(qǐng)

40、將2006級(jí)所有學(xué)生學(xué)號(hào)、班級(jí)、姓名、出生日期、家庭地址信息插入到XS_2中，并將ZT（狀態(tài)）值設(shè)為1。年級(jí)信息保存在BJ表中,所以要知道學(xué)生所在年級(jí)，需要將BJ表和XS表聯(lián)接。INSERT INTO XS_2 (XH,BJDM, XM, CSRQ, JTDZ,ZT)SELECT XH, BJ.BJDM,XM, CSRQ, JTDZ,1FROM XS INNER JOIN BJ ON XS.BJDM = BJ.BJDMWHERE BJ.NJ = 2006第六十六張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.9.1 使用INSERT語(yǔ)句插入數(shù)據(jù)3、將數(shù)據(jù)插入新表可以使用如下語(yǔ)法將查詢(xún)到的數(shù)據(jù)插入

41、到一個(gè)新表中：SELECT INTO FROM 表名例 將CJ表中KCDM為6的數(shù)據(jù)插入到新表CJ_2中：SELECT * INTO CJ_2 FROM CJ WHERE KCDM=6第六十七張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.9.2 使用UPDATE語(yǔ)句修改數(shù)據(jù)UPDATE語(yǔ)法如下：UPDATE SET 字段1=字段值1, 字段2=字段值2,WHERE 例 XS表中有些學(xué)生民族為“漢”,有些為“漢族”，將表中民族為“漢族”的數(shù)據(jù)統(tǒng)一改為民族為“漢”。UPDATE XSSET MZ=漢WHERE MZ=漢族第六十八張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.9.3 使用 DELET

42、E 刪除數(shù)據(jù)DELETE語(yǔ)句基本語(yǔ)法如下：DELETE FROM WHERE 例 ZY表中，如果一個(gè)專(zhuān)業(yè)沒(méi)有任何班級(jí)屬于它，則將這個(gè)專(zhuān)業(yè)從ZY表中刪除。DELETE ZY WHERE ZYDM NOT IN (SELECT ZYDM FROM BJ) 第六十九張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.9.4 TRUNCATE TABLE語(yǔ)句用DELETE語(yǔ)句和TRUNCATE TABLE語(yǔ)句都能夠刪除表中的所有數(shù)據(jù)，但是使用TRUNCATE TABLE語(yǔ)句比用DELETE語(yǔ)句快得多，原因如下：使用DELETE語(yǔ)句，系統(tǒng)將一次一行地處理要?jiǎng)h除的表中的記錄，在從表中刪除行之前，在事務(wù)處理日志中

43、記錄相關(guān)的刪除操作和刪除行中的列值，以防止刪除失敗時(shí)，可以使用事務(wù)處理日志來(lái)恢復(fù)數(shù)據(jù)。TRUNCATE TABLE則一次性完成刪除與表有關(guān)的所有數(shù)據(jù)頁(yè)的操作。另外，TRUNCATE TABLE語(yǔ)句并不更新事務(wù)處理日志。TRUNCATE TABLE語(yǔ)句語(yǔ)法如下：TRUNCATE TABLE 第七十張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.10事務(wù)5.10.1 事務(wù)的基本概念事務(wù)是一種機(jī)制，是一個(gè)操作序列，它包含了一組數(shù)據(jù)庫(kù)操作命令，所有的命令作為一個(gè)整體一起向系統(tǒng)提交或撤消操作請(qǐng)求，即要么都執(zhí)行，要么都不執(zhí)行。因此，事務(wù)是一個(gè)不可分割的工作邏輯單元。在數(shù)據(jù)庫(kù)系統(tǒng)上執(zhí)行并發(fā)操作時(shí)，事務(wù)是作為最

44、小的控制單元來(lái)使用的。第七十一張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.10.1 事務(wù)的基本概念事務(wù)具有以下幾個(gè)屬性：原子性 每一個(gè)事務(wù)是一個(gè)工作單元。它不能被分割成更小的部分。這個(gè)屬性意味著在事務(wù)中定義的一切數(shù)據(jù)更改要么都完成，要么都不完成。 一致性 一個(gè)事務(wù)不能違背定義在數(shù)據(jù)庫(kù)中的任何完整性檢查。為了維護(hù)一致性，所有的規(guī)則、約束、檢查和觸發(fā)都會(huì)應(yīng)用在事務(wù)中。由于所有的數(shù)據(jù)更改在事務(wù)期間內(nèi)進(jìn)行，這些數(shù)據(jù)在事務(wù)開(kāi)始和事務(wù)結(jié)束前會(huì)被確保為一致的。隔離性 事務(wù)必須與其他事務(wù)進(jìn)行的數(shù)據(jù)更改相隔離。這意味著沒(méi)有其他操作可以改變中間態(tài)(沒(méi)有提交的)的數(shù)據(jù)。為了避免中間態(tài)數(shù)據(jù)被更改，事務(wù)必須要么等待來(lái)

45、自其他事務(wù)的更改被提交，要么只能查看到處于上一個(gè)提交狀態(tài)的數(shù)據(jù)。持久性 在一個(gè)事務(wù)完成，并且客戶(hù)端應(yīng)用程序已經(jīng)被提示這個(gè)事務(wù)已經(jīng)成功完成后，無(wú)論發(fā)生任何系統(tǒng)錯(cuò)誤，這些更改的數(shù)據(jù)將永久存在。第七十二張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.10.1 事務(wù)的基本概念SQL Server數(shù)據(jù)庫(kù)引擎會(huì)通過(guò)事務(wù)日志強(qiáng)制執(zhí)行事務(wù)的物理一致性，并且保證事務(wù)的持久性。SQL Server還會(huì)強(qiáng)制對(duì)約束、數(shù)據(jù)類(lèi)型以及其他內(nèi)容執(zhí)行一切一致性檢查以確保邏輯上的一致性。這都由SQL Server自動(dòng)地強(qiáng)制執(zhí)行。但是，諸如轉(zhuǎn)賬之類(lèi)的業(yè)務(wù)處理，還必須由開(kāi)發(fā)人員來(lái)自己定義事務(wù)。定義一個(gè)事務(wù)時(shí)需要考慮以下方面：定義事務(wù)的

46、外延。開(kāi)發(fā)者必須定義事務(wù)從哪里開(kāi)始，到哪里結(jié)束。一個(gè)事務(wù)總應(yīng)該越短越好，但是同時(shí)要做到盡量符合實(shí)際業(yè)務(wù)過(guò)程的需要。定義錯(cuò)誤管理。在事務(wù)中并不是所有的錯(cuò)誤都會(huì)自動(dòng)回滾。開(kāi)發(fā)者有責(zé)任實(shí)現(xiàn)錯(cuò)誤管理。定義隔離級(jí)別。隔離不同的事務(wù)總是會(huì)有與并行性相關(guān)的缺陷。如果完全地隔離事務(wù)并且其它事務(wù)希望讀取相同但不為前一狀態(tài)的數(shù)據(jù)，那么它會(huì)被阻塞直到您結(jié)束您的事務(wù)。這對(duì)于有許多并行連接的數(shù)據(jù)庫(kù)系統(tǒng)來(lái)說(shuō)是一個(gè)嚴(yán)重問(wèn)題。SQL Server實(shí)現(xiàn)了一些不同種類(lèi)的、需要正確選擇的隔離級(jí)別。一般規(guī)則是選擇在盡可能短的時(shí)間內(nèi)鎖住盡可能少的數(shù)據(jù)，并可以提供需要的事務(wù)安全性的隔離級(jí)別。第七十三張，PPT共七十九頁(yè)，創(chuàng)作于2022年6月5.10.2 自動(dòng)提交事務(wù)自動(dòng)提交事務(wù)模式是SQ