除了之前介绍过的CREATE TABLE可以创建一张新的表,然后正常插入数据,正常运转业务。其实很多时间我们会在已有的表的结构和数据上,创建新表,这次我们就重点介绍这部分内容。

1. INSERT INTO...SELECT语句

INSERT INTO ... SELECT ...和之前学习的INSERT语句很相似,只是后面是一个SELECT语句,按照正常的语句执行顺序,就是先执行后面SELECT内容,获取到字段和数据,然后执行INSERT语句,执行插入数据操作,一个语句,表结构和数据都有了。

下面我们通过几个案例来说明常见的情况。

2.1 前后表字段完全对应的情况

创建一个和表product_ins字段完全相同的新表product_ins_copy

create table product_ins_copy(
	product_id char(4) not null,
    product_name varchar(100) not null,
    product_type varchar(32) not null,
    sale_price integer,
    purchase_price integer,
    regist_date date,
    primary key (product_id)
);
# CREATE TABLE

因为之前表product_ins已经存在部分数据,使用select into语句,正常情况下,可以一次性导入到新创建的表product_ins_copy中。

# 执行插入语句
insert into product_ins_copy 
(product_id, product_name, product_type, sale_price, purchase_price, regist_date) 
select product_id, product_name, product_type, sale_price, purchase_price, regist_date from product_ins;
# INSERT 0 7

# 查看数据,和表product_ins数据是一致的
shop=# select * from product_ins_copy;
 product_id | product_name | product_type | sale_price | purchase_price | regist_date
------------+--------------+--------------+------------+----------------+-------------
 0001       | T恤衫        | 衣服         |       1000 |            500 | 2009-09-20
 0002       | 打孔器       | 办公用品     |        500 |            320 | 2009-09-11
 0003       | 运动T恤      | 衣服         |       4000 |           2800 |
 0004       | 菜刀         | 厨房用具     |       3000 |          28000 |
 0005       | 高压锅       | 厨房用品     |       6800 |           5000 | 2009-01-15
 0006       | 叉子         | 厨房用具     |        500 |                | 2009-01-15
 0007       | 擦菜板       | 厨房用具     |          0 |            790 | 2009-04-28

1.2 前后表不完全一致的情况

实际上insert into...select对于后面的select语句,是可以运行多种形式的,包括where、聚合函数等,最关键的一点,就是通过后面select查询出来的数据,和前面insert字段数目以及数据类型能够对应上就可以正常执行。

创建产品类型统计表

# 创建产品类型表
create table product_type(
	product_type varchar(32) not null,
    sum_sale_price integer,
    sum_purchase_price integer,
    primary key (product_type)
);
# CREATE TABLE

对商品表执行分组查询,并进行统计,导入到表product_type中。

insert into product_type (product_type, sum_sale_price, sum_purchase_price)
select product_type, sum(sale_price) as sum_sale_price, sum(purchase_price) as sum_purchase_price 
from product_ins group by product_type;

shop=# select * from product_type;
 product_type | sum_sale_price | sum_purchase_price
--------------+----------------+--------------------
 衣服         |           5000 |               3300
 办公用品     |            500 |                320
 厨房用品     |           6800 |               5000
 厨房用具     |           3500 |              28790

2. 注意事项

INSERT INTO ... SELECT ...执行过程中,是表级锁,当表数据量比较大的时候,很容易造成长时间表锁时间,数据无法插入和修改,线上环境慎用

参考资料:

  1. SQL基础教程第二版-图灵社区
  2. https://www.postgresql.org/docs/14/sql-insert.html