SQL 常用数据类型

 SQL 语言最基本的数据类型包括:字符串类型、数值类型、日期和时间类型、NULL 数据类型、布尔值。MySQL 为除了 NULL 值外的所有通用数据类型提供了列类型。列类型是一种手段,通过这种手段可以描述表的列可以包含什么样类型的值。下面我们分别加以介绍。
一、NULL数据类型
        NULL 值是一种无类型的值,表示“空,什么也没有”。NULL 是表示“没有值”的专用术语。如果表中某个字段的值是 NULL,其表现形式就是其值为空,其值就是没有值。NULL 并不等同于0或空格。值为 NULL 的字段在表创建过程中会保持为空。
        在使用 NULL 数据类型时,需要明确它表示相应字段不是必须要输入数据的。如果某个字段必须包含数据,就把它设置为 NOT NULL 。只要字段有可能不包含数据,最好就把它设置为 NULL 。
二、字符串类型
        字符串是由单引号或者双引号括起来的字符或者数字。如:"abc",'abc10' 
     
    1、定长字符串 CHAR 类型
        CHAR 列的长度固定为创建表时声明的长度,可以为从0到255的任何值。
        CHAR 类型声明的长度表示你想要保存的最大字符数。例如,CHAR(30)可以占用30个字符,如果输入的数据只有20个字符,那么剩余10位就会被记录为空格。填充空格确保了字段里每个值都具有相同的长度。
        注意,不要使用 CHAR 类型保存长度不定的数据,比如姓名。如果不恰当地使用定长数据类型,可能会导致浪费可用空间,以及影响对不同的数据进行精确比较。应该使用 VARCHAR 类型来保存长度不定的字符串,从而节省数据库空间。
    2、变长字符串 VARCHAR 类型
        变长字符串就是长度不固定的字符串。VARCHAR 列的长度可以指定为0到65,535之间的值。同 CHAR 对比,VARCHAR 值保存时只保存需要的字符数,另加一个字节来记录长度(如果列声明的长度超过255,则使用两个字节)。
        VARCHAR 值保存时不进行填充。举例来说,如果某个变长字段的长度定义为10,即 VARCHAR(10),而输入的字符串长度为5,那么这个值的总长度也就是5,这时不会使用空格来填充字段里的空白。所以如果字符串的长度不固定,最好使用 VARCHAR 类型,以节省数据存储空间。
        如果分配给 CHAR 或 VARCHAR 列的值超过列的最大长度,则对值进行裁剪以使其适合。如果被裁掉的字符不是空格,则会产生一条警告。
    3、二进制字符串 BINARY 和 VARBINARY 类型
        BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR,不同的是它们包含二进制字符串而不要非二进制字符串。也就是说,它们包含的是字节而不是字符。这两种数据类型通常被用来保存数字式数据,例如图像文件。
        当保存BINARY值时,在它们右边填充值以达到指定长度。
        对于VARBINARY,插入时不填充字符,选择时不裁剪字节。
    4、二进制大对象 BLOB 类型
        有些变长数据类型需要保存更长的数据,超过了一般情况下为 VARCHAR 字段所保留的长度,比如常见的 BLOB 和 TEXT 数据类型。这些数据类型是专门用于保存大数据集的。
        BLOB是二进制大对象,它的数据是很长的二进制字符串(字节串),可以容纳可变数量的数据。BLOB 适合在数据库里存储二进制媒体文件,比如图像和 MP3。有4种BLOB类型:TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们可容纳值的最大长度不同。
    5、长字符串 TEXT 类型
        TEXT 数据类型是一种长字符串类型,可以被看作一个大 VARCHAR 字段,通常用于在数据库里保存大字符集,比如博客站点的 HTML 数据。在数据库里保存 TEXT 类型的数据可以实现站点的动态更新。
        有4种TEXT类型:TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT,分别对应4种 BLOB 类型,有相同的最大长度和存储需求。
        在 TEXT 或 BLOB 列的存储或检索过程中,不存在大小写转换。
        MySQL 连接程序 /ODBC 将 BLOB 值定义为 LONGVARBINARY,将 TEXT 值定义为 LONGVARCHAR。
    6、枚举 ENUM 类型
        ENUM 是一个字符串对象,其值来自表创建时在列规定中显式枚举的一列值。如:ENUM('one','two','three')
        在某些情况下,ENUM 值也可以为空字符串('')或 NULL:
 * 如果你将一个非法值插入ENUM(也就是说,允许的值列之外的字符串),将插入空字符串作为特殊错误值。
 * 如果将 ENUM 列声明为允许 NULL,NULL 值则为该列的一个有效值,并且 默认值为 NULL;如果 ENUM 列被声明为 NOT NULL,其默认值为允许的值列的第1个元素。
        每个枚举值有一个索引:
  * 来自列规定的允许值列中的值从 1 开始编号。
 * 空字符串错误值的索引值是 0。
 * NULL 值的索引是 NULL。
        例如,定义为 ENUM('one','two','three') 的列可以有下面所示任何值:NULL、''、'one'、'two'、'three';其索引分别为:NULL、0、1、2、3。
    7、集合 SET 类型
        SET 是一个字符串对象,可以有零或多个值,其值来自表创建时规定的允许的一列值。指定包括多个成员的 SET 列值时各成员之间用逗号间隔开。这样 SET 成员值本身不能包含逗号。
        例如,指定为 SET('one', 'two')    NOT NULL 的列可以有下面的任何值:'','one','two','one,two'
        SET 最多可以有64个不同的成员。
三、数值类型
        标准SQL数值数据类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC),以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。关键字 INT 是 INTEGER 的同义词,关键字 DEC 是 DECIMAL 的同义词。BIT 数据类型保存位字段值。作为 SQL 标准的扩展,MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。
 
    1、整型
        整数是不包含小数点的数值(包括正数、负数和0)。整数由数字序列组成,如:1,100。整型可以由十进制和十六进制表示。由十六进制表示方法:0x且后面加 1-9 和 A-F 的任意数字或字母,并且 0x 中的 X 不 能大写。
        TINYINT、SAMLLINT、MEDIUMINT、INT、BIGINT 每个整数类型的取值范围不同,可以表示不同的数值范围。下面的表显示了每个整数类型的存储字节和数值范围。
       
        在定义整型列时,可以在该类型关键字后面的括号内指定整数值的显示宽度M(例如,INT(M)),M 是从1到255的值,它表示显示列中值的字符数。显示宽度并不限制可以在列内保存的值的范围,也不限制超过列的指定宽度的值的显示。
        当输入数值的显示宽度小于指定的列宽度值时从左侧填满宽度。当结合扩展属性 ZEROFILL 使用时, 默认补充的空格用零代替。例如,对于声明为INT(5) ZEROFILL 的列,值4检索为00004。
        所有整数类型可以有一个可选(非标准)属性 UNSIGNED。当你想要在列内只允许非负数或者该列需要较大的上限数值范围时,可以使用无符号值。
    2、浮点型
        浮点数是有效位数和标度都可变并且没有限制的小数数值,任何有效位数和标度都是可以的。浮点数由一个数字加小数点再加上一个数字组成。两个数字序列不能同时为空。
        对于浮点列类型,在MySQL中单精度值使用4个字节,双精度值使用8个字节。SQL 标准允许在关键字 FLOAT 后面的括号内选择用位指定精度。            MySQL 允许使用非标准语法:FLOAT(M,D) 或 REAL(M,D) 或 DOUBLE PRECISION(M,D)。这里,“(M,D)”表示该值一共显示 M 位整数,其中 D 位位于小数点后面。例如,定义为 FLOAT(7,4) 的一个列可以显示为 -999.9999。MySQL 保存值的时侯进行四舍五入,因此如果在 FLOAT(7,4) 列内插入999.00009,近似结果是999.0001。MySQL 将 DOUBLE 视为 DOUBLE PRECISION 的同义词,还将 REAL 视为 DOUBLE PRECISION 的同义词。
        浮点类型也可以为 UNSIGNED,该属性防止负值保存到列中。
    3、小数类型
        小数类型是指包含小数点的数值。
        SQL 的小数标准如下所示,其中 M 表示精度(数值的总体长度,即有效位数),N 表示标度(小数点后面的位数)。
        DECIMAL(M,N)
        SQL 实现中一个通用的数值类型是 NUMERIC,它符合 ANSI 标准。在 MySQL 中把 DECIMAL 和 NUMERIC 视为相同的类型,它们用于保存必须为确切精度的值,例如货币数据。当声明该类型的列时,通常要指定精度和标度。
        例如:salary DECIMAL(5,2)
        表示 salary 列能够用5位有效位和两位小数保存任何值。因此,在这种情况下可以保存在 salary 列的数据值的范围是从-999.99到999.99。
        在标准 SQL 中,语法 DECIMAL(M) 等价于 DECIMAL(M,0);语法 DECIMAL 等价于 DECIMAL(M,0),可以通过计算确定 M 的值。在 MySQL 中支持 DECIMAL 和 NUMERIC 数据类型的变量形式,M默认值是10。DECIMAL 或 NUMERIC 的最大位数是65,但实际范围受具体列的精度或标度约束。如果实际数值的小数位数超出了定义的位数,数值就会被四舍五入。
        例如:定义为 DECIMAL(4,2)的字段允许输入的数值包括:12、12.4、12.44、12.449,最后一个值 12.449 在保存到字段时会被四舍五入为 12.45 。
    4、数值列的完整性约束
        
四、日期和时间类型
        日期和时间(DATETIME)数据类型是用于保存日期和时间信息的。
        日期和时间值是存储如 '2005-1-1' 或者 '12:00:00' 这样的值。在MySQL中日期是按“年-月-日”的顺序保存的。
        所有日期和时间列类型的类型名及其说明见下表

说明:
   * 每个日期和时间列类型都有一个零值,当插入非法数值时就用零值来添加。
   * 表示日期时必须先按“年,月,日”的顺序给出。
   * DATE、TIME、DATETIME 分别是存储日期、时间及日期和时间的组合,其格式为 "YYYY-MM-DD"、"hh:mm:ss" 和 "YYYY-MM-DD hh:mm:ss",对于 DATETIME 类型,  日期和时间部分都需要。
   * TIMESTAMP 时间戳列类型以 “YYYYMMDDhhmmss”的格式来表示值,其取值范围是19700101000000到2037年的某个时间,主要用于记录更改或创建某个记录。
        在非严格模式,MySQL 服务器只对日期的合法性进行基本检查:年、月和日的范围分别是 1000~9999、00~12 和 00~31。任何超出这些范围的日期被转换成 '0000-00-00' 。请注意仍然允许你保存非法日期,例如 '2002-04-31' 。在严格模式,非法日期不被接受,并且不转换。
        包含两位年值的日期会令人模糊,因为世纪不知道。MySQL 使用以下规则解释两位年值:
 *  00~69 范围的年值转换为 2000~2069。
 *  70~99 范围的年值转换为 1970~1999。
五、布尔值
        布尔值(BOOLEAN)的取值范围是 TRUE、FALSE 和 NULL,用于进行数据比较。
        举例来说,在查询中设置条件时,每个条件都会被求值,得到 TRUE、FALSE 或 NULL。如果查询中所有条件都是 TRUE,数据就会被返回;如果某个条件的值是 FALSE 或 NULL,数据就不会返回。
        大多数数据库实现并没有一个严格意义上的 BOOLEAN 类型,而是代之以各自不同的实现方法。MySQL 拥有 BOOLEAN 类型,但实质上与其现有的 TINYINT 类型相同。
六、自定义类型
        自定义类型是由用户定义的类型,它允许用户根据已有的数据类型来定制自己的数据类型,从而满足数据存储的需要。自定义类型极大地丰富了数据存储的可能性,使开发人员在数据库程序开发过程中具有更大的灵活性。
        语句 create type 用于创建自定义类型。
七、小结


  
        SQL 具有多种数据类型,允许不同类型的数据保存到数据库。无论是使用像 C 这样的编程语言,还是使用关系型数据库实现 SQL 编码,数据类型的概念都是一样的。
        在考虑数据类型、长度、精度和标度时,一定要仔细地进行短期和长远的规划。另外,公司制度和希望用户以什么方式访问数据库也是要考虑的因素。开发人员应该了解数据的本质,以及数据在数据库里是如何关联的,从而使用恰当的数据类型。
        本节对数据类型的介绍比较抽象,在今后的学习中,我们会逐渐了解各种数据类型的具体应用。

 【参考资料】
W3school 在线SQL教程   http://www.w3school.com.cn/sql/sql_null_values.asp  SQL NULL 值
   http://www.w3school.com.cn/sql/sql_datatypes.asp  SQL 数据类型

 课后练习:
        1. 判断对错:个人社会保险号码,输入格式为 '111111111',它可以是下面任何一种数据类型:定长字符串、变长字符串、数值。
        2. 判断对错:数值类型的标度是指数值的总体长度。
        3.    下面定义的有效位数和标度分别是多少?
         DECIMAL(4,2)
         DECIMAL(10,2)
         DECIMAL(14,1)
        4.    下面哪个数值能够输入到定义为 DECIMAL(4,1)的字段里?
         A.  16.2
         B.  116.2
         C.  16.21
         D.  1116.2
         E.  1116.21
        5.    考虑以下字段名称,为它们设置适当的实际类型,确定恰当的长度,并给出一些示范数据:
         a)  ssn
         b)  state
         c)  city
         d)  phone_number
         e)  zip
         f)  last_name
         g)  first_name
         h)  salary
         i)  hourly_pay_rate
         j)  date_hired
        6.  同样是上面这些字段,判断它们应该是 NULL 或 NOT NULL 。
        7. 思考以下问题:
         1)电话号码一般使用什么数据类型存储?
 2)性别一般使用什么数据类型存储?
 3)年龄信息一般使用什么数据类型存储?
 4)照片信息一般使用什么数据类型存储?
 5)薪水一般使用什么数据类型存储?
 6)学员姓名允许为空吗?
 7)家庭地址允许为空吗?
 8)电子邮件信息允许为空吗?
 9)考试成绩允许为空吗?