快麦设计系统-数据库常用查询

一：典型的查询语句：

select * from tbl_psd where user_id=436 ORDER BY create_time DESC;

select * from tbl_template_process where user_id=436 ORDER BY id desc ;

select * from idpt_product_desc_template ORDER BY create_time desc;

唯品会 -店铺在数据表 tbl_authorize_shop 中换绑用户之后，需要修改数据表 tbl_vips_size_table 中的字段 user_id，否则新用户下新建的唯品会宝贝，不能提供尺码表；

update tbl_vips_size_table set user_id = 151 WHERE shop_id = 632 /* 浙江森马电子商务有限公司 * /

二：Navicat 工具中的Beautify SQL：

SELECT

TABLE_SCHEMA,

TABLE_NAME,

COLUMN_NAME,

COLUMN_TYPE,

COLUMN_COMMENT

FROM

information_schema.COLUMNS

WHERE

TABLE_SCHEMA = ‘design_1988'

三选中某张表，点击鼠标右键，然后选择“Design Table”, 即在右侧展开该数据表的每个字段，以及一些字段的解释；

四：数据表中表示状态Status的值；

涉及到Status的内容有，详情页的套版（伪套版、待套版、套版中、套版成功）、

套版详情页的审核（未审核、待审核、审核通过、审核失败）、

审核详情页的编辑（微调中、微调完成、保存中；）、

宝贝发布（发布成功、发布成功、需完善、更新成功、更新失败）；

五：

（1）tbl_order 数据表（’财务报表—订单的状态信息’，如打款、退款等）中的 “ID” 和 tbl_user_resources 数据表中 order_id 进行关联；

（2）然后，

tbl_user_resources 数据表（’财务报表--新增订单’）中 “ id、order_id、user_id、good_id（9表示模板解析、10表示详情套版）” 中的 “ID”

和 tbl_resources_product 数据表（’财务报表--新增订单的有效时间’）中“ resource_id、product_id（1表示模板解析、2表示详情套版）、left_num（剩余素材套版次数或剩余模板解析次数）、valid_duration、start_time 、end_time 等” 中 resource_id 进行关联；

（3）数据表tbl_use_operator_record 中 resource_id 字段实现了将套版或模版解析的每次消耗关联到订单表。

数据表tbl_use_operator_record （“ID、user_id 、resource_id” ）中的 resource_id 字段记录的值是否准确，它记录了数据表tbl_record 中的 ID 字段。

六：

1. “产品详情页模版”数据表，即记录主站用户的套版详情页的数据表： idpt_product_desc_template

2. 详情页领取记录数据表： idpt_desc_examine_log

数据表 tbl_shop_form，更改宝贝发布的状态，改成 15。

tbl_single_platform_form

数据表 idpt_product_desc_template：用于记录“ 运营系统-详情审核”页面中，保存每一款详情页模版的数据。

数据表 idpt_package_group_snapshot：用于记录运营系统—详情页审核页面中，每一款详情页的综合信息；

数据表 idpt_package_snapshot：用于记录运营系统—详情页审核页面中，每一款详情页中图片等数据；

idpt_mark_job 是记录素材包的任何一个前置标注审核、详情页套版审核任务；

idpt_material_artificial_mark_info 用于记录运营系统，素材包中每一张图片的颜色聚类信息（运营--图片标注—颜色聚类）

select *

from idpt_material_artificial_mark_info where folder_id = (

select idpt_mark_job.material_id

from idpt_mark_job where id = 2558); /* 云涛 KMSJ-4803 2558 是运营系统图片标注-颜色聚类页面链接中的 Key */

唯品会尺码表 tbl_vips_size_table ，其中字段 size_table_template_id 是唯品会尺码表模版ID；备注：星柚店铺的shop_id=1261

七：算法中用到的数据表

图片标注页面，数据表 tbl_image_material_group 中有标签；

数据表： idpt_colourplate_tag 是算法中用到数据表，记录“运营系统—场景配置”的相关数据；

主站配置中心—智能裁剪： tbl_item_image_crop_rule tbl_item_image_crop_rule_cat_relation

八：素材空间素材包的查询

第一步：素材包的ID：

第二步：

九：tbl_authorize_shop

select * from tbl_authorize_shop where shop_nick in ('若只遇见你','主图打标test30');

十：

idpt_material_image_info 这张表用于存储图片的识别信息。后端是在这张表中，获取图图片的识别信息的。

20200402:

一：

select * from tbl_slice_record ORDER BY id desc where id=1101

select * from tbl_user where nick='gst'

select * from tbl_user where nick='山橘'

SELECT * FROM tbl_authorize_shop WHERE id=820

delete from tbl_authorize_shop where id=621

SELECT * FROM tbl_authorize_shop;

SELECT * FROM tbl_authorize_shop WHERE user_id=151

SELECT * FROM tbl_authorize_shop WHERE shop_nick='岭月一线'

SELECT * FROM tbl_single_platform_form where user_id=151 and shop_nick='蓝森空间' ORDER BY create_time desc /* tbl_shop_form */

SELECT * FROM tbl_authorize_shop WHERE shop_nick IN ('boylondon官方旗舰店','dcshoes服饰旗舰店')

SELECT * FROM tbl_cat where name Like'%文胸%' and platform in (1,2,4,6,7) /*文胸、文胸套装、内裤、女士内裤、塑身裤、塑身美体裤、丝袜、短袜/打底袜/丝袜/美体袜*/

SELECT * FROM tbl_cat where name Like'%文胸%' and platform = 4 /* 京东有文胸、文胸套装、连裤袜/丝袜、女士内裤；没有美体裤 */

SELECT ASh.nick，ASh.user_id FROM tbl_authorize_cat as ACa，tbl_authorize_shop as ASh where ACa.shop_id=ASh.id and ACa.name Like'%文胸%'

SELECT tbl_authorize_shop.user_id, tbl_authorize_shop.nick, tbl_authorize_shop.shop_nick,tbl_authorize_shop.platform_id,tbl_authorize_cat.`name`

FROM tbl_authorize_shop

LEFT JOIN tbl_authorize_cat

ON tbl_authorize_shop.id=tbl_authorize_cat.shop_id

WHERE tbl_authorize_cat.`name`Like'%文胸%'

and tbl_authorize_shop.user_id!=''

SELECT * FROM tbl_authorize_shop

SELECT * FROM tbl_authorize_cat where name Like'%文胸%'

/* */

SHOW INDEX in tbl_authorize_shop

二：

SELECT * FROM tbl_authorize_shop WHERE user_id= 151

SELECT * FROM tbl_platform_form order by id DESC; /* 待发布列表 */

SELECT * FROM tbl_shop_form order by id DESC; /* 已发布列表 */

SELECT * FROM tbl_authorize_shop WHERE shop_nick= '杭州星柚服饰有限公司'

SELECT * FROM tbl_authorize_shop where shop_nick Like "%西瓜%"

SELECT * FROM tbl_authorize_shop where nick Like "%Lily%"

SELECT * FROM tbl_authorize_shop where platform_id=7 /* 7:拼多多*/

SELECT * FROM tbl_vips_size_table；

SELECT * FROM tbl_psd where user_id=436 ORDER BY id desc;

select * from tbl_person_template where user_id=151 and title like "%夏大大%" ORDER BY id desc;

select * from tbl_template_process where user_id=436 ORDER BY id desc ;

select * from idpt_product_desc_template ORDER BY create_time desc;

select * from tbl_shop_form where user_id=151 and title="商品标题" ORDER BY create_time desc;

select * from tbl_shop_form where user_id=436 and title="淘宝女休闲裤商品标题" ORDER BY create_time desc;

update tbl_shop_form set STATUS=15 where user_id=151; /* 436,538，edit_status */

select * from tbl_item_image_crop_rule；

select * from tbl_item_image_crop_rule_cat_relation；

select * from tbl_slice_record ORDER BY id desc where id=1101

select * from tbl_user where nick = 13736835403

select * from tbl_user where nick='苏子'

select * from tbl_user where ID=151

SELECT * FROM tbl_authorize_shop WHERE id=820

delete from tbl_authorize_shop where id=621

SELECT * FROM tbl_authorize_shop;

SELECT * FROM tbl_authorize_shop WHERE user_id=151 /*151，436，504，524，538,621，820，10588*/

SELECT * FROM tbl_authorize_shop WHERE shop_nick='杭州爵卓服饰有限公司'

SELECT * FROM tbl_shop_form ORDER BY create_time desc

SHOW INDEX in tbl_authorize_shop

三：SELECT id, user_id, name,group_id,code from idpt_material where id = 421912;

SELECT id,name,group_id from idpt_material where user_id = 151 and parent_code = '0000057' and type = 1;

select * idpt_package_snapshot;

select * idpt_package_group_snapshot; /*151，436，504，524，621，820，10588*/

SELECT * idpt_product_desc;

SELECT * tbl_psd;

select * tbl_image_templet;

select * tbl_shop_form;

四：

select * from tbl_slice_record ORDER BY id desc where id=1101

select * from tbl_psd;

select * from tbl_image_templet;

select * from tbl_user_resources;

select * from tbl_resource_product;

select * from tbl_request_resource;

select * from tbl_user_platforms;

select * from tbl_use_operator_record;

select * from tbl_user;

select * from tbl_shop_form;

select * from idpt_product_desc;

select * from idpt_product_desc_template where user_id=436 ORDER BY id desc ; /*详情页列表*/

select * from idpt_image_export_record;

select * from tbl_vips_size_table ORDER BY create_time DESC; /* size_table_template_id 是尺码表ID，星柚的shop_id=1261, tbl_vips_size_table 是唯品会尺码表*/

select * from tbl_vips_size_table where

select * from tbl_user where ID =504

select * from tbl_user where nick='gst'

SELECT * FROM tbl_authorize_shop WHERE id=820

delete from tbl_authorize_shop where id=621

SELECT * FROM tbl_authorize_shop;

SELECT * FROM tbl_authorize_shop WHERE user_id=151

/*151，436，502，504，524，538,621，820，10588*/

SELECT * FROM tbl_authorize_shop WHERE shop_nick Like '%星柚%'

SHOW INDEX in tbl_authorize_shop

SELECT * FROM tbl_authorize_shop where platform_id=7 /* 7:拼多多*/

五：

select * from tbl_slice_record ORDER BY id desc where id=1101

select * from tbl_psd where user_id=10586 ORDER BY create_time desc;

select * from tbl_image_templet;

select * from tbl_user_resources;

select * from tbl_resource_product;

select * from tbl_request_resource;

select * from tbl_user_platforms;

select * from tbl_use_operator_record;

select * from tbl_user;

select * from tbl_shop_form;

select * from idpt_product_desc;

select * from idpt_product_desc_template;

select * from idpt_image_export_record;

select * from idpt_material where user_id =151 ;

select * from idpt_master_material where user_id =151 and material_id=406717;

select * from tbl_user where id=502

select * from tbl_user where nick='gst'

select * from tbl_user where nick Like '%圆圆%'

SELECT * FROM tbl_authorize_shop WHERE id=15

delete from tbl_authorize_shop where id=621

SELECT * FROM tbl_authorize_shop;

SELECT * FROM tbl_authorize_shop WHERE user_id=524 /*151，436，504，524，621，820，10588*/

SELECT * FROM tbl_authorize_shop WHERE shop_nick Like '%童曲%'

SHOW INDEX in tbl_authorize_shop

SELECT * FROM tbl_authorize_shop where platform_id=7 /* 7:拼多多*/

SELECT * FROM idpt_product_desc_template WHERE id = 268230

SELECT * FROM idpt_product_desc_template WHERE template_id = 7716 ORDER BY create_time DESC

20200601

素材包的查询，素材包商品图的查询；注意：素材包URL中包含了 material_root_id，在materialSpace/的后面。

select material_root_id,b.name

from idpt_master_material a

left join idpt_material b

on a.material_root_id = b.id

where a.user_id = 151 and a.platform = 8 group by material_root_id,b.name;

select * from idpt_master_material where user_id = 151 and material_root_id=489498 and platform=8

select * from idpt_material where user_id = 151 and id=489498;

四：

SELECT * FROM idpt_detail_operation_log where user_id =151 ORDER BY create_time DESC

// 运营系统--数据统计--服务效率统计，这个页面的表格

insert into idpt_detail_operation_log (product_desc_template_id,item_number,template_type,type,user_id,user_nick,service_user_id,service_user_nick,

template_id,template_name,initiate_time,initiate_date,receive_duration,mark_duration,audit_duration,service_duration,total_duration,modify_time,

create_time,`status`,transfer_status)

select product_desc_template_id,item_number,template_type,type,user_id,user_nick,service_user_id,service_user_nick,template_id,template_name,

initiate_time,initiate_date,receive_duration,mark_duration,audit_duration,service_duration,total_duration,modify_time,create_time,`status`,

transfer_status

from idpt_detail_operation_log // 4083

select count(*) from idpt_detail_operation_tail -f tomcat/logs/catalina.out

五：

运营系统—标签配置，相关数据表中的字段含义：

标签的类型，type=1 是通用；2 是用户

适用场景，useScenes=1 商品图裁剪； =2 模版/详情页编辑器

（1）当useScenes=1，scope的值对应以下含义

标签的作用域：scope=1是全局级规则；2 是坑位类型级； 3是坑位级

（2）当useScenes=2，scope的值对应以下含义

标签的作用域：scope=1是模版；2是模块； 3是坑位；4是场景；5是元件组、6是元件；

posted @ 2022-09-06 19:51 moonly0118 阅读(80) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

快麦设计系统-数据库 常用查询

公告

快麦设计系统-数据库常用查询