本文共 3682 字,大约阅读时间需要 12 分钟。
sql临时表
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中的临时表。 以下是临时表的一些功能。
We will now consider creation, use and removal of a temporary table in MySQL.
现在,我们将考虑在MySQL中创建,使用和删除临时表。
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.
在上述语法中,由于关键字Temporary,将创建一个临时表。
Let us create a Library table and we will use the same table for the creation of a temporary table.
让我们创建一个Library表,并将使用相同的表创建一个临时表。
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.
我们将创建一个图书数量大于10的临时表。
Create temporary table libary_10 select * from library where bookquantity>10;
Now we will use statement for check if the table is created.
现在,我们将使用语句检查表是否已创建。
Select * from libary_10;
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.
现在,我们将了解如何删除临时表。
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.
Temporary关键字用于确保错误地删除了永久表。 如果我们尝试使用上述语法删除永久表,则会收到错误消息。
Let us see how to drop the temporary table.
让我们看看如何删除临时表。
Drop Temporary Table libary_10;
That’s all for SQL Temporary table;
这就是SQL临时表的全部内容;
翻译自:
sql临时表
转载地址:http://cnlzd.baihongyu.com/