mysql面试练习题

查询没有报名的学生 方法1 SELECT s.* FROM student s LEFT JOIN baoming bm ON s.sid = bm.sid WHERE bm.sid IS NULL; 方法2 SELECT* FROM student WHERE sid NOT IN (SELECT DISTINCT sid FROM baoming); 查出哪个老师的学生最多 方法1 SELECT t. NAME,COUNT(s.sid) 学生数 FROM student s,teacher t,baoming bm,ke WHERE s.sid = bm.sid AND t.tid = ke.tid AND bm.kid = ke.kid GROUP BY t. NAME ORDER BY COUNT(s.sid) DESC LIMIT 0,  1;   方法2  SELECT t. NAME,COUNT(bm.sid) AS 学生数 FROM teacher t JOIN ke ON ke.tid = t.tid JOIN baoming bm ON bm.kid = ke.kid GROUP BY t.name ORDER BY COUNT(bm.sid) DESC LIMIT 1; 理题思路 原始表是student表和class表，查询结果是不是'班级1'的结果，'班级4'有null值，所以需要用right join 第一种 SELECT*FROM student AS s RIGHT JOIN class  AS c ON s.cid = c.cid WHERE c.name!='班级1'; 第二种 SELECT*FROM student AS s RIGHT JOIN class  AS c ON s.cid = c.cid WHERE c.name <>'班级1';