【SQL初心者メモ】SQLの基礎

SQLの概要

SQLとは？

SQLとは、RDB（Relational Database）を操作するための言語です。
データベースのデータを格納したり、データを抽出したり、データを書き換えたりすることができます。SQLは「Structured Query Language」の略で、日本語では「構造化照会言語」と呼ばれます。

RDBとは？

RDBとはデータを「表（テーブル）」として管理するデータベースのことです。

基本の考え方

• データを 行（レコード）と列（カラム）で管理する
• 複数のテーブルを関係（リレーションシップ）でつなげる
• SQLでデータを操作する

イメージ

このような表形式がRDBの基本単位なります。

id	name	age
1	田中太郎	25
2	鈴木花子	30

特徴

• 整合性を保ちやすい
• 外部キーで関連テーブルの整合性を保証できる
• 柔軟に検索できる
• SQLで複雑な条件検索や集計が可能
• スキーマが決まっている
• 列の型や制約（NOT NULL、UNIQUEなど）が決まっている

RDBMSとは？

RDB型データベースを管理するためのシステムを「RDBMS（Relational DataBase Management System」と呼びます。

RDBMSには企業から提供されている商用と、有志の開発者コミュニティで開発され、自由に利用・改変可能なオープンソースのデータベース管理システムがあります。

商用のデータベース管理システムには以下のようなソフトウェアがあります。

• Oracle Database（Oracle）
• Microsoft SQL Server（Microsoft）
• IBM DB2（IBM）

一方で、オープンソースのデータベース管理システムには以下のようなソフトウェアがあります。

• MySQL
• Postgre SQL

SQLの特徴

多くのDBMSで使用可能

現在の主要なDBMS（MySQL, PostgreSQL, SQLiteなど）はリレーショナル型データベースを採用しており、SQLを使って操作します。そのため、一度SQLを覚えれば多くのデータベースで応用できます。

一方的に命令を送る

SQLは、シンプルな命令文を1文ずつデータベースに送る仕組みです。プログラミング言語のように命令を順序立てて書くというよりも、必要なときに必要な命令を対話のように送信します。基本的に1文で完結し、複雑な処理を行う場合は、返ってきた処理の結果に応じてまた命令を送るというようにデータベースと対話するように命令文をやりとりします。

あくまで「データベースを操作する言語」

SQL自体にアプリケーションを作る機能はなく、ほかのプログラミング言語（例：Java, Python）と組み合わせて使います。

SQLでデータベースを操作する方法

SQLでデータベースを操作するには、大きく分けて2つの方法があります。

対話型

ターミナルやコマンドライン、SQLツール（MySQL, psql, SQLiteなど）で直接SQLコマンドを入力して実行する方法です。ユーザーが一つずつコマンドを打ち込み、実行結果を見ながら操作します。

埋め込み型

JavaやPythonなど他のプログラミング言語にSQL文を埋め込んで実行する方法です。これによりプログラムの一部としてSQLを使うことができ、条件分岐や繰り返し処理と組み合わせて柔軟な操作が可能になります。また、「ある命令の結果をもとに次の命令を実行する」といった入れ子構造（ネスト）でSQLを書くことを「サブクエリ」といいます。

SQLに関する基本用語

▼ テーブル（table）
RDB型データベースで、データを項目ごとに整理して格納する「表」。
例：usersテーブル、ordersテーブル など

▼ レコード（record）
テーブル内にある1行分のデータ。1人のユーザーや1つの商品など。
例：1行 = 1人の顧客情報（id, name, email など）

▼ カラム（column）
テーブルの列（縦方向）にあたる。各データの「属性」。
例：商品ID・商品名・価格など

▼ ビュー（view）
テーブルのデータを特定の条件で抽出した結果を仮想的に表現するもの。

▼ フィールド（field）
データベースの最小単位。Excelでいう「セル」。
例：あるレコードの「名前」欄だけ、など

▼ 主キー（Primary Key）
各レコードを一意に識別するためのカラム。
例：社員番号、会員ID など（重複・NULL不可）

▼ リレーション（relation）
複数のテーブルが外部キーで関連づけられていること。

▼ リレーションシップ（relationship）
テーブル同士の関係（1対多、多対多など）を示す概念。
例：1人のユーザーが複数の投稿を持つ → users と posts にリレーションシップあり

▼ 外部キー（Foreign Key）
他のテーブルの主キーを参照するカラム。
例：postsテーブルのuser_idカラムは、usersテーブルのidカラムを参照する外部キー。
これにより、テーブル間のリレーションシップを構築する。

▼ 制約（constraint）
テーブルのカラムに対して設定するルール。
例：NOT NULL（NULL不可）、UNIQUE（重複不可）、CHECK（条件付き制約）など

▼ インデックス（index）
テーブルの検索を高速化するためのデータ構造。
例：ユーザー名で検索する場合、nameカラムにインデックスを作成することで検索速度が向上する。

▼ サブクエリ（subquery）
SQLの中で使われる“入れ子のSELECT文”のこと

▼ トランザクション（transaction）
一連のSQL操作をまとめて実行する単位。全て成功するか、全て失敗するかのいずれか。
例：銀行振込処理では、送金元口座からの引き落としと送金先口座への入金を1つのトランザクションとして扱う。

▼ コミット（commit）
トランザクションの処理を確定させること。データベースに変更を保存する。
例：振込処理が成功した場合、送金元口座からの引き落としと送金先口座への入金をコミットする。

▼ ロールバック（rollback）
トランザクションの処理を取り消すこと。データベースの状態を変更前に戻す。
例：振込処理が失敗した場合、送金元口座からの引き落としと送金先口座への入金をロールバックする。

SQLの主な種類（分類）

SQLは機能によって以下の3つに分類されます。

データ操作言語（DML:Data Manipulation Language）

データベースに対してデータを追加・更新・削除などを行うためのSQL命令文の種類です。
例：SELECT（データの検索）、INSERT（データの追加）、
　　UPDATE（データの更新）、DELETE（データの削除）

データ定義言語（DDL:Data Definition Language）

テーブルなどを作成・削除したり、設定を変更するためのSQL命令文の種類です。
例：CREATE（テーブルの作成）、ALTER（テーブルの設定変更）、
　　DROP（テーブルの削除）、TRUNCATE（テーブルデータの削除）

データ制御言語（DCL:Data Control Language）

DMLやDDLの利用を制御するためのSQL命令文の種類です。
例：GRANT（権限の付与）、REVOKE（権限の剥奪）

トランザクション制御言語（TCL:Transaction Control Language）

トランザクション（複数の操作）を制御するためのSQL命令文の種類です。
例：BEGIN（トランザクションの開始）、COMMIT（トランザクションの確定）、
　　ROLLBACK（トランザクションの取り消し）、SAVEPOINT（戻るポイントの作成）

SQLで使われるデータ型の種類

SQLでは、データを格納するためのデータ型がいくつかあります。

代表的なデータ型

• 自動連番（ID用）に使われる型
  serial：整数の自動連番（32bit）
  bigserial：より大きな自動連番（64bit）
  smallserial：小さい自動連番（16bit）
  uuid：グローバル一意識別子（文字列）
• 数値型
  integer（int）：32bitの整数
  bigint：64bitの整数
  smallint：16bitの整数
  numeric(10,2) / decimal(10,2)：精度指定可能な小数（例：金額）
  real：単精度浮動小数点（誤差あり）
  double precision：倍精度浮動小数点（より正確）
• 文字列型
  text：長さ制限なしの文字列
  varchar(n) ：最大n文字までの文字列
  char(n)：必ずn文字分確保（余白埋め）→ 非推奨なことも
• 日時型
  timestamp：日付＋時刻（タイムゾーンなし）
  timestamp with time zone（timestamptz）：タイムゾーン付き
  date：日付のみ
  time：時刻のみ
• 配列型
  integer[]：整数の配列
  text[]：文字列の配列
  boolean[]：真偽値の配列
• 論理型/その他
  boolean：true / false（またはt / f）
  json / jsonb：JSONデータ（jsonbはバイナリ最適化済みで高速）
  bytea：バイナリデータ（画像など）
  array：配列（例：integer[]）

よく使う組み合わせ

主キーID → serial or uuid
名前 → text or varchar(100)
年齢 → integer
登録日時 → timestamp with time zone default now()
フラグ → boolean default false
JSON形式の設定 → jsonb

SQLで使われる比較演算子

SQLでは以下のような比較演算子が使われます。

• =：等しい
• <> または !=：等しくない
• >：より大きい
• <：より小さい
• >=：以上
• <=：以下

データ操作言語

1. SELECT:データを取り出す

▼ 基本構文
SELECT 列名1, 列名2, ... FROM テーブル名 WHERE 条件;

▼ 例

-- ユーザーの名前と年齢を取得
SELECT name, age FROM users;

-- ユーザーの名前と年齢を取得（年齢が25歳以上）
SELECT name, age FROM users WHERE age >= 25;

-- ユーザーの名前と年齢を取得（年齢が25歳以上、かつ名前が'Taro'）
SELECT name, age FROM users WHERE age >= 25 AND name = 'Taro';

2. INSERT：データを追加する

▼ 基本構文
INSERT INTO テーブル名 (列名1, 列名2, ...) VALUES (値1, 値2, ...);

▼ 例

-- ユーザー「Taro」を追加（ID:1、年齢:25）
INSERT INTO users (id, name, age) VALUES (1, 'Taro', 25);

-- ユーザー「Hanako」を追加（ID:2、年齢:22）
INSERT INTO users (id, name, age) VALUES (2, 'Hanako', 22);

3. UPDATE：データを更新する

▼ 基本構文
UPDATE テーブル名 SET 列名1 = 値1, 列名2 = 値2, ... WHERE 条件;

▼ 例

-- ユーザーの年齢を更新（ユーザーIDが1のユーザーの年齢を30に変更）
UPDATE users SET age = 30 WHERE id = 1;

-- ユーザーの年齢を更新（ユーザーIDが1のユーザーの年齢を30に変更、かつ名前が'Taro'）
UPDATE users SET age = 30 WHERE id = 1 AND name = 'Taro';

-- ユーザーの年齢を更新（ユーザーIDが1のユーザーの年齢を30に変更、かつ名前が'Taro'、かつ年齢が25歳以上）
UPDATE users SET age = 30 WHERE id = 1 AND name = 'Taro' AND age >= 25;

4. DELETE：データを削除する

▼ 基本構文
DELETE FROM テーブル名 WHERE 条件;

▼ 例

-- ユーザーを削除（ユーザーIDが1のユーザーを削除）
DELETE FROM users WHERE id = 1;

-- ユーザーを削除（ユーザーIDが1のユーザーを削除、かつ名前が'Taro'）
DELETE FROM users WHERE id = 1 AND name = 'Taro';

-- ユーザーを削除（ユーザーIDが1のユーザーを削除、かつ名前が'Taro'、かつ年齢が25歳以上）
DELETE FROM users WHERE id = 1 AND name = 'Taro' AND age >= 25;

データ定義言語

1. CREATE TABLE：テーブルを作成する

▼ 基本構文

CREATE TABLE テーブル名 (
  列名1 データ型 制約,
  列名2 データ型 制約,
  ...
);

▼ 例

-- ユーザー情報を管理するテーブル（users）
CREATE TABLE users (
  id INT PRIMARY KEY,                         
  name VARCHAR(50) NOT NULL,                  
  email VARCHAR(100) UNIQUE,                  
  age INT CHECK (age >= 0),                   
  status VARCHAR(20) DEFAULT 'active'         
);

-- 注文情報を管理するテーブル（orders）
CREATE TABLE orders (
  id INT PRIMARY KEY,                         
  user_id INT,                                
  FOREIGN KEY (user_id) REFERENCES users(id)  
);

2. ALTER TABLE：テーブルの構造を変更する

▼ 基本構文

-- 列を追加する
ALTER TABLE テーブル名 ADD 列名 データ型 制約;

-- 列を削除する
ALTER TABLE テーブル名 DROP COLUMN 列名;

-- 列の名前を変更する
ALTER TABLE テーブル名 RENAME COLUMN 旧列名 TO 新列名;

-- テーブル名を変更する
ALTER TABLE 旧テーブル名 RENAME TO 新テーブル名;

▼ 例

-- usersテーブルにemail列を追加
ALTER TABLE users ADD email VARCHAR(100);

-- usersテーブルからemail列を削除
ALTER TABLE users DROP COLUMN email;

-- usersテーブルのname列をusernameに変更
ALTER TABLE users RENAME COLUMN name TO username;

-- usersテーブルの名前をmembersに変更
ALTER TABLE users RENAME TO members;

3. DROP TABLE：テーブルを削除する

▼ 基本構文
DROP TABLE テーブル名;

▼ 例

-- usersテーブルを削除
DROP TABLE users;

-- usersテーブルとpostsテーブルを同時に削除
DROP TABLE users, posts;

4. TRUNCATE TABLE：テーブルのデータを削除する

▼ 基本構文
TRUNCATE TABLE テーブル名;

▼ 例

-- usersテーブルのデータを全て削除（テーブル構造は残る）
TRUNCATE TABLE users;

-- usersテーブルとpostsテーブルのデータを全て削除（テーブル構造は残る）
TRUNCATE TABLE users, posts;

データ制御言語

1. GRANT：権限を付与する

▼ 基本構文
GRANT 権限 ON テーブル名 TO ユーザー名;

▼ 例

-- user1 に users テーブルの SELECT（参照）権限を付与
GRANT SELECT ON users TO user1;

-- user1 に users テーブルのすべての権限（SELECT, INSERT, UPDATE, DELETE など）を付与
GRANT ALL PRIVILEGES ON users TO user1;

-- user1 に users テーブルのすべての権限を付与し、さらに他のユーザーに権限を付与する権限も与える
GRANT ALL PRIVILEGES ON users TO user1 WITH GRANT OPTION;

2. REVOKE：権限を剥奪する

▼ 基本構文
REVOKE 権限 ON テーブル名 FROM ユーザー名;

▼ 例

-- user1 から users テーブルの SELECT（参照）権限を剥奪
REVOKE SELECT ON users FROM user1;

-- user1 から users テーブルのすべての権限（SELECT, INSERT, UPDATE, DELETE など）を剥奪
REVOKE ALL PRIVILEGES ON users FROM user1;

-- user1 から users テーブルのすべての権限を剥奪し、さらにその権限に依存している権限もまとめて剥奪（CASCADE）
REVOKE ALL PRIVILEGES ON users FROM user1 CASCADE;

トランザクション制御言語

トランザクションは、データベースの一連の操作をまとめて実行するための単位です。トランザクションを使用することで、データの整合性を保つことができます。

• トランザクションは、BEGINで開始し、COMMITで確定します
• もし途中でエラーが発生した場合は、ROLLBACKを使用して、トランザクションを取り消すことができます

1. BEGIN/START TRANSACTION：トランザクションを開始する

▼ 基本構文
BEGIN;
START TRANSACTION;

▼ 例

BEGIN; -- トランザクションを開始
INSERT INTO users (id, name, age) VALUES (1, 'Taro', 25);

2. COMMIT：トランザクションを確定する

▼ 基本構文
COMMIT;

▼ 例

BEGIN;
UPDATE users SET age = 30 WHERE id = 1;
COMMIT; -- 変更を確定

3. ROLLBACK：トランザクションを取り消す

▼ 基本構文
ROLLBACK;

▼ 例

BEGIN;
UPDATE users SET age = 30 WHERE id = 1;
ROLLBACK; -- 更新を取り消す

BEGIN;
UPDATE users SET age = 30 WHERE id = 1;
SAVEPOINT sp1;
UPDATE users SET age = 40 WHERE id = 2;
ROLLBACK TO sp1; -- id=2 の更新だけ取り消す
COMMIT;

4. SAVEPOINT：戻れるポイントを作成する

▼ 基本構文
SAVEPOINT;

▼ 例

BEGIN;
UPDATE users SET age = 30 WHERE id = 1;
SAVEPOINT sp1;
UPDATE users SET age = 40 WHERE id = 2;
-- ここでROLLBACK TO sp1を使えばid=2の更新だけ取り消せる

データ操作補足

WHERE：検索、更新、削除時の条件を指定する

▼ 基本構文（条件に一致するデータだけを取得）
SELECT（UPDATE、DELETE） 列名 FROM 表名 WHERE 条件;

▼ 例

SELECT * FROM users WHERE age >= 30;
SELECT * FROM users WHERE name = 'Alice';
SELECT * FROM users WHERE age > 20 AND gender = 'male';

HAVING：グループ化されたデータに条件を付ける

▼ 基本構文（GROUP BYと併用して集計結果に条件を付ける）
SELECT グループ列, 集計関数 FROM 表名 GROUP BY グループ列 HAVING 条件;

▼ 例

-- ユーザー数が10人以上の性別グループだけを取得
SELECT gender, COUNT(*) FROM users GROUP BY gender HAVING COUNT(*) >= 10;

-- 平均年齢が30歳未満の都道府県を抽出
SELECT prefecture, AVG(age) FROM users GROUP BY prefecture HAVING AVG(age) < 30;

▼ 補足

• WHEREは「個々の行」に対して、HAVINGは「グループ集計結果」に対して使う。
• GROUP BYがない場合はHAVINGは使えない。

比較述語

これらはWHERE句やHAVING句などで使用されます。

• IN
  INは、指定した値のリストの中に値が含まれているかどうかをチェックします。
  SELECT * FROM users WHERE id IN (1, 3, 5);
• BETWEEN
  BETWEENは、指定した範囲内に値があるかどうかをチェックします。
  SELECT * FROM users WHERE age BETWEEN 20 AND 29;
• LIKE
  LIKEは、文字列のパターンマッチングを行います。ワイルドカード（%）を使用して部分一致を指定できます。
  SELECT * FROM users WHERE name LIKE 'A%';
• NOT LIKE
  NOT LIKEは、文字列のパターンマッチングで、指定したパターンに一致しないデータを取得します。
  SELECT * FROM users WHERE name NOT LIKE 'A%';
• IS NULL
  IS NULLは、指定した列がNULLであるかどうかをチェックします。
  SELECT * FROM users WHERE age IS NULL;
• IS NOT NULL
  IS NOT NULLは、指定した列がNULLでないかどうかをチェックします。
  SELECT * FROM users WHERE age IS NOT NULL;
• EXISTS
  EXISTSは、サブクエリの結果が存在するかどうかをチェックします。
  SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);
• NOT EXISTS
  NOT EXISTSは、サブクエリの結果が存在しないかどうかをチェックします。
  SELECT * FROM users WHERE NOT EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);

VALUES：INSERTで挿入する値を指定

▼ 基本構文（VALUESで新しいデータを追加）
INSERT INTO 表名 (列1, 列2, ...) VALUES (値1, 値2, ...);

▼ 例

-- 新しいユーザーを追加
INSERT INTO users (name, age, gender) VALUES ('Taro', 25, 'male');

-- 複数行をまとめて挿入
INSERT INTO users (name, age, gender)
VALUES
  ('Hanako', 30, 'female'),
  ('Ken', 22, 'male');

JOIN

複数のテーブルを結合して、一つの結果セットとして取得する操作です。

JOIN（INNER JOIN）：2つのテーブルを内部結合する

▼ 基本構文（両方のテーブルに共通するデータを取得）

SELECT 列名
FROM 表A
INNER JOIN 表B
ON 表A.共通列 = 表B.共通列;

▼ 例

-- ユーザーと注文の情報を結合して取得する（共通のuser_id）
SELECT users.id, users.name, orders.amount
FROM users
INNER JOIN orders
ON users.id = orders.user_id;

▼ 補足
JOIN（INNER JOIN）は内部結合です。
内部結合とは両方のテーブルに共通するデータだけを取得する結合方法です。
上記の例では、sers.id = orders.user_id が成立する行のみ取得します。

LEFT JOIN：左側のテーブルのすべての行を取得する

▼ 基本構文（左側のテーブルを基準に、右に一致するデータを結合）

SELECT 列名
FROM 表A
LEFT JOIN 表B
ON 表A.共通列 = 表B.共通列;

▼ 例

-- 全ユーザーと、それに対応する注文情報（ない場合はNULL）を取得
SELECT users.id, users.name, orders.amount
FROM users
LEFT JOIN orders
ON users.id = orders.user_id;

▼ 補足
LEFT JOINは外部結合です、
外部結合とは片方のテーブルにしか存在しない行も結果に含める結合方法です。
上記の例では、ordersに該当するデータがなくても、usersのデータはすべて取得されます。
結合できない部分はNULLになります。

RIGHT JOIN：右側のテーブルのすべての行を取得する

▼ 基本構文（右側のテーブルを基準に、左に一致するデータを結合）

SELECT 列名
FROM 表A
RIGHT JOIN 表B
ON 表A.共通列 = 表B.共通列;

▼ 例

-- 全注文と、それに対応するユーザー情報（ない場合はNULL）を取得
SELECT users.id, users.name, orders.amount
FROM users
RIGHT JOIN orders
ON users.id = orders.user_id;

▼ 補足
RIGHT JOINも外部結合です。
上記の例では、usersに該当するデータがなくても、ordersのデータはすべて取得されます。
結合できない部分はNULLになります。

ORDER BY：結果の並び順を指定する

▼ 基本構文（指定した列で昇順・降順に並び替える）
SELECT 列名 FROM 表名 ORDER BY 列名 ASC|DESC;

▼ 例

-- 年齢が若い順にユーザーを並び替える（昇順）
SELECT * FROM users ORDER BY age ASC;

-- 年齢が高い順に並び替える（降順）
SELECT * FROM users ORDER BY age DESC;

• ASC（昇順）がデフォルト
• 複数列で並び替えも可能。
  例）age → 同じなら name
  SELECT * FROM users ORDER BY age, name;

GROUP BY：グループごとに集計する

▼ 基本構文（指定列でグループ化して集計）
SELECT グループ列, 集計関数 FROM 表名 GROUP BY グループ列;

▼ 例

-- 性別ごとのユーザー数を集計
SELECT gender, COUNT(*) FROM users GROUP BY gender;

-- 都道府県ごとの平均年齢を取得
SELECT prefecture, AVG(age) FROM users GROUP BY prefecture;

LIMIT：取得する件数を制限する

▼ 基本構文
SELECT 列名 FROM 表名 LIMIT 行数;

▼ 例

-- ユーザーの上位5件だけ取得する
SELECT * FROM users LIMIT 5;

OFFSET：何行目から取得を始めるかを指定する

▼ 基本構文
SELECT 列名 FROM 表名 LIMIT 行数 OFFSET 開始位置;

▼ 例

-- 6件目から5件取得（ページネーションに便利）
SELECT * FROM users LIMIT 5 OFFSET 5;

CASE：条件分岐を実現する

▼ 基本構文

SELECT
  CASE
    WHEN 条件1 THEN 値1
    WHEN 条件2 THEN 値2
    ELSE デフォルト値
  END AS 列名
FROM 表名;

▼ 例

-- 年齢によって「成人」「未成年」と表示する
SELECT name,
  CASE
    WHEN age >= 20 THEN '成人'
    ELSE '未成年'
  END AS status
FROM users;

SQLの関数

組み込み関数

組み込み関数とはデータベースにあらかじめ用意されている関数です。
RDBMSには、多くの組み込み関数が用意されており、データの操作や変換を簡単に行うことができます。

代表的な組み込み関数には以下のようなものがあります：

• COUNT：行数をカウントする
• SUM：数値の合計を計算する
• AVG：数値の平均を計算する
• MAX：最大値を取得する
• MIN：最小値を取得する
• LENGTH：文字列の長さを取得する
• UPPER：文字列を大文字に変換する
• LOWER：文字列を小文字に変換する
• NOW：現在の日時を取得する
• COALESCE：NULLでない最初の値を返す
• CONCAT：文字列を連結する
• TO_CHAR：日付や数値を文字列に変換する
• TO_NUMBER：文字列を数値に変換する
• LEAST(a, b, c, ...)；最小値を返す
• GREATEST(a, b, c, ...)；最大値を返す

トリガー関数

トリガー関数は、特定のイベント（INSERT, UPDATE, DELETEなど）が発生したときに自動的に実行される関数です。トリガーは、データベースの整合性を保つためや、特定の処理を自動化するために使用されます。

▼ 基本構文

CREATE TRIGGER トリガー名
  トリガータイミング（BEFORE|AFTER、INSERT|UPDATE|DELETE）
  ON テーブル名
  詳細指定（FOR EACH ROW、 EXECUTE FUNCTION 関数名()など）;

▼ トリガータイミング
BEFORE：操作（INSERT/UPDATE/DELETE）の前に実行
AFTER：操作（INSERT/UPDATE/DELETE）の後に実行
INSTEAD OF：実際の操作（INSERT/UPDATE/DELETE）の代わりに実行
INSERT：データが追加されたときに実行
UPDATE：データが更新されたときに実行
DELETE：データが削除されたときに実行

▼ トリガーの詳細指定
FOR EACH ROW：行ごとに実行
EXECUTE FUNCTION：実行する関数を指定

▼ 例

-- ユーザーが追加されたときに、ログを記録するトリガー
CREATE TRIGGER log_user_insert
AFTER INSERT
ON users
FOR EACH ROW
EXECUTE FUNCTION log_user_activity();

-- ユーザーが削除されたときに、ログを記録するトリガー
CREATE TRIGGER log_user_delete
AFTER DELETE
ON users
FOR EACH ROW
EXECUTE FUNCTION log_user_activity();

ユーザー定義関数

ユーザー定義関数は、特定の処理を実行するために作成する関数です。
SQL文やロジックをまとめて再利用できるため、効率的なデータベース操作が可能です。

▼ 基本構文

-- 新しく関数を作成
CREATE FUNCTION 関数名 (引数1 型, 引数2 型, ...)
RETURNS 戻り値の型
LANGUAGE 言語名
AS $$
BEGIN
  -- SQL文やロジック
  RETURN 戻り値;
END;
$$;

-- 既存の関数を変更
REPLACE FUNCTION 関数名 (引数1 型, 引数2 型, ...)
RETURNS 戻り値の型
LANGUAGE 言語名
AS $$
BEGIN
  -- SQL文やロジック
  RETURN 戻り値;
END;
$$;

• BEGIN ... END
  複数のSQL文をひとまとまりの処理ブロックにする。
• $$ ... $$
  関数の本体（コード）を囲む文字列リテラルの区切り、SQLでは関数定義の中身（本体のコード）を文字列として扱う必要があります。"$$" は、その文字列の開始と終了を囲むための記号（区切り）となります。
• AS
  「別名」や「内容の定義」 を指定するキーワードです。
  AS $$ ... $$の部分で、「この関数の処理はここに書いてありますよ」と指定しています。

▼ 例（関数単体）

-- ユーザーの年齢を取得する関数
CREATE FUNCTION get_user_age(IN user_id INT)
RETURNS INT
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN (SELECT age FROM users WHERE id = user_id);
END;
$$;

-- 関数を実行する
SELECT get_user_age(1);
SELECT get_user_age();
SELECT get_user_age() AS age;

▼ 例（ユーザー定義関数とトリガー関数の組み合わせ）
ユーザー定義関数とトリガー関数は組み合わせて使用することが多いです。

-- 関数を定義
-- ユーザーが追加されたときに、登録日時を自動で設定するトリガー関数
CREATE FUNCTION set_added_at()
RETURNS trigger AS $$
BEGIN
  NEW.added_at := now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- トリガーを定義
-- ユーザーが追加されたときに、登録日時を自動で設定するトリガー
CREATE TRIGGER trg_set_added_at
BEFORE INSERT ON items
FOR EACH ROW
EXECUTE FUNCTION set_added_at();

• RETURNS triggerとは
  この関数はトリガーから呼び出されることが前提であることを示します。RETURNS trigger を指定したトリガー関数の中では、以下のような特殊変数を使うことができます。
  ・NEW：INSERTやUPDATEで新しく挿入される行の値を参照
  ・OLD：DELETEやUPDATEで削除される行の値を参照
  ・TG_OP：トリガーが実行された操作の種類（INSERT, UPDATE, DELETEなど）
  ・TG_TABLE_NAME：トリガーが関連付けられたテーブルの名前を参照

ウィンド関数

ウィンド関数は、SELECT文で取得した行の集合（ウィンドウ）に対して、集計などの計算を行う関数です。集計関数（SUM()やAVG()など）のように結果セットの行数を減らす（集約する）のではなく、各行に対して計算結果を返すため、元のデータと同じ行数を維持したまま、行間の比較やランキング、移動平均などの分析が可能です。

▼ 代表的な関数
ROW_NUMBER)：行に連番を付ける
RANK()：同値の場合は順位が飛ぶ
DENSE_RANK()：同値の場合も順位が飛ばない
SUM()/AVG()：移動合計や移動平均に使用
LEAD()/LAG()：前後の行の値を取得

▼ 基本構文

SELECT 列名, 
 ウィンド関数() OVER (PARTITION BY 列名 ORDER BY 列名) AS 別名 
FROM 表名;

▼ 例

-- ユーザーの年齢に基づいてランキングを付ける
SELECT name, age,
  RANK() OVER (ORDER BY age DESC) AS age_rank
FROM users;

制約（Constraints）

制約はテーブルの列に対して適用されるルールで、データの整合性を保つために使用されます。

制約の種類

制約の種類には以下のようなものがあります。

• NOT NULL：カラムに入るNULL値を許可しない
• NULL；カラムに入るNULL値を許可する（何も指定しない場合デフォルトで許可される）
• UNIQUE：列の値が一意であることを保証する
• PRIMARY KEY：主キー制約（NOT NULLかつ必ず一意でなくてはならない）
• FOREIGN KEY：外部キー制約（他のテーブルの主キーを参照する）
• CHECK：カラムに入る値が特定の条件を満たすかどうかをチェックする
• DEFAULT：カラムに入るデフォルト値を指定する
• INDEX：検索を高速化するためのインデックスを作成する
• EXCLUDE：特定の条件を満たす行が存在しないことを保証する（PostgreSQL特有）
• TRIGGER：特定のイベントが発生したときに自動的に実行される処理を定義する
• RULE：特定のクエリに対して自動的に変換されるルールを定義する（PostgreSQL特有）

制約の指定方法

制約の指定方法はインライン（列レベル）とアウト・オブ・ライン（テーブルレベル）の指定方法があります。

インライン（列レベル）

▼ 基本構文

CREATE TABLE テーブル名 (
  列名1 データ型 制約の種類,
  列名2 データ型 制約の種類,
);

-- ▼ 例

-- ユーザー情報を管理するテーブル（users）
CREATE TABLE users (
  id INT PRIMARY KEY,                         -- 主キー制約
  name VARCHAR(50) NOT NULL,                  -- NOT NULL制約
  email VARCHAR(100) UNIQUE,                  -- UNIQUE制約
  age INT CHECK (age >= 0),                   -- CHECK制約（年齢は0以上）
  status VARCHAR(20) DEFAULT 'active'         -- DEFAULT制約（初期値を設定）
); 

-- 注文情報を管理するテーブル（orders）
CREATE TABLE orders (
  id INT PRIMARY KEY,                         -- 主キー制約
  user_id INT,                                -- ユーザーID（外部キーになる列）
  amount DECIMAL(10, 2),                      -- 注文金額
  FOREIGN KEY (user_id) REFERENCES users(id)  -- 外部キー制約
);

アウト・オブ・ライン（テーブルレベル）

もしくは、constraintを使用して、制約に名前を付けることもできます。

▼ 基本構文

CREATE TABLE テーブル名 (
  列名1 データ型 制約,
  列名2 データ型 制約,
  ...
  CONSTRAINT 制約名 制約の種類 (列名)
);

-- ▼ 例

-- 注文情報を管理するテーブル（orders）
CREATE TABLE orders (
  id INT PRIMARY KEY,                         -- 主キー制約
  user_id INT,                                -- ユーザーID（外部キーになる列）
  amount DECIMAL(10, 2),                      -- 注文金額
  CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id)  -- 外部キー制約
);

外部キー制約

外部キーは、あるテーブルの列が別のテーブルの主キーを参照することを示す制約です。
外部キーを使用することで、テーブル間のリレーションシップを定義し、データの整合性を保つことができます。

インライン

▼ 基本構文

CREATE TABLE 子テーブル名 (
  列名1 データ型,
  列名2 データ型,
  FOREIGN KEY (子テーブルの列名) REFERENCES 親テーブル名(親テーブルの主キー列名)
);

▼ 例

-- ユーザー情報を管理するテーブル（users）
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  age INT CHECK (age >= 0)
);

-- 注文情報を管理するテーブル（orders）
CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  amount DECIMAL(10, 2),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
);

アウトオブライン

▼ 基本構文

CREATE TABLE 子テーブル名 (
  列名1 データ型,
  列名2 データ型,
  列名3 データ型,
  CONSTRAINT 外部キー名 FOREIGN KEY (子テーブルの列名) REFERENCES 親テーブル名(親テーブルの主キー列名)
);

▼ 例

-- ユーザー情報を管理するテーブル（users）
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  age INT CHECK (age >= 0)
);

-- 注文情報を管理するテーブル（orders）
CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  amount DECIMAL(10, 2),
  CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
);

制約のオプション

外部キー制約を設定することで、親テーブルのデータが削除・更新されたときに、子テーブルのデータも自動で削除・更新されるようにオプションを設定できます。これにより、データの整合性が保たれ、リレーションシップが明確になります。以下のような指定方法があります。

• ON DELETE CASCADE：親のデータが削除されたとき、子のデータも削除される
• ON UPDATE CASCADE：親のキーが更新されたとき、子のキーも更新される
• ON DELETE SET NULL：親のデータが削除されたとき、子の外部キーをNULLに設定
• ON UPDATE SET NULL：親のキーが更新されたとき、子の外部キーをNULLに設定
• ON DELETE RESTRICT：親のデータが削除されるとき、子のデータが存在する場合は削除できない
• ON UPDATE RESTRICT：親のキーが更新されるとき、子のデータが存在する場合は更新できない

▼ 例

-- ユーザーが削除されたら、そのユーザーに関連する注文データも削除される
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  FOREIGN KEY (user_id)
    REFERENCES users(id)
    ON DELETE CASCADE    -- 親(user)が削除されたら、関連する注文も削除
    ON UPDATE CASCADE    -- 親(user)のIDが更新されたら、注文のuser_idも更新
);

INDEX：検索を高速化する

INDEXは、テーブルの特定の列に対して作成されるデータ構造で、検索やソートを高速化するために使用されます。インデックスを作成することで、特定の列に対する検索が効率的になり、パフォーマンスが向上します。

▼ 基本構文
CREATE 制約 INDEX インデックス名 ON テーブル名 (列名);

▼ 例

-- ユーザーテーブルの名前列にインデックスを作成
CREATE INDEX idx_users_name ON users (name);

-- ユーザーテーブルの名前列にインデックスを作作成
CREATE UNIQUE INDEX IF NOT EXISTS idx_users_name ON users (name);

▼ なぜIndexを指定すると検索が高速化されるのか？
もしインデックスがなければ、例えば、SELECT * FROM public.profiles WHERE username = 'Alice';はテーブルの全行を順番にチェックしながら、username が 'alice' の行を探します。これを 全表走査（フルテーブルスキャン） と呼び、行数が多いほど時間がかかり、効率が悪いです。
しかし、インデックスを指定すると、例えば、create index idx_profiles_username on public.profiles using btree (username)は、btree（バランス木）というデータ構造を使って、検索をかけています。btree（バランス木）は検索、挿入、削除が平均して高速にできるデータ構造となっています。

▼ 補足

• IF NOT EXISTS
  指定した名前のインデックスが既に存在する場合、エラーを発生させずに処理をスキップします。
  例：CREATE INDEX IF NOT EXISTS idx_users_name ON users (name);
• USING
  インデックスの種類を指定する。
  以下のよウナ種類があります。
  ・btree：B-treeインデックス（指定しない場合はデフォルトで使用される）
  ・hash：ハッシュインデックス（等価検索に最適）
  ・gin：Generalized Inverted Index（全文検索や配列に最適）
  ・gist：Generalized Search Tree（空間データや複雑なデータ型に最適）
  例：CREATE INDEX idx_users_name ON users USING btree (name);

その他

ANY / ALL

ANYとは、「配列やサブクエリの中のいずれか1つにマッチすればTRUE」となる演算子です。
ALLとは、「配列やサブクエリのすべてにマッチしなければTRUE」となる演算子です。
▼ 基本構文
SELECT 列名 FROM 表名 WHERE 列名 演算子 ANY|ALL (配列|サブクエリ);
▼ 例

-- ANYを使った例
SELECT * FROM users WHERE age > ANY (array[20, 30, 40]);
-- ALLを使った例
SELECT * FROM users WHERE age > ALL (array[20, 30, 40]);

• AS：列名・テーブル名に別名を付ける
  ▼ 基本構文
  SELECT 列名 AS 別名 FROM 表名 AS 別名;

▼ 例

-- name列をusernameとして表示
SELECT name AS username FROM users;

▼ 補足

• 複雑な計算やJOIN時の可読性を高めるためによく使う。
• ASは省略可能（列名 別名でも動作する）。

CAST：型変換を行う

▼ 基本構文
SELECT CAST(値 AS 型名);

▼ 例

-- 年齢を文字列に変換する
SELECT CAST(age AS CHAR) FROM users;

▼ 補足

• データ型を明示的に変換したい場合に使用。
• 型変換が必要な場合、::型名も使える。

ストアドプロシージャ

ストアドプロシージャは、データベース内に保存される一連のSQL文やロジックをまとめたものです。ストアドプロシージャを使用すると、複雑な処理を簡潔に実行でき、再利用性が高まります。

▼ 基本構文

CREATE PROCEDURE プロシージャ名 (引数1 型, 引数2 型, ...)
LANGUAGE 言語名
AS $$
BEGIN
   -- SQL文やロジック
END;
$$;

▼ 例

-- ユーザーを追加するストアドプロシージャ
CREATE PROCEDURE add_user(IN user_name VARCHAR(50), IN user_age INT)
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO users (name, age) VALUES (user_name, user_age);
END;
$$;

-- ストアドプロシージャを実行する
CALL add_user('Taro', 25);
-- ストアドプロシージャを実行する（引数を指定）
CALL add_user('Hanako', 30);
-- ストアドプロシージャを実行する（引数を省略）
CALL add_user();