SQL语句大全，你需要的SQL在这里

//修改数据库的名称:
sp_renamedb 'old_name', 'new_name'Copy

//法一
select * into b from a where 1<>1（仅用于SQlServer）
//法二：
select top 0 * into b from aCopy

insert into b(a, b, c) select d,e,f from b;Copy

insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
例子：..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..Copy

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)Copy

select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) bCopy

select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.cCopy

select * from (SELECT a,b,c FROM a) T where t.a > 1;Copy

select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 数值1 and 数值2Copy

select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)Copy

delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )Copy

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 .....Copy

SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5Copy

select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段**具体实现：**关于数据库分页：
  declare [@start](https://learnku.com/users/7236) int,@end int
  @sql  nvarchar(600)
  set @sql=’select top’+str(@end-[@start](https://learnku.com/users/7236)+1)+’+from T where rid not in(select top’+str(@str-1)+’Rid from T where Rid>-1)’
  exec sp_executesql @sqlCopy

select top 10 * form table1 where 范围Copy

select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)Copy

(select a from tableA ) except (select a from tableB) except (select a from tableC)Copy

select top 10 * from tablename order by **newid()**Copy

//1
delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
//2
select distinct * into temp from tablename
  delete from tablename
  insert into tablename select * from tempCopy

select name from sysobjects where type='U' // U代表用户Copy

select name from syscolumns where id=object_id('TableName')Copy

select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type

//显示结果：
type vender pcs
**电脑 A 1
电脑 A 1
光盘 B 2
光盘 A 2
手机 B 3
手机 C 3Copy

TRUNCATE TABLE table1Copy

select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id descCopy

--重建索引
DBCC REINDEX
DBCC INDEXDEFRAG
--收缩数据和日志
DBCC SHRINKDB
DBCC SHRINKFILECopy

dbcc shrinkdatabase(dbname)Copy

exec sp_change_users_login 'update_one','newname','oldname'
goCopy

RESTORE VERIFYONLY from disk='E:\dvbbs.bak'Copy

ALTER DATABASE [dvbbs] SET SINGLE_USER
GO
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK
GO
ALTER DATABASE [dvbbs] SET MULTI_USER
GOCopy

SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
 @MaxMinutes INT,
 @NewSize INT
USE tablename -- 要操作的数据库名
SELECT  @LogicalFileName = 'tablename_log', -- 日志文件名
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
 @NewSize = 1  -- 你想设定的日志文件的大小(M)
Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size 
 FROM sysfiles
 WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' + 
 CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + 
 CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
 FROM sysfiles
 WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
 (DummyColumn char (8000) not null)

DECLARE @Counter    INT,
 @StartTime DATETIME,
 @TruncLog   VARCHAR(255)
SELECT @StartTime = GETDATE(),
 @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'

DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
 AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  
 AND (@OriginalSize * 8 /1024) > @NewSize  
 BEGIN -- Outer loop.
SELECT @Counter = 0
 WHILE   ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
 BEGIN -- update
 INSERT DummyTrans VALUES ('Fill Log') DELETE DummyTrans
 SELECT @Counter = @Counter + 1
 END
 EXEC (@TruncLog)  
 END
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
 CONVERT(VARCHAR(30),size) + ' 8K pages or ' + 
 CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
 FROM sysfiles 
 WHERE name = @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFFCopy

exec sp_changeobjectowner 'tablename','dbo'Copy

CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
@OldOwner as NVARCHAR(128),
@NewOwner as NVARCHAR(128)
AS

DECLARE [@Name](https://learnku.com/users/31010)    as NVARCHAR(128)
DECLARE @Owner   as NVARCHAR(128)
DECLARE @OwnerName   as NVARCHAR(128)

DECLARE curObject CURSOR FOR 
select 'Name'    = name,
   'Owner'    = user_name(uid)
from sysobjects
where user_name(uid)=@OldOwner
order by name

OPEN   curObject
FETCH NEXT FROM curObject INTO [@Name](https://learnku.com/users/31010), @Owner
WHILE(@@FETCH_STATUS=0)
BEGIN     
if @Owner=@OldOwner 
begin
   set @OwnerName = @OldOwner + '.' + rtrim([@Name](https://learnku.com/users/31010))
   exec sp_changeobjectowner @OwnerName, @NewOwner
end
-- select @name,@NewOwner,@OldOwner

FETCH NEXT FROM curObject INTO [@Name](https://learnku.com/users/31010), @Owner
END

close curObject
deallocate curObject
GOCopy

Name     score
Zhangshan   80
Lishi       59
Wangwu      50
Songquan    69
while((select min (score) from tb_table)<60)
begin
update tb_table set score=score*1.01
where score<60
if  (select min (score) from tb_table)>60
break
else
continue
endCopy

Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as //从少到多Copy

select encrypt('原始密码')
select pwdencrypt('原始密码')
select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同 encrypt('原始密码')
select pwdencrypt('原始密码')
select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同Copy

EXEC master..xp_fixeddrives**Copy

if (select checksum_agg(binary_checksum(*)) from A)
     =
    (select checksum_agg(binary_checksum(*)) from B)
print '相等'
else
print '不相等'Copy

DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) FROM master.dbo.sysprocesses
WHERE program_name IN('SQL profiler',N'SQL 事件探查器')
EXEC sp_msforeach_worker '?'Copy

//开头到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
//N到结尾记录
Select Top N * From 表 Order by ID DescCopy

 select top 10 recid from A where recid not  in(select top 30 recid from A)Copy

select Name from sysobjects where xtype='u' and status>=0Copy

select name from syscolumns where id=object_id('表名')
select name from syscolumns where id in (select id from sysobjects where type = 'u' and name = '表名')
//两种方式的效果相同Copy

select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'Copy

select name as 存储过程名称 from sysobjects where xtype='P'Copy

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 <> 0x01Copy

select column_name,data_type from information_schema.columns
where table_name = '表名'Copy

//链接服务器
exec sp_addlinkedserver   'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '
exec sp_addlinkedsrvlogin  'ITSV ', 'false ',null, '用户名 ', '密码 '
--查询示例

select * from ITSV.数据库名.dbo.表名

--导入示例

select * into 表 from ITSV.数据库名.dbo.表名

**--****以后不再使用时删除链接服务器**

exec sp_dropserver  'ITSV ', 'droplogins '

**--****连接远程/局域网数据**(openrowset/openquery/opendatasource)

--1、openrowset

--查询示例

select * from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)

--生成本地表

select * into 表 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)

--把本地表导入远程表

insert openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)

select *from 本地表

--更新本地表

update b

set b.列A=a.列A

 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地表 b

on a.column1=b.column1

--openquery用法需要创建一个连接

--首先创建一个连接创建链接服务器

exec sp_addlinkedserver   'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '

--查询

select *

FROM openquery(ITSV,  'SELECT *  FROM 数据库.dbo.表名 ')

--把本地表导入远程表

insert openquery(ITSV,  'SELECT *  FROM 数据库.dbo.表名 ')

select * from 本地表

--更新本地表

update b

set b.列B=a.列B

FROM openquery(ITSV,  'SELECT * FROM 数据库.dbo.表名 ') as a 

inner join 本地表 b on a.列A=b.列A

--3、opendatasource/openrowset

SELECT   *

FROM   opendatasource( 'SQLOLEDB ',  'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ' ).test.dbo.roy_ta

--把本地表导入远程表

insert opendatasource( 'SQLOLEDB ',  'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ').数据库.dbo.表名

select * from 本地表 Copy