MySQL資料庫復制表的幾種方式講解

一、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;

這裡寫圖片描述

You May Also Like