SQL查询技巧精讲-优快云博客

本文链接：https://blog.youkuaiyun.com/suncheng_hong/article/details/4633368

以学生，课程，成绩，教师四张表来讲解常用的sql,先来看，这四张表的关系

Student(S#,Sname,Sage,Ssex) 学生表

Course(C#,Cname,T#) 课程表

SC(S#,C#,score) 成绩表

Teacher(T#,Tname) 教师表

清晰明了，下面用sql建立表结构：

create database db_school;
use db_school;
drop table t_student;
create table t_student(
id char(3) primary key,
sname varchar(20),
sage int,
ssex varchar(2)
);
exec sp_help t_student;

create table t_teacher(
id char(3) primary key,
tname varchar(20)
);
create table t_course(
id char(3) primary key,
cname varchar(20),
t_id char(3),
foreign key(t_id) references t_teacher(id)
);
exec sp_help t_course;
create table t_sc(
s_id char(3),
c_id char(3),
primary key(s_id,c_id),
foreign key(s_id) references t_student(id),
foreign key(c_id) references t_course(id)
);
alter table t_sc add score int;

查询语句：
--1 查询“001”课程比“002”课程成绩高的所有学生的学号 ；
select * from t_sc;
select sc1.s_id from t_sc sc1,t_sc sc2 where sc1.s_id=sc2.s_id and sc1.c_id = '001' and sc2.c_id = '002' and sc1.score>sc2.score;
--2 查询平均成绩大于60分的同学的学号和平均成绩；
select s_id, avg(score) 平均成绩 from t_sc group by s_id having avg(score)>60 ;
--3 查询所有同学的学号、姓名、选课数、总成绩；
select * from t_sc;
select id,sname from t_student t;
select id,sname,tt.cnum,tt.totalScore
from t_student t
left join (
select s_id, count(c_id) cnum,sum(score) totalScore from t_sc group by s_id
)tt
on t.id= tt.s_id
--4 查询姓“李”的老师的个数
select * from t_teacher;
select count(*) from t_teacher where tname like '李%'
--5 查询没学过“叶平”老师课的同学的学号、姓名；
select * from t_teacher;
select ss.id,ss.sname from t_student ss where ss.id not in(
select s.id from t_student s,t_sc sc where s.id = sc.s_id and sc.c_id in (
select c.id from t_course c,t_teacher t where t.tname= '叶平' and c.t_id = t.id
)
);
--6 查询学过“001”并且也学过编号“002”课程的同学的学号、姓名 ；

select * from t_sc;
select * from t_sc sc, t_student s where s.id = sc.s_id and c_id = '001' and s_id in(
select s_id from t_sc sc, t_student s where s.id = sc.s_id and c_id = '002')

--7 查询学过“张老师”老师所教的所有课的同学的学号、姓名；

select c.id from t_teacher t,t_course c where c.t_id = t.id and t.tname = '张老师';

select s.*,tt.* from t_student s right join
(
select distinct s_id from t_sc sc where sc.c_id in(
select c.id from t_teacher t,t_course c where c.t_id = t.id and t.tname = '张老师'
)
)tt on s.id = tt.s_id;

--8 查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；

思路：先查出002号课程成绩比001课程号成绩好的学生的学号，如何知道002号课程比001号课程成绩好呢？首先比较的都是成绩字段，又都涉及到t_sc这张表，如果参加比较的是同一张表，就可以考虑自连接，下面我们先用自连接查处“002”的成绩比课程编号“001”课程低的所有同学的学号，有了学号，就可以得到姓名了。

select sc1.s_id from t_sc sc1,t_sc sc2
where sc1.s_id = sc2.s_id -- 同一个学生
and sc1.c_id = '001' --001科目
and sc2.c_id = '002' --002科目
and sc2.score<sc1.score

得到id，有了学号，怎么得到姓名呢？学生表里就有学号和姓名，关键是怎么把上面的表和学生表连接起来呢？

有几种方式，可以用表连接，也可以用右连接

select t.id,t.sname from t_student t,
(select sc1.s_id from t_sc sc1,t_sc sc2
where sc1.s_id = sc2.s_id -- 同一个学生
and sc1.c_id = '001' --001科目
and sc2.c_id = '002' --002科目
and sc2.score<sc1.score) tt
where t.id = tt.s_id

这种方式，我不常用，看起来很别扭.

2 看用join 的方式

select t.id,t.sname from t_student t right join(
select sc1.s_id from t_sc sc1,t_sc sc2
where sc1.s_id = sc2.s_id -- 同一个学生
and sc1.c_id = '001' --001科目
and sc2.c_id = '002' --002科目
and sc2.score<sc1.score
)tt on t.id = tt.s_id --join条件，两个表的id一致才表示是一个人嘛。

以求出的学号为主表(与主从表不是一个概念)，t_student为附表，因为学号为主表，所以用right join，right join 表示右边表主表，可以与第三个问题的left join，体会join 的用法。

----9、查询所有课程成绩小于60分的同学的学号、姓名；
select t.id,t.sname from t_student t right join(
select distinct s_id from t_sc where score<60
)tt on t.id = tt.s_id

这里为什么用right join ，那么我们看一看left join 和right join 的区别

left join : 左边表为主表，查询结果为：连接查询结果+左表中有但右表中不符合条件的数据。

right join:右边表为主表，查询结果为：连接查询结果+右表中有但左表中不符合条件的数据。

--10 、查询没有学全所有课的同学的学号、姓名；

select t.id,t.sname from t_student t right join(
select s_id from t_sc group by s_id having count(c_id)<(select count(*) from t_course)
) tt on t.id = tt.s_id

--11、查询至少有一门课与学号为“002”的同学所学相同的同学的学号和姓名；

12 、查询至少学过学号为“ 001 ”同学所有一门课的其他同学学号和姓名；

select t.id,t.sname from t_student t join(
select distinct s_id from t_sc where c_id in(
select c_id from t_sc where s_id = '002'
)
)tt on t.id = tt.s_id and t.id != '002'

13 、把“SC”表中“ 张老师'”老师教的课的成绩都更改为此课程的平均成绩；

1.join方式

update sc
set sc.score = avgscore
from t_sc sc
join (
select c_id,avg(score) avgscore from t_sc group by c_id
)t on sc.c_id = t.c_id
join t_course tt on tt.id = sc.c_id
join t_teacher ttt on ttt.id = tt.t_id and ttt.tname = '张老师'

2.非join

update
sc
set
sc.score = tt.avg_score
from t_sc sc,
(
select c_id , avg(score) avg_score from t_sc where c_id in(
select c.id from t_teacher t,t_course c where t.id = c.t_id and t.tname = '张老师'
)group by c_id
)tt
where tt.c_id = sc.c_id

14 、查询和“ 001 ”号的同学学习的课程完全相同的其他同学学号和姓名；

select stuid,Sname from t_student where stuid
in(
select aa.s_id from
  (
    select s_id from t_sc
    group by s_id having count(*)=(select count(*) from t_sc where s_id='001')
--计算和001号学生上课课程数目相等的学生　
         ) aa
inner join
(
  select s_id from t_sc where c_id in(
   select c_id from t_sc where s_id='001'
  ) --学了001号学生所学的任何一门课程的学生
  group by s_id having count(*)=(select count(*) from t_sc where s_id='001')
  --并且与001号学生学的课程个数与001学生所学课程数目一致的学生
)bb on aa.s_id=bb.s_id and aa.s_id<>'001'
)

15 、删除学习“叶平”老师课的SC表记录

delete from t_sc
where c_id in(
select distinct c_id from t_sc sc
join t_course c on sc.c_id = c.id
join t_teacher t on c.t_id = t.id
and t.tname = '叶平')

17 、按平均成绩从高到低显示所有学生的“语文”、“数学”三门的课程成绩，按如下形式显示：学生ID,语文,数学,有效课程数,有效平均分

select tttt.t_s_id,
max(case cname when '语文' then score else '' end)语文,
max(case cname when '数学' then score else '' end)数学,
max(cnum) 有效课程数,
max(avgscore) 平均成绩
from
(
     select t.s_id t_s_id,t.score,tt.cname,ttt.avgscore,ttt.cnum from t_sc t
  join t_course tt on t.c_id = tt.id
  and (tt.cname = '语文' or tt.cname='数学')
  join (
    select s_id,avg(score) avgscore,count(c_id) cnum from t_sc group by s_id
  )ttt on t.s_id = ttt.s_id --order by avgscore desc
     )tttt
group by tttt.t_s_id order by 平均成绩 desc

18 、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

select c_id,max(score) 最高分,min(score)最低分 from t_sc group by c_id;

19 、按各科平均成绩从低到高和及格率的百分数从高到低顺序

select ttt.c_id1,ttt.avgscore,ttt.百分比 from
    (
     select *
  from
    (select c_id c_id1,avg(score) avgscore from t_sc t group by c_id)t
  join (
      select c_id,sum(case when score-60>=0 then 1 else 0 end)*100/count(*) 百分比 from t_sc group by c_id
  )tt on tt.c_id = t.c_id1
    )ttt
order by ttt.avgscore,百分比 desc

20 、查询如下课程平均成绩和及格率的百分数(用 "1行" 显示): 数学（ 001 ），语文（ 002 ）

select
max(case when ttt.cname='数学' then ttt.avgscore else '' end) 数学平均成绩,
max(case when ttt.cname='数学' then ttt.jgpercent else '' end) 数学及格百分比,
max(case when ttt.cname='语文' then ttt.avgscore else '' end) 语文平均成绩,
max(case when ttt.cname='语文' then ttt.jgpercent else '' end) 语文及格百分比
from(
select * from
(select c_id,avg(score) avgscore,
sum(case when score-60>=0 then 1 else 0 end)*100/count(*) jgpercent
from t_sc t group by c_id)t
join
t_course tt on tt.id = t.c_id and tt.cname in('语文','数学')
)ttt

21 、查询不同老师所教不同课程平均分从高到低显示

select tttt.tname,tttt.cname,avg(tttt.score) avgscore from
(
     select t.*,tt.cname,tt.t_id,ttt.tname from t_sc t
  join t_course tt on t.c_id = tt.id
  join t_teacher ttt on ttt.id = tt.t_id
)tttt
group by tttt.cname,tttt.tname order by tttt.tname,tttt.avgscore desc;

--22、查询如下课程成绩第 2 名到第 3 名的学生成绩单：语文（001），数学（002），英语（003） ,以及学生平均成绩

select
*
from
(
  select *,row_number() over(partition by tttt.c_id order by tttt.score desc) rn from
   (
    select * from t_sc t
    join t_course tt on t.c_id = tt.cid
    join (
      select s_id sid,avg(score) avgscore from t_sc group by s_id
    )ttt on t.s_id = ttt.sid
    join t_student s on t.s_id = s.stuid
   )tttt
)ttttt
where ttttt.rn >=2 and ttttt.rn<=3

--23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

select *
from
(select t.c_id,
sum(
case when score>=0 and score<60 then 1 else 0 end
) score_0_60,
sum(
case when score>=60 and score<70 then 1 else 0 end
) score_60_70,
sum(
case when score>=70 and score<80 then 1 else 0 end
) score_70_85,
sum(
case when score>=80 and score<100 then 1 else 0 end
) score_85_100
from t_sc t
group by t.c_id)tt join t_course ttt on tt.c_id = ttt.cid

--24、查询学生平均成绩及其名次
select *,row_number() over(order by avgscore desc) rn from
(select s_id,avg(score) avgscore from t_sc group by s_id)t;

--25、查询各科成绩前三名的记录:(不考虑成绩并列情况)
select * from t_sc order by c_id,score desc;
select * from
(select c_id,score,row_number() over(partition by c_id order by score desc) rn from t_sc )tt
where tt.rn <=3;

--26、查询每门课程被选修的学生数
select c_id,count(s_id) from t_sc group by c_id

--27、查询出只选修了二门课程的全部学生的学号和姓名
select * from
(
select s_id from t_sc group by s_id having count(c_id)=2
)t
left join t_student tt on t.s_id = tt.stuid

--28、查询男生、女生人数
select * from t_student;
select ssex,count(stuid) from t_student group by ssex;

--29查询姓“张”的学生名单
select * from t_student where sname like '张%';

--30、查询同名同性学生名单，并统计同名人数
select sname,ssex, count(stuid) from t_student group by sname,ssex