数据类型和表结构

数据类型和表结构

在表创建结构前，需要熟悉SQL Server 2016的数据类型。

3.1.1 数据类型

SQL Server提供的数据类型如表3.2所示。

在讨论数据类型时，使用了精度、小数位数和长度3个概念，前两个概念是针对数值型数据的，它们的含义如下。

• 精度：指数值数据中所存储的十进制数据的总位数。

• 小数位数：指数值数据中小数点右边可以有的数字位数的最大值。例如，数值数据3890.587的精度是7，小数位数是3。

• 长度：指存储数据所使用的字节数。

下面分别介绍常用的系统数据类型。

1. 整数型

整数型包括bigint、int、smallint和tinyint，从标识符的含义就可以看出，它们的表示数范围逐渐缩小。

（1）bigint：大整数，数范围为−263～263−1，其精度为19，小数位数为0，长度为8字节。

（2）int：整数，数范围为−231～231−1，其精度为10，小数位数为0，长度为4字节。

（3）smallint：短整数，数范围为−215～215−1，其精度为5，小数位数为0，长度为2字节。

（4）tinyint：微短整数，数范围为0～255，其精度为3，小数位数为0，长度为1字节。

2. 精确数值型

精确数值型数据由整数部分和小数部分构成，其所有的数字都是有效位，能够以完整的精度存储十进制数。精确数值型包括decimal和numeric两类。但这两种数据类型在功能上完全等价。

声明精确数值型数据的格式是numeric | decimal(p[,s])，其中p为精度，s为小数位数，s的默认值为0。例如，指定某列为精确数值型，精度为6，小数位数为3，即decimal(6,3)，那么当向某记录的该列赋值56.342 689时，该列实际存储的是56.343。

decimal和numeric可存储−1038+1～1038−1的固定精度和小数位的数字数据，它们的存储长度随精度变化而变化，最少为5字节，最多为17字节。

注意

声明精确数值型数据时，其小数位数必须小于精度。在给精确数值型数据赋值时，必须使所赋数据的整数部分位数不大于列的整数部分的长度。

3. 浮点型

浮点型也称近似数值型。顾名思义，这种类型不能提供精确表示数据的精度，使用这种类型来存储某些数值时，有可能会损失一些精度，所以它可用于处理取值范围非常大且对精确度要求不太高的数值量，如一些统计量。

有两种近似数值数据类型：float[(n)]和real，两者通常都使用科学计数法表示数据，即形为：尾数E阶数，如5.6432E20，−2.98E10，1.287659E−9等。

（1）real：使用4字节存储数据，数范围为−3.40E+38～3.40E+38，数据精度为7位有效数字。

（2）float：float型数据的数范围为−1.79E+308～1.79E+308。定义中的n取值范围是1～53，用于指示其精度和存储大小。当n在1～24时，实际上将定义一个real型数据，存储长度为4字节，精度为7位有效数字。

当n在25～53时，存储长度为8字节，精度为15位有效数字。当省略n时，代表n在25～53。

4. 货币型

SQL Server提供了两个专门用于处理货币的数据类型：money和smallmoney，它们用十进制数表示货币值。

（1）money：数据的数范围为−263～263−1，其精度为19，小数位数为4，长度为8字节。money的数范围与bigint相同，不同的只是money型有4位小数。实际上，money就是按照整数进行运算的，只是将小数点固定在最后4位。

（2）smallmoney：数范围为−231～231−1，其精度为10，小数位数为4，长度为4字节。

可见smallmoney与int的关系就如同money与bigint的关系。

当向表中插入money或smallmoney类型的值时，必须在数据前面加上货币表示符号（$），并且数据中间不能有逗号（,）；若货币值为负数，则需要在符号$的后面加上负号（−）。例如，$15 000.32，$680，$−20000.9088都是正确的货币数据表示形式。

5. 位型

SQL Server的位（bit）型数据相当于其他语言中的逻辑型数据，它只存储0和1，长度为1字节。但要注意，SQL Server对表中bit类型列的存储进行了优化：如果一个表中有不多于8个的bit列，则这些列将作为1字节存储；如果表中有9～16个bit列，则这些列将作为2字节存储，更多列的情况以此类推。

当为bit类型数据赋0时，其值为0；而赋非0（如100）时，其值为1。

字符串值TRUE和FALSE可以转换为以下bit值：TRUE转换为1，FALSE转换为0。

6. 字符型

字符型数据用于存储字符串，字符串中可包括字母、数字和其他特殊符号（如#、@、&等）。在输入字符串时，需将串中的符号用单引号或双引号括起来，如'abc'、"Abc<Cde"。

SQL Server字符型包括两类：固定长度（char）或可变长度（varchar）字符数据类型。

（1）char[(n)]：定长字符数据类型，其中n定义字符型数据的长度，n在1～8 000，默认为1。当表中的列定义为char(n)类型时，若实际存储的串长度不足n时，则在串的尾部添加空格以达到长度n，所以char(n)的长度为n。

例如，某列的数据类型为char(20)，而输入的字符串为"ahjm1922"，则存储的是字符ahjm1922和12个空格。若输入的字符个数超出了n，则超出的部分被截断。

（2）varchar[(n)]：变长字符数据类型，其中，n的规定与定长字符型char中的n完全相同，但这里n表示的是字符串可达到的最大长度。

varchar(n)的长度为输入字符串的实际字符个数，而不一定是n。例如，表中某列的数据类型为varchar(100)，而输入的字符串为"ahjm1922"，则存储的就是字符ahjm1922，其长度为8字节。当列中的字符数据值长度差不多时，如姓名，此时可使用char；当列中的数据值长度显著不同时，使用varchar较为恰当，可以节省存储空间。

7. unicode字符型

unicode是“统一字符编码标准”，用于支持国际上非英语语种的字符数据的存储和处理。SQL Server的unicode字符型可以存储Unicode标准字符集定义的各种字符。

unicode字符型包括nchar[(n)]和nvarchar[(n)]两类。nchar是固定长度unicode数据的数据类型，nvarchar是可变长度unicode数据的数据类型，二者均使用UNICODE UCS-2字符集。

（1）nchar[(n)]：nchar[(n)]为包含n个字符的固定长度unicode字符型数据，n的值在1～4 000，长度为2n字节。若输入的字符串长度不足n，将以空白字符补足。

（2）nvarchar[(n)]：nvarchar[(n)]为最多包含n个字符的可变长度unicode字符型数据，n的值在1～4 000，默认为1。长度是所输入字符个数的两倍。

实际上，nchar、nvarchar与char、varchar的使用非常相似，只是字符集不同（前者使用unicode字符集，后者使用ASCII字符集）。

8. 文本型

当需要存储大量的字符数据，如较长的备注、日志信息等时，字符型数据最长8 000个字符的限制可能使它们不能满足这种应用需求，此时可使用文本型数据。

文本型包括text和ntext两类，分别对应ASCII字符和unicode字符。

（1）text类型：可以表示最大长度为231−1个字符，其数据的存储长度为实际字符数个字节。

（2）ntext类型：可表示最大长度为230−1个unicode字符，其数据的存储长度是实际字符个数的两倍（以字节为单位）。

9. 二进制型

二进制数据类型表示的是位数据流，包括binary（固定长度）和varbinary（可变长度）两种。

（1）binary [(n)]：固定长度的n个字节二进制数据。n的取值范围为1～8000，默认为1。binary(n)数据的存储长度为n+4个字节。若输入的数据长度小于n，则不足部分用0填充；若输入的数据长度大于n，则多余部分被截断。

（2）varbinary [(n)]：n个字节变长二进制数据。n取值范围为1～8 000，默认为1。varbinary(n)数据的存储长度为实际输入数据长度+4个字节。

10. 日期时间类型

日期时间类型数据用于存储日期和时间信息，日期时间数据类型包括date、time、datetime2和datetimeoffset。

（1）datetime：可表示的日期范围从1753年1月1日到9999年12月31日，精确度为0.03s（3.33ms或0.00333s）。例如，1～3ms的值都表示为0ms，4～6ms的值都表示为4ms。

datetime类型数据长度为8字节，日期和时间分别使用4个字节存储。前4字节用于存储datetime类型数据中距1900年1月1日的天数。为正数表示日期在1900年1月1日之后，为负数则表示日期在1900年1月1日之前。后4字节用于存储datetime类型数据中距12:00（24小时制）的毫秒数。

用户以字符串形式输入datetime类型数据，系统也以字符串形式输出datetime类型数据。通常将用户输入到系统及系统输出的datetime类型数据的字符串形式称为datetime类型数据的“外部形式”，而将datetime在系统内的存储形式称为“内部形式”。SQL Server负责datetime类型数据的两种表现形式之间的转换，包括合法性检查。

用户给出datetime类型数据值时，日期部分和时间部分分别给出。

日期部分的表示形式常用的格式如下：

 

说明年可用4位或2位表示，月和日可用1位或2位表示。

时间部分常用的表示格式如下：

（2）smalldatetime：可表示从1900年1月1日到2079年6月6日的日期和时间，数据精确到分钟。即29.998s或更低的值向下舍入为最接近的分钟，29.999s或更高的值向上舍入为最接近的分钟。

smalldatetime类型数据的存储长度为4字节，前2字节用来存储smalldatetime类型数据中日期部分距1900年1月1日之后的天数。后2字节用来存储smalldatetime类型数据中时间部分距中午12点的分钟数。

用户输入smalldatetime类型数据的格式与datetime类型数据完全相同，只是它们的内部存储可能不相同。

（3）date：可以表示从公元元年1月1日到9999年12月31日的日期，date类型只存储日期数据，不存储时间数据，存储长度为3字节，表示形式与datetime数据类型的日期部分相同。

（4）time：只存储时间数据，表示格式为“hh:mm:ss[.nnnnnnn]”。hh表示小时，范围为0～23。mm表示分钟，范围为0～59。ss表示秒数，范围为0～59。n是0～7位数字，范围为0～9 999 999，表示秒的小数部分，即微秒数。所以time数据类型的取值范围为00:00:00.000 000 0～23:59:59.999 999 9。time类型的存储大小为5字节。另外，还可以自定义time类型微秒数的位数，例如，time(1)表示小数位数为1，默认为7。

（5）datetime2：也用于存储日期和时间信息。但是datetime2类型取值范围更广，日期部分取值范围从公元元年1月1日到9999年12月31日，时间部分的取值范围为00:00:00.000 000 0～23:59:59.999 999。另外，用户还可以自定义datetime2数据类型中微秒数的位数，例如，datetime(2)表示小数位数为2。datetime2类型的存储大小随着微秒数的位数（精度）而改变，精度小于3时为6字节，精度为4和5时为7字节，所有其他精度则需要8字节。

（6）datetimeoffset：也用于存储日期和时间信息，取值范围与datetime2类型相同。但datetimeoffset类型具有时区偏移量，此偏移量指定时间相对于协调世界时（UTC）偏移的小时和分钟数。

datetimeoffset的格式为“YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|−}hh:mm]”，其中，hh为时区偏移量中的小时数，范围为00～14，mm为时区偏移量中的额外分钟数，范围为00～59。时区偏移量中必须包含“+”（加）或“−”（减）号。这两个符号表示是在UTC时间的基础上加上还是从中减去时区偏移量以得出本地时间。时区偏移量的有效范围为−14:00～+14:00。

11. 时间戳型

标识符是timestamp。若创建表时定义一个列的数据类型为时间戳类型，那么每当对该表加入新行或修改已有行时，都由系统自动将一个计数器值加到该列，即将原来的时间戳值加上一个增量。记录timestamp列的值实际上反映了系统对该记录修改的相对（相对于其他记录）顺序。一个表只能有一个timestamp列。timestamp类型数据的值实际上是二进制格式数据，其长度为8字节。

12. 图像数据类型

标识符是image，它用于存储图片、照片等。实际存储的是可变长度二进制数据，介于0与231−1 (2 147 483 647)字节。该类型是为了向下兼容而保留的数据类型，微软推荐用户使用varbinary(MAX)数据类型来替代image类型。

其他几种数据类型有：cursor、sql_variant、table、uniqueidentifier、xml和hierarchyid。

（1）cursor：游标数据类型，用于创建游标变量或定义存储过程的输出参数。

（2）sql_variant：一种存储SQL Server支持的各种数据类型（除text、ntext、image、timestamp和sql_variant外）值的数据类型。sql_variant的最大长度可达8 016字节。

（3）table：用于存储结果集的数据类型，结果集可以供后续处理。

（4）uniqueidentifier：唯一标识符类型。系统将为这种类型的数据产生唯一标识值，它是一个16字节长的二进制数据。

（5）xml：用来在数据库中保存xml文档和片段的一种类型，但是此种类型文件的大小不能超过2 GB。

（6）hierarchyid：可表示层次结构中的位置。

varchar、nvarchar、varbinary这3种数据类型可以使用MAX关键字，如varchar(MAX)、nvarchar(MAX)、varbinary(MAX)，加了MAX关键字的这几种数据类型最多可存放231-1个字节的数据，分别可以用来替换text、ntext和image数据类型。

3.1.2 表结构设计

定义表结构就是要确定表的名字、表的属性，同时确定表所包含的列名、列的数据类型、长度、是否可为空值、约束条件、默认值设置、规则，以及所需索引、哪些列是主键、哪些列是外键等。这里仍以学生成绩管理系统的3个表：学生表（xsb）、课程表（kcb）和成绩表（cjb）为例介绍如何设计表的结构。

SQL创建数据库

CREATE TABLE xsb1
(
	学号		char(6)			NOT NULL PRIMARY KEY,
	姓名		char(8)			NOT NULL,
	性别		bit    			NULL DEFAULT 1,
	出生时间    	date			NULL,
	专业		char(12)		NULL,
	总学分		int    			NULL DEFAULT 0,
	备注		varchar(500)	NULL
)
GO

【例3.2】创建一个带计算列的表“pcj”，表中包含课程的课程号、总成绩和学习该课程的人数，以及课程的平均成绩。

CREATE TABLE pcj
(
	课程号			char(3) PRIMARY KEY,
	总成绩			real	NOT NULL,
	人数			int		NOT NULL,
	平均成绩 AS 总成绩/人数 PERSISTED  --计算列
)