오라클 기본 6 - 인덱스

 1. 개념 

검색을 빠르게 하기 위해 책의 찾아보기 부분과 같이 미리 정리를 해놓는 기술

2. 장점 

-검색의 속도를 높일 수 있다.

-쿼리의 부하가 줄어들어 시스템 전체 성능이 향상된다.

3. 단점

-인덱스가 DB 공간을 차지하여 추가적인 공간이 필요

-처음 인덱스를 생성하는데 시간이 필요

-변경 작업(insert, update, delete)이 자주 일어날 경우 오히려 성능이 나빠질 수 있음

4. 정의법

CREATE TEBLE 테이블명

(

    userID CHAR(8) NOT NULL PRIMARY KEY,  --기본키를 지정하면 인덱스 자동 생성

)

테이블 생성 시 제약 조건에 Primary Key 또는 Unique를 사용하면 자동으로 인덱스 생성

5. 인덱스 제거법

DROP INDEX 인덱스이름;

6. 결론

-인덱스는 열 단위에서 생성된다.

-WHERE 절에서 사용되는 열에 인덱스를 생성해야 한다

-WHERE 절에 사용되더라도 자주 사용해야 가치가 있다

-데이터의 중복도가 높은 열은 인덱스를 만들어도 별 효과가 없다.

-JOIN 에 자주 사용되는 열에는 인덱스를 생성해주는 것이 좋다.

-인덱스는 읽기에만 성능 향상 시키기에 데이터 변경이 자주 일어나는 DB에는 좋지 않다.

- 사용하지 않는 인덱스는 제거하여 용량과 부하를 줄일 수 있다.

오라클 기본 5 - 뷰편

 - 뷰(view)란?

1. 보여주고 싶은 데이터만 보여줌으로 보안에 도움이 됨

2. 복잡한 쿼리를 단순화 시킬 수 있다.

CREATE OR REPLACE VIEW v_userbuyTBL --뷰 테이블 명

AS

  SELECT U.userID, U.userName, B.prodName, U.addr, U.mobile1 || U.mobile2 AS "연락처"

  FROM userTBL U

    INNER JOIN buyTBL B

       ON U.userID = B.userID ;

--위의 쿼리문을 아래와 같이 단순화 가능

SELECT * FROM v_userbuyTBL WHERE userName = '김범수'; 

3. 뷰 활용

DROP VIEW v_userbuyTBL; --뷰 삭제

UPDATE v_userTBL SET addr = '부산' WHERE userID='JKW'; --뷰 수정

INSERT INTO v_userTBL(userID, userName, addr) VALUES('KBM','김병만','충북'); --뷰 입력

4. 뷰 옵션

CREATE MATERIALIZED VIEW 뷰이름

    --뷰 생성 후 동시에 데이터 채워짐 | 나중에 채워짐

    BUILD { IMMEDIATE | DEFERRED } 

    --커밋 발생 때마다 내용 변경 | 직접 DBMS_MVIEW 패키지를 싱행하여 변경   

    REFRESH { ON COMMIT | ON DEMAND } 

--원본 테이블에서 변경된 데이터만 뷰에 업데이트, 원본 데이터가 변경되면 전체를 업데이트, 원본이 변경되어도 업데이트 안함

    { FAST | FORCE |  COMPLETE  | NEVER }

AS

    SELECT SUM(sales) FROM buyTBL;

 

오라클 기본 4 - 테이블편

 1. 기본키 설정

CREATE TABLE userTBL 

( userID  CHAR(8) NOT NULL PRIMARY KEY, --userTBL의 기본키

  userName  NVARCHAR2(10) NOT NULL,

  birthYear NUMBER(4) NOT NULL

);

CREATE TABLE buyTBL 

(  idNum NUMBER(8)  NOT NULL  PRIMARY KEY, --buyTBL 의 기본키

   userID  CHAR(8)  NOT NULL, 

   prodName NCHAR(6)  NOT NULL

);

2. 외래키 설정

CREATE TABLE buyTBL 

(  idNum NUMBER(8)  NOT NULL  PRIMARY KEY,

   userID  CHAR(8)  NOT NULL, 

   , FOREIGN KEY(userID) REFERENCES userTBL(userID) --buyTBL 의 외래키 지정

);

또는

ALTER TABLE buyTBL

ADD CONSTRAINT FK_userTBL_buyTBL --외래키 이름 지정

FOREIGN KEY (userID) --현재  테이블 외래키

REFERENCES userTBL (userID) -- 연결할 기본 테이블 기본키

ON DELETE CASCADE ; --기본 테이블 삭제시 현재 테이블도 삭제

3. UNIQUE 제약 조건 -중복되지 않은 유일한 값이지만 NULL 허용

CREATE TABLE userTBL 

(  userID  CHAR(8) NOT NULL ,

  userName  NVARCHAR2(10) NOT NULL,

  birthYear NUMBER(4) NOT NULL,  

  , CONSTRAINT AK_email UNIQUE (email) --이메일은 유일해야 하지만 null일 수도 있다

);

또는

ALTER TABLE USERTBL

    ADD CONSTRAINT AK_EMAIL UNIQUE (EMAIL);

4. CHECK 제약 조건 - 입력되는 데이터 점검 기능

ALTER TABLE userTbl

ADD CONSTRAINT CK_height

CHECK   (height >= 0) ; -- 키는 0이상이어야 함.

ALTER TABLE userTbl

ADD CONSTRAINT CK_mobile1

CHECK  (mobile1 IN ('010','011','016','017','018','019')); -- 휴대폰 국번 체크

5. DEFAULT 정의 -  값 입력이 없으면 기본으로 입력되는 기본 값  

CREATE TABLE userTBL 

( userID  CHAR(8) NOT NULL PRIMARY KEY ,

  userName  NVARCHAR2(10) NOT NULL ,

  birthYear NUMBER(4) DEFAULT -1 NOT NULL ,  --기본 -1 입력

  addr NCHAR(2) DEFAULT '서울' NOT NULL , --기본 서울 입력

  mobile1 CHAR(3) NULL,

  mobile2 CHAR(8) NULL,

  height NUMBER(3) DEFAULT 170 NULL, --기본 170 입력

  mDate    DATE  NULL 

);

또는

ALTER TABLE userTBL

MODIFY birthYear DEFAULT -1; --기본 -1 입력

ALTER TABLE userTBL

MODIFY addr DEFAULT '서울'; --기본 서울 입력

ALTER TABLE userTBL

MODIFY height DEFAULT 170;  --기본 170 입력

오라클 기본편 3

1. ROLLUP() - 총합 또는 중간 합계가 필요할 때 사용

//이름과 id별로 합계와 총합을 조회

SELECT id, name SUM(a * b) AS "비용" FROM buyTBL

GROUP  BY ROLLUP  (name, id)

2.  GROUPING_ID() - 함수의 결과가 0이면 데이터, 1이면 합계를 위해 추가된 열

SELECT id, name SUM(a * b) AS "비용", GROUPING_ID(name) AS "추가행 여부" 

FROM buyTBL

GROUP  BY ROLLUP  (name, id)

3. WITH절 비재귀적 CTE 구조

WITH abc(id, total)

AS

( SELECT id, SUM(a * b) FROM buyTBL GROUP BY userID )

SELECT * FROM abc ORDER BY total DESC;

4. WITH절 재귀적 CTE 구조

WITH empCTE(empName, mgrName, dept, empLevel)

AS

(

    (SELECT  emp, manager, department, 0 FROM  empTBL WHERE manager = "없음")

    UNION ALL

    (SELECT empTbl.emp,  emmpTbl.manager, empTbl.department, empCTE.empLevenl + 1

      FROM empTbl INNER  JOIN empCTE ON  empTbl.manager = empCTE.empName)

)

SELECT * FROM empCTE ORDER BY dept, empLevel;

오라클 기본편 2

 1. 중복제거 

//주소가 겹치는 사람은 1개씩만 출력

SELECT DISTINCT addr FROM userTBL;

2. ROWNUM 

//결과값의 5개만 출력

SELECT * FROM 

    (SELECT id, data FROM emp ORDER BY date ASC) WHERE ROWNUM <= 5;

3. SAMPLE(퍼센트)

//전체 결과 값 중 5%의 갯수만큼 출력

SELECT id, data FROM emp WHERE SAMPLE(5);

4. 테이블 복사

CREATE TEBLE 새로운 테이블명 AS (SELECT 복사할 열 FROM 기존 테이블명)

5.  GROUP BY 절 - 그룹으로 묶어주는 역할

SELECT userID, amount FROM buyTBL ORDER BY userID; //userID로 그룹화

6. 집계함수

SUM() 합계

AVG() 평균

MAX() 최대값

COUNT()  행의 갯수

COUNT(DISTINCT) 행의 갯수(중복은 1개만 인정)

STDEV() 표준편차

VARIANCE() 분산

CAST() 데이터 변환 함수

7. HAVING 절 - 집계 함수시 조건절

//총 구매액이 1000원 이상인 값만 출력

SELECT userID AS "사용자", SUM(a * b) AS "총 구매액" FROM buyTBL

GROUP BY userID

HAVING   SUM(a * b) > 1000

오라클 기본편 1

이것이 오라클이다. 공부 요약

설치 버전 : Oracle XE 11g R2 버전

툴 : SQL  Developer 

자료실 : https://cafe.naver.com/thisismysql/610

-SQL 기초-

1. 조회

기본 형식 - SELECT 열이름 FROM 테이블이름 WHERE 조건 

관계 연산자 -  1970년 이후 출생, 키가 182 이상의 아이디와 이름 조회

SELECT userID, userName FROM userTBL WHERE birthYear >= 1970 AND height >= 182;

AND 연산자 - 키가 180~183인 사람 조회

SELECT userName, height FROM userTBL WHERE height BETWEEN 180 AND 183;

OR 연산자 - 경남 이거나 전남인 사람 정보 조회

SELECT userName, addr FROM userTBL WHERE addr='경남' OR addr='전남';

IN 연산자 - 경남, 전남을 포함한 정보 조회

SELECT userName, addr FROM userTBL WHERE addr IN('경남', '전남');

LIKE 연산자 - 문자열 검색을 위한 연산자

SELECT userName, height FROM userTBL WHERE userName LIKE '김%'  //김씨 

SELECT userName, height FROM userTBL WHERE userName LIKE '_종신' //이름이 종신

SELECT userName, height FROM userTBL WHERE userName LIKE '_용%' //두번째가 용

2. 서브 쿼리

쿼리문 안에 쿼리문이 있는 형식

SELECT userName, height FROM userTBL WHERE height > 177 이라면

//김경호 키가 177이상이라는 가정

SELECT userName, height FROM userTBL WHERE height > 

    (SELECT height FROM userTBL WHERE userName = '김경호') 

ANY 연산자 - 서브 쿼리문의 여러 결과 중 한 가지만 만족한 결과면 출력

//경남 출신이면서 키가 170보다 크거나 같은 사람이 출력

SELECT userName, height FROM userTBL WHERE height >= 

     ANY (SELECT height FROM userTBL WHERE addr= '경남') 

ALL 연산자 - 서브 쿼리문의 여러개 결과를 모두 만족한 결과만 출력

//경남 출신이면서 키가 170보다 크거나 같은 사람만 출력

SELECT userName, height FROM userTBL WHERE height >= 

     ALL (SELECT height FROM userTBL WHERE addr= '경남') 

3.  원하는 순서대로 정렬하여 출력

 //날짜 오름차순 정렬

SELECT userName, height FROM userTBL ORDER BY mDate;

//날짜 내림차순 정렬

SELECT userName, height FROM userTBL ORDER BY mDate DESC; 

//키가 큰 순서대로 정렬, 키가 같으면 이름순으로 정열 

SELECT userName , height FROM userTBL ORDER BY height DESC, userName ASC; 

초효율 공부법을 읽고

 IT 업계는 항상 공부를 해야만 한다.

하지만 언제나 시간이 부족하다.

그래서 이 책을 구입하고 효율적인 공부법을 배웠다.

내가 보기 위해 요약 정리 한다.

-공부법-

1. 능동 학습 - 전체를 빠르게 읽고, 섞어서 자세히 공부(영어, 수학에 적합)

2. 상기 - 공부 후 스스로 퀴즈를 만들기

3. 분산 학습 - 3가지를 조금씩 나눠서 반복 (영어 -> 수학 -> IT)

4. 청크화 - 의미 있는 것을 묶어서 학습(마인드 맵)

5. 자문 자답 - 주제에 따라 질문 하는 방법을 바꾼다.

6. 티칭 - 공부한 것을 남에게 설명해보기

7. 토론 - 토론한다는 느낌으로 상상하며 공부하기

8. 오감 자극으로 공부(혼잣말, 퀴즈, 타이핑, 쓰기, 그리기, 제스처, 공부 중에도 운동)

9. 부정적 의식 지우기

10. 오버러닝 - 이미 알고 있는 것을 빠르게 반복 학습하고 새로운 지식 습득

-공부 전 준비-

1. 목표 - 자기 초월 목표 세우기(세계 평화 같은 거창한 목표)

2. 준비 - 공부 전 아는 것을 적어보기

3. 호기심 자극, 보상계 자극

4. 배경 음악은 공부를 방해 한다. 

5. 자연은 집중력을 향상 시킨다.

6. 동기부여가 되는 곳에서 공부한다.

-공부 후-

1. 뇌의 집중 모드, 완화 모드를 잘 사용한다.

2. 낮잠을 적극 활용(10~20분), 불가능 하면 눈 감고 뇌의 휴식 시간 활용

3. 공부를 어중간한 곳에서 멈추고 취침 한다.(수면 중에 뇌가 정보 처리)

4. 운동으로 뇌의 기능을 높여라 (공부 전, 후 10분 이상)

5. 생체 리듬에 맞춰 뇌가 피곤을 느끼면 철저하게 쉰다.