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());