【笔记六】：分组查询

1，分组查询

select <列名>, count(*) from <表名> group by <列名>;

查询每个班级的人数 

MariaDB [mydb]> select class, count(*) from score group by class;
+-------+----------+
| class | count(*) |
+-------+----------+
|     1 |        4 |
|     2 |        1 |
|     3 |        2 |
+-------+----------+
3 rows in set (0.001 sec)

查询人数大于2的班级

MariaDB [mydb]> select class, count(*) from score group by class having count(*)>2;
+-------+----------+
| class | count(*) |
+-------+----------+
|     1 |        4 |
+-------+----------+
1 row in set (0.002 sec)

查询数学大于80的各班人数

MariaDB [mydb]> select class, count(*) from score where math > 80 group by class;
+-------+----------+
| class | count(*) |
+-------+----------+
|     1 |        3 |
|     3 |        1 |
+-------+----------+

查询数学大于80并且人数大于1的班级

MariaDB [mydb]> select class, count(*) from score where math > 80 group by class having count(*)>1;
+-------+----------+
| class | count(*) |
+-------+----------+
|     1 |        3 |
+-------+----------+
1 row in set (0.002 sec)

查询每个班下的男生和女生人数

MariaDB [mydb]> select * from score;
+-------+-------+-----------+------+------+----------+--------+
| id    | class | name      | sex  | math | language | sports |
+-------+-------+-----------+------+------+----------+--------+
| 20001 |     1 | 小明      | 男   |   81 |       86 |     93 |
| 20002 |     3 | 小红      | 女   |   86 |       86 |     89 |
| 20003 |     2 | 小张      | 男   |   77 |       83 |     93 |
| 20004 |     1 | 露丝      | 女   |   88 |       78 |     65 |
| 20005 |     1 | 丽丽      | 女   |   92 |       94 |     64 |
| 20006 |     3 | 李明      | 男   |   75 |       78 |     88 |
| 20007 |     1 | 张大明    | 男   |   54 |       65 |     95 |
+-------+-------+-----------+------+------+----------+--------+
7 rows in set (0.000 sec)

MariaDB [mydb]> select class, sex, count(*) from score group by class,sex; 
+-------+------+----------+
| class | sex  | count(*) |
+-------+------+----------+
|     1 | 女   |        2 |
|     1 | 男   |        2 |
|     2 | 男   |        1 |
|     3 | 女   |        1 |
|     3 | 男   |        1 |
+-------+------+----------+
5 rows in set (0.001 sec

MariaDB [mydb]> select class, sex, count(*) from score group by class,sex having count(*) > 1; 
+-------+------+----------+
| class | sex  | count(*) |
+-------+------+----------+
|     1 | 女   |        2 |
|     1 | 男   |        2 |
+-------+------+----------+
2 rows in set (0.002 sec)

2，分页显示 

select * from <表名> limit <起始位置>, <显示条数>;

MariaDB [mydb]> select * from score;
+-------+-------+-----------+------+------+----------+--------+
| id    | class | name      | sex  | math | language | sports |
+-------+-------+-----------+------+------+----------+--------+
| 20001 |     1 | 小明      | 男   |   81 |       86 |     93 |
| 20002 |     3 | 小红      | 女   |   86 |       86 |     89 |
| 20003 |     2 | 小张      | 男   |   77 |       83 |     93 |
| 20004 |     1 | 露丝      | 女   |   88 |       78 |     65 |
| 20005 |     1 | 丽丽      | 女   |   92 |       94 |     64 |
| 20006 |     3 | 李明      | 男   |   75 |       78 |     88 |
| 20007 |     1 | 张大明    | 男   |   54 |       65 |     95 |
+-------+-------+-----------+------+------+----------+--------+
7 rows in set (0.000 sec)

MariaDB [mydb]> select * from score limit 2,3;
+-------+-------+--------+------+------+----------+--------+
| id    | class | name   | sex  | math | language | sports |
+-------+-------+--------+------+------+----------+--------+
| 20003 |     2 | 小张   | 男   |   77 |       83 |     93 |
| 20004 |     1 | 露丝   | 女   |   88 |       78 |     65 |
| 20005 |     1 | 丽丽   | 女   |   92 |       94 |     64 |
+-------+-------+--------+------+------+----------+--------+
3 rows in set (0.001 sec)

 

select 列名 from 表名
    where  限定条件
        group by 分组的列
            [having  分组之后的筛选条件]
                order by 排序的列  desc/asc
例如：
select studsex,schoolno ,count(*) from studentnew where studteacher ='李老师' GROUP BY schoolno,studsex HAVING count(*)>5;

select * from 表名
    where 筛选条件
        geoup by  --> 分组
            having  -->  分组后的筛选条件
                order by 排序的列  asc/desc
                 limit 1,5开始位置,显示条数
注：where,order by ,limit 可以单独出现，也可以组合出现