数据库和表的基本操作（一）

主要内容：查看表结构与修改表名、修改字段名与字段数据类型、添加与删除字段、修改字段的排列位置、删除表的外键约束

查看表结构与修改表名

任务描述

本关任务：修改表名，并能顺利查询到修改后表的结构。

相关知识

为了完成本关任务，你需要掌握：
1.如何查看表的结构；
2.如何修改表的名称。

查看数据表基本结构

有强迫症或健忘症的小伙伴们在建好数据库和表以后，通常会怀疑自己刚才是不是敲错了，怎么办？如果不是使用图形界面是不是就没法查看啦？

不存在的，这就告诉你查看口令：DESCRIBE
语法规则为:DESCRIBE 表名；

大小写不敏感哟！
DESCRIBE可以查看表的字段信息，包括：字段名、字段数据类型、是否为主键、是否有默认值等。

比如本岛主新建了一个名叫Products的表，结构如下：

现在来看看“现场”，用DESCRIBE查表的结构是这样的。
其中，该结构中各个字段的含义分别为：

NULL：表示该列是否能存储 NULL 值；

Key：表示该列是否已编制索引；

PRI：表示该列是此表主键的一部分；

UNI：表示该列是 UNIQUE 索引的一部分；

MUL：表示在列中某个给定值允许出现多次；

Default：表示该列是否有默认值，如果有的话值是多少；

Extra：表示可以获取的与给定列有关的附加信息。

查看数据表详细结构

什么？你发现表建错了，你想知道错在哪里了？
那就用SHOW CREATE TABLE语句吧。

语法规则为：SHOW CREATE TABLE 表名; 。
还是刚才那个例子，让我们看看SHOW CREATE TABLE有什么能耐。


可以看到，使用 SHOW CREATE TABLE 语句，不仅仅可以返回给我们建表时所写的详细语句，而且还可以查看存储引擎和字符编码。
再告诉大家一个小诀窍，是不是觉得返回的结果排版有点乱。我们加上\G后效果就会有所改善哟，来看看！

修改表名

随着表越来越多，有时候你会后悔之前的数据表的名字根本没有认真斟酌过，想要重新命名一下，可以做到吗？

答案是：可以！MySQL是通过ALTER TABLE语句来修改表名的。

语法规则为：ALTER TABLE 旧表名 RENAME 新表名; 。
举个例子，我们先用SHOW TABLES看看我们的数据库中有什么表吧。

可以看到，我们目前已经有了两张表，分别叫Products和tb_emp。

接下来，我们使用ALTER TABLE为Products改名。

改名成功！Products成功变成了Mall_products。
接下来你们可以自行体验一下了！

编程要求

根据提示，在右侧编辑器补充代码：

  • 把数据表tb_emp改名为jd_emp；

  • 查看该数据库下数据表的列表；

  • 查看数据表jd_emp的基本结构。

测试说明
我会对你编写的代码进行测试，最终结果会如下图所示：

我的代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
USE Company;
#请在此处添加实现代码
########## Begin ##########

########## modify the table name ##########
alter table tb_emp rename jd_emp;


########## show tables in this database ##########
show tables;


########## describe the table ##########
describe jd_emp;


########## End ##########

修改字段名与字段数据类型

任务描述

本关任务：修改表中的字段名，并修改字段的数据类型。

相关知识

为了完成本关任务，你需要掌握：1.如何修改字段名；2.如何修改字段的数据类型。

修改字段名

有时，在我们建好一张表后会突然发现，哎呀！字段名貌似写错了！怎么办？要删了表再重新建一个新表吗？还是要删了这个字段再新建一个新的字段？
都不用，MySQL 中修改字段名称也有专门的语句，接近于自然语言，很好记忆。

语法规则为： ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型; 。
举个例子：
现在我们要把表Mall_products中的字段prod_id改名为ID。表结构如下：

输入命令：ALTER TABLE Mall_products CHANGE prod_id ID varchar(20);。执行过程如下所示。简单吧！

小提示： 如果不需要修改字段的数据类型，可以把新字段的数据类型设置为和原来一样，但是！千万不要空着它！

修改字段数据类型

随着存储的数据越来越多，数据库新人可能会发现，哎呀，当初预留的数据类型已经不能满足要求了，或者当初预留的字符太少了，怎么办？怎么修改？规则来了。

语法规则为： ALTER TABLE 表名 MODIFY 字段名 数据类型; 。
举个例子：
现在我们要把表Mall_products中的字段ID的数据类型改为varchar(30)。表结构如下：

输入命令：ALTER TABLE Mall_products MODIFY ID varchar(30);。执行过程如下所示。

ID的数据类型varchar(20)成功变成了varchar(30)。
接下来你们可以自行体验一下了！

编程要求

根据提示，在右侧编辑器补充代码:

  • 把数据表tb_emp的字段Id改名为prod_id，数据类型不变；
  • 把数据表tb_emp字段Name的数据类型改为varchar(30)。

数据表结构如下:

测试说明

我会对你编写的代码进行测试，最终结果会如下图所示：

我的代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
USE Company;

#请在此处添加实现代码
########## Begin ##########

########## change the column name ##########
alter table tb_emp change Id prod_id int(11);


########## change the data type of column ##########
alter table tb_emp modify Name varchar(30);


########## End ##########

DESCRIBE tb_emp;

添加与删除字段

任务描述

本关任务：分别在表的最后一列、第一列和指定列后添加新的字段，并删除表中的指定字段。

相关知识

为了完成本关任务，你需要掌握：
1.如何在指定位置添加新的字段，
2.如何删除指定的字段。

添加字段

因为甲方的业务需求是不停变化的，所以在数据库操作中，添加字段可是常有的事。一个完整的字段包括：字段名、数据类型和完整性约束。

语法规则为： ALTER TABLE 表名 ADD 新字段名 数据类型 [约束条件] [FIRST|AFTER] 已存在字段名; 。
以下是在 MySQL 中常用的约束。

NOT NULL 约束：确保某列不能有 NULL 值。

DEFAULT 约束：当某列没有指定值时，为该列提供默认值。

UNIQUE 约束：确保某列中的所有值是不同的。

PRIMARY Key 约束：唯一标识数据库表中的各行/记录。

CHECK 约束：CHECK 约束确保某列中的所有值满足一定条件。

在表的最后一列添加字段
只要不做[FIRST|AFTER]的位置说明，在添加字段时MySQL会默认把新字段加入到表的最后一列。

举个例子：
现在我们要把字段prod_country添加到表Mall_products的最后一列。表结构如下：

输入命令：
ALTER TABLE Mall_products ADD prod_country varchar(30);
执行结果如下所示：

在表的第一列添加字段
如果我们想在第一列添加新的字段，只需做FIRST的位置说明。

举个例子：
现在我们要把字段prod_country添加到表Mall_products的第一列。

输入命令：
ALTER TABLE Mall_products ADD prod_country varchar(30) FIRST;
执行结果如下所示：

在表的指定列后添加字段
如果我们想在某一列后面添加新的字段，只需做AFTER的位置说明，然后注明你想让它添加在哪个字段的后面即可。

举个例子：
现在我们要把字段prod_country添加到表Mall_products的 prod_name字段的后面。

输入命令：
ALTER TABLE Mall_products ADD prod_country varchar(30) AFTER prod_name;
执行结果如下所示：

总之，想要添加新的字段，记住绿色框里的语法规则就能记住三种位置的添加方式。

删除字段

有添加的需求就会有删除的需求。删除一个字段就是将数据表中的某个字段从表中移除。

语法规则为： ALTER TABLE 表名 DROP 字段名; 。
举个例子：
现在我们要把字段prod_price从表Mall_products中删除。表结构如上图结果所示。

输入命令：
ALTER TABLE Mall_products DROP prod_price;
执行结果如下所示：

字段prod_price成功删除！
接下来你们可以自行体验一下了！

编程要求

根据提示，在右侧编辑器补充代码:

  • 在数据表tb_emp的Name字段后添加字段Country，数据格式为varchar(20)；
  • 删除数据表tb_emp中的字段Salary。

数据表结构如下:

测试说明

我会对你编写的代码进行测试，最终结果会如下图所示：

我的代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
USE Company;

#请在此处添加实现代码
########## Begin ##########

########## add the column ##########
alter table tb_emp add Country varchar(20) after Name;

 
########## delete the column ##########
alter table tb_emp drop Salary;


########## End ##########

DESCRIBE tb_emp;

修改字段的排列位置

任务描述

本关任务：修改表中某字段的顺序，分别将其排至表中的第一列与指定列之后。

相关知识

为了完成本关任务，你需要掌握：如何将某字段排至表中的指定位置。

修改字段的排列位置

在一个数据表被创建的时候，其字段的排列顺序就已被确定了。不能变了吗？怎么可能，一锤子买卖没人敢做。

我们可以通过ALTER TABLE来改变表中字段的相对位置。

语法规则为： ALTER TABLE 表名 MODIFY 字段1 数据类型 FIRST|AFTER 字段2; 。
其中，字段1指要修改位置的字段，FIRST与AFTER 字段2为可选参数。

修改字段为表的第一个字段

如果我们想把字段的位置调整到第一列，只需做FIRST的位置说明。

举个例子
现在我们要把字段prod_price调整到表Mall_products的第一列。Mall_products表结构如下所示：

输入命令：
ALTER TABLE Mall_products MODIFY prod_price FLOAT FIRST;

执行过程如下所示：

修改字段到表的指定列之后

还有一种位置调整的方法可以让你把想调整的字段放在除了第一列的任何位置。调整的时候需要做AFTER 字段2的`位置说明。

举个例子
现在我们要把字段prod_price调整到字段prod_country的后面。Mall_products表结构如下所示：

输入命令：
ALTER TABLE Mall_products MODIFY prod_price FLOAT AFTER prod_country;

执行过程如下所示：

调整成功！
接下来你们可以自行体验一下了！

编程要求

根据提示，在右侧编辑器补充代码:

将数据表tb_emp的Name字段移至第一列，数据格式不变；

将DeptId字段移至Salary字段的后边，数据格式不变。

数据表结构如下:

测试说明

我会对你编写的代码进行测试，最终结果会如下图所示：

我的代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
USE Company;

#请在此处添加实现代码
########## Begin ##########

########## modify the column to top ##########
alter table tb_emp modify Name varchar(25) first;


########## modify the column to the rear of another column ##########
alter table tb_emp modify DeptId int(11) after Salary;


########## End ##########

DESCRIBE tb_emp;

删除表的外键约束

任务描述

本关任务：删除表中的外键约束。

相关知识

为了完成本关任务，你需要掌握：如何删除表的外键约束

删除表的外键约束

我们曾在第一章中讲过外键的作用，以及如何创建一个表的外键。建立了外键我们就建立起了两张表的关联关系，那如果我想删除主表呢？为了确保数据库的正确性，我们必须先解除两个表之间的关联关系，那就是删除外键约束啦！让我们先来看看删除外键约束的语法规则。

语法规则为： ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名; 。
举个例子：
我们先建立了一个主表country，表结构如下图所示：

然后建立一个有外键约束的子表Mall_products2，让它的键country_id作为外键关联到country的主键id。SQL语句如下：

1
2
3
4
5
6
7
CREATE TABLE Mall_products2
(
id INT(11) PRIMARY KEY,
country_name VARCHAR(20) NOT NULL,
country_id INT(11) NOT NULL,
CONSTRAINT prod_country FOREIGN KEY(country_id) REFERENCES country(id)
)

上述语句成功执行后，在表Mall_products2上添加了名称为prod_country的外键约束，外键名称为prod_country，依赖于表country的主键id，从下图中可以看到，已经成功添加了表的外键：

Mall_products2表结构如下图所示：

下面开始删除外键约束，语句如下：
ALTER TABLE Mall_products2 DROP FOREIGN KEY prod_country;

使用SHOW CREATE TABLE查看表 Mall_products2` 的结构，结果如下：

我们可以看到，FOREIGN KEY不见了，外键约束删除成功！
接下来你们可以自行体验一下了！

编程要求

我们已经为你建立了主表tb_dept和子表tb_emp，在表tb_emp上添加了名称为emp_dept的外键约束，外键名称为DeptId，依赖于表tb_dept的主键Id，下面那是两张表的结构展示：

请你根据提示，在右侧编辑器Begin-End中补充代码:

  • 删除数据表tb_emp的外键约束emp_dept。

测试说明

我会对你编写的代码进行测试，最终结果会如下图所示：

我的代码

1
2
3
4
5
6
7
8
9
10
USE Company;

#请在此处添加实现代码
########## Begin ##########

########## delete the foreign key ##########
alter table tb_emp drop foreign key emp_dept;

########## End ##########
SHOW CREATE TABLE tb_emp \G;