数据库学习-MySQL基础篇（四）

1. MySQL中的数据值分类

(1) 数值

整数(不包含小数部分)，定点数/浮点数(可以包含小数部分)以及位域值。如：48，36.15，-2.34E12，b’1001’。注意：逗号不能作为分隔符。

1) 整数及定点数

精确值和近似值：整数及非科学计数法表示的值为精确值；科学计数法表示的值为近似值。
精确值及近似值的计算规则：

• 计算表达式中有近似值，就会当作近似表达式来计算
• 计算表达式中只包含整数精确值，会以BIGINT(64位)精度来计算
• 计算表达式中只包含精确值，但是包含带小数的值，会以DECIMAL(65位)精度来计算
• 计算表达式中包含字符串必须转化为数值，则会转化为双精度浮点值，按照近似表达式来计算

2) 位域值(b’val’/0bval，val由0，1串构成)

将位域值＋0或者使用CAST()函数可以将其转化位整数值

(2) 字符串值

1) 表示

• 可使用单引号，也可以使用双引号，最好使用单引号
• 也可以使用十六进制来表示一个字符串，X’val’/x’val’或者0xval，其中val由16进制字符构成。此表示在数字上下文中会被当成一个数，在字符串上下文中会当成字符串。
  注意：X/x记法val中16进制字符的个数必须位偶数个，否则会报错，因为每两位16进制字符会被处理成一个8位数字字节值
  

2) 分类

字符串值一般分为两类，即二进制串和非二进制串

• 二进制串：无特殊的比较和排序特性，所有比较是基于各字节的数值逐字节的比较
• 非二进制串：是一个字符序列，与字符集有关，排序特性取决与不同的字符集。可以使用CHARSET()以及COLLATION()来确定使用的字符集即排序规则
  二进制串转非二进制串CONVERT()

3) 一些注意点

• 字符串长度：使用CHAR_LENGTH()计算字符串的字符长度，使用LENGTH()计算字符串的字节长度

(3) 时态值（时间/日期）

• MySQL是按照’年-月-日’顺序来表示日期的，如’2020-11-10’，也可以使用DATE_FORMAT()及STR_TO_DATE()按任意方式来显示日期
• MySQL支持时间以及日期值的组合，如’2020-11-10T11:11:0’/‘2020-11-10 11:11:0’/‘2020-11-10 11:11:0.1213’

(4) 空间值

可支持实现点、线、多边形等这样的值

(5) 布尔值

TRUE常量使用1表示，FALSE常用使用0表示

(6) NULL值

MySQL中\N代表NULL值；可以使用ISNULL()函数来判断一个值是否是NULL

2.MySQL的数据类型

(1) 数值类型

1) 类型表

DECIMAL每9位需要一个字节，由小数部分和整数部分总共需要的字节数之和决定一共需要多少字节
BIT(M) 大约需要(M+7)/8个字节

2) 一些说明

• 整数和DECIMAL(可用来存放小数)是精确值类型；
• 浮点类型也可存放小数，与DECIMAL不同的是可能存放科学计数法的近似数；
• 带小数部分的值可以复制给整数，会按照“四舍五入”来处理；
• DECIMAL可以存储小数且是精确值类型，因此很适合用来存储货币值；
• DECIMAL(M,D)，其中M表示整个数据的位数，D表示小数位数，M取值位1_{65，D取值0}30；
• FLOAT(M,D)/DOUBLE(M,D)，M取值1_{255，D取值0}30。

3) 数字类型的属性

• UNSIGNED：除BIT之外其它数字类型都可设置；DECIMAL或者浮点数使用这个属性会直接在原有范围将负值砍掉；
• ZEROFILL：补0；除BIT之外其它数字类型都可设置；某列设置了此属性，会自动设置UNSIGNED属性；
• AUTO_INCREMENT：可用于整数或浮点类型；每个表至多只有一个列能设置该属性，且设置该属性的列会自动设置为NOT NULL；
• NULL/NOT NULL：设置在前面属性后面；
• DEFAULT：指定默认值。

(2) 字符串类型

1) 类型表

M表示列值的最大长度，L表示某个给定值的字节长度，* w *表示字符集中最宽的字符所占用的字节数。

2) 一些说明

• CHAR vs VARCHAR：CHAR长度固定，VARCHAR长度不固定，VARCHAR需要耗费字节来记录长度。BINARY与VARBINARY的区别类似；
• BLOB(binary large object) vs TEXT：BLOB存储二进制串，而TEXT存储非二进制串；都是以字节为单位来衡量最大长度的；
• ENUM vs SET：ENUM类型的值不允许同时出现，而SET类型的值允许同时出现；SET的成员之间使用逗号隔开，因此SET的成员内部不应该包含逗号；ENUM及SET定义时应该使用字符串，但是实际存储是以数字形式存储；ENUM中会对程序进行顺序编号，而SET的成为被存储为二进制位值，第一个成员对应0位，第二个成员对应第1位……。

3) 字符串类型的属性

• CHARACTER SET及COLLATE：用于指定字符集和排序规则；不适用与二进制串数据类型
• NULL/NOT NULL：即使设置了NOT NULL，字符串中也是可以存储’'值的
• DEFAULT：设置默认值

(3) 时态类型

1) 类型表

如果要声明包含小数位的时态类型，则需要如TypeName(fsp)进行声明，fsp位小数位精度，取值范围为0~6。

2) 一些说明

• 如果把DATE值赋值给DATETIME，时间部分会赋值为00:00:00；如果把DATETIME转为DATE或TIME，不相干的部分就会被去掉
  
• 年份在00_{69之间的值，会自动被转化为2000}2069；年份在70_{99之间的值会被转化为1970}1999；注意：00会转化为0000，需要使用’00’/‘0’;
  

3) 时态类型的属性

• NULL/NOT NULL：默认为NULL，TIMESTAMP除外，默认位NOT NULL，TIMESTAMP在设置为NULL时会自动设置为当前时间，只有在定义时设置为NULL才会存储NULL。
• DEFAULT：不能用CURRENT_TIMESTAMP()函数将DATE列的默认值设置为当前日期和时间，TIMESTAMP及DATETIME可以
• 自动初始化和自动更新特性：TIMESTAMP和DATETIME列可以具有自动初始化和自动更新属性，会自动初始化为当前时间戳，当其他列有改动时，TIMESTAMP及DATETIME会自动更新为当前时间戳；可以使用NO UPDATE CUR_TIMESTAMP来取消自动更新属性；利用DEFAULT CURRENT_TIMESTAMP赋于自动更新属性。

3. 处理无效数据值

(1) 默认情况下的处理

• 对于数值列或者TIME列：超出范围的值会被截断到离取值范围最近的那个端点；
• 对于除TIME以外的其他类型的时态列：非法值会被转化成和该类型相一致的“零”值；
• 对于字符串列，除ENUM和SET：过长的字符串会被截断到该列的最大长度；
• 对于ENUM及SET：需要根据定义中给出的合法值进行选取。对于枚举列，非合法值将作为出错成员，对于集合列，非法字符串(包含成员字符串)中的非成员字符串部分将被消除

(2) 插入或更新数据时执行更严格的检查

设置为以下两种模式：

SET sql_mode = 'STRICT_ALL_TABLES';
SET sql_mode = 'STRICT_TRANS_TABLES';

• 如果插入或者修改第一行时，发生某个值无效或者缺失时，则结果会产生一个错误，语句终止执行并回滚；
• 如果插入或者修改多行时，如果第一行后面的行发生值无效或者缺失时，两种模式的处理会不同，第一种模式下，语句停止执行(会存在部分更新的问题)；第二种模式下，会继续执行语句，并将无效值转化为与其最接近的合法值，缺失值转化为隐式默认值。

(3) 最严格的检查模式

• ERROR_FOR_DIVISION_BY_ZERO：遇到0为除数的情况下，会拒绝数值进入数据库
• NO_ZERO_IN_DATE：阻止月或者日部分以0值进入数据库
• NO_ZERO_DATA：阻止0日期值进入数据库

-- 启用第(2)(3)中模式
SET sql_mode = 'TRADITIONAL';
-- 启用第(2)中的第一种及(3)中的第一种
SET sql_mode = 'STRICT_ALL_TABLES,ERROR_FRO_DIVISION_BY_ZERO';

4. 处理序列

(1) 自增AUTO_INCREMENT属性

• 每个表智能由一列有该属性；
• 必须建立索引，如PRIMARY KEY以及UNIQUE等；
• NOT NULL约束；
• 可使用LAST_INSERT_ID()获取最近生成的序号值；
• 在NO_AUTO_VALUE_ON_ZERO模式，插入0值会导致存储值为0，而非下一个序号值；
• 插入一个非NULL非0的值，有两种情况，如果有一行已经使用了该值，则会发生键重复错误，否则正常插入；
• 如果插入一个大于当前下一个序号值的值，则这个序列将会被重置，可以使用这种方式达到增大计数器的目的。
  
• 使用UPDATE将某一列更新为0，那么它就会被置0，不论是否开始NO_AUTO_VALUE_ON_ZERO
  -
• 使用TRUNCATE TABLE清空表格后，序号会重用，如果不想序号重用可以使用DELETE FROM TableName WHERE TRUE，第一种方式是一次性删除表中所有的内容，包括索引等，第二种方式是一行一行的删除，会保留序列、索引等信息。

(2) InnoDB中的AUTO_INCREMENT和MyISAM中AUTO_INCREMENT的异同

MyISAM	InnoDB
同可以在表定义的时候使用AUTO_INCREMENT = n来设置初始编号，也可使用ALTER TABLE TableName AUTO_INCREMENT = n来设置初始编号，不能将编号设置比当前表中最大计数值小	可以在表定义的时候使用AUTO_INCREMENT = n来设置初始编号，也可使用ALTER TABLE TableName AUTO_INCREMENT = n来设置初始编号，不能将编号设置比当前表中最大计数值小
异行删除后，值不会被重用(使用TRUNCATE TABLE清空表后例外，会从1开始以及复合索引除外，可以重复)	由于InnoDB实在内存里维护一个计数器，未存储在表的内部，因此当在序列的顶端删除了值且重启了服务器，那么删除的值可以被重用且CREATE TABLE 以及 ALTER Table设置的AUTO_INCREMENT = n语句效果会消失。不能使用复合索引生成多个独立的序列

(3) 不使用AUTO_INCREMENT的情况下生成序列

UPDATE TableName SET ColNname = LAST_INSERT_ID(ColName+1);

可以生成步长不为1且可以是负数的序列编号

5. 表达式计算和类型转换

(1) 表达式编写及计算注意事项

• 调用内建函数时，函数名和紧随其后的左括号之间，不允许出现空格，启用IGNORE_SPACE模式后可有空格；
• MySQL中除了like可以进行模式匹配外，使用REGEXP(类似于linux系统中的正则表达式)也可以。

符号	作用
.	匹配任何单个字符。如’abc’ REGEXP ‘a.c’
[]	匹配列在方括号内的任何字符，如’e’ REGEXP [aeiou]
-	用于指定字符范围，两端分别为范围的起始字符和结束字符。如’abc’ REGEXP ‘[a-z]’
*	匹配任意个前面的字符，如’x*‘将匹配任意个数的’x’
+	匹配1个或多个前面的字符，如’x*‘将匹配一个或多个的’x’
^pattern/pattern$	当pattern出现在字符串开头/结尾时，模式匹配成功

• 运算符优先级
  

(2) 类型转化注意点

• 除<=>运算符之外，所有涉及NULL值的比较结果都为NULL（NULL<=> NULL的值为真）
• 非二进制串和二进制串之间的比较按照二进制串的方式进行