mysql数据类型和运算符

数据类型

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

1.1 整数类型

默认存的是有符号数

类型名称	说明	存储需求	有符号数	无符号数
tinyint	很小的整数	一个字节	-128~127	0~255
smallint	小的整数	两个字节	-2^15~2^15-1	0~2^16-1
mediumint	中等大小的整数	三个字节	-2^23~2^23-1	0~2^24-1
int	普通大小的整数	四个字节	-2^31~2^31-1	0~2^32-1
bigint	大整数	八个字节	-2^63~2^63-1	0~2^64-1

create table int_test(
    a tinyint,
    b smallint,
    c mediumint,
    d int,
    e bigint
);

1.2 浮点数类型和定点数类型

类型名称	说明	存储需求	有符号数	无符号数
float	单精度浮点数	4个字节	-3.402823466E+38~-1.175494351E-38	0和1.175494351E-38~3.402823466E+38
double	双精度浮点数	8个字节	-1.7976931348623157E+38~-2.2250738585072014E-308	0和-1.7976931348623157E-308~-2.2250738585072014E+308
decimal(M,D),dec	压缩的‘严格’定点数	M+2个字节	最大取值范围和double一样

//float(M,N)中M 称为精度，表示总共显示的位数，N称为标度，表示小数的位数
create table float_test(
    a float,
    b double,
    c decimal,
    a1 float(5,1),
    b1 double(5,1),
    c1 decimal(5,1)
);
//float和double会自动四舍五入，decimal也会四舍五入但会有一个warning，看下图
insert into float_test (a1,b1,c1) values(5.12,5.15,5.16);

1.3 日期与时间类型

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

1.3.1 year

1. 以4为字符串或者4位数字格式表示year。输入格式为‘2018’或2018，数据库的数据均为2018。
2. 以两位字符传串格式表示year，范围为‘00~’99’。’00’~’69’转化为2000~2069，’70’~’99’转化为1970~1999，’0’和’00’一样转化为2000。不在此范围内的，会出现超出范围异常：Out of range value for column ‘a’ at row 1。
3. 以两位数字表示year，范围为1~99。1~69转化为2001~2069,70~99转化为1970~1999。0会转化为0000，其他不在范围的值会报超出范围异常：Out of range value for column ‘a’ at row 1。

1.3.2 time

1. ‘D HH:MM:SS’格式的字符串。还可以使用下面任何一种“非严格”的语法：’HH:MM:SS’、’HH:MM’、’D HH:MM’、’D HH’、’SS’。这里的D表示日，可以去0~34之间的值，在插入数据库时，D会被转换为小时保存，格式为“D*24+HH”，在使用’D HH’时，HH一定要是两位的。
2. ‘HHMMSS’格式的字符串或者数值，需要为有意义的时间，如果不合法会报错，不正常的时间值：Incorrect time value: ‘111299’ for column ‘b’ at row 1。
3. now()，插入当前系统时间。

4. time不支持不严格的语法，不能像date和datetime一样，使用任何的标点符号作为时间部分的间隔符。

   

注意
如果没有冒号的格式，mysql解释时，会假定最右边的两位表示秒，1112和’1112’表示00:11:12,13或‘13’表示00:00:13。
如果使用冒号，‘11:12’表示11:12:00。

1.3.3 date

1. 以‘YYYY-MM-DD’、’YYYYMMDD’和YYYYMMDD，形式均可保存。
2. 以’YY-MM-DD’、’YYMMDD’和YYMMDD，其中00~69表示2000~2069,70~99表示1970~1999
3. current_date和now()，插入当前系统日期。

4. 不严格的语法，任何的标点符号都可以用作日期部分之间的间隔符。例如‘98-11-31’、‘98.11.31’、‘98/11/13’、‘98@11@13’、‘98-11@13’是等价的，都可以正确的插入到数据库中。

   

1.3.4 datetime

1. 以’YYYY-MM-DD HH:MM:SS’、’YYYYMMDDHHMMSS’字符串格式输入
2. 以’YY-MM-DD HH:MM:SS’、’YYMMDDHHMMSS’字符串格式输入，其中年份的表示同之前的year和date一样。
3. 以YYMMDDHHMMSS、YYYYMMDDHHMMSS数字格式输入。
4. 允许不严格的语法，任何标点符号都可以作为日期或时间部分之间的间隔符，同date一样

1.3.5 timestamp

1. 输入格式和datetime一样
2. 存储的字节和支持的范围不一样，见1.3的表格。
3. 最大区别：datetime存入的数据与时区无关。如果在东八区存入10点的值，那么改动时区为东十区，则取出来还是10点。如果是timestamp，则取出来的是12点。

1.3.6 date、datetime、timestamp转换

1. 如果为datetime、timestamp分配一个date值，则时间部分为设置为00:00:00。
2. 如果为date分配一个datetime、timestamp值，则时间部分的值被删除。

1.4 文本字符串类型

1. 数据字符编码：gbk，则一个字符占用2个字节，utf-8，则一个字符最多可占用3个字节
2. 下表中的L 表示实际字符串的长度。

类型名称	说明	存储需求
char(M)	固定长度非二进制字符串	(每个字符所占的字节数之和)字节，M个字符长度，一个中文也算一个字符，1<=M<=255,当超出范围，会报错不会自动转换
varchar(M)	变长非二进制字符串	(每个字符所占的字节数之和)+1字节，L<=M。M最大65535个字节，字符编码gbk时，M最大32766，utf-8时，21845
tinytext	非常小的非二进制字符串	(每个字符所占的字节数之和)+1字节，L<2^8
text	小的非二进制字符串	(每个字符所占的字节数之和)+2字节，L<2^16
mediumtext	中等的非二进制字符串	(每个字符所占的字节数之和)+3字节，L<2^24
longtext	大的非二进制字符串	(每个字符所占的字节数之和)+4字节，L<2^32
enum	枚举类型，只能有一个枚举字符串值	1或2个字节，取决于枚举值的数目（最大65535(2^16-1)）
set	一个设置，一个字符串对象可以有零个或多个set成员	1、2、3、4或8个字节，取决于集合成员的数量（最多64个）

1.4.1 char和varchar类型

1. char(M)为固定长度的字符串，在定义时指定字符串的列长。当保存时在右侧填充空格以达到指定的长度。当select char值时，尾部的空格会被删除。所以当保存的数据末尾有空格时，在取值时，空格会没有。
2. varchar(M)为长度可变的字符串，M表示可以存储的最长值，例如varchar(50)，表示最长可以存储50个字符，如果实际插入的字符串只有10个字符，则数据库实际存储的为10个字符和一个字符串结束字符。varchar在值保存和检索时，尾部的空格仍保留。

1.4.2 text类型

当保存text类型的值时，不删除尾部空格。

1.4.3 enum类型

enum列总有一个默认值，如果将enum声明为null，null值则为该列的一个有效值，并且默认值为null。如果enum列被声明为not null，其默认值为允许的值列表的第一个元素。

1.4.4 set类型

1. set是一个字符串对象，可以有零或多个值，最多可以有64个成员，其值为创建时规定的一列值。
2. 以enum类型相同，set的值在内部用整数表示，列中的每一个值都有一个索引编号。当创建表时，set成员值尾部的空格将会自动被删除。也只能插入定义列中的值，如果set的值，存在重复会默认去重
3. 与enum不同，enum字段只能从定义列值中选择一个值插入，set类型的列，可以从定义的列值中选择多个字符，中间以逗号“,”分割。

4. 只能插入定义列中的值；插入的值存在重复会默认去重；如果插入时不是按顺序排列，则会自动按顺序插入；

   

1.5 二进制字符串类型

2 常见运算符

1. 算数运算符，加减乘除，取余（模运算%）
2. 比较运算符：大于，小于，等于，大于等于，小于等于，不等于，in，between and，is null ，greatest，least，like，regexp等
3. 逻辑运算符：逻辑非（not或！），逻辑与（and 或者 &&），逻辑或（or 或 ||），逻辑异或（xor）
4. 位操作运算，位操作运算参与运算的操作数按二进制位进行运算。位与（&），位或（|），位非（~），位异或（^），左移（<<），右移（>>）。

2.1 算数运算符

1. 整数相除保留四位有效数字
2. 被除数为0，则返回null

2.2 比较运算符

2.2.1 等于（=）

1. 若有一个或两个参数为null，则比较运算的结果为null
2. 若同一个比较运算中的两个参数都是字符串，则按照字符串进行比较（Windows下不区分大小写，在字段前使用binary关键字）
3. 若两个参数均为整数，则按整数进行比较
4. 若一个字符串和一个整数进行比较，则mysql自动将字符串转换成数字，不能讲非数字型字符串转化成数字比较

2.2.2 安全等于（<=>）

和等于唯一的区别：<=>可以对null进行判断，两个都为null返回true，一个不为null返回false。

2.2.3 不等于（<>,!=），小于等于，小于，大于等于，大于

不能用于判断空值null。与null比较返回null。

2.2.4 is null（isnull），in not null

判断是否为null。

2.2.5 between and

可以比较数字也可以比较字符串

2.2.6 least

比较多列，取最小值
如果有null，则返回null

2.2.7 greatest

比较多列，取最大值
如果有null，则返回null

2.2.8 in，not in

不能用来判断null。

2.2.9 like

1. % 匹配任何数目的字符，包括零个字符
2. _ 只能匹配一个字符
3. 变量或匹配条件为null，则返回null。

2.2.10 regexp

使用正则表达式匹配
变量或匹配条件为null，则返回null。

2.3 逻辑运算符

所有逻辑运算符的求值所得的结果均为true，false，null。

2.3.1 not，！

如果操作数为null，则返回null

2.3.2 and，&&，or，||，xor

如果操作数含有null，则返回null
a xor b 等同于 (a and (not b)),((not a) and b)

2.4 位运算符