How does mysql clear and delete tables?

The method of clearing the table in mysql: use the “TRUNCATE table name” statement to completely clear a table; the method of deleting the table: use “DROP TABLE table name 1 [ , table name 2, table name 3 . ..];” statement.

mysql empty table

MySQL provides DELETE and TRUNCATE keywords to delete the data in the table.

The TRUNCATE keyword is used to completely empty a table. Its syntax format is as follows:

TRUNCATE [TABLE] table name

The TABLE keyword can be omitted.

Example

Create a new table tb_student_course, insert data and The query, SQL statement and running result are as follows:

mysql> CREATE TABLE `tb_student_course` (
     -> `id` int(4) NOT NULL AUTO_INCREMENT,
     -> `name` varchar(25) NOT NULL,
     -> PRIMARY KEY (`id`)
     -> );
 Query OK, 0 rows affected (0.04 sec)

 mysql> INSERT INTO tb_student_course(name) VALUES ('Java'),('MySQL'),('Python');
 Query OK, 3 rows affected (0.05 sec)
 Records: 3 Duplicates: 0 Warnings: 0

 mysql> SELECT * FROM tb_student_course;
 +----+--------+
 | id | name |
 +----+--------+
 | 1 | Java |
 | 2 | MySQL |
 | 3 | Python |
 +----+--------+
 3 rows in set (0.00 sec)

Use the TRUNCATE statement to clear the records in the tb_student_course table, the SQL statement and the running results are as follows:

  mysql> TRUNCATE TABLE tb_student_course;
 Query OK, 0 rows affected (0.04 sec)
 mysql> SELECT * FROM tb_student_course;
 Empty set (0.00 sec)

mysql delete table

in MySQL In the database, for data tables that are no longer needed, we can delete them from the database.

While deleting the table, the structure of the table and all the data in the table will be deleted, so it is best to back up the table before deleting the data table to avoid irreparable losses.

Basic syntax

Use the DROP TABLE statement to delete one or more data tables, the syntax is as follows:

DROP TABLE [IF EXISTS] table name 1 [ , table name 2, table name 3 ...]

The description of the syntax format is as follows:

• Table name 1, table name 2, table name 3 … indicates the name of the data table to be deleted. DROP TABLE can delete multiple tables at the same time, as long as the table names are written one after the other, separated by commas.

• IF EXISTS is used to determine whether the table exists before deleting the data table. If IF EXISTS is not added, MySQL will prompt an error and interrupt the execution of the SQL statement when the data table does not exist; after adding IF EXISTS, the SQL statement can be executed smoothly when the data table does not exist, but a warning (warning) will be issued.

Two points to note:

• The user must have the ability to execute DROP TABLE Command permission, otherwise the data table will not be deleted.

• When a table is deleted, the user’s permissions on the table are not automatically deleted.

Example

Choose the database test_db, create the tb_emp3 data table, the entered SQL statement and the running results are as follows.

mysql> USE test_db;
 Database changed
 mysql> CREATE TABLE tb_emp3
     -> (
     -> id INT(11),
     -> name VARCHAR(25),
     -> deptId INT(11),
     -> salary FLOAT
     -> );
 Query OK, 0 rows affected (0.27 sec)
 mysql> SHOW TABLES;
 +--------------------+
 |Tables_in_test_db|
 +--------------------+
 |tb_emp2|
 |tb_emp3|
 +--------------------+
 2 rows in set (0.00 sec)

It can be seen from the running results that there are two data tables, tb_emp2 and tb_emp3, in the test_tb database.

Let’s delete the data table tb_emp3, the entered SQL statement and the running results are as follows:

mysql> DROP TABLE tb_emp3;
 Query OK, 0 rows affected (0.22 sec)
 mysql> SHOW TABLES;
 +--------------------+
 |Tables_in_test_db|
 +--------------------+
 |tb_emp2|
 +--------------------+
 1 rows in set (0.00 sec)

As you can see from the execution results, the table named tb_emp3 does not exist in the data table list of the test_db database, and the deletion operation is successful.

Recommended tutorial: mysql video tutorial

The above is how to clear and delete tables in mysql? For more details, please pay attention to other related articles on 1024programmer.com!