Creating Temporary Tables : Temporary Table « Table Index « SQL / MySQL

Home
SQL / MySQL
1.Aggregate Functions
2.Backup Load
3.Command MySQL
4.Cursor
5.Data Type
6.Database
7.Date Time
8.Engine
9.Event
10.Flow Control
11.FullText Search
12.Function
13.Geometric
14.I18N
15.Insert Delete Update
16.Join
17.Key
18.Math
19.Procedure Function
20.Regular Expression
21.Select Clause
22.String
23.Table Index
24.Transaction
25.Trigger
26.User Permission
27.View
28.Where Clause
29.XML
SQL / MySQL » Table Index » Temporary Table 
Creating Temporary Tables
     
mysql>
mysql>
mysql> CREATE TABLE mail
    -> (
    ->  t               DATETIME,       # when message was sent
    ->  senderUser      CHAR(8),        # sender (source user and host)
    ->  senderHost      CHAR(20),
    ->  recipientUser   CHAR(8),        # recipient (destination user and host)
    ->  recipientHost   CHAR(20),
    ->  size    BIGINT,         # message size in bytes
    ->  INDEX   (t)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO mail (t,senderHost,senderUser,recipientHost,recipientUser,size)
    ->  VALUES
    ->          ('2010-05-11 10:15:08','saturn','barb','mars','tricia',58274),
    ->          ('2010-05-12 12:48:13','mars','tricia','venus','gene',194925),
    ->          ('2010-05-12 15:02:49','mars','phil','saturn','phil',1048),
    ->          ('2010-05-13 13:59:18','saturn','barb','venus','tricia',271),
    ->          ('2010-05-14 09:31:37','venus','gene','mars','barb',2291),
    ->          ('2010-05-14 11:52:17','mars','phil','saturn','tricia',5781),
    ->          ('2010-05-14 14:42:21','venus','barb','venus','barb',98151),
    ->          ('2010-05-14 17:03:01','saturn','tricia','venus','phil',2394482),
    ->          ('2010-05-15 07:17:48','mars','gene','saturn','gene',3824),
    ->          ('2010-05-15 08:50:57','venus','phil','venus','phil',978),
    ->          ('2010-05-15 10:25:52','mars','gene','saturn','tricia',998532),
    ->          ('2010-05-15 17:35:31','saturn','gene','mars','gene',3856),
    ->          ('2010-05-16 09:00:28','venus','gene','mars','barb',613),
    ->          ('2010-05-16 23:04:19','venus','phil','venus','barb',10294),
    ->          ('2010-05-17 12:49:23','mars','phil','saturn','tricia',873),
    ->          ('2010-05-19 22:21:51','saturn','gene','venus','gene',23992)
    -> ;
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql>
mysql> CREATE TEMPORARY TABLE mail SELECT * FROM mail;
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> SELECT COUNT(*) FROM mail;
+----------+
| COUNT(*) |
+----------+
|       16 |
+----------+
1 row in set (0.00 sec)

mysql>
mysql> DELETE FROM mail;
Query OK, 16 rows affected (0.00 sec)

mysql> SELECT COUNT(*) FROM mail;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql>
mysql> DROP TABLE mail;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT(*) FROM mail;
+----------+
| COUNT(*) |
+----------+
|       16 |
+----------+
1 row in set (0.00 sec)

mysql>
mysql> drop table mail;
Query OK, 0 rows affected (0.00 sec)

   
    
    
    
    
  
Related examples in the same category
1.Using Temporary Tables
2.Give ordinary users the ability to lock (LOCK) tables, create temporary tables, and execute stored procedures.
3.Copying to a Temporary Table
4.A temporary table can be created by adding the keyword TEMPORARY to the CREATE TABLE statement:
5.CREATE TEMPORARY TABLE
6.Duplicate table name between normal table and temporary table
7.Memory engine for temporary table
8.Create a TEMPORARY TABLE;
java2s.com  |  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.