数据库数据类型（Database data type）

1、数据库数据类型（Database data type）本文由stevefrancis8贡献1、int (integer)int (或integer) 数据类型存储从 - 2的31次方 (- 2, 147 483 648) 到2的31次方 (2, 147, 483647) 之间的所有正负整数.每个int 类型的数据按4 个字节存储, 其中1 位表示整数值的正负号, 其它31 位表示整数值的长度和大小.2、smallint数据类型存储从 - 2的15次方 smallint (- 32, 768) 到2的15次方 (32, 767) 之间的所有正负整数.每个smallint 类型的数据占用2 个字节

2、的存储空间, 其中1 位表示整数值的正负号, 其它15 位表示整数值的长度和大小.3、tinyinttinyint数据类型存储从0 到255 之间的所有正整数.每个tinyint类型的数据占用1 个字节的存储空间.4、bigintbigint 数据类型存储从 - 2 63 (9), 372, 036, 854, see, e.g. 到2) 63 (1) (9), 372, 036, 854, see, e.g. 之间的所有正负整数.每个bigint 类型的数据占用8个字节的存储空间).a 浮点数据类型浮点数据类型用于存储十进制小数.浮点数值的数据在sql server 中采用上舍入 (roun

3、d up 或称为只入不舍) 方式进行存储.所谓上舍入是指, 当 (且仅当) 要舍入的数是一个非零数时, 对其保留数字部分的最低有效位上的数值加1, 并进行必要的进位.若一个数是上舍入数, 其绝对值不会减少.如: 对 3.14159265358979 分别进行2 位和12位舍入, 结果为 3.15 和 3.141592653590.1、real 数据类型real数据类型可精确到第7 位小数, 其范围为从 -3.40e 38 到 3.40e + 38. 每个real类型的数据占用4 个字节的存储空间.2、floatfloat数据类型可精确到第15 位小数, 其范围为从 -1.79e 308 到 1

4、.79e + 308. 每个float 类型的数据占用8 个字节的存储空间. float数据类型可写为float n 的形式.n 指定float 数据的精度.n 为1到15 之间的整数值.当n 取1 到7 时, 实际上是定义了一个real 类型的数据, 系统用4 个字节存储它; 当n 取8 到15 时, 系统认为其是float 类型, 用8 个字节存储它.3、decimaldecimal数据类型可以提供小数所需要的实际存储空间, 但也有一定的限制, 您可以用2 到17 个字节来存储从 10的38次方 - 1 - 1 - 到10的38次方 之间的数值.可将其写为decimal p 的形式, p

5、和s 确定了精确的比例和数位.其中p 表示可供存储的值的总位数 (不包括小数点), 缺省值为18; 表示小数点后的位数, 缺省值为0. 例如: decimal (5), 表示共有15 位数, 其中整数10 位, 小数5. 位表4 - 3 列出了各精确度所需的字节数之间的关系.4、numericnumeric数据类型与decimal数据类型完全相同.注意: sql server 为了和前端的开发工具配合, 其所支持的数据精度默认最大为28位.但可以通过使用命令来执行sqlserver.exe程序以启动sql server, 可改变默认精度.命令语法如下: sqlservr / master _

6、device _ path / w precisim _ leve1例4 - 4: 用最大数据精度38 启动sql serversqlservr / d c: mssql2000 data master.dat / p38/ * 在使用了 / p 参数后, 如果其后没有指定具体的精度数值, 则默认为38 位. *these 二进制数据类型1、binarybinary 数据类型用于存储二进制数据.其定义形式为binary (n), n 表示数据的长度, 取值为1 到8000.在使用时必须指定binary 类型数据的大小, 至少应为1 个字节.binary 类型数据占用n + 4 个字节的存储空间

7、.在输入数据时必须在数据前加上字符 0x 作为二进制标识, 如: 要输入 abc and 0xabc 则应输入.若输入的数据过长将会截掉其超出部分.若输入的数据位数为奇数 则会在起始符号 0x , 后添加一个0.If the above 0xabc will be automatically converted to 0x0abc system.2, VARBINARYThe VARBINARY data type is defined in the form of VARBINARY (n). It is similar to the BINARY type, and the value o

8、f n is from 1 to 8000. If the input data is too long, the excess will be deleted. The difference is that the VARBINARY data type has the characteristic of variable length because the storage length of the VARBINARY data type is the actual numeric value, +4 bytes in length. When the BINARY data type

9、allows the NULL value, the VARBINARY data type is considered.Typically, since the BINARY data type is fixed in length, it is faster than the VARBINARY type.4.3.4 logical data typeThe BIT:BIT data type occupies 1 bytes of storage space, with a value of 0 or 1. If you enter a value other than 0 or 1,

10、it will be considered 1. The BIT type cannot be defined as a NULL value (the so-called NULL value refers to null or meaningless values).4.3.5 character data typeCharacter data types are the most used data types. It can be used to store all kinds of letters, numbers, symbols, and special symbols. In

11、general, when you use character type data, you must add single quotation marks or double quotation marks before and after.1 CHARThe CHAR data type is defined as CHAR(n). A storage space in which each character and symbol stored in the CHAR type occupies one byte. N represents the storage space for a

12、ll characters, and the value of N from 1 to 8000 can hold 8000 ANSI characters. If the n value is not specified, the system defaults to 1. If the number of characters in the input data is less than N, the system automatically adds spaces later to fill the set space. If the input data is too long, th

13、e excess will be deleted.2, NCHARThe NCHAR data type is defined as NCHAR(n). It is similar to the CHAR type. The difference is that the NCHAR data type n has a value of 1 to 4000. Because the NCHAR type uses the UNICODE standard character set (CharacterSet). The UNICODE standard specifies that each

14、character occupies two bytes of storage space, so it takes more than twice as much storage space as the UNICODE standard data type. The use of the UNICODE standard is good because of the use of two bytes of storage capacity of the unit, a storage unit is greatly increased, can be the worlds language

15、s are included, in a data column can appear at the same time, Chinese English, French and German, and will not appear encoding conflict.3, VARCHARThe VARCHAR data type is defined in the form of VARCHAR (n). It is similar to the CHAR type, and the value of n is from 1 to 8000. If the input data is to

16、o long, the excess will be deleted. The difference is, VARCHAR data type has the characteristics of variable length, because the storage length of the VARCHAR data type for the actual value of length, the number of characters if the input data is less than N, then the system will add space to fill i

17、n the following set space.Typically, since the CHAR data type is fixed in length, it is faster than the VARCHAR type.4, NVARCHARThe NVARCHAR data type is defined as NVARCHAR(n). It is similar to the VARCHAR type. The difference is that the NVARCHAR data type uses the UNICODE standard character set (

18、Character Set), and the n values range from 1 to 4000.4.3.6 text and graphics data typesThis type of data is used to store large amounts of characters or binary data.1, TEXTThe TEXT data type is used to store a large amount of text data, and its capacity is theoretically 1 to 2 bytes of 31 times -1

19、(2147483647) bytes, and in actual use it depends on the storage space of the hard disk.In the previous version of SQL Server 2000, a TEXT object stored in the database is actually a pointer pointing to a data page (Data Page) in 8KB (8192 bytes). These data pages are dynamically added and logically

20、linked. In SQL Server 2000, the TEXT and IMAGE types are stored directly into the data rows of the table instead of being stored on different data pages. This reduces the space used to store TEXT and IMA- GE types and reduces the number of I/O for disk processing of such data accordingly.2 NTEXTThe

21、NTEXT data type is similar to the TEXT. type. The difference is that the NTEXT type uses the UNICODE standard character set (Character Set), so the theoretical capacity is 230-1 (1, 073, 741, 823) bytes.3 IMAGEThe IMAGE data type is used to store large amounts of binary data, Binary, Data.Its theore

22、tical capacity is 2 of the 31 square -1 (2147483647) bytes. The schema for storing data is the same as the TEXT data type. Usually used to store graphics, such as OLE, Object, Linking, and, Embedding, object connections and embedded objects. When you input data, like the BINARY data type, you must a

23、dd the character 0X as the binary identifier before the data4.3.7 date and time data type1 DATETIMEA combination of DATETIME data types used to store date and time. It can store all dates and times between 23:59 January 1, 1753 AD and 59 seconds between December 31, 9999 and 3.33 BC, with an accurac

24、y of 1/300 seconds, or milliseconds. The DATETIME data type occupies 8 bytes of storage space. The first 4 bytes are used to store the number of days before or after January 1, 1900, and the values are positive and negative. The positive numbers indicate the date after this date, and the negative nu

25、mbers indicate the date before this date. The last 4 bytes are used to store the number of milliseconds elapsed at the specified time from zero. If the time section is omitted in the input data, the system uses 12:00:00:000AM as the default time value: if the date part is omitted, the system default

26、s to the date in January 1, 1900.2 SMALLDATETIMEThe SMALLDATETIME data types and DATETIME data types are similar, but the date and time range is small, from January 1, 1900 to June 2079 6: on the low precision, can only be accurate to the minute, a minute for four seconds to five homes in the value

27、to 30 seconds for the four to five homes. For example: DATETIME time is 14:38:30.283SMALLDATETIME considers the 14:39:00 SMALLDATETIME data type to store data with 4 bytes. The first 2 bytes store the number of days from the base date since January 1, 1900, and the last two bytes store the minutes s

28、pecified at this zero hour.The input formats for date and time are described belowDate input formatThe date has many input formats and can be broadly divided into three categories:English + digital formatIn this format, the month is available in English full name or abbreviation, and is case insensi

29、tive; no comma is used between year and month;The year may be 4 or 2; if it is two, if the value is less than 50, it is considered to be 20XX years, if it is greater than or equal to 50Deemed to be 19XX years; if the day is partially omitted, it shall be deemed number 1 of the month. The following f

30、ormats are in the correct date format:June 212000, Oct 11999, January 20002000, February2000 May 120001 Sep 99 June July 00Digital + delimiter formatAllows slashes (/), connectors (-) and decimal points (numbers) to be represented as numbers, months, and daysDelimiter. Such as:YMD:2000/6/22 2000-6-2

31、2 2000.6.22MDY:3/5/2000 3-5-2000 3.5.2000DMY:31/12/1999 31-12-1999 31.12.2000Pure digital formatA pure digital format represents a date in consecutive 4, 6, or 8 digit numbers. If the input is 6 bits or 8 bitsThe digital system, will be the year month day to identify, YMD format, and the month and d

32、ay are two digit:If the input number is 4 digits, the system considers the 4 digits to represent the year, and the month and the day default for January of this year1 day. Such as:June 1, 200006012000, December 12, 9912121999, 19981998 yearsTime input formatInput time must be entered in the order of

33、 hours, minutes, seconds, and milliseconds. The colon is separated by :. But the milliseconds can be divided into decimal places, followed by the first digits for 1/10 seconds, the second digits for one percent seconds, and the third digits for 1/1000 seconds. Use AM when using 12 hour system. Am an

34、d PM (PM) respectively, the specified time is noon or afternoon, if not specified, the default system for AM. AM and PM are case insensitive. Such as:3:5:7.2pm 3 p.m., 5 minutes, 7 seconds, 200 milliseconds10:23:5.123Am 10:23 a.m., 5 seconds, 123 millisecondsYou can use the SET DATEFORMAT command to

35、 set the systems default date - time format.4.3.8 currency data typeCurrency data types are used to store currency values. In the use of currency data type, should be added before the currency symbol in the data, to identify the system for which the currency of the country, if not the currency symbo

36、l, the default is . The currency symbols are shown in figure 4-2.1 MONEYThe MONEY data type is a 4 bit decimal DECIMAL values, the values from 63 -2 (63 -922337203685477.5808 to 2 -1 (+922337203685477.5807), the data accuracy of 1/10000 monetary units. The MONEY data type is stored in 8 bytes.2 SMAL

37、LMONEYThe SMALLMONEY data type is similar to the MONEY type, but its currency value range is smaller than the MONEY data type, with values ranging from -214748.3648 to +214748.3647, and the storage space is 4 bytes.4.3.9 specific data typeSQL Server contains some special data types for data storage.

38、1 TIMESTAMPThe TIMESTAMP data type provides the database within the scope of this type is only equivalent to BINARY8 or VARBINARY (8), but when it defined the listed in the update or insert rows, the columns will be automatically updated, a count will be automatically added to the TIMESTAMP data in

39、the column. There is only one TIMESTAMP data column in each database table. If a column called TIMESTAMP is created, then the columns type is automatically set to the TIMESTAMP data type.2 UNIQUEIDENTIFIERThe UNIQUEIDENTIFIER data type stores a 16 bit binary digit. This number is called (GUIDGlobally Uni