SQL Server中的数据类型

by admin on 2019年10月7日

参考 SQL Server 2012编程入门经典(第4版)

SQL Server 自带的数据类型

 

 

 

 

 

SQL Server 2005 中的数据类型归纳为下列类别:

前言

图片 1

图片 1

整型:

 图片 3

 

 

精确数字 

bigint

decimal

int

numeric

smallint

money

tinyint

smallmoney

bit

 

 

前面几篇文章我们讲解了索引有关知识,这一节我们再继续我们下面内容讲解,简短的内容,深入的理解。

货币

图片 4

 

近似数字

float

real

 

数据类型

近似小数

  图片 5

 

日期和时间

datetime

smalldatetime

 

SQL
Server支持两种字符数据类型,一种是常规,另外一种则是Unicode。常规数据类型包括CHAR和VARCHAR,Unicode数据类型包括NCAHR和NVARCHAR。常规字符的每个字符使用1个字节存储,而Unicode数据的每个字符要求2个字节。常规字符列限制为仅仅只针对于英语,而Unicode则是针对于多种语言。两种字符数据类型的文本表示方式也不相同,在表示常规字符文本时,只需要使用单引号,比如’Hello,my
name is JeffckyWang,I’m from
cnblogs’,而对于Unicode字符文本时,需要指定字符N作为前缀,即N‘Hello,my
name is JeffckyWang,I’m from cnblogs’。

日期/时间

字符串

char

text

varchar

 

 

名称中没有VAR元素的任何数据类型(CHAR、NCHAR)具有固定长度,即SQL
Server按照列定义大小保留行空间,而不是按照字符中的实际字符保留空间。比如某列定义大小为CHAR(25),则SQL
Server在该行保留25个字符的空间,而不管存储字符串的长度。

 

图片 6

 

Unicode字符串

nchar

ntext

nvarchar

 

 

名称中含有VAR元素的数据类型(VARCHAR、NVARCHAR)具有可变长度,即SQL
Server根据存储需要,在行中使用尽可能多的存储空间存储字符串,同时外加两个额外的字节偏移数据。例如,如果将某列定义为VARCHAR(25),此时支持的最大字符数为25,但实际上按照字符串中实际字符确定存储量。-摘抄自SQL
Server 2012 T-SQL基础教程。

 特殊数字

图片 7

图片 8

 

二进制字符串

binary

image

varbinary

 

 

这里关于Unicode字符数据类型我们需要重点理解下。我们先创建一个表,如下:

 字符

图片 9

图片 10

 

其他数据类型

cursor

timestamp

sql_variant

uniqueidentifier

table

 

 

1、精确数字

   I) 整型数据类型int、bigint、smallint 和 tinyint

数据类型

范围

存储

bigint

-2^63 (-9,223,372,036,854,775,808) 到 2^63-1 (9,223,372,036,854,775,807)

8 字节

int

-2^31 (-2,147,483,648) 到 2^31-1 (2,147,483,647)

4 字节

smallint

-2^15 (-32,768) 到 2^15-1 (32,767)

2 字节

tinyint

0 到 255

1 字节

 II)
bit  可以取值为 1、0 或 NULL 的整数数据类型。如果表中的列为 8
bit 或更少,则这些列作为 1 个字节存储。如果列为 9 到 16 bit,则这些列作为 2 个字节存储,以此类推。字符串值 TRUE 和 FALSE 可以转换为以下 bit 值:TRUE 转换为 1,FALSE 转换为 0。

III) decimal 和 numeric,两者都是带固定精度和小数位数的数值数据类型。decimal[ (p[ , s]
)] 和 numeric[
(p[ , s]
)] 两者都是固定精度和小数位数。使用最大精度时,有效值从 – 10^38 +1 到 10^38 – 1。numeric 在功能上等价于 decimal。p(精度)最多可以存储的十进制数字的总位数,包括小数点左边和右边的位数。该精度必须是从 1 到最大精度 38 之间的值。默认精度为 18。s(小数位数)小数点右边可以存储的十进制数字的最大位数。小数位数必须是从 0 到 p 之间的值。仅在指定精度后才可以指定小数位数。默认的小数位数为 0;因此,0 <= s <=
p。最大存储大小基于精度而变化。

Ⅳ) money 和 smallmoney,两者是 代表货币或货币值的数据类型。

数据类型

范围

存储

money

-922,337,203,685,477.5808 到 922,337,203,685,477.5807

8 字节

smallmoney

-214,748.3648 到 214,748.3647

4 字节

money 和 smallmoney 数据类型精确到它们所代表的货币单位的万分之一。

2、近似数字 float 和 real,两者用于表示浮点数值数据的大致数值数据类型。浮点数据为近似值;因此,并非数据类型范围内的所有值都能精确地表示。

数据类型

范围

存储

float

-1.79E + 308 至 -2.23E – 308、0 以及 2.23E – 308 至 1.79E + 308

取决于 n 的值

real

-3.40E + 38 至 -1.18E – 38、0 以及 1.18E – 38 至 3.40E + 38

4 字节

float [ ( n )
] 其中 n 为用于存储 float 数值尾数的位数,以科学记数法表示,因此可以确定精度和存储大小。如果指定了 n,则它必须是介于 1 和 53 之间的某个值。n 的默认值为 53。

3、日期和时间  datetime 和 smalldatetime 两者用于表示某天的日期和时间的数据类型。

数据类型

范围

精确度

datetime

1753 年 1 月 1 日到 9999 年 12 月 31 日

3.33 毫秒

smalldatetime

1900 年 1 月 1 日到 2079 年 6 月 6 日

1 分钟

datetime用两个 4 字节的整数存储,第一个 4 字节存储“基础日期”(即 1900 年 1 月 1 日)之前或之后的天数。基础日期是系统参照日期。另外一个 4 字节存储天的时间(以午夜后经过的毫秒数表示)。

smalldatetime 数据类型存储天的日期和时间,但精确度低于 datetime。数据库引擎 将 smalldatetime 值存储为两个 2 字节的整数。第一个 2 字节存储 1900 年 1 月 1 日后的天数。另外一个 2 字节存储午夜后经过的分钟数。

4、字符串char [ ( n ) ],varchar [ ( n | max )
],text

char [ ( n )
]可以存储字母数字值,固定长度,非 Unicode 字符数据,长度为 n 个字节。n 的取值范围为 1 至 8,000,存储大小是 n 个字节。如果未在数据定义或变量声明语句中指定 n,则默认长度为 1。如果在使用 CAST 和 CONVERT 函数时未指定 n,则默认长度为 30。

varchar [ ( n | max )
] 可以存储字母数字值,可变长度,非 Unicode 字符数据。n 的取值范围为 1 至 8,000。max 指示最大存储大小是 2^31-1 个字节。存储大小是输入数据的实际长度加 2 个字节。所输入数据的长度可以为 0 个字符。如果未在数据定义或变量声明语句中指定 n,则默认长度为 1。如果在使用 CAST 和 CONVERT 函数时未指定 n,则默认长度为 30。

text服务器代码页中长度可变的非 Unicode 数据,最大长度为 2^31-1
(2,147,483,647) 个字符。当服务器代码页使用双字节字符时,存储仍是 2,147,483,647 字节。根据字符串,存储大小可能小于 2,147,483,647 字节。

备注:在 Microsoft SQL
Server 的未来版本中将删除 ntext、text 和 image 数据类型。请避免在新开发工作中使用这些数据类型,并考虑修改当前使用这些数据类型的应用程序。请改用 nvarchar(max)、varchar(max) 和 varbinary(max)。

5、Unicode字符串  nchar [ ( n ) ],nvarchar [ ( n | max )
],ntext

nchar [ ( n )
] ,n 个字符的固定长度的 Unicode 字符数据。n 值必须在 1 到 4,000 之间(含)。存储大小为两倍 n 字节。如果没有在数据定义或变量声明语句中指定 n,则默认长度为 1。如果没有使用 CAST 函数指定 n,则默认长度为 30。

nvarchar [ ( n | max )
],可变长度 Unicode 字符数据。n 值在 1 到 4,000 之间(含)。max 指示最大存储大小为 2^31-1 字节。存储大小是所输入字符个数的两倍 +
2 个字节。所输入数据的长度可以为 0 个字符。如果没有在数据定义或变量声明语句中指定 n,则默认长度为 1。如果没有使用 CAST 函数指定 n,则默认长度为 30。

ntext是长度可变的 Unicode 数据,最大长度为 2^30 – 1
(1,073,741,823) 个字符。存储大小是所输入字符个数的两倍(以字节为单位)。

备注:如果站点支持多语言,请考虑使用 Unicode nchar 或 nvarchar 数据类型,以最大限度地消除字符转换问题。

6、二进制字符串  binary [ ( n )
],varbinary [ ( n
| max ) ],image

binary [ ( n )
],varbinary [ ( n
| max ) ]两者是固定长度或可变长度的 Binary 数据类型。binary [ ( n )
] 长度为 n 字节的固定长度二进制数据,其中 n 是从 1 到 8,000 的值。存储大小为 n 字节。如果未在数据定义或变量声明语句中指定 n,则默认长度为 1。如果未使用 CAST 函数指定 n,则默认长度为 30。varbinary [ ( n | max )
] 可变长度二进制数据。n 可以取从 1 到 8,000 的值。max 指示最大的存储大小为 2^31-1 字节。存储大小为所输入数据的实际长度 +
2 个字节。所输入数据的长度可以是 0 字节。如果未在数据定义或变量声明语句中指定 n,则默认长度为1。如果未使用 CAST 函数指定 n,则默认长度为 30。

   Image长度可变的二进制数据,从 0 到 2^31-1
(2,147,483,647) 个字节。

7、其他数据类型

  Ⅰ) 这是变量或存储过程 OUTPUT 参数的一种数据类型,这些参数包含对游标的引用。使用 cursor 数据类型创建的变量可以为空。

有些操作可以引用那些带有 cursor 数据类型的变量和参数,这些操作包括:

DECLARE
@local_variable 和 SET
@local_variable 语句。

OPEN、FETCH、CLOSE 及 DEALLOCATE 游标语句。

存储过程输出参数。

CURSOR_STATUS 函数。

sp_cursor_list、sp_describe_cursor、sp_describe_cursor_tables 以及 sp_describe_cursor_columns 系统存储过程。

备注:对于 CREATE
TABLE 语句中的列,不能使用 cursor 数据类型。

Ⅱ) sql_variant

一种数据类型,用于存储 SQL
Server 2005 支持的各种数据类型(不包括 text、ntext、image、timestamp 和 sql_variant)的值。

sql_variant 可以用在列、参数、变量和用户定义函数的返回值中。sql_variant 使这些数据库对象能够支持其他数据类型的值。

Ⅲ)table

一种特殊的数据类型,用于存储结果集以进行后续处理。table 主要用于临时存储一组行,这些行是作为表值函数的结果集返回的。可将函数和变量声明为 table 类型。table 变量可用于函数、存储过程和批处理中。

Ⅳ) timestamp

公开数据库中自动生成的唯一二进制数字的数据类型。timestamp 通常用作给表行加版本戳的机制。存储大小为 8 个字节。

每个数据库都有一个计数器,当对数据库中包含 timestamp 列的表执行插入或更新操作时,该计数器值就会增加。该计数器是数据库时间戳。这可以跟踪数据库内的相对时间,而不是时钟相关联的实际时间。一个表只能有一个 timestamp 列。每次修改或插入包含 timestamp 列的行时,就会在 timestamp 列中插入增量数据库时间戳值。这一属性使 timestamp 列不适合作为键使用,尤其是不能作为主键使用。对行的任何更新都会更改 timestamp 值,从而更改键值。如果该列属于主键,那么旧的键值将无效,进而引用该旧值的外键也将不再有效。如果该表在动态游标中引用,则所有更新均会更改游标中行的位置。如果该列属于索引键,则对数据行的所有更新还将导致索引更新。

使用某一行中的 timestamp 列可以很容易地确定该行中的任何值自上次读取以后是否发生了更改。如果对行进行了更改,就会更新该时间戳值。如果没有对行进行更改,则该时间戳值将与以前读取该行时的时间戳值一致。rowversion 的数据类型为 timestamp 数据类型的同义词,并具有数据类型同义词的行为。在 DDL 语句,请尽量使用 rowversion 而不是 timestamp。如果不指定列名,则 Microsoft SQL Server 2005
Database Engine 将生成 timestamp 列名;但 rowversion 同义词不具有这样的行为。在使用 rowversion 时,必须指定列名。不可为空的 timestamp 列在语义上等同于 binary(8) 列。可为空的 timestamp 列在语义上等同于 varbinary(8) 列。

Ⅴ) uniqueidentifier

16 字节 GUID。uniqueidentifier 数据类型的列或局部变量可通过以下方式初始化为一个值:

使用 NEWID 函数。

从 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx 形式的字符串常量转换,其中,每个 x 是一个在 0-9 或 a-f 范围内的十六进制数字。例如,6F9619FF-8B86-D011-B42D-00C04FC964FF 为有效 uniqueidentifier 值。

比较运算符可与 uniqueidentifier 值一起使用。不过,排序不是通过比较两个值的位模式来实现的。可针对 uniqueidentifier 值执行的运算只有比较运算(=、<>、<、>、<=、>=)以及检查是否为 NULL(IS NULL 和 IS NOT
NULL)。不能使用其他算术运算符。除 IDENTITY 之外的所有列约束和属性均可对 uniqueidentifier 数据类型使用。

具有更新订阅的合并复制和事务复制使用 uniqueidentifier 列来确保在表的多个副本中唯一地标识行。

Ⅵ) xml

存储 XML 数据的数据类型。可以在列中或者 xml 类型的变量中存储 xml 实例。存储的 xml 数据类型表示实例大小不能超过 2 GB。

CREATE TABLE UnicodeType
(
 firstname VARCHAR(5) NOT NULL,
 lastname NVARCHAR(5) NOT NULL
);

Unicode

图片 11

 

此时我们手动插入数据,正常插入,如下:

二进制

图片 12

 

INSERT dbo.UnicodeType
  ( firstname, lastname )
VALUES ( '11111', -- firstname - varchar(5)
   N'啊的发个好' -- lastname - nvarchar(5)
   )

 其他

图片 13

图片 14

 

字符都完全插入表中,如下:

图片 15

此时我们将firstname,插入五个中文试试如下:

INSERT dbo.UnicodeType
  ( firstname, lastname )
VALUES ( '达得到让人', -- firstname - varchar(5)
   N'达得到让人' -- lastname - nvarchar(5)
   )

此时出现如下结果:

图片 16

也就是说在常规字符类型如上述VARVHAR中定义为五个字符,此时我们插入五个中文字符则会被截取,当然也插入不进去。因为上述已经明确讲了1个非英语字符串相当于两个字节,此时中文所占用的是十个字节,而此时VARCHAR才五个字符,所以出现警告。我们再来将firstname插入两个中文两个英文或者数字看看

INSERT dbo.UnicodeType
  ( firstname, lastname )
VALUES ( '达得1', -- firstname - varchar(5)
   N'达得到让人' -- lastname - nvarchar(5)
   )

此时插入进去为出现警告,因为此时两个中文字符即四个字节加上一个数字字节刚好五个字节,所以能正常插入,我们再来看看lastname,由上知,既然英文或者数字被当做一个字节,那么我们对lastname插入四个中文字符和两个英文字节刚好十个字节应该是好使的。我们看看:

INSERT dbo.UnicodeType
  ( firstname, lastname )
VALUES ( '达得1', -- firstname - varchar(5)
   N'达得到让ab' -- lastname - nvarchar(5)
   )

oh,shit,此时居然出错了,如下:

图片 17

我们上述分析的不是有理有据么,难道这里英文不是占用一个字节么,我们插入一个英文试试。

INSERT dbo.UnicodeType
  ( firstname, lastname )
VALUES ( '达得1', -- firstname - varchar(5)
   N'达得到让b' -- lastname - nvarchar(5)
   )

结果正确了,实践是检验真理的唯一标准,从这里我们可以看出:在常规字符中,一个中文会当做是两个字节来使用,一个英文会当做是一个字节使用,但是在Unicode中,一个中文也是会当做两个字节来使用,但是一个英文也会当做是两个字节来使用。至此我们可以得出结论,个人一直以为在Unicode中,将英文是作为一个字节存储,见识短啊。

常规字符和Unicode中一个中文字符用两个字节存储,而对英文,常规字符用一个字节存储,而Unicode依然是用两个字节存储。

字符串函数

对字符串操作的函数有SUBSTRING、LEFT、RIGHT、CHARINDEX、PATINDEX、REPLACE、REPICATE、STUFF、UPPER、LOWER、RTRIM、LTRIM、FORMAT。对于简单的函数我们略过,下面我们来讲讲几个需要注意的地方。

LEN与DATALENGTH比较

我们首先创建如下测试表

CREATE TABLE StringFun
(
 firststr VARCHAR(max) NOT NULL,
 secondstr TEXT NOT NULL
);

我们插入测试数据

INSERT dbo.StringFun
  ( firststr, secondstr )
VALUES ( '我是JeffckyWang,我来自于博客园,专注于.NET技术', -- firststr - varchar(max)
   '我是JeffckyWang,我来自于博客园,专注于.NET技术' -- secondstr - text
   )

我们首先利用LEN函数来返回firststr和secondstr的字符串长度大小

SELECT LEN(firststr) AS VARCAHRFieldSize 
FROM dbo.StringFun
SELECT LEN(secondstr) AS TEXTFieldSize 
FROM dbo.StringFun

图片 18

好极了,出错了。LEN函数无法对TEXT进行操作。我们接着往下看。

SELECT DATALENGTH(firststr) AS VARCAHRFieldSize 
FROM dbo.StringFun
SELECT DATALENGTH(secondstr) AS TEXTFieldSize 
FROM dbo.StringFun

图片 19

此时未报错误,结果显示为47个字节大小。
既然LEN对文本无效,我们不对文本操作就是。

SELECT LEN(firststr) AS VARCAHRFieldSize 
FROM dbo.StringFun
SELECT DATALENGTH(secondstr) AS TEXTFieldSize 
FROM dbo.StringFun

图片 20

此时类型为VARCAHR的firststr字节大小却为31,为何,看到这里我们想必恍然大悟,在上述我们讲到常规字符会对中文以一个字符两个字节大小存储,但是这里实际上返回的是实际字符大小,当然一个是存储,一个是检索,还是有点不同,同时我们也不会将中文存储到VARCHAR中。到这里我们可以得出结论。

结论:DATALENGTH函数是针对于TEXT,而LEN是针对于VARCHAR,对TEXT无效会报错。

到这里我们还有一个特殊值未进行处理,那就是NULL。那么问题来了,LEN和DATALENGTH对NULL,它的长度大小是多少呢,是0还是不是0尼?

是我们来测试下:

DECLARE @MyVar VARCHAR(10)
SET @MyVar = NULL
IF (LEN(@MyVar) = 0)
PRINT 'LEN of NULL is 0'
ELSE
PRINT 'LEN of NULL is NULL'

图片 21

我们上述得到的结果是LEN of NULL is NULL,DATALENGTH就不再演示了。

结论:LEN和DATALENGTH对于NULL计算的结果就是NULL。

我们再来看看二者差异的一个小地方:

SELECT LEN('JeffckyWang ') AS 'LEN'
SELECT DATALENGTH('JeffckyWang ') AS 'DATALENGTH'

图片 22

结论:LEN会删除尾随空格,而DATALENGTH不会

CHARINDEX与PATINDEX比较

CHARINDEX和PATINDEX字符串函数都是查询返回指定匹配字符串的开始位置。

我们先查询一个字符串,此字符串在表中存在,如下:

USE AdventureWorks2012;
GO
SELECT CHARINDEX('Worn', DocumentSummary) AS 'CHARINDEX'
FROM Production.Document
WHERE ChangeNumber = 55;
GO
SELECT PATINDEX('Worn', DocumentSummary) AS 'PATINDEX'
FROM Production.Document
WHERE ChangeNumber = 55;

图片 23

为何CHARINDEX函数查找到了,而PATINDEX没有查询到呢?此时就说说二者的区别,二者都有两个参数,第二个参数都是要匹配的字符串,但是PATINDEX函数必须在需要匹配的字符串之前或者之后添加百分号即通配符,而CHARINDEX函数则不需要。如下即可:

USE AdventureWorks2012;
GO
SELECT CHARINDEX('Worn', DocumentSummary) AS 'CHARINDEX'
FROM Production.Document
WHERE ChangeNumber = 55;
GO
SELECT PATINDEX('%Worn%', DocumentSummary) AS 'PATINDEX'
FROM Production.Document
WHERE ChangeNumber = 55;

图片 24

结论:PATINDEX匹配字符串必须在字符串前面或者后面或者前后添加通配符,而CHARINDEX无需添加。

总结

本节我们主要讲解了SQL中的数据类型以及几个需要注意的地方,简短的内容,深入的理解,我们下节再会。

以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,如果有疑问大家可以留言交流,同时也希望多多支持脚本之家!

您可能感兴趣的文章:

  • 详解MySQL数据类型int(M)中M的含义
  • mysql存储引擎和数据类型(二)
  • Java数据类型与MySql数据类型对照表
  • SQL Server数据类型转换方法
  • SQL
    Server比较常见数据类型详解
  • SQLite教程(七):数据类型详解
  • SQL
    Server数据类型char、nchar、varchar、nvarchar的区别浅析
  • sql使用cast进行数据类型转换示例
  • SQL2005中char nchar varchar
    nvarchar数据类型的区别和使用环境讲解
  • SQL的常用数据类型列表详解

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图