SQL截断表

SQL截断表 (SQL Truncate Table)

Assume a case when we want to have the database but we would like to delete the data of the table. In such cases, TRUNCATE command is used to delete just the data from the table. The table structure will remain as it is.

假设有一种情况，当我们想拥有数据库但想删除表中的数据时。 在这种情况下，TRUNCATE命令用于仅删除表中的数据。 表结构将保持原样。

Syntax for SQL TRUNCATE

SQL TRUNCATE的语法

TRUNCATE TABLE <table_name>;

We will try to understand how the TRUNCATE command works in the following databases.

我们将尝试了解TRUNCATE命令在以下数据库中如何工作。

1. MySQL
   MySQL
2. PostgreSQL
   PostgreSQL
3. SQL Server
   SQL服务器

MySQL截断表 (MySQL Truncate Table)

Let us first create a Teacher table and we will insert data in the table as follows.

让我们首先创建一个Teacher表，然后将数据插入表中，如下所示。

Create Table: –

创建表：–

CREATE TABLE `teacher` ( `TeacherId` INT NOT NULL, `TeacherName` VARCHAR(45) NULL, `State` VARCHAR(45) NULL, `Country` VARCHAR(45) NULL, PRIMARY KEY (`TeacherId`), UNIQUE INDEX `TeacherId_UNIQUE` (`TeacherId` ASC) VISIBLE);

Insert Data: –

插入数据：–

Insert into Teacher(TeacherId,TeacherName,State,Country) VALUES (1, 'Amit','Bengaluru','India'), (2, 'Harry','Texas','US'), (3, 'John','London','UK'), (4, 'Rohit','Delhi','India');

Let us validate if the table creation and data insertion worked fine using the following query.

让我们使用以下查询验证表创建和数据插入是否工作正常。

Select * from teacher;

Table After SQL Insert

SQL插入后的表

Now we will TRUNCATE the data of the table using the following command.

现在，我们将使用以下命令截断表的数据。

TRUNCATE TABLE teacher;

MySQL Truncate Table

MySQL截断表

We will recheck if the data still exists in the table using the following query.

我们将使用以下查询重新检查表中数据是否仍然存在。

Select * from Teacher;

MySQL Table After Truncate

截断后MySQL表

PostgreSQL截断表 (PostgreSQL Truncate Table)

We will create the same table in PostgreSQL table

我们将在PostgreSQL表中创建相同的表

Create Table: –

创建表：–

CREATE TABLE public."Teacher"
(
"TeacherId" bigint,
"TeacherName" character varying,
"State" character varying,
"Country" character varying,
PRIMARY KEY ("TeacherId")
)

Insert Data: -

INSERT INTO public."Teacher"(
	"TeacherId", "TeacherName", "State", "Country")
	VALUES  (1, 'Amit','Bengaluru','India'), (2, 'Harry','Texas','US'), (3, 'John','London','UK'), (4, 'Rohit','Delhi','India');

Let us validate if the table creation and data insertion worked fine using the following query.

让我们使用以下查询验证表创建和数据插入是否工作正常。

Select * from teacher;

PostgreSQL Table After Insert

插入后的PostgreSQL表

Now we will TRUNCATE the data of the table using the following command.

现在，我们将使用以下命令截断表的数据。

TRUNCATE TABLE teacher;

PostgreSQL Truncate Table

PostgreSQL截断表

We will recheck if the data still exists in the table using the following query.

我们将使用以下查询重新检查表中数据是否仍然存在。

Select * from Teacher;

PostgreSQL Table After Truncate

截断后的PostgreSQL表

SQL Server截断表 (SQL Server Truncate Table)

We have already created the table and have inserted the data using following commands.

我们已经创建了表，并使用以下命令插入了数据。

Create Table: –

创建表：–

CREATE TABLE teacher ( TeacherId INT NOT NULL, TeacherName VARCHAR(45) NULL, State VARCHAR(45) NULL, Country VARCHAR(45) NULL, PRIMARY KEY (TeacherId));

Insert Data: –

插入数据：–

Insert into Teacher(TeacherId,TeacherName,State,Country) VALUES (1, 'Amit','Bengaluru','India'), (2, 'Harry','Texas','US'), (3, 'John','London','UK'), (4, 'Rohit','Delhi','India');

Let us validate if the table creation and data insertion worked fine using the following query.

让我们使用以下查询验证表创建和数据插入是否工作正常。

Select * from teacher;

SQL Server Table After Insert

插入后SQL Server表

Now we will TRUNCATE the data of the table using the following command.

现在，我们将使用以下命令截断表的数据。

TRUNCATE TABLE teacher;

SQL Server Truncate Table

SQL Server截断表

We will recheck the data in the table using the following query.

我们将使用以下查询重新检查表中的数据。

Select * from Teacher;

SQL Server Table After Truncate

截断后SQL Server表

翻译自: https://www.journaldev.com/28009/sql-truncate-table