PostgreSQL入門 — 基本SQL完全ガイド

# データベース一覧
\l

# 接続先を切り替え
\c mydb

# テーブル一覧
\dt

# 特定テーブルのカラム情報
\d users

-- 現在のデータベース内のテーブル一覧
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
  AND table_type = 'BASE TABLE'
ORDER BY table_name;

-- usersテーブルのカラム情報を確認
SELECT
  column_name,
  data_type,
  is_nullable,
  column_default
FROM information_schema.columns
WHERE table_name = 'users'
ORDER BY ordinal_position;

-- ユーザーテーブル
CREATE TABLE users (
  id         SERIAL PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  email      VARCHAR(255) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT NOW()
);

-- 注文テーブル
CREATE TABLE orders (
  id         SERIAL PRIMARY KEY,
  user_id    INTEGER REFERENCES users(id),
  item_name  VARCHAR(200) NOT NULL,
  price      INTEGER NOT NULL,
  ordered_at TIMESTAMP DEFAULT NOW()
);

-- 全カラム・全件取得
SELECT * FROM users;

-- 特定カラムのみ取得
SELECT id, name, email FROM users;

-- idが3のユーザーを取得
SELECT * FROM users WHERE id = 3;

-- nameが「田中」を含むユーザー（部分一致）
SELECT * FROM users WHERE name LIKE '%田中%';

-- 複数条件（AND）
SELECT * FROM users
WHERE email LIKE '%@example.com'
  AND created_at >= '2026-01-01';

-- 複数条件（OR）
SELECT * FROM users
WHERE name = '田中' OR name = '山田';

-- NULLの確認（= NULLは使えない）
SELECT * FROM orders WHERE user_id IS NULL;

-- 新しい順（降順）
SELECT * FROM orders ORDER BY ordered_at DESC;

-- 名前のアルファベット順（昇順・デフォルト）
SELECT * FROM users ORDER BY name ASC;

-- 複数条件で並び替え
SELECT * FROM orders ORDER BY price DESC, ordered_at ASC;

-- 最初の10件だけ取得
SELECT * FROM users ORDER BY id LIMIT 10;

-- 11件目から20件目（ページネーション）
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10;

-- ユーザーごとの注文数を集計
SELECT
  user_id,
  COUNT(*) AS order_count,
  SUM(price) AS total_price,
  AVG(price) AS avg_price
FROM orders
GROUP BY user_id
ORDER BY total_price DESC;

-- 注文が2件以上のユーザーだけ表示
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) >= 2;

SELECT
  u.id   AS user_id,
  u.name,
  o.item_name,
  o.price
FROM users AS u
INNER JOIN orders AS o ON u.id = o.user_id;

users テーブル          orders テーブル
┌────┬──────┐          ┌────┬─────────┬──────────┐
│ id │ name │          │ id │ user_id │item_name │
├────┼──────┤    JOIN  ├────┼─────────┼──────────┤
│  1 │ 田中 │ ◄──────► │  1 │    1    │ 商品A    │
│  2 │ 山田 │ ◄──────► │  2 │    1    │ 商品B    │
│  3 │ 佐藤 │   (なし) │  3 │    2    │ 商品C    │
└────┴──────┘          └────┴─────────┴──────────┘

結果：田中・山田のみ（佐藤は注文なしで除外）

SELECT
  u.id   AS user_id,
  u.name,
  o.item_name,
  o.price
FROM users AS u
LEFT JOIN orders AS o ON u.id = o.user_id;

-- 一度も注文したことがないユーザーを抽出
SELECT u.id, u.name
FROM users AS u
LEFT JOIN orders AS o ON u.id = o.user_id
WHERE o.id IS NULL;

-- usersとordersとproductsを結合する例（productsテーブルが別にある場合）
SELECT
  u.name,
  o.ordered_at,
  p.product_name,
  o.price
FROM users AS u
INNER JOIN orders AS o ON u.id = o.user_id
INNER JOIN products AS p ON o.product_id = p.id;

-- 1件追加
INSERT INTO users (name, email)
VALUES ('田中太郎', 'taro@example.com');

-- 複数件まとめて追加
INSERT INTO users (name, email) VALUES
  ('山田花子', 'hanako@example.com'),
  ('佐藤次郎', 'jiro@example.com');

-- 追加したIDを返す（PostgreSQL固有）
INSERT INTO users (name, email)
VALUES ('鈴木一郎', 'ichiro@example.com')
RETURNING id, name;

-- 特定レコードのメールを更新
UPDATE users
SET email = 'new-taro@example.com'
WHERE id = 1;

-- 複数カラムを同時更新
UPDATE users
SET
  name  = '田中 太郎',
  email = 'taro2@example.com'
WHERE id = 1;

-- 特定レコードを削除
DELETE FROM users WHERE id = 3;

-- 条件に合う複数件を削除
DELETE FROM orders WHERE ordered_at < '2025-01-01';

-- ordersテーブルに1件追加（実データが書き込まれる）
INSERT INTO orders (user_id, item_name, price) VALUES (1, '商品A', 3000);

CREATE VIEW order_summary AS
SELECT
  o.id,
  u.name AS user_name,
  o.item_name,
  o.price
FROM orders AS o
INNER JOIN users AS u ON o.user_id = u.id;

-- ビューへのSELECTは、定義したJOINクエリに展開されて実行される
SELECT o.id, u.name AS user_name, o.item_name, o.price
FROM orders AS o
INNER JOIN users AS u ON o.user_id = u.id;

CREATE MATERIALIZED VIEW user_order_stats AS
SELECT
  u.id,
  u.name,
  COUNT(o.id)  AS order_count,
  SUM(o.price) AS total_price
FROM users AS u
LEFT JOIN orders AS o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- このコマンドを実行した瞬間だけ最新データに更新される
REFRESH MATERIALIZED VIEW user_order_stats;

-- ordersに新しい注文を1件追加した
INSERT INTO orders (user_id, item_name, price) VALUES (1, '新商品', 9000);

REFRESH MATERIALIZED VIEW user_order_stats;
-- これを実行して初めて新しい注文が集計に含まれる

-- テーブル一覧
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public' AND table_type = 'BASE TABLE';

-- カラム一覧
SELECT column_name, data_type, is_nullable
FROM information_schema.columns WHERE table_name = 'users';

-- 全件取得
SELECT * FROM users;

-- 条件絞り込み
SELECT * FROM users WHERE id = 1;

-- 並び替え・件数制限
SELECT * FROM orders ORDER BY price DESC LIMIT 10;

-- 集計
SELECT user_id, COUNT(*), SUM(price) FROM orders GROUP BY user_id;

-- INNER JOIN
SELECT u.name, o.item_name FROM users u INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN
SELECT u.name, o.item_name FROM users u LEFT JOIN orders o ON u.id = o.user_id;

-- INSERT
INSERT INTO users (name, email) VALUES ('田中', 'taro@example.com') RETURNING id;

-- UPDATE
UPDATE users SET email = 'new@example.com' WHERE id = 1;

-- DELETE
DELETE FROM users WHERE id = 1;