DB Oracle 정리 1탄 (오라클 SQL과 PL/SQL Ch1-3)

 

▶ DBMS (Database Management System) : 테이터관리전문프로그램

▶ SQL (Structured Query Language) : 구조화된 쿼리 언어 (규칙있는 말)

 

▶ 실행 : CTRL + ENTER 

▶ 마지막은 세미콜론 (;)

▶ 키워드 = 대문자 /  컬럼, 조건 = 소문자 => 대소문자 구분하기

▶ 한줄주석 = --

▶ 여러줄주석 = /* */

▶ 유저명.테이블명 /다른사용자테이블접근

---

▶SELECT = 데이터를 가져오는 명령어

▶SELECT * = 모든 컬럼 조회

▶SELECT * FROM 테이블명 = 특정 컬럼 조회

▶SELECT 컬럼명, 컬럼명 FROM 테이블명 = 원하는 컬럼만 조회

▶DESC 테이블명 = 테이블에 어떤 컬럼이 있는지 조회

---

▶표현식(리터럴상수(문자), 리터럴): 컬럼 이름 이외에 출력하기 원하는 내용 

▶SELECT 컬럼명 'good' ~ 

---

▶컬럼 별칭 사용하기 (별명에 " " 필수)

1) 컬럼이름 뒤에 공백 주고 별명

2) 컬럼이름 뒤에 As 키워드 사용

 

▶연결(합성)연산자 , || (바기호) : 연결하면 하나의 컬럼으로 인식한다

=문자끼리 더할때 || 사용해야됨

 

▶WITH

= 코드 재사용, 같은 일 반복

= 쿼리양 (코드양)을 줄일 수 있다.

WITH V_DATA AS (
SELECT NATION, VRANCH, NAME, TO_CHAR(REG_DATE, 'YYYY-MM-DD') REG_DATE
FROM ATE ~~~
)

 

WITH V_DATA AS {
SELECT 'SWCHOI' NM FROM DUAL
}

LPAD(NM,9,'0')
FROM V_DATA;

---

▶DISTINCT 컬럼명 FROM 테이블명; 

= 중복값 제거 / 1개의 컬럼에만 적어도 모든 컬럼에 적용된다.

SELECT DISTINCT BBS_NM
FROM FW_BBS;

 

▶NULL = 사칙연산 다 안된다.

 

 

 

---

 

▶WHERE 절 = 조건절 / 원하는 조건만 골라내기

= SELECT 컬럼명 FROM 테이블명 WHERE 원하는 조건;

= 숫자 이외에는 작은 따옴표 필수  (문자,날자 조회는 '' 따옴표 주의하기)

= 조건 입력시 문자는 대소문자구분 

= 그룹함수를 비교조건으로 사용 못함

WHERE COLUMN_NAME = '제목'; //조건을 주면 제목인 아이들을 출력 (제목 컬럼은 출력안함)
WHERE COLUMN_NAME != '제목';  // 반대
WHERE COLUMN_NAME <> '제목';  // 조건의 반대

 

▶WHERE 컬럼명 IS NULL  = 널 값 찾고 싶을 때

▶WHERE 컬럼명 IS NOT NULL = 널 값 아닌 모든 값 찾고 싶을 때

▶WHERE 컬럼명 = &컬럼명;

=사용자에게 값을 입력받기

 

 

---

▶ ==  : 문자 크기 비교 (아스키 값 비교)

 

▶BETWEEN = 특정구간 모든 값 출력 / 구간 데이터 조회하기

= 작은 값의 앞, 큰 값의 뒤  (두 값 포함 결과 출력함)

= (속도 느림)

WHERE sal BETWEEN 200 AND 3000;

▶AND /조건추가

- BETWEEN 보다 속도 빠름

= 조건 반대로 작성하면 실행이 안된다 

WHERE sal >= 2000 AND sal <= 3000;

 

▶OR  = 둘 중 하나의 조건만 만족

=AND가 우선순위 높음, AND는 괄호해줘야됨

 

 

▶IN = 여러조건 간편검색

= 숫자, 글자, 날짜 가능 / 속도 빠름

 

▶LIKE = 비슷한 것들 모두 찾기

= 숫자, 글자, 날짜 가능 / 따옴표로 묶기

= '%' 제한없이 0개~  /' _' 한글자~제한없이

= 이건 조건에서 먼저 사용되면 성능이 최악

WHERE sal LIKE '1%';

 

 

---

▶산술연산자 우선순위

▶날짜 = 클수록 최신 날짜

 

▶INSERT INTO 테이블명(컬럼명1, 컬럼명2) 

▶VALUES ('값1','값2');

---

 

▶ORDER BY = 정렬

= SQL 가장 마지막에 적는다 !!

= 기본값은 ASC(오름차) , DESC(내림차)

= 수행속도가 느려진다, 최대한 사용하지 말기 (인덱스로 대체)

= WHERE 로 조건 정리하고 정렬하는 것은 괜찮다

= 문자는 한글, 소문자, 대문자 (아스키코드 기준) 구분된다.

ORDER BY ename;
ORDER BY 1,2;
--2를 정렬하고 동일한 2는 1로 재정렬
ORDER BY deptno ASC, sal DESC

order by 1 ; 1번꺼로 정렬
order by 1 desc ; 역순으로 정렬

---

▶DUAL

= 자체적으로 제공하는 가상의 테이블

select 'a' as aa;  // 에러가 생김  
새로운 테이블을 만들고싶은데...

select 'a'  as aa
from dual;

 

 

▶집합연산자 4가지

= 컬럼 개수가 동일해야한다

= 데이터형이 동일해야한다

= 이름은 달라도 된다

= 연결이 많아질수록 성능이 떨어진다

 

▶UNION /두 집합을 더하는 연산

= 중복값 제거 O , 정렬 O

= 먼저 붙인게 타이틀이 됨 ( order by 뒤에붙인거 ; 하면 오류가 뜨는거임)

select 'a' from dual
union
select 'b' from dual;

▶UNION ALL  /두 집합을 더하는 연산

= 중복값 제거 X , 정렬 X 

= 먼저 붙인게 타이틀이 됨 ( order by 뒤에붙인거 ; 하면 오류가 뜨는거임)

 

▶INTERSECT / 교집합

= 정렬을 동반해서 속도가 느리다

 

 

 

▶MINUS /차집합

= 큰 집합에서 작은 집합을 뺀다

= 순서에 주의해야한다. (SELECT(큰값) -> MINUS -> SELECT(작은값))

= 정렬을 동반해서 속도가 느리다

 

---

▶함수

1) 단일행함수 = 데이터를 하나씩 처리한다

2) 복수행함수(그룹함수)  = 여러 데이터를 동시에 입력받아 하나의 결과를 만든다

 

 

▶단일행함수

1) 문자함수

▶INITCAP() /대문자변환

= 맨처음 문자가 공백이면 공백을 건너뛰고 대문자로 만든다.

SELECT  INITCAP(' ' || GRORP_CD) AS GROUP_CD

 

▶LOWER /all 소문자

▶UPPER /all 대문자

WHERE LOWER(GROUP_CD) = LOWER('SUPER');
--대소문자 달라도 검색되도록
--들어오는값 무조건 소문자OR 대문자 DB에 들어간것도 무조건 소문자OR대문자

 

▶LENGTH() /문자열길이값

▶LENGTHB() /문자열바이트값

= utf-8 한글한글자당 3바이트

 

▶CONCAT()/ 문자열결합 

= || 연산자와 동일함

 

★▶SUBSTR() /특정문자 추출

=오->왼 , (-) 왼->오 검색 후 오->왼 

= 0=1은 인덱스 시작위치 , 2~끝까지 (이동범위?)

SUbstr ( '', -3); //정적
substr(  , instr (,) 위치 반환)+1) //동적
7→  / 8→
-- 확장자골라내기

▶SUBSTRB() / 특정 바이트만큼 추출

▶INSTR() /특정 글자 위치 추출

= 특정 글자를 N1에서 시작해서 N2번째인지 찾기 (둘다 생략가능, 자동 1)

=시작위치를 -로 주면 오->왼 으로 결괏값 검색함

SELECT 'A-B-C-D', INSTR('A-B-C-D','-',N1,N2)

select 'a-b-c-d', instr('a-b-c-d','-',-3,1) 
from dual; --4

select name, tel, substr(tel,0,instr(tel,')')-1) 지역번호
from student
where deptno1 = 201;

 

▶LPAD() /왼쪽에 채운다

= 10자리를 출력하되 빈자리는 기호로 채운다.

SELECT name, id, LPAD(id,10,'*')

select LPAD(ename,9,12345)
from emp
where deptno = 10;

▶RPAD() /오른쪽에 채운다

select RPAD(ename,9,substrb('123456789',lengthb(ename)+1)) 
from emp
where deptno = 10;

▶TRIM() / 왼오 둘다 제거하기

▶LTRIM() /시작글자 제거하기

SELECT ename, RTRIM(ename,'R') "RTRIM"

▶RTRIM() /마지막글자 제거하기

★▶REPLACE() /문자1을 문자2로 바꾸어서 출력

REPLACE(ename, SUBSTR(ename, 1, 2), '*')
-- PLACE (컬럼,찾을,바꿀)
-- 앞 3부터 뒤로 마스킹


select ename , replace(ename, substr(ename,2,1),'--')
from emp
where deptno = 20;


select jumin ,replace(jumin, substr(jumin,7),'*******')
from student
where deptno1 = 101;

 

2) 숫자함수

▶ROUND() /반올림

=출력 자릿수에 따라 값이 달라짐 (주의!!)

▶TRUNC() /버림

▶MOD() /나머지

▶CEIL() /근접 큰 정수 

▶FLOOR() /근접 작은 정수 

▶POWER() /승수

 

 

 

 

3) 날짜관련함수

▶SYSDATE /현재 시스템 시간 출력

▶MONTHS_BETWEEN / 두날짜 개월 수 출력

= 큰 날짜를 써야 양수가 나옴

 

▶ADD_MONTHS() /숫자만큼 달 추가

▶NEXT_DAY() / 돌아오는 최근 요일날짜 반환

▶LAST_DAY() / 날짜가 속한 마지막날 출력

▶ROUND() / 정오를 넘어서면 다음날짜, 아니면 당일출력

▶TRUNC() /정오를 넘어서면 무조건 당일출력

 

4) 형변환함수

= 묵시적 : 숫자처럼 생긴 문자 (숫자형태를 가지고있어야한다) ex) 'A' 안됨

= 명시적 :

SELECT 2+ TO_NUMBER('2') FROM dual

 

 

▶TO_CHAR

= 자릿수를 알아야함 ?

= '' 작은따옴표로 묶기

= 날짜-> 문자

YYYY RRRR
YY RR YEAR
MM MON MOTH
DD DAY DOTH
HH24 HH MI SS

= 숫자 -> 문자

9 0 $ . ,

 

▶TO_NUMBER()

= 숫자처럼 생긴 문자 -> 숫자

 

▶ASCII()

= 아스키코드로 변환

 

▶TO_DATE

= 날짜처럼 생긴 문자 -> 날짜

 

5) 일반함수

▶NVL() /NULL만나면 다른 값으로 치환

SELECT NVL COLUMN_LENGTH
SELECT NVL(COLUMN_LENGTH, 0) //NULL 값이면 0으로 대체한다.

 

▶NVL2(COL1,COL2,COL3) /NULL아니면 CO2, 맞으면 COL3 치환

=많이 사용하지않음

=하나의 컬럼이되는데 데이터형이 같아야된다 !

=삼항연산

 

 

★▶DECODE() /IF문 사용해야하는 조건문 처리 (6가지) /비교조건 =(같다) 처리

DECODE(A,B,'1',null)
DECODE(A,B,'1','2')
DECODE(A,B,'1',C,'2','3')
DECODE(A,B,DECODE(C,D,',null))
DECODE(A,B,DECODE(C,D,'1','2'))
DECODE(A,B,DECODE(C,D,'1','2')'3')

SELECT deptno, name,DECODE(deptno, 101, 'computer~~') 
from professor;

 

A가 B면 1
A가 B면 1, 아니면 2
A가 B면 1, A가 C면 2, 둘다 아니면 2
A가 B이고, C가 D이면 1, 아니면 NULL
A가 B이고, C가 D이면 1, 아니면 2
A가 B이고, (C가 D이면 1, 아니면2) A가 B가 아니면 3

 

▶CASE / 비교조건 다양하게 처리할때

= 값 처리

= 내부식 콤마 사용 안함 XX (!!!)

CASE SUBSTR(TEL,1,INSTR(TEL,')')-1)

WHEN '02' THEN '서울' --CASE 가 WHEN 이면 서울로 출력한다
WHEN '031' THEN
ELSE '기타' END
-- CASE에 걸리지 않는 애들이면 ELSE


3 AND 3 TEHN '1/4'  --≤3 THEN '1/4' 로 바꿀 수 있다.
4 AND 6 TEHN '2/4'
7 AND 9 TEHN '3/4'
10 AND 12 TEHN '4/4' --ELSE '4/4' 로 바꿀 수 있다.
END QUA

 

6) 정규식

=다양한 조건 조회하기

 

^ $ . * [] [^]

 

▶REGEXP_LIKE /특정패턴 매칭결과

▶REGEXP_REPLACE /특정패턴 치환

▶REGEXP_COUNT /문자개수 찾기

▶REGEXP_SUBSTR /특정문자 추출

 

 

▶그룹행함수

 

1) GROUP 함수 종류

SELECT LOG_CODE ~~~~
FROM
WHERE LOG_CODE = 'BAV';

/*그룹함수사용할때 다른 셀렉트도 표현( 넣을려면 (집계해서 넣기위해서)
GROUP BY LOG_CODE; 라고 적어줘야됨
그룹 바이는 여러개 넣어줄 수 있음*/
성별로 카운트하기 위해?  젠더 그룹핑
연령도 추가하려면 ?   젠더 , 나이 그룹핑

SELECT LOG_CODE, COUNT(LOG_DAY) , SUM(LOG_DAY)
FROM ATBMS2017.AUDIT_ADMIN
WHERE LOG_CODE = 'BAL'
GROUP BY LOG_CODE, LOGMONTH;

 

WITH V_DATA AS (    
SELECT NULL NM, 100 A FROM DUAL UNION ALL
SELECT 'A' NM, NULL A FROM DUAL UNION ALL    
SELECT 'A' NM, 100 A FROM DUAL UNION ALL    
SELECT 'A' NM, 100 A FROM DUAL)
SELECT NVL(NM, 'B') NAME, COUNT(A), SUM(A), AVG(A), AVG(NVL(A, 0))
FROM V_DATAGROUP
BY NVL(NM, 'B')

/* NVL(NM,'B') NAME 
GROUP BY NAME
그룹바이에 넣으면 안됨 ( 실제 컬럼명만 넣어주세요)*/

 

▶COUNT(*) /데이터 총 건수  NULL O

▶COUNT(COMM) /  데이터 총 건수 NULL X 

▶SUM() / 데이터 합계

▶AVG() / 데이터 평균값

= 연산 도중 null 값이 나오면 제외시킨다.

AVG(NVL(A,0))

▶MAX() /데이터 가장 큰 값

▶MIN() /데이터 가장 작은 값

▶STDDEV() /표준편차

▶VARIANCE() /분산

 

▶GROUP BY / 특정 조건으로 세부적인 그룹화

= GROUP BY 에 오는 컬럼을 기준으로 SELECT 를 실행한다.

= 반드시 SELECT 절의 컬럼, 표현식은 GROUP BY 절에 사용되어야한다. (오류)

= GROUP BY 절에 사용된 컬럼은 SELECT 에 사용되지 않아도 된다.

= GROUP BY 절에는 반드시 컬럼명만 사용, Alias 는 사용 금지

 

▶HAVING / 그룹핑한 조건 검색하기

= 그룹함수는 WHERE 절로 비교 조건 쓸 수가 없음

= 위치는 GROUP BY 위 아래 상관없음

 

▶다양한 분석 함수들

 

▶ROLLUP() /소계값 자동 계산

= GROUP BY 확장형태

= 소계의 기준이 되는 컬럼들 수가  N = N+1Level 소계가 생성

= 지정된 컬럼 순서가 바뀌면 결과도 바뀌므로 주의

ROLLUP 그룹 (DEPTNO, JOB) 
기본의 DEPNO 뒤에서부터 잡에대한 서브토탈을 만들어준다

 

 

 

▶CUBE() /소계, 전체총계 자동 계산

= 지정된 컬럼수가 N , 2*N승 소계가 생성됨

 

▶GROUPING SETS() / 그룹핑 조건이 여러개일 경우

= 하나의 테이블에 여러가지 그룹함수가 사용될때

 

 

▶LISTAGG() / 그룹핑 함수 

= ????

 

▶PIVOT() / 단위 변경

= row 단위를 column 단위로

 

▶UNPIVOT() / 단위 역변경 

= column 단위를 row 단위로

 

▶LAG() /이전행 값 가져오기

 

▶LEAD() / 이후의 값 가져오기

 

▶RANK() / 순위 출력

 

▶DENSE_RANK /순위함수

= 동일한 순위를 하나의 건수로 취급함

= 연속된 순위를 보여줌

 

▶ROW_NUMBER() /순위함수

= 동일한 값이라도 고유의 순위를 부여함

 

▶SUM() OVER / 누계 구하기

 

▶RATIO_TO_REPORT() / 판매 비율 구하기

 

▶LAG() / 활용하여 차이 구하기