[6주차] 17. 쿼리문 사용시 사용할 수 있는 주요함수(그룹함수 + 기타)***

그룹함수 : 숫자들의 집합을 이용해 연산이 수행되는 함수

* 그룹함수는 where 조건에 사용할 수 없음. 그룹함수는 having 조건에 쓸 수 있음.

* 모든 그룹 함수는 null을 포함하지 않는다. 단, count(필드) 말고 count(*) 은 null 을 포함하여 연산한다.

 

1. sum(필드) : 합계

SQL> select sum(salary) "급여 총합계" from members;

급여 총합계
-----------
    1004000

2. avg(필드) : 평균

SQL> select avg(salary) "급여 평균" from members;

 급여 평균
----------
    100400

3. min(필드) : 최소값

SQL> select min(salary) "급여 최소값" from members;

급여 최소값
-----------
        100

4. max(필드) : 최대값

SQL> select max(salary) "급여 최대값" from members;

급여 최대값
-----------
    1000000

5. count(필드) : 해당 필드에서 null이 들어가있지 않은 값의 개수를 반환

SQL> select count(email) "email값의 개수" from members;

email값의 개수
--------------
             7

6. count(*) : 한 행의 모든 필드의 값이 null 이더라도 개수를 세며, 전체 행의 개수를 반환한다.

select count(*) 행갯수 from members;

    행갯수
----------
        10

---

7. distinct(필드) : 중복을 제거하고 가져오고 싶을 때 - 이건 함수는 아니고 식별자

select distinct(gender) from members;
select distinct gender from members; -- 둘다 가능

GENDER
------------
여자
남자

8. mod(a,b) : 나머지 구하는 함수

select mod(13,4) from dual;

 MOD(13,4)
----------
         1
         
-- 생일이 짝수월인 레코드 조회
select name, birth from members where mod(to_char(birth, 'mm'),2) = 0;

NAME   BIRTH
------ --------
제시카 90/12/25
티파니 85/12/03
수영   78/08/21
효연   97/02/09
효연   88/06/19
제시카 91/12/02
써니   91/12/02
유리   91/12/02
수영   22/06/10

--id가 5의 배수인 행의 depart 콜럼 값을 '홍보부' 로 변경
update members set depart = '홍보부' where mod(id,5) = 0;

9. round(a), round(a,b) : a를 반올림 하는 함수

select round(789.54321), round(0.987), round(0.12345, 3) from dual;

ROUND(789.54321) ROUND(0.987) ROUND(0.12345,3)
---------------- ------------ ----------------
             790            1             .123
             
             
             
select round(789.24321), round(0.387), round(0.12365, 3) from dual;

ROUND(789.24321) ROUND(0.387) ROUND(0.12365,3)
---------------- ------------ ----------------
             789            0             .124

10. ceil(a) : 소수점 아래에 0이 아닌 숫자가 있다면 무조건 올림하는 함수

select ceil(789.2)from dual;

CEIL(789.2)
-----------
        790

11. floor(a) : 소수점 아래를 무조건 버림하는 함수

select floor(789.2)from dual;

FLOOR(789.2)
------------
         789
         
select round(avg(salary),2)from company;

ROUND(AVG(SALARY),2)
--------------------
                4500

12. lower('abc'), upper('abc') : 각각 소문자, 대문자로 바꾸는 함수

select lower('bANcaA'), upper('abBfFgH') from dual;

LOWER('BANCA UPPER('ABBFFGH
------------ --------------
bancaa       ABBFFGH

--문자열 패턴 비교 할 때 대소문자가 일치해야 해서 lower 해주고 비교하는 SQL문
select * from members where lower(email) like '%daum%';

 ID NAME       SALARY BIRTH    EMAIL           PH                     KOR GENDER
--- ------ ---------- -------- --------------- --------------- ---------- ------
  4 효연          800 97/02/09 ccc@DAum.net    010-1234-5678              남
  
  
--문자열 패턴 비교 할 때 대소문자가 일치해야 해서 패턴에도 lower 해주고 비교
select * from members where lower(email) like lower('%dAuM%');

13. length('abc'), lengthb('abc') : 공백포함 문자 개수, 문자열을 저장하는데 필요한 byte수

select length('카페 라떼'), length('cafe latte'), lengthb('카페 라떼'), lengthb('cafe latter') from dual;

LENGTH('카페라떼') LENGTH('CAFELATTE') LENGTHB('카페라떼') LENGTHB('CAFELATTE')
------------------ ------------------- ------------------- ---------------------
                 5                  10                  13                    10
                 
--이름이 3글자인 레코드 조회
select * from members where length(name)=3;
select * from members where name like '_ _ _;'

14. to_date(필드) : 문자열 -> 날짜 변환

--문자열의 날짜를 날짜타입으로 만들고 싶을때 to_date()를 사용
select to_date('91/10/12') from dual;

15. to_char(날짜) : 날짜 -> 문자열 변환

--날짜를 내가 원하는 포멧의 문자열로 만들고 싶을때 to_char()를 사용
select to_char(sysdate, 'yyyy_mm/dd hh:Mi:ss') from dual;

--date 타입의 birth 필드에서 연도만 가져오고 싶을때
select to_char(birth, 'yy') from members;
select to_char(birth, 'yyyy') YEAR from members;

YEAR
--------
1990
1985
1978
1997
1988
1991
1991
1991
1972
2022


--date 타입의 birth 필드에서 연도만 가져와서 나이를 구하는 것 -> 나이구할 땐 포멧을 yy 로 하면 마이너스 나오므로 이렇게 하자
select name, to_char(sysdate, 'yyyy')-to_char(birth, 'yyyy') AGE from members;

NAME          AGE
------ ----------
제시카         32
티파니         37
수영           44
효연           25
효연           34
제시카         31
써니           31
유리           31
윤아           50
수영            0

--나이가 40 이상인 사람만을 출력하는 쿼리문
select name, to_char(sysdate, 'yyyy')-to_char(birth, 'yyyy') AGE from members where to_char(sysdate, 'yyyy')-to_char(birth, 'yyyy') >= 40;

NAME          AGE
------ ----------
수영           44
윤아           50

--생일이 6월인 레코드 name, birth 조회
select name, birth from members where to_char(birth, 'mm') = '06';

NAME   BIRTH
------ --------
효연   88/06/19
수영   22/06/10

--생일이 6월인 레코드 name, birth 조회, 숫자랑 비교해도 잘 작동함
select name, birth from members where to_char(birth, 'mm') = 6;

NAME   BIRTH
------ --------
효연   88/06/19
수영   22/06/10

--요일 출력1
select to_char(sysdate, 'dy') from dual;

TO_CHAR(
--------
화

--요일 출력2
select to_char(sysdate, 'day') from dual;

TO_CHAR(SYSDATE,'DAY')
------------------------
화요일

--특정 일자의 요일 출력 
select to_char(to_date('98-12-26'), 'day') from dual;

TO_CHAR(TO_DATE('93-12-2
------------------------
수요일

---

사용 추가예시

-- 여자 급여 평균
select avg(salary) "여자 급여 평균" from members where gender = '여자';

-- 90년대생 급여 최대값
select max(salary) "90년대생 급여 최대값" from members where birth like '9%';

-- 연달아 2개의 메소드를 사용해본 결과
select sum(salary), avg(salary) from members;

SUM(SALARY) AVG(SALARY)
----------- -----------
    1004000      100400
    
-- 연달아 2개의 메소드를 사용해본 결과
select sum(salary) as 급여합계, avg(salary) as 급여평균 from members;

  급여합계   급여평균
---------- ----------
   1004000     100400
   
-- 연달아 2개의 메소드를 사용해본 결과(별칭 사용 as를 생략한 방법)
select sum(salary) 급여합계, avg(salary) 급여평균 from members;

  급여합계   급여평균
---------- ----------
   1004000     100400
   
-- 연달아 2개의 메소드를 사용해본 결과(별칭 사용 as를 생략한 방법, 별칭에 공백 있으면 "" 사용해야함)
select sum(salary) "급여 합계", avg(salary) "급여 평균" from members;

 급여 합계  급여 평균
---------- ----------
   1004000     100400