[복습] SQL | Oracle SQL의 분류(DDL, DML, DCL, TCL, DQL) 중 DDL의 CREATE + 복습후기(20231106)

오늘 배운 내용은 SQLD 시험에 나오는 내용들이다.

 

SQL의 분류

1. DDL(Data Definition Lanuage): 객체를 정의하는 언어. 객체 자체를 만들고 삭제하고 수정 등을 수행한다(auto commit).

  - CREATE, DROP, ALTER, TRUNCATE(데이터를 삭제하는데 2.에도 DELETE가 있지만 TRUNCATE는 DDL에 속한다. auto commit이기 때문이다.)

 

  1) create:

 

object_name이 들어오는 것처럼 객체에 대한 이해가 중요하다.

제일 많이 쓰이는 create는 create table 이다.

 

★ 테이블 명명 규칙(변수의 명명규칙과 비슷)

  - 테이블 이름은 반드시 문자로 시작(숫자 시작 불가). 

    특수문자 가능(" "로 감싸야 하나 사용을 권장하지 않는다). 그러나 언더바(_)는 사용 가능하며 쌍따옴표로 묶지 않아도 된다. 문자로 지칭되는 것들 중에 언더바도 포함됨.

  - 최대 30바이트까지 가능(character set에 따라 한글은 2바이트나 3바이트 일 수 있음)

  - 다른 사용자는 다른 오브젝트들의 이름을 중복 사용할 수 있음(소유자가 다르면 같은 객체 이름 사용 가능).

  - 테이블 이름이나 오브젝트 이름을 오라클이 사용하는 키워드를 사용해서는 안됨(예를 들어 테이블 이름을 select라고 하지 말 것). 즉 예약어(이미 만들어진 함수명, 문법) 가급적 사용 지양(사용은 가능)

 

 

테이블 생성 예

  - number type는 사이즈 지정하지 않아도 됨(생략 가능). 소수점 데이터를 정의할 때에는 number(n, m)으로 표현함. 총 n자리 수 중 m자리 소수점 자리라는 의미. (7,2)면 7자리 수 중에서 소수점 자리가 2자리.

  - 날짜에는 사이즈 지정 하면 안됨. 에러 발생.

  - varchar2는 사이즈 지정 필수 ★ 없으면 에러 발생

  - char는 사이즈 명시 없이도 만들어짐. desc로 확인하면 사이즈 1로 들어감. CHAR(1) ★

 

각 컬럼마다 default값 명시 가능하다.

여기서 말하는 ★default 값이란 데이터를 입력할 때 특정 컬럼에 대한 언급을 하지 않을 때 null 대신에 할당값으로 테이블 생성 시 정의 가능. 생성후에도 alter 명령어로 수정 가능

 

CTAS 테이블 생성

CTAS로 테이블 생성은 많이 해보았다.

emp table을 이용하여 test4테이블을 생성해보자.

emp테이블 구조와 test4 테이블 구조는 같은데 한 가지 다른 게 보인다.

EMPNO의 Nullable에 NOT NULL의 차이가 있다.

신규 사원이 들어왔다고 가정했을 때 emp 테이블에 추가를 하게 될 때, 이 데이터가 잘못 들어오게 하지 않으려면 어떤 규칙이 있어야 할까?

중복값을 허용하지 않는다, null을 입력할 수 없다 등이 제약조건이다.

여기서 테이블을 복사하면 제약조건은 복제되지 않는다. 그러나 not null은 복제 된다.

이 말은 무슨 뜻인가. 

즉, PK(기본키)가 제약조건 중 일부인데 not null의 속성을 가진다. 이 테이블에서는 not null을 넣은 게 아니라 PK로 인하여 not null이 들어가게 된 것이다. 

 

다른 예를 들어보면 더 잘 이해가 갈 것이다.

CTAS를 이용하여 student 테이블을 test5로 복제하여라.

 

즉 PK인 STUDNO는 복제가 되지 않았고 NOT NULL은 복제되었다.

 

CTAS로 복제시 따라가지 않는 것은 PK.

CTAS로 복제시 따라가지 않는 것은 NOT NULL이라고 하면 틀린 답이다.

 

위 방법으로 CTAS 하게 되면 데이터들도 복사가 되는데 테이블 레이아웃 자체만 copy 하고 싶으면 아래와 같은 방법의 CTAS를 실행하면 된다.

 

위와 같이 데이터 테이블만 나오는 것을 확인할 수 있다.

즉 항상 거짓인 조건을 사용하여 0건의 데이터가 출력되게 한 뒤 CTAS로 테이블 생성하면 데이터 없이 구조만 복제된다.

이런식으로도 컬럼 순서를 바꾸고 컬럼명을 바꿀 수도 있다.

 

위에 언급된 제약조건을 알아보고 넘어가지

제약조건

1) primary key(PK, 기본키): 각 행의 유일한(not null + unique) 식별자

2) unique key(UK, 고유키): 해당 컬럼은 중복값이 들어올 수 없다(전화번호, 주민번호 등), 절대 중복을 허용하지 않는 제약. 여기서 null은 중복이 될 수 없다. null은 아직 정의되지 않은 값이므로 중복이라고 볼 수 없기 때문이다. 따라서 null이 들어갈 수 있다★(null 허용)

3) foreign key(FK, 외래키): 제약 조건을 다른 곳에서 찾아온다. 예를 들면 emp 테이블을 관리하는데 아래 홍길동 신입사원의 데이터를 emp테이블에 넣으려고 한다.

 

그런데 오류가 생긴다(ORA-02291: 무결성 제약조건(SCOTT.FK_DEPTNO)이 위배되었습니다-부모 키가 없습니다.)

여기서 emp는 자식테이블, dept 테이블은 부모테이블이기 때문이다. deptno는 10, 20, 30, 40까지만 명시되어있어서 50을 넣으려고 하면 에러가 발생하는 것이다. 즉 emp테이블은 다른 테이블(외래)에서 제약 조건을 찾아오는데 이런 키를 FK라고 하는 것이다. 다시 말해 무결성 조건이 다른 테이블을 참조하는 경우의 제약을 의미한다.

4) CHECK: 특정 범위나 범주 제한 시 사용.

예를 들면 grade 컬럼에 6이 들어가서는 안된다. 이 의미는 in (1,2,3,4)중에 하나처럼 범주를 제한하는 것이다.

5) NOT NULL: null을 허용하지 않음.

 

업무적으로 여러 데이터가 들어오게 될 때 DB에 잘못 들어오는 순간 그 회사 데이터는 복잡해지는 것이다.

그래서 모델러가 제약 조건을 꼼꼼하게 설계를 하는데, 1차 제약은 보통 개발자가 프로그래밍을 할 때 설계한다.

DB가 지저분한 것은 모델러가 모델 설계를 잘못했거나 개발자가 엉망이거나 둘 중 하나일 것이다.

 

★ 가상컬럼: 데이터 입력, 수정 등이 불가하다. 실존하는 컬럼이 아니므로 조회만 가능하다.

- 실존하지는 않지만(저장공간도 없음) 마치 있는 것처럼 조회되는 컬럼

- 업무적으로 여러 컬럼으로 인해 파생되는 값을 자주 사용하는 경우 주로 생성

  예를들면 주민번호가 있는 경우 성별 컬럼은 굳이 만들지 않아도 된다. 가상으로 성별 컬럼을 주민번호 컬럼을 사용하여 만들면 되기 때문이다.

- 가상컬럼의 데이터는 수정이 불가(insert, update 등 불가)

- 기타 오브젝트(index, 제약조건 등)등의 객체 생성 불가

- 저장된 컬럼의 데이터 변경 시 가상 컬럼 데이터도 그에 맞게 즉시 변경되어 출력(실시간 반영)

 

 

이건 참고로 알아두자.

create table 후에 insert to ~ 작업을 수행했다.

여기서 insert to 후에 commit을 하지 않고 ctas 작업을 수행하고 어떠한 작업에 대해 delete를 했다.

이 직후 rollback 하면 어느 시점에서 rollback이 수행되는가?

commit을 하지 않은 insert 시점 전으로 갈 것 같지만, ctas를 수행하고 나면 그 전의 insert는 commit이 된다. 

즉 DDL은 DDL 직후 commit이 찍히기 때문에 DML에 대하여 commit을 찍지 않아도 확정이 되는 현상이 발생한다. 따라서 delete에 대해서만 rollback이 적용된다. 즉 rollback은 바로 직전까지만 rollback된다. 

어디까지 rollback이 되는지도 SQLD에 나온다고 한다. ★

 

DDL의 설명 중 CREATE까지 밖에 오늘은 하지 못했다.

내일은 DDL의 나머지를 배울 예정이다.

 

 

 

 

 

------------------------------------------------------------------------------------------------------------------------------

 

 

2. DML(Data Manipulation Language): 객체는 변화가 없고 데이터를 수정하는 언어이다.

  - DELETE, UPDATE, INSERT, MERGE

 

3. DCL(Data Control Language): 데이터를 컨트롤(제어)하는 언어

  - GRANT(권한 넣기), REVOKE(권한 빼기)

 

4. TCL(Transaction Control Language): 트랜잭션 제어 언어(트랜잭션 제어 언어가 필요한 순간은 DML 뿐이다. Data 수정이 완료 되었다고 명시하여야 하기 때문이다. 반드시 필요하다).
  - commit, rollback

delect from emp

  where deptno = 20; 

까지만 수행하고 commit하지 않으면 result 창에는 보이지 않으나 다른 orange를 수행하여 emp를 열어보면 보인다.

  

5. DQL(Data Query Language): 데이터 질의 언어

  - SELECT

 

 

★ drop, delete, truncate는 모두 삭제와 관련된 행위이다(차이 SQLD에 많이 나옴).

  - drop: 객체를 지움

  - delete: 데이터를 지움(테이블은 남아있음). delete는 where절을 쓸 수 있음(즉 원하는 데이터를 지울 수 있음). auto commit이 아님

  - truncate: 데이터를 지움. where절 쓸 수 없음(즉 1. 데이터의 삭제 범위가 다름), auto commit 임.

orecle 구조를 보면 redo라는 공간(buffer)(메모리 영역)과 redo file(디스크 영역)이 있다. redo buffer가 사용자가 지운 내용을 보관하고 있다. redo buffer에 있는 내용을 주기적으로 redo file로 보내게 되는데, 왜냐하면 메모리가 꺼지면 버퍼에 있는 내용은 날아가기 때문이다. 즉 buffer 내용은 전원 공급이 원활하지 않으면 사라지기 때문에 디스크로 보내게 된다. 우리가 작성하는 것들도 ctrl + s 하지 않으면 메모리상 기록일 뿐이다. redo file도 copy본을 만드는데 이걸 아카이브 로그 파일이라고 한다. 여기서 delete는 로그에 기록하고 truncate는 로그에 기록하지 않는다.

 

 ※ 참고: 잘못 수정한 데이터(insert, update, delete)에 대한 복구

 insert into emp

(as 없음)

select *

  from emp as of timestamp(to_timestamp('2023/11/06 13:00', 'YYYY/MM/DD HH24:MI'));

commit;

의미: 13시 데이터로 복구하겠다.

 

현업에서는 (as가 있는 것처럼 읽어서) ITAS(아이타스)라고 읽는다.

 

 

복습후기

서브쿼리가 드디어 끝났다.

그런데 서브쿼리와 조인보다 DDL, DQL, DCL 이런 게 더 어려운 것 같다.

내일 더 배워보면 알겠지...?