2021. 05. 06(목) TIL

Dylan·2021년 5월 6일

TIL db

Database

다중행함수(그룹함수)

조회된 행들의 집합그룹에 적용되어 그룹당 하나의 결과를 생성하는 함수다.
- 집합그룹이란
  - 테이블 전체 또는 그룹화된 테이블의 행들을 말한다.
  - 집합그룹의 예
    - 사원테이블전체 혹은 부서별 평균임금
    - 직종별 전체 급여
    - 관리자별 관리직원수
    - 입사년도별 입사한 사원수
    - 급여등급별 사원수
- 사용시 주의사항
  - 그룹함수는 where절에서 사용할 수 없다.
  - 그룹함수와 그룹함수가 아닌 표현식을 select절에 같이 적을 수 없다
  - 그룹함수의 중첩은 한번만 허용된다.
  - group by 절에 등장한 표현식은 그룹함수와 같이 select절에 적을 수 있다.

그룹함수의 종류

count(*)
- 조회된 모든 행의 갯수를 반환한다.
count(컬럼 혹은 표현식)
- 조회된 행에서 지정된 컬럼의 값이 null이 아닌 행의 갯수를 반환한다.
sum(컬럼 혹은 표현식)
- 조회된 행에서 지정된 컬럼의 값의 합계를 반환한다.(null값은 무시된다.)
avg(컬럼 혹은 표현식)
- 조회된 행에서 지정된 컬럼의 값의 평균을 반환한다.(null값은 무시된다.)
min(컬럼 혹은 표현식)
- 조회된 행에서 지정된 컬럼의 값의 최소값을 반환한다.(null값은 무시된다.)
max(컬럼 혹은 표현식)
- 조회된 행에서 지정된 컬럼의 값의 최대값을 반환한다.(null값은 무시된다.)
variance(컬럼 혹은 표현식)
- 조회된 행에서 지정된 컬럼의 값의 분산을 반환한다.(null값은 무시된다.)
stddev(컬럼 혹은 표현식)
- 조회된 행에서 지정된 컬럼의 값의 표준편차를 반환한다.(null값은 무시된다.)

테이블의 행을 그룹화하기

group by 절을 사용해서 지정된 컬럼의 값이 같은 값을 가지는 행끼리 그룹화할 수 있다.
group by절의 작성 규칙

  select 컬럼혹은표현식, 그룹함수(), 그룹함수()
  from 테이블명
  [where 조건식]
  [group by 컬럼혹은표현식]
  [order by 컬럼명]

group by절은 행 그룹화 기준을 결정하는 값을 가진 컬럼을 지정한다.
select 절에서 group by절에 지정한 컬럼은 그룹함수와 같이 사용할 수있다.
select 절에서 사용된 그룹함수는 group by로 그룹화된 각각의 그룹마다 실행된다.

그룹함수 실행결과를 필터링하기

having절은 group by 절을 사용해서 행을 그룹화하고, 각 그룹에 그룹함수를 실행한 결과를 필터링할 때 사용한다.
- 일반적으로 행을 필터링할 때는 where절을 사용한다.
where과 having
- where
  - 행을 필터링한다.
  - 그룹함수를 조건식에 사용할 수 없다.
- having
  - 그룹화된 그룹에 그룹함수 적용한 후에 계산된 결과로 필터링을 한다.
  - 그룹함수를 조건식에 사용할 수 있다.
having절의 작성규칙

  select column, 그룹함수
  from table
  [where  조건식]
  [group by 컬럼혹은표현식]
  [having 그룹함수적용결과를 필터링하는 조건식]
  [order by 컬럼]


-- 다중행 함수
-- employees 테이블에 등록된 모든 사원들의 수를 조회하기
select count(*)
from employees;

-- 60번 부서에 소속된 사원들의 수 조회하기
select count(*)
from employees
where department_id = 60;

-- 커미션을 받는 사원들의 수를 조회하기
select count(*)
from employees
where commission_pct is not null;

select count(commission_pct)
from employees;

-- 60번 부서에 소속된 사원들이 받는 급여의 총합, 최저급여, 최고급여, 평균급여를 조회하기
select sum(salary) 급여총합, min(salary) 최저급여, max(salary) 최고급여, avg(salary) 평균급여
from employees
where department_id = 60;

-- group by를 사용해서 테이블의 행들을 그룹화하기
-- 같은 부서에 소속된 사원들끼리 그룹화한 다음, 그룹함수를 적용해보기
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;

-- 부서별로 사원들을 그룹화한 다음, 부서별 급여총합, 급여평균을 조회하기
SELECT department_id, SUM(salary), ROUND(AVG(salary))
FROM employees
GROUP BY department_id;

-- 부서별로 사원들을 그룹화한 다음, 부서별 급여총합, 급여평균을 조회하기
-- 부서이름, 부서별 급여총합, 부서별 급여평균을 조회함
SELECT D.department_name, SUM(salary), ROUND(AVG(salary))
FROM employees E, departments D
WHERE E.department_id = D.department_id
GROUP BY D.department_name;

-- 부서별로 사원들을 그룹화하고, 같은 부서에 소속된 사원들을 직종으로 다시 그룹화 한 다음
-- 해당 그룹에 속하는 사원들의 숫자를 조회하기
SELECT department_id, job_id, COUNT(*)
FROM employees
GROUP BY department_id, job_id
ORDER BY 1, 2;

-- 급여별로 사원들을 그룹화 했을 때, 각 급여별 사원수를 조회하기
SELECT TRUNC(salary, -3) salary, COUNT(*)
FROM employees
GROUP BY TRUNC(salary, -3)
ORDER BY salary;

-- 급여별 사원수를 조회하기
SELECT (TRUNC(salary/5000) + 1) * 5000 || '미만' sal, COUNT(*)
FROM employees
GROUP BY TRUNC(salary/5000)
ORDER BY TRUNC(salary/5000);

-- 부서별 사원수를 조회했을 떄 사원수가 5미만인 부서의 아이디와 사원수를 조회하기
-- groupby가 있을때 having은 없어도되지만 having만 존재해서는 안된다.
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id
HAVING COUNT(*) < 5;

-- 2007년에 입사한 사원들의 수를 직종아이디로 기준잡아 집계했을 때
-- 3명 이상 채용된 직종의 아이디와 사원수를 조회하기
SELECT job_id, COUNT(*)
FROM employees
WHERE hire_date >= to_date('2007/01/01') and hire_date < to_date('2008/01/01')
GROUP BY job_id
HAVING COUNT(*) >= 3
ORDER BY job_id;

-- 부서별 사원수를 집계했을 떄 사원수가 10명 이상인 부서의 아이디, 사원수를 조회하기
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id
HAVING COUNT(*) >= 10;

-- with절을 사용해서 쿼리의 실행속도를 개선 및 쿼리의 가독성을 높이기
WITH employees_count
AS (
    SELECT department_id dept_id, count(*) cnt
    FROM employees
    GROUP BY department_id
)
SELECT A.dept_id, B.department_name, A.cnt
FROM employees_count A, departments B
WHERE A.dept_id = B.department_id
AND A.cnt >= 5
ORDER BY A.dept_id;

package demo2.app;

import demo2.dao.UserDao;
import demo2.vo.User;

public class SimpleUserApp {
	
	public static void main(String[] args) {
		
		UserDao userDao = new UserDao();
		
//		// 새로운 사용자정보 등록하기
//		User user = new User();
//		user.setId("hong");
//		user.setPassword("zxcv1234");
//		user.setName("홍길동");
//		user.setEmail("hong@naver.com");
//		user.setPhone("010-1234-5678");
//		
//		
//		userDao.insertUser(user);
//		System.out.println("새로운 사용자 정보가 등록되었습니다.");
		
		// 사용자 정보 조회
		User savedUser = userDao.getUserById("hong");
		System.out.println("아이디 : " + savedUser.getId());
		System.out.println("비밀번호 : " + savedUser.getPassword());
		System.out.println("사용자명 : " + savedUser.getName());
		System.out.println("전화번호 : " + savedUser.getPhone());
		System.out.println("이메일 : " + savedUser.getEmail());
		System.out.println("사용자 상태 : " + savedUser.getStatus());
		System.out.println("사용자 정보 생성일 : " + savedUser.getCreatedDate());
		
		// 사용자 정보 변경하기
		// "hong" 아이디를 가진 사용자의 이메일, 전화번호 변경하기
		User savedUser2 = userDao.getUserById("hong");
		savedUser2.setEmail("gildong@gmail.com");
		savedUser2.setPhone("010-5678-1234");
		
		userDao.updateUser(savedUser2);
		
	}
}

오라클의 데이터 타입

VARCHAR2(size)
- 가변길이 문자 데이터, 최대값: 4000
- size범위내에서 실제 데이터의 크기만큼만 저장공간을 사용한다.
- 예) 이름, 주소, 과목명, 상품명, 뉴스제목
CHAR(size)
- 고정길이 문자 데이터, 최대값: 2000
- size크기만큼의 저장공간을 무조건 사용한다.
- 예) 주민번호, 학번, 수강과목코드
LONG
- 가변길이 대용량 문자 데이터, 최대값: 2GB
- 현재는 잘 사용되지 않음
- 테이블당 하나밖에 사용할 수 없다.
- 제약조건을 정의할 수 없다.
- order by나 group by에 포함시킬 수 없다.
CLOB
- 가변길이 대용량 문자 데이터(Character Large OBject), 최대값: 4GB
- 예) 블로그의 본문, 신문기사, 논문
NUMBER(p, s)
- 가변길이 숫자 데이터
- p:십진수의 총 갯수, s:소숫점이하 자릿수
DATE
- 날짜 및 시간 데이터
- 예) 입사일, 가입일, 주문날짜, 이체날짜, 신청날짜 ...
TIMESTAMP
- 날짜 및 시간 데이터, 소수점 이하 초까지 포함한다.
BLOB
- 가변길이 대용량 이진 데이터(Binary Large OBject), 최대값: 4GB
- 예) 그림, 영상, 게임파일

ROWID

테이블에서 행의 고유주소를 나타내는 64진수 숫자데이터

select rowid, department_id, department_name
from departments;

rowid			departmet_id	department_name
--------------------------------------------------------
AAAEAWAAEAAAACtAAA	10		Administration	
AAAEAWAAEAAAACtAAB	20		Marketing	
AAAEAWAAEAAAACtAAC	30		건희 영업팀	

오브젝트번호  파일번호  블록번호  데이터번호 
AAAEAW  AAE  AAAACt  AAA 
AAAEAb  AAE  AAAADN  AAA

오브젝트번호
- 해당 데이터가 속해있는 데이터베이스 객체(테이블)의 번호
- 데이터베이스 객체마다 고유하다.
파일번호
- 해당 데이터가 위치하고 있는 테이블스페이스 파일번호
블록번호
- 파일내부의 블록번호
데이터번호
- 데이터가 저장되어있는 데이터 디렉토리 슬롯 번호
```
 
```

데이터 정의어(DDL)

오라클 데이터베이스 객체 생성, 변경, 삭제에 사용되는 명령어
CREATE, ALTER, DROP, TRUNCATE

주요 데이터베이스 객체

테이블
- 데이터의 기본 저장단위, 행과 열로 구성되어 있다.
뷰
- 하나 이상의 테이블을 사용해서 만든 가상의 테이블
시퀀스
- 일련번호 생성기
인덱스
- 검색성능을 향상시키기 위해 데이터에 대한 색인을 가지고 있다.
동의어
- 객체에 대한 다른 이름을 제공한다.

테이블

테이블과 컬럼의 이름규칙
- 문자로 시작, 30자까지 가능, A-Za-z0-9_$# 만 허용,
- 오라클 예약어 사용금지
테이블 생성하기

  CREATE TABLE 테이블명 (
    컬럼명 데이터타입(크기) [DEFAULT 기본값],
    ...
  )

테이블 삭제

  DROP TABLE 테이블명

테이블 변경

컬럼추가

  ALTER TABLE 테이블명 ADD (컬럼명 데이터타입(크기) [DEFAULT 기본값]);

컬럼수정

  ALTER TABLE 테이블명 MODIFY (컬럼명 테이터타입(크기) [DEFAULT 기본값]);

컬럼삭제

  ALTER TABLE 테이블명 DROP 컬럼명;

테이블 이름 변경

  RENAME 테이블명 TO 새테이블명

테이블 절단하기

  TRUNCATE TABLE 테이블명;
  
  -- truncate는 테이블에서 모든 행을 제거한다.
  -- 해당 테이블이 사용하는 저장공간을 해제한다.
  -- truncate로 제거된 행은 rollback할 수 없다.

시퀀스

일련번호를 자동으로 생성하는 데이터베이스 객체다.
기본키 값(행을 대표하는 값)을 생성하는데 주로 사용됩니다.
시퀀스 생성

  CREATE SEQUENCE 시퀀스명;	
  -- 1부터 1씩 증가하는 번호가 발행된다.

  CREATE SEQUENCE 시퀀스명
  [INCREMENT BY a]		a씩 증가한다.
  [START WITH   b]		b부터 시작한다.
  [{MAXVALUE c | NOMAXVALUE}]	c가 최대값이다.
  [{MINVALUE d | NOMINVALUE}]	d가 최소값이다.
  [{CYCLE | NOCYCLE}]		최대값/최소값이 도달했을 때 반복여부를 결정한다.
  [{CACHE e | NOCACHE}]		일련번호를 e개 만큼 미리 할당해서 메모리에 캐시하거나, 캐시를 활용하지 않는다.

시퀀스 생성하기 예제

  CREATE SEQUENCE 시퀀스명
  INCREMENT BY 1		
  START WITH   1	
  NOMAXVALUE
  NOMINVALUE
  NOCYCLE		
  CACHE 20;

시퀀스의 삭제

  DROP SEQUENCE 시퀀스명

시퀀스의 수정

  ALTER SEQUENCE 시퀀스명
  [INCREMENT BY a]		
  [{MAXVALUE c | NOMAXVALUE}]	
  [{MINVALUE d | NOMINVALUE}]	
  [{CYCLE | NOCYCLE}]		
  [{CACHE e | NOCACHE}]		
  
  -- START WITH를 제외한 나머지 설정을 변경할 수 있다.

시퀀스의 사용
- 시퀀스명.NEXTVAL
  - 새로운 일련번호를 반환한다.
- 시퀀스명.CURRVAL
  - 현재 연결된 세션에서 nextval를 통해서 발행했던 번호를 다시 .
  - CURRVAL을 사용하려면 해당 시퀀스에 대한 NEXTVAL이 실행된 후에만 가능한다.

뷰

테이블 혹은 다른 뷰를 기반으로 하는 가상의 테이블(논리적인 테이블)이다.
특징
- 물리적인 저장공간을 가지지 않는다.
- INSERT, UPDATE, DETELE 작업의 수행이 불편하다.
목적
- 복잡한 SQL문을 간단하게 작성하기 위해서 사용
- 데이터의 엑세스를 제한하기 위해서 사용(데이터에 대한 보안성 강화)
- 동일한 데이터로부터 다양한 결과를 얻기 위해서 사용
종류
- 단순 뷰
  - 한 테이블에서만 데이터를 가져온다.
  - 함수 또는 데이터 그룹을 사용하지 않았음
  - DML(INSERT/UPDATE/DELETE가 가능은 하다)
- 복합 뷰
  - 여러 테이블에 데이터를 가져온다.
  - 함수 또는 데이터 그룹을 포함하고 있음
  - DML 거의 불가능하다.
뷰 생성

  CREATE [OR REPLACE] VIEW 뷰이름
  AS 서브쿼리
  WITH READ ONLY
  
  -- 서브쿼리는 가상의 테이블이 포함할 데이터를 조회하는 SELECT문

뷰 수정

  CREATE OR REPLACE VIEW 뷰이름
  AS 서브쿼리
  
  -- 뷰이름을 수정하려는 기존 뷰이름과 동일하게 지정하면 된다.

뷰 삭제

  DROP VIEW 뷰이름

인라인 뷰

SELECT 문의 FROM절에 서브쿼리를 정의하고, 이 서브쿼리로 조회된 결과를 가상의 테이블로 취급하고, 별칭을 부여한 것
데이터베이스 객체가 아니다.
형식

  SELECT A.column, A.column, A.column
  FROM (SELECT column, column, column, ...
        FROM table1
        WHERE 조건식) A
   WHERE 조건식

인덱스(색인)

데이터 행의 검색 속도를 향상시키기 위해서 사용되는 데이터베이스 객체다.
데이터의 위치를 빠르게 찾는 신속한 경로 엑세스 방법을 사용하여 디스크 I/O를 줄여 준다.
인덱스는 테이블과 독립적으로 존재한다.
한 번 생성된 인덱스는 Oracle이 자동으로 유지 관리한다.
테이블이 삭제되면 그 테이블의 데이터를 색인화하고 있는 인덱스도 같이 삭제된다.
인덱스 생성
- 자동 생성
  - 테이블 정의할 때 primary key 제약조건, unique 제약조건이 정의된 컬럼의 값들은 자동으로 인덱스가 생성된다.
- 수동 생성
  - 사용자가 행에 대한 엑세스 시간을 줄이기 위해서 특정 열을 대상으로 인덱스를 생성할 수 있다.
인덱스 생성하기

  CREATE INDEX 인덱스명
  ON 테이블명 (컬럼명, ....)

인덱스 삭제하기

  DROP INDEX 인덱스명

함수 기반 인덱스 생성하기

  CREATE INDEX 인덱스명
  ON 테이블명 (오라클함수)

인덱스를 생성이 필요한 경우
- where절이나 조인조건에 자주 사용되는 경우
- 컬럼이 매우 다양한 값을 포함하고 있는 경우
- 데이터가 아주 많은 테이블을 대상으로 조회작업을 했을 때 대부분의 조회작업에서 검색되는 행이 전체 데이터의 2%~4%미만인 경우
인덱스 생성이 필요하지 않는 경우
- 테이블이 작은 경우
- 테이블이 자주 갱신되는 경우
- 인덱스화된 열이 표현식의 일부로 사용되는 경우
- where절의 조회 조건으로 자주 사용되지 않는 경우
- 대부분의 조회작업에서 전체 데이터의 2%~4%이상 검색되는 경우

트리거

특정 테이블에 INSERT, UPDATE, DELETE 작업이 수행될 때 자동으로 실행되는 것이다.
트리거는 데이터베이스 객체다.
종류
- 행 트리거
  - 테이블의 데이터가 변경될 때 실행되는 트리거
- 문장 트리거
  - 영향을 받는 행이 없더라고 실행되는 트리거
  - 특정 시간에 실행되는 트리거
형식

  CREATE OR REPLACE TRIGGER 트리거명
  {BEFORE | AFTER} 			-- 트리거 실행 싯점
  {INSERT, UPDATE, DELETE} ON 테이블명 	-- 이벤트종류	
  FOR EACH ROW				-- 데이터 행의 변화가 생길 때 마다 실행된다
  BEGIN
    실행할 SQL
  END;

트리거의 접두어(OLD, NEW)
- OLD, NEW 접두어는 행 트리거에서만 사용가능하다.
- 트리거 수행문에서 변경이 발생한 행의 이전값, 변경된(추가된 값)을 사용할 수 있다.
작업 OLD값 NEW값
INSERT NULL 추가된 값
UPDATE 갱신전의 값 갱신후의 값
DELETE 삭제전의 값 NULL

작업	OLD값	NEW값
INSERT	NULL	추가된 값
UPDATE	갱신전의 값	갱신후의 값
DELETE	삭제전의 값	NULL

작성예

주문정보 이력 저장하기
- 주문정보가 추가되거나, 주문상태가 변경될 때마다 그 이력을 저장하기
- 주문정보 이력을 저장하는 테이블 생성하기

  create table tb_order_history (
  order_no number(7),
  cust_no number(5),
  order_status varchar2(20),
  order_update_date date
  );

주문정보 추가/변경시 실행될 트리거 생성하기

  CREATE OR REPLACE TRIGGER order_history_trigger
  AFTER 
  INSERT OR UPDATE ON tb_orders 
  FOR EACH ROW     
  BEGIN
    insert into tb_order_history
    (order_no, cust_no, order_status, order_update_date)
    values
    (:new.order_no, :new.cust_no, :new.order_status, sysdate);
  END;

트리거 활성화 비활성화 하기

  ALTER TRIGGER 트리거명 ENABLE;
  ALTER TRIGGER 트리거명 DISABLE;

트리거 삭제하기

  DROP TRIGGER 트리거명;

-- 테이블에 저장된 각 행들의 ROWID 조회하기
SELECT ROWID, department_id, department_name
FROM departments;

select rowid, department_id, department_name
from departments;

-- 1부터 시작하는 시퀀스 생성하기
CREATE SEQUENCE product_seq
START WITH 1
NOCACHE;

SELECT product_seq.NEXTVAL FROM dual;

-- 상품테이블 생성하기
CREATE TABLE sample_products (
    product_no  number(8) primary key,
    product_name varchar2(200) not null,
    product_maker varchar2(100) not null,
    product_category varchar2(100) not null,
    product_price   number(8) not null,
    product_discount_price number(8) not null,
    product_stock   number(5) default 100,
    product_sold_out char(1) default 'N',
    product_created_date date default sysdate
);

-- 상품테이블에 상품정보 저장하기
INSERT INTO sample_products
(product_no, product_name, product_maker, product_category, product_price, product_discount_price)
values (product_seq.nextval, 'iphone12 plus', 'apple', '스마트폰', 1400000, 1200000);

-- 새로운 일련번호 조회하기
SELECT PRODUCT_SEQ.nextval FROM dual;

-- 현재 시퀀스의 일련번호 조회하기
SELECT product_seq.currval FROM dual;

------------- 퀴즈
-- 사원아이디, 이름, 소속부서 아이디, 소속부서명을 조회하기
SELECT e.employee_id, e.first_name, d.department_id, d.department_name
FROM employees E, departments D;

-- 60번부서에 소속된 사원들의 사원아이디, 이름, 급여, 직종아이디, 직종제목, 직종최저급여, 직종최고급여를 조회하기
SELECT e.employee_id, e.first_name, e.salary, j.job_id, j.job_title, j.min_salary, j.max_salary
FROM employees E, jobs J
WHERE e.department_id = 60
AND e.job_id = j.job_id;

-- 모든 사원들의 사원아이디, 이름, 급여, 급여등급을 조회하기
SELECT e.employee_id, e.first_name, e.salary, s.grade
FROM employees E, salary_grade S
WHERE e.salary >= s.min_salary and e.salary <= s.max_salary
ORDER BY e.employee_id;

-- 60번부서에 소속된 사원들의 사원아이디, 이름, 상사의 이름을 조회하기
SELECT e.employee_id, e.first_name, m.first_name
FROM employees E, employees M
WHERE e.employee_id = m.manager_id
AND e.department_id = 60;

-- 부서관리자가 있는 부서의 부서아이디, 부서명, 부서관리자 아이디, 부서관리자이름을 조회하기
SELECT d.department_id, d.department_name, d.manager_id, e.first_name
FROM departments D, employees E
WHERE d.department_id = e.department_id
AND d.manager_id = e.employee_id
AND d.manager_id is not null;

-- 부서관리자가 있는 부서의 부서소재지 도시명을 중복없이 조회하기
SELECT DISTINCT l.city
FROM departments D, locations L
WHERE d.manager_id is not null
AND d.location_id = l.location_id;

-- 소속부서명이 'Sales'이고, 급여등급이 'A'나 'B'에 해당하는 사원들의 아이디, 이름, 급여, 급여등급을 조회하기
SELECT e.employee_id, e.first_name, e.salary, s.grade
FROM employees E, departments D, salary_grade S
WHERE e.department_id = d.department_id
AND d.department_name = 'Sales'
AND e.salary >= s.min_salary and e.salary <= s.max_salary
AND s.grade in ('A', 'B');

-- 60번 부서에 소속된 사원들의 평균급여를 조회하기
SELECT AVG(salary)
FROM employees
WHERE department_id = 60;

-- 직종아이디별 사원수를 조회하기
SELECT job_id, COUNT(*)
FROM employees
GROUP BY job_id;

-- 급여 등급별 사원수를 조회하기
SELECT s.grade, COUNT(*)
FROM employees E, salary_grade S
WHERE e.salary >= s.min_salary AND e.salary < s.max_salary
GROUP BY s.grade;

-- 2007년 입사한 사원들의 월별 입사자 수를 조회하기
SELECT to_char(hire_date, 'MM') MM, COUNT(*)
FROM employees
WHERE hire_date >= to_date('2007/01/01') and hire_date < to_date('2008/01/01')
GROUP BY to_char(hire_date, 'MM')
ORDER BY to_char(hire_date, 'MM');