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

news/2024/12/22 0:16:25/

【重学 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.ppmy.cn/news/1537266.html

相关文章

如何在 SQL 中创建一个新的数据库？

如何在 SQL 中创建一个新的数据库？

在SQL中创建一个新的数据库，首先你需要有一个可以执行SQL语句的环境。 这通常意味着你已经有了一个数据库管理系统（DBMS），如MySQL、PostgreSQL、Oracle或Microsoft SQL Server等。 不同的DBMS可能有不同的细节，但基本…
阅读更多...
MATLAB使用高斯消元法计算方程组的解

MATLAB使用高斯消元法计算方程组的解

function X uptrbk(A,B) % A,B是系数矩阵和列向量 % 求方阵A 含多少行(列) X是N*1列向量解 [~, N] size(A); X zeros(N,1); % C一行,N1列 C zeros(1,N1); % 增广矩阵Aug Aug [A,B]; %循环从第一列到倒数第二列 for p1:N-1[~,j] max(abs(Aug(p:N,p)));%返回每一列中的绝对…
阅读更多...
如何免费为域名申请一个企业邮箱

如何免费为域名申请一个企业邮箱

背景 做SEO的是有老是会有一些网站来做验证你的所有权，这个时候，如果你域名对应的企业邮箱就会很方便。zoho为了引导付费，有很多多余的步骤引导，反倒是让不付费的用户有些迷茫，所以会写这个教程，按照教程走…
阅读更多...
使用纯CSS和JavaScript来实现一个转盘抽奖效果

使用纯CSS和JavaScript来实现一个转盘抽奖效果

使用纯CSS和JavaScript来实现一个转盘抽奖效果,抽到机率相同 <!DOCTYPE html> <html lang"zh"><head><meta charset"UTF-8" /><meta name"viewport" content"widthdevice-width, initial-scale1.0" />&…
阅读更多...
手游和应用出海资讯：字节跳动《Lemon8》在美下载量飙升；美团海外版《Keeta》进军沙特市场

手游和应用出海资讯：字节跳动《Lemon8》在美下载量飙升；美团海外版《Keeta》进军沙特市场

NetMarvel帮助游戏和应用广告主洞察全球市场、获取行业信息&#xff0c;以下为10月第一周资讯&#xff1a; ● OpenAI Sora负责人加盟 Google DeepMind ● 字节跳动《Lemon8》登顶美国App Store排行榜 ● 消息称腾讯与Guillemot家族考虑收购育碧 ● OpenAI官宣获66亿美元融资 ●…
阅读更多...
宠物空气净化器哪个牌子吸毛好？希喂、IAM、352真实测评

宠物空气净化器哪个牌子吸毛好？希喂、IAM、352真实测评

前段时间忙活了个大工程——热门宠物空气净化器大横评&#xff0c;通过多方位的测试&#xff0c;帮助不少朋友解决了宠物空气净化器的选购难题。 但我们还不局限于此&#xff0c;在从事家电行业这些年间&#xff0c;我们还花费大价钱购置一堆产品实测体验过各类大小家电产品&a…
阅读更多...
C++语言学习要点

C++语言学习要点

C11和C20是C语言发展史上的两个重要里程碑&#xff0c;它们引入了许多新特性&#xff0c;极大地丰富了C的功能和表达能力。 C11 的一些值得关注的特性包括&#xff1a; 自动类型推导&#xff08;Auto&#xff09;&#xff1a;允许编译器自动推导变量的类型。decltype&#xf…
阅读更多...
光伏开发：一充一放和两充两放是什么意思？

光伏开发：一充一放和两充两放是什么意思？

一充一放 一充一放是指储能设备在一次充电过程中充满电&#xff0c;并在一次放电过程中将电能全部释放。这种模式的原理相对简单&#xff0c;充电时电能转化为化学能或其他形式的能量储存&#xff0c;放电时则将这些能量转化回电能供应给负载。一充一放模式适用于对储能设备充…
阅读更多...
最新文章

Copyright @ 2022~2023