The difference between change and modify in Mysql:alter statements

You can rename a column by using the change old_col_namecolumn_definition clause. When renaming, you need to give the old and new column names and columns the current type. For example, to change the name of an integer column from A to B, you need to do the following:

· mysql> ALTER TABLE T1 change a b INTEGER;

If you want to change the type of the column instead of the name, the changes syntax still requires the old and new column names, even if the old and new column names are the same. For example:

mysql> ALTER TABLE T1 change b b BIGINT not NULL;

You can also use modify to change the type of the column without renaming:

mysql> ALTER TABLE t1 MODIFY b BIGINT not NULL;

MySQL ALTER statement usage, ADD, modify, delete fields, etc.

Primary key

ALTER TABLE tabelname add new_field_id int (5) unsigned default 0 NOT NULL auto_increment, add primary key (NEW_FIELD_ID);
Add a new column

ALTER TABLE T2 add d timestamp;
ALTER TABLE infos add ex tinyint NOT null default ' 0 ';
Delete Column

ALTER TABLE T2 drop column C;
renaming columns

ALTER TABLE T1 change a b integer;

Changing the type of a column

ALTER TABLE T1 change b b bigint not null;
ALTER TABLE infos change list list tinyint not null default ' 0 ';

Renaming a table

ALTER TABLE t1 rename T2;
Add index

Mysql> ALTER TABLE tablename change depno depno int (5) is not null;
Mysql> ALTER TABLE tablename ADD index index name (field name 1[, field Name 2 ...]);
Mysql> ALTER TABLE tablename Add index Emp_name (name);
Index of the plus primary keyword

Mysql> ALTER TABLE TableName ADD PRIMARY key (ID);
Index with unique restriction criteria

Mysql> ALTER TABLE tablename add unique emp_name2 (cardnumber);
Delete an index

Mysql>alter table tablename DROP index emp_name;
To modify a table:

Add Field:

mysql> ALTER TABLE table_name ADD field_name Field_type;
Modify the original field name and type:

mysql> ALTER TABLE table_name change old_field_name new_field_name field_type;
To delete a field:

mysql> ALTER TABLE table_name DROP field_name;

Delete PRIMARY key: ALTER TABLE Employees drop PRIMARY key;

The difference between change and modify in Mysql:alter statements