[네이버클라우드캠프] 61일차. MySQL에서의 조인(JOIN), ERD(테이블 관계도), 합집합, 교집합, 차집합, GROUP BY와 HAVING을 포함한 데이터베이스 작성과 예제

* 테이블 ERD (테이블 관계도)

Exam05.sql (eomcs-docs) 참고

읽는 법 예시 )

 

memb 에는 PK로 학생, 매니저, 강사 등등이 있다. 

이것은 mgr에 FK로 사용된다. 왜냐하면 memb에는 학생, 매니저, 강사가 있지만,

mgr에는 대리, 사원 등의 직급 정보가 있기 때문에 0 또는 1로 memb가 구분된다.

또한 memb의 mno는 mgr에서 외래키로 사용된다

 

# 멤버

# 주소

# 매니저

# 수강신청 

# 강의실

# 강사

---

* ALTER 명령어

-- 강의배정
ALTER TABLE lect_tcher
    ADD CONSTRAINT PK_lect_tcher -- 강의배정 기본키
        PRIMARY KEY (
            lno, -- 강의번호
            mno  -- 강사번호
        );

-- 수강생
ALTER TABLE stnt
    ADD CONSTRAINT FK_memb_TO_stnt -- 멤버 -> 수강생
        FOREIGN KEY (
            mno -- 수강생번호
        )
        REFERENCES memb ( -- 멤버
            mno -- 멤버번호
        );

-- 강사
ALTER TABLE tcher
    ADD CONSTRAINT FK_memb_TO_tcher -- 멤버 -> 강사
        FOREIGN KEY (
            mno -- 강사번호
        )
        REFERENCES memb ( -- 멤버
            mno -- 멤버번호
        );

-- 강의
ALTER TABLE lect
    ADD CONSTRAINT FK_mgr_TO_lect -- 매니저 -> 강의
        FOREIGN KEY (
            mno -- 매니저번호
        )
        REFERENCES mgr ( -- 매니저
            mno -- 매니저번호
        );

-- 강의
ALTER TABLE lect
    ADD CONSTRAINT FK_room_TO_lect -- 강의실 -> 강의
        FOREIGN KEY (
            rno -- 강의실번호
        )
        REFERENCES room ( -- 강의실
            rno -- 강의실번호
        );

-- 매니저
ALTER TABLE mgr
    ADD CONSTRAINT FK_memb_TO_mgr -- 멤버 -> 매니저
        FOREIGN KEY (
            mno -- 매니저번호
        )
        REFERENCES memb ( -- 멤버
            mno -- 멤버번호
        );

-- 수강신청
ALTER TABLE lect_appl
    ADD CONSTRAINT FK_stnt_TO_lect_appl -- 수강생 -> 수강신청
        FOREIGN KEY (
            mno -- 수강생번호
        )
        REFERENCES stnt ( -- 수강생
            mno -- 수강생번호
        );

-- 수강신청
ALTER TABLE lect_appl
    ADD CONSTRAINT FK_lect_TO_lect_appl -- 강의 -> 수강신청
        FOREIGN KEY (
            lno -- 강의번호
        )
        REFERENCES lect ( -- 강의
            lno -- 강의번호
        );

-- 강의실사진
ALTER TABLE room_phot
    ADD CONSTRAINT FK_room_TO_room_phot -- 강의실 -> 강의실사진
        FOREIGN KEY (
            rno -- 강의실번호
        )
        REFERENCES room ( -- 강의실
            rno -- 강의실번호
        );

-- 멤버
ALTER TABLE memb
    ADD CONSTRAINT FK_addr_TO_memb -- 주소 -> 멤버
        FOREIGN KEY (
            ano -- 주소번호
        )
        REFERENCES addr ( -- 주소
            ano -- 주소번호
        );

-- 강의배정
ALTER TABLE lect_tcher
    ADD CONSTRAINT FK_tcher_TO_lect_tcher -- 강사 -> 강의배정
        FOREIGN KEY (
            mno -- 강사번호
        )
        REFERENCES tcher ( -- 강사
            mno -- 강사번호
        );

-- 강의배정
ALTER TABLE lect_tcher
    ADD CONSTRAINT FK_lect_TO_lect_tcher -- 강의 -> 강의배정
        FOREIGN KEY (
            lno -- 강의번호
        )
        REFERENCES lect ( -- 강의
            lno -- 강의번호
        );

 

---

* mysql 파일로 열기 !

 

1. prowrshell 을 실행하고 실행 할 sql 파일이 있는 폴더 경로로 이동하여, sql 실행

2. 사용 할 DB를 선택해주고 명령어 타이핑

 

source Examp05.sql

 

 

3. DB가 로드되었는지 show tables를 통해 확인

 

show tables;

---

* 중요 ! sql 명령어 실행순서

-- order by 에서 컬럼을 지정할 때 select 절에 선택된 컬럼이 아니더라도 지정할 수 있다.
-- 즉 select 절에 있는 컬럼 또는 테이블 컬럼을 지정할 수 있다.

select rno, name, concat(loc, '-', name) as name2
from room
order by loc asc, name2 asc;

-- 실행 순서: from -> where -> select -> order by
-- 1) from 또는 join : 테이블의 전체 데이터 또는 조인 데이터 
-- 2) where : 조건에 따라 결과로 뽑을 데이터를 selection 한다.
-- 3) group by : 조건에 따라 뽑은 데이터를 특정 컬럼을 기준으로 데이터를 묶는다.
-- 4) having : 그룹으로 묶은 데이터를 조건에 따라 선별한다.
-- 5) select : 최종 결과로 뽑을 컬럼을 표시(projection)한다. 표현식으로 계산한 컬럼도 포함시킨다.
-- 6) order by : select 절에서 추가한 임의 컬럼이나 테이블 컬럼을 기준으로 정렬한다.
-- 7) limit : 결과 데이터에서 지정한 범위의 데이터를 선택한다.
-- 8) 결과 추출: 7번을 수행한 결과 데이터에서 5번에 표시된 컬럼만 추출한다.

 

예시 ) 

---

* 복잡한 형식으로 출력할 경우 라벨명(별명)을 부여한다.

    예) 강의실명(지점명)*/

select concat(name, '(', loc, ')')  강의실지역
from room;

 

 

---

* union 과 union all / 차집합과 교집합

 

/* union 과 union all */

/* select 결과 합치기
   union : 중복 값 자동 제거*/
select distinct bank from stnt
union
select distinct bank from tcher;

/* union all: 중복 값 제거 안함*/
select distinct bank from stnt
union all
select distinct bank from tcher;

 

 

/* 차집합
   mysql 은 차집합 문법을 지원하지 않는다.
   따라서 다음과 기존의 SQL 문법을 사용해서 처리해야 한다.
*/
select distinct bank
from stnt
where not bank in (select distinct bank from tcher);

 

 

/* 교집합
   mysql 은 교집합 문법을 지원하지 않는다.
   따라서 다음과 기존의 SQL 문법을 사용해서 처리해야 한다.
*/
select distinct bank
from stnt
where bank in (select distinct bank from tcher);

 

 

---

* Join

* 조인
=> 서로 관련된 테이블의 데이터를 연결하여 추출하는 방법

=> 기법
1) CROSS 조인(=Cartesian product)

-- 1) CROSS 조인(=Cartesian product) - 두 테이블의 데이터를 1:1로 모두 연결한다.

select b.bno, title, content, fno, filepath, a.bno
from board1 as b cross join attach_file1 as a;

 

 

2) NATURAL 조인
-- board3의 no와 attach_file3의 no는 PK/FK 관계가 아니다.
-- 그럼에도 불구하고 이름이 같기 때문에 이 컬럼을 기준으로 데이터를 연결한다.

select b.no, title, content, a.no, filepath, bno
from board3 b natural join attach_file3 a;

 

-- 고전 문법 : 
-- 고전 문법에서는 where 절의 조건으로 두 테이블의 조인 기준이 되는 컬럼 값을 검사하기 때문에 
-- 실행 결과는 정상적으로 나온다.

select b.no, title, content, a.no, filepath, bno
from board3 b, attach_file3 a
where b.no = a.bno;

 

3) JOIN ~ USING(컬럼명)

-- 3) JOIN ~ USING
--    같은 이름을 가진 컬럼이 여러 개 있을 경우 USING을 사용하여 컬럼을 명시할 수 있다.

select b.bno, b.title, content, a.fno, a.title, a.bno
from board4 b join attach_file4 a using (bno);

 

-- join ~ using 의 한계
-- => 두 테이블에 같은 이름의 컬럼이 없을 경우 연결하지 못한다.

select no, title, content, fno, filepath, bno
from board5 b join attach_file5 a using (bno);

 

 

4) JOIN ~ ON

-- 4) JOIN ~ ON
--  조인 조건을 on에 명시할 수 있다

--  조건에 일치하는 경우에만 두 테이블의 데이터를 연결한다.
--  이런 조인을 'inner join' 이라 부른다.

select no, title, content, fno, filepath, bno
from board5 b join attach_file5 a on b.no=a.bno;

 

 

-- SQL 문에서도 inner join 이라 기술할 수 있다.
-- 물론 inner를 생략할 수도 있다

select no, title, content, fno, filepath, bno
from board5 b inner join attach_file5 a on b.no=a.bno;

 

5) OUTER JOIN
--    조인 조건에 일치하는 데이터가 없더라도 두 테이블 중에서 한 테이블의 데이터를 
--    결과로 포함시키는 명령이다.
--    문법:
--         select  컬럼명, 컬럼명, ...
--         from 테이블1 t1 [left|right] outer join 테이블2 t2 on t1.컬럼=t2컬럼
--    left outer join => 왼쪽 테이블의 데이터는 반드시 포함시키라는 뜻이다.
--    right outer join => 오른쪽 테이블의 데이터를 반드시 포함시키는 뜻이다.

select no, title, content, fno, filepath, bno
from board5 b left outer join attach_file5 a on b.no=a.bno
order by no desc;

 

---

* 조인 연습문제

-- 조인 연습

-- 문제:
--   모든 멤버의 번호와 이름을 출력하라!
--   단 학생의 경우 재직여부도 출력하라!

-- 1) 모든 멤버 데이터 출력하기
select mno, name
from memb;

-- 2) 학생 데이터를 가져와서 연결하기
select mno, name, work
from memb natural join stnt;

-- 3) join ~ using으로 연결하기
select mno, name, work
from memb join stnt using(mno);

-- 4) 고전 문법으로 연결하기
select memb.mno, name, work
from memb, stnt
where memb.mno=stnt.mno;

-- 5) inner join ~ on 연결하기
select memb.mno, name, work
from memb inner join stnt on memb.mno=stnt.mno;

-- 6) inner 생략하기
select memb.mno, name, work
from memb join stnt on memb.mno=stnt.mno;

-- 7) 테이블에 별명 부여하기
select m.mno, name, work
from memb m join stnt s on m.mno=s.mno;

/*
안타깝게도 위의 SQL문은 학생 목록만 출력한다.
왜? 
- memb테이블의 데이터와 stnt 테이블의 데이터를
  연결할 때 mno가 같은 데이터만 연결하여 추출하기 때문이다.
해결책!
- 상대 테이블(stnt)에 연결할 대상(데이터)이 없더라도
  select에서 추출하는 방법 
*/

-- 8) outer join ~ on 으로 연결하기
select m.mno, name, work
from memb m left outer join stnt s on m.mno=s.mno;

---

* 조인 연습문제2

-- 조인 연습

-- 문제:
--   다음의 결과가 출력될 수 있도록 수강 신청 데이터를 출력하시오!
--   수강신청번호, 강의명, 학생명, 재직여부, 수강신청일, 강의실명, 매니저명, 직위

/* 1단계: 수강신청 데이터를 출력 */
select la.lano, la.lno, la.mno, la.rdt
from lect_appl la;

 

 

/* 2단계: 수강신청한 학생의 번호 대신 이름을 출력 */
select la.lano, la.lno, m.name, la.rdt
from lect_appl la
     inner join memb m on la.mno=m.mno;

 

 

/* 3단계: 수강 신청한 학생의 재직 여부 출력
 * => inner join 에서 inner는 생략 가능
 */
select la.lano, la.lno, m.name, s.work, la.rdt
from lect_appl la
        join memb m on la.mno=m.mno
        join stnt s on la.mno=s.mno;

 

 

/* 4단계: 수강신청한 강의 번호 대신 강의명을 출력 */
select la.lano, l.titl, m.name, s.work, la.rdt, l.rno
from lect_appl la
        join memb m on la.mno=m.mno
        join stnt s on la.mno=s.mno
        join lect l on la.lno=l.lno;

 

 

/* 5단계: 강의실 이름을 출력한다.
 * => 강의실 번호는 lect 테이블 데이터에 있다.
 * => 강의실 이름은 room 테이블 데이터에 있다.
 */
select la.lano, l.titl, m.name, s.work, la.rdt, r.name, l.mno
from lect_appl la
        join memb m on la.mno=m.mno
        join stnt s on la.mno=s.mno
        join lect l on la.lno=l.lno
        left outer join room r on l.rno=r.rno;

 

 

/* 6단계: 매니저 이름을 출력
 * => 매니저 번호는 lect 테이블에 있다.
 * => 매니저 이름은 memb 테이블에 있다.
 */
select
  la.lano,
  l.titl,
  m.name member_name,
  s.work,
  la.rdt,
  r.name room_name,
  m2.name manager_name
from lect_appl la
        join memb m on la.mno=m.mno
        join stnt s on la.mno=s.mno
        join lect l on la.lno=l.lno
        left outer join room r on l.rno=r.rno
        left outer join memb m2 on l.mno=m2.mno;

 

 

/* 7단계: 매니저의 직위 출력
 * => 매니저 번호는 lect 테이블 있다.
 * => 매니저 직위는 mgr 테이블에 있다.
 */
select
  la.lano,
  l.titl,
  m.name snm,
  s.work,
  la.rdt,
  r.name rnm,
  m2.name mnm,
  mr.posi
from lect_appl la
        join memb m on la.mno=m.mno
        join stnt s on la.mno=s.mno
        join lect l on la.lno=l.lno
        left outer join room r on l.rno=r.rno
        left outer join memb m2 on l.mno=m2.mno
        left outer join mgr mr on l.mno=mr.mno;

 

---

* GROUP BY, HAVING 

/* 데이터를 특정 컬럼을 기준으로 그룹으로 묶어 질의하기
=> group by ~ having ~
*/

 

/* 각 지점별 강의실 수 구하기*/
-- 1단계: 강의실 목록 구하기
select
    r.rno,
    r.loc,
    r.name
from
    room r;

 

 

-- 2단계: 지점정보를 저장한 컬럼을 기준으로 그룹으로 묶는다.
select
    -- r.rno, -- 그룹으로 묶인 경우 그 그룹의 첫 번째 데이터 값만 출력한다.
    r.loc
    -- r.name -- 그룹으로 묶인 경우 그 그룹의 첫 번째 데이터 값만 출력한다.
from
    room r
group by
    r.loc;

 

 

-- 3단계: group by를 통해 데이터를 그룹으로 묶은 경우
--        개별 항목의 값을 나타내는 컬럼의 값은 의미가 없기 때문에 제거한다.
select
    r.loc
from
    room r
group by
    r.loc;

 

 

-- 4단계: 그룹으로 묶은 경우 그룹 관련 함수를 사용할 수 있다.
select
    r.loc,
    count(*) as cnt
from
    room r
group by
    r.loc;

 

 

-- 5단계: group by의 결과에서 최종 결과를 선택할 조건을 지정하고 싶다면
--       having절을 사용한다.

-- 예1) having 절에서 집합 함수 사용
select
    r.loc,
    count(*) as cnt
from
    room r
group by
    r.loc
having
    count(*) > 3; -- 집합 함수, group by 조건 컬럼, select 절의 컬럼 사용 가능

 

 

-- 예2) having 절에서 group by 조건 컬럼 사용
select
    count(*) as cnt
from
    room r
group by
    r.loc
having
    r.loc = '강남'; -- 집합 함수, group by 조건 컬럼, select 절의 컬럼 사용 가능

 

 

-- 예3) having 절에서 select 절 컬럼 사용
select
    count(*) as cnt
from
    room r
group by
    r.loc
having
    cnt > 3; -- 집합 함수, group by 조건 컬럼, select 절의 컬럼 사용 가능