SQL经典短小代码收集-阿里云开发者社区

--SQL Server：
Select TOP N * From TABLE Order By NewID()

--开头到N条记录
Select Top N * From 表

--N到M条记录(要有主索引ID)
Select Top M-N * From 表Where ID in (Select Top M ID From 表) Order by ID Desc

--选择10从到15的记录
select top 5 * from (select top 15 * from table order by id asc) A order by id desc

--N到结尾记录
Select Top N * From 表Order by ID Desc

统计比例：0.11

select cast( (sum( case when id=2 then 1 else 0 end) +0.00)/Count(1) as decimal(18,2)) as 比例 from @rtb

--显示最后5条记录,但是显示的顺序必须为5,6,7,8,9,10,而不是10,9,8,7,6,5 如下解决方法:

select top 5 from test where id in(select top 5 from test orderby id desc) orderby id asc

--通过这个问题也能总结出4-10条,5-100条这种限定一定范围内的sql语句的写法:

selecttop<末端ID-顶端ID+1>*from<表名>where ID notin(selecttop<顶端ID-1>) ID from<表名>)

--例如:4-10条就应该写成
selecttop10-4+1*from test where id notin(selecttop4-1 id from test)

上一篇: select top 1 * from [news_table] where [新闻标识列]<当前id号 where ......
下一篇: select top 1 * from [news_table] where [新闻标识列]>当前id号 where ...... order by [新闻标识列] desc

  
 
 
  --
  最新发布的20条信息列表，要求包含：信息ID、信息标题、信息发布时间、信息发布人姓名、信息评论总数和最后评论时间，并且按最后评论时间排序
  Select
  Top
  (
  100
  ) a.infoID,a.infoTitle,a.infoPubDate,c.userName,
  Max
  ( b.infoReplyDate ) 
  As
  最后时间,
  Count
  ( b.infoReplyID ) 
  As
   评论总数 
  From
   info a
  Left
  Join
  [
  InfoReply
  ]
   b 
  On
   a.infoID 
  =
   b.infoID 
  Left
  Join
  [
  User
  ]
   c 
  On
  a.infoPubUser 
  =
   c.userNo 
  And
   b.infoReplyUser 
  =
   c.userNo 
  Group
  By
  a.infoPubDate,a.infoID,a.infoTitle,c.userName 
  Order
  By
  Max
  ( a.infoPubDate ) 
  desc 
 

  
 

create table t (name varchar(20))

  
  --
  drop table t
  
 --
  DELETE t
  
  insert  
  into t
  
  select  
  '
  1
  '
  
  union 
  all
  
  select  
  '
  2
  '
  
  union 
  all
  
  select  
  '
  3
  '
  
  union 
  all
  
  select  
  '
  5
  '
  
  union 
  all
  
  select  
  '
  5
  '
  
  union 
  all
  
  select  
  '
  5
  '
  
  union 
  all
  
  select  
  '
  6
  '
  
  union 
  all
  
  select  
  '
  3
  '
  
  union 
  all
  
  select  
  '
  4
  '
  
  /*
    找出相同的 

 3
 5
 5
 5
 3

  */
  
  --
  1.   in
  
  select  
  *
  
  from    t
  
  where   NAME 
  in (
  select name
  
  from   t
  
  group 
  by name
  
  having 
  count(name)
  >
  1)
  
  --
  2.  join
  
  select  t.
  *
  
  from    t
  
  join    (
  select name
  
  from   t
  
  group 
  by name
  
  having 
  count(name)
  >
  1
  
         ) a 
  on T.NAME
  =a.name
  
  --
  3 .  EXISTS
  
  select  
  *
  
  from    t
  
  where   
  exists ( 
  select 
  *
  
  from   (
  select name
  
  from   t
  
  group 
  by name
  
  having 
  count(name)
  >
  1
  
                         ) a
  
  where  a.NAME
  =t.name )
  
  --
   4.  2005  ROW_NUMBER()
  
  select  t.
  *
  
  from    t
  
  join    (
  select 
  *
  
  from   (
  select row_number() 
  over (partition 
  by NAME 
  order 
  by name) 
  as id,
  
                         name
  
  from   t
  
                 ) a
  
  where  id
  =
  2
  
         ) b 
  on t.NAME
  =b.name

  
 
--a b 字段phone
 --A表大 B表小 a和b可能有重复数据
 --随机删除a表n条数据 重复的保留
DELETE  A
 
FROM    A
 
JOIN ( 
 SELECTTOP ( 
 6000 )
 
*
FROM     ( 
 SELECT    ROW_NUMBER() 
 OVER ( 
 ORDERBY u2.phone ) id ,
 
                                     u2.phone
 
FROM      ( 
 SELECT*
FROM      ( 
 SELECT    phone
 
FROM      A
 
EXCEPT
SELECT    phone
 
FROM      B
 
                                                 ) u1
 
                                     ) u2
 
                         ) result1
 
WHERE    id 
 >30--30条之后的随机删除
ORDERBYNEWID()  
 --随机

              ) result3 
 ON A.phone 
 = result3.phone
 



BULKINSERT A
 
FROM'C:\Users\zengzhan\Desktop\a.txt'
WITH (
 
     FIELDTERMINATOR 
 ='|',
 
     ROWTERMINATOR 
 ='\n'
 )
 
--2w

BULKINSERT B
 
FROM'C:\Users\zengzhan\Desktop\B.txt'
WITH (
 
     FIELDTERMINATOR 
 ='|',
 
     ROWTERMINATOR 
 ='\n'
 )
 
--927
  
 

  
 --
 号码相同 内容不同

 select
  a.
 *
  
 from
  hebin4 a 
 where
  
 exists
 (
 select
  
 1
  
 from
  hebin4 b 
 where
  Phone
 =
 a.Phone 
 and
  msg 
 <>
 a.msg)  
 
 

 
         --大数据找差集
        
         select 
         t.ip 
         into 
         iptemp 
         from 
         ( 
        
         SELECT 
         b.ip 
         FROM 
         tb1  a  
         JOIN  
         tb2 b 
        
         on   
         b.num  
         between 
         a.startnum 
         and   
         a.endnum 
        
         ) t
        
         SELECT  
         ip 
         FROM 
         tb2 
         except 
        
         SELECT  
         ip 
         FROM 
         iptemp

清理日志：

  
 
 
  use
   CTC315

  select
   
  *
   
  from
   sysfiles

   
  dump
   
  transaction
   CTC315 
  with
   no_log  
   
  DBCC
   SHRINKFILE (
  '
  CTC315_Log
  '
  )


  set
   ANSI_NULLS 
  ON
  

  set
   QUOTED_IDENTIFIER 
  ON
  

  go
  

  create
   
  procedure
   
  [
  dbo
  ]
  .
  [
  p清除日志
  ]
  

  @databasename
   
  varchar
  (
  100
  ) 
  --
   数据库名称
  

  as
  

  begin
  
  
  declare
   
  @execsql
   
  nvarchar
  (
  max
  ), 
  --
    执行语句
  

    
  @logfilename
   
  varchar
  (
  100
  ) 
  --
   日志逻辑文件
  

   
  select
   
  @execsql
  =
  '
  select @filename=name from  
  '
  +
  @databasename
  
   
  +
  '
  .dbo.[sysfiles]  where fileid=2
  '
   
  --
    查询文件名
  
 --
   select 1,@execsql,@logfilename 
  

   
  exec
   sp_executesql 
  @execsql
  ,N
  '
  @filename varchar(100) output 
  '
  ,
  @logfilename
   output 

  --
   select 1,@logfilename
  

   
  select
   
  @execsql
  =
  '
  use 
  '
  +
  @databasename
  
   
  +
  '
     dump transaction 
  '
  +
  @databasename
  +
  '
    with no_log  
  '
  
   
  +
  '
    DBCC SHRINKFILE (
  '
  +
  @logfilename
  +
  '
  ) 
  '
    
  --
   清除脚本
  
 --
   select @execsql
  

   
  exec
   sp_executesql 
  @execsql
   
  --
   清除
  

   

  end
  

  /*
  
 exec [p清除日志] 'master'

  */ 
 

  
 

  
 
 
  SQL 
  7.0
  /
  2000
  :

  BACKUP
   
  TRANSACTION
   DBName 
  WITH
   TRUNCATE_ONLY 
  DBCC
   SHRINKFILE(
  2
  ,
  200
  )

 SQL 
  2005
  :
 在 SQL 
  2005
   中備份 
  Transaction
   
  Log
   語法改為 
  Backup
   
  LOG
  

  BACKUP
   
  LOG
   DBName 
  WITH
   NO_LOG

  DBCC
   SHRINKFILE(
  2
  ,
  200
  )

 SQL 
  2008
  :
 必須先將復原模式改為 "簡單" 才能清除，完成之後再將模式改回 "完整"

  USE
   DBName

  Alter
   
  Database
   DBName 
  Set
   Recovery Simple 
  DBCC
   SHRINKFILE(
  2
  ,
  100
  ) 
  Alter
   
  Database
   DBName 
  Set
   Recovery 
  Full

  
 SQL 
  2008
   R2:

  USE
   DBName;

  GO
  

  --
   Truncate the log by changing the database recovery model to SIMPLE.
  

  ALTER
   
  DATABASE
   DBName

  SET
   RECOVERY SIMPLE;

  GO
  

  --
   Shrink the truncated log file to 10 MB.
  

  DBCC
   SHRINKFILE (
  2
  , 
  10
  );

  GO
  

  --
   Reset the database recovery model.
  

  ALTER
   
  DATABASE
   DBName

  SET
   RECOVERY 
  FULL
  ;

  GO
  

  *
  上述 
  DBCC
    SHRINKFILE (
  2
  , 
  10
  )，2指的是LOG 
  File
  , 10指的是壓到10MB哦! 
 

  
 

一个表中的Id有多个记录，把所有这个id的记录查出来，并显示共有多少条记录数。
------------------------------------------
select id, Count（*) from tb group by id having count(*)>1

 
 --
 删除上万条记录 不要用 not in

 delete
  
 from
  acct_item a 
 where
  
 not
  
 exists
  (
 select
  
 1
  
 from
  subs b 
 where
  a.subs_id
 =
 b.subs_id ) 
 
 

--两条记录完全相同，如何删除其中一条
set rowcount=1
delete from thetablename where id=@duplicate_id--@duplicate_id为重复值的id

--模糊查询
select * from product where detail like '%123.jpg%'
--替换字段里面部分内容
update product set detail=replace(cast(detail as varchar(8000)),'abc.jpg','efg.jpg')

--SQL 替换掉 括号部分内容
select reverse(stuff(reverse(ServiceShopName), 1, charindex('(', reverse(ServiceShopName)), '')) from dbo.[签名] 

update dbo.签名 set ServiceShopName=reverse(stuff(reverse(ServiceShopName), 1, charindex('(', reverse(ServiceShopName)), ''))

select distinct('【'+ServiceShopName+'】') from dbo.签名

  
 
 
  --
  查询逻辑顺序
   (
  8
  ) 
  SELECT
   (
  9
  ) 
  DISTINCT
   (
  11
  ) 
  <
  TOP_specification
  >
  <
  select_list
  >
   (
  1
  ) 
  FROM
  <
  left_table
  >
   (
  3
  ) 
  <
  join_type
  >
  JOIN
  <
  right_table
  >
   (
  2
  )
  ON
  <
  join_condition
  >
   (
  4
  ) 
  WHERE
  <
  where_condition
  >
   (
  5
  ) 
  GROUP
  BY
  <
  group_by_list
  >
  (
  6
  ) 
  WITH
   {CUBE 
  |
   ROLLUP} (
  7
  ) 
  HAVING
  <
  having_condition
  >
   (
  10
  )
  ORDER
  BY
  <
  order_by_list
  > 
 

  
 

  
 
 
  (
  5
  ) 
  SELECT
   (
  5
  -
  2
  ) 
  DISTINCT
   (
  5
  -
  3
  ) 
  TOP
  (
  <
  top_specification
  >
  ) (
  5
  -
  1
  ) 
  <
  select_list
  >
  
 (
  1
  ) 
  FROM
   (
  1
  -
  J) 
  <
  left_table
  >
   
  <
  join_type
  >
   
  JOIN
   
  <
  right_table
  >
   
  ON
   
  <
  on_predicate
  >
  
        
  |
   (
  1
  -
  A) 
  <
  left_table
  >
   
  <
  apply_type
  >
   APPLY 
  <
  right_table_expression
  >
   
  AS
   
  <
  alias
  >
  
        
  |
   (
  1
  -
  P) 
  <
  left_table
  >
   PIVOT(
  <
  pivot_specification
  >
  ) 
  AS
   
  <
  alias
  >
  
        
  |
   (
  1
  -
  U) 
  <
  left_table
  >
   UNPIVOT(
  <
  unpivot_specification
  >
  ) 
  AS
   
  <
  alias
  >
  
 (
  2
  ) 
  WHERE
   
  <
  where_predicate
  >
  
 (
  3
  ) 
  GROUP
   
  BY
   
  <
  group_by_specification
  >
  
 (
  4
  ) 
  HAVING
   
  <
  having_predicate
  >
  
 (
  6
  ) 
  ORDER
   
  BY
   
  <
  order_by_list
  > 
 

  
 

  
 
--存储过程 批量插入txt
DECLARE@filepathNVARCHAR(
 200)
 
SET@filepath=@path
DECLARE@bulkinsertNVARCHAR(
 2000)
 
SET@bulkinsert= N
 'BULK INSERT #PhoneData FROM '''+@filepath
+ N
 ''' WITH (FIELDTERMINATOR = '''', ROWTERMINATOR = ''\n'')'
CREATETABLE #PhoneData ( phone 
 VARCHAR(
 12) )
 
EXEC sp_executesql 
 @bulkinsert
  
 

-- 取到小數第二位四捨五入
Select Convert (Numeric( 20 , 2 ), IsNull ( 50.01634 , 0 ))
-- 50.02
Select Convert (Numeric( 20 , 2 ), IsNull ( 9 , 0 ))
-- 9.00

--日期转换参数,值得收藏
select CONVERT(varchar, getdate(), 120 )
2004-09-12 11:06:08

select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')
20040912110608

select CONVERT(varchar(12) , getdate(), 111 )
2004/09/12

select CONVERT(varchar(12) , getdate(), 112 )
20040912

select CONVERT(varchar(12) , getdate(), 102 )
2004.09.12

--一个月第一天
SELECT   DATEADD(mm,   DATEDIFF(mm,0,getdate()),   0)  --  2009-06-01 00:00:00.000

--当天
select  * from product  where DateDiff(day,modiDate,GetDate())>1

--如何查询本日、本月、本年的记录SQL
本年：
select * from loanInfo where year(date)=year(getdate())
本月：
select * from loanInfo where year(date)=year(getDate()) And month(date)=month(getdate())
本日：
select * from loanInfo where year(date)=year(getDate()) And month(date)=month(getdate()) and Day(date)=Day(getDate())

昨天的记录：
datediff(day,[Datetime],getdate())=1  把Datetime换为你的相应字段，getdate()-Datetime即为时间差。
本月记录：
SELECT * FROM 表 WHERE datediff(month,[dateadd],getdate())=0
本周记录：
SELECT * FROM 表 WHERE datediff(week,[dateadd],getdate())=0
本日记录：
SELECT * FROM 表 WHERE datediff(day,[dateadd],getdate())=0

本周的星期一   SELECT  DATEADD(wk,  DATEDIFF(wk,0,getdate()),  0)

一年的第一天  SELECT  DATEADD(yy,  DATEDIFF(yy,0,getdate()),  0)

季度的第一天   SELECT  DATEADD(qq,  DATEDIFF(qq,0,getdate()),  0)

当天的半夜   SELECT  DATEADD(dd,  DATEDIFF(dd,0,getdate()),  0)

上个月的最后一天

       这是一个计算上个月最后一天的例子。它通过从一个月的最后一天这个例子上减去毫秒来获得。有一点要记住，在Sql  Server中时间是精确到毫秒。这就是为什么我需要减去毫秒来获得我要的日期和时间。

       SELECT  dateadd(ms,-3,DATEADD(mm,  DATEDIFF(mm,0,getdate()),  0))

       计算出来的日期的时间部分包含了一个Sql  Server可以记录的一天的最后时刻(“:59:59:997”)的时间。

去年的最后一天

       连接上面的例子，为了要得到去年的最后一天，你需要在今年的第一天上减去毫秒。

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

本月的最后一天

       现在，为了获得本月的最后一天，我需要稍微修改一下获得上个月的最后一天的语句。修改需要给用DATEDIFF比较当前日期和“-01-01”返回的时间间隔上加。通过加个月，我计算出下个月的第一天，然后减去毫秒，这样就计算出了这个月的最后一天。这是计算本月最后一天的SQL脚本。

       SELECT  dateadd(ms,-3,DATEADD(mm,  DATEDIFF(m,0,getdate())+1,  0))

本年的最后一天

       你现在应该掌握这个的做法，这是计算本年最后一天脚本

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

本月的第一个星期一

       好了，现在是最后一个例子。这里我要计算这个月的第一个星期一。这是计算的脚本。

         select  DATEADD(wk,  DATEDIFF(wk,0,
                               dateadd(dd,6-datepart(day,getdate()),getdate())
                                                                                                 ),  0)

       在这个例子里，我使用了“本周的星期一”的脚本，并作了一点点修改。修改的部分是把原来脚本中“getdate()”部分替换成计算本月的第天，在计算中用本月的第天来替换当前日期使得计算可以获得这个月的第一个星期一。

--删除一个月前,三个月前, 6个月前,一年前的数据

DELETE FROM 表名WHERE datediff(MM, AddTime,GETDATE()) > 1
DELETE FROM 表名WHERE datediff(MM, AddTime,GETDATE()) > 3
DELETE FROM 表名WHERE datediff(MM, AddTime,GETDATE()) > 6
DELETE FROM 表名WHERE datediff(YY, AddTime,GETDATE()) > 1

---------------------------------------------------------------
附录，其他日期处理方法

1)去掉时分秒
declare  @  datetime
set  @  =  getdate()  --'2003-7-1  10:00:00'
SELECT  @,DATEADD(day,  DATEDIFF(day,0,@),  0)

2）显示星期几
select  datename(weekday,getdate())

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

另外，取得本月天数
select    datediff(day,cast(month(GetDate())  as  varchar)+'-'+cast(month(GetDate())  as  varchar)+'-15'  ,cast(month(GetDate())  as  varchar)+'-'+cast(month(GetDate())+1    as  varchar)+'-15')

任意月份的最大天数
select  day(dateadd(dd,-1,dateadd(mm,1,Dateadd(mm,datediff(mm,0,getdate()),0))))

或者使用计算本月的最后一天的脚本，然后用DAY函数区最后一天
SELECT  Day(dateadd(ms,-3,DATEADD(mm,  DATEDIFF(m,0,getdate())+1,  0)))

4）判断是否闰年：

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

5）一个季度多少天
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))

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

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

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

调用示例：

select dbo.DaysInMonth ('2006-02-03')

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

SELECT DATEADD(week, DATEDIFF(week,'1900-01-01',getdate()), '1900-01-01')  --返回-11-06 00:00:00.000
或
SELECT DATEADD(week, DATEDIFF(week,0,getdate()),0)

(3)当前季度的第一天

SELECT DATEADD(quarter, DATEDIFF(quarter,0,getdate()), 0)—返回-10-01 00:00:00.000

(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)) —返回

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

2.分页SQL语句
select * from(select (row_number() OVER (ORDER BY tab.ID Desc)) as rownum,tab.* from 表名As tab) As t where rownum between 起始位置And 结束位置

8.如何修改数据库的名称:

sp_renamedb 'old_name', 'new_name'

3.获取当前数据库中的所有用户表
select * from sysobjects where xtype='U' and category=0

4.获取某一个表的所有字段
select name from syscolumns where id=object_id('表名')

5.查看与某一个表相关的视图、存储过程、函数
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'

6.查看当前数据库中所有存储过程
select name as 存储过程名称from sysobjects where xtype='P'

7.查询用户创建的所有数据库
select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')
或者
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01

8.查询某一个表的字段和数据类型
select column_name,data_type from information_schema.columns where table_name = '表名'

9.使用事务
在使用一些对数据库表的临时的SQL语句操作时，可以采用SQL SERVER事务处理，防止对数据操作后发现误操作问题

开始事务
Begin tran
Insert Into TableName Values(…)
SQL语句操作不正常，则回滚事务。

回滚事务
Rollback tran
SQL语句操作正常，则提交事务，数据提交至数据库。

提交事务
Commit tran

计算执行SQL语句查询时间

declare @d datetime
set @d=getdate()
select * from SYS_ColumnProperties select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

set statistics io on

set statistics time on

  
 
 
  ALTER
  proc
  [
  dbo
  ]
  .
  [
  seequerytime
  ]
  @sql
  varchar
  (
  max
  ) 
  as
  set
   nocount
  on
  declare
  @d
  datetime
  set
  @d
  =
  getdate
  () 
  /*
  你的SQL脚本开始
  */
  exec
   (
  @sql
  ) 
  /*
  你的SQL脚本结束
  */
  declare
  @result
  int
  select
  @result
  =
  datediff
  (ms, 
  @d
  , 
  getdate
  ())
  select
  @result
  print
  @result
   调用 
  exec
  [
  seequerytime
  ]
  '
  select * from dbo.费用表 where 生成日期=
  ''
  2008-12-09 00:00:00.000
  ''' 
 

  
 

【关闭SQL Server 数据库所有使用连接】

use  master
go
create  proc  KillSpByDbName(@dbname  varchar(20))
as
begin
declare  @sql  nvarchar(500),@temp varchar(1000)
declare  @spid  int
set  @sql='declare  getspid  cursor  for
select  spid  from  sysprocesses  where  dbid=db_id('''+@dbname+''')'
exec  (@sql)
open  getspid
fetch  next  from  getspid  into  @spid
while  @@fetch_status <>-1
begin
  set @temp='kill  '+rtrim(@spid)
  exec(@temp)
fetch  next  from  getspid  into  @spid
end
close  getspid
deallocate  getspid
end

--举例使用，关闭数据库下的所有连接操作
Use  master
Exec  KillSpByDbName  '数据库名称'

（一）挂起操作
在安装Sql或sp补丁的时候系统提示之前有挂起的安装操作，要求重启，这里往往重启无用，解决办法：
到HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSession Manager
删除PendingFileRenameOperations

（二）收缩数据库
--重建索引
DBCC REINDEX
DBCC INDEXDEFRAG
--收缩数据和日志
DBCC SHRINKDB
DBCC SHRINKFILE

（三）压缩数据库
dbcc shrinkdatabase(dbname)

（四）转移数据库给新用户以已存在用户权限
exec sp_change_users_login update_one,newname,oldname
go

（五）检查备份集
RESTORE VERIFYONLY from disk=Evbbs.bak

（六）修复数据库
ALTER DATABASE [dvbbs] SET SINGLE_USER
GO
DBCC CHECKDB(dvbbs,repair_allow_data_loss) WITH TABLOCK
GO
ALTER DATABASE [dvbbs] SET MULTI_USER
GO

  
 
 
  --
  过滤关键词
  

  declare 
  @sql 
  varchar(
  max)
  

  set 
  @sql 
  = 
  
     
  '
   select  Id, UserId, SendId, Phone, Message, 
        SentTime, CreateTime, BatchId,null Result1,CompanyID  from #tb 
  where 1=1 
  '                                
  

  select 
  @sql 
  = 
  @sql 
  + 
  '
    and  [Message]  not    like  
  '       
  + 
  '''' 
  + 
  '
  %
  ' 
  + Name 
  + 
  '
  %
  ' 
  + 
  '''' 
  from  ShieldWord 
  

  where  
  1 
  = 
  1 
  
  
  --
  exec (@sql) 
 

  
 

  
 
 
   
  --
  地址 加 市（没有就加，有则不改） 
  

  update Business_Login
  

  set
  

  [
  Address
  ] 
  =
  CASE 
  WHEN 
  [
  Address
  ]  
  LIKE 
  '
  广州%
  ' 
  THEN 
  [
  Address
  ]  
  ELSE  
  '
  广州市
  '
  + 
  [
  Address
  ] 
  ENd 
  

  where CityID
  =
  190 
  

  

  

  --
  跨数据库更新表
  

  update 
  
 Business_Login 
  

  set
  
 Business_Login.
  [
  Address
  ] 
  = A.
  [
  Address
  ] 
  FROM  Yht.dbo.Business_Login A
  

  where Business_Login.BusinessId
  = A.BusinessId
  

  and Business_Login.CityID
  =
  190
  

  and Business_Login.CityID
  =A.CityID
 
 
 
    SELECT a.BusinessId,
    
        a.CityID,
    
        
    CASE 
    
             
    WHEN 
    CHARINDEX(b.CityName, a.
    [
    Address
    ]) 
    = 
    0 
    THEN b.CityName 
    + a.
    [
    Address
    ]
    
             
    ELSE a.
    [
    Address
    ]
    
        
    END 
    AS ADDRESS
    

    FROM   Business_Login a
    
        
    LEFT 
    JOIN T_City b
    
             
    ON  b.CityID 
    = a.CityID
    

    --
    批量更新
    

    update  a 
    

    set
    
 a.
    [
    Address
    ] 
    =
    CASE 
    WHEN 
    CHARINDEX(b.CityName, a.
    [
    Address
    ]) 
    = 
    0 
    THEN b.CityName 
    + a.
    [
    Address
    ] 
    ELSE a.
    [
    Address
    ] 
    END
    

    FROM Business_Login a, T_City b
    
           
    where  b.CityID 
    = a.CityID
    

    

    

    --
    Tag
    

    update Business_Login
    

    set
    
     Tag 
    = A.CategoryName  
    from B_Category A 
   
where TypeId2
 =A.CategoryId  
  

  
 

  
 
 
  --
  限制某ip一个小时内不能频繁操作
  

  SELECT 
  COUNT(
  1) 
  AS num 
  FROM PhoneVcode  
  

  where 
  datediff( s, CreateTime,
  getdate())
  >=
  1 
  AND 
  datediff( s, CreateTime,
  getdate())
  <=
  3600 
 
and Ip in (SELECT   Ip FROM PhoneVcode WHERE Ip='127.0.0.1' GROUP BY Ip   having count(Ip)>2)  

  
 

select top m * from tablename where id not in (select top n id from tablename)

select top m * into 临时表(或表变量) from tablename order by columnname -- 将top m笔插入
set rowcount n
select * from 表变量order by columnname desc

select top n * from
(select top m * from tablename order by columnname) a
order by columnname desc

-- Trim not removing spaces 不能去掉空白
-- 10- 换行, 13 - 回车, 32 - 空格 ,160 - 空格 
SELECT
LTRIM ( RTRIM ( REPLACE ( REPLACE ( REPLACE ( [ YourColumn ] , CHAR ( 10 ), CHAR ( 32 )),
CHAR ( 13 ), CHAR ( 32 )), CHAR ( 160 ),
CHAR ( 32 )))) AS [ YourColumn ]
FROM [ YourTable ]

复制表(只复制结构,源表名：a 新表名：b) (Access可用)
法一：select * into b from a where 1 <>1
法二：select top 0 * into b from a

select * into SendBatch_DianXin from SendBatch where 1=0

拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)
insert into b(a, b, c) select d,e,f from b;

跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’where 条件
例子：..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..

子查询(表名：a 表名：b)
select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)

显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b

外连接查询(表名：a 表名：b)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

在线视图查询(表名：a )
select * from (SELECT a,b,c FROM a) T where t.a > 1;

between的用法,between限制查询数据范围时包括了边界值,not between不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 数值and 数值

in 的使用方法
select * from table1 where a [not] in (‘值’,’值’,’值’,’值’)

两张关联表，删除主表中已经在副表中没有的信息
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )

四表联查问题：
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where ..

日程安排提前五分钟提醒
SQL: select * from 日程安排where datediff('minute',f开始时间,getdate())>5

一条sql 语句搞定数据库分页
select top 10 b.* from (select top 20 主键字段,排序字段from 表名order by 排序字段desc) a,表名b where b.主键字段= a.主键字段order by a.排序字段

选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)

包括所有在TableA 中但不在TableB和TableC 中的行并消除所有重复行而派生出一个结果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)

随机取出条数据
select top 10 * from tablename order by newid()

随机选择记录
select newid()

select distinct provincename , newid () from City order by newid (),provincename

  
 
 --随机更新 n条
UPDATE TOP(10) SEND
SET    Result3 = 0
FROM   SEND b
        JOIN (
                 SELECT TOP 10 * 
                 FROM   SEND
                 WHERE  Result3 IS NULL
                        AND BatchID = '20101004093814'
                 ORDER BY
                        NEWID()
             ) a
             ON  a.sendid = b.sendid   
  
 

  
 
--随机提取10个号码 获取城市
CREATEprocedure[dbo].
 [Applet_RandomTop10City](  
 
@phonesnvarchar(
 max)   
 
 )
 
as
begin
declare@strphonenvarchar(
 max)
 
set@strphone=@phones
begin
set arithabort 
 on
SELECT  B.phone ,
 
         ( 
 SELECT    provincename 
 +'-'+ areaname  
 
FROM      mobilearea
 
WHERE     listid 
 =LEFT(B.phone, 
 7)
 
         ) 
 AS city
 
FROM    ( 
 SELECT[phone]=CONVERT(XML, 
 '<root><v>'+REPLACE(
 [phone], 
 ',',
 
'</v><v>')
 
+'</v></root>')
 
FROM      ( 
 SELECT''+@strphone+''AS[phone]
                     ) t
 
         ) A
 
OUTER APPLY ( 
 SELECT    phone 
 = N.v.value(
 '.', 
 'varchar(100)')
 
FROM      A.
 [phone].nodes(
 '/root/v') N ( v )
 
                     ) B
 
end
end
  
 

   declare @x xml
    select @x=cast('<A>'+replace(@Ids,',','</A><A>')+'</A>' as xml)     
    
    begin
    --插入
        INSERT dbo.tb
                select 111,t.value('.','int'),getdate()
                    from @x.nodes('/A') as x (t)

  
 
Sql Server随机抽取数据效率优化
Declare@dDatetime
Set@d=getdate()
 
SELECTTop1*FROM[TGBus_Card].
 [dbo].
 [Ka_Card]
WHERE0.01>=CAST(CHECKSUM(
 NEWID(), CardNo) 
 &0x7fffffffASfloat) 
 /CAST (
 0x7fffffffASint)
 
And ActivityID 
 =501And State 
 =0
Select[语句执行花费时间(毫秒)]=Datediff(ms,
 @d,
 Getdate())
 

--200w数据 就几毫秒 重复可能多 io消耗大  下面更加高效快速
  
 

  
 
--top 5 
SETSTATISTICS IO 
 ON
SETSTATISTICS TIME 
 ON
SELECT*FROM news 
 WHERE DocId
 =CEILING (
 rand()
 * ( 
 SELECTmax(DocId)  
 FROM news  )
 -1) 
 UNIONall
SELECT*FROM news 
 WHERE DocId
 =CEILING (
 rand()
 * ( 
 SELECTmax(DocId)  
 FROM news  )
 -1) 
 UNIONall
SELECT*FROM news 
 WHERE DocId
 =CEILING (
 rand()
 * ( 
 SELECTmax(DocId)  
 FROM news  )
 -1) 
 UNIONall
SELECT*FROM news 
 WHERE DocId
 =CEILING (
 rand()
 * ( 
 SELECTmax(DocId)  
 FROM news  )
 -1) 
 UNIONall
  SELECT*FROM news 
 WHERE DocId
 =CEILING (
 rand()
 * ( 
 SELECTmax(DocId)  
 FROM news  )
 -1) 
 

  
 

 
 --
 效率提高几百倍 （号码前n位相同视为连续 数目小于3的） 
 

 select a.
 * 
 from 提取不连续号码 a
 

 join (
 select 
 left(phone,
 7) 
 as p 
 from 提取不连续号码 
 group 
 by 
 left(phone,
 7) 
 having 
 count(
 1)
 <
 3) b
 

 on 
 left
 (a.phone,
 7
 )
 =
 b.p 

  
 sql to c# linq
var q = list.GroupBy(x => x.Substring(
 0, m))
 
     .Select(x => 
 new { count = x.Count(), name = x.Key })
 
     .Where(x => x.count <= n)
 
     .ToList();
 
var query = 
 from c 
 in list
 
             join p 
 in q on c.Substring(
 0, m) equals p.name 
 
        select c;

  
 
 
  --
  前后比较 
 

with t 
 as
 (
 select row_number() 
 over(
 orderbygetdate()) rn,
 
left( phone,
 5) p,phone 
 from tb
 
 )
 
select a.rn,a.phone
 
from t a
 
leftjoin t b 
 on a.rn
 =b.rn
 +1
leftjoin t c 
 on a.rn
 =c.rn
 -1
wherecast(a.p 
 asint)
 -cast(b.p 
 asint)
 <>0
andcast(a.p asint)-cast(c.p asint)<>0
  
 

  
 
 sql to c# linq
var q = list
 
     .Select((u, index) => 
 new { phone = u,p=u.Substring(
 0,m), num = index+
 1})
 
     .ToList();
 
var query = 
 from b 
 in q
 
             join a 
 in q on b.num equals a.num + 
 1
             join c 
 in q on b.num equals c.num - 
 1
where b.p.CompareTo( a.p)!=
 0  && b.p.CompareTo(c.p)!=
 0
selectnew
             { b.phone
 
 };
  
 

删除重复记录
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,)

select distinct * into #Tmp from TB
drop table TB
select * into TB from #Tmp
drop table #Tmp

   
  
----每次按需要取14 电话号码跟消息相同的 每次取只取2条 先进先出原则
SELECT  b.
  *
INTO    #tb
  
FROM    ( 
  SELECTTOP15
*
FROM      ( 
  SELECT    Id, UserId, SendId, Phone, Message, SendTime,
  
                                 CreateTime, BatchId,
  
                                 ROW_NUMBER() 
  OVER ( PARTITION  
  BY[Message]ORDERBY[Message], Phone, CreateTime )
  
AS keyid
  
FROM      dbo.Send_Api
  
                     ) a
  
WHERE     a.keyid 
  <3
         ) b 
  
   
  

在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢?
1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断
select   *   from   people
where   peopleId   in   (select     peopleId     from     people     group     by     peopleId     having     count(peopleId)   >   1)

2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录
delete   from   people
where   peopleId     in   (select     peopleId     from   people     group     by     peopleId       having     count(peopleId)   >   1)
and   rowid   not   in   (select   min(rowid)   from     people     group   by   peopleId     having   count(peopleId   )>1)

3、查找表中多余的重复记录（多个字段）
select   *   from   vitae   a
where   (a.peopleId,a.seq)   in     (select   peopleId,seq   from   vitae   group   by   peopleId,seq     having   count(*)   >   1)

4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录
delete   from   vitae   a
where   (a.peopleId,a.seq)   in     (select   peopleId,seq   from   vitae   group   by   peopleId,seq   having   count(*)   >   1)
and   rowid   not   in   (select   min(rowid)   from   vitae   group   by   peopleId,seq   having   count(*)>1)

5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录
select   *   from   vitae   a
where   (a.peopleId,a.seq)   in     (select   peopleId,seq   from   vitae   group   by   peopleId,seq   having   count(*)   >   1)
and   rowid   not   in   (select   min(rowid)   from   vitae   group   by   peopleId,seq   having   count(*)>1)

经典尝试删除重复值

declare @table table (id int,name nvarchar(10))
insert into @table select 1,'aa'
union all select 1,'aa'
union all select 2,'bb'
union all select 3,'bb'
union all select 4,'cc'
union all select 1,'aa'
union all select 4,'cc'

delete a
from (
select id,name,rn = row_number() over (partition by id,name order by id) from @table
) a where rn > 1

select * from @table

id name
----------- ----------
1 aa
2 bb
3 bb
4 cc

(4 row(s) affected)

  
  --
  去重复 重复超过3个以上的保留前3个 不保留重复的顺序
  
  SELECT 
  *  
  
  FROM  
  
 (  
  
  SELECT rid
  =ROW_NUMBER() 
  OVER(PARTITION 
  BY phone 
  ORDER 
  BY id 
  desc),
  *  
  
  FROM 去重复保留前三  
  
 ) 
  AS T  
  
  WHERE rid
  <=
  3
  
  ORDER 
  BY id

  
 
--保留重复的顺序
SELECT*FROM (  
 
SELECTTOP100PERCENT r
 =ROW_NUMBER() 
 OVER( PARTITION 
 BY T.phone 
 ORDERBY t.rid ),T.
 *
FROM
 (  
 SELECT rid
 =ROW_NUMBER() 
 OVER(
 ORDERBYGETDATE() ),phone 
 
FROM 去重复保留前三
 

 ) 
 AS T 
 ORDERBY T.rid 
 
 ) tb
 
WHERE tb.r
 <=3
ORDERBY tb.rid 
 
  
 

  
  //
   去重复 顺序不变 保留n个
  
  if (n == 
  1)
  
 {
  
     IEnumerable<
  string> p = phones.Distinct();
  
     mobile = 
  string.Join(
  "
  ,
  ", p);
  
 }
  
  if (n > 
  1)
  
 {
  
  var q = phones.Select((u, index) => 
  new { phone = u, id = index + 
  1 }).ToList();
  
  var s = q.OrderBy(x => x.id).GroupBy(x => x.phone)
  
         .Select(g => 
  new { g, count = g.Count() })
  
         .SelectMany(t => t.g.Select((j, i) => 
  new { j.phone, j.id, rn = i + 
  1 }));
  
  var o = s.OrderBy(t => t.id).Select(t => t);
  
   List<
  string> list=
  new List<
  string>();
  
  foreach (
  var item 
  in o)
  
     {
  
  if (item.rn <= n)
  
         {
  
             list.Add(item.phone);
  
         }
  
     }
  
     mobile = 
  string.Join(
  "
  ,
  ", list);
  
 }

--float字段保留一位小数，四舍五入

SELECT CONVERT(DECIMAL(18,1),1024.791454) ------- 1024.8 （所影响的行数为1 行）
<%# Eval("字段")==null?"":Eval("字段").toString("0.0") %>

--事务(进程 ID 77)与另一个进程被死锁在 锁 | 通信缓冲区 资源上，并且已被选作死锁牺牲品。请重新运行该事务。

--优先选择3，2，1

--1.表后面加
with (nolock)
--2.会话前面加 update的时候，能select
SET TRANSACTION ISOLATION LEVEL Read uncommitted 
--3.整个数据库隔离级别改为行版本
ALTER DATABASE SMMM SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE SMMM SET read_committed_snapshot ON --行版本隔离
ALTER DATABASE SMMM set MULTI_USER

select object_name(id) as 表名,
       rows as 使用行数,
       rtrim(8*reserved/1024)+'Mb' as 分配空间,
       rtrim(8*dpages/1024) as 使用空间,
       rtrim(8*(reserved-dpages)/1024) as 未使用空间,
       rtrim(8*dpages/1024-rows/1024*minlen/1024) as 空闲空间 
from sysindexes 
order by rows desc

本文转自曾祥展博客园博客，原文链接：http://www.cnblogs.com/zengxiangzhan/archive/2009/09/12/1565320.html，如需转载请自行联系原作者

SQL经典短小代码收集

热门文章

最新文章

相关课程

相关电子书

相关实验场景