SQL SERVER學習筆記(二)資料庫管理

第二部分：資料庫管理

單詞記憶：transact：處理

          create：建立

          execute：執行、完成

一、     SQL Server的特性

1、 安裝簡便：為了便于安裝、使用和管理，SQL Server2000提供了一組管理和開發工具。

2、 伸縮性和有效性

3、 支援客戶/伺服器模式（C/S）

4、 多協定相容性

5、 資料倉庫

6、 遵從ANSI/ISO SQL-92标準：Micrsoft 還對其進行了語言擴充并命名為：“Transact-SQL”

7、 支援資料複制

8、 全文檢索

9、 聯機叢書

二、T-SQL簡介

    Transact-SQL（或稱為T-SQL）是Micosoft的一個程式擴充集合。T-SQL為SQL增加了些功能,包括事務控制、異常錯誤處理和行處理。

Transact- SQL允許使用者在Transact- SQL對象中聲明和使用局部變量和常量，并且有資料類型。

三、SQL Server存儲體系構成

Ø  資料檔案

字尾為.MDF 檔案

–主資料檔案，一個資料庫隻有一個 MDF檔案，存放資料庫中各種資料實體的定義（資料字典），也可以存放其他資料，但要注意控制其大小。

字尾為.NDF檔案

    –擴充資料檔案，一個資料庫可以有多個NDF檔案，存放各種資料

1.   資料規劃(組）

2.   資料擴充

3.   檔案仿真陣列

Ø  日志檔案

字尾為.LDF檔案

     –日志檔案，一個資料庫可以有多個LDF檔案，存放聯機重做日志資料

•  注意：如果日志檔案已經填滿，SQL Server将自動停止運作并等待人工幹預，是以要經常監控日志的情況保證系統的穩定運作。

Ø  檔案組

FileGroup

–将MDF和NDF檔案在邏輯上進行分組，進行表規劃時候，隻需要指定表所需存放的組，就可以實作資料分離。

注意：系統預設的組為“PRIMARY”，不指定資料檔案所隸屬的組，則資料檔案自動劃分到“PRIMARY”組中；建立的表不指定所在的組，系統也自動配置設定該表到“PRIMARY”組中。

四、資料庫的建立

    示例：

Create Database 資料庫名

On

 (Name=‘邏輯名‘，

  FileName=‘實體檔案完全路徑(*.mdf)’,

  Size=n,

  MaxSize=n,

  FileGrowth=n)

FileGroup 組名字

  (…….)

Log On

  (Name=‘邏輯名‘，

  FileName=‘實體檔案完全路徑(*.ldf)’,

  FileGroup=n)

五、建立過程中需要注意的問題：

Ø  隻能為一個資料庫建立一個MDF檔案；

Ø  如果需要進行檔案分組，隻需要在檔案前加上FILEGROUP組名就可以了，但PRIMARY組中至少必須包含一個檔案；

Ø  如果Size和Max Size不指定機關，系統預設用“M”為機關；

Ø  如果不指定資料庫檔案的大小，系統預設為“1M”；

Ø  如果建立時沒有使用Log On字句，系統自動建立一個LDF檔案，大小為MDF檔案的1/4；

Ø  檔案的增長率FILEGROWTH應該根據實際需要設定大些，以保證系統不會頻繁擴容。

六、資料庫性能選項配置

Ø  選項設定

    Exec Sp_Dboption ‘資料庫名’,‘選項‘，’值’

 如：Exec Sp_Dboption ‘Pubs’,’AutoClose’,’False’

Ø  查詢所有可用選項

    Exec Sp_Dboption

Ø  查詢資料庫已經開啟的選項

    Exec Sp_Dboption ‘資料庫名’

 如：Exec Sp_Dboption ‘Pubs’

七、資料庫的調整

1、設定資料庫隻讀取

  Exec Sp_dboption ‘Pubs’,’Read_Only’,’True’

2、設定資料庫離線

  Exec Sp_dboption ‘Pubs’,’Offline’,’True’

3、資料庫更名（Sp_RenameDb)

  Exec Sp_dboption ‘Pubs’,’Single User’,’True’

  Exec Sp_RenameDb ‘Pubs’,’Pubs_1’

  Exec Sp_dboption ‘Pubs_1’,’Single User’,’False’

八、資料庫結構調整

1、調整資料庫結構

  Alter Database 資料庫名

•    Add File (檔案參數) To FileGroup 檔案組

•    Add Log File (檔案參數）

•    Remove File ‘邏輯資料庫檔案名字’

•    Remove FileGroup ‘組名’

•    Add FileGroup ‘組名’

•    Modify File (檔案參數)

注意：

 1、ADD FILE時如果不指定“TO FileGroup 檔案組”,系統自動将該檔案歸到“Primary”組中；

 2、REMOVE FILEGROUP時，如果組中有資料，将無法删除。

九、資料規劃的實作

表的資料規劃的實作

  Create Table Student

  (Stu_Code Varchar(10) Primary Key

   On G_Index,

   Stu_Name Varchar(10) Not Null,

   Stu_Sex Varchar(1) Not Null,

   Stu_Pic Image)

   On G_Normal

   TextImage_On G_Big

規化重點：

 1、将大資料（G_Big)和正常資料(G_Normal)分離;

 2、将索引資料（G_Index）和表資料分離；

第二部分習題樣例

一、建立本班資料庫（T607），根據規劃原則将四大類資料分組，每組一個資料檔案5－100M，按30％增長，日志檔案2個，5－100M，按50％增長。

create database T607

on primary

(

    name = 'T607_mdat',

    filename = 'd:" T607_M.mdf ',

    size = 5,

    maxsize = 100,

    filegrowth = 30%

),

filegroup G_Normal

    name = 'T607_n1dat',

    filename = 'd:" T607_N1.ndf ',

filegroup G_Change

    name = 'T607_n2dat',

    filename = 'd:" T607_N2.ndf ',

filegroup G_Index

    name = 'T607_n3dat',

    filename = 'd:" T607_N3.ndf ',

filegroup G_Big

    name = 'T607_n4dat',

    filename = 'd:" T607_N4.ndf ',

)

log on

    name = 'T607_log1',

    filename = 'd:" T607_L1.ldf ',

    filegrowth = 50%

    name = 'T607_log2',

    filename = 'd:" T607_L2.ldf ',

二、資料庫狀态屬性修改練習

1、 将建立好的資料庫設定成自動“檔案自動關閉”和自動“檔案自動收縮”方式

檔案自動關閉：exec Sp_dboption ' T607',' autoclose ',' true '

檔案自動收縮：exec Sp_dboption ' T607',' autoshrink ',' true '

2、 将建立好的資料庫設定成“隻讀方式”

exec Sp_dboption ' T607',' read only ',' true '

3、 将建立好的資料庫設定成“離線狀态”後恢複

exec sp_dboption ' T607',' offline ',' true '

4、 将建立好的資料庫名稱改名為“T607-2006”

exec sp_dboption ' T607',' single user ',' true '

exec sp_renameDb ' T607',' T607_2006 '

exec sp_dboption ' T607-2006 ',' single user ',' false '

注意事項：首先必須把隻讀屬性取消

三、修改資料庫檔案屬性

将資料庫中的一個資料檔案屬性修改為10—無限大，增長比率為40％；

alter database [T607_2006]

modify file

    name = T607_n1dat,

    size = 10,

    maxsize = unlimited,

    filegrowth = 40%

注意事項：修改時，請勿指定實體名稱。

四、收縮資料庫檔案

将資料庫中的一個檔案收縮到3M；

dbcc shrinkfile(‘ T607_n2dat ‘, 3)

注意事項：必須先use T607_2006才能收縮

五、組的建立和維護

在資料庫中建立一個新的組“G_TEST”

add filegroup G_TEST

六、組中增加一個檔案

在資料庫中增加一個新的檔案“T607_TEST.ndf”，并自動加入到“G_TEST”組中；

alter database [T607-2006]

add file

    name = 'T607_TEST',

    filename = 'd:" T607_TEST.ndf'

to filegroup G_ TEST

七、向資料庫中增加日志檔案

在資料庫中增加一個新的日志檔案“T607_LOG_TEST”檔案；

add log file

    name = 'T607_LOG_TEST',

    filename = 'd:" T607_LOG_TEST.log'

八、資料庫的規劃方式

建立一個學生資料表，包含字段：

學生編碼、

學生名稱不允許重複

學生性别、

學生年齡 15—30、

學生生日、

學生位址、

學生國籍、

學生備注大量文本、

學生照片 JPG

根據表規劃及優化原則，建立該表：

create table 學生

    學生編碼 smallint,

    學生名稱 varchar(10) unique,

    學生性别 nvarchar(1),

    學生年齡 tinyint check(學生年齡>=15 and 學生年齡<=30),

    學生生日 datetime,

    學生位址 nvarchar(20),

    學生國籍 varchar(10),

    學生備注 text,

    學生照片 image

on group1

textimage_on group2

九、删除資料庫

删除本練習中建立的資料庫

    drop database T607-2006

本文轉自溫景良部落格園部落格，原文連結：http://www.cnblogs.com/wenjl520/archive/2008/11/01/1324393.html，如需轉載請自行聯系原作者