In real time scenario, consider that you have read-only access on a table and you have to manipulate some data in it. It is always useful to create a temp table when you want to use the table in the current session.
In this tutorial, we will discuss the temporary table in MySQL. Following are some features of the temporary table.
- MySQL removes a temporary table when we close the session or if we close the connection.
- A temporary table is only visible to the client who has created it.
- A temporary table can share the same name as a normal table.
- Two temporary tables can also have the same name if there are in different sessions.
- If the temporary table and the existing normal table shares the same name. The normal table becomes inaccessible until the temporary table is not removed.
We will now consider creation, use and removal of a temporary table in MySQL.
Create a Temporary Table
Syntax for Create Temporary table: –
CREATE TEMPORARY TABLE table_name SELECT column(s) FROM existing_table;
In the above syntax, due to the keyword Temporary, a temporary table gets created.
Let us create a Library table and we will use the same table for the creation of a temporary table.
CREATE TABLE `library` ( `idLibrary` int(11) NOT NULL, `BookTitle` varchar(45) DEFAULT NULL, `BookQuantity` int(11) DEFAULT NULL, `Author` varchar(45) DEFAULT NULL, `BookPrice` float DEFAULT NULL, PRIMARY KEY (`idLibrary`), UNIQUE KEY `idLibrary_UNIQUE` (`idLibrary`) )
The below-mentioned query will be used for data insertion.
INSERT INTO `test`.`library`(`idLibrary`,`BookTitle`,`BookQuantity`,`Author`,`BookPrice`)VALUES(1,'The Chamber of Secrets',10,'J K Rowling',20.99); INSERT INTO `test`.`library`(`idLibrary`,`BookTitle`,`BookQuantity`,`Author`,`BookPrice`)VALUES(2,'One night at the call center',13,'Chetan Bhagat',100.99); INSERT INTO `test`.`library`(`idLibrary`,`BookTitle`,`BookQuantity`,`Author`,`BookPrice`)VALUES(3,'The God of Small things',11,'Arundhati Roy',120.99); INSERT INTO `test`.`library`(`idLibrary`,`BookTitle`,`BookQuantity`,`Author`,`BookPrice`)VALUES(4,'War and Peace',5,'Leo Tolstoy',80.00);
We will create a temporary table with book quantity of more than 10.
Create temporary table libary_10 select * from library where bookquantity>10;
Now we will use Select statement for check if the table is created.
Select * from libary_10;
Usage of Temporary Table
Now we have the table created, assume we would like to get the highest book price. We will use the below mentioned query to get the result set.
Select max(bookprice) from libary_10;
Now we will understand how to remove a temporary table.
Remove Temporary Table
Syntax for Drop Temporary Table:
Drop Temporary Table temp_table_name;
The Temporary keyword is used to make sure that by mistake the permanent table is not deleted. If we try to remove a permanent table using the above-mentioned syntax, we will get an error.
Let us see how to drop the temporary table.
Drop Temporary Table libary_10;
That’s all for SQL Temporary table;