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

This article mainly introduces the case of modifying the mysql database table, the article is very detailed, has a certain reference value, interested friends must read it!

The method of modifying the mysql database table: using the "ALTER TABLE" statement, you can change the structure of the original table, such as adding or deleting fields, modifying the original field data type, renaming the field or table, modifying the table character set, etc.; syntax "ALTER TABLE [modify options]".

The premise of modifying a data table is that the table already exists in the database. Modifying a table refers to modifying the structure of a data table that already exists in the database. The operation of modifying the data table is also essential in database management, just like drawing a sketch, you can erase it with an eraser if you draw too much, and add it with a pen if you draw less.

Not knowing how to modify the data table is equivalent to throwing away the redrawing as soon as we make a mistake, which increases the unnecessary cost.

In MySQL, you can use ALTER TABLE statements to change the structure of the original table, such as adding or deleting columns, changing the original column type, renaming columns or tables, and so on.

The syntax format is as follows:

ALTER TABLE [modify options]

The syntax format of the modification option is as follows:

{ADD COLUMN | CHANGE COLUMN | ALTER COLUMN {SET DEFAULT | DROP DEFAULT} | MODIFY COLUMN | DROP COLUMN | RENAME TO | CHARACTER SET | COLLATE}

Modify table name

MySQL modifies the table name through the ALTER TABLE statement. The syntax rules are as follows:

ALTER TABLE RENAME [TO]

Among them, TO is an optional parameter, and whether it is used or not does not affect the result.

Example 1

Use ALTER TABLE to rename the datasheet student to the tb_students_info,SQL statement and run the result as shown below.

Mysql > ALTER TABLE student RENAME TO tb_students_info;Query OK, 0 rows affected (0.01 sec) mysql > SHOW TABLES;+-+ | Tables_in_test | +-+ | tb_students_info | +-+ 1 row in set (0.00 sec)

Tip: changing the table name does not change the structure of the table, so the structure of the table after the name change is the same as that before the name change. Users can use the DESC command to view the modified table structure

Modify table character set

MySQL modifies the character set of the table through the ALTER TABLE statement. The syntax rules are as follows:

ALTER TABLE table name [DEFAULT] CHARACTER SET [DEFAULT] COLLATE

Among them, DEFAULT is an optional parameter, and whether it is used or not does not affect the result.

Example 2

Use ALTER TABLE to change the character set of the datasheet tb_students_info to gb2312 and the proofreading rule to gb2312_chinese_ci. The SQL statement and the result of the run are shown below.

Mysql > ALTER TABLE tb_students_info CHARACTER SET gb2312 DEFAULT COLLATE gb2312_chinese_ci Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > SHOW CREATE TABLE tb_students_info\ Graph * 1. Row * * Table: tb_students_infoCreate Table: CREATE TABLE `tb_students_ info` (`id` int (11) NOT NULL `name` varchar (20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`) ENGINE=MyISAM DEFAULT CHARSET=gb23121 row in set (0.00 sec)

MySQL data Table add Field

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

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

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

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.

MySQL modifies and deletes datasheet fields

Modify field name

The syntax rules for modifying table field names in MySQL are as follows:

ALTER TABLE CHANGE

Where:

Old field name: refers to the field name before modification

New field name: refers to the modified field name

New data type: refers to the modified data type. If you do not need to modify the data type of the field, you can set the new data type to the same as the original, but the data type cannot be empty.

Example

Use ALTER TABLE to modify the structure of the table tb_emp1, change the name of the col1 field to col3, and change the data type to CHAR (30). The SQL statement and run results are shown below.

Mysql > ALTER TABLE tb_emp1-> CHANGE col1 col3 CHAR (30); Query OK, 0 rows affected (0.76 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > DESC tb_emp1 +-+-+ | Field | Type | Null | Key | Default | Extra | + -+ | col3 | char (30) | YES | | NULL | id | int (11) | YES | | NULL | | name | varchar (30) | YES | | NULL | | deptId | int (11) | YES | | NULL | salary | float | YES | | NULL | | +-+-- | -+ 5 rows in set (0.01 sec)

CHANGE can also change only the data type to achieve the same effect as MODIFY by setting the "new field name" and "old field name" in the SQL statement to the same name, and changing only the "data type".

Tip: because different types of data are stored in different ways and lengths in the machine, changing the data type may affect the existing data records in the data table. Therefore, when there is already data in the data table, do not easily modify the data type.

Modify field data type

To change the data type of a field is to convert the data type of the field to another data type. The syntax rules for modifying field data types in MySQL are as follows:

ALTER TABLE MODIFY

Where:

Table name: refers to the name of the table where the field of the data type is to be modified

Field name: refers to the field to be modified

Data type: refers to the new data type of the modified field.

Example

Use ALTER TABLE to modify the structure of the table tb_emp1, changing the data type of the name field from VARCHAR (22) to VARCHAR (30). The SQL statement and run results are shown below.

Mysql > ALTER TABLE tb_emp1-> MODIFY name VARCHAR (30); Query OK, 0 rows affected (0.15 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > DESC tb_emp1 +-+-+ | Field | Type | Null | Key | Default | Extra | + -+ | col1 | int (11) | YES | | NULL | id | int (11) | YES | | NULL | | name | varchar (30) | YES | | NULL | | col2 | int (11) | YES | | NULL | deptId | int (11) | YES | NULL | | salary | float | YES | | | NULL | | +-+-+ 6 rows in set (0.00 sec) |

After the statement is executed, it is found that the data type of the name field in the table tb_emp1 has been changed to VARCHAR (30), and the modification is successful.

Delete a field

Deleting a field removes a field from the data table in the following syntax format:

ALTER TABLE DROP

Where Field name refers to the name of the field that needs to be deleted from the table.

Example

Use ALTER TABLE to modify the structure of the table tb_emp1, delete the col2 fields, SQL statements, and run the results as shown below.

Mysql > ALTER TABLE tb_emp1-> DROP col2;Query OK, 0 rows affected (.53 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > DESC tb_emp1 +-+-+ | Field | Type | Null | Key | Default | Extra | + -+ | col1 | int (11) | YES | | NULL | id | int (11) | YES | | NULL | | name | varchar (30) | YES | | NULL | | deptId | int (11) | YES | | NULL | salary | float | YES | | NULL | | +-+-- | -+ 5 rows in set (0.00 sec) above are all the contents of the case of modifying the mysql database table Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.