[SQL] 함수(Functions)

1. ORDER BY  (정렬)

 ORDER BY col1, col2...( co1로 정렬,  중복되면 col2로 정렬)

 **컬럼명 뒤에 ASC, DESC를 추가한다

 

  1-1 NULL을 포함하는 컬럼의 정렬

 

  NULL데이터를 원하는 위치에 정렬시키는 법 

  

•  NULLS FIRST : NULL값을 처음으로 
• NULLS LAST : 마지막으로 

SELECT empno, ename, comm FROM emp ORDER BY comm DESC NULLS LAST;
SELECT empno, ename, comm FROM emp ORDER BY comm DESC NULLS FIRST,empno;

 

2. DISTINCT (중복제거)

중복데이터를 제거하는 키워드 이며 SELECT 키워드와 함께쓰인다. SELECT키워드 바로 뒤에 붙여서 사용한다. 

SELECT DISTINCT deptno FROM emp ORDER BY deptno;
 
SELECT DISTINCT deptno,ename FROM emp order by deptno,ename; 
--두개의 컬럼이 모두 같아야 중복제거가 되기때문에 -deptno가 중복제거 안된다

 

3. 기타 여러함수 

3-1 length, lengthb  길이출력함수

 

length : 길이 출력 

lengthb : byte길이 출력

SELECT length('Apple') AS len1 FROM dual;
SELECT length('Apple') len1, length('안녕') len2, length('Hello 오라클')len3 FROM dual;

 

3-2 대소문자, 첫글자 대문자

 

UPPER : 모두 대문자로 변환

LOWER : 모두 소문자로 변환

INITCAP : 첫글자 대문자

-- 'hELlo' 모두 대문자로 변환 -> HELLO
SELECT UPPER('hELlo') FROM dual;
 
-- 'hELlo' 모두 소문자로 변환 -> hello
SELECT LOWER('hELlo') FROM dual;
 
-- 'hELlo' 이니셜(첫글자) 대문자 -> Hello
SELECT INITCAP('hELlo') FROM dual;

 

3-3 문자열의 위치, 문자열 자르기

 

INSTR : 문자열의 위치반환 첫번째는 1이다 (0아님)

SUBSTR : 문자열 자르기

SUBSTR(문자열, 시작위치, 끝위치) : 숫자가 하나만 들어간다면 1번부터 숫자까지 반환한다.

 

-SUBSTR와 INSTR은 B를 붙여주면 Byte 길이로 반환한다.

-- SQL에서는 첫번째가 1이다 
-- 'ABCDEFGHI'에서 'D' 의 위치 -> 4
SELECT INSTR('ABCDEFGHI','D') FROM dual;
 
-- 'ABCDEFGHI'에서 'DEF'문자열의 위치 ->4
SELECT INSTR('ABCDEFGHI','DEF') FROM dual;
 
-- 'ABCDEFGHI'에서 'DF'문자열의 위치 -> 0 --없으면 0
SELECT INSTR('ABCDEFGHI','DF') FROM dual;
 
-- '안녕하세요'에서 '하'문자열의 위치 -> 3
SELECT INSTR('안녕하세요','하') FROM dual;
 
 -- '안녕하세요'에서 '하' 문자열까지 바이트
SELECT INSTRB('안녕하세요','하') FROM dual;
 
-- 'ABCABCDDD'에서 'C'문자열의 위치 -> 3
SELECT INSTR('ABCABCDDD','C') FROM dual;
 
--'Oracle SQL Developer'에서 5번째 인덱스 이후의 문자열로 자르기
SELECT SUBSTR('Oracle SQL Developer',5) FROM dual;
 
--'Oracle SQL Developer'에서 5번째 인덱스부터 5글자로 자르기
SELECT SUBSTR('Oracle SQL Developer',6,5) FROM dual; --5번째 이후
 
--'오라클 SQL'에서 2번째 인덱스부터 5글자로 자르기
SELECT SUBSTR('오라클 SQL',3,5) FROM dual;
SELECT SUBSTRB('오라클 SQL',3,5) FROM dual; --3번째부터 잘라서 5바이트 가져와라
--'안녕하세요오라클'에서 3번째 부터 자르기
SELECT SUBSTR('안녕하세요오라클',3) FROM dual;

 

3-4 패딩(Padding)

 

문자를 표현하기 위해서 공간을 확보하고 문자를 채우는 함수.

 

• LPAD : left padding
• RPAD : right padding

--LEFT PADDING : lpad()
SELECT lpad('SQL',10) FROM dual; --10개의 공간을 만든뒤 오른쪽으로 SQL출력
SELECT lpad(ename,10) FROM emp;  -- 10칸을 확보하고 오른쪽으로 값 출력
-- 글자수보다 공백이 작다면 글자수 만큼만 표현해준다
 
--RIGHT PADDING : rpad()
SELECT rpad('SQL',10) FROM dual; --오른쪽에 10개의 공간을 만든뒤 SQL출력
 
SELECT rpad('SQL',10,'*') FROM dual; --공백대신 *이 들어감

 

3-5 TRIM 공백지우기

 

TRIM : 데이터의 양 끝단에 있는 공백을 제거해줍니다

SELECT '   SQL   ', ltrim('   SQL   '), rtrim('   SQL   '), trim('   SQL   ') FROM dual; --왼쪽공백제거 오른쪽 공백제거 등--
SELECT ltrim(rtrim('   SQL   ')) FROM dual; -- = trim()

 

4. 날짜 함수

날짜를 매개로 사용하는 함수

** Sysdate : 현재 날짜시간을 반환하는 함수이다.

사용방법 : 매개변수와 ()도 미사용

 

**대체로 서식지정자와 함께 사용한다.

 

--날짜함수, DATETIME--
 --sysdate 
 
SELECT sysdate FROM dual; --오늘날짜 출력 20/3/24 :년원일
 
--날짜시간 타입->문자 타입(서식지정)
SELECT to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') now FROM dual; 
 
--MONTHS_BETWEEN : 개월 수 차이
SELECT months_between('20-01-01','20-02-01') A FROM dual; --왼쪽-오른쪽 (1=1개월)
SELECT months_between('20-01-01','20-02-01') A,round(months_between('20-01-01','20-02-15'),2) B FROM dual; 
--round로 반올림
 
--next_day : 지정된 요일이 다가오는 날짜 구하기
SELECT next_day(sysdate,'금') FROM dual;
 
 
--trunc 함수 (버리기)--trunc는 매개변수로 숫자or날짜가능 ** 중요(시간을00시로 버린다)
--trunc(datetime)
SELECT sysdate,to_char(sysdate,'YYYY/MM/DD HH24:MI:SS'),to_char(trunc(sysdate),'YYYY/MM/DD HH24:MI:SS')
 FROM dual;
--20/03/24    2020/03/24 19:43:17    2020/03/24 00:00:00
 
--보이는 결과는 같지만 시간이 포함되어 서로 다른 값을 가진다
SELECT sysdate,trunc(sysdate) FROM dual;

 

 

5. 단일 행 함수

5-1 NVL

 매개변수가 NULL인지 판단하여 NULL이라면 원하는 값을 반환하는 함수

 

5-2 NVL2 

 NVL과 같지만 NULL이 아닐때 원하는 값으로 반환가능

 NVL2 (데이터, NULL이 아닐 경우 반환값, NULL일 경우 반환값)

 

--NVL
 
SELECT empno, ename, NVL(comm,0) as BONUS FROM emp ORDER BY comm DESC,ename;
-- comm(상여금)  NULL ->0으로 치환해준다
 
--NVL2
-- comm이 NULL이면 sal로 치환
-- comm이 NULL이 아니면 sal+comm로 치환
SELECT ename, sal, comm, sal+comm FROM emp; --하나가 NULL이라면 덧셈을 수행하지 않는다
SELECT ename, NVL2(comm,sal+comm,sal)AS pay FROM emp; --comm이 NULL이어도 덧셈을 수행할 수 있게 변경

5-3 NULLIF

  두 개의 값이 같은지 비교하여 같으면 NULL반환

  다르면 첫번째 매개변수를 반환

  

  NULLIF(첫째값, 둘째값)

 

5-4 DECODE && CASE

  비교값을 여러 개 나열하여 비교결과를 반환하는 함수

  기준값과 비교값이 같으면 반환값을 반환

 

** default 값은 생략 가능

** 기준값과 비교값이 같을 때 비교값 바로 다음에 오는 반환값을 반환

 

--NULLIF
 
SELECT
    NULLIF(10,20)"1", --10
     NULLIF(20,10)"2", --20
      NULLIF(10,10)"3" --null
      FROM DUAL;
      
 

--응용
--job컬럼에서 'salesman' 을 찾는다 ->null변환
-- NULL값을 NVL을 이용해 '영업'변환

 
SELECT empno,ename,job, 
    NULLIF(job,'SALESMAN') N_IF, 
    NVL(NULLIF(job,'SALESMAN'),'영업') job_kor 
FROM emp;

#CASE : DECODE와 같은 용도 (CASE를 더 많이 사용한다)

--CASE 구문
--이렇게도 표현 가능하다 
SELECT empno, ename, deptno,
    CASE deptno       
        WHEN 10 THEN '회계팀'
        WHEN 20 THEN '연구팀'
        WHEN 30 THEN '영업팀'
        WHEN 40 THEN '운영팀'
        ELSE '부서없음'
    END as dname --alias는 여기에
FROM EMP;
 
 
SELECT empno, ename, deptno,
    CASE      
        WHEN job=upper('president') THEN'사장'
        WHEN deptno=10 THEN '회계팀'
        WHEN deptno=20 THEN '연구팀'
        WHEN deptno=30 THEN '영업팀'
        WHEN deptno=40 THEN '운영팀'
        ELSE '부서없음'
    END as dname --alias는 여기에
FROM EMP;
 

 

5-5 기타 함수

 

 

 
--COUNT
SELECT count(*) FROM emp;  --12개의 행 --전체 행수
 
SELECT empno FROM emp; --전체 출력
SELECT count(empno) cnt_empno FROM emp; --그룹핑해서 한줄로 12출력
 
SELECT mgr FROM emp; --12개중 null값을 하나 가지고 있다
SELECT count(mgr) FROM emp; --null을 제외하고 11(행의합) 출력
 
SELECT count(comm) FROM emp ORDER BY comm; --4개 (null)값 제외
 
SELECT 1 FROM emp; --1을 12개출력
SELECT count(*) FROM emp; --12
SELECT count(1) FROM emp; --12
 
--SUM
SELECT sum(sal) total FROM emp;
 
--AVG
SELECT round(avg(sal),2)average FROM emp; -- 둘째짜리까지 반올림
 
--MAX
SELECT max(sal) maximum FROM emp;
 
--MIN
SELECT min(sal) minimun FROM emp;
 
SELECT max(ename) FROM emp; --알파벳순서로 가장 큰값
SELECT min(ename) FROM emp; --알파베순서 제일 앞의 값
 
SELECT max(hiredate),min(hiredate) FROM emp; --날짜도 가능(큰게 최근) , (작은게 오래된) --단순 숫자가 큰것
 
 
--전체 sal에 대한 합계
SELECT sum(sal) FROM emp; 
-->변경 -부서별 급여 합계
 
SELECT 
    deptno,
    sum(sal)
FROM emp GROUP BY deptno ORDER BY deptno; --deptno로 그룹을 지어준다
 
--부서별 인원수
 
SELECT
    deptno,
    count(*) cnt
FROM emp GROUP BY deptno;
 
SELECT ename from emp WHERE sal=5000;
 
--부서별+ 직무별 사원수
SELECT 
    deptno,
    job,
    count(*)
FROM emp
GROUP BY deptno,job --두개의 컬럼을 하나처럼 인식
ORDER BY deptno,job;