[🪪SQLD] 2과목 - 관리구문 : DML/TCL/DDL/DCL(24년 개정 요약)

[DML]

1) INSERT문  : 테이블에 데이터를 입력하는 DML

 

   [INSER문의 구조]

INSERT INTO 테이블명 (칼럼명, 칼럼명)
VALUES (테이블에 입력될 데이터, ‘문자열 사용 시’)

 

- SELECT문으로 입력 : 데이터 조회 후 해당 테이블에 바로 삽입가능 단, 입력되는 테이블은 사전에 생성이 되어 있어야 함

- Nologging : 데이터를 입력하면 로그파일에 정보를 기록하는데 Check Point 라는 이벤트가 발생하면 로그파일의 데이터를 데이터 파일에 저장함

-> 이 옵션은 로그파일의 기록을 최소화시켜 입력 시 성능을 향상시키는   방법으로 Buffer Cache라는 메모리 영역을 생략하고 기록

 

 2) UPDATE문 : 원하는 조건으로 데이터를 검색하여 데이터 값을 수정

 

   [UPDATE문의 구조]

UPDATE 수정되는 테이블 SET 칼럼 값 = 변경할 값
WHERE 조건 = 조건 값;

 

 3) DELETE문 : 원하는 조건 검색 후 해당되는 행 삭제로 만약 조건문을 입력하지 않으면 테이블의 모든 데이터는 삭제가 됨

-> DELETE문으로 데이터를 삭제해도 테이블의 용량이 초기화 되지 않음

 

   [DELETE문의 구조]

DELETE FROM 테이블명 WHERE 조건 = 조건 값;

 

* 테이블의 용량이 초기화되지 않는다는 의미

 -> Oracle DB는 저장공간 할당 시 Extent 단위로 할당/저장을 하는데 만약 Extent의 크기가 MAX-EXTEBTS를 넘어서게 되면 용량 초과 오류가 발생함. DELETE문은 삭제 여부만 표시하기 때문에   용량 초기화가 되지 않는 것

 

[테이블의 모든 데이터 삭제]

 1) DELETE FROM 테이블명;

   - 테이블의 모든 데이터를 삭제하지만 테이블 용량은 감소하지 않음

 2) TRUNCATE TABLE 테이블명;

   - 테이블의 모든 데이터를 삭제하고 삭제 후 테이블  용량 초기화

 

[TCL]

1) COMMIT

   - INSET, UPDATE, DELETE문으로 변경한 데이터를 DB에 반영

   - 변경 이전의 데이터 값은 잃어 버리고 다른 모든 데이터베이스 사용자는 변경된 데이터를 보고 조작할 수 있음

   - COMMIT이 완료되면 데이터베이스 변경으로 인한 LOCK 해제가되며 COMMIT 실행 시 하나의 트랜잭션 과정을 종료

2) ROLLBACK

   - 데이터에 대한 변경 사용을 모두 취소하고 트랜잭션을 종료

     (INSERT, UPDATE, DELETE 작업을 모두 취소하지만 이전 COMMIT한 곳 까지만 복구)

   - ROCK이 해제되고 타 사용자도 데이터베이스 행 조작이 가능

 

 3) SAVEPOINT (저장점)

   - 트랜잭션을 작게 분할하여 관리하는 것으로 SAVEPOINT 사용 시 지정된 위치 이후의 트랜잭션만 ROLLBACK이 가능

   [SAVEPOINT 실행]

SAVEPOINT <SAVEPOINT명>

지정된 저장점 까지만 데이터 변경을 취소하고 싶은 경우
-> ROLLBACK TO <SAVEPOINT명> 실행

* ROLLBACK을 실행하면 SAVEPOINT와 관계없이 데이터의 모든 변경사항을 저장하지 않음

[DDL]

· 테이블 생성

 -> 데이터베이스 사용을 위해서는 테이블을 먼저 생성해야 함

 [Create Table의 구조]

Create Table 테이블명
   칼럼정보 데이터타입 (기본키) ;

 

1) Create Table 테이블명

   - 테이블명을 가진 테이블을 생성하라는 의미로 ()사이에 칼럼을 쓰고   세미콜론으로 끝나야 함

 2) 칼럼정보

   - 테이블에 생성되는 칼럼 이름과 데이터 타입을 입력 해야함

   - 칼럼 이름은 영문, 한글, 숫자 모두 가능하며 ,으로 구분

 3) 데이터 타입

   - number : 칼럼의 데이터 타입을 숫자형으로 지정

   - varchar2 : 가변 길이 문자열로 지정

   - char : 칼럼의 데이터 타입을 고정된 크기의 문자로 지정

   - date : 날짜형 타입으로 지정

 4) 기본키

   - 기본키를 지정할 시 칼럼 옆에 primary key를 입력

 

· 테이블 관리 SQL문

 1) Create Table

   - 새 테이블을 생성함, 생성 시 기본키/외래키/제약사항 등 설정이 가능

 2) Alter Table

   - 생성된 테이블을 변경, 칼럼 추가 및 변경/삭제 가능

   - 기본키/외래키 설정이 가능

 3) Drop Table

   - 해당 테이블을 삭제, 데이터 구조와 저장된 데이터 모두 삭제

[제약조건 사용]

 -> 기본키, 외래키, 기본값, not null등은 테이블 생성 시 지정

 

* 외래키 : 외래키를 지정하려면 마스터 테이블이 우선 생성되어야 함

 [외래키 지정방법]

 Constraint 외래키 이름 foreign key (참조 테이블)
    References 테이블명(칼럼명)

 

[CASCASE]

 -> 참조 관계 (PK와 FK)가 있는 경우 참조되는 데이터를 자동으로 반영할 수 있는 옵션으로 테이블을 생성할 때 사용이 가능

 

· 테이블 변경

 -> 테이블명을 변경하거나 칼럼 추가/변경/삭제 등이 가능

 

 [테이블 변경 방법]

 ALTER TABLE 테이블명
    RENAME TO 변경할 테이블 명;

 

 [칼럼추가 방법]

 ALTER TABLE 테이블 명
     ADD (칼럼명 데이터타입 조건);

 

[칼럼변경 방법]

 ALTER TABLE 테이블명
    MODIFY (칼럼명 변경 할 데이터 타입 조건);

 * 데이터 타입 변경이나 데이터 길이 변경이 가능하고 데이터 변경할 때 제약조건 설정도 가능함

 * 데이터 타입을 변경할 때 기본 데이터가 있는 경우는 에러가 발생

 ex) 숫자 타입, 숫자 데이터가 저장되어 있는데 문자형 타입으로 변경 -> 에러

 

 [칼럼삭제 방법]

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

 

 [칼럼명 변경 방법]

 ALTER TABLE 테이블명
    RENAME COLUMN 기존 칼럼명 to 새 칼럼명;

 

· 테이블 삭제

 [테이블 삭제 방법]

 DROP TABLE 테이블명;
 -> 데이터와 테이블 구조 모두 삭제

 DROP TABLE 테이블명 CASCASE CONSTRAINT;
 -> 참조된 제약사항까지도 모두 삭제

 

· 뷰 (View)

 -> 테이블로부터 유도된 가상의 테이블로 실제 데이터를 가지고 있지 않고

     테이블을 참조해서 원하는 칼럼만 조회할 수 있게 함

 -> 데이터 딕셔너리에 SQL문 형태로 저장하되 실행 시에 참조됨

 

 [뷰 생성]

 CREATE VIEW 뷰 이름 AS SELECT * FROM 조회 할 테이블;

 [뷰 조회]

 SELECT * FROM 조회할 뷰 이름;

 [뷰 삭제]

 DROP VIEW 삭제할 뷰 이름;

 

· 뷰의 장단점

 [장점]

 1) 특정 칼럼만 조회가 가능하여 보안 기능이 있음

 2) 데이터 관리와 SELECT문이 간단해짐

 3) 한 개 테이블에 여러 개 뷰 생성이 가능

 

 [단점]

 1) 독자적인 인덱스 생성이 불가

 2) 삽입, 수정, 삭제 등 연산에 제약되고 데이터 구조 변경이 불가

 

[DCL]

· GRANT

   - 데이터베이스 사용자에게 권한을 부여하여 연결/입력/수정/삭제/조회가 가능

 

 [GRANT문]

 GRANT privileges ON object TO user;

 

* privileges은 권한을 의미

* user는 Oracle 데이터 베이스 사용자를 지정하면 됨

 

[privileges]

 1) SELECT : 지정된 테이블에 대해 SELECT 권한 부여

 2) INSERT : 지정된 테이블에 대해 INSERT 권한 부여

 3) UPDATE : 지정된 테이블에 대해 UPDATE 권한 부여

 4) DELETE : 지정된 테이블에 대해 DELETE 권한 부여

 5) REFERENCES : 지정된 테이블을 참조하는 제약조건 생성권한 부여

 6) ALTER : 지정된 테이블에 대해 수정할 수 있는 권한 부여

 7) INDEX : 지정된 테이블에 대해 인덱스 생성 권한 부여

 8) ALL : 테이블에 대한 모든 권한 부여

 

· WITH GRANT OPTIN

 - 특정 사용자에게 권한을 부여할 수 있는 권한을 부여

   즉, 권한을 A가 B에게 부여하고 B가 다시 C에게 부여한 후에 권한을 취소하면 모든 권한이 회수가 됨

 

· ADMIN OPTION

 - 테이블에 대한 모든 권한을 부여

   즉, 권한을 A가 B에게 부여하고 B가 다시 C에게 부여한 후 권한을 취소하면 B 사용자 권한만 취소

 

· REVOKE : 부여된 권한을 회수

 [REVOKE문]

 REVOKE privileges ON object FROM user;