sql基础回顾

create table testy (id varchar2(100),name varchar2(100),subject varchar2(100),point varchar2(100));
alter table testy add classcode varchar2(100);
alter table testy modify id  not null;
delete testy;
drop table testy;

insert into testy (id,name,subject,point) values ('1','张三','语文','90');
insert into testy (id,name,subject,point) values ('2','张三','数学','58');
insert into testy (id,name,subject,point) values ('3','李四','语文','90');
insert into testy (id,name,subject,point) values ('4','李四','数学','90');
insert into testy (id,name,subject,point) values ('5','王五','语文','70');
insert into testy (id,name,subject,point) values ('6','王五','数学','80');

update testy t set t.classcode ='1231' where t.id ='1';
update testy t set t.classcode ='1232' where t.id ='2';
update testy t set t.classcode ='1233' where t.id ='3';
update testy t set t.classcode ='1234' where t.id ='4';
update testy t set t.classcode ='1235' where t.id ='5';
update testy t set t.classcode ='1236' where t.id ='6';
update testy t set t.classcode =null where t.id ='7';
commit;
select * from testy;

-- 查询每门功课都大于80分的学生姓名
select distinct name from  testy t where t.name not in (select distinct name from  testy y where y.point  <= 80);

-- 删除除了自动编号不同,其他都相同的学生冗余信息
insert into testy (id,name,subject,point) values ('7','王五','语文','70');
insert into testy (id,name,subject,point) values ('8','王五','数学','80');
commit;
-- 方法1
delete testy a where a.id not in( select min(t.id) from testy t group by t.name,t.subject,t.point);
-- 方法2 查找表中多余的重复记录，重复记录是根据多个字段（name，subject）来判断
select *
  from testy t
 where t.name in (select a.name
                    from testy a
                   group by a.name, a.subject
                  having(count(a.name) > 1))
       and rowid not in (select min(rowid) from testy b group by b.name,b.subject having(count(b.name)>1));

-- 删重复记录
delete testy t
 where (t.name, t.subject) in
       (select a.name, a.subject
          from testy a
         group by a.name, a.subject
        having(count(a.name) > 1))
   and rowid not in (select min(rowid)
                       from testy b
                      group by b.name, b.subject
                     having(count(b.name) > 1));




insert into testy (id,name,subject,point) values ('6','bob','数学','80');
insert into testy (id,name,subject,point) values ('7','bob Sim','数学','80');
delete from testy t where t.id ='7';
select * from testy t where t.id >all (1,3); -- any
select * from testy t where t.name like '_o%';
select * from testy t where t.name like '_ob';
select * from testy t where t.id between 3 and 6;
select * from testy t where t.id > 5 or t.name like'_ob' and t.point >90; -- 与下面的sql等效
select * from testy t where t.id > 5 or (t.name like'_ob' and t.point >90);

--- (+) 左（右）外连接
create table testz (id varchar2(100),classcode varchar2(100),className varchar2(100),header varchar2(100));
drop  table testz;
delete from testz;

insert into testz (id,classcode,classname,header) values ('1','1231','高三(1)班','王怀平');
insert into testz (id,classcode,classname,header) values ('2','1232','高三(2)班','李伟');
insert into testz (id,classcode,classname,header) values ('3','1232','高三(3)班','陈胜');
insert into testz (id,classcode,classname,header) values ('4','1232','高三(4)班','李广');
insert into testz (id,classcode,classname,header) values ('5','1232','高三(5)班','吕布');
commit;

select * from testz for update;

 select * from testy y ,testz z where y.id = z.id;
 select * from testy y ,testz z where y.id = z.id(+);
 select * from testy y ,testz z where y.id (+)= z.id;