Oracle查询二

1. 复合主键的问题

--学生表create table student(       sid number(4) primary key ,       sname varchar2(20),       sidcard varchar2(25),       sgender number(1) --0:男,1:女);--课程表create table course(       cid number(4) primary key ,       cname varchar2(20),       tid number(4) references teacher(tid));--选课表create table sc(       sid number(4) references student(sid),       cid number(4) references course(cid),       score number(4,1),       primary key (sid,cid) --复合主键);

这里对于选课表来说，他的的sid 和cid就是一个复合主键，共同决定了选课表的内容。那么对于sc里面的外键sid和cid在创建的时候，他们的类型必须和student和course的类型一致，并且字节大小也要尽量一致，如果小的话会造成内存溢出，如果太大呢，会造成空间的浪费。

1. 查询学过100并且也学过编号200课程的同学的学号，姓名

这是一种比较简单和简洁的写法，像这样同一个人有两种行为的一般都要建立两张表，进行自身的对比。同时有的时候还要和其他表进行连接select student.sid, sname from sc a,sc b,student where a.sid=b.sid and a.cid=100 and b.cid=200 and student.sid=a.sid

这是一种比较保守的简单的思路，虽然代码要复杂一些，但是思路很简单，一般顺着题目的意思一步一步的写，就可以写出来。这里我们思路大概是这样的，先找出学了100的学号，然后找出学了200的学号，题目要求我们同时学了100和200的学号，那么我们只需要将这两张表和student表连接起来，自然也就是两个课程都学了的表。然后选出学号和姓名就可以了。但是这里要注意表的连接，要注意重命名并且要注意on之后的条件，三张表的连接直接在后面写join 就行了。select stu.sid,stu.sname from student stu join (select sid from sc where cid =100) c100 on stu.sid=c100.sid  join (select sid from sc where cid =200) c200 on stu.sid=c200.sid

1. 查询100课程比200课程成绩高的所有学生的学号。

先来一个比较简单的写法，和上面的有点像，做法也是先分别查询出100课程和200课程的分数，然后将这两张表通过sid连接起来，再利用一个where语句就可以是实现了是不是很简单select c100.sid from (select sid,score from sc where cid=100) c100 join (select sid,score from sc where cid=200) c200 on c100.sid=c200.sidwhere c100.score>c200.score

第二种写法同样是涉及到自身的比较，那么高深一点的人就会马上反应会有一个表的自身对比将表变成两张表，一张表用于100一张表用于200，下面我们来看看具体怎么实现的select a.sid from sc a,sc b where a.sid=b.sid and a.cid=100 and b.cid=200 and a.score>b.score这里我们利用一个语句就实现了，是不是很简单呢，所以一定要总结经验，看到这样的题目我们第一反应就是表的重命名

1. 按各科平均成绩降序排列，平均成绩相同时按及格率升序排列
   这道题的难点其实就在及格率怎么计算，及格率那么我们就要先找到每一科及格的人数，然后除以每一科的的总人数

及格人数select cid,count(1) from sc where score>=60 group by cid

如图
这里对于求每一科的及格人数我们还可以这么想
及格我们设置为1，不及格设置0，那么我们求和不就是及格的人数吗

case when then else end 这个语句返回的一个列，我们可以写在select语句里面来用感觉这种方法虽然不是很简单，但是思维很特别，select cid, case when score>=60 then 1 else 0 end from sc 

下面我们来看下效果

也就是我们将sc课表里面所有的同学的成绩都分成了两类，及格1，不及格0
那么我们现在只需要统计每一科有多少个1就行了

select cid,   sum(case when score>=60 then 1 else 0 end )  from sc  group by cid  

如图：

我们可以看到这里的结果和第一种方法一样
那么下面我还要统计每一科的总人数，这个就很简单了

select cid,   sum(case when score>=60 then 1 else 0 end )  from sc  group by cid

如图

那么及格率我们就可以实现了

select cid,   sum(case when score>=60 then 1 else 0 end )/count(1)  from sc  group by cid

这里我们将结果美化一下，利用round()函数保留2为小数select cid,  round( sum(case when score>=60 then 1 else 0 end )/count(1),2)  from sc  group by cid

平均成绩的话就很简单了

select cid, round(avg(score),2) avg_score from sc group by cid 

那么下面我们只需要对平均成绩降序排列，平均成绩相同时，按照及格率升序排列

select cid, round(avg(score),2) avg_score,round( sum(case when score>=60 then 1 else 0 end )/count(1),2) pass_rate   from sc group by cid order by avg_score desc,pass_rate asc--->order by 后面可以随意进行排序的定义

我们可以看到每一科成绩都是按照平均成绩由高到低，及格率由低到高

1. 查询男生和女生的人数
   要查询男生和女生的人数，那么我只需要两列，一列是性别，一列是人数，性别可以根据group by 人数只需要count就行了，所以这题也很简单，只需要将思路理清晰就很快实现了，要注意groupby后面的元素就是表格的主键或者一般都是第一列，根据什么分类
   所以我们在进行查询的时候一定要先知道我需要什么，我select出的表格有哪些元素
   再根据这些元素去一一实现。

select sgender  ,count(1) number from student group by sgender

1. 查出成绩排名第五的学生姓名和总成绩
   要找排名，那么我们可以通过rownum伪列来实现

select t2.*from(select rownum r,t1.* from(select sid,sum(score) from sc group by sid order by sum(score) desc) t1) t2where r=5一定要注意这里的rownum是和原始的表的行对应的一旦我们进行了排序的或者分组的操作就需要将改变之后的表看成一张新的表，然后选择出新的表的伪列，才是真正的排名  

那么这里我们就选出了排名第五的学生
但是有个问题来了，如果存在并列第五的话，那么伪列rownum就不再试用了。我们就需要借助排名函数rank来实现

select sname,t1.sum_score,t1.rank_sum from student,(select sid,sum(score) sum_score, rank() over( order by sum(score) desc) rank_sum from sc group by sid ) t1where rank_sum=5 and student.sid=t1.sid

1. 表的主键是自己的外键，同时自己的外键也是自己的主键如下

create table emp2(  empno number(4) primary key,  ename varchar2(25),  job varchar2(10),  hiredate date,  mgr number(4) references emp2(empno))

今天就差不多总结到这里了，有什么不对希望多多指教