A detailed explanation of the use of the alter command for MySQL

Lin Bingwen Evankaka Original works. Reprint please specify the source Http://blog.csdn.net/evankaka

This article explains in detail the use of the ALTER command in MySQL syntax, which is a much more useful syntax, and the functionality is very powerful.

[SQL]View PlainCopy

1. Use learning; (to be built in advance)
2. CREATE TABLE student (id INT not NULL,
3. Name CHAR (Ten) is not NULL,
4. Class INT not NULL,
5. Age INT
6. );

Take a look at the new table.

One, delete, add or modify table field

• Delete a table field

The following command uses the ALTER command and the drop clause to delete the age field of the created table above:

[SQL]View PlainCopy

1. ALTER TABLE student DROP age;

Let's look at the results:

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

• Add a table field

Use the Add clause in MySQL to add columns to the data table, add an age field to the table student, and define the data type as follows:

[SQL]View PlainCopy

1. ALTER TABLE student ADD age INT is not NULL;

After executing the above command, the I field is automatically added to the end of the data table fields.

SHOW COLUMNS from Student view table structure

If you need to specify the location of the new field, you can use the keyword first provided by MySQL (set to position one column), after field name (After a field is set).
Try the following ALTER table statement, and after successful execution, use show COLUMNS to view the table structure changes:

[SQL]View PlainCopy

1. ALTER TABLE student ADD sex CHAR (2) first ;

The first and after keywords are used only for the ADD clause, so if you want to reset the location of the data table fields, you need to use drop to delete the field and then use Add to add the field and set the position.

[SQL]View PlainCopy

1. ALTER TABLE student DROP sex;
2. ALTER TABLE student ADD sex CHAR (2) after age ;

• Modify a table field

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 the field name from Char (10) to char (100), you can execute the following command:

[SQL]View PlainCopy

1. ALTER TABLE student MODIFY age CHAR (100);

The syntax differs greatly by using the change clause. 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:

[SQL]View PlainCopy

1. ALTER TABLE student Change ID stu_id BIGINT PRIMARY KEY;

Effects of ALTER TABLE on Null values and default values
When you modify a field, you can specify whether to include only or whether to set the default value.
The following instance specifies that the field sex is not NULL and that the default value is male.

[SQL]View PlainCopy

1. ALTER TABLE sutdent MODIFY sex CHAR (2) not NULL DEFAULT ' male ';

If you do not set the default value, MySQL automatically sets the word defaults to be considered NULL.
You can also use the ALTER command and the drop clause to delete the default value of the field, as in the following example:

[SQL]View PlainCopy

1. Alter TABLE student alter sex DROP DEFAULT;
2. SHOW COLUMNS from student;

To modify a data table type, you can do so by using the ALTER command and the type clause. Try the following example, and we will modify the type of table student to MYISAM:
Note: You can use the Show CREATE table statement to view the data table type.

[SQL]View PlainCopy

1. ALTER TABLE Student ENGINE = MYISAM
2. SHOW CREATE TABLE student;

Second, modify the table name

If you need to modify the name of the data table, you can do so by using the RENAME clause in the ALTER table statement.
Try the following instance to rename the data table student to student_1:

mysql> ALTER TABLE student RENAME to student_1;

Third, primary key and index modification

• Delete primary key in table

[SQL]View PlainCopy

1. ALTER TABLE student DROP PRIMARY KEY;

• Add primary Key

[SQL]View PlainCopy

1. ALTER TABLE student ADD CONSTRAINT pk_student PRIMARY KEY (id,class);

Add index

[SQL]View PlainCopy

1. ALTER TABLE student ADD INDEX index_name (name);

View Index

[SQL]View PlainCopy

1. SHOW INDEX from student;

To add a unique restricted criteria index

[SQL]View PlainCopy

1. ALTER TABLE student ADD UNIQUE emp_name (age);

Delete Index

ALTER TABLE student DROP INDEX index_name;

http://blog.csdn.net/evankaka/article/details/46664337

MySQL alter command usage detailed interpretation (RPM)