2022-03-02(수)

git/bitcamp-study/eomcs-docs/sql/Exam07_5.sql

/* 여러 테이블의 데이터를 연결하기
    => 다음의 결과가 출력될 수 있도록 수강 신청 데이터를 출력하시오!
    수강신청번호, 강의명, 학생명, 재직여부, 수강신청일, 강의실명, 매니저명, 직위 */

년월일만 추출하고 싶음

https://mariadb.com/kb/en/built-in-functions/

https://mariadb.com/kb/en/date_format/

select
lano,
lno,
mno,
date_format(rdt, '%Y')
from lect_appl;

컬럼 이름이 너무 길다.. date_format(rdt, '%Y-%m-%d')

강의명을 추출해보자
강의 테이블에서 가져와야 됨
수강신청 테이블이랑 강의 테이블을 join 해야 된다

테이블을 계속 조인해 나가다 보면
구분하기 위해서 테이블명을 앞에 계속 두면 길어짐

oracle erp erd 검색

실무에서는 테이블명 엄청 길다
컬럼 하나 적는데 너무 길어진다
as 생략 가능
테이블 하나에 있어도 바로 인지할 수 있도록 가능하면 테이블명 붙이기

select
la.lano,
la.lno,
la.mno,
date_format(la.rdt, '%Y-%m-%d') reg_date,
l.titl
from lect_appl as la
inner join lect as l on la.lno=l.lno;

SELECT 결과 뽑아질 때는 앞에 테이블명 안 붙음

왜 outer join 안 하고 inner join 했습니까?

무조건 존재하는 데이터면 inner join
반드시 있을 때만 뽑아라

없을 수도 있으면 outer join
없어도 결과로 뽑아라

이름을 가져올 때는 멤버 테이블 join

select
la.lano,
la.mno,
date_format(la.rdt, '%Y-%m-%d') reg_date,
l.titl,
m.name
from lect_appl as la
inner join lect as l on la.lno=l.lno
inner join memb m on la.mno=m.mno;

select
la.lano,
date_format(la.rdt, '%Y-%m-%d') reg_date,
l.titl,
m.name
from lect_appl as la
inner join lect as l on la.lno=l.lno
inner join memb m on la.mno=m.mno;

재직여부 출력해보자
수강생 테이블에 재직여부 들어있음
수강생 테이블 조인하자
데이터가 반드시 존재하기 때문에 inner join

select
la.lano,
date_format(la.rdt, '%Y-%m-%d') reg_date,
l.titl,
m.name,
s.work
from lect_appl as la
inner join lect as l on la.lno=l.lno
inner join memb m on la.mno=m.mno
inner join stnt s on la.mno=s.mno;

강의실명

강의 테이블의 rno와 강의실 테이블의 rno가 같은 경우

아직 강의실이 배정이 안 된 강의가 있음
강의실 번호가 NULL
연결되지 않은 데이터는 결과에서 빠져버리는 문제가 발생
outer join 해야 됨

select
la.lano,
date_format(la.rdt, '%Y-%m-%d') reg_date,
l.titl,
m.name,
s.work,
r.name
from lect_appl as la
inner join lect as l on la.lno=l.lno
inner join memb m on la.mno=m.mno
inner join stnt s on la.mno=s.mno
left outer join room r on l.rno=r.rno;

강의실 데이터가 없다 하더라도 무조건 왼쪽 데이터는 나오게 해라

https://mariadb.com/kb/en/string-functions/

https://mariadb.com/kb/en/to_char/

to_char(la.rdt, 'YYYY-MM-DD') reg_date,

select
la.lano,
/* date_format(la.rdt, '%Y-%m-%d') reg_date, */
to_char(la.rdt, 'YYYY-MM-DD') reg_date,
l.titl,
m.name,
s.work,
COALESCE(r.name, '') room_name
from lect_appl as la
inner join lect as l on la.lno=l.lno
inner join memb m on la.mno=m.mno
inner join stnt s on la.mno=s.mno
left outer join room r on l.rno=r.rno;

name 같으니까 별명을 주면 구별할 수 있다

항상 별명을 붙임

select
la.lano,
/* date_format(la.rdt, '%Y-%m-%d') reg_date, */
to_char(la.rdt, 'YYYY-MM-DD') reg_date,
l.titl,
m.name student_name,
s.work,
ifnull(r.name, '') room_name,
ifnull(m2.name,'') manager_name
from lect_appl as la
inner join lect as l on la.lno=l.lno
inner join memb m on la.mno=m.mno
inner join stnt s on la.mno=s.mno
left outer join room r on l.rno=r.rno
left outer join memb m2 on l.mno=m2.mno;

매니저 직위 출력해보자

매니저 테이블에 직위 있음

강의 테이블에 있는 매니저 번호와 매니저 테이블에 있는 매니저번호가 일치할 때 가져온다

select
la.lano,
/* date_format(la.rdt, '%Y-%m-%d') reg_date, */
to_char(la.rdt, 'YYYY-MM-DD') reg_date,
l.titl,
m.name student_name,
s.work,
ifnull(r.name, '') room_name,
ifnull(m2.name,'') manager_name,
ifnull(mr.posi, '') manager_posi
from lect_appl as la
inner join lect as l on la.lno=l.lno
inner join memb m on la.mno=m.mno
inner join stnt s on la.mno=s.mno
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;

서브 쿼리

select
  la.lano,
  la.lno,
  la.mno,
  to_char(la.rdt, 'YYYY-MM_DD') reg_date
from lect_appl la;

select문 안에 select문이 들어감
강의번호에 해당되는 강의명을 출력하고 싶음

select
  la.lano,
  (select titl from lect where lno=la.lno),
  la.mno,
  to_char(la.rdt, 'YYYY-MM_DD') reg_date
from lect_appl la;

수강생 이름 출력하기

컬럼은 한 개만 있어야 됨

select
  la.lano,
  (select titl from lect where lno=la.lno) lect_title,
  (select name from memb where mno=la.mno) member_name,
  to_char(la.rdt, 'YYYY-MM_DD') reg_date
from lect_appl la;

16번의 select가 실행된다
그에 비해서 기존의 join 방식은 join 한 다음에 select 한 번
join 방식이 실행 방식이 빠르다

직관적인데 속도가 느림

서브쿼리가 1개보다 더 많은 값을 리턴할 수 있어서 안 돼!!

결과가 1개인지 확인해야 됨

값이 1개 놓일 수 있다

select 결과 1개여야 하고 컬럼도 1개여야 한다

concat 써서 1개로 만들면 괜찮음

근데 컬럼이 2개 있으면 안 됨

각 레코드에 대해서 서브쿼리를 여러 번 실행하면 속도가 떨어진다

select
  la.lano,
  (select titl from lect where lno=la.lno) lect_title,
  (select name from memb where mno=la.mno) member_name,
  (select work from stnt where mno=la.mno) member_work,
  to_char(la.rdt, 'YYYY-MM_DD') reg_date
from lect_appl la;

select
  la.lano,
  (select titl from lect where lno=la.lno) lect_title,
  (select name from memb where mno=la.mno) member_name,
  (select work from stnt where mno=la.mno) member_work,
  to_char(la.rdt, 'YYYY-MM_DD') reg_date,
  (select name from room where rno=(select rno from lect where lno=la.lno)) room_name
from lect_appl la;

select
  la.lano,
  (select titl from lect where lno=la.lno) lect_title,
  (select name from memb where mno=la.mno) member_name,
  (select work from stnt where mno=la.mno) member_work,
  to_char(la.rdt, 'YYYY-MM_DD') reg_date,
  ifnull((select name from room where rno=(select rno from lect where lno=la.lno)), '') room_name
from lect_appl la;

join 더 깔끔하다...

select
  la.lano,
  (select titl from lect where lno=la.lno) lect_title,
  (select name from memb where mno=la.mno) member_name,
  (select work from stnt where mno=la.mno) member_work,
  to_char(la.rdt, 'YYYY-MM_DD') reg_date,
  ifnull((select name from room where rno=(select rno from lect where lno=la.lno)), '') room_name,
  ifnull((select name from memb where mno=(select mno from lect where lno=la.lno)), '') manager_name
from lect_appl la;

이게 극단적인 서브 쿼리

수강신청한 강의의 매니저는

select
  la.lano,
  (select titl from lect where lno=la.lno) lect_title,
  (select name from memb where mno=la.mno) member_name,
  (select work from stnt where mno=la.mno) member_work,
  to_char(la.rdt, 'YYYY-MM_DD') reg_date,
  ifnull((select name from room where rno=(select rno from lect where lno=la.lno)), '') room_name,
  ifnull((select name from memb where mno=(select mno from lect where lno=la.lno)), '') manager_name,
  ifnull((select posi from mgr where mno=(select mno from lect where lno=la.lno)), '') manager_posi
from lect_appl la;

where절에 select가 들어 있을 경우에는
데이터가 100개면 where절을 100번 실행
서브쿼리는 극단적으로 실행속도를 떨어뜨린다

요즘 DBMS는 최적화한다

select
  l.lno lect_no,
  l.titl lect_title,
  ifnull((select name from room where rno=l.rno), '') room_name,
  ifnull((select name from memb where mno=l.mno), '') manager_name
from
  lect l;

select
  l.lno lect_no,
  l.titl lect_title,
  ifnull((select name from room where rno=l.rno), '') room_name,
  ifnull((select name from memb where mno=l.mno), '') manager_name,
  ifnull((select posi from mgr where mno=l.mno), '') mgr_posi
from
  lect l;

select
  s.mno student_no,
  (select name from memb where mno=s.mno) student_name,
  s.work student_work
from
  stnt s;

select
  la.lano,
  la.lno,
  la.mno,
  to_char(la.rdt, 'YYYY-MM-DD') reg_date
from lect_appl la;

가상 테이블의 컬럼

from절에 서브 쿼리 둠

select
  la.lano,
  le.lect_no,
  le.lect_title,
  le.room_name,
  le.mgr_name,
  le.mgr_posi,
  st.std_no,
  st.std_name,
  st.std_work,
  to_char(la.rdt, 'YYYY-MM-DD') reg_date
from lect_appl la
  inner join (
    select
      l.lno lect_no,
      l.titl lect_title,
      ifnull((select name from room where rno=l.rno), '') room_name,
      ifnull((select name from memb where mno=l.mno), '') mgr_name,
      ifnull((select posi from mgr where mno=l.mno), '') mgr_posi
    from
      lect l
  ) le on la.lno=le.lect_no
  inner join (
    select
      s.mno std_no,
      (select name from memb where mno=s.mno) std_name,
      s.work std_work
    from
      stnt s
  ) st on la.mno=st.std_no;

git/bitcamp-study/eomcs-docs/sql/Exam07_7.sql

그룹으로 묶은 다음에 그룹 단위로 연산을 수행하는 함수를 호출할 때 사용

https://mariadb.com/kb/en/aggregate-functions/

그 그룹에 소속된 데이터의 수

where절이 아니라 having절

집합함수는 쓸 수 있다

프로젝트 할 때 그룹 단위로 카운트 하는 거 있으면 여쭤보기

SQL과 DB 프로그래밍

SQL 입력
DBMS Cleint

DBMS 전용 프로토콜로 통신

JDBC 드라이버

자바 DB 프로그램을 짜보겠다
이게 되려면 SQL을 알아야 한다