Adding Tables to MySQL and Assigning Values to Table Fields
You can add tables to MySQL database and assign values to table fields in the following ways:
# Adding Tables And Table Structures CREATE TABLE tianyiyun ( uid MEDIUMINT NOT NULL AUTO_INCREMENT, user CHAR(10) NOT NULL, PRIMARY KEY (uid) ); |
1. If no value is assigned to a new field, the database automatically assigns the value to the field.
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. If 0 or Null is assigned to a new field, the database automatically enters the value to the field.
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. If a value greater than the value of AUTO_INCREMENT is specified, the database increases the value of AUTO_INCREMEN and adds the value to the field.
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. If a value less than the value of AUTO_INCREMENT is specified, it does not affect the data insertion.
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. If a negative value is specified, the data can also be inserted.
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) |