*34일차 (where / 연산자 / 단일행처리함수)

*where

• 대상테이블에서 특정행을 필터링하는 구문
• 행에 대해서 조건절의 결과를 t/f로 구분 , t인 행만 결과집합에 포함시킴

select
	emp_name, dept_code

from
	employee
where
	dept_code = 'D9';  --홑따옴표 안 문자열은 실제값이므로 대소문자 구분

 

*연산자

• =
• > < >= <=  
• != <> ^=  -같지않다.
• between a and b -a이상 b이하
• like | not like  -문자패턴비교
• is null | is not null -null값 비교
• in | not in -값목록에 포함여부

 

*논리 연결연산

• and :  두 조건을 모두 만족시키면 true && 없다.
• or : 두 조건중 하나를 만족시키면 true || 없다. (문자열 연결연산)
• not : 반전

 

-- 부서코드가 D6이고, 급여를 2000000원보다 많이 받는 사원의 이름, 부서코드, 급여 조회
select
    emp_name, dept_code, salary
from 
    employee
where
    dept_code = 'D6' and salary > 2000000;

-- 부서코드가 D9이 아닌 사원 조회
select 
    *
from 
    employee
where
    dept_code <> 'D9'; -- null인 컬럼은 제외

-- 직급코드가 J1이 아닌 사원들의 월급등급(sal_level)을 중복없이 출력
select
    distinct sal_level
from
    employee
where
    job_code ^= 'J1';

-- 부서코드가 D5가 아닌 사원과 부서코드가 null인 사원 모두 조회
select
    *
from
    employee
where
    dept_code != 'D5' 
  or
    dept_code is null;

-- 근무기간이 20년이상인 사원의 이름, 급여, 보너스율을 조회
select
    emp_name, 
    salary,
    bonus,
    (sysdate - hire_date) / 365
from
    employee
where
    (sysdate - hire_date) / 365 >= 20;
    
-- between value1 and value2
-- value1 이상 value2 이하

-- 급여가 3500000원 이상 6000000원 이하의 사원 조회
select
    *
from
    employee
where
--    salary between 3500000 and 6000000;
    salary >= 3500000 and salary <= 6000000;

-- 날짜에 대해서 처리
-- 1990년 ~ 2000년 입사자 조회
select
    emp_name, hire_date
from
    employee
where
--    hire_date between '90/01/01' and '00/12/31';
    hire_date >= '90/01/01' and hire_date < '01/01/01'; -- 1990년 1월 1일 자정(포함)부터 2001년 1월1일 자정(미포함) 전까지 조회

-- like | not like
-- 문자열 패턴 검사

/*
    wildcard (특수한 의미를 가진 문자)
    1. % 문자가 0개이상   a% a다음에 문자 0개이상 -> a ab aaab....
    2. _  문자가 1개      a_ a다음에 문자 1개만 -> ab ac ad

*/

-- 전씨 성을 가지 사원조회
-- 전씨이면서 이름이 2글자인 사원조회
select
    *
from 
    employee
where
--    emp_name like '전%';
    emp_name like '전__';
    
-- 이름이 3글자이고, 가운데 글자가 '옹'인 사원조회
select
    emp_name
from 
    employee
where
    emp_name like '_옹_';

-- 이름에 '이'가 들어가는 사원조회
select
    emp_name
from 
    employee
where
    emp_name like '%이%';
    
-- 성이 이씨가 아닌 사원 조회
select
    emp_name
from
    employee
where
--    emp_name not like '이%';
    not (emp_name like '이%');
    

-- _앞에 글자가 3글자인 이메일조회
select
    email
from 
    employee
where
    email like '___#_%' escape '#'; -- 데이터에 # escape 문자가 있어서는 안된다.

-- in | not in
-- 값목록에 포함되어있으면 true로 처리

-- D6, D8부서원 조회
select
    emp_name, dept_code
from 
    employee
where
    dept_code in ('D6', 'D8');
--    dept_code = 'D6' or dept_code = 'D8';

-- D6, D8부서원을 제외하고 조회
select
    emp_name, dept_code
from 
    employee
where
--    dept_code not in ('D6', 'D8');
--    dept_code != 'D6' and dept_code != 'D8' 
    not (dept_code = 'D6' or dept_code = 'D8');
    
-- is null | is not null
-- 인턴 사원 조회
select
    emp_name,
    dept_code,
    nvl(dept_code, '인턴')
from 
    employee
where
--    dept_code is null;
--    dept_code = null;   -- 작동x
--    nvl(dept_code, '인턴') = '인턴';
    nvl(dept_code, 'ㅌㅌㅌㅌㅌ') = 'ㅌㅌㅌㅌㅌ';
    

select
    emp_name,
    hire_date,
    quit_date,
    nvl(quit_date, sysdate) - hire_date,
    quit_yn
from
    employee;

 

 

*ORDER BY

• 마지막에 실행되며 행의 순서를 재배치한다.
• 오름차순 (작은수→큰수 , 사전등재빠른순→늦은순 , 과거→미래)
• 1개이상의 정렬기준컬럼작성가능
• nulls first | last

select
    emp_name,
    dept_code,
    salary,
    hire_date
from 
    employee
order by
    dept_code nulls first, salary desc;

-- 컬럼명 대신 별칭, 컬럼순서
select
    emp_name 사원명,
    dept_code 부서코드,
    salary 급여,
    hire_date 입사일
from 
    employee
order by
    부서코드 nulls first, 3 desc; -- 3번째 컬럼

 

 

@FUNCTION

• 일련의 작업절차를 모아놓은 서브프로그램
• 호출시 매개인자를 전달하고 그에 따른 수행결과를 반드시 리턴한다. (리턴값이 없을수 없다)

 <함수의 종류>

1.단일행처리 함수 - 행마다 처리되는 함수

• a.문자처리함수
• b.숫자처리함수
• c.날짜처리함수
• d.형변환함수
• e.기타함수

2.그룹처리함수 -행을 그룹핑한후 그룹에 대해서 처리하는 함수

 

 

*단일행처리함수

a.문자처리함수 : length(컬럼/값) - 길이값을 리턴

select
    emp_name, length(emp_name), lengthb(emp_name)
from 
    employee;

-- instr(대상문자열, 검색할문자열[, 시작인덱스, 출현횟수]) : 인덱스를 반환

select
    instr('kh정보교육원 국가정보원 정보문화사', '정보'), -- 3
    instr('kh정보교육원 국가정보원 정보문화사', '정보', 10), -- 11 
    instr('kh정보교육원 국가정보원 정보문화사', '정보', 1, 3), -- 15
    instr('kh정보교육원 국가정보원 정보문화사', '정보', -1), -- 15 시작인덱스 음수인 경우 뒤에서 검사
    instr('kh정보교육원 국가정보원 정보문화사', '안녕') -- 0 
from
    dual;
    
-- 사원테이블에서 이메일 아이디의 길이, 아이디를 조회
select
    email, 
    instr(email, '@') - 1 id_length, 
    substr(email, 1, instr(email, '@') - 1) id
    
from
    employee;

-- substr(대상문자열, index[, length]) : 문자열 리턴
select
    substr('SHOWMETHEMONEY', 5, 2), -- ME
    substr('SHOWMETHEMONEY', 5),    -- METHEMONEY
    substr('SHOWMETHEMONEY', -5)
from 
    dual;
    
-- lpad(문자열, 길이[, 패딩문자]) : 문자열 리턴
-- rpad(문자열, 길이[, 패딩문자]) : 문자열 리턴

select 
    lpad('hello', 10, '#'),
    rpad('hello', 10, '#'),
    lpad(123, 5, 00),
    'kh-' || to_char(sysdate, 'yymmdd') || '-' || lpad(123, 5, 0) 주문번호
from
    dual;
    
-- replace(대상문자열, 검색문자열, 치환문자열) : 문자열 리턴
select
    replace('hello@naver.com', 'naver.com', 'google.com') 새이메일
from
    dual;

-- 사원테이블에서 남자사원의 사번, 이름, 주민번호를 조회
-- (주민번호의 뒤 6자리는 *로 숨김처리)

select
    emp_id,
    emp_name,
    rpad(substr(emp_no, 1, 8), 14, '*') 주민번호,
    substr(emp_no, 1, 8) || '******' 주민번호
from
    employee
where
    substr(emp_no, 8, 1) in ('1', '3');

 

b.숫자처리함수 

• mod(피제수,제수) : 나머지를 리턴
• + - * / 사칙연산
• % 나머지연산은 없다.

select
    10 + 3,
    10 - 3,
    10 * 3,
    10 / 3,
    mod(10, 3)
from 
    dual;

-- 생일 끝자리가 짝수인 사원만 조회
select 
    *
from
    employee
where
    mod(substr(emp_no, 6, 1), 2) = 0;
    
-- ceil(number) : 숫자리턴
select
    ceil(123.456), -- 124
    ceil(123.456 * 100) / 100  -- 123.46
from 
    dual;
    
-- round(number, 소수점이하자리수) : 숫자를 리턴
select 
    round(1234.56),
    round(1234.56, 1),
    round(1234.56, -2)
from
    dual;
    
-- floor(number) : 숫자리턴
select
    floor(234.567),
    floor(234.567 * 100) / 100
from 
    dual;

-- trunc(number, 소수점이하자리수) : 숫자를 리턴

select
    trunc(234.567),
    trunc(234.567, 2),
    trunc(234.567, -2)
from 
    dual;

 

c.날짜처리함수 

• add_months(date,number) : date를 반환

select
    add_months(sysdate, -1) 한달전, 
    sysdate,
    add_months(sysdate, 1) 한달뒤,
    add_months(to_date('22/03/31', 'yy/mm/dd'), 1) "3/31로부터 한달뒤",
    add_months(to_date('22/04/30', 'yy/mm/dd'), 1) "4/30로부터 한달뒤"
from
    dual;
    
-- months_between(미래날짜, 과거날짜) : 개월수차이를 리턴
select
    months_between('22/08/29', sysdate)
from 
    dual;

-- 사원테이블에서 사원별 근무개월수1(n개월), 근무개월수2(k년 j개월)를 출력
select
    emp_name,
    floor(months_between(sysdate, hire_date)) "근무개월수1",
    floor(months_between(sysdate, hire_date) / 12) || '년 ' || 
    floor(mod(months_between(sysdate, hire_date), 12)) || '개월' "근무개월수2"
from
    employee;


-- extract (year | month | day from date) : 숫자를 리턴
-- extract (hour | minute | second from cast(date as timestamp)) : 숫자를 리턴
select
    extract(year from sysdate) 년,
    extract(month from sysdate) 월,
    extract(day from sysdate) 일,
    extract(hour from cast(sysdate as timestamp)) 시,
    extract(minute from cast(sysdate as timestamp)) 분,
    extract(second from cast(sysdate as timestamp)) 초
from 
    dual;

-- trunc(date) : 날짜형을 리턴(시분초를 제거)
select
    to_char(sysdate, 'yy/mm/dd hh24:mi:ss') "sysdate",
    to_char(trunc(sysdate), 'yy/mm/dd hh24:mi:ss') "sysdate"
from
    dual;