Detailed usage of ALTER command in Mysql _mysql

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:

root@host# mysql-u root-p password;
Enter password:*******
mysql> use tutorials;
Database changed
mysql> create TABLE Testalter_tbl
  -> (
  -> i INT,
  -> C CHAR (1)
  ->); C9/>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

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 the column

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:

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:

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:

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:

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:

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.

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.

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)

You can remove a default constraint from any column by using the ALTER command with the DROP clause.

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.

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 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
    :
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

mysql> ALTER TABLE testalter_tbl RENAME to Alter_tbl;

You can use the ALTER command to create and delete indexes in MySQL files. In the next chapter, we'll see this feature.