Create temporary table in MySQL

Answer:

Temporary table is very useful when you need some tables for temporary storage, and they will be automatically removed when you disconnect from the MySQL server.

Steps:

1. Create a temporary table

mysql> CREATE TEMPORARY TABLE test (id int); 

2. Insert some values

mysql> INSERT INTO test VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test VALUES (2);
Query OK, 1 row affected (0.00 sec)

3. Query the table

mysql> SELECT * FROM test

+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

4. Close the connection and the table will be automatically removed.

How to rename database in MySQL

Answer:

If you are using MyISAM storage engine, the easiest way is to take down the MySQL server and rename the database folder name.

Else, you can use the mysqldump tool, steps as following:

1. Export the database using mysqldump tool

# mysqldump -u root -p old_db > old_db.sql

2. Create a new database

# mysql -u root -p -e 'create database new_db'

3. Finally import into the new database

# cat old_db.sql |mysql -u root -p new_db