mysql笔试题

面试笔试常考的mysql 数据库操作group by分类：数据库2014-08-06 16:38 773人阅读评论(0) 收藏举报面试数据库mysqlIT 面试中，数据库的相关问题基本上属于必考问题，而其中关于sql 语句也是经常考察的一个重要知识点。

下面介绍下sql语句中一个比较重要的操作group by，他的重要行一方面体现在他的理解困难度，一方面体现应用中的长见性。

首先，给出一个studnet学生表：[sql]view plaincopyprint?1.CREATE TABLE `student` (2. `id` int(11) NOT NULL AUTO_INCREMENT,3. `name` varchar(30) DEFAULT NULL,4. `sex` tinyint(1) DEFAULT'0',5. `score` int(10) NOT NULL,6. `dept` varchar(10) DEFAULT NULL,7.PRIMARY KEY (`id`)8.) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8添加一些测试数据：[sql]view plaincopyprint?1.mysql> select * from student where id<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]view plaincopyprint?1.mysql> select *, max(score) as max from student group by dept order by name;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.+----+------+------+-------+---------+------+10. 4 rows in set (0.00 sec)这只是个简单的例子，我们可以再把这个例子复杂化，比如分数最高的必须是女生，即sex列值必须为1才挑选出，这时的sql语句应该为：[sql]view plaincopyprint?1.mysql> select *,max(score) as max from student group by dept having sex='1'order by name;2.+----+------+------+-------+---------+------+3.| id | name | sex | score | dept | max |4.+----+------+------+-------+---------+------+5.| 1 | a | 1 | 90 | dev | 90 |6.| 6 | d | 1 | 100 | product | 100 |7.+----+------+------+-------+---------+------+8. 2 rows in set (0.46 sec)这里我们没有用where语句而是用了having,这里简单说明一下，因为我们的条件是在分组后进行的，其实分组前挑选出sex='1',然后再按照dept部门分组，也是可行的，这里就要看题目是怎么要求的：[sql]view plaincopyprint?1.mysql> select *,max(score) as max from student where sex='1'group by dept order by name;2.+----+------+------+-------+---------+------+3.| id | name | sex | score | dept | max |4.+----+------+------+-------+---------+------+5.| 1 | a | 1 | 90 | dev | 90 |6.| 6 | d | 1 | 100 | product | 100 |7.+----+------+------+-------+---------+------+8. 2 rows in set (0.05 sec)查询出的结果时一致的，如果把选择条件改为必须部门所有人的分数之和大于150才能把分数最高的部门的人列出来，这里就必须使用having了，因为having 里面可以使用聚合函数sum，并且也必须分完组我们才能得到这个组的总分数，才能比较是否该值大于150：[sql]view plaincopyprint?1.mysql> select *,max(score) as max from student group by dept having sum(score)>150 order by name;2.+----+------+------+-------+---------+------+3.| id | name | sex | score | dept | max |4.+----+------+------+-------+---------+------+5.| 1 | a | 1 | 90 | dev | 90 |6.| 6 | d | 1 | 100 | product | 100 |7.+----+------+------+-------+---------+------+8. 2 rows in set (0.00 sec)额外增加一个例子，比如我要选出不重复的部门，我们可以使用[sql]view plaincopyprint?1.mysql> select distinct dept from student;2.+---------+3.| dept |4.+---------+5.| dev |6.| design |7.| sales |8.| product |9.+---------+10. 4 rows in set (0.02 sec)但是如果我们还要列出他的id等一些其他信息，我们如果这样：[sql]view plaincopyprint?1.mysql> select name,distinct dept from student;2.ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct dept from student'at line 1这是不行的，因为distinct只能放到开始位置，如果：[sql]view plaincopyprint?1.mysql> select distinct dept,name from student;2.+---------+------+3.| dept | name |4.+---------+------+5.| dev | a |6.| dev | b |7.| design | b |8.| sales | c |9.| product | d |10.| product | m |11.+---------+------+12. 6 rows in set (0.00 sec)为什么没有达到预期的效果，因为distinct 作用到了2个字段上，这时，我们就需要groub by 出场了。

[sql]view plaincopyprint?1.mysql> select dept,name from student group by dept;2.+---------+------+3.| dept | name |4.+---------+------+5.| design | b |6.| dev | a |7.| product | d |8.| sales | c |9.+---------+------+10. 4 rows in set (0.00 sec)按照dept分组，自然就达到去重的目的了。

所以有时候如果我们碰到了一个问题很难解决，比如用distinct去重，并带上其他列值，我们就需要尝试换个思路，可能答案自然就找到了。

题目如下：要求：1，查询两门及两门以上不及格的学生的平均分。

比如：张三有两门功课不及格, 语文50 分，地理40分，他的平均分为：（90+50+40）/3 = 60。

2，用where 或group by 或having 等一条语句实现查询。

解答：。