SQL中的高级查询

时间:2023-02-13 20:40:57
drop table stuInfo;
drop table course;
drop table score;


drop sequence seq_stuid;
drop sequence seq_cid;
drop sequence seq_scid;


create table stuinfo( --学生
   stuid int primary key,
   sname varchar2(50) unique,
   age int not null 
        constraint CK_sage check(age>=18 and age<=30),
   addr varchar2(50),
   sex char(2) default 'M'
       constraint CK_sexs check(sex in('M','F'))
);


create sequence seq_stuid start with 10001 increment by 1;




create table course( --科目
  cid int primary key,
  cname varchar2(50)      
); 


create sequence seq_cid start with 10001 increment by 1;
    


create table score( --成绩
   scid int primary key,
   stuid int
      constraint FK_stuid references stuinfo(stuid),
   courseid int
      constraint FK_courseid references course(cid),
   score int
);


create sequence seq_scid start with 10001 increment by 1;
   
insert into course values(seq_cid.nextval,'j2se精讲');
insert into course values(seq_cid.nextval,'Oracle');
insert into course values(seq_cid.nextval,'html网页设计');
insert into course values(seq_cid.nextval,'Java基础');
   
insert into stuinfo values(seq_stuid.nextval,'张果老',22,null,'M');
insert into stuinfo values(seq_stuid.nextval,'李豹',22,null,'M');
insert into stuinfo values(seq_stuid.nextval,'老胡',22,'北京','M');
insert into stuinfo values(seq_stuid.nextval,'*',24,'湖南','M');
insert into stuinfo values(seq_stuid.nextval,'张无忌',26,'衡阳','M');
insert into stuinfo values(seq_stuid.nextval,'二师兄',28,'长沙','M');
insert into stuinfo values(seq_stuid.nextval,'凤姐',24,'上海','F');
insert into stuinfo values(seq_stuid.nextval,'元芳',28,'上海','M');
insert into stuinfo values(seq_stuid.nextval,'离歌',23,'上海','M');
insert into stuinfo values(seq_stuid.nextval,'八戒',24,'上海','M');
insert into stuinfo values(seq_stuid.nextval,'赵柳',22,'北京','F');
commit;
insert into score values(seq_scid.nextval,10005,10001,95);
insert into score values(seq_scid.nextval,10005,10002,58);
insert into score values(seq_scid.nextval,10005,10003,56);
insert into score values(seq_scid.nextval,10005,10004,81);
insert into score values(seq_scid.nextval,10005,10002,80);


insert into score values(seq_scid.nextval,10001,10001,78);
insert into score values(seq_scid.nextval,10001,10003,81);
insert into score values(seq_scid.nextval,10001,10004,92);




insert into score values(seq_scid.nextval,10002,10003,30);
insert into score values(seq_scid.nextval,10002,10003,78);
insert into score values(seq_scid.nextval,10003,10003,66);
insert into score values(seq_scid.nextval,10004,10003,66);
insert into score values(seq_scid.nextval,10006,10003,78);
insert into score values(seq_scid.nextval,10007,10003,76);
insert into score values(seq_scid.nextval,10008,10002,81);
insert into score values(seq_scid.nextval,10009,10002,90);
insert into score values(seq_scid.nextval,10010,10003,42);


insert into score values(seq_scid.nextval,10002,10001,80);
insert into score values(seq_scid.nextval,10004,10002,78);
insert into score values(seq_scid.nextval,10008,10003,69);
insert into score values(seq_scid.nextval,10008,10001,69);




select * from stuinfo;
select * from course;
select * from score;




commit;


--查出所有姓'张'的学员信息
 select * from stuinfo  where  sname like '张%';


--查出所有addr字段为'NULL'值的学员的信息
 select * from stuinfo  where addr is null;


--查出成绩在60到70分之间的学员的stuid号


 select stuid  from  score   where   score   between    60  and  70;


--查出地址是北京和上海的学生的信息


 select *  from stuinfo  where  addr in   ('北京' , '上海')  ;






--多表的连接查询
   --种类1.内连接  (inner join )  :仅当至少有一个同属于两表的行符合连接条件时,内连接才返回行,内连接消除了与另一张表中任何不匹配的行
         --2.外连接:  外连接会返回from子句中提到的至少一个表中的行,只要这些行符合where条件或having条件
              --左外连接(left join ): 返回左表中所有的行,右表中没有出现的字段用null代替
              --右外连接(right join ): 返回右表中所有的行,左表中没有出现的字段用null代替
              --完整连接 (full join):两表中的数据都会返回,没有的用null代替
         --3.交叉连接(cross  join ): 笛卡尔集 
    
   
   --内连接语法: select 字段  from  表名1  inner  join    表名2  on  连接条件;
   --左外连接:  select 字段 from  表名1  left  join 表名2  on  连接条件
 


   --显示学生姓名,成绩   stuinfo   s   score  sc
    内连接:  select s.sname,sc.score  from  stuinfo s  inner join  score  sc on   s.stuid =sc.stuid ;    
     左外连接:  select s.sname,sc.score from  score sc left join   stuinfo s   on  s.stuid =sc.stuid   where  sc.score  is not null ;
     右外连接: select s.sname ,sc.score  from stuinfo s right join   score sc  on  s.stuid = sc.stuid  where sc.score is not null ;
     
     完整连接:  select s.sname ,sc.score  from stuinfo s full join score sc on s.stuid =sc.stuid;
     
     --
     select * from stuinfo,score  where stuinfo.stuid = score.stuid;
     
     
     
 --显示学生姓名,成绩 及课程名    
    select s.sname,sc.score ,c.cname from  stuinfo s  inner join  score  sc on   s.stuid =sc.stuid inner join course c  on  sc.courseid =c.cid ;  
    


--查出学员在'html网页设计'这门课程中的总成绩和平均成绩,最高分数,最低分数
  select sum(score),avg(score),max(score),min(score)  from  score  inner join course on score.courseid =course.cid  and course.cname='html网页设计';
  
--查出学员在'html网页设计'这门课程中不及格的学生人数
distinct() --去重
select  count(distinct(stuid))  from score  inner join course on score.courseid =course.cid  and  score.score<60 and course.cname='html网页设计';
 
--统计出参加“html网页设计”考试的人数


 select count(distinct(stuid)) 参加考试人数  from score  inner join course on score.courseid =course.cid  and course.cname='html网页设计';


select  count(distinct(stuid))  参加考试人数 from  score,course where  score.courseid =course.cid and  course.cname='html网页设计';


 
--查出没有及格的学生姓名和课程名以及分数
select stuinfo.sname,course.cname,score.score  from  stuinfo,score,course  
where stuinfo.stuid =score.stuid  and course.cid =score.courseid  and score.score<60;




select stuinfo.sname,score.score,course.cname  from stuinfo inner join score 
on  stuinfo.stuid =score.stuid  inner join course on score.courseid  = course.cid 
and score.score<60;