Date

2021-04-07

국비 수업시간에 배운 Oracle DB 기초 정리

Oracle DB

주요 자료형 (data type)

중요 개념

제약 조건 종류

테이블 생성

예제를 통해서 코드를 설명

    create table student(

        hakbun varchar2(12) primary key,
        name varchar2(30) not null,
        major varchar2(30) not null,
        year number(1),
        age number(3),
        regdate date not null

    );
create table로 테이블 생성, 그 밑에 자료형을 적는다.

테이블 컬럼 관련문

    -- 테이블에 컬럼을 추가
    -- 형식) alter table 테이블이름 add(컬럼명, 데이터타입(크기), 제약조건);
    alter table student add(address varchar2(200));

    -- 테이블의 컬럼을 수정(자료형 수정)
    -- 형식) alter table 테이블이름 modify(컬럼명 데이터타입(크기));
    alter table student modify(age varchar2(3));

    -- 테이블의 컬럼명을 변경하기
    -- 형식) alter table 테이블이름 rename column 기존컬럼명 to 새로운 컬럼명;
    alter table student rename column address to addr;

    -- 테이블의 컬럼을 삭제하기
    -- 형식) alter table 테이블이름 drop column 컬럼명;
    alter table student drop column age;

테이블 데이터 추가

    -- 방법 1
    insert into student values('2021_001', '홍길동', '경제학과', 3, 27, sysdate);

    -- 방법 2
    insert into student(hakbun, name, major, regdate) values('2021_002', '유한별', '회계학과', sysdate);
칼럼 갯수가 맞아야 한다.

테이블 데이터 수정, 삭제

    -- update 테이블이름 set 컬럼이름 = 컬럼수정내용 where primary로 되어 있는 컬럼명
    update student set addr = '충청남도 천안시' where hakbun = '2021_002';

    -- 형식) delete from 테이블이름 where 기본키로 설정된 컬럼명 = 컬럼 데이터;
    delete from student where hakbun = '2021_004';

    -- student 테이블에 있는 모든 데이터를 삭제하는 방법.
    delete from student;

테이블 조회

    -- student 테이블의 모든 컬럼을 보여준다.
    select * from student;

    -- student 테이블의 학번, 전공, 학년, 나이를 보여준다.
    select hakbun, major, year, age from student;

    -- student 테이블의 학번으로 컬럼 제목 바꾸기.
    select hakbun as "학번" from student;

    -- distinct 중복제거 조회
    select distinct major from student;

literal 문자열

    select name || '학생의 전공은' || major || '입니다' from student;

where 조건절

모든 데이터를 가져오는 것이 아니라 사용자가 원하는 데이터만 조회할 경우에 사용.

기본 Oracle DB에 있는 emp 테이블 사용

select 컬럼명1, 컬럼명2…. from 테이블이름 where 조건식;

    select * from emp where job = 'MANAGER';
    -- emp 테이블에서 급여가 1300 ~ 1500 사이인
    -- 사원의 이름과 담당업무, 급여, 부서번호 출력
    select ename, job, sal, deptno from emp where sal between 1300 and 1500;
    -- emp 테이블에서 사번이 7902, 7788, 7566인
    -- 사원의 사번, 이름, 담당업무 출력
    select empno, ename, job from emp where empno in (7902, 7788, 7566);

like 키워드

    -- ename 컬럼에 S자를 포함하는 사원의 이름을 검색.
    where ename like '%S%'

    -- ename 컬럼의 첫글자가 S자로 시작하는 사원의 이름을 검색.
    where ename like 'S%'

    -- ename 컬럼의 마지막 글자가 S자로 끝나는 사원의 이름을 검색.
    where ename like '%S'

    -- ename 컬럼의 두번째 글자가 S자를 포함하는 사원의 이름을 검색.
    where ename like '_S%'

order by 절

    -- emp테이블에서 부서번호를 기준으로 오름차순으로 정렬을 하고,
    -- 부서번호가 같은 경우 급여를 기준으로 내림차순 정렬을 하여 출력
    select * from emp order by deptno, sal desc;
deptno 뒤에는 asc가 생략되어있다.

그룹함수

    -- 관리자수, 급여 평균과, 급여최고액, 급여최소액, 급여합계액 출력
    select count(mgr), avg(sal), max(sal), min(sal), sum(sal) from emp

시퀀스

순번을 부여할 때 사용하는 문법.

    -- MEMO 테이블 생성
    create table memo(
    bunho number(5),                    -- 메모 글번호
    title varchar2(100) not null,       -- 메모 글제목
    content varchar2(1000) not null,    -- 메모 글내용
    regdate date,                       -- 메모 작성일
    primary key(bunho)                  -- primary key의 다른지정방법
    );

    -- MEMO 작성시 사용할 시퀀스 생성
    create sequence memo_seq start with 1 increment by 1;

    -- 1 부터 생성된 시퀀스를 bunho 값에다 넣어준다.
    insert into memo values(memo_seq.nextval, '메모1', '길동이 글', sysdate);

join ~ on 키워드

조인의 종류

DB Join 다이어그램 설명에서 자세한 설명과 예제를 확인 할 수 있다.

서브쿼리

주의사항

    -- emp 테이블에서 이름이 'SCOTT'인 사원의 급여보다 더 많은
    -- 급여를 받는 사원의 사번, 이름, 담당업무, 급여를 출력
    select empno, ename, job, sal
    from emp
    where sal > (select sal from emp where ename = 'SCOTT');

View 테이블

View를 사용하는 이유

View 정리에서 자세한 내용 확인

트랜잭션(transaction)

데이터 처리의 한 단위

트랜잭션 사용 이유

데이터의 일관성을 유지하면서 데이터의 안정성을 보장하기 위해 사용. 트랜잭션 사용 시 트랜잭션을 제어하기 위한 명령어.

PL/SQL(Procedual Language / SQL)

SQL 만으로는 구현이 어렵거나 구현 불가능한 작업을 수행하기 위해 오라클에서 제공하는 프로그래밍 언어

기본구조

주의사항

    declare
    -- 스칼라 자료형
    v_empno number(4) := 7788;
    v_ename varchar2(20);
    -- 레퍼런스 자료형
    v_empno emp.empno%type := 7693;
    v_ename emp.ename%type;
    -- rowtype : 테이블의 모든 컬럼을 한꺼번에 저장하기 위한 변수로 선언하는 방법.
    emp_row emp%rowtype;
    begin
    -- 실행문, 조건제어문 등이 들어갈 위치
    v_ename := 'ADAMS';
    dbms_output.put_line('v_empno >>> ' || v_empno);
    dbms_output.put_line('v_ename >>> ' || v_ename);
    end;
    /

PL/SQL 변수 선언 방법 및 조건 제어문, 반복문 사용법 정리에서 자세한 예제를 확인할 수 있다.

마무리

DB를 통해 CRUD정도를 할 수 있을 정도의 기초적인 내용을 배우고 JAVA 프로젝트를 최종적으로 DB연결을 하여 마무리를 하였다.

  1. number(7, 2) : 전체 자릿수는 7자리이고, 정수는 5자리, 실수 2자리 

  2. 기본키는 해당 테이블을 대표하는 컬럼으로서의 역할을 수행하며, 다른 테이블에서외래키들이 참조할 수 있는 키로서의 자격을 가진다. 이를 참조 무결성이라고 함. 

  3. 데이터 조회에 많이 사용한다.