mysql笔试题.pdf

文档介绍：该【mysql笔试题 】是由【闰土】上传分享，文档一共【8】页，该文档可以免费在线阅读，需要了解更多关于【mysql笔试题 】的内容，可以使用淘豆网的站内搜索功能，选择自己适合的文档，以下文字是截取该文章内的部分文字，如需要获得完整电子版，请下载此文档到您的设备，方便您编辑和打印。面试笔试常考的mysql数据库操作groupby
分类:数据库2014-08-0616:38773人阅读评论(0)收藏举报
面试数据库mysql
IT面试中,数据库的相关问题基本上属于必考问题,而其中关于sql
语句也是经常考察的一个重要知识点。
下面介绍下sql语句中一个比较重要的操作groupby,他的重要行一
方面体现在他的理解困难度,一方面体现应用中的长见性。
首先,给出一个studnet学生表:
[sql]viewplaincopyprint?
`student`(
2.`id`int(11)NOTNULLAUTO_INCREMENT,
3.`name`varchar(30)DEFAULTNULL,
4.`sex`tinyint(1)DEFAULT'0',
5.`score`int(10)NOTNULL,
6.`dept`varchar(10)DEFAULTNULL,
(`id`)
8.)ENGINE=MyISAMAUTO_INCREMENT=8DEFAULTCHARSET=utf8
添加一些测试数据:
[sql]viewplaincopyprint?
>select*fromstudentwhereid<10;
2.+----+------+------+-------+---------+
3.|id|name|sex|score|dept|
4.+----+------+------+-------+---------+
5.|1|a|1|90|dev|
6.|2|b|1|90|dev|
7.|3|b|0|88|design|
8.|4|c|0|60|sales|
9.|5|c|0|89|sales|
10.|6|d|1|100|product|
11.+----+------+------+-------+---------+
给出需求,写出sql:
给出各个部门最高学生的分数。
要想得到各个部门学生,首先就要分组,按照部门把他们分组,然后在
各个部门中找到分数最高的就可以了。
所以sql语句为:
[sql]viewplaincopyprint?
>select*,max(score)asmaxfromstudentgroupbydeptorderbyname
;
2.+----+------+------+-------+---------+------+
3.|id|name|sex|score|dept|max|
4.+----+------+------+-------+---------+------+
5.|1|a|1|90|dev|90|
6.|3|b|0|88|design|88|
7.|4|c|0|60|sales|89|
8.|6|d|1|100|product|100|
9.+----+------+------+-------+---------+------+
()
这只是个简单的例子,我们可以再把这个例子复杂化,比如分数最高的
必须是女生,即sex列值必须为1才挑选出,这时的sql语句应该为:
[sql]viewplaincopyprint?
>select*,max(score)asmaxfromstudentgroupbydepthavingsex='1'
orderbyname;
2.+----+------+------+-------+---------+------+
3.|id|name|sex|score|dept|max|
4.+----+------+------+-------+---------+------+
5.|1|a|1|90|dev|90|
6.|6|d|1|100|product|100|
7.+----+------+------+-------+---------+------+
()
这里我们没有用where语句而是用了having,这里简单说明一下,因为
我们的条件是在分组后进行的,其实分组前挑选出sex='1',然后再按照
dept部门分组,也是可行的,这里就要看题目是怎么要求的:
[sql]viewplaincopyprint?
>select*,max(score)asmaxfromstudentwheresex='1'groupbydepto
rderbyname;
2.+----+------+------+-------+---------+------+
3.|id|name|sex|score|dept|max|
4.+----+------+------+-------+---------+------+
5.|1|a|1|90|dev|90|
6.|6|d|1|100|product|100|
7.+----+------+------+-------+---------+------+
()
查询出的结果时一致的,如果把选择条件改为必须部门所有人的分数之
和大于150才能把分数最高的部门的人列出来,这里就必须使用
having了,因为having里面可以使用聚合函数sum,并且也必须分
完组我们才能得到这个组的总分数,才能比较是否该值大于150:
[sql]viewplaincopyprint?
>select*,max(score)asmaxfromstudentgroupbydepthavingsum(sc
ore)>150orderbyname;
2.+----+------+------+-------+---------+------+
3.|id|name|sex|score|dept|max|
4.+----+------+------+-------+---------+------+
5.|1|a|1|90|dev|90|
6.|6|d|1|100|product|100|
7.+----+------+------+-------+---------+------+
()
额外增加一个例子,比如我要选出不重复的部门,我们可以使用
[sql]viewplaincopyprint?
>selectdistinctdeptfromstudent;
2.+---------+
3.|dept|
4.+---------+
5.|dev|
6.|design|
7.|sales|
8.|product|
9.+---------+
()
但是如果我们还要列出他的id等一些其他信息,我们如果这样:
[sql]viewplaincopyprint?
>selectname,distinctdeptfromstudent;
(42000):YouhaveanerrorinyourSQLsyntax;checkthemanualt
hatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear
'distinctdeptfromstudent'atline1
这是不行的,因为distinct只能放到开始位置,如果:
[sql]viewplaincopyprint?
>selectdistinctdept,namefromstudent;
2.+---------+------+
3.|dept|name|
4.+---------+------+
5.|dev|a|
6.|dev|b|
7.|design|b|
8.|sales|c|
9.|product|d|
10.|product|m|
11.+---------+------+
()
为什么没有达到预期的效果,因为distinct作用到了2个字段上,这
时,我们就需要groubby出场了。
[sql]viewplaincopyprint?
>selectdept,namefromstudentgroupbydept;
2.+---------+------+
3.|dept|name|
4.+---------+------+
5.|design|b|
6.|dev|a|
7.|product|d|
8.|sales|c|
9.+---------+------+
()
按照dept分组,自然就达到去重的目的了。所以有时候如果我们碰到
了一个问题很难解决,比如用distinct去重,并带上其他列值,我们就
需要尝试换个思路,可能答案自然就找到了。
题目如下:


要求:
1,查询两门及两门以上不及格的学生的平均分。比如:张三有两门功课不及格,
语文50分,地理40分,他的平均分为:(90+50+40)/3=60。
2,用where或groupby或having等一条语句实现查询。

解答: