ITSourceCode.com

We Exist to Provide 100% Free Source Code and Tutorials

Custom Search

SQL Temporary Tables

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

Check Your Domain Ranking

Leave a Reply

Your email address will not be published. Required fields are marked *

ITSourceCode.com © 2016 Frontier Theme

Subscribe For Latest Updates

Signup for our newsletter and get notified when we publish new articles for free!