祝大家龙年大吉新春快乐！二级MySQL基础知识（三）Alter Table修改表字段和更新约束，mysqldump备份source恢复数据库

提示：二级MySQL常用数据库db_school，常用表tb_student，tb_class，tb_course，tb_score

---

回顾上篇

创建表的SQL语句较长，为了代码的可读性，SQL语句均采用换行书写方式，每定义一个字段或定义一个表级约束的SQL语句单独写一行，以逗号结束(非最后一句)。
但是添加外键约束的SQL语句特别长一般分2行写，换行的地方不能有逗号，还必须写好每一个英文关键字和空格。

一、Alter Table 修改表字段

1. 添加、修改、删除表字段

（1）添加字段
alter table 表名 add [column] 字段名 数据类型 [约束条件] [first 或 after 已有字段];
[方括号里可省略]
例如：给学生表增加一个院系字段department，并设置默认值约束为信息学院。
alter table tb_student add department varchar(16) default’信息学院’;

可以同时增加add（或同时删除drop）多个字段
alter table 表名 add
(字段名1 数据类型 约束条件,
字段名2 数据类型 约束条件,
字段名3 数据类型 约束条件
);

（2）修改字段
alter table 表名change [column] 原字段名 新字段名 数据类型 [约束条件];

（3）修改字段的数据类型、约束条件、位置
alter table 表名modify [column] 字段名 数据类型 [约束条件] [first 或 after 已有字段];

（4）删除字段（删除列）
alter table 表名 drop [column] 字段名;

2. 修改表名

（1）修改表名
alter table 原表名 rename to 新表名;

（2）用 rename table命令修改表名
rename table 原表名 to 新表名;

二、Alter Table 修改约束

常见有7种约束：
default 默认值约束（缺省值）
unique 唯一键约束（候选键约束）
primary key 主键约束
foreign key 外键约束
not null 非空约束
auto_increment 自增属性约束
check 取值范围约束（字段名=取值范围条件表达式）

1. 添加、删除字段的默认值约束( 缺省值)

（1）添加默认值
alter table 表名alter [column] 字段名 set default ‘默认值’;

（2）删除默认值
alter table 表名alter [column] 字段名 drop default ;

2. 添加、删除唯一键约束(候选键约束)

（1）添加唯一键约束
alter table 表名 add constraint 唯一键约束名
unique key(字段名)

（2）删除唯一键约束(删除索引名 drop index_name）
没有设置约束名，系统就用字段名作为唯一键约束名：
alter table 表名 drop index 字段名;

使用 constraint 设置唯一键约束名，删除的索引名是唯一键约束名：
alter table 表名 drop index 唯一键约束名;

3. 添加、修改、删除主键约束和外键约束

（1）添加主键约束
alter table 表名 add primary key(字段名);

（2）添加外键键约束
alter table 表名(参照表) add constraint 外键约束名
foreign key(字段名) references 表名(被参照表) (主键字段名);

（3）删除主键约束
alter table 表名(被参照表) drop primary key;

（4）删除外键约束( 删除外键约束后才能删除索引 )
alter table 表名(参照表) drop foreign key 外键名;

4. 添加删除主键约束和外键约束的规则

（1）添加外键约束的规则： 添加外键约束的表称为参照表，需要reference参照另一个表被参照表的主键，因此被参照表必须已经创建并设置好主键，参照表才能设置外键约束。

添加外键约束的表tb_student（参照表）需要reference参照另一个表tb_class（被参照表）的主键，因此被参照表tb_class必须已经创建并设置主键，参照表tb_student才能设置外键约束。

添加主键约束的SQL语句（被参照变先设置主键）：
alter table tb_class add primary key(classNo);

添加外键约束的SQL语句(参照表才能添加外键)：
alter table tb_student add constraint fk_student
foreign key(classNo) references tb_class(classNo);

如果被参照表没有设置主键，参照表直接创建外键约束不成功，会提示以下错误：

ERROR 1005 (HY000): Can’t create table ‘db_school.#sql-1ef0_17’ (errno: 150)

（2）删除主键约束的规则：删除主键约束的表称为被参照表，需要先删除参照表的外键约束后，然后才能删除被参照表的主键。

tb_class（被参照表）删除主键约束提示错误无法删除时，一般是被参照表tb_class被另一个tb_student（参照表）参照了。

因此先删除参照表tb_student中的外键约束。
删除外键约束SQL语句：
alter table tb_student drop foreign key fk_student;

然后才能删除被参照表的主键。
删除主键约束SQL语句：
alter table tb_class drop primary key;

如果参照表没有删除外键约束，被参照表无法直接删除主键，会提示以下错误：

ERROR 1025 (HY000): Error on rename of ‘.\db_school#sql-1ef0_17’ to ‘.\db_school\tb_class’ (errno: 150)

5. 修改非空约束

(1) 修改字段为非空约束
alter table 表名 modify 字段名 数据类型 not null;

字段已经是唯一键，修改为非空约束，唯一和非空这2个约束合体，该字段升级为PRI。
字段已经是非空，添加唯一键，非空和唯一合体，该字段升级为PRI。
上面2种情况，用desc查看表结构时字段的Key显示PRI，
但是用show create table 表名 查看创建表详细结构没有看到主键，用show index from 表名 也没有看到主键索引。
所以这里PRI并不代表主键，不要被PRI迷惑，定义主键还是用primary key。

(2) 删除非空约束
alter table 表名 modify 字段名 数据类型;

6. 修改自增属性约束

（1）添加自增属性约束
字段的数据类型必须使用整数，
字段必须先添加主键或唯一键，才能添加自增属性约束
也就是字段先添加为主键或唯一键，才能添加自增属性。一个表只能有一个自增属性的字段。
alter table 表名 add primary key(字段名) ;
alter table 表名 modify 字段名 int auto_increment;

或者
alter table 表名 modify 字段名 int unique auto_increment;

(2) 删除自增属性
alter table 表名 modify 字段名 int;
和添加时相反，必须先删除字段的自增属性约束，才能删除主键或唯一键
直接删除主键或唯一键会报以下错误：

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

Alter Table 应用汇总

应用操作	使用命令
添加字段	alter table 表名 add 字段名 数据类型 约束条件 [first 或 after 已有字段]
添加普通索引	alter table 表名 add index 索引名(字段名);
添加唯一索引	alter table 表名 add constraint uq_唯一索引名 unique key(字段名)；
添加未命名唯一索引 或 主键	alter table 表名 modify 字段名 数据类型 unique 或 primary key;
添加主键约束	alter table 表名 add primary key(字段名)
添加外键约束	alter table 表名 add constraint fk_外键约束名 foreign key(字段名) references 被参照表名(主键字段名)
删除字段	alter table 表名 drop 字段名
删除索引( 删除唯一索引 )	alter table 表名 drop index 索引名
删除主键	alter table 表名 drop primary key
删除主键	alter table 表名 drop foreign key 外键约束名
更改字段名	alter table 表名 change 原字段名 新字段名 数据类型 约束条件 [first 或 after 已有字段]
仅把字段名小写改大写（或大写改小写）	alter table 表名 modify 大写字段名(或改小写) 数据类型
仅更改数据类型	alter table 表名 modify 字段名 新数据类型
仅添加非空约束	alter table 表名 modify 字段名 数据类型 not null
仅删除非空约束(不为主键和唯一键时)	alter table 表名 modify 字段名 数据类型
添加默认值	alter table 表名 alter 字段名 set default ‘默认值’
删除默认值	alter table 表名 alter 字段名 drop default

三、备份恢复数据库

新安装的MySQL数据库没有设置root密码，用drop database或 drop table 删除数据库或删除表很轻松，一旦插入数据，用delete * from 命令删除数据，后果更加严重。
建表不易，数据无价，所以学习一下备份数据库，有备无患！
备份的数据库sql文件还可以迁移到其他电脑上使用。

1. 备份数据库命令mysqldump

首先打开命令提示符
输入进入二级mysql的bin文件夹命令：
cd C:\wamp\bin\mysql\mysql5.5.24\bin

mysqldump 备份工具就在bin目录下
然后就能输入备份数据库命令：
例如：mysqldump -uroot -p db_school > bak001_db_school.sql

备份文件取名 bak001_db_school.sql
因为没有root密码，直接回车就能生成备份文件，
bak001_db_school.sql 保存在二级mysql的bin文件夹里，
备份文件路径是 C:\wamp\bin\mysql\mysql5.5.24\bin\bak001_db_school.sql

mysqldump 命令格式：mysqldump -uroot -p root密码 备份数据库名> 备份文件夹路径\备份文件名.sql
备份文件夹路径：首先要新建备份文件夹，然后再执行备份数据库命令。

2. 恢复数据库命令source

删除数据库命令(用分号结束) drop database db_school;

先新建数据库(用分号结束) create database db_school character set gb2312;

然后选择数据库命令(不需要分号) use db_school

然后输入恢复数据命令(不需要分号)
source C:/wamp/bin/mysql/mysql5.5.24/bin/bak001_db_school.sql
注意备份文件路径中每一个反斜杠\ 都要改成斜杆/ 才能恢复成功

最后查看表命令(用分号结束) show tables;

mysql> drop database db_school
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> create database db_school character set gb2312;
Query OK, 1 row affected (0.00 sec)

mysql> use db_school
Database changed
mysql> source C:/wamp/bin/mysql/mysql5.5.24/bin/bak001_db_school.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db_school          |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> show tables;
+---------------------+
| Tables_in_db_school |
+---------------------+
| tb_class            |
| tb_course           |
| tb_score            |
| tb_student          |
| tb_student_china    |
+---------------------+
5 rows in set (0.00 sec)