MySQL单表练习题

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

insert into student(id,name,chinese,english,math) values(1,'行哥',89,78,90);
insert into student(id,name,chinese,english,math) values(2,'潘金莲',67,53,95);
insert into student(id,name,chinese,english,math) values(3,'凤姐',87,78,77);
insert into student(id,name,chinese,english,math) values(4,'旺财',88,98,92);
insert into student(id,name,chinese,english,math) values(5,'白小黑',82,84,67);
insert into student(id,name,chinese,english,math) values(6,'白小黄',55,85,45);
insert into student(id,name,chinese,english,math) values(7,'范蹦蹦',75,65,30);

 

#25.查询表中所有学生的信息。
SELECT * FROM student;

#26.查询表中所有学生的姓名和对应的英语成绩。
SELECT name , english FROM student;

#27.过滤表中重复数据。
SELECT DISTINCT * FROM student;

#28.统计每个学生的总分。
SELECT chinese+english+math FROM student;

#29.在所有学生总分数上加10分特长分。
SELECT (chinese+english+math+10) FROM student;

#30.使用别名表示学生分数。
SELECT chinese AS"语文",english AS"英语",math AS"数学" FROM student;

#31.查询姓名为行哥的学生成绩
SELECT * FROM student WHERE name="行哥";

#32.查询英语成绩大于90分的同学
SELECT * FROM student WHERE english>90;

#33.查询总分大于200分的所有同学
SELECT * FROM student WHERE(chinese+english+math)>200;

#34.查询英语分数在 80－90之间的同学。
SELECT * FROM student WHERE english BETWEEN 80 AND 90;

#35.查询数学分数为89,90,91的同学。
SELECT * FROM student WHERE math in(89,90,91);

#36.查询所有姓白的学生英语成绩。
SELECT english,name FROM student WHERE name LIKE "白%";

#37.查询数学分>80并且语文分>80的同学
SELECT name FROM student WHERE math>80 AND chinese > 80;

#38.查询英语>80或者总分>200的同学
SELECT * FROM student WHERE english>80 or (chinese+english+math)>200;

#39.对数学成绩排序后输出
SELECT * FROM student ORDER BY math ASC;

#40.对总分排序后，然后再按数学从高到低的顺序输出
SELECT * FROM student ORDER BY (chinese+english+math) DESC,math DESC;

#41.对姓白的学生成绩排序输出
SELECT* FROM student  WHERE name LIKE "白%" ORDER BY(chinese+english+math)DESC;