SQL Select Into

1. 定义

慕课解释:Select Into既可以给表做备份,也可以通过某张表的数据新建另一张表。

2. 前言

本小节,我们将介绍一个数据备份利器——Select Into

在数据维护工作中,我们经常需要对某张重要的数据表做备份,又或者只对某张表中的某些重要字段做备份,甚至需要从一张表从提取某些字段组织成另一张表。

对于这些需求,虽然通过多条 SQL 语句来实现,但是 Select Into 让它们变得更加简单。

本小节测试数据如下,请先在数据库中执行:

DROP TABLE IF EXISTS imooc_user;
CREATE TABLE imooc_user
(
  id int PRIMARY KEY,
  username varchar(20),
  age int,
  birthday date
);
INSERT INTO imooc_user(id,username,age,birthday)
VALUES (1,'peter', 18, '2002-10-21'), (2,'pedro', 24, '1996-03-15'), 
(3,'jerry', 28, '1992-02-13'), (4,'mike', 38, '1982-04-01');

3. 初级使用

Select Into 可以将数据从某一张表(表 A)备份到另一张表(表 B)中。

使用语法如下:

SELECT [col] INTO [new_table_name] FROM [table_name];

其中col表示字段名,此处可以使用*来表示所有字段,new_table_name表示备份表名称,table_name是被备份表名称。

3.1 例1 Select Into 备份用户表

请书写 SQL 语句,备份imooc_user表,但剔除掉其中的birthday字段,新表名称为imooc_user_backup

分析:

按照语法直接使用 Select Into 即可,但需剔除掉birthday字段。

语句:

整理可得语句如下:

SELECT id,username,age INTO imooc_user_backup FROM imooc_user;

执行成功后,结果如下:

+----+----------+-----+
| id | username | age |
+----+----------+-----+
| 1  | peter    | 18  |
| 2  | pedro    | 24  |
| 3  | jerry    | 28  |
| 4  | mike     | 38  |
+----+----------+-----+

注意: MySQL 不支持 Select Into 直接创建数据表,你可以使用如下方式替代:

CREATE TABLE imooc_user_backup(SELECT id,username,age FROM imooc_user);

4. 高级使用

Select Into 还可以搭配 Where 以及 Join 来更加有选择性备份。

使用语法如下:

SELECT [col] INTO [new_table_name] FROM [table_name] WHERE [col] [condition];
SELECT [col] INTO [new_table_name] FROM [table_name1] JOIN [table_name2] ON [condition];

其中col表示字段名称,table_name表示数据表名称,condition表示条件子句,数字1、2表示多张表。

4.1 例2 Select Into 搭配 Where

请书写SQL语句,备份imooc_user表中id大于2的记录,新表名称为imooc_user_backup

分析:

按照语法使用 Select Into 搭配 Where,且过滤条件为id > 2

语句:

整理可得语句如下:

SELECT * INTO imooc_user_backup FROM imooc_user WHERE id > 2;

执行成功后,结果如下:

+----+----------+-----+------------+
| id | username | age | birthday   |
+----+----------+-----+------------+
| 3  | jerry    | 28  | 1992-02-13 |
| 4  | mike     | 38  | 1982-04-01 |
+----+----------+-----+------------+

MySQL 不支持 Select Into,可使用如下语句替代:

CREATE TABLE imooc_user_backup LIKE imooc_user;
INSERT INTO imooc_user_backup SELECT * FROM imooc_user WHERE id > 2;

4.2 例3 Select Into 搭配 Join

使用 Join 之前,我们需要新建一张 imooc_user_score 表与 imooc_user 表关联,新建语句如下:

DROP TABLE IF EXISTS imooc_user_score;
CREATE TABLE imooc_user_score
(
  id int PRIMARY KEY,
  user_id int references imooc_user(id),
  user_score int
);
INSERT INTO imooc_user_score VALUES(1,1,10), (2,2,15);

请书写 SQL 语句,备份imooc_userimooc_user_score表,要求新表拥有用户 id,用户积分,用户名这三个字段,且名称为imooc_user_score_backup

分析:

使用 Select Into 搭配 Join,连接条件为imooc_user.id = imooc_user_score.user_id

语句:

整理可得语句如下:

SELECT imooc_user.id,username,user_score INTO imooc_user_score_backup FROM imooc_user
JOIN imooc_user_score ON imooc_user.id = imooc_user_score.user_id;

执行成功后,结果如下:

+----+----------+------------+
| id | username | user_score |
+----+----------+------------+
| 1  | peter    | 10         |
| 2  | pedro    | 15         |
+----+----------+------------+

MySQL 不支持 Select Into,可使用如下语句替代:

CREATE TABLE imooc_user_score_backup 
(
  id int PRIMARY KEY,
  username varchar(20),
  user_score int
);
INSERT INTO imooc_user_score_backup 
SELECT imooc_user.id,username,user_score
FROM imooc_user
JOIN imooc_user_score ON imooc_user.id = imooc_user_score.user_id;

5. 小结

  • Select Into 不常用于项目开发,但却是数据库运维的利器。
  • MySQL 数据库不支持 Select Into,而在 PostgreSQL 中却可以良好使用,如果你使用 MySQL 则需要有良好的 SQL 基础。