SQL语句2——统计查询

select sdept,count(*) 总人数,avg(sage) 平均年龄
from student
group by sdept

--rule1：分组字段一般都会出现在select后
  rule2：如果一个查询select后既有单个列，
         又有统计列，那么该查询必须用到
         分组，而且单个列即为分组字段
   rule3:where是全表筛选，一行一行的进行，
         where后的条件均为原始列的比较
         having是组内筛选，一组一组的进行
         having后的条件均为统计列比较
查询选修的成绩80分以上门数大等于2的学生学号和选课门数
grade>80 count(*)>=2
select sno,count(*) 选课门次,avg(grade) 平均成绩
from sc
where grade>80
group by sno
having count(*)>=2

查询每个院系不同性别的人数和平均年龄、最大和最小年龄
select sdept,ssex,count(*),avg(sage),max(sage),min(sage)
from  student
group by sdept,ssex
order by sdept desc
select * from sc
查询所有男生人数大于2的院系的名称和人数
select sdept,count(*)
from student
where ssex='m'
group by sdept
having  count(*)>=2
select sdept,count(*)
from student
where ssex='m'
group by sdept
统计每个学生的选课门数
select sno,count(*)
from sc
group by sno

select * from student
select * from sc

select *
from student s left outer join sc 
     on s.sno=sc.sno
where cno is not null
order by cno desc
子查询：select中的where里嵌套select
1不相关子查询
select *
from student 
where sno not in (select sno from sc)
查询所有和tom同性的学生的姓名
--规则：子查询查什么列就写什么列
 select sname
 from student
 where ssex in (select ssex from student s where sname='tom')
  
查询即选了c03又选了c01的学生学号
select sno
from student
where sno in (select sno from sc where cno='c01')
   and sno in (select sno from sc where cno='c03')
2相关子查询
查询所有选的课程成绩大于该门课平均成绩的学生的课程号和成绩
select sno,cno,grade
from sc a
where grade>(select avg(grade) from sc b where b.cno=a.cno)