《表中數(shù)據(jù)的操作》PPT課件.ppt

1、數(shù)據(jù)庫基礎與應用第5章 表中數(shù)據(jù)的操作,本章學習目標,在企業(yè)管理器中操作表中的數(shù)據(jù) 使用SELECT語句查詢數(shù)據(jù) 使用INSERT語句插入數(shù)據(jù) 使用UPDATE語句更新數(shù)據(jù) 使用DELETE語句刪除數(shù)據(jù),本課核心知識點,SELECT語句 INSERT語句 UPDATE語句 DELETE語句,本課重點難點,SELECT語句的基本結(jié)構及其各子句的用法 多表查詢（連接查詢、嵌套查詢） 使用INSERT、UPDATE、DELETE語句插入、修改和刪除表中數(shù)據(jù) 嵌套查詢,第5章 表中數(shù)據(jù)的操作,5.1 在企業(yè)管理器中操作表中的數(shù)據(jù),表中數(shù)據(jù)的操作包括數(shù)據(jù)的查詢、插入、更新和刪除。,在企業(yè)管理器中，打開指

2、定的服務器和數(shù)據(jù)庫選項，右擊所要操作的表，選擇“打開表”選項，選擇“查詢”選項，出現(xiàn)查詢數(shù)據(jù)對話框 。在對話框中設置各種查詢條件，還可以直接輸入Transact-SQL中的查詢語句，單擊工具欄中的執(zhí)行按鈕將執(zhí)行指定的查詢，并顯示查詢結(jié)果。,打開指定的服務器和數(shù)據(jù)庫選項，右擊所要操作的表，從彈出的快捷菜單中選擇“打開表”選項，選擇“返回所有行”或“返回首行”選項，出現(xiàn)顯示數(shù)據(jù)對話框。,將光標定位到最后一條記錄后面，輸入數(shù)據(jù)就可以完成插入操作；將光標定位到某條記錄，改變某個數(shù)據(jù)的值就可以完成更新操作；右擊某條記錄，從彈出的快捷菜單中選擇“刪除”選項可以完成記錄的刪除操作。 如果對表定義了約束，則在

3、執(zhí)行插入、更新、刪除操作時不能違反約束，否則系統(tǒng)會提示。,5.2 使用SELECT語句查詢數(shù)據(jù),【問題5-1】要求按所發(fā)帖子的總點擊數(shù)由高到低，列出所發(fā)帖子的總點擊數(shù)超過1000的論壇用戶的編號、昵稱和帖子總點擊數(shù)。如何使用SQL語句完成這個查詢?nèi)蝿眨?SELECT語句的功能就是從數(shù)據(jù)庫中檢索出符合用戶需求的數(shù)據(jù)。,語法格式： SELECT 字段列表 INTO 新表名 FROM 要查詢的表 WHERE 查詢條件 GROUP BY 作為分組依據(jù)的表達式 HAVING 對分組結(jié)果進行的篩選條件 ORDER BY 排序依據(jù)的字段 ASC | DESC SELECT語句至少要包含SELECT和FROM

4、兩個子句，其它用括起的短語都是可選的。,5.2.1 使用SELECT子句,SELECT子句語法形式如下： （1）SELECT 參數(shù) 字段列表（每個字段用，隔開） 其中，參數(shù)若是distinct，表示對于某個字段值有重復的記錄，則查詢結(jié)果中只保留一條； 參數(shù)若是TOP N，則代表只保留滿足條件的前N條記錄； 字段列表中的字段若都是同一張表的，則直接寫字段名即可，若涉及到多個表，則形式為：表名.字段名； （2）SELECT * 代表查詢表中的所有字段 （3）SELECT 聚集函數(shù)（字段名）,在expression中可以使用行聚合函數(shù)（又稱統(tǒng)計函數(shù)），SQL Server中常用的聚合函數(shù)如表 。,【

5、例5-1】假設Section表中的數(shù)據(jù)如表所示。,（1）查詢表中的所有記錄。 SELECT * FROM Section （2）查詢所有版塊的名稱（SName），點擊率（SClickCount）和帖子數(shù)量（STopicCount）。 SELECT Sname, SClickCount, STopicCount FROM Section 查詢結(jié)果如下： SName SClickCount STopicCount - - - 【逍遙體苑】 50 1 【相約同行】 100 1 【游記攻略】 120 0 （所影響的行數(shù)為 3 行）,（3）查詢所有版塊的SName（別名為版塊名稱），SMasterID（

6、別名為版主編號）和SClickCount（別名為點擊率）。 SELECT 版塊名稱=Sname, SMasterID AS 版主編號, SClickCount 點擊率 FROM Section 說明：在上例中使用了更改列標題（定義別名）的3種方法： 列別名= 列名 列名 AS 列別名 列名 列別名 注意：列別名的使用范圍：列別名只在定義的語句中有效。,（4）查詢前2條記錄。 SELECT TOP 2 * FROM Section （5）查詢所有版塊的帖子數(shù)量（STopicCount），去掉重復值。 SELECT DISTINCT STopicCount FROM Section （6）統(tǒng)計所有

7、版塊的帖子總數(shù)。 SELECT SUM(STopicCount) FROM Section,5.2.2 使用INTO子句,INTO子句用于創(chuàng)建新表并將查詢結(jié)果插入新表中 。 語法格式： INTO 新表 其中的參數(shù)“新表”用于指定所要生成的新表的名稱。新創(chuàng)建表的列由select_list指定。,【例5-2】創(chuàng)建一個只有版塊編號、版塊名稱和點擊率的新表New_Section_，其列定義和表中數(shù)據(jù)與Section表相同。 SELECT SID, Sname, SClickCount INTO New_Section FROM Section 運行結(jié)果如下： （所影響的行數(shù)為 3 行） 執(zhí)行selec

8、t * from New_Section，返回結(jié)果為： SID Sname SClickCount - - - 1 【逍遙體苑】 50 2 【相約同行】 100 3 【游記攻略】 120 （所影響的行數(shù)為 3 行）。,5.2.3 使用FROM子句,FROM子句用于指定要查詢的表 。 語法形式如下： FROM ,.n . ：指定查詢所用的表、視圖、派生表或聯(lián)接表。 ,【例5-3】假設Users表中的數(shù)據(jù)如下圖所示。 （1）查詢所有版塊的版塊編號、版塊名稱、版主的編號、姓名以及電子郵箱。 USE bbsDB GO SELECT SID , SName, UID, UName, UEmail FRO

9、M Section, Users WHERE Users.UID= Section.SmasterID GO,（2）使用內(nèi)聯(lián)接INNER JOIN 完成（1）的功能。 SELECT SID , SName, UID, UName, UEmail FROM Section INNER JOIN Users ON UID= SmasterID,5.2.4 使用WHERE子句,WHERE子句是條件子句，用于限定查詢的內(nèi)容。 語法格式： WHERE 查詢的條件表達式 查詢的條件表達式:= NOT | ( ) AND |OR NOT | ( ) ,.n 參數(shù)說明： search_condition：查詢

10、的條件表達式,1. 比較表達式 使用比較表達式的一般形式為： 表達式 比較運算符 表達式 表達式：可以是列名、常量、函數(shù)、變量、標量子查詢，或者是由運算符或子查詢連接的列名、常量和函數(shù)的任意組合。還可以包含 CASE 函數(shù)。 比較運算符 : =（等于） （大于） =（對于等于） （不等于） !（不大于） !（不小于） !=（不等于）,【例5-4】查詢表Section中帖子數(shù)量不小于10的版塊。 SELECT * FROM Section WHERE STopicCount =10,2邏輯表達式 在Transact-SQL中可以使用的邏輯運算符有3個： NOT（邏輯反）、AND（邏輯與）、OR（

11、邏輯或） 邏輯運算符的優(yōu)先順序是 NOT、 AND、 OR。在比較表達式和邏輯表達式中有3種可能的取值：TRUE、FALSE或UNKNOWN。 【例5-5】查詢表Section中點擊率不低于100，并且帖子數(shù)量不為0的版塊。 SELECT * FROM Section WHERE SClickCount=100 AND STopicCount0,3BETWEEN關鍵字 使用BETWEEN關鍵字可以限定查尋范圍，其語法形式如下： test_expression NOT BETWEEN begin_expression AND end_expression 參數(shù)說明： test_expressio

12、n：被測試的表達式。 begin_expression ：指定取值范圍的上限。 end_expression：指定取值范圍的下限。 【例5-6】查詢表Section中點擊率在50和100之間的版塊。 SELECT * FROM Section WHERE SClickCount BETWEEN 50 AND 100,4IN關鍵字 使用IN關鍵字可以測試給定的值是否與子查詢或列表中的值相匹配。 語法格式： test_expression NOT IN ( subquery | expression ,.n ) 參數(shù)說明： test_expression：任何有效的SQL Server表達式。 s

13、ubquery：包含某列結(jié)果集的子查詢。 expression ,.n：一個表達式列表，用來測試是否匹配。 【例5-7】從Users表中查詢生日不在3月、5月和7月的論壇用戶信息。 SELECT * FROM Users WHERE MONTH(UBirthday) NOT IN (3,5,7),5LIKE關鍵字 LIKE關鍵字用于將所給字符串與指定的的模式匹配。 語法格式： match_expression NOT LIKE pattern 參數(shù)說明： match_expression：任何字符串數(shù)據(jù)類型的有效 SQL Server 表達式。 Pattern：指定match_expressi

14、on 中的搜索模式，可以包含下列有效 SQL Server 通配符： %：可匹配任意類型和長度的字符串。 _（下劃線）：可匹配任何單個字符。,【例5-8】在Pubs數(shù)據(jù)庫的 authors 表中查找所有區(qū)號為 415 的電話號碼。 USE pubs SELECT phone FROM authors WHERE phone LIKE 415% ORDER by au_lname GO,【例5-9】在Pubs數(shù)據(jù)庫的 authors 表中查找名字為 Cheryl 或 Sheryl 的作者。 USE pubs SELECT au_lname, au_fname, phone FROM author

15、s WHERE au_fname LIKE CSheryl ORDER BY au_lname ASC, au_fname ASC GO 查詢結(jié)果如圖5所示。,【例5-10】 在Pubs數(shù)據(jù)庫的 authors 表中查找姓為 Carson、Carsen、Karson 或 Karsen 的作者。 USE pubs SELECT au_lname, au_fname, phone FROM authors WHERE au_lname LIKE CKarseon ORDER BY au_lname ASC, au_fname ASC GO 查詢結(jié)果如圖所示。,6NULL關鍵字 在WHERE子句中不

16、能使用比較運算符對空值進行判斷，只能使用IS NULL來確定一個給定的表達式是否為 NULL。其語法形式如下： 表達式 IS NOT NULL,【例5-11】在Pubs數(shù)據(jù)庫的 authors 表中查找所有預付款少于 $5,000 或者預付款未知（或為 NULL）的書，返回它們的書號及預付款。 USE pubs GO SELECT title_id, advance FROM titles WHERE advance 5000 OR advance IS NULL GO 查詢結(jié)果如下：,5.2.5 使用GROUP BY子句,GROUP BY子句用來對查詢結(jié)果分組。 語法格式： GROUP BY

17、 分組表達式 注意：指定 GROUP BY 時，SELECT列表中任一非聚合表達式內(nèi)的所有列都應包含在 GROUP BY 列表中。,【例5-12】統(tǒng)計論壇庫中Users表中的男、女用戶人數(shù)。 USE bbsDB GO SELECT USex, COUNT(*) 人數(shù) FROM Users GROUP BY USex GO 查詢結(jié)果如圖,5.2.6 使用HAVING子句,HAVING 子句指定分組或聚合的搜索條件。 HAVING 必須與 GROUP BY 子句一起使用。如果不使用 GROUP BY 子句，不能使用HAVING子句。 語法格式： HAVING 篩選條件 “篩選條件”指定組或聚合應滿

18、足的搜索條件。,【例5-13】在Pubs數(shù)據(jù)庫的 titles表中查詢截止到目前的銷售額超過$40,000的出版商。 USE pubs GO SELECT pub_id, total = SUM(ytd_sales) FROM titles GROUP BY pub_id HAVING SUM(ytd_sales) 40000 GO 查詢結(jié)果如圖 。,5.2.7 使用ORDER BY子句,ORDER BY子句用于根據(jù)一個列或者多個列來排序查詢結(jié)果 。 語法形式如下： ORDER BY 排序表達式 ASC | DESC ,.n 參數(shù)說明： 排序表達式 ：指定要排序的列，可以是多個字段，用“，”隔

19、開。 ASC：指定按遞增順序的值進行排序。 DESC：指定按遞減順序的值進行排序。 說明：空值被視為最低的可能值。,【例5-14】將發(fā)帖表Topic中的記錄按點擊率由低到高排序。 USE bbsDB GO SELECT * FROM Topic ORDER BY TClickCount GO,5.2.8 使用COMPUTE子句,COMPUTE子句用于生成統(tǒng)計結(jié)果，放在查詢結(jié)果的最后。 語法格式： COMPUTE 聚集函數(shù)（列名） 對于SELECT查詢的返回結(jié)果，計算指定的某個聚集函數(shù)的值。,【例5-15】查詢Section表中所有版塊的點擊率及平均點擊率。 USE bbsDB SELECT S

20、ID, SName, SClickCount FROM Section COMPUTE AVG(SClickCount) GO 查詢結(jié)果如圖,5.2.9 使用聯(lián)合查詢,聯(lián)合查詢是指將兩個或兩個以上的SELECT語句通過UNION運算符連接起來的查詢， 聯(lián)合查詢可以將兩個或更多查詢的結(jié)果組合為單個結(jié)果集，該結(jié)果集包含聯(lián)合查詢中所有查詢的全部行。 使用UNION組合兩個查詢結(jié)果集的兩個基本規(guī)則是： 所有查詢中的列數(shù)和列的順序必須相同。 數(shù)據(jù)類型必須兼容。,語法格式： 查詢語句1 UNION ALL 查詢語句2 參數(shù)說明： ALL：在結(jié)果中包含所有的行，包括重復行。如果沒有指定，則刪除重復行。,【例

21、5-16】查詢發(fā)帖表Topic和跟帖表Reply中所有帖子的發(fā)帖人編號和主題。 USE bbsDB GO SELECT TUID AS 發(fā)帖人編號, TTopic AS 主題 FROM Topic UNION SELECT RUID,RTopic FROM Reply GO 查詢結(jié)果如圖 。,5.2.10 使用嵌套查詢,在SQL語言中，一個SELECT-FROM-WHERE語句稱為一個查詢塊，將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING子句的條件中的查詢稱為嵌套查詢。 嵌套查詢中上層的查詢塊稱為外層查詢或父查詢，下層查詢塊稱為內(nèi)層查詢或子查詢。SQL語言允許多層嵌套。嵌套查詢主

22、要用于復雜的查詢中。 在某些嵌套查詢中WHERE之后還可以使用ANY和ALL兩個關鍵字。,【例5-17】查詢年齡最小的論壇用戶的用戶編號、昵稱、出生日期和電子郵箱。 USE bbsDB GO SELECT UID, UName, UBirthday, UEmail FROM Users WHERE UBirthday = ALL (SELECT UBirthday FROM Users ) 查詢結(jié)果如圖,【例5-18】查詢除年齡最小的論壇用戶外的所有用戶的編號、昵稱、出生日期和電子郵箱。 USE bbsDB GO SELECT UID, UName, UBirthday, UEmail FRO

23、M Users WHERE UBirthday ANY (SELECT UBirthday FROM Users ) 查詢結(jié)果如圖,【例5-19】SELECT語句綜合應用：關于【問題5-1】的解答。 SELECT UID,Uame,SUM(TClickCount)as TClickCount_Total FROM Users,Topic WHERE Users.UID=Topic.TUID GROUP BY UID,UName HAVING SUM(TClickCount)1000 ORDER BY TClickCount_Total DESC 查詢結(jié)果如圖,5.3 使用INSERT語句插入數(shù)

24、據(jù),【問題5-2】在Pubs數(shù)據(jù)庫中，從編號為1389的出版社新進一本business類書，書號為BU9876, 書名為Creating Web Pages, 價格為29.99元，如何使用SQL語句將此書加入Pubs數(shù)據(jù)庫的圖書表Titles中？ 可以使用INSERT語句向表或視圖中添加一行或多行數(shù)據(jù)。,語法格式： INSERT INTO 表名（要插入數(shù)據(jù)的字段列表） VALUES(每個字段相應的值) 說明： 字段列表中字段的個數(shù)與值的個數(shù)必須相等； 要插入的字段值的類型必須與表定義相同； 字段列表如果是表中全部字段時，可以省略；,【例5-20】將一行添加到 Pubs數(shù)據(jù)庫的titles 表中

25、，以指定書名、類型、出版商及價格的值： USE Pubs GO INSERT INTO titles(title_id, title, type, pub_id, price) VALUES (BU9876, Creating Web Pages, business, 1389, 29.99),5.4 使用UPDATE語句更新數(shù)據(jù),【問題5-3】 如何使用SQL語句將Pubs數(shù)據(jù)庫的titles 表中書號為BU9876的書名修改為“網(wǎng)頁制作”？ 可以使用UPDATE語句修改表中特定記錄或字段的數(shù)據(jù)。,語法格式： UPDATE 表名 SET 列名1= 表達式1，列名2= 表達式2， WHERE

26、查詢表達式 。 參數(shù)說明： WHERE：指定條件來限定所更新的行。若無WHERE子句，將會修改表中的每行數(shù)據(jù)。,【例5-21】修改Pubs數(shù)據(jù)庫的titles 表中書號為BU9876的書名為“網(wǎng)頁制作”。 USE Pubs GO UPDATE titles SET title=網(wǎng)頁制作 WHERE title_id=BU9876,5.5 使用DELETE語句刪除數(shù)據(jù),DELETE語句用于刪除表中記錄。,5.5.1 使用DELETE語句,語法格式： DELETE FROM 表名 WHERE 查詢表達式 參數(shù)說明： WHERE：指定條件來限定所刪除的行。若無WHERE子句，將刪除表中所有行。,【例

27、5-22】刪除pubs數(shù)據(jù)庫titles表中書號為BU9876的記錄。 USE pubs GO DELETE FROM titles WHERE title_id=BU9876 【例5-23】刪除pubs數(shù)據(jù)庫authors表中 au_lname 是 McBadden 的所有行。 USE pubs GO DELETE FROM authors WHERE au_lname = McBadden,5.5.2 使用TRUNCATE語句,清除表中的所有數(shù)據(jù)、只留下表的定義，可以使用TRUNCATE語句。與DELETE語句相比，TRUNCATE通常速度快，因為TRUNCATE是不記錄日志的操作。 語法

28、格式： TRUNCATE TABLE name 參數(shù)name指定要刪除全部行的表的名稱。 【例5-24】清空跟帖表Reply中的數(shù)據(jù)。 TRUNCATE TABLE Reply,5.6 綜合實例,【實例說明】 對在4.7中創(chuàng)建的COLLEGE數(shù)據(jù)庫，完成以下對表中數(shù)據(jù)的操作： （1）查詢“06182”班總分在300分以上的學生的學號，姓名和總成績，結(jié)果按總成績由高到低排序。（設學號字段S_number的前5位為班號）。 （2）根據(jù)Student表產(chǎn)生一個名為Student_1的新表，其內(nèi)容僅包括黨員同學。 （3）向Student_1表插入單條記錄，情況如表所示。 （4）批量插入記錄：將表Stu

29、dent表中所有女同學加入到Student_1中。 （5）將Score表中分數(shù)低于60分的在原來的基礎上加10分 （6）將Student_2表中的所有政治面目為群眾的同學刪除。,【實現(xiàn)技術分析】 （1）使用SELECT語句完成查詢操作； （2）使用INSERT語句完成向表中添加新記錄的插入操作； （3）使用UPDATE語句完成表中數(shù)據(jù)的修改操作； （4）使用DELETE語句完成表中數(shù)據(jù)的刪除操作；。,【功能實現(xiàn)】 （1）使用SELECT語句進行查詢操作,需要用到GROUP BY、HAVING 、ORDER BY等子句。將查詢結(jié)果存入新表，還需要使用INTO子句。 （2）插入單條記錄，若提供的字段值去不全，需要列出各字段值所對應的字段名，注意要一一對應。 （3）成批插入記錄的數(shù)據(jù)來源為一個查詢。 （4）可以成批更新和刪除符合條件的記錄。,【關鍵代碼（或技術）】 （1）SELECT Student.S_number,S_name AS 姓名,SUM(Score) AS 總分 FROM Student,Score WHERE Student.S_number=Score.S_number