sql练习学生成绩查询实践

大家好，又见面了，我是你们的朋友全栈君。

数据使用介绍：

--1.学生表
Student(SId,Sname,Sage,Ssex)
--SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

--2.课程表
Course(CId,Cname,TId)
--CId 课程编号,Cname 课程名称,TId 教师编号

--3.教师表
Teacher(TId,Tname)
--TId 教师编号,Tname 教师姓名

--4.成绩表
SC(SId,CId,score)
--SId 学生编号,CId 课程编号,score 分数

create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-01' , '女');

create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

1. 查询” 01 “课程比” 02 “课程成绩高的学生的信息及课程分数
   （使用join时要有on与之对应，表示连接的条件）

select * from Student JOIN (
	select t1.sid,class1, class2 from 
	(select sid,score as class1 from sc where sc.CId='01')as t1,
	(select sid,score as class2 from sc where sc.CId='02')as t2 
	where t1.SId = t2.SId AND t1.class1 > t2.class2)
as r on Student.SId = r.sid;

1.1 查询同时存在” 01 “课程和” 02 “课程的情况

select t1.sid,t1.score as class1,t2.score as class2 from 
	(select sid,cid,score from sc where sc.CId='01') as t1,
	(select sid,cid,score from sc where sc.CId='02')as t2 
 where t1.sid = t2.sid;

1.2 查询存在” 01 “课程但可能不存在” 02 “课程的情况(不存在时显示为 null )
（使用左外连接时，保留左侧多余数据）

select t1.sid,class1,class2 from 
	(select sid,cid,score as class1 from sc where sc.CId='01') as t1 
 left join
	(select sid,cid,score as class2 from sc where sc.CId='02')as t2 
 on t1.sid = t2.sid;

1.3 查询不存在” 01 “课程但存在” 02 “课程的情况
（使用in和not in 进行选择）

select * from sc where sc.sid 
    not in (select sid from sc where cid='01') and sc.cid='02';

2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
   (两种方法)

select student.sid,student.Sname,ss from (
    select sid,sum(score)/count(score) as ss from sc 
    group by sc.sid)as r,student where 
    ss>60 and student.sid=r.sid;
select student.sid,student.Sname,ss from (
    select sid,avg(score) as ss from sc 
    group by sc.sid having avg(score)>60)as r,student 
    where student.sid=r.sid;

3. 查询在 SC 表存在成绩的学生信息

(distinct对重复行进行去重)
select * from student where sid in(select distinct sid from sc);

4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

(第二条会显示出没有选课的学生的成绩和选课数为null)
select student.sid,Sname,ss,cc from student,(select sid,count(cid) as cc,sum(score) as ss from sc group by sc.sid) as r where student.sid=r.sid;
select student.sid,Sname,ss,cc from student left join (select sid,count(cid) as cc,sum(score) as ss from sc group by sc.sid) as r on student.sid=r.sid;

4.1 查有成绩的学生信息

（关于in和exists的不同用法）
select * from student where sid in(select distinct sid from sc);
select * from student where exists(select sc.sid from sc where student.sid=sc.sid);

5. 查询「李」姓老师的数量

(部分匹配查询：%->0个或多个字符，_(下划线)->代表一个字符，[]->表示在某一范围的字符，[^]->表示不再某一范围的字符)
select count(*) from Teacher where Teacher.Tname like '李%';

6. 查询学过「张三」老师授课的同学的信息

(进行多表的连接)
select * from student join(select sc.sid from Teacher,sc,Course where Tname='张三' and sc.cid=Course.cid and Course.TId=Teacher.TId)as r on student.sid=r.sid;

7. 查询没有学全所有课程的同学的信息

(后面使用exists)++++++++++++++++
select * from student where student.sid in (select sc.sid from sc group by sc.sid having count(sc.sid)<(select count(*) from Course));

8. 查询至少有一门课与学号为” 01 “的同学所学相同的同学的信息

select * from student where student.sid in (select sc.sid from sc where sc.cid in (select sc.cid from sc where sc.sid='01'));

9. 查询和” 01 “号的同学学习的课程 完全相同的其他同学的信息

(只有深刻理解exists的作用才能明白其含义：1.先从student中拿出一条记录，2.从a中拿出第一条记录，
	3.根据student和a中的信息去遍历sc中每一条记录,如果存在就返回True，
         否则返回False；not exists正好相反。
	4.如果返回True，将student记录放入结果集中。
     学习连接： https://zhuanlan.zhihu.com/p/20005249 )
select * from student where not exists(
	select * from (select cid from sc where sid = '01')as a where not exists(
		select * from sc where sc.sid=student.sid and a.cid=sc.cid
		)) and student.sid!='01';

10. 查询没学过”张三”老师讲授的任一门课程的学生姓名

(对于exists的理解要再思考一下)
select Sname from student where not exists(
	select * from (select cid from Teacher,Course where Tname='张三'and Teacher.TId=Course.TId)as a 
	where  exists(select * from sc where student.sid=sc.sid and a.cid=sc.cid));

select * from student
    where student.sid not in(
        select sc.sid from sc where sc.cid in(
            select course.cid from course where course.tid in(
                select teacher.tid from teacher where tname = "张三"
            )
        )
    );

11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select student.sid,student.Sname,r.ss from student 
join (
	select sid,avg(score)as ss from sc where score<60 group by sid having count(*)>=2
	)as r on student.sid=r.sid;

12. 检索” 01 “课程分数小于 60，按分数降序排列的学生信息

select * from student join sc on student.sid=sc.sid where sc.cid='01' and score<60 order by score desc;

13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select sc.*,avg(score) as ss from sc group by sid order by ss desc;

14. 查询各科成绩最高分、最低分和平均分：

以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select sc.cid,max(sc.score)as 最高分,min(sc.score)as 最低分,avg(sc.score)as 平均分 from sc group by cid;

16. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

select a.sid,a.cid,a.score ,count(b.score)+1 as rank from sc as a 
    left join sc as b on a.cid=b.cid and a.score<b.score 
    group by a.cid,a.sid,a.score order by a.cid,rank asc;

15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次

select a.sid,a.cid,a.score ,count(distinct b.score)+1 as rank from sc as a 
    left join sc as b on a.cid=b.cid and a.score<b.score 
    group by a.cid,a.sid,a.score order by a.cid,rank asc;

16. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺

select (select count( distinct ss) from (select sc.sid,sum(score) as ss from sc group by sc.sid) as a where a.ss>=sums.ss)as rank,sums.sid,sums.ss 
from (select sc.sid,sum(score) as ss from sc group by sc.sid) as sums order by sums.ss desc;

16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

select (@i:=@i+1) as rank,b.sid,b.ss from (
	select sid,sum(score)as ss from sc group by sid order by ss desc)as b,(select @i:=0)a;

17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

select sc.cid,Course.Cname,
count(case when sc.score>85 then 1 end)as "[100-85]",
count(case when sc.score<=85 and sc.score>70 then 1 end)as "[85-70]",
count(case when sc.score<=70 and sc.score>60 then 1 end)as "[70-60]",
count(case when sc.score<=60 then 1 end)as "[100-85]"
 from sc left join course on sc.cid=course.cid group by sc.cid;

18. 查询各科成绩前三名的记录

select * from sc as a left join sc as b on a.cid=b.cid and a.score<b.score group by a.cid,a.sid,a.score having count(b.cid)<3;

19. 查询每门课程被选修的学生数

select sc.cid,Course.Cname,count(sid) from sc,course where sc.cid=course.cid group by sc.cid;

20. 查询出只选修两门课程的学生学号和姓名

select student.sid,student.Sname from student,(select * from sc group by sid having count(cid)=2)as r where student.sid=r.sid;

21. 查询男生、女生人数

select Ssex,count(*) from student group by Ssex;

22. 查询名字中含有「风」字的学生信息

select * from student where Sname like '%风%';

23. 查询同名同性学生名单，并统计同名人数

select Sname,count(*) from student group by Sname having count(*)>1;

24. 查询 1990 年出生的学生名单

select * from student where Sage like '1990%';
select * from student where year(student.Sage)=1990;

25. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

select sc.cid,course.Cname,avg(score)as ss from sc,course where sc.cid=course.cid group by cid order by ss desc,sc.cid asc;

26. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

select student.sid,student.Sname,r.average from student,(
    select sid,avg(score)as average from sc group by sid having average>=85)r 
    where r.sid = student.sid; 
select student.sid, student.sname, AVG(sc.score) as aver from student,sc 
    where student.sid = sc.sid group by sc.sid having aver > 85;

27. 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

select student.sname,sc.score from student,sc,course 
    where course.Cname='数学'and course.cid = sc.cid and student.sid=sc.sid and sc.score<60;

28. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

select student.sname,sc.cid,sc.score from student left join sc on student.sid=sc.sid;

29. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

select student.sname,course.Cname,sc.score from student,course,sc where student.sid=sc.sid and sc.cid=course.cid and sc.score>70;

30. 查询不及格的课程

select sc.cid,course.Cname,'存在' as 不及格 from sc,course where score<60 and sc.cid=course.cid group by cid;

31. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名

select student.sid,student.sname from student,sc 
    where student.sid=sc.sid and sc.cid='01' and sc.score>=80;
select student.sid,student.sname from student 
    where student.sid in (select sid from sc where sc.cid='01' and score>=80);

32. 求每门课程的学生人数

select course.Cname,count(*)as '人数' from sc,course where course.cid=sc.cid group by sc.cid;

33. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

select student.*,sc.score from student,sc,teacher,course 
    where Teacher.tname='张三' and Teacher.tid=course.tid and course.cid=sc.cid and sc.sid=student.sid 
    order by score desc limit 1;

34. 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

(进行查询前先添加一条记录)
insert into SC values('13' , '02' , 90);
select student.*,sc.score from student,sc,course,teacher where sc.score=(
	select max(score) from sc,teacher,course where teacher.tname='张三' and Teacher.tid=course.tid and course.cid=sc.cid)
and teacher.tname='张三' and Teacher.tid=course.tid and course.cid=sc.cid and student.sid=sc.sid;

35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

(通过group by来对重复记录（01 03,03 01）进行去重)
select sc.sid,sc.cid,sc.score from sc join sc as r on sc.sid=r.sid and sc.cid!=r.cid and sc.score=r.score group by sc.sid,sc.cid;

36. 查询每门功成绩最好的前两名

(这里count是为了筛选：比如最高的分数不比任何一个分数低，第二高仅仅比最高成绩低，而最低分可能比三四个记录中的分数要低)
select a.sid,a.cid,a.score from sc as a left join sc as b 
    on a.cid=b.cid and a.score<b.score 
    group by a.cid,a.sid,a.score having count(b.cid)<2 order by a.cid;

37. 统计每门课程的学生选修人数（超过 5 人的课程才统计）。

select sc.cid,count(sc.sid) as '选修人数' from sc group by sc.cid having count(sc.sid)>5;

38. 检索至少选修两门课程的学生学号

select sc.sid,count(sc.cid) as '人数' from sc group by sid having count(sc.cid)>=2;

39. 查询选修了全部课程的学生信息

select student.* from student where not exists(
    select * from course where  not exists(
    select * from sc where course.cid=sc.cid and sc.sid=student.sid));

40. 查询各学生的年龄，只按年份来算

select student.sname,year(curdate())-year(student.Sage) as '年龄' from student;

41. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

select student.sid,student.sname,year(from_days(datediff(curdate(),student.Sage))) as '年龄' from student;
select student.sid,student.sname,timestampdiff(year,student.Sage,curdate()) as '年龄' from student;

42. 查询本周过生日的学生

select student.sid,student.sname,weekofyear(curdate()) from student where weekofyear(curdate())=weekofyear(student.Sage);

43. 查询下周过生日的学生

select student.sid,student.sname,weekofyear(curdate()) from student where weekofyear(curdate())+1=weekofyear(student.Sage);

44. 查询本月过生日的学生

select student.sid,student.sname,month(curdate()) from student where month(curdate())=month(student.Sage);

45. 查询下月过生日的学生

select student.sid,student.sname,month(curdate())+1 from student where month(curdate())+1=month(student.Sage);

视图创建：

create view  Student_age 
as  select student.sname,year(curdate())-year(student.Sage) as '年龄' 
    from student;

授权：
系统授权：

grant create table to zhangSan;

回收权限：

revoke create table from zhangSan;

对象授权：(C表上的选择操作赋予所用用户,并且可以将此权利赋予他人)

grant  select on C to public
with grant option;

回收授权：

revoke select on c from user3;