create database chapter040;
use chapter040;
create table chapter040.student
(
id int primary key,
name varchar(10),
grade int,
gender varchar(10)
);
insert into student
values
(1,'songjiang',40,'nan'),
(2,'wuyong',43,'nv'),
(3,'zhangsa',46,'nan'),
(4,'lisi',49,'nv'),
(5,'wangwu',52,'nan'),
(6,'shuihu',55,'nv'),
(7,'shaoyun',58,'nan'),
(8,'liubei',61,'nv'),
(9,'zhaoliu',64,'nan'),
(10,'tianbo',67,'nv'),
(11,'shaoshao',70,'nan'),
(12,'tianchao',73,'nv'),
(13,'zhanbo',76,'nan'),
(14,'dongyu',79,'nv'),
(15,'mayu',82,'nan'),
(16,'mawu',85,'nv'),
(17,'zhaoyang',88,'nan'),
(18,'xiaoyu',91,'nv'),
(19,'yangzhan',94,'nan'),
(20,'zhanglei',97,'nv'),
(21,'zhangsi',80,'nan'),
(22,'wangnan',82,'nv'),
(23,'zhangyi',84,'nan'),
(24,'tiyan',86,'nv'),
(25,'shehui',88,'nan');
1、统计 student 表中学生人数
select count(*) from student;
2、统计 student 表中男生人数
select count(*) from student where gender='nan';
3、统计 student 表中女生人数
select count(*) from student where gender='nv';
4、统计 student 表中及格学生人数
select count(*) from student where grade>=60;
5、统计 student 表中 80 多分学生人数
select count(*) from student where grade>=80;
6、统计 student 表中 80 多分男生人数
select count(*) from student where grade>=80 and gender='nan';
7、求出 student 表中女生成绩总分
select sum(grade) from student where gender='nv';
8、求出 student 表中学生成绩总分
select sum(grade) from student ;
9、求出 student 表中男生成绩总分
select sum(grade) from student where gender='nan';
10、求出 student 表中及格学生成绩总分
select sum(grade) from student where grade>=60;
11、求出 student 表中不及格学生成绩总分
select sum(grade) from student where grade<60;
12、求出 student 表中学生平均分
select avg(grade) from student;
13、求出 student 表中男生平均分
select avg(grade) from student where gender='nan';
14、求出 student 表中女生平均分
select avg(grade) from student where gender='nv';
15、求出 student 表中及格学生平均分
select avggrade) from student where grade>=60;
16、求出 student 表中不及格学生平均分
select avg(grade) from student where grade<60;
17、求出 student 表中学生最高分
select max(grade) from student;
18、求出 student 表中女生最高分
select max(grade) from student where gender='nv';
19、求出 student 表中男生最高分
select max(grade) from student where gender='nan';
20、求出 student 表中学生最低分
select min(grade) from student;
21、求出 student 表中男生最低分
select min(grade) from student where gender='nan';
22、求出 student 表中女生最低分
select min(grade) from student where gender='nv';
23、对 student 表中学生按成绩进行降序排序
select * from student order by grade desc;
24、对 student 表中学生按成绩进行升序排序
select * from student order by grade asc;
25、对 student 表中男生按成绩进行降序排序
select * from student where gender='nan' order by grade desc;
26、对 student 表中及格学生按成绩进行升序排序
select * from student where grade>=60 order by grade asc;
27、统计 student 表中男、女生人数
select gender,count(*) from student group by gender;
28、计算 student 表中男、女生平均成绩
select gender,avg(grade) from student group by gender;
29、查看 student 表中前四条记录
select * from student limit 4;
30、查看 student 表中成绩值从第 20 名到第 25 名的学生
select * from student order by grade desc limit 19,6;
