インターネット技術特論
H:SQLite3
山口 実靖
http://www.ns.kogakuin.ac.jp/~ct13140/inet/
SQLite
• オープンソース(フリー)RDBMS実装の1個
• http://www.sqlite.org/
– SQLite 2.x と SQLite 3.x が有名.
• 特徴
– RDBMSサーバプロセスの起動が不要.
– 「1データベース,1ファイル」で格納..
• つまり「お手軽」なRDBMS.
– SQLiteの業務での使用はまれ.使い方やSQL文法は他の
実装と類似.勉強には(ほとんど)問題ない.
– 問題点:型付けが弱い.ユーザが無く,GRANTなどがない.
インターネット技術特論H-2
起動 と 終了
• www.ns.kogakuin.ac.jp では以下にある
/usr/bin/sqlite3
• 起動方法 (データベースと接続)
sqlite3 DBファイル
指定ファイルにデータ保存.無ければ新規作成.
• 終了方法
(データベースと切断)
sqlite> .exit
インターネット技術特論H-4
起動 と 終了
>sqlite3 /home/ct13140/db/sane.db
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
sqlite> .help
.backup ?DB? FILE
接続
接続中
Backup DB (default "main") to FILE
(略)
width:
sqlite> .exit
>
切断
sqlite>
というプロンプトが
表示されている間は,
SQLite3と接続中.
インターネット技術特論H-5
操作
• 赤字がユーザの入力である.
• 以下のような表を作るとする.
id
name
email
0
fukuda
[email protected]
1
abe
[email protected]
↑
Integer型
↑
text型
↑
text型
インターネット技術特論H-6
復習 (SQL文) 0/6
• データベースの作成
– データベースにつないでいない状態で
sqlite3 データベースファイル名
– ファイルが存在したら「既存DBに接続」,存在
しなかったら「新規DB作成」
– これはSQLite特有のコマンド.
• データベースの削除
– データベースにつないでいない状態で
rm データベースファイル名
– これはUnix一般のファイル削除コマンド.
インターネット技術特論H-7
復習 (SQL文) 1/6
• データベースに接続する.
– データベースにつないでいない状態で
sqlite3 データベースファイル名
– これはSQLite特有のコマンド.
• データベースとの接続を切断する.
– データベースにつないでいる状態で
.exit
– これは,SQLite特有のコマンド.
インターネット技術特論H-8
復習 (SQL文) 2/6
• 存在する表の一覧を表示
– データベースにつないでいる状態で
.tables
– これは,SQLite特有のコマンド.
インターネット技術特論H-9
復習 (SQL文) 3/6
• 表の作成
– データベースにつないでいる状態で
CREATE TABLE...;
– これはRDBMS一般のコマンド.
• 表の削除
– データベースにつないでいる状態で
DROP TABLE テーブル名;
– 表内のデータが全て消えてしまう.
– これはRDBMS一般のコマンド.
インターネット技術特論H-10
復習 (SQL文) 4/6
• 表内のデータを得る
– データベースにつないでいる状態で
SELECT 列名 FROM 表名...;
– これはRDBMS一般のコマンド.
• 表にデータを挿入する.
– データベースにつないでいる状態で
INSERT INTO 表名 VALUES (...);
– これはRDBMS一般のコマンド.
インターネット技術特論H-11
復習 (SQL文) 5/6
• 表内のデータを変更する
– データベースにつないでいる状態で
UPDATE 表名 SET 列名=値 WHERE 条件;
– これはRDBMS一般のコマンド.
インターネット技術特論H-12
復習 (型)
• Integer型 : 整数型
• Real型 : 浮動小数点型
• TEXT型 : 文字列型
– SQLite に, CHAR型, VARCHAR型はない.
• BLOB型 : Binary Large OBject
– 値をそのままバイナリで格納.
インターネット技術特論H-13
操作 0/21
• PuTTYでwww.ns.kogakuin.ac.jp
にloginする
– 「Host Name (or IP address)」に
「www.ns.kogakuin.ac.jp」と,
「Port」に「22」と入力し「Open」をクリック.
– 「Security Alert」が表示されたら「Yes」を押す.
– 「login as」に対してユーザ名を,「password」に対して
パスワードを入力.
• 注意:ここで使用するユーザ名とパスワードは,工学院大学計算機
(Windows)にlogonする時に使用するものです.
インターネット技術特論H-14
操作 1/21
• 自分用データベースの作成&接続.
[email protected][100]:sqlite3 ~/db/sane.db
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
このように表されれば
データベースに接続中.
インターネット技術特論H-15
操作 2/21
• データベースとの接続を切断する.
sqlite> .exit
[email protected][101]:
表示が sqlite> でなくなれば,切断成功.
上の例では,表示が [email protected][101]: に代わってい
る.
インターネット技術特論H-16
操作 3/21
• 再度自分用データベースに接続する.
[email protected][101]:sqlite3 ~/db/sane.db
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
このように表されれば
データベースに接続中.
インターネット技術特論H-17
操作 4/21
• 存在する表を調べる(データベース接続中).
sqlite> .tables
sqlite>
何も表示されずに
プロンプト(sqlite)
が表示されたら,
「表はない」ということ.
インターネット技術特論H-18
操作 5/21
• 表usertblを作る(データベース接続中).
sqlite>
...>
...>
...>
...>
sqlite>
CREATE TABLE usertbl (
id integer,
name txt,
email txt
);
使用したSQL文は
CREATE TABLE usertbl (id integer, name text,
email text);
途中の改行はあってもなくてもよい.
最後の;(セミコロン)を忘れない様に.
インターネット技術特論H-19
操作 6/21
• 存在する表を調べる(データベース接続中).
sqlite> .tables
usertbl
sqlite>
「操作5」で作成し
た表が表示されれ
れば成功.
インターネット技術特論H-20
操作 7/21
• 表にSELECTを実行する(データベース接続中).
– 現在,表は空のはずである.
sqlite> SELECT * FROM usertbl;
sqlite>
何も成功されなければ,
成功
インターネット技術特論H-21
操作 8/21
• 表にデータを入力する(データベース接続中).
sqlite> INSERT INTO usertbl VALUES (0,
'fukuda', [email protected]');
sqlite> SELECT * FROM usertbl;
[email protected]
SELECTを実行して,
表の中身を確認し
てみる.
これがデータ
の挿入命令.
表示は特に
変化なし.
途中改行は
あってもなく
てもよい.
インターネット技術特論H-22
操作 9/21
• 表にデータを入力する(データベース接続中).
sqlite> INSERT INTO usertbl VALUES (1, 'abe',
[email protected]');
sqlite> INSERT INTO usertbl VALUES (2,
'koizumi', [email protected]');
sqlite> SELECT * FROM usertbl;
[email protected]
[email protected]
[email protected]
sqlite>
インターネット技術特論H-23
操作 10/21
• 表diarytblを作る(データベース接続中).
sqlite> CREATE TABLE diarytbl (diaryid
Integer, userid Integer, year Integer, month
Integer, day Integer, sentence txt);
sqlite> .tables
diarytbl usertbl
sqlite>
日記記事1個ごとに1行の表とする.
diaryid:日記記事の通し番号
Userid:日記を書いたユーザのID
year,month,day:日記の日付
sentence:日記本文
インターネット技術特論H-24
操作 11/21
• 表diarytblにデータを挿入する(データベース
接続中).
sqlite> INSERT INTO diarytbl VALUES (0, 0, 2007,
1, 1, 'gantan deshita');
sqlite> INSERT INTO diarytbl VALUES (1, 0, 2007,
7, 29, 'senkyo deshita');
sqlite> INSERT INTO diarytbl VALUES (2, 2, 2005,
8, 8, 'kaisan shita');
sqlite> SELECT * FROM diarytbl;
0|0|2007|1|1|gantan deshita
1|0|2007|7|29|senkyo deshita
2|2|2005|8|8|kaisan shita
sqlite>
インターネット技術特論H-25
操作 12/21
• 現状の確認(データベース接続中).
sqlite> SELECT * FROM usertbl;
[email protected]
[email protected]
[email protected]
sqlite> SELECT * FROM diarytbl;
0|0|2007|1|1|gantan deshita
1|0|2007|7|29|senkyo deshita
2|2|2005|8|8|kaisan shita
sqlite>
インターネット技術特論H-26
操作 13/21
• 現状の確認(データベース接続中).
sqlite> SELECT * FROM diarytbl WHERE userid=0;
0|0|2007|1|1|gantan deshita
1|0|2007|7|29|senkyo deshita
sqlite>
インターネット技術特論H-27
操作 14/21
• 現状の確認(データベース接続中).
sqlite> UPDATE diarytbl SET sentence='ganjitu
deshita' WHERE diaryid=0;
sqlite> SELECT * FROM diarytbl;
0|0|2007|1|1|ganjitu deshita
1|0|2007|7|29|senkyo deshita
2|2|2005|8|8|kaisan shita
sqlite>
表内の列の順番は全く保証されていないので,
順番はRDBMSが勝手に決めてくる.
必ずしこの順になるとは限らない.
インターネット技術特論H-28
操作 15/21
• 内部結合(データベース接続中).
sqlite> SELECT * FROM usertbl CROSS JOIN diarytbl;
[email protected]|0|0|2007|1|1|ganjitu deshita
[email protected]|1|0|2007|7|29|senkyo deshita
[email protected]|2|2|2005|8|8|kaisan shita
[email protected]|0|0|2007|1|1|ganjitu deshita
[email protected]|1|0|2007|7|29|senkyo deshita
[email protected]|2|2|2005|8|8|kaisan shita
[email protected]|0|0|2007|1|1|ganjitu deshita
[email protected]|1|0|2007|7|29|senkyo deshita
[email protected]|2|2|2005|8|8|kaisan shita
sqlite>
userID = 2 の横に,
userID=0 の書込を置いても,
意味が無い.
インターネット技術特論H-29
操作 16/21
• 内部結合(データベース接続中).
sqlite> SELECT * FROM usertbl INNER JOIN diarytbl ON
usertbl.id = diarytbl.userid;
[email protected]|0|0|2007|1|1|ganjitu deshita
[email protected]|1|0|2007|7|29|senkyo deshita
[email protected]|2|2|2005|8|8|kaisan shita
sqlite>
インターネット技術特論H-30
操作 17/21
• 表内のデータの削除(データベース接続中).
sqlite> DELETE FROM diarytbl WHERE diaryid=0;
sqlite> SELECT * FROM diarytbl;
1|0|2007|7|29|senkyo deshita
2|2|2005|8|8|kaisan shita
sqlite>
インターネット技術特論H-31
操作 18/21
• 表の削除(データベース接続中).
sqlite> DROP TABLE diarytbl;
sqlite> .tables
usertbl
sqlite>
表を削除すると,
表内のデータは全て消失するので注意.
インターネット技術特論H-32
操作 19/21
• データベースとの接続を切断(データベース接続
中).
sqlite> .exit
[email protected][102]:
インターネット技術特論H-33
操作 20/21
• データベースの削除.
[email protected][102]:rm ~/db/a.db
[email protected][103]:
データベースを削除すると,
データベース内の全てのデータが失われるので注意!!
頻繁に行う作業ではありません.
インターネット技術特論H-34
注意事項
• RDBMSに接続中は,
sqlite>
と表示されるのが正常な状態です.
•
...>
などと表示されたのなら「コマンド入力途中」とい
う変な状態です.これを正常に戻すには
;[Ent] などを押すと,(入力文はエラーとな
るが)元に戻せる.
インターネット技術特論H-35
注意事項
• DBのデータを読むには,
– DBファイルへの読込権限が必要.
• DBにデータを書き込むには,
– DBのファイルへの書込権限と,
– DBのファイルのディレクトリへの書込権限が必要
• Webなどで使用するときは後者を要注意!
インターネット技術特論H-36
Ruby で SQLite3
sqlite0.rb
$LOAD_PATH.push('/home/ct13140/app/sqlite3-ruby1.3.1/lib/ruby/site_ruby/2.0.0/x86_64-linux/')
# ↑これは1行です.
require 'sqlite3'
db = SQLite3::Database.new("/home/ct13140/db/a.db")
# DBと接続する.ここでDBファイル名を指定.
res = db.execute('select * from usertbl;')
# この形↑でSQL文を文字列で送る.戻り値は2次元配列 つまり 2次元の表.
res.each do | row |
puts row.join(" , ")
end
赤字部は,(変更せずに)そのまま打ち込む.
青字部は,各自 適切な内容に変更する.
これは,工学院大学固有の話です
インターネット技術特論H-38
sqlite0.rb 実行方法
• /home/ct13140/app/ruby-2.0.0p247/bin/ruby I/home/ct13140/app/sqlite3-ruby1.3.1/lib/ruby/site_ruby/2.0.0/
sqlite0.rb
– これ↑は,1行です.
Ruby version 2.0.0 を使用してください。
インターネット技術特論H-39
sqlite1.rb
#!/home/ct13140/app/ruby-2.0.0-p247/bin/ruby I/home/ct13140/app/sqlite3-ruby1.3.1/lib/ruby/site_ruby/2.0.0/ -#↑これは1行です.これはコメントではありません.消してはいけません.
$LOAD_PATH.push('/home/ct13140/app/sqlite3-ruby1.3.1/lib/ruby/site_ruby/2.0.0/x86_64-linux/')
# ↑これは1行です.
require 'sqlite3'
db = SQLite3::Database.new("/home/ct13140/db/a.db")
# DBと接続する.ここでDBファイル名を指定.
db.execute('select * from usertbl;') do | row |
# この形↑でSQL文を文字列で送る.戻り値は2次元配列 つまり 2次元の表.
puts row.join(" , ")
end
これは,工学院大学固有の話です
インターネット技術特論H-40
sqlite1.rb 実行方法
• chmod 755 sqlite1.rb
– 実行権限を付与する.
• ./sqlite1.rb
– 1行目の内容を使ってこのプログラムは動く.
• つまり,これ↓
#!/home/ct13140/app/ruby-2.0.0-p247/bin/ruby -I/home/ct13140/app/sqlite3-ruby-1.3.1/lib/ruby/site_ruby/2.0.0/ --
インターネット技術特論H-41
Ruby + CGI + SQLite3
• データベースファイルを作成する
sqlite3 /home/ct13140/db/a.db
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .exit
• ファイルとディレクトリに書込権限を与える
chmod 606 /home/ct13140/db/a.db
chmod 707 /home/ct13140/db
– ディレクトリに与え忘れないように
– ディレクトリに書込権限を与えたら,他人がそのディレクトリに書き込めてしまう.
不必要な場所に書込権限を与えないように注意.
インターネット技術特論H-42
004.cgi
#!/home/ct13140/app/ruby-2.0.0-p247/bin/ruby I/home/ct13140/app/sqlite3-ruby1.3.1/lib/ruby/site_ruby/2.0.0/ -$LOAD_PATH.push('/home/ct13140/app/sqlite3-ruby1.3.1/lib/ruby/site_ruby/2.0.0/x86_64-linux/')
require 'sqlite3'
print "Content-type: text/html\n\n"
db = SQLite3::Database.new("/home/ct13140/db/a.db")
puts '<table border=5>'
db.execute('select * from person;') do | row |
puts '<tr><td>'
puts row.join("</td><td>")
puts '</td></tr>'
end
puts '</table>'
インターネット技術特論H-43
005.cgi
#!/home/ct13140/app/ruby-2.0.0-p247/bin/ruby I/home/ct13140/app/sqlite3-ruby1.3.1/lib/ruby/site_ruby/2.0.0/ -$LOAD_PATH.push('/home/ct13140/app/sqlite3-ruby1.3.1/lib/ruby/site_ruby/2.0.0/x86_64-linux/')
require 'sqlite3'
require 'cgi'
cgi = CGI.new
uid = cgi["id"]
name = cgi["name"]
email = cgi["email"]
print "Content-type: text/html\n\n"
print uid," , ",name," , ",email, "<br>\n"
インターネット技術特論H-44
005.cgi
begin
db = SQLite3::Database.new("/home/ct13140/db/a.db")
db.execute('insert into usertbl (id,name,email)
values ('+uid+',\''+name+'\',\''+email+'\')')
rescue => exp
print "ouch!<br>\n"
print exp.class, "<br>\n"
print exp.message, "<br>\n"
end
puts 'fin<br>'
puts '<a href="./">back</a>'
IDは本来は「ユーザ入力するもの」ではなく,「システムが自動で割り振るもの」だが,
ご愛敬...
インターネット技術特論H-45
フィールド制約:NOT NULL
• NOT NULL制約
– NULL値を入れられなくなる
sqlite> CREATE TABLE tbl0(id integer, name
text NOT NULL, email text);
sqlite> INSERT INTO tbl0 VALUES
(0,'sane',NULL);
sqlite> INSERT INTO tbl0 VALUES
(1,NULL,[email protected]');
Error: constraint failed
これは,
正常に実行される.
これは,
エラーとなる.
インターネット技術特論H-46
フィールド制約:UNIQUE
• UNIQUE制約
– 同じ値を複数行に入れられなくなる
sqlite> CREATE TABLE tbl1(id integer UNIQUE,
name text, email text);
sqlite> INSERT INTO tbl1 VALUES
(0,'sane',[email protected]');
sqlite> INSERT INTO tbl1 VALUES
(0,'yasu',[email protected]');
Error: constraint failed
これは,
正常に実行される.
これは,
エラーとなる.
インターネット技術特論H-47
フィールド制約:DEFAULT
• DEFAULT制約
– 入力略時には,DEFAULT値が採用される.
sqlite> CREATE TABLE tbl2(id integer, name
text, email text DEFAULT [email protected]');
sqlite> INSERT INTO tbl2 (id,name) VALUES
(0,'sane');
sqlite> select * from tbl2;
[email protected]
インターネット技術特論H-48
フィールド制約:PRIMARY KEY
• PRIMARY KEY制約
– 自動的に一意の値が割り振られる.
– ただし,削除してしまった行の値は再利用される.
sqlite> CREATE TABLE tbl3(id integer PRIMARY
KEY, name text, email text);
sqlite> INSERT INTO tbl3 VALUES (NULL,'sane',
[email protected]');
sqlite> INSERT INTO tbl3 (name,email) VALUES
('yasu',[email protected]');
sqlite> SELECT * FROM tbl3;
[email protected]
[email protected]
インターネット技術特論H-49
フィールド制約:ANTOINCREMENT
• 一意なIDの自動割り振り
sqlite> CREATE TABLE usertbl2 (id INTEGER PRIMARY KEY
AUTOINCREMENT, name TEXT, email TEXT);
sqlite> .tables
usertbl
usertbl2
sqlite> INSERT INTO usertbl2 (name, email) VALUES
('sane',[email protected]');
sqlite> INSERT INTO usertbl2 (name, email) VALUES
('yasu',[email protected]');
sqlite> INSERT INTO usertbl2 VALUES (NULL, 'hoge',
[email protected]');
sqlite> SELECT * FROM usertbl2;
[email protected]
PRIMARY KEY かつ AUTOINCREMENT
[email protected]
にしておくと,一度削除された値が
[email protected]
再利用されることがない.
sqlite>
インターネット技術特論H-50
フィールド制約:CHECK
• CHECK制約
– 値に制限をかけられる.
sqlite> CREATE TABLE tbl4(id integer
CHECK(id>0), name text, email text);
sqlite> INSERT INTO tbl4 VALUES (1,'sane',
[email protected]');
これは,
正常に実行される.
sqlite> INSERT INTO tbl4 VALUES (0,'yasu',
[email protected]');
これは,
エラーとなる.
Error: constraint failed
インターネット技術特論H-51
SQLiteコマンド
• (DBではなく) DBMSを制御するコマンド
.help :ヘルプメッセージを表示
.dump :データベースをダンプ
例:シェルにて sqlite3 a.db .dump | sqlite3 b.db
.quit :SQLiteを終了
.tables :表の一覧を表示
例:「.tables」,「.tables us%」
.databases :データベース一覧を表示
.schema :表のスキーマ(型など)を表示.
インターネット技術特論H-52
無料でRDBMSの勉強をするには
• 講義ツールを使用(本学サーバ+SQLite3)
– 年度末まで有効.SQLite3の型の問題などがある.
• 本学14階情報処理演習室 Microsoft Access.
– おすすめしない.AccessはSQLを使いづらい.
• 自分のWindows機に無料RDBMSをinstall
– 例:Windows版MySQL,Windows版PostgreSQL
• 自分のPCに無料OSと無料RDBMSをinstall
– 例:Linux+MySQL,Linux+PostgreSQL
インターネット技術特論H-53
ダウンロード

インターネット技術特論