Shulou(Shulou.com)06/01 Report--

Editor to share with you how mysql adds columns to the table. I hope you will gain a lot after reading this article. Let's discuss it together.

Mysql add columns to the table: 1, use "ALTER TABLE table name ADD new field name data type [constraint];" add a column at the end; 2, use "ALTER TABLE table name ADD new field name data type [constraint] FIRST;" to add a column at the beginning.

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.

MySQL allows you to add fields at the beginning, middle, and end.

Add a field at the end

A complete field includes the field name, data type, and constraints. The syntax format of the MySQL add field is as follows:

ALTER TABLE ADD [constraint]

The syntax format is described as follows:

Is the name of the data table

The name of the field to be added

The data type that can store data for the field to be added

[constraints] are optional and are used to constrain added fields.

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

Note: in this section we only add new fields and do not pay attention to its constraints.

Example 1

Create a new student data table in the test database, and the SQL statement and run result are as follows:

Mysql > USE test;Database changedmysql > CREATE TABLE student (- > id INT (4),-> name VARCHAR (20),-> sex CHAR (1); Query OK, 0 rows affected (0.09 sec)

Use DESC to view the student table structure, the SQL statement and the run result are as follows:

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,SQL statement and run the result as follows:

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

Use DESC to view the structure of the student table and verify that the age field is added successfully. The SQL statement and the run result are as follows:

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)

As you can see from the running results, the age field has been added to the student table, and the field is in the last position of the table, and the field is added successfully.

Add a field at the beginning

By default, MySQL adds a new field at the end of the table. If you want to add a new field at the beginning (before the first column), you can use the FIRST keyword with the syntax format as follows:

ALTER TABLE ADD [constraint] FIRST

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

Example 2

Use the ALTER TABLE statement to add a field of type INT to the first column of the table, the stuId,SQL statement and the run results are shown below.

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)

As you can see from the running results, the stuId field has been added to the student table, and the field is in the first position in the table.

Add a field in the middle

MySQL not only allows fields to be added at the beginning and end of the table, but also allows fields to be added in the middle (after the specified field). In this case, the AFTER keyword is required, and the syntax format is as follows:

ALTER TABLE ADD [constraint] AFTER

The role of AFTER is to add a new field to the end of an existing field.

Note that you can only add a new field after an existing field, not before it.

Example 3

Use the ALTER TABLE statement to add a field named stuno and data type INT to the student table, with the stuno field after the name field. The SQL statement and the run result are as follows:

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)

As you can see from the running results, the stuId field has been added to the student table, and the field is in the position after the name field. The field was added successfully.

After reading this article, I believe you have a certain understanding of mysql's method of adding columns to the table. If you want to know more about it, welcome to follow the industry information channel. Thank you for your reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.