データベース(2)
データベース操作
関係代数演算
集合演算
和(union) A∪B
複数の表に含まれている全ての要素を含む集合。重複を取り除いた集合が返される
差(difference) A−B
AからBの属性を取り除いたもの
積(product)・共通(intersection) A∩B
AとBに共通する属性
直積(cartesian product) A×B
AとBの属性の組合せの集合
固有演算
結合(join) A
B
複数の表を共通の列を介して結び付け、新たに一つの表を作成する操作
射影(projection) πα(A)
列名とその並びを指定し、対象とする表から特定の列を抽出する操作
表Aからα列を抽出する
選択(selection) σα(A)
選択条件を指定し、対象となる表から条件をみたす行を抽出する操作
商(division) A÷B
表Bの属性を含む行を表Aから取り出し、表Bの属性以外の値を抽出する操作
SQL
SQL-DDL
リレーショナルデータベースのテーブルを制御する言語。テーブル全体の作成・変更・削除などを行う際に使用する
実表の定義(CREATE TABLE)
データベースにデータを格納するには、データベースの構造である表を定義する必要がある
| CREATE TABLE 表名 (列名1 データ型 1[列定義 1], (列名2 データ型 2[列定義 2], (列名3 データ型 3[列定義 3], ...) |
ビュー表の定義(CREATE VIEW)
ある実表をもとにして必要な項目だけを抽出して集め、仮想的に作成する表のこと
| CREATE VIEW ビュー名[(ビューの項目名，…)] AS SELECT 項目名，…FROM 表名 ※[ ]内は、必要に応じて記述。省略した場合、実表と同じ項目名となる |
使用する目的
●利用者にデータベース構造を意識させない
●正規化によって分割された表を、実際の利用形態に合わせて、見かけ上組み合わせることにより、データ操作を容易にする
●セキュリティの観点から、実表での権限を制限して、ビュー表に権限を付与する
ビュー表の権限
●何も指定しなければ元となる表に対する権限と同じ権限が与えられる
●ビュー表独自の権限を付与することが可能
●元となる表に対する権限以上の権限は与えられない
更新可能なビュー
●結合関係がない(単一表から構成されている)こと
●集合関数や演算を使用してないこと
●GROUP BY句が含まれていないこと
●DISTINCT句が含まれていないこと
●副問合せが含まれていないこと
導出表
関係データベースの操作によって作成される仮想的な表のこと。SQLにおけるビューも導出表の一種
埋込みSQL
C言語、Javeなどの手続型プログラミングに、関係データベースを操作する為のSQLを埋め込む手法。SQLステートメントはコンパイル実行前にSQLプリプロセッサによって前処理される
CURSOR(カーソル)をFETCH文の使用によって問合せ、得られた導出表を１行ずつ親プログラムに引出し、操作する
制約
整合性制約
PRIMARY KEY(主キー制約)
その列が行を一意に識別できること。値は重複してはならず、NULLも許可されない
CHECK(検査制約)
列の値が指定し合条件を満たすこと。(指定の範囲内になければならない)
FOREIGN KEY(参照制約)・REFERENCES(参照整合性制約)
外部キーとして登録される値が、参照する他表の列内に必ず存在しなければならない
UNIQUE(一意性制約)
指定した列で値が重複することは許されないが、NULLの存在は許す
NOT NULL
列の値がNULLでないこと。値入力は必須
CASCADE()
他のビューや制約が参照されていても、削除・更新される。親テーブルの更新・削除に合わせて子テーブルのデータも更新・削除される
SET DEFAULT
指定した列のデフォルト値を変更するときに使用
RESTRICT
指定した表がビューや整合制約により参照されたものである場合、変更はエラーとなり、実行されない
処理権限
PRIMARY KEY(主キー制約)
その列が行を一意に識別できること。値は重複してはならず、NULLも許可されない
CHECK(検査制約)
列の値が指定し合条件を満たすこと。(指定の範囲内になければならない)
FOREIGN KEY(参照制約)・REFERENCES(参照整合性制約)
外部キーとして登録される値が、参照する他表の列内に必ず存在しなければならない
UNIQUE(一意性制約)
指定した列で値が重複することは許されないが、NULLの存在は許す
NOT NULL
列の値がNULLでないこと。値入力は必須
CASCADE()
他のビューや制約が参照されていても、削除・更新される。親テーブルの更新・削除に合わせて子テーブルのデータも更新・削除される
SET DEFAULT
指定した列のデフォルト値を変更するときに使用
RESTRICT
指定した表がビューや整合制約により参照されたものである場合、変更はエラーとなり、実行されない
| 項目 | 説明 | |
|---|---|---|
| SELECT | 問合せ | 表を検索(参照)する権限 |
| INSERT | 追加 | 表にレコードを挿入する権限 |
| UPDATE | 更新 | 表の項目を更新する権限 |
| DELETE | 削除 | 表のレコードを削除する権限 |
| ROLE | ロール定義 | (ユーザ)グループに対しての権限 |
| ALL | 表に対するすべての権限 | |
権限設定
GRANT
ユーザまたはユーザグループに、テーブルに関する処理権限を与える
REVOKE
ユーザまたはユーザグループに、テーブルに関する処理権限を取り上げる
SELECT文
SQL-DML
実際のデータベースの操作を記述するもの
結合と射影、選択
| FROM | ：使用する表を指定 |
| 複数表を指定可能、別名を指定可能 | |
| WHERE | ：結合条件と選択条件を指定する |
集合関数
項目のデータの個数や平均などを計算する場合は、「集合関数(集約関数)」を使 用
| SELECT 集合関数(項目名) FROM 表名 |
| 名称 | 説明 | 設定例 |
|---|---|---|
| COUNT | 検索結果の行数を数える | COUNT(＊) ※すべてのレコード数を数える |
| COUNT(受注合計) ※項目の合計がNULLの行は数えない | ||
| SUM(項目名) | 項目の値の合計値を求める | SUM(受注合計) |
| AVG(項目名) | 項目の値の平均値を求める | AVG(受注合計) |
| MAX(項目名) | 項目の値の最大値を求める | MAX(受注合計) |
| MIN(項目名) | 項目の値の最小値を求める | MIN(受注 合計) |
グループ化(GROUP BY)
“〜ごと”や“〜別”の操作を行うときにはGROUP BYでグループ化が必要
※SELECTの後にはグループ化で指定した列名と集合関数しか記述できない
グループ化後の条件指定(HAVING)
グループ化した集計結果に対して、抽出条件を記述する場合にはHAVINGを使用
必ずGROUP BYの後に記述する
| SELECT 項目名,… FROM 表名 [WHERE … ] GROUP BY 項目名,… HAVING グループ化したデータの検索条件 ※SELECT文で指定する項目名には集合関数利用可能 ※[ ]内は、必要に応じて記述する |
副問い合わせ
抽出する列と選択条件に使用する列が別の表にある場合に使用する
( )内のSELECT文が先に実行され、その抽出結果が選択条件となる
IN
( )内の抽出結果が1つである場合、( )の前に「＝」を使用できるが、複数抽出される可能性がある場合はINを使用する
LIKE
パターンの照会にはLIKEを使用する
範囲指定(BETWEEN AND)
選択の条件として範囲を指定する場合、BETWEEN ANDを使用する
抽出結果の整列(ORDER BY)
並べ替えをする場合はORDER BYを使用
| 並べ替えが | 昇順： | ASC(省略可) |
| 降順： | DESC |
重複行の排除(DISTINCT)
SELECT文の結果に対して重複した行がある場合、その重複を排除するには、 DISRINCT を使用する
| SELECT DISTINCT 項目名1(,項目名2,…) FROM 表名 |
ホスト変数
外部のプログラムからの変数のこと
SQLでは変数名の前に：(コロン)をつけて“：変数名”とする
