mysql数据类型及运算符

数据类型

mysql数据类型介绍
一、数据类型简介 1. 数据表由多列字段构成，每一个字段指定了不同的数据类型，指定了数据类型之后，也就决定了向字段
插入的数据内容
2. 不同的数据类型也决定了 MySQL 在存储它们的时候使用的方式，以及在使用它们的时候选择什么运算符
号进行运算
3. 数值数据类型：
整数： TINYINT 、SMALINT 、MEDIUMINT 、INT 、BIGINT 、
浮点数： FLOAT 、DOUBLE 、
定点数： DECIMAL
4. 日期/时间类型：YEAR 、TIME 、DATE 、DATETIME 、TIMESTAMP
5. 字符串类型：CHAR 、VARCHAR 、BINARY 、VARBINARY 、BLOB 、TEXT 、ENUM 、SET

二、数值类型简介
1.数值类型主要用来存储数字，不同的数值类型提供不同的取值范围，可以存储的值范围越大，所需要的
存储空间也越大
2.数值类型分为：①整数类型 ②浮点数类型 ③定点数类型

1、整数类型

数据类型	TINYINT	SMALLINT	MEDIUMINT	INT	BIGINT
说明	很小的整数	小的整数	中等大小的整数	普通大小的整数	大整数
存储需求	1个字节	2个字节	3个字节	4个字节	8个字节
有符号的取值范围	-128~127	-32768~32767	-8388608~8388607	-2147483648~2147483647	-9223372036854775808~9223372036854775807
无符号的取值范围	0~255	0~65535	0~16777215	0~4294967295	0~18446744073709551615

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

类型名 称	FLOAT	DOUBLE	DECIMAL
说明存储需求	单精度浮点数	双精度浮点数	压缩的"严格"定点数
有符号的取值范围	4个字节	8个字节	M+2个字节 不固定
无符号的取值范围	-3.402823466EE+38 ~ -1.175494251E-38	-1.7976931348623157E+308 ~-2.2250738585072014E-308	不固定

MySQL 日期/时间类型

1. MySQL 有多种表示日期的数据类型，比如，当只记录年信息的时候，可以使用 YEAR 类型，而没有必要使用 DATE 类型
2. 每一个类型都有合法的取值范围，当指定确实不合法的值时系统将 “零” 值插入到数据库中

类型名称	YEAR	TIME	DATE	DATETIME	TIMESTAMP
日期格式	YYYY	HH:MM:SS	YYYY-MM-DD	YYYY-MM-DD HH:MM:SS	YYYY-MM-DD HH:MM:SS
日期范围	1901 ~ 2155	-838:59:59 ~ 838:59:59	1000-01-01 ~ 9999-12-31	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07UTC
存储需求	1个字节	3个字节	3个字节	8个字节	4个字节

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
5. TIME 类型的格式为 HH:MM:SS ，HH 表示小时，MM 表示分钟，SS 表示秒
6. 格式：以 ‘HHMMSS’ 格式表示的 TIME ，例如 ‘101112’ 被理解为 10:11:12 ，但如果插入不合法的时间，如 ‘109712’ ，则被存储为 00:00:00
7. 格式：以 ‘D HH:MM:SS’ 字符串格式表示的 TIME ，其中 D 表示日，可以取 0 ~ 34 之间的值，在插入数据库的时候 D 会被转换成小时，如 ‘2 10:10’ 在数据库中表示为 58:10:00 ，即 2x24+10 = 58
3、DATE
1.DATE 类型的格式为 YYYY-MM-DD ，其中，YYYY 表示年，MM 表示月，DD 表示日
2.格式：‘YYYY-MM-DD’ 或 ‘YYYYMMDD’ ，取值范围为 ‘1000-01-01’ ~ ‘9999-12-3’
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-0100: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
8. 格式：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 是世界标准时间，存储时会对当前时区进行转换，检索时再转换回当前时区

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

文本字符串类型	CHAR(M)	VARCHAR(M)	TINYTEXT	TEXT	MEDIUMTEXT	LONGTEXT
说明	固定长度的文本字符串	可变长度的文本字符串	非常小的文本字符串	小的文本字符串	中等大小的文本字符串	大的文本字符串
存储需求	M 字节，1 <= M <= 255	L+1 字节，在此 L <= M 和 1 <= M <= 255	L+1 字节，在此 L < 2^8	L+2 字节，在此 L < 2^16	L+3 字节，在此 L < 2^24	L+4字节，在此 L < 2^32

文本字符串类型	ENUM	SET
说明	枚举类型，只能有一个枚举字符串值	一个设置，字符串对象可以有零个或多个SET成员
存储需求	1 或 2 个字节，取决于枚举值的数目（最大值65535）	1, 2, 3, 4 或 8 个字节，取决于集合成员的数量（最多64个成员）

二进制字符串类型	BIT(M)	BINARY(M)	VARBINARY(M)	TINYBLOB(M)	BLOB(M)	MEIDUMBLOB(M)	LONGBLOB(M)
说明	位字段类型	固定长度的二进制字符串	可变长度的二进制字符串	非常小的BLOB	小的BLOB	中等大小的BLOB	非常大的BLOB
存储需求	大约（M+7）/8 个字节	M个字节	M+1个字节	L+1 字节，在此 L < 2^8	L+2 字节，在此 L < 2^16	L+3 字节，在此 L < 2^24	L+4 字节，在此 L < 2^32

1、CHAR 和 VARCHAR
1.CHAR(M) 为固定长度的字符串，在定义时指定字符串列长，当保存时在右侧填充空格以达到指定的长度，M 表示列长度，取值范围是 0~255 个字符，例如，CHAR(4) 定义了一个固定长度的字符串列，其包含的字符个数最大为 4，当检索到 CHAR 值时，尾部的空格将被删掉

2.VARCHAR(M) 为可变长度的字符串，M 表示最大列长度，取值范围是 0~65535 ，VARCHAR 的最大实际长度由最长的行的大小和使用的字符集确定，而其实际占用的空间为字符串的实际长度加一（一个字符串结束符） 插入值 CHAR(4) 存储需求 VARCHAR(4) 存储需求 ‘’ ’ ’ 4个字节 ’ 1个字节 ‘ab’ ‘ab ’ 4个字节’ab’ 3个字节 ‘abc’ ‘abc’ 4个字节 ‘abc’ 4个字节 ‘abcd’ ‘abcd’ 4个字节 ‘abcd’ 5个字节 ‘abcde’ ‘abcd’ 4个字节 ‘abcd’ 5个字节

2、TEXT
1.TINYTEXT 最大长度为 255 个字符
2.TEXT 最大长度为 65536 个字符
3.MEDIUMTEXT 最大长度为 16777215 个字符
4.LONGTEXT 最大长度为 4294967295 个字符

3.ENUM
(1) 在基本的数据类型中，无外乎就是些数字和字符，但是某些事物是较难用数字和字符来准确地表示的。比如一周有七天，分别是Sunday、Monday、Tuesday、Wednesday、Thursday、Friday 和 Saturday。如果我们用整数 0、1、2、3、4、5、6 来表示这七天，那么多下来的那些整数该怎么办？而且这样的设置很容易让数据出错，即取值超出范围。我们能否自创一个数据类型，而数据的取值范围就是这七天呢？因此有了 ENUM 类型（Enumeration，枚举），它允许用户自己来定义一种数据类型，并且列出该数据类型的取值范围。ENUM 是一个字符串对象，其值为表创建时在列规定中枚举（即列举）的一列值，语法格式为：字段名 ENUM （‘值1’， ‘值2’， … ‘值n’） 字段名指将要定义的字段，值 n 指枚举列表中的第 n 个值，ENUM类型的字段在取值时，只能在指定的枚举列表中取，而且一次只能取一个。如果创建的成员中有空格时，其尾部的空格将自动删除。ENUM 值在内部用整数表示，每个枚举值均有一个索引值：列表值所允许的成员值从 1 开始编号，MySQL 存储的就是这个索引编号。枚举最多可以有 65535 个元素。

mysql> create table temp1
    -> (
    -> soc int,
    -> level enum('excellent','good','bad')                   # 自定义枚举值  
    -> );
mysql> insert into tmp1 values (70,'good'),(90,1),(75,2),(50,3)      # 插入数据，后面三个是根据索