SQL笔记04

--连接查询 等值连接
select stu.*,sc.* from stu,sc where stu.sno=sc.sno
select sname from stu,sc where stu.sno=sc.sno and sc.cno='20201'

--多表连接查询
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
insert into major values(5,'aa');
select major.mno,count(sno) from major left outer join stu on major.mno=stu.mno group by major.mno

--嵌套查询