MySQL数据库基本操作-DQL-聚合查询

简介

使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个单一的值；另外聚合函数会忽略空值。

字段说明：

• product 是一个表
• pname 是商品名
• price 是 价格
• category_id 是分类

-- 1 查询商品的总条数
select count(*) from product; -- count() 统计的是行数
-- 2 查询价格大于200商品的总条数
select count(*) from product where price > 200;
-- 3 查询分类为'c001'的所有商品的价格总和
select sum(price) from product where category_id = 'c001';
-- 4 查询商品的最大价格
select max(price) from product;
-- 5 查询商品的最小价格
select min(price) from product;
-- 6 查询分类为'c002'所有商品的平均价格
select avg(price) from product where category_id = 'c002';

1
2
3
4
5
6
7
8
9
10
11
12
13

创建product表的语句

create table product(
    pid int primary key auto_increment,
    pname varchar(20) not null ,
    price double,
    category_id varchar(20)
);

insert into product values(NULL,'海尔洗衣机',5000,'c001');
insert into product values(NULL,'美的冰箱',3000,'c001');
insert into product values(NULL,'格力空调',5000,'c001');
insert into product values(NULL,'九阳电饭煲',5000,'c001');

insert into product values(NULL,'啄木鸟衬衣',300,'c002');
insert into product values(NULL,'恒源祥西裤',800,'c002');
insert into product values(NULL,'花花公子夹克',440,'c002');
insert into product values(NULL,'劲霸休闲裤',266,'c002');
insert into product values(NULL,'海澜之家卫衣',180,'c002');
insert into product values(NULL,'杰克琼斯运动裤',430,'c002');

insert into product values(NULL,'兰蔻面霜',300,'c003');
insert into product values(NULL,'雅诗兰黛精华水',200,'c003');
insert into product values(NULL,'香奈儿香水',350,'c003');
insert into product values(NULL,'SK-II神仙水',350,'c003');
insert into product values(NULL,'资生堂粉底液',180,'c003');

insert into product values(NULL,'老北京方便面',56,'c004');
insert into product values(NULL,'良品铺子海带丝',17,'c004');
insert into product values(NULL,'三只松鼠坚果',88,NULL);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29