SQLite でのテーブル構築

はじめに

この実験（Lab）では、小規模アプリケーションに最適な軽量でサーバーレスなデータベースエンジンである SQLite で、テーブルの作成と管理に関する基本的なスキルを学びます。SQLite のデータ型を調べ、主キーを持つテーブルを作成し、NOT NULL や UNIQUE などの制約を適用し、.schema コマンドを使用してテーブル構造を検査し、テーブルを安全に削除します。LabEx VM 環境でのステップバイステップの演習を通して、sqlite3 コマンドラインツールを使用してデータベーステーブルを構築および管理する実践的な経験を積むことができます。この実験（Lab）は初心者向けに設計されており、SQLite テーブル管理を理解するための明確な道筋を提供します。

SQLite に接続し、contacts テーブルを作成する

この最初のステップでは、SQLite データベースに接続し、contacts という名前の最初のテーブルを作成します。SQLite は、データを単一のファイルに保存する軽量なデータベースエンジンです。データベースを操作するには、sqlite3 コマンドラインツールを使用します。

LabEx VM 環境でターミナルを開きます。デフォルトの作業ディレクトリ /home/labex/project にいることを確認してください。
sqlite3 ツールを使用して、test.db という名前の SQLite データベースに接続します。このコマンドは、データベースファイルが存在しない場合は作成します。
```
sqlite3 test.db
```
このコマンドは SQLite シェルを開き、sqlite> のようなプロンプトが表示され、SQLite 環境内にいることを示します。
id、name、phone のカラムを持つ contacts という名前のテーブルを作成します。sqlite> プロンプトで、次の SQL コマンドを入力します。
```
CREATE TABLE contacts (
    id INTEGER PRIMARY KEY,
    name TEXT,
    phone TEXT
);
```
このコマンドは、contacts という名前のテーブルを作成します。id カラムは整数であり、PRIMARY KEY として指定されています。これは、テーブル内の各行を一意に識別することを意味します。name と phone カラムはどちらも TEXT 型であり、任意の文字列データを格納できます。

コマンドを分解してみましょう。
- CREATE TABLE contacts: これは、contacts という名前の新しいテーブルを作成するように SQLite に指示します。
- id INTEGER PRIMARY KEY: これは、データ型 INTEGER を持つ id という名前のカラムを定義します。PRIMARY KEY 制約により、このカラムの各値が一意であり、各行のプライマリ識別子として機能することが保証されます。
- name TEXT: これは、データ型 TEXT を持つ name という名前のカラムを定義します。このカラムは、任意の文字列データを格納できます。
- phone TEXT: これは、データ型 TEXT を持つ phone という名前のカラムを定義します。このカラムは、任意の文字列データを格納できます。

contacts テーブルにデータを挿入する

contacts テーブルを作成したので、データを挿入してみましょう。INSERT INTO ステートメントを使用して、テーブルに新しい行を追加します。

新しい連絡先を contacts テーブルに挿入します。sqlite> プロンプトで、次のコマンドを実行します。
```
INSERT INTO contacts (name, phone) VALUES ('John Doe', '123-456-7890');
```
このコマンドは、名前が 'John Doe'、電話番号が '123-456-7890' の新しい行を contacts テーブルに挿入します。id カラムは主キーであるため、一意の値が自動的に割り当てられます。

コマンドを分解してみましょう。
- INSERT INTO contacts: これは、contacts テーブルにデータを挿入するように SQLite に指示します。
- (name, phone): これは、データを挿入するカラムを指定します。
- VALUES ('John Doe', '123-456-7890'): これは、対応するカラムに挿入する値を指定します。
別の連絡先を contacts テーブルに挿入します。
```
INSERT INTO contacts (name, phone) VALUES ('Jane Smith', '987-654-3210');
```
このコマンドは、名前が 'Jane Smith'、電話番号が '987-654-3210' の別の行を contacts テーブルに挿入します。

contacts テーブルからデータを取得する

contacts テーブルにデータを挿入したので、今度はそれを取得してみましょう。SELECT ステートメントを使用してテーブルをクエリ（問い合わせ）し、データを表示します。

contacts テーブルからすべてのデータを取得します。sqlite> プロンプトで、次のコマンドを実行します。
```
SELECT * FROM contacts;
```
このコマンドは、contacts テーブルからすべてのカラム (*) を選択し、結果を表示します。

期待される出力:
```
1|John Doe|123-456-7890
2|Jane Smith|987-654-3210
```
出力には、挿入した 2 つの連絡先と、自動的に割り当てられた id の値が表示されます。

コマンドを分解してみましょう。
- SELECT *: これは、テーブルからすべてのカラムを選択するように SQLite に指示します。
- FROM contacts: これは、データの取得元となるテーブルを指定します。
contacts テーブルから name と phone カラムのみを取得します。
```
SELECT name, phone FROM contacts;
```
このコマンドは、contacts テーブルから name と phone カラムのみを選択し、結果を表示します。

期待される出力:
```
John Doe|123-456-7890
Jane Smith|987-654-3210
```

制約付きの users テーブルを作成する

制約（constraint）は、テーブル内のデータ整合性を強制するルールです。新しいテーブル users に NOT NULL および UNIQUE 制約を追加してみましょう。

制約付きの users という名前のテーブルを作成します。sqlite> プロンプトで、次のコマンドを実行します。
```
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL,
    age INTEGER
);
```
このコマンドは、次の制約を持つ users という名前のテーブルを作成します。
- username は NULL にできず、UNIQUE である必要があります。
- email は NULL にできません。
コマンドを分解してみましょう。
- CREATE TABLE users: これは、users という名前の新しいテーブルを作成するように SQLite に指示します。
- id INTEGER PRIMARY KEY: これは、データ型 INTEGER の id という名前のカラムを定義します。PRIMARY KEY 制約により、このカラムの各値は一意であり、各行のプライマリ識別子として機能します。
- username TEXT NOT NULL UNIQUE: これは、データ型 TEXT の username という名前のカラムを定義します。NOT NULL 制約により、このカラムを空にすることはできず、UNIQUE 制約により、このカラムのすべての値は異なる必要があります。
- email TEXT NOT NULL: これは、データ型 TEXT の email という名前のカラムを定義します。NOT NULL 制約により、このカラムを空にすることはできません。
- age INTEGER: これは、データ型 INTEGER の age という名前のカラムを定義します。
次に、これらの制約に違反するデータを挿入して、それらがどのように機能するかを確認してみましょう。まず、ユーザー名を指定せずにユーザーを挿入しようとします。
```
INSERT INTO users (email, age) VALUES ('test@example.com', 25);
```
username カラムが NOT NULL として定義されているため、エラーが表示されます。

期待される出力:
```
Error: NOT NULL constraint failed: users.username
```
このエラーは、SQLite が username に対して NOT NULL 制約を強制し、挿入を防止していることを示しています。
次に、重複するユーザー名を持つユーザーを挿入しようとします。まず、有効なユーザーを挿入します。
```
INSERT INTO users (username, email, age) VALUES ('johndoe', 'john.doe@example.com', 30);
```
次に、同じユーザー名を持つ別のユーザーを挿入しようとします。
```
INSERT INTO users (username, email, age) VALUES ('johndoe', 'jane.doe@example.com', 25);
```
username カラムが UNIQUE として定義されているため、エラーが表示されます。

期待される出力:
```
Error: UNIQUE constraint failed: users.username
```
このエラーは、SQLite が username に対して UNIQUE 制約を強制し、重複する値を防止していることを示しています。

.schema でテーブル構造を検査する

.schema コマンドを使用すると、テーブルの構造を検査できます。このコマンドは、テーブルの作成に使用された SQL ステートメントを表示します。これには、カラム名、データ型、および制約が含まれます。

users テーブルの構造を検査します。sqlite> プロンプトで、次のコマンドを実行します。
```
.schema users
```
期待される出力:
```
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL,
    age INTEGER
);
```
出力には、users テーブルの CREATE TABLE ステートメントが表示されます。これには、username カラムの NOT NULL および UNIQUE 制約が含まれます。

.schema コマンドは、テーブルの構造を理解し、制約が正しく定義されていることを確認するための便利なツールです。
SQLite シェルを終了します。
```
.quit
```
このコマンドは、SQLite シェルを終了し、Linux ターミナルに戻ります。

重要： 「Continue」ボタンをクリックする前に、必ず .exit してください。そうしないと、sqlite の履歴が記録されないため、ステップを検証できません。

contacts テーブルを削除する

DROP TABLE コマンドを使用すると、データベースからテーブルを削除できます。このコマンドは、テーブルとそのすべてのデータを完全に削除するため、注意して使用してください。

test.db データベースに接続します。
```
sqlite3 test.db
```
contacts テーブルを削除します。sqlite> プロンプトで、次のコマンドを実行します。
```
DROP TABLE contacts;
```
このコマンドは、contacts テーブルとそのすべてのデータをデータベースから削除します。

警告: この操作は元に戻せません。テーブルを削除すると、テーブルまたはそのデータを復元することはできません。
contacts テーブルが削除されたことを確認するために、テーブルから選択を試みます。
```
SELECT * FROM contacts;
```
contacts テーブルが存在しなくなったため、エラーが表示されます。
SQLite シェルを終了します。
```
.quit
```

まとめ

この実験（Lab）では、SQLite でのテーブルの構築と管理に関する基本的なスキルを学びました。SQLite のデータ型を調査し、主キー（primary key）を持つテーブルを作成し、NOT NULL や UNIQUE などの制約（constraint）を適用し、.schema コマンドを使用してテーブル構造を検査し、安全にテーブルを削除しました。これらのスキルは、SQLite データベースを操作し、データ駆動型アプリケーション（data-driven application）を構築するために不可欠です。