mysql 復制表構造和數據實例代碼

mysql 復制表構造和數據實例代碼。本站提示廣大學習愛好者：（mysql 復制表構造和數據實例代碼）文章只能為提供參考，不一定能成為您想要的結果。以下是mysql 復制表構造和數據實例代碼正文

在mysql數據庫開辟中，我們有時刻須要復制或拷貝一張表構造和數據到破例一張表，這個時刻我們可使用create ... select ... from語句來完成，本文章向年夜家引見mysql復制表構造和數據一個簡略實例，

 好比如今有一張表，我們要將該表復制一份，以備今後應用，那末若何應用mysql語句來完成呢？其實我們可以直接應用create ... select ... from語句來完成，詳細完成辦法請看上面實例。 

我們先來創立一張Topic表，創立Topic表的SQL語句以下：

mysql> CREATE TABLE Topic(
  ->  TopicID   SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ->  Name    VARCHAR(50) NOT NULL,
  ->  InStock   SMALLINT UNSIGNED NOT NULL,
  ->  OnOrder   SMALLINT UNSIGNED NOT NULL,
  ->  Reserved  SMALLINT UNSIGNED NOT NULL,
  ->  Department ENUM('Classical', 'Popular') NOT NULL,
  ->  Category  VARCHAR(20) NOT NULL,
  ->  RowUpdate  TIMESTAMP NOT NULL
  -> );

向Topic表中拔出數據：

mysql> INSERT INTO Topic (Name,     InStock, OnOrder, Reserved, Department,  Category) VALUES
  ->          ('Java',     10,   5,    3,    'Popular',  'Rock'),
  ->          ('JavaScript',  10,   5,    3,    'Classical', 'Opera'),
  ->          ('C Sharp',    17,   4,    1,    'Popular',  'Jazz'),
  ->          ('C',       9,    4,    2,    'Classical', 'Dance'),
  ->          ('C++',      24,   2,    5,    'Classical', 'General'),
  ->          ('Perl',     16,   6,    8,    'Classical', 'Vocal'),
  ->          ('Python',    2,    25,   6,    'Popular',  'Blues'),
  ->          ('Php',      32,   3,    10,    'Popular',  'Jazz'),
  ->          ('ASP.net',    12,   15,   13,    'Popular',  'Country'),
  ->          ('VB.net',    5,    20,   10,    'Popular',  'New Age'),
  ->          ('VC.net',    24,   11,   14,    'Popular',  'New Age'),
  ->          ('UML',      42,   17,   17,    'Classical', 'General'),
  ->          ('www.java2s.com',25,   44,   28,    'Classical', 'Dance'),
  ->          ('Oracle',    32,   15,   12,    'Classical', 'General'),
  ->          ('Pl/SQL',    20,   10,   5,    'Classical', 'Opera'),
  ->          ('Sql Server',  23,   12,   8,    'Classical', 'General');
Query OK, 16 rows affected (0.00 sec)
Records: 16 Duplicates: 0 Warnings: 0

如今我們要將這張表復制一份，詳細操作以下：

mysql> CREATE TABLE Topic2
  -> (
  ->  TopicID   SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ->  Name    VARCHAR(50) NOT NULL,
  ->  InStock   SMALLINT UNSIGNED NOT NULL,
  ->  OnOrder   SMALLINT UNSIGNED NOT NULL,
  ->  Reserved  SMALLINT UNSIGNED NOT NULL,
  ->  Department ENUM('Classical', 'Popular') NOT NULL,
  ->  Category  VARCHAR(20) NOT NULL,
  ->  RowUpdate  TIMESTAMP NOT NULL
  -> )
  -> SELECT *
  -> FROM Topic

如許表Topic2和Topic表不只具有雷同的表構造，表數據也是一樣的了。

破例，假如我們只須要復制表構造，不須要復制數據，也能夠應用create like來完成：

create table a like users;

感激浏覽此文，願望能贊助到年夜家，感謝年夜家對本站的支撐！