MySQL如何增加表和给表字段赋值

MySQL增加表和给表字段赋值

MySQL数据库对增加表和给表字段赋值的操作提供了以下几种方式：

#增加表和表结构
 CREATE TABLE tianyiyun (
 uid MEDIUMINT NOT NULL AUTO_INCREMENT,
 user CHAR(10) NOT NULL,
 PRIMARY KEY (uid)
 );

1.不对新增字段赋值，数据库会自动赋值到字段中。

mysql> INSERT INTO tianyiyun (user) VALUES ('a'),('b'),('c'),('d'),('e'),('f');
 Query OK, 6 rows affected (0.01 sec)
 Records: 6  Duplicates: 0  Warnings: 0
 mysql> select * from tianyiyun;
 +-----+------+
 | uid | user |
 +-----+------+
 |   1 | a    |
 |   2 | b    |
 |   3 | c    |
 |   4 | d    |
 |   5 | e    |
 |   6 | f    |
 +-----+------+
 6 rows in set (0.00 sec)
 
 mysql> show create table tianyiyun;
 +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | Table     | Create Table                                                                                                                                                                                                                |
 +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | tianyiyun | CREATE TABLE `tianyiyun` (
   `uid` mediumint(9) NOT NULL AUTO_INCREMENT,
   `user` char(10) COLLATE utf8mb4_bin NOT NULL,
   PRIMARY KEY (`uid`)
 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
 +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 1 row in set (0.00 sec)
 
 mysql>

2.新增字段赋值0或者null，数据库会将新增的值填写到字段中。

mysql> INSERT INTO tianyiyun (uid,user) VALUES(0,'g');
 Query OK, 1 row affected (0.00 sec)
 mysql> INSERT INTO tianyiyun (uid,user) VALUES(NULL,'h');
 Query OK, 1 row affected (0.00 sec)
 mysql> select * from tianyiyun;
 +-----+------+
 | uid | user |
 +-----+------+
 |   1 | a    |
 |   2 | b    |
 |   3 | c    |
 |   4 | d    |
 |   5 | e    |
 |   6 | f    |
 |   7 | g    |
 |   8 | h    |
 +-----+------+
 8 rows in set (0.00 sec)
 
 mysql> show create table tianyiyun;
 +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | Table     | Create Table                                                                                                                                                                                                                |
 +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | tianyiyun | CREATE TABLE `tianyiyun` (
   `uid` mediumint(9) NOT NULL AUTO_INCREMENT,
   `user` char(10) COLLATE utf8mb4_bin NOT NULL,
   PRIMARY KEY (`uid`)
 ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
 +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 1 row in set (0.00 sec)

3.使用大于AUTO_INCREMENT的值，数据库会将AUTO_INCREMEN的值增加，该值也会新增到字段里面。

mysql> INSERT INTO tianyiyun (uid,user) VALUES(99,'i');
 Query OK, 1 row affected (0.00 sec)
 mysql> select * from tianyiyun;
 +-----+------+
 | uid | user |
 +-----+------+
 |   1 | a    |
 |   2 | b    |
 |   3 | c    |
 |   4 | d    |
 |   5 | e    |
 |   6 | f    |
 |   7 | g    |
 |   8 | h    |
 |  99 | i    |
 +-----+------+
 9 rows in set (0.00 sec)
 mysql> show create table tianyiyun;
 +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | Table     | Create Table                                                                                                                                                                                                                  |
 +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | tianyiyun | CREATE TABLE `tianyiyun` (
   `uid` mediumint(9) NOT NULL AUTO_INCREMENT,
   `user` char(10) COLLATE utf8mb4_bin NOT NULL,
   PRIMARY KEY (`uid`)
 ) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
 +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 1 row in set (0.00 sec)

4.使用小于AUTO_INCREMENT的值，不会影响到数据插入。

mysql> INSERT INTO tianyiyun (uid,user) VALUES(30,'j');
 Query OK, 1 row affected (0.00 sec)
 mysql> select * from tianyiyun;
 +-----+------+
 | uid | user |
 +-----+------+
 |   1 | a    |
 |   2 | b    |
 |   3 | c    |
 |   4 | d    |
 |   5 | e    |
 |   6 | f    |
 |   7 | g    |
 |   8 | h    |
 |  30 | j    |
 |  99 | i    |
 +-----+------+
 10 rows in set (0.00 sec)
 mysql> show create table tianyiyun;
 +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | Table     | Create Table                                                                                                                                                                                                                  |
 +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | tianyiyun | CREATE TABLE `tianyiyun` (
   `uid` mediumint(9) NOT NULL AUTO_INCREMENT,
   `user` char(10) COLLATE utf8mb4_bin NOT NULL,
   PRIMARY KEY (`uid`)
 ) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
 +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 1 row in set (0.00 sec)

5.使用负值，数据也能正常插入。

mysql> INSERT INTO tianyiyun (uid,user) VALUES(-30,'k');
 Query OK, 1 row affected (0.00 sec)
 mysql> select * from tianyiyun;
 +-----+------+
 | uid | user |
 +-----+------+
 | -30 | k    |
 |   1 | a    |
 |   2 | b    |
 |   3 | c    |
 |   4 | d    |
 |   5 | e    |
 |   6 | f    |
 |   7 | g    |
 |   8 | h    |
 |  30 | j    |
 |  99 | i    |
 +-----+------+
 11 rows in set (0.00 sec)
 mysql> show create table tianyiyun;
 +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | Table     | Create Table                                                                                                                                                                                                                  |
 +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | tianyiyun | CREATE TABLE `tianyiyun` (
   `uid` mediumint(9) NOT NULL AUTO_INCREMENT,
   `user` char(10) COLLATE utf8mb4_bin NOT NULL,
   PRIMARY KEY (`uid`)
 ) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
 +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 1 row in set (0.00 sec)