ホーム » 「SQLite」タグがついた投稿

タグアーカイブ: SQLite

2022年1月
 1
2345678
9101112131415
16171819202122
23242526272829
3031  

最新の投稿(電子情報)

アーカイブ

カテゴリー

SQLの基本

先週の、関係データベースの導入説明を終えて、実際のSQLの説明。

SQLの命令

SQL で使われる命令は、以下のものに分類される。((参考資料))

  • データ定義言語 – CREATE, DROP, ALTER 等
  • データ操作言語 – INSERT, UPDATE, DELETE, SELECT 等
  • データ制御言語 – GRANT, REVOKE 等 (その他トランザクション制御命令など)

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

実際にテーブルを宣言する命令。構造体の宣言みたいなものと捉えると分かりやすい。

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の演習環境の使い方

下記URLにアクセスすると、認証画面が表示されるので、情報処理センターのユーザIDとパスワードで、Login する。

以下のような画面が表示されるので、最初に”データベースリセット”を押すこと。

以降、登録済みの処理を実行する場合は、左上のプルダウンメニューから、処理を選んで”バッチ処理実行”を行う。
画面下に、実行された結果が表示される。

教科書内の基本演習のデータを利用したい場合は、”0_Create_DB” , “1_Insert_Data”を実行する。

自分で処理を実行したい場合には、中段のSQLコマンドの欄に、命令を記載する。”create table”や”insert”といった結果を伴わない命令の場合は、”EXEC”を実行。”select” などの実行結果がある場合は、”QUERY”を実行すること。


SQLの基礎

ここまで述べたようにデータベースでは記録されているデータの読み書きは、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 では、このようなイメージの繰り返し処理を、数行で分かりやすく記述できている。このプログラム例では、キーに対応するものを単純 for ループで説明しているが、SQL ではプライマリキーなら、B木やハッシュなどを用いた効率の良い検索が自動的に行われる。このため、SQLを扱う応用プログラマは、SQLの結合の処理方法は通常はあまり考えなくて良い。

創造工学演習・予備実験・PHPとDB

インターネットを活用したプログラムを作成する場合、データを保存管理するためのデータベースと、データベースのデータを処理するためのプログラム言語が必要となってくる。今回の予備実験では、そのためにリレーショナルデータベースと、Webの動的なプログラム言語である PHP について説明する。

リレーショナル・データベース

データベースは、データを保存し、矛盾が発生しない様に管理してくれるシステムであり、インターネットで活用されている。

データを確実に保存し、矛盾なく扱うためには、本来複雑なプログラムが必要となる。この中で、データを表形式のテーブルを組み合わせて管理するシステムはリレーショナルデータベースと呼ばれる。リレーショナルデータベースでは、データの問い合わせなどの処理が簡単にできるように、SQL と呼ばれる言語を使って処理を行う。

大量のデータをインターネットの中で利用するためには、ネットワークを経由してデータの問い合わせが求められ、有名なデータベースシステムには、Oracle, MySQL などがある。今回の実験では、ネットワーク機能は持たないが簡単な手続きで使うことができる SQLite を使って説明する。

また、今回の予備実験では時間も限られることから、複数の表を組み合わせた SQL の処理については割愛する。

SQLの基本

リレーショナルデータベースでは、データの基本は表形式データであり、1つの表に相当するデータはテーブルと呼ぶ。

以下の様な名前・住所・年齢のデータがあったとすると、1人前のデータをレコードと呼び、name, addr, age といった属性はカラムと呼ぶ。

name addr age
t-saitoh 越前市 55 ←レコード
sakamoto 福井市 50
murata 福井市 35
↑カラム

データの型には、文字列型(char型,varchar型,text型)や、数値型(integer型,decimal型,real型)などがあり、create table 文にてカラムの型を定義する。

create table テーブルを作る

データベースの表を使う最初には、create table 文を実行する。C言語での struct 文をイメージすると解り易いかもしれないが、データはデータベースの中に永続的に保存されるので、システムを動かす最初に一度実行するだけで良い。

上記のような名前・住所・年齢のデータ構造であれば、次の様な create table 文を使う。

create table テーブル名 (
    カラム名1  型1 ,
    カラム名2  型2 
    ) ;
-- 例 --
create table PERSON (        -- テーブル名:PERSON
    name  varchar( 20 ) ,    -- 名前
    addr  varchar( 20 ) ,    -- 住所
    age   integer ,          -- 年齢
    primary key( name )      -- name はデータ検索のキーであり重複は許されない
    ) ;

これと同じ様な処理をC言語で書くのであれば、以下の様な構造体宣言と同じであろう。

struct PERSON {
    char name[ 20 ] ;    // 名前
    char addr[ 20 ] ;    // 住所
    int  age ;           // 年齢
} ;

drop table テーブルを消す

データベースは永続的に保存されるので、テーブル全体のデータが不要であれば、drop table 命令で、テーブル全体を消す。

drop table テーブル名 ;
-- 例 --
drop table PERSON ;

insert into レコードを追加

データベースに1レコードを保存するには、insert文を用いる。

insert into テーブル名 ( カラム名... ) values( 値... ) ;
-- 例 --
insert into PERSON ( name ,       addr ,    age )
            values ( 't-saitoh' , '越前市' , 55  ) ;
insert into PERSON ( name ,       addr ,    age )
            values ( 'sakamoto' , '福井市' , 50  ) ;
insert into PERSON ( name ,       addr ,    age )
            values ( 'murata' ,   '福井市' , 35  ) ;

delete レコードを消す

データベースのレコードを消すには、delete 文を用いる。条件を満たす複数のデータをまとめて消すことができる。

delete from テーブル名 where 条件 ;
-- 例 --
-- 40歳未満のデータを全て消す。 murata,福井市,35 が消える。
delete from PERSON
       where age < 40 ;

update レコードを更新

データベースのレコードを修正するには、update 文を用いる。条件を満たす複数のデータをまとめて修正することもできる。

update テーブル名 set カラム = 値 where 条件 ;
-- 例 --
-- 住所が越前市のレコードの年齢を 0 にする。
update PERSON set age = 0
       where addr == '越前市' ;

select データを探す

データベースの内容を参照するための命令が select 文。where を記載することで、特定の条件のデータだけを選択したり、特定のカラムだけを抽出することができる。

select カラム名 from テーブル名 where 条件 ;
-- 例 --
-- PERSON の全データを出力
select * from PERSON ;

-- PERSON の住所が福井市だけを選別し、名前と住所を抽出
select name,addr from PERSON
       where addr = '福井市' ;

-- PERSON の年齢の最高値を出力 (集約関数)
select max(age) from PERSON
       where addr = '福井市' ;

-- PERSON の年齢条件を満たす人数を数える (集約関数)
select count(name) from PERSON
       where age >= 50 ;

動的なプログラム言語とPHP

本来、Webサーバが作られた頃は、論文や研究用のデータを公開する物であったが、扱うデータが増えるにつれ、特定の論文や研究データの一覧を表示したり探したりという処理が求められた。こういった処理のためにWebページのアクセスを受けた時に処理を実行する CGI という機能があったが、これを発展させてできたプログラム言語が PHP である。

PHPでは、ページを表示するための HTML の中に <?php?> のといった開始タグ・終了タグの中に、ブラウザから送られてきたデータに合わせて、処理を行うPHPの命令を記述し、データを(一般的にはHTML形式で)表示することができる。基本文法は C 言語に似ているが、様々なデータを扱うために変数にはどのような型でも保存できるようになっている。

ブラウザからデータを送るためのform文

ブラウザで入力欄を作ったり選択肢を表示し、その結果を送るための HTML は、入力フォーム(form)と呼ぶ。

<form method="get" action="処理ページ" >

  <input type="text" name="変数名" />

  <input type="radio" name="変数名" value="値" />
  <input type="checkbox" name="変数名" value="値" />

  <textarea cols="横文字数" rows="行数"></textarea>

  <select name="変数名">
    <option value="値1">表示1</option>
    <option value="値2">表示2</option>
  </select>
  <input type="submit" value="実行ボタンに表示する内容" />
</form>

formでは、入力する項目に変数名の名前を付け、action=”” で示したページにデータを送る。

PHPのプログラムの基本

PHPのプログラムは、外見は一般的に HTML ファイルであり、途中で <?php のタグからは、?> までの範囲が、PHP で処理が行われる。PHP のプログラムで print が実行されると、その場所に print 内容が書かれているような HTML ファイルが生成され、ブラウザで表示される。

PHP の中で変数は、$ で始まり、型宣言は基本的に不要である。

文字データを連結する場合は、”.” 演算子を使う。ダブルクオテーション”…”で囲まれた文字列の中の $名前 の部分は、変数名として扱われ、変数名の内容に置き換えられる。

HTMLのform文の action 属性で示された php であれば、PHPの中で送られてきた値を $_GET[‘変数名’] (method=”get”の場合)、 $_POST[‘変数名’] (method=”post”の場合)、または $_REQUEST[‘変数名’] (method=”get” or “post”) で参照できる。

((( sample.php )))
<html>
   <head>
      <title>sample.php</title>
      <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
   </head>
   <body>
      <form action="sample.php" method="POST">
         <input name="A" type="text" />     <!-- 変数 $A -->
         +
         <input name="B" type="text" />     <!-- 変数 $B -->
         =
         <?php 
            ini_set( 'error_reporting' , E_WARNING ) ;
            if ( $_REQUEST[ "A" ] != "" && $_REQUEST[ "B" ] != "" ) {
               print $_REQUEST[ "A" ] + $_REQUEST[ "B" ] ;
            } else {
               print "<INPUT TYPE=submit>" ;
            }
         ?>
      </form>
   </body>
</html>

PHPでデータベースを扱う

SQLのデータベースを、プログラム言語の中で扱う場合は、その記述も色々である。PHPでは以下の様にSQLを扱う。

((( survey-init.php )))
<html>
   <head>
      <title>survey_init.php</title>
      <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
   </head>
   <body>
      <?php
         // デバッグ用にエラー警告を表示する
         ini_set( 'error_reporting' , E_WARNING ) ;
         // データベースに接続する
         $data_dir = "../public_data" ;
         $dbh = new PDO( "sqlite:$data_dir/sqlite.db" ) ;
         // データベースを初期化する
         $init_sql = "drop table if exists Survey ;"
                   . "create table Survey ("
                   . "  uid  varchar( 20 ) ,"
                   . "  item varchar( 10 )"
                   . ") ;"
                   . "insert into Survey ( uid , item ) values ( 't-saitoh' , '猫'        ) ;"
                   . "insert into Survey ( uid , item ) values ( 'tomoko' ,   'ケーキ'     ) ;"
                   . "insert into Survey ( uid , item ) values ( 'mitsuki' ,  'ボードゲーム' ) ;"
                   ;
         if ( $dbh->exec( $init_sql ) < 0 ) {
            print "Error: $init_sql" ;
         }
         // データベースの表形式を読み出し、表形式で出力する。
         print "<table border='1'>\n" ;
         print "<tr><td align='center'>uid</td><td align='center'>item</td></tr>\n" ;
         $select_sql = "select uid,item from Survey ;" ;
         foreach( $dbh->query( $select_sql ) as list( $uid , $item ) ) {
            print "<tr><td>$uid</td><td>$item</td></tr>\n" ;
         }
         print "<table>\n" ;

         // データベースの単一データを取り出す
         $count_sql = "select count(item) from Survey where item = 'ケーキ' ;" ;
         print $dbh->query( $count_sql )->fetchColumn() ;
      ?>
   </body>
</html>

PHPの主要なSQL関数(PDO)

$dbh = new PDO(…) ; データベースに接続するハンドラを取得。
$dbh->exec( “create…” ) ; データベースでSQLを実行。
$dbh->query( “select…” ) ; データベースに問い合わせ。「1レコードに対応した配列」が全データだけ繰り返す、2次元配列を返す。
$dbh->query( “…” )->fetchColumn() 結果が1つだけの問い合わせ。集約関数の結果を参照する場合に用いる。

練習問題(1)

  • 上記の survey-init.php の select 文の部分を編集し、色々なデータ検索を試してみよ。

入力フォームのデータをデータベースに書き込む

((( survey-vote.php )))
<?php
    // エラー警告を表示                                                                                                     
    ini_set( 'error_reporting' , E_WARNING ) ;

    // form から送られてきた変数を保存                                                                                      
    $uid  = $_REQUEST[ "uid" ] ;
    $item = $_REQUEST[ "item" ] ;

    // データベースに接続する                                                                                               
    $data_dir = "../public_data" ;
    $dbh = new PDO( "sqlite:$data_dir/sqlite.db" ) ;

    // データベースに項目を追加する                                                                                         
    if ( $uid != "" && $item != "" ) {
        $insert_sql = sprintf( "insert into Survey( uid , item ) values ( %s , %s ) ;" ,
                               $dbh->quote( $uid ) , $dbh->quote( $item ) ) ;
        $dbh->exec( $insert_sql ) ;

        // reload処理で追記しないためページを強制的に再表示させる                                                           
        header( "Location: survey-vote.php" ) ;
    }
?>
<html>
  <head>
    <title>survey_vote.php</title>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  </head>
  <body>
    <form method="get" action="survey-vote.php">
       名前:   <input type="text" name="uid" />
       好きな物:<input type="text" name="item" />
       <input type="submit" value="投票" />
    </form>
    <?php
      // データベースの表形式を読み出し、表形式で出力する。                                                                   
      print "<table border='1'>\n" ;
      print "  <tr><td align='center'>uid</td><td align='center'>item</td></tr>\n" ;

      $select_sql = "select uid,item from Survey ;" ;
      foreach( $dbh->query( $select_sql ) as list( $t_uid , $t_item ) ) {
          print "  <tr><td>$t_uid</td><td>$t_item</td></tr>\n" ;
      }
      print "</table>\n" ;
    ?>
  </body>
</html>

練習問題(2)

  • 上記の survey-vote.php のプログラムを編集し色々な入力方法・出力方法を試してみよ。
    • 例えば、入力の item 選択に select や ラジオボタン フォームを使う。
    • 例えば、出力結果で、item の投票結果を、棒グラフで出力する。