《基于C＃的SQL Server應用技術》課件第3章

工作任務1查詢數據工作任務2更新數據情境總結

練習題

子任務1簡單查詢

查詢是對存儲于SQLServer2008中的數據的請求，通過查詢用戶可以獲得所需要的數據。查詢可以通過執(zhí)行SELECT語句實現，也可通過圖形界面實現，但它們最后都要將每個查詢轉換成SELECT語句，然后發(fā)送到SQLServer服務窗口執(zhí)行。本任務將介紹簡單查詢的一些基本用法。工作任務1查詢數據

1．SELECT語句的基本語法

2．SELECT語句的執(zhí)行方式

數據的查詢可以在SQLServer管理平臺中執(zhí)行，也可以在查詢分析器中執(zhí)行，具體方法如下。

1)在SQLServer管理平臺中執(zhí)行SELECT語句

系統(tǒng)將打開如圖3-1所示的窗口。圖3-1查詢分析器窗口

3．SELECT子句

4．FROM子句

FROM子句指定需要進行數據查詢的表。只要SELECT子句中有要查詢的列，就必須使用FROM子句。其最常用語法如下：

FROM{<源表>}[,…n]

源表：指明SELECT語句要用到的表、視圖等數據源。

5．WHERE子句

WHERE子句是篩選條件，它定義了源表中的行要滿足SELECT語句的要求所必須達到的條件。只有符合條件的行才向結果集提供數據，不符合條件的行中的數據不會被使用。

在WHERE子句使用的條件如表3-1所示。表3-1常用的查詢條件

【任務1】在“學生信息表”中查詢所有學生的學號，姓名。

操作步驟如下：

①在查詢窗口中輸入以下命令文本：

SELECT學號,姓名

FROM學生信息表

②單擊【執(zhí)行】按鈕，得到結果如圖3-2所示。圖3-2查詢所有學生的學號、姓名

【任務2】為結果集內的列指定別名：在“班級管理系統(tǒng)”數據庫中將“成績信息表”中的成績打四折。

操作步驟如下：

①在查詢窗口中輸入以下命令文本：

SELECT學號,成績,成績*0.4AS平時成績

FROM成績信息表

②單擊【執(zhí)行】按鈕，得到結果如圖3-3所示。圖3-3帶有別名的查詢

【任務3】

用DISTINCT消除結果集中重復的記錄：查詢“成績信息表”中的“學號”、“成績”的信息。

操作步驟如下：

①在查詢窗口中輸入以下命令文本：

SELECTDISTINCT學號,成績

FROM成績信息表

②單擊【執(zhí)行】按鈕，得到結果如圖3-4所示。圖3-4使用DISTINCT篩選的結果

【任務4】使用TOP顯示前面有限條記錄：查詢“學生信息表”中的前5條記錄，字段為學號和姓名。

操作步驟如下：

①在查詢窗口中輸入以下命令文本：

SELECTTOP5學號,姓名

FROM學生信息表

②單擊【執(zhí)行】按鈕，得到結果如圖3-5所示。圖3-5使用TOP語句

【任務5】FROM子句應用：在“班級管理系統(tǒng)”數據庫中查詢所有學生的學號，姓名，成績相關信息。

操作步驟如下：

①在查詢窗口中輸入以下命令文本：

SELECT姓名,課程編號,成績

FROM學生信息表XSJOIN成績信息表CJONXS.學號=CJ.學號

②單擊【執(zhí)行】按鈕，得到結果如圖3-6所示。圖3-6FROM子句應用

【任務6】比較大?。涸凇皩W生信息表”中查詢“系別”為“計算機”的記錄。

操作步驟如下：

①在查詢窗口中輸入以下命令文本：

SELECT 學號,姓名,系別

FROM 學生信息表

WHERE (系別=‘計算機系’)

②單擊【執(zhí)行】按鈕，得到結果如圖3-7所示。圖3-7查詢“系別”為“計算機”的記錄

【任務7】在“成績信息表”中，查詢“成績”在80～90之間的成績，要求只顯示課程編號和成績字段。

操作步驟如下：

①在查詢窗口中輸入以下命令文本：

SELECT課程編號,成績

FROM成績信息表

WHERE成績BETWEEN80AND90

②單擊【執(zhí)行】按鈕，得到結果如圖3-8所示。圖3-8使用BETWEEN…AND

【任務8】在“成績信息表”中，查詢“成績”不在80～90之間的成績，要求只顯示課程編號和成績字段。

操作步驟如下：

①在查詢窗口中輸入以下命令文本：

SELECT 課程編號,成績

FROM 成績信息表

WHERE 成績NOTBETWEEN80AND90

②單擊【執(zhí)行】按鈕，得到結果如圖3-9所示。圖3-9使用NOTBETWEEN…AND

【任務9】確定集合：在“學生信息表”中，查詢“系別”為“計算機系”和“會計系”的學生，字段包括“學號”、“姓名”、“系別”。

操作步驟如下：

①在查詢窗口中輸入以下命令文本：

SELECT學號,姓名,系別

FROM學生信息表

WHERE系別IN(‘計算機系’,‘會計系’)

②單擊【執(zhí)行】按鈕，得到結果如圖3-10所示。圖3-10使用IN篩選的結果

【任務10】

字符匹配：查詢學生信息表中姓“張”的學生信息。

操作步驟如下：

①在查詢窗口中輸入以下命令文本：

SELECT 學號,姓名,系別

FROM 學生信息表

WHERE 姓名LIKE‘張%’

②單擊【執(zhí)行】按鈕，得到結果如圖3-11所示。圖3-11模糊查詢

【任務11】涉及空值的查詢：

在“班級管理系統(tǒng)”數據庫中查詢所有電話為空的學生信息。

操作步驟如下：

①在查詢窗口中輸入以下命令文本：

SELECT*

FROM學生信息表

WHERE電話ISNULL

②單擊【執(zhí)行】按鈕，得到結果如圖3-12所示。圖3-12查詢空值

【任務12】

多重條件查詢：在“班級管理系統(tǒng)信息表”中，查詢機電系的姓張的學生信息。要求輸出字段為學號、姓名、系別。

操作步驟如下：

①在查詢窗口中輸入以下命令文本：

SELECT學號,姓名,系別

FROM學生信息表

WHERE系別=‘機電系’

AND姓名LIKE‘張%’

②單擊【執(zhí)行】按鈕，得到結果如圖3-13所示。圖3-13多重條件查詢

【任務13】ORDERBY子句：將“成績信息表”按成績排序，輸出有關字段。

操作步驟如下：

①在查詢窗口中輸入以下命令文本：

SELECT學號,成績

FROM成績信息表

ORDERBY成績

②單擊【執(zhí)行】按鈕，得到結果如圖3-14所示。圖3-14使用ORDERBY子句篩選的結果子任務2分類匯總

用戶經常需要對結果集進行統(tǒng)計，例如，求和、平均值、最大值、最小值、個數等，這些統(tǒng)計可以通過集合函數、COMPUTE子句、GROUPBY子句來實現。本節(jié)將詳細介紹這些常用函數、COMPUTE子句和GROUPBY子句的用法。

【任務1】SUM函數應用：計算“成績信息表”中某個學生的成績總和。

操作步驟如下：

①在查詢窗口中輸入以下命令文本：

SELECTSUM(成績)AS成績總和

FROM成績信息表

WHERE學號=‘2007110101’

②單擊【執(zhí)行】按鈕，得到結果如圖3-15所示。圖3-15使用SUM()函數

【任務2】AVG函數應用：計算“成績信息表”中某個學生的平均成績。

操作步驟如下：

①在查詢窗口中輸入以下命令文本：

SELECTAVG(成績)AS平均成績

FROM成績信息表

WHERE學號=‘2007110101’

②單擊【執(zhí)行】按鈕，得到結果如圖3-16所示。圖3-16使用AVG()函數

【任務3】COUNT函數應用：統(tǒng)計“學生信息表”中的學生總數。

操作步驟如下：

①在查詢窗口中輸入以下命令文本：

SELECTCOUNT(*)AS學生總數

FROM學生信息表

②單擊【執(zhí)行】按鈕，得到結果如圖3-17所示。圖3-17使用COUNT()函數

【任務4】

用GROUPBY分組：查詢“學生信息表”中各系學生的個數。

操作步驟如下：

①在查詢窗口中輸入以下命令文本：

SELECT系別，COUNT(*)AS數量

FROM學生信息表

GROUPBY系別

②單擊【執(zhí)行】按鈕，得到結果如圖3-18所示。圖3-18分組統(tǒng)計篩選結果

【任務5】在GROUPBY中使用HAVING子句：在“成績信息表”中，找出所有學生的平均成績大于80的信息。

操作步驟如下：

①在查詢窗口中輸入以下命令文本：

SELECT學號,AVG(成績)AS平均成績

FROM成績信息表

GROUPBY學號

HAVINGAVG(成績)>80

②單擊【執(zhí)行】按鈕，得到結果如圖3-19所示。圖3-19分組統(tǒng)計篩選結果

【任務6】COMPUTE子句應用：在“成績信息表”中，檢索“學號”為“2007110101”的記錄，并求出平均成績、最低成績、最高成績。

操作步驟如下：

①在查詢窗口中輸入以下命令文本：

SELECT學號,課程編號，成績

FROM成績信息表

WHERE學號=‘2007110101’

COMPUTEAVG(成績),MAX(成績),MIN(成績)

②單擊【執(zhí)行】按鈕，得到結果如圖3-20所示。圖3-20分組統(tǒng)計篩選結果

【任務7】COMPUTEBY子句應用：從“成績信息表”中檢索數據，列出每個學生的成績以及每個學生的平均成績、最低成績、最高成績。

操作步驟如下：

①在查詢窗口中輸入以下命令文本：

SELECT學號,課程編號,成績

FROM成績信息表

ORDERBY學號

COMPUTEAVG(成績),MAX(成績),MIN(成績)

BY學號

②單擊【執(zhí)行】按鈕，得到結果如圖3-21所示。圖3-21分組統(tǒng)計篩選結果子任務3聯(lián)接

若一個查詢同時涉及多個表，則稱之為聯(lián)接查詢。聯(lián)接查詢是關系數據庫中最重要的查詢，它包括內聯(lián)接、外聯(lián)接、交叉聯(lián)接、自聯(lián)接、多表聯(lián)接等。1．內聯(lián)接

2．外聯(lián)接

1)左外聯(lián)接

2)右外聯(lián)接

3)全聯(lián)接

3．交叉聯(lián)接

4．自聯(lián)接

【任務1】

內聯(lián)接：查詢所有課程信息和選課學生的成績。

操作步驟如下：

①在查詢窗口中輸入以下命令文本：

SELECTCJ.*,KC.*

FROM成績信息表CJJOIN課程信息表KC

ONCJ.課程編號=KC.課程編號

②單擊【執(zhí)行】按鈕，得到結果如圖3-22所示。圖3-22使用SQL語句創(chuàng)建視圖

【任務2】左外聯(lián)接：查詢所有學生的信息和選課學生的情況。

操作步驟如下：

①在查詢窗口中輸入以下命令文本：

SELECT姓名,性別,課程編號,成績

FROM學生信息表XSLEFTJOIN成績信息表CJ

ONXS.學號=CJ.學號

②單擊【執(zhí)行】按鈕，得到結果如圖3-23所示。圖3-23左外聯(lián)接

【任務3】交叉聯(lián)接：查找所有學生選課的可能情況；得到的結果集的行數是兩個表的行數的乘積。

操作步驟如下：

①在查詢窗口中輸入以下命令文本：

SELECT姓名,性別,課程編號,課程名稱

FROM學生信息表CROSSJOIN課程信息表

ORDERBY課程編號

②單擊【執(zhí)行】按鈕，得到結果如圖3-24所示。圖3-24交叉聯(lián)接

【任務4】

自聯(lián)接：查找不同課程成績相同的學生的學號、課程編號和成績。

操作步驟如下：

①在查詢窗口中輸入以下命令文本：

SELECTCJ1.學號,CJ1.課程編號,CJ1.成績

FROM成績信息表CJ1JOIN成績信息表CJ2

ONCJ1.成績=CJ2.成績

WHERECJ1.課程編號!=CJ2.課程編號

ANDCJ1.學號!=CJ2.學號

②單擊【執(zhí)行】按鈕，得到結果如圖3-25所示。圖3-25自身聯(lián)接查詢子任務4實現嵌套查詢

所謂子查詢，是指包含在某一個SELECT、INSERT、UPDATE或DELETE命令中的SELECT查詢。在SELECT、INSERT、UPDATE或DELETE命令中允許是一個表達式的地方均可以使用子查詢。當從表中選取數據行的條件依賴于該表本身或其他表的聯(lián)合信息時，需要使用子查詢來實現。子查詢也稱為內部查詢，而包含子查詢的語句稱為外部查詢。

1．子查詢基礎

2．非相關子查詢(不依賴于外部查詢的子查詢)

ANY或ALL與比較運算符一起使用的語義如表3-2所示。表3-2ANY或ALL與比較運算符一起使用的語義

3．相關子查詢

在相關子查詢中，子查詢的執(zhí)行依賴于外部查詢，多數情況下是在子查詢的WHERE子句中引用了外部查詢的表。

4．帶EXISTS測試的子查詢

【任務1】返回單個值：查詢“成績信息表”中，所有成績低于平均成績的學生。

操作步驟如下：

①在查詢窗口中輸入以下命令文本：

SELECT學號,課程編號,成績

FROM成績信息表

WHERE成績<(SELECTAVG(成績)

AS平均成績FROM成績信息表)

②單擊【執(zhí)行】按鈕，得到結果如圖3-26所示。圖3-26返回單個值

【任務2】返回一組值：在“成績信息表”中，找出某個學生的成績比另外一個學生的最高成績還高的學生成績信息。

操作步驟如下：

①在查詢窗口中輸入以下命令文本：

SELECT學號,課程編號,成績

FROM成績信息表

WHERE學號=‘2007110101’AND成績>ALL

(SELECT成績FROM成績信息表

WHERE學號=‘2007110102’)

②單擊【執(zhí)行】按鈕，得到結果如圖3-27所示。圖3-27返回一組值

【任務3】相關子查詢：查詢“成績信息表”中大于該課程平均值的學生成績信息。

操作步驟如下：

①在查詢窗口中輸入以下命令文本：

SELECT學號,課程編號,成績

FROM成績信息表ASCJ1

WHERE成績>

(SELECTAVG(成績)

FROM成績信息表ASCJ2

WHERECJ1.課程編號=CJ2.課程編號)

②單擊【執(zhí)行】按鈕，得到結果如圖3-28所示。圖3-28相關子查詢

【任務4】帶EXISTS測試的子查詢：利用EXISTS查詢所有成績記載的信息。

操作步驟如下：

①在查詢窗口中輸入以下命令文本：

SELECT學號,課程編號,成績

FROM成績信息表

WHEREEXISTS

(SELECT學號

FROM學生信息表

WHERE成績信息表.學號=學生信息表.學號AND系別=‘計算機系’)

②單擊【執(zhí)行】按鈕，得到結果如圖3-29所示。圖3-29帶EXISTS測試的子查詢子任務1增加記錄

一個表建立以后，就可以向表中添加數據。在前面的學習情境中實現了通過管理平臺向表中添加數據。在本工作任務中將使用INSERT語句插入數據以及用INSERT…SELECT語句添加來自另外一個表中的數據。工作任務2更新數據1．使用INSERT語句插入新記錄

1)給插入記錄的所有字段添加數據

2)給插入記錄的部分字段添加數據

3)給插入的記錄使用默認值添加數據

2．使用INSERT…SELECT語句插入新記錄

【任務1】給插入記錄的所有字段添加數據：在“班級管理系統(tǒng)”數據庫的“學生信息表”中添加一條記錄，其中，學號：2009430102，姓名：張子瑤，性別：女，出生年月日：1990-6-3，班級：094301，電話：2754635，入學時間：2009-9-1，系別：機電系。添加新記錄之后，顯示“學生信息表”的全部記錄。

操作步驟如下：

①在查詢窗口中輸入以下命令文本：

INSERTINTO學生信息表

VALUES(‘2009430102’,‘張子瑤’,

‘女’,‘1990-6-3’,‘094301’,‘2754635’,‘2009-9-1’,‘機電系’,‘’)

SELECT*FROM學生信息表

②單擊【執(zhí)行】按鈕，得到結果如圖3-30所示。圖3-30給插入記錄的所有字段添加數據

【任務2】

給插入記錄的部分字段添加數據：在“學生信息表”中添加一條記錄，其中，學號：2008310204,姓名：張凱,性別：男，系別：計算機系。

操作步驟如下：

①在查詢窗口中輸入以下命令文本：

INSERTINTO學生信息表(學號,姓名,性別,系別)

VALUES(‘2008310204’,‘張凱’,‘男’,‘計算機系’)

SELECT*FROM學生信息表

②單擊【執(zhí)行】按鈕，得到結果如圖3-31所示。圖3-31給插入記錄的部分字段添加數據

【任務3】用INSERT…SELECT語句插入新記錄：利用已有表“學生信息表”創(chuàng)建一個表，表名為A，包括字段：學號、姓名、性別、系別。再將“學生信息表”中“系別”為計算機系的學生信息插入A表中，并顯示A表內容。

操作步驟如下：

①在查詢窗口中輸入以下命令文本：

SELECT學號,姓名,性別,系別

INTOA

FROM學生信息表

WHERE系別=‘計算機系’

SELECT*

FROMA

②單擊【執(zhí)行】按鈕，得到結果如圖3-32所示。圖3-32利用已有表創(chuàng)建一個表子任務2修改記錄

在查詢分析器窗口中掌握用命令方式修改數據，可以使用UPDATE語句對表中的數據進行修改，也可以使用FROM子句對UPDATE語句進行擴展，以便從一個或多個已經存在的表中獲取修改時需要用到的數據。1．使用UPDATE語句修改記錄

2．使用FROM子句擴展UPDATE語句

【任務1】

修改記錄：將“成績信息表”中的“課程編號”為“303”的學生成績在原成績的基礎上增加5分。

操作步驟如下：

①在查詢窗口中輸入以下命令文本：

UPDATE成績信息表

SET成績=成績+5

WHERE課程編號=‘303’

SELECT*FROM成績信息表

WHERE課程編號=‘303’

②單擊【執(zhí)行】按鈕，得到結果如圖3-33所示。圖3-33插入新記錄

【任務2】用FROM子句擴展UPDATE語句：將“成績信息表”中是“計算機系”的學生的成績增加5分。

操作步驟如下：

①在查詢窗口中輸入以下命令文本：

UPDATE成績信息表

SET成績=成績+5

FROM成績信息表ASa,學生信息表ASb

WHEREa.學號=b.學號AND系別=‘計算機系’

②單擊【執(zhí)行】按鈕，得到結果如圖3-34所示。圖3-34擴展UPDATE語句子任務3刪除記錄

1．使用DELETE語句刪除表中的指定記錄

2．使用TRUNCATETABLE語句刪除表中所有記錄

【任務1】

用DELETE語句刪除表中的指定記錄：從“學生信息表”中刪除會計系的學生，并顯示結果。

操作步驟如下：

①在查詢窗口中輸入以下命令文本：

USE班級管理系統(tǒng)

DELETE學生信息表

WHERE系別=‘會計’

②單擊【執(zhí)行】按鈕，得到結果如圖3-35所示。圖3-35用DELETE語句刪除表中的指定記錄

【任務2】

使用TRUNCATE語句刪除數據