대용량 웹서비스를 위한 MSA Full-Stack 개발자 양성 과정 -21일차 노트 필기_데이터베이스 Constraint(제약 조건), DML, DQL, DDL_220819

Alter - create로 만들어진 것을 변경할 때, 

update - domain을 수정할 때, 사용

 

Delete - 레코드를 삭제

Drop - create로 만든 object들을 물리적으로 완전히 제거

 

 

---

CONSTRAINT 제약 조건 종류

2) Foreign Key

CREATE TABLE fk_member(
     code number(2) NOT NULL ,
     id VARCHAR2(20) NOT NULL
     CONSTRAINT id_fk REFERENCES MEMBER(id) ,
     etc VARCHAR2(10)
 )

 

: 다른 테이블의 PK를 참조하는 것

: 테이블에 레코드를 추가할 때 참조되는 대상의 값 이외에는 등록 없도록 하는 것
        - 참조 무결성 원칙

: NULL 허용, 중복 가능
: 하나의 테이블에 여러개의 컬럼들이 FK설정이 가능하다.
: 재귀적 관계 설계 가능 - 자기자신 테이블의 PK를 참조하는 것;

 

** 주의 사항
    INSERT: 부모키가 먼저 INSERT ---> 자식키 INSERT
    DELETE: 참조되는 자식이 먼저 DELETE ---> 부모 키를 DELETE

                    ** 삭제할 때 연관관계를 찾아서 자식을 먼저 삭제하는 자체가 불편하기 때문에

                         FK를 설정할 때, ON DELETE CASCADE 옵션을 함꼐 설정하면 부모가 삭제될 때,

                         모든 참조되는 자식 레코드로 함께 삭제 된다.

 

Foreign Key 예시)

** 부서정보를 따로 table로 분리해서 참조하는 형식으로 사용

** null: ex) 신입사원 입사 시 아직 부서 배정 X

** 테이블과 테이블 간 수평적으로 관계를 맺음 (DBus)

** 부서 정보 테이블의 부서코드는 부모 코드가 됨

 

FK 재귀적 관계 설정 예시)

 

ex) 게시판 + 댓글

글번호	제목	작성자	내용	등록일	부모 글의 글번호
1	aa				null
2	bb				null
3	cc				null
4					2
5					1

** 4, 5번 글은 부모글에 대한 댓글

** 부모 글의 글번호 컬럼은 자기 자신 테이블의 PK를 참조하고 있음;

** 계층적 관계 만들 수 있음;

 

**관계
식별 관계: 1대 1의 관계로 다른 테이블의 PK를 자식의 PK가 참조할 때 (PK, FK)
비 식별 관계: 1대 다의 관계로 다른 테이블의 PK를 자식테이블의  일반 속성이 참조할 때

 

---

3) UNIQUE KEY

    - 중복 불가 // null을 허용함 (Not null 설정하면 pk와 동일)
    - 후보키 중에 대표키가 될 수 없는 키를 UNIQUE로 설정한다
    - 한 테이블에 여러 개의 컬럼에 UNIQUE 설정 가능

4) CHECK

    : 조건을 설정하여 조건에 만족하지 않는 정보를 INSERT할 수 없도록 하는 것    

 

  ** DEFAULT

    : 기본값을 설정 (자주 사용되는 값을 미리 설정해놓고 자동으로 값이 들어갈 수 있도록 하는 것)
        ex) 등록일(SYSDATE), 조회수(0부터 시작)
    : DEFAULT를 설정할 때 CONSTRAINT 별칭은 사용하지 않는다.
    : DEFAULT와 함께 NOT NULL 설정을 할 때는 반드시 DEFAULT를 먼저 작성한다.

---

DML(Data Manipulation Language) - INSERT, UPDATE, DELETE

레코드 삽입

insert into 테이블이름(컬럼명, 컬럼명,...) values(값, 값, ...)
insert into 테이블이름 values(값, 값, ...)
 =>모든 컬럼에 모두 값을 넣을 때 사용

 

 

레코드 수정

update 테이블이름
 set 컬럼명=변경값 , 컬럼명=변경값 , 컬럼명=변경값 , ..... -- 여러 셀의 값을 변경할 때 ,로 연결
 [ where 조건식 ]

 

 

Ex) 직원 정보 수정

UPDATE COPY_EMP 
SET COMM=100, JOB='STUDENT', HIREDATE=SYSDATE 
WHERE SAL >= 1000 AND SAL <=2000;

 

레코드 삭제

    1) ROLLBACK이 가능 (취소처리)

 DELETE [FROM] 테이블 이름  -- 모든 레코드 삭제
        [WHERE 조건식] -- 특정 레코드만 삭제

--예시
DELETE FROM MEMBER WHERE ID='JANG';
DELETE MEMBER WHERE REG_DATE IS NULL;

        ** ROLLBACK: DML 문장에 의해 변화를 갖는 데이터들 - 잘못된 쿼리를 실행했을 때 되돌릴 기회를  줌

    2) ROLLBACK 불가

TRUNCATE TABLE 테이블 이름 -- 모든 레코드 절삭(삭제)

---

DDL - 테이블 수정(ALTER)

 

    ① 컬럼추가 
    alter table 테이블이름 add (컬럼명 자료형 [제약조건] , 컬럼명 자료형 [제약조건] , ....) 
    
    ② 컬럼삭제 
    alter table 테이블이름 drop column 컬럼이름 
    
    ③ datatype변경 
    alter table 테이블이름 modify 컬럼이름 변경자료형 
    
    ④ 컬럼이름 변경 
    alter table 테이블이름 rename column 기존컬럼명 to 변경컬럼명 

    * 제약 조건 추가
      alter table 테이블이름 add constraint 제약조건_별칭 제약조건_종류 
    * 제약 조건 삭제
      alter table 테이블이름 drop constraint 제약조건_별칭

**DEFAULT는 삭제 불가! (제약 조건이 아님) 

 

    ⑤ 테이블 삭제 
    drop table 테이블이름

 

 

---

**테이블 복사 방법

CREATE TABLE 테이블 이름 AS 복사할 테이블 정보;

 

: 단, 테이블을 복사하면 제약 조건은 복사가 되지 않는다.

: 제약 조건도 복사하고 싶다면, 테이블을 복사한 후 변도로 제약 조건을 추가해야 함

-- 1) 모든 컬럼과 모든 레코드 복사
CREATE TABLE COPY_EMP AS SELECT * FROM EMP;
SELECT * FROM COPY_EMP;

-- 2) 특정한 컬럼과 조건에 해당하는 레코드만 복사 -- SELECT(COLUMN), WHERE(레코드)
CREATE TABLE COPY_EMP2 AS SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE SAL >=3000;
SELECT * FROM COPY_EMP2;

-- 3) 테이블의 구조만 복사 -- WHERE 뒤의 조건식을 항상 FALSE로 만들어서 레코드를 불러오지 않도록 함
CREATE TABLE COPY_EMP3 AS SELECT * FROM EMP WHERE 1=0; 
SELECT * FROM COPY_EMP3;

 

---

DQL (Data Query Language) - SELECT

 

select 문장

select distinct | * | 컬럼명 as 별칭, 컬럼명 별칭,....  -- 열 제한 = projection
from 테이블이름
[where 조건식 ] -- 행 제한 = selection
[order by 컬럼명 desc | asc , .. ] -- 정렬 대상 desc: 올림차순(기본), asc: 내림차순

 

*distinct는 중복 레코드 제거할 떄

*As는 별칭을 줄 때 사용( 생략 가능)

 

* 중요!! - 실행순서
from  →  where →select  → order by 순으로 실행

 

예제) 사원 검색 및 정렬

--SCOTT 계정의 EMP를 SELECT해본다
--1) 원하는 컬럼만 검색
SELECT EMPNO, JOB, ENAME FROM EMP;

--2) 컬럼에 별칭 주기
SELECT EMPNO AS "사원 번호", JOB AS 직업, ENAME AS 이름 
FROM EMP; -- 컬럼에 직접적인 변화는 주는 것은 아님 

--3) 중복행 제거

-- EX) 우리 사원들의 JOB의 종류를 알고 싶다!
SELECT DISTINCT JOB FROM EMP;
-- EX) 우리 사원들이 소속되어 있는 부서번호를 알고 싶다!
SELECT DISTINCT DEPTNO FROM EMP;

-- 4) 정렬
--1) SAL을 기준으로 내림차순 정렬한다.
SELECT * FROM EMP ORDER BY SAL /*ASC*/; -- 올림차순
SELECT * FROM EMP ORDER BY SAL DESC; -- 내림차순

--2) JOB을 기준으로 올림차순으로 정렬하되 JOB이 같으면 SAL 높은 순서대로 정렬
SELECT * FROM EMP ORDER BY JOB, SAL DESC; -- ,(콤마)로 연결

 

---

 

WHERE 조건식: 다양한 함수와 연산자를 함께 사용

 

** 모든 연산자에 NOT을 붙히면 반대!!