软件测试常用SQL及Navicat一些操作

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表的导入导出

注意：导入操作，要谨慎操作，新手建议忽略

导入导出

select查询导出