Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      Temporary Tables

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

      Temporary Tables

      What are Temporary Tables?

      The Temporary Tables are the tables that are created in a database to store data temporarily. These tables will be automatically deleted once the current client session is terminated or ends. In addition to that, these tables can be deleted explicitly if the users decide to drop them manually. You can perform various SQL operations on temporary tables, just like you would with permanent tables, including CREATE, UPDATE, DELETE, INSERT, JOIN, etc.
      Temporary tables were introduced in MySQL version 3.23. If you're using an older version of MySQL that's older than 3.23, you won't be able to use temporary tables, instead you can use the Heap Tables.
      As stated earlier, temporary tables will only last as long as the session is alive. If you run the code in a PHP script, the temporary table will be destroyed automatically when the script finishes executing. If you are connected to the MySQL database server through the MySQL client program, then the temporary table will exist until you close the client or manually destroy the table.

      Creating Temporary Tables in MySQL

      Creating a temporary table in MySQL is very similar to creating a regular database table. But, instead of using CREATE TABLE, we use CREATE TEMPORARY TABLE statement.

      Syntax

      Following is the syntax to create a temporary table in MySQL 
      CREATE TEMPORARY TABLE table_name(
         column1 datatype,
         column2 datatype,
         column3 datatype,
         .....
         columnN datatype,
         PRIMARY KEY( one or more columns )
      );

      Example

      First of all, let us create a temporary table named CUSTOMERS using the below query 
      CREATE TEMPORARY TABLE CUSTOMERS(
         ID   INT              NOT NULL,
         NAME VARCHAR (20)     NOT NULL,
         AGE  INT              NOT NULL,
         ADDRESS  CHAR (25) ,
         SALARY   DECIMAL (18, 2),
         PRIMARY KEY (ID)
      );
      Similar to normal tables, we can insert records into a temporary table using the INSERT statement. Here, we are inserting three records into the above created temporary table 
      INSERT INTO CUSTOMERS VALUES
         (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
         (2, 'Khilan', 25, 'Delhi', 1500.00 ),
         (3, 'kaushik', 23, 'Kota', 2000.00 );
      Execute the following query to display all the records of the temporary table CUSTOMERS.
      SELECT * FROM CUSTOMERS;
      Following are the records of CUSTOMERS table
      ID
      NAME
      AGE
      ADDRESS
      SALARY
      1
      Ramesh
      32
      Ahmedabad
      2000.00
      2
      Khilan
      25
      Delhi
      1500.00
      3
      Kaushik
      23
      Kota
      2000.00
      When we issue a SHOW TABLES command, our temporary table won't be displayed in the list of tables. To verify if the temporary table exists, we need to use the SELECT statement to retrieve its data. Temporary tables will be deleted when we ends our session, so if we log out of MySQL and then try to issue the SELECT command, we won't find the temporary table in the database.

      Dropping Temporary Tables in MySQL

      Though MySQL automatically removes temporary tables when your database connection ends, we can still delete them ourselves by using the DROP TEMPORARY TABLE command if we want to.

      Syntax

      Following is the syntax for dropping a temporary table in MySQL 
      DROP TEMPORARY TABLE table_name;

      Example

      In the following query, we are dropping the temporary table CUSTOMERS that was created in the previous example 
      DROP TEMPORARY TABLE CUSTOMERS;

      Output

      Executing the query above will produce the following output 
      Query OK, 0 rows affected (0.00 sec)

      Verification

      Now, let us verify the temporary table CUSTOMERS by retrieving it's records using the following query 
      SELECT * FROM CUSTOMERS;
      Since we removed the the temporary table CUSTOMERS, it will generate an error saying the table does not exist.
      ERROR 1146: Table 'TUTORIALS.CUSTOMERS' doesn't exist

      Creating Temporary table Using a Client Program

      In addition to create a temporary table in MySQL Database using the MySQL query, we can also perform the "TEMPORARY TABLE" operation on a table using a client program.

      Syntax

      Following are the syntaxes to create a temporary table into MySQL Database in various programming languages
      PHPNodeJSJavaPython
      To create a temporary table into MySQL database through a PHP program, we need to execute the Create Temporary Table statement using the mysqli function query() as 
      $sql="CREATE temporary Table table_name(column_name, column_type, ...)";
      $mysqli->query($sql);

      Example

      Following are the programs
      PHPNodeJSJavaPython
      $dbhost = 'localhost';
      $dbuser = 'root';
      $dbpass = 'password';
      $dbname = 'TUTORIALS';
      $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
      
      if ($mysqli->connect_errno) {
          printf("Connect failed: %s", $mysqli->connect_error);
          exit();
      }
      // printf('Connected successfully.');
      
      $sql = "CREATE TEMPORARY TABLE SalesSummary (" . " product_name VARCHAR(50) NOT NULL, " . " total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00, " . " avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00, " . " total_units_sold INT UNSIGNED NOT NULL DEFAULT 0 )";
      if ($mysqli->query($sql)) {
          printf("temporary table created successfully.");
      }
      if ($mysqli->errno) {
          printf("temporary table could not be created: %s", $mysqli->error);
      }
      $mysqli->close();

      Output

      The output obtained is as follows 
      temporary table created successfully.