• 4.5.6 create table的扩展
  • 创建与现有的某个表的模式相同的表 create table like
    • create table like只复制表结构不复制表数据
  • 把查询的结果存储成一个新表 create table as
    • MySQL中的写法
    • create table as的表结构是查询结果对应的表结构 与原表不同
    • t1表存放查询得到的数据
  • create table as与create table like的区别
  • create table as和create view的区别
  • 参考资料

4.5.6 create table的扩展

创建与现有的某个表的模式相同的表 create table like

应用常常要求创建与现有的某个表的模式相同的表。SQL提供了一个create table like的扩展来支持这项任务:

create table temp_instructor like instructor;

mysql> create table temp_instructor like instructor;
Query OK, 0 rows affected (0.06 sec)

create table like只复制表结构不复制表数据

instructor和temp_instructor具有相同的表结构

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

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表中复制数据

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,该表包含一个查询的结果。

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子句也可以将查询的结果存储成新的表:

create table t1 as (
    select * 
    from instructor
    where dept_name ='Music'
);

create table as的表结构是查询结果对应的表结构 与原表不同

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

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表存放查询得到的数据

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)

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

原文链接: 4.5.6 create table的扩展