6.26 / Type of Join

Oracle/SQL 2010/08/06 14:08
  1.  natural join
     이름 같은 컬럼 1개. -join 1개
     join되는 컬러이름 지정부 생략
  2. inner join(기본 조인)
     using 사용가능(table alias 생략 가능)
     on절에서는 table alias 생략 불가
  3.  outer join
     using 사용가능
  4.  cross join

on clause
 : 모든 경우 가능, table alias 필수

self-joins
 : inner joind의 응용, where 대신 and 가능

non-equijoins
 : on절에서 = 대신 between문 사용. ( 등급 표시를 구현이 목적)

92버전
(+)족이 덤.(+)안한 쪽이 다 나옴.

inner versus outer joins (join되는 컬럼의 값이 널이라도)

left outer join 왼쪽이 다 나오게
right outer join 오른쪽이 다 나오게
full outer join 양쪽 다 나오게

cartesian products 모든 경우의 수 다나오게

cross join 카르티잔 곱 (on, using 쓰면 에러)
natual join (on, using 쓰면 에러)

subqueries
row 1개 리턴(값) : single row subquery
row n개 리턴(값) : multi row subquery
뷰형태(2차원) : view style의 subquery

 () singlerow일때는 상관없음 multirow일때 중의
multirow (in,any,all)


select rownum, rowid, deptno, dname from dept;

    ROWNUM ROWID                  DEPTNO DNAME
---------- ------------------ ---------- --------------
         1 AAAMfKAAEAAAAAQAAA         10 ACCOUNTING
         2 AAAMfKAAEAAAAAQAAB         20 RESEARCH
         3 AAAMfKAAEAAAAAQAAC         30 SALES
         4 AAAMfKAAEAAAAAQAAD         40 OPERATIONS

rownum은 order by이 하기전의 출력 순서

multirow ex)
select ename, sal, rownum
  2  from (select ename, sal from emp order by sal desc)
  3  where rownum <= 5;

ENAME             SAL     ROWNUM
---------- ---------- ----------
KING             5000          1
SCOTT            3000          2
FORD             3000          3
JONES            2975          4
BLAKE            2850          5

any 하나의 조건이라도 만족하면 됨
all 모든 조건을 만족해야 됨

union (합집합)

SQL> select ename from emp
  2  union
  3   select dname from dept;

ENAME
--------------
ACCOUNTING
ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
KING
MARTIN
MILLER

ENAME
--------------
OPERATIONS
RESEARCH
SALES
SCOTT
SMITH
TURNER
WARD

18 rows selected.


union all(정렬을 안해서 빠르다)
교집합
차집합 minus 순서조심(결과 다름)
union 합집합 (중복제거, 집합이기때문에 내부적 정렬)

col "1" noprint 컬럼 리스트 안나옴


읽기 일관성 : commit하기 전까지 다른 유저는 보지 못함.

2010/08/06 14:08 2010/08/06 14:08
  1. 단위

     10^3 k
    10^6 m
    10^9 g
    10^12 t
    10^15 peta
    10^18 exa

  2. date function
  3. months_between 월과 월차이
    add_months 몇개월후
    next_day 돌아오는 해당일
    last_day 해당월일의 마지막날
    round 반올림
    trunc 내림
  4.  implicit(암시적,자동)
    select last_day(sysdate-30) from dual;
  5. explicit(명시적, 수동)
    select to_char(1)||to_char(1), to_number('1')+to_number('1') from dual;
  6. 문자->날짜
    select sysdate-to_date('1986.09.30','yyyy.mm.dd') from dual
  7. 날짜-문자
    select to_char(sysdate,'yyyy')||'년 ' || to_char(sysdate,'MM')||'월' from dual

    hh:hour,hh24,mi:분,ss : 초,ff6: 소수점 이하 초(6자리

    yy(95을 2095년으로 인식) rr(1950~2049년으로 자동 mapping)

  8. nesting functions(중첩 가능한 이유는 리터값이 1개라서)
  9. nvl 널이면 특정값으로 변환
  10. nvl2 널이면 두번째값 리턴 아니면 세번째값 리턴
  11.  SQL> select nvl2(null,1,2), nvl2(123,1,2) from dual;

    NVL2(NULL,1,2) NVL2(123,1,2)
    -------------- -------------
                 2             1

  12. nullif 같으면 널 값리턴, 다르면 첫번째 값

    select nullif(1,1), nullif(1,2) from dual;
    NULLIF(1,1) NULLIF(1,2)
    ----------- -----------
                          1

  13.  coalesce : nul중첩 (null이 아닌 첫번째 파라미터 리턴)
    select coalesce(null,null,null,1) from dual

    COALESCE(NULL,NULL,NULL,1)
    --------------------------
                             1

  14.  성능 때문에
    case expression(where절에서 많이 사용)
    select ename,
        case deptno
         when 10 then 'ACCOUNTING'
        when 20 then 'RESEARCH'
        when 30 then 'SALES'
        when 40 then 'OPERATIONS'
        else 'OPERATIONS'
       end dname
        from emp;
  15.  decode function(select와 from사이에 사용)

    select ename,
    decode( deptno
    , 10 , 'ACCOUNTING'
    , 20 , 'RESEARCH'
    , 30 , 'SALES'
    , 40 , 'OPERATIONS'
    , 'OPERATIONS'
    ) dname
    from emp


     버퍼 사용시 중간 강제 종료시 해결 방법
    ed을 수정하는 중 강제 종료 되면 swp 파일이 남아 있다
    그럼 sqlplus로 접속후 ed 편집하면 에러 메시지 발생
    그럴경우 rm .*.swp 실행

  16.  메뉴얼 보는 법

     otn.oracle.com -> documentation
    키워드 이용 : 단어는 아는데 뜻을 모르는 경우
    ex)는 oe/oe or hr/hr 계정에서
    분류체게 이용. 뜻은 아는데 단어를 모르는 경우
    books->

  17.  락 풀기
    alter user oe identified by oe account unlock;
  18.  4장

    aggregated (group multi-row에 대해 연산하는...)
    select job, count(*) from emp group by job having count(*)>1
    job별로 1명이상인 job의 명수를 구하기.
    stddev 편준편찬(값자체를 이용할때), variance 분산(단순비교형)
    count(컬럼)널이 아닌 값의 갯수
    count(*) row의 갯수
    select avg(nvl(comm,0)) from emp;

    group by 묶은 컬럼명만 사용 가능
    select deptno, avg(nvl(sal,0)), sum(sal) from emp group by deptno;

  19.  5장
    multiple tables (join -92, 99)
    sql 92를 통한 조인
2010/08/06 14:08 2010/08/06 14:08
  1. 질의의 기본 절차
    1. 접속
       conn scott/tiger
    2. 테이블 리스트 확인
       select * from tab;
    3. 테이블 컬럼 리스트
       desc dept;
    4. 질의 확인
      select * from table_name;
  2. sqlplus 명령어
    list : 마지막 실행 명령어
    run : 마지막으로 실행시킨(버퍼에 있는) 명령 실행
    conn : 접속
    show user : 접속한 유저명 보기
    save t : 버퍼에 있는 명령 저장
    @t : 실행(확장자 sql로 자동) t는 파일명
    ! : command 창으로 나가기
    exit : 다시 들어오기
    get t : 해당 파일 불러오기
    ed t : 해당 파일 수정하기
    ed : 버퍼를 편집 - sql명령 1개만 편집가능
    sav t replace : 덮어 씌기
    show all : 설정사항 확인
    set command : 설정 사항 변경
    set linesize : 라인 크기
    /u01/app/oracle/product/10.2.0/db_1/sqlplus/admin/glogin.sql : 세팅 파일
  3. isqlplus : 웹버전 sqlplus
  4. SQL

    select
    * 모든 컬럼
    distinct 중복제거
    expression : 계산식 계산
    alias : 컬럼의 heading을 지정

sql는 대소문자(case-sensitive) 구별 안함. 단 따옴표("')안에서는 구분
' : 문자열
" : 객체명, 비번 컬럼 alias

select 300*3 from dual; dual 가상 테이블 1개만 나오게 함
null값 연산은 null 값

concatenation operator : 문자열 더하기 ||


EX)

select 1+'1', '1'+'1', 1||1, '1'||'1' from dual;

     1+'1'    '1'+'1' 1| '1
---------- ---------- -- --
         2          2 11 11

  1. literal character strings
    select 'Mr. '||ename from emp;

    'MR.'||ENAME
    --------------
    Mr. SMITH
    Mr. ALLEN
    Mr. WARD

  2. alternative quote(q) operator
    select 'Mr. '||ename||'''s salary is '||sal from emp

    'MR.'||ENAME||'''SSALARYIS'||SAL
    -------------------------------------------------------------------
    Mr. SMITH's salary is 800
    Mr. ALLEN's salary is 1600


    select 'i''m a boy.' from dual; //9i버전
    select q'-i'm a boy.-' from dual; //10g 버전

  3. distinct : duplicate rows

  4. isqlplus (익스플로러 영문으로 해야 깨지지 않음)

  5. append sql

    EX)
    SQL> select * from
      2
    SQL>
    SQL> a  emp
      1* select * from emp
    SQL> r
      1* select * from emp

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

where 은 로우 마다 비교

정규표현식 검색

is null
is not null

rules of precedence (연산자 우선순위)
(), */+-, ||(문자열 더하기) , < =, like, is null, between, between, not, and, or

&변수 정의 / 매번 실행 물어봄
&&변수 / 처음 한번만 물어봄 , define 변수에 등록함
define 환경 변수 셋팅
undefine sortcolumn 환경변수 지우기
set verify off

  1. Function
    1. user defined function : pl/sql 로 만든 함수
    2. built-in function
      1.  single function :
          ex)
          select power(2,10) from dual;,
          select ename, sal+nvl(comm, 0) from emp;
          select ename from emp where ename like upper('%bl%')
        1. character : lower, upper, initcap
             concat ||
             substr 뽑아내기
             length 길이
             instr 몇번째 있는가?
             lpad | rpad 채우기
             trim 양끝단 '' 없애기
             replace 치환
        2. number
             round : 반올림 round(11.2111, 2)
             trunc : 내림
             mod : 나머지 mod(1600,300)
        3. date function : sysdate(시스템 날짜)
              alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
             1일(시간) = 숫자 1
             시각 + 시간 = 시각
             시각 - 시각 = 시간
             하루 1440분 / 86400초
        4. conversion
        5. general
      2.  group function : count(*), sum(sal)
    3. function의 arg는 n개 result는 1개
2010/08/06 14:07 2010/08/06 14:07
  1. 윈도우 오라클 설치
    1. setup 실행 안될 시
    2.  / install/oui 실행 후 설치
  2. 설치시 주의점
    1. 설치 파일이 있는 폴더에 한글이나 특수문자 있으면 안됨.
    2.  설치 유저명에 특수문자나 한글 있으면 안됨.
    3. 기존 버전 정리... 처음 설치시는 삭제
  3. 기존 버전 설치 여부 확인 방법
    1. cmd -> sqlplus
    2. 제어판 - 관리도구 - 서비스 - 관련서비스가 있음
  4. 설치 후 확인 절차
    1. cmd -> sqlplus / as sysdba  -> select status from v$instance;

      STATUS
      ------------
      OPEN  <-- 설치 완료

  5. 윈도우 오라클 삭제 방법
  6.  실행 - regedit

  7.  local_machin - software - oracle <- delete

  8.  local_machin - system - controlset001 - services - oracle 시작하는 서비스 4개(기본적으로)
    - controlset 폴더 있는 거 전부 2차 처럼 지우기

  9.  재부팅

  10.  c:\Program files\oracle <- 폴더 삭제

  11. INTRO
    1. 소개

    Dr.E.F.Codd(전산, 집합론)
    IBM에서 상용화

  12. 데이터베이스와의 차이점

    종이문서 : 간단한 경우

    EXCEL  : 복사 용이, 보관용이 / 파일을 메모리에 올리기 때문에

    데이터베이스 : 동시사용자 지원, 대용량 지원

  13.  정리

    인사DB 샘플
     직원명부 부서리스트
    SCOTT  EMP   DEPT     <--수업용
    HR EMPLOYEES DEPARTMENTS <--숙제용, 교재용

    설계시 Entity / 테이블 (실제 존재)
    Attribute 컬럼
    UID PK

    PK(NOT NULL + UNIQUE)
    FK(다른 테이블의 PK만 들어오도록 함)


    select from  SQL -> oracle server
    테이블 <-  Cursor

    빌트인 뷰

  14. SQL

    1. Query

      select

    2. DML

      insert  
      update
      delete
      merge 있으면 업데이트 없으면 삽입

    3. DDL
      create  
      alter
      drop
      rename
      truncate 비우기
      comment 주석달기

    4. DCL

      grant  권한주기
      REVOKE 권한뺏기

    5. Transaction

      Transaction (all or nothing) 전무 혹은 전부를 보장
      저장 commit
      취소 rollback
      savepoint

    6. 기타

      DML (Transaction 수동)
      DDL DCL (Transaction 자동)

2010/08/06 14:06 2010/08/06 14:06

환영합니다

분류없음 2010/08/03 20:32

텍스트큐브 사용을 환영합니다. 텍스트큐브(Textcube) 는 웹에서 자신의 생각이나 일상을 기록하고 표현하기 위한 도구입니다. 강력한 글 관리와 편집 기능을 통하여 쉽고 빠르게 글을 작성하고 알릴 수 있습니다. 또한 통합된 소통 기능및 RSS 바깥글 읽기 기능을 통하여 다양한 사람들과 간단하게 의견을 주고 받을 수 있습니다.

또한 텍스트큐브는 플러그인과 테마 시스템을 통하여 다양한 기능을 추가하거나 스킨을 바꾸고 편집할 수 있습니다. 뿐만 아니라 OpenID, microformat 지원 등의 기술적인 부분 및 다국어 지원을 포함한 강력한 저작 도구입니다.

사용하며 도움말이 필요할 때는 관리자 메뉴의 우측 상단의 도우미 링크를 누르시면 도움말을 보실 수 있습니다. 기타 자세한 정보는 http://www.textcube.org 를 방문해서 확인하실 수 있습니다.

이 글은 새 블로그에 자동으로 적힌 글입니다. 관리자 화면에서 언제든지 지우셔도 됩니다.

2010/08/03 20:32 2010/08/03 20:32