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.
Use Learning (to be built in advance) CREATE TABLE INT not NULL , CHAR (tennotnull, INTis notnull, INT );
Take a look at the new table.
One, delete, add or modify table field
The following command uses the ALTER command and the drop clause to delete the age field of the created table above:
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.
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:
ALTER TABLE student ADDINTnotNULL;
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:
ALTER TABLE student ADDCHAR(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.
ALTER TABLE student DROP sex; ALTER TABLE student ADDCHAR(2) after age;
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:
ALTER TABLE student CHAR(+);
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:
ALTER TABLE student Change ID BIGINTPRIMARYKEY;
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.
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:
ALTER TABLE ALTER DROP DEFAULT ; 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.
ALTER TABLE = MYISAM 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
ALTER TABLE student DROPPRIMARYKEY;
ALTER TABLE student ADDCONSTRAINT pk_student PRIMARYKEY (Id,class);
Add index
ALTER TABLE ADD INDEX index_name (name);
View Index
INDEX from student;
To add a unique restricted criteria index
ALTER TABLE student ADDUNIQUE 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)