SQL Temporary Tables

0
23

sqltuts

The temporary tables could be exceptionally valuable at times to keep transitory information. An essential thing that ought to be known for impermanent tables is that they will be erased when the present customer session ends.

 
Brief tables were included MySQL from 3.23. On the off chance that you utilize a more seasoned adaptation of MySQL than 3.23, you can’t utilize transitory tables, however, you can utilize load tables.
 

As expressed before, provisional tables will just keep going the length of the session is alive. On the off chance that you run the code in a PHP script, the makeshift table will be devastated naturally when the script gets done with executing. On the off chance that you are associated with the MySQL database server through the MySQL customer project, then the provisional table will exist until you close the customer or physically crush the table.

Example:

Here is an example showing you usage of the temporary table.

Same code can be applied in  PHP scripts using mysql_query() function as it works same in both the cases:-

  1. mysql> CREATE TEMPORARY TABLE FanListings (
  2.     -> product_Name VARCHAR(50) NOT NULL
  3.     -> product ID VARCHAR(25)   NOT NULL
  4.     ->  total_sales DECIMAL(10,1) NOT NULL
  5.     ->  avg_unit_price DECIMAL(4,1) NOT NULL DEFAULT 0.00
  6.     -> total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
  7. );
  8. Query OK, 0 rows affected (0.00 sec)
  9.  
  10. mysql> INSERT INTO FanListings
  11.     -> (product_name, total_sales, avg_unit_price, total_units_sold)
  12.     -> VALUES
  13.     -> ('tata', 2598.2, 60, 13);
  14.  
  15. mysql> SELECT * FROM FanListings;
  16. +--------------+-------------+----------------+------------------+
  17. | product_name | total_sales | avg_unit_price | total_units_sold |
  18. +--------------+-------------+----------------+------------------+
  19. | tata    |      2598.2 |          60.00 |                13 |
  20. +--------------+-------------+----------------+------------------+
  21. 1 row in set (0.00 sec)

 

Dropping Temporary Tables:

 

By default, all the temporary tables are deleted by MySQL when your database connection gets terminated. You can do it manually by issuing DROP TABLE command.

Following is the example on dropping a temporary table:

 

  1. mysql> CREATE TEMPORARY TABLE FanListings (
  2.     -> product_name VARCHAR(50) NOT NULL
  3.      -> product ID VARCHAR(25)   NOT NULL
  4.     ->  total_sales DECIMAL(10,1) NOT NULL
  5.     ->  avg_unit_price DECIMAL(4,1) NOT NULL DEFAULT 0.00
  6.     -> total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
  7. );
  8. Query OK, 0 rows affected (0.00 sec)
  9.  
  10. mysql> INSERT INTO FanListings;
  11.     -> (product_name, total_sales, avg_unit_price, total_units_sold)
  12.     -> VALUES
  13.     -> ('tata', 2598.2, 60, 13);
  14.  
  15. mysql> SELECT *
  16. +--------------+-------------+----------------+------------------+
  17. | product_name | total_sales | avg_unit_price | total_units_sold |
  18. +--------------+-------------+----------------+------------------+
  19. | cucumber     |      100.25 |          90.00 |                2 |
  20. +--------------+-------------+----------------+------------------+
  21. 1 row in set (0.00 sec)
  22. mysql> DROP TABLE FanListings;
  23. mysql>  SELECT * FROM FanListings;
  24. ERROR 1146: Table 'TUTORIALS. FanListings ' doesn't exist
Facebook Comments
(Visited 33 times, 1 visits today)

LEAVE A REPLY

Please enter your comment!
Please enter your name here