Shulou(Shulou.com)06/01 Report--

Editor to share with you the method of mysql emptying table and deletion table. I hope you will gain a lot after reading this article. Let's discuss it together.

The method of emptying a table in mysql: using the "TRUNCATE table name" statement, you can completely empty a table; the method of deleting a table: use the "DROP TABLE table name 1 [, table name 2, table name 3.];" statement.

Mysql emptying table

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

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

TRUNCATE [TABLE] Table name

The TABLE keyword can be omitted.

Example

Create the new table tb_student_course, insert the data and query it. The SQL statement and run result are as follows:

Mysql > CREATE TABLE `tb_student_ room` (- > `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: 0mysql > 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, and the SQL statement and run 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 deletion table

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

When 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 data table before deleting it, so as to avoid irreparable losses.

Basic grammar

Use the DROP TABLE statement to delete one or more data tables in the following syntax format:

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

The syntax format is described as follows:

Table name 1, table name 2, table name 3. Represents 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 after each other and separated by commas.

IF EXISTS is used to determine whether a data table exists before it is deleted. Without IF EXISTS, MySQL will prompt an error when the data table does not exist and interrupt the execution of the SQL statement; with IF EXISTS, the SQL statement can be executed smoothly when the data table does not exist, but will issue a warning (warning).

Two points to note:

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

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

Example

Select the database test_db, create the tb_emp3 data table, enter the SQL statement and run the results as shown below.

Mysql > USE test_db;Database changedmysql > 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)

From the running results, we can see that there are two data tables, tb_emp2 and tb_emp3, in test_tb database.

Let's delete the data table tb_emp3. The SQL statement entered and the run 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 can be seen from the execution result, the table named tb_emp3 no longer exists in the datasheet list of the test_db database, and the delete operation was successful.

After reading this article, I believe you have a certain understanding of the methods of mysql emptying table and deletion list. If you want to know more about it, welcome to follow the industry information channel. Thank you for your reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.