데이터 베이스 구현 - SELECT(SUBQUERY), SQL활용 - DDL(CREATE)

1. SUBQUERY (서브쿼리) 

- 하나의 주된 SQL문(SELECT, INSERT,UPDATE, DELETE, CREATE...) 안에 포함된 또 하나의 QUERY문, 메인 SQL문을 보조해주는 QUERY문

간단 서브쿼리 예시 

- 전체사원의 평균급여보다 더 많은 급여를 받고 있는 사원들의 사번, 이름, 직급코드 조회

--1) 전체 사원의 평균 급여 구하기
SELECT ROUND(AVG(SALARY))
    FROM EMPLOYEE; --대략 3,047,663원

--2) 급여가 3,047,663원 이상인 사원들 조회
SELECT EMP_ID, EMP_NAME, JOB_CODE
FROM EMPLOYEE
WHERE SALARY > 3047663;

--3) 합치기
SELECT EMP_ID, EMP_NAME, JOB_CODE
FROM EMPLOYEE
WHERE SALARY > (SELECT ROUND(AVG(SALARY))
                 FROM EMPLOYEE);

*  서브쿼리 구분: 서브쿼리를 수행한 결과값이 몇 행 몇 열이냐에 따라서 분류

    - 단일행 [단일열] 서브쿼리 : 서브쿼리를 수행한 결과값이 오로지 1개일 때 
    - [단일행] 다중행 서브쿼리 : 서브쿼리를 수행한 결과값이 여러 행일 때
    - [단일행] 다중열 서브쿼리 : 서브쿼리를 수행한 결과값이 여러 열일 때 
    - 다중행 다중열 서브쿼리 : 서브쿼리를 수행한 결과값이 여러 행 여러 열일 때
    => 서브쿼리를 수행한 결과가 몇 행 몇 열이냐에 따라 사용가능한 연산자도 달라짐

 

1) 단일 행 서브쿼리(SINGLE ROW SUBQUERY)

- 서브쿼리의 조회 결과값이 오로지 1개일 때

- 일반 연산자 사용가능(=, !=, >=, <)

 

- 전 직원의 평균 급여보다 더 적게 받는 사원들의 사원명, 직급코드, 급여 조회 

--1)
SELECT AVG(SALARY)
FROM EMPLOYEE;

--2) 
SELECT EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY < 3047662;

--3)
SELECT EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY < (SELECT AVG(SALARY)
                FROM EMPLOYEE);

- 최저급여를 받는 사원의 사번, 사원명, 직급코드, 급여, 입사일 조회 

--1) 
SELECT MIN(SALARY)
FROM EMPLOYEE;

--2) 
SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY, HIRE_DATE
FROM EMPLOYEE
WHERE SALARY = 1380000;

--3) 
SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY, HIRE_DATE
FROM EMPLOYEE
WHERE SALARY = (SELECT MIN(SALARY)
                FROM EMPLOYEE);

- 실습) 노옹철 사원의 급여보다 더 많이 받는 사원들의 사번, 이름, 부서코드, 급여 조회 

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY > (SELECT SALARY
                    FROM EMPLOYEE
                    WHERE EMP_NAME = '노옹철');

- 노옹철 사원의 급여보다 더 많이 받는 사원들의 사번, 이름, 부서명, 급여 조회

SELECT EMP_ID, EMP_NAME, DEPT_TITLE, SALARY
FROM EMPLOYEE, DEPARTMENT --조인
WHERE SALARY > (SELECT SALARY
                    FROM EMPLOYEE
                    WHERE EMP_NAME = '노옹철')
AND DEPT_CODE = DEPT_ID;

- 전지연과 같은 부서인 사원들의 사번, 사원명, 전화번호, 직급명 조회(단, 전지연은 제외)

--> ORACLE
SELECT EMP_ID, EMP_NAME, PHONE, JOB_NAME
FROM EMPLOYEE E, JOB J
WHERE E.JOB_CODE = J.JOB_CODE
AND E.DEPT_CODE = (SELECT DEPT_CODE
                    FROM EMPLOYEE
                    WHERE EMP_NAME = '전지연')
AND EMP_NAME != '전지연';
--> ANSI
SELECT EMP_ID, EMP_NAME, PHONE, JOB_NAME
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE DEPT_CODE = (SELECT DEPT_CODE
                    FROM EMPLOYEE
                    WHERE EMP_NAME = '전지연')
AND EMP_NAME != '전지연';

- 부서별 급여합이 가장 큰 부서 하나만을 조회 부서코드, 부서명, 급여합 조회 

--> ORACLE
SELECT DEPT_CODE, DEPT_TITLE, SUM(SALARY)
FROM EMPLOYEE, DEPARTMENT
GROUP BY DEPT_CODE, DEPT_TITLE
HAVING DEPT_CODE = DEPT_ID
AND SUM(SALARY) = (SELECT MAX(SUM(SALARY))
                     FROM EMPLOYEE
                    GROUP BY DEPT_CODE);
--> ANSI        
SELECT DEPT_CODE, DEPT_TITLE, SUM(SALARY)
FROM EMPLOYEE
JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
GROUP BY DEPT_CODE, DEPT_TITLE
HAVING SUM(SALARY) = (SELECT MAX(SUM(SALARY))
                       FROM EMPLOYEE
                      GROUP BY DEPT_CODE);

2) 다중행 서브쿼리(MULTI ROW SUBQUERY)

: 서브쿼리의 조회 결과값이 여러 행

    - IN : 여러개의 결과 값 중에서 하나라도 일치하는 값이 있으면
    - NOT IN : 아예 없으면
    - > ANY : 여러개의 결과 값 중에서 "하나라도" 클 경우
    - < ANT : 여러개의 결과 값 중에서 "하나라도" 작을 경우

 

-사원 < 대리 < 과장 < 차장 < 부장 

-대리직급임에도 불구하고 과장직급의 급여보다 많이 받는 직원 조회(사번, 이름, 직급명, 급여) 

--1) 과장직급의 급여
SELECT SALARY
FROM EMPLOYEE E, JOB J
WHERE E.JOB_CODE = J.JOB_CODE
AND JOB_NAME = '과장'; --2200000, 2500000, 3760000

--2) 위의 급여보다 높은 급여를 받는 직원들 조회(사번, 이름, 직급명, 급여)
SELECT EMP_ID, EMP_NAME, JOB_NAME, SALARY
FROM EMPLOYEE E, JOB J
WHERE E.JOB_CODE = J.JOB_CODE
AND SALARY > ANY(2200000, 2500000, 3760000)
AND JOB_NAME = '대리';

--3) 합치기 
SELECT EMP_ID, EMP_NAME, JOB_NAME, SALARY
FROM EMPLOYEE E, JOB J
WHERE E.JOB_CODE = J.JOB_CODE
AND SALARY > ANY(SELECT SALARY
                    FROM EMPLOYEE E, JOB J
                    WHERE E.JOB_CODE = J.JOB_CODE
                    AND JOB_NAME = '과장')
AND JOB_NAME = '대리';

-각 부서별로 최고 급여를 받는 사원의 이름, 직급코드, 급여 조회 

--1)부서별 최고급여
SELECT MAX(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE; -- 2890000, 3660000, 8000000, 3760000, 3900000, 2490000, 2550000

--2)사원의 이름, 직급코드, 급여 -> 위의 급여를 받는 사람들만 
SELECT EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY IN(2890000, 3660000, 8000000, 3760000, 3900000, 2490000, 2550000);

--3) 합치기 
SELECT EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY IN(SELECT MAX(SALARY)
               FROM EMPLOYEE
               GROUP BY DEPT_CODE);

-실습) 선동일 또는 유재식 사원과 같은 부서인 사원들을 조회(사원명, 부서코드, 급여)

--1) 
SELECT DEPT_CODE
FROM EMPLOYEE
WHERE EMP_NAME IN ('선동일', '유재식');

--2) 
SELECT EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE IN(SELECT DEPT_CODE
                    FROM EMPLOYEE
                    WHERE EMP_NAME IN ('선동일', '유재식'));

- 과장 직급임에도 불구하고 모든 차장 직급의 급여보다 많이 받는 사원 조회 (사번, 이름, 직급명, 급여)

--1) 
SELECT SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE JOB_NAME = '차장';

--2) 
SELECT EMP_ID, EMP_NAME, JOB_NAME, SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE SALARY > ALL(SELECT SALARY --ALL: 가장 높은 값하고만 비교 
                    FROM EMPLOYEE
                    JOIN JOB USING(JOB_CODE)
                    WHERE JOB_NAME = '차장')
    AND JOB_NAME = '과장';

3) 다중열 서브쿼리

: 조회 결과값은 한 행이지만, 나열된 컬럼수 가 여러 개일 때

 

- 하이유 사원과 같은 부서코드 AND 직급코드에 해당하는 사원들 조회 

--1)
SELECT DEPT_CODE, JOB_CODE
FROM EMPLOYEE
WHERE EMP_NAME = '하이유';
                    
                    
--2) 부서코드가 D5면서 직급코드가 J5인 사원 조회
--이름 , 부서코드, 직급코드 , 입사일
SELECT EMP_NAME, DEPT_CODE, JOB_CODE, HIRE_DATE
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5' AND JOB_CODE = 'J5'; 

--3) 합치기
SELECT EMP_NAME, DEPT_CODE, JOB_CODE, HIRE_DATE
FROM EMPLOYEE
WHERE DEPT_CODE = (SELECT DEPT_CODE
                                    FROM EMPLOYEE
                                    WHERE EMP_NAME = '하이유')
AND JOB_CODE = (SELECT JOB_CODE
                                    FROM EMPLOYEE
                                    WHERE EMP_NAME = '하이유');

--> 다중열 서브쿼리
SELECT EMP_NAME, DEPT_CODE, JOB_CODE, HIRE_DATE
FROM EMPLOYEE
WHERE (DEPT_CODE, JOB_CODE) = (SELECT DEPT_CODE, JOB_CODE
                                FROM EMPLOYEE
                                WHERE EMP_NAME = '하이유');

-실습) 박나라 사원과 같은 직급코드, 같은 사수번호를 가진 사원들의 사번, 이름, 직급코드 ,사수번호 조회

--1) 
SELECT JOB_CODE, MANAGER_ID
FROM EMPLOYEE
WHERE EMP_NAME = '박나라';

--2) 
SELECT EMP_ID, EMP_NAME, JOB_CODE, MANAGER_ID
FROM EMPLOYEE
WHERE (JOB_CODE, MANAGER_ID) = (SELECT JOB_CODE, MANAGER_ID
                                FROM EMPLOYEE
                                WHERE EMP_NAME = '박나라');

 4) 다중행 다중열 서브쿼리

: 서브쿼리 조회 결과값이 여러 행 여러 컬럼인 경우

 

-각 직급별 최소 급여를 받는 사원들을 조회(사번, 이름, 직급코드, 급여)

--1)각 직급별 최소 급여
SELECT JOB_CODE, MIN(SALARY)
FROM EMPLOYEE
GROUP BY JOB_CODE;
-- J2: 3700000, J7: 1380000, J3: 3400000, J6: 2000000, J5: 2200000, J1: 8000000, J4: 1550000

--2)위의 목록들 중에 일치하는 사원 //사번, 이름, 직급코드 ,급여 
SELECT EMP_NAME
FROM EMPLOYEE
WHERE JOB_CODE = 'J2' AND SALARY = 3700000 
    OR JOB_CODE = 'J7' AND SALARY = 1380000 
    OR JOB_CODE = 'J3' AND SALARY = 3400000 
    OR JOB_CODE = 'J6' AND SALARY = 2000000
    OR JOB_CODE = 'J5' AND SALARY = 2200000
    OR JOB_CODE = 'J1' AND SALARY = 8000000
    OR JOB_CODE = 'J4' AND SALARY = 1550000; 

SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE (JOB_CODE, SALARY) IN (SELECT JOB_CODE, MIN(SALARY)
                                FROM EMPLOYEE
                                GROUP BY JOB_CODE);

-실습) 각 부서별 최고 급여를 받는 사원들 조회(사번, 이름, 부서코드, 급여) 정렬까지(오름차순)

--1)
SELECT NVL(DEPT_CODE, '부서없음'), MAX(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE;

--2) 
SELECT EMP_ID, EMP_NAME, NVL(DEPT_CODE, '부서없음'), SALARY
FROM EMPLOYEE
WHERE (NVL(DEPT_CODE, '부서없음'),SALARY) IN(SELECT  NVL(DEPT_CODE, '부서없음'), MAX(SALARY)
                                            FROM EMPLOYEE
                                            GROUP BY DEPT_CODE)
ORDER BY DEPT_CODE;

- 실습) 보너스 포함 연봉이 3000만원 이상인 사원들의 사번, 이름, 보너스 포함연봉, 부서코드를 조회

SELECT EMP_ID, EMP_NAME, (SALARY+SALARY*NVL(BONUS,0))*12 "보너스포함연봉", DEPT_CODE
FROM EMPLOYEE
WHERE (SALARY+SALARY*NVL(BONUS,0))*12  >= 30000000;

--인라인 뷰를 사용해보면?
SELECT *
FROM (SELECT EMP_ID, EMP_NAME, (SALARY+SALARY*NVL(BONUS,0))*12 "보너스포함연봉", DEPT_CODE
        FROM EMPLOYEE)
WHERE "보너스포함연봉" >= 30000000;

5) 인라인 뷰(INLINE VIEW)

: FROM 절에 서브쿼리를 제시
    서브쿼리를 수행한 결과(RESULT SET)를 테이블 대신 사용함 

- 인라인뷰를 주로 사용하는 예
-TOP-N분석 : 데이터베이스 상에 존재하는 자료 중 최상위 몇 개 자료를 보기 위해 사용 

-전직원 중에서 급여가 가장 높은 5명 
-*ROWNUM : 오라클에서 제공해주는 컬럼, 조회된 순서대로 1부터 ...순번을 부여해줌

SELECT ROWNUM, EMP_NAME, SALARY --3
    FROM EMPLOYEE               --1
    WHERE ROWNUM <= 5           --2
    ORDER BY SALARY DESC;       --4
 
 --> ORDER BY로 정렬한 테이블을 가지고 ROWNUM 순번 부여 후에 ROYNUM <= 5
 SELECT ROWNUM, EMP_NAME, SALARY   
 FROM  (SELECT * 
         FROM EMPLOYEE
        ORDER BY SALARY DESC)
    WHERE ROWNUM <= 5;
--> 인라인 뷰 적용해 실행 

SELECT ROWNUM, E.* 
    FROM (SELECT * 
         FROM EMPLOYEE
        ORDER BY SALARY DESC) E
 WHERE ROWNUM <= 5;

-각 부서별 평균급여가 높은 3개의 부서코드, 평균 급여 조회

SELECT ROWNUM, DEPT_CODE, ROUND("평균급여")
FROM (SELECT DEPT_CODE, AVG(SALARY) "평균급여"
        FROM EMPLOYEE
        GROUP BY DEPT_CODE
        ORDER BY "평균급여" DESC)
WHERE ROWNUM <= 3;

--가장 최근에 입사한 사원 5명 조회(사원명, 급여, 입사일)
SELECT ROWNUM, E.*
FROM (SELECT EMP_NAME, SALARY, HIRE_DATE
        FROM EMPLOYEE
        ORDER BY HIRE_DATE DESC) E
WHERE ROWNUM <= 5;

6) 순위 매기는 함수 
    ** SELECT절에서만 사용 가능 
    
    - RANK() OVER(정렬기준)
    - DENSE_RANK() OVER(정렬기준)
    
    - RANK() OVER : 공동 1위가 2명이면 그 다음 순위를 3위로 한다. 
    - DENSE_RANK() : 공동 1위가 2명이래도 그 다음 순위는 2위로 한다.

 

-사원들의 급여가 높은 순서대로 순위를 매겨서 사원명, 급여, 순위 조회

SELECT EMP_NAME, SALARY, RANK() OVER(ORDER BY SALARY DESC) "순위"
FROM EMPLOYEE;

SELECT EMP_NAME, SALARY, DENSE_RANK() OVER(ORDER BY SALARY DESC) "순위"
FROM EMPLOYEE;

--5위까지만 조회한다면? => SELECT절에서만 사용할 수 있어서 불가능 
SELECT EMP_NAME, SALARY, RANK() OVER(ORDER BY SALARY DESC) "순위"
FROM EMPLOYEE
WHERE RANK() OVER(ORDER BY SALARY DESC) <= 5;

SELECT * 
FROM (SELECT EMP_NAME, SALARY, RANK() OVER(ORDER BY SALARY DESC) "순위"
        FROM EMPLOYEE)
WHERE 순위 <= 5;

2. DDL ( DATA DEFINITION LANGUAGE) : 데이터 정의 언어

- 오라클에서 제공하는 객체(OBJECT)를 
 새로 만들고(CREATE), 구조를 변경하고(ALTER), 구조자체를 삭제하는(DROP) 명령문
 즉, 구조자체를 정의하고 언어로 주로 DB관리자, 설계자가 사용함

* 오라클에서의 객체(구조): 테이블(TABLE), 뷰(VIEW), 시퀀스(SEQUENCE)
                                           인덱스(INDEX), 패키지(PACKAGE), 트리거(TRIGGER)
                                           프로지서(PROCEDUER), 함수(FUNTION)
                                           동의어(SYNONYM),  사용자(USER)

 

1) CREATE TABLE

(1) 테이블이란? 행(ROW), 열(COLUMN)로 구성되는 가장 기본적인 데이터베이스 객체
                    모든 데이터는 테이블을 통해서 저장됨(데이터를 보관하고자한다면 테이블을 만들어야 함)

(2) [표현법]
    CREATE TABLE 테이블명 (
        컬럼명 자료형,
        컬럼명 자료형, 
        컬럼명 자료형, 
        ...
    );

(3) 자료형

    -문자 (CHAR(크기) / VARCHAR2(크기): 크기는 BYTE단위 
                                        (숫자, 영문자, 특수문자 => 1글자당 1BYTE    
                                                        한글 => 1글자당 3BYTE)
                                                        
    CHAR(바이트수) : 최대 2000BYTE까지 지정가능
                    고정길이(아무리 적은 값으로 들어와도 공백으로 채워서 처음 할당된 크기 유지)
                    주로 들어올 값의 글자수가 정해져 있을 경우
                    예) 성별 : 남 / 여, M / F
    VARCHAR2(바이트수) : 최대 4000BYTE까지 지정가능
                        가변길이(적은 값이 들어오면 그 담긴 값에 맞춰서 크기가 줄어듬)
                        VAR는 '가변'을 의미, 2는 '2배'를 의미한다.
                                                        
    -숫자 (NUMBER): 정수 / 실수 상관없이 NUMBER
    
    -날짜 (DATE)

 

-> 회원들의 데이터(아이디, 비밀번호, 이름, 회원가입일)를 담기 위한 테이블

-> MEMBER 테이블 생성하기

CREATE TABLE MEMBER(
    MEMBER_ID VARCHAR2(20),
    MEMBER_PWD VARCHAR2(20),
    MEMBER_NAME VARCHAR(20),
    MEMBER_DATE DATE
);

SELECT *
FROM MEMBER;

(4) 컬럼에 주석달기 (컬럼에 대한 설명)

[표현법]
    COMMENT ON COLUMN 테이블명.컬럼명 IS '주석내용'

COMMENT ON COLUMN MEMBER.MEMBER_ID IS '회원아이디';
COMMENT ON COLUMN MEMBER.MEMBER_PWD IS '회원비밀번호';
COMMENT ON COLUMN MEMBER.MEMBER_NAME IS '회원이름';
COMMENT ON COLUMN MEMBER.MEMBER_DATE IS '회원가입일';

+ 데이터 딕셔너리 : 다양한 객체들의 정보를 저장하고 있는 시스템테이블

- USER_TABLE : 현재 이 계정이 가지고 있는 테이블들의 전반적인 구조를 확인할 수 있는 데이터 딕셔너리
SELECT * FROM USER_TABLES;
- USER_TAB_COLUMNS
SELECT * FROM USER_TAB_COLUMNS;

 

(5) 데이터를 추가할 수 있는 구문(INSERT : 한 행으로 추가, 값의 순서)

- INSERT INTO 테이블명 VALUES(첫 번째 컬럼의 값, 두 번째 컬럼의 값, 세 번째 컬럼의 값,...);

INSERT INTO MEMBER VALUES('user01', 'pass01', '홍길동', '2022-09-29');
INSERT INTO MEMBER VALUES('user02', 'pass02', '김길동',  SYSDATE);
INSERT INTO MEMBER VALUES('user03', 'pass02', '이길동', '2022-12-12');
INSERT INTO MEMBER VALUES(NULL, NULL, NULL, SYSDATE);
--NULL값으로 들어감
INSERT INTO MEMBER VALUES('user01', 'pass03', '김개똥', SYSDATE);
--중복아이디가 존재

-NULL값이나 중복된 아이디값은 유효하지 않은 값들이다. 

-유효한 데이터값을 유지하기 위해서는 제약조건을 걸어줘야 한다. 

 

(6) 제약조건 COMSTRAINTS

- 종류 : NOT NULL, UNIQUE, CHECK, PRIMARY KET, FOREIGN KEY

- 원하는 데이터 값만들 유지하기 위해서(보관하기 위해서) 특정 컬럼마다 설정하는 제약
    (데이터의 무결성 보장을 목적으로) -> 권한이 없는 사람이 위변조 하지 않도록
- 제약조건이 부여된 컬럼에 들어갈 데이터가 문제가 있는지 없는지 자동으로 검사

- 컬럼에 제약조건을 부여하는 방식 : 컬럼레벨 / 테이블레벨

 

① NOT NULL 제약조건

: 해당 컬럼에 반드시 값이 존재해야만 하는 경우 사용(NULL값이 절대 들어와서는 안되는 컬럼)
    삽입 / 수정 시 NULL값을 허용하지 않도록 제한 

단, NOT NULL 제약조건은 컬럼레벨 방식으로 밖에 사용할 수 없음

 

- NOT NULL 제약조건만 설정한 테이블 만들기

- 컬럼 레벨 방식 : 컬럼명 자료형 제약조건 => 제약조건을 부여하고자 하는 컬럼 뒤에 기술 

CREATE TABLE MEM_NOTNULL(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30)
);

SELECT * FROM MEM_NOTNULL; 

INSERT INTO MEM_NOTNULL 
VALUES (1, 'user01', 'pass01', '홍길동', '남', '010-1234-2222', 'hgd@gd.com');

INSERT INTO MEM_NOTNULL
VALUES (2, NULL, NULL, NULL, NULL, NULL, NULL); --NOTNULL 제약조건에 위배 오류 발생

INSERT INTO MEM_NOTNULL
VALUES(2, 'user02', 'pass02' , '김길동' , NULL, NULL, NULL);
--NOT NULL 제약조건이 부여되어있는 컬럼에는 반드시 값이 존재해야 함. 

INSERT INTO MEM_NOTNULL
VALUES(3, 'user01', 'pass03' , '길동' , '남', NULL, NULL);

② UNIQUE 제약조건

: 컬럼에 중복값을 제한하는 제약조건 
    삽입 / 수정 시 기존에 해당 컬럼값 중에 중복값이 있을 경우 추가 또는 수정이 되지 않게 제약

- 컬럼레벨 / 테이블레벨 방식 둘 다 사용 가능 

CREATE TABLE MEM_UNIQUE(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR(20) NOT NULL UNIQUE,--컬럼레벨
    MEM_PWD VARCHAR(20) NOT NULL, 
    MEM_NAME VARCHAR(20) NOT NULL, 
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30)
);

DROP TABLE MEM_UNIQUE;

CREATE TABLE MEM_UNIQUE(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL, 
    MEM_PWD VARCHAR2(20) NOT NULL, 
    MEM_NAME VARCHAR2(20) NOT NULL, 
    GENDER CHAR(3),
    PHONE VARCHAR2(15), 
    EMAIL VARCHAR2(30),
    UNIQUE(MEM_ID) --테이블레벨 방식
    );
    
    INSERT INTO MEM_UNIQUE VALUES (1, 'user01', 'pass01', '홍길동', '여', null, null);
    INSERT INTO MEM_UNIQUE VALUES (2, 'user02', 'pass02', '김길동', '남', null, null);
    INSERT INTO MEM_UNIQUE VALUES (3, 'user02', 'pass03', '김말똥', '여', null, null);
    --UNIQUE 제약조건에 위배되었으므로 INSERT에 실패
    --오류구문으로 제약조건명 알려줌
    --제약조건을 모르면 파악하기 어려움(ORA-0001: UNIQUE CONSTRAINT)
    --제약조건명을 지정해 줄 수 있음
    
    SELECT * FROM MEM_UNIQUE;

②-1. 제약조건 부여 시 제약조건 명도 지정하는 표현식
        >컬럼 레벨 방식
        CREATE TABLE 테이블명(
            컬럼명 자료형 CONSTRAINT 제약조건명 제약조건, 
            컬럼명 자료형
            ...
        );
        > 테이블 레벨 방식
        CREATE TABLE 테이블명(
            컬럼명 자료형,
            컬럼명 자료형, 
            CONSTRAINT 제약조건명 제약조건(컬럼명)

    CREATE TABLE MEM_CON_NN(
        MEM_NO NUMBER NOT NULL,
        MEM_ID VARCHAR2(20) NOT NULL,
        MEM_PWD VARCHAR2(20) NOT NULL,
        MEM_NAME VARCHAR(20) CONSTRAINT MEM_NAME_NN NOT NULL,
        GENDER CHAR(3),
        PHONE VARCHAR2(15),
        EMAIL VARCHAR2(30),
        CONSTRAINT MEM_ID_UQ UNIQUE(MEM_ID)
    );
    
    INSERT INTO MEM_CON_NN VALUES(1, 'user01', 'pass01', '홍길동', null, null, null);
    INSERT INTO MEM_CON_NN VALUES(1, 'user01', 'pass01', '김길동', null, null, null);
    
    SELECT * FROM MEM_CON_NN;
    
    INSERT INTO MEM_CON_NN VALUES(2, 'user02', 'pass02', '김길동', '가', null, null);

-> GENDER 컬럼은 '남' / '여' 값만 들어가게 하고 싶다면?

 

③ CHECK 제약조건

: 컬럼의 기록될 수 있는 값에 대한 조건을 설정해 둘 수 있다.

CHECK(조건식)

   CREATE TABLE MEM_CHECK (
        MEM_NO NUMBER NOT NULL,
        MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
        MEM_PWD VARCHAR2(20) NOT NULL,
        MEM_NAME VARCHAR2(20) NOT NULL,
        GENDER CHAR(3) CHECK(GENDER IN('남', '여')),
        PHONE VARCHAR2(15),
        EMAIL VARCHAR2(30),
        MEM_DATE DATE NOT NULL
    );
    
    INSERT INTO MEM_CHECK VALUES(1, 'user01', 'pass01', '홍길동', '남', null, null, SYSDATE);
    INSERT INTO MEM_CHECK VALUES(2, 'user02', 'pass02' '김길동' , '여' , null, null, '37/02/28');
    
    SELECT * FROM MEM_CHECK;

-> 회원가입일을 항상 sysdate값으로 받고 싶다면 테이블에서 지정 가능

③ - 1. 특정 컬럼에 들어올 값에 대한 기본값 설정 => 제약조건은 아님

    DROP TABLE MEM_CHECK;
    
    CREATE TABLE MEM_CHECK(
        MEM_NO NUMBER NOT NULL,
        MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
        MEM_PWD VARCHAR2(20) NOT NULL,
        MEM_NAME VARCHAR2(20) NOT NULL,
        GENDER CHAR(3) CHECK (GENDER IN('남', '여')),
        PHONE VARCHAR2(15),
        EMAIL VARCHAR2(15),
        MEM_DATE DATE DEFAULT SYSDATE NOT NULL --DEFAULT는 제약조건이 아니기 때문에 제약조건 앞에 써줘야 함.
    );

③ - 2. 특정 컬럼만 입력

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

    SELECT * FROM MEM_CHECK;
    --지정 안 된 컬럼에는 기본적으로 NULL값이 들어가지만
    --만일 DEFAULT값이 부여되어 있다면 NULL 값이 아닌 DEFAULT값이 들어가게 됨

④ PRIMARY KEY(기본키) 제약조건

:  테이블에서 각 행들의 정보를 유일하게 식별할 수 있는 컬럼에 부여하는 제약조건

        => 각 행들을 구분할 수 있는 식별자의 역할
            예) 사번, 학번, 주문번호, 예약번호, 회원번호
        => 중복되지 않고 값이 존재해야만 하는 컬럼에 PRIMARY KEY부여(UNIQUE + NOT NULL)
        한 테이블 당 한 개의 컬럼만 설정 가능 

    CREATE TABLE MEM_PRIMARYKEY(
        MEM_NO NUMBER CONSTRAINT MEM_PK PRIMARY KEY,
        MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
        MEM_PWD VARCHAR2(20) NOT NULL,
        MEM_NAME VARCHAR2(20) NOT NULL,
        GENDER CHAR(3) CHECK(GENDER IN('남','여')),
        PHONE VARCHAR2(15),
        EMAIL VARCHAR2(30)
            );
    INSERT INTO MEM_PRIMARYKEY VALUES(1, 'USER01', 'PASS01', '홍길동', '남', NULL, NULL);
    
    INSERT INTO MEM_PRIMARYKEY VALUES(NULL, 'USER02', 'PASS02', '홍길동', NULL, NULL, NULL);
    --기본키 컬럼에 NULL값을 넣을 수 없음
     INSERT INTO MEM_PRIMARYKEY VALUES(1, 'USER02', 'PASS02', '홍길동', NULL, NULL, NULL);
    --기본키 컬럼에 중복값을 넣을 수 없음 
    INSERT INTO MEM_PRIMARYKEY VALUES(2, 'USER02', 'PASS02', '김길동', NULL, NULL, NULL);
    
    SELECT * FROM MEM_PRIMARYKEY;
    
    CREATE TABLE MEM_PRIMARYKEY2(
        MEM_NO NUMBER PRIMARY KEY,
        MEM_ID VARCHAR2(20) PRIMARY KEY,
        MEM_PWD VARCHAR2(20) NOT NULL,
        MEM_NAME VARCHAR2(20) NOT NULL,
        GENDER CHAR(3) CHECK(GENDER IN('남','여')),
        PHONE VARCHAR2(15),
        EMAIL VARCHAR2(30)
    );
    -- PRIMARY키가 한 테이블에 두 개가 될 수 없다
    --"table can have only one primary key"