分组必统计，分组查询其实是排序
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 )
