[4] DB 연관 관계

1. DB 연관 관계

관계형 데이터베이스는 테이블끼리 관계를 맺을 수 있다.

관계는 논리적으로 연관이 있는 두 테이블 사이의 연결을 설정한다.

테이블 구조를 정제하고 중복 데이터를 최소화하는 것을 도와준다.

 

1.1 1:1 관계

하나의 테이블이 상대 테이블과 반드시 단 하나의 관계를 가지는 것

하나의 테이블에 데이터를 다 넣지 않는 이유는 아래와 같다.

• 너무 많은 칼럼들이 있을 때
• 보안상 민감한 정보가 있을 때

1.2 1:N 관계

한 쪽 테이블의 레코드가 관계를 맺은 테이블의 여러 레코드와 연결된다는 것

외래키를 이용하고, 부모와 자식 관계라고도 한다.

1.3 N:N 관계

양쪽 엔티티 모두에서 1:N 관계를 가지는 것

두 테이블의 대표키를 컬럼으로 갖는 연결 테이블을 생성해서 관리한다.

 

2. 연관 관계 실습

CREATE TABLE student 
( 
    id INT AUTO_INCREMENT PRIMARY KEY, 
    name VARCHAR(30) NOT NULL, 
    age INT NOT NULL, 
    class INT, 
    height FLOAT 
);

CREATE TABLE teacher 
( 
    id INT AUTO_INCREMENT PRIMARY KEY, 
    name VARCHAR(30) NOT NULL, 
    age INT NOT NULL, 
    phone_number VARCHAR(30) 
);

먼저 필요한 테이블을 생성한다.

CREATE TABLE 테이블명 
( 
    ... 
    parent_id 
    FOREIGN KEY (parent_id) 
    REFERENCES 부모테이블명(부모테이블의 키) [ON UPDATE ON DELETE 제약조건] 
);

CREATE TABLE class 
( 
    id INT AUTO_INCREMENT PRIMARY KEY, 
    name VARCHAR(30) NOT NULL, 
    teacher_id INT NOT NULL, 
    FOREIGN KEY (teacher_id) 
    REFERENCES teacher(id) 
);

class 테이블은 teacher 와 N:1 관계를 맺는다.

외래키를 위와 같이 설정한다.

 

ON DELETE, ON UPDATE

• 테이블의 데이터를 수정할 때, 참조되어 있는 테이블에도 영향을 끼친다.
• 그런 상황에서 CASCADE, NO ACTION, RESTRICT 등의 방법으로 어떻게 처리할 지 결정한다.

INSERT INTO teacher(name, age, phone_number) 
    VALUES("조정식", 28, "0102938491");
INSERT INTO class(name, teacher_id) 
   VALUES ("체육", 1);

레코드를 만든다.

 

3. JOIN

연관이 된 두 테이블을 서로 이어준다.

보통 Primary key혹은 Foreign key로 두 테이블을 연결한다.

INSERT INTO teacher(name, age, phone_number)  
		VALUES("신승범", 42, "010-2933-3492"); 
INSERT INTO teacher(name, age, phone_number)  
		VALUES("한석원", 49, "010-3939-33939"); 
INSERT INTO class(name, teacher_id) 
   VALUES ("미술", 1); 
INSERT INTO class(name, teacher_id) 
   VALUES ("음악", 1); 
INSERT INTO class(name, teacher_id) 
   VALUES ("수학", 2); 
INSERT INTO class(name, teacher_id) 
   VALUES ("영어", 3); 
INSERT INTO class(name, teacher_id) 
   VALUES ("도덕", 3); 
CREATE TABLE class_info( 
	class_id INT NOT NULL PRIMARY KEY, 
	description VARCHAR(30) NOT NULL, 
	midterm_date VARCHAR(30) NOT NULL 
); 
INSERT INTO class_info(class_id, description, midterm_date) VALUES(1, "체육 수업입니다", "4/14"); 
INSERT INTO class_info(class_id, description, midterm_date) VALUES(2, "미술 수업입니다", "4/15"); 
INSERT INTO class_info(class_id, description, midterm_date) VALUES(4, "수학 수업입니다", "4/16"); 
INSERT INTO class_info(class_id, description, midterm_date) VALUES(5, "도덕 수업입니다", "4/16");

먼저 필요한 레코드를 생성한다.

 

3.1 LEFT JOIN

SELECT {첫번째테이블이름} LEFT JOIN {두번째테이블이름} ON 조건

SELECT * FROM class  
LEFT JOIN class_info ON class.id = class_info.class_id;

ON 절은 WHERE 절과 똑같이 사용할 수 있는 모든 조건을 사용할 수 있다.

이때 ON 절의 조건을 만족하지 않는 경우에는 첫 번째 테이블의 필드 값은 그대로 가져온다.

class 에는 있지만 class_info 에는 없는 칼럼은 NULL로 표시된다.

 

3.2 INNER JOIN

SELECT * FROM class  
INNER JOIN class_info ON class.id = class_info.class_id;

왼쪽 테이블과 오른쪽 테이블이 모두 가지고 있는 데이터만 검색

 

3.3 RIGHT JOIN

SELECT * FROM class  
RIGHT JOIN class_info ON class.id = class_info.class_id;

class_info 가 NULL이 아닌 것 중 조건을 만족하는 데이터를 가져온다.

 

3.4 M:N 사용해보기

CREATE TABLE student_class 
( 
    student_id INT NOT NULL, 
    class_id INT NOT NULL, 
    FOREIGN KEY (student_id) 
    REFERENCES student(id), 
    FOREIGN KEY (class_id) 
    REFERENCES class(id) 
);

INSERT INTO student_class(student_id, class_id) 
    VALUES(1, 1); 
INSERT INTO student_class(student_id, class_id) 
    VALUES(1, 2); 
INSERT INTO student_class(student_id, class_id) 
    VALUES(1, 3); 
INSERT INTO student_class(student_id, class_id) 
    VALUES(2, 2); 
INSERT INTO student_class(student_id, class_id) 
    VALUES(2, 3); 
INSERT INTO student_class(student_id, class_id) 
    VALUES(3, 4);

student_class는 어떤 학생이 어떤 수업을 듣는지 나타낸다.

SELECT * FROM student INNER JOIN student_class ON student.id = student_class.student_id WHERE student.id = 1;

특정 학생이 듣는 수업들의 id를 가져온다.