创建如下学生表
create table student(
id int,
name varchar(20),
gender varchar(20),
chinese int,
math int,
english int
);
insert into 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);
完成下列要求
-- 查询表中所有学生的信息
select * from student;
-- 查询表中所有学生的姓名和英语成绩
select name, english from student;
-- 过滤表中的重复数据
select distinct * from student;
-- 统计每个学生的总分
select chinese+math+english total from studet;
-- 所有学生总分上加上10
select chinese+math+english+10 total from studet;
-- 使用别名表示学生分数
select name 姓名,chinese as 语文,math 数学,english 英语 from student;
-- 查询英语成绩大于90分的所有同学
select * from student
where english > 90;
-- 查询总分大于200分的所有同学
select * from student
where chinese+math+english > 200;
-- 查询英语成绩在80到90之间的所有同学
select * from student
where english between 80 and 90;
-- 查询英语成绩不在80到90之间的所有同学
select * from student
where english not between 80 and 90;
-- 查询数学分数为89,90,91的同学
select * from student
where math in (89,90,91);
-- 查询数学分数不为89,90,91的同学
select * from student
where math not in (89,91,90);
-- 查询所有姓李的学生英语成绩
select english from student
where name like '李%';
-- 查询数学分80且语文80分的同学
select name from student
where math = 80 and english = 80;
-- 查询英语80或者总分200的同学
select name from student
where english = 80 or math+chinese+english=200;
-- 对学生数学成绩进行降序排序后输出
select math from student
order by math desc;
-- 对总分从高到低的顺序输出
select math+chinese+english as total from student
order by total desc;
-- 对姓李的学生按总成绩升序排序输出
select name, math+chinese+english as total from student
where name like '李%'
order by total asc;
-- 查询男生和女生分别有多少人,并将人数降序输出
select count(gender) from student
group by gender
order by count(gender) desc;
-- 查询男生和女生分别有多少人,并将人数降序输出,并查询出大于9的性别人数信息
SELECT gender, COUNT(gender) AS count
FROM student
GROUP BY gender
ORDER BY count DESC;