工作中SQL收藏
1.不小心将数据库中所有人性别更新为了男，还好可以根据身份证号判断性别，18为身份证号 update b_ygxxb set fxb='女性' where fsfzh18 in (select fsfzh18 from b_ygxxb where substring(fsfzh18,17,1)%2=0) 2.数据库某字段后面有很多空格，要将多余空格删除。 update tmp_cardManage set fxm=substring(fxm,0,charindex(' ',fxm)) where fnbid=1 update tmp_cardManage set fxm=substring(fxm,0,charindex(' ',fxm)),fsfzh=substring(fsfzh,0,charindex(' ',fsfzh)),fflzh=substring(fflzh,0,charindex(' ',fflzh)) 3.IsNull():使用指定的替换值替换 NULL, ISNULL(字段,如果为null替换成的指定值) select * from o_storage_input_main where OauditFlag=1 and DestineDate+30<getdate() and IsReached=0 and IsNull(IsCancel,0)=0 4.Converth函数使用:将指定字段值转换为指定类型值，Convert(类型(大小),字段) select prd_id,prd_no,prdname,plu_no,unit,'['+Convert(varchar(100),property)+']'+sys_name as sys_name,lsjg from spxx(0, 1) 5.给日期添加天数 select dateadd(day,7,convert(datetime,IsNull(DestineDate,null))),DestineDate from o_storage_input_main 6.用sql语句将数据导出成excel文件 EXEC master..xp_cmdshell 'bcp "SELECT * FROM shine2011bak.dbo.O_Lable_Set" queryout "D:/1.xls" -c -S"SHILI\SQL2000" -U"sa" -P"123"' http://www.exceltip.net/thread-5863-1-1.html 7.查询重复数据
select count(*),HeadPhoto,Passwd,IdNumber from SL_User group by HeadPhoto,Passwd,IdNumber having count(*)>1
8.查找插入数据的自增id,用SCOPE_IDENTITY()比@@identity好
存储过程 /*--数据导出EXCEL 导出查询中的数据到Excel,包含字段名,文件为真正的Excel文件 ,如果文件不存在,将自动创建文件 ,如果表不存在,将自动创建表 基于通用性考虑,仅支持导出标准数据类型 --邹建 2003.10--*/ /*--调用示例 p_exporttb @sqlstr='select * from 地区资料' ,@path='c:\',@fname='aa.xls',@sheetname='地区资料' --*/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_exporttb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[p_exporttb] GO create proc p_exporttb @sqlstr sysname,--查询语句,如果查询语句中使用了order by ,请加上top 100 percent,注意,如果导出表/视图,用上面的存储过程 @path nvarchar(1000),--文件存放目录 @fname nvarchar(250),--文件名 @sheetname varchar(250)=''--要创建的工作表名,默认为文件名 as declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000) --参数检测 if isnull(@fname,'')=''set @fname='temp.xls' if isnull(@sheetname,'')='' set @sheetname=replace(@fname,'.','#') --检查文件是否已经存在 if right(@path,1)<>'\' set @path=@path+'\' create table #tb(a bit,b bit,c bit) set @sql=@path+@fname insert into #tb exec master..xp_fileexist @sql --数据库创建语句 set @sql=@path+@fname if exists(select 1 from #tb where a=1) set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE' +';CREATE_DB="'+@sql+'";DBQ='+@sql else set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 5.0;HDR=YES' +';DATABASE='+@sql+'"' --连接数据库 exec @err=sp_oacreate 'adodb.connection',@obj out if @err<>0 goto lberr exec @err=sp_oamethod @obj,'open',null,@constr if @err<>0 goto lberr --创建表的SQL declare @tbname sysname set @tbname='##tmp_'+convert(varchar(38),newid()) set @sql='select * into ['+@tbname+'] from('+@sqlstr+') a' exec(@sql) select @sql='',@fdlist='' select @fdlist=@fdlist+','+a.name ,@sql=@sql+',['+a.name+'] ' +case when b.name in('char','nchar','varchar','nvarchar') then 'text('+cast(case when a.length>255 then 255 else a.length end as varchar)+')' when b.name in('tynyint','int','bigint','tinyint') then 'int' when b.name in('smalldatetime','datetime') then 'datetime' when b.name in('money','smallmoney') then 'money' else b.name end FROM tempdb..syscolumns a left join tempdb..systypes b on a.xtype=b.xusertype where b.name not in('image','text','uniqueidentifier','sql_variant','ntext','varbinary','binary','timestamp') and a.id=(select id from tempdb..sysobjects where name=@tbname) select @sql='create table ['+@sheetname +']('+substring(@sql,2,8000)+')' ,@fdlist=substring(@fdlist,2,8000) exec @err=sp_oamethod @obj,'execute',@out out,@sql if @err<>0 goto lberr exec @err=sp_oadestroy @obj --导入数据 set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 5.0;HDR=YES ;DATABASE='+@path+@fname+''',['+@sheetname+'$])' exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from ['+@tbname+']') set @sql='drop table ['+@tbname+']' exec(@sql) return lberr: exec sp_oageterrorinfo 0,@src out,@desc out lbexit: select cast(@err as varbinary(4)) as 错误号 ,@src as 错误源,@desc as 错误描述 select @sql,@constr,@fdlist go
9.查询SQL语句的IO开销
set statistics io on
sql语句
set statistics io off
10.查看sql语句执行的资源消耗
菜单-》查询-》包括实际的执行计划
11.给表添加索引：create Index IX_CN_Article_SynchronizedPlateId on CN_Article(SynchronizedPlateId)
删除索引：drop index IX_CN_Article_SynchronizedPlateId on CN_Article
12.给字段添加默认值alter table CN_Article add constraint df_SynchronizedPlateId default(0) for SynchronizedPlateId
13.让sql语句中的In结果不排序
string sql = "select RealName from SL_User where UserId in(" + strUserId.TrimEnd(',')
+ ") order By charindex(','+ cast(UserId as varchar(10)) +',','," + strUserId.TrimEnd(',') + ",')";
14.声明表变量，如下分页示例
string sql = "set nocount on"
+ " declare @indextable table(id int identity(1,1),nid int)"
+ " set rowcount " + endRecordIndex.ToString()
+ " insert into @indextable(nid) select [zskid] from [dbo].[taoluTable] where schooltag='" + schooltag + "' and marc is not null" + (string.IsNullOrEmpty(where) ? string.Empty : " and " + where) + " order by zskid desc"
+ " select O.marc from [dbo].[taoluTable] O,@indextable t where O.zskid=t.nid and t.id between " + startRecordIndex.ToString() + " and " + endRecordIndex.ToString() + " order by t.id"
+ " set nocount off";
15.sql用分号拼接查询出的结果
declare @str varchar(100)
select @str=isnull(@str+',','')+cast(ClassId as varchar) from SL_Class where gradeid in(select GradeId from SL_Grade where schoolid=1)
print @str
16.select count(*) from tableName group by columnName，没有数据查询出的count会返回为null
select count(*) from tableName 没有数据查询出的count为0
17、SQL语句的实现去除最后一个指定字符
update tb set col4=left(col2,len(col2)-1) where right(col2,1)='县' and len(col2)>2
18、按日期分组数据并插入数据到指定表
truncate table OrderStatistics ALTER TABLE OrderStatistics ADD CONSTRAINT DF__Id DEFAULT(NewSequentialId()) FOR Id--添加默认约束 go --按用户每天统计数据，并插入统计表，每天一条记录 insert into OrderStatistics(UserId,[Date],MoneyTotal,WinMoneyTotal,CreateDateTime) select UserId,convert(nvarchar(10),CreateDateTime,120) as [Date], ISNULL(sum(Money),0.0) 'MoneyTotal',ISNULL(sum(WinMoney),0.0) 'WinMoneyTotal',convert(nvarchar(10),CreateDateTime,120) 'CreateDateTime' from Order group by convert(nvarchar(10),CreateDateTime,120),UserId order by [Date] go ALTER TABLE OrderStatistics DROP CONSTRAINT DF__Id--删除默认约束 go
19、根据查询出的结果批量更新指定表数据
update OrderStatistics set ActivityMoneyTotal=pb.ActivityMoney from OrderStatistics a,( select ISNULL(SUM(Money),0.0) ActivityMoney,User_Id,convert(nvarchar(10),CreateDateTime,120) Date2 from BalanceChanges where ChangeType=10 group by convert(nvarchar(10),CreateDateTime,120),User_Id) as pb where pb.User_Id=a.UserId and a.[Date]=pb.Date2
20、游标循环更新查询出来的数据
ALTER PROCEDURE [dbo].[updateCode] AS BEGIN declare @gid varchar(50), @code varchar(50), @pid varchar(50) --定义一个游标 declare user_cur cursor for SELECT ka_product_id from ka_return_order_line WHERE return_order_no in(SELECT return_order_no FROM ka_return_order_head where ka_id=181) and bar_code is null --打开游标 open user_cur --读取游标 fetch next from user_cur into @pid while @@fetch_status=0 begin -- 定义内层游标 DECLARE code_cur cursor for SELECT goods_id,barcode from internationl_barcode open code_cur fetch next from code_cur into @gid,@code while @@fetch_status=0 BEGIN -- 判断internationl_barcode里有的code,然后更新 if(@pid=@gid) BEGIN UPDATE ka_return_order_line set bar_code=@code WHERE ka_product_id=@pid BREAK END fetch next from code_cur into @gid,@code END close code_cur --摧毁游标 deallocate code_cur fetch next from user_cur into @pid end close user_cur --摧毁游标 deallocate user_cur END
21、循环删除大量数据不出现死锁 https://www.cnblogs.com/CoderAyu/p/11375088.html
SET ROWCOUNT 500 delete_more: delete from `后宫佳丽` where age>18 IF @@ROWCOUNT > 0 GOTO delete_more SET ROWCOUNT 0
22、SQL两表之间：根据一个表的字段更新另一个表的字段
(1). 写法轻松，更新效率高：
update table1
set field1=table2.field1,
field2=table2.field2
from table2
where table1.id=table2.id
(2). 常规方式，种写法相当于一个 Left join， 以外面的where为更新条数，如果不加where就是所有记录
update table1
set field1=(select top 1 field1 from table2 where table2.id=table1.id)
where table1.id in (condition)
23、SqlServer批量删除大量数据
用一次性dao删除的方法是很不明du智的。
1、delete操作会被zhi完整记录到日志dao里,它需要大量空间回和时间;
2、如果删答除中间发生中断,一切删除会回滚(在一个事务里);
3、同时删除多行,记录上的锁也许会被提升为排它表锁,从而阻碍操作完成之前有对这个表的操作(有时候会妨碍正常的业务)所以一般采取分批删除的方法.
所以我们可以通过分批次迁移压缩数据，这样可以大大提升执行速度和执行效率
首先通过set rowcount 来控制每次删除的记录数
执行Sql语句
SET ROWCOUNT 0; 记录每次执行删除的条数
通过TOP的方法，并且使用循环
WHILE 1 = 1
BEGIN
DELETE TOP(5000) FROM dbo.Table WHERE time< '2016-12-12';
IF @@rowcount < 5000
BREAK;
END
24、MySql批量删除大量数据
drop procedure if exists delete_huge;
DELIMITER $$
CREATE PROCEDURE delete_huge()
BEGIN
REPEAT
SELECT SLEEP(0.1);
delete from `table` where time<'2016-12-12' limit 5000;
UNTIL ROW_COUNT()=0 END REPEAT;
END$$
DELIMITER ;
call delete_huge();
drop procedure if exists delete_huge;
25.sqlserver复制自增列表
https://www.cnblogs.com/cnzeno/p/7161323.html
第4步一定要指定列，第一步可以手动创建
--1、创建与原表表结构一致的临时表，并且在列上添加了自增属性 CREATE TABLE dbo.Tmp_t1 ( id int NOT NULL IDENTITY (10, 1), c1 char(10) NULL, c2 char(10) NULL ) ON [PRIMARY] --2、把新增临时表的锁升级为表锁 ALTER TABLE dbo.Tmp_t1 SET (LOCK_ESCALATION = TABLE) --3、设置新增临时表的自增列为可插入状态 SET IDENTITY_INSERT dbo.Tmp_t1 ON --4、把原表中的数据插入到临时表里，列一定要指定 INSERT INTO dbo.Tmp_t1 (id, c1, c2) SELECT id, c1, c2 FROM dbo.t1 WITH (HOLDLOCK TABLOCKX) --5、设置新增临时表的自增列为不可插入状态 SET IDENTITY_INSERT dbo.Tmp_t1 OFF --6、删除原表 DROP TABLE dbo.t1 --7、把临时表的表名修改为跟原表一致 EXECUTE sp_rename N'dbo.Tmp_t1', N't1', 'OBJECT'
26、表结构一样，从a2表中找出不存在于a1表的数据，并插入a1表
insert into a1([Name],[code]) select [Name],[code] from a2 where not exists(select * from a1 where a1.name = a2.name)
27、select * from table with (nolock) http://www.cnblogs.com/xiaofei59/archive/2011/04/15/2017359.html
要提升SQL的查询效能，一般来说大家会以建立索引(index)为第一考虑。其实除了index的建立之外，当我们在下SQL Command时，在语法中加一段WITH (NOLOCK)可以改善在线大量查询的环境中数据集被LOCK的现象藉此改善查询的效能。
不过有一点千万要注意的就是，WITH (NOLOCK)的SQL SELECT有可能会造成Dirty Read
