数据库技术提升-MySQL数据库原理、设计与应用【1.5】

第3章 数据类型与约束

学习目标
掌握 MySQL中常用数据类型的使用
掌握 MySQl.中常用的束的使用
掌握 MySQL中字符集的设置与处理
        在数据库中,数据表用来组织和保存各种数据,它是由表结构和数据组成的。在设计表结构时,经常需要根据实际需求,选择合适的数据类型和约束。本章将围绕数据类型和约束进行详细讲解。

3.1 数据类型

        使用 MySQ1.,数据库存储数据时,不同的数据类型决定了 MySQL.存储数据方式的不同。MySQL,数据库提供了多种数据类型,其中包括数字类型,时间和目期类型,字符串类型。本节将针对这些数据类型进行讲解

3.1.1 数字类型

        在数据库中,经常需要存储一些数字,如商品的库存,销量,价格等,适合用数字类型来保存。数字类型包括整数类型,浮点数类型,定点数类型、BIT(位)类型等,下面分别进行讲解。
1.整数类型
        MySQ1.中的整数类型用于保存整数。根据取值范围的不同,整数类型可分为5种,分别是 TINYINT,SMALLINT,MEDIUMINT、INT 和 BIGINT。不同整数类型所对应的字节大小和取值范围如表 3-1所示。

        从表 3-1中可以看出,不同整数类型所占用的字节数和取值范围都是不同的。其中,占用字节数最小的是 TINYINT,占用字节数最大的是 BIGINT。不同整数类型的取值范围可以根据字节数计算出来,例如,TINYINT类型的整数占用1字节,1字节是8位,那么,TINYINT 类型无符号数的最大值就是2-1(即255),有符号数的最大值就是2-1(即127)。同理,可以算出其他不同整数类型的取值范围。
需要注意的是,若使用无符号数据类型,需要在数据类型右边加上 UNSIGNED 关键字来修饰,例如,INT UNSIGNED 表示无符号 INT 类型。
为了让读者更好理解,下面通过案例的方式演示整数类型的使用及注意事项。
(1)创建 my_int 表,选取 INT 和 TINYINT 两种类型测试。具体 SQL 语句如下
myegl>UsE myb;MYeg1>CREATE, TABLE mY int 
int. 1 1kr,
-3int 2 INF UNSIGNED.
int 3 TINYINT,-
-)int, A TINYINT UNSIGNED
->]
        上述 SQI, 语句中,int 1和 int 3是有符号类型,int 2和 int 4是无符号类型(2)插人记录进行测试。当数值在合法的取值范围内时,可以正确插人,反之则无法插人,提示错误信息。具体 SQ1.语句及执行结果如下
+0插人盛功测试
MYSG1> INSERT INTO MY Int WALUES(1000,1000,100,100).ury oK, l row affected (0.00 soc)
+の 插人失败测试
MYSGl>INSEET IHTO MY InE, VAIUES(1000,-1000,100.100).ERBOR, 1264 122003): 0ut of range value for colunn "int 2'at row 1
        从上述结果可以看出,由于“-1000”超出了无符号 INT类型的取值范围,数据插人失败,MySQL. 显示了错误信息,提示 int_2字段超出取值范围。(3)查看 my_int 表的结构,具体 SQL, 语句及执行结果如下。

        在执行结果中,数据类型右边使用小括号数字标注了显示宽度。默认情况下,显示宽度是取值范围所能表示的最大宽度。对于有符号类型,符号也占用一个宽度。例如,255的显示宽度为3,-128的显示宽度为4。需要注意的是,显示宽度与取值范围无关,若数值的位
        数小于显示宽度,会填充空格,若大于显示宽度,则不影响显示结果。(4)为字段设置零填充(ZEROFIL.)时,若数值宽度小于显示宽度,会在左侧填充 0。创建 my_int2 表,为字段设置零填充和宽度,具体 SQL 语句执行结果如下,

在上述结果中,设置零填充后,字段自动设为无符号类型,这是因为负数不能使用零填充。
(5)插人数据测试,具体 SQ1.语句及执行结果如下

从上述结果可知,当数值超过显示宽度时,不填充零;当数值未达到显示宽度时,在左侧填充零
小提示:
(1)在选择教据类型时,若一个数据将来可能参与数学计算,推荐使用整教,浮点数或定点数类型:若只用来显示,则推荐使用字符串类型。倒如，商品库存可能需要增加、减少求和等,所以保存为整数类型:用户的身份证,电话号码一般不需要计算,可以保存为字符
串型。(2)表的主键推荐使用整数类型,与字符串相比,整数类型的处理效率更高,查询速度更快。
(3)当插入的值的数据类型与字段的数据类型不一致,或使用 ALTER TABLE修改字段的数据类型时，MySQL会尝试尽可能将现有的值转换为新类型。例如，字符串"23'123'、1,23'与数字 123、-123、1.23 可以互相转换:1.5 转换为整数时,会被四舍五入,结果为 2。

2.浮点数类型
在 MySQ1.中,存储的小数都是使用浮点数或定点数来表示的。浮点数的类型有两种，分别是单精度浮点数类型(FLOAT)和双精度浮点数类型(DOUBLE),对应的字节大小及其取值范围如表 3-2所示

        表 3-2中列举的取值范围是理论上的极限值,但根据不同的硬件或操作系统,实际范围可能会小。另外,当浮点数类型使用UNSIGNED修饰为无符号时,取值范围将不包含负数。
        需要注意的是,浮点数类型虽然取值范围很大,但是精度并不高。FLOAT 的精度为 6位或7位,DOUBIE的精度大约为15位。如果超出精度,可能会导致给定的数值与实际保存的数值不一致,发生精度损失。
        为了让读者更好地理解,下面通过案例的方式演示浮点数类型的使用及注意事项。具体 SQ1语句及执行结果如下

        从上述结果可以看出,当一个数字的整数部分和小数部分加起来达到7位时,第7位就会四舍五人。
3.定点数类型
        定点数类型(DECIMAL)通过 DECIMAL(M,D)设置位数和精度,其中,M 表示数字总位数(不包括“,”和“一”),最大值为 65,默认值为 10;D 表示小数点后的位数,最大值为 30,默认值为 0。例如,DECIMAL(5,2)表示的取值范围是-999.99~999.99。系统会自动根据存储的数据来分配存储空间。若不允许保存负数,可通过 UNSIGNED 修饰。为了让读者更好地理解,下面通过案例的方式演示定点数类型的使用及注意事项。具体 SQL 语句及执行结果如下。

        从上述结果可以看出,若小数部分超出范围,会进行四舍五人,并出现 Data truncated(数据截断)警告;若整数部分超出范围,数据会插入失败,提示 Out of range value(超出取值范围)错误。
        小提示:浮点数类型也可以设置位教和精度,如{oat(8,2),但仍有可能损失精度。在实际使用时应避免使用浮点数类型，以免出现不能人为控制的问题。因此，对于小数类型的设置，推荐使用定点数类型并设置合理的范围可以使计算更为准确。
4.BIT 类型
        BIT(位)类型用于存储二进制数据,语法为 BIT(M),M 表示位数,范围为 1~64。下面以保存字符 A 为例,A 的 ASCII 码为十进制 65,对应的二进制为 1000001,总共有?位,需要至少?位来保存。示例 SQ1,语句如下。

        从上述示例可以看出,利用 MySQL. 中的 ASCII()、BIN(),L.ENGTH()函数可以方便地查询 ASCI码、二进制值和数字长度。BIT类型字段在数字插人时转换为二进制保存，但在利用 SELECT 查询时,会自动转换为对应的字符显示。
 多学一招:MySOL 中的直接常量
        直接常量是指在 MySQL, 中直接编写的字面常量,如数字 123、字符串abc等,常用于在INSERT语句中编写插入的数据。直接常量有多种语法形式，具体如下。(1)十进制数;语法近似于日常生活中的数字,如 123、1.23、-1.23,以及科学计数法
1E2.1E-2(E不分大小写)。
        (2)二进制数:在二进制字符串前加前级b,形如“b'100000]"”。通过“SEL.ECTb1000001';"可查看二进制转为ASCII字符后的结果,即字符A。
        (3)十六进制数;有两种表示方式,形如“x1"和“0x41"。其中,十六进制数 41 对应十进制数为 65。通过“SELECT HEX(65);"可查看十进制 65 转为十六进制的结果,即 41;通过“SEIECT x41'，0x41:"可查看 ASCII 字符,即字符 A。
        (4)字符串:MySOL支持单引号和双引号定界特,形如abe'和"abe",推荐使用单引号定界符。若要在单/双引号宇符串中书写单/双引号,需要在单/双引号前面加上反斜线“\”转义,即“\”和”\"”,这种方式称为转义字符。常用的转义字符如表 3-3 所示。

        (5)布尔值:有 TRUE和FALSE两个值(不分大小写),通常用于逻辑判断,表示事物的“真”和“假”。在 SELECT、INSERT等语句中使用布尔值时,TRUE会转换为I,FALSE会转换为0。
        (6)NULL值:通常用来表示没有值、值不确定等含义。例如,在插入一条商品数据时，暂时不知道该商品的库存量,可将库存量设为 NUL,以后再修改。

3.1.2 时间和日期类型

        为了方便在数据库中存储日期和时间,MySQL.提供了表示日期和时间的数据类型,分别是 YEAR,DATE,TIME,DATETIME 和 TIMESTAMP。表 3-4列举了这些 MySQL中日期和时间数据类型所对应的字节数、取值范围、日期格式以及零值。

        在表 3-4中,日期格式 YYYY表示年,MM表示月,DD表示日。每种日期和时间类型的取值范围都是不同的。需要注意的是,如果插人的数值不合法,系统会自动将对应的零值插人数据库中。
为了让读老更好地学习日期和时间类型,接下来分别进行详细讲解
1.YEAR 类型
YEAR 类型用于表示年份,使用示例如下
CREATE, TABIE mY YOaT IY YEAE!!
INSFRT TNTO MY YAT YALUES 42020).
#设置，字段的数据类型为YEAR
#插人年份数据，2020年
在 MSQL. 中,可以使用以下3种格式指定 YEAR 类型的值,
        (1)使用4位字符串或数字表示,为'1901'-'2155'或 1901~2155。例如,输人'2020'或2020,插人到数据库中的值均为2020。
        (2)使用两位字符串表示为'00'-"99'其中,00'~'69"的值会被转换为 2000~2069 的YEAR 值,70’-99'的值会被转换为 1970~1999 的 YEAR 值。例如,输人"20,插人到数据库中的值为 2020。
        (3)使用两位数字表示为1~99,其中,1~69 的值会被转换为 2001~2069 的 YEAR值,70~99 的值会被转换为 1970~1999 的 YEAR 值。例如,输人 20,插人到数据库中的值为 2020。
        需要注意的是,当使用 YEAR类型时,一定要区分0·和0。因为字符串格式的0'表示的YEAR 值是 2000,而数字格式的0 表示的 YEAR 值是 0000。
2.DATE 类型
DATE 类型用于表示日期值,不包含时间部分,使用示例如下

        在 MySQL 中,可以使用以下 4种格式指定 DATE 类型的值,(1)以'YYYY-MM-DD'或者'YYYYMMDD字符串格式表示。例如,输人2020-01-21或20200121',插人数据库中的日期都为 2020-01-21。(2)以'YY-MM-DD或者'YYMMDD字符串格式表示。YY 表示的是年,为00’~"99',其中00'-%9'的值会被转换为 2000一2069 的值,70-99的值会被转换为 1970~1999 的值。例如,输人20-01-21'或200121',插人数据库中的日期都为 2020-01-21。(3)以 YY-MM-DD或者 YYMMDD 数字格式表示。例如,输人 20-01-21 或 200121,插人数据库中的日期都为 2020-01-21.(4)使用CURRENT DATE或者NOWO输人当前系统日期。小提示:
        (1)通过"SELECTCURRENT DATE:"或“SELECT NOW():”可查看当前日期。(2)日期中的分隔符“_"还可以用"."“.”“/”等符号替代,
3.TIME 类型
        TIME类型用于表示时问值,它的显示形式一般为 HH:MM:SS,其中,HH 表示小时,MM表示分,SS表示秒。在MySQL中,可以使用以下3种格式指定 TIME 类型的值。
        (1)以"HHMMSS字符串或者 HHMMSS 数字格式表示。
        例如,输人345454'或 345454,插人数据库中的时间为34:54:54(34小时54分54秒)。(2)以D HH:MM:SS字符串格式表示。其中,D表示日,可以取 0~34 之间的值,插人数据时,小时的值等于(Dx24+HH)。
        例如,输人2 11:30:50',插人数据库中的时间为 59:30:50:输人'11:30:50',插人数据库中的时间为 11:30:50:输人34 22:59:59',插人数据库中的时间为 838:59:59。(3)使用 CURRENT TIME或 NOWO)输人当前系统时间。

4.DATETIME 类型
        DATETIME类型用于表示日期和时间,它的显示形式为'YYYY-MM-DD HH:MM:SS',其中,YYYY 表示年,MM 表示月,DD表示日,HH 表示小时,MM 表示分,SS 表示秒在 MySQ1. 中,可以使用以下 4种格式指定 DATETIME 类型的值。(1)以'YYYY-MM-DD HH:MM:SS'盛者'YYYYMMDDHHMMSS字符串格式表示的日期和时间,取值范围为1000-01-0100:00:00'-9999-12-31 23:59:59'。
例如,输人2014-01-22 09:01:23'或 20140122090123,插人数据库中的 DATETIME 值都为 2014-01-22 09:01:23。
        (2)以'YY-MM-DD HH:MM:SS或者'YYMMDDHHMMSS字符串格式表示的日期和时间,其中 YY 表示年,取值范围为00'-99'。与 DATE类型中的 YY 相同,00'-'9'范围的值会被转换为 2000-2069 范围的值,"70'-"99'范围的值会被转换为 1970-1999 范围的值。
        (3)以YYYYMMDDHHMMSS或者 YYMMDDHHMMSS 数字格式表示的日期和时间。
例如,插人 20140122090123 或者 140122090123,插人数据库中的 DATETIME 值都为2014-01-22 09:01:23。
        (4)使用NOWO)来输人当前系统的日期和时间
5.TIMESTAMP 类型
        TIMESTAMP(时问戳)类型用于表示日期和时间,它的显示形式与 DATETIME 相同,但取值范围比 DATETIME 小。下面介绍儿种 TIMESTAMP 类型与 DATATIME 类型不同的形式，具体如下。
(1)使用CURRENT TIMESTAMP 来输人系统当前日期和时间(2)无任何输人,或输人 NULL时,实际保存的是系统当前日期和时间。

        在MySQL中,TIMESTAMP 字段默认情况下会自动设置 NOT NULL, DEFAULTCURRENT TIMESTAMP ON UPDATE CURRENT TIMESTAMP 属性,具体解释如下。
        (1)NOT NULL表示非空约束,该字段将不允许保存 NULL值。
        (2)DEFAULT表示默认约束,当字段无任何输入时,自动设置某个值作为跌认值。此处设为 CUJRRENT TIMESTAMP 表示使用系统当前日期和时间作为默认值。
        (3)ON UPDATE用于当一条记录中的其他字段被 UJPDATE语句修改时,自动更改该字段为某个值。此处设为CURRENT TIMESTAMP表示每次修改时保存修改时的系统日期和时间。
若为TIMESTAMP宇段手动设置DEFAUJL,T属性时,该字段将不会自动设置 ONUPDATE 属性,通过如下 SQl,语句可进行测试。

        上述 SQL,语句通过 t1,t2、t3 字段演示了 TIMESTAMP 字段的3种使用方式。其中，t1和 t2的设置结果是相同的,t3 没有设置 ON UPDATE 属性。通过 DESC 查看表结构,会发现 t1和 t2 字段具有 DEFAULT 和 ON UPDATE 属性,t3 字段没有 ON UPDATE 属性,如下所示。

        值得一提的是,若使用 MySQL5.6之前的版本,my_timestamp 表会创建失败。这是因为 MySQL5.6之前的版本在一张表中只允许一个字段使用 CURRENT TIMESTAMP作为 DEFAULT和ON UPDATE的值。此时可以分成多张表进行测试。