create database mydb1;
alter database mydb1 character set utf8;
use mydb1;
-- 建表
create table student(
    id int,
    name varchar(20),
    gender varchar(20),
    chinese int,
    math int,
    english int
);
insert into student values
(1,'张明','男',89,78,90),
(2,'李进','男',67,53,80),
(3,'王五','女',87,90,77),
(4,'李一','女',88,98,92),
(5,'李财','男',80,80,80),
(6,'张宝','男',55,91,45),
(7,'黄蓉','女',89,90,91),
(7,'黄蓉','女',89,90,91);
-- 1.    查询表中所有学生的信息
select * from student;

-- 2.    查询表中所有学生的姓名和英语成绩
select name,english from student;

-- 3.    过滤表中的重复数据
select distinct * from student;

-- 4.    统计每个学生的总分
select name,(chinese+math+english) as total from student;

-- 5.    所有学生总分上加上10
select name,(chinese+math+english)+10 as total from student;

-- 6.    使用别名表示学生分数
select name as "姓名",(chinese+math+english)+10 as '总分' from student;

-- 7.    查询英语成绩大于90分的所有同学
select name,english from student where english>90;

-- 8.    查询总分大于200分的所有同学
select name,(chinese+math+english) as total from student having total>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=89 || math=90 || math=91;
select name,math from student where math in(80,90,91);
-- 12.    查询数学分数不为89，90，91的同学
-- select name,math from student where math!=89 || math!=90 || math!=91;
select name,math from student where math not in(80,90,91);

-- 13.    查询所有姓李的学生英语成绩
select name,english from student where name like '李%';

-- 14.    查询数学分80且语文80分的同学
select name,math,chinese from student where math=80 && chinese=80;

-- 15.    查询英语80或者总分200的同学
select name,english,(chinese+math+english) as tatol from student where english=80 having tatol=200;

-- 16.    对学生数学成绩进行降序排序后输出
select name,math from student order by math desc;

-- 17.    对总分从高到低的顺序输出
select name,(chinese+math+english) as tatol from student order by tatol desc;

-- 18.    对姓李的学生按总成绩升序排序输出
select name,(chinese+math+english) as tatol from student where name like '李%' order by tatol asc ;

-- 19.    查询男生和女生分别有多少人，并将人数降序输出
select sum(gender='男') as male,sum(gender='女') as female from student;
select gender,count(*) as count from student group by gender order by count desc;

-- 20.    查询男生和女生分别有多少人，并将人数降序输出，并查询出大于9的性别人数
select sum(gender='男') as male,sum(gender='女') as female from student order by male desc;
select gender,count(*) as count from student group by gender having count>9 order by count desc;