第7章 創(chuàng)建和使用數(shù)據(jù)庫

1、Principle and Application of Database SystemAnQing Teachers College Department of Computer & Information數(shù)據(jù)庫原理與應(yīng)用數(shù)據(jù)庫原理與應(yīng)用Principle and Application of Database System安慶師范學(xué)院計算機與信息學(xué)院安慶師范學(xué)院計算機與信息學(xué)院Principle and Application of Database System 第第7章章 創(chuàng)建和使用數(shù)據(jù)庫創(chuàng)建和使用數(shù)據(jù)庫 7.1查看數(shù)據(jù)庫 7.2SQL Server數(shù)據(jù)庫和文件 7.3 創(chuàng)建和使用數(shù)據(jù)

2、庫Principle and Application of Database SystemSQL Server 2000系統(tǒng)數(shù)據(jù)庫 在安裝SQL Server 2000時，將創(chuàng)建四個系統(tǒng)數(shù)據(jù)庫：master、model、msdb和tempdb。(1) master數(shù)據(jù)庫包含了SQL Server的登錄帳號、系統(tǒng)配置、數(shù)據(jù)庫位置及數(shù)據(jù)庫錯誤信息等系統(tǒng)信息，用于控制用戶數(shù)據(jù)庫和SQL Server的運行。(2) model數(shù)據(jù)庫為新創(chuàng)建的數(shù)據(jù)庫提供模板。(3) msdb數(shù)據(jù)庫為SQL Server Agent調(diào)度信息和作業(yè)記錄提供存儲空間。 (4) tempdb數(shù)據(jù)庫為臨時表和臨時存儲過程提供存儲

3、空間，所有與系統(tǒng)連接的用戶的臨時表和臨時存儲過程都存儲于該數(shù)據(jù)庫中。 Principle and Application of Database SystemSQL Server 2000示例數(shù)據(jù)庫 在安裝SQL Server 2000時，將創(chuàng)建二個示例數(shù)據(jù)庫：pubs和Northwind。(1) Pubs是一個書籍出版公司的數(shù)據(jù)庫(2) Northwind是一個貿(mào)易公司的銷售數(shù)據(jù)庫。Principle and Application of Database Systemn關(guān)系數(shù)據(jù)庫是按照二維表結(jié)構(gòu)方式組織的數(shù)據(jù)集合，數(shù)據(jù)庫中的每個表都稱為一個關(guān)系。二維表由行和列組成，表的行稱為元組，也稱記錄

4、；列稱為屬性，也稱字段。Principle and Application of Database System1. 邏輯數(shù)據(jù)庫邏輯數(shù)據(jù)庫SQL Server2000數(shù)據(jù)庫對象Principle and Application of Database System2. 物理數(shù)據(jù)庫物理數(shù)據(jù)庫1) 文件文件(1)主數(shù)據(jù)文件 主數(shù)據(jù)文件簡稱主文件，它是數(shù)據(jù)庫的關(guān)鍵文件，包含了數(shù)據(jù)庫的啟動信息。每個數(shù)據(jù)庫必須有且僅能有一個主文件，其默認(rèn)擴展名為.MDF。例如master數(shù)據(jù)庫的主數(shù)據(jù)文件名為master.mdf。 (2) 輔助數(shù)據(jù)文件 輔助數(shù)據(jù)文件簡稱輔（助）文件，用于存儲未包括在主文件內(nèi)的其他數(shù)據(jù)。

5、輔助文件的默認(rèn)擴展名為.NDF。輔助文件是可選的，根據(jù)具體情況，可以創(chuàng)建多個輔助文件，也可以不用輔助文件。一般當(dāng)數(shù)據(jù)庫很大時，有可能需要創(chuàng)建多個輔助文件；而數(shù)據(jù)庫較小時，則只要創(chuàng)建主文件而不需要輔助文件。 (3) 日志文件 日志文件用于保存恢復(fù)數(shù)據(jù)庫所需的事務(wù)日志信息。每個數(shù)據(jù)庫至少有一個日志文件，也可以有多個。日志文件的擴展名為.LDF。 Principle and Application of Database System 2) 文件組文件組(2) 用戶定義文件組T-SQL語句中用于創(chuàng)建和修改數(shù)據(jù)庫的語句分別是CREATE DATABASE語句和ALTER DATABASE，在這兩個語句

6、中都可以用FILEGROUP關(guān)鍵字指定文件組，用戶定義文件組就是指使用這兩個語句創(chuàng)建或修改數(shù)據(jù)庫時指定的文件組。(1) 主文件組主文件組包含主數(shù)據(jù)文件和任何沒有明確指派給其它文件組的其它文件。Principle and Application of Database System7.3 創(chuàng)創(chuàng) 建建 數(shù)數(shù) 據(jù)據(jù) 庫庫1 界面方式創(chuàng)建數(shù)據(jù)庫界面方式創(chuàng)建數(shù)據(jù)庫1. 數(shù)據(jù)庫創(chuàng)建數(shù)據(jù)庫創(chuàng)建1) 通過企業(yè)管理器創(chuàng)建數(shù)據(jù)庫通過企業(yè)管理器創(chuàng)建數(shù)據(jù)庫Principle and Application of Database System1 界面方式創(chuàng)建數(shù)據(jù)庫界面方式創(chuàng)建數(shù)據(jù)庫【例【例7.17.1】創(chuàng)建數(shù)據(jù)庫XS

7、BOOK，初始大小為5MB，最大大小20MB，數(shù)據(jù)庫自動增長，增長方式是按10%比例增長；日志文件初始為2MB，最大可增長到5MB，按1MB增長。Principle and Application of Database SystemPrinciple and Application of Database SystemPrinciple and Application of Database SystemPrinciple and Application of Database SystemPrinciple and Application of Database SystemPrincip

8、le and Application of Database SystemPrinciple and Application of Database SystemPrinciple and Application of Database SystemPrinciple and Application of Database SystemPrinciple and Application of Database SystemPrinciple and Application of Database System7.2.1 界面方式創(chuàng)建數(shù)據(jù)庫Principle and Application of

9、 Database System2. 數(shù)據(jù)庫修改數(shù)據(jù)庫修改Principle and Application of Database System1) 改變數(shù)據(jù)文件的大小和增長方式改變數(shù)據(jù)文件的大小和增長方式Principle and Application of Database System 界面方式創(chuàng)建數(shù)據(jù)庫2) 增加數(shù)據(jù)文件增加數(shù)據(jù)文件Principle and Application of Database System3) 刪除數(shù)據(jù)文件刪除數(shù)據(jù)文件4) 增加或刪除文件組增加或刪除文件組Principle and Application of Database System【例例7.

10、2】使用T-SQL語句，創(chuàng)建一個名為xsbook1的數(shù)據(jù)庫，其初始大小為5MB，最大大小20MB，允許數(shù)據(jù)庫自動增長，增長方式是按10%比例增長；日志文件初始為2MB，最大可增長到5MB，按1MB增長。命令方式創(chuàng)建數(shù)據(jù)庫命令方式創(chuàng)建數(shù)據(jù)庫1. 用用CREATE DATABASE創(chuàng)建數(shù)據(jù)庫創(chuàng)建數(shù)據(jù)庫Principle and Application of Database System 命令方式創(chuàng)建數(shù)據(jù)庫1. 用用CREATE DATABASE創(chuàng)建數(shù)據(jù)庫創(chuàng)建數(shù)據(jù)庫Principle and Application of Database System 命令方式創(chuàng)建數(shù)據(jù)庫命令方式創(chuàng)建數(shù)據(jù)庫CREA

11、TE DATABASE xsbook1ON ( NAME=xsbook1_Data, FILENAME=C:Program filesMicrosoft SQL ServerMSSQLdataxsbook1.mdf, SIZE=5MB, MAXSIZE=20MB, FILEGROWTH=10%)LOG ON (NAME= xsbook1_Log, FILENAME= C:Program filesMicrosoft SQL ServerMSSQLdata xsbook1_Log.ldf, SIZE=2MB, MAXSIZE=5MB, FILEGROWTH=1MB)GOPrinciple and

12、 Application of Database System 命令方式創(chuàng)建數(shù)據(jù)庫命令方式創(chuàng)建數(shù)據(jù)庫CREATE DATABASE database_name ON 子句 LOG ON 子句 Principle and Application of Database System7.2.2 命令方式創(chuàng)建數(shù)據(jù)庫命令方式創(chuàng)建數(shù)據(jù)庫【例【例7.3】創(chuàng)建一個名為DB1的數(shù)據(jù)庫，該數(shù)據(jù)庫只包含一個主數(shù)據(jù)文件和一個主日志文件，它們均采用系統(tǒng)默認(rèn)存儲路徑和文件名。相應(yīng)的T-SQL語句為：CREATE DATABASE DB1ON ( NAME = DB1_data, FILENAME = C:Program

13、 filesMicrosoft SQL ServerMSSQLdatadb1.mdf ) GO Principle and Application of Database System7.2.2 命令方式創(chuàng)建數(shù)據(jù)庫命令方式創(chuàng)建數(shù)據(jù)庫【例【例7.4】 創(chuàng)建一個名為DB2的數(shù)據(jù)庫，它有三個數(shù)據(jù)文件，其中主數(shù)據(jù)文件為10MB，最大大小為100MB，按10MB增長；2個輔數(shù)據(jù)文件為10MB，最大大小不限，按10%增長；有2個日志文件，大小均為5MB，最大大小均為50MB，按5MB增長。 Principle and Application of Database System7.2.2 命令方式創(chuàng)建數(shù)據(jù)

14、庫命令方式創(chuàng)建數(shù)據(jù)庫CREATE DATABASE DB2ON PRIMARY (NAME = DB2_data1, FILENAME = C:Program filesMicrosoft SQL ServerMSSQLdatadb2_data1.mdf, SIZE = 10MB, MAXSIZE = 100MB, FILEGROWTH = 10MB), (NAME = DB2_data2, FILENAME = C:Program filesMicrosoft SQL ServerMSSQLdatadb2_data2.ndf, SIZE = 10MB, MAXSIZE = UNLIMITED

15、, FILEGROWTH = 10%), (NAME = DB2_data3, FILENAME = C:Program filesMicrosoft SQL ServerMSSQLdatadb2_data3.ndf, SIZE = 10MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%)Principle and Application of Database System7.2.2 命令方式創(chuàng)建數(shù)據(jù)庫命令方式創(chuàng)建數(shù)據(jù)庫LOG ON (NAME = DB2_log1, FILENAME = C:Program filesMicrosoft SQL Server

16、MSSQLdatadb2_log1.ldf, SIZE = 5MB, MAXSIZE = 50MB, FILEGROWTH = 5MB), (NAME = DB2_log2, FILENAME = C:Program filesMicrosoft SQL ServerMSSQLdatadb2_log2.ldf, SIZE = 5MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)GOPrinciple and Application of Database System【例【例7.5】創(chuàng)建一個具有】創(chuàng)建一個具有3個文件組的數(shù)據(jù)庫個文件組的數(shù)據(jù)庫DB3。主文件組包括文件。主

17、文件組包括文件db3_dat1和和db3_dat2，文件初始大小均為，文件初始大小均為10MB，按，按10%增長；第增長；第2個文件組名為個文件組名為db3G1，包括，包括文件文件db3_dat3和和db3_dat4，文件初始大小，文件初始大小均為均為5MB,最大為最大為30MB，按，按5MB增長；第增長；第2個個文件組名為文件組名為db3G2，包括文件，包括文件db3_dat5，文，文件初始大小為件初始大小為20MB，最大為，最大為100MB，按，按10MB增長。增長。Principle and Application of Database SystemCREATE DATABASE DB

18、3ONPRIMARY(NAME=db3_dat1, FILENAME=C:Program FilesMicrosoft SQL ServerMSSQLDatadb3_dat1.mdf, SIZE=10MB, MAXSIZE=100MB, FILEGROWTH=10%),(NAME=db3_dat2, FILENAME=C:Program FilesMicrosoft SQL ServerMSSQLDatadb3_dat2.ndf, SIZE=10MB, MAXSIZE=100MB, FILEGROWTH=10%),Principle and Application of Database Sy

19、stemFILEGROUP db3G1(NAME=db3_dat3, FILENAME=C:Program FilesMicrosoft SQL ServerMSSQLDatadb3_dat3.ndf, SIZE=5MB, MAXSIZE=30MB, FILEGROWTH=5MB),(NAME=db3_dat4, FILENAME=C:Program FilesMicrosoft SQL ServerMSSQLDatadb3_dat4.ndf, SIZE=5MB, MAXSIZE=30MB, FILEGROWTH=5MB),Principle and Application of Databa

20、se SystemFILEGROUP db3G2(NAME=db3_dat5, FILENAME=C:Program FilesMicrosoft SQL ServerMSSQLDatadb3_dat5.ndf, SIZE=10MB, MAXSIZE=50MB, FILEGROWTH=20%)LOG ON (NAME=db3_log, FILENAME=C:Program FilesMicrosoft SQL ServerMSSQLDatadb3_log.ldf, SIZE=20MB, MAXSIZE=100MB, FILEGROWTH=10MB)Principle and Application of Database System 命令方式創(chuàng)建數(shù)據(jù)庫2. 用用ALTER DATABASE修改數(shù)據(jù)庫修改數(shù)據(jù)庫Principle and Application of Database System 命令方式創(chuàng)建數(shù)據(jù)庫ALTER DATABASE語句的完整語法格式：語句的完整語法格式： Principle and Application of Database System 命令方式創(chuàng)建