[ORACLE] 06. CREATE(DDL)

DQL (QUERY 질의) : SELECT
DML (MANIPULATION 조작) : [SELECT], INSERT, UPDATE, DELETE
DDL (DEFINITION 정의) : CREATE, ALTER, DROP
DCL (CONTROLL 제어) : GRANT, REVOKE, [COMMIT, ROLLBACK]
TCL (TRANSACTION) : COMMIT, ROLLBACK

DDL(DATA DEFINITION LANGUAGE)

: 데이터 정의 언어

오라클에서 제공하는 객체(OBJECT)들을 새로이 만들고(CREATE), 구조를 변경(ALTER)하고, 구조 자체를 삭제(DROP)하는 구문
주로 DB설계자, DB관리자가 사용하는 구문
오라클에서의 객체(구조) : 테이블(TABLE), 뷰(VIEW), 시퀀스(SEQUENCE), 인덱스(INDEX), 패키지(PACKAGE), 트리거(TRIGGER), 프로시저(PROCEDURE), 함수(FUNCTION), 동의어(SYNONYM), 사용자(USER)

CREATE : 다양한 객체(구조)들 생성하는 구문

1. 테이블 생성

테이블 : 행(ROW)과 열(COLUMN)로 구성되는 가장 기본적인 데이터베이스 객체 데이터들을 보관하기 위한 제일 핵심적인 객체
[표현법] CREATE TABLE 테이블명( 컬럼명 자료형(크기), 컬럼명 자료형(크기), 컬럼명 자료형, ... );
자료형 (DATA TYPE)
- 문자 (CHAR(크기) / VARCHAR2(크기))
  - CHAR(크기) : 최대 2000BYTE까지 지정 가능 /
    고정길이 (아무리 적은 값은 들어와도 처음 할당한 크기 그대로 유지/공백으로라도 채워서!)
    →고정적인 길이의 데이터가 들어올 예정일 경우 사용
  - VARCHAR2(크기) : 최대 4000BYTE까지 지정 가능 /
    가변길이 (담기는 값에 따라서 공간의 크기 맞춰짐)
    →유동적인 길이의 데이터가 들어올 예정일 경우 사용
- 숫자 (NUMBER)
- 날짜 (DATE)

--> 회원에 대한 데이터를 담기위한 테이블 MEMBER
CREATE TABLE MEMBER(
	MEM_NO NUMBER,
	MEM_ID VARCHAR2(20),
	MEM_PWD VARCHAR2(20),
	MEM_NAME VARCHAR2(20),
	GENDER CHAR(3),
	PHONE CHAR(13),
	EMAIL VARCHAR2(50),
	MEM_DATE DATE
);

2. 컬럼에 주석 달기 (컬럼에 대한 설명같은 것)

[표현법] COMMENT ON COLUMN 테이블명.컬럼명 IS '주석내용';

COMMENT ON COLUMN MEMBER.MEM_NO IS '회원번호';
COMMENT ON COLUMN MEMBER.MEM_ID IS '아이디';
COMMENT ON COLUMN MEMBER.MEM_PWD IS '비밀번호';
COMMENT ON COLUMN MEMBER.MEM_NAME IS '이름';
COMMENT ON COLUMN MEMBER.GENDER IS '성별(남/여)';
COMMENT ON COLUMN MEMBER.PHONE IS '전화번호';
COMMENT ON COLUMN MEMBER.EMAIL IS '이메일';
COMMENT ON COLUMN MEMBER.MEM_DATE IS '가입일';

제약조건 CONSTRAINTS

원하는 데이터값(유효한값)만 유지하기 위해서 특정 컬럼에 설정하는 제약
데이터 무결성 보장을 목적으로 한다.
종류 : NOT NULL, UNIQUE, CHECK(조건), PRIMARY KEY, FOREIGN KEY

NOT NULL 제약조건

해당 컬럼에 반드시 값이 존재해야만 할 경우 (즉, 해당 컬럼에 NULL이 들어와서는 안되는 경우)
삽입/수정시 NULL값을 허용하지 않도록 제한
제약조건을 부여하는 방식이 크게 2가지 있음 (컬럼레벨방식 / 테이블레벨방식)
- NOT NULL 제약조건 부여시 컬럼레벨방식만 사용가능

-- 컬럼레벨 방식 : 컬럼명 자료형 제약조건
CREATE TABLE MEM_NOTNULL(
	MEM_NO NUMBER NOT NULL,
	MEM_ID VARCHAR2(20) NOT NULL,
	MEM_PWD VARCHAR2(20) NOT NULL,
	MEM_NAME VARCHAR2(20) NOT NULL,
	GENDER CHAR(3),
	PHONE CHAR(13),
	EMAIL VARCHAR2(50)
);

UNIQUE 제약조건

컬럼값에 중복값을 제한하는 제약조건
삽입/수정시 기존에 있는 데이터값 중 중복값이 있을 경우 오류 발생
컬럼레벨방식 / 테이블레벨 방식 둘 다 가능

CREATE TABLE MEM_UNIQUE(
	MEM_NO NUMBER NOT NULL,
	MEM_ID VARCHAR2(20) NOT NULL UNIQUE, --> 컬럼레벨방식
	MEM_PWD VARCHAR2(20) NOT NULL,
	MEM_NAME VARCHAR2(20) NOT NULL,
	GENDER CHAR(3),
	PHONE CHAR(13),
	EMAIL VARCHAR2(50)
);


-- 테이블레벨 방식 : 모든 컬럼들 다 나열한 후 마지막쪽에 기술
--                 제약조건(컬럼명)
CREATE TABLE MEM_UNIQUE(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE CHAR(13),
    EMAIL VARCHAR2(50),
    UNIQUE(MEM_ID) --> 테이블 레벨 방식
);

제약조건명까지 지어주면서 제약조건을 부여하는 표현법

컬럼 레벨 방식
- CREATE TABLE 테이블명(
  컬럼명 자료형 [CONSTRAINT 제약조건명] 제약조건,
  컬럼명 자료형
  );
테이블 레벨 방식
- CREATE TABLE 테이블명(
  컬럼명 자료형,
  컬럼명 자료형,
  [CONSTRAINT 제약조건명] 제약조건(컬럼명)
  );

CREATE TABLE MEM_UNIQUE(
	MEM_NO NUMBER CONSTRAINT MEMNO_NN NOT NULL,
	MEM_ID VARCHAR2(20) CONSTRAINT MEMID_NN NOT NULL,
	MEM_PWD VARCHAR2(20) CONSTRAINT MEMPWD_NN NOT NULL,
	MEM_NAME VARCHAR2(20) CONSTRAINT MEMNM_NN NOT NULL,
	GENDER CHAR(3),
	PHONE CHAR(13),
	EMAIL VARCHAR2(50),
	CONSTRAINT MEMID_UQ UNIQUE(MEM_ID) --> 테이블 레벨 방식
);

CHECK(조건) 제약조건

특정 컬럼에 들어올 값에 대한 조건을 제시해둘 수 있음
해당 조건에 만족하는 데이터값만 담길 수 있음

CREATE TABLE MEM_CHECK(
	MEM_NO NUMBER NOT NULL,
	MEM_ID VARCHAR2(20) NOT NULL,
	MEM_PWD VARCHAR2(20) NOT NULL,
	MEM_NAME VARCHAR2(20) NOT NULL,
	GENDER CHAR(3) CONSTRAINT GENDER_CK CHECK(GENDER IN ('남', '여')), -- 컬럼레벨방식
	PHONE CHAR(13),
	EMAIL VARCHAR2(50),
	UNIQUE(MEM_ID)
	--, CONSTRAINT GENDER_CK CHECK(GENDER IN ('남', '여')) -- 테이블레벨방식
);

PRIMARY KEY(기본키) 제약조건

테이블에서 각 행의 정보를 식별하기 위해 사용될 컬럼에 부여하는 제약조건 (식별자의 역할)
- EX) 학번, 회원번호, 사원번호, 부서코드, 직급코드, 주문번호, 예약번호, 운송장번호, ...
PRIMARY KEY 제약조건을 부여하게 되면 해당 그 컬럼에 NOT NULL+UNIQUE 제약조건을 의미
주의할점 : 한 테이블당 한개만 설정 가능

CREATE TABLE MEM_PRIMARYKEY(
	MEM_NO NUMBER CONSTRAINT MEMNO_PK PRIMARY KEY, --> 컬럼레벨 방식
	MEM_ID VARCHAR2(20) NOT NULL,
	MEM_PWD VARCHAR2(20) NOT NULL,
	MEM_NAME VARCHAR2(20) NOT NULL,
	GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),
	PHONE CHAR(13),
	EMAIL VARCHAR2(50),
	UNIQUE(MEM_ID)
	-- , CONSTRAINT MEMNO_PK PRIMARY KEY(MEM_NO) --> 테이블레벨 방식
);

CREATE TABLE MEM_PRIMARYKEY2(
	MEM_NO NUMBER,
	MEM_ID VARCHAR2(20),
	MEM_PWD VARCHAR2(20) NOT NULL,
	MEM_NAME VARCHAR2(20) NOT NULL,
	GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),
	PHONE CHAR(13),
	EMAIL VARCHAR2(50),
	PRIMARY KEY(MEM_NO, MEM_ID) --> 묶어서 PRIMARY KEY제약조건 부여 (복합키)
);

FOREIGN KEY(외래키) 제약조건

다른테이블에 존재하는 값만 들어와야되는 특정 컬럼에 부여하는 제약조건
→ 다른 테이블을 참조한다고 표현
→ FOREIGN KEY 제약조건에 의해 테이블 간의 관계가 형성됨!
- 컬럼레벨방식 컬럼명 자료형 [CONSTRAINT 제약조건명] REFERENCES 참조할테이블명[(참조할컬럼명)]
- 테이블레벨방식 [CONSTRAINT 제약조건명] FOREIGN KEY(컬럼명) REFERENCES 참조할테이블명[(참조할컬럼명)]
- 공통 : 참조할컬럼명 생략시 참조할테이블에 PRIMARY KEY로 잡혀있는 컬럼으로 매칭

CREATE TABLE MEM(
	MEM_NO NUMBER PRIMARY KEY,
	MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
	MEM_PWD VARCHAR2(20) NOT NULL,
	MEM_NAME VARCHAR2(20) NOT NULL,
	GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),
	PHONE CHAR(13),
	EMAIL VARCHAR2(50),
	GRADE_ID NUMBER, --REFERENCES MEM_GRADE --(GRADE_CODE) --> 컬럼레벨방식
	FOREIGN KEY(GRADE_ID) REFERENCES MEM_GRADE--(GRADE_CODE) --> 테이블레벨방식
);

-- 부모테이블(MEM_GRADE) -|--------<-(1 대 다) 자식테이블(MEM)

-- 전체 회원의 회원번호, 회원명, 등급명
-- 오라클구문
SELECT MEM_NO, MEM_NAME, GRADE_NAME
FROM MEM, MEM_GRADE
WHERE GRADE_ID = GRADE_CODE(+);

-- ANSI구문
SELECT MEM_NO, MEM_NAME, GRADE_NAME
FROM MEM
LEFT JOIN MEM_GRADE ON (GRADE_ID = GRADE_CODE);

--> 부모테이블(MEM_GRADE)에서 데이터값을 삭제할 경우 어떤 문제?

--> 10번등급 삭제!!
-- 데이터삭제 : DELETE FROM 테이블명 WHERE 조건식;
DELETE FROM MEM_GRADE
WHERE GRADE_CODE = 10;
--> 자식테이블(MEM) 중에 10이라는 값을 사용하고 있기 때문에 삭제가 안됨!!
--> 자식테이블에 사용하고 있는 값이 있을 경우 부모테이블로부터 삭제가 안되는
-- "삭제제한" 옵션이 걸려있음!!

DELETE FROM MEM_GRADE
WHERE GRADE_CODE = 30;

INSERT INTO MEM_GRADE
VALUES(30, '특별회원');

→자식테이블 생성시 외래키 제약조건 부여할 때 삭제옵션 부여가능

삭제옵션

부모테이블의 데이터 삭제시 그 데이터를 사용하고 있는 자식테이블의 값을 어떻게 처리할 건지에 대한 옵션
FOREIGN KEY 삭제옵션
- ON DELETE RESTRICTED (삭제제한) : 자식데이터로 쓰이는 부모데이터는 삭제 아예 안되게끔 (기본값)
- ON DELETE SET NULL : 부모데이터 삭제시 해당데이터를 쓰고있는 자식데이터의 값을 NULL로 변경
- ON DELETE CASCADE : 부모데이터 삭제시 해당데이터를 쓰고있는 자식데이터도 같이 삭제시키는

-- ON DELETE SET NULL : 부모데이터 삭제시 해당데이터를 쓰고있는 자식데이터의 값을 NULL로 변경
DROP TABLE MEM;
CREATE TABLE MEM(
	MEM_NO NUMBER PRIMARY KEY,
	MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
	MEM_PWD VARCHAR2(20) NOT NULL,
	MEM_NAME VARCHAR2(20) NOT NULL,
	GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),
	PHONE CHAR(13),
	EMAIL VARCHAR2(50),
	GRADE_ID NUMBER REFERENCES MEM_GRADE(GRADE_CODE) ON DELETE SET NULL
);

INSERT INTO MEM
VALUES(1, 'user01', 'pass01', '홍길동', null, null, null, 10);

INSERT INTO MEM
VALUES(2, 'user02', 'pass02', '김말똥', null, null, null, 20);

INSERT INTO MEM
VALUES(3, 'user03', 'pass03', '안중근', null, null, null, 10);

INSERT INTO MEM
VALUES(4, 'user04', 'pass04', '강개순', null, null, null, null);

-- 10번 등급 삭제
DELETE FROM MEM_GRADE
WHERE GRADE_CODE = 10;
--> 잘 삭제됨 (단, 10을 가져다 쓰고있던 자식데이터값도 NULL로 변경)

ROLLBACK;

-- ON DELETE CASCADE : 부모데이터 삭제시 해당데이터를 쓰고있는 자식데이터도 같이 삭제시키는
DROP TABLE MEM;
CREATE TABLE MEM(
	MEM_NO NUMBER PRIMARY KEY,
	MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
	MEM_PWD VARCHAR2(20) NOT NULL,
	MEM_NAME VARCHAR2(20) NOT NULL,
	GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),
	PHONE CHAR(13),
	EMAIL VARCHAR2(50),
	GRADE_ID NUMBER REFERENCES MEM_GRADE(GRADE_CODE) ON DELETE CASCADE
);

INSERT INTO MEM
VALUES(1, 'user01', 'pass01', '홍길동', null, null, null, 10);

INSERT INTO MEM
VALUES(2, 'user02', 'pass02', '김말똥', null, null, null, 20);

INSERT INTO MEM
VALUES(3, 'user03', 'pass03', '안중근', null, null, null, 10);

INSERT INTO MEM
VALUES(4, 'user04', 'pass04', '강개순', null, null, null, null);

DELETE FROM MEM_GRADE
WHERE GRADE_CODE = 10;
--> 잘 삭제됨 (단, 해당 데이터를 사용하고 있던 자식데이터도 같이 DELETE됨)

DEFAULT 기본값

컬럼을 선정하지 않고 INSERT시 NULL이 아닌 기본값을 INSERT하고자 할 때 세팅해둘 수 있는 값

CREATE TABLE MEMBER(
  MEM_NO NUMBER PRIMARY KEY,
  MEM_NAME VARCHAR2(20) NOT NULL,
  MEM_AGE NUMBER,
  ENROLL_DATE DATE DEFAULT SYSDATE
);

-- INSERT INTO 테이블명 VALUES(컬럼값, 컬럼값, 컬럼값, 컬럼값);
INSERT INTO MEMBER VALUES(1, '홍길동', 20, SYSDATE);
INSERT INTO MEMBER VALUES(2, '김말똥', NULL, NULL);

-- INSERT INTO 테이블명(컬럼명, 컬럼명) VALUES(컬럼값, 컬럼값);
INSERT INTO MEMBER(MEM_NO, MEM_NAME) VALUES(3, '강개순');
--> 선택되지 않은 컬럼에는 기본적으로 NULL이 들어감
--> 단, 해당 컬럼에 DEFAULT값이 부여되어있을 경우 NULL이 아닌 DEFAULT값이 들어감

SUBQUERY를 이용한 테이블 생성 (테이블 복사 개념)

[표현식] CREATE TABLE 테이블명 AS 서브쿼리;

-- EMPLOYEE 테이블을 복제한 새로운 테이블 생성
DROP TABLE EMPLOYEE_COPY;
CREATE TABLE EMPLOYEE_COPY
AS SELECT *
FROM EMPLOYEE;
--> 컬럼, 데이터값, 제약조건 같은 경우 NOT NULL만 복사됨

SELECT * FROM EMPLOYEE_COPY;

CREATE TABLE EMPLYOEE_COPY2
AS SELECT EMP_ID, EMP_NAME, SALARY, BONUS
FROM EMPLOYEE
WHERE 1=0; --> 구조만을 복사하고자 할 때 쓰이는 구문 (데이터값은 필요없을 때)

CREATE TABLE EMPLOYEE_COPY3
AS SELECT EMP_ID, EMP_NAME, SALARY, SALARY*12 "연봉"
FROM EMPLOYEE;
--> 서브쿼리 SELECT절에 산술식 또는 함수식 기술된 경우 반드시 별칭 지정해야됨

SELECT EMP_NAME, 연봉
FROM EMPLOYEE_COPY3;

테이블 다 생성된 후에 뒤늦게 제약조건 추가

ALTER TABLE 테이블명 변경할내용;
- PRIMARY KEY : ALTER TABLE 테이블명 ADD PRIMARY KEY(컬럼명);
- FOREIGN KEY : ALTER TABLE 테이블명 ADD FOREIFN KEY(컬럼명) REFERENCES 참조할테이블명[(참조할컬럼명)];
- UNIQUE : ALTER TABLE 테이블명 ADD UNIQUE(컬럼명);
- CHECK : ALTER TABLE 테이블명 ADD CHECK(컬럼에대한 조건);
- NOT NULL : ALTER TABLE 테이블명 MODIFY 컬럼명 NOT NULL;

-- EMPLOYEE_COPY 테이블에 PRIMARY KEY제약조건 추가(EMP_ID)
ALTER TABLE EMPLOYEE_COPY ADD PRIMARY KEY(EMP_ID);

-- EMPLOYEE 테이블에 DEPT_CODE에 외래키제약조건 추가 (참조하는테이블(부모) : DEPARTMENT(DEPT_ID))
ALTER TABLE EMPLOYEE ADD FOREIGN KEY(DEPT_CODE) REFERENCES DEPARTMENT; --(DEPT_ID);

-- EMPLOYEE 테이블 JOB_CODE에 외래키제약조건 추가 (참조하는테이블 : JOB)
ALTER TABLE EMPLOYEE ADD FOREIGN KEY(JOB_CODE) REFERENCES JOB;

-- DEPARTMENT 테이블에 LOCATION_ID에 외래키제약조건 추가 (참조하는테이블:LOCATION)
ALTER TABLE DEPARTMENT ADD FOREIGN KEY(LOCATION_ID) REFERENCES LOCATION;

'IT개발 과정 [취성패 학원] > ORACLE' 카테고리의 다른 글

[ORACLE] 05. SUBQUARY (0)	2021.04.07
[ORACLE] 04. JOIN (0)	2021.04.07
[ORACLE] 03. GROUP BY&HAVING (0)	2021.04.02
[ORACLE] 02. 함수 (FUNCTION) (0)	2021.03.31
[ORACLE] 01. 기본문법 (0)	2021.03.30

[ORACLE] 06. CREATE(DDL)

DDL(DATA DEFINITION LANGUAGE)

CREATE : 다양한 객체(구조)들 생성하는 구문

제약조건 CONSTRAINTS

'IT개발 과정 [취성패 학원] > ORACLE' 카테고리의 다른 글

'IT개발 과정 [취성패 학원]/ORACLE' Related Articles

티스토리툴바