mysql-学生测试题目stu.txt

游湖

104

2页

6次

2022-12-07

免费下载

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;

of 2

免费下载

mysql

关注

评论