Day 26. 오라클 DB 정리

ho_c·2022년 3월 23일

국비교육

목록 보기

26/71

2주 동안 DBMS를 빠르게 배웠다
정말 감잡고 이해한다고 풀어본 DQL문제만 몇개인지...더 이상의 출력은 멈춰!✋
But, 너무 재밌었고, 이제 JDBC로 연결해서 다룰 일만 남았다!

그런 의미에서 지난 2주동안 배운 걸 총 정리하는 시간을 가지고자 한다 :)
물론 오늘 배운 것도 추가되어 있음😼

DateBase

여러 사람이 공유하고 사용할 목적으로 통합 관리되는 데이터 집합

중복된 데이터는 지양하고 데이터를 구조화해, 효율적 처리를 하고자 만들어졌고, 이를 조작하기 위해선 DBMS가 필요하다.

DBMS

Data Base Management System
DBMS는 데이터베이스 내부의 데이터를 조작하고, 생성하기 위한 미들웨어

[ 특징 ]
1) 독립성 : 응용프로그램을 건드리지 않고, DB 사이즈나 데이터를 추가할 수 있다.
2) 무결성 : 특정 양식과 규격을 둠으로써 잘못된 데이터가 들어오는 걸 방지한다.
3) 보안성 : 계정이란 객체를 통해, 그 밑에 데이터를 보관한다. 접근 권한을 가진 사용자만 데이터 자원을 조작할 수 있다.
4) 중복 최소화 : 각 프로그램의 데이터를 통합 관리함으로써, 중복되는 데이터들을 줄일 수 있다.

SQL

Structured Query Language
DBMS 안에서 수행되는 명령어 체계를 말한다. 크게 4가지로 분류된다.

1. DDL

Data Definition Language
객체를 생성, 수정, 삭제하는 명령어

1) Create

객체를 생성하는 명령어

[ 계정 생성 시 ]

create user OO identified by OO;

[ 테이블 및 객체 생성 시 ]

create table cafe(
    컬럼명 자료형 제약조건
);

[ 제약조건 ] : 들어오는 데이터를 통제하기 위한 조건

① `not null`

데이터의 공백을 방지하기 위한 조건

[ 특징 ]

여러 조건으로 활용이 가능.
insert into 로 값을 입력하지 않으면 에러가 난다. not enough values

create table cafe(
    pid number not null,
    pname varchar(30) not null,
    price number not null
);

[ 응용 ]

create table cafe(
    pid number not varchar,
    pname varchar(30) not number,
);

② primary key

식별자 데이터의 중복을 막기 위한 조건

[ 특징 ]

식별자 데이터의 중복을 막기 위해 사용
not null + 중복 방지

create table cafe(
    pid number primary key — 컬럼 레벨 주키 설정
);

create table cafe(
    pid number, 
    pname varchar(30) not number,
    primary key(pid, pname) — 테이블 레벨 주키 설정
);

컬럼 레벨 : 한 데이터에만 주키를 줄 때 사용.
테이블 레벨 : 여러 개의 컬럼을 혼합해 주키를 줄 때 사용.
예외상황 : 주키 설정한 컬럼에 중복 데이터를 입력하면 발생한다.

unique constraint (account.SYS_C007022) violated

③ unique

데이터의 중복을 방지하기 위해 사용

[ 특징 ]

공백을 거르지 않는다.
여러 컬럼에 적용할 수 있다.
주로 unique not null 로 사용하고 이는 primary key와 같다.

create table cafe(
    pid number primary key, 
    pname varchar(30) unique not null,
    price number unique not null
);

④ check

컬럼 안에 들어갈, 값을 통제

[ 특징 ]

check로 설정하면 설정된 데이터만 넣을 수 있다.
check + 조건식

create table cafe(
    pid number primary key,
    pname varchar(30) unique not null, 
    price number not null,
    iced varchar(1) check (iced in ('Y', 'N')) not null
);

⑤ Foreign key(외래키) : 데이터 무결성을 유지하기 위한 수단

[ 특징 ]

동일 데이터를 개별 테이블로 운용하면 의도되지 않는 데이터가 입력될 수 있다.
예) 식별자 컬럼에 뜬금없는 수가 들어가는 경우
이런 경우, 데이터의 무결성이 깨지게 된다. 이를 외래키를 통해 보완한다.
외래키는 타 테이블에 있는 값을 참조하여 사용한다.
참조하는 키(부모 키) | 참조되는 키(자식 키)
부모 키는 primary key unique 로 제약되어야 한다.

create table product(
    pid number primary key,
);

create table sales_history(
    sid number primary key,
-- 컬럼명 references 테이블(부모컬럼)
      pid references product(pid), -- 실행시, 부모 테이블로 가서 데이터 확인 및 입력
    sdate date default sysdate not null 
);

[ 주의 ]

부모 키를 삭제할 때, 문제가 발생하기 쉽다.

데이터 무결성을 위해 부모 키를 삭제하면 전 테이블 안에 그 값이 남아있으면 안 된다.
그러나, 자식 키에 그 값이 남는다. 따라서 부모 키를 삭제하기 위해선 3가지 방법이 있다.

① 외래키 제약조건을 없앤다. - references 제거

② 자식 레코드를 지운 뒤, 부모 레코드를 지운다.

③ 자식 테이블 자체를 재생성하고 옵션을 준다.

on delete cascade : 부모 키 삭제 시, 연결된 자식 키도 함께 삭제

create table sales_history(
    sid number primary key,
    pid references product(pid) on delete cascade, 
    sdate date default sysdate not null  -- default 는 제약조건 앞에만 가능
);

on delete set null : 부모 키 삭제 시, 모든 자식 키의 값을 null로 세팅

create table sales_history(
    sid number primary key,
    pid references product(pid) on delete set null, 
    sdate date default sysdate not null  
);

2) Alter

객체를 수정하는 명령어

① alter – drop : 기존 테이블의 컬럼을 삭제

alter table cafe drop column iced;

② alter – add() : 기존 테이블에 컬럼을 추가

alter table cafe add(iced varchar(1) check(iced in ('Y', 'N')));

③ alter – modify : 기존 테이블 컬럼의 정보를 수정

alter table cafe modify (iced varchar(5) not null);
-- not null을 줄 때는, 기존 컬럼에 공백이 없어야 한다.

④ alter - rename 기존명 to 수정명 : 기존 테이블의 컬럼의 이름을 수정

alter table cafe rename column price to pprice;

⑤ alter 객체유형 객체명 – rename : 기존 테이블의 이름을 수정

alter table cafe rename to cafe_menu;

3) Drop

객체를 삭제하는 명령어

drop | 지울 객체 유형 | 객체명;
drop       table       cafe;

4) 객체

(1) User : 보안성을 위한 각종 권한을 가진 객체

① 시스템 계정

데이터 관리 용도 X
사용자 계정을 생성, 삭제, 수정
계정에 대한 권한을 부여

② 사용자 계정

관리자 계정 내부에 존재
실 데이터를 관리, 조작하는 용도

(2) Table : 관계형 데이터베이스를 구성하는 기본 데이터 객체

record (행- 가로) : 하나의 개체의 여러 정보
column (열- 세로) : 하나의 자료형에 대한 여러 개체 정보

(3) Sequence : 자동으로 순차적으로 증가하는 순번을 반환하는 DB객체

[ 특징 ]

순차적으로 입력되는 데이터를 관리하는 객체이다.
nocache : 메모리에 미리 올려놓기 때문에 의도치 않은 값이 들어갈 수 있기 때문에 설정
객체라서 .을 통해 메서드 활용이 가능하다.

create sequence [시퀀스명]
start with [시작점] -- 예) start with 1 “1부터 시작”
increment by [증감숫자] -- 1씩 증가한다. (변경 가능)
maxvalue -- 최대값 || nomaxvalue — 최대값 없음
minvalue — 최소값 || nominvalue – 최소값 없음
nocache; 

-- 활용
insert into cafe values(cafe_seq.nextval, 'Orange Juice', 4000, 'Y');

(4) View : 링크로 만들어진 가상테이블 객체

하나 이상의 테이블에서 원하는 데이터를 선별해 새로운 가상 테이블을 생성한다.
데이터를 타 계정에 제한해서 보여줄 때 용이.
시스템으로부터 view 생성 권한을 받아서 사용한다.
데이터를 조회할 타 계정에도 출력 권한을 줘야 한다.
‘inline view’는 ‘join’과 성능 면에선 큰 차이가 없다.

[ 예시 ]

grant create view to kh; -- view 생성 권한 (system)
grant select on sub_employee_view to test; -- test 계정에 권한 부여 (kh)

-- view 객체 생성
create view 객체명 as
컬럼1, 컬럼2, 컬럼3 ∙∙∙ from 참조테이블;

create view sub_employee_view as
select emp_id, emp_name, phone from employee;

-- view 객체 조회
select * from kh.sub_employee_view; -- test 계정

[ 원리 ]

view는 물리 테이블을 만들어서 정보를 가져오는 것 X
입력된 컬럼에 링크를 통해 불러오는 것이다.
다른 권한까지 부여하면, view를 통해 상황에 따라 수정, 삭제가 가능

[ 주의 ]
: 다음과 같은 경우는 View를 통해 DML을 수행할 수 없음.

1. 뷰 정의에 포함되지 않은 컬럼을 조작하는 경우
2. 뷰에 포함되지 않은 컬럼 중에 베이스가 되는 테이블 컬럼이 NOT NULL 제약조건이 지정된 경우
3. 산술 표현식으로 컬럼이 정의된 경우
4. JOIN을 이용해 여러 테이블을 연결한 경우
5. DISTINCT를 포함한 경우
6. 그룹 함수나 GROUP BY 절을 포함한 경우

2. DCL

Data Control Language
객체 또는 데이터에 대한 접근 권한을 생성, 수정, 삭제하는 명령어 체계

1) Grant

: 계정 생성 후, 해당 계정에 권한을 부여할 때 사용

grant 권한 to 계정명;
grant connect, resource to admin;

2) Revoke

: 계정에 부여한 권한을 회수할 때 사용.

revoke 권한 from 계정명;
revoke select on employee from test; -- employee 테이블을 검색할 권한

3) Role

: 다양한 권한의 묶음

connect : 오라클 DB에 접속하도록 하는 시스템 권한 묶음
resource : 객체 (생성, 수정, 삭제) 권한 묶음

3. DML

Data Manipulation Language
데이터를 생성 / 수정 / 삭제하는 명령어

DQL

(1) select

데이터를 조회할 때 사용

select문 구성 명령어

[ 기본 문법 ]

select < 컬럼명, 연산식, 리터럴, 서브쿼리 ···· > from 테이블명

[ 실행 순서 ]

select 5 from 1 where 2 group by 3 having 4 order by 6

실행순서가 존재해, 그에 따라 검색 결과가 달라질 수 있다

① select

: 데이터를 검색하여 가져오고 특정 양식으로 출력함.

컬럼을 기준으로 행별로 결과를 출력한다.

② from <데이터를 검색할 테이블>

: 데이터를 검색할 테이블을 지정한다.

select문에서 가장 먼저 실행됨.
inline view, join을 활용할 수 있음.

③ where

: 전체 데이터에 대해 조건을 걸 때 사용

서브 쿼리, 조건식 등을 통해 조건문을 만들 수 있다.

[ 응용 ]

`between` : 특정 구간을 설정할 때 사용.

select emp_name, salary from EMPLOYEE 
where salary >= 300 and salary <= 400;

select emp_name, salary from EMPLOYEE 
where salary between 300 and 400;

`is null` : 해당 값이 공백인지 확인할 때 사용. (공백은 부등호로 계산되지 않는다)

select * from employee where bonus is null; 
select * from employee where bonus is not null;

in (a, b) : `or` 연산자의 간략화이다.

select emp_name, dept_code from EMPLOYEE 
where dept_code='D2' or dept_code='D6';

select emp_name, dept_code from EMPLOYEE 
where dept_code in ('D2', 'D6');

`date` 로 일수 계산하기 : `sysdate`- 날짜 = 일수 (date – date = number)

select emp_name, salary, bonus  from EMPLOYEE 
where floor(sysdate - hire_date) > 365*20;

like` 구문 : 검색 대상의 데이터가 모호할 때 사용

% : %을 기준으로 이후 글자가 없을 수도 있고, 있을 수도 있다.

select * from employee where emp_name like '차%';
select * from employee where emp_name like '%연%'; -- '연'을 포함하는 데이터
select * from employee where emp_name like '%태'; -- '태'로 끝나는 데이터
select * from employee where emp_name like '노%'; -- '노'로 시작하는 데이터

_ : 정확한 한 글자가 존재한다.

select * from employee 
where emp_name like '차__'; -- '차'로 시작, 2글자 존재 (검색 결과 O)

select * from employee 
where emp_name like '차_'; -- '차'로 시작, 1글자 존재 (검색 결과 X)

④ group by <기준>

: 특정 기준을 기반으로 데이터를 묶는다.

[ 특징 ]

기준으로 잡은 컬럼의 각 행을 분석한다.
이를 기준으로 그룹별로 묶는다.
사용 시, 자동 중복 제거

그룹화된 데이터는 개별 출력 불가

select emp_name, dept_code from employee — emp_name X
group by dept_code;

그룹화된 해당 데이터 | 그룹 함수로 데이터 출력 가능

① 그룹화된 해당 데이터

select dept_code from employee group by dept_code;

② 그룹함수로 쓰인 데이터

select sum(salary) from employee group by dept_code;

조건 별로 그룹화 가능

select
    case
        when 나이 between 30 and 39 then '30대'
        when 나이 between 40 and 49 then '40대'
        when 나이 between 50 and 59 then '50대'
        else '60대'
    end 연령대,
    count(*) 인원
from 
    employee
group by 
    case
        when 나이 between 30 and 39 then '30대'
        when 나이 between 40 and 49 then '40대'
        when 나이 between 50 and 59 then '50대'
        else '60대'
    end;
 --단, select에서 출력하는 데이터와 group by 로 묶은 양식이 일치해야 힌디.

⑤ having

: 그룹화된 데이터에 대한 조건을 걸 때 사용

실행 순서 상, where절에는 그룹에 대한 조건을 걸지 못한다.
따라서, 그룹에 대한 조건은 having절에 건다.

[ 응용 ]

-- 부서별 급여합계가 1000만원 이하만 출력
select dept_code, sum(salary)
from employee 
group by dept_code 
having sum(salary)<10000000 --group by에 대한 조건문
order by 1;

⑥ order by <기준>

: 검색한 데이터를 기준으로 정렬할 때 사용

[ 특징 ]

실행순서 중 가장 마지막이다.
기본 오름차순 asc ⟷ desc : 내림차순
기준이 2개 이상이라면, 순서대로 정렬된다.

select emp_id, emp_name, salary 
from employee; -- 기본
select emp_id, emp_name, salary from employee 
order by 3; -- 숫자로도 설정가능
select * from employee 
order by 
	dept_code, salary desc; -- dept_code 정렬 후, salary로 내림차순 정렬

⑦ join

: 여러 테이블의 데이터(행)를 조건으로 하나의 가상 테이블로 모으는 문법

반복문으로 따지면, 이중 for문에 조건을 걸어
여러 테이블의 레코드들을 하나로 묶어 운영하는 문법이다.

[ 원리 ]

select * from DEPARTMENT, Job; -- 오라클 문법

 for Department
      for job
 // 굳이 표현하면 이런 느낌..

조건 없이 사용 시, 앞쪽 테이블 각 행이 뒷쪽 테이블의 각 행에 한 번씩 연결이 된다.

이렇게 출력되는 데이터는 의미가 없다.
의미가 있는 데이터를 위해 조건을 부여해 사용.

on , where 을 통해 조건 부여
조건의 결과가 거짓이면 행끼리 연결되지 않게 된다.
출력 대상 컬럼으로 두 테이블의 컬럼을 모두 사용할 수 있다.
조건에서 서로 존재하지 않는 행들은 출력되지 않는다. = 모두 false

[ 주의 ]
: 비교 컬럼 간의 이름이 같은 경우

별명처리 : `as` 로 구분해준다.

employee (job_code) | job (job_code)

select 
	emp_name, 
	A.job_code, 
	job_name  
from employee A 
join job B 
on (A.job_code=B.job_code)  -- 확실하게 구분.
order by 1;

using : 비교 대상이 되는 컬럼명이 같을 때만 사용.

select 
	emp_name, 
	A.job_code, 
	job_name
from employee A 
join job B using(job_code)  
-- from employee A join job B on (A.job_code=B.job_code)와 같다.
order by 1;

[ 두 가지 문법 ]

① Oracle 전용 문법 : 오라클에서만 사용되는 문법

select emp_id, emp_name, dept_title 
from employee, department 
where (dept_code=dept_id) 
order by 1;

② ANSI 표준 문법 : ANSI를 따르는 DBMS에선 통용된다.

select emp_id, emp_name, dept_title 
from employee join department on (dept_code=dept_id) 
order by 1;

[ join의 종류 ]

cross join

조건이 없는 join

select * from DEPARTMENT, job;

inner join (inner 생략 가능 : default)

의미 있는 데이터를 만드는 join

null 값은 조건에 대해 false가 되어서 반환 X
inner join은 공백은 생략하고자 하는 의미가 강하다.

select emp_id, emp_name, dept_title 
from employee (inner) join department on (dept_code=dept_id) 
order by 1;

(left, right) outer join (outer는 생략 가능)

각 테이블의 데이터 생략을 막는 join

outer join 도 표준에 따라서 다르게 적용된다.

① left outer join : 왼쪽에 있는 테이블의 데이터 생략 X

[ ANSI ]

select emp_id, emp_name, dept_title 
from employee 
left outer join department on (dept_code = dept_id);

오라클 : 값이 없는 테이블에 (+)을 추가해줌

select emp_id, emp_name, dept_title 
from employee, department where dept_code = dept_id(+);

② right outer join : 오른쪽에 있는 테이블의 데이터 생략 X

[ ANSI ]

select emp_id, emp_name, dept_title
from employee 
right outer join department on (dept_code = dept_id);

오라클

select emp_id, emp_name, dept_title 
from employee, department where dept_code(+) = dept_id;

③ full outer join : 양 테이블의 데이터 생략 X

[ ANSI ]

select emp_id, emp_name, dept_title 
from employee 
full outer join department on (dept_code = dept_id);

self join

테이블 2개를 쓰는 것은 맞지만, 둘은 같은 테이블이다.

-- employee의 MANAGER_ID: 각 직원 별 직속 상사의 ID
select * from employee; join employee;

-- 자신이 관리하고 있는 사원의 이름, 급여 등을 출력하세요.
select 
    e1.emp_name,  상사의 이름
    e2.emp_name,  직원의 이름
    e2.salary   직원의 월급
from employee e1 
join employee e2 on (e1.emp_id = e2.manager_id);

-- 그룹화 추가
select 
    e1.emp_name, 
    count(*),    
    sum(e2.salary)
from employee e1 join employee e2 on (e1.emp_id = e2.manager_id)  
group by e1.emp_name;

다중 join

-- 여러 개의 join도 가능
select
    emp_name, dept_title, job_name
from employee e 
left join department d on (dept_code = dept_id)  -- 첫 번째 join
join job j on (e.job_code=j.job_code)  -- 첫 join에 따른 두 번째 join 
order by 1;

함수

① 단일행 함수

각 행 하나마다 적용되는 함수

[ 문자열 관련 ]

length : 문자열 길이

select length('Hello') from dual; -- 문자열의 길이를 리턴 [5]

lengthb : 문자열의 바이트 수

select lengthb('Hello') from dual; -- 영문자는 한 글자당 1바이트 
select lengthb('한글') from dual; -- 한 글자당 3바이트

instr : 문자열에서 시작을 기준으로 찾는 문자의 n번째 인덱스를 반환

instr('문자열', '찾고자 하는 거', 전체 인덱스 중 시작점, 발견 시점);
select instr('Hello World Hi High', 'H', 1, 1) from dual;

substr : 문자열 안에서 원하는 만큼의 문자열을 추출하는 기능

substr('Now on Oracle practice',시작점, 시작포함 n개)
select substr('Now on Oracle practice',8,6) from dual;

distinct : 중복 제거

select distinct substr(emp_name,1,1) from employee;

replace : 문자열 치환 기능

select replace('Hello Java', 'Java', 'oracle') from dual;
select replace(email, 'kh', 'iei') from employee;

[ 숫자 관련 ]

abs : 절대값 구하는 기능

select abs(-1) from dual;

mod : 나머지 연산자(%)

select mod(10,2) from dual; -- 0
select mod(10,3) from dual; -- 1

round : 반올림 함수

select round(126.756, 소수점 기준 몇 번째 자리) from dual;
select round(126.756, -1) from dual; -- 130
select round(127.243, 1) from dual; -- 128.2

floor : 버림 함수

trunc : 소수점 자리수를 지정하여 버림

select trunc(123.456, 1) from dual; -- 123.4

ceil : 올림 기능 - 강제로 올려버림

select ceil(122.024) from dual; -- 123

[ 날짜 관련 ]

sysdate : 현재 시간 값을 가져오는 함수의 일종. 소괄호X = 키워드

select sysdate from dual;

months_between : 두 날짜 사이의 개월 수 차이를 반환

select emp_name, hire_date, months_between(sysdate, hire_date) 
from employee;

add_months : 인자로 전달받은 날짜에 인자로 전달받은 숫자만큼의 개월 수를 더해 반환

select add_months('22/03/17',6) from dual; -- 22/09/17

next_day : 인자로 전달받은 날짜에 인자로 전달받은 요일 중 가장 가까운 다음 날짜를 반환

select sysdate, next_day('22/03/17', '토요일') from dual; --'22/03/19'

last_day : 인자로 전달받은 날짜가 속한 달의 마지막 날을 구해서 반환

select last_day(sysdate) from dual;

extract : 인자 날짜로부터 원하는 값을 추출

select extract(year from sysdate) from dual;
select extract(month from sysdate) from dual;
select extract(day from sysdate) from dual;
select emp_name,extract(year from hire_date) from employee;

[ 형변환 함수 ]

to_char : 날짜, 숫자 데이터를 문자열로 변환하는 함수

<날짜의 경우>

아스키 코드(/, -, .)를 넘어가는 양식에 대해선 “”을 통해서 설정해줘야 한다.

select to_char(sysdate, 'YYYY/MM/DD') from dual;
select 
	to_char(sysdate, 'YYYY-MM-DD HH:MI:SS') -- mm = MI
from dual; 
select to_char(sysdate, 'YYYY"년 "MM"월 "DD"일" HH24:MI:SS') from dual;

<숫자의 경우>

1. 원본 데이터 개체보다 형식의 크기가 커야 한다. 작으면 깨진다.
2. L은 시스템 국가의 화폐 기호가 붙는다.
3. 9대신 0을 쓰면, 빈자리를 0으로 채워준다.

select emp_name, to_char(salary, 'L999,999,999') from employee;
select emp_name, to_char(salary, '000,000,000')||'원' from employee;

to_date : 문자, 숫자 데이터를 날짜로 변경해준다.

인자간 양식이 일치해야 한다.
입력되는 데이터들의 범위가 일치해야 함.

select to_date('2019년01월02일', 'YYYY"년"MM"월"DD"일"') from dual;
select to_date(1901020820, 'YYMMDDHHMI') from dual;

[ 조건 함수 ]

decode : 삼항연산자 (자바 swicth)

가변 인자를 가진다.
`decode(조건, 결과1, 결과1에 대한 동작, 결과2, 결과2에 대한 동작)
‘같다’ 라는 연산만 가능하다.
조건에 대한 인자는 계속 추가될 수 있다.

select 
    decode(substr(emp_no, 8, 1), 1, '남','여' ) 
from employee;

case : 특정 값에 대한 조건으로 경우의 수를 나눈 함수 (자바 IF)

select
    case
        when 조건 then 결과
    end
from employee;

② 그룹 함수

특정 컬럼 전체에 한번에 적용되는 함수

sum(컬럼명) : 총합 함수

select sum(salary) from 
employee; --- 70096240
select to_char(sum(salary), 'L999,999,999') 
from employee; --- ￦70,096,240

avg(컬럼명) : 평균 함수

select round(avg(salary), 1) from employee;

count() : null값 제외, 행의 개수를 계산해줌 (행 갯수 계산 목적)

null은 카운트하지 않는다.
주로 *찍어서 테이블 전체 행을 계산.

select count(dept_code) from employee; 
select count(*) from employee;

max() : 컬럼에서 최대치를 구한다. | min : 컬럼에서 최소치를 구한다.

select max(salary) as "최고연봉" from employee;

Ranking (Top-N)

위에서부터 순서를 붙히는 함수

주로 ‘inline view’와 함께 사용된다.

1) rank() over(order by 기준 컬럼명 desc)

: 공동순위 부여 가능, 공동순위 부여시 공백은 제거

select 
    emp_name, 
    salary,
    rank() over(order by salary desc) 순위
from 
    employee;

2) dense_rank() over(order by 기준 컬럼명 desc)

: 공동순위를 부여하지만, 그로 인해 비는 값을 지우지 않는다.

select 
    emp_name, 
    salary,
    dense_rank() over(order by salary desc) 순위
from 
    employee;

3) row_number() over(order by 기준 컬럼명 desc)

: 공동순위를 붙이지 않고, 행번호를 컬럼으로 빼는 용도로 사용된다.

select 
    emp_name, 
    salary,
    row_number() over(order by salary desc) 순위
from 
    employee;

추가적으로 inline view랑 row_number()는 같이 쓰인다.

select * from
    (select emp_name, salary, row_number() over(order by salary desc) 순위 from employee) 
where 
    순위 between 1 and 5;

Set Operator

: 두 개 이상의 테이블을 합치는 건 같지만, 원리가 Join과 다르다.

종류 : `UNION` / `UNION ALL` / `INTERSECT` / `MINUS`
용도 : 데이터 양식은 똑같은데, 의미가 다른 경우. 전체 조회 시 사용.

① UNION

: 중복되는 데이터는 한 번만 출력되게 병합.

- 열의 개수만 맞으면 병합이 된다.

select emp_id, emp_name, emp_no -- 3개 
from employee 
union
select * from department;  -- 3개

- 상응하는 두 테이블의 모든 열의 자료형이 맞아야 한다.

select salary from employee  -- 숫자
union
select dept_id from department;  -- 문자
-- 병합 X

select * from tmp1
union
select * from tmp2;

② UNION ALL

: 중복되는 데이터까지 포함해 출력되게 병합

select * from tmp1
union all
select * from tmp2;

③ INTERSECT

: 중복되는 데이터만 출력

select * from tmp1
intersect
select * from tmp2;

④ MINUS

: 첫 번째 select 결과에서 두 번째 select 결과를 뺄셈하여 남는 첫 번째 데이터만 출력

select * from tmp1  ABC (중심은 첫번째)
minus
select * from tmp2;  BCD

Sub Query

: Main Query + (Sub Query) : 메인 쿼리 내부에 쿼리를 하나 더 넣는 문법

1. 쿼리 안의 쿼리를 다루는 문법
2. join과 어느 정도 호환
3. 문법적으로 order by 는 차단되어 있다.
4. 중첩 서브쿼리도 가능하다.
5. where 뿐만이 아니라, 모든 곳에서 사용될 수 있다.
6. 반환 종류와 활용법에 따라 다양하게 나뉜다.

① 단일행

: 서브 쿼리의 반환값이 단일행일 때

join, subquery 미사용

-- 특정 직원의 매니저 이름을 알아내세요. 
select * from employee where emp_name = '전지연';
select emp_name from employee where emp_id = 214;

단일행 쿼리

select emp_name from employee 
where emp_id= -- 메인 쿼리
(select manager_id from employee where emp_name='전지연'); -- 서브 쿼리
-- 딱 한 개의 값이 출력된다.

join

select e2.emp_name 
from employee e1 
join employee e2 on (e1.manager_id = e2.emp_id)
where
    e1.emp_name='전지연';

where에 그룹함수 사용가능하다는 이점

-- 전 직원의 평균 급여보다 많은 급여를 받는 직원의 사번, 이름, 직급코드, 급여를 출력하세요
select emp_id, emp_name, job_code, salary 
from employee 
where 
    (select avg(salary) from employee)<=salary;

② 다중행

: 서브 쿼리의 결과가 여러 행을 반환할 때

서브쿼리의 결과로 여러 행을 반환하기 때문에 =(같다) 비교를 사용할 수 없다.
in, any, all 을 이용해 여러 값 중 하나를 고르게 한다.

join, subquery 미사용

-- 송종기나, 박나라가 속한 부서의 직원 이름, 부서코드, 급여를 출력하시오
select dept_code from employee 
where emp_name = '송종기';
select dept_code from employee 
where emp_name = '박나라';

select emp_name, dept_code, salary from employee 
where dept_code in ('D5', 'D9');

다중행 쿼리

select 
	emp_name, dept_code, salary from employee
where 
dept_code in 
(select dept_code from employee where emp_name in ('송종기', '박나라'));

대소비교

예) J3 직급인 사람들의 급여보다 더 큰 급여를 가진 직원의 이름과 급여를 출력

-- J3 직급인 사람들의 급여보다 더 큰 급여를 가진 직원의 이름과 급여를 출력
-- all : 다중행의 모든 결과 중 1개
select emp_name, salary 
from employee
where salary > all (select salary from employee where job_code='J3');  

-- any : 다중행의 모든 결과 
select emp_name, salary 
from employee
where salary > any (select salary from employee where job_code='J3');

③ 다중열 - 다중행열

: 반환 값이 여러 열인 서브 쿼리 또는 여러 열과 여러 행이 서브 쿼리

[ 문법 ]

(값 1, 값2) = (select 값1, 값2  from 테이블 where 조건);
(dept_code, job_code) 
= (select dept_code, job_code from employee where ent_yn='Y)

여러 열만 나올 땐 = , in 비교가 가능.
서브 쿼리의 출력값과 메인 쿼리의 입력 값이, 순서가 일치해야됨.

[ = , in, any, all ]

: 다중 행-열 또는 단일행으로 받는 반환 값을 메인쿼리로 담을 때, 조건이 존재

전제 : 같은 컬럼 값은 컴퓨터가 구분할 수 없다.

=(같다) 는 다중행, 다중행열의 경우 적용할 수 없다.
any : 모든 값에 대해 비교 | all : 모든 값 중 대표 1개랑 비교
in : 다중행, 다중행열이 반환 값에서 하나를 찾을 때 주로 사용.

④ 상관 쿼리

: 주로 select 절에 들어가는 서브쿼리

select 
    emp_name 직원명, 
    (select dept_title from department where dept_code = dept_id) 부서명 
    -- 조건을 걸어서 가져옴.
from employee;

위 예제는 서브쿼리가 상관 쿼리로 사용된 예시이다.
이를 통해 다음 특징을 알 수 있다.

[ 특징 ]

1. 외부 테이블에서 값을 끌어왔다.
2. 상관 쿼리는 메인 쿼리의 데이터를 사용힌다.
3. 실행 순서는 from 을 제외하고, 서브쿼리가 먼저 실행된다.
4. 메인 쿼리와 연관되어 동작하는 쿼리는 의미에서 상(호연)관 쿼리
5. 출력마다 한 번씩 서브쿼리를 동작하기 때문에 성능이 안 좋다.
6. 홀로 사용될 수 없다.

⑤ Inline view

: from 절에서 주로 사용되는 서브쿼리

view 객체로서 테이블의 컬럼을 링크로 연결해 가상테이블을 만든다.
이 가상 테이블을 이용해 검색을 한다.
join으로 엮어서도 사용할 수 있다.

select * from employee;
select * from (select emp_name, salary from employee);

(2) insert

객체가 아닌 “데이터”를 넣을 때 사용

insert into <테이블명>  values (입력값); 
-- `into` `values()` 와 함께 사용된다.

-- 데이터를 각 컬럼별로 넣을 때는 다음과 같이 사용한다.
insert into <테이블명> (컬럼명) values (입력값);

(3) update

데이터를 수정할 때 사용

update <테이블명> set (대상컬럼 = 수정값); -- 기본

insert into cafe values(1000, 'Americano', 3000, 'Y');
insert into cafe values(1001, 'CafeLatte', 4000, 'Y');

update cafe set price = 1500; -- 모든 가격이 바뀐다.
update cafe set price = 1500 where pid=1000; -- 조건을 걸어 사용

-- 여러 행을 동시에 수정할 때
update cafe set price = 2500, iced='N' where pid = 1000;

(4) delete

데이터를 지울 때 사용

delete from <테이블명> where 조건; -- 조건이 없으면 모든 행을 삭제한다.

4. TCL

Transaction Control Language
트랜잭션을 통제하는 명령어

Transaction

: 데이터베이스의 상태를 변화시키기 위해 수행하는 작업의 단위

공간이 아니다.
insert, delete, update 만 트랜잭션에 걸린다.
4가지 특징 존재한다.

[ 특징 ]

① 원자성 (Atomicity)

: 트랜잭션에 쌓인 명령이 DB에 모두 반영되던지, 아니면 모두 반영되지 말아야 한다.

작업의 원자성은 개발자에게 가장 중요하다. 

하나의 작업에 연결된 각 쿼리에 의한 데이터가 입력, 삭제, 수정될 때, 
모든 쿼리가 일관된 결과를 가져야 한다. 한 개의 쿼리라도 실행되지 않는다면 
각 쿼리에 연결된 테이블의 데이터가 꼬일 수 있다.
이런 문제로부터 보호하기 위해 트랜잭션은 모든 쿼리가 성공해야 DB에 커밋을 한다.

② 일관성 (Consistency)

: 트랜잭션의 처리 결과가 일관성이 있어야 한다.

트랜잭션 중 DB가 변경되어도, 실행하는 시점의 DB를 이용해야 한다.
이게 없으면 모든 DB사용자가 사용할 때마다 달라지는 DB를 봐야 한다.

③ 독립성 (Isolation)

: 둘 이상의 세션이 DB에 연결하게 되면, 먼저 진행 중인 트랜잭션이 끝날 때까지 실행되지 않는다.

동시에 다른 수정(생성, 수정, 삭제)작업을 진행하지 못하게 오라클에서 막아버린다.
만약 동시 같은 데이터를 누군 수정하고, 삭제할 수 있다면 해당 데이터는 안전하지 못하다.

④ 지속성 (Durability)

: 트랜잭션이 성공했을 경우, 그 결과는 DB에 영구적으로 반영되어야 한다.

1) Commit

트랜잭션이 성공하여 쌓인 명령의 결과를 DB에 반영함

커밋이 되는 경우는 세 가지이다.
① commit; 입력
② 트랜잭션이 꽉 찬 경우 – 자동 커밋
③ DDL, DCL과 같은 쿼리 – 자동 커밋

2) Rollback

트랜잭션에 쌓인 명령을 취소한다.

안전성을 위해 트랜잭션에서 명령들을 대기 상태로 묶는다.
따라서 delete 로 데이터를 날려도, 커밋을 하지 않는 이상 명령은 DB에 반영되지 않고 트랜잭션에 쌓여 있다.
rollback;을 하게 되면 해당 명령이 사라진다.

3) Savepoint

롤백 시, 돌아갈 위치를 설정하

delete from employee where dept_code = ‘J3’;
savepoint point1; -- point1 기준점 생성
delete from  employee where emp_name='선동일';
savepoint point2; -- point2 기준점 생성 

rollback to point1 -- point1로 돌아감. (선동일은 삭제되지 않음)
rollback to point2 -- point2로 돌아감. (J3, 선동일이 삭제되어 있음)

단, 과거에서 더 과거로 갈 순 있지만, 과거에서 과거 기준 미래로 갈 순 없다.
commit 이 되는 순간, savepoint 를 이용할 수 없다. (트랜잭션이 종료됨)

ho_c

기록을 쌓아갑니다.

이전 포스트

Day 25. 오라클 DB 4 (서브 쿼리)

다음 포스트

Day 26. 오라클 DB 정리

국비교육

DateBase

DBMS

SQL

1. DDL

1) Create

[ 제약조건 ] : 들어오는 데이터를 통제하기 위한 조건

① not null

② primary key

③ unique

④ check

⑤ Foreign key(외래키) : 데이터 무결성을 유지하기 위한 수단

2) Alter

3) Drop

4) 객체

(1) User : 보안성을 위한 각종 권한을 가진 객체

(2) Table : 관계형 데이터베이스를 구성하는 기본 데이터 객체

(3) Sequence : 자동으로 순차적으로 증가하는 순번을 반환하는 DB객체

(4) View : 링크로 만들어진 가상테이블 객체

2. DCL

1) Grant

2) Revoke

3) Role

3. DML

DQL

(1) select

select문 구성 명령어

① select

② from <데이터를 검색할 테이블>

③ where

[ 응용 ]

between : 특정 구간을 설정할 때 사용.

is null : 해당 값이 공백인지 확인할 때 사용. (공백은 부등호로 계산되지 않는다)

in (a, b) : or 연산자의 간략화이다.

date 로 일수 계산하기 : sysdate- 날짜 = 일수 (date – date = number)

like` 구문 : 검색 대상의 데이터가 모호할 때 사용

④ group by <기준>

⑤ having

⑥ order by <기준>

⑦ join

별명처리 : as 로 구분해준다.

using : 비교 대상이 되는 컬럼명이 같을 때만 사용.

① Oracle 전용 문법 : 오라클에서만 사용되는 문법

② ANSI 표준 문법 : ANSI를 따르는 DBMS에선 통용된다.

cross join

inner join (inner 생략 가능 : default)

(left, right) outer join (outer는 생략 가능)

① left outer join : 왼쪽에 있는 테이블의 데이터 생략 X

② right outer join : 오른쪽에 있는 테이블의 데이터 생략 X

③ full outer join : 양 테이블의 데이터 생략 X

self join

다중 join

함수

① 단일행 함수

[ 문자열 관련 ]

length : 문자열 길이

lengthb : 문자열의 바이트 수

instr : 문자열에서 시작을 기준으로 찾는 문자의 n번째 인덱스를 반환

substr : 문자열 안에서 원하는 만큼의 문자열을 추출하는 기능

distinct : 중복 제거

replace : 문자열 치환 기능

[ 숫자 관련 ]

abs : 절대값 구하는 기능

mod : 나머지 연산자(%)

round : 반올림 함수

floor : 버림 함수

trunc : 소수점 자리수를 지정하여 버림

ceil : 올림 기능 - 강제로 올려버림

[ 날짜 관련 ]

sysdate : 현재 시간 값을 가져오는 함수의 일종. 소괄호X = 키워드

months_between : 두 날짜 사이의 개월 수 차이를 반환

add_months : 인자로 전달받은 날짜에 인자로 전달받은 숫자만큼의 개월 수를 더해 반환

next_day : 인자로 전달받은 날짜에 인자로 전달받은 요일 중 가장 가까운 다음 날짜를 반환

last_day : 인자로 전달받은 날짜가 속한 달의 마지막 날을 구해서 반환

extract : 인자 날짜로부터 원하는 값을 추출

[ 형변환 함수 ]

to_char : 날짜, 숫자 데이터를 문자열로 변환하는 함수

to_date : 문자, 숫자 데이터를 날짜로 변경해준다.

[ 조건 함수 ]

① `not null`

`between` : 특정 구간을 설정할 때 사용.

`is null` : 해당 값이 공백인지 확인할 때 사용. (공백은 부등호로 계산되지 않는다)

in (a, b) : `or` 연산자의 간략화이다.

`date` 로 일수 계산하기 : `sysdate`- 날짜 = 일수 (date – date = number)

별명처리 : `as` 로 구분해준다.