mysql经典50题（1-10）

表依次是：students,scores,course,teachers

-- *1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数 -- select st.s_name, sc1.s_score s1_score ,sc2.s_score s2_score

-- from students st 
-- left join score sc1 on st.s_id = sc1.s_id and sc1.c_id='01'
-- left join score sc2 on st.s_id = sc2.s_id and sc2.c_id ='02'
-- where sc2.s_score < sc1.s_score;


#可以再次理解外连接和内连接的区别，外连接是将一个表看成主表，
#主表将会完成输出，当副表没有与主表相匹配的值就自动填充为NULL

#内连接则是得到两者的交集，只输出交集

-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

--         select st.s_name,sc1.s_score,sc2.s_score
--         from students st
--         left join score sc1 on sc1.s_id = st.s_id and sc1.c_id = '01'
--         left join score sc2 on sc2.s_id = st.s_id and sc2.c_id = '02'
--         where sc1.s_score < sc2.s_score;

-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

--         select st.s_name, sc1.score
--         from students st
--         right join (select s_id, avg(s_score) score from score group by s_id having     avg(s_score) > 60) sc1
--         on sc1.s_id = st.s_id

#参考        
-- select st.s_id,st.s_name,ROUND(AVG(sc.s_score),2) "平均成绩" from students st
-- left join score sc on sc.s_id=st.s_id
-- group by st.s_id having AVG(sc.s_score)>=60

    
-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩-- (包括有成绩的和无成绩的)

-- select st.s_id,st.s_name, 
-- (case when Round(avg(sc.s_score),2) is null then 0 
-- else Round(avg(sc.s_score),2)
-- end)
-- from students st
-- left join score sc on sc.s_id = st.s_id
-- group by st.s_id having AVG(sc.s_score)<60 or AVG(sc.s_score) is NULL;

-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

-- select sc.s_id,st.s_name, count(sc.c_id) 'num_class',
-- (case when sum(sc.s_score) is NULL then 0 else sum(sc.s_score) end) 'sum_score'
-- from students st 
-- left join score sc on st.s_id = sc.s_id
-- GROUP BY st.s_id;

-- 6、查询"李"姓老师的数量 

--         select count(t_id) from teacher where t_name LIKE "李%";

-- *7、查询学过"张三"老师授课的同学的信息 

-- select st.s_id,st.s_name
-- from students st
-- left join score s on st.s_id = s.s_id
-- left join course c on c.c_id = s.c_id
-- left join teacher t on t.t_id = c.t_id
-- where t.t_name='张三';

-- 8、查询没学过"张三"老师授课的同学的信息 

-- select st.s_id,st.s_name
-- from students st
-- WHERE st.s_id not in (
-- SELECT st.s_id
-- FROM students st
-- left join score s on s.s_id = st.s_id
-- left join course c on c.c_id = s.c_id
-- left join teacher t on t.t_id = c.t_id
-- where t.t_name='张三'
-- );
-- 

-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

-- select st.s_id,st.s_name from students st
-- left join score s1 on st.s_id = s1.s_id and s1.c_id='01'
-- left join score s on st.s_id = s.s_id and s.c_id ='02'
-- where s1.s_id = s.s_id and s1.s_id is not null and s.s_id is not null;

#参考
-- select st.s_id,st.s_name from students st
-- join score sc on st.s_id = sc.s_id and sc.c_id='01'
-- where sc.s_id in (select st2.s_id from students st2
-- join score sc2 on st2.s_id = sc2.s_id and sc2.c_id='02')

#这1题，第9题，第10题可以明显的看出内连接和外连接的区别

 

-- *10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

-- select st.s_id,st.s_name from students st
-- join score sc on st.s_id = sc.s_id and sc.c_id='01'
-- where sc.s_id not in (select st2.s_id from students st2
-- join score sc2 on st2.s_id = sc2.s_id and sc2.c_id='02')