SELECT クエリ

SQL

基本的なデータ取得

基本的な SELECT

SELECT、WHERE、ORDER BY、LIMIT

-- 全列取得
SELECT * FROM users;

-- 特定列
SELECT id, name, email FROM users;

-- エイリアス
SELECT id, name AS user_name, email AS mail FROM users;

-- 条件絞り込み
SELECT * FROM users
WHERE age >= 18
  AND active = true
  AND role IN ('admin', 'user')
  AND name LIKE 'Alice%'   -- 前方一致
  AND name LIKE '%alice%'  -- 部分一致
  AND email IS NOT NULL;

-- 並べ替え
SELECT * FROM users
ORDER BY age DESC, name ASC;

-- 取得件数の制限
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20; -- ページ2（21〜30件目）

-- 重複除外
SELECT DISTINCT country FROM users;

WHERE 条件の演算子

比較、論理、LIKE、BETWEEN、IN

-- 比較演算子
WHERE price = 100
WHERE price != 100  -- または <>
WHERE price > 100
WHERE price <= 100

-- 論理演算子
WHERE age >= 18 AND age <= 65
WHERE role = 'admin' OR role = 'superadmin'
WHERE NOT active

-- BETWEEN（両端含む）
WHERE price BETWEEN 100 AND 500
WHERE created_at BETWEEN '2025-01-01' AND '2025-12-31'

-- IN
WHERE country IN ('JP', 'US', 'GB')
WHERE id NOT IN (1, 2, 3)
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000)

-- LIKE
WHERE name LIKE 'A%'       -- A で始まる
WHERE name LIKE '%son'     -- son で終わる
WHERE name LIKE '%li%'     -- li を含む
WHERE code LIKE '_BC_'     -- 任意の1文字

-- NULL チェック
WHERE deleted_at IS NULL
WHERE deleted_at IS NOT NULL
WHERE COALESCE(nickname, name) = 'Alice' -- NULLならnameを使用

JOIN