数据处理中，SQL收藏`(比较实用)

1. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。     
2. 在所有的存储过程、触发器或者没有必要返回受影响行的处理中。在SQL的开始处设置 SET NOCOUNT ON; ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。 (如有需要防止脏数据或者并发数据，加上锁SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;) 

SQL收藏:

取第10-15的记录

select top 5 * from YX_T_Khb where khid not in (select top 10 khid from YX_T_Khb)

 

 

从EXCEL获取信息

select *  from

openrowset('MicroSoft.Jet.OleDB.4.0','Excel 5.0;HDR=yes;Database=d://web_tlerp/kpinfo.xls',aa$)

 

备份sql server
--创建 备份数据的 device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:/mssql7backup/MyNwind_1.dat'

--sp_addumpdevice:存储过程会将一个备份设备添加到 sys.backup_devices 目录视图中

--sp_addumpdevice 不执行对物理设备的任何访问。

--select * from sys.backup_devices (查询所有备份目录视图)

--开始 备份
BACKUP DATABASE pubs TO testBack

 

随机取出10条数据

select top 10 * from tablename order by newid()

获取表结构相关信息

Select 表名 = CASE WHEN a.colorder = 1 THEN d .name ELSE '' END,
      表说明 = CASE WHEN a.colorder = 1 THEN isnull(f.value, '') ELSE '' END,
      字段序号 = a.colorder, 字段名 = a.name,
      标识 = CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity')
      = 1 THEN '√' ELSE '' END, 主键 = CASE WHEN EXISTS
          (Select 1
         FROM sysobjects
         Where xtype = 'PK' AND parent_obj = a.id AND name IN
                   (Select name
                  FROM sysindexes
                  Where indid IN
                            (Select indid
                           FROM sysindexkeys
                           Where id = a.id AND colid = a.colid))) THEN '√' ELSE '' END,
      类型 = b.name, 占用字节数 = a.length, 长度 = COLUMNPROPERTY(a.id, a.name,
      'PRECISION'), 小数位数 = isnull(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0),
      允许空 = CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END, 默认值 = isnull(e.text,
      ''), 字段说明 = isnull(g.[value], '')
FROM syscolumns a LEFT JOIN
      systypes b ON a.xusertype = b.xusertype INNER JOIN
      sysobjects d ON a.id = d .id AND d .xtype = 'U' AND d .name <> 'dtproperties' and d.name='Orders' LEFT
      JOIN
      syscomments e ON a.cdefault = e.id LEFT JOIN
      sysproperties g ON a.id = g.id AND a.colid = g.smallid LEFT JOIN
      sysproperties f ON d .id = f.id AND f.smallid = 0
ORDER BY a.id, a.colorder

 

--获取指定表的字段

Select a.name AS 字段名, b.name AS 类型
FROM syscolumns a INNER JOIN
      systypes b ON a.xusertype = b.xusertype INNER JOIN
      sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties'
Where (d.name = 'Products')
ORDER BY a.id

 

    
--ＳＱＬ中用正则匹配查询
select * from tab1 where title like '%[0-9]abc[0-9]%'

 

索引列上>=代替>
 低效：select * from emp where deptno > 3
 高效：select * from emp where deptno >=4
两者的区别在于，前者dbms将直接跳到第一个deptno等于4的记录，而后者将首先定位到deptno等于3的记录并且向前扫描到第一个deptno大于3的

 

用exists替代in ，可以提高查询的效率。
 用not exists 替代 not in

 

优化group by(当然是你要group by 的字段了，其它字段用where就OK了~)
提高group by语句的效率，可以将不需要的记录在group by之前过滤掉。如：
低效：select job, avg(sal) from emp group by job having job = 'president'  or job='manager'
高效：select job, avg(sal) from emp having job='president' or job='manager'  group by job

 

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

1.按姓氏笔画排序:
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as

 

2.数据库加密:
select encrypt('原始密码')
select pwdencrypt('原始密码')
select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同 encrypt('原始密码')
select pwdencrypt('原始密码')
select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同

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

 

 

 

-------------------------------日期经典start---------------------------------

1、确定某年某月有多少天

实现原理：先利用DATEDIFF取得当前月的第一天，再将月份加一取得下月第一天，然后减去1分钟，再取日期的天数部分，即为当月最大日期，也即当月天数

CREATE FUNCTION DaysInMonth ( @date datetime ) Returns int
AS
BEGIN
 RETURN Day(dateadd(mi,-3,DATEADD(m, DATEDIFF(m,0,@date)+1,0)))
END

 

2.计算哪一天是本周的星期一

SELECT DATEADD(week, DATEDIFF(week,'1900-01-01',getdate()), '1900-01-01') 
或
SELECT DATEADD(week, DATEDIFF(week,0,getdate()),0)

 

3.当前季度的第一天

SELECT DATEADD(quarter, DATEDIFF(quarter,0,getdate()), 0)

 

4.一个季度多少天

declare @m tinyint,@time smalldatetime

select @m=month(getdate())
select @m=case when @m between 1 and 3 then 1
                       when @m between 4 and 6 then 4
                       when @m between 7  and 9 then 7
                       else 10 end

select @time=datename(year,getdate())+'-'+convert(varchar(10),@m)+'-01'
select datediff(day,@time,dateadd(mm,3,@time))

 

5.本年的最后一天 　

SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。  

 

6.如何取得某个月的天数  
　　declare @m int  
　　set @m=2 --月份  
　　select datediff(day,'2003-'+cast(@m as varchar)+'-15' ,'2003-'+cast(@m+1 as varchar)+'-15')  

 

7.判断是否闰年
SELECT case day(dateadd(mm, 2, dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)))) when 28 then '平年' else '闰 end  
　　或者  
　　select case datediff(day,datename(year,getdate())+'-02-01',dateadd(mm,1,datename(year,getdate())+'-02-01'))  
　　when 28 then '平年' else' 闰年' end  

 

--------------------------------日期经典end----------------------------------

 

将float转换成varchar(从excel里导入到数据库表经常忘记设置类型):转成decimal->再转成varchar即可

select convert(varchar(20),cast(convert(float,'1.58609e+010') as decimal))