21.02.16 TIL (ORACLE SUB QUERY, JOIN, TABLE, DDL, DML, 무결성)

학습 내용

- SUB QUERY : QUERY 안의 QUERY

한개의 행(ROW, RECORD)에서 결과 값이 반환되는 QUERY

 

    SELECT  단일 ROW 단일 COLUMN 이어야 한다 (산출되는 데이터는 한개, 컬럼도 한개)
    FROM    다중 ROW 다중 COLUMN 가능
    WHERE   다중 ROW 다중 COLUMN 가능

 

-- SELECT

SELECT employee_id, first_name,
    (SELECT first_name
    FROM employees
    WHERE  employee_id = 100)
FROM employees;

-- FROM

SELECT employee_id, first_name
FROM (SELECT employee_id, first_name
      FROM employees
      WHERE department_ID = 100)
WHERE salary > 8000;

-- WHERE
-- 평균급여보다 많이 받는 사원
SELECT
    *
FROM employees
WHERE SALARY > (SELECT AVG(SALARY) 
                FROM employees);

 

- 특수 쿼리

 

- CASE (== SWITCH 문이랑 같다. )

  조건에 따라 데이터를 다른 값이나 컬럼으로 추출 할 수 있도록 도와준다.

  CASE~ WHEN~ THEN~ ELSE (END로 끝내야 한다.)

SELECT employee_id, first_name, phone_number,
    CASE SUBSTR(PHONE_NUMBER, 1, 3)
        WHEN '515' THEN '서울'
        WHEN '590' THEN '부산'
        WHEN '650' THEN '광주'
        ELSE '기타'
    END AS 지역
FROM employees;

-- WHEN 안쪽에 모두 작성도 가능 (위, 아래 결과는 같다.)

SELECT employee_id, first_name, phone_number,
    CASE 
        WHEN SUBSTR(PHONE_NUMBER, 1, 3) = '515' THEN '서울'
        WHEN SUBSTR(PHONE_NUMBER, 1, 3) = '590' THEN '부산'
        WHEN SUBSTR(PHONE_NUMBER, 1, 3) = '650' THEN '광주'
        ELSE '기타'
    END AS 지역
FROM employees;

 

- DECODE

CASE 문과 같은 이유로 사용되며 많이 쓰인다.

SELECT employee_id, first_name, phone_number,
    DECODE(SUBSTR(PHONE_NUMBER, 1, 3),
            '515', '서울',
            '590', '부산',
            '650', '광주',
            '기타') AS 지역
FROM employees;

 

- UNION (합집합)

SELECT job_id
FROM employees
WHERE job_id IN('AD_VP', 'FI_ACCOUNT')
UNION ALL
SELECT job_id
FROM JOBS
WHERE job_id IN('AD_VP', 'FI_ACCOUNT');

 

- INTERSECT (교집합)

SELECT employee_id  -- 매니저만
FROM employees
INTERSECT
SELECT manager_id
FROM employees;

 

- MINUS (차집합)

SELECT employee_id  -- 사원들만
FROM employees
MINUS
SELECT manager_id
FROM employees;

 

---

- 분석 함수

 

 순위 함수    

RANK()	1 2 3 3 5 6 (공동 3등이 2명이면 그 다음은 5등으로)
DENSE_RANK()	1 2 3 3 4 5
ROW_NUMBER()	1 2 3 4 5 6
ROWNUM

 사용 이유 : 순서를 번호로 설정하기 위해

 

 

- 나중에 게시판 10개 , 20개 잘라올때 사용하는 구문 형식이다.

SELECT RNUM, employee_id, first_name, salary    -- 3. 범위 지정
FROM
    (SELECT ROWNUM AS RNUM, employee_id, first_name, salary -- 2. 번호 설정
    FROM
        (SELECT employee_id, first_name, salary -- 1. 급여의 순위 정렬
        FROM employees
        ORDER BY salary DESC)
    )
WHERE RNUM > 10 AND RNUM <= 20;

 

---

 

 - JOIN

    두개 이상의 테이블을 연결해서 데이터 검색하는 방법이다.
    보통 두 개이상의 행(ROW)들의 공통된 값 기본키, 외래키를 사용해서 Join한다.
    
    기본 키(Primary Key) : 테이블에서 중복이 되지 않는 키 
    외래 키(Foreign Key) : 다른 테이블에서 PK, UK인 경우가 많다.

 

    JOIN 종류                    (중요도)

   
    inner Join      = 교집합    *****
    full outer Join = 합집합
    cross Join
    outer Join
        Left                          ***  
        Right                        ***
     self Join                       *****

 

 

- INNER JOIN (내부조인)

SELECT employee_id, first_name,
    e.department_id, d.department_id,
    department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

 

- CROSS JOIN : 하나하나 조인을 다 시킨다. 양이 방대해짐. (거의 쓰지 않음)

SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name
FROM employees E, departments D;

 

 

- FULL OUTER JOIN : 전체를 출력(합집합)

 

 ANSI 쿼리

SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name
FROM employees E FULL OUTER JOIN departments D
    ON E.DEPARTMENT_ID = d.department_id;

 ORACLE에는 문법이 따로없다. 그래서 LEFT OUTER JOIN + RIGHT OUTER JOIN 방법을 사용하게 된다.

SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name
FROM employees E, departments D
WHERE E.department_id = D.department_id(+)  -- LEFT OUTER JOIN

UNION   -- 공유

SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name
FROM employees E, departments D
WHERE E.department_id(+) = D.department_id; -- RIGHT OUTER JOIN

 

 

- OUTER JOIN (외부조인)

 

LEFT : 왼쪽에 있는 테이블의 모든 결과를 오른쪽 테이블과 매칭하고, 매칭되는 데이터가 없는 경우 NULL로 표시한다.

 (쉽게 말해, 오른쪽 테이블 값이 NULL이어도 왼쪽 테이블 값의 모든 데이터를 출력한다.)

( LEFT 면 등호의 오른쪽에 (+) )

SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name
FROM employees E, departments D
WHERE E.department_id = D.department_id(+);

 RIGHT는 (+) 를 왼쪽으로!

 

- 차집합

OUTER JOIN을 이용한 방법으로,

집합 A(왼쪽 테이블) - B(오른쪽 테이블)을

LEFT OUTER JOIN + IS NULL로 구할 수 있다.

SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name
FROM employees E, departments D
WHERE E.department_id = D.department_id(+)
    AND D.department_id IS NULL;    -- NULL값이라는건 결국 교집합에 성립되지 않은 것이므로 차집합을 구하게 된다.

 

- SELF JOIN : 동일한 테이블에서 JOIN

SELECT a.employee_id, a.first_name,
    a.manager_id, b.employee_id,
    b.first_name
FROM employees A, employees B   -- A:사원 B:상사 (같은 테이블이지만 다른 테이블이라 생각하고 조인)
WHERE a.manager_id = b.employee_id;

 

- 계층형 구조 오름, 내림

CONNECT BY 문에 등호기준 PRIOR의 위치에 따라 상향식, 하향식이 결정된다.

(이 부분은 연관된 문법 START WITH~ , LEVEL 등이 있으니 더 공부할 것)

SELECT a.employee_id, a.first_name AS "사원",
    a.manager_id AS "사원의 상사" , b.employee_id,
    b.first_name AS "상사"
FROM employees A, employees B
WHERE a.manager_id = b.employee_id(+) -- LEFT
--CONNECT BY PRIOR  A.manager_id = A.employee_id; -- 상향식
CONNECT BY A.manager_id = PRIOR A.employee_id; -- 하향식

 

---

 

- 테이블 스페이스 생성 

오라클 프로그램을 통해 쿼리문 없이도 쉽게 생성 가능하지만, 기록성을 위해 쿼리문을 더 많이 사용한다고 한다.

CREATE TABLESPACE TABLESPACE2
DATAFILE 'D:\TMP\TEST_TEST1.DBF' SIZE 10M
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED     -- 추가되는 용량
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

 

DDL(Data Definition Language) : 데이터 정의 언어

 테이블이나 관계의 구조를 생성

 CREATE, ALTER, DROP

 

- 이름 변경

ALTER TABLESPACE TABLESPACE2
RENAME TO TEST_TBS_NEW;

 

- 용량 변경

ALTER DATABASE
DATAFILE 'D:\tmp\TEST_TEST1.DBF' RESIZE 7M;

 

- 테이블 생성

CREATE TABLE TB_TEST01(
    COL1 VARCHAR2(10),
    COL2 VARCHAR2(10),
    COL3 VARCHAR2(10)
);

 

-* 테이블 복제 : 데이터 포함

CREATE TABLE TB_TEST03
AS
SELECT *
FROM JOBS;

 

-* 테이블 복제 : 데이터 미포함

CREATE TABLE TB_TEST04
AS
SELECT *
FROM JOBS
WHERE 1=2;

 

- 테이블 복제 : N개의 컬럼만

CREATE TABLE TB_TEST05("업무번호", "업무명")
AS
SELECT JOB_ID, JOB_TITLE
FROM JOBS;

 

- 테이블명 수정

ALTER TABLE TB_TEST04
RENAME
TO
TB_TEST99;

 

- 컬럼 1개 추가

ALTER TABLE TB_TEST99
ADD
COL_NEW1 VARCHAR2(30);

 

- 컬럼 여러개 추가

ALTER TABLE TB_TEST99
ADD
(COL_NEW2 NUMBER, COL_NEW3 DATE);

 

- 컬럼 1개 수정

ALTER TABLE TB_TEST99
MODIFY
COL_NEW1 VARCHAR2(20);

 

- 컬럼 여러개 수정

ALTER TABLE TB_TEST99
MODIFY
(COL_NEW2 VARCHAR2(10), COL_NEW3 NUMBER);

 

- 컬럼 삭제

ALTER TABLE TB_TEST99
DROP
COLUMN COL_NEW1;

 

- 다중 컬럼 삭제

ALTER TABLE TB_TEST99
DROP
(COL_NEW2, COL_NEW3);

 

- 컬럼명 수정

ALTER TABLE TB_TEST99
RENAME
COLUMN
JOB_ID TO JOBNUM;

 

- 테이블 삭제

(CASCADE CONSTRAINTS 도 알고 있자.)

DROP TABLE TB_TEST99;


DROP TABLE TB_TEST
CASCADE CONSTRAINTS;    -- 무결성까지 완전히 지워진다.

 

- (참고) 휴지통 비우기

PURGE RECYCLEBIN;

 

 

DML (Data Manipulation Language) : 데이터 조작 언어

테이블의 데이터 검색, 삽입, 수정, 삭제

SELECT, UPDATE, DELETE, INSERT

 

SELECT (데이터 검색)

 

(SELECT 문법은 너무 많이 써놓아서 생략)

 

- INSERT (데이터 삽입)

컬럼의 개수, 자료형만 신경쓰면 어렵지 않다.

INSERT INTO TB_TEST(DEPTNO, DEPTNAME, MGRID, LOCID)
VALUES(10, '기획부', 100, 120);

INSERT INTO TB_TEST(DEPTNO, DEPTNAME)
VALUES(20, '관리부');

INSERT INTO TB_TEST -- 컬럼이름을 적어주지 않을때는 자동으로 맞춰서 들어간다.
VALUES(30, '전산부', 300, 230);    --하지만 반드시 컬럼의 개수에 맞춰서 적어줘야 삽입이 가능하다.

INSERT INTO TB_TEST(DEPTNAME, DEPTNO, LOCID, MGRID) -- 순서가 바뀌어도 삽입 가능
VALUES('경리부', 40, 210, 150);

INSERT INTO TB_TEST(DEPTNO, DEPTNAME, MGRID, LOCID)
VALUES('50', '개발부', 250, 110); -- 숫자가 문자열로 들어가도 삽입 가능

 

- DELETE (데이터 삭제)

DELETE FROM TB_TEST
WHERE DEPTNAME = '전산부';

DELETE FROM TB_TEST
WHERE MGRID IS NULL;

 

- UPDATE (데이터 수정)

UPDATE TB_TEST
SET MGRID = 120
WHERE DEPTNO = 40;

UPDATE TB_TEST
SET MGRID = 300, LOCID = 270
WHERE DEPTNAME = '개발부';

 

---

 

  무결성(Integrity)

 

    DB에 저장된 값들이 정확하고 일관성 있는 데이터임을 나타내는 의미.

    DB는 무결성이 보장되도록 여러 개체에 규정을 부여하여 운영한다.

 

  제약 조건(Constraint) : COLUMN을 지정하는 성질.

    1. Primary key (PK) : 기본 키. NULL값 X.    중복값 X.   ex) ID, 주민번호
    2. Unique key  : 고유 키. NULL값 허용.  중복값 X.   ex) 이메일주소
    3. Foreign key (FK) : 외래 키. NULL값 허용.  테이블과 테이블을 연결(JOIN)하는 목적.
                    (외래 키로 설정된 컬럼은 연결된 테이블에서 PK나 UK로 설정되어 있어야 한다.)
    4. CHECK       : 범위를 설정. NULL값 허용. 지정된 값 외에 사용할 수 없다.
    5. NOT NULL    : NULL값 X.

 

 

- Primary key = Unique + NOT NULL

CREATE TABLE TB_TEST01(
    PKCOL VARCHAR2(10) CONSTRAINT PK_TEST_01 PRIMARY KEY, -- PKCOL 컬럼을 PK로 지정
    COL1 VARCHAR2(20),
    COL2 VARCHAR2(20)
);

INSERT INTO TB_TEST01(PKCOL, COL1, COL2)
VALUES ('AAA', 'aaa', '111');

INSERT INTO TB_TEST01(PKCOL, COL1, COL2)
VALUES ('BBB','', '');

INSERT INTO TB_TEST01(PKCOL, COL1, COL2) -- PKCOL값 'BBB'가 중복되므로 ERROR
VALUES ('BBB','bbb', '222');

INSERT INTO TB_TEST01(PKCOL, COL1, COL2) -- PKCOL값이 NULL 이므로 ERROR
VALUES ('','bbb', '222');

 

한 테이블에 PK 2개 이상 만들기

CREATE TABLE TB_TEST01(     -- 한 테이블에 PK가 1개 있어야 한다는 ERROR가 뜬다.
    PKCOL VARCHAR2(10) CONSTRAINT PK_TEST_01 PRIMARY KEY,
    COL1 VARCHAR2(20) CONSTRAINT PK_TEST_02 PRIMARY KEY,
    COL2 VARCHAR2(20)
);


CREATE TABLE TB_TEST01(     -- 한 테이블에 PK 2개 이상 만드는 방법
    PKCOL VARCHAR2(10),
    COL1 VARCHAR2(20),
    COL2 VARCHAR2(20),
    CONSTRAINT PR_TEST_01 PRIMARY KEY(PKCOL, COL1)
);

 

- Unique : 고유 키. NULL값 허용.  중복값 X.

CREATE TABLE TB_TEST01(
    UKCOL VARCHAR2(10) CONSTRAINT UK_TEST_01 UNIQUE,
    COL1 VARCHAR2(20),
    COL2 VARCHAR2(30)
);

INSERT INTO TB_TEST01(ukcol, COL1, COL2)
VALUES('AAA','aaa','111');

INSERT INTO TB_TEST01(ukcol, COL1, COL2)  -- NULL값 허용
VALUES('','aaa','111');

-- UNIQUE KEY를 삭제해야 할 경우
ALTER TABLE TB_TEST01
DROP CONSTRAINT UK_TEST_01;

INSERT INTO TB_TEST01(ukcol, COL1, COL2)    -- UNIQUE KEY가 삭제되어 중복값 삽입이 가능해진다.
VALUES('AAA','aaa','111');

 

 

- FOREIGN KEY : 외래 키

CREATE TABLE TB_DEPT(	-- PK가 있는 테이블 생성
    DEPARTMENT_ID VARCHAR2(10),
    DEPARTMENT_NAME VARCHAR2(20),
    LOCATION_ID NUMBER,
    CONSTRAINT PK_DEPT_TEST PRIMARY KEY(DEPARTMENT_ID)
);


CREATE TABLE TB_EMP(	-- FK가 있는 테이블 생성
    EMPNO VARCHAR2(10),
    ENAME VARCHAR2(20),
    DEPARTMENT_ID VARCHAR2(10),    -- PK와 자료형, 용량 크기도 똑같이 맞춰줘야 한다.
    CONSTRAINT FK_EMP_TEST FOREIGN KEY(DEPARTMENT_ID) REFERENCES TB_DEPT(DEPARTMENT_ID)
);

INSERT INTO TB_DEPT(DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
VALUES ('10', '기획부', '120');

INSERT INTO TB_DEPT(DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
VALUES ('20', '관리부', '150');

INSERT INTO TB_DEPT(DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
VALUES ('30', '개발부', '180');



INSERT INTO TB_EMP(EMPNO, ENAME, DEPARTMENT_ID)
VALUES (100, '홍길동', '10');

INSERT INTO TB_EMP(EMPNO, ENAME, DEPARTMENT_ID)
VALUES (110, '성춘향', '20');

INSERT INTO TB_EMP(EMPNO, ENAME, DEPARTMENT_ID) -- '40'이 FK에 없으므로 ERROR
VALUES (120, '일지매', '40');

INSERT INTO TB_EMP(EMPNO, ENAME, DEPARTMENT_ID)
VALUES (120, '일지매', '30');

INSERT INTO TB_EMP(EMPNO, ENAME, DEPARTMENT_ID) -- NULL값 허용
VALUES (130, '정수동', '');

 

- CHECK : 지정된 값만 넣을 수 있다. NULL, 중복 허용

CREATE TABLE TB_CHECK(
    COL1 VARCHAR2(10),
    COL2 VARCHAR2(20),
    CONSTRAINT CHK_01 CHECK(COL1 IN ('사과', '배', '바나나')), -- 컬럼에 들어갈 수 있는 값의 범위를 정해준다.
    CONSTRAINT CHK_02 CHECK(COL2 > 0 AND COL2 <= 100)
);

INSERT INTO TB_CHECK(COL1, COL2)
VALUES('사과', 50);

INSERT INTO TB_CHECK(COL1, COL2) -- ERROR
VALUES('귤', 50);

INSERT INTO TB_CHECK(COL1, COL2) -- NULL 값 가능
VALUES('', 50);

INSERT INTO TB_CHECK(COL1, COL2) -- ERROR
VALUES('바나나', 0);

INSERT INTO TB_CHECK(COL1, COL2) -- NULL 값 가능
VALUES('바나나', '');

 

- NOT NULL

CREATE TABLE TB_TEST01(
    COL1 VARCHAR2(10) NOT NULL, -- COL1 은 NULL값을 받을 수 없게 된다.
    COL2 VARCHAR2(20)
);

INSERT INTO TB_TEST01(COL1, COL2)
VALUES ('AAA', '111');

INSERT INTO tb_test01(COL1, COL2) -- 삽입 가능
VALUES ('BBB', '');

INSERT INTO tb_test01(COL1, COL2) -- CO1은 NOT NULL이므로 ERROR
VALUES ('', '222');

INSERT INTO tb_test01(COL1) -- 삽입 가능
VALUES ('333');