1、临时表
临时表是放在内存中的,只要用户退出就会删除,用show看不到,用select只能在建表的这个数据库内看到,在其他的数据库内看不到,没有用户(数据库)都有自己的内存空间,临时表是放在内存的,普通表是放在磁盘的,可以往磁盘里面读取。
用户可以创建一个和已有的普通表名字相同的临时表。在这种情况下,该用户只能看到临时表而看不见同名的普通表。当临时表被删除后,才可以看到普通表。
创建临时表:
create temporary table li(id int);
2、复制表结构
语句:
create table new_t1 like t1;
这种语法,将从源表复制列名、数据类型、大小、非空约束以及索引。而表的内容以及其它约束不会复制,新表是一张空表。
root@hunan 12:19 mysql>desc t1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
root@hunan 12:19 mysql>desc new_t1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
3、复制表内容
语句:
create table new_t2 as select * from t1;
新表的结构由select列表决定。同时把查询返回的结果集中的行插入到目标表中,这种语法同样只能把非空约束带入到新表中,也不会复制索引。
root@hunan 15:21 mysql>select * from t1;
+----+----------+
| id | name |
+----+----------+
| 12 | chenchen |
+----+----------+
1 row in set (0.00 sec)
root@hunan 15:21 mysql>create table new_t2 as select * from t1;
Query OK, 1 row affected (0.09 sec)
Records: 1 Duplicates: 0 Warnings: 0
root@hunan 15:23 mysql>select * from new_t2;
+----+----------+
| id | name |
+----+----------+
| 12 | chenchen |
+----+----------+
1 row in set (0.00 sec)
4、字段、表取别名
语句:
select PLAYERNO pn,amount at from PENALTIES PEN;
root@TENNIS 12:28 mysql>select PLAYERNO pn,amount at from PENALTIES PEN;
+-----+--------+
| pn | at |
+-----+--------+
| 6 | 100.00 |
| 44 | 75.00 |
| 27 | 100.00 |
| 104 | 50.00 |
| 44 | 25.00 |
| 8 | 25.00 |
| 44 | 30.00 |
| 27 | 75.00 |
+-----+--------+
8 rows in set (0.00 sec)
root@TENNIS 12:28 mysql>select PLAYERNO,amount from PENALTIES;
+----------+--------+
| PLAYERNO | amount |
+----------+--------+
| 6 | 100.00 |
| 44 | 75.00 |
| 27 | 100.00 |
| 104 | 50.00 |
| 44 | 25.00 |
| 8 | 25.00 |
| 44 | 30.00 |
| 27 | 75.00 |
+----------+--------+
8 rows in set (0.00 sec)