SQL查询二之分组统计

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

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/zzpfsy.html