MySQL Create temp table-old table build new table

1. Create a temporary table

A temporary table is a table, used to temporarily save some data

Characteristics:

Visible only to the user who created the temporary table ;

When the session ends, MySQL automatically deletes the temporary table.

The core of the temporal table: very little consumption of resources for building tables and deleting tables

to create a basic format for a temporary table:

　　CREATE temporary TABLE tbl_name (...);

① created temporary table in current session , normal use

② Disconnects and then re-connects and executes the query, throwing an exception :

Error code: 1146

Table ' db_name.temtbl_name ' doesn ' t exist. The temporary table is deleted by the system at the end of the session.

Note: users can create a temporary table with the same name as an existing normal table.

In this case,

The user can only see the temporary table and cannot see the common table with the same name;

When the temporary table is deleted , you can see the normal table .

Example: creating a normal table and temporary table with the same name

Mysql> CREATE TABLEtest_table(numint); Query OK,0Rows Affected (0.12sec) MySQL> INSERT into test_table values (1); Query OK,1Row affected (0.04sec) MySQL> CreateTemporaryTabletest_table(numint, Name varchar ( -)); Query OK,0Rows Affected (0.04sec) MySQL> INSERT into test_table values (2,'temporary table test'); Query OK,1Row affected (0.00sec) MySQL>Select* fromtest_table;+------+-----------------+| num | Name |+------+-----------------+|2| Temporary table Test |+------+-----------------+1Rowinch Set(0.00sec) MySQL> drop table test_table; Query OK,0Rows Affected (0.00sec) MySQL>Select* fromtest_table;+------+| Num |+------+|1|+------+1Rowinch Set(0.00Sec

Q: When creating a table, if the table already exists, then MySQL will return an error message, we do not want to see the error information, how to deal with it?

A: Adding an IF not EXISTS option forces the error message to be displayed; However, the statement execution fails---the build table fails .

Mysql> CREATE TABLEifNOT EXISTS PLAYERS (IDint(5), Name varchar ( -)); Query OK,0Rows affected,1Warning (0.00sec) MySQL>show warnings;+-------+------+--------------------------------+| Level | Code | Message |+-------+------+--------------------------------+| Note |1050| Table'PLAYERS'already exists |+-------+------+--------------------------------+1Rowinch Set(0.00sec) MySQL>show tables;+-------------------+| Tables_in_tennis |+-------------------+| Committee_members | | MATCHES | | Penalties | | PLAYERS | | TEAMS | | test_table |+-------------------+6 rowsinch Set(0.00Sec

2. Create a new table based on an existing table

Syntax 1: just want to copy table structure

　　CREATE TABLE new_tbl like orig_tbl;

column names, data types, sizes, non-null constraints, and indexes are copied from the source table; the contents of the table and other constraints are not duplicated, and the new table is an empty table.

Mysql>desc TEAMS;+----------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+---------+------+-----+---------+-------+| Teamno |int( One) | NO | PRI |       NULL | || Playerno |int( One) |     NO | |       NULL | || Division |Char(6) |     NO | |       NULL | |+----------+---------+------+-----+---------+-------+3Rowsinch Set(0.00sec) MySQL>CREATE table copy_teams like TEAMS; Query OK,0Rows Affected (0.11sec) MySQL>Select* fromCopy_teams; EmptySet(0.00sec) MySQL>desc copy_teams;+----------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+---------+------+-----+---------+-------+| Teamno |int( One) | NO | PRI |       NULL | || Playerno |int( One) |     NO | |       NULL | || Division |Char(6) |     NO | |       NULL | |+----------+---------+------+-----+---------+-------+3Rowsinch Set(0.00Sec

Syntax 2: copy based on select subquery

　　CREATE TABLE new_tbl [as] SELECT {*|column,...} From Orig_tbl;

The structure of the new table is determined by the select list , and the rows in the result set returned by the query are inserted into the target table ; only non-null constraints can be brought into the new table, and the index is not copied

Mysql>CREATE TABLE P_m- as-SelectA.name,a.sex,b.matchno,b.won,b.lost- fromPLAYERS a,matches b-wherea.playerno=B.playerno; Query OK, -Rows Affected (0.15sec) Records: -Duplicates:0Warnings:0MySQL>Select* fromp_m;+-----------+-----+---------+-----+------+| NAME | SEX | Matchno | WON | LOST |+-----------+-----+---------+-----+------+| Parmenter | M |1|3|1|| Parmenter | M |2|2|3|| Parmenter | M |3|3|0|| Baker | M |4|3|2|| Hope | M |5|0|3|| Everett | M |6|1|3|| Brown | M |7|3|0|| Newcastle | F |8|0|3|| Collins | F |9|3|2|| Moorman | F |Ten|3|2|| Bailey | F | One|2|3|| Bailey | F | A|1|3|| Newcastle | F | -|0|3|+-----------+-----+---------+-----+------+ -Rowsinch Set(0.00Sec

By creating a new table based on an existing table, adding the option temporary, creating a temporary copy is the most appropriate exercise : The table content is the same, the session ends, the temporary table is automatically deleted, and the contents of the original table are unaffected.

Attention:

If the column name specified after the table name is the same as the column name in the original table, the column size and non-null constraints can be changed ;

If the column name specified after the table name differs from the column name in the original table, it acts as a new column .

Mysql>Select* fromTEAMS;+--------+----------+----------+| Teamno | Playerno | Division |+--------+----------+----------+|1|6| First | |2| -| Second |+--------+----------+----------+MySQL>CREATE TABLE teams_copy- (    -Teamno INTEGER not NULL PRIMARY KEY,-Playerno INTEGER NULL,DivisionChar(Ten) not NULL,Coach varchar ( -)    - )    - as-Select* fromTEAMS; Query OK,2Rows Affected (0.11sec) Records:2Duplicates:0Warnings:0MySQL>Select* fromteams_copy;+-------+--------+----------+----------+| Coach | Teamno | Playerno | Division |+-------+--------+----------+----------+| NULL |1|6| First | | NULL |2| -| Second |+-------+--------+----------+----------+

MySQL Create temp table-old table build new table