Software_DBMS

Data Base Management System

┌──┐
│目次│
├──┘
│☆DBMS dbms
│ ◆DBMSの役割と機能 func
│ ◆DBMSの分類 class
│ ◆データベース構造の設計 struc
│ ◆SQL sql
│ ◆トランザクション管理 tran
│ ◆ロック lock
│ ◆Oracle oracle
│ ◆MySQL my
│ ◆PostgreSQL postgre
│ ◆SQL Server server
│ ◆DB2 db2
│ ◆SQLite lite
│ (Node-REDなどからのSQLite呼び出しの練習へのリンクあり）

☆DBMS
│　データの集合体であるデータベース
│　複数同時のアクセスを制御
│　データを保護する

※データベース
│　データ形式：予め定義される。
│　ファイル構造：管理される。

※データファイル
│　データを記録するためのファイル

◆DBMSの役割と機能
│　①データに一貫性を持たせる。整合性を持たせる
│　②データの重複を排除する
│　③データ利用の標準化、インタフェース
│　④アクセス制限の管理

プログラム＜－＞ＤＢＭＳ＜－＞データベース

＿◇データベース設計機能
│　テーブルの設計
│　リレーションの定義

＿◇データベース構築機能
│　データの作成・削除

＿◇データベース操作機能
│　データの並べ替え、抽出、検索

＿◇データ共有化機能
│　複数のユーザ間での共有

＿◇アクセス管理機能
│　アクセス権
│　ユーザ認証

◆DBMSの分類

＿◇ツリー型
│　（階層型）
│　一つの親データに複数の子データがツリー構造的に結合

＿◇ネットワーク型
│　各レコード間の関係が網目状。
│　多対多の複雑なデータ構造

＿◇分散型
│　データベースを複数にコンピュータに分けて管理。
│　全体として一つの構造を持つ

＿◇オブジェクト型
│　オブジェクト指向の構造。
│　マルチメディアデータなど

＿◇リレーショナル型(RDB)
│　Relational Data Base
│　データを２次元の表形式で表現する
│　行と列
│　行＝レコード
│　列＝フィールド
│　RDBMS: Relational Data Base Management System

※SQL
│　共通のデータベース言語
│　データを参照、変更するときに使われる
│　ISO, JISでも標準化

※主キー(Primary Key)
│　各表に必ずある
│　データを識別するためのユニークな値が入った列（レコードを識別する）
│　⇒複数のフィールドをセットにしてプライマリキーとすることもできる

※リレーション
│　複数の表をＳＱＬで一時的に結びつけて処理する
│　⇒あるテーブルのプライマリキーを
│　⇒それと同じ値を持った別のテーブルのフィールド値（外部キー：Foreign Key）とを関連づける

※参照整合性制約
│　表同士のデータの矛盾が生じないように、
│　データの参照関係規則に違反するデータを変更しようとしてもエラーとして処理しない
│　参照する側外部キー(Foreign Key)
│　参照される側参照キー

※正規化
│　データ重複が無いようにデータを複数の表に分割していく作業（データベースの設計段階で行われる）

◆データベース構造の設計

＿◇正規化
│　全テーブルのデータを分析、効率化されたリレーションを作成する

※整合性がとれていないデータベース
│　⇒データ領域を無駄に消費
│　⇒性能悪化

※テーブルの分解とリレーションの作成をリレーショナルデータベースの正規化と呼ぶ

※正規化手順
①第１正規化
⇒全ての属性が異なった値を持っているようにする
│　フィールドの重複を避ける
│　フィールドの繰り返し
│　フィールド中に複数のデータがある
│　 ⇒別レコードに分離
│　導出フィールドは排除
│　（他のフィールドから算出できるデータ）
│　重複が無くなるようにテーブル分割
※リレーションはスカラー値でなければならない

⇒プライマリキー
│　ユニークな値を持つ、ＮＵＬＬではならない
│　値は変更されない
⇒外部キー
│　２つの関係変数の間の参照整合性制約

②第２正規化
⇒プライマリキーに従属するフィールドと部分従属するフィールドを分離する
│　（プライマリキーが決まれば、その他のフィールドも決定できるようにする）
⇒値の重複を避け、他のテーブルから参照されるために用意されるテーブル＝マスターテーブル
※全ての非キー属性が、候補キーに完全従属する

③第３正規化
⇒プライマリキー以外の全てのフィールドは、プライマリキーに直接に従属する
│　＜＞やりすぎると小さいテーブルを数多く作りすぎる
※非キー属性の間での従属を問題とする

※第４正規化
│　BCNF (Boyce Codd Normal Form)

※従属
│　あるデータ項目の値が決定すると、他の列の値も一意に決定する関係

※部分従属
│　プライマリーキー以外のフィールドが、プライマリキーを構成する一部のフィールドに従属している
│　（決定項に余分な属性がある）

完全従属
│　プライマリーキー以外のフィールドが、プライマリキー全体に従属している
│　（決定項に余分な属性がない）

＿◇フィールド定義

※適切なデータ型の選択と決定

※データ型を大きくしておいて、使わない分を後から切り詰めることはできる。
│　⇒alter table
│　⇒後から大きくすることもできるが以下の欠落が心配される。

※小さいデータ型で溢れてしまうと、格納時に切り詰めされてしまい、データが欠落する。

※後で検索の対象となるようなフィールドにはINDEXを作成しておくと良い

◆SQL
│　Structured Query Language
※一つの文で完結した意味を持つ
※もともとは条件分岐や繰り返し処理などのフロー制御文は含まれていなかった
※命令文をquery(問い合わせ）と呼ぶ
│　①クエリ発行
│　②ＤＢＭＳがクエリ解釈、データファイルを読み書き
│　③結果が返される
※ANSI, ISO, JISで規格化されている

＿◇SQL文の要素
│　基本コマンド
│　基本コマンドの条件、オプション
│　文字列や数値と、関数、演算子
│　アクセス制御

＿◇予約語
│　大文字、小文字の区別がない
※実際に使用されているのがSQLキーワード
※テーブル、フィールド名としての使用が禁止される

＿◇文字列
シングルクオートもしくはダブルクオートで囲む

①シングルクオート ‘
│　\’と\\以外のエスケープシーケンスは解釈されない

②ダブルクオート ”
│　エスケープシーケンスを解釈する

※エスケープシーケンス
\0 ASCII 0 (NUL)
\n 改行
\t タブ
\r リターン
\b バックスペース
\z Control-Z (ASCII 26)
\’ シングルクォート
\” ダブルクォート
\\ バックスラッシュ
\% %文字
\_ _文字

※クオートを２つ連続させることで１クオートを文字列中に入れることができる

例）
‘IT”S FUNNY.’

※BLOBフィールドにバイナリデータを挿入する場合は、以下の特殊文字をエスケープする
│　NUL
│　\
│　’\’
│　”\”

＿◇名前規則
①データベース名
│　64文字
②テーブル名
│　64文字
③フィールド名
│　64文字
④エイリアス
│　255文字

※識別子が予約語と同じか、特殊文字を含む場合は、バッククォートで囲む
※識別子命の先頭に数字を使った場合、1eなどは数値として解釈される文脈がある。

＿◇数値
①整数数字の並び
②１６進数 0xで始まる
③浮動小数点整数と小数の間に「.」
④指数「e」の後に指数

※負数には「-」をつける

＿◇コメント
│　#以降から改行まで
│　/* から　*/まで（複数行）
※シングルクオートと、ダブルクオートはコメントの中でも文字列の開始となる
※セミコロンはSQL構文末なので、以降の文字列は次の構文の開始とみなされる

＿◇NULL
│　値の未定義を意味する。空白ではない。
│　比較や演算の対象にはならない。
│　NULLどおしでも等しくない。
※テキストファイルの読み書きでは\Nで表現される

＿◇式項
│　フィールド名
│　文字列、数値などのリテラル

＿◇算術演算子

+
–
*
/

例）
select 10 + 100;

SELECT price + 10 FROM goods;
⇒フィールドの値を式項として演算

※０で割るなどエラーの場合はNULLが変える

＿◇グループ化演算子
│　()で囲んで、式項をグループ化し、評価の優先度を制御できる

＿◇比較演算子

=
>
<
>=
<=
!=, <>

※論理値
│　０がFALSE、空文字列はFALSE
│　１がTRUE

※数値でも文字列でも比較できる。
※文字列と数値の比較もできる。
│　両方が文字列なら文字列
│　両方が整数なら整数
※一方がNULLなら、比較結果はNULL
※引数の一方がTIMESTAMPまたはDATETIMEで、他方が定数の場合は、定数は比較前にTIMESTAMPに変換される。他の場合は浮動小数として比較。

※IS NULL
│　NULLで1, 他で0を返す

例)
>SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;

※IS NOT NULL
│　NULLで0, 他で1を返す

※expr BETWEEN min AND max
│　exprがmin以上、max以下なら１、そうでないなら0

例）
>SELECT 5 BETWEEN 1 AND 10;

※expr IN (value …)
│　引数リストとマッチすれば1, そうでなければ０

例）
>SELECT ‘abc’ IN (1, 2, 3, ‘abc’);

※expr NOT IN (value …)

※COALESCE (value …)
│　リストの中にあるNULL以外の要素を返す
│　⇒１個だけ？

例）
>SELECT COALESCE (5, NULL)

※INTERVAL(N, N1, N2, N3,…)
│　N<N1なら0, N<N2なら1…

例）
│　>SELECT INTERVAL(10, 1, 10, 100, 1000);

＿◇論理演算子
│　1(TRUE)か0(FALSE)を返す

①NOT (!)
│　0なら1、そうでなければ0を返す。NULLのときはNULLを返す

②OR (||)
│　引数のどれかが０またはＮＵＬＬでなければ1を返す
│　左側が真であれば右側は評価されない

③AND (&&)
│　全ての引数が０またはＮＵＬＬでなければ１を返す
│　左側の演算が偽なら右側は評価されない

④IFNULL
│　IFNULL(expr1, expr2)
│　expr1がNULLでなければexpr1をそうでなければexpr2

⑤IF
│　IF(expr1, expr2, expr3)
│　expr1が真ならexpr2, そうでなければexpr3
例）
> SELECT IF(1<2,’yes’,’no’);

※expr1はINTEGERとして評価。浮動小数を使う場合は比較演算が必須

＿◇ビット演算子
①OR (|)
②AND (&)
③左シフト <<
④右シフト >>
⑤ビット反転 ~
⑥ビットカウント BIT_COUNT(N)
│　２進表記での１の数
例）
>SELECT BIT_COUNT(29);

＿◇データ型：文字列

①CHAR 固定長１～２５５文字
│　BINARY属性がつかなければ大文字小文字を区別しない
│　※CHAR(M)と宣言すればMバイト
②VARCHAR 可変長１～２５５文字
│　BINARY属性がつかなければ大文字小文字を区別しない
│　※VARCHAR(M)、文字列の長さ＋１バイト
│　Mは最大の長さ
③TINYBLOB / TINYTEXT 最大長２５５文字
│　⇒BLOBはケース依存、TEXTはケースに依存しない
│　バイナリオブジェクトを格納
④BLOB / TEXT 最大長６５５３５文字
⑤MEDIUMBLOB / MEDIUMTEXT 最大長16M
⑥LONGBLOB / LONGTEXT 4G

※ユニコード対応
│　nchar
│　１～２５５文字、ユニコード文字列
│　national character varying
│　１～２５５文字、ユニコード可変長文字列

※BLOB, TEXT
│　インデックスを持たない
│　値の後ろの空白を切り落とさない
│　デフォルト値がない
│　GROUP BYやORDER BYの対象にするためにはSUBSTRING関数により固定長にする必要がある

例）
>SELECT comment FROM tbl ORDER BY substring(comment,20);
>SELECT id,substring(blob1,1,100) AS b FROM tbl GROUP BY b;

＿◇データ型：数値型

※数値型のオプション
│　UNSIGNED 整数符号なし
│　ZEROFILL 表示の際に左から0が詰められる
│　(M)/(D) 最大表示サイズ(255)と小数点以下の桁数
※マイナスも１文字

①TINYINT(M)
│　1バイト
②SMALLINT(M)
│　2バイト
③MEDIUMINT(M)
│　3バイト
④INTEGER, INT(M)
│　4バイト
⑤BIGINT(M)
│　8バイト
⑦FLOAT(精度)
│　8桁以内の浮動小数。単精度4,倍精度8を指定可能
⑧FLOAT(M,D)
│　４バイト浮動小数
⑨DOUBLE(M, D)
│　８バイト浮動小数
⑩DECIMAL, NUMERIC (M,D)
│　パック無し浮動小数。正確な精度で計算できる。貨幣など扱うとき。

※整数値の表示幅指定
│　⇒実際の値や桁数を制限するわけではない
※値を超える数値を代入しようとすると値は許容範囲内に切り詰められる

＿◇データ型：日付型
※日付や時間型のフィールドに範囲外の値を格納すると、その値はゼロに変換される

①DATE
│　日付
│　３バイト
②DATETIME
│　日付と時間
│　８バイト
③TIMESTAMP
│　現在日時（自動格納）
│　TIMESTAMP(14) YYYYMMDDHHMMSS
│　TIMESTAMP(12) YYMMDDHHMMSS
│　TIMESTAMP(10) YYMMDDHHMM
│　TIMESTAMP(8) YYYYMMDD
│　TIMESTAMP(6) YYMMDD
│　TIMESTAMP(4) YYMM
│　TIMESTAMP(2) YY
│　４バイト
④TIME
│　時刻
│　HHMMDD
│　HHは3桁まで格納可能（経過時間や間隔などの格納のため）
│　３バイト
⑤YEAR
│　年
│　許される値
│　４桁１９０１～２１５５
│　２桁１９７０～２０６９
│　２桁の整数は１～６９なら２００１～２０６９
│　７０～９９は１９７０～１９９９に変換される
│　１バイト

＿◇算術関数
①LEAST(X, Y, …)
│　引数リストの中で最小の数を返す
②GREATEST(X, Y, …)
│　引数リストの中で最大の数を返す
③ROUND(X)
│　四捨五入を返す
④TRUNCATE(X, D)
│　Xを少数点以下Ｄ桁で切り捨てる
⑤MIN(expr)/MAX(expr)
│　通常 where や group by と同時に使用し、指定したフィールドの最大、最小を返す。
⑥CEILING(X)
│　整数に丸める。正の数は切り上げ、負の数は切り捨て
⑦FLOOR(X)
│　整数に丸める。正の数は切り捨て、負の数は切り上げ
⑧LOG(X)
│　自然対数
⑨LOG10(X)
│　常用対数
⑩POW(X,Y)/POWER(X, Y)
│　XのY乗
⑪SQRT(X)
│　Xの平方根
⑫EXP(X)
│　eのX乗
(13) SIN(X)
│　Xのサイン。ラジアンで指定
(14) COS(X)
│　Xのコサイン。ラジアンで指定
(15) TAN(X)
│　Xのタンジェント
(16) COT(X)
│　Xのコタンジェント
(17) ACOS(X)
│　Xのアークコサイン -1<X<1を超えるとNULL
(18) ASIN(X)
│　Xのアークサイン -1<X<1を超えるとNULL
(19) ATAN(X)
│　Xのアークタンジェント
(20) ATAN2(X, Y)
│　点(X,Y)のアークタンジェント
(21) ABS(X)
│　Xの絶対値
(22) MOD(N, M) あるいは %
│　NをMで割ったときのあまり
(23) PI()
│　π
(24) RADIANS(X)
│　Xを度からラジアンに変換
(25) DEGREES(X)
│　Xをラジアンから度に変換
(26) RAND あるいは RAND(N)
│　0から1.0までの浮動小数点数の乱数
│　Nを指定した場合は、それをシード値とする
(27) SIGN(X)
│　Xが負のとき-1, 0のとき0, 正のとき1を返す

＿◇文字列関数

※max_allowed_packetよりも結果の長さが大きい場合、文字列は NULL を返す
※文字列の先頭位置は 1

①LENGTH(str), OCTET_LENGTH(str),
│　CHAR_LENGTH(str), CHARACTER_LENGTH(str)
│　文字列 str の長さを返す
②CONCAT(S1, S2, …)
│　引数を結合した結果を返す。引数がNULLならNULL
③CONCAT_WS(separator, S1, S2, …)
│　separatorを区切り文字として、S1以下を結合した結果を返す。
│　区切り文字がNULLだとNULLとなる。
│　区切り文字より後のNULLと空文字列をスキップ
④LEFT(str, len)
│　文字列 str の先頭から len 個の文字を取り出す
⑤RIGHT(str, len)
│　SUBSTRING(str FROM len)
│　strの末尾からlen個の文字を返す
⑥SUBSTRING(str, pos, len)
│　MID(str, pos, len)
│　SUBSTRING(str FROM pos FOR len)
│　strのpos文字目＋len文字を返す
⑦SUBSTRING_INDEX(str, delim, count)
│　strから delimで count個に区切られた文字列を返す
│　countが正なら左から、負なら右からとる
例)
>select substring_index(‘www.rfs.jp’, ‘.’, 1);
-> www

⑧ASCII(str)
│　strの左端の文字のASCIIコード値を返す。strが空文字の場合は0. NULLの場合はNULL
⑨CHAR(N, …)
│　引数のASCIIコード値から対応する文字列を返す
│　NULLはスキップする
⑩CONV(N, from_base, to_base)
│　数値Nの基数を、from_base進数から、to_base進数に変換した結果の文字列を返す。
│　NULLならNULLを返す。
│　2~36進数、to_baseが負数なら符号付となる
(10) BIN(N)
│　Nはlong型、Nを２進数にした文字列を返す。
│　NULLならNULL
(11) OCT(N)
│　Nはlonglong. ８進数文字列を返す
(12) HEX(N)
│　Nはlonglong. １６進数文字列を返す
(13) ORD(str)
│　マルチバイト文字の文字コードを返す
(14) LCASE(str) / LOWER(str)
│　大文字を小文字に変換
(15) UCASE(str) / UPPER(str)
│　小文字を大文字に変換
(16) SOUNDEX(str)
│　strに関するsoundexコードを返す。
│　非アルファベット文字は無視される。

※SOUNDEXコード
│　文字列の類似性を評価するための4文字のコード。
│　コードの先頭文字はstrの先頭、２～４番目は数字。
│　母音は文字列の先頭になければ無視される。

(17) FIELD(str, str1, str2, str3, …)
│　第１引数にマッチする第２引数以降のリストの位置を返す。
│　見つからなければ0を返す
(18) FIND_IN_SET(str, strlist)
│　strlistはカンマで分割されたN個の文字列。
│　strがstrlistに含まれる値にマッチすれば１～Nの値を返す。
│　strlistが空なら0. どちらかがNULLならNULL
(19) INSERT(str, pos, len, newstr)
│　str中のpos位置(最初が1)から長さlenをnewstrで置き換える。
(20) INSTR(str, substr)
│　LOCATE(substr, str, pos)
│　POSITION(substr IN str)
│　str内のsubstrの位置を返す。
│　posはマッチングを開始する位置だが、省略すると1となる。
(21) MAKE_SET(bits, str1, str2, …)
│　bitsに対してstr1がビット0, str2がビット1のように対応した文字列のセットを返す。
│　文字列はカンマで区切られる。
(22) REPLACE(str, from_str, to_str)
│　str内の全ての文字列 from_strをto_strに置き換える
(23) LPAD(str, len, padstr)
│　strの長さがlenになるまで先頭にpadstrを埋める
(24) RPAD(str, len, padstr)
│　strの長さがlenになるまで末尾にpadstrを埋める
(25) LTRIM(str) / RTRIM(str)
│　TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM]str)
│　LTRIMは先頭から、RTRIMは末尾から空白文字を削除
│　TRIMは、指定によりどちらか、または両方から削除、
│　さらに削除文字列を指定することで空白文字以外でも削除できる
(26) SPACE(N)
│　N個の空白文字を返す
(27) LOAD_FILE(file_name)
│　ローカルファイルの中身を文字列として返す
※フルパス指定、ファイル権限要、ファイルはmax_allowed_packetより小さくなければならない。なんらかの理由で読み込めない場合はNULL

例）
UPDATE table_name SET blob_column=LOAD_FILE(“/tmp/picture”) WHERE id=1;

(28) EXPORT_SET(bits, on, off, [separator, [number_of_bits]])
│　bits内のビットのうち、１をonの文字列、０をoffの文字列で表した文字列にして返す。
│　separator文字列で区切られ（デフォルトは「,」)、
│　number_of_bits（最大６４）で指定したビット数だけを処理する
(29) REPEAT(str, count)
│　str を count回繰り返した文字列を返す
(30) ELT(N, str1, str2, …)
│　N=1ならstr1というように、Nに応じたstrを返す。
│　対応する文字列が無ければNULL
(31) REVERSE(str)
│　strの文字列を逆順にして返す

＿◇日付時刻関数

①NOW() / SYSDATE() / CURRENT_TIMESTAMP()
│　数値コンテキストでは YYYYMMDDHHMMSS,
│　それ以外では YYYY-MM-DD HH:MM:SS
②CURTIME() / CURRENT_TIME()
│　数値コンテキストでは HHMMSS それ以外は HH:MM:SS
③CURDATE() / CURRENT_DATE()
│　現在の日付を返す。数値コンテキストでは YYYYMMDD,
│　それ以外では YYYY-MM-DD
④HOUR(time)
│　timeのなかの時の部分を返す
⑤MINUTE(time)
│　timeの中の分の部分を返す
⑥SECOND(time)
│　timeの中の秒を返す
⑦MONTH(date)
│　dateの中の月を返す
⑧YEAR(date)
│　dateの中の年を返す
⑨WEEKDAY(date)
│　日曜を０とする、曜日インデックスを返す
⑩QUARTER(date)
│　dateが属する４半期(カレンダー年）を返す
(11) DAYNAME(date)
│　曜日名を返す
(12) MONTHNAME(date)
│　月名を返す
(13) DAYOFMONTH(date)
│　その月の何日目であるか返す
(14) DAYOFWEEK(date)
│　週の何日目であるかを返す。1が日曜。
(15) DAYOFYEAR(date)
│　年の何日目であるかを返す。
(16) WEEK(date)/WEEK(date, first)
│　年の第何週か返す。２番目の引数を0とすると週の始まりは日曜。1とすると月曜。
(17) YEARWEEK(date)/YEARWEEK(date, first)
│　年と週を返す
(18) SEC_TO_TIME(seconds)
│　秒引数を、時、分、秒に変換。数値コンテキストならHHMMSS形式、他ではHH:MM:SS
(19) TIME_TO_SEC(time)
│　timeで指定した時間表現を秒に変換する
(20) DATE_FORMAT(date, format)
│　format文字列にしたがって日付の値をフォーマットする
(21) TIME_FORMAT(time, format)
│　format文字列にしたがって時刻の値をフォーマットする
(22) DATE_ADD(date, INTERVAL expr type)
│　DATE_SUB(date, INTERVAL expr type)
│　ADDDATE(date, INTERVAL expr type)
│　SUBDATED(date, INTERVAL expr type)
│　dateに exprを加算または減算する。
│　exprにマイナスをつけることができるので実質は一つ。
│　単なる演算子「+」でも同じ演算ができる。
│　typeには、SECOND, MINUTEなどのexprを解釈する型を記す。
(23) PERIOD_DIFF(P1, P2)
│　２つの期間P1とP2の差を月数で返す。
│　P1,P2は日付ではなく、YYMMもしくはYYYYMM形式。
(24) FROM_DAYS(N)
│　紀元0年からの日数Nを日付にして返す。
│　グレゴリオ暦(1582)以前の値は考慮されない。
(25) PERIOD_ADD(P, N)
│　期間PにN月を加算し、YYYYMM形式で返す
(26) TO_DAYS(date)
│　日付を紀元0年からの日数にして返す。
│　グレゴリオ暦(1582)以前の値は考慮されない。
(27) UNIX_TIMESTAMP()
│　UNIX_TIMSTAMP(date)
│　引数なしで呼び出された場合は、現在のUNIXタイムスタンプを、
│　dateが与えられた場合は、dateのUNIXタイムスタンプを与える。

※UNIXのタイムスタンプ
│　GMT 1970-01-01 00:00:00からの秒数

(28) FROM_UNIXTIME(unix_timestamp, format)
│　formatに従って整形されたUnix timestamp文字列を返す。
│　formatを省略すれば、通常のdateと同様なフォーマットで返される
(29) EXTRACT(type FROM date)
│　文字列から日付を抜き出す
例）
>SELECT EXTRACT(YEAR FROM “2002-08-10″);
2002

＿◇その他の関数

①BENCHMARK(count, expr)
│　exprをcount回実行する。結果は常に0。処理時間を測定するために用いる
例）
> SELECT BENCHMARK(1000000,”SELECT * FORM item”);

※CPU時間でなく、クライアントでの時間

②CONNECTION_ID()
│　接続IDを返す
③DATABASE()
│　現在使用しているデータベース名を返す
│　SELECT DATABASE();
④ENCRYPT(str [,salt])
│　UNIXのcryptシステムコールで暗号化する。
│　saltは２文字の文字列。
│　システムでcrypt()が利用できない場合は、NULLを返す。

※salt [a-zA-Z0-9./]から選ばれた２文字の文字列。アルゴリズムの出力を４０９６通りにかく乱するのに使われる。

⑤FORMAT(X, D)
│　数値 X を #,###,###.##形式に整形する。
│　Dには小数点以下の桁数を指定する。
⑥GET_LOCK(str, timeout)
│　timeoutまで、一定秒間待機するロックを作成する。
│　同じロック名を使ってプログラム間で連携する。
│　ロックが成功すると１、タイムアウトは０、それ以外はNULL。
│　RELEASE_LOCK, 新しいGET_LOCK, スレッドの終了の時にロックが開放される
⑦LAST_INSERT_ID([expr])
│　最後にINSERTされたID
⑧MD5(string)
│　文字列をMD5を使って変換した結果を返す。32桁の16進数。
⑨PASSWORD(str)
│　strで指定した文字列を暗号化する。
│　不可逆。
│　ユーザのパスワードを登録する際に利用する。
⑩RELEASE_LOCK(str)
│　GET_LOCKで作成された str という名前のロックを解放する。
│　成功すると1, 失敗で0, 存在しない場合はNULLを返す。
⑪USER() / SYSTEM_USER() / SESSION_USER()
│　現在のユーザ名を返す
⑫VERSION()
│　MySQLサーバのバージョンを返す。

＿◇集合関数
│　GROUP BY節を含めば、該当グループについてグループされ、その中で集合関数が適用される。
│　GROUP BY節を含まなければ、全レコードについてグループ化されたものとみなす

①AVG(expr)
│　平均値を返す
例）
SELECT student_name, AVG(test_score)
FROM student
GROUP BY student_name;

②BIT_AND(expr)
│　exprに含まれる全ビットのAND集合を返す。
│　６４ビット精度
③BIT_OR(expr)
│　exprに含まれる全ビットのOR集合を返す。
│　６４ビット精度
④COUNT(expr)
│　行の数を返す
※COUNT(*)は、一つのテーブルから１つだけカラムが選択され、WHERE節がなければ最適化されて高速に動作する
例）
SELECT COUNT(*) form student;

⑤MIN(expr) / MAX(expr)
│　最大値、最小値を返す。文字列の値でも計算できる。
⑥SUM(expr)
│　総和を求める。該当レコードがなければNULL
⑦STD(expr) / STDDEV(expr)
│　標準偏差を返す

※STDDEVはOracleとMySQL互換

＿◇Query
│　問い合わせ。データを検索する
│　(DMLに分類される）

※SELECT データ照会

例）
SELECT id, title, add_time FROM review;

※データベースの３操作
│　選択、射影、結合

※射影
│　特定のデータ列の取り出し

例）
SELECT 商品名, 単価 FROM 顧客

※選択
│　特定のデータ行の取り出し

例）
SELECT * FROM 顧客
WHERE 姓=’桜庭’ OR 姓=’abc’

※結合
│　２つ以上のテーブルデータの結びつけ
│　⇒共通するものを介して新規の行列を作る

例）
SELECT 姓, 名, 商品名, 単価,
FROM 購入履歴, 顧客, 商品
WHERE 購入履歴.顧客ID=顧客.顧客ID
AND 購入履歴.商品ID=商品.商品ID

＿◇DDL
│　Data Definition Language
│　データ定義。表の作成や削除を行う
│　CREATE, DROP, ALTER

※CREATE TABLE テーブル作成
例）

CREATE TABLE review (
    id MEDIUMINT(8) UNSIGNED DEFAULT '0' NOT NULL AUTO_INCREMENT,
    title VARCHAR(250),
    detail TEXT,
    addtime DATETIME,
    PRIMARY KEY (id)
);

※DROP TABLE テーブル削除

※ALTER TABLE テーブル定義一部変更

※CREATE VIEW ビュー作成

※DROP VIEW ビュー削除

＿◇DCL
│　アクセス権限など管理する
│　GRANT, REVOKE

＿◇DML
│　Data Manipulate Language
│　データ操作。データを追加、変更、削除する
│　INSERT, UPDATE, DELETE

※INSERT データ追加

※UPDATE データ修正

※DELETE データ削除

＿◇トランザクション制御
│　データ変更の確定、取り消しを行う
│　COMMIT, ROLLBACK

※COMMIT トランザクションコミット

※ROLLBACK トランザクションキャンセル

＿◇データベースの作成、選択、削除

①データベースの作成
│　> CREATE DATABASE データベース名;
※mySQL
> CREATE DATABASE IF NOT EXISTS データベース名;

②データベースの確認
│　> SHOW DATABASES;
③使用データベースの選択
│　> USE データベース名;
※セッションごとに使用データベースは設定する
※選択すると、クエリ内でデータベース名を省略できる。※USEしなくても、名前修飾によりアクセス可能。

④指定データベースと含まれる全てのテーブルの削除
│　> DROP DATABASE データベース名
※mySQL
> DROP DATABASE IF EXISTS データベース名

＿◇テーブルの作成

> CREATE TABLE テーブル名 (
    フィールド名 データ型,
    ...
);

> CREATE [TEMPORARY] TABLE [IF NOT EXISTS]
  テーブル名 [(作成定義, ...)]
  [テーブル定義]
  [SELECT構文];

※テーブル名
│　テーブルの名前を指定する

※作成定義
│　フィールド名データ型制約
│
※制約＝プライマリーキー、NULL認めるかなど
│　AUTO_INCREMENT
│　一つのテーブルに一つだけ
│　インデックス指定が必要
│　INDEX
│　インデックス指定
│　NOT NULL
│　フィールドの値としてNULLを許可しない
│　 INDEX, UNIQUE, PRIMARY KEYには必須
│　PRIMARY KEY()

※テーブル定義
│　MySQL独自拡張部

※SELECT構文
│　選択したテーブルのフィールドで新しいテーブルを作成し、値をコピーする

例）

> CREATE TABLE review (
    id MEDIUMINT(8) UNSIGNED
        NOT NULL AUTO_INCREMENT,
    title VARCHAR(250),
    detail TEXT,
    add_time DATETIME,
    PRIMARY KEY (id),
    INDEX (title)
);

> CREATE TABLE note AS
    SELECT id, title FORM review;

※テーブルの確認
│　> SHOW TABLES;

※テーブルのフィールドの確認
│　> SHOW FIELDS FROM テーブル名;
│　⇒DESCRIBE テーブル名
│　でも同じことが行える

＿◇テーブルの参照
│　どのデータベースにあるテーブルかを明確に指定する必要がある場合は、名前の修飾を行う
│　データベース名.テーブル名
※テーブル名構文
テーブル名 [[AS] エイリアス名][USE INDEX（キーリスト)][IGNORE INDEX(キーリスト)]

＿◇テーブル構造の変更

> ALTER TABLE テーブル名
    ADD フィールド名 属性
    CHANGE 旧フィールド名 旧属性 新フィールド名 新属性
    MODIFY フィールド名 属性
    DROP フィールド名

例）
ALTER TABLE review ADD genre TINYINT(3) UNSIGNED DEFAULT 0 AFTER id;

※AFTER フィールド名
│　⇒フィールド名の後に追加する

※FIRST
│　⇒指定したフィールドの前に追加

＿◇テーブルの最適化
│　> OPTIMIZE TABLE テーブル名
※レコードの大量の削除、可変長行の多くの変更の場合に行うとよい

＿◇テーブルの削除
│　> DROP TABLE [IF EXISTS] テーブル名
※テーブルのデータも削除されてしまう

＿◇フィールドの属性

TINYINT(X)
│　TINYINT整数型

MDEINUMINT(X)

VARCHAR(X)

TEXT

DATETIME

UNSIGNED

DEFAULT X
│　値が指定されなかったらデフォルトでXの値をもつ

AFTER フィールド名
│　フィールド名の後に該当フィールドを置く

＿◇データの挿入 insert
> INSERT [INTO] テーブル名 VALUES(値1,値2,…);

> INSERT [INTO] テーブル名
-> SET フィールド名1=値1, …

※SETを使ったINSERTの場合、全フィールドを指定する必要はない

①VALUES
│　テーブル名の後に指定したフィールド名の順番どおりに値を列挙
│　省略した場合は、定義時の宣言の順番どおりに値を列挙

> INSERT テーブル名 (フィールド名1, フィールド名2,…) VALUES (‘値’, ‘値’,…);

②SET
│　必要なフィールド名と値のペアを列挙する

> INSERT テーブル名 SET フィールド名=’値’,…;

③SELECTを使った複数のレコードの追加

> INSERT テーブル名 (フィールド名,…)
-> SELECT フィールド名リスト
-> FROM テーブル名リスト
-> WHERE 検索条件

※INSERT構文での式の利用
│　式項としてのフィールド名は、それを参照するフィールドよりも先に宣言されていないとならない。

※クエリはORDER BYを含むことができない
※INSERTのターゲットとなるテーブルはSELECTのFROMに指定できない

※キーワード
INTO 省略可能
LOW_PRIORITY
IGNORE
DELAYED

＿◇データの挿入2 REPLACE
│　INSERTと似ている
│　PRIMARYなどのユニークなフィールドへの価の重複がある場合
│　⇒古いレコードを削除してから新しいレコードを挿入する
│　⇒値の指定がないフィールドはデフォルト値かNULLとなる。

> REPLACE テーブル名 VALUES(値1,値2,…);

> REPLACE テーブル名 SET フィールド名1=値1, …

＿◇データの表示と検索 select

> SELECT フィールド名, … FROM テーブル名 WHERE 検索条件

例）
> SELECT title FROM review WHERE id=2;

> SELECT [オプション1] フィールド名, … FROM テーブル名 [オプション2]

※フィールド名
│　フィールド名はいくつ指定してもよく、「*」を使うことで全てのフィールドを指定することもできる
│　⇒フィールド名は最初に見出しとして出力されるが、これを別名に変更できる
│　フィールド名 AS エイリアス名
│　⇒テーブル名にも AS エイリアス名は有効

※オプション1
ALL
│　選択された全ての行を返す。
DISTINCT
│　重複行があれば１行だけを選択して返す。

※オプション2
GROUP BY
│　選択された行をフィールドの値や式の値をもとにグループ化
│　各値に対応した要約情報（他のフィールドの集計など）を各１行づつもどす。
│　ASCとDESCでソート順を指定できる
│
FOR UPDATE
│　選択した行をロックする

LIMIT
│　SELECTの戻す行数を指定する。
│　引数が一つなら行の最大数。
│　２つなら最初は行オフセット、２つめが行の最大数。

ORDER BY
│　指定したフィールドの値でソート
例）
> SELECT sei, age FROM customer
-> ORDER BY age DESC;

※複数のフィールドをカンマで区切って並べた場合、指定の先頭フィールドから順に並べられる
※デフォルトは ASC で昇順。降順のときはDESCを指定。

WHERE
│　条件がTRUE となる行だけを選択

LIKE
│　WHEREの条件式内でワイルドカードを使った文字列検索に向く

HAVING
│　取り出したデータについて条件づけする

INTO OUTFILE/DUMPFILE
│　選択した行をファイルに書き込む。既存のファイルには書き込めない。
│　⇒LOAD DATA INFILE構文で読み込むことができる
│　⇒エスケープキャラクタ、ASCII-0などはエスケープされる。
│　⇒DUMPFILEは1ファイル1レコード。BLOBの格納に向く

例）
SELECT * INTO OUTFILE “a.txt” FROM customer;

例）CSV形式のファイルの生成
SELECT * INTO OUTFILE “c:\\var\\a.csv” FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘ LINES TERMINATED BY “\n” FROM table;

＿◇WHERE構文の詳細

> SELECT フィールド名 FROM テーブル名 WHERE 条件式

①マッチしたレコードの取り出し
│　比較演算子による大小比較も可能
│　複数条件は論理演算子により可能
②LIKEによりワイルドカードが使える
│　% … 任意の文字列
│　_ … １文字の任意の文字

※比較演算子
フィールドの値を指定して、それにあったものだけを選択する。
│　= 等しい
│　> 大きい
│　< 小さい
│　>= 大なりイコール
│　<= 小なりイコール
│　!= 等しくない
│　<> 等しくない

※論理演算子
ANDとORを組み合わせて複数の条件を指定する
│　AND 論理積
│　OR 論理和
│　NOT 反転
⇒NOTを使う場合、式をカッコで囲わないと、直後のフィールド名の反転がとられてしまうので注意

※LIKE演算子
│　ワイルドカード検索

例）
> SELECT * FROM customer WHERE sei LIKE ‘高%’;

⇒%や_などのワイルドカード文字自体を検索対象にするためには\をつかってエスケープ処理する

※BETWEEN演算子
│　範囲指定
│　対象値 BETWEEN 最低値 AND 最大値

>SELECT sei, age FROM customer
WHERE age BETWEEN 30 AND 35;

※IN演算子
│　値がINの後に指定したリストの中にあるかどうか

>SELECT sei, age FROM customer
WHERE age IN(32, 30);

＿◇WHEREによるリレーションの作成

※FROMで複数のテーブルを指定し、WHEREでテーブル間のリレーションを張る

①テーブルの結合
│　両方のテーブルに共通するフィールドを「=」で結びリレーションを張る

例)
> SELECT id_p, sei, mei FROM purchase, customer
WHERE puchase.id_c = customer.id_c;

例）
> SELECT title, value FROM review, m_review_genre
> WHERE review.genre=m_review_genre.id;

※複数のテーブルで同じフィールド名が使われている場合には、名前が衝突するのでテーブル名を指定する。
※２つ以上のテーブルであっても同様に行える
※AS を使ったテーブル名のエイリアス
│　テーブル名の後に AS エイリアス名とすることで、エイリアスを定義することができる

例）
> SELECT id_p, sei, mei, name
> FROM purchase AS p, customer, goods
> WHERE purchase.id_c = customer.id_c
> And purchase.id_g = goods.id_g;

②再帰結合（自己自身との結合）
│　テーブル名を利用して１つのテーブルを２つのテーブルのように扱う

例）
> SELECT C1.sei, C1.sex, C2.sei, C2.sex
> FROM customer AS C1, customer AS C2
> WHERE C1.sex = C2.sex
> AND C1.id_c < C2.id_c;

⇒AND条件により、同じ組み合わせで順序のみが異なるものを落としている

＿◇GROUP BYによる集計

①AVE 平均値を求める
②COUNT レコード数を求める
③MIN 最小値を求める
④MAX 最大値を求める
⑤SUM 総和を求める

※集計関数を使う場合は、GROUP BYを使ってグループ化する必要がある

例）
> SELECT name, SUM(price * quantity) AS total
> FROM purchase, goods
> GROUP BY name;

※HAVINGによる選択データへの条件式の適用
⇒集計後に条件を適用したいときに使う

例）
> SELECT name, SUM(price * quantity) AS total
> FROM purchase, goods
> GROUP BY name;
> HAVING total > 200000;

＿◇JOINによるテーブルの結合

※テーブルの単純結合
│　FROMでテーブル間をカンマでつなぐことで行える

※JOIN
│　カンマと同義であり、テーブル間の直積をとる
│　⇒より細かな制御をするための構文がある

※JOIN条件式
│　ON 条件式
│　USING (フィールド・リスト)

①LEFT JOIN ON
│　通常のテーブル結合
│　一方のテーブルにマッチしないレコードがあると表示されない
│　LEFT JOIN ONでは、右側がNULLでも表示される。
│　条件式はWHEREの代わりにONの後に書く

テーブル名 LEFT [OUTER] JOIN テーブル名 ON 条件式

※OUTERは省略可能なキーワード

例）
> SELECT id_p, sei, mei
> FROM customer LEFT JOIN purchase
> ON purchase.id = customer.id;

②USING
│　両方のテーブルに存在し「=」であるフィールドを指定する。
│　USING (F1, F2, F3,…)
③NATURAL LEFT JOIN
│　USINGでテーブルに共通するフィールド名を指定したINNER JOINやLEFT JOINと同じ
④STRAIGHT_JOIN
│　つねに左側のテーブルを先に読むJOIN。まれな不当順序においてのみ有効
⑤INNER JOIN
│　「,」と同義。
⑥RIGHT JOIN
│　つねに右側のテーブルを必ず出力させようとする

※混乱を招くので推奨されない

＿◇データの更新 update
│　レコードのフィールド値を更新する
│　SETで更新するフィールドと値を指定し、
│　WHEREで更新するレコードを選択する

> UPDATE テーブル名 SET フィールド名=値, … [WHERE 条件]

※UPDATE構文からWHEREを省略すると、全てのレコードに対して更新が行われてしまう！

※オプション
LOW_PRIORITY
WHERE
LIMIT 更新するレコード数を指定する
IGNORE PRIMARYやUNIQUEなフィールドで値の衝突があってもエラーにしない（更新も行われない）

例）
> UPDATE goods SET price = 26000 WHERE id_g=2;

> UPDATE goods SET price = price * 1.05;
│　⇒式をつかって更新もできる
│　⇒式は左から右に評価される

＿◇データの削除 delete
│　行を削除する。１度削除したデータは下に戻すことができない。削除されたレコード数を戻す

> DELETE FORM テーブル名 [WHERE 条件]

※DELETE構文からWHEREを省略すると、全レコードが削除されてしまう！

例）
> DELETE FROM review WHERE id=1;

※ORDER BY と LIMIT を組み合わせて行頭もしくは、行末から指定行数のデータを削除することもできる

例）日付の古いものから１０件削除
> DELETE FROM goods
-> WHERE id_g > 0 ORDER BY add_time LIMIT 10;

※キーワード
LOW_PRIORITY
QUICK
WHERE
ORDER BY
LIMIT

◆トランザクション管理
│　障害によるデータ矛盾発生からの保護

※トランザクション
│　データベースに対して行う変更処理
│　⇒１つだけでなく複数のＳＱＬ文で一つの目的を果たせることが多い。
│　⇒この作業単位をトランザクションと呼ぶ

※コミット
│　トランザクションでの変更処理をデータベースで確定

※ロールバック
│　コミットしていない変更を取り消す操作。
│　ユーザが明示的に行う場合もあれば、自動的に行われる場合もある

※トランザクションは必ずコミットかロールバックで終了する

※OracleとSQL Serverの違い
│　Oracleは明示的にコミットする
│　SQL Serverは何も宣言しないと変更処理を実施するたびに自動コミットする
│　⇒トランザクションには
│　 BEGIN TRANSACTION
│　宣言が必要⇒COMMIT TRANSACTION, ROLLBACK TRANSACTION

◆ロック
│　同時に複数のユーザが同じデータにアクセスする取り扱い順序を制御

※先に要求を出した方の処理が終わるまで、後のものを待たせる。
※二重更新によるデータ矛盾の発生を防ぐ
※ロックは自動的に行われる（明示的にロックすることもできる）
※変更対象の行にのみロックがかかる
※通常の参照処理ではロックされず、また、ロックされている行を参照しても待機状態にはならない
│　⇒FOR UPDATEオプションでロックかけることができる
│　⇒FOR UPDATE NOWAIT
│　ロックかけられないとエラーが返るので誰かがロックしていることが分かる

＿◇デッドロック
│　複数のトランザクションがお互いにロックをかけ合ってしまって動きがとれなくなる状態
※Oracle
│　デッドロックが発生すると一方のトランザクションにエラーコードが返る。
│　エラー側ではロールバックしてやり直すことが必要

◆Oracle

＿◇Oracleデータベースの構成

①データファイル
│　データ格納の実体。
│　通常は複数のデータファイルからデータベースは構成される。
│　一つのデータファイルに複数の表を入れることも、
│　一つの表を複数のデータファイルに格納することも可能。
※データファイルの総容量がデータベースに格納できる容量となる
※表にアクセスするときには、データベースのどこの物理的な場所に何があるかを知る必要がなく、表名と列名が分かっていればアクセスできる
※ファイルはサイズを決めて作成。作成直後は空。データを追加してデータファイルがいっぱいになると、領域不足エラーが返る
│　→新規データファイルの追加
│　→既存データファイルのサイズ変更
│　データファイルの自動拡張機能あり→ただしこれはエラー回避のための保険。
│　データサイズの拡張は負荷が重いので負荷の低い時間に手動で行う方が良い。

※データファイルには、通常の表以外のオブジェクト、索引、UNDOセグメント（ロールバックセグメント）、一時セグメントが格納される）

②オンラインREDOファイル
│　障害時の復旧のために履歴を書き込む
│　最低２グループは必要。一つのファイルに書き込み、サイズを使い切ると次にスイッチするため
│　→ログスイッチ
│　→ログ順序番号がスイッチ毎に＋１される

※アクセスの実負荷を観察しないと、どの程度のサイズが必要か、どれだけの書き込みかを見当をつけることは難しい。
※ログファイルへの書き込みは停止できない
※サイズ変更のためには削除して作り直すことが必要。ただし、２グループでは先にファイルを削除できないので、追加してからでないと削除できない
※通常、ＲＥＤＯファイルは多重化する。同じ内容を２ファイル以上に書き込む
│　→同じ内容を保持するもの。。。グループ
│　→各ファイル。。。メンバ

※アーカイブＲＥＤＯログファイル
│　オンラインＲＥＤＯファイルはグループで循環して上書き使用される
│　上書きする前にログスイッチ発生時に自動でコピーを作成することができる

③制御ファイル
│　物理構造の管理
│　データベースのファイルの所在、データベースの名前、作成日時など
│　→制御ファイルも多重化できる

＿◇データディクショナリ
│　データベース内の情報を保持管理している表の集合

例）物理データベースを構成しているファイル名などの調査

SQL> SELECT file_name, bytes FROM dba_data_files;
│　他にv$logfile, v$controlfiles

＿◇データベースの論理構造

①表領域
│　データファイルを論理的にグループ化（最低一ファイル）し、名前をつけたもの。
│　→すべてのデータファイルは必ずいずれかの表領域に属する。
│　表の作成時にその表を格納する表領域を指定する。
│　→ひとつの表領域に複数の表を格納できる
│　（表がどの物理データファイルに格納するかを意識する必要はない）
│　→表領域のサイズはデータファイルの合計サイズ
│　→足らなくなった場合、データベースを停止せず、データファイルを追加することが可能
│　→複数の表領域に分けることで、障害対処時、メンテナンスの作業性を良くすることができる。
例）バックアップの取得は表領域レベル（バックアップ時間はデータファイルのサイズに依存する）

※表領域の種類
│　SYSTEM表領域 Oracleが内部的に使用するデータオブジェクト、データディクショナリなどを格納する。 SYSAUX表領域 Oracle10g以降。SYSTEM表領域の補助
│　一時表領域ソートで使用される
│　UNDO表領域ロールバックのため、変更前の情報を格納する。Oracle9i以降
│　ユーザデータ用表領域

②セグメント
│　論理的な単位。一つの表は１つのセグメント。
│　セグメントには最低１つのエクステントが含まれる。
│　表が獲得したエクステントの集合
│　エクステントは表領域内で連続しているわけではない
│　データセグメント（表）
│　索引セクメント
│　ＵＮＤＯセグメント（ロールバックセグメント）
│　１時セグメント
③エクステント
│　連続したデータブロックの集合
│　領域はエクステント単位で確保され、ブロック単位に使われる
│　データ追加により自動的に次のエクステントが確保される。
│　エクステントを構成するデータブロック数は表領域の設定による
④データブロック
│　論理構造の最小単位
│　複数の行データを格納できる
│　使用領域と空き領域
│　行の追加によりブロックが満杯になると次のブロックに
│　データブロックのサイズ
│　データベース作成時に決定
│　２ＫＢ～３２ＫＢ
│　行データ＋ヘッダ領域

＿◇Oracleインスタンス
│　データを読み込むメモリ領域、プロセスなどを総称してインスタンスと呼ぶ

※System Global Area (SGA)
│　データベースを使用するユーザが共有して利用するメモリ領域
①共有プール
②データベースバッファキャッシュ
③ＲＥＤＯログバッファ

※他に
│　Javaプール
│　ラージプール
※複数のOracleプロセス
※バックグラウンドで動くOracleプロセス

①データベースライタープロセス (DBWn)
②ログライタープロセス(LGWR)
※ひとつのOracleインスタンスは１つのOracleデータベースに対応する
│　１インスタンス＝１データベース
│　→シングルデータベース
│　複数のインスタンス＝１データベース
│　→ＲＡＣ(Real Application Clusters)

＿◇SQLの処理ステップ

①ユーザプロセス
│　SQLを発行（サーバプロセスに送信）
│　SQL*PlusやVBなど

②サーバープロセス（Oracleプロセス）
│　ＳＱＬ要求を受付
│　 PARSE
│　構文チェック
│　表、列の存在チェック
│　権限チェック
│　データアクセス方法の決定
※一度参照された情報は、共有プール内のディクショナリキャッシュに保持される
※ＳＱＬの解析結果は、共有プール内の共有ＳＱＬ領域に残す
│　→同じＳＱＬ文であれば再利用される
│　ＳＱＬ文の文字列が完全に一致しなければならない
│　（大文字、小文字の区別も影響するので、ＳＱＬの書き方の統一が必要）
│　ＳＧＡ内で処理
│　 EXECUTE
※まず、データベースバッファキャッシュ内を検索し、無いときはデータファイルから読み込む
※読み込まれたデータは実行後もデータベースバッファキャッシュに残される

※メモリ上キャッシュはＬＲＵアルゴリズムで管理されており、もっとも使用されていないデータを掃きだす
│　ＳＱＬの結果をユーザプロセスに返す
│　 FETCH
│　→戻すのはSELECTのときのみ

※ＳＧＡのサイズは、初期化パラメータファイルのパラメータでバイト単位で指定する。
│　１０ｇ以降は全体サイズの中で自動的に決めることもできるようになった

＿◇データベースライタープロセス(DBWn)
│　メモリから掃きだされるか、チェックポイントのときにデータベースに書き込みを行う
│　→コミットされても直ぐに書き込みがおきるわけではない。

＿◇Redoログバッファ
│　データベースバッファキャッシュ上での変更はすべてＲＤＯログバッファ上のＲＥＤＯエントリに書き込まれる。
│　メモリ上のエントリがある程度たまると、ＬＧＷＲにより、ディスクのオンラインＲＥＤＯログファイルに書き込まれる

※トランザクションがコミットされると書き込みが行われる。

＿◇ALERTファイル
│　管理作業、エラー内容が発生順に書き込まれる
│　障害時にまず参照すると原因究明できる
│　どんどん大きくなるので、バックアップをとって定期的に切り捨てる必要あり

＿◇インスタンス障害
│　サーバマシンなどのダウン
│　停電などのトラブル→メモリ上のデータ失われる
│　→インスタンスでの処理を継続できない
※インスタンス障害からのリカバリは、データベースの再起動時にOracleが自動的に行う
※データの更新処理の詳細ステップ
│　まず、変更履歴をＲＥＤＯログバッファに格納
│　変更前の値をＵＮＤＯセグメントに格納
│　データベースキャッシュを新値で上書き
※ROLLBACKコマンドを実行してトランザクションを取り消した場合、ＵＮＤＯセグメントの値で変更前の値に戻す
※Oracle8iまではロールバックセグメント。9iからはUNDOセグメント。管理面等での使いでが改善
※ＬＧＷＲ（ログライタープロセス）
│　一つのコミットが起こると、その時点での全てのＲＥＤＯログバッファの変更履歴がオンラインＲＥＤＯログファイルに書き出される
│　よって
│　コミットしたトランザクションは必ず
│　まだコミットされていないものもその時点で
※ＤＢＷｎ（データベースライタープロセス）
│　データベースバッファキャッシュ上の変更済みデータはコミットに依存しないタイミングで書き込まれる
│　チェックポイント
│　（全変更済データが書き込まれる）
※インスタンス障害からのリカバリ
│　ロールフォーワード
│　オンラインＲＥＤＯログファイル内のＲＥＤＯエントリを使って障害発生直前の状態まで戻す。
│　（必要なデータはデータファイルから読み込まれ、変更履歴が適用されてＵＮＤＯセグメントにも値が入る）
│　メモリ上のコミット済データが失われていてもそれ以前に書き込まれているので確実に戻る
│　適用する変更履歴は、前回のチェックポイント以降のもの
※オンラインＲＥＤＯログファイルの９０％サイズ以上の変更履歴を書き込むと強制的にチェックポイントが起こる
│　オンラインＲＥＤＯログファイルは上書き利用されていても、必要な変更履歴が無いということはない

ロールバック
│　コミットされていない変更を取り消す作業
│　ＲＥＤＯエントリにはコミット前の履歴も含まれるため

＿◇メディア障害
│　ファイルに起きた障害。
│　管理者による手動の対処が必要
│　バックアップとリストアが基本
※クローズドバックアップ
│　（オフラインバックアップ、コールドバックアップ）
│　データベースを正常停止させてバックアップ
│　ファイル間の整合性が保たれるがサービス停止が必要
※オープンバックアップ
│　（オンラインバックアップ、ホットバックアップ）
│　データベースを停止させずにバックアップ
│　→表領域レベルでバックアップモードに変更
│　（Oracle10gではデータベース全体をバックアップモードに入れることができる）
│　→バックアップモードに入っていない表領域のデータファイルをバックアップしてはならない
│　→データベースはARCHIVELOGモードの場合のみ可能
※バックアップファイルをリストアしただけではバックアップ時点にしか戻れない
│　→リカバリ作業 REDOログファイル
※データベースの設定
│　ARCHIVELOGモード
│　上書きされる前にオンラインREDOログファイルを保存する運用（アーカイブREDOログファイル）
│　メディア障害時にリカバリ可能
│　 →アーカイブREDOログファイルの容量に注意
│　NOARCHIVELOGモード
│　オンラインREDOログファイルだけで運用
│　メディア障害時にリカバリ不可能
│　（バックアップ後にオンラインＲＥＤＯログファイルが一度も上書きされていなければ可能）
※オンラインREDOログファイルの多重化
│　最終的に必要になるファイルなので、これに障害があると最新状態まで復旧できない。
│　また、ログライタープロセスが書き込みできないとデータベースは異常終了する
│　→オンラインREDOログファイルを多重化するとともに、異なるディスクに配置する
※制御ファイルの多重化
│　データベースの構造を記録する制御ファイルも障害に備えて多重化することが推奨される
│　→障害発生しても正常なものから再生成できる
│　→多重化しても障害発生すると異常終了する
│　⇒制御ファイルはOracleだけでなくRAIDによるミラー化が推奨される

＿◇リカバリ方法
①ALERTファイルから破損したファイルを確認
②バックアップファイルのリストア
③アーカイブＲＥＤＯログファイル等の順次適用

※完全リカバリ
│　バックアップのリストアとＲＥＤＯ適用でデータの損失がない状態にできる方法

ユーザの表などが格納されているデータファイル破損
│　⇒完全停止せずにリカバリできる
│　破損ファイルと破損ファイルを含む表領域をオフラインとして、その部分だけリストア、リカバリする
SYSTEM表領域, UNDO表領域への障害
│　⇒停止状態で行う必要あり

リストアするのは破損したデータファイルのみ。ログのリセットは不要

※不完全リカバリ
│　データの損失がありえる（途中でリカバリを中断する
│　停止して作業する必要あり
│　⇒必要なアーカイブＲＥＤＯログファイルの破損
│　⇒ユーザの誤操作で完全リカバリすると意味がない
│　（誤操作直前までのリカバリをしてそこで止める。誤操作以降は復旧できない）

※バックアップしたデータファイル全てのリストア必要
│　⇒一部ファイルだけ、昔の状態で止まってしまうと不整合が起こる
│　データベース全体を過去のある時点まで戻し、
│　アーカイブＲＥＤＯログファイルを適用して、
│　全体の整合性をとる必要がある

※ログもリセット
│　ログ情報にリカバリ以降の操作も含まれる
│　これを取り除かないと整合性がとれない
│　⇒RESETLOGSでデータベースのログ情報を消去、インカネーション（バージョン）を新しくする
│　⇒データベースのバックアップも必要になる

＿◇論理バックアップ
│　物理的なデータファイルのバックアップ以外の論理単位でのバックアップ。
│　一部の表だけの復旧用途など

※エクスポートユーティリティでデータをＯＳファイルに抽出する

※インポートユーティリティで読み込める
│　⇒リカバリでなく、再生成

＿◇フラッシュバック
│　ユーザエラーの対処などのために、リカバリよりも軽い手段で復旧できることもある

フラッシュバッククエリ
│　ＵＮＤＯ表領域必須
フラッシュバックドロップ
│　Oracle10g以降では、表を削除してもその領域が必要になるまではデータが残っている。これを元に戻す。
フラッシュバックテーブル
│　ＵＮＤＯ表領域必須
フラッシュバックデータベース

＿◇データベースユーザと権限

※SYS, SYSTEM
│　管理ユーザ
│　データベースを管理するためのユーザ。あらゆる操作を行える。

※一般ユーザ
│　管理ユーザが作成する
│　作成直後には、操作は一切許可されていない
│　権限の付与が必要

※システム権限
│　データベースで特定の操作を行う権限
│　管理ユーザが付与する
│　 CREATE SESSION権限
│　接続する権限
│　 CREATE TABLE権限
│　表を作成する権限

※オブジェクト権限
│　表などのオブジェクトに対して特定の操作を行うための権限
│　オブジェクトの作成者が付与する
│　 SELECT
│　 INSERT
│　 UPDATE
│　 DELETE

＿◇ロール
│　複数の権限をグループ化し、ユーザに付与できる
│　管理ユーザがロールを作成し、システム権限を付与
│　オブジェクト作成者がオブジェクト権限をロールに付与
│　ロールを必要なユーザに付与。
※ロールを変更すれば、付与されている全てのユーザの権限が変わる。

※事前定義のロールがある
│　DBAロール
│　CONNECTロール

＿◇プロファイル
│　ユーザに対する制限事項を設定する
※パスワード制限
│　失敗許容回数、有効期限など
※システムリソース制限
│　１セッションあたりの接続時間、ＣＰＵ時間など

＿◇データへのアクセス制御
│　SELECT権限では無制限にどのデータにもアクセスできてしまう
│　⇒一部データへのアクセスを制御する方法がある

※ビュー
│　表の特定範囲（特定の列を除くなど）にアクセスできるデータベースオブジェクト。
│　表の変わりにビューの名前をＳＱＬで指定してデータを参照できる。
│　当然ビューの範囲にのみ参照できる

※VPD
│　Virtual Private database
│　アクセス先は元の表のまま
│　ユーザの登録情報に従って、ユーザが参照できる範囲をOracleが自動的に制御する

＿◇データの暗号化

※DBMS_OBFUSCATION_TOOLKIT

※DBMS_CRYPTO
│　Oracle 10gより実装

※Transparent Data Encryption (TDE)
│　Oracle 10g R2
│　透過的なデータ暗号化

＿◇監査
│　データベースで行われた作業を監視して記録する「監査」機能
│　⇒監査対象としているＳＱＬ文が発行されると、その情報を特別な表に記録する。
│　管理ユーザはユーザのアクセス履歴を確認できる

①必須監査
│　管理ユーザのログイン、データベースの起動、停止などは監査の設定に関わらず常に監査される
②ＤＢＡ監査
│　データベース管理者が行った全ての操作を監査する
③標準監査
│　特定のＳＱＬ文や、特定のオブジェクトに対する操作を監査する

※ファイングレイン監査
│　Oracle9i以降。特定の行、列など監査範囲を細かく設定できる。

＿◇データアクセス

①フルテーブルスキャン（全表走査）
│　ＳＱＬで指定された表のすべての行（ＨＷＭまで）にアクセスし、条件にあう行を探す
│　複数ブロックをまとめて読み込むマルチブロック読み込みをする（デフォルトでは８ブロック）
│　⇒選択する行数が多い場合は、インデックススキャンより高速になる

※ＨＷＭ
│　ハイウォーターマーク
│　これまでにデータが挿入されたことのある最後のブロック。
│　ＤＥＬＥＴＥで行が減ってもＨＷＭは変化しない

②インデックススキャン（索引走査）
│　索引を使って、条件を満たす行のアドレスを得て、アクセスする
│　表の中で僅かな割合の行にアクセスするには効率的
│　データはシングルブロック読み込みなので、大量のデータに適用するとかえって速度が遅くなる

※索引
│　表の列について予め作成する必要がある
│　行のアドレスはＲＯＷＩＤと呼ばれる
│　列の値はソートされる
│　追加、削除に応じて自動的にメンテナンスされる

③アクセス方法の決定⇒オプティマイザ
│　オラクル１０ｇ以前はルールベースオプティマイザがあった
│　現在はコストベースオプティマイザのみ
│　→統計情報をもとにスキャン方法を選択する
│　（時々統計情報を更新する必要がある：１０ｇからは自動収集）

④ヒントの使用
│　ＳＱＬ文にヒントを埋め込むことで明示的にアクセス方法を指定できる。

＿◇ユーティリティツール

※SQL*Plus
│　開発、テスト用のSQLを直接記述して実行できるアプリケーション

※Export/Importユーティリティ
│　データの抽出、抽入。
│　データベース、表領域、ユーザ、オプジェクトなどの各レベルで行える。
│　ファイルは独自形式。
│　データベース間のデータの移行やバックアップに使われたり、表の再生成による表データのフラグメンテーションの解消にも使われる

※Data Pump Export/Importユーティリティ
│　Oracle10g以降の性能に優れるもの

※SQL*Loader
│　テキスト形式で保存されたデータのデータベースへの挿入。
│　ＣＳＶ形式などを挿入。SQL文で一つ一つINSERT処理するより高速
│　⇒データベースのデータをテキストファイルへ抽出
│　 PL/SQLユーティリティパッケージのUTL_FILE
│　 SQL*PlusのSPOOLコマンド

※RMAN
│　Recovery Manager
│　バックアップファイルの管理
│　バックアップ領域の節約

※ＧＵＩツール
│　Oracle Universal Installer (OUI)
│　Database Configuration Assistant (DBCA)
│　Net Configuration Assistant (NetCA)
│　Oracle Enterprise Manager (EM)
│　ブラウザベースでデータベースを管理
│　 Export/Import, RMANなどの操作も可能

＿◇DBA
│　データベース管理者

◆MySQL
│　ソフトエージェンシー製品

＿◇起動
│　mysql -u ユーザ名 -p
│　で起動するとパスワードを聞いてくる
※データベースの選択
> use mysql
│　⇒mysql データベースが選択される
> show tables;
│　⇒データベースにある全テーブルが表示される
※オプション
│　-A データベース名

＿◇MySQL コマンドプロンプト

※切断
│　quit もしくは exit
│　(UNIX系なら^D, Windowsなら^Cでも切断できる）
※行末は「;」
│　途中で改行を挿入しても、複数行にわたってコマンド文を入力できる。
│　複数行にわたった入力中でキャンセルする場合は、\c+[Enter].
※コマンドで使われるキーワードは大文字、小文字を区別しない
※バージョンを問い合わせるクエリ
│　select version();
※コマンドプロンプト
│　mysql> 新しいコマンドの待ち
│　-> 複数行入力の次行待ち
│　’> 「’」で始められた文字の次行待ち
│　”> 「”」で始められた文字の次行待ち

＿◇ユーザの管理

※ユーザテーブルの表示
│　use mysqlの状態で
│　> select Host,User from user;
│　で、ユーザテーブルのHostとUserフィールドが表示される
※ユーザの削除
│　> DELETE FROM user WHERE Host=’localhost’ AND User=”;
※本日のユーザの表示
│　> select user(), CURRENT_DATE;
※ユーザのアクセス権設定
│　> GRANT ALL ON データベース名.* TO ユーザ名;
※ユーザ名は select user()で表示されるもの

例）
localhostから接続可能なアカウントを追加する例
ユーザ名: webuser
パスワード: p9z8y7
操作を許すのは shop データベース

>GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO webuser@localhost
>IDENTIFIED BY ‘p9z8y7’;

※パスワードの変更
>SET PASSWORD FOR webuser@localhost = PASSWORD(‘mypasswd’);

＿◇MySQL固有のデータ型
①ENUM(‘value1’, ‘value2’,…)
│　最大６５５３５個までのCHAR型のリスト。１から６５５３５までの数値が固定長の文字列に変換される
②SET(‘value1′,’value2’,…)
│　最大６４個の要素。２進のビット列として格納される。
※最大８バイト

＿◇mysqladminツール

※データベースの更新
│　C\> mysqladmin reload

※rootユーザへのパスワード設定
│　C\> mysqladmin -u root password パスワード

※シャットダウン
│　C\> mysqladmin -p -u root shutdown

＿◇mysqlshowツール
│　C\> mysqlshow -u root -p
│　パスワード入力
│　で、データベースがリストされる

＿◇実装
│　データベースは、ディレクトリとして実装
│　テーブルはファイル

◆PostgreSQL

◆SQL Server

＿◇インストール

①認証
│　Windows認証、SQLサーバ認証
②saユーザのパスワード

＿◇設定
SQL構成マネージャ
│　ネットワークの構成
│　 TCP/IPプロパティ
│　 IPALL TCPポート1433（標準）
│　１台で複数走るときは変わる
⇒変更後有効化する必要がある
│　サービス
│　 SQL Server再起動

＿◇コマンドプロンプト

例）
> sqlcmd -H localhost -u sa

＿◇SSMSE
│　SQL Server Management Studio Epress
│　Masterデータベースなどシステムデータベースも見れる

＿◇SQL Server Brower
│　ネットワーク上にあるシステムにおけるSQL DBエンジンの名前つきインスタンスの場所を特定する。
※デフォルトではディスエーブル。不要
│　なお、このシステムは UDP 1434で報告

◆DB2

◆SQLite
│　パブリック・ドメイン
│　単一のディスクファイルで動作する
│　SQL92サブセット

※御本家 SQLite のホームページが以下に。
https://www.sqlite.org/

＿◇SQL Liteの受け付けるSQL文

() INSERT
> INSERT INTO ‘テーブル名’
> (カラムリスト) VALUES (値リスト);

() CREATE
> CREATE TABLE ‘テーブル名’ (
> カラム定義,
> …
> テーブルのコンストレイント,
> …
> );

※カラムの定義
カラム名型名カラムのコンストレイント
│　PRIMARY KEY … AUTOINCREMENT
│　NOT NULL
│　UNIQUE
│　DEFAULT
│
※テーブルのコンストレイント

＿◇SQL Lite3 コマンドラインツール

() .read ファイル名

() .tables

() .databases

() .help

＿◇Node-REDからのSQL Liteの使用記事

ブロックを積みながら(27) BBC micro:bitとラズパイでサウンドモニタ　その４

＿◇C言語から SQLite3の使用記事

ソフトな忘却力(30) SQLite3をC言語から使う。Amalgamationって何よ。