SQLserver數據庫創(chuàng)建管理

數據庫系統原理實踐

——SQLServer20052創(chuàng)建和管理數據庫1創(chuàng)建和管理數據庫1SQLServer數據庫概述2創(chuàng)建數據庫3修改數據庫4刪除數據庫5分離和附加數據庫*21SQLServer數據庫概述在一個實例中可以創(chuàng)建多個數據庫，每個數據庫在物理上和邏輯上都是獨立的。每個數據庫存儲相關的數據。*3數據庫分類SQLServer將數據庫分為兩大類：系統數據庫用戶數據庫。系統數據庫是SQLServer數據庫管理系統自動創(chuàng)建和維護的，這些數據庫用于保存維護系統正常運行的信息。用戶數據庫保存與用戶的業(yè)務有關的數據，用戶數據庫中的數據是用戶來維護的。*4數據庫分類示意圖*51.1系統數據庫master：是最重要的數據庫，用于記錄SQLServer系統中所有系統級信息。msdb：保存關于調度報警、作業(yè)、操作員等信息。model：是用戶數據庫的模板，其中包含所有用戶數據庫的共享信息。當創(chuàng)建用戶數據庫時，系統自動將model數據庫中的全部內容復制到新建數據庫中。tempdb：臨時數據庫，用于存儲臨時信息。*61.2SQLServer數據庫的組成由若干個文件組成，文件被分為兩類：數據文件：用于存放數據庫中的數據信息。日志文件：用于存放對數據庫的操作記錄。*7數據庫文件每個數據庫都包含：一個主數據文件一個或多個日志文件零個或多個輔助數據文件每個文件都有：物理文件名邏輯文件名*8數據頁在SQLServer2005中，數據的存儲單位是頁（Page，也稱為數據頁）。一頁是一塊8KB（8×1024字節(jié)，其中用8060個字節(jié)存放數據，另外的132個字節(jié)存放系統信息）的連續(xù)磁盤空間，頁是存儲數據的最小單位。頁的大小決定了數據庫表中一行數據的最大大小。*9不允許表中的一行數據存儲在不同頁上，即行不能跨頁存儲。因此表中一行數據的大小不能超過8060字節(jié)。例：一個數據表有10000行數據，每行3000字節(jié)。則每個數據頁可存放兩行數據，此表需要的空間為：(10000/2)*8KB=40MB*10數據存儲示意圖*111.3數據文件和日志文件數據文件用于存放數據庫數據。分為：主數據文件和輔助數據文件事務日志文件推薦擴展名為.ldf。用于存放恢復數據庫的所有日志信息。每個數據庫必須至少有一個日志文件。最小為512KB，但最好不要小于1MB。*12數據文件主數據文件：推薦擴展名是.mdf包含數據庫的系統信息，并可存放用戶數據每個數據庫都有且僅有一個主數據文件。輔助數據文件：推薦擴展名是.ndf。一個數據庫可以包含0~n個輔助數據文件。輔助數據文件可以分別建立不同的磁盤上。*131.4數據庫文件的屬性文件名及其位置初始大小增長方式最大大小*14文件名及其位置每個數據文件和日志文件都具有一個邏輯文件名和物理存儲位置（包括物理文件名）。一般情況下，如果有多個數據文件，則為了獲得更好的性能，建議將文件分散存儲在多個磁盤上。*15初始大小主數據文件的初始大小時，其大小不能小于model數據庫主文件的大小。因為系統是將model數據庫中主數據文件的內容拷貝到用戶數據庫的主數據文件上。*16增長方式如果需要的話，可以指定文件是否自動增長。默認配置為自動增長，即當數據庫的空間用完后，系統自動擴大數據庫的空間，以防止由于數據庫空間用完而造成的不能進行數據操作的錯誤。*17最大大小指文件增長的最大空間限制。默認情況是無限制。建議設定允許文件增長的最大空間大小，以防止因文件無限制增長而將磁盤空間用完。*182創(chuàng)建數據庫可以在SQLServerManagementStudio（SSMS）工具中用圖形化的方式實現，也可以通過T-SQL語句實現。*19T-SQL語句創(chuàng)建數據庫CREATEDATABASEdatabase_name[ON--數據文件[PRIMARY][<filespec>[,...n][,<filegroup>[,...n]][LOGON–日志文件{<filespec>[,...n]}]]]*20T-SQL語句創(chuàng)建數據庫（續(xù)）<filespec>::={(NAME=logical_file_name,--邏輯名FILENAME={‘os_file_name’|‘filestream_path'}--物理名[,SIZE=size[KB|MB|GB|TB]]--初始大小[,MAXSIZE={max_size[KB|MB|GB|TB]|UNLIMITED}]--最大大小[,FILEGROWTH=growth_increment[KB|MB|GB|TB|%]]--增長方式，0為不增長)[,...n]}*21T-SQL語句創(chuàng)建數據庫（續(xù)）<filegroup>::={FILEGROUPfilegroup_name[DEFAULT]<filespec>[,...n]}*22示例1例1．創(chuàng)建一個名字為“學生管理數據庫”的數據庫，其他選項均采用默認設置。

CREATEDATABASE學生管理數據庫*23示例2CREATEDATABASERShDBON(NAME=RShDB_Data,FILENAME='D:\RShDB_Data\RShDB_Data.mdf',SIZE=10,MAXSIZE=30,FILEGROWTH=5)LOGON(NAME=RShDB_log,FILENAME='D:\RShDB_Data\RShDB_log.ldf',SIZE=3,MAXSIZE=12,FILEGROWTH=2)*24示例3CREATEDATABASEstudentsONPRIMARY(NAME=students,FILENAME='F:\Data\students.mdf',SIZE=3MB,MAXSIZE=UNLIMITED),(NAME=students_data1,FILENAME='D:\Data\students_data1.ndf',SIZE=5MB,MAXSIZE=10MB,FILEGROWTH=1MB)*25示例3（續(xù)）LOGON(NAME=students_log,

FILENAME='F:\Data\students_log.ldf',SIZE=2MB,MAXSIZE=6MB,FILEGROWTH=10%)*263查看和設置數據庫選項數據庫選項是指在數據庫范圍內有效的一些參數，用于控制這個數據庫的某些特性和行為。所有的數據庫選項只能取True或False。*27設置數據庫選項窗口*284修改數據庫擴大數據庫空間；縮小數據庫空間；添加和刪除文件。*29擴大數據庫空間擴大數據庫空間有兩種方法，擴大數據庫中已有文件的大小為數據庫添加新的文件這兩種方法均可在SQLServerManagementStudio中用圖形化的方法實現，也可以用T-SQL語句實現。*30修改數據庫的語句ALTERDATABASEdatabase_name{

<add_or_modify_files>}<add_or_modify_files>::={

ADDFILE<filespec>[,...n]--添加數據文件

[TOFILEGROUP{filegroup_name|DEFAULT}]

|ADDLOGFILE<filespec>[,...n]—添加日志文件

|MODIFYFILE<filespec>--修改文件*31示例1例1．為“RShDB”數據庫添加一個新的數據文件，邏輯文件名為：RShDB_Data2，物理存儲位置為：E:\Data文件夾下，物理文件名為：RShDB_Data2.ndf,初始大小為6MB，不自動增長。ALTERDATABASERShDBADDFILE(NAME=RShDB_Data2,FILENAME='E:\Data\RShDB_Data2.ndf',SIZE=6MB,FILEGROWTH=0)*32示例2例2．增加students數據庫中students文件的初始大小，將其初始大小改為8MB。ALTERDATABASEstudentsMODIFYFILE(NAME=students,SIZE=8MB)*33示例3例3.為students數據庫加添加一個新的日志文件。ALTERDATABASEstudentsADDLOGFILE(NAME=students_log1,FILENAME='D:\Data\students_log1.ldf',SIZE=2MB,FILEGROWTH=1MB,MAXSIZE=10MB)*34收縮數據庫空間就是釋放數據庫中未使用的空間?？梢允湛s數據文件和事務日志文件。可以成組或單獨地手動收縮數據庫文件?？梢酝ㄟ^設置數據庫選項，使其按照指定的間隔自動收縮。*35收縮數據庫（續(xù)）文件的收縮都是從末尾開始的。如果文件中被釋放的空間部分包含使用過的頁，則數據庫引擎先將這些頁重新放置到保留的空間部分，然后再進行收縮。只能將數據庫收縮到沒有剩余的可用空間為止。*36自動收縮只需將該數據庫的“自動收縮”（AUTO_SHRINK）選項設置為“ON”即可（在數據庫屬性窗口的“選項”對應的窗口中，在“自動”部分的“自動收縮”選項），數據庫引擎會定期檢查數據庫空間的使用情況，并減少數據庫中文件的大小。*37手動收縮兩種收縮方式：收縮數據庫中某個文件的大小，收縮整個數據庫的大小。當收縮整個數據庫空間的大小時，收縮后各文件的大小不能小于創(chuàng)建這些文件時指定的初始大小。*38收縮整個數據庫大小的SQL語句DBCCSHRINKDATABASE('database_name'|database_id|0--當前正在使用的數據庫[,target_percent]--剩余百分比[,{NOTRUNCATE|TRUNCATEONLY}])NOTRUNCATE|TRUNCATEONLY：釋放空間是否給操作系統*39收縮指定文件大小的SQL語句DBCCSHRINKFILE({'file_name'|file_id}{[,EMPTYFILE]--清空該文件|[[,target_size]--目標大小[,{NOTRUNCATE|TRUNCATEONLY}]]})*40數據庫文件使用情況數據文件按比例填充數據，各文件存儲的數據量與文件的可用空間成正比，這種方式使得所有數據文件幾乎是同時被填滿的。日志文件是填充到滿的策略,即先填充第一個日志文件，第一個日志文件填滿后，再填充第二個日志文件，依此類推。*41添加文件向數據庫添加文件時，可以指定文件的初始大小存放位置增長方式等屬性同創(chuàng)建數據庫時指定文件屬性方法相同。*42刪除文件刪除數據或日志文件是將該文件從數據庫中刪除。只有當文件中沒有數據或事務日志信息，文件完全為空時，才可以從數據庫中刪除該文件。*43刪除文件的T-SQL語句ALTERDATABASEdatabase_nameREMOVEFILElogical_file_name例1.刪除students數據庫中的students_data1文件。ALTERDATABASEstudentsREMOVEFILEstudents_data1*445刪除數據庫刪除一個數據庫，也就刪除了該數據庫的全部對象，包括數據文件和日志文件也被從磁盤上刪除。一旦刪除數據庫，它即被永久刪除。*45刪除數據庫的T-SQL語句DROPDATABASEdatabase_name[,…n]例1．刪除studnets數據庫。DROPDATABASEstudents例2．刪除“學生管理數據庫”和“RShDB”數據庫。DROPDATABASE學生管理數據庫,RShDB*466分離和附加數據庫可以實現將數據庫從一臺計算機移動到另一臺計算機，而不需要重新創(chuàng)建數據庫。可以分離數據庫的數據文件和事務日志文件，然后將它們重新附加到其他SQLServer實例中。*47分離數據庫分離數據庫是指將數據庫從SQLServer實例中刪除，但不刪除數據庫中的數據文件和事務日志文件。分離數據庫會保持數據庫的數據文件和日志文件的完整和一致。用戶不能使用被分離的數據庫。*48分離數據庫的語句sp_detach_db[@dbname=]'dbname'

[,[@skipchecks=]'skipchecks'][,[@KeepFulltextIndexFile=]'KeepFulltextIndexFile'][@skipchecks=]'skipchecks'：默認為NULL。True：跳過“更新統計信息”；false：運行“更新統計信息”，則指定。[@KeepFulltextIndexFile=]‘KeepFulltextIndexFile’:是否保留“全文目錄”。*49示例例．分離students數據庫，并跳過“更新統計信息”。EXECsp_detach_db'students','true'*50附加數據庫將分離的數據庫重