数据库设计-C3-数据类型以及数据转换函数

SQLServerSQLServer数据库设计复习数据库设计复习数据类型以及数据转换函数目标•掌握SQL2008中常用数据类型•掌握部分数据类型范围及各自之间相互区别•在设计数据表的时候会选择合适的数据类型•掌握使用Convert和Cast函数进行数据类型或者格式的转换数据类型分类•字符数据类型•精确数值数据类型•近似数值数据类型•二进制数据类型•日期和时间数据类型•其他系统数据类型•CLR集成-自定义数据类型字符数据类型•字符数据类型（字符数据是由任何字母、符号和数字任意组合而成的数据–varchar–char–text–nvarchar–nchar–ntext字符、字节、Unicode字符•字符：人们使用的记号，抽象意义上的一个符号如￿'1',￿'中',￿'a',￿'$',￿'￥',￿……￿•字节：计算机中存储数据的单元，一个8位的二进制数，是一个很具体的存储空间如￿0x01,￿0x45,￿0xFA,￿……￿•Unicode字符：￿Unicode（统一码、万国码、单一码）是一种在计算机上使用的字符编码它为每种语言中的每个字符设定了统一并且唯一的二进制编码，以满足跨语言、跨平台进行文本转换、处理的要求，每个字符占用2个字节。

这样理论上一共最多可以表示￿65,536(2的16次方)￿个字符基本满足各种语言的使用字符数据类型存储空间及范围数据类型数据类型描述描述存储空间存储空间Char(n)N为1~8000字符之间n字节Nchar(n)N为1~4000￿Unicode字符之间(2n字节)＋2字节额外开销Ntext最多为230–1(1 073 741 823)Unicode字符每字符2字节Nvarchar(max)最多为230–1(1 073 741 823)Unicode字符2×字符数＋2字节额外开销Text最多为231–1(2 147 483 647)字符每字符1字节Varchar(n)N为1~8000字符之间每字符1字节＋2字节额外开销Varchar(max)最多为231–1(2 147 483 647)字符每字符1字节＋2字节额外开销字符数据类型区别•CHAR：CHAR存储定长数据很方便，CHAR字段上的索引效率级高，比如定义char(10)，那么不论你存储的数据是否达到了10个字节，都要占去10个字节的空间,不足的自动用空格填充•VARCHAR：存储变长数据，但存储效率没有CHAR高如果一个字段可能的值是不固定长度的，我们只知道它不可能超过10个字符，把它定义为￿VARCHAR(10)是最合算的。

VARCHAR类型的实际长度是它的值的实际长度+1为什么“+1”呢？这一个字节用于保存实际使用了多大的长度从空间上考虑，用varchar合适；从效率上考虑，用char合适，关键是根据实际情况找到权衡点•TEXT：text存储可变长度的非Unicode数据，最大长度为2^31-1(2,147,483,647)个字符•NCHAR、NVARCHAR、NTEXT这三种从名字上看比前面三种多了个“N”它表示存储的是Unicode数据类型的字符我们知道字符中，英文字符只需要一个字节存储就足够了，但汉字众多，需要两个字节存储，英文与汉字同时存在时容易造成混乱，Unicode字符集就是为了解决字符集这种不兼容的问题而产生的，它所有的字符都用两个字节表示，即英文字符也是用两个字节表示nchar、nvarchar的长度是在1到4000之间和char、varchar比较起来，nchar、nvarchar则最多存储4000个字符，不论是英文还是汉字；而char、varchar最多能存储8000个英文，4000个汉字可以看出使用nchar、nvarchar数据类型时不用担心输入的字符是英文还是汉字，较为方便，但在存储英文时数量上有些损失。

•特别要提的数据类型是text和ntexttext数据类型用于在数据页内外存储大型字符数据应尽可能少地使用这两种数据类型，因为可能影响性能但可在单行的列中存储多达2GB的数据与text数据类型相比，更好的选择是使用varchar(max)类型，因为将获得更好的性能另外，text和ntext数据类型在SQL￿Server的一些未来版本中将不可用，因此现在开始还是最好使用varchar(max)和nvarchar(max)而不是text和ntext数据类型精确数值数据类型•精确数值数据类型–bit–tinyint–smallint–int–bigint–numeric–decimal–money–float–real精确数值数据类型存储空间及范围数据类型数据类型描述描述存储空间存储空间bit0、1或Null1字节(8位)tinyint0～255之间的整数1字节smallint–32 768～32 767之间的整数2字节int–2 147 483 648～2 147 483 647之间的整数4字节bigint–9 223 372 036 854 775 808～9 223 372 036 854 775 807￿之间的整数8字节numeric(p,s)或decimal(p,s)–1 038＋1～1 038–1之间的数值最多17字节money–922 337 203 685 477.580 8～922 337 203 685 477.580 78字节smallmoney–214 748.3648～2 14 748.36474字节•numeric￿和￿decimal￿数据类型的默认最大精度值是￿38。

•如decimal和numeric等数值数据类型可存储小数点右边或左边的变长位数Scale是小数点右边的位数精度(Precision)定义了总位数，包括小数点右边的位数例如，14.88531可为numeric(7,5)或decimal(7,5)如果将14.25插入到numeric(5,1)列中，它将被舍入为14.3•p￿和￿s￿必须遵守的规则：0￿<=￿s￿<=￿p￿<=￿38•对于￿decimal￿和￿numeric￿数据类型，Microsoft®￿SQL￿Server™￿将精度和小数位数的每个特定组合看作是不同的数据类型例如，decimal(5,5)￿和￿decimal(5,0)￿被当作不同的数据类型近似数值数据类型•float•real近似数值数据类型存储空间及范围数据类型数据类型描述描述存储空间存储空间float[(n)]–1.79E＋308~–2.23E–308,0,2.23E–308~1.79E＋308N<￿=24－4字节N>￿24－8字节real–3.40E＋38~–1.18E–38,0,1.18E–38~3.40E＋384字节•Float和Real数据类型为近似数据。

每个数据包括科学计数法，由于缺少精度就造成了数据丢失例如，三分之一这个分数记作0.3333333…，当使用近似数据类型时能准确表示因此，从系统中检索到的数据可能与存储在该列中数据不完全一样你就可以将1，234，467，890存储为1.23E+9也就是说1.23这个数据中的小数点可以向右移9位你可以看到，在用这种方法存储数据时，你丢失了一些小数在转换成科学计数法时，原数据（1，234，467，890）就变成了1，230，000，000　　现在返回到数据类型Float和real数据类型能用科学计数法存储，唯一的区别就是这些值得范围和存储大小上面的表为这些数据类型值的范围Real数据类型需要4个字节的存储空间，并且固定精度为7你可以用这些浮点数据指定精度或整个数据的数字，即从1到53存储空间大小为4个字节（精度小于25时）到8个字节（精度为25到53）二进制数据类型•binary•image•Varbinary二进制数据类型存储空间及范围数据类型数据类型描述描述存储空间存储空间Binary(n)N为1~8000十六进制数字之间n字节Image最多为231–1(2 147 483 647)十六进制数位每字符1字节Varbinary(n)N为1~8000十六进制数字之间每字符1字节＋2字节额外开销Varbinary(max)最多为231–1(2 147 483 647)十六进制数字每字符1字节＋2字节额外开销•varbinary、binary、varbinary(max)或image等二进制数据类型用于存储二进制数据，如图形文件、Word文档或MP3文件。

其值为十六进制的0x0~0xfimage数据类型可在数据页外部存储最多2GB的文件image数据类型的首选替代数据类型是varbinary(max)，可保存最多8KB的二进制数据，其性能通常比image数据类型好SQL￿Server￿2008的新功能是可以在操作系统文件中通过FileStream存储选项存储varbinary(max)对象这个选项将数据存储为文件，同时不受varbinary(max)的2GB大小的限制日期和时间数据类型•date•datetime•datetime2•datetimeoffset•smalldateTime•time日期和时间数据类型存储空间及范围数据类型数据类型描述描述存储空间存储空间Date0001-1-1至9999-12-31【精准到天￿yyyy-mm-dd￿】3字节Datetime1753-1-1至9999-12-31【精准到0.00333秒￿yyyy-mm-dd￿hh:mm:ss:nnn】8字节Datetime2(n)0001-1-1至9999-12-31【精准到100纳秒￿yyyy-mm-dd￿hh:mm:ss:nnnnnn】6~8字节Datetimeoffset(n)0001-1-1至9999-12-31(全球标准时间)￿【精准到100纳秒￿yyyy-mm-dd￿hh:mm:ss:nnnnnn￿+|-￿hh:mm】8~10字节SmalldateTime1900-1-1至2079-6-6【精准到1分钟￿yyyy-mm-dd￿hh:mm:ss】4字节￿Time(n)0:0:0.000000至23:59:59.999999【精准到100纳秒￿hh:mm:ss:nnnnnn￿】3~5字节•datetime和smalldatetime数据类型用于存储日期和时间数据。

smalldatetime为4字节，存储1900年1月1日~2079年6月6日之间的时间，且只精确到最近的分钟datetime数据类型为8字节，存储1753年1月1日~9999年12月31日之间的时间，且精确到最近的3.33毫秒•SQL￿Server￿2008有4种与日期相关的新数据类型：datetime2、dateoffset、date和time通过SQL￿Server联机丛书可找到使用这些数据类型的示例•datetime2数据类型是datetime数据类型的扩展，有着更广的日期范围时间总是用时、分钟、秒形式来存储可以定义末尾带有可变参数的datetime2数据类型--如datetime2(3)这个表达式中的3表示存储时秒的小数精度为3位，或0.999有效值为0~9之间，默认值为3•datetimeoffset数据类型和datetime2数据类型一样，带有时区偏移量该时区偏移量最大为＋/-14小时，包含了UTC偏移量，因此可以合理化不同时区捕捉的时间•date数据类型只存储日期，这是一直需要的一个功能而time数据类型只存储时间它也支持time(n)声明，因此可以控制小数秒的粒度与datetime2和datetimeoffset一样，n可为0~7之间。

其他系统数据类型•cursor•hierarchyid•SQL_Variant•table•timestamp￿or￿rowversion•uniqueidentifier•xmlCLR集成•在SQL￿Server￿2008中，还可使用公共语言运行库(Common￿Language￿Runtime，CLR)创建自己的数据类型和存储过程这让用户可以使用Visual￿Basic或C#编写更复杂的数据类型，以满足业务需求这些类型被定义为基本的CLR语言中的类结构CAST和CONVERT•CAST和CONVERT用来更改一个值的数据类型或格式两个函数的目的相同，但CONVERT有能力去更改格式格式可以用来把数字4431.334更改为常用的货币风格4,431.33（每3个数字一个逗号并且小数点后保留2位），或者把4位的年2007更改位两位的年07CAST函数•CAST￿(￿expression￿AS￿data_type￿)￿CONVERT函数•CONVERT(data_type,expression[,style])•例子:SELECT￿CONVERT(varchar(30),getdate(),101)￿now结果为:now---------------------------------------09/15/2001•SELECT￿CONVERT(varchar(100),￿GETDATE(),￿0):￿05￿16￿2006￿10:57AMSELECT￿CONVERT(varchar(100),￿GETDATE(),￿1):￿05/16/06SELECT￿CONVERT(varchar(100),￿GETDATE(),￿2):￿06.05.16SELECT￿CONVERT(varchar(100),￿GETDATE(),￿3):￿16/05/06SELECT￿CONVERT(varchar(100),￿GETDATE(),￿4):￿16.05.06SELECT￿CONVERT(varchar(100),￿GETDATE(),￿5):￿16-05-06SELECT￿CONVERT(varchar(100),￿GETDATE(),￿6):￿16￿05￿06SELECT￿CONVERT(varchar(100),￿GETDATE(),￿7):￿05￿16,￿06SELECT￿CONVERT(varchar(100),￿GETDATE(),￿8):￿10:57:46SELECT￿CONVERT(varchar(100),￿GETDATE(),￿9):￿05￿16￿2006￿10:57:46:827AMSELECT￿CONVERT(varchar(100),￿GETDATE(),￿10):￿05-16-06SELECT￿CONVERT(varchar(100),￿GETDATE(),￿11):￿06/05/16SELECT￿CONVERT(varchar(100),￿GETDATE(),￿12):￿060516￿•SELECT￿CONVERT(varchar(100),￿GETDATE(),￿13):￿16￿05￿2006￿10:57:46:937SELECT￿CONVERT(varchar(100),￿GETDATE(),￿14):￿10:57:46:967SELECT￿CONVERT(varchar(100),￿GETDATE(),￿20):￿2006-05-16￿10:57:47SELECT￿CONVERT(varchar(100),￿GETDATE(),￿21):￿2006-05-16￿10:57:47.157SELECT￿CONVERT(varchar(100),￿GETDATE(),￿22):￿05/16/06￿10:57:47￿AMSELECT￿CONVERT(varchar(100),￿GETDATE(),￿23):￿2006-05-16SELECT￿CONVERT(varchar(100),￿GETDATE(),￿24):￿10:57:47SELECT￿CONVERT(varchar(100),￿GETDATE(),￿25):￿2006-05-16￿10:57:47.250SELECT￿CONVERT(varchar(100),￿GETDATE(),￿100):￿05￿16￿2006￿10:57AMSELECT￿CONVERT(varchar(100),￿GETDATE(),￿101):￿05/16/2006SELECT￿CONVERT(varchar(100),￿GETDATE(),￿102):￿2006.05.16•SELECT￿CONVERT(varchar(100),￿GETDATE(),￿103):￿16/05/2006SELECT￿CONVERT(varchar(100),￿GETDATE(),￿104):￿16.05.2006SELECT￿CONVERT(varchar(100),￿GETDATE(),￿105):￿16-05-2006SELECT￿CONVERT(varchar(100),￿GETDATE(),￿106):￿16￿05￿2006SELECT￿CONVERT(varchar(100),￿GETDATE(),￿107):￿05￿16,￿2006SELECT￿CONVERT(varchar(100),￿GETDATE(),￿108):￿10:57:49SELECT￿CONVERT(varchar(100),￿GETDATE(),￿109):￿05￿16￿2006￿10:57:49:437AMSELECT￿CONVERT(varchar(100),￿GETDATE(),￿110):￿05-16-2006SELECT￿CONVERT(varchar(100),￿GETDATE(),￿111):￿2006/05/16SELECT￿CONVERT(varchar(100),￿GETDATE(),￿112):￿20060516SELECT￿CONVERT(varchar(100),￿GETDATE(),￿113):￿16￿05￿2006￿10:57:49:513SELECT￿CONVERT(varchar(100),￿GETDATE(),￿114):￿10:57:49:547SELECT￿CONVERT(varchar(100),￿GETDATE(),￿120):￿2006-05-16￿10:57:49SELECT￿CONVERT(varchar(100),￿GETDATE(),￿121):￿￿2010-09-08￿23:39:34.390•CAST与CONVERT–cast是SQL标准￿–convert是MS的–cast能实现的convert都能实现–convert此样式一般在时间类型(datetime,smalldatetime)与字符串类型(nchar,nvarchar,char,varchar)相互转换的时候才用到。