【第8回】データベース入門(1)

データベースの基礎やSQL文について学びます

Homeブログ一覧【第8回】データベース入門(1)

データベースとは

データベースとは、データを保存するための仕組みです。データベースを使うことで、データの保存や検索、更新、削除などを効率よく行うことができます。

どのような場面で使われるか

データベースは、オンラインショッピングやSNS、医療など様々な場面で使われています。

  • オンラインショッピング

    • 商品情報や顧客情報を保存
    • 注文情報を保存
    • 検索から商品情報を取得

  • SNS (XやInstagramなど)

    • ユーザー情報を保存
    • 投稿情報を保存

  • 図書館

    • 蔵書情報を保存
    • 貸出情報を保存

  • ヘルスケア

    • 患者情報(名前、年齢、性別など)を保存
    • 診療情報を保存

などなど...

データベースには主にリレーショナルデータベース、NoSQLデータベース、NewSQLデータベースなど様々な種類があり、それぞれ異なる保存形式やパフォーマンス、機能を持っています。

NoSQL(Not Only SQL)データベースは、リレーショナルデータベース以外の総称を指します。

NewSQLデータベースは、新しくいいとこどりをしたデータベースです。

まずは、SQLiteというデータベースを使ってデータベースの基本的な操作を学びます。

リレーショナルデータベースとは

リレーショナルデータベースとは、データを表形式で保存するデータベースのことです。さらに、リレーショナルデータベースは、複数の表を結合してデータを取得することができます。

例えば、次のようなデータベースを見てみましょう。

  • ツイートの投稿内容を保存するデータベース(ツイートテーブル)
idcontentuser_idcreated_at
1今日はいい天気ですね22024-06-15 8:00:00
2おなかがすいたなー32024-06-15 11:30:00
3課題がおわりません32024-06-20 13:00:00
4今日のWeb研は何だろう12024-06-28 13:30:00
5ICPCがんばるぞ22024-06-29 14:00:00

このような表のことをテーブルとも呼びます。テーブルは、行(レコード) と 列(カラム) で構成されています。

それぞれの列にはカラム名とデータ型を持ちます。

例えば、上記のテーブルの場合、

  • idは整数型(Integer)
  • contentは文字列型(Text)
  • user_idは整数型(Integer)
  • created_atは日時型(Date)

となっています。

idはツイートを識別するためのIDであり、contentはツイートの内容、user_idはツイートをしたユーザーのID、created_atはツイートが投稿された日時を表しています。

  • ユーザー情報を保存するデータベース(ユーザーテーブル)
idusernameemail
1鈴木一郎[email protected]
2田中太郎[email protected]
3山田花子[email protected]

リレーショナルデータベースでは、複数のテーブルを作ることができ、これらのテーブルを結合してデータを取得することができます。

ツイートid=1のツイートを投稿したユーザーは誰でしょうか？

答え

ツイートid=1のuser_idは2です。ユーザーテーブルからid=2のユーザーは田中太郎ということがわかります。

SQLiteとは

SQLiteは、リレーショナルデータベースのひとつで、整理された表形式のデータを扱うことができます。

このデータベースは、MicrosoftのExcelのように表形式かつ単一のファイルとして保存することができます。そのため、データベースを扱うためのサーバーを用意する必要がなく、初心者にも扱いやすいデータベースです。

SQLiteをインストールしよう

ターミナルを開いて、以下のコマンドを実行してください。

sudo apt install sqlite3

SQL

データベースを操作するためには、SQL(Structured Query Language)という言語を使います。SQLは、データベースに対して様々な操作を行うための言語で、データの追加、更新、削除、検索などができます。

準備

まず、データベースを操作するための準備をします。

今回の作業ディレクトリ(db-intro)を作成し、以下のコマンドを実行してください。

# データベースファイルを作成
touch database.db

# sqlite3を用いてデータベースファイルを開く
sqlite3 database.db

テーブルの作成

先ほどのツイートテーブルを作ってみましょう。まずは、テーブルのカラム名、データ型を指定してテーブルを作成します。

CREATE TABLE tweets (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  content TEXT NOT NULL,
  user_id INTEGER NOT NULL,
  created_at TEXT NOT NULL
);

  • CREATE TABLE <テーブル名> (...)で、テーブルを作成することができます。

  • それぞれのカラム(列)は<カラム名> <データ型> <制約>で指定します。

    • PRIMARY KEYは、そのテーブルの主キーを指定します。主キーは、テーブル内の行を一意に識別するためのキーです。

    • AUTOINCREMENTは、主キーを自動的に増加させるためのキーワードです。

    • NOT NULLは、そのカラムには必ず値が入れる必要があることを示します。

    • UNIQUEは、そのカラムには重複する値を入れることができないことを示します。

    • DEFAULT <デフォルト値>は、そのカラムに何も値が入っていない場合にデフォルトの値を入れることができます。

これらを踏まえて、tweetテーブルを見てみましょう。

# テーブル一覧を表示
.tables

# テーブルの構造を表示
.schema tweets

テーブルの削除

テーブルを削除するには、DROP TABLEを使います。

DROP TABLE <テーブル名>で、テーブルを削除することができます。

レコードの追加

レコードを追加するには、INSERT INTOを使います。

INSERT INTO <テーブル名> (<カラム名1>, <カラム名2>, ...) VALUES (<値1>, <値2>, ...)で、レコードを追加することができます。

試しにid=1とid=2のツイートを追加してみましょう。

INSERT INTO tweets (content, user_id, created_at) VALUES ('今日はいい天気ですね', 2, '2024-06-15 8:00:00');
INSERT INTO tweets (content, user_id, created_at) VALUES ('おなかがすいたなー', 3, '2024-06-15 11:30:00');

レコードの取得

レコードを取得するには、SELECTを使います。

SELECT <カラム名1>, <カラム名2>, ... FROM <テーブル名>で、レコードを取得することができます。

tweetsテーブルの中身を取得してみましょう。

SELECT id, content, user_id, created_at FROM tweets;

上のようにすべてのカラムを取得する場合は、下のように省略して書くこともできます。

SELECT * FROM tweets;

実行してみると、

1|今日はいい天気ですね|2|2024-06-15 8:00:00
2|おなかがすいたなー|3|2024-06-15 11:30:00

このように表示されれば正常にレコードが追加され、取得できています。

レコードの更新

レコードを更新するには、UPDATEを使います。

UPDATE <テーブル名> SET <カラム名1> = <値1>, <カラム名2> = <値2>, ... WHERE <条件>で、レコードを更新することができます。

例えば、id=1のツイートの内容を更新してみましょう。

UPDATE tweets SET content = '明日はいい天気になるといいな' WHERE id = 1;

レコードを取得してみると、内容が更新されていることがわかります。

1|明日はいい天気になるといいな|2|2024-06-15 8:00:00
2|おなかがすいたなー|3|2024-06-15 11:30:00

レコードの削除

レコードを削除するには、DELETEを使います。

DELETE FROM <テーブル名> WHERE <条件>で、レコードを削除することができます。

例えば、id=1のツイートを削除してみましょう。

DELETE FROM tweets WHERE id = 1;

レコードを取得してみると、id=1のツイートが削除されていることがわかります。

2|おなかがすいたなー|3|2024-06-15 11:30:00

AUTOINCREMENTによる連番について

AUTOINCREMENTを指定すると、そのカラムは自動的に増加するようになります。

そのため、例えばid=1のレコードを削除した場合、id=2のレコードがid=1になるわけではありません。

また、今のテーブルの状態から新たにレコードを追加してみるとどうなるでしょうか？

INSERT INTO tweets (content, user_id, created_at) VALUES ('課題がおわりません', 3, '2024-06-20 13:00:00');

レコードを取得してみると、id=3のレコードが追加されていることがわかります。

2|おなかがすいたなー|3|2024-06-15 11:30:00
3|課題がおわりません|3|2024-06-20 13:00:00

連番は自動的に増加するため、削除したレコードのIDが再利用されることはありません。

もし、連番を初期化したい場合は、DELETEで全てのレコードを削除し、連番を管理しているsqlite_sequenceを削除することでリセットすることができます。

DELETE FROM sqlite_sequence WHERE name = '<テーブル名>'

# テーブルの全レコードを削除
DELETE FROM tweets;

# sqlite_sequenceを削除
DELETE FROM sqlite_sequence WHERE name = 'tweets';

ツイートテーブルを作ってみよう

これまでの操作をまとめて、先ほどのツイートテーブルを作成してみましょう。

idcontentuser_idcreated_at
1今日はいい天気ですね22024-06-15 8:00:00
2おなかがすいたなー32024-06-15 11:30:00
3課題がおわりません32024-06-20 13:00:00
4今日のWeb研は何だろう12024-06-28 13:30:00
5ICPCがんばるぞ22024-06-29 14:00:00

先ほどのtweetsテーブルに上記のデータを追加してみましょう。

答え

既にテーブルが作成されているのでテーブルの作成は不要です。

以下のコマンドを実行すればよいです。

INSERT INTO tweets (content, user_id, created_at) VALUES ('今日はいい天気ですね', 2, '2024-06-15 8:00:00');
INSERT INTO tweets (content, user_id, created_at) VALUES ('おなかがすいたなー', 3, '2024-06-15 11:30:00');
INSERT INTO tweets (content, user_id, created_at) VALUES ('課題がおわりません', 3, '2024-06-20 13:00:00');
INSERT INTO tweets (content, user_id, created_at) VALUES ('今日のWeb研は何だろう', 1, '2024-06-28 13:30:00');
INSERT INTO tweets (content, user_id, created_at) VALUES ('ICPCがんばるぞ', 2, '2024-06-29 14:00:00');

テーブルの中身を確認するときは、以下のコマンドを実行してください。

SELECT * FROM tweets;

1|今日はいい天気ですね|2|2024-06-15 8:00:00
2|おなかがすいたなー|3|2024-06-15 11:30:00
3|課題がおわりません|3|2024-06-20 13:00:00
4|今日のWeb研は何だろう|1|2024-06-28 13:30:00
5|ICPCがんばるぞ|2|2024-06-29 14:00:00

このように表示されればOKです。

ユーザーテーブルを作ってみよう

次に、ユーザーテーブルを作成してみましょう。

idusernameemail
1鈴木一郎[email protected]
2田中太郎[email protected]
3山田花子[email protected]

ユーザーテーブルを作成し、上記のデータを追加してみましょう。

答え

まずは、ユーザーテーブルを作成します。

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT NOT NULL,
  email TEXT NOT NULL
);

ユーザーテーブルが作成されているか、以下のコマンドで確認しましょう。

.tables
.schema users

次に、ユーザーテーブルにデータを追加します。

INSERT INTO users (username, email) VALUES ('鈴木一郎', '[email protected]');
INSERT INTO users (username, email) VALUES ('田中太郎', '[email protected]');
INSERT INTO users (username, email) VALUES ('山田花子', '[email protected]');

テーブルの中身を確認するときは、以下のコマンドを実行してください。

SELECT * FROM users;

1|鈴木一郎|[email protected]
2|田中太郎|[email protected]
3|山田花子|[email protected]

このように表示されればOKです。

SQL(応用)

レコードの取得(応用)

冒頭にあった、

ツイートid=1のツイートを投稿したユーザーは誰でしょうか？

という問題をSQLで表してみましょう。

必要なのは、「id=1のツイートのユーザーID」と「そのユーザーID(user_id=2)のユーザー名」です。

  • ツイートテーブルからid=1のツイートのユーザーIDを取得する
SELECT user_id FROM tweets WHERE id = 1;
  • ユーザーテーブルからuser_id=2のユーザー名を取得する
SELECT username FROM users WHERE id = 2;

これらを組み合わせて、id=1のツイートを投稿したユーザー名を取得することができます。

SELECT username FROM users WHERE id = (SELECT user_id FROM tweets WHERE id = 1);

カラムの追加

テーブルにカラムを追加するには、ALTER TABLEを使います。

ALTER TABLE <テーブル名> ADD COLUMN <カラム名> <データ型> <制約>で、カラムを追加することができます。

試しに、ユーザーテーブルにageカラムを追加してみましょう。

ALTER TABLE users ADD COLUMN age INTEGER;

カラムを追加したら、データを追加してみましょう。

レコードの更新を使ってデータを追加することができます。

UPDATE users SET age = 18 WHERE id = 1;
UPDATE users SET age = 31 WHERE id = 2;
UPDATE users SET age = 22 WHERE id = 3;

1|鈴木一郎|[email protected]|18
2|田中太郎|[email protected]|31
3|山田花子|[email protected]|22

このように表示されれば、カラムの追加が成功しています。

フィルタリング

WHEREを使って、条件に合うレコードのみを取得することができます。

例えば、20歳以上のユーザーのみを取得してみましょう。

SELECT * FROM users WHERE age >= 20;

2|田中太郎|
3|山田花子|

次に、今日から過去3日以内に投稿されたツイートを取得してみましょう。

SELECT * FROM tweets WHERE created_at >= date('2024-07-01', '-3 day');

4|今日のWeb研は何だろう|1|2024-06-28 13:30:00
5|ICPCがんばるぞ|2|2024-06-29 14:00:00

<条件>の部分には、比較演算子を使って条件を指定することができたり、date()関数を使って日付を計算することができます。

レコードの結合

ツイートテーブルとユーザーテーブルを結合して、ツイートの内容とユーザー名を取得してみましょう。

JOINを使って、ツイートテーブルとユーザーテーブルを結合することができます。

SELECT tweets.id, tweets.content, users.username FROM tweets JOIN users ON tweets.user_id = users.id;

1|今日はいい天気ですね|田中太郎
2|おなかがすいたなー|山田花子
3|課題がおわりません|山田花子
4|今日のWeb研は何だろう|鈴木一郎
5|ICPCがんばるぞ|田中太郎

最後に

SQLiteでは、.modeや.headersなどのコマンドを使って、表示形式を変更することができます。

.showコマンドを実行すると、データベース接続に関する設定オプションが表示されます。

.echo: off
eqp: off
explain: auto
headers: off
mode: list
nullvalue: ""
output: stdout
colseparator: "|"
rowseparator: "\n"
stats: off
width:
filename: database.db

.headers onを実行すると、テーブルにカラム名が表示されるようになります。

id|content|user_id|created_at
1|今日はいい天気ですね|2|2024-07-01 12:00:00
2|おなかがすいたなー|3|2024-07-01 12:30:00
3|課題がおわりません|3|2024-07-01 13:00:00
4|今日のWeb研は何だろう|1|2024-07-01 13:30:00
5|ICPCがんばるぞ|2|2024-07-01 14:00:00

.mode columnを実行すると、結果がカラム形式で表示されます。

id          content                  user_id     created_at
----------  -----------------------  ----------  -------------------
1           今日はいい天気ですね     2           2024-07-01 12:00:00
2           おなかがすいたなー       3           2024-07-01 12:30:00
3           課題がおわりません       3           2024-07-01 13:00:00
4           今日のWeb研は何だろう   1           2024-07-01 13:30:00
5           ICPCがんばるぞ         2           2024-07-01 14:00:00

sqlite3を終了するには、.exitを実行してください。

まとめ

今回は、データベースの基礎やSQLの文法、SQLiteの機能について学びました。

次回のWeb研では、Node.jsを用いてデータベースとアプリケーションサーバーを連携してみましょう。

【第7回】簡単なWebアプリを作ってみよう

【第9回】データベース入門(2)