查询没有报名的学生
方法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';
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31546585/viewspace-2168743/ ，如需转载，请注明出处，否则将追究法律责任。
