sql复制表结构和数据_SQL复制表

sql复制表结构和数据

In real time we do face situations where we need to copy data from one table to another table or when we need to create a table from a set of data from the table. We will discuss in this article the solution for such cases.

在实时情况下，我们确实遇到需要将数据从一个表复制到另一个表，或者需要根据表中的一组数据创建表的情况。 我们将在本文中讨论此类情况的解决方案。

SQL复制表 (SQL Copy Table)

Creating a backup table is very important in situations where we are dealing with sensitive data. In such situation copying data from one table and creating a new table using the same data is very useful. SQL Copy table is a feature that allows us to perform the copying of data from one table to another.

在我们处理敏感数据的情况下，创建备份表非常重要。 在这种情况下，从一个表中复制数据并使用相同的数据创建新表非常有用。 SQL复制表是一项功能，使我们能够将数据从一个表复制到另一个表。

We will see how Copy table feature can be used in below mentioned three databases.

我们将看到如何在下面提到的三个数据库中使用“复制表”功能。

1. MySQL Copy Table
   MySQL复制表
2. PostgreSQL Copy Table
   PostgreSQL复制表
3. SQL Server Copy Table
   SQL Server复制表

1. MySQL复制表 (1. MySQL Copy Table)

Syntax:- 

句法：-

Create table newTable

Select column(s) from existingTable;

In the above syntax, first, a table is created with the newTable name then the structure of the new table is defined by the result set of the select statement.

在上面的语法中，首先使用newTable名称创建一个表，然后通过select语句的结果集定义新表的结构。

Let us assume the Library table for example.

让我们假设图书馆表为例。

CREATE TABLE `library` (
`idLibrary` int(11) NOT NULL,
`BookTitle` varchar(45) DEFAULT NULL,
`BookQuantity` int(11) DEFAULT NULL,
`Author` varchar(45) DEFAULT NULL,
`BookPrice` float DEFAULT NULL,
PRIMARY KEY (`idLibrary`),
UNIQUE KEY `idLibrary_UNIQUE` (`idLibrary`)
)

The below-mentioned query will be used for data insertion.

以下查询将用于数据插入。

INSERT INTO `test`.`library`(`idLibrary`,`BookTitle`,`BookQuantity`,`Author`,`BookPrice`)VALUES(1,'The Chamber of Secrets',10,'J K Rowling',20.99);
INSERT INTO `test`.`library`(`idLibrary`,`BookTitle`,`BookQuantity`,`Author`,`BookPrice`)VALUES(2,'One night at the call center',13,'Chetan Bhagat',100.99);
INSERT INTO `test`.`library`(`idLibrary`,`BookTitle`,`BookQuantity`,`Author`,`BookPrice`)VALUES(3,'The God of Small things',11,'Arundhati Roy',120.99);
INSERT INTO `test`.`library`(`idLibrary`,`BookTitle`,`BookQuantity`,`Author`,`BookPrice`)VALUES(4,'War and Peace',5,'Leo Tolstoy',80.00);

SQL Table Before Copy

复制前SQL表

Now we will try to create a table with a price of more than 100.

现在，我们将尝试创建价格超过100的表。

Create table library_moreThan100
Select * from library where bookprice>100;

Let us check the newly created table.

让我们检查新创建的表。

Select * from library_moreThan100

SQL New Table After Copy

复制后SQL新表

If we want to only insert copied data in a table then the following query can be used.

如果我们只想在表中插入复制的数据，则可以使用以下查询。

INSERT newTable
SELECT *
FROM existingTable;

2. PostgreSQL复制表 (2. PostgreSQL Copy Table)

Syntax:- 

句法：-

Create table newTable

Select column(s) from existingTable;

The syntax for MySQL and PostgreSQL is the same for SQL Copy command.

MySQL和PostgreSQL语法与SQL Copy命令相同。

Let us assume the Library table for example.

让我们假设图书馆表为例。

CREATE TABLE "library" (
  "idLibrary" int NOT NULL,
  "BookTitle" varchar(45) DEFAULT NULL,
  "BookQuantity" int DEFAULT NULL,
  "Author" varchar(45) DEFAULT NULL,
  "BookPrice" float DEFAULT NULL,
  PRIMARY KEY ("idLibrary"),
  Constraint "idLibrary_UNIQUE"  UNIQUE ("idLibrary")
)

The below-mentioned query will be used for data insertion.

以下查询将用于数据插入。

INSERT INTO "test"."library"("idLibrary","BookTitle","BookQuantity","Author","BookPrice")VALUES(1,'The Chamber of Secrets',10,'J K Rowling',20.99);
INSERT INTO "test"."library"("idLibrary","BookTitle","BookQuantity","Author","BookPrice")VALUES(2,'One night at the call center',13,'Chetan Bhagat',100.99);
INSERT INTO "test"."library"("idLibrary","BookTitle","BookQuantity","Author","BookPrice")VALUES(3,'The God of Small things',11,'Arundhati Roy',120.99);
INSERT INTO "test"."library"("idLibrary","BookTitle","BookQuantity","Author","BookPrice")VALUES(4,'War and Peace',5,'Leo Tolstoy',80.00);

PostgreSQL Table Before Copy

复制前的PostgreSQL表

Now we will try to create a backup table from the library table.

现在，我们将尝试从库表创建备份表。

CREATE TABLE test.library_bk AS 
TABLE test.library;

Let us check the newly created table.

让我们检查新创建的表。

Select * from test.library_bk;

PostgreSQL New Table After Copy

复制后的PostgreSQL新表

3. SQL Server复制表 (3. SQL Server Copy Table)

Syntax:- 

句法：-

Select * into newTable from existingTable;

Based on the syntax above, SQL server will create a new table with the name as newTable and will use the structure of the existingTable.

根据上述语法，SQL Server将创建一个名为newTable的新表，并将使用现有表的结构。

Let us assume the Library table for example.

让我们假设图书馆表为例。

CREATE TABLE "library" (
  "idLibrary" int NOT NULL,
  "BookTitle" varchar(45) DEFAULT NULL,
  "BookQuantity" int DEFAULT NULL,
  "Author" varchar(45) DEFAULT NULL,
  "BookPrice" float DEFAULT NULL,
  PRIMARY KEY ("idLibrary"),
  Constraint "idLibrary_UNIQUE"  UNIQUE ("idLibrary")
)

The below-mentioned query will be used for data insertion.

以下查询将用于数据插入。

INSERT INTO "library"("idLibrary","BookTitle","BookQuantity","Author","BookPrice")VALUES(1,'The Chamber of Secrets',10,'J K Rowling',20.99);
INSERT INTO "library"("idLibrary","BookTitle","BookQuantity","Author","BookPrice")VALUES(2,'One night at the call center',13,'Chetan Bhagat',100.99);
INSERT INTO "library"("idLibrary","BookTitle","BookQuantity","Author","BookPrice")VALUES(3,'The God of Small things',11,'Arundhati Roy',120.99);
INSERT INTO "library"("idLibrary","BookTitle","BookQuantity","Author","BookPrice")VALUES(4,'War and Peace',5,'Leo Tolstoy',80.00);

SQL Server Table Before Copy

复制前SQL Server表

Now we will try to create a backup table from the library table.

现在，我们将尝试从库表创建备份表。

Select * into library_bk from library;

Let us check the newly created table.

让我们检查新创建的表。

Select * from library_bk;

SQL Server New Table After Copy

复制后SQL Server新表

翻译自: https://www.journaldev.com/28517/sql-copy-table

sql复制表结构和数据