Oracle SQL, select문 구조 , 기본함수

* 질의 절차

1. 접속 : sqlplus scott/tiger      

sqlplus hr/hr                 : 여기까지 일반 권한

            sqlplus / as sysdba      : 최고권한 접속

            등등..

2. 테이블 리스트 확인 

           select * from tab;

3. 컬럼 리스트 확인 

           desc EMP

4. 질의 

* SELECT Statement

SELECT * | {[DISTINCT] column | expression [alias], ...}

FROM    table;

DISTINCT : 중복제거

alias : heading 표시

heading을 표현시 kjin's 처럼 외따를 쓸려면 앞에 한번더 '를 써주던지 q() 문을 써준다.

ex) select ename || '''salary is ' || sal from emp;

      select ename || q'('salary is )' || sal from emp;    -> 10g 방식 괄호말고 다른 특수문자로 써도 가능

이렇게 두가지 방식으로 가능

SELECT player_name, e_player_name, height
FROM PLAYER_T
WHERE e_player_name LIKE '%!%%' ESCAPE '!';   -> escape로 구분자를 지정해주어서 문자에 %가 있는걸 찾음.. 

*스크립트 저장

select * from tab;        - 1

sav t                          - 2

@t                             - 3

하나의 예로써 매번 명령어를 치는 것보다 자주쓰는 명령어를 스크립트로 저장시켜서 사용하기 쉽게 함.

* sqlplus 명령정리

- l                        : 버퍼보기

- r                        : 버퍼실행

- ed                     : 버퍼수정

- ed 파일명           : 해당 파일을 수정

- show user         : 유저명

- conn id/pw        : 접속

- sav 파일명         : 저장

- get 파일명           : 저장된 파일 버퍼로

- @파일명             : 파일 실행. - 여러개 가능

- host o/s 명령       : os 명령 실행

- host                    : command창으로 나감. 리눅스에서는 !가능

- exit                     : 나가기

- a                         : 버퍼 끝에 내용 붙이기

- show all              : sqlplus의 모든 셋팅 보기

* where 문             : select 문에서 필요한 정보를 추출

- and

   or 

   not 

   between A and B 

   in(A,B,C,D,E) , = some(A,B,C,D,E) , = any(A,B,C,D,E)

   like ( %, _ )

   ex) select ename from emp

        where regexp_like(ename,'^.O.*$')

* order by 문          : 검색결과를 정렬해서 보기

ASC : default

DESC

ex> order by deptno, sal desc;

* 변수

& 변수명 : 저장해 두고 쓰면 여러값에 대한 처리를 할때 좋음, 실행할때 마다 물어봄

&& 변수명 : 처음만 물어보고 그 다음부터 안물어봄 설정된것을 해지 할때는 undefine 변수명

* 출력설정

set pagesize 50

set line 111

col 컬럼이름 for 표현방식 : 문자일경우 a숫자

  숫자일경우 자리수만큼의 숫자나 표현방식 -> 5자리일때    99999

                                                                천자리 표시  99,999

* 함수

upper('내용')                                               : 대문자로 바꾸기

nvl(내용,0)                                                   : 내용이 null 이면 0으로

nvl2(expr1,expr2,expr3)                                : expr1이 널이 아니면 expr2 널이면 expr3을 리턴

nullif(expr1,expr2)                                        : 같으면 널, 다르면 첫번째 값 리턴

coalesce(expr1,expr2,expr3, ... ,expr n)         : 널이 아닌 첫번째 값을 리턴

todate('1999-12-31','yyyy-mm-dd')                : 명시적 날짜 입력

to_char(sal*12,'9,999,999$')                            : 숫자를 문자열로

to_char(hiredate,'yyyy')                                    : 날짜를 문자로

power(2,3)                                                    : 2의 3승 

                                                                      함수는 중첩이 가능하다 power(2,power(2,power(2,2)))

concat('aaa','bbb')                                        : aaabbb 문자를 합치기

substr('abcdef',2,3)                                        : 2번째 문자부터 3개 잘라내기 결과 bcd

instr('abcdef','bc')                                         : bc가 시작되는 캐릭터 위치 결과 2

lpad(sal,10,'##')                                             : sal을 10자리로 표현하고 왼쪽 빈칸은 ##로 채움

trim('&T')                                                       : 입력값에서 공백(빈칸)잘라내기

replace('In the fucking world..','fuck','####')              : 글자치환, fuck이라는 글자를 ####로 치환한다.
translate(ename,'1234567890','일이삼사오육칠팔구영') : 내용에서 하나라도 일치하는 부분이 있으면 교체

round(45.926 , 2)                                                     : 소수점 둘째 자리까지 남기고 반올림 -> 45.93

trunc(45.926 , 2)                                              : 소수점 둘째 자리까지 남기고 내림 -> 45.92

avg()                                                               : 평균

sum()                                                              : 총합

count()                                                            : 라인수 null은 집계에서 제외됨

group by                                                         : 컬럼에 대해서 그룹지어주기

having                                                             : group by 에 대해서 조건 걸어주기

min()

max()

round(수,자리)  : 반올림
trunc(수,자리) : 내림  자리가 -3이라고 하면 10의 3승으로 기억하면 계산하기 쉽다.
mod(수,수) : 나머지
ceil(3.3) , ceil(-2.4) : 소수점 이하 자리를 올림함 결과값 : 4 , -2
floor(3.3), floor(-2.4) :  소수점 이하 자리를 내림함 결과값 : 3, -3

length() : 글자길이 계산
                                                       

* 시간설정

select sysdate from dual;

alter session set nls_date_format='yy-mm-dd dy hh24:mi:ss';

sysdate : 현재시간

set nls_date_format : 시간출력방식 설정

months_between(날짜1,날짜2) : 월차이(날짜1-날짜2)

add_months(날짜,개월수)        : 월 더하기

next_day             : 돌아오는 해당요일

last_day              : 월의 마지막 날

round                 : 반올림

trunc                  : 내림

* 데이터 타입 변경

                  to_char                                       to_date

            ------------------>                 ------------------->

NUMBER                           CHARACTER                              DATE

             <----------------                   <-------------------

                     to_number                                to_char  

날짜 -> 문자 to_char 사용이유 : 날짜 정보중 특정 정보(년도, 월, 일자, 요일, 분기, 주..)만 이용.
                              사용방법 : 1. to_char(날짜,'보고싶은정보')
                                             ex) select to_char(sysdate,'mm') from dual;
                                                   select to_char(sysdate,'day') from dual;
숫자 -> 문자 to_char 사용이유 : 숫자값에 천단위 문자를 붙여서 좀더 빨리 숫자값을 읽도록 도와줌.
                              사용방법 : ex) select 12345, to_char(12345,'999,999') from dual;
                                                   select 12345, to_char(12345,'000,000') from dual; 빈자리는 0으로 채움
2070년 12월 25일은 무슨요일??
SQL> SELECT TO_CHAR(TO_DATE('2070-12-25','yyyy-mm-dd'),'day') FROM dual;
1998년 12월 23일기준 15년뒤는??

SQL> SELECT ADD_MONTHS(TO_DATE('19981223','yyyymmdd'),15*12) FROM dual;

* case 문

select ename,

case deptno

when 10 then 'ACCOUNTING'

when 20 then 'RESEARCH'

when 30 then 'SALES'

else 'OPERATIONS'

end                                       dname

from emp;

select ename,

decode( deptno

, 10 , 'ACCOUNTING'

, 20 , 'RESEARCH'

, 30 , 'SALES'

, 'OPERATIONS'

)          dname

from emp;

* 질의 순서

  select             : 6

  from               : 1

[ where ]          : 2

[ group by ]      : 3

[ having ]         : 4

[ order by ]       : 5