11.MySql(scott)-기본문법 - DDL(create, drop,truncate,alter) 의 이해

1.DDL 이란?

• 데이터베이스 구조(객체)를 생성, 수정, 삭제하는 데 사용
• 실제 DB에 즉각 반영되며, DB사전에 정보를 기록함

DDL 종류

• CREATE
• ALTER
• DROP
• RENAME
• TRUNCATE

스키마(schema): 특정 사용자가 DB에 접근해 생성한 객체들의 대표이름

• 일반적으로 사용자의 계정명과 동일
• 생성한 객체들은 생성한 계정이 소유하며, 다른 스키마는 기본적으로 접근이 불가함 (권한 할당 필요)
• 권한을 가진 사용자가 다른 스키마에 접근하기 위해선 ‘스키마.객체명’ 형식으로 접근해야 함

SELECT * FROM scott.dept; -- 스키마.객체명

2.CREATE: 테이블 생성

테이블 생성을 위해서는 각각의 DB에서 제공하는 테이터 타입을 먼저 살펴 보아야함.

3.CREATE: 테이블 생성

• DB객체 이름 지정방법
  • 테이블, 컬럼명은 문자로 시작하며 1 ~ 30자 이내로 작성(30byte)
  • 테이블, 컬럼명은 영어 대소문자, 숫자, 특수문자(_,$,#) 로만 작성
    • 한글 작성도 가능하지만 권장하지 않음
  • 동일한 사용자의 다른 객체와 이름이 중복되면 안됨
  • 예약어 사용 불가
  • 대소문자 구분 안함

CREATE TABLE employee
(empno      int,
 ename      VARCHAR(20),
 hiredate   datetime default null,
 sal        double default null);

• DEFAULT: 해당 컬럼에 값을 지정하지 않으면 자동으로 디폴트값이 입력됨
  • NULL값이 저장되는 것을 방지

-- 일반 컬럼 --
CREATE TABLE def_table1
(num        NUMBER(2),
 writeday   DATE);

INSERT INTO def_table1(num)
VALUES(20); -- 20, (null)

INSERT INTO def_table1(num, writeday)
VALUES(2, DEFAULT); -- 2, (null)

-- DEFAULT 적용 컬럼 --
CREATE TABLE def_table2
(num        NUMBER(2),
 writeday   DATE DEFAULT SYSDATE);

INSERT INTO def_table2(num)
VALUES(20); -- 20, 21/01/14

INSERT INTO def_table2(num, writeday)
VALUES(2, DEFAULT); -- 2, 21/01/14

4.테이블 삭제(DROP, TRUNCATE)

• DROP: 테이블 삭제
  • 테이블에 저장된 모든 데이터, 인덱스 및 제약조건이 전부 삭제됨 (FK 제외)
  • FK까지 삭제하려면 CASCADE CONSTRAINTS 옵션 사용

DROP TABLE dept_new CASCADE CONSTRAINTS;
SHOW RECYCLEBIN; -- DB휴지통 보기
FLASHBACK TABLE dept_new TO BEFORE DROP;

TRUNCATE: 테이블 잘라내기

• 테이블의 모든 행들을 삭제함
• 테이블이 사용하던 저장공간을 해제 (DELETE는 저장공간을 해제하진 않음)
• ROLLBACK 정보를 발생시키지 않아 DELETE보다 수행속도가 빠르지만 ROLLBACK이 불가함

TRUNCATE TABLE 테이블명;

5.테이블 변경 (ALTER)

RENAME: 테이블 이름 변경

RENAME 테이블명 TO 새 테이블명;

+ALTER: 테이블 변경

• 컬럼: 새 컬럼 추가, 기존 컬럼 수정/삭제/이름변경
• 제약조건: 추가/삭제, 활성화/비활성화
• 테이블 읽기모드(READ ONLY)

컬럼추가

ALTER TABLE emp04
ADD (email		VARCHAR2(10),
	 address	VARCHAR2(20));

DELETE vs DROP vs TRUNCATE 차이

DELETE

WHERE 절을 사용하여 특정 조건의 레코드를 삭제할 수도 있고, 모든 레코드를 삭제할 수도 있다.
DELETE된 데이터는 COMMIT(END) 명령어를 실행하기 이전에 ROLLBACK 명령어를 이용하여 복구할 수 있다.

TRUNCATE

테이블의 모든 레코드를 삭제하는 데 사용하는 구문이다.
DROP 문과 차이점은 TRUNCATE는 Table의 레코드들만 모두 날리는 것이기 때문에 테이블 구조는 남아있다
모든 레코드가 삭제되며, 자동 COMMIT이 되는 명령어라서 복구할 수 없다.

DROP

데이터베이스에서 객체(테이블, 뷰, 인덱스 등)를 완전히 삭제할 때 사용하는 명령어다

컬럼 변경

• 숫자, 문자 컬럼의 전체 길이 증가/축소
  • 축소는 모든 행의 컬럼값이 NULL이거나 행이 없는경우 가능
• 데이터 타입 변경
  • 모든 행의 컬럼값이 NULL일 때만 가능
• 디폴트 값을 변경하면 변경 이후에 입력된 행에만 적용됨

ALTER TABLE scott_t
MODIFY (num		NUMBER(6),
        name	VARCHAR2(20));

컬럼 삭제

• CASCADE CONSTRAINTS 옵션 사용하면 해당 컬럼 참조하는 모든 제약조건 삭제됨
• 삭제된 컬럼이 포함된 모든 제약조건도 삭제

ALTER TABLE emp04
DROP (email);

https://cheeseb.github.io/mysql/sql-ddl2/