SQL 논리 연산자 - and, or, not , not in, 데이터 정렬 (오름차순, 내림차순) asc, desc , 내장함수 문자함수 - upper, lower, substr, instr, lpad, rpad, length, trim, replace, 숫자함수 - round, trunc, mod, 날짜함수 - sysdate,..

----------------------------오라클의 논리연산자----------------------------

오라클의 논리연산자는 and, or, not 세종류가 있다.

- and : 제시된 모든 조건식이 true면 true를 반환한다.
- or   : 제시된 모든 조건식 중 하나라도 true면 true를 반환한다. // or는 최대한 안쓰는것 거의 절대적
(x이거나 y 인것  =  in 을 쓰고,   x이하이거나 y이상인것  =  between을쓴다.)
- not : 제시된 조건식이 true이면 false로, false면 true로 반환한다.

※ not in : in의 반대이다. x, y, z .... 등이 아닌것



where 절에서 논리연산자를 사용하면 하나 이상의 조건에 부합되는 행을 선택할 수 있다.

예시) 80번 부서에 소속된 사원중에서 급여를 5000달러 이상 받는 사원의 이름과 급여, 직종, 부서아이디를 조회하기

select first_name, salary, job_id, department_id
from EMPLOYEES
where department_id =80 and salary > 5000;

예시2) 소속부서가 60번 이거나 90번인 사원의 이름, 소속부서를 조회하기

SELECT first_name, department_id
from EMPLOYEES
where department_id in (60, 90); -> where department_id = 60 or department_id = 90;
// or도 가능하지만 대부분 in을 쓴다.

예시3) 급여를 3000달러 이하로 받는 사원과 급여를 15000달러 이상으로 받는 사원의 이름, 급여를 조회하기
(이경우는 합집합을 구하는 식으로 or을 쓸수밖에 없다.)

select first_name, salary
from employees
where salary <= 3000 
or salary >= 15000;

 


예시4) 소속부서가 50번 부서에 소속된 사원 중에서 급여를 3000달러 이하로 받거나 급여를 15000달러 이상으로 받는 사원의 
이름, 급여, 부서아이디를 조회하시오

select first_name, salary, department_id
from EMPLOYEES
where department_id = 50
and (salary <= 3000 or salary >= 15000); // 괄호가 중요하다. 

 


예시5) 소속부서가 50번 80번 외의 부서에서 근무하는 사원의 이름, 소속부서를 조회하기
(50번, 80번인 사람찾을때는 in을 쓰지만 아닌것의 반대는 not in이다.)

select first_name, department_id
from EMPLOYEES
where department_id not in (50, 80);

===========조회된 데이터의 정렬==============
데이터 정렬
order by 절을 사용하면 조회된 데이터를 정렬할 수 있다.
order by 절은 select문의 가장 끝에 위치한다.
asc응 오름차순, desc는 내림차순으로 정렬한다. 생략하면 오름차순 정렬한다.
order by에서는 select 절의 별칭을 사용할 수 있다.
두 가지 이상의 정렬기준을 적용해서 정렬할 수 있다.

데이터 정렬(오름차순)
- 날짜는 옛날 날짜부터 표시
- 숫자는 작은 값부터 표시
- 문자는 알파벳, 가나순으로 표시
- null값은 마지막에 표시


실행순서
select -- 3번
from -- 1번
where -- 2번
order by -- 4번



select *
from 테이블명
where 조건식
order by 컬럼명 asc   <--- 지정된 컬럼의 값들이 기준으로 오름차순 정렬한다.   ※. asc일경우 생략이 가능하다.

select *
from 테이블명
where 조건식
order by 컬럼명 desc  <--- 지정된 값들의 기준으로 내림차순으로 정렬한다.


예시) 사원테이블에서 아이디, 이름, 급여를 조회하고, 급여를 기준으로 내림차순 정렬하기
select employee_id, first_name, salary
from EMPLOYEES
order by salary DESC;

 


예시2) 80번 부서에 소속된 사원들의 이름, 입사일을 조회하고 입사입을 기준으로 오름차순 정렬하기
select first_name, hire_date
from EMPLOYEES
order by hire_date; -- asc 생략 가능

 



예시3) 80번 부서에 소속된 사원중에서 이름에 'e'를 포함하고 있고, 급여를 10000달러 이상으로 받는 사원들의
아이디, 이름, 급여를 조회하고, 급여순으로 내림차순 정렬하기

select employee_id, first_name, salary
from EMPLOYEES
where department_id = 80 
and (first_name like '%e%' or first_name like '%E%') 
and salary > 10000
order by salary desc; --> order by 3 desc;  select에 3번째로 써있어서 이렇게도 쓸수있다.

예시4) 80번 부서 사원의 급여, 이름을 조회하기
급여와 이름순으로 오름차순 정렬하기(급여가 동일할 때는 이름순으로 정렬)

select salary, first_name
from EMPLOYEES
where department_id = 80
order by salary , first_name;

------------------------오라클의 내장함수--------------------------

내장함수란
- 값을 입력받아서 적절한 작업을 수행하고 결과값을 반환하는 것

 

 

 

- 구분

 

   - 단일행 함수
         - 단일 행에 대해서만 연산을 수행하고, 행 당 하나의 결과를 반환한다.
         - 단일행 함수는 여러 번 중첩해서 사용할 수 있다.
           예시) 문자자르기(대문자변환(문자), 1, 8)  -  대문자 변환 후 문자 자르기 2번 중첩
         - select절, where절, order by절에서 사용할 수 있다.

         - 종류
        문자함수 - 문자를 입력받아서 처리하고 결과를 반환한다.
        숫자함수 - 숫자를 입력받아서 처리하고 결과를 반환한다. 
        날짜함수 - 날짜를 입력받아서 처리하고 결과를 반환한다.
        변환함수 - 날짜 <-> 문자, 숫자 <-> 문자로 변환한다.
        기타함수 - 기타

   - 다중행 함수(그룹함수)
        - 여러 행(행 그룹)을 조작해서 행 그룹 당 하나의 결과를 반환한다.
        - 평균, 합계, 분산, 표준편차, 최댓값, 최소값, 갯수 등 계산한다.
        - 통계작업에 활용된다. 
        - 한번만 중첩이 가능하다.

///////////////////////////////////문자 함수


=======upper(컬럼), lower(컬럼)========
예시) 문자함수 - 대소문자 변환함수
upper(컬럼), lower(컬럼)

select first_name, upper(first_name), lower(first_name)
from EMPLOYEES;

=======substr(컬럼,m[,n]) : m번째부터  n번째까지 문자를 반환한다.========
예시) 문자열 추출하기 - substr(컬럼,m[,n])

select first_name, substr(first_name, 2)
from employees;

=======instr(컬럼, '문자') : 지정된 문자의 위치를 반환한다.============
예시) 지정된 문자의 등장 위치 찾기 - insert(컬럼, '문자')

select instr('801010-1121211','-')
from dual; // dual : 더미테이블

※ dual : 오라클의 빌트인 테이블, 1행1열짜리 테이블, 더미테이블이다.
특별한 테이블을 대상으로 수행하는 조회작업이 아닐때 사용된다.

========lpad(컬럼, 길이, '문자'), rpad(컬럼, 길이, '문자')==============
컬럼을 쓰고 컬럼의 값이 지정된 길이보다 짧으면 부족한 길이만큼 문자를 채운다.

lpad : 왼쪽에서 부터 문자를 채운다.
rpad : 오른쪽에서 부터 문자를 채운다.

예시)문자 채우기
SELECT lpad('abc',10,'*'), rpad('abc',10,'*')
from dual;

 



========length(컬럼) : 문자의 길이를 반환=====================
예시) 문자 길이 반환
SELECT first_name, length(first_name)
from employees;

 


예시2) 문자가 4개이상인 이름 반환
select first_name, length(first_name)
from EMPLOYEES
where length(first_name) <= 4;

 



심화 예제) 이름에 'C'나 'c'가 들어가있는 사원의 이름을 조회하기
select first_name
from EMPLOYEES
where lower(first_name) like '%c%';

=========trim(컬럼) : 불필요한 좌우 공백을 제거한다.===========
예시) 불필요한 공백 없애기
select '                      abc                                 ', trim('                        abc                                 ')
from dual;

// tab은 없어지지 않는다. tab은 문자로 인식



==========replace(문자, '찾을문자', '변경할문자')=============
예시)문자 바꾸기
select replace('자바 입문자를 위한 자바의 정성', '자바', '파이썬')
from dual;

///////////////////////////////////숫자 함수


==========round(숫자, 자릿수)==================
round(숫자, 자릿수) : 지정된 자리로 값을 반올림한다.

예시)
select round(3.2), round(3.6)
from dual;

예시2)
select round(10/3,2)
from dual;
 

예시3) - 자리로 적을경우 반대로 간다.
SELECT round (12345, -1), round (12345, -2), round (12345,-3), round (12345, -4)
from dual;

==========trunc(숫자, 자릿수)==================
trunk(숫자, 자릿수) : 지정된 자리까지 남기고 값을 버린다.


예시)
select trunc(123.58,1), round(123.58,1)
from dual;

예시2)
select first_name, salary, trunc(salary, -3)
from EMPLOYEES
where department_id = 80;

 



==========mod(숫자1, 숫자2)===================
mod(숫자1, 숫자2) : 숫자1을 숫자2로 나눈 나머지를 반환한다.





//////////////////////////////날짜 함수

============sysdate=====================
sysdate : 현재날짜와 시간정보를 반환한다. 반환값은  DATE타입의 값이다.

예시)
select SYSDATE
from dual;

============months_between(날짜, 날짜)========
months_between(날짜, 날짜) : 두 날짜사이의 개월 수 반환

예시) 사원아이디, 이름, 입사일, 근무개원 수 조회하기
select employee_id, first_name, hire_date, trunc(months_between(sysdate, hire_date)) months
from employees;

==========add_months(날짜, 개월수)============
add_months(날짜, 개월수) : 날짜에 개월수를 더한 날짜를 반환

예시) 앞으로 6개월 후
select add_months(sysdate, 6)
from dual;

===============날짜 연산=================

날짜 + 일수 : 지정된 날짜에서 더해진 숫자만큼 이후의 날짜를 반환한다.
날짜 - 일수 : 지정된 날짜에서 빼진 숫자만큼 이전의 날짜를 반환한다.
날짜 - 날짜 : 두 날짜 사이의 일수를 반환한다.
날짜 + 시간/24 : 지정된 날짜에 시간이 더해진 날짜를 반환한다.


예시) 최근 3일전에 입사한 사원 조회하기
select employee_id, first_name, hire_date
from EMPLOYEES
where hire_date < (sysdate -3);

예시) 사원들의 이름, 입사일, 근무일수 조회하기
select first_name, hire_date, trunc(sysdate - hire_date) days
from EMPLOYEES;

/////////////////////////////////////변환함수
변환 함수란
날짜를 문자로 꾸고 문자를 날짜로 바꾸거나 
문자를 숫자로 숫자를 문자로 빠꿀 수 있는 함수를 말한다.

종류
to_char(날짜, '패턴')
날짜를 문자로 바꾼다.

to_char(숫자, '패턴)
숫자를 문자로 바꾼다.
--------------------------------------
to_date('문자', '패턴)
문자를 날짜로 바꾼다.

to_number('문자', '패턴')
문자를 숫지로 바꾼다.



예시)  날짜를 문자로 변경하기     to_char - 출력값 형식
select to_char(SYSDATE,'YYYY') 년, to_char(sysdate, 'MM') 월, to_char(sysdate, 'DD') 일,
to_char(sysdate, 'HH24') 시, to_char(SYSDATE, 'MI') 분, to_char(sysdate, 'SS') 초,
to_char(sysdate, 'am') 오전오후, to_char(sysdate, 'day') 요일
from dual;

 

 

 

 

 

예시2) 문자를 날짜로 변경하기     to_date - 입력값의 형식

select to_date('2019/01/31', 'YYYY/MM/DD'),
 to_date('2019-01-31', 'YYYY-MM-DD'),
 to_date('31/01/2019', 'DD/MM/YYYY')
from dual;

                         (테이블에 출력된 값은 그대로지만 입력된 값의 타입을 지정해준것이다.)

 

 

 

 

 

 

 

예시3) to_date로 연산하기

select '2019/08/19' - SYSDATE
from dual;                   // 에러

select to_date('2019/08/19','yyyy/mm/dd') - SYSDATE
from dual;

위의 첫번째 방식으로 하면 date 와 char은 타입이 달라 계산이 안된다하여 아래처럼 해야한다.

 

 

 

 

 

 

 

예시4)  2008년 3월 1일 ~ 2008년 3월 15일 사이에 입사한 사원의 아이디, 이름, 입사일 조회하기

select employee_id, first_name, hire_date
from EMPLOYEES
where hire_date >= to_date('2008/03/01', 'yyyy/mm/dd')
and hire_date <= to_date('2008/03/15 23:59:59', 'yyyy/mm/dd hh24:mi:ss');

(시간을 적어둔것은 시간을 안적을경우 00:00:00시 이기때문에 사실적으로 15일에 입사한사람은 안나온다.

아니면 처음부터 16일로 적어둬도 된다.)

 

사실 아래처럼 사용이 가능하다. 이런것을 묵시적 변환이라 하고 날짜형식으로 입력되면 자동으로 

날짜타입으로 바뀐다.

 

 

 

 

 

 

 

to_char(숫자, '패턴')은 숫자사이에 , 넣고 그런거에 사용하는것인데 이런건 자바에서 패턴을 써서 많이 안씀

 

예시5) 숫자를 문자로 변환

select employee_id, first_name, to_char(salary, '99,999'), to_char(salary, '00,000'),
             to_char(commission_pct, '9.99'), to_char(commission_pct, '0.00')
from employees
where commission_pct is not null;

숫자 표현을 자바는 #이었지만 sql은 0, 9가 사용된다.

9 : 소숫점 이상의 자리에서 없는자리는 생략하지만 소숫점 이하는 없으면 0을 표현

0 :  소숫점 이상과 이하 모두 없는자리를 생략하지않고 0을 넣는다.

 

 

 

 

 

 

to_number은 묵시적 변환으로 자동으로 되기때문에 많이 사용되지 않는다.

 

예시6) 문자를 숫자로 변환하기

select employee_id, first_name, salary
from EMPLOYEES
where salary > to_number('15,000','99,999');

15000은 바로 묵시적 변환이 되지만 " , "를 사용하면 묵시적 변환이 되지않아 to_number을 사용한다.