[Database] 단일행 함수 / 그룹 함수

[ DBMS - DataBase MAnagement System ]

> 데이터 원활한 CRUD를 위해 사용

> 보안성/ 안정성/ 성능

> DBA - DataBase Administrator

> SQLD 수준으로 학습 목적

[ SQL 명령 체계로 프로그램 사용 ]

DDL : CREATE / DROP / ALTER

DCL : GREANT / revoke

DML : SELECT / INSERT / UPDATE / DELETE

TCL : COMMIT / ROLLBACK / savepoint

[ SELECT ]

WHERE / ORDER BY

LIKE / NOT LIKE / IN / NOT IN / BETWEEN AND / AND / OR / || / IS NULL / IS NOT NULL

컬럼절 연산도 수행 가능 / LITERAL 값도 출력 가능

[ FUNCTION ]

자바에서 MeThod와 같음

1. 단일행 함수( 한 개의 행에 대하여 적용되는 함수 )

 *dual 테이블 : 테스트 쿼리를 실행해보기 위한 더미데이터 테이블(테스트용 테이블)

   

   

   

    1.1 length : 컬럼 값의 길이를 측정하는 함수

        select emp_name length(emp_name) from employee;

        select emp_name lengthb(emp_name) from employee; --lengthb : 컬럼값의 byte 크기 측정

        select lengthb('B') from daul; -- 영어 대문자 하나 1바이트

        select lengthb(가) from dual; -- 문자 하나 3바이트

    1.2 instr : 문자열 내에서 특정 문자의 위치값을 검색하여 반환

        --자바에서는 indexof 라는 메스드로 존재한다.

        INSTR(문자열, 검색문자열, 위치,찾을수 )

        SELECT INSTR('ALL WE NEED TO IS JUST TO...','TO') FROM DUAL;

        -- TO의 위치값 반환, 13반환

        SELECT INSTR('ALL WE NEED TO IS JUST TO...', 'TO', 15) FROM DUAL

        -- 시작위치 15부터 TO를 찾아라

        SELECT INSTR('ALL WE NEED TO IS JUST TO...','TO',1,2) FROM DUAL;

        -- 시작위치 1번째부터 찾는데, 두번 째 TO의 위치값을 찾아라

        select instr( 'Hello World Hi High', 'W', 1, 1) from dual; -- 시작위치 첫글자에서 첫번째로 만나는 W 위치값 --7

   

        Q) 회원의 전화번호에서 두 번째 대시(-) 문자가 존재하는 위치를 출력하시오.

            SELECT INSTR(PHONE, '-',1,2) FROM MEMBERS;

        Q) 회원의 전화번호에서 첫 번째 대시(-) 문자와 두 번째 대시(-) 문자 사이의 간격은?

            SELECT INSTR(PHONE,'-',1,2) - INSTR(PHONE,'-',1,1) -1 FROM MEMBERS;

        Q) 회원의 전화번호에서 첫 번째와 두 번째 사이의 국번을 출력하시오.

            SELECT SUBSTR(PHONE,5,INSTR(PHONE,'-',1,2)-INSTR(PHONE,'-',1,1)-1) FROM MEMBERS;

    1.3 substr : 문자열 내에서 문자열을 추출하는 함수

        *distinct: 중복제거 / 첫 번째 컬럼에 대해서만 사용 가능

   

        SELECT SUBSTR('HELLO',1,3) FROM DUAL; --HELL 출력

        SELECT SUBSTR('HELLO',3) FROM DUAL; -- 3번쨰부터 잘라줘, LLO출력

        SELECT SUBSTRB('HELLO',3) FROM DUAL; --3번째바이트부터 잘라줘 , LLO출력( 영어 한글자당 1BYTE)

        Q) 모든 학생의 이름과 출생 월만을 조회하시오

        SELECT NAME, SUBSTR(BIRTHDAY,6,2) FROM MEMBERS;

        Q) 회원 중에서 전화번호가 011으로 시작하는 회원의 모든 정보를 출력하시오.

        011을 출력하라는 것이 아니고 , WHERE절에서 조건을 추가하여 SELECT에서 모든 정보를 추출해야함

        SELECT*FROM MEMBERS WHERE SUBSTR(PHONE,1,3) = '011';

        SELECT*FROM MEMBERS WHERE PHONE LIKE '011%'; -- 아래가 더 좋은 방법이다.

       

        Q) 회원중에서 생년 월이 7,8,9월인 회원의 모든 정보를 출력하시오

        SELECT*FROM MEMBERS WHERE BIRTHDAY BETWEEN 7 AND 9;-- 오답

        SELECT*FROM MEMBERS WHERE SUBSTR(BIRTHDYA, 6, 2) IN ('07','08','09');

        Q) 전화번호를 등록하지 않은 회원 중에서 생년월일이 7,8,9월인 회원의 모든 정보를 출력하시오

        SELECT*FROM MEMBERS WHERE PHONE IS NULL AND SUBSTR(BIRTHDAY,6,2) IN ('07','08','09');

    1.3.2 CONCAT : 문자열 덧셈 함수

        SELECT CONCAT('홍','길동') FROM DUAL;

        * 문자열 연산

        || --이 연산자를 쓰는 게 훨씬 편리하고 자주 사용됨

    1.4 replace : 문자열 내에서 특정 문자열을 다른 문자열로 대체하는 함수

        SELECT REPLACE('WHERE WE ARE', 'WE','YOU') FROM DUAL;

        -- WHERE YOU ARE 출력, WE가 통채로 YOU로 바뀜

        SELECT TRANSLATE('WHERE WE ARE', 'WE','YOU') FROM DUAL;

        -- W를 Y로 바꿔주고 E를 O로 바꿔줌 U는 아무 의미없는 글자, 오류는 나지 않는다.

        -- 모든 글자를 1대1로 바꿔준다.

        Q) 회원의 이름과 주소를 조회하시오(단, 주소는 빈칸이 없이 출력하시오)

        SELECT NAME, REPLACE(ADDRESS,' ','') FROM STUDENTS;

    1.5 TRIM  : 문자열의 공백을 없애주는 함수

        SELECT LTRIM('   HELLO   ') FROM DUAL; --왼쪽 공백 없애줌

        SELECT RTRIM('   HELLO   ') FROM DUAL; --오른쪽 공백 없어줌

        SELECT TRIM('   HELLO   ') FROM DUAL; --양쪽 공백 없애줌

    1.6 LOWER : 문자열 소문자 또는 대문자로 변경하기

        SELECT LOWER ('NeWIEC') FROM DUAL; -- 모두 소문자로 바꿔줌

        SELECT UPPER('neWLec') FROM DUAL; -- 모두 대문자로 바꿔줌

        Q) 회원의 아이디가 newlec인 회원을 조회하시오(단, 소/대문자를 가리지 않음)

        SELECT*FROM MEMBERS WHERE MID='newlec';

        SELECT*FROM MEMBERS WHERE MID='NEWLEC';

        SELECT*FROM MEMBERS WHERE UPPER(MID) = UPPER('newlec');

    -- 숫자 관련 함수들------------------------------------------------

   

   

   

    2.1 floor : 소수점을 모두 버리는 함수

    2.1.2 tunc : 소수점 특정 자리에서 버리는 함수

    2.2 ceil : 소수점을 잘라내면서 올리는 함수

    2.2..2 round  : 소수점 특정 자리에서 반올림하는 함수

    2.3 abs : 절대값 함수

    2.4 mod : 나눗셈하고 나머지를 반환하는 함수

    -- 날짜 관련 함수들-------------------------------------------------

   

   

   

   

    3.1 sysdate : 현재 시간 반환 - 연월일 시분초를 모두 포함한 timestamp

    3.1.2 localtimestamp : 현재 시간 반환 - 초 이하의 값까지 반환

    3.2 months_between (날짜1, 날짜2) : 두 날짜 사이의 개월 수 차이를 반환

    3.3 add_months (날짜, 정수(개월수)) : 개월수를 추가

    3.4 next_day (날짜, 요일) : 날짜를 기준으로 가장 가까운 요일을 반환해주는 함수

    3.5 last_day(날짜) : 해당 날짜가 속한 달의 마지막 날짜를 반환해주는 함수

    3.6 extract : 날짜 데이터로 부터 연/월/일을 추출하는 함수

        select extract(year from sysdate) from dual;

        select extract(month from sysdate) from dual;

        select extract(day from sysdate) from dual;

   

    -- 형변환 함수들------------------------------------------------------

            TO_CHAR()              TO_DATE()

                --->                --->

    ----------         -----------      -----------

    |   숫자  |  <->   |  문자열  |  <-> |  날짜   |

    ----------         -----------      -----------

                <---                <---

            TO_NUMBER()             TO_CHAR()

    (숫자 -> 문자열)

        select to_char(123456789,'999,999,999') from dual; -- 숫자를 3자리씩 , 를 찍어주기

        select to_char(123456789,'999,999,999.99') from dual; --.99 소수점 두자리 생성

        select to_char(123456789,'L999,999,999.99') from dual; -- L : 로컬 통화 형식으로 출력

       

    (날짜 -> 문자열)

        select to_char( sysdate,'YYYY"년"mm"월"dd"일" (dy) hh24:mi:ss')  from dual;

        - 정해져 있는 문자 외에 다른 값을 추가하고 싶으면 " "을 사용

        select to_char(sysdate, 'yyyymmdd') from dual;

    (문자열 -> 날짜)

    -- 문자열이 어떻게 생겼는지 알려줘야한다

    select to_date('240301','yymmdd') from dual;

   

    Q) 1950년 6월 25일은 무슨 요일인지 출력하세요

    select to_char(to_date('1950625','yyyymmdd'),'YYYY"년"mm"월"dd"일"(day)') from dual;

    -- 날짜 문자열을 날짜 데이터로 변환한 다음, 해당 날짜를 원하는 형식으로 문자열로 출력

    -- 여기서 '19500625'는 날짜를 표현하는 문자열이며

    -- 'yyyymmdd'형식으로 년도, 월, 일을 나타낸다.

   

    -- to_date() 함수는 'yyyymmdd'형식의 날짜 문자열을 날짜 데이터로 변환

    -- to_char() 함수는 날짜 데이터를 지정된 형식으로 문자열로 변환

   

2. 그룹 함수( 전체 행에 대하여 일괄 적용되는 함수 )

    -- 그룹함수들을 사용할 떄는 그룹함수들끼리 출력해야 한다.---------------

    2.1 sum: 컬럼값에 대한 합 도출

    2.2 avg : 컬럼값 전체에 대한 평균

    2.3 max : 컬럼값 전체 중에 제일 큰 값

    3.4 min : 컬럼값 전체 중에 제일 작은 값

   

    3.5 count : 행의 개수를 반환하는 함수

        * 데이터가 null 이 아닌 값만 count한다.

        count(*) : 특정 컬럼에 null 이 있고 없고를 따지지 않고, 그냥 전체 행 개수 반환

   

   

   

    -- 분기함수--------------------------------------------------------

    3.6 decode(대상값, 1,'결과1',2,'결과2'  3, '결과3' ,'결과4') - 가변인자값을 허용함

        //자바에서는 switch문에 해당

    switch(대상값){

        case 1:

            return '결과1';

        case 2:

            return '결과2';

    }

    Q) 사번, 사원명, 퇴사여부를 출력하시오

    - 퇴사하지 않은 직원은, 퇴사여부에 '재직중'이라고 출력하세요

    - 퇴사한 직원은 퇴사여부에 'YY년 MM월 DD일 퇴사'라고 출력하세요

            select

                emp_id as "사번",

                emp_name as "사원명",

            decode(ent_yn, 'Y', '재직중', 'N', to_char(ent_ date, 'YY"년"MM"월"DD"일"')) as "퇴사여부"

            from employee;

    Q) 사원 번호, 사원 명, 성별(남,여)을 출력하세요

            select

                emp_id,

                emp_name,

                decode(substr(emp_no,8,1),1,'남','여')

            from employee;

    3.7 case : decode 보다 더 복잡한 분기점을 구현할 때 사용한다.

        //자바에서는 if문에 해당

        - case와 and는 같이 와야한다.

       

        Q) 사번, 사원명과 나이대를 출력하세요

        - 주민번호 오름차순 정렬로 출력해주세요

        - 60~69사이일 때 (60년대생)

        - 70~79사이일 때 (70년대생)

        - ...

        select

            emp_id 사번,

            emp_name 사원명,

            case    

                when substr(emp_no,1,2) between 60 and 69 then '60년대 생'

                when substr(emp_no,1,2) between 70 and 79 then '70년대 생'

                wher substr(emp_no,1,2) between 80 and 89 then '80년대 생'

            end 나이대

        from employe;

        order by

            emp_no;