目录
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 | 位或 |
& | 位与 |
^ | 位异或 |
<< | 位左移 |
>> | 位右移 |
~ | 位反转,反转所有比特 |