MySQL 23번째수업

▶ SELECT 명령문 : 조인(JOIN)

​

*조인(JOIN)

테이블의 조인에서 테이블에 있는 행은 조합되어 결과 테이블을 생성한다.

SELECT 명령문의 FROM 절에 적어도 두 개의 테이블 명세가 있고, WHERE 절에는 서로 다른 테이블의 열을 비교하는 조건이 적어도 하나가 있다면 이를 조인(JOIN)이라 한다.

​

​

예제 15-1) 학적 테이블에 존재하는 학생들 중에서 아직 등록을 하지 못한 학생이 있다. 각 학생에 대하여 학번, 이름, 등록년도, 학기, 학생이 등록한 납입금 총액을 출력하라.​

A)

mysql> select student.stu_no, stu_name, fee_year, fee_term, fee_pay

-> from student inner join fee

-> on student.stu_no = fee.stu_no;

​

​

예제 15-2) 학적 테이블과 성적 테이블을 cross join하여 학번, 이름, 성적년도, 학기를 출력하라.

A)

mysql> select s.stu_no, stu_name, sco_year, sco_term

-> from student s inner join score sc;

​

​

예제 15-3) 학적 테이블과 성적 테이블을 cross join하여 학번, 이름, 성적년도, 학기를 출력하라.(단, 학적 테이블에 존재하는 '20161001' 학생만을 출력하라.)

A)

mysql> select s.stu_no, stu_name, sco_year, sco_term

-> from student s inner join score sc

-> on s.stu_no = '20161001';

​

​

예제 15-4) 학적 테이블에 존재하는 학생들 중에서 등록한 학생의 학번, 이름, 반, 등록년도, 학기, 등록금 총액을 출력하라.

A)

inner join으로 출력

mysql> select s.stu_no, stu_name, class, fee_year, fee_term, fee_pay

-> from student s inner join fee f

-> on s.stu_no = f.stu_no

-> order by s.stu_no, fee_year, fee_term;

​

A)

left outer join으로 출력

mysql> select s.stu_no, stu_name, class, fee_year, fee_term, fee_pay

-> from student s left outer join fee f

-> on s.stu_no = f.stu_no

-> order by s.stu_no, fee_year, fee_term;

​

​

예제 15-5) 수강 테이블에 존재하는 학생의 수강신청년도, 학기, 과목코드, 과목명을 출력하라.

A)

mysql> select stu_no, att_year, att_term, a.sub_code, sub_name

-> from attend a inner join subject s

-> on a.sub_code = s.sub_code;

​

​

예제 15-6) 재학생이면서 수강신청테이블에 존재하는 학생의 학번, 이름, 수강신청년도, 학기, 과목코드, 과목명, 교수코드, 교수명을 출력하라. (단, 출력순서는 학번, 수강년도, 수강학기, 수강코드 순이고, 과목명과 교수명을 표제어로 사용하라.)

A)

mysql> select s.stu_no, stu_name, att_year, att_term, a.sub_code, sub_name 과목명,

-> p.prof_code, prof_name 교수명

-> from student s inner join attend a inner join subject b inner join professor p

-> on s.stu_no = a.stu_no and a.sub_code = b.sub_code and a.prof_code = p.prof_code

-> order by s.stu_no, att_year, att_term, a.sub_code;

​

​

예제 15-7) 학적 테이블에 존재하는 모든 학생이 출력될 수 있도록 left join으로 출력하라. (단, 미등록자는 등록년도를 "미납", 학기는 0, 납입금 총액도 0으로 출력하라.)

A)

mysql> select student.stu_no, stu_name, fee_year, fee_term, fee_pay

-> from student left join fee

-> on student.stu_no = fee.stu_no

-> union

-> select stu_no, stu_name, '미납', 0, 0

-> from student

-> where stu_no not in

-> (select stu_no from fee);

​

​

​

<연습문제>

​

15-2) 각 학생에 대하여 학번, 이름, 등록금 총액의 합을 구하여라.(단, 등록금을 납부하지 못한 학생은 0으로 출력하라)

A)

mysql> select s.stu_no, stu_name, ifnull(sum(fee_pay), 0)

-> from student s left join fee f

-> on s.stu_no = f.stu_no

-> group by s.stu_no;

​

​

15-3) 각 학생에 대하여 학번, 이름, 동아리 명을 출력하라.(단, 재학생이면서 동아리에 가입하지 않은 학생들은 "미가입"을 출력하라. left join으로 출력​)

A)

mysql> select s.stu_no, s.stu_name, ifnull(cir_name, '미가입')

-> from student s left join circle c

-> on s.stu_no = c.stu_no;

​​

​

​​

▶ 테이블의 변경

​

1. 새로운 행 삽입

​​

예제 16-1) 교수코드 "6001"과 교수이름 "대조영"을 추가하라. (단, 데이터는 교수코드와 교수명만 입력한다.)

A)

mysql> insert into professor(prof_code, prof_name)

-> values('6001', '대조영');

Query OK, 1 row affected (0.01 sec)

​

​

예제 16-2) 새로운 과목(JSP Programming)을 추가하라. 단, 데이터는 다음과 같다.

A)

mysql> insert into subject values

-> ('5001', 'JSP 프로그래밍', 'JSP Programming', 2006);

Query OK, 1 row affected (0.00 sec)

​​

 

​

2. 테이블에서 테이블로 행 복사

​

예제 16-3) 새로운 호남(광주, 전라남도, 전라북도, 제주도) 향우회 테이블을 생성시켜보자. 향우회 테이블은 학생의 학번, 이름, 우편번호, 현주소, 휴대폰번호로 구성되어있다.

A)

​

(1) 먼저 새로운 테이블을 생성한다

​

mysql> create table hang(

-> stu_no char(10) Not null,

-> stu_name char(10) Not null,

-> hyun_post_no varchar(6) Not null,

-> hyun_add varchar(100) Not null,

-> hyun_mobile varchar(14),

-> primary key (stu_no)

-> ) engine = innoDB;

Query OK, 0 rows affected (0.02 sec)

​

​

(2) 다음 INSERT 명령문은 STUDENT 테이블에 등록되어있는 호남지역(현주소 우편번호 첫 번째 1자리가 광주, 제주는 6이고 전남, 전북은 5로 시작하는)의 학생에 관한 정보를 HANG 테이블로 복사한다.

​

mysql> insert into hang

-> (stu_no, stu_name, hyun_post_no, hyun_add, hyun_mobile)

-> select stu_no, stu_name, post_no, address, mobile

-> from student

-> where substring(post_no, 1, 1) = 5 or substring(post_no, 1, 1) = 6;

Query OK, 4 rows affected (0.01 sec)

Records: 4 Duplicates: 0 Warnings: 0

​

​

(3) 다음은 호남지역에 사는 학생들의 내용을 확인하여 보자.

​

​​

​

3. 행 값의 변경

​

예제 16-5) 자바프로그래밍의 생성년도를 2010년으로 갱신하라.

A)

mysql> update subject

-> set create_year = '2010'

-> where sub_name = '자바프로그래밍';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

​

​

예제 16-6) 모든 등록금 총액을 5% 증가시켜라.

A)

mysql> update fee

-> set fee_total = fee_total * 1.05;

Query OK, 24 rows affected (0.01 sec)

Rows matched: 24 Changed: 24 Warnings: 0

​

​

예제 16-7) 이순신 학생이 서울특별시 노원구 석계로 현대아파트 2동 1004호에서 서울특별시 영등포구 영등포로79가길 10 모던하우스 403호로 이사하였다. 우편번호는 07318이다. 데이터를 변경하라.

A)

mysql> update student

-> set address = '서울특별시 영등포구 영등포로79가길 10 모던하우스 403호',

-> post_no = '07318'

-> where stu_name = '이순신';

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

​

​

예제 16-8) 동아리에 가입한 학생들을 대상으로 동아리 회비를 5000원 등록금에 추가하여 납부하도록 하고, 등록 테이블의 FEE_TOTAL과 FEE_PAY도 변경하라.

A)

mysql> update fee

-> set fee_price = fee_price + 5000,

-> fee_total = fee_total + 5000,

-> fee_pay = fee_pay + 5000

-> where stu_no in

-> (select stu_no from circle); → 동아리에 가입한 학생들에게만 적용되도록

Query OK, 14 rows affected (0.01 sec)

Rows matched: 14 Changed: 14 Warnings: 0

​

​

적용되었나 확인해보기↓

A)

mysql> select stu_no, fee_year, fee_term, fee_price, fee_total, fee_pay

-> from fee

-> where stu_no in

-> (select stu_no from circle);

​

​

예제 16-9) "운영체제" 과목을 삭제하라.

A)