How to clear and delete tables in mysql?

How to clear a table in mysql: use the "TRUNCATE table name" statement to completely clear a table; how to delete a table: use "DROP TABLE table name 1 [, table name 2, table name 3. ..];" statement.

mysql clears the table

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

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

TRUNCATE [TABLE] 表名

Among them, the TABLE keyword can be omitted.

Example

Create a new table tb_student_course, insert data and query, the SQL statement and running results 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 tb_student_course table The records, SQL statements and 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 the MySQL database, for data tables that are no longer needed , we can delete it from the database.

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

Basic syntax

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

DROP TABLE [IF EXISTS] 表名1 [ ,表名2, 表名3 ...]

The syntax format is explained 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. Just write the table names at the end and separate them with commas.

• IF EXISTS is used to determine whether the table exists before deleting it. 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, when the data table does not exist, the SQL statement can be executed smoothly, but a warning will be issued.

Two points to note:

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

• When a table is deleted, the user's permissions on the table will not be automatically deleted.

Example

Select the database test_db and create the tb_emp3 data table. The input SQL statements and 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 input SQL statement and the execution result 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 result, the name tb_emp3 no longer exists in the data table list of the test_db database. table, the delete operation was successful.

Recommended tutorial: mysql video tutorial

The above is the detailed content of How to clear and delete tables in mysql?. For more information, please follow other related articles on the PHP Chinese website!