2.1 T-SQL语句回顾
在《使用SQL Server管理和查询数据》中，我们学习过T-SQL语句，它是Microsoft SQL Server 数据库对SQL语句的扩展，T-SQL语句的数据操作语法如下。
1. 添加数据
语法：Insert [into] 表名 （字段1，字段2，···） Values （值1，值2，···）
其中，Into 可选。
例如： Insert into stuInfo (stuName, stuNo, stuAge) values ('张三', 's25301'， 22)
2. 修改数据
语法：Update 表名 set 字段1 = 值1 ， 字段2 = 值2，··· Where (条件)
例如：Update StuInfo set stuAge = 24 where stuName = '张三'
3. 查询数据
语法：Select 字段1，字段2，··· from 表名 where (条件) Order by 字段名
例如：Select stuName, stuNo from stuInfo where stuAge < 25 Order by stuNo
4. 删除数据
语法：Delete from 表名 where (条件)
例如：Delete from stuInfo where stuAge < 20
当项目经测试基本满足客户的需求后，需要部署在客户的实际环境中试运行。我们在部署前需要考虑的是，后台的数据库如何移植到客户的计算机中？考虑各种数据库版本的兼容性，行之有效的办法就是编写比较通用的SQL语句，包括创建库、创建表、添加约束、插入测试数据等。编写完毕后，存入*.sql文件中，最后复制到客户的计算机中，并执行*.sql文件中的SQL语句，从而实现后台数据库的移植。所以，我们还需要掌握如何使用SQL语句，实现创建库、创建表、添加约束和创建登录账户等。
2.2 使用SQL语句创建和删除数据库
我们先简要回顾一下SQL Server数据库的基础知识。
数据库文件由以下3部分组成。
》》》主数据文件：*.mdf。
》》》次要数据文件：*.ndf。
》》》日志文件：*.ldf。
其中，次要数据文件库可选，可以有多个数据库文件和日志文件。
数据库文件的其他属性。
》》》文件存放位置，分配的初始空间，属于哪个文件组。
》》》文件的增长设置，可以按百分比或实际大小指定增长速度。
》》》文件容量设置，可以指定文件增长的最大值或不受限。
其中，文件组允许对数据文件进行分组，以便于管理和数据的分配/放置。例如，可以分别在3个硬盘驱动器上创建3个文件（Data1.mdf，Data2.ndf和Data3.ndf），并将这3个文件指派到文件组fgroup1中。然后，可以明确地在文件组fgroup1中创建一个表。对表中数据的查询将分散到3个磁盘上，可以采用并发查询，因而性能得以提高。
2.2.1 创建数据表
T-SQL创建数据库的语法如下：
Creat Database 数据库名
On [PRIMARY]
(
<数据文件参数>[,···n] [<文件组参数>]
)
[Log on]
(
{<日志文件参数>[,···n]}
)
文件的具体参数的语法如下。
([Name = 逻辑文件名，]
FileName = 物理文件名
[, Size = 大小]
[, MaxSize = (最大容量 | Unlimited)]
[, Filegrowth = 增长量]) [,···n]
文件组参数的语法如下。
Filegroup 文件组名 <文件参数>[,···n]
其中，“[]”表示可选部分，“{}”表示必需的部分。各参数的含义说明如下。
》》》数据库名：数据库的名称，最长为128个字符。
》》》Primary：该选项是一个关键字，指定主文件组中的文件。
》》》Log on：指明事务日志文件的明确定义。
》》》Name：指定数据库的逻辑名称，这是在SQL Server系统中使用的名称，是数据库在SQL Server中的标识符。
》》》FileName：指定数据库所在文件的操作系统文件名称和路径，该操作系统文件名和Name的逻辑名称一一对应。
》》》Size：指定数据库的初始容量大小。
》》》Maxsize：指定操作系统文件可以增长到的最大尺寸。
》》》Filegrowth：指定文件每次增加容量的大小，当指定数据为0时，表示文件不增长。
示例一：
创建一个数据文件和一个日志文件。
Create Database stuDB
on primary --默认就属于primary主文件组，可省略
(
/*-- 数据文件的具体描述 --*/
Name = 'stuDB_data', --主数据文件的逻辑名称
filename = 'D:stuDB_data.mdf', --主数据文件的物理名称
size = 5mb, --主数据文件的初始大小
Maxsize = 100mb, --主数据文件增长的最大值
Filegrowth = 15%, --主数据文件的增长率
)
Log on
(
/*-- 日志文件的具体描述，各参数含义同上 --*/
Name = 'stuDB_log',
FileName = 'D:stuDB_log.ldf',
size = 2mb,
Filegrowth = 1mb
)
go --和后续的SQL语句分隔开
在示例1中创建了学员信息数据库stuDB，该数据库的主数据文件逻辑名称为stuDB_data，物理文件名称为stuDB_data.mdf，初始大小为5MB，最大尺寸为100MB，增长速度为15%。数据库的日志文件逻辑名称为stuDB_log，物理文件名称为stuDB_log.ldf，初始大小为2MB，增长速度为1MB。该数据库存放在D:盘。
示例二：
创建多个数据文件和多个日志文件。
Create DataBase employees
on Primary
(
/*-- 主数据文件的具体描述 --*/
Name = 'employees1',
FileName = 'D:employees1.mdf',
Size = 10,
Filegrowth = 10%,
),
(
/*-- 次要数据文件的具体描述 --*/
Name = 'employee2',
FileName = 'D:employee2.ndf',
Size = 20,
MaxSize = 100,
FileGrowth = 1
)
Log On
(
/*-- 日志文件1的具体描述 *--/
Name = 'employeelog1',
FileName = 'D:employeelog1_log.ldf',
Size = 10,
MaxSize = 50,
FileGrowth = 1
)
(
/*-- 日志文件2的具体描述 *--/
Name = 'employeelog2',
FileName = 'D:employeelog2_log.ldf',
Size = 10,
MaxSize = 50,
FileGrowth = 1
)
go -- 和后续的SQL语句分隔开
示例2创建了employees数据库，有1个10MB和1个20MB的数据文件，还有两个10MB的事务日志文件。数据文件逻辑名称为employee1和employee2，物理文件名称为employee1.mdf和employee2.ndf。主文件是employee1，由primary指定，两个数据文件的最大尺寸分别为无限大和100MB，增长速度分别为10%和1MB。事务日志文件的逻辑名称为employeelog1和employeelog2，物理文件名称为employeelog1_log.ldf和employeelog2_log.ldf，最大尺寸均为50MB，文件增长速度均为1MB。
2.2.2 删除数据库
如果SQL Server中已存在stuDB数据库，再次运行示例1的创建数据库的语句，就会发现系统提示错误：该数据库已存在，创建失败。如何解决？
我们应预先检测是否存在stuDB数据库，如果已存在，就应先删除stuDB数据库，然后再创建。
删除数据库的语法如下。
Drop database 数据库名
例如：Drop database stuDB
现在的问题是，如何检测是否存在stuDB数据库？答案很简单，SQL Server将数据库的清单存放在master系统数据库的sysdatabases表中，只需要查看该表是否存在于该数据库中就可以了，所以，示例1完整的T-SQL语句即如下所示。
User master
go
if exists (select * from sysdatabases where name = 'stuDB')
Drop database stuDB
Create database stuDB
···
Exists （查询语句）检测某个查询是否存在。如果查询语句返回的记录结果不为空，则表示存在；否则表示不存在。
2.3 使用SQL语句创建和删除表
我们先简要回顾以下SQL Server中表的基础知识。
（1）建表的步骤如下。
确定表中有哪些列。
确定每列的数据类型。
给表添加各种约束。
创建各表之间的关系。
（2）为方便查阅，下面列出SQL Server中的数据类型。
| 类型 | 数据类型 | 描述 |
| 整型 | int | int 数据类型可以存储 -2(31次方)（-2147183648）~2(31次方)---1(2147483647)之间的整数。存储到数据库的几乎所有数值型的数据都可以用这种数据类型。这种数据类型在数据库中占用4个字节 |
| smallint | smallint 数据类型可以存储-2(15次方)（-32768）~2(15次方)---1(32767)之间的整数。这种数据类型对存储一些常限定在特定范围内的数值型数据非常有用。这种数据类型在数据库中占用两个字节 | |
| tinyint | tinyint 数据类型可以存储0~255之间的整数，它在只打算存储有限数目的数值时很有用，这种数据类型在数据库中占用1个字节 | |
| 浮点型 | numeric | numeric 数据类型于decimal 型相同 |
| real | real 数据类型像浮点数一样，是近似数值类型。它可以表示数值在-3.40E+308~1.79E+308之间的任意数 | |
| float | float 数据类型是一种近似数值类型，供浮点数使用。说浮点数是近似的，是因为在其范围内不是所有的数都能精确地表示。浮点数可以是-1.79E+308~1.79E+308之间的任意数 | |
| decimal | decimal 数据类型用来存储从-10(38次方)~10(38次方)-1的固定精度和范围的数值型数据。使用这种数据类型时，必须指定范围和精度。范围是小数点左右所能存储的数字的总位数，精度是小数点右边存储的数字的位数 | |
| 字符型 | char | char数据类型用来存储指定长度的定长非统一编码型的数据。当定义一列为此类型时，必须指定列长。当总能知道要存储的数据的长度时，此数据类型很有用。例如。当按邮政编码加4个字符格式来存储数据时，应知道总要用到10个字符。此数据类型的列宽最大为8000个字符。 |
| varchar | varchar 数据类型同 char 类型一样，用来存储非统一编码型字符数据。与char 类型不一样的是，此数据类型为变长。当定义一列为该数据类型时，要指定该列的最大长度。它与 char 数据类型最大的区别是，存储的长度不是列长，而是数据的长度 | |
| text | text 数据类型用来存储大量的非统一编码型字符数据。这种数据类型最多可以有2(31次方)-1或20亿个字符 | |
| Unicode型 | nchar | nchar 数据类型用来存储定长 Unicode (统一编码) 字符型数据。Unicode 编码用双字节结构来存储每个字符，而不是用单字节（普通文本中的情况）。它允许大量地扩展字符。此数据类型能存储4000种字符，使用的字节空间增加了一倍 |
| nvarchar | nvarchar 数据类型用作变长的 Unicode 编码字符型数据。这种数据类型能存储4000种字符，使用的字节空间增加了一倍 | |
| ntext | ntext 数据类型用来存储大量的 Unicode 编码字符型数据。这种数据类型能存储 2(30次方)-1 或将近10亿个字符。使用的字节空间增加了一倍 | |
| 是/否型 | bit | bit 数据类型表示是/否值，其值只能是0、1或空值。这种数据类型用于存储只有两种可能值的数据，如yes 或 no 、 true 或 false 、 on 或 off。 |
| 二进制型 | binary | binary 数据类型用来存储可达8000 字节长的定长的二进制数据。当输入表的内容接近相同的长度时，应该使用这种数据类型 |
| varbinary | varbinary 数据类型用来存储可达8000 字节长的变长的二进制数据。当输入表的内容大小可变时，应该使用这种数据类型 | |
| image | image 数据类型用来存储变长的二进制数据，最大可达 2(31次方)-1 或 20亿字节 | |
| 货币型 | money | money 数据类型用来表示钱和货币值。这种数据类型可以存储从-9220 ~ 9220 亿之间的数据，可以精确到货币单位的1/10000 |
| smallmoney | smallmoney 数据类型用来表示钱和货币值。这种数据类型可以存储从-214748.3648 ~ 214748.3647 之间的数据，可以精确到货币单位的1/10000 | |
| 日期时间型 | datetime | datetime 数据类型用来表示日期和时间，这种数据类型存储从1753年1月1日到9999年12月31日间所有的日期和时间数据，可以精确到11300秒或3.33毫秒 |
| smalldatetime | smalldatetime 数据类型用来表示从1990年1月1日到2079年6月6日间的日期和时间，精确到一分钟 | |
| 特殊类型 | timestamp | timestamp数据类型是一种特殊的数据类型，用来创建一个数据库范围内的唯一时间戳。一个表中只能有一个timestamp列。每次插入或修改一行时，timestamp列的值都会改变。尽管它的名字中有'time'，但timestamp列不是人们可识别的日期。在一个数据库中，timestamp值是唯一的 |
| uniqueidentifier | uniqueidentifier 数据类型用来存储一个全局的唯一标识符，既GUID.这个数几乎没有机会在另一个系统中被重建，可以使用NEWID 函数或转换一个字符串为唯一标识符来初始化具有唯一标识符的列 |
2.3.1 创建表
创建表的语法如下：
create table 表名 { 字段1 数据类型 列的特征， 字段2 数据类型 列的特征 }
其中，“列的特征”包括该列是否为空（NULL）、是否是标识列（自动编号）、是否有默认值、是否为主键等。
考虑各种开发语言的兼容性，表中各字段名称推荐使用英文缩写。
示例 3
创建学员信息表 stuInfo
Use stuDB --- 将当前数据库设置为stuDB，以便在stuDB数据库中创建表
Go
Create Table stuInfo( --- 创建学员信息表
stuName varcahr(20) not null, --- 学员姓名，非空（必填）
stuNo char(6) not null, --- 学号，非空（必填）
stuAge int not null, --- 年龄，int类型不用指定大小，默认是4个字节
stuID Numeric(18,0), --- 身份证号，numeric(18,0)代表18位数字，
stuSeat SmallInt Identity(1,1), --- 座位号，自动编号（标识列），从1开始递增
stuAddress text --- 住址，允许为空，即可选输入
)GO
其中，列属性“Identity”（起始值，递增量）表示“stuSeat”列为自动编号，也称为标识列。
示例 4
创建学员成绩表 stuMarks
create table stuMarks (
examNo char(7) not null,
stuNo char(6) not null,
writtenExam int not null,
labexam int not null
) go
2.3.2 删除表
同创建数据库一样，如果当前数据库中已存在stuInfo表，再次创建时系统将提示出错。解决的办法也一样。我们需要预先检测是否存在该表，如果存在，则先删除，再创建。
删除表的语法如下。
Drop table 表名
例如：
Drop table stuInfo
某个数据库中表的清单又存放在那里呢？答案是该数据库的系统表sysobjects，所以上述完整的创建表的语句应如下所示。
User stuDB
go
if exists (select * from sysobjects where name = 'stuInfo')
drop table stuInfo
create table stuInfo (...) go
2.4 使用SQL语句创建和删除约束
约束的目的是确保表中数据的完整性。
常用的约束类型如下。
=== 主键约束（Primary key constraint）: 要求主键列数据唯一，并且不允许为空。
=== 唯一约束（Unique Constraint）: 要求该列唯一，允许为空，但只能出现一个空值。
=== 检查约束（Check Constraint）: 某列取值范围限制、格式限制等，如有关年龄的约束。
=== 默认约束（Default Constraint）: 某列的默认值，如我们的男性学员较多，性别默认为男。
=== 外键约束（Foreign key Constraint）: 用于在两表之间建立关系，需要指定引用主表的那一列。
2.4.1 添加约束
在创建表时，我们可以在字段后添加各种约束，但一般不这样混合使用，推荐将添加约束和创建表的语句分开编写。
添加约束的语法如下。
Alter table 表名
Add constraint 约束名 约束类型 具体的约束说明
上述语法表示修改某个表，添加某个约束。其中，约束名的命名规则推荐采用“约束类型_约束字段”这样的形式。
例如：
为学号字段（stuNo）添加的主键约束，约束名推荐为“PK_stuNo”
为身份证号字段（stuID）添加的唯一约束，约束名推荐取名为“UQ_stuID”
为地址字段（stuAddress）添加的默认约束，约束名推荐取名为“DF_stuAddress”
为年龄字段（stuAge）添加的检查约束，约束名推荐取名为“CK_stuAge”
为学员成绩表字段（stuMarks）添加的外键约束，约束名推荐取名为“FK_stuNo”
示例5
--- 添加主键约束（将stuNo作为主键）
Alter table stuInfo
Add Constraint PK_stuNo Primary key (stuNo)
--- 添加唯一约束（身份证号唯一，因为每个人的身份证号全国唯一）
Alter table stuInfo
Add Constraint UQ_stuID Unique (stuID)
--- 添加默认约束（如果地址不填，默认为“地址不详”）
Alter table stuInfo
Add Constraint DF_stuAddress Default ('地址不详') for stuAddress
--- 添加检查约束，要求年龄只能在15~40岁之间
Alter table stuInfo
Add Constraint CK_stuAge Check ( stuAge BetWeen 15 and 40)
--- 添加外键约束（主表stuInfo和从表stuMarks建立关系，关联字段为stuNo）
Alter table stuMarks
Add Constrint FK_stuNo
foreign key(stuNo) references stuInfo (stuNo)
go
2.4.2 删除约束
如果错误地添加了约束，则可以删除约束
删除约束的语法如下
Alter table 表名
Drop Constraint 约束名
例如：删除stuInfo 表中地址默认约束的语句如下。
Alter table stuInfo
Drop Constraint DF_stuAddress
2.5 使用SQL语句创建登录
正如一个防卫森严的小区，如果要进入自己的房间，需要闯3关。第一关：需要通过小区的门卫检查，进入小区。第二关：到了所在的单元楼门前，还需要单元门的钥匙或门铃密码。第三关：进了单元门后，还需要自己房间的钥匙。
我们回忆以下SQL Server 的3层安全模型，非常类似于小区的3层验证关口。
=== 第一关：需要登录到SQL Server系统，即需要登录账户。
=== 第二关：需要防卫某个数据库（相当于我们的单元楼），即需要成为该数据库的用户。
=== 第三关：需要访问数据库中的表（相当于打开我们的房间），即需要数据库管理员DBA给自己授权，如增添、修改、删除、查询等权限。
2.5.1 创建登录账户
登录验证有以下两种方式。
SQL 身份验证：适合于非Windows平台的用户或Internet用户，需要提供账户和密码
Windows身份验证：适合于Windows平台用户，不需要提供密码，和Windows集成验证。
所以，我们创建的登录账户相应有两种：SQL账户和Windows账户。
添加Windows登录账户需要调用SQL Server内置的系统存储过程sp_grantlogin，调用语法如下：
Exec sp_grantlogin 'windows域名/域账户'
其中，Exec关键字表示调用执行，如果是本机，可用计算机名替换“Windows域名”。关于存储过程，将在后续章节中讲解。
添加SQL登录账户需要调用系统存储过程sp_addlogin，调用语法如下。
Exec sp_addlogin '账户名'，'密码'
示例6
--- 添加windows登录账户
Exec sp_grantlogin 'qazs123'
--- Windows用户为qazs123，qaz表示域
--- 添加SQL登录账户
Exec sp_addlogin 'zhangsan','123' --- 账户名为 zhangsan， 密码为123
go
2.5.2 创建数据库用户
正如您进入了小区，但还不能进入单元门一样。创建了登录账户，现在能登录到SQL Server服务器，但还不能访问某个数据库。如果希望访问某个数据库，必须有“打开”该数据库的“通关钥匙”，即需要成为该数据库的一个用户。
创建数据库用户需要调用系统存储过程sp_grantdbaccess，调用语法如下。
Exec sp_grantdbaccess '登录账户', '数据库用户'
其中，“数据库用户”为可选参数，默认为登录账户，即数据库用户默认和登录账户同名。
示例7
--- 在stuDB数据库中添加两个用户
User stuDB
go
Exec sp_grantdbaccess 'qazs123' , 's123DBUser'
--- s123DBUser 为数据库用户名
Exec sp_grantdbaccess 'zhangsan', 'zhangsanDBUser'
2.5.3 给数据库用户授权
数据库中有很多表，所以，必须让数据库管理员DBA给您授权，指定具有访问哪些表格的哪些权限。常用的权限包括添加数据（Insert）、删除数据（Delete）、更新数据（Update）、查看数据（Select）和创建表（Create Table）等操作。
授权的语法如下：
Grant 权限 [on 表名] to 数据库用户
示例8
user stuDB
go
--- 为zhangsanDBUser 分配对表stuInfo的Select、insert、update权限
Grant select , insert , update on stuInfo to zhangsanDBUser
--- 为s123DBUser 分配创建表的权限
Grant create table to s123DBUser