SqlServer语句收集
来源:互联网 发布:大数据培训课程体系 编辑:程序博客网 时间:2024/06/10 07:17
来源于: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,0,@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, 0, 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
6Full 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
7 条件语句的放置位置
语句如下:
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后的结果进行率选,所有可能得到比第一种少的结果集
8 对重复记录进行操作
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
)
9 判断是否为数字
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,col1,col2)
VALUES (2,NULL,'Second row');
INSERT INTO table1 (i,col1,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,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,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,charindex(@splitSign,@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)' );
- SqlServer语句收集
- SQLserver 语句
- SqlServer 语句
- sqlserver语句
- SqlServer语句
- SQLServer 语句
- sqlserver语句
- 语句收集
- 从网上收集的精妙sql语句之二- sqlserver操作access和execl
- SqlServer整理收集
- SqlServer资料收集
- sqlserver 数据恢复 资料收集
- SQLServer存储过程收集总结
- 自动化收集SQLSERVER诊断信息
- SQLServer Windows数据收集器
- sqlserver语句总结
- sqlserver中注释语句
- sqlserver常用语句-维护
- 电影票问题
- VS2010下注释引发的惨案
- ffmpeg安装步骤(windows)
- 架构师点滴
- 学习网站(收藏)
- SqlServer语句收集
- 解读CRM规划时怎样做好项目的集成性设计
- ubuntu 搭建samba服务
- 当客户关系管理遭遇网络营销
- 限制scp命令传输速度
- Java中 new date 莫名其妙的时区错误 win7环境下
- Oracle Spatial空间分析详解
- 判断数据库是否存在的两种方法
- 项目经理修炼(1)