SQLiteデータベースの作成

SQLite

SQLiteは， データベースシステムでも利用したリレーショナルデータベース管理システム（RDBMS）の一つです． 公式サイトから Precompiled Binaries for Windows（sqlite-tools-win32-x86-xxxx.zip）をダウンロードして展開してください． 複数のパッケージが存在しますが，コマンドライン・ツール（sqlite3.exe）を含むパッケージを選択するようにしてください．

SQLiteのダウンロード

インストールが完了したら，インストールされたフォルダに含まれる コマンドライン・ツール（sqlite3.exe）をダブルクリックして起動してください． 下図のようなコマンド入力が可能なターミナルが表示されます．

データベースの作成

本講義では，架空のオンライン・ショッピングサイト 椙天市場 のサービス構築を想定して，下記の3種類のテーブルを作成します． usersは，サイトのユーザー情報を格納するテーブルです． ユーザID，パスワード，氏名，電話番号，メールアドレスの情報を管理します． itemsは，サイトで取り扱う商品の情報を格納するテーブルです． 商品ID，商品名，価格，在庫数の情報を管理します． historiesは，ユーザによる商品の購買履歴を格納するテーブルです． 取引ID，取引日，ユーザID，商品IDの情報を管理します．

• Users（ユーザの情報）
• Items（商品の情報）
• Histories（商品の購買履歴）

データベースの作成

まずはデータベースを作成します． .openコマンドで sugiten.db という名前のデータベースを作成します． .databaseコマンドでメインのデータベースがsugiten.dbになっていることを確認します．

sqlite> .open sugiten.db
sqlite> .database
main: /Users/naoto/sugiten.db

Usersテーブルの作成

ユーザの情報を格納するUsersテーブルをCREATE文で作成します． 主キーは id であることに注意してください．

CREATE TABLE Users(
	id text primary key,
	password text,
	name text,
	tel text,
	mail text
);

テーブルを作成したら，.tableコマンドで確認しましょう．

sqlite> .table
Users

次にINSERT文でレコードを追加します． 今回はパスワードをそのまま平文で記録していますが， 実際のデータベースでは，ハッシュ関数を利用してパスワードは暗号化されます．

INSERT INTO Users VALUES("u01", "X2ng", "中谷俊介", "090-1111-1111", "nakatani@mail.jp");

レコードを追加したら，SELECT文で確認しましょう．

sqlite> .header on
sqlite> select * from Users;
id|password|name|tel|mail
u01|X2ng|中谷俊介|090-1111-1111|shunsuke@mail.jp

同様に下記の3件のレコードを追加しましょう．

Itemsテーブルの作成

商品の情報を格納するItemsテーブルをCREATE文で作成します． 主キーは id であることに注意してください．

CREATE TABLE Items(
	id text primary key,
	name text,
	price integer,
	stock integer
);

テーブルを作成したら，.tableコマンドで確認しましょう．

sqlite> .table
Items  Users

次にINSERT文でレコードを追加します．

INSERT INTO Items VALUES("i01", "マウス", 2000, 10);

レコードを追加したら，SELECT文で確認しましょう．

sqlite> select * from Items;
id|name|price|stock
i01|マウス|2000|10

同様に下記の5件のレコードを追加しましょう．

Historiesテーブルの作成

購買履歴を格納するHistoriesテーブルをCREATE文で作成します． 主キーは id であり，自動的に番号を増加させながら設定します． また，user_id はUsersテーブルの外部キー， item_id はItemテーブルの外部キーです．

CREATE TABLE Histories(
	id integer primary key AUTOINCREMENT,
	time text,
	user_id text,
	item_id text,
	foreign key(user_id) references Users(id),
	foreign key(item_id) references Items(id)
);

テーブルを作成したら，.tableコマンドで確認しましょう．

sqlite> .table
Histories  Items      Users

次にINSERT文でレコードを追加します．

INSERT INTO Histories(time, user_id, item_id) VALUES("2020-01-01 12:00:00", "u01", "i01");

レコードを追加したら，SELECT文で確認しましょう．

sqlite> select * from histories;
id|time|user_id|item_id
1|2020-01-01 12:00:00|u01|i01

同様に下記の3件のレコードを追加しましょう．

SQLiteの終了

.quitコマンドでSQLiteを終了してください．

sqlite> .quit

参考書籍