SQLserver2008數(shù)據(jù)庫應用與開發(fā)教程(第二版)ppt課件匯總(完整版)

1、SQLserver2008數(shù)據(jù)庫應用與開發(fā)教程(第二版)1.1 SQL server 2008概述1.1.1 SQL server 2008的體系結構（一）Microsoft SQL Server 2008系統(tǒng)由4個部分組成（二）這4個部分被稱為4個服務數(shù)據(jù)庫引擎Analysis ServicesReporting ServicesIntegration Services1.1.2 數(shù)據(jù)庫和數(shù)據(jù)庫對象1. 數(shù)據(jù)庫的類型和特點Microsoft SQL Server 2008系統(tǒng)提供了兩種類型的數(shù)據(jù)庫（一）系統(tǒng)數(shù)據(jù)庫 （二）用戶數(shù)據(jù)庫 1.1 SQL server 2008概述2. 數(shù)據(jù)庫對象主

2、要的數(shù)據(jù)庫對象包括數(shù)據(jù)庫關系圖、表、視圖、同義詞、存儲過程、函數(shù)、觸發(fā)器、程序集、類型、規(guī)則和默認值等。1.1 SQL server 2008概述1.2 SQL server 2008的安裝1.1.3 SQL Server 2008的新增特點 (1) 簡單的數(shù)據(jù)加密 (2) 外鍵管理 (3) 增強了審查 (4) 改進了數(shù)據(jù)庫鏡像 (5) 加強了可支持性 (6) 熱添加CPU 1.2 SQL Server 2008的安裝1.2.1 系統(tǒng)版本SQL Server 2008企業(yè)版SQL Server 2008標準版SQL Server 2008工作組版SQL Server 2008網(wǎng)絡版SQL Se

3、rver 2008開發(fā)版SQL Server 2008系統(tǒng)的Express版本SQL Server 移動版1.2.2 SQL Server 2008的運行環(huán)境要求硬件環(huán)境要求處理器、內(nèi)存和操作系統(tǒng)要求1.2.3 Microsoft SQL server 2008的安裝1. 安裝SQL server 2008標準版之前應該注意的問題增強物理安全性使用防火墻隔離服務禁用 NetBIOS 和服務器消息塊2. 安裝Microsoft SQL server 2008標準版的步驟1.2.3 Microsoft SQL server 2008的安裝1.3 SQL server 2008的配置1.3.1 注冊

4、服務器1. 注冊服務器2. 修改服務器的注冊3. 刪除服務器1.3.2 配置服務器選項1.4.1 SQL Server Management Studio1.4.2 Business Intelligence Development Studio1.4.3 SQL Server Profiler1.4.4 SQL Server Configuration Manager1.4.5 Database Engine Tuning Advisor1.4.6 實用工具1.4 SQL server 2008常用的管理工具第 2 章數(shù)據(jù)庫的建立與維護 在SQL Server 2008中，用戶如何訪問及使用

5、數(shù)據(jù)庫，就需要正確了解數(shù)據(jù)庫中所有對象及其設置。數(shù)據(jù)庫就像一個容器，它里面除了存放著數(shù)據(jù)的表之外，還有視圖、存儲過程、觸發(fā)器、約束等數(shù)據(jù)庫對象。數(shù)據(jù)庫管理的核心任務包括創(chuàng)建、操作和維護數(shù)據(jù)庫。 本章的學習目標：了解系統(tǒng)數(shù)據(jù)庫的作用了解數(shù)據(jù)庫對象及構成掌握數(shù)據(jù)庫快照的使用掌握用SSMS和T-SQL創(chuàng)建數(shù)據(jù)庫的方法掌握用SSMS和T-SQL修改、刪除數(shù)據(jù)庫的操作掌握如何查看數(shù)據(jù)庫狀態(tài)熟悉如何對數(shù)據(jù)庫中的物理空間進行科學的設置掌握數(shù)據(jù)庫的分離和附加2.1 數(shù)據(jù)庫的組成 在SQL Server 2008中，數(shù)據(jù)庫是表、視圖、存儲過程、觸發(fā)器等數(shù)據(jù)庫對象的集合，是數(shù)據(jù)庫管理系統(tǒng)的核心內(nèi)容。為了更好地學習

6、并理解數(shù)據(jù)庫，首先來了解一個數(shù)據(jù)庫的組成及其數(shù)據(jù)庫中的數(shù)據(jù)文件、事務日志文件及文件組等基本概念。2.1.1 系統(tǒng)數(shù)據(jù)庫 這些數(shù)據(jù)庫分別是master、model、msdb、tempdb和resource數(shù)據(jù)庫。這些系統(tǒng)數(shù)據(jù)庫對于用戶來說，不要隨意修改它們。1. master數(shù)據(jù)庫Master數(shù)據(jù)庫用于記錄SQL Server實例的所有系統(tǒng)級信息，是SQL Server的核心，不能對其進行直接修改，應當對其定期進行備份，如果Master數(shù)據(jù)庫變得不可用，那么SQL Server數(shù)據(jù)庫引擎將無法啟動。 2. model數(shù)據(jù)庫model數(shù)據(jù)庫用作SQL Server 2008實例上創(chuàng)建的所有數(shù)據(jù)庫的

7、模板，若對model數(shù)據(jù)庫進行修改，都將應用于以后創(chuàng)建的用戶數(shù)據(jù)庫中。3. msdb數(shù)據(jù)庫msdb數(shù)據(jù)庫用于SQL Server 2008代理計劃警報和作業(yè)，是SQL Server中的一個Windows服務。4. resource數(shù)據(jù)庫resource數(shù)據(jù)庫是一個只讀和隱藏的數(shù)據(jù)庫，它包含了SQL Server 2008中的所有系統(tǒng)對象，對于resource數(shù)據(jù)庫是唯一沒有顯示在其中的系統(tǒng)數(shù)據(jù)庫，這是因為它在sys框架中存在。2.1.2 用戶數(shù)據(jù)庫用戶數(shù)據(jù)庫是用戶根據(jù)自己的管理需求進行創(chuàng)建的數(shù)據(jù)庫，便于自己管理相應的數(shù)據(jù)。例如：圖書館可以針對圖書的管理創(chuàng)建圖書管理數(shù)據(jù)庫，大型超市可以針對貨品創(chuàng)

8、建超市管理數(shù)據(jù)庫等。2.1.3 示例數(shù)據(jù)庫 示例數(shù)據(jù)庫是系統(tǒng)為了讓用戶學習和理解SQL Server而設計的。Northwind和pubs示例數(shù)據(jù)庫是SQL Server 2000中的示例數(shù)據(jù)庫；Adventure Works示例數(shù)據(jù)庫是SQL Server2005中的示例數(shù)據(jù)庫。2.1.4 數(shù)據(jù)庫文件及其文件組在SQL Server 2008 中，一個數(shù)據(jù)庫至少需要有一個數(shù)據(jù)文件和一個事務日志文件。也可以有多個數(shù)據(jù)文件和多個事務日志文件。數(shù)據(jù)庫中的數(shù)據(jù)文件用于存放數(shù)據(jù)庫的數(shù)據(jù)和各種對象，而事務日志文件用于存放事務日志。數(shù)據(jù)庫中存放數(shù)據(jù)文件和日志文件的默認位置為：C:Program Files

9、Microsoft SQL Server MSSQL10.MSSQLSERVER MSSQL DATA的文件夾下。 數(shù)據(jù)庫的文件組成：（通?？梢杂扇愇募M成） 主數(shù)據(jù)文件：默認的擴展名為.mdf 輔助數(shù)據(jù)文件：默認的擴展名為.ndf 事務日志文件：默認的擴展名為.ldf 配置事務日志： 建立好數(shù)據(jù)庫之后，在進行具體的操作之前應當首先配置事務日志。事務日志文件記錄SQL Server 對這個數(shù)據(jù)庫的操作，以便于在系統(tǒng)出現(xiàn)故障時恢復數(shù)據(jù)庫。 存儲數(shù)據(jù)的方法：日志文件: .ldf數(shù)據(jù)文件:.mdf or .ndf區(qū): 8 個連續(xù)的 8 KB 頁頁: 8 KB文件組： 文件組就是文件的邏輯集合。文件

10、組也分為主要文件組、用戶定義文件組和默認文件組。文件組可以把一些指定的文件組合在一起，方便管理和分配數(shù)據(jù)。數(shù)據(jù)庫對象： 數(shù)據(jù)庫對象是存儲、管理和使用數(shù)據(jù)不同結構形式，主要包括表、視圖、存儲過程、觸發(fā)器、索引等。 使用SSMS圖形界面創(chuàng)建數(shù)據(jù)庫 在SQL Server 2008 中，通過SQL Server Management Studio 創(chuàng)建數(shù)據(jù)庫使用Transact-SQL 語句創(chuàng)建數(shù)據(jù)庫該命令的語法如下： CREATE DATABASE database_name ON PRIMARY ，n ， ，n LOG ON ，n FOR RESTORE :=（NAME=logical_file

11、_name， FILENAME=os_file_name ，SIZE=size ，MAXSIZE=max_size|UNLIMITED ，F(xiàn)ILEGROWTH=growth_increment ） ，n :=FILEGROUP filegroup_name ，n數(shù)據(jù)庫的查看和修改 用SSMS圖形界面進行查看和修改在【對象資源管理器】窗口中，展開【數(shù)據(jù)庫】節(jié)點，用右鍵單擊目標數(shù)據(jù)庫（如：學生信息StuInfo數(shù)據(jù)庫或Test數(shù)據(jù)庫等）從彈出的快捷菜單中選擇【屬性】命令，彈出【數(shù)據(jù)庫屬性StuInfo】的“常規(guī)”窗口 使用T-SQL語句進行修改數(shù)據(jù)庫 使用ALTER DATABASE語句修改數(shù)據(jù)庫

12、語法格式如下：ALTER DATABASE database ADD FILE ,.,n TO FILEGROUP filegroup_name | ADD LOG FILE ,.,n | REMOVE FILE logical_file_name | ADD FILEGROUP filegroup_name| REMOVE FILEGROUP filegroup_name| MODIFY FILE 刪除數(shù)據(jù)庫 使用SQL Server Management Studio圖形化界面刪除數(shù)據(jù)庫使用Transact-SQL語句刪除數(shù)據(jù)庫使用Transact-SQL語句刪除數(shù)據(jù)庫的語法如下：DROP

13、 DATABASE database ,n2.2.5 數(shù)據(jù)庫操作通過前面的介紹，可以掌握數(shù)據(jù)庫的創(chuàng)建方法，如何修改數(shù)據(jù)庫大小、名稱和屬性，如何刪除數(shù)據(jù)庫和查看數(shù)據(jù)庫狀態(tài)及信息，這些都是針對數(shù)據(jù)庫進行的操作。除了這些，常見的操作還包括本節(jié)介紹的分離數(shù)據(jù)庫和附加數(shù)據(jù)庫2.2.6 復制和移動數(shù)據(jù)庫SQL Server具有強大的復制功能，除了將數(shù)據(jù)和數(shù)據(jù)庫對象從一個數(shù)據(jù)庫復制并準確分發(fā)的另一個數(shù)據(jù)庫中，還要實行數(shù)據(jù)庫之間的同步。SQL Server的復制分為三種:1. 快照復制2. 事務復制3. 合并復制第3章 表的建立與維護本章的學習目標：掌握用SSMS和T-SQL創(chuàng)建表熟悉各種列數(shù)據(jù)類型掌握列的各

14、種屬性掌握用SSMS和T-SQL修改、刪除表熟悉保持數(shù)據(jù)完整性的各種約束掌握根據(jù)完整性規(guī)則的要求使用SSMS和 T-SQL設置表的主鍵、外鍵和約束等3.1 表概述在使用數(shù)據(jù)庫的過程中，接觸最多的莫過于表。表是存儲數(shù)據(jù)的地方，是一種結構化的文件，可用來存儲一些特定數(shù)據(jù)類型的數(shù)據(jù)，是數(shù)據(jù)庫中最重要的部分。3.1.1 什么是表在關系數(shù)據(jù)庫中，每一個關系都表現(xiàn)為一張表。表是用來存儲數(shù)據(jù)和操作數(shù)據(jù)的邏輯結構，關系數(shù)據(jù)庫中的所有數(shù)據(jù)都表現(xiàn)為表的形式，由列和行組成，如圖3-1所示。關系數(shù)據(jù)庫由表、具體查詢等對象組成，而查詢等對象又是通過表來呈現(xiàn)的。表的數(shù)據(jù)類型 種類數(shù)據(jù)類型 數(shù)字整數(shù)int, bigint,

15、 smallint, tinyint精確數(shù)值decimal, numeric近似數(shù)值float, real貨幣money, smallmoney日期和時間 datetime, smalldatetime字符Non-Unicodechar, varchar, varchar(max), textUnicodenchar, nvarchar, nvarchar(max), ntext二進制 binary, varbinary, varbinary(max)圖像 image全局標識符 uniqueidentifierXMLxml特殊 bit, cursor, timestamp, sysname,

16、table, sql_variant使用SQL Server Management Studio創(chuàng)建表 使用T-SQL 語句創(chuàng)建表 CREATE TABLE database_name.owner.|owner. table_name （ |column_name AS computed_column_expression | ，n） ON filegroup | DEFAULT TEXTIMAGE_ON filegroup|DEFAULT := column_name data_type DEFAULT constant_expression |IDENTITY（seed,increment

17、 ） .n 3.2 列數(shù)據(jù)類型在計算機中，按照數(shù)據(jù)描述信息的含義，將數(shù)據(jù)分為不同的種類，稱之為數(shù)據(jù)類型。3.2.1 數(shù)據(jù)類型的分類SQL Server提供兩類數(shù)據(jù)類型：一類是用戶定義數(shù)據(jù)類型，用戶可以根據(jù)需要創(chuàng)建自己的數(shù)據(jù)類型，會對數(shù)據(jù)組織帶來一定的好處。一類是基本數(shù)據(jù)類型，是SQL Server系統(tǒng)提供的數(shù)據(jù)類型。本書只介紹基本數(shù)據(jù)類型。種 類數(shù) 據(jù) 類 型數(shù)字數(shù)據(jù)類型int,bigint,smallint,tinyint,decimal,numeric,float,real,money,smallmoney,bit字符數(shù)據(jù)類型char, varchar, nchar, nvarchar,

18、ntext text日期和時間datetime, smalldatetime,date,time,datetime2,datetimeoffset二進制數(shù)據(jù)類型binary, varbinary,image其他數(shù)據(jù)類型uniqueidentifier,xml, timestamp, sql_variantSQL Server中常用的數(shù)據(jù)類型3.2.2 數(shù)字數(shù)據(jù)類型數(shù)字數(shù)據(jù)類型表示符號用途精確數(shù)字類型整數(shù)數(shù)據(jù)類型int、bigint、smallint、tinyint存儲數(shù)字數(shù)據(jù)，直接進行數(shù)據(jù)運算位數(shù)據(jù)類型bit用于一些條件邏輯判斷貨幣數(shù)據(jù)類型money、smallmoney存儲貨幣數(shù)據(jù)近似數(shù)字類

19、型十進制小數(shù)型decimal、numeric存儲帶固定精度和位數(shù)的小數(shù)浮點數(shù)據(jù)類型float、real存儲范圍非常大的數(shù)字，用于表示指定數(shù)的大致數(shù)據(jù)值3.2.3 字符數(shù)據(jù)類型字符類型表示符號用法ANSI字符char存儲指定長度的字符串varchar存儲可變長度字符text存儲大量非Unicode字符Unicode字符nchar用于存儲Unicode字符nvarchar與varchar類似，存儲可變長度Unicode字符ntext與text類似，存儲大量Unicode字符數(shù)據(jù)3.2.4 日期時間數(shù)據(jù)類型日期時間數(shù)據(jù)類型用于存儲日期和時間信息。在SQL Server 2008之前的版本中，日期時間

20、類型只包括datetime和smalldatetime兩種類型。它們的差別在于其表示的日期和時間范圍不同、時間精確度也不同。其中最常用的是datetime型數(shù)據(jù)。3.2.5 二進制數(shù)據(jù)類型二進制數(shù)據(jù)類型用于表示位數(shù)據(jù)流，包括binary（固定長度）、varbinary（可變長度）和image三種。binary用于存儲固定長度的二進制數(shù)據(jù)；varbinary用于存儲可變長度的二進制數(shù)據(jù)，若存儲的二進制大于8000字節(jié)，就必須使用varbinary（max）數(shù)據(jù)類型；image數(shù)據(jù)類型用于存儲圖像信息，在SQL Server 2008中，只有在數(shù)據(jù)的字節(jié)數(shù)超過了8KB的情況下，才使用image數(shù)據(jù)

21、類型，其他情況應使用varbinary（max）代替，其中max最大可以達到231-1字節(jié)；3.2.6 其他數(shù)據(jù)類型除了上述數(shù)據(jù)類型外，SQL Server還提供了其他幾類常用數(shù)據(jù)類型，使用這些數(shù)據(jù)類型可以完成特殊數(shù)據(jù)對象的定義、存儲和使用：timestampsql_variant類型uniqueidentifier唯一標識符類型3.2.7 數(shù)據(jù)類型的選擇數(shù)據(jù)的使用情況：數(shù)據(jù)是用于數(shù)學運算？表示日期或者時間？存儲文本信息？不同的使用情況決定了不同數(shù)據(jù)類型的選擇；數(shù)據(jù)的大小：選擇的數(shù)據(jù)類型能否存放期望存儲的最大值。例如，選擇整型數(shù)據(jù)，要根據(jù)數(shù)據(jù)的取值決定使用INT型數(shù)據(jù)還是BIGINT型數(shù)據(jù)；選

22、擇字符型數(shù)據(jù)，需要考慮實際需要的最大字符長度；正確的存儲信息：例如使用integer數(shù)據(jù)類型存儲貨幣值，將造成小數(shù)部分丟失，這將導致不正確的值；非英語字符：在SQL Server 2008系統(tǒng)中，如果某些列需要存儲非英語字符（如中文字符），建議最好使用NCHAR、NVARCHAR數(shù)據(jù)類型。3.3 列的其他屬性3.3.1 默認約束默認約束指用戶在進行插入操作時，沒有顯示地為列提供數(shù)據(jù)，那么系統(tǒng)將把默認值賦給該列。默認值約束所提供的默認值可以為常量、函數(shù)、系統(tǒng)函數(shù)、空值等，表中的每一列只能定義一個默認約束，對于具有IDENTITY屬性和timestamp數(shù)據(jù)類型的字段，不能使用默認約束，同時，定義

23、的默認值長度不允許大于對應字段所允許的最大長度。DEFAULT 約束 使用SSMS圖形化界面創(chuàng)建默認約束 用Transact-SQL語句創(chuàng)建默認約束。其語法形式如下： CONSTRAINT constraint_name DEFAULT constraint_expression FOR column_name 3.3.2 空值約束空值約束即是否允許該字段的值為NULL，即空值。主鍵列不允許為空值，否則就失去了唯一標識的意義。NULL 約束 使用SSMS圖形化界面設置空值約束。用Transact-SQL語句創(chuàng)建空值約束。其語法形式如下：直接在相應的語句后書寫 NULL | NOT NULL 3

24、.3.3 IDENTITY 的應用 identity表示的是最近一次向具有identity屬性(即自增列)的表插入數(shù)據(jù)時對應的自增列的值，是系統(tǒng)定義的全局變量。比如有個表A，它的自增列是id，當向A表插入一行數(shù)據(jù)后，如果插入數(shù)據(jù)后自增列的值自動增加至101，則通過select identity得到的值就是101。3.4 向表中添加數(shù)據(jù)首先在需要添加數(shù)據(jù)的表上單擊鼠標右鍵，從彈出的快捷菜單中選擇“打開表”命令，出現(xiàn)如圖3-8所示的“表”對話框，在其中可向打開的表添加數(shù)據(jù)。本節(jié)向各個表中添加的數(shù)據(jù)如表3-53-7所示。3.5 查看表 3.5.1 查看表中的有關信息打開指定的數(shù)據(jù)庫，在需要查看的表上

25、單擊鼠標右鍵，從彈出的快捷菜單中選擇“屬性”命令，將打開“表屬性”對話框，如圖3-10所示?！俺Ｒ?guī)”選項頁中顯示了該表格的定義，包括存儲結構、當前的連接及名稱等屬性，該選項頁中顯示的屬性不能修改。3.5.2 查看表中存儲的數(shù)據(jù)在表“課程表”上單擊鼠標右鍵，在彈出的快捷菜單中選擇“打開表”命令，顯示“課程表”中的數(shù)據(jù)，如圖3-11所示。3.5.3 查看表與其他數(shù)據(jù)對象的依賴關系在要查看的表上單擊鼠標右鍵，從彈出的快捷菜單中選擇“查看依賴關系”命令，打開“對象依賴關系”對話框，該對話框顯示了該表依賴的其他數(shù)據(jù)對象和依賴于此表的依賴對象。例如，查看“選課表”的依賴對象，如圖3-12所示的對話框顯示了

26、選課表依賴于課程表與學生表。3.6 修改表3.6.1 使用SSMS圖形化界面修改表在“對象資源管理器”窗口中，展開“數(shù)據(jù)庫”節(jié)點，再展開具體選擇的數(shù)據(jù)庫，然后展開“表”節(jié)點，用右鍵單擊要修改的表，從彈出的快捷菜單中選擇“設計”命令，打開“表設計器”，即可對表的定義進行修改，方法同創(chuàng)建表。3.6.2 使用T-SQL語句修改表 ALTER TABLE 表名 (ALTER COLUMN 列名 列定義， ADD 列名 數(shù)據(jù)類型 約束， DROP 列名， ADD CONSTRAINT 約束名 約束， )例【3-2】：在AdventureWorks數(shù)據(jù)庫中，在 Sales下創(chuàng)建名為 CustomerOrd

27、ers 的表；創(chuàng)建OrderID、OrderDate、CustomerID、Notes 列，并定義其數(shù)據(jù)類型為int、datetime、int、nvarchar(200)和可空性(Notes列為空)，指定OrderID列為標志列。T-SQL語句如下：CREATE TABLE Sales.CustomerOrders ( OrderID int identity(1,1), OrderDate datetime, CustomerID int, Notes nvarchar(200) NULL )；例【3-3】：基于例3-2定義的 Sales.CustomerOrders 表，添加列和更改列的可

28、空性。- 修改 Sales.CustomerOrders 表的定義- 在表中添加名為SalesPersonID 的列，并定義其數(shù)據(jù)類型為int，指其不可空 ALTER TABLE Sales.CustomerOrders ADD SalesPersonID int NOT NULL GO - 修改 Sales.CustomerOrders 表的定義 - 修改 Notes 列，并定義其數(shù)據(jù)類型為nvarchar(200)，指定其不可空 ALTER TABLE Sales.CustomerOrders ALTER COLUMN Notes nvarchar(200) NOT NULL GO3.6.

29、3 使用INSERT SELECT語句INSERT SELECT語句可以將其他數(shù)據(jù)源中的行添加到數(shù)據(jù)庫中現(xiàn)有的表當中。這種方法用于插入的數(shù)據(jù)是不確定(一般都多于一條)。具體方法是：通過SELECT語句生成的結果集，再結合INSERT語句，就可以把結果集插入到指定的表中。例【3-4】：求每個學生的平均成績，并按學號、姓名、平均成績存入學生信息數(shù)據(jù)庫。首先創(chuàng)建一個表：CREATE TABLE AG( 學號 CHAR(8)PRIMARY KEY, 姓名 CHAR(8),平均成績 SMALLINT)GO;3.6.4 使用SELECT INTO語句SELECT INTO語句與INSERT SELECT語

30、句不同的是：它可以將其他數(shù)據(jù)源中的任何查詢結果或?qū)氲臄?shù)據(jù)都添加到數(shù)據(jù)庫的新表當中。例【3-5】：統(tǒng)計每個學生未通過課程的門數(shù)，將其保存到臨時表#stuexam表。顯示系名、學號、姓名、未通過門數(shù)，并按系名排序。T-SQL語句如下：SELECT MIN(系名), SC.學號, MIN(姓名), COUNT(*) 未通過門數(shù)INTO #stuexamFROM Student, SCWHERE Student.學號=SC.學號 AND 成績 60GROUP BY SC.學號ORDER BY 系名;3.7 刪除表1. 使用SSMS圖形化界面刪除表在“對象資源管理器”窗口中，展開“數(shù)據(jù)庫”節(jié)點，再展開

31、所選擇的具體數(shù)據(jù)庫節(jié)點，然后展開“表”節(jié)點，用右鍵單擊要刪除的表，從彈出的快捷菜單中選擇“刪除”命令，打開“刪除對象窗口”，2. 使用T-SQL語句刪除表DROP TABLE 表名例【3-6】：在AdventureWorks數(shù)據(jù)庫中，刪除CustomerOrders表，它的架構是Sales。 USE AdventureWorksGODROP TABLE Sales.CustomerOrders第4章 表數(shù)據(jù)操作本章我們學習使用T-SQL語句創(chuàng)建和操作數(shù)據(jù)庫和表。與圖形用戶界面方式相比，用T-SQL命令方式更為靈活。本章的學習目標：操縱數(shù)據(jù)需要解決的問題使用INSERT語句插入數(shù)據(jù)使用UPDAT

32、E語句更新數(shù)據(jù)使用DELETE語句刪除數(shù)據(jù)理解數(shù)據(jù)加密的方式和特點4.1 概述表創(chuàng)建之后，只是一個空表。如何向表中添加數(shù)據(jù)？如果表中已經(jīng)有了數(shù)據(jù)，不合適或不正確，怎么辦?如果表中的數(shù)據(jù)不再需要了，怎么辦？這些都是數(shù)據(jù)操縱問題。用戶可以使用INSERT、UPDATE、DELETE等語句來解決這些問題。4.2 界面操作表數(shù)據(jù)與創(chuàng)建數(shù)據(jù)庫和表一樣，把不直接使用T-SQL語句對表數(shù)據(jù)的操作稱為界面操作表數(shù)據(jù)。界面操作表數(shù)據(jù)主要在SQL Server Management Studio 中進行。在選擇了“打開表”后，將進入操作所選擇的表數(shù)據(jù)窗口，在此窗口中，表中的記錄按行顯示，每個記錄占一行。在此界面中

33、，可向表中插入記錄、也可刪除和修改記錄。4.2.1 插入記錄4.2.2 刪除記錄4.2.3 修改記錄4.3 數(shù)據(jù)操縱語言(DML)數(shù)據(jù)操縱語言主要是用于操縱表、視圖中數(shù)據(jù)的語句。當我們創(chuàng)建表對象之后，初始狀態(tài)時該表是空的，沒有任何數(shù)據(jù)。這時需要使用INSERT語句向表中添加數(shù)據(jù)。如何檢索表中數(shù)據(jù)呢？可以使用我們前面介紹的SELECT語句。如果表中數(shù)據(jù)不正確的，那么可以使用UPDATE語句進行更新。也可以使用DELETE語句刪除表中的數(shù)據(jù)。DML就是指處理數(shù)據(jù)變更的3種SQL語句：INSERT、UPDATE和DELETE。4.3 命令操作表數(shù)據(jù)對表數(shù)據(jù)的插入、修改和刪除還可以通過T-SQL語句來

34、進行，與界面操作表數(shù)據(jù)相比，通過T-SQL語句操作表數(shù)據(jù)更為靈活，功能更為強大。4.3.1 INSERT語句INSERT語句用于向已經(jīng)存在的表中插入新的數(shù)據(jù)。我們需要聲明向哪個表中插入數(shù)據(jù)、向那一列插入數(shù)據(jù)，以及插入什么數(shù)據(jù)。INSERT語句的語法格式為：INSERT INTO (列1，列2，)VALUES (表達式1，表達式2，)【例4-35】 向course表插入一條記錄，課程號為10，課程名為network，學分為4，任課教師為Sophie。T-SQL語句為：INSERT INTO course VALUES (10,network,4,Sophie)【例4-36】向student表插入

35、一條學生記錄，其中學號為20090101，學生姓名為張靜。T-SQL語句為：INSERT INTO student(sno,sname) VALUES (20090101,N張靜)由于只插入student表中的兩列的數(shù)據(jù)，因此，使用列表需要指明所要插入的列。執(zhí)行完成后，查看student表，可以看到課程已經(jīng)被添加。4.3.2 UPDATE語句如果表中的數(shù)據(jù)不正確或者已經(jīng)變化，可以使用UPDATE語句更新這些不恰當?shù)臄?shù)據(jù)。插入新數(shù)據(jù)和更新已經(jīng)存在的數(shù)據(jù)之間的主要差別在于需要指明要更改哪些記錄。一般需要用WHERE子句來指明需要更新的記錄所滿足的條件。UPDATE語句的語法格式為：UPDATE S

36、ET 列1=，列2=，F(xiàn)ROM 表名WHERE 【例4-37】將course表中的課程名稱為network的記錄項更改為課程名稱為database。T-SQL語句為：UPDATE course SET cname=Ndatabase WHERE cname=Nnetwork執(zhí)行完成后，使用SELECT語句查詢course表可以看到，對應紀錄已經(jīng)被修改。4.3.3 DELETE語句使用DELETE語句可以刪除表中的數(shù)據(jù)。一般地，如果在DELETE語句中沒有刪除條件，那么將刪除表中的所有數(shù)據(jù)。需要注意的是，DELETE語句與DROP語句不同：DELETE語句刪除表中的數(shù)據(jù)，但是該表依然存在，而DR

37、OP語句則刪除了表，表中的數(shù)據(jù)自然也不存在了。DELETE語句的語法非常容易，只需要簡單指定從哪一個表中刪除了記錄，如果需要，則用WHERE子句指定要刪除哪些記錄。格式為：DELETE FROM WHERE 條件【例4-38】將student表中學生姓名為張靜的記錄刪除。T-SQL語句為：DELETE FROM student WHERE sname=N張靜執(zhí)行完成后，使用SELECT語句查詢student表可以看到，對應紀錄已經(jīng)被刪除。第5章 安全與權限第5章 安全與權限 5.1 安全與權限的基礎知識5.1.1 SQL server 2008安全機制的總體策略遠程網(wǎng)絡主機通過Internet

38、訪問SQL server 2008服務器所在的網(wǎng)絡，這由網(wǎng)絡環(huán)境提供某種保護機制。(2) 網(wǎng)絡中的主機訪問SQL server 2008服務器，這首先要求對SQL Server進行正確配置，其內(nèi)容將要在下一節(jié)中介紹；其次是要求擁有對SQL server 2008實例的訪問權 登錄名。(3) 訪問SQL server 2008數(shù)據(jù)庫，這要求擁有對SQL server 2008數(shù)據(jù)庫的訪問權數(shù)據(jù)庫用戶。(4) 訪問SQL server 2008數(shù)據(jù)庫中的表和列，這要求擁有對表和列的訪問權 權限。安全對象Server 角色SQL Server 登錄Windows 組域用戶賬戶本地用戶賬戶用戶數(shù)據(jù)庫角

39、色應用程序角色組SQL Server數(shù)據(jù)庫Windows文件密鑰服務器架構數(shù)據(jù)庫安全對象權限主體5.1.2 安全對象SQL Server2008的安全機制SQL Server2008的安全機制可以分為四個級別：操作系統(tǒng)的安全性SQL Server2008的登錄安全性數(shù)據(jù)庫的使用安全性數(shù)據(jù)庫對象的使用安全性SQL Server 2008 權限Server 角色SQL Server 登錄Windows 組域用戶賬戶本地用戶賬戶用戶數(shù)據(jù)庫角色應用程序角色組SQL Server數(shù)據(jù)庫Windows文件密鑰CREATEALTERDROPCONTROLCONNECTSELECTEXECUTEUPDATED

40、ELETEINSERTTAKE OWNERSHIPVIEW DEFINITIONBACKUP授予/撤銷/拒絕ACL服務器架構數(shù)據(jù)庫安全對象權限主體5.1.3 SQL Server 2008 權限Windows 身份驗證模式 用戶由 Windows 授權，適用于當數(shù)據(jù)庫僅在組織內(nèi)部訪問時。 通過登錄而被授予 SQL Server 的訪問權 混合身份驗證模式 用戶通過一個受信任連接連接到 SQL Server 并使用 Windows 身份驗證來訪問 SQL Server 適用于當外界的用戶需要訪問數(shù)據(jù)庫時或當用戶不能使用WINDOWS域時。SQL Server 身份驗證模式5.2.1 SQL Se

41、rver 2008的身份認證模式 1Windows 身份驗證模式 當使用Windows身份驗證連接到SQL Server時， Microsoft Windows將完全負責對客戶端進行身份驗證。在這種情況下，將按其Windows用戶賬戶來識別客戶端。當用戶通過Windows用戶賬戶進行連接時，SQL Server使用Windows操作系統(tǒng)中的信息驗證賬戶名和密碼，這是SQL Server默認的身份驗證模式，比混合模式安全的多。 5.2.1 SQL Server 2008的身份認證模式2混合身份驗證模式混合驗證模式允許以SQL Server身份驗證模式或者Windows身份驗證模式來進行驗證。使用

42、哪個模式取決于在最初的通信時使用的網(wǎng)絡庫。如果一個用戶使用TCP/IP Sockets進行登錄驗證，則使用SQL Server身份驗證模式；如果用戶使用命名管道，則登錄時將使用Windows驗證模式。這種模式能更好地適應用戶的各種環(huán)境。 5. 設置身份驗證模式 通過圖形化界面設置身份驗證模式 第步 ：打開SQL Server Management studio,使用WINDOWS或SQL SERVER身份驗證建立連接。 第2步：對象資源管理器-服務器右擊-屬性-服務器屬性 第3步： 選擇安全性-在此設置身份驗證模式5.3 數(shù)據(jù)庫賬戶 在SQL Server中，賬號有兩種：一種是登錄服務器的登錄

43、賬號，另外一種就是使用數(shù)據(jù)庫的用戶賬號。登錄賬號只是讓用戶登錄到SQL Server中，登錄名本身并不能讓用戶訪問服務器中的數(shù)據(jù)庫。 要訪問特定的數(shù)據(jù)庫，還必須具有用戶名。用戶名在特定的數(shù)據(jù)庫內(nèi)創(chuàng)建，并關聯(lián)一個登錄名（當一個用戶創(chuàng)建時，必須關聯(lián)一個登錄名）。通過授權給用戶來指定用戶可以訪問的數(shù)據(jù)庫對象的權限。 5.3.1 服務器的登錄賬號 創(chuàng)建登錄賬號時需要指出該賬號使用的是Windows身份驗證還是使用的SQL Server身份驗證。 1使用Windows身份驗證的登錄2使用SQL Server身份驗證的登錄5.3.2 數(shù)據(jù)庫用戶賬戶 一般情況下，用戶登錄SQL Server實例后，還不具備

44、訪問數(shù)據(jù)庫的條件。在用戶可以訪問數(shù)據(jù)庫之前，管理員必須為該用戶在數(shù)據(jù)庫中建立一個數(shù)據(jù)庫賬號作為訪問該數(shù)據(jù)庫的ID。這個過程就是將SQL Server登錄賬號映射到需要訪問的每個數(shù)據(jù)庫中，這樣才能夠訪問數(shù)據(jù)庫。如果數(shù)據(jù)庫中沒有用戶賬戶，則即使用戶能夠連接到SQL Server實例也無法訪問到該數(shù)據(jù)庫。 1默認的數(shù)據(jù)庫用戶2添加數(shù)據(jù)庫用戶3刪除數(shù)據(jù)庫用戶創(chuàng)建數(shù)據(jù)庫用戶的方法創(chuàng)建數(shù)據(jù)庫用戶可分為兩個過程：首先創(chuàng)建數(shù)據(jù)庫用戶使用的SQL Server2008登錄名，如果使用內(nèi)置的登錄名則可省略這一步。其次為數(shù)據(jù)庫創(chuàng)建用戶，指定到創(chuàng)建的登錄名。用 SQL Server Management Studio

45、 或 CREATE USER 語句創(chuàng)建用戶5.3.1 管理用戶的方法下面通過SQL Server Management studio工具來創(chuàng)建數(shù)據(jù)庫用戶帳戶，然后給用戶授予訪問【教務管理系統(tǒng)】數(shù)據(jù)庫的權限。1、展開【服務器】|【數(shù)據(jù)庫】|【教務管理系統(tǒng)】節(jié)點。2、再展開【安全性】|【用戶】節(jié)點并右擊，選擇【新建用戶】，打開【數(shù)據(jù)庫用戶-新建】3、在【用戶名】文本框中輸入dur_suna來指定要創(chuàng)建的數(shù)據(jù)庫用戶名稱4、單擊【登錄名】文本框旁邊的【選項】，打開【選擇登錄名】窗口，然后單擊【瀏覽】按鈕，彈出【查找對象】對話框。5、啟用suna旁邊的復選框，單擊確定按鈕，返回【選擇登錄名】窗口，單擊確

46、定，返回【數(shù)據(jù)庫用戶-新建】窗口6、用同樣的方式，選擇【默認架構】為dbo7、單擊確定按鈕8、為了驗證是否創(chuàng)建成功，可以展開用戶節(jié)點5.4 固定服務器角色 服務器角色獨立于各個數(shù)據(jù)庫。如果我們在SQL Server中創(chuàng)建一個登錄賬號后，要賦予該登錄者具有管理服務器的權限，此時可設置該登錄賬號為服務器角色的成員。SQL Server提供了以下固定服務器角色： 內(nèi)置服務器角色角色描述sysadmin可執(zhí)行任何操作 dbcreator創(chuàng)建和修改數(shù)據(jù)庫 diskadmin管理磁盤文件 serveradmin配置服務器級的設置 securityadmin管理和審核服務器登錄 processadmin管理

47、 SQL Server 進程 bulkadmin執(zhí)行 BULK INSERT 語句 setupadmin配置和復制已鏈接的服務器 5.4.2 服務器角色管理 通過圖形化界面實現(xiàn)服務器角色管理 第1步 以系統(tǒng)管理員身份登錄到SQL Server服務器，在登錄圖標對應的列表項中，選擇登錄賬號“zhou”的項目雙擊； 第2步 選擇“服務器角色” 選項卡，如圖8.9所示，選項卡中列出了SQL Server所有的固定服務器角色，將“System administrators”服務器角色前的復選框選中。圖8.9 SQL Server服務器角色設置窗口5.4.2 服務器角色管理 2. 利用系統(tǒng)存儲過程實現(xiàn)服

48、務器角色管理 利用系統(tǒng)存儲過程sp_addsrvrolemember可將一登錄賬號添加到某一固定服務器角色中，使其成為固定服務器角色的成員。語法格式： sp_addsrvrolemember login，role 參數(shù)含義： login：添加到固定服務器角色role的登錄賬號名， 【例8.6】 將 Windows NT 用戶 dreamzhouym 添加到 sysadmin 固定服務器角色中。EXEC sp_addsrvrolemember dreamzhouym , sysadmin 5.4.2 服務器角色管理利用sp_dropsrvrolemember系統(tǒng)存儲過程可從固定服務器角色中刪除S

49、QL Server 登錄賬號或Windows NT用戶或組?！纠?.7】從 sysadmin 固定服務器角色中刪除登錄 zhou。 EXEC sp_dropsrvrolemember zhou, sysadmin 利用sp_srvrolepermission系統(tǒng)存儲過程可以瀏覽固定服務器角色的權限。5.5 數(shù)據(jù)庫角色固定數(shù)據(jù)庫角色 授予了管理公共數(shù)據(jù)庫任務的權限 用戶定義的數(shù)據(jù)庫角色相同數(shù)據(jù)庫權限的多個用戶 應用程序角色包含數(shù)據(jù)庫中所有用戶5.3.3 數(shù)據(jù)庫角色5.5.1 固定的數(shù)據(jù)庫角色 1通過管理器工具瀏覽固定的數(shù)據(jù)庫角色 第步 打開對象資源管理器，選擇任意一個數(shù)據(jù)庫名。 第2步 展開數(shù)據(jù)

50、庫的節(jié)點，選擇【安全性】 第3步 在【安全性】下選擇【角色】，展開節(jié)點，然后選擇【數(shù)據(jù)庫角色】，就可以看到系統(tǒng)默認的固定數(shù)據(jù)庫角色了 2.使用系統(tǒng)存儲過程瀏覽固定的數(shù)據(jù)庫角色 EXEC SP_HELPdbfixedrole go5.5.1 固定的數(shù)據(jù)庫角色1. 固定數(shù)據(jù)庫角色 固定數(shù)據(jù)庫角色定義在數(shù)據(jù)庫級別上，并且有權進行特定數(shù)據(jù)庫的管理及操作。SQL Server提供了以下固定數(shù)據(jù)庫角色： （1）db_owner：數(shù)據(jù)庫所有者，可執(zhí)行數(shù)據(jù)庫的所有管理操作。 SQL Server 數(shù)據(jù)庫中的每個對象都有所有者，通常創(chuàng)建該對象的用戶即為其所有者。其他用戶只有在相應所有者對其授權后，方可訪問該對象

51、。 （2）db_accessadmin：數(shù)據(jù)庫訪問權限管理者，具有添加、刪除數(shù)據(jù)庫使用者、數(shù)據(jù)庫角色和組的權限。 5.5.1 固定的數(shù)據(jù)庫角色（3）db_securityadmin：數(shù)據(jù)庫安全管理員，可管理數(shù)據(jù)庫中的權限，如設置數(shù)據(jù)庫表的增、刪、修改和查詢等存取權限。 （4）db_ddladmin：數(shù)據(jù)庫DDL管理員，可增加、修改或刪除數(shù)據(jù)庫中的對象。 （5）db_backupoperator：數(shù)據(jù)庫備份操作員，具有執(zhí)行數(shù)據(jù)庫備份的權限。 （6）db_datareader：數(shù)據(jù)庫數(shù)據(jù)讀取者。 （7）db_datawriter：數(shù)據(jù)庫數(shù)據(jù)寫入者，具有對表進行增、刪修改的權限。 （8）db_den

52、ydatareader：數(shù)據(jù)庫拒絕數(shù)據(jù)讀取者，不能讀取數(shù)據(jù)庫中任何表的內(nèi)容9）db_denydatawriter：數(shù)據(jù)庫拒絕數(shù)據(jù)寫入者，不能對任何表進行增、刪修改操作。 （10）public：是一個特殊的數(shù)據(jù)庫角色，每個數(shù)據(jù)庫用戶都是public 角色的成員，因此，不能將用戶、組或角色指派為public角色的成員，也不能刪除public角色的成員。通常將一些公共的權限賦給public角色。5.5.2 用戶自定義數(shù)據(jù)庫角色1通過SQL命令創(chuàng)建數(shù)據(jù)庫角色 1）定義數(shù)據(jù)庫角色 語法格式： CREATE ROLE role_name AUTHORIZATION owner_name 其中語法中的參數(shù)介

53、紹如下：role_name 將要創(chuàng)建的角色的名稱owner_name 該角色擁有者的名字，默認為dbo。其中owner_name必須是當前數(shù)據(jù)庫里的用戶或角色5.5.2 用戶自定義數(shù)據(jù)庫角色2通過管理器創(chuàng)建數(shù)據(jù)庫角色 第步： 展開指定的數(shù)據(jù)庫節(jié)點。 第2步: 選擇【安全性】選項，展開【安全性】節(jié)點，選擇【角色】選項； 第3步: 展開【角色】選項下的節(jié)點，選擇【數(shù)據(jù)庫角色】選項，鼠標右鍵單擊，選擇【新建數(shù)據(jù)庫角色】； 第4步: 輸入數(shù)據(jù)庫角色的名字，也可以單擊【添加】按鈕添加數(shù)據(jù)庫成員。 第5步: 單擊【確定】按鈕5.5.3 應用程序角色 應用程序角色是用戶定義數(shù)據(jù)庫角色的一種形式，與固定數(shù)據(jù)庫

54、角色不同。它規(guī)定了某個應用程序的安全性，用來控制通過某個應用程序?qū)?shù)據(jù)的間接訪問。例如，管理員允許雇員使用雇員處理程序錄入新員工、離職員工和打印統(tǒng)計報表等。 5.6 數(shù)據(jù)庫權限 權限提供了一種方法來對特權進行分組，并控制實例、數(shù)據(jù)庫和數(shù)據(jù)庫對象的維護和實用程序的操作。用戶可以具有授予一組數(shù)據(jù)庫對象的全部特權的管理權限，也可以具有授予管理系統(tǒng)的全部特權但不允許存取數(shù)據(jù)的系統(tǒng)權限。5.6.1 權限概述權限類型：對象權限語句權限隱式權限 對于表和視圖，擁有者可以授予數(shù)據(jù)庫用戶INSERT、UPDATE、DELETE、SELECT和REFERENCES五種權限。5.6.2 管理權限 對于用戶或角色權限

55、的操作有以下3種狀態(tài)：授予、廢除和拒絕。用戶和角色的權限以記錄的形式存儲在各個數(shù)據(jù)庫的sysprotects系統(tǒng)表中。1授予權限 GRANT2撤銷權限 REVOKE3拒絕權限 DENY5.6.2 管理權限1 授予權限 利用GRANT語句可以給數(shù)據(jù)庫用戶或數(shù)據(jù)庫角色賦予執(zhí)行T-SQL語句的權限及對數(shù)據(jù)庫對象進行操作的權限。 授予執(zhí)行T-SQL語句的權限。5.6.2 管理權限 語法格式： GRANT ALL | statement ,.n TO security_account ,.n 授予對數(shù)據(jù)庫對象操作的權限。 語法格式： GRANT ALL PRIVILEGES | permission ,

56、.n ( column ,.n ) ON table | view | ON table | view ( column ,.n ) | ON stored_procedure | extended_procedure | ON user_defined_function TO security_account ,.n WITH GRANT OPTION AS group | role 5.6.2 管理權限例如：在下面的例子中使用GRANT語句，授予角色guest對“教務管理系統(tǒng)”數(shù)據(jù)庫中“學生信息”表的INSERT、UPDATE、DELETE權限 use 教務管理系統(tǒng) go grant se

57、lect,update,delete on 學生信息 to guest go5.6.2 管理權限2. 拒絕權限 使用DENY命令可以拒絕給當前數(shù)據(jù)庫內(nèi)的用戶授予的權限，并防止數(shù)據(jù)庫用戶通過其組或角色成員資格繼承權限。 拒絕語句權限。 語法格式： DENY ALL | statement ,.n TO security_account ,.n 拒絕對象權限。 語法格式： DENY ALL PRIVILEGES | permission ,.n ( column ,.n ) ON table | view | ON table | view ( column ,.n ) | ON stored_p

58、rocedure | extended_procedure | ON user_defined_function TO security_account ,.n CASCADE 5.6.2 管理權限【例8.18】 首先給 public 角色授予 對于表XS的SELECT 權限，然后，拒絕用戶 zhang, wang, Nanjingliu 的特定權限，這樣，這些用戶就沒有對XS表的操作權限了。 USE XSCJ GO GRANT SELECT ON XS TO public GO DENY SELECT, INSERT, UPDATE, DELETE ON XS TO zhang, wang,

59、 dreamzhou GO【例8.19】首先將在“教務管理系統(tǒng)”數(shù)據(jù)庫的“學生信息”表中執(zhí)行INSERT操作的權限授予PUBLIC角色，這樣所有的數(shù)據(jù)庫用戶都擁有了該項權限。然后，又拒絕了用戶guest擁有該項權限 use 教務管理系統(tǒng) GO GRANT INSERT ON 學生信息 TO PUBLIC GO DENY INSERT ON 學生信息 TO GUEST5.6.2 管理權限3. 撤消權限 利用REVOKE命令可取消以前給當前數(shù)據(jù)庫用戶授予或拒絕的權限。 取消以前授予或拒絕的語句權限。 語法格式： REVOKE ALL | statement ,.n FROM security_ac

60、count ,.n 取消以前授予或拒絕的對象權限。 語法格式： REVOKE GRANT OPTION FOR ALL PRIVILEGES | permission ,.n ( column ,.n ) ON table | view | ON table | view ( column ,.n ) | ON stored_procedure | extended_procedure | ON user_defined_function TO | FROM security_account ,.n CASCADE AS group | role 5.6.2 管理權限【例8.20】取消已授予用