SQL备忘

最新推荐文章于 2024-11-11 08:41:40 发布

MS_DOS_1991

最新推荐文章于 2024-11-11 08:41:40 发布

阅读量113

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/xuezhizhang/archive/2013/02/06/2904641.html

版权

修改基础表后，刷新关联视图的两种方法：http://www.cnblogs.com/Sabre/archive/2012/04/23/2467144.html

更新ntext字段的方法：http://www.cnblogs.com/miaomiaoga/archive/2006/07/31/463858.html

断开指定用户对某个数据库的连接：

1.在指定的数据库中使用sp_who存储过程获得正在连接此数据库的用户

例如 sp_who sa

2.使用KILL 终止 SPID

例如 kill 10

分组编号（参考）：

---2000
----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2010-04-06 20:38:41
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--    Nov 24 2008 13:01:59 
--    Copyright (c) 1988-2005 Microsoft Corporation
--    Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([YM] int,[Name] varchar(1),[OrderNo] varchar(6))
insert [tb]
select 200901,'a','014123' union all
select 200901,'b','014723' union all
select 200901,'c','015145' union all
select 200902,'a','015146' union all
select 200902,'b','015178' union all
select 200902,'c','015100' union all
select 200903,'a','014174' union all
select 200901,'a','015197' union all
select 200903,'a','016127' union all
select 200901,'b','014567' union all
select 200901,'a','016200'
--------------开始查询--------------------------
select
 *,item=(select count(1)+1 from tb where ym=t.ym and name=t.name and OrderNo<t.OrderNo) 
from
 [tb] t
order by
  ym,name
----------------结果----------------------------
/* YM          Name OrderNo item
----------- ---- ------- --------------------
200901      a    014123  1
200901      a    015197  2
200901      a    016200  3
200901      b    014567  1
200901      b    014723  2
200901      c    015145  1
200902      a    015146  1
200902      b    015178  1
200902      c    015100  1
200903      a    014174  1
200903      a    016127  2

(11 行受影响)

*/

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2010-04-06 20:38:41
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--    Nov 24 2008 13:01:59 
--    Copyright (c) 1988-2005 Microsoft Corporation
--    Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([YM] int,[Name] varchar(1),[OrderNo] varchar(6))
insert [tb]
select 200901,'a','014123' union all
select 200901,'b','014723' union all
select 200901,'c','015145' union all
select 200902,'a','015146' union all
select 200902,'b','015178' union all
select 200902,'c','015100' union all
select 200903,'a','014174' union all
select 200901,'a','015197' union all
select 200903,'a','016127' union all
select 200901,'b','014567' union all
select 200901,'a','016200'
--------------开始查询--------------------------
select *,item=row_number()over(partition by ym,name order by OrderNo) from [tb] 
----------------结果----------------------------
/* YM          Name OrderNo item
----------- ---- ------- --------------------
200901      a    014123  1
200901      a    015197  2
200901      a    016200  3
200901      b    014567  1
200901      b    014723  2
200901      c    015145  1
200902      a    015146  1
200902      b    015178  1
200902      c    015100  1
200903      a    014174  1
200903      a    016127  2

(11 行受影响)

*/

分组取最大/最新值（借用上面分组编号的测试数据参考）：

select YM,[Name],OrderNo from 
(
select *,item=(select count(1)+1 from tb where YM=t.YM and Name=t.Name and OrderNo>t.OrderNo) 
from [tb] t
) t
where item=1
order by YM,[Name] 


select a.*
from [tb] a
where not exists
(
    select 1
    from [tb]
    where YM=a.YM and [Name]= a.[Name]  
    and  OrderNo> a.OrderNo
)
order by YM,[Name] 

select a.*
from [tb] a
where  exists
(
    select count(1)
    from [tb]
    where YM=a.YM and [Name]= a.[Name]  
    and  OrderNo> a.OrderNo
    having count(1)<1
)
order by YM,[Name] 


select a.*
from [tb] a
inner join 
(
    select YM,[Name],max(OrderNo) as OrderNo
    from [tb]
    group by YM,[Name]
) b on  a.YM=b.YM and a.[Name]=b.[Name] and a.OrderNo=b.OrderNo
order by YM,[Name] 


select a.*
from [tb] a
where OrderNo =
(
    select top 1 OrderNo
    from [tb]
    where YM=a.YM and [Name]= a.[Name]  
    order by OrderNo desc
)
order by YM,[Name]

在SQL Server中区分大小写的几种方法（参考）

从数据库Collate到存储过程到函数，各种方法都有，选择适合你的。

第一种：

ALTER TABLE tb
ALTER COLUMN colname nvarchar(100) COLLATE Chinese_PRC_CI_AS
--不区分大小写
ALTER TABLE tb
ALTER COLUMN colname nvarchar(100) COLLATE Chinese_PRC_CS_AS
--区分大小写
alter database 数据库 COLLATE Chinese_PRC_CS_AS

第二种：--创建如下用户自定义函数(UDF)

CREATE FUNCTION StrComp(@Str1 VARCHAR(50),@Str2 VARCHAR(50))
--ALTER FUNCTION StrComp(@Str1 VARCHAR(50),@Str2 VARCHAR(50))
RETURNS INTEGER
AS
BEGIN
DECLARE @i INTEGER
--DECLARE @Str1 VARCHAR(50)
--DECLARE @Str2 VARCHAR(50)
DECLARE @y INT
--SET @Str1='a'
--SET @Str2='A'
SET @i=0
--SELECT ASCII(SUBSTRING(@Str1,@i+1,1))
SET @y=1
DECLARE @iLen INT
SET @iLen = LEN(LTRIM(RTRIM(@Str1)))
IF LEN(LTRIM(RTRIM(@Str1))) < LEN(LTRIM(RTRIM(@Str2))) --THEN
SET @iLen = LEN(LTRIM(RTRIM(@Str2)))
WHILE (@i < @iLen)
BEGIN
IF (ASCII(SUBSTRING(@Str1,@i+1,1))=ASCII(SUBSTRING(@Str2,@i+1,1))) --THEN
SET @i = @i +1
ELSE
BEGIN
SET @y=0
BREAK
END
END
RETURN @y
END

测试：　

select *
from Table1
Where dbo.StrComp(Field1,'aAbB') =1

第三种：

　　SQL Server 数据库中的文本信息可以用大写字母、小写字母或二者的组合进行存储。例如，姓氏可"SMITH"、"Smith"或"smith"等形式出现。

　　数据库是否区分大小写取决于 SQL Server 的安装方式。如果数据库区分大小写，当搜索文本数据时，必须用正确的大小写字母组合构造搜索条件。例如，如果搜索名字"Smith"，则不能使用搜索条件"=smith"或"=SMITH"。

　　另外，如果服务器被安装成区分大小写，则必须用正确的大小写字母组合提供数据库、所有者、表和列的名称。如果提供的名称大小写不匹配，则 SQL Server 返回错误，报告"无效的对象名"。

　　当使用关系图窗格和网格窗格创建查询时，查询设计器始终正确地反映出服务器是否区分大小写。但是，如果在 SQL 窗格中输入查询，则必须注意使名称与服务器解释名称的方式相匹配。

　　如果服务器是用不区分大小写的选项安装的，则提示若要确定服务器是否区分大小写，请执行存储过程 sp_server_info，然后检查第18 行的内容。如果服务器是用不区分大小写的设置安装的，则 sort_order 选项将设置为"不区分大小写"。可以从查询分析器运行存储过程。

第四种：

select * from servers where convert(varbinary, name)=convert(varbinary, N'RoCKEY')

第五种：

　　如ascii('a')再配合Substring()一起用。

SQL Server中生成随机密码 (参考1、参考2)

-- 生成纯数字密码
SELECT LEFT(abs(checksum(newid()))+'0000000000',10)

-- 生成数字字母混合密码
SELECT LEFT(LOWER(NEWID()),8)

--From URL http://www.sqlservercentral.com/articles/SQL+Puzzles/2878/

CREATE PROCEDURE dbo.uspCreatePassword(    
  @UpperCaseItems SMALLINT--指定含有的大写个数
  ,    @LowerCaseItems SMALLINT--指定含有的小写个数
  ,    @NumberItems SMALLINT--指定含有的数字个数
  ,    @SpecialItems SMALLINT)--指定含有的特殊字符个数
 AS  
 SET NOCOUNT ON 
 DECLARE @UpperCase VARCHAR(26)
        , @LowerCase VARCHAR(26)
          , @Numbers VARCHAR(10)
          , @Special VARCHAR(13)
          , @Temp VARCHAR(8000)
          , @Password VARCHAR(8000)
          , @i SMALLINT
          , @c VARCHAR(1)
          , @v TINYINT 
-- Set the default items in each group of characters
SELECT @UpperCase = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
    ,    @LowerCase = 'abcdefghijklmnopqrstuvwxyz'
     ,    @Numbers = '0123456789'
     ,    @Special = '!@#$%&*()_+-='
     ,    @Temp = ''
     ,    @Password = '' 
-- Enforce some limits on the length of the password
IF @UpperCaseItems > 20
  SET @UpperCaseItems = 20 
IF @LowerCaseItems > 20
  SET @LowerCaseItems = 20 
IF @NumberItems > 20    
  SET @NumberItems = 20 
IF @SpecialItems > 20    
  SET @SpecialItems = 20 

-- Get the Upper Case Items
SET @i = ABS(@UpperCaseItems) 
WHILE @i > 0 AND LEN(@UpperCase) > 0
  SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@UpperCase) + 1
  ,    @c = SUBSTRING(@UpperCase, @v, 1)
  ,    @UpperCase = CASE 
       WHEN @UpperCaseItems < 0 
            THEN STUFF(@UpperCase, @v, 1, '') 
         ELSE @UpperCase 
  END
  ,    @Temp = @Temp + @c
  ,    @i = @i - 1 
-- Get the Lower Case Items
SET @i = ABS(@LowerCaseItems) 
WHILE @i > 0 AND LEN(@LowerCase) > 0    
  SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@LowerCase) + 1
  ,    @c = SUBSTRING(@LowerCase, @v, 1)
  ,    @LowerCase = CASE 
       WHEN @LowerCaseItems < 0 
           THEN STUFF(@LowerCase, @v, 1, '') 
         ELSE @LowerCase 
  END
  ,    @Temp = @Temp + @c
  ,    @i = @i - 1 

-- Get the Number Items
SET @i = ABS(@NumberItems) 
WHILE @i > 0 AND LEN(@Numbers) > 0    
  SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Numbers) + 1
  ,    @c = SUBSTRING(@Numbers, @v, 1)
  ,    @Numbers = CASE 
       WHEN @NumberItems < 0 
           THEN STUFF(@Numbers, @v, 1, '') 
         ELSE @Numbers 
  END
  ,    @Temp = @Temp + @c
  ,    @i = @i - 1 
  
-- Get the Special Items
SET @i = ABS(@SpecialItems) 
WHILE @i > 0 AND LEN(@Special) > 0    
  SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Special) + 1
  ,    @c = SUBSTRING(@Special, @v, 1)
  ,    @Special = CASE 
       WHEN @SpecialItems < 0 
           THEN STUFF(@Special, @v, 1, '') 
        ELSE @Special 
  END
  ,    @Temp = @Temp + @c
  ,    @i = @i - 1 
  
-- Scramble the order of the selected items
WHILE LEN(@Temp) > 0    
  SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Temp) + 1
  ,    @Password = @Password + SUBSTRING(@Temp, @v, 1)
  ,    @Temp = STUFF(@Temp, @v, 1, '') 

SELECT @Password



/*

--
exec uspCreatePassword 2,2,2,2

----------
MT&4g(5j


exec uspCreatePassword 2,2,4,0

------------
38Io84Vw

*/

/**
use master

SELECT name, dbo.fn_RandomPassword(8,8) as UserPassword
    FROM sysusers

dbo.fn_RandomPassword(最小长度,最大长度)
**/


--- Start vwRand --- 
SET quoted_identifier  ON 

GO 

SET ansi_nulls  ON 

GO 

IF EXISTS (SELECT * 
           FROM   sys.sysobjects 
           WHERE  id = Object_id(N'[dbo].[vwRand]') and OBJECTPROPERTY(id, N'IsView') = 1) 
  DROP VIEW [dbo].[vwrand] 

GO 

--created by Rick Toner on 03/16/2007 
--updated by Rick Toner on 03/16/2007 
CREATE VIEW dbo.vwrand 
AS 
  SELECT Rand() AS R 

GO 

SET quoted_identifier  off 

GO 

SET ansi_nulls  ON 

GO 
--Uncomment the below line if you need to implement security 
--GRANT SELECT ON [vwRand] TO [UserAccountOrDataseRole] 

--- End vwRand --- 


--- Start fn_Rand --- 
SET quoted_identifier  ON 

GO 

SET ansi_nulls  ON 

GO 

IF EXISTS (SELECT * 
           FROM   dbo.sysobjects 
           WHERE  id = Object_id(N'[dbo].[fn_Rand]') 
                  AND xtype IN (N'FN',N'IF',N'TF')) 
  DROP FUNCTION [dbo].[fn_rand] 

GO 

--created by Rick Toner on 03/16/2007 
--updated by Rick Toner on 03/16/2007 
CREATE FUNCTION fn_rand( ) 
RETURNS FLOAT 
AS 
  BEGIN 
    RETURN (SELECT r 
     FROM   vwrand) 
  END 

GO 

SET quoted_identifier  off 

GO 

SET ansi_nulls  ON 

GO 
--Uncomment the below line if you need to implement security 
--GRANT EXECUTE ON [fn_Rand] TO [UserAccountOrDataseRole] 

--- End fn_Rand --- 


--- Start fn_RandomPassword --- 
SET quoted_identifier  ON 

GO 

SET ansi_nulls  ON 

GO 

IF EXISTS (SELECT * 
           FROM   dbo.sysobjects 
           WHERE  id = Object_id(N'[dbo].[fn_RandomPassword]') 
                  AND xtype IN (N'FN',N'IF',N'TF')) 
  DROP FUNCTION [dbo].[fn_randompassword] 

GO 

--created by Rick Toner on 03/16/2007 
--updated by Rick Toner on 03/16/2007 
CREATE FUNCTION fn_randompassword 
               (@MinLength SMALLINT  = 5, 
                @MaxLength SMALLINT  = 8) 
RETURNS VARCHAR(100) 
AS 
  BEGIN 
    DECLARE  @Password VARCHAR(30) 
    DECLARE  @Length SMALLINT 
    DECLARE  @Position SMALLINT 
    DECLARE  @Characters VARCHAR(55) 
    DECLARE  @LetterPosition INT 
    DECLARE  @Letter CHAR(1) 
    SET @Characters = 'aeubcdfghjklmnpqrstvwxyzBCDFGHJKLMNPQRSTVWXYZ23456789' 
    SET @Password = '' 
    -- determine length 
    SET @Length = @MinLength 
                    + Round(dbo.Fn_rand() 
                              * (@MaxLength 
                                   - @MinLength),0,0) 
    SET @Position = 1 
    WHILE @Position <= @Length 
      BEGIN 
        BEGIN 
          BEGIN 
            SET @LetterPosition = CONVERT(INT,Round((dbo.Fn_rand() 
                                                       * (Len(@Characters) 
                                                            - 1)),0,0),1) 
                                    + 1 
            SET @Letter = Substring(@Characters,@LetterPosition,1) 
            SET @Password = @Password 
                              + @Letter 
          END 
        END 
        SET @Position = @Position 
                          + 1 -- incriment counter 
      END 
    -- return password 
    RETURN @password 
  END 

GO 

SET quoted_identifier  off 

GO 

SET ansi_nulls  ON 

GO 
--Uncomment the below line if you need to implement security 
--GRANT EXECUTE ON [fn_RandomPassword] TO [UserAccountOrDataseRole] 

--- End fn_RandomPassword ---