SQLiteデータベースの作成

Image from Gyazo

SQLite

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

SQLiteのダウンロード

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

Image from Gyazo

データベースの作成

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

データベースの作成

まずはデータベースを作成します. .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件のレコードを追加しましょう.

id password name tel mail
u01 X2ng 中谷俊介 090-1111-1111 shunsuke@mail.jp
u02 fL4m 永松克也 090-2222-2222 katsuya@mail.jp
u03 hY8W 神野愛美 090-3333-3333 aimi@mail.jp
u04 jY4p 大久真鈴 090-4444-4444 masuzu@mail.jp

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件のレコードを追加しましょう.

id name price stock
i01 マウス 2000 10
i02 パソコン 150000 5
i03 キーボード 3000 10
i04 モニタ 40000 3
i05 ウェブカメラ 6000 5
i06 ノートパソコン 170000 3

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件のレコードを追加しましょう.

id time user_id item_id
1 2020-01-01 12:00:00 u01 i01
2 2020-01-02 14:00:00 u02 i02
3 2020-01-02 18:00:00 u01 i04
4 2020-01-03 12:00:00 u04 i02

SQLiteの終了

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

sqlite> .quit

愛知県名古屋市にある椙山女学園大学 文化情報学部 向研究室の公式サイトです. 専門は情報科学であり,人工知能やデータベースなどの技術要素を指導しています. この公式サイトでは,授業で使用している教材を公開すると共に, ベールに包まれた女子大教員のミステリアスな日常を4コマ漫画でお伝えしていきます. サイトに関するご意見やご質問はFacebookまたはTwitterでお問い合わせください.