史上最简单MySQL教程详解(基础篇)之表的维护和改造

表结构修改

在我们实际的开发的过程,随着开发的深入,会发现我们事先设计好的表可能已经不再适合,就会设计到对表的修改和改造。这里我就向大家介绍一下一些常用的方法和情况。这里我们使用的是之前在史上最简单MySQL教程详解(基础篇)之常用表操作和表参数介绍中就已经使用到了【ALTER】语句进行修改。

注意事项:

  • 任何设计到表的的修改和改造的操作,都有可能发生不可预知的错误或者情况出现,所以安全起见,请在每次操作之前都做好数据的备份,以防万一。

修改列的数据类型

具体如下:

ALTER TABLE 表名 MODIFY 列名 数据类型;

这里,我们就以我们之前创建好的【user】表来进行演示:
运行结果如下:

mysql> desc user;
+——–+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——–+————-+——+—–+———+——-+
| userId | char(5) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | 0 | |
+——–+————-+——+—–+———+——-+
3 rows in set (0.00 sec)
mysql> alter table user modify name varchar(30);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user;
+——–+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——–+————-+——+—–+———+——-+
| userId | char(5) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| sex | char(1) | YES | | 0 | |
+——–+————-+——+—–+———+——-+
3 rows in set (0.00 sec)

注意事项:

  • 列的数据类型是跨域随时修改的。但是当数据库已经存在数据的时候,一定要注意,修改后的数据类型是否可用。例如我在【user】表插入了一条数据后,再改变【name】的数据类型为【INT】就会报错【ERROR 1366 (HY000): Incorrect integer value】。
  • 修改的过程中有时会出现原来的数据变成乱码,或者一部分数据消失的情况 ,所以请务必记得备份数据。

追加新列

表尾追加新列

如果我们想在已经建好的表里添加一个新的列,那么我们依然使用的是【ALTER】语句,具体的语法如下:

ALTER TABLE 表名 ADD 列名 数据类型;

我们往我们的【user】表中追加一列【phone】,运行结果如下:

mysql> alter table user add phone int(13);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user;
+——–+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——–+————-+——+—–+———+——-+
| userId | char(5) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| sex | char(1) | YES | | 0 | |
| phone | int(13) | YES | | NULL | |
+——–+————-+——+—–+———+——-+
4 rows in set (0.00 sec)

可以看出我们在表尾添加上了域名(字段名)为【phone】的列。

表头追加新列

语法如下:

ALTER TABLE 表名 ADD 列名 数据类型 FIRST;

我们在追加一列名为【address】在表头吧,运行结果如下:

mysql> alter table user add address varchar(50) first;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user;
+———+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———+————-+——+—–+———+——-+
| address | varchar(50) | YES | | NULL | |
| userId | char(5) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| sex | char(1) | YES | | 0 | |
| phone | int(13) | YES | | NULL | |
+———+————-+——+—–+———+——-+
5 rows in set (0.00 sec)

表头追加成功;

任意位置追加新列

如果我们想要在任意的位置追加新列,那么我们可以使用下面的语法:

ALTER TABLE 表名 ADD 列名 数据类型 after 已经存在的列名;

我们在【sex】后面追加一列【province】试一试,运行结果如下:

mysql> alter table user add province varchar(5) after sex;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user;
+———-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———-+————-+——+—–+———+——-+
| address | varchar(50) | YES | | NULL | |
| userId | char(5) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| sex | char(1) | YES | | 0 | |
| province | varchar(5) | YES | | NULL | |
| phone | int(13) | YES | | NULL | |
+———-+————-+——+—–+———+——-+
6 rows in set (0.00 sec)

我们就可以看出,我们已经在【sex】后面追加一列【province】

改变某列的位置

如果我们是想要改变已经创建好的表中的某列的位置,我们已改怎么办呢?语法如下:

ALTER TABLE 表名 MODIFY 需要移动的表名 字段类型 AFTER 已经存在的某列的位置

我们就尝试把【address】移动到【province】后面吧,运行结果如下:

mysql> alter table user MODIFY address varchar(50) after province;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user;
+———-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———-+————-+——+—–+———+——-+
| userId | char(5) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| sex | char(1) | YES | | 0 | |
| province | varchar(5) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
| phone | int(13) | YES | | NULL | |
+———-+————-+——+—–+———+——-+
6 rows in set (0.00 sec)

移动成功;

删除表

这里我们又用到了我们的【DROP】命令,语法如下:

ALTER TABLE 表名 DROP 列名;

我们尝试删除【province】试一试,运行结果如下:

mysql> alter table user drop province;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user;
+———+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———+————-+——+—–+———+——-+
| userId | char(5) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| sex | char(1) | YES | | 0 | |
| address | varchar(50) | YES | | NULL | |
| phone | int(13) | YES | | NULL | |
+———+————-+——+—–+———+——-+
5 rows in set (0.00 sec)

删除成功;

表的复制

这里我们先演示往我们的【user】中插入这样的一条数据:

域名(字段名)
userId 1
name 张三
sex 0

我们需要使用的是【INSERT】语句,这个我这里就不过多的解释,这里仅仅只是作为插入测试数据使用 ,我们会详细介绍它的用法。语法如下:

INSERT INTO 表名 (列名1,列名2,列名3...) VALUES (数据1,数据2,数据3....);

运行结果如下:

mysql> insert into user (userId,name,sex) value (1,’张三’,0);
Query OK, 1 row affected (0.00 sec)

这里我们还需要【SELECT】语句,进行数据查询,语法如下:

SELECT * FROM 表名

执行结果如下:

mysql> select * from user;
+——–+——+——+
| userId | name | sex |
+——–+——+——+
| 1 | 张三 | 0 |
+——–+——+——+
1 row in set (0.00 sec)

当我们做好数据准备以后,我们就可以尝试表的复制操作了;

表结构复制

这种复制方法呢,仅仅只复制了表的结构,而不会将表中的数据进行复制,具体语法如下:

CREATE TABLE 新表名 LIKE 旧表名;

我们复制下【user】表试试,运行结果如下:

mysql> create table user_a like user;
Query OK, 0 rows affected (0.06 sec)
mysql> show tables;
+—————-+
| Tables_in_test |
+—————-+
| user |
| user_a |
+—————-+
2 rows in set (0.00 sec)
mysql> desc user_a;
+——–+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——–+————-+——+—–+———+——-+
| userId | char(5) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| sex | char(1) | YES | | 0 | |
+——–+————-+——+—–+———+——-+
3 rows in set (0.00 sec)
mysql> select * from user_a;
Empty set (0.00 sec)

首先我们将复制出来的新表命名为【user_a】然后查看他的表结构与我们原来的【user】表相同,并且通过【SELECT】语句发现表中并没有任何数据;

数据复制

这种方法主要是将旧表的数据复制到新表中,所以我们就尝试着将【user】表中的数据复制到我们刚刚创建的表【user_a】中吧。语法如下:

INSERT INTO 新表 SELECT * FROM 旧表

运行结果如下:

mysql> insert into user_a select * from user;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from user_a;
+——–+——+——+
| userId | name | sex |
+——–+——+——+
| 1 | 张三 | 0 |
+——–+——+——+
1 row in set (0.00 sec)

我们可以看到,我们之前在【user】表中插入的数据现在已经复制到了【user_a】表中

表结构+数据复制

你看了上面两种方法,也许会说:这也太麻烦了吧,我想要完整复制一张新表居然需要敲两次命令。别急,接下来这个命令就可以解决的你的抱怨。语法如下:

CREATE TABLE 新表名 SELECT * FROM 旧表名;

运行结果如下:

mysql> create table user_b select * from user;
Query OK, 1 row affected (0.10 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> show tables;
+—————-+
| Tables_in_test |
+—————-+
| user |
| user_a |
| user_b |
+—————-+
3 rows in set (0.00 sec)
mysql> desc user_b;
+——–+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——–+————-+——+—–+———+——-+
| userId | char(5) | NO | | NULL | |
| name | varchar(30) | YES | | NULL | |
| sex | char(1) | YES | | 0 | |
+——–+————-+——+—–+———+——-+
3 rows in set (0.00 sec)
mysql> select * from user_b;
+——–+——+——+
| userId | name | sex |
+——–+——+——+
| 1 | 张三 | 0 |
+——–+——+——+
1 row in set (0.00 sec)

我们可以看出,我们同时将【user】的表结构和数据都复制到了【user_b】中。

到此,我们也就已经完成MySQL基础操作中的大部分内容,接下来,我们将介绍的是MySQL基础应用中最为重要也是最常用的SQL语句

原文地址:https://www.cnblogs.com/newtol/p/10159111.html