先週の、関係データベースの導入説明を終えて、実際のSQLの説明。
SQLの命令
SQL で使われる命令は、以下のものに分類される。((参考資料))
- データ定義言語 – CREATE, DROP, ALTER 等
- データ操作言語 – INSERT, UPDATE, DELETE, SELECT 等
- データ制御言語 – GRANT, REVOKE 等 (その他トランザクション制御命令など)
データベースは元々商用データの処理に使われることが多かったため、商用計算向けプログラム言語 COBOL と似ている点が多い。COBOL では、計算命令を 英語の文章の様に記述するのが特徴。
一般的な言語 // COBOL A_100 // A-100 変数名にハイフン(マイナス記号)が使える。Aから100を引くという意味ではない。 A = 100 ; // MOVE 100 TO A . A = A + B ; // ADD A B TO A .
create user
データベースを扱う際の create user 文は、DDL(Data Definition Language)で行う。
CREATE USER ユーザ名
IDENTIFIED BY "パスワード"
(例) CREATE USER saitoh IDENTIFIED BY "PASSWORD" ;
grant
テーブルに対する権限を与える命令。
GRANT システム権限 TO ユーザ名 データベースシステム全体に関わる権限をユーザに与える。 (例) GRANT execute ON admin.my_package TO saitoh ; GRANT オブジェクト権限 ON オブジェクト名 TO ユーザ名 作られたテーブルなどのオブジェクトに関する権限を与える。 (例) GRANT select,update,delete,insert ON admin.my_table TO saitoh ; REVOKE オブジェクト権限 ON オブジェクト名 TO ユーザ名 オブジェクトへの権限を剥奪する。
ただし、後に示す実験環境では、データベースのシステムにSQLiteを用いている。SQLite はネットワーク対応型のデータベースではないため、データベースをアクセスするユーザや権限の概念が存在せず、これらの命令は実行できても無視される。
create table
実際にテーブルを宣言する命令。構造体の宣言みたいなものと捉えると分かりやすい。ただし、作られたテーブルはRDBシステムが永続的に保存しているので、最初に一度実行するだけでよい。逆に、運用が始まったら大量のデータが実際に保存される。この段階でデータベースの設計が悪くてテーブルの内容を変更するのであれば、既存の全データを一旦掃き出し、テーブルを定義しなおし吐き出しておいたデータを再読み込みといった面倒な作業が必要となる。
CREATE TABLE テーブル名
( 要素名1 型 , 要素名2 型 ... ) ;
PRIMARY KEY 制約
1つの属性でのキーの場合、型の後ろに"PRIMARY KEY"をつける、
複数属性でキーとなる場合は、要素列の最後に PRIMARY KEY(要素名,...) をつける。
これによりKEYに指定した物は、重複した値を格納できない。
要素名には、文字コードに注意は必要だけど、日本語なども使うことができる。
(例)
create table S
( 業者番号 varchar(5) not null ,
業者名 varchar(20) ,
優良度 integer ,
所在 varchar(15) ,
unique (業者番号)
) ;
型には、以下の様なものがある。(Oracle)
CHAR( size) : 固定長文字列 / NCHAR国際文字
VARCHAR2( size ) : 可変長文字列 / NVARCHAR2...
NUMBER(桁) : 指定 桁数を扱える数
BINARY_FLOAT / BINARY_DOUBLE
: 浮動小数点(float / double)
DATE : 日付(年月日時分秒)
SQLiteでの型
INTEGER : int型
REAL : float/double型
TEXT : 可変長文字列型
BLOB : 大きいバイナリデータ
DROP TABLE テーブル名
テーブルを削除する命令
insert,update,delete
指定したテーブルに新しいデータを登録,更新,削除する命令
INSERT INTO テーブル名 ( 要素名,... ) VALUES ( 値,... ) ;
要素に対応する値をそれぞれ代入する。
(例)
INSERT INTO S ( 業者番号, 業者名, 優良度, 所在 )
VALUES ( 'S1', 'ABC社', 20, '福井' ) ;
UPDATE テーブル名 SET 要素名=値 WHERE 条件
指定した条件の列の値を更新する。
DELETE FROM テーブル名 WHERE 条件
指定した条件の列を削除する。
select
データ問い合わせは、select文を用いる、 select文は、(1)必要なカラムを指定する射影、(2)指定条件にあうレコードを指定する選択、 (3)複数のテーブルの直積を処理する結合から構成される。
SELECT 射影 FROM 結合 WHERE 選択 (例) SELECT S.業者番号 FROM S WHERE S.優良度 > 30 ;
理解確認
- キー・プライマリキー・外部キーについて説明せよ。
- 上記説明中の、科目テーブルにふさわしい create table 文を示せ。
- select文における、射影,結合,選択について説明せよ。
SQLの演習環境の使い方
SQL の演習は、Paiza.IO で動作確認をしてください。
SQLの基礎/select文と射影・結合・選択
ここまで述べたようにデータベースでは記録されているデータの読み書きは、SQL で行われ、射影・結合・選択を表す処理で構成されることを示した。SQL の機能を理解するために、同じ処理を Java で書いたらどうなるのかを示す。
((( 元のSQL )))
SELECT S.業者番号 -- 必要とされるデータを抽出する射影 --
FROM S -- 複数のテーブルを組合せる結合 --
WHERE S.優良度 >= 20 ; -- 対象となるデータを選び出す選択 --
((( SQLをJavaで書いたら )))
// Javaなら... S のデータをclass宣言で書いてみる。
class Table_S {
String 業者番号 ; // 当然、Javaでは要素名を
String 業者名 ; // 漢字で宣言はできない。
int 優良度 ;
String 所在 ;
Table_S( String id , String nm , int ex , String ad ) {
this.業者番号 = id ;
this.業者名 = nm ;
this.優良度 = ex ;
this.所在 = ad ;
}
} ;
Table_S[] table_s = {
new Table_S( "S1" , "ABC社" , 20 , "福井" ) ,
:
} ;
// SELECT...をC言語で書いた場合の命令のイメージ
// 結合
for( int s = 0 ; s < table_s.length ) ; s++ ) {
// 選択
if ( table_s[s].優良度 >= 20 )
// 射影
System.out.println( table_s[s].業者番号 ) ;
}
Sは、テーブル名であり、文脈上対象テーブルが明らかな場合、フィールド名の前の テーブルは省略可能である。
SELECT 業者番号 FROM S WHERE 優良度 >= 20 ;
WHERE 節で記述できる条件式では、= , <>(not equal) , < , > , <= , >= の比較演算子が使える。
# これ以外の演算機能は、次週にて紹介予定。
直積と結合処理
ここで、SQLの最も便利な機能は、直積による結合処理。複数の表を組み合わせる処理。単純な表形式の関係データベースで、複雑なデータを表現できる基本機能となっている。
SELECT SG.商品番号 , S.所在 FROM S , SG WHERE SG.業者番号 = S.業者番号
上記の様に FROM 節に複数のテーブルを書くと、それぞれのテーブルの直積(要素の全ての組み合わせ)を生成する処理が行われる。この機能が結合となる。しかし、これだけでは意味がないので、通常は外部キーが一致するレコードでのみ処理を行うように、WHERE SG.業者番号 = S.業者番号 のような選択を記載する。最後に、結果として欲しいデータを抽出する射影を記載する。
SELECTの結合処理と処理内容
selectでの選択,結合,射影の処理(select 射影 from 結合 where 選択)を理解するために、同じ処理をJavaで書いたらどうなるかを示す。
class Table_SG {
String 業者番号 ;
String 商品番号 ;
int 在庫量 ;
Table_SG( String sid , String gid , int stk ) {
this.業者番号 = sid ;
this.商品番号 = gid ;
this.在庫量 = stk ;
}
} ;
Table_SG[] table_sg = {
new Table_SG( "S1" , "G1" , 300 ) ,
:
} ;
// FROM S
for( int s = 0 ; s < table_s.length ; s++ ) {
// FROM SG
for( int sg = 0 ; sg < table_sg.length ; sg++ ) {
// WHERE S.業者番号 = SG.業者番号
if ( table_s[s].業者番号.equals( table_sg[sg].業者番号 ) ) {
// SELECT SG.商品番号 , S.所在
System.out.println( table_sg[sg].商品番号 +"|"+ table_s[s].所在 ) ;
}
}
}
(1) s,sgの2重forループが、FROM節の結合に相当し、(2) ループ内のif文がWHERE節の選択に相当し、(3) printfの表示内容が射影に相当している。
射影の処理では、データの一部分を抽出することから、1件の抽出レコードが同じになることもある。この際の重複したデータを1つにまとめる場合には、DISTINCT を指定する。
SELECT DISTINCT SG.商品番号, S.所在 FROM S, SG WHERE SG.業者番号 = S.業者番号 ;
上記のプログラムでは、データの検索は単純 for ループで記載しているが、内部で HASH などが使われていると、昇順に処理が行われない場合も多い。出力されるデータの順序を指定したい場合には、ORDER BY … ASC (or DESC) を用いる
SELECT SG.商品番号, S.所在 FROM S, SG WHERE SG.業者番号 = S.業者番号 ORDER BY S.所在 ASC ; -- ASC:昇順 , DESC:降順 --
表型のデータと串刺し
FROM に記載する直積のための結合では、2つ以上のテーブルを指定しても良い。
SELECT S.業者名, G.商品名, SG.在庫量
FROM S, G, SG
WHERE S.業者番号 = SG.業者番号 -- 外部キー業者番号の対応付け --
AND SG.商品番号 = G.商品番号 -- 外部キー商品番号の対応付け --
class Table_G {
String 商品番号 ;
String 商品名 ;
String 色 ;
int 価格 ;
String 所在 ;
Table_G( String id , String nm , String cl , int pr , String ad ) {
this.商品番号 = id ;
this.商品名 = nm ;
this.色 = cl ;
this.価格 = pr ;
this.所在 = ad ;
}
} ;
Table_G[] table_g = {
new Table_G( "G1" , "赤鉛筆" , "青" , 120 , "福井" ) ,
:
} ;
// [結合] S,G,SGのすべての組み合わせ
// FROM S -- 結合
for( int s = 0 ; s < table_s.length ; s++ ) {
// FROM G -- 結合
for( int g = 0 ; g < table_g.length ; g++ ) {
// FROM SG -- 結合
for( int sg = 0 ; sg < table_sg.length ; sg++ ) {
// [選択] 条件でレコードを選び出す
// WHERE S.業者番号 = SG.業者番号
// AND SG.商品番号 = G.商品番号
if ( table_s[s].業者番号.equals( table_sg[sg].業者番号 )
&& table_g[g].商品番号.equals( table_sg[sg].商品番号 ) ) {
// [射影] 使用するフィールドを出力
System.out.println( table_s[s].業者名 +"|"+
table_g[g].商品名 +"|"+
table_sg[sg].在庫量 ) ;
}
}
}
}
ここで結合と選択で実行している内容は、外部キーである業者番号を S から探す、商品番号を G から探している。この、外部キー対応しているものを探すという視点で、上記 Java のプログラムを書き換えると、以下のように表せる。
// FROM SG
for( int sg = 0 ; sg < table_sg.length ; sg++ ) {
// 外部キー SG.業者番号に対応するものを S から探す
for( int s = 0 ; s < table_s.length ; s++ ) {
if ( table_s[s].業者番号.equals( table_sg[sg].業者番号 ) ) {
// 外部キー SG.商品番号に対応するものを G から探す
for( int g = 0 ; g < table_g.length ; g++ ) {
if ( table_sg[sg].商品番号.equals( table_g[g].商品番号) ) {
System.out.println( table_s[s].業者名 +"|"+
table_g[g].商品名 +"|"+
table_sg[sg].在庫量 ) ;
// break ; primary key ならばこれ以上探す必要はない
}
}
// break ; brimary key ならばこれ以上探す必要はない
}
}
}
このような、複数の表の実体と関係を対応付けた検索を、データベースの専門の人は「データを串刺しにする」という言い方をすることも多い。
また、SQL では、このようなイメージの繰り返し処理を、数行で分かりやすく記述できている。このJavaのプログラム例では、キーに対応するものを単純 for ループで説明しているが、SQL ではプライマリキーなら、B木やハッシュなどを用いた効率の良い検索が自動的に行われる。このため、SQLを扱う応用プログラマは、SQLの結合の処理方法は通常はあまり考えなくて良い。
INNER JOIN 構文
ここで示しているS:業者とG:商品のデータベースでは実体とよばれ、その2つの間に実際に在庫としてもつものをSG:在庫として関連づけている(関係とよぶ)。
こういった場合は、在庫の中で、対応する業者を探す、対応する商品を探すというように、何の情報をもとに関連する情報を探すということを明確にするために、INNER JOIN という書き方がある。
((( 元のSQL )))
SELECT S.業者名, G.商品名, SG.在庫量
FROM S, G, SG
WHERE S.業者番号 = SG.業者番号 -- 外部キー業者番号の対応付け --
AND SG.商品番号 = G.商品番号 -- 外部キー商品番号の対応付け --
AND S.優良度 >= 20 ; -- 抽出条件
((( INNER JOIN を使う場合 )))
SELECT S.業者名, G.商品名, SG.在庫量
FROM SG
INNER JOIN S ON S.業者番号 = SG.業者番号 -- 対応する業者番号を探すことが明確にわかる
INNER JOIN G ON G.商品番号 = SG.商品番号 -- 対応する商品番号を探すことが明確にわかる
WHERE S.優良度 >= 20 ; -- 抽出条件
上記のような INNER JOIN に対応する Java プログラムは、以下のようになる。
// SELECT S.業者名,G.商品名,SG.在庫量
// FROM SG
// INNER JOIN S ON SG.業者番号 = S.業者番号
// INNER JOIN G ON SG.商品番号 = G.商品番号
// WHERE S.優良度 >= 20
// FROM SG
for( int sg = 0 ; sg < table_sg.length ; sg++ ) {
int s , sg ;
// INNER JOIN S
for( s = 0 ; s < table_s.length ; s++ ) { // 昇順なら2分探索法が使えるかも
// ON S.業者番号 = SG.業者番号
if ( table_sg[sg].業者番号.equals( table_s[s].業者番号 ) )
break ;
}
// INNER JOIN G
for( g = 0 ; g < table_g.length ; g++ ) { // 昇順なら2分探索法が使えるかも
// ON SG.商品番号 = G.商品番号
if ( table_sg[sg].商品番号.equals( table_g[g].商品番号) )
break ;
}
// INNER JOIN では対応するものが存在する時だけ
if ( s < table_s.length && g < table_g.length ) {
// WHERE S.優良度 >= 20
if ( table_s[s].優良度 >= 20 ) {
// SELECT S.業者名,G.商品名,SG.在庫量
System.out.println( table_s[s].業者名 +"|"+
table_g[g].商品名 +"|"+
table_sg[sg].在庫量 ) ;
}
}
}
INNER JOIN S や INNER JOIN G で対応するものを探す場合、事前に 業者番号や商品番号でソートしておいたり、B木を作ったり、ハッシュ表をつくっておけば、上記プログラムの for( s … ) や for( g … ) の部分は、2分探索法を使ったり、B木検索をしたり、ハッシュ検索で高速に探すことができる。
B木やハッシュ検索をするために、インデックスファイルを作らせることができる。
CREATE INDEX インデックス名 ON S (業者番号 ASC) ; -- 業者番号の昇順でインデックスを作成 CREATE INDEX インデックス名 ON G (商品番号 DESC) ; -- 商品番号の降順でインデックスを作成 -- ただし、業者番号,商品番号は primary key なので、通常はインデックスは一般的に不要