數(shù)據(jù)庫chapSQLServer創(chuàng)建和維護

1、SQL Server主要內(nèi)容SQL Server數(shù)據(jù)庫創(chuàng)建和維護T-SQL語言視圖、存儲過程和觸發(fā)器數(shù)據(jù)庫管理和保護 Microsoft SQL Server 2000（簡稱SQL Server）是Microsoft公司在Windows平臺上開發(fā)的一個基于客戶機/服務器(C/S) 模式的關系數(shù)據(jù)庫管理系統(tǒng)。網(wǎng)絡客戶機客戶機SQLServer數(shù)據(jù)庫服務器客戶機應用程序負責商業(yè)邏輯和向用戶提供數(shù)據(jù)。服務器對數(shù)據(jù)庫的數(shù)據(jù)進行操作和管理。SQL Server主要管理工具 SQL Server提供了一整套數(shù)據(jù)庫系統(tǒng)管理工具和實用程序，打開“開始/程序Microsoft SQL Server”菜單即可看到

2、如下所示的SQL Server主要工具：1服務管理器（SQL Server Services Manager） SQL Server服務管理器的作用:啟動、停止或暫停SQL Server服務器所支持的服務。服務有3種狀態(tài)：停止（紅色）：該服務沒有啟動，所有與SQL Server連接的客戶端全部斷開。啟動（綠色）：服務正常運行，相關管理和服務功能可用。暫停（黃色）：暫停狀態(tài)時，已連接的客戶端任務可以完成，不允許新的連接。選擇該復選框可以在啟動操作系統(tǒng)時自動啟動所選擇的SQL Server服務。表示服務管理器處于啟動狀態(tài)表1 SQL Server提供的主要服務服務名稱主要作用支持主要服務和管理SQ

3、L Server（SQL Server數(shù)據(jù)庫引擎服務）是SQL Server的核心服務組件，是實際的數(shù)據(jù)庫服務器，該服務啟動后，客戶端才可以連接服務器上。一般的數(shù)據(jù)庫功能都由它提供數(shù)據(jù)管理（創(chuàng)建、維護數(shù)據(jù)庫等）、T-SQL處理、并發(fā)控制、數(shù)據(jù)完整性控制等SQL Server Agent（SQL Server代理服務）作業(yè)調(diào)度和管理，可支持數(shù)據(jù)庫定時、自動備份、維護，監(jiān)視數(shù)據(jù)庫、異常告警等功能，必需和SQL Server服務一起使用作業(yè)、報警、操作員等Distributed Transaction Coordinator（分布式事務處理協(xié)調(diào)器服務DTC）管理分布式事務，它允許客戶端應用程序在一個

4、事務中包含多個不同的數(shù)據(jù)源分布式事務處理協(xié)調(diào)器2企業(yè)管理器（Enterprise Manager） SQL Server企業(yè)管理器是可視化的數(shù)據(jù)庫管理工具，使用企業(yè)管理器可以對SQL Server服務器、數(shù)據(jù)庫及數(shù)據(jù)庫對象進行各種有效的管理，執(zhí)行所有的SQL Server管理任務。菜單欄 對象瀏覽器:進行數(shù)據(jù)庫維護和操作SQL語句編輯器 執(zhí)行結(jié)果顯示窗口 工具條 3查詢分析器（SQL Query Analyzer） 4SQL Server聯(lián)機叢書數(shù)據(jù)庫中的對象 表（Table）索引（Index）默認（Default）約束（Constraint）視圖（View）存儲過程（Stored Proce

5、dure）觸發(fā)器（Trigger）用戶（User）、角色（Role）用戶定義的數(shù)據(jù)類型、用戶定義的函數(shù)SQL Server有兩類數(shù)據(jù)庫：(1)系統(tǒng)數(shù)據(jù)庫:存放SQL Server工作時所需要的系統(tǒng)級信息。數(shù)據(jù)庫主要作用master從整體上控制SQL Server系統(tǒng)和用戶數(shù)據(jù)庫的運行。保存登錄標識、系統(tǒng)配置、用戶數(shù)據(jù)庫基本信息等。該庫非常重要，應設置權限禁止一般用戶訪問，另外要及時備份model是新建數(shù)據(jù)庫的模板，包含了每個用戶數(shù)據(jù)庫都需要的一些系統(tǒng)表，SQL Server以它為基礎創(chuàng)建新的數(shù)據(jù)庫msdb支持SQL Server Agent服務，它記錄有關作業(yè)、警報、操作員等信息tempdb是

6、個臨時數(shù)據(jù)庫，它為SQL Server的查詢、存儲過程等的執(zhí)行提供臨時存儲(2)用戶自定義數(shù)據(jù)庫:用戶根據(jù)數(shù)據(jù)管理的需要建立的數(shù)據(jù)庫，由用戶自行創(chuàng)建和維護。 SQL Server提供兩個樣本數(shù)據(jù)庫 ：pubs（圖書出版的數(shù)據(jù)庫）Northwind（有關貿(mào)易公司的數(shù)據(jù)庫）創(chuàng)建數(shù)據(jù)庫創(chuàng)建數(shù)據(jù)庫 創(chuàng)建和打開數(shù)據(jù)庫 修改數(shù)據(jù)庫 刪除數(shù)據(jù)庫創(chuàng)建和打開數(shù)據(jù)庫 數(shù)據(jù)庫名 數(shù)據(jù)庫名稱在服務器中必須惟一符合標識符的規(guī)則數(shù)據(jù)庫名稱最多可以包含128個字符 創(chuàng)建和打開數(shù)據(jù)庫數(shù)據(jù)庫文件數(shù)據(jù)文件日志文件（.ldf）主數(shù)據(jù)文件（.mdf）次數(shù)據(jù)文件（.ndf）一個多個所有文件都具有邏輯文件名與物理文件名創(chuàng)建和打開數(shù)據(jù)庫文

7、件組是用戶對數(shù)據(jù)庫文件（不包括事務日志文件）進行分組而形成的。每個數(shù)據(jù)庫中至少包含一個文件組（主文件組）使用文件和文件組時要遵循以下規(guī)則 主數(shù)據(jù)文件必須屬于主文件組任何未指定文件組中的文件都放在主文件組。所有系統(tǒng)表都包含在主文件組中。一個文件只能屬于一個文件組一個文件或文件組只能屬于一個數(shù)據(jù)庫日志文件不能成為任何文件組的成員使用CREATE DATABASE命令建立數(shù)據(jù)庫 CREATE DATABASE 數(shù)據(jù)庫名ON PRIMARY(NAME=, FILENAME=, SIZE=, MAXSIZE=, FILEGROWTH=)LOG ON(NAME=, FILENAME=, SIZE=, MA

8、XSIZE=, FILEGROWTH=)FILEGROUP 文件組名 使用USE命令可以打開已創(chuàng)建的數(shù)據(jù)庫 打開已創(chuàng)建的數(shù)據(jù)庫命令格式為 USE 數(shù)據(jù)庫名 例1 創(chuàng)建期刊采編系統(tǒng)數(shù)據(jù)庫MagDb 磁盤的文件夾要預先建立例2 創(chuàng)建未指定數(shù)據(jù)文件和日志文件的數(shù)據(jù)庫usedb1 例3 創(chuàng)建數(shù)據(jù)庫usedb，其主數(shù)據(jù)文件為use1，次數(shù)據(jù)文件為use2和use3；日志文件包括uselog1和uselog2 例4 創(chuàng)建包含多個文件組的數(shù)據(jù)庫usedb2 修改數(shù)據(jù)庫 增加或刪除數(shù)據(jù)文件和日志文件縮小分配給數(shù)據(jù)文件或日志文件的空間擴大分配給數(shù)據(jù)文件或日志文件的空間創(chuàng)建文件組改變默認文件組改變數(shù)據(jù)庫名使用AL

9、TER DATABASE命令改變數(shù)據(jù)庫定義 ALTER DATABASE 數(shù)據(jù)庫名ADD FILE (文件參數(shù)) TO FILEGROUP 文件組名REMOVE FILE (文件參數(shù)) MODIFY FILE (文件參數(shù)) ADD LOG FILE (文件參數(shù)) ADD FILEGROUP 文件組名REMOVE FILEGROUP 文件組名 例5 為數(shù)據(jù)庫usedb1增加一個數(shù)據(jù)文件add_use1 例6 為數(shù)據(jù)庫usedb1增加一個日志文件add_use1_log 例7 為數(shù)據(jù)庫usedb1增加一個數(shù)據(jù)文件add_use2并添加到新建的文件組usedbFG中 例8 更改數(shù)據(jù)庫usedb1中文

10、件add_use2的初始大小為10M 縮小數(shù)據(jù)庫或數(shù)據(jù)文件 縮小數(shù)據(jù)庫大小 DBCC SHRINKDATABASE（數(shù)據(jù)庫名，參數(shù)） 參數(shù)數(shù)據(jù)庫縮小到原來大小的百分比縮小數(shù)據(jù)文件大小 DBCC SHRINKFILE（文件名，參數(shù)）參數(shù)數(shù)據(jù)文件縮小后的絕對數(shù)值，默認單位MB例9 將數(shù)據(jù)庫usedb2的大小縮小到60% 例10 將數(shù)據(jù)庫usedb2中的文件userlog2的大小縮小到10M 刪除數(shù)據(jù)庫 刪除數(shù)據(jù)庫的指令格式為：DROP DATABASE 數(shù)據(jù)庫名 例11 將數(shù)據(jù)庫usedb刪除 創(chuàng)建和維護表創(chuàng)建和維護表創(chuàng)建數(shù)據(jù)庫表修改數(shù)據(jù)庫表使用約束 建立索引刪除數(shù)據(jù)庫表創(chuàng)建數(shù)據(jù)表的一般過程：設計

11、表結(jié)構(gòu) 定義字段 輸入字段 定義字段類型 定義字段屬性 編輯字段 保存表 輸入數(shù)據(jù)創(chuàng)建數(shù)據(jù)庫表的指令格式 CREATE TABLE 表名（列名1 數(shù)據(jù)類型 identity |NOT NULL| NULL , 列名2 數(shù)據(jù)類型 identity |NOT NULL| NULL , ）NULL：可以無值，默認值NOT NULL：必須輸入值identity計數(shù)器，遞增的整數(shù)，默認：初值1，步長1；用戶可自定義，如identity (-20,4)規(guī)則每個數(shù)據(jù)表只能有一個具有identity屬性的列；該列的數(shù)據(jù)類型使用整型或精確數(shù)型；該列的數(shù)據(jù)值自動擁有，用戶不能修改該列的數(shù)據(jù)值。例：Create t

12、able demotable(user_id numeric(10,0) identity, Name varchar(30) not NULL, Comments varchar(100) null) 表名 表名是惟一的 可以使用中文，也可以使用西文符號最多64個字符 一般表都是永久表臨時表 臨時表的表名必須以#打頭 仍然使用CREATE TABLE命令例:Create table #demotable (user_id numeric(10,0) identity, Name varchar(30) not NULL) 列名 表中的每一列也稱為字段，所以列名也叫字段名；字段名的命名方式基本

13、同數(shù)據(jù)表名的命名；字段名在數(shù)據(jù)表中要求是惟一的，但在整個數(shù)據(jù)庫中可以重名 。數(shù)據(jù)類型 T-SQL中支持的、常用的數(shù)據(jù)類型 字符型 日期時間類型 數(shù)值類型 二進制型 位型 文本型 圖像型 時間戳型 字符型 字符型變量應定義長度允許定義的最大長度是255 有兩種固定長度的char( )類型，空格補位可變長度的varchar( )類型 輸入字符型數(shù)據(jù)時必須用單引號將數(shù)據(jù)括起來 日期時間類型 datetime和smalldatetime是用來存儲日期和時間數(shù)據(jù) 日期時間類型數(shù)據(jù)必須用單引號括起來比較內(nèi)容 Datetime Smalldatetime 最小值 Jan 1,1753 Jan 1,1900

14、最大值 Dec 31,9999 Jun 6,2079 占用存儲空間 8byte 4byte 精度 3.33毫秒 1分鐘 整數(shù)類型 整數(shù)類型的比較 比較內(nèi)容 Int Smallint Tinyint 最小值 -231 -215 0 最大值 231-1 215-1 255 占用存儲空間 4byte 2byte 1byte 浮點數(shù)類型 比較內(nèi)容FloatReal最小值-1.79E+308-3.40E+38最大值1.79E+3083.40E+38占用存儲空間8Byte4Byte精度最多15位最多7位精確數(shù)類型 精確數(shù)類型有兩種形式DecimalNumeric 與浮點類型數(shù)據(jù)的區(qū)別 可以自定義精度的位數(shù)

15、（最多28位）例 num_col(5,2)貨幣型 貨幣類型的比較 比較內(nèi)容MoneySmallmoney最小值-922,337,203,685,477.5808-214,748.3648最大值922,337,203,685,477.5807214,748.3647占用存儲空間8Byte4Byte精度小數(shù)點后4位小數(shù)點后4位使用SQL語句輸入貨幣類型數(shù)據(jù)可以在數(shù)據(jù)前加上貨幣符號（如￥、$等）位數(shù)據(jù)類型 位數(shù)據(jù)類型bit是一種邏輯數(shù)據(jù)類型 只有1和0兩種數(shù)值一般常用作true/false使用 定義bit列時，不允許為NULL，也不能建立索引 多個bit列可占用一個字節(jié)文本型和圖像型 文本類型tex

16、t 圖像類型image 每行可以存儲2GB的二進制大型對象 時間戳型 時間戳型表示Timestamp由系統(tǒng)自動賦值的一個計數(shù)器數(shù)據(jù) 記錄了數(shù)據(jù)行的操作順序 時間戳列的數(shù)據(jù)保持惟一，它可以惟一標識表中的列 時間戳列的值實際來自于事務日志 例3 建立期刊采編系統(tǒng)數(shù)據(jù)庫中各數(shù)據(jù)表 創(chuàng)建mag_dept表 創(chuàng)建mag_emp表 創(chuàng)建mag_info表 創(chuàng)建mag_doc表 修改數(shù)據(jù)庫表 添加或刪除列修改列的屬性 更改列名和表名 添加或刪除列 增加列的語句格式 Alter table 表名 add 列名 數(shù)據(jù)類型 identity |NOT NULL| NULL ，刪除列的語句格式 Alter tabl

17、e 表名 drop column 列名 例12 向數(shù)據(jù)表mag_emp中添加一新屬性ID（身份證號），數(shù)據(jù)類型為字符型，固定長度18 修改列的屬性 命令格式 alter table 表名 alter column 列名 數(shù)據(jù)類型 identity |NOT NULL| NULL 例13 將數(shù)據(jù)表mag_info中PubDate的數(shù)據(jù)類型更改為smalldatetime 更改列名和表名 使用SQL Server內(nèi)部存儲過程更改列名 sp_rename 表名.原列名, 新列名 更改表名sp_rename 原表名 新表名 例14 數(shù)據(jù)庫usedb1中建有數(shù)據(jù)表test，包含number、stu_na

18、me和class三個字段，請將class更名為class_no 例15 將數(shù)據(jù)庫usedb1中數(shù)據(jù)表test更名為student 使用約束 約束的作用實現(xiàn)數(shù)據(jù)的完整性實體完整性參照完整性用戶自定義完整性約束的類型 建立約束 刪除約束 參照完整性學號姓名S256S257S258王丹章華李力性別系號男女男D23D25D30系號D23D25D30系名負責人物理機械計算機王娟楊華張?zhí)礻P系S關系DEPT約束的類型 名稱作用實現(xiàn)的完整性Primary key定義主鍵,保證主鍵列無重復值實體完整性Unique保證該列無重復值實體完整性Foreign key定義外鍵,保證數(shù)據(jù)表間數(shù)據(jù)的一致性參照完整性Chec

19、k定義表中某些列的數(shù)據(jù)范圍自定義完整性Default為列的數(shù)據(jù)提供默認值自定義完整性建立約束 定義約束時使用Create table語句或使用Alter table語句即可以在定義數(shù)據(jù)表的數(shù)據(jù)列時直接定義約束，也可以對已定義的數(shù)據(jù)表添加約束 使用Alter table語句添加約束的基本格式 Alter table 表名 Add Constraint 約束名 約束定義使用Create table語句建立約束 Create table 表名 （列名1 數(shù)據(jù)類型 identity |NOT NULL| NULL Constraint 約束名 約束定義,列名2 數(shù)據(jù)類型 identity |NOT NULL| NULL Constraint 約束名 約束定義, Constraint 約束名 約束定義） PRIMARY KEY定義主鍵約束的基本格式為： Constraint 約束名 primary key (列名1，列名2，)例16 向數(shù)據(jù)表map_dept中添加主鍵約束 例17在數(shù)據(jù)庫usedb2中建立數(shù)據(jù)表map_dept，同時定義主鍵約束 例18 在數(shù)據(jù)庫usedb1中建立數(shù)據(jù)表grade