SQL CRUD

 

Create : INSERT

테이블에 새로운 행을 추가하는 SQL 명령어

INSERT INTO table_name (user_id, name, age)
VALUES(1, 'kim', 23);

INSERT INTO table_name -- 모든 칼럽에 넣는 경우 생략 가능. 단 칼럼 순서 정확히 일치해야함 안쓰는게 나음
VALUES (2, 'lee', 1000);

INSERT INTO table_name (user_id, name)-- 일부 칼럼만 넣는 경우 나머지는 NULL or default
VALUES (3, 'Jo'); -- NOT NULL 제약 없어야 가능

 

Read : SELECT

테이블에서 원하는 데이터를 조회하는 SQL 명령어

SELECT * FROM topic; -- topic 테이블 전체 가져오기
-- 조건 WHERE
SELECT id,title FROM topic; -- 모든 행의 is, title 가져오기
SELECT id,title FROM topic WHERE id = 1; -- id가 1 인 행
SELECT id,title FROM topic WHERE id <> 1; -- id가 1 이 아닌 행
SELECT id,title FROM topic WHERE id > 1; -- id가 1 보다 큰 행
-- ORDER BY 정렬
SELECT id,title FROM topic WHERE id > 1 ORDER BY id DESC; -- id가 1 보다 큰 행을 id 내림차순으로
SELECT id,title FROM topic WHERE id > 1 ORDER BY id ASC; -- id가 1 보다 큰 행을 id 오름차순으로
SELECT id,title FROM topic WHERE id > 1 ORDER BY id ASC LIMIT 2;
-- id가 1 보다 큰 행을 id 오름차순으로 상위 2개만
-- 여러 조건 AND OR NOT
SELECT id,title FROM topic WHERE id = 1 OR id = 3;
SELECT id,title FROM topic WHERE id > 1 AND id < 5;
SELECT id,title FROM topic WHERE NOT id = 1; -- NOT보다는 <> 로 더 많이 쓴다
-- NULL 비교
SELECT email, name FROM topic WHERE email IS NOT NULL;
SELECT email, name FROM topic WHERE email IS NULL;
-- LIKE / ILIKE
SELECT email, name FROM topic WHERE email LIKE '%@naver.com'; -- %가 와일드카드
SELECT email, name FROM topic WHERE name LIKE 'k_m'; -- _ : 한글자. kim, kam 다 가져옴
SELECT email, name FROM topic WHERE name ILIKE 'kim'; -- 대소문자 무시 KIM Kim kim 다 가져옴
-- '%kim%' -> 인덱스 못해서 풀스캔 성능 구림 '%kim'도 구림. 'kim%' 은 좋음.

WHERE 절의 조건 작성 순서는 일반적으로 성능에 큰 영향을 주지 않는다. DB 옵티마이저가 실행 순서를 최적화하기 때문이다.

그러니 인덱스를 활용하는 쿼리문과 잘 만든 DB 설계가 성능에 훨씬 더 중요하다.

 

예시의 마지막 쿼리 실행 결과

UPDATE

테이블에 이미 존재하는 행의 값을 수정하는 SQL 명령어

UPDATE topic SET title = 'Delete2', body = 'Delete2 is ...' WHERE id = 4;
UPDATE products SET price = price * 1.1; -- 기존값 기반 연산도 가능

UPDATE 테이블명 SET 컬럼1 = 값1, 컬럼2 = 값2 WHERE 조건;

-- JOIN 기반 UPDATE도 가능
-- other_table의 값 이용해서 users 테이블 수정하는 쿼리 u와 o는 별칭
-- where u.user_id = o.user_id 두 테이블 연결하는 조건. 사실상 JOIN조건
UPDATE users u
SET name = o.new_name
FROM other_table o
WHERE u.user_id = o.user_id;

만약 other_table에 동일 user_id가 여러개면 어떤 값이 적용될지 예측이 어렵다. (postgresql에서는 한 행이 선택되지만 보장 순서 없음) 그래서 보통 unique조건 보장 or GROUP BY/ DISTINCT 처리를 먼저 해줘야 안전하다

 

WHERE를 안쓰면 모든 데이터를 처리하니 큰일난다.

DISTINCT

중복된 값을 제거하는 키워드

SELECT DISTINCT name FROM users;

만약 users 테이블에 name컬럼에 kim이 2개 lee가 3개면

kim

lee

출력됨

SELECT DISTINCT name, emailaddress FROM users;

2개 이상의 칼럼을 쓰면 (name, emailaddress) 조합이 같은것만 제거함.

 

GROUP BY

같은 값을 가진 행들을 하나의 그룹으로 묶는 것 보통 집계함수랑 같이 쓰임.

SELECT name, COUNT(*) FROM users GROUP BY name;

만약 users 테이블에 name컬럼에 kim이 2개 lee가 3개면

kim 2

lee 3

출력됨

DELETE

테이블에서 행을 삭제하는 SQL 명령어

DELETE FROM topic WHERE id = 4; -- 선택된 행 삭제
TRUNCATE TABLE users; -- 테이블 전체 제거 where 불가 매우빠름

DELETE는 내부적으로 바로 지우는게 아니라 죽은 상태로 표시한다. 실제 디스크에서 정리되는건  VACUUM이 한다. (postgresql)

VACUUM

삭제되거나 업데이트되어 [죽은 행] 을 정리하는 작업

PostgreSQL은 MVCC(Multi Version Concurrency Control) 구조를 사용.

update는 기존 행 수정 x [새 행] 생성하고 기존 행은 [오래된 버전 행] 을 남김.

delete는 [죽은 행] 을 남김 (죽은 행과 오래된 버전행은 비슷한데 죽은 행은 논리적으로 완전히 죽은거라 어떤 트랜잭션에서도 보이면 안됨. 오래된 버전 행은 어떤 트랜잭션에서는 보일 수 있음 잠시 살아있는 과거 데이터임.)

VACUUM이 하는 일

[죽은 행]과 [오래된 버전행] 이 차지한 공간을 재사용 가능하게 만듦

테이블 통계 정보 갱신

트랜잭션 ID 정리 (엄청 중요)

VACUUM users; -- 공간을 재사용 가능하게 함. 파일 크기 줄어들지 않을수도 있음.

VACUUM FULL users; --실제 파일 크기 줄임, 테이블 Lock 발생, 매우 무거운 작업

 

AUTOVACUUM (자동)

postgresql은 기본적으로 자동으로 VACUUM 돌려줌.

제거된 id=4 줄

WHERE를 안쓰면 모든 데이터를 처리하니 큰일난다.