How to add fields to the database table-Mysql Tutorial-php.cn

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.

How to add fields to the database table

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 <新字段名><数据类型>[约束条件];

Copy after login

The syntax format is explained as follows:

is the name of the data table;

is the name of the field to be added;

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;

Copy after login

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 <已经存在的字段名>;

Copy after login

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)

Copy after login

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)

Copy after login

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)

Copy after login

#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)

Copy after login

(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!

Related labels：

mysql database Field

source：php.cn

Previous article：How to delete table from database Next article：How to delete data in the data table

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn