t_base_order_goods_info ----产品明细表
t_base_order_info ----主表
常用SQL
修改
单表修改
UPDATE t_base_order_goods_info
SET b.customer = "小明"
WHERE
order_id = "GDD20210511111438318"
多表修改
UPDATE t_base_order_goods_info bg
LEFT JOIN t_base_order_info b ON b.order_id = bg.order_id
SET b.customer = "小明"
WHERE
b.order_id = "GDD20210511111438318"
查询
单表查询
SELECT
*
FROM
t_base_order_info
WHERE
id = 10
多表查询
SELECT
*
FROM
t_base_order_info g
LEFT JOIN t_base_order_goods_info gd ON g.order_id = gd.order_id
WHERE
order_id = "GDD20210511111438318"
删除
单表删除
DELETE
FROM
t_base_order_goods_info
WHERE
order_id = "GDD20210511111438318"
多表删除
-- 多表联合删除需要指定对应删除的表;如:delete bg from t_base_order_goods_info bg
DELETE bg
FROM
t_base_order_goods_info bg
LEFT JOIN t_base_order_info b ON b.order_id = bg.order_id
WHERE
b.order_id = "GDD20210511111438318"
增加(很少用,navicat上面可以现成)
insert into sys_user` (`user_id`, `user_name`, `password`, `img_url`, `nick_name`, `name`, `mobile`, `email`, `photo`, `create_time`, `update_time`, `status`, `create_user_id`) VALUES ('4893', '123', '86aeec28b2ab8e67e64a98a03a7c1756', '', 'yebin', 'yebin', '123', '123', '', '2021-03-22 14:07:16', NULL, '1', '1');
拓展
GROUP_CONCAT
多条数据同一个字段拼接在一起
COUNT()
统计某个字段相同的值出现的次数
SELECT
o.order_id "集团单",
count(o.order_id) AS "服务数量",
o.customer "客户",
o.tax_total_sales "销售价(含税)",
o.tax_distribution_amount "经销价(含税)",
GROUP_CONCAT(d.server_name) AS '服务模式'
FROM
t_base_order_info AS o
LEFT JOIN t_base_flow_info AS d ON o.order_id = d.order_id
WHERE
o.customer_id = 4915
AND (
o.tax_distribution_amount * 1.02 > o.tax_total_sales
OR o.tax_distribution_amount = o.tax_total_sales
)
AND o. STATUS != 5
GROUP BY
d.order_id
SUM
某个字段求和
SELECT
sum(total)
FROM
t_base_order_goods_info
WHERE
order_id = "GDD20210507101433612"
CASE WHEN THEN ELSE END
SELECT
g.order_id,
CASE
WHEN p.internal_purchase != 0 THEN
'没有'
ELSE
'有'
END '对应的采购单' -- 对应的字段显示
FROM
t_base_order_info g
LEFT JOIN t_base_order_goods_info gd ON g.order_id = gd.order_id
LEFT JOIN t_business_purchase_detail pd ON pd.source_number = g.order_id
LEFT JOIN t_business_purchase_order p ON p.purchase_id = pd.purchase_id
WHERE
gd.id = pd.source_detail_id
AND gd.order_id IN ()
GROUP BY
order_id
DESC(降序)DE/S/C
SELECT
*
FROM
t_base_order_info
ORDER BY
id DESC
子查询(一般用左查询比较多)
UPDATE t_base_order_info g
LEFT JOIN (
SELECT
sum(d.tax_distribution_amount) tax_distribution_amount,
d.order_id order_id
FROM
t_base_order_info o
LEFT JOIN t_base_order_goods_info d ON o.order_id = d.order_id
GROUP BY
d.order_id
) t ON t.order_id = g.order_id
SET g.tax_distribution_amount = t.tax_distribution_amount
WHERE
t.order_id = 'GDD20210602171930329'
如何快速知道请求接口对应的数据库表
1、在url提取有用的字段
如:https://image.baidu.com/user/logininfo?src=pc&page=searchresult&time=1657804626370
很明显image就是关注字段
然后到数据库上面搜索即可
2、看数据库表设计的注释
3、主动找开发
Navicat实现数据同步
注意:要谨慎操作,新手建议忽略
注意:要谨慎操作,新手建议忽略
在Navicat软件找到对应的数据库,右键,选择数据传输,选择对应的选项即可
Navicat表的导入导出
注意:导入操作,要谨慎操作,新手建议忽略