SQL 기본

2019-06-10


테이블 만들기

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, age INTEGER, weight REAL);

데이터 삽입

INSERT INTO customers VALUES (73, "Brian", 33);

데이터 쿼리

전체 선택

SELECT * FROM customers;

다중 조건으로 필터링

SELECT * FROM customers WHERE age < 21 AND state = "NY";

IN으로 필터링

SELECT * FROM customers WHERE plan IN ("free", "basic");

특정 열 선택

SELECT name, age FROM customers;

결과 정렬

SELECT * FROM customers WHERE age > 21 ORDER BY age DESC;

CASE로 변환

SELECT name, CASE WHEN age > 18 THEN "adult" 
ELSE "minor" END "type" FROM customers;

LIMIT으로 제한

SELECT * FROM top_programs ORDER BY votes DESC LIMIT 3;

LIKE로 필터링

/* which ones have floral patterns? */
SELECT * FROM dresses WHERE description LIKE "%floral%";

데이터 집계

집계 함수

SELECT MAX(age) FROM customers;

데이터 그룹화

SELECT gender, COUNT(*) FROM students GROUP BY gender;

관련 테이블에 조인

Inner Join

SELECT customers.name, orders.item FROM customers JOIN orders ON customers.id = orders.customer_id;

Outer join

SELECT customers.name, orders.item FROM customers LEFT OUTER JOIN orders ON customers.id = orders.customer_id;

데이터 업데이트 및 삭제

데이터 업데이트

UPDATE customers SET age = 33 WHERE id = 73;

데이터 삭제

DELETE FROM customers WHERE id = 73;