六、sql server 分组查询、联合查询

1.分组查询

select ClassId 班级编号 , sex 性别 , count(*) 人数 , avg(age) 平均年龄
from Students 
where StuName <> '张无忌'
group by ClassID,Sex
having avg(age)>18
order by ClassID asc;

 

2.联合查询

【内连接】inner join

--sql92
select StuNo,StuName,Age,ClassName
from Students,ClassInfo
where Students.ClassID = ClassInfo.ClassID;

--sql99  内连接
select StuNo,StuName,Age,ClassName
from Students s
inner join ClassInfo c
on s.ClassID = c.ClassId;

==》如果还想加上两个表的公共属性ClassId，那么要说明该属性属于哪一个表，如：Students.ClassId

【外连接】左外连接 left join、右外连接 right join

--右外连接
select StuNo,StuName,Age,ClassName
from Students s
right join  ClassInfo c
on s.ClassID = c.ClassId;