20230215 [SQL] 날짜함수, JOIN문

--1.날짜 함수

-- 시스템 날짜를 출력
SELECT SYSDATE FROM dual;	--dual: 컬럼 값을 출력하기 위한 임시 테이블
-- 시스템 날짜를 문자열로 출력 2000-01-01
-- 날짜형을 문자형으로 변환
SELECT TO_CHAR(SYSDATE, 'yyyy-mm-dd') AS 현재날짜 FROM dual;
-- 문자형을 날짜형으로 변환
SELECT TO_DATE('20020101','yyyymmdd') AS 날짜 FROM dual;

[예제]

-- member1230에 writeday컬럼의 날짜형 데이터를 문자형 2023-01-01 형태대로 출력하시오
SELECT TO_CHAR(writeday,'yyyy-mm-dd') AS ymd FROM MEMBER1230;

 

 

-- 특정일에 달 수를 더할 때 add_months()

-- 현재 날짜에서 3개월 후의 날짜를 출력하시오.
SELECT ADD_MONTHS(SYSDATE,3) AS ymd FROM dual;

-- 특정일의 해당 월의 마지막 날짜를 출력 last_day()

SELECT LAST_DAY(SYSDATE) FROM dual;

-- 현재일로부터 6개월 후의 마지막 날짜를 출력하세요
SELECT LAST_DAY(ADD_MONTHS(SYSDATE,6)) FROM dual;

 

-- 요일함수 NEXT_DAY('기준일','찾을요일')

-- 다음 일요일의 날짜를 출력하시오
SELECT SYSDATE,NEXT_DAY(sysdate,1) AS 다음일요일날짜 FROM dual;

 

-- trunc() 첫 날짜를 뽑기

-- 현재일로부터 2개월 후에 첫 날짜를 출력하시오
SELECT TRUNC(ADD_MONTHS(sysdate,2),'mm') FROM dual;
-- 현재일로부터 올해년도 첫 날짜를 출력하시오
SELECT TRUNC(sysdate,'yyyy') FROM dual;

-- 현재 날짜의 3개월 후에/ 그 달의 세번째 토요일 날짜/를 출력하시오 5월20일 출력
SELECT SYSDATE,NEXT_DAY(ADD_MONTHS(sysdate,3),7) FROM dual;
SELECT NEXT_DAY(TRUNC(ADD_MONTHS(SYSDATE,3),'mm')+14,7) FROM dual;

 

 

2. JOIN 문

-- 테이블 생성
-- board1230
CREATE TABLE board1230 (
       bidx NUMBER NOT NULL
              PRIMARY KEY, --숫자형 기본키로 널 값을 허용하지 않으며 다른 행과 구분하는 고유 값이다.
              subject     VARCHAR2(50) NOT NULL,
              content    VARCHAR2(1000) NOT NULL,
              writer   VARCHAR2(20) NOT NULL,
              delYN        CHAR(1) DEFAULT 'n',
              writeday     DATE DEFAULT SYSDATE,
              ip           VARCHAR2(30) NULL,
              midx NUMBER NOT NULL,
              CONSTRAINT midx_fk FOREIGN KEY(midx) REFERENCES member1230(midx)
);
SELECT *FROM board1230;

INSERT INTO board1230(bidx,subject,content,writer,ip,midx) 
VALUES(1,'첫번째글입니다.','첫번째내용입니다.','김민수','111.222.333.444',1);
INSERT INTO board1230(bidx,subject,content,writer,ip,midx) 
VALUES(2,'두번째글입니다.','두번째내용입니다.','행인','222.222.333.444',2);
INSERT INTO board1230(bidx,subject,content,writer,ip,midx) 
VALUES(3,'세번째글입니다.','세번째내용입니다.','아이언맨','222.222.333.555',1);
INSERT INTO board1230(bidx,subject,content,writer,ip,midx) 
VALUES(4,'네번째글입니다.','네번째내용입니다.','토르',null,3);
INSERT INTO board1230(bidx,subject,content,writer,ip,midx) 
VALUES(5,'다섯번째글입니다.','다섯번째내용입니다.','스파이더맨',null,4);
INSERT INTO board1230(bidx,subject,content,writer,ip,midx) 
VALUES(6,'여섯번째글입니다.','여섯번째내용입니다.','슈퍼맨',null,3);
INSERT INTO board1230(bidx,subject,content,writer,ip,midx) 
VALUES(7,'일곱번째글입니다.','일곱번째내용입니다.','원더우먼',null,5);
INSERT INTO board1230(bidx,subject,content,writer,ip,midx) 
VALUES(8,'여덟번째글입니다.','여덟번째내용입니다.','배트맨',null,6);
INSERT INTO board1230(bidx,subject,content,writer,ip,midx) 
VALUES(9,'아홉번째글입니다.','아홉번째내용입니다.','이순신',null,1);
INSERT INTO board1230(bidx,subject,content,writer,ip,midx) 
VALUES(10,'열번째글입니다.','열번째내용입니다.','김순자',null,9);
COMMIT;
SELECT *FROM board1230;

-- inner join (공통된 컬럼으로 묶어서 하나의 테이블로 만드는 작업)

-- 형식 (테이블 A inner join 테이블 B on A.PK컬럼=B.FK컬럼)

-- 3번째글을 쓴 사람의 나이를 알려주세요 
SELECT AS 나이 FROM MEMBER1230 A 
INNER JOIN board1230 B ON A.MIDX=B.MIDX --INNER 생략도 가능
WHERE B.BIDX=3;

같은 의미의 WHERE 조건 사용법

-- JOIN말고 WHERE조건으로 합치기
SELECT TO_CHAR(SYSDATE,'YYYY')-SUBSTR(A.MEMBERBIRTH,1,4) FROM MEMBER1230 A, BOARD1230 B 
WHERE A.MIDX=B.MIDX AND B.BIDX=3;

 

 

[예제]

-- 회원번호 1번인 사람이 쓴 글의 갯수를 출력하시오
SELECT COUNT(CONTENT) FROM MEMBER1230 A INNER JOIN board1230 B ON  A.MIDX=B.midx 
WHERE A.memberName='홍길순';

-- 각 지역별로 게시물을 쓴 사람들의 인원수를 출력하시오
SELECT A.MEMBERADDR,COUNT(DISTINCT A.MIDX)	--DISTINCT : 중복제거
FROM MEMBER1230 A INNER JOIN board1230 B ON A.MIDX=B.midx 
GROUP BY A.memberAddr;

SELECT DISTINCT B.MIDX FROM BOARD1230 B;

 

 

[숙제]

-- 게시글 내용 중에 다섯이라는 글자가 포함된 글을 쓴 사람의 아이디를 출력하시오
SELECT A.MEMBERID 
FROM MEMBER1230 A INNER JOIN board1230 B ON A.MIDX=B.midx
WHERE B.CONTENT LIKE '%다섯%';
-- 회원번호 1번, 8번에 해당하는 사람이 쓴 게시글의 IP를 출력하고 없으면 IP없음으로 출력하시오
SELECT A.MIDX,NVL(B.IP,'IP없음')
FROM MEMBER1230 A INNER JOIN board1230 B ON A.MIDX=B.midx
WHERE a.midx IN(1,8);
-- 게시글 쓴 사람의 닉네임이 3글자이상인 사람의 성별을 출력하시오
SELECT DECODE(A.MEMBERGENDER,'male','남성','female','여성') AS gender
FROM MEMBER1230 A INNER JOIN board1230 B ON A.MIDX=B.midx
WHERE LENGTH(B.writer)>=3;

 

---