常见的sql语句 注意点及用法【区分mysql 和Sqlserver】

sql语句中加事务

var sql = "START TRANSACTION;";//transaction rollback

sql += @"sql语句";

sql += "COMMIT;";

 

-*****************************************************************************************************************************-

如何判断在字符串字段中是否包含某个字符串

mysql:   url:http://www.springload.cn/springload/detail/399

　　mysql> SELECT * FROM users WHERE find_in_set('2', limits);

 

sqlserver: url:http://bbs.csdn.net/topics/110118170

  select * from [tbServerWeb]. [dbo].[tb_gameStaff] where PATINDEX('%130601%' ,[g_partnerNo])> 0 and UserID=10043;

  select * from [tbServerWeb]. [dbo].[tb_gameStaff] where charindex('130601' ,[g_partnerNo])> 0 and UserID=10043;

 

 

-*****************************************************************************************************************************-

 

字段判空：

Sqlserver：

  SELECT TOP 1000 icon ,*

  FROM [userapp].[dbo] .[tbiTunesNewApp]

  i left join userapp. dbo.tbUserApp u on i .appid= u.id

   where i.isActive =1 and len( u.icon )<1 order by seq desc

 

 

-*****************************************************************************************************************************-

 

Char(32)类型的数字不能按数值来进行比较：

eg:

 130699 和 1306167：

(int)130699 < (int)1306167    按数值大小比较

(Char)130699 > (Char)1306167  按字符从左到右依次比较

 

 

-*****************************************************************************************************************************-

 

查询语句做除法 保留几位小数

 

Serversql: SELECT ROUND(COALESCE(CAST([curTimes] AS FLOAT), 0)/1000, 3) a FROM [iMessage].[dbo].[tbmaclist]

 

mysql:  SELECT TRUNCATE(7185521/7185522,4)

   

 

------------------------------------------------------

获取insert的id

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;

 -----------------------------------------------------------