How to query temporary table in mysql

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

mysql temporary table

In MySQL, a temporary table is a special type of table that allows you to store temporary result sets. You can Reuse multiple times in a single session.

Temporary tables are very convenient when querying data that requires a single SELECT statement with a JOIN clause is impossible or very time-consuming. In this case you can use a temporary table to store the immediate result and use another query to process it.

How does mysql query a temporary table

Querying data from a temporary table is like querying Yiyang from an ordinary table, just use the SELECT statement.

SELECT *|字段名列表 FROM 临时表名
[WHERE子句]
[GROUP BY子句]
[ORDER BY子句]
[LIMIT子句]

• *|Field name list: Use the "*" wildcard character to view all field data, and "field name list" to view specified one or more field data.

• WHERE clause: It is optional. If this item is selected, the query data must meet the query conditions.

• GROUP BY clause: It is optional. This clause tells MySQL how to display the queried data and group it according to the specified fields.

• ORDER BY clause: It is optional. This clause tells MySQL in what order to display the queried data. The sorting that can be done is ascending order (ASC) and descending order (DESC). ), which is ascending by default.

• LIMIT clause: It is optional. This clause tells MySQL to display the number of data items queried each time.

mysql temporary table example:

Create a temporary table to store the top 10 customers by revenue:

CREATE TEMPORARY TABLE top10customers
SELECT p.customerNumber, 
       c.customerName, 
       ROUND(SUM(p.amount),2) sales
FROM payments p
INNER JOIN customers c ON c.customerNumber = p.customerNumber
GROUP BY p.customerNumber
ORDER BY sales DESC
LIMIT 10;

Use SELECT statement query top10customersData in temporary table

SELECT 
    customerNumber, 
    customerName, 
    sales
FROM
    top10customers
ORDER BY sales;

Output results:

##[Related recommendations:

mysql video tutorial 】

The above is the detailed content of How to query temporary table in mysql. For more information, please follow other related articles on the PHP Chinese website!