mysql sql查询案例(一)

1.所需要表

学生表tbl_student(id,sname) id为主键

课程表 tbl_course(id,cname) id 为主键

选课表tbl_stu_course（id，sid,cid,score）

 

2.查询案例

##查询选了课程id=1的学生姓名

select s.sname from tbl_student s INNER JOIN  tbl_stu_course sc 
on s.id=sc.sid where sc.cid=1;

##查询没有选课程id=4的学生用户名

select id,sname from tbl_student where id not in
 (select sid from tbl_stu_course where cid =4 GROUP BY sid);

##查询没有选满课程的学生

select sname from tbl_student where id IN
(select sid from tbl_stu_course group by sid having count(*) < (select count(*) from tbl_course ))

##查询全选课程的学生

SELECT sname FROM tbl_student WHERE NOT EXISTS(
     SELECT *FROM tbl_course WHERE NOT EXISTS(
       SELECT * FROM tbl_stu_course WHERE sid = tbl_student.id AND cid = tbl_course.id
)
)

select a.sid,b.sname,a.cnt as ctotal from 
(select sid,count(1) as cnt from tbl_stu_course group by sid
         having count(1)=(select count(1) from tbl_course)) a,
tbl_student b where a.sid=b.id;

 

##查看学生id=3的没选哪些课程

select cname from tbl_course  where not exists 
(select * from tbl_stu_course where tbl_course.id = cid and sid = 3)

##查看学生id=3的选了哪些课程
 

select cname from tbl_course  where  exists 
(select * from tbl_stu_course where tbl_course.id = cid and sid = 3)

###给每个学生每门课程成绩超过60分以上的降低10%，给30分到50分的乘以2

select cid,score,(case when score>=60 then score*0.9 
  when score>=30 and score<50 then score*2 
  else score end) as newscore from tbl_stu_course

##查询只选一门课程的学生名称

select id,sname from tbl_student where id IN (
select sc.sid from tbl_stu_course sc
 INNER JOIN
 tbl_course c on sc.cid=c.id   GROUP BY sc.sid HAVING count(*)=1
)

##查询各个课程的平均分

select c.id,c.cname,AVG(tsc.score) as avgscore from tbl_course c
INNER JOIN tbl_stu_course tsc on c.id=tsc.cid GROUP BY cid

##查询各学生选课的总成绩
 

select s.id,s.sname,sum(t.score) from tbl_stu_course t
INNER JOIN tbl_student s where s.id=t.sid
GROUP BY sid

##统计学生的总成绩(没选课总成绩为0)
 

select s.id,s.sname,IFNULL(sum(t.score),0) as total from tbl_student s
LEFT JOIN tbl_stu_course t on s.id=t.sid
GROUP BY sid

##分数转化为等级

select cid,score,(case when score<60 then 'E'
                when score>=60 and score<70 then 'C'
                when score>=70 and score<80 then 'B'
                when score>=80 then 'A'
                else 'N' end ) as class from tbl_stu_course

###查询学生各科成绩将行转成列

select sname,
    MAX(CASE cname WHEN '数学' THEN score ELSE 0 END ) 数学,
    MAX(CASE cname WHEN '语文' THEN score ELSE 0 END ) 语文,
    MAX(CASE cname WHEN '英语' THEN score ELSE 0 END ) 英语,
    MAX(CASE cname WHEN '体育' THEN score ELSE 0 END ) 体育
from (
select s.sname,c.coursename,t.score from tbl_student s 
INNER JOIN tbl_stu_course t on s.id=t.sid
INNER JOIN tbl_course c on t.cid=c.id
) as m group by sname 

MAX函数是为了取group by 分组中最大的值

## 查询每个课程学生成绩最高分

select cname,sname,b.sid,score
from tbl_student a,tbl_stu_course b,tbl_course c
where a.id =b.sid and b.cid =c.id and 
score=(select MAX(score) from tbl_stu_course where cid =c.id )

##查询课程id=1的学生且该学生该课程超过平均分

select cname,sname,b.cid,score
from tbl_student a,tbl_stu_course b,tbl_course c
where a.id =b.sid and b.cid =c.id and c.id=1 and 
score>(select avg(score) from  tbl_stu_course where cid =1)