SQL 問い合わせ

• SQL のデータ型

  SQL の標準が定めるデータ型は多数あるが，代表的なものを下にまとめる。

  • NULL: 空値 (a NULL value)
  • integer: 符号付きの整数 (signed integer) ※ SQLite では BIGINT と書いても integer と書いても同じ「８バイトの整数」という意味
  • real: 浮動小数点値 (floating point value)
  • char, STRONG>text: 文字列 (text string) ※ char(n) のように，最大の長さを指定することもある．
  • datetime: 日付や時刻など

    ※ 日時を示す datetimeについて．SQLite では datetime と書いても text と書いても同じ「可変長文字列」の意味．だけど使い分けるべき（その方が分かりやすい）

  • <bool: ブール値
  • BLOB: バイナリ・ラージ・オブジェクト (Binary Large Object). 入力がそのままの形で格納される (stored exactly as it was inpu
  　
• SQL の文字列定数

  SQL の規格では，文字列定数はシングルクオーテーションマーク「'」で囲むことになっている．

• SQLでのプレフィックス文字「N」

  テキストによっては，SQLでのプレフィックス文字「N」を使う場合があるので，ここで説明しておく．

  日本語を含むような列は，SQLのCHARACTER型ではなく，NATIONAL CHARACTER型（NCHAR型とも書く．同じ意味）として定義されるのが普通である．NATIONAL CHARACTER型での比較では，「専攻 = N'情報知能工学'」のように，定数である「'情報知能工学'」の前に，プレフィックス文字「N」を付ける（付ける流儀が，SQLの正式な記法である．本によっては省略している場合がある）．

• SQL の比較演算子

  比較演算子は2項述語である． SQL で，数値や文字列の比較演算子には次のようなものがある

  • <
  • <=
  • =
  • >=
  • >
  • <>
• SQL の論理演算子

  AND, OR, NOT

• IS NULL

  NULL 値を持つような行を得るために使うことができる．

• \'

  「\'」は，「'」を含むような文字列を扱うための記法

• LIKEと%と_

  SQLでの文字列のマッチングに使うキーワード

• SQL 問い合わせ (SQL query)

  SQL 問い合わせは，リレーショナルデータベースに格納された1つまたは複数のテーブルを使う．問い合わせの評価結果として，リレーショナルデータベース内のテーブルをそのままの形で得るということはめったになく，新しいテーブルが 1つ作られる．

  SQL 問い合わせのプログラムは，SELECT, FROM, WHERE を使うのが基本である． 文法は次のようになる．※ SQLの文法は多彩なので，ここでは，基本的な場合に説明を絞る．

      SELECT <list-of(result-column)>
      FROM <list-of(table-name)>
  

  あるいは

      SELECT <list-of(result-column)>
      FROM <list-of(table-name)>
      WHERE <expression>
  

  以上のことを例を使って説明する．例えば，

  SELDCT A1, A2, ..., An
  FROM   T1, T2, ,,,, Tm
  WHERE  <expression>
  

  のように書くと，m 個のテーブル T1, T2, ..., Tm の直積集合から <expression> を満足する行のみを選び，そうして出来たテーブルの属性 A1, A2, ..., An を出力するという意味になる．

  WHERE の部分が無い場合，例えば，

  SELDCT A1, A2, ..., An
  FROM   T1, T2, ,,,, Tm
  

  のように書くと，m 個のテーブル T1, T2, ..., Tm の直積集合から属性 A1, A2, ..., An を出力するという意味になる．

  • SELECT <list-of(result-column)>

    SELECT の後には，問い合わせの結果として得たい列名あるいは列名を含む式の，1個以上の並びを書く． 2個以上ある場合には半角のカンマ「,」で区切る． 問い合わせ結果として得られるテーブルの中の列の並びは，SQL の SELECT に書いた列名あるいは列名を含む式の並びの順序通りになる．

    SELECT の後に列名を書くとき， 「<テーブル名>.<列名>」のように，「<テーブル名>.」を前に付けねばならない場合がある． これは，FROM に指定するテーブルが2個以上あり，しかも，これらのテーブルが同じ属性名の属性を持つ場合である．例えば， 「科目」と「履修」という2つのテーブルが，同じ属性名「科目番号」を持つ場合，下記のように「科目番号」の前に「科目.」を付けるなどして，あいまいさを排除する必要がある．

        SELECT 科目.科目番号, 科目名
        FROM 科目, 履修
            WHERE 科目.科目番号 = 履修.科目番号 AND 履修.学生番号 = 00001;
    

    列名として「*」あるいは「<テーブル名>.*」のような書き方ができ，これは、全ての属性名を並べて書 いたのと同じ意味を持つ．

  • FROM <list-of(table-name)>

    FROM の後には，テーブル名の1個以上の並びを書く．2個以上ある場合には半角のカンマ「,」で区切る．

    テーブル参照リストの中で，タップル変数を定義することもできる． 例えば，下記の SQL 文では X と Y の 2つのタップル変数が定義されている．

                SELECT X.社員番号, Y.社員番号
                FROM 社員 X, 社員 Y
                WHERE X.部長 = Y.社員番号 AND X.給与 > Y.給与
    

  • WHERE <expression>

    WHERE の後には探索条件を書く．複数の探索条件を組み合わせたい場合には，（半角のカンマではなく）論理演算の AND, OR システムを用いる． 探索条件は，FROM で指定したテーブルの属性名や，FROM で指定したタップル変数の変数名を含む式である． 探索条件に使用できるキーワードとしては次のものがある．

    • 比較演算（<, <=, =, >=, >, <>）
    • 論理演算（AND, OR, NOT）
    • 各種の述語（BETWEEN, IN, LIKE, NULL, EXIST など）

    など

• 入れ子型質問

  SQL での入れ子型質問では，探索条件に，SELECTで始まるSQL文が入る．探索条件の中のSQL文のことを副問い合わせという．入れ子型質問では，

      属性名 IN ＜副問い合わせ＞
  

  あるいは

  　　　　値     IN ＜副問い合わせ＞
  

  という形をした述語が現れる．これは，指定した属性名や値が，副問い合わせの評価結果に含まれるという意味になる．

  評価結果を (x1, x2, ..., xn) とするとき, 「x IN (x1, x2, ..., xn)」は，x = x1 OR x = x2 OR ... OR x = xn と等価である．

• θ-結合演算

  R(A1, A2, …, An) とS(B1, B2, …, Bm)をリレーションとする．R の属性 Ai と S の属性 Bj 上のθ-結合演算を「R[AiθBj]S」と書く．定義は次の通りである．（θは比較演算子である）．

  R[AiθBj]S は，R と S の直積集合 (R×S) の中から「R.AiθS.Bj」を満足する要素を選んだもの
  つまり，θ-結合演算は，直積演算と， -選択演算を使って，次のように定義することもできる．
  R[Ai θ Bj]S = (R×S) [R.Ai θ S.Bj]

  θ-結合演算「R[AiθBj]S」を SQL を用いて次のように書き下すことができる．

  SELECT *
  FROM R, S
  WHERE <AiθBj>
  

  ※ （参考）リレーショナル代数式を使って次のように書くことができる．

  　　R[Aiθ Bj]S = { (t, u) | t ∈ R ∧ u ∈ S ∧ t[Ai]θu[Bj] }
  

  但し，「(t, u)」と書いているのは，t = (a1, a2, …, an), u = (b1, b2, …, bm) とするときに，(t, u) = (a1, a2, …, an, b1, b2, …, bm) なる n + m 項のタップルである．

  ※　関係演算で「射影」という演算がある．射影 results[name, student_name] といったときは，属性 name と teacher_name だけを抜き出した上で， 重複行があるとき１つを残して他を除去することになっている

• 結合問い合わせ（結合質問ともいう)

  結合問い合わせとは，問い合わせの中の「WHERE 」のところの「」の中に，θ-結合演算を含むような問い合わせのことである． 結合問い合わせでは，SQL のテーブル参照リストに，2つ以上のテーブル名が現れる．一方で，結合問い合わせの評価結果は，一般の問い合わせと同様に1つのテーブルであることに注意して欲しい．結合問い合わせでは，表参照リストに登場する表の結合が行われる．

  SQL 問い合わせのプログラムは，SELECT, FROM, WHERE を使うのが基本である． 文法は次のようになる．※ SQLの文法は多彩なので，ここでは，基本的な場合に説明を絞る．

      SELECT
      FROM
  

  あるいは

      SELECT
      FROM
      WHERE 
  

• 商演算

  R(A1, A2, …, An-m, B1, B2, …, Bm) をn次のリレーション，S(B1, B2, …, Bm)をm次のリレーションとする（但し, m < n）．R システムをSで割った商を「R÷S」と書く．定義は次の通りである．

  R÷S = { t | t ∈R[A1, A2, …, An-m] ∧ (∀u∈S) ((t, u) ∈R )} 
  

  但し，「(t, u)」と書いているのは，t = (a1, a2, …, an-m), u = (b1, b2, …, bm) とするときに，(t, u) = (a1, a2, …, an, b1, b2, …, bm) なる n 項のタップルである．「∀」は，全称作用素であり，全て（for all）を表す． 「R[A1, A2, …, An-m]」と書いているのは射影演算であり，「{ u | u ∈ dom(A1)×dom(A2)×…×dom(An-m) ∧ (∃t∈R) (t[A1] = u[A1]∧t[A2] = u[A2]∧ … ∧t[An-m] = u[An-m])}」のことである．

• テキスト・エンコーディング (text encoding)

  データベースには，文字データはエンコード (encoding) されて格納されている． エンコードの手法にはいくつかの種類がある． SQLite バージョン 3 では，エンコードとして次のいずれかを指定できる．

  • UTF-8
  • big-endian UTF-16
  • little-endian UTF-16
• リレーショナル代数演算子

  リレーショナル代数は，集合論に基づく体系である．集合演算と，リレーショナル代数に特有の演算がある．

  　【集合演算】 　　和集合演算，差集合演算，共通集合演算，直積集合演算

  　【リレーショナル代数に特有の演算】 　　射影演算，選択演算，結合演算，商演算

  これら8つの演算は必ずしも独立ではない．例えば，共通集合演算は，差集合演算を使って同等のことを行える．結合演算は，直積演算と選択演算を使って同等のことを行える．商演算は，直積演算と射影演算と差演算を使って同等のことを行える．

• 　和両立

  リレーションR(A1, A2, …, An) とS(B1, B2, …, Bm) が和両立であるとは，次の2条件を満足しているときを言う．

  1. RとSの次数が等しい．つまり n = m
  2. 各i（1≦i≦n）について，AiとBiのドメインが等しい．
• 和集合演算

  RとSを和両立なリレーションとする．RとSの和集合演算は「R∪S」と書く．定義は次の通りである．

  R∪S = { t | t ∈ R ∨ t ∈ S }
  

• 　差集合演算

  RとSを和両立なリレーションとする．RとSの差集合演算は「R−S」と書く．定義は次の通りである．

  R−S = { t | t ∈ R ∧ ¬( t ∈ S ) }
  

• 共通集合演算

  RとSを和両立なリレーションとする．RとSの共通集合演算は「R∩S」と書く．定義は次の通りである．

  R∩S = { t | t ∈ R ∧ t ∈ S }
  

• 　直積集合演算

  R(A1, A2, …, An) とS(B1, B2, …, Bm)をリレーションとする，RとSの直積集合演算は「R×S」と書く．定義は次の通りである．

  　　R×S = { (r, s) | r ∈ R ∧ s ∈ S }
  

  但し，「(r, s)」と書いているのは，r = (a1, a2, …, an), s = (b1, b2, …, bm) とするときに，(r, s) = (a1, a2, …, an, b1, b2, …, bm) なる n + m 項のタップルである．

• 射影演算

  R(A1, A2, …, An) をリレーションとする．全属性集合 {A1, A2, …, An} の部分集合 X = {Ai1, Ai2, …, Aik}について，R のX上の射影は，「R[X]」あるいは「R[Ai1, Ai2, …, Aik]」と書く．定義は次の通りである．

  R[Ai1, Ai2, …, Aik] = { u | u ∈ dom(Ai1)×dom(Ai2)×…×dom(Aik) 
  ∧ (∃t∈R) (t[Ai1] = u[Ai1]∧t[Ai2] = u[Ai2]∧ … ∧t[Aik] = u[Aik])}
  

  ※「R[Ai1, Ai2, …, Aik]」の代わりに「πAi1, Ai2, …, Aik(R)」のように書くこともある．

• 　選択演算

  R(A1, A2, …, An) をリレーションとする．R の属性AiとAj上の -選択演算を「R[Ai Aj]」と書く．定義は次の通りである．θは2項述語である．

  　　R[Aiθ Aj] = { t | t ∈ R ∧ t[Ai]θt[Aj] }
  

  R の属性Aiと値cに関する -選択演算を「R[Aiθc]」と書く．定義は次の通りである． 　　R[Aiθc] = { t | t ∈ R ∧ t[Ai]θc } 　※「R[AiθAj]」や「R[Aiθc」の代わりに「πAiθAj(R)」や「πAiθc(R)」のように書くこともある．
• リレーショナル代数での比較演算子

  比較演算子は2項述語である．リレーショナル代数では，数値及び文字列に関する比較演算子として，＞，≧，＝，≦，＜，≠ の6種類を考えるのが普通である．※ SQL では「<=」, 「>=」, 「<>」のように書いていた．混同しないこと．

• リレーショナル演算

  ※　定義では論理式などを使用．論理式の記法は，集合を基礎とする論理式にしている（テキストと同じ流儀），タプルリレーショナル論理式（一階述語論理を基礎とする）にはなっていない．

  • 和集合演算

    R∪S R∪S = { t | t ∈ R ∨ t ∈ S }

  • 差集合演算

    R−S R−S = { t | t ∈ R ∧ ¬( t ∈ S ) }

  • 共通集合演算

    R∩S R∩S = { t | t ∈ R ∧ t ∈ S }

  • 直積集合演算

    R×S R×S = { (r, s) | r ∈ R ∧ s ∈ S }

    ※ (r, s) は，r = (a1, a2, …, an), s = (b1, b2, …, bm) とするときに，(r, s) = (a1, a2, …, an, b1, b2, …, bm) なる n + m 項のタップル

  • 　射影演算

    R[Ai1, Ai2, …, Aik]

    R[Ai1, Ai2, …, Aik] = { u | u ∈ dom(Ai1)×dom(Ai2)×…×dom(Aik) ∧ (∃t∈R) (t[Ai1] = u[Ai1]∧t[Ai2] = u[Ai2]∧ … ∧t[Aik] = u[Aik])}

  • 選択演算

    R[AiθAj]，R[Aiθc] R[AiθAj] = { t | t ∈ R ∧ t[Ai]θ t[Aj] } R[Aiθc] = { t | t ∈ R ∧ t[Ai] θc }

  • 結合演算 R[Ai θ Bj]S R[Ai θ Bj]S = { (t, u) | t ∈ R ∧ u ∈ S ∧t[Ai] θ u[Bj] }

    ※ (t, u)は，t = (a1, a2, …, an), u = (b1, b2, …, bm) とするときに，(t, u) = (a1, a2, …, an, b1, b2, …, bm) なる n + m 項のタップル

  • 自然結合

    R * S R * S = (R[C1 = C1, C2 = C2, …, Ck = Ck]S) [A1, A2, …, An, D1, D2, …, Dm-k],

    ※ C1, C2, …, Ck は，AとB の結合属性, {B1, B2, …, Bm}−{C1, C2, …, Ck} = {D1, D2, …, Dm-k}

  • 商演算

    R÷S R÷S = { t | t ∈R[A1, A2, …, An-m] ∧ (∀u∈S) ((t, u) ∈R )}

  　
• タプルリレーショナル論理

  タプルリレーショナル論理は，一階述語論理に基づき，リレーションを宣言的に記述したもの．タプルリレーショナル論理表現では，以下に説明する「アトム」と「式」の組み合わせを使う．

  ※　「∈」のような記号は現れない．

  1. アトム
     　
     • 　リレーション 「R(u)」の形　
     • 　タプルの属性値同士の比較 「u[Ai] θ v[Bj]」の形
     • 　タプルの属性値と定数との比較 「u[Ai] θ c」の形

     ※　「R(u)」は，u がリレーションR のタップルであるとき真(true)になる述語．

     「u[Ai]」は，u = (a1, a2, …, an) の第i成分を表す．「v[Bi]」も同様

  2. 式　（以下，式を「Φ」と書く）
     • アトム
     • 式の論理否定 ¬Φ　の形
     • 式の論理積，論理和 Φ1∧Φ2，Φ1∨Φ2　の形
     • 全称記号や存在記号を用いた変数の定義 (∃u)Φ(u), (∀u)Φ(u)　の形

     全称記号や存在記号を用いて定義された変数は，自由変数でなく，束縛変数である．

     タプルリレーショナル論理表現されたリレーションは，「{ t | t が満たすべき条件を示した式}」のような形になる（この式が，t以外の自由変数を含まないことが，タプルリレーショナル論理表現が成り立つ条件になる）

  　

演習

演習で行うこと

• SQLiteman の起動と終了 (Start and end SQLiteman)
• SQLiteman で新しいデータベースを作成する (Create a new database)
• SQLiteman で既存のデータベースを開く (Open an existing database using SQLiteman)
• SQL を用いたテーブル定義と一貫性制約の記述 (Table defintion and integrity constraint specification using SQL)

  リレーショナル・スキーマ (relational schema):

  products(id, product_name, type, price, created_at, updated_at)

  SQL プログラム:

  create table products (
      id            integer  primary key autoincrement not null,
      product_name  text     UNIQUE not null,
      type          text     not null,
      price         real,
      created_at    datetime not null,
      updated_at    datetime );
  

• SQL を用いたテーブルへの行の挿入 (Insert rows into a table using SQL)

  begin transaction;
  insert into products values( 1, 'Fukuoka apple',     'apple',  50, datetime('now', 'localtime'), NULL );
  insert into products values( 2, 'Kumamoto orange L', 'orange', 30, datetime('now', 'localtime'), NULL );
  insert into products values( 3, 'Kumamoto orange M', 'orange', 20, datetime('now', 'localtime'), NULL );
  insert into products values( 4, 'Fukuoka melon',     'melon',  NULL, datetime('now', 'localtime'), NULL );
  commit;
  

• SQL 問い合わせの発行と評価結果の確認 (Issue SQL queries and inspect the results)

  SELECT * FROM products;
  SELECT * FROM products WHERE type = 'orange';
  SELECT * FROM products WHERE price > 25;
  

• SQL を用いたテーブル定義と一貫性制約の記述 (Table defintion and integrity constraint specification using SQL)

  リレーショナル・スキーマ (relational schema):

  orders(id, year, month, day, customer_name, product_id, qty, created_at)

  SQL プログラム:

  create table orders (
      id            integer  primary key autoincrement not null,
      year          integer  not null check ( year > 2008 ),
      month         integer  not null check ( month >= 1 AND month <= 12 ),
      day           integer  not null check ( day >= 1 AND day <= 31 ),
      customer_name text  not null,
      product_id    text  not null,
      qty           integer  not null DEFAULT 1 check ( qty > 0 ),
      created_at    datetime not null );
  

• SQL を用いたテーブルへの行の挿入 (Insert rows into a table using SQL)

  begin transaction;
  insert into orders values( 1001, 2014, 10, 26,  'kaneko',   1, 10, datetime('now', 'localtime') );
  insert into orders values( 1002, 2014, 10, 26,  'miyamoto', 2, 2, datetime('now', 'localtime') );
  insert into orders values( 1003, 2014, 10, 27,  'kaneko',   3, 8, datetime('now', 'localtime') );
  insert into orders values( 1004, 2014, 10, 27,  'kaneko',   3, 8, datetime('now', 'localtime') );
  commit;
  

• SQLiteman を用いたデータのブラウズ (Browse Data using SQLiteman)
• SQL 問い合わせの発行と評価結果の確認 (Issue SQL queries and inspect the results)
• SQL を用いたテーブル定義と一貫性制約の記述 (Table defintion and integrity constraint specification using SQL)

  リレーショナル・スキーマ (relational schema, created_at):

  bundles(id, order_id, qty, shipping_id)

  shippings(id, year, month, day, created_at)

  SQL プログラム:

  create table bundles (
      id          integer  primary key autoincrement not null,
      order_id  integer  not null REFERENCES orders(id),
      qty         integer  not null,
      shipping_id integer  not null REFERENCES shippings(id),
      created_at datetime not null );
  
  create table shippings (
      id    integer  primary key autoincrement not null,
      year  integer  not null check ( year > 2008 ),
      month integer  not null check ( month >= 1 AND month <= 12 ),
      day   integer  not null check ( day >= 1 AND day <= 31 ),
      created_at datetime not null );
  

• SQL を用いたテーブルへの行の挿入 (Insert rows into a table using SQL)

  begin transaction;
  insert into bundles values( 1, 1001, 2, 101, datetime('now', 'localtime') );
  insert into bundles values( 2, 1001, 1, 102, datetime('now', 'localtime') );
  insert into bundles values( 3, 1002, 1, 103, datetime('now', 'localtime') );
  insert into bundles values( 4, 1003, 2, 104, datetime('now', 'localtime') );
  insert into bundles values( 5, 1004, 4, 104, datetime('now', 'localtime') );
  commit;
  

  begin transaction;
  insert into shippings values( 101, 2019, 10, 28, datetime('now', 'localtime') );
  insert into shippings values( 102, 2019, 10, 31, datetime('now', 'localtime') );
  insert into shippings values( 103, 2009, 11,  1, datetime('now', 'localtime') );
  insert into shippings values( 104, 2009, 11,  2, datetime('now', 'localtime') );
  commit;
  

• SQLiteman を用いたデータのブラウズ (Browse Data using SQLiteman)
• SQL 問い合わせの発行と評価結果の確認 (Issue SQL queries and inspect the results)

演習を行うために必要になる機能や文法

SQLite 3の SQL の説明は http://www.hwaci.com/sw/sqlite/lang.html (English Web Page) にある．

• SQL 問い合わせ (SQL query)
  • SELECT <list-of(result-column)> FROM <list-of(table-name)>

    table-name で指定したテーブルの直積集合から，list-of(result-column) で指定した属性を抽出したり，式を評価して新しいテーブルを 1つ作り出力する．

  • SELECT <list-of(result-column)> FROM <list-of(table-name)> WHERE <expression>

    table-name で指定したテーブルの直積集合から，<expression> を満足する行のみを選び，そうして出来たテーブルから，list-of(result-column) で指定した属性を抽出したり，式を評価して新しいテーブルを 1つ作り出力する．

SQLiteman の起動と終了 (Start and end SQLiteman)

create table などの SQL コマンドを編集できるエディタの機能等をもったソフトウェアとして SQLiteman を使うことにする． (We use the SQLiteman as SQL editor, database manager interface, ...)

1. SQLiteman の起動 (Start SQLiteman)

   ■ Windows での SQLiteman の起動例

   「SQLiteman」 のアイコンをダブルクリック (double click "Sqliteman.exe")

   

   SQLiteman の新しいウインドウが開く．(A New window appears)

   

   ■ Ubuntu での SQLiteman の起動例

   端末で「sqliteman」+ Enter キーと操作する．

   

   SQLiteman の新しいウインドウが開く．(A New window appears)

   
2. ヘルプの表示 (Help Content)

   「Help」→ 「Help Content」 と操作する

   

   ヘルプが表示されるので確認する

   
3. SQLiteman の終了 (End SQLiteman)

   「File」→ 「Exit」で終了．

   

SQLiteman で新しいデータベースを作成する (Create a new database)

以下の手順で，新しいデータベースを作成する．その結果，データベースファイルができる． (Create a new database)

1. データベースを新規作成したいので 「File」→ 「New」 と操作する
   
2. データベースの新規作成を開始する (Start a new databae creation)

   ■ Ubuntu での実行例（データベースファイル名を「mydb2」にする場合）

   データベースファイル名である mydb2 を指定し， 「保存」をクリック

   ※ データベースファイル名は何でも良いが、英語の名前がよい．

   ※ データベースを新規作成したいときは，データベースファイル名として「新しい」ものを指定すること

   
   
3. データベースの中身が表示されるので確認する (Database appears)

   このときテーブル (Tables) 数も，ビュー (Views) の数も 0 である．

   
4. テキスト・エンコーディングの設定を確認する (text encoding)

   まず，「Pragmas」をクリック．(Click 'Pragmas')

   

   encodingの行に「UTF-8」のように表示されている．

   

   ※ もし，データベースの文字のエンコーディングを変えたいときは， SQLiteman のようなグラフィカルなツールを使うのではなく， sqlite.exe を起動し「PRAGMA encoding=...;」で変える方がずっと簡単でしょう． 例えば「UTF-16le」などに変えたいときは「PRAGMA encoding=UTF-16le;」

5. 終了 (End SQLiteman)

   あとの混乱を防ぎたいので、１度、SQLiteman を終了する

   「File」→ 「Exit」で終了．

   

SQLiteman で既存のデータベースを開く (Open an existing database using SQLiteman)

すでに作成済みのデータベースを，下記の手順で開くことができる．

以下の手順で，既存のデータベースファイルを開く． (Open an existing database file)

1. 「File」→ 「Open」
   
2. データベースファイルを開く (Open Database File)

   ■ Windows での実行例（「C:\SQLite\mydb」を開く場合）

   データベースファイル C:\SQLite\mydb を選び， 「開く」をクリック (Click '開く' after choosing the database file "C:\SQLite\mydb")

   

   ■ Ubuntu での実行例（「SQLite/mydb」を開く場合）

   データベースファイル mydb2 を選び， 「開く」をクリック (Click '開く' after choosing the database file "mydb2")

   要するに，ホームディレクトリの の下の mydb2 を選ぶ．　

   
3. データベースの中身が表示されるので確認する (Database appears)

   ◆ 表示例（データベースの中身によって表示が変わる）

   
4. 「Tables」の数字が1以上の場合には展開できる

   「Tables」の数字が1以上のとき，「Tables」を展開すると，テーブルの一覧 (List of Tables) が表示されるので確認する (List of tables appears by clicking 'Tables')

   ◆ 展開の例

   
5. テキスト・エンコーディングの設定を確認する (text encoding)

   まず，「Pragmas」をクリック．(Click 'Pragmas')

   

   encodingの行に「UTF-8」のように表示されている．

   

   ※ もし，データベースの文字のエンコーディングを変えたいときは， SQLiteman のようなグラフィカルなツールを使うのではなく， sqlite.exe を起動し「PRAGMA encoding=...;」で変える方がずっと簡単でしょう． 例えば「UTF-16le」などに変えたいなど．

6. 「System Catalogue」を展開し，「sqlite_master」をクリックすると，データベース・スキーマ (database schema) が表示されるので確認する (Database schema appears by clicking 'sqlite_master')

   ◆ 表示の例（１）

   データベースが空の場合，表示も空．

   

   ◆ 表示の例（２）

   score_records, order_records などのテーブルを定義すみの場合

   

SQL を用いたテーブル定義と制約の記述 (Table definition and integrity constraint specification using SQL)

SQL を用いて，products テーブルを定義し，制約を記述する． (Define 'products' table and specify constrants of the table using SQL)

リレーショナル・スキーマ (relational schema): products(id, product_name, type, price, created_at, updated_at)

1. products テーブルの定義 (Define a table)

   次の SQL を入力し，「Run SQL」のアイコンをクリック (Write the following SQL, and click "Run SQL" icon).

   create table products (
       id            integer  primary key autoincrement not null,
       product_name  text     UNIQUE not null,
       type          text     not null,
       price         real,
       created_at    datetime not null,
       updated_at    datetime );
   

   ※ 「SQL Editor」のウインドウには，SQL プログラムを書くことができる．
   (In the 'SQL Editor' window, you can write down SQL program(s).)

   
2. コンソールの確認 (Inspect console)

   エラーメッセージが出ていないことを確認

   

SQL を用いたテーブルへの行の挿入 (Insert rows into a table using SQL)

次のような products テーブルを作る． (Construct table 'products')

以下の手順で，SQL を用いて products テーブルへの行の挿入を行う (Insert rows into table 'products' using SQL)

1. SQL プログラムの記述

   「insert into ...」は行の挿入．ここには 4つの SQL 文を書き， 「begin transaction」と「commit」で囲む． ("insert into ..." means inserting a row into a table. Four SQL statements are wrote).

   ※ つまり， 挿入の前に begin transaction; を実行し，一連の挿入が終わったら commit; を実行する．(Issue "begin transaction" before database update and "commit" after database update).

   begin transaction;
   insert into products values( 1, 'Fukuoka apple',     'apple',  50, datetime('now', 'localtime'), NULL );
   insert into products values( 2, 'Kumamoto orange L', 'orange', 30, datetime('now', 'localtime'), NULL );
   insert into products values( 3, 'Kumamoto orange M', 'orange', 20, datetime('now', 'localtime'), NULL );
   insert into products values( 4, 'Fukuoka melon',     'melon',  NULL, datetime('now', 'localtime'), NULL );
   commit;
   
   

   insert into には 2つの方法がある．(Two styles of "insert into")

   ■ 属性の値を，テーブル定義の順に全て並べる (List all attribute values. The order is the same as its table definition)

   insert into products values( 1, 'Fukuoka apple',     'apple',  50, datetime('now', 'localtime') );
   

   ■ 属性の値の並び方を，属性名を使って明示的に指定する (Specify the order of attribute values using attribute name list)

   このとき，属性値を省略すると，テーブル定義のときに指定されたデフォルト値が使われる (defaults values are used)

   insert into products values( 2, 'Kumamoto orange L', 'orange', 30, datetime('now', 'localtime') );
   

2. 複数の SQL 文の一括実行 (Execute multiple SQL statements)

   複数の SQL 文を一括実行したいので，カーソルを先頭行に移動した後に， 「Run multiple SQL statements ...」のボタンをクリックする．
   (Move the cursor to the top statement. Click "Run multiple SQL statements from current cursor position in one batch" icon)

   
3. 「Script Output」ウインドウの確認 (Inspect "Script Output" window)

   エラーメッセージが出ていないことを確認

   

   ※ エラーメッセージが出ているときは，SQL を書き直して，もう１度「Run multiple SQL statements ...」のボタンをクリックする．

   例えば，下記のようにエラーが出ていたとする．このときは，

   • 「begin transaction」は終わっている．
   • それ以降は実行されていない

   

   このようなときは，再開したい行にカーソルをあわせて、「Run multiple SQL statements ...」のボタンをクリックする．

SQL 問い合わせの発行と評価結果の確認 (Issue SQL queries and inspect the results)

ここでは，SQL を用いた問い合わせの実行例を示す． SQL 問い合わせの詳細については，別の Web ページで説明する．ここでは，テーブルの中身を確認して欲しい．

テーブルの全ての行の表示 (List all rows of a table)

SELECT * FROM products;

条件を満足する行のみの表示 (List the rows which satisfy a given condition)

SELECT * FROM products WHERE type = 'orange';

SELECT * FROM products WHERE price > 25;

SELECT * FROM products WHERE price > 18;

SQL を用いたテーブル定義と一貫性制約の記述 (Table defintion and integrity constraint specification using SQL)

SQL を用いて，orders テーブルを定義し，一貫性制約を記述する． (Define a table 'orders'. Specify integrity constrants of the table using SQL)

1. products テーブルの定義 (Define a table)

   次の SQL を入力し，「Run SQL」のアイコンをクリック (Write the following SQL, and click "Run SQL" icon).

   create table orders (
       id            integer  primary key autoincrement not null,
       year          integer  not null check ( year > 2008 ),
       month         integer  not null check ( month >= 1 AND month <= 12 ),
       day           integer  not null check ( day >= 1 AND day <= 31 ),
       customer_name text  not null,
       product_id    text  not null,
       qty           integer  not null DEFAULT 1 check ( qty > 0 ),
       created_at    datetime not null );
   
   

   
2. コンソールの確認 (Inspect console)

   エラーメッセージが出ていないことを確認

   

SQL を用いたテーブルへの行の挿入 (Insert rows into a table using SQL)

次のような orders テーブルを作る． (Construct table 'orders')

以下の手順で，SQL を用いて orders テーブルへの行の挿入を行う (Insert rows into table 'orders' using SQL)

1. SQL プログラムの記述

   「insert into ...」は行の挿入．ここには 4つの SQL 文を書き， 「begin transaction」と「commit」で囲む． ("insert into ..." means inserting a row into a table. Four SQL statements are wrote).

   begin transaction;
   insert into orders values( 1001, 2014, 10, 26,  'kaneko',   1, 10, datetime('now', 'localtime') );
   insert into orders values( 1002, 2014, 10, 26,  'miyamoto', 2, 2, datetime('now', 'localtime') );
   insert into orders values( 1003, 2014, 10, 27,  'kaneko',   3, 8, datetime('now', 'localtime') );
   insert into orders values( 1004, 2014, 10, 27,  'kaneko',   3, 8, datetime('now', 'localtime') );
   commit;
   
   

   
2. 複数の SQL 文の一括実行 (Run multiple SQL statements)

   複数の SQL 文を一括実行したいので，カーソルを先頭行に移動した後に，「Run multiple SQL statements ...」のボタンをクリックする．
   (Move the cursor to the top statement. Click "Run multiple SQL statements from current cursor position in one batch" icon)

   
3. 「Script Output」ウインドウの確認 (Inspect "Script Output" window)

   エラーメッセージが出ていないことを確認

   

SQLiteman を用いたデータのブラウズ (Browse Data using SQLiteman)

• products テーブル

  まず，オブジェクト・ブラウザ (Object Browser) の中の「Tables」を展開 (Click 'Tables')

  

  次に，テーブル productsを選び、ダブルクリックする (Select table 'products', and Double-click it)

  

  テーブル productsが表示される (table 'products' appears)

  

  ※ もし，データに間違いがあれば，このウインドウで修正できる (If you find any mistakes, you can modify the data using this window).

• 今度は，orders テーブル を表示
  

SQL 問い合わせの発行と評価結果の確認 (Issue SQL queries and inspect the results)

直積 (Cartesian product)

SQL を使い，複数のテーブルの直積を簡単に得ることができる．

SELECT *
FROM orders, products;

結合問い合わせ (join query)

結合問い合わせは，直積から，条件を満足する行を選んだものになる．

List all 'name', 'price' and 'orders.qty' that satisfy "orders.month = 10"

SELECT products.product_name, products.price, orders.qty
FROM products, orders
WHERE products.id = orders.product_id
           AND orders.month = 10;

SQL を用いたテーブル定義と一貫性制約の記述 (Table defintion and integrity constraint specification using SQL)

実体関連図 (Entity Relationship Diagram)

SQL を用いて，bundles テーブル，shippings テーブルを定義し，一貫性制約を記述する． (Define two table 'bundles' and 'shippings'. Specify integrity constrants of the table using SQL)

1. bundles テーブルの定義

   次の SQL を入力し，「Run SQL」のアイコンをクリック (Write the following SQL, and click "Run SQL" icon).

   create table bundles (
       id          integer  primary key autoincrement not null,
       order_id  integer  not null REFERENCES orders(id),
       qty         integer  not null,
       shipping_id integer  not null REFERENCES shippings(id),
       created_at datetime not null );
   

   
2. コンソールの確認 (Inspect console)
   

   エラーメッセージが出ていないことを確認

3. shippings テーブルの定義

   次の SQL を入力し，「Run SQL」のアイコンをクリック (Write the following SQL, and click "Run SQL" icon).

   create table shippings (
       id    integer  primary key autoincrement not null,
       year  integer  not null check ( year > 2008 ),
       month integer  not null check ( month >= 1 AND month <= 12 ),
       day   integer  not null check ( day >= 1 AND day <= 31 ),
       created_at datetime not null );
   

   
4. コンソールの確認 (Inspect console)
   

   エラーメッセージが出ていないことを確認

5. テーブル一覧の表示 (List of tables)

   オブジェクト・ブラウザ (Object Browser) の中の「Tables」を展開 (Click 'Tables')． テーブル一覧が表示される．

   

SQL を用いたテーブルへの行の挿入 (Insert rows into a table using SQL)

テーブル間の関係

■ 以下の手順で，SQL を用いて bundles テーブルへの行の挿入を行う (Insert rows into table 'bundles' using SQL)

1. SQL プログラムの記述

   begin transaction;
   insert into bundles values( 1, 1001, 2, 101, datetime('now', 'localtime') );
   insert into bundles values( 2, 1001, 1, 102, datetime('now', 'localtime') );
   insert into bundles values( 3, 1002, 1, 103, datetime('now', 'localtime') );
   insert into bundles values( 4, 1003, 2, 104, datetime('now', 'localtime') );
   insert into bundles values( 5, 1004, 4, 104, datetime('now', 'localtime') );
   commit;
   

   
2. 複数の SQL 文の一括実行 (Run multiple SQL statements)

   複数の SQL 文を一括実行したいので，カーソルを先頭行に移動した後に，「Run multiple SQL statements ...」のボタンをクリックする．
   (Move the cursor to the top statement. Click "Run multiple SQL statements from current cursor position in one batch" icon)

   
3. 「Script Output」ウインドウの確認 (Inspect "Script Output" window)

   エラーメッセージが出ていないことを確認

   

■ 以下の手順で，SQL を用いて shippings テーブルへの行の挿入を行う (Insert rows into table 'shippings' using SQL)

1. SQL プログラムの記述

   begin transaction;
   insert into shippings values( 101, 2019, 10, 28, datetime('now', 'localtime') );
   insert into shippings values( 102, 2019, 10, 31, datetime('now', 'localtime') );
   insert into shippings values( 103, 2009, 11,  1, datetime('now', 'localtime') );
   insert into shippings values( 104, 2009, 11,  2, datetime('now', 'localtime') );
   commit;
   

   
2. 複数の SQL 文の一括実行 (Run multiple SQL statements)

   複数の SQL 文を一括実行したいので，カーソルを先頭行に移動した後に，「Run multiple SQL statements ...」のボタンをクリックする．
   (Move the cursor to the top statement. Click "Run multiple SQL statements from current cursor position in one batch" icon)

   
3. 「Script Output」ウインドウの確認 (Inspect "Script Output" window)

   エラーメッセージが出ていないことを確認

   

SQLiteman を用いたデータのブラウズ (Browse Data using SQLiteman)

• bundles テーブル

  テーブル bundlesを選ぶ (Select table 'products')

  

  テーブル bundlesが表示される (table 'products' appears)

  

  ※ もし，データに間違いがあれば，このウインドウで修正できる (If you find any mistakes, you can modify the data using this window).

• shippings テーブル
  

SQL 問い合わせの発行と評価結果の確認 (Issue SQL queries and inspect the results)

直積集合 (Cartesian product)

SQL を使い，複数のテーブルからの直積を簡単に得ることができる．

SELECT *
FROM shippings, bundles, orders;

結合問い合わせ (join query)

結合問い合わせは，直積から，条件を満足する行を選んだものになる．

List all 'shippings.month', 'shiping.day' and 'bundles.qty' that satisfy "orders.day = 27"

SELECT shippings.month, shippings.day, bundles.qty
FROM shippings, bundles, orders
WHERE orders.id = bundles.order_id
           AND shippings.id = bundles.shipping_id
           AND orders.day = 27;

演習問題と解答例

次の問いに答えよ．その後，下記の解答例を確認せよ． Answer the following questions. Then, inspect answers described below.

問い (Questions)

1. 次の SQL 問い合わせの評価結果は何か？ (What is the evaluation result of the following SQL query).

   SELECT products.product_name, orders.qty
   FROM products, orders
   WHERE products.id = orders.product_id
              AND orders.qty > 2;
   

   解答例

   Fukuoka apple     |3
   Kumamoto orange M |4
   

   
   
   
2. 次の SQL 問い合わせの評価結果は何か？ (What is the evaluation result of the following SQL query).

   SELECT shippings.year, shippings.month, shippings.day
   FROM shippings, bundles, orders, products
   WHERE products.id = orders.product_id
              AND orders.id = bundles.order_id
              AND shippings.id = bundles.shipping_id
              AND price > 20;
   

   解答例

   2009|10|28
   2009|10|31
   2009|11|1
   2009|11|2
   

   
3. 下記の問に解答せよ
   • まず SQLite を使い，下記のテーブルをした．

         loan(id, branchname, amount)
         borrow(id, customername, loanid)
     

     テーブル定義のSQLは次の通りである．

     create table loan (
         id            integer  primary key autoincrement not null,
         branchname    text not null, 
         amount        integer );             
     
     create table borrow (
         id            integer  primary key autoincrement not null,
         customername  text not null, 
         loanid        integer );             
     

   • SQLite を使い，下記の SQL を評価させた。(Evaluate the following SQL) ．

         begin transaction;
         insert into loan values( 1, 'fukuoka', 1000 );
         insert into loan values( 2, 'saga', 2000 );
         insert into loan values( 3, 'saga', 1500 );
         insert into loan values( 4, 'kumamoto', 3000 );
         insert into loan values( 5, 'fukuoka', 2500 );
         commit;
     
         begin transaction;
         insert into borrow values(1001, 'X', 1);
         insert into borrow values(1002, 'X', 2);
         insert into borrow values(1003, 'X', 3);
         insert into borrow values(1004, 'Y', 4);
         insert into borrow values(1005, 'X', 5);
         commit;
     

   • その後、次に SQLite を使い，下記の SQL を評価させたときの評価結果を解答しなさい (Evaluate the following SQL)

     1.     select * from loan;
        

        解答例 (Answers)

        1|fukuoka|1000
        2|saga|2000
        3|saga|1500
        4|kumamoto|3000
        5|fukuoka|2500
        

     2.     select branchname from loan;
        

        解答例 (Answers)

        fukuoka
        saga
        saga
        kumamoto
        fukuoka
        

     3.     select distinct branchname from loan;
        

        解答例 (Answers)

        fukuoka
        saga
        kumamoto
        

     4.     select id, branchname, amount * 1000 from loan;
        

        解答例 (Answers)

        1|fukuoka|1000000
        2|saga|2000000
        3|saga|1500000
        4|kumamoto|3000000
        5|fukuoka|2500000
        

     5.     select id from loan where branchname = 'fukuoka';
        

        解答例 (Answers)

        1
        5
        

     6.     select id from loan where amount < 2000 and amount >= 1000;
        

        解答例 (Answers)

        1
        3
        

     7. 解答例 (Answers)

            select *
            from loan, borrow;
        

        解答例 (Answers)

        1|fukuoka|1000|1001|X|1
        1|fukuoka|1000|1002|X|2
        1|fukuoka|1000|1003|X|3
        1|fukuoka|1000|1004|Y|4
        1|fukuoka|1000|1005|X|5
        2|saga|2000|1001|X|1
        2|saga|2000|1002|X|2
        2|saga|2000|1003|X|3
        2|saga|2000|1004|Y|4
        2|saga|2000|1005|X|5
        3|saga|1500|1001|X|1
        3|saga|1500|1002|X|2
        3|saga|1500|1003|X|3
        3|saga|1500|1004|Y|4
        3|saga|1500|1005|X|5
        4|kumamoto|3000|1001|X|1
        4|kumamoto|3000|1002|X|2
        4|kumamoto|3000|1003|X|3
        4|kumamoto|3000|1004|Y|4
        4|kumamoto|3000|1005|X|5
        5|fukuoka|2500|1001|X|1
        5|fukuoka|2500|1002|X|2
        5|fukuoka|2500|1003|X|3
        5|fukuoka|2500|1004|Y|4
        5|fukuoka|2500|1005|X|5
        

     8.     select customername, amount
            from loan, borrow;
        

        解答例 (Answers)

        X|1000
        X|1000
        X|1000
        Y|1000
        X|1000
        X|2000
        X|2000
        X|2000
        Y|2000
        X|2000
        X|1500
        X|1500
        X|1500
        Y|1500
        X|1500
        X|3000
        X|3000
        X|3000
        Y|3000
        X|3000
        X|2500
        X|2500
        X|2500
        Y|2500
        X|2500
        

     9.     select customername, amount
            from loan, borrow
            where loan.id = borrow.loanid;
        

        解答例 (Answers)

        X|1000
        X|2000
        X|1500
        Y|3000
        X|2500
        

     10.     select customername, amount
             from loan, borrow
             where loan.id = borrow.loanid AND borrow.customername = 'X';
         

         解答例 (Answers)

         X|1000
         X|2000
         X|1500
         X|2500