'sql'에 해당되는 글 4건

  1. 2010/08/06 boyo 7.2 / DCL
  2. 2010/08/06 boyo 6.30 / DDL and Transantion
  3. 2010/08/06 boyo 6.26 / Type of Join
  4. 2010/08/06 boyo 6.25 / Date and Nesting Function

7.2 / DCL

Oracle/SQL 2010/08/06 14:09
with read only 읽기 전용
select view_name from user_views;--뷰 확인
sequences
일련번호 생성기, PK 기본값
next.val : transaction의 영향 안받음., 다음 숫자
currentval.val : transcation의 영향 받음., 세션에서의 마지막 리턴 숫자
cache 메모리에서 미리 읽어 드림.
cycle : maxvlaue에 도착하면 minvalue로 바꿔라

create sequence ss
increment by 10
start with 5
maxvalue 20
minvalue 3
cycle
nocache

select sequence_name from user_sequences;
시퀀스 확인

index
select index_name from user_indexes
인덱스 확인
index은 primary key로 설정하면 인덱스가 자동으로 생성
primary key로 생성된 인덱스 삭제 방법
alter table ttt drop constraint sys_C005440;
수동으로 생성된 인덱스 삭제 방법
drop index index_name;

물리적인 rowid 확인
select comm, rowid from emp where comm is not null order by comm

built-in view
 performance view - from 메모리(V$~~시작 : v$fixed_table)
 data dictionary view - from 파일 [dictionary view] : (DBA_~~ : 전체, ALL_~~ : 접근가능한, USER_~~ : 내가소유한)

 scott 소유한 테이블
 select count(*) from user_tables;
 scott 접근 가능한 테이블
 select count(*) from all_tables;
 db전체의 테이블 수(sysdba)
 select count(*) from dba_tables;
 전체 view(sysdba)
 select count(*) from dba_views;
 현재 접속자(session) 리스트(sysdba)
 select username from v$session where username is not null

세션관련 테이블
select name from v$fixed_table where name like '%SESSION%';

--built-in view검색하기(save fv)
select view_name from dba_views where view_name like upper('%&key%')

select CONSTRAINT_NAME, CONSTRAINT_TYPE from USER_CONSTRAINTS where TABLE_NAME='EMP'

07.02 오후 dcl

grant select on emp to hr;
revoke select on emp from hr;
rol 권한의 집합, 권한 받을 때는 유저처럼 권한 줄때는 system권한처럼...

ddl -- alter문
 add - defualt
 modify - defualt
 drop

flashback 기능 -> 과거의 변화를 추적 혹은 복원
external 테이블 -> text파일을 select할 수 있음.

flashback

external tables -> text파일을 select할수 있음

insert all
insert first

dss 의사결정지원 시스템
dw-dss 분석
1.자료수집 - external table
2.분류작업 -
3.분석
4.레포팅
rollup : 부분합
cube : cross tabulation질의 (차원질의,타블릿질의)

5장
date
timestamp : 소주점 이하 초까지
timestamp with timezone : 위에꺼 플러스 시간대 정보
timestamp with local timezone : 시간대 정보가 자동으로 변경
interval day to second (3 12:13:5) : 3일 12시간 13분 5초
interval year to month 3-4 : 3년 4개월

6장
서브쿼리

7장
트리구조 구현
self조인응용.

8장
Regular Expression(정규표현식)

기존    10g정규표현식 응용(4개 함수로)
컬럼명 like 키문자열  regexp_like(컬럼명,표현식)
replace    regexp_replace
substr    regxp_substr
instr    regxp_instr

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

statment-level rollback
ex)ddl 실패시 해당 문장만 rollback되고 자동으로 commit됨
read consistency commit하기 전까지는 다른 유저가 못 봄

table : 데이터를 저장하는 객체
view : select문 저장 객체(table처럼 사용됨)
sequence : 일련번호 생성기
 create sequence t2s;
 t2s.nextval;
 select t2s.currval from dual;

index : 검색속도 향상위해 미리 만든 객체
create index

synonym : 동의어

질의 실행 시간 측정 (set timing on)
질의의 실행 계획 확인 set autot on exp
truncate 공간까지 비워짐(delete은 undo 테이블에 복사하기때문에 느리고 공간도 안비워짐)

select emp.ename from scott.emp; 테이블 명과 유저명 생략(당연한 거니까 ㅋㅋㅋ)
다른 유저의 테이블을 볼러면 유저명을 써서 사용한다.

data types
varchar2(size) 가변길이-> 글자영릐 넓이값까지 저장, 공간은 덜차지
char(size) 고정길이: 글자1이 들어가도 넓이값만큼 공간차지
number(p,s) p: 전체넓이, s : 그중에서도 소주점으로 사용할 자리수.
datetime data types
timestamp : 소주점이하까지
interval year to month
interval day to second
timestamp with local time zone : 자동으로 변환해서 저장.

not null 널을 허용 안함
unique 중복을 허용 안함, 에러위치 모름
primary key not null + unique
foreign key 다른(특정) primary key값만 허용.
check 특정 조건만 허용

talbe level로 하는 것을 권장
inline방식 constraint 지정
create table tc2(xx number unique, x2 number); column level
outline방식 constraint 지정
create table tc2a(xx number, yy number, constraint tc2a_uk unique(xx)) table level

create table tc4( id number, yy number, deptno number, constraint tc4_fk foreign key(deptno) references dept(deptno));
constraint tc5_ck check(xx>0);

not null talbe level constraint을 지원하지 않음

crating table as : constraint는 복사가 안됨

alter table
alter table d2 add(xxx number);
alter table d2 add constraint d2_pk primary key(deptno);
alter table d2 modify(xxx number(4) default 0);
alter table d2 drop column xxx;
alter table d2 drop(loc);

테이블이 이미 있다고 하면 찾아보기 (시퀀스나 인덱스에 있을수 있음)
select object_name, object_type from user_objects where object_name like '%&key%'

simple view는 dml이 모두 가능

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

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