SQL データ操作の基礎 (中級) 10

日付と時刻の操作

日付と時刻の関数は、Web サイトの構築に非常に役立ちます。サイト所有者は、テーブル内のデータがいつ更新されるかに関心を持つことがよくあります。日付と時刻の関数を使用すると、テーブルへの変更をミリ秒レベルで追跡できます。

現在の日付と時刻を返す

関数GETDATE()を通じて、現在の日付と時刻を取得できます。たとえば、ステートメント SELECT GETDATE() は次の結果を返します:

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

NOV 30 1997 3:29AM

(1 行 影響を受けます)

明らかに、今後この関数を使用すると、取得される日付は今回よりも遅くなるか、それより早くなります。

関数GETDATE()は、DATEDIME()型フィールドのデフォルト値として使用できます。これは、レコードを挿入するときに現在時刻を保存するのに役立ちます。たとえば、サイト上のアクティビティのログを保持するテーブルがあるとします。訪問者がサイトにアクセスするたびに、テーブルに新しいレコードを追加して、訪問者の名前、アクティビティ、訪問時間を記録します。現在の日付と時刻をレコードに含むテーブルを作成するには、次のように DATETIME フィールドを追加し、そのデフォルト値が GETDATE() 関数の戻り値であるように指定できます:

CREATE TABLE site_log (

ユーザー名 VARCHAR(40),

ユーザーアクティビティ VARCHAR(100),

entrydate DATETIME DEFAULT GETDATE())

日付と時刻を変換する

前のセクションの例では、関数 GETDATE() の戻り値が秒までしか表示されていないことに気づいたかもしれません。実際、SQL サーバーの内部時間はミリ秒レベルまで正確です (正確には、3.33 ミリ秒まで正確です)。

さまざまな形式で日付と時刻を取得するには、関数 CONVERT() を使用する必要があります。たとえば、次のステートメントが実行されると、表示される時間にはミリ秒が含まれます:

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

この例では数値 9 が使用されていることに注意してください。この数値は、日付と時刻を表示するときに使用する日付と時刻の形式を指定します。このステートメントが実行されると、次の日付と時刻が表示されます:

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

Nov 1997 年 30 月 3:29:55:170AM

(1 行) 影響を受けます)

関数 CONVERT() では、さまざまなスタイルの日付と時刻の形式を使用できます。表 11.1 にすべてのフォーマットを示します。

表11.1 日付と時刻のタイプ

タイプ値 標準出力

0 デフォルト mon dd yyyy hh:miAM

1 USA mm/dd/yy

2 ANSI yy.mm.dd

3 イギリス/フランス語 dd/mm/yy

4 ドイツ語 dd.mm.yy

5 イタリア語 dd-mm-yy

6 - dd mon yy

7 - mon dd,yy

8 - hh:mi:ss

デフォルト + ミリ秒 -- 月 dd yyyy
hh:mi:ss:mmmAM(または )

10 USA mm-dd-yy

11 日本 yy/mm/dd

12 ISO yymmdd

13 ヨーロッパ デフォルト + ミリ秒--dd mon yyyy

hh:mi:ss:mmm(24h)

14 - hh:mi:ss:mmm(24h)

タイプ 0、9、および 13 は常に 4 桁の年を返します。他のタイプの場合、世紀を表示するには、スタイル値に 100 を追加します。タイプ 13 および 14 は、24 時間制の時刻を返します。タイプ 0、7、および 13 は、月を 3 文字の表現として返します (11 月の場合は Nov を使用します)。

表 11.1 にリストされている各形式について、タイプの値に 100 を追加して、年を世紀とともに表示できます。 、00 年は 2000 年として表示されます)。たとえば、世紀を含む日本の標準に従って日付を表示するには、次のステートメントを使用します:

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

この例では、関数 CONVERT() が日付形式を変換して 1997/11/30 と表示します

日付と時刻を抽出します

多くの場合、完全な日付と時刻ではなく、日付と時刻の一部のみを取得したい場合があります。たとえば、サイト ディレクトリ内の各サイトがクエリされた月を一覧表示するとします。この時点では、完全な日付と時刻でページが乱雑になることは望ましくありません。日付の特定の部分を抽出するには、次のように関数 DATEPART() を使用できます:

SELECT site_name ‘サイト名’,

DATEPART(mm,site_entrydate) ‘投稿月’ FROM site_directory

関数 DATEPART() のパラメーターは 2 つの変数です。最初の変数は、日付のどの部分を抽出するかを指定します。2 番目の変数は実際のデータです。この例では、mm が月を表すため、関数 DATEPART() によって月が抽出されます。以下はこのSELECTです ステートメントの出力結果:

サイト名 Month 投稿

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

Yahoo 2

Microsoft 5

Magicw3 5

(3行が影響を受ける)

月 投稿列には、各サイトがクエリされた月が表示されます。関数 DATEPART() の戻り値は整数です。表 11.2 に示すように、この関数を使用して日付のさまざまな部分を抽出できます。

表11.2 日付の一部とその略語

日付部分の略語値

年 yy 1753--999​​9

四半期 QQ 1--4

月 mm 1--12

日 dy 1--366

日 dd 1--31

週 1--53

平日 dw 1--7(日曜日--土曜日)

時 0--23

分 mi 0--59

秒 ss 0--59

ミリ秒 ms 0--999​​

日付と時刻を比較する必要がある場合、関数 DATEPART() を使用して整数を返すと便利です。ただし、上記の例のクエリ結果 (2、5) はあまり読みやすくありません。日付と時刻の一部を読みやすい形式で取得するには、次の例に示すように、関数 DATENAME() を使用できます。 site_name ‘サイト名’

DATENAME(mm,site_entrydate) ‘月 投稿しました』

から site_directory

関数 DATENAME() と関数 DATEPART() は同じパラメーターを受け取ります。ただし、戻り値は整数ではなく文字列です。以下は、上記の例で DATENAME() を使用して取得された結果です。 名前 月 Postec

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

Yahoo 2月

マイクロソフト6月

Magicw3 6月

(3 row(s) 影響を受けます)

関数 DATENAE() を使用して曜日を抽出することもできます。次の例では、日付の曜日と月の両方を抽出します。 site_name ‘サイト名’,

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

「投稿日と月」FORM site_directory

この例を実行すると、次の結果が返されます:

サイト名 日と月 投稿しました

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

Yahoo Friday - 2月

マイクロソフト火曜日 - 6月

Magicw3月曜日 - 6月

(3 行 影響を受けます)

日付と時刻の範囲を返します

テーブル内のデータを分析するとき、特定の時間のデータを取得したい場合があります。あなたは、特定の日 (たとえば、2000 年 12 月 25 日) のサイトの訪問者のアクティビティに興味があるかもしれません。このタイプのデータを取得するには、次のような SELECT ステートメントを使用してみてください:

SELECT * ウェブログのどこから entrydate=”12/25/20000”

これはやめてください。この SELECT ステートメントは正しいレコードを返しません。2000 年 12 月 25 日の日付と時刻のみが返されます。 午前12時00分00秒の記録。つまり、午前 0 時ちょうどに入力されたレコードのみが返されます。

注:

このセクションの説明では、フィールドentrydateの型がSMALLDATETIMEではなくDATETIMEであると想定しています。このセクションの説明は SMALLDATETIME 型フィールドにも当てはまりますが、SMALLDATETIME 型フィールドの精度は秒単位までしかありません。

問題はSQLです サーバーは、部分的な日付と時刻を完全な日付と時刻に置き換えます。たとえば、日付は入力するが時刻は入力しない場合、SQL サーバーはデフォルト時刻「12:00:00:000AM」を追加します。日付ではなく時刻を入力すると、SQL Server はデフォルトの日付「1 月 1 日」を追加します。 1900"。

正しいレコードを返すには、日付と時刻の範囲を適用する必要があります。これを行う方法は複数あります。たとえば、次の SELECT このステートメントは正しいレコードを返します:

SELECT * FROM weblog

WHERE エントリ日付>=”2000/12/25” かつ entrydate<”12/26/2000”

このステートメントは、テーブル内の 2000 年 12 月 25 日以降の日付と時刻を選択するため、タスクを完了できます。 12:00:00:000AM 以降、2000 年 12 月 26 日未満 午前12時00分00秒の記録。つまり、2000 年のクリスマスの日に入力されたすべてのレコードが正しく返されます。

あるいは、LIKE を使用して正しいレコードを返すこともできます。日付式にワイルドカード文字「%」を含めることにより、特定の日付のすべての時間を照合できます。以下に例を示します:

SELECT * ウェブログ WHERE エントリー日付 LIKE ‘12 月 25 日から 2000%'

このステートメントは正しいレコードと一致する可能性があります。ワイルドカード「%」は任意の時間を表すためです。

日付と時刻の範囲を照合するこれら 2 つの関数を使用すると、特定の月、日、年、時、分、秒、さらにはミリ秒以内に入力されたレコードを選択できます。しかし、「LIKE」を使用すると、 秒またはミリ秒を一致させるには、まず CONVERT() 関数を使用して日付と時刻をより正確な形式に変換する必要があります (前のセクション「日付と時刻の変換」を参照)。

日付と時刻の比較

最後に、日付と時刻に基づいてレコードを取り出すのに便利な日付と時刻関数が 2 つあります。関数 DATEADD() および DATEDIFF() を使用すると、以前の日付と後の日付を比較できます。たとえば、次の SELECT ステートメントは、テーブル内の各レコードが入力された時間を表示します:

SELECT entrydate ‘入力時刻’

DATEDIFF(hh,entrydate,GETDATE()) ‘時間前’ FROM weblog

現在の時刻が 2000 年 11 月 30 日の午後 6 時 15 分である場合、次の結果が返されます:

Time Entered 何時間前

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

2000年12月30日 午後4時09分 2

12月30日 2000 4:13PM 2

2000 年 12 月 1 日 4:09PM 698

(3 行) 影響を受けます)

関数 DADEDIFF() のパラメーターは 3 つの変数です。変数は日付の一部を指定します。この例では、日付は 1 時間ごとに比較されます (日付のさまざまな部分の詳細については、表 11.2 を参照してください)。2000 年 11 月 1 日と 2000 年 11 月 30 日の指定された時刻の間には 689 回の時刻があります。 。 時間。他の 2 つのパラメーターは比較される時間です。正の数値を返すには、早い方の時間を最初に指定する必要があります。

関数 DATEADD() は 2 つの日付を加算します。この機能は、納期などのデータを計算する必要がある場合に便利です。たとえば、訪問者がサイトを使用する前に登録する必要があるとします。サインアップ後、1 か月間無料でサイトを使用できます。自由時間がいつなくなるかを判断するには、次のような SELECT ステートメントを使用できます:

SELECT username ‘ユーザー名’,

DATEADD(mm,1,firstvisit_date) ‘登録 期限切れ

から register_table

関数 DATEADD() のパラメータには 3 つの変数があります。最初の変数は日付の一部を表します (表 11.2 を参照)。この例では、mm は月を表します。 2 番目の変数は時間間隔を指定します (この場合は 1 か月)。この例では、日付は DATETIME 型フィールド firstvisit_date から取得されます。現在の日付は 6 月であると仮定します。 30,2000、このステートメントは次の内容を返します:

ユーザー名の登録 期限切れ

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

ビル・ゲイツ 2000年7月30日 午後4時9分

クリントン大統領 2000年7月30日 4:13PM

ウィリアム・シェイクスピア 2000年7月1日 4:09PM

(3行) 影響を受けます)

注:

ご想像に反して、関数 DATEADD() を使用して日付に月を加算しても、30 日は加算されません。この関数は単純に月の値に 1 を加算します。これは、11 月にサインアップした人は 2 月にサインアップした人よりも 2 ～ 3 日多くの日数が得られることを意味します。この問題を回避するには、関数 DATEADD() を使用して、月の代わりに日を直接追加します。

メールを送信

SQLを使用できます サーバーは単純な電子メール メッセージを送信します。これを行うには、Microsoft Exchange などのメール サーバーがシステムにインストールされている必要があります。 サーバー (第 4 章「Exchange アクティブ サーバー、インデックス サーバー、および NetShow」を参照)。 SQL Serverの設定も必要です メールサーバーを識別します。

SQL Sever にメール サーバーを認識させるには、トランザクション マネージャーを起動し、メニューから [サーバー] | [SQL] を選択します。 「メール|設定」を選択すると、図 11.3 に示すようなダイアログボックスが表示されます。メールサーバーに登録したユーザー名とパスワードを入力し、「OK」をクリックします。

注:

Microsoft を使用している場合 Exchange Sever と SQL Sever を構成するプロセスは大きく異なります。同じ（ドメイン）ユーザー アカウントで Microsoft SQL を実行する必要があります サーバーと交換サーバー。 SQL Sever がインストールされているマシンに Exchange をインストールする必要もあります。 をクリックして、このアカウントの構成ファイルを作成します。これを完了すると、SQL Mail を使用できるようになります。 [構成] ダイアログ ボックスに構成ファイルの名前を入力します。

図 11.3

メールを送信する前に、まず SQL を開始する必要があります 郵便。メニューから「サーバー」→「SQL メール」→「開始」を選択します。メールサーバーが正しく設定されており、正しいユーザー名とパスワードを入力した場合、SQL メールは正常に開始されます。

注:

電子メール サービスを自動的に開始するように SQL Server を構成できます。これを行うには、Set Server で [オプション] ダイアログ ボックスで [メールの自動開始] を選択します (メニューから [サーバー] | [SQL サーバー] | [構成] を選択します)。 ただのクライアント。

電子メールを送信するには、xp_sendmail という拡張ストアド プロシージャを使用できます。このプロセスの使用方法の例を次に示します:

master..xp_sendmail "president@whitehouse.gov","こんにちは。 大統領「

」この手順では、電子メール アドレス President@whitehouse.gov に簡単な電子メール メッセージを送信します。「こんにちは」 氏 上記の例の対応するコンテンツを他の電子メール アドレスや情報に置き換えることができますが、送信する情報の長さは 255 文字を超えることはできません。

いつでもサイト データベースのステータスを知りたい場合は、たとえば、ストアド プロシージャ xp_sendmail は、サイトで問題が発生した場合にメッセージを送信するのに役立ちます。 概要

この章では、SQL の知識を深めます。クエリを高速化するためのインデックスの作成方法と、テーブル内のデータの統計情報を取得するための集計関数の使用方法も学びました。文字列、日付、時刻、電子メールを操作するための多くの貴重な式、関数、プロシージャ

次の章では、Microsoft についての知識がさらに深まります。 SQL Server の習得。 SQL を使用したプログラミング方法と、ストアド プロシージャ、トリガー、実行プランの作成方法を学びます。さらに興味深いのは、SQL の使用方法を学べることです。 サーバーは、Web ページを自動的に作成する簡単な方法です。

上記は SQL データ操作の基礎 (中級) 10 の内容です。その他の関連記事については、PHP 中国語 Web サイト (www.php.cn) に注目してください。