✿∘˚˳°∘°
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
옵션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이 작성한 글만 자식테이블에서 삭제됨
▣ 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;
[ 후기 ] -------------------------------------------------------------------------------------------------------------------------------------------------------
너무 오랜만에 배워서 그런지 기억나는게 하나도없었다ㅎ
다른 제약조건은 다 괜찮았는데 외래키 제약조건이 자꾸 헷갈려서 실수가 많았다.
이론자체는 이해가되는데, 막상 작성하려니 아직 못외워서 그런지 자꾸 했던거 확인하면서 했다.
몇번 작성하면서 익숙해져야겠다
오늘 실수한 점
없음!
'국비수업 > DB' 카테고리의 다른 글
| 23일차 : PL/SQL (0) | 2022.12.28 |
|---|---|
| 22일차 : ALTER, DROP / TCL / OBJECT (0) | 2022.12.27 |
| 20일차 : GROUP BY / JOIN (0) | 2022.12.23 |
| 19일차 : 함수 (0) | 2022.12.22 |
| 18일차 : SELECT, 연산자 (0) | 2022.12.21 |
'국비수업/DB' Related Articles
more
Comments