How to use the MySQL alter command

MySQL's alter command is very useful when you want to change the name of the table, the field of the table, or if you want to add or delete columns from an existing table.

Let's start by creating a use case with a table named Testalter_tbl:

The code is as follows

Copy Code

root@host# mysql-u root-p password; Enter password:******* mysql> use tutorials; Database changed Mysql> CREATE TABLE Testalter_tbl -> ( -> I INT, -> c CHAR (1) ->); Query OK, 0 rows affected (0.05 sec) Mysql> show COLUMNS from Testalter_tbl; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | I | Int (11) |     YES | |       NULL | | | C | char (1) |     YES | |       NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in Set (0.00 sec)

Discard, add or reposition fields:
Suppose you want to delete an existing column I from the MySQL table above, use the drop clause to use the ALTER command as follows

The code is as follows	Copy Code
mysql> ALTER TABLE testalter_tbl DROP i;

If there is only one field left in the table, the drop command does not work.

To add a column, use the column definition specified by Add. The following statement restores the testalter_tbl of column I

The code is as follows	Copy Code
mysql> ALTER TABLE testalter_tbl ADD i INT;

The testalter will contain the same two columns, and the first time you create the table, it will not have the same structure. This is because the new column is added to the default and the table ends. Even though Ioriginally is the first column of Mytbl, now is the last one:

code is as follows	copy code
mysql> show COLUMNS from testalter_tbl; +-------+---------+------+---- -+---------+-------+ | Field | type    | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | c     | char (1) | yes  |     | null    |       | | i     | int (11) | yes  |     | null    |       | +-------+---------+------+-----+---------+-------+ 2 rows in Set (0.00 sec)

To specify a column position in the table, you can use the first column of it, or alter col_name to indicate that the new column should be placed in the back col_name. Try the following ALTER TABLE statement, where everyone has a different effect after you use show columns:

The code is as follows	Copy Code
ALTER TABLE testalter_tbl DROP i; ALTER TABLE testalter_tbl ADD i INT i; ALTER TABLE testalter_tbl DROP i; ALTER TABLE testalter_tbl ADD i INT after C;

The first and second characters can only be with the ADD clause. This means that if you want to reposition an existing column in a table, you must first delete it and then add it to the new location.

To change a column definition or name:
To change the definition of the column, modify or change the terms, together with the Alter command. For example, to change column C from char (1) to char (10), do this:

The code is as follows	Copy Code
mysql> ALTER TABLE testalter_tbl MODIFY C CHAR (10);

The change syntax is a little different. The changed keyword, the name of the column you want to change, and then specify a new definition, including the new name. Try the following example:

The code is as follows	Copy Code
mysql> ALTER TABLE testalter_tbl change i j BIGINT;

If you now use the bigint of the Convert J field as int without changing the column name, the declaration should be:

The code is as follows	Copy Code
mysql> ALTER TABLE Testalter_tbl Change J J INT;

Effects of ALTER table, NULL, and default value properties:

When you modify or change a column, you can also specify whether the column can contain a null value and what its default value is. In fact, if you don't, MySQL will automatically assign values to these properties.

The following is an example by default, the value of the NOT NULL column will be 100.

The code is as follows	Copy Code
mysql> ALTER TABLE Testalter_tbl -> MODIFY J BIGINT not NULL DEFAULT 100;

If you do not use the above command, MySQL fills the null values in all columns.

To change the default value for a column:

You can use the ALTER command to change the default values for any column. Try the following example.

code is as follows

copy code

mysql> alter TABLE TESTALTER_TBL alter I SET DEFAULT 1000; mysql> Show COLUMNS from Testalter_tbl; +-------+---------+------+-----+---------+-------+ | Field | type    | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | c     | char (1) | yes  |     | null    |       | | i     | int (11) | yes  |     | 1000    |       | +-------+---------+------+-----+---------+-------+ 2 rows in Set (0.00 sec)

The

can remove a default constraint from any column by using the ALTER command together by using the drop clause.

The code is as follows

Copy Code

Mysql> alter TABLE TESTALTER_TBL alter I DROP DEFAULT; Mysql> show COLUMNS from Testalter_tbl; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | C | char (1) |     YES | |       NULL | | | I | Int (11) |     YES | |       NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in Set (0.00 sec)

To change the table type:

You can use the Modify table type with the ALTER command and the TYPE clause. Try the following example to change the testalter_tbl to the InnoDB type.

To find the current type of a table, use the Show Table status statement.

The code is as follows

Copy Code

mysql> ALTER TABLE testalter_tbl TYPE = InnoDB; Mysql> show TABLE STATUS like ' testalter_tbl ' G 1. Row **************** Name:testalter_tbl Type:innodb Row_format:fixed rows:0 avg_row_length:0 data_length:0 max_data_length:25769803775 index_length:1024 data_free:0 Auto_increment:null create_time:2007-06-03 08:04:36 update_time:2007-06-03 08:04:36 Check_time:null Create_options: Comment: 1 row in Set (0.00 sec)

To rename a table:

To rename a table, use the Rename option in the ALTER TABLE statement. Try the following example, rename Testalter_tbl to Alter_tbl

The code is as follows	Copy Code
mysql> ALTER TABLE testalter_tbl RENAME to Alter_tbl;

You can use the ALTER command to create and delete indexes in MySQL files

Modify a field type

The code is as follows	Copy Code
Mysql> ALTER TABLE employee change depno depno Int (5) is not null;

Add index
Mysql> ALTER TABLE name add index index name (field name 1[, field Name 2 ...]);
Example:

The code is as follows	Copy Code
Mysql> ALTER TABLE employee ADD index emp_name (name);

Index of the Primary keyword
Mysql> ALTER TABLE name add primary key (field name);
Example:

The code is as follows	Copy Code
Mysql> ALTER TABLE employee ADD primary key (ID);

Index with unique restriction criteria
Mysql> ALTER TABLE name add
Unique index name (field name);
Example:

The code is as follows	Copy Code
Mysql> ALTER TABLE employee add unique emp_name2 (cardnumber);

View the index of a table
Mysql> Show index from table name;
Example:

The code is as follows	Copy Code
Mysql> Show index from employee;

Delete an index
mysql> ALTER TABLE name DROP INDEX index name;
Example:

The code is as follows	Copy Code
Mysql>alter table employee DROP index emp_name;