2020-10-28 TIL

sql

join

natural join: 같은 이름을 가진 컬럼 값을 기준으로 연결한다.

MariaDB [studydb]> /* natural join: 같은 이름을 가진 컬럼 값을 기준으로 레코드를 연결한다. */
MariaDB [studydb]> select m.mno, name, s.mno, work, bank
    -> from memb m natural join stnt s; 
+-----+------+-----+------+--------------+
| mno | name | mno | work | bank         |
+-----+------+-----+------+--------------+
| 100 | s100 | 100 | N    | 비트은행     |
| 101 | s101 | 101 | Y    | 비트은행     |
| 102 | s102 | 102 | N    | 캠프은행     |
| 103 | s103 | 103 | Y    | 우리은행     |
| 104 | s104 | 104 | N    | 국민은행     |
+-----+------+-----+------+--------------+
5 rows in set (0.000 sec)

MariaDB [studydb]> /* 예전 문법 */
MariaDB [studydb]> select m.mno, name, s.mno, work, bank
    -> from memb m, stnt s
    -> where m.mno=s.mno;
+-----+------+-----+------+--------------+
| mno | name | mno | work | bank         |
+-----+------+-----+------+--------------+
| 100 | s100 | 100 | N    | 비트은행     |
| 101 | s101 | 101 | Y    | 비트은행     |
| 102 | s102 | 102 | N    | 캠프은행     |
| 103 | s103 | 103 | Y    | 우리은행     |
| 104 | s104 | 104 | N    | 국민은행     |
+-----+------+-----+------+--------------+
5 rows in set (0.000 sec)

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

MariaDB [studydb]> /* 만약에 두 테이블에 같은 이름을 가진 컬럼이 여러 개 있다면, 
   /*>    join ~ using (기준컬럼) 을 사용하여 
   /*>    두 테이블의 데이터를 연결할 때 기준이 될 컬럼을 지정한다.*/
MariaDB [studydb]> select m.mno, name, s.mno, work, bank
    -> from memb m join stnt s using (mno);  
+-----+------+-----+------+--------------+
| mno | name | mno | work | bank         |
+-----+------+-----+------+--------------+
| 100 | s100 | 100 | N    | 비트은행     |
| 101 | s101 | 101 | Y    | 비트은행     |
| 102 | s102 | 102 | N    | 캠프은행     |
| 103 | s103 | 103 | Y    | 우리은행     |
| 104 | s104 | 104 | N    | 국민은행     |
+-----+------+-----+------+--------------+
5 rows in set (0.016 sec)

natural join 의 문제점 2

• 두 테이블에 같은 이름의 컬럼이 없을 경우 연결하지 못한다.

MariaDB [studydb]> /* 만약 두 테이블에 같은 이름을 가진 컬럼이 없으면, 
   /*>    natural join을 수행하지 못한다.
   /*>    또한 join using 으로도 해결할 수 없다.
   /*>    이럴 경우 join ~ on 컬럼a=컬럼b 문법을 사용하여
   /*>    각 테이블의 어떤 컬럼과 값을 비교할 것인지 지정하라!*/
MariaDB [studydb]> select m.mno, name, s.mno, work, bank
    -> from memb m inner join stnt s on m.mno=s.mno;
+-----+------+-----+------+--------------+
| mno | name | mno | work | bank         |
+-----+------+-----+------+--------------+
| 100 | s100 | 100 | N    | 비트은행     |
| 101 | s101 | 101 | Y    | 비트은행     |
| 102 | s102 | 102 | N    | 캠프은행     |
| 103 | s103 | 103 | Y    | 우리은행     |
| 104 | s104 | 104 | N    | 국민은행     |
+-----+------+-----+------+--------------+
5 rows in set (0.002 sec)

MariaDB [studydb]> /* inner는 생략 가능하다 */
MariaDB [studydb]> select m.mno, name, s.mno, work, bank
    -> from memb m join stnt s on m.mno=s.mno;
+-----+------+-----+------+--------------+
| mno | name | mno | work | bank         |
+-----+------+-----+------+--------------+
| 100 | s100 | 100 | N    | 비트은행     |
| 101 | s101 | 101 | Y    | 비트은행     |
| 102 | s102 | 102 | N    | 캠프은행     |
| 103 | s103 | 103 | Y    | 우리은행     |
| 104 | s104 | 104 | N    | 국민은행     |
+-----+------+-----+------+--------------+
5 rows in set (0.000 sec)

MariaDB [studydb]> /* 즉 inner join 은 기준 컬럼의 값이 일치할 때만 데이터를 연결한다. */

MariaDB [studydb]> /* 예전의 조인 문법 = inner join */
MariaDB [studydb]> select m.mno, name, s.mno, work, bank
    -> from memb m, stnt s
    -> where m.mno=s.mno;    
+-----+------+-----+------+--------------+
| mno | name | mno | work | bank         |
+-----+------+-----+------+--------------+
| 100 | s100 | 100 | N    | 비트은행     |
| 101 | s101 | 101 | Y    | 비트은행     |
| 102 | s102 | 102 | N    | 캠프은행     |
| 103 | s103 | 103 | Y    | 우리은행     |
| 104 | s104 | 104 | N    | 국민은행     |
+-----+------+-----+------+--------------+
5 rows in set (0.000 sec)

[inner] join ~ on 의 문제점

• 반드시 on 에서 지정한 컬럼의 값이 같을 경우에만 두 테이블의 데이터가 연결된다.
• 같은 값을 갖는 데이터가 없다면 연결되지 않고, 결과로 출력되지 않는다.

MariaDB [studydb]> /* 전체 강의 목록 */
MariaDB [studydb]> select lno, titl, rno, mno from lect;
+-----+--------------------------+------+------+
| lno | titl                     | rno  | mno  |
+-----+--------------------------+------+------+
|   1 | 자바프로그래밍               |    1 |  303 |
|   2 | IoT프로그래밍               |    4 | NULL |
|   3 | 윈도우프로그래밍              | NULL |  304 |
+-----+--------------------------+------+------+
3 rows in set (0.002 sec)

MariaDB [studydb]> /* 전체 강의실 목록 */
MariaDB [studydb]> select rno, loc, name from room;
+-----+--------+------+
| rno | loc    | name |
+-----+--------+------+
|   1 | 강남   | 501  |
|   2 | 강남   | 502  |
|   3 | 강남   | 503  |
|   7 | 서초   | 301  |
|   8 | 서초   | 302  |
|   9 | 서초   | 501  |
|  10 | 서초   | 601  |
|   4 | 종로   | 301  |
|   5 | 종로   | 302  |
|   6 | 종로   | 303  |
+-----+--------+------+
10 rows in set (0.000 sec)

MariaDB [studydb]> select rno, loc, name from room order by rno;
+-----+--------+------+
| rno | loc    | name |
+-----+--------+------+
|   1 | 강남   | 501  |
|   2 | 강남   | 502  |
|   3 | 강남   | 503  |
|   4 | 종로   | 301  |
|   5 | 종로   | 302  |
|   6 | 종로   | 303  |
|   7 | 서초   | 301  |
|   8 | 서초   | 302  |
|   9 | 서초   | 501  |
|  10 | 서초   | 601  |
+-----+--------+------+
10 rows in set (0.000 sec)

MariaDB [studydb]> /* 강의 테이블에서 강의명을 가져오고, 강의실 테이블에서 지점명과 강의실명을 가져오자. */
MariaDB [studydb]> select 
    ->     l.lno, 
    ->     l.titl, 
    ->     r.rno, 
    ->     r.loc, 
    ->     r.name
    -> from lect l inner join room r on l.rno=r.rno;
+-----+-----------------------+-----+--------+------+
| lno | titl                  | rno | loc    | name |
+-----+-----------------------+-----+--------+------+
|   1 | 자바프로그래밍        |   1 | 강남   | 501  |
|   2 | IoT프로그래밍         |   4 | 종로   | 301  |
+-----+-----------------------+-----+--------+------+
2 rows in set (0.002 sec)

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

inner join의 문제점 예2

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

MariaDB [studydb]> select 
    ->   r.rno, 
    ->   r.name, 
    ->   r.loc, 
    ->   l.titl
    -> from room r inner join lect l on r.rno = l.rno;
+-----+------+--------+-----------------------+
| rno | name | loc    | titl                  |
+-----+------+--------+-----------------------+
|   1 | 501  | 강남   | 자바프로그래밍        |
|   4 | 301  | 종로   | IoT프로그래밍         |
+-----+------+--------+-----------------------+
2 rows in set (0.000 sec)

MariaDB [studydb]> /* => 만약 기준 컬럼의 값과 일치하는 데이터가 없어서 
   /*>       다른 테이블의 데이터와 연결되지 않았다 하더라도 
   /*>       결과로 뽑아내고 싶다면 outer join을 사용하라!*/
MariaDB [studydb]> /* 즉 아직 강의실이 배정되지 않은 강의 데이터도 출력하고 싶을 때
   /*>    출력하고 싶은 테이블을 바깥쪽 테이블로 지정하라!
   /*>  */
MariaDB [studydb]> select 
    ->     l.lno, 
    ->     l.titl, 
    ->     r.rno, 
    ->     r.loc, 
    ->     r.name
    -> from lect l right outer join room r on l.rno=r.rno;
+------+-----------------------+-----+--------+------+
| lno  | titl                  | rno | loc    | name |
+------+-----------------------+-----+--------+------+
|    1 | 자바프로그래밍        |   1 | 강남   | 501  |
|    2 | IoT프로그래밍         |   4 | 종로   | 301  |
| NULL | NULL                  |   2 | 강남   | 502  |
| NULL | NULL                  |   3 | 강남   | 503  |
| NULL | NULL                  |   7 | 서초   | 301  |
| NULL | NULL                  |   8 | 서초   | 302  |
| NULL | NULL                  |   9 | 서초   | 501  |
| NULL | NULL                  |  10 | 서초   | 601  |
| NULL | NULL                  |   5 | 종로   | 302  |
| NULL | NULL                  |   6 | 종로   | 303  |
+------+-----------------------+-----+--------+------+
10 rows in set (0.003 sec)

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

• /* 요구사항: 모든 멤버의 번호와 이름을 출력하라!
  단 학생의 경우 재직여부도 출력하라!*/

MariaDB [studydb]> -- 1) 모든 멤버 데이터 출력하기
MariaDB [studydb]> select mno, name
    -> from memb;
+-----+------+
| mno | name |
+-----+------+
| 300 | m300 |
| 301 | m301 |
| 302 | m302 |
| 303 | m303 |
| 304 | m304 |
| 100 | s100 |
| 101 | s101 |
| 102 | s102 |
| 103 | s103 |
| 104 | s104 |
| 200 | s200 |
| 201 | s201 |
| 202 | s202 |
| 203 | s203 |
| 204 | s204 |
+-----+------+
15 rows in set (0.001 sec)

MariaDB [studydb]> -- 2) 학생 데이터를 가져와서 연결하기
MariaDB [studydb]> select mno, name, work
    -> from memb natural join stnt;
+-----+------+------+
| mno | name | work |
+-----+------+------+
| 100 | s100 | N    |
| 101 | s101 | Y    |
| 102 | s102 | N    |
| 103 | s103 | Y    |
| 104 | s104 | N    |
+-----+------+------+
5 rows in set (0.000 sec)

MariaDB [studydb]> desc stnt;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| mno    | int(11)     | NO   | PRI | NULL    |       |
| work   | char(1)     | NO   |     | NULL    |       |
| acc_no | varchar(20) | YES  | MUL | NULL    |       |
| bank   | varchar(50) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.009 sec)
/*mno 가 fk 이면서 pk 이기 때문에 join 사용 가능*/

select mno, name, work
from memb join stnt using(mno);

select memb.mno, name, work
from memb, stnt
where memb.mno=stnt.mno;

select memb.mno, name, work
from memb inner join stnt on memb.mno=stnt.mno;

/*inner는 생략 가능하다.*/
select memb.mno, name, work
from memb join stnt on memb.mno=stnt.mno;

select m.mno, name, work
from memb m join stnt s on m.mno=s.mno;

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

MariaDB [studydb]> select m.mno, name, work
    -> from memb m left outer join stnt s on m.mno=s.mno;  
+-----+------+------+
| mno | name | work |
+-----+------+------+
| 300 | m300 | NULL |
| 301 | m301 | NULL |
| 302 | m302 | NULL |
| 303 | m303 | NULL |
| 304 | m304 | NULL |
| 100 | s100 | N    |
| 101 | s101 | Y    |
| 102 | s102 | N    |
| 103 | s103 | Y    |
| 104 | s104 | N    |
| 200 | s200 | NULL |
| 201 | s201 | NULL |
| 202 | s202 | NULL |
| 203 | s203 | NULL |
| 204 | s204 | NULL |
+-----+------+------+
15 rows in set (0.000 sec)

여러 테이블의 데이터를 연결하기

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

MariaDB [studydb]> /* 1단계: 수강신청 데이터를 출력 */
MariaDB [studydb]> select la.lano, la.lno, la.mno, la.rdt
    -> from lect_appl la;  
+------+-----+-----+---------------------+
| lano | lno | mno | rdt                 |
+------+-----+-----+---------------------+
|    1 |   1 | 100 | 2017-11-02 00:00:00 |
|    2 |   1 | 101 | 2017-11-03 00:00:00 |
|    3 |   1 | 102 | 2017-11-04 00:00:00 |
|    4 |   2 | 104 | 2017-12-06 00:00:00 |
|    5 |   2 | 100 | 2017-12-07 00:00:00 |
|    6 |   3 | 101 | 2017-10-08 00:00:00 |
|    7 |   3 | 102 | 2017-11-09 00:00:00 |
|    8 |   3 | 104 | 2017-11-11 00:00:00 |
+------+-----+-----+---------------------+
8 rows in set (0.000 sec)

MariaDB [studydb]> /* 2단계: 수강신청한 학생의 번호 대신 이름을 출력 */
MariaDB [studydb]> select la.lano, la.lno, m.name, la.rdt
    -> from lect_appl la 
    ->      inner join memb m on la.mno=m.mno;
+------+-----+------+---------------------+
| lano | lno | name | rdt                 |
+------+-----+------+---------------------+
|    1 |   1 | s100 | 2017-11-02 00:00:00 |
|    2 |   1 | s101 | 2017-11-03 00:00:00 |
|    3 |   1 | s102 | 2017-11-04 00:00:00 |
|    4 |   2 | s104 | 2017-12-06 00:00:00 |
|    5 |   2 | s100 | 2017-12-07 00:00:00 |
|    6 |   3 | s101 | 2017-10-08 00:00:00 |
|    7 |   3 | s102 | 2017-11-09 00:00:00 |
|    8 |   3 | s104 | 2017-11-11 00:00:00 |
+------+-----+------+---------------------+
8 rows in set (0.000 sec)

MariaDB [studydb]> /* 3단계: 수강 신청한 학생의 재직 여부 출력
   /*>  * => inner join 에서 inner는 생략 가능 
   /*>  */
MariaDB [studydb]> 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;
+------+-----+------+------+---------------------+
| lano | lno | name | work | rdt                 |
+------+-----+------+------+---------------------+
|    1 |   1 | s100 | N    | 2017-11-02 00:00:00 |
|    2 |   1 | s101 | Y    | 2017-11-03 00:00:00 |
|    3 |   1 | s102 | N    | 2017-11-04 00:00:00 |
|    4 |   2 | s104 | N    | 2017-12-06 00:00:00 |
|    5 |   2 | s100 | N    | 2017-12-07 00:00:00 |
|    6 |   3 | s101 | Y    | 2017-10-08 00:00:00 |
|    7 |   3 | s102 | N    | 2017-11-09 00:00:00 |
|    8 |   3 | s104 | N    | 2017-11-11 00:00:00 |
+------+-----+------+------+---------------------+
8 rows in set (0.002 sec)

MariaDB [studydb]> /* 4단계: 수상신청한 강의 번호 대신 강의명을 출력 */
MariaDB [studydb]> 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;
+------+--------------------------+------+------+---------------------+------+
| lano | titl                     | name | work | rdt                 | rno  |
+------+--------------------------+------+------+---------------------+------+
|    1 | 자바프로그래밍           | s100 | N    | 2017-11-02 00:00:00 |    1 |
|    2 | 자바프로그래밍           | s101 | Y    | 2017-11-03 00:00:00 |    1 |
|    3 | 자바프로그래밍           | s102 | N    | 2017-11-04 00:00:00 |    1 |
|    5 | IoT프로그래밍            | s100 | N    | 2017-12-07 00:00:00 |    4 |
|    4 | IoT프로그래밍            | s104 | N    | 2017-12-06 00:00:00 |    4 |
|    6 | 윈도우프로그래밍         | s101 | Y    | 2017-10-08 00:00:00 | NULL |
|    7 | 윈도우프로그래밍         | s102 | N    | 2017-11-09 00:00:00 | NULL |
|    8 | 윈도우프로그래밍         | s104 | N    | 2017-11-11 00:00:00 | NULL |
+------+--------------------------+------+------+---------------------+------+
8 rows in set (0.000 sec)

MariaDB [studydb]> /* 5단계: 강의실 이름을 출력한다.
   /*>  * => 강의실 번호는 lect 테이블 데이터에 있다.
   /*>  * => 강의실 이름은 room 테이블 데이터에 있다. 
   /*>  */
MariaDB [studydb]> 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;
+------+--------------------------+------+------+---------------------+------+------+
| lano | titl                     | name | work | rdt                 | name | mno  |
+------+--------------------------+------+------+---------------------+------+------+
|    1 | 자바프로그래밍           | s100 | N    | 2017-11-02 00:00:00 | 501  |  303 |
|    5 | IoT프로그래밍            | s100 | N    | 2017-12-07 00:00:00 | 301  | NULL |
|    2 | 자바프로그래밍           | s101 | Y    | 2017-11-03 00:00:00 | 501  |  303 |
|    6 | 윈도우프로그래밍         | s101 | Y    | 2017-10-08 00:00:00 | NULL |  304 |
|    3 | 자바프로그래밍           | s102 | N    | 2017-11-04 00:00:00 | 501  |  303 |
|    7 | 윈도우프로그래밍         | s102 | N    | 2017-11-09 00:00:00 | NULL |  304 |
|    4 | IoT프로그래밍            | s104 | N    | 2017-12-06 00:00:00 | 301  | NULL |
|    8 | 윈도우프로그래밍         | s104 | N    | 2017-11-11 00:00:00 | NULL |  304 |
+------+--------------------------+------+------+---------------------+------+------+
8 rows in set (0.001 sec)

MariaDB [studydb]> /* 6단계: 매니저 이름을 출력
   /*>  * => 매니저 번호는 lect 테이블에 있다.
   /*>  * => 매니저 이름은 memb 테이블에 있다. 
   /*>  */
MariaDB [studydb]> 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; 
+------+--------------------------+-------------+------+---------------------+-----------+--------------+
| lano | titl                     | member_name | work | rdt                 | room_name | manager_name |
+------+--------------------------+-------------+------+---------------------+-----------+--------------+
|    1 | 자바프로그래밍           | s100        | N    | 2017-11-02 00:00:00 | 501       | m303         |
|    5 | IoT프로그래밍            | s100        | N    | 2017-12-07 00:00:00 | 301       | NULL         |
|    2 | 자바프로그래밍           | s101        | Y    | 2017-11-03 00:00:00 | 501       | m303         |
|    6 | 윈도우프로그래밍         | s101        | Y    | 2017-10-08 00:00:00 | NULL      | m304         |
|    3 | 자바프로그래밍           | s102        | N    | 2017-11-04 00:00:00 | 501       | m303         |
|    7 | 윈도우프로그래밍         | s102        | N    | 2017-11-09 00:00:00 | NULL      | m304         |
|    4 | IoT프로그래밍            | s104        | N    | 2017-12-06 00:00:00 | 301       | NULL         |
|    8 | 윈도우프로그래밍         | s104        | N    | 2017-11-11 00:00:00 | NULL      | m304         |
+------+--------------------------+-------------+------+---------------------+-----------+--------------+
8 rows in set (0.003 sec)

MariaDB [studydb]> /* 7단계: 매니저의 직위 출력
   /*>  * => 매니저 번호는 lect 테이블 있다.
   /*>  * => 매니저 직위는 mgr 테이블에 있다.  
   /*>  */
MariaDB [studydb]> 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; 
+------+--------------------------+------+------+---------------------+------+------+--------+
| lano | titl                     | snm  | work | rdt                 | rnm  | mnm  | posi   |
+------+--------------------------+------+------+---------------------+------+------+--------+
|    1 | 자바프로그래밍           | s100 | N    | 2017-11-02 00:00:00 | 501  | m303 | 주임   |
|    5 | IoT프로그래밍            | s100 | N    | 2017-12-07 00:00:00 | 301  | NULL | NULL   |
|    2 | 자바프로그래밍           | s101 | Y    | 2017-11-03 00:00:00 | 501  | m303 | 주임   |
|    6 | 윈도우프로그래밍         | s101 | Y    | 2017-10-08 00:00:00 | NULL | m304 | 과장   |
|    3 | 자바프로그래밍           | s102 | N    | 2017-11-04 00:00:00 | 501  | m303 | 주임   |
|    7 | 윈도우프로그래밍         | s102 | N    | 2017-11-09 00:00:00 | NULL | m304 | 과장   |
|    4 | IoT프로그래밍            | s104 | N    | 2017-12-06 00:00:00 | 301  | NULL | NULL   |
|    8 | 윈도우프로그래밍         | s104 | N    | 2017-11-11 00:00:00 | NULL | m304 | 과장   |
+------+--------------------------+------+------+---------------------+------+------+--------+
8 rows in set (0.001 sec)

서브쿼리

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

MariaDB [studydb]> /* select 절에 서브쿼리 사용하기 */
MariaDB [studydb]> 
MariaDB [studydb]> /* => 1단계: 수강신청 데이터를 출력 */
MariaDB [studydb]> select 
    ->   la.lano, 
    ->   la.lno, 
    ->   la.mno, 
    ->   date_format(la.rdt, '%Y-%m-%d') reg_dt
    -> from lect_appl la; 
+------+-----+-----+------------+
| lano | lno | mno | reg_dt     |
+------+-----+-----+------------+
|    1 |   1 | 100 | 2017-11-02 |
|    2 |   1 | 101 | 2017-11-03 |
|    3 |   1 | 102 | 2017-11-04 |
|    4 |   2 | 104 | 2017-12-06 |
|    5 |   2 | 100 | 2017-12-07 |
|    6 |   3 | 101 | 2017-10-08 |
|    7 |   3 | 102 | 2017-11-09 |
|    8 |   3 | 104 | 2017-11-11 |
+------+-----+-----+------------+
8 rows in set (0.003 sec)

MariaDB [studydb]> /* => 2단계 : 서브 쿼리를 이용하여 강의명을 가져오기 */
MariaDB [studydb]> /* 단 컬럼 자리에 가져올 때는 결과 값이 하나여야 한다.*/
MariaDB [studydb]> /* 컬럼 값이 여러개가 리턴된다면 컬럼 값으로 사용할 수 없기 때문에 오류이다.*/
MariaDB [studydb]> select 
    ->     la.lano, 
    ->     (select titl from lect where lno=la.lno) as lect_title, 
    ->     la.mno, 
    ->     la.rdt
    -> from lect_appl la;
+------+--------------------------+-----+---------------------+
| lano | lect_title               | mno | rdt                 |
+------+--------------------------+-----+---------------------+
|    1 | 자바프로그래밍           | 100 | 2017-11-02 00:00:00 |
|    2 | 자바프로그래밍           | 101 | 2017-11-03 00:00:00 |
|    3 | 자바프로그래밍           | 102 | 2017-11-04 00:00:00 |
|    4 | IoT프로그래밍            | 104 | 2017-12-06 00:00:00 |
|    5 | IoT프로그래밍            | 100 | 2017-12-07 00:00:00 |
|    6 | 윈도우프로그래밍         | 101 | 2017-10-08 00:00:00 |
|    7 | 윈도우프로그래밍         | 102 | 2017-11-09 00:00:00 |
|    8 | 윈도우프로그래밍         | 104 | 2017-11-11 00:00:00 |
+------+--------------------------+-----+---------------------+
8 rows in set (0.005 sec)

MariaDB [studydb]> /* 단 컬럼 자리에 가져올 때는 결과 값이 하나여야 한다.*/
MariaDB [studydb]> /* 컬럼 값이 여러개가 리턴된다면 컬럼 값으로 사용할 수 없기 때문에 오류이다.*/
MariaDB [studydb]> select 
    ->     la.lano, 
    ->     (select titl, sdt from lect where lno=la.lno) as lect_title, 
    ->     la.mno, 
    ->     la.rdt
    -> from lect_appl la;
ERROR 1241 (21000): Operand should contain 1 column(s)

MariaDB [studydb]> /* 또한 컬럼 갯수도 한개여야 한다.*/
MariaDB [studydb]> select 
    ->     la.lano, 
    ->     (select titl from lect) as lect_title, 
    ->     la.mno, 
    ->     la.rdt
    -> from lect_appl la;
ERROR 1242 (21000): Subquery returns more than 1 row

MariaDB [studydb]> /* => 3단계 : 서브 쿼리를 이용하여 학생명을 가져오기 */
MariaDB [studydb]> select 
    ->     la.lano, 
    ->     (select titl from lect where lno=la.lno) as lect_title, 
    ->     (select name from memb where mno=la.mno) as stud_name,
    ->     la.rdt
    -> from lect_appl la;
+------+--------------------------+-----------+---------------------+
| lano | lect_title               | stud_name | rdt                 |
+------+--------------------------+-----------+---------------------+
|    1 | 자바프로그래밍           | s100      | 2017-11-02 00:00:00 |
|    2 | 자바프로그래밍           | s101      | 2017-11-03 00:00:00 |
|    3 | 자바프로그래밍           | s102      | 2017-11-04 00:00:00 |
|    4 | IoT프로그래밍            | s104      | 2017-12-06 00:00:00 |
|    5 | IoT프로그래밍            | s100      | 2017-12-07 00:00:00 |
|    6 | 윈도우프로그래밍         | s101      | 2017-10-08 00:00:00 |
|    7 | 윈도우프로그래밍         | s102      | 2017-11-09 00:00:00 |
|    8 | 윈도우프로그래밍         | s104      | 2017-11-11 00:00:00 |
+------+--------------------------+-----------+---------------------+
8 rows in set (0.001 sec)

from 절에 서브쿼리 사용하기

MariaDB [studydb]> /* 0단계 : 강의 정보를 가져온다. */
MariaDB [studydb]> select
    ->     l.lno,
    ->     l.titl,
    ->     l.rno,
    ->     l.mno
    -> from lect l;
+-----+--------------------------+------+------+
| lno | titl                     | rno  | mno  |
+-----+--------------------------+------+------+
|   1 | 자바프로그래밍           |    1 |  303 |
|   2 | IoT프로그래밍            |    4 | NULL |
|   3 | 윈도우프로그래밍         | NULL |  304 |
+-----+--------------------------+------+------+
3 rows in set (0.000 sec)

MariaDB [studydb]> /* 1단계 : 강의 상세 정보를 가져오는 select를 준비한다.
   /*>     => 서브 쿼리를 이용하여 강의실 이름과 매니저 이름, 직위 정보를 가져오기 */
MariaDB [studydb]> select 
    ->     l.lno, 
    ->     l.titl, 
    ->     (select name from room where rno=l.rno) as room_name, 
    ->     (select name from memb where mno=l.mno) as manager_name,
    ->     (select posi from mgr where mno=l.mno) as manager_posi
    -> from lect l;
+-----+--------------------------+-----------+--------------+--------------+
| lno | titl                     | room_name | manager_name | manager_posi |
+-----+--------------------------+-----------+--------------+--------------+
|   1 | 자바프로그래밍           | 501       | m303         | 주임         |
|   2 | IoT프로그래밍            | 301       | NULL         | NULL         |
|   3 | 윈도우프로그래밍         | NULL      | m304         | 과장         |
+-----+--------------------------+-----------+--------------+--------------+
3 rows in set (0.001 sec)

MariaDB [studydb]> /* 2단계: 위에서 준비한 select 결과를 가상 테이블로 사용하여 
   /*>              기존의 lect_appl 테이블과 조인한다.*/
MariaDB [studydb]> select 
    ->     la.lano, 
    ->     /*(select titl from lect where lno=la.lno) as lect_title,*/ 
    ->     (select name from memb where mno=la.mno) as stud_name,
    ->     lec.titl,
    ->     lec.room_name,
    ->     lec.manager_name,
    ->     lec.manager_posi
    -> from lect_appl la 
    ->     join (select 
    ->                 l.lno, 
    ->                 l.titl, 
    ->                 (select name from room where rno=l.rno) as room_name, 
    ->                 (select name from memb where mno=l.mno) as manager_name,
    ->                 (select posi from mgr where mno=l.mno) as manager_posi
    ->             from lect l) as lec on la.lno=lec.lno;
+------+-----------+--------------------------+-----------+--------------+--------------+
| lano | stud_name | titl                     | room_name | manager_name | manager_posi |
+------+-----------+--------------------------+-----------+--------------+--------------+
|    1 | s100      | 자바프로그래밍           | 501       | m303         | 주임         |
|    2 | s101      | 자바프로그래밍           | 501       | m303         | 주임         |
|    3 | s102      | 자바프로그래밍           | 501       | m303         | 주임         |
|    5 | s100      | IoT프로그래밍            | 301       | NULL         | NULL         |
|    4 | s104      | IoT프로그래밍            | 301       | NULL         | NULL         |
|    6 | s101      | 윈도우프로그래밍         | NULL      | m304         | 과장         |
|    7 | s102      | 윈도우프로그래밍         | NULL      | m304         | 과장         |
|    8 | s104      | 윈도우프로그래밍         | NULL      | m304         | 과장         |
+------+-----------+--------------------------+-----------+--------------+--------------+
8 rows in set (0.003 sec)

MariaDB [studydb]> /* from 절에서 반복적으로 사용하는 서브 쿼리가 있다면,
   /*>  * 차라리 가상 테이블인 view로 정의해놓고 사용하는 것이 편하다. 
   /*>  */ 
MariaDB [studydb]> create view lect2 as
    -> select 
    ->     l.lno, 
    ->     l.titl, 
    ->     (select name from room where rno=l.rno) as room_name, 
    ->     l.mno as manager_no,
    ->     (select name from memb where mno=l.mno) as manager_name,
    ->     (select posi from mgr where mno=l.mno) as manager_posi
    -> from lect l;
Query OK, 0 rows affected (0.017 sec)

MariaDB [studydb]> select * from lect2;
+-----+--------------------------+-----------+------------+--------------+--------------+
| lno | titl                     | room_name | manager_no | manager_name | manager_posi |
+-----+--------------------------+-----------+------------+--------------+--------------+
|   1 | 자바프로그래밍           | 501       |        303 | m303         | 주임         |
|   2 | IoT프로그래밍            | 301       |       NULL | NULL         | NULL         |
|   3 | 윈도우프로그래밍         | NULL      |        304 | m304         | 과장         |
+-----+--------------------------+-----------+------------+--------------+--------------+
3 rows in set (0.004 sec)

MariaDB [studydb]> /* 위의 질의문을 view를 사용하여 다시 작성해보자! */
MariaDB [studydb]> select 
    ->     la.lano, 
    ->     (select name from memb where mno=la.mno) as snm,
    ->     lec.titl,
    ->     lec.room_name,
    ->     lec.manager_name,
    ->     lec.manager_posi
    -> from lect_appl la 
    ->     join lect2 lec on la.lno=lec.lno;
+------+------+--------------------------+-----------+--------------+--------------+
| lano | snm  | titl                     | room_name | manager_name | manager_posi |
+------+------+--------------------------+-----------+--------------+--------------+
|    1 | s100 | 자바프로그래밍           | 501       | m303         | 주임         |
|    2 | s101 | 자바프로그래밍           | 501       | m303         | 주임         |
|    3 | s102 | 자바프로그래밍           | 501       | m303         | 주임         |
|    5 | s100 | IoT프로그래밍            | 301       | NULL         | NULL         |
|    4 | s104 | IoT프로그래밍            | 301       | NULL         | NULL         |
|    6 | s101 | 윈도우프로그래밍         | NULL      | m304         | 과장         |
|    7 | s102 | 윈도우프로그래밍         | NULL      | m304         | 과장         |
|    8 | s104 | 윈도우프로그래밍         | NULL      | m304         | 과장         |
+------+------+--------------------------+-----------+--------------+--------------+
8 rows in set (0.001 sec)

where 절에 서브쿼리 사용하기

MariaDB [studydb]> select 
    ->     la.lano, 
    ->     /* (select titl from lect where lno=la.lno) as lect_title, */ 
    ->     (select name from memb where mno=la.mno) as stud_name,
    ->     lec.titl,
    ->     lec.room_name,
    ->     /* lec.manager_no, */
    ->     lec.manager_name,
    ->     lec.manager_posi
    -> from lect_appl la 
    ->     join lect2 as lec on la.lno=lec.lno;
+------+-----------+--------------------------+-----------+--------------+--------------+
| lano | stud_name | titl                     | room_name | manager_name | manager_posi |
+------+-----------+--------------------------+-----------+--------------+--------------+
|    1 | s100      | 자바프로그래밍           | 501       | m303         | 주임         |
|    2 | s101      | 자바프로그래밍           | 501       | m303         | 주임         |
|    3 | s102      | 자바프로그래밍           | 501       | m303         | 주임         |
|    5 | s100      | IoT프로그래밍            | 301       | NULL         | NULL         |
|    4 | s104      | IoT프로그래밍            | 301       | NULL         | NULL         |
|    6 | s101      | 윈도우프로그래밍         | NULL      | m304         | 과장         |
|    7 | s102      | 윈도우프로그래밍         | NULL      | m304         | 과장         |
|    8 | s104      | 윈도우프로그래밍         | NULL      | m304         | 과장         |
+------+-----------+--------------------------+-----------+--------------+--------------+
8 rows in set (0.001 sec)

MariaDB [studydb]> /* 과장 또는 대리 매니저가 담당하고 있는 수강 신청만 추출하기 */
MariaDB [studydb]> select 
    ->     la.lano, 
    ->     /* (select titl from lect where lno=la.lno) as lect_title, */ 
    ->     (select name from memb where mno=la.mno) as stud_name,
    ->     lec.titl,
    ->     lec.room_name,
    ->     /* lec.manager_no, */
    ->     lec.manager_name,
    ->     lec.manager_posi
    -> from lect_appl la 
    ->     join lect2 as lec on la.lno=lec.lno 
    -> where
    ->     lec.manager_no in (select mno from mgr where posi in ('과장', '주임'));
+------+-----------+--------------------------+-----------+--------------+--------------+
| lano | stud_name | titl                     | room_name | manager_name | manager_posi |
+------+-----------+--------------------------+-----------+--------------+--------------+
|    1 | s100      | 자바프로그래밍           | 501       | m303         | 주임         |
|    2 | s101      | 자바프로그래밍           | 501       | m303         | 주임         |
|    3 | s102      | 자바프로그래밍           | 501       | m303         | 주임         |
|    6 | s101      | 윈도우프로그래밍         | NULL      | m304         | 과장         |
|    7 | s102      | 윈도우프로그래밍         | NULL      | m304         | 과장         |
|    8 | s104      | 윈도우프로그래밍         | NULL      | m304         | 과장         |
+------+-----------+--------------------------+-----------+--------------+--------------+
6 rows in set (0.012 sec)

• in 대신 = 은 안된다. mno가 여러개이기 때문에.

API

DBMS 와 Client

• DBMS 와 통신하기 위해서는 전용 프로토콜을 알고 있어야 한다.
• 각각의 DBMS 마다 전용 프로토콜이 다르고 전용 프로토콜로 통신하기 위해서 전용 클라이언트를 사용해야 한다.
  • 프로토콜을 안다고 해도 통신 프로그램을 짠다는 것은 많은 노동을 요구한다.
  • 또한 개발자들이 같은 일을 하는 코드를 중복 작성하는 문제도 있다.

DBMS Native API

• 조금 더 수월한 통신을 위해 전용 프로토콜을 사용해서 통신을 하는 API를 DBMS 회사에서 제공하기 시작했다.
  • 이것을 Vender API, Native API라고 부른다.
    • API 는 c, c++ 로 만들어졌다.
  • 이 API 를 사용하는 전용 App을 사용해 통신을 한다.
    • 단, DBMS 마다 함수 사용법(이름, 리턴타입, 파라미터 등)이 다르다.
    • 그렇기 때문에 DBMS 마다 API와 통신할 APP 을 만들어야 했다.

ODBC driver

• Open DataBase Conectivity
• ODBC API 규격에 맞춰 만든 것이 ODBC Driver 고 이것을 사용해 DBMS API 에 접속한다.
• DBMS 에 종속되지 않아서 코드에 일관성이 있다.
• 단, ODBC Driver 가 API 를 모두 구현한 것은 아니다.

JDBC driver

• JDBC API 규격(java.sql., javax.sql.)에 맞춰 인터페이스를 구현한 것.
• Java App 이 드라이버를 call 하면 드라이버가 Oracle이나 MariaDB의 ODBC 드라이버를 call 한다.

Type 1 : ODBC-JDBS bridge

• JRE에 기본으로 포함되어 있다.
• java App 에서 JDBC 드라이버를 call 하면 JDBC 드라이버가 ODBC 드라이버를 call 한다.(c, c++ 함수호출 코드 포함. 드라이버가 100% 자바 코드가 아니다.) 그리고 ODBC 드라이버는 Native API 구현체(ODBC 드라이버와 Native API 구현체는 사용자 PC에 설치되어 있어야 한다.)를 통해서 DBMS(서버) 와 통신을 한다.
• 실행 속도가 느리다.

Type 2

• JDBC API 규격에 따라 DBMS별로 JDBC Driver를 만든다.
• 벤더가 제공한다.
• Native API 구현체를 call 한다.
• JDBC 드라이버(*.jar) 를 별도로 다운로드 해야한다.
• Native API 구현체를 PC에 설치해야 한다.
• JDBC driver 는 c, c++ 함수호출 코드를 포함하고 있어서 100% 자바 코드가 아니다.

Type 4

• DBMS 전용 프로토콜로 직접 통신한다.
• 벤더가 직접 제공한다.
• 별도 다운로드가 필요하다.
• c, c++ 함수를 호출하지 않는다.
• 100% pure java
• Java 앱에서 Oracle JDBC Driver 나 MariaDB JDBC Driver를 호출해서 각 DBMS 와 통신한다.

Type 3

• App 과 각 DBMS 의 JDBC Driver 를 결합해서 배포하는 방식은 DBMS 가 다르면 JDBC Driver를 교체해야 하는 번거로움이 있다.
• Type 3 JDBC Driver와 각 DBMS Driver 사이에 중계 서버를 둬서 중계 서버가 설정된 JDBC Driver를 사용하여 일을 처리하고 결과를 리턴한다.
• 이점으로는 DBMS 에 상관없이 단일 JDBC 드라이버를 사용
  • 배포 및 관리가 편하다.
• 단, 실행 속도가 느리다.

MariaDB

• mavenrepository.com 나 https://search.maven.org/ 에서 mariaDB 검색
• mariadb-java-client 를 build.gradle 에 넣어두고 gradle eclipse 를 한다.

JDBC programming

• 1) java.sql.Driver. 구현체를 DriverManager 객체에 등록한다.
  • DriverManager 는 Driver 구현을 관리
• 2) java.sql.DriverManager를 통해 DBMS 와 연결
  • getConnection() -> DriverManager -> connection() -> MariaDB 의 Driver 구현체 -> MariaDB의 Connection 구현체