SQL DAY 2

220511 DAY 19 정리중

■ SQL(Structuered Query Language) 종류

• SQL : 구조화된 DB에 질문하는 언어

종류	설명
DDL(Data Definition Language)	CREATE, ALTER, DROP, RENAME, TRUNCATE
DML(Data Manipulation Language)	INSERT, UPDATE, DELETE, SELECT
DCL(Data Control Language)	GRANT, REVOKE
TCL(Transaction Control Language)	COMMIT, ROLLBACK, SAVEPOINT

 

■ 오라클의 기본 함수 (문자, 수, 날짜)

■ MATH 함수

ceil	올림	select ceil(0.1) from dual;--올림//1
floor	내림	select floor(0.9) from dual;--내림//0
round	반올림	select round(0.5) from dual;--반올림//1
mod	나머지	select mod(49, 5) from dual;--49를 5로 나눈 나머지
trunc	소수점 이하의 자리수를 자른다.	select trunc(0.12346789, 3) from dual;--소수점 3자리 남김//0.123

 

■ 문자열 함수

upper	모두 대문자로	select upper('nice to meet you') from dual;
lower	모두 소문자로	select lower('NICE TO MEET YOU') from dual;
initcap	첫 문자만 대문자로. 이니셜만 대문자로	select initcap('NICE TO MEET YOU') from dual;--Nice To Meet You
length	문자열의 길이를 눈에 보이는 길이로	select length('hello') from dual;--5 select length('안녕하세요') from dual;--5
lengthb	문자열의 길이를 byte 단위로	select lengthb('hello') from dual;--5 select lengthb('안녕하세요') from dual;--15
substr	문자열을 눈에 보이는 글자 수대로 자르기	select substr('nice to meet you', 2, 10) from dual;--ice to mee select substr('안녕하세요', 2, 3) from dual;--녕하세
substrb	문자열을 byte 단위로 자르기	select substrb('nice to meet you', 2, 10) from dual;--ice to mee select substrb('안녕하세요', 4, 3) from dual;--녕
instr	문자열을 눈에 보이는 글자 수대로 찾기	select instr ('nice to meet you', 'to') from dual;--6 select instr('안녕하세요', '하세') from dual; --3
instrb	문자열을 byte단위로 찾기	select instrb ('nice to meet you', 'to') from dual;--6 select instrb('안녕하세요', '하세') from dual; --7
lpad	왼쪽을 지정한 문자로 채워라.	select lpad('hi', 10, '#') from dual;--########hi
rpad	오른쪽을 지정한 문자로 채워라.	select rpad('hi', 10, '$') from dual;--hi$$$$$$$$

 

■ 형 변환 함수

to_char(데이터, '출력 형식')	날짜 또는 숫자를 문자 format으로 변경
to_number()	문자를 숫자로 변경 - 오라클 기능 좋아져서 잘 안씀.
to_date(데이터, 날짜 형식)	문자를 날짜로 변경 (기본 날짜 형식 : YY/MM/DD)

 

■ 날짜 함수

moths_between	날짜와 날짜 사이의 개월 수를 구한다.
add_months	기준 날짜에 개월 수를 추가한 날짜를 계산해 준다.
next_day	가장 가까운 요일의 날짜 반환
last_day	기준이 되는 달의 마지막 날짜
round	날짜 반올림
trunc	날짜 자르기

 

■ 기타 함수 (NVL, NVL2, DECODE, CASE)

NVL (컬럼명, 대체값)	null을 0 또는 다른 값으로 변환.  ※ 컬럼과 대체값의 데이터 타입은 동일해야 한다.
NVL2 (컬럼명, 대체값1, 대체값2)	컬럼이 null이 아니면 대체값1, null이면 대체값2 ※ 컬럼과 대체값의 데이터 타입은 동일해야 한다.
DECODE (컬럼명, 조건, 결과)	여러 조건에 따른결과를 정한다.
CASE WHEN 컬럼 = 조건 THEN 결과 ELSE ~	여러 조건에 따른결과를 정한다.

** 1. NVL : 일종의 if~else문 **
--comm 데이터가 null이면 0으로 대체
select ename, job, sal, comm, nvl(comm, 0) from emp;

--데이터 타입이 다르면 에러
select ename, job, sal, comm, nvl(comm, sysdate) from emp;--error:숫자 타입vs날짜 타입
select ename, job, sal, comm, nvl(comm, '없음') from emp; --error


2. NVL2
select ename, job, sal, comm, nvl2(comm, comm * 12, 0) from emp;
select ename, job, nvl2(job, '직업 있음', '직업 없음') from emp;
select ename, hiredate, nvl2(hiredate, hiredate, sysdate) from emp;


3. DECODE
--부서번호가 10이면 회계로 변환, 그 나머지는 else로 변환
select deptno, decode(deptno, 10, '회계') from emp; -- [DECODE 1]
select deptno, decode(deptno, 10, '회계', 'else') from emp; -- [DECODE 2]
select deptno, decode(deptno, 10, '회계', 20, '기획', 'else') from emp; -- [DECODE 3]
select deptno, decode(deptno, 10, '회계', 20, '기획', 30, '영업', 'else') from emp; -- [DECODE 4]


Q. emp의 모든 데이터를 출력하는데,급여를 CLERK과 ANALYST는 5%, SALESMAN과 MANAGER은 10% 올려서 출력하시오.
select empno, ename, job, mgr, hiredate, sal, comm, deptno
	, case	when job = 'CLERK' then sal + (sal / 100 * 5)
		when job = 'ANALYST' then sal + (sal * 0.05)
		when job = 'SALESMAN' then sal * 1.1
		when job = 'MANAGER' then sal + (sal / 100 * 10)
		else 0 --then과 else의 데이터타입 일치
	end as "월급 인상안"
from emp;


4. CASE
select deptno
	, case	when deptno = 10 then '회계'
		when deptno = 20 then '기획'
		when deptno = 30 then '영업'
		else 'else'
	end
from emp;
, sal * 1.05, sal)as "월급인상" from emp;

DECODE1, DECODE2, DECODE3, DECODE4