database sql

 

subject_mst

INSERT INTO subject_mst

VALUE
	(0, 'java'),
	(0, 'c'),
	(0, '영어'),
	(0, '수학');

 

 

student_mst

INSERT INTO student_mst

VALUE
	(0, 'a'),
	(0, 'b'),
	(0, 'c'),
	(0, 'd');

 

major_mst

INSERT INTO major_mst

VALUE
	(0,'컴공'),
	(0,'디자인'),
	(0,'경영'),
	(0,'전자');

 

major_dtl

INSERT INTO major_dtl

VALUE
	(0,500),
	(0,400),
	(0,300),
	(0,350);

 

student_number_dtl

INSERT INTO student_number_dtl

VALUE
	(0,1),
	(0,2),
	(0,3),
	(0,4);

 

semester_mst

INSERT INTO semester_mst

VALUE
	(0,1,1,1,1,1),
	(0,1,2,1,1,1),
	(0,2,2,2,2,2),
	(0,3,3,3,3,3),
	(0,4,4,4,4,4),
	(0,3,1,3,3,3),
	(0,1,3,1,1,1);

 

이제는 합칠때

SELECT
snd.student_id AS '학번',
sjm.`name` AS '과목명',
stm.`name` AS '이름',
mjm.`name` AS '학과',
md.price AS '학비'


	
FROM
	semester_mst sm
	LEFT OUTER JOIN student_mst stm ON(stm.id = sm.student_id)
	LEFT OUTER JOIN student_number_dtl snd ON(snd.student_id = sm.st_num)
	LEFT OUTER JOIN subject_mst sjm ON(sjm.id = sm.sub_id)
	LEFT OUTER JOIN major_mst mjm ON(mjm.id = sm.major_id)
	LEFT OUTER JOIN major_dtl md ON(md.id = sm.price);

 

compare

문제

 

결과

데이터 양이 별로 없어서 이렇게 할 수 있으나 대용량일 때는 

학과 와 학비를 한 테이블로 하는 것이 수정할 일이 있을때 수월하다.