来源于：http://www.cnblogs.com/stswordman/archive/2007/03/15/675518.html

SqlServer语句收集

1 临时表

?

--创建临时表 数据库重启后表#a1会消失
create table #a1
(
col1 int,
col2 int
)
select * from #a1

--创建临时表 改表只存在与当前的session

create table ##a2
(
col1 int,
col2 int
)

?

2 从两个表中select

create table ##b1
(
col1 int
)

create table ##b2
(
col2 int
)

insert ##b1 values(1)
insert ##b1 values(2)
insert ##b1 values(3)

insert ##b2 values(2)
insert ##b2 values(1)

select col1,col2 from ##b1,##b2

/*

得到的结果是2*3=6条记录

col1 col2

1????? 2
2????? 2
3????? 2
1???? 1
2???? 1
3???? 1

*/

3? 只保留时间的日期部分
select convert(varchar(10),getdate(),120)

?? /*

得到如下结果

2007-03-15

*/

?

4? 时间的小时,分秒部分归零

declare @date datetime
set @date=getdate()
select DATEADD(day,DATEDIFF(day,@date),0)

?

?? /*

?

得到如下结果

2007-03-15 00:00:00.000

*/

5? 读取第n条记录以后的记录

declare @n int

set @n=5

select * from 表 where id not in (select top (@n) id from 表)

6 如何得到时间段内的所有日期

给出起始时间和结束时间,然后的到之间的全部日期
例如其时日期为2007-1-1,结束日期为2007-1-5
得到如下结果
2007-1-1
2007-1-2
2007-1-3
2007-1-4
2007-1-5

?

?给出两个方法

1

?

create table #
(
col datetime
)
go
declare @begintime datetime
declare @endtime datetime
declare @day int
set @begintime=cast('2007-1-1' as datetime)
set @endtime =cast('2007-1-5' as datetime)
select @day=DATEDIFF(day,@begintime,@endtime)
while @day>=0
begin
insert into # select dateadd(day,@day,@begintime)
set @day=@day-1
end
select * from #
drop table #

?

2

?

Declare @StartDate DateTime,@EndDate DateTime
Select @StartDate = '2007-1-1',@EndDate = '2007-1-5'
Select Top 100 Identity(Int,1) As ID Into #T from Sysobjects A,Sysobjects B
Select
DateAdd(dd,ID,@StartDate) As [Date]
From #T
Where ID <= DateDiff(dd,@StartDate,@EndDate)
Drop table #T

?

?

６Full Join

?

a,b两表数据如下

a:
name chinese
张三 60
李四 70

b:
name english
张三 90
王五 80

希望查询的结果如下:
name chinese english
张三 60 90
李四 70 null
王五 null 90

sql语句如下

Select
IsNull(A.name,B.name) As name,
IsNull(A.chinese,0) As chinese,
IsNull(B.english,0) As english
From
A
Full Join
B
On A.name = B.name

?

７　条件语句的放置位置

语句如下:

select a.id,b.id from a

left join b on a.id=b.id and b.date>'2007-1-1'

?

?

select a.id,b.id from a

left join b on a.id=b.id
where b.date>'2007-1-1'

?

两个语句的结果是不一样的,第一个语句是现对b表进行筛选,然后再和a表join

而第二个语句是先join再对整体的join后的结果进行率选,所有可能得到比第一种少的结果集

?

８　对重复记录进行操作

?? 1 去除重复项

?

select * from table1 where col1 not in
(
select col1 from table1 group by col1 having count(col1)>1
)

?? 2 只筛选出重复项

?

?

select * from table1 where col1 in
(
select col1 from table1 group by col1 having count(col1)>1
)

?

９　判断是否为数字

ISNUMERIC(expression)

? 数字返回1

?非数字返回0

?

10?? 得到存储过程内容

select text from syscomments where id=object_id('procName')

11? 删除所有的存储过程

--使用游标

declare @delProc nvarchar(200)
declare @dll nvarchar(4000)
set @dll=''
set @delProc='drop proc '
declare @procName nvarchar(100)

DECLARE proc_cur CURSOR FOR
SELECT name FROM sysobjects where xtype='p';
OPEN proc_cur;
FETCH NEXT FROM proc_cur
into @procName
WHILE @@FETCH_STATUS = 0
BEGIN
set @dll=@dll+ ' '+ @delProc+@procName

FETCH NEXT FROM proc_cur
into @procname
END;
CLOSE proc_cur;
DEALLOCATE proc_cur;

print @dll

12 事务

BEGIN TRY

??? ?BEGIN TRAN

?

??? ?INSERT INTO table1 (i,col1,col2)

??? ?VALUES (1,'First row','First row');

??? ?INSERT INTO table1 (i,col2)

??? ?VALUES (2,NULL,'Second row');

??? ?INSERT INTO table1 (i,col2)

??? ?VALUES (3,'Third row','Third row');

?

??? ?COMMIT TRAN;

END TRY

BEGIN CATCH

??? ?ROLLBACK TRAN

END CATCH;

?

13 外键约束

create table company
(
id int identity(1,1),
name nvarchar(20) unique
)
create table product
(
id int identity(1,
companyid int,
name nvarchar(20),
unitprice money,
unique(companyid,name)
)

--添加外键约束

alter table product add constraint fk_company_prodcut
foreign key(companyid)
references company(id) on UPDATE CASCADE ON DELETE CASCADE

?

----删除

alter table product drop constraint? fk_company_prodcut

?

14? 触发器

USE tempdb
GO
CREATE TABLE TZ (
Z_id int IDENTITY(1,1)PRIMARY KEY,
Z_name varchar(20) NOT NULL)

CREATE TABLE TY (
Y_id int IDENTITY(100,5)PRIMARY KEY,
Y_name varchar(20) NULL)

/*Create the trigger that inserts a row in table TY
when a row is inserted in table TZ*/
CREATE TRIGGER Ztrigsssss
ON TZ
FOR INSERT,update,delete AS
BEGIN
INSERT TY VALUES ('')
END

15?? 备份数据库

use master
backup database databaseName to? disk='c:/1.bak'

16字符串拆分
将类似 1,2,3,4,5,6的字符串进行转秩
1
2
3
4
5
6


CREATE?? function StrSplit(@str nvarchar(4000),@splitSign nvarchar(10))
returns @table table (itemid int identity(1,tempStr nvarchar(3000))
as
???
??? begin
??? ??? while charindex(@splitSign,@str)>0
??? ??? ??? begin
??? ??? ??? ??? insert into @table (tempStr)
??? ??? ??? ??? select left(@str,charindex(@splitSign,@str)-1)
??? ??? ??? ???
??? ??? ??? ??? select @str=stuff(@str,1,@str)+len(@splitSign)-1,'')

??? ??? ??? end???
??? ??? insert @table (tempStr) select @str
??? ??? return
??? end



17? DBCC命令
? DBCC TRACEON(3604)
? DBCC?PAGE

? DBCC CheckConstraints(tableName)--检查表中不符合约束的数据

?DBCC ShrinkDatabase

?DBCC ShrinkFile

?

18 Query the DB size

SELECT type_desc,Sum(Size) FROM sys.database_Files Group by type_desc

?

CREATE TABLE sp_table_pages
(PageFID tinyint,
? PagePID int,
? IAMFID?? tinyint,
? IAMPID? int,
? ObjectID? int,
? IndexID? tinyint,
? PartitionNumber tinyint,
? PartitionID bigint,
? iam_chain_type varchar(30),
? PageType? tinyint,
? IndexLevel? tinyint,
? NextPageFID? tinyint,
? NextPagePID? int,
? PrevPageFID? tinyint,
? PrevPagePID int,
? Primary Key (PageFID,PagePID));

TRUNCATE TABLE sp_table_pages;

INSERT INTO sp_table_pages

EXEC ('dbcc ind
 ( AdventureWorks,[Sales.SalesOrderDetail],-1)' );

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!