第2章数据库的创建

2.1数据库及其数据库对象

1.数据库库实例

在一台计算机上可以安装一个或多个SQL Server（不同版本或同一版本），其中的每一个称为一个数据库实例。

2.数据库对象

1. 表：表是主要的数据库对象，由行和列组成，也称二维表。表是存放数据及表示关系的主要形式。
2. 视图：视图是从一个或多个基本表中引用表。由于视图本身并不存储实际数据，因此也可以称为虚表。当基本表中的数据发生变化时，从视图中查询出来的数据也随之改变。
3. 索引：表中的记录通常按其输入的时间顺序存放，这种顺序称为记录的物理顺序。为了实现对表记录的快速查询，可以对表的记录按某个或某些字段或他们的组合（称为索引表达式）进行顺序，这种顺序称为逻辑顺序。通过逻辑顺序搜索索引表达式的值，可以实现对该类数据记录的快速访问。
4. 约束：约束用于保障数据的一致性与完整性。具有代表性的约束就是主键和外键。主键约束当前表记录的主键字段值的唯一性，外键约束当前表记录与其他表的关系。
5. 存储过程：存储过程是一组为了完成特定功能的SQL语句集合。这个语句集合经过编译后存储在数据库中，存储过程具有接收参数、输出参数、返回单个或多个值的功能。存储过程独立于表的存在。
6. 触发器：触发器与表紧密关联。它可以实现更加复杂的数据操作，更加有效的保障数据库系统中数据的完整性和一致性。触发器基于一个表创建，但可以对多个表进行操作。
7. 默认值：默认值是在用户没有给出具体数据时，系统所自动生成的数值。
8. 用户和角色：用户是指对数据库有存取权限的使用者；角色是指一组数据库用户的集合。
9. 规则：规则用来限制表字段的数据范围。
10. 类型：用户可以根据需要在给定的系统类型之上定义自己的数据类型。
11. 函数：用户可以根据需要将系统若干个语句或者系统函数进行组合实现特定功能，定义成自己的函数。然后，在需要该功能处调用该函数。

3.数据库中的架构

架构的作用是将数据库中的所有对象分成不同的集合，每一个集合就称为一个架构。数据库中的每一个用户都会有自己的默认架构。这个默认架构可以在创建数据库用户时由创建者设定，若不设定，则系统默认架构为dbo。数据库用户只能对属于自己架构中的数据库对象执行相应的数据操作，至于操作权限则由数据库角色决定。

4.SQL Server数据库

1. Master数据库：它记录SQL Server系统的所有系统级信息。包括实例范围的元数据（如登陆账户）、端点、链接服务器和系统配置设置。此外，master数据库还记录了所有其他数据库的存在、数据库文件的位置及SQL Server的初始化信息。因此，如果master数据库不可用，则SQL Server无法启动。
2. Model数据库：它用作在SQL Server实例上创建的所有数据库的模板。当发出CREATEDATABASE语句时，将通过复制model数据库中的内容来创建数据库的第一部分，然后用空页填充新数据库的剩余部分。如果修改model数据库，之后创建的所有数据库都将继承这些修改。如果用特定于用户的模板信息修改model数据库，建议备份model。
3. Tempdb数据库：tempdb系统数据库是一个全局资源，可供连接到SQL Server实例的所有用户使用。Tempdb中的操作是最小日志记录操作，这将使事务产生回滚。每次启动SQL Server时都会重新创建tempdb，从而在系统启动时总是保持一个干净的数据库副本。在断开连接时会自动删除临时表和存储过程，并且在系统关闭后没有活动连接。因此tempdb中不会有内容从一个SQL Servere会话保存到另一个会话。不允许对tempdb进行备份和还原操作。
4. Msdb数据库：SQL Server代理使用msdb数据库来计划警报和作业，SQL Server Management Studio、Server Broker和数据库邮件等其他功能也使用该数据库。在进行任何更新msdb的操作（如备份和还原任何数据库）后，建议备份msdb。
5. Resource数据库：为只读数据库，包含了SQL Server中所有系统对象。SQL Server系统对象在物理上保留在resource数据库中，但在逻辑上显示在每个数据库的sys架构中。Resource数据库中不包含用户数据或用户元数据。

5.文件和文件组

操作系统只管理文件，当然数据库及其数据库对象仍然存放在文件中，按照文件组方式组织。文件组是一个逻辑名，可以包含若干物理文件。

从系统管理的需求出发，采用多个数据文件来存储数据，可以避免数据文件过大。同时，数据文件存放在不同的硬盘上，可以提高处理速度。

6.FILESTREAM

借助FILESTREAM，基于SQL Server的应用程序可以将非结构化的数据（如文档和图像）存储在文件系统中。应用程序在利用丰富的流式API和文件系统性能的同时，还可保持非结构化数据和对应的结构化数据之间的事务一致性。

 

2.2创建数据库

2.2.1数据库的创建

首先要明确，能够创建数据库的用户必须是系统管理员，或者被授权使用CREAT DATABASE语句的用户

数据库中存放数据的记录可能会越来越多。在SQL Server中，数据文件和日志文件可以指定初始大小、增长方式和最大容量。当数据库内容初始大小无法存放时，就会按照增长方式增加文件大小，但不能超过最大容量。

创建数据库必须确定数据库名、所有者（即创建数据库的用户）、数据库大小（最初的大小、最大的大小、是否允许增长及增长方式）和存储数据库的文件。

对于新创建的数据库，系统对数据文件的默认值为：初始文件大小为5MB；最大容量不受限制（仅受硬盘空间的限制）；允许数据库的自动增长，增量为1MB。

对日志文件的默认值为：文件初始大小为1MB；最大容量不受限制（仅受硬盘空间的限制）；允许日志文件自动增长，增长方式为按10%的比例增长。

2.2.2数据库的修改和删除

1.数据库的修改

数据库成功创建后，数据文件名和日志文件名就不能改变。对已存在的数据库可以进行的修改包括以下几项：

1. 增加或删除数据文件
2. 改变数据文件的大小和增长方式
3. 改变日志文件的大小和增长方式
4. 增加或删除日志文件
5. 增加或删除文件组
6. 重命名数据库

2.2.3通过命令方式创建数据库

1.CREATE DATABASE 数据库名

[ON

[PRIMARY]

[<数据文件选项>...

[,<数据文件组选项>...]

[LOG ON{<日志文件选项>...}]]

[COLLATE 排序名]

...

]

 

2.数据文件的语法格式：

<数据文件选项> =

{(

NAME =逻辑文件名,

FILENAME ={‘操作系统文件名’|'存储路径'}

[,SIZE =文件初始容量]

[,MAXSIZE ={文件最大容量|UNLIMITED}]

[,FILEGROWTH =文件增量[容量|%]]

)

}

说明：

a.逻辑文件名：数据库使用的名称；

b.操作系统文件名：操作系统在创建物理文件时使用的路径和文件名。

c.文件初始容量：对于主文件，若不指出大小，则默认为model数据库主文件的大小，对于辅助数据文件，自动设置为3MB；

d.文件最大容量：指定文件的最大大小。UNLIMITED关键字表示文件大小不受限制，但实际上受磁盘可用空间的限制。如果不指定MAXSIZE选项，则文件增长到磁盘空间满；

e.文件增量：有百分比和容量值两种格式，前者如10%，即每次在原来空间大小的基础上增长10%；后者如5MB,即每次增长5MB，而不管原来空间大小是多少。

 

3.数据文件组选项的语法格式：

<数据文件组选项>=

{

FILEGROUP 文件组名[DEFAULT]

<文件选项>...

}

a.DEFAULT关键词：指定命名文件组为数据库中的默认文件组。

b.<文件选项>：用于指定属于该文件组的文件的属性，其格式描述和数据文件的属性描述相同。

 

示例：

CREATE DATABASE test1

ON

(

NAME = "test1_data",

FILENAME = "D:\SQLServer\DATA\test1.mdf",

SIZE = 5MB,

MAXSIZE = 50MB,

FILEGROWTH = 10%

)

LOG ON

(

NAME = "test1_log",

FILENAME = "D:\SQLServer\DATA\test1.ldf",

SIZE = 2MB,

MAXSIZE = 5MB,

FILEGROWTH = 1MB

);

 

 

4.修改数据库 ：ALTER DATABASE

ALTER DATABASE 数据库名

{

ADD FILE<文件选项>...[TO FILEGROUP 文件组名]    /*在文件组中增加数据文件*/

|ADD LOG FILE<文件选项>...                    /*增加日志文件*/

|REMOVE FILE 逻辑文件名                       /*删除数据文件*/

|ADD FILEGROUP 文件组名 [...]                 /*增加文件组*/                      

|REMOVE FILEGROUP 文件组名                    /*删除文件组*/                       

|MODIFY FILE<文件选项>                        /*更改文件属性*/                                        

|MODIFY NAME = 新数据库名                     /*数据库更名*/                           

|MODIFY FILEGROUP 文件组名                                            

{ <文件组可更新选项>

|DEFAULT

|NAME = 新文件组名

}                                             /*更改文件组属性*/

|SET<属性选项>...[WITH<终止>]                 /*设置数据库属性*/

|COLLATE 排序名                               /*指定数据库排序规则*/

}

说明：

a.ADD FILE子句：向数据库添加数据文件，<文件选项>给出文件的属性，结构参见CREATE DATABASE语法说明。关键字TO FILEGROUP指出了添加的数据文件所在的文件组名，若省略，则为主文件组。

b.ADD LOG FIL子句：向数据库添加日志文件，<文件选项>给出日志文件的属性。

c.REMOVE FILE子句：从数据库中删除数据文件，被删除的数据文件由其中的参数“逻辑文件名”给出。当删除一个数据文件时，逻辑文件与物理文件全部被删除。

d.ADD FILEGROUP子句：向数据库中添加文件组，被添加的文件组名由参数“文件组名”给出。

e.REMOVE FILEGROUP子句：删除文件组，被删除的文件组名由参数“文件组名”给出。

f.MODIFY FILE子句：修改数据文件的属性，被修改文件的逻辑名由<文件选项>的NAME选项给出，可以修改的文件属性包括FILENAME、SIZE、MAXSIZE和FILEGROWTH,但是要注意，一次只能修改其中的一个属性。修改文件大小时，修改后的大小不能小于当前文件的大小。

g.MODIFY NAME子句：更改数据库名，新的数据库名由参数“新数据库名”给出。

h.MODIFY FILEGROUP子句：用于修改文件组的属性。“文件组名”为要修改的文件组名称。<文件组可更新选项>设置文件组的读写权限。DEFAULT选项表示将默认数据库文件组改为指定文件组。NAME选项用于将文件组名改成新文件组名。

i.SET子句：用于设置数据库的属性，<属性选项>中指定了要修改的属性

 

<文件组可更新选项>::=

{

 {READONLY|READWRITE}

|{READ_ONLY|READ_WRITE}

}

j.READONLY和READ_ONLY选项：用于将文件组设为只读。

k.READWRITE 和READ_WRITE选项：将文件组设为读/写模式。

 

示例：

增：

ALTER DATABASE test1

ADD FILE

(

NAME = "test1bak",

FILENAME = "D:\SQLServer\DATA\test1bak.ndf",

SIZE = 10MB,

MAXSIZE = 50MB,            

FILEGROWTH = 5%          

)

删：

ALTER DATABASE test1

REMOVE FILE test1bak

GO

 

改：

ALTER DATABASE test1

MODIFY FILE

(

NAME = "test1_data",

MAXSIZE = 100MB,            /*将主数据文件的最大容量改为100MB*/

FILEGROWTH = 5MB            /*将主数据文件的增长方式改为按5MB增长*/

)

GO

添加文件数组：为数据库test1添加文件组fgroup，并为此文件组添加两个大小均为10MB的数据文件。

ALTER DATABASE test1

ADD FILEGROUP fgroup

GO

ALTER DATABASE test1

ADD FILE

(

NAME = "test1_data2",

FILENAME = "D:\SQLServer\DATA\test1data2.ndf",

SIZE = 10MB         

),

(

NAME = "test1_data3",

FILENAME = "D:\SQLServer\DATA\test1data3.ndf",

SIZE = 10MB         

)

TO FILEGROUP fgroup

GO

 

5.删除数据库 DROP DATABASE

DROP DATABASE 数据库名...

GO

 

6.数据库快照 AS SNAPSHOT

CREATE DATABASE 数据库快照名

ON(

NAME =逻辑文件名，

FILENAME ='操作系统文件名'

)[,...]

AS SNAPSHOT OF 源数据库名

a.数据库快照名：数据库快照的名称，这个名称在SQL Server实例中必须唯一且符合标识符规则。

b.ON子句：若要创建数据库快照，要在源数据库中指定文件列表。若要使快照工作，则必须分别指定所有数据文件。其中，NAME是逻辑文件名，FILENAME是操作系统文件名（包含路径）。日志文件不允许用于数据库快照。

c.AS SNAPSHOT OF子句：指定要创建的快照为“源数据库名”指定的数据库的快照。数据库快照必须与源数据处于同一实例中。

示例:

CREATE DATABASE test1_s1

ON

(

NAME=test1,

FILENAME='D:\SQLServer\DATA\test1_s1.mdf'

)

AS SNAPSHOT OF test1

GO

 

SQL Server 数据类型

Character 字符串：

数据类型	描述	存储
char(n)	固定长度的字符串。最多 8,000 个字符。	n
varchar(n)	可变长度的字符串。最多 8,000 个字符。
varchar(max)	可变长度的字符串。最多 1,073,741,824 个字符。
text	可变长度的字符串。最多 2GB 字符数据。

Unicode 字符串：

数据类型	描述	存储
nchar(n)	固定长度的 Unicode 数据。最多 4,000 个字符。
nvarchar(n)	可变长度的 Unicode 数据。最多 4,000 个字符。
nvarchar(max)	可变长度的 Unicode 数据。最多 536,870,912 个字符。
ntext	可变长度的 Unicode 数据。最多 2GB 字符数据。

Binary 类型：

数据类型	描述		存储
bit	允许 0、1 或 NULL
binary(n)	固定长度的二进制数据。最多 8,000 字节。
varbinary(n)		可变长度的二进制数据。最多 8,000 字节。
varbinary(max)		可变长度的二进制数据。最多 2GB 字节。
image		可变长度的二进制数据。最多 2GB。

Number 类型：

数据类型	描述	存储
tinyint	允许从 0 到 255 的所有数字。	1 字节
smallint	允许从 -32,768 到 32,767 的所有数字。	2 字节
int	允许从 -2,147,483,648 到 2,147,483,647 的所有数字。	4 字节
bigint	允许介于 -9,223,372,036,854,775,808 和 9,223,372,036,854,775,807 之间的所有数字。	8 字节
decimal(p,s)	固定精度和比例的数字。允许从 -10^38 +1 到 10^38 -1 之间的数字。 p 参数指示可以存储的最大位数（小数点左侧和右侧）。p 必须是 1 到 38 之间的值。默认是 18。 s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。	5-17 字节
numeric(p,s)	固定精度和比例的数字。允许从 -10^38 +1 到 10^38 -1 之间的数字。 p 参数指示可以存储的最大位数（小数点左侧和右侧）。p 必须是 1 到 38 之间的值。默认是 18。 s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。	5-17 字节
smallmoney	介于 -214,748.3648 和 214,748.3647 之间的货币数据。	4 字节
money	介于 -922,337,203,685,477.5808 和 922,337,203,685,477.5807 之间的货币数据。	8 字节
float(n)	从 -1.79E + 308 到 1.79E + 308 的浮动精度数字数据。 参数 n 指示该字段保存 4 字节还是 8 字节。float(24) 保存 4 字节，而 float(53) 保存 8 字节。n 的默认值是 53。	4 或 8 字节
real	从 -3.40E + 38 到 3.40E + 38 的浮动精度数字数据。	4 字节

Date 类型：

数据类型	描述	存储
datetime	从 1753 年 1 月 1 日 到 9999 年 12 月 31 日，精度为 3.33 毫秒。	8 bytes
datetime2	从 1753 年 1 月 1 日 到 9999 年 12 月 31 日，精度为 100 纳秒。	6-8 bytes
smalldatetime	从 1900 年 1 月 1 日 到 2079 年 6 月 6 日，精度为 1 分钟。	4 bytes
date	仅存储日期。从 0001 年 1 月 1 日 到 9999 年 12 月 31 日。	3 bytes
time	仅存储时间。精度为 100 纳秒。	3-5 bytes
datetimeoffset	与 datetime2 相同，外加时区偏移。	8-10 bytes
timestamp	存储唯一的数字，每当创建或修改某行时，该数字会更新。timestamp 基于内部时钟，不对应真实时间。每个表只能有一个 timestamp 变量。

其他数据类型：

数据类型	描述
sql_variant	存储最多 8,000 字节不同数据类型的数据，除了 text、ntext 以及 timestamp。
uniqueidentifier	存储全局标识符 (GUID)。
xml	存储 XML 格式化数据。最多 2GB。
cursor	存储对用于数据库操作的指针的引用。
table	存储结果集，供稍后处理。