程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SQL數據操作基礎(中級) 10

SQL數據操作基礎(中級) 10

編輯:關於SqlServer
操作日期和時間
  
   日期和時間函數對建立一個站點是非常有用的。站點的主人往往對一個表中的數據何時被更新感興趣。通過日期和時間函數,你可以在毫秒級跟蹤一個表的改變。
  
   返回當前日期和時間
  
   通過函數GETDATE(),你可以獲得當前的日期和時間。例如,語句SELECT GETDATE()返回如下的結果:
  
   ……………………………..
  
   NOV 30 1997 3:29AM
  
   (1 row(s) affected)
  
   顯然,如果你將來使用這個函數,你得到的日期將比這個時間晚,或者梗早。
  
   函數GETDATE()可以用來作為DATEDIME()型字段的缺省值。這對插入記錄時保存當時的時間是有用的。例如,假設有一個表用來保存你站點上的活動日志。每當有一個訪問者訪問到你的站點時,就在表中添加一條新記錄,記下訪問者的名字,活動,和進行訪問的時間。要建立一個表,其中的記錄包含有當前的日期和時間,可以添加一個DATETIME型字段,指定其缺省值為函數GETDATE()的返回值,就象這樣:
  
   CREATE TABLE site_log (
  
   username VARCHAR(40),
  
   useractivity VARCHAR(100),
  
   entrydate DATETIME DEFAULT GETDATE())
  
   轉換日期和時間
  
   你也許已經注意到,在上一節的例子中,函數GETDATE()的返回值在顯示時只顯示到秒。實際上,SQL Sever內部時間可以精確到毫秒級(確切地說,可以精確到3.33毫秒)。
  
   要得到不同格式的日期和時間,你需要使用函數CONVERT()。例如,當下面的這個語句執行時,顯示的時間將包括毫秒:
  
   SELECT CONVERT(VARCHAR(30),GETDATE(),9)
  
   注意例子中數字9的使用。這個數字指明了在顯示日期和時間時使用哪種日期和時間格式。當這個語句執行時,將顯示如下的日期和時間:
  
   …………………………………..
  
   Nov 30 1997 3:29:55:170AM
  
   (1 row(s) affected)
  
   在函數CONVERT()中你可以使用許多種不同風格的日期和時間格式。表11.1顯示了所有的格式。
  
   表11.1 日期和時間的類型
  
   類型值 標准 輸出
  
   0 Default mon dd yyyy hh:miAM
  
   1 USA mm/dd/yy
  
   2 ANSI yy.mm.dd
  
   3 British/French dd/mm/yy
  
   4 German dd.mm.yy
  
   5 Italian dd-mm-yy
  
   6 - dd mon yy
  
   7 - mon dd,yy
  
   8 - hh:mi:ss
  
   Default + milliseconds--mon dd yyyy
   hh:mi:ss:mmmAM(or )
  
   10 USA mm-dd-yy
  
   11 JAPAN yy/mm/dd
  
   12 ISO yymmdd
  
   13 Europe Default + milliseconds--dd mon yyyy
  
   hh:mi:ss:mmm(24h)
  
   14 - hh:mi:ss:mmm(24h)
  
   類型0,9,和13總是返回四位的年。對其它類型,要顯示世紀,把style值加上100。類型13和14返回24小時時鐘的時間。類型0,7,和13返回的月份用三位字符表示(用Nov代表November).
  
   對表11.1中所列的每一種格式,你可以把類型值加上100來顯示有世紀的年(例如,00年將顯示為2000年)。例如,要按日本標准顯示日期,包括世紀,你應使用如下的語句:
  
   SELECT CONVERT(VARCHAR(30),GETDATE(),111)
  
   在這個例子中,函數CONVERT()把日期格式進行轉換,顯示為1997/11/30
  
   抽取日期和時間
  
   在許多情況下,你也許只想得到日期和時間的一部分,而不是完整的日期和時間。例如,假設你想列出你的站點目錄中每個站點被查詢的月份。這時你不希望完整的日期和時間把網頁弄亂。為了抽取日期的特定部分,你可以使用函數DATEPART(),象這樣:
  
   SELECT site_name ‘Site Name’,
  
   DATEPART(mm,site_entrydate) ‘Month Posted’ FROM site_directory
  
   函數DATEPART()的參數是兩個變量。第一個變量指定要抽取日期的哪一部分;第二個變量是實際的數據。在這個例子中,函數DATEPART()抽取月份,因為mm代表月份。下面是這個SELECT 語句的輸出結果:
  
   Site Name Month Posted
  
   ………………………………………………………………
  
   Yahoo 2
  
   Microsoft 5
  
   Magicw3 5
  
   (3 row(s) affected)
  
   Month Posted列顯示了每個站點被查詢的月份。函數DATEPART()的返回值是一個整數。你可以用這個函數抽取日期的各個不同部分,如表11.2所示。
  
   表11.2 日期的各部分及其簡寫
  
   日期部分 簡寫 值
  
   year yy 1753--9999
  
   quarter qq 1--4
  
   month mm 1--12
  
   day of year dy 1--366
  
   day dd 1--31
  
   week wk 1--53
  
   weekday dw 1--7(Sunday--Saturday)
  
   hour hh 0--23
  
   minute mi 0--59
  
   second ss 0--59
  
   milisecond ms 0--999
  
   當你需要進行日期和時間的比較時,使用函數DATEPART()返回整數是有用的。但是,上例中的查詢結果(2,5)不是十分易讀。要以更易讀的格式得到部分的日期和時間,你可以使用函數DATENAME(),如下例所示:
  
   SELECT site_name ‘Site Name’
  
   DATENAME(mm,site_entrydate) ‘Month Posted’
  
   FROM site_directory
  
   函數DATENAME()和函數DATEPART()接收同樣的參數。但是,它的返回值是一個字符串,而不是一個整數。下面是上例該用DATENAME()得到的結果:
  
   Site Name Month Postec
  
   ………………………………………………………………….
  
   Yahoo February
  
   Microsoft June
  
   Magicw3 June
  
   (3 row(s) affected)
  
   你也可以用函數DATENAE()來抽取一個星期中的某一天。下面的這個例子同時抽取一周中的某一天和日期中的月份:
  
   SELECT site_name ‘Site Name’,
  
   DATENAME(dw,site_entrydate)+ ‘-’ + DATENAME(mm,site_entrydate)
  
   ‘Day and Month Posted’ FORM site_directory
  
   這個例子執行時,將返回如下的結果:
  
   Site Name Day and Month Posted
  
   ………………………………………………………………………
  
   Yahoo Friday - February
  
   Microsoft Tuesday - June
  
   Magicw3 Monday - June
  
   (3 row(s) affected)
  
   返回日期和時間范圍
  
   當你分析表中的數據時,你也許希望取出某個特定時間的數據。你也許對特定的某一天中――比如說2000年12月25日――訪問者在你站點上的活動感興趣。要取出這種類型的數據,你也許會試圖使用這樣的SELECT語句:
  
   SELECT * FROM weblog WHERE entrydate=”12/25/20000”
  
   不要這樣做。這個SELECT語句不會返回正確的記錄――它將只返回日期和時間是12/25/2000 12:00:00:000AM的記錄。換句話說,只有剛好在午夜零點輸入的記錄才被返回。
  
   注意:
  
   在本節的討論中,假設字段entrydate是DATETIME型,而不是SMALLDATETIME型。本節的討論對SMALLDATETIME型字段也是適用的,不過SMALLDATETIME型字段只能精確到秒。
  
   問題是SQL Sever將用完整的日期和時間代替部分日期和時間。例如,當你輸入一個日期,但不輸入時間時,SQL Sever將加上缺省的時間“12:00:00:000AM”。當你輸入一個時間,但不輸入日期時,SQL Sever將加上缺省的日期“Jan 1 1900”。
  
   要返回正確的記錄,你需要適用日期和時間范圍。有不止一種途徑可以做到這一點。例如,下面的這個SELECT 語句將能返回正確的記錄:
  
   SELECT * FROM weblog
  
   WHERE entrydate>=”12/25/2000” AND entrydate<”12/26/2000”
  
   這個語句可以完成任務,因為它選取的是表中的日期和時間大於等於12/25/2000 12:00:00:000AM並小於12/26/2000 12:00:00:000AM的記錄。換句話說,它將正確地返回2000年聖誕節這一天輸入的每一條記錄。
  
   另一種方法是,你可以使用LIKE來返回正確的記錄。通過在日期表達式中包含通配符“%”,你可以匹配一個特定日期的所有時間。這裡有一個例子:
  
   SELECT * FROM weblog WHERE entrydate LIKE ‘Dec 25 2000%’
  
   這個語句可以匹配正確的記錄。因為通配符“%”代表了任何時間。
  
   使用這兩種匹配日期和時間范圍的函數,你可以選擇某個月,某一天,某一年,某個小時,某一分鐘,某一秒,甚至某一毫秒內輸入的記錄。但是,如果你使用LIKE 來匹配秒或毫秒,你首先需要使用函數CONVERT()把日期和時間轉換為更精確的格式(參見前面“轉換日期和時間”一節)。
  
   比較日期和時間
  
   最後,還有兩個日期和時間函數對根據日期和時間取出記錄是有用的。使用函數DATEADD()和DATEDIFF(),你可以比較日期的早晚。例如,下面的SELECT語句將顯示表中的每一條記錄已經輸入了多少個小時:
  
   SELECT entrydate ‘Time Entered’
  
   DATEDIFF(hh,entrydate,GETDATE()) ‘Hours Ago’ FROM weblog
  
   如果當前時間是2000年11月30號下午6點15分,則會返回如下的結果:
  
   Time Entered Hours Ago
  
   …………………………………………………..
  
   Dec 30 2000 4:09PM 2
  
   Dec 30 2000 4:13PM 2
  
   Dec 1 2000 4:09PM 698
  
   (3 row(s) affected)
  
   函數DADEDIFF()的參數是三個變量。第個變量指定日期的某一部分。在這個例子中,是按小時對日期進行比較,(要了解日期各部分的詳細內容,請參考表11.2)在日期2000年11月1日和2000年11月30日的指定時間之間有689個小時。另外兩個參數是要進行比較的時間。為了返回一個正數,較早的時間應該先給。
  
   函數DATEADD()把兩個日期相加。當你需要計算截止日期這一類的數據時,這個函數是有用處的。例如,假設訪問者必須先注冊才能使用你的站點。注冊以後,他們可以免費使用你的站點一個月。要確定什麼時候他們的免費時間會用完,你可以使用如下的SELECT語句:
  
   SELECT username ‘User Name’,
  
   DATEADD(mm,1,firstvisit_date) ‘Registration Expires’
  
   FROM registration_table
  
   函數DATEADD()的參數有三個變量。第一個變量代表日期的某一部分(參見表11.2),這個例子用到了代表月份的mm。第二個變量指定了時間的間隔――在本例中是一個月。最後一個變量是一個日期,在這個例子中,日期是取自DATETIME型字段firstvisit_date.假設當前日期是June 30,2000,這個語句將返回如下的內容:
  
   User Name Registration Expires
  
   ……………………………………………………………………………
  
   Bill Gates Jul 30 2000 4:09PM
  
   President Clinton Jul 30 2000 4:13PM
  
   William Shakespeare Jul 1 2000 4:09PM
  
   (3 row(s) affected)
  
   注意:
  
   與你預料的相反,使用函數DATEADD()把一個日期加上一個月,它並不加上30天。這個函數只簡單地把月份值加1。這意味著在11月注冊的人將比在2月注冊的人多得到2天或3天的時間。要避免這個問題,你可以用函數DATEADD()直接增加天數,而不是月份。
  
   發送郵件
  
   你可以用SQL Sever發送簡單的e_mail信息。要做到這一點,你需要在你的系統中安裝郵件服務器,如Microsoft Exchange Sever(參見第四章“Exchange Active Sever,Index Sever,和NetShow”)。你還需要配置SQL Sever 以識別郵件服務器。
  
   要讓SQL Sever 能識別郵件服務器,啟動事務管理器並從菜單中選擇Sever|SQL Mail|Configue,這時會出現一個如圖11.3所示的對話框。輸入你在郵件服務器中注冊的用戶名和口令,然後單擊OK。
  
   注意:
  
   如果你使用Microsoft Exchange Sever,配置SQL Sever的過程將會大大不同。你需要在同一個(域)用戶帳號下運行Microsoft SQL Sever和Exchange Sever。你還需要在安裝了SQL Sever的機器上安裝Exchange Cliect並給這個帳號創建一個一個配置文件。完成這些之後,你就可以在SQL Mail Configuration對話框中輸入該配置文件的名字。
  
   圖11。3
  
   在發送郵件之前,你要先啟動SQL Mail。從菜單中選擇Sever|SQL Mail|Start。如果你的郵件服務器配置正確,並且你輸入了正確的用戶名和口令,則SQL Mail會成功啟動。
  
   注意:
  
   你可以把SQL Sever配置為自動啟動郵件服務。要做到這一點,在Set Sever Optons對話框(從菜單中選擇Sever|SQL Sever|Configure)中選擇Auto Start Mail Client即可。
  
   要發送一個郵件,你可以使用名為xp_sendmail的擴展存儲過程。這裡有一個如何使用這個過程的例子:
  
   master..xp_sendmail “[email protected]”,”Hello Mr. President”
  
   這個過程調用向e_mail地址[email protected]發送一個簡單的email信息:“Hello Mr. President”。你可以用任何其它的email地址和信息取代上例中相應的內容,但是,你所發送的信息不能超過255個字符長度。
  
   當你想隨時了解你的站點數據庫的狀態時,存儲過程xp_sendmail是有用處的。例如,你可以向一個頁面管理程序發送信息。如果你的站點出了什麼問題,你馬上就可以知道。下一章將講述更多有關存儲過程的內容。
  
   總結
  
   這一章加深了你的SQL知識。你學會了如何建立索引,使你的查詢速度更快。你還學會了如何插入,刪除和更新一個表中的數據,如何使用集合函數得到一個表中數據的統計信息。最後,你學會了許多有價值的表達式,函數和過程,用來操作字符串,日期和時間及郵件。
  
   下一章將進一步加深你對Microsoft SQL Sever的掌握。你將學習如何用SQL來進行程序設計,如何建立存儲過程,觸發器和執行計劃。更另人興奮的是,你將學會讓SQL Sever自動創建網頁的一個簡單方法。
  
 
  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved