📚 サンプル SQL
1. テーブル作成
CREATE TABLE users (id INTEGER, name TEXT, age INTEGER)
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)
CREATE TABLE events (id INTEGER, title TEXT, event_date DATE)
CREATE TABLE sales (id INTEGER, product TEXT, amount REAL, sale_date DATE)
2. データ挿入
INSERT INTO users (id, name, age) VALUES (1, 'Alice', 25)
INSERT INTO users (id, name, age) VALUES (2, 'Bob', 30)
INSERT INTO users (id, name, age) VALUES (3, 'Charlie', 22)
INSERT INTO users (id, name, age) VALUES (1, 'Alice', 25), (2, 'Bob', 30), (3, 'Charlie', 22)
INSERT INTO events (id, title, event_date) VALUES (1, 'Meeting', '2026-06-15')
INSERT INTO events (id, title, event_date) VALUES (2, 'Conference', '2026-07-01')
INSERT INTO events (id, title, event_date) VALUES (1, 'Meeting', '2026-06-15'), (2, 'Conference', '2026-07-01')
INSERT INTO sales (id, product, amount, sale_date) VALUES (1, 'Apple', 150.5, '2026-06-01'), (2, 'Banana', 80.0, '2026-06-02'), (3, 'Apple', 200.0, '2026-06-03'), (4, 'Orange', 120.0, '2026-06-04')
3. データ取得
SELECT * FROM users
SELECT name, age FROM users WHERE age > 23
SELECT * FROM users WHERE age >= 25 AND age <= 30
SELECT * FROM users WHERE name = 'Alice' OR age > 28
SELECT * FROM users WHERE name LIKE '%li%'
SELECT * FROM users WHERE name LIKE 'A%'
SELECT * FROM events
SELECT * FROM events WHERE event_date > '2026-06-20'
SELECT * FROM events WHERE title LIKE '%ing'
4. データ更新
UPDATE users SET age = 26 WHERE name = 'Alice'
UPDATE users SET age = 31, name = 'Robert' WHERE id = 2
5. データ削除
DELETE FROM users WHERE age < 25
DELETE FROM users WHERE name = 'Bob'
6. ORDER BY と LIMIT（ソートとページネーション）
SELECT * FROM users ORDER BY age
SELECT * FROM users ORDER BY age DESC
SELECT * FROM users ORDER BY age DESC, name ASC
SELECT * FROM users LIMIT 2
SELECT * FROM users ORDER BY age LIMIT 2
SELECT * FROM users ORDER BY age LIMIT 2 OFFSET 1
SELECT * FROM sales ORDER BY amount DESC LIMIT 3
7. 集約関数（COUNT, SUM, AVG, MIN, MAX）
SELECT COUNT(*) FROM users
SELECT COUNT(name) FROM users
SELECT AVG(age) FROM users
SELECT MIN(age), MAX(age) FROM users
SELECT SUM(amount) FROM sales
SELECT COUNT(*), AVG(amount), SUM(amount) FROM sales
SELECT COUNT(*) FROM users WHERE age > 25
8. GROUP BY と HAVING（グループ化とフィルタリング）
SELECT product, COUNT(*) FROM sales GROUP BY product
SELECT product, SUM(amount) FROM sales GROUP BY product
SELECT product, COUNT(*), AVG(amount) FROM sales GROUP BY product
SELECT product, SUM(amount) FROM sales GROUP BY product HAVING SUM(amount) > 200
SELECT product, COUNT(*) FROM sales GROUP BY product HAVING COUNT(*) > 1
SELECT product, SUM(amount) FROM sales GROUP BY product ORDER BY SUM(amount) DESC
SELECT product, AVG(amount) FROM sales WHERE amount > 100 GROUP BY product HAVING AVG(amount) > 150
9. PRIMARY KEY 制約
CREATE TABLE accounts (id INTEGER PRIMARY KEY, username TEXT, email TEXT)
INSERT INTO accounts (id, username, email) VALUES (1, 'alice', 'alice@example.com')
INSERT INTO accounts (id, username, email) VALUES (2, 'bob', 'bob@example.com')
UPDATE accounts SET email = 'alice.new@example.com' WHERE id = 1
SELECT * FROM accounts
10. カラムエイリアス（AS句）
SELECT name AS product_name, age AS user_age FROM users
SELECT COUNT(*) AS total FROM users
SELECT COUNT(*) AS count, AVG(age) AS average FROM users
SELECT product, COUNT(*) AS count, SUM(amount) AS total FROM sales GROUP BY product
SELECT product, AVG(amount) AS avg_amount FROM sales GROUP BY product HAVING AVG(amount) > 100
SELECT name AS user_name, age AS user_age FROM users ORDER BY user_age DESC
11. CREATE TABLE IF NOT EXISTS
CREATE TABLE IF NOT EXISTS users (id INTEGER, name TEXT, age INTEGER)
CREATE TABLE IF NOT EXISTS products (id INTEGER PRIMARY KEY, name TEXT, price REAL)
CREATE TABLE IF NOT EXISTS accounts (id INTEGER PRIMARY KEY, username TEXT, email TEXT)
12. DROP TABLE
DROP TABLE users
DROP TABLE IF EXISTS users
DROP TABLE IF EXISTS products
DROP TABLE IF EXISTS accounts
13. プレースホルダー（位置指定）
INSERT INTO users (id, name, age) VALUES (?, ?, ?)
SELECT * FROM users WHERE name = ?
SELECT * FROM users WHERE age > ? AND age < ?
UPDATE users SET age = ? WHERE name = ?
DELETE FROM users WHERE id = ?
14. プレースホルダー（名前付き）
INSERT INTO users (id, name, age) VALUES (:id, :name, :age)
SELECT * FROM users WHERE name = :name
SELECT * FROM users WHERE age > :minAge AND age < :maxAge
UPDATE users SET age = :age WHERE name = :name
DELETE FROM users WHERE id = :id
15. NOT NULL 制約
CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT)
INSERT INTO members (id, name, email) VALUES (1, 'Alice', 'alice@example.com')
INSERT INTO members (id, name, email) VALUES (2, 'Bob', NULL)
SELECT * FROM members
16. UNIQUE 制約
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, email TEXT UNIQUE)
INSERT INTO customers (id, name, email) VALUES (1, 'Alice', 'alice@example.com')
INSERT INTO customers (id, name, email) VALUES (2, 'Bob', 'bob@example.com')
INSERT INTO customers (id, name, email) VALUES (3, 'Charlie', NULL)
INSERT INTO customers (id, name, email) VALUES (4, 'Dave', NULL)
SELECT * FROM customers
17. DEFAULT 制約
CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT NOT NULL, status TEXT DEFAULT 'draft', created_at DATETIME DEFAULT CURRENT_TIMESTAMP)
INSERT INTO posts (id, title) VALUES (1, 'First Post')
INSERT INTO posts (id, title, status) VALUES (2, 'Second Post', 'published')
SELECT * FROM posts
18. ALTER TABLE ADD COLUMN
ALTER TABLE users ADD COLUMN email TEXT
ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active'
ALTER TABLE users ADD COLUMN created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
ALTER TABLE users ADD COLUMN phone TEXT UNIQUE
SELECT * FROM users
19. ALTER TABLE DROP COLUMN
ALTER TABLE users DROP COLUMN email
ALTER TABLE users DROP COLUMN status
SELECT * FROM users
20. JavaScript API（listTables / describeTable）