4일차 DDL, DML

1. DDL(Data Definition Language) 데이터 정의어
   ★ 데이터베이스 객체를 다루는 언어이다, 트랙잭션 대상이 아니다(작업을 취소할 수 없다)

• 데이터와 데이터간의 관계를 정의하여 데이터베이스 구조를 설정하는 SQL문
• 테이블(table), 뷰(view) 등 데이터베이스 객체를 생성/수정/삭제하는 기능을 담당
• 실행 후에는 작업을 취소할 수 없음
• 종류 : CREATE(객체 생성) ALTER(객체 수정) DROP(객체 삭제), TRUNCATE(내용만 삭제)

• 무결성 제약 조건

1. NOT NULL(필수) (NULL값을 허용하지 않음)
2. UNIQUE(유일성) (중복값을 허용하지 않음)
3. PRIMARY KEY(기본키) (각 레코드를 구별하는 칼럼-> NOT NULL + UNIQUE)
4. FOREIGN KEY(외래키) (다른 테이블의 값을 참조할 때 사용하는 키)
5. 값의 유효성을 검사

• 테이블 생성 방법(제약조건 이름이 없을때)
  CREATE TABLE 테이블이름(
  칼럼이름 데이터타입[제약 조건],
  칼럼이름 데이터타입[제약 조건],
  칼럼이름 데이터타입 NOT NULL PRIMARY KEY,
  칼럼이름 데이터타입 UNIQUE,
  칼럼이름 데이터타입 CEHCK(조건식),
  칼럼이름 데이터타입 REFERENCES 부모테이블(칼럼이름)
  

• 테이블 생성 방법(제약조건 이름이 있을때)
  CREATE TABLE 테이블이름(
  칼럼이름 데이터타입 [제약 조건],
  칼럼이름 데이터타입 [제약 조건],
  칼럼이름 데이터타입 NOT NULL CONSTRAINT 제약조건이름 PRIMARY KEY,
  칼럼이름 데이터타입 CONSTRAINT 제약조건이름 UNIQUE
  칼럼이름 데이터타입 CONSTRAINT 제약조건이름 CHECK(조건식),
  칼럼이름 데이터타입 CONSTRAITN 제약조건_이름 REFERENCES 부모

  

  테이블 수정하기
  1. 칼럼 추가 : ALTER TABLE 테이블명 ADD 칼럼명 데이터타입 [제약조건]
  2. 칼럼 수정 : ALTER TABLE 테이블명 MODIFY 칼럼명 데이터타입 [제약조건]
  3. 칼럼 삭제 : ALTER TABLE 테이블명 DROP COLUMN 칼럼명
  4. 칼럼 이름 : ALTER TABLE 테이블명 RENAME COLUMN 기존칼럼명 TO 신규칼럼명
  5. 테이블 이름 : ALTER TABLE 테이블명 RENAME TO 신규테이블명
  *FK 제약조건
  1. PK
  (1) 추가
  ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 PRIMARY KEY(칼럼)
  (2) 삭제
  ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명
  ALTER TABLE 테이블명 DROP PRIMARY KEY
  2. FK
  (1) 추가
  ALTER TABLE 자식테이블명 ADD CONSTRAINT 제약조건명 FOREIGN KEY(칼럼) FOREGIN KEY
  (2) 삭제
  ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명
  

IN(A,B,C) : A,B,C 중 하나임
NOT IN(A,B,C) : A,B,C, 모두 아님

2. EMPLOYEES 테이블에서 DEPARTMENT_ID가 50인 사원 중에서 SALARY가 5000이상인 사원을 조회하시오.
   SELECT *
   FROM EMPLOYEES
   WHERE DEPARTMENT_ID = 50
   AND SALARY > = 5000;
   SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID
   FROM EMPLOYEES
   WHERE DEPARTMENT_ID IN(50)
   AND SALARY > = 5000;

---

• 실습하기

DROP TABLE SCHEDULE_TBL;
DROP TABLE PLAYER_TBL;
DROP TABLE EVENT_TBL;
DROP TABLE NATION_TBL;

CREATE TABLE NATION_TBL(
N_CODE NUMBER(3) NOT NULL-- NUMBER(3) : 0~999
, N_NAME VARCHAR2(30BYTE) NOT NULL
, N_PARTI_PERSON NUMBER NULL
, N_PARTI_EVENT NUMBER NULL
, N_PREV_RANK NUMBER NULL
, N_CURR_RANK NUMBER NULL
);

CREATE TABLE EVENT_TBL(
E_CODE NUMBER NOT NULL
, E_NAME VARCHAR2(30 BYTE) NOT NULL
, E_FIRST_YEAR NUMBER(4) NULL
, E_INFO VARCHAR2(100 BYTE) NULL
);

CREATE TABLE PLAYER_TBL(
P_CODE NUMBER(3) NOT NULL
, P_NAME VARCHAR2(30 BYTE) NOT NULL
, N_CODE NUMBER(3) NOT NULL
, E_CODE NUMBER NOT NULL
, P_RANK NUMBER NULL
, P_AGE NUMBER(3) NULL
);

CREATE TABLE SCHEDULE_TBL(
S_NO NUMBER(3 BYTE) NOT NULL
, N_CODE NUMBER(3 BYTE) NULL
, E_CODE NUMBER NULL
, S_START_DATE DATE NULL
, S_END_DATE DATE NULL
, S_INFO VARCHAR2(100 BYTE) NULL
);

-- 기본키 추가하기
ALTER TABLE NATION_TBL ADD CONSTRAINT PK_NATION(제약조건만들기) PRIMARY KEY(N_CODE);
ALTER TABLE EVENT_TBL ADD CONSTRAINT PK_EVENT(제약조건만들기) PRIMARY KEY(E_CODE);
ALTER TABLE PLAYER_TBL ADD CONSTRAINT PK_PLAYER(제약조건만들기) PRIMARY KEY(P_CODE);
ALTER TABLE SCHEDULE_TBL ADD CONSTRAINT PK_SCHEDULE(제약조건만들기) PRIMARY KEY(S_NO);

-- 외래키 추가하기
ALTER TABLE PLAYER_TBL ADD CONSTRAINT FK_NATION_PLAYER(제약조건만들기) FOREIGN KEY REFERENCES NATION_TBL(N_CODE) ON DELETE CASCADE -- PLAYER_TBL의 N_CODE가 NOT NULL이므로, ON DELETE SET NULL이 불가능하다

ALTER TABLE PLAYER_TBL ADD CONSTRATIN FK_EVENT_PLYAER(제약조건만들기) FOREIGN KEY(E_CODE) REFERENCES EVENT_TBL(E_CODE) ON DELETE CASCADE --PLAYER_TBL의 E_CODE가 NOT NULL이므로, ON DELETE SET NULL이 불가능하다.
ALTER TABLE SCHEDULE_TBL ADD CONSTRAINT FK_NATION_SCHEDULE FOREIGN KEY(N_CODE) REFERENCES NATION_TBL(N_CODE) ON DELETE SET NULL; -- ON DELETE CASCADE도 가능하다.
ALTER TABLE SCHEDULE_TBL ADD CONSTRAINT FK_EVENT_SCHEDULE FOREIGN KEY(E_CODE) REFERENCES EVENT_TBL(E_CODE) ON DELETE SET NULL; -- ON DELETE CASCADE도 가능하다.

-- 국가 테이블의 기본키 삭제하기
-- 국가 테이블의 기본키를 참조하는 외래키를 먼저 삭제해야 한다.
ALTER TABLE PLAYER_TBL DROP CONSTRAINT FK_NATION_PLAYER;
ALTER TABLE SCHEDULE_TBL DROP CONSTRAINT FK_NATION_SCHEDULE;
ALTER TABLE NATION_TBL DROP CONSTRAINT PK_NATION;

--선수 테이블의 FK_EVENT_PLAYER 외래키 일시중지하기

ALTER TABLE PLAYER_TBL DISABLE CONSTRAINT FK_EVENT_PLAYER;

--선수 테이블의 FK_EVENT_PLAYER 외래키 다시 시작하기
ALTER TABLE PLAYER_TBL ENABLE CONSTRAINT FK_EVNET_PLAYER;

★ ON DELETE SET NULL

F
★ ON DELETE CASCADE

. DML(Data Manipulation language) : 데이터 조작어
★ 데이터(행,ROW)를 조작(삽입, 수정, 삭제)하는 언어이다, 트랙잭션 대상이다(작업이 완료되면 COMMIT, 작업을 취소하려면 ROLLBACK이 필요하다)
1) COMMIT : 작업을 저장한다. COMMIT이 완료된 작업은 ROLLBACK으로 취소할 수 없다.
2) ROLLBACK : 작업을 취소한다. COMMIT이후 작업을 취소한다.

• 종류
  1) 삽입 : INSERT INTO VALUES
  2) 수정 : UPDATE SET WHERE
  3) 삭제 : DELETE FROM WHERE
  (자격증에서는 DML 범주를 INSERT, UPDATE, DELETE, SELECT로 보기도 함)

• 실습하기
  

DROP TABLE EMPLOYEE_TBL;
DROP TABLE DEPARTMENT_TBL;

CREATE TABLE DEPARTMENT_T(
DEPT_NO NUMBER NOT NULL
, DEPT_NAME VARCHAR2(15 BYTE) NOT NULL
, LOCATION VARCHAR2(15 BYTE) NOT NULL
, CONSTRAINT PK_DEPART PRIMARY KEY(DEPT_NO)

CREATE TABLE EMPLOYEE_T(
EMP_NO NUMBER NOT NULL
, NAME VARCHAR2(20 BYTE) NOT NULL
, DEPART NUMBER NULL
, POSTION VARCHAR2(20 BYTE) NULL
, GENDER CHAR2(2 BYTE) NULL
, HIRE_DATE DATE NULL
, SALARY NUMBER NULL
, CONSTRAINT PK_EMP PRIMARY KEY(EMP_NO)
, CONSTARINT FK_DEPART_EMP FOREGIN KEY(DEPART) REFERENCES DEPARTMENT_T(DEPT_NO) ON DELETE SET NULLL
);

• 시퀀스 : 순차적으로 증가하는 일련번호를 생성하는 데이터베이스 객체

DROP SEQUENCE DEPT_SEQ;
CREATE SEQUENCE DEPT_SEQ ORDER;

INSERT INTO DEPARTMENT_T(DEPT_NO, DEPT_NAME, LOCATION) VALUES(DEPT_SEQ.NEXTBAL, '영업부', '대구');

-- 부서번호를 생성하는 시퀀스 만들기 (씨퀸스 생성방법)
CREATE SEQUENCE DEPT_SEQ
INCREMENT BY 1 -- 1씩 증가하는 번호를 만든다(디폴트)
START WITH 1 -- 1부터 번호를 만든다(디폴트)
NOMAXVALUE -- 번호의 상한선이 없다(디폴트)
NOCYCLE -- 번호 순환이 없다(디폴트)
CACHE 20 -- 20개씩 번호를 미리 만들어 둔다(디폴트)
NOORDER -- 번호를 순서대로 사용하지 않는다(디폴트)
( 번호를 순서대로 사용하는 ORDER옵션으로 바꿔서 시퀀스를 생성한다)