How to add fields to the database table

Methods to add fields in the table: 1. Use the "ALTER TABLE table name ADD new field name data type;" statement to add fields at the end; 2. Use "ALTER TABLE table name ADD new field name data type FIRST ;" statement adds fields at the beginning; 3. Use the "ALTER TABLE table name ADD new field name data type [constraints] AFTER existing field name;" statement to add fields in the middle.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

How to add fields in the table in Mysql database

1. Add fields at the end

A complete field includes field name, data type and constraints. The syntax format for adding fields in MySQL is as follows:

ALTER TABLE <表名> ADD <新字段名><数据类型>[约束条件];

The syntax format is explained as follows:                                                        

• 249823cbf09623dd508dcdf0f05edb2a is the name of the data table;

• 54a54d14e22f2e75e56c180fa5bad895 is the name of the field to be added;

• 4cd7266a2a3b9d11d200585237d5f70a is the field that can store data Data type;

• [Constraints] is optional and is used to constrain the added fields.

This syntax format adds a new field at the last position of the table (after the last column) by default.

2. Add a field at the beginning

MySQL defaults to adding a new field at the last position of the table. If you want to add a new field at the beginning (before the first column) To add a new field, you can use the FIRST keyword. The syntax format is as follows:

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] FIRST;

The FIRST keyword is generally placed at the end of the statement.

3. Add fields in the middle position

In addition to allowing fields to be added at the beginning and end of the table, MySQL also allows fields to be added in the middle position (after the specified field ) to add a field, you need to use the AFTER keyword at this time, the syntax format is as follows:

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] AFTER <已经存在的字段名>;

The function of AFTER is to add a new field after an existing field.

Note that you can only add a new field after an existing field, but not in front of it.

Example:

Use DESC to view the student table structure

mysql> DESC student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)      | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | char(1)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

1. Add an INT type at the end The field age

mysql> ALTER TABLE student ADD age INT(4);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)      | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | char(1)     | YES  |     | NULL    |       |
| age   | int(4)      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

2. Add the INT type field stuId

mysql> ALTER TABLE student ADD stuId INT(4) FIRST;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| stuId | int(4)      | YES  |     | NULL    |       |
| id    | int(4)      | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | char(1)     | YES  |     | NULL    |       |
| age   | int(4)      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

#3 in the first column of the table. After the name field Add a field named stuno with data type INT

mysql> ALTER TABLE student ADD stuno INT(11) AFTER name;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> DESC student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| stuId | int(4)      | YES  |     | NULL    |       |
| id    | int(4)      | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| stuno | int(11)     | YES  |     | NULL    |       |
| sex   | char(1)     | YES  |     | NULL    |       |
| age   | int(4)      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

(Recommended tutorial: mysql video tutorial)

The above is the detailed content of How to add fields to the database table. For more information, please follow other related articles on the PHP Chinese website!