char和varchar的区别：

varchar会把后面的空格存进去 char会自动去除后面存的多余的空格
但两者前面的空格都不会去掉
示列：

  1. mysql> show create table user_test;
  2. +-----------+-----------------------------------------------------------------------------------------------------------------------------------+
  3. | Table     | Create Table                                                                                                                      |
  4. +-----------+-----------------------------------------------------------------------------------------------------------------------------------+
  5. | user_test | CREATE TABLE `user_test` (
  6.   `name` varchar(6) DEFAULT NULL,
  7.   `phton` char(11) DEFAULT NULL
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
  9. +-----------+-----------------------------------------------------------------------------------------------------------------------------------+
  10. 1 row in set (0.00 sec)
  11. #:在name后面和phton后面都添加上空格
  12. mysql> insert into user_test values ('aaaaa ','1234567891 ');
  13. Query OK, 1 row affected (0.00 sec)
  15. mysql> select * from user_test;
  16. +--------+------------+
  17. | name   | phton      |
  18. +--------+------------+
  19. | aaaaa  | 1234567891 |
  20. +--------+------------+
  21. 1 row in set (0.00 sec)
  22. #；使用字符串拼接发现name  显示空格，phton不显示空格
  23. mysql> select concat(",",name,",") as 姓名,concat(",",phton,".")from user_test;
  24. +----------+-----------------------+
  25. | 姓名     | concat(",",phton,".") |
  26. +----------+-----------------------+
  27. | ,aaaaa , | ,1234567891.          |
  28. +----------+-----------------------+
  29. 1 row in set (0.00 sec)
  31. mysql>

2.datetime和timestamp的区别

 datetime:添加的时间只是第一次添加时候的时间，且不会自动更新.<br />     timestamp:会自动添加时间,同时，没一次的更新操作都会自动更新时间<br />示列:

CREATE TABLE `tb1` (
  `name` varchar(10) NOT NULL,
  `add_time` datetime DEFAULT NULL,
  `moidfy_time` timestamp
) ENGINE=InnoDB DEFAULT CHARSET=utf8
##:初次插入时，会自动插入时间
mysql> insert into tb1 (name,add_time) values ('杨晴',now());
Query OK, 1 row affected (0.00 sec)
mysql> select * from tb1;
+------+---------------------+---------------------+
| name | add_time            | moidfy_time         |
+------+---------------------+---------------------+
| 杨晴 | 2021-06-07 20:16:13 | 2021-06-07 20:16:13 |
+------+---------------------+---------------------+
1 row in set (0.00 sec)
##：更新时会自己动更新时间
mysql> update tb1 set name='小可爱';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tb1;
+--------+---------------------+---------------------+
| name   | add_time            | moidfy_time         |
+--------+---------------------+---------------------+
| 小可爱 | 2021-06-07 20:16:13 | 2021-06-07 20:17:07 |
+--------+---------------------+---------------------+
1 row in set (0.00 sec)

enum的使用：

mysql中使用enum类型会给人想象不到的好处；
示列:

mysql> alter table add sex enum('男','女');
mysql> select * from tb1;
+--------+---------------------+---------------------+------+
| name   | add_time            | moidfy_time         | sex  |
+--------+---------------------+---------------------+------+
| 小可爱 | 2021-06-07 20:16:13 | 2021-06-07 20:17:07 | NULL |
+--------+---------------------+---------------------+------+
mysql> insert into tb1 (name,add_time,sex) values('大可爱',now(),'男');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb1;
+--------+---------------------+---------------------+------+
| name   | add_time            | moidfy_time         | sex  |
+--------+---------------------+---------------------+------+
| 大可爱 | 2021-06-07 20:25:16 | 2021-06-07 20:25:16 | 男   |
| 小可爱 | 2021-06-07 20:16:13 | 2021-06-07 20:17:07 | NULL |
+--------+---------------------+---------------------+------+
2 rows in set (0.00 sec)
##:在添加数据的时候，只能添加enum中定义的数据
mysql> insert into tb1 (name,add_time,sex) values('大可爱',now(),'未知');
ERROR 1265 (01000): Data truncated for column 'sex' at row 1
##：enum中定义的数据都有顺序，所以可以使用数字来代替插入。
mysql> insert into tb1 (name,add_time,sex) values('诸葛亮',now(),'1');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb1;
+--------+---------------------+---------------------+------+
| name   | add_time            | moidfy_time         | sex  |
+--------+---------------------+---------------------+------+
| 大可爱 | 2021-06-07 20:25:16 | 2021-06-07 20:25:16 | 男   |
| 小可爱 | 2021-06-07 20:16:13 | 2021-06-07 20:17:07 | NULL |
| 诸葛亮 | 2021-06-07 20:25:57 | 2021-06-07 20:25:57 | 男   |
+--------+---------------------+---------------------+------+
3 rows in set (0.00 sec)

mysql> update tb1 set sex=2 where name='小可爱';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tb1;
+--------+---------------------+---------------------+------+
| name   | add_time            | moidfy_time         | sex  |
+--------+---------------------+---------------------+------+
| 大可爱 | 2021-06-07 20:25:16 | 2021-06-07 20:25:16 | 男   |
| 小可爱 | 2021-06-07 20:16:13 | 2021-06-07 20:26:41 | 女   |
| 诸葛亮 | 2021-06-07 20:25:57 | 2021-06-07 20:25:57 | 男   |
+--------+---------------------+---------------------+------+
3 rows in set (0.00 sec)

decimal

decimal适和在表示金额的字段中的数据类型。在插入时候，可以设置数字的位数，已经小数点后的位数，同时，如果插入的数据小数点大于指定的那么多余的值会按照四舍五入的情况截取。但是在计算表的字段的时候不会四舍五入
示列：

mysql> create table monery (a decimal(10,4));##表示最多可以插入10为数字，其中后四位为小数位
Query OK, 0 rows affected (0.03 sec)
mysql> insert into monery values(159855.2354);##最大的插入位数
Query OK, 1 row affected (0.00 sec)
mysql> insert into monery values(159855.2354789);##插入的多余小数位会四舍五入
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from monery;
+-------------+
| a           |
+-------------+
| 159855.2355 |
+-------------+
1 row in set (0.00 sec)
mysql> insert into monery values(1258.2334);
Query OK, 1 row affected (0.00 sec)
##计算的时候不会四舍五入
mysql> select sum(a) from monery;
+-------------+
| sum(a)      |
+-------------+
| 161113.4689 |
+-------------+
1 row in set (0.00 sec)

mysql>

int(x)和int(y)的区别：

这两者本身没有任何区别，都会在存储数据超过指定宽度之后不能插入。但是如果配合zerofill来使用的话，当不足指定的宽度时会在前面补0；

mysql> show create table iwidth \G
*************************** 1. row ***************************
       Table: iwidth
Create Table: CREATE TABLE `iwidth` (
  `aid` int(4) unsigned zerofill DEFAULT NULL,
  `bid` int(10) unsigned zerofill DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> create table iwidth (aid int(4) zerofill,bid int(10) zerofill);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into iwidth select 1,2;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from iwidth;
+------+------------+
| aid  | bid        |
+------+------------+
| 0001 | 0000000002 |
+------+------------+
1 row in set (0.00 sec)

mysql> insert into iwidth select 1234,123456789;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from zf;
+------+------------+
| aid  | bid        |
+------+------------+
| 0001 | 0000000002 |
+------+------------+
1 row in set (0.00 sec)

mysql> select * from iwidth;
+------+------------+
| aid  | bid        |
+------+------------+
| 0001 | 0000000002 |
| 1234 | 0123456789 |
+------+------------+
2 rows in set (0.00 sec)