数据库 | 常用的 sql 语句

1
2
3
4
5
6
7
/*查询结果：分销申请表 rs_user_examine 中 pass = 2 的 user 记录*/

SELECT *                       // 要查询的字段名，* 代表全部
FROM user a                    // 要查询的表
LEFT JOIN rs_user_examine b    // 要连接进行筛选的表
ON a.id = b.user_id            // 两个表连接的条件
WHERE b.pass = 2               // 筛选的条件

1
2
3
/*操作结果：删除 user 表中 nickname = "Haauleon" 的记录*/

DELETE FROM user WHERE nickname = "Haauleon"

1
2
3
4
5
6
7
8
9
/*查询结果：存在于分销申请表 rs_user_examine 但不存在于 user 表中的 user 记录*/

SELECT *                                           // 要查询的字段名，* 代表全部
FROM user a                                        // 要查询的表
LEFT JOIN rs_user_examine b                        // 要连接进行筛选的表
ON a.id = b.user_id and b.shop_no = 878245257      // 两个表连接的条件
WHERE b.user_id is null                            // 筛选的条件
ORDER BY a.id desc                                 // 排序的条件
LIMIT 35                                           // 限制的数量

1
2
3
4
5
/*操作结果：更新 user 中记录为 1463201≤id≤1463236 的用户密码为 123456*/

UPDATE user 
set password = "e10adc3949ba59abbe56e057f20f883e" , salt = "" 
WHERE id>=1463201 and id <=1463236

1
2
3
4
5
/*统计结果：rs_user_commission_record 表中 shop_no = 293491245 的记录的 commission 字段值的总和*/

SELECT parent_id, SUM(commission) as total 
FROM rs_user_commission_record 
WHERE shop_no = 293491245 AND commission_bill in ("PREDICT","INVALID")

1
2
3
4
5
6
/*过滤 share_content_order_user_record 表总重复的订单号 order_no*/

SELECT *
FROM share_content_order_user_record 
WHERE user_id = 109345 AND shop_no = 798189675 AND bill_type = "FINISH" 
GROUP BY `order_no` HAVING count(*)>1 

1
2
3
4
5
6
7
8
9
/*多表连接查询店铺员工关联的权限*/

SELECT role.shop_no, role.user_id, role.role_id, rpms.permission_id, bpms.name, bpms.uri
FROM brb_user_role role 
LEFT JOIN brb_role_permission AS rpms ON role.role_id = rpms.role_id
LEFT JOIN brb_permission AS bpms ON rpms.permission_id = bpms.id
WHERE role.shop_no = 486856075 and role.status = 0 and rpms.status = 0 and role.user_id = 1463406
GROUP BY bpms.name HAVING count(*)>=1 
ORDER BY rpms.permission_id ASC

1
2
3
4
5
/*操作结果：将 user_address 表中记录为 user_id = 1458580 的 status 值由 0 替换为 1*/

UPDATE user_address 
SET status = REPLACE(status, 0, 1)
WHERE user_id = 1458580;

1
2
3
4
5
6
/*查询结果：优惠券关联的优惠券商品*/

SELECT a.id, a.title, a.product_scope, b.product_id
FROM coupon a
INNER JOIN coupon_product b
on a.id = 358 and a.id = b.coupon_id

1
2
3
/*查询结果：user 表中 phone 字段值中包含 1397606 的记录*/

SELECT * FROM user where phone LIKE "%1397606%"

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
/*表 amazonshopinfoext 中 ProdSellerId、areas 分别等于表 rpareport_fbainventory 中的 SellerId、SiteName。
查询表 amazonshopinfoext 中的记录，而这些记录的 SellerId 和 SiteName 在表 rpareport_fbainventory 中对应记录的 status 不为 1。
即剔除表 rpareport_fbainventory 中 status 不为 1 的 SellerId 和 SiteName 的记录，
最终的结果 = 表 amazonshopinfoext 中的记录总和 - 表 rpareport_fbainventory 中 status 不为 1 的记录总和
*/

SELECT shop.ProdSellerId, shop.areas
FROM `datacenteranalysis`.`amazonshopinfoext` shop
WHERE (shop.ProdSellerId, shop.areas) NOT IN (
	SELECT rpa.SellerId, rpa.SiteName
	FROM `datacentersp`.`rpareport_fbainventory` rpa
	WHERE rpa.BaseCreateTime > '2023-11-12 09:00:00'
	    AND rpa.Remark !='【下载文件】'
        AND rpa.status != 1
)
    AND shop.ProdApiState NOT IN ('', 'Normal')
    AND shop.ProdSellerId <> ''
    AND shop.areas <> ''
	AND shop.ProdSellerId IS NOT NULL
	AND shop.areas IS NOT NULL
	AND shop.ProdApiState IS NOT NULL
	AND shop.areas NOT IN ('', 'NL', 'PL', 'SE', 'TR', 'BE', 'BR')

1
2
3
4
5
6
7
8
9
10
11
12
13
/*查询发起人，批次号，计算相同批次号的记录总数，计算相同批次号中未查询变狗的记录总数*/
SELECT 
    DISTINCT BaseCreatorId as creator, 
    batch, 
	COUNT(*) as duplicate_count, 
	SUM(CASE WHEN isDog=0 THEN 1 ELSE 0 END) as unspider_count
FROM 
    keepa_asin_dog 
WHERE 
    asinUrl <> '' 
	AND DATE(BaseCreateTime) = CURDATE()
GROUP BY batch
HAVING COUNT(*) > 1