Tables temporaires MySQL et données de session

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 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

Connect to MySQL and Create a Temporary Table

In this step, you will connect to the MySQL server and create your first temporary table. Temporary tables are special tables that exist only for the duration of a single client session. They are automatically dropped when the session ends. This makes them very useful for storing intermediate results or data that is only needed temporarily.

First, open a terminal in the LabEx VM. You can connect to the MySQL server as the root user using the following command. You will be prompted for the root password, which is password.

sudo mysql -u root -p

After entering the password, you should see the MySQL prompt: mysql>. This indicates that you are successfully connected to the MySQL server.

Now, let's create a temporary table named temp_employees. This table will store some temporary employee data. The syntax for creating a temporary table is similar to creating a regular table, but you include 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: An integer that will automatically increment for each new row and serves as the primary key.
• name: A string (up to 255 characters) to store the employee's name.
• department: A string (up to 255 characters) to store the employee's department.

To confirm that the temporary table was created successfully within your current session, you can use the DESCRIBE command.

DESCRIBE temp_employees;

The output should display the structure of the temp_employees table, showing the columns you defined:

+------------+--------------+------+-----+---------+----------------+
| 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 temp_employees temporary table is available in your current MySQL session.

Insert and Query Data in the Temporary Table

Now that you have created the temporary table temp_employees, you can insert data into it and query the data, just like you would with a regular table. This step will demonstrate how to populate the temporary table and retrieve information from it.

You should still be connected to the MySQL server from the previous step, with the mysql> prompt visible.

Let's insert a few rows of data into the temp_employees table using the INSERT INTO statement.

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 add three records to your temporary table. Each record includes a name and a department.

To see the data you just inserted, use the SELECT statement to query the temp_employees table.

SELECT * FROM temp_employees;

This command retrieves all columns (*) from all rows in the temp_employees table. The output should show the data you inserted:

+----+---------------+------------+
| 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 a WHERE clause to filter the results. For example, to find employees in the 'IT' department:

SELECT name FROM temp_employees WHERE department = 'IT';

The output will show only the name of the employee in the IT department:

+---------------+
| name          |
+---------------+
| Charlie Brown |
+---------------+
1 row in set (0.00 sec)

This demonstrates that you can perform standard data manipulation and querying operations on temporary tables within your session.

Understand Temporary Table Scope

A key characteristic of temporary tables is their session-specific scope. This means a temporary table created in one MySQL session is not visible or accessible in another session, even if both sessions are connected to the same database. This step will demonstrate this behavior.

Keep your current MySQL session open in the first terminal window.

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, just like you did in Step 1:

sudo mysql -u root -p

Enter the password password when prompted. You now have two separate terminal windows, each with its own independent MySQL session.

In this new session, try to query the temp_employees table that you created in the first session:

SELECT * FROM temp_employees;

You will receive an error message similar to this:

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

This error clearly indicates that the temp_employees table is not found in this new session. This is because temporary tables are isolated to the session in which they were created.

Now, try to describe the table in this new session:

DESCRIBE temp_employees;

You will get the same error:

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

This confirms that the temporary table is completely invisible outside of its originating session.

You can close this new terminal window now. Return to the original terminal where you created and populated the temp_employees table.

Drop the Temporary Table and End the Session

Temporary tables are automatically dropped when the MySQL session ends. However, you can also explicitly drop a temporary table using the DROP TEMPORARY TABLE statement. This is useful if you are finished with the temporary table before your session ends and want to free up resources or avoid potential naming conflicts.

You should be back in the original terminal window where you created and used the temp_employees table.

To explicitly drop the temporary table, use the following command:

DROP TEMPORARY TABLE temp_employees;

This command removes the temp_employees table from your current session.

To confirm that the table has been dropped, try to query it again:

SELECT * FROM temp_employees;

You should receive the "Table doesn't exist" error, confirming that the temporary table is no longer available in this session:

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

Finally, you can exit the MySQL monitor and end your session by typing:

exit

This will close the connection to the MySQL server and return you to the regular terminal prompt. Since the session has ended, the temporary table would have been dropped automatically even if you hadn't explicitly dropped it.

Summary

In this lab, you learned how to work with temporary tables in MySQL. You connected to the MySQL server and created a temporary table named temp_employees using the CREATE TEMPORARY TABLE statement. You then inserted data into this table and queried it using standard SQL commands like INSERT INTO and SELECT.

You also explored the scope of temporary tables, understanding that they are session-specific and not accessible from other MySQL sessions. Finally, you learned how to explicitly drop a temporary table using the DROP TEMPORARY TABLE statement and confirmed that temporary tables are automatically dropped when the session ends. Temporary tables are a valuable tool for managing session-specific data and intermediate results in MySQL.