[SQLD] 2과목 SQL 기본 및 활용 (PART3 관리 구문) 요약 정리

DML(Data Manipulation Language): DDL에서 정의한 대로 데이터를 입력하고, 입력된 데이터를 수정, 삭제, 조회하는 명령어

• INSERT: 테이블에 데이터를 입력하는 명령어

INSERT INTO 테이블명 (컬럼명1, 컬럼명2 ...) VALUES (데이터1, 데이터2 ...);

 

INSERT INTO 테이블명 VALUES (전체 컬럼에 입력될 데이터 리스트);

​

Q. 다음 SQL의 결과?

[SQL]
INSERT INTO (COL2, COL3) VALUES ('S', 'A');

[SAMPLE 테이블]

COL1

COL2 COL3 DEFAULT 'D'

[결과] 에러가 발생한다.

​

<해설> PK(Primary Key)는 NOT NULL 조건이기 때문에 반드시 데이터가 입력되어야 하지만 주어진 SQL에서는 COL1이 명시적으로 정의되어 있지 않기 때문에 "ORA-01400: NULL을 (SAMPLE.COL1) 안에 삽입할 수 없습니다."라는 에러가 발생

​

​

Q. 다음 중 정상적으로 데이터가 입력되는 SQL?

CREATE TABLE SAMPLE (
   COL1 VARCHAR(1) PRIMARY KEY,
   COL2 NUMBER NOT NULL,
   COL3 VARCHAR(3)
);

1. INSERT INTO SAMPLE ('1', 1, 'ABC'); -> VALUES가 누락됨
2. INSERT INTO SAMPLE (COL1, COL2) VALUES ('1', 2);
3. INSERT INTO SAMPLE VALUES ('A', 1, 'ABCDE'); -> 'ABCDE'는 3byte가 넘음
4. INSERT INTO SAMPLE (COL1, COL3) VALUES ('A', 'ABC'); -> COL2는 NOT NULL 조건이므로 반드시 입력되어야 함

​

​

• UPDATE: 이미 저장된 데이터를 수정하고 싶을 때 사용. 수정하고 싶은 컬럼이 많다면 SET 절에 , 로 이어서 명시해줄 수 있음(SET 컬럼명1 = 데이터, 컬럼명2 = 데이터...), WHERE 절이 없으면 테이블의 모든 Row가 변경되니 주의

UPDATE 테이블명 SET 컬럼명 = 새로운 데이터 (WHERE 수정할 데이터에 대한 조건);

 

• DELETE: 이미 저장된 데이터를 삭제하고 싶을 때 사용. WHERE 절이 없으면 테이블의 모든 Row가 삭제되니 주의. 만약 WHERE 절 없이 정말 테이블 전체 데이터를 삭제하고자 하는 경우 TRUNCATE를 쓰는 게 시스템 부하 측면에서 유리. 대신 TRUNCATE는 별도의 로그를 쌓지 않아 ROLLBACK이 불가능하며 DELETE는 COMMIT 전에 ROLLBACK이 가능

DELETE FROM 테이블명 (WHERE 수정할 데이터에 대한 조건);

 

​

Q. 다음 SQL 결과?

[SQL]
DELETE FROM SAMPLE1 A // SAMPLE1 테이블을 대상으로 하고, 테이블에 대한 별칭으로 A를 사용
  WHERE NOT EXISTS (SELECT 1 FROM SAMPLE2 B // SAMPLE2 테이블에서 데이터를 가져와서 SAMPLE1 테이블과 조건을 비교
                    WHERE A.COL3 = B.COL1); // SAMPLE1 테이블의 COL3 값이 SAMPLE2 테이블의 COL1 값과 일치하지 않는 행이 삭제

[SAMPLE1 테이블]

COL1	COL2	COL3
1	가	A
2	나	B
3	다	C

[SAMPLE2 테이블]

COL1	COL2
B	10
A	12
B	11

[결과]

COL1	COL2	COL3
1	가	A
2	나	B

<해설> NOT EXISTS 구문을 이용하여 SAMPLE1 테이블의 COL3 데이터가 SAMPLE2 테이블의 COL1 컬럼에 존재하지 않으면 삭제하라고 했기 때문에 COL3이 'C'인 행만 삭제됨

​

​

💡TIP

INSERT, UPDATE, DELETE 명령어를 날리고 별도의 COMMIT 명령어를 실행시켜 주어야 데이터가 반영되며 ROLLBACK도 가능. SQL Server(MSSQL)의 경우 DML도 AUTO COMMIT 됨

​

​

• MERGE: 데이터에 새로운 데이터를 입력하거나 이미 저장되어 있는 데이터에 대한 변경 작업을 한번에 할 수 있도록 해줌

​

ex) DEPARTMENTS 테이블과 여기 저장된 데이터를 백업해주는 DEPARTMENT_BACKUP 테이블이 있다고 가정. DEPARTMENTS_BACKUP 테이블은 지속적으로 DEPARTMENTS 테이블과 데이터 동기화를 시켜줘야 함. MERGE 문을 이용하여 DEPARTMENTS 테이블의 데이터를 DEPARTMENTS_BACKUP 테이블에 반영해보자.

MERGE
  INTO DEPARTMENTS_BACKUP DB
USING DEPARTMENTS D
   ON (DB.DEPARTMENT_ID = D.DEPARTMENT_ID)
 WHEN MATCHED THEN
      UPDATE
        SET DB.DEPARTMENT_NAME = D.DEPARTMENT_NAME,
            DB.MANAGER_ID = D.MANAGER_ID,
            DB.LOCATION_ID = D.LOCATION_ID
 WHEN NOT MATCHED THEN
      INSERT (DB.DEPARTMENT_ID, DB.DEPARTMENT_NAME, DB.MANAGER_ID, DB.LOCATION_ID)
      VALUES (D.DEPARTMENT_ID, D.DEPARTMENT_NAME, D.MANAGER_ID, D.LOCATION_ID);

이 쿼리를 항목별로 해석해보면

1. INTO DEPARTMENTS_BACKUP DB: DEPARTMENT_BACKUP 테이블의 데이터를 변경 또는 생성해라
2. USING DEPARTMENTS D: 변경 또는 생성할 때 DEPARTMENTS 테이블을 이용해라
3. ON (DB.DEPARTMENT_ID = D.DEPARTMENT_ID): DEPARTMENTS_BACKUP 테이블에 DEPARTMENTS의 DEPARTMENT_ID와 동일한 값이 있는지가 조건
4. WHERE MATCHED THEN UPDATE ~: 조건에 맞는 데이터가 있으면 그 데이터를 변경해라
5. WHEN NOT MATCHED THEN INSERT ~: 조건에 마는 데이터가 없으면 그 데이터를 생성해라

​

​

Q. 다음 SQL의 결과 SAMPLE1 테이블의 건수는 몇 건?

[SQL]
MERGE INTO SAMPLE1 S1
 USING SAMPLE2 S2
    ON (S1.COL1 = S2.COL1)
WHEN MATCHED THEN
     UPDATE SET S1.COL2 = '라'
      WHERE S1.COL3 <> 'C'
     DELETE WHERE S1.COL2 = '라'
WHEN NOT MATCHED THEN
     INSERT (S1.COL1, S1.COL2, S1.COL3)
     VALUES (S2.COL1, S2.COL2, S2.COL3);

[SAMPLE1 테이블]

COL1	COL2	COL3
1	가	A
2	나	B
3	다	C

[SAMPLE2 테이블]

COL1	COL2	COL3
1	가	A
2	나	B
3	다	C
4	라	D
5	마	E

[답] 3

​

<해설> SAMPLE2 테이블을 이용하여 SAMPLE1 테이블의 데이터를 UPDATE, DELETE 및 INSERT 하는 쿼리이다. 먼저,

WHEN MATCHED THEN
     UPDATE SET S1.COL2 = '라'
      WHERE S1.COL3 <> 'C'
     DELETE WHERE S1.COL2 = '라'

위 구문으로 인해 다음과 같이 COL3 <> 'C'인 행들의 COL2가 '라'로 변경되었고, 그 뒤 COL2 = '라'인 행들이 삭제됨

COL1	COL2	COL3
1	라	A
2	라	B
3	다	C

WHEN NOT MATCHED THEN
     INSERT (S1.COL1, S1.COL2, S1.COL3)
     VALUES (S2.COL1, S2.COL2, S2.COL3);

그리고 위 구문으로 인해 아래 두 행이 삽입되었으므로

COL1	COL2	COL3
4	라	D
5	마	E

결론적으로 아래의 데이터들이 남게 됨

COL1	COL2	COL3
3	다	C
4	라	D
5	마	E

​

​

TCL(Transaction Control Language): TCL은 트랜잭션을 제어하는 명령어로 COMMIT, ROLLBACK, SAVEPOINT가 있음

• 트랜잭션의 특징

원자성 (Atomicity)	트랜잭션으로 묶인 일련의 동작들은 모두 성공하거나 모두 실패. 즉, 살아도 같이 살고 죽어도 같이 죽는 관계 (All or Nothing)
일관성 (Consistency)	트랜잭션이 완료된 후에도 데이터베이스가 가진 데이터에 일관성이 있어야 함. 예를 들어 이미 결제된 티셔츠의 수량과 남아있는 티셔츠 재고의 합은 언제나 쇼핑몰이 처음 보유하고 있었던 티셔츠의 총 수량과 일치
고립성 (Isolation)	하나의 트랜잭션은 고립되어 수행되어야 함. 만약 내가 구매하고자 하는 티셔츠를 지금 다른 사람이 먼저 구매하고 있다면 나는 재고 데이터를 참조하거나 변경할 수 없고 그 사람의 트랜잭션이 끝날 때까지 대기해야 함
지속성 (Durability)	트랜잭션이 성공적으로 수행되었을 경우 트랜잭션이 변경한 데이터가 영구적으로 저장되어야 함. 쉽게 말해 모든 트랜잭션이 로그에 남겨진 뒤 COMMIT 되어야 하고, 그래서 시스템 장애가 발생하더라도 복구 가능해야 한다는 의미

• COMMIT: INSERT, DELETE, UPDATE 후 변경된 내용을 확정, 반영
• ROLLBACK: INSERT, DELETE, UPDATE 후 변경된 내용을 취소. ROLLBACK을 하면 변경하기 이전 값으로 복구됨
• SAVEPOINT: ROLLBACK을 수행할 때 전체 작업을 되돌리지 않고 일부만 되돌릴 수 있게 하는 기능

​

​

DDL(Data Definition Language): 데이터를 정의하는 명령어로 CREATE, ALTER, DROP, RENAME, TRUNCATE가 있다.

​

• CREATE: 테이블을 생성하기 위한 명령어

CREATE TABLE 테이블명 (
   컬럼명1   데이터타입 (DEFAULT / NULL 여부),
   ...
);

 

💡 TIP

• NULL: 공백(' ')과는 다르며 존재하지 않는 값이란 의미
• DEFAULT: 데이터의 기본값을 의미. DEL_YN(삭제여부) 컬럼의 DEFAULT를 'N'으로 정의해 놓으면 DEL_YN 값을 별도로 명시하지 않았을 때 NULL 대신 'N'이 저장

​

테이블 생성 시 반드시 지켜야 할 규칙

• 테이블명은 고유해야 한다.
• 한 테이블내에서 컬럼명은 고유해야 한다.
• 컬럼명 뒤에 데이터 유형과 데이터의 크기가 명시되어야 한다.
• 컬럼에 대한 정의는 괄호 ( ) 안에 기술한다.
• 각 컬럼들은 , (콤마)로 구분된다.
• 테이블명과 컬럼명은 숫자로 시작될 수 없다.
• 마지막은 ; (세미콜론)으로 끝난다.

​

에러를 발생시키지는 않지만 지키지 않으면 매우 피곤해지는 항목

• 테이블은 각각 정체성을 나타내는 이름을 가져야 한다. TMSE3과 같은 정체 모를 이름의 테이블과 마주하게 되면 퍽 난감해진다.
• 컬럼명을 정의할 때는 다른 테이블과 통일성이 있어야 한다. 같은 데이터를 저장하는 컬럼이 A 테이블에서는 COSTIMER_ID이고 B 테이블에서는 MEMBER_ID이면 이것은 정~말 아름답지 못하다.

​

CREATE TABLE을 할 때 제약조건(CONSTRAINT)도 함께 정의해줄 수 있는데 제약조건은 테이블에 저장될 무결성, 즉 데이터의 정확성과 일관성을 유지하고, 데이터에 결손과 부정합이 없음을 보증하기 위해 해놓는 장치

​

제약조건의 종류

• PRIMARY KEY(기본키): 테이블에 저장된 각각의 Row에 대한 고유성을 보장. 한 테이블에 하나씩만 정의할 수 있으며 PK(PRIMARY KEY)로 지정된 컬럼에는 NULL 값이 입력될 수 없고 자동으로 UNIQUE 인덱스로 생성됨
• UNIQUE KEY(고유키): PRIMARY KEY와 유사하게 테이블에 저장된 각각의 Row에 대한 고유성을 보장하기 위한 제약조건이지만 NULL 값이 허용된다는 차이점이 있다.
• NOT NULL: 해당 컬럼이 NULL 값이 입력되는 것을 허용하지 않는 제약조건
• CHECK: 컬럼에 저장될 수 있는 값의 범위를 제한 ex) CONSTRAINT CHK_DEL_YN CHECK(DEL_YN IN('Y', 'N')) -> DEL_YN(삭제여부) 컬럼에 'Y'나 'N'만 입력될 수 있도록 CHK_DEL_YN이란 이름의 제약조건을 정의
• FOREIGN KEY(외래키): 하나의 테이블이 다른 테이블을 참조하고자 할 때 FK(FOREIGN KEY)를 정함. EMPLOYEES 테이블에 있는 DEPARTMENT_ID 컬럼이 DEPARTMENTS 테이블에 있는 DEPARTMENT_ID 컬럼을 참조한다고 했을 때 EMPLOYEES 테이블의 DEPARTMENT_ID 값은 반드시 DEPARTMENTS 테이블의 DEPARTMENT_ID 컬럼에 존재해야 하며 이와 관련된 상세한 참조 무결성 제약 옵션은 별도로 선택 가능

​

참조 무결성 규정 관련 옵션

• CASCADE: Parent 값 삭제 시 Child 값 같이 삭제
• SET NULL: Parent 값 삭제 시 Child의 해당 컬럼 NULL 처리
• SET DEFAULT: Parent 값 삭제 시 Child의 해당 컬럼 DEFAULT 값으로 변경
• RESTRICT: Child 테이블에 해당 데이터가 PK로 존재하지 않는 경우에만 Parent 값 삭제 및 수정 가능
• NO ACTION: 참조 무결성 제약이 걸려있는 경우 삭제 및 수정 불가

​

완전히 새로운 테이블을 생성하는 것이 아니고 기존에 존재하던 테이블을 복사해서 생성하고 싶은 경우 CTAS(CREATE TABLE ~ AS SELECT ~) 문을 활용할 수 있는데, 컬럼별로 데이터 유형을 다시 명시해주지 않아도 된다는 장점이 있음. BUT 제약조건의 백 퍼센트가 복사되는 것은 아니고 NOT NULL 조건만 되며 PRIMARY KEY, UNIQUE KEY, CHECK 등의 제약조건은 초기화되므로 필요한 경우 별도로 ALTER 명령어를 써서 정의해줘야 함

CREATE 테이블명 AS SELECT * FROM 복사할 테이블명;

​

ALTER

• ADD COLUMN: 새로운 컬럼을 추가할 때 쓰는 명령어. 추가된 컬럼의 위치는 늘 맨 끝이 되며 별도로 위치 지정 불가

ALTER TABLE 테이블명 ADD 컬럼명 데이터유형;

 

• DROP COLUMN: 기존에 있던 컬럼이 필요 없어졌을 때 삭제하는 명령어. 한번 삭제한 컬럼은 복구 불가

ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

 

• MODIFY COLUMN: 기존에 있던 컬럼을 변경하고 싶을 때 쓰는 명령어. 데이터 유형, DEFAULT 값, NOT NULL 제약조건에 대한 변경 가능

ALTER TABLE 테이블명 MODIFY (컬럼명1 데이터 유형 [DEFAULT 값] [NOT NULL], 컬럼명2 데이터 유형 ...);

 

• RENAME COLUMN: 기존에 있던 컬럼의 이름을 변경하고 싶을 때 쓰는 명령어

ALTER TABLE 테이블명 RENAME COLUMN 변경할 컬럼명 TO 변경할 이름;

 

• ADD CONSTRAINT: 제약조건을 추가하고 싶을 때 쓰는 명령어

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (컬럼명);

 

• DROP TABLE: 테이블을 삭제할 때 쓰는 명령어. 만약 해당 테이블을 참조하고 있는 다른 테이블이 존재하는 경우 CASCADE 옵션을 명시하지 않으면 삭제되지 않는다. CASCADE CONSTRAINT는 참조 제약조건도 함께 삭제

DROP TABLE 테이블명 [CASCADE CONSTRAINT];

 

• TRUNCATE TABLE: 테이블에 저장되어 있는 데이터를 모두 제거하는 명령어. DELETE 명령어와 유사하지만 저장 공간이 재사용되도록 초기화한다는 차이점이 있고, ROLLBACK이 불가능해 DDL로 분류됨

TRUNCATE TABLE 테이블명;

 

​

DCL(Data Control Language): USER를 생성하고 권한을 부여하는 명령어로 CREATE USER, ALTER USER, DROP USER가 있다.

​

USER 관련 명령어

• CREATE USER: 사용자를 생성하는 명령어. CREATE USER 권한이 있어야 수행 가능

CREATE USER 사용자명 IDENTIFIED BY 패스워드;

 

• ALTER USER: 사용자를 변경하는 명령어

ALTER USER 사용자명 IDENTIFIED BY 패스워드;

 

• DROP USER: 사용자를 삭제하는 명령어

DROP USER 사용자명;

 

​

권한 관련 명령어

• GRANT: 사용자에게 권한을 부여하는 명령어

GRANT 권한 TO 사용자명;

 

• REVOKE: 사용자에게 권한을 회수하는 명령어

REOVKE 권한 FROM 사용자명;

 

​

ROLE 관련 명령어

ROLE이란 특정 권한들을 하나의 세트처럼 묶는 것이다. CREATE SESSION, CREATE USER, CREATE TABLE 권한을 묶어서 CREATE_R이라고 지정할 수 있다. (ROLE 이름은 상황에 따라 적절히 지어주면 됨)

​

ROLE을 이용한 권한 부여

• ROLE을 생성한다.

CREATE ROLE 롤명;

 

• ROLE에 권한을 부여한다.

GRANT 권한 TO 롤명;

 

• ROLE을 사용자에게 부여한다.

GRANT 롤명 TO 사용자명;

 

​