MySQL Temporary Tables and Session Data

Introduction

In this lab, you will learn how to work with temporary tables in MySQL, focusing on their creation and usage within a session. You'll begin by connecting to the MySQL server as the root user and creating a temporary table named temp_employees with columns for id, name, and department.

After creating the temporary table, you'll verify its existence by describing its structure using the DESCRIBE command. This confirms the table's creation and displays its column definitions, including data types and constraints. The lab will then guide you through inserting and querying data within the temporary table, demonstrating its utility for session-specific data manipulation. Finally, you'll explore the scope of temporary tables and learn how to explicitly drop them.

Skills Graph

Create a Temporary Table for Session Use

In this step, you will learn how to create a temporary table in MySQL. Temporary tables are useful for storing intermediate results during a session. They are automatically dropped when the session ends. This makes them ideal for tasks like data manipulation and reporting where you need a temporary workspace.

First, let's connect to the MySQL server. Open a terminal in the LabEx VM. You can use the following command to connect to the MySQL server as the root user. You'll be prompted for the root password, which is password.

mysql -u root -p

After entering the password, you should see the MySQL prompt: mysql>.

Now, let's create a temporary table. The syntax for creating a temporary table is similar to creating a regular table, but with the TEMPORARY keyword.

CREATE TEMPORARY TABLE temp_employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    department VARCHAR(255)
);

This command creates a temporary table named temp_employees with three columns: id, name, and department. The id column is an auto-incrementing primary key.

You can verify that the table has been created by using the SHOW TABLES command. However, SHOW TABLES will not show temporary tables. Instead, you can try to describe the table:

DESCRIBE temp_employees;

This command will show the structure of the temp_employees table, confirming that it has been created. The output should look similar to this:

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int          | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255) | YES  |     | NULL    |                |
| department | varchar(255) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

This confirms that the temporary table temp_employees has been successfully created within your current MySQL session.

Insert and Query Temporary Table Data

In this step, you will learn how to insert data into the temporary table you created in the previous step and then query that data. This will demonstrate how to use temporary tables for storing and retrieving information within a session.

Continuing from the previous step, you should still be connected to the MySQL server with the mysql> prompt. If you are not, reconnect using the following command:

mysql -u root -p

Enter the password password when prompted.

Now, let's insert some data into the temp_employees table. Use the INSERT INTO statement to add records.

INSERT INTO temp_employees (name, department) VALUES ('Alice Smith', 'Sales');
INSERT INTO temp_employees (name, department) VALUES ('Bob Johnson', 'Marketing');
INSERT INTO temp_employees (name, department) VALUES ('Charlie Brown', 'IT');

These commands insert three rows into the temp_employees table, with names and departments for each employee.

To verify that the data has been inserted correctly, you can query the table using the SELECT statement.

SELECT * FROM temp_employees;

This command will retrieve all columns and rows from the temp_employees table. The output should look similar to this:

+----+---------------+------------+
| id | name          | department |
+----+---------------+------------+
|  1 | Alice Smith   | Sales      |
|  2 | Bob Johnson   | Marketing  |
|  3 | Charlie Brown | IT         |
+----+---------------+------------+
3 rows in set (0.00 sec)

You can also use WHERE clause to filter the data. For example, to select only employees in the 'Marketing' department:

SELECT * FROM temp_employees WHERE department = 'Marketing';

The output should be:

+----+-------------+------------+
| id | name        | department |
+----+-------------+------------+
|  2 | Bob Johnson | Marketing  |
+----+-------------+------------+
1 row in set (0.00 sec)

This demonstrates how to insert data into a temporary table and query it using standard SQL commands. The data will only be available within your current MySQL session.

Verify Temporary Table Scope

In this step, you will verify the scope of the temporary table. Temporary tables are only accessible within the session in which they were created. To demonstrate this, you will open a new MySQL session and attempt to access the temp_employees table.

First, ensure you are still connected to the MySQL server from the previous steps. If so, keep that terminal open.

Now, open a new terminal window in the LabEx VM. You can do this by right-clicking on the desktop and selecting "Open Terminal Here".

In this new terminal, connect to the MySQL server as the root user:

mysql -u root -p

Enter the password password when prompted. You now have two separate MySQL sessions.

In this new session, try to select data from the temp_employees table:

SELECT * FROM temp_employees;

You should see an error message similar to this:

ERROR 1146 (42S02): Table 'test.temp_employees' doesn't exist

This error message confirms that the temp_employees table, created in the first session, is not visible or accessible in this new session. This is because temporary tables are session-specific.

Now, try to describe the table:

DESCRIBE temp_employees;

You will also get an error:

ERROR 1146 (42S02): Table 'test.temp_employees' doesn't exist

This further confirms that the temporary table only exists within the session where it was created. Close the new terminal window and return to the original terminal where you created the temporary table. You will use that terminal in the next step.

Drop the Temporary Table

In this step, you will explicitly drop the temporary table you created earlier. While temporary tables are automatically dropped at the end of a session, it's good practice to drop them explicitly when you are finished with them. This can help to free up resources and avoid potential naming conflicts if you need to create a table with the same name later in the same session.

Ensure you are still connected to the MySQL server in the original terminal session where you created the temp_employees table. If you are not, reconnect using the following command:

mysql -u root -p

Enter the password password when prompted.

To drop the temporary table, use the DROP TEMPORARY TABLE statement:

DROP TEMPORARY TABLE temp_employees;

This command will remove the temp_employees table from your current session.

To verify that the table has been dropped, try to select data from it again:

SELECT * FROM temp_employees;

You should see the same error message as in the previous step when you tried to access the table from a different session:

ERROR 1146 (42S02): Table 'test.temp_employees' doesn't exist

This confirms that the temporary table has been successfully dropped.

You can now exit the MySQL monitor by typing:

exit

This will disconnect you from the MySQL server.

Summary

In this lab, you learned how to create a temporary table within a MySQL session. This involves connecting to the MySQL server using the mysql -u root -p command and then using the CREATE TEMPORARY TABLE statement to define the table structure, including column names, data types, and constraints like primary keys and auto-incrementing fields.

You also learned that standard SHOW TABLES command does not display temporary tables. Instead, you can verify the creation of the temporary table by using the DESCRIBE command to view its structure and confirm the defined columns and their properties.