当前位置： 首页 > article >正文

【重学 MySQL】四十七、表的操作技巧——修改、重命名、删除与清空

article 2024/11/20 15:18:07

【重学 MySQL】四十七、表的操作技巧——修改、重命名、删除与清空

  • 修改表
    • 添加字段
      • 语法
      • 示例
      • 注意事项
    • 删除字段
      • 语法
      • 示例
    • 修改字段
      • 使用 MODIFY COLUMN
        • 语法
        • 示例
      • 使用 CHANGE COLUMN
        • 语法
        • 示例
  • 重命名表
    • 语法
    • 示例
  • 删除表
      • 语法
      • 示例
  • 清空表
    • 使用 `TRUNCATE TABLE`
    • 使用 `DELETE FROM`
    • 对比 `TRUNCATE TABLE` 和 `DELETE FROM`
      • 操作方式
      • 性能
      • 事务处理
      • 触发器和外键约束
      • 自增主键
      • 使用建议
  • 注意事项
  • 总结

在这里插入图片描述

修改表

在MySQL中，我们经常需要对已存在的表进行修改，以满足不断变化的数据存储需求。这通常包括添加新列、删除现有列、修改列的数据类型或约束条件等操作。

添加字段

在MySQL中，为已存在的表添加字段（也称为列）是一个常见的操作，这通常是为了满足新的数据存储需求或适应业务逻辑的变化。使用ALTER TABLE语句可以方便地实现这一操作。

语法

ALTER TABLE table_name ADD COLUMN column_name datatype [constraints] [FIRST | AFTER existing_column];
  • table_name：要添加字段的表的名称。
  • column_name：新字段的名称。
  • datatype：新字段的数据类型，如INT、VARCHAR、DATE等。
  • constraints：对新字段的约束条件，如NOT NULL、DEFAULT值、UNIQUE等（可选）。
  • [FIRST | AFTER existing_column]：
    • FIRST：将新字段添加到表的开头。
    • AFTER existing_column：将新字段添加到指定字段 existing_column 之后。如果省略此部分，新字段将默认添加到表的末尾。

示例

  1. 将新字段添加到表末尾（默认行为）：

    ALTER TABLE employees  
ADD COLUMN phone_number VARCHAR(20);

  2. 将新字段添加到表开头：

    ALTER TABLE employees  
ADD COLUMN employee_id INT AUTO_INCREMENT FIRST,  
ADD PRIMARY KEY (employee_id); -- 假设这是主键字段

    注意：在添加主键字段时，通常也会同时设置 AUTO_INCREMENT 属性和 PRIMARY KEY 约束。

  3. 将新字段添加到特定字段之后：

    ALTER TABLE employees  
ADD COLUMN hire_date DATE AFTER last_name;

    在这个例子中，hire_date 字段将被添加到 last_name 字段之后。

注意事项

  1. 字段位置：在MySQL中，默认情况下新添加的字段会被放置在表的最后。如果需要将新字段添加到特定位置，可以使用AFTER column_name子句来指定位置。例如，将phone_number字段添加到email字段之后：

    ALTER TABLE employees ADD COLUMN phone_number VARCHAR(20) AFTER email;

    或者，如果希望将新字段添加到表的最前面，可以使用FIRST关键字：

    ALTER TABLE employees ADD COLUMN phone_number VARCHAR(20) FIRST;

  2. 表锁定：在执行ALTER TABLE操作时，MySQL可能会锁定表，这会导致在该表上的其他操作（如查询、更新等）被阻塞，直到ALTER TABLE操作完成。因此，在执行此类操作时应尽量选择在系统负载较低的时候进行。

  3. 备份数据：虽然ALTER TABLE操作通常是安全的，但在执行任何可能影响表结构的操作之前，始终建议备份数据以防止意外情况发生。

  4. 权限要求：执行ALTER TABLE操作需要相应的权限。通常，只有表的创建者或具有足够权限的用户才能修改表结构。

通过掌握ALTER TABLE ... ADD COLUMN语句的使用，我们可以灵活地根据需求为MySQL数据库中的表添加新的字段。

删除字段

在MySQL中，删除表中的字段（也称为列）是一个需要谨慎操作的任务，因为一旦字段被删除，与该字段相关的所有数据也将被永久移除，且无法恢复（除非你有备份）。使用ALTER TABLE语句可以方便地删除表中的字段。

语法

ALTER TABLE table_name DROP COLUMN column_name;
  • table_name：要删除字段的表的名称。
  • column_name：要删除的字段的名称。

示例

假设我们有一个名为employees的表，并且该表包含一个名为middle_name的字段，现在我们想要删除这个字段，可以使用以下SQL语句：

ALTER TABLE employees DROP COLUMN middle_name;

执行上述语句后，middle_name字段及其所有数据将从employees表中被永久删除。

修改字段

在MySQL中，修改表中的字段（也称为列）通常涉及更改字段的数据类型、名称、默认值、约束条件等。这可以通过ALTER TABLE语句结合MODIFY COLUMN或CHANGE COLUMN子句来实现。

使用 MODIFY COLUMN

MODIFY COLUMN 用于更改现有字段的数据类型、约束条件等，但不能更改字段的名称。

语法
ALTER TABLE table_name MODIFY COLUMN column_name datatype [constraints];
  • table_name：要修改的表的名称。
  • column_name：要修改的字段的名称。
  • datatype：新的数据类型。
  • [constraints]：可选的字段约束条件，如 NOT NULL、DEFAULT 值、UNIQUE 键等。
示例

假设我们有一个名为employees的表，并且该表包含一个名为salary的字段，现在我们想要更改该字段的数据类型为DECIMAL并设置默认值为50000.00，可以使用以下SQL语句：

ALTER TABLE employees MODIFY COLUMN salary DECIMAL(10, 2) DEFAULT 50000.00;

使用 CHANGE COLUMN

CHANGE COLUMN 既可以更改字段的数据类型、约束条件，也可以更改字段的名称。

语法
ALTER TABLE table_name CHANGE COLUMN old_column_name new_column_name datatype [constraints];
  • table_name：要修改的表的名称。
  • old_column_name：要修改的现有字段的名称。
  • new_column_name：新的字段名称（如果不需要更改名称，可以与old_column_name相同）。
  • datatype：新的数据类型。
  • [constraints]：可选的字段约束条件。
示例

假设我们有一个名为employees的表，并且该表包含一个名为emp_salary的字段，现在我们想要将字段名称更改为salary，并将其数据类型更改为DECIMAL，同时设置默认值为50000.00，可以使用以下SQL语句：

ALTER TABLE employees CHANGE COLUMN emp_salary salary DECIMAL(10, 2) DEFAULT 50000.00;

重命名表

在MySQL中，重命名表的操作相对简单，你可以使用RENAME TABLE语句来实现。这个语句允许你一次性重命名一个或多个表。

语法

RENAME TABLE old_table_name TO new_table_name;

或者，如果你需要同时重命名多个表，可以使用逗号分隔的列表（注意，在MySQL 8.0及更高版本中，一次性重命名多个表的能力被限制为在同一个数据库内的表）：

RENAME TABLE 
    old_table_name1 TO new_table_name1,
    old_table_name2 TO new_table_name2,
    ...;
  • old_table_name：当前的表名称。
  • new_table_name：新的表名称。

示例

假设你有一个名为employees的表，现在你想要将这个表重命名为staff，你可以使用以下SQL语句：

RENAME TABLE employees TO staff;

如果你同时想要将另一个名为departments的表重命名为orgs，你可以这样做：

RENAME TABLE 
    employees TO staff,
    departments TO orgs;

删除表

在MySQL中，删除表的操作是通过DROP TABLE语句来实现的。这个操作是永久性的，一旦执行，表及其包含的所有数据都会被删除，且无法撤销。因此，在执行此操作之前，请务必确保你已经备份了所有需要的数据，或者确认这些数据不再需要。

语法

DROP TABLE IF EXISTS table_name;
  • table_name：要删除的表的名称。

示例

假设你有一个名为old_table的表，现在你想要删除它，可以使用以下SQL语句：

DROP TABLE IF EXISTS old_table;

注意：DROP TABLE操作是不可逆的，一旦执行，表及其所有数据都将被永久删除，因此在执行此操作前务必备份重要数据。

通过正确地使用DROP TABLE语句，你可以安全地删除MySQL数据库中的表。但请务必谨慎操作，并在执行前做好充分的准备和评估。如果你不确定是否应该删除某个表，或者担心删除操作可能会带来不可预知的后果，建议先咨询数据库管理员或具有相关经验的同事。

清空表

在MySQL中，如果你想要清空表中的所有数据，但保留表结构（即表的定义、索引、约束等），你可以使用TRUNCATE TABLE语句或DELETE FROM语句。这两种方法各有优缺点，适用于不同的场景。

使用 TRUNCATE TABLE

TRUNCATE TABLE 是一种快速清空表的方法，它通常比 DELETE FROM 更高效，因为它不会逐行删除数据，而是直接释放表数据所占用的空间并重置表。但是，TRUNCATE TABLE 是一个DDL（数据定义语言）操作，而不是DML（数据操作语言）操作，这意味着它会自动提交，不能回滚，并且会重置表的自增计数器。

TRUNCATE TABLE table_name;
  • table_name：要清空的表的名称。

注意事项：

  • TRUNCATE TABLE 不能带有 WHERE 子句，它会删除表中的所有行。
  • 如果表中有外键约束，并且这些外键被其他表引用，则可能无法直接 TRUNCATE 该表。
  • TRUNCATE TABLE 会重置表的自增计数器（AUTO_INCREMENT）。
  • TRUNCATE TABLE 通常比 DELETE 快，因为它不生成单独的行删除操作。

使用 DELETE FROM

DELETE FROM 语句逐行删除表中的数据，并且可以在 WHERE 子句中指定条件来删除特定的行。由于 DELETE 是DML操作，它可以被事务控制，允许回滚。

DELETE FROM table_name [WHERE condition];
  • table_name：要清空的表的名称。
  • [WHERE condition]：可选的条件，用于指定要删除的行。如果不带条件，则会删除表中的所有行。

注意事项：

  • DELETE FROM 可以带有 WHERE 子句来指定删除条件。
  • DELETE FROM 操作可以被事务包围，允许回滚。
  • DELETE FROM 通常比 TRUNCATE TABLE 慢，特别是当表中有大量数据时。
  • DELETE FROM 不会重置表的自增计数器，除非使用 TRUNCATE TABLE 或手动重置。

对比 TRUNCATE TABLE 和 DELETE FROM

TRUNCATE TABLE和DELETE FROM是MySQL中用于删除表中数据的两种不同方法，它们之间存在显著的差异。

操作方式

  • TRUNCATE TABLE：这是一个DDL（数据定义语言）操作，它直接删除表中的所有数据，并重置表的自增计数器（如果存在）。该操作相当于删除表并重新创建一个空表，但不会删除表结构（如列、索引、约束等）。
  • DELETE FROM：这是一个DML（数据操作语言）操作，它逐行删除表中的数据。可以通过WHERE子句指定删除条件，如果没有条件则删除所有行。此外，DELETE操作会触发相关的触发器和外键约束。

性能

  • TRUNCATE TABLE：由于TRUNCATE操作不会逐行删除数据，而是直接释放整个表的存储空间，因此通常比DELETE操作更快，特别是在处理大型表时。
  • DELETE FROM：DELETE操作需要逐行删除数据，并记录每个删除操作的事务日志，以便支持回滚。因此，在处理大量数据时，DELETE操作可能会比较慢，并且会占用更多的磁盘空间来存储事务日志。

事务处理

  • TRUNCATE TABLE：TRUNCATE操作是一个隐式的提交操作，它会立即提交当前事务并释放锁。因此，它不能在事务中回滚。
  • DELETE FROM：DELETE操作可以在事务中使用，并且支持回滚。如果在事务中执行DELETE操作后发生错误或需要取消删除，可以使用ROLLBACK命令来撤销该操作。

触发器和外键约束

  • TRUNCATE TABLE：TRUNCATE操作不会触发与表相关的触发器，也不会检查外键约束。因此，如果表被其他表的外键所引用，则可能无法直接TRUNCATE该表。
  • DELETE FROM：DELETE操作会触发与表相关的触发器，并且会检查外键约束。如果尝试删除的行被其他表的外键所引用，则DELETE操作会失败并返回错误。

自增主键

  • TRUNCATE TABLE：执行TRUNCATE操作后，表的自增主键计数器会被重置。这意味着下一次插入数据时，自增主键将从初始值（通常是1）开始。
  • DELETE FROM：DELETE操作不会重置表的自增主键计数器。即使删除了所有行，自增主键的计数器也会继续递增。

使用建议

  • 如果需要快速清空表中的所有数据，并且不关心自增主键计数器的重置、触发器的触发或外键约束的检查，可以使用TRUNCATE TABLE。
  • 如果需要在事务中控制数据的删除，或者需要基于特定条件删除行，或者希望保留自增主键计数器的当前值，则应该使用DELETE FROM。

综上所述，TRUNCATE TABLE和DELETE FROM在MySQL中各有优缺点，选择哪种方法取决于具体的需求和场景。在使用这些命令时，请务必谨慎操作，并确保已经备份了重要的数据。

在执行任何清空表的操作之前，请务必备份数据，以防万一需要恢复。

注意事项

  1. 权限要求：执行RENAME TABLE操作需要相应的权限。通常，只有表的创建者或具有足够权限的用户才能重命名表。

  2. 表锁定：在执行RENAME TABLE操作时，MySQL会锁定涉及的表以进行结构修改。这可能会导致在该表（或这些表）上的其他操作被阻塞，直到RENAME TABLE操作完成。因此，建议在系统负载较低且对表的使用较少的时候进行此类操作。

  3. 依赖关系：检查要重命名的表是否被其他表的查询、视图、存储过程、触发器等引用，或者是否作为外键的参照表。如果有，你需要先处理这些依赖关系，否则可能会导致数据库完整性问题或查询错误。

  4. 应用程序更新：如果你的应用程序直接引用了要重命名的表，你需要确保更新应用程序中的相关代码，以使用新的表名称。

  5. 数据库引擎：虽然大多数MySQL存储引擎都支持RENAME TABLE操作，但某些特定的引擎（如Federated、Archive等）可能有特殊的限制或要求。在使用这些引擎时，请查阅相关的文档。

  6. 复制和分区：如果你的MySQL服务器配置了复制或使用了分区表，重命名表时可能需要额外的注意。例如，在复制环境中，你需要确保所有相关的从服务器都应用了相应的更改。

通过正确地使用RENAME TABLE语句，你可以安全地重命名MySQL数据库中的表。但请务必谨慎操作，并在执行前做好充分的准备和评估。

总结

通过掌握上述操作技巧，我们可以更加灵活和高效地管理MySQL数据库中的表。无论是修改表结构、重命名表、删除表还是清空表，都可以根据实际需求选择合适的操作方式。同时，务必注意在执行删除或清空操作前备份重要数据，以防止数据丢失。


http://www.kler.cn/a/329335.html

相关文章：

  • MySQL安装及数据库基础
  • 25.UE5时间膨胀，慢动作，切换地图，刷BOSS
  • K8s 一键部署 MongoDB 的 Replica-Set 和 MongoDB-Express
  • 大学作业参考:网页设计作业 - 工作计划-Java SQL HTML源码下载
  • 39.十进制数转化为二进制数 C语言
  • 小试牛刀-Anchor安装和基础测试
  • 前端学习第一天笔记 HTML5 CSS初学以及VSCODE中的常用快捷键
  • 基于AutoDL复现Nice-slam
  • C++入门基础 (超详解)
  • Thinkphp/Laravel基于vue的实验室上机管理系统
  • 基于Python的屏幕录制转GIF工具
  • VisionPro - 基础 - 模板匹配技术-应用3 - Search\PMAline\PatMax\Alignment Guidelines
  • 使用VBA快速生成Excel工作表非连续列图片快照
  • 二、创建drf纯净项目
  • LeetCode 152. 乘积最大子数组
  • TIM（Timer）定时器的原理
  • 深入浅出SpringBoot框架
  • Python 在区块链智能合约开发中的应用与实践
  • 土地规划与区域经济发展：筑基均衡未来的战略经纬
  • MongoDB 工具包安装（mongodb-database-tools）
  • （27）oracle镜像启动
  • 【更新】红色文化之红色博物馆数据集（经纬度+地址）
  • 用Promise实现前端并发请求
  • Win10鼠标总是频繁自动失去焦点-非常有效-重启之后立竿见影
  • Bigemap Pro首发（一款真正全面替代Arcgis的国产基础软件）
  • Linux Mint急救模式