21.2.5(금) DB/DML(SELECT)_ SUBQUERY(2) DB/DDL(CREATE)(1)

민국·2021년 2월 5일
db
0

SUBQUERY(2)

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

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

[표현법]
WHERE (컬럼1, 컬럼2, 컬럼3...) IN (서브쿼리1, 서브쿼리2,....)

<예시>
-- 각 직급별 최소급여 받는 사원조회
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);
                    
-- 컬럼 안에 NULL 값이 존재할 경우

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
LEFT WHERE (DEPT_CODE, SALARY) IN (SELECT DEPT_CODE, MAX(SALARY)
                           FROM EMPLOYEE
                           GROUP BY DEPT_CODE);
        -- DEPT_CODE = 'D9' AND SALARY = 8000000
        -- OR DEPT_CODE = 'D6' AND SALARY = 390000
        -- ... OR DEPT_CODE = NULL AND SALARY = 289000
        
-- => 해당 부서에 NULL 일 경우 급여 값이 조회되지 않는 문제가 있음
-- IN 안에 서브쿼리문의 DEPT_CODE의 NULL 값을 NVL 함수로 바꾸어주어야만 조회가 되지만
-- 또 EMPLOYEE 테이블 안에 DEPT_CODE 안에 NULL 값이 존재하기 때문에 거기도 NVL 함수로 묶어주어야만 함

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

(5) 인라인뷰(INLINE-VIEW)

: FROM절에 서브쿼리를 제시하는 것
서브쿼리를 수행한 결과(RESULT SET)을 테이블 대신에 사용함!
[표현법]
SELECT ROWNUM "순번"
FROM (쿼리문)
WHERE ROWNUM <= 원하는 조회 숫자;

<예시>
<인라인 뷰 X>
-- 보너스 포함한 연봉이 3000만원 이상인 사원들의 사번 이름 보너스포함한 연봉 부서코드 조회
SELECT EMP_ID, EMP_NAME, ((SALARY+ SALARY * NVL(BONUS,0)) * 12) "보너스 포함 연봉", DEPT_CODE
FROM EMPLOYEE
WHERE ((SALARY+ SALARY * NVL(BONUS,0)) * 12) >= 30000000;

<인라인 뷰 O>
-- > 인라인 뷰 써보자 ( FROM절에 내가 만든 테이블값을 올려놓고 거기서 조회해보는 것 -> 인라인뷰 )
SELECT *
FROM ( SELECT EMP_ID, EMP_NAME, ((SALARY+ SALARY * NVL(BONUS,0)) * 12) "보너스포함연봉", DEPT_CODE
       FROM EMPLOYEE )
WHERE 보너스포함연봉 >= 30000000;

인라인뷰 사용예시

: *TOP-N 분석 (베스트상품, 검색어순위, 조회수 베스트....)

<예시>
<인라인뷰 필요예시>
-- 전 직원 중 급여가 가장 높은 상위 5명
-- * ROWNUM : 오라클 전용 컬럼, 조회된 순서대로 1부터 순번을 부여해주는 컬럼

SELECT ROWNUM, EMP_NAME, SALARY
FROM EMPLOYEE                    -- 1
WHERE ROWNUM <= 5                -- 2
ORDER BY SALARY DESC;

--> 정렬 시키고 나서 ROWNUM이 출력되야하므로 FROM절부터 테이블 세팅을 다시하는것이 좋음

<인라인뷰 적용>
SELECT ROWNUM "순번", E.* -- 테이블의 모든 값을 조회할때 저런식으로 FROM을 별칭 부여해주고 별칭.* 로 해주어야 함
FROM (SELECT *
      FROM EMPLOYEE
      ORDER BY SALARY DESC) E
WHERE ROWNUM <=5;

(6) 순위 매기는 함수

: ROWNUM이 오라클 전용 함수라면, 윈도우 함수가 존재함

  • RANK() OVER(정렬기준)
  • DENS_RANK() OVER(정렬기준)
    단, 위의 함수들은 오로지 SELECT 절에만 사용 가능함

<[표현법]>
(DENSE_)RANK() OVER(ORDER BY 절)

  • RANK() OVER(정렬기준)
    : 공동순위가 있을 경우 그 다음 순위는 숫자 순서 안따라감
    ( 3위가 공동순위 4명이면 뒤이은 순위는 7위)
<예시>
-- 급여가 높은 순대로 순위를 매겨서 사원명, 급여, 순위 조회
SELECT EMP_NAME, SALARY, RANK() OVER(ORDER BY SALARY DESC) "순위"
FROM EMPLOYEE;
--> 공동 19위 2명 그 뒤 순위 21위
  • DENS_RANK() OVER(정렬기준)
    : 공동순위가 있을경우 그 다음 순위는 숫자 순서에 따라 감
    ( 3위가 공동순위 4명이여도 뒤이은 순위는 4위)
<예시>
SELECT EMP_NAME, SALARY, DENSE_RANK() OVER(ORDER BY SALARY DESC) "순위"
FROM EMPLOYEE;
--> 공동 19위 2명 그 뒤 순위 20위
<인라인뷰 활용법>
SELECT *
FROM (SELECT EMP_NAME, SALARY, RANK() OVER(ORDER BY SALARY DESC) "순위"
      FROM EMPLOYEE)
WHERE 순위 <= 5;

※ TIP. 계정 생성방법

<계정 생성>

  • CREATE USER 계정명 IDENTIFIED BY 비밀번호;

<최소한의 권한 부여>

  • GRANT CONNECT, RESOURCE TO 계정명;
    • GRANT = 권한생성 <-> REVOKE = 권한삭제
    • CONNECT = 접속 권한
    • RESOURCE = 객체(생성,수정,삭제), 데이터(입력,수정,조회,삭제)권한

DDL(DATA DEFINITION LANGUAGE)

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

↑ 오라클에서의 객체(OBJECT)

1. CREATE TABLE

  • 테이블이란? 행(ROW)과 열(COLUMN)로 구성되는 가장 기본적인 데이터베이스 객체
    모든 데이터는 테이블을 통해서 저장됨 ( ==

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

(1) 자료형

  • 문자(CHAR(크기) / VARCHAR2(크기))
    ※ 1글자당 1BYTE = 숫자,영문자,특수문자 / 1글자당 3BYE = 한글

    • CHAR (고정길이 / 최대 2000BYTE) : 아무리 적은 값이 들어와도 처음 할당한 크기 유지

    • VARCHAR2 (가변길이 / 최대 4000BYTE) : 적은 값이 들어오면 그 담긴 값에 맞춰 크기가 줄어듬

  • 숫자(NUMBER)
    : 정수,음수, 소수 상관없이 전부 가능

  • 날짜(DATE)

(2) 회원가입 테이블 만들기

 <회원가입 테이블 만들기>
 CREATE TABLE MEMBER(
    MEMBER_ID VARCHAR2(20),
    MEMBER_PWD VARCHAR2(20),
    MEMBER_NAME VARCHAR2(20),
    MEMBER_DATE DATE
);

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

[표현법] COMMNET 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 '회원가입일';

(4) 데이터 딕셔너리

: 다양한 객체들의 정보를 저장하고 있는 시스템 테이블

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

  • SELECT * FROM USER_TAB_COLUMNS;
    USER_TAB_COLUMNS : 현재 이 계정이 가지고 있는 테이블들의 모든 컬럼의 정보를 조회할 수 있는 데이터 딕셔너리

(5) 데이터 추가하는 구문(INSERT)

[표현법] INSERT INTO 테이블명 VALUES(값, 값, 값, 값,....)

INSERT INTO MEMBER VALUES('user01', 'pass01', '홍길동', '2021-02-01');
INSERT INTO MEMBER VALUES('user02', 'pass02', '홍길똥', '21/2/2');
INSERT INTO MEMBER VALUES('user03', 'pass03', '홍기루똥', sysdate);

-- 하지만 들어와서는 안되는 값
INSERT INTO MEMBER VALUES(NULL, NULL, NULL, sysdate); -- NULL값
INSERT INTO MEMBER VALUES('user03', 'pass03', '홍똥', sysdate); -- 아이디 비번이 같은 값(중복값)

위와 같이 들어와서는 안되는 값이 있으므로 제약조건이 필요함

(6) 제약조건(CONSTRAINTS)

  • 원하는 데이터값만 유지하기 위해서(보관하기 위해서) 특정 컬럼마다 설정하는 제약(데이터 무결성 보장을 목적으로)
  • 제약조건이 부여된 컬럼에 들어올 데이터에 문제가 없는지 자동으로 검사할 목적

※ 종류 : NOT NULL, UNIQUE, CHECK, PRIMARY KEY, FOREIGN KEY

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

(1) 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)
);

(2) UNIQUE (중복 제약)

profile
민국
새싹개발자
이전 포스트

21.2.4(목) DB/DML(SELECT)_ JOIN(2), SUBQUERY(1)

다음 포스트

21.2.10(수) DB/DDL(CREATE)(3), DML, DDL(1)

0개의 댓글

관련 채용 정보