常用MySQL语句(3)

1、首先创建数据库，方便测试：

create table salary
(
id int unsigned not null primary key auto_increment,
name varchar(50),
price float(6,1),
customer_name varchar(30)
);

2、添加数据

INSERT INTO `salary` VALUES (0, '牛奶', 11.0, '张三');
INSERT INTO `salary` VALUES (0, '牛奶', 18.0, '李四');
INSERT INTO `salary` VALUES (0, '三明治', 8.8, '张三');
INSERT INTO `salary` VALUES (0, '巧克力', 7.5, '王二');
INSERT INTO `salary` VALUES (0, '耳机', 123.0, '小王');
INSERT INTO `salary` VALUES (0, '面包', 5.3, '张三');
INSERT INTO `salary` VALUES (0, '苹果', 3.5, '李四');
INSERT INTO `salary` VALUES (0, '香蕉', 22.8, '王二');
INSERT INTO `salary` VALUES (0, '面包', 11.0, '小李');
INSERT INTO `salary` VALUES (0, '香蕉', 44.0, '张三');

group by分组

select customer_name,count(*) from salary group by customer_name;

having过滤分组

类似于where过滤一样，如果想要对分组的结果进行过滤，那么就需要使用having，通俗的理解：where用来过滤行，having用来过滤分组；

select customer_name,count(*) from salary group by customer_name having count(*)>=2;先对customer_name进行分组，再用having对分组结果进行过滤；
select customer_name,count(*) from salary where id<=8 group by customer_name having count(*)>=2;先通过where来进行选择，即得到的结果为id在1－8间的纪录，然后进行分组，最后用having对分组结果进行过滤；