How to add columns (fields) in mysql

In mysql, you can use the "ALTER TABLE" statement and the ADD keyword to add columns (fields). The syntax is "ALTER TABLE table name ADD new field name data type [constraints];"; this kind The syntax format adds columns (fields) at the end of the data table.

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

MySQL data tables are composed of rows and columns. Usually the "columns" of the table are called fields (Field), and the "rows" of the table are called records (Record). As your business changes, you may need to add new fields to existing tables.

In mysql, you can use the "ALTER TABLE" statement and the ADD keyword to add columns (fields).

Grammar:

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

The syntax format is explained as follows:                                  

• Table name: The name of the data table;

• New field name: is the name of the field to be added;

• Data type : The data type that can store data for the field to be added;

• [Constraints]: It is optional and used to add fields to constrain.

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

Example:

We have a student data table, 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)

Use the ALTER TABLE statement to add an INT type field age, and look at student Table structure

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)

It can be seen that the age field has been added to the student table, and the field is at the last position of the table. The field was added successfully.

So what do you need to do if you want to add a field at the beginning or in the middle?

• If you want to add a new field at the beginning (in front of the first column), you need to use the FIRST keyword

• If you want to add a new field in the middle, you need to use the AFTER keyword

##The syntax format is as follows:

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

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

Example :

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)

A stuId field is added to the student table, and the field is in the first position in the table

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)

A stuId field is added to the student table, and the field is in the name field The following position

[Related recommendations:

mysql video tutorial]

The above is the detailed content of How to add columns (fields) in mysql. For more information, please follow other related articles on the PHP Chinese website!