#15 집합

 

오늘의 노래!!

 

Harunohi

아티스트

Aimyon

앨범

Harunohi

발매일

1970.01.01

[0] 집합

이런 집합을 이용한 문도 있다라고 생각하고 넘어가기

--(1)집합

-- A집합 : 2조인 사람

SELECT * FROM PYSTU WHERE STU_ZO =2;

-- B집합 : 성이 '김'씨인 사람
SELECT * FROM PYSTU WHERE STU_NUMBER LIKE'김%';

 

--교집합(INTERSECT) : 결과값이 같은 데이터만 출력
SELECT * FROM PYSTU WHERE STU_ZO =2 
INTERSECT
SELECT * FROM PYSTU WHERE STU_NUMBER LIKE'김%';

 

--합집합(UNION) : 모든 결과 데이터를 출력
SELECT * FROM PYSTU WHERE STU_ZO =2 
UNION
SELECT * FROM PYSTU WHERE STU_NUMBER LIKE'김%';

 

--차집합(MINUS) : 먼저 작성한 집합(SELECT)에서
--나중에 작성한 집합(SELECT)의 데이터를 빼고 출력
SELECT * FROM PYSTU WHERE STU_ZO =2 
MINUS
SELECT * FROM PYSTU WHERE STU_NUMBER LIKE'김%';

 

--(2) TRIM() : 공백제거, LTRIM(), RTRIM()

SELECT 
       REPLACE(TRIM('    인천일보 아카데미    '),' ')AS TRIM,
       LTRIM('    인천일보 아카데미    ') AS LTRIM,
       RTRIM('    인천일보 아카데미    ') AS RTRIM
FROM DUAL;

//출력

반올림!!

자리수 버림!!

자신보다 크면 올리고 낮으면 낮춘다! -일때 주의할것!

이거 보면서 이해하기

--(7) 나머지 값 구하기 : MOD(A, B) >>> A % B 랑 같다
--15를 6으로 나눌때 몫과 나머지를 구하시오!!!

SELECT 
FLOOR(15/6) AS 몫,
MOD(15, 6) AS 나머지
FROM DUAL;

//출력값

--1년은 몇주 + 며칠(나머지 일수)로 이루어져 있는가?
SELECT 
FLOOR(365/7) AS 몇주,
MOD(365, 7) ||' 일로 이루어져 있다'
FROM DUAL;

//출력값

--(8) 날짜 관련 함수
-- DATE 날짜 + 숫자 : 숫자만큼 이후 날짜
-- DATE 날짜 - 숫자 : 숫자만큼 이전 날짜
-- DATE 날짜1 - 날짜2 : 두날짜간의 일수 차이
-- DATE 날짜1 + 날짜2 : 연산불가능

 

--날짜 형식 변환
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD'; 

 

SELECT 
    SYSDATE AS "NOW(오늘)",
    SYSDATE+1 AS "TOMORROW(내일)",
    SYSDATE-1 AS "YESTERDAY(어제)"
FROM DUAL;

//출력값

--오늘, 일주일전, 일주일후, 전과후의 일수 구하기

SELECT 
    SYSDATE AS "NOW(오늘)",
    SYSDATE+7 AS "TOMORROW(내일)",
    SYSDATE-7 AS "YESTERDAY(어제)",
    (SYSDATE +7) - (SYSDATE-7) AS "일수차이"
FROM DUAL;

//출력값

오늘 / 내일/ 어제/ 일수차이

--(9) 개월 수 구하기 : MONTHS_BETWEEN(A, B)
--빠른 날짜를 B에 작성

SELECT 
    MONTHS_BETWEEN('2023/08/21','2023-03-21') AS "개월 수"
FROM DUAL;

//출력값

개월수 차이 잘보기!

--(10) 개월 수 더하기 : ADD_MONTHS(A, B)
--A : 날찌, B : 더할 개월 수

SELECT 
    ADD_MONTHS(SYSDATE, 5)
FROM DUAL;

//출력값

 

--(11) 다가올 요일 해당하는 날짜 구하기 : NEXT_DAY(A, B)
--A : 날짜, B : 요일

SELECT
    NEXT_DAY(SYSDATE, '금요일') AS "돌아오는 금요일"
FROM DUAL;

//출력값

--(12) LAST_DAY(A, B) : 이번달 / 특정달의 마지막 일수
--A : 날짜, B : 요일

SELECT
    LAST_DAY(SYSDATE) AS "이번달 마지막일은?" 
FROM DUAL;

//출력값

--원하는 요일 구하는 법!!

SELECT
    LAST_DAY('2024/02/01') AS "2024/2 마지막일은?" 
FROM DUAL;

//출력값

---

--(13) TO_CHAR, TO_NUMBER, TO_DATE

-- 중요한거
/*
(13) TO_CHAR, TO_NUMBER, TO_DATE

    (1) TO_CHAR : 숫자 또는 날짜형대의 데이터를 문자데이터로 전환  -- 가장많이 사용함
        EX) String.valueOf(100)
    
    (2) TO_NUMBER : 문자형태에만 데이터를 숫자데이터로 전환
        ex)Integer.parseInt("100")
        
    (3) TO_DATE : 문자형태의 데이터를 날짜데이터로 전환
    
    YYYY(YEAR) : 년도
    MM(MONTH)  : 월
    DD(DAY)  : 일
    HH(HOUR)  : 시
    MI(MINUTE)  : 분
    SS(SECOND)  : 초
    DAY         : 요일
*/

//기본 예제 코드를 공부~~

SELECT TO_CHAR(SYSDATE, 'MM-DD DAY') FROM DUAL; -- 월일 요일
SELECT TO_CHAR(SYSDATE, 'YYYY') FROM DUAL;  --년도만
SELECT TO_CHAR(SYSDATE, 'YY') FROM DUAL; --년도 끝에만 2023 >>> 23

 

SELECT TO_CHAR(SYSDATE, 'MM')FROM DUAL; --04
SELECT TO_CHAR(SYSDATE, 'MON')FROM DUAL;    --4월

 

SELECT TO_CHAR(SYSDATE, 'D')FROM DUAL;  -- 2 [일주일 기준 일수] (일요일 = 1)
SELECT TO_CHAR(SYSDATE, 'DD')FROM DUAL; -- 10
SELECT TO_CHAR(SYSDATE, 'DDD')FROM DUAL;-- 100 [1년 기준 일수]

 

SELECT TO_CHAR(SYSDATE, 'DAY')FROM DUAL; --월요일
SELECT TO_CHAR(SYSDATE, 'DY')FROM DUAL; -- 월

SELECT TO_CHAR(SYSDATE, 'WW')FROM DUAL; -- 15 (1년 기준52주 중에 15주차)
SELECT TO_CHAR(SYSDATE, 'W')FROM DUAL;  -- 2  (한달 기준 주차)

 

-- 5월 8일은 몇주차?
SELECT TO_CHAR(TO_DATE('2023/05/08'), 'W')FROM DUAL; -- 2

SELECT TO_CHAR(SYSDATE, 'DL')FROM DUAL; -- 2023년 4월 10일 월요일 >> 해당지역 날짜 형식

 

--강제적으로 나타냄 ↓
--TO_CHAR(SYSDATE, 'AM'),(SYSDATE, 'PM') 
SELECT TO_CHAR(SYSDATE, 'AM') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'PM') FROM DUAL;

 

--날짜 계산
SELECT TO_DATE('2023/04/11 00:00:00')- SYSDATE FROM DUAL;
--시간(단위) 남은 시간 구하기
SELECT FLOOR((TO_DATE('2023/04/11 00:00:00')- SYSDATE)*24) FROM DUAL;
--분(단위) 남은 시간 구하기
SELECT FLOOR((TO_DATE('2023/04/11 00:00:00')- SYSDATE)*24*60) FROM DUAL;
--초(단위) 남은 시간 구하기
SELECT FLOOR((TO_DATE('2023/04/11 00:00:00')- SYSDATE)*24*60*60) FROM DUAL;

--4월 11일까지 XX시간 XX분 XX초 남았습니다
SELECT '4월11일까지 '||FLOOR((TO_DATE('2023/04/11 00:00:00')- SYSDATE)*24)||'시간'||
FLOOR((TO_DATE('2023/04/11 00:00:00')- SYSDATE)*24*60)||'분'|| FLOOR((TO_DATE('2023/04/11 00:00:00')- SYSDATE)*24*60*60)||'초 남았습니다!'FROM DUAL;

 

--날짜형 변환!

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD';

 

--TO_DARE()
SELECT 
TO_DATE('2023/08/21') - TO_DATE('2023/03/21')
FROM DUAL;

 

출력값

 

--TO_NUBER() : 문자 데이터를 숫자형 데이터로 변환하는 함수
SELECT
TO_NUMBER('123')-TO_NUMBER('100')
FROM DUAL;

출력값

---

--(14) NULL 처리함수

--[1] NULL 처리함수 : NVL(), NVL2() >>> 실제 데이터 변함 없음!! 출력만
--NVL() : NULL값에 한해 데이터를 바꿔준다.
--NVL([컬럼이름], 0) : 컬럼의 데이터가 NULL경우 0으로 바꿔준다

--NVL2() : NULL값인 데이터와 NULL이 아인 데이터를 바꿔준다.
--NVL([컬럼이름], 'NULL이 아닐경우, 'NULL일 경우'

SELECT PNAME, NVL(AGE, 20)FROM PSTU;

출력값

SELECT PNAME, NVL(AGE, 20), NVL2(PIG, 'IG', 'V30')FROM PSTU;

출력값

--[2] DECODE문 : JAVA에서 switch-case문과 비슷
SELECT ENAME, DEPTNO,
        DECODE( DEPTNO , 10, '인사팀',
                         20, '영업팀',
                         30, '홍보팀') AS 담당부서
FROM EMP
ORDER BY DEPTNO;

 

출력값

 

--[3] CASE - WHEN : JAVA에서 If-else문
SELECT ENAME, DEPTNO,
        CASE 
        WHEN DEPTNO = 10 THEN '인사팀' -- WHEN 조건1 THEN RETURN 1
        WHEN DEPTNO = 20 THEN '영업팀' -- WHEN 조건2 THEN RETURN 2
        WHEN DEPTNO = 30 THEN '홍보팀' -- WHEN 조건3 THEN RETURN 3
        END AS 담당부서
FROM EMP
ORDER BY DEPTNO;

출력값

---

[4] 그룹화 함수 ★★★
    1. SUM() : 지정한 컬럼 데이터 합
    2. AVG() : 지정한 컬럼 데이터 평균
    3. COUNT() : 지정한 컬럼 데이터 갯수
    4. MAX() : 지정한 컬럼 데이터 최댓값
    5. MIN() : 지정한 컬럼 데이터 최솟값

-- EMP테이블로 가지구 함수사용
SELECT * FROM EMP;

--직원들의 급여 합계
SELECT SUM(SAL) AS "전 직원의 급여 합계" FROM EMP;

--직원의 평균 급여를 소숫점 둘째자리까지 출력하여라!
SELECT ROUND(AVG(SAL),2) AS "전 직원의 급여 합계" FROM EMP;

--최고급여와 최저급여, 급여를 받는 사람의 수를 구하여라!
SELECT MAX(SAL)AS "최고" , MIN(SAL)AS"최저", COUNT(SAL) AS "급여 인원수" FROM EMP;

--Q. 부서번호 20번인 사람이 몇명인지, 그들의 급여 합계와 평균을 조회하고,
--가장 많은 급여와 가장 적은 급여를 조회하세요!
SELECT COUNT(SAL) AS "부서 인원수",SUM(SAL) AS "전 직원의 급여 합계",ROUND(AVG(SAL),2) AS "전 직원의 급여 합계", MAX(SAL)AS "최고" , MIN(SAL)AS"최저" 
FROM EMP
WHERE DEPTNO =20;

---

[5] 그룹화 함수 ★★★
    GROUP BY : 특정 컬럼 또는 데이터를 기준응로 데이터를 그룹으로 묶음
    ※ 어떤 것을 묶어야 더 효율적인가 생각
    ※ 그룹을 묶에 되면 기존 컬럼  외에 다른 컬럼은 조회가 불가능하다
        (그룹화 함수 사용가능)
        
    SELECT
    FROM 
    WHERE
    FROUP BY [그룹화 할 컬럼을 지정(여러개 지정가능)]
    ORDER BY [정렬할 컬럼];

--부서별 평균 급여
SELECT DEPTNO, ROUND(AVG(SAL))
FROM EMP
GROUP BY DEPTNO  -- 부서별로 묶겠다!
ORDER BY DEPTNO;

출력값

-- Q. 직급별로 인원수, 급여합계, 급여 평균 구하시오
SELECT JOB,COUNT(*)AS "직급별로 인원수", SUM(SAL)AS"급여합계" ,ROUND(AVG(SAL))AS"급여 평균"
FROM EMP
GROUP BY JOB 
ORDER BY JOB;

--인원수가 많은 직급 순으로 정렬
--ROLLUP : 그룹화 데이터의 합계를 함께 출력
SELECT JOB,COUNT(*)AS "직급별로 인원수", SUM(SAL)AS"급여합계" ,ROUND(AVG(SAL))AS"급여 평균"
FROM EMP
GROUP BY ROLLUP(JOB)  
ORDER BY COUNT(*)DESC;

---

--[6]HAVING : GROUP BY 와 짝꿍! GROUP BY절을 사용해서 그룹화 된 결과 줌
--                그룹을 선별하는 조건식
/*
SELECT 
FROM 
GROUP BY [그룹화 할 컬럼을 지정]
HAVING [묶인 그룹을 제한하는 조건식]
ORDER BY [정렬할 컬럼]

-- 10번 부서와 30번 부서의 직원 중에서 급여가 1500원보다 높은 직원은 몇명?
SELECT DEPTNO, COUNT(*)
FROM EMP
WHERE SAL >1500
GROUP BY DEPTNO
HAVING DEPTNO IN (10,30) -- WHERE절과 같은 조건식!! 그룹바이랑 따라뎅김
ORDER BY DEPTNO;

 

--WHERE절 : 우리가 출력하는 모든 것에 대한 조건을 건다.
--HAVING절 : 그룹화 한 데이터 안에서만 조건을 간다.