目录
20)查询男生和女生分别有多少人,并将人数降序输出,并查询出大于9的性别人数信息
步骤:
1、创建学生表
命令:
create table student(
id int,
name varchar(20),
gender varchar(20),
chinese int,
math int,
english int
);
2、插入数据
命令:
insert into student values
(1,'张明','男',89,78,90),
(2,'李进','男',67,53,95),
(3,'王五','女',87,78,77),
(4,'李一','女',88,98,92),
(5,'李财','男',82,84,67),
(6,'张宝','男',55,85,45),
(7,'黄蓉','女',75,65,30),
(7,'黄蓉','女',75,65,30);
1)查询表中所有学生的信息
命令:
select *from student;
2)查询表中所有学生的姓名和英语成绩
命令:
select name,english from student;
3) 过滤表中的重复数据
命令:
select distinct *from student;
4) 统计每个学生的总分
命令:
select name, chinese+math+english from student;
5) 所有学生总分上加上10
命令:
select name, chinese+math+english+10 from student;
6) 使用别名表示学生分数
命令:
select name, (chinese+math+english+10) sum_score from student;
7)查询英语成绩大于90分的所有同学
命令:
select name,english from student where english>90;
8) 查询总分大于200分的所有同学
命令:
select name,(chinese+math+english) sum_score from student where (chinese+math+english+10)>200;
9)查询英语成绩在80到90之间的所有同学
命令:
select name,english from student where english between 80 and 90;
10) 查询英语成绩不在80到90之间的所有同学
命令:
select name,english from student where english not between 80 and 90;
11)查询数学分数为89,90,91的同学
命令:
select name,math from student where math between 89 and 91;
或
select name,math from student where math=89 or math=90 or math=91;
12)查询数学分数不为89,90,91的同学
命令:
select name,math from student where math not between 89 and 91;
13) 查询所有姓李的学生英语成绩
命令:
select name,english from student where name like '李%';
14) 查询数学分80且语文80分的同学
命令:
select name from student where math=80 and chinese=80;
15)查询英语80或者总分200的同学
命令:
select name from student where english=80 or (math+english+chinese)=200;
16)对学生数学成绩进行降序排序后输出
命令:
select name,math from student order by math desc;
17)对总分从高到低的顺序输出
命令:
select *from student order by (math+english+chinese) desc;
18)对姓李的学生按总成绩升序排序输出
命令:
select *from student where name like '李%' order by (math+english+chinese);
19) 查询男生和女生分别有多少人,并将人数降序输出
命令:
select gender, count(*) from student group by gender order by count(gender) desc;
20)查询男生和女生分别有多少人,并将人数降序输出,并查询出大于9的性别人数信息
命令:
elect gender, count(*) from student group by gender having count(gender)>9 order by count(gender) desc;