【SQL】クエリーの結果がNULLの場合に特定の値を返す。

--COALESCE関数は可変長の引数をとり、最初のNULL以外の値を返す。
--MSAccess以外の大抵のデータベースで使える。
--次の例の場合、T_TEST_TABLEにデータが無い場合、MAX(NUM)の結果は
--NULLになるが、COALESCE関数の第二引数に指定されている0が返される。
SELECT COALESCE(MAX(NUM),0) FROM T_TEST_TABLE
WHERE CategoryID = 7

フリーで利用できるデータベース・開発ツール（オラクル）

オラクルの提供するフリーのデータベースおよび開発ツール

【データベース】
無料で使える「Oracle Database XE」をインストール

Oracle Database Express Edition


【ツール】
Javaアプリケーションの開発支援機能が充実
Oracle JDeveloper

Oracle JDeveloper

【Transact-SQL】テーブルの追加と設定

--単純なテーブルを作成した後、ALTERを使って以下を行う
--　・プライマリキーの追加
--　・規定値（デフォルト値）制約の追加
--　・カラムの追加
--　・カラムの変更
--　・一意制約（UNIQUE）の設定
--　・チェック制約の設定

--テーブルの追加
CREATE TABLE t_test (
id INT IDENTITY(1,1) not NULL,
data VARCHAR(50)
)

--制約の追加
--プライマリキーの設定
ALTER TABLE t_test
ADD CONSTRAINT pk_t_test_id PRIMARY KEY (id)

--規定値（デフォルト値）制約の追加
ALTER TABLE t_test
ADD CONSTRAINT df_default_data2
DEFAULT 'test test' FOR data2 --フィールドdata2の規定値は'test test'

--カラムの追加
ALTER TABLE t_test ADD data2 VARCHAR(77)

--カラムの型変更
ALTER TABLE t_test ALTER COLUMN data VARCHAR(32)

--NULL制約の設定
--　・　NULLを許可する場合→NULL
--　・　NULLを許可しない場合→NOT NULL
ALTER TABLE t_test ALTER COLUMN data NOT NULL

--一意制約（UNIQUE）の設定
--テーブルにsub_idフィールドを追加し、一意制約を設定する
--フィールドの追加
ALTER TABLE t_test
ADD sub_id INT

--一意制約の設定
--既にフィールドに一意でないデータが入っている場合は実行に失敗する
ALTER TABLE t_test
ADD CONSTRAINT ix_t_test_sub_id UNIQUE (sub_id)

--チェック制約の設定
--値の範囲をチェックする制約を設定
--SMALLINTのフィールドを追加し、制約を設定する
ALTER TABLE t_test
ADD type_number SMALLINT

--数値の範囲が０～１０であることを確認する制約を設定する。
--この制約を設定した状態でも、当該フィールドの値がNULLの
--データを追加できる
--NULLデータの禁止はNULL制約を別途設定する
--また、この制約はデータの追加時にのみチェックされるため
--範囲外のデータが既にテーブル上に存在する場合でも設定できる

ALTER TABLE t_test
ADD CONSTRAINT ck_t_test_type_number
CHECK(type_number between 0 and 10)

--チェック制約は次のSQLでON/OFFすることができる。
--チェック制約―ON (CHECK CONSTRAINT)
ALTER TABLE t_test
CHECK CONSTRAINT ck_t_test_type_number
--チェック制約―OFF(NOCHECK CONSTRAINT)
ALTER TABLE t_test
NOCHECK CONSTRAINT ck_t_test_type_number

データベース スナップショット概要

データベース スナップショットについてのメモ

データベースの「ある時点」の読取り専用コピー

○メリット１

　ミラーリングした際に、通常は待機側（復元状態）データベースの
　内容を見ることは出来ないが、データベーススナップショットを
　作成すれば、読み取り専用サーバーとして使うことが出来る。

○メリット２
　テスト時に使える。

　テスト開始前の状態のデータベーススナップショットを
　作成した状態でテストを実行、結果を確認後に
　データベーススナップショットの時点の状態に戻す。

NTFSの特徴

MCPの勉強でNTFSがでてきたので、その特徴をメモしておく。

【NTFSの特徴】
・アカウントごとにアクセス権が設定できる
・ファイルシステムレベルで暗号化ができる
・ジャーナリングファイルシステム
　（クラッシュ時の復旧をファイル更新履歴を使って行なう）

最後（直前）に追加されたデータのIDを取得する

追加したデータのIDを別のテーブルにも設定する場合など、
直前に追加されたID（identity）の値を知りたい時には

@@identity

の値を取得する。

関連リンク：
@@IDENTITY (Transact-SQL)

【Transact-SQL】日付の加算・減算（その１）

--日付の加算・減算を行なう。
--１日を１とした場合の各時間の値
--　３時間＝0.125
--　６時間＝0.25
--　９時間＝0.375
--１２時間＝0.5

DECLARE @date DATETIME
SET @date = CONVERT(DATETIME, '2008/10/10 12:00:00')

PRINT @date + 0.5
PRINT @date + 0.375
PRINT @date + 0.25
PRINT @date + 0.125
PRINT @date
PRINT @date - 0.125
PRINT @date - 0.25
PRINT @date - 0.375
PRINT @date - 0.5

--【実行結果】
-- 10 11 2008 12:00AM
-- 10 10 2008 9:00PM
-- 10 10 2008 6:00PM
-- 10 10 2008 3:00PM
-- 10 10 2008 12:00PM
-- 10 10 2008 9:00AM
-- 10 10 2008 6:00AM
-- 10 10 2008 3:00AM
-- 10 10 2008 12:00AM

【SQL】文字数をあわせるため不足分を０で埋める

--取得した日付を年月日に分解する。
--分解した際の文字数が足らない場合は左側を０で埋める。
DECLARE @date datetime
SET @date = getdate()

--分解した値を必要な文字数のVARCHARにキャストするのがポイント
PRINT right('00' + CONVERT(varchar(4),DATEPART(YEAR,@date)),4)
PRINT right('00' + CONVERT(varchar(2),DATEPART(MONTH,@date)),2)
PRINT right('00' + CONVERT(varchar(2),DATEPART(DAY,@date)),2)
PRINT right('00' + CONVERT(varchar(2),DATEPART(HOUR,@date)),2)
PRINT right('00' + CONVERT(varchar(2),DATEPART(MINUTE,@date)),2)
PRINT right('00' + CONVERT(varchar(2),DATEPART(SECOND,@date)),2)

--実行結果 : 値が一桁の場合には左側に０が入っている。
--2008
--08
--19
--20
--18
--01

【SQL】重複のあるデータをHaving句で取得

--HAVING句を使ってidに重複のあるデータのみ取得する。
--概要・・・
--①id ごとに件数を集計する。(GROUP BY)
--②集計した結果が１より大きいものを選択する。(HAVING)
--テーブルtbl_dataの構成例
--[id] AS int
--[data] AS varchar(50)

SELECT [id]
FROM [tbl_data]
GROUP BY [id]
HAVING COUNT(*) > 1

【Transact-SQL】引数で値を返すストアドプロシージャの作成

--引数で値を返すストアドプロシージャの作成
CREATE PROCEDURE st_get_output_data
(
@inArg1 varchar(16)
,@outArg1 varchar(32) OUTPUT
)
AS
BEGIN
SET @outArg1='@inArg1 is ' + @inArg1
RETURN 0
END

--ストアドプロシージャの実行
DECLARE @RC int
DECLARE @inArg1 varchar(16)
DECLARE @outArg1 varchar(32)

SET @inArg1 = 'test data'

EXECUTE @RC = [test].[dbo].[st_get_output_data]
@inArg1
,@outArg1 OUTPUT

SELECT @outArg1

--実行結果
--@inArg1 is test data

【Transact-SQL】複数行を返すテーブル値関数を作成する

--複数行を返すテーブル値関数を作成する
CREATE FUNCTION tfn_get_table_data
(
@inID AS int
)
RETURNS @wk TABLE
--戻り値のテーブル定義
(
outID int
)
AS
BEGIN
--条件式（IF,ELSE）、WHILE文も使うことが出来る。

--呼出元に返す値をテーブルにINSERTする。
INSERT INTO @wk(outID)VALUES(@inID)
--処理の最後はRETURNで終了させる。
RETURN
END

【Transact-SQL】四捨五入・切捨て（ROUND関数）

--切捨て
PRINT '--------------------'
PRINT '切捨て'
PRINT '--------------------'
PRINT ROUND(555.55,2,1) --小数点第二位まで
PRINT ROUND(555.55,1,1) --小数点第一位まで
PRINT ROUND(555.55,0,1) --
PRINT ROUND(555.55,-1,1)--１の位
PRINT ROUND(555.55,-2,1)--１０の位
--四捨五入の確認
PRINT '--------------------'
PRINT '四捨五入の確認'
PRINT '--------------------'
PRINT ROUND(555.54,1) --小数点第二位が４で切り捨て
PRINT ROUND(555.55,1) --小数点第二位が５で切り上げ


【結果】
--------------------
切捨て
--------------------
555.55
555.50
555.00
550.00
500.00
--------------------
四捨五入の確認
--------------------
555.50
555.60

【SQL】table1に存在しないIDのデータをtable2より取得する

--【table1に存在しないIDのデータをtable2より取得する】
--　table2とtable1をLEFT JOINで連結し、
--　table2から、tabel1.id=table2.idの条件でtable1.idがNULLのデータを取得する
--例）データ
------table1 | table2
--[id] 　1　 　|　 1
--[id] 　2 　　| 　2
--[id] 　3 　　| 　3
--[id] 　4 　　| 　4
--[id] NULL |　 5
--[id] NULL |　 6
-- LEFTJOINなので、table1に存在しないidはNULLになる

SELECT DEST.[id],DEST.[key]
FROM [test].[dbo].[table2] DEST
LEFT JOIN [test].[dbo].[table1] SRC
ON DEST.[id]=SRC.[id]
WHERE SRC.[id] IS NULL

【結果】
id key
----------- ----------
5 data_2_5
6 data_2_6

(2 行処理されました)

【SQL】重複した行を取り出す

--テーブルからフィールドAの値が重複しているデータを取り出す。
--手順：
--①GROUP BYでフィールドAを指定し、HAVING句を用いて
--　COUNT()の結果が２以上のデータのリストを取得する。
--　例）リストの取得方法
SELECT [key]
FROM [test].[dbo].[tbl_union1]
GROUP BY [key]
HAVING COUNT([key])>1

--②①で取得したリストに入っているデータをテーブルから取得する。
SELECT [key]
FROM [test].[dbo].[tbl_union1]
WHERE [key] in
(SELECT [key]
FROM [test].[dbo].[tbl_union1]
GROUP BY [key]
HAVING COUNT([key])>1)

【結果】
key
----------
union1_3
union1_3
union1_3

(3 行処理されました)

【Transact-SQL】条件式とループ処理（IF-ELSE, WHILE)

--WHILEによるループ処理とIF-ELSEによる条件分岐
DECLARE @cnt INT
SET @cnt = 0

WHILE @cnt < style="color: rgb(0, 153, 0);">--ループ処理の中身はBEGIN-ENDで囲む
BEGIN
IF @cnt = 0
--条件によって実行される処理が複数の場合はBEGIN-ENDで囲む
BEGIN
Print '複数行実行開始'
Print '@cnt = 0'
Print '複数行実行終了'
END
ELSE IF @cnt = 1
BEGIN
Print '複数行実行開始'
Print '@cnt = 1'
Print '複数行実行終了'
END
--条件によって実行される処理が１つの場合はBEGIN-ENDは必要ない
ELSE IF @cnt = 2
Print '@cnt = 2'
ELSE
Print 'Else'
SET @cnt = @cnt +1
END -- End of WHILE Statement

【結果】
複数行実行開始
@cnt = 0
複数行実行終了
複数行実行開始
@cnt = 1
複数行実行終了
@cnt = 2
Else
Else

【SQL】Datetime型の日付データを年月日に分割する

--Datetime型の日付データを年月日に分割する
DECLARE @currdate Datetime

SET @currdate = getDate()

Print @currdate
Print DATEPART(YEAR,@currdate)
Print DATEPART(MONTH,@currdate)
Print DATEPART(DAY,@currdate)
Print DATEPART(HOUR,@currdate)
Print DATEPART(MINUTE,@currdate)
Print DATEPART(SECOND,@currdate)


【結果】
04 30 2008 7:26PM
2008
4
30
19
26
29

【MSSQL】新規にINSERTしたレコードのIDを取得する方法

--新規にINSERTしたレコードのIDを取得する方法
--INSERTを実行した直後に@@IDENTITYの値を取得する。

INSERT INTO [dbo].[TEST]
([data1])
VALUES('test value')


Print 'New ID is ' + CONVERT(VARCHAR,@@IDENTITY)

【SQL】トリム処理

--文字列の先頭・末尾にあるスペースを取り除く
DECLARE @value varchar(32)
SET @value = ' test '

print '-' + @value + '-'
print '-' + LTRIM(@value) + '-'
print '-' + RTRIM(@value) + '-'
print '-' + LTRIM(RTRIM(@value)) + '-'

【結果】
- test -
-test -
- test-
-test-

【SQL】NULL値の扱い方１（比較方法）

--NULL値の扱い方１（比較方法）
--①間違ったNULL値の比較方法
IF @value = NULL
print 'null'
ELSE
print 'not null'

--②正しいNULL値の比較方法
IF @value IS NULL
print 'null'
ELSE
print 'not null'

--NULL値でなければ①の比較方法で比較できる。
SET @value = ''
IF @value = ''
print 'value is empty'
ELSE
print 'value is not empty'

【結果】
start of test1
test
end of test1
start of test2

end of test2
not null
null
value is empty

【SQL】キャストの方法（CONVERT関数 数字から文字）

キャストの方法（CONVERT関数 数字から文字）

DECLARE @value int
SET @value = 2
--文字＋数字で出力すると、数字で出力される。
print '0' + @value
--キャストすると文字として出力される。
print '0' + CONVERT(varchar,@value)

--結果
2
02