sql练习-2
2023-6-19 sql语句
-- -- 以下题目涉及到的表有
-- ecs_users(用户表)、
-- ecs_order_info(订单表)、
-- ecs_goods(商品表)、
-- ecs_goods_type(商品类型表)
--
use test;
select * from ecs_users;
select * from ecs_goods;
select * from ecs_order_info;
select * from ecs_order_goods;
-- 1、查询电商平台的总注册用户有多少
use test;
select * from ecs_users;
select count(*) from ecs_users;
-- 2、查询电商平台的商品数量有多少?
select * from ecs_goods;
select count(goods_sn) from ecs_goods;
-- 3、查询出品牌为“资生堂”的商品的平均价格是多少?
select * from ecs_goods where goods_name like '资生堂%';
select avg(market_price) from ecs_goods where goods_name like '资生堂%';
select avg(shop_price) from ecs_goods where goods_name like '资生堂%';
-- 4、查询出品牌为“资生堂”的商品的总库存为多少?
select sum(goods_number) from ecs_goods where goods_name like '资生堂%';
-- 5、查询出电器类商品上架了多少?
select * from ecs_goods join ecs_goods_type on ecs_goods_type.cat_id = ecs_goods.cat_id where ecs_goods_type.cat_name like '%电器%';
select count(*) from ecs_goods join ecs_goods_type on ecs_goods_type.cat_id = ecs_goods.cat_id where ecs_goods_type.cat_name like '%电器%';
-- 6、查询出平台今天的总销售是多少?
select * from ecs_order_info;
select count(order_sn) from ecs_order_info;
select sum(goods_amount) from ecs_order_info where order_sn like '%20230619%';
-- 7、查询出用户名为test的用户的订单?
select * from ecs_users;
select * from ecs_order_info;
select * from ecs_order_info join ecs_users on ecs_order_info.user_id = ecs_users.user_id
where ecs_users.user_name = 'test';
-- 8、查询出所有商品中价格最贵的商品信息?
select * from ecs_goods;
select max(shop_price) from ecs_goods;
select max(market_price) from ecs_goods;
select * from ecs_goods ORDER BY shop_price desc limit 1;
-- 9、查询出平台消费能力最强的用户信息?
select * from ecs_order_info;
select sum(ecs_order_info.goods_amount), ecs_users.* from ecs_order_info join ecs_users on ecs_order_info.user_id = ecs_users.user_id group by ecs_users.user_id order by sum(ecs_order_info.goods_amount) desc limit 1;
-- 10、查询出食品类的商品有多少种?
select * from ecs_goods_type;
select * from ecs_goods;
select count(*) from ecs_goods join ecs_goods_type on ecs_goods.cat_id = ecs_goods_type.cat_id where ecs_goods_type.cat_name like '%食品%';
select * from ecs_goods where goods_name like '塞翁福%'
or goods_name like '进口橄榄油%'
or goods_name like '名野%';
-- 11、查询出有消费记录的用户信息
select ecs_users.* from ecs_order_info join ecs_users on ecs_order_info.user_id = ecs_users.user_id group by ecs_users.user_id;
-- 12、查询出有消费记录的用户的总消费额
select * from ecs_order_info;
-- select ecs_users.* from ecs_order_info join ecs_users on ecs_order_info.user_id = ecs_users.user_id group by ecs_users.user_id ;
select sum(ecs_order_info.goods_amount),ecs_users.* from ecs_order_info join ecs_users on ecs_order_info.user_id = ecs_users.user_id group by ecs_users.user_id;
-- 13、查询出只注册了账号但是没有消费过的用户信息
select * from ecs_order_info;
select ecs_users.* from ecs_users where ecs_users.user_id not in (select user_id from ecs_order_info );
-- 14、查询出消费过的用户总消费额是多少
select sum(ecs_order_info.goods_amount),ecs_users.* from ecs_order_info join ecs_users on ecs_order_info.user_id = ecs_users.user_id;
-- 15、查询出在2月份平台的销售额是多少
select * from ecs_order_info;
select sum(goods_amount),ecs_order_info.* from ecs_order_info where order_sn like '202302%';
select sum(goods_amount),ecs_order_info.* from ecs_order_info where order_sn like '202306%';
-- select unix_timestamp(now());