多表查询(连接查询、嵌套查询、exists、集合查询)

---

连接查询：

#查询学生的信息和选课程的信息
SELECT stu.*,sc.*
FROM stu,sc
WHERE stu.sno = sc.sno
#查询每个学生的信息和选修课程的信息和学时
SELECT stu.*,sc.*,ctime
FROM stu,sc,cou
WHERE stu.sno = sc.sno AND sc.cno = cou.cno

#查询所有的学生信息和选课信息，没有选修的学生信息也要展示出来；这里注意上面的查询都是结果都选了课程的；这里需要保留本表的全部信息，需要用到外连接
SELECT stu.*,sc.*
FROM stu LEFT OUTER JOIN sc
ON stu.sno = sc.sno

#查询每个专业的学生人数,假设每个专业都有人
SELECT mno, COUNT(sno)
FROM stu
GROUP BY mno
HAVING mno BETWEEN 1 AND 4;

#查询每个专业的学生人数，但是有的专业可能没人
DELETE FROM
major
WHERE mno = 5;

insert into major VALUES(5,"aa")

SELECT major.mno,COUNT(stu.sno) AS num
FROM major LEFT OUTER JOIN stu
ON major.mno = stu.mno
GROUP BY major.mno

---

 

嵌套查询：