MySQL语句练习题

insert into exam values(null,\'关羽\',85,76,70);insert into exam values(null,\'张飞\',70,75,70);insert into exam values(null,\'赵云\',90,65,95);insert into exam values(null,\'刘备\',97,50,50);insert into exam values(null,\'曹操\',90,89,80);insert into exam values(null,\'司马懿\',90,67,65);select * from exam#1、查询表中所有学生的信息。select * from exam#查询表中所有学生的姓名和对应的英语成绩。select name,english from exam#3、过滤表中重复数据。SELECT DISTINCT  english from exam#在所有学生分数上加10分特长分。select name,chinese+10,math+10,english+10 from exam#统计每个学生的总分。select name as 姓名,chinese as 语文 , math as 数学,english as 英语 from exam#6、使用别名表示学生分数select name as 姓名,chinese+math+english as 总成绩 from exam#查询姓名为刘备的学生成绩select * from exam where name =\'刘备\'#查询英语成绩大于90分的同学select  * from exam where english >90;#查询总分大于200分的所有同学select name,chinese+math+english as 总分 from exam where chinese+math+english>200#查询英语分数在 80－90之间的同学。select name ,english from exam where english BETWEEN  80 and 90#查询数学分数为89,75,91的同学。select name , math from exam where math in(89,75,91)#查询所有姓刘的学生成绩。select * from exam where name like \'刘%\'#查询数学分>80并且语文分>80的同学。select * from exam where math>80 and chinese >80#对数学成绩排序后输出。select name,math from exam ORDER BY math#对总分排序按从高到低的顺序输出select name,chinese+english+math as 总分 from exam ORDER BY chinese+english+math DESC#对姓刘的学生成绩排序输出select name ,chinese+english+math as 总成绩 from exam where name like \'刘%\' ORDER BY 总成绩 DESC#统计一个班级共有多少学生？SELECT COUNT(*) from exam#统计数学成绩大于或等于90的学生有多少个？select COUNT(*) from exam where math>=90#统计总分大于250的人数有多少？select COUNT(*) from exam where math+chinese+english >250;#统计一个班级数学总成绩？select SUM(math) from exam#统计一个班级语文、英语、数学各科的总成绩select SUM(chinese) as 语文,SUM(english) as 英语,SUM(math) as 数学 from exam#统计一个班级语文、英语、数学的成绩总和select SUM(math+english+chinese) as 总成绩 from exam;#统计一个班级语文成绩平均分select SUM(chinese)/COUNT(chinese) as 平均分 from exam#求一个班级数学平均分？select AVG(math) as 数学成绩平均数 from exam#27、求一个班级总分平均分select AVG(math+chinese+english) as 总分平均数 from exam#28、求班级最高分和最低分（数值范围在统计中特别有用）select MAX(math+chinese+english) from exam#最低分select MIN(math+chinese+english) from examinsert into orders(id,product,price) values(NULL,\'电视\',900);insert into orders(id,product,price) values(NULL,\'洗衣机\',100);insert into orders(id,product,price) values(NULL,\'洗衣粉\',90);insert into orders(id,product,price) values(NULL,\'桔子\',9);insert into orders(id,product,price) values(NULL,\'洗衣粉\',90);

select * FROM orders
#查询购买了几类商品，并且每类总价大于100的商品
select id,
product,
price>100 as 总价大于100的商品 from orders

select id from exam GROUP BY english HAVING english>60

select exam.name,orders.product from exam,orders where orders.price>100

select exam.name,orders.product RIGHT JOIN orders o on price>100

SELECT *from exam
select name, english from exam group by english