'MOD'에 해당하는 글 2건



다운로드


- Oracle 11g Express

- Oracle SQL Developer(jdk포함)





DQL(Data Query Lenguage)


1. SELECT 구문에 의해서 테이블의 자료를 검색하는 명령

2.

SELECT 컬럼리스트

FROM 테이블

WHERE 조건식

GROUP BY 기준컬럼

HAVING 조건식

ORDER BY 기준컬럼;


-- 특정 테이블의 자료 확인

select * from employees;



--특정 테이블의 자료 일부 출력.  행(column) 제한

select first_name, last_name, phone_number

from employees;


-- 특정 테이블의 자료 일부 출력. 열(Row) 제한.

select *

from employees

where job_id = 'IT_PROG';


-- 특정 테이블의 자료 일부 출력. 행(column), 열(Row) 제한.


select first_name, last_name, phone_number

from employees

where job_id = 'IT_PROG';






조건식(행 제한)


1. 

select 컬럼리스트

from 테이블

[where 조건식]



2. 연산자 종류

연산자 종류

비교연산자 : =, >, <, >=, <=, <>

논리연산자 : and, or, not

SQL 연산자 : IN(값, ...), BETWEEN ~ AND , LIKE



3. 자료형

-문자형 : CHAR2, VARCHAR2

-날짜형 : DATE

-숫자형 : NUMBER



4. 리터럴(상수)

문자형 : 작은 따옴표로 표기된 문자열. EX) '테스트' 

날짜형 : 날짜 서식을 가진 문자열. '년-월-일', '월/일/년' 형식을 가진 문자열. ex)'2012-10-22'

숫자형 : 정수 또는 실수로 표기된 숫자. ex) 10


올바른 자료형 비교 예.

select *

from employees

where job_id='IT_PROG'; -- 성공


틀린 자료형 비교 예.

select *

from employees

here job_id="IT_PROG";" -- 실패






날짜 데이터 검색하는 방법

-> 날짜 데이터를 문자열 형식으로 변환하는 과정 필요.

-> to_char(날짜데이터, '서식') 함수 이용


--employees 테이블에서 입사년도(hire_date)가 '2003'년인 경우 출력.

select *

from employees

where to_char(hire_date, 'yyyy') = '2003';



--employees 테이블에서 입사월(hire_date)이 '1'월인 경우 출력.

select *

from employees

where to_char(hire_date, 'mm') = '01';


--employees 테이블에서 입사년도(hire_date)가 '2005'년이고, 입사월이 '1'월인 경우 출력.

select *

from employees

where to_char(hire_date, 'yyyy-mm') = '2005-01'; 


-- 혹은

select *

from employees

where to_char(hire_date, 'yyyy') = '2005' and to_char(hire_date, 'mm') = '01';


--employees 테이블에서 입사년도(hire_date)가 '2000'년대인 경우 출력.

-> substr(원본데이터, 출발위치, 길이)


select *

from employees

where to_char(hire_date, 'yyyy') = '2000' or to_char(hire_date, 'yyyy') = '2001' or to_char(hire_date, 'yyyy') = '2002'

      or to_char(hire_date, 'yyyy') = '2003' or to_char(hire_date, 'yyyy') = '2004' or to_char(hire_date, 'yyyy') = '2005'

      or to_char(hire_date, 'yyyy') = '2006' or to_char(hire_date, 'yyyy') = '2007' or to_char(hire_date, 'yyyy') = '2008'

      or to_char(hire_date, 'yyyy') = '2009';

      

-- 혹은

select *

from employees

where substr(to_char(hire_date, 'yyyy'), 1, 2) = '20';


-- 혹은 (문자열도 크기 비교가 됨.)

select *

from employees

where to_char (hire_date, 'yyyy') >= '2000' and

         to_char(hire_date, 'yyyy') <= '2009';






숫자 데이터 검색하는 방법



-- employees 테이블에서 salary(급여)가 20000달러 이상인 경우 출력

select *

from employees

where salary >= 20000;


--employees 테이블에서 salary(급여)가 5000~10000번위인 경우 출력


and연산자 사용.








SQL 연산자


1. IN(값, ...): or 연산 역할. 괄호 안의 나열된 값중 하나와 일치하면 true


2. BETWEEN 값1 AND 값2: and 연산 역할. 값1~값2 범위 지정시


3. LIKE: 피연산자가 패턴(_, %)과 일치하는 경우 true;



--jobs 테이블에서 job_title이 'President' 이거나, 'Sales Manager'인 경우 출력.

select *

from jobs

where job_title='President' or job_title = 'Sales Manager';


-- 혹은

select *

from jobs

where job_title in ('President', 'Sales Manager');



--employees 테이블에서 salary(급여)가 5000~10000번위인 경우 출력


select *

from employees

where salary between 5000 and 10000;




--employees 테이블에서 전화번호가 4567로 끝나는 경우 출력

-> substr은 길이가 서로 다른 문자열에서는 일부 문자열을 얻기가 힘듬.

-> like 연산자 사용


select *

from employees

where phone_number like



--employees 테이블에서 전화번호가 국번이 123로 끝나는 경우 출력


select *

from employees

where phone_number like '%123_____';


select *

from employees

where phone_number like '___.123.____';








null 확인


1. 데이터베이스 내의 데이터 저장 공간을 확보한 후 데이터를 채우지 않으면

자동으로 NULL값이 저장된다.


2. is 로 확인.


-- employees 테이블에서 commission_pct 정보가 null인 경우 출력


select *

from employees

where commission_pct is null;









내장 함수


- 함수는 데이터를 입력시 , 단일 결과를 리턴하는 프로그램의 집합


1. 입력데이터가 없는 경우

select sysdate from dual; -- 시스템의 현재 날짜


select to_char(sysdate,'yyyy-mm-dd') from dual;


select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;






2. 입력 데이터가 있는 경우

TO_DATE('날짜문자열') : 문자열을 날짜 형식으로 변환


select sysdate - to_date('특정날짜') from dual;

-- 특정 날짜부터 특정 날짜 사이의 날짜수 계산



TO_CHAR(날짜자료형, '서식')


select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;

--서식에 맞게 날짜, 시간 출력


ROUND(값)


select round (10.123) from dual; --10

select round (10.567) from dual; --11

select round (10.567, 2) from dual; --10.57

select round (10.567, 1) from dual; --10.6

-- 반올림 함수



TRUNC(값)


select trunc (10.123) from dual; --10

select trunc (10.567) from dual; --10

-- 절삭함수

ex) employees 테이블에서 salary가 10000이상이면서, 20000 미만인 경우 출력

select *

from employees

where trunc (salary/10000) = 1;



MOD(값1, 값2)


-- 나머지 연산 함수


LOWER('대문자') : 입력된 문자를 소문자로 변경


UPPER('소문자') : 입력된 문자를 대문자로 변경


ex)

select *

from employees

where first_name = 'ellen'; --X


select *

from employees

where first_name = 'Ellen'; --O


select *

from employees

where first_name = 'ELLEN'; --X


select *

from employees

where LOWER(first_name) = LOWER('ellen'); --O


select *

from employees

where UPPER(first_name) = UPPER('ellen'); --O



CONCAT(문자열1, 문자열2) : 입력된 문자열1과 문자열2를 하나의 컬럼으로 합쳐 출력


LENGTH(원본문자열) : 문자열 길이


SUBSTR(원본문자열, 위치, 길이) : 부분 문자열 리턴


INSTR(원본문자열, 검색문자열) : 문자열 검색.


REPLACE(원본문자열, 검색문자열, 대체문자열) : 문자열 대체


LPAD(식,길이,표시문자열) : 문자열 대체. 특정 문자열로 채워짐. (LIFT)

EX) 

select '801010-1234567' from dual;

select lpad((substr('801010-1234567', 7, 8), 14, '*') from dual;



RPAD(식,길이,표시문자열) : 문자열 대체. 특정 문자열로 채워짐. (RIGHT)

EX) 

select rpad(substr('801010-1234567', 1, 7), 14, '*') from dual;


NVL(식, 값) : 식의 결과값이 null 이면 값 리턴 (if구문만 있는 상태)


NVL2(식, 값1, 값2) : 식의 결과값이 null이면 값1 리턴 아니면 값2 리턴 (if~else 구문 상태)


DECODE(식, 조건, 값1, 조건2, 값2, ...) : 식의 조건이 참이면 값1을 리턴하고 조건2를 만족하면 값2를 리턴.. (if ~ else if~ else 인 상태)

ex)

select first_name, last_name, decode(department_id, 90, 'Excutive', 60, 'IT', 50, 'Shipping')

from employees;


ex)employees 테이블에서 salary가 10000미만인 경우는 C등급, 10000이상 20000미만인 경우는 B등급, 20000이상인 경우는 A등급으로 출력

select first_name || ' ' || last_name AS 이름, 

decode(trunc(salary/10000), 0, 'C등급', 1, 'B등급' , 2, 'A등급') as 급여등급

from employees;


WRITTEN BY
빨강꼬마

,

- trunc
  소수점 이하 특정수치까지 표현할때 사용할수 있다. 소수있는 데이터를 소수없이 정수로만 표현할때도 사용한다.

 

  trunc(임의의데이터, [x])
  X = 소수점을 표현할 자릴수를 의미.
      0은 정수만 표현하며, 이는 X를 기입하지 않는 경우 trunc는 자동으로 0으로 인식한다.
      -1은 10의 자리까지만 보여주고 이하는 버리라는 의미. -2는 100의 자리까지 보여주라는 것.

 


- mod
  특정데이터를 나누었을때 남는 나머지값을 보여줌.

 

  mod(임의의데이터, [X])
  X = 임의의데이터를 나눌 값을 입력.
    2를 입력하면 임의의데이터를 2로 나눈후 나머지 값을 출력하고.
    5를 입력하면 임의의데이터를 5로 나눈후 나머지 값을 출력.

※ 시간에서 시간을 빼면 그 결과값은 일수로 출력된다.

 

- months_between
  입력된 두 날짜를 기준으로 몇개월차이나는지 확인.

 

 

  months_between([X], [Y])
  X, Y = 입력될 데이터는 날짜형식에 맞추어(ex: 2001-01-01) 입력하면 별도로 타입변형을 하지 않아도 됨.

 

- last_day
  해당 날짜가 속한 달의 매월 마지막날이 몇일인지 출력

 

 

 

- next_day
  지정된 돌아올 요일이 해당하는 날짜

 

 

  next_day(sysdate, '일')
  next_dat('2012-09-07', '월') -- 2012-09-07을 기준으로 월요일이 돌아올 날짜 -- 출력값은 12/09/10

 

 

 

 

 

 


복습 및 응용.

1. 1년 2개월 3일 4시간 5분 6초를 초로 환산하기.

2. 환산된 초를 년월일시간분초 형태로 나타내기

3. 경매번호, 경매물건, 경매종료시각으로 구성된 테이블에서
   현재로부터 남은기간은 얼마인지 나타내기

4. 26개월을 복무기간으로 입대한다. 제대까지 먹어야할 끼니는 몇번 먹을까.

5. 사용자 khman1에서 만든 jikwon 테이블을 기준으로 아래와 같이 나타내기.
   단, 정년퇴직일자는 해당사원의 나이(한국나이)로 60세가 되는 년도의 입사일자로 한다.
   ----------------------------------------------------
    사원명      현재나이     입사일자     정년퇴직일자
   ----------------------------------------------------


WRITTEN BY
빨강꼬마

,