(高等教育)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上創(chuàng)建名為 MyDB 的數(shù)據庫 該數(shù)據庫包括1個主要數(shù)據文件、3個用戶定義的文件組和1個日志文件 ALTER DATABASE語句將用戶定義文件組指定為默認文件組。 之后，通過指默認的文件組來創(chuàng)建表，并且將圖像數(shù)據和索引放到指定的文件組中。 最后，將文件組中的指定數(shù)據文件刪除-*/-切換到 master 數(shù)據庫USE masterGO-A. 創(chuàng)建數(shù)據庫 MyDBCREATE DATABASE MyDBON PRIMARY -主文件組和主要數(shù)據文件 ( NAME=MyDB_Primary, FILENAME= c:MyDB_Prm.mdf),FILEGROUP MyDB_FG1 -用戶定義文件組1 ( NAME = MyDB_FG1_Dat1, FILENAME = c:MyDB_FG1_1.ndf), -次要數(shù)據文件1 ( NAME = MyDB_FG1_Dat2, FILENAME = d:MyDB_FG1_2.ndf), -次要數(shù)據文件2FILEGROUP MyDB_FG2 -用戶定義文件組2 ( NAME = MyDB_FG1_Dat, FILENAME = e:MyDB_FG2.ndf) -次要數(shù)據文件LOG ON -日志文件 ( NAME=MyDB_log, FILENAME =d:MyDB.ldf)GO-B. 修改默認數(shù)據文件組ALTER DATABASE MyDB MODIFY FILEGROUP MyDB_FG1 DEFAULTGO-切換到新建的數(shù)據庫 MyDBUSE MyDB-C. 在默認文件組MyDB_FG1創(chuàng)建表,并且指定圖像數(shù)據保存在用戶定義文件組MMyDB_FG2CREATE TABLE MyTable ( cola int PRIMARY KEY , colb char(8) , colc image ) TEXTIMAGE_ON MyDB_FG2-在用戶定義文件組MyDB_FG2上創(chuàng)建索引CREATE INDEX IX_MyTable ON MyTable(cola) ON MyDB_FG2GO-D. 將要刪除數(shù)據文件MyDB_FG1_Dat1上的數(shù)據轉移到其他數(shù)據文件中，并且清空數(shù)據文件MyDB_FG1_Dat1DBCC SHRINKFILE(MyDB_FG1_Dat1,EMPTYFILE)-刪除數(shù)據文件MyDB_FG1_Dat1ALTER DATABASE MyDB REMOVE FILE MyDB_FG1_Dat1調整tempdb數(shù)據庫的文件屬性.sql: SQL code -A. 將tempdb數(shù)據庫的主數(shù)據文件大小設置為10MB。ALTER DATABASE tempdbMODIFY FILE( name=tempdev, size=100 MB)GO-B. 將tempdb數(shù)據庫的主數(shù)據文件移動到指定的磁盤分區(qū)上，并且為其添加一個數(shù)據文件。-移動主數(shù)據文件ALTER DATABASE tempdb MODIFY FILE ( NAME=tempdev, FILENAME =d:tempdb.mdf)-添加次要數(shù)據文件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-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(date 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 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（所影響的行數(shù)為 10 行）-*/GO-B. 對于 datetime 類型的純日期和時間的十六進制表示DECLARE dt datetime-單純的日期SET dt=1900-1-2SELECT CAST(dt as 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在日期轉換中的使用示例.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. Style=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 DATEFORMAT設置對使用CONVERT把字符型日期轉換為日期的處理也具有影響 但不影響明確指定了style的CONVERT處理。-*/-示例 ，在下面的示例中，第一個CONVERT轉換未指定style，轉換的結果受SET DATAFORMAT的影響，第二個CONVERT轉換指定了style，轉換結果受style的影響。-設置輸入日期順序為 日/月/年SET DATEFORMAT DMY-不指定Style參數(shù)的CONVERT轉換將受到SET DATEFORMAT的影響SELECT CONVERT(datetime,2-1-2005)-結果: 2005-01-02 00:00:00.000-指定Style參數(shù)的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-輸入的日期中指定世紀部分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 DATEFORMAT 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 -以下示例演示了在不同的語言環(huán)境(SET LANGUAGE)下，DATENAME與CONVERT函數(shù)的不同結果。USE master-設置會話的語言環(huán)境為: 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-*/-設置會話的語言環(huán)境為: 簡體中文SET LANGUAGE N簡體中文SELECT DATENAME(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長日期格式：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:mmmSELECT 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, CONVERT(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, CONVERT(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)-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)特殊日期加減函數(shù).sql SQL code if exists (select * from dbo.sysobjects where id = object_id(Ndbo.f_DateADD) and xtype in (NFN, NIF, NTF) drop function dbo.f_DateADDGO/*-特殊日期加減函數(shù) 對于日期指定部分的加減，使用DATEADD函數(shù)就可以輕松實現(xiàn)。 在實際的處理中，還有一種比較另類的日期加減處理 就是在指定的日期中，加上（或者減去）多個日期部分 比如將2005年3月11日，加上1年3個月11天2小時。 對于這種日期的加減處理，DATEADD函數(shù)的力量就顯得有點不夠。 本函數(shù)實現(xiàn)這樣格式的日期字符串加減處理： y-m-d h:m:s.m | -y-m-d h:m:s.m 說明： 要加減的日期字符輸入方式與日期字符串相同。日期與時間部分用空格分隔 最前面一個字符如果是減號（-）的話，表示做減法處理，否則做加法處理。 如果日期字符只包含數(shù)字，則視為日期字符中，僅包含天的信息。-*/*-調用示例 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 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 CHARINDEX(-,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 iDATEADD(Year,YEAR-1900,1900-12-31) IF bz=0 INSERT INTO re(Date,Weekday) SELECT Date,DATENAME(Weekday,Date) FROM tb WHERE (DATEPART(Weekday,Date)+DATEFIRST-1)%7 BETWEEN 1 AND 5 ELSE IF bz=1 INSERT INTO re(Date,Weekday) SELECT Date,DATENAME(Weekday,Date) FROM tb WHERE (DATEPART(Weekday,Date)+DATEFIRST-1)%7 IN (0,6) ELSE INSERT INTO re(Date,Weekday) SELECT Date,DATENAME(Weekday,Date) FROM tb RETURNENDGO/*=*/if exists (select * from dbo.sysobjects where id = object_id(Ndbo.f_getdate) and xtype in (NFN, NIF, NTF)drop function dbo.f_getdateGO/*-生成列表 生成指定日期段的日期列表-鄒建 2005.03(引用請保留此信息)-*/*-調用示例 -查詢工作日 SELECT * FROM dbo.f_getdate(2005-1-3,2005-4-5,0) -查詢休息日 SELECT * FROM dbo.f_getdate(2005-1-3,2005-4-5,1) -查詢全部日期 SELECT * FROM dbo.f_getdate(2005-1-3,2005-4-5,NULL)-*/CREATE FUNCTION dbo.f_getdate(begin_date Datetime, -要查詢的開始日期end_date Datetime, -要查詢的結束日期bz bit -bz=0 查詢工作日,bz=1 查詢休息日,bz IS NULL 查詢全部日期)RETURNS re TABLE(id int identity(1,1),Date datetime,Weekday nvarchar(3)ASBEGIN DECLARE tb TABLE(ID int IDENTITY(0,1),a bit) INSERT INTO tb(a) SELECT TOP 366 0 FROM sysobjects a ,sysobjects b IF bz=0 WHILE begin_date=end_date BEGIN INSERT INTO re(Date,Weekday) SELECT Date,DATENAME(Weekday,Date) FROM( SELECT Date=DATEADD(Day,ID,begin_date) FROM tb )a WHERE Date=end_date AND (DATEPART(Weekday,Date)+DATEFIRST-1)%7 BETWEEN 1 AND 5 SET begin_date=DATEADD(Day,366,begin_date) END ELSE IF bz=1 WHILE begin_date=end_date BEGIN INSERT INTO re(Date,Weekday) SELECT Date,DATENAME(Weekday,Date) FROM( SELECT Date=DATEADD(Day,ID,begin_date) FROM tb )a WHERE Date=end_date AND (DATEPART(Weekday,Date)+DATEFIRST-1)%7 in(0,6) SET begin_date=DATEADD(Day,366,begin_date) END ELSE WHILE begin_date=end_date BEGIN INSERT INTO re(Date,Weekday) SELECT Date,DATENAME(Weekday,Date) FROM( SELECT Date=DATEADD(Day,ID,begin_date) FROM tb )a WHERE Datedt_end SELECT bz=1,dt=dt_begin,dt_begin=dt_end,dt_end=dt ELSE SET bz=0 SELECT i=DATEDIFF(Day,dt_begin,dt_end)+1, workday=i/7*5, dt_begin=DATEADD(Day,i/7*7,dt_begin) WHILE dt_begin=dt_end BEGIN SELECT workday=CASE WHEN (DATEFIRST+DATEPART(Weekday,dt_begin)-1)%7 BETWEEN 1 AND 5 THEN workday+1 ELSE workday END, dt_begin=dt_begin+1 END RETURN(CASE WHEN bz=1 THEN -workday ELSE workday END)ENDGO/*=*/if exists (select * from dbo.sysobjects where id = object_id(Ndbo.f_WorkDayADD) and xtype in (NFN, NIF, NTF)drop function dbo.f_WorkDayADDGO-在指定日期上,增加指定工作天數(shù)后的日期CREATE FUNCTION f_WorkDayADD(date datetime, -基礎日期workday int -要增加的工作日數(shù))RETURNS datetimeASBEGIN DECLARE bz int -增加整周的天數(shù) SELECT bz=CASE WHEN workday0 THEN -1 ELSE 1 END ,date=DATEADD(Week,workday/5,date) ,workday=workday%5 -增加不是整周的工作天數(shù) WHILE workday0 SELECT date=DATEADD(Day,bz,date), workday=CASE WHEN (DATEFIRST+DATEPART(Weekday,date)-1)%7 BETWEEN 1 AND 5 THEN workday-bz ELSE workday END -避免處理后的日期停留在非工作日上 WHILE (DATEFIRST+DATEPART(Weekday,date)-1)%7 i