面试笔试常考的mysql 数据库操作group by

标签: 面试 mysql 数据库 | 发表时间:2014-08-07 00:38 | 作者:fujiafeihudui
出处:http://blog.csdn.net

IT 面试中,数据库的相关问题基本上属于必考问题,而其中关于sql语句也是经常考察的一个重要知识点。


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


首先,给出一个studnet学生表:

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `sex` tinyint(1) DEFAULT '0',
  `score` int(10) NOT NULL,
  `dept` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 


添加一些测试数据:


mysql> select * from student where id<10;
+----+------+------+-------+---------+
| id | name | sex  | score | dept    |
+----+------+------+-------+---------+
|  1 | a    |    1 |    90 | dev     |
|  2 | b    |    1 |    90 | dev     |
|  3 | b    |    0 |    88 | design  |
|  4 | c    |    0 |    60 | sales   |
|  5 | c    |    0 |    89 | sales   |
|  6 | d    |    1 |   100 | product |
+----+------+------+-------+---------+



给出需求,写出sql:

给出各个部门最高学生的分数。

要想得到各个部门学生,首先就要分组,按照部门把他们分组,然后在各个部门中找到分数最高的就可以了。


所以sql语句为:

mysql> select *, max(score) as max  from student group by dept order by name;
+----+------+------+-------+---------+------+
| id | name | sex  | score | dept    | max  |
+----+------+------+-------+---------+------+
|  1 | a    |    1 |    90 | dev     |   90 |
|  3 | b    |    0 |    88 | design  |   88 |
|  4 | c    |    0 |    60 | sales   |   89 |
|  6 | d    |    1 |   100 | product |  100 |
+----+------+------+-------+---------+------+
4 rows in set (0.00 sec)


这只是个简单的例子,我们可以再把这个例子复杂化,比如分数最高的必须是女生,即sex列值必须为1才挑选出,这时的sql语句应该为:

mysql> select *,max(score) as max from student group by dept having sex='1' order by name;
+----+------+------+-------+---------+------+
| id | name | sex  | score | dept    | max  |
+----+------+------+-------+---------+------+
|  1 | a    |    1 |    90 | dev     |   90 |
|  6 | d    |    1 |   100 | product |  100 |
+----+------+------+-------+---------+------+
2 rows in set (0.46 sec)


这里我们没有用where语句而是用了having,这里简单说明一下,因为我们的条件是在分组后进行的,其实分组前挑选出sex='1',然后再按照dept部门分组,也是可行的,这里就要看题目是怎么要求的:

mysql> select *,max(score) as max from student where sex='1' group by dept order by name;
+----+------+------+-------+---------+------+
| id | name | sex  | score | dept    | max  |
+----+------+------+-------+---------+------+
|  1 | a    |    1 |    90 | dev     |   90 |
|  6 | d    |    1 |   100 | product |  100 |
+----+------+------+-------+---------+------+
2 rows in set (0.05 sec)


查询出的结果时一致的,如果把选择条件改为必须部门所有人的分数之和大于150才能把分数最高的部门的人列出来,这里就必须使用having了,因为 having 里面可以使用聚合函数sum,并且也必须分完组我们才能得到这个组的总分数,才能比较是否该值大于150:

mysql> select *,max(score) as max from student   group by dept having sum(score)>150 order by name;
+----+------+------+-------+---------+------+
| id | name | sex  | score | dept    | max  |
+----+------+------+-------+---------+------+
|  1 | a    |    1 |    90 | dev     |   90 |
|  6 | d    |    1 |   100 | product |  100 |
+----+------+------+-------+---------+------+
2 rows in set (0.00 sec)



额外增加一个例子,比如我要选出不重复的部门,我们可以使用

mysql> select distinct dept from student;
+---------+
| dept    |
+---------+
| dev     |
| design  |
| sales   |
| product |
+---------+
4 rows in set (0.02 sec)


但是如果我们还要列出他的id等一些其他信息,我们如果这样:

mysql> select name,distinct dept from student;
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只能放到开始位置,如果:

mysql> select distinct  dept,name from student;
+---------+------+
| dept    | name |
+---------+------+
| dev     | a    |
| dev     | b    |
| design  | b    |
| sales   | c    |
| product | d    |
| product | m    |
+---------+------+
6 rows in set (0.00 sec)


为什么没有达到预期的效果,因为distinct 作用到了2个字段上,这时,我们就需要groub by 出场了。


mysql> select  dept,name from student group by dept;
+---------+------+
| dept    | name |
+---------+------+
| design  | b    |
| dev     | a    |
| product | d    |
| sales   | c    |
+---------+------+
4 rows in set (0.00 sec)


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








作者:fujiafeihudui 发表于2014-8-6 16:38:46 原文链接
阅读:15 评论:0 查看评论

相关 [面试 mysql 数据库] 推荐:

面试笔试常考的mysql 数据库操作group by

- - CSDN博客数据库推荐文章
IT 面试中,数据库的相关问题基本上属于必考问题,而其中关于sql语句也是经常考察的一个重要知识点. 下面介绍下sql语句中一个比较重要的操作group by,他的重要行一方面体现在他的理解困难度,一方面体现应用中的长见性. 首先,给出一个studnet学生表:. 给出各个部门最高学生的分数. 要想得到各个部门学生,首先就要分组,按照部门把他们分组,然后在各个部门中找到分数最高的就可以了.

MySQL数据库的修复

- Xin - 博客园-首页原创精华区
找到mysql的安装目录的bin/myisamchk工具,在命令行中输入:. 然后myisamchk 工具会帮助你恢复数据表的索引. 好象也不用重新启动mysql,问题就解决了. 当你试图修复一个被破坏的表的问题时,有三种修复类型. 如果你得到一个错误信息指出一个临时文件不能建立,删除信息所指出的文件并再试一次--这通常是上一次修复操作遗留下来的.

MySQL数据库的IO操作

- - haohtml's blog
         淘宝丁奇分享的PPT:MySQL数据库的IO操作,详细分享了四块的内容,并且告诉大家如何调整MySQL数据库IO操作相关的参数,给出了详细的选择策略,现替其整理成文章分享与此. 4.影响io行为的一些参数和选择策略. 一个简单的查询 select * from t where id>=(  select id from t where k1=100 limit 100000,1) limit 2;.

MySQL数据库优化总结

- - CSDN博客推荐文章
        对于一个以数据为中心的应用,数据库的好坏直接影响到程序的性能,因此数据库性能至关重要. 一般来说,要保证数据库的效率,要做好以下四个方面的工作:数据库设计、sql语句优化、数据库参数配置、恰当的硬件资源和操作系统,这个顺序也表现了这四个工作对性能影响的大小.        一、数据库设计   适度的反范式,注意是适度的.

Google数据库产品LevelDB对决MySQL

- - HTML5研究小组
去年一月份,Google发布了LevelDB. LevelDB是Key-Value嵌入式数据库管理系统编程库,目前的版本能够支持Billion级别的数据量. LevelDB是一个C++库,可按照字符串键值顺序映射. 源于其本身的良好设计,特别是LSM算法,LevelDB性能非常之高. 在一台4个Q6600的CPU机器上,每秒钟写数据超过40w,而随机读的性能每秒钟超过10w.

excel数据导入mysql数据库

- - 互联网 - ITeye博客
1、excel另存为txt.       选中将要导出的数据列,然后另存为选择其它格式=>文本文件(制表符分割). E:\项目\fblike\game_code_san.txt. 2、txt导入到mysql数据库. load data infile 'E:\\项目\\fblike\\game_code_san.txt' into table game_code_san(code).

c/c++连接mysql数据库

- - CSDN博客数据库推荐文章
        由于项目需要,要用c/c++链接mysql数据库. 网上很多类似的解说,但是大部分都需要在本机器上安装完整版的msyql. 其实,有时候我们并不想在改变自己电脑上原有的环境,但是我们却希望通过我们的程序链接数据库. 比如:我在本机上已经安装了一个mysql,但并不是完整版的(比如appserv集成mysql或者wamp集成mysql),或者我的工作在局域网中,我只需要链接另外一台机器上的mysql.

理解MySQL数据库覆盖索引

- - haohtml's blog
看AUTO_INCREMENT就知道数据并不多,75万条. 很简单对不对?怪异的地方在于:. 如果换成MyISAM做存储引擎的时候,查询耗时只需要0.01s,用InnoDB却会是0.15s左右. 如果只是就这么点差距其实不是什么大不了的事,但是真实的业务需求比这个复杂,造成的差距也很大:MyISAM只需要0.12s,InnoDB则需要2.2s.,最终定位到问题症结是在这条SQL.

MySQL数据库设置主从同步

- - CSDN博客架构设计推荐文章
MYSQL主从同步是目前使用比较广泛的数据库架构,技术比较成熟,配置也不复杂,特别是对于负载比较大的网站,主从同步能够有效缓解数据库读写的压力. 1、可以作为一种备份机制,相当于热备份. 2、可以用来做读写分离,均衡数据库负载. 1、主从数据库版本一致,建议版本5.5以上. # 日志文件名 log-bin = mysql-bin # 日志格式,建议mixed binlog_format = mixed # 主数据库端ID号 server-id = 1.

浅谈MySQL 数据库性能优化

- - BlogJava-qileilove
数据库是 IO 密集型的程序,和其他数据库一样,主要功能就是数据的持久化以及数据的管理. 本文侧重通过优化MySQL 数据库缓存参数如查询缓存,表缓存,. 日志缓存,索引缓存,innodb缓存,插入缓存,以及连接参数等方式来对MySQL数据库进行优化.   这里先引用一句话,从内存中读取一个数据的时间消耗是微秒级别,而从普通硬盘上读取一个数据是在毫秒级别,二者相差3个数量级.