DB 개념 · SQL 기초 · SQL 심화

애플리케이션 → DBMS → 데이터베이스(파일/디스크)

[users 테이블]
id | name   | email
1  | 김철수 | kim@example.com
2  | 이영희 | lee@example.com

[orders 테이블]
id | user_id | product | price
1  | 1       | 노트북  | 1500000
2  | 1       | 마우스  | 30000

// MongoDB 문서
{
  "_id": "64abc123",
  "name": "김철수",
  "orders": [
    { "product": "노트북", "price": 1500000 },
    { "product": "마우스", "price": 30000 }
  ]
}

SELECT name, email FROM users WHERE id = 1;
INSERT INTO users (name, email) VALUES ('김철수', 'kim@example.com');
UPDATE users SET email = 'new@example.com' WHERE id = 1;
DELETE FROM users WHERE id = 1;

my-project/
├── app.js
├── database.db   ← 이 파일이 전체 DB
└── package.json

# macOS
brew install --cask db-browser-for-sqlite

# Ubuntu
sudo apt-get install sqlitebrowser

# Windows: https://sqlitebrowser.org/dl/

CREATE TABLE books (
    id        INTEGER PRIMARY KEY AUTOINCREMENT,
    title     TEXT    NOT NULL,
    author    TEXT    NOT NULL,
    price     INTEGER DEFAULT 0,
    isbn      TEXT    UNIQUE,
    published TEXT
);

-- 모든 컬럼에 값
INSERT INTO books VALUES (1, '클린 코드', '로버트 마틴', 30000, '978-…', '2008-08-01');

-- 컬럼 명시 (권장)
INSERT INTO books (title, author, price) VALUES ('리팩터링', '마틴 파울러', 35000);

-- 여러 행 동시
INSERT INTO books (title, author, price) VALUES
    ('자바스크립트 완벽 가이드', '데이비드 플래너건', 45000),
    ('파이썬 입문', '귀도 반 로썸', 28000);

SELECT * FROM books;
SELECT title, author, price FROM books;

SELECT * FROM books WHERE price >= 30000;
SELECT * FROM books WHERE author = '마틴 파울러';
SELECT * FROM books WHERE price >= 20000 AND price <= 40000;
SELECT * FROM books WHERE title LIKE '%자바%';
SELECT * FROM books WHERE isbn IS NULL;

SELECT * FROM books ORDER BY price ASC;
SELECT * FROM books ORDER BY price DESC;
SELECT * FROM books ORDER BY author ASC, price DESC;

SELECT * FROM books LIMIT 5;
SELECT * FROM books LIMIT 5 OFFSET 5;  -- 6번째부터 5개

UPDATE books SET price = 32000 WHERE id = 1;
UPDATE books SET price = 32000, title = '클린 코드 2판' WHERE id = 1;

DELETE FROM books WHERE id = 3;
DELETE FROM books WHERE price < 10000;

sqlite3 library.db
.read 예제/queries.sql
.tables
.schema books
.quit

SELECT users.name, orders.total_price, orders.status
FROM orders
INNER JOIN users ON orders.user_id = users.id;

-- 주문 없는 회원도 포함
SELECT users.name, orders.total_price
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

SELECT
    u.name AS 회원명, p.name AS 상품명,
    oi.quantity AS 수량, oi.unit_price AS 단가
FROM order_items oi
INNER JOIN orders   o ON oi.order_id   = o.id
INNER JOIN users    u ON o.user_id     = u.id
INNER JOIN products p ON oi.product_id = p.id;

-- 카테고리별 상품 수
SELECT category, COUNT(*) AS 상품수
FROM products
GROUP BY category;

-- 카테고리별 평균 가격
SELECT category, AVG(price) AS 평균가격
FROM products
GROUP BY category;

-- 주문 2건 이상 회원만
SELECT user_id, COUNT(*) AS 주문수
FROM orders
GROUP BY user_id
HAVING COUNT(*) >= 2;

-- 두 절 함께
SELECT category, AVG(price) AS 평균가격
FROM products
WHERE stock > 0
GROUP BY category
HAVING AVG(price) > 50000;

-- 평균보다 비싼 상품
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- 특정 회원이 주문한 상품 이름
SELECT name FROM products WHERE id IN (
    SELECT product_id FROM order_items WHERE order_id IN (
        SELECT id FROM orders WHERE user_id = 1
    )
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_products_category ON products(category);
CREATE INDEX idx_order_items_op ON order_items(order_id, product_id);

DROP INDEX idx_orders_user_id;

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE user_id = 1;
-- SEARCH TABLE orders USING INDEX idx_orders_user_id ← 인덱스 사용
-- SCAN TABLE orders                                 ← 인덱스 없음 (느림)