Schema 구현 및 INNER JOIN과 OUTER JOIN
오늘 실습한 SQL 및 Schema 과제 중 일부를 복습해보자
먼저 위의 테이블들을 intellij에서 schema.sql파일에 구현했다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
CREATE TABLE `user` (
`id` int PRIMARY KEY AUTO_INCREMENT,
`name` varchar(255) not NULL,
`email` varchar(255) not NULL
);
CREATE TABLE `content` (
`id` int PRIMARY KEY AUTO_INCREMENT,
`title` varchar(255) not NULL,
`body` varchar(255) not NULL,
`created_at` timestamp not NULL DEFAULT CURRENT_TIMESTAMP,
`userId` int,
FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
);
CREATE TABLE `category` (
`id` int PRIMARY KEY AUTO_INCREMENT,
`name` varchar(255) not NULL
);
CREATE TABLE `role` (
`id` int PRIMARY KEY AUTO_INCREMENT,
`name` varchar(255) not NULL
);
CREATE TABLE `content_category` (
`id` int PRIMARY KEY AUTO_INCREMENT,
`contentId` int,
FOREIGN KEY (`contentId`) REFERENCES `content` (`id`),
`categoryId` int,
FOREIGN KEY (`categoryId`) REFERENCES `category` (`id`)
);
ALTER TABLE user ADD roleId int;
ALTER TABLE user ADD FOREIGN KEY (roleId) REFERENCES role (id);
|
cs |
schema.sql파일을 구현할 때 가장 신경써야 했던 부분은 코드 흐름에 맞게 작성해야만 모든 table을 인식한다는 점이었다.
처음 파일을 구현할 때 아래와 같이 user 테이블 안에 roleId-pk를 같이 구현했었다.
|
1
2
3
4
5
6
7
|
CREATE TABLE `user` (
`id` int PRIMARY KEY AUTO_INCREMENT,
`name` varchar(255) not NULL,
`email` varchar(255) not NULL,
`roleId` int,
FOREIGN KEY (`roleId`) REFERENCES 'role' (`id`);
);
|
cs |
코드의 흐름은 기본적으로 위에서 아래로 내려가기 때문에 6번째 줄에서는 role 테이블이 구현되어 있지 않으므로 에러가 생길 수 밖에 없었다.
이를 인지하고 role 테이블을 생성한 후에 ALTER를 통해 user테이블을 수정했고, 문제를 해결했다.
(지금 와서 든 생각이지만 role테이블을 생성하고 바로 아래에 user테이블을 수정하는게 가독성이 좀 더 좋지 않을까 싶다.)
다음은 SQL을 작성해보는 문제 중 JOIN과 관련된 문제를 추려보았다.
| 1번 문제 |
content의 title과 그 컨텐츠를 작성한 user의 name을 찾기 위한 SQL을 작성해주세요.
- 저자가 없더라도, 컨텐츠의 title을 모두 찾아야합니다.문제풀이
(1)SQL목적 - content.title 과 user.name 을 찾기 : SELECT 사용
(2)content와 user는 서로 user.id와 content.userId로 연결되어 있음(교집합이 존재) : JOIN 사용
(3)저자가 없더라도 컨텐츠의 title을 모두 찾아야 한다 = content.userId의 값이 null이라도 content.title 을 찾아야한다 = INNER JOIN 과 OUTER JOIN 중 OUTER JOIN을 사용
|
1
2
3
4
|
SELECT content.title, user.name
FROM content
LEFT JOIN user
ON content.userId = user.id;
|
cs |
| 2번 문제 |
minsanggu이 작성한 content의 category name을 찾기위한 SQL을 작성해주세요.문제풀이
(1)SQL목적 - category.name 을 찾기 : SELECT 사용
(2)user, content, content_category, category는 연결되어 있음 : JOIN 사용
(3)minsanggu가 작성한 category name을 찾아야 한다 = INNER JOIN을 계속 연결하여 user.name = 'minsanggu'로 마무리
|
1
2
3
4
5
6
7
8
|
SELECT category.name FROM category
JOIN content_category
ON content_category.categoryId = category.id
JOIN content
ON content.id = content_category.contentId
JOIN user
ON user.id = content.userId
WHERE user.name = 'minsanggu';
|
cs |
+ 다음부턴 일일이 적지 말고 AS를 활용하자
예) SELECT cg.name FROM category AS cg
이번 과제로 SQL 문법과 스키마를 많이 공부할 수 있었다.
역시 내 손으로 코드를 직접 타이핑하면서 공부하는 것이 가장 빨리 익숙해진다는 걸 다시금 느낄 수 있었다.