MySQL modify data table (MYSQL data table operation tutorial 2)

Modify the table structure

Use the ALTER TABLE statement to modify the table structure. Modifying the table structure means adding or deleting fields, modifying field names or field types, setting or canceling primary keys and foreign keys, setting or canceling indexes, and modifying table comments, etc.

The syntax format is as follows:

ALTER[IGNORE] TABLE data table name alter_spec[,alter_spec]...;

Note: When specifying IGNORE When, if there are duplicate key rows, only one row will be executed, and other duplicate rows will be deleted.

Among them, the alter_spec clause defines the content to be modified. The syntax is as follows:

alter_specification:

ADD[COLUMN]create_definition[FIRST|AFTER column_name]                            //添加新字段
ADD INDEX [index_name](index_col_name,...)                                       //添加索引名称
ADD PRIMARY KEY(index_col_name,...)                                              //添加主键名称
ADD UNIQUE[index_name](index_col_name,...)                                       //添加唯一索引
ALTER[COLUMN]col_name{SET DEFAULT ilteral |DROP DEFAULT}                         //修改字段名称
CHANGE[COLUMN]old_col_name create_definition                                     //修改字段类型
MODIFY[COLUMN]create_definition                                                  //修改子句定义字段
DROP[COLUMN]col_name                                                             //删除字段名称
DROP PRIMARY KEY                                                                 //删除主键名称
DROP INDEX index_name                                                            //删除索引名称
RENAME [AS]new_tbl_name                                                          //更改表名
table_options

ALTER TABLE statement allows specifying multiple alter_spec clauses, each clause Use commas to separate the clauses, and each clause represents a modification to the table.

For example: Suppose there is a table called admin, now you want to add a new field email, the type is varcher(50), not null, change the type of field user from varcher(50) to varcher(40), The code is as follows:

alter table admin add email varcher(50) not null,modify user varcher(40);

After the addition is completed, you can also view the entire table through show admin; structure to confirm whether the field was added successfully.

Note: The prerequisite for modifying table columns through alter is that all data in the table must be deleted before modification can be made.

The above is the detailed content of MySQL modify data table (MYSQL data table operation tutorial 2). For more information, please follow other related articles on the PHP Chinese website!