示例:select sex,count(*) from student group by sex having count(*) > 3
示例:Select clzid from student where clzid > 1 group by clzid
6.使用inner join关键字内连接查询两表共有的部分:
示例:Select * from student inner join clazz on student.clzid = clazz.id
示例:Select * from student,clazz where student.clzid = clazz.id
7.使用left join左连接,先把左边的表全部查询出来,再查出来右表共同的部分数据:
示例:Select * from student left join clazz on student.clzid = clazz.id
示例:Select * from student left join clazz on student.clzid = clazz.id where clazz.cid is null
8.使用right join左连接查出左表除共同部分以外的数据,右表数据为空:
示例:Select * from student right join clazz on student.clzid = clazz.id
示例:Select * from student right join clazz on student.clzid = clazz.id where clazz.cid is null
9.多表联合查询第三行语句就是对前两张表查询完的一个结果进行左连接:
示例:Select * from student
inner join clazz on student.clzid = clazz.id
Left join subject on student.id = subject.id
10.子查询:把一张表的查询结果当成一个条件:
示例:Select * from student where clzid = (Select cid from clazz where name =’王老师’)
示例:Select * from student where clzid in (select cid from clazz)