ALTER主要用于修改表结构：

    1. 1.修改表名
    2. alter table `旧表名` rename `新表名`;
    4. 2.修改表中的字段名(数据类型必须填)
    5. ALTER TABLE `表名` change `旧字段名` `新字段名` 数据类型;
    7. 3.修改表中的字段的数据类型
    8. ALTER TABLE `表名` MODIFY `字段名` 新数据类型;
    10. 4.修改表中的字段名+数据类型：
    11. ALTER TABLE `表名` change `旧字段名` `新字段名` 新数据类型;
    13. 5.在表中新增一个字段(如果不加first,排列顺序默认为表的最后一列字段)
    14. ALTER TABLE `表名` ADD `字段名` 数据类型
    15. [not null unique primary key default '默认值' COMMENT '注释'] -- 非必填项
    16. [first] -- 将新增字段的位置设置成表的第一列
    17. [after `字段A`] -- 将新增字段的位置设置为字段A的后面;
    19. 6.修改表中字段的排列顺序
    20. ALTER TABLE `表名` MODIFY `字段名` 数据类型 
    21. [FIRST] -- 将字段的位置修改成表的第一列
    22. [AFTER `字段A`] -- 将字段的位置修改为字段A的后面;
    24. 7.删除表中的一个字段
    25. ALTER TABLE `表名` DROP `字段名`;

    -7ab41fd6c244a070.jpg

    1. -- 家庭户籍表: `family`
    2. ALTER TABLE `family` add `FK_FPID` int(15) null COMMENT '外键: 标识该家庭属于哪种贫困类型';
    3. ALTER TABLE `family` add constraint FOREIGN KEY ( `FK_FPID` ) references `family_pool` ( `FP_ID` );
    4. ALTER TABLE `family` add `FK_VGID` int(15) null COMMENT '外键: 标识该家庭属于哪个村组';
    5. ALTER TABLE `family` add CONSTRAINT FOREIGN KEY ( `FK_VGID` ) REFERENCES `village_group` ( `VG_ID` );
    8. -- 村民表: `villager`
    9. ALTER TABLE `villager` add `FK_RID` int(15) null COMMENT '外键: 标识村民的角色';
    10. ALTER TABLE `villager` add CONSTRAINT FOREIGN KEY ( `FK_RID` ) REFERENCES `villager_role` ( `R_ID` );
    11. ALTER TABLE `villager` add `FK_DLID` int(15) null COMMENT '外键: 如果村民残疾，标识其残疾级别';
    12. ALTER TABLE `villager` add CONSTRAINT FOREIGN KEY ( `FK_DLID` ) REFERENCES `disability_level` ( `DL_ID` );
    13. ALTER TABLE `villager` add `FK_VID` int(15) null COMMENT '外键: 户主(村民)ID ，标识村民所属哪个家庭';
    14. ALTER TABLE `villager` add CONSTRAINT FOREIGN KEY ( `FK_VID` ) REFERENCES `family` ( `V_ID` );
    15. ALTER TABLE `villager` add `FK_FRID` int(15) null COMMENT '外键: 标识村民与户主的血缘关系';
    16. ALTER TABLE `villager` add CONSTRAINT FOREIGN KEY ( `FK_FRID` ) REFERENCES `family_relation` ( `FR_ID` );
    19. -- 村组信息表`village_group`
    20. ALTER TABLE `village_group` add `FK_VID` int(15) null COMMENT '外键: 标识该村组的村组干部是哪个村民';
    21. ALTER TABLE `village_group` add    CONSTRAINT FOREIGN KEY ( `FK_VID` ) REFERENCES `villager` ( `V_ID` );
    24. -- 村民工作信息表`job_info`
    25. ALTER TABLE `job_info` add `FK_VID` int(15) null COMMENT '外键: 标识这行工作信息元组属于哪个村民';
    26. ALTER TABLE `job_info` add CONSTRAINT FOREIGN KEY ( `FK_VID` ) REFERENCES `villager` ( `V_ID` );
    29. -- 村民患病信息表`illness_info`
    30. ALTER TABLE `illness_info` add `FK_VID` int(15) null COMMENT '外键: 标识这行患病信息元组属于哪个村民';
    31. ALTER TABLE `illness_info` add CONSTRAINT FOREIGN KEY ( `FK_VID` ) REFERENCES `villager` ( `V_ID` );
    34. -- 残疾补贴表`disability_payments`
    35. ALTER TABLE `disability_payments` add `FK_VID` int(15) null COMMENT '外键: 标识这行残疾补贴领取记录属于哪个村民';
    36. ALTER TABLE `disability_payments` add CONSTRAINT FOREIGN KEY ( `FK_VID` ) REFERENCES `villager` ( `V_ID` );
    39. -- 养老补贴表`pension_subsidy`
    40. ALTER TABLE `pension_subsidy` add `FK_VID` int(15) null COMMENT '外键: 标识这行养老补贴领取记录属于哪个村民';
    41. ALTER TABLE `pension_subsidy` add CONSTRAINT FOREIGN KEY ( `FK_VID` ) REFERENCES `villager` ( `V_ID` );
    44. -- 家庭年收支表`income_expenditure`
    45. ALTER TABLE `income_expenditure` add `FK_VID` int(15) null COMMENT '外键: 标识这行家庭年收支调查记录属于哪一户家庭';
    46. ALTER TABLE `income_expenditure` add CONSTRAINT FOREIGN KEY ( `FK_VID` ) REFERENCES `family` ( `V_ID` );
    49. -- 村民保障表`villager_ensure`
    50. -- 1对1: 主键即外键
    51. ALTER TABLE `villager_ensure` add CONSTRAINT FOREIGN KEY ( `V_ID` ) REFERENCES `villager` ( `V_ID` );
    54. -- 家庭保障表 `family_ensure`
    55. -- 1对1: 主键即外键
    56. ALTER TABLE `family_ensure` add CONSTRAINT FOREIGN KEY ( `V_ID` ) REFERENCES `family` ( `V_ID` );
    59. -- 土地所属详情表`family_land`
    60. -- 多对多: 主键即外键
    61. ALTER TABLE `family_land` add CONSTRAINT PRIMARY KEY ( `V_ID`,`L_ID` );
    62. ALTER TABLE `family_land` add CONSTRAINT FOREIGN KEY ( `V_ID` ) REFERENCES `family` ( `V_ID` );
    63. ALTER TABLE `family_land` add CONSTRAINT FOREIGN KEY ( `L_ID` ) REFERENCES `land` ( `L_ID` );
    66. -- 种植类型详情表`family_plant`
    67. -- 多对多: 主键即外键
    68. ALTER TABLE `family_plant` add CONSTRAINT PRIMARY KEY ( `V_ID`,`P_ID` );
    69. ALTER TABLE `family_plant` add CONSTRAINT FOREIGN KEY ( `V_ID` ) REFERENCES `family` ( `V_ID` );
    70. ALTER TABLE `family_plant` add CONSTRAINT FOREIGN KEY ( `P_ID` ) REFERENCES `plant` ( `P_ID` );
    73. -- 养殖类型详情表`family_breed`
    74. -- 多对多: 主键即外键
    75. ALTER TABLE `family_breed` add CONSTRAINT PRIMARY KEY ( `V_ID`,`B_ID` );
    76. ALTER TABLE `family_breed` add CONSTRAINT FOREIGN KEY ( `V_ID` ) REFERENCES `family` ( `V_ID` );
    77. ALTER TABLE `family_breed` add CONSTRAINT FOREIGN KEY ( `B_ID` ) REFERENCES `breed` ( `B_ID` );