常见MYSQL查询试题

-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT A.s_id,A.s_score
FROM (SELECT * FROM score WHERE c_id=1) A,
(SELECT * FROM score WHERE c_id=2 ) B
WHERE A.s_id=B.s_id
AND A.s_score>B.s_score;

-- 2、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT A.s_id,A.s_name,AVG(B.s_score) FROM student A,score B where A.s_id=B.s_id GROUP BY a.s_id HAVING AVG(B.s_score)>=60;

GROUP BY我们可以先从字面上来理解，GROUP表示分组，BY后面写字段名，就表示根据哪个字段进行分组，如果有用Excel比较多的话，GROUP BY比较类似Excel里面的透视表。
GROUP BY必须得配合聚合函数来用，分组之后你可以计数（COUNT），求和（SUM），求平均数（AVG）等。

当然提到GROUP BY 我们就不得不提到HAVING，HAVING相当于条件筛选，但它与WHERE筛选不同，HAVING是对于GROUP BY对象进行筛选。

-- 3、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)
SELECT A.s_id,A.s_name,AVG(B.s_score)
FROM student A,score B where A.s_id=B.s_id
GROUP BY a.s_id
HAVING AVG(B.s_score)<=60 OR AVG(B.s_score) is null;

-- 4、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select A.s_id,A.s_name,COUNT(A.s_id) as c_num,SUM(B.s_score) as c_sum
from student A,score B
where A.s_id=B.s_id
GROUP BY A.s_id;

-- 5、查询"李"姓老师的数量
select COUNT(A.t_name)
from teacher A
where A.t_name like '李%';

-- 6、查询学过"张三"老师授课的同学的信息
SELECT B.*
from score A,student B,course C,teacher D
WHERE A.s_id=B.s_id and A.c_id=C.c_id and D.t_name='张三' and C.t_id=D.t_id
GROUP BY B.s_id

-- 7、查询没学过"张三"老师授课的同学的信息
SELECT S.*
FROM Student S
WHERE
NOT EXISTS
(SELECT S.s_id
FROM Score X,Course C,Teacher T
WHERE X.c_id=C.c_id AND C.t_id=T.t_id AND T.t_name='张三' AND S.s_id=X.s_id)

-- 8、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT DISTINCT S.* FROM Score C,Student S
WHERE S.S_ID=C.S_ID AND C_ID=1
UNION
SELECT DISTINCT S.* FROM Score C,Student S
WHERE S.S_ID=C.S_ID AND C_ID=2

-- 9、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT * FROM STUDENT S WHERE S.S_ID
IN (SELECT DISTINCT S.S_ID FROM Score C,Student S WHERE S.S_ID=C.S_ID AND C_ID=1)
AND S.S_ID NOT IN (SELECT DISTINCT S.S_ID FROM Score C,Student S WHERE S.S_ID=C.S_ID AND C_ID=2)

　　in 和 exists的区别: 如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键)，如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了 ，另外IN时不对NULL进行处理。in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。

如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

-- 10、查询没有学全所有课程的同学的信息
select X.* from student X where X.s_id IN (select s.s_ID from score S group by s.s_ID having count(s.s_ID)<3)

-- 11、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select DISTINCT S.* from student S,score where score.s_id in (select score.c_id from score where score.s_id=1) and S.s_id!=1

在表中，可能会包含重复值。这并不成问题，不过，有时您也许希望仅仅列出不同（distinct）的值。

关键词 DISTINCT 用于返回唯一不同的值。

-- 12、查询和"01"号的同学学习的课程完全相同的其他同学的信息
select A.* from
student A,score B where A.s_id=B.s_id and A.s_id!=01 and A.s_id not in
(select s_id from score where c_id not in (select c_id from score where s_id =01)) GROUP BY B.s_id
having count(B.s_id) = (select count(*) from score where s_id =01)

这里我百度了很久，我都没有找到答案，然后自己写了这个：先查询01号学生选择的课程，然后查询选了跟01号不同课程的学生，把他们去掉，再加一个所学课程数目相同的条件，剩下的就是和"01"号的同学学习的课程完全相同的其他同学的信息。

-- 13、查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT S_ID,S_NAME FROM STUDENT WHERE S_ID NOT IN
(SELECT C.S_ID
FROM SCORE C,COURSE E,TEACHER T
WHERE C.C_ID=E.C_ID AND E.T_ID=T.T_ID AND T.T_NAME='张三');

-- 14、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT A.s_id,A.s_name,AVG(B.s_score)
FROM student A,score B
where A.s_id=B.s_id and
B.s_id in (select s_id from score where s_score<60 GROUP BY s_id HAVING COUNT(s_id)>=2)
GROUP BY a.s_id

-- 15、检索"01"课程分数小于60，按分数降序排列的学生信息
select A.*,B.s_score from student A,score B where A.s_id=B.s_id and B.s_score<60 and B.c_id=01 ORDER BY B.s_score DESC

-- 16、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select s_id,max(case c_id when '01' then s_score else 0 end)'01',
max(case c_id when '02' then s_score else 0 end)'02',
max(case c_id when '03' then s_score else 0 end)'03',AVG(s_score)平均分 from score
group by s_id order by 平均分 desc

-- 17.查询各科成绩最高分、最低分和平均分：
-- 以如下形式显示：课程 ID ，课程 name ，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select distinct A.c_id,c_name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率 from score A
left join
course on A.c_id=course.c_id
left join
(select c_id,MAX(s_score)最高分,MIN(s_score)最低分,AVG(s_score)平均分 from score group by c_id)B on A.c_id=B.c_id
left join
(select c_id,(sum(case when s_score>=60 then 1 else 0 end)*1.00)/COUNT(*)*100 及格率 from score group by c_id)C on A.c_id=C.c_id
left join
(select c_id,(sum(case when s_score >=70 and s_score<80 then 1 else 0 end)*1.00)/COUNT(*)*100 中等率 from score group by c_id)D on A.c_id=D.c_id
left join
(select c_id,(sum(case when s_score >=80 and s_score<90 then 1 else 0 end)*1.00)/COUNT(*)*100 优良率 from score group by c_id)E on A.c_id=E.c_id
left join
(select c_id,(sum(case when s_score >=90 then 1 else 0 end)*1.00)/COUNT(*)*100 优秀率 from score group by c_id)F on A.c_id=F.c_id

-- 18、查询下月过生日的学生
select * from Student s where ((DATE_FORMAT(s.s_birth, '%m'))-(DATE_FORMAT(curdate(),'%m')))=1;

-- 19、查询本月过生日的学生
select * from Student s where ((DATE_FORMAT(s.s_birth, '%m'))-(DATE_FORMAT(curdate(),'%m')))=0

-- 20、查询下周过生日的学生
select * from Student s where (DATE_FORMAT(s.s_birth, '%m%d')) BETWEEN (DATE_FORMAT(curdate(),'%m%d'))+7 and (DATE_FORMAT(curdate(),'%m%d'))+13

-- 21、查询本周过生日的学生
select * from Student s where (DATE_FORMAT(s.s_birth, '%m%d')) BETWEEN (DATE_FORMAT(curdate(),'%m%d')) and (DATE_FORMAT(curdate(),'%m%d'))+7

-- 22、查询各学生的年龄,按照出生日期来算，当前月日 < 出生年月的月日则年龄减一
select s.*,convert(((DATE_FORMAT(curdate(),'%Y%m%d'))-DATE_FORMAT(s.s_birth, '%Y%m%d'))/10000,DECIMAL(2,0)) s_age from Student s GROUP BY s.s_id