[Oracle] SQL 명령어 정리 (3)

2020/04/07 - [DB] - [Oracle] SQL 명령어 정리 (1)

2020/04/23 - [DB] - [Oracle] SQL 명령어 정리 (2)

 

조인

• inner join : 참조키를 기준으로 일치하는 행만 조인

• outer join : 참조키 기준으로 일치하지 않는 행도 포함시키는 조인 (left, right, full)

• self join : 나를 다시 참조하는 조인

-- member id와 notice writer이 같고 inner join 하고 id , name 그리고 회원 별 작성한 게시글 수를 조회하시오
select M.id , M.name, count(n.id) count from member M
join notice N on M.id = n.writer_id
GROUP by m.id,m.name;

-- id , name 그리고 회원 별 작성한 게시글 수를 조회하시오 -> 내부조인시 0이 안나오니깐 외부조인함
select M.id , M.name, count(n.id) count 
from member M
LEFT join notice N on M.id = n.writer_id
GROUP by m.id,m.name;

-- notice의 id, title,writer_id 와 member의 name와 comment의 갯수를 출력하시오. 
select N.id, N.title,N.writer_id, m.name writer_name, count(c.id) count  
from notice N 
left join member M on M.id = n.writer_id 
left join "COMMENT" C on N.id = C.notice_id 
group by n.id, n.title, n.writer_id, m.name;

 

Union, UnionAll, Intersect, Minus

• Union , UnionAll : 레코드를 합치는 것 (합집합)

• join은 컬럼을 추가한다. (다르다)

• Intersect : 공통만 뽑아준다. (교집합)

• Minus : (차집합)

-- 유니온으로 합치는 방법 : id와 writer_id 를 섞어서 출력된다. age + hit가 합쳐서 나온다.
-- 똑같은걸 지워준다. ( 중복제거 ) : 합집합
select id, age from member
union 
select writer_id name , hit from notice;

-- 중복 제거를 하지 않고 합친다. 
select id, age from member
union all
select writer_id name , hit from notice;

-- 공통분모만 뽑아준다. intersect  : 교집합
select id, age from member
INTERSECT
select writer_id name , hit from notice;

-- MINUS : 차집합
select id, age from member
MINUS
select writer_id name , hit from notice;

 

Row_number

• rownum : 실제 컬럼이 아닌 가상 컬럼이다.

-- 랭킹을 정의할때 rank() 사용한다.
select 
row_number() over (order by regdate) num ,
rank() over (order by hit desc) rank,
N.* from notice N;

-- 1부터 10까지 나열해라. 
select *
from (
SELECT 
ROWNUM num, 
N.* FROM 
(select * from notice ORDER BY REGDATE DESC)N 
)
where num between 1 and 10
;

-- 11 부터 20까지  나열해라.
select *
from (
SELECT 
ROWNUM num, 
N.* FROM 
(select * from notice ORDER BY REGDATE DESC)N 
)
where num between 11 and 20
;

- 멤버 id랑 notice의 글쓴이와 같은걸 10개만 보여줘라
select *
from (
SELECT 
ROWNUM num, 
N.* FROM 
(select notice.*, member.name mName
from notice
join member 
on member.id = notice.writer_id
ORDER BY notice.REGDATE DESC)N 
)
where num between 1 and 10
;

 

View

• 가상 테이블이다.

create view notice_view
as
SELECT 
        ROWNUM NUM, 
        N.* 
    FROM (
        SELECT NOTICE.*, MEMBER.NAME WRITER_NAME
        FROM NOTICE 
            LEFT JOIN MEMBER 
            ON MEMBER.ID = NOTICE.WRITER_ID
        ORDER BY NOTICE.REGDATE DESC) N
;

-- 코멘트를 추가해봐라. 
create view notice_view
as
SELECT 
        ROWNUM NUM, 
        N.* 
    FROM (
        SELECT 
            NOTICE.id,
            NOTICE.title,
            NOTICE.writer_id,
            --NOTICE.content,
            NOTICE.regdate,
            NOTICE.hit,
            NOTICE.files, 
            MEMBER.NAME WRITER_NAME, 
            COUNT("COMMENT".ID) CMT_COUNT
        FROM NOTICE
            LEFT JOIN MEMBER ON MEMBER.ID = NOTICE.WRITER_ID
            LEFT JOIN "COMMENT" ON NOTICE.ID = "COMMENT".NOTICE_ID            
        GROUP BY NOTICE.id,
                NOTICE.title,
                NOTICE.writer_id,
                --NOTICE.content,
                NOTICE.regdate,
                NOTICE.hit,
                NOTICE.files,
                MEMBER.NAME
        ORDER BY NOTICE.REGDATE DESC) N;