数据表的基础操作（三）修改_java sheet 最后一列-CSDN博客

本文链接：https://blog.csdn.net/m0_62654099/article/details/126527333

文章目录

修改表

修改表

修改表名 —— RENAME

修改数据表名称

ALTER TABLE 旧表名 RENAME 新表名;

实例1

题目：将employee表的名称改为demo。

1.查看数据表

 mysql> SHOW TABLES;
+----------------+
| Tables_in_firm |
+----------------+
| employee       |
+----------------+
1 row in set (0.00 sec)

2.修改表名

mysql> ALTER TABLE employee RENAME demo;
Query OK, 0 rows affected (0.06 sec)

3.再次查看数据表

  mysql> SHOW TABLES;
    +----------------+
    | Tables_in_firm |
    +----------------+
    | demo           |
    +----------------+
    1 row in set (0.00 sec)

修改字段数据类型 —— MODIFY

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

实例2

题目：将employee表中的type字段的数据类型修改为CHAR(30)。

1.将type字段的数据类型修改为CHAR(30)。

mysql> ALTER TABLE employee MODIFY type CHAR(30);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

2.查看表的基本结构

mysql> DESC employee;
+------------+-------------------+------+-----+---------+-------+
| Field      | Type              | Null | Key | Default | Extra |
+------------+-------------------+------+-----+---------+-------+
| id         | int               | NO   | PRI | NULL    |       |
| name       | varchar(30)       | NO   |     | NULL    |       |
| sex        | enum('男','女')    | YES  |     | 男      |       |
| calling    | bigint            | YES  |     | NULL    |  	    |
| S_id       | int               | YES  |     | NULL    |       |
| entry_time | date              | YES  |     | NULL    |       |
| type       | char(30)          | YES  |     | NULL    |       |
+------------+-------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

修改字段名 —— CHANGE

ALTER TABLE 表名 CHANGE 旧列名 新列名+数据类型;

实例3

题目：将employee表中的S_di修改为x_id

1.将S_di修改为x_id

mysql> ALTER TABLE employee CHANGE S_id x_id INT(11);
Query OK, 0 rows affected, 1 warning (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 1

2.查看表结构

mysql> DESC employee;
+------------+-------------------+------+-----+---------+-------+
| Field      | Type              | Null | Key | Default | Extra |
+------------+------------------ +------+-----+---------+-------+
| id         | int               | NO   | PRI | NULL    |       |
| name       | varchar(30)       | NO   |     | NULL    |       |
| sex        | enum('男','女')   | YES  |      | 男      |       |
| calling    | bigint            | YES  |     | NULL    |       |
| x_id       | int               | YES  |     | NULL    |       |
| entry_time | date              | YES  |     | NULL    |       |
| type       | char(30)          | YES  |     | NULL    |       |
+------------+-------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

3.将name改为x_name数据类型修改为CHAR(30)

mysql> ALTER TABLE employee CHANGE name x_name CHAR(30);
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

4.再次查看表结构

mysql> DESC employee;
+------------+-------------------+------+-----+---------+-------+
| Field      | Type              | Null | Key | Default | Extra |
+------------+-------------------+------+-----+---------+-------+
| id         | int               | NO   | PRI | NULL    |       |
| x_name     | char(30)          | YES  |     | NULL    |       |
| sex        | enum('男','女')   | YES  |     | 男       |       |
| calling    | bigint            | YES  |     | NULL    |       |
| x_id       | int               | YES  |     | NULL    |       |
| entry_time | date              | YES  |     | NULL    |       |
| type       | char(30)          | YES  |     | NULL    |       |
+------------+-------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

添加字段 —— ADD

添加字段一共有三种情况

    在表的第一列
    在表的最后一列
    在指定的列之后

添加在表的第一列——FIRST

ALTER TABLE 表名 ADD 添加的列名+数据类型 FIRST;

实例4

1.将employee表中添加state字段

mysql> ALTER TABLE employee ADD state TINYINT(4) FIRST;
Query OK, 0 rows affected, 1 warning (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 1

2.查询表的结构

mysql> DESC employee;
+------------+-------------------+------+-----+---------+-------+
| Field      | Type              | Null | Key | Default | Extra |
+------------+-------------------+------+-----+---------+-------+
| state      | tinyint           | YES  |     | NULL    |        |
| id         | int               | NO   | PRI | NULL    |        |
| x_name     | char(30)          | YES  |     | NULL    |        |
| sex        | enum('男','女')    | YES  |     | 男      |        |
| calling    | bigint            | YES  |     | NULL    |        |
| x_id       | int               | YES  |     | NULL    |        |
| entry_time | date              | YES  |     | NULL    |        |
| type       | char(30)          | YES  |     | NULL    |        |
+------------+-------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

在表的最后一列添加字段

ALTER TABEL 表名 ADD 添加的列+数据类型；

实例5

题目：在employee表中添加price字段

1.添加price字段

mysql> ALTER TABLE employee ADD price VARCHAR(30);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

2.查看表的结构

 mysql> DESC employee;
    +------------+-------------------+------+-----+---------+-------+
    | Field      | Type              | Null | Key | Default | Extra |
    +------------+-------------------+------+-----+---------+-------+
    | state      | tinyint           | YES  |     | NULL    |       |
    | id         | int               | NO   | PRI | NULL    |       |
    | x_name     | char(30)          | YES  |     | NULL    |       |
    | sex        | enum('男','女')    | YES  |     | 男      |       |
    | calling    | bigint            | YES  |     | NULL    |       |
    | x_id       | int               | YES  |     | NULL    |       |
    | entry_time | date              | YES  |     | NULL    |       |
    | type       | char(30)          | YES  |     | NULL    |       |
    | price      | varchar(30)       | YES  |     | NULL    |       |
    +------------+-------------------+------+-----+---------+-------+
    9 rows in set (0.00 sec)

添加在表的指定的列之后——AFTER

 ALTER TABLE 表名 ADD 添加的字段名+数据类型 AFTER 指定的字段名;

实例6

题目：在employee表中price字段之后添加intro字段。

1.在price字段之后添加intro字段。

 mysql> ALTER TABLE employee ADD intro TEXT AFTER price;
    Query OK, 0 rows affected (0.04 sec)
    Records: 0  Duplicates: 0  Warnings: 0

2.查看表结构

mysql> DESC employee;
+------------+-------------------+------+-----+---------+-------+
| Field      | Type              | Null | Key | Default | Extra |
+------------+-------------------+------+-----+---------+-------+
| state      | tinyint           | YES  |     | NULL    |       |
| id         | int               | NO   | PRI | NULL    |       |
| x_name     | char(30)          | YES  |     | NULL    |       |
| sex        | enum('男','女')    | YES  |     | 男      |       |
| calling    | bigint            | YES  |     | NULL    |       |
| x_id       | int               | YES  |     | NULL    |       |
| entry_time | date              | YES  |     | NULL    |       |
| type       | char(30)          | YES  |     | NULL    |       |
| price      | varchar(30)       | YES  |     | NULL    |       |
| intro      | text              | YES  |     | NULL    |       |
+------------+-------------------+------+-----+---------+-------+
10 rows in set (0.00 sec)

删除字段——DROP

删除数据表

ALTER TABLE 表名 DROP 需要删除的列名;

实例7

题目：将employee表中的x_id字段删除

1.删除x_id字段。

mysql> ALTER TABLE employee DROP x_id;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

2.查看表的结构

mysql> DESC employee;
+------------+-------------------+------+-----+---------+-------+
| Field      | Type              | Null | Key | Default | Extra |
+------------+-------------------+------+-----+---------+-------+
| state      | tinyint           | YES  |     | NULL    |       |
| id         | int               | NO   | PRI | NULL    |       |
| x_name     | char(30)          | YES  |     | NULL    |       |
| sex        | enum('男','女')    | YES  |     | NULL    |       |
| calling    | brigint           | YES  |     | 男      |       |
| entry_time | date              | YES  |     | NULL    |       |
|  type      | char(30)          | YES  |     | NULL    |       |
| price      | varchar(30)       | YES  |     | NULL    |       |
| intro      | text              | YES  |     | NULL    |       |
+------------+-------------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

修改字段的顺序：——MODIFY

修改字段的顺序

ALTER TABLE 表名 MODIFY 需要修改的列名+数据类型 FIRST 固定列之前
ALTER TABLE 表名 MODIFY 需要修改的列名+数据类型 AFTER 固定列之后

实例8

题目：将employee表中type字段的位置修改为x_name之后。

1.将type字段的位置修改为x_name之后。

   mysql> ALTER TABLE employee MODIFY type CHAR(30) AFTER x_name;
    Query OK, 0 rows affected (0.20 sec)
    Records: 0  Duplicates: 0  Warnings: 0

2.查询表的结构

mysql> DESC employee;
+------------+-------------------+------+-----+---------+-------+
| Field      | Type              | Null | Key | Default | Extra |
+------------+-------------------+------+-----+---------+-------+
| state      | tinyint           | YES  |     | NULL    |       |
| id         | int               | NO   | PRI | NULL    |       |
| x_name     | char(30)          | YES  |     | NULL    |       |
| type       | char(30)          | YES  |     | NULL    |       |
| sex        | enum('男','女')    | YES  |     | 男      |       |
| calling    | bigint            | YES  |     | NULL    |       |
| entry_time | date              | YES  |     | NULL    |       |
| price      | varchar(30)       | YES  |     | NULL    |       |
| intro      | text              | YES  |     | NULL    |       |
+------------+-------------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

如果要修改某字段为表的第一字段

 ALTER TABLE 表名 MODIFY 想要到第一位的列+数据类型 FIRST;

修改存储引擎

  ALTER TABLE 表名 旧存储引擎=新存储引擎;