本文详细介绍了MySQL中的各种数据类型，包括数值、日期/时间、字符串类型，并深入解析了算术、比较、逻辑和位运算符的使用。通过实例展示了每种数据类型的特点及运算符的具体操作。
目录
1、数据类型
mysql支持多种数据类型，主要有数值数据类型，时间/日期类型和字符串类型
- 数值数据类型：包括整数类型TINYINT、SMALLINT、INT、MEDIUINT、BIGINT，浮点小数数据类型FLOAT和DOUBLE，定点小数类型DECEIMAL。
- 日期/时间类型：包括YEAR、TIME、DATE、DATETIME和TIMESTAMP。
- 字符串类型：包括CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET等。字符串类型又分文本字符串类型和二进制字符串类型。
1.1整数类型
整数型数据类型
| 数据类型 | 说明 | 存储需求 |
|---|---|---|
| TINYINT | 很小的整数 | 1个字节 |
| SNALLINT | 小的整数 | 2个字节 |
| MEDIUMINT | 中等大小的整数 | 3个字节 |
| INT(INTEGER) | 普通大小的整数 | 4个字节 |
| BINGINT | 大整数 | 8个字节 |
整数类型的取值范围
| 数据类型 | 有符号 | 无符号 |
|---|---|---|
| TINYINT | (-128，127) | (0，255) |
| SNALLINT | (-32768，32767) | (0，65 535) |
| MEDIUMINT | (-8388608，8388607) | (0，16777215) |
| INT(INTEGER) | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) |
| BINGINT | (-9223372036854775,808，9223 372036854775807) | (0，18446744073 709551615) |
1.2浮点数类型和定点数类型
mysql中使用浮点数类型和定点数类型表示小数。
浮点数据类型有两种，单精度浮点型（FLOAT）和双精度浮点型（DOUBLE）。定点类型只有一种，DECIMAL。浮点和定点数据类型都可以用（M,N）来表示，其中M称为精度，表示总共的位数，N称为标度，表示小数的位数。
mysql中的小数类型
| 类型名称 | 说明 | 存储需求 |
|---|---|---|
| FLOAT | 单精度浮点 | 4个字节 |
| DOUBLE | 双精度浮点 | 8个字节 |
| DECIMAL(M,D),DEC | 压缩的严格定点数 | M+2个字节 |
小数类型取值范围
FLOAT:
- 有符号： (-3.402823466 E+38，-1.175494351 E-38)，0，(1.175 494351E-38，3.402823466351E+38)
- 无符号：0，(1.175494351 E-38，3.402823466E+38)
DOUBLE:
- 有符号： (-1.7976931348623157 E+308，-2.2250738585072014 E-308)，0，(2.2250738585072014 E-308，1.7976931348623157 E+308)
- 无符号：0，(2.2250738585072014 E-308，1.7976931348623157E+308)
DECIMAL:依赖于M和D值
1.3日期与时间类型
日期与时间数据类型
| 类型名称 | 日期格式 | 时间范围 | 存储需求 |
|---|---|---|---|
| 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:01 UTC~2038-01-19 03:14:07 UTC | 4字节 |
1.4文本字符串类型
文本字符串数据类型
| 数据类型 | 说明 | 存储需求 |
|---|---|---|
| CHAR(M) | 固定长度字符串 | M字节，1<=M<=255 |
| VARCHAR(M) | 变长字符串 | M字节,M<65535 |
| TINYTEXT(M) | 短文本字符串 | M字节,M<255 |
| MEDIUMTEXT(M) | 中等长本字符串 | M字节,M<=16777215 |
| LONGTEXT(M) | 极大文本字符串 | M字节,M<4294967295 |
| ENUM(M) | 短文本字符串 | 取决于枚举值的数目（最大65535） |
| SET(M) | 短文本字符串 | 取决于集合成员的数量（最多64个成员） |
1.5二进制字符串类型
| 类型名称 | 说明 | 存储需求 |
|---|---|---|
| BIT(M) | 位字段类型 | 8个字节 |
| BINARY(M) | 固定长度二进制字符串 | M个字节 |
| VARBINARY(M) | 可变长度二进制字符串 | M+1个字节 |
| TINYBLOB(M) | 非常小的BLOB | 8个字节 |
| BLOB(M) | 小BLOB | M+1,<2^8个字节 |
| MEDIUNBLOB(M) | M+2,<2^16个字节 中等大小的BLOB | M+3,<2^24个字节 |
| LONGBLOB(M) | 非常大的BLOB | M+4,<2^32个字节 |
ENUM类型
ENUM是一个字符串对象，其值为表创建时在列规定中枚举的一列值。
字段名 ENUM('值1','值2',...,'值n')
ENUM的值在内部用整数表示，每个枚举值均有一个索引，成员值从1开始。
例子：
创建表，定义一个ENUM类型的列enm
mysql> create table tmp9(
-> enm ENUM("first","second","third")
-> );
Query OK, 0 rows affected (0.10 sec)
enm列的取值范围如下
| 值 | 索引 |
|---|---|
| NULL | NULL |
| ‘’ | 0 |
| first | 1 |
| second | 2 |
| third | 3 |
ENUM类型插入的值，可以是ENUM的值，也可以是索引，如果插入的值不在ENUM列表中，就会报错
mysql> insert into tmp9 values("first"),(1),(2);
mysql> select * from tmp9;
+--------+
| enm |
+--------+
| first |
| first |
| second |
+--------+
3 rows in set (0.00 sec)
//插入ENUM中不存在的值
mysql> insert into tmp9 values("哈哈");
ERROR 1265 (01000): Data truncated for column 'enm' at row 1
查询enm的索引值
mysql> select enm, enm+0 from tmp9;
+--------+-------+
| enm | enm+0 |
+--------+-------+
| first | 1 |
| first | 1 |
| second | 2 |
+--------+-------+
3 rows in set (0.00 sec)
SET类型
SET与ENUM类型相同，也可以有0个或者多个值，区别是ENUM类型的列可以从定义的ENUM中取多个值。
例子:
mysql> create table temp8(
-> hobby SET("打篮球","看书","羽毛球","玩游戏")
-> );
Query OK, 0 rows affected (0.08 sec)
mysql> insert into temp8 values("打篮球,羽毛球,看书"),("看书"),("打篮球");
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from temp8;
+--------------------+
| hobby |
+--------------------+
| 打篮球,看书,羽毛球 |
| 看书 |
| 打篮球 |
+--------------------+
3 rows in set (0.00 sec)
2、常见运算符
运算符是告诉MySQL执行特定算术或逻辑操作的符号。MySQL内部运算符很丰富，主要分为四类：
- 算术运算符
- 比较运算符
- 逻辑运算符
- 位操作运算符
2.1算术运算符
| 运算符 | 作用 |
|---|---|
| + | 加法运算 |
| - | 减法运算 |
| * | 乘法运算 |
| / | 除法运算，返回商 |
| % | 求余运算，返回余数 |
列子：
创建表
mysql> create table tmp01(
-> num int(11)
-> );
插入数据
insert into tmp01 values(10),(9)
用运算符查询数据
mysql> select num,num+10,num-10,num*10,num/10,num%10 from tmp01;
+------+--------+--------+--------+--------+--------+
| num | num+10 | num-10 | num*10 | num/10 | num%10 |
+------+--------+--------+--------+--------+--------+
| 10 | 20 | 0 | 100 | 1.0000 | 0 |
| 9 | 19 | -1 | 90 | 0.9000 | 9 |
+------+--------+--------+--------+--------+--------+
2 rows in set (0.00 sec)
2.2比较运算符
MySQL中的比较运算符
| 运算符 | 作用 | 说明 |
|---|---|---|
| = | 等于 | |
| <=> | 安全的等于 | |
| <>(!=)= | 不等于 | |
| <= | 小于等于 | |
| >= | 大于等于 | |
| > | 小于 | |
| < | 大于 | |
| IS NULL | 为空 | |
| IS NOT NULL | 不为空 | |
| LEAST | 返回最小值 | |
| GREATEST | 返回最大值 | |
| BETWEEN AND | 在两值间 | >=min&&<=max |
| NOT BETWEEN | 不在在两值间 | |
| ISNULL | 与IS NULL相同 | |
| IN | 在集合中 | |
| NOT IN | 不在集合中 | |
| LIKE | 模糊匹配 | |
| REGEXP或 RLIKE | 正则表达式匹配 |
等于运算符（=）
mysql> select 1=1,2=3,'1'=1,'1'='1',1=null,null=null;
+-----+-----+-------+---------+--------+-----------+
| 1=1 | 2=3 | '1'=1 | '1'='1' | 1=null | null=null |
+-----+-----+-------+---------+--------+-----------+
| 1 | 0 | 1 | 1 | NULL | NULL |
+-----+-----+-------+---------+--------+-----------+
结果可以看到：1=1和’1’=1、‘1’='1’值相同。”=“不能判断null值。
因为，数值比较时有如下规则
- 若有一个h或者两个参数为NULL，则比较运算的返回结果为NULL
- 若一个比较运算中l两个参数都是字符串，则按字符串进行比较
- 若两个参数均为整数，则按整数进行比较
- 若一个字符串和数字进行相等判断，则mysql可以自动将数字转化为字符串
安全等于（<=>）
<=>可以用来判断null值
mysql> select null<=>null,null<=>1;
+-------------+----------+
| null<=>null | null<=>1 |
+-------------+----------+
| 1 | 0 |
+-------------+----------+
IS NULL(ISNULL)和IS NOT NULL运算符
mysql> select isnull(10),isnull(null),10 is null,10 is not null;
+------------+--------------+------------+----------------+
| isnull(10) | isnull(null) | 10 is null | 10 is not null |
+------------+--------------+------------+----------------+
| 0 | 1 | 0 | 1 |
+------------+--------------+------------+----------------+
BETWEEN AND运算符
mysql> select 4 between 2 and 5,6 between 2 and 5,4 not between 2 and 5;
+-------------------+-------------------+-----------------------+
| 4 between 2 and 5 | 6 between 2 and 5 | 4 not between 2 and 5 |
+-------------------+-------------------+-----------------------+
| 1 | 0 | 0 |
+-------------------+-------------------+-----------------------+
least和greatest运算
mysql> select least (2,3,4),greatest(1,2,3,4),least('a','b','c'),greatest('a','d','b');
+---------------+-------------------+--------------------+-----------------------+
| least (2,3,4) | greatest(1,2,3,4) | least('a','b','c') | greatest('a','d','b') |
+---------------+-------------------+--------------------+-----------------------+
| 2 | 4 | a | d |
+---------------+-------------------+--------------------+-----------------------+
1 row in set (0.00 sec)
in和not in运算符
mysql> select 1 in(1,2,3), 1 not in(1,2,3),null in（1，2，3）;
+-------------+-----------------+---------------+
| 1 in(1,2,3) | 1 not in(1,2,3) | in（1，2，3） |
+-------------+-----------------+---------------+
| 1 | 0 | NULL |
+-------------+-----------------+---------------+
1 row in set (0.00 sec)
like运算符
mysql> select 'stud' like 'stud','stu' like 'stud','stud' like 'stu_','stud' like '%d','stud' like '_tu_';
+--------------------+-------------------+--------------------+------------------+--------------------+
| 'stud' like 'stud' | 'stu' like 'stud' | 'stud' like 'stu_' | 'stud' like '%d' | 'stud' like '_tu_' |
+--------------------+-------------------+--------------------+------------------+--------------------+
| 1 | 0 | 1 | 1 | 1 |
+--------------------+-------------------+--------------------+------------------+--------------------+
1 row in set (0.00 sec)
- ‘%’，匹配任何数目的字符，甚至包括零字符
- ‘_’，只能匹配一个字符
regexp运算符
mysql> select 'ssky' regexp '^s','ssky' regexp 'y$','ssky'regexp 'ss.y','ssky' regexp '[ab]';
+--------------------+--------------------+---------------------+----------------------+
| 'ssky' regexp '^s' | 'ssky' regexp 'y$' | 'ssky'regexp 'ss.y' | 'ssky' regexp '[ab]' |
+--------------------+--------------------+---------------------+----------------------+
| 1 | 1 | 1 | 0 |
+--------------------+--------------------+---------------------+----------------------+
1 row in set (0.00 sec)
- ‘^’：匹配以该字符后面的字符开头的字符串
- ‘$’：匹配以该字符后面的字符结尾的字符串
- ‘.’：匹配任何一个但字符
‘[…]’：匹配在括号内的任何字符。
“[abc]”:匹配’a’,‘b’或’c’
“[0-9]”:匹配任何数字
“[a-z]”:匹配任字母 - ‘*’：匹配0个或多个在它前面的字符
2.3逻辑运算
| 运算符 | 作用 |
|---|---|
| not或者! | 逻辑非 |
| and或者& | 逻辑与 |
| or或者11 | 逻辑或 |
| xor | 逻辑异或 |
2.4位运算符
| 运算符 | 作用 |
|---|---|
| 1 | 位或 |
| & | 位与 |
| ^ | 位异或 |
| << | 位左移 |
| >> | 位右移 |
| ~ | 位反转，反转所有比特 |
2.5运算的优先级
扫一扫
913
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
