know701

know701

Github blog

Oracle sql_2

7 분 소요

Sub-query

  • SQL 문안의 또다른 SQL 문 을 가키며 main-query 에 종속적인 관계를 가짐
    • 구절의 순서를 바꾸거나, 계산된 값으로 다음 쿼리를 실행할 때 사용
    • 사용 시 주의사항
      1. 반드시 소괄호로 묶어 사용
      2. 연산자 우항에 작성할 것.
      3. ORDER BY 문법 지원이 안됨.
  • 서브쿼리 위치에 따른 명칭
    • SELECT 절에 있는 서브쿼리 : 스칼라 서브쿼리
    • FROM 절에 있는 서브쿼리 : 인라인 뷰
    • WHERE 절에 있는 서브쿼리 : 서브쿼리
  • 유형
    • 단일행 단일컬럼
    • 다중행 단일컬럼
    • 다중열(단일행/다중행)
    • 상관
    • 스칼라(scala - 단일값)
    • inline-view
  • 단일 행 서브쿼리: 서브쿼리의 결과가 1행 
    -- 1. 해당사원 급여, 급여가 a와 동일한 사원 조회
select emp_id, emp_name, salary 
    from employee
    where salary = (
        select salary from employee where emp_name = '윤은해')
        and emp_name != '윤은해';
  • 다중 행 서브쿼리: 서브쿼리의 결과가 여러행
    • in not in: 같은 값 같지않은값 찾음 (단일행 비교연산자 ‘=’과 비슷한용도로 사용)
    • EXIST : 서브쿼리 값이 있을경우 반환 
      -- 특정 인물와 같은 직급 사원 조회
select e.emp_name, j.job_name
from employee e join job j
using(job_code)
where job_code in (
    select job_code from employee
    where emp_name in ('차태연','박나라','이오리')
);
  • 다중 컬럼 서브쿼리 : 서브쿼리의 결과가 여러컬럼
    • 메인쿼리 WHERE 절과 서브쿼리에서 반환하는 컬럼의 수가 반드시 같아야 함.
    • 2개의 컬럼을 반환하는 서브쿼리 사용 시 WHERE 절에도 2개의 컬럼명 적고 괄호로 묶음 
      SELECT DEPT_CODE, EMP_NAME, SALARY, d.dept_title 
FROM EMPLOYEE E 
JOIN DEPARTMENT D 
  ON E.DEPT_CODE = D.DEPT_ID
WHERE (E.DEPT_CODE,E.SALARY) IN 
    (SELECT DEPT_CODE, MIN(SALARY) 
    FROM EMPLOYEE GROUP BY DEPT_CODE);
  • 상호연관 서브쿼리(상관)
    • 메인쿼리의 값을 서브쿼리가 사용하고, 서브쿼리의 값을 받아 메인쿼리가 계산하는 구조
    • 각행의 컬럽값이 sub-query 에 필요한경우 유용
    • 사용 시 main-query 테이블 별칭 필수
    • 잘못 사용될 경우 성능 저하의 원인이 될수있음.
  • 일반 서브쿼리와 의 차이점

  • 일반 sub-query 는 단독사용하지만, 상관 sub-query 는 main-query 로 부터 값을 전달받아 사용

    -- 직급별 평균급여보다 많은 사원을 조회
select emp_name, salary, job_code from employee E
    where salary > ( select avg(salary) 
    from employee where job_code = E.job_code);
    1. 메인쿼리에서 직급번호 읽은뒤 서브쿼리로 전달
    2. 서브쿼리는 메인쿼리에서 받은 직급번호로 평균 급여 계산
    3. 메인쿼리에서는 서브쿼리의 평균 급여보다 큰 급여의 직원 출력
  • 스칼라 : 단일값
    • SELECT 절에 오는 서브쿼리로 결과값으로 1행만 반환함. 
      -- 인턴사원중 최대/최소 급여 사원 포함
select 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)
      or (nvl(dept_code,'인턴'), salary) in 
  (select nvl(dept_code,'인턴'), min(salary)from employee group by dept_code)
  order by 2 asc;
  • INLINE VIEW
    • FROM 절에 오는 서브쿼리
    • 데이터를 조회하여 가상의 집합을 만들어 조인을 수행 또는 가상의 집합을 다시 조회 시 사용
    • INLINE VIEW 안에 또 다른 INLINE VIEW 올수있음 
      select emp_name,dept_code, gender
from (select E.*, decode(substr(emp_no,8,1),'1','남','3','남','여') gender
from employee E
)
where gender = '여';

JOIN

  • 여러 테이블에 흩어진 정보 중 사용자가 필요한 정보만 가져와 가상의 테이블처럼 만들어 결과를 보여주는것

  • 조인의 종류

    INNER JOIN(교집합)

    • 키 값이 있는 테이블의 컬럼 값을 비교 후 조건에 맞는 값을 가져오는 것
    • inner join 키워드는 생략 가능함.
    • 기준 컬럼의 값이 상대테이블에서 없는 경우, 기준컬럼의 값이 null 인 경우 result set에서 제외됨. 
      select * from employee E 
  inner join department D 
    on E.dept_code = D.dept_id order by 1;
    • EQUI JOIN (동등 조인)
      • EQUAL 연산자(=) 를 사용

    OUTER JOIN(합집합)

    • LEFT OUTER JOIN
      • 좌측 테이블의 모든 결과를 가져온 뒤 테이블의 데이터를 매칭하고, 매칭되는 데이터가 없는경우 NULL 을 반환 
        select *
from employee E left outer join department D
on E.dept_code = D.dept_id
order by 1;
    • RIGHT OUTER JOIN
      • 우측 테이블의 모든 결과를 가져온 뒤 테이블의 데이터를 매칭하고, 매칭되는 데이터가 없는경우 NULL 을 반환 
        select *
from employee E right outer join department D
on E.dept_code = D.dept_id
order by 1;
    • FULL OUTER JOIN
      • 양쪽 모두의 조건이 일치하지 않는 데이터까지 모두 결합해 출력 
        select *
from employee E full outer join department D
on E.dept_code = D.dept_id order by 1;

    SELF JOIN

    • 같은 테이블을 조인
    • 테이블에서 자기 자신을 조인시키는것
    select A.emp_id, A.emp_name, A.manager_id, B.emp_id, B.emp_name
  from employee A join employee B
  on A.manager_id = B.emp_id;

    CROSS JOIN(Cartesian Product)

    • 조인되는 두 테이블에서 곱집합을 반환(모든 경우의 수를 반환) 
      select * from employee E cross join department D order by 1;

집합연산자(SET OPERATOR)

  • 여러개의 질의결과(결과 집합)를 세로로 연결해서 하나의 가상테이블로 리턴
  • 사용 조건
    1. 컬럼수가 동일해야함.
    2. 동일 위치의 컬럼은 자료형이 같아야함.
    3. 컬럼명이 다른 경우 첫번째 컬럼명을 사용
    4. ORDER BY 절은 맨 마지막에 한번만 사용 가능
    • UNION
      • 두개의 SELECT 결과를 합침. 합친 결과에서 중복되는 행은 하나만 표시됨. 
        SELECT NAME,ID FROM MEMBER WHERE NAME LIKE('%길동%')
UNION
SELECT NAME,ID FROM MEMBER WHERE ID LIKE ('%EW%');
    • UNION ALL
      • 중복을 제거하지않고 출력 
        SELECT NAME,ID FROM MEMBER WHERE NAME LIKE ('_길동%')
UNION ALL
SELECT NAME,ID FROM MEMBER WHERE ID LIKE ('%EW%');
    • INTERSECT
      • 서로 중복되는 내용만 출력(교집합) 
        SELECT NAME,ID FROM MEMBER WHERE NAME LIKE ('_길동%')
INTERSECT
SELECT NAME,ID FROM MEMBER WHERE ID LIKE ('%EW%');
    • MINUS
      • 먼저 위치한 SELECT 문을 기준으로 다른 SELECT 문과 공통된 레코드만 제외한 항목을 추출(차집합) 
        SELECT NAME,ID FROM MEMBER WHERE NAME LIKE ('_길동%')
INTERSECT
SELECT NAME,ID FROM MEMBER WHERE ID LIKE ('%EW%');

VIEW / 참고사이트,참고사이트2

  • 물리적인 테이블에 근거한 논리적인 가상 테이블(여러 테이블에서 원하는값만 가져옴)
  • 실제 데이터는 물리적인 테이블에 담겨있지만 테이블처럼 사용 가능함.(실제데이터 없음)

  • 물리적인 테이블의 데이터가 변경되면 가상 테이블의 값도 자동으로 변경됨.

  • 뷰 생성 쿼리에 함수 사용 시 ALIAS 를 지정
    • CREATE VIEW 권한은 RESOURCE 롤에 포함되지 않으므로, 관리자로부터 권한부여가 필요함. ``` – VIEW 권한부여 GRANT CREATE VIEW TO kh;

    – 타사용자에게 view 권한 부여 grant select on view_emp to qwerty;

    - 뷰 생성

    CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW 뷰이름 [(column_aliases)] AS SELECT 문 [WITH READ ONLY] [WITH CHECK OPTION [CONSTRAINT 제약 조건명]]

      - [OR REPLACE] : 구문사용 시 DROP 없이 **수정** 가능
  - FORCE : 뷰 생성 시 쿼리문의 테이블,컬럼,함수등이 존재하지 않아도 생성 가능
  - NOFORCE : 뷰 생성 시 쿼리문의 테이블,컬럼,함수 등이 존재하지 않으면 생성 불가
  - COLUMN_ALIASES : SELECT 컬럼의 별칭을 미리 정의
  - WITH READ ONLY : SELECT 만 가능(수정,삭제,추가 불가)
  - WITH CHECK OPTION : WHERE 절의 조건에 해당하는 데이터만 저장,변경 가능

    – 뷰 생성 시 함수를 사용할 경우 컬럼 별칭 부여해야 함. CREATE OR REPLACE VIEW VIEW_EMPLOYEE AS SELECT E.EMP_NAME, D.DEPT_TITLE, AVG(E.SALARY) AS AVG_SAL FROM EMPLOYEE E, DEPARTMENT D WHERE E.DEPT_CODE = D.DEPT_ID GROUP BY E.EMP_NAME,D.DEPT_TITLE, SALARY; – 코멘트 추가 comment on column view_employee.dept_title is ‘설명’; – 조회 select * from user_views; – 삭제 DROP VIEW VIEW_EMPLOYEE

      
- 컬럼 별칭 선언

    CREATE OR REPLACE VIEW VIEW_EMPLOYEE ( 이름, TITLE, AVG ) AS SELECT E.EMP_NAME, D.DEPT_TITLE, AVG(E.SALARY) AS AVG_SAL FROM EMPLOYEE E, DEPARTMENT D WHERE E.DEPT_CODE = D.DEPT_ID GROUP BY E.EMP_NAME,D.DEPT_TITLE, SALARY; ```

    • FORCE 옵션 추가
      • 뷰 생성은 되지만 INVALID 상태로 뷰는 동작하지않음. 
        CREATE OR REPLACE FORCE VIEW NEW_VIEW
AS
  SELECT EMPNO, EMPNAME
  FROM EMP;
    • WITH CHECK OPTION 옵션
      • 해당 옵션 사용 시 INSERT,UPDATE, DELETE 사용 시 WHERE 절의 조건에 해당하는 데이터만 접근 가능
      • 제약 조건명 지정 가능함. 
        WHERE DEPT_CODE ='D5'
WITH CHECK OPTION [CONSTRAINT 제약 조건명]
    • DML(INSERT,UPDATE,DELECT) 시 주의
      • 단순 뷰의 경우 DML 제약 없음(NOT NULL 컬럼 주의)
      • 함수, UNION, GROUP BY 등 복합 뷰의 경우 DML 불가능함.(조인만 사용한 경우 제한적으로 가능)
      • 생성 및 삭제의 경우 타겟 뷰의 이름이 없을경우 생성, 있을경우 수정함. 
        -- 생성 및 수정
CREATE OR REPLACE VIEW [스키마.][뷰 NAME] AS SELECT 문;
-- 삭제
DROP VIEW [스키마][뷰 NAME]
--구조 확인
DESC [스키마][뷰 NAME];

시퀀스(Sequence)

  • 시퀀스란 자동으로 순차적으로 증가하는 순번을 반환하는 데이터베이스 객체
  • 보통 PK값에 중복값을 방지하기 위해 사용

    만약 게시판에 글이 하나 추가될때마다 글번호(PK) 가 생겨야 한다면 하나의 레코드를 넣는 로직을 어딘가에 넣어야하지만 시퀀스를 사용하면 자동으로 추가해주므로 로직이 불필요함.

  • 시퀀스 생성 
    CREATE SEQUENCE [시퀀스명]
INCREMENT BY [증감 숫자] -- 시퀀스 증가값(DEFAULT 1)
START WITH [시작숫자] -- 시퀀스 시작값
MAXVALUE [최대값] -- 시퀀스 최대값(생략시 무한대)
MINVALUE [최소값] -- 시퀀스 최소값(생략시 무한대)
CYCLE OR NOCYCLE --기본값 NOCYCLE, CYCLE 로 지정 시 최대값 도달했을때 다시 최소값부터 시작 / MAX/MIN VALUE 도달 시 오류발생
CACHE 숫자 | NOCACHE -- 메모리상에서 시퀀스값 관리, 기본값은 CACHE 20 번호 유실이 문제된다면 NOCACHE 할 것.
  • 시퀀스 조회 방법 
    SELECT * FROM USER_SEQUENCES; --전체 시퀀스 조회
SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME = '생성한시퀀스 명' -- 생성시퀀스 조회
-- 시퀀스 객체의 현재번호
SELECT SEQ_TB_USER_NO.CURRVAL FROM DUAL;
  • 시퀀스 수정방법 
    ALTER SEQUENCE [시퀀스명]
INCREMENT BY [증감 숫자]
MAXVALUE [최대값]
MINVALUE [최소값]
CYCLE OR NOCYCLE
  • 시퀀스 삭제 
    DROP SEQUENCE [시퀀스명];

INDEX(색인)

  • SQL 조회 구문등에 처리속도 향상을 위해서 테이블 컬럼에 대해 생성하는 객체
  • key-value 형식으로 생성, key에는 컬럼값, value 에는 행에 접근할 수 있는 주소값이 저장 
    -- emp_name 컬럼에 인덱스 추가
create index idx_employee_emp_name on employee(emp_name);

    • 검색 속도 향상, 시스템 부하 줄여짐.(table full scan 하지않고 index 먼저 검색 후 행을 조회)

    • 인덱스 저장공간 필요, 인덱스를 생성 및 갱신하는데도 부가적인 시간이 필요
    • 변경작업(insert, update , delete)이 많다면 실제데이터처리 + 인덱스 갱신 시간이 소요되서, 성능 저하유발 함.
  • 인덱스 생성시 컬럼 선택 시 고려사항
    • 선택도가 좋은 컬럼으로 생성(중복값이 적다는 의미)
    • 고유번호를 가지고있는(id,email,name)것들이 선택도가 좋고, 선택도가 나쁜건 성별, null 값이 많은것 등이 선택도가 좋지못함
    • pk, uq 제약조건이 걸린 컬럼은 자동으로 인덱스 생성함.
  • 인덱스 적용
    • WHERE 조건절에 자주 사용되는 컬럼은 인덱스 생성
    • JOIN 기준컬럼은 인덱스 생성
    • 한번 입력후에 데이터변경이 많지 않은경우
    • 데이터가 20만건 이상인 경우
  • optimizer 가 index 사용여부 결정하는데 다음경우는 인덱스 사용 안함
    • 인덱스 컬럼에 변형이 가해진 경우(substr() 로 변형)
    • null 비교
    • not 비교
    • 인덱스컬럼 자료형과 비교하고자 하는 값의 타입이 다른 경우 
      -- emp_id 가 문자열로 저장되어있는 상태에서 숫자형 200으로 검색 시 full scan
select * from employee where emp_id = 200;

카테고리:

업데이트:

참고

Q&A

최대 1 분 소요

개발중 궁금한 사항 모음 제네릭스 ArrayList<type> 사용 시

Spring MVC Controller

4 분 소요

스프링 MVC MVC를 이용한 Controller 의 특징 HttpServletRequest, HttpServletResopnse 를 거의 사용할 필요없이 기능 구현 다양한 타입의 파라미터 처리, 다양한 타입의 리턴 타입 사용 가능 G...

Spring MVC

1 분 소요

스프링 MVC 스프링은 원래 목적 자체가 웹 어플리케이션을 목적으로 나온 프레임워크가 아니기 때문에 달라지는 영역에 대해서는 완전히 분리하고 연동하는 방식으로 구현 되있음.