오라클 기본 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;