SQL Sever實例教程（2019版） 課件 劉志成 第4、5章 表操作、查詢操作

SQLServer實例教程(2019版)表操作第四章”十四五”職業(yè)教育國家規(guī)劃教材01SQLServer表的概念與數(shù)據(jù)類型SQLServer表的概念與數(shù)據(jù)類型01SQLServer表的概念表是存放數(shù)據(jù)庫中所有數(shù)據(jù)的數(shù)據(jù)庫對象。如同Excel電子表格一樣，數(shù)據(jù)在表中是按行和列的格式進行組織的。其中，每行代表一條記錄，每列代表記錄中的一個域。例如，在包含商品信息的商品表中每行代表一種商品，每列代表這種商品的某一方面的特性，如商品名稱、價格、數(shù)量及折扣等。SQLServer表的概念與數(shù)據(jù)類型01SQLServer基本數(shù)據(jù)類型SQLServer可以存儲不同類型的數(shù)據(jù)，如字符、貨幣、整型和日期時間等。SQLServer表中的每列都必須指出該列可存儲的數(shù)據(jù)類型。SQLServer常用的數(shù)據(jù)類型及其存儲值范圍如表4-2所示。SQLServer表的概念與數(shù)據(jù)類型01SQLServer基本數(shù)據(jù)類型續(xù)表：02使用SSMS管理表使用SSMS管理表02創(chuàng)建表【任務1-1】在電子商城數(shù)據(jù)庫WebShop中創(chuàng)建存放商品信息的表Goods。（1）啟動SQLServerManagementStudio，在“對象資源管理器”中依次展開【數(shù)據(jù)庫】節(jié)點、【W(wǎng)ebShop】節(jié)點。（2）右擊【表】，在彈出的快捷菜單中選擇【新建】→【表】選項，如圖4-3所示。也可以在“摘要頁”區(qū)域右擊，在彈出的快捷菜單中選擇【新建】一【表】選項。使用SSMS管理表02創(chuàng)建表（3）在如圖4-4所示的窗口的右上部面板中輸入列名、數(shù)據(jù)類型、長度和為空性等表的基本信息。輸入完成后的表的基本信息如圖4-5所示。使用SSMS管理表02創(chuàng)建表（4）在圖4-4所示的窗口的右下部面板中輸入表是否自動增長等補充信息，如圖4-6所示。使用SSMS管理表02創(chuàng)建表（5）所有列名輸入完成后，單擊窗口標題欄中的

按鈕或工具欄中的

按鈕，打開如圖4-7所示的對話框，確認是否保存所創(chuàng)建表。使用SSMS管理表02創(chuàng)建表（6）單擊【是】按鈕后打開“選擇名稱”對話框，輸入表名Goods，如圖4-8所示，完成表的創(chuàng)建。如果在該數(shù)據(jù)庫中已經(jīng)有同名的表存在，系統(tǒng)會彈出警告對話框，用戶可以改名并重新進行保存。新表創(chuàng)建后，在“對象資源管理器”中展開【數(shù)據(jù)庫】節(jié)點下的數(shù)據(jù)庫節(jié)點【W(wǎng)ebShopl,可以看到剛才所建的表，如圖4-9所示。使用SSMS管理表02修改表（1）修改表的結構?！救蝿?-2】在電子商城數(shù)據(jù)庫WebShop中將Goods表中的列名g_ProduceDate修改為gDate。①啟動SQLServerManagementStudio，在“對象資源管理器”中依次展開【數(shù)據(jù)庫】節(jié)點、【W(wǎng)ebShop】節(jié)點。②在【Goods】表上右擊，在彈出的快捷菜單中選擇【設計】選項，如圖4-10所示。使用SSMS管理表02修改表③將g_ProduceDate修改為g_Date，如圖4-11所示。④所有內容修改完成后，單擊窗口標題欄中的

或工具欄中的

按鈕進行保存，完成表的修改。如果表中已有數(shù)據(jù)，則保存時系統(tǒng)會打開對話框讓用戶進行確認。（2）重命名表。表在創(chuàng)建以后可以根據(jù)需要對其表名進行修改，在圖4-10所示的【Goods】表的右鍵快捷菜單中選擇【重命名】選項，或者在選定的表上單擊，在表名的編輯狀態(tài)下完成表名的重新命名。使用SSMS管理表02查看表【任務1-3】查看電子商城數(shù)據(jù)庫WebShop中所創(chuàng)建的Goods表的信息。（1）啟動SQLServerManagementStudio，在“對象資源管理器”中依次展開【數(shù)據(jù)庫】節(jié)點、【W(wǎng)ebShop】節(jié)點。（2）在【Goods】表上右擊，如圖4-10所示，在彈出的快捷菜單中選擇【屬性】選項。使用SSMS管理表02查看表（3）打開“表屬性”對話框，如圖4-12所示，可以查看Goods表的常規(guī)、權限和擴展屬性等詳細信息。使用SSMS管理表02刪除表【任務1-4】刪除電子商城數(shù)據(jù)庫WebShop中所創(chuàng)建的Goods表。（1）啟動SQLServerManagementStudio，在“對象資源管理器”中展開【數(shù)據(jù)庫】節(jié)點。（2）展開【W(wǎng)ebShop】節(jié)點，在【Goods】表上右擊，在彈出的快捷菜單中選擇【刪除】選項，如圖4-13所示。使用SSMS管理表02刪除表（3）打開“刪除對象”對話框，如圖4-14所示，單擊【確定】按鈕即可完成表的刪除操作。03使用T-SQL語句管理表使用T-SQL語句管理表03創(chuàng)建表使用T-SOL語句創(chuàng)建表的基本語句格式如下:參數(shù)含義如下。表名：要建立的表名必須是符合命名規(guī)則的任意字符串。在同一個數(shù)據(jù)庫中的表名應當是唯一的，而在不同數(shù)據(jù)庫中允許出現(xiàn)名稱相同的表。使用T-SQL語句管理表03創(chuàng)建表列名：組成表的各個列的名稱。在一個表中，列名也應該是唯一的，而在不同的表中允許出現(xiàn)相同的列名。數(shù)據(jù)類型：對應列數(shù)據(jù)所采用的數(shù)據(jù)類型，可以是數(shù)據(jù)庫管理系統(tǒng)支持的任何數(shù)據(jù)類型。列級約束：用來對列中的數(shù)據(jù)進行限制，如非空約束、鍵約束及用條件表達式表示的完整性約束。表級約束：如果完整性約束條件涉及該表的多個屬性列，則必須定義在表級上。這些約束連同列約束會被存儲到系統(tǒng)的數(shù)據(jù)字典中。當用戶對數(shù)據(jù)進行相關操作的時候，由DBMS自動檢查該操作的合法性。使用T-SQL語句管理表03修改表在數(shù)據(jù)庫設計完成后，有時要求對數(shù)據(jù)庫中的表進行修改，通過ALTERTABLE語句可以在創(chuàng)建一個表以后對它的結構進行調整，包括添加新列、增加新約束條件、修改原有的列定義和刪除已有的列及約束條件。其基本語句格式如下：其中，<表名>指定需要修改的表，ADD子句用于增加新列和新的完整性約束條件，DROP子句用于刪除指定的完整性約束條件或指定的列，MODIFY子句用于修改原有的列定義(主要是數(shù)據(jù)類型)。04記錄操作記錄操作04使用SSMS進行記錄操作（1）啟動SQLServerManagermentStudio，在“對象資源管理器”中依次展開【數(shù)據(jù)庫】節(jié)點、【W(wǎng)ebShop】節(jié)點。（2）在【Goods】表上右擊，在彈出的快捷菜單中選擇【編輯前200行】選項，如圖4-18所示。記錄操作04使用SSMS進行記錄操作（3）在SQLServerManagementStudio中可以直接對表格進行添加、修改表中的記錄的操作，如圖4-19所示。記錄操作04使用SSMS進行記錄操作（4）如果要刪除記錄，在選定的記錄上單擊【刪除】按鈕即可，如圖4-20所示。操作完成后，根據(jù)提示保存操作結果即可完成對表中記錄的操作。記錄操作04使用T-SQL語句進行記錄操作1．使用T-SQL語句插入記錄使用INSERTINTO語句可以向表中添加記錄或者創(chuàng)建追加查詢，插入單個記錄的基本語句格式如下。記錄操作04使用T-SQL語句進行記錄操作2．使用T-SQL語句修改記錄使用UPDATE語句可以按照某個條件修改特定表中的字段值，其基本語句格式如下。記錄操作04使用T-SQL語句進行記錄操作3．使用T-SQL語句刪除記錄使用DELETE語句可以刪除表中的記錄，其基本語句格式如下:05SQLServer2019中的數(shù)據(jù)完整性SQLServer2019中的數(shù)據(jù)完整性05數(shù)據(jù)完整性1．實體完整性實體完整性規(guī)定表的每一行在表中是唯一的。實體表中定義的索引、UNIQUE約束、PRIMARYKEY約束和IDENTITY屬性就是實體完整性的體現(xiàn)。SQLServer2019中的數(shù)據(jù)完整性05數(shù)據(jù)完整性2．域完整性域完整性是指數(shù)據(jù)庫表中的列必須滿足某種特定的數(shù)據(jù)類型或約束，其中約束又包括取值范圍和精度等規(guī)定。表中的CHECK約束、FOREIGNKEY約束、DEFAULT定義、NOTNULL和規(guī)則都屬于域完整性的范疇。SQLServer2019中的數(shù)據(jù)完整性05數(shù)據(jù)完整性3．引用完整性引用完整性是指兩個表的主關鍵字和外關鍵字的數(shù)據(jù)應相應一致。它確保了有主關鍵字的表中對應其他表的外關鍵字的行存在，即保證了表之間的數(shù)據(jù)的一致性，防止了數(shù)據(jù)丟失或無意義的數(shù)據(jù)在數(shù)據(jù)庫中擴散。引用完整性是創(chuàng)建在外關鍵字和主關鍵字之間或外關鍵字和唯一性關鍵字之間的關系上的。引用完整性時，SQLServer將阻止用戶執(zhí)行下列操作。（1）在主表中沒有關聯(lián)的記錄時，將記錄添加或更改到相關表中。（2）更改主表中的值，會導致相關表中生成孤立記錄。（3）從主表中刪除記錄，但仍存在與該記錄匹配的相關記錄。FOREIGNKEY和CHECK約束都屬于引用完整性。SQLServer2019中的數(shù)據(jù)完整性05數(shù)據(jù)完整性4．用戶定義完整性用戶定義完整性指的是由用戶指定的一組規(guī)則，它不屬于實體完整性、域完整性或引用完整性。CREATETABLE中的所有列級和表級約束、存儲過程和觸發(fā)器都屬于用戶完整性。SQLServer2019中的數(shù)據(jù)完整性05列約束與表約束對數(shù)據(jù)庫來說，約束分為列約束和表約束。其中，列約束作為列定義的一部分只作用于此列本身：表約束作為表定義的一部分，可作用于多個列。下面的語句將會創(chuàng)建多個列約束和一個表約束。SQLServer2019中的數(shù)據(jù)完整性05PRIMARYKEY約束表通常具有包含唯一標識表中每一行的值的一列或多列,這樣的一列或多列稱為表的主鍵（PrimaryKey，PK），用于強制表的實體完整性。PRIMARYKEY約束通過創(chuàng)建唯一索引保證指定列的實體完整性，使用PRIMARYKEY約束時，列的空值屬性必須定義為NOTNULL。PRIMARYKEY約束可以應用于表中一列或多列。SQLServer2019中的數(shù)據(jù)完整性05FOREIGNKEY約束FOREIGNKEY約束為表中一列或多列數(shù)據(jù)提供引用完整性，它規(guī)定插入到表中被約束列的值必須在被引用表中已經(jīng)存在。如數(shù)據(jù)表Orders中c_ID列和e_ID列分別引用數(shù)據(jù)表Customers中的c_ID（說明了是誰下的訂單）和數(shù)據(jù)表Employees中的eID列（說明了是誰負責處理該訂單)。在向數(shù)據(jù)表Orders中插入新行或修改其數(shù)據(jù)時,這兩列的值必須在數(shù)據(jù)表Customers和數(shù)據(jù)表Employees中已經(jīng)存在，否則將不能執(zhí)行插入或修改操作。實施FOREIGNKEY約束時，要求在被引用表中己經(jīng)定義了PRIMARYKEY約束或UNIQUE約束。圖4-31說明了主鍵和外鍵的關系。SQLServer2019中的數(shù)據(jù)完整性05FOREIGNKEY約束感謝觀看，再見！SQLServer實例教程(2019版)”十四五”職業(yè)教育國家規(guī)劃教材SQLServer實例教程(2019版)查詢操作第五章”十四五”職業(yè)教育國家規(guī)劃教材01單表查詢單表查詢01選擇列1．所有列【任務1-1】網(wǎng)站銷售部管理人員或采購部管理人員需要了解所有商品的詳細信息。SELECT*FROMGoods該語句無條件地把Goods表中的全部信息查詢出來，所以也稱為全表查詢，這是最簡單的一種查詢，運行結果如圖5-3所示。單表查詢01選擇列2．指定列【任務1-2】網(wǎng)站管理人員在了解商品信息時只需要了解所有商品的商品號、商品名稱和商品單價信息，而不需要了解商品的其他信息。SELECTgID,g_Name,gPriceFROMGoods該語句把Goods表中所有商品的g_ID（商品號)、g_Name（商品名稱〉和g_Price（商品單價）查詢出來，運行結果如圖5-4所示。單表查詢01選擇列3．計算列【任務1-3】在Goods表中存儲了商品的基本信息（包括商品數(shù)量和商品單價)，現(xiàn)在需要了解網(wǎng)站中所有商品的商品號、商品名稱和商品總金額。SELECTgID,gName,gPrice*gNumberFROMGoodsR運行結果如圖5-6所示。單表查詢01選擇列4．使用別名在顯示結果集時，可以指定以別名（顯示的名稱〉代替原來的列名，通常也用來顯示結果集中列的漢字標題。【任務1-4】要求了解所有商品的商品號、商品名稱和總價值，但希望以漢字標題商品號、商品名稱和總價值表示g_ID、g_Name和g_Price*g_Number。SELECT

gID商品號，gName

商品名稱，g_Price*g_Number總價值FROMGoods運行結果如圖5-7所示。單表查詢01選擇行1．查詢滿足條件的行查詢滿足指定條件的記錄可以通過WHERE子句實現(xiàn)，WHERE后可以使用關系運算符來進行條件判斷。在WHERE中常見的運算符如表5-2所示。單表查詢01選擇行1．查詢滿足條件的行（1）簡單條件查詢?！救蝿?-5】需要了解所有商品中“熱點”商品的所有信息。SELECT*FROMGoodsWHEREg_Status=’熱點’運行結果如圖5-8所示。單表查詢01選擇行1．查詢滿足條件的行（2）復合條件查詢。使用邏輯運算符AND和OR可連接多個查詢條件。如果這兩個運算符同時出現(xiàn)在同一個WHERE子句中，則AND的優(yōu)先級高于OR，但用戶可以用括號改變優(yōu)先級?！救蝿?-6】需要了解商品類別為“01”、商品單價在2500元以上的商品信息，要求以漢字標題顯示商品號、商品名稱、商品類別號和價格。運行結果如圖5-9所示。單表查詢01選擇行2.消除重復行【任務1-16】需要了解在WebShop網(wǎng)站進行購物并下訂單的會員編號，如果一個會員下了多個訂單，則只需要顯示一次會員編號。SELECTg_ID

FROMOrderDetails運行結果如圖5-19所示。單表查詢01選擇行2.消除重復行如果想讓會員編號“c0001”只顯示一次，則必須指定DISTINCT短語。SELECTDISTINCTg_IDFROMOrderDetails運行結果如圖5-20所示。單表查詢01ORDERBY子句在利用T-SQL語句進行查詢時，如果沒有指定查詢結果的顯示順序，DBMS將按其最方便的順序（通常是記錄在表中的先后順序）輸出查詢結果?！救蝿?-1】需要了解商品類別號為“01”的商品的商品號、商品名稱和商品單價，并要求根據(jù)商品的價格進行降序（價格由高到低）排列。運行結果如圖5-22所示。單表查詢01GROUPBY子句GROUPBY子句可以將查詢結果表的各行按某一列或多列取值相等的原則進行分組。一般情況下，分組的目的是便于進一步統(tǒng)計。在SELECT子句中使用聚合函數(shù)對記錄組進行操作，可返回應用于一組記錄的單一值。T-SQL中常見的聚合函數(shù)如表5-3所示。單表查詢01分頁和排名過去使用SQLServer2000進行分頁時，需要用到臨時表。在SQLServer2019中，借助于ROW_NUMBER可以很方便地實現(xiàn)分頁。ROW_NUMBER的基本語句格式如下：使用RANK可返回結果集的分區(qū)內每行的排名，行的排名是相關行之前的排名數(shù)加1。RANK的基本語句格式如下:02連接查詢連接查詢02內連接1.等值連接用來連接兩個表的條件稱為連接條件或連接謂詞，其一般格式如下:2.非等值連接使用>、<、>=、<-等運算符作為連接條件的連接稱為非等值連接。但在實際應用中很少使用非等值連接，并且非等值連接只有與自身連接同時使用才有意義。3.自身連接連接操作不僅可以在兩個表之間進行，也可以是一個表與其自身進行連接，這種連接稱為表的自身連接。自身連接一般很少用于查詢數(shù)據(jù)，主要用于使用INSERT、UPDATE語句對一個表中滿足特定條件的行進行操作的情況。連接查詢02外連接1.左外連接左外連接包括第一個命名表（“左”表，出現(xiàn)在JOIN子句的最左邊）中的所有行，但不包括右表中不滿足條件的行。2.右外連接右外連接包括第二個命名表（“右”表，出現(xiàn)在JOIN子句的最右邊）中的所有行，但不包括左表中不滿足條件的行。3.完整外部連接完整外部連接將包括所有連接表中的所有行，而不論它們是否匹配。連接查詢02交互連接連接運算中還有一種特殊情況，即卡氏積連接，卡氏積是不帶連接謂詞的連接。兩個表的卡氏積是兩表中記錄的交叉乘積，即其中一表中的每條記錄都要與另一表中的每條記錄做拼接，因此結果表往往很大?！救蝿?-8】對商品信息表和類別表進行交叉連接，完成語句有以下兩種:03子查詢子查詢03子查詢類型1．使用IN或NOTIN的子查詢帶有IN謂詞的子查詢是指父查詢與子查詢之間用N進行連接,判斷某個屬性列值是否在子查詢的結果中。由于在嵌套查詢中，子查詢的結果往往是一個集合，所以謂詞IN是嵌套查詢中最常使用的謂詞。子查詢03子查詢類型2．使用比較運算符的子查詢帶有比較運算符的子查詢是指父查詢與子查詢之間用比較運算符進行連接。當用戶能確切知道內層查詢返回的是單值時，可以使用>、<、=、>=、<-、!-或<>等比較運算符。單值情況下使用-，多值情況下使用IN或NOTIN謂詞。子查詢03子查詢類型3．使用ANY或ALL的子查詢子查詢返回單值時可以使用比較運算符，而使用ANY或ALL謂詞時必須同時使用比較運算符，其含義如表5-4所示。子查詢03子查詢規(guī)則子查詢也是由SELECT語句組成的，所以在使用SELECT語句時應注意的問題也同樣適用于子查詢，同時，子查詢還受下列條件的限制。（1）通過比較運算符引入的子查詢的選擇列表只能包括一個表達式或列名稱。（2）如果外部查詢的WHERE子句中包括某個列名，則該子句必須與子查詢選擇列表中的該列兼容。（3）子查詢的選擇列表中不允許出現(xiàn)ntext、text和image數(shù)據(jù)類型。（4）無修改的比較運算符引入的子查詢中不能包括GROUPBY和HAVING子句。（5）包括GROUPBY的子查詢不能使用DISTINCT關鍵字。子查詢03子查詢規(guī)則（6）不能指定COMPUTE和INTO子句。（7）只有同時指定了TOP，才可以指定ORDERBY。（8）由子查詢創(chuàng)建的視圖不能更新。（9）通過EXISTS引入的子查詢的選擇列表由星號（*）組成，而不使用單個列名。（10）當-、!=、<、<=、>或>-被用在主查詢中時，ORDERBY子句和GROUPBY子句不能用在內層查詢中，因為內層查詢返回的一個以上的值不可被外層查詢處理。04聯(lián)合查詢聯(lián)合查詢03每個SELECT語句都能獲得一個或一組記錄。若要把多個SELECT語句的結果合并為一個結果，可用集合操作來完成。集合操作主要包括并操作UNION、交操作INTERSECT和差操作MINUS。當使用UNION將多個查詢結果合并起來，形成一個完整的查詢結果時，系統(tǒng)會自動去掉