21일차 : 서브쿼리 / DML / DDL

2022/12/26

 

[ 1. 서브쿼리 ] =====================================================================

1 - 1 ) 스칼라 서브쿼리

-- 스칼라서브쿼리
SELECT * FROM EMPLOYEE;
-- 직원들 중 자기부서의 평균 급여보다 급여가 많은 직원의 이름,부서코드,급여 조회
SELECT EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE E1
WHERE SALARY > 
     (SELECT AVG(SALARY) FROM EMPLOYEE E2 WHERE E2.DEPT_CODE = E1.DEPT_CODE);
-- 메인쿼리문의 테이블을 서브쿼리문에서 사용(동작순서:항상 서브쿼리가먼저)
-- 상호연관쿼리:메인쿼리의 값을 서브쿼리에서 사용하고 그로인해 완성된 서브쿼리를 메인쿼리가 사용 
--           메인 쿼리 테이블 값이 변경되면 서브쿼리의 값도 변경됨
-- 상호연관쿼리에서 값이 하나만 나오는걸 스칼라서브쿼리라고한다 
-- 상호연관쿼리를 너무 무분별하게 사용하면 동작속도가 매우느려진다
-- 조인대체가능

-- 스칼라서브쿼리를 ORDER BY 절에도 사용할 수 있다.
-- 전직원의 사번, 이름, 부서코드 출력 - 정렬을 부서이름 오름차순
SELECT EMP_ID, EMP_NAME, DEPT_CODE
FROM EMPLOYEE
ORDER BY (SELECT DEPT_TITLE FROM DEPARTMENT WHERE DEPT_ID=DEPT_CODE);
-- 별칭안붙이는이유 : 컬럼명이 겹치지 않으므로
SELECT * FROM DEPARTMENT ORDER BY DEPT_TITLE; -- 순서확인을위한 쿼리문

SELECT EMP_ID, EMP_NAME, DEPT_CODE, 
        (SELECT DEPT_TITLE FROM DEPARTMENT WHERE DEPT_ID=DEPT_CODE)부서명
FROM EMPLOYEE;
-- 컬럼자리에도 사용할 수 있다(JOIN 효과를 볼 수 있음)
-- 서브쿼리를 꼭 WHERE절에 사용하는게 X / SELECT절에도 ORDER BY절에도 사용할 수 있다.

 

1 - 1 ) 인라인뷰

/*
    SELECT     조회하고 싶은 컬럼1, 조회하고 싶은 컬럼2 
    FROM       테이블
    JOIN       테이블 + 조인조건
    WHERE      조건
    GROUP BY   그룹함수가 적용될 컬럼
    HAVING     그룹핑 후 적용할 조건
    ORDER BY   정렬기준 컬럼
*/
-- 서브쿼리 FROM절에 사용하는 방식(인라인 뷰)
-- 서브쿼리로 나온 조회결과를 가상의 테이블로 사용
SELECT *
FROM (SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY FROM EMPLOYEE);

(SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE);
-- 사용목적 : 정보의제한(필요한것만 정제해서 주려고), 
--          필요한 정보만 모아서 새로운 테이블로 사용하기 위해
-- TOP-N 분석 : 회사에서 연봉이 가장 높은사람 5명의 이름, 급여 출력
-- ROWNUM은 조회된 결과에 순서대로 숫자를 붙이는 역할 
-- ROWNUM 동작순서가 ORDER BY보다 먼저동작(SELECT문에 있기때문에)
SELECT ROWNUM, EMP_NAME, SALARY 
FROM (SELECT EMP_NAME, SALARY FROM EMPLOYEE ORDER BY SALARY DESC)
WHERE ROWNUM < 6;
(SELECT EMP_NAME, SALARY FROM EMPLOYEE ORDER BY SALARY DESC); --인라인뷰
-- 급여를 정렬한 쿼리문을 인라인뷰로 사용하여 테이블을 새로생성하고 그 테이블에서 ROWNUM을 사용하면
-- 항상 상위부터 표시되므로 연봉1~5순위를 알 수있다.
-- 사원의 급여가 변경되어서 순위가 변해도 결과가 똑같다

[ SELECT 끝 ]

 

[ 2. DML ] ========================================================================

 DML(데이터 조작어): INSERT, UPDATE, DELETE

 SELECT도 DML에 포함되지만 양이 방대해서 DQL로 따로빠진다
 SELECT와 INSERT, UPDATE, DELETE의 가장 큰 차이 
 SELECT : 데이터(행)의 변화가없다 / INSERT, UPDATE, DELETE : 데이터(행)의 변화가 있다

 

-- DML 연습을 위한 임의의 테이블 생성 --

CREATE TABLE MEMBER_TBL(
    MEMBER_ID       VARCHAR2(20),   -- 아이디
    MEMBER_PW       VARCHAR2(30),   -- 비밀번호
    MEMBER_NAME     VARCHAR2(20),   -- 이름
    MEMBER_PHONE    CHAR(13),       -- 전화번호
    MEMBER_AGE      NUMBER,         -- 나이
    MEMBER_ADDR     VARCHAR(300),   -- 주소
    ENROLL_DATE     DATE            -- 가입일
);

 

2 - 1 ) INSERT

 INSERT : 테이블에 새로운 행을 추가하는 구문 / 추가할때마다 행 개수가 증가

 

< INSERT 표현식 1 >
 INSERT INTO 테이블이름(컬럼1,컬럼2,컬럼3...) 
 VALUES(컬럼1에 들어갈 값, 컬럼2에 들어갈 값, 컬럼3에 들어갈 값...)

INSERT INTO 
MEMBER_TBL(MEMBER_ID, MEMBER_PW, MEMBER_NAME, MEMBER_PHONE, MEMBER_AGE,
           MEMBER_ADDR, ENROLL_DATE)
VALUES('user01', '1234', '유저1', '010-1111-1111', 20, 
       '서울시 영등포구 당산동', SYSDATE);
-- 자료형 : 문자열 ''
-- CHAR - 글자수가 정해져있으면 사용해줌 예)전화번호, 성별 처럼 정해져있는것 
-- VARCHAR
-- 둘다 수가넘어가면 에러나는건 동일 
/*
    ORACLE 
    CHAR(9) - 'ORACLE   ' = ORACLE + 공백3칸 저장(남으면공백)
    VARCHAR - 'ORACLE' = ORACLE 저장(남으면 잘라냄)
*/
-- 핸드폰번호는(-포함13자리) 고정되어있으므로 CHAR로 생성한것
INSERT INTO 
MEMBER_TBL(MEMBER_ID, MEMBER_PW, MEMBER_NAME, MEMBER_AGE, ENROLL_DATE)
VALUES('user02', '1357', '유저2', 22, SYSDATE);
-- PHONE/ADDR 지움 : 명시하지않은 컬럼은 알아서 NULL로 들어간다
INSERT INTO 
MEMBER_TBL(MEMBER_PW, MEMBER_ID, MEMBER_NAME, MEMBER_AGE, ENROLL_DATE)
VALUES('user02', '3333', '유저3', 23, SYSDATE);
-- INSRT INTO컬럼순서가 바뀌면 값의 순서도 같이 바뀌어야 한다.
INSERT INTO 
MEMBER_TBL(MEMBER_ID, MEMBER_PW, MEMBER_NAME, MEMBER_AGE, ENROLL_DATE, MEMBER_PHONE)
VALUES('user04', '4444', '유저4', 24, SYSDATE, '010-4444-4444');
-- INSERT할 컬럼갯수와 VALUES의 갯수도 맞춰줘야함(항상 명시한 만큼 들어감)

< INSERT 표현식 2 >

 INSERT INTO 테이블이름
 VALUES(첫번째 컬럼에 들어갈값, 두번째컬럼에 들어갈 값,....);
 컬럼명이 생략된경우 모든컬럼을 순서대로 대입, VALUES에서 생략불가능(NULL도 명시를해줘야함)

INSERT INTO MEMBER_TBL
VALUES('user04', '4444', '유저4', '010-4444-4444', 24, '서울시 영등포구', SYSDATE);

INSERT INTO MEMBER_TBL
VALUES('user04', '4444', '유저4', NULL, 24, NULL, SYSDATE);
-- "not enough values" : 컬럼은 명시했는데 값이 모자랄 때
-- 컬럼명을 따로 입력하지않으면 컬럼7개가 전부 들어가있음 - NULL을 넣고싶으면 직접 넣어줘야함

 

2 - 2 ) UPDATE

UPDATE : 테이블에 기록된 컬럼의 값을 수정하는 구문 / 행 개수 변화없음

 

< UPDATE 표현식 >

UPDATE 테이블이름 SET 수정할컬럼1 = 수정할값1, 수정할컬럼2 = 수정할값2,...WHERE 조건

UPDATE MEMBER_TBL SET MEMBER_PHONE = '010-222-2222',
                      MEMBER_ADDR = '서울시 영등포구'
                      WHERE MEMBER_ID = 'user02';
-- WHERE 절이 빠지는순간 테이블 전체데이터가 바뀜(조건절이 굉장히 중요)
-- WHERE 절은 SELECT에 썻던 WHERE과 사용법이 같음(서브쿼리 AND OR 함수 전부 OK)

UPDATE MEMBER_TBL SET MEMBER_ID = 'user03',
                      MEMBER_PW = '3333'
                      WHERE MEMBER_NAME = '유저3';
-- INSERT는 ROW수가 늘어나지만 UPDATE는 수정만 해주는 작업이므로 ROW수 변화X

UPDATE MEMBER_TBL SET 
                  MEMBER_ADDR = 
                 (SELECT MEMBER_ADDR FROM MEMBER_TBL WHERE MEMBER_ID = 'user01')
                  WHERE MEMBER_ID = 'user03';
-- 서브쿼리 사용가능 : user01의 주서정보를 따와서 user03의 주소에 넣어주겠다

UPDATE MEMBER_TBL SET MEMBER_PW = '3333'
                      WHERE MEMBER_ID = 'user03';
-- 중복된 데이터라도 업데이트는 동작한다(같은값이 들어갈뿐 업데이트가 안되는게아님)

 

2 - 3 ) DELETE 

DELETE : 테이블의 행을 삭제하는 구문 / 전체 행 개수 감소

< DELETE 표현식 > 

DELETE FROM 테이블명 WHERE 조건

조건절 중요! 조건을 명시하지않으면 모든행을 지운다 /  ROW 자체를 삭제함(행이 감소한다)

DELETE FROM MEMBER_TBL WHERE MEMBER_ID = 'user03';
DELETE FROM MEMBER_TBL WHERE MEMBER_ID = 'user04';
-- 중복값이 잇던 user04는 둘다 지워지는 것

 

2 - 4 ) INSERT / UPDATE / DELETE 연습하기

CREATE TABLE FREE_BOARD(
    BOARD_TITLE     VARCHAR(1000),  -- 게시글 제목
    BOARD_CONTENT   VARCHAR(3000),  -- 게시글 내용
    BOARD_FILE      VARCHAR(300),   -- 첨부파일
    BOARD_WRITER    VARCHAR2(20),   -- 작성자
    WRITE_DATE      DATE            -- 작성일
);

INSERT INTO FREE_BOARD(BOARD_TITLE, BOARD_CONTENT, BOARD_WRITER, WRITE_DATE)
VALUES('제목111', '인서트구문1로작성', 'user11', sysdate);
INSERT INTO FREE_BOARD
VALUES('제목222', 'INSERT구문2로작성', '야호.INSERT', 'user22', sysdate);

UPDATE FREE_BOARD SET BOARD_CONTENT = 'INSERT구문1로 작성'
                      WHERE BOARD_TITLE = '제목111';
                      
DELETE FROM FREE_BOARD WHERE BOARD_TITLE = '제목222';

[ 3.  CREATE ] =====================================================================

DDL(데이터정의어) : CREATE, ALTER, DROP

객체를 만들고 수정하고 삭제하는 구문 (오라클 객체 종류는 많다 TABLE만 있는게 아님 가장 많이만들 뿐)

 

CREATE : 객체를 생성

오라클 객체 종류 : USER, TABLE, VIEW, INDEX ...등등

 

 3 - 1 )  USER 생성하기 

 관리자 계정 : DB의 생성과 관리를 담당, 모든권한과 책임을 가짐
 사용자 계정 : DB에 대하여 질의, 갱신, 보고서작성 등 업무에 필요한 최소한의 권한만 가짐 

 사용자만들기 : USER생성은 관리자계정으로만 가능

< 표현식 >

CREATE USER 사용자이름 IDENTIFIED BY 비밀번호; 

-- 사용자 계정에서 생성 시 생성권한이 없다는 insufficient privileges(권한X)에러 발생

CREATE USER test01 IDENTIFIED BY 1234;

 : 관리자 계정에서 USER를 생성하게되면 계정은 생성되었지만 아무 권한도 없는 상태(CONNECT 불가)

생성은 되었으나 권한이없음

권한 부여 방법 : GRANT ROLE이름 TO 계정이름;

GRANT CONNECT,RESOURCE TO test01; --CONNECT 권한을 부여

권한부여

권한 회수 방법 : REVOKE ROLE이름 FROM 계정이름;

REVOKE CONNECT FROM test01; -- CONNECT권한을 회수

권한 회수

 3 - 2 ) TABLE 생성하기 

 

< 표현식 > 

  CREATE TABLE 테이블이름 (컬럼이름1 자료형(길이), 컬럼이름2 자료형(길이)...);
  CREATE TABLE 테이블이름(
        컬럼이름1   자료형(길이),
        컬럼이름2   자료형(길이),
        컬럼이름3   자료형(길이)
  );
테이블이름, 컬럼이름은 대/소문자 구분이 없음
JAVA는 변수선언시 대소문자구분이 되므로 카멜표기법을 사용했는데(memberPhone)
오라클은 대소문자 구분이 없기때문에 [ memberPhone, memberphone, MEMBERPHONE ]를 같은걸로 인식한다.
그러므로 대문자사용[권고], 단어와 단어가 합쳐질때 _로 연결(스네이크 표기법, MEMBER_PHONE, member_phone)
단,안에 들어가는 데이터는 대소문자 구분! (컬럼명과 테이블명만 구분하지않는다)

CREATE TABLE MEMBER_TBL1(
        MEMBER_ID   VARCHAR2(20),
        MEMBER_PW   VARCHAR2(30),
        MEMBER_NAME VARCHAR2(20)
);

-- INSERT시 데이터가 테이블생성 때 지정한 자료형과 안맞거나 길이를 넘어가면안된다.
INSERT INTO MEMBER_TBL1 VALUES('user01', '1234', '유저1');
INSERT INTO MEMBER_TBL1 VALUES('user02', '2222', '유저2');
INSERT INTO MEMBER_TBL1 VALUES('user03', '3333', '유저3');
INSERT INTO MEMBER_TBL1 VALUES('user01', '4444', '유저4');

 

3 - 3 ) 제약조건 : 데이터 무결성을 지키기 위해 제한된 조건

 NOT NULL / UNIQUE / PRIMARY KEY / FOREIGN KEY / CHECK

INSERT INTO MEMBER_TBL1 VALUES('user01', '1234', '유저1');
INSERT INTO MEMBER_TBL1 VALUES('user02', '2222', '유저2');
INSERT INTO MEMBER_TBL1 VALUES('user03', '3333', '유저3');
INSERT INTO MEMBER_TBL1 VALUES('user01', '4444', '유저4');
INSERT INTO MEMBER_TBL1 VALUES('user01', NULL, '유저4');
UPDATE MEMBER_TBL1 SET MEMBER_PW = '5555' WHERE MEMBER_ID = 'user01';
-- MEMBER_ID = 'user01'인 회원이 두명이 있기때문에 둘다 수정됨
-- 필수값인 PW가 입력되지 않을 수 있음(NULL이 들어감)
-- 값을 제한하여 데이터의 무결성을 위해 제약조건을 해줘야함(중복안되게)

   

▣ NOT NULL : 컬럼에 NULL을 허용하지 않는 제약조건

-- NOT NULL 
CREATE TABLE MEMBER_TBL_NN(
        MEMBER_ID   VARCHAR2(20) NOT NULL,
        MEMBER_PW   VARCHAR2(30) NOT NULL,
        MEMBER_NAME VARCHAR2(20) 
);
-- 한번생성된 제약조건은 글자를 지워도 유지되고있음(나중에 ALTER / DROP으로 처리해줘야함)

INSERT INTO MEMBER_TBL_NN VALUES('user01', '1234', '유저1'); -- 정상적으로 INSERT

-- 빈값을 허용하지 않는 컬럼(NOT NULL)에 NULL값을 넣게되면
-- cannot insert NULL into 에러발생
INSERT INTO MEMBER_TBL_NN VALUSE(MEMBER_ID, MEMBER_NAME) 
VALUES('user02','유저2'); 
INSERT INTO MEMBER_TBL_NN VALUES('user01', NULL, '유저1');
INSERT INTO MEMBER_TBL_NN VALUES(NULL, '1234', '유저1');

 

▣ UNIQUE : 컬럼에 중복된 데이터를 허용하지 않는 제약조건

-- 중복값이 들어가면 UPDATE/DELETE 시 문제 발생
INSERT INTO MEMBER_TBL_NN VALUES('user01', '2222', '유저11');
-- 이를 막기 위해 UNIQUE 사용
-- 컬럼을 선언하면서 제약조건설정(NOT NULL은 컬럼에서만 가능)
CREATE TABLE MEMBER_TBL_UNQ(
        MEMBER_ID   VARCHAR2(20) UNIQUE,
        MEMBER_PW   VARCHAR2(30) NOT NULL,
        MEMBER_NAME VARCHAR2(20) 
);
INSERT INTO MEMBER_TBL_UNQ VALUES('user01', '1111', '유저1');
--INSERT INTO MEMBER_TBL_UNQ VALUES('user02', null, '유저2'); --not null 에러
INSERT INTO MEMBER_TBL_UNQ VALUES('user02', '2222', '유저2');
--INSERT INTO MEMBER_TBL_UNQ VALUES('user02', '1111', '유저2');
-- unique constraint : 유니크 제약조건 위배(값중복)
-- 데이터가 모두 중복이지만 UNIQUE제약조건이 걸린 ID가 중복이기때문에 발생
INSERT INTO MEMBER_TBL_UNQ VALUES('user022', '1111', '유저2');

-- 컬럼을 모두 작성 후 맨아래에 제약조건 설정
CREATE TABLE MEMBER_TBL_UNQ2(
        MEMBER_ID   VARCHAR2(20),
        MEMBER_PW   VARCHAR2(30) NOT NULL,
        MEMBER_NAME VARCHAR2(20),
        UNIQUE(MEMBER_ID)
);
INSERT INTO MEMBER_TBL_UNQ2 VALUES('user01', '1111', '유저1');
INSERT INTO MEMBER_TBL_UNQ2 VALUES('user01', '1113', '유저2');-- UNIQUE에러


CREATE TABLE MEMBER_TBL_UNQ3(
        MEMBER_ID       VARCHAR2(20) UNIQUE,
        MEMBER_PW       VARCHAR2(30) NOT NULL,
        MEMBER_NAME     VARCHAR2(20),
        MEMBER_PHONE    CHAR(13) UNIQUE
);

INSERT INTO MEMBER_TBL_UNQ3 
VALUES('user01', '1234', '유저1', '010-1111-1112');
INSERT INTO MEMBER_TBL_UNQ3 
VALUES('user02', '1234', '유저1', '010-1111-1112');

-- UNIQUE 제약조건을 테이블레벨에 걸 때 실수하는 점
CREATE TABLE MEMBER_TBL_UNQ4(
        MEMBER_ID       VARCHAR2(20),
        MEMBER_PW       VARCHAR2(30) NOT NULL,
        MEMBER_NAME     VARCHAR2(20),
        MEMBER_PHONE    CHAR(13),
        UNIQUE(MEMBER_ID, MEMBER_PHONE) --두개를 합쳐서 UNIQUE
    --  MEMBER_ID와 MEMBER_PHONE이 모두 겹칠때만 제약조건에 걸림
    --  UNIQUE(MEMBER_ID),
    --  UNIQUE(MEMBER_PHONE) 이렇게 콤마로 구분해서 작성해줘야한다
);
INSERT INTO MEMBER_TBL_UNQ4 VALUES('user01', '1111', '유저1', '010-1111-1111');
INSERT INTO MEMBER_TBL_UNQ4 VALUES('user02', '1111', '유저1', '010-1111-1111');
-- PHONE은 겹치지만 ID가 다르기때문에 삽입됨
INSERT INTO MEMBER_TBL_UNQ4 VALUES('user01', '1111', '유저1', '010-1111-1112');
-- ID가 겹치지만 PHONE이 다르기때문에 삽입됨
INSERT INTO MEMBER_TBL_UNQ4 VALUES('user01', '1111', '유저1', '010-1111-1111');
-- 이경우에만 유니크제약조건에 걸림(ID,PHONE이 동시에 중복일 경우)

-- 한컬럼에 NOT NULL과 UNIQUE를 사용하고 싶을 때 방법 1
CREATE TABLE MEMBER_TBL_UNQ5(
        MEMBER_ID       VARCHAR2(20) NOT NULL,
        MEMBER_PW       VARCHAR2(30) NOT NULL,
        MEMBER_NAME     VARCHAR2(20),
        MEMBER_PHONE    CHAR(13) UNIQUE,
        UNIQUE(MEMBER_ID)
);

-- 한컬럼에 NOT NULL과 UNIQUE를 사용하고 싶을 때 방법 2
CREATE TABLE MEMBER_TBL_UNQ6(
        MEMBER_ID       VARCHAR2(20) NOT NULL UNIQUE, -- 띄어쓰기로 구분(콤마X)
        MEMBER_PW       VARCHAR2(30) NOT NULL,
        MEMBER_NAME     VARCHAR2(20),
        MEMBER_PHONE    CHAR(13) UNIQUE
);

INSERT INTO MEMBER_TBL_UNQ5
VALUES('user01', '1234', '유저1', '010-1111-1111');
INSERT INTO MEMBER_TBL_UNQ5
VALUES(null, '1234', '유저1', '010-1111-1111'); -- null에러
INSERT INTO MEMBER_TBL_UNQ5
VALUES('user01', '1111', '유저2', '010-1111-1112'); -- unique 에러

INSERT INTO MEMBER_TBL_UNQ6
VALUES('user01', '1234', '유저1', '010-1111-1111');
INSERT INTO MEMBER_TBL_UNQ6
VALUES('user02', '2222', '유저2', '010-2222-2222');
-- INSERT뿐만이 아닌 UPDATE때도 제약조건이 발생함
UPDATE MEMBER_TBL_UNQ6 SET MEMBER_PHONE = NULL WHERE MEMBER_ID = 'user02';
-- MEMBER_PHONE 은 not null이 아니기 때문에 정상적으로 업데이트됨
UPDATE MEMBER_TBL_UNQ6 SET MEMBER_ID = NULL WHERE MEMBER_NAME = '유저2';
-- MEMBER_ID는 NOT NULL제약조건이 있기때문에 수정불가
UPDATE MEMBER_TBL_UNQ6 SET MEMBER_ID = 'user01' WHERE MEMBER_NAME = '유저2';
-- 동일한 MEMBER_ID가 존재하므로 UNIQUE 제약조건에 의해 수정불가

 

▣ PRIMARY KEY : 테이블에서 한행의 정보를 구분하기 위한 고유식별자역할 / 한테이블당 한개만 설정가능

                                NOT NULL과 UNIQUE의 의미를 둘다 가지고있음

CREATE TABLE MAMBER_TBL_PK1(
    MEMBER_ID       VARCHAR2(20)     PRIMARY KEY,
    MEMBER_PW       VARCHAR2(30)     NOT NULL,
    MEMBER_NAME     VARCHAR2(20),
    MEMBER_PHONE    CHAR(13)         NOT NULL UNIQUE
);
INSERT INTO MEMBER_TBL_PK1 VALUES
('user01', '1234', '유저1', '010-1111-1111');
INSERT INTO MEMBER_TBL_PK1 VALUES
('user01', '2222', '유저2', '010-2222-2222');
INSERT INTO MEMBER_TBL_PK1 VALUES
(NULL, '2222', '유저2', '010-2222-2222');
-- PRIMARY KEY를 걸어주게되면 UNIQUE와 NOT NULL은 내포하고있음

CREATE TABLE MAMBER_TBL_PK2(
    MEMBER_ID       VARCHAR2(20),
    MEMBER_PW       VARCHAR2(30)     NOT NULL,
    MEMBER_NAME     VARCHAR2(20),
    MEMBER_PHONE    CHAR(13)        NOT NULL UNIQUE,
    PRIMARY KEY(MEMBER_ID)
); -- 테이블 레벨에서도 PRIMARY KEY사용가능

CREATE TABLE MAMBER_TBL_PK3(
    MEMBER_ID       VARCHAR2(20),
    MEMBER_PW       VARCHAR2(30)     NOT NULL,
    MEMBER_NAME     VARCHAR2(20),
    MEMBER_PHONE    CHAR(13)        NOT NULL UNIQUE,
 -- PRIMARY KEY(MEMBER_ID, MEMBER_PHONE) -- 두개를 합쳐서 하나의 PRIMARY KEY
    PRIMARY KEY(MEMBER_ID),
    PRIMARY KEY(MEMBER_PHONE) -- 안됨, 한테이블당 프라이머리키 1개
);

 

▣ FOREIGN KEY : 참조된 다른 테이블이 제공하는 값만 사용할 수 있도록 제한 / 참조무결성을 유지하기위한 제약조건

                                참조되는 테이블의 참조되는 컬럼은 PRIMARY KEY 또는 UNIQUE 제약조건 중 하나를 가져야함
                                최소조건이 UNIQUE

CREATE TABLE MEMBER_TBL(
    MEMBER_ID       VARCHAR2(20)    PRIMARY KEY,
    MEMBER_PW       VARCHAR2(30)    NOT NULL,
    MEMBER_NAME     VARCHAR2(20),
    MEMBER_NICKNAME VARCHAR2(20)    UNIQUE,
    MEMBER_PHONE    CHAR(13)        UNIQUE
);

INSERT INTO MEMBER_TBL
VALUES('user01', '1111', '유저1', '1번', '010-1111-1111');
INSERT INTO MEMBER_TBL
VALUES('user02', '2222', '유저2', '2번', '010-2222-2222');
INSERT INTO MEMBER_TBL
VALUES('user03', '3333', '유저3', '3번', '010-3333-3333');

CREATE TABLE BOARD1(
    BOARD_NO        NUMBER          PRIMARY KEY, -- 글번호
    BOARD_TITLE     VARCHAR(100)    NOT NULL,    -- 제목
    BOARD_CONTENT   VARCHAR(1000)   NOT NULL,    -- 내용
    BOARD_WRITER    VARCHAR(20) REFERENCES MEMBER_TBL(MEMBER_ID), -- 작성자
    BOARD_DATE      DATE                         -- 작성일
);
-- BOARD_WRITER는 MEMBER_TBL테이블의 MEMBER_ID를 참조할것
INSERT INTO BOARD1
VALUES(1, '첫번째 글', '내용은 없습니다', 'user01', SYSDATE);
INSERT INTO BOARD1
VALUES(2, '두번째 글', '내용은 없습니다', 'user02', SYSDATE);
INSERT INTO BOARD1
VALUES(3, '세번째 글', '내용은 없습니다', 'user03', SYSDATE);
INSERT INTO BOARD1
VALUES(4, '네번째 글', '내용은 없습니다', 'user04', SYSDATE);
-- parent key not found : user04라는 ID가 존재하지 않으므로 에러발생
-- MEMBER_TBL의 MEMBER_ID에 있는 사람만 글을 작성할 수있음 -> 외래키

CREATE TABLE BOARD2(
    BOARD_NO        NUMBER          PRIMARY KEY, -- 글번호
    BOARD_TITLE     VARCHAR(100)    NOT NULL,    -- 제목
    BOARD_CONTENT   VARCHAR(1000)   NOT NULL,    -- 내용
    BOARD_WRITER    VARCHAR(20),                 -- 작성자
    BOARD_DATE      DATE,                        -- 작성일
    FOREIGN KEY(BOARD_WRITER) REFERENCES MEMBER_TBL(MEMBER_ID)
);
-- 외래키도 테이블레벨에서 설정가능
-- FOREIGN KEY(외래키를지정할컬럼) REFERENCES 참조할테이블명(참조할컬럼)
-- BOARD1도 BOARD2도 MEMBER_TBL을 참조하고있음(참조만 하고있을 뿐 전혀다른테이블)
INSERT INTO BOARD2
VALUES(1, '첫번째 글', '내용은 없습니다', 'user01', SYSDATE);
INSERT INTO BOARD2
VALUES(2, '두번째 글', '내용은 없습니다', 'user02', SYSDATE);
INSERT INTO BOARD2
VALUES(3, '세번째 글', '내용은 없습니다', 'user03', SYSDATE);

CREATE TABLE BOARD3(
    BOARD_NO        NUMBER          PRIMARY KEY, -- 글번호
    BOARD_TITLE     VARCHAR(100)    NOT NULL,    -- 제목
    BOARD_CONTENT   VARCHAR(1000)   NOT NULL,    -- 내용
    BOARD_WRITER    VARCHAR(20) REFERENCES MEMBER_TBL(MEMBER_NICKNAME), -- 작성자
    BOARD_DATE      DATE                         -- 작성일
);
-- REFERENCES MEMBER_TBL(MEMBER_NAME): 외래키로 사용하려면 
-- UNIQUE 또는 PRIMARY KEY 가 필수이기 때문에 아무것도 설정되어있지 않은 NAME은
-- 에러발생

INSERT INTO BOARD3
VALUES(1, '첫번째 글', '내용은 없습니다', '1번', SYSDATE);
INSERT INTO BOARD3
VALUES(2, '두번째 글', '내용은 없습니다', '2번', SYSDATE);
INSERT INTO BOARD3
VALUES(3, '세번째 글', '내용은 없습니다', '3번', SYSDATE);
INSERT INTO BOARD3
VALUES(4, '네번째 글', '내용은 없습니다', 'user01', SYSDATE);
-- 해당하는(참조하고있는) 컬럼데이터만 들어갈 수 있음

-- 외래키 설정 시 컬럼명을 생략하면, 해당테이블의 PRIMARY KEY가 자동으로 선택됨
CREATE TABLE BOARD4(
    BOARD_NO        NUMBER          PRIMARY KEY, -- 글번호
    BOARD_TITLE     VARCHAR(100)    NOT NULL,    -- 제목
    BOARD_CONTENT   VARCHAR(1000)   NOT NULL,    -- 내용
    BOARD_WRITER    VARCHAR(20) REFERENCES MEMBER_TBL, -- 작성자
    BOARD_DATE      DATE                         -- 작성일
);
-- 지금까지는 MEMBER_TBL(컬럼명)으로 어떤컬럼인지 지정해줬는데
-- 위처럼 컬럼명을 생략하게되면 자동으로 해당하는 테이블의 PRIMARY KEY를 외래키로 가지고옴
INSERT INTO BOARD4
VALUES(1, '첫번째글', '내용없음', 'user01', SYSDATE);
INSERT INTO BOARD4
VALUES(2, '두번째글', '내용없음', '1번', SYSDATE); -- parent key not found에러

 

  FOREIGN KEY(외래키) 사용 시 지정하는 옵션 3가지

-- 멤버테이블(부모테이블)
CREATE TABLE MEMBER_TBL_FK(
    MEMBER_ID       VARCHAR2(20)    PRIMARY KEY,
    MEMBER_PW       VARCHAR2(30)    NOT NULL,
    MEMBER_NAME     VARCHAR2(20),
    MEMBER_NICKNAME VARCHAR2(20)    UNIQUE,
    MEMBER_PHONE    CHAR(13)        UNIQUE
);

-- 멤버테이블 INSERT
INSERT INTO MEMBER_TBL_FK
VALUES('user01', '1111', '유저1', '일호', '010-1111-1111');
INSERT INTO MEMBER_TBL_FK
VALUES('user02', '2222', '유저2', '이호', '010-2222-2222');
INSERT INTO MEMBER_TBL_FK
VALUES('user03', '3333', '유저3', '삼호', '010-3333-3333');
INSERT INTO MEMBER_TBL_FK
VALUES('user04', '4444', '유저4', '사호', '010-4444-4444');
INSERT INTO MEMBER_TBL_FK
VALUES('user05', '5555', '유저5', '오호', '010-5555-5555');

옵션1. ON DELETE RESTRICTED

        : 아무것도 지정하지 않는 경우 기본적으로 설정 / 참조하고있는 데이터가있으면 삭제불가. 

CREATE TABLE BOARD_FK1(
    BOARD_NO        NUMBER          PRIMARY KEY, 
    BOARD_TITLE     VARCHAR(100)    NOT NULL,    
    BOARD_CONTENT   VARCHAR(1000)   NOT NULL,    
    BOARD_WRITER    VARCHAR(20) REFERENCES MEMBER_TBL_FK, 
    BOARD_DATE      DATE                         
);

INSERT INTO BOARD_FK1
VALUES(1, '첫번째글', '내용무', 'user02', SYSDATE);
INSERT INTO BOARD_FK1
VALUES(2, '두번째글', '내용유', 'user01', SYSDATE);
INSERT INTO BOARD_FK1
VALUES(3, '세번째글', '잠이와용', 'user03', SYSDATE);

DELETE FROM MEMBER_TBL_FK WHERE MEMBER_ID = 'user04'; 
-- BOARD_FK1에 글을 작성하지 않은 user04는 바로 삭제됨

DELETE FROM MEMBER_TBL_FK WHERE MEMBER_ID = 'user01';
-- BOARD_FK1에 글을 작성한 user01는 삭제시 에러발생
-- BOARD_FK1이 MEMBER_ID = 'user01'을 참조하고있는데 MEMBER_TBL_FK에서 삭제해버리면
-- BOARD_FK1의 값도 이상해지는데? - 에러
-- 외래키를 설정할 때 옵션을 지정해 줄 수 있음
-- 현재 BOARD_FK1은 옵션설정이 되어있지않은 상태 : 기본값 '삭제불가'

 

옵션2. ON DELETE SET NULL
          : 내가 혹시 여기(부모테이블)서 지워지면, 나를 참조하고있는 애(자식테이블의 컬럼)를 null로 바꿔

CREATE TABLE BOARD_FK2(
    BOARD_NO        NUMBER          PRIMARY KEY, -- 글번호
    BOARD_TITLE     VARCHAR(100)    NOT NULL,    -- 제목
    BOARD_CONTENT   VARCHAR(1000)   NOT NULL,    -- 내용
    BOARD_WRITER    VARCHAR(20) REFERENCES MEMBER_TBL_FK ON DELETE SET NULL, -- 작성자
    BOARD_DATE      DATE                         -- 작성일
);

INSERT INTO BOARD_FK2
VALUES(1, '첫번째글', '내용1', 'user03', SYSDATE);
INSERT INTO BOARD_FK2
VALUES(2, '두번째글', '내용2', 'user05', SYSDATE);

DELETE FROM MEMBER_TBL_FK WHERE MEMBER_ID = 'user05';
-- 옵션을 지정해준 user05는 MEMBER_TBL_FK에서 삭제가능
-- BOARD_FK2에서 MEMBER_TBL(MEMBER_ID)를 참조하고있던 BOARD_WRITER의 데이터는 NULL로 바뀜
-- 이미 연결이 끊긴 글이기 때문에 다시 user03이 들어간다고해서 null -> user03으로 변경X

결과 화면 - 삭제 후 null이 된걸 확인할 수 있다.

옵션3. CASCADE
        : 부모 테이블에서 데이터삭제 시 해당데이터를 참조하고 있던 자식테이블의 데이터까지 모두삭제 

CREATE TABLE BOARD_FK3(
    BOARD_NO        NUMBER          PRIMARY KEY, -- 글번호
    BOARD_TITLE     VARCHAR(100)    NOT NULL,    -- 제목
    BOARD_CONTENT   VARCHAR(1000)   NOT NULL,    -- 내용
    BOARD_WRITER    VARCHAR(20) REFERENCES MEMBER_TBL_FK ON DELETE CASCADE, -- 작성자
    BOARD_DATE      DATE                         -- 작성일
);

INSERT INTO BOARD_FK3
VALUES(1, '첫번째글', '내용내용', 'user03', SYSDATE);
INSERT INTO BOARD_FK3
VALUES(2, '두번째글', '용내용내', 'user01', SYSDATE);
INSERT INTO BOARD_FK3
VALUES(3, '세번째글', '야호', 'user03', SYSDATE);
INSERT INTO BOARD_FK3
VALUES(4, '네번째글', '호야', 'user03', SYSDATE);

DELETE FROM MEMBER_TBL_FK WHERE MEMBER_ID = 'user03';
-- user03이 작성한 글만 자식테이블에서 삭제됨

user03 삭제 전 출력화면

user03 삭제 후 출력화면

 

▣ CHECK : 저장가능한 데이터 값의 범위/조건을 지정하여 설정한 값만 허용

CREATE TABLE MEMBER_TBL_CHECK(
    MEMBER_ID       VARCHAR2(20)    PRIMARY KEY,
    MEMBER_PW       VARCHAR2(30)    NOT NULL,
    MEMBER_NAME     VARCHAR2(20)    NOT NULL,       
    MEMBER_GENDER   CHAR(6)         CHECK(MEMBER_GENDER IN('남자', '여자'))
    -- 성별(남자,여자/남,여/M,F) 여러가지로 입력할 수 있음
    -- CHECK제약조건을 통해 MEMBER_GENDER라는 컬럼에는 '남자', '여자'만 들어갈 수 있음
    -- 들어갈 수 있는 값을 제한해주는 것
);

INSERT INTO MEMBER_TBL_CHECK
VALUES('user01', '1111', '유저1', '남자');
INSERT INTO MEMBER_TBL_CHECK
VALUES('user02', '2222', '유저2', '여자');
INSERT INTO MEMBER_TBL_CHECK
VALUES('user03', '3333', '유저3', '남');
-- check constraint violated : '남자', '여자'라는 값만 허용하는데 다른값이 들어오면 에러

 

▣ DEFAULT : 기본값 설정

CREATE TABLE MEMBER_TBL_DEFAULT(
    MEMBER_ID       VARCHAR2(20)    PRIMARY KEY,
    MEMBER_PW       VARCHAR2(30)    NOT NULL,
    MEMBER_NAME     VARCHAR2(20),
    MEMBER_NICNAME  VARCHAR2(20)   DEFAULT '별명없음',
    MEMBER_AGE      NUMBER  DEFAULT 0
);

INSERT INTO MEMBER_TBL_DEFAULT(MEMBER_ID, MEMBER_PW, MEMBER_NAME, MEMBER_NICNAME, MEMBER_AGE)
VALUES('user01', '1111', '유저1', '일호', 30);
INSERT INTO MEMBER_TBL_DEFAULT(MEMBER_ID, MEMBER_PW)
VALUES('user02', '1234');
INSERT INTO MEMBER_TBL_DEFAULT
VALUES('user03', '3333', NULL, DEFAULT, DEFAULT);
-- DEFAULT는 INSERT에서만 사용가능(UPDATE, WHERE절 등에서는 사용불가)
SELECT * FROM MEMBER_TBL_DEFAULT WHERE MEMBER_AGE = DEFAULT; -- 사용불가

결과화면

+ 서브쿼리를 이용해서 테이블 생성하기

컬럼이름, 자료형, 길이, 조회된 결과(데이터)가 그대로 복사 / 단, 제약조건은 NOT NULL만 복사

-- 컬럼이름, 자료형, 길이, 조회된 결과(데이터)가 그대로 복사  
-- 단, 제약조건은 NOT NULL만 복사
CREATE TABLE MEMBER_TBL_FK_COPY
AS 
SELECT * FROM MEMBER_TBL_FK;
-- AS SELECT문 : SELECT문에서 나온 결과(자료형,컬럼명그대로)가 테이블로 만들어진다
-- 테이블 구조를 복사할 때 주로 사용

CREATE TABLE MEMBER_TBL_FK_COPY
AS 
SELECT * FROM MEMBER_TBL_FK_COPY WHERE 1=0;
-- WHERE절에 FALSE인 조건을 넣어 생성하면 데이터없이 구조만 복사가된다

 

-- 오늘 수업 종합연습문제 --

테이블을 생성하고 / 제약조건을 설정하고 / 삭제 시 자식테이블에 연관된 데이터도 삭제되도록

-- EXPERT_TBL 테이블
CREATE TABLE EXPERT_TBL(
    MEMBER_CODE     NUMBER          PRIMARY KEY,
    MEMBER_ID       VARCHAR2(20)    UNIQUE,
    MEMBER_PWD      VARCHAR2(20)    NOT NULL,
    MEMBER_NAME     VARCHAR2(10)    NOT NULL,
    MEMBER_ADDR     VARCHAR2(50)    NOT NULL,
    GENDER          CHAR(3) CHECK(GENDER IN('남', '여')),
    PHONE           CHAR(13)
);

-- Company테이블
-- 회원이 삭제되는경우 COMPANY_TBL의 삭제회원 연관정보는 모두 삭제되게 만드세요
-- 데이터를 입력 및 삭제해서 정삭적으로 동작하는지 확인해보세요
CREATE TABLE COMPANY_TBL(
    MEMBER_CODE     NUMBER  REFERENCES EXPERT_TBL ON DELETE CASCADE, --회원전용코드
    SKILL           VARCHAR2(50)    -- 회원능력
);

INSERT INTO EXPERT_TBL
VALUES(1, 'user01', '1111', '사원1', '서울시 영등포구', '남', '010-1111-1111');
INSERT INTO EXPERT_TBL
VALUES(2, 'user02', '2222', '사원2', '서울시 강남구', '여', '010-2222-2222');

INSERT INTO COMPANY_TBL VALUES(2, '능력A');
INSERT INTO COMPANY_TBL VALUES(1, '능력B');

DELETE FROM COMPANY_TBL WHERE MEMBER_CODE = 1;

 

 

[ 후기 ] -------------------------------------------------------------------------------------------------------------------------------------------------------

 

너무 오랜만에 배워서 그런지 기억나는게 하나도없었다ㅎ

다른 제약조건은 다 괜찮았는데 외래키 제약조건이 자꾸 헷갈려서 실수가 많았다.

이론자체는 이해가되는데, 막상 작성하려니 아직 못외워서 그런지 자꾸 했던거 확인하면서 했다.

몇번 작성하면서 익숙해져야겠다

 

오늘 실수한 점

없음!