StudyServer
web, server, java, spring 등.. 공부한 것을 기록하는 장소 입니다. 공부하면서 정리하였기 때문에 틀린 내용이 있을 수 있습니다. 이야기 해주시면 수정하겠습니다.
HOME /About /algorithmus /db /front /git /java /ml /search /server /spring

Oracle 다중쿼리(Multiple row query)

2018-11-29 00:00:00 +0000

다중쿼리

  • 서브 쿼리의 결과가 여러개인 경우 사용
  • 서브 쿼리의 결과를 하나의 값과 비교할 수 도 있고, 두개 이상도 비교 가능
  • WHERE 컬럼 = (SELECT ~ ) => 단일로우

  • WHERE 컬럼 IN(SELECT ~ ) =>다중로우

  • IN : 검색된 값 중에 하나만 일치하면 참이다
  • ANY : 검색된 값 중에 조건에 맞는 것이 하나 이상 있으면 참

  • ALL : 검색된 값과 조건에 모두 일치해야 참

  • 컬럼 > ALL(서브쿼리) => 컬럼 > MAX : 가장 큰 값 보다 크다.

  • 컬럼 < ALL(서브쿼리) => 컬럼 > MIN: 가장 작은 값 보다 작다.

  • 컬럼 > ANY(서브쿼리) => 컬럼 > MIN : 가장 작은 값 보다 크다.
  • 컬럼 < ANY(서브쿼리) => 컬럼 > MAX: 가장 큰 값 보다 작다.
SELECT 컬럼
FROM 테이블
WHERE 비교대상 (서브쿼리)
  • 두 개 이상의 값을 쿼리로 받아서 비교
SELECT * FROM STUDENT WHERE MAJOR = '화학';
SELECT SNO, SNAME, SYEAR, MAJOR, AVR
FROM STUDENT S
WHERE (AVR,SYEAR) IN (SELECT AVR,SYEAR FROM STUDENT WHERE MAJOR = '화학' )
AND MAJOR != '화학';

FROM절 서브쿼리(Top-N SQL

  • FROM 절에 테이블 이름 대신 서브쿼리 사용
  • 테이블을 질의하기 편하게 수정하면 데이터 찾기가 쉽다
  • 예를 들어 입사를 제일 오래한 사원을 출력 시, 테이블에 입사순서대로 정렬되어있으면 편하다
  • FROM에 서브쿼리로 입사순으로 정렬된 테이블을 가져올 수 있다
서브쿼리 SELECT * FROM 테이블이름 ORDER BY DESC
SELECT A.*
FROM (서브쿼리).A
WHERE 조건
  • 4.5 환산평점이 가장 높은 3명의 학생을 검색하는 예제
SELECT SNO,SNAME,(AVR/4.0 *4.5)  FROM STUDENT ORDER BY 3 DESC;
SELECT ROWNUM, S.*
FROM (SELECT SNO,SNAME,(AVR/4.0 *4.5)  FROM STUDENT ORDER BY AVR DESC)S
WHERE ROWNUM <= 3;

Oracle 인덱스, 뷰, 시퀀스, 트랜잭션, 세션 정리

2018-11-29 00:00:00 +0000

인덱스(INDEX)

  • 데이터를 처음부터 끝까지 찾는 건 Table Full Scan, 인덱스를 통해 검색하는 것은 Index Scan이라고 한다
  • 데이터 양이 많을 때 Full Scan을 하면 검색 속도가 느려지고, 일정하지 않다
  • 기본키(Primary key)또는 고유키(unique key)일 경우 자동으로 생성됨
  • Index Scan은 일정한 검색속도를 제공한다
  • Index 생성 시 오히려 성능이 떨어질 수도 있다(튜닝 참고)
  • 인덱스 생성
CREATE INDEX index_name
ON table_name(column_name)
  • 인덱스 삭제
DROP INDEX 인덱스 이름;

뷰(VIEW)

  • 하나 이상의 테이블을 조회하는 SELECT 문을 저장한 객체
  • 물리적으로 따로 저장하지 않음
  • 쿼리의 복잡도를 낮추고, 보안적인 용도로 사용
뷰 조회 기존 테이블 조회
SELECT * FROM VM_TOP_3 SELECT C.CNO, C.CNAME, S.AVG_RESULT FROM COURSE C, (SELECT CNO, AVG(RESULT) AS AVG_RESULT FROM SCORE GROUP BY CNO ORDER BY AVG(RESULT) DESC)S WHERE C.CNO = S.CN
  • view 생성
CREATE VIEW VIEW_NAME
AS ( 복잡한 SELECT 쿼리)
  • view 삭제
DROP VIEW VW_EMP20;

시퀀스(Sequence)

  • 대리 식별자. 특정 규칙에 맞는 연속 숫자를 생성하는 객체. 값이 커지면서, 고유한 값을 생성한다
  • 시퀀스는 이전 값을 돌아오게 할 수 없다
  • 시퀀스 생성 및 사용
CREATE SEQUENCE Sequence 이름    -- 시퀀스 생성

CREATE SEQUENCE department_id_seq
START WITH 1                -- 어떤 숫자부터 값을 매길 것인지
INCREMENT BY 10;         -- 다음 숫자는 얼마만큼 커질 것인지

INSERT IINTO departments                --  시퀀스 사용
VALUES(department_id_seq.NEXTVAL , 'IT Education', 103, 1400);

DROP SEQUENCE SEQUENCE_NAME   -- 시퀀스 삭제

트랜잭션(Transaction)

  • 실행단위 안에 여러 SQL문이 존재할 수 있다.
  • 실행단위 기준으로 실행 되거나, 실행되지 않아야 한다
  • 트랜잭션을 취소 하고 싶을 때 ROLLBACK 사용
  • 트랜잭션을 영원히 반영하고 싶다면 COMMIT. ROLLBACK되지 않는다
  • 트랜잭션 예제
CREATE TABLE NEW_TABLE(        -- 테이블 생성
NEW_INDEX NUMBER,
INEW_COUNT NUMBER
);

INSERT INTO NEW_TABLE VALUES(1, 10); 
INSERT INTO NEW_TABLE VALUES(2, 10);
COMMIT;                                                      -- 데이터 저장(ROLLBACK으로 못돌림)
INSERT INTO NEW_TABLE VALUES(3, 10); 
INSERT INTO NEW_TABLE VALUES(4, 10);
ROLLBACK;

-- 데이터 3, 4만 존재

세션(Session)

  • 세션은 어떤 활동을 위한 시간이나 기간을 뜻함(게임에서 로그인-로그아웃 기간 같은 것)
  • 세션이 여러개라는 건 DB 접속을 여러명이 했다는 뜻
  • 만약 A세션에서 데이터를 삭제했다면 B세션에는 반영되지 않는다. 반영하려면 COMMIT을 해야한다

Oracle JOIN 정리

2018-11-28 00:00:00 +0000

조인(JOIN)

  • 2개 이상의 테이블에서 데이터를 검색하기 위해서 사용
  • FROM 절에 두 개 이상의 테이블을 명시한다(View, Subquery도 가능)
  • 공통된 컬럼이 없다면, 두 테이블의 공통컬럼을 가진 다른 테이블과 JOIN한 후 목표 테이블과 JOIN
  • 두 테이블의 모든 조합 확인
SELECT * FROM 테이블1, 테이블2;
  • 만약 테이블1, 테이블 2에 각 각 3개의 정보가 있다면 모든 데이터를 조합하므로 총 9개의 데이터가 나옴
  • 조건을 걸어서 데이터에 알맞은 값을 매칭 시켜줘야 함

INNER JOIN 사용법

  • 조건에 부합하는 두 테이블의 데이터를 합치는 방식
  • 컬럼 사용 시 테이블 구분이 필요
SELECT a컬럼명, b.컬럼명 etc... FROM TABLE_NAME  a, TABLE_NAME b
WHERE a.조건컬럼 == b.조건컬럼 (그외 조건이 있다면 추가)
  • INNER JOIN ANSI JOIN
  • WHERE절에 조건이 길어지만 AND가 많이 늘어남
  • 결과는 같은데 표기법이 다름
SELECT a.칼럼명, b.칼럼명 ...
FROM 테이블1 A JOIN테이블2 B
ON 조건
  • 3개 이상의 테이블 JOIN
SELECT a.칼럼명, b.칼럼명 ...
FROM 테이블1 A, 테이블2 B
ON 조건
JOIN 테이블3
ON 조건

SEFE JOIN

  • 자기 자신의 테이블과 합치는 것
  • 찾고자 하는 값이 자신의 테이블에 있을 때 사용
SELECT a.칼럼명, b.칼럼명 ...
FROM 테이블1 A JOIN 테이블1 B
ON A.컬럼 = B.다른컬럼

OUTER JOIN

  • INNER JOIN 사용시 테이블 데이터가 매칭되지 않은 정보는 볼 수 없음
  • 데이터가 매칭 되지 않더라도 한쪽의 테이블을 전부 보고 싶을 때 OUTER JOIN을 사용함
  • 매칭되는 데이터가 없을 때 NULL이 표시
  • +표시는 전체를 보고 싶은쪽 반대편에 붙이도록 함
SELECT A.칼럼, B.칼럼 ..
FROM TALBLE1 A, TABLE2 B
WHERE A.칼럼 = B.칼럼(+)
  • OUTER JOIN ANSI JOIN
  • 전체 데이터를 보고 싶은 쪽을 JOIN에 지정
  • 왼쪽 테이블의 데이터를 전체 출력하고 싶다면 LEFT JOIN, 오른쪽일 경우 RIGHT JOIN
SELECT TABLE1 A, TABLE2 B
FROM TABLE1 A LEFT JOIN TABLE2 B
ON A.컬럼 = B.컬럼

SUB QUERY

  • 예를들어 ‘평균 연봉보다 많은 사람만 출력하시오’란 예제가 있다면 먼저 평균연봉을 구해야한다
  • 이 때 평균연봉을 구한 후 결과를 복사해서 비교하면 2번 실행해야하므로 비효율적이다
  • SUB QUERY를 사용해서 결과값을 구한 후 메인쿼리에서 사용할 수 있다
  • 서브쿼리를 먼저 작성 후 메인쿼리를 작성한다
SELECT TABLE1 칼럼
FROM TABLE1
WHERE 연봉 > (SELECT AVG(연봉) FROM TABLE1)

Oracle DDL, DML 정리

2018-11-27 00:00:00 +0000

테이블 추가

  • 테이블을 복사하여 새로운 테이블 만들 때 CREATE 문 사용
  • ORI_TABLE을 복사하여 NEW_TABLE을 만든다
  • ORI_TABLE의 내용이 그대로 복사된다 
    CREATE TABLE NEW_TABLE 
AS SELECT * FROM ORI_TABLE;
  • 일부 복사 
    CREATE TABLE NEW_TABLE
AS SELECT 복사할 컬럼 FROM ORI_TABLE;
  • 테이블 구조만 복사 
    CREATE TABLE NEW_TABLE
AS SELECT * FROM WHERE 1=0;
  • 컬럼을 새로 설정하는 경우 
    CREATE TABLE NEW_TABLE(
NEW_INDEX NUMBER,
INEW_COUNT NUMBER
);

테이블 변경

  • 이미 생성된 데이터베이스 객체 수정
  • 테이블에 새 열을 추가, 삭제, 길이 변경 등의 일을 할 수 있음
  • 테이블에 열을 추가하는 ADD 
    ALTER TABLE ITEMNAME ADD 컬럼이름 컬럼자료형;
  • 테이블의 열 이름 변경 RENAME 
    ALTER TABLE 테이블NAME RENAME COLUMN 컬럼명 TO 바꿀 컬럼명
  • 열의 자료형 변경 MODIFY
  • DESC TABLE_NAME 으로 데이터형이 수정되었는지 확인할 수 있다 
    ALTER TABLE 테이블NAME MODIFY 컬럼명 수정데이터형
  • 열을 삭제하는 DROP 
    ALTER TABLE NAME DROP COLUMN 컬럼명
  • 테이블 이름을 변경하는 RENAME 
    RENAME TABLE_NAME TO 바꿀 TABLE_NAME
  • 테이블의 데이터를 삭제하는 TRUNCATE
  • TRUNCATE로 삭제한 테이블은 복구할 수 없다 
    TRUNCATE TABLE TABLE_NANE
  • 테이블의 데이터를 삭제하는 DROP
  • DROP으로 삭제한 테이블은 복구할 수 있다 
    DROP TABLE TABLENAME;

테이블 데이터 처리

  • 테이블에 데이터 추가 시 INSERT문 사용 
    INSERT INTO ITEM VALUES(VALUE,  VALUE); // 순서대로 값이 들어갈 때
INSERT INTO NEW_ITEM(NEW_COUNT, NEW_INDEX)  VALUES(VALUE, VALUE); // 순서를 지정하고 싶을 때
  • 테이블의 데이터 수정 시 UPDATE문 사용
  • ROLLBACK; 명령문으로 이전 데이터로 돌아갈 수 있다 
    UPDATE TABLE_NAME SET 컬럼=VALUE WHERE 조건...
  • 테이블에 들어있는 데이터 삭제 시 DELETE 문 사용 
    DELETE FROM TABLE_NAME WHERE 조건

Oracle 문자열 함수 정리

2018-11-27 00:00:00 +0000

오라클 함수

  • Oracle 함수란 특정한 결과 값을 얻기 위해 데이터를 입력할 수 있는 특수 명령어

DUAL 테이블이란?

  • Oracle의 최고 권한 관리자 계정인 SYS 소유의 테이블
  • 임시 연산, 함수의 결과값을 확인할 때 주로 사용

문자 함수

  • UPEER(문자열)
  • 문자열을 모두 대문자로 변경
  • SELECT UPPER(‘AbcdEF’) FROM DUAL;
  • 결과 ABCDEF
  • LOWER(문자열)
  • 문자열을 모두 소문자로 변경
  • SELECT LOWER(‘AbcdEF’) FROM DUAL;
  • 결과 abcdef
  • INITCAP(문자열)
  • 첫글자를 대문자로. 나머지는 소문자로 변경
  • SELECT INITCAP(‘AbcdEF’) FROM DUAL;

  • 결과 Abcdef

  • 특정 단어를 찾으려고 하는데, 찾을 대상들이 형식이 다를 때(‘ABcd’, ‘aBcd’) 일원화하여 쉽게 찾을 수 있다

  • LENGTH(문자열)
  • 문자열의 길이를 구한다
  • SELECT LENGTH(‘가나다라’) FROM DUAL;
  • 결과 4
  • LENGTHB(문자열)
  • 문자열의 길이를 바이트 수로 반환한다
  • SELECT LENGTHB(‘가나다라’) FROM DUAL;
  • 결과 12

  • LENGTH는 문자열 갯수를 반환하지만, LENGTHB는 바이트 수를 반환한다

  • SUBSTR(문자열, 시작위치, 추출길이)
  • 문자열을 시작위치부터 추출길이만큼 구한다. 시작위치는 1부터 시작
  • SELECT SUBSTR(‘가나다라’,2) FROM DUAL;
  • 결과 나다라
  • 추출길이를 지정하지 않으면 문자 끝까지 추출
  • 시작위치에 음수값을 넣으면 끝에서 부터 추출

  • 가(-4) 나(-3) 다(-2) 라(-1), 시작위치에 -1을 넣으면 ‘라’가 출력됩니다

  • INSTR(대상 문자열 데이터(필수),위치를 찾으려는 부분 문자(필수),위치 찾기를 시작할 대상 문자열 데이터 위치,시작 위치에서 찾으려는 문자가 몇번째인지 지정)
  • 문자열안에서 특정 문자를 찾는다
  • SELECT INSTR(‘가나다라’, ‘나다’) FROM DUAL;

  • 결과 2

  • REPLACE(문자열 데이터 또는 열 이름(필수),찾는 문자(필수),대체할 문자)
  • 문자를 다른 문자로 바꾼다
  • SELECT REPLACE(‘010-1111-2222’, ‘-‘) FROM DUAL;
  • 01011112222

  • 만약 대체할 문자를 지정하지 않는다면 찾는 문자는 삭제 된다

  • LPAD(문자열 또는 열이름(필수),데이터 자릿수(필수),빈공간에 채울 문자)
  • 빈 공간이 있다면 왼쪽에 특정 문자로 채움
  • SELECT RPAD(‘201204-‘, 14, ‘*’) FROM DUAL;
  • 결과 201204-***

  • RPAD는 사용법은 같고, 오른쪽을 특정 문자로 채움

  • CONCAT(문자열,문자열)
  • 두 문자열을 합침
  • SELECT CONCAT(‘AB’,’CD’) FROM DUAL;

  • ABCD

  • TRIM(삭제옵션, 삭제할 문자) FROM 원본문자열 데이터(필수)
  • 특정 문자 삭제
  • SELECT TRIM(‘A’ FROM ‘ABCDEFG’) FROM DUAL;
  • BCDEFG

Posts

subscribe via RSS