IT/SQLD

[SQLD/IT자격증] SQLD 2과목 "관리구문" 이론 정리

y-seo 2024. 8. 18. 12:10

SQL 2과목 SQL 활용 중 SQL 기본

SQLD(SQL개발자) 시험의 2과목인 SQL 활용 중 SQL 관리구문에서 요구하는 항목은 아래 사진과 같습니다.

아래 유튜브 채널 강의를 들으며 정리하였습니다.

[개정판] SQLD 2과목 PART3. 관리 구문(2024 신유형 반영) #DML #TCL #DDL #DCL - YouTube

 

DML(Data Manipulation Language)

  • 데이터 삽입(INSERT), 수정(UPDATE), 삭제(DELETE), 병합(MERGE)을 할 수 있는 언어
  • 저장(Commit) 혹은 취소(Rollback)이 반드시 필요하며 이를 통해 트랜잭션을 제어한다.

 

INSERT

  • 테이블에 행을 삽입할 때 사용하는 명령어로 한 번에 한 행만 입력이 가능하다. (SQL Server는 여러 행을 동시에 삽입할 수 있다.)
  • 하나의 컬럼에는 한 값만 삽입 가능하다. 
  • 데이터 타입과 사이즈 조건에 맞게 컬럼을 삽입해야 한다.
  • INTO절에 컬럼명을 명시하면 일부 컬럼만 입력할 수 있으며 작성하지 않은 컬럼은 NULL이 입력된다. 
  • 전체 컬럼에 대해 데이터를 삽입할 경우 테이블 명 뒤의 컬럼명 열거를 생략할 수 있다.
#전체 컬럼의 값을 삽입
INSERT INTO 테이블 VALUES(값1, 값2, ...)
#선택한 컬럼의 값을 삽입
INSERT INT 테이블(컬럼1, 컬럼2, ...) VALUES(값1, 값2, ...)

문자 컬럼에 숫자값을, 숫자 컬럼에 문자값을 삽입할 수는 있지만 권장하지 않는다.

ORACLE에서 여러 행을 삽입하려면 아래와 같이 서브 쿼리를 사용해야 한다.

INSERT INTO EMP3(EMTNO, ENAME, DEPTNO)
SELECT EMPNO, ENAME, DEPTNO
FROM EMP
WHERE DEPTNO=20;

 

UPDATE

  • 데이터를 수정할 때 사용하는 명령어
  • 컬럼 단위로 수행한다.
  • 여러 컬럼을 수정할 수 있다.
#하나의 컬럼을 수정할 때 
UPDATE 테이블명
SET 수정할컬럼명=수정값
WHERE 조건;

#여러 컬럼을 수정할 때
UPDATE 테이블명
SET 수정할컬럼명1=수정값1, 수정할컬럼명2=수정값2, ...
WHERE 조건;

UPDATE 테이블명
SET (수정할컬럼명1, 수정할컬럼명2, ...) = (SELECT 수정값1, 수정값2, ...)
WHERE 조건;

이때 수정값은 상수로 할 수도 있고 서브쿼리를 통해 설정할 수도 있다.

 

DELETE

  • 데이터를 삭제할 때 사용하는 명령어
  • 행 단위로 실행한다.
DELETE [FROM] 테이블명
[WHERE 조건];

 

MERGE

  • 데이터를 병합할 때 사용하는 명령어
  • 참조 테이블과 동일하게 맞추는 작업을 한다.
  • INSERT, UPDATE, DELETE를 동시에 수행한다.
MERGE INTO 수정할테이블명
USING 참조할테이블명
ON (연결조건)
WHERE MATCHED THEN
UPDATE
SET 수정내용
DELETE (조건)
WHEN NOT MATCHED THEN
INSERT VALUES (값1, 값2, ...);

 

TCL(Transaction Control Language)

  • 트랜잭션 제어어로 COMMIT, ROLLBACK이 있다.
  • DML에 의해 조작된 결과를 트랜잭션별로 제어하는 명령어이다.
  • DML 수행 후 트랜잭션을 정상 종료하지 않는 경우 LOCK이 발생할 수 있다.

LOCK이란 트랜잭션이 수행하는 동안 특정 데이터에 대해 다른 트랜잭션이 동시에 접근하지 못하도록 제한하는 기능이다.

 

트랜잭션(Transaction)

  • DB의 논리적 연산 단위로 더이상 분할 할 수 없는 최소 단위이다.
  • 하나의 트랜잭션에는 하나 이상의 SQL문장이 포함된다.
  • ALL OR NOTHING의 개념으로 모두 COMMIT 하거나 모두 ROLLBACK 처리해야 한다.

트랜잭션의 특성

  • 원자성(automicity) : 트랜잭션 정의된 연산들이 모두 성공적으로 실행되거나 전혀 실행되지 않은 상태로 돌아가야 한다.
  • 일관성(consistency) : 트랜잭션 실행 전에 DB 내용이 잘못되어 있지 않았다면 트랜잭션 실행 이후에도 DB 내용에는 잘못이 있으면 안된다.
  • 고립성(isolation) : 트랜잭션은 실행 도중 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들면 안된다.
  • 지속성(durability) : 트랜잭션이 성공적으로 수행하면 갱신한 DB 내용이 영구적으로 저장된다.

 

COMMIT

  • 입력, 수정, 삭제한 데이터에 이상이 없을 경우 데이터를 저장하는 명령어
  • 한 번 COMMIT을 수행하면 XOMMIT 이전에 수행된 DML은 모두 저장되며 돌릴 수 없다.
  • ORACLE은 DDL 시 AUTO COMMIT이다. SQL Server은 AUTO COMMIT을 비활성화 할 수 있다.

 

ROLLBACK

  • 입력, 수정, 삭제한 데이터에 대해 변경을 취소하는 명령어
  • DB에 저장되지 않고 최정 COMMIT 지점이나 변경 전, 혹은 특정 SAVEPOINT 지점으로 복귀된다.
  • 최종 COMMIT 시점 이전까지 ROLLBACK이 가능하다.
  • SAVEPOINT를 설정하며 최종 COMMIT이 아닌 그 이후의 원하는 시점으로도 복귀시킬 수 있다.

SAVEPOINT란 트랜잭션 내에서 ROLLBACK을 부분적으로 수행하기 위해 지정하는 지점을 일컫는다. 사용자가 원하는 위치에 원하는 이름으로 설정이 가능하며  COMMIT 이전으로는 원복이 불가능하다. 

SAVEPOINT savepoint_name;

ROLLBACK TO savepont_name;

 

DDL(Data Definition Language)

  • 데이터 정의어로 데이터의 구조를 정의한다. (객체를 생성하거나 삭제하거나 변경한다.)
  • CREATE(객체 생성), ALTER(객체 변경), DROP(객체 삭제), TRUNCATE(데이터 삭제)
  • AUTO COMMIT으로 명령어를 수행하는 즉시 저장되고 되돌릴 수 없다.

TRUNCATE는 DML의 특성을 가지지만 AUTO COMMIT이기 때문에 DDL로 분류된다.

 

CREATE

  • 테이블이나 인덱스와 같은 객체를 생성하는 명령어
  • 테이블명, 컬럼명, 컬럼순서, 컬럼크기, 컬럼 데이터타입을 모두 정의해야 한다.
  • 컬럼의 제약조건이나 기본값은 생략할 수 있다.
  • 소유자를 명시할 수 있다.
  • 숫자컬럼의 경우에는 컬럼 사이즈를 생략할 수 있고 날짜 컬럼은 사이즈를 명시해서는 안된다. 문자 컬럼은 ORACLE의 경우 반드시 컬럼 사이즈를 지정해야 한다.
##하나하나 지정하는 경우
CREAT TABLE [소유자.]테이블명(
	컬럼1 데이터타입 [DEFAULT 기본값] [제약조건], 
    컬럼2 데이터타입 [DEFAULT 기본값] [제약조건], ...);
    
#복제하는 경우
 CREATE TABLE 새로만들테이블명
 AS
 SELECT * FROM 복제할테이블명;

NULL 속성도 복제되지만 테이블에 있는 제약조건이나 INDEX 등은 복제되지 않는다.

SQL Server에서는 VARCHAR2를 VARCHAR로, NUMBER을 NUMBERIC으로 사용하며 문자타입 사이즈를 생략할 수 있으며 그때는 1로 설정된다.

##데이터 없이 구조만 복제할 때
CREATE TABLE TEST
AS
SELECT * 
FROM EMP
WHERE 1=2;

→WHERE절의 값이 항상 FALSE이기 때문에 데이터는 하나도 출력되지 않고 컬럼 정보들만 출력된다.

##테이블 복제 시 컬럼명 변경하기
CREATE TABLE TEST2(A,B)
AS
SELECT EMPNO, ENMAE
FROM EMP;

 

ALTER

  • 테이블 구조를 변경하는 명령어
  • 컬럼명, 컬럼 데이터타입, 컬럼 사이즈, dafault 값, 컬럼 삭제, 컬럼 추가, 제약조건을 젼경할 수 있다. 하지만 컬럼 순서는 변경할 수 없으며 재생성으로 해결해야 한다.

컬럼 추가

  • 컬럼 추가는 무조건 맨 마지막에 위치한다.
  • 컬럼 추가 시 데이터 타입 명시는 필수이다.
  • 괄호를 사용하여 여러 컬럼을 동시에 추가할 수 있다.
  • 해당 테이블에 데이터가 있는 경우에 컬럼 추가 시 모두 NULL 값을 가져야 하므로 NOT NULL 속성은 지정할 수 없다.
    • DEAFULT 값을 선언하면 NOT NULL 속성을 가지는 컬럼 추가가 가능하다.
  • 해당 테이블에 데이터가 없는 경우에는 NOT NULL 속성을 지정할 수 있다.
ALTER TABLE 테이블명 ADD 컬럼명 데이터타입 [DEFAULT] [제약조건];

컬럼 속성 변경

  • 컬럼 사이즈, 데이터 타입, DEFAULT 값을 변경할 수 있다.
  • 여러 컬럼을 동시에 변경할 수 있다.
ALTER TABLE 테이블명 MODIFY (컬럼명 DEFAULT 값)

컬럼 사이즈 변경

  • 증가는 항상 가능하다.
  • 축소는 데이터가 존재할 경우 데이터의 길이까지만 축소할 수 이싿.
  • 동시 변경이 가능하다.
ALTER TABLE TEST MODIFY COL_A NUMBER(100), COL_B(6));

데이터타입 변경

  • 데이터가 없는 경우 자유롭게 변경할 수 있다.
  • CHAR, VARCHAR의 경우에는 데이터가 있어도 서로 변경할 수 있다.

DEFAULT 값 변경

  • DEFAULT 값이란 특정 컬럼에 값이 입력되지 않을 경우 자동으로 부여되는 값이다.
  • INSER 시에 DEFAULT 값이 선언된 컬럼에 NULL을 직접 입력할 때에는 NULL이 저장된다.
  • 이미 데이터가 존재하는 테이블에 DEFAULT 값을 선언할 경우 기존 데이터는 수정되지 않는다.
  • DEAFULT 값을 해제할 경우 DEFAULT 값을 NULL로 선언하면 된다.
ALTER TABLE 테이블명 MODIFY (컬럼명 DEFAULT 바꿀값);

컬럼명 변경

ALTER TABLE 테이블명 RENAME COLUM 기존컬럼명 TO 바꿀컬럼명;

컬럼 삭제

  • 데이터 존재 여부와 상관없이 항상 가능하다.
  • RECYCLEBIN에 남지 않기에 복구가 불가능하다.
  • 동시에 삭제가 불가능하다.
ALTER TABLE 테이블 DROP COLUMN 컬럼명;

 

DROP

  • 테이블 또는 INDEX와 같은 객체를 삭제하는 명령어
  • DROP 이후에은 조회가 불가능하다.
DROP TABLE 테이블명 [PURGE];

PURGE로 테이블을 삭제할 경우 RECYCLEBIN에서 조회할 수 없다.

 

TRUNCATE

  • 객체 구조는 남기고 데이터만 삭제하는 명령어
  • AUTO COMMIT이기 때문에 RECYCLEBIN에 남지 않고 복구가 불가능하다.
TRUNCATE TABLE 테이블명;

 

DELETE vs DROP vs TRUNCATE

 

제약조건

  • 데이터 무결성을 위해 각 컬럼에 생성하는 데이터의 제약 장치
  • 테이블 생성 시와 컬럼 추가 시에 정의 가능하다.
##테이블 생성 시
CREATE TABLE 테이블명(컬럼1 데이터타입 [DEFAULT 기본값] [[CONSTRAINT 제약조건명] 제약조건종류],
	컬럼2 데이터타입 [DEFAULT 기본값] [[CONSTRAINT 제약조건명] 제약조건종류], ... ]
    ...
    );
    
##컬럼 추가 시
ALTER TABLE 테이블명
ADD 컬럼명 데이터타입 [DEFAULT 기본값] [[CONSTRAINT 제약조건명] 제약조건종류];

##컬럼 변경 시 
ALTER TABLE 테이블명
ADD [CONSTRAINT 제약조건명] 제약조건종류;

##제약조건 삭제
ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;

PRIMARY KEY(기본키)

  • 각 행을 구별할 수 있는 유일한 식별자
  • 중복, NULL을 허용하지 않는다. (UNIQUE+NOT NULL)
  • 특정 컬럼에 PRIMARY KEY를 생성하면 NOT NULL 속성이 자동으로 부여된다. 
  • CTAS로 테이블 복사 시에는 PK와 NOT NULL 속성이 복사되지 않는다.
  • 하나의 테이블에 여러 기본키를 생성할 수 없다.
  • 하나의 기본키를 여러 컬럼을 결합하여 생성할 수 있다
  • PK 생성 시 자동으로 UNIQUE INDEX를 생성할 수 있다.

UNIQUE

  • 중복을 허용하지 않는 제약조건
  • NULL은 허용된다.

NOT NULL

  • 다른 제약조건과 달리 컬럼의 특징을 나타낸다. 따라서 CTAS로 복제가 가능하다.
  • 컬럼 생성 시에 NOT NULL을 선언하지 않으면 Nullable 컬럼으로 생성된다.
  • 이미 있는 컬럼에 NOT NULL 선언을 하고 싶으면 MODIFY로 해결해야 한다.

FOREIGN KEY

  • 참조 테이블의 참조 컬럼에 있는 데이터를 확인하면서 본 테이블 데이터를 관리할 목적으로 생성
  • 반드시 참조 테이블의 참조 컬럼이 PK 혹은 UNIQUE KEY를 가져야 한다.
  • 옵션
    • ON DELETE CASCADE : 부모 데이터 삭제 시에 자식 데이터도 같이 삭제된다.
    • ON DELETE SET NULL : 부모 데이터 삭제 시에 자식 데이터의 참조값이 NULL로 변경된다.
CREATE TABLE 테이블명(컬럼1 데이터타입 [DEFAULT 값] REFERENCES 참조테이블(참조키), ...);

CHECK

  • 직접적으로 데이터의 값 범위(도메인)을 제한하는 제약조건

 

뷰(View) 

  • 테이블처럼 물리적으로 디스크에 저장되지는 않지만 메타 데이터가 생성되어 조회 및 수정할 수 있는 객체
  • 자주 사용하는 쿼리를 View로 설정하면 별칭처럼 간단하게 사용할 수 있다.
CREATE [OR REPLACE] VIEW 뷰이름
AS
조회쿼리;

DROP VIEW 뷰명;

뷰의 종류

  • 단순뷰 : 하나의 테이블을 조회하는 뷰
  • 복합뷰 : 둘 이상의 테이블을 조인하는 뷰

뷰의 특징

  • 뷰는 기본 테이블과 같은 형태의 구조를 가진다.
  • 뷰는 저장공간을 차지하지 않는다.
  • 뷰는 데이터를 안전하게 보호할 수 있다.
  • 기본 테이블이 삭제되면 뷰 또한 삭제된다.

뷰의 장점

  • 논리적 독립성을 제공한다.
  • 데이터의 접근을 제어함으로써 보안을 유지한다.
  • 사용자의 데이터 관리를 단순화한다.

뷰의 단점

  • 정의 변경이 불가능하다.
  • 삽입, 삭제, 경신 연산에 제한이 있다.
  • 인덱스 구성이 불가능하다.

 

시퀀스(SEQUENCE)

  • 자동으로 연속적인 숫자를 부여해주는 객체
CREATE SEQUENCE 시퀀스명
INCREMENT BY			#증가값(DEFAULT:1)
START WITH			#시작값(DEFAULT:1)
MAXVALUE			#마지막값(증가시퀀스), 재사용 시 시작값(감소시퀀스)
MINVALUE 			#시작값(감소시퀀스), 재사용 시 마지막값(증가시퀀스)
CYCLE | NOCYCLE			#시퀀스 번호 재사용(DEFAULT : NOCYCLE)
CACHE N				#캐시값(DEFAULT:20)
;

 

시노님(SYNONYM)

  • 테이블의 별칭을 생성하는 객체
  • 본인 소유 테이블이 아니더라도 테이블의 별칭을 붙여 간단하게 조회할 수 있다.
CREATE [OR REPLACE] [PUBLIC] SYNONYM 별칭 FOR 테이블명;
  • OR REPLACE : 기존에 같은 이름으로 시노님이 생성되어 있는 경우에 대체
  • PUBLIC : 시노님을 생성한 유저만 사용 가능한 PRIVATE SYNONYM의 반대로 누구나 사용 가능
  • PUBLIC으로 생성한 시노님은 반드시 PUBLIC으로 삭제해야 한다.

 

DCL(Data Control Langauge)

  • 데이터 제어어
  • 객체에 대한 권한을 부여(GRANT)하거나 회수(REVOKE)하는 기능
  • 테이블 소유자는 타계정에 테이블 조회 및 ㅜ정 권한을 부여하거나 회수할 수 있다.

권한

  • 일반적으로 본인 소유가 아닌 테이블은 원칙적으로 조회가 불가능하나 권한을 부여하여 테이블 소유자가 아닌 계정도 테이블을 조회하거나 수정할 수 있다.

권한 종류

  • 오브젝트 권한
    • 테이블에 대한 권한을 제어한다. (SELECT INSERT, UPDATE, DELETE, MERGE) 
    • 테이블 소유자는 타계정 소유 테이블에 대해 조회/수정 권한을 부여하거나 회수할 수 있다.
  • 시스템 권한
    • 시스템 작업에 대한 권한을 제어한다. (테이블 생성, 인덱스 삭제)
    • 관리자 권한만 권한을 부여하거나 회수할 수 있다.

 

GRANT

  • 권한을 부여하는 명령어
  • 권한 부여 시 반드시 소유자나 관리자계정으로 접속하여 권한을 부여해야 한다.
  • 동시에 여러 유저에 대한 여러 권한을 부여할 수 있다. 하지만 여러 객체에 대한 권한은 부여 불가능하다.
GRANT 권한 ON 테이블명 TO 유저;

 

REVOKE

  • 권한을 회수하는 명령어
  • 동시에 여러 유저로부터 여러 권한을 회수할 수 있다.
  • 이미 회수한 권한을 재회수 할 경우에는 오류가 발생한다.
REVOKE 권한 ON 테이블명 FROM 유저;

 

ROLE 

  • 권한의 묶음
  • CREATE로 생성할 수 있는 객체이다.
  • SYSTEM 계정에서 ROLE을 생성할 수 있다.
##롤 생성
CREATE ROLE 롤이름;

##롤 부여
GRANT 롤이름 TO 부여대상;

##롤에서 권한 제외
REVOKE SELECT ON 권한 FROM 롤이름;

롤에서 회수된 권한은 즉시 반영되기 때문에 다시 롤을 부여할 필요 없다.

 

권한 부여 옵션

  • 중간 관리자를 둘 때 쓰는 옵션이다.

WITH GRANT OPTION

  • WITH GRANT OPTION으로 받은 오브젝트 권한을 다른 사용자에게 부여할 수 있다.
  • 중간관리자가 부여한 권한은 중간관리자만 회수할 수 있다.
  • 중간관리자에게 부여된 권한이 회수될 경우 제3자에게 부여된 권한도 같이 회수된다.

WITH ADMIN OPTION

  • WITH ADMIN OPTION을 통해 부여 받은 시스템 권한이나 롤 권한을 다른 사용자에게 부여할 수 있다.
  • 중간관리자가 부여한 권한도 총괄관리자가 직접 회수할 수 있다.
  • 중간관리자에게 부여된 권한이 회수될 경우 제3자에게 부여된 권한은 회수되지 않고 남아있다.
GRANT SELECT ON SCOTT.TEST TO CYS WITH GRANT OPTION;
GRANT CREATE ANY TABLE TO CYS WITH ADMIN OPTION;

GRANT SELECT ON SCOTT.TEST TO PARK;
GRANT CREATE ANY TABLE TO PARK;

 

저작자표시