관리 구문

 

본 게시물은 ‘정미나, ⌜2022 유선배 SQL개발자(SQLD)⌟, 시대고시기획, 2022’ 을 인용하였습니다. 

 

1. DML(Data Manipulation Language)

• DDL에서 정의한 대로 데이터 입력하고, 입력된 데이터를 수정/삭제/조회하는 명령어
• SELECT, INSERT, UPDATE, DELETE : 셀인업데

1-1. INSERT

• 테이블에 데이터 입력
• INSERT Action : AUTOMATIC, SET NULL, SET DEFAULT, DEPENDENT
  • AUTOMATIC : 부모 테이블에 PK 없는 경우, 부모 PK 생성 후 자식 입력
  • SET NULL : 부모 테이블에 PK 없는 경우, 자식 외부 키를 NULL 값 처리
  • SET DEFAULT : 부모 테이블에 PK 없는 경우, 자식 외부 키를 지정된 기본 값으로 입력

1. 단일 행 INSERT 문은 VALUES 절 포함, 한 번에 한 행만 입력

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

INSERT INTO 입사 (부서명, 입사년월, 입사자사번) VALUES ('개발', '202201', '220101');

SELECT * FROM 입사;

 

• 명시되지 않은 컬럼에는 NULL 값 입력됨
• 주의! PK, NOT NULL 제약조건 걸린 컬럼에는 NULL 값 입력X(반드시 값이 있어야 함)

2.

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

INSERT INTO 입사 VALUES('개발', '202201', '220101', '신입');

SELECT * FROM 입사;

 

• 전체 컬럼 대한 데이터가 테이블의 컬럼 순서대로 빠짐없이 나열되어야 함
• 순서 뒤바뀌어 데이터 유형이 맞지 않거나 누락된 데이터가 있어 전체 컬럼 갯수와 맞지 않을 경우, 데이터베이스 에러 발생

1-2. SELECT

셀프웨구해오

SELECT [ALL | DISTINCT] 속성명1, 속성명2, ...     FROM 테이블명1, ...  [WHERE 조건]  [GROUP BY 속성명1, ...]  [HAVING 그룹조건]  [ORDER BY 속성 [ASC | DESC]];

• ALL : 모든 튜플 검색 시
• DISTINCT : 중복 속성 조회 경우, 1개만 검색
• ASC : 오름차순
• DESC : 내림차순

1-2. UPDATE

업셋웨

• 이미 저장된 데이터를 수정하고 싶을 때 사용하는 명령어
• 수정하고 싶은 컬럼이 많으면, SET 절에 ,(콤마)로 이어서 명시O
  • SET 컬럼명1 = 데이터, 컬럼명2 = 데이터, ...
• 주의! WHERE 절 없으면, 테이블의 모든 Row 변경됨

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

UPDATE 입사 SET 구분 = '경력' WHERE 입사자사번 = '220101';

SELECT * FROM 입사;

 

1-3. DELETE

델프웨

• 이미 저장된 데이터를 삭제하고 싶을 때 사용하는 명령어
• 주의! WHERE 절 없으면, 모든 Row 삭제됨

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

DELETE FROM 입사 WHERE 입사자사번 = '220101';

SELECT * FROM 입사;

 

• WHERE 절 없이 테이블 전체 삭제하고자 하는 경우
  • 분명히 삭제하고자 할 때 TRUNCATE 명령어 사용이 시스템 부하 측면에서 유리
    • 대신, TRUNCATE는 별도 로그 없어 ROLLBACK 불가
  • DELETE는 COMMIT 전 ROLLBACK 가능
• DELETE(/MODIFY) Action : CASCADE, SET NULL, SET DEFAULT, RESTRICT
  • CASCADE : 부모 삭제 시 자식 같이 삭제
  • SET NULL : 부모 삭제 시 해당 필드 NULL
  • SET DEFAULT : 부모 삭제 시 자식의 해당 피드 DEFAULT 값 설정
  • RESTRICT : 자식 테이블에 PK 없는 경우만 부모 삭제 허용

1-3. MERGE

테이블에 새로운 데이터 입력하거나 이미 저장된 데이터 대한 변경을 한 번에 할 수 있도록 하는 명령어

MERGE     INTO 타겟 테이블명    USING 비교 테이블명          ON 조건     WHEN MATCHED THEN                 UPDATE                         SET 컬럼명 = 새로운 데이터 [, 컬럼명 = 새로운 데이터 ... ]     WHEN NOT MATCHED THEN                 INSERT [(컬럼명1, 컬럼명2, ...)]                 VALUES (데이터1, 데이터2, ...)'

• ex. DEPARTMENTS 테이블과 데이터 백업하는 DEPARTMENTS_BACKUP 테이블이 있다고 가정

SELECT * FROM DEPARTMENTS_BACKUP;

DEPARTMENTS_BACKUP 테이블은 지속적으로 DEPARTMENTS 테이블과 데이터 동기화 시켜주어야 함

• MERGE 문을 이용해 DEPARTMENTS 테이블의 데이터를 DEPARTMENTS_BACKUP 테이블에 반영하기

MERGE
 INTO DEPARTMENTS_BACKUP DB # 테이블 데이터 변경/생성
USING DEPARTMENTS D # 변경/생성 시 DEPARTMENTS 테이블 이용
   ON (DB.DEPARTMENT_ID = D.DEPARTMENT_ID) # 조건 : 두 테이블의 동일한 값(ID)
 WHEN MATCHED THEN # 조건에 맞는 데이터 있으면, 그 데이터 변경
       UPDATE
          SET DB.DEPARTMENT_NAME = D.DEAPRTMENT_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);

• 만약 DEPARTMENT 테이블의 모든 데이터가 아닌, 특정 조건 데이터들만 백업하고 싶은 경우의 쿼리

# DEPARTMENTS 테이블에서 MANAGER_ID가 NULL 아닌 데이터만 DEPARTMENTS_BACKUP 테이블에 반영

MERGE
 INTO DEPARTMENTS_BACKUP DB
USING (SELECT * FROM DEPARTMENTS WHERE MANAGER_ID IS NOT NULL) 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);

 

 

2. TCL(Transaction Control Language)

• 트랜잭션을 제어하는 명령어
  • 트랜잭션 : 쪼갤 수 없는 업무처리의 단위(한 세트로 묶일 수 밖에 없는 논리적인 업무 단위)
  • ex. 쇼핑몰에서 티셔츠를 하나 살 때 결제 트랜잭션
    • 티셔츠 하나 결제 & 티셔츠 재고 하나 차감 => 하나로 묶여 동작됨
• COMMIT, ROLLBACK, SAVEPOINT
• COMMIT&ROLLBACK 장점
  • 데이터 무결성 보장
  • 영구적 변경 전, 데이터 변경 사항 확인O
  • 논리적으로 연관된 작업을 그룹핑해 처리O

2-1. 트랜잭션 특징 : ACID

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

2-2. COMMIT

• INSERT, DELETE, UPDATE 후 변경된 내용을 확정/반영하는 명령어
• COMMIT을 실행하지 않으면, 메모리까지만 반영됨(메모리는 휘발성, 언제든 사라질 수 있음, 다른 사용자는 변경된 값 조회X)
• COMMIT을 실행해야 최종적으로 데이터 파일에 기록되고 트랜잭션이 완료됨
• 주의! UDPATE 한 뒤 오랜 시간동안 COMMIT/ROLLNBACK 하지 않은 경우, Lock에 걸려 다른 사용자가 변경할 수 없는 상황이 발생할 수 있음

2-3. ROLLBACK

• INSERT, DELETE, UPDATE 후 변경된 내용을 취소하는 명령어로 변경 이전 값으로 복구됨
• 주의! UDPATE 한 뒤 오랜 시간동안 COMMIT/ROLLNBACK 하지 않은 경우, Lock에 걸려 다른 사용자가 변경할 수 없는 상황이 발생할 수 있음

2-4. SAVEPOINT

• ROLLBACK 수행 시, 전체 작업을 되돌리지 않고 일부만 되돌릴 수 있게 하는 기능 가진 명령어
• ROLLBACK 뒤에 특정 SAVEPOINT 지정 시 그 지점까지만 데이터 복구됨

 

 

3. DDL(Data Definition Language)

• CREATE, ALTER, DROP, TRUNCATE : 크알드트
• 데이터를 정의하는 SQL
• 특정 구조 생성/변경/삭제/이름변경
• DDL 대상 : 도스테뷰인(도메인, 스키마, 테이블, 뷰, 인덱스)
• 주의할 점
  • 선언한 유형이 아닌 다른 유형 데이터를 저장하면 에러 발생
  • 적절한 크기로 정의하기
  • 영어, 한글 BYTE 수 달라, 어느 언어로 입력될 컬럼인지 고려해 크기 계산해야 함
    • 영어 한 글자 1byte, 한글 한 글자 2(3)byte
• CREATE, ALTER, DROP, RENAME, TRUNCATE
• 유형 : 데이터 타입
  • 문자 : CHAR, VARCHAR, CLOB
    • CHAR 20 byte 정의 경우, 그대로 20 byte 사용O(크기 고정)
      • 'Pil' = 'Pil ' (스페이스)
    • VARCHAR 20byte 정의 경우, 입력 데이터 수 만큼 사용O(크기 가변)
      • 'Pil' != 'Pil '
  • 숫자 : NUMBER
  • 날짜 : DATE

3-1. CREATE : 테이블 생성 명령어

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

CREATE TABLE TEACHER (
    TEACHER_NO      NUMBER NOT NULL,
    TEACHER_NAME    VARCHAR2(20) NOT NULL,
    SUBJECT_ID      VARCHAR2(5) NOT NULL,
    MOBILE_NO       VARCHAR2(15),
    ADDRESS         VARCHAR(100),
    CONSTRAINT TEACHER_PK PRIMARY KEY (TEACHER_NO),
    CONSTRAINT TEACHER_FK FOREIGN KEY (SUBJECT_NO) REFERENCES SUBJECT(SUBJECT_ID)
 );

• 테이블 생성 시 반드시 지켜야 할 규칙(지키지 않으면 에러 발생)
  • 테이블 명은 고유해야 함
  • 한 테이블 내 컬럼명은 고유해야 함
  • 컬럼명 뒤 데이터 유형&데이터 크기 명시되어야 함
  • 컬럼 대한 정의는 괄호() 안에 기술
  • 테이블명&컬럼명은 숫자로 시작X
  • 마지막은 ; 으로 끝남
• 에러를 발생시키지는 않으나, 지켜야 할 항목
  • 테이블은 각각의 정체성을 나타내는 이름이어야 함
  • 컬럼명 정의 시 다른 테이블과 통일성 있어야 함
• CREATE TABLE 시 제약조건(CONSTRAINTS)도 함께 정의할 수 있음
  • 제약조건 : 테이블에 저장될 데이터의 무결성(데이터 정확성/일관성)을 유지하고, 데이터 결손/부정합이 없음을 보증하기 위해 해놓는 장치
  • 테이블 생성 시 필수 요소는 아니나, 데이터 많이 쌓인 후 정의하기는 힘듦(초기에 정의하는 것이 바람직)

• CREATE TABLE 제약 조건
  • PK(PRIMARY KEY) : 기본 키 정의. 유일하게 테이블 각 행 식별. UNIQUE + NOT NULL 특징
  • FK(FOREIGN KEY) : 외래 키 정의. 참조 대상 테이블(컬럼명) 명시. 열과 참조된 테이블 열 사이의 외래 키 관계 설정
  • UNIQUE : 테이블 내 유일한 값 갖도록 하는 제약조건(NULL 가능)
  • CHECK : 개발자가 정의하는 제약조건. TRUE 이어야 하는 조건 지정
  • DEFAULT : 데이터 INSERT 수행 시 해당 컬럼 넣지 않는 경우, 기본 값으로 설정

• 제약조건(CONSTRAINTS) 종류
  • PRIMARY KEY(기본 키)
    • 테이블에 저장된 각 Row 대한 고유성 보장
    • 한 테이블에 하나씩만 정의O
    • PK 지정 컬럼에는 NULL 값 입력X, 자동으로 UNIQUE 인덱스로 생성됨
  • UNIQUE KEY(고유 키)
    • PRIMARY KEY와 유사하게 테이블에 저장된 각 Row 대한 고유성 보장 위한 제약조건이나, NULL 값이 허용되는 차이점
  • NOT NULL : 해당 컬럼에 NULL 값이 입력되는 것 허용X
  • CHECK : 컬럼에 저장될 수 있는 값의 범위 제한
    • CONSTRAINT CHK_DEL_YN CHECK(DEL_YN IN('Y', 'N'))
      • DEL_YN(삭제여부) 컬럼에 'Y'나 'N'만 입력될 수 있게 CHK_DEL_YN 제약조건 정의
  • FOREGIN KEY(외래 키) : 하나의 테이블이 다른 테이블을 참조하고자 할 때 FK 정의
    • EMPLOYEES 테이블의 DEPARTMENT_ID 컬럼이 DEPARTMENTS 테이블에 있는 DEPARTMENT_ID 컬럼을 참조한다고 할 때, EMPLOYEES 테이블의 DEPARTMENT_ID 값은 반드시 DEPARTMENTS 테이블의 DEPARTMENT_ID 컬럼에 존재해야 함
    • 참조 무결성 제약 옵션 별도 선택O
• 참조 무결성 규정 관련 옵션
  • CASCADE : Parent  값 삭제 시 Child 값 같이 삭제
  • SET NULL : Parent 값 삭제 시 Child의 해당 컬럼 NULL 처리
  • SET DEFAULT : Parent 값 삭제 시 Child의 해당 컬럼 DEFAULT 값으로 변경
  • RESTRICT : Child 테이블에 해당 데이터가 PK로 존재하지 않는 경우만 Parent 값 삭제/수정O
• 완전히 새로운 테이블 생성X, 기존에 존재하던 테이블을 복사해 생성하고 싶은 경우 : CTAS
  • 컬럼별로 데이터 유형 다시 명시하지 않아도 되는 장점
  • 제약조건의 100% 복사되는 것이 아닌, NOT NULL 조건만O
  • PRIMARY KEY, UNIQUE KEY, CHECK 등 제약조건은 초기화됨, 필요할 경우 별도로 ALTER 명령어 사용해 정의

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

3-2. ALTER : 테이블 구조 변경

1. ADD COLUMN : 새 컬럼 추가(추가된 컬럼 위치 : 맨 끝, 별도 위치 지정X)

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

 

2. DROP COLUMN : 기존 컬럼 삭제(삭제한 컬럼 복구X)

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

 

3. MODIFY COLUMN : 기존 컬럼 변경

• 데이터 유형, DEFAULT 값, NOT NULL 제약 조건 대한 변경O
• 컬럼에 저장된 모든 데이터 크기가 줄이고자 하는 컬럼의 크기보다 작을 경우만 줄임O
• 컬럼에 저장된 데이터 없는 경우만 데이터 유형 변경O
• 크기 늘리기는 데이터 상관 없이O
• DEFAULT 값 변경 시 변경 이후 저장되는 데이터에만 적용
• 현재 NULL 값이 저장되어 있지 않은 컬럼에만 NOT NULL 제약 조건 추가O

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

 

4. RENAME COLUMN : 기존 컬럼 이름 변경

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

 

5. ADD CONSTRAINT : 제약조건 추가 시

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (컬럼명); PK : ALTER TABLE PRODUCT ADD CONSTRAINT PRODUCT_PK PRIMARY KEY(PROD_ID) FK : ALTER TABLE PRODUCT ADD CONSTRAINT PRODUCT_FK FOREIGN KEY(TEAM_ID) REFERENCES TEAM(TEAM_ID);

• 주의 : 외래 키 의해 참조되는 테이블 삭제X(PLAYER 테이블이 참조하는 TEAM 테이블 데이터 삭제X)

3-3. DROP : 테이블 삭제(테이블 구조 완전 삭제)

• 해당 테이블 참조하는 다른 테이블 존재하는 경우, CASCADE 옵션 명시하지 않으면 삭제X

DROP TABLE [CASCADE | RESTRICT];

• CASCADE : 참조하는 테이블까지 연쇄 제거
• RESTRICT : 다른 테이블이 삭제할 테이블 참조 중이면, 제거X
• 디스크 사용량 초기화O, 테이블 스키마 정의까지 삭제됨

3-4. TRUNCATE :  테이블에 저장된 데이터 제거

• DELETE 명령어와 유사하나, 저장 공간이 재사용되도록 초기화됨(테이블의 모든 행 삭제)
• ROLLBACK 불가능해 DDL로 분류

TRUNCATE TABLE 테이블명;

• TRUNCATE/DROP TABLE은 로그X, DELETE TABLE은 로그O(디스크 사용량 초기화X)

 

4. DCL(Data Control Language)

• GRANT, REVOKE
• USER 생성, USER에게 데이터를 컨트롤할 수 있는 권한 부여/회수하는 명령어

4-1. USER 관련 명령어

• 하나의 DATABASE는 여러 개의 USER를 가질 수 있음

1. CREATE USER : 사용자 생성(CREATE USER 권한 있어야 수행O)

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

 

2. ALTER USER : 사용자 변경

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

 

3. DROP USER : 사용자 삭제

DROP USER 사용자명;

4-2. 권한 관련 명령어

1.  GRANT : 사용자에게 권한 부여

GRANT 권한 TO 사용자명;

 

2. REVOKE : 사용자에게 권한 회수

REVOKE 권한 FROM 사용자명;

4-3. ROLE 관련 명령어

• ROLE : 특정 권한들을 하나의 세트처럼 묶는 것

▶ ROLE 이용한 권한 부여

1. ROLE 생성

CREATE ROLE 롤명;

2. ROLE에 권한 부여

GRANT 권한 TO 롤명;

3. ROLE을 사용자에게 부여

GRANT 롤명 TO 사용자명;