select * into 新表名 from (select * from T1 union all select * from T2)

這個語句可以實現將合併的資料追加到一個新表中。

不合並重複數據 select * from T1 union all select * from T2

合併重複資料 select * from T1 union select * from T2

兩個表，表1 表2

如果要將 表1的資料併入表2用以下語句即可

insert into 表2(欄位1,欄位2) select 欄位1,欄位2 from b1

注意，必須把欄位名全部寫清楚，而且不允許把自動編號進去寫進去，要合併自動編號欄位必須重寫一個演算法一條一條記錄地加進去

1 insert into b1 select * from b2

2 select * into newtable from (select * from b1 union all select * from b2)

考察左連線、右連線和聯合

表結構,都沒有新增時間和更新時間 演示用:

CREATE TABLE `target` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`username` varchar(20) NOT NULL DEFAULT "A",

`category` varchar(20) NOT NULL,

`price` int(11) unsigned NOT NULL DEFAULT "0",

`num` int(11) unsigned NOT NULL DEFAULT "0",

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

CREATE TABLE `orders` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`username` varchar(20) NOT NULL DEFAULT "A",

`category` varchar(20) NOT NULL,

`price` int(11) unsigned NOT NULL DEFAULT "0",

`num` int(11) unsigned NOT NULL DEFAULT "0",

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

準備資料:

INSERT INTO `target` (`id`, `username`, `category`, `price`, `num`)

VALUES

(1, "A", "電", 10, 2),

(2, "A", "洗", 20, 3),

(3, "A", "廚", 30, 4),

(4, "A", "衛", 40, 5),

(5, "A", "腳", 50, 8);

INSERT INTO `orders` (`id`, `username`, `category`, `price`, `num`)

VALUES

(1, "A", "保暖瓶", 20, 6),

(2, "A", "電", 30, 7),

(3, "A", "洗", 40, 8),

(4, "A", "衛", 70, 9),

(5, "A", "廚", 80, 10),

(6, "A", "手提", 90, 11);

執行sql:

select t.username,t.category,t.price,t.num,o.price,o.num from target as t left join orders as o using(category)

union

select o.username,o.category,t.price,t.num,o.price,o.num from target as t right join orders as o using(category)

結果:

select a.pg,a.種類，decode(月金額，"/") … from（select pg,種類 from target unoin select 人，種類 from 訂單）a left join target b on a.pg=b.pg and b.種類=a.種類 left join 訂單 c on a.pg=c.人 and a.種類=c.種類