Welcome to MySQL Workbench:MySQL 複製表

MySQL 複製表
如果我們需要完全的複製MySQL的資料表，包括表的結構，索引，預設值等。 如果僅僅使用CREATE TABLE … SELECT 命令，是無法實現的。
本章節將為大家介紹如何完整的複製MySQL資料表，步驟如下：
使用 SHOW CREATE TABLE 命令獲取建立資料表(CREATE TABLE) 語句，該語句包含了原資料表的結構，索引等。
複製以下命令顯示的SQL語句，修改資料表名，並執行SQL語句，通過以上命令 將完全的複製資料表結構。
如果你想複製表的內容，你就可以使用 INSERT INTO … SELECT 語句來實現。

CREATE TABLE test_NO1(
test_NO1_id INT NOT NULL AUTO_INCREMENT,
test_NO1_title VARCHAR(100) NOT NULL,
test_NO1_author VARCHAR(40) NOT NULL,
test_NO1_date DATE,
PRIMARY KEY ( test_NO1_id )
)ENGINE=InnoDB;

drop table test_no1;


INSERT INTO test_NO1 
    (test_NO1_title, test_NO1_author, test_NO1_date )
    VALUES
    ("programb", "welcome to programb", NOW());
    
    
select * from test_NO1;


INSERT INTO test_NO1 
    (test_NO1_title, test_NO1_author, test_NO1_date )
    VALUES
    ("programb2", "welcome to programb2", NOW());
    
INSERT INTO test_NO1 
    (test_NO1_title, test_NO1_author, test_NO1_date )
    VALUES
    ("programb3", "welcome to programb3", NOW());
    
    

SELECT * from test_NO1  WHERE test_NO1_title='programb2';

SELECT * from test_NO1;

update test_NO1 SET test_NO1_title='programb100' WHERE test_NO1_id=1;

SELECT * from test_NO1 where test_NO1_id=1;

SELECT * from test_NO1;

DELETE FROM test_NO1 WHERE test_NO1_id=1;

SELECT * from test_NO1;

 SELECT * from test_NO1  WHERE test_NO1_title LIKE '%programb2';
 
 INSERT INTO test_NO1 
    (test_NO1_title, test_NO1_author, test_NO1_date )
    VALUES
    ("programb2", "welcome.to.programb2", NOW());
 
 SELECT * from test_NO1  WHERE test_NO1_author LIKE '%programb2';
 
 SELECT * from test_NO1  WHERE test_NO1_author LIKE '%to%';
 
 SELECT * from test_NO1  WHERE test_NO1_author LIKE 'welcome%';
 
 SELECT * from test_NO1;
 
 CREATE TABLE test_NO2(
test_NO1_id INT NOT NULL AUTO_INCREMENT,
test_NO1_title VARCHAR(100) NOT NULL,
test_NO1_author VARCHAR(40) NOT NULL,
test_NO1_date DATE,
PRIMARY KEY ( test_NO1_id )
)ENGINE=InnoDB;

 INSERT INTO test_NO2
    (test_NO1_title, test_NO1_author, test_NO1_date )
    VALUES
    ("programb2", "welcome.to.programb2", NOW());
    
 SELECT * from test_NO2;
 
 SELECT test_NO1_author FROM test_no1
 UNION
 SELECT test_NO1_author FROM test_no2
 ORDER BY test_NO1_author; 
 
  SELECT test_NO1_author FROM test_no1
 UNION all
 SELECT test_NO1_author FROM test_no2
 ORDER BY test_NO1_author; 
 
 
select * from test_no1 order by test_NO1_date asc;

INSERT INTO test_NO1 
    (test_NO1_title, test_NO1_author, test_NO1_date )
    VALUES
    ("programb4", "welcome to programb4", NOW());
    
INSERT INTO test_NO1 
    (test_NO1_title, test_NO1_author, test_NO1_date )
    VALUES
    ("programb5", "welcome to programb5", NOW());
    
select * from test_no1 order by test_NO1_date asc;

select * from test_no1 order by test_NO1_date desc;

select test_NO1_title, count(*) from test_no1 group by test_NO1_title;

select test_NO1_title, sum(test_NO1_id)  from test_no1 group by test_NO1_title;

select test_NO1_title, avg(test_NO1_id)  from test_no1 group by test_NO1_title;

SELECT * from test_NO1;

SELECT * from test_no2;

INSERT INTO test_NO2 
    (test_NO1_title, test_NO1_author, test_NO1_date )
    VALUES
    ("programb4", "welcome to programb4", NOW());
    
INSERT INTO test_NO2 
    (test_NO1_title, test_NO1_author, test_NO1_date )
    VALUES
    ("programb2", "welcome to programb2", NOW());
    
SELECT * from test_no2;

select t1.test_NO1_title, t2.test_NO1_date from test_no1 t1, test_no2 t2 where t1.test_NO1_date=t2.test_NO1_date;

select t1.test_NO1_title, t2.test_NO1_date from test_no1 t1 left join test_no2 t2 on t1.test_NO1_date=t2.test_NO1_date;

select t1.test_NO1_title, t2.test_NO1_date from test_no1 t1 right join test_no2 t2 on t1.test_NO1_date=t2.test_NO1_date;


SELECT * from test_no2;

INSERT INTO test_NO2 
    (test_NO1_title, test_NO1_author, test_NO1_date )
    VALUES
    ("programb2", "welcome", NOW());

SELECT * from test_no2;

select * from test_no2 where test_NO1_author is null;


INSERT INTO test_NO2 
    (test_NO1_title, test_NO1_author )
    VALUES
    ("programb2", "welcome");

INSERT INTO test_NO2 
    (test_NO1_title, test_NO1_author )
    VALUES
    ("programb2", "welcome");

INSERT INTO test_NO2 
    (test_NO1_title, test_NO1_author )
    VALUES
    ("programb2", "welcome");

SELECT * from test_no2;

select * from test_no2 where test_NO1_date is null;

select * from test_no2 where test_NO1_date is not null;

select * from test_no1 where test_NO1_title regexp '^pro';

select * from test_no1 where test_NO1_title regexp '2$';

select * from test_no1 where test_NO1_title regexp 'gra';

select * from test_no1 where test_NO1_title regexp '^[pro]|5$';

begin;

insert into  test_NO1(test_NO1_title, test_NO1_author ) value("pro","welcome");
insert into  test_NO1(test_NO1_title, test_NO1_author ) value("pro","welcome");
insert into  test_NO1(test_NO1_title, test_NO1_author ) value("pro","welcome");
insert into  test_NO1(test_NO1_title, test_NO1_author ) value("pro","welcome");
insert into  test_NO1(test_NO1_title, test_NO1_author ) value("pro","welcome");
insert into  test_NO1(test_NO1_title, test_NO1_author ) value("pro","welcome");

commit;

SELECT * from test_NO1;


show columns from test_no1;

alter table test_no1 alter test_NO1_author set default 1000;

alter table test_no1 modify test_NO1_author  varchar(100);

show columns from test_no1;

alter table test_no1 drop  test_NO1_author;

show columns from test_no1;

alter table test_no1 add test_NO1_author int first;

show columns from test_no1;

alter table test_no1 drop  test_NO1_author;

alter table test_no1 add test_NO1_author int;

show columns from test_no1;

alter table test_no1 drop  test_NO1_author;

alter table test_no1 add test_NO1_author varchar(100);

CREATE TABLE test_no3(ID INT NOT NULL,username VARCHAR(16) NOT NULL, INDEX username2 (username(12)));  

show columns from test_no3;

SELECT * from test_no3;

select username from test_no3;

SHOW INDEX FROM test_no3;

DROP INDEX username2 ON test_no3; 

SHOW INDEX FROM test_no3;

ALTER TABLE test_no3 ADD INDEX (username);

 ALTER TABLE test_no3 DROP INDEX username;
 
 
 
 CREATE TEMPORARY TABLE test_no4 (test_name VARCHAR(50) NOT NULL);
 
 select * from test_no4;
 
 DROP TABLE test_no4;
 
 INSERT INTO test_no4 (test_name)VALUES(1000000);
 
 
  select * from test_no3;
  
  SHOW CREATE TABLE test_no3;
  
  INSERT INTO test_no3 (id,username)VALUES(1,1000000);
  
  select * from test_no3;
  
  CREATE TABLE test_no5 (id int ,username VARCHAR(50) NOT NULL);
  INSERT INTO test_no5(id,username)SELECT id,username FROM test_no3; 
  
    select * from test_no5;
  

  SHOW CREATE TABLE test_no3;
  
  INSERT INTO test_no3 (id,username)VALUES(1,1000000);
  
  select * from test_no3;
  
  CREATE TABLE test_no5 (id int ,username VARCHAR(50) NOT NULL);
  INSERT INTO test_no5(id,username)SELECT id,username FROM test_no3; 
  
    select * from test_no5;