sqlServer2000实用sql语句

:arrow: 1.备份数据库

backup database master to DISK ='d:\database.dat'

2.还原数据库

restore database master from disk=''d:\database.dat'

3.十五位身份证号转18位

-- =============================================
-- Create inline function (IF)
-- =============================================
IF EXISTS (SELECT * 
	   FROM   sysobjects 
	   WHERE  name = 'checkCardId')
	DROP FUNCTION  dbo.checkCardId
GO

CREATE FUNCTION dbo.checkCardId(@cardId varchar(18))
RETURNS int
AS
BEGIN
     DECLARE @ret int
     declare @year  int
     declare @month int
     declare @day int
     declare @date datetime
     SET @ret= 1
     if(len(@cardId) = 15)
     begin
	 set @year = cast('19' + substring(@cardId,7,2) as int)
         set @month = cast(substring(@cardId,9,2) as int)
	 set @day = cast(substring(@cardId,11,2) as int)
         if(@month=0 or @month>12 or @day>31 or @day = 0) set @ret =  -2
     end
     else if(len(@cardId) = 18)
     begin
	 set @year = cast(substring(@cardId,7,4) as int)
         set @month = cast(substring(@cardId,11,2) as int)
	 set @day = cast(substring(@cardId,13,2) as int)
         if(@month=0 or @month>12 or @day>31 or @day = 0)  set @ret =  -2
         else 
         if(substring(@cardId,18,1) <> case (substring(@cardId,1,1)*7+
         substring(@cardId,2,1)*9+
         substring(@cardId,3,1)*10+
         substring(@cardId,4,1)*5+
         substring(@cardId,5,1)*8+
         substring(@cardId,6,1)*4+
         substring(@cardId,7,1)*2+
         substring(@cardId,8,1)*1+
         substring(@cardId,9,1)*6+
         substring(@cardId,10,1)*3+
         substring(@cardId,11,1)*7+
         substring(@cardId,12,1)*9+
         substring(@cardId,13,1)*10+
         substring(@cardId,14,1)*5+
         substring(@cardId,15,1)*8+
         substring(@cardId,16,1)*4+
         substring(@cardId,17,1)*2)%11 

         when 0 then '1'
         when 1 then '0'
         when 2 then 'x'
         when 3 then '9'
         when 4 then '8'
         when 5 then '7'
         when 6 then '6'
         when 7 then '5'
         when 8 then '4'
         when 9 then '3'
         when 10 then '2' end)
          set @ret =  -1
     end
     else
       set @ret =  -3
     RETURN(@ret)
END  
GO

使用：
：

update bbjy_person
 set person_cardid = dbo.getcardId(person_cardid)
where len(person_cardid)=15

4.存储过程游标用法

CREATE PROC SP_COLLECTIONMAKE
  @period varchar(6)
as 
declare @persId varchar(11)       --个人编号
declare cur_person cursor for 
select INSPERSON_PERSID
from CQCI_INSPERSON   
       WHERE INSPERSON_REGIS = @period 
open cur_person
FETCH NEXT FROM cur_person INTO @persId
WHILE (@@FETCH_STATUS = 0)
BEGIN 
    select @persId
FETCH NEXT FROM cur_person INTO @persId
End
CLOSE cur_person
DEALLOCATE cur_person

5.取日期
[code]
.一个月第一天的
Select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

2.本周的星期一
Select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

3.一年的第一天
Select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)

4.季度的第一天
Select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)

5.当天的半夜
Select DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)

6.上个月的最后一天
Select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))

7.去年的最后一天
Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))

8.本月的最后一天
Select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))

9.本年的最后一天
Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))

10.本月的第一个星期一
select DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0
[/code]