- 本文出自 “朴实的追梦者” 博客,请务必保留此出处http://sfzhang88.blog.51cto.com/4995876/871823
我有个同学最近在学Mysql数据库,为了测试自己的学习成果,他们老大出了6道Mysql试题让他去做,晚上回来他又分享给我,我感觉非常经典,记录了这几道试题已及答案。
将测试数据表导入数据库,其中name字段代表“姓名”,score字段代表“分数”。
- create table `t1` (
- `id` double ,
- `name` varchar (300),
- `score` double
- );
- insert into `t1` (`id`, `name`, `score`) values('1','n1','59');
- insert into `t1` (`id`, `name`, `score`) values('2','n2','66');
- insert into `t1` (`id`, `name`, `score`) values('3','n3','78');
- insert into `t1` (`id`, `name`, `score`) values('4','n1','48');
- insert into `t1` (`id`, `name`, `score`) values('5','n3','85');
- insert into `t1` (`id`, `name`, `score`) values('6','n5','51');
- insert into `t1` (`id`, `name`, `score`) values('7','n4','98');
- insert into `t1` (`id`, `name`, `score`) values('8','n5','53');
- insert into `t1` (`id`, `name`, `score`) values('9','n2','67');
- insert into `t1` (`id`, `name`, `score`) values('10','n4','88');
- 1. 单分数最高的人和单分数最低的人。
- mysql> select name,max(score) from t1;
- 2. 两门分数加起来的第2至5名。
- mysql> select name,sum(score) from t1 group by name order by sum(score) desc limit 1,4;
- +------+------------+
- | name | sum(score) |
- +------+------------+
- | n3 | 163 |
- | n2 | 133 |
- | n1 | 107 |
- | n5 | 104 |
- +------+------------+
- 3. 两门总分数在150分以下的人。
- select name sum(score) from t1 group by name having sum(score) < 150;
- 4. 两门平均分数介于60和80的人。
- select name,avg(score) from t1 group by name having avg(score) between 60 and 80;
- select name,avg(score) from t1 group by name having ave(score) >60 and avg(score) <80;
- 5. 总分大于150分,平均分小于90分的人数。
- mysql> select name,sum(score),avg(score) from t1 group by name having sum(score)>150 and avg(score)<90;
- +------+------------+------------+
- | name | sum(score) | avg(score) |
- +------+------------+------------+
- | n3 | 163 | 81.5 |
- +------+------------+------------+
- 1 row in set (0.00 sec)
- 6. 总分大于150分,平均分小于90分的人数有几个。
- select count(distinct name) from t1 group by name having sum(score) > 150 and avg(score) < 90;
- <pre></pre>