SQL Server从入门到项目实践（超值版）读书笔记 09

第7章 创建与管理数据表

🎉学习指引：

在数据库中，数据实际存储在数据表中，可见在数据库中，数据表是数据库中最重要、最基本的操作对象，是数据存储的基本单位。本章内容包括：创建数据表、管理数据表结构、修改数据表、删除数据表等。

7.1 数据表基础

在创建数据表之前，需要事先定义好数据列的数据类型，即定义数据表中各列所允许的数据值。SQL Server为用户提供了两种数据类型，一种是基本数据类型，一种是自定义数据类型。

7.1.1 基本数据类型

SQL Server提供的基本数据类型按照数据的表现方式及存储方式不同，可以分为整数数据类型、货币数据类型、浮点数据类型。

通过使用这些数据类型，在创建数据表的过程中，SQL Server会自动限制每个系统数据类型值的范围，当插入的值超过了数据类型允许的范围时，就会报错。

7.1.1.1 整数数据类型

整数数据类型是常用的一种数据类型，主要用于存储整数，可以直接进行数据运算而不必使用函数转换。

数据类型	描述
bit	允许介于 -9223372036854775808 ~ 9223372036854775808 之间的所有数字
int	允许介于 -2147483648 ~ 214748364 之间的所有数字
smallint	允许介于 -32768 ~ 32768 之间的所有数字
tinyint	允许介于 0 ~ 255 之间的所有数字

7.1.1.2 浮点数据类型

浮点数据类型用于存储十进制小数。浮点数据为近似值，浮点数据在SQL Server中采用只入不舍的方式进行存储，即当且仅当要舍入的数是一个非零数时，对其保留数字部分的最低位加1，并进行必要的进位。

数据类型	描述
real	从 -3.40E+38 ~ 3.40E+38 的浮动精度数字数据
float(n)	从 -1.79E+308 ~ 1.79E+308 的浮动精度数字数据 参数n指示该字段保存4字节还是8字节 float（24）保持4字节，而float（53）保存8字节（默认）
decimal(p,s) 建议使用	固定精度和比例的数字 允许从 -10^38+1 ~ 10^38-1 的数字 参数p指示可以存储的最大位数（小数点左侧和右侧） p必须是1~38的值，默认18 参数s指示小数点右侧存储的最大位数，s必须是0~p的值，默认是0
numeric(p,s)	固定精度和比例的数字 允许从 -10^38+1 ~ 10^38-1 的数字 参数p指示可以存储的最大位数（小数点左侧和右侧） p必须是1~38的值，默认18 参数s指示小数点右侧存储的最大位数，s必须是0~p的值，默认是0

💡提示：
decimal(p,s)与numeric(p,s)在SQL Server中是等价的。它们的存储方式、精度范围、计算规则均完全相同。在功能的定义上没有任何差异，可以互换使用。
若考虑到数据库的兼容性，建议使用decimal(p,s)。

7.1.1.3 字符数据类型

字符数据类型也是SQL Server中最常见的数据类型之一，用来存储各种字母、数字符号和特殊符号，在使用字符数据类型时，需要在数据两端加上英文的单引号或双引号。

数据类型	描述
char(n)	固定长度的字符串。最多8000字符
varchar(n)	可变长度的字符串。最多8000字符
varchar(max)	可变长度的字符串。最多1073741824字符
nchar	固定长度的unicode字符串。最多4000字符
nvarchar	可变长度的unicode字符串。最多4000字符
nvarchar(max)	可变长度的unicode字符串。最多536870912字符

7.1.1.4 日期和时间数据类型

日期和时间数据类型用于存储日期类型和时间类型的组合数据

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

7.1.1.5 图像和文本数据类型

图像和文本数据类型用于存储大量的字符及二进制数据

数据类型	描述
text	可变长度的字符串，最多2GB文本数据
ntext	可变长度的字符串，最多2GB文本数据
image	可变长度的二进制字符串，最多2GB

7.1.1.6 货币数据类型

货币数据类型用于存储货币值，使用时在数据前加上货币符号，不加货币符号的情况下默认“￥”

数据类型	描述
money	介于 -922337203685477.5808 ~ 922337203685477.5808的货币数据
smallmoney	介于 -214748.3648 ~ 214748.3648的货币数据

7.1.1.7 二进制数据类型

二进制数据类型用于存储二进制数

数据类型	描述
binary(n)	固定长度的二进制字符串，最多8000B
varbinary	可变长度的二进制字符串，最多8000B
varbinary(max)	可变长度的二进制字符串，最多2GB

7.1.1.8 其他数据类型

除上述介绍的数据类型外，SQL Server还提供大量其他数据类型

数据类型	描述
bit	位数据类型，只取0或1为值，长度1字节。 bit值经常当作逻辑值用于判断true（1）和false（0） 输入非零值时系统将其换为1
timestamp	时间戳数据类型，timestamp的数据类型为rowversion数据类型的同义词，提供数据库范围内的唯一值，反映数据修改的相对顺序，是一个单调上升的计数器，此列的值被自动更新
sql_variant	用于存储文本、图像数据和timestamp数据外的其他任何合法的SQL Server数据，可以方便SQL Server的开发工作
uniqueidentifier	存储全局唯一标识符（GUID）
xml	存储xml数据的数据类型。可以在列中或者xml类型的变量中存储xml实例。 存储的xml数据类型表示实例大小不能超过2GB
cursor	游标数据类型，该类型类似于数据表，其保存的数据中包含行和列的值，但是没有索引，游标用来建立一个数据的数据集，每次处理一行数据
table	用于存储对表或者视图处理后的结果集。 这种新的数据类型使得变量可以存储一张表，从而使函数或过程返回查询结果更加方便、快捷

7.1.2 自定义数据类型

自定义数据类型并不是真正的数据类型，它只是提供了一种加强数据库内部元素和基本数据类型之间一致性的机制，通过使用用户自定义数据类型能够简化对常用规则和默认值的管理。

用户创建自定义数据类型的方法有两种：SSMS和SQL语句

7.1.2.1 在SSMS中的“对象资源管理器”中创建

1、打开SSMS界面，在“对象资源管理器”中选择需要创建数据类型的数据库；

2、依次打开mydatabase➡“可编程性”➡“类型”结点，右击“用户定义数据类型”结点，在弹出的快捷菜单中选择“新建用户定义数据类型”命令；

3、打开“新建用户定义数据类型”窗口，在“名称”文本框中输入需要定义的数据类型名称【remark】，表示存储一个简介数据值，在“数据类型”下拉列表框中选择char的系统数据类型，“长度”指定为8000，如果用户希望该类型的字段值为空的话，可以选择“允许null值”复选框，其他参数不做更改；

4、单击“确定”按钮，完成用户自定义数据类型的创建，即可看到新创建的自定义数据类型

7.1.2.2 使用SQL语句创建

在SQL Server中，还可以使用系统数据类型sp_addtype来创建用户自定义数据类型。其语法结构如下：

sp_addtype [@typename=] type,
[@phystype=] system_data_type [,[@nulltype=] 'null_type']

各参数的含义如下：

• type：用于指定用户定义的数据类型的名称。
• system_data_type：用于指定相应的系统提供的数据类型的名称及定义。注意，未能使用timestamp数据类型，当所使用的系统数据类型有额外说明时，需要用引号括起来。
• null_type：用于指定用户自定义的数据类型的null属性，其值可以为“null”“not null”或“nonull”。默认时与系统默认的null属性相同。用户自定义的数据类型的名称在数据库中应该是唯一的。

例：在mydatabase数据库中，创建用来存储邮政编号信息的“postcode”用户自定义数据类型

sp_addtype postcode,'char(128)','not null'

7.1.3 删除自定义数据类型

当不再需要用户自定义的数据类型时，可以将其删除，删除的方法跟创建一样，可以通过SSMS或SQL语句来实现。

7.1.3.1 在“对象资源管理器”中删除

在“对岸个资源管理器”中选择需要删除的数据类型，然后右击鼠标，在弹出的菜单中选择“删除”命令；

打开“删除对象”窗口，单击“确定”按钮，即可删除自定义数据类型；

7.1.3.2 使用SQL语句删除

使用sp_droptype来删除自定义数据类型，该存储过程从systypes删除别名数据类型，语法如下：

sp_sroptype type  --type为用户定义的数据类型

例：在mydatabase数据库中，删除remark自定义数据类型。

sp_droptype remark

💡注意：数据库中正在使用的用户定义数据类型不能被删除。