一、查询插入
INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising'
FROM dual
WHERE not exists (select * from clients
where clients.client_id = 10345);
查看是否存在client_id=10345的记录,如果不存在,则将查询的临时结果放到dual临时库中,再将该临时记录插入
insert INTO shop_goods(goods_sn,category_sn) SELECT CASE WHEN ISNULL(MAX(goods_sn))||LENGTH(TRIM(MAX(goods_sn)))<1 THEN '100000' ELSE MAX(goods_sn)+1 END ,1001 FROM shop_goods
判断goods_sn是否最大值为空,为空则设值100000,不为空加一,保持goods_sn为最大值,最后将查询结果插入,sql执行顺序是先select 再 insert;
SELECT goods_sn_index + 1 FROM shop_goods_index FOR UPDATE;
UPDATE shop_goods_index SET goods_sn_index = goods_sn_index + 1 ;
查询修改:先将查询表、字段改为可改状态,然后再修改值;