Sql Server系列：数据库操作

1. 创建数据库 CREATE DATABASE语法： CREATE DATABASE database_name [ ON [ PRIMARY ] filespec [ ,...n ] [ , filegroup [ ,...n ] ] [ LOG ON filespec [ ,...n ] ] ] [ COLLATE collation_name ] filespec :: = {( NAME = logical_file_name , FILENAME

1. 创建数据库

　　CREATE DATABASE语法：

<span>CREATE</span> <span>DATABASE</span><span> database_name 
</span><span>[</span><span> ON 
      [ PRIMARY </span><span>]</span> <span>filespec<span>></span> <span>[</span><span> ,...n </span><span>]</span> 
      <span>[</span><span> , <filegroup> [ ,...n </filegroup></span><span>]</span><span> ] 
      </span><span>[</span><span> LOG ON <filespec> [ ,...n </filespec></span><span>]</span><span> ] 
] 
</span><span>[</span><span> COLLATE collation_name </span><span>]</span>
<span>filespec<span>></span> ::<span>=</span><span> 
{
(
    NAME </span><span>=</span><span> logical_file_name ,
    FILENAME </span><span>=</span> { <span>'</span><span>os_file_name</span><span>'</span> <span>|</span> <span>'</span><span>filestream_path</span><span>'</span><span> } 
    </span><span>[</span><span> , SIZE = size [ KB | MB | GB | TB </span><span>]</span><span> ] 
    </span><span>[</span><span> , MAXSIZE = { max_size [ KB | MB | GB | TB </span><span>]</span> <span>|</span><span> UNLIMITED } ] 
    </span><span>[</span><span> , FILEGROWTH = growth_increment [ KB | MB | GB | TB | % </span><span>]</span><span> ]
)
}</span></span></span>

　　ON：用来定义数据库的数据文件。PRIMARY指出其后所定义的文件是主数据文件，如果省略，则第一个定义的文件是主数据文件。

　　LOG ON：用来定义数据库的日志文件。如果没有LOG ON，SQL Server将自动创建一个日志文件。

　　数据库中的文件类型与推荐扩展名：主要数据文件.mdf ，次要数据文件.ndf ，事务日志.ldf 。

　　创建未指定文件的数据库：

<span>--</span><span> Drop the database if it already exists</span>
<span>IF</span>  <span>EXISTS</span><span> (
    </span><span>SELECT</span><span> name 
        </span><span>FROM</span><span> sys.databases 
        </span><span>WHERE</span> name <span>=</span> N<span>'</span><span>Portal</span><span>'</span><span>
)
</span><span>DROP</span> <span>DATABASE</span><span> Portal
</span><span>GO</span>

<span>CREATE</span> <span>DATABASE</span><span> Portal
</span><span>GO</span>

　　创建指定数据文件和事务日志文件的数据库：

<span>CREATE</span> <span>DATABASE</span> <span>[</span><span>Portal</span><span>]</span>
 <span>ON</span>  <span>PRIMARY</span><span> 
(
    NAME </span><span>=</span> N<span>'</span><span>Portal</span><span>'</span><span>,
    FILENAME </span><span>=</span> N<span>'</span><span>F:\Database\Portal.mdf</span><span>'</span><span> ,
    SIZE </span><span>=</span><span> 5MB ,
    FILEGROWTH </span><span>=</span><span> 1MB
 )
 </span><span>LOG</span> <span>ON</span><span>
(
    NAME </span><span>=</span> N<span>'</span><span>Portal_log</span><span>'</span><span>,
    FILENAME </span><span>=</span> N<span>'</span><span>F:\Database\Portal_log.ldf</span><span>'</span><span> ,
    SIZE </span><span>=</span><span> 2MB ,
    FILEGROWTH </span><span>=</span> <span>10</span><span>%</span><span>
)</span>

　　创建数据库指定多个数据及事务日志文件：

<span>CREATE</span> <span>DATABASE</span> <span>[</span><span>Portal</span><span>]</span>
 <span>ON</span>  <span>PRIMARY</span><span> 
(
    NAME </span><span>=</span> N<span>'</span><span>Portal</span><span>'</span><span>,
    FILENAME </span><span>=</span> N<span>'</span><span>F:\Database\Portal.mdf</span><span>'</span><span> ,
    SIZE </span><span>=</span><span> 5MB ,
    FILEGROWTH </span><span>=</span><span> 1MB
 ),
 (
    NAME </span><span>=</span> N<span>'</span><span>Portal_Data_2014</span><span>'</span><span>,
    FILENAME </span><span>=</span> N<span>'</span><span>F:\Database\Portal_Data_2014.ndf</span><span>'</span><span> ,
    SIZE </span><span>=</span><span> 5MB ,
    FILEGROWTH </span><span>=</span><span> 1MB
 )
 </span><span>LOG</span> <span>ON</span><span>
(
    NAME </span><span>=</span> N<span>'</span><span>Portal_log</span><span>'</span><span>,
    FILENAME </span><span>=</span> N<span>'</span><span>F:\Database\Portal_log.ldf</span><span>'</span><span> ,
    SIZE </span><span>=</span><span> 2MB ,
    FILEGROWTH </span><span>=</span> <span>10</span><span>%</span><span>
),
(
    NAME </span><span>=</span> N<span>'</span><span>Portal_log_2014</span><span>'</span><span>,
    FILENAME </span><span>=</span> N<span>'</span><span>F:\Database\Portal_log_2014.ldf</span><span>'</span><span> ,
    SIZE </span><span>=</span><span> 2MB ,
    FILEGROWTH </span><span>=</span> <span>10</span><span>%</span><span>
)</span>

　　创建具有文件组的数据库：

<span>CREATE</span> <span>DATABASE</span> <span>[</span><span>Portal</span><span>]</span>
 <span>ON</span>  <span>PRIMARY</span><span> 
(
    NAME </span><span>=</span> N<span>'</span><span>Portal</span><span>'</span><span>,
    FILENAME </span><span>=</span> N<span>'</span><span>F:\Database\Portal.mdf</span><span>'</span><span> ,
    SIZE </span><span>=</span><span> 10MB ,
    FILEGROWTH </span><span>=</span><span> 1MB
 ),
 FILEGROUP </span><span>[</span><span>div2014</span><span>]</span><span> 
 (
    NAME </span><span>=</span> N<span>'</span><span>Portal_Data_2014</span><span>'</span><span>,
    FILENAME </span><span>=</span> N<span>'</span><span>F:\Database\Portal_Data_2014.ndf</span><span>'</span><span> ,
    SIZE </span><span>=</span><span> 5MB ,
    FILEGROWTH </span><span>=</span><span> 1MB
 )
 </span><span>LOG</span> <span>ON</span><span>
(
    NAME </span><span>=</span> N<span>'</span><span>Portal_log</span><span>'</span><span>,
    FILENAME </span><span>=</span> N<span>'</span><span>F:\Database\Portal_log.ldf</span><span>'</span><span> ,
    SIZE </span><span>=</span><span> 2MB ,
    FILEGROWTH </span><span>=</span> <span>10</span><span>%</span><span>
)</span>

2. 修改数据库

　　修改数据库语法：

<span>ALTER</span> <span>DATABASE</span><span> database_name 
{
    </span><span>add_or_modify_files<span>></span>
  <span>|</span> <span>add_or_modify_filegroups<span>></span><span>
}
</span><span>[</span><span>;</span><span>]</span>

<span>add_or_modify_files<span>></span>::<span>=</span><span>
{
    </span><span>ADD</span> <span>FILE</span> <span>filespec<span>></span> <span>[</span><span> ,...n </span><span>]</span> 
        <span>[</span><span> TO FILEGROUP { filegroup_name } </span><span>]</span>
  <span>|</span> <span>ADD</span> <span>LOG</span> <span>FILE</span> <span>filespec<span>></span> <span>[</span><span> ,...n </span><span>]</span> 
  <span>|</span> REMOVE <span>FILE</span><span> logical_file_name 
  </span><span>|</span> MODIFY <span>FILE</span> <span>filespec<span>></span><span>
}

</span><span>filespec<span>></span>::<span>=</span><span>  
(
    NAME </span><span>=</span><span> logical_file_name  
    </span><span>[</span><span> , NEWNAME = new_logical_name </span><span>]</span> 
    <span>[</span><span> , FILENAME = {'os_file_name' | 'filestream_path' | 'memory_optimized_data_path' } </span><span>]</span> 
    <span>[</span><span> , SIZE = size [ KB | MB | GB | TB </span><span>]</span><span> ] 
    </span><span>[</span><span> , MAXSIZE = { max_size [ KB | MB | GB | TB </span><span>]</span> <span>|</span><span> UNLIMITED } ] 
    </span><span>[</span><span> , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % </span><span>]</span><span> ] 
    </span><span>[</span><span> , OFFLINE </span><span>]</span><span>
)  

</span><span>add_or_modify_filegroups<span>></span>::<span>=</span><span>
{
    </span><span>|</span> <span>ADD</span> FILEGROUP <span>filegroup_name</span> 
        <span>[</span><span> CONTAINS FILESTREAM | CONTAINS MEMORY_OPTIMIZED_DATA </span><span>]</span>
    <span>|</span> REMOVE FILEGROUP <span>filegroup_name</span> 
    <span>|</span> MODIFY FILEGROUP <span>filegroup_name</span><span>
        { </span><span>filegroup_updatability_option<span>></span>
        <span>|</span> <span>DEFAULT</span>
        <span>|</span> NAME <span>=</span><span> new_filegroup_name 
        }
}
</span><span>filegroup_updatability_option<span>></span>::<span>=</span><span>
{
    { READONLY </span><span>|</span><span> READWRITE } 
    </span><span>|</span> { READ_ONLY <span>|</span><span> READ_WRITE }
}</span></span></span></span></span></span></span></span></span></span></span>

　　新增文件组：

<span>ALTER</span> <span>DATABASE</span> <span>[</span><span>Portal</span><span>]</span> <span>ADD</span> FILEGROUP <span>[</span><span>div2014</span><span>]</span>

　　新增文件指定文件组：

<span>ALTER</span> <span>DATABASE</span> <span>[</span><span>Portal</span><span>]</span> <span>ADD</span> <span>FILE</span><span>
(
    NAME </span><span>=</span> N<span>'</span><span>Portal_Data_2014</span><span>'</span><span>,
    FILENAME </span><span>=</span> N<span>'</span><span>F:\Database\Portal_Data_2014.ndf</span><span>'</span><span> ,
    SIZE </span><span>=</span><span> 5MB ,
    FILEGROWTH </span><span>=</span><span> 1MB
)
</span><span>TO</span> FILEGROUP <span>[</span><span>div2014</span><span>]</span>

　　删除数据库文件：

<span>ALTER</span> <span>DATABASE</span> <span>[</span><span>Portal</span><span>]</span> REMOVE <span>FILE</span> Portal_Data_2014

　　修改数据名称:

<span>ALTER</span> <span>DATABASE</span> <span>[</span><span>Portal</span><span>]</span> MODIFY NAME <span>=</span> <span>[</span><span>Portal_2014</span><span>]</span>

<span>EXEC</span> sp_renamedb <span>[</span><span>Portal</span><span>]</span>, <span>[</span><span>Portal_2014</span><span>]</span>

　　修改设置默认文件组：

<span>ALTER</span> <span>DATABASE</span> <span>[</span><span>Portal</span><span>]</span> MODIFY FILEGROUP <span>[</span><span>PRIMARY</span><span>]</span> <span>DEFAULT</span>

3. 删除数据库

　　删除数据库语法：

<span>DROP</span> <span>DATABASE</span> { database_name <span>|</span> database_snapshot_name } <span>[</span><span> ,...n </span><span>]</span> <span>[</span><span>;</span><span>]</span>

　　示例：

<span>DROP</span> <span>DATABASE</span> <span>[</span><span>Portal</span><span>]</span>

4. 分离数据库

　　使用系统存储过程sp_detach_db分离数据库。

sp_detach_db <span>[</span><span> @dbname= </span><span>]</span> <span>'</span><span>database_name</span><span>'</span> 
    <span>[</span><span> , [ @skipchecks= </span><span>]</span> <span>'</span><span>skipchecks</span><span>'</span><span> ] 
    </span><span>[</span><span> , [ @keepfulltextindexfile = </span><span>]</span> <span>'</span><span>KeepFulltextIndexFile</span><span>'</span> ] 

<span>EXEC</span> sp_detach_db <span>[</span><span>Portal</span><span>]</span>

　　直接运行分离数据库的SQL语句，可能会提示有进程（用户）正在使用，分离失败。要解决这个问题，先查看哪些进程（用户）正在使用该数据库。

　　查看用户和进程：

<span>USE</span> <span>[</span><span>master</span><span>]</span><span>
sp_who</span>

　　先结束占用数据库的进程，再分离数据库：

USE [master]

KILL 55
KILL 56
KILL 57

<span>EXEC</span> sp_detach_db <span>[</span><span>Portal</span><span>]</span>

5. 附加数据库

　　使用CREATE DATABASE附加数据库：

<span>CREATE</span> <span>DATABASE</span> <span>[</span><span>Portal</span><span>]</span>
<span>ON</span><span>
(
    FILENAME </span><span>=</span> <span>'</span><span>F:\Database\Portal.mdf</span><span>'</span><span>
)
</span><span>FOR</span> ATTACH

<span>CREATE</span> <span>DATABASE</span> <span>[</span><span>Portal</span><span>]</span>
<span>ON</span><span>
(
    FILENAME </span><span>=</span> <span>'</span><span>F:\Database\Portal.mdf</span><span>'</span><span>
),
(
    FILENAME </span><span>=</span> <span>'</span><span>F:\Database\Portal_log.ldf</span><span>'</span><span>
)
</span><span>FOR</span> ATTACH

　　使用系统存储过程附加数据库：

<span>EXEC</span> sp_attach_db <span>[</span><span>Portal</span><span>]</span>, <span>'</span><span>F:\Database\Portal.mdf</span><span>'</span>

<span>EXEC</span> sp_attach_db <span>[</span><span>Portal</span><span>]</span>, <span>'</span><span>F:\Database\Portal.mdf</span><span>'</span>, 'F:\Database\Portal_log.ldf'

6. 查看数据库信息

　　SQL Server中可以使用多种方式查看数据库信息，例如使用目录视图、函数、存储过程等。

6.1> 使用目录视图

　　使用目录视图查看数据库基本信息：

　　◊ sys.databse_files：查看数据库文件信息；

　　◊ sys.filegroups：查看数据库组信息；

　　◊ sys.master_files：查看数据库文件的基本信息和状态信息；

　　◊ sys.database：数据库和文件目录视图查看数据库的基本信息。

<span>SELECT</span> <span>*</span> <span>FROM</span> sys.databases <span>WHERE</span> name <span>=</span> <span>'</span><span>Northwind</span><span>'</span>