오라클 스터디

1. COUNT 함수

입력되는 데이터의 총 건수를 반환

SELECT COUNT (*) , COUNT(hpage)

FROM professor ;

COUNT(*) = NULL값 포함 결과

COUNT(hpage) = NULL값 제외한 결과 

2. SUM 함수

입력된 데이터들의 합계값을 구하는 함수

3. AVG 함수

입력된 데이터들의 평균값을 구하는 함수

※ 전체 평균을 구할 때 NULL 값이 있을 수도 있기에 NVL함수를 응용해서 구해줌

예) 전체 인원 16명, 보너스 받는인원 10명, 그대로 평균을 구해주면 보너스 총합에서 보너스 받는 인원만 나눠주기에

정확한 결과가 나오지 않는다. 그래서 NVL을 이용해서 보너스 받지않는 인원은 0 으로 해서 평균을 구해준다.

4. MAX / MIN 함수

MAX 함수 = 주어진 데이터 중에서 가장 큰 값을 돌려줌

MIN 함수 = 주어진 데이터 중에서 가장 작은 값을 돌려줌

SELECT MAX(SAL), MIN(SAL)                            SELECT MAX(hiredater), MIN(hiredate)

FROM emp;                                                     FROM emp;

MAX(SAL)   MIN(SAL)                                        MAX(HIREDATE)    MIN(HIREDATE)

--------  --------                                        -------------    -------------

       5000          800                                            23-MAY-87         17-DEC-80

날짜의 경우 최근 날짜가 크고 이전 날짜가 작다.

5. STDDEV 함수 / VARIANCE 함수

STDDEV 함수 = 표준편차 구하는 함수

VARIANCE 함수 = 분산 구하는 함수

특정 조건으로 세부적인 그룹화 하기 (GROUP BY 절 사용)

GROUP BY 절 사용시 주의사항

1. SELECT 절에 사용된 그룹함수 이외의 컬럼이나 표현식은 반드시 GROUP BY 절에 사용되어야 한다.

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

3. GROUP BY 절에는 반드시 컬럼명이 사용되어야 하며 컬럼 Alias 는 사용하면 안된다.

조건을 주고 검색하기 (HAVING 절 사용)

그룹 함수를 비교 조건으로 사용하고 싶은 경우에 WHERE 절 대신 HAVING 절을 사용

자동으로 소계 / 합계를 구해주는 함수

1. ROLLUP 함수

ROLLUP 함수는 주어진 데이터들의 소계를 구해줌

       deptno ,position 컬럼으로 ROLLUP 한 경우

       deptno 컬럼으로 ROLLUP 한 경우                          position 컬럼으로 ROLLUP 한 경우

2. CUBE 함수

ROLLUP 함수와 같이 각 소계도 출력하고 전체 총계까지 출력

     deptno,position 컬럼으로 ROLLUP 한 경우

실전 그룹핑 관련 함수

1. GROUPING SETS 

그룹핑 조건이 여러 개 일 경우 유용하게 사용됨

  기존 방법은 따로 구한뒤 UNION 으로 묶었지만 GROUPING SETS 함수를 사용한 경우 간결하게 쿼리를 작성가능

2. LISTAGG 함수

출력 결과를 가로로 나열

LISTAGG(나열하고싶은 컬럼명,'데이터 구분할 문자') WITHIN GROUP(ORDER BY 가로로나열하고싶은 규칙)

LISTAGG(name,'**') WITHIN GROUP(ORDER BY hiredate) 

3.LAG 함수

이전 행 값을 가져 올 때 사용하는 함수

-문법:

LAG(출력할 컬럼명 , OFFSET , 기본 출력값)

 OVER(Query_partition구문 , ORDER BY 정렬할 컬럼)

4. LEAD함수

LAG 함수와 반대로 이후의 값을 가져오는 함수. LAG 함수와 방법은 동일하나 OFFSET 값이 마지막에 보인다.

5. RANK 함수 - 순위 출력 함수

집계용 - 단일조건 순위 출력

분석용 - 전체조건에서 순위 출력

집계용 문법 

-RANK(조건값) WITHIN GROUP(ORDER BY 조건값 컬럼명 [ASC|DESC])

예 ) 이름이 '송도권' 인 교수의 순위를 조회

분석용 문법 : RANK() 뒤가 WITHIN GROUP 에서 OVER 로 바뀜

-RANK() OVER(ORDER BY 조건컬럼명 [ASC|DESC]

6. SUM( ) OVER 를 활용한 누계 구하기

7. 판매 비율 구하기

RATIO_TO_REPORT 라는 함수를 사용하여 비율을 구할 수 있다.

예 ) panmae 테이블에서 100번 제품의 판매 내역과 각 판매점 별로 판매 비중을 구해보자

8. LAG 함수를 활용한 차이 구하기

정규식 

정규 표현식

1. REGEXP_LIKE

like 함수처럼 특정 패턴과 매칭되는 결과를 검색하는 함수

사용 예 1.)

소문자 영문자가 들어있는 행 출력  - SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'[a-z]') ;

대문자 영문자가 들어있는 행 출력  - SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'[A-Z]') ;

대소문자 영문자가 들어있는 행 출력 - SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'[a-zA-Z]') ; 

사용 예 2.)

소문자로 시작하고 뒤에 공백이 있는 모든 행 출력 

- SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'[a-z]  ') ;

소문자로 시작하고 공백이 1칸 있고 숫자로 끝나는 행 출력

- SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'[a-z] [0-9]') ;

공백이 있는 모든 데이터를 찾고 싶은 경우

- SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'[[:SPACE:]]') ;

사용 예 3.)

대문자가 연속적으로 2글자 이상오는 경우 출력

- SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'[A-Z]{2}') ;

대문자가 연속적으로 3글자 이상오는 경우 출력

- SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'[A-Z]{3}') ;

사용 예 4.)

시작 문자 지정 ^(캐럿) , 끝나는 문자 지정 $(달러)

시작을 대문자나 소문자로 하는 행 출력

- SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'^[a-zA-Z]') ;

시작을 숫자나 대문자로 시작하는 행 출력

- SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'^[0-9A-Z]') ;

여러가지 조건을 줄 경우 바 기호(|)  를 사용하여 연결 할 수도 있음

소문자로 시작하거나, 숫자로 시작하는 경우

- SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'^[a-z] | ^[0-9]') ; 

STUDENT 테이블에서 학생의 ID중 첫 글자가 s(소문자) 로 시작하고 두번째 글자가 a 나 t가 나오는 id 출력

- SELECT name, id FROM STUDENT WHERE REGEXP_LIKE( id,'^s(a|t).') ;

소문자로 끝나는 모든 행 출력

- SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'[a-z]$') ;

^(캐럿)문자가 대괄호 안에 들어갈 경우에는 대괄호 안의 문자가 아닌 다른 것만 출력하라는 의미

소문자로 시작하지 않는 행을 모두 출력

- SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'^[^a-z]') ;

STUDENT 테이블에서 학생의 id를 조사해 4번째 자리에 v(소문자) 가 있는 행을 출력

- SELECT name, id FROM STUDENT WHERE REGEXP_LIKE( id,'^...v.') ;

특정 조건을 제외한 결과 출력  (NOT)

영문자(대소문자)를 포함하지 않는 행을 출력

- SELECT * FROM reg_test WHERE NOT REGEXP_LIKE( text,'[A-Za-z]') ;

특수문자 찾기 

( '*' 나 '?' 같은 기호는 SQL에서 '모든것' 이라는 뜻을 가진 메타캐릭터 문자이기 때문에 \붙여줘야 한다.)

?가 들어간 행 출력

- SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'\?') ;

?가 들어가지 않는 행 출력

- SELECT * FROM reg_test WHERE NOT REGEXP_LIKE( text,'\?') ;

소문자가 들어 있는 모든 행을 출력 (찾고자 하는 쿼리의 앞에 '*' 나 '?' 를 사용 뒤에 쓰면 적용X)

- SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'*[a-z]') ;

- SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'?[a-z]') ;

2. REGEXP_REPLACE 함수

REPLACE 함수의 확장 개념으로 주어진 문자열에서 특정 패턴을 찾아서 다른 모양으로 치환하는 함수

사용 예1.) 모든 숫자를 특수 기호로 변경

숫자 부분을 '@' 기호로 변경

- SELECT text , REGEXP_REPLACE( text,'[[:digit:]]','@')  FROM reg_test ;

사용 예2.) 특정 패턴을 찾아서 패턴을 변경

숫자를 찾아서 숫자 뒤에 '-*' 을 추가

- SELECT text , REGEXP_REPLACE( text,'[0-9]','\1-*')  FROM reg_test ;

reg_test2 테이블에서 ip의 .(dot) 부분을 모두 삭제하고 출력

- SELECT no, ip, REGEXP_REPLACE(ip,'\.','') FROM reg_test2 ;

reg_test2 테이블에서 ip의 첫번째 .(dot) 부분을 '/' (슬래쉬) 기호로 변경해서 출력

- SELECT no, ip, REGEXP_REPLACE(ip,'\.','/',1,1) FROM reg_test2 ;

사용 예3.) 사용자에게 입력 받은 문자가운데 공백이 여러 개 들어있을 경우 그 공백을 제거 하는 방법 (중요)

사용자가 ID를 'abc 123' 이렇게 입력했을 경우 'abc' 와 '123' 사이의 공백을 없애고 싶은 경우

- SELECT REGEXP_REPLACE('abc  123','( ){1,}','') FROM dual ;

위 예제에서 {1,} 부분을 {1}로 해도 무방

{} 내의 숫자는 앞문자가 나타나는 횟수 또는 범위를 의미

예를 들어 a{5} 'a' 의 5번 반복인 aaaaa

              a{3,} 는 'a'가 3번 이상 반복인 aaa , aaaa , aaaaa ... 등을 의미

              a{3,5} 는 aaa , aaaa , aaaaa 를 의미

              ab{2,3} 은 뒤의 b가 2번, 3번 반복된 형태로 abb , abbb 를 의미

              {,}를 붙이면 이상을 의미

사용 예4.) 사용자가 검색어를 입력할 때 공백 문자를 가장 먼저 입력하고 아이디 중간에도 공백이 있어서 모든 공백을 제거하는 예

아이디 입력시 : (공백)  75   true 를 입력하였을때 중간 중간 공백을 모두 제거하는 방법

- SELECT studno,name,id FROM student WHERE id=REGEXP_REPLACE('&id','( ){1,}','') ;

 Enter Value for id :         75       true

3. REGEXP_INSTR 함수

특정 패턴이 출현하는 첫 위치 값을 반환하는 함수

사용 예1.) 특정 문자의 위치를 찾는 방법

text 중에서 '*' 의 위치를 찾는 방법

- SELECT text, REGEXP_INSTR(text,'\*') FROM reg_test ;

4. REGEXP_SUBSTR 함수

SUBSTR 함수의 확장판으로 특정 패턴에서 주어진 문자를 추출해 내는 함수

'abc* *def %ghi,jkl' 이란 문자열에서 첫 글자가 공백이 아니고 ('[^ ]') 그 후에 'def'가 나오는 부분을 추출

- SELECT REGEXP_SUBSTR('abc* *def %ghi,jkl' , '[^ ]+[def]') FROM dual ;

5. REGEXP_COUNT 함수

특정 문자의 개수를 세는 함수

주어진 문자열에서 소문자 'a'가 몇개인지 찾아 주는 예

- SELECT text, REGEXP_COUNT(text,'a') FROM reg_test ;

1. NVL 함수  

NULL 값을 만나면 다른 값으로 치환해서 출력하는 함수

NVL(칼럼, 치환할 값)

※치환 값이  숫자일 경우

NVL(sal, 0) -> sal 칼럼의 값이 null 일 경우 null 대신 0 으로 치환

NVL(sal, 100) -> sal 칼럼의 값이 null 일 경우 null 대신 0 으로 치환

※치환 값이 문자일 경우

NVL(position, '사원') -> position 값이 null 일 경우 '사원'으로 치환

※치환 값이 날짜일 경우

NVL(hiredate, '2014/05/01') -> hiredate 값이 null 일 경우 '2014/05/01' 날짜로 치환

NVL 함수 퀴즈

Professor 테이블에서 201번 학과 교수들의 이름과 급여, bonus, 총연봉을 아래와 같이 출력

단 총연봉은 (pay*12+bonus)로 계산하고 bonus 가 없는 교수는 0으로 계산

SELECT profno, name, pay, bonus, TO_CHAR(pay*12+NVL(bonus,0)) "총연봉" 

2 FROM professor 

3 WHERE deptno=201

NVL2 함수

NVL 함수의 확장판으로 NULL 값이 아닐 경우 출력할 값을 지정할 수 있음

NVL2( COL1, COL2, COL3)

= COL1 의 값이 NULL 이 아니면 COL2를, NULL 이면 COL3 을 출력

NVL2 함수 퀴즈

아래 화면과 같이 emp 테이블에서 deptno 가 30 번인 사원들을 조회하여 comm 값이 있을 경우 '상여금 있음' 을 출력하고 comm 값이 null 일 경우 '상여금 없음' 을 출력

SELECT empno,ename,comm,NVL2(comm,'상여금 있음','상여금 모름') "NVL2"

2 FROM emp

3 WHERE deptno=30

3. DECODE 함수

유형 1. A 가 B 일 경우 '1' 을 출력

-DECODE( A, B, '1', null) (단, 마지막 null은 생략 가능)

유형 2. A 가 B 일 경우 '1' 을 출력하고 아닐 경우 '2' 를 출력

-DECODE( A, B, '1', '2')

유형 3. A 가 B 일 경우 '1' 을 출력하고 A 가 C 일 경우 '2' 를 출력하고 둘 다 아닐 경우 '3' 을 출력

-DECODE ( A, B, '1', C, '2', '3' )

유형 4. A 가 B 일 경우 중에서 C 가 D 를 만족하면 '1' 을 출력하고 C 가 D 가 아닐 경우 NULL 을 출력

-DECODE ( A, B, DECODE( C, D, '1', null) )   null은 생략 가능

유형 5. A 가 B 일 경우 중 C 가 D 를 만족하면 '1'을 출력 C 가 D가 아닐 경우 '2' 를 출력

-DECODE ( A, B, DECODE( C, D, '1', '2,'))

답> SELECT name,jumin,DECODE(substr(jumin,7,1),1,' 남자','여자 ') "성별"

2  FROM student

3  WHERE deptno1=101 ;

답> SELECT name "이름",tel "연락처",DECODE(substr(tel,1,instr(tel,')')-1),02,'서울',031,'경기',051,'부산',052,'울산',055,'경남') "지역"

2 FROM student

3 WHERE deptno1=101

4. CASE 문

CASE 조건 WHEN 결과1 THEN 출력1

                [WHEN 결과2 THEN 출력2]

     ELSE 출력3

END " 칼럼명"

※DECODE 함수의 경우 함수 내부에 , 로 조건들이 구분 되었으니 CASE 표현식 내부는 콤마가 사용되지 않음

답> SELECT empno, ename, sal,

CASE WHEN (sal) between 1 and 1000 then 'level 1'

when (sal) between 1001 and 2000 then 'level 2'

when (sal) between 2001 and 3000 then 'level 3'

when (sal) between 3001 abd 4000 then 'level 4'

else 'level 5'

END "LEVEL"

from emp

order by sal desc;

char : 

고정길이

최대 2000byte

varchar2 

가변길이 

      최대 4000byte

number (p,s)

  p는 전체 자리수로 1-38가지 가능

  s는 소수점 이하 자리수로 -84 ~ 127

  123.45 = NUMBER(5,2)

날짜 - date

1. 묵시적(자동) 형 변환과 명시적(수동) 형 변환

묵시적 - 오라클이 자동으로 형 변환을 시킴

SELECT 2+'2' FROM dual ;

 = '2' 문자를 숫자로 자동으로 형 변환해 계산함

명시적 - 사용자가 수동으로 지정해 주어야 함

2. TO_CHAR 함수 (날짜 -> 문자로 형 변환)

년도 

YYYY - 연도를 4자리로 표현

RRRR - 2000년 이후 Y2K 버그로 인해 등장한 날짜 표기법, 4자리 연도 표기

YY - 연도를 끝에 2자리만 표시 예 : 14

RR - 연도를 마지막 2자리만 표시 예 : 14

YEAR - 연도의 영문 이름 전체를 표시

월

MM - 월을 숫자 2자리로 표현 예 : 10

MON - 유닉스용 오라클에서 월을 뜻하는 영어 3글자로 표시 예 : OCT

MONTH - 월을 뜻하는 이름 전체를 표시

일

DD - 일을 숫자 2자리로 표시 예 : 26

DAY - 요일에 해당하는 명칭을 표시 유닉스=영문 , 윈도우=한글

DDTH - 몇 번째 날인지 표시

시간 

HH24 - 하루를 24시간으로 표시

HH - 하루를 12시간으로 표시

MI - 분 표시

SS - 초 표시

형 변환 함수 퀴즈 - 날짜 변환하기 1

Student 테이블의 bitrthday 칼럼을 사용하여 생일이 1월인 학생의 이름과 birthday 를 아래 화면과 같이 출력

SELECT studno,name,birthday

2  FROM student

3  WHERE TO_CHAR(birthday,'mm')=1;

형 변환 함수 퀴즈 - 날짜 변환하기 2

emp 테이블의 hiredate 칼럼을 사용하여 입사일이 1,2,3 월인 사람들의 사번과 이름, 입사일을 출력

SELECT empno,ename,hiredate

2  FROM emp

3  where TO_CHAR(hiredate,'mm') IN (1,2,3) ;

3. TO_CHAR 함수 (숫자형 -> 문자형으로 변환하기)

예1. emp 테이블을 조회하여 이름이 'ALLEN' 인 사원의 사번과 이름과 연봉을 출력하세요.

단 연봉은 (sal*12)+comm 로 계산하고 천 단위 구분기호로 표시하세요

예2. professor 테이블을 조회하여 201 번 학과에 근무하는 교수들의 이름과 급여, 보너스, 연봉을 출력

단 연봉은 (pay12)+bonus로 계산

형변환 함수 퀴즈 3

emp 테이블을 조회하여 comm 값을 가지고 있는 사람들의 empno, ename, hiredate, 총연봉, 15% 인상 후 연봉을 아래 화면처럼 출력, 단 총 연봉은 (sal*12)+comm 으로 계산. 15%인상은 총연봉의 15%인상 값 

SELECT empno,ename,to_char(sysdate,'yy-mmdd') "HIREDATE",

2 TO_CHAR((sal*12)+comm,'$999,999') "SAL" ,

3 TO_CHAR(((sal*12)+comm)*0.15+(sal*12)+comm,'$999,999') "15% up"

4 FROM emp

5 WHERE comm is not null ;

4. TO_NUMBER 함수

숫자가 아닌 숫자처럼 생긴 문자를 숫자로 바꾸어 주는 함수

SELECT TO_NUMBER('5') FROM dual; = 5

5. TO_DATE 함수

날짜가 아닌 날짜처럼 생긴 문자를 날짜로 바꾸어 주는 함수

SELECT TO_DATE ('14-MAR-10') FROM dual ; = 14-MAR-10

1. SYSDATE 함수

현재 시스템의 시간을 출력해 주는 함수

SELECT SYSDATE FROM dual ;      미국 기준으로 시간이 출력됨

alter session set NLS_DATE_FORMAT='YYYY-MM-DD:HH24:MI:SS'; 해주면 한국에서 익숙하게 표기

2. MONTHS_BETWEEN 함수

두 날짜를 입력 받아서 두 날짜 사이의 개월 수를 출력하는 함수

SELECT MONTHS_BETWEEN('14/05/31','14/04/30')

FROM dual;

= 1

1) 두 날짜 중 큰 날짜를 먼저 써야 양수가 나옴

2) 두 날짜가 같은 달에 속해 있으면 특정 규칙으로 계산된 값이 나옴

※ 윤달을 구분하지 못함, 리눅스에서 사용할 때는 NLS_DATE_FORMAT 을 이용해 날짜 형태 변경

3. ADD_MONTHS 함수

주어진 날짜에 숫자만큼의 달을 추가 하는 함수

- SYSDATYE,ADD_MONTHS(SYSDATE,1)

FROM dual ;

SYSDATE   ADD_MONT

--------  ----------

14/05/26     14/06/26

4. NEXT_DAY  함수

주어진 날짜를 기준으로 돌아오는 가장 최근 요일의 날짜를 반환해주는 함수

SELECT SYSDATE , NEXT_DAY(SYSDATE,'MON') NEXT_DAY

FROM dual ;

SYSDATE           NEXT_DAY

---------        ---------

28-APR-11        02-MAY-11

5. LAST_DAY 함수

주어진 날짜가 속한 달의 가장 마지막 날을 출력해주는 함수

SELECT SYSDATE , LAST_DAY(SYSDATE) "LAST_DAY"

FROM dual ;

SYSDATE        LAST_DAY

--------       ---------

28-APR-11     30-APR-11

6. 날짜의 ROUND, TRUNC 함수

ROUND - 하루의 절반에 해당 되는 낮 12:00 를 기준으로 12:00 지나면 다음 날짜로 출력 이 시간이 안될 경우 당일로 출력

TRUNC - 무조건 당일 출력

1. ROUND 함수 (반올림)

ROUND(숫자 , 출력을 원하는 자리수) 

- ROUND(123.456,2) = 123.46

- ROUND(123.456,-1) = 120

- ROUND(123.456,0) = 123

2. TRUNC 함수 (버림)

TRUNC(숫자, 원하는 자리수)

-TRUNC(123.456,2) = 123.45

-TRUNC(123.456,-1) = 120

-TRUNC(123.456,0) = 123

3. MOD , CEIL, FLOOR 함수

MOD - 나머지 값을 구하는 함수

CEIL - 주어진 숫자가 가장 가까운 큰 정수를 구하는 함수

FLOOR - 주어진 함수와 가장 가까운 작은 정수를 구하는 함수

-MOD(!24,3) = 1      = 124/3  .... 1   따라서 1만 보임

-CEIL(123.456) = 124

-FLOOR(123.456) = 123

CEIL 함수의 다른 사용법

12건의 데이터를 3줄씩 나누어 한조로 만드는 예

rownum 은 출력될 때 줄 번호

4. POWER 함수

숫자 1의 숫자 2의 승수를 구해주는 함수

POWER( 숫자1, 숫자2)

SELECT POWER(2,3) FROM dual ;  = 8        2의 3승

단일행 함수 - 여러 건의 데이터를 한번에 하나씩 처리하는 함수

-문자 함수

-숫자 함수

-날짜 함수

-변환 함수

-묵시적 데이터형 변환

-명시적 데이터형 변환 

-일반 함수

문자 함수

1. INITCAP 함수

영어에서 첫 글자만 대문자로 출력하고 나머지는 전부 소문자로 출력하는 함수

INITCAP(문자열 or 칼럼명)

2. LOWER 함수

입력되는 값을 전부 소문자로 변경하여 출력

LOWER(문자열 or 칼럼명)

3. UPPER 함수

입력되는 값을 전부 대문자로 변경하여 출력

UPPER(문자열 or 칼럼명)

4. LENGTH / LENGTHB 함수

입력된 문자열의 길이를 (바이트수를) 계산해주는 함수

5. CONCAT 함수 (|| 연산자와 동일한 기능)

CONCAT('문자열1','문자열2')

6. SUBSTR 함수 (아주 중요)

주어진 문자열에서 특정길이의 문자를 골라낼 때 사용하는 함수

2번 칼럼과 3번 칼럼을 보면 시작 위치의 숫자를 -(마이너스)로 주었는데 이럴경우 뒤에서 부터(오른쪽) 자릿수를 계산해서 문자를 추출

ex) Student 테이블에서 jumin 칼럼을 사용해서 1 전공이 101번인 학생들의 이름과 생년월일 , 생일 하루전 날짜를 출력하세요

SCOTT>SELECT name,SUBSTR(jumin,3,4) "생일",SUBSTR(jumin,3,4)-1 "생일 1일전"

        2  FROM student

        3  WHERE deptno1=101 ;

7. SUBSTRB 함수

SUBSTR 함수와 동일하지만 추출할 자리수가 아니라 추출 할 바이트 수를 지정

8. INSTR 함수

이 함수는 주어진 문자열이나 칼럼에서 특정 글자의 위치를 찾아주는 함수

INSTR('문자열' 또는 칼럼, 찾는 글자, 시작위치, 몇 번째인지(기본 값 1))

9. LPAD 함수

원래 데이터를 두고 왼쪽에 빈자리가 있을 경우 왼쪽을 특정 기호나 문자로 채우라는 뜻

LPAD('문자열' 또는 칼럼명, 자리수, '채울문자')

※LPAD 퀴즈

emp 테이블을 사용하여 deptno가 10번인 사원들의 사원이름을 총 9바이트로 출력하되 빈 자리에는 해당 자리의 숫자로 채우시오

10. RPAD 함수

원래 데이터를 두고 오른쪽에 빈자리가 있을 경우 오른쪽을 특정 기호나 문자로 채우라는 뜻

RPAD('문자열' 또는 칼럼명, 자리수, '채울문자')

※RPAD 퀴즈

emp테이블에서 deptno가 10번인 사원들의 이름을 총 9자리로 출력하되 오른쪽 빈자리에는 해당 자리 수에 해당되는 숫자가 출력되도록 하시오

11. LTRIM 함수

LPRD,RPAD 와 반대로 LTRIM,RTRIM 함수는 제거하는 함수

LTRIM('문자열'or 칼럼명 , '제거할 문자')

12. RTRIM 함수

RTRIM('문자열'or 칼럼명 , '제거할 문자')

13. REPLACE 함수

주어진 첫 번째 문자열이나 칼럼에서 문자1 을 문자2로 바꾸어 출력하는 함수

REPLACE('문자열'or 칼럼명, '문자1','문자2')

 REPLACE 퀴즈 1

professor 테이블에서 102 번 학과에 소속된 교수들의 이름을 가운데 이름만 '#'으로 변경해서 출력

student 테이블에서 1 전공이 101번인 학생들의 이름과 주민등록번호를 출력하되 주민등록번호의 뒤 7자리는 '*'로 표시되게 출력 

student 테이블에서 1전공이 102번인 학생들의 이름과 전화번호, 전화번호에서 국번 부분만 '*' 처리하여 출력

student 테이블에서 detptno1 이 101번인 학과 학생들의 이름과 전화번호, 전화번호에서 지역번호와 국번을 제외한 나머지 번호를 '*'로 표시해서 출력

계정 → 관리자 : sys, system
       ↘

            일반용 : scott , HR 
                          -> sample schema 설치해야 생김 (연습용 자료)

리눅스 -> $ sqlplus scott/tiger
          프로그램이름  계정  암호

오라클 -> sql> Conn scott/tiger

/home/oracle 디렉토리에 test_data.sql 넣고 scott 계정으로 로그인

sql에 적용방법 : scott> @/home/oracle/test_data.sql

sql로그인 하기

$ sqlplus scott/tiger

scott>

 -데이터베이스가 종료되어 있어서 연습용 계정으로 접속 안되고 에러 나는 경우

sys/oracle as sysdba 로 관리자 계정으로 접속후 startup으로 db시작

그 후 연습용 계정으로 로그인 sql> conn scott/tiger

현재 접속해 있는 계정 확인 = show user

set sqlprompt "_user>" 명령어 치면 프롬포트 모양이 계정이름으로 변경됨

로그인 할때 ORA-28000: the account is locked 라는 메세지가 나오면

관리자 계정으로 접속해 락 해제해주고 연습용 계정으로 로그인

sql> alter user scott identified by tiger account unlock ;

sql> conn scott/tiger

오라클 Table

행 - column

열 - row

/ -> 제일 마지막으로 쳤던 쿼리 입력

칼럼 길이 조정 하는 방법

데이터가 숫자일 경우

COL empno FOR 9999 : empno 라는 칼럼의 길이를 숫자 4자리까지 들어가게 설정

  (한자리당 9 하나씩)

데이터가 문자일 경우

COL ename FOR a8 : ename 라는 칼럼의 길이를 8바이트 까지 들어가게 설정    

표현식 사용하여 출력

select 구문뒤 '(홀따옴표) 로 묶어서 사용

(리터럴 안에 홀 따옴표가 있을 경우 홀따옴표 1개를 출력하기 위해 2개를 사용)

ex) 교수님 's 매너 최고!!를 출력하기 위해 '교수님 ''s 매너 최고!!'

별칭 사용

select 칼럼명 " " 를 사용

ex) name 의 칼럼명을 이름으로 출력하기 위해 select name "이름"

Alias 연습문제 1 > emp 테이블을 사용하여 empno 를 입사번호, ename 을 사원이름, job을 직급으로 별명을 설정하여 출력

> select empno "입사번호" , ename "사원이름" , job "직급" from emp ;

Alias 연습문제 2 > dept 테이블을 사용하여 deptno 를 부서번호, dname 부서명, loc를 부서의 지역으로 별명을 설정하여 출력

> select deptno "부서번호" , dname "부서명" , loc "부서의 지역" from dept ;

DISTINCT 명령어 - 중복된 값을 제거하고 출력

정렬 <-이전 10g R2 이후-> 정렬x  hash함수 사용

hash = 입력값이 같으면 출력값이 같고, 입력값이 다르면 출력값이 다르게 나오는 함수

DISTINCT 함수는 컬럼이 많으면 사용하면 안됨 , SELECT문 뒤에 와야한다.

연결 연산자

|| 사용

연결 연산자 문제 1 > 아래 그림과 같이 출력

> select name||' 의 ID는 '||ID||' 이고 , 체중은 '||weight||' 입니다. ' "ID 와 체중 "FROM student ;

연결 연산자 문제 2 > 아래 그림과 같이 출력

> select ''||ENAME||'('||JOB||') ,'||ENAME||''''||JOB||'''' "이름과 직업" FROM emp ;

원하는 조건만 골라내기 - WHERE

SELECT [Column or Expression]

FROM [Table or View]

WHERE 원하는 조건

※where 절은 반드시 from 절 아래에 와야 함

- 숫자 조회시 그대로 쓰면 되지만 문자와 날짜를 조회 할 때는 해당 문자나 날짜에 홀따옴표를 붙여야 함

- 문자는 대소문자 구분을 하고 날짜는 대소문자 구분 없음

기본 산술연산자 사용

일반적으로 사용되는 산수 계산 잘 됨. (사칙연산 할때 우선순위 조심)

다양한 연산자 활용

BETWEEN 사용하여 사이 값 출력

ex)2000 ~ 3000 사이인 사람들의 empno,ename,sal 출력

BETWEEN - SELECT empno,ename,sal FROM emp WHERE sal BETWEEN 2000 AND 3000 ;

연산자 활용 - SELECT empno,ename,sal FROM emp WHERE sal >= 2000 AND SAL <= 3000 ;

※위와같이 특정 구간의 값을 검색할 때 BETWEEN 연산자보다 비교 연산자를 사용하여 쓰는 것이 훨씬 빠르다.

IN 연산자로 여러 조건을 간편하게 검색

SELECT empno,ename,deptno

   2  FROM emp

   3  WHERE deptno IN (10,20) ;

- emp 테이블에서 deptno 가 10,20 인 모든 사원들의 empno, ename, deptno 출력

LIKE 연산자로 비슷한 것들 모두 찾기

LIKE 연산자를 쓸 때 문자나 날짜를 처리하기 위해 홀따옴표를 사용

 - % : 글자수 제한 없고 (0개 포함) 어떤 글자가 와도 상관 없음

 - _ (Underscore) : 글자수는 한글자만 올 수 있고 어떤 글자가 와도 상관없음

ex) 이름 글자수가 3글자 인데 성은 "박" 이고 마지막 글자가 "주" 인 사람을 찾는다면

      WHERE name LIKE '박_주' ;  이런식으로 검색

※LIKE 연산자를 쓸 때 % 나 _ 를 가장 먼저 쓰면 안된다. 먼저 쓰게 될 경우 인덱스 때문에 속도가 느리게 검색된다.

값이 무엇인지 모를 경우 - IS NULL / IS NOT NULL

NULL 값은 값이 얼마인지 모른다는 의미

ex) 1 ~100 사이의 값이 들어와야 하는 곳에 값이 안들어오면 NULL, 초과해서 들어오면 N/A 

NULL값을 조회하고 싶으면 IS NULL 

NULL값을 제외하고 조회하고 싶으면 IS NOT NULL

검색조건이 두 개 이상일 경우 조회

두 조건을 동시에 만족해야 하는 경우 AND, 두 조건중 하나만 만족해도 되는 경우 OR

정렬하여 출력하기 - ORDER BY 절

정렬의 기본값은 오름차순. (날짜는 최근 날짜가 더 큼)

오름차순 = ASC  내림차순 = DESC

ename 순서대로 오름차순 정렬             

SELECT ename,sal,hiredate

   2 FROM emp ;

   3 ORDER BY ename ;

dept 순으로 오름차순으로 선 정렬후 동일 dept가 있을시 sal 값으로 내림차순으로 정렬

SELECT ename,sal,hiredate,deptno

   2 FROM emp

   3 ORDER BY deptno ASC , sal DESC ;

ORDER BY 2,1 ; 이런식으로 숫자로 사용할 때 도 있는데 이 숫자의 의미는 SELECT 절에 오는 컬럼의 순서를 뜻함. 즉 2 란 sal을 뜻 하고 1 은 ename을 뜻함.

sal로 먼저 정렬 후 동일한 sal 값이 있을 경우 ename으로 한번 더 정렬을 하라는 의미

SELECT ename, sal, hiredate

  2 FROM emp

  3 WHERE SAL > 1000

  4 ORDER BY 2,1 ;

집합 연산자

※ 위 4가지 집합 연산자를 사용할 경우 주의 사항

- 두 집합의 SELECT 절에 오는 칼럼의 개수가 동일해야 함

- 두 집합의 SELECE 절에 오는 칼럼의 데이터 형이 동일해야 함

- 두 집합의 칼럼명은 달라도 상관없음

ASM 설치

$ cd /home/oracle/pkg/database

$ ./runInstaller

Product Languages 선택해서 Korean 추가 

이름이랑 경로 변경해주기

Name = OraDb10g_home2

Path = /home/oracle/product/10g/asm

Select All

성공 못한 부분은 체크해주고 넘어가기

Configure Automatic Storage Management (ASM)체크하고 비밀번호 설정

/dev/raw/raw6 , /dev/raw/raw7 선택     /dev/raw/raw8 은 플래쉬백용으로 남겨두기

각 노드에 스크립트 입력

$ crs_stat -t 해서 asm 설치확인

ASM 패치

ASM 패치를 위해 ASM 정지 후 상태확인 

$ srvctl stop asm -n rac1

$ srvctl stop asm -n rac2

$ cd /home/oracle/pkg/Disk1

$ ./runInstaller

이름 경로 확인해서 바꿔준다. (이름 바꾸면 경로 자동으로 바뀜)

모든 노드에서 스크립트 입력

asm 구동되나 확인

$ crsctl start asm -n rac1

$ crsctl start asm -n rac1

DB생성

$ dbca

Select All

Custom Database 선택

vi /home/oracle/.bash_profile 에 SID 명이랑 같게 해줌

체크 해제하고 Next

암호설정

Automatic Storage Management (ASM) 체크

Create New 선택

플래쉬백 디스크 생성을 위해 External 선택하고 raw8 체크

DATA만 체크하고 Next

플래쉬백 경로 설정을 위해 Browse 선택

FRA 체크 하고 free 용량 확인

Flash Recovery Area Size 에 아까 확인했던 free사이즈 기입

Standard Database Components선택

체크 다풀고 OK

체크 해제하고 Next

$ crs_stat -t  로 DB확인

엔진 설치

$ cd /home/oracle/pkg

$ unzip 10201_database_linux32.zip

$ cd database

$ ./runInstaller

Product Languages 선택해서 Korean 추가

이름 경로 확인

Select All 눌러서 모든 노드 선택

실패한거는 체크 눌러서 확인

Software only 선택

설치중

node1 node2 차례로 스크립트 입력

엔진 패치

$ cd /home/oracle/pkg/Disk1

$ ./runInstaller

이름, 경로 확인

node1 node2 에서 스크립트 입력

리스너 구성

$ netca

Select all nodes 선택

crs_stat -t 로 리스너 제대로 구성되어있는지 확인