常见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必须得配合聚合函数来用,分组之后你可以计数(COUNT),求和(SUM),求平均数(AVG)等。
-- 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