MySQL alter command use detailed _mysql

ALTER TABLE allows you to modify the structure of an existing table. For example, you can add or delete columns, create or eliminate indexes, change the type of existing columns, or rename columns or the table itself. You can also change the annotation of the table and the type of the table.
You can rename a column using the change old_col_name create_definition clause. In order to do so, specify the old and new column names and the current types of columns. For example, to rename an integer column, from A to B, you can do this:
mysql> ALTER TABLE T1 change a b INTEGER;
If you want to change the type of the column rather than the name, even if they are the same, the changing syntax still requires 2 column names. For example:
mysql> ALTER TABLE T1 change b b BIGINT not NULL;
However, in mysql3.22.16a, you can also use modify to change the type of the column instead of renaming it:
mysql> ALTER TABLE t1 MODIFY b BIGINT not NULL;
If you shorten a column with change or modify, an index exists in that column (for example, if you have an index of the first 10 characters of a varchar column), you cannot make the column shorter than the number of characters indexed.
ALTER column assigns a new default value to the column or deletes the old default value. If the old default is deleted and the column can be null, the new default value is null. If the column cannot be null,mysql give a default value. The default value assignment is described in the 7.7 CREATE table syntax.
Drop index deletes an index. This is an extension of MySQL to ANSI SQL92.
If the columns are discarded from a table, the columns are also removed from any index in which they are part. If all the columns that make up an index are discarded, the index is also discarded.
The drop PRIMARY key discards the primary index. If such an index does not exist, it discards the first unique index in the table. (if the primary key,mysql tag is not explicitly specified, the first unique key is primary key.) ）
With the C API function Mysql_info (), you can find out how many records are copied and (when using ignore) how many records are deleted because of unique key values.
Here is an example that shows some ALTER table usages. We take one of the following

/* CREATE TABLE T1 start:
mysql> CREATE table T1 (a integer,b CHAR);
Rename the table from T1 to T2:
mysql> ALTER table T1 RENAME T2;
In order to alter column A, change from integer to tinyint not NULL (the first name) and change column B

from char (10) to char (20), rename it, and change from B to c:
mysql> ALTER TABLE T2 MODIFY a TINYINT not NULL and change B C CHAR (m);
Add a new TIMESTAMP column named D:
mysql> ALTER TABLE T2 ADD D TIMESTAMP;
Add an index to column D and make column a the primary key:
mysql> ALTER TABLE T2 Add index (d), add PRIMARY key (a);
Delete column C:
mysql> ALTER TABLE T2 DROP column C;
Add a new auto_increment integer column named C:
mysql> ALTER TABLE T2 add c INT UNSIGNED not NULL auto_increment,
      add INDEX (c) ;*/

Note that here the C is indexed, because the auto_increment column must be indexed and in addition we declare C to NOT NULL because the indexed column cannot be null. When you add a auto_increment column, automatically fill in the row value with the sequential number.

Concrete Example Analysis:

We need to use the MySQL alter command when we need to modify the datasheet name or modify the Data table field.

Before starting this chapter tutorial, let's first create 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)

Delete, add, or modify a table field
The following command uses the ALTER command and the DROP clause to delete the I field of the table created above:

mysql> ALTER TABLE testalter_tbl DROP i;

You cannot use drop to delete a field if only one field is left in the datasheet.

In MySQL, use the ADD clause to add columns to the datasheet, the following instance adds the I field in the table Testalter_tbl, and defines the data type:

mysql> ALTER TABLE testalter_tbl ADD i INT;
After you execute the above command, the I field is automatically added to the end of the data table field.

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)

If you need to specify the location of the new field, you can use the keyword provided by MySQL (set to the first column), after the field name (set in a field).

Try the following ALTER table statement to view changes in the table structure using show COLUMNS after successful execution:

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 after keywords are used only for the ADD clause, so if you want to reset the position of the data table field, you need to use drop to remove the fields and then use Add to add the fields and set the location.

Modify field type and name
If you need to modify the field type and name, you can use the MODIFY or change clause in the ALTER command.

For example, to change the type of field C from char (1) to char (10), you can execute the following command:

mysql> ALTER TABLE testalter_tbl MODIFY C CHAR (10);
with the change clause, the syntax is a lot different. After the Change keyword, follow the name of the field you want to modify, and then specify the type and name of the new field. Try the following example:

mysql> ALTER TABLE testalter_tbl change i j BIGINT;
mysql> ALTER TABLE Testalter_tbl Change J J INT;

Effect of ALTER TABLE on Null values and default values
When you modify a field, you can specify whether to include only or not to set a default value.

The following instance specifies that the field J is not NULL and the default value is 100.

mysql> ALTER TABLE testalter_tbl
  -> MODIFY J BIGINT not NULL DEFAULT 100;

If you do not set the default value, MySQL will automatically set the word defaults to assume NULL.

Modify the default value of a field
You can use ALTER to modify the default value of the field, and try the following examples:

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 also use the ALTER command and the drop clause to remove the default value for a field, as follows:

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)
Changing a Table Type:

Modifying a datasheet type can be done using the ALTER command and the type clause. Try the following instance, we modify the type of table Testalter_tbl to MYISAM:

Note: You can use the Show Table STATUS statement to view the datasheet type.

 mysql> ALTER TABLE testalter_tbl TYPE = MYISAM;
Mysql> show TABLE STATUS like ' testalter_tbl ' \g
*************************** 1. Row ****************
      Name: Testalter_tbl
      type:myisam
   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) 

Modify Table Name
If you need to modify the name of the datasheet, you can use the RENAME clause in the ALTER table statement to implement it.

Try the following instance to rename the datasheet testalter_tbl to ALTER_TBL:

mysql> ALTER TABLE testalter_tbl RENAME to Alter_tbl;

The above is the entire content of this article, I hope to help you learn.