分组必统计，分组查询其实是排序
　1 --使用in查询信息工程系和电子商务系的学生
  2 
  3 --查询信息工程系和电子商务系的学生
  4 
  5 select * from student where stuDept='信息工程系' or stuDept='电子商务系' 
  6 
  7 select * from student where stuDept in('电子商务系','信息工程系')
  8 
  9 
 10 select * from student
 11 
 12 --使用count函数查询全体学生的人数
 13 
 14 select count(stuId) as 人数 from student
 15 
 16 select count(*) as 人数 from student
 17 
 18 
 19 /********************************/
 20 --分组必统计
 21 --使用group分组查询各系学生的数量
 22 
 23 --男生女生各多少人
 24 
 25 
 26 
 27 select * from student
 28 
 29 select stuSex, max(stuAvgrade) from student
 30 group by stuSex
 31 
 32 --查询男生和女生都有谁：(分组查询信息-都有谁-:是排序不是分组)
 33 select stuSex, * from student
 34 order by student.stuSex
 35 
 36 --各系学生的数量
 37 select stuDept,count(*) as 人数 from student
 38     group by stuDept
 39 
 40 select * from student
 41 
 42 计算机系  男 3
 43 计算机系  男 1
 44 电子商务系 男 1
 45 电子商务系 女 1
 46 
 47     
 48 --各系男生女生各多少人
 49 select stuDept, stuSex,count(*) as 人数 from student
 50     group by stuDept,stuSex
 51 
 52     
 53 select stuDept, stuSex,count(*) as 人数 from student
 54     group by stuDept, stuSex
 55     
 56 --各系学生总分数
 57 
 58 
 59 select stuDept, sum(stuAvgrade) as 总成绩 from student
 60         group by stuDept
 61 
 62 --每个系的成绩最好的
 63 select stuDept, Max(stuAvgrade) as 最好的一个 from student
 64         group by stuDept
 65 
 66 select stuDept, Min(stuAvgrade) as 最差劲的一个 from student
 67         group by stuDept
 68 
 69 
 70 select stuDept, avg(stuAvgrade) as 平均 from student
 71         group by stuDept
 72 
 73 select * from student
 74 
 75 --统计各系的男生和女生各多少人
 76 select stuDept,stuSex,COUNT(*) from student
 77 group by stuDept, stuSex
 78 order by stuDept --order by 排序
 79 
 80 
 81 --查询各系学生信息
 82 
 83 
 84 
 85 select * from student
 86 group by stuDept,stuId,stuName
 87 
 88 select stuDept, stuName, stuSex, stuBirth, stuSpeciality, stuAvgrade  from student 
 89 group by stuDept, stuName, stuSex, stuBirth, stuSpeciality, stuAvgrade --这样写是可以的,其实组到最后，会发现等同于select * from student,也就是过分分组等于没有分组
 90 
 91 --查询各系学生的信息，不是分组，因为分组必统计，这里其实是按系进行排序的概念
 92 select student.stuDept, student.* from student
 93 order by student.stuDept
 94 
 95 --查询每个系的各专业的学生人数
 96 select stuDept, stuSpeciality, count(*) from student
 97 group by stuDept, stuSpeciality
 98 
 99 --查询每个系的各专业的最好成绩
100 
101 
102 
103 select stuDept, stuSpeciality, max(stuAvgrade) from student
104 group by stuDept, stuSpeciality
105 
106 
107 
108 
109 select stuDept, stuName, stuSex, stuBirth, stuSpeciality, stuAvgrade  from student 
110 order by stuDept -- order by 是排序关键字 dian, ji, xin
111 
112 select * from student order by stuAvgrade Desc --desc是降序，默认值是Asc
113 
114 select * from student order by stuAvgrade Asc
115 
116 
117 --使用having子句查询人数大于2的系
118 
119 --查询人数大于2的系
120 
121 --select stuDept from student where count(*) > 2
122 
123 
124 --//where子句是用于分组前的条件筛选//
125 select stuDept from student
126 where count(*) > 2
127 group by stuDept --非法，where条件部分不能有聚合函数 
128 
129 --select stuDept from student where count(*) > 2 group by stuDept 这样的写法是我们很自然就想到的，但是是非法，因为在Sql中不能在where条件后使用有计算的表达式，如聚合函数
130 
131 
132 --//having子句用于分组后的筛选
133 select stuDept, count(*) as 人数 from student
134 group by stuDept
135 having count(*) >= 2 
136 
137 select * from student
138 
139 
140 
141 
142 --查询人数大于1的系并且，不能是计算机系
143 
144 --能在分组前的where子句中筛选的就一定要放在where子句中
145 select stuDept from student
146 group by stuDept
147 having count(*) >= 2 and stuDept <> '计算机系'
148 
149 select stuDept from student
150 where stuDept <> '计算机系'
151 group by stuDept
152 having count(*) >= 2 
153 
154 
155 
156 
157 select stuDept as 系, count(*) as 人数, sum(stuAvgrade) as 总成绩, avg(stuAvgrade) as 平均成绩, max(stuAvgrade) as 最好成绩 from student
158 group by stuDept
159 having count(*) > 2
160 
161 --查询平均成绩大于全体学生平均成绩的学生的信息
162 
163 
164 select * from student 
165 where stuAvgrade > (
166     select AVG(stuAvgrade) from student
167 )