[SQL] im-sprint-learn-sql

데이터 베이스용 프로그래밍 언어인 SQL을 공부했다.

데이터베이스에 쿼리를 보내 CRUD를 할수있는 언어인 SQL 문법을 스프린트 기준으로 정리해보자.

 

스프린트는 part 5까지 있고,

part1,2는 mysql 퀴즈와 설치로 이루어져있어 어렵지 않으므로 part3부터 다루기로 한다.

 

Part 3의 목표

데이터베이스를 연결하고, 주어진 스키마를 구현할 수 있도록 SQL 문 작성.

vscode에서 과제를 시작하기에 앞서 설치해둔mysql에 접속해야 테스트케이스를 통과할수있다.

/migrations/schema.sql

이번 스프린트의 핵심이고, 반드시 작성해야 하는 부분. 테이블과 컬럼을 생성해야 과제를 진행할수있다.

만들어야하는 테이블 스키마를 보고 필요한부분을 채운다

 

users, content 스키마

 

USE learnmysql;

/* DESIGN SCHEMA FOR REQUIREMENT */

-- PART 3:
-- 여기 user 테이블 생성 SQL 구문이 있습니다. 
-- user 테이블을 참고해서, content 테이블 생성 SQL 구문을 완성하세요.

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`)
);​

schema.sql을 이렇게 작성한뒤 part3.js로 넘어가자.

TODO: Q 3-1. 현재 있는 데이터베이스에 존재하는 모든 테이블 정보를 보기위한 SQL을 작성해주세요.

const PART3_1 = `SHOW TABLES`;

TODO: Q 3-2. user 테이블의 구조를 보기위한 SQL을 작성해주세요.
        - 요구사항에 맞는 user 테이블을 작성해야만, 테스트를 통과합니다.

const PART3_2 = `describe user`;

TODO: Q 3-3. content 테이블의 구조를 보기위한 SQL을 작성해주세요.
        - 요구사항에 맞는 content 테이블을 작성해야만, 테스트를 통과합니다.
const PART3_3 = `describe content`;

Part 4는 Part 3에서 구성한 스키마에 데이터를 추가하거나, 불러올 수 있도록 SQL 문을 작성하는 것이 목표

TODO: Q 4-1. user 테이블에 존재하는 모든 컬럼을 포함한 모든 데이터를 확인하기 위한 SQL을 작성해주세요.
const PART4_1 = `select * from user`;

TODO: Q 4-2. user 테이블에 존재하는 모든 데이터에서 name 컬럼만을 확인하기 위한 SQL을 작성해주세요.
const PART4_2 = `select name from user`;

TODO: Q 4-3. user 테이블에 데이터를 추가하기 위한 SQL을 작성해주세요.
const PART4_3 = `INSERT INTO user (name, email) VALUES ('WOODY', 'woody@naver.com')`;

TODO: Q 4-4. user 테이블에서 특정 조건을 가진 데이터를 찾기위한 SQL을 작성해주세요.
        - 조건 : name이 duhyunkim이여야 합니다.
const PART4_4 = `SELECT name FROM user WHERE user.name ='duhyunkim'`;

TODO: Q 4-5. user 테이블에서 특정 조건을 가진 데이터를 찾기위한 SQL을 작성해주세요.
        - 조건 : name이 duhyunkim이 아니여야 합니다.
const PART4_5 = `SELECT name FROM user WHERE NOT user.name = 'duhyunkim'`;

TODO: Q 4-6. content 테이블에 존재하는 모든 데이터에서 title 컬럼만을 찾기 위한 SQL을 작성해주세요.
const PART4_6 = `SELECT title FROM content`;

TODO: Q 4-7. content의 title과 그 컨텐츠를 작성한 user의 name을 찾기 위한 SQL을 작성해주세요.
        - 저자가 없더라도, 켄턴츠의 title을 모두 찾아야합니다.
const PART4_7 = `SELECT content.title, user.name FROM user INNER JOIN content ON user.id = content.id`;

TODO: Q 4-8. content의 title과 그 컨텐츠를 작성한 user의 name을 찾기 위한 SQL을 작성해주세요.
        - 저자가 있는 컨텐츠의 title만 찾아야합니다.
const PART4_8 = `
SELECT content.title, user.name 
FROM user 
INNER JOIN content ON user.id = content.id 
WHERE content.userId `;

TODO: Q 4-9. content의 데이터를 수정하기 위한 SQL을 작성해주세요.
        - title이 database sprint인 content 데이터에서 body를 database is very easy로 수정해야합니다.
const PART4_9 = `UPDATE content
SET body = 'database is very easy'
WHERE title = 'database sprint'`;

TODO: Q 4-10. content의 데이터를 추가하기 위한 SQL을 작성해주세요.
        - duhyunkim이 작성한 컨텐츠를 추가해주세요. 제목과 본문은 자유입니다. (참고: duhyunkim의 아이디는 1입니다.)
const PART4_10 = `
INSERT INTO content(title, body, userId) 
VALUES ('힘내요', '통과' , '1')
`;

 

Part 5는 요구 조건에 따라 스키마를 추가/변경하고, 추가/변경된 스키마를 확인하는 과정.

1:N(일대다), N:N(다대다) 관계와 더불어 JOIN 테이블에 대해 이해하기

 

먼저,

/migrations/schema.sql에 테이블을 추가 및 수정해준다.

기존의 작성 테이블 토대로 작성 

 

TODO: Q 5-1-1. category 테이블의 구조를 보기위한 SQL을 작성해주세요.
const PART5_1_1 = `describe category`;

TODO: Q 5-1-2. content_category 테이블의 구조를 보기위한 SQL을 작성해주세요.
const PART5_1_2 = `DESCRIBE content_category`;

TODO: Q 5-1-3. role 테이블의 구조를 보기위한 SQL을 작성해주세요.
const PART5_1_3 = `DESCRIBE role`;

TODO: Q 5-1-4. user 테이블의 구조를 보기위한 SQL을 작성해주세요.
const PART5_1_4 = `DESCRIBE user`;

TODO: Q 5-2-1. category 테이블에 존재하는 데이터에서 id, name을 찾는 SQL을 작성해주세요.
const PART5_2_1 = `select id, name from category`;

TODO: Q 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`;

TODO: Q 5-2-3. 어느 role에도 속하지 않는 user의 모든 컬럼 데이터를 찾기위한 SQL을 작성해주세요.
const PART5_2_3 = `SELECT * FROM user WHERE roleId IS NULL`;

TODO: Q 5-2-4. content_category 테이블에 존재하는 모든 칼럼의 데이터를 찾기위한 SQL을 작성해주세요.
const PART5_2_4 = `SELECT * FROM content_category`;

TODO: Q 5-2-5. jiSungPark이 작성한 content의 title을 찾기위한 SQL을 작성해주세요.
const PART5_2_5 = `
SELECT content.title, user.name 
FROM user 
INNER JOIN content ON user.id = content.id 
WHERE user.name = 'jiSungPark'`;

TODO: Q 5-2-6. JiSungPark이 작성한 content의 category name을 찾기위한 SQL을 작성해주세요.
const PART5_2_6 = `
SELECT user.name, category.name
FROM user
INNER JOIN content ON user.id = content.userId
INNER JOIN content_category ON content_category.contentId = content.id
INNER JOIN category ON content_category.categoryId = category.id
WHERE user.name = 'jiSungPark'`;

TODO: Q 5-2-7. category의 name이 soccer인 content의 title, body, created_at을 찾기위한 SQL을 작성해주세요.
const PART5_2_7 = `
SELECT content.title, content.body, content.created_at
FROM content
JOIN content_category ON content.Id = content_category.contentID
JOIN category ON categoryId = category.id
WHERE category.name = 'soccer'`;

TODO: Q 5-2-8. category의 name이 soccer인 content의 title, body, created_at, user의 name을 찾기위한 SQL을 작성해주세요.
const PART5_2_8 = `
SELECT content.title, content.body, content.created_at, user.name
FROM user
JOIN content ON user.id = content.userId
JOIN content_category ON content.Id = content_category.contentID
JOIN category ON categoryId = category.id
WHERE category.name = 'soccer'`;

TODO: Q 5-2-9. duRiCha가 작성한 글의 개수 (컬럼명: ContentCount)를 출력하기 위한 SQL을 작성해주세요.
const PART5_2_9 = `
SELECT COUNT(content.title) AS ContentCount 
FROM user
JOIN content ON user.id = content.userId
WHERE user.name = 'duRiCha';`;

TODO: Q 5-2-10. 각 user(컬럼명: name)가 작성한 글의 개수 (컬럼명: ContentCount)를 출력하기 위한 SQL을 작성해주세요.
const PART5_2_10 = `
SELECT user.name as name, 
COUNT(content.userId) AS ContentCount
FROM user
LEFT JOIN content ON user.id = content.userId
GROUP BY user.name`;

 

SQL문법을 어렵게 생각했는데 막상 사용하다보니 관계를 잘 이해할 수 있다면 문법은 크게 어렵지 않다는 생각이 들었다.