（中職）數據庫管理與應用案例教程03-Chap03課件

1、（中職）數據庫管理與應用案例教程03-Chap03ppt課件數據庫管理與應用實例教程彭勇 主編第3章 表的創(chuàng)建與管理 本章導航本章的主要內容及其在SQL Server2005數據庫管理系統(tǒng)中的位置如圖示。 SQL server2005基礎數據模型數據庫技術概述數據庫的創(chuàng)建數據庫的管理數據庫基礎視圖索引表存儲過程觸發(fā)器數據庫對象子查詢連接查詢簡單查詢聯合查詢查詢技術數據庫備份與恢復數據庫的安全游標、事務和鎖數據庫分享與附加數據庫安全與管理數據庫設計和應用程序開發(fā)學習要點（1）設計表的組成要素（2）SQL Server2005中的數據類型（3）表的創(chuàng)建與管理（4）數據的操作（5）索引的創(chuàng)建與管理（

2、6）數據完整性 設計表 一、表的類型 在Microsoft SQL Server 2005系統(tǒng)表中，可以把表分為4種類型，即普通表、分區(qū)表、臨時表和系統(tǒng)表。每一種類型的表都有其自身的作用和特點。 二、數據類型 SQL Server提供系統(tǒng)數據類型集，該類型集定義了可與SQL Server一起使用的所有數據類型。用戶還可以在Transact-SQL或.NET框架中定義自己的數據類型，它是系統(tǒng)數據類型的別名。也叫別名數據類型。 1系統(tǒng)數據類型 整數型 精確數值型 浮點型（近似數值型） 貨幣數據類型 字符數據類型 二進制字符數據類型 日期和時間數據類型 位型 其他數據類型 2自定義數據類型 使用SQ

3、L Server管理平臺創(chuàng)建用戶自定義數據類型 利用T-SQL語句創(chuàng)建用戶自定義數據類型 課堂實踐1 【任務1】創(chuàng)建用戶自定義數據類型mytext，用于Books數據庫中各表的備注列的類型?；绢愋蜑閠ext?！救蝿?】查閱資料，比較SQL Server 2005與SQL Server 2000中創(chuàng)建用戶自定義類型的區(qū)別。 【案例學習目標】 掌握用Microsoft SQL Server管理平臺管理books數據庫中Book數據表的方法?！景咐R要點】 數據表的創(chuàng)建、修改、查看和刪除操作?！景咐瓿刹襟E】1創(chuàng)建表（1）啟動“SQL Server Management Studio”，在“對象

4、資源管理器”中依次展開【數據庫】節(jié)點、【books】數據庫節(jié)點。（2）右鍵單擊【表】，選擇【新建表】，如圖所示。 表的創(chuàng)建與管理 一、課堂案例1使用SSMS創(chuàng)建與管理表 （3）在右下部面板中輸入列的某些屬性值。（4）用戶可以為表添加主鍵。 （5）用戶依次完成表的所有創(chuàng)建工作后，單擊窗口中的【文件】，在下拉菜單中，選擇【保存（S）Table_1】命令，如圖所示，打開【選擇名稱】對話框，在其中輸入合適的表名，此處輸入“Book”，單擊【確定】按鈕，保存所創(chuàng)建的表。按照同樣的步驟，參考附錄A，可以創(chuàng)建books數據庫中的其他所有表。 圖3-7 “文件”菜單圖3-8 “選擇名稱”對話框2修改表 （1）

5、啟動“SQL Server Management Studio”，在“對象資源管理器”中依次展開【數據庫】節(jié)點、【books】數據庫節(jié)點。 （2）在【Book】表上右鍵單擊，在彈出的快捷菜單中選擇【修改】，如圖所示 。（3）在打開的設計表窗口中，對列的屬性進行修改，當所有內容修改完后，單擊工具欄上的按鈕進行保存，完成表的修改。 （4）重命名表。 3查看表 4刪除表 二、課堂案例2使用T-SQL創(chuàng)建與管理表 【案例學習目標】 掌握用Transact-SQL語句管理books數據庫Book數據表的方法?！景咐R要點】 數據表的創(chuàng)建、修改、查看和刪除操作?！景咐瓿刹襟E】1創(chuàng)建表使用T-SQL語句

6、創(chuàng)建表的基本語法格式如下：CREATE TABLE (列級完整性約束條件,列級完整性約束條件,)2修改表創(chuàng)建完一個表以后，可以使用ALTER TABLE語句來修改表的結構，包括添加新列、增加新約束條件、修改原有的列定義和刪除已有的列和約束條件。其基本語法格式如下：ALTER TABLE ALTER COLUMNADD完整性約束DROP3查看表使用存儲過程sp_help可以查看表的相關信息。存儲過程sp_help的基本語句格式為：sp_help 表名如果要查看Book表的詳細信息，該操作的T-SQL語句為：sp_help Book執(zhí)行該語句可看到Book表的詳細信息。 4刪除表使用DROP TA

7、BLE可以刪除數據庫的表，其基本語句格式如下：DROP TABLE 如果要刪除books數據庫中的Book表，該操作的T-SQL語句為：DROP TABLE Book可以用一條DROP TABLE語句刪除多個表，表名之間要用逗號隔開。但是用這種方法不能刪除系統(tǒng)表。 課堂實踐2 【任務1】 啟動“SQL Server Management Studio”，在數據庫中創(chuàng)建讀者信息表Reader。包含以下字段：讀者編號、姓名、性別、部門、讀者類型、聯系電話等。 修改Reader表。 查看表的信息。 刪除所建的Reader表?！救蝿?】使用T-SQL語句實現以上操作。 表數據操作 一、課堂案例3使用S

8、SMS操作表數據【案例學習目標】 學會用圖形界面方式操作books數據庫中表的數據?！景咐R要點】 數據的插入、更新與刪除?！景咐瓿刹襟E】（1）啟動SQL Server Management Studio，在“對象資源管理器”中，展開數據庫節(jié)點【books】，選中要操作的Book表，右擊，在彈出的菜單中選擇【打開表】，在窗口右側就打開了查詢表數據的窗口。 （2）插入數據時，將光標定位在空白行某個字段的編輯框中，就可以輸入新的數據，編輯完成后選中其他行就可以完成輸入。（3）如果要刪除某條記錄，只需選擇行頭，然后右擊，在彈出的快捷菜單中選擇【刪除】命令。（4）如果要修改某條記錄，選擇該記錄所對

9、應的字段項就可以直接修改。 二、課堂案例4使用T-SQL操作表數據 【案例學習目標】 學會用T-SQL語句操作books數據庫中的表數據?！景咐R要點】 數據的插入、更新與刪除?！景咐瓿刹襟E】 1使用INSERT 命令向表中添加數據使用INSERT INTO語句可以向表中添加數據，其基本的語法格式如下：INSERT INTO ,VALUES (,)例如，要向數據庫Books的Book表中添加以下列的數據：CISBN、VCLCID、cpublisherID、vBookName和cEmpID，數據分別是（978-7-151-05678-9，TP035，P0222，E0008），該操作的T-SQ

10、L語句為：USE booksGOINSERT INTO Book(CISBN,VCLCID,cpublisherID,vBookName,cEmpID)VALUES(978-7-151-05678-9,TP035,P0222,數據庫原理與應用實例教程,E0008)插入數據后的表記錄 2使用UPDATE命令修改表中的數據 修改表中數據可用UPDATE命令完成，其語法格式如下：UPDATE SET =,=FROM WHERE 例如，將Book表中CISBN值為978-7-151-05678-9的圖書名稱改為“數據庫原理與應用案例教程”。該操作的T-SQL語句為：UPDATE BookSET vBo

11、okName=數據庫原理與應用案例教程WHERE CISBN=978-7-151-05678-93使用DELETE或TRANCATE TABLE刪除表中的數據 刪除表中數據用DELETE語句來完成。其語法格式如下：DELETE FROM WHERE刪除表中所有記錄也可以用TRANCATE TABLE語句，其語法格式為：TRANCATE TABLE 表名例如，刪除Book表中CISBN值為978-7-151-05678-9的記錄，其T-SQL語句為：DELETE FROM BookWHERE CISBN=978-7-151-05678-9課堂實踐3 【任務1】使用T-SQL語句在數據庫books

12、的Reader表中添加完整的5條記錄?！救蝿?】在Reader表中，將讀者姓名為“陳茜玲”的讀者名稱改為“楊茜玲”?！救蝿?】將Reader表中部門編號為“D0010”的讀者聯系電話改為“22783857”?！救蝿?】刪除Reader表中讀者姓名為“曾玲”的讀者信息。 索引操作一、索引基礎知識 索引是一個列表，這個列表中包含了某個表中一列或若干列的集合以及這些值的記錄在數據表中存儲位置的物理地址。 1索引的作用使用索引可以大大提高系統(tǒng)的性能，其具體體現在以下幾個方面。（1）加快數據查詢 （2）加快表的連接、排序和分組工作 建立索引的一般原則如下：對經常用來搜索數據記錄的字段建立索引。對表中的主

13、鍵字段建立索引。對表中的外鍵字段建立索引。對在查詢中用來連接表的字段建立索引。對經常用來作為排序基準的字段建立索引。2索引的分類從不同的角度，對索引的類型有不同的劃分方法。按存儲結構區(qū)分，有聚集索引和非聚集索引；按數據的唯一性來區(qū)分，有唯一索引和非唯一索引；按鍵列的個數區(qū)分，有單列索引和多列索引。 二、課堂案例5使用SSMS操作索引 【案例學習目標】 學會用圖形界面方式操作books數據庫中Book表的索引?！景咐R要點】 索引的創(chuàng)建、查看與刪除?！景咐瓿刹襟E】（1）啟動SQL Server Management Studio，在“對象資源管理器”中，依次展開各節(jié)點到數據庫Books下的【

14、表】節(jié)點。（2）展開Book表，在【索引】項上右擊，在快捷菜單中選擇【新建索引】命令 。（3）彈出【新建索引】窗口。 （4）在【索引名稱】文本框中，輸入索引的名稱“IX_id”；【索引類型】下拉列表框用于設置索引類型，我們選擇“非聚集”；【唯一】表示創(chuàng)建唯一索引，這里我們不選。（5）單擊【添加】按鈕，彈出如圖所示的對話框，選擇“cpublisherID”列，單擊【確定】按鈕。 （6）返回到【新建索引】窗口，其中“排序順序”列用于設置索引的排列順序，默認為“升序”，如圖所示。 （7）單擊【確定】按鈕完成索引的創(chuàng)建過程。（8）右擊某個索引名稱，依次選擇【編寫索引腳本為】【CREATE到】【新查詢編

15、輯器窗口】，就可以查看到索引的定義語句，如圖所示。 （9）如果要刪除已建的索引，可以右擊某個索引名稱，如圖所示，在快捷菜單中選擇【刪除】命令。 三、課堂案例6使用T-SQL操作索引 【案例學習目標】 學會用T-SQL語句操作books數據庫中Book表的索引。【案例知識要點】 索引的創(chuàng)建、查看與刪除，索引的分析，索引的維護?！景咐瓿刹襟E】1使用CREATE INDEX語句創(chuàng)建索引CREATE UNIQUECLUSTERED|NONCLUSTEREDINDEX 索引名 ON 表名（字段名ASC|DESC,n）例如，在Book表的“cpublisherID”列和“vBookName”列創(chuàng)建名為I

16、X-pb的非聚集、復合索引。運行如下命令：CREATE NONCLUSTERED INDEX IX_pb ON Book(cpublisherID,vBookName)GO2查看索引信息查看表的索引信息可以使用系統(tǒng)存儲過程sp_helpindex，例如查看Book表的索引信息使用以下語句：EXEC sp_helpindex Book運行結果如圖所示。 3刪除索引用DROP INDEX 命令刪除索引，其語法格式如下：DROP INDEX 表名.索引名,例如，要刪除Book表中的索引IX_pb，運行以下命令：DROP INDEX Book.IX_pb4索引的分析 （1）顯示查詢計劃 以圖形方式顯示

17、執(zhí)行計劃。例如，執(zhí)行圖書信息的查詢，顯示執(zhí)行計劃。執(zhí)行以下查詢語句：SELECT * FROM Book B INNER JOIN publisher P ON B.cpublisherID=P.cpublisherIDGO查詢的執(zhí)行計劃 查詢索引的說明 以表格方式顯示計劃通過在查詢語句中設置SHOWPLAN選項，可以選擇是否讓SQL Server顯示查詢計劃。其命令格式如下：SET SHOWPLAN_ALL ON|OFF或SET SHOWPLAN_TEXT ON|OFF例如，查詢圖書信息，以表格的方式顯示執(zhí)行計劃。SET SHOWPLAN_TEXT ONGOSELECT * FROM Boo

18、k B INNER JOIN publisher P ON B.cpublisherID=P.cpublisherIDGO顯示的執(zhí)行計劃如圖所示。（2）數據I/O統(tǒng)計設置是否顯示磁盤I/O統(tǒng)計的命令格式如下：SET STATISTICS IO ON|OFF例如，給出執(zhí)行圖書信息查詢的I/O統(tǒng)計。在查詢分析器中運行以下命令：SET STATISTICS IO ONGOSELECT * FROM Book B INNER JOIN publisher P ON B.cpublisherID=P.cpublisherIDGOSET STATISTICS IO OFF運行結果如圖所示。 5索引的維護

19、（1）統(tǒng)計信息更新使用UPDATE STATISTICS命令更新統(tǒng)計信息，例如，更新Book表主鍵索引的統(tǒng)計信息。運行以下命令：UPDATE STATISTICS Book PK_BookGO（2）使用DBCC SHOWCONTIG語句掃描表例如，利用DBCC SHOWCONTIG獲取Book表主鍵索引的碎片信息。運行以下命令：DBCC SHOWCONTIG (Book,PK_BOOK)GO可使用DBCC INDEXDEFRAG命令對Book表主鍵索引進行碎片整理運行以下命令：DBCC INDEXDEFRAG (Book,PK_BOOK)GO課堂實踐4 【任務1】在Book表的vclcID列上

20、創(chuàng)建非聚集索引ix_clcID。（SSMS）【任務2】在Reader表的vReadercategoryID和vReaderName列上創(chuàng)建復合非聚集索引ix_RC_RN。(SSMS)【任務3】查看所建索引ix_clcID和ix_RC_RN的基本信息。(SSMS)【任務4】刪除所建索引ix_clcID和ix_RC_RN。(SSMS)【任務5】在Reader表的vReaderName列上創(chuàng)建非聚集索引ix_RN。（T-SQL） 數據完整性一、數據完整性概述 1實體完整性 2域完整性 3參照完整性 4用戶定義完整性 二、課堂案例7使用SSMS管理約束 【案例學習目標】 學會用圖形界面方式管理book

21、s數據庫中Book表的約束。【案例知識要點】 主鍵約束，外鍵約束，唯一性約束，檢查約束和默認約束。【案例完成步驟】1主鍵（PRIMARY KEY）約束2外鍵（FOREIGN KEY）約束3唯一性（UNIQUE）約束4檢查（CHECK）約束 5默認（DEFAULT）約束 三、課堂案例8使用T-SQL管理約束 【案例學習目標】 學會用T-SQL語句管理books數據庫中Book表的約束?！景咐R要點】 主鍵約束，外鍵約束，唯一性約束，檢查約束和默認約束。【案例完成步驟】1主鍵（PRIMARY KEY）約束定義PRIMARY KEY約束的基本語法格式如下：CONSTRAINT 約束名 PRIMARY KEY（列或列的組合）2外建（FOREIGN KEY）約束使用T-SQL語句定義FOREIGN KEY約束的語法格式如下：CONSTRAINT 約束名 FOREIGN KEY（列） REFERENCES 被引用表（列）3唯一性（UNIQUE）約束使用T-SQL語句定義唯一性約束的語法格式如下：CONSTRAINT 約束名 UNIQUE（列或列的組合）4檢查（CHECK）約束一般在創(chuàng)建表的語句中定義檢查約束，定義檢查約束的語句如下：CONSTRAINT 約束名