MySQL如何增加表和給表字段賦值
更新時間 2023-12-16 22:24:34
最近更新時間: 2023-12-16 22:24:34
分享文章
本頁介紹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)
