[ORACLE] 기본 문법 5_ 데이터 정의어

데이터 정의어 (DDL:Data Definition Language)     (1) 데이터를 보관하고 관리하기 위해 제공되는 여러 객체(object)의     생성`변경`삭제 관련 기능을 수행하는 명령어     (2) 데이터 조작어(DML)과 달리 데이터 정의어(DDL)은 실행하면 자동으로 COMMIT된다.

자료형(data type)     (1) VARCHAR2(길이)         4000byte만큼의 가변 길이 문자열 데이터를 저장할 수 있다.     (2) NUMBER(전제 자릿수, 소수점 이하 자릿수)         . ±38 자릿수의 숫자를 저장할 수 있다.         . NUMBER(p, s)와 같이 표기할 경우 s자리만큼 소수점 이하 자릿수를 표현하고,           이 소수점 자리를 포함한 전체 p자리만큼 숫자 데이터를 저장한다.     (3) DATE         날짜 형식을 저장하기 위해 사용하는 자료형으로         세기, 년, 월, 일, 시, 분, 초 저장이 가능하다.     (4) CHAR(길이)         4000byte만큼의 고정 길이 문자열 데이터를 저장할 수 있다.

---

CREATE문     (1) 정의         데이터베이스 객체를 생성하는 데 사용하는 명령어     (2) 기본 형식         - 소유계정 이름은 생략 가능하다.         CREATE TABLE 소유계정.테이블 이름(             열1 이름 열1 자료형,             열2 이름 열2 자료형,             ...             열N 이름 열N 자료형         );     (3) 테이블 이름 생성 규칙         1. 테이블 이름은 문자로 시작해야 한다(한글도 가능하며 숫자로 시작할 수 없음)             ex) EMP90(O), 90EMP(X)         2. 테이블 이름은 30byte 이하여야 한다(즉 영어는 30글자, 한글은 15글자까지 사용 가능)         3. 같은 사용자 소유의 테이블 이름은 중복될 수 없다(SCOTT 계정에 두 EMP 테이블은 존재할 수 없음)         4. 테이블 이름은 영문자(한글 가능), 숫자(0-9)와 특수 문자 $, #, _를 사용할 수 있다.             ex) EMP#90_OB         5. SQL 키워드는 테이블 이름으로 사용할 수 없다(SELECT, FROM 등은 테이블 이름으로 사용 불가)     (4) 열 이름 생성 규칙         1. 열 이름은 문자로 시작해야 한다.         2. 열 이름은 30byte 이하여야 한다.         3. 한 테이블의 열 이름은 중복될 수 없다(EMP 테이블에 EMPNO 열이 두 개 존재할 수 없음)         4. 열 이름은 영문자(한글 가능), 숫자(0-9)와 특수 문자 $, #, _를 사용할 수 있다.         5. SQL 키워드는 열 이름으로 사용할 수 없다.

사용 예시↓

새 테이블을 생성하는 방법 4가지
1. 자료형을 각각 정의하여 새 테이블 생성하기

CREATE TABLE EMP_DDL(
    EMPNO       NUMBER(4),
    ENAME       VARCHAR2(10),
    JOB         VARCHAR2(9),
    MGR         NUMBER(4),
    HIREDATE    DATE,
    SAL         NUMBER(7, 2),       -- 자연수는 5자리까지, 소수점 이하는 2자리까지
    COMM        NUMBER(7, 2),
    DEPTNO      NUMBER(2)
);
DESC EMP_DDL;

 

2. 기존 테이블 열 구조와 데이터를 복사하여 새 테이블 생성하기

CREATE TABLE DEPT_DDL
    AS SELECT * FROM DEPT;

 

3. 기존 테이블 열 구조와 일부 데이터만 복사하여 새 테이블 생성하기

CREATE TABLE EMP_DDL_30
    AS SELECT *
        FROM EMP
    WHERE DEPTNO = 30;

4. 기존 테이블의 열 구조만 복사하여 새 테이블 생성하기

CREATE TABLE EMPDEPT_DDL
    AS SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM, E.DEPTNO, D.DNAME, D.LOC
        FROM EMP E, DEPT D
    WHERE 1<>1;

 

---

ALTER문     (1) 정의         데이터베이스 객체를 변경할 때 사용하는 명령어         ex) 테이블에 새 열을 추가 또는 삭제, 열의 자료형 또는 길이를 변경     (2) 종류         ADD      : 테이블에 열 추가         RENAME   : 열 이름 변경         MODIFY   : 열의 자료형 변경         DROP     : 특정 열을 삭제

 

사용 예시↓

> HP 열 추가하기

ALTER TABLE EMP_ALTER ADD HP VARCHAR2(20);

> HP열 이름을 TEL로 변경하기

ALTER TABLE EMP_ALTER RENAME COLUMN HP TO TEL;

> EMPNO 열 길이 변경하기

ALTER TABLE EMP_ALTER
    MODIFY EMPNO NUMBER(5);

> TEL 열 삭제하기

ALTER TABLE EMP_ALTER
    DROP COLUMN TEL;

---

RENAME     테이블 이름을 변경할 때 사용하는 명령어

 

사용 예시 ↓

> EMP_ALTER 테이블명을 EMP_RENAME으로 변경하기

RENAME EMP_ALTER TO EMP_RENAME;

---

TRUNCATE     특정 테이블의 모든 데이터를 삭제하는 명령어

 

사용 예시 ↓

> EMP_RENAME 테이블의 전체 데이터 삭제하기

TRUNCATE TABLE EMP_RENAME;

---

DROP     데이터베이스 객체를 삭제하는 명령어

사용 예시 ↓

> EMP_RENAME 테이블 삭제하기

DROP TABLE EMP_RENAME;

---

시퀀스  자동으로 번호를 부여하는 방법  시퀀스 생성  [1] INCREMENT BY 한번에 증가되는수 [2] START WITH 시작번호 [3] MAXVALUE 최대치 [4] MINVALUE 최소치 [5] NOCYCLE 번호가 MAX 에도달하면 다시 1이되는걸 방지 [6] NOCACHE 시퀀스 시작점을 여러개 설정할수있는것 NOCACHE 는 단일 시퀀스 이다.

 

사용 예시 ↓

CREATE SEQUENCE SEQ_DEPT
    INCREMENT BY 1   // 1씩 증가하고,
    START WITH 1  // 1부터 시작,
    MAXVALUE 5  // 최대치는 5 ==  5개만 만들 수 있음
    MINVALUE 1  // 최소치는 1
    NOCYCLE    // MAX에 도달하고 다시 1되는 것 방지
    NOCACHE;  // 단일 시퀀스 (시작점 여러개 설정할지 말지)

----------------------- 시퀀스 테이블 생성 --------------------------

INSERT INTO DEPT_SEQQ (DEPTNO, DNAME, LOC) VALUES (SEQ_DEPT.NEXTVAL , 'DATABASE' , 'SEOUL');
=> 6번 시행하면, 마지막엔 추가 오류가 뜸(최대치에 도달했기 때문)

DROP SEQUENCE SEQ_DEPT; // 시퀀스 테이블 삭제
COMMIT;

INSERT INTO DEPT_SEQQ (DEPTNO, DNAME, LOC) VALUES (11 , 'DATABASE' , 'SEOUL');
=> 시퀀스(숫자 자동 증가)를 삭제했으므로 추가 가능

 

 

---

NOT NULL  컬럼에 NOT NULL 옵션을 적용시키면 NULL 이나 빈값을 설정할수없다.

 

사용 예시 ↓

CREATE TABLE TEST_NOTNULL(
    LOGIN_ID VARCHAR(20) NOT NULL,
    LOGIN_PWD VARCHAR(20) NOT NULL,
    TEL VARCHAR(20)
);

INSERT INTO TEST_NOTNULL VALUES('TEST1' , NULL , '010-111-111');
=> 에러 발생 NULL 값 적용 불가능

---

PRIMARY KEY NOT NULL 과 UNIQUE 특성을 가지고있다. 빈값을 넣어서도 안되고 중복되는 값을 넣어서도안된다. 로그인할시 아이디나 , 넘버에 주로사용한다.  줄여서 PK 라고 한다.

사용 예시 ↓

CREATE TABLE TEST_PK(
LOGIN_ID VARCHAR(20) PRIMARY KEY,
LOGIN_PWD VARCHAR(20) NOT NULL,
TEL VARCHAR(20) );

INSERT INTO TEST_PK VALUES(NULL , '1234' , '010-111-111');
=> 에러 발생, NULL 값 적용 불가능

INSERT INTO TEST_PK (LOGIN_ID , LOGIN_PWD , TEL )VALUES('TEST1' , '1234' , '010-111-111');
=> 아래내용을 두번 적용시켜보자 ( 아이디가 중복되므로 두번째는 에러가 발생된다)