sql语句中加事务
var sql = "START TRANSACTION;";//transaction rollback
sql += @"sql语句";
sql += "COMMIT;";
-*****************************************************************************************************************************-
mysql> SELECT * FROM users WHERE find_in_set(
'2'
, limits);
SELECT
TRUNCATE
(7185521/7185522,4)
SELECT @@IDENTITY;
---------------------------
mysql:
SELECT LAST_INSERT_ID();
------------------------------------------------------
存在更新,不存在新增
SqServer:
if exists (select * from dbo.users s where s.name='张三')
update users set sex='男' where name = '张三'
ELSE
insert into users (name,sex) values ('张三','女')
mysql:
replace into students (StuName, Stuid, Class) VALUES ('张三', '123456789', '1234567');
insert ignore into user_info (last_name,first_name) values ('LeBron','James');
INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
------------------------------------------------------
复杂判断要用存储过程实现
Oracle:
MERGE INTO "tablename" T1
USING (SELECT '123' AS check_id, N'xxx' AS result,'D:\Report\123.jpg' AS img_path,TO_DATE('2019-12-12 20:25:33', 'YYYY-MM-DD HH24:MI:SS') AS check_time FROM dual) T2
ON ( T1."check_id"=T2.check_id)
WHEN MATCHED THEN
UPDATE SET T1."result" = T2.result, T1."check_time" = T2.check_time
WHEN NOT MATCHED THEN
INSERT ("check_id","result","img_path","check_time") VALUES(T2.check_id,T2.result,T2.img_path,T2.check_time)
-*****************************************************************************************************************************-
查出数据表中连续出现三次或三次以上的数据
查询数据表中符合某个条件(同分同编号同一天)的连续出现三次或三次以上的数据
mysql:
select * from table where id in (
select distinct n1.id from table n1,table n2,table n3
where (n1.score = n2.score and n2.score = n3.score and n1.score=100 and
n1.device_code = n2.device_code and n2.device_code = n3.device_code and n1.device_code='2000100013' and
TO_DAYS(n1.ctime) = TO_DAYS(n2.ctime) and TO_DAYS(n2.ctime) = TO_DAYS(n3.ctime) and TO_DAYS(n1.ctime) = TO_DAYS('2020-03-07 08:46:09') and (
(n1.id + 1= n2.id and n2.id +1 = n3.id)or
(n3.id + 1= n2.id and n2.id +1 = n1.id)or
(n3.id + 1= n1.id and n1.id +1 = n2.id)
)
)
order by n1.id );
https://blog.csdn.net/weixin_34279061/article/details/93512869
------------------------------------------------------------------------------------------------
批量更新
mysql:
update table set name= case id when 1 then '小明' when 2 then '小花' end, age= case id when 1 then 10 when 2 then 12 end where id in(1, 2);
sqlserver:
update table set name='小明',age=10 where id=1;update table set name='小花',age=12 where id=2;
--------------------------------------------------------------------------------------------------------------------------------------------------
mysql 查询存在A表中而不存在B表中的数据
select * from A where (select count(1) from B where cate_id =15 and A.pid =B.pid) = 0;
select * from A left join B on b.pid=A.pid and B.cate_id =12 where b.pid is null;
-----------------------------------------------------------