建表语句如下:
/* Navicat MySQL Data Transfer Source Host : localhost:3306 Source Database : test Target Host : localhost:3306 Target Database : test Date: 2013-08-19 21:51:49 */ SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for users -- ---------------------------- DROP TABLE IF EXISTS `users`; CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `age` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of users -- ---------------------------- INSERT INTO `users` VALUES ('1', 'huanhuan', '20'); INSERT INTO `users` VALUES ('2', 'xiaoxiao', '20'); INSERT INTO `users` VALUES ('3', 'dada', '20'); INSERT INTO `users` VALUES ('4', 'xiaoxiao', '20'); INSERT INTO `users` VALUES ('5', 'jiaojiao', '20'); INSERT INTO `users` VALUES ('6', 'dada', '20'); INSERT INTO `users` VALUES ('7', 'wenwen', '20'); INSERT INTO `users` VALUES ('8', 'wenwen', '20');
将表建完之后,我们会发现,users表的name列值重复了,那么这时候,我们想name列不重复的记录该怎么写?想一想,我们可以使用group by函数对name进行分组,将name列划分成若干个组,然后对这些组进行处理,处理的时候,我们用到having和count函数,来对name进行筛选和统计,SQL如下:
select name,age from users group by name having count(*) = 1;