select count(*) as 人数 from student where classid = 1 go select sum(sage) as 年龄总和 from student go select avg(sage) as 平均年龄 from student go select max(sage) as 最大年龄 from student go select min(sage) as 最小年龄 from student go select * from student go select * from class go select * from teacher go alter table student drop constraint FK_student_class go insert into student values(1004,'王五',22,'m',4) go insert into class values(5,'五班') go --内联接 select sid,sname,sage,ssex,class.classid,classname from (student inner join class on student.classid = class.classid) go --左外联接 select * from (student left outer join class on student.classid = class.classid) go --右外联接 select * from (student right outer join class on student.classid = class.classid) --全外联接 select * from (student full outer join class on student.classid = class.classid) --多重联接 select * from ((student inner join class on student.classid = class.classid) inner join teacher on class.classid = teacher.classid) where sname = '张山' go --嵌套查询 select classname from class where classid in (select classid from student where sname='张山') go insert into student values(1005,'王五',22,'m',1) go insert into student values(1006,'王五',22,'m',2) go insert into student values(1007,'王五',22,'m',1) go insert into student values(1008,'王五',22,'m',2) go --分组查询 select class.classid,classname,count(*) as 人数 from (student inner join class on student.classid = class.classid) group by class.classid,classname go select class.classid,classname,count(*) as 人数 from (student inner join class on student.classid = class.classid) group by class.classid,classname having count(*) >= all(select count(*) as 人数 from (student inner join class on student.classid = class.classid) group by class.classid,classname) go