SQL 관리구문

D/B 관리에 대한 이해를 넓히고자 SQL(structured query language)에 대해 공부하고자 합니다. 웹 개발에 있어서 백엔드 개발자는 기본적으로 알아야하는 언어이며, 프론트 엔드 개발자도 D/B에서 데이터에 어떻게 접근하고 처리하는지에 대한 개념이 필요하다고 생각합니다.

오늘은 SQL에서 테이블 및 데이터 등을 생성, 변경, 삭제 등을 할 수 있는 관리구문에 대해 정리하려합니다. 구문별 사용법과 주의사항에 대해 알아봅시다.

관리 구문

DML(Data Manipulation Language)

INSERT

테이블에 데이터를 입력하는 명령어입니다.

INSERT INTO 테이블명 (컬럼명1, 컬럼명2...) VALUES (데이터1, 데이터2...);
// 입사 테이블에 속성으로 [부서명 | 인사년월 | 직원번호 | 구분]이 있다고 가정
// INSERT INTO 입사 VALUES ('개발', '170821', 170203', '신입')

UPDATE

테이블에 데이터를 수정하고 싶을 때 사용하는 명령어입니다.

UPDATE 테이블명 SET 컬럼명 = 새로운 데이터 (WHERE 수정할 데이터에 대한 조건);
// UPDATE 입사 SET 구분 ='신입' WHERE 직원번호 '220508'

DELETE

테이블에 데이터를 삭제하고 싶을 때 사용하는 명령어입니다. WHERE절이 없으면 테이블의 모든 데이터가 삭제되지만 COMMIT 전에 ROLLBACK이 가능합니다.

DELETE FROM 테이블명 (WHERE 수정한 데이터에 대한 조건);
// DELETE FROM 입사 WHERE 직원번호 = '220508'

MERGE

테이블에 새로운 데이터를 입력하거나 이미저 저장되어 있는 데이터에 대한 변경 작업을 한번에 할 수 있습니다.

MERGE
  INTO 타겟 테이블명  // 테이블의 데이터를 변경 또는 생성
  USING 비교 테이블명  // 변경 또는 생성 시 비교 테이블을 이용
  	ON 조건
  WHEN MATCHED THEN // 조건에 맞는 데이터가 있으면 그 데이터 변경
  		UPDATE
     		SET 컬럼병 = 새로운 데이터[컬럼명 = 새로운 데이터]
  WHEN NMT MATCHED THEN  // 조건에 맞는 데이터가 없으면 그 데이터 생성
 			INSERT[(컬럼명1, 컬럼명2...)]
  			VALUES(데이터1, 데이터2);

// 데이터를 백업해주는 테이블을 관리하는데 사용할 수 있다.

TCL(Transaction Control Language)

TCL은 트랜잭션을 제어하는 명령어입니다. 트랜잭션이란 쪼개질 수 없는 업무 처리 단위를 말합니다. 예를 들어 인터넷을 통해 사고 싶은 옷을 결재하면 매장의 재고가 하나가 줄어드는 것과 같은 개념입니다.. 이렇게 두 가지 액션은 하나로 묶여서 동작하는 트랜잭션입니다.

트랜잭션의 특징

• 1. 원자성 : 트랙적션으로 묶인 일련의 동작들은 모두 성공하거나 실패해야합니다.
• 2. 일관성 : 트랜잭션이 완료된 이후에도 데이터베이스가 가진 데이터에 일관성이 있어야헙니다.
• 3. 고립성 : 하나의 트랜잭션은 고립되어 수행되어야 한다. 예를들어 내가 구매하려는 물품을 다른 사람이 보고 있다면 나는 재고 데이터를 참조하거나 변경할 수 없습니다.
• 4. 지속성 : 트랜잭선이 성공적으로 수행되었을 경우, 트랜잭션이 변경한 데이터가 영구적으로 저장되어야 합니다.

COMMIT

INSERT, DELETE, UPDATE 후 변경된 내용을 확정 및 반영하는 명령어이다. 메모리에 올라간 내용이 COMMIT을 실행해야 최종적을 데이터 파일에 기록이 됩니다.

ROLLBACK

INSERT, DELETE, UPDATE 후 변경된 내용을 취소하는 명령어이다. ROLLBACK을 하면 변경하기 이전 값으로 복구됩니다.
➡ 트랜젝션 종료 시점에서 시작 시점으로 되돌립니다.

SAVEPOINT

ROLLBACK을 수행할 때 전체 작업을 되돌리지 않고 일부만 되돌릴 수 있는 기능입니다.
➡ 트랜젝션 종료 시점과 시작시점 사이의 저장 지점으로 데이터를 되돌립니다.

DDL(Data Definition Language)

DDL은 테이블을 생성, 변경, 삭제할 수 있는 정의어입니다.

CREATE

테이블을 생성하기 위한 명령어입니다.

CREATAE TABLE 테이블명 (컬럼명 데이터 타입 (DEFAUL / NULL 여부)...
/* CREATE TABLE STUDENT (
   STUDENT_NO NUMBER NOT NULL,
   STUDENT_NAME VARCHAR2(20) NOT NULL ... ) */

ALTER

테이블을 변경하기 위한 명령어입니다.

ALTER TABLE 테이블명 ADD 컬럼명 데이터 유형;
// ALTER TABLE STUDENT ADD AGE NUMBER; (추가)
// ALTER TABLE STUDENT DROP AGE; (삭제)
// ALTER TABLE STUDENT RENAME STUDENT TO TEACHER (컬럼 이름 변경)
// ALTER TABLE STUDENT MODIFY (AGE NUMBER DEFAUL 0 NOT NULL); (데이터 수정)

DROP

테이블을 삭제할 때 사용하는 명령어입니다.

DROP TABLE 테이블명 [CASCADE CONSTRAINT]
// 해당 테이블을 참조하고 있는 다른 테이블이 존재할 경우 CASCADE 옵션을 명시하지 않으면 삭제되지 않습니다.
// CASCADE CONSTRAINT는 제약조건을 삭제하겠다는 의미입니다.

TRUNCATE

테이블에 저장되어 있는 데이터를 모두 제거하는 명령어로 ROLLBACK이 불가능합니다.

TRUNCATR TABLE 테이블명;

DCL(Data Contol Language)

1.유저 관련 명령어

CREATE USER

사용자를 생상하는 명령어입니다.

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

ALTER USER

사용자를 변경하는 명령어입니다.

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

DROP USER

사용자을 삭제하는 명령어입니다.

DROP USER 사용자명;

2.권한 관련 명령어

GRANT

사용자에게 권한을 부여하는 명령어입니다.

GRANT 권한 TO 사용자명
// GRANT CREATE SESSION TO 홍길동
// GRANT CREATE USER TO 홍길동
// GRANT CREATE TABLE TO 홍길동

REVOKE

사용자에게 권한을 회수하는 명령어입니다.

REVOKE 권한 FROM 사용자명;

3. ROLE 관련 명령어

ROLE

ROLE이란 특정 권한들을 하나의 세트처럼 묶어서 관리하는 것 입니다. 사용자에게 필수적으로 주어야하는 권한을 묶어서 관리한다면 효율적으로 DATA를 컨트롤 할 수 있습니다.

CREATE ROLE 롤명;
// CREATE ROLE CREATE_R;

GRANT 권한 TO 롤명;
// GRANT CREATE CLASS, CREATE STUDENT, CREATE MAJOR TO CREATE_R

GRANT 롤명 TO 사용자
// GRANT CREATE_R TO