[Database #16] 제약조건(2), 뷰(view)

• 제약조건
  • FOREIGN KEY
    • 외래키, 외부키, 참조키
    • 다른 테이블의 컬럼값을 참조(REFERENCE)하여 참조하는 테이블의 값만 허용한다.
    • FOREIGN KEY 제약 조건을 통해서 다른 테이블과의 관계(RELATIONSSHIP)가 형성된다.
    • 외래키로 지정된 GRADE_CODE 는참조하는 USER_GRADE테이블의 GRADE_CODE 에있는 데이터만 INSERT 가능하다.
    • constraint fk_grade_code foreign key (grade_code) references user_grade(grade_code)

• DML : DELETE
  • DELETE FROM USER_GRADE WHERE GRADE_CODE = 4;
  • 삭제 옵션
    • 일반적으로 참조된는 원본 컬럼 내용을 삭제하려고 할 때 외래키로 사용중인 자식 컬럼이 있다면 함부로 삭제 할 수 없다.
    • 참조되고 있는 원본 테이블의 컬럼값이 삭제 될 때 참조하는 값을 어떻게 처리할 것인지 설정하는 옵션
    • ON DELETE CASCADE : 부모 컬럼 삭제할때 관련된 자식도 함께 삭제
      • ON UPDATE CASCADE, ON DELETE SET NULL
• DML(데이터 조작언어)
  • INSERT, UPDATE, DELETE, SELECT(DQL)
  • [CRUD]
    • C(CREATE) : INSERT / 데이터 추가
    • R(READ) : SELECT / 데이터 조회
    • U(UPDATE) : UPDATE / 데이터 수정
    • D(DELETE) : DELETE / 데이터 삭제
  • INSERT : 새로운 행을 특정 테이블에 추가하는 명령어, 해당 명령어 실행 후에는 테이블의 행의 개수가 증가 [사용형식]
  1. 특정 컬럼에 값을 추가 INSERT INTO 테이블명(컬럼, 컬럼, ...) VALUES(값, 값, ...);
  2. 모든 컬럼에 값을 추가 INSERT INTO 테이블명 VALUES(값, 값, ...);
     • INSERT + SUBQUERY
       • INSERT 문에 서브쿼리를 사용하여 VALUES에 작성하던 값 대신 저장하여 추가 가능하다.

insert into EMP_01(
select EMP_ID, EMP_NAME, DEPT_TITLE
from EMPLOYEE
left join DEPARTMENT ON(DEPT_CODE=DEPT_ID)
);

• UPDATE : 해당 테이블의 데이터를 수정하는 명령어
  • UPDATE 테이블명 SET 컬럼명 = 바꿀값 [WHERE 조건식]
  • UPDATE 실행 후 데잍터의 개수는 변하지 않는다.

create table DEPT_COPY
as select * from DEPARTMENT ;
update DEPT_COPY set DEPT_TITLE = '전략기획부'
where DEPT_ID = 'D9';

• UPDATE + SUBQUERY
  • 여러 행을 변경하거나, 여러 컬럼의 값을 변경하고자 할때 서브쿼리를 사용하여 UPDATE문 작성 가능
  • SET 컬럼 = (서브쿼리)

UPDATE EMP_SALARY
set (SALARY, BONUS) = (select SALARY, BONUS from EMP_SALARY where EMP_NAME = '유재식')
where EMP_NAME in ('노옹철','전형돈','정중하','하동운');

• DELETE
  • 테이블의 행을 삭제하는 명령어
  • 실행 후에는 테이블의 행의 개수가 줄어든다.
  • delete from TEST_DELETE where EMP_ID IN(200,201);

• •  
• DCL
  • COMMIT, ROLLBACK —> TCL(TRANSACTION CONTROL LANGUAGE)
  • 트랜잭션
    • 정의 : 데잍터를 처리하는 작업을 잘게 나눈 데이터처리의 최소 작업 단위
    • 하나의 트랜잭션으로 이루어진 작업 내용은 전체 성공(저장) OR 하나라도 실패하면 전체 실패(복구)
    • 각각의 작업마다 저장시점을 구분해서 COMMIT(작업 내역 저장/반영) / ROLLBACK(작업 내역 취소) 
    • savepoint SP1; rollback to SP1; -- 해당 SAVEPOINT로 되돌아 가겠다.

• •  
• DDL
  • ALTER : 생성한 객체를(TABLE, VIEW, ...) 수정
    • 컬럼을 추가하기 
      • alter table DEPT_COPY add LNAME2 VARCHAR(20) default 'Y';
    • 컬럼을 삭제하기 
      • alter table DEPT_COPY drop column LNAME2;
    • 변경하기 
      • alter table DEPT_COPY modify DEPT_ID VARCHAR(30);
    • 제약조건 추가하기 
      • alter table DEPT_COPY add constraint PK_DEPT_CP primary KEY(DEPT_ID);
    • 객체명 변경 
      • alter table DEPT_COPY rename to DEPTCOPY;
  • DROP
    • 객체의 특정요소를 제거하거나 객체 자체를 제거할때 사용하는 명령어
    1. 객체의 특정요소를 제거
       ALTER 객체 객체명
       DROP COLUMN 컬럼명 
       alter table DEPT_COPY drop column LNAME2;
    2. 객체 자체를 제거
       DROP 객체 객체명; 
       drop table DEPTCOPY;
• VIEW(뷰)
  • select를 실행한 결과화면을 담는 객체
  • select 문자 자체를 저장
  • 호출할때마다 저장된 쿼리를 실행하여 결과를 보여주는 객체다
  • 실질적으로 데이터를 담고 있지 않다.
  • 보통 노출하고 싶지 않은 정보나 업무에 불필요한 정보를 제외한 필요 정보들만 조회하고자 할 때 사용한다.
  • CREATE [OR REPLACE] VIEW 뷰이름 AS 서브쿼리(뷰에 저장하여 확인할 SELECT 쿼리)
  • VIEW는 SELECT 쿼리를 저장하고 있기에 원본이 변경되었을때 뷰도 함께 변경해서 조회 가능하다.
  • 뷰에는 연산 결과를 포함한 SELECT문 저장가능하다
• 뷰에 데이터 삽입, 수정, 삭제하기

create or replace view V_JOB
as select * from JOB;

• 뷰에 삽입

INSERT into V_JOB VALUES('J8','인턴');

• 뷰 수정

update V_JOB
set JOB_NAME = '알바'
where JOB_CODE = 'J8';

• 뷰 삭제

delete from V_JOB
where JOB_CODE = 'J8';

• AUTO_INCREMENT
  • INSERT 할 때 자동으로 키를 1씩 증가

create table AT_TEST(
ID INT auto_increment primary key,
NAME VARCHAR(30)
);
insert into AT_TEST VALUES(null,'가');
insert into AT_TEST(NAME) VALUES('나');
select LAST_INSERT_ID();