MySQL 数据库怎样把一个表的数据插入到另一个表
2010-08-23 11:19:56
标签：mysql insert 另一张表
原创作品，允许转载，转载时请务必以超链接形式标明文章 原始出处 、作者信息和本声明。否则将追究法律责任。yangfei520.blog.51cto/1041581/381568
web开发中，我们经常需要将一个表的数据插入到另外一个表，有时还需要指定导入字段，设置只需要导入目标表中不存在的记录，虽然这些都可以在程序中拆分成简单sql来实现，但是用一个sql的话，会节省大量代码。下面我以mysql数据库为例分情况一一说明：
两张表：insertTest和insertTest2，前者中有测试数据
create table insertTest(id int(4),name varchar(12));
insert into insertTest values(100,'liudehua');
insert into insertTest values(101,'zhourunfa');
insert into insertTest values(102,'zhouhuajian');
1.如果2张表的字段一致，并且希望插入全部数据，可以用这种方法：
INSERT INTO 目标表 SELECT * FROM 来源表;
insert into insertTest select * from insertTest2;
2.如果只希望导入指定字段，可以用这种方法：
INSERT INTO 目标表 (字段1, 字段2, ...) SELECT 字段1, 字段2, ... FROM 来源表;
注意字段的顺序必须一致。
insert into insertTest2(id) select id from insertTest2;
3.如果您需要只导入目标表中不存在的记录，可以使用这种方法：
INSERT INTO 目标表
(字段1, 字段2, ...)
SELECT 字段1, 字段2, ...
FROM 来源表
WHERE not exists (select * from 目标表
where 目标表.比较字段 = 来源表.比较字段);
1>.插入多条记录：
insert into insertTest2
(id,name)
select id,name
from insertTest
where not exists (select * from insertTest2
where insertTest2.id=insertTest.id);
2>.插入一条记录：
insert into insertTest
(id, name)
SELECT 100, 'liudehua'
FROM dual
WHERE not exists (select * from insertTest
where insertTest.id = 100);
使用 dual 作表名，select 语句后面直接跟上要插入的字段的值。
本文出自 “夜狼” 博客，请务必保留此出处yangfei520.blog.51cto/1041581/381568
一个利用触发器（trigger）记录表操作的例子
2007-12-21 16:54
首先建立数据库Test，建立两个表，我们命名为grades和audit，利用audit实现对grades的审计功能，其中grades用于存放学生的成绩，包括“sdudentID，courseID，grade”三个字段，主键为sdudentID，courseID；audit仅仅比表grades增加了两个字段：changeType，changTime，用于记录grades表被修改的类型和修改时间。
建立2个表格的sql语句如下：
create table grades(
studentID int,
courseID int,
grade int,
primary key(studentID,courseID)
);
create table audit(
changeType char(15),
changeTime datetime,
studentID int,
courseID int,
grade int,
primary key(changeType,changeTime,studentID,courseID,grade)
)

然后在表grades上建立触发器，如果对grades表进行“insert”，"delete","update"操作，在audit表中分别记录这些操作的类型（update(old,new)，insert或者delete），操作的时间，操作影响的数据（比如被删除的数据，插入的数据，修改前的数据，修改后的数据）。
建立触发器的sql语言如下：
/*the beginning of the trigger definition*/
create trigger tr_GradesChanged on grades for delete,insert,update
as
declare @insertedCount int
declare @deletedCount int
declare @changeType char(10)
declare @changeTime datetime
declare @updateType char(4)
/*在ms sql server中有两个临时表保存着被删除和被插入的记录，分别叫“deleted”，“inserted”。update可以看作一次删除和一次添加*/
select @insertedCount=count(*) from inserted
select @deletedCount=count(*) from deleted
select @changeType=
case
when @insertedCount>0 and @deletedCount>0
then 'update'
when @insertedCount=0 and @deletedCount>0
then 'delete'
else 'insert'
end
select @changeTime=getdate()  //select语句为变量赋值！
select @updateType=''
if @changeType='update' select @updateType=' old'

insert into audit(changeType,changeTime,studentID,courseID,grade) select @changeType+@updateType, @changeTime,studentID,courseID,grade from deleted

if @changeType='update' select @updateType=' new'

insert into audit(changeType,changeTime,studentID,courseID,grade) select @changeType+@updateType, @changeTime,studentID,courseID,grade from inserted

/*the end of the trigger definition*/

至此，整个触发器就建立好了，我们可以对此触发器进行测试

运行以下语句：
delete from grades
delete from audit
insert into grades values (19224,002,90)
update grades set grade=100 where studentID =19224
delete from grades where studentID=19224
select * from audit order by changeTime asc
将得到以下结果：

可见，audit表对grades表的操作进行了监控，记载了其变化情况。由于上面的几条测试语句运行的时间间隔太短，所以changetime相同，查询结果首先按changtime升序排列，然后按changtype升序（默认）排列，所以和实际结果有点差别，理论上应该是：
changType changeTime studentID courseID grade
1 insert ----------- 19224 2 90
2 update old ----------- 19224 2 90
3 update new ----------- 19224 2 100
insert语句字段顺序4 delete ---------- 19224 2 100

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。