JAVA 42일차 (2023-07-20) 자바 프로그래밍_DBMS_SQL - join(조인), 서브쿼리, 그룹

- 자바 프로그래밍
  - JDBC 프로그래밍(com.eomcs.jdbc)
    - SQL - DQL 사용법: 조인, 서브쿼리, 그룹

study 계정으로 MySQL 접속 후 studydb 를 기본 데이터베이스로 설정

 

테이블 생성 및 데이터 준비

멤버 테이블 생성

 

멤버 유니크 인덱스 생성

 

멤버 인덱스 생성

멤버 테이블 구조 변경

ㄴ mno 컬럼을 AUTO_INCREMENT 를 이용하여 자동 증가 컬럼으로 지정하도록 수정

 

주소 테이블 생성

주소 테이블 구조 변경

ㄴ 주소 기본키 추가

 

주소 인덱스 생성

 

주소 테이블 구조 변경

ㄴ ano 컬럼을 AUTO_INCREMENT 를 이용하여 자동 증가 컬럼으로 지정하도록 수정

=>

 

강의실 테이블 생성

 

강의실 테이블 구조 변경

ㄴ  강의실 기본키 추가

 

강의실 유니크 인덱스 생성

 

강의실 테이블 구조 변경

ㄴ rno 컬럼을 AUTO_INCREMENT 를 이용하여 자동 증가 컬럼으로 지정하도록 수정

=>

매니저 테이블 생성

 

매니저 테이블 구조 변경

ㄴ 매니저 기본키 추가

ㄴ mgr 테이블의 mno 컬럼이 memb 테이블의 mno 컬럼을 참조하도록 하는 참조키 추가

 

강의 테이블 생성

 

강의 테이블 구조 변경

ㄴ 강의 기본키 추가 

 

강의 인덱스 생성

 

강의 테이블 구조 변경

ㄴ lno 컬럼을 AUTO_INCREMENT 를 이용하여 자동 증가 컬럼으로 지정하도록 수정

=>

 

강의 테이블 구조 변경

ㄴ lect 테이블의 mno 컬럼이 mgr 테이블의 mno 컬럼을 참조하도록 하는 참조키 추가

ㄴ lect 테이블의 rno 컬럼이 room 테이블의 rno 컬럼을 참조하도록 하는 참조키 추가

 

수강생 테이블 생성

 

수강생 테이블 구조 변경

ㄴ 수강생 기본키 추가

 

수강생 유니크 인덱스 생성

ㄴ 통장번호와 은행명을 함께 묶어서 유니크 인덱스로 사용

=>

 

수강생 테이블 구조 변경

ㄴ stnt 테이블의 mno 컬럼이 memb 테이블의 mno 컬럼을 참조하도록 하는 참조키 추가

 

강사 테이블 생성

 

강사 테이블 구조 변경

ㄴ 강사 기본키 추가

 

강사 유니크 인덱스 생성

=>

강의배정 테이블 생성

ㄴ 위에서 실수로 만들어서 발생하는 오류임

 

강의배정 테이블 구조 변경

ㄴ 강의배정 기본키 생성 => lno(강의번호), mno(강사번호) 를 묶어서 기본키로 설정

=>

ㄴ lno(강의번호), mno(강사번호) 각각이 기본키가 아닌 둘을 묶어서 기본키로 설정한 것임

 

강의배정 테이블 구조 변경

ㄴ lect_tcher 테이블의 mno 컬럼이 tcher 테이블의 mno 컬럼을 참조하도록 하는 참조키 추가

ㄴ lect_tcher 테이블의 lno 컬럼이 lect 테이블의 lno 컬럼을 참조하도록 하는 참조키 추가

=>

 

강의실 사진 테이블 생성

 

강의실 사진 테이블 구조 변경

ㄴ 강의실 사진 기본키 생성

ㄴ rpno 컬럼을 AUTO_INCREMENT 를 이용하여 자동 증가 컬럼으로 지정하도록 수정

ㄴ room_phot 테이블의 rno 컬럼이 room 테이블의 rno 컬럼을 참조하도록 하는 참조키 추가

 

현재까지 생성한 모든 테이블을 삭제한 후 파일 실행하여 테이블 생성하기

=>

ㄴ drop 한 번으로는 자식 테이블 때문에 모두 지워지지 않으므로 한 번 더 drop 해주기

=>

=>

ㄴ 모두 삭제됨

 

source 명령을 이용하여 sql 파일 실행하여 테이블 생성

ㄴ MacOs 에서 iTerm 을 사용하는 경우 주석 때문에 해당 방법은 오류가 발생할 수 있으므로 기본 터미널에서 진행하자!

=>

ㄴ 테이블이 잘 생성됨을 확인

 

source 명령을 이용하여 sql 파일 실행하여 데이터 추가

=>

모든 데이터를 가져오기

 

all은 생략할 수 있음

 

중복 값을 한 개만 추출할 때 distinct 를 붙임

 

컬럼이 2 개 이상일 때 그 컬럼들의 값이 중복될 경우만 한 개로 간주함

 

order by

기본 인덱스 컬럼을 기준으로 정렬

 

이름의 오름 차순(ascending)으로 정렬하기

 

asc는 생략 가능

 

이름의 내림 차순(desceding)으로 정렬하기

 

이름은 오름차순, 지점명도 오름차순으로 정렬하기

 

이름은 오름차순, 지점명은 내림차순으로 정렬하기

 

지점명은 오름차순으로, 이름은 오름차순  정렬하기

지점명은 오름차순으로, 이름은 내림차순  정렬하기

 

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

실행 순서: 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번에 표시된 컬럼만 추출한다.

 

 

as 로 컬럼에 별명(라벨명) 붙이기

출력 라벨명을 변경하기
   => 라벨명을 지정하지 않으면 컬럼명이 라벨명이 됨

 

as 생략 가능

 

라벨명에 공백을 넣고 싶으면 '' 안에 작성

ㄴ 가능은 하지만 비추천

 

복잡한 형식으로 출력할 경우 라벨명(별명)을 부여함
예) 강의실명(지점명)

 

count()를 호출할 때 컬럼 이름을 지정하면 해당 컬럼의 값이 null 이 아닌 데이터만 카운트함

 

union 과 union all

select 결과 합치기

ㄴ union : 중복 값 자동 제거

 

=>

union all: 중복 값 제거 안함

 

차집합
ㄴ mysql 은 차집합 문법을 지원하지 않음
ㄴ 따라서 다음과 기존의 SQL 문법을 사용해서 처리해야 함

 

교집합
ㄴ mysql 은 교집합 문법을 지원하지 않음
ㄴ 따라서 다음과 기존의 SQL 문법을 사용해서 처리해야 함

 

Join(조인)

조인

조인
=> 서로 관련된 테이블의 데이터를 연결하여 추출하는 방법
=> 기법
1) CROSS 조인(=Cartesian product)
2) NATURAL 조인
3) JOIN ~ USING(컬럼명)
4) JOIN ~ ON
5) OUTER JOIN

 

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

테이블 생성 및 데이터 준비

 

bno 컬럼이 두 테이블에 모두 존재함
=> 따라서 어떤 테이블의 컬럼인지 지정하지 않으면 실행 오류!

 

select 컬럼이 두 테이블에 모두 있을 경우, 컬럼명 앞에 테이블명을 명시하여 구분하라!

 

cross join 고전 문법

 

컬럼명 앞에 테이블명을 붙이면 너무 김
=> 테이블에 별명을 부여하고 그 별명을 사용하여 컬럼을 지정하라 (as는 생략 가능)

 

고전 문법

 

2) NATURAL 조인
   같은 이름을 가진 컬럼 값을 기준으로 레코드를 연결함

 

고전 문법

 

natural join 의 문제점
가. 두 테이블의 조인 기준이 되는 컬럼 이름이 다를 때 연결되지 못한다.
나. 상관 없는 컬럼과 이름이 같을 때 잘못 연결된다.
다. 같은 이름의 컬럼이 여러 개 있을 경우 잘못 연결된다.
   모든 컬럼의 값이 일치할 경우에만 연결되기 때문이다.

 

가. 두 테이블의 조인 기준이 되는 컬럼 이름이 다를 때:

테이블 생성 및 데이터 준비

 

natural join의 기준이 되는 같은 이름을 가진 컬럼이 양 테이블에 존재하지 않음
=> cross join 처럼 실행됨

 

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

 

나. 같은 이름을 가진 컬럼이 있지만 서로 상관(PK와 FK 관계)이 없는 컬럼일 때:

테이블 생성 및 데이터 준비

 

board3의 no와 attach_file3의 no는 PK/FK 관계가 아님
=> 그럼에도 불구하고 이름이 같기 때문에 이 컬럼을 기준으로 데이터를 연결함

 

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

 

다. 같은 이름을 가진 컬럼이 여러 개 있을 때:

테이블 생성 및 데이터 준비

 

board4와 attach_file4에 같은 이름을 가진 컬럼이 여러 개 있음
=> 해당 컬럼들의 값이 같을 때만 두 테이블의 데이터를 연결함
=> 따라서 실행 결과 데이터는 없을 것

 

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

 

 

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

 

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

테이블 생성 및 데이터 준비

 

두 테이블의 데이터를 연결할 때 기준이 되는 컬럼이 이름이 같지 않음
이런 경우 using을 사용할 수 없음 => 실행 오류!

 

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

 

조건에 일치하는 경우에만 두 테이블의 데이터를 연결한다.
이런 조인을 'inner join' 이라 부른다.
SQL 문에서도 inner join 이라 기술할 수 있다.
물론 inner를 생략할 수도 있다.

 

[inner] join ~ on 의 문제점
   => 반드시 on 에서 지정한 컬럼의 값이 같을 경우에만
      두 테이블의 데이터가 연결된다.
   => 같은 값을 갖는 데이터가 없다면 연결되지 않고, 결과로 출력되지 않는다.
   => 위 SQL의 실행 결과를 보라!
      첨부파일이 없는 2번 게시글은 결과에 포함되지 않는다.

 

5) OUTER JOIN
   조인 조건에 일치하는 데이터가 없더라도 두 테이블 중에서 한 테이블의 데이터를 결과로 포함시키는 명령

   문법:
        select  컬럼명, 컬럼명, ...
        from 테이블1 t1 [left|right] outer join 테이블2 t2 on t1.컬럼=t2컬럼
   left outer join => 왼쪽 테이블의 데이터는 반드시 포함시키라는 뜻이다.
   right outer join => 오른쪽 테이블의 데이터를 반드시 포함시키는 뜻이다.

 

실무
1) 여러 테이블을 조인하여 컬럼을 projection 할 때 각 컬럼이 어떤 테이블의 컬럼인지 명시한다.
2) 컬럼을 나열할 때 한 줄에 한 컬럼을 나열한다.

 

[inner join의 문제점 예1]

1) 전체 강의 목록

 

2) 전체 강의실 목록

 

3) 강의 정보를 출력할 때 센터 이름과 강의실 이름도 함께 출력해 보자!
   강의 테이블(lect)에서 강의명을 가져오고, 강의실 테이블(room)에서 지점명과 강의실명을 가져오자.

inner join의 문제는 위의 경우처럼
   강의실이 아직 지정되지 않은 강의의 경우 강의실 테이블의 데이터와 연결하지 못해
   결과로 출력되지 않는 문제가 있다.

 

[inner join의 문제점 예2]

1) 모든 강의장 이름을 출력하라.
   단 강의장에 강의가 배정된 경우 그 강의 이름도 출력하라.

위의 경우 처럼 만약 기준 컬럼의 값과 일치하는 데이터가 없어서 다른 테이블의 데이터와 연결되지 않았다 하더라도
결과로 뽑아내고 싶다면 outer join을 사용하라!
즉 아직 강의실이 배정되지 않은 강의 데이터도 출력하고 싶을 때 출력하고 싶은 테이블을 바깥쪽 테이블로 지정하라!

 

왼쪽 테이블인 lect를 기준으로 room 데이터를 연결한다.
만약 lect와 일치하는 데이터가 room에 없더라도 lect 데이터를 출력한다!

 

오른쪽 테이블인 room을 기준으로 lect데이터를 연결한다.
만약 room과 일치하는 데이터가 lect에 없더라도 room 데이터를 출력한다!

 

조인 연습

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

1) 모든 멤버 데이터 출력하기

 

2) 학생 데이터를 가져와서 연결하기

 

3) join ~ using으로 연결하기

 

4) 고전 문법으로 연결하기

 

5) inner join ~ on 연결하기

 

6) inner 생략하기

 

7) 테이블에 별명 부여하기

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

 

8) outer join ~ on 으로 연결하기

 

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

1단계: 수강신청 데이터를 출력

 

2단계: 수강신청한 학생의 번호 대신 이름을 출력

 

3단계: 수강 신청한 학생의 재직 여부 출력
=> inner join 에서 inner는 생략 가능

 

4단계: 수강신청한 강의 번호 대신 강의명을 출력

 

5단계: 강의실 이름을 출력
=> 강의실 번호는 lect 테이블 데이터에 있음
=> 강의실 이름은 room 테이블 데이터에 있음

 

6단계: 매니저 이름을 출력
=> 매니저 번호는 lect 테이블에 있음
=> 매니저 이름은 memb 테이블에 있음

 

7단계: 매니저의 직위 출력
=> 매니저 번호는 lect 테이블 있음
=> 매니저 직위는 mgr 테이블에 있음

 

 

 

서브 쿼리

서브 쿼리
=> 쿼리문 안에 쿼리문을 실행하는 기법
=> 성능 문제를 생각하면서 사용해야 한다.

select 절에 서브쿼리 사용하기

수강신청 데이터를 출력

 

=> 1단계: 수강신청 데이터를 출력

 

=> 2단계 : 서브 쿼리를 이용하여 강의명을 가져오기
   - 단, 컬럼 자리에 사용할 때는 결과 값이 한 개여야 함
   - 결과 값이 여러 개가 리턴된다면 컬럼 값으로 사용할 수 없기 때문에 오류임
   - 또한 컬럼 개수도 한 개여야 함

 

=> 3단계 : 서브 쿼리를 이용하여 학생명을 가져오기

 

from 절에 서브쿼리 사용하기 

0단계 : 강의 정보를 가져오기

 

1단계 : 강의 상세 정보를 가져오는 select를 준비
    => 서브 쿼리를 이용하여 강의실 이름과 매니저 이름, 직위 정보를 가져오기

 

2단계: 위에서 준비한 select 결과를 가상 테이블로 사용하여 기존의 lect_appl 테이블과 조인

 

lect_appl 테이블 대신에 서브 쿼리의 결과를 테이블로 사용할 수 있음

 

from 절에서 반복적으로 사용하는 서브 쿼리가 있다면,
차라리 가상 테이블인 view로 정의해놓고 사용하는 것이 편함

 

위의 질의문을 view를 사용하여 다시 작성해보자!

 

where 절에 서브쿼리 사용하기

과장 또는 주임 매니저가 담당하고 있는 수강 신청만 추출하기

 

서브쿼리 예1 : select 절에 서브쿼리를 둘 때 + where 절에 서브쿼리를 둘 때

 

서브쿼리 예2 : from 절에 서브쿼리를 둘 때

1) 강의 정보

 

2) 수강생 정보

 

3) 수강신청 정보

 

4) 수강신청 정보 + 강의 정보 + 수강생 정보

 

 

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

각 지점별 강의실 수 구하기

1단계: 강의실 목록 구하기

 

2단계: 지점정보를 저장한 컬럼을 기준으로 그룹으로 묶기

 

3단계: group by를 통해 데이터를 그룹으로 묶은 경우 개별 항목의 값을 나타내는 컬럼의 값은 의미가 없기 때문에 제거함

 

4단계: 그룹으로 묶은 경우 그룹 관련 함수를 사용할 수 있음

 

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

예1) having 절에서 집합 함수 사용

 

예2) having 절에서 group by 조건 컬럼 사용

 

예3) having 절에서 select 절 컬럼 사용

 

오류) select 절에 선언되지 않은 컬럼을 지정할 수 없음

 

실행 순서: from --> where --> group by --> select --> having --> order by
=> MySQL이 아닌 다른 DBMS에서 실행하여 실행 순서를 확인할 필요가 있음!