SQLite SQLite3 http://www.ns.kogakuin.ac.jp/~ct13140/prog/ オープンソース ( フリー )RDBMS 実装の 1 個 http://www.sqlite.org/ 現在,3.6 が最新版. SQLite 2.x と SQLite 3.x が有名. 特徴 RDBMS サーバプロセスの起動が不要. 1 データベース,1 ファイル で格納.. つまり お手軽 な RDBMS. SQLite の業務での使用はまれ. 使い方や SQL 文法は他の実装と類似. 勉強には ( ほとんど ) 問題ない. 問題点 : 型付けが弱い. ユーザが無く,GRANT などがない. 起動と終了 起動と終了 SQLite3-2 www.ns.kogakuin.ac.jp では以下にある /home/ct13140/bin/sqlite3 起動方法 ( データベースと接続 ) /home/ct13140/bin/sqlite3 DB ファイル指定ファイルにデータ保存. 無ければ新規作成. 終了方法 ( データベースと切断 ).exit 上記は, 山口が個人的にインストールしたものです. 年度末までは, 存在することを保証します. それ以降は, 予告無く削除することもありえます. >/home/ct13140/bin/sqlite3 ~/a.db SQLite version 3.6.20 Enter ".help" for instructions Enter SQL statements terminated with a ";".help.backup?db? FILE Backup DB (default "main") to FILE ( 略 ) width:.exit > というプロンプトが表示されている間は, SQLite3 と接続中. 接続 接続中 切断 操作 SQLite3-3 復習 (SQL 文 ) 0/6 SQLite3-4 赤字がユーザの入力である. 以下のような表を作るとする. id name email 0 fukuda fukuda@kantei.gr.jp 1 abe abe@kantei.gr.jp Integer 型 text 型 復習 (SQL 文 ) 1/6 データベースに接続する. データベースにつないでいない状態で sqlite3 データベースファイル名 これは SQLite 特有のコマンド. データベースとの接続を切断する..exit SQLite 特有のコマンド. text 型 SQLite3-5 データベースの作成 データベースにつないでいない状態で sqlite3 データベースファイル名 ファイルが存在したら 既存 DB に接続, 存在しなかったら 新規 DB 作成 これは SQLite 特有のコマンド. データベースの削除 データベースにつないでいない状態で rm データベースファイル名 これは Unix 一般のファイル削除コマンド. 復習 (SQL 文 ) 2/6 存在する表の一覧を表示.tables SQLite 特有のコマンド. SQLite3-6 SQLite3-7 SQLite3-8
復習 (SQL 文 ) 3/6 復習 (SQL 文 ) 4/6 表の作成 CREATE TABLE...; 表の削除 DROP TABLE テーブル名 ; 表内のデータが全て消えてしまう. 表内のデータを得る SELECT 列名 FROM 表名...; 表にデータを挿入する. INSERT INTO 表名 VALUES (...); 復習 (SQL 文 ) 5/6 SQLite3-9 復習 ( 型 ) SQLite3-10 表内のデータを変更する UPDATE 表名 SET 列名 = 値 WHERE 条件 ; Integer 型 : 整数型 Real 型 : 浮動小数点型 TEXT 型 : 文字列型 SQLite に, CHAR 型, VARCHAR 型はない. BLOB 型 : Binary Large OBject 値をそのままバイナリで格納. 操作 0/21 SQLite3-11 操作 1/21 SQLite3-12 PuTTY で green.ns.kogakuin.ac.jp に login する Host Name (or IP address) に green.ns.kogakuin.ac.jp と, Port に 22 と入力し Open をクリック. Security Alert が表示されたら Yes を押す. login as に対してユーザ名を, password に対してパスワードを入力. 注意 : ここで使用するユーザ名とパスワードは, 工学院大学計算機 (Windows) に logon する時に使用するものです. 自分用データベースの作成 & 接続. ct13140@green[100]:/home/ct13140/bin/sqlite3 ~/a.db SQLite version 3.6.20 Enter ".help" for instructions Enter SQL statements terminated with a ";" このように表されればデータベースに接続中. 操作 2/21 SQLite3-13 操作 3/21 SQLite3-14 データベースとの接続を切断する..exit ct13140@green[101]: 表示が でなくなれば, 切断成功. 上の例では, 表示が ct13140@green[101]: に代わっている. 再度自分用データベースに接続する. ct13140@green[101]:/home/ct13140/bin/sqlite3 ~/a.db SQLite version 3.6.20 Enter ".help" for instructions Enter SQL statements terminated with a ";" このように表されればデータベースに接続中. SQLite3-15 SQLite3-16
操作 4/21 操作 5/21 存在する表を調べる ( データベース接続中 )..tables 何も表示されずにプロンプト (sqlite) が表示されたら, 表はない ということ. 表 usertbl を作る ( データベース接続中 ). CREATE TABLE usertbl (...> id integer,...> name txt,...> email txt...> ); 操作 6/21 SQLite3-17 使用した SQL 文は CREATE TABLE usertbl (id integer, name text, email text); 途中の改行はあってもなくてもよい. 最後の ;( セミコロン ) を忘れない様に. 操作 7/21 SQLite3-18 存在する表を調べる ( データベース接続中 )..tables usertbl 表に SELECT を実行する ( データベース接続中 ). 現在, 表は空のはずである. SELECT * FROM usertbl; 操作 5 で作成した表が表示されれれば成功. 何も成功されなければ, 成功 操作 8/21 SQLite3-19 操作 9/21 SQLite3-20 表にデータを入力する ( データベース接続中 ). INSERT INTO usertbl VALUES (0, 'fukuda', 'fukuda@kantei.go.jp'); SELECT * FROM usertbl; 0 fukuda fukuda@kantei.go.jp SELECT を実行して, 表の中身を確認してみる. これがデータの挿入命令. 表示は特に変化なし. 途中改行はあってもなくてもよい. 表にデータを入力する ( データベース接続中 ). INSERT INTO usertbl VALUES (1, 'abe', 'abe@kantei.go.jp'); INSERT INTO usertbl VALUES (2, 'koizumi', 'koizumi@kantei.go.jp'); SELECT * FROM usertbl; 0 fukuda fukuda@kantei.go.jp 1 abe abe@kantei.go.jp 2 koizumi koizumi@kantei.go.jp 操作 10/21 SQLite3-21 操作 11/21 SQLite3-22 表 diarytbl を作る ( データベース接続中 ). CREATE TABLE diarytbl (diaryid Integer, userid Integer, year Integer, month Integer, day Integer, sentence txt);.tables diarytbl usertbl 日記記事 1 個ごとに 1 行の表とする. diaryid: 日記記事の通し番号 Userid: 日記を書いたユーザの ID year,month,day: 日記の日付 sentence: 日記本文 SQLite3-23 表 diarytbl にデータを挿入する ( データベース接続中 ). INSERT INTO diarytbl VALUES (0, 0, 2007, 1, 1, 'gantan deshita'); INSERT INTO diarytbl VALUES (1, 0, 2007, 7, 29, 'senkyo deshita'); INSERT INTO diarytbl VALUES (2, 2, 2005, 8, 8, 'kaisan shita'); SELECT * FROM diarytbl; 0 0 2007 1 1 gantan deshita SQLite3-24
操作 12/21 現状の確認 ( データベース接続中 ). SELECT * FROM usertbl; 0 fukuda fukuda@kantei.go.jp 1 abe abe@kantei.go.jp 2 koizumi koizumi@kantei.go.jp SELECT * FROM diarytbl; 0 0 2007 1 1 gantan deshita 操作 13/21 現状の確認 ( データベース接続中 ). SELECT * FROM diarytbl WHERE userid=0; 0 0 2007 1 1 gantan deshita 操作 14/21 SQLite3-25 操作 15/21 SQLite3-26 現状の確認 ( データベース接続中 ). UPDATE diarytbl SET sentence='ganjitu deshita' WHERE diaryid=0; SELECT * FROM diarytbl; 0 0 2007 1 1 ganjitu deshita 表内の列の順番は全く保証されていないので, 順番は RDBMS が勝手に決めてくる. 必ずしこの順になるとは限らない. SQLite3-27 操作 16/21 内部結合 ( データベース接続中 ). SELECT * FROM usertbl INNER JOIN diarytbl ON usertbl.id = diarytbl.userid; 0 fukuda fukuda@kantei.go.jp 0 0 2007 1 1 ganjitu deshita 0 fukuda fukuda@kantei.go.jp 2 koizumi koizumi@kantei.go.jp 内部結合 ( データベース接続中 ). SELECT * FROM usertbl CROSS JOIN diarytbl; 0 fukuda fukuda@kantei.go.jp 0 0 2007 1 1 ganjitu deshita 0 fukuda fukuda@kantei.go.jp 0 fukuda fukuda@kantei.go.jp 1 abe abe@kantei.go.jp 0 0 2007 1 1 ganjitu deshita 1 abe abe@kantei.go.jp 1 abe abe@kantei.go.jp 2 koizumi koizumi@kantei.go.jp 0 0 2007 1 1 ganjitu deshita 2 koizumi koizumi@kantei.go.jp 2 koizumi koizumi@kantei.go.jp userid = 2 の横に, userid=0 の書込を置いても, 意味が無い. 操作 17/21 SQLite3-28 表内のデータの削除 ( データベース接続中 ). DELETE FROM diarytbl WHERE diaryid=0; SELECT * FROM diarytbl; 操作 18/21 SQLite3-29 操作 19/21 SQLite3-30 表の削除 ( データベース接続中 ). DROP TABLE diarytbl;.tables usertbl データベースとの接続を切断 ( データベース接続中 )..exit ct13140@green[102]: 表を削除すると, 表内のデータは全て消失するので注意. SQLite3-31 SQLite3-32
操作 20/21 注意事項 データベースの削除. ct13140@green[102]:rm ~/a.db ct13140@green[103]: データベースを削除すると, データベース内の全てのデータが失われるので注意!! 頻繁に行う作業ではありません. 注意事項 SQLite3-33 RDBMS に接続中は, と表示されるのが正常な状態です....> などと表示されたのなら コマンド入力途中 という変な状態です. これを正常に戻すには ;[Ent] などを押すと,( 入力文はエラーとなるが ) 元に戻せる. フィールド制約 :NOT NULL SQLite3-34 DB のデータを読むには, DB ファイルへの読込権限が必要. DB にデータを書き込むには, DB のファイルへの書込権限と, DB のファイルのディレクトリへの書込権限が必要 Web などで使用するときは後者を要注意! NOT NULL 制約 NULL 値を入れられなくなる CREATE TABLE tbl0(id integer, name text NOT NULL, email text); INSERT INTO tbl0 VALUES (0,'sane',NULL); INSERT INTO tbl0 VALUES (1,NULL,'sane@sane.com'); Error: constraint failed 正常に実行される. エラーとなる. フィールド制約 :UNIQUE SQLite3-35 フィールド制約 :DEFAULT SQLite3-36 UNIQUE 制約 同じ値を複数行に入れられなくなる CREATE TABLE tbl1(id integer UNIQUE, name text, email text); DEFAULT 制約 入力略時には,DEFAULT 値が採用される. CREATE TABLE tbl2(id integer, name text, email text DEFAULT 'a@a.com'); INSERT INTO tbl1 VALUES (0,'sane','sane@sane.com'); INSERT INTO tbl1 VALUES (0,'yasu','yasu@sane.com'); Error: constraint failed 正常に実行される. エラーとなる. INSERT INTO tbl2 (id,name) VALUES (0,'sane'); select * from tbl2; 0 sane a@a.com フィールド制約 :PRIMARY KEY SQLite3-37 フィールド制約 :CHECK SQLite3-38 PRIMARY KEY 制約 自動的に一意の値が割り振られる. ただし, 削除してしまった行の値は再利用される. CREATE TABLE tbl3(id integer PRIMARY KEY, name text, email text); INSERT INTO tbl3 VALUES (NULL,'sane', 'sane@sane.com'); INSERT INTO tbl3 (name,email) VALUES ('yasu','yasu@sane.com'); SELECT * FROM tbl3; 1 sane sane@sane.com 2 yasu yasu@sane.com SQLite3-39 CHECK 制約 値に制限をかけられる. CREATE TABLE tbl4(id integer CHECK(id>0), name text, email text); INSERT INTO tbl4 VALUES (1,'sane', 'sane@sane.com'); 正常に実行される. INSERT INTO tbl4 VALUES (0,'yasu', 'yasu@sane.com'); Error: constraint failed エラーとなる. SQLite3-40
フィールド制約 :ANTOINCREMENT SQLite コマンド 一意なIDの自動割り振り CREATE TABLE usertbl2 (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, email TEXT);.tables usertbl usertbl2 INSERT INTO usertbl2 (name, email) VALUES ('sane','sane@sane.com'); INSERT INTO usertbl2 (name, email) VALUES ('yasu','yasu@sane.com'); INSERT INTO usertbl2 VALUES (NULL, 'hoge', 'hoge@sane.com'); SELECT * FROM usertbl2; 1 sane sane@sane.com 2 yasu yasu@sane.com 3 hoge hoge@sane.com PRIMARY KEY かつ AUTOINCREMENT にしておくと, 一度削除された値が再利用されることがない. 無料で RDBMS の勉強をするには SQLite3-41 (DB ではなく ) DBMS を制御するコマンド.help : ヘルプメッセージを表示.dump : データベースをダンプ例 : シェルにて sqlite3 a.db.dump sqlite3 b.db.quit :SQLite を終了.tables : 表の一覧を表示例 :.tables,.tables us%.databases : データベース一覧を表示.schema : 表のスキーマ ( 型など ) を表示. SQLite3-42 講義ツールを使用 ( 本学サーバ +SQLite3) 年度末まで有効.SQLite3 の型の問題などがある. 本学情報処理演習室 Microsoft Access. おすすめしない.Access は SQL を使いづらい. 自分の Windows 機に無料 RDBMS を install 例 :Windows 版 MySQL,Windows 版 PostgreSQL 自分の PC に無料 OS と無料 RDBMS を install 例 :Linux+MySQL,Linux+PostgreSQL SQLite3-43