3.复制旧表的数据到新表(假设两个表的结构一样):
1 INSERT INTO 新表 SELECT * FROM 旧表 |
mysql> select * from q2;
Empty set (0.01 sec)
mysql> insert into q2 select * from q1;
Query OK, 5 rows affected (0.42 sec)
Records: 5 Duplicates: 0 Warnings: 0
Query OK, 5 rows affected (0.42 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from q2;
+--------+------+--------+------+--------+------+------+-------+
| sname | sage | tname | t | cname | s | c | score |
+--------+------+--------+------+--------+------+------+-------+
| 刘一 | 18 | 叶平 | 1 | 语文 | 1 | 1 | 56 |
| 钱二 | 19 | 叶平 | 1 | 语文 | 2 | 1 | 79 |
| 张三 | 17 | 叶平 | 1 | 语文 | 3 | 1 | 91 |
| 王五 | 17 | 叶平 | 1 | 语文 | 5 | 1 | 46 |
| 赵六 | 19 | 叶平 | 1 | 语文 | 6 | 1 | 35 |
+--------+------+--------+------+--------+------+------+-------+
5 rows in set (0.00 sec)
+--------+------+--------+------+--------+------+------+-------+
| sname | sage | tname | t | cname | s | c | score |
+--------+------+--------+------+--------+------+------+-------+
| 刘一 | 18 | 叶平 | 1 | 语文 | 1 | 1 | 56 |
| 钱二 | 19 | 叶平 | 1 | 语文 | 2 | 1 | 79 |
| 张三 | 17 | 叶平 | 1 | 语文 | 3 | 1 | 91 |
| 王五 | 17 | 叶平 | 1 | 语文 | 5 | 1 | 46 |
| 赵六 | 19 | 叶平 | 1 | 语文 | 6 | 1 | 35 |
+--------+------+--------+------+--------+------+------+-------+
5 rows in set (0.00 sec)
1 INSERT INTO 新表(字段1,字段2,.......) SELECT 字段1,字段2,...... FROM 旧表 |
mysql> insert into sc1(s) select 4inall.s from 4inall;
Query OK, 21 rows affected (0.19 sec)
Records: 21 Duplicates: 0 Warnings: 0
Query OK, 21 rows affected (0.19 sec)
Records: 21 Duplicates: 0 Warnings: 0
mysql> select * from sc1;
+------+------+-------+
| s | c | score |
+------+------+-------+
| 2 | 1 | 79 |
| 2 | 2 | 81 |
| 2 | 3 | 92 |
| 2 | 4 | 68 |
| 3 | 1 | 91 |
| 3 | 2 | 47 |
| 3 | 3 | 88 |
| 3 | 4 | 56 |
| 4 | 2 | 88 |
| 4 | 3 | 90 |
| 4 | 4 | 93 |
| 1 | NULL | NULL |
| 1 | NULL | NULL |
| 1 | NULL | NULL |
| 1 | NULL | NULL |
| 2 | NULL | NULL |
| 2 | NULL | NULL |
| 2 | NULL | NULL |
| 2 | NULL | NULL |
| 3 | NULL | NULL |
| 3 | NULL | NULL |
| 3 | NULL | NULL |
| 3 | NULL | NULL |
| 4 | NULL | NULL |
| 4 | NULL | NULL |
| 4 | NULL | NULL |
| 5 | NULL | NULL |
| 5 | NULL | NULL |
| 5 | NULL | NULL |
| 6 | NULL | NULL |
| 6 | NULL | NULL |
| 6 | NULL | NULL |
+------+------+-------+
32 rows in set (0.00 sec)
+------+------+-------+
| s | c | score |
+------+------+-------+
| 2 | 1 | 79 |
| 2 | 2 | 81 |
| 2 | 3 | 92 |
| 2 | 4 | 68 |
| 3 | 1 | 91 |
| 3 | 2 | 47 |
| 3 | 3 | 88 |
| 3 | 4 | 56 |
| 4 | 2 | 88 |
| 4 | 3 | 90 |
| 4 | 4 | 93 |
| 1 | NULL | NULL |
| 1 | NULL | NULL |
| 1 | NULL | NULL |
| 1 | NULL | NULL |
| 2 | NULL | NULL |
| 2 | NULL | NULL |
| 2 | NULL | NULL |
| 2 | NULL | NULL |
| 3 | NULL | NULL |
| 3 | NULL | NULL |
| 3 | NULL | NULL |
| 3 | NULL | NULL |
| 4 | NULL | NULL |
| 4 | NULL | NULL |
| 4 | NULL | NULL |
| 5 | NULL | NULL |
| 5 | NULL | NULL |
| 5 | NULL | NULL |
| 6 | NULL | NULL |
| 6 | NULL | NULL |
| 6 | NULL | NULL |
+------+------+-------+
32 rows in set (0.00 sec)