mySQL(13)-多表查询

承接上篇，MARK一下多表查询。

 

有个常犯的错误要特别注意：
完全相同， 不是In 而是not in的思路，详见第7题。
 

-- 多表查询：

-- 1.查询平均成绩大于85的所有学生的学号、姓名和平均成绩 
-- 2表查询+返回值(原始字段+计算值)
-- 两表查询：student, sc；返回值:s.sid,s.sname.avg(sc.score)
-- note： 筛选条件：分组后用having avg(sc.score)>85  而不是where
  select s.sid,s.sname,avg(score) from sc 
                                  inner join student s on sc.sid=s.sid
	                                group by s.sid ,s.sname having avg(sc.score)>85
	     
-- 2.查询课程名为“睡觉”，且分数低于60的学生姓名和分数
-- 3表查询+返回值(原始字段)
-- 3表查询 student,sc,course； 返回值: s.sname,sc.score
-- note：关联查询：inner join ..on 
      -- 条件直接堆后面
select s.sname,sc.score from studnet s inner join sc  on s.sid=sc.sid
							           inner join course c on s.cid=c.cid
							           where c.cname='睡觉'
							                 and sc.score<60
-- 3.查询所有学生的选课情况
-- 3表查询+返回值(原始字段)
-- 3表查询 student,sc,course； 返回值:s.sname,c.cname
-- student 全集，
-- note： 关联查询：student 全集 所以left join  
select s.sname,c.cname from student s 
	                 left join sc on s.sid=sc.sid
					         left join course c on sc.cid=c.cid
									 
   
-- 4.查询任何一门课程成绩在70分以上的姓名、课程名称和分数
-- 3表查询+返回值(原始字段)
-- 3表查询 student,sc,course; 返回值:s.sname,c.cname,sc.score
-- note: 关联查询：inner join ..on 
       -- 筛选条件： where sc.score>70
  select s.sname,c.cname,sc.score from student s
	     inner join sc on s.sid=sc.sid
		   inner join course c on sc.cid=c.cid
	     where sc.score>70 
	
	

-- 5.查询每门课程被选修的学生数
-- 2表查询+返回值(原始字段，计算字段)
-- 2表查询 sc,course； 返回值: c.cname,count（*）(计算)
-- note：关联查询  inner join ..on
    --   分组：每门课 group by c.cname(或者c.cid只要唯一标识就好)
select c.cname ,count(*) from course c 
		        inner join sc sc on c.cid=sc.cid
				    group by c.cname
														 
-- 6.查询不同老师所教不同的课程平均分从高到低显示
-- 3表查询+返回值(原始字段，计算字段)
-- 3表查询 sc,course,teacher；返回值: t.tname,c.cname,avg(sc.score)
-- note： 关联查询 inner join
--        分组：每门课 group by c.cname,t.tname
--        从高到低显示:order by 
 select t.tname,c.cname,avg(sc.score) from teacher t 
		                              inner join course c on t.tid=c.tid
									  inner join sc  on c.cid=sc.cid
									  group by t.tname,c.cname
																				 
		
		
-- 7. 查询和“2”号的同学学习的课程完全相同的其他同学学号和姓名
-- 2表查询+返回值(原始字段)
-- 2表查询： student sc; 返回值: s.sid,s.sname
-- note ：完全相同的科目名+相同的数量+其它同学
    -- not in 而不是in
		-- count(*)=count()
		-- ！=
-- 1.科目名 科目名学过和没学过两种情况
--  1.1.2号同学学习的课程 select sc.cid from sc where sc.cid=2  返回课程id 
--  1.2.学过的同学  select sc.sid from sc where sc.cid in(select sc.cid from sc where sc.cid=2) 括号里为课程id
-- 1.3.没学过的同学  select sc.sid from sc where sc.cid not in(select sc.cid from sc where sc.cid=2)
-- 1.4.把没学过的摘出去  
--         select sc.sid from sc where sc.cid not in(
        -- select sc.sid from sc where sc.cid not in(select sc.cid from sc where sc.cid=2))
-- 2.数量 按这个条件分组
   -- group by sc.sid having count(*)=select count(*) from sc where sc.sid=2

	-- select * from sc  where sc.cid not in(
    -- select sc.sid from sc where sc.cid not in( 
    -- select sc.cid from sc where sc.sid=2)) 
    -- group by sc.sid having count(*)=（select count(*) from sc where sc.sid=2）

-- 3.其他人sc.sid!=2
     select s.sid ,s.sname from student s where s.sid in(
          select * from sc  where sc.cid not in(
               select sc.sid from sc where sc.cid not in( 
               select sc.cid from sc where sc.sid=2)
       )and sc.sid!=2 
          group by sc.sid having count(*)=（select count(*) from sc where sc.sid=2）
)


-- 9.查询学过“黄观”老师所教的所有课的同学的学号、姓名
-- 3表查询+返回值(原始字段)
-- 3表查询： student ,teacher,sc,course 返回值: s.sid,s.sname
-- note ：
-- 1.黄观老师教的所有课(2表关联 inner join in 
--                         筛选 where )
     -- select c.cid from course c 
		 --    inner join teacher t on c.tid=t.tid
		 --   where  t.tname='黄观
--  2.学过课的学生（3表关联 inner join in
--                    筛选 where
--                    子查询

-- 		select sc.sid,sc.cid  from sc where sc.cid in (
      --        select c.cid from course c inner join  teacher t on c.tid=t.tid	where t.tname='黄观')

-- 4.数量 老师教课的数量count(c.cid)
          -- select count(c.cid) from course c inner join teacher t on c.tid=t.tid where t.tname='黄观'
    --  学生学的课 数量  学生分组 计数 group by sc.sid having count(*)
    -- 两数量一样
      select sc.sid  from sc where sc.cid in(
                           select c.cid from course c inner join teacher t on c.tid=t.tid where  t.tname=‘黄观‘)
    group by sc.sid having count(*)= (select count(*) from course c inner join teacher t on c.tid=t.tid where t.tname='黄观'
		
														


-- 10.查询没学过“黄观”老师课的同学的学号、姓名
-- 4表查询+返回值(原始字段)
-- A."黄观"老师教过什么课  两表联合查询（t,c） 返回的是课程 c.id
     -- select c.cid from course c inner join  teacher t on c.tid=t.tid   where t.tname='黄观'
         
-- B.谁学过这些课（学生，课程， 三表查询(s,sc,c )， 学过什么课 所以条件: sc.cid)
               select sc.sid  from sc where sc.cid in(
                 select c.cid from course c inner join  teacher t on c.tid=t.tid  -- 前面查询返回的结果
                                                          where t.tname='黄观'）
-- c.学生没学过 not in
    --  此处为什么不可以直接在B的基础上直接把in 改成 not in ? 因为你要返回的是student的表的字段，而不是 sc表的字段    --
    select * from student where sid not in(
         select sc.sid from sc where sc.cid in(
              select c.cid from course c inner join  teacher t on c.tid=t.tid where t.tname='黄观'))

 

备注：数据表和题目是网上找来，注释和总结是我写的，希望对大家写sql时有所启发与帮助。