4. DML이 뭐고 어떻게 사용하나요? [ INSERT, UPDATE, DELETE ]

하루입니다.

 

오늘은 DML의 CREATE, UPDATE, DELETE문에 대해 알아보겠습니다.

 

---

 

DML(Data Manipulation Language)

 - 데이터 조작 언어이다.

 - 데이터베이스에서 데이터를 추가/조회/변경/삭제하는 작업을 수행한다.

 

INSERT	테이블에 새로운 데이터 행을 추가한다.
SELECT	테이블에서 데이터를 조회한다
UPDATE	테이블의 데이터를 변경한다
DELETE	테이블에 저장된 특정 데이터 행을 삭제한다

 

오늘 복습할 것은 INSERT, UPDATE, DELETE! 데이터를 건드리는 작업이라 제약조건을 지켜야 한다고 함. 무결성 위배라는 메세지도 많이 봤음. 조만간 배울 예정. SELECT는 정보를 조회만 하는 거니까 괜찮다.

 

 

1. INSERT : 데이터 추가

 INSERT는 테이블에 새로운 데이터 행을 추가하는 것이다. 실습을 위해 CREATE로 새로운 테이블을 만들었다. 

 

- 샘플 테이블 생성하기 -

• CREATE TABLE 명령어로 테이블을 생성한다.

 

테이블 생성 가이드

글로 쓰려고 했는데 가독성이 떨어져서 캡처본 첨부. 

 

 정리하자면 : NOT NULL이 없다면 NULL값 허용됨. NOT NULL 있으면 NULL값 비허용. DEFAULT 기본값은 값 지정되지 않았을 때 그 자리에 들어갈 값을 지정하는 것. 제약조건이 있다면 제약조건에 위배되는 값은 저장되지 않음. CONSTRAINT 제약조건별칭은 오류 발생시 오류메세지에 제약조건별칭 표시되며 오류원인 쉽게 파악할 수 있게 함.

참고로 CONSTRAINT는 강제라는 뜻임.

 

 제약조건 별칭 = 테이블명_컬럼명_제약조건

                       SAMPLE_PRODUCTS_NO_PK 

                       아래가 우리가 만든 제약조건 별칭. SAMPLE_PRODUCTS 테이블의 NO 컬럼, 제약조건은 PRIMARY KEY. 

 

• 테이블 생성했음

 

WOW 내가 해냄!

 

테이블 생성 가이드를 참조하며 보자. 

 - NO는 NUMBER타입이고 사이즈는 6이다. 제약조건이 지정되어 있다.

 - NAME은 VARCHAR2타입이고 사이즈는 255이다. NULL값이 허용되지 않는다. VARCHAR2가 뭐냐면 가변형 문자열 저장 데이터이다. CHAR과의 차이점은 CHAR(255)에 10자를 적는다면 245자는 공백으로 채워진다면(고정), VARCHAR2(255)는 10자 적으면 알아서 줄어든다(가변). 데이터 공간 확보에 좋다. 암튼 그렇습니다 전 처음 보는 개념이라 구글링 좀 했어용. 

 - COMPANY는 VARCHAR2타입이고 사이즈는 255이다. NULL값이 허용되지 않는다.

 - PRICE는 NUMBER타입이고 사이즈는 10이다. NULL이 허용되지 않는다.

 - DISCOUNT_PRICE는 NUMBER타입이고 사이즈는 10이다.

 - STOCK는 NUMBER타입이고 사이즈는 6이다. NULL이 허용되지 않는다.

 - STATUS는 VARCHAR2타입이고 사이즈는 100이다. 기본값으로 '판매중' 값을 갖는다.

 - CREATED_DATE는 DATE타입이다. DATE타입은 값을 지정할 필요가 없다. SYSDATE는 오라클의 내장함수이며, SYSDATE는 시스템의 현재 날짜와 시간정보를 제공하는 내장함수다. ( )가 없는 이유는 매개변수(값인가?)가 없어서이다. 매개변수가 1개라도 있으면 () 사용한다.

 

 

컨트롤 + 엔터를 누르니 테이블이 생겼다. 여기서 NULL값이 안 되는 애들은 회원가입 할 때 필수입력 생각하면 될 듯. 집전화는 필수가 아니지만 폰번호는 필수 입력(비워지면 안 되는 값)인 것처럼.

 

 

- 일련번호를 발행하는 시퀀스 객체 생성하기 - 

 

왜 갑자기 시퀀스 객체를 만드냐면 NO에 순서대로 숫자를 기입하기 위함임. NOCACHE를 사용하는 이유는 일련번호를 미리 생성하지 않게 하기 위함. 자기 혼자 20까지 뽑아 버리면 다음엔 내 의지와 관계 없이 21부터 시작해야 하니까.

 

• INSERT문에서 시퀀스의 일련번호를 발행받아서 저장하기

INSERT INTO SAMPLE_PRODUCTS (PRODUCT_NO, PRODUCT_NAME, ... )
VALUES (PRODUCT_SEQ.NEXTVA

 

• 상품번호로 사용할 일련번호 발행해 주는 시퀀스객체 발행하기

CREATE SEQUENCE PRODUCT_SEQ
INCREMENT BT 1 STARTS WITH 1001 NOCACHE;

이게 무슨 뜻일까요!

CREATE SEQUENCE PRODUCT_SEQ : PRODUCT_SEQ 라는 이름의 시퀀스 객체를 만든다.
INCREMENT BY 1 : 일련번호를 시작할 때마다(실행할 때마다?) 1씩 증가시킨다.

START WITH 1001 : 일련번호의 시작을 1001로 한다.

NOCACHE; : 일련번호를 미리 생성해두지 않는다.

 

 

- 테이블에 새로운 행 추가하기 - 

 자 드디어. INSERT를 사용해서 새로운 행 추가해 보겠습니다. 

 

• INSERT INTO ~ VALUE 명령어를 사용해서 테이블에 새로운 행을 추가할 수 있다.
• 행의 모든 컬럼에 값을 지정해서 새로운 행을 추가하기

INSERT INTO 테이블명 (컬럼명1, 컬럼명2, 컬럼명3, 컬럼명4, 컬럼명5, ... )
VALUE                (값,      값,      값,      값,      값, ...);

 

• 행의 모든 컬럼에 값을 지정해서 새로운 행을 추가하기
• VALUES 절에서 지정하는 값의 순서가 테이블의 컬럼 순서와 일치하고, 모든 컬럼에 빠짐없이 값을 지정하는 경우 INSERT INTO 절에서 컬럼명을 생략할 수 있다.
• 하지만 우리는 용이한 유지보수를 위해 컬럼명을 쓸 것입니다!

INSERT INTO 테이블명
VALUE                (값,    값,    값,    값,    값, ...);

 

• 행의 특정 컬럼에만 값을 지정해서 새로운 행을 추가하기
• 제외된 컬럼은 DEFAULT값이 지정되어 있으면 그 값이 컬럼에 저장된다. 
• DEFAULT값이 지정되어 있지 않으면 자동으로 NULL이 된다.
• 행의 특정 컬럼에만 값을 지정해서 새로운 행을 추가할 때는 INSERT INTO절에서 컬럼명을 생략할 수 없다. 

INSERT INTO 테이블명 (컬럼명2, 컬럼명3, 컬럼명4)
VALUES               (값,      값,      값);

 

실습 - NULL값이 가능한 테이블 : PRODUCT_DISCOUNT_PRICE, PRODUCT_STATUS

-- SAMPLE_PRODUCTS 테이블에 새 상품정보 저장하기
INSERT TO SAMPLE_PRODUCTS
(PRODUCT_NO, PRODUCT_NAME, PRODUCT_COMPANY, PRODUCT_PRICE, PRODUCT_DISCOUNT_PRICE, PRODUCT_STOCK)
VALUES
(1000, '애플워치SE', '애플', 350000, NULL, 10);

-- 시퀀스에서 발행받은 일련번호로 상품번호를 지정하고, 새 상품정보 저장하기
-- 앞서 PRODUCT_SEQ라는 시퀀스 객체 생성함.
INSERT INTO SAMPLE_PRODUCTS
(PRODUCT_NO, PRODUCT_NAME, PRODUCT_COMPANY, PRODUCT_PRICE, PRODUCT_STOCK)
VALUES
(PRODUCT_SEQ.NEXTVAL, '갤럭시22', '삼성전자', 1500000, 5);

-- 이걸 계속 저장하면 
1001, '갤럭시22', '삼성전자', 1500000, 5
1002, '갤럭시22', '삼성전자', 1500000, 5
1003, '갤럭시22', '삼성전자', 1500000, 5
...
이런 게 저장된다. 

-- NOT NULL 제약조건이 지정된 컬럼의 값을 누락시키고 새 상품정보를 저장하기
INSERT INTO SAMPLE_PRODUCTS
(PRODUCT_NO, PRODUCT_NAME, PRODUCT_COMPANY, PRODUCT_PRICE, PRODUCT_STOCK)
VALUES
(PRODUCT_SEQ.NEXTVAL, '갤럭시22', '삼성전자', 1500000);
--!! 오류 !! PRODUCT_STOCK은 NOT NULL인데 값이 비었다.

오류메세지
ORA-01400: NULL을 ("HR"."SAMPLE_PRODUCTS"."PRODUCT_STOCK") 안에 삽입할 수 없습니다

오류가 뜨는 이유 : STOCK은 NOT NULL 제약조건이다. NO-SEQ, NAME-갤럭시, COMPANY-삼성, PRICE-1500000으로 컬럼마다 값이 저장되나 STOCK는 값이 누락되었다. 그래서 NULL값이 STOCK값 안에 삽입될 수 없다고 오류가 뜨는 것이다.

 

 

~ 수많은 오류들의 향연 ~ 

선생님께서 테이블에 새로운 행을 추가할 때 체크할 내용을 정리해 주셨다.

  1. 컬럼명을 체크하자
  2. 데이터타입과 크기를 체크하자
  3. NOT NULL 허용여부를 체크하자. NULL이 허용되지 않는 컬럼은 반드시 값을 지정해야 한다.
  4. 컬럼별 제약조건을 체크하자.
  5. INSERT문 작성시 컬럼문을 반드시 작성하자. 
  6. 컬럼갯수와 값의 갯수가 일치하는지 체크하자.
  7. NULL 허용되지 않는 컬럼이 누락되엇는지 체크하자.
  8. PRIMARY KEY로 설정된 컬럼의 값으로 사용되는 시퀀스가 있는지 확인하자. 

 

솔직히 말하자면 현 상황에서는 오타만 안 나도 오류의 90%는 줄어들 듯 하다.

 

 

2. UPDATE : 데이터 변경

1. 테이블의 모든 행에서 지정된 컬럼의 값을 새로운 값으로 변경할 경우

UPDATE 테이블명
SET
    컬럼명 = 값,
    컬럼명 = 값,
    컬럼명 = 값;
    
-- WHERE절 없는 UPDATE문을 사용하는 경우는 드물다.

 

2. TRUE인 행에서만 지정된 컬럼의 값을 새로운 값으로 변경할 경우

UPDATE 테이블명
SET
    컬럼명 = 값,
    컬럼명 = 값,
    컬럼명 = 값				// 마지막은 , 생략
WHERE 조건식;

 

아래는 예시입니다.

-- SAMPLE_PRODUCTS 테이블의 모든 상품 재고를 100개로 변경하기
UPDATE SAMPLE_PRODUCTS
SET
    PRODUCT_STOCK = 100;


-- SAMPLE_PRODUCTS 테이블의 모든 상품 재고를 10개로 변경하고,
-- 상품의 할인가격을 상품가격에서 15% 할인된 가격으로 변경한다.
UPDATE SAMPLE_PRODUCTS
SET 
    PRODUCT_STOCK = 10;
    PRODUCT_DISCOUNT_PRICE = PRODUCT_PRICE*0.85;


-- '삼성전자'에서 제조한 상품을 재고량을 5개로 변경하기
UPDATE SAMPLE_PRODUCTS
SET
    PRODUCT_STOCK = 5
WHERE PRODUCT_NAME = '삼성전자';


-- 1010번 상품의 상품명, 가격, 할인가격, 재고량을 변경하기
-- 상품명: '갤럭시 S22 Ultra', 가격: 1900000, 할인가격: 1700000, 재고량: 20
UPDATE SAMPLE_PRODUCTS
SET
    PRODUCT_NAME = '갤럭시 S22 Ultra'
    PRODUCT_PRICE = 1900000
    PRODUCT_DISCOUNT_PRICE = 1700000
    PRODUCT_STOCK = 20
WHERE PRODUCT_NO = 1010;

 프라이머리키를 제약으로 걸면 한개만 바꿀 수 있다 -> 라고 하심. 아직 제약조건을 배우진 않았지만 프라이머리키라는게 NO처럼 한 값에 하나만 있는 거라면, 1010번(PK)만 바꿔! 이런 식으로 바꾸는 걸 말하는 거 아닐까? 라는 추측을 했다.

 

 

3. DELETE : 데이터 삭제

 1. 테이블에 저장된 모든 행을 삭제할 경우

DELETE FROM 테이블명;

-- WHERE절없이 DELETE문을 실행하면 테이블의 모든 행이 삭제되기 때문에 WHERE절 없이 사용되는 경우는 드물다.

 

 2.  WHERE절의 조건식이 TRUE로 판정되는 행만 삭제하기

DELETE FROM 테이블명
WHERE 조건식;

 

 

 위 캡처에서 갤럭시s22가 엄청나게 많다고 생각해 봅시다. 같은 값이 1026, 1027, 1028... 엄청나게 중복된 거죠. 그래서 저는 1026번 갤럭시s22 말고는 다 지우려고 합니다.

 

-- 상품명이 '갤럭시s22' 이고, 상품번호가 1026번이 아닌 상품 삭제하기
DELETE FROM SAMPLE_PRODUCTS
WHERE PRODUCT_NAME = '갤럭시s22'
AND PRODUCT_NO != 1026;

SAMPLE_PRODUCTS 에서 이름이 갤럭시s22고 번호가 1026이 아닌 것들을 삭제할 거야. 

 

COMMIT; 을 하면 SAMPLE_PRODUCTS 테이블에서 1026번의 갤럭시s22 말고는 모두 삭제된 것을 볼 수 있습니다.

 

---

 

 수업 들은 날 중 가장 단톡이 활발했던 날. 수많은 오류들의 향연. 반점 하나, 알파벳 하나, 따옴표 하나로 오류들이 발생할 때 나는 정말 ... 광광 울고 싶었던 거야. 하지만 참았지. 난 멋진 어른이니까. 사실 눈물 쪼금 났다. 어쩜 이리 유도리가 없는지. 조만간 SQL 더 알아 보고 세팅법 올려야겠다.

 

잘못된 정보에 대한 지적은 언제나 환영입니다.

 

이번 시간은 끝입니다. 안녕히 계세요.