1. INSERT문

• 테이블 안에 데이터를 삽입하는 명령어

1.1 문법

• INSERT INTO 테이블명 VALUES(v1,v2,...)
• INSERT INTO 테이블명(칼럼명1, 칼럼명2,..) VALUES(v1, v2,...)

1.2 예제

• 실습용 테이블 생성

CREATE TABLE LINK (
ID SERIAL PRIMARY KEY
, URL VARCHAR (255) NOT NULL
, NAME VARCHAR (255) NOT NULL
, DESCRIPTION VARCHAR (255)
, REL VARCHAR (50)
);

SELECT * FROM LINK;

• 1개의 데이터 행 추가

INSERT INTO LINK (URL, NAME)
VALUES ('http://naver.com','Naver');
COMMIT;

SELECT * FROM LINK;

• 작은따옴표를 데이터로 입력

INSERT INTO LINK(URL, NAME)
VALUES ('''http://naver.com''', '''Naver''');
COMMIT;

SELECT * FROM LINK;

• 여러 개의 데이터 행 추가

INSERT INTO LINK(URL, NAME)
VALUES ('http://www.google.com','Google')
     , ('http://www.yahoo.com' ,'Yahoo')
     , ('http://www.bing.com'  ,'Bing')
;
COMMIT;

SELECT * FROM LINK;

• 다른 테이블을 이용해 스키마만 있는 빈 껍데기 테이블 생성

CREATE TABLE LINK_TMP AS
SELECT * FROM LINK WHERE 0=1;
COMMIT

SELECT * FROM LINK_TMP

• 다른 테이블로부터 SELECT 하여 데이터 삽입

INSERT INTO LINK_TMP
SELECT * FROM LINK;
COMMIT;

SELECT * FROM LINK_TMP

2. UPDATE문

• 테이블 안에 데이터를 편집할 때 사용하는 명령어

2.1 문법

• UPDATE 테이블명 SET 컬럼명 = 값 WHERE 조건

2.2 예제

• name 이 'Naver'인 데이터의 description을 name 컬럼값으로 수정

UPDATE LINK SET DESCRIPTION = NAME
WHERE NAME = 'Naver'
COMMIT;

SELECT * FROM LINK

3. UPDATE JOIN 문

• 다른 테이블의 값을 참조하여 한 테이블의 값을 편집할 때 사용하는 명령어

3.1 문법

• UPDATE 테이블1 A SET A.컬럼 = 값 FROM 테이블2 B WHERE A.컬럼 = B.컬럼

3.2 예제

• 실습용 테이블 생성

-- PRODUCT_SEGMENT 테이블 생성 및 삽입
CREATE TABLE PRODUCT_SEGMENT
(
ID SERIAL PRIMARY KEY
, SEGMENT VARCHAR NOT NULL
, DISCOUNT NUMERIC (4, 2)
);

INSERT INTO PRODUCT_SEGMENT (SEGMENT, DISCOUNT)
VALUES
('Grand Luxury', 0.05)
, ('Luxury', 0.06)
, ('Mass', 0.1);
COMMIT;

-- PRODUCT 테이블 생성 및 삽입
CREATE TABLE PRODUCT(
ID SERIAL PRIMARY KEY
, NAME VARCHAR NOT NULL
, PRICE NUMERIC(10, 2)
, NET_PRICE NUMERIC(10, 2)
, SEGMENT_ID INT NOT NULL
, FOREIGN KEY(SEGMENT_ID)
REFERENCES PRODUCT_SEGMENT(ID)
);
NSERT INTO PRODUCT (NAME, PRICE, SEGMENT_ID)
VALUES
('K5', 804.89, 1)
, ('K7', 228.55, 3)
, ('K9', 366.45, 2)
, ('SONATA', 145.33, 3)
, ('SPARK', 551.77, 2)
, ('AVANTE', 261.58, 3)
, ('LOZTE', 519.62, 2)
, ('SANTAFE', 843.31, 1)
, ('TUSON', 254.18, 3)
, ('TRAX', 427.78, 2)
, ('ORANDO', 936.29, 1)
, ('RAY', 910.34, 1)
, ('MORNING', 208.33, 3)
, ('VERNA', 985.45, 1)
, ('K8', 841.26, 1)
, ('TICO', 896.38, 1)
, ('MATIZ', 575.74, 2)
, ('SPORTAGE', 530.64, 2)
, ('ACCENT', 892.43, 1)
, ('TOSCA', 161.71, 3);
COMMIT;

SELECT * FROM PRODUCT_SEGMENT;

SELECT * FROM PRODUCT;

• PRODUCT_SEGMENT의 값을 참조하여 NET_PRICE 계산

UPDATE PRODUCT A
SET NET_PRICE = A.PRICE - (A.PRICE * B.DISCOUNT)
FROM PRODUCT_SEGMENT B
WHERE A.SEGMENT_ID = B.ID;
COMMIT;
SELECT * FROM PRODUCT;

4. DELETE 문

• 테이블의 특정 데이터를 삭제할 때 사용

4.1 문법

• DELETE FROM 테이블 WHERE 조건

4.2 예제

• LINK 테이블

SELECT * FROM LINK;

• LINK_TMP 테이블

SELECT * FROM LINK_TMP;

• LINK 테이블에서 ID가 5인 행 삭제

DELETE FROM LINK WHERE ID = 5;
COMMIT;
SELECT * FROM LINK;

• 다른 테이블 값을 참조하여 삭제

DELETE FROM LINK_TMP A USING LINK B
WHERE A.ID = B.ID
COMMIT;
SELECT * FROM LINK_TMP;

5. UPSERT 문

• INSERT 시도 시에 조건에 따라 UPDATE를 할 수 있는 구문

5.1 문법

• INSERT INTO 테이블명(컬럼) VALUES(값) ON CONFLICT(타켓컬럼) 액션
• 액션 종류
  • DO NOTHING : 충돌 시 아무 것도 하지 말라
  • DO UPDATE SET ... : 충돌 시 업데이트 하라

5.2 예시

• 실습 테이블 생성(NAME 컬럼에 UNIQUE 제약 조건 설정)

CREATE TABLE CUSTOMERS
(
CUSTOMER_ID SERIAL PRIMARY KEY
, NAME VARCHAR UNIQUE
, EMAIL VARCHAR NOT NULL
, ACTIVE BOOL NOT NULL DEFAULT TRUE
);

INSERT INTO CUSTOMERS (NAME, EMAIL)
VALUES
('IBM', 'contact@ibm.com'),
('Microsoft', 'contact@microsoft.com'),
('Intel', 'contact@intel.com');
COMMIT;

SELECT * FROM CUSTOMERS;

• 이미 있는 NAME 값을 추가 시 아무것도 하지 않음

INSERT INTO CUSTOMERS (NAME, EMAIL)
VALUES
(
'Microsoft', 'hotline@microsoft.com'
)
ON CONFLICT (NAME) DO NOTHING;
COMMIT;

• 이미 있는 NAME 값을 추가 시 email 컬럼에 내용 추가
  • EXCLUDED.EMAIL 은 위에서 INSERT 시도한 EMAIL을 가리킴

INSERT INTO CUSTOMERS (NAME, EMAIL)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT (NAME) DO UPDATE
SET EMAIL = EXCLUDED.EMAIL || ';' || CUSTOMERS.EMAIL;

참고

• 모두를 위한 SQL/DB 올인원 패키지 Online(패스트 캠퍼스)