What are MySQL Temporary Tables? How can we create them?

MySQLMySQLi Database

As the name suggests, Temporary tables are the tables in which we can keep our temporary data. The most important thing about temporary tables is that they will be deleted when the current client session terminates. It can be created with the help of the CREATE statement but we must have to use the keyword ‘Temporary’ while creating it. To illustrate the creation of a temporary table we are using the following example −

Example

mysql> CREATE TEMPORARY TABLE SalesSummary (
    -> product_name VARCHAR(50) NOT NULL
    -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
    -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
    -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO SalesSummary
    -> (product_name, total_sales, avg_unit_price, total_units_sold)
    -> VALUES
    -> ('cucumber', 100.25, 90, 2);

mysql> SELECT * FROM SalesSummary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber     |    100.25   |       90.00    |        2         |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)

The above queries have created and inserted the values into a temporary table named ‘SalesSummary’.

raja
Published on 22-Feb-2018 14:04:11
Advertisements