mysql --排序、聚合、分组

创建表17并插入数据

drop table  if exists  students17;
CREATE table students17(
studentNo int(3) unsigned zerofill  PRIMARY KEY ,
name varchar (20),
sex varchar(2),
age TINYINT(3) ,
hometown varchar(50)  DEFAULT NULL, 
class varchar(10)  DEFAULT NULL,
card varchar(50) DEFAULT NULL
);

insert  into students17 VALUES
('1','王昭君','女',20,'北京','1班','31162100000910018'),
('2','诸葛亮','男',18,'上海','2班','87235958214647020'),
('3','刘玄德', '男',26,'南京','1班','98230764940128343'),
('4','白起','男',22,'安徽','4班','72898231636783201'),
('5','大乔','女',19,'河南','3班','12872396253622358'),
('6','孙尚香','女',18,'河北','1班','32276853291927386'),
('7','百里玄策','男',20,'山西','2班','69283154237972492'),
('8','小乔','女',18,'河南','3班',NULL),
('9','百里守约','男',22,'河南','2班',''),
('10','妲己','女',19,'陕西','1班','77538912786375823'),
('11','李白','男',24,'山东','3班','13467923823989730'),
('12','刘禅','男',18,'陕西','4班','13729023873820233');

1.查询所有同学的年龄，从小到大

select * from students17 order by age asc ;

 2.查询所有同学的年龄，从大到小

select * from students17 order by age desc;

3.查询所有同学的年龄，从小到大，如果相同按照学号从小到大排序

select * from students17 order by age asc ,studentNo;

4. 查询学生总人数 

select count(*) as 学生总人数 from students17 ;

5.查询女生最大年龄

select max(age) as 女生最大的年龄 from students17 where sex='女' ;

6.查询男生最小年龄

select min(age) as 男生最小的年龄 from students17 where sex='男' ;

7.查询河南学生年龄总和

select sum(age) as  河南学生年龄总和 from students17 where hometown='河南';

8.查询女生平均年龄

select sex, avg(age) as 女生平均年龄 from students17 where sex='女';

9.查询各种性别的人数

select sex,count(*) from students17 group by sex ;

10.查询各种年龄的人数

select age,count(*) from  students17 group by age ;

11. 查询男同学人数

select sex,count(*) from students17 group by sex having sex='男';