SQL经典短小代码收集

简介:

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

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

--N到M条记录(要有主索引ID)
Select Top M-* 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 from test where id in(select top from test orderby id descorderby 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 (  SELECT  TOP (  6000 )
                         *
                FROM     (  SELECT    ROW_NUMBER()  OVER (  ORDER  BY u2.phone ) id ,
                                    u2.phone
                           FROM      (  SELECT     *
                                       FROM      (  SELECT    phone
                                                   FROM      A
                                                   EXCEPT
                                                   SELECT    phone
                                                   FROM      B
                                                ) u1
                                    ) u2
                        ) result1
                WHERE    id  >  30   -- 30条之后的随机删除
                ORDER  BY  NEWID()   -- 随机
               
             ) result3  ON A.phone  = result3.phone



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

BULK  INSERT 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  @filepath  NVARCHAR( 200)
  SET  @filepath  =  @path
  DECLARE  @bulkinsert  NVARCHAR( 2000)
  SET  @bulkinsert  = N ' BULK INSERT #PhoneData FROM  '''  +  @filepath
      + N '''  WITH (FIELDTERMINATOR =  '''' , ROWTERMINATOR =  '' \n '' ) '
  CREATE  TABLE #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(varchargetdate(), 120 )
2004-09-12 11:06:08 

select replace(replace(replace(CONVERT(varchargetdate(), 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( 1AS 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 - 空格&nbsp; 
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个号码 获取城市 
CREATE  procedure  [ dbo ]. [ Applet_RandomTop10City ](  
     @phones         nvarchar( max)   
)
as
begin
     declare  @strphone  nvarchar( 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  @d  Datetime
Set  @d = getdate()
SELECT  Top  1 *  FROM  [ TGBus_Card ]. [ dbo ]. [ Ka_Card ] 
WHERE  0.01  >=  CAST(CHECKSUM( NEWID(), CardNo)  &  0x7fffffff  AS  float/  CAST ( 0x7fffffff  AS  int)
And ActivityID  =  501  And State  =  0
Select  [ 语句执行花费时间(毫秒) ]  =  Datediff(ms, @d, Getdate())

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

 

复制代码
-- top 5 
   SET  STATISTICS IO  ON 
   SET  STATISTICS TIME  ON  
   SELECT    *     FROM news  WHERE DocId = CEILING ( rand() * (  SELECT   max(DocId)   FROM news  ) - 1UNION  all
   SELECT    *     FROM news  WHERE DocId = CEILING ( rand() * (  SELECT   max(DocId)   FROM news  ) - 1UNION  all
   SELECT    *     FROM news  WHERE DocId = CEILING ( rand() * (  SELECT   max(DocId)   FROM news  ) - 1UNION  all
   SELECT    *     FROM news  WHERE DocId = CEILING ( rand() * (  SELECT   max(DocId)   FROM news  ) - 1UNION  all
  SELECT    *     FROM news  WHERE DocId = CEILING ( rand() * (  SELECT   max(DocId)   FROM news  ) - 1
复制代码

   

 

-- 效率提高几百倍 (号码前n位相同视为连续 数目小于3的) 
select a. *  from 提取不连续号码 a
join ( select  left(phone, 7as p  from 提取不连续号码  group  by  left(phone, 7having  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( order  by  getdate()) rn,
left( phone, 5) p,phone  from tb
)
select a.rn,a.phone
from t a
left  join t b  on a.rn =b.rn + 1
left  join t c  on a.rn =c.rn - 1
where  cast(a.p  as  int) - cast(b.p  as  int) <> 0
and  cast (a.p  as  int ) - cast (c.p  as  int ) <> 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
             select  new
            { 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    (  SELECT  TOP  15
                     *
           FROM      (  SELECT    Id, UserId, SendId, Phone, Message, SendTime,
                                CreateTime, BatchId,
                                ROW_NUMBER()  OVER ( PARTITION   BY  [ Message ]  ORDER  BY  [ 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 (  
SELECT  TOP  100  PERCENT r =ROW_NUMBER()  OVER( PARTITION  BY T.phone  ORDER  BY t.rid ),T. *  
FROM  
(   SELECT rid =ROW_NUMBER()  OVER( ORDER  BY  GETDATE() ),phone 
    FROM 去重复保留前三
   
AS T  ORDER  BY T.rid 
) tb
WHERE tb.r <= 3
ORDER  BY 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,如需转载请自行联系原作者

相关实践学习
日志服务之使用Nginx模式采集日志
本文介绍如何通过日志服务控制台创建Nginx模式的Logtail配置快速采集Nginx日志并进行多维度分析。
相关文章
拿php写个原生增删改查案例出来(提供全部代码+sql)
拿php写个原生增删改查案例出来(提供全部代码+sql)
拿php写个原生增删改查案例出来(提供全部代码+sql)
|
4月前
|
SQL Java 流计算
Flink CDC在代码里面集成cdc的时候,是不是也要用上面这个胖包flink-sql-connector-mysql-cdc,不要去用瘦包flink-connector-mysql-cdc? com.ververica flink-sql-connector-mysql-cdc 2.4.0
Flink CDC在代码里面集成cdc的时候,是不是也要用上面这个胖包flink-sql-connector-mysql-cdc,不要去用瘦包flink-connector-mysql-cdc? com.ververica flink-sql-connector-mysql-cdc 2.4.0
51 1
|
2月前
|
SQL 数据库
小技巧:如何让 ABAP OPEN SQL 代码具有自解释性(Self-Explained)
小技巧:如何让 ABAP OPEN SQL 代码具有自解释性(Self-Explained)
23 0
|
29天前
|
SQL XML Java
整理几个常用的sql和其他代码
整理几个常用的sql和其他代码
11 1
|
2月前
|
SQL Java 关系型数据库
Flink SQL 问题之用代码执行报错如何解决
Flink SQL报错通常指在使用Apache Flink的SQL接口执行数据处理任务时遇到的问题;本合集将收集常见的Flink SQL报错情况及其解决方法,帮助用户迅速恢复数据处理流程。
107 6
|
2月前
|
SQL 关系型数据库 MySQL
MySQL日期函数的SQL代码示例和使用场景
MySQL日期函数的SQL代码示例和使用场景
24 0
|
3月前
|
SQL Kubernetes 调度
DataphinV3.14 Flink SQL任务支持基于Session集群调试,模拟生产代码逻辑的调试效果
实时研发一直以来的都是通过local-debug的方式来调试开发中的Flink SQL任务,该方式有如下不足: 1. 支持的采样数据有限,且非是流式数据的调试。 2. 手动上传构造数据的方式较为繁琐,局限性较大。 为便于Flink SQL任务的调试,DataphinV3.14版本支持Flink SQL任务基于Session集群调试,期望做到像离线即席查询般方便地获取实时任务的输出结果,方便用户对线上的真实数据进行代码逻辑上的调试。
|
3月前
|
SQL 存储 监控
公司内部电脑监控软件的数据库管理:SQL代码实践
随着科技的飞速发展,公司内部电脑监控软件在保障信息安全和提高员工生产效率方面发挥着重要作用。本文将深入探讨监控软件的数据库管理,并通过SQL代码实践展示其操作和优化方法。
300 0
|
4月前
|
SQL 数据采集 Java
Java【代码分享 02】商品全部分类数据获取(建表语句+Jar包依赖+树结构封装+获取及解析源代码)包含csv和sql格式数据下载可用
Java【代码分享 02】商品全部分类数据获取(建表语句+Jar包依赖+树结构封装+获取及解析源代码)包含csv和sql格式数据下载可用
41 0
|
4月前
|
Oracle Java 关系型数据库
Generator【SpringBoot集成】代码生成+knife4j接口文档(2种模板设置、逻辑删除、字段填充 含代码粘贴可用)保姆级教程(注意事项+建表SQL+代码生成类封装+测试类)
Generator【SpringBoot集成】代码生成+knife4j接口文档(2种模板设置、逻辑删除、字段填充 含代码粘贴可用)保姆级教程(注意事项+建表SQL+代码生成类封装+测试类)
28 0