Sql 简明教程

SQL - Rename Table

SQL 提供了两种方法来重命名 MySQL 表。在 MySQL RDBMS 中，您可以使用 SQL RENAME TABLE 或 ALTER TABLE 语句来更改表名。

SQL provides two ways to rename an MySQL table. You can use either SQL RENAME TABLE or ALTER TABLE statement to change a table name in MySQL RDBMS.

The SQL RENAME TABLE Statement

您可以使用 SQL RENAME TABLE 语句来更改 MySQL 表名。

You can change a MySQL table name using SQL RENAME TABLE statement.

Syntax

以下是 SQL RENAME TABLE 语句的语法：-

Following is the syntax of the SQL RENAME TABLE Statement −

RENAME TABLE table_name TO new_table_name;

其中， table_name 是现有表的当前名称， new_table_name 是该表的新名称。

Where, table_name is the current name of an existing table and new_table_name is the new name of the table.

Example: SQL RENAME TABLE Statement

让我们使用名为 CUSTOMERS 的表，其中包含客户的个人详细信息，包括姓名、年龄、地址和薪水等，如下所示：-

Let us create a table with the name CUSTOMERS which contains the personal details of customers including their name, age, address and salary etc. as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

现在，让我们使用 INSERT 语句将一些记录插入此表，如下所示：-

Now, let us insert few records into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

该表将按如下方式创建：

The table will be created as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

以下 SQL 查询将 CUSTOMERS 表的名称更改为 BUYERS ：-

Following SQL Query changes the name of the CUSTOMERS table to BUYERS −

RENAME TABLE CUSTOMERS to BUYERS;

Verification

更改表名后，您就可以在 SQL 查询中开始使用新表名了。

Once you change the name of a table, you can start using the new table name in your SQL queries.

SELECT * FROM BUYERS;

如果表名已更改成功，则它应列出 CUSTOMERS 表中现有的所有记录。

If table name got changed successfully, then it should list down all the records which were available in CUSTOMERS table.

The SQL ALTER TABLE Statement

ALTER TABLE 语句可用于更改或修改现有表的结构，即使用此语句，您可以添加/删除列、创建/销毁索引、更改现有列的 datatypes 、重命名列，我们甚至可以重命名此表。

The ALTER TABLE statement can be used to change or modify the structure of an existing table i.e. using this statement you can add/delete columns, create/destroy indexes, change the datatypes of the existing columns, rename the columns and, we can even rename the table.

Syntax

以下是 SQL ALTER TABLE 语句重命名现有表的语法：

Following is the syntax of the SQL ALTER TABLE statement to rename an existing table −

ALTER TABLE table_name RENAME [TO|AS] new_table_name

Example: SQL ALTER TABLE Statement

以下 SQL ALTER TABLE 语句将表名从 BUYERS 更改为 CUSTOMERS 。

Following SQL ALTER TABLE statement will change the table name from BUYERS to CUSTOMERS.

ALTER TABLE BUYERS RENAME TO CUSTOMERS;

Verification

将表名更改为 CUSTOMERS 后，您就可以在 SQL 查询中开始使用此名称。

Once you change the name of the table to CUSTOMERS, you can start using this name in your SQL queries.

SELECT * FROM CUSTOMERS;

这将产生以下结果：

This will produce the following result:

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Renaming a Table in SQL Server

没有 SQL Server 中的查询可以直接重命名表。但是，它允许您访问名为 sp_rename 的存储过程，您可以使用此存储过程重命名表。

There isn’t a query in SQL Server that can rename a table directly. However, it does give you access to a stored procedure called sp_rename that enables you to rename a table.

sp_rename 是 SQL 中一个系统存储过程（在数据库中执行任务的一组预建子程序），它可用于重命名各种数据库对象，包括表格、列、索引和约束。

The sp_rename is a system stored procedure (set of pre-built subroutines that perform tasks within the database) in SQL that can be used to rename various database objects including tables, columns, indexes, and constraints.

Syntax

以下是 SQL Server 中重命名表的语法：

Following is the basic syntax to rename a table in SQL Server −

EXEC sp_rename 'old_table_name', 'new_table_name'

在此处，您必须确保旧表名存在于数据库中并且新表名尚未存在。否则，这将发出警告。第二点要保证的是表处于解锁状态且没有涉及此表的活动事务。

Here, you must ensure that old table name is present in the database and that new table name does not already exist. Otherwise, it will issue a warning. Second important point is to make sure that the table is not locked and there is no active transaction involving this table.

Example: Renaming a Table in SQL Server

假设我们的数据库中已有 CUSTOMERS 表。现在，我们将使用以下查询将此表从 CUSTOMERS 重命名为 WORKERS ：

Assume we already have the CUSTOMERS table in our database. Now, we are going to rename this table from CUSTOMERS to WORKERS, using the following query −

EXEC sp_rename 'CUSTOMERS', 'WORKERS';

Output

获得的结果如下所示 −

The result obtained is as shown below −

Completion time: 2023-08-15T19:21:49.1144163+05:30

Verification

我们可以通过使用 SELECT 语句检索其内容来验证所做的更改是否已反映，如下所示：

We can verify whether the changes are reflected by retrieving its contents using the SELECT statement as follows −

SELECT * FROM WORKERS;

此操作将如下所列列出 WORKERS 表中的所有记录：

This will list down all the records available in WORKERS table as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

由于您已将表重命名为 WORKERS，因此如果您尝试使用旧表名来获取详细信息，这会引发错误，显示该表不存在。

Because you have renamed the table to WORKERS so if you try to get the details by using the old table name, it will throw an error showing that the table does not exist.

Rules to be followed while renaming tables

在 SQL 中重命名表时，有一些规则和最佳做法应遵循，以确保重命名过程顺利进行，且不会导致任何意外后果或问题。

When renaming tables in SQL, there are some rules and best practices that should be followed to ensure that the renaming process goes smoothly and does not cause any unintended consequences or issues.

Avoid renaming system tables − System tables are tables that are created and used by the database management system itself. Renaming these tables can cause issues with the functioning of the database system, so it is generally not recommended to rename system tables.
Update all references to the table − After renaming a table, any stored procedures, views, triggers, or other database objects that reference the table will need to be updated to use the new name of the table. Failure to update these references can result in errors or issues with the functioning of the database system.
Test thoroughly − Before renaming a table in a production environment, it is important to test the renaming process thoroughly in a development or testing environment to ensure that all references to the table have been updated correctly and that the database system continues to function as expected.
Use a consistent naming convention − It is a good practice to use a consistent naming convention for tables and other database objects to make it easier to understand and maintain the database system. If you need to rename a table, consider following the same naming convention that you have used for other tables in the database.
Backup the database − Before renaming a table, it is recommended to create a backup of the database to ensure that you have a restore point; in case anything goes wrong during the renaming process.