Dreamer - I'm wendy~♥♥

Oracle,Mysql 소수점 이하 버림, 반올림, 올림 (trunc 빼고 나머지는 Oracle, Mysql 동일)

전부 버림 : select 34.5678, floor(34.5678) from dual;

특정자리수 버림 : select trunc(345.123, 1), trunc(345.123, 0), trunc(345.123, -1) from dual

반올림 : select 34.5678, round(34.5678), round(34.5678, 1), round(34.5678, 2) from dual;

올림 : select ceil(34.5678) from dual;

나눈후 나머지값 가져오기 : select mod(4/5) from dual;

=> 결과 4 (java의 % 와 같은 기능)

실행

sqlplus "/as sysdba"

-- 테이블스페이스 정보 조회
 select * from dba_data_files;

--유저 조회

select username,account_status,lock_date from dba_users;

글쓴이 : 잡식동물| 원글보기

1. ROLLUP()

   ROLLUP 은 GROUP BY 결과물 테이터 항목별 합을 나타낸다,

   아래와 같은 테이블에서 GROUP BY 만 한것과 ROLLUP 결과물 차이를 보면쉽게 알수 있다.

위> GROUP BY 로만 한결과문 :  B_TYPE(형액형) 별로 PT(포인트)의 평균을 구해보았다.

   SELECT B_TYPE, ROUND(AVG(PT))
   FROM TBL_POINT
   GROUP BY B_TYPE; 

아래> ROLLUP 까지 적용 :  제일 마지막행은 혈액형별 전체 평균 이것은 (결국 모든이의 포인트 평균이 된다.) 

낸것들의 합이 나타난다.

      
   SELECT B_TYPE, ROUND(AVG(PT))
   FROM TBL_POINT
   GROUP BY ROLLUP(B_TYPE);
  

2. ROLLUP() 인자로 두 가지 column을 넣었을?

   SELECT JOB,B_TYPE, ROUND(AVG(PT))
   FROM TBL_POINT
   GROUP BY ROLLUP(JOB,B_TYPE);

   위 쿼리문에서는 ROLLUP 시킬 것으로 형액형외에 JOB(직업)이라는 요소도 추가하였다.

   참고로 편의성을 위해 직업은 숫자로 표현되게하였다. (10 은 학생을 의미, 20은 회사원을 의미 이런식으로)   

위에서 보면 직업별로 형액혈별 모두를 적용시켜 종합시킨 포인트의 평균이 나온다.

위에서 직업번호10번인 사람들중 형액형별(여기서는 O,AB)로 다시 구분해서

포인트의 평균값이 나왔다.

참고로 NULL값들은 해당항목의 총계를 나타낸다.지금은 구분이 어렵지만 테크닉으로

NULL값대신  적당한 값 '10번부서의 포인트 평균" , '총평균' 이런식으로 이름을 지어줄수 있다.

(후반부에 설명함)

3. CUBE

큐브명령은 ROLLUP과 거의 같다.

그러나 차이점은 바로 위에 2번에서 설명한 ROLLUP결과물과 아래것을 비교해보라

   SELECT B_TYPE, ROUND(AVG(PT))
   FROM TBL_POINT
   GROUP BY CUBE(JOB ,B_TYPE);

ROLLUP대신 CUBE를 쓴것외에 차이가 없는 더 많은 항목이 나온것을 알수 있다.

ROLLUP 결과물이 먼저 직업별로 group을 하고-> 각가 직업에 대한 혈액형별로 구분을 둘다 적용한 결과 보여주는데 반해

CUBE는 모든 조합결과물을 보여준다. 여기서는 형액혈별로 평균값을 보여주고 -> 직업별 group을 한후 -> 그 각각 직업에 형액형별로 

구분을 적용한결과를 보여준다.

3. CUBE 와 ROLLUP 비교

3개이상의 column을 ROLLUP 이나 CUBE해줄수도 있으므로 이를 일반화해보면

ROLLUP(a,b,c)  :  a,b,c  집계 -> a,b 집계 -> a 집계 -> 전체합계표시 순으로 하는데 반해

CUBE(a,b,c)   :  a,b,c 집계 -> (a, b) 집계 -> (b,c) 집계 ->

                         (a,c)집계 -> (a)집계 -> (b)집계  -> (c) 집계  -> 전체합계표시순으로 모든 조합 결과를 보여줍니다.

4. GROUPING SETS   

   CUBE 명령시에 내가 원치 않는 모든 조합의 결과물을 보여주므로 내가 원하는 않는 부분도 보게 되므로

   원하는 부분만 세팅해서 볼수 이씨게 하는것이 GROUPING SETS 입니다.

  예를들어서 형액형,직업별을 같이 조합한 결과물과  형액형별,부서별의 조합결과만 보고 싶다면

   SELECT B_TYPE, JOB, DEPTNO

   FROM 테이블이름

   GROUPING SETS((B_TYPE, JOB),(B_TYPE,DEPTNO))

  이런식으로 쿼리문을 작성하면됩니다. 

5. GROUPING()

    - 그것이 총계를 나타내는 결과물이면 1, 아니면 0을  반환 합니다.

   SELECT B_TYPE, ROUND(AVG(PT)),GROUPING(B_TYPE)
   FROM TBL_POINT
   GROUP BY ROLLUP(B_TYPE);

위에서 NULL 부분은 총합계를 나타나므로 GROUPING 값이 1을 반환했습니다.

어찌보면 그리 의미가 없는 결과물같습니다만.

이를 잘 이용하면 아까 앞에서 총계를 나타내는 부분이 NULL로 나와버리는 문제를 해결할수 있습니다.

 <참고> ROLLUP, CUBE 결과물로 나오는 NULL 값을 원래?이로 변경해보는 테크닉

  CASE 문 또는 DECODE문을 쓰면 해결된다.

  ( DECODE문의 경우는 다음 사이트를 참고해보라 http://www.statwith.pe.kr/ORACLE/functions054.htm)

   위의 표에서 밑의 쿼리문을 실행해보면

   SELECT JOB,B_TYPE,ROUND(AVG(PT)), GROUPING(JOB),GROUPING(B_TYPE)
   FROM TBL_POINT
   GROUP BY ROLLUP(JOB,B_TYPE);

   위에처럼 총합이라는 표현이 NULL값이 나와버린다. 이것을 적당한 표현이 되도록 CASE문의 경우로 해결해보자.

   예를들면 job 10 의 null값은 10번 부서 전체의 '포인트평균값'을 의미하므로 바꾸어주면 해석이 편할것이다.

   GROUPING 값을 표시한 이유는 이 값을 이용해서 1일때는 총합을 의미하기 ?문에

   그 값을 가진 필드값만 변환해주는 테크닉을 쓸것이기 때문이다.

    SELECT
    CASE GROUPING(JOB) WHEN 1 THEN '총평균' ELSE TO_CHAR(JOB) END AS "직업번호",
   
    CASE GROUPING(B_TYPE) WHEN 1 THEN '혈액형별평균' ELSE B_TYPE END AS "혈액형",
    ROUND(AVG(PT)) AS "평균포인트값"
   

    FROM TBL_POINT
    GROUP BY ROLLUP(JOB,B_TYPE);

1. 새 컬럼 추가
    alter table 테이블이름
    add (컬럼이름 데이터타입 [default 표현식] [, 다른 컬럼 정의...]);

    ex) alter table any_table add ( any_column1 int not null, any_column2 int);

- 추가된 컬럼은 항상 마지막 순서에 놓인다.(컬럼 순서를 변경할 수 없다.)
- 추가된 데이터는 각 행에 널값으로 삽입된다.
 테이블 내에 레코드가 존재한다면 낫널인 컬럼을 추가할 수 없다.
 빈 테이블일 경우 낫 널을 부여할 수 있다.

2. 존재하는 컬럼 수정
    alter table 테이블이름
    modify (컬럼이름 데이터타입 [default 표현식] [, 다른 컬럼 정의...]);

    ex) alter table any_table modify (any_column1 varchar2(20));

- 타입, 사이즈, 기본값을 변경 가능
- 디폴트 값을 변경했을 경우 이후 삽입되는 데이터에 대해서만 적용된다.

가이드라인
- 숫자형 사이즈 증가 가능
- 문자형 데이터 사이즈 증가 가능
- 사이즈 줄일 수 있는 경우
        모든 값이 널일 경우
        레코드가 없을 경우
        줄이고자 하는 사이즈가 현재 들어가있는 값들보다 작을 경우
- 컬럼의 모든 값이 널 값일 경우 컬럼의 타입을 변경 가능하다.
        char를 varchar2 형태로 바꿀 때는 타입 변경 가능.
- cahr to varchar2, varchar2 to char 변환은 컬럼이 널값만 가지고 있거나 사이즈 변경이 없을 경우만.
- 디폴트 값은 다음 입력부터 반영

3. 컬럼 드랍
    alter table 테이블이름
    drop (컬럼이름);

    ex) alter table any_table drop (any_column1);

- 컬럼에 데이터 존재 유무에 무관하게 드랍 가능
- 한번에 한 컬럼만 삭제 가능하다.
- 테이블은 적어도 한개의 컬럼은 가지고 있어야 한다.
- 드랍하고 나면 복구 불가능
- 제약의 일부이거나 인덱스의 일부일 경우 삭제할 수 없다.
 cascade옵션을 추가한다.
- 레코드가 많은 경우 드랍 칼럼은 옳지 않다. 락을 피하기 위해 유저가 적을때 unused를 쓰고 드랍하자.

4. set unused 옵션

- 더이상 사용하지 않는 컬럼에 대해 set unused를 사용한다.

    alter table 테이블 이름
    set unused (컬럼 이름);
or
    alter table 테이블 이름
    set unused column (컬럼 이름);    

then

    alter table 테이블이름
    drop unused columns;

set unused 옵션도 복구가 불가능하다.
딕셔너리의 USER_UNUSED_COL_TABS 에 저장된다.

5. 제약 추가

- 제약을 추가하거나 드랍할 수 있지만 존재하는 제약을 수정할 수는 없다.
- 제약을 활성화, 비활성화 할 수 있다.
- 낫널 제약을 추가할 때는 반드시 modify를 함께 사용한다.

    alter table 테이블명
    add [constraint 제약명]
    타입 (컬럼명);

- 낫널은 테이블이 비어있거나 해당 컬럼의 모든 레코드가 값을 가질 경우 가능하다.

6. on delete cascade

    alter table Emp2
    add constraint emp_dt_fk
    foreign key (Department_id)
    references departments on delete cascade);

    on delete cascade
- 부모 테이블의 레코드가 삭제되면 같이 삭제될것

7. 제약 드랍

    alter table emp2
    drop constraint emp_mgr_fk;

    - pk를 참조하는 fk도 함께 삭제하기 위해 cascade를 사용한다.
    alter table dept2
    drop primary key cascade;

!! 제약을 드랍하기 위해 제약 이름을 USER_CONSTRAINRS와 USER_CONS_COLUMNS에서 확인할 수 있다.

8. 제약 비활성화/활성화

제약 비활성화

- disable 절을 이용해 비활성화
- cascade 옵션으로 종속적인 constraints도 함께 비활성화

    alter table emp2
    disable  constraint emp_dt_fk;

- pk나 uk를 비활성화 할 경우 내부적으로 uk를 삭제해버린다.

제약 활성화

    alter table emp2
    enable  constraint emp_dt_fk;

- 활성화할 경우 모든 데이터에 대해 제약 조건 검사를 한다.
- pk, uk가 활성화 될 경우 uk가 자동으로 생성된다.
- enable은 create table, alter table에 모두 사용할 수 있다.
- cascade 로 비활성화된 포린 키는 활성화되지 않는다.
- uk, pk를 활성화하기 위해 create index 권한이 필요하다.

Oracle 시퀸스(sequence) 생성

사용하는 이유는 간단히 아래와 같다고 할 수 있겠다.

    - 자동으로 고유한 숫자값을 생성해주며 기본 키 값을 생성하기위해 사용(ex 게시판 글번호)

    - 자동으로 Unique number 를 생성, 공유 가능한 object, 일반적으로 primary key 값을 생성을 위해 사용

사용 예)

CREATE SEQUENCE / 시퀸스 NAME /

INCREMENT BY 1                / 값은 1씩 증가한다 /

START WITH 10                  / 초기 시작 값은 10 이다. 즉 10,11,12... 증가된다 /

MAXVALUE 10000                 / 마지막 최대 값은 10000 이다 /

NOCACHE       / 시스템에 미리 수를 계산해서 만들어 놓을것인지 나중에 계산해서 넣을것인지 /

NOCYCLE       / 최대값까지 증가 되었다면 다시 초기값으로 시작된다 만약 10000 까지 갔다면                   다시 초기 값부터 시작한다 /

1. Oracle 시퀸스(sequence) 확인

--- 최초 조회시 ---

SELECT 시퀸스이름.NEXTVAL FROM DUAL ;

화면출력 >>

        NEXTVAL

        10

출력설명) 초기 시작값을 10으로 설정했기에 10이 나온다. 당연한 말인가..^^;;;

select 시퀸스이름.nextval from daul; 이라 다시 명령하면, 그 조회값은 11이 된다.

다시 말하면, 조회한번에 증가폭만큼 증가 된다.

예제)

INSERT INTO ORDER(ORDER_ID, ORDER_NAME, PHONE)

VALUES (order_seq.NEXTVAL,'가나다','010-0000-1111');

2. Oracle 시퀸스(sequence) 변경

- ALTER SEQYENCE 시퀸스 이름 뒤에 바꿀 내용들.

ALTER SEQUENCE emp_id_seq INCREMENT BY 3 NOCHCHE NOCYCLE;

- 시퀸스 변경시 START WITH 옵션은 변경 할수 없다. 변경하려면 삭제 후 생성.

3. Oracle 시퀸스(sequence) 삭제

drop sequence 시퀸스이름;