Sql 简明教程

SQL - Clone Tables

有时，您可能需要同列、属性、索引、默认值等的表的精确副本。无需花费时间创建现有表的完全相同版本，您可以创建现有表的克隆。

There may be a situation when you need an exact copy of a table with the same columns, attributes, indexes, default values and so forth. Instead of spending time on creating the exact same version of an existing table, you can create a clone of the existing table.

SQL Cloning Operation 允许创建现有表的精确副本及其定义。使用 SQL 在各种 RDBMS 中可进行三种类型的克隆；列举如下——

SQL Cloning Operation allows to create the exact copy of an existing table along with its definition. There are three types of cloning possible using SQL in various RDBMS; they are listed below −

Simple Cloning
Shallow Cloning
Deep Cloning

Simple Cloning in MySQL

简单的克隆操作从现有表创建一个新的副本表，并将所有记录复制到新创建的表中。要分解此过程，使用 CREATE TABLE 语句创建新表；并将现有表中的数据，作为 SELECT 语句的结果，复制到新表中。

Simple cloning operation creates a new replica table from the existing table and copies all the records in newly created table. To break this process down, a new table is created using the CREATE TABLE statement; and the data from the existing table, as a result of SELECT statement, is copied into the new table.

在此处，克隆表仅从原始表继承基本列定义，例如 NULL 设置和默认值。它不会继承索引和 AUTO_INCREMENT 定义。

Here, clone table inherits only the basic column definitions like the NULL settings and default values from the original table. It does not inherit the indices and AUTO_INCREMENT definitions.

Syntax

以下是在 MySQL 中执行简单克隆的基本语法——

Following is the basic syntax to perform simple cloning in MySQL−

CREATE TABLE new_table SELECT * FROM original_table;

Example

考虑以下存在的 CUSTOMERS 表，它将在接下来的几个新步骤中被克隆。

Consider the following existing CUSTOMERS table which will be cloned in next new few steps.

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 创建 NEW_CUSTOMERS 表。

Now let’s use the following SQL statement to create NEW_CUSTOMERS table using the existing table CUSTOMERS.

CREATE TABLE NEW_CUSTOMERS SELECT * FROM CUSTOMERS;

Output

输出显示为 -

The output is displayed as −

Query OK, 7 rows affected (0.06 sec)
Records: 7  Duplicates: 0  Warnings: 0

Verification

为了验证该表是否成功克隆，我们可以使用以下 SELECT 查询 -

To verify whether the table has been cloned successfully, we can use the following SELECT query −

SELECT * FROM NEW_CUSTOMERS;

如果 NEW_CUSTOMERS 表成功创建，那么它应该获得 CUSTOMERS 表中可用的所有记录。

If NEW_CUSTOMERS table is created successfully, then it should get all the records which are available in CUSTOMERS table.

Shallow Cloning in MySQL

浅克隆操作从现有表创建一个新的副本表，但是不会将任何数据记录复制到新创建的表中，因此仅创建一个新的空表。

Shallow cloning operation creates a new replica table from the existing table but does not copy any data records into newly created table, so only new but empty table is created.

在此，克隆表仅包含原始表的结构以及列属性（包括索引和 AUTO_INCREMENT 定义）。

Here, the clone table contains only the structure of the original table along with the column attributes including indices and AUTO_INCREMENT definition..

Syntax

以下是在 MySQL RDBMS 中执行浅克隆的基本语法 -

Following is the basic syntax to perform shallow cloning in MySQL RDBMS −

CREATE TABLE new_table LIKE original_table;

Example

以下是创建现有表 CUSTOMERS 的浅克隆副本的示例。

Following is an example to create a shallow clone copy of the existing table CUSTOMERS.

CREATE TABLE SHALL_CUSTOMERS LIKE CUSTOMERS;

Output

输出显示为 -

The output is displayed as −

Query OK, 0 rows affected (0.06 sec)

Verification

为了验证该表是否成功克隆，我们可以使用以下 DESC table_name 查询 -

To verify whether the table has been cloned successfully, we can use the following DESC table_name query −

DESC SHALL_CUSTOMERS;

这将显示 SHALL_CUSTOMERS 表（该表只是 CUSTOMERS 表的副本）的以下信息 -

This will display the following information about the SHALL_CUSTOMERS table which is just a replica of CUSTOMERS table −

Field

Type

Null

Key

Default

Extra

int(11)

PRI

NULL

NAME

varchar(20)

NULL

AGE

int(11)

NULL

ADDRESS

char(25)

YES

NULL

SALARY

decimal(18,2)

YES

NULL

Deep Cloning in MySQL

深克隆操作是简单克隆和浅克隆的组合。它不仅复制现有表的结构，还将其数据复制到新创建的表中。因此，新表将具有现有表中的所有内容和所有属性（包括索引和 AUTO_INCREMENT 定义）。

Deep cloning operation is a combination of simple cloning and shallow cloning. It not only copies the structure of the existing table but also its data into the newly created table. Hence, the new table will have all the contents from existing table and all the attributes including indices and the AUTO_INCREMENT definitions.

由于它是浅克隆和简单克隆的组合，因此此类克隆将具有两个需要执行的不同查询：一个带有 CREATE TABLE 语句，另一个带有 INSERT INTO 语句。CREATE TABLE 语句将通过包括表的所有属性，创建新表；INSERT INTO 语句将数据从现有表插入到新表中。

Since it is a combination of shallow and simple cloning, this type of cloning will have two different queries to be executed: one with CREATE TABLE statement and one with INSERT INTO statement. The CREATE TABLE statement will create the new table by including all the attributes of existing table; and INSERT INTO statement will insert the data from existing table into new table.

Syntax

以下是在 MySQL RDBMS 中执行深克隆的基本语法 -

Following is the basic syntax to perform deep cloning in MySQL RDBMS −

CREATE TABLE new_table LIKE original_table;
INSERT INTO new_table SELECT * FROM original_table;

Example

以下是创建现有表 CUSTOMERS 的深克隆副本的示例。第一步是创建现有表的浅克隆。

Following is an example to create a deep clone copy of the existing table CUSTOMERS. First step is to create a shallow clone of the existing table.

CREATE TABLE DEEP_CUSTOMERS LIKE CUSTOMERS;

输出显示为 -

The output is displayed as −

Query OK, 0 rows affected (0.06 sec)

现在，第二步是从 CUSTOMERS 表将所有记录复制到 DEEP_CUSTOMERS。

Now second step is to copy all the records from the CUSTOMERS table to DEEP_CUSTOMERS.

INSERT INTO DEEP_CUSTOMERS SELECT * FROM CUSTOMERS;

Output

输出显示为 -

The output is displayed as −

Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

Verification

为了验证该表是否成功克隆，我们可以使用以下 SELECT 查询 -

To verify whether the table has been cloned successfully, we can use the following SELECT query −

SELECT * FROM DEEP_CUSTOMERS;

如果 DEEP_CUSTOMERS 表成功克隆，那么它应该获得 CUSTOMERS 表中可用的所有记录。

If DEEP_CUSTOMERS table is cloned successfully, then it should get all the records which are available in CUSTOMERS.

Table Cloning in SQL Server

但是，没有直接的方法在 SQL 服务器中完全克隆一个表。但是，我们有一些解决方法来处理这种情况。

However, there is no direct way to fully clone a table in an SQL server. However, we have some work around to handle the situation.

SELECT…INTO STATEMENT

MS SQL 服务器可以使用 SELECT…INTO 语句来创建一个新表，并将现有表中的数据复制到其中。但是，此命令仅复制数据，而不复制其定义，因此，如果存在，则会省略约束、索引等。如果希望新表具有与原始表完全相同的结构，则需要分别对其进行添加。

MS SQL Server can make use of the SELECT…INTO statement to create a new table and copies the data from an existing table into it. However, this command only copies the data and not the definition of it, thus, omitting constraints, indexes etc., if any. They need to be added separately if one wishes to have the exact same structure of the original table in their new table.

Syntax

以下是 SELECT…INTO 语句的基本语法：

Following is the basic syntax of the SELECT…INTO statement −

SELECT * INTO new_table FROM original_table;

上述 SQL 命令将使用 new_table 的结构创建一个表，然后它将从 original_table 复制所有数据到 new_table。

The above SQL command will create a table new_table using the structure of original_table and then it will copy all the data from original_table to new_table.

Example

考虑将在本节中克隆的现有 CUSTOMERS 表。

Consider the following existing CUSTOMERS table which will be cloned in this section.

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

现在，如果您想将此表的克隆数据复制到新表 NEW_CUSTOMERS 中，那么让我们使用以下所示的 SQL 查询：

Now, if you want to clone the data of this table into a new table NEW_CUSTOMERS, let’s use the following SQL query as shown below −

SELECT * INTO NEW_CUSTOMERS FROM CUSTOMERS;

Output

输出将显示为：

The output will be displayed as −

(7 rows affected)

Verification

为了验证是否已将所有数据复制到新表 NEW_CUSTOMERS 中，我们应使用如下所示的 SQL SELECT 语句：

To verify whether all the data has been copied into the new table NEW_CUSTOMERS, we shall use the SQL SELECT statement as follows −

SELECT * FROM NEW_CUSTOMERS;

如果 NEW_CUSTOMERS 表成功创建，那么它应该获得 CUSTOMERS 表中可用的所有记录。

If NEW_CUSTOMERS table is created successfully, then it should get all the records which are available in CUSTOMERS table.

Sql 简明教程

SQL - Clone Tables

Simple Cloning in MySQL

Syntax

Example

Output

Verification

Shallow Cloning in MySQL

Syntax

Example

Output

Verification

Deep Cloning in MySQL

Syntax

Example

Output

Verification

Table Cloning in SQL Server

SELECT…​INTO STATEMENT

Syntax

Example

Output

Verification

SELECT…INTO STATEMENT