How mysql adds columns

This article mainly explains "how to add columns in mysql". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's ideas to study and learn "how to add columns in mysql".

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

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

A MySQL data table is made up of rows and columns. The "column" of the table is usually called a Field, and the "row" of a table is called a Record. As the 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).

Syntax:

ALTER TABLE table name ADD new field name data type [constraint]

The syntax format is described as follows:

Table name: the name of the data table

New field name: the name of the field to be added

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

[constraint]: is optional and is used to constrain the added fields.

By default, this syntax format adds a new field at the end of the table (after the last column).

Example:

We have a student data table and use DESC to view the structure of the student table

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.01sec)

Use the ALTER TABLE statement to add a field of type INT age, and take a look at the student table structure

Mysql > ALTER TABLE student ADD age INT (4); Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > 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)

You can see that the age field has been added to the student table, and the field is in the last position of the table. The field was added successfully.

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

If you want to add a new field at the beginning (before 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 table name ADD new field name data type [constraint] FIRST;ALTER TABLE table name ADD new field name data type [constraint] AFTER

Example:

Mysql > ALTER TABLE student ADD stuId INT (4) FIRST;Query OK, 0 rows affected (0.14 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > 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 has been added to the student table, and it 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 has been added to the student table, and the field is located after the name field

Thank you for your reading, the above is the content of "how to increase the column of mysql". After the study of this article, I believe you have a deeper understanding of how to increase the column of mysql, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!