sql使用大全.docx

1、sql使用大全设置内存选项: SQL code -设置 min server memory 配置项EXEC sp_configure Nmin server memory (MB), 0-设置 max server memory 配置项EXEC sp_configure Nmax server memory (MB), 256-使更新生效RECONFIGURE WITH OVERRIDE使用文件及文件组.sql: SQL code /*-功能说明 下面的代码是在SQL Server 2000上创建名为 MyDB 的数据库 该数据库包括1个主要数据文件、3个用户定义的文件组和1个日志文件 ALT

2、ER DATABASE语句将用户定义文件组指定为默认文件组。 之后，通过指默认的文件组来创建表，并且将图像数据和索引放到指定的文件组中。 最后，将文件组中的指定数据文件删除-*/-切换到 master 数据库USE masterGO-A. 创建数据库 MyDBCREATE DATABASE MyDBON PRIMARY -主文件组和主要数据文件 ( NAME=MyDB_Primary, FILENAME= c:MyDB_Prm.mdf),FILEGROUP MyDB_FG1 -用户定义文件组1 ( NAME = MyDB_FG1_Dat1, FILENAME = c:MyDB_FG1_1.nd

3、f), -次要数据文件1 ( NAME = MyDB_FG1_Dat2, FILENAME = d:MyDB_FG1_2.ndf), -次要数据文件2FILEGROUP MyDB_FG2 -用户定义文件组2 ( NAME = MyDB_FG1_Dat, FILENAME = e:MyDB_FG2.ndf) -次要数据文件LOG ON -日志文件 ( NAME=MyDB_log, FILENAME =d:MyDB.ldf)GO-B. 修改默认数据文件组ALTER DATABASE MyDB MODIFY FILEGROUP MyDB_FG1 DEFAULTGO-切换到新建的数据库 MyDBUSE

4、 MyDB-C. 在默认文件组MyDB_FG1创建表,并且指定图像数据保存在用户定义文件组MMyDB_FG2CREATE TABLE MyTable ( cola int PRIMARY KEY , colb char(8) , colc image ) TEXTIMAGE_ON MyDB_FG2-在用户定义文件组MyDB_FG2上创建索引CREATE INDEX IX_MyTable ON MyTable(cola) ON MyDB_FG2GO-D. 将要删除数据文件MyDB_FG1_Dat1上的数据转移到其他数据文件中，并且清空数据文件MyDB_FG1_Dat1DBCC SHRINKFIL

5、E(MyDB_FG1_Dat1,EMPTYFILE)-删除数据文件MyDB_FG1_Dat1ALTER DATABASE MyDB REMOVE FILE MyDB_FG1_Dat1调整tempdb数据库的文件属性.sql: SQL code -A. 将tempdb数据库的主数据文件大小设置为10MB。ALTER DATABASE tempdbMODIFY FILE( name=tempdev, size=100 MB)GO-B. 将tempdb数据库的主数据文件移动到指定的磁盘分区上，并且为其添加一个数据文件。-移动主数据文件ALTER DATABASE tempdb MODIFY FILE

6、 ( NAME=tempdev, FILENAME =d:tempdb.mdf)-添加次要数据文件ALTER DATABASE tempdb ADD FILE ( NAME=tempdata_1, FILENAME =d:tempdb_data_1.ndf)日期概念理解中的一些测试.sql: SQL code -A. 测试 datetime 精度问题DECLARE t TABLE(date char(21)INSERT t SELECT 1900-1-1 00:00:00.000INSERT t SELECT 1900-1-1 00:00:00.001INSERT t SELECT 1900-

7、1-1 00:00:00.009INSERT t SELECT 1900-1-1 00:00:00.002INSERT t SELECT 1900-1-1 00:00:00.003INSERT t SELECT 1900-1-1 00:00:00.004INSERT t SELECT 1900-1-1 00:00:00.005INSERT t SELECT 1900-1-1 00:00:00.006INSERT t SELECT 1900-1-1 00:00:00.007INSERT t SELECT 1900-1-1 00:00:00.008SELECT date,转换后的日期=CAST(d

8、ate as datetime) FROM t/*-结果date 转换后的日期- -1900-1-1 00:00:00.000 1900-01-01 00:00:00.0001900-1-1 00:00:00.001 1900-01-01 00:00:00.0001900-1-1 00:00:00.009 1900-01-01 00:00:00.0101900-1-1 00:00:00.002 1900-01-01 00:00:00.0031900-1-1 00:00:00.003 1900-01-01 00:00:00.0031900-1-1 00:00:00.004 1900-01-01

9、00:00:00.0031900-1-1 00:00:00.005 1900-01-01 00:00:00.0071900-1-1 00:00:00.006 1900-01-01 00:00:00.0071900-1-1 00:00:00.007 1900-01-01 00:00:00.0071900-1-1 00:00:00.008 1900-01-01 00:00:00.007（所影响的行数为 10 行）-*/GO-B. 对于 datetime 类型的纯日期和时间的十六进制表示DECLARE dt datetime-单纯的日期SET dt=1900-1-2SELECT CAST(dt as

10、 binary(8)-结果: 0x0000000100000000-单纯的时间SET dt=00:00:01SELECT CAST(dt as binary(8)-结果: 0x000000000000012CGO-C. 对于 smalldatetime 类型的纯日期和时间的十六进制表示DECLARE dt smalldatetime-单纯的日期SET dt=1900-1-2SELECT CAST(dt as binary(4)-结果: 0x00010000-单纯的时间SET dt=00:10SELECT CAST(dt as binary(4)-结果: 0x0000000ACONVERT在日期

11、转换中的使用示例.sql: SQL code -字符转换为日期时,Style的使用-1. Style=101时,表示日期字符串为:mm/dd/yyyy格式SELECT CONVERT(datetime,11/1/2003,101)-结果:2003-11-01 00:00:00.000-2. Style=101时,表示日期字符串为:dd/mm/yyyy格式SELECT CONVERT(datetime,11/1/2003,103)-结果:2003-01-11 00:00:00.000/*= 日期转换为字符串 =*/DECLARE dt datetimeSET dt=2003-1-11-1. St

12、yle=101时,表示将日期转换为:mm/dd/yyyy 格式SELECT CONVERT(varchar,dt,101)-结果:01/11/2003-2. Style=103时,表示将日期转换为:dd/mm/yyyy 格式SELECT CONVERT(varchar,dt,103)-结果:11/01/2003/*= 这是很多人经常犯的错误,对非日期型转换使用日期的style样式 =*/SELECT CONVERT(varchar,2003-1-11,101)-结果:2003-1-11SET DATEFORMAT对日期处理的影响.sql SQL code -1./*-说明 SET DATEFO

13、RMAT设置对使用CONVERT把字符型日期转换为日期的处理也具有影响 但不影响明确指定了style的CONVERT处理。-*/-示例 ，在下面的示例中，第一个CONVERT转换未指定style，转换的结果受SET DATAFORMAT的影响，第二个CONVERT转换指定了style，转换结果受style的影响。-设置输入日期顺序为 日/月/年SET DATEFORMAT DMY-不指定Style参数的CONVERT转换将受到SET DATEFORMAT的影响SELECT CONVERT(datetime,2-1-2005)-结果: 2005-01-02 00:00:00.000-指定Styl

14、e参数的CONVERT转换不受SET DATEFORMAT的影响SELECT CONVERT(datetime,2-1-2005,101)-结果: 2005-02-01 00:00:00.000GO-2./*-说明 如果输入的日期包含了世纪部分，则对日期进行解释处理时 年份的解释不受SET DATEFORMAT设置的影响。-*/-示例，在下面的代码中，同样的SET DATEFORMAT设置，输入日期的世纪部分与不输入日期的世纪部分，解释的日期结果不同。DECLARE dt datetime-设置SET DATEFORMAT为:月日年SET DATEFORMAT MDY-输入的日期中指定世纪部分

15、SET dt=01-2002-03SELECT dt-结果: 2002-01-03 00:00:00.000-输入的日期中不指定世纪部分SET dt=01-02-03SELECT dt-结果: 2003-01-02 00:00:00.000GO-3./*-说明 如果输入的日期不包含日期分隔符，那么SQL Server在对日期进行解释时 将忽略SET DATEFORMAT的设置。-*/-示例，在下面的代码中，不包含日期分隔符的字符日期，在不同的SET DATEFORMAT设置下，其解释的结果是一样的。DECLARE dt datetime-设置SET DATEFORMAT为:月日年SET DAT

16、EFORMAT MDYSET dt=010203SELECT dt-结果: 2001-02-03 00:00:00.000-设置SET DATEFORMAT为:日月年SET DATEFORMAT DMYSET dt=010203SELECT dt-结果: 2001-02-03 00:00:00.000-输入的日期中包含日期分隔符SET dt=01-02-03SELECT dt-结果: 2003-02-01 00:00:00.000SET LANGUAGE对日期处理的影响示例.sql SQL code -以下示例演示了在不同的语言环境(SET LANGUAGE)下，DATENAME与CONVER

17、T函数的不同结果。USE master-设置会话的语言环境为: EnglishSET LANGUAGE NEnglishSELECT DATENAME(Month,GETDATE() AS Month, DATENAME(Weekday,GETDATE() AS Weekday, CONVERT(varchar,GETDATE(),109) AS CONVERT/*-结果:Month Weekday CONVERT- - -March Tuesday Mar 15 2005 8:59PM-*/-设置会话的语言环境为: 简体中文SET LANGUAGE N简体中文SELECT DATENAME(

18、Month,GETDATE() AS Month, DATENAME(Weekday,GETDATE() AS Weekday, CONVERT(varchar,GETDATE(),109) AS CONVERT/*-结果Month Weekday CONVERT- - -05 星期四 05 19 2005 2:49:20:607PM-*/日期格式化处理.sql SQL code DECLARE dt datetimeSET dt=GETDATE()-1短日期格式：yyyy-m-dSELECT REPLACE(CONVERT(varchar(10),dt,120),N-0,-)-2长日期格式：

19、yyyy年mm月dd日 -A. 方法1 SELECT STUFF(STUFF(CONVERT(char(8),dt,112),5,0,N年),8,0,N月)+N日-B. 方法2 SELECT DATENAME(Year,dt)+N年+DATENAME(Month,dt)+N月+DATENAME(Day,dt)+N日-3长日期格式：yyyy年m月d日SELECT DATENAME(Year,dt)+N年+CAST(DATEPART(Month,dt) AS varchar)+N月+DATENAME(Day,dt)+N日-4.完整日期+时间格式：yyyy-mm-dd hh:mi:ss:mmmSEL

20、ECT CONVERT(char(11),dt,120)+CONVERT(char(12),dt,114)日期推算处理.sql SQL code DECLARE dt datetimeSET dt=GETDATE()DECLARE number intSET number=3-1指定日期该年的第一天或最后一天-A. 年的第一天SELECT CONVERT(char(5),dt,120)+1-1-B. 年的最后一天SELECT CONVERT(char(5),dt,120)+12-31-2指定日期所在季度的第一天或最后一天-A. 季度的第一天SELECT CONVERT(datetime, CO

21、NVERT(char(8), DATEADD(Month, DATEPART(Quarter,dt)*3-Month(dt)-2, dt), 120)+1)-B. 季度的最后一天（CASE判断法）SELECT CONVERT(datetime, CONVERT(char(8), DATEADD(Month, DATEPART(Quarter,dt)*3-Month(dt), dt), 120) +CASE WHEN DATEPART(Quarter,dt) in(1,4) THEN 31ELSE 30 END)-C. 季度的最后一天（直接推算法）SELECT DATEADD(Day,-1, C

22、ONVERT(char(8), DATEADD(Month, 1+DATEPART(Quarter,dt)*3-Month(dt), dt), 120)+1)-3指定日期所在月份的第一天或最后一天-A. 月的第一天SELECT CONVERT(datetime,CONVERT(char(8),dt,120)+1)-B. 月的最后一天SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,dt),120)+1)-C. 月的最后一天（容易使用的错误方法）SELECT DATEADD(Month,1,DATEADD(Day,-DAY(dt),dt)

23、-4指定日期所在周的任意一天SELECT DATEADD(Day,number-DATEPART(Weekday,dt),dt)-5指定日期所在周的任意星期几-A. 星期天做为一周的第1天SELECT DATEADD(Day,number-(DATEPART(Weekday,dt)+DATEFIRST-1)%7,dt)-B. 星期一做为一周的第1天SELECT DATEADD(Day,number-(DATEPART(Weekday,dt)+DATEFIRST-2)%7-1,dt)特殊日期加减函数.sql SQL code if exists (select * from dbo.sysobj

24、ects where id = object_id(Ndbo.f_DateADD) and xtype in (NFN, NIF, NTF) drop function dbo.f_DateADDGO/*-特殊日期加减函数 对于日期指定部分的加减，使用DATEADD函数就可以轻松实现。 在实际的处理中，还有一种比较另类的日期加减处理 就是在指定的日期中，加上（或者减去）多个日期部分 比如将2005年3月11日，加上1年3个月11天2小时。 对于这种日期的加减处理，DATEADD函数的力量就显得有点不够。 本函数实现这样格式的日期字符串加减处理： y-m-d h:m:s.m | -y-m-d h

25、:m:s.m 说明： 要加减的日期字符输入方式与日期字符串相同。日期与时间部分用空格分隔 最前面一个字符如果是减号（-）的话，表示做减法处理，否则做加法处理。 如果日期字符只包含数字，则视为日期字符中，仅包含天的信息。-*/*-调用示例 SELECT dbo.f_DateADD(GETDATE(),11:10)-*/CREATE FUNCTION dbo.f_DateADD(Date datetime,DateStr varchar(23)RETURNS datetimeASBEGIN DECLARE bz int,s varchar(12),i int IF DateStr IS NULL

26、OR Date IS NULL OR(CHARINDEX(.,DateStr)0 AND DateStr NOT LIKE %:%:%.%) RETURN(NULL) IF DateStr= RETURN(Date) SELECT bz=CASE WHEN LEFT(DateStr,1)=- THEN -1 ELSE 1 END, DateStr=CASE WHEN LEFT(Date,1)=- THEN STUFF(RTRIM(LTRIM(DateStr),1,1,) ELSE RTRIM(LTRIM(DateStr) END IF CHARINDEX( ,DateStr)1 OR CHAR

27、INDEX(-,DateStr)1 OR(CHARINDEX(.,DateStr)=0 AND CHARINDEX(:,DateStr)=0) BEGIN SELECT i=CHARINDEX( ,DateStr+ ) ,s=REVERSE(LEFT(DateStr,i-1)+- ,DateStr=STUFF(DateStr,1,i,) ,i=0 WHILE s and i BEGIN IF CHARINDEX(.,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 i3 SELECT Date=CASE i WHEN 0 THEN DATEADD(Hour,bz*LEFT(D