SQL Server数据库的增删改

一、数据库的创建

（一）数据库的命名规则

1.兼容级别

数据库	数据库版本	兼容级别
SQL Server 2017	14	140
SQL Server 2016	13	130
SQL Server 2014	12	120
SQL Server 2012	11	110
SQL Server 2008	10	100
SQL Server 2005	9	90
SQL Server 2000	8	80

数据库级别可以用ALTER DATABASE语句来修改，其语法格式为：

ALTER DATABASE database_name  SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 | 110 | 120 | 130 | 140 }

2.命名规定

当兼容级别为100以上时，数据库的命名规则为：
（1）名称长度不能超过128个字符，本地临时表的名称不能超过116个字符。
（2）名称的第一个字符必须是英文字母、汉字（或其他语言的字母）、下划线、符号“＠”或“＃”。
（3）除第一个字符之外的其他字符，还可以包括数字和“$”符号。
（4）名称中间不允许有空格或其他特殊字符。
（5）名称不能是数据库中的保留字。
注：由于在T-SQL中，“＠”表示局部变量，“＠＠”表示全局变量，“＃＃”表示全局临时对象，所以不建议用这些符号作为数据库名称的开头。

（二）数据库文件

每个SQL Server 数据库至少包含二个文件：一个数据文件（.mdf）和一个日志文件(.ldf)。

1.数据文件

数据库的数据文件中包含数据库的数据和对象，如表、视图、索引等。数据文件可以分为主要数据文件和次要数据文件两种。

（1） 主要数据文件

每个数据库有且仅有一个主要数据文件，主要数据文件（primary data file）的扩展名为.mdf。主要数据文件包含数据库的启动信息，并指向数据库中的其它文件。用户数据和对象可以存储在主要数据文件和次要数据文件中，使用次要文件可以将数据分散在多个磁盘上以提高读取速度。

（2）次要数据文件

次要数据文件（Secondary data files）是可选的，一般是用户自定义，扩展名一般为.ndf。如果数据库很大，大到单个数据文件大小超过单个 Windows文件的最大限量时，就必须使用次要数据文件，从而可以让数据库继续增长。

2.日志文件

数据库的日志文件用于存储数据库的事务日志信息，事务日志是数据库的黑匣子，它记录了数据库的操作轨迹，包含了可用于恢复数据库的日志信息。日志文件扩展名为.ldf，每个数据库至少有一个日志文件，日志文件也分主要日志文件和次要日志文件，但所有日志文件的扩展名都为.ldf。

3.文件组

• 文件组主要用于数据库文件的集中管理，通常可以将数据库文件集中起来放在文件组中，每个文件组有一个组名。文件组也有三种类型：主文件组、用户定义文件组、默认文件组。
• 每个数据库都有一个主要文件组，该文件组包含主要数据文件和未放入其他文件组的所有次要文件。
• 用户定义文件组是指在创建或修改数据库时，用户创建的文件组。创建了用户文件组后，我们可以任意分配数据文件。如果用户定义的文件组被填满，那么只有该文件组的用户表会受影响。

（三）权限

1.所有者

所有者是对该数据库具有完全操作的用户，默认的该数据库的所有者为创建该数据库的用户。任何可以访问到SQL Server的连接的用户都可以成为数据库的所有者。可以使用 sp_changedbowner 更改数据库所有者。

2.数据库与数据库实例的区别

• 数据库：数据库是文件的集合，是依照某种数据模型组织起来并存放于二级存储器中的数据集合。
• 数据库实例：数据库实例是程序，是位于用户和操作系统之间的一层数据管理软件，用户对数据库中的数据做任何的操作，包括数据定义、数据查询、数据维护、数据库运行控制等等都是在数据库实例下进行的，应用程序只有通过数据库实例才能和数据库打交道。

3.数据库上限

SQL Server支持在同一个数据库实例中创建多个数据库，最多可以达到32767个，一旦超过这个数量，将不能再创建新的数据库。Oracle是一个实例对应一个数据库。

（四）图形界面下创建数据库

例：使用Microsoft SQL Server Management Studio创建数据库“teachingDataD”，要求将数据库存放在D盘的teaching management目录下。
步骤1：在资源管理器中选择D盘根目录，新建一目录“teaching management”。
步骤2：打开Microsoft SQL Server Management Studio。在“连接到服务器”窗口中选择相应的服务器和身份验证方式（这里选择“Windows验证方式”），单击“连接”按钮。

步骤3：在【对象资源管理器】中右击【数据库】，选择命令【新建数据库】。

步骤4：在“新建数据库”窗口中输入数据库名“teachingDataD”，将路径设置为E:\teaching management。
步骤5：完成后单击【确定】按钮。

（五）T-SQL命令创建数据库

创建模板

CREATE DATABASE database_name		--指定待创建数据库的名称
[ON 								--指定显式定义用来存储数据库数据文件
	[PRIMARY] [<filespec> [,…n]			--指定关联的<filespec>列表定义主文件
	[,<filegroup>[,…n]
[LOG ON {<filespec> [,…n]}]			--指定显式定义用来存储数据库日志文件
	]
	[COLLATE collation_name]				--指定数据库默认排序规则
	[FOR ATTACH]    						--附加数据库
	]
	[;]

	<filespec>::=							--文件标识
	{
	(
		[NAME=logical_file_name,]			--指定数据库的逻辑文件名
		FILENAME=’os_file_name’			--指定操作系统文件名
		[,SIZE=size [KB|MB|GB|TB]]		--指定数据库文件的初始大小
		[,MAXSIZE={max_size [KB|MB|GB|TB| UNLIMITED }]	 --指定文件的最大尺寸
		[,FILEGROUTH=growth_increment [KB|MB|GB|TB|%]] 	 --指定文件的增长方式
	) [,…]
	}
	
	<filegroup>::=							--文件组标识
	{
		FILEGROUP filegroup_name<filespec>[,…n]	   --指定创建文件组的组名
		<filespec>[,…n]							--指定组内的文件
	}
	

例：新建一数据库mytest1,要求将数据文件存放在D盘test目录下,日志文件存放在C盘mytest目录下，并要求将数据库文件的初始大小为5M，数据库文件的自动增长率为10%,日志文件的最大尺寸为10M。
步骤1：打开Microsoft SQL Server Management Studio，单击工具栏中的按钮【新建查询】。

步骤2：在查询编辑器中输入建库命令

create database mytest1
on primary
(
    name='mytest1',
	filename='d:\test\mytest1.mdf', size=5MB, filegrowth=10%
)
log on
(
    name=mytest1_log,
	filename='c:\mytest\mytest1_log.ldf', maxsize=10MB
)

步骤3：单击查询编辑器上方的【执行】按钮，可以看【消息】框中显示“命令已成功完成”，则表明已完成建库。

二、数据库的删除

（一）图形界面下删除数据库

在Microsoft SQL Server Management Studio中右击需要修改的数据库名，选择“删除”命令。

（二）T-SQL命令删除数据库

DROP DATABASE { database_name} [ ,...n ] }

三、数据库的修改

（一）图形界面下修改数据库

例：使用Microsoft SQL Server Management Studio修改数据库“teachingDataD”，将自动增长方式修改为2M，并添加次要数据文件teachingDataA3.ndf，其数据增长也设置为２M。
步骤1：在 【对象资源管理器】中右击teachingDataD，选择【属性】命令，然后在“数据库属性”窗口的【选择页】中选择【文件】，单击主文件行中的自动增长按钮，然后按要求将文件增长【按MB】设置为“２”，如图4所示，单击【确定】按钮。

步骤2：在“数据库属性”窗口中单击【添加】按钮，输入逻辑文件名teachingDataA3，并采用与步骤１类似的方法设置文件增长为２MB。
步骤3：单击【确定】按钮。

（二）T-SQL命令修改数据库

ALTER DATABASE databasename
{
	ADD FILE <filespec>[,…n] [to filegroup filegroupname]		--添加数据文件
	| ADD LOG FILE <filespec>[,…n]						--添加日志文件
	| REMOVE FILE logical_file_name [WITH DELETE]	--移除或删除日志文件
	| MODIFY FILE <filespec>							--修改文件属性
	| MODIFY NAME=new_databasename					--修改文件名
	| ADD FILEGROUP filegroup_name					--添加文件组
	| REMOVE FILEGROUP filegroup_name				--移除文件组
	| MODIFY FILEGROUP filegroup_name				--修改文件组名
	{
		FILEGROUP_PROPERTY | NAME=new_filegroup_name
}
}

例：使用ALTER DATABASE命令进行修改数据库“teachingDataE”，将自动增长方式修改为2M，并添加次要数据文件teachingData3.ndf，其数据增长也设置为２M。
步骤1：在查询编辑器中输入如下命令，单击【执行】按钮，即可将自动增长方式修改为２MB。

alter database teachingDataE
modify file
{
    name='teachingDataE',
    filegrowth=2MB
}

步骤2：在查询编辑器中输入以下命令，单击查询编辑器上方的【执行】命令。

alter database teachingDataE
add file
{
    name='teachingDataE',
    filename='E:\teaching management\teachingData3.ndf',
    filegrowth=2MB
}