试题: 数据库操作

XueTing 2021-09-11 原文

#根据商品名称模糊查询商品的信息和所属分类名
#条件:产品名称
select p.*,pc.name as pname from product p
left join product_category pc on p.category_id = pc.id
where p.name like \'%柚子%\'
group by p.id;


#新增一条商品评论记录
#条件:用户id,产品id,评论内容,评论状态,星级评价
insert into comments values(null,123518,946,\'优秀\',0,5,now());


#显示商品评论信息列表(包括评论人和评论人的头像)
select c.*,u.user_name,u.image from comments c
left join user u on (c.user_id = u.id)
where user_id = 123518 and product_id = 946;


#首页商品分类显示
#1.查询所有分类信息
select pc.id from product_category pc group by pc.id
#2.根据分类id查询分类信息和该分类下的产品信息
#条件:产品分类id
SELECT * FROM product INNER JOIN product_category ON product.category_id = product_category.id


#电子钱包充值
#1.获取系统钱包信息
select * from ewallet;
#2.进行充值:比如充值1000元
#条件:用户名,充值金额,充值类型
insert into account values(null,\'17767746537\',1000,\'充值\',now());


#获取某个用户电子钱包的余额
#条件:用户名
select sum(a.amount) as account from account a where username = \'17767746537\';


#新增文章
#条件:内容,文章分类id,用户,标题
insert into values(null,\'<p>购物车管理已经上线</p>\',21,now(),\'admin\',\'购物车管理\');


#商品的收藏功能
#条件:productId,optionValueIds,userId
insert into wishlist values(null,935,\'4_7\',123484,now()); 


#获取热销产品
select p.* from product p where p.hot = 1 order by p.create_time limit 0,6;


#获取pc端广告轮播图
select * from advert;


#获取mobile端广告轮播图
select * from slide;


#获取订单按月份统计数据
#条件:开始日期和结束日期
select count(order_num) AS `count`, sum(price) AS `price`, DATE_FORMAT(create_time, \'%Y-%m\') AS `date` from order_info
where create_time between \'2019-08-08\' and \'2019-12-30\'
group by DATE_FORMAT(create_time, \'%Y-%m\');


#商品购买流程
#1.将商品加入购物车
#条件:产品id,用户id,数量,sku_id,
insert into cart values(\'1002\',934,123473,10,189,\'7_11\',\'颜色:红 材料:皮\',now());

#2.获取某个用户的购物车列表和商品信息
select c.*,p.* from cart c 
left join product p on p.id=c.product_id
where c.user_id = \'123473\';

#3.立即支付,生成订单记录
INSERT INTO `shopping`.`order_info`(`order_num`, `price`, `payment_flag`, `user_id`, `contact_name`, `contact_mobile`, `contact_address`, `message`, `status`, `type`, `create_time`) VALUES (\'O2019112100001\', \'9800\', 1, 123518, \'李升\', \'17623721608\', \'天津市-天津市-和平区-北京市-北京市-东城区-123\', NULL, 1, 0, \'2019-10-21 00:13:41\');

#4.电子支付
#4-1.从账户表中插入一条购买的消费记录
INSERT INTO `shopping`.`account`(`id`, `username`, `amount`, `source`, `updtime`) VALUES (1350, \'17767746537\', -130, \'商城消费\', \'2019-09-05 10:56:43\');
#4-2.生成订单历史记录
INSERT INTO `shopping`.`order_history`(`id`, `order_num`, `status`, `note`, `update_user_id`, `create_time`) VALUES (53, \'O2020112900001\', 1, \'已下单\', 123522, \'2020-11-29 17:17:13\');