SQL數(shù)據(jù)操作基礎(chǔ)(中級(jí))  10

2019-11-18 22:16:42

字體：大中小

供稿：網(wǎng)友

操作日期和時(shí)間

日期和時(shí)間函數(shù)對(duì)建立一個(gè)站點(diǎn)是非常有用的。站點(diǎn)的主人往往對(duì)一個(gè)表中的數(shù)據(jù)何時(shí)被更新感興趣。通過(guò)日期和時(shí)間函數(shù)，你可以在毫秒級(jí)跟蹤一個(gè)表的改變。

返回當(dāng)前日期和時(shí)間

通過(guò)函數(shù)GETDATE（），你可以獲得當(dāng)前的日期和時(shí)間。例如，語(yǔ)句SELECT GETDATE()返回如下的結(jié)果：

……………………………..

NOV 30 1997 3:29AM

(1 row(s) affected)

顯然，如果你將來(lái)使用這個(gè)函數(shù)，你得到的日期將比這個(gè)時(shí)間晚，或者梗早。

函數(shù)GETDATE（）可以用來(lái)作為DATEDIME（）型字段的缺省值。這對(duì)插入記錄時(shí)保存當(dāng)時(shí)的時(shí)間是有用的。例如，假設(shè)有一個(gè)表用來(lái)保存你站點(diǎn)上的活動(dòng)日志。每當(dāng)有一個(gè)訪問(wèn)者訪問(wèn)到你的站點(diǎn)時(shí)，就在表中添加一條新記錄，記下訪問(wèn)者的名字，活動(dòng)，和進(jìn)行訪問(wèn)的時(shí)間。要建立一個(gè)表，其中的記錄包含有當(dāng)前的日期和時(shí)間，可以添加一個(gè)DATETIME型字段，指定其缺省值為函數(shù)GETDATE（）的返回值，就象這樣：

CREATE TABLE site_log (

username VARCHAR(40),

useractivity VARCHAR(100),

entrydate DATETIME DEFAULT GETDATE())

轉(zhuǎn)換日期和時(shí)間

你也許已經(jīng)注意到，在上一節(jié)的例子中，函數(shù)GETDATE（）的返回值在顯示時(shí)只顯示到秒。實(shí)際上，SQL Sever內(nèi)部時(shí)間可以精確到毫秒級(jí)（確切地說(shuō)，可以精確到3.33毫秒）。

要得到不同格式的日期和時(shí)間，你需要使用函數(shù)CONVERT（）。例如，當(dāng)下面的這個(gè)語(yǔ)句執(zhí)行時(shí)，顯示的時(shí)間將包括毫秒：

SELECT CONVERT(VARCHAR(30),GETDATE(),9)

注意例子中數(shù)字9的使用。這個(gè)數(shù)字指明了在顯示日期和時(shí)間時(shí)使用哪種日期和時(shí)間格式。當(dāng)這個(gè)語(yǔ)句執(zhí)行時(shí)，將顯示如下的日期和時(shí)間：

…………………………………..

Nov 30 1997 3:29:55:170AM

(1 row(s) affected)

在函數(shù)CONVERT（）中你可以使用許多種不同風(fēng)格的日期和時(shí)間格式。表11.1顯示了所有的格式。

表11.1 日期和時(shí)間的類型

類型值標(biāo)準(zhǔn) 輸出

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總是返回四位的年。對(duì)其它類型，要顯示世紀(jì)，把style值加上100。類型13和14返回24小時(shí)時(shí)鐘的時(shí)間。類型0，7，和13返回的月份用三位字符表示（用Nov代表November）.

對(duì)表11.1中所列的每一種格式，你可以把類型值加上100來(lái)顯示有世紀(jì)的年（例如，00年將顯示為2000年）。例如，要按日本標(biāo)準(zhǔn)顯示日期，包括世紀(jì)，你應(yīng)使用如下的語(yǔ)句：

SELECT CONVERT（VARCHAR（30），GETDATE（），111）

在這個(gè)例子中，函數(shù)CONVERT（）把日期格式進(jìn)行轉(zhuǎn)換，顯示為1997/11/30

抽取日期和時(shí)間

在許多情況下，你也許只想得到日期和時(shí)間的一部分，而不是完整的日期和時(shí)間。例如，假設(shè)你想列出你的站點(diǎn)目錄中每個(gè)站點(diǎn)被查詢的月份。這時(shí)你不希望完整的日期和時(shí)間把網(wǎng)頁(yè)弄亂。為了抽取日期的特定部分，你可以使用函數(shù)DATEPART（），象這樣：

SELECT site_name ‘Site Name’,

DATEPART(mm,site_entrydate) ‘Month Posted’ FROM site_directory

函數(shù)DATEPART（）的參數(shù)是兩個(gè)變量。第一個(gè)變量指定要抽取日期的哪一部分；第二個(gè)變量是實(shí)際的數(shù)據(jù)。在這個(gè)例子中，函數(shù)DATEPART（）抽取月份，因?yàn)閙m代表月份。下面是這個(gè)SELECT 語(yǔ)句的輸出結(jié)果：

Site Name Month Posted

………………………………………………………………

Yahoo 2

Microsoft 5

Magicw3 5

(3 row(s) affected)

Month Posted列顯示了每個(gè)站點(diǎn)被查詢的月份。函數(shù)DATEPART（）的返回值是一個(gè)整數(shù)。你可以用這個(gè)函數(shù)抽取日期的各個(gè)不同部分，如表11.2所示。

表11.2 日期的各部分及其簡(jiǎn)寫(xiě)

日期部分簡(jiǎn)寫(xiě) 值

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

當(dāng)你需要進(jìn)行日期和時(shí)間的比較時(shí)，使用函數(shù)DATEPART（）返回整數(shù)是有用的。但是，上例中的查詢結(jié)果（2，5）不是十分易讀。要以更易讀的格式得到部分的日期和時(shí)間，你可以使用函數(shù)DATENAME（），如下例所示：

SELECT site_name ‘Site Name’

DATENAME(mm,site_entrydate) ‘Month Posted’

FROM site_directory

函數(shù)DATENAME（）和函數(shù)DATEPART（）接收同樣的參數(shù)。但是，它的返回值是一個(gè)字符串，而不是一個(gè)整數(shù)。下面是上例該用DATENAME（）得到的結(jié)果：

Site Name Month Postec

………………………………………………………………….

Yahoo February

Microsoft June

Magicw3 June

(3 row(s) affected)

你也可以用函數(shù)DATENAE（）來(lái)抽取一個(gè)星期中的某一天。下面的這個(gè)例子同時(shí)抽取一周中的某一天和日期中的月份：

SELECT site_name ‘Site Name’,

DATENAME(dw,site_entrydate)+ ‘-’ + DATENAME（mm,site_entrydate）

‘Day and Month Posted’ FORM site_directory

這個(gè)例子執(zhí)行時(shí)，將返回如下的結(jié)果：

Site Name Day and Month Posted

………………………………………………………………………

Yahoo Friday - February

Microsoft Tuesday - June

Magicw3 Monday - June

(3 row(s) affected)

返回日期和時(shí)間范圍

當(dāng)你分析表中的數(shù)據(jù)時(shí)，你也許希望取出某個(gè)特定時(shí)間的數(shù)據(jù)。你也許對(duì)特定的某一天中――比如說(shuō)2000年12月25日――訪問(wèn)者在你站點(diǎn)上的活動(dòng)感興趣。要取出這種類型的數(shù)據(jù)，你也許會(huì)試圖使用這樣的SELECT語(yǔ)句：

SELECT * FROM weblog WHERE entrydate=”12/25/20000”

不要這樣做。這個(gè)SELECT語(yǔ)句不會(huì)返回正確的記錄――它將只返回日期和時(shí)間是12/25/2000 12:00:00:000AM的記錄。換句話說(shuō)，只有剛好在午夜零點(diǎn)輸入的記錄才被返回。

注意：

在本節(jié)的討論中，假設(shè)字段entrydate是DATETIME型，而不是SMALLDATETIME型。本節(jié)的討論對(duì)SMALLDATETIME型字段也是適用的，不過(guò)SMALLDATETIME型字段只能精確到秒。

問(wèn)題是SQL Sever將用完整的日期和時(shí)間代替部分日期和時(shí)間。例如，當(dāng)你輸入一個(gè)日期，但不輸入時(shí)間時(shí)，SQL Sever將加上缺省的時(shí)間“12：00：00：000AM”。當(dāng)你輸入一個(gè)時(shí)間，但不輸入日期時(shí)，SQL Sever將加上缺省的日期“Jan 1 1900”。

要返回正確的記錄，你需要適用日期和時(shí)間范圍。有不止一種途徑可以做到這一點(diǎn)。例如，下面的這個(gè)SELECT 語(yǔ)句將能返回正確的記錄：

SELECT * FROM weblog

WHERE entrydate>=”12/25/2000” AND entrydate<”12/26/2000”

這個(gè)語(yǔ)句可以完成任務(wù)，因?yàn)樗x取的是表中的日期和時(shí)間大于等于12/25/2000 12:00:00:000AM并小于12/26/2000 12:00:00:000AM的記錄。換句話說(shuō)，它將正確地返回2000年圣誕節(jié)這一天輸入的每一條記錄。

另一種方法是，你可以使用LIKE來(lái)返回正確的記錄。通過(guò)在日期表達(dá)式中包含通配符“％”，你可以匹配一個(gè)特定日期的所有時(shí)間。這里有一個(gè)例子：

SELECT * FROM weblog WHERE entrydate LIKE ‘Dec 25 2000%’

這個(gè)語(yǔ)句可以匹配正確的記錄。因?yàn)橥ㄅ浞埃ァ贝砹巳魏螘r(shí)間。

使用這兩種匹配日期和時(shí)間范圍的函數(shù)，你可以選擇某個(gè)月，某一天，某一年，某個(gè)小時(shí)，某一分鐘，某一秒，甚至某一毫秒內(nèi)輸入的記錄。但是，如果你使用LIKE 來(lái)匹配秒或毫秒，你首先需要使用函數(shù)CONVERT（）把日期和時(shí)間轉(zhuǎn)換為更精確的格式（參見(jiàn)前面“轉(zhuǎn)換日期和時(shí)間”一節(jié)）。

比較日期和時(shí)間

最后，還有兩個(gè)日期和時(shí)間函數(shù)對(duì)根據(jù)日期和時(shí)間取出記錄是有用的。使用函數(shù)DATEADD（）和DATEDIFF（），你可以比較日期的早晚。例如，下面的SELECT語(yǔ)句將顯示表中的每一條記錄已經(jīng)輸入了多少個(gè)小時(shí)：

SELECT entrydate ‘Time Entered’

DATEDIFF(hh,entrydate,GETDATE()) ‘Hours Ago’ FROM weblog

如果當(dāng)前時(shí)間是2000年11月30號(hào)下午6點(diǎn)15分，則會(huì)返回如下的結(jié)果：

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)

函數(shù)DADEDIFF（）的參數(shù)是三個(gè)變量。第個(gè)變量指定日期的某一部分。在這個(gè)例子中，是按小時(shí)對(duì)日期進(jìn)行比較，（要了解日期各部分的詳細(xì)內(nèi)容，請(qǐng)參考表11.2）在日期2000年11月1日和2000年11月30日的指定時(shí)間之間有689個(gè)小時(shí)。另外兩個(gè)參數(shù)是要進(jìn)行比較的時(shí)間。為了返回一個(gè)正數(shù)，較早的時(shí)間應(yīng)該先給。

函數(shù)DATEADD（）把兩個(gè)日期相加。當(dāng)你需要計(jì)算截止日期這一類的數(shù)據(jù)時(shí)，這個(gè)函數(shù)是有用處的。例如，假設(shè)訪問(wèn)者必須先注冊(cè)才能使用你的站點(diǎn)。注冊(cè)以后，他們可以免費(fèi)使用你的站點(diǎn)一個(gè)月。要確定什么時(shí)候他們的免費(fèi)時(shí)間會(huì)用完，你可以使用如下的SELECT語(yǔ)句：

SELECT username ‘User Name’,

DATEADD(mm,1,firstvisit_date) ‘Registration Expires’

FROM registration_table

函數(shù)DATEADD（）的參數(shù)有三個(gè)變量。第一個(gè)變量代表日期的某一部分（參見(jiàn)表11.2），這個(gè)例子用到了代表月份的mm。第二個(gè)變量指定了時(shí)間的間隔――在本例中是一個(gè)月。最后一個(gè)變量是一個(gè)日期，在這個(gè)例子中，日期是取自DATETIME型字段firstvisit_date.假設(shè)當(dāng)前日期是June 30,2000，這個(gè)語(yǔ)句將返回如下的內(nèi)容：

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)

注意：

與你預(yù)料的相反，使用函數(shù)DATEADD（）把一個(gè)日期加上一個(gè)月，它并不加上30天。這個(gè)函數(shù)只簡(jiǎn)單地把月份值加1。這意味著在11月注冊(cè)的人將比在2月注冊(cè)的人多得到2天或3天的時(shí)間。要避免這個(gè)問(wèn)題，你可以用函數(shù)DATEADD（）直接增加天數(shù)，而不是月份。

發(fā)送郵件

你可以用SQL Sever發(fā)送簡(jiǎn)單的e_mail信息。要做到這一點(diǎn)，你需要在你的系統(tǒng)中安裝郵件服務(wù)器，如Microsoft Exchange Sever(參見(jiàn)第四章“Exchange Active Sever,Index Sever,和NetShow”)。你還需要配置SQL Sever 以識(shí)別郵件服務(wù)器。

要讓SQL Sever 能識(shí)別郵件服務(wù)器，啟動(dòng)事務(wù)管理器并從菜單中選擇Sever|SQL Mail|Configue，這時(shí)會(huì)出現(xiàn)一個(gè)如圖11.3所示的對(duì)話框。輸入你在郵件服務(wù)器中注冊(cè)的用戶名和口令，然后單擊OK。

注意：

如果你使用Microsoft Exchange Sever,配置SQL Sever的過(guò)程將會(huì)大大不同。你需要在同一個(gè)（域）用戶帳號(hào)下運(yùn)行Microsoft SQL Sever和Exchange Sever。你還需要在安裝了SQL Sever的機(jī)器上安裝Exchange Cliect并給這個(gè)帳號(hào)創(chuàng)建一個(gè)一個(gè)配置文件。完成這些之后，你就可以在SQL Mail Configuration對(duì)話框中輸入該配置文件的名字。

圖11。3

在發(fā)送郵件之前，你要先啟動(dòng)SQL Mail。從菜單中選擇Sever|SQL Mail|Start。如果你的郵件服務(wù)器配置正確，并且你輸入了正確的用戶名和口令，則SQL Mail會(huì)成功啟動(dòng)。

注意：

你可以把SQL Sever配置為自動(dòng)啟動(dòng)郵件服務(wù)。要做到這一點(diǎn)，在Set Sever Optons對(duì)話框（從菜單中選擇Sever|SQL Sever|Configure）中選擇Auto Start Mail Client即可。

要發(fā)送一個(gè)郵件，你可以使用名為xp_sendmail的擴(kuò)展存儲(chǔ)過(guò)程。這里有一個(gè)如何使用這個(gè)過(guò)程的例子：

master..xp_sendmail “president@whitehouse.gov”,”Hello Mr. President”

這個(gè)過(guò)程調(diào)用向e_mail地址president@whitehouse.gov發(fā)送一個(gè)簡(jiǎn)單的email信息：“Hello Mr. President”。你可以用任何其它的email地址和信息取代上例中相應(yīng)的內(nèi)容，但是，你所發(fā)送的信息不能超過(guò)255個(gè)字符長(zhǎng)度。

當(dāng)你想隨時(shí)了解你的站點(diǎn)數(shù)據(jù)庫(kù)的狀態(tài)時(shí)，存儲(chǔ)過(guò)程xp_sendmail是有用處的。例如，你可以向一個(gè)頁(yè)面管理程序發(fā)送信息。如果你的站點(diǎn)出了什么問(wèn)題，你馬上就可以知道。下一章將講述更多有關(guān)存儲(chǔ)過(guò)程的內(nèi)容。

總結(jié)

這一章加深了你的SQL知識(shí)。你學(xué)會(huì)了如何建立索引，使你的查詢速度更快。你還學(xué)會(huì)了如何插入，刪除和更新一個(gè)表中的數(shù)據(jù)，如何使用集合函數(shù)得到一個(gè)表中數(shù)據(jù)的統(tǒng)計(jì)信息。最后，你學(xué)會(huì)了許多有價(jià)值的表達(dá)式，函數(shù)和過(guò)程，用來(lái)操作字符串，日期和時(shí)間及郵件。

下一章將進(jìn)一步加深你對(duì)Microsoft SQL Sever的掌握。你將學(xué)習(xí)如何用SQL來(lái)進(jìn)行程序設(shè)計(jì)，如何建立存儲(chǔ)過(guò)程，觸發(fā)器和執(zhí)行計(jì)劃。更另人興奮的是，你將學(xué)會(huì)讓SQL Sever自動(dòng)創(chuàng)建網(wǎng)頁(yè)的一個(gè)簡(jiǎn)單方法。

上一篇：數(shù)據(jù)庫(kù)聊天室的“無(wú)刷新”技術(shù)要點(diǎn)

下一篇：SQL數(shù)據(jù)操作基礎(chǔ)(中級(jí))  9