oracle联合查询 删除的有关问题
oracle联合查询 删除的问题
两张表的结构:
create table sudents(
sudents_id int primary key,
sudents_name varchar2(20) not null,
sudents_date date not null
)
insert into sudents values(1,'张三','08-9月-2010');
insert into sudents values(2,'李四','03-2月-2011');
insert into sudents values(3,'王五','08-6月-2012');
insert into sudents values(4,'赵六','08-11月-2013');
create table sudent(
sudent_id int primary key,
sudent_sex varchar2(20) not null,
sudent_sore int not null,
foreign key (sudent_id) references sudents(sudents_id)
)
insert into sudent values(1,'女',98);
insert into sudent values(2,'男',90);
insert into sudent values(3,'男',80);
insert into sudent values(4,'男',70);
也就是说sudent表的sudent_id 是引用的sudents表的sudents_id
1结合两个表 查询分数大于等于90分的学生的所有信息
2删除结合两个表王五所有信息
3查询日期大于2012-06-08 的两张表的所有信息
最近在学表联合操作 两张表的联合操作还真不会 请大神一一解答
------解决方案--------------------
两张表的结构:
create table sudents(
sudents_id int primary key,
sudents_name varchar2(20) not null,
sudents_date date not null
)
insert into sudents values(1,'张三','08-9月-2010');
insert into sudents values(2,'李四','03-2月-2011');
insert into sudents values(3,'王五','08-6月-2012');
insert into sudents values(4,'赵六','08-11月-2013');
create table sudent(
sudent_id int primary key,
sudent_sex varchar2(20) not null,
sudent_sore int not null,
foreign key (sudent_id) references sudents(sudents_id)
)
insert into sudent values(1,'女',98);
insert into sudent values(2,'男',90);
insert into sudent values(3,'男',80);
insert into sudent values(4,'男',70);
也就是说sudent表的sudent_id 是引用的sudents表的sudents_id
1结合两个表 查询分数大于等于90分的学生的所有信息
2删除结合两个表王五所有信息
3查询日期大于2012-06-08 的两张表的所有信息
最近在学表联合操作 两张表的联合操作还真不会 请大神一一解答
------解决方案--------------------
select t1.sudents.id,
t1.sudents_name,
t1.sudents_date,
t2.sudent_sex,
t2.sudent_sore
from sudents t1,sudent t2
where t1.sudents_id=t2.sudent_id
and t2.sudent_sore>=90;
delete from sudent t1 where t1.sudent_id=(select sudents_id from sudents t2 where t2.sudents_name='王五');
delete from sudents where sudents_name='王五';
select t1.sudents.id,
t1.sudents_name,
t1.sudents_date,
t2.sudent_sex,
t2.sudent_sore
from sudents t1,sudent t2
where t1.sudents_id=t2.sudent_id
and t1.sudents_date>=to_date('2012-06-08','yyyy-mm-dd');