🎃个人专栏：
🐬 算法设计与分析：算法设计与分析_IT闫的博客-CSDN博客
🐳Java基础：Java基础_IT闫的博客-CSDN博客
🐋c语言：c语言_IT闫的博客-CSDN博客
🐟MySQL：数据结构_IT闫的博客-CSDN博客
🐠数据结构：数据结构_IT闫的博客-CSDN博客
💎C++：C++_IT闫的博客-CSDN博客
🥽C51单片机：C51单片机（STC89C516）_IT闫的博客-CSDN博客
💻基于HTML5的网页设计及应用：基于HTML5的网页设计及应用_IT闫的博客-CSDN博客
🥏python：python_IT闫的博客-CSDN博客
欢迎收看，希望对大家有用！
目录
🎯问题及答案：
- 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
方法1
select s.sid,s.sname,s.sbirth,s.ssex,sc1.score,sc2.score from student s,sc sc1,sc sc2 where sc1.cid='01' and sc2.cid='02' and sc1.score>sc2.score and sc1.sid=sc2.sid and s.sid=sc1.sid;
方法2
SELECT s.sid,s.sname,s.sbirth,s.ssex,a.score,b.score
FROM(SELECT * FROM sc WHERE cid='01')a,
(SELECT * FROM sc WHERE cid='02')b ,student s WHERE a.sid=b.sid AND s.sid=a.sid
AND a.score>b.score;
2、查询所有的男生姓名
SELECT * FROM student WHERE ssex='男'
3.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select s.sid,s.sname,avg(sc.score) from student s,sc group by s.sid having avg(sc.score)>=60;
4、查询名字中含有"风"字的学生信息
select * from student where sname like ‘%风%’;
5、查询课程名称为"数学"，且分数低于60的学生姓名和分数
SELECT s.sname,score FROM student s,sc,course
WHERE s.sid=sc.sid AND sc.cid=course.cid AND cname='数学' AND score<60;
6、查询所有学生的课程及分数情况；
SELECT sname,cname,score FROM student,sc,course WHERE student.sid=sc.sid AND sc.cid=course.cid;
7.查询没学过"张三"老师授课的同学的信息
SELECT s.* FROM student s WHERE s.sid NOT IN
(SELECT sc1.sid FROM sc sc1,course c,teacher t
WHERE t.tid=c.tid AND sc1.cid=c.cid AND t.tname='张三');
8.查询学过"张三"老师授课的同学的信息
SELECT s.* FROM student s ,sc sc1,course c,teacher t
WHERE s.sid=sc1.sid AND sc1.cid=c.cid AND c.tid=t.tid AND t.tname='张三';
9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT s.* FROM student s,sc sc1,sc sc2 WHERE s.sid=sc1.sid AND sc1.sid=sc2.sid AND sc1.cid='01' AND sc2.cid='02';
10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT s.* ,sc.* FROM student s,sc
WHERE s.sid=sc.sid AND cid='01' AND s.sid NOT IN (SELECT sid FROM sc WHERE cid='02')
11、查询没有学全所有课程的同学的信息
思路：查询学生存在他没选的课
SELECT sname
FROM student
WHERE EXISTS
( SELECT *
FROM course
WHERE NOT EXISTS
( SELECT *
FROM sc
WHERE sc.cid=course.cid
AND student.sid=sc.sid
)
)
12、查询至少有一门课与学号为"01"的同学所学课程相同的同学的信息
SELECT sname
FROM student
WHERE EXISTS
( SELECT *
FROM sc sc1
WHERE sc1.sid='01' AND EXISTS
( SELECT *
FROM sc sc2
WHERE sc1.cid=sc2.cid
AND student.sid=sc2.sid
)
)
13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
SELECT student.sid,sname
FROM student
WHERE NOT EXISTS
( SELECT *
FROM sc sc1
WHERE sc1.sid='01' AND NOT EXISTS
( SELECT *
FROM sc sc2
WHERE sc1.cid=sc2.cid
AND student.sid=sc2.sid
)
)
14、查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT s.* FROM student s WHERE s.sid NOT IN(SELECT sc1.sid FROM sc sc1,course c,teacher t WHERE sc1.cid=c.cid AND c.tid=t.tid AND t.tname='张三');
15.查询出只选修两门课程的全部学生的学号和姓名
SELECT s.* FROM student s,sc WHERE s.sid=sc.sid
GROUP BY sc.sid HAVING COUNT(sc.sid)=2
16、查询1990年出生的学生名单(注：Student表中sbirth列的类型是datetime)
SELECT * FROM student
WHERE sbirth BETWEEN '1990-01-01' AND '1990-12-31';
SELECT s.* FROM student s WHERE sbirth LIKE '1990-%'（方法2）
17、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select sc.cid,avg(score) from sc group by sc.cid order by avg(score) DESC , sc.cid;
18、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
select s.sname,c.cname,score from student s,sc,course c where s.sid=sc.sid and sc.cid=c.cid and score>70;
19、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select s.sname,avg(score) from sc,student s where s.sid=sc.sid group by sc.sid having avg(score)>=85;
20、查询有不及格课程的学生姓名与课程名称
select s.sname,c.cname,score from student s,sc,course c where s.sid=sc.sid and sc.cid=c.cid and score<60;
21、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；
SELECT s.sid,s.sname FROM student s,sc WHERE sc.sid=s.sid AND sc.cid=1 AND score>=80;
22、求每门课程的学生人数
select cid,count(sid) from sc group by sc.cid;
23、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select cid,count(sid) from sc group by cid having count(sid)>5 order by count(sid),cid ASC;
24、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT DISTINCT s1.sid,s2.sid,sc1.cid,sc1.score,sc2.score FROM student s1,student s2,sc sc1,sc sc2 WHERE s1.sid!=s2.sid AND s1.sid=sc1.sid AND s2.sid=sc2.sid AND sc1.cid!=sc2.cid AND sc1.score=sc2.score;
25、检索至少选修两门课程的学生学号
select sid from sc group by sid having count(cid)>=2;
26、查询选修了全部课程的学生信息
SELECT *
FROM student
WHERE NOT EXISTS(
SELECT * FROM course
WHERE NOT EXISTS (
SELECT * FROM sc
WHERE sc.`cid`=course.`cid` AND student.`sid`=sc.`sid`
)
)
27.查询各学生的年龄
SELECT s.sname,YEAR(CURDATE())-YEAR(s.sbirth) AS age FROM student s;
28、查询本月过生日的学生
SELECT s.sname,sbirth FROM student s WHERE MONTH(s.sbirth)=MONTH(CURDATE());
29、查询下月过生日的学生
SELECT s.sname,sbirth FROM student s WHERE MONTH(s.sbirth)=MONTH(CURDATE())+1;
30.查询学全所有课程的同学的信息
SELECT *
FROM student
WHERE NOT EXISTS(
SELECT * FROM course
WHERE NOT EXISTS (
SELECT * FROM sc
WHERE sc.`cid`=course.`cid` AND student.`sid`=sc.`sid`
)
)
来源地址：https://blog.csdn.net/shsjssnn/article/details/133930813
