mysql DQL-基本查询练习（1）

create table student(
	id int,
	name varchar(20),
	gender varchar(20),
	chinese int,
	english int,
	math int
);

INSERT INTO student(id,NAME,gender,chinese,english,math) VALUES(1,'张明','男',89,78,90);
INSERT INTO student(id,NAME,gender,chinese,english,math) VALUES(2,'李进','男',67,53,95);
INSERT INTO student(id,NAME,gender,chinese,english,math) VALUES(3,'王五','女',87,78,77);
INSERT INTO student(id,NAME,gender,chinese,english,math) VALUES(4,'李一','女',88,98,92);
INSERT INTO student(id,NAME,gender,chinese,english,math) VALUES(5,'李财','男',82,84,67);
INSERT INTO student(id,NAME,gender,chinese,english,math) VALUES(6,'张宝','男',55,85,45);
INSERT INTO student(id,NAME,gender,chinese,english,math) VALUES(7,'黄蓉','女',75,65,30);
INSERT INTO student(id,NAME,gender,chinese,english,math) VALUES(7,'黄蓉','女',75,65,30);
-- 查询表中所有信息
select * from student;
-- 查询姓名对应的英语成绩
select name,english from student;
-- 过滤表中重复信息
select distinct * from student;
-- 统计每个学生的总分
select distinct name,chinese+english+math sum_score from student;
-- 给学生加10分特长分，并使用别名表示学生分数
select distinct name,chinese+english+math+10 student_sum_score from student;
-- 查询英语成绩大于90分的同学
select name,english from student where english > 90;
-- 查询总分大于200的同学
select distinct name,chinese + english + math student_score from student where chinese + english + math > 200;
-- 查询英语分数在80-90的同学。
select distinct name,english from student where english >= 80 and english <= 90;
-- 查询英语分数不在80-90的同学
select distinct name,english from student where not (english >= 80 and english <= 90);
-- 查询数学分数位89，90，91的同学
select distinct name,math from student where math = 89 or math = 90 or math = 91;
-- 查询所有姓李的学生英语成绩
select distinct name,english from student where name like '李%';
-- 查询所数学语文都为80分的同学
select name,math,chinese from student where math = 80 and chinese = 80;
-- 查询所英语为80分或总分为200的同学
select name from student where english = 80 or (chinese + english + math) = 200;
-- 对数学成绩降序后输出
select * from student order by math desc;
-- 对总分排序后输出从高到低
select * from student order by (chinese + english + math) desc;
-- 对姓李的学生成绩排序输出
select * from student where name like '李%' order by (chinese + english + math) desc;
-- 查询男生女生分别于多少人，并将人数降序输出,
select gender,count(*) as num from student	GROUP BY gender order by count(*) desc;