MySQL 12 临时表、复制表

1 临时表

1）介绍

MySQL临时表可以保存一些临时数据。

临时表只在当前连接可见，当关闭连接时，MySQL会自动删除表并释放所有空间。

2）创建临时表

mysql> CREATE TEMPORARY TABLE stuents_class1(
    ->     id int not null,
    ->     name varchar(10) not null,
    ->     age int not null,
    ->     sex varchar(5) not null
    -> )default charset=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into stuents_class1 values(121,'lisi',22,'male');
Query OK, 1 row affected (0.00 sec)

mysql> select * from stuents_class1;
+-----+------+-----+------+
| id  | name | age | sex  |
+-----+------+-----+------+
| 121 | lisi |  22 | male |
+-----+------+-----+------+
1 row in set (0.00 sec)

3）删除临时表

mysql> DROP TABLE stuents_class1;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from stuents_class1;
ERROR 1146 (42S02): Table 'my_db.stuents_class1' doesn't exist

2 复制表

1）获取数据表的完整结构

mysql> SHOW CREATE TABLE tb1_study \G;
*************************** 1. row ***************************
       Table: tb1_study
Create Table: CREATE TABLE `tb1_study` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL DEFAULT 'UNKNOWN',
  `type` int(11) DEFAULT '0',
  `date` date DEFAULT NULL,
  `author_name` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

2）修改数据表名

mysql> CREATE TABLE `tb1` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `title` varchar(100) NOT NULL DEFAULT 'UNKNOWN',
    ->   `type` int(11) DEFAULT '0',
    ->   `date` date DEFAULT NULL,
    ->   `author_name` varchar(10) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

3）复制原表的数据

mysql> INSERT INTO tb1 (id,title,type,date,author_name) SELECT id,title,type,date,author_name FROM tb1_study;

mysql> select * from tb1;
+----+--------------+------+------------+-------------+
| id | title        | type | date       | author_name |
+----+--------------+------+------------+-------------+
|  1 | 学习mysql    | NULL | 2018-07-13 |             |
|  2 | 学习java     | NULL | 2018-06-30 |             |
|  3 | 学习scala    | NULL | NULL       |             |
|  4 | 学习python   | NULL | 2018-08-29 |             |
|  5 | 学习spark    | NULL | 2018-08-29 |             |
|  6 | 学习hadoop   | NULL | 2018-08-29 |             |
|  7 | 学习storm    | NULL | 2018-08-31 |             |
|  8 | 学习php      | NULL | 2018-08-31 |             |
+----+--------------+------+------------+-------------+
8 rows in set (0.00 sec)