MySQL建立暫存資料表-舊錶建新表

標籤：資訊   列表   查詢   default   核心   play   --   comm   message   

1、建立暫存資料表

　　暫存資料表是一張表，用來臨時儲存一些資料

特點：

　　只對建立該暫存資料表的使用者可見；

　　當會話結束時，MySQL自動刪除暫存資料表。

暫存資料表的核心：建表和刪表消耗資源極其少

 建立暫存資料表的基本格式：

　　CREATE TEMPORARY TABLE  tbl_name(……); 

①建立的暫存資料表在當前會話，正常使用

②中斷連線，再重新串連後執行查詢，拋出異常：

　　錯誤碼： 1146

　　Table ‘db_name.temtbl_name’ doesn‘t exist。//該暫存資料表在會話結束的時候被系統刪除。

 注意：使用者可以建立一個和已有的普通表名字相同的暫存資料表。

在這種情況下，

　　該使用者只能看到暫存資料表而看不見同名的普通表；

　　當暫存資料表被刪除後，才可以看到普通表。

樣本：使用相同的名字建立一個普通表和暫存資料表

mysql> create table test_table(num int);Query OK, 0 rows affected (0.12 sec)mysql> insert into test_table values(1);Query OK, 1 row affected (0.04 sec)mysql> create temporary table test_table(num int,name varchar(20));Query OK, 0 rows affected (0.04 sec)mysql> insert into test_table values(2,‘暫存資料表測試‘);Query OK, 1 row affected (0.00 sec)mysql> select * from test_table;+------+-----------------+| num  | name            |+------+-----------------+|    2 | 暫存資料表測試       |+------+-----------------+1 row in set (0.00 sec)mysql> drop table test_table;Query OK, 0 rows affected (0.00 sec)mysql> select * from test_table;+------+| num  |+------+|    1 |+------+1 row in set (0.00 sec)

 

Q：當建立表時，如果表已經存在了，則MySQL會返回出錯訊息，我們不希望看到報錯的資訊，該如何處理？

A：添加IF NOT EXISTS選項，則強制不顯示這個出錯訊息；但是，語句執行失敗---建表失敗。

mysql> create table if not exists PLAYERS(id int(5),name varchar(20));Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> show warnings;+-------+------+--------------------------------+| Level | Code | Message                        |+-------+------+--------------------------------+| Note  | 1050 | Table ‘PLAYERS‘ already exists |+-------+------+--------------------------------+1 row in set (0.00 sec)mysql> show tables;+-------------------+| Tables_in_TENNIS  |+-------------------+| COMMITTEE_MEMBERS || MATCHES           || PENALTIES         || PLAYERS           || TEAMS             || test_table        |+-------------------+6 rows in set (0.00 sec)

 

 

2、根據已有的表來建立新表

文法1：只想拷貝表結構

　　CREATE TABLE new_tbl LIKE orig_tbl;

將從源表複製列名、資料類型、大小、非空約束以及索引；而表的內容以及其它約束不會複製，新表是一張空表。

mysql> desc TEAMS;+----------+---------+------+-----+---------+-------+| Field    | Type    | Null | Key | Default | Extra |+----------+---------+------+-----+---------+-------+| TEAMNO   | int(11) | NO   | PRI | NULL    |       || PLAYERNO | int(11) | NO   |     | NULL    |       || DIVISION | char(6) | NO   |     | NULL    |       |+----------+---------+------+-----+---------+-------+3 rows in set (0.00 sec)mysql> create table copy_TEAMS like TEAMS;Query OK, 0 rows affected (0.11 sec)mysql> select * from copy_TEAMS;Empty set (0.00 sec)mysql> desc copy_TEAMS;+----------+---------+------+-----+---------+-------+| Field    | Type    | Null | Key | Default | Extra |+----------+---------+------+-----+---------+-------+| TEAMNO   | int(11) | NO   | PRI | NULL    |       || PLAYERNO | int(11) | NO   |     | NULL    |       || DIVISION | char(6) | NO   |     | NULL    |       |+----------+---------+------+-----+---------+-------+3 rows in set (0.00 sec)

 

文法2：根據SELECT子查詢的拷貝

　　CREATE TABLE new_tbl [AS] SELECT {*|column,...} FROM orig_tbl;

新表的結構由select列表決定；同時把查詢返回的結果集中的行插入到目標表中；只能把非空約束帶入到新表中，也不會複製索引

mysql> create table p_m    -> as    -> select a.NAME,a.SEX,b.MATCHNO,b.WON,b.LOST    -> from PLAYERS a,MATCHES b    -> where a.PLAYERNO=b.PLAYERNO;Query OK, 13 rows affected (0.15 sec)Records: 13  Duplicates: 0  Warnings: 0mysql> select * from p_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   |      10 |   3 |    2 || Bailey    | F   |      11 |   2 |    3 || Bailey    | F   |      12 |   1 |    3 || Newcastle | F   |      13 |   0 |    3 |+-----------+-----+---------+-----+------+13 rows in set (0.00 sec)

 

通過根據已有表來建立新表，添加選項temporary，建立臨時副本用來做練習最合適：表內容可一樣，會話結束，暫存資料表自動刪除，原始表中的內容不受任何影響。

注意：

　　如果在表名後面指定的列名和原始表中的列名相同，則可以改變列的大小和非空約束；

　　如果在表名後面指定的列名和原始表中的列名不同，則它作為一個新的列。

mysql> select * from TEAMS;+--------+----------+----------+| TEAMNO | PLAYERNO | DIVISION |+--------+----------+----------+|      1 |        6 | first    ||      2 |       27 | second   |+--------+----------+----------+mysql> CREATE TABLE teams_copy    -> (    ->    teamno INTEGER NOT NULL PRIMARY KEY,    ->    playerno INTEGER NULL,    ->    division char(10) NOT NULL,    ->    coach varchar(20)    -> )    -> as    -> select * from TEAMS;Query OK, 2 rows affected (0.11 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> select * from teams_copy;+-------+--------+----------+----------+| coach | TEAMNO | PLAYERNO | DIVISION |+-------+--------+----------+----------+| NULL  |      1 |        6 | first    || NULL  |      2 |       27 | second   |+-------+--------+----------+----------+

MySQL建立暫存資料表-舊錶建新表