常用MySQL语句(3)

来源：互联网发布：java url base64编码编辑：程序博客网时间：2024/06/13 22:36

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对分组结果进行过滤；

1 0