数据库分组统计

一.分组统计(聚合函数不能用order by进行排序,不能用where进行筛选)
1.group by语句(写在where和order by之间)
select category_id,sum(stock) from item group by category_id;
select avg(price) from item where category_id in (13,11) group by category_id;

2.对分组数据进行筛选,应该在group by中写条件(having)
select category_id,sum(stock) from item group by category_id having sum(stock)>1000;

3.尽量写在where内,效率高
select category_id,sum(stock) from item where category_id>10 ground by category_id;
select category_id,sum(stock) from item group by category_id having category_id>10;

4.分组后排序
select category_id,max(price) from item group by category_id order by category_id desc;
select category_id,sum(price*stock) as s from item group by category_id order by s desc;

二.子查询
1.查询所有有地址的用户的id
select distinct user_id from address;
2.查询所有有地址的用户的全部信息
select*from user where id in (1,3,4);
select*from user where id in (select distinct user_id from address);
3.查询所有有商品的分类的id
select distinct category_id from item;
4.查询所有有商品的分类的全部信息
select*from category where id in (7,8,9);
select*from category where id in (select distinct category_id from item);
5.采用exists也可以实现上述逻辑
select*from user where exists (select*from address where user_id=user.id);
select*from category where exists (select*from item where category_id=category.id);
6.查询出所有用户以及他们所包含的地址数量
select*,(select count(*) from address where user_id=user.id) as amount from user;
7.查询出所有商品分类以及每个分类所包含的库存总数

select*,(select count(*) from item where category_id=category.id) as amount from category;


三.关联查询数据
1.等值连接:查询出A和B相匹配的数据
select*from category,item where category.id=item.category_id;
select u.*,a.name 姓名,a.address from user as u,address as a where u.id=a.user_id;
2.内连接:其作用与等值连接相同
select*from category inner join item on category.id=item.category_id;
select*from category c join item i on c.id=i.category_id order by i.id;
select*from user u join address a on u.id=a.user_id join city c on a.city_id=c.id;

3.左外连接:查询出A的所有数据,以及与A匹配的B的数据

select left(name,1) surname, count(*) from address group by surname;——查询同一姓氏的个数

select*from category left outer join item on category.id=item.category_id;
select*from category left join item on category.id=item.category_id;
4.右外连接:查询出B的所有数据,以及与B匹配的A的数据
select*from category right outer join item on category.id=item.category_id;
select*from category right join item on category.id=item.category_id;
  • 1
    点赞
  • 6
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

linsa_pursuer

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值