GROUP BY HAVINGとビューテーブル
GROUP BY HAVING
GROUP BY-HAVING では、指定されたカラムについて同じ値を持つレコードがグループ化される。SELECT 文に指定される集約関数は、グループごとに適用される。HAVING は、ある条件を満たす特定のグループを選択するための条件で、WHERE と違い、集約関数が使える。
SELECT SG.商品番号, SUM(SG.在庫量) FROM SG GROUP BY SG.商品番号 HAVING SUM(SG.在庫量) >= 500 ;
このSQLを実行すると、SG のテーブルから、商品番号が同じものだけをあつめてグループ化される。そのグループごとに在庫量のデータの合計SUMを集約し、合計が500以上のデータが出力される。
|
⇒ |
|
⇒ |
|
CREATE VIEW
今までで述べてきたSQLでは、実際のテーブルを対象に、結合・選択・射影を行う命令であり、これは概念スキーマと呼ばれる、対象となるデータベース全体を理解したプログラマによって扱われる。
しかし、プログラムの分業化を行い、例えば結果の表示だけを行うプログラマにしてみれば、全てのデータベースの表を考えながらプログラムを作るのは面倒である。そこで、結合・選択・射影の演算の結果で、わかりやすい単純な表となったものであれば、初心者のデータベースプログラマでも簡単に結果を扱うことができる。このような外部スキーマを構成するための機能が、ビューテーブルである。
-- 優良業者テーブルを作る -- CREATE VIEW 優良業者 ( 業者番号 , 優良度 , 所在 ) AS SELECT S.業者番号, S.優良度, S.所在 FROM S WHERE S.優良度 >= 15 ; -- 優良業者テーブルから情報を探す -- SELECT * FROM 優良業者 WHERE 優良業者.所在 = '福井' ; -- 文房具データベース -- CREATE VIEW 文房具データベース ( 業者名 , 商品名 , 在庫量 ) AS SELECT S.業者名, G.商品名, SG.在庫量 FROM S, SG, G WHERE S.業者番号 = SG.業者番号 and SG.商品番号 = G.商品番号 ; -- VIEWの確認 -- SELECT * FROM 文房具データベース ; -- 串刺ししたデータベースに見える ----- 実行結果 ----- 業者名 商品名 在庫量 ABC社 赤鉛筆 300 ABC社 ノート 200 ABC社 消しゴム 400 ABC社 消しゴム 200 ABC社 筆箱 100 ABC社 バインダ 100 LMN社 赤鉛筆 300 LMN社 ノート 400 PQR社 ノート 200 STU社 ノート 200 STU社 消しゴム 300 STU社 筆箱 400
ビューテーブルに対する SQL を実行すると、システムによっては予め実行しておいた CREATE VIEW の AS 以下の SQL の実行結果をキャッシュしておいて処理を行うかもしれない。システムによっては SQL の命令を 副クエリを組合せた SQL に変換し、処理を行うかもしれない。しかし、応用プログラマであれば、その SQL がどのように実行されるかは意識する必要はほとんど無いであろう。
ただし、ビューテーブルに対する 挿入・更新・削除といった演算を行うと、データによっては不整合が発生することもあるので注意が必要である。
SQL言語
教科書の流れに沿ってSQLの言語について、再掲
- スキーマ定義
- CREATE – 実テーブル、ビューテーブルの定義
- GRANT – 権限の定義
- スキーマ操作
- DROP – 実テーブル、ビューテーブルの削除
- REVOKE – 権限の削除
- ALTER – テーブルの変更
- ADD – カラムの追加
- データ操作
- SELECT, INSERT, DELETE, UPDATE – レコードの検索、追加・削除・更新
- トランザクション処理
- データベースでは、原子性などを満たすためにデータベースへの更新履歴を保持している。これらの更新履歴をデータベースに反映させ確定する処理がトランザクション処理。
- COMMIT – データベースの更新処理を確定
- ROLLBACK – データベースの更新処理を取り消す
ホスト言語とのインタフェースとSQLインジェクション
プログラミング言語によっては、その言語の中でSQLを使うために「組み込み型のSQL」が使えるものがある。
(COBOL,PL/Iなど)
動的メモリ管理が得意な最近のPythonやPHPなどの言語であれば、データベース参照の関数が利用できる。
SQLインジェクション
例えば、PHPでは、SQLからデータを取り出す処理は、以下のようになる。
// 検索するユーザID $id = "t-saitoh" ; $pdo = new PDO( '...' ) ; // データベースに接続する関数 $sql = "select name from usertable where id='$id'" ; $query = $pdo->prepare( $sql ) ; // 取り出せたデータに関する処理 id がプライマリキーならforeachは1回ループのはず foreach( $query->fetcAll() as $name ) { // $name に取り出した名前が入っている }
しかし、$id の部分を、Web の入力フォームなどの値であれば、名前以外の情報が入力される場合もある。
この際に、「 $id = ” ‘ or 1==1 — ‘ ” 」といった値が入っていた場合、SQLで実行される命令は、
$id = "' or 1==1 --'" の場合 $sql = "select name from usertable where id='' or 1==1 -- ''" ;
となってしまい、本来なら1人のデータを抽出する select 命令が、全テーブルに対して該当してしまい、情報漏洩が発生するかもしれない。
「 $id = “‘; drop usertable ; — ‘” 」であれば、usertable が消されてしまい、システムが動かなくなる(サービスを提供できなくする攻撃 = DoS攻撃 – Denial-of-service attack)ことも考えられる。
他にもSQLインジェクションを使う技がある。こちらの CTF の WriteUp などが参考になる。
こういった攻撃手法は、SQLに本来の意図ではないSQL命令を紛れ込ませる攻撃ということで、SQLインジェクションという。
SQLインジェクションで発生した有名な事件では、以下のようなものがある。
- Yahoo! BB 顧客情報漏洩事件 – 100億円を超える被害
- PlayStation Network個人情報流出事件
対策としては、ユーザが入力したデータを用いて SQL 命令を実行する場合は、ユーザ入力をSQLとして悪用されないように、シングルクオートなどをエスケープするなどの処理が必要となる。さまざまな手法があるので、SQL無効化の専用関数を用いるべき。
また、データベースシステムは、ネットワーク経由でSQLによる処理を行うが、データベースサーバ自体がインターネットに接続されていて、パスワード攻撃によりデータベース本体に不正アクセスが行われる場合もある。一般的なデータベースを用いたシステムは、フロントエンドのWebサーバ、スレーブDBサーバ、マスタDBサーバの三層構成をとることが多いが、バックエンドのデータベースは、インターネットから隔離しフロントエンドのWebサーバのみ接続できるようにするのが一般的である。
データベースに接続する場合はパスワードにより利用者を限定することができるが、データベースシステム自体がインターネットに接続されていると、パスワード総当たり攻撃(ブルートフォース攻撃)や、パスワードスプレー攻撃(総当たり攻撃は、短時間でパスワード失敗が多発するのでシステムで接続拒否するのが一般的。これを回避するために時間をかけて総当たり攻撃をする手法)により、情報漏洩が発生する。
2分探索木への追加とAVLと2分ヒープ
2分探索木にデータを追加
前回の2分探索木では、基本的な説明ということで、木の生成では直接木構造を生成していた。しかし、本来であれば、扱うデータに応じて木を生成することが求められる。
Javaの場合
以下の Javaのプログラムの関数 entry( … ) では、再帰を使いながら、value を追加した木を返す関数で記述した。
import java.util.*; class BTreeNode { int data ; BTreeNode left ; BTreeNode right ; BTreeNode( int x , BTreeNode l , BTreeNode r ) { this.data = x ; this.left = l ; this.right = r ; } } public class Main { public static void print_tree( BTreeNode p ) { // 木構造をわかりやすくするために()をつけて表示 if ( p != null ) { System.out.print( "(" ) ; print_tree( p.left ) ; System.out.print( p.data ) ; print_tree( p.right ) ; System.out.print( ")" ) ; } } public static BTreeNode entry( BTreeNode p , int value ) { if ( p == null ) { return new BTreeNode( value , null , null ) ; } else { if ( p.data == value ) ; // 何もしない else if ( p.data > value ) p.left = entry( p.left , value ) ; else p.right = entry( p.right , value ) ; return p ; } } public static void main(String[] args) throws Exception { BTreeNode top = null ; top = entry( top , 42 ) ; top = entry( top , 27 ) ; top = entry( top , 72 ) ; top = entry( top , 13 ) ; top = entry( top , 38 ) ; top = entry( top , 64 ) ; top = entry( top , 81 ) ; print_tree( top ) ; System.out.println() ; } } // 動作結果 // (((13)27(38))42((64)72(81)))
- 2分探索木へのデータの追加(Paiza.io)
しかしながらこのプログラムでは、以下のように小さい順序でデータを与えると、右に伸びる木が作られてしまう。
// 不均一な木ができる場合 public static void main(String[] args) throws Exception { BTreeNode top = null ; top = entry( top , 13 ) ; top = entry( top , 27 ) ; top = entry( top , 38 ) ; top = entry( top , 42 ) ; top = entry( top , 64 ) ; top = entry( top , 72 ) ; top = entry( top , 81 ) ; print_tree( top ) ; System.out.println() ; } // 動作結果 // (13(27(38(42(64(72(81)))))))
このような不均一な木が生成されてしまうと、本来であればデータ探索の処理時間は O( log N ) であるが、最悪の状態では O( N ) となってしまう。
AVL木
こういった偏った木が生成されてしまった時には、以下のような処理を加えると、不均一な状態を改善できる。
depth() 関数は与えられた木の深さを求める関数であり、左枝と右枝が大きく違う場所で 木の右回転を行う rotate_right() を実行する。
public class Main { public static int depth( BTreeNode p ) { // 木の深さを求める if ( p == null ) { return 0 ; } else { int l = depth( p.left ) ; int r = depth( p.right ) ; if ( l > r ) return 1 + l ; else return 1 + r ; } } public static BTreeNode rotate_right( BTreeNode p ) { BTreeNode q = p.left ; // p BTreeNode r = q.right ; // / \ q.right = p ; // q p.left = r ; // / \ return q ; // r } public static void main(String[] args) throws Exception { BTreeNode top = null ; top = entry( top , 86 ) ; top = entry( top , 53 ) ; top = entry( top , 92 ) ; top = entry( top , 11 ) ; top = entry( top , 65 ) ; top = entry( top , 22 ) ; // 不均一な状態を確認 print_tree( top ) ; System.out.println() ; System.out.println( "p.left=" + depth( top.left ) ) ; System.out.println( "p.right=" + depth( top.right ) ) ; // top の右回転を実行 top = rotate_right( top ) ; // 改善された状態を確認 print_tree( top ) ; System.out.println() ; System.out.println( "p.left=" + depth( top.left ) ) ; System.out.println( "p.right=" + depth( top.right ) ) ; } }
- AVL木と右回転(Paiza.io)
上記のプログラムでは、左枝が3段で右枝が1段であり不均一な状態となっている。
そこで、最上段の86の左枝を上に移動させるように枝の繋ぎ方を rotate_right() により変更している。実際には、繋ぎ変えを左回転させる処理も作る必要がある。
このように、木の枝の深さが不均一な場所で、このような処理を加えて均一化を図った木構造は AVL 木と呼ぶ。
AVL木は、考案した Velski と Landis の名前が由来(Adelson-Velskii and Landis’ tree)
2分ヒープ(binary heap)
2分探索木では、1つのノードにつき2つのポインタを持ち、データ1件あたりのメモリの使用量が多い。通常の「配列の先頭から昇順にデータを並べる2分探索法」では、途中にデータを挿入する場合、データを後ろにずらす必要があるため、O(N)の処理時間を要する。
これらの問題の解決法の1つとして、2分ヒープがある。左右に均一に成長している2分探索木で、上から番号を以下の様に振ると、i番目のデータの左の枝は 2×i+1 番目、右の枝は 2×i+2 番目であることが判る。
このような順序で配列にデータを保存する方法が2分ヒープである。この方式ならアルゴリズムの説明は省略するが、入れるべきノードの場所でのデータの入れ替えで実現できるため O( log( N ) )で挿入が可能となる。
import java.util.*; public class Main { public static int find_heap( int[] array , int idx , int key ) { while( idx < array.length ) { if ( array[ idx ] == key ) return idx ; // 見つかった else if ( array[ idx ] > key ) idx = idx*2 + 1 ; else idx = idx*2 + 2 ; } return -1 ; // 見つからなかった } public static void main(String[] args) throws Exception { int[] a = { 53 , 11 , 86 , 10 , 22 , 65 , 92 } ; System.out.println( find_heap( a , 0 , 22 ) ) ; } }
- 2分ヒープ(Paiza.io)
レポート課題
以下のようなデータを扱う2分探索木のプログラムを作成せよ。以下の箇条書き番号の中から、(出席番号 % 3+1)のデータについてプログラムを作ること。
- 名前(name)と電話番号(phone)
- 名前(name)と誕生日(year,mon,day)
- 名前(name)とメールアドレス(mail)
プログラムは以下の機能を持つこと。
- 1行1件でデータを入力し、2分木に追加できること。
- 全データを昇順(or降順)で表示できること。
- 検索条件を入力し、目的のデータを探せること。
レポートでは、(a)プログラムリスト,(b)その説明,(c)動作検証結果,(d)考察 を記載すること。
(2年後期実験) 簡単ゲーム作成
1.目的
簡単なゲーム作成を通して、遊びながらプログラミングすることで、スキルアップを目指す。
2.概要
簡単なゲームとして、以下の3つのゲームの中から1つを選び、リンク先の JavaScript による基本機能だけのゲームに、ルール判定・勝敗判定などの機能を組み込む。以下のリンク先の五目並べ、オセロゲームのプログラムは、2人で交互にコマをうつX,Y座標を入力しながら、コマの結果(や勝敗の結果)が表示されるものを作成する。
- 五目並べ(連珠)
- 5つ並べば勝ち…が五目並べ。これだけのルールだと、先手が有利なので先手の黒には、長連、四四、三三など禁じ手などが決められているのが連珠
- オセロゲーム
- 自分のコマで挟んだ相手の駒を裏返しで自分のコマにできる。ベースとなったリバーシを、日本のゲーム研究家の長谷川さんが改良したものがオセロゲーム。ルールが単純だけど、最後に大逆転となることも多い。
- ポーカー
- ポーカーはプレーヤーにトランプのカードを5枚づつ配り、その後にカードを入れ替えた後に、カードの役で勝敗が決まる。カジノゲームとして遊ぶ際には、役の完成度に応じて賭け(ベッド)たり、降りたりしながら遊ぶ。ゲームのやり方により、細かくルールが違ったりローカルルールを設ける場合もある。
プログラム作成の腕試しとしての難易度であれば、ポーカー(すべての役判定をするなら) > オセロ ≒ 連珠 > 五目並べ だと思われる。
もし、この3つのテーマの難易度が面白くないのであれば、三目並べでもよい。ただし人間対コンピュータであること。
3.実験方法
- 概要で示した五目並べ,オセロ,ポーカーのルールについて調べ、自分で2週の実験時間の中で、改良できそうなテーマを最初に1つ選んで下さい。
- 選んだテーマについて、概要で示したリンク先の html ファイルを自分のパソコンに保存し、エディタにてプログラム中の JavaScript の内容を理解してください。
- 以下のチェックシートのファイルをダウンロードし、シートのタブの中から自分が取り組むテーマを選んで、2週の実験の中で実装できそうな機能について、「作成の予定」の列に”〇”を入力してください。
- 実験1週目でプログラムを作成したら、実験時間の最後に、実験1週目で完成した機能について、チェックシートの「1週目」の列に”〇”を入力してください。
- 実験2周目では、前回の続きでプログラムを完成にむけて作成してください。2周目の実験時間の最後に、チェックシートの「2週目・自己判定」の欄に”〇”(一部未完成なら”△”,未完成なら”×”)を入力してください。
- 自己判定の記入が終わったら、同じ実験グループの1人と交代して、相手のプログラムの完成度をチェックし、相手のチェックシートの「2周目・他者判定」の欄に”〇”,”△”,”×”を入力してください。また、他の人のチェックシートに記入する際には、チェックシート表の下の「チェック担当」の欄に名前を記入しておいてください。
4.結果
- 出来上がったプログラムを動かして、作成した機能がちゃんと動作しているということが分かる実行例をいくつか画面のキャプチャ機能を使って「実験結果」として記録してください。
- 実験方法での2週目・他者判定において、(自分では動くと思っていたのに)”△”や”×”の判定のあったものについては、その実行例も「実験結果」として記録してください。
- また、他の人に他者判定をしてもらった自分のチェックシートも「実験結果」として保存してください。
5.レポート
実験レポートでは、以下の内容について記載してください。
- 目的
- 概要
- 自分が作成したテーマのルールなどを簡単にまとめる。
- プログラムの説明
- 自分のプログラムのフローチャートなどを作成し(全体の詳細なフローチャートでなくてもよい)、簡単に全体像を説明してください。
- 自分で工夫した所については、詳しく説明してください。
(例えばオセロなら裏返し処理の詳細なフローチャートなどを記載するなど。)
- 実験結果
- 実験結果として、プログラムリスト、チェックシート、動作の解る実例などをつけてください。
- 実験結果として、プログラムリストや動作例などの図をレポート内に入れる場合は、「プログラムリスト1」「図2動作例」といったように、リスト番号、図番号などをつけてレポート内で引用すること。
- 考察
- 自分で作ったプログラムでうまく動いていない所があれば、その原因やどのようにすれば直せるかを考え、その内容を説明してください。(必ずしもプログラムを直す必要はない)
- 動作で問題が見つからなかった場合は、さらにゲームを完全なものとするにはどういう改良が期待できるか、説明してください。
WiFi接続、ネットワーク層
無線LANと暗号化
無線LAN(通称 WiFi)は、IEEE 802.11 にて規格が定められている。無線LANは、使う通信周波数で、2.4GHz帯を使うものと、最近増えてきた5GHz帯のものに分けられる。
-
- IEEE802.11a 5GHz帯を使う、最大54Mbps
- IEEE802.11b 2.4GHz帯を使う、最大11Mbps
- IEEE802.11g 2.4GHz帯を使う、最大54Mbps
- IEEE802.11n 2.4GHz/5GHzを使う、最大600Mbps
- IEEE802.11ac 5GHz帯を使う、最大6.9GBps – 11a/g/n/ac を組み合わせで現時点の主流
- IEEE802.11ad 60GHz 最大6.8Gbps
- IEEE802.11ax 2.4GHz/5GHz 最大 9.6Gbps (通称 WiFi 6)
- IEEE802.11be 2.5GHz/5GHz/6GHz 最大 46Gbps (WiFi 7)
2.4GHz帯は、電子レンジで使う電波の周波数と重なるため、電波干渉を受けやすい。また、2.4GHzは様々な家電製品・電子機器で利用されているため、他の機器との干渉を受けやすく速度低下を起しやすいが、遠くまで電波が届きやすい。 5GHzは、この周波数帯を利用している機器が少ない為、干渉を受けにくく安定して通信できるが、あまり遠くには電波が届かず、通信が極端に不安定になる場合がある。
無線LANに接続する場合には、接続先(アクセスポイント)に付けられた名前(SSID)と、SSIDに割り振られたパスワードが必要となる。ただし無線は、電波で信号を飛ばすため、近くに行くだけで通信を傍受できる。このため、データの暗号化が必須となる。この暗号化は、そのアルゴリズムにより解読の困難さが変わる。
- WEP 64bit / 128bit – すでに古い暗号化で専用ソフトを使うとすぐに解読される可能性が高い。使うべきではない。
- WPA/WPA2 –
現時点の主流?- 暗号化方式 TKIP や 暗号化アルゴリズム AES
- WPA3 – そろそろこちらが主流かな…
- 192bit暗号化, 辞書攻撃に強い SAE を使用
無線LANでは、車でセキュリティの甘いアクセスポイント(暗号化無しやWEPを使うAP)を探し、その無線LANを使ってクラッキングなどをおこなう場合も多い。(ウォードライビング)
勝手に無線LANを使われないようにするために一般的には、(1)アクセスポイントに接続できる機器をMACアドレス(機器に割り当てられた48bitの固有値)で制限したり、(2)SSIDのステルス化(APが出す電波にSSIDを入れない方式)を行う場合も多い。ただし、これらの制限をかけても専用の機器を使えば通信は傍受可能。
移動通信システム
WiFiは屋内にアクセスポイントを設置し十数メートル範囲の通信を行うが、屋外の基地局と端末の間での数百メートルから数キロといった距離の通信を行う場合には「移動通信システム」が用いられる。
- 第1世代移動通信システム – アナログ方式 1980年代
- 第2世代移動通信システム – デジタル方式 1990年代 (GSM,CDMA,PDC)
- 第3世代移動通信システム – モバイルインターネットの時代 2000年代 (数Mbps) W-CDMA, CDMA2000
- 2024年現在 Docomo のみサービスを提供しているが、FOMA/iモードは2026/3/31に終了予定
- 第4世代移動通信システム – 高速大容量 2010年代 (数十Mbps-数百Mbps) LTE
- 現時点では山間部は4G、主要市町では5Gが利用可能 (参考:auのカバーマップ)
- 第5世代移動通信システム – 超高速大容量 2020年代 (最大20Gbps) 通称 5G
- ローカル5G – 通信事業者ではなく、企業や自治体などが自社の敷地や特定のエリア内に構築する専用の5Gネットワーク(国指定の無線局免許取得が必要)
移動体通信での 5G の話をすると、無線LANの話から、5G = 5GHz と勘違いするかもしれないが、5G = 第5世代移動通信システム(5th Generation) なので注意。
ネットワーク層
前回説明したMACアドレスによるデータリンク層では、1つのサブネットの中で指定した相手にデータを送ることはできる。しかし、データリンク層だけでは、他のサブネットにいる相手にデータを送ることができない。(相手の名前を知っていても、住所を知らなければ郵便は送れない。)
ネットワーク層とIPアドレス(IPv4)
サブネットに分割し、隣接するサブネット、さらには上流のインターネットと通信をするためには、IPアドレスを用いた通信が行われる。
ネットワークに接続する機器には、それぞれユニークな32bitの番号(IPv4アドレス)を割り振る。
コンピュータへのIPアドレスの設定には、(a)IPアドレス,(b)サブネットマスク,(c)ゲートウェイの情報が必要となる。
- IPアドレス: 192.156.145.100 といった、0~255の8bitの値をピリオド区切りで4つ並べて表記するのが一般的。
- サブネットマスク: 255.255.255.0 といった値で、IPアドレスを2進数で書き並べた32bitと、サブネットマスクの32bitで、2進数の論理積をとった値は、ネットワーク番号と呼ばれ、機器が存在する場所を表す。
また、IPアドレスとサブネットマスクの否定と論理積をとった値は、ホスト番号と呼ばれる。
サブネットマスクは、先行する1のbit数で書き表すことも多い。255.255.255.0は、”/24″のように書く。 - ゲートウェイ: 自分自身のネットワーク番号と通信相手のネットワーク番号が異なる場合は、異なるサブネットにいるので、パケットを中継してもらう機器(ルータ,ゲートウェイ)にパケットを送る。
- IPアドレスとクラス: IPアドレスは、先頭8bit をネットワーク番号とするクラスA,16bitのクラスB,24bitのクラスCに分類されている。以前は、IPアドレスを割り当てる企業規模に応じて、大規模な大学だからクラスA、中規模ならクラスB(福井大学は133.7.0.0/16 ←このような書き方はCIDR記法という)、小規模ならクラスCを割り当てていた。(福井高専はCクラスを5本192.156.145~149.0/24 : 福井高専のIPアドレスでは3つのCIDR記法で表現できる。 192.156.145.0/24, 192.156.146.0/23, 192.156.148.0/23)
しかし、最近では IPv4 アドレスの不足から、大きな組織に割り振られた クラスA を再分配しているため、先頭が0~126で始まるアドレスでも大きなネットワーク組織とは限らない。
CTF問題とセキュリティの実験でWebサーバを使う場合
CTF問題とセキュリティ実験で、Webサーバを必要とする自作問題を作る時は、nitfcei.mydns.jp のサーバを使えます。
- nitfcei.mydns.jp に guestXX のIDでログイン(情報メディア工学(前期)資料を参考)
- public_html 配下のフォルダに xxxx.php などのファイルを作成
- http://nitfcei.mydns.jp/~guestXX/xxxx.php にブラウザでアクセス
- 最初に、Webの認証画面が出たら、別途アナウンスの ID, PW でログインしてください。
- https は使えないので要注意
- https://tsaitoh.net/~t-saitoh/ctf/ に設置してあるファイルと同じものが、
nitfcei.mydns.jp の /home0/t-saitoh/public_html/ctf/ のフォルダに置いてあります。
必要に応じてコピーや改造をしながら自作問題を作ってみてください。
集約関数と副問い合わせ
特殊な条件演算子
WHERE 節の中で使える特殊な条件演算子を紹介する。
... AND ... WHERE S.業者番号 <= 100 AND S.業者番号 >= 200 ; ... OR ... WHERE S.業者番号 >= 100 OR S.業者番号 <= 200 ; NOT ... WHERE NOT S.業者番号 >= 100 ; ... IN ... WHERE S.業者番号 IN ( 'S1' , 'S4' ) ; ... BETWEEN A AND B WHERE S.優良度 BETWEEN 50 AND 100 ; ... LIKE ... WHERE S.業者名 LIKE 'A_C社' ; _ は任意の1文字 ABC社 ADC社 WHERE S.業者名 LIKE 'A%社' ; % は任意の0~N文字 A社, AA社 ABC社 ... IS NULL WHERE S.業者名 IS NULL WHERE S.業者名 IS NOT NULL
集約関数
集約関数は、SQL の SELECT の射影部分で使える関数で、出力対象となった項目に対して、COUNT(),SUM(),AVG()といった計算を行うもの。
COUNT() - 項目の数 SUM() - 項目の合計 AVG() - 項目の平均 MAX() - 項目の最大値 MIN() - 項目の最低値 SELECT COUNT(S.業者番号) FROM S WHERE S.優良度 > 20 ;
集合計算
複数の SQL の結果に対し、集合和, 集合積, 集合差などの処理を行う。
... UNION ... 集合和 ... EXPECT ... 集合差 ... INTERSECT ... 集合積 SELECT S.業者名 FROM S WHERE S.所在 = '福井' UNION SELECT S.業者名 FROM S WHERE S.所在 = '東京'
教科書 40p の union の解説
前半の select 文で、(ノート,青),(消しゴム,白),… の6つ、後半の select文で (筆箱,青),(バインダ,緑) が求まる。この結果で2つの集合和が行われるが、集合計算の時に重複している要素が取り除かれるので、(ノート,青),(消しゴム,白),(筆箱,青),(バインダ,緑)の4つが最終的な答えとなる。
-- 教科書40p の例題 select G.商品名, G.色 -- (ノート,青) from G, SG -- (消しゴム,白) where (G.商品番号 = SG.商品番号) and (SG.在庫量 <= 200) ; -- (筆箱,青) -- (バインダ,緑) -- (ノート,青) - 重複 -- (ノート,青) - 重複 union select G.商品名, G.色 -- (筆箱,青) - 重複 from G -- (バインダ,緑) - 重複 where G.価格 >= 200 ; -- 結果 = { (ノート,青),(消しゴム,白),(筆箱,青),(バインダ,緑) }
SQLの副問い合せ
前節の結合処理は時として効率が悪い。このような場合は、副問い合わせを用いる場合も多い。
SELECT S.業者名, S.所在 FROM S WHERE S.業者番号 IN ( SELECT SG.業者番号 FROM SG WHERE SG.商品番号 = 'G2' AND SG.在庫量 >= 200 ) ;
まず、『◯ IN { … }』 の比較演算子は、◯が{…}の中に含まれていれば真となる。また、SQLの中の (…) の中が副問い合わせである。
この SQL では、副問い合わせの内部には、テーブル S に関係する要素が含まれない。この場合、副問い合わせ(商品番号がG2で在庫量が200以上)は先に実行される。
{ (S1,G2,200), (S2,G2,400), (S3,G2,200), (S4,G2,200) }が該当し、その業者番号の{ S1, S2, S3, S4 }が副問い合わせの結果となる。最終的に SELECT … FROM S WHERE S.業者番号 IN { ‘S1’, ‘S2’, ‘S3’, ‘S4’ } を実行する。
相関副問い合わせ
SELECT G.商品名, G.色, G.価格 FROM G WHERE 'S4' IN ( SELECT SG.業者番号 FROM SG WHERE SG.商品番号 = G.商品番号 ) ;
この副問い合わせでは、内部に G.商品番号 が含まれており、単純に()内を先に実行することはできない。こういった副問い合わせは、相関副問い合わせと呼ばれる。
処理は、Gのそれぞれの要素毎に、副問い合わせを実行し、その結果を使って WHERE節の判定を行う。WHERE節の選択で残った結果について、射影で商品名,色,価格が抽出される。
// 概念の説明用に、C言語風とSQL風を混在して記載する for( int i = 0 ; i < sizeofarray( G ) ; i++ ) { SELECT SG.業者番号 FROM SG WHERE SG.商品番号 = G[i].商品番号 を実行 if ( WHERE 'S4' IN 副query の結果が真なら ) { printf( ... ) ; } } // 全てのG 副queryの結果 WHERE 射影 // G1 -> {S1,S2} // G2 -> {S1,S2,S3,S4} -> ◯ -> (ノート,青,170) // G3 -> {S1} // G4 -> {S1,S4} -> ◯ -> (消しゴム,白,50) // G5 -> {S1,S4} -> ◯ -> (筆箱,青,300) // G6 -> {S1}
演習課題
Paiza.io などを使って、自分で考えたSQLの命令を2つ実行すること。実行した命令とその意味を説明し、出力された結果と一致することを確認すること。
さらにこの実行と同じ結果が出力される様なC言語のプログラムを作成し、おなじく結果を確認すること。
考察として、SQLで書いたプログラムとCで書いたプログラムの違いや便利な点や、Cでのプログラムの速度を早めるにはどう書くと良いかを比較検討すること。
2分探索木
配列やリスト構造のデータの中から、目的となるデータを探す場合、配列であれば2分探索法が用いられる。これにより、配列の中からデータを探す処理は、O(log N)となる。(ただし事前にデータが昇順に並んでいる必要あり)
// 2分探索法 import java.util.*; public class Main { public static int bin_search( int[] a , int key , int L , int R ) { // Lは探す範囲の左端 // Rは探す範囲の右端+1(注意!!) while( R > L ) { int m = (L + R) / 2 ; if ( a[m] == key ) return m ; else if ( a[m] > key ) R = m ; else L = m + 1 ; } return -1 ; } public static void main(String[] args) throws Exception { int[] array = { 11, 13, 27, 38, 42, 64, 72, 81 } ; System.out.println( bin_search( array , 64 , 0 , array.length ) ) ; System.out.println( bin_search( array , 14 , 0 , array.length ) ) ; } }
// 2分探索法 int array[ 8 ] = { 11, 13 , 27, 38, 42, 64, 72 , 81 } ; int bin_search( int a[] , int key , int L , int R ) { // Lは、範囲の左端 // Rは、範囲の右端+1 (注意!!) while( R > L ) { int m = (L + R) / 2 ; if ( a[m] == key ) return m ; else if ( a[m] > key ) R = m ; else L = m + 1 ; } return -1 ; // 見つからなかった } void main() { printf( "%d¥n" , bin_search( array , 0 , 8 ) ) ; }
一方、リスト構造ではデータ列の真ん中のデータを取り出すには、先頭からアクセスするしかないのでO(N)の処理時間がかかり、極めて効率が悪い。リスト構造のようにデータの追加が簡単な特徴をもったまま、もっとデータを高速に探すことはできないものか?
2分探索木
ここで、データを探すための効率の良い方法として、2分探索木(2分木)がある。以下の木のデータでは、分離する部分に1つのデータと、左の枝(下図赤)と右の枝(下図青)がある。
この枝の特徴は何だろうか?この枝では、中央のデータ例えば42の左の枝には、42未満の数字の枝葉が繋がっている。同じように、右の枝には、42より大きな数字の枝葉が繋がっている。この構造であれば、64を探したいなら、42より大きい→右の枝、72より小さい→左の枝、64が見つかった…と、いう風にデータを探すことができる。
特徴としては、1回の比較毎にデータ件数は、(N-1)/2件に減っていく。よって、この方法であれば、O(log N)での検索が可能となる。これを2分探索木とよぶ。
このデータ構造をプログラムで書いてみよう。
import java.util.*; class BTreeNode { int data ; BTreeNode left ; BTreeNode right ; BTreeNode( int x , BTreeNode l , BTreeNode r ) { this.data = x ; this.left = l ; this.right = r ; } } public class Main { public static void print_tree( BTreeNode p ) { if ( p != null ) { print_tree( p.left ) ; System.out.print( p.data + " " ) ; print_tree( p.right ) ; } } public static int tree_search( BTreeNode p , int key ) { while( p != null ) { System.out.println( "p.data=" + p.data + " : key=" + key ) ; if ( p.data == key ) return key ; else if ( p.data > key ) p = p.left ; else p = p.right ; } return -1 ; } public static void main(String[] args) throws Exception { BTreeNode top = new BTreeNode( 42 , new BTreeNode( 27 , new BTreeNode( 13 , null , null ) , new BTreeNode( 38 , null , null ) ) , new BTreeNode( 72 , new BTreeNode( 64 , null , null ) , new BTreeNode( 81 , null , null ) ) ) ; print_tree( top ) ; System.out.println() ; if ( tree_search( top , 64 ) >= 0 ) System.out.println( "Find." ) ; } }
struct Tree { struct Tree* left ; int data ; struct Tree* right ; } ; // 2分木を作る補助関数 struct Tree* tcons( struct Tree* L , int d , struct Tree* R ) { struct Tree* n = (struct Tree*)malloc( sizeof( struct Tree ) ) ; if ( n != NULL ) { /* (A) */ n->left = L ; n->data = d ; n->right = R ; } return n ; } // 2分探索木よりデータを探す int tree_search( struct List* p , int key ) { while( p != NULL ) { if ( p->data == key ) return key ; else if ( p->data > key ) p = p->left ; else p = p->right ; } return -1 ; // 見つからなかった } struct Tree* top = NULL ; void main() { // 木構造をtcons()を使って直接生成 (B) top = tcons( tcons( tcons( NULL , 13 , NULL ) , 27 , tcons( NULL , 38 , NULL ) ) , 42 , tcons( tcons( NULL , 64 , NULL ) , 72 , tcons( NULL , 81 , NULL ) ) ) ; printf( "%d¥n" , tree_search( top , 64 ) ) ; }
この方式の注目すべき点は、struct Tree {…} で宣言しているデータ構造は、2つのポインタと1つのデータを持つという点では、双方向リストとまるっきり同じである。データ構造の特徴の使い方が違うだけである。
理解度確認
- main() の (B) の部分 “top = tcons(…)” において、末端部に NULL を入れる理由を答えよ。
2分木に対する処理
2分探索木に対する簡単な処理を記述してみよう。
// (略) public class Main { public static void print_tree( BTreeNode p ) { if ( p != null ) { print_tree( p.left ) ; System.out.print( p.data + " " ) ; print_tree( p.right ) ; } } public static int count( BTreeNode p ) { // データ件数を数える // 考えてみよう } public static int sum( BTreeNode p ) { // 合計を求める // 考えてみよう } public static int max( BTreeNode p ) { // 最大値を探す // 考えてみよう } public static void main(String[] args) throws Exception { BTreeNode top = /*(略)*/ ; print_tree( top ) ; System.out.println() ; System.out.println( count( top ) ) ; // データ件数を数える System.out.println( sum( top ) ) ; // 合計を求める System.out.println( max( top ) ) ; // 最大値を探す } }
下記のC言語のプログラムをヒントとしておく。
// データを探す int search( struct Tree* p , int key ) { // 見つかったらその値、見つからないと-1 while( p != NULL ) { if ( p->data == key ) return key ; else if ( p->data > key ) p = p->left ; else p = p->right ; } return -1 ; } // データを全表示 void print( struct Tree* p ) { if ( p != NULL ) { print( p->left ) ; printf( "%d¥n" , p->data ) ; print( p->right ) ; } } // データ件数を求める int count( struct Tree* p ) { if ( p == NULL ) return 0 ; else return 1 + count( p->left ) + count( p->right ) ; } // データの合計を求める int sum( struct Tree* p ) { if ( p == NULL ) return 0 ; else return p->data + count( p->left ) + count( p->right ) ; } // データの最大値 int max( struct Tree* p ) { while( p->right != NULL ) p = p->right ; return p->data ; }
これらの関数では、木構造の全てに対する処理を実行する場合には、再帰呼び出しが必要となる。
EthernetとCSMA/CD方式
CSMA/CD方式
10BASE/5,2のような Ethernet では、1本の線を共有するバス型であり、複数の機器が同時に信号を出力すると、電圧の高低がおかしい状態となる(衝突,コリジョン)ため、同時に信号を出さない工夫が必要となる。ただし、他の人が信号線を使っていないことを確認してから、信号を出せばいいけど、確認から信号を出すまでの遅延により、衝突を避けるのは難しい。
また、1本の線を共有する機器の数が増えてくると、衝突の発生の可能性が高まってくる。
これらの問題を解決するためのルールが CSMA/CD(Carrier Sense Multiple Access with Collision Detection)方式である。
- 機器は、信号を出す場合、信号線が空いている状態を待ち、出力を行う。
- もし、複数の機器が同時に信号を出した場合、電圧異常を検知したら衝突なので再送を試みる。
- 再送を行う場合には、乱数時間待つ。(機器が多い場合は、これでも衝突が起こるかもしれない)
- 乱数時間待っても信号線が空かない場合は、乱数時間の単位時間を倍にする。
どちらにしろ、バス共有する機器の台数が増えてくると、衝突の可能性は高まり、100台を越えるような状態は通信効率も悪くなる。
ただし、最近はスイッチングHUBで通信制御を行うことが一般的になり、CSMA/CD方式ではなくフロー制御が行われる。フロー制御では、通信相手に一時的にデータを送らないように要求を出すことでパケット送受信を制御する方式。HUBのフロー制御では、基本的に衝突は起らないが、複数のネットワークケーブルから同時にHUBにデータが送られてくると、処理ができない場合がある。この際には CSMA/CD 方式での JAM 信号を使うことで衝突が発生しているように扱う。
スイッチングHUB
*BASE-T のような、HUB による接続では、複数の機器が異なる機器どうしで通信をする場合、その通信路を時分割多重するのではなく、通信相手に応じて内部回路を直接つながるように接続するスイッチングHUB(以下SW-HUB)が普及している。
ストア&フォワード方式では、フレーム(パケット)を全部読み込んで、宛先情報を確認してからパケットを送り出す方式。しかし、パケットを全部読み込んだ後の転送による遅延が問題となる。カットスルー方式は、フレーム先頭にあるMACアドレス(6byte)を読み込んで宛先が分かったらすぐにフレームを流す方式で遅延が少ない。しかしフレームにエラーが発生していてもそのままフレームを流してしまう欠点がある。この両者の利点を合わせた方式であるフラグメントフリー方式では、衝突を検出するために必要なフレームの先頭64byteを読み込んだ時点でフレームを流す方式。
バス型通信では、1本の線を共有するため、同じネットワーク内の別機器間の通信は、傍受することができる(タッピング)。しかし、SW-HUB の場合、機器同士が直接つながるので、傍受するのが困難であり、セキュリティ的にも望ましい。
SW-HUBでは、ネットワークケーブルがループ状になると、データ転送が無限に繰り返されるようになる(ブロードキャストストーム)ため、ネットワーク全体がダウンする。ブロードキャストフレームとは、新しくネットワークに入った機器が自分のMACアドレスを周囲の機器に伝えるためのパケット。ループが発生するとブロードキャストフレームが無限に繰り返し流れてしまう。
データリンク層とMACアドレス
前述のように、1つのバス型接続のネットワーク内部には、同時に設置できる機器の数には限界がある。このため、小さなネットワークに分割したもの(サブネット)を、ブリッジやルータで接続し、隣接するサブネットにサブネット内の通信情報が出ないように分割することを行う。
Ethernetに接続する機器は、機器ごとにユニークな番号(MACアドレス)を持っている。このMACアドレスは、8bit✕6個の48bitの値で、メーカー毎に割振られた範囲の値を、機器ごとに異なる値がついている。
Windows であれば、コマンドプロンプト(cmd.exe)を開き、ipconfig /allを実行するとMACアドレスを確認できる。
しかし、公衆WiFiサービスを提供する企業が、個人のネットワーク機器のMACアドレス情報を使ってユーザに合わせた広告を出すなどの使い方を始めている。しかしこれが悪用されると、企業が個人の居場所を特定できるなどの問題が出てきた。このため最近はこういった問題を防ぐためにプライベートMACアドレス/ローカルアドレスが使われるようになっており、機器ごとに異なるプライベートMACアドレスを設定したり、接続する度にデタラメなプライベートMACアドレスを設定する方法がある。
通信は、一般的に1500byte程のパケットを単位として送受信が行われる。サブネット内の通信では、自分宛のパケットかどうかをMACアドレスを見て受け取る。これらのレイヤーは、データリンク層と呼ばれる。
旧式のHUB(Dumb HUB)は、電気的に信号を増幅するだけなので、物理層(レイヤー1)だけで通信を行う。
スイッチングHUBは、MACアドレスを見て通信相手を判断(データリンク層/レイヤー2)する。このため SW-HUB の内部には「土のポートの先にどんな MAC アドレスの機器が接続されているのか」を保存するアドレステーブルを持っている。最近の家庭用の SW-HUB であれば 2000 個ぐらいの情報を覚えることができる。また最近では、SW-HUBのコネクタ毎に、パケットにタグを付加することで、1本のネットワーク経路に仮想的な複数のネットワークを構築するタグV-LANといった方式を使う場合もある。このような機能を持つSW-HUBは特にレイヤ2スイッチとも呼ばれる。
L2スイッチとL3スイッチ
サブネットに分割し、それぞれに異なるネットワーク番号を割り振り、中継するルータで FireWall を機能させることで、セキュリティを高めることが行われる。しかし、性能の高いスイッチングHUBは高価でもあり、1つのHUBに異なるネットワークを接続する必要がでてくることもある。この場合、IPアドレスを異なるネットワークの番号を偽装されると、データが盗み見られるかもしれない。
こういった相互に分離すべきネットワークであっても、柔軟なネットワーク構成とするためには、VLAN機能を持った L2スイッチ(レイヤ2スイッチングHUB) が使われる。タグVLAN機能付きのL2スイッチでは、特定のポートにVLANのタグ番号を割り当て、ポートに入る時にパケットに VLAN のタグ情報を付加し、そのパケットは同じ VLAN のタグをもつポートからしかデータを取り出せない。
L2スイッチ(レイヤ2スイッチ)は、機器のMACアドレスやパケットに付けられたVLANのタグ番号の情報(レイヤ2=データリンク層)でパケットの流れを制御している(下記OSI参照モデルの表を参照)。最近では、許可されていない機器がネットワークに侵入する不正侵入を防ぐために、登録されていないMACアドレスのパケットを通さないといった機能がある。
OSI参照モデルとレイヤ 第7層 アプリケーション層 アプリケーションの種類の規定 第6層 プレゼンテーション層 データフォーマットの交換 第5層 セッション層 コネクションの確立や切断などの管理 第4層 トランスポート層 パケットの分割合成や再送といった管理(TCP) 第3層 ネットワーク層 隣接するネットワーク間の通信(IPアドレス) 第2層 データリンク層 直接接続された機器間の通信(MACアドレス) 第1層 物理層 物理的な接続方法(コネクタや電圧など)
スイッチングHUBの中には、レイヤ3(IPアドレス)の情報でパケットの流れを制御するものもある。こういったスイッチは、L3スイッチ(レイヤ3スイッチ)と呼ばれるが、機能的にはルータと同じである。
一般的には、LANとWANを接続するための機器はルータ、LAN内部のネットワークを分離するためのものはL3スイッチと呼ぶ。
無線LANと暗号化
無線LAN(通称 WiFi)は、IEEE 802.11 にて規格が定められている。無線LANは、使う通信周波数で、2.4GHz帯を使うものと、最近増えてきた5GHz帯のものに分けられる。
- IEEE802.11a 5GHz帯を使う、最大54Mbps
- IEEE802.11b 2.4GHz帯を使う、最大11Mbps
- IEEE802.11g 2.4GHz帯を使う、最大54Mbps
- IEEE802.11n 2.4GHz/5GHzを使う、最大600Mbps
- IEEE802.11ac 5GHz帯を使う、最大6.9GBps
- IEEE802.11ad 60GHz 最大6.8Gbps
- IEEE802.11ax 2.4GHz/5GHz 最大 9.6Gbps
2.4GHz帯は、電子レンジで使う電波の周波数と重なるため、電波干渉を受けやすい。5GHz帯は、障害物の影響を受けやすい。
2.4GHzは様々な家電製品・電子機器で利用されているため、他の機器との干渉を受けやすく速度低下を起しやすいが、遠くまで電波が届きやすい。 5GHzは、この周波数帯を利用している機器が少ない為、干渉を受けにくく安定して通信できるが、あまり遠くには電波が届かず、通信が極端に不安定になる場合がある。
無線LANに接続する場合には、接続先(アクセスポイント)に付けられた名前(SSID)と、SSIDに割り振られたパスワードが必要となる。ただし無線は、電波で信号を飛ばすため、近くに行くだけで通信を傍受できる。このため、データの暗号化が必須となる。この暗号化は、そのアルゴリズムにより解読の困難さが変わる。
- WEP 64bit / 128bit – すでに古い暗号化で専用ソフトを使うとすぐに解読される可能性が高い。使うべきではない。
- WPA/WPA2 – 現時点の主流。
- 暗号化方式 TKIP や 暗号化アルゴリズム AES
無線LANでは、車でセキュリティの甘いアクセスポイント(暗号化無しやWEPを使うAP)を探し、その無線LANを使ってクラッキングなどをおこなう場合も多い。(ウォードライビング)
勝手に無線LANを使われないようにするために一般的には、(1)アクセスポイントに接続できる機器をMACアドレス(機器に割り当てられた48bitの固有値)で制限したり、(2)SSIDのステルス化(APが出す電波にSSIDを入れない方式)を行う場合も多い。ただし、これらの制限をかけても専用の機器を使えば通信は傍受可能。
携帯電話を使っていると、最近では高速大容量通信、多数同時接続、低遅延を特徴とする 5G 回線という言葉が出てくる。ここまでの話から、5G = 5GHz と勘違いするかもしれない。また、5G = 5G bps といった勘違いもあるかもしれないけど、5G = 第5世代移動通信システム(5th Generation) なので注意。
SQLの基本
先週の、関係データベースの導入説明を終えて、実際の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 "パスワード"
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に指定した物は、重複した値を格納できない。 型には、以下の様なものがある。(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 ( 値,... ) ; 要素に対応する値をそれぞれ代入する。 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 の機能を理解するために、同じ処理を C 言語で書いたらどうなるのかを示す。
((( 元のSQL ))) SELECT S.業者番号 -- 必要とされるデータを抽出する射影 -- FROM S -- 複数のテーブルを組合せる結合 -- WHERE S.優良度 >= 20 ; -- 対象となるデータを選び出す選択 -- ((( SQLをC言語で書いたら ))) // 配列の個数を求める #define 文 #define sizeofarray(ARY) (sizeof(ARY) / sizeof(ARY[0])) // C言語なら... S のデータを構造体宣言で書いてみる。 struct Table_S { char 業者番号[ 6 ] ; // 当然、C言語では要素名を char 業者名[ 22 ] ; // 漢字で宣言はできない。 int 優良度 ; char 所在[ 16 ] ; } S[] = { { "S1" , "ABC社" , 20 , "福井" } , : } ; // SELECT...をC言語で書いた場合の命令のイメージ // 結合 for( int i = 0 ; i < sizeofarray( S ) ; i++ ) { // 選択 if ( S[i].優良度 >= 20 ) // 射影 printf( "%d¥n" , S[i].業者番号 ) ; }
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 選択)を理解するために、同じ処理をC言語で書いたらどうなるかを示す。
// C言語なら struct Table_S { char 業者番号[ 6 ] ; char 業者名[ 22 ] ; int 優良度 ; char 所在[ 16 ] ; } S[] = { { "S1" , "ABC社" , 20 , "福井" } , : } ; struct Table_SG { char 業者番号[ 6 ] ; char 商品番号[ 6 ] ; int 在庫量 ; } = SG[] { { "S1" , "G1" , 300 } , : } ; // FROM S for( int i = 0 ; i < sizeofarray( S ) ; i++ ) { // FROM SG for( int j = 0 ; j < sizeofarray( SG ) ; j++ ) { // WHERE S.業者番号 = SG.業者番号 if ( strcmp( S[i].業者番号 , SG[j].業者番号 ) == 0 ) { // SELECT SG.商品番号 , S.所在 printf( "%s %s¥n" , SG[j].商品番号 , S[i].所在 ) ; } } }
(1) i,jの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.商品番号 -- 外部キー商品番号の対応付け -- // 上記の処理をC言語で書いたら struct Table_G { char 商品番号[ 6 ] ; char 商品名[ 22 ] ; char 色[ 4 ] ; int 価格 ; char 所在[ 12 ] ; } = G[] = { { "G1" , "赤鉛筆" , "青" , 120 , "福井" } , : } ; // [結合] S,G,SGのすべての組み合わせ // FROM S -- 結合 for( int i = 0 ; i < sizeofarray( S ) ; i++ ) { // FROM G -- 結合 for( int j = 0 ; j < sizeofarray( G ) ; j++ ) { // FROM SG -- 結合 for( int k = 0 ; k < sizeofarray( SG ) ; k++ ) { // [選択] 条件でレコードを選び出す // WHERE S.業者番号 = SG.業者番号 // AND SG.商品番号 = G.商品番号 if ( strcmp( S[i].業者番号 , SG[k].業者番号 ) == 0 && strcmp( SG[k].商品番号 , G[j].商品番号 ) == 0 ) { // [射影] 使用するフィールドを出力 printf( "%s %s %d\n" , S[i].業者名 , G[j].商品名 , SG[k].在庫量 ) ; } } } }
ここで結合と選択で実行している内容は、外部キーである業者番号を S から探す、商品番号を G から探している。この、外部キー対応しているものを探すという視点で、上記 C 言語のプログラムを書き換えると、以下のように表せる。
// FROM SG for( int k = 0 ; k < sizeofarray( SG ) ; k++ ) { // 外部キー SG.業者番号に対応するものを S から探す for( int i = 0 ; i < sizeofarray( S ) ; i++ ) { if ( strcmp( S[i].業者番号 , SG[k].業者番号 ) == 0 ) { // 外部キー SG.商品番号に対応するものを G から探す for( int j = 0 ; j < sizeofarray( G ) ; j++ ) { if ( strcmp(SG[k].商品番号,G[j].商品番号) == 0 ) { printf( "%s %s %d\n" , S[i].業者名,G[j].商品名,SG[k].在庫量 ) ; } } } } }
このような、複数の表の実体と関係を対応付けた検索を、データベースの専門の人は「データを串刺しにする」という言い方をすることも多い。
また、SQL では、このようなイメージの繰り返し処理を、数行で分かりやすく記述できている。このC言語のプログラム例では、キーに対応するものを単純 for ループで説明しているが、SQL ではプライマリキーなら、B木やハッシュなどを用いた効率の良い検索が自動的に行われる。このため、SQLを扱う応用プログラマは、SQLの結合の処理方法は通常はあまり考えなくて良い。
CTF問題とセキュリティ(4年実験)
この実験では、セキュリティコンテストのCTF問題(Capture The Flag競技)について、インターネットの仕組みを理解し、その問題の解き方を考え、新しく自分自身でCTF問題を作ってもらいます。
日程
実験は、4週にわたり、以下の日程で行います。
- 1回目の実験グループは、Cyber SAKURA CTF大会(オンライン) 10/26に腕試しで参加しませんか?
週 | 内容 |
1 | (前半)暗号・ファイル・Web |
2 | |
3 | (後半) プログラム作成・インターネット・OS |
4 |
前半・後半でそれぞれ、問題例(もしくは自分で見つけたCTF問題)の1つの説明と自作のCTF問題1つを新しく作り説明をしてください。最終的に4つの問題の説明のレポートを提出となります。
提出物
- 実験の目的
- 問題例(or 自分で見つけたCTF問題)を1つ選び
- 前半・後半それぞれについて
- その問題が情報セキュリティにどう関係しているのか
- 問題の解き方のしくみと解説
- 自作問題について
- 前半・後半それぞれ
- その問題が情報セキュリティにどう関係しているのか
- 問題の解き方、問題の作り方
- しくみと解説
- 提出先はこちらのTeams共有フォルダに。
- 自作問題の例
- サブリミナル効果で隠されたGIF
- 電子透かし
- 学校からアクセスする時だけ、答えが見える。(REMOTE_HOST)
- スマホでアクセスした時だけ、答えが見える。(HTTP_USER_AGENT)
- Google 検索した後だけ、答えが見える。(HTTP_REFERER)