sqlserver數(shù)據(jù)庫的管理以及維護

   http://xiaorenwutest.blog.51cto.com   

            數(shù)據(jù)庫和表的管理

前言：上次主要介紹的是sql server數(shù)據(jù)庫的搭建以及通過SSMS工具進行連接數(shù)據(jù)庫，本次內(nèi)容主要介紹數(shù)據(jù)庫的表進行管理。在工作中DBA通常對數(shù)據(jù)庫進行創(chuàng)建和刪除以及修改表的內(nèi)容，擴展和收縮、分離及附加。

創(chuàng)新互聯(lián)堅持“要么做到，要么別承諾”的工作理念，服務(wù)領(lǐng)域包括：成都網(wǎng)站設(shè)計、做網(wǎng)站、企業(yè)官網(wǎng)、英文網(wǎng)站、手機端網(wǎng)站、網(wǎng)站推廣等服務(wù)，滿足客戶于互聯(lián)網(wǎng)時代的興安盟網(wǎng)站設(shè)計、移動媒體設(shè)計的需求，幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡(luò)建設(shè)合作伙伴！

表是存放數(shù)據(jù)、查找數(shù)據(jù)以及更新數(shù)據(jù)的基本數(shù)據(jù)構(gòu)建，在對數(shù)據(jù)的操作都是在表的基礎(chǔ)上進行的，以及如何維護表。   注：實際上表是邏輯的存在，事實上數(shù)據(jù)存放在硬盤上面。

另外在創(chuàng)建數(shù)據(jù)庫的時候回形成兩個基本的庫文件主數(shù)據(jù)庫文件.mdf和事物日志文件.ldf

sql server 2008R2如何存儲數(shù)據(jù)

數(shù)據(jù)庫在磁盤上是以文件為單位存儲的，由數(shù)據(jù)文件和事物日志文件組成。一個數(shù)據(jù)庫至少應(yīng)該包含一個數(shù)據(jù)文件和一個事物日志文件。

數(shù)據(jù)庫創(chuàng)建在物理介質(zhì)（磁盤）上的一個或多個文件上，它預(yù)先分配了被數(shù)據(jù)和事物日志所要適用的物理存儲空間。存儲數(shù)據(jù)的文件叫做數(shù)據(jù)文件，數(shù)據(jù)文件包含數(shù)據(jù)和對象，如表和索引。存儲事物日志的文件叫做事物日志文件（又稱日志文件）。

sql server 2008 R2數(shù)據(jù)庫有一下類型的文件

1. 主數(shù)據(jù)文件

2. 輔數(shù)據(jù)文件

3. 事物日志文件

4. 文件流

舉例說明

--創(chuàng)建數(shù)據(jù)庫

create database schoolDB

go

use schoolDB

go

這樣創(chuàng)建完的數(shù)據(jù)庫只有一個數(shù)據(jù)文件和一個事物日志文件，查看方式在創(chuàng)建的schoolDB上右鍵選擇“屬性”--“文件”

 

 

可以在界面中單擊“添加”增加新的數(shù)據(jù)文件schooldb2，以后再schooldb數(shù)據(jù)庫中創(chuàng)建新的表，表中的數(shù)據(jù)就會存放在schooldb和schoolDB2數(shù)據(jù)文件中。

 

數(shù)據(jù)文件在數(shù)據(jù)庫中存放的時候都有標(biāo)識，我們可以通過sp_helpdb schoolDB

 

數(shù)據(jù)文件結(jié)構(gòu)

數(shù)據(jù)文件1和數(shù)據(jù)文件3的結(jié)構(gòu)如果所示：

數(shù)據(jù)文件由64kb大小的區(qū)（extend)組成的,每個區(qū)由8個8KB的連續(xù)的頁組成的。

sqlserver所能識別的最小存儲單元被稱為頁，一個頁的大小是8KB,是sql server存儲數(shù)據(jù)的單位。

在數(shù)據(jù)庫中一頁的大小是8KB，在計算機中1KB就是1024，所以1頁可以存放1024*8=8192個字節(jié)

 

 

 

 

 

  

 1.1. 如何根據(jù)表的記錄數(shù)量估算占用的磁盤空間

1.先算一個表中一行有多少個字節(jié)，例如學(xué)號字段6個字節(jié)，姓名字段8個字節(jié)，出生日期字段多少個字節(jié)

2.算一個頁能夠存多少行

3.數(shù)據(jù)庫中的表每天增加多少行記錄，就能夠算出每天要增加多少硬盤空間，這樣就可根據(jù)數(shù)據(jù)量估算規(guī)劃多大的空間。

在數(shù)據(jù)庫中創(chuàng)建一張Tstudent表

 

create TABLE TStudent (

  StudentID varchar(10) NOT NULL,

  Sname varchar(10) DEFAULT NULL,

sex char(2) DEFAULT NULL,

cardID varchar(20) DEFAULT NULL,

  Birthday datetime DEFAULT NULL,

  Email varchar(40) DEFAULT NULL,

  Class varchar(20) DEFAULT NULL,

enterTime datetime DEFAULT NULL

 )

go

 

10個字節(jié)+10個字節(jié)+2個字節(jié)+20字節(jié)+8字節(jié)+40字節(jié)+20字節(jié)+8個字節(jié)=118字節(jié)

Tstudent表的一個行有118個字節(jié)

如果每天增加10000條記錄，10000條記錄占多少頁？

1頁可以存放1024*8=8192個字節(jié)，拋去標(biāo)頭96字節(jié)，真正存放的字節(jié)數(shù)就8192-96=8096字節(jié)。

一頁能存8096字節(jié)，一頁能夠存8096/118=68條記錄

10000行有多少頁？10000/68=148(頁）

一頁是8KB，148*8==1184KB

在Tstudent表中存放10000條記錄需要預(yù)備出來1MB多的磁盤空間。這樣就可以根據(jù)每天增加的記錄數(shù)，合理規(guī)劃好磁盤空間了。

現(xiàn)在Tstudent表中還沒有插入任何記錄，所以占用了0頁的數(shù)據(jù)

提供查看數(shù)據(jù)文件頁數(shù)的命令

select OBJECT_NAME(i.object_id) as 表名,data_pages as 數(shù)據(jù)頁數(shù)

from sys.indexes as i

join sys.partitions as p ON p.object_id=i.object_id and p.index_id=i.index_id

join sys.allocation_units as a ON a.container_id=p.partition_id

where i.object_id=object_id('dbo.TStudent')

 

執(zhí)行教學(xué)環(huán)境中的存儲過程，添加10000條記錄

 

這個頁數(shù)和我們剛才算出來的有些差距，這是正常的，每一頁可能并沒有插入68條記錄。

1.2. 事物日志

事物是一個或多個T-SQL語句的集合，事物有一個特性：要么執(zhí)行成功，要么執(zhí)行失敗。每個sql server數(shù)據(jù)庫都具有事物日志，用于記錄所有事物的SQL語句。當(dāng)發(fā)生數(shù)據(jù)災(zāi)難時候，通過事物日志記錄的T-SQL語句可以恢復(fù)數(shù)據(jù)庫。

如果系統(tǒng)出現(xiàn)故障，sql server將使用事物日志重做（前滾）所有已確認的事物，撤銷（回滾）所有未完成的事物。

1.3. 創(chuàng)建數(shù)據(jù)庫（擴展/收縮）

使用圖形界面創(chuàng)建數(shù)據(jù)庫，在對象資源管理器中，右擊“數(shù)據(jù)庫”節(jié)點，在彈出的快捷菜單中選擇“新建數(shù)據(jù)庫”命令，打開“新建數(shù)據(jù)庫”窗口，設(shè)置數(shù)據(jù)庫名稱為“class”，制定數(shù)據(jù)庫的所有者，默認創(chuàng)建數(shù)據(jù)庫的用戶將成為該數(shù)據(jù)庫的所有者。主數(shù)據(jù)文件初始值為3mb,自動增長值為1mb，當(dāng)數(shù)據(jù)文件或日志文件空間使用完之后，會根據(jù)設(shè)定的自動增長值增大文件的容量。

在路徑列中可以設(shè)置數(shù)據(jù)文件和日志文件的保存路徑。單擊“確定”之后一個新的數(shù)據(jù)庫就建立成功了，隨之會生成兩個文件一個是包含數(shù)據(jù)文件的.mdf，一個是包含日志信息的.ldf文件。

 

創(chuàng)建數(shù)據(jù)庫之前，必須先確定數(shù)據(jù)庫的名稱，所有者（創(chuàng)建數(shù)據(jù)庫的用戶），大小以及存儲該數(shù)據(jù)文件和事物日志文件的位置。

如果要對數(shù)據(jù)庫做數(shù)據(jù)庫級別的設(shè)置

 

在數(shù)據(jù)庫屬性中選擇“選項”，在右側(cè)可以修改數(shù)據(jù)庫相關(guān)的屬性。

 

1.3.1. 擴展數(shù)據(jù)庫

當(dāng)數(shù)據(jù)庫中的數(shù)據(jù)文件和日志文件被充滿時候，需要為數(shù)據(jù)文件和日志文件分配更多的空間。sql server 可以根據(jù)在新建數(shù)據(jù)庫時定義的增長參數(shù)自動擴展數(shù)據(jù)庫，也可以通過在現(xiàn)有的數(shù)據(jù)文件上分配更多的文件空間，或者在另一個新的數(shù)據(jù)文件上分配空間來手動擴展數(shù)據(jù)庫。

擴展數(shù)據(jù)庫時，必須使數(shù)據(jù)庫的容量至少增加1mb，還可以指定允許文件增長到的最大值，這樣可以防止文件無限制的增長，導(dǎo)致用盡整個磁盤空間。

舉例說明：

將class數(shù)據(jù)庫的class數(shù)據(jù)文件大小設(shè)置為100MB,不自動增長，然后添加一個新的數(shù)據(jù)文件，文件名為“class1”，初始文件大小設(shè)置為“50MB”，自動增長，最大文件大小設(shè)置為“500MB”

在對象資源管理器中，右擊class數(shù)據(jù)庫，在彈出的快捷菜單中選擇“屬性”命令，打開“數(shù)據(jù)庫屬性-class”窗口，在“數(shù)據(jù)庫屬性-class”窗口的“選擇頁”中選擇“文件”選項，打開“文件”選擇頁

配置為如下圖所示：最后單擊“確定”

 

1.3.2. 收縮數(shù)據(jù)庫

數(shù)據(jù)庫在使用一段時間后，時常會因為數(shù)據(jù)刪除而造成數(shù)據(jù)庫中空閑空間增多的情況，這時就需要減少分配給數(shù)據(jù)庫文件和事物日志文件的磁盤空間，以免浪費磁盤空間。當(dāng)數(shù)據(jù)庫中沒有數(shù)據(jù)時，可以修改數(shù)據(jù)庫文件屬性，直接改變其占用空間，但是當(dāng)數(shù)據(jù)庫中有數(shù)據(jù)時，這樣做會破壞數(shù)據(jù)庫中的數(shù)據(jù)，因此需要使用收縮的方式來縮減數(shù)據(jù)庫空間。

數(shù)據(jù)庫中的每個文件都可以通過刪除未使用的空間的方法來減小，SQL server允許通過縮小數(shù)據(jù)庫，把不使用的空間釋放出來，數(shù)據(jù)文件和日志文件都可以收縮?？梢圆捎檬謩邮湛s和自動收縮數(shù)據(jù)庫。

手動收縮數(shù)據(jù)庫的方法

 

從圖中可以看出來即可以選擇收縮數(shù)據(jù)庫，也可以選擇單獨收縮某個數(shù)據(jù)文件。

收縮數(shù)據(jù)庫：

 

在收縮數(shù)據(jù)庫時，無法將整個數(shù)據(jù)庫收縮到比初始大小更小，如果數(shù)據(jù)庫創(chuàng)建時的大小是10mb，后來增長到10mb,則該數(shù)據(jù)庫最小只能收縮到10mb，即使已經(jīng)刪除了所有數(shù)據(jù)也是10mb。但是收縮文件時，可以將數(shù)據(jù)庫文件收縮得比其初始大小更小

收縮數(shù)據(jù)文件

選擇--“任務(wù)”--“收縮”--“文件”命令，打開收縮文件對話框

 

在“文件類型”下拉列表框中選擇“數(shù)據(jù)”選項，也可以在這里選擇收縮日志文件?！碑?dāng)前分配的空間“選項和”可用空間“選項顯示了該文件的占用空間，使用空間和收縮的百分比。

釋放未使用的空間：將釋放文件中所有未使用的空間，并將文件收縮到上次分配的大小，這樣將減小文件大小，但不移動任何數(shù)據(jù)。

在釋放未使用空間前重新組織頁：將釋放文件中所有未使用的空間，并嘗試重新定位到未分配的空間。這里需要指定”將文件收縮到“選項的值。

通過將數(shù)據(jù)遷移到同一文件組中的其他文件來清空文件：將指定文件中所有數(shù)據(jù)遷移至同一文件組中的其他文件中，然后可以刪除空文件。

根據(jù)需要確定選項，然后單擊“確定”，執(zhí)行收縮操作。

自動收縮數(shù)據(jù)庫

可以通過設(shè)置“數(shù)據(jù)庫屬性--class”窗口中的“選項”選擇頁中的“自動收縮”選項參數(shù)來實現(xiàn)自動收縮功能。將“自動收縮”選項設(shè)置為“true”后，將自動收縮可用空間的數(shù)據(jù)庫。

 

1.3.3. 分離和附加數(shù)據(jù)庫

如果要將數(shù)據(jù)庫更改到同一計算機的不同sql server實例中或要移動數(shù)據(jù)庫，就可以使用分離和附加數(shù)據(jù)庫的功能了?？梢苑蛛x數(shù)據(jù)庫的數(shù)據(jù)文件和日志文件，然后將它們從新附加到同一其他SQL Server實例中。

分離數(shù)據(jù)庫

分離數(shù)據(jù)庫是指將數(shù)據(jù)庫從SQL server實例中移除，但是要保證數(shù)據(jù)庫中的數(shù)據(jù)文件和日志文件完整無損。這些分離的數(shù)據(jù)文件和日志文件可以附加到其他的數(shù)據(jù)庫實例中。

舉例說明：

將class數(shù)據(jù)庫進行分離

在對象資源管理器中右擊class數(shù)據(jù)庫，在彈出的快捷菜單中選擇“任務(wù)”---“分離”命令，打開“分離數(shù)據(jù)庫”窗口。

 

 

數(shù)據(jù)庫中有一個或多個活動連接時，“消息”列將顯示活動連接的數(shù)量，如“一個活動連接”。必須選中“刪除連接”復(fù)選框以斷開所有活動連接。

默認情況下，分離操作將在分離數(shù)據(jù)庫時保留過期的優(yōu)化統(tǒng)計信息，若要更新現(xiàn)有的優(yōu)化統(tǒng)計信息，可以選中“更新統(tǒng)計信息”復(fù)選框。

 

附加數(shù)據(jù)庫

附加數(shù)據(jù)庫時，所有的數(shù)據(jù)文件（主數(shù)據(jù)文件和次要數(shù)據(jù)文件）都必須可用。如果任何數(shù)據(jù)文件的路徑不同于首次創(chuàng)建數(shù)據(jù)庫或上次附加數(shù)據(jù)庫時的路徑，則必須指定文件的當(dāng)前路徑。

 

單擊“添加”按鈕，選擇“class.mdf”數(shù)據(jù)文件

 

單擊“確定”之后，附加文件成功。

如果數(shù)據(jù)庫的事物日志文件丟失，數(shù)據(jù)文件保持完好，也可以附加成功，在附加的時候，數(shù)據(jù)庫會自動新建事物日志文件。

 

 

1.3.4. 刪除數(shù)據(jù)庫

當(dāng)用戶不在需要自己的數(shù)據(jù)庫時就可以刪除數(shù)據(jù)庫了，但不能刪除系統(tǒng)數(shù)據(jù)庫。刪除數(shù)據(jù)庫后，文件及其數(shù)據(jù)都從服務(wù)器上的磁盤中刪除，一旦刪除數(shù)據(jù)庫，就會被永久刪除。

 

確認要刪除的數(shù)據(jù)庫

 

1.4. 表的基本管理

數(shù)據(jù)完整性：

實體完整性：確保每一行有一個唯一的標(biāo)識列：

域完整性：規(guī)定該列能夠接受的數(shù)據(jù)取值（域完整性）

引用完整性：表內(nèi)和表間的列參照

用戶定義的完整性：列級約束和表級約束，存儲過程及觸發(fā)器

主鍵：唯一標(biāo)識表中的記錄，一個主鍵值對應(yīng)一行數(shù)據(jù)。主鍵由一個或多個字段組成，主鍵值具有唯一性，而且不允許取空值（null），一個表只能有一個主鍵。

如果主鍵由多個列組成，則其中一個列將允許有重復(fù)值，但是主鍵中所有列的值得各種組合必須是唯一的。

定義主鍵可以對在不允許空值的指定列中輸入的值強制其唯一性。如果為數(shù)據(jù)庫中的某個表定義了主鍵，則可將該表與其他表相關(guān)連，從而減少對冗余數(shù)據(jù)的要求。

sql server 中的每個數(shù)據(jù)庫最多可以存儲20億個表，每個表可以有1024列，表的行數(shù)及大小僅受可用存儲空間的限制，每行最多可以存儲8060B，創(chuàng)建表時必須指定表名，列名即數(shù)據(jù)類型等。

1.5. sql server的數(shù)據(jù)類型

1. 數(shù)字型代表數(shù)字 int tinyint smallint bigint  十進制小數(shù)money smallmoney decimal  浮點數(shù)和real

2. 日期型 datetime 可以精確到0.333毫秒small

3. 字符型包括char和nchar也包含變長字符類型varchar和nvarchar

4. 定長字符  char(20)

5. 變長字符  varchar(20)

6. Char  適合存放英文一個字符占用1個字節(jié)

7. Nchar 適合存放中文一個字符占用2個字節(jié)

8. 二進制型 Binary和varbinary，bit代表一位的值0或1，rowversion代表數(shù)據(jù)庫中唯一的8位二進制。

1.5.1. 默認值

如果插入行時沒有為列指定值，則該列使用默認值，默認值可以是計算結(jié)果為常量的任何值，可以是表達式，內(nèi)置函數(shù)或數(shù)學(xué)表達式。

對于表的每個列，如果沒有分配默認的值，并且保留為空白：則

1.如果設(shè)置了允許空值的選項，則將象該列中插入NULL

2.如果沒有設(shè)置允許為空值，則該列將保持空白。但在用戶為該列提供值之前，他們將無法保存行。

1.5.2. 標(biāo)識符列

對于每個表，均可創(chuàng)建一個博暗含系統(tǒng)生成的序號值得標(biāo)識付列，該序號值以唯一的方式標(biāo)識表中的每一行。當(dāng)在表中插入行時，標(biāo)識符列可自動為應(yīng)用程序生成唯一的標(biāo)號。

標(biāo)識符列具有以下三種特點

1.列的數(shù)據(jù)類型為不帶小數(shù)的數(shù)值類型

2.在進行插入操作時，該列的值有系統(tǒng)按一定規(guī)律生成，不允許用空值

3.列值不重復(fù)，具有標(biāo)識表中每一行的作用。每個表只能有一個標(biāo)識列

創(chuàng)建標(biāo)識列，通常指定三個內(nèi)容

類型：decimal ,int,numeric,smallint,bigint,tinyint。其中decimal和numeric，小數(shù)位數(shù)必須為零

種子：指派給表中第一行的值：默認為“1”

地增量：相鄰兩個標(biāo)識值之間的增量，默認為1.

1.5.3. check約束

通過限制列可接收的值，check約束可以強制域的完整性。此類約束類似于外鍵約束，因為可以控制放入列中的值。但是他們在確定有效值的方式上有所不同。外鍵約束從其他表中獲得有效值列表，而check約束通過不基于其他列中的數(shù)據(jù)的邏輯表達式確定有效值。

可以通過任何基于邏輯運算符返回TRUE或FALSE的邏輯表達式創(chuàng)建check約束。

舉例說明：

可以通過創(chuàng)建check約束將age列中的值得范圍限制在0-200之間的數(shù)據(jù)，以防止輸入的年齡值超出正常的年齡范圍，邏輯表達式為：

age >=0 and age <=200

1.6. 管理表

在class 數(shù)據(jù)庫中創(chuàng)建表，表名為“student”，其中包含列為：學(xué)生編號（int），姓名（nvarchar(50)），×××號（varchar(18)），所在班級（thinyint），成績（tinyint）,備注（nvarchar(2000)），其中學(xué)生編號自動生成，從1開始，每增加1人則編號自動增加1，要求輸入成績的時候，其值必須為0-100，設(shè)置×××號列為主鍵：

 

為學(xué)生編號列設(shè)置標(biāo)識列，標(biāo)識增量和標(biāo)識種子設(shè)置為“1”，如圖所示：

 

分別新建“姓名”，“×××號”，“所在班級”，“成績”，“備注”如圖所示

 

由于要求輸入成績時，值必須是0-100,因此可以在成績列設(shè)置“check約束”，

 

在打開的“check約束”對話框中，單擊“添加”按鈕，然后在“常規(guī)”文本框中輸入“表達式”為“成績>=0 and成績<=100”。單擊確定即可

 

 

 

右擊“×××號”，在彈出的快捷菜單中選擇“設(shè)置主鍵”，可將該列設(shè)置為主鍵，主鍵列的左邊會顯示“×××鑰匙”，圖標(biāo)

 

設(shè)置完成后，保存該表，

 

輸入表的名稱“student”

 

在對象資源管理器中查看剛剛創(chuàng)建的student表

 

編輯student表，右擊表，在彈出的快捷菜單中選擇“編輯前200行”命令，然后可以在表中插入、更新或刪除數(shù)據(jù)了

 

修改表的結(jié)構(gòu)

如果需要修改表結(jié)構(gòu)，在表中新增或刪除字段，則需要重新對表進行設(shè)計，在對象資源管理器中，右擊student表，選擇“設(shè)計”命令

 

刪除表：如果需要刪除表，可以右擊表student，在彈出的快捷菜單中選擇“刪除”命令

 

使用T-SQL語句操作數(shù)據(jù)表

使用iNSERT語句將數(shù)據(jù)插入到表中

INSERT INTO <表名> [列名] VALUES <值列表>

INSERT INTO student (姓名，×××號，所在班級，成績) VALUES ('百合'，'152323198201190045',2,90)

更新數(shù)據(jù)

使用UPDATE語句更新表中的數(shù)據(jù)

UPDATE <表名> set <列名=更新值> [WHERE <更新條件>]

update student set 成績=‘88’where姓名=‘玉蘭’

刪除數(shù)據(jù)

使用delete語句刪除表中的數(shù)據(jù)

DELETE FROM <表名> [WHERE <刪除條件>]

DELETE FROM student WHERE 姓名=‘玉蘭’

刪除student表中所有記錄

DELETE FROM student

 

使用truncate table 語句刪除表中的數(shù)據(jù)

truncate table 語句用來刪除表中的所有行，功能上類似于沒有WHERE子句的DELETE語句。

TRUNCATE TABLE <表名>

要刪除student表中的所有記錄，可以使用如下語句

TRUNCATE TABLE student

TRUNCATE語句與DELETE語句的區(qū)別

1. TRUNCATE語句不帶WHERE子句，只能將整個表數(shù)據(jù)清空，而DELETE語句可以帶WHERE子句，允許按條件刪除某些記錄

2. TRUNCATE語句不記錄事物日志，而DELETE語句無論刪除多少條記錄，都會記錄事物日志，所以使用TRUNCATE語句刪除數(shù)據(jù)后是無法通過事物日志恢復(fù)數(shù)據(jù)。

3. TRUNCATE語句刪除表中所有行，標(biāo)識符列會重置為0，而DELETE語句不會重置標(biāo)識付列。

4. TRUNCATE語句不能用于有外鍵約束引用的表，這種情況下需要使用DELETE語句。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

當(dāng)前標(biāo)題：sqlserver數(shù)據(jù)庫的管理以及維護
URL分享：http://m.jiaotiyi.com/article/pdceic.html