`
txf2004
  • 浏览: 6866709 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类
最新评论

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类

分享到：
| [ACM_ZOJ_1003]Crashing Balloon
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    精通sql结构化查询语句

    8.2.1 日期时间函数 8.2.2 CONVERT函数转换日期、时间 8.2.3 数据运算函数 8.3 字符串函数 8.3.1 字符串处理函数介绍 8.3.2 字符转换函数 8.3.3 去字符串空格函数 8.3.4 取字符串长度的函数 8.3.5 查询包含部分字符...

    Sql Server与Oracle的区别

    7. 把所有 PL/SQL 游标改为非游标 SELECT 语句或 Transact-SQL 游标。 8. 用 Transact-SQL 过程代替 PL/SQL 过程、函数和包。 9. 把 PL/SQL 触发器转换为 Transact-SQL 触发器。 10. 使用 SET SHOWPLAN 语句，...

    ORACLE和SQL Server的语法区别

    7. 把所有 PL/SQL 游标改为非游标 SELECT 语句或 Transact-SQL 游标。 8. 用 Transact-SQL 过程代替 PL/SQL 过程、函数和包。 9. 把 PL/SQL 触发器转换为 Transact-SQL 触发器。 10. 使用 SET SHOWPLAN 语句，优化...

    SQl 函数说明大全

    9.行集函数 返回可在 Transact-SQL 语句中表引用所在位置使用的行集。 10.安全函数 返回有关用户和角色的信息。 11.字符串函数 可更改 char、varchar、nchar、nvarchar、binary 和 varbinary 的值。 12.系统函数 对...

    oracle和SQL的语法区别

    5. 用“+”字符串串联运算符代替“||”字符串串联运算符。 6. 用 Transact-SQL 程序代替 PL/SQL 程序。 7. 把所有 PL/SQL 游标改为非游标 SELECT 语句或 Transact-SQL 游标。 8. 用 Transact-SQL 过程代替 PL/SQL...

    表的相关查询(最全面的查询语句集合)

    --SQL2000/2005字符串拆分为列表通用函数 --SQL2005以上版本可以结合apply进行拆分列值 --4、FOR XML PATH 语句的应用 --5、一些有用的系统存储过程及用法 --6、关于死锁 --7、添加远程服务器 --8、日期的相关操作 -...

    达梦数据库_SQL语言手册

    达梦数据库_SQL语言手册.pdf 数据库快照定义语句 数据库快照删除语句 第章数据查询语句和全文检索语句 单表查询 简单查询 带条件查询 集函数 情况表达式 连接查询 子查询 标量子查询 表子查询 派生表子...

    SQL应用开发范例宝典：SQL应用开发范例宝典.iso (源码光盘)

    内容包括SQL语言基础、常规数据查询、高级数据过滤、字符串查询、日期函数、日期函数、数据排序、聚集函数与分组统计、使用子查询、多表查询、高级查询、插入数据、更新和删除数据、创建、操纵数据库和表、使用视图...

    SQL_SERVER应用与开发范例宝典_12357672.part3

    内容包括SQL语言基础、常规数据查询、高级数据过滤、字符串查询、日期函数、日期函数、数据排序、聚集函数与分组统计、使用子查询、多表查询、高级查询、插入数据、更新和删除数据、创建、操纵数据库和表、使用视图...

    SQL SERVER 2000开发与管理应用实例

    本书不但融合了作者在使用SQL Server 2000过程中遇到的各种常见问题和应用案例，还总结了作者几年来在CSDN 社区SQL Server版所解决的大量问题，面向实际项目需求，涉及不同类型的应用，能够多角度地引导读者学习相关...

    SQL21日自学通

    第二天查询— — SELECT 语句的使用 30 目标 30 背景 30 一般的语法规则 30 你的第一个查询 33 总结 37 问与答 38 校练场 38 练习 39 第三天表达式条件语句与运算 40 第四天函数对数据的进一步处理 60 目标 60 汇总...

    SQL_SERVER应用与开发范例宝典_12357672.part2

    内容包括SQL语言基础、常规数据查询、高级数据过滤、字符串查询、日期函数、日期函数、数据排序、聚集函数与分组统计、使用子查询、多表查询、高级查询、插入数据、更新和删除数据、创建、操纵数据库和表、使用视图...

    SQL_SERVER应用与开发范例宝典_12357672.part1

    内容包括SQL语言基础、常规数据查询、高级数据过滤、字符串查询、日期函数、日期函数、数据排序、聚集函数与分组统计、使用子查询、多表查询、高级查询、插入数据、更新和删除数据、创建、操纵数据库和表、使用视图...

    SQLServer2008查询性能优化 2/2

    9.8.4 实现准备/执行模式以避免重传查询字符串 253 9.8.5 避免即席查询 253 9.8.6 对于动态查询sp_executesql优于EXECUTE 253 9.8.7 小心地参数化查询的可变部分 254 9.8.8 不要允许查询中对象的隐含解析 254 ...

    大数据Oracle理论+SQL+面试问题汇总+BI理论

    汇总了Oracle在开发中常用到的各种函数如分析函数、开窗函数、数字函数、字符串函数、时间函数、转换函数、空值转换函数等。从SQL句子的讲解到函数、游标、存储过程、序列等进行了例子讲解。如果你现在从事的行业有

    Oracle PL/SQL语言初级教程

    单行字符串函数 30 单行转换函数 37 SQL中的组函数 40 嵌套函数 42 4.Oracle数据库数据对象分析（上） 42 删除表和更改表名 46 管理视图 47 5.Oracle数据库数据对象分析（中） 49 完整性约束 49 Check 约束 49 NOT ...

    SQLServer2008查询性能优化 1/2

    9.8.4 实现准备/执行模式以避免重传查询字符串 253 9.8.5 避免即席查询 253 9.8.6 对于动态查询sp_executesql优于EXECUTE 253 9.8.7 小心地参数化查询的可变部分 254 9.8.8 不要允许查询中对象的隐含解析 254 ...

    sqlserver2000基础(高手也有用)

    3.6.4　动态Transact-SQL语句中常见问题　92 3.7　text与ntext字段处理　96 3.7.1　text与ntext数据的存储　96 3.7.2　管理text与ntext数据　97 3.7.3　text与ntext字段的复制和合并　99 3.7.4　text与image字段转换...

Magicbox
Global site tag (gtag.js) - Google Analytics