一、数据存储单位
1.位(bit)
数据存储的最小单位。每个二进制数字0或者1就是个位。
2.字节(B、byte)
- 1 byte(字节) = 8 bit(位) 8个位构成一个字节
- 1 KB = 1024 B (2^10B)
- 1 MB = 1024 KB (2^20B)
- 1 GB = 1024 MB (2^30B)
- 1 TB = 1024 GB (2^40B)
3.字符
单个字母、汉字、特殊符号(a、A、中、+、*、の…)均表示一个字符。
一般 utf-8 编码下,一个汉字字符占用 3 个字节;
一般 gbk 编码下,一个汉字字符占用 2 个 字节。
一、系统数据类型
(一)精确数字
1.整数
数据类型 | 范围 | 大小 |
---|---|---|
bigint | 从 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 的整型数据(所有数字) | 8bit |
int | 从 -2^31 (-2,147,483,648) 到 2^31 - 1 (2,147,483,647) 的整型数据(所有数字) | 4bit |
smallint | 从 -2^15 (-32,768) 到 2^15 - 1 (32,767) 的整数数据 | 2bit |
tinyint | 从 0 到 255 的整数数据 | 1bit |
bit | 整型数据 1、0 或 NULL | 1bit |
2.小数
- 精度:数中的数字个数。123.45的精度是5
- 小数位数:数中小数点右边的数字个数。123.45小数位数是2
- 数字数据类型的长度:存储此数据类型所占用的字节数
decimal[(p[, s])] 和 numeric[(p[, s])]
使用最大精度时,有效值从 - 10^38 +1 到 10^38 - 1。
1° p(精度)
指定小数点左边和右边可以存储的十进制数字的最大个数。精度必须是从 1 到最大精度之间的值。最大精度为 38。
2° s(小数位数)
指定小数点右边可以存储的十进制数字的最大个数。小数位数必须是从 0 到 p 之间的值。默认小数位数是 0,因而 0 <= s <= p。最大存储大小基于精度而变化。
精度 | 存储字节数 |
---|---|
1 - 9 | 5 |
10 - 19 | 9 |
20 - 28 | 13 |
29 - 38 | 17 |
3.货币
数据类型 | 范围 | 大小 |
---|---|---|
money | 货币数据值介于 -2^63 (-922,337,203,685,477.5808) 与 2^63 - 1 (+922,337,203,685,477.5807) 之间,精确到货币单位的千分之十 | 8bit |
smallmoney | 货币数据值介于 -214,748.3648 与 +214.748,3647 之间,精确到货币单位的千分之十 | 4bit |
(二)近似数字
用于表示浮点数字数据的近似数字数据类型。浮点数据为近似值;并非数据类型范围内的所有数据都能精确地表示。
1.float [ ( n ) ]
从 - 1.79E + 308 到 1.79E + 308 之间的浮点数字数据。n 为用于存储科学记数法 float 数尾数的位数,同时指示其精度和存储大小。n 必须为从 1 到 53 之间的值。
n所在范围 | 精度 | 存储大小 |
---|---|---|
1 - 24 | 7位数 | 4字节 |
25 - 53 | 15位数 | 8字节 |
2.real
从 -3.40E + 38 到 3.40E + 38 的浮点精度数字。存储大小为 4 字节。
(三)日期和时间数据
1.datetime
从 1753 年 1 月 1 日到 9999 年 12 月 31 日的日期和时间数据,精确度为百分之三秒(等于 3.33 毫秒或 0.00333 秒)。
注:Microsoft SQL Server 用两个 4 字节的整数内部存储 datetime 数据类型的值。第一个 4 字节存储 base date(即 1900 年 1 月 1 日)之前或之后的天数。基础日期是系统参考日期。不允许早于 1753 年 1 月 1 日的 datetime 值。另外一个 4 字节存储以午夜后毫秒数所代表的每天的时间。
2.smalldatetime
从 1900 年 1 月 1 日到 2079 年 6 月 6 日的日期和时间数据精确到分钟。
- 不高于29.998 秒的 smalldatetime 值向下舍入为最接近的分钟
- 超过29.999 秒的 smalldatetime 值向上舍入为最接近的分钟
--returns time as 12:35
SELECT CAST('2000-05-08 12:35:29.998' AS smalldatetime)
GO
--returns time as 12:36
SELECT CAST('2000-05-08 12:35:29.999' AS smalldatetime)
GO
注:smalldatetime 数据类型存储日期和每天的时间,但精确度低于 datetime。SQL Server 将 smalldatetime 的值存储为两个 2 字节的整数。第一个 2 字节存储 1900 年 1 月 1 日后的天数。另外一个 2 字节存储午夜后的分钟数。日期范围从1900 年 1 月 1 日到 2079 年 6 月 6 日,精确到分钟。
(四)二进制数据
1.binary [ ( n ) ]
固定长度的 n 个字节二进制数据。N 必须从 1 到 8,000。存储空间大小为 n+4 字节。
2.varbinary [ ( n ) ]
n 个字节可变二进制数据。n 必须从 1 到 8,000。存储空间大小为实际输入数据长度 +4 个字节,而不是 n 个字节。输入的数据长度可能为 0 字节。
注:如果在数据定义或变量声明语句中没有指定 n,默认长度为 1。如果没有用 CAST 函数指定 n,默认长度为 30。
当列数据项大小一致时应使用 binary。
当列数据项大小不一致时应使用 varbinary。
3.image
可变长度的二进制数据,其最大长度为 2^31 - 1 (2,147,483,647) 个字节。
(五)字符数据
1.char[(n)]
长度为 n 个字节的固定长度且非 Unicode 的字符数据。n 必须是一个介于 1 和 8,000 之间的数值。存储大小为 n 个字节。
2.varchar[(n)]
长度为 n 个字节的可变长度且非 Unicode 的字符数据。n 可以是一个介于 1 和 8,000 之间的数值。存储大小为输入数据的字节的实际长度,而不是 n 个字节。所输入的数据字符长度可以为零。
3.text
可变长度的非 Unicode 数据,最大长度为 2^31 - 1 (2,147,483,647) 个字符。
(六)Unicode 数据
双字节
1.nchar(n)
固定长度的 Unicode 数据,最大长度为 4,000 个字符。
2.nvarchar(n)
可变长度 Unicode 数据,其最大长度为 4,000 字符。sysname 是系统提供用户定义的数据类型,在功能上等同于 nvarchar(128),用于引用数据库对象名。
3.ntext
可变长度 Unicode 数据,其最大长度为 2^30 - 1 (1,073,741,823) 个字符。
二、用户自定义数据类型
用户定义的数据类型基于在 Microsoft SQL Server 中提供的数据类型。当几个表中必须存储同一种数据类型时,并且为保证这些列有相同的数据类型、长度和可空性时,可以使用用户定义的数据类型。
(一)创建用户自定义数据类型
1. sp_addtype
创建用户定义的数据类型可以使用系统存储过程 sp_addtype
(1)语法
sp_addtype [ @typename = ] type,
[ @phystype = ] system_data_type
[ , [ @nulltype = ] ‘null_type’ ]
[ , [ @owner = ] ‘owner_name’ ]
(2)参数
1° [@typename =] type
用户定义的数据类型的名称。数据类型名称必须遵照标识符的规则,而且在每个数据库中必须是唯一的。type 的数据类型为 sysname,没有默认值。
2° [@phystype =] system_data_type
是用户定义的数据类型所基于的物理数据类型或 Microsoft® SQL Server™ 提供的数据类型(decimal、int 等等)。system_data_type 的数据类型是 sysname,没有默认值。
3° [@nulltype =] ‘null_type’
指明用户定义的数据类型处理空值的方式。null_type 的数据类型为 varchar(8),默认值为 NULL,并且必须用单引号引起来(‘NULL’、‘NOT NULL’ 或 ‘NONULL’)。如果没有用 sp_addtype 显式定义 null_type,则将其设置为当前默认的为空性。使用 GETANSINULL 系统函数可确定当前默认的为空性,可以使用 SET 语句或 sp_dboption 对该为空性进行调整。应显式定义为空性。
4° [@owner =] ‘owner_name’
指定新数据类型的创建者或所有者。owner_name 的数据类型为 sysname。当没有指定时,owner_name 为当前用户。
(3)示例
例:为数据库teachingDataA创建一个用户定义的数据类型Sname,该数据类型为基于系统数据类型变长为8的字符,且不允许为空。
use teachingDataA
Exec sp_addtype Sname, 'Varchar(8)','Not Null'
注:varchar(8) 由单引号引了起来,这是因为它包含了标点符号(圆括号)。
2.CREATE TYPE
在SQL-Server 2005之后的后续版本中,sp_addtype将不再可用,取而代之的是CREATE TYPE语句。
(1)语法
CREATE TYPE type_name
{
FROM base_type
[ ( precision [, scale ] ) ]
[ NULL | NOT NULL ]
}
(2)参数
typename:用户自定义的数据类型的名称。
base_type:表示建立的数据类型所基于的由SQL Server提供的数据类型。当建立decimal或numeric类型时,需要用precision来指定总位数,用scale来指定小数位数。
NULL | NOT NULL:指定此类型是否可容纳空值。如果未指定,则默认为NULL。
(3)示例
例:为数据库teachingDataB自定义一个工作部门myDept的数据类型为varchar(20),允许为空。
CREATE TYPE myDept FROM varchar(20)
3.界面设置
在Microsoft SQL Server Management Studio的对象资料管理器中展开【可编程性】|【类型】,右击【用户定义数据类型】,选择【新建用户定义数据类型】命令,打开【新建用户定义数据类型】,输入自定义的数据类型名,选择数据类型,设置长度设定,确定是否允许空值(通过勾选【允许空值】进行设置),完成后单击【确定】。
(二)删除用户自定义数据类型
1.sp_droptype
当用户定义的数据类型不需要时,可以用命令sp_droptype将其删除。
exec sp_droptype {’type_name’}
2.界面设置
在Microsoft SQL Server Management Studio的对象资料管理器中展开【可编程性】|【类型】|【用户定义数据类型】,右击要删除的数据类型,选择“删除”。
三、数据类型的转换
(一)数据类型的优先顺序
当两个不同数据类型的表达式用运算符组合后,数据类型的优先顺序规则确定哪种数据类型要向另一种转换。优先顺序低的数据类型向优先顺序高的数据类型转换。如果此转换不是所支持的固有转换,则返回错误。当两个操作数表达式有相同的数据类型时,运算的结果就为那种数据类型。
sql_variant > datetime > smalldatetime > float > real > decimal > money > smallmoney > bigint > int > smallint > tinyint > bit > ntext > text > image > timestamp > uniqueidentifier > nvarchar > nchar > varchar > char > varbinary > binary
(二)排序规则的优先顺序
排序规则的优先顺序规则只适用于字符串数据类型:char、varchar、text、nchar、nvarchar 和 ntext。
操作数强制标志 | 显性X | 隐性X | 强制默认 | 无排序规则 |
---|---|---|---|---|
显性Y | 生成错误 | 结果为显性Y | 结果为显性Y | 结果为显性Y |
隐性Y | 结果为显性X | 结果为无排序规则 | 结果为隐性Y | 结果为无排序规则 |
强制默认 | 结果为显性X | 结果为隐性X | 结果为强制默认 | 结果为无排序规则 |
无排序规则 | 结果为显性X | 结果为无排序规则 | 结果为无排序规则 | 结果为无排序规则 |