實驗三數(shù)據(jù)庫的查詢和視圖

1、實驗三 數(shù)據(jù)庫的查詢和視圖T4.1 數(shù)據(jù)庫的查詢1、目的和要求（1）掌握SELECT語句的基本語法。（2）掌握子查詢的表示。（3）掌握連接查詢的表示。（4）掌握SELECT語句的GROUP BY子句的作用和使用方法。（5）掌握SELECT語句的ORDER BY子句的作用和使用方法。2、實驗準備（1）了解SELECT語句的基本語法格式。（2）了解SELECT語句的執(zhí)行方法。（3）了解子查詢的表示方法。（4）了解連接查詢的表示。（5）了解SELECT語句的GROUP BY子句的作用和使用方法。（6）了解 SELECT語句的ORDER BY子句的作用。3、實驗內(nèi)容（1）SELECT語句的基本使用。

2、對于實驗二給出的數(shù)據(jù)庫表結構，查詢每個雇員的所有數(shù)據(jù)。新建一個查詢，在“查詢分析器”窗口中輸入如下語句并執(zhí)行。單擊右鍵空白處，選擇執(zhí)行，在下方會出現(xiàn)所有結果?！舅伎寂c練習】用SELECT語句查詢Departments表和Salary表中所有信息。新建一個查詢，在“查詢分析器”窗口中輸入如下語句并執(zhí)行。單擊右鍵空白處，選擇執(zhí)行，在下方會出現(xiàn)所有結果。在“消息”中會發(fā)現(xiàn)總共有60行受影響。 用SECECT語句查詢Employees表中每個雇員的地址和電話號碼。新建一個查詢，在“查詢分析器”窗口中輸入如下代碼并執(zhí)行。單擊右鍵空白處，選擇執(zhí)行，在下方會出現(xiàn)所有結果。在“消息”中會發(fā)現(xiàn)有12行記錄受影響

3、【思考與練習】 A、用SELECT語句查詢Departments表和Salary表的一列或若干行。利用SELECT語句查詢Departments表和Salary表中 EmployeeID為000001的記錄，在“查詢分析器”中輸入以下代碼。執(zhí)行后在下方會出現(xiàn)所有EmployeeID=000001的記錄B、查詢Employees表中部門號和性別 ，要求使用DISTINCT消除重復行。對表只選擇某些行時，可能會出現(xiàn)重復行。此時就需要使用DISTINCT關鍵字消除結果中的重復行。此時使用DISTINCT消除在Employees中查詢到的部門號和性別的重復行，具體代碼如下。 上面代碼分別是使用了DIS

4、TINCT和沒有使用DISTINCT關鍵字，下面就是兩種的結果的對比。 可以從上面的執(zhí)行結果中看出使用了DISTINCT關鍵字，結果集中的重復行就被消除了。 查詢EmployeeID為000001的雇員的地址和電話。 鼠標右鍵單擊在彈出的菜單窗口中選擇“執(zhí)行”，結果中只會出現(xiàn)一行記錄，EmployeeID為000001的雇員的地址和電話?！舅伎寂c練習】 A、查詢月收入高于2000元的員工號碼。只需要查詢員工號碼，條件為月收入高于2000元員工，那么至于要Salary表就行。用T-SQL命令在表Salary表中查詢月收入高于2000元的員工代碼如下。 在結果欄中顯示(9 行受影響)，說明月收入高

5、于2000的員工共有9人，如上截圖所示，將所有月收入高于2000的員工的員工號碼輸出。B、查詢1970年以后出生的員工的姓名和地址。通過分析員工信息，跟員工年齡、姓名、地址相關的只有表Employees表，所以只需要在一張表中查詢即可，具體T-SQL查詢命令如下。 在結果欄中消息顯示(7 行受影響)，說明1970年以后出生有7人，如上截圖所示，將部分1970年以后出生員工姓名、地址輸出顯示。C、查詢所有財務部的員工的號碼和姓名。通過分析的，需要知道財務部與員工的號碼和姓名，財務部只存在于Departments表中，員工姓名和號碼只存在于Employees表中。因此需要在表Departments

6、表和Employees表中進行查詢。利用T-SQL命令查詢代碼如下。 在結果欄中顯示3行受影響，從上面截圖中可以看出所有財務部員工有3人，并且查詢顯示其姓名、電話號碼。 查詢Employees表中女雇員的地址和電話，使用AS子句將結果中各列的標題分別指定為地址、電話。 從上面結果的截圖發(fā)現(xiàn)Address變?yōu)榈刂?，PhoneNumber變?yōu)殡娫挕！舅伎寂c練習】查詢Employees表中男員工的姓名和出生日期，要求各列標題用中文表示。通過分析此查詢屬于單表查詢，只需要用到Employees表，使用AS子句就能夠使結果集中各列標題用中文顯示。利用T-SQL命令查詢代碼如下。 從上面結果的截圖發(fā)現(xiàn)各列

7、標題都是中文。從消息中顯示(9 行受影響)說明，員工中男性有9人，并且全部男性的姓名和出生日期都已經(jīng)查詢顯示出。 查詢Employees表中員工的姓名和性別，要求Sex值為1時顯示為“男”，為0時顯示為“女”。 【思考與練習】查詢Employees員工的姓名、住址和收入水平，2000元以下顯示為低收入，20003000元顯示為中等收入，3000元以上顯示高收入。經(jīng)過分析需要查詢員工的姓名、住址和收入水平，需要在Employees表和Salary表中進行查詢，并且將Salary表中員工的InCome進行分類需要用到AS子句。利用T-SQL命令進行多表查詢語句如下。執(zhí)行上面截圖中代碼，在結果欄中出

8、現(xiàn)員工的姓名，住址和收入水平。消息中顯示12行受影響，Employees表中所有員工信息都已經(jīng)查詢輸出。 計算每個雇員的實際收入 。經(jīng)過分析需要員工的收入以及員工號碼和員工姓名，就需要在Employees表和Salary表中進行查詢。利用T-SQL命令查詢?nèi)缦?。結果欄中就會顯示EmployeeID，Name，實際收入這三項。注意：在上面T-SQL命令中EmployeeID如果不指明為哪個表中的EmployeeID，代碼就會報錯“EmployeeID不明確”，改為Employees.EmployeeID或者Salary.EmployeeID都行?！舅伎寂c練習】使用SELECT語句進行簡單的計算。

9、在這里我將使用SELECT語句計算出Employees表中工齡三年以上的人數(shù)，并且查詢員工號碼，姓名，工齡。 獲得員工總數(shù)。 經(jīng)分析得需要得到員工總數(shù)，只需要在Employees表內(nèi)使用內(nèi)置函數(shù)COUNT就行。利用T-SQL命令執(zhí)行操作如下。【思考與練習】A、 計算Salary表中員工月收入的平均數(shù)。B、 獲得Employees表中最大的員工號碼。C、 計算Salary表中所有員工的總支出。D、 查詢財務部雇員的最高和最低實際收入。經(jīng)過分析需要查詢財務部雇員的最高和最低實際收入，涉及到Employees表、Departments表和Salary表，需要在三張表中進行查詢。首先確定Departm

10、entName為“財務部”的DepartmentID。其次在Employees表中確定DepartmentID為財務部的雇員的EmployeeID。最后在Salary表中通過EmployeeID查詢財務部雇員的InCome和OutCome，在最后的輸出時候用 InCome-OutCome的結果為實際收入。執(zhí)行后在結果欄中看到財務部雇員的最高實際收入和最低實際收入。 找出所有姓王的雇員的部門號。 【思考與練習】A、 找出所有地址中含有“中山”的雇員的號碼及部門號。 注意：%代表著任意長度的字符串（長度可以為0），“%中山%”表示“中山”前面和后面都能夠有任意長度字符串，用來表示地址中含有“中山”

11、二字。B、 查找員工號碼中倒數(shù)第2個數(shù)字為0的員工的姓名、地址和學歷。 注意：數(shù)字占單個字符，所以通配符“_”只有一個，“%0_”表示倒數(shù)第二位為0。 找出所有收入在20003000元的員工號碼。 【思考與練習】找出所有在部門“1”或“2”工作的雇員的號碼。 10 使用INTO子句，由表Salary創(chuàng)建“收入在1500元以上的員工”表，包括編號和收入。在左邊對象資源管理器的YGGL數(shù)據(jù)庫的表中刷新，會發(fā)現(xiàn)多了表“收入在1500元以上的員工”?！舅伎寂c練習】使用INTO子句，由表Employees創(chuàng)建“男員工”表，包括編號和姓名。 （2）子查詢的使用。 查找在財務部工作的雇員的情況。查詢財務部工

12、作的雇員情況，需要用到Department表和Employees表進行多表查詢。首先，在Department表中查詢DepartmentName為“財務部”的DepartmentID。然后，將DepartmentID拿到表Employees表中查詢所有DepartmentID為財務部的雇員的所有信息。利用T-SQL命令查詢代碼如下。 在結果欄中顯示所有工作部門為“財務部”的雇員的詳細信息?！舅伎寂c練習】用子查詢的方法查找所有收入在2500元以下的雇員的情況。進分析需要用到工資表Salary和員工信息表Employees進行查詢。首先，在表Salary中查詢InComeall”查詢到的是“李麗”

13、，年齡小于所有研發(fā)部的，所有書本上源碼表示的是“財務部雇員年齡不大于研發(fā)部雇員年齡”與題目中“不低于”沖突。所以書上源碼有誤，將源碼改成上面截圖中第12行即可。【思考與練習】用子查詢的方法查找研發(fā)部比所有財務部雇員收入都高的雇員的姓名。分析：首先在Employees表里找到EmployeeName為“研發(fā)部”的雇員的姓名和查找雇員的EmployeeID，其次這樣的EmployeeID還需要滿足InCome大于所有的DepartmentName為“財務部”的雇員的InCome，滿足條件返回True，select到滿足這樣條件的雇員的Name，否則返回FALSE。Where子查詢中嵌套使用。執(zhí)行T

14、-SQL命令后在結果欄中顯示只有1人滿足。 （3）連接查詢的使用。 查詢每個雇員的情況及其薪水情況。執(zhí)行之后，將Employees表和Salary表中所有員工的基本情況進行輸出。不足之處就是EmployeeID這一列有重復?！舅伎寂c練習】查詢每個雇員的情況及其工作部門的情況。執(zhí)行后將每個雇員的基本情況、工資情況、工作部門情況輸出。 使用內(nèi)連接的方法查詢名字為“王林”的員工的所在部門。 【思考與練習】A、 使用內(nèi)連接方法查找出不在財務部工作的所有員工信息。內(nèi)連接是指定了關鍵字INNER的連接，內(nèi)連接按照ON所指定的連接條件合并兩個表，返回滿足條件的行。此處INNER關鍵字能夠省略。查找所有不在財

15、務部工作的員工的信息，就需要使用員工基本信息表Employees和部門信息表Department進行查詢。T-SQL命令語句如下。執(zhí)行結果包含表Employees和表Department的所有字段，不去除重復字段（DepartmentID）。B、 使用外連接方法查找出所有員工的月收入。外連接為指定了關鍵字OUTER關鍵字的連接，外連接包括以下三種連接。左外連接（LEFT OUTER JOIN）：結果表中除了包括滿足連接條件的行外，還包括左表的所有行。右外連接（RIGHT OUTER JOIN）：結果表中除了包括滿足連接條件的行外，還包括右表的所有行。完全外連接 （FULL OUTER JOIN

16、）：結果表中除了包括滿足連接條件的行外，還包括兩個表的所有行。在這里將表Employees和表Salary做右連接查詢，結果表中除了包括連接條件Employees.EmployeeID=Salary.EmployeeID外，還將包括右表（Salary表）中的所有行（InCome和OutCome）。執(zhí)行代碼結果如下。 查找財務部收入在2000元以上的雇員姓名及其薪水情況。分析得需要財務部收入2000元以上雇員姓名和薪水情況，涉及雇員基本信息的Employees表、部門信息Department表和薪水信息Salary表。此時就用到where語句里面多表查詢將三張表做自然連接。執(zhí)行上面截圖中代碼，在

17、下面結果欄中顯示財務部工資在2000元以上的雇員的姓名和薪水情況?！舅伎寂c練習】查詢研發(fā)部在1976年以前出生的雇員姓名及其薪水情況。將三張表做自然連接后查詢滿足條件的雇員的姓名、收入和支出。（4）聚合函數(shù)的使用 求財務部雇員的平均收入。新建一個查詢，在“查詢分析器”窗口中輸入如下語句并執(zhí)行。執(zhí)行截圖中代碼，結果欄中顯示才而不平均收入。【思考與練習】查詢財務部雇員的最高收入和最低收入。查詢最高和最低收入，將用到聚合函數(shù)表中的MAX（求最大值）和MIN（求最小值）。執(zhí)行上面截圖中T-SQL命令，在結果欄中顯示財務部雇員最高收入和最低收入。 求財務部雇員的平均實際收入。 新建一個查詢，在“查詢分析

18、器”窗口中輸入如下語句并執(zhí)行。執(zhí)行上面截圖中的T-SQL命令，在結果欄中顯示財務部雇員平均實際收入。【思考與練習】查詢財務部雇員的最高和最低實際收入。需要財務部實際收入需要用表Salary中的InCome字段和OutCome字段。其次用到聚合函數(shù)MAX和MIN。最后在用where子查詢找到DepartmentName為“財務部”的雇員的EmployeeID。執(zhí)行上面截圖中的T-SQL命令，在結果欄中顯示財務部雇員最高和最低實際收入。（5）GROUP BY、ORDER BY子句的使用。 查找Employees表中男性和女性人數(shù) 。 【思考與練習】A、按部門列出在該部門工作的員工的人數(shù)。 B、按員

19、工的學歷分組，排列出本科、大專和碩士的人數(shù)。 查找員工數(shù)超過2的部門名稱和員工數(shù)量。執(zhí)行上面截圖中代碼，在結果欄中顯示員工數(shù)超過2的部門名稱以及人數(shù)?！舅伎寂c練習】按照員工的工作年份分組，統(tǒng)計各個工作年份的人數(shù)，例如，工作1年的多少人，工作2年的多少人 。分析只需要Employees表就能夠完成，通過GROUP BY對WorkYear進行分組，并統(tǒng)各個年份的人數(shù)。 將各個雇員的情況按收入由低到高排列。 新建一個查詢，在“查詢分析器”窗口中輸入如下語句并執(zhí)行。需要對雇員的收入情況進行排序，就需要使用到謂詞ORDER BY，謂詞ORDER BY后面能夠接多個排序的變量名，第一個變量為主序，下面依次

20、類推。每一個排序列名后面可用限定詞ASC（升序）或者DESC（降序）聲明排序的方式 ，缺省為升序（如果不接限定詞系統(tǒng)默認的為ASC（升序）。下面為執(zhí)行上面截圖中T-SQL命令，在結果欄中顯示的查詢結果?？梢杂^察雇員的基本信息是按照InCome升序排列（由低到高）?！舅伎寂c練習】A、將員工信息按照出生時間從小到大排列。執(zhí)行上面截圖中的T-SQL命令，在下面結果欄中顯示雇員信息根據(jù)出生日期從小到大排列。C、在ODER BY子句中使用子查詢，查詢員工姓名，性別和工齡信息，要求按照實際收入從大到小排序。經(jīng)分析將表Employees和表Salary根據(jù)EmployeeID相同做自然連接，其次根據(jù)實際收入

21、（InCome-OutCome）進行ORDER BY排序。最后，由于是根據(jù)實際收入（InCome-OutCome）從大到小進行排序，那么接在ORDER BY后面的謂詞就需要換成DESC。根據(jù)上面截圖中T-SQL命令，在結果欄中顯示根據(jù)實際收入進行排序輸出雇員的姓名，性別和工齡信息。T4.1 視圖的使用1、目的和要求（1）熟悉視圖的概念和作用。（2）掌握視圖的創(chuàng)建方法。（3）掌握如何查詢和修改視圖。2、實驗準備（1）了解視圖的概念（2）了解創(chuàng)建視圖的方法。（3）了解并掌握對視圖的操作。3、實驗內(nèi)容（1）創(chuàng)建視圖。 創(chuàng)建YGGL數(shù)據(jù)庫上的視圖DS_VIEW，視圖包含Departments表的全部列

22、。新建“查詢分析器”窗口，在其中輸入以上T-SQL命令，點擊執(zhí)行按鈕。在YGGL數(shù)據(jù)庫的視圖中進行刷新，就會顯示創(chuàng)建的DS_VIEW視圖，如下所示。 創(chuàng)建YGGL數(shù)據(jù)庫上的視圖Employees_view，視圖包含員工號碼、姓名和實際收入3列。使用如下T-SQL命令進行操作。執(zhí)行上面截圖中代碼，在YGGL數(shù)據(jù)庫的視圖中進行刷新，顯示視圖Employees_view，如下所示?！舅伎寂c練習】A、在創(chuàng)建視圖時SELECT有哪些限制？用來創(chuàng)建視圖的SELECT語句，可在SELECT語句中查詢多個表或者視圖，以表明新創(chuàng)的視圖所參照的表或視圖。B、在創(chuàng)建視圖時有哪些注意點？只有在當前數(shù)據(jù)庫中才能夠創(chuàng)建視

23、圖。視圖的命名必須遵循標識符命名規(guī)則，不能夠與表同名。不能夠把規(guī)則、默認值或觸發(fā)器與視圖相關聯(lián)。C、創(chuàng)建視圖，包含員工號碼、姓名、所在部門名稱和實際收入這幾列。在數(shù)據(jù)庫YGGL的視圖中刷新，會顯示剛剛創(chuàng)建的視圖test_view，包含員工號碼、姓名、員工部門名稱和實際收入這幾列。結果如下圖所示。（2）查詢視圖 從視圖DS_VIEW中查詢部門號為3的部門名稱。注：在上面代碼下是顯示錯誤，但是代碼邏輯都正確，而且也能夠正確執(zhí)行，并且輸出正確的結果。（不知道是什么原因） 從視圖Employees_view中查詢姓名為“王林”的員工的實際收入。【思考與練習】A、若視圖關聯(lián)了某表中的所有字段，而此時該表

24、中添加了新的字段，視圖中能否查詢到該字段？首先建立視圖Employees_view并且關聯(lián)表Employees中的所有字段。然后在表Employees中插入一行新的數(shù)據(jù)。最后在“查詢分析器”中查詢EmployeeID等于“000000”的雇員的所有信息。從上面的T-SQL命令可以看出，當視圖關聯(lián)的表中添加新的字段時，視圖中是無法查詢到該字段的，必須重新創(chuàng)建視圖才能夠查詢到新添加的字段。B、自己創(chuàng)建一個視圖，并查詢視圖中的字段。利用T-SQL命令創(chuàng)建一個視圖Employees_view，該視圖包含雇員EmployeeID、Name和RealInCome（實際收入），并且查詢該視圖中姓名為“王林”

25、的雇員的基本情況。具體T-SQL命令代碼如下。查詢該視圖中姓名為“王林”的雇員的基本情況。 （3）更新視圖。在更新視圖前需要了解可更新視圖的概念，了解什么視圖時不可以進行修改的。更新視圖真正更新的是和視圖關聯(lián)額表。 向DS_VIEW中插入一行數(shù)據(jù)“6，廣告部，廣告業(yè)務”。當在“查詢分析器”中輸入上面代碼時，會出現(xiàn)這樣的錯誤。規(guī)則已經(jīng)和Departments。DepartmentID綁定，只需要解除綁定就行了。那么就將前面的T-SQL修改為這樣就行了。當再次執(zhí)行時就會發(fā)現(xiàn)，Departments.DepartmentID已經(jīng)沒有了規(guī)則，也就說明第一次執(zhí)行時規(guī)則已經(jīng)解除綁定。同樣當再次執(zhí)行時會出字

26、段字段已經(jīng)出現(xiàn)，不能夠重復輸入primary key相同的記錄。繼續(xù)在視圖中刷新重新打開“編輯前200行”就會發(fā)現(xiàn)字段已經(jīng)插入。 修改視圖DS_VIEW，將部門號為5的部門名稱修改為“生產(chǎn)車間”。 執(zhí)行上面的T-SQL命令，將更新數(shù)據(jù)之前和之后的DS_VIEW表進行對照，發(fā)現(xiàn)數(shù)據(jù)已經(jīng)成功更新，說明T-SQL命令執(zhí)行成功。DepartmentID為5的DepartmentName已經(jīng)更新為“生產(chǎn)車間”。 執(zhí)行完該命令后，使用select語句分別查看視圖DS_VIEW和和基本表Departments中發(fā)生的變化?；颈鞤epartments中的改變。 視圖DS_VIEW中發(fā)生的改變，見上面截圖。

27、將視圖Employee_view中的員工號為“000001”的員工的姓名修改為“王浩”。執(zhí)行上面的T-SQL命令后，打開視圖Employees_view，可以看出EmployeeID=000001的雇員的Name已經(jīng)修改為“王浩”。 修改視圖DS_VIEW中部門號為“1”的一行數(shù)據(jù)。 【思考與練習】 視圖Employees_view中無法插入和刪除數(shù)據(jù)，其中的RealInCome字段也無法修改，為什么？要通過視圖更新（插入、修改和刪除）基本數(shù)據(jù)，必須保證視圖為可更新視圖，只有滿足一下條件的視圖才是可更新視圖。A、創(chuàng)建視圖的SELECT語句中沒有聚合函數(shù)，且沒有TOP、GROUP BY、UNION子句及DISTINCT關鍵字。B、創(chuàng)建視圖的SELECT語句不包含從基本表列通過計算得到的列。C、創(chuàng)建視圖的SELECT語句的FROM子句中至少要包含一個基本表