第四章《MySQL的数据类型和运算符》

一、数据类型介绍：
（1）数据表由多个字段组成，每一个字段都指定了自己的数据类型，指定了数据类型后，也就决定了向字段插入数据的内容；
（2）不同的数据类型也决定了MySQL在存储数据的时候使用的方式，以及在使用数据的时候选择什么运算符进行运算；
（3）数值数据类型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT、FLOAT、DOUBLE、DECIMAL
（4）日期/时间数据：YEAR、TIME、DATE、DATETIME、TIMESTAMP
（5）字符串数据类型：CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM、SET
二、数值类数据类型：
（1）数值类数据类型主要用来存储数字，不同的数值类型提供不同的取值范围，可以存储的值范围越大，需要的存储空间也越大；
（2）数值型分为：整数类型，浮点数类型，定点数类型；

1.整数类型（整数类型的属性字段可以添加auto_increment自增约束条件）

2.浮点数类型和定点数类型：
（1）MySQL中使用浮点数和定点数来表示小数，浮点数有两种类型：单精度浮点数（FLOAT），
双精度浮点数（DOUBLE），定点数只有DECIMAL；
（2）浮点数和定点数都可以用（M，D）来表示，其中M是精度，表示总共的位数（不算点号），D是标度，表示小数的位数
（3）DECIMAL实际上是以字符串形式存放的，在对精度要求比较高的时候（如货币、科学数据等）使用DECIMAL类型比较好；
（4）浮点数相对于定点数的有点是在长度一定的情况下，浮点数能够表示更大的数据范围（取值范围更大），他的缺点是会引起精度的问题

3.日期时间类型;
(1)MySQL有多重表示日期的数据类型，比如：当只记录年份信息时，可以使用YEAR类型，而没有必要使用DATE类型；
（2）每一个类型都有合法的取值范围，当数据是不合法的值时，系统将“零”值插入到数据库中

1、YEAR
(1) 格式：以4位字符串格式表示的 YEAR ，范围为 ‘1901’ ~ ‘2155’
(2) 格式：以4位数字格式表示的 YEAR ，范围为 1901 ~ 2155
(3) 格式：以2位字符串格式表示的 YEAR ，范围为 ‘00’ ~ ‘99’ ，其中，‘00’ ~ ‘69’ 被转换为 2000 ~ 2069 ，‘70’ ~ ‘99’ 被转换为 1970 ~ 1999
(4) 格式：以2位数字格式表示的 YEAR ，范围为 1 ~ 99 ，其中，1 ~ 69 被转换为 2001 ~ 2069 ，70 ~ 99 被转换为 1970 ~ 1999

2、TIME
(1) TIME 类型的格式为 HH:MM:SS ，HH 表示小时，MM 表示分钟，SS 表示秒
(2) 格式：以 ‘HHMMSS’ 格式表示的 TIME ，例如 ‘101112’ 被理解为 10:11:12 ，但如果插入不合法的时间，如 ‘109712’ ，则被存储为 00:00:00
(3) 格式：以 ‘D HH:MM:SS’ 字符串格式表示的 TIME ，其中 D 表示日，可以取 0 ~ 34 之间的值，在插入数据库的时候 D 会被转换成小时，如 ‘2 10:10’ 在数据库中表示为 58:10:00 ，即 2×24+10 = 58

3、DATE
(1) DATE 类型的格式为 YYYY-MM-DD ，其中，YYYY 表示年，MM 表示月，DD 表示日
(2) 格式：‘YYYY-MM-DD’ 或 ‘YYYYMMDD’ ，取值范围为 ‘1000-01-01’ ~ ‘9999-12-31’
(3) 格式：‘YY-MM-DD’ 或 ‘YYMMDD’ ，这里 YY 表示两位的年值，范围为 ‘00’ ~ ‘99’ ，其中，‘00’ ~ ‘69’ 被转换为 2000 ~ 2069 ，‘70’ ~ ‘99’ 被转换为 1970 ~ 1999
(4) 格式：YY-MM-DD 或 YYMMDD ，数字格式表示的日期，其中 YY 范围为 00 ~ 99 ，其中，00 ~ 69 被转换为 2000 ~ 2069 ，70 ~ 99 被转换为 1970 ~ 1999

4、DATETIME
(1) DATETIME 类型的格式为 YYYY-MM-DD HH:MM:SS ，其中，YYYY 表示年，MM 表示月，DD 表示日，HH 表示小时，MM 表示分钟，SS 表示秒
(2) 格式：‘YYYY-MM-DD HH:MM:SS’ 或 ‘YYYYMMDDHHMMSS’ ，字符串格式，取值范围为 ‘1000-01-01 00:00:00’ ~ ‘9999-12-31 23:59:59’
(3) 格式：‘YY-MM-DD HH:MM:SS’ 或 ‘YYMMDDHHMMSS’ ，字符串格式，其中 YY 范围为 ‘00’ ~ ‘99’ ，其中，‘00’ ~ ‘69’ 被转换为 2000 ~ 2069 ，‘70’ ~ ‘99’ 被转换为 1970 ~ 1999
(4) 格式：YYYYMMDDHHMMSS 或 YYMMDDHHMMSS ，数字格式，取值范围同上

5、TIMESTAMP
(1) TIMESTAMP 类型的格式为 YYYY-MM-DD HH:MM:SS，显示宽度固定在19个字符
(2) TIMESTAMP 与 DATETIME 的区别在于，TIMESTAMP 的取值范围小于 DATETIME 的取值范围
(3) TIMESTAMP 的取值范围为 1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC ，其中 UTC 是世界标准时间，存储时会对当前时区进行转换，检索时再转换回当前时区

单纯日期类的数据类型的数据插入时可以不用字符串格式，但是有time的就必须用字符串格式
日期和时间类型，如果插入的数据时纯数字格式，可以不用引号，如果数据中有连字符，必须用引号；

字符串类型：
（1）字符串类型用来存储字符串的数据，还可以存储比如图片和声音的二进制数据
（2）MySQL支持两种字符串类型：文本字符串和二进制字符串

2.如何选择数据类型；
2.1整数和浮点数及定点数。
（1）如果不需要小数部分，则使用整数来保存数据，如果需要表示小数部分，则使用浮点数类型和定点数，对于浮点数据列，存入的数值会对该列定义的小数位进行四舍五入。
（2）浮点数据类型中，DOUBLE类型精度比FLOAT类型高，因此，如果要求存储精度较高时，应该选择DOUBLE类型。当然DOUBLE它占用的存储空间更大一点。
（3）浮点数相对于定点数DECIMAL的优势是:在长度一定的情况下，浮点数能表示的数据范围更大，但是由于浮点数容易产生误差，因此对精度要求高，建议用定点数；
（4）DECIMAL在MySQL当中是以字符串存储的，用于定义货币等对精度要求高的数据，在数据迁移中，FLOAT（M，N）是非标准的SQL定义，所以数据迁移时可能会出现问题，最好不要用；
（5）另外两种浮点数在进行运算的时候也容易出问题，所以如果进行数值的比较最好用定点数。

2.2日期和时间类型；
（1）如果只需要记录年末，用YEAR类型：如果只需要记录时间，用TIME类型；
（2）如果同时记录日期和时间可以使用DATETIME或者是TIMESTAMP这两种类型；
TIMESTAMP的取值范围小于DATETIME；
（3）默认情况下，当插入一条记录但并没有指定TIMESTAMP这个列值时，MySQL默认会把TIMESTAMP列设为当前时间

3.字符串；
3.1 CHAR和VARCHAR；
（1）CHAR（M）是固定长度字符，VARCHAR（M）是可变长度的字符；CHAR会自动删除插入数据尾部的空格，VARCHAR不会删除尾部空格,

CHAR(M): ‘abcd’ M字节 VARCHAR(M) ‘abcd ’ 6字节

(2)CHAR时固定长度，所以他的处理速度比VARCHAR的速度更快，但是他的特点时浪费存储空间；
（3）具体选择CHAR还是VARCHAR类型，也要参考数据表的存储引擎，对于yISAM引擎，最好使用固定长度的数据列代替可变长度的数据列。这样可以使整个表静态化，从而使数据检索速度更快，用空间换时间。对于InnoDB引擎，使用可变长度的数据列，因为InnoDB数据表的存储格式不分固定长度和可变长度，因此使用CHAR不一定比使用VARCHAR更快，但由于VARCHAR是按照实际的长度来存储的，比较节省空间，对磁盘I/O和数据存储总量比较好。

3.2BIT
（1）BIT数据类型用来保存位字段值，即以二进制的形式保存数据；例如：保存13，则实际保存的是13的二进制值，1101；
（2）BIT是位字段类型，BIT(M)中的M表示每个值的位数，范围1-64，如果M被省略，则默认M为1，如果BIT（M）列分配的长度小于M位，则在值的左边用0/来填充；
（3）如果需要位数至少为4位的BIT类型，即可定义BIT（4），则转换为二进制后大于1111的数据插入时，会有误差，它只能显示位数为4的最大值，比如我们插入的值是16，16的二进制是10000，他的位数是5，但是我们规定的是4，所以只能存入15，就产生误差

创建一个表（二进制）

查看数据类型为BIT的二进制

3.3BINARY（M）和VARBINARY（M）
（1）BINARY和VARBINARY类型类似于CHAR和VARCHAR，不同的是他们包含二进制字符串；
（2）BINARY类型的长度是固定的，指定长度后，不足最大长度的，将在他们右边填充，‘\x00’以补齐指定长度
（3）VARBINARY类型的长度是可变的，指定长度后，其长度可以在0到最大值之间

3.4 BLOB和TEXT
（1）BLOB是二进制字符串，TEXT是非二进制字符串，两者均可存放大容量的信息，BLOB主要存放图片，音频等信息，而TEXT只能存放纯文本文件。

3.5 ENUM和SET
（1）ENUM只能取单值（只能取他给出的值的其中之一），他的数据列表是一个枚举集合，他的例举的值最多可以有65535个；
（2）SET可以取多个值，他的列表中最多可例举64个值
（3）ENUM和SET的值是以字符串形式出现的，但在内部MySQL以数值的形式进行存储；
ENUM：
（1）在基本的数据类型中，无外乎就是写数字和字符串，但是某些事物是较难用数字和字符来准确的表示，比如一周有7天，分别是Sunday、Monday、Tuesday、wednesday、thursday、friday、saturday，如果我们用数字0、1、2、3、4、5、6来表示这七天，那么多下来的数字该怎么办呢？而且这样的设置很容易让数据出错，超出取值范围，这种情况下，我们就可以用枚举这种数据类型来自定义取值范围，ENUM是一个字符串对象，其值为表创建时，在字段中规定的ENUM例举出来的那些值中的某一个。
语法：
字段名 ENUM （‘值1’，‘值2’，‘值3’，。。。）；
我们在取枚举中的值的时候还可以使用它的（索引）下标，下标的值从1 开始。

往表里插入数据的语法：
INSERT INTO 表名 （字段1、字段2，。。。） values（值1，值2，。。。），（值1，值2），。。。
可以同时插入多组

SET：
（1）SET是一个字符串对象，可以有零个或多个值，SET列最多可以有64个成员，其值为标创建时规定的一列值，语法：SET （‘值1’，‘值2’，。。。‘值n’）
（2）与ENUM类型相同，SET值在内部用整数表示，列表中每一个值都有一个索引编号
（3）与ENUM类型不同的是 ，ENUM类型的字段只能从定义的列值中选择一个值插入，而SET类型的列可从定义的列值中选择多个字符的联合
（4）如果插入SET字段中列值有重复，则MySQL自动删除重复的值，插入SET字段的值的顺序并不重要，MySQL会在存入数据库时，按照定义的顺序显示

更新表数据的语法;
UPDATE 表名 SET 字段名=‘值’ WHERE

三、运算符介绍：
1.mysql的算数运算符；
2.比较运算符；
3.逻辑运算符
4.位运算符

1算数运算符
（1）+：加法运算；
（2） – ：减法运算
（3）* ：乘法运算
（4）/： 除法运算
（5）DIV：求商
（6）MOD或者% ：求余/取模



2.比较运算符：
= 等于
<=> 安全等于 （和 “=”的区别是“<=>”可以判断null）
<> 不等于 ，也可以写成！=

 >      大于
 <      小于
>=        大于等于
<=      小于等于

IS NULL 判断一个值是否为空值NULL （这个要判断的值写在前面）
ISNULL （值）同上

IS NOT NULL 判断一个值是否不为NULL

GREATEST(值1，值2，。。。） 当有两个或多个参数时，取其中的最大值，如果参数里有NULL，显示NULL

LEAST (值1，值2，。。。） 当有两个或多个参数时，取其中的最小值，如果参数里有NULL，显示NULL

值 BETWEEN min值 AND max值 判断一个值是否在两个范围内，
min值是最小值 max是最大值
min<=值<=max

值 IN 判断值是否在IN给出的范围内在返回1，不在返回0；in后面给的值由NULL的时候如果我们要判断这个值在其中则返回1 ，不在返回NULL。


NOT IN 判断值是否不在后面给出的范围内，在返回1，不在返回0；in后面给的值由NULL的时候如果我们要判断这个值在其中则返回1 ，不在返回NULL。

LIKE 模糊匹配
REGEXP 正则表达式

LIKE：
（1）like运算符用来匹配字符串，如果匹配则返回1，如果不匹配则返回0；
（2）like经常使用两种通配符，’%’ 用于匹配任何数据的字符，包括零字符：‘_’只能匹配单个字符；




REGEXP ：
（1）REGEXP 运算符用来匹配字符串，如果匹配返回1，如果不匹配返回0；
（2）REGEXP 使用的几种通配符；
‘^’ 用来匹配以什么开头的字符串；
‘$’ 用来匹配以什么结尾的字符串；
‘.’ 用来匹配任何一个单字符串；
‘[]’ 用来匹配方括号里面的任意字符串；
‘*’用来匹配零个或多个在它前面的字符；

3.逻辑运算符：
运算符 作用
NOT或！ 逻辑非
AND 或&& 逻辑与
OR 或 || 逻辑或
XOR 逻辑异或
1.逻辑非（NOT或！）
（1）当操作数为0时，返回1；
（2）当操作数不为0时，返回0；
（3）当操作数为NULL时，返回NULL；

2.逻辑与（and或&&）；
（1）当所有操作数均为非0值，并且不为NULL时，返回1
（2）当一个或多个操作数为0时，返回0；
（3）当操作数里没有0但是有NULL，返回NULL；


3.逻辑或(OR或||)；
（1）任意一个操作数为非零值时，且不是NULL，结果为1.否则为0；
（2）当两个操作数均为NULL时，或者一个为0一个为NULL时，返回NULL；

4.逻辑异或：（XOR）
（1）任何一个操作数为NULL，返回NULL；
（2）对于非NULL的操作数，如果两个操作数时非0值或者都是0值，则返回0；
（3）如果一个为0值，一个为非0值，返回1，
（4）a xor b的判断计算等同于a and （not b）或者（not a）and b；

4.位运算符：
运算符 作用
| 位或
& 位与
^ 位异或
<< 位左移
>> 位右移
~ 位取反

 1.位或运算符（|）：多个操作数对应的二进位有一个或多个1，则该位的运算结果为1，否则为0；
 比如10的二进制是1010，14的二进制是1110，他两个的位或运算结果1110 ，转换成十进制就是14

2.位与运算符（&）：对应二进制全为1时，结果位为1，否则为0；
3.位异或（^）：对应的二进制位不相同时，结果位为1，否则为0；
4.位左移（<<）:将一个值的二进制位上的1向左移动指定的位数
5.位右移（>>）:使一个数值的二进制位上的1往右移动指定的单位，左边被移走的1用0来补充，右边被移出边界的1被舍弃
6.位取反（~）：将对应的二进制数逐位取反（1变为0，0变为1）；