数据类型和运算符

MySql支持多种数据类型，主要有数值类型、日期/时间类型和字符串类型。

一、MySql数据类型介绍

• 数值数据类型：整数型tinyint、smallint、mediumint、int、bigint；浮点数类型float、double；定点小数类型decimal。
• 日期/时间类型：year、time、date、datetime、timestamp。
• 字符串类型：char、varchar、binary、varbinary、blob、text、enum、set。

   1、整数类型

   整数类型用来存储数字，可以添加auto_increment自增约束条件。

   1个字节（8bits）来存储，那么tinyint无符号数的最大值为2的8次方减一，也就是255。

MySql中的整数型数据类型

类型名称	说明	存储需求	有符号	无符号
tinyint	很小的整数	1个字节	-128~127	0~255
smallint	小的整数	2个字节	-32768~32767	0~65535
mediumint	中等大小的整数	3个字节	-8388608~8388607	0~16777215
int（Integer）	普通大小的整数	4个字节	-2147483648~2147483647	0~4294967295
bigint	大整数	8个字节	-9223372036854775808~9223372036854775807	0~18446744073709551615

  假设创建数据库时声明一个int类型的字段：year int(11)，该声明指明，在year字段中的数据一般只显示4位数字的宽度。

  注意：显示宽度和数据类型的取值范围无关。显示宽度只是指明MySql最大可能显示的数字个数，数值的位数小于指定的宽度时会由空格填充；如果出入了大于显示宽度的值，只要该值不超过该类型整数的取值范围，数值依然可以插入而且能显示出来。例如向year里面插入19999，select时还是会显示19999。

如果创建数据库的时候没有指定数据类型的宽度，MySql会默认给它们最大的宽度，要注意符号也算进宽度中。

2、浮点数类型和定点数类型

MySql使用浮点数类型和定点数类型来标识小数，浮点：float和double，定点：decimal；它们都可以用（M,N）来表示，其中M称为精度，表示总共的位数，N称为标度，表示小数的位数。

MySql中的小数类型

类型名称	说明	存储需求	有符号	无符号
float	单精度浮点数	4个字节	-3.402823466E+38~-1.175494351E-38	0和1.175494351E-38~3.402823466E+38
double	双精度浮点数	8个字节	-1.7976931348623157E+308~-2.2250738585072014E-308	0和2.2250738585072014E-308~1.7976931348623157E+308
decimal（M，D），DEC	压缩的“严格”定点数	M+2个字节	--------------------------	--------------------------

decimal的存储空间并不是固定的，而由其精度值M决定，占用M+2个字节。

这三个数据类型在用户指定精度之后，超出精度范围的都会进行四舍五入处理。

3、日期与时间类型

 MySql中有多种表示日期的数据类型，如果插入不合法的值时系统将会将“零”值插入到数据库中。

日期与时间数据类型

类型名称	日期格式	日期范围	存储需求
year	YYYY	1901~2155	1字节
time	HH：MM：SS	-838:59:59~838:59:59	3字节
date	YYYY-MM-DD	1000-01-01~9999-12-31	3字节
datetime	YYYY-MM-DD HH：MM：SS	1000-01-01 00:00:00~9999-12-31 23:59:59	8字节
timestamp	YYYY-MM-DD HH：MM：SS	1970-01-01 00:00:00 UTC~2038-01-19 03:14:07 UTC	4字节

• year类型用来表示年。以四位字符串或者四位数字表示时，输入格式为YYYY或者‘YYYY’，范围是1901~2155，如输入‘2010’和2010，插入到数据库都是2010；以两位字符串或者两位数字表示year，范围是00~99，其中00~69和70~99分别表示2000~2069和1970~1999，这里如果直接输入0，会被转化成0000，而不是2000，输入‘0’会被转换成2000。例：‘2166’保存在year会报错，‘0’、‘00’、‘77’、‘10’分别会转换成2000、2000、1977、2010；0、78、11分别会转换成0000、1978、2011.
• time类型用于只需要时间信息的值。格式为“HH:MM:SS”,分别表示小时、分钟、秒。取值范围是-838:59:59~838:59:59，小时范围会如此大的原因是：time类型不仅可以表示一天的时间，还可能是某个事件过去的时间或者两个事件之间的时间间隔。‘D HH:MM:SS’也可以使用 ‘HH:MM:SS’、‘HH:MM’、‘D HH:MM’、‘D HH’或‘SS’。这里的D表示日，可取0~34之间的值，在插入数据库时2，D会转换为小时保存，格式是：“D*24+HH”。时间也可以使用‘HHMMSS’表示，但是小时分钟秒均不能超过他们的范围。例：‘10:05:05’、‘23:23’、‘2 10:10’、‘3 02’、‘10’分别会转换成10:05:05、23:23:23、58:10:00、74;00:00、00:00:10；‘101112’、111213，‘0’、107010分别会转换成10:11:12、11:12:13、00:00:00、报错。注意：time简写时，没有冒号，如‘1122’和1122会将最右边的两位视为秒，会转换成00:11:22；而有冒号时不一样，如‘11:12’会被转化成11:12:00。使用current_time和now()均可获得系统当前时间。
• date类型，用在仅需要日期值时，格式‘YYYY-MM-DD’,可以使用字符串或者数字类型插入，只要符合格式就行。可以使用‘YYYY-MM-DD’或者‘YYYYMMDD’；也可使用‘YY-MM-DD’或者‘YYMMDD’或YY-MM-DD和YYMMDD，‘00’~‘69’和‘70’~‘99’分别表示2000~2069和1970~1999；使用current_date和now()可以获得当前日期。
• datetime类型同时包含日期和时间。格式为：‘YYYY-MM-DD HH:MM:SS’或者‘YYYYMMDD HHMMSS’；也可以使用‘YY-MM-DD HH:MM:SS’或者‘YYMMDD HHMMSS’，YY表示年份，规则和date类型一样；可以使用now()获取当前时间。
• timestamp小时格式和datetime一样，只是timestamp的取值范围小一些

  4、字符串类型

    字符串类型用来存储字符串数据，除此之外还可以存储其它数据，如图片和声音的二进制数据。

MySql中字符串数据类型

类型名称	说明	存储需求
char(M)	固定长度非二进制字符串	M字节，1<=M<=255
varchar(M)	变长非二进制字符串	L+1字节，在此L<=M和1<=M<=255
tinytext	非常小的非二进制字符串	L+1字节，在此 L 小于2的8次方
text	小的非二进制字符串	L+2字节，在此 L 小于2的16次方
mediumtext	中等大小的非二进制字符串	L+3字节，在此 L 小于2的24次方
longtext	大的非二进制字符串	L+4字节，在此 L 小于2的32次方
enum	枚举类型，只能有一个枚举字符串值	1或2个字节，取决于枚举值的数目（最大值65535）
set	一个设置，字符串对象可以有0个或多个set成员	1、2、3、4或8个字节，取决于集合成员的数量（最多64个成员）

这里面有些数据类型很少用到，常用的就是varchar。

5、二进制类型

MySql中二进制数据类型

类型名称	说明	存储需求
bit(M)	位字段类型	大约（M+7）/8个字节
binary(M)	固定长度的二进制字符串	M个字节
varbinary(M)	可变长度的二进制字符串	M+1个字节
tinyblob(M)	非常小的blob	L+1个字节，在此 L<2的8次方
blob(M)	小的blob	L+2个字节，在此 L<2的16次方
mediumblob(M)	中等大小的blob	L+3个字节，在此 L<2的24次方
longblob(M)	非常大的blob	L+4个字节，在此 L<2的32次方

二、数据类型的选择

1、整数和浮点数

不需要小数部分，自然使用整数类型；需要小数就是用浮点数类型。精度要求高使用double，不高使用float。

2、浮点数和定点数

浮点数容易产生误差，对精度要求很高时使用decimal，decimal可用于定义货币等要求精度高的字段，要进行数值比较，最好使用decimal。

3、日期与时间类型

按照要求使用即可，比如只取年份时使用year，只记录时间用time就行了。

4、char和varchar

按需求使用即可。

5、enum和set

enum只能去单值，它的数据列表是一个枚举集合。set可取多值，它的合法值列表最多允许有64个成员。

6、blob和text

blob是二进制字符串，text是非二进制字符串，两者均可存放大容量的信息，blob主要存储图片、音频信息；text只能存储纯文本。

三、常见运算符的介绍

1、算数运算符

Mysql中的算术运算符

运算符	作用
+	加法运算
-	减法运算
*	乘法运算
/	除法运算，返回商
%	取模运算，返回余数

2、比较运算符

Mysql中的比较运算符

运算符	作用
=	等于
<=>	安全的等于
<>(!=)	不等于
<=	小于等于
>=	大于等于
>	大于
IS NULL	判断一个值是否为NULL
IS NOT NULL	判断一个值是否不为NULL
LEAST    语法：LEAST（值1,值2,...值n）	在有两个或多个参数时，取最小值
GREATEST  语法：GREATEST (值1，值2，..值n)	在有两个或多个参数时，取最大值
between and    语法：expr between min and max	判断一个值是否落在两个值之间，min<=expr<=max
ISNULL	与IS NULL相同的功能
IN	判断一个值是否是IN列表中的任意一个值
NOT IN	判断一个值不是IN列表中的任意一个值
LIKE	通配符匹配（“%”匹配任何数目的字符，甚至包括零字符；“_”匹配一个字符）
REGEXP	正则表达式匹配

正则匹配符：

• “^”匹配以该字符后面的字符开头的字符串
• “$”匹配以该字符前面的字符结尾的字符串
• “.”匹配任何一个单字符
• “[...]”匹配在方括号内的任何字符
• “*”匹配零个或多个在它前面的字符

3、逻辑运算符

MySql中的逻辑运算符

运算符	作用
NOT或者！	逻辑非
AND或者&&	逻辑与
OR或者||	逻辑或
XOR	逻辑异或（不同返回1，相同返回0）

4、位运算符

MySql中的位运算符

运算符	作用
|	位或
&	位与
^	位异或（不同返回1，相同返回0）
<<	位左移（例：expr<<n，这里n表示expr要移位的位数）
>>	位右移（例：expr>>n，这里n表示expr要移位的位数）
~	位取反（注意：可以使用bin()查看1取反的结果：bin(~1)=111111111111111111111111111111110）

5、运算符的优先级

运算符按优先级由低到高排列

优先级	运算符
最低	=（赋值运算），：=
	||、OR
	XOR
	&&、AND
	NOT
	BETWEEN、CASE、WHEN、THEN、ELSE
	=（比较运算）、<=>、>=、<=、>、<、<>、!=、IS、LIKE、REGEXP、IN
	|
	&
	<<、>>
	-、+
	*、/(DIV)、%(MOD)
	^
	-（负号）、~（位反转）
最高	!