您的位置:首页技术文章
文章详情页

SQL Server中的数据类型详解

【字号: 日期:2023-03-06 14:25:25浏览:32作者:猪猪
目录
  • 哪些对象需要数据类型
  • 一、 整数数据类型
    • 1、bit
    • 2、tinyint
    • 3、smallint
    • 4、int (integer)
    • 5、bigint
  • 二、 浮点数据类型
    • 1、real: 近似数值型
    • 2、float[(n)]:近似数值型
    • 3、decimal[p [s] ]
    • 4、numeric[p [s] ]
    • 5、smallMoney货币型
    • 6、money货币型
  • 三、字符数据类型
    • 1、char[(n)]
    • 2、nchar[(n)]
    • 3、varchar[(n| max )]
    • 4、nvarchar[(n| max )]
    • 5、text
    • 6 、ntext
  • 四、 日期和时间数据类型
    • 1、datetime :日期和時間(1753-1-1 日到 9999-12-31)
    • 2、dateTime2 (n):高精度的datetime类型(0001-01-01 到 9999-12-31)
    • 3、smalldatetime  :精确到分钟,日期和時間。(1900-01-01 到 2079-06-06)不推荐。
    • 4、date:只表示日期:年-月-日(0001-01-01 到 9999-12-31)
    • 5、time:只表示时间:hh:mm:ss(00:00:00.0000000 到 23:59:59.9999999)
    • 6、datetimeOffset:时间部分增加时区表示
  • 五、二进制数据类型
    • 1、binary
    • 2、varbinary
    • 3、image
  • 六、特殊数据类型
    • 1、 cursor:游标引用
    • 2、timestamp:时间戳
    • 3、uniqueIdentifier:全局唯一标识符,即GUID
    • 4、sql_variant:SQL变量
    • 5、table:表
    • 6、xml
  • 七、SQL Server 2008新增数据类型
    • hierarchid
  • 八、用户定义的数据类型
    • (1)创建用户定义的数据类型
    • (2)删除用户定义的数据类型
  • 九、参考:

    T-SQL语言和SQLServer数据库中的数据通常需要定义一个数据类型,数据类型定义了对象可以容纳的数据的种类。

    哪些对象需要数据类型

    (1)、表和视图的列:
    在定义表或视图时,其列需要定义数据类型。

    (2)、存储过程的参数:
    在定义存储过程的T-SQL代码中定义参数的数据类型。

    (3)、变量:
    如果在T-SQL中使用变量,需要定义数据类型。

    (4)、带返回值的函数:
    返回一个或多个特定类型数据值的T-SQL函数中的返回值需要定义数据类型。

    (5)、带返回代码的存储过程:
    具有返回代码的存储过程。

    一、 整数数据类型

    1、bit

    bit数据类型是整型,其值只能是0、1或空值。这种数据类型用于存储只有两种可能值的数据,如Yes 或No、True 或False 、On 或Off.   
    注意:很省空间的一种数据类型,如果能够满足需求应该尽量多用。每个TINYINT类型的数据占用1个字节的存储空间。

    • bit值保存为1、0或NULL 的整数数据类型。1代表true,0代表false。
    • 在插入、修改 bit 数据时,使用 0 或 1。
      SQL Server将字符串值“TRUE”(字符串不分大小写,或者数字字符'1')转换为1,将“FALSE”(字符串不分大小写,或者数字字符'0')转换为0。将任何非零值转换为1。
    • 在c# 中读取数据库数据时,可以直接用bool型读取该字段,会直接转换为true/false。

    2、tinyint

    tinyint数据类型存储从0 到255 之间的所有正整数。每个tinyint类型的数据占用1个字节的存储空间。

    3、smallint

    smallint 数据类型存储从-2的15次方( -32768) 到2的15次方-1( 32767 )之间的所有正负整数。
    每个smallint类型的数据占用2个字节的存储空间,其中1 位表示整数值的正负号,其它15 位表示整数值的长度和大小。

    4、int (integer)

    int (或 integer)数据类型存储从-2的31次方 (-2147483648) 到2的31次方-1 (2147483647) 之间的所有正负整数。
    每个INT 类型的数据按4个字节存储,其中1 位表示整数值的正负号,其它31 位表示整数值的长度和大小。

    5、bigint

    bigint数据类型存储从-2^63 (-9 223372036854775807) 到2^63-1( 9223372036854775807) 之间的所有正负整数。
    每个BIGINT 类型的数据占用8个字节的存储空间。

    二、 浮点数据类型

    浮点数据类型用于存储十进制小数。浮点数值的数据在SQL Server 中采用上舍入(Round up 或称为只入不舍)方式进行存储。
    所谓上舍入是指,当(且仅当)要舍入的数是一个非零数时,对其保留数字部分的最低有效位上的数值加1 ,并进行必要的进位。若一个数是上舍入数,其绝对值不会减少。
    如:对3.14159265358979 分别进行2 位和12位舍入,结果为3.15 和3.141592653590。

    1、real: 近似数值型

    real数据类型可精确到第7 位小数,其范围为从-3.40E -38 到3.40E +38。 每个real类型的数据占用4 个字节的存储空间。

    2、float[(n)]:近似数值型

    float数据类型可精确到第15 位小数,其范围为从-1.79E -308 到1.79E +308。 每个FLOAT 类型的数据占用8 个字节的存储空间。 
    float数据类型可写为FLOAT[ n ]的形式。n 指定float数据的精度。n 为1到15 之间的整数值。当n 取1 到7 时,实际上是定义了一个real类型的数据,系统用4 个字节存储它;
    当n 取8 到15 时,系统认为其是float类型,用8 个字节存储它。

    3、decimal[p [s] ]

    decimal数据类型可以提供小数所需要的实际存储空间,但也有一定的限制,您可以用2 到17 个字节来存储从-10的38次方-1 到10的38次方-1 之间的数值。
    可将其写为decimal[ p [s] ]的形式,p 和s 确定了精确的比例和数位。其中p 表示可供存储的值的总位数(不包括小数点),缺省值为18; s 表示小数点后的位数,缺省值为0。
    例如:decimal (15 5),表示共有15 位数,其中整数10 位,小数5。  

    4、numeric[p [s] ]

    numeric数据类型与decimal 数据类型完全相同。
    注意:SQL Server 为了和前端的开发工具配合,其所支持的数据精度默认最大为28位。但可以通过使用命令来执行sqlserver.exe程序以启动SQL Server,可改变默认精度。

    5、smallMoney货币型

    smallMoney 数据类型用来表示钱和货币值。这种数据类型能存储从-214748.3648 到214748.3647 之间的数据,精确到货币单位的万分之一。smallmoney 数据类型要求 4 个存储字节。

    6、money货币型

    money数据类型用来表示钱和货币值。这种数据类型能存储从-9220亿到9220 亿之间的数据,精确到货币单位的万分之一。money数据类型要求 8 个存储字节。

    三、字符数据类型

    字符数据类型是使用最多的数据类型。它可以用来存储各种字母、数字符号、特殊符号。一般情况下,使用字符类型数据时须在其前后加上单引号’或双引号” 。

    1、char[(n)]

    char数据类型的定义形式为char[(n)] 。 以char类型存储的每个字符和符号占一个字节的存储空间。n 表示所有字符所占的存储空间,n 的取值为1 到8000, 
    即可容纳8000 个ANSI 字符。若不指定n 值,则系统默认值为1。若输入数据的字符数小于n,则系统自动在其后添加空格来填满设定好的空间。若输入的数据过长,将会截掉其超出部分。

    如果没有在数据定义或变量声明语句中指定 n,则默认长度为 1如果在使用 CAST 和 CONVERT 函数时未指定 n,则默认长度为 30。

    2、nchar[(n)]

    nchar数据类型的定义形式为nchar[(n)]。 它与char类型相似。不同的是nchar数据类型n的取值为1 到4000。 因为nchar类型采用UNICODE 标准字符集(CharacterSet)。 
    UNICODE 标准规定每个字符占用两个字节的存储空间,所以它比非UNICODE 标准的数据类型多占用一倍的存储空间。使用UNICODE 标准的好处是因其使用两个字节做存储单位,
    其一个存储单位的容纳量就大大增加了,可以将全世界的语言文字都囊括在内,在一个数据列中就可以同时出现中文、英文、法文、德文等,而不会出现编码冲突。

    3、varchar[(n| max )]

    varchar数据类型的定义形式为varchar[(n| max )]。 它与char类型相似,n 的取值也为1 到8000,若输入的数据过长,将会截掉其超出部分。
    不同的是,varchar数据类型具有变动长度的特性,因为varchar数据类型的存储长度为实际数值长度,若输入数据的字符数小于n ,则系统不会在其后添加空格来填满设定好的空间。
    一般情况下,由于char数据类型长度固定,因此它比varchar类型的处理速度快。

    4、nvarchar[(n| max )]

    nvarchar数据类型的定义形式为nvarchar [ ( n | max ) ]它与varchar类型相似。不同的是,nvarchar数据类型采用UNICODE 标准字符集(Character Set), n 的取值为1 到4000。

    5、text

    text数据类型用于存储大量文本数据,其容量理论上为1 到2的31次方-1 (2147483647)个字节,在实际应用时需要视硬盘的存储空间而定。
    SQL Server 中,将text和image 类型的数据直接存放到表的数据行中。

    6 、ntext

    ntext数据类型与text类型相似不同的,是ntext类型采用UNICODE 标准字符集(Character Set), 因此其理论容量为230-1(1073741823)个字节。

    四、 日期和时间数据类型

    1、datetime :日期和時間(1753-1-1 日到 9999-12-31)

    datetime 数据类型用于存储日期和时间的结合体。它可以存储从公元1753 年1 月1 日零时起到公元9999 年12 月31 日23 时59 分59 秒之间。精确到三百分之一秒或3.33毫秒 

    2、dateTime2 (n):高精度的datetime类型(0001-01-01 到 9999-12-31)

    类似于之前的datetime类型,不过其精度比较高,可以精确到小数点后面7位(100ns),其使用语法为:datetime2(n)。
    0001-01-01 00:00:00.0000000到9999-12-31 23:59:59:9999999,表示日期和时间

    使用示例:

    declare @dt as datetime2(5)
    set @dt = getdate()
    select @dt

    3、smalldatetime  :精确到分钟,日期和時間。(1900-01-01 到 2079-06-06)不推荐。

    smalldatetime 数据类型用来表示小范围间的日期和时间,精确到一分钟。

    4、date:只表示日期:年-月-日(0001-01-01 到 9999-12-31)

    按照年-月-日的格式表示日期

    5、time:只表示时间:hh:mm:ss(00:00:00.0000000 到 23:59:59.9999999)

    按照hh:mm:ss的格式精确表示时间

    6、datetimeOffset:时间部分增加时区表示

    加入了时区偏移量部分,时区偏移量表示为 [+|-] HH:MM。  必需的 + 或 - 符号指示在 UTC(通用协调时间或格林尼治标准时间)中是加上还是减去时区偏移量以获取本地时间。

    00000-01-01 00:00:00.0000000到99999-12-31 23:59:59:9999999

    使用示例:

    declare @dt as datetimeoffset(8)
    set @dt = "2008-08-08 08:08:08.0 +8:00"
    select @dt

    五、二进制数据类型

    1、binary

    binary 数据类型用于存储二进制数据。其定义形式为binary (n), n 表示数据的长度,取值为1 到8000 。
    在使用时必须指定binary 类型数据的大小,至少应为1 个字节。BINARY 类型数据占用n+4 个字节的存储空间。
    在输入数据时必须在数据前加上字符“0X” 作为二进制标识,如:要输入“abc ”则应输入“0xabc ”。
    若输入的数据过长将会截掉其超出部分。若输入的数据位数为奇数,则会在起始符号“0X ”后添加一个0,如上述的“0xabc ”会被系统自动变为“0x0abc”。

    2、varbinary

    varbinary 数据类型的定义形式为varbinary (n)。 它与binary 类型相似,n 的取值也为1 到8000,若输入的数据过长,将会截掉其超出部分。
    不同的是varbinary 数据类型具有变动长度的特性,因为varbinary 数据类型的存储长度为实际数值长度+4个字节。当binary 数据类型允许NULL 值时,将被视为varbinary 数据类型。
    一般情况下,由于BINARY 数据类型长度固定,因此它比varbinary  类型的处理速度快。

    3、image

    image 数据类型用于存储大量的二进制数据Binary Data。其理论容量为2的31次方-1(2147483647)个字节。其存储数据的模式与text数据类型相同。
    通常用来存储图形等OLE Object Linking and Embedding对象连接和嵌入对象。在输入数据时同BINARY数据类型一样,必须在数据前加上字符“0X”作为二进制标识。应用程序可以使用BMP、TIEF、GIF 和 JPEG 格式把数据存储在 image 数据类型中。

    六、特殊数据类型

    1、 cursor:游标引用

    cursor 数据类型是一种特殊的数据类型,它包含一个对游标的引用。这种数据类型用在存储过程中,而且创建表时不能用。

    2、timestamp:时间戳

    timestamp 数据类型是一种特殊的数据类型,用来创建一个数据库范围内的唯一数码。 一个表中只能有一个timestamp列。每次插入或修改一行时,timestamp列的值都会改变。Timestamp 用于表示SQL Server 活动的先后顺序。尽管它的名字中有“time”, 但timestamp列不是人们可识别的日期。在一个数据库里,timestamp值是唯一的  。timestamp的数据类型为rowVersion数据类型的同义词。

    3、uniqueIdentifier:全局唯一标识符,即GUID

    uniqueIdentifier数据类型用来存储一个全局唯一标识符,即GUID。由 16 字节的十六进制数字组成。GUID确实是全局唯一的。这个数几乎没有机会在另一个系统中被重建。可以使用NEWID 函数或转换一个字符串为唯一标识符来初始化具有唯一标识符的列。

    例如:6F9619FF-8B86-D011-B42D-00C04FC964FF,此号码可以通过newid()函数获得,在全世界各地的计算机由此函数产生的数字不会相同。

    4、sql_variant:SQL变量

    用于存储除文本,图形数据和timestamp数据外的其它任何合法的Sql Server数据,可以方便Sql Server的开发工作。

    5、table:表

    用于存储对表或视图处理后的结果集。这种新的数据类型使得变量可以存储一个表,从而使函数或过程返回查询结果更加方便、快捷。

    6、xml

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

    七、SQL Server 2008新增数据类型

    hierarchid

    新增的数据类型,用于创建层次结构的表,或引用位于另一位置的数据层次结构。

    八、用户定义的数据类型

    用户定义的数据类型基于在 Microsoft SQL Server 中提供的数据类型。当几个表中必须存储同一种数据类型时,并且为保证这些列有相同的数据类型、长度和可空性时,可以使用用户定义的数据类型。例如,可定义一种称为postal_code 的数据类型,它基于 Char 数据类型。
    当创建用户定义的数据类型时,必须提供三个数:数据类型的名称、所基于的系统数据类型和数据类型的可空性。

    图形化操作:【可编程性】-->【类型】节点,右击【用户定义数据类型】节点,在弹出的框中,按照说明相应操作即可。

    (1)创建用户定义的数据类型

    创建用户定义的数据类型可以使用 Transact-SQL 语句。系统存储过程 sp_addtype 可以来创建用户定义的数据类型。其语法形式如下:

    sp_addtype {type},[,system_data_bype][,"null_type"]

    其中,type 是用户定义的数据类型的名称。system_data_type 是系统提供的数据类型,例如 Decimal、Int、Char   等等。 null_type 表示该数据类型是如何处理空值的,必须使用单引号引起来,例如'NULL'、'NOT NULL'或者'NONULL'。
    例子:创建一个用户定义的数据类型 ssn,其基于的系统数据类型是变长为11 的字符,不允许空。

    Use cust
    Exec sp_addtype ssn,"Varchar(11)","Not Null"

    例子:创建一个用户定义的数据类型 birthday,其基于的系统数据类型是 DateTime,允许空。

    Use cust
    Exec sp_addtype birthday,datetime,"Null"

    例子:创建两个数据类型,即 telephone 和 fax

    Use master
    Exec sp_addtype telephone,"varchar(24),"Not Null"
    Eexc sp_addtype fax,"varchar(24)","Null"

    (2)删除用户定义的数据类型

    当用户定义的数据类型不需要时,可删除。删除用户定义的数据类型的命令是

    sp_droptype {"type"}

    例子:

    Use master
    Exec sp_droptype "ssn"

    注意:当表中的列还正在使用用户定义的数据类型时,或者在其上面还绑定有默认或者规则时,这种用户定义的数据类型不能删除。

    九、参考:

    到此这篇关于SQL Server数据类型的文章就介绍到这了。希望对大家的学习有所帮助,也希望大家多多支持。

    标签: MsSQL
    相关文章: