（三）MySQL数据类型和运算符
一、MySQL数据类型介绍
1.数据类型简介
（1）数据表由多列字段构成，每一个字段指定了不同的数据类型，指定了数据类型之后，也就决定了向字段插入的数据内容
（2）不同的数据类型也决定了MySQL在存储它们的时候使用的方式，以及在使用它们的时候选择什么运算符号进行运算
（3）数值数据类型：TINYINT、SMALINT、MEDIUMINT、INT、BIGINT、FLOAT、DOUBLE、DECIMAL
（4）日期/时间类型：YEAR 、TIME、 DATE、 DATETIME、TIMESTAMP
（5）字符串类型：CHAR、VARCHAR 、BINARY、VARBINARY、BLOB、TEXT、ENUM、SET
2.数值类型简介
（1）整数类型
| 类型名称 | 说明 | 存储需求 | 有符号的取值范围 | 无符号的取值范围（unsigned） |
|---|---|---|---|---|
| TINYINT | 很小的整数 | 1个字节 | -128 ~ 127 | 0 ~ 255 |
| SMALLINT | 小的整数 | 2个字节 | -32768～ 32767 | 0 ~ 65535 |
| MEDIUMINT | 中等大小的整数 | 3个字节 | -8388608 ~ 8388607 | 0 ~ 16777215 |
| INT | 普通大小的整数 | 4个字节 | -2147483648 ~ 2147483647 | 0 ~ 4294967295 |
| BIGINT | 大整数 | 8个字节 | -9223372036854775808 ~ 9223372036854775807 | 0 ~ 18446744073709551615 |
1）TINYINT
有符号取值范围
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mytest |
| performance_schema |
| sys |
| test2 |
+--------------------+
6 rows in set (0.05 sec)
mysql> use test2;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table test1(test1 tinyint);
Query OK, 0 rows affected (0.10 sec)
mysql> desc test1;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| test1 | tinyint(4) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql> insert into test1 values (1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test1 values (2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test1 values (-2);
Query OK, 1 row affected (0.02 sec)
mysql> insert into test1 values (127);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test1 values (128);
ERROR 1264 (22003): Out of range value for column 'test1' at row 1
#报错原因：超出取值范围
mysql> insert into test1 values (-128);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test1 values (-129);
ERROR 1264 (22003): Out of range value for column 'test1' at row 1
mysql>
无符号取值范围（unsigned）
mysql> alter table test1 add test2 tinyint unsigned;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| test1 | tinyint(4) | YES | | NULL | |
| test2 | tinyint(3) unsigned | YES | | NULL | |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into test1 (test2) values (127);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test1 (test2) values (128);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test1 (test2) values (255);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test1 (test2) values (256);
ERROR 1264 (22003): Out of range value for column 'test2' at row 1
mysql> insert into test1 (test2) values (-1);
ERROR 1264 (22003): Out of range value for column 'test2' at row 1
mysql>
2）SMALLINT
有符号取值范围
mysql> alter table test1 add test3 smallint; #有符号取值范围
Query OK, 0 rows affected (0.61 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| test1 | tinyint(4) | YES | | NULL | |
| test2 | tinyint(3) unsigned | YES | | NULL | |
| test3 | smallint(6) | YES | | NULL | |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into test1(test3) values(256);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test1(test3) values(32767);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test1(test3) values(32768);
ERROR 1264 (22003): Out of range value for column 'test3' at row 1
mysql> alter table test1 add test4 smallint unsigned; #无符号取值范围
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test1;
+-------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| test1 | tinyint(4) | YES | | NULL | |
| test2 | tinyint(3) unsigned | YES | | NULL | |
| test3 | smallint(6) | YES | | NULL | |
| test4 | smallint(5) unsigned | YES | | NULL | |
+-------+----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql>
3）MEDIUMINT
mysql> alter table test1 add test5 mediumint; #有符号取值范围
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table test1 add test6 mediumint unsigned; #无符号取值范围
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test1;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| test1 | tinyint(4) | YES | | NULL | |
| test2 | tinyint(3) unsigned | YES | | NULL | |
| test3 | smallint(6) | YES | | NULL | |
| test4 | smallint(5) unsigned | YES | | NULL | |
| test5 | mediumint(9) | YES | | NULL | |
| test6 | mediumint(8) unsigned | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql>
4）INT
mysql> alter table test1 add test7 int; #有符号取值范围
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table test1 add test8 int unsigned; #无符号取值范围
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test1;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| test1 | tinyint(4) | YES | | NULL | |
| test2 | tinyint(3) unsigned | YES | | NULL | |
| test3 | smallint(6) | YES | | NULL | |
| test4 | smallint(5) unsigned | YES | | NULL | |
| test5 | mediumint(9) | YES | | NULL | |
| test6 | mediumint(8) unsigned | YES | | NULL | |
| test7 | int(11) | YES | | NULL | |
| test8 | int(10) unsigned | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
mysql>
5）BIGINT
语法：
mysql> alter table test1 add test9 bigint; #有符号取值范围
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table test1 add test10 bigint unsigned; #无符号取值范围
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test1;
+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| test1 | tinyint(4) | YES | | NULL | |
| test2 | tinyint(3) unsigned | YES | | NULL | |
| test3 | smallint(6) | YES | | NULL | |
| test4 | smallint(5) unsigned | YES | | NULL | |
| test5 | mediumint(9) | YES | | NULL | |
| test6 | mediumint(8) unsigned | YES | | NULL | |
| test7 | int(11) | YES | | NULL | |
| test8 | int(10) unsigned | YES | | NULL | |
| test9 | bigint(20) | YES | | NULL | |
| test10 | bigint(20) unsigned | YES | | NULL | |
+--------+-----------------------+------+-----+---------+-------+
10 rows in set (0.00 sec)
mysql>
（2）浮点数类型和定点数类型
- MySQL中使用浮点数和定点数来表示小数，浮点数有两种类型：单精度浮点数（FLOAT）和双精度浮点数（DOUBLE），定点数只有DECIMAL
- 浮点数和定点数都可以用（M，N）来表示，其中M是精度，表示总共的位数，N是标度，表示小数的位数
- DECIMAL实际是以字符串形式存放的，在对精度要求比较高的时候（如货币、科学数据等）使用DECIMAL类型会比较好
- 浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的数据范围，它的缺点是会引起精度问题
| 类型名称 | 说明 存储需求 | 有符号的取值范围 | 无符号的取值范围 |
|---|---|---|---|
| FLOAT | 单精度浮点数 | 4个字节 | -3.402823466EE+38 ~ -1.175494251E-38 |
| DOUBLE | 双精度浮点数 | 8个字节 | -1.7976931348623157E+308 ~ -2.2250738585072014E-308 |
| DECIMAL | 压缩的"严格"定点数 | M+2个字节不固定 | 不固定 |
mysql> create table test2(t1 float,t2 double,t3 decimal);
Query OK, 0 rows affected (0.45 sec)
mysql> desc test2;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| t1 | float | YES | | NULL | |
| t2 | double | YES | | NULL | |
| t3 | decimal(10,0) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
#decimal(10,0) ：10表示小数的总位数，0表示小数点后面的精度
mysql> insert into test2 values (123.123,456.456,789.789);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from test2;
+---------+---------+------+
| t1 | t2 | t3 |
+---------+---------+------+
| 123.123 | 456.456 | 790 |
+---------+---------+------+
1 row in set (0.00 sec)
mysql> create table test3 (t1 float(5,2));
Query OK, 0 rows affected (0.51 sec)
mysql> desc test3;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| t1 | float(5,2) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into test3 values (123.456789);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test3;
+--------+
| t1 |
+--------+
| 123.46 |
+--------+
1 row in set (0.00 sec)
mysql> insert into test3 values (1234.456789);
ERROR 1264 (22003): Out of range value for column 't1' at row 1
mysql> insert into test3 values (1234.4);
ERROR 1264 (22003): Out of range value for column 't1' at row 1
#可以看出，小数点后面占两位，整数的位数就是5-2=3，故整数最多只能是3位
mysql> insert into test3 values (123.4);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test3;
+--------+
| t1 |
+--------+
| 123.46 |
| 123.40 |
+--------+
2 rows in set (0.00 sec)
mysql>
float、double与decimal的对比：float、double会发生丢失精度的问题
mysql> create table test4 (t1 float(10,2),t2 decimal(10,2));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test4 values (9876543.21,9876543.21);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test4;
+------------+------------+
| t1 | t2 |
+------------+------------+
| 9876543.00 | 9876543.21 |
+------------+------------+
1 row in set (0.00 sec)
#说明float类型会丢失精度
mysql> create table test5 (t1 decimal(10,2),t2 decimal(10,2),t3 decimal(12,2));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into test5 values(12345.67,123456.78,t1+t2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test5;
+----------+-----------+-----------+
| t1 | t2 | t3 |
+----------+-----------+-----------+
| 12345.67 | 123456.78 | 135802.45 |
+----------+-----------+-----------+
1 row in set (0.00 sec)
mysql> create table test6 (t1 float(10,2),t2 float(10,2),t3 float(12,2));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into test6 values(12345.67,123456.78,t1+t2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test6;
+----------+-----------+-----------+
| t1 | t2 | t3 |
+----------+-----------+-----------+
| 12345.67 | 123456.78 | 135802.45 |
+----------+-----------+-----------+
1 row in set (0.00 sec)
mysql> insert into test6 values(12345678.09,12345678.09,t1+t2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test6;
+-------------+-------------+-------------+
| t1 | t2 | t3 |
+-------------+-------------+-------------+
| 12345.67 | 123456.78 | 135802.45 |
| 12345678.00 | 12345678.00 | 24691356.00 |
+-------------+-------------+-------------+
2 rows in set (0.00 sec)
mysql>
故，在做科学计算或货币使用，这种需要高精度计算时，要使用decimal，不能使用float和double
3.日期/时间类型
- MySQL有多种表示日期的数据类型，比如，当只记录年信息的时候，可以使用YEAR类型，而没有必要
使用DATE类型 - 每一个类型都有合法的取值范围，当指定确实不合法的值时系统将"零”值插入到数据库中
| 类型名称 | 日期格式 | 日期范围 | 存储需求 |
|---|---|---|---|
| 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-0100:00:00 ~ 9999-12-3123: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）YEAR
- 格式：以4位字符串格式表示的YEAR，范围为’1901’ ~ ‘2155’
- 格式：以4位数字格式表示的YEAR，范围为1901 ~ 2155
- 格式：以2位字符串格式表示的YEAR，范围为’00’ ~ ‘99’，其中，"0’~ '69’被转换为2000 ~ 2069，‘70’ ~ '99’被转换为1970 ~ 1999
- 格式：以2位数字格式表示的YEAR，范围为1~ 99，其中，1 ~ 69被转换为2001 ~ 2069，70 ~ 99被转换为1970~1999
mysql> create table test7 (t1 year);
Query OK, 0 rows affected (0.04 sec)
mysql> desc test7;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| t1 | year(4) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into test7 values (1999);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test7 values ("2020");
Query OK, 1 row affected (0.00 sec)
mysql> select * from test7;
+------+
| t1 |
+------+
| 1999 |
| 2020 |
+------+
2 rows in set (0.00 sec)
mysql> insert into test7 values (21);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test7 values (79);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test7 values (1901);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test7 values (1900);
ERROR 1264 (22003): Out of range value for column 't1' at row 1
mysql> insert into test7 values (2155);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test7 values (2156);
ERROR 1264 (22003): Out of range value for column 't1' at row 1
mysql> insert into test7 values ('77');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test7 values ('22');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test7;
+------+
| t1 |
+------+
| 1999 |
| 2020 |
| 2021 |
| 1979 |
| 1901 |
| 2155 |
| 1977 |
| 2022 |
+------+
8 rows in set (0.00 sec)
mysql>
（2）TIME
- TIME类型的格式为HH:MM:SS ，HH表示小时，MM表示分钟，SS表示秒
- 格式：以’HHMMSS’格式表示的TIME，例如’101112’’被理解为10:11:12，但如果插入不合法的时间，如’109712’，则被存储为00:00:00
- 格式：以’D HH:MM:SS’字符串格式表示的TIME，其中D表示日，可以取0～34之间的值，在插入数据库的时候D会被转换成小时，如’2 10:10’在数据库中表示为58:10:00，即2x24+10= 58
mysql> alter table test7 add t2 time;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test7;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| t1 | year(4) | YES | | NULL | |
| t2 | time | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into test7 (t2) values ("10:12:30");
Query OK, 1 row affected (0.00 sec)
mysql> insert into test7 (t2) values (10:12:30);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':12:30)' at line 1
#报错原因：没加双引号，但可同时不加双引号和冒号，如下所示：
mysql> insert into test7 (t2) values (101230); #用6位的纯数字表示
Query OK, 1 row affected (0.00 sec)
mysql> select * from test7;
+------+----------+
| t1 | t2 |
+------+----------+
| 1999 | NULL |
| 2020 | NULL |
| 2021 | NULL |
| 1979 | NULL |
| 1901 | NULL |
| 2155 | NULL |
| 1977 | NULL |
| 2022 | NULL |
| NULL | 10:12:30 |
| NULL | 10:12:30 |
+------+----------+
10 rows in set (0.00 sec)
mysql> insert into test7 (t2) values ('2 8:30');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test7 (t2) values ('2 8:30:45');
Query OK, 1 row affected (0.00 sec)
#2表示两天，8表示8小时，合在一起就是56小时，30表示分钟，45表示秒
mysql> select * from test7;
+------+----------+
| t1 | t2 |
+------+----------+
| 1999 | NULL |
| 2020 | NULL |
| 2021 | NULL |
| 1979 | NULL |
| 1901 | NULL |
| 2155 | NULL |
| 1977 | NULL |
| 2022 | NULL |
| NULL | 10:12:30 |
| NULL | 10:12:30 |
| NULL | 56:30:00 |
| NULL | 56:30:45 |
+------+----------+
12 rows in set (0.00 sec)
mysql>
（3）DATE
- DATE类型的格式为YYYY-MM-DD，其中，YYYY表示年，MM表示月，DD表示日
- 格式：‘YYYY-MM-DD’或’YYYYMMDD’，取值范围为‘1000-01-01’ ~ ‘9999-12-3’
- 格式：‘YY-MM-DD’或"YYMMDD’，这里Y表示两位的年值，范围为’00’ ~ ‘99’，其中，'00’～ '69’被转换为2000~2069，‘70’ ~ '99’被转换为1970~1999
- 格式：YY-MM-DD或YYMMDD，数字格式表示的日期，其中YY范围为00 ~ 99，其中，00～69被转换为2000 ~ 2069 , 70 ~ 99被转换为1970 ~ 1999
mysql> alter table test7 add t3 date;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test7;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| t1 | year(4) | YES | | NULL | |
| t2 | time | YES | | NULL | |
| t3 | date | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into test7 (t3) values ('20200819');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test7 (t3) values ('2020-08-19');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test7 (t3) values ('200819');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test7 (t3) values ('790819');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test7;
+------+----------+------------+
| t1 | t2 | t3 |
+------+----------+------------+
| 1999 | NULL | NULL |
| 2020 | NULL | NULL |
| 2021 | NULL | NULL |
| 1979 | NULL | NULL |
| 1901 | NULL | NULL |
| 2155 | NULL | NULL |
| 1977 | NULL | NULL |
| 2022 | NULL | NULL |
| NULL | 10:12:30 | NULL |
| NULL | 10:12:30 | NULL |
| NULL | 56:30:00 | NULL |
| NULL | 56:30:45 | NULL |
| NULL | NULL | 2020-08-19 |
| NULL | NULL | 2020-08-19 |
| NULL | NULL | 2020-08-19 |
| NULL | NULL | 1979-08-19 |
+------+----------+------------+
16 rows in set (0.00 sec)
mysql>
（4）DATETIME
- DATETIME类型的格式为YYYY-MM-DD HH:MM:SS，其中，YYYY表示年，MM表示月，DD表示日，HH表示小时，MM表示分钟，SS表示秒
- 格式：‘YYYY-MM-DD HH:MM:SS’或’YYYYMMDDHHMMSS’，字符串格式，取值范围为’1000-01-01 00:00:00’ ~ ‘9999-12-31 23:59:59’
- 格式：YY-MM-DD HH:MM:SS’或’YYMMDDHHMMSS’，字符串格式，其中YY范围为’00’ ~ ‘99’，其中，‘00’ ~ '69’被转换为2000 ~ 2069 ，‘70’ ~ '99’被转换为1970 ~ 1999
- 格式：YYYYMMDDHHMMSS或YYMMDDHHMMSS，数字格式，取值范围同上
mysql> alter table test7 add t4 datetime;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test7;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| t1 | year(4) | YES | | NULL | |
| t2 | time | YES | | NULL | |
| t3 | date | YES | | NULL | |
| t4 | datetime | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> insert into test7 (t4) values ('2020-08-18 15:17:30');
Query OK, 1 row affected (0.01 sec)
mysql> insert into test7 (t4) values ('15:17:30'); #省略日期会报错
ERROR 1292 (22007): Incorrect datetime value: '15:17:30' for column 't4' at row 1
mysql> insert into test7 (t4) values ('2020-08-18'); #省略时间可以，会自动将时间设为00:00:00
Query OK, 1 row affected (0.01 sec)
mysql> select * from test7;
+------+----------+------------+---------------------+
| t1 | t2 | t3 | t4 |
+------+----------+------------+---------------------+
| 1999 | NULL | NULL | NULL |
| 2020 | NULL | NULL | NULL |
| 2021 | NULL | NULL | NULL |
| 1979 | NULL | NULL | NULL |
| 1901 | NULL | NULL | NULL |
| 2155 | NULL | NULL | NULL |
| 1977 | NULL | NULL | NULL |
| 2022 | NULL | NULL | NULL |
| NULL | 10:12:30 | NULL | NULL |
| NULL | 10:12:30 | NULL | NULL |
| NULL | 56:30:00 | NULL | NULL |
| NULL | 56:30:45 | NULL | NULL |
| NULL | NULL | 2020-08-19 | NULL |
| NULL | NULL | 2020-08-19 | NULL |
| NULL | NULL | 2020-08-19 | NULL |
| NULL | NULL | 1979-08-19 | NULL |
| NULL | NULL | NULL | 2020-08-18 15:17:30 |
| NULL | NULL | NULL | 2020-08-18 00:00:00 |
+------+----------+------------+---------------------+
18 rows in set (0.00 sec)
mysql>
（5）TIMESTAMP（时间戳）
- TIMESTAMP类型的格式为YYYY-MM-DD HH:MM:SS，显示宽度固定在19个字符
- TIMESTAMP与 DATETIME的区别在于，TIMESTAMP的取值范围小于DATETIME的取值范围
- TIMESTAMP的取值范围为1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC，其中UTC是世界标准时间，存储时会对当前时区进行转换，检索时再转换回当前时区
mysql> alter table test7 add t5 timestamp;
Query OK, 0 rows affected (0.54 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from test7;
+------+----------+------------+---------------------+---------------------+
| t1 | t2 | t3 | t4 | t5 |
+------+----------+------------+---------------------+---------------------+
| 1999 | NULL | NULL | NULL | 2020-08-19 17:35:23 |
| 2020 | NULL | NULL | NULL | 2020-08-19 17:35:23 |
| 2021 | NULL | NULL | NULL | 2020-08-19 17:35:23 |
| 1979 | NULL | NULL | NULL | 2020-08-19 17:35:23 |
| 1901 | NULL | NULL | NULL | 2020-08-19 17:35:23 |
| 2155 | NULL | NULL | NULL | 2020-08-19 17:35:23 |
| 1977 | NULL | NULL | NULL | 2020-08-19 17:35:23 |
| 2022 | NULL | NULL | NULL | 2020-08-19 17:35:23 |
| NULL | 10:12:30 | NULL | NULL | 2020-08-19 17:35:23 |
| NULL | 10:12:30 | NULL | NULL | 2020-08-19 17:35:23 |
| NULL | 56:30:00 | NULL | NULL | 2020-08-19 17:35:23 |
| NULL | 56:30:45 | NULL | NULL | 2020-08-19 17:35:23 |
| NULL | NULL | 2020-08-19 | NULL | 2020-08-19 17:35:23 |
| NULL | NULL | 2020-08-19 | NULL | 2020-08-19 17:35:23 |
| NULL | NULL | 2020-08-19 | NULL | 2020-08-19 17:35:23 |
| NULL | NULL | 1979-08-19 | NULL | 2020-08-19 17:35:23 |
| NULL | NULL | NULL | 2020-08-18 15:17:30 | 2020-08-19 17:35:23 |
| NULL | NULL | NULL | 2020-08-18 00:00:00 | 2020-08-19 17:35:23 |
+------+----------+------------+---------------------+---------------------+
18 rows in set (0.00 sec)
#虽然没插入数据，但会自动插入当前时间
mysql> insert into test7 (t1) values (2020);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test7;
+------+----------+------------+---------------------+---------------------+
| t1 | t2 | t3 | t4 | t5 |
+------+----------+------------+---------------------+---------------------+
| 1999 | NULL | NULL | NULL | 2020-08-19 17:35:23 |
| 2020 | NULL | NULL | NULL | 2020-08-19 17:35:23 |
| 2021 | NULL | NULL | NULL | 2020-08-19 17:35:23 |
| 1979 | NULL | NULL | NULL | 2020-08-19 17:35:23 |
| 1901 | NULL | NULL | NULL | 2020-08-19 17:35:23 |
| 2155 | NULL | NULL | NULL | 2020-08-19 17:35:23 |
| 1977 | NULL | NULL | NULL | 2020-08-19 17:35:23 |
| 2022 | NULL | NULL | NULL | 2020-08-19 17:35:23 |
| NULL | 10:12:30 | NULL | NULL | 2020-08-19 17:35:23 |
| NULL | 10:12:30 | NULL | NULL | 2020-08-19 17:35:23 |
| NULL | 56:30:00 | NULL | NULL | 2020-08-19 17:35:23 |
| NULL | 56:30:45 | NULL | NULL | 2020-08-19 17:35:23 |
| NULL | NULL | 2020-08-19 | NULL | 2020-08-19 17:35:23 |
| NULL | NULL | 2020-08-19 | NULL | 2020-08-19 17:35:23 |
| NULL | NULL | 2020-08-19 | NULL | 2020-08-19 17:35:23 |
| NULL | NULL | 1979-08-19 | NULL | 2020-08-19 17:35:23 |
| NULL | NULL | NULL | 2020-08-18 15:17:30 | 2020-08-19 17:35:23 |
| NULL | NULL | NULL | 2020-08-18 00:00:00 | 2020-08-19 17:35:23 |
| 2020 | NULL | NULL | NULL | 2020-08-19 17:35:23 |
+------+----------+------------+---------------------+---------------------+
19 rows in set (0.00 sec)
mysql>
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 | 一个设置，字符串对象可以有零个或多个SET成员 | 1,2,3,4或8个字节，取决于集合成员的数量（最多64个成员） |
二进制字符串
| 二进制字符串类型 | 说明 | 存储需求 |
|---|---|---|
| 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 |
| MEIDUMBLOB(M) | 中等大小的BLOB | L+3字节，在此L < 2^24 |
| LONGBLOB(M) | 非常大的BLOB | L+4字节，在此L < 2^32 |
（1）CHAR和VARCHAR
- CHAR(M)为固定长度的字符串，在定义时指定字符串列长，当保存时在右侧填充空格以达到指定的长度，M表示列长度，取值范围是0 ~ 255个字符，例如，CHAR(4)定义了一个固定长度的字符串列，其包含的字符个数最大为4，当检索到CHAR值时，尾部的空格将被删掉
- VARCHAR(M)为可变长度的字符串，M表示最大列长度，取值范围是0 ~ 65535，VARCHAR的最大实际长度由最长的行的大小和使用的字符集确定，而其实际占用的空间为字符串的实际长度加一（一个字符串结束符）。插入值CHAR(4)存储需求，VARCHAR(4)存储需求’’ ’ ’ 4个字节 ’ 1个字节 ‘ab’ ‘ab ’ 4个字节’ab’ 3个字节 ‘abc’ ‘abc’ 4个字节 ‘abc’ 4个字节 ‘abcd’ ‘abcd’ 4个字节 ‘abcd’ 5个字节 ‘abcde’ ‘abcd’ 4个字节 ‘abcd’ 5个字节
mysql> create table test8 (t1 char(2)); #字符串长度设为2
Query OK, 0 rows affected (0.01 sec)
mysql> desc test8;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| t1 | char(2) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into test8 values ("ad");
Query OK, 1 row affected (0.00 sec)
mysql> insert into test8 values ("adc");
ERROR 1406 (22001): Data too long for column 't1' at row 1 #超出长度
mysql> alter table test8 add t2 varchar(2);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test8;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| t1 | char(2) | YES | | NULL | |
| t2 | varchar(2) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into test8 (t2) values ("ab");
Query OK, 1 row affected (0.00 sec)
mysql> insert into test8 (t2) values ("abc");
ERROR 1406 (22001): Data too long for column 't2' at row 1
mysql>
（2）TEXT
- TINYTEXT 最大长度为 255 个字符
- TEXT 最大长度为 65536 个字符
- MEDIUMTEXT 最大长度为 16777215 个字符
- LONGTEXT 最大长度为 4294967295 个字符
mysql> create table test9 (t1 char(2),t2 varchar(2),t3 tinytext,t4 text, t5 mediumtext);
Query OK, 0 rows affected (0.01 sec)
mysql> desc test9;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| t1 | char(2) | YES | | NULL | |
| t2 | varchar(2) | YES | | NULL | |
| t3 | tinytext | YES | | NULL | |
| t4 | text | YES | | NULL | |
| t5 | mediumtext | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql>
（3）ENUM
在基本的数据类型中，无外乎就是些数字和字符，但是某些事物是较难用数字和字符来准确地表示的。比如一周有七天，分别是Sunday、Monday、Tuesday、Wednesday、Thursday、Friday 和 Saturday。如果我们用整数 0、1、2、3、4、5、6 来表示这七天，那么多下来的那些整数该怎么办？而且这样的设置很容易让数据出错，即取值超出范围。我们能否自创一个数据类型，而数据的取值范围就是这七天呢？因此有了 ENUM 类型（Enumeration，枚举），它允许用户自己来定义一种数据类型，并且列出该数据类型的取值范围。ENUM 是一个字符串对象，其值为表创建时在列规定中枚举（即列举）的一列值，语法格式为：字段名 ENUM （‘值1’， ‘值2’， … ‘值n’） 字段名指将要定义的字段，值 n 指枚举列表中的第 n 个值，ENUM类型的字段在取值时，只能在指定的枚举列表中取，而且一次只能取一个。如果创建的成员中有空格时，其尾部的空格将自动删除。ENUM 值在内部用整数表示，每个枚举值均有一个索引值：列表值所允许的成员值从 1 开始编号，MySQL 存储的就是这个索引编号。枚举最多可以有 65535 个元素。
mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | NO | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint(5) unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)
mysql> create table test10 (scores int,level enum("excellent","good","bad")); #自定义枚举
Query OK, 0 rows affected (0.35 sec)
mysql> desc test10;
+--------+--------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------------------+------+-----+---------+-------+
| scores | int(11) | YES | | NULL | |
| level | enum('excellent','good','bad') | YES | | NULL | |
+--------+--------------------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> insert into test10 (level) values ("excellent");
Query OK, 1 row affected (0.00 sec)
mysql> insert into test10 (level) values ("good");
Query OK, 1 row affected (0.00 sec)
mysql> insert into test10 (level) values ("bad");
Query OK, 1 row affected (0.00 sec)
mysql> insert into test10 (level) values ("text"); #如果插入enum中没有定义过的值，如text的值，会报错
ERROR 1265 (01000): Data truncated for column 'level' at row 1
mysql> insert into test10 values (70,'good'),(90,1),(75,2),(50,3);
Query OK, 4 rows affected (0.04 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from test10;
+--------+-----------+
| scores | level |
+--------+-----------+
| NULL | excellent |
| NULL | good |
| NULL | bad |
| 70 | good |
| 90 | excellent |
| 75 | good |
| 50 | bad |
+--------+-----------+
7 rows in set (0.00 sec)
mysql>
（4）SET
- SET 是一个字符串对象，可以有零个或多个值，SET 列最多可以有 64 个成员，其值为表创建时规定的一列值，语法：SET（‘值1’，‘值2’，… ‘值n’）
- 与 ENUM 类型相同，SET 值在内部用整数表示，列表中每一个值都有一个索引编号
- 与 ENUM 类型不同的是，ENUM 类型的字段只能从定义的列值中选择一个值插入，而 SET 类型的列可从定义的列值中选择多个字符的联合
- 如果插入 SET 字段中列值有重复，则 MySQL 自动删除重复的值，插入 SET 字段的值的顺序并不重要，MySQL 会在存入数据库时，按照定义的顺序显示
类似与集合，但是不能有重复的，出现重复会自动去重
mysql> create table test11 (t1 set('a','b','c')); #自定义SET字段的值
Query OK, 0 rows affected (0.61 sec)
mysql> desc test11;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| t1 | set('a','b','c') | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into test11 values ('a');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test11 values ('b');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test11 values ('d'); #如果插入的不是自定义的值会报错
ERROR 1265 (01000): Data truncated for column 't1' at row 1
mysql> insert into test11 values ('a,b'); #只能插入自定义的SET字段的值
Query OK, 1 row affected (0.00 sec)
mysql> insert into test11 values ('a,b,c');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test11 values ('a,b,c,d');
ERROR 1265 (01000): Data truncated for column 't1' at row 1
mysql> insert into test11 values ('a,b,c,a'); #重复的a会被去重
Query OK, 1 row affected (0.00 sec)
mysql> select * from test11;
+-------+
| t1 |
+-------+
| a |
| b |
| a,b |
| a,b,c |
| a,b,c |
+-------+
5 rows in set (0.00 sec)
mysql>
（5）BIT
- BIT 数据类型用来保存位字段值，即以二进制的形式来保存数据，如保存数据 13，则实际保存的是 13 的二进制值，即 1101
- BIT 是位字段类型，BIT(M) 中的 M 表示每个值的位数，范围为 1 ~ 64 ，如果 M 被省略，则默认为 1 ，如果为 BIT(M) 列分配的值的长度小于 M 位，则在值的左边用 0 填充
- 如果需要位数至少为 4 位的 BIT 类型，即可定义为 BIT(4) ，则大于 1111 的数据是不能被插入的
mysql> create table test12 (b bit(4));
Query OK, 0 rows affected (0.00 sec)
mysql> desc test12;
+-------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| b | bit(4) | YES | | NULL | |
+-------+--------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into test12 values (2),(9),(15);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test12;
+------+
| b |
+------+
| |
| |
| |
+------+
3 rows in set (0.00 sec)
mysql> select bin(b+0) from test12;
+----------+
| bin(b+0) |
+----------+
| 10 |
| 1001 |
| 1111 |
+----------+
3 rows in set (0.00 sec)
mysql> select bin(b) from test12;
+--------+
| bin(b) |
+--------+
| 10 |
| 1001 |
| 1111 |
+--------+
3 rows in set (0.00 sec)
mysql>
（6）BINARY 和 VARBINARY
-
BINARY 和 VARBINARY 类型类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字节字符串
-
BINARY 类型的长度是固定的，指定长度之后，不足最大长度的，将在它们右边填充 ‘\0’ 以补齐指定长度
-
VARBINARY 类型的长度是可变的，指定长度之后，其长度可以在 0 到最大值之间
mysql> create table test13(t1 binary(3)); #定义固定长度为3的BINARY类型
Query OK, 0 rows affected (0.58 sec)
mysql> desc test13;
+-------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| t1 | binary(3) | YES | | NULL | |
+-------+-----------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into test13 values (5);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test13 values (10);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test13;
+------+
| t1 |
+------+
| 5 |
| 10 |
+------+
2 rows in set (0.00 sec)
mysql> alter table test13 add t2 varbinary(100); #定义可变长度为100的VARBINARY类型
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test13;
+-------+----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| t1 | binary(3) | YES | | NULL | |
| t2 | varbinary(100) | YES | | NULL | |
+-------+----------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into test13 values (80,101);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test13;
+------+------+
| t1 | t2 |
+------+------+
| 5 | NULL |
| 10 | NULL |
| 80 | 101 |
+------+------+
3 rows in set (0.00 sec)
mysql> select length(t1),length(t2) from test13;
+------------+------------+
| length(t1) | length(t2) |
+------------+------------+
| 3 | NULL |
| 3 | NULL |
| 3 | 3 |
+------------+------------+
3 rows in set (0.00 sec)
#BINARY占用的空间为固定的指定的值
#VARBINARY占用的空间为可变的插入的值
mysql>
（7）BLOB
- BLOB 用来存储可变数量的二进制字符串，分为 TINYBLOB 、BLOB 、MEDIUMBLOB 、LONGBLOB 四种类型
- BLOB 存储的是二进制字符串，TEXT 存储的是文本字符串
- BLOB 没有字符集，并且排序和比较基于列值字节的数值：TEXT 有一个字符集，并且根据字符集对值进行排序和比较
| 数据类型 | 存储范围 |
|---|---|
| TINYBLOB | 最大长度为255 |
| BLOB | 最大长度为65535 |
| MEDIUMBLOB | 最大长度为16777215 |
| LONGBLOB | 最大长度为4294967295 |
二、运算符介绍
1.MySQL算术运算符
-
+：加法运算
-
-：减法运算
-
*：乘法运算
-
/：除法运算，返回商
-
%：求余运算，返回余数
mysql> select 1+2;
+-----+
| 1+2 |
+-----+
| 3 |
+-----+
1 row in set (0.01 sec)
mysql> use test2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table testOperator(test1 tinyint);
Query OK, 0 rows affected (0.42 sec)
mysql> alter table testOperator add test2 tinyint unsigned;
Query OK, 0 rows affected (0.66 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table testOperator add test3 smallint;
Query OK, 0 rows affected (0.65 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc testOperator;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| test1 | tinyint(4) | YES | | NULL | |
| test2 | tinyint(3) unsigned | YES | | NULL | |
| test3 | smallint(6) | YES | | NULL | |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into testOperator values (123,200,test1+test2);
Query OK, 1 row affected (0.01 sec)
mysql> select * from testOperator;
+-------+-------+-------+
| test1 | test2 | test3 |
+-------+-------+-------+
| 123 | 200 | 323 |
+-------+-------+-------+
1 row in set (0.00 sec)
mysql> select 12-6;
+------+
| 12-6 |
+------+
| 6 |
+------+
1 row in set (0.00 sec)
mysql> select 12*6;
+------+
| 12*6 |
+------+
| 72 |
+------+
1 row in set (0.00 sec)
mysql> select 12/6; #结果是浮点数
+--------+
| 12/6 |
+--------+
| 2.0000 |
+--------+
1 row in set (0.00 sec)
mysql> select 12%6;
+------+
| 12%6 |
+------+
| 0 |
+------+
1 row in set (0.00 sec)
mysql> select 11%6;
+------+
| 11%6 |
+------+
| 5 |
+------+
1 row in set (0.00 sec)
mysql>
2.运算符
| 运算符 | 作用 |
|---|---|
| = | 等于 |
| <=> | 安全的等于 |
| <> | 不等于，也可写成 != |
| <= | 小于等于 |
| >= | 大于等于 |
| > | 大于 |
| < | 小于 |
| IS NULL | 判断一个值是否为NULL |
| IS NOT NULL | 判断一个值是否不为NULL |
| LEAST | 当有两个或多个参数时，返回最小值 |
| GREATEST | 当有两个或多个参数时，返回最大值 |
| BETWEEN AND | 判断一个值是否落在两个值之间 |
| ISNULL 与 IS NULL | 作用相同，注意IS NULL 和ISNULL虽然作用相同，但是ISNULL一个函数 |
| IN | 判断一个值是否是 IN 列表中的任意一个值 |
| NOT IN | 判断一个值是否不是 IN 列表中的任意一个值 |
| LIKE | 通配符匹配 |
| REGEXP | 正则表达式匹配 |
（1）等于运算符 ( = )
等于运算符用来判断数字、字符串和表达式是否相等，如果相等，则返回值为 1 ，否则返回值为 0 ，如果有一个值是 NULL ，则比较结果为 NULL
mysql> select 123=345;
+---------+
| 123=345 |
+---------+
| 0 | #结果为假返回0
+---------+
1 row in set (0.00 sec)
mysql> select 123=123;
+---------+
| 123=123 |
+---------+
| 1 | #结果为真返回1
+---------+
1 row in set (0.00 sec)
mysql> select 1=0, '2'=2, (1+3)=(2+2), null=null, 1=null;
+-----+-------+-------------+-----------+--------+
| 1=0 | '2'=2 | (1+3)=(2+2) | null=null | 1=null |
+-----+-------+-------------+-----------+--------+
| 0 | 1 | 1 | NULL | NULL | #可看出不能判断一个字段是否为空
+-----+-------+-------------+-----------+--------+
1 row in set (0.00 sec)
mysql> select "test"="test"; #判断字符串
+---------------+
| "test"="test" |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
mysql>
（2）安全等于运算符（<=> ）
这个操作符和等于运算符（ = ）的作用一致，只不过多了一个功能，就是可以判断 NULL 值
mysql> select 1<=>1, '2'<=>2, (1+3)<=>(2+2), null<=>null, 1<=>null, null<=>"";
+-------+---------+---------------+-------------+----------+-----------+
| 1<=>1 | '2'<=>2 | (1+3)<=>(2+2) | null<=>null | 1<=>null | null<=>"" |
+-------+---------+---------------+-------------+----------+-----------+
| 1 | 1 | 1 | 1 | 0 | 0 |
+-------+---------+---------------+-------------+----------+-----------+
1 row in set (0.00 sec)
mysql> select "test"<=>"test";
+-----------------+
| "test"<=>"test" |
+-----------------+
| 1 |
+-----------------+
1 row in set (0.00 sec)
mysql>
（3）不等于运算符（ <> 或 != ）
不等于运算符用于判断数字、字符串、表达式是否不相等，如果不相等则返回 1，否则返回 0 ，但是不能判断NULL 值
mysql> select 1<>2, 1<>0, "good"<>"good", "test"<>"tt", null<>null, null<>1;
+------+------+----------------+--------------+------------+---------+
| 1<>2 | 1<>0 | "good"<>"good" | "test"<>"tt" | null<>null | null<>1 |
+------+------+----------------+--------------+------------+---------+
| 1 | 1 | 0 | 1 | NULL | NULL |
+------+------+----------------+--------------+------------+---------+
1 row in set (0.00 sec)
mysql> select 100!=100, 5.5!=5;
+----------+--------+
| 100!=100 | 5.5!=5 |
+----------+--------+
| 0 | 1 |
+----------+--------+
1 row in set (0.00 sec)
mysql>
（4）IS NULL 、ISNULL 、IS NOT NULL
注意IS NULL 和ISNULL虽然作用相同，但是ISNULL一个函数
- IS NULL 和 ISNULL 检验一个值是否为 NULL ，如果为 NULL ，返回值为 1，否则返回值为 0
- IS NOT NULL 检验一个值是否不为 NULL ，如果不为 NULL ，返回值为 1，否则返回值为 0
mysql> select null is null, 1 is null;
+--------------+-----------+
| null is null | 1 is null |
+--------------+-----------+
| 1 | 0 |
+--------------+-----------+
1 row in set (0.00 sec)
mysql> select isnull(null), isnull(100);
+--------------+-------------+
| isnull(null) | isnull(100) |
+--------------+-------------+
| 1 | 0 |
+--------------+-------------+
1 row in set (0.50 sec)
mysql> select null is not null, 10 is not null;
+------------------+----------------+
| null is not null | 10 is not null |
+------------------+----------------+
| 0 | 1 |
+------------------+----------------+
1 row in set (0.00 sec)
mysql>
（5）BETWEEN AND
用于判断一个值是否落在两个值之间，真返回1，假返回0
mysql> select 5 between 2 and 10; #判断5是否在2~10之间
+--------------------+
| 5 between 2 and 10 |
+--------------------+
| 1 |
+--------------------+
1 row in set (0.00 sec)
mysql> select 5 between 6 and 10;
+--------------------+
| 5 between 6 and 10 |
+--------------------+
| 0 |
+--------------------+
1 row in set (0.00 sec)
mysql> select 5 between 5 and 10; #包括下值
+--------------------+
| 5 between 5 and 10 |
+--------------------+
| 1 |
+--------------------+
1 row in set (0.00 sec)
mysql> select 10 between 5 and 10; #包括上值
+---------------------+
| 10 between 5 and 10 |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.00 sec)
mysql>
（6）LEAST 、GREATEST
- LEAST（函数） ：当有两个或多个参数时，返回最小值，如果有一个值是 NULL ，则返回结果为 NULL
- GREATEST （函数）：当有两个或多个参数时，返回最大值，如果有一个值是 NULL ，则返回结果为 NULL
mysql> select least(10,20), least(10,20,30), least('a','b','c'), least(10,null);
+--------------+-----------------+--------------------+----------------+
| least(10,20) | least(10,20,30) | least('a','b','c') | least(10,null) |
+--------------+-----------------+--------------------+----------------+
| 10 | 10 | a | NULL |
+--------------+-----------------+--------------------+----------------+
1 row in set (0.00 sec)
mysql> select greatest(10,20), greatest(10,20,30,40), greatest('a','b','c'), greatest(10,null);
+-----------------+-----------------------+-----------------------+-------------------+
| greatest(10,20) | greatest(10,20,30,40) | greatest('a','b','c') | greatest(10,null) |
+-----------------+-----------------------+-----------------------+-------------------+
| 20 | 40 | c | NULL |
+-----------------+-----------------------+-----------------------+-------------------+
1 row in set (0.51 sec)
mysql> show variables like '%char%'; #字符集：决定使用什么编码
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | gb2312 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.60 sec)
mysql> show variables like '%coll%'; #校验集：决定字符的先后顺序
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_general_ci |
| collation_database | gb2312_chinese_ci |
| collation_server | utf8_general_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)
mysql>
（7）IN 、NOT IN
两个都是将列表序列化
- IN ：判断一个值是否是 IN 列表中的任意一个值，在返回1，不在返回0
- NOT IN ：判断一个值是否不是 IN 列表中的任意一个值，取反，在返回0，不在返回1
mysql> select 3 in (1,3,5), 2 in (1,3,5);
+--------------+--------------+
| 3 in (1,3,5) | 2 in (1,3,5) |
+--------------+--------------+
| 1 | 0 |
+--------------+--------------+
1 row in set (0.00 sec)
mysql> select 3 not in (1,3,5), 2 not in (1,3,5);
+------------------+------------------+
| 3 not in (1,3,5) | 2 not in (1,3,5) |
+------------------+------------------+
| 0 | 1 |
+------------------+------------------+
1 row in set (0.00 sec)
mysql>
（8）LIKE
- LIKE 运算符用来匹配字符串，如果匹配则返回 1，如果不匹配则返回 0
- LIKE 使用两种通配符：’%’ 用于匹配任何数目的字符，包括零字符 ； ‘_’ 只能匹配一个字符
mysql> select 'test' like 'test';
+--------------------+
| 'test' like 'test' |
+--------------------+
| 1 |
+--------------------+
1 row in set (0.00 sec)
mysql> select 'test' like 'te%', 'hest' like 'te%', 'an' like 'an%', 'test' like '%e';
+-------------------+-------------------+-----------------+------------------+
| 'test' like 'te%' | 'hest' like 'te%' | 'an' like 'an%' | 'test' like '%e' |
+-------------------+-------------------+-----------------+------------------+
| 1 | 0 | 1 | 0 |
+-------------------+-------------------+-----------------+------------------+
1 row in set (0.52 sec)
mysql> select 'yes' like 'ye_', 'yesterday' like 'ye_', 'yes' like 'yes_', 'yes' like '_es';
+------------------+------------------------+-------------------+------------------+
| 'yes' like 'ye_' | 'yesterday' like 'ye_' | 'yes' like 'yes_' | 'yes' like '_es' |
+------------------+------------------------+-------------------+------------------+
| 1 | 0 | 0 | 1 |
+------------------+------------------------+-------------------+------------------+
1 row in set (0.00 sec)
mysql>
（9）REGEXP（正则表达式）
- REGEXP 运算符用来匹配字符串，如果匹配则返回 1，如果不匹配则返回 0
- REGEXP 使用几种通配符：
- ‘^’ 用于匹配以什么开头的字符串
- ‘$’ 用于匹配以什么结尾的字符串
- ‘.’ 用于匹配任何一个单字符串
- ‘[…]’ 用于匹配在方括号内的任何字符
- ‘*’ 用于匹配零个或多个在它前面的字符
mysql> select "yes" regexp "yes", "yesterday" regexp "yesteryear";
+--------------------+---------------------------------+
| "yes" regexp "yes" | "yesterday" regexp "yesteryear" |
+--------------------+---------------------------------+
| 1 | 0 |
+--------------------+---------------------------------+
1 row in set (0.00 sec)
mysql> select 'yes' regexp '^y', 'yes' regexp '^t', 'yes' regexp 'y$', 'yes' like 'ye.', 'yes' regexp 'y[a-z]', 'yes' regexp 'y*';
+-------------------+-------------------+-------------------+------------------+-----------------------+-------------------+
| 'yes' regexp '^y' | 'yes' regexp '^t' | 'yes' regexp 'y$' | 'yes' like 'ye.' | 'yes' regexp 'y[a-z]' | 'yes' regexp 'y*' |
+-------------------+-------------------+-------------------+------------------+-----------------------+-------------------+
| 1 | 0 | 0 | 0 | 1 | 1 |
+-------------------+-------------------+-------------------+------------------+-----------------------+-------------------+
1 row in set (0.00 sec)
mysql> select 'yes' regexp 'y.*', 'y4es' regexp 'y[a-z]', 'y4es' regexp 'y[a-z]*';
+--------------------+------------------------+-------------------------+
| 'yes' regexp 'y.*' | 'y4es' regexp 'y[a-z]' | 'y4es' regexp 'y[a-z]*' |
+--------------------+------------------------+-------------------------+
| 1 | 0 | 1 |
+--------------------+------------------------+-------------------------+
1 row in set (0.00 sec)
mysql> select 'test' regexp 't[a,b,e]sx', 'hello' regexp 'h[e,l,o]', 'yes' regexp 'y[es]';
+----------------------------+---------------------------+----------------------+
| 'test' regexp 't[a,b,e]sx' | 'hello' regexp 'h[e,l,o]' | 'yes' regexp 'y[es]' |
+----------------------------+---------------------------+----------------------+
| 0 | 1 | 1 |
+----------------------------+---------------------------+----------------------+
1 row in set (0.00 sec)
mysql>
3.MySQL逻辑运算符
| 运算符 | 作用 |
|---|---|
| NOT 或 ！ | 逻辑非 |
| AND 或 && | 逻辑与 |
| OR 或 || | 逻辑或 |
| XOR | 逻辑异或 |
（1）逻辑非 ( NOT 或 ！)
- 当操作数为 0 时，所得值为 1
- 当操作数为非 0 时，所得值为 0
- 当操作数为 NULL 时，所得值为 NULL
将值取反
mysql> select not 1, not false, not true, not "", not "a", not (1-1), not 1+1, not null;
+-------+-----------+----------+--------+---------+-----------+---------+----------+
| not 1 | not false | not true | not "" | not "a" | not (1-1) | not 1+1 | not null |
+-------+-----------+----------+--------+---------+-----------+---------+----------+
| 0 | 1 | 0 | 1 | 1 | 1 | 0 | NULL |
+-------+-----------+----------+--------+---------+-----------+---------+----------+
1 row in set, 1 warning (0.00 sec)
mysql> select !10, !(10), !(1), !(0), !(-1), !(1-1); #结果为0返回1，结果为其他返回0
+-----+-------+------+------+-------+--------+
| !10 | !(10) | !(1) | !(0) | !(-1) | !(1-1) |
+-----+-------+------+------+-------+--------+
| 0 | 0 | 0 | 1 | 0 | 1 |
+-----+-------+------+------+-------+--------+
1 row in set (0.00 sec)
mysql> select !(false), !(true); #使用！，最好添加（）
+----------+---------+
| !(false) | !(true) |
+----------+---------+
| 1 | 0 |
+----------+---------+
1 row in set (0.00 sec)
mysql>
（2）逻辑与 ( AND 或 && )
- 当所有操作数均为非零值、并且不为 NULL 时，所得值为 1
- 当一个或多个操作数为 0 时，所得值为 0
- 其余情况所得值为 NULL
mysql> select 1 and 2, 1 and 0, 0 and 1, 1 && 2, 1 && 0, 0 && 1, 1 and -1, 0 and null, 1 && null;
+---------+---------+---------+--------+--------+--------+----------+------------+-----------+
| 1 and 2 | 1 and 0 | 0 and 1 | 1 && 2 | 1 && 0 | 0 && 1 | 1 and -1 | 0 and null | 1 && null |
+---------+---------+---------+--------+--------+--------+----------+------------+-----------+
| 1 | 0 | 0 | 1 | 0 | 0 | 1 | 0 | NULL |
+---------+---------+---------+--------+--------+--------+----------+------------+-----------+
1 row in set (0.00 sec)
mysql>
（3）逻辑或 ( OR 或 || )
- 当两个操作数均为非 NULL 值，且任意一个操作数为非零值时，结果为 1 ，否则为 0
- 当有一个操作数为 NULL ，且另一个操作数为非零值时，则结果为 1 ，否则结果为 NULL
- 当两个操作数均为 NULL 时，则所得结果为 NULL
语法：
mysql> select 2 or -2 or 0, 2 || 0 || -2, 0 or 0, 0 or null, null || null;
+--------------+--------------+--------+-----------+--------------+
| 2 or -2 or 0 | 2 || 0 || -2 | 0 or 0 | 0 or null | null || null |
+--------------+--------------+--------+-----------+--------------+
| 1 | 1 | 0 | NULL | NULL |
+--------------+--------------+--------+-----------+--------------+
1 row in set (0.00 sec)
mysql>
（4）逻辑异或 ( XOR )
- a XOR b 的计算等同于 ( a AND (NOT b) ) 或 ( (NOT a) AND b )
- 当任意一个操作数为 NULL 时，返回值为 NULL
- 对于非 NULL 的操作数，如果两个操作数都是非 0 值或者都是 0 值，则返回结果为 0
- 如果一个为 0 值，另一个为非 0 值，返回结果为 1
mysql> select 1 xor 1, 0 xor 0, 1 xor 0, 0 xor 1, 1 xor null, 1 xor 1 xor 1;
+---------+---------+---------+---------+------------+---------------+
| 1 xor 1 | 0 xor 0 | 1 xor 0 | 0 xor 1 | 1 xor null | 1 xor 1 xor 1 |
+---------+---------+---------+---------+------------+---------------+
| 0 | 0 | 1 | 1 | NULL | 1 |
+---------+---------+---------+---------+------------+---------------+
1 row in set (0.00 sec)
mysql>
4.位运算符
| 运算符 | 作用 |
|---|---|
| | | 位或 |
| & | 位与 |
| ^ | 位异或 |
| << | 位左移 |
| >> | 位右移 |
| ~ | 位取反 |
（1）位或运算符 ( | )
对应的二进制位有一个或两个为 1 ，则该位的运算结果为 1 ，否则为 0
mysql> select 10 | 15, 8 | 4 | 2;
+---------+-----------+
| 10 | 15 | 8 | 4 | 2 |
+---------+-----------+
| 15 | 14 |
+---------+-----------+
1 row in set (0.00 sec)
# 10的二进制1010
# 15的二进制1111
#位或运算结果1111，即为15
# 9的二进制1000
# 4的二进制0100
# 2的二进制0010
#位或运算结果1110，即为14
mysql>
（2）位与运算符 ( & )
对应的二进制位都为 1 ，则该位的运算结果为 1 ，否则为 0
mysql> select 10 & 15, 8 & 4 & 2;
+---------+-----------+
| 10 & 15 | 8 & 4 & 2 |
+---------+-----------+
| 10 | 0 |
+---------+-----------+
1 row in set (0.00 sec)
# 10的二进制1010
# 15的二进制1111
#位或运算结果1010，即为10
# 9的二进制1000
# 4的二进制0100
# 2的二进制0010
#位或运算结果0000，即为0
mysql>
（3）位异或运算符 ( ^ )
对应的二进制位不相同时，结果为 1 ，否则为 0
mysql> select 10 ^ 15, 8 ^ 4 ^ 2;
+---------+-----------+
| 10 ^ 15 | 8 ^ 4 ^ 2 |
+---------+-----------+
| 5 | 14 |
+---------+-----------+
1 row in set (0.00 sec)
# 10的二进制1010
# 15的二进制1111
#位或运算结果0101，即为5
# 9的二进制1000
# 4的二进制0100
# 2的二进制0010
#位或运算结果1110，即为14
mysql>
（4）位左移运算符 ( << )
使指定的二进制位都左移指定的位数，左移指定位之后，左边高位的数值将被移出并丢弃，右边低位空出的位置用 0 补齐
mysql> select 1<<2, 4<<2, 28<<5;
+------+------+-------+
| 1<<2 | 4<<2 | 28<<5 |
+------+------+-------+
| 4 | 16 | 896 |
+------+------+-------+
1 row in set (0.01 sec)
# 1的二进制值为00000001，左移2位之后变成00000100，即十进制数4
# 4的二进制值为00000100，左移2位之后变成00010000，即十进制数16
# 4的二进制值为00011100，左移5位之后变成1110000000，即十进制数896
mysql>
（5）位右移运算符 ( >> )
使指定的二进制位都右移指定的位数，右移指定位之后，右边低位的数值将被移出并丢弃，左边高位空出的职位用 0 补齐
mysql> select 1>>1, 16>>2;
+------+-------+
| 1>>1 | 16>>2 |
+------+-------+
| 0 | 4 |
+------+-------+
1 row in set (0.00 sec)
# 1的二进制值为00000001，右移1位之后变成00000000，即十进制数0
#16的二进制值为00010000，左移2位之后变成00000100，即十进制数4
mysql>
（6）位取反运算符 ( ~ )
将对应的二进制数逐位反转，即 1 取反后变 0 ， 0 取反后变 1
mysql> select 5 & ~1;
+--------+
| 5 & ~1 |
+--------+
| 4 |
+--------+
1 row in set (0.00 sec)
#1的二进制值为00000001，
#~取反后为 11111110，
#5的二进制值为00000101，
#&后值为 00000100，&只有两个都为1才为1，故转为十进制为4
mysql>
5.优先级（从低到高）
最低：
=（赋值运算） || or
XOR
&& AND
NOT
BETWEEN
=(比较运算) ， <=>， >=， <>， <=， < ，> ， != ，IS， LIKE， IN ，REGEXP
|
&
<< >>
- +
* / %
-(负号) ~(位取反)
!
三、补充：
1.占位符的使用（zerofill）
zerofill：位数不够用0补齐，无该限制时，不补齐位数
mysql> create table t2(id smallint(4)); #4表示占位符
Query OK, 0 rows affected (0.52 sec)
mysql> insert into t2 values (1234);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 values (12345);
Query OK, 1 row affected (0.01 sec)
mysql> alter table t2 add t3 smallint(4) zerofill;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into t2 (t3) values (12);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 (t3) values (12345);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t2;
+-------+-------+
| id | t3 |
+-------+-------+
| 1234 | NULL |
| 12345 | NULL |
| NULL | 0012 |
| NULL | 12345 |
+-------+-------+
4 rows in set (0.00 sec)
mysql>
这只是我的一些浅薄的见解，望多指教！
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
