演習

今回はSQLiteを使って演習を行います

環境構築

以下を実行してsqlite3がインストールされているかどうかを確認してください

sqlite3 -version

されていない人は以下を参考にインストールしてください

Mac
- 最初からデフォルトでインストールされていると思います
- もしされていない場合はHomebrewなどを使ってインストールしてください(brew install sqlite3)
Ubuntu(WSL)
- sudo apt install -y sqlite3

演習の準備

以下を実行します

# ex1.dbという名前のDB(ファイル)を作成
$ sqlite3 ex1.db

# 初期化を行う
sqlite> DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS contents;

-- usersテーブルを作成
CREATE TABLE users(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE
);

-- usersテーブルにデータを挿入
INSERT INTO users (id, name)
VALUES
    (1, 'matumoto'),
    (2, 'eraser'),
    (3, 'suzakutakumi'),
    (4, 'yuorei');

-- contentsテーブルを作成
CREATE TABLE contents(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    content VARCHAR(140) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- contentsテーブルにデータを挿入
INSERT INTO contents (user_id, content)
VALUES
    (1, 'おなかいて'),
    (1, '二度寝してた'),
    (1, '家 集中力 どうして[検索]'),
    (2, '雨ワロタ'),
    (2, 'とりっくおあとりーと'),
    (3, '手足が軽く震えてる'),
    (4, '山手線一周した'),
    (1, 'ぐわーーーっっっ！！！(断末魔)');


# SELECTの際にヘッダーを表示するように設定を変更
sqlite> .headers on

# テーブル名を表示
sqlite> .table

途中でコピペしているSQL文

DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS contents;

-- usersテーブルを作成
CREATE TABLE users(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE
);

-- usersテーブルにデータを挿入
INSERT INTO users (id, name)
VALUES
    (1, 'matumoto'),
    (2, 'eraser'),
    (3, 'suzakutakumi'),
    (4, 'yuorei');

-- contentsテーブルを作成
CREATE TABLE contents(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    content VARCHAR(140) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- contentsテーブルにデータを挿入
INSERT INTO contents (user_id, content)
VALUES
    (1, 'おなかいて'),
    (1, '二度寝してた'),
    (1, '家 集中力 どうして[検索]'),
    (2, '雨ワロタ'),
    (2, 'とりっくおあとりーと'),
    (3, '手足が軽く震えてる'),
    (4, '山手線一周した'),
    (1, 'ぐわーーーっっっ！！！(断末魔)');

.tableの実行結果でusersテーブルとcontentsテーブルが確認できればOKです

上記の手順どおりに実行できていれば、以下のようなテーブルが生成されていると思います

本題

それでは、ここからSELECT文やINSERT文を使って、DBにたいして操作を行っていきます

`SELECT`文

SELECT文はテーブルから0行以上のデータを取得します

構文

SELECT カラム名1, カラム名2, ... FROM テーブル名;(カラムを指定)
SELECT * FROM テーブル名;(すべてのカラム)

例

SELECT name FROM users;

SELECT content FROM contents;

`WHERE`句

WHERE句によって、条件を指定してテーブルからデータを取得できます

構文

WHERE 条件式

例

SELECT * FROM users WHERE id == 1;

SELECT * FROM contents WHERE user_id == 2;

`JOIN`句

JOIN句によって、複数のテーブルを組み合わせてデータを取得できます

構文

JOIN テーブル名 ON 条件;

例

SELECT * FROM users JOIN contents ON users.id == contents.user_id;

`INSERT`文

INSERT文でテーブルにレコードを追加できます

構文

INSERT INTO テーブル名 VALUES(カラムの値1, カラムの値2, ...)(1レコード丸々挿入)
INSERT INTO テーブル名 (カラム名1, カラム名2, ...) VALUES (カラムの値1, カラムの値2, ...)(カラムを指定して挿入)

例

INSERT INTO users VALUES (5, 'yamada taro')

INSERT INTO contents (user_id, content) VALUES (1, '課プロなう');

`CREATE TABLE`文

CREATE TABLE文でテーブルを作成できます

構文

CREATE TABLE テーブル名(
    カラム名1 型1 制約 ...,
    カラム名2 型2 制約 ...,
    ...
    カラム名N 型N 制約 ...
);

※最後のカラムの指定の際は、末尾のカンマがいらないことに注意！

例

CREATE TABLE favorite_foods(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    favorite_food VARCHAR(100) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

テーブルの制約

PRIMARY KEY
主キー制約
レコードを一意に特定できるものにつける(UNIQUE制約とは違う性質を持つ)
テーブルごとに一つしかつけられない
AUTOINCREMENT
値を指定せずにINSERTすると自動で最後の値に1足されたものが格納される
SQLiteの場合は INTEGERのみに指定可能
NOT NULL
SQLのテーブル定義ではデフォルトではNULLが格納可能だが、これをつけるとそのカラムにNULLを格納できなくなる
FOREIGN KEY (カラム名) REFERENCES テーブル名(カラム名)
外部キー制約
別のテーブルのカラムに存在する値を使う場合、ここでそれを明記する
これで整合性を保つ

SQLiteの主要なデータ型

INTERGER
符号付き整数
REAL
符号付き浮動小数点数
TEXT
文字列

`UPDATE`文

UPDATE文でレコードの値の更新ができます

構文

UPDATE テーブル名 SET カラム名1 = 値1, カラム名2 = 値2, ... WHERE 条件式;

※WHERE句を省略すると全てのレコードが更新されてしまうので注意

例

UPDATE users SET name = 'matumoto1234' WHERE id == 1;

`DELETE`文

DELETE文でレコードを削除できます

構文

DELETE FROM テーブル名 WHERE 条件式;

※WHERE句を省略すると全てのレコードが削除されてしまうので注意

例

DELETE FROM contents WHERE id == 1;

`DROP TABLE`文

DROP TABLE文でテーブルを削除できます

構文

DROP TABLE テーブル名
DROP TABLE IF EXISTS テーブル名

例

DROP TABLE IF EXISTS favorite_foods;

やってみよう

usersテーブルに自分を追加しよう
contentsテーブルに自分のツイートをいくつか追加しよう

追加の演習(追記: 2023/11/09 18:06)

このスプレッドシートの追加演習用のテーブル(usersとfavorite_foods)と同じテーブルを作成してください！

注意事項

制約などは書いてありますが、各カラムのデータ型は書いてありません
それぞれ適切なものを選んで作成してみてください

演習

環境構築​

演習の準備​

本題​

SELECT文​

WHERE句​

JOIN句​

INSERT文​

CREATE TABLE文​

テーブルの制約​

SQLiteの主要なデータ型​

UPDATE文​

DELETE文​

DROP TABLE文​

やってみよう​

追加の演習(追記: 2023/11/09 18:06)​