sql使用大全.docx
《sql使用大全.docx》由会员分享，可在线阅读，更多相关《sql使用大全.docx（46页珍藏版）》请在冰豆网上搜索。
sql使用大全
设置内存选项:
SQLcode
--设置minservermemory配置项
EXECsp_configureN'minservermemory（MB）',0
--设置maxservermemory配置项
EXECsp_configureN'maxservermemory（MB）',256
--使更新生效
RECONFIGUREWITHOVERRIDE
使用文件及文件组.sql:
SQLcode
/*--功能说明
下面的代码是在SQLServer2000上创建名为MyDB的数据库
该数据库包括1个主要数据文件、3个用户定义的文件组和1个日志文件
ALTERDATABASE语句将用户定义文件组指定为默认文件组。
之后，通过指默认的文件组来创建表，并且将图像数据和索引放到指定的文件组中。
最后，将文件组中的指定数据文件删除
--*/
--切换到master数据库
USEmaster
GO
--A.创建数据库MyDB
CREATEDATABASEMyDB
ONPRIMARY--主文件组和主要数据文件
（NAME='MyDB_Primary',
FILENAME='c:
\MyDB_Prm.mdf'）,
FILEGROUPMyDB_FG1--用户定义文件组1
（NAME='MyDB_FG1_Dat1',
FILENAME='c:
\MyDB_FG1_1.ndf'）,--次要数据文件1
（NAME='MyDB_FG1_Dat2',
FILENAME='d:
\MyDB_FG1_2.ndf'）,--次要数据文件2
FILEGROUPMyDB_FG2--用户定义文件组2
（NAME='MyDB_FG1_Dat',
FILENAME='e:
\MyDB_FG2.ndf'）--次要数据文件
LOGON--日志文件
（NAME='MyDB_log',
FILENAME='d:
\MyDB.ldf'）
GO
--B.修改默认数据文件组
ALTERDATABASEMyDBMODIFYFILEGROUPMyDB_FG1DEFAULT
GO
--切换到新建的数据库MyDB
USEMyDB
--C.在默认文件组MyDB_FG1创建表,并且指定图像数据保存在用户定义文件组MMyDB_FG2
CREATETABLEMyTable
（colaintPRIMARYKEY,
colbchar（8）,
colcimage）
TEXTIMAGE_ONMyDB_FG2
--在用户定义文件组MyDB_FG2上创建索引
CREATEINDEXIX_MyTableONMyTable（cola）ONMyDB_FG2
GO
--D.将要删除数据文件MyDB_FG1_Dat1上的数据转移到其他数据文件中，并且清空数据文件MyDB_FG1_Dat1
DBCCSHRINKFILE（MyDB_FG1_Dat1,EMPTYFILE）
--删除数据文件MyDB_FG1_Dat1
ALTERDATABASEMyDBREMOVEFILEMyDB_FG1_Dat1
调整tempdb数据库的文件属性.sql:
SQLcode
--A.将tempdb数据库的主数据文件大小设置为10MB。
ALTERDATABASEtempdb
MODIFYFILE（
name=tempdev,
size=100MB）
GO
--B.将tempdb数据库的主数据文件移动到指定的磁盘分区上，并且为其添加一个数据文件。
--移动主数据文件
ALTERDATABASEtempdbMODIFYFILE
（NAME='tempdev',
FILENAME='d:
\tempdb.mdf'）
--添加次要数据文件
ALTERDATABASEtempdbADDFILE
（NAME='tempdata_1',
FILENAME='d:
\tempdb_data_1.ndf'）
日期概念理解中的一些测试.sql:
SQLcode
--A.测试datetime精度问题
DECLARE@tTABLE（datechar（21））
INSERT@tSELECT'1900-1-100:
00:
00.000'
INSERT@tSELECT'1900-1-100:
00:
00.001'
INSERT@tSELECT'1900-1-100:
00:
00.009'
INSERT@tSELECT'1900-1-100:
00:
00.002'
INSERT@tSELECT'1900-1-100:
00:
00.003'
INSERT@tSELECT'1900-1-100:
00:
00.004'
INSERT@tSELECT'1900-1-100:
00:
00.005'
INSERT@tSELECT'1900-1-100:
00:
00.006'
INSERT@tSELECT'1900-1-100:
00:
00.007'
INSERT@tSELECT'1900-1-100:
00:
00.008'
SELECTdate,转换后的日期=CAST（dateasdatetime）FROM@t
/*--结果
date转换后的日期
-----------------------------------------------
1900-1-100:
00:
00.0001900-01-0100:
00:
00.000
1900-1-100:
00:
00.0011900-01-0100:
00:
00.000
1900-1-100:
00:
00.0091900-01-0100:
00:
00.010
1900-1-100:
00:
00.0021900-01-0100:
00:
00.003
1900-1-100:
00:
00.0031900-01-0100:
00:
00.003
1900-1-100:
00:
00.0041900-01-0100:
00:
00.003
1900-1-100:
00:
00.0051900-01-0100:
00:
00.007
1900-1-100:
00:
00.0061900-01-0100:
00:
00.007
1900-1-100:
00:
00.0071900-01-0100:
00:
00.007
1900-1-100:
00:
00.0081900-01-0100:
00:
00.007
（所影响的行数为10行）
--*/
GO
--B.对于datetime类型的纯日期和时间的十六进制表示
DECLARE@dtdatetime
--单纯的日期
SET@dt='1900-1-2'
SELECTCAST（@dtasbinary（8））
--结果:
0x0000000100000000
--单纯的时间
SET@dt='00:
00:
01'
SELECTCAST（@dtasbinary（8））
--结果:
0x000000000000012C
GO
--C.对于smalldatetime类型的纯日期和时间的十六进制表示
DECLARE@dtsmalldatetime
--单纯的日期
SET@dt='1900-1-2'
SELECTCAST（@dtasbinary（4））
--结果:
0x00010000
--单纯的时间
SET@dt='00:
10'
SELECTCAST（@dtasbinary（4））
--结果:
0x0000000A
CONVERT在日期转换中的使用示例.sql:
SQLcode
--字符转换为日期时,Style的使用
--1.Style=101时,表示日期字符串为:
mm/dd/yyyy格式
SELECTCONVERT（datetime,'11/1/2003',101）
--结果:
2003-11-0100:
00:
00.000
--2.Style=101时,表示日期字符串为:
dd/mm/yyyy格式
SELECTCONVERT（datetime,'11/1/2003',103）
--结果:
2003-01-1100:
00:
00.000
/*==日期转换为字符串==*/
DECLARE@dtdatetime
SET@dt='2003-1-11'
--1.Style=101时,表示将日期转换为:
mm/dd/yyyy格式
SELECTCONVERT（varchar,@dt,101）
--结果:
01/11/2003
--2.Style=103时,表示将日期转换为:
dd/mm/yyyy格式
SELECTCONVERT（varchar,@dt,103）
--结果:
11/01/2003
/*==这是很多人经常犯的错误,对非日期型转换使用日期的style样式==*/
SELECTCONVERT（varchar,'2003-1-11',101）
--结果:
2003-1-11
SETDATEFORMAT对日期处理的影响.sql
SQLcode
--1.
/*--说明
SETDATEFORMAT设置对使用CONVERT把字符型日期转换为日期的处理也具有影响
但不影响明确指定了style的CONVERT处理。
--*/
--示例，在下面的示例中，第一个CONVERT转换未指定style，转换的结果受SETDATAFORMAT的影响，第二个CONVERT转换指定了style，转换结果受style的影响。
--设置输入日期顺序为日/月/年
SETDATEFORMATDMY
--不指定Style参数的CONVERT转换将受到SETDATEFORMAT的影响
SELECTCONVERT（datetime,'2-1-2005'）
--结果:
2005-01-0200:
00:
00.000
--指定Style参数的CONVERT转换不受SETDATEFORMAT的影响
SELECTCONVERT（datetime,'2-1-2005',101）
--结果:
2005-02-0100:
00:
00.000
GO
--2.
/*--说明
如果输入的日期包含了世纪部分，则对日期进行解释处理时
年份的解释不受SETDATEFORMAT设置的影响。
--*/
--示例，在下面的代码中，同样的SETDATEFORMAT设置，输入日期的世纪部分与不输入日期的世纪部分，解释的日期结果不同。
DECLARE@dtdatetime
--设置SETDATEFORMAT为:
月日年
SETDATEFORMATMDY
--输入的日期中指定世纪部分
SET@dt='01-2002-03'
SELECT@dt
--结果:
2002-01-0300:
00:
00.000
--输入的日期中不指定世纪部分
SET@dt='01-02-03'
SELECT@dt
--结果:
2003-01-0200:
00:
00.000
GO
--3.
/*--说明
如果输入的日期不包含日期分隔符，那么SQLServer在对日期进行解释时
将忽略SETDATEFORMAT的设置。
--*/
--示例，在下面的代码中，不包含日期分隔符的字符日期，在不同的SETDATEFORMAT设置下，其解释的结果是一样的。
DECLARE@dtdatetime
--设置SETDATEFORMAT为:
月日年
SETDATEFORMATMDY
SET@dt='010203'
SELECT@dt
--结果:
2001-02-0300:
00:
00.000
--设置SETDATEFORMAT为:
日月年
SETDATEFORMATDMY
SET@dt='010203'
SELECT@dt
--结果:
2001-02-0300:
00:
00.000
--输入的日期中包含日期分隔符
SET@dt='01-02-03'
SELECT@dt
--结果:
2003-02-0100:
00:
00.000
SETLANGUAGE对日期处理的影响示例.sql
SQLcode
--以下示例演示了在不同的语言环境（SETLANGUAGE）下，DATENAME与CONVERT函数的不同结果。
USEmaster
--设置会话的语言环境为:
English
SETLANGUAGEN'English'
SELECT
DATENAME（Month,GETDATE（））AS[Month],
DATENAME（Weekday,GETDATE（））AS[Weekday],
CONVERT（varchar,GETDATE（）,109）AS[CONVERT]
/*--结果:
MonthWeekdayCONVERT
----------------------------------------------------------
MarchTuesdayMar1520058:
59PM
--*/
--设置会话的语言环境为:
简体中文
SETLANGUAGEN'简体中文'
SELECT
DATENAME（Month,GETDATE（））AS[Month],
DATENAME（Weekday,GETDATE（））AS[Weekday],
CONVERT（varchar,GETDATE（）,109）AS[CONVERT]
/*--结果
MonthWeekdayCONVERT
---------------------------------------------------------------------
05星期四051920052:
49:
20:
607PM
--*/
日期格式化处理.sql
SQLcode
DECLARE@dtdatetime
SET@dt=GETDATE（）
--1．短日期格式：
yyyy-m-d
SELECTREPLACE（CONVERT（varchar（10）,@dt,120）,N'-0','-'）
--2．长日期格式：
yyyy年mm月dd日
--A.方法1
SELECTSTUFF（STUFF（CONVERT（char（8）,@dt,112）,5,0,N'年'）,8,0,N'月'）+N'日'
--B.方法2
SELECTDATENAME（Year,@dt）+N'年'+DATENAME（Month,@dt）+N'月'+DATENAME（Day,@dt）+N'日'
--3．长日期格式：
yyyy年m月d日
SELECTDATENAME（Year,@dt）+N'年'+CAST（DATEPART（Month,@dt）ASvarchar）+N'月'+DATENAME（Day,@dt）+N'日'
--4.完整日期+时间格式：
yyyy-mm-ddhh:
mi:
ss:
mmm
SELECTCONVERT（char（11）,@dt,120）+CONVERT（char（12）,@dt,114）
日期推算处理.sql
SQLcode
DECLARE@dtdatetime
SET@dt=GETDATE（）
DECLARE@numberint
SET@number=3
--1．指定日期该年的第一天或最后一天
--A.年的第一天
SELECTCONVERT（char（5）,@dt,120）+'1-1'
--B.年的最后一天
SELECTCONVERT（char（5）,@dt,120）+'12-31'
--2．指定日期所在季度的第一天或最后一天
--A.季度的第一天
SELECTCONVERT（datetime,
CONVERT（char（8）,
DATEADD（Month,
DATEPART（Quarter,@dt）*3-Month（@dt）-2,
@dt）,
120）+'1'）
--B.季度的最后一天（CASE判断法）
SELECTCONVERT（datetime,
CONVERT（char（8）,
DATEADD（Month,
DATEPART（Quarter,@dt）*3-Month（@dt）,
@dt）,
120）
+CASEWHENDATEPART（Quarter,@dt）in（1,4）
THEN'31'ELSE'30'END）
--C.季度的最后一天（直接推算法）
SELECTDATEADD（Day,-1,
CONVERT（char（8）,
DATEADD（Month,
1+DATEPART（Quarter,@dt）*3-Month（@dt）,
@dt）,
120）+'1'）
--3．指定日期所在月份的第一天或最后一天
--A.月的第一天
SELECTCONVERT（datetime,CONVERT（char（8）,@dt,120）+'1'）
--B.月的最后一天
SELECTDATEADD（Day,-1,CONVERT（char（8）,DATEADD（Month,1,@dt）,120）+'1'）
--C.月的最后一天（容易使用的错误方法）
SELECTDATEADD（Month,1,DATEADD（Day,-DAY（@dt）,@dt））
--4．指定日期所在周的任意一天
SELECTDATEADD（Day,@number-DATEPART（Weekday,@dt）,@dt）
--5．指定日期所在周的任意星期几
--A.星期天做为一周的第1天
SELECTDATEADD（Day,@number-（DATEPART（Weekday,@dt）+@@DATEFIRST-1）%7,@dt）
--B.星期一做为一周的第1天
SELECTDATEADD（Day,@number-（DATEPART（Weekday,@dt）+@@DATEFIRST-2）%7-1,@dt）
特殊日期加减函数.sql
SQLcode
ifexists（select*fromdbo.sysobjectswhereid=object_id（N'[dbo].[f_DateADD]'）andxtypein（N'FN',N'IF',N'TF'））
dropfunction[dbo].[f_DateADD]
GO
/*--特殊日期加减函数
对于日期指定部分的加减，使用DATEADD函数就可以轻松实现。
在实际的处理中，还有一种比较另类的日期加减处理
就是在指定的日期中，加上（或者减去）多个日期部分
比如将2005年3月11日，加上1年3个月11天2小时。
对于这种日期的加减处理，DATEADD函数的力量就显得有点不够。
本函数实现这样格式的日期字符串加减处理：
y-m-dh:
m:
s.m|-y-m-dh:
m:
s.m
说明：
要加减的日期字符输入方式与日期字符串相同。
日期与时间部分用空格分隔
最前面一个字符如果是减号（-）的话，表示做减法处理，否则做加法处理。
如果日期字符只包含数字，则视为日期字符中，仅包含天的信息。
--*/
/*--调用示例
SELECTdbo.f_DateADD（GETDATE（）,'11:
10'）
--*/
CREATEFUNCTIONdbo.f_DateADD（
@Datedatetime,
@DateStrvarchar（23）
）RETURNSdatetime
AS
BEGIN
DECLARE@bzint,@svarchar（12）,@iint
IF@DateStrISNULLOR@DateISNULL
OR（CHARINDEX（'.',@DateStr）>0
AND@DateStrNOTLIKE'%[:
]%[:
]%.%'）
RETURN（NULL）
IF@DateStr=''RETURN（@Date）
SELECT@bz=CASE
WHENLEFT（@DateStr,1）='-'THEN-1
ELSE1END,
@DateStr=CASE
WHENLEFT（@Date,1）='-'
THENSTUFF（RTRIM（LTRIM（@DateStr））,1,1,''）
ELSERTRIM（LTRIM（@DateStr））END
IFCHARINDEX（'',@DateStr）>1
ORCHARINDEX（'-',@DateStr）>1
OR（CHARINDEX（'.',@DateStr）=0
ANDCHARINDEX（':
',@DateStr）=0）
BEGIN
SELECT@i=CHARINDEX（'',@DateStr+''）
@s=REVERSE（LEFT（@DateStr,@i-1））+'-'
@DateStr=STUFF（@DateStr,1,@i,''）
@i=0
WHILE@s>''and@i<3
SELECT@Date=CASE@i
WHEN0THENDATEADD（Day,@bz*REVERSE（LEFT（@s,CHARINDEX（'-',@s）-1））,@Date）
WHEN1THENDATEADD（Month,@bz*REVERSE（LEFT（@s,CHARINDEX（'-',@s）-1））,@Date）
WHEN2THENDATEADD（Year,@bz*REVERSE（LEFT（@s,CHARINDEX（'-',@s）-1））,@Date）
END,
@s=STUFF（@s,1,CHARINDEX（'-',@s）,''）,
@i=@i+1
END
IF@DateStr>''
BEGIN
IFCHARINDEX（'.',@DateStr）>0
SELECT@Date=DATEADD（Millisecond
@bz*STUFF（@DateStr,1,CHARINDEX（'.',@DateStr）,''）,
@Date）,
@DateStr=LEFT（@DateStr,CHARINDEX（'.',@DateStr）-1）+':
',
@i=0
ELSE
SELECT@DateStr=@DateStr+':
',@i=0
WHILE@DateStr>''and@i<3
SELECT@Date=CASE@i
WHEN0THENDATEADD（Hour,@bz*LEFT（@D
升级会员 