工作中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