[배운내용정리] 1221 자바 국비교육 _ Oracle

2020년 12월 21일 9시 ~ 15시 30분 zoom으로 수업 진행

 

---

CHECK 제약조건

 컬럼에 값을 기록할 때 지정한 값 이외에는 값이 기록되지 않도록 범위를 제한하는 조건

 CHECK(컬럼명   비교연산자   값)

 

PRIMARY KEY 제약조건

 '기본키 제약조건'

 테이블 내에서 한 행 한 행을 식별하기 위한 고유의 값을 가지는 컬럼

 NOT NULL, UNIQUE 제약 조건을 함께 걸어준다

 테이블 전체에 각 데이터들간의 식별자 역할을 수행시키는 제약조건이다

 

FOREIGN KEY 제약조건

 외래키, 외부키, 참조키

 다른 테이블의 컬럼 값을 참조하여(REFERENCE)

 참조하는 테이블의 값만 허용한다

 해당 제약조건을 통해 다른 테이블과의 관계(RELATIONSHIP)가 형성된다.

 

컬럼레벨

 REFERENCES 참조할테이블명[(참조할 컬럼명)] [삭제 옵션]

 

테이블 레벨

 FOREIGN KEY(적용할 컬럼) REFERENCES [(참조할테이블명)] [삭제 옵션]

 

FOREIGN KEY 제약조건 : 참조할 컬럼 명이 생략 될 경우

 참조 대상 테이블의 기본키를 참조 할 컬럼으로 가져온다.

 참조하고자 하는 컬럼은 반드시 PRIMARY KEY 이거나 UNIQUE 제약조건이 걸려있어야 한다.

 

DML (데이터 조작 언어)

 INSERT, UPDATE, DELETE, SELECT  -> 통틀어서 CRUD라고 부른다.

 [CRUD]

 C(CREATE) : INSERT / 데이터 추가

 R(READ)   : SELECT / 데이터 조회

 U(UPDATE) : UPDATE / 데이터 수정

 D(DELETE) : DELETE / 데이터 삭제

 

 

INSERT

 새로운 행을 특정 테이블에 추가하는 명령어

 사용형식

  1. 특정 컬럼에 값을 추가하는 방법

    INSERT INTO 테이블명(컬럼명 , ...) VALUES(값1, 값2, ...);

  2. 모든 컬럼에 값을 추가하는 방법

    INSERT INTO 테이블명 VALUES(값1, 값2, ...);

 

 

---

 

--1221
--DAY 6

--UNIQUE 제약조건을 여러 컬럼에 적용하기
--D1 --200
--D1 --201
--D2 --200
--D2 --201
--예를들어, 이름 컬럼과 주민번호 컬럼이 있다면 , 한 컬럼에만 UNIQUE 가 걸려있을 땐
--김민지 + 940922~ 김민지 + 870708~ 은 같이 있을 수 없다. 하지만 여러컬럼에
-- UNIQUE를 걸게되면 둘다 중복되야 중복자료로 인정하고 오류가 뜬다

CREATE TABLE USER_UNIQUE3(
    USER_NO NUMBER,
    USER_ID VARCHAR2(20),
    USER_PWD VARCHAR2(30),
    USER_NAME VARCHAR2(15),
    UNIQUE(USER_NO,USER_ID)
);

INSERT INTO USER_UNIQUE3
VALUES(1,'USER01','PASS01','LEE');

 INSERT INTO USER_UNIQUE3
VALUES(1,'USER02','PASS02','KIM');

 INSERT INTO USER_UNIQUE3
VALUES(2,'USER01','PASS03','HONG');

 INSERT INTO USER_UNIQUE3
VALUES(2,'USER02','PASS04','BEAK');

SELECT * FROM USER_UNIQUE3;

--제약조건에 이름 설정하기
CREATE TABLE CONS_NAME(
    NO1 NUMBER CONSTRAINT NN_NO1 NOT NULL,
    DATA1 VARCHAR2(20) CONSTRAINT UN_DATA UNIQUE,
    DATA2 VARCHAR(20),
    CONSTRAINT UK_DATA2 UNIQUE(DATA2)
);

--CONS_NAME테이블에서 선언한 제약조건들 전보 조회하는 코드
SELECT * FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'CONS_NAME';



--CHECK 제약조건
--컬럼에 값을 기록할 때 지정한 값 이외에는 값이 기록되지 않도록 범위를 제한하는 조건
--CHECK(컬럼명   비교연산자   값)

CREATE TABLE USER_CHECK(
    NO NUMBER,
    ID VARCHAR2(20),
    PW VARCHAR2(30),
    NAME VARCHAR2(15),
    GENDER CHAR(3) CHECK (GENDER IN ('남','여'))
);

INSERT INTO USER_CHECK
VALUES(1,'USER01','PASS01','LEE','남'); --체크조건을 만족함

INSERT INTO USER_CHECK
VALUES(1,'USER01','PASS01','LEE','남자');--체크조건 불만족 오류

INSERT INTO USER_CHECK
VALUES(1,'USER01','PASS01','LEE','M');--체크조건 불만족 오류

INSERT INTO USER_CHECK
VALUES(2,'USER02','PASS02','BAE','여'); --체크조건을 만족함

SELECT * FROM USER_CHECK;


--CHECK 제약조건에 부등호로 표기
CREATE TABLE TEST_CHECK2(
    TEST_DATA NUMBER,
    CONSTRAINT CK_TEST_DATA CHECK(TEST_DATA >0)
);

INSERT INTO TEST_CHECK2 VALUES(10);

INSERT INTO TEST_CHECK2 VALUES(-10);
--넣는 데이터가 0보다 작아서 check 제약조건에 위배되어 오류난다.
--check constraint

SELECT * FROM TEST_CHECK2;


CREATE TABLE TEST_CHECK3(
    CPRICE NUMBER,
    CDATE DATE,
    CONSTRAINT CK_TEST3_CPRICE CHECK(CPRICE BETWEEN 1 AND 999999),
    CONSTRAINT CK_TEST3_CDATE CHECK(CDATE >= TO_DATE('2020/01/01', 'YYYY/MM/DD'))
);

--제약조건 여러 개 설정
CREATE TABLE TEST_DOUBLE_CONS (
    TEST_NO NUMBER NOT NULL UNIQUE,
    TEST_NO2 NUMBER NOT NULL
);

SELECT * FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = 'TEST_DOUBLE_CONS';
--제약조건을 동시에 선언하는 것 뿐이지 제약조건의 이름이 같은 건 아님


--PRIMARY KEY 제약조건
--'기본키 제약조건'
--테이블 내에서 한 행 한 행을 식별하기 위한 고유의 값을 가지는 컬럼
--NOT NULL, UNIQUE 제약 조건을 함께 걸어준다
--테이블 전체에 각 데이터들간의 식별자 역할을 수행시키는 제약조건이다
--
CREATE TABLE USER_PK_TABLE(
    USER_NO NUMBER CONSTRAINT PK_USER_NO PRIMARY KEY,
    USER_ID VARCHAR2(20) UNIQUE,
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(15) NOT NULL,
    GENDER CHAR(30) CHECK(GENDER IN('남', '여'))
);

SELECT * FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'USER_PK_TABLE';

INSERT INTO USER_PK_TABLE VALUES(1, 'USER01', 'PASS01','LEE', '남');
INSERT INTO USER_PK_TABLE VALUES(2, 'USER02', 'PASS02','KIM', '여');

INSERT INTO USER_PK_TABLE VALUES(1, 'USER03', 'PASS03','PARK', '남');
--unique constraint (TEST.PK_USER_NO) violated 에러난다. 

INSERT INTO USER_PK_TABLE VALUES(NULL, 'USER03', 'PASS03','PARK', '남');
--cannot insert NULL into ("TEST"."USER_PK_TABLE"."USER_NO") 에러난다
--기본키 제약조건을 설정할 경우 자동으로 NOT NULL, UNIQUE 설정된다.


--기본키 제약조건을 여러 컬럼에 적용
CREATE TABLE USER_PK_TABLE2(
    USER_NO NUMBER,
    USER_ID VARCHAR2(20) UNIQUE,
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(15) NOT NULL,
    GENDER CHAR(30) CHECK(GENDER IN('남', '여')),
    --CONSTRAINT PK_USER_NO PRIMARY KEY 는 안됨 -> 다른 테이블에서 같은 이름을 가진 제약조건이 존재하기 떄문
    CONSTRAINT PK_USER_NO2 PRIMARY KEY(USER_NO, USER_ID)
);
--1, 'USER01' ==> 성공
--2, 'USER02' ==> 성공
--1, 'USER03' ==> 성공 (왜 되냐구? PRIMARY KEY 제약조건을 USER_NO, USER_ID 두 개 묶어서 설정했기 때문에!!
--                      두 개 한번에 비교했을때가 기준이 됨! user_id 유니크 제약조건 위배x , user_no는 제약조건 건게 없어서 삽가능!
--2, 'USER02' ==> 실패 (USER_ID 를 UNIQUE로 설정해서 유니크 제약조건에 위배되니까 안되는 것)
--user_no, user_id 를 묶어서 기본키 제약 조건을 설정한것이다!

--DROP은 객체를 삭제한다.
DROP TABLE MEMBER;


--FOREIGN KEY 제약조건
--외래키, 외부키, 참조키
--다른 테이블의 컬럼 값을 참조하여(REFERENCE)
--참조하는 테이블의 값만 허용한다
--해당 제약조건을 통해 다른 테이블과의 관계(RELATIONSHIP)가 형성된다.

--컬럼레벨
--REFERENCES 참조할테이블명[(참조할 컬럼명)] [삭제 옵션]

--테이블 레벨
--FOREIGN KEY(적용할 컬럼) REFERENCES [(참조할테이블명)] [삭제 옵션]


--참조할 컬럼 명이 생략 될 경우
--참조 대상 테이블의 기본키를 참조 할 컬럼으로 가져온다.
--참조하고자 하는 컬럼은 반드시 PRIMARY KEY 이거나 UNIQUE 제약조건이 걸려있어야 한다.

DROP TABLE USER_GRADE;

CREATE TABLE USER_GRADE(
    GRADE_CODE NUMBER PRIMARY KEY,
    GRADE_NAME VARCHAR2(30) NOT NULL
);

INSERT INTO USER_GRADE VALUES(1, '일반회원');
INSERT INTO USER_GRADE VALUES(2, 'SILVER');
INSERT INTO USER_GRADE VALUES(3, 'GOLD');
INSERT INTO USER_GRADE VALUES(4, 'VIP');
INSERT INTO USER_GRADE VALUES(10, 'MANAGER');

SELECT * FROM USER_GRADE;

--
DROP TABLE USER_FOREIGN_KEY;

CREATE TABLE USER_FOREIGN_KEY(
    USER_NO NUMBER PRIMARY KEY,
    USER_ID VARCHAR2(20),
    USER_PWD VARCHAR2(30),
    USER_NAME VARCHAR2(15),
    GENDER CHAR(1) CHECK(GENDER IN('M', 'F')),
    GRADE_CODE NUMBER,
    FOREIGN KEY(GRADE_CODE) REFERENCES USER_GRADE(GRADE_CODE)
    --USER_FOREIGN_KEY테이블의 GRADE_CODE컬럼에 FK_GRADE_CODE 라는 이름의 외래키 제약조건을 걸 것이다.
    --이는 참조테이블 USER_GRADE의 GRADE_CODE컬럼을 참조할것으로 설정한다.
);

INSERT INTO USER_FOREIGN_KEY VALUES(1, '123', '321' , 'LEE', 'F', 2);
INSERT INTO USER_FOREIGN_KEY VALUES(2, 'ABC', 'CBA' , 'KIM', 'M', 4);
INSERT INTO USER_FOREIGN_KEY VALUES(3, '456', '654' , 'PARK', 'M', 1);
INSERT INTO USER_FOREIGN_KEY VALUES(4, 'DEF', 'FED' , 'CHO', 'F', 3);
INSERT INTO USER_FOREIGN_KEY VALUES(5, 'QWE', 'EWQ' , 'JANG', 'F', 1);

SELECT * FROM user_foreign_key;

INSERT INTO USER_FOREIGN_KEY VALUES(6, 'QWE', 'EWQ' , 'JANG', 'F', 10);
--integrity constraint (TEST.FK_GRADE_CODE) violated - parent key not found 에러난다.
--참조하는 테이블의 컬럼에 10이라는 값이 없어서 발생하는 에러
--INSERT INTO USER_GRADE VALUES(10, 'MANAGER'); 추가해주니 에러가 발생하지 않음


SELECT * 
FROM user_foreign_key
JOIN USER_GRADE USING(GRADE_CODE);


--삭제 옵션
DELETE FROM USER_GRADE
WHERE GRADE_CODE = 10;
--에러난다 integrity constraint (TEST.FK_GRADE_CODE) violated - child record found
--참조하는 데이터? 테이블? 이 있을 경우 삭제하지 못 한다.

--참조되고(USER_GRADE) 있는 원본 테이블의 걸럼 값이 삭제될 때
--참조하고(USER_FOREIGN_KEY) 있는 값을 어떻게 처리할 것인지 설정하는 옵션

--일반적으로 원본 컬럼 내용을 삭제하고자 할 때 외래키로 사용중인 자식이 존재한다면 함부로 삭제할 수 없다.

--1. 부모 컬럼을 삭제할 때 자식을 NULL 로 변경하기
--ON DELETE SET NULL
--2. 부모 컬럼을 삭제할 때 관련된 자식도 함께 삭제하기
--ON DELETE CASCADE

--
DROP TABLE USER_GRADE;
--unique/primary keys in table referenced by foreign keys 에러

--USER_FOREIGN_KEY을 먼저 지우고, USER_GRADE을 지우면 된다 -> 참조되는 테이블이 없기때문에
DROP TABLE USER_FOREIGN_KEY;
DROP TABLE USER_GRADE;



CREATE TABLE USER_GRADE(
    GRADE_CODE NUMBER PRIMARY KEY,
    GRADE_NAME VARCHAR2(30) NOT NULL
);

INSERT INTO USER_GRADE VALUES(1, '일반회원');
INSERT INTO USER_GRADE VALUES(2, 'SILVER');
INSERT INTO USER_GRADE VALUES(3, 'GOLD');
INSERT INTO USER_GRADE VALUES(4, 'VIP');
INSERT INTO USER_GRADE VALUES(10, 'MANAGER');

SELECT * FROM USER_GRADE;

--1. USER_FOREIGN_KEY  + ON DELETE SET NULL
CREATE TABLE USER_FOREIGN_KEY(
    USER_NO NUMBER PRIMARY KEY,
    USER_ID VARCHAR2(20),
    USER_PWD VARCHAR2(30),
    USER_NAME VARCHAR2(15),
    GENDER CHAR(1) CHECK(GENDER IN('M', 'F')),
    GRADE_CODE NUMBER,
    FOREIGN KEY(GRADE_CODE) 
    REFERENCES USER_GRADE(GRADE_CODE) ON DELETE SET NULL
);

INSERT INTO USER_FOREIGN_KEY VALUES(1, '123', '321' , 'LEE', 'F', 2);
INSERT INTO USER_FOREIGN_KEY VALUES(2, 'ABC', 'CBA' , 'KIM', 'M', 4);
INSERT INTO USER_FOREIGN_KEY VALUES(3, '456', '654' , 'PARK', 'M', 1);
INSERT INTO USER_FOREIGN_KEY VALUES(4, 'DEF', 'FED' , 'CHO', 'F', 3);
INSERT INTO USER_FOREIGN_KEY VALUES(5, 'QWE', 'EWQ' , 'JANG', 'F', 1);

SELECT * FROM USER_FOREIGN_KEY;

COMMIT;
--지우기 전에 데이터베이스 내용 저장
--커밋 시점까지만 데이터가 저장됨

DELETE FROM USER_GRADE
WHERE GRADE_CODE = 4;
--삭제 된다.
SELECT * FROM USER_GRADE;
--진짜 삭제됐다.
SELECT * FROM USER_FOREIGN_KEY;
--데이터가 삭제될 경우, 해당 데이터를 참조하고 있던 자식의 값을 NULL 값으로 바꿔버린다.


--2. USER_FOREIGN_KEY  + ON DELETE CASCADE
DROP TABLE USER_FOREIGN_KEY;
CREATE TABLE USER_FOREIGN_KEY(
    USER_NO NUMBER PRIMARY KEY,
    USER_ID VARCHAR2(20),
    USER_PWD VARCHAR2(30),
    USER_NAME VARCHAR2(15),
    GENDER CHAR(1) CHECK(GENDER IN('M', 'F')),
    GRADE_CODE NUMBER,
    FOREIGN KEY(GRADE_CODE) 
    REFERENCES USER_GRADE(GRADE_CODE) ON DELETE CASCADE
);
INSERT INTO USER_FOREIGN_KEY VALUES(1, '123', '321' , 'LEE', 'F', 2);
INSERT INTO USER_FOREIGN_KEY VALUES(2, 'ABC', 'CBA' , 'KIM', 'M', 4);
INSERT INTO USER_FOREIGN_KEY VALUES(3, '456', '654' , 'PARK', 'M', 1);
INSERT INTO USER_FOREIGN_KEY VALUES(4, 'DEF', 'FED' , 'CHO', 'F', 3);
INSERT INTO USER_FOREIGN_KEY VALUES(5, 'QWE', 'EWQ' , 'JANG', 'F', 1);

SELECT * FROM USER_GRADE;
SELECT * FROM USER_FOREIGN_KEY;

DELETE FROM USER_GRADE
WHERE GRADE_CODE = 2;
--행 전체가 삭제되었다.
--부모가 삭제되면 그걸 참조하고있던 자식 데이터 자체가 삭제된다.

---------------------------------------------
SELECT * FROM EMPLOYEE;


--SUBQUERY 활용한 테이블 만들기
--SUBQUERY 사용하여 테이블을 생성할 경우
--컬럼명, 데이터 타입, 값, NOT NULL 은 복사 가능
--하지만 다른 제약조건은 복사되지 않는다!

CREATE TABLE EMPLOYEE_COPY 
AS SELECT * FROM EMPLOYEE;

CREATE TABLE EMPLOYEE_COPY2
AS SELECT EMP_ID, EMP_NAME FROM EMPLOYEE;
--서브쿼리를 이용한 테이블 생성 시 , 셀렉트문의 결과를 바탕으로 테이블을 만드는 것이라 EMP_ID, EMP_NAME만 SELECT 시키고 테이블을 복사하면
--복사되는 테이블의 컬럼은 EMP_ID, EMP_NAME만 복사된다.

--원본 테이블
SELECT * FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMPLOYEE';

--서브쿼리를 이용한 테이블 복사
SELECT * FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMPLOYEE_COPY';
--USER_NAME 에 걸려있던 기본키 제약조건이 복사되지 않은 모습을 볼 수 있다.

SELECT * FROM EMPLOYEE_COPY;
--값도 잘 복사되어 있다.

SELECT * FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMPLOYEE_COPY2';

--테이블의 형식만 복사하기
--저장된 값 제외
DROP TABLE EMPLOYEE_COPY2;

CREATE TABLE EMPLOYEE_COPY2
AS SELECT * FROM EMPLOYEE WHERE 1=2;
--WHERE의 조건이 FALSE 인 상태로 서브쿼리가 실행되는 것이라 형식, 타입은 그대로 가져오나
--값이 없는 상태로 테이블이 복사.생성 된다.

SELECT * FROM EMPLOYEE_COPY2;
--값은 없으나 형식은 그대로 유지되어있는 모습 확인
SELECT * FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMPLOYEE_COPY2';
--제약조건도 잘 복사되어 있다.


--기본 값 설정하기
CREATE TABLE DEFAULT_TABLE(
    COL1 VARCHAR2(30) DEFAULT '없음',
    COL2 DATE DEFAULT SYSDATE
);

INSERT INTO DEFAULT_TABLE
VALUES(DEFAULT, DEFAULT);

SELECT * FROM DEFAULT_TABLE;
--DEFAULT 값대로 데이터가 저장되어 있는 모습을 확인 할 수 있다.


--제약조건을 테이블 생성 후 추가하는 DDL
-- [ALTER]
--ALTER TABLE 테이블명 ADD 제약조건~ : PRIMARY KEY . FOREIGN KEY, UNIQUE, CHECK
--ALTER TABLE 테이블명 MODIFY 컬럼명 NOT NULL  -> NOT NULL은 이렇게 사용해야 한다.
--NOT NULL은 컬럼 레벨 제약조건으로만 설정할 수 있음을 알고 지나가자.
ALTER TABLE EMPLOYEE ADD FOREIGN KEY(DEPT_CODE) REFERENCES DEPARTMENT;
ALTER TABLE EMPLOYEE ADD FOREIGN KEY(SAL_LEVEL) REFERENCES SAL_GRADE;

ALTER TABLE EMPLOYEE ADD CHECK(ENT_YN IN ('Y', 'N'));
ALTER TABLE EMPLOYEE ADD UNIQUE(EMP_NO);

ALTER TABLE DEPARTMENT ADD FOREIGN KEY(LOCATION_ID) REFERENCES LOCATION;


-------------------------------------------------------------------------------------
--DML (데이터 조작 언어)
--INSERT, UPDATE, DELETE, SELECT  -> 통틀어서 CRUD라고 부른다.
--[CRUD]
--C(CREATE) : INSERT / 데이터 추가
--R(READ)   : SELECT / 데이터 조회
--U(UPDATE) : UPDATE / 데이터 수정
--D(DELETE) : DELETE / 데이터 삭제


--INSERT : 새로운 행을 특정 테이블에 추가하는 명령어
--사용형식
--1. 특정 컬럼에 값을 추가하는 방법
--INSERT INTO 테이블명(컬럼명 , ...) VALUES(값1, 값2, ...);
--2. 모든 컬럼에 값을 추가하는 방법
--INSERT INTO 테이블명 VALUES(값1, 값2, ...);

DESC EMPLOYEE;

--컬럼명을 명시하여 데이터 추가
INSERT INTO EMPLOYEE(EMP_ID, EMP_NAME, EMP_NO, EMAIL, PHONE, DEPT_CODE, JOB_CODE, SAL_LEVEL, SALARY, BONUS, MANAGER_ID, HIRE_DATE, ENT_DATE, ENT_YN)
VALUES(500, '이소근', '700101-1234567', 'leesg@naver.com', '01011112222', 'D1', 'J7', 'S4', 3100000, 0.1, '200', SYSDATE, NULL, DEFAULT);
SELECT * FROM EMPLOYEE WHERE EMP_ID = 500;


--컬럼을 생략하고 사용하기
INSERT INTO EMPLOYEE VALUES(900, '밥보검', '510101-1234567', 'bob_bg@naver.com', '01022223344', 'D1', 'J7', 'S3', 4300000, 0.2, '200', SYSDATE, NULL, DEFAULT);
--확인하기
SELECT * FROM EMPLOYEE WHERE EMP_ID = 900;

COMMIT;


--INSERT +  SUBQUERY
CREATE TABLE EMP_01(
    EMP_ID NUMBER,
    EMP_NAME VARCHAR2(20),
    DEPT_TITLE VARCHAR2(40)
);

INSERT INTO EMP_01(SELECT EMP_ID, EMP_NAME, DEPT_TITLE
                    FROM EMPLOYEE
                    LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
);

SELECT * FROM EMP_01;