SQL數據操作基礎(中級)10
2024-07-21 02:11:05
供稿：網友
操作日期和時間
日期和時間函數對建立一個站點是非常有用的。站點的主人往往對一個表中的數據何時被更新感興趣。通過日期和時間函數，你可以在毫秒級跟蹤一個表的改變。
返回當前日期和時間
通過函數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自動創建網頁的一個簡單方法。
