[learn-sql] SQL query문 연습

손연주·2021년 7월 26일

Database sprint sql

SPRINT

목록 보기

1/5

Part 4

✓ TODO 4-3 : user 테이블에 데이터를 추가하기 위한 SQL을 작성해주세요.

const PART4_3 = `INSERT INTO user (name, email) VALUES ('yeonjuson', '테스트')`;

id는 AUTO_INCREMENT 처리를 해주어서 저절로 기존의 id에 1이 추가된 값이 저장되기 때문에 따로 작성하지 않았다.

✓ TODO 4-7 : content의 title과 그 컨텐츠를 작성한 user의 name을 찾기 위한 SQL을 작성해주세요.

저자가 없더라도, 컨턴츠의 title을 모두 찾아야합니다.

const PART4_7 = 
`SELECT content.title, user.name 
FROM content
LEFT JOIN user ON content.userId = user.id`

각각 데이터가 따로 있는 테이블에서 몇 개의 데이터를 연결지어 무언가를 찾고 있다. 병합하면 되겠다. 저자가 없더라도 content의 title을 모두 찾아야 된다고 한다.

LEFT JOIN은 왼쪽 테이블의 모든 레코드를 리턴하고, 이에 매칭하는 오른쪽 테이블의 레코드를 리턴하지만 조건이 맞지 않으면 NULL을 리턴한다. 따라서, 이 문제에서는 content.title을 다 가져오고, 이에 매칭하는 user.name도 다 가져오는데 매칭이 안되면 user.name이 NULL로 표시된다.

✓ TODO 4-8 : content의 title과 그 컨텐츠를 작성한 user의 name을 찾기 위한 SQL을 작성해주세요.

const PART4_8 = 
`SELECT content.title, user.name 
FROM content
JOIN user ON content.userId = user.id
`;

꼭 저자가 있는 컨텐츠의 title을 찾아야하기 때문에 content의 userId와 user의 id가 일치하는 데이터만 출력했다. INNER JOIN을 이용했다.

Part 5

✓ TODO : category, content_category, role 테이블을 만드세요.

CREATE TABLE `category` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) not NULL
);


CREATE TABLE `content_category` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `contentId` int not NULL,
  `categoryId` int not NULL
);


CREATE TABLE `role` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) not NULL
 );

ALTER TABLE `content_category` ADD FOREIGN KEY (`contentId`) REFERENCES `content` (`id`);
ALTER TABLE `content_category` ADD FOREIGN KEY (`categoryId`) REFERENCES `category` (`id`);

ALTER TABLE `content` ADD FOREIGN KEY (`userId`) REFERENCES `user` (`id`);

새롭게 알게된 두 개

원래 있던 content 코드에서 FOREIGN KEY 를 정해주지 않고도 ALTER TABLE을 이용해서 밖에서도 수정해줄 수 있다.
*로 받는 부분(?)에 FOREIGN KEY 구문을 작성하고 1로 받는 부분을 레퍼런스로 받아온다.

✓ TODO 5-2-2 : user의 name과 email 그리고 그 user가 속한 role name(컬럼명: roleName)을 찾기 위한 SQL을 작성해주세요.

- 속한 role이 없더라도, user의 name과 email,role name을 모두 찾아야합니다.

const PART5_2_2 = 
`SELECT user.name, user.email, role.name
AS roleName 
FROM user
LEFT JOIN role 
ON user.roleId = role.id`;

하지만 FROM(기준 테이블)이 role, LEFT JOIN 테이블이 user일 때는 결과가 아주 다르게 나온다. 우선, user 테이블을 기준으로 잡아서 user.name, email, role.name을 셀렉해오면 기준 테이블의 user.name과 user.email의 정보를 모두 가져온 후에 role 테이블을 LEFT JOIN 하여 role.name을 가져오고 있다. 그렇게 되면 role.name값이 없어도 null로 데이터 값이 들어가게 된다.

위는 기준 테이블을 role로 잡았을 때다. role.name 정보를 다 가져오고, 그 옆에 user.name, user.email을 붙이게 된다. 따라서 role.name 값이 없는 'woonJaeLee'의 정보는 아예 담기지가 않게 되는 것이다.

✓ TODO 5-2-9 : duRiCha가 작성한 글의 개수 (컬럼명: ContentCount)를 출력하기 위한 SQL을 작성해주세요.

const PART5_2_9 = 
`SELECT COUNT(content.id) AS 'ContentCount', user.name
FROM user
JOIN content
ON content.userId = user.id
Where user.name = 'duRiCha'
`;

테이블에 아무리 봐도 작성한 글의 개수가 있는 컬럼이 없다. 하지만 우리는 id값을 보고 알 수 있다. 글을 하나 쓸 때마다 id가 추가된다. 고로 id의 개수를 구하면 글의 개수를 알 수 있다. duRiCha가 작성한 글만 알면 되니까 INNER JOIN을 썼다. 각 두개의 테이블에서 모두 존재하는 케이스만(글의 개수가 있고, user name의 정보가 있고) 걸러주고, 유저 네임이 duRicha 인 데이터만 나오게 했다. name은 추가하지 SELECT 하지 않아도 되는데, 보기 쉽게 나타내기 위해 추가해줬다.

✓ TODO 5-2-10 : 각 user(컬럼명: name)가 작성한 글의 개수 (컬럼명: ContentCount)를 출력하기 위한 SQL을 작성해주세요.

const PART5_2_10 = 
`SELECT user.name, COUNT(content.id) AS 'ContentCount'
FROM user
LEFT JOIN content
ON content.userId = user.id
GROUP BY user.name 
`;

user를 기준 테이블로 잡고 user.name값을 다 가져온 다음에 content 를 LEFT JOIN하여 content.id 값을 가져왔다. MySQL에서 유형별로 개수를 가져오고 싶은데, 단순히 COUNT 함수로 데이터를 조회하면 전체 개수만을 가져온다. 유형별로 개수를 알고 싶을 때는 컬럼에 데이터를 그룹화 할 수 있는 GROUP BY를 사용한다.