文章目录
一、MySQL数据类型
1、整数类型
类型名称 | 说明 | 存储长度 | 存储范围(无符号) |
---|---|---|---|
TINYINT | 很小的整数 | 1字节 | 0~255 |
SMALLINT | 小的整数 | 2字节 | 0~65535 |
MEDIUMINT | 中等大小的整数 | 3字节 | 0~16777215 |
INT(INTEGER) | 普通大小的整数 | 4字节 | 0~4294967295 |
BIGINT | 大整数 | 8字节 | 0~28446744073709551615 |
CREATE TABLE `student`{
id INT(5),
name VARCHAR(10)
};
***注意:上面的例子中,在声明id
的数据类型时,INT(5)
中括号里的内容表示可以***显示的宽度,与存储范围无关, 当要显示的数字宽度小于该值时,会用空格填充;当插入的数据宽度大于指定的宽度时(如指为INT(4),插 入的数据却是10000时),不会报错,显示时也会完整的显示数据;当不指定宽度时,系统会为每一种类型 指定默认的宽度值
2、浮点数类型和定点数类型
类型名称 | 说明 | 存储长度 | 无符号范围 | 有符号范围 |
---|---|---|---|---|
FLOAT | 单精度浮点数 | 4字节 | 0和1.175494351E-38~3.402823466E+38 | -3.402823466E+38~1.175494351E-38 |
DOUBLE | 双精度浮点数 | 8字节 | 0和2.2250738585072014E-308~1.7976931348623157E+308 | -1.7976931348623157E-308~-2.2250738585072014E+308 |
DECIMAL(M,D) | 压缩的“严格”定点数 | M+2字节 | 和DOUBLE一样 | 和DOUBLE一样 |
-
DECIMAL的有效值的取值范围有M和D决定
-
DECIMAL不指定精度时,默认为(10,0)
CREATE TABLE `test`{ x FLOAT(5,1), y DOUBLE(5,1), z DECIAML(5,1) }; INSERT INTO `test` VALUES (5.12, 5.16, 5.123); # 查询结果为5.1, 5.2, 5.1 # FLOAT和DOUBLE为四舍五入,DECIMAL为截断
3、日期与时间类型
MySQL中有DATETIME、DATE、TIMESATMP、TIME、YEAR
共五种表示日期的数据类型,每种类型都有合法的取值范围,当指定不合法的值时,系统会将“零”值插入数据库中
(1)YEAR(单字节)
- 4位字符串或者4位数字格式表示,范围为’1901’~’2155’或1901~2155
如’2010’和2010均表示插入的值为2010年
- 2位字符串格式表示,范围为’00’~’99’,表示1970~2069
如’10’表示2010年,超过取值范围的值均被转换为2000
- 2位数字,范围为1~99,分别表示2001~2069和1970~1999
如10表示2010年,同样超出取值范围的值被转换为2000
(2)TIME(3字节)
格式为**‘HH:MM:SS’**,分别表示小时、分钟、秒,取值范围为-838:59:59~838:59:59
- **‘D HH:MM:SS’**格式,或者’HH:MM:SS’、‘HH:MM’、‘D HH:MM’、‘D HH’、‘SS’,D表示日,范围为0~34
在插入数据库时,D被转换为小时,即24*D+HH
如’2 10:10’插入数据库后为’58:10:00’
- **‘HHMMSS’**格式的字符串或者数值,类似于’D HH:MM:SS’,取值范围相同,超出范围均被存储为00:00:00
使用这种方法存储时,注意1112或者’1112’表示’00:11:12’,而不是’11:12:00’
如281020表示’28:10:20’
- 使用系统日期函数向数据库中插入数值
INSERT INTO table VALUES(CURRENT_TIME), (NOW());
# CURRENT_TIME,NOW()均可表示系统当前时间
(3)DATE(3字节)
DATE类型只有日期,没有时间部分,格式为**‘YYYY-MM-DD’**,分别表示年、月、日
-
**‘YYYY-MM-DD’或’YYYYMMDD’**格式的字符串,范围为’1000-01-01’~’9999-12-3’
-
**‘YY-MM-DD’或者’YYDDMM’**格式的字符串,其中YY的取值范围为00~99,分别表示1970~2069年
-
YY-MM-DD或者YYMMDD格式的数字
如12-12-12表示2012年12月12日,存储为’2012-12-12’
- 使用CURRENT_DATE或者**NOW()**插入当前系统时间
**注意:**MySQL允许不严格语法:任何标点符号都可以用作日期部分的间隔符,如’98@12@23’,DATETIME类型也
是允许不严格语法
(4)DATETIME(8字节)
包含日期和时间,格式为’YYYY-MM-DD HH:MM:SS’
- **‘YYYY-MM-DD HH:MM:SS’或者’YYYYMMDDHHMMSS’**格式字符串,范围同前面的一样
如’20120321123234’表示’2012-03-21 12:32:34’
- **‘YY-MM-DD HH:MM:SS’或者’YYMMDD HH:MM:SS’**格式字符串,范围同前面一样
如’79-10-12 121212’表示’1979-10-12 12:12:12’
- YYYYMMDDHHMMSS或者YYMMDDHHMMSS格式的数字
如19990107101010表示’1999-01-07 10:10:10’
- 使用**NOW()**插入当前系统时间
(5)TIMESTAMP(4字节)
插入时间戳,范围为’1970-01-01 00:00:01’UTC~’2038-01-19 03:14:07’UTC,UTC(Coordinated Universal Time)为世界标准时间;使用TIMESTAMP插入时是先根据当前时区将时间转换为时间戳存储在数据库中,查询时再把时间戳取出来根据当前时区转换为时间,所以同一时间戳在不同时区表示的时间不同
mysql> INSERT INTO datetest VALUES (NOW());
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM datetest;
+---------------------+
| date |
+---------------------+
| 2020-01-10 11:52:37 |
+---------------------+
1 row in set (0.00 sec)
mysql> set time_zone='+10:00'; # 将时区改为东10区(原本为东8区,差两个小时)
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM datetest;
+---------------------+
| date |
+---------------------+
| 2020-01-10 13:52:37 |
+---------------------+
1 row in set (0.00 sec)
(6)类型转换
DATETIME和TIMESTAMP可以与DATE互相转换:如果为DATETIME或TIMESTAMP分配一个DATE值,结果的时间被设置为’00:00:00’;如果为DATE分配一个DATETIME或TIMESTAMP值,结果的时间部分被删除
4、文本字符串类型
类型 | 说明 | 存储需求 |
---|---|---|
CHAR(M) | 固定长度非二进制字符串 | M字节,1<=M<=255 |
VARCHAR(M) | 变长非二进制字符串 | L+2字节,L为字符串实际长度,L<=M,1<=M<=65535 |
TINYTEXT | 非常小非二进制字符串 | L+1字节,L<28 |
TEXT | 小的非二进制字符串 | L+2字节,L<216 |
MEDIUMTEXT | 中等的非二进制字符串 | L+3字节,L<224 |
LONGTEXT | 大的非二进制字符串 | L+4字节,L<232 |
ENUM | 枚举类型 | 1或2字节,最大为65535 |
SET | 设置(集合类型) | 取决于成员数量(最多64个成员) |
(1)CHAR和VARCHAR
-
CHAR(M)表示该字符串的长度固定为M字节大小,不足的部分用空格补全,超出部分被删除
-
VARCHAR(M)表示该字符串最大为M字节,实际大小为所存储的字符串长度+2,多的两个字节用来存储字符串的实际长度
注意:在保存字符串时,CHAR类型的字符串的尾部的空格会被删除,VARCHAR类型的会保留空格
mysql> SHOW CREATE TABLE chartest \G;
*************************** 1. row ***************************
Table: chartest
Create Table: CREATE TABLE `chartest` (
`s` varchar(1000) DEFAULT NULL,
`sss` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> INSERT INTO chartest VALUES ('aaa ', 'aaa ');
Query OK, 1 row affected (0.02 sec)
mysql> SELECT * FROM chartest;
+---------+------+
| s | sss |
+---------+------+
| aaa | aaa |
+---------+------+
1 row in set (0.00 sec)
# s为VARCHAR类型,字符串尾部的空格被保存
(2)TEXT类型
TEXT的类型用来保存比较长的字符串,如文章内容、评论等
(3)ENUM类型
ENUM为枚举类型,一个枚举类型为一组字符串,每个字符串在内部用一个整数索引
mysql> CREATE TABLE enumtest (enm ENUM('first', 'second', 'third'));
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO enumtest VALUES ('first'), ('second'), ('third');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT enm, enm+0 FROM enumtest;
+--------+-------+
| enm | enm+0 |
+--------+-------+
| first | 1 |
| second | 2 |
| third | 3 |
+--------+-------+
3 rows in set (0.00 sec)
# 上表在定义时没有将enm声明为NULL类型,所以默认值为第一个字符串(即first);若声明为NULL,则默认值为NULL
枚举类型只能取单值,最多允许有65535个成员,适合多个值取一个时使用,如性别字段
(4)SET类型
SET类型与ENUM类型的区别在于以下两点
- SET类型可以取多个值,ENUM只能取单值
- SET类型有自动去重的功能(和python中的集合类型相似)
mysql> CREATE TABLE settest (s SET('a', 'b', 'c', 'as', 'be'));
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO settest VALUES ('a,b,as,as,as,as,as'), ('c,b,a,d,d,as');
ERROR 1265 (01000): Data truncated for column 's' at row 2
# 当插入的值不在SET定义的值中时,系统会报错
mysql> INSERT INTO settest VALUES ('a,b,as,as,as,as,as'), ('c,b,a,as');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM settest;
+----------+
| s |
+----------+
| a,b,as |
| a,b,c,as |
+----------+
2 rows in set (0.00 sec)
SET类型适合于可以取多值时使用,如一个人的兴趣爱好,最多可以有64个成员
5、二进制字符串类型
类型 | 说明 | 存储需求 |
---|---|---|
BIT(M) | 位字段类型 | M位二进制 |
BINARY(M) | 固定长度二进制字符串 | M字节 |
VARBINARY(M) | 可变长度二进制字符串 | L+1字节,L为实际大小 |
TINYBLOB(M) | 非常小的BLOB | L+1字节,L<28 |
BLOB(M) | 小的BLOB | L+2字节,L<216 |
MEDIUMBOLOB(M) | 中等大小的BLOB | L+3字节,L<224 |
LONGBLOB(M) | 非常大的BLOB | L+4字节,L<232 |
(1)BIT类型
直接指定存储的值的二进制位数大小,但存储的单位为字节,最大位数为64位,例子如下
- BIT(6)的字段分配值b’111’,在数据库存储为b’000111’
- BIT(6)的字段分配值b’0000001’,无法插入,会报错
mysql> CREATE TABLE bintest (bit_s BIT(4));
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO bintest VALUES (2);
Query OK, 1 row affected (0.01 sec)
SELECT BIN(bit_s) FROM bintest;
+------------+
| BIN(bit_s) |
+------------+
| 10 |
+------------+
2 rows in set (0.01 sec)
(2)BINARY和VARBINARY
类似于CHAR和VARCHAR,BINARY使用’\0’补足字段的尾部的
CREATE TABLE bintest (bin_s BINARY(4), vbin_s VARBINARY(4));
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO bintest VALUES (1, 1);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT bin_s, vbin_s, length(bin_s), length(vbin_s) FROM bintest;
+-------+--------+---------------+----------------+
| bin_s | vbin_s | length(bin_s) | length(vbin_s) |
+-------+--------+---------------+----------------+
| 1 | 1 | 4 | 1 |
| 1 | 1 | 4 | 1 |
+-------+--------+---------------+----------------+
2 rows in set (0.00 sec)
mysql> SELECT bin_s, vbin_s, bin_s='1', vbin_s='1', bin_s='1\0\0\0', vbin_s='1\0\0\0' FROM bintest;
+-------+--------+-----------+------------+-----------------+------------------+
| bin_s | vbin_s | bin_s='1' | vbin_s='1' | bin_s='1\0\0\0' | vbin_s='1\0\0\0' |
+-------+--------+-----------+------------+-----------------+------------------+
| 1 | 1 | 0 | 1 | 1 | 0 |
| 1 | 1 | 0 | 1 | 1 | 0 |
+-------+--------+-----------+------------+-----------------+------------------+
2 rows in set (0.00 sec)
# 从上面的表中可以看到BINARY类型的值确实使用'\0'补全字段尾部的
(3)BLOB类型
BLOB存储的是二进制字符串,BLOB没有字符集,而TEXT有字符集
- TEXT类型的值的比较和排序是基于字符集比较的
- BLOB类型的值的比较和排序是基于列值字节的数值
二、MySQL运算符
1、算术运算符
运算符 | 说明 |
---|---|
+ | 加 |
- | 减 |
* | 乘 |
/ | 除 |
% | 求余 |
- 除法和求余运算若除数为0,返回结果为NULL
2、比较运算符
为真返回1,为假返回0
运算符 | 说明 |
---|---|
= | 等于 |
<=> | 安全的等于(操作数可为NULL) |
!=(<>) | 不等于 |
< / <= | 小于/小于等于 |
> / >= | 大于/大于等于 |
IS NULL / ISNULL | 判断值是否为NULL |
IS NOT NULL | 判断值是否不为NULL |
LEAST | 返回最小值 |
GREATEST | 返回最大值 |
BETWEEN AND | 判断值是否落在两个值之间 |
IN | 判断值是否在IN列表中(类似python的in) |
NOT IN | 判断值是否不在IN列表中 |
LIKE | 通配符匹配 |
REGEP | 正则表达式匹配 |
=
的比较规则
A=B,A/B任意一个为NULL,返回NULL
A=B,A、B均为字符串时,进行比较
A=B,若A为字符串(如"123"),B为整数,则把A转换为整数后再比较(即123)
-
<=>
:与=
一样,只是可以进行NULL的比较;A=B,A、B均为NULL时,返回1,任意一个为NULL时,返回0 -
<>
!=
<
<=
>
>=
:不能用于判断NULL -
IS NULL,ISNULL,IS NOT NULL:1 IS NULL / ISNULL(1) / 1 IS NOT NULL
-
BETWEEN AND:4 BETWEEN 1 AND 5:判断4是否大于等于1并且小于等于5
-
LEAST / GREATEST
使用方法:LEAST(1,2,5);GREATEST(‘a’,‘b’,‘s’)
当参数中有NULL时,返回NULL
字符串比较安装字母表顺序比较
- IN / NOT IN
A IN B:B为列表,为真返回1,为假返回0
当A为NULL,或者A不在B中,且B中有NULL时,返回NULL
- LIKE
<表达式> LINKE <匹配条件>:“a” LIKE “a”,返回1
“%”:匹配任意数目的字符
“_”:只匹配一个字符
匹配条件为NULL时,返回NULL
- REGEXP:<表达式> REGEXP <匹配条件>,正则表达式链接
3、逻辑运算符
返回值为0、1或者NULL
操作符 | 作用 | 补充说明 |
---|---|---|
NOT / ! | 逻辑非 | 操作数为NULL时,返回NULL |
AND / && | 逻辑与 | 操作数中有NULL,且另一个操作数不为0时返回NULL |
OR / || | 逻辑或 | 操作数中有NULL,且另一个操作数不为1时返回NULL |
XOR | 逻辑异或 | 操作数中有NULL时,返回NULL |
4、位运算符
操作符 | 说明 |
---|---|
| | 按位或操作 |
& | 按位与操作 |
^ | 按位异或操作 |
<< | 位左移操作,语法为:<表达式> << <左移位数> |
>> | 位右移操作 |
~ | 按位取反操作 |
- MySQl经过位运算后的结果为一个64位的无符号整数集