3.2 用戶數(shù)據(jù)庫創(chuàng)建與修改

一個(gè)SQL Server實(shí)例，可以創(chuàng)建32000多個(gè)用戶數(shù)據(jù)庫。在創(chuàng)建數(shù)據(jù)庫之前，首先用戶應(yīng)該清楚是否有相關(guān)的權(quán)限。要?jiǎng)?chuàng)建數(shù)據(jù)庫，必須至少擁有CREATE DATABASE、CREATE ANY DATABASE或ALTER ANY DATABASE等語句的權(quán)限。其次，創(chuàng)建數(shù)據(jù)庫的用戶將成為該數(shù)據(jù)庫的所有者。

3.2.1 用戶數(shù)據(jù)庫的創(chuàng)建

在SQL Server中，用戶要?jiǎng)?chuàng)建數(shù)據(jù)庫必須確定數(shù)據(jù)庫的名稱、所有者、大小以及存儲該數(shù)據(jù)庫的文件和文件組。數(shù)據(jù)庫名稱必須遵循為標(biāo)識符指定的規(guī)則。這些規(guī)則主要包括以下幾點(diǎn)：

（1）數(shù)據(jù)庫名稱長度為1～128個(gè)字符。

（2）名稱首字符必須是一個(gè)英文字母或“_”“#”和“@”中的任意字符。

（3）在中文版SQL Server 2016中，可以直接使用漢字為數(shù)據(jù)庫命名。

（4）名稱中不能出現(xiàn)空格，不允許使用SQL Server 2016的保留字。

每個(gè)SQL Server數(shù)據(jù)庫至少具有兩個(gè)操作系統(tǒng)文件，即一個(gè)數(shù)據(jù)文件和一個(gè)日志文件。數(shù)據(jù)文件包含數(shù)據(jù)庫對象，日志文件包含恢復(fù)數(shù)據(jù)庫中的所有事務(wù)所需的信息。為了便于分配和管理，可以將數(shù)據(jù)文件集合起來放到文件組中。

在SQL Server 2016中創(chuàng)建用戶數(shù)據(jù)庫主要有以下兩種形式。

1．在SQL Server Management Studio中創(chuàng)建數(shù)據(jù)庫

（1）啟動SQL Server Management Studio，在“對象資源管理器”中右擊“數(shù)據(jù)庫”選項(xiàng)，在彈出的快捷菜單中選擇“新建數(shù)據(jù)庫”命令，如圖3-3所示，打開“新建數(shù)據(jù)庫”窗口。

在“新建數(shù)據(jù)庫”窗口中的“常規(guī)”選項(xiàng)卡，如圖3-4所示，有以下幾個(gè)可選項(xiàng)。

①在“數(shù)據(jù)庫名稱”文本框中輸入數(shù)據(jù)庫名稱，如test01。

②若要通過接受所有的默認(rèn)值來創(chuàng)建數(shù)據(jù)庫，則單擊“確定”按鈕；否則，繼續(xù)后面的可選項(xiàng)目的選擇。

③若要更改所有者名稱，單擊“所有者”后的…按鈕選擇其他所有者。

④若要啟用數(shù)據(jù)庫的全文搜索，選中“使用全文索引”復(fù)選框。

⑤若要更改主數(shù)據(jù)文件和事務(wù)日志文件的默認(rèn)值，在“數(shù)據(jù)庫文件”列表框中單擊相應(yīng)的單元格并輸入新值。各項(xiàng)的具體含義如下。

●邏輯名稱：默認(rèn)的邏輯數(shù)據(jù)文件和日志文件的名稱。

●文件類型：數(shù)據(jù)庫文件的類型。默認(rèn)情況下，在SQL Server 2016中數(shù)據(jù)庫包含一個(gè)主數(shù)據(jù)文件和一個(gè)日志文件。

●文件組：數(shù)據(jù)庫中的數(shù)據(jù)文件所屬的默認(rèn)文件組為PRIMARY，日志文件沒有文件組的概念。

●初始大小：默認(rèn)的數(shù)據(jù)文件初始大小為5MB，日志文件為2MB。

●自動增長：顯示默認(rèn)設(shè)置的數(shù)據(jù)文件和日志文件的增長方式。

●位置：顯示數(shù)據(jù)庫物理文件的存放路徑和名稱。

●路徑：顯示數(shù)據(jù)庫物理文件存放的物理路徑。

●文件名：顯示數(shù)據(jù)文件和日志文件的物理名稱（在路徑右邊）。

（2）切換到“新建數(shù)據(jù)庫”窗口中的“選項(xiàng)”選項(xiàng)卡中，如圖3-5所示。其中有以下幾個(gè)可選項(xiàng)。

①若要更改數(shù)據(jù)庫的排序規(guī)則，從“排序規(guī)則”下拉列表框中選擇一個(gè)排序規(guī)則。

②若要更改恢復(fù)模式，從“恢復(fù)模式”下拉列表框中選擇一個(gè)恢復(fù)模式。

③若要更改數(shù)據(jù)庫其他選項(xiàng)，從下面的列表中根據(jù)需要修改選項(xiàng)值。

（3）切換到“文件組”選項(xiàng)卡進(jìn)行設(shè)置，如圖3-6所示。如果要添加文件組，可以單擊“添加文件組”按鈕，然后輸入文件組的名稱。

（4）如果單擊“腳本”按鈕，系統(tǒng)還會在查詢窗口自動生成創(chuàng)建數(shù)據(jù)庫test01命令代碼，如果執(zhí)行此代碼，系統(tǒng)也會創(chuàng)建數(shù)據(jù)庫test01。

（5）所有參數(shù)設(shè)置完畢后，單擊“確定”按鈕，新的數(shù)據(jù)庫就創(chuàng)建成功。

展開“對象資源管理器”中的數(shù)據(jù)庫文件夾，就可以觀察到test01數(shù)據(jù)庫已經(jīng)創(chuàng)建成功。

需要說明的是，SQL Server 2016將同一類型的文件以組的形式組織起來。其中將主數(shù)據(jù)文件和任何沒有明確分配給其他文件組的文件存放在主文件組PRIMARY。如果查看一下SQL Server 2016自帶的系統(tǒng)數(shù)據(jù)表就會發(fā)現(xiàn)，其所有頁都分配在主文件組中。

2．利用Transact-SQL語句創(chuàng)建數(shù)據(jù)庫

在SQL Server 2016中，也可以利用Transact-SQL提供的CREATE DATABASE語句來創(chuàng)建數(shù)據(jù)庫。創(chuàng)建步驟為：選擇“文件”→“新建”→“使用當(dāng)前連接查詢”菜單命令，彈出查詢設(shè)計(jì)器窗口，在該窗口中編寫Transact-SQL語句。

下面是創(chuàng)建數(shù)據(jù)庫的命令格式，這里只介紹主要參數(shù)內(nèi)容。

（1）CREATE DATABASE語句的基本格式如下：

上述格式的主要參數(shù)說明如下。

①database_name：新建數(shù)據(jù)庫的名稱，同一個(gè)SQL Server的實(shí)例中數(shù)據(jù)庫名稱必須唯一，且最多可以包含128個(gè)字符。

②ON：顯式定義用來存儲數(shù)據(jù)庫數(shù)據(jù)部分的數(shù)據(jù)文件。當(dāng)后面是以逗號分隔的、用以定義主文件組的數(shù)據(jù)文件的<filespec>項(xiàng)列表時(shí)，需要使用ON。

③<filespec>：控制文件屬性。詳細(xì)定義數(shù)據(jù)文件或日志文件屬性。

④PRIMARY：指定關(guān)聯(lián)的<filespec>列表定義主文件。在主文件組的<filespec>項(xiàng)中指定的第一個(gè)文件將成為主文件，一個(gè)數(shù)據(jù)庫只能有一個(gè)主文件。

⑤<filegroup>：控制文件組屬性。

⑥LOG ON：顯式定義數(shù)據(jù)庫的日志文件。LOG ON后跟以逗號分隔的用以定義日志文件的<filespec>項(xiàng)列表。

⑦COLLATE collation_name：指定數(shù)據(jù)庫的默認(rèn)排序規(guī)則。

⑧WITH <external_access_option>：控制外部與數(shù)據(jù)庫之間的雙向訪問。

（2）filespec的定義格式如下：

上述格式的主要參數(shù)說明如下。

①<filespec>：控制文件屬性。

②NAME=logical_file_name：指定文件的邏輯名稱。

③FILENAME='os_file_name'：指定操作系統(tǒng)（物理）文件名稱。os_file_name是創(chuàng)建文件時(shí)由操作系統(tǒng)使用的路徑和文件名。

④SIZE=size：指定文件的大小。如果沒有為主文件提供size，則數(shù)據(jù)庫引擎將使用model數(shù)據(jù)庫中的主文件的大小，默認(rèn)值為MB。

⑤MAXSIZE=max_size：指定文件可增大到的最大大小。

⑥FILEGROWTH=growth_increment：指定文件的自動增量。growth_increment為每次需要新空間時(shí)為文件添加的空間量，該值可以固定值或百分比（%）為單位指定。

⑦UNLIMITED：指定文件將增長到磁盤充滿。

（3）filegroup的定義如下：

上述格式的主要參數(shù)說明如下。

①FILEGROUP filegroup_name：文件組的邏輯名稱。

②DEFAULT：指定命名文件組為數(shù)據(jù)庫中的默認(rèn)文件組。

（4）external_access_option的定義如下：

上述格式的主要參數(shù)說明如下。

①DB_CHAINING{ON|OFF}：當(dāng)指定為ON時(shí)，數(shù)據(jù)庫可以為跨數(shù)據(jù)庫所有權(quán)鏈接的源或目標(biāo)。當(dāng)為OFF時(shí)，數(shù)據(jù)庫不能參與跨數(shù)據(jù)庫所有權(quán)鏈接。默認(rèn)值為OFF。

②TRUSTWORTHY{ON|OFF}：當(dāng)指定為ON時(shí)，使用模擬上下文的數(shù)據(jù)庫模塊可以訪問數(shù)據(jù)庫以外的資源。默認(rèn)值為OFF。只要附加數(shù)據(jù)庫，TRUSTWORTHY就會設(shè)置為OFF。

下面舉例看一下實(shí)際的應(yīng)用。

【例3-1】 創(chuàng)建數(shù)據(jù)庫student，并指定數(shù)據(jù)庫的數(shù)據(jù)文件所在位置、初始容量、最大容量和文件增長量。

程序代碼如下：

      CREATE DATABASE student
      ON
      (
           NAME = 'student',
           FILENAME = 'D:\sqlprogram\student.mdf',
           SIZE = 5MB,
           MAXSIZE = 10MB,
           FILEGROWTH = 5%
      )
      GO

在查詢設(shè)計(jì)器中輸入上述程序后，單擊“執(zhí)行”按鈕，數(shù)據(jù)庫student就創(chuàng)建成功。

本例中僅指定數(shù)據(jù)庫student的數(shù)據(jù)文件的相關(guān)屬性，而日志文件的屬性則以model數(shù)據(jù)庫中日志文件為模板建成。在“對象資源管理器”的“數(shù)據(jù)庫”選項(xiàng)中，可看到student數(shù)據(jù)庫。

【例3-2】 創(chuàng)建數(shù)據(jù)庫teaching，并指定數(shù)據(jù)庫的數(shù)據(jù)文件和日志文件的所在位置、初始容量、最大容量和文件增長量。

程序代碼如下：

      CREATE DATABASE teaching
      ON PRIMARY
       (NAME = 'teaching',
      FILENAME = 'D:\sqlprogram\teaching.mdf',
      SIZE = 6MB,
      MAXSIZE = 30MB,
      FILEGROWTH = 1MB)
       LOG ON
       (NAME = 'teaching_log',
       FILENAME = 'D:\sqlprogram\teaching_log.ldf',
       SIZE = 2MB,
       MAXSIZE = 10 MB,
       FILEGROWTH = 10%)
       COLLATE Chinese_PRC_CI_AS
      GO

本例創(chuàng)建一個(gè)大小為8MB教務(wù)數(shù)據(jù)庫teaching，其數(shù)據(jù)文件6MB，日志文件2MB。在以后的章節(jié)中如不特別指明，本書例題將以teaching為默認(rèn)數(shù)據(jù)庫介紹相關(guān)內(nèi)容。

3.2.2 修改數(shù)據(jù)庫

創(chuàng)建完數(shù)據(jù)庫后，若需要修改，可以使用SQL Server Management Studio與Transact-SQL語句兩種方法。

1．使用SQL Server Management Studio修改數(shù)據(jù)庫

使用SQL Server Management Studio修改數(shù)據(jù)庫，其主要步驟如下。

（1）啟動SQL Server Management Studio，在“對象資源管理器”中用戶可以右擊所選擇的數(shù)據(jù)庫test01，在彈出的快捷菜單中選擇“屬性”命令，打開“數(shù)據(jù)庫屬性”窗口，如圖3-7所示。在“數(shù)據(jù)庫屬性”窗口的“常規(guī)”選項(xiàng)卡中，顯示的是數(shù)據(jù)庫的基本信息，這些信息不能修改。

（2）單擊“文件”選項(xiàng)卡，如圖3-8所示，可以修改數(shù)據(jù)庫的邏輯名稱、初始大小、自動增長等屬性，也可以根據(jù)需要添加數(shù)據(jù)文件和日志文件，還可以更改數(shù)據(jù)庫的所有者。

例如，添加一個(gè)數(shù)據(jù)文件test011、一個(gè)日志文件test011_log，并分別設(shè)置其增長方式和大小。單擊“添加”按鈕，依次按照圖3-9所示的內(nèi)容輸入，單擊“確定”按鈕即可。

（3）在“文件組”選項(xiàng)卡中，可以修改現(xiàn)有的文件組，也可以指定數(shù)據(jù)庫的默認(rèn)文件組、添加新文件組。

（4）在“選項(xiàng)”選項(xiàng)卡中，修改數(shù)據(jù)庫的排序規(guī)則。

“數(shù)據(jù)庫屬性”窗口包含的各種屬性，只要需要就可以選擇相應(yīng)的選項(xiàng)卡來修改。

2．使用Transact-SQL語句修改數(shù)據(jù)庫

Transact-SQL提供了修改數(shù)據(jù)庫的語句ALTER DATABASE。這里只介紹基本格式說明和主要參數(shù)。

（1）ALTER DATABASE語句的語法如下：

上述格式的主要參數(shù)說明如下。

①database_name：要修改的數(shù)據(jù)庫的名稱。

②<add_or_modify_files>：：=：指定要添加或修改的文件。

③<add_or_modify_filegroups>：：=：在數(shù)據(jù)庫中添加或刪除文件組。

④<set_database_options>：設(shè)置數(shù)據(jù)庫選項(xiàng)。

⑤MODIFY NAME=new_database_name：使用指定的名稱重命名數(shù)據(jù)庫。

⑥COLLATE collation_name：指定數(shù)據(jù)庫的排序規(guī)則。

（2）<add_or_modify_files>子句的語法如下：

上述格式的主要參數(shù)說明如下。

①ADD FILE：將文件添加到數(shù)據(jù)庫。

②TO FILEGROUP {filegroup_name | DEFAULT}：將指定文件添加到的文件組。

③ADD LOG FILE：將要添加的日志文件添加到指定的數(shù)據(jù)庫。

④REMOVE FILE logical_file_name：從SQL Server的實(shí)例中刪除邏輯文件說明并刪除物理文件。除非文件為空；否則無法刪除文件。

⑤MODIFY FILE：指定應(yīng)修改的文件，一次只能更改一個(gè)<filespec>屬性。

下面通過幾個(gè)例題來進(jìn)一步介紹修改數(shù)據(jù)庫的內(nèi)容。

【例3-3】 為student數(shù)據(jù)庫增加一個(gè)日志文件。

程序代碼如下：

      ALTER DATABASE student
      ADD LOG FILE
      (
      NAME = stud_log,
      FILENAME = 'D:\sqlprogram\stud_log.LDF',
      SIZE = 2 MB,
      MAXSIZE = 6 MB,
      FILEGROWTH = 1MB)
      GO

【例3-4】 修改student數(shù)據(jù)庫的排序規(guī)則。

程序代碼如下：

      ALTER DATABASE student
      COLLATE Chinese_PRC_CI_AS_KS

【例3-5】 給student數(shù)據(jù)庫添加文件組studentfgrp，再添加數(shù)據(jù)文件studentfile.ndf到文件組studentfgrp中。

程序代碼如下：

      ALTER DATABASE student
      ADD FILEGROUP studentfgrp
      GO
      ALTER DATABASE student
      ADD FILE
      (
        NAME = 'studentfile',
        FILENAME = 'D:\sqlprogram\studentfile.ndf')
      TO FILEGROUP studentfgrp
      GO

3.2.3 數(shù)據(jù)庫文件的腳本生成

1．創(chuàng)建對象的腳本代碼

在SQL Server中，要對數(shù)據(jù)庫對象執(zhí)行基本操作時(shí)，通常需要編寫SQL腳本。對于常見數(shù)據(jù)庫對象的基本操作，SQL Server提供了快速生成操作腳本的功能。如要?jiǎng)?chuàng)建test01數(shù)據(jù)庫的腳本，可按以下步驟完成。

（1）在“對象資源管理器”中，依次展開“服務(wù)器”→“數(shù)據(jù)庫”→test01子目錄，右擊test01，在彈出的快捷菜單中選擇“編寫數(shù)據(jù)庫腳本為”命令，出現(xiàn)一個(gè)級聯(lián)菜單。

（2）其中有9個(gè)編寫腳本子命令，執(zhí)行“CREATE到”→“新查詢編輯器窗口”命令，如圖3-10所示。

（3）系統(tǒng)將打開一個(gè)新查詢編輯器窗口，執(zhí)行連接并顯示完整CREATE DATABASE的語句，結(jié)果如圖3-11所示。

另外，在數(shù)據(jù)庫及其對象的許多對話框的操作過程中，通過單擊“腳本”圖標(biāo)按鈕，也可以得到當(dāng)前操作的腳本圖標(biāo)，具體操作如圖3-12所示。

2．使用模板創(chuàng)建腳本代碼

SQL Server 2016中為許多任務(wù)提供了腳本模板，只需要為模板指定相應(yīng)參數(shù)就可以自動生成相應(yīng)模板，從而快速完成代碼的書寫。使用模板創(chuàng)建數(shù)據(jù)庫test02腳本的步驟如下。

（1）在Management Studio的“視圖”菜單中單擊“模板資源管理器”命令。

（2）模板資源管理器中的模板是分組列出的，先展開“SQL Server模板”→DATABASE子目錄，再雙擊CREATE DATABASE。

（3）在“連接到數(shù)據(jù)庫引擎”對話框中填寫連接信息，再單擊“連接”按鈕。此時(shí)將打開一個(gè)新查詢編輯器窗口，其中包含“創(chuàng)建數(shù)據(jù)庫”模板的內(nèi)容，代碼如下：

      USE master
      GO
      -- Drop the database if it already exists
      IF EXISTS(
        SELECT name
        FROM sys.databases
        WHERE name = N'<Database_Name,sysname,Database_Name>'
      )
      DROP DATABASE <Database_Name,sysname,Database_Name>
      GO
      CREATE DATABASE <Database_Name,sysname,Database_Name>
      GO

在模板代碼中，多處出現(xiàn)了<Database_Name，sysname，Database_Name>，這就是模板參數(shù)。它指明了有個(gè)參數(shù)名為Database_Name，其類型為sysname，其默認(rèn)值為Database_Name。此時(shí)不能執(zhí)行該代碼，需要為該模板參數(shù)指定其具體數(shù)值。

（4）單擊“查詢”→“指定模板參數(shù)的值”菜單命令，彈出“指定模板參數(shù)的值”對話框，如圖3-13所示。

（5）在該對話框中，“值”列包含一個(gè)Database_Name參數(shù)的建議值。在“值”參數(shù)框中輸入test02，再單擊“確定”按鈕。

（6）系統(tǒng)自動用輸入的test02替代了上述參數(shù)值<Database_Name，sysname，Database_Name>，代碼變?yōu)椋?/p>

      --=============================================
      -- Create database template
      -- =============================================
      USE master
      GO
      -- Drop the database if it already exists
      IF EXISTS(
      SELECT name
      FROM sys.databases
      WHERE name = N'test02'
      )
      DROP DATABASE test02
      GO
      CREATE DATABASE test02
      GO

（7）執(zhí)行代碼，即可創(chuàng)建數(shù)據(jù)庫test02。

可以看出，利用模板使得創(chuàng)建腳本更容易、更快捷，不需要記憶復(fù)雜的命令，也不需要編寫冗長的代碼，就可以完成大多數(shù)腳本的書寫。