【无标题】

最新推荐文章于 2023-12-21 17:47:58 发布

每天一篇博客

最新推荐文章于 2023-12-21 17:47:58 发布

阅读量385

点赞数

分类专栏： mysql 文章标签：数据库开发

本文链接：https://blog.csdn.net/m0_43609780/article/details/122145361

版权

mysql 专栏收录该内容

9 篇文章 1 订阅

订阅专栏

实践的目标就是能够根据文字提示，完成商品表（sh_goods）与商品评论表（sh_goods_comment）各种需求的查询操作。

CREATE TABLE sh_goods (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '商品id',
category_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '分类id',
spu_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'SPU id',
sn VARCHAR(20) NOT NULL DEFAULT '' COMMENT '编号',
name VARCHAR(120) NOT NULL DEFAULT '' COMMENT '名称',
keyword VARCHAR(255) NOT NULL DEFAULT '' COMMENT '关键词',
picture VARCHAR(255) NOT NULL DEFAULT '' COMMENT '图片',
tips VARCHAR(255) NOT NULL DEFAULT '' COMMENT '提示',
description VARCHAR(255) NOT NULL DEFAULT '' COMMENT '描述',
content TEXT NOT NULL COMMENT '详情',
price DECIMAL(10, 2) UNSIGNED NOT NULL DEFAULT 0 COMMENT '价格',
stock INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '库存',
score DECIMAL(3, 2) UNSIGNED NOT NULL DEFAULT 0 COMMENT '评分',
is_on_sale TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否上架',
is_del TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否删除',
is_free_shipping TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否包邮',
sell_count INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '销量计数',
comment_count INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '评论计数',
on_sale_time DATETIME DEFAULT NULL  COMMENT '上架时间',
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_time DATETIME DEFAULT NULL COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO sh_goods (id, category_id, name, keyword, content, price, 
stock, score, comment_count) VALUES
(1, 3, '2B铅笔', '文具', '考试专用', 0.5, 500, 4.9, 40000),
(2, 3, '钢笔', '文具', '练字必不可少', 15, 300, 3.9, 500),
(3, 3, '碳素笔', '文具', '平时使用', 1, 500, 5, 98000),
(4, 12, '超薄笔记本', '电子产品', '轻小便携', 5999, 0, 2.5, 200),
(5, 6, '智能手机', '电子产品', '人人必备', 1999, 0, 5, 98000),
(6, 8, '桌面音箱', '电子产品', '扩音装备', 69, 750, 4.5, 1000),
(7, 9, '头戴耳机', '电子产品', '独享个人世界', 109, 0, 3.9, 500),
(8, 10, '办公电脑', '电子产品', '适合办公', 2000, 0, 4.8, 6000),
(9, 15, '收腰风衣', '服装', '春节潮流单品', 299, 0, 4.9, 40000),
(10, 16, '薄毛衣', '服装', '居家旅行必备', 48, 0, 4.8, 98000);
CREATE TABLE sh_goods_comment (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '评论id',
parent_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '上级评论id',
user_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户id',
goods_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品id',
content TEXT NOT NULL COMMENT '评论内容',
is_staff TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否为工作人员',
is_show TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否显示',
is_del TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否删除',
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_time DATETIME DEFAULT NULL COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `sh_goods_comment` (id, user_id, goods_id, content,
is_show, create_time) VALUES
(1, 1, 8, '好', 0, '2017-11-08 00:00:00'),
(2, 2, 10, '不错', 1, '2017-12-03 00:00:00'),
(3, 3, 9, '满意', 1, '2017-12-30 00:00:00'),
(4, 4, 4, '携带方便', 1, '2018-01-19 00:00:00'),
(5, 4, 7, '中低音效果特别棒', 1, '2018-01-19 00:00:00'),
(6, 5, 8, '卡机', 1, '2018-01-22 00:00:00'),	
(7, 6, 5, '黑夜拍照也清晰', 1, '2018-02-15 00:00:00'),
(8, 7, 9, '掉色、有线头', 0, '2018-03-03 00:00:00'),
(9, 4, 9, '还行', 1, '2018-04-05 00:00:00'),
(10, 8, 9, '特别彰显气质', 1, '2018-04-16 00:00:00');


-- select g.id,gc.content from sh_goods g left join sh_goods_comment gc on g.id=gc.goods_id where g.id in(8);
-- select user_id,count(goods_id) from sh_goods_comment group By user_id;
-- select id,create_time,content from sh_goods_comment order by create_time desc limit 1,5;
#Group_concat连接
-- select user_id,group_concat(goods_id) goodsid from sh_goods_comment group by user_id having count(distinct goods_id )>=2;
-- select sg.id from sh_goods sg where sg.id not in(select goods_id from sh_goods_comment group by goods_id);
select sg.id,sc.content from sh_goods sg,sh_goods_comment sc where sg.score=5;