学习MySQL复杂查询——day04

一、MySQL复制表的几种方式

我们首先创建表stuents_score，利用SQL语句查看创建表的结构与列名字段。

-- 显示students_score的创建语句与结构
SHOW CREATE TABLE students_score;
-- 显示Create table表SQL语句
/*
CREATE TABLE `students_score` (
  `ID` varchar(11) NOT NULL,
  `SNAME` varchar(255) DEFAULT NULL,
  `CLASS` varchar(255) DEFAULT NULL,
  `SCORE` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*/

之后查看students_score表数据，便于之后观察对比。

SELECT * FROM students_score;

1、CREATE TABLE table_copy SELECT...FROM table形式

如果我们需要完全的复制MySQL的数据表，包括表的结构，索引，默认值等。 如果仅仅使用CREATE TABLE ... SELECT 命令，是无法实现的，但是可以复制表的内容。

CREATE TABLE students_score_copy_1 SELECT *FROM students_score;

查看表结果与表的数据。

-- 1.显示students_score_copy_1的创建语句与结构
SHOW CREATE TABLE students_score_copy_1;
/*
CREATE TABLE `students_score_copy_1` (
  `ID` varchar(11) NOT NULL,
  `SNAME` varchar(255) DEFAULT NULL,
  `CLASS` varchar(255) DEFAULT NULL,
  `SCORE` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*/

这里发现ID并没有主键约束，并没有完全copy到表students_score的表结构。
查看表数据

SELECT * FROM students_score_copy_1;

CREATE TABLE table_copy SELECT * FROM table是有数据的。

2、CREATE TABLE table_copy LIKE table形式

该形式只Copy表的结构，但是不能copy表的内容

-- 2.只复制表结构
CREATE TABLE students_score_copy_2 LIKE students_score;

查看表结构

-- 2.显示students_score_copy_2的创建语句与结构
SHOW CREATE TABLE students_score_copy_2;
/*
CREATE TABLE `students_score_copy_2` (
  `ID` varchar(11) NOT NULL,
  `SNAME` varchar(255) DEFAULT NULL,
  `CLASS` varchar(255) DEFAULT NULL,
  `SCORE` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*/

这里的ID主键有约束，跟原始表students_score是一样的。

SELECT * FROM students_score_copy_2;

但是是没有数据的。

3、复制完整的（表结构+数据）表形式

该形式就是在形式2的基础上补充的，既可以copy到表的结构，又可以copy到数据。

-- 3.完整复制表结构与表内容
CREATE TABLE students_score_copy_3 LIKE students_score;
INSERT INTO students_score_copy_3 SELECT * FROM students_score;

执行CREATE TABLE table_copy LIKE table之后再利用INSERT INTO table_copy SELECT* FROM table将完整的数据copy到复制表中。
注意这是两个语句，不是一个语句。用“;”封号隔开。

-- 3.显示students_score_copy_3的创建语句与结构
SHOW CREATE TABLE students_score_copy_3;
/*
CREATE TABLE `students_score_copy_3` (
  `ID` varchar(11) NOT NULL,
  `SNAME` varchar(255) DEFAULT NULL,
  `CLASS` varchar(255) DEFAULT NULL,
  `SCORE` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*/

查看数据

SELECT * FROM students_score_copy_3;

数据是完整的。

4、CREATE TABLE table_copy AS (SELECT ... FROM table)的形式

该形式就是第一种形式CREATE TABLE table_copy SELECT ... FROM table的延伸。
该形式是复制一个表中的一些字段。
（1）复制表中一些字段

-- 4.复制表中一些字段
CREATE TABLE students_score_copy_4 AS (
SELECT ID,SNAME,SCORE FROM students_score
);

类似于

CREATE TABLE students_score_copy_4 SELECT ID,SNAME,SCORE FROM students_score;

同样只是copy了表的数据，并没有copy表的内容

-- 4.显示students_score_copy_4的创建语句与结构
SHOW CREATE TABLE students_score_copy_4;
/*
CREATE TABLE `students_score_copy_4` (
  `ID` varchar(11) NOT NULL,
  `SNAME` varchar(255) DEFAULT NULL,
  `SCORE` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*/

查看表数据

SELECT * FROM students_score_copy_4;

（2）复制表中一些字段的同时对copy的表进行定义。

-- 5.复制表中的部分内容的同时定义表中的字段信息
CREATE TABLE students_score_copy_5 
(
-- 对copy的表中的ID进行主键约束
ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
)
AS (
SELECT ID,SNAME,SCORE FROM students_score
);

查看表结构与表数据

-- 5.显示students_score_copy_5的创建语句与结构
SHOW CREATE TABLE students_score_copy_5;
/*
CREATE TABLE `students_score_copy_5` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `SNAME` varchar(255) DEFAULT NULL,
  `SCORE` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2017009 DEFAULT CHARSET=utf8
*/

这里主键约束并不是从原始表来的，而是copy的时候自定义的。

SELECT * FROM students_score_copy_5;

（3）复制表中某些字段的同时对列名进行重命令

-- 6.复制表中一些字段并且同时重命名列名
CREATE TABLE students_score_copy_6 AS (

SELECT ID AS SID,SNAME AS STU_NAME,SCORE AS STU_SCORE FROM students_score
);

查看表结构

-- 6.显示students_score_copy_6的创建语句与结构
SHOW CREATE TABLE students_score_copy_6;
/*
CREATE TABLE `students_score_copy_6` (
  `SID` varchar(11) NOT NULL,
  `STU_NAME` varchar(255) DEFAULT NULL,
  `STU_SCORE` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*/

查看表数据

SELECT * FROM students_score_copy_6;