Day 22. 오라클 DB 2

ho_c·2022년 3월 20일
국비교육
0

국비교육

목록 보기
22/71
post-thumbnail
post-custom-banner

3일째 DQL의 늪에서 허덕이고 있는 나.
조건에 따라서 출력하라면 만들 수는 있는데, 뭔가 무의식으로 하는 느낌이 강하다.
계속해서 정리하다 보면 깨달음이 오겠지..
무튼 오늘은 DQL의 심연과 DDL. DCL을 잠시 배워봤다. ( 평가 때문에..급하게 진도 뺌 )

📝목차

  1. 그룹화 : group by , having
  2. 그룹 함수
  3. DDL
  4. DCL
  5. DML - ETC

DQL의 기본 구조는 다음과 같다.

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

여기에 우리는 추가적으로 조건과 정렬을 붙여봤다. 거기서 좀 더 나아가, 특정 기준을 기반으로 데이터를 묶고, 조건에 따라 묶고자 한다. 그러나 한 가지 짚고 넘어갈 것이 DQL의 실행 순서이다.

우리가 그룹화까지 진행하게 되면 다음의 문장 구조를 가진다.

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

위의 입력된 숫자대로 실행되게 된다. 이에 따라 출력 결과가 달라질 수 있으니 주의해서 사용하자.

다시 본론으로 돌아와 그룹화를 알아보자.

1. 그룹화

group by

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

[ 특징 ]

  • 사용 시 중복 제거 기능이 따라온다.
select dept_code from employee group by dept_code;
  • 그룹화된 데이터는 개별 출력이 불가능하다. (컴퓨터는 대표 데이터를 정할 수 없음)
select emp_name, dept_code from employee 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 (2022 - (1900+substr(emp_no, 1,2))) between 30 and 39 then '30대'
        when (2022 - (1900+substr(emp_no, 1,2))) between 40 and 49 then '40대'
        when (2022 - (1900+substr(emp_no, 1,2))) between 50 and 59 then '50대'
        else '60대'
    end 연령대,
    count(*) 인원
from 
    employee
group by 
    case
        when (2022 - (1900+substr(emp_no, 1,2))) between 30 and 39 then '30대'
        when (2022 - (1900+substr(emp_no, 1,2))) between 40 and 49 then '40대'
        when (2022 - (1900+substr(emp_no, 1,2))) between 50 and 59 then '50대'
        else '60대'
    end

이때, 분류해둔 데이터에서 추출하기 때문에 select 에서 출력하는 데이터와 group by 로 묶은 양식이 일치해야 한다.

위와 같이 그룹화된 데이터는 실행 순서에 따라 where 로 조건을 걸 수 없다. 따라서 DQL 에는 그룹화된 데이터를 대상으로 적용되는 조건 문법이 있다.

having

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

부서별 급여합계가 1000만원 이하만 출력

select dept_code, sum(salary)
from employee 
group by dept_code 
having sum(salary)<=10000000 --group by에 대한 조건문
order by 1;

지난 시간 단일행 함수에 대해서 배웠다. 단일행 함수는 각 행 하나마다 적용되는 함수를 의미한다. 따라서 출력 결과도 각 행 별로 나오게 된다.

하지만 위에 보면, 그룹화된 데이터는 각 행별로 출력될 수 없다.
왜냐면 컴퓨터는 그룹화 된 데이터에서 특정 데이터를 고를 수 없기 때문이다.
따라서 그룹화 된 데이터를 대상으로 적용되는 함수가 있다.

2. 그룹 함수

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

  • 행 전체를 대상으로 하여 결과가 1개만 나온다.
  • 그룹 함수는 독자적으로 사용해야 한다.

1) sum(컬럼명) : 총합 함수

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

2) avg(컬럼명) : 평균 함수
select round(avg(salary), 1) from employee;

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

  • null은 카운트하지 않는다.
  • 주로 *찍어서 테이블 전체 행을 계산한다.
select count(dept_code) from employee; 
select count(*) from employee;

4) max() : 컬럼에서 최대치를 구한다. | min : 컬럼에서 최소치를 구한다.
select max(salary) as "최고연봉" from employee;

이렇게 DQL의 기본을 살펴보았다. 원래 여기서 더 나가야 하는데, 다음날 평가가 있는 사실을
강사님이 늦게 전달받았기 때문에, 급격하게 평가 범위 진도를 나가게 되었다.

3. DDL

: 객체를 생성, 수정, 삭제하는 명령어

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 로 제약되어야 한다.
  • 컬럼명 references 테이블(부모컬럼)
create table product(
    pid number primary key,
);

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

해당 코드가 실행되면, 부모 테이블로 가서 데이터를 확인하고 받아온다.
쉽게 말하면 부모 테이블에 존재하는 값만 받도록 하는 목적으로 사용한다.

[ 문제점 ]

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

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

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

  • 부모는 먼저 못 지우지만 자식을 지우면 가능하다.

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

  • 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) drop

: 객체를 삭제하는 명령어

[ 문법 ]

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

3) squence

: 자동으로 순차적으로 증가하는 순번을 반환하는 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. DCL

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

grant

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

grant connect, resource to admin;
  • connet : 접근권한
  • resource : 자원 생성

5. DML - insert / delete

1) insert

: 객체가 아닌 데이터를 넣을 때 사용하는 명령어.

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

2) delete

: 데이터를 지울 때 사용하는 명령어.

delete from <테이블명> where 조건;
  • 조건이 없으면 모든 행 자체를 삭제한다.
profile
ho_c
기록을 쌓아갑니다.
이전 포스트

Day 21. 오라클 DB 1

다음 포스트

Day 23. 국비교육 네번째 평가

post-custom-banner

0개의 댓글