前回のデータベースの説明で、最も中心的な機能のSQLについて説明を行ったが、その続き。
SQLの基礎
データベースでは、記録されているデータの読み書きは、SQLという言語を用いる。 SQLでは、射影・結合・選択を表す処理で構成される。
SELECT S.業者番号 -- 必要とされるデータを抽出する射影 --
FROM S -- 複数のテーブルを組合せる結合 --
WHERE S.優良度 >= 20 ; -- 対象となるデータを選び出す選択 --
// C言語なら
for( int i = 0 ; i < sizeofarray( S ) ; i++ ) {
if ( S[i].優良度 >= 20 )
printf( "%d¥n" , S[i].業者番号 ) ;
}
Sは、テーブル名であり、文脈上対象テーブルが明らかな場合、フィールド名の前の テーブルは省略可能である。
SELECT 業者番号 FROM S WHERE 優良度 >= 20 ;
ここで、SQLの最も便利な機能は、直積による結合処理。2つの表を組み合わせる処理。
PERSON 個人の情報 ((C言語の構造体をイメージ))
id | name | age | work | struct PERSON {
----+---------+-----+--------- | int id ;
101 | tsaitoh | 52 | teacher | char name[10] ;
102 | tomoko | 41 | worker | int age ;
203 | mitsuki | 18 | student | char work[10] ;
| } table_person[3] ;
GRAD 卒業した学校
id | graduate # idは外部キー
----+----------- | struct GRAD {
203 | manyo-jh | int id ;
102 | fukui-nct | char graduate[10] ;
101 | univ-fukui | } table_grad[3] ;
※ ER図的に言うと、実体(PERSON),関係(GRAD) の他に実体(SCHOOL)があるべき
このような表があった時、tsaitohがどこを卒業したのかという情報を調べたかったら、PERSONから”tsaitoh”を調べ、id=”101″を見つけ、GRADからid=”101″を探しgraduate=”univ-fukui”が得られる。
SELECT PERSON.id FROM PERSON WHERE PERSON.name = 'tsaitoh' ; SELECT GRAD.graduate FROM GRAD WHERE GRAD.id = 101 ; # 2つめのSELECT文に"101"をどう受け渡すのか？
// C言語で書いたら...
for( int i = 0 ; i < 3 ; i++ ) {
if ( strcmp( table_person[i].name , "tsaitoh" ) == 0 ) {
for( int j = 0 ; j < 3 ; j++ ) {
if ( table_grad[ j ].id == table_person[i].id ) {
printf( "%s¥n" , table_grad[ j ].graduate ) ;
break ; // 重複が無いなら、終わらせる
}
}
break ; // 重複が無いなら、終わらせる
}
}
このような2つの表を関連付けた検索を SQL では、以下のように書ける。
SELECT GRAD.graduate -- 射影 -- FROM PERSON, GRAD -- 結合 -- WHERE PERSON.name = "tsaitoh" AND PERSON.id = GRAD.id ; -- 選択 --
このSQLでは、PERSONとGRADの全データについて、(1)直積である組み合わせ処理「結合」を行い、(2)その中で指定する条件のデータを「選択」し、(3)欲しい情報を抽出「射影」する。
// C言語で書いたら、こんな処理をするイメージ
for( int i = 0 ; i < 3 ; i++ ) { // 結合
for( int j = 0 ; j < 3 ; j++ ) {
if ( strcmp( table_person[i].name , "tsaitoh" ) == 0 // 選択
&& table_person[i].id == table_grad[j].id )
printf( "%s¥n" , table_grad[j].graduate ) ; // 射影
}
}
(1) i,jの2重forループが、FROM節の結合に相当し、(2) ループ内のif文がWHERE節の選択に相当し、(3) printfの表示内容が射影に相当している。
データ定義言語
教科書ではいきなりSELECT文から説明が始まるけど、その前にテーブルってなに？ といった疑問もあるだろうから、あらかじめ CREATE TABLE 文、DROP TABLE文、 INSERT VALUES文などを紹介する。
CREATE TABLE S ( -- Sはテーブル名 -- 業者番号 CHAR(5) , -- カラム名の後ろに型を記載 -- 業者名 CHAR(20) , 優良度 INTEGER , 所在地 CHAR(20) ) ; -- 型の後ろに "PRIMARY KEY" をつけると -- -- 重複ができなくなったり、NULL値が使えなくなる。 -- -- 型に"UNIQUE" をつけると重複禁止 -- -- "UNIQUE(カラム1,カラム2...)" 複数カラムによるキーの場合 -- DROP TABLE S ; -- テーブルSのデータを消す --
データの型
CHAR(長さ) 固定長の文字列。短いと空白で埋められる。長いとエラー VARCHAR(長さ) 可変長文字列。"長さ"は最大長さ。 NUMBER(桁数,小数点桁数) 固定小数点型(桁数などを指定した時),浮動小数点型 INTEGER,SHORTINTEGER,LONGINTEGER 整数型 REAL,DOUBLE 実数型(REAL=float) DATETIME 日付+時間
データ操作言語
-- データの挿入 -- INSERT INTO S (業者番号,業者名,優良度,所在地) VALUES('S1' , 'ABC社' , 20 , '福井' ) ; -- データの更新 -- UPDATE S SET 優良度=30 WHERE 業者番号='S1' ; -- データの削除 -- DELETE FROM S WHERE 業者番号='S1' ; -- データの検索 -- SELECT S.業者名 FROM S WHERE S.優良度 >= 30 ;
データ制御言語
-- 特定のユーザに特定の作業の権限を与える -- GRANT 権限 ON レベル TO ユーザ ; -- 特定のユーザの権限を剥奪する -- REVOKE 権限 ON レベル FROM ユーザ ; -- トランザクションの開始 -- BEGIN -- トランザクションの確定 -- COMMIT -- トランザクションの取り消し -- ROLLBACK
データ制御言語は、説明の詳細を略。演習で利用する SQLite では、権限の概念が無く、トランザクションも暗黙に実行されるので特に意識する必要はない。