How to use the MySQL alter command

Source: Internet
Author: User

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;

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.