SQL 語法 INSERT INTO SELECT FROM

測試環境為 CentOS 8 x86_64 (虛擬機)

INSERT 的資料有辦法從另外一個資料表 ( TABLE ) 而來嗎? 可以使用 INSERT INTO SELECT FROM .

先建立要用來測試的資料.

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-MariaDB MariaDB Server
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> USE testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [testdb]> CREATE TABLE Project (K1 INT(11) NOT NULL AUTO_INCREMENT , ProjectName VARCHAR(199) NOT NULL , Member VARCHAR(199) NOT NULL, PRIMARY KEY (K1));
Query OK, 0 rows affected (0.008 sec)

MariaDB [testdb]> INSERT INTO Project (ProjectName , Member) VALUES('A','Ben') , ('A','Jason') , ('A','Thomas') , ('B','Jack') , ('B','Andy') , ('B','Chuck') , ('C','Jack') , ('C','Jason') , ('C','Chuck');
Query OK, 9 rows affected (0.003 sec)
Records: 9  Duplicates: 0  Warnings: 0

MariaDB [testdb]> SELECT * FROM Project;
+----+-------------+--------+
| K1 | ProjectName | Member |
+----+-------------+--------+
|  1 | A           | Ben    |
|  2 | A           | Jason  |
|  3 | A           | Thomas |
|  4 | B           | Jack   |
|  5 | B           | Andy   |
|  6 | B           | Chuck  |
|  7 | C           | Jack   |
|  8 | C           | Jason  |
|  9 | C           | Chuck  |
+----+-------------+--------+
9 rows in set (0.001 sec)

現在建立 ProjectA 資料表,資料是從 Project 資料表而來的.

MariaDB [testdb]> CREATE TABLE ProjectA (K1 int(11) NOT NULL AUTO_INCREMENT , Name VARCHAR(199) NOT NULL , PRIMARY KEY (K1));
Query OK, 0 rows affected (0.007 sec)

MariaDB [testdb]> SELECT Member FROM Project WHERE ProjectName='A';
+--------+
| Member |
+--------+
| Ben    |
| Jason  |
| Thomas |
+--------+
3 rows in set (0.001 sec)

一般 INSERT 後面接著是 VALUE() ,這語法後面接著 SELECT ,這 SELECT 的回傳值直接等於 INSERT 的 VALUE() 值,而且可以搭配 WHERE 來使用,這解決了 INSERT 本身無法使用 WHERE 的限制.

MariaDB [testdb]> INSERT INTO ProjectA (Name) SELECT Member FROM Project WHERE ProjectName='A';
Query OK, 3 rows affected (0.002 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [testdb]> SELECT * FROM ProjectA;
+----+--------+
| K1 | name   |
+----+--------+
|  1 | Ben    |
|  2 | Jason  |
|  3 | Thomas |
+----+--------+
3 rows in set (0.001 sec)

前面分開 Create 跟 Insert 可以直接用 CREATE AS 來取代.

MariaDB [testdb]> CREATE TABLE ProjectB AS SELECT Member FROM Project WHERE ProjectName='B';
Query OK, 3 rows affected (0.003 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [testdb]> SELECT * FROM ProjectB;
+--------+
| Member |
+--------+
| Jack   |
| Andy   |
| Chuck  |
+--------+
3 rows in set (0.000 sec)