Modification of MySQL table operation

First, modify the table

语法：1. 修改表名      ALTER TABLE 表名                           RENAME 新表名;2. 增加字段      ALTER TABLE 表名                          ADD 字段名  数据类型 [完整性约束条件…],                          ADD 字段名  数据类型 [完整性约束条件…];      ALTER TABLE 表名                          ADD 字段名  数据类型 [完整性约束条件…]  FIRST;      ALTER TABLE 表名                          ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;                            3. 删除字段      ALTER TABLE 表名                           DROP 字段名;4. 修改字段      ALTER TABLE 表名                           MODIFY  字段名 数据类型 [完整性约束条件…];      ALTER TABLE 表名                           CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];      ALTER TABLE 表名                           CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

Instance Code

Example:1. Modifying the storage engine MySQL>ALTER TABLE T1 -Engine=InnoDB;2. Add Field MySQL>ALTER TABLE T1 -Add Name varchar ( -) notNull -Add ageint(3) notNull default A;Mysql>ALTER TABLE T1 -Add Stu_num varchar (Ten) notNull after name;                //After adding the name field MySQL>ALTER TABLE T1 -Add Sex enum (' Male ',' Female ') Default' Male 'First;          //Add to the front3. Delete field MySQL>ALTER TABLE T1 -Drop Sex;Mysql>ALTER TABLE T1 -Drop Mac;4. Modify field type Modifymysql>ALTER TABLE T1 -Modify Ageint(3);Mysql>ALTER TABLE T1 -ModifyID int( One) notNULL PRIMARY KEY Auto_increment;    //Modify the primary key5. Add constraint (Increase auto_increment for existing primary key) MySQL>ALTER TABLE T1 ModifyID int( One) notNULL PRIMARY KEY Auto_increment;ERROR1068(42000): Multiple PRIMARY key Definedmysql>ALTER TABLE T1 ModifyID int( One) notNull auto_increment;Query OK,0Rows Affected (0.01SEC) Records:0Duplicates:0Warnings:06. Add a composite primary key to a table that already exists MySQL>ALTER TABLE SERVICE2 -Add primary key (Host_ip,port);        7. Add primary key MySQL>ALTER TABLE Student1 -Modify Name varchar (Ten) notNULL PRIMARY key;8. Increase primary key and auto grow MySQL>ALTER TABLE Student1 -ModifyID int  notNULL PRIMARY KEY Auto_increment;9. Delete primary key A. Remove self-restraint MySQL>ALTER TABLE STUDENT10 ModifyID int( One) notNull;B. Deleting a primary key MySQL>ALTER TABLE STUDENT10 -Drop PRIMARY Key;Example Header1 |Header2---|---Row1Col1 |Row1Col2Row2Col1 |Row2Col2

Second, copy the table

复制表结构＋记录 （key不会复制: 主键、外键和索引）mysql> create table new_service select * from service;只复制表结构mysql> select * from service where 1=2;        //条件为假，查不到任何记录Empty set (0.00 sec)mysql> create table new1_service select * from service where 1=2;  Query OK, 0 rows affected (0.00 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> create table t4 like employees;

Modification of MySQL table operation