(데이터베이스)22일차

ASCII

• 문자의 정수형 값 반환

INSTR

• 문자열에서 몇번째에 있는 문자인지 반환

예시1

예시2

이메일에서 우선 '@골뱅이'의 위치를 찾고 그 이후에 나오는 'dot(점 . )' 의 인덱스를 반환

SUBSTR

• 문자열을 잘라내어 반환

예시1

이름의 첫글자를 반환

예시2 - 여성 서교동에사는 여성직원들의 정보를 출력

INSTR 함수로 주민번호 가운데 있는 '대시(-)'의 인덱스를 반환받고
SUBSTR 함수로 '대시(-)'의 인덱스부터 1글자 (뒷자리 첫번째)가 2 또는 4라면 여자

SELECT e.jumin, e.ename 사원이름 , m.ename 관리자이름, 
e.email 사원이메일, m.email 관리자이메일, e.dno, 
d.dname, (e.salary +e.COMM)*12 

FROM emp m, emp e, dept d

WHERE (SUBSTR(e.jumin, INSTR(e.jumin,'-')+1, 1) in ('2','4')) AND d.DLOC LIKE '%서교동%'
AND m.eno = e.mgr AND e.dno = d.dno ORDER by e.dno, e.ename;

REPLACE 

• '문자열1'에서 '문자열2'의 글자를 '문자열3'으로 바꿔라

LPAD

• 문자열을 자릿수 만큼 공간을 잡아 오른쪽에 정렬하여 출력하고 나머지 왼쪽 빈칸은 채울글자로 채웁니다.

 

RPAD

• 문자열을 자릿수 만큼 공간을 잡아 왼쪽 정렬하여 출력하고 나머지 오른쪽 빈칸은 채울글자로 채웁니다.

예시1 - 이름의 첫 글자를 출력하여 나머지는 * 로 마스킹

SELECT RPAD(SUBSTR(ename,1,1),LENGTH(ename),'*') FROM emp;

예시2 -아래와 같이 출력하는데 주민번호는 마스킹 처리하라

select e.eno, e.ename, SUBSTR(e.email, 1, INSTR(e.email, '@') -1) id,
rpad(SUBSTR(e.jumin, 1, 8) , 14, '*') jumin,
LPAD(e.salary+e.comm , 10, 0 ) salary
FROM emp e, emp m 
WHERE e.mgr = m.eno AND
e.ename ='김한희'
ORDER BY e.salary + e.comm DESC, e.ename;

날짜 관련 함수

to_date(char, datetime)	문자열 데이터를 날짜형으로 반환 TO_DATE ('2014-02-14', 'yyyy-mm-dd') = 2014-02-14
TO_CHAR (date, datetime)	날짜형 데이터를 문자열로 반환 TO_CHAR (TO_DATE ('2014-02-14', 'yyyy-mm-dd')) = 20140214
ADD_MONTHS(date, 숫자)	date형의 날짜에서 지정한 만큼 더함 (1 : 다음달, -1: 이전달)
LAST_DAY(date)	date형의 날짜에서 월의 마지막 날을 반환 LAST_DAY(TO_DATE('2014-02-14', 'yyyy-mm-dd')) = 2014-02-28
SYSDATE	시스템 상의 오늘 날짜를 반환

SYSDATE

예시 - 어제, 오늘 내일 

TO_CHAR

TO_CHAR (날짜, 형식 문자)
TO_CHAR (TO_DATE ('2014-02-14', 'yyyy-mm-dd')) = 20140214

년 : yyyy / yy
월 : mm 
일: dd
요일 : d ('3' 출력, 화요일 = 3)
요일 : day ('화요일' 출력)
요일 약어 : dy ('화' 출력)
시: hh
분: mi
초: ss

• 날짜형 데이터를 문자열로 반환

사용 예시들

SELECT TO_CHAR(SYSDATE, 'yyyy') FROM dual;  // 2022
SELECT TO_CHAR(SYSDATE, 'yy') FROM dual;    // 22
SELECT TO_CHAR(SYSDATE, 'mm') FROM dual;    // 01
SELECT TO_CHAR(SYSDATE, 'dd') FROM dual;    // 일
SELECT TO_CHAR(SYSDATE, 'hh') FROM dual;    // 시
SELECT TO_CHAR(SYSDATE, 'mi') FROM dual;    
SELECT TO_CHAR(SYSDATE, 'ss') FROM dual;
SELECT TO_CHAR(SYSDATE, 'yyyy-mm') FROM dual;
SELECT TO_CHAR(SYSDATE, 'yyyymm') FROM dual;
SELECT TO_CHAR(SYSDATE, 'yyyy-ss') FROM dual;

TO_DATE

• 문자열 데이터를 날짜형으로 반환

ADD_MONTHS

ADD_MONTH (날짜, 숫자)
- 날짜에 숫자 개월수 만큼 더해 반환

LAST_DAY

LAST_DAY(날짜)
- 이달의 마지막 날을 반환

MONTHS_BETWEEN

• 두 날짜 사이의 개월 수를 반환

MONTHS_BETWEEN (최근 날짜1, 올드 날짜2)

DECODE 함수

DECODE(컬럼, 값1, 결과1, 값2, 결과2, 결과3)

컬럼의 값이 값1이면 결과1를 반환
컬럼의 값이 값2이면 결과2를 반환
그 나머지이면 결과 3을 반환

문제

같은 성씨찾기

SELECT SUBSTR(eNAME,1,1), COUNT(*) FROM emp
GROUP BY SUBSTR(eNAME,1,1);

상관 서브 쿼리 사용해서 나이 구하기

상관 서브 쿼리가 사용된 부분은 3번째 줄에서 사용.
왜 사용했냐면 속성 값으로 조건에 따라 1900을 더할 수도 있고, 2000을 더할 수도 있도록 하기 위해서
상관 서브 쿼리문을 사용

SELECT eno, ename, jumin,
TO_CHAR(SYSDATE, 'yyyy') - (SUBSTR(jumin, 1, 2) + 
(SELECT DECODE( SUBSTR(jumin,8,1), '1',1900, '2',1900, 2000) 
FROM emp e2 where e1.eno = e2.eno)) age
FROM emp e1;

테이블 복사하기

CREATE TABLE 새테이블명 AS SELECT * FROM 복사할테이블명

tip

집계함수 사용시 주의점

• NULL + 숫자 = NULL
• NULL 은 집계함수 실행 시 포함되지 않음
• count는 만족하는 레코드의 값이 없으면 0을 반환
• sum, avg는 값이 없으면 null을 반환