4.5.6 create table的扩展
创建与现有的某个表的模式相同的表 create table like
应用常常要求创建与现有的某个表的模式相同的表。SQL提供了一个create table like的扩展来支持这项任务:
1
| create table temp_instructor like instructor;
|
1
2
| mysql> create table temp_instructor like instructor;
Query OK, 0 rows affected (0.06 sec)
|
create table like只复制表结构不复制表数据
instructor和temp_instructor具有相同的表结构
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| mysql> show create table instructor \G;
*************************** 1. row ***************************
Table: instructor
Create Table: CREATE TABLE `instructor` (
`ID` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '老师的主键',
`name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '老师的姓名',
`dept_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '老师所在的系',
`salary` decimal(8,2) DEFAULT NULL COMMENT '老师的工资',
PRIMARY KEY (`ID`),
KEY `dept_name` (`dept_name`),
CONSTRAINT `instructor_ibfk_1` FOREIGN KEY (`dept_name`) REFERENCES `department` (`dept_name`) ON DELETE SET NULL,
CONSTRAINT `instructor_chk_1` CHECK ((`salary` > 29000))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| mysql> show create table temp_instructor \G;
*************************** 1. row ***************************
Table: temp_instructor
Create Table: CREATE TABLE `temp_instructor` (
`ID` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '老师的主键',
`name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '老师的姓名',
`dept_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '老师所在的系',
`salary` decimal(8,2) DEFAULT NULL COMMENT '老师的工资',
PRIMARY KEY (`ID`),
KEY `dept_name` (`dept_name`),
CONSTRAINT `temp_instructor_chk_1` CHECK ((`salary` > 29000))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
|
temp_instructor表中不会从instructor表中复制数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| mysql> select * from instructor;
+-------+------------+------------+-----------+
| ID | name | dept_name | salary |
+-------+------------+------------+-----------+
| 10101 | Srinivasan | Comp. Sci. | 65000.00 |
| 12121 | Wu | Finance | 90000.00 |
| 15151 | Mozart | Music | 40000.00 |
| 22222 | Einstein | Physics | 95000.00 |
| 25566 | Brown | Biology | 100000.00 |
| 30765 | Green | Music | NULL |
| 32343 | El Said | History | 60000.00 |
| 33456 | Gold | Physics | 87000.00 |
| 45565 | Katz | Comp. Sci. | 75000.00 |
| 58583 | Califieri | History | 62000.00 |
| 76543 | Singh | Finance | 80000.00 |
| 76766 | Crick | Biology | 72000.00 |
| 83821 | Brandt | Comp. Sci. | 92000.00 |
| 98345 | Kim | Elec. Eng. | 80000.00 |
+-------+------------+------------+-----------+
14 rows in set (0.04 sec)
mysql> select * from temp_instructor;
Empty set
|
把查询的结果存储成一个新表 create table as
当书写一个复杂查询时,把查询的结果存储成一个新表通常是很有用的;这个表通常是临时的。这里需要两条语句,一条用于创建表(具有合适的列),另一条用于把查询结果插入到表中。SQL:2003提供了一种更简单的技术来创建包含查询结果的表。例如,下面的语句创建了表t1,该表包含一个查询的结果。
1
2
3
4
5
6
| create table t1 as (
select *
from instructor
where dept_name ='Music'
)
with data;
|
在默认情况下,列的名称和数据类型是从查询结果中推导出来的。通过在关系名后面列出列名,可以给列显式指派名字。
正如SQL:2003标准所定义的,如果省略with data子句,表会被创建,但不会载入数据。但即使在省略with data子句的情况下,很多数据库实现还是通过默认方式往表中加载了数据。注意几种数据库实现都用不同语法支持create table…like和create table…as的功能;请参考相应的系统手册以获得进一步细节。
MySQL中的写法
MySQL中好像不执行with data子句,省略上面的with data子句也可以将查询的结果存储成新的表:
1
2
3
4
5
| create table t1 as (
select *
from instructor
where dept_name ='Music'
);
|
create table as的表结构是查询结果对应的表结构 与原表不同
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| mysql> show create table instructor \G;
*************************** 1. row ***************************
Table: instructor
Create Table: CREATE TABLE `instructor` (
`ID` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '老师的主键',
`name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '老师的姓名',
`dept_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '老师所在的系',
`salary` decimal(8,2) DEFAULT NULL COMMENT '老师的工资',
PRIMARY KEY (`ID`),
KEY `dept_name` (`dept_name`),
CONSTRAINT `instructor_ibfk_1` FOREIGN KEY (`dept_name`) REFERENCES `department` (`dept_name`) ON DELETE SET NULL,
CONSTRAINT `instructor_chk_1` CHECK ((`salary` > 29000))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
|
1
2
3
4
5
6
7
8
9
10
11
12
13
| mysql> show create table t1 \G;
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`ID` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '老师的主键',
`name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '老师的姓名',
`dept_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '老师所在的系',
`salary` decimal(8,2) DEFAULT NULL COMMENT '老师的工资'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
|
t1表存放查询得到的数据
1
2
3
4
5
6
7
8
9
10
| mysql> select *
from instructor
where dept_name ='Music';
+-------+--------+-----------+----------+
| ID | name | dept_name | salary |
+-------+--------+-----------+----------+
| 15151 | Mozart | Music | 40000.00 |
| 30765 | Green | Music | NULL |
+-------+--------+-----------+----------+
2 rows in set (0.03 sec)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| mysql> create table t1 as (
select *
from instructor
where dept_name ='Music'
);
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+-------+--------+-----------+----------+
| ID | name | dept_name | salary |
+-------+--------+-----------+----------+
| 15151 | Mozart | Music | 40000.00 |
| 30765 | Green | Music | NULL |
+-------+--------+-----------+----------+
2 rows in set (0.03 sec)
|
create table as与create table like的区别
- 相同点：
- 不同点
create table as 只是复制原数据，其实就是新建一个表来保存查询的结果create table like 产生与源表相同的表结构，包括索引和主键,但是create table like只是复制表结构,并没有复制表中的数据.
create table as和create view的区别
上述create table … as语句与create view语句非常相似,并且都用查询来定义。
两者主要的区别在于:
create table … as语句创建的表的内容在建时表的时候就确定下来了create view语句创建的视图的内容总是反映当前查询的结果。
参考资料
https://www.jianshu.com/p/ec0ee80b560c