mysql复制表结构和内容

许多时候我们想复制一张表部分或者全部内容，或者只复制一张表的框架，这种需求应该说我们很常见。

其实想要实现这种需求十分简单。

表一：

mysql> select * from student_info;
+----+-----------+------+
| id | name      | sex  |
+----+-----------+------+
|  1 | 科比      | 男   |
|  2 | 毛线      | 女   |
|  3 | 黄鱼      | 男   |
|  4 | 子栋      | 男   |
|  5 | 子豪      | 女   |
|  6 | 小鸟      | 男   |
|  7 | 星爷      | 男   |
|  8 | 戴鹏      | 男   |
|  9 | 秦子琪    | 女   |
+----+-----------+------+
9 rows in set (0.00 sec)

表二：

mysql> select * from student_addr;
+--------+--------------+
| name   | addr         |
+--------+--------------+
| 科比   | 湖北罗田     |
| 小鸟   | 湖北襄阳     |
| 子豪   | 江苏扬州     |
| 毛线   | 湖北孝感     |
| 子栋   | 湖北洪湖     |
| 黄鱼   | 湖北罗田     |
+--------+--------------+
6 rows in set (0.00 sec)

表三：

mysql> select * from student_score;
+--------+-------+-------+--------+
| name   | MYSQL | LIUNX | python |
+--------+-------+-------+--------+
| 科比   |    56 |    87 |     74 |
| 毛线   |    87 |    95 |     78 |
| 小鸟   |    60 |    78 |     87 |
| 子豪   |    54 |    67 |     57 |
| 子栋   |    90 |    95 |     94 |
| 黄鱼   |    78 |    69 |     90 |
| 星爷   |    24 |    45 |     42 |
+--------+-------+-------+--------+
7 rows in set (0.00 sec)

 

需求一：我想要一张新的学生信息表，表名new_student_info，只想要湖北的同学的信息。

SQL语句：create table new_student_info (select * from student_addr where addr like '湖北%');

mysql> create table new_student_info (select * from student_addr where addr like '湖北%');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from new_student_info;
+--------+--------------+
| name   | addr         |
+--------+--------------+
| 科比   | 湖北罗田     |
| 小鸟   | 湖北襄阳     |
| 毛线   | 湖北孝感     |
| 子栋   | 湖北洪湖     |
| 黄鱼   | 湖北罗田     |
+--------+--------------+
5 rows in set (0.00 sec)

 

需求二：我想复制表三的框架，表名new_student_score.

SQL语句：create table new_student_score select * from student_score where 1=2;

mysql> create table new_student_score select * from student_score where 1=2;  #where后面的条件随便写
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from new_student_score;  #框架存在
Empty set (0.00 sec)

mysql> desc new_student_score;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| name   | char(10) | YES  |     | NULL    |       |
| MYSQL  | int(4)   | YES  |     | NULL    |       |
| LIUNX  | int(4)   | YES  |     | NULL    |       |
| python | int(4)   | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
4 rows in set (0.01 sec)

 

需求三：一张新的表，囊括所有信息，表名info_all

SQL语句：

mysql> create table info_all
    -> select new.id,new.name,new.sex,new.addr,sc.python,sc.MYSQL,sc.LIUNX
    -> from (
    -> select student_info.id,student_info.name,student_info.sex,student_addr.addr
    -> from student_info left join student_addr
    -> on student_info.name = student_addr.name) as new
    -> left join student_score as sc on new.name=sc.name;

mysql> select * from info_all;
+----+-----------+------+--------------+--------+-------+-------+
| id | name      | sex  | addr         | python | MYSQL | LIUNX |
+----+-----------+------+--------------+--------+-------+-------+
|  1 | 科比      | 男   | 湖北罗田     |     74 |    56 |    87 |
|  2 | 毛线      | 女   | 湖北孝感     |     78 |    87 |    95 |
|  3 | 黄鱼      | 男   | 湖北罗田     |     90 |    78 |    69 |
|  4 | 子栋      | 男   | 湖北洪湖     |     94 |    90 |    95 |
|  5 | 子豪      | 女   | 江苏扬州     |     57 |    54 |    67 |
|  6 | 小鸟      | 男   | 湖北襄阳     |     87 |    60 |    78 |
|  7 | 星爷      | 男   | NULL         |     42 |    24 |    45 |
|  8 | 戴鹏      | 男   | NULL         |   NULL |  NULL |  NULL |
|  9 | 秦子琪    | 女   | NULL         |   NULL |  NULL |  NULL |
+----+-----------+------+--------------+--------+-------+-------+
9 rows in set (0.00 sec)