MySQL alter usage Summary

About ALTER TABLE

When a table is created, there may be some new requirements during use. In this case, you may need to modify the table structure. If the TABLE has been filled with data, re-creating the TABLE will cause loss of existing data. Therefore, you can use alter table to modify the TABLE structure.

Add new columns to a table

Prerequisites

To add a column to a table, make sure that the column to be added can use a NULL value or that the DEFAULT value is specified for the column.

Command Format

Alter table $ tablename ADD $ column_name $ object_type [NULL | not null default "DEFAULT"];

Parameter description:

Tablename: name of the table to be added
Column_name: name of the added Column
Object_type: Data Type of the added Column
[]: Default value for adding a column

Description

When a new column is added to a table, Database Engine inserts a value for each existing data row in the column. Therefore, it is useful to specify the DEFAULT definition when adding columns to a table. If the new column does not have the DEFAULT definition, you must specify that the column allows NULL values. The database engine inserts NULL values into the column. If the NULL value is not allowed, an error is returned.

Example operation

Delete columns in a table

Prerequisites

Before deleting a column, you must delete any constraints, default expressions, calculated column expressions, or indexes that reference the column.

Command

Alter table $ tablename drop column $ column_name;

Example operation

1 2 3 Next Page