2019년 7월 8일 월요일

[강좌]Oracle beginner

Oracle beginner

강좌 정보

  • manialab.co.kr
  • 작성년도 : 2002-03

강사 정보

  • 장동준

약어들

  1. Cartesian Product : 곱집합

제1회 오라클 설치하기

  • 오라클 계정 필요함
  • Database 11g Express Edition - 설치함.
  • 개발 도구 : sqldeveloper-17.4.1.054.0712-x64.zip
  • 접속 명령어
  • connect 계정/pwd

다른점

  • 설치 시 system 계정에 대한 암호를 설정해야 한다.
  • 현재는 manager 라고 설정함.
  • scott/tiger라는 계정은 활성화 되어 있지 않음.

참고 자료 :


제2회 SQL  맛보기

sqlplus 실행

  • sqlplus 를 cmd에서 실행하거나 혹은 [Run SQL Command Line] 아이콘을 실행한다.
  • 종료는 exit

기본적인 명령어들

사용자 생성 및 삭제

  • 생성 : create user <사용자ID> identified by <패스워드>;
  • 삭제 : 
  • drop user <사용자ID>;
  • drop user <사용자ID> cascade;
  • 이 경우에는 사용자 정보(테이블) 까지 함께 삭제함.

사용자 권한 부여 및 취소

  • 사용자 권한 분류 :
  • connect : 접속 권한
  • resource : 자원 생성(테이블 이외)
  • dba : 모든 권한 (?)
  • 권한 부여
  • grant connect[,resource] to <사용자ID>;
  • 권한 취소
  • revoke connect[,resource] from <사용자ID>;

테이블 목록 조회

  • select * from tab;

테이블 생성

  • greate table <테이블명>(컬럼);

테이블 정보 보기

  • desc <테이블명>;

테이블 정보 넣기

  • insert into <table명> values (컬럼정보);

테이블 전체 내용 조회

  • select * from <table명>;

제3회 Oracle svrmgr란...

svrmgr 위치 10 버젼에는 존재하지 않는 것 같다. 다른 형태로 존재할 것이다.
  • <INSTALL_HOME>\app\oracle\product\10.2.0\server\BIN
  • 10i 이상 버젼에서는
  • $ sqlplus / as sysdba
  • $ sqlplus sys/manager  as sysdba

Oracle 서버 관리자가 할 수 있는 일

  • 오라클 DB의 시작 및 종료
  • 오라클 DB 생성
  • 테이터 파일 및 Redo로그 파일의 생성
  • 오라클 DB 복구 및 생성
  • 오라클 DB 사용자 생성
  • 오라클 데이터 베이스 튜닝
  • 시스템에 관련된 특수한 질의의 실행

주요 명령어들

  • 시작과 종료
  • startup/shutdown
  • 백업과 복구
  • archive log
  • archive recover
  • 접속 해지
  • connect
  • disconnect
  • 주석
  • rem
  • 서버관리자 종료
  • exit
  • 도움말
  • help
  • help index : 도움말 항목 보기
  • OS 명령어 잠시 사용하기
  • host
  • spool
  • 서버 관리자상에서 사용하는 명령어와 출력을 외부 파일로 저장 하는데 사용함
  • execute
  • 외부 pl/sql 문을 실행하는데 사용
  • describe (desc)
  • 오라클 DB 객체의 구조를 보기 위해서 사용되는 명령어

오라클 시작/중단 그림

참고사이트


제4회 SQL *Plus 사용하기

SQL*Plus 요약

  • SQL*Plus는 명령어는 SQL 문이 아님
  • SQL*Plus 명령어는 SQL*Plus라는 툴 자체를 사용하기 위한 명령어
  • 끝이 “;” (세미콜론)으로 끝나면 SQL문 그렇치 않으면 SQL*Plus 명령어 라고 할 수 있음.

SQL*Plus 명령어들

  • append <<텍스트>> : 텍스트를 현재 조회한 행에 추가 한다.
  • 단축명령어 : a
  • list : 수행한 명령어  보기
  • 단축명령어 : l
  • <<행번호>> : 행번호 출력하기
  • change /<<old_text>>/<<new_text>>
  • 단축명령어 : c
  • ‘’를 사용시 대소문자가 정확하게 일치 해야 한다.
  • del <<행번호>> : 행번호 삭제하기
  • save <<파일이름>> : 버퍼에 있는 명령어를 주어진 패스의 파일로 저장한다.
  • get <<파일이름>> : 주어진 파일에 있는 명령어를 버퍼로 가지고 온다.
  • run : 버퍼에 있는 명령어 다시 실행 함.
  • 단축명령어 : r
  • start <<파일이름>> : 주어진 파일에 있는 명령어를 실행함.
  • set : 환경설정하기
  • show <<환경변수>> : 환경 설정을 보기

제5회 Oracle Listener

설명

  • 정의 : 오라클 네트워크 관리자 : 네트워크를 이용해서 클라이언트에서 서버로 연결할 수 있게 해줌
  • 추가 설명
  • 참고 #1 ,
  • 관련 파일 위치
  • 디렉토리 위치 : <INSTALL_HOME>\app\oracle\product\10.2.0\server\BIN
  • 파일명 : LSNRCTL.EXE
  • 리스너 설정 관련 파일위치
  • <INSTALL_HOME>\app\oracle\product\10.2.0\server\NETWORK\ADMIN
  • listener.ora
  • 조회 명령어 : status

제6회 Oracle SQL*Loader

설명

  • 정의 : 외부 파일을 오라클 DB에 넣기 위해서 사용하는 유틸리티
  • 파일 관련 정보
  • 디렉토리 위치 : <INSTALL_HOME>\app\oracle\product\10.2.0\server\BIN
  • 파일명 : sqlldr.exe
  • 추가 설명
  • 참고 #1

제7회 Oracle SQL 문 - select 문

Select 문

  • 정의 : DB에서 정보를 질의(검색)하는 명령어.
  • 형식 :
  • SELECT [DISTICT] {*, column [alias],...}
    FROM <table이름>;
  • 참고
  • scott 관련 예제 설명
  • scott 예제 sql 위치
  • <INSTALL_HOME>\app\oracle\product\10.2.0\server\RDBMS\ADMIN
  • scott.sql
  • 참고사이트 #1

함수

  • NVL(컬럼,<null 일때 결과 값>)

alias 사용하기

  • 사용목적은 긴 컬럼명을 간단하게 표현할 때 사용.
  • 사용 방법 예
  • select ename as name, sal “월급” , sal month_money from emp
  • 한글을 “” 사용해야 한다.

연결 연산자(||)

  • ‘문자열1’ || 컬럼 || ‘문자열2’
  • 컬럼이 null 이어도 합친다.

중복해제거 (DISTINCT)

  • 예제 : SELECT DISTINCT deptno FROM emp

제8회 Oracle SQL 문 - Where 절

Where 절

  • 정의 : 조건
  • 형식
  • SELECT [DISTICT] {*, column [alias],...}
    FROM <table이름>
    [WHERE <조건들>];
  • 예제

비교연산자 사용( <,>,<=,=>,=,<>)

  • 예제 select ename,sal,comm
    from emp
    where sal < comm;

Between 연산자

  • 정의 : 특정 범위에 대한 사용하는 연산자
  • 형식 : Between A and B
  • A와 B의 값을 모두 포함합니다.
  • 예제
  • select ename ,sal
    from emp
    where sal between 1000 and 1500;

In 연산자

  • 정의 : IN 연산자 안에 있는 값에 대하여 테스트
  • or 연산자와 비슷함
  • 형식 : IN (a,b,c,d)
  • a,b,c,d 값에 대해서 모두 시험하기
  • 예제
  • select empno, ename, sal, mgr
    from emp
    where mgr in(7902,7566,7788)

Like 연산자 사용

  • 정의 : 검색 문자열 값에 대한 와일드카드 검색을 위해 사용
  • % 는 문자가 없거나 하나이상
  • _ 는 하나의 문자
  • % , _ 를 조합하여 사용할 수 있다
  • 실제 % 나 _ 를 조회할 때는 escape 문자(\)를 사용하여 조회 한다.
  • 형식 : ‘S%’ , ‘S_’
  • 예제
  • select ename
    from emp
    where ename like ‘S%’
  • select ename
    from emp
    where ename like '%A\_B%' escape '\';

IS NULL 연산자 사용

  • 정의 : Null 값에 대한 테스트
  • 형식 : is null
  • 예제
  • select ename, mgr
    from emp
    where mgr IS NULL;

AND 연산자 사용

  • 정의 : AND 양쪽의 조건을 비교하여 참일 경우 true를 리턴

OR 연산자 사용

  • 정의 : OR 양쪽의 조건을 비교하여 한 쪽이라도 참인 경우 true를 리턴

NOT 연산자 사용

  • 정의 : 조건의 반대인 것만

연산자 우선 순위

  • 비교연산자 > NOT > AND > OR

ORDER BY 사용

  • 정의 : 행을 정렬할 때 사용
  • ASC 오름차순
  • DESC 내림차순
  • NULL 값은 오름 차순에서는 제일 나중에 내림차순에서는 제일 먼저 나옴
  • 열별칭을 사용할 수 있음
  • 다중열을 이용하여 정렬할 수 있음. (생략시 asc값이 기본임)
  • 예제
  • select ename,job,deptno,hiredate
    from emp
    order by hiredate desc;

제9회 Oracle SQL 문 - 함수

단일 함수 정의

  • 하나 이상의 인수를 받고 질의에 의해 리턴 될 각각의 행에 대해 하나의 값을 리터함.
  • 임시 테이블 명 DUAL (참고#1)

대소문자 변환함수

  • LOWER : 소문자로 변환
  • UPPER : 대문자로 변환
  • INITCAP : 첫번째 문자를 대문자로 변환 나머지는 소문자로 변환

문자 조작 함수

  • CONCAT() : 값을 합친다. 두 개의 매개변수만 사용
  • SUBSTR() : 지정된 길이 만큼의 문자열을 추출함.
  • 1부터 시작 함.
  • LENGTH() : 문자열의 길이를 숫자 값으로 반환
  • INSTR()  : 명명된 문자의 위치를 숫자 값으로 반환
  • 1부터 시작함.
  • LPAD()   : 문자 값을 우측부터 채웁니다.
  • 예: LPAD(‘1000’,5,’*’);

숫자함수

  • ROUND : 명시된 소수점으로 반올림
  • 예) ROUND(23.786,2) : 23.79
  • 0, -1 , 1
  • TRUNC : 명시된 소수점에서 버림
  • 예) TRUNC(23.786,2) : 23.78
  • MOD   : 나누기를 한 후의 나머지 값
  • 예) MOD(1600,300) : 100

날짜 연산

  • 날짜 + 숫자 = 날짜
  • 날짜 - 숫자 = 날짜
  • 날짜 - 날짜 = 일수
  • 날짜 + 숫자/24 = 날짜
  • SYSDATE : 현재 날짜와 시간을 반환
  • DUAL : 더미 테이블 , 모든 사용자가 사용 가능함.
  • TO_DATE : 주어진 문자열을 날짜로 변환

날짜 함수

  • MONTHS_BETWEEN(date1, date2) : date1 와 date2 사이의 월수를 리턴
  • ADD_MONTHS(date,n) : 월수(n)을 date에 추가함.
  • NEXT_DAY(date,’char’) : date다음의 명시된 요일의 날짜를 리턴
  • LAST_DAY(date) : date를 포함하는 월의 마지막 날짜를 리턴

테이터형 변환

  • TO_CHAR() : number와 date 형을 varchar2 문자 스트링으로 변환
  • TO_NUMBER() : 숫자를 포함하는 문자 스트링을 숫자로 변환
  • TO_DATE() : 날짜를 나타내는 문자 스트링을 날짜형으로 변환

NVL 함수

  • NULL 값을 실제 값으로 변환

DECODE 함수

  • case나 IF-THEN-ELSE 문장의 조건적 조회를 가능하게 함.
  • 예)
  • select ename, deptno, decode(deptno,10,’Accountin’,
    20,’Research’,’Default’)
    from emp;
  • 현재 관련 예제에 오류가 있음.

제10회 Oracle SQL 문 - JOIN

JOIN 정의

  • 한 테이블 이상에서 데이터를 질의해 가져 오는 것을 말함.
  • 종류
  • Equi Join
  • None Equi Join
  • Outer Join
  • Self Join
  • 사용방법
  • select table1.col, table2.col
    from table1, table2
    where table1.col1 = table2.col2
  • where 조건절에 join 조건을 작성합니다.
  • 여러 개의 테이블에서 같은 이름의 칼럼이름을 사용한다면 컬럼이름 앞에 테이블 이름을 넣어 주어야 한다.
  • 주의해야 할것
  • Cartesian Product는 조인 조건이 생략될 때 발생하는데 만약 두개의 테이블이 존재한다면 첫번째 테이블의 모든행이 두번째 테이블의 모든행과 조인이 되는 것
  • 튜닝

Join의 종류

  • Equi Join
  • Where 절에 ‘=’이 들어간 조인
  • 앞부분이 데이타 많은 것을 넣는다. 그러면 튜닝됨
  • Nonequi Join
  • ‘=’ 아닌 다른 operator를 사용한 경우

  • select e.ename, e.sal, s.grade
    from emp e, salgrade s
    where e.sal between s.losal and s.hisal;
  • Outer join
  • 일반적인 조인으로 얻을 수 없는 데이터를 구하고자 할 때
  • 조인 조건을 만족하지 않는 행들도 보기 위해서 함.
  • 상세 공부가 필요함.
  • 참고#1

  • select e.ename, d.deptno, d.dname
    from emp e, dept d
    where e.deptno (+)= d.deptno
    order by e.deptno
  • Self join
  • 두 개이상의 테이블이 하나의 테이블일 경우 즉 한 테이블에서 서로를 조인하는 경우
  • 오라클을 느리게 할 수 있으니 주의 해서 사용해야 한다.

테이블 별칭

  • 30자 까지 가능
  • FROM 절에서 특별한 테이블 명을 위해서 사용됨.
  • SELECT 문장 전체에서 테이블 명 대신에 사용 되어야 한다
  • 의미가 있어야 함
  • 현재의 SELECT 문장에 대해서만 유효

제11회 Oracle SQL 문 - 그룹 함수

정의

  • 그룹당 하나의 결과가 주어지도록 행의 집합에 대해 연산을 하는 함수를 말합니다.

그룹함수의 종류

  • 7가지
  • AVG(평균)
  • COUNT : NULL 값을 무시하지 않고 계산 ,나머지들은 무시
  • MAX
  • MIN
  • STDDEV : 표준 편차
  • SUM
  • VARIANCE : 분산  
  • 숫자 데이터를 저장할 수 있는 열에 대해서
  • AVG,SUM,VARIANCE, STDDEV 사용가능
  • AVG와 SUM
  • COUNT

  • select mgr,avg(deptno)
    from emp
    group by mgr
    having avg(deptno) >= 20
    order by mgr asc;

제12회 Oracle SQL 문 - Sub Query

Main Query 와 Sub Query

정의

  • 다른 sql문에 내장되어 있는 select 문장이라고 생각하면 됨
    select 문장이 독립적으로 있는 것이 아니라 다른 sql 문장 안에 들어 있는 것

서브쿼리 형식

  • select select_list from table : Main Query
    where <연산자>(
    select select_list from table) : Sub Query
  • <연산자> : 비교연산자(>,<,=,in,...) 오른쪽에 괄호로 감싸서 나타낸다 having 절이나
    from 절 에도 올 수 있다.
  • 괄호로 둘러 싸여져야 한다.
  • 비교연산자의 우측에 있어야
  • 단일행 서브쿼리에는 단일행 연산자
  • 다중행 서브쿼리에는 다중행 연산자 (IN, Between)
  • ANY : or 조건
  • ALL : and 조건

수행 순서

  • Sub Query가 먼저 실행되고 결과가 나온 다음 Main Query가 실행 된다.

  • select * from emp
    where sal >= (select sal from emp where ename=’SMITH’);

제13회 Oracle 테이블

정의

  • 테이터를 저장하는 논리적 저장창고 이며 테이터베이스 스키마에 가장 근본이 되는 객체

테이블 생성

  • CREATE TABLE 명령어로 생성
  • 테이블 명은 문자로 시작해야 하고 문자 길이는 30자 이내여야 함
  • A-Z,a-z,0-9,_,$,#
  • 중복된 이름은 사용할 수 없음

다른 사용자 테이블 참조

  • 테이블의 접두어로서 소유자 이름을 사용하면됨
  • 예 scott.emp

사용자 시스템 테이블 조회 (각종 조회:링크)

  • 사용자가 가지고 있는 테이블 목록 조회
  • select * from USER_TABLES;
  • 사용자가 가지고 있는 객체 타입 조회
  • select DISTINCT OBJECT_TYPE from USER_OBJECTS;
  • 사용자가 가지고 있는 테이블, 뷰
  • select * from USER_CATALOG

다른 테이블에서 조회해서 새로운 테이블 생성하기

  • create table dept_test2
    as select empno,ename, sal * 12 as sal, hiredate
    from emp where deptno = 30;

테이블 변경하기

  • 컬럼 추가 : alter table <테이브명> add (<컬럼 정보>)
  • 컬럼 변경 : alter table <테이브명> modify (<컬럼 정보>)
  • 테이블 명 변경하기
  • rename <old 테이블명> to <new 테이블명>
  • 테이블의 모든 내용을 삭제하기
  • truncate table <테이블명>
  • 롤백 불가
  • delete table <테이블명>

테이블 삭제

  • drop table <테이블명>

제14회 Oracle 테이블 제약 조건

정의

  • 부적합한 테이터가 테이블에 삽입되는 것을 방지 하기 위해 constraint 를 사용한다

종류

  • NOT NULL : 이 열은 NULL값을 허용하지 않는다
  • UNIQUE KEY : 테이블의 모든 행에 대해 유일한 값을 가져야 한다.
  • PRIMARY KEY : UNIQUE + NOT NULL 테이블에 오직 하나만 존재
  • FOREIGN KEY : 다른 테이블에서 참조하는 역할을 함.

조회

  • 제약조건 조회
  • select * from user_constraints;

생성

  • 예:create table emp5(
    empno number(4),
    ename varchar2(10),
    deptno number(7,2) not null,
    constraint emp5_empno_pk primary key(empno),
    constraint emp5_deptno_fk foreign key(deptno) references dept(deptno),
    constraint emp5_ename_uk unique(ename)
    );
  • 제약조건 추가
  • alter table emp5
    add constraint emp5_deptno_fk foreign key (deptno)
    references dept(deptno)
  • 제약조건 변경
  • alter table emp5
    modify ename not null


삭제

  • alter table emp5
    drop constraint emp5_deptno_fk

View 란

  • 편리하게 보기 위해서 작성된다.
  • 뷰와 snapshot의 차이점
  • view : select 문을 쉽게 보기 위한 것(
  • snapshot은 select 결과를 저장하기 위한 것
  • 생성
  • create view test_view
    as select empno, ename, deptno from emp
    where deptno=20
  • 생성을 위해서 create view 관련 권한이 있어야 한다.

제15회 Oracle 시퀀스

정의

  • 자동적으로 유일한 번호를 생성하는 오라클 데이터베이스의 객체의 하나임
  • 한 사용자만 사용하는 것이 아니라 여러 사용자가 사용 가능 함.
  • PRIMERY KEY나 UNIQUE KEY를 만들 때 자주 사용됨.
  • 시퀀스를 번호를 재활용 할 수 없음. --> 여러 테이블에서는 사용하지 않는 것이 좋다.

생성

  • create sequence <시퀀스이름>
            increment by <증가치> :

            start with <시작값> :
            maxvalue <최대값> : 기본값은 10^27
            minvalue <최소값> :
            cycle : 순환여부 (nocycle)
            cache : 메모리를 사용여부 (nocache)

조회

  • select * from user_sequences;

시퀀스 사용하기

  • 다음 값을 가져오기
  • select <시퀀스이름>.nextval from dual;
  • 현재 값을 가져오기
  • select <시퀀스이름>.currval from dual;

시퀀스 삭제하기

  • drop sequence <시퀀스이름>

인텍스 생성

  • create index test_2 on emp(ename);

인텍스 조회

  • select * from user_indexes;

인텍스 삭제

  • drop index test_2;
cosmoslight.huni@gmail.com님이 다음 문서를 첨부했습니다.

[강좌]Oracle beginner
Google 문서: 온라인에서 문서를 만들고 수정해 보세요.
Google LLC, 1600 Amphitheatre Parkway, Mountain View, CA 94043, USA
다른 사용자가 Google 문서의 문서를 나와 공유하여 발송된 이메일입니다.
Google 문서 로고

댓글 없음: