请教:怎样从一张表中查出数据插入到另一张表?

请教:怎样从一张表中查出数据插入到另一张表?

问题描述:

一个SQL问题,以MySQL为例

#两张表:
#部门表department(department_id,department_name)
#人员表staff(staff_id,department_id,staff_no,staff_name)
#staff_backup(staff_id,department_id,staff_no,staff_name,Back_date)

问题:从staff表中找出所有department_name是dept_1的数据,插入staff_backup表中,并插入备份时间

注意:这里操作的是使用SQL

建表的语句:

drop database if exists depart;
create database depart;
use depart;

create table department(
department_id int(10) NOT NULL auto_increment,
department_name varchar(20),
PRIMARY KEY(department_id)
);

create table staff(
staff_id int(10) NOT NULL auto_increment,
department_id int(10),
staff_no varchar(20) ,
staff_name varchar(20),
PRIMARY KEY(staff_id)
);

create table staff_backup(
staff_id int(10) NOT NULL auto_increment,
department_id int(10),
staff_no varchar(20),
staff_name varchar(20),
back_date datetime,
PRIMARY KEY(staff_id)
);

#======================================
#初始化department表数据
insert into department values(null, 'dept_1');
insert into department values(null, 'dept_2');
insert into department values(null, 'dept_3');
insert into department values(null, 'dept_4');
insert into department values(null, 'dept_5');
#======================================

#======================================
#初始化staff表数据
insert into staff values(null, 1, '00001', 'Tom');
insert into staff values(null, 2, '00002', 'Jery');
insert into staff values(null, 3, '00003', 'Mike');
insert into staff values(null, 4, '00004', 'Lily');
insert into staff values(null, 5, '00005', 'Lucy');
insert into staff values(null, 1, '00006', 'Henry');
insert into staff values(null, 2, '00007', 'Tiger');
insert into staff values(null, 3, '00008', 'Scott');
insert into staff values(null, 4, '00009', 'Lary');
insert into staff values(null, 5, '00010', 'Linc');
#======================================

现在的问题是:从staff表中找出所有department_name是dept_1的数据是多条结果,使用SQL的嵌套查询貌似实现不了,谁能给个解决办法?

如果不使用存储过程是否能够实现?

insert into staff_backup
( staff_id,department_id,staff_no,staff_name ,back_date)
select staff_id,department_id,staff_no,staff_name ,now() from staff where department_name='dept_1'

:lol: 赞,果真可以