SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, SALARY FROM EMPLOYEES X--사원을 조회, 행이 107개가 존재 WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEES Y WHERE Y.JOB_ID = X.JOB_ID--여기에 직종아이디가 들어가야함 , 외부 행의 107를 사용하고 싶음 --JOB_ID 의 평균에 대해서 계산을 각기 진행하는 것, 각 행마다 실행되는 것이 중요함 );
원래는 TABLE만 가지고 쿼리가 실행된다. 그러나 상호연관의 서브쿼리는 외부의 테이블의 쿼리가 서브쿼리내에 사용되는 것을 뜻한다.
바깥쪽의 쿼리문의 처리 결과로 사용된다.
일반서브쿼리는 딱 1번만 실행된다.
많이 사용되는 곳
게시글의 댓글을 표시할 때 사용이된다.
그러나 게시글 테이블을 만들 때부터 댓글, 평점에 대한 칸을 추가해서 연산하여 반영시키는 것이 좋다.
게시글 테이블에 좋아요, COLUMN을 생성시켜서 INSERT시 변경될 수 있도록 변경하는 것이 중요하다.
상호연관쿼리는 쉽게 사용할 것이 아니다.
TOP-N분석
테이블의 행을 특정 컬럼의 값을 기준으로 정렬했을 때 지정된 순위에 포함되는 행을 조회하는 것
급여를 많이 받는 사람 3명, 급여를 제일 적게 받는 사람 3명을 조회할 때 사용한다.
조건에 맞는 최상위 레코드 n개 혹은 최하위 n개를 조회한다.
ROWNUM(의사 컬럼 : Pseudo column)
조회된 결과에 대해서 순번을 붙이는 가상의 컬럼
SELECT ROWNUM, EMPLOYEE_ID, FIRST_NAME, SALARY --ROWNUM으로 숫자가 부여됨을 확인, 숫자순위를 찾으면됨 FROM (SELECT EMPLOYEE_ID, FIRST_NAME, SALARY FROM EMPLOYEES ORDER BY SALARY DESC) --내림차순으로 인라인뷰로 정렬함, 분석대상컬럼의 정렬기준이 된다. WHERE ROWNUM <= 5;
ROWNUM은 가상의 테이블로 WHERE절에서 조회를 하는 것이 아니다.
순번이 매겨진 곳에서 가져오는 것이 아닌 가상의 테이블에서 순번이 IF문으로 매겨지면 5개까지만 가져오라는 WHERE절이다.
SELECT절에서 ROWNUM은 제외해도 된다. 단지 보여주기 위함으로 작성된 부분이다.
상위를 ROWNUM으로 작성하면 2위가 5그룹으로 중복이 된다면 나머지 3그룹이 누락될 수 있다.
이부분을 극복하기 위해서 RANK가 등장했다.
분석함수
테이블의 데이터를 특정 용도로 분석하여 결과를 반환하는 함수이다.
종류
순위함수 : RANK, DENSE_RANK, ROW NUMBER
집계함수 : SUM, MIN, MAX, AVG,COUNT
급여를 기준으로 내림차순으로 정렬한 다음 조회된 행에 순위 혹은 순번을 부여한다.
SELECT SALARY, RANK() OVER (ORDER BY SALARY DESC) "RANK", DENSE_RANK() OVER(ORDER BY SALARY DESC) "DENSC_RANK", ROW_NUMBER() OVER(ORDER BY SALARY DESC) "ROW_NUMBER" FROM EMPLOYEES;
RANC()
중간에 중복이 있으면 1,2,2,4 처럼 순위가 빠질 수 있다.
3등까지 출력시 3개가 출력된다.
DENSE_RANK()
중복이 있어도 순위가 빠지지 않는다.
1,2,2,3,4,5처럼 연속적인 출력이 된다. 3등까지 출력시 4개가 출력된다.
ROW_NUMBER()
중복과 순위가 상관없이 1부터 쭉 출력된다.
같은 값이 나오지는 않는다.
분석함수() OVER (PARITITION BY 컬럼명 ORDER BY 컬럼명 DESC ) "분석함수 별칭"
PARTITION BY를 사용하면 지정된 컬럼명내에서 급여를 기준으로 내림차순으로 정렬한 다음 다음 조회된 행에 순번 혹은 순위를 부여한다.
SELECT DEPARTMENT_ID, SALARY, RANK() OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) "RANK", DENSE_RANK() OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) "DENSC_RANK", ROW_NUMBER() OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) "ROW_NUMBER" FROM EMPLOYEES;
ROW_NUMBER()을 사용한다면, PAGING처리시에 이 함수를 많이 사용한다.
계층검색
계층형 쿼리를 이용해서 상/ 하 관계에 있는 데이터를 조회한다.
계층형 쿼리를 이용해서 트리구조의 형태로 결과를 조회할 수 있다.
일반적으로 조직도나 카테고리, 메뉴등의 정보는 트리구조가 가지고 있다.
특정 데이터의 하위데이터들을 조회한다.
상위 데이터에서 하위데이터로 검색할 경우
EMPLOYEE _ ID = MANAGER_ID
부모님의 기본키 = 자식키의 외래키
특정 데이터의 상위데이터들을 조회한다.
자식키의 외래키 = 부모키의 기본키를 입력한다.
SELECT LEVEL , EMPLOYEE_ID, FIRST_NAME FROM EMPLOYEES START WITH EMPLOYEE_ID = 205 CONNECT BY PRIOR MANAGER_ID = EMPLOYEE_ID;
205번 직원의 상위에 위치하고 있는 매니저를 조회하기
좌측에 있는 컬럼명이 주 식별자이다.
주 식별자의 데이터를 우측에있는 컬럼명의 데이터를 조회하는 것이다.
LEVEL
반환되는 행에서 최상위행을 의미한다.
부모행이 1, 자식행이 2,3,4..으로 값을 정한다.
상위로 올라갈 경우 자신이 1 부모의 수도 2,3,4로 올라간다.
START WITH
계층검색의 시작지점을 의미한다.
CONNECT BY PRIOR
부모행과 자식행과의 관계가 있다는 의미이다.
어떻게 연결할지 재정의하는 부분이다. 상위를 찾아간다.
하위로 한번에 가지 않고, 상위로 찾아가는 경우가 대다수이다.
브래드크럼
헨젤과 그레텔에서 길에 흔적을 남기기 위해 빵조각을 남긴 것을 유래한 것으로 UI의 기능을 이용하여 자신의 원래 자리로 돌아가는 것이다.
역순을 이용하여 상위로 돌아갈 수 있는 DATE를 표시할 수 이싿.
LEVEL과 CONNECT BY로 연속된 숫자/ 날짜를 생성할 수 있다.
START WITH없이 CONNECT BY와 LEVEL이 존재한다.
이 방법을 이용하여 중간에 빠진 데이터의 날짜를 생성하여, 연속된 그래프, 매출확인, 일자별 매출확인,연속된 부분을 체크할 때 사용한다. -->GOOGLE CHART를 이용하여 그래프를 이용하자
연속된 숫자를 출력하기 (달)
SELECT LPAD(LEVEL,2,'0') MONTH FROM DUAL CONNECT BY LEVEL <= 31;
특정날짜를 출력하기
SELECT TO_DATE('2021/10/01','YYYY/MM/DD') + LEVEL -1 FROM DUAL CONNECT BY LEVEL <= TO_DATE('2021/10/31') - TO_DATE('2021/10/01')+1;
직원 급여1000달러 단위로 구분해서 급여별 인원수를 조회하기
SELECT Y.SALARY, NVL(CNT, 0) NVL FROM (SELECT TRUNC(SALARY,-3) SALARY , COUNT(*) CNT FROM EMPLOYEES GROUP BY TRUNC(SALARY, -3)) X, --직원급여 구하는 공식 (SELECT LEVEL*1000 SALARY FROM DUAL --1000부터 24000까지나오는 공식을 LEVEL *1000으로 1을 1000부터로 만들었다. CONNECT BY LEVEL <= 24 ) Y WHERE X.SALARY(+) = Y.SALARY ORDER BY 1;
사용자의 생성 및 권한 관리
권한
특정 SQL을 실행할 수 있는 권리이다.
시스템권한
데이터베이스, 테이블, 시퀀스, SELECT등을 엑세스 할 수 있는 권한이다.
데이터베이스의 관리자만 시스템 권한을 부여/ 회수 할 수 있다.
대표적인 시스템권한
시스템 권한은 200가지가 넘는다.
SYSTEM계정 -> 다른 사용자 -> HR ->사용자 편집
CREATE SESSION
데이터베이스의 연결할 수 있는 권한이다.
CREATE TABLE
테이블을 생성할 수 있는 권한이다.
객체 권한
데이터 베이스 객체의 내용을 조작할 수 있는 권한
사용자가 소유하고 있는 데이터 베이스 객체를 다른 사용자들이 엑세스 하거나 조작할 수 있게하는 권한을 의미한다.
사용자는 시스템 권한과 객체 권한이 필요하다.
ROLE (롤)권한
사용자가 사용할 수 있는 시스템권한을 그룹화해놓은 것.
사용자가 부여할 수 있는 관련 권한을 하나로 묶어서 그룹화하고 이름을 부여한 것
롤을 사용하면 권한의 부여, 회수 , 유지관리가 쉬워진다.
시스템의 권한은 200여가지인 반면 롤권한은 20가지가 된다.
GRANT 시스템권한,시스템권한,....TO 사용자;
GRANT 롤,롤,롤, TO 사용자
일반 사용자에게 부여되는 필수적인 롤
CONNECT ROLE
오라클에 접속할 수 있는 세션의 생성 권한
데이터 베이스와 연결하는 세션을 생성할 수 있는 시스템권한을 포함하고 있는 롤
RESOURCE ROLE
DML/DDL/DCL의 사용권한
PL/SQL을 사용할 수 있는 권한
테이블, 뷰, 시퀀스, 인덱스를 생성하거나 조회할 수 있는 권한
가장 일반적인 권한들을 가지고 있다.
데이터베이스의 객체 (테이블, 뷰, 시퀀스, 인덱스 등 )의 생성/변경/삭제, PL/SQL을 사용할 수 있는 권한등을 포함하고 있는 롤, 데이터베이스 사용자에게 필수적으로 필요한 시스템권한이 포함되어 있는 롤이다.
일반 사용자를 생성하면 보통 connect,resource롤을 부여한다
일반적인 작업이 가능하기 때문
DBA ROLE
모든 시스템의 권한이 부여된 롤
데이터 베이스 관리자에게만 부여해야한다.
세미프로젝트시 새로운 객체를 만들어서 깨끗한 계정에서 사용하도록하면 좋다.
ROLE단위로 부여, 회수가 가능하다.
스키마
테이블, 뷰, 시퀀스 등의 여러객체의 집합이다.
사용자의 이름을 말하기도 한다.
데이터베이스 사용자가 스키마를 소유하고 있으며, 사용자의 이름과 동일한 이름을 갖는다.
사용자의 이름으로 관리되고 있다
사용자의 이름으로 구분하기 때문에 집합의 구분이다.
다른 사용자가 소유하고 있는 객체를 엑세스할 때는 반드시 스키마를 명시해야한다.
JAVA 클래스를 사용할 때처럼
SELECT * FROM HR.EMPLOYEES;
DB의 객체 구분이름 : 내가 만든 스키마 , 다른사람이 만든 스키마로 구분한다.
다른사용자가 소유하고 있는 객체로 스키마를 명시해야한다.
일반적으로 다른사용자의 DB객체를 엑세스권한을 부여해야 접근이 가능하다
소유자가 엑세스 허락으로 접근시 그 테이블의 소유자를 명시해야한다.
생략시 내 객체에서 찾기 때문에 다른 소유자의 이름을 꼭 명시해주어야한다.
스키마의 이름은
집합의 이름
사용자의 이름
DB생성하는 쿼리 자체
생성하는 SQL문
CREATE쿼리
TABLE의 구조
인덱스의 구조
시퀀스의 구조
을 일컬어서 의미한다.
신규 사용자 생성하기
오라클 12버젼의 사용자 생성방식을 사용하도록 설정함
ALTER SESSION SET "_ORACLE_SCRIPT" = TRUE;
CREATE USER 사용자명 IDENTIFIED BY 비밀번호; ---비밀번호는 대소문자를 구분한다.
동의어 (SYNONYN)
데이터 베이스 객체에 대한 별칭을 정의하는 것
다른 사용자의 데이터베이스 객체를 사용할 때 동의어를 정의해서 사용한다.
HR에서 DB객체명을 변경할 경우
동의어 설정 부분만 변경하면 된다.
다른사람의 TABLE로 어떤상황이 올지 모르기 때문에 동의어로 사용한 부분을 바꿀 필요없이 동의어 부분만 변경해주면 된다.
DB LINK접근시 소유주, 서버명,SYNONYM을 작성하여, DB객체 사용시에 편리하다.
동의어를 사용하는 목적
복잡한 데이터베이스 객체명을 짧은 이름으로 대체할 수 있어서, SQL이 간단해진다.
참조하는 객체에 대한 SQL을 동의어로 사용해서 작성했다면, 참조하는 데이터베이스 객체명이 변경되더라도 동의어만 변경하면 사용중인 SQL문을 수정할 필요가 없다.
동의어가 정의되어 있으면, 데이터베이스 객체에 대한 소유주, 서버명, 데이터베이스 객체의 실제 이름등을 모르더라도 사용할 수 있다.
