实用sql记录.docxVIP

==styletable{border:1px #000 solid;}//关键是这个还有table的设置！/style增量更新空字符截取select SUBSTR(Alice Zhang,0,INSTR(Alice Zhang, ,-1,1)) , SUBSTR(Alice Zhang,INSTR(Alice Zhang, ,-1,1),LENGTH(Alice Zhang)) from dual批量更新--批量更新--1.使用merge?into：高效merge?into?sys_userdept?suusing?sys_dept?sd?on(su.col=sd.col)when?matched?then?????update?????set?su.deptid=sd.id--2.，一般的更新，col为两表的连接属性update?sys_userdept?su??set?deptid?=?(??????select?id?from?sys_dept?sd??????where?su.col=sd.col)Oracle：UPDATE(SELECT*FROM DEMO.ITMFACILIT ITF LEFTJOIN DEMO.ITMMASTER ITM ON ITM.ITMREF_0=ITF.ITMREF_0 WHERE ITM.TSICOD_0=010) A SET PLHUOT_0=2WHERE A.TSICOD_0=010UPDATE MGK.ITMMVT A SET AMATCST_0=(SELECT SUM(AMTVAL_0) AS VAL FROM MGK.STOJOU B WHERE B.ITMREF_0=A.ITMREF_0 AND B.STOFCY_0=A.STOFCY_0)WHERE EXISTS ( SELECT B.ITMREF_0,B.ITMREF_0 FROM MGK.STOJOU B WHERE B.ITMREF_0=A.ITMREF_0 AND B.STOFCY_0=A.STOFCY_0 AND A.ITMREF_0 AND A.STOFCY_0 = 3201);Sql：update person set pers_SecTerr=a.User_PrimaryTerritoryfrom person left join Users a on a.User_UserId=Pers_CreatedByExcel批量语句修改=UPDATE PERSON SET PERS_EMAILADDRESS1=D2 WHERE PERS_ERPCODE=B2取四位小数CAST( @S*1.0/@v *1.0 AS decimal(16,4))将int型转换为00:00:00型Right(100+@S/3600,2)+:+Right(100+@S/60,2)+:+Right(100+@S %60,2)将一个数据库内的表复制到另一数据库中（包含所有数据）insert into DBProxyConfig.dbo.code_Area select * FROM HongNiuOldDatabase.dbo.code_Areaselect * into HN_CRM.dbo.Syn_Crm_Code_Province FROM HongNiuOldDatabase.dbo.Code_Provence清空表truncate table tablename对存储过程重新编译EXECUTE procname WITH RECOMPILEXECUTE sp_recompile数据库对象名维护索引第一步：查看是否需要维护，查看扫描密度/Scan Density是否为100% declare @table_id int set @table_id=object_id(表名) dbcc showcontig(@table_id) 第二步:重构表索引 dbcc dbreindex(表名,pk_索引名,100) 重做第一步，如发现扫描密度/Scan Density还是小于100%则重构表的所有索引 dbcc dbreindex(表名,,100)备份数据库1. USE masterEXEC sp_addumpdevice disk, Back, E:\DIFFER1.BAK--- 开始备份BACKUP DATABASE infoCRM TO Back 2. BACKUP DATABASE infoCRM TO DISK=E:\DIFFER.BAK 删除重复，只剩一行deletefrom syn_crm_customer where phone in(select phone from