Sql常见问题总结二（Sql语句怎么样查询IP，游标

文章导航 SQL Server 2005 学习笔记系列文章导航（存储过程，视频，索引，Clr，各种工具使用）

这篇文章是上篇 SQl使用方法总结的延续篇

1.当很多在共用你自己的数据时，时不时的会报这种错误“已超过了锁请求超时时段”

直接把下在面的存储过程放到你的Master数据库执行一下就可以了

View Code

        
          set
        
         ANSI_NULLS 
        
          ON
        
        
          set
        
         QUOTED_IDENTIFIER 
        
          ON
        
        
          go
        
        
          ALTER
        
        
          Proc
        
        
          [
        
        
          dbo
        
        
          ]
        
        .
        
          [
        
        
          Sp_KillAllProcessInDB
        
        
          ]
        
        
          @DbName
        
        
          VarChar
        
        (
        
          100
        
        )


        
          as
        
        
          if
        
        
          db_id
        
        (
        
          @DbName
        
        ) 
        
          =
        
        
          Null
        
        
          begin
        
        
          Print
        
        
          '
        
        
          DataBase dose not Exist
        
        
          '
        
        
          end
        
        
          else
        
        
          Begin
        
        
          Declare
        
        
          @spId
        
        
          Varchar
        
        (
        
          30
        
        )


        
          DECLARE
        
         TmpCursor 
        
          CURSOR
        
        
          FOR
        
        
          Select
        
        
          '
        
        
          Kill 
        
        
          '
        
        
          +
        
        
          convert
        
        (
        
          Varchar
        
        , spid) 
        
          as
        
         spId

        
          from
        
         master..SysProcesses

        
          where
        
        
          db_Name
        
        (dbID) 
        
          =
        
        
          @DbName
        
        
          and
        
         spId 
        
          <>
        
        
          @@SpId
        
        
          and
        
         dbID 
        
          <>
        
        
          0
        
        
          OPEN
        
         TmpCursor


        
          FETCH
        
        
          NEXT
        
        
          FROM
        
         TmpCursor

        
          INTO
        
        
          @spId
        
        
          WHILE
        
        
          @@FETCH_STATUS
        
        
          =
        
        
          0
        
        
          BEGIN
        
        
          Exec
        
         (
        
          @spId
        
        )


        
          FETCH
        
        
          NEXT
        
        
          FROM
        
         TmpCursor

        
          INTO
        
        
          @spId
        
        
          END
        
        
          CLOSE
        
         TmpCursor

        
          DEALLOCATE
        
         TmpCursor


        
          end

这个时候如果再有这种情况出现只要你执行一下存储过程就行了如例子

exec Sp_KillAllProcessInDB ' 要访问的数据库 '

2.如果修改数据的架构


                      --'guest.Table_1'架构名+（表名，视频，存储过程），新架构名，


                      EXEC


                       SP_ChangeObjectOwner


                      'guest.Table_1'


                      'dbo'

3.如果安装时选择的windows验证方法，怎么修改为Windows验证和登录用户验证模式？

View Code

        
          /*
        
        
           标题：更改登录用户验证方式 作者：苏飞  时间：2011-09-25 地点：郑州 
        
        
          */
        
        
          /*
        
        
               登录用户的验证方式一般是在 SQL Server 2005 安装时被确定的。如果需要改变登录用 户的验证方式，只可以通过 SQL Server Configuration Manager 改变服务器的验证方式。改 变登录用户的验证方式的步骤如下：
1、通过"开始"/"程序"/"Microsoft SQL Server 2005"/"SQL Server Management Studio"菜单 打开SQL Server Management Studio 工具。
2、通过"连接到服务器"对话框连接到需要改变登录用户验证方式的 SQL Server 2005 服务器。
3、连接正确后，SQL Server Management Studio 中的"对象资源管理器"版面将出现连接的服务 器。选中这个服务器，单击鼠标右键，选择"属性"菜单命令。
4、选择"服务器属性"对话框中的"选择页"版面内的"安全性"页面。
5、在"服务器身份验证"框架内，重新选择登录用户的验证方式。选择完成后单击"确定"按钮， 这时会弹出"SQL Server Management Studio"提示框，提示重新启动 SQL Server后做作的更改 才会生效。
6、单击"SQL Server Management Studio"提示框中的"确定"按钮，重新启动 SQL Server，即可 更改登录用户的验证方式。

        
        
          */

4.SQl中如何处理Nvarchar数字排序问题

select top 10 * from 表名 order by cast ( Ltrim (字段名) as int ) desc

5.时间格式转化

View Code

-- 日期转换参数 select CONVERT ( varchar , getdate (), 120 ) -- 2009-03-15 15:10:02 select replace ( replace ( replace ( CONVERT ( varchar , getdate (), 120 ), ' - ' , '' ), ' ' , '' ), ' : ' , '' ) -- 20090315151201 select CONVERT ( varchar ( 12 ) , getdate (), 111 ) -- 2009/03/15 select CONVERT ( varchar ( 12 ) , getdate (), 112 ) -- 20090315 select CONVERT ( varchar ( 12 ) , getdate (), 102 ) -- 2009.03.15 select CONVERT ( varchar ( 12 ) , getdate (), 108 ) -- 15:13:26 其它我不常用的日期格式转换方法: select CONVERT ( varchar ( 12 ) , getdate (), 101 ) -- 03/15/2009 select CONVERT ( varchar ( 12 ) , getdate (), 103 ) -- 15/03/2009 select CONVERT ( varchar ( 12 ) , getdate (), 104 ) -- 15.03.2009 select CONVERT ( varchar ( 12 ) , getdate (), 105 ) -- 15-03-2009 select CONVERT ( varchar ( 12 ) , getdate (), 106 ) -- 15 03 2009 select CONVERT ( varchar ( 12 ) , getdate (), 107 ) -- 15, 2009 select CONVERT ( varchar ( 12 ) , getdate (), 109 ) -- 03 15 2009 select CONVERT ( varchar ( 12 ) , getdate (), 110 ) -- 03-15-2009 select CONVERT ( varchar ( 11 ) , getdate (), 113 ) -- 15 03 2009 select CONVERT ( varchar ( 12 ) , getdate (), 114 ) -- 15:15:55:717

6.两个时间的运算

DATEDIFF ( minute , 时间, getdate ())

第一个参数可以是下列任意一个

年 yy, yyyy 季度 qq, q 月 mm, m 年中的日 dy, y 日 dd, d 周 wk, ww 星期 dw, w 小时 hh 分钟 mi, n 秒 ss, s 毫秒 ms 微妙 mcs 纳秒 ns

例子

ELECT DATEDIFF ( day , ' 2008-12-29 ' , ' 2008-12-30 ' )

结果是1

ELECT DATEDIFF ( day , ' 2008-12-30 ' , ' 2008-12-29 ' )

结果是-1

7.相同结构表去重复后进行复制

insert into 新表名select DomainInFo_FIp.D_Domain,源表名.D_ID,源表名.D_IP,源表名.stat from 源表名

inner join ( select min (D_ID) D_ID, distinct (D_Domain) as D_Domain from 源表名) TempTab on 源表名.D_ID = TempTab.D_ID

8.一个取一张表中域名第一个“.”之前部分，并写入另外一张表的游标实现

declare @D_Domain nvarchar ( 200 ); Declare Mycursor cursor for select D_Domain FROM dbo.DomainInFo_FIp Open Mycursor Fetch next from Mycursor into @D_Domain -- 开始抓数据 while @@FETCH_STATUS = 0 begin set @D_Domain =Left ( @D_Domain , Charindex ( ' . ' , @D_Domain ) - 1 ) if ( Len ( @D_Domain ) > 0 and @D_Domain <> ' www ' ) begin insert into dbo.DomainInFo_FIp_log (D_Domain) values ( @D_Domain ) end Fetch next from Mycursor into @D_Domain end Close Mycursor -- 关闭游标 Deallocate Mycursor -- 删除游标

9.计算一表中某个字段的重复次数，游标实现

declare @D_Domain nvarchar ( 200 ); declare @count int ; Declare Mycursor cursor for select D_Domain FROM dbo.DomainInFo_FIp_log Open Mycursor Fetch next from Mycursor into @D_Domain -- 开始抓数据 while @@FETCH_STATUS = 0 begin select @count = count ( * ) from DomainInFo_FIp_log where D_Domain = @D_Domain update DomainInFo_FIp_log set numindex = @count where D_Domain = @D_Domain Fetch next from Mycursor into @D_Domain end Close Mycursor -- 关闭游标 Deallocate Mycursor -- 删除游标

10.Replace函数的使用方法

update dbo.DomainInFo_FIp set D_Domain = Replace (D_Domain, ' = ' , '' )

update dbo.DomainInFo_FIp set D_Domain = Replace (D_Domain, ' < ' , '' )

update dbo.DomainInFo_FIp set D_Domain = Replace (D_Domain, ' - ' , '' )

update dbo.DomainInFo_FIp set D_Domain = Replace (D_Domain, ' ~ ' , '' )

update dbo.DomainInFo_FIp set D_Domain = Replace (D_Domain, ' ] ' , '' )

update dbo.DomainInFo_FIp set D_Domain = Replace (D_Domain, ' [ ' , '' )

update dbo.DomainInFo_FIp set D_Domain = Replace (D_Domain, ' $ ' , '' )

update dbo.DomainInFo_FIp set D_Domain = Replace (D_Domain, ' { ' , '' )

update dbo.DomainInFo_FIp set D_Domain = Replace (D_Domain, ' } ' , '' )

update dbo.DomainInFo_FIp set D_Domain = Replace (D_Domain, ' · ' , '' )

11.Substring函数与Len以及Left的配合使用（字段D_Domain值第一个字符是点的就清除这个点）

update dbo.DomainInFo_FIp set D_Domain = Substring (D_Domain, 2 , Len (D_Domain)) where Left (D_Domain, 1 ) = ' . '

12.一个实现怎么样查询IP所在段的函数代码如下

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER FUNCTION [ dbo ] . [ GetIP ] ( @ip varchar ( 20 )) RETURNS varchar ( 20 ) AS BEGIN declare @IPre varchar ( 20 ) SET @IPre = '' select @IPre = right ( ' 00 ' + ParseName ( @IP , 4 ), 3 ) + ' . ' + right ( ' 00 ' + ParseName ( @IP , 3 ), 3 ) + ' . ' + right ( ' 00 ' + ParseName ( @IP , 2 ), 3 ) + ' . ' + right ( ' 00 ' + ParseName ( @IP , 1 ), 3 ) RETURN @IPre END

如果大家想快速查询的话我建议大家先把自己数据的IP段执行一下这个函数，然后每次查询的时候直接转要查询的IP就行了，这样速度会很快的。

例子如下：

-- ============================================= -- Author: <Author,sufei> -- Create date: <Create Date,2011-12-03> -- Description: <Description,查询IP地址> -- ============================================= ALTER PROCEDURE [ dbo ] . [ GetIPInfoByIP ] @ip varchar ( 200 ) output AS BEGIN SET @ip = dbo.GetIP( @ip ) SELECT iptitle FROM Qqwry WHERE GetIP( @ip ) BETWEEN ipst AND ipend END

大家一定不要傻的这样写语句

SELECT iptitle FROM Qqwry WHERE GetIP( @ip ) BETWEEN ipst AND ipend

因为这样的速度是极慢的，那是因为每一次检查都要转化。

在这里也提示大家在写Sql语句时不要把函数直接写在查询语句内，当然如果是必须的那就没有办法了只能那样了。

13.解决数据库正在使用无法分离的存储过程

View Code

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER proc [ dbo ] . [ p_killspid ] @dbname varchar ( 200 ) -- 要关闭进程的数据库名 as declare @sql nvarchar ( 500 ) declare @spid nvarchar ( 20 ) declare #tb cursor for select spid = cast (spid as varchar ( 20 )) from master..sysprocesses where dbid = db_id ( @dbname ) open #tb fetch next from #tb into @spid while @@fetch_status = 0 begin exec ( ' kill ' + @spid ) fetch next from #tb into @spid end close #tb deallocate #tb

使用方法

exec dbo.p_killspid ‘数据库名称’

　就先到这里吧，虽然不多，但也是大家常见的问题，希望各位网友也把自己的经验分享一下。

14.删除表中重复数大于N的数据

delete from dbo.DomainInFo_FIp_domain_old where D_IP in ( select D_IP FROM ( select max(D_IP)AS D_IP ,count(D_IP) AS con FROM DomainInFo_FIp_domain_old group by D_IP) as t WHERE con> 500000 )

15.把表中重复数小于N的数据写入到一个新表中去（比较删除的速度快100倍以上）

insert dbo.DomainInFo_FIp_domain_new select D_Domain,D_IP from dbo.DomainInFo_FIp_domain_old where D_IP in ( select D_IP FROM ( select max(D_Domain) as D_Domain, max(D_IP)AS D_IP ,count(D_IP) AS con FROM dbo.DomainInFo_FIp_domain group by D_IP ) as t WHERE con< 5000 )

16.把一个表中某列转成以豆号分隔的字符串

declare @column_name varchar ( 2000 ) set @column_name = '' select @column_name = @column_name + convert ( varchar ,column_name ) + ' , ' from table

17.怎么取时间中的小时，分钟等值

返回表示指定日期的指定日期部分的整数。语法

DATEPART ( ‘下面备注里面的选项’, date )

参数 datepart 指定要返回的日期部分的参数。有关详细信息，

日期部分缩写年份 yy、yyyy

季度 qq、q

月份 mm、m

每年的某一日 dy、y

日期 dd、d

星期 wk、ww

工作日 dw

小时 hh

分钟 mi、n

秒 ss、s

毫秒 ms

date 返回 datetime 值（或可隐式转换为 datetime 值的值）的表达式。date 参数也可以是日期格式的字符串。datetime 数据类型仅用于 1753 年 1 月 1 日之后的日期。对于之前的日期，将存储为字符数据。在输入 datetime 值时，请始终使用单引号将它们括起来。

如果只指定年份的后两位数字，小于或等于两位截止年份配置选项值的后两位数字的值将与截止年份处于同一世纪中。比此选项值的后两位数字大的值先于截止年份的世纪。例如，如果两位数字的截止年份是 2049（默认值），则“49”将被解释为 2049，而“50”将被解释为 1950。为了避免产生歧义，请使用四位年份。

有关指定日期的详细信息，请参阅数据类型中的 datetime。

返回值 int

-------------------------------------------------------------签名部分------------------------------------------------------------------------

欢迎大家转载，如有转载请注明文章来自： http://sufei.cnblogs.com/

签名：做一番一生引以为豪的事业;在有生之年报答帮过我的人;并有能力帮助需要帮助的人;

软件开发，功能定制，请联系我 QQ:361983679 Email: sufei.1013@163.com MSN: sufei.1013@163.com

-------------------------------------------------------------------推荐文章--------------------------------------------------------------
1. C#仿QQ皮肤 2. Sql2005学习笔记 3. httpHelper类

Sql常见问题总结二（Sql语句怎么样查询IP，游标去重复，各种函数使用，各种取时间格式，字符串精确排序，超时锁问题）

更多文章、技术交流、商务合作、联系博主

微信扫码或搜索：z360901061

微信扫一扫加我为好友

QQ号联系： 360901061

您的支持是博主写作最大的动力，如果您喜欢我的文章，感觉我的文章对您有帮助，请用微信扫描下面二维码支持博主2元、5元、10元、20元等您想捐的金额吧，狠狠点击下面给点支持吧，站长非常感激您！手机微信长按不能支付解决办法：请将微信支付二维码保存到相册，切换到微信，然后点击微信右上角扫一扫功能，选择支付二维码完成支付。

【本文对您有帮助就好】元

2元

5元

10元

20元

自定义