Mysql basics: delete databases, Delete tables, and rename tables

Mysql basics: deleting databases and tables, rename a table ============== delete a DATABASE ================ drop database is used to cancel the table used in the DATABASE and cancel the DATABASE. Be careful when using this statement! To use drop database, you need to get the database drop permission. If exists is used to prevent errors when the database does not exist. You can also use drop schema.

////////// Delete the jack and tmp databases ////////// [SQL] mysql> drop database jack; query OK, 0 rows affected (0.49 sec) mysql> drop schema tmp; Query OK, 0 rows affected (0.33 sec)

If you use drop database for a DATABASE with a symbolic link, the link and the original DATABASE are canceled. Drop database returns the number of canceled tables. This number is equivalent to the number of canceled. frm files. In normal operations, MySQL creates some files and directories. The drop database statement cancels these files and directories from the given DATABASE directory:

====================== Delete a table ========================== Syntax: DROP [TEMPORARY] TABLE [if exists] tbl_name [, tbl_name]... [RESTRICT | CASCADE]

 

Drop table is used to cancel one or more tables. You must have the DROP permission for each table. All table data and table definitions will be canceled, so be careful when using this statement! Note: For a TABLE with partitions, drop table permanently cancels TABLE definitions, cancels partitions, and removes all data stored in these partitions. Drop table also cancels the partition definition (. par) file associated with the canceled TABLE. For tables that do not exist, use if exists to prevent errors. When if exists is used, a NOTE is generated for each table that does not exist.

////////// Delete a table /////////// [SQL] mysql> drop table err_table; Query OK, 0 rows affected( 0.65 sec) ====================== rename a table ======================= [SQL] mysql> show tables; + rows + | channels | + -------------------- + | channel_table | err_table | log_table | + ---------------------- + 3 rows in set (0.00 sec) mysql> rename table err_table to err, channel_table to channel; Query OK, 0 rows affected (0.01 sec) mysql>

 

Replace the names of two tables: Syntax:

RENAME TABLE old_table TO tmp_table,             new_table TO old_table,             tmp_table TO new_table;

 

Instance:

[sql] mysql> rename table err to tmp,channel to err,tmp to channel;  Query OK, 0 rows affected (0.00 sec)  mysql>   

 

If two databases exist in the same file system, you can RENAME the TABLE of one database and move it TO another TABLE Syntax: rename table current_db.tbl_name TO other_db.tbl_name; instance:

[sql] mysql> rename table monitor_db.log_table to jack2.jack;  Query OK, 0 rows affected (0.02 sec)  

 

Note: if an error occurs during the rename process, mysql will automatically restore to the original state without worrying about data loss or errors!