SQL

CREATE, INSERT

CREATE TABLE groceries (id INTEGER PRIMARY KEY, name TEXT, quantity INTEGER );

INSERT INTO groceries VALUES (1, "Bananas", 4);

postgresql에서 auto increment key 만들기: sequence 이용
https://semtax.tistory.com/15

create table users (id serial primary key, name text, email text)

explicit join

hobbies, persons 테이블이 있을때 explicit join

SELECT hobbies.name, persons.name FROM hobbies JOIN persons ON persons.id=hobbies.person_id; 

LEFT OUTER JOIN

LEFT OUTER JOIN : 두 테이블중 해당하는 항목이 없다해도 NULL 값을 넣어서 row 를 만들어 반환.
RIGHT 나 LEFT나 같은 결과를 만들수 있어서, LEFT 를 씀.
customers 테이블과 oreders 테이블이 있을때, 사용자가 총 얼마 썼는지 JOIN 으로 보고 내림차순 정렬.

name 기준으로 GROUP BY 하고, SUM(orders.prcie) 로 각 그룹당 얼마썼는지 합산.

SELECT customers.name, customers.email, SUM(orders.price) FROM customers LEFT OUTER JOIN orders ON customers.id = orders.customer_id GROUP BY name ORDER BY price DESC;

자기 자신과의 JOIN

alias(별칭) 부여해주기.
students 라는 테이블에 buddies 라는 별칭을 주고, buddies.email as buddy_email 로 불러오기.

SELECT students.first_name, students.last_name, buddies.email as buddy_email
    FROM students
    LEFT OUTER JOIN students buddies
    ON students.buddy_id = buddies.id;

JOIN 과 ON 여러번 하기

JOIN 과 ON은 여러번 쓸 수 있음. 하지만 JOIN 많이 쓰면 느려짐.
다음 코드는 다른 테이블끼리의 join과 students_projects 라는 테이블 자기자신과의 join 을 복합적으로해서 두개의 짝을 만들어내는 JOIN.

SELECT a.title, b.title FROM project_pairs
    JOIN student_projects a
    ON project_pairs.project1_id = a.id
    JOIN student_projects b
    ON project_pairs.project2_id = b.id;

persons 테이블과, 친구관계를 표시한 테이블이 있다고 하면
다음 코드로 친구들끼리 짝지어서 쿼리 가능

SELECT a.fullname, b.fullname FROM friends LEFT OUTER JOIN persons a ON a.id=friends.person1_id JOIN persons b ON b.id = friends.person2_id

UPDATE & DELETE

diary_logs 라는 테이블에서, content 라는 칼럼에서, id가 1인 부분을 업데이트하기

UPDATE diary_logs SET content = "I had a horrible fight with OhNoesGuy" WHERE id = 1;

id =1 인 행을 삭제하기.

DELETE FROM diary_logs WHERE id = 1;

삭제하기는 위험하니까 DELETE 문을 아예 안쓰기도 함. 대신 deleted 칼럼을 추가하고, 해당 항목의 true / false 를 바꾸는 식으로 구현.

이미 만들어진 테이블에 컬럼 추가하기(ALTER)

ALTER TABLE diary_logs ADD emotion TEXT default "unknown";

diary_logs 라는 테이블에 TEXT 타입을 갖는 emotion 컬럼을 추가했다. 이때 새로 생성되는 컬럼들에는 모두 default 값으로 "unknown"을 가진다(default 를 지정안하면 NULL 이 된다)

행이 아주많은 테이블에서 ALTER를 하면 느릴수 있음.

TRANSACTION 으로 여러 명령어 묶기

BEGIN TRANSACTION 과 COMMIT 사이에 명령어 작성.

BEGIN TRANSACTION;
UPDATE people SET husband = "Winston" WHERE user_id = 1;
UPDATE people SET wife = "Winnefer" WHERE user_id = 2;
COMMIT;

명령어중 하나라도 이상이 생기면 전부 처리안됨.

나쁜 업데이트/삭제 방지

다음과 같은 명령어를 쓰기 전에

UPDATE users SET deleted = true WHERE id = 1;

다음과 같이 먼저 테스트

SELECT id, deleted FROM users WHERE id = 1;

LIMIT 문을 써서 실수로 너무 많은 업데이트를 하는것 방지하기

UPDATE users SET deleted = true WHERE id = 1 LIMIT 1;