系统数据库

SQL Server有两种数据库，系统数据库和用户数据库，系统自动创建5个系统数据库，master，model，msdb，resource和tempdb。其中master，model，msdb，tempdb能在SSMS里面看到。resource是隐藏的。
image.png

master

master数据库记录了SQL Server系统的所有系统级别的信息，登录账户，连接服务器，系统配置信息，初始化信息，错误信息等。master损坏，SQL Server无法启动。

model

是创建用户数据库的模板，当用户创建了一个数据库时，model数据库的内容会自动复制到用户数据库中。

msdb

用于存储报警作业以及操作员的信息

tempdb

tempdb为临时表，临时存储过程提供空间，是负担最重的数据库，几乎所有查询都可能用到它，tempdb数据库中的所有对象，在SQL Server关闭时都将被释放。下次启动SQL Server时又将会被重新创建。

resource

resource包含SQL Server中所有系统对象，该数据具有只读特性，该数据库物理文件名为mssqlsystemresource.mdf，不许移动或者重命名，否则SQL Server不能启动。

数据库结构

数据库文件

主要数据文件.mdf

后缀名为.mdf，用户数据和对象可以存储在此文件中，也可以存储在次要数据库文件中。每个数据库必定有一个主要数据文件。

次要数据文件.ndf

次数据库文件是可选的，用于定义并存储用户数据，后缀为.ndf。可以有多个次要文件，比如限定了主数据库最大2GB大小，主数据满了，无法增加数据了，可以加次数据库，数据库就能继续增长了，还可以放在不同的磁盘里面。

如果数据量过大，用主要数据文件和次要数据文件放在同一文件组中，能提高效率。

事务日志文件.ldf

后缀为.ldf，记录所有事务，以及每个事务对数据库的修改。数据库被破坏时，可以通过事务日志 文件恢复数据库，每个数据库必须至少拥有一个事务日志文件，并允许拥有多个事务日志文件。

文件组

主要文件组

包含主要数据文件和未放入其他文件组的次要数据文件，每个数据库有一个主要文件组。
随便找个数据库，右键属性，就能看到它的文件组了
image.png

用户定义文件组

用户自定义的文件组，用于数据文件的集合，管理，分配和放置。
如果在数据库中，创建对象时，没有指定对象所属的文件组，对象江北分配给默认文件组。只能有一个默认文件组。PRIMARY是默认文件组，可以自己改。但系统对象和表仍然分配给PRIMARY文件组，而不是新的默认文件组。事物日志不能属于任何文件组，一个数据文件只能属于一个文件组。

创建数据库

<>必填，[]选填

默认创建

  1. CREATE DATABASE <database_name>

语法

database_name：数据库名字，必填，唯一 ON：用户定义数据库的数据文件，从on开始都是选填，加上on关键字 PRIMARY：用于定义其后指定的数据文件为主要数据文件，如果省略的话，系统将第一个定义的文件作为主要数据文件。 LOG ON：日志文件的定义

  1. CREATE DATABASE <database_name> 
  2. [
  3.   ON [PRIMARY]
  4.   [
  5.     <filespec>
  6.     [,...n]
  7.     [,<filegroup>[,...n]]
  8.   ]
  9.     [LOG ON <filespec>[,...n]]
  10. ]

filespec格式如下

每个filespec文件都用小括号括起来。 NAME：数据文件或者日志文件所使用的逻辑名称。 FILENAME：这个要用单引号，指定这个文件的物理路径 后面的都是选填 SIZE：指定文件的初始容量，不带单位就是mb MAXSIZE：指定文件的最大容量，默认单位mb，如果省略maxsize，或者指定为unlimited。则文件容量可以不断的增加，直到磁盘满。 FILEGROWTH：指定文件每次的增量，可以为百分比。

  1. (
  2.     NAME = <logical_file_name>,
  3.   FILENAME = <'os_file_name'>,
  4.   [,SIZE = <size>]
  5.   [,MAXSIZE = <max_size|unlimited>]
  6.   [,FILEGROWTH = <growth_increment>[KB|MB|GB|TB|%]]
  7. ),[,...n]

filegroup格式如下

FILEGROUP：用于指定用户自定义的文件组 filegroup_name：文件组名 DEFAULT：指定文件组为默认文件组

  1. FILEGROUP <filegroup_name> [DEFAULT] <filespec>[,...n]

例子

  1. CREATE DATABASE DBName ON PRIMARY
  2. (
  3. NAME=DBName,                  
  4. FILENAME='D:\ADB\DBName.mdf', 
  5. SIZE=10MB,
  6. MAXSIZE=1GB,
  7. FILEGROWTH=1MB
  8. )
  9. LOG ON
  10. (
  11. NAME=DBName_log,
  12. FILENAME='D:\ADB\DBName_log.ldf',
  13. SIZE=8MB,
  14. MAXSIZE=UNLIMITED,
  15. FILEGROWTH=10%
  16. )

执行完成后，可以在对应的目录下看到
image.png

例子多个文件

primary位置后面的一个文件设为主要数据文件，物理路径后缀得为.mdf

  1. CREATE DATABASE DBName ON 
  2. (
  3. NAME=DBName2,                  
  4. FILENAME='D:\ADB\DBName2.ndf', 
  5. SIZE=5MB,
  6. MAXSIZE=1GB,
  7. FILEGROWTH=1MB
  8. ),
  9. PRIMARY
  10. (
  11. NAME=DBName1,                  
  12. FILENAME='D:\ADB\DBName1.mdf', 
  13. SIZE=10MB,
  14. MAXSIZE=1024,
  15. FILEGROWTH=1%
  16. )
  17. LOG ON
  18. (
  19. NAME=DBName1_log,
  20. FILENAME='D:\ADB\DBName1_log.ldf',
  21. SIZE=8MB,
  22. MAXSIZE=UNLIMITED,
  23. FILEGROWTH=10%
  24. ),
  25. (
  26. NAME=DBName2_log,
  27. FILENAME='D:\ADB\DBName2_log.ldf',
  28. SIZE=8MB,
  29. MAXSIZE=1GB,
  30. FILEGROWTH=5MB
  31. )

创建完成后，目录
image.png
SSMS里面看数据库名字
image.png
右键数据库可以查看属性
image.png

查看数据库信息

  1. sp_helpdb <database_name>

image.png
不指定数据库名，就查询当前服务器上所有的数据库
image.png

修改数据库

数据库创建完成后，使用过程中需要修改可以使用alter database

语法

  1. ALTER DATABASE <database_name>                                                                  --修改指定的数据库
  2. ADD FILE <filespec>[,...n] [TO FILEGROUP <filegroup_name>]                                      --添加数据文件到数据库
  3. | ADD LOG FILE <filespec>[,...n]                                                                --添加事务日志到数据库
  4. | REMOVE FILE <logical_file_name>                                                               --删除文件，文件必须为空
  5. | ADD FILEGROUP <filegroup_name>                                                                --添加文件组
  6. | REMOVE FILEGROUP <filegroup_name>                                                             --删除文件组，文件组必须为空
  7. | MODIFY FILE <filespec>                                                                        --修改文件属性，一次只能修改一个
  8. | MODIFY NAME = <new_dbname>                                                                    --数据库改名
  9. | MODIFY FILEGROUP <filegroup_name> <filegroup_option> | DEFAULT | NAME = <new_filegroup_name>  --修改文件组                               --

filespec格式如下

  1. (
  2.     NAME = <logical_name>                                  --改之前的逻辑名
  3.   [,NEWNAME = <new_logical_name>]                        --新的逻辑名
  4.   [,FILENAME = <'os_file_name'>]                         --新的文件路径
  5.   [,SIZE = <size>]                                       --新的初始大小
  6.   [,MAXSIZE = <max_size>|UNLIMITED ]                     --新的最大容量
  7.   [,FILEGROWTH = <growth_increment> [KB|MB|GB|TB|%]]     --新的增量
  8. )

filegroup_option格式如下

四选一，只读，读写，两个版本都可以

  1. READONLY|
  2. READWRITE|
  3. READ_ONLY|
  4. READ_WRITE

例子

给DBName数据库添加一个次要数据文件，再修改原本的DBName2_log日志的属性。 注意：两个操作不能再一个ALTER语句中完成

  1. ALTER DATABASE DBName
  2. ADD FILE 
  3. (
  4. NAME = DBName3,
  5. FILENAME = 'D:\ADB\DBName3.ndf'
  6. )
  7. GO
  8. ALTER DATABASE DBName
  9. MODIFY FILE
  10. (
  11. NAME = DBName2_log,
  12. SIZE = 20MB
  13. )
  14. GO

重命名数据库

①sp_renamedb

  1. sp_renamedb <old_database_name>,<new_database_name>

②ALTER DATABASE

  1. ALTER DATABASE <old_database_name>
  2. MODIFY NAME = <new_database_name>

使用数据库

  1. USE <database_name>

分离数据库

把数据集分离了，就可以使.mdf，.ndf，.ldf文件与SQL Server脱离关系，将数据库从SQL Server实例中删除，无法再当前服务器上使用数据库，数据文件仍然在磁盘上。分离了就可以进行拷贝文件到其他地方了。

①SSMS分离

image.png

②sp_detach_db

语法

  1. sp_detach_db [@dbname =]'dbname'                          --指定要分离的数据库名字
  2. [, [@skipchecks=] 'shipchecks']                            --跳过还是运行UPDATA STATISTICS，shipchecks数据类型nvarchar(10),默认为NULL。要跳过指定为true，显式运行，指定为false
  3. [, [@KeepFulltextIndexFile=] 'KeepFulltextIndexFile']      --指定在数据库分离操作过程中是否删除与正在被分离的数据库关联的全文索引文件。数据类型为nvarchar(10),默认值true，如果为NULL或者false，则会删除

例子

  1. sp_detach_db @dbname = 'newDBName',@Skipchecks='true',@KeepFulltextIndexFile='false'
  2. --或者
  3. sp_detach_db 'newDBName','true','false'
  4. --或者
  5. sp_detach_db 'newDBName'

SINGLE_USER

在分离数据库时，需要拥有对数据库的独占访问权。如果要分离的数据库正在使用中，则必须将其设置为SINGLE_USER模式，才能进行分离操作

  1. USE master
  2. ALTER DATABASE <database_name>
  3. SET SINGLE_USER

执行完了就变成这样了
image.png

附加数据库

①SSMS附加

image.png
选中主要数据文件，就可以附加了

②CREATE DATABASE

ON：用来显式的指定数据文件，FILENAME一定要指明 FOR ATTACH：通过附加一组现有的数据文件来创建数据库 FOR ATTACH_REBUILD_LOG：通过附加一组洗哪有的数据文件来创建数据库。该选项只限于读写数据库，如果缺少一个或者多个事务日志，将重新生成日志文件，必须指定一个主文件项

  1. CREATE DATABASE <database_name> ON 
  2. <filespec>[,...n]
  3. FOR {ATTACH|ATTACH_REBUILD_LOG}

filespec看上面的创建数据库

例子

  1. CREATE DATABASE newDBName ON 
  2. (FILENAME='D:\ADB\DBName2.ndf'),
  3. (FILENAME='D:\ADB\DBName2_log.ldf')
  4. FOR ATTACH

删除数据库

除了系统数据库外，其他数据库都可以删除。删除是真删除，对应的数据文件和数据都会被删除。

  1. DROP DATABASE <database_name>

收缩数据库

SQL Server数据库中有一些未使用的空间，可以删掉这些未使用的空间，节约存储空间。数据和事务日志文件都可以收缩。

①SSMS收缩

image.png
image.png

①先点✓ ②然后设置百分比，这里的百分比指的是剩余未使用的空间占用整个数据库的多少空间的百分比，剩余占用5%的话，那么已经使用的空间占95%。如果已使用的空间是95MB，那么现在收缩后，未使用的空间只有5MB。 ③二设置的值要比三小才有效果

②DBCC SHRINKDATABASE

DBCC SHRINKDATABASE后面跟数据库名或者数据库id，0的话就是当前数据库。 剩余可占用空间百分比看上买SSMS的方法②。 NOTRUNCATE，只对收缩文件有效 TRUNCATEONLY，只对收缩数据文件有效

  1. DBCC SHRINKDATABASE
  2. (
  3.     {<database_name>|<database_id>|0}
  4.   [,<剩余可占用空间百分比，数字>]
  5.   [,{NOTRUNCATE|TRUNCATEONLY}]
  6. )

例子

  1. DBCC SHRINKDATABASE (LIB45,1)

执行完了

DbId：数据库引擎要收缩的数据库id FileId：数据库引擎要收缩的文件id CurrentSize：文件占用的页数（每页占用8KB空间） MinimumSize：文件最少占用的页数 UsedPages：文件当前使用的页数 EstimatedPages：数据库引擎估算可以收缩到的页数

image.png
并不是收缩得越小越好，要预留一部分空间便于操作。

③DBCC SHRINKFILE

上面是搜索数据库，这里是搜索单独的文件，数据文件或者日志文件

用逻辑文件名，不是物理路径。 EMPTYFILE：指定数据库引擎将当前的所有数据迁移到同一文件组中的其他文件，然后使用ALTER DATABASE语句来删除该文件。

  1. DBCC SHRINKFILE
  2. (
  3.     {<logical_file_name>|<logical_file_id>}
  4.   [,EMPTYFILE]
  5.   [,<收缩后的文件大小，数字，单位MB>]
  6.   [,{NOTRUNCATE|TRUNCATEONLY}]
  7. )

例子

  1. DBCC SHRINKFILE (LIB45,5)

移动数据库

①先将数据库设置为离线状态

  1. ALTER DATABASE <database_name> SET OFFLINE

②修改数据库文件的位置

  1. ALTER DATABASE <database_name> 
  2. MODIFY FILE(
  3. NAME=logical_name,            --逻辑名
  4. FILENAME='D:\ADB\LIB45.mdf'  --修改目标路径
  5. )

③将数据库设置为在线

  1. ALTER DATABASE <database_name> SET ONLINE

可以分离数据库，然后拷贝到指定路径，然后附加，也是一种办法